Obvladovanje baze podatkov SQLite v Pythonu

SQLite je sistem za upravljanje relacijske baze podatkov, ki temelji na jeziku SQL; gre za strežniški mehanizem baz podatkov z ničelno konfiguracijo. Je eden najbolj priljubljenih mehanizmov za zbirke podatkov in je zelo enostaven za uporabo v majhnih aplikacijah. Ustvari samo eno diskovno datoteko za shranjevanje celotne baze podatkov, zaradi česar je datoteka prenosna. V OS Android se uporablja kot glavni vir za shranjevanje podatkov. Google Chrome ga uporablja tudi za shranjevanje podatkov o spletnih mestih in uporabniških podatkih, vključno z gesli v lokalnem računalniku.

Napredno delo z bazo podatkov SQLite v Pythonu

V tej vadnici bodo obravnavane teme: vstavljanje slik v tabelo SQLite, naštevanje tabel v zbirki podatkov, ugotavljanje skupnih sprememb, odkar je baza podatkov povezana, varnostno kopiranje baze podatkov, odlaganje baze podatkov SQLite, vrnitev v SQLite, brisanje zapisi iz tabele, Spuščanje tabele in izjeme baze podatkov SQLite.

Morda boste želeli videti tudi prvi del te vadnice, ki predstavlja osnove SQLite, prednosti njegove uporabe, povezava z datoteko baze podatkov, ustvarjanje tabele v bazi podatkov, vstavljanje podatkov v tabelo, poizvedovanje po podatkih iz tabele, posodabljanje tabele in še veliko več.

Datoteke in slike v zbirki podatkov SQLite

Med delom z bazami podatkov lahko v bazo vstavite slike ali datoteke ali izvozite iz nje. Če na primer ustvarjate bazo podatkov za shranjevanje podatkov o zaposlenih, boste morda morali v bazo vstaviti tudi slike vsakega zaposlenega.

Za dodajanje slik v zbirko podatkov SQLite moramo uporabiti podatkovni tip BLOB SQLite. Podatkovni tip BLOB () se uporablja za shranjevanje velikih predmetov, običajno velikih datotek, kot so slike, glasba, videoposnetki, dokumenti, PDF itd. Prvi korak je pretvorba podatkov in slik v bajtni objekt Pythona, ki je podoben podatkovnemu tipu BLOB SQLite. Pred nadaljevanjem ustvarite tabelo z imenom študent v zbirki podatkov s polji id, ime, slike, oznake. Za izdelavo tabele zaženite naslednjo kodo.

uvozi sqlite3 conn = sqlite3.poveži ("vzorec.db ") print (" \ n [+] Uspešno povezan z bazo podatkov ") cur = conn.cursor () print ("\ n [+] Kazalec je bil uspešno nastavljen") tabela = cur.execute ("" "USTVARI ŠTUDENT (ID INT PRIMARNI KLJUČ, ime BESEDILO, slike BLOB, oznake TEXT);" "") print ("\ n [+] Tabela je bila uspešno ustvarjena") cur.close () conn.commit () conn.zapri ()

Ta program bo ustvaril novo tabelo z imenom študent. V terminalu boste videli naslednji izhod.

Vstavljanje slike

Če želite vstaviti sliko v bazo podatkov SQLite, jo pretvorite v bajtni objekt python in jo nato vstavite v stolpec images, ki sprejme BLOB podatke. Zaženite naslednjo kodo, da dodate sliko img.png v zbirki podatkov z uporabo Pythona.

uvozi sqlite3 conn = sqlite3.poveži ("vzorec.db ") prik.text_factory = str print ("\ n [+] Uspešno povezan z bazo podatkov") cur = conn.cursor () print ("\ n [+] Kazalec je bil uspešno nastavljen") z odprtim ("img.png "," rb ") kot datoteka: podatki = datoteka.read () python_tuple = (101, "robin", data, "90") print ("\ n [+] Slika je bila uspešno uvožena") print ("\ n [+] Zdaj vstavljanje v bazo podatkov") cur.execute ("INSERT INTO student (id, name, images, marks) VALUES (?,?,?,?) ", python_tuple) print (" \ n [+] Podatki so bili uspešno vstavljeni ") cur.close () conn.commit () conn.zapri ()

