Mastering af SQLite-databasen i Python

SQLite er et relationsdatabasesystem baseret på SQL-sproget; det er en serverløs, nulkonfigurationsdatabasemotor. Det er en af de mest populære databasemotorer og meget let at bruge i små applikationer. Det opretter kun en diskfil til at gemme hele databasen, hvilket gør filen bærbar. Det bruges i Android OS som den primære kilde til lagring af data. Det bruges også af Google Chrome til at gemme webstedsdata og brugerdata, herunder adgangskoder på den lokale maskine.

Avanceret arbejde med SQLite Database i Python

I denne vejledning er emnerne, der vil blive dækket: indsættelse af billeder i en SQLite-tabel, Liste over de tabeller, der er til stede i en database, Identificer samlede ændringer, da databasen er tilsluttet, Backup af en database, Dumping af en SQLite-database, Tilbageførsel i SQLite, Sletning poster fra en tabel, undtagelse af en tabel og SQLite-databaseundtagelser.

Det kan også være en god idé at se den første del af denne tutorial, der præsenterer Grundlæggende om SQLite, Fordele ved at bruge det, Oprette forbindelse til en databasefil, Oprette en tabel i databasen, Indsætte data i tabellen, Forespørgsel om data fra tabellen, opdatering af tabellen og mange flere.

Filer og billeder i SQLite-databasen

Mens du arbejder med databaser, er der situationer, hvor du skal indsætte billeder eller filer i en database eller eksportere fra den. For eksempel, hvis du opretter en database til lagring af medarbejderdata, skal du muligvis også indsætte billeder af hver medarbejder i databasen.

For at tilføje billeder i en SQLite-database skal vi bruge BLOB-datatypen SQLite. BLOB () datatypen bruges til at gemme store objekter, typisk store filer som billeder, musik, videoer, dokumenter, PDF osv. Det første trin er at konvertere data og billeder til byteobjektet i Python, hvilket svarer til BLOB-datatypen SQLite. Før du fortsætter, skal du oprette en tabel med navnet studerende i databasen med felterne id, navn, billeder, mærker. Kør følgende kode for at oprette tabellen.

import sqlite3 conn = sqlite3.forbinde ("prøve.db ") print (" \ n [+] Vellykket tilsluttet databasen ") cur = conn.cursor () print ("\ n [+] Markør er oprettet med succes") tabel = cur.execute ("" "CREATE TABLE student (id INT PRIMARY KEY, name TEXT, images BLOB, marks TEXT);" "") print ("\ n [+] Tabellen er oprettet med succes") cur.luk () tilslut.begå () konn.tæt()

Dette program opretter en ny tabel med navnet studerende. Du ser følgende output i terminalen.

Indsættelse af et billede

For at indsætte et billede i en SQLite-database skal du omdanne billedet i et python-byteobjekt og derefter indsætte det i billedkolonnen, som accepterer BLOB-data. Kør følgende kode for at tilføje et billede img.png i databasen ved hjælp af Python.

import sqlite3 conn = sqlite3.forbinde ("prøve.db ") tilslutning.text_factory = str print ("\ n [+] Forbundet til databasen") cur = conn.markør () print ("\ n [+] Markør er konfigureret med succes") med åben ("img.png "," rb ") som fil: data = fil.læs () python_tuple = (101, "robin", data, "90") print ("\ n [+] Billedet er importeret med succes") print ("\ n [+] Indsættes nu i databasen") cur.execute ("INSERT INTO student (id, name, images, marks) VALUES (?,?,?,?) ", python_tuple) print (" \ n [+] Data er indsat med succes ") cur.luk () tilslut.begå () konn.tæt()

Dette program indsætter billedet i den elevdatabase, du oprettede. Du vil se følgende output.

I ovenstående program har vi åbnet filen i binær tilstand og læser hver byte og gemmer den i variabel data. Derefter bruger vi denne variabel i INSERT-sætningen til at indsætte billedet i databasen.

Henter et billede

For at hente et billede fra en database skal du hente rækken ved hjælp af en select-sætning og derefter få adgang til de binære data i billedet til en pythonvariabel, som lagres i en billedfil. Se følgende kode til illustration.

