Mestring av SQLite-databasen i Python

SQLite er et relasjonelt databasesystem basert på SQL-språket; det er en serverløs databasemotor uten nullkonfigurasjon. Det er en av de mest populære databasemotorene og veldig enkel å bruke i små applikasjoner. Den oppretter bare en diskfil for å lagre hele databasen, noe som gjør filen bærbar. Den brukes i Android OS som den primære kilden for å lagre data. Den brukes også av Google Chrome til å lagre nettsteddata og brukerdata, inkludert passord på den lokale maskinen.

Avansert arbeid med SQLite Database i Python

I denne opplæringen er emnene som dekkes: sette inn bilder i en SQLite-tabell, liste opp tabellene i en database, identifisere totale endringer siden databasen er koblet til, sikkerhetskopiere en database, dumpe en SQLite-database, tilbakeføre i SQLite, slette poster fra en tabell, slippe en tabell og SQLite-database unntak.

Det kan også være lurt å se den første delen av denne veiledningen, som presenterer grunnleggende om SQLite, fordeler med å bruke det, koble til en databasefil, lage en tabell i databasen, sette inn data i tabellen, spørre data fra tabellen, oppdatere tabellen og mange flere.

Filer og bilder i SQLite-databasen

Mens du arbeider med databaser, er det situasjoner der du trenger å sette inn bilder eller filer i en database eller eksportere fra den. For eksempel, hvis du oppretter en database for å lagre medarbeiderdata, kan det hende du også må sette inn bilder av hver ansatt i databasen.

For å legge til bilder i en SQLite-database, må vi bruke BLOB-datatypen SQLite. BLOB () datatypen brukes til å lagre store objekter, vanligvis store filer som bilder, musikk, videoer, dokumenter, PDF osv. Det første trinnet er å konvertere data og bilder til byteobjektet til Python, som ligner på BLOB-datatypen SQLite. Før du fortsetter, må du opprette en tabell med navnet student i databasen med feltene id, navn, bilder, merker. Kjør følgende kode for å lage tabellen.

importere sqlite3 conn = sqlite3.koble til ("prøve.db ") print (" \ n [+] Vellykket koblet til databasen ") cur = conn.markør () utskrift ("\ n [+] Markøren er satt opp vellykket") table = cur.execute ("" "CREATE TABLE student (id INT PRIMARY KEY, name TEXT, images BLOB, marks TEXT);" "") print ("\ n [+] Tabellen er opprettet med hell") cur.lukk () konn.begå () konn.Lukk()

Dette programmet vil opprette en ny tabell med navnet student. Du vil se følgende utgang i terminalen.

Sette inn et bilde

For å sette inn et bilde i en SQLite-database, transformer du bildet i et python-byteobjekt og sett det inn i bildekolonnen, som godtar BLOB-data. Kjør følgende kode for å legge til et bilde img.png i databasen ved hjelp av Python.

importere sqlite3 conn = sqlite3.koble til ("prøve.db ") konn.text_factory = str print ("\ n [+] Vellykket koblet til databasen") cur = conn.markør () utskrift ("\ n [+] Markøren er satt opp vellykket") med åpen ("bild.png "," rb ") som fil: data = fil.les () python_tuple = (101, "robin", data, "90") print ("\ n [+] Bildet er vellykket importert") print ("\ n [+] Settes nå inn i databasen") cur.utføre ("INSERT INTO student (id, name, images, marks) VALUES (?,?,?,?) ", python_tuple) print (" \ n [+] Dataene er satt inn vellykket ") cur.lukk () konn.begå () konn.Lukk()

Dette programmet vil sette inn bildet i studentdatabasen du opprettet. Du vil se følgende utdata.

I programmet ovenfor har vi åpnet filen i binærmodus og lest hver byte og lagret den i variabel data. Deretter bruker vi den variabelen i INSERT-setningen for å sette inn bildet i databasen.

Henter et bilde

Hvis du vil hente et bilde fra en database, henter du raden ved å velge en setning og får tilgang til bildets binære data til en pythonvariabel som lagres i en bildefil. Se følgende kode for illustrasjon.

importere sqlite3 conn = sqlite3.koble til ("prøve.db ") konn.text_factory = str print ("\ n [+] Vellykket koblet til databasen") cur = conn.markør () utskrift ("\ n [+] Markøren er satt opp vellykket") utskrift ("\ n [+] Henter bildet") cur.execute ("SELECT * FROM student") ret = cur.fetchall () for i i ret: data = i [2] med åpen ("img2.png "," wb ") som fil: fil.skriv (data) utskrift ("\ n [+] Bildet er lagret") cur.lukk () konn.begå () konn.Lukk()