Ta program bo sliko vstavil v podatkovno bazo študentov, ki ste jo ustvarili. Videli boste naslednji izhod.

V zgornjem programu smo datoteko odprli v binarnem načinu in prebrali vsak bajt ter ga shranili v spremenljivko podatkov. Nato s to spremenljivko v stavku INSERT vstavimo sliko v bazo podatkov.

Pridobivanje slike

Če želite sliko pridobiti iz zbirke podatkov, z vrstico select izberite vrstico in nato dostopite do binarnih podatkov slike v spremenljivko python, ki bo shranjena v slikovni datoteki. Za ponazoritev glejte naslednjo kodo.

uvozi sqlite3 conn = sqlite3.poveži ("vzorec.db ") prik.text_factory = str print ("\ n [+] Uspešno povezan z bazo podatkov") cur = conn.cursor () print ("\ n [+] Kazalec je bil uspešno nastavljen") print ("\ n [+] Pridobivanje slike") cur.izvrši ("SELECT * FROM student") ret = cur.fetchall () za i in ret: data = i [2] z odprtim ("img2.png "," wb ") kot datoteka: datoteka.zapiši (podatke) natisni ("\ n [+] Slika je shranjena") cur.close () conn.commit () conn.zapri ()

Ta preprost program bo sliko pridobil iz baze podatkov in jo shranil na disk z imenom img2.png. Za slikovno datoteko lahko izberete tudi drugo ime. Rezultat programa je prikazan spodaj.

Naštejte vse tabele zbirke podatkov

V zbirki podatkov lahko ustvarimo veliko število tabel. Torej je treba navesti tudi vse tabele, ki so prisotne v zbirki podatkov. Če želite navesti tabele, ki so prisotne v zbirki podatkov, poizvedite tabelo sqlite_master z uporabo stavka SELECT SQL. Sintaksa poizvedbe bo:

IZBERI ime FROM sqlite_master WHERE type = "table"

Tukaj je opisano, kako uporabljamo to poizvedbo za seznam vseh tabel v naši bazi podatkov.

uvozi sqlite3 conn = sqlite3.poveži ("vzorec.db ") print (" \ n [+] Uspešno povezan z bazo podatkov ") cur = conn.kurzor () print ("\ n [+] Kazalec je bil uspešno nastavljen").izvrši ("IZBERI ime iz sqlite_master, kjer je vrsta =" tabela "") vrstice = ukaz.fetchall () print (vrstice) cur.close () conn.commit () conn.zapri ()

Zgornja koda bo vsebovala vse tabele v naši bazi podatkov. Rezultat, ki ga ustvari koda, ko se izvede, je naslednji. Morda boste videli kakšen drug izhod, odvisno od tabel, ki ste jih ustvarili v zbirki podatkov.

Ugotovitev skupnih sprememb, odkar smo povezani z bazo podatkov

V vsakem primeru je koristno določiti število vrstic, ki so bile spremenjene, vstavljene ali izbrisane, odkar je bila baza podatkov povezana. Za to uporabite total_changes () metoda predmeta povezave, ki vrne skupno število vrstic baze podatkov, ki so bile prizadete od povezave. Oglejmo si primer predstavitve, da bomo vedeli, kako deluje.

uvozi sqlite3 conn = sqlite3.poveži ("vzorec.db ") prik.text_factory = str print ("\ n [+] Uspešno povezan z bazo podatkov") cur = conn.cursor () print ("\ n [+] Oba kurzorja sta bila uspešno nastavljena") cur.execute ("INSERT INTO student (id, name, images, marks) VALUES (140, 'David',", 99) ") cur.Izvedi ("INSERT INTO student (id, name, images, marks) VALUES (150, 'Sam',", 97) ") spremembe = conn.total_changes print ("\ n [+] Skupno število sprememb vrstic je:", spremembe) conn.commit () cur.close () conn.zapri ()