import sqlite3 conn = sqlite3.forbinde ("prøve.db ") tilslutning.text_factory = str print ("\ n [+] Forbundet til databasen") cur = conn.cursor () print ("\ n [+] Markør er konfigureret med succes") print ("\ n [+] Henter billedet") cur.udfør ("VÆLG * FRA studerende") ret = cur.fetchall () for i i ret: data = i [2] med åben ("img2.png "," wb ") som fil: fil.skriv (data) print ("\ n [+] Billedet er gemt") cur.luk () tilslut.begå () konn.tæt()

Dette enkle program henter billedet fra databasen og gemmer det på disken med navnet img2.png. Du kan også vælge et andet navn til billedfilen. Programmets output vises nedenfor.

Liste over alle tabeller i en database

I en database kan vi oprette en lang række tabeller. Så der er også et behov for at liste alle de tabeller, der findes i en database. For at liste de tabeller, der er til stede i en database, skal du forespørge sqlite_master-tabellen ved hjælp af SELECT-sætningen i SQL. Forespørgslens syntaks vil være:

VÆLG navn FRA sqlite_master WHERE type = "tabel"

Sådan bruger vi denne forespørgsel til at liste alle de tabeller, der findes i vores database.

import sqlite3 conn = sqlite3.forbinde ("prøve.db ") print (" \ n [+] Vellykket forbundet til databasen ") cur = conn.cursor () print ("\ n [+] Markør er konfigureret med succes") cur.udfør ("VÆLG navn fra sqlite_master hvor type =" tabel "") rækker = cur.fetchall () print (rækker) cur.luk () tilslut.begå () konn.tæt()

Ovenstående kode viser alle de tabeller, der findes i vores database. Outputtet produceret af koden, når det udføres, er som følger. Du kan muligvis se noget andet output afhængigt af de tabeller, du har oprettet i databasen.

Identificering af samlede ændringer siden tilslutning til databasen

I enhver situation er det nyttigt at identificere antallet af rækker, der er blevet ændret, indsat eller slettet, siden databasen er blevet tilsluttet. Brug det til total_changes () metode til forbindelsesobjektet, som returnerer det samlede antal databaserækker, der er berørt siden forbindelsen. Lad os se et eksempel på en demo for at vide, hvordan det fungerer.

import sqlite3 conn = sqlite3.forbinde ("prøve.db ") tilslutning.text_factory = str print ("\ n [+] Forbundet til både databasen") cur = conn.cursor () print ("\ n [+] Begge markøren er konfigureret med succes") cur.execute ("INSERT INTO student (id, name, images, marks) VALUES (140, 'David',", 99) ") cur.udfør ("INSERT INTO student (id, name, images, marks) VALUES (150, 'Sam',", 97) ") ændringer = conn.total_changes print ("\ n [+] Samlet antal ændringer for rækker nu er:", ændringer) konn.begå () cur.luk () tilslut.tæt()

Ovenstående program udskriver antallet af ændringer i rækker i den aktuelle forbindelse. Du får vist følgende output.

Tilbageførsel i SQLite

Når det kommer til fortrydelse af nogle opgaver, kan du bruge funktionen rollback (). Denne metode kan bruges til at fortryde opgaven, som er udført efter den sidste begåelse. Se nedenstående eksempel for en illustration.

import sqlite3 conn = sqlite3.forbinde ("prøve.db ") tilslutning.text_factory = str print ("\ n [+] Forbundet til både databasen") cur = conn.cursor () print ("\ n [+] Begge markøren er konfigureret med succes") 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 [+] To rækken er indsat med succes") cur.udfør ("VÆLG * FRA studerende") først = cur.fetchall () print ("\ n [+] De nye poster 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 [+] To rækken er indsat med succes, men ikke forpligtet ") conn.rollback () print ("\ n [+] Vi har tilbagevendende de tidligere kommandoer, så de nye data ikke vil blive indsat") conn.begå () cur.execute ("SELECT * FROM student") second = cur.fetchall () print ("\ n [+] De nye poster i databasen er:") for i i sekund: print (i) cur.luk () tilslut.tæt()

I ovenstående eksempel indsætter de første to indsættelseserklæringer dataene som angivet, men de sidste to indsætningserklæringer vil være tilbagevendende, så de ikke tilføjer nogen data i tabellen. Outputtet vil være som vist nedenfor.

Sikkerhedskopier en database