Dette enkle programmet vil hente bildet fra databasen og lagre det på disken som heter img2.png. Du kan også velge et annet navn for bildefilen. Resultatet av programmet er vist nedenfor.

Liste opp alle tabellene i en database

I en database kan vi lage mange tabeller. Så det er også behov for å liste opp alle tabellene i en database. For å liste opp tabellene i en database, spør du sqlite_master-tabellen ved hjelp av SELECT-setningen til SQL. Syntaksen til spørringen vil være:

VELG navn FRA sqlite_master WHERE type = "tabell"

Slik bruker vi dette spørsmålet for å liste opp alle tabellene i databasen vår.

importere sqlite3 conn = sqlite3.koble til ("prøve.db ") print (" \ n [+] Vellykket koblet til databasen ") cur = conn.markør () utskrift ("\ n [+] Markøren er satt opp vellykket") cur.utfør ("VELG navn fra sqlite_master der type =" tabell "") rader = cur.fetchall () utskrift (rader) cur.lukk () konn.begå () konn.Lukk()

Ovennevnte kode vil liste opp alle tabellene i databasen vår. Utdata produsert av koden når den kjøres er som følger. Du kan se noen andre utdata, avhengig av tabellene du har opprettet i databasen.

Identifisere totale endringer siden de er koblet til databasen

I alle situasjoner er det nyttig å identifisere antall rader som er endret, satt inn eller slettet siden databasen er koblet til. For det, bruk total_changes () metode for tilkoblingsobjektet, som vil returnere det totale antallet databaserader som har blitt berørt siden tilkoblingen. La oss se et eksempel på en demo for å vite hvordan det fungerer.

importere sqlite3 conn = sqlite3.koble til ("prøve.db ") konn.text_factory = str print ("\ n [+] Vellykket koblet til både databasen") cur = conn.cursor () print ("\ n [+] Både markøren er satt opp vellykket") cur.utføre ("INSERT INTO student (id, name, images, marks) VALUES (140, 'David',", 99) ") cur.utfør ("INSERT INTO student (id, name, images, marks) VALUES (150, 'Sam',", 97) ") changes = conn.total_changes print ("\ n [+] Totalt nå med radendringer er:", endringer) konn.begå () cur.lukk () konn.Lukk()

Ovennevnte program vil skrive ut antall radendringer i den nåværende tilkoblingen. Du vil se følgende utdata.

Tilbakeslag i SQLite

Når det gjelder å angre noen oppgaver, kan du bruke tilbakestillingsfunksjonen (). Denne metoden kan brukes til å angre oppgaven som er utført etter siste forpliktelse. Se eksemplet nedenfor for en illustrasjon.

importere sqlite3 conn = sqlite3.koble til ("prøve.db ") konn.text_factory = str print ("\ n [+] Vellykket koblet til både databasen") cur = conn.cursor () print ("\ n [+] Både markøren er satt opp vellykket") cur.utfør ("INSERT INTO student (id, name, images, marks) VALUES (100001, 'David',", 99) ") cur.utføre ("INSERT INTO student (id, name, images, marks) VALUES (100002, 'Sam',", 97) ") Conn.commit () print ("\ n [+] To raden er satt inn vellykket") cur.utfør ("SELECT * FRA student") først = cur.fetchall () print ("\ n [+] De nye postene i databasen er:") for i i første: 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 [+] The Two row has been entered successfully but not committed ") conn.rollback () utskrift ("\ n [+] Vi har tilbakeført de forrige kommandoene slik at de nye dataene ikke blir satt inn").begå () cur.utfør ("SELECT * FROM student") andre = cur.fetchall () print ("\ n [+] De nye postene i databasen er:") for i i andre: print (i) cur.lukk () konn.Lukk()

I eksemplet ovenfor vil de to første innsettingsuttrykkene sette inn dataene som gitt, men de to siste innleggssetningene vil være tilbakevendende, slik at de ikke legger til noen data i tabellen. Resultatet blir som vist nedenfor.

Sikkerhetskopier en database

Mens du arbeider med databasen, er det viktig å ta en sikkerhetskopi av databasen. Sqlite3-modulen gir en funksjon for å ta sikkerhetskopi av databasen. Ved å bruke backup () -metoden til tilkoblingsobjektet kan vi ta sikkerhetskopi av SQLite-databasen. Den grunnleggende syntaksen for sikkerhetskopimetoden er:

sikkerhetskopi (mål, *, sider = 0, fremgang = ingen, navn = "hoved", søvn = 0.250)

Som standard, eller når sider er enten 0 eller et negativt heltall, kopieres hele databasen i ett enkelt trinn, som er å foretrekke for en liten database; Ellers utfører metoden en loopkopiering opp til sider på et tidspunkt som kunne gjøres med den omfattende databasen. De Navn argumentet viser databasenavnet som skal kopieres: det må være en streng som inneholder enten standard, for å indikere hoveddatabasen eller for å indikere den midlertidige databasen. De søvn argument spesifiserer tiden i sekunder for å sove mellom forsøk på å sikkerhetskopiere de gjenværende sidene. Det kan være enten som et heltall eller en flytende punktverdi.

La oss ta en sikkerhetskopi av database.db database vi har brukt i veiledningen.

importere sqlite3 conn_main = sqlite3.koble til ("prøve.db ") conn_backup = sqlite3.koble til ("sample_backup.db ") print (" \ n [+] Vellykket koblet til både databasen ") cur_main = conn_main.markør () cur_backup = conn_backup.markør () utskrift ("\ n [+] Begge markøren er konfigurert vellykket") conn_main.backup (conn_backup, pages = 0, progress = None, name = "main") print ("Databasen har blitt sikkerhetskopiert vellykket") cur_main.lukk () cur_backup.lukk () conn_main.begå () conn_backup.begå () conn_main.lukk () conn_backup.Lukk()

I koden ovenfor er de to databasene koblet sammen, den ene er databasen som vi vil ta sikkerhetskopi av, og den andre er databasen der vi tar sikkerhetskopien. Bruke sikkerhetskopi () metoden til det første databaseforbindelsesobjektet for å lage en sikkerhetskopi. Denne funksjonen godtar tilkoblingsobjektet til den andre databasen som målet for å lage en sikkerhetskopi på den andre databasen. Bruke sider = 0 argumenter, så prosessen vil finne sted i ett trinn, som anbefales for små databaser. Dette programmet vil opprette et nytt eksempel på databasenavn_sikkerhetskopi.db og fyll den med sikkerhetskopien av den første databasen. Det kan hende du ser at en ny database er opprettet i den gjeldende mappen med samme filstørrelse som den forrige.

Dumping av en SQLite-database

Dumping av databaser er en viktig oppgave. Vanligvis er en dumpfil et sett med SQL-setninger for dataene, som vanligvis brukes til sikkerhetskopiering. Vi kan dumpe en database ved hjelp av dump () -metoden. Se eksemplet nedenfor for å vite hvordan du slipper en SQLite-database.

importere sqlite3 con = sqlite3.koble til ("database.db ") med åpen ('dump.sql ',' w ') som f: for linje i kon.iterdump (): f.skriv ('% s \ n'% linje)

Ovennevnte program vil dumpe databaseprøven.db, og det vil lagre de dumpede dataene i en fil som heter dump.kvm. Du kan se dataene som er tilstede i katalogen der pythonfilene er gjeldende, og åpne dem ved hjelp av hvilken som helst teksteditor.

executemany () metode for SQLite3

De mange () metoden utfører en SQL-kommando mot alle parametersekvenser eller tilordninger som finnes i sekvensen seq_of_parameters. For enkelhets skyld kan denne metoden brukes til å utføre de fleste SQL-kommandoer på en linje. E.g., Vi kan sette inn et hvilket som helst antall rader gjennom en pythonliste ved hjelp av denne kommandoen. Se eksemplet nedenfor for illustrasjonen.

importere sqlite3 conn = sqlite3.koble til ("prøve.db ") print (" \ n [+] Vellykket koblet til databasen ") cur = conn.markør () utskrift ("\ n [+] Markøren er satt opp vellykket") 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) VERDIER (?,?,?,?) ", python_list) print (" \ n [+] Alle dataene er satt inn vellykket ") cur.lukk () konn.begå () konn.Lukk()

Ovennevnte program vil sette inn alle dataene som er gitt i pythonlisten. Produksjonen produsert av programmet er vist nedenfor.

Slett poster fra en tabell

Vi kan bruke SLETT-operasjonen til å slette poster fra en tabell. Vi kan raskt fjerne en rad ved å bruke DELETE-operasjonen med WHERE-setningen. Den grunnleggende syntaksen for DELETE-setningen er:

SLETT fra tabellnavn WHERE noen_tilstand;

La oss se et eksempel. Vi vil slette raden med id 1001 fra arbeidstabellen i databasen vår.

importere sqlite3 conn = sqlite3.koble til ("prøve.db ") konn.text_factory = str print ("\ n [+] Vellykket koblet til databasen") cur = conn.markør () utskrift ("\ n [+] Markøren er satt opp vellykket") cur.utfør ("SLETT FRA student WHERE id = 1001") utskrift ("\ n [+] Raden er slettet vellykket") cur.execute ("SELECT * FROM student") data = cur.fetchall () for rad i data: skriv ut (rad) cur.lukk () konn.begå () konn.Lukk()

Ovennevnte kode vil slette raden med ID 1001. Du kan se fra retur av SELECT-setningen at raden er fjernet. Resultatet av programmet er som vist nedenfor.

Slipp et bord

Vi kan raskt droppe eller slette en tabell ved hjelp av SQLite DROP-setningen. Syntaksen til DROP-setningen er som vist nedenfor:

DROP tabell tabellnavn

Hvis tabellen ikke eksisterer, vil SQLite kaste en feil, så for å forhindre dette kan vi bruke hvis eksisterer tag med DROP-setningen. Se syntaksen nedenfor:

DROP-tabell hvis det finnes tabellnavn

La oss se hvordan vi kan bruke denne uttalelsen med python sqlite3 modul for å slette en tabell. I dette programmet vil vi fjerne student tabellen vi har laget tidligere.

importere sqlite3 conn = sqlite3.koble til ("prøve.db ") konn.text_factory = str print ("\ n [+] Vellykket koblet til databasen") cur = conn.markør () utskrift ("\ n [+] Markøren er satt opp vellykket") cur.utfør ("DROP TABLE IF EXISTS student") utskrift ("\ n [+] Tabellen har blitt vellykket") cur.lukk () konn.begå () konn.Lukk()

Ovennevnte program vil slette tabellen student fra prøve database. Vi kan bruke listetabellkommandoen vi har sett tidligere for å se om tabellen er slettet. Resultatet av programmet er som vist nedenfor.

SQLite Database Unntak

Noen SQLite-database unntak kan heves på grunn av noen feil. La oss se litt når feilene ble reist.

sqlite3.Advarsel: Det er en unntaksklasse. Dette unntaket viser noen advarsler, og de kan ignoreres i mange tilfeller.
sqlite3.Feil: Det er også en unntaksklasse. Det er basisklassen for alle andre unntak i sqlite3 modul.
sqlite3.DatabaseError: Dette er feilene som blir reist på grunn av noen feil i databaser. For eksempel: - Hvis vi prøver å koble til en kryptert database eller en feil databasefil, vil det vise en DatabaseError at dataene er kryptert eller ikke en gyldig databasefil.
sqlite3.IntegrityError: Dette unntaket er en underklasse av en DatabaseError. Vi får dette unntaket når relasjonsintegriteten til en database påvirkes, f.eks.g., En feil i kontrollen av utenlandsk nøkkel.
sqlite3.ProgrammingError: Dette unntaket er også en underklasse av DatabaseError. Dette unntaket heves på grunn av programmeringsfeil, f.eks.g., lage en tabell med samme navn som allerede eksisterer, syntaksfeil i SQL-spørringene osv.
sqlite3.OperationalError: Det er også en underklasse av DatabaseError. Dette unntaket hevdes for feil som er relatert til databasens drift og som ikke er i vår kontroll. For eksempel, en utilsiktet frakobling med systemet, serveren nede, en timeout oppstår, datakildeproblemer, maskinavstenging osv.
sqlite3.NotSupportedError: Dette unntaket heves når databasen ikke støtter et database-API som brukes.

Dette er listen over alle SQLite-unntak; vi kan håndtere disse unntakene i programmene våre ved hjelp av grunnleggende prøve / unntatt feilhåndteringsmetode for Python.

Konklusjon

Det bringer oss til slutten av den omfattende guiden om avanserte metoder for å jobbe med SQLite ved hjelp av Python. Jeg håper du har lært alle aspektene av SQLite3 ved hjelp av Python, som vil hjelpe oss med å bygge fantastiske Python-prosjekter.