Zgornji program bo natisnil število sprememb vrstic v trenutni povezavi. Videli boste naslednji izhod.

Vrnitev v SQLite

Ko gre za razveljavitev nekaterih nalog, lahko uporabite funkcijo vračanja (). S to metodo lahko razveljavite nalogo, ki je bila opravljena po zadnji odobritvi. Za ponazoritev glejte spodnji primer.

uvozi sqlite3 conn = sqlite3.poveži ("vzorec.db ") prik.text_factory = str print ("\ n [+] Uspešno povezan z bazo podatkov") cur = conn.cursor () print ("\ n [+] Oba kurzorja sta bila uspešno nastavljena") cur.Izvedi ("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 [+] Dve vrstici sta bili uspešno vstavljeni") cur.izvršiti ("SELECT * FROM student") first = cur.fetchall () print ("\ n [+] Novi zapisi v zbirki podatkov so:") za i in first: print (i) cur.Izvedi ("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 [+] Dve vrstici sta bili uspešno vstavljeni, vendar ne zavezani ").rollback () print ("\ n [+] Preklicali smo prejšnje ukaze, tako da novi podatki ne bodo vstavljeni") conn.commit () cur.izvrši ("SELECT * FROM student") second = cur.fetchall () print ("\ n [+] Novi zapisi v bazi podatkov so:") za i v drugem: print (i) cur.close () conn.zapri ()

V zgornjem primeru bosta prva dva stavka za vstavljanje vstavila podatke, kot sta podana, zadnja dva stavka za vstavljanje pa bosta vrnjena nazaj, tako da ne bosta dodala nobenih podatkov v tabelo. Rezultat bo prikazan spodaj.

Varnostno kopirajte bazo podatkov

Med delom z bazo podatkov je bistveno izdelati varnostno kopijo baze podatkov. Modul sqlite3 ponuja funkcijo za varnostno kopiranje baze podatkov. Z uporabo metode backup () objekta povezave lahko naredimo varnostno kopijo baze podatkov SQLite. Osnovna sintaksa metode varnostnega kopiranja je:

varnostna kopija (target, *, pages = 0, progress = None, name = "main", sleep = 0.250)

Privzeto ali kdaj strani so bodisi 0 ali negativno celo število, se celotna baza podatkov kopira v enem koraku, kar je priporočljivo za majhno bazo podatkov; v nasprotnem primeru metoda izvede kopiranje zanke do strani v času, ki bi ga lahko storili z obsežno bazo podatkov. The ime Argument prikazuje ime baze podatkov, ki bo kopirano: to mora biti niz, ki vsebuje bodisi privzeto, bodisi glavno bazo podatkov bodisi začasno zbirko podatkov. The spanje argument določa čas v sekundah za spanje med poskusi varnostnega kopiranja preostalih strani. Lahko je celo število ali vrednost s plavajočo vejico.

Vzemimo varnostno kopijo zbirka podatkov.db zbirko podatkov, ki smo jo uporabljali v vadnici.

uvozi sqlite3 conn_main = sqlite3.poveži ("vzorec.db ") conn_backup = sqlite3.poveži ("sample_backup.db ") print (" \ n [+] Uspešno povezan z obema bazama podatkov ") cur_main = conn_main.cursor () cur_backup = conn_backup.cursor () print ("\ n [+] Oba kurzorja sta bila uspešno nastavljena") conn_main.varnostna kopija (conn_backup, pages = 0, progress = None, name = "main") print ("Baza podatkov je bila varnostno kopirana uspešno") cur_main.zapri () cur_backup.close () conn_main.commit () conn_backup.commit () conn_main.close () conn_backup.zapri ()