Når du arbejder med databasen, er det vigtigt at tage en sikkerhedskopi af databasen. Sqlite3-modulet giver en funktion til at tage backup af databasen. Ved hjælp af backup () -metoden til forbindelsesobjektet kan vi lave backup af SQLite-databasen. Den grundlæggende syntaks for sikkerhedskopimetoden er:

backup (mål, *, sider = 0, fremskridt = ingen, navn = "main", sleep = 0.250)

Som standard, eller hvornår sider er enten 0 eller et negativt heltal kopieres hele databasen i et enkelt trin, hvilket er at foretrække for en lille database; Ellers udfører metoden en loopkopiering op til sider på et tidspunkt, der kunne gøres med den omfattende database. Det navn argumentet viser databasenavnet, der skal kopieres: det skal være en streng, der indeholder enten standard, for at angive hoveddatabasen eller for at angive den midlertidige database. Det søvn argument specificerer tiden i sekunder til at sove mellem forsøg på at tage backup af de resterende sider. Det kan enten være som et heltal eller en værdi med flydende punkt.

Lad os tage en sikkerhedskopi af database.db database, vi har brugt i vejledningen.

import sqlite3 conn_main = sqlite3.forbinde ("prøve.db ") conn_backup = sqlite3.tilslut ("sample_backup.db ") print (" \ n [+] Forbundet til både databasen ") cur_main = conn_main.cursor () cur_backup = conn_backup.cursor () print ("\ n [+] Begge markøren er konfigureret med succes") conn_main.backup (conn_backup, sider = 0, progress = Ingen, name = "main") print ("Databasen har været backup med succes") cur_main.luk () cur_backup.luk () conn_main.begå () conn_backup.begå () conn_main.luk () conn_backup.tæt()

I ovenstående kode er de to databaser forbundet, den ene er den database, som vi vil lave en sikkerhedskopi af, og den anden er den database, hvor vi tager sikkerhedskopien. Brug sikkerhedskopi () metode til det første database-forbindelsesobjekt til at lave en sikkerhedskopi. Denne funktion accepterer forbindelsesobjektet for den anden database som mål for at oprette en sikkerhedskopi på den anden database. Brug sider = 0 argumenter, så processen finder sted i et trin, hvilket anbefales til små databaser. Dette program opretter en ny prøve med databasenavn_backup.db og udfyld den med sikkerhedskopien af den første database. Du kan muligvis se, at der er oprettet en ny database i den aktuelle mappe med samme filstørrelse som den forrige.

Dumping af en SQLite-database

Dumping af databaser er en vigtig opgave. Normalt er en dumpfil et sæt SQL-udsagn til dataene, som normalt bruges til sikkerhedskopiering. Vi kan dumpe en database ved hjælp af dump () -metoden. Se nedenstående eksempel for at vide, hvordan du dropper en SQLite-database.

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

Ovenstående program vil dumpe databaseeksemplet.db, og det gemmer de dumpede data i en fil med navnet dump.kvm. Du kan se de data, der findes i biblioteket, hvor python-filerne er aktuelle, og åbne dem ved hjælp af en hvilken som helst teksteditor.

executemany () metode til SQLite3

Det eksekutiv () metode udfører en SQL-kommando mod alle parametersekvenser eller tilknytninger, der findes i sekvensen seq_of_parameters. For enkelheds skyld kan denne metode bruges til at udføre de fleste SQL-kommandoer på en linje. E.g., Vi kan indsætte et hvilket som helst antal rækker gennem en pythonliste ved hjælp af denne kommando. Se nedenstående eksempel for illustrationen.

import sqlite3 conn = sqlite3.forbinde ("prøve.db ") print (" \ n [+] Vellykket forbundet til databasen ") cur = conn.cursor () print ("\ n [+] Markøren er konfigureret med succes") 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) VÆRDIER (?,?,?,?) ", python_list) print (" \ n [+] Alle data er indsat med succes ") cur.luk () tilslut.begå () konn.tæt()

Ovenstående program indsætter alle de data, der er angivet på pythonlisten. Programmets outputproduktion er vist nedenfor.

Slet poster fra en tabel

Vi kan bruge SLET-operationen til at slette poster fra en tabel. Vi kan hurtigt fjerne en række ved hjælp af DELETE-operationen med WHERE-klausulen. Den grundlæggende syntaks for DELETE-sætningen er:

SLET fra tabelnavn WHERE nogle_tilstande;

Lad os se et eksempel. Vi sletter rækken med id 1001 fra medarbejdertabellen i vores database.

import sqlite3 conn = sqlite3.forbinde ("prøve.db ") tilslutning.text_factory = str print ("\ n [+] Forbundet til databasen") cur = conn.cursor () print ("\ n [+] Markør er konfigureret med succes") cur.udfør ("SLET FRA studerende WHERE id = 1001") print ("\ n [+] Rækken er slettet med succes") cur.execute ("SELECT * FROM student") data = cur.fetchall () for række i data: udskriv (række) cur.luk () tilslut.begå () konn.tæt()

Ovenstående kode sletter rækken med ID 1001. Du kan se fra returneringen af SELECT-sætningen, at rækken er blevet fjernet. Programmets output er som vist nedenfor.

Slip et bord

Vi kan hurtigt droppe eller slette en tabel ved hjælp af SQLite DROP-sætningen. Syntaksen for DROP-sætningen er som vist nedenfor:

DROP-tabel tabelnavn

Hvis tabellen ikke findes, vil SQLite kaste en fejl, så for at forhindre dette kan vi bruge hvis der findes tag med DROP-erklæringen. Se nedenstående syntaks:

DROP-tabel, hvis der findes tabelnavn

Lad os se, hvordan vi kan bruge denne erklæring med python sqlite3 modul for at slette en tabel. I dette program fjerner vi studerende tabel, vi har oprettet tidligere.

import sqlite3 conn = sqlite3.forbinde ("prøve.db ") tilslutning.text_factory = str print ("\ n [+] Forbundet til databasen") cur = conn.cursor () print ("\ n [+] Markør er konfigureret med succes") cur.udfør ("DROP TABLE IF EXISTS student") print ("\ n [+] Tabellen er faldet med succes") cur.luk () tilslut.begå () konn.tæt()

Ovenstående program sletter tabellen studerende fra prøve database. Vi kan bruge listetabellen, som vi har set tidligere, for at se om tabellen slettes. Programmets output er som vist nedenfor.

Undtagelser fra SQLite-database

Nogle SQLite-databaseundtagelser kan hæves på grund af en eller anden fejl. Lad os se lidt, hvornår disse fejl blev rejst.

sqlite3.Advarsel: Det er en underklasse af undtagelse. Denne undtagelse viser nogle advarsler, og de kan ignoreres i mange tilfælde.
sqlite3.Fejl: Det er også en undtagelsesklasse. Det er basisklassen for alle andre undtagelser i sqlite3 modul.
sqlite3.DatabaseError: Dette er de fejl, der rejses på grund af nogle fejl i databaser. For eksempel: - Hvis vi forsøger at oprette forbindelse til en krypteret database eller en forkert databasefil, vil det vise en DatabaseError, at dataene er krypteret eller ikke en gyldig databasefil.
sqlite3.IntegrityError: Denne undtagelse er en underklasse af en DatabaseError. Vi får denne undtagelse, når relationens integritet af en database påvirkes, f.eks.g., En fejl i den udenlandske nøglekontrol.
sqlite3.ProgrammingError: Denne undtagelse er også en underklasse af DatabaseError. Denne undtagelse hæves på grund af programmeringsfejl, f.eks.g., oprettelse af en tabel med det samme navn, som allerede findes, syntaksfejl i SQL-forespørgsler osv.
sqlite3.OperationalError: Det er også en underklasse af DatabaseError. Denne undtagelse rejst for fejl, der er relateret til databasens drift og ikke er under vores kontrol. For eksempel, en utilsigtet afbrydelse med systemet, serveren nede, der opstår en timeout, datakildeproblemer, nedlukning af maskinen osv.
sqlite3.NotSupportedError: Denne undtagelse hæves, når databasen ikke understøtter en anvendt database-API.

Dette er listen over alle SQLite-undtagelser. vi kan håndtere disse undtagelser i vores programmer ved hjælp af grundlæggende prøve / undtagen fejlhåndteringsmetode for Python.

Konklusion

Det bringer os til slutningen af den omfattende vejledning om avancerede metoder til at arbejde med SQLite ved hjælp af Python. Jeg håber, du har lært alle aspekterne af SQLite3 ved hjælp af Python, hvilket vil hjælpe os med at opbygge fantastiske Python-projekter.