Zvládnutí databáze SQLite v Pythonu

SQLite je systém správy relačních databází založený na jazyku SQL; je to bezserverový databázový stroj s nulovou konfigurací. Je to jeden z nejpopulárnějších databázových strojů a jeho použití v malých aplikacích je velmi snadné. Vytvoří pouze jeden soubor na disku pro uložení celé databáze, díky čemuž je soubor přenosný. Používá se v OS Android jako primární zdroj pro ukládání dat. Používá jej také Google Chrome k ukládání dat o stránkách a uživatelských dat, včetně hesel v místním počítači.

Pokročilá práce s databází SQLite v Pythonu

V tomto výukovém programu se budete zabývat těmito tématy: vkládání obrázků do tabulky SQLite, výpis tabulek obsažených v databázi, identifikace celkových změn od připojení databáze, zálohování databáze, výpis databáze SQLite, vrácení zpět v SQLite, mazání záznamy z tabulky, zrušení tabulky a výjimky databáze SQLite.

Můžete také vidět první část tohoto tutoriálu, který představuje základy SQLite, výhody jeho použití, připojení k databázovému souboru, vytvoření tabulky v databázi, vkládání dat do tabulky, dotazování na data z tabulky, aktualizace tabulky a mnoho dalšího.

Soubory a obrázky v databázi SQLite

Při práci s databázemi existují situace, kdy je třeba vložit obrázky nebo soubory do databáze nebo z ní exportovat. Například pokud vytváříte databázi pro ukládání dat zaměstnanců, možná budete také muset vložit obrázky všech zaměstnanců v databázi.

Chcete-li přidat obrázky do databáze SQLite, musíme použít datový typ BLOB SQLite. Datový typ BLOB () se používá k ukládání velkých objektů, obvykle velkých souborů, jako jsou obrázky, hudba, videa, dokumenty, PDF atd. Prvním krokem je převod dat a obrázků do bajtového objektu Pythonu, který je podobný BLOB datovému typu SQLite. Než budete pokračovat, vytvořte tabulku s názvem student v databázi s poli id, jméno, obrázky, značky. Spuštěním následujícího kódu vytvořte tabulku.

import sqlite3 conn = sqlite3.připojit ("ukázka.db ") print (" \ n [+] Úspěšně připojeno k databázi ") cur = conn.cursor () print ("\ n [+] Kurzor byl úspěšně nastaven") table = cur.execute ("" "VYTVOŘIT TABULKU student (id INT PRIMÁRNÍ KLÍČ, název TEXT, obrázky BLOB, označí TEXT);" "") print ("\ n [+] Tabulka byla úspěšně vytvořena") cur.zavřít () připojit.commit () conn.zavřít()

Tento program vytvoří novou tabulku s názvem student. V terminálu uvidíte následující výstup.

Vkládání obrázku

Chcete-li vložit obrázek do databáze SQLite, transformujte obrázek v bajtovém objektu pythonu a poté jej vložte do sloupce obrázků, který přijímá data BLOB. Spuštěním následujícího kódu přidejte obrázek obr.png v databázi pomocí Pythonu.

importovat sqlite3 conn = sqlite3.připojit ("ukázka.db ") spoj.text_factory = str print ("\ n [+] Úspěšně připojeno k databázi") cur = conn.cursor () print ("\ n [+] Kurzor byl úspěšně nastaven") s open ("obr.png "," rb ") jako soubor: data = soubor.read () python_tuple = (101, "robin", data, "90") print ("\ n [+] Obrázek byl úspěšně importován") print ("\ n [+] Nyní se vkládá do databáze") cur.vykonat ("VLOŽTE DO studenta (ID, jméno, obrázky, značky) HODNOTY (?,?,?,?) ", python_tuple) print (" \ n [+] Data byla úspěšně vložena ") cur.zavřít () připojit.commit () conn.zavřít()

Tento program vloží obrázek do databáze studentů, kterou jste vytvořili. Uvidíte následující výstup.

Ve výše uvedeném programu jsme soubor otevřeli v binárním režimu a přečetli každý bajt a uložili jej do proměnné data. Potom použijeme tuto proměnnou v příkazu INSERT k vložení obrázku do databáze.

Načítání obrázku

Chcete-li načíst obrázek z databáze, načtěte řádek pomocí příkazu select a poté otevřete binární data obrázku do proměnné pythonu, která bude uložena v souboru obrázku. Pro ilustraci viz následující kód.

importovat sqlite3 conn = sqlite3.připojit ("ukázka.db ") spoj.text_factory = str print ("\ n [+] Úspěšně připojeno k databázi") cur = conn.cursor () print ("\ n [+] Kurzor byl úspěšně nastaven") print ("\ n [+] Načtení obrázku") cur.execute ("SELECT * FROM student") ret = cur.fetchall () pro i v ret: data = i [2] s open ("img2.png "," wb ") jako soubor: soubor.write (data) print ("\ n [+] Obrázek byl uložen") cur.zavřít () připojit.commit () conn.zavřít()

Tento jednoduchý program načte obrázek z databáze a uloží jej na disk s názvem img2.png. Můžete také zvolit jiný název obrazového souboru. Výstup programu je uveden níže.

Seznam všech tabulek databáze

V databázi můžeme vytvořit velké množství tabulek. Existuje tedy také potřeba vypsat všechny tabulky v databázi. Chcete-li vypsat tabulky přítomné v databázi, dotazujte se na tabulku sqlite_master pomocí příkazu SELECT jazyka SQL. Syntaxe dotazu bude:

VYBRAT jméno FROM sqlite_master WHERE type = "table"

Zde je způsob, jakým pomocí tohoto dotazu vypsáme všechny tabulky v naší databázi.

import sqlite3 conn = sqlite3.připojit ("ukázka.db ") print (" \ n [+] Úspěšně připojeno k databázi ") cur = conn.cursor () print ("\ n [+] Kurzor byl úspěšně nastaven") cur.vykonat ("VYBRAT jméno z sqlite_master kde type =" tabulka "") řádky = cur.fetchall () tisk (řádky) cur.zavřít () připojit.commit () conn.zavřít()

Výše uvedený kód zobrazí seznam všech tabulek v naší databázi. Výstup vytvořený kódem při spuštění je následující. Může se zobrazit nějaký další výstup v závislosti na tabulkách, které jste vytvořili v databázi.

Identifikace celkových změn od připojení k databázi

V každé situaci je užitečné zjistit počet řádků, které byly od připojení databáze upraveny, vloženy nebo odstraněny. K tomu použijte celkem_změny () metoda objektu připojení, která vrátí celkový počet řádků databáze, které byly ovlivněny od připojení. Podívejme se na ukázku, abychom věděli, jak to funguje.

import sqlite3 conn = sqlite3.připojit ("ukázka.db ") spoj.text_factory = str print ("\ n [+] Úspěšně připojeno k databázi") cur = conn.cursor () print ("\ n [+] Oba kurzor byl úspěšně nastaven") cur.execute ("INSERT INTO student (id, name, images, marks) VALUES (140, 'David',", 99) ") cur.provést ("VLOŽIT DO studenta (id, jméno, obrázky, značky) HODNOTY (150, 'Sam',", 97) ") changes = conn.total_changes print ("\ n [+] Celkový počet změn v řádcích je:", changes) conn.commit () cur.zavřít () připojit.zavřít()

Výše uvedený program vytiskne počet změn řádků v aktuálním připojení. Uvidíte následující výstup.

Vrátit zpět v SQLite

Pokud jde o zrušení některých úkolů, můžete použít funkci rollback (). Tuto metodu lze použít k vrácení úlohy, která byla provedena po posledním odevzdání. Pro ilustraci viz níže uvedený příklad.

import sqlite3 conn = sqlite3.připojit ("ukázka.db ") spoj.text_factory = str print ("\ n [+] Úspěšně připojeno k databázi") cur = conn.cursor () print ("\ n [+] Oba kurzor byl úspěšně nastaven") cur.execute ("INSERT INTO student (id, name, images, marks) VALUES (100001, 'David',", 99) ") cur.execute ("INSERT INTO student (id, name, images, marks) VALUES (100002, 'Sam',", 97) ") conn.commit () print ("\ n [+] Řádek Two byl úspěšně vložen") cur.nejprve provést („VYBRAT * OD studenta“) = cur.fetchall () print ("\ n [+] Nové záznamy v databázi jsou:") pro i v prvním: print (i) cur.execute ("INSERT INTO student (id, name, images, marks) VALUES (10003, 'Kishan',", 100) ") cur.execute ("INSERT INTO student (id, name, images, marks) VALUES (10004, 'Ankit',", 100) ") print (" \ n [+] Řádek Two byl úspěšně vložen, ale nebyl potvrzen ") conn.rollback () print ("\ n [+] Vrátili jsme předchozí příkazy, takže nová data nebudou vložena") conn.commit () cur.execute ("SELECT * FROM student") sekunda = cur.fetchall () print ("\ n [+] Nové záznamy v databázi jsou:") pro i ve druhém: print (i) cur.zavřít () připojit.zavřít()

Ve výše uvedeném příkladu první dva příkazy vložení vloží data tak, jak jsou uvedena, ale poslední dva příkazy vložení budou vráceny zpět, takže do tabulky nepřidají žádná data. Výstup bude uveden níže.

Zálohujte databázi

Při práci s databází je nezbytné provést zálohu databáze. Modul sqlite3 poskytuje funkci pro zálohování databáze. Pomocí metody backup () objektu připojení můžeme provést zálohu databáze SQLite. Základní syntaxe metody zálohování je:

záloha (cíl, *, stránky = 0, pokrok = žádný, jméno = "hlavní", spánek = 0.250)

Ve výchozím nastavení nebo kdy stránky jsou buď 0 nebo záporné celé číslo, celá databáze se zkopíruje v jednom kroku, což je výhodné pro malou databázi; v opačném případě metoda provede kopírování smyčky až do stránky v době, kdy by to bylo možné s rozsáhlou databází. The název Argument zobrazuje název databáze, která bude zkopírována: musí to být řetězec obsahující buď výchozí, k označení hlavní databáze, nebo k označení dočasné databáze. The spát argument určuje čas v sekundách do spánku mezi pokusy o zálohu zbývajících stránek. Může to být celé číslo nebo hodnota s plovoucí desetinnou čárkou.

Vezměme si zálohu databáze.db databázi, kterou jsme v tutoriálu používali.

importovat sqlite3 conn_main = sqlite3.připojit ("ukázka.db ") conn_backup = sqlite3.připojit ("sample_backup.db ") print (" \ n [+] Úspěšně připojeno k databázi ") cur_main = conn_main.cursor () cur_backup = conn_backup.cursor () print ("\ n [+] Oba kurzor byl úspěšně nastaven") conn_main.backup (conn_backup, pages = 0, progress = None, name = "main") print ("Databáze byla úspěšně zálohována") cur_main.close () cur_backup.close () conn_main.commit () conn_backup.commit () conn_main.close () conn_backup.zavřít()

Ve výše uvedeném kódu jsou obě databáze propojeny, jedna je databáze, kterou chceme vytvořit zálohu, a druhá je databáze, ve které provedeme zálohu. Použijte záloha() metoda prvního objektu připojení k databázi k vytvoření zálohy. Tato funkce přijímá objekt připojení druhé databáze jako cíl k vytvoření zálohy v jiné databázi. Použijte stránky = 0 argumentů, takže proces bude probíhat v jednom kroku, což je doporučeno pro malé databáze. Tento program vytvoří nový vzorek názvu databáze_záloha.db a naplňte ji zálohou první databáze. Můžete vidět, že v aktuální složce byla vytvořena nová databáze se stejnou velikostí souboru jako předchozí.

Vypsání databáze SQLite

Dumping databází je důležitý úkol. Soubor výpisu je obvykle sada příkazů SQL pro data, která se obecně používá pro zálohování. Můžeme vypsat databázi pomocí metody dump (). V následujícím příkladu zjistíte, jak zrušit databázi SQLite.

importovat sqlite3 con = sqlite3.připojit ("databáze.db ") s otevřeným ('dump.sql ',' w ') jako f: pro řádek v kon.iterdump (): f.write ('% s \ n'% line)

Výše uvedený program vypíše ukázku databáze.db a uloží uložená data do souboru s názvem dump.sql. Můžete vidět data přítomná v adresáři, kde jsou aktuální soubory pythonu, a otevřít je pomocí libovolného textového editoru.

metoda execemany () SQLite3

The vykonat mnoho () metoda provede příkaz SQL proti všem sekvencím parametrů nebo mapování nalezeným v sekvenci seq_of_parameters. Pro zjednodušení lze tuto metodu použít k provedení většiny příkazů SQL v jednom řádku. E.G., Pomocí tohoto příkazu můžeme do seznamu pythonů vložit libovolný počet řádků. Pro ilustraci viz níže uvedený příklad.

import sqlite3 conn = sqlite3.připojit ("ukázka.db ") print (" \ n [+] Úspěšně připojeno k databázi ") cur = conn.cursor () print ("\ n [+] Kurzor byl úspěšně nastaven") python_list = [(10000000, 'vivek', ", '10'), (100000001, 'rose',", '21'), (100000002, 'robin', ", '31'), (100000003, 'Dev',", '4'), (100000004, 'michael', ", '52')] cur.executemany ("VLOŽTE DO studenta (ID, jméno, obrázky, značky) HODNOTY (?,?,?,?) ", python_list) print (" \ n [+] Všechna data byla úspěšně vložena ") cur.zavřít () připojit.commit () conn.zavřít()

Výše uvedený program vloží všechna data uvedená v seznamu pythonu. Níže je uveden výstup produkce programem.

Odstranit záznamy z tabulky

Můžeme použít operaci DELETE k odstranění záznamů z tabulky. Můžeme rychle odebrat řádek využívající operaci DELETE s klauzulí WHERE. Základní syntaxe příkazu DELETE je:

DELETE from table_name WHERE some_condition;

Podívejme se na příklad. Řádek s ID 1001 odstraníme z tabulky zaměstnanců naší databáze.

import sqlite3 conn = sqlite3.připojit ("ukázka.db ") spoj.text_factory = str print ("\ n [+] Úspěšně připojeno k databázi") cur = conn.cursor () print ("\ n [+] Kurzor byl úspěšně nastaven") cur.execute ("DELETE FROM student WHERE id = 1001") print ("\ n [+] Řádek byl úspěšně smazán") cur.execute ("SELECT * FROM student") data = cur.fetchall () pro řádek v datech: print (řádek) cur.zavřít () připojit.commit () conn.zavřít()

Výše uvedený kód odstraní řádek s ID 1001. Z návratu příkazu SELECT můžete vidět, že řádek byl odebrán. Výstup programu je uveden níže.

Zrušte stůl

Můžeme rychle zrušit nebo odstranit tabulku pomocí příkazu SQLite DROP. Syntaxe příkazu DROP je uvedena níže:

DROP tabulka název_tabulky 

Pokud tabulka neexistuje, pak SQLite vyvolá chybu, abychom tomu zabránili, můžeme použít pokud existují tag s příkazem DROP. Viz níže uvedená syntaxe:

DROP tabulka, pokud existuje název_tabulky

Podívejme se, jak můžeme tento příkaz použít s pythonem sqlite3 modul k odstranění tabulky. V tomto programu odstraníme student tabulku, kterou jsme vytvořili dříve.

import sqlite3 conn = sqlite3.připojit ("ukázka.db ") spoj.text_factory = str print ("\ n [+] Úspěšně připojeno k databázi") cur = conn.cursor () print ("\ n [+] Kurzor byl úspěšně nastaven") cur.execute ("DROP TABLE IF EXISTS student") print ("\ n [+] Tabulka byla úspěšně zrušena") cur.zavřít () připojit.commit () conn.zavřít()

Výše uvedený program odstraní tabulku student z vzorek databáze. Můžeme použít příkaz list table, který jsme viděli dříve, abychom zjistili, zda je tabulka odstraněna. Výstup programu je uveden níže.

Výjimky databáze SQLite

Některé výjimky z databáze SQLite mohou být vyvolány kvůli nějaké chybě. Podívejme se trochu, kdy byly tyto chyby vyvolány.

• sqlite3.Varování: Je to podtřída výjimky. Tato výjimka ukazuje některá varování, která lze v mnoha případech ignorovat.
• sqlite3.Chyba: Je to také podtřída výjimky. Je to základní třída všech ostatních výjimek v sqlite3 modul.
• sqlite3.DatabaseError: Toto jsou chyby, které jsou vyvolány kvůli některým chybám v databázích. Například: - Pokud se pokusíme připojit k zašifrované databázi nebo k nesprávnému databázovému souboru, zobrazí se DatabaseError, že data jsou šifrovaná, nebo ne platný databázový soubor.
• sqlite3.IntegrityError: Tato výjimka je podtřídou DatabaseError. Tuto výjimku získáme, když je ovlivněna relační integrita databáze, např.G., Selhání při kontrole cizího klíče.
• sqlite3.ProgrammingError: Tato výjimka je také podtřídou DatabaseError. Tato výjimka je vyvolána kvůli programovacím chybám, např.G., vytvoření tabulky se stejným názvem, která již existuje, syntaktická chyba v dotazech SQL atd.
• sqlite3.OperationalError: Je to také podtřída DatabaseError. Tato výjimka vyvolaná pro chyby, které souvisejí s provozem databáze a nejsou v naší kontrole. Například náhodné odpojení od systému, serveru vypnuto, dojde k vypršení časového limitu, problémy se zdroji dat, vypnutí stroje atd.
• sqlite3.NotSupportedError: Tato výjimka se vyvolá, když databáze nepodporuje použité databázové API.

Toto je seznam všech výjimek SQLite; můžeme tyto výjimky zpracovat v našich programech pomocí základní metody pokusu / kromě zpracování chyb v Pythonu.

Závěr

Tím se dostáváme na konec komplexního průvodce pokročilými metodami práce s SQLite pomocí Pythonu. Doufám, že jste se naučili všechny aspekty SQLite3 pomocí Pythonu, což nám pomůže vybudovat fantastické projekty v Pythonu.