V zgornji kodi sta zbirki podatkov povezani, ena je baza podatkov, za katero želimo narediti varnostno kopijo, druga pa baza podatkov, v katero bomo vzeli varnostno kopijo. Uporabi rezerva() metoda prvega predmeta povezave z bazo podatkov za izdelavo varnostne kopije. Ta funkcija kot cilj sprejme objekt povezave druge baze podatkov, da ustvari varnostno kopijo v drugi bazi podatkov. Uporabi strani = 0 argumentov, tako bo postopek potekal v enem koraku, kar je priporočljivo za majhne zbirke podatkov. Ta program bo ustvaril nov vzorec imena baze podatkov_rezerva.db in ga napolnite z varnostno kopijo prve baze podatkov. Morda boste videli, da je v trenutni mapi ustvarjena nova baza podatkov z enako velikostjo datoteke kot prejšnja.

Odmetavanje baze podatkov SQLite

Odmetavanje baz podatkov je pomembna naloga. Dump datoteka je običajno niz stavkov SQL za podatke, ki se običajno uporabljajo za varnostno kopiranje. Zbirko podatkov lahko izvažamo z uporabo metode dump (). Oglejte si spodnji primer, če želite vedeti, kako spustiti bazo podatkov SQLite.

uvozi sqlite3 con = sqlite3.connect ("zbirka podatkov.db ") z odprtim ('dump.sql ',' w ') kot f: za vrstico v con.iterdump (): f.pisanje ('% s \ n'% vrstica)

Zgornji program bo izpisal vzorec baze podatkov.db, shranjene podatke pa bo shranil v datoteko z imenom dump.sql. Podatke, ki so prisotni v imeniku, v katerem so datoteke python, si lahko ogledate in jih odprete s katerim koli urejevalnikom besedil.

metoda executemany () SQLite3

The izvršilno () metoda izvrši ukaz SQL proti vsem zaporedjem parametrov ali preslikavam, ki jih najdemo v zaporedju seq_of_parameters. Zaradi enostavnosti lahko to metodo uporabimo za izvajanje večine ukazov SQL v eni vrstici. E.g., S pomočjo tega ukaza lahko skozi seznam python vstavimo poljubno število vrstic. Za ilustracijo glejte spodnji primer.

uvozi sqlite3 conn = sqlite3.poveži ("vzorec.db ") print (" \ n [+] Uspešno povezan z bazo podatkov ") cur = conn.cursor () print ("\ n [+] Kazalec je bil uspešno nastavljen") python_list = [(10000000, 'vivek', ", '10'), (100000001, 'rose',", '21'), (100000002, 'robin', ", '31'), (100000003, 'Dev',", '4'), (100000004, 'michael', ", '52')] cur.executemany ("INSERT INTO student (id, name, images, marks) VALUES (?,?,?,?) ", python_list) print (" \ n [+] Vsi podatki so bili uspešno vstavljeni ").close () conn.commit () conn.zapri ()

Zgornji program bo vstavil vse podatke na seznamu python. Rezultat, ki ga ustvari program, je prikazan spodaj.

Izbriši zapise iz tabele

Z operacijo DELETE lahko izbrišemo zapise iz tabele. Vrstico lahko hitro odstranimo z uporabo DELETE s stavkom WHERE. Osnovna sintaksa stavka DELETE je:

IZBRIŠI iz ime_tabele KJE neki_pogoj;

Poglejmo primer. Vrstico z id 1001 bomo izbrisali iz tabele zaposlenih v naši bazi podatkov.

uvozi sqlite3 conn = sqlite3.poveži ("vzorec.db ") prik.text_factory = str print ("\ n [+] Uspešno povezan z bazo podatkov") cur = conn.kurzor () print ("\ n [+] Kazalec je bil uspešno nastavljen").Izvedi ("IZBRIŠI ŠTUDENT KJE ID = 1001") print ("\ n [+] Vrstica je bila uspešno izbrisana") cur.izvršiti ("SELECT * FROM student") podatki = cur.fetchall () za vrstico v podatkih: print (vrstica) cur.close () conn.commit () conn.zapri ()

Zgornja koda bo izbrisala vrstico z ID 1001. Iz vrnitve stavka SELECT lahko vidite, da je bila vrstica odstranjena. Rezultat programa je prikazan spodaj.

Spustite mizo

Tabelo lahko hitro spustimo ali izbrišemo z uporabo stavka SQLite DROP. Sintaksa stavka DROP je prikazana spodaj:

DROP tabela ime_tabele

Če tabela ne obstaja, bo SQLite pripravil napako, zato lahko to preprečimo s pomočjo če obstajajo z izjavo DROP. Glejte spodnjo sintakso:

DROP tabela, če obstaja ime_tabele

Poglejmo, kako lahko uporabimo to izjavo s pythonom sqlite3 modul za brisanje tabele. V tem programu bomo odstranili študent tabelo, ki smo jo ustvarili prej.

uvozi sqlite3 conn = sqlite3.poveži ("vzorec.db ") prik.text_factory = str print ("\ n [+] Uspešno povezan z bazo podatkov") cur = conn.kurzor () print ("\ n [+] Kazalec je bil uspešno nastavljen").Izvedi ("DROP TABLE IF EXISTS student") print ("\ n [+] Tabela je bila uspešno spuščena") cur.close () conn.commit () conn.zapri ()

Zgornji program bo tabelo izbrisal študent Iz vzorec zbirka podatkov. Z ukazom seznam tabel, ki smo ga videli že prej, lahko preverimo, ali je tabela izbrisana. Rezultat programa je prikazan spodaj.

Izjeme zbirke podatkov SQLite

Zaradi nekaterih napak se lahko pojavijo nekatere izjeme v zbirki podatkov SQLite. Poglejmo malo, kdaj so se pojavile te napake.

sqlite3.Opozorilo: Gre za podrazred izjem. Ta izjema prikazuje nekaj opozoril, ki jih je v mnogih primerih mogoče prezreti.
sqlite3.Napaka: gre tudi za podrazred izjem. Je osnovni razred vseh drugih izjem v sqlite3 modul.
sqlite3.DatabaseError: To so napake, ki se pojavijo zaradi nekaterih napak v zbirkah podatkov. Na primer: - Če se poskušamo povezati s šifrirano bazo podatkov ali z napačno datoteko baze podatkov, bo prikazana napaka DatabaseError, da so podatki šifrirani ali ne veljavna datoteka baze podatkov.
sqlite3.IntegrityError: Ta izjema je podrazred DatabaseError. To izjemo bomo dobili, ko bo prizadeta relacijska celovitost baze podatkov, npr.g., Napaka pri preverjanju tujega ključa.
sqlite3.ProgrammingError: Ta izjema je tudi podrazred DatabaseError. Ta izjema se pojavlja zaradi programskih napak, npr.g., ustvarjanje tabele z enakim imenom, ki že obstaja, sintaksna napaka v poizvedbah SQL itd.
sqlite3.OperationalError: Je tudi podrazred DatabaseError. Ta izjema je nastala zaradi napak, ki so povezane z delovanjem baze podatkov in niso pod našim nadzorom. Na primer nenamerna prekinitev povezave s sistemom, izpad strežnika, časovna omejitev, težave z viri podatkov, izklop stroja itd.
sqlite3.NotSupportedError: Ta izjema se pojavi, če baza podatkov ne podpira API-ja baze podatkov, ki je bil uporabljen.

To je seznam vseh izjem SQLite; te izjeme lahko obvladamo v naših programih z uporabo osnovne metode Python za obdelavo napak / razen napak.

Zaključek

S tem smo na koncu izčrpnega vodnika o naprednih metodah dela z SQLite z uporabo Pythona. Upam, da ste se z Pythonom naučili vseh vidikov SQLite3, ki nam bodo pomagali zgraditi fantastične Pythonove projekte.