De SQLite-database beheersen in Python

SQLite is een relationeel databasebeheersysteem gebaseerd op de SQL-taal; het is een serverloze database-engine zonder configuratie. Het is een van de meest populaire database-engines en zeer gemakkelijk te gebruiken in kleine toepassingen. Het creëert slechts één schijfbestand om de hele database op te slaan, waardoor het bestand overdraagbaar is. Het wordt in het Android-besturingssysteem gebruikt als de primaire bron om gegevens op te slaan. Het wordt ook gebruikt door Google Chrome om sitegegevens en gebruikersgegevens, inclusief wachtwoorden, op te slaan op de lokale computer.

Geavanceerd werken met SQLite Database in Python

In deze tutorial komen de volgende onderwerpen aan bod: afbeeldingen invoegen in een SQLite-tabel, Lijst van de aanwezige tabellen in een database, Identificeer totale veranderingen sinds de database is aangesloten, Back-up van een database, Dumpen van een SQLite-database, Terugdraaien in SQLite, Verwijderen records uit een tabel, Een tabel neerzetten en SQLite-database-uitzonderingen.

Misschien wil je ook het eerste deel van deze tutorial zien, waarin de basisprincipes van SQLite, voordelen van het gebruik ervan, verbinding maken met een databasebestand, een tabel in de database maken, gegevens in de tabel invoegen, gegevens uit de tabel opvragen, de tabel bijwerken en nog veel meer.

Bestanden en afbeeldingen in de SQLite-database

Tijdens het werken met databases zijn er situaties waarin u afbeeldingen of bestanden in een database moet invoegen of daaruit moet exporteren. Als u bijvoorbeeld een database maakt om werknemersgegevens op te slaan, moet u mogelijk ook afbeeldingen van elke werknemer in de database invoegen.

Om afbeeldingen toe te voegen aan een SQLite-database, moeten we het BLOB-gegevenstype SQLite gebruiken. Het gegevenstype BLOB() wordt gebruikt om grote objecten op te slaan, meestal grote bestanden zoals afbeeldingen, muziek, video's, documenten, PDF, enz. De eerste stap is om de gegevens en afbeeldingen om te zetten in het byte-object van Python, wat vergelijkbaar is met het BLOB-gegevenstype van SQLite. Maak voordat u verder gaat een tabel met de naam leerling in de database met de velden id, naam, afbeeldingen, merken. Voer de volgende code uit om de tabel te maken:.

import sqlite3 conn = sqlite3.connect("voorbeeld.db") print("\n [+] Met succes verbonden met de database") cur = conn.cursor() print("\n [+] Cursor is succesvol ingesteld") table = cur.execute(""" CREATE TABLE student( id INT PRIMARY KEY, naam TEXT, afbeeldingen BLOB, markeert TEXT ); """) print("\n [+] De tabel is succesvol aangemaakt") cur.close() conn.commit() verbind.dichtbij()

Dit programma maakt een nieuwe tabel aan met de naam leerling. U ziet de volgende uitvoer in de terminal:.

Een afbeelding invoegen

Om een ​​afbeelding in een SQLite-database in te voegen, transformeert u de afbeelding in een python-byte-object en voegt u deze vervolgens in de afbeeldingenkolom, die BLOB-gegevens accepteert. Voer de volgende code uit om een ​​afbeelding toe te voegen img.png in de database met behulp van Python.

import sqlite3 conn = sqlite3.connect("voorbeeld.db").text_factory = str print("\n [+] Met succes verbonden met de database") cur = conn.cursor() print("\n [+] Cursor is succesvol ingesteld") met open("img.png","rb") als bestand: data = bestand.read() python_tuple = (101,"robin",data,"90") print("\n [+] De afbeelding is succesvol geïmporteerd") print("\n [+] Wordt nu in de database ingevoegd") cur.execute("INSERT INTO student (id,name,images,marks) VALUES (?,?,?,?)", python_tuple) print("\n [+] De gegevens zijn met succes ingevoerd ") cur.close() conn.commit() verbind.dichtbij()

Dit programma zal de afbeelding invoegen in de studentendatabase die je hebt gemaakt. U ziet de volgende uitvoer:.

In het bovenstaande programma hebben we het bestand in binaire modus geopend en elke byte gelezen en in variabele opgeslagen gegevens. Vervolgens gebruiken we die variabele in de INSERT-instructie om de afbeelding in de database in te voegen.

Een afbeelding ophalen

Om een ​​afbeelding uit een database op te halen, haalt u de rij op met behulp van een select-instructie en opent u vervolgens de binaire gegevens van de afbeelding in een python-variabele, die wordt opgeslagen in een afbeeldingsbestand. Zie de volgende code ter illustratie:.

import sqlite3 conn = sqlite3.connect("voorbeeld.db").text_factory = str print("\n [+] Met succes verbonden met de database") cur = conn.cursor() print("\n [+] Cursor is succesvol ingesteld") print("\n [+] Ophalen van de afbeelding") cur.execute ("SELECT * FROM student") ret = cur.fetchall() voor i in ret: data = i[2] met open("img2.png","wb") als bestand: bestand.write(data) print("\n [+] De afbeelding is opgeslagen") cur.close() conn.commit() verbind.dichtbij()

Dit eenvoudige programma haalt de afbeelding op uit de database en slaat deze op op de schijf met de naam img2.png. U kunt ook een andere naam voor het afbeeldingsbestand kiezen. De uitvoer van het programma wordt hieronder getoond:.

Maak een lijst van alle tabellen van een database

In een database kunnen we een groot aantal tabellen maken. Het is dus ook nodig om alle tabellen in een database op te sommen. Om de tabellen in een database weer te geven, vraagt ​​u de sqlite_master-tabel op met behulp van de SELECT-instructie van SQL. De syntaxis van de query zal zijn:

SELECT naam FROM sqlite_master WHERE type = "tabel"

Hier is hoe we deze query gebruiken om alle tabellen in onze database weer te geven:.

import sqlite3 conn = sqlite3.connect("voorbeeld.db") print("\n [+] Met succes verbonden met de database") cur = conn.cursor() print("\n [+] Cursor is succesvol ingesteld") cur.execute("SELECT name from sqlite_master where type="table"") rijen = cur.fetchall() print(rijen) cur.close() conn.commit() verbind.dichtbij()

De bovenstaande code geeft een lijst van alle tabellen die aanwezig zijn in onze database. De uitvoer die door de code wordt geproduceerd wanneer deze wordt uitgevoerd, is als volgt:. Mogelijk ziet u wat andere uitvoer, afhankelijk van de tabellen die u in de database hebt gemaakt.

Identificeren van totale wijzigingen sinds verbinding met de database

In elke situatie is het handig om het aantal rijen te identificeren dat is gewijzigd, ingevoegd of verwijderd sinds de database is verbonden. Gebruik daarvoor de totaal_veranderingen() methode van het verbindingsobject, die het totale aantal databaserijen retourneert dat is beïnvloed sinds de verbinding. Laten we een voorbeelddemo bekijken om te zien hoe het werkt.

import sqlite3 conn = sqlite3.connect("voorbeeld.db").text_factory = str print("\n [+] Met succes verbonden met beide databases") cur = conn.cursor() print("\n [+] Beide cursors zijn succesvol ingesteld") cur.execute("INSERT INTO student (id, name, images, marks) VALUES (140, 'David',",99 )") cur.execute("INSERT INTO student (id, name,images, marks) VALUES (150, 'Sam',"", 97)") changes = conn.total_changes print("\n [+] Het totale aantal wijzigingen in rijen is nu :",changes) conn.commit() cur.close() conn.dichtbij()

Het bovenstaande programma zal het aantal rijwijzigingen in de huidige verbinding afdrukken. U ziet de volgende uitvoer:.

Terugdraaien in SQLite

Als het gaat om het ongedaan maken van sommige taken, kunt u de functie rollback() gebruiken. Deze methode kan worden gebruikt om een ​​taak ongedaan te maken die is uitgevoerd na de laatste commit. Zie het onderstaande voorbeeld voor een illustratie.

import sqlite3 conn = sqlite3.connect("voorbeeld.db").text_factory = str print("\n [+] Met succes verbonden met beide databases") cur = conn.cursor() print("\n [+] Beide cursors zijn succesvol ingesteld") cur.execute("INSERT INTO student (id, name, images, marks) VALUES (10001, 'David',",99 )") cur.execute("INSERT INTO student (id, name,images, marks) VALUES (100002, 'Sam',"", 97)") conn.commit() print("\n [+] De twee rij is met succes ingevoegd") cur.execute ("SELECT * FROM student") first = cur.fetchall() print("\n [+] De nieuwe records in de database zijn :") voor i in first: 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 [+] De rij Twee is ingevoegd maar niet vastgelegd") conn.rollback() print("\n [+] We hebben de vorige commando's teruggedraaid, zodat de nieuwe gegevens niet worden ingevoegd") conn.commit() cur.execute ("SELECT * FROM student") second = cur.fetchall() print("\n [+] De nieuwe records in de database zijn :") voor i in de tweede: print(i) cur.close() conn.dichtbij()

In het bovenstaande voorbeeld zullen de eerste twee invoeginstructies de gegevens invoegen zoals opgegeven, maar de laatste twee invoeginstructies worden teruggedraaid zodat ze geen gegevens aan de tabel toevoegen. De uitvoer zal zijn zoals hieronder weergegeven:.

Back-up van een database

Tijdens het werken met de database is het essentieel om een ​​back-up van de database te maken. De sqlite3-module biedt een functie om de back-up van de database te maken. Met behulp van de methode backup() van het verbindingsobject kunnen we de back-up van de SQLite-database maken. De basissyntaxis van de back-upmethode is:

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

Standaard, of wanneer Pagina's zijn ofwel 0 of een negatief geheel getal, de hele database wordt in één stap gekopieerd, wat de voorkeur heeft voor een kleine database; anders voert de methode een lus uit die kopieert tot Pagina's op een moment dat met de uitgebreide database gedaan zou kunnen worden. De naam argument toont de databasenaam die wordt gekopieerd: het moet een tekenreeks zijn die ofwel de standaard bevat, om de hoofddatabase aan te geven, of om de tijdelijke database aan te geven. De slaap argument specificeert de tijd in seconden om te slapen tussen pogingen om een ​​back-up te maken van de resterende pagina's. Het kan een geheel getal of een drijvende-kommawaarde zijn.

Laten we een back-up maken van de database.db database die we in de tutorial hebben gebruikt.

import sqlite3 conn_main = sqlite3.connect("voorbeeld.db") conn_backup = sqlite3.connect("sample_backup.db") print("\n [+] Met succes verbonden met beide databases") cur_main = conn_main.cursor() cur_backup = conn_backup.cursor() print("\n [+] Beide cursors zijn succesvol ingesteld") conn_main.backup(conn_backup, pages=0, progress=None, name="main") print("De database is succesvol geback-upt") cur_main.close() cur_backup.close() conn_main.commit() conn_backup.commit() conn_main.close() conn_backup.dichtbij()

In de bovenstaande code zijn de twee databases met elkaar verbonden, de ene is de database waarvan we een back-up willen maken en de tweede is de database waarin we de back-up zullen maken. Gebruik de back-up() methode van het eerste databaseverbindingsobject om een ​​back-up te maken. Deze functie accepteert het verbindingsobject van de tweede database als het doel om een ​​back-up te maken op de andere database. Gebruik de pagina's = 0 argumenten, dus het proces zal in één stap plaatsvinden, wat wordt aanbevolen voor kleine databases. Dit programma maakt een nieuw voorbeeld van een databasenaam_backup.db en vul het met de back-up van de eerste database. Mogelijk ziet u dat er een nieuwe database is gemaakt in de huidige map met dezelfde bestandsgrootte als de vorige.

Een SQLite-database dumpen

Databases dumpen is een belangrijke taak. Gewoonlijk is een dumpbestand een set SQL-instructies voor de gegevens, die over het algemeen worden gebruikt voor back-up. We kunnen een database dumpen met de methode dump()). Zie het onderstaande voorbeeld om te weten hoe u een SQLite-database kunt neerzetten.

import sqlite3 con = sqlite3.connect("database.db") met open('dump.sql', 'w') als f: voor regel in con.iterdump(): f.schrijven('%s\n' % regel)

Het bovenstaande programma zal het databasevoorbeeld dumpen.db, en het zal de gedumpte gegevens opslaan in een bestand met de naam dump.sql. U kunt de gegevens zien die aanwezig zijn in de map waar de python-bestanden actueel zijn en deze openen met een teksteditor.

executemany() methode van SQLite3

De executemany() methode voert een SQL-opdracht uit tegen alle parameterreeksen of toewijzingen die in de reeks worden gevonden seq_of_parameters. Voor de eenvoud kan deze methode worden gebruikt om de meeste SQL-opdrachten op één regel uit te voeren. E.g., We kunnen een willekeurig aantal rijen invoegen via een python-lijst met behulp van deze opdracht. Zie het onderstaande voorbeeld voor de illustratie:.

import sqlite3 conn = sqlite3.connect("voorbeeld.db") print("\n [+] Met succes verbonden met de database") cur = conn.cursor() print("\n [+] Cursor is succesvol ingesteld") python_list = [(10000000 ,'vivek',",'10'), (100000001,'rose',",'21'), (100000002,'robin',",'31'), (100000003,'Dev',",'4'), (100000004,'michael',",'52') ].executemany("INSERT INTO student (id, naam, afbeeldingen, cijfers) VALUES (?,?,?,?)",python_list) print("\n [+] Alle gegevens zijn met succes ingevoerd ") cur.close() conn.commit() verbind.dichtbij()

Het bovenstaande programma voegt alle gegevens in de python-lijst in. De output die door het programma wordt geproduceerd, wordt hieronder weergegeven:.

Records uit een tabel verwijderen

We kunnen de DELETE-bewerking gebruiken om records uit een tabel te verwijderen. We kunnen snel een rij verwijderen met behulp van de DELETE-bewerking met de WHERE-component. De basissyntaxis voor de DELETE-instructie is:

DELETE uit tabelnaam WHERE some_condition;

Laten we een voorbeeld zien. We zullen de rij met id 1001 verwijderen uit de werknemerstabel van onze database.

import sqlite3 conn = sqlite3.connect("voorbeeld.db").text_factory = str print("\n [+] Met succes verbonden met de database") cur = conn.cursor() print("\n [+] Cursor is succesvol ingesteld") cur.execute("DELETE FROM student WHERE id=1001") print("\n [+] De rij is succesvol verwijderd ") cur.execute("SELECT * FROM student") data = cur.fetchall() voor rij in data: print(rij) cur.close() conn.commit() verbind.dichtbij()

De bovenstaande code verwijdert de rij met ID 1001. U kunt aan de terugkeer van de SELECT-instructie zien dat de rij is verwijderd. De uitvoer van het programma is zoals hieronder weergegeven:.

Zet een tafel neer

We kunnen snel een tabel laten vallen of verwijderen met behulp van de SQLite DROP-instructie. De syntaxis van de DROP-instructie is zoals hieronder weergegeven:

DROP tabel tabelnaam 

Als de tabel niet bestaat, zal SQLite een fout genereren, dus om dit te voorkomen, kunnen we de . gebruiken indien bestaan tag met de DROP-instructie. Zie de onderstaande syntaxis:

DROP tabel indien bestaat tabelnaam

Laten we eens kijken hoe we deze verklaring kunnen gebruiken met de python sqlite3 module om een ​​tabel te verwijderen. In dit programma zullen we de leerling tabel die we eerder hebben gemaakt.

import sqlite3 conn = sqlite3.connect("voorbeeld.db").text_factory = str print("\n [+] Met succes verbonden met de database") cur = conn.cursor() print("\n [+] Cursor is succesvol ingesteld") cur.execute("DROP TABLE IF EXISTS student") print("\n [+] De tabel is succesvol verwijderd") cur.close() conn.commit() verbind.dichtbij()

Het bovenstaande programma zal de tabel verwijderen leerling van de monster database. We kunnen het lijsttabelcommando gebruiken dat we eerder hebben gezien om te zien of de tabel is verwijderd. De uitvoer van het programma is zoals hieronder weergegeven:.

Uitzonderingen op SQLite-database

Sommige SQLite-database-uitzonderingen kunnen optreden als gevolg van een fout. Laten we eens kijken wanneer die fouten zijn gemaakt.

• sqlite3.Waarschuwing: het is een subklasse van uitzondering. Deze uitzondering toont enkele waarschuwingen, en die kunnen in veel gevallen worden genegeerd.
• sqlite3.Fout: het is ook een subklasse van uitzondering. Het is de basisklasse van alle andere uitzonderingen in de sqlite3 module.
• sqlite3.DatabaseError: Dit zijn de fouten die optreden als gevolg van enkele fouten in databases. Voor bijvoorbeeld: - Als we proberen verbinding te maken met een gecodeerde database of een verkeerd databasebestand, dan zal het een DatabaseError laten zien dat de gegevens zijn gecodeerd of geen geldig databasebestand zijn.
• sqlite3.IntegrityError: deze uitzondering is een subklasse van een DatabaseError. We krijgen deze uitzondering wanneer de relationele integriteit van een database wordt aangetast, bijv.g., Een fout in de externe sleutelcontrole.
• sqlite3.ProgrammingError: Deze uitzondering is ook een subklasse van DatabaseError. Deze uitzondering wordt veroorzaakt door programmeerfouten, e.g., een tabel maken met dezelfde naam die al bestaat, syntaxisfout in de SQL-query's, enz.
• sqlite3.OperationalError: het is ook een subklasse van DatabaseError. Deze uitzondering is opgetreden voor fouten die verband houden met de werking van de database en waarover wij geen controle hebben. Bijvoorbeeld een onbedoelde verbinding met het systeem, een server uitgevallen, een time-out optreedt, problemen met de gegevensbron, het afsluiten van de machine, enz.
• sqlite3.NotSupportedError: Deze uitzondering treedt op wanneer de database geen database-API ondersteunt die gebruikt.

Dit is de lijst met alle SQLite-uitzonderingen; we kunnen die uitzonderingen in onze programma's afhandelen met behulp van de standaard try/behalve-foutafhandelingsmethode van Python.

Conclusie

Dat brengt ons bij het einde van de uitgebreide gids over geavanceerde methoden voor het werken met SQLite met behulp van Python. Ik hoop dat je alle aspecten van SQLite3 hebt geleerd met Python, wat ons zal helpen fantastische Python-projecten te bouwen.