Zvládnutie databázy SQLite v Pythone

SQLite je systém správy relačných databáz založený na jazyku SQL; je to serverový server s nulovou konfiguráciou databázy. Je to jeden z najpopulárnejších databázových strojov a je veľmi ľahko použiteľný v malých aplikáciách. Vytvára iba jeden súbor na disku na uloženie celej databázy, vďaka čomu je súbor prenosný. Používa sa v systéme Android OS ako primárny zdroj na ukladanie údajov. Prehliadač Google Chrome ho tiež používa na ukladanie údajov o stránkach a používateľských údajov vrátane hesiel v miestnom počítači.

Pokročilá práca s databázou SQLite v Pythone

V tomto tutoriáli sa budeme venovať týmto témam: vkladanie obrázkov do tabuľky SQLite, zoznam tabuliek nachádzajúcich sa v databáze, identifikácia celkových zmien od pripojenia databázy, zálohovanie databázy, výpis databázy SQLite, vrátenie zmien v SQLite, odstránenie záznamy z tabuľky, zrušenie tabuľky a výnimky z databázy SQLite.

Môžete tiež vidieť prvú časť tohto tutoriálu, ktorý predstavuje základy SQLite, výhody jeho používania, pripojenie k databázovému súboru, vytvorenie tabuľky v databáze, vkladanie údajov do tabuľky, dopytovanie údajov z tabuľky, aktualizácia tabuľky a mnoho ďalších.

Súbory a obrázky v databáze SQLite

Pri práci s databázami existujú situácie, kedy musíte vložiť obrázky alebo súbory do databázy alebo z nej exportovať. Napríklad ak vytvárate databázu na ukladanie údajov o zamestnancoch, možno budete musieť vložiť aj obrázky všetkých zamestnancov v databáze.

Ak chcete pridať obrázky do databázy SQLite, musíme použiť dátový typ BLOB typu SQLite. Dátový typ BLOB () sa používa na ukladanie veľkých objektov, zvyčajne veľkých súborov, ako sú obrázky, hudba, videá, dokumenty, PDF atď. Prvým krokom je prevod údajov a obrázkov na bajtový objekt Pythonu, ktorý je podobný dátovému typu BLOB typu SQLite. Pred pokračovaním vytvorte tabuľku s názvom študent v databáze s poľami id, meno, obrázky, značky. Spustením nasledujúceho kódu vytvorte tabuľku.

importovať sqlite3 conn = sqlite3.pripojiť („ukážka.db ") print (" \ n [+] Úspešne pripojené k databáze ") cur = conn.cursor () print ("\ n [+] Kurzor bol úspešne nastavený") table = cur.execute ("" "VYTVORIŤ TABUĽKU študent (id INT PRIMÁRNY KLÍČ, názov TEXT, obrázky BLOB, značky TEXT);" "") print ("\ n [+] Tabuľka bola úspešne vytvorená") cur.zavrieť () spoj.potvrdiť () spoj.Zavrieť()

Tento program vytvorí novú tabuľku s názvom študent. V termináli uvidíte nasledujúci výstup.

Vkladanie obrázka

Ak chcete vložiť obrázok do databázy SQLite, transformujte obrázok v bajtovom objekte pythonu a potom ho vložte do stĺpca images, ktorý prijíma údaje BLOB. Spustite nasledujúci kód a pridajte obrázok obr.png v databáze pomocou Pythonu.

importovať sqlite3 conn = sqlite3.pripojiť („ukážka.db ") pripoj.text_factory = str print ("\ n [+] Úspešne pripojené k databáze") cur = conn.cursor () print ("\ n [+] Kurzor bol úspešne nastavený") s open ("obr.png "," rb ") ako súbor: data = súbor.read () python_tuple = (101, "robin", data, "90") print ("\ n [+] Obrázok bol úspešne importovaný") print ("\ n [+] Teraz sa vkladá do databázy") cur.vykonať („VLOŽIŤ DO ŽIAKA (id, meno, obrázky, známky) HODNOTY (?,?,?,?) ", python_tuple) print (" \ n [+] Údaje boli úspešne vložené ") cur.zavrieť () spoj.potvrdiť () spoj.Zavrieť()

Tento program vloží obrázok do databázy študentov, ktorú ste vytvorili. Uvidíte nasledujúci výstup.

Vo vyššie uvedenom programe sme otvorili súbor v binárnom režime a prečítali sme každý bajt a uložili ho do premennej údaje. Potom pomocou tejto premennej vo vyhlásení INSERT vložíme obrázok do databázy.

Načítanie obrázka

Ak chcete načítať obrázok z databázy, načítajte riadok pomocou príkazu select a potom pristupujte k binárnym údajom obrázka do premennej python, ktorá bude uložená v súbore obrázka. Pre ilustráciu pozri nasledujúci kód.

importovať sqlite3 conn = sqlite3.pripojiť („ukážka.db ") pripoj.text_factory = str print ("\ n [+] Úspešne pripojené k databáze") cur = conn.cursor () print ("\ n [+] Kurzor bol úspešne nastavený") print ("\ n [+] Načítanie obrázka") cur.execute ("SELECT * FROM student") ret = cur.fetchall () pre i v ret: data = i [2] s open ("img2.png "," wb ") ako súbor: súbor.write (data) print ("\ n [+] Obrázok bol uložený") cur.zavrieť () spoj.potvrdiť () spoj.Zavrieť()

Tento jednoduchý program načíta obraz z databázy a uloží ho na disk s názvom img2.png. Môžete tiež zvoliť iný názov obrazového súboru. Výstup programu je uvedený nižšie.

Zoznam všetkých tabuliek databázy

V databáze môžeme vytvoriť veľké množstvo tabuliek. Je teda tiež potrebné uviesť zoznam všetkých tabuliek nachádzajúcich sa v databáze. Ak chcete zobraziť zoznam tabuliek nachádzajúcich sa v databáze, dotazujte sa na tabuľku sqlite_master pomocou príkazu SELECT v jazyku SQL. Syntax dotazu bude:

VYBERTE meno FROM sqlite_master WHERE type = "table"

Týmto spôsobom použijeme tento dopyt na výpis všetkých tabuliek nachádzajúcich sa v našej databáze.

importovať sqlite3 conn = sqlite3.pripojiť („ukážka.db ") print (" \ n [+] Úspešne pripojené k databáze ") cur = conn.cursor () print ("\ n [+] Kurzor bol úspešne nastavený") cur.vykonať ("SELECT meno od sqlite_master kde type =" tabuľka "") riadky = cur.fetchall () tlač (riadky) pr.zavrieť () spoj.potvrdiť () spoj.Zavrieť()

Vyššie uvedený kód vypíše zoznam všetkých tabuliek nachádzajúcich sa v našej databáze. Výstup produkovaný kódom po vykonaní je nasledovný. V závislosti od tabuliek, ktoré ste vytvorili v databáze, môžete vidieť ďalší výstup.

Identifikácia celkových zmien od pripojenia k databáze

V každej situácii je užitočné zistiť počet riadkov, ktoré boli zmenené, vložené alebo odstránené po pripojení databázy. Na to použite total_changes () metóda objektu pripojenia, ktorá vráti celkový počet riadkov databázy, ktoré boli ovplyvnené od pripojenia. Pozrime sa na ukážku ukážky, aby sme vedeli, ako to funguje.

importovať sqlite3 conn = sqlite3.pripojiť („ukážka.db ") pripoj.text_factory = str print ("\ n [+] Úspešne pripojené k databáze") cur = conn.cursor () print ("\ n [+] Oba kurzory boli úspešne nastavené") cur.execute ("INSERT INTO student (id, name, images, marks) VALUES (140, 'David',", 99) ") cur.execute ("INSERT INTO student (id, name, images, brands) VALUES (150, 'Sam',", 97) ") changes = conn.total_changes print ("\ n [+] Celkový počet zmien v riadkoch je:", changes) conn.commit () cur.zavrieť () spoj.Zavrieť()

Vyššie uvedený program vytlačí počet zmien riadkov v aktuálnom pripojení. Uvidíte nasledujúci výstup.

Vrátenie zmien v SQLite

Ak chcete vrátiť späť niektoré úlohy, môžete použiť funkciu rollback (). Touto metódou je možné vrátiť späť úlohu vykonanú po poslednom potvrdení. Ilustráciu nájdete v nasledujúcom príklade.

importovať sqlite3 conn = sqlite3.pripojiť („ukážka.db ") pripoj.text_factory = str print ("\ n [+] Úspešne pripojené k databáze") cur = conn.cursor () print ("\ n [+] Oba kurzory boli úspešne nastavené").execute ("INSERT INTO student (id, name, images, brands) VALUES (100001, 'David',", 99) ") cur.execute ("INSERT INTO student (id, name, images, brands) VALUES (100002, 'Sam',", 97) ") conn.commit () print ("\ n [+] Riadok Two bol úspešne vložený") cur.najskôr vykonať ("VYBRAŤ * OD študenta") = cur.fetchall () print ("\ n [+] Nové záznamy v databáze sú:") pre i na prvom mieste: print (i) cur.execute ("INSERT INTO student (id, name, images, brands) VALUES (10003, 'Kishan',", 100) ") cur.execute ("INSERT INTO student (id, name, images, marks) VALUES (10004, 'Ankit',", 100) ") print (" \ n [+] Riadok Two bol úspešne vložený, ale nebol zadaný ") conn.rollback () print ("\ n [+] Vrátili sme späť predchádzajúce príkazy, takže sa nebudú vkladať nové údaje") conn.commit () cur.execute ("SELECT * FROM student") second = cur.fetchall () print ("\ n [+] Nové záznamy v databáze sú:") pre i v druhom rade: print (i) cur.zavrieť () spoj.Zavrieť()

Vo vyššie uvedenom príklade vložia prvé dva príkazy vloženia údaje tak, ako sú uvedené, ale posledné dva príkazy vloženia sa vrátia späť, takže do tabuľky nepridajú žiadne údaje. Výstup bude uvedený nižšie.

Zálohujte databázu

Pri práci s databázou je nevyhnutné vykonať jej zálohu. Modul sqlite3 poskytuje funkciu na zálohovanie databázy. Pomocou metódy backup () objektu pripojenia môžeme vytvoriť zálohu databázy SQLite. Základná syntax metódy zálohovania je:

zálohovanie (cieľ, *, stránky = 0, priebeh = žiadny, meno = "hlavný", spánok = 0.250)

Predvolene alebo kedy strán sú buď 0 alebo záporné celé číslo, celá databáza sa skopíruje v jednom kroku, čo je výhodnejšie pre malú databázu; inak metóda vykonáva kopírovanie slučky až do strán v čase, ktorý sa dal urobiť pomocou rozsiahlej databázy. The názov argument zobrazuje názov databázy, ktorá sa bude kopírovať: musí to byť reťazec obsahujúci buď predvolenú hodnotu, aby označil hlavnú databázu, alebo dočasnú databázu. The spať argument určuje čas v sekundách do spánku medzi pokusmi o zálohovanie zostávajúcich stránok. Môže to byť celé číslo alebo hodnota s pohyblivou rádovou čiarkou.

Zoberme si zálohu databázy.db databázy, ktorú sme používali v tomto návode.

importovať sqlite3 conn_main = sqlite3.pripojiť („ukážka.db ") conn_backup = sqlite3.pripojiť ("sample_backup.db ") print (" \ n [+] Úspešne pripojené k databáze ") cur_main = conn_main.cursor () cur_backup = conn_backup.cursor () print ("\ n [+] Oba kurzory boli úspešne nastavené") conn_main.backup (conn_backup, pages = 0, progress = None, name = "main") print ("Databáza bola úspešne zálohovaná") cur_main.close () cur_backup.close () conn_main.commit () conn_backup.commit () conn_main.close () conn_backup.Zavrieť()

Vo vyššie uvedenom kóde sú obe databázy prepojené, jednou je databáza, z ktorej chceme urobiť zálohu, a druhou je databáza, do ktorej zálohu vykonáme. Použi záloha () metóda prvého objektu databázového pripojenia na vytvorenie zálohy. Táto funkcia prijíma objekt pripojenia druhej databázy ako cieľ na vytvorenie zálohy v druhej databáze. Použi stránky = 0 argumentov, takže proces bude prebiehať v jednom kroku, ktorý sa odporúča pre malé databázy. Tento program vytvorí novú ukážku názvu databázy_zálohovanie.db a naplňte ju zálohou prvej databázy. Môžete vidieť, že v aktuálnom priečinku bola vytvorená nová databáza s rovnakou veľkosťou súboru ako predchádzajúca.

Vypisovanie databázy SQLite

Dumping databáz je dôležitá úloha. Súbor výpisu je zvyčajne sada príkazov SQL pre údaje, ktorá sa zvyčajne používa na zálohovanie. Môžeme vyložiť databázu pomocou metódy dump (). V nasledujúcom príklade sa dozviete, ako zrušiť databázu SQLite.

importovať sqlite3 con = sqlite3.pripojiť ("databáza.db ") s otvoreným ('výpisom.sql ',' w ') ako f: pre riadok v kon.iterdump (): f.zápis ('% s \ n'% riadok)

Vyššie uvedený program vypíše ukážku databázy.db a uloží uložené údaje do súboru s názvom výpis.štvorcový. Môžete vidieť údaje prítomné v adresári, kde sú aktuálne súbory pythonu, a otvoriť ich pomocou ľubovoľného textového editora.

metóda execemany () programu SQLite3

The vykonat vela () metóda vykoná príkaz SQL proti všetkým sekvenciám parametrov alebo mapovaniu nájdeným v sekvencii seq_of_parameters. Pre jednoduchosť je možné túto metódu použiť na vykonanie väčšiny príkazov SQL v jednom riadku. E.g., Pomocou tohto príkazu môžeme vložiť ľubovoľný počet riadkov do zoznamu pytónov. Ilustráciu nájdete v nasledujúcom príklade.

importovať sqlite3 conn = sqlite3.pripojiť („ukážka.db ") print (" \ n [+] Úspešne pripojené k databáze ") cur = conn.cursor () print ("\ n [+] Kurzor bol úspešne nastavený") python_list = [(10000000, 'vivek', ", '10'), (100000001, 'rose',", '21'), (100000002, 'robin', ", '31'), (100000003, 'Dev',", '4'), (100000004, 'michael', ", '52')] cur.execemany ("VLOŽTE DO ŽIAKA (id, meno, obrázky, známky) HODNOTY (?,?,?,?) ", python_list) print (" \ n [+] Všetky údaje boli úspešne vložené ") cur.zavrieť () spoj.potvrdiť () spoj.Zavrieť()

Vyššie uvedený program vloží všetky údaje uvedené v zozname pythónov. Produkcia výstupu z programu je uvedená nižšie.

Odstrániť záznamy z tabuľky

Na odstránenie záznamov z tabuľky môžeme použiť operáciu DELETE. Riadok môžeme rýchlo odstrániť pomocou operácie DELETE pomocou klauzuly WHERE. Základná syntax príkazu DELETE je:

ODSTRÁNIŤ z table_name WHERE some_condition;

Pozrime sa na príklad. Vymažeme riadok s id 1001 z tabuľky zamestnancov v našej databáze.

importovať sqlite3 conn = sqlite3.pripojiť („ukážka.db ") pripoj.text_factory = str print ("\ n [+] Úspešne pripojené k databáze") cur = conn.cursor () print ("\ n [+] Kurzor bol úspešne nastavený") cur.execute ("DELETE FROM student WHERE id = 1001") print ("\ n [+] Riadok bol úspešne vymazaný") cur.vykonať („VYBRAT * OD ŽIAKA“) data = cur.fetchall () pre riadok v dátach: print (riadok) cur.zavrieť () spoj.potvrdiť () spoj.Zavrieť()

Vyššie uvedený kód vymaže riadok s ID 1001. Z návratu príkazu SELECT vidíte, že riadok bol odstránený. Výstup programu je uvedený nižšie.

Odhoďte stôl

Tabuľku môžeme rýchlo zrušiť alebo vymazať pomocou príkazu SQLite DROP. Syntax príkazu DROP je uvedená nižšie:

DROP tabuľka table_name 

Ak tabuľka neexistuje, potom SQLite spôsobí chybu, aby sme tomu zabránili, môžeme použiť znak ak existujú značka s výpisom DROP. Pozrite si nižšie uvedenú syntax:

DROP tabuľka, ak existuje table_name

Pozrime sa, ako môžeme toto vyhlásenie použiť s pythonom sqlite3 modul na odstránenie tabuľky. V tomto programe odstránime študent tabuľku, ktorú sme vytvorili skôr.

importovať sqlite3 conn = sqlite3.pripojiť („ukážka.db ") pripoj.text_factory = str print ("\ n [+] Úspešne pripojené k databáze") cur = conn.cursor () print ("\ n [+] Kurzor bol úspešne nastavený") cur.execute ("DROP TABLE IF EXISTS student") print ("\ n [+] Tabuľka bola úspešne zrušená") cur.zavrieť () spoj.potvrdiť () spoj.Zavrieť()

Vyššie uvedený program vymaže tabuľku študent z vzorka databázy. Pomocou príkazu list table, ktorý sme videli už skôr, môžeme zistiť, či je tabuľka vymazaná. Výstup programu je uvedený nižšie.

Výnimky databázy SQLite

Niektoré výnimky z databázy SQLite môžu byť vyvolané kvôli chybe. Pozrime sa trochu, kedy sa tieto chyby vyskytli.

• sqlite3.Upozornenie: Je to podtrieda výnimky. Táto výnimka zobrazuje niektoré varovania, ktoré je v mnohých prípadoch možné ignorovať.
• sqlite3.Chyba: Je to tiež podtrieda výnimky. Je to základná trieda všetkých ostatných výnimiek v sqlite3 modul.
• sqlite3.DatabaseError: Toto sú chyby, ktoré sa vyskytujú kvôli chybám v databázach. Napríklad: - Ak sa pokúsime pripojiť k zašifrovanej databáze alebo nesprávnemu databázovému súboru, zobrazí sa chyba DatabaseError, že údaje sú šifrované alebo nie sú platným databázovým súborom.
• sqlite3.IntegrityError: Táto výnimka je podtriedou DatabaseError. Túto výnimku dostaneme, keď bude ovplyvnená relačná integrita databázy, napr.g., Zlyhanie v kontrole cudzieho kľúča.
• sqlite3.ProgrammingError: Táto výnimka je tiež podtriedou DatabaseError. Táto výnimka je vyvolaná z dôvodu programovacích chýb, napr.g., vytvorenie tabuľky s rovnakým názvom, ktorá už existuje, syntaktická chyba v dotazoch SQL atď.
• sqlite3.OperationalError: Je to tiež podtrieda DatabaseError. Táto výnimka vyvolaná pre chyby, ktoré súvisia s prevádzkou databázy a nie sú pod našou kontrolou. Napríklad náhodné odpojenie od systému, servera, výpadku času, problémov so zdrojom údajov, vypnutia stroja atď.
• sqlite3.NotSupportedError: Táto výnimka sa vyvolá, keď databáza nepodporuje použité databázové API.

Toto je zoznam všetkých výnimiek SQLite; môžeme tieto výnimky vybaviť v našich programoch pomocou základnej metódy pokusu / okrem spracovania chýb v Pythone.

Záver

Týmto sa dostávame na koniec komplexného sprievodcu pokročilými metódami práce s SQLite pomocou Pythonu. Dúfam, že ste sa naučili všetky aspekty SQLite3 pomocou Pythonu, ktorý nám pomôže zostaviť fantastické projekty v Pythone.