Hoe te werken met SQLite in Python – Een handboek voor beginners

SQLite is een van de populairste relatieve databasebeheerstelsels (RDBMS). Het is lichtgewicht, wat betekent dat het weinig ruimte in beslag neemt op uw systeem. Eén van zijn beste kenmerken is dat het serverloos is, dus u heeft geen aparte server nodig om het te gebruiken.

In plaats daarvan slaat het alle informatie op in een eenvoudig bestand op uw computer. Het vereist ook geen configuratie, dus er is geen complexe setup-proces, waardoor het perfect is voor beginnende gebruikers en kleine projecten.

SQLite is een goede keuze voor kleine tot medium-grootte toepassingen omdat het gemakkelijk te gebruiken is, snel is en de meeste taken kan uitvoeren die grote databases kunnen, maar zonder de zorgen van het beheren van extra software. Of u een persoonlijk project bouwt of een nieuwe app prototypeert, is SQLite een solide optie om snel aan de slag te kunnen.

In deze handleiding leert u hoe u SQLite kunt gebruiken met Python. Hieronder staat wat we in deze handleiding zullen bespreken:

Hoe uw Python-omgeving in te stellen
Hoe u een SQLite-database aanmaakt
Hoe u database tabellen aanmaakt
Hoe voeg je data toe aan een tabel
Hoe zoek je data
Hoe bijwerken en verwijderen van data
Hoe gebruik je transacties
Hoe optimaliseer je SQLite query prestaties met indexering
Hoe verwerking fouten en uitzonderingen
Hoe gegevens exporteren en importeren [Bonussectie]
Afronding

Deze tutorial is perfect voor iedereen die wil beginnen met databases zonder zich te verdiepen in complexe configuraties.

Hoe je jouw Python-omgeving instelt

Voordat je met SQLite gaat werken, zorg ervoor dat jouw Python-omgeving klaar is. Dit is hoe je alles instelt.

Python installeren

Als je Python nog niet op jouw systeem hebt geïnstalleerd, kun je het downloaden van de officiële Python-website. Volg de installatie-instructies voor jouw besturingssysteem (Windows, macOS of Linux).

Om te controleren of Python is geïnstalleerd, open je jouw terminal (of opdrachtprompt) en typ je:

python --version

Dit zou de huidige versie van Python moeten tonen. Als het niet is geïnstalleerd, volg dan de instructies op de Python-website.

SQLite3-module installeren

De goede nieuws is dat SQLite3 standaard ingebouwd is in Python! U hoeft het niet apart te installeren, want het maakt deel uit van de standaard Python-bibliotheek. Dit betekent dat u direct kunt beginnen met het gebruik ervan zonder enige extra configuratie.

Hoe u een virtuele omgeving creëert (Optioneel, maar aanbevolen)

Het is een goed idee om voor elk project een virtuele omgeving te creëren om uw afhankelijkheden geordend te houden. Een virtuele omgeving is als een schone startpagina waar u pakketten kunt installeren zonder dat dit uw algemene Python-instellingen zal beïnvloeden.

Om een virtuele omgeving te creëren, volg de volgende stappen:

Open eerst uw terminal of command prompt en navigeer naar het directory waar u uw project wilt creëren.
Voer het volgende commando uit om een virtuele omgeving te creëren:

python -m venv env

Hier is env de naam van de virtuele omgeving. U kunt hem welke naam u wilt geven.

Activeer de virtuele omgeving:

# Gebruik dit commando voor Windows
env\Scripts\activate

# Gebruik dit commando voor macOS/Linux:
env/bin/activate

Na het activeren van de virtuele omgeving zult u zien dat uw terminalprompt verandert, dat de naam van de virtuele omgeving weergeeft. Dit betekent dat u nu binnen deze werkt.

Installeer noodzakelijke bibliotheken

We zullen voor dit project enkele aanvullende bibliotheken nodig hebben. Specifiek zullen we gebruik maken van:

pandas: Dit is een optionele bibliotheek voor het behandelen en weergeven van data in tabellaire vorm, handig voor geavanceerde use-cases.
faker: Deze bibliotheek zal ons helpen gefaKE data te genereren, zoals willekeurige namen en adressen, die we kunnen invoeren in onze database voor testen.

Om pandas en faker te installeren, moet u eenvoudigweg de volgende commando’s uitvoeren:

pip install pandas faker

Dit installeert zowel pandas als faker in uw virtuele omgeving. Met dit, is uw omgeving klaar en kunt u beginnen met het maken en beheren van uw SQLite-database in Python!

Hoe u een SQLite-database kunt aanmaken

Een database is een gestructureerde manier om data op te slaan en te beheren zodat het gemakkelijk geaccessioneerd, bijgewerkt en georganiseerd kan worden. Het is net als een digitaal archiefingssysteem dat u toestaat om efficiënt grote hoeveelheden data op te slaan, ofwel voor een eenvoudige app dan wel voor een complexer systeem. Databases gebruiken tabellen om data te organiseren, met rijen en kolommen die individuele records en hun attributen representeren.

Hoe SQLite-databases werken

Contrasterend met de meeste andere databasebesturingssystemen is SQLite een serverloze database. Dit betekent dat het geen server nodig heeft die opgezet of beheerd wordt, waardoor het licht en gemakkelijk te gebruiken is. Alle gegevens worden opgeslagen in één bestand op uw computer, dat u gemakkelijk kunt verplaatsen, delen of back-uppen. Ondanks zijn eenvoudigheid is SQLite genoeg krachtig om veel algemene database taken te kunnen afhandelen en wordt breed gebruikt in mobiele apps, ingebedde systemen en kleine tot middelgrote projecten.

Hoe een Nieuwe SQLite Database Te Maken

Laten we een nieuwe SQLite database maken en leren hoe we met behulp van de Python-bibliotheek `sqlite3` met de database kunnen interacteren.

Verbinding Maken Met de Database

Omdat `sqlite3` al geïnstalleerd is, hoeft u hem alleen maar te importeren in uw Python-script. Om een nieuwe database te maken of een bestaande aan te sluiten, gebruiken we de `sqlite3.connect()` methode. Deze methode neemt de naam van het databasebestand als argument. Als het bestand bestaat, zal SQLite het automatisch aanmaken.

import sqlite3

# Maak een verbinding met de SQLite database (of maak deze aan als het bestaat niet)
connection = sqlite3.connect('my_database.db')

In dit voorbeeld wordt een bestand genaamd `my_database.db` aangemaakt in dezelfde map als uw script. Als het bestand al bestaat, zal SQLite de verbinding alleen maar openen.

Cursor Aanmaken

Als u een verbinding hebt, is het volgende stap om een cursorobject te maken. Het cursorobject is verantwoordelijk voor het uitvoeren van SQL-commando’s en queries op de database.

# Maak een cursorobject
cursor = connection.cursor()

Verbinding Sluiten

Na het werken met de database is het belangrijk de verbinding te sluiten om eventuele resources vrij te maken. U kunt de verbinding sluiten met het volgende commando:

# Sluit de databaseverbinding
connection.close()

Het is echter aan te raden de verbinding alleen maar te sluiten nadat u alle uw operationele bewerkingen heeft afgerond.

Als u uw Python-script uitvoert, wordt een bestand genaamd my_database.db aangemaakt in uw huidige werkdirectory. U heeft nu succesvol uw eerste SQLite-database aangemaakt!

Hoe gebruik je een contextbeheerder om verbindingen te openen en te sluiten

Python biedt een efficiënter en schooner manier om databaseverbindingen te behandelen met behulp van de with-uitspraak, ook wel bekend als een contextbeheerder. De with-uitspraak automatisch verbindingen open en sluit, waardoor de verbinding goed wordt gesloten, zelfs als er een fout optreedt tijdens de databasebewerkingen. Dit elimineert de noodzaak om handmatig connection.close() aan te roepen.

Hier is hoe u de with-uitspraak kunt gebruiken om databaseverbindingen te behandelen:

import sqlite3

# Step 1: Gebruik 'with' om verbinding te maken met de database (of een aan te maken) en sluit deze automatisch af als gereed
with sqlite3.connect('my_database.db') as connection:

    # Step 2: Maak een cursorobject om te communiceren met de database
    cursor = connection.cursor()

    print("Database created and connected successfully!")

# Er is geen noodzaak meer om connection.close() aan te roepen; dat gebeurt automatisch!

Vanaf nu zullen we in onze aanstaande codevoorbeelden de with-boodschap gebruiken om databaseverbindingen efficiënt te beheren. Dit zal de code concis maken en gemakkelijker beheerbaar maken.

Hoe u database tabellen aanmaakt

Nu we een SQLite-database hebben aangemaakt en er aan zijn gekoppeld, is de volgende stap om binnen de database tabellen te maken. Een tabel is de plek waar we onze gegevens zullen opslaan, georganiseerd in rijen (record) en kolommen (eigenschappen). Voor dit voorbeeld zullen we een tabel aanmaken genaamd Students om informatie over studenten op te slaan, die we in latere secties opnieuw zullen gebruiken.

Om een tabel aan te maken, gebruiken we SQL’s CREATE TABLE-opdracht. Deze commando’s definiëren de structuur van de tabel, inclusief de kolomnamen en de gegevenstypen voor elke kolom.

Hier is een eenvoudige SQL-opdracht om een Students-tabel aan te maken met de volgende velden:

id: Een unieke identificator voor elke student (een integer).
name: De naam van de student (tekst).
age: Het leeftijd van de student (een integer).
email: Het e-mailadres van de student (tekst).

De SQL-opdracht om deze tabel aan te maken zou er als volgt uitzien:

CREATE TABLE Students (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER,
    email TEXT
);

We kunnen deze CREATE TABLE SQL-instructie in Python uitvoeren met behulp van de sqlite3-bibliotheek. Let’s zien hoe dat gaat.

import sqlite3

# Gebruik 'with' om verbinding te maken met de SQLite-database en de verbinding automatisch te sluiten als het klaar is
with sqlite3.connect('my_database.db') as connection:

    # Maak een cursorobject aan
    cursor = connection.cursor()

    # Schrijf de SQL-instructie om de Studenten tabel aan te maken
    create_table_query = '''
    CREATE TABLE IF NOT EXISTS Students (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        age INTEGER,
        email TEXT
    );
    '''

    # Uitvoeren van de SQL-instructie
    cursor.execute(create_table_query)

    # Bevestig de wijzigingen
    connection.commit()

    # Toon een bevestigingsbericht
    print("Table 'Students' created successfully!")

IF NOT EXISTS: Dit zorgt ervoor dat de tabel alleen wordt aangemaakt als deze niet bestaat, waardoor er geen fouten optreden als de tabel al eerder is aangemaakt.
connection.commit(): Dit slaat (bevestigt) de wijzigingen op in de database.

Als u de bovenstaande Python-code uitvoert, zal het de Students-tabel in het bestand my_database.db aanmaken. U zult ook een bericht in de terminal zien dat bevestigt dat de tabel succesvol is aangemaakt.

Als u Visual Studio Code gebruikt, kunt u de extensie SQLite Viewer installeren om SQLite-databases te bekijken.

Data Types in SQLite and Their Mapping to Python

SQLite ondersteunt verschillende data types, die we moeten begrijpen bij het definiëren van onze tabellen. Hier volgt een snel overzicht van algemene SQLite data types en hoe ze worden gemapt naar Python-typen:

SQLite Data Type	Beschrijving	Python Equivalent
INTEGER	Hele getallen	`int`
TEXT	Tekst strings	`str`
REAL	Kommagetallen	`float`
BLOB	Binaire gegevens (bijv. afbeeldingen, bestanden)	`bytes`
NULL	代表无值或缺失数据	`None`

In onze Students tabel:

id is van type INTEGER, die overeenkomt met Python’s int.
name en email zijn van type TEXT, die overeenkomstig met Python’s str.
age is ook van het type INTEGER, dat overeenkomt met Python’s int.

Hoe u gegevens in een tabel kan invoeren

Nu we onze Students tabel hebben aangemaakt, is het tijd om te beginnen met het invoeren van gegevens in de database. In dit gedeelte zullen we kijken hoe u een enkele en meerdere records kunt invoeren met Python en SQLite, en hoe u veelvoorkomende veiligheidssproblemen als SQL-injectie kunt vermijden door het gebruik van parameterized queries.

Hoe u een enkele record kunt invoeren

Om gegevens in de database te kunnen invoeren, gebruiken we de SQL-commando INSERT INTO. Laten we beginnen met het invoeren van een enkele record in onze Students tabel.

Hier is de basis SQL-syntaxis voor het invoeren van een enkele record:

INSERT INTO Students (name, age, email) 
VALUES ('John Doe', 20, '[email protected]');

Hoewel we SQL direct in ons Python-script kunnen schrijven met hardcoded waarden, zullen we parameterized queries gebruiken om ons code veiliger en flexibeler te maken. Parameterized queries helpen SQL-injectie te voorkomen, een common aanval waarbij kwaadaardige gebruikers de SQL-query kunnen manipuleren door schadelijke invoer mee te geven.

Hier is hoe we een enkele record kunnen invoeren in de Students tabel met behulp van een parameterized query:

import sqlite3

# Gebruik 'with' om de verbinding automatisch te openen en te sluiten
with sqlite3.connect('my_database.db') as connection:
    cursor = connection.cursor()

    # Voeg een record toe aan de Studenten tabel
    insert_query = '''
    INSERT INTO Students (name, age, email) 
    VALUES (?, ?, ?);
    '''
    student_data = ('Jane Doe', 23, '[email protected]')

    cursor.execute(insert_query, student_data)

    # Committeer de wijzigingen automatisch
    connection.commit()

    # U hoeft geen connection.close(); te bellen, dit gebeurt automatisch!
    print("Record inserted successfully!")

De ? placeholder vertegenwoordigt de waarden die moeten worden ingevoegd in de tabel. De actuele waarden worden als een tupel (student_data) doorgegeven in de cursor.execute() methode.

Hoe om Meerdere Records In te Voegen

Als u meerdere records tegelijkertijd wilt invoegen, kunt u de executemany() methode in Python gebruiken. Deze methode neemt een lijst van tupels waar elke tuple een record voorstelt.

Om ons voorbeeld dynamischer te maken, kunnen we de Faker bibliotheek gebruiken om willekeurige studentendata te genereren. Dit is handig voor testen en het simuleren van echte wereld situaties.

from faker import Faker
import sqlite3

# Initializeer Faker
fake = Faker(['en_IN'])

# Gebruik 'with' om de verbinding automatisch te openen en te sluiten
with sqlite3.connect('my_database.db') as connection:
    cursor = connection.cursor()

    # Voeg een record toe aan de Studenten tabel
    insert_query = '''
    INSERT INTO Students (name, age, email) 
    VALUES (?, ?, ?);
    '''
    students_data = [(fake.name(), fake.random_int(
        min=18, max=25), fake.email()) for _ in range(5)]

    # Uitvoeren van de query voor meerdere records
    cursor.executemany(insert_query, students_data)

    # Committeer de wijzigingen
    connection.commit()

    # Toon de bevestigingsmelding
    print("Fake student records inserted successfully!")

In dit code:

Faker() genereert willekeurige namen, leeftijden en e-mailadressen voor studenten. Het doorgeven van de locale ([‘en_IN’]) is optioneel.
cursor.executemany(): Deze methode stelt ons in staat om tegelijkertijd meerdere records in te voeren, waardoor de code efficiënter wordt.
students_data: Een lijst van tuples waarin elke tuple de gegevens van een student weergeeft.

Hoe te Handelen bij Algemene Problemen: SQL Injection

SQL injectie is een veiligheidsmelder waarbij aanvallers SQL-query’s kunnen invoeren of manipuleren door schadelijke invoer te geven. Bijvoorbeeld, een aanvaller zou misschien proberen code zoals '; DROP TABLE Students; -- in te voeren om de tabel te verwijderen.

door het gebruik van parametergelijke query’s (zoals hierboven getoond), voorkom we dit probleem. De ? placeholder’s in parametergelijke query’s zorgen ervoor dat invoerwaarden als data behandeld worden, niet als onderdeel van de SQL-opdracht. Dit maakt het onmogelijk om kwaadaardige code uit te voeren.

Hoe om Data Op te Vragen

Nu we gegevens hebben ingevoerd in onze Studenten tabel, leren we hoe we de gegevens uit de tabel kunnen ophalen. We verkennen verschillende methoden voor het ophalen van gegevens in Python, waaronder fetchone(), fetchall() en fetchmany().

Om gegevens uit een tabel op te vragen, gebruiken we het SELECT statement. Hier is een eenvoudige SQL-opdracht om alle kolommen uit de tabel Studenten te selecteren:

SELECT * FROM Students;

Deze opdracht haalt alle records en kolommen op uit de tabel Studenten. We kunnen deze SELECT query in Python uitvoeren en de resultaten ophalen.

Hoe alle records ophalen

Hier wordt uitgelegd hoe we alle records uit de tabel Studenten kunnen ophalen:

import sqlite3

# Gebruik 'with' om verbinding te maken met de SQLite database
with sqlite3.connect('my_database.db') as connection:

    # Maak een cursorobject
    cursor = connection.cursor()

    # Schrijf de SQL-opdracht om alle records te selecteren uit de tabel Students tabel
    select_query = "SELECT * FROM Students;"

    # Voer de SQL-opdracht uit
    cursor.execute(select_query)

    # Alle records ophalen
    all_students = cursor.fetchall()

    # Resultaten weergeven in de terminal
    print("All Students:")
    for student in all_students:
        print(student)

In dit voorbeeld, haalt de methode fetchall() alle rijen op die door de query zijn geretourneerd als een lijst met tupels.

All Students:
(1, 'Jane Doe', 23, '[email protected]')
(2, 'Bahadurjit Sabharwal', 18, '[email protected]')
(3, 'Zayyan Arya', 20, '[email protected]')
(4, 'Hemani Shukla', 18, '[email protected]')
(5, 'Warda Kara', 20, '[email protected]')
(6, 'Mitali Nazareth', 19, '[email protected]')

Hoe een enkele record ophalen

Als u slechts één record wilt ophalen, kunt u de methode fetchone() gebruiken:

import sqlite3

# Gebruik 'with' om verbinding te maken met de SQLite-database
with sqlite3.connect('my_database.db') as connection:

    # Maak een cursorobject
    cursor = connection.cursor()

    # Schrijf het SQL-commando om alle records van de Studenten tabel te selecteren
    select_query = "SELECT * FROM Students;"

    # Voer het SQL-commando uit
    cursor.execute(select_query)

    # Haal een record op
    student = cursor.fetchone()

    # Toon het resultaat
    print("First Student:")
    print(student)

Uitvoer:

First Student:
(1, 'Jane Doe', 23, '[email protected]')

Hoe om meerdere records op te halen

Om een specifiek aantal records op te halen, kun je de functie fetchmany(size) gebruiken:

import sqlite3

# Gebruik 'with' om verbinding te maken met de SQLite-database
with sqlite3.connect('my_database.db') as connection:

    # Maak een cursorobject
    cursor = connection.cursor()

    # Schrijf het SQL-commando om alle records van de Studenten tabel te selecteren
    select_query = "SELECT * FROM Students;"

    # Voer het SQL-commando uit
    cursor.execute(select_query)

    # Haal drie records op
    three_students = cursor.fetchmany(3)

    # Toon de resultaten
    print("Three Students:")
    for student in three_students:
        print(student)

Uitvoer:

Three Students:
(1, 'Jane Doe', 23, '[email protected]')
(2, 'Bahadurjit Sabharwal', 18, '[email protected]')
(3, 'Zayyan Arya', 20, '[email protected]')

Hoe de `pandas`-bibliotheek gebruiken voor betere data-weergave

Voor betere data-weergave kunnen we de pandas-bibliotheek gebruiken om een DataFrame te maken van onze queryresultaten. Dit maakt het gemakkelijker om de data te bewerken en te visualiseren.

Hier is hoe je alle records op haalt en ze weergeeft als een pandas DataFrame:

import sqlite3
import pandas as pd

# Gebruik 'with' om verbinding te maken met de SQLite-database
with sqlite3.connect('my_database.db') as connection:
    # Schrijf het SQL-commando om alle records van de Studenten tabel te selecteren
    select_query = "SELECT * FROM Students;"

    # Gebruik pandas om een SQL-query direct in een DataFrame te lezen
    df = pd.read_sql_query(select_query, connection)

# Laat het DataFrame zien
print("All Students as DataFrame:")
print(df)

Uitvoer:

All Students as DataFrame:
   id                  name  age                        email
0   1              Jane Doe   23             [email protected]
1   2  Bahadurjit Sabharwal   18  [email protected]
2   3           Zayyan Arya   20  [email protected]
3   4         Hemani Shukla   18    [email protected]
4   5            Warda Kara   20           [email protected]
5   6       Mitali Nazareth   19          [email protected]

pd.read_sql_query()functie voert de SQL-query uit en geeft de resultaten direct terug als een pandas DataFrame.

Hoe gegevens bijwerken en verwijderen

In dit gedeelte leren we hoe bestaande records bij te werken en records uit onze Students tabel te verwijderen met behulp van SQL-commando’s in Python. Dit is essentieel voor het effectief beheren en behouden van uw gegevens.

Bijwerken van bestaande records

Om bestaande records in een database aan te passen, gebruiken we het SQL-commando UPDATE. Dit commando laat ons de waarden van specifieke kolommen in één of meer rijen wijzigen op basis van een specificeerde voorwaarde.

Bijvoorbeeld, als we de leeftijd van een student willen bijwerken, ziet het SQL-commando er ongeveer als dit uit:

UPDATE Students 
SET age = 21 
WHERE name = 'Jane Doe';

Nu laten we Python-code schrijven om de leeftijd van een specifieke student in onze Students tabel bij te werken.

import sqlite3

# Gebruik 'with' om verbinding te maken met de SQLite-database
with sqlite3.connect('my_database.db') as connection:
    cursor = connection.cursor()

    # SQL-commando om de leeftijd van een student bij te werken
    update_query = '''
    UPDATE Students 
    SET age = ? 
    WHERE name = ?;
    '''

    # Gegevens voor de update
    new_age = 21
    student_name = 'Jane Doe'

    # Voer het SQL-commando uit met de gegevens
    cursor.execute(update_query, (new_age, student_name))

    # Bevestig de wijzigingen om de update op te slaan
    connection.commit()

    # Toon een bevestigingsbericht
    print(f"Updated age for {student_name} to {new_age}.")

In dit voorbeeld hebben we geparameteriseerde queries gebruikt om SQL-injectie te voorkomen.

Hoe te Verwijderen van records uit de tabel

Om records uit een database te verwijderen, gebruiken we het SQL-commando DELETE. dit commando laat ons toe om één of meer rijen op basis van een specifieke voorwaarde te verwijderen.

Bijvoorbeeld, als we een student genaamd ‘Jane Doe’ willen verwijderen, zou het SQL-commando er ongeveer zo uitzien:

DELETE FROM Students 
WHERE name = 'Jane Doe';

Laten we Python-code schrijven om een specifieke student uit onze Students tabel te verwijderen met behulp van het with statement.

import sqlite3

# Gebruik 'with' om verbinding te maken met de SQLite-database
with sqlite3.connect('my_database.db') as connection:
    cursor = connection.cursor()

    # SQL-commando om een student te verwijderen
    delete_query = '''
    DELETE FROM Students 
    WHERE name = ?;
    '''

    # Naam van de student die moet worden verwijderd
    student_name = 'Jane Doe'

    # Voer het SQL-commando uit met de gegevens
    cursor.execute(delete_query, (student_name,))

    # Bevestig de wijzigingen om de verwijdering op te slaan
    connection.commit()

    # Toon een bevestigingsbericht
    print(f"Deleted student record for {student_name}.")

Belangrijke Overwegingen

Voorwaarden: Gebruik altijd de WHERE-clausule bij het bijwerken of verwijderen van records om te voorkomen dat alle rijen in de tabel worden gewijzigd of verwijderd. Zonder een WHERE-clausule beïnvloedt het commando elke rij in de tabel.
Back-up: Het is een goede gewoonte om een back-up te maken van uw database voordat u updates of verwijderingen uitvoert, vooral in productieomgevingen.

Hoe transacties te gebruiken

Een transactie is een reeks van één of meer SQL-bewerkingen die als een enkele eenheid van werk worden behandeld. In de context van een database biedt een transactie u de mogelijkheid om meerdere bewerkingen uit te voeren die ofwel allemaal slagen of helemaal niet. Dit zorgt ervoor dat uw database in een consistente staat blijft, zelfs bij fouten of onverwachte problemen.

Bijvoorbeeld, als u geld overmaakt tussen twee bankrekeningen, wilt u dat zowel de afschrijving van de ene rekening als de bijschrijving op de andere rekening samen slagen of falen. Als een bewerking mislukt, mag de andere niet worden uitgevoerd om consistentie te behouden.

Waarom transacties gebruiken?

Atomariteit: Transacties zorgen ervoor dat een reeks van operationele stappen als één eenheid behandeld worden. Als één operatie mislukt, worden geen van de operationen toegepast op de database.
Consistentie: Transacties helpen de integriteit van de database te behouden door er voor te zorgen dat alle regels en constraints gevolgd worden.
Isolatie: Elke transactie werkt onafhankelijk van andere transacties, waardoor onbedoelde interferentie wordt voorkomen.
Duurzaamheid: Als een transactie is gecomitteerd, zijn de wijzigingen permanent, zelfs bij een systeemfout.

Wanneer moet je transacties gebruiken?

Gebruik transacties wanneer:

U meerdere gerelateerde operationele stappen uitvoert die samen moeten slagen of mislukken.
U verandert kritische gegevens die consistentie en integriteit vereisen.
Werken met operationele taken die potentiëel mislukken kunnen, zoals financiële transacties of gegevensmigraties.

Hoe Transacties in Python Beheerst Worden

In SQLite worden transacties beheerd met behulp van de commando’s BEGIN, COMMIT en ROLLBACK. Echter, bij gebruik van het sqlite3-module in Python, beheert u transacties meestal via het verbindingsobject.

Starten van een Transactie

Een transactie begint impliciet als u elke SQL-instructie uitvoert. Om een transactie expliciet te starten, kunt u het BEGIN-commando gebruiken:

cursor.execute("BEGIN;")

Hoewel het meestal onnodig is om een transactie handmatig te starten, start SQLite een transactie automatisch als u een SQL-instructie uitvoert.

Hoe een Transactie Wordt vastgelegd

Om alle wijzigingen die tijdens een transactie zijn aangebracht op te slaan, gebruikt u de commit()-methode. Dit maakt alle wijzigingen permanent in de database.

connection.commit()

We hebben de commit()-methode reeds in de bovenstaande voorbeelden gebruikt.

Terugdraaien van een Transactie

Als iets misgaat en u de wijzigingen die tijdens de transactie zijn aangebracht wilt herstellen, kunt u de rollback()-methode gebruiken. Dit zal alle wijzigingen ongedaan maken sinds de transactie is begonnen.

connection.rollback()

Voorbeeld van het gebruik van transacties in Python.

Om het gebruik van transacties in een echte wereld scenario weer te geven, zullen we een nieuwe tabel aanmaken genaamd Klanten om klantenrekeningen te beheren. In dit voorbeeld zullen we iedere klant een saldo aan voorstellen. We zullen twee klanten aan deze tabel toevoegen en een financiële overschrijving uitvoeren tussen hen.

Eerstens, laten we de Klanten tabel aanmaken en twee klanten toevoegen:

import sqlite3

# Maak de Klanten tabel aan en voeg twee klanten toe
with sqlite3.connect('my_database.db') as connection:
    cursor = connection.cursor()

    # Maak Klanten tabel aan
    create_customers_table = '''
    CREATE TABLE IF NOT EXISTS Customers (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL UNIQUE,
        balance REAL NOT NULL
    );
    '''
    cursor.execute(create_customers_table)

    # Voeg twee klanten toe
    cursor.execute(
        "INSERT INTO Customers (name, balance) VALUES (?, ?);", ('Ashutosh', 100.0))
    cursor.execute(
        "INSERT INTO Customers (name, balance) VALUES (?, ?);", ('Krishna', 50.0))

    connection.commit()

Nu laten we de financiële overschrijving uitvoeren tussen Ashutosh en Krishna:

import sqlite3


def transfer_funds(from_customer, to_customer, amount):
    with sqlite3.connect('my_database.db') as connection:
        cursor = connection.cursor()

        try:
            # Start een transactie
            cursor.execute("BEGIN;")

            # Verminder bedrag van de afzender
            cursor.execute(
                "UPDATE Customers SET balance = balance - ? WHERE name = ?;", (amount, from_customer))
            # Voeg bedrag toe aan de ontvanger
            cursor.execute(
                "UPDATE Customers SET balance = balance + ? WHERE name = ?;", (amount, to_customer))

            # Committeer de wijzigingen
            connection.commit()
            print(
                f"Transferred {amount} from {from_customer} to {to_customer}.")

        except Exception as e:
            # Als er een fout optreedt, rol terug de transactie
            connection.rollback()
            print(f"Transaction failed: {e}")


# Voorbeeld van gebruik
transfer_funds('Ashutosh', 'Krishna', 80.0)

In dit voorbeeld hebben we eerst een tabel Klanten gecreëerd en twee klanten ingevoegd, Ashutosh met een saldo van ₹100 en Krishna met een saldo van ₹50. We hebben vervolgens ₹80 overgeschreven van Ashutosh naar Krishna. door gebruik te maken van transacties, we zorgen ervoor dat zowel de debet van Ashutoshs account als de credit aan Krishna’s account als een enkele atomaire operatie worden uitgevoerd, het behoud van data integriteit in geval van eventuele fouten. Als de overschrijving mislukt (bijvoorbeeld door ontoereikende financiën), zal de transactie terugrollen, zonder de accounts te veranderen.

Hoe u SQLite-query prestaties optimaliseert met indexering

Indexering is een krachtige techniek die wordt gebruikt in databases om query-prestaties te verbeteren. Een index is essentieel een datastructuur die de locatie van rijen op basis van specifieke kolomwaarden opslaat, net zoals een index aan het eind van een boek helpt u snel een onderwerp te vinden.

Zonder een index moet SQLite de gehele tabel rij voor rij doorzoeken om de relevante gegevens te vinden, wat inefficiënt wordt als het gegevensset groeit. Door een index te gebruiken, kan SQLite direct naar de nodige rijen springen, wat de uitvoering van query’s significant versnelt.

Hoe u de database met valse gegevens populeert

Om de impact van indexering effectief te testen, hebben we een groot gegevensset nodig. In plaats van handmatig records toe te voegen, kunnen we de faker-bibliotheek gebruiken om snel valse gegevens te genereren. In deze sectie zullen we 10.000 valse records genereren en ze invoegen in onze tabel Studenten. Dit zal een realistische scenario simuleren waarin databases groot worden en de query-prestaties van belang zijn.

We zullen de executemany() methode gebruiken om de records in te voeren zoals onderstaand:

import sqlite3
from faker import Faker

# Initializeer de Faker bibliotheek
fake = Faker(['en_IN'])


def insert_fake_students(num_records):
    """Generate and insert fake student data into the Students table."""
    fake_data = [(fake.name(), fake.random_int(min=18, max=25),
                  fake.email()) for _ in range(num_records)]

    # Gebruik 'with' om de database verbinding te behandelen
    with sqlite3.connect('my_database.db') as connection:
        cursor = connection.cursor()

        # Voeg fictieve gegevens toe aan de Students tabel
        cursor.executemany('''
        INSERT INTO Students (name, age, email) 
        VALUES (?, ?, ?);
        ''', fake_data)

        connection.commit()

    print(f"{num_records} fake student records inserted successfully.")


# Voeg 10.000 fictieve records toe aan de Students tabel
insert_fake_students(10000)

Door deze script te draaien, worden 10.000 fictieve studentenrecords toegevoegd aan de Students tabel. In het volgende gedeelte zullen we de database aanvragen en de prestaties van queries met en zonder indexering vergelijken.

Hoe te Query Zonder Indexen

In dit gedeelte zullen we de Students tabel queryen zonder enige indexen om te observeren hoe SQLite presteert als er geen optimalisaties zijn ingesteld. Dit zal dienen als een basislijn om de prestaties later te vergelijken toen we indexen toevoegen.

Zonder indexen voert SQLite een volledige tabelscan uit, wat betekent dat het elke rij in de tabel moet controleren om overeenkomsten te vinden. Voor kleine datasets is dit aanvaardbaar, maar als het aantal records toeneemt, wordt de tijd die nodig is om te zoeken dramatisch toegenomen. Laten we dit zien door een basis SELECT query te draaien om een specifieke student naar zijn naam te zoeken en te meten hoe lang het duurt om uit te voeren.

Eerst zal we de Students tabel queryen door een student op zoek te doen met een specifieke naam. We zullen de tijd die nodig is om de query uit te voeren loggen met behulp van Python’s time module om de prestaties te meten.

import sqlite3
import time


def query_without_index(search_name):
    """Query the Students table by name without an index and measure the time taken."""

    # Maak verbinding met de database met behulp van 'with'
    with sqlite3.connect('my_database.db') as connection:
        cursor = connection.cursor()

        # Meet de starttijd
        start_time = time.perf_counter_ns()

        # Voer een SELECT-query uit om een student op naam te zoeken
        cursor.execute('''
        SELECT * FROM Students WHERE name = ?;
        ''', (search_name,))

        # Haal alle resultaten op (er zou in de praktijk maar een of enkele moeten zijn)
        results = cursor.fetchall()

        # Meet de eindtijd
        end_time = time.perf_counter_ns()

        # Bereken de totale tijd die is nodig
        elapsed_time = (end_time - start_time) / 1000

        # Toon de resultaten en de gemiste tijd
        print(f"Query completed in {elapsed_time:.5f} microseconds.")
        print("Results:", results)


# Voorbeeld: Zoeken naar een student op naam
query_without_index('Ojasvi Dhawan')

Hier is de uitvoer:

Query completed in 1578.10000 microseconds.
Results: [(104, 'Ojasvi Dhawan', 21, '[email protected]')]

door het uitvoeren van het bovenstaande script zult u zien hoe lang het duurt om door de tabel Students te zoeken zonder enige indexen. Bijvoorbeeld, als er 10.000 records in de tabel zijn, kan de query ongeveer 1000-2000 microseconden duren, afhankelijk van de grootte van de tabel en uw hardware. Dat zou niet te langzaam lijken voor een kleine gegevensset, maar de prestaties zullen afnemen als er meer records toegevoegd worden.

We gebruiken time.perf_counter_ns() om de tijd die is nodig voor de uitvoering van de query in nanosecleden te meten. Deze methode is zeer nauwkeurig voor het benchmarksmaken van kleine tijdsintervallen. We converteren de tijd naar microseconden (us) voor een gemakkelijkere leesbaarheid.

Inleiding tot het Query Plan

Bij het werken met databases kun je de prestaties verbeteren en performance bottlenecks detecteren door te begrijpen hoe queries worden uitgevoerd. SQLite biedt hiervoor een nuttig hulpmiddel aan onder de naam EXPLAIN QUERY PLAN, dat je toelaat de stappen die SQLite onderneemt om gegevens op te halen te analyseren.

In deze sectie zullen we uitleggen hoe je EXPLAIN QUERY PLAN kunt gebruiken om de werking van een query te visualiseren en te begrijpen — specifiek, hoe SQLite een volledige tabelscan uitvoert wanneer geen index aanwezig is.

Ga nu EXPLAIN QUERY PLAN gebruiken om te zien hoe SQLite gegevens uit de tabel Students oproept zonder enige indexen. We zoeken een student op naam, en de query plan zal de stappen die SQLite onderneemt om de overeenkomstige rijen te vinden onthullen.

import sqlite3


def explain_query(search_name):
    """Explain the query execution plan for a SELECT query without an index."""

    with sqlite3.connect('my_database.db') as connection:
        cursor = connection.cursor()

        # Gebruik EXPLAIN QUERY PLAN om te analyseren hoe de query wordt uitgevoerd
        cursor.execute('''
        EXPLAIN QUERY PLAN
        SELECT * FROM Students WHERE name = ?;
        ''', (search_name,))

        # Haal en tonen van de query plan
        query_plan = cursor.fetchall()

        print("Query Plan:")
        for step in query_plan:
            print(step)


# Voorbeeld: Analyseren van het query plan voor zoekopdrachten op naam
explain_query('Ojasvi Dhawan')

Als je deze code uitvoert, zal SQLite een overzicht teruggeven van hoe het de query gaat uitvoeren. Hier is een voorbeeld van hoe de uitvoer er misschien uit zou kunnen zien:

Query Plan:
(2, 0, 0, 'SCAN Students')

Dit geeft aan dat SQLite de gehele tabel Students scant (een volledige tabelscan) om de rijen te vinden waar de kolom name overeenkomt met de gegeven waarde (Ojasvi Dhawan). Aangezien er geen index op de kolom name is, moet SQLite elke rij in de tabel controleren.

Hoe maak je een index?

Het aanmaken van een index op een kolom laat SQLite sneller rijen vinden tijdens query-bewerkingen. In plaats van de hele tabel te doorzoeken, kan SQLite de index gebruiken om direct naar de relevante rijen te springen, wat de query significant versnellen – vooral bij grote datasets.

Om een index te maken, gebruikt u het volgende SQL-commando:

CREATE INDEX IF NOT EXISTS index-name ON table (column(s));

In dit voorbeeld zal een index worden aangemaakt op de kolom name van de tabel Students. U kunt dit doen met Python als volgt:

import sqlite3
import time


def create_index():
    """Create an index on the name column of the Students table."""
    with sqlite3.connect('my_database.db') as connection:
        cursor = connection.cursor()

        # SQL-commando om een index aan te maken op de naamkolom
        create_index_query = '''
        CREATE INDEX IF NOT EXISTS idx_name ON Students (name);
        '''

        # Meet de starttijd
        start_time = time.perf_counter_ns()

        # Uitvoeren van het SQL-commando om de index aan te maken
        cursor.execute(create_index_query)

        # Meet de starttijd
        end_time = time.perf_counter_ns()

        # Committeer de wijzigingen
        connection.commit()

        print("Index on 'name' column created successfully!")

        # Bereken de totale tijd die is nodig
        elapsed_time = (end_time - start_time) / 1000

        # Toon de resultaten en de nodiggebrachte tijd
        print(f"Query completed in {elapsed_time:.5f} microseconds.")


# Roep de functie aan om de index aan te maken
create_index()

Uitvoer:

Index on 'name' column created successfully!
Query completed in 102768.60000 microseconds.

Hoewel het aanmaken van de index lang duurt (102768,6 microseconden), is dit eenmalige handeling. U zult nog steeds een significante snelheidsverhoging krijgen bij het uitvoeren van meerdere query’s. In de volgende secties zullen we de database opnieuw aanvragen om de prestatieverbeteringen die mogelijk zijn door deze index te observeren.

Hoe query’s met indexen uit te voeren.

In dit gedeelte zullen we dezelfde SELECT-query uitvoeren die we eerder hebben uitgevoerd, maar deze keer zullen we profiteren van het index dat we hebben aangemaakt op de kolom name van de tabel Students. We zullen de uitvoeringstijd meten en loggen om de performanceverbeteringen die het index biedt te observeren.

import sqlite3
import time


def query_with_index(student_name):
    """Query the Students table using an index on the name column."""
    with sqlite3.connect('my_database.db') as connection:
        cursor = connection.cursor()

        # SQL-commando om een student op naam te selecteren
        select_query = 'SELECT * FROM Students WHERE name = ?;'

        # Meet de uitvoeringstijd
        start_time = time.perf_counter_ns()  # Start de timer

        # Uitvoeren van de query met de opgegeven studentennaam
        cursor.execute(select_query, (student_name,))
        result = cursor.fetchall()  # Haal alle resultaten op

        end_time = time.perf_counter_ns()  # Stop de timer

        # Bereken de verstreken tijd in microseconden
        execution_time = (end_time - start_time) / 1000

        # Toon resultaten en uitvoeringstijd
        print(f"Query result: {result}")
        print(f"Execution time with index: {execution_time:.5f} microseconds")


# Voorbeeld: Zoeken naar een student op naam
query_with_index('Ojasvi Dhawan')

Hier is wat we krijgt in de uitvoer:

Query result: [(104, 'Ojasvi Dhawan', 21, '[email protected]')]
Execution time with index: 390.70000 microseconds

We kunnen een significante vermindering in de uitvoeringstijd vergelijken ten opzichte van toen de query werd uitgevoerd zonder een index.

Bekijk nu het queryuitvoeringsplan voor de query met het index op de kolom name van de tabel Students. Als u hetzelfde script opnieuw uitvoert om de query te verklaren, krijgt u het volgende resultaat:

Query Plan:
(3, 0, 0, 'SEARCH Students USING INDEX idx_name (name=?)')

Het plan toont nu dat de query het index idx_name gebruikt, waardoor het aantal rijen dat moet worden doorzocht significant wordt verminderd, wat tot snellere queryuitvoering leidt.

Vergelijken van Prestatiegegevens

Laat ons nu de prestatiegegevens samenvatten die we verkregen bij het uitvoeren van queries met en zonder indexen.

Vergelijking van Uitvoeringstijd

Query Type	Uitvoeringstijd (microseconden)
Zonder Index	1578.1
Met Index	390.7

Overzicht van Prestatieverbetering

De query met de index is ongeveer 4.04 keer sneller dan de query zonder de index.
De uitvoeringstijd is met ongeveer 75.24% verbeterd na het toevoegen van de index.

Best Practices bij het Gebruik van Indexen

Indexen kunnen de prestaties van uw SQLite-database aanzienlijk verbeteren, maar ze moeten voorzichtig worden gebruikt. Hier zijn enkele best practices om te overwegen bij het werken met indexen:

Wanneer en Waarom Indexen Te Gebruiken

Veelgebruikte Querykolommen: Gebruik indexen op kolommen die veel worden gebruikt in SELECT-queries, vooral diegenen die worden gebruikt in WHERE, JOIN en ORDER BY-clausules. Dit omdat het indexeren van deze kolommen de uitvoeringstijd van queries aanzienlijk kan verminderen.
Unieke beperkingen: Als u kolommen heeft die unieke waarden moeten bevatten (zoals gebruikersnamen of e-mailadressen), kunt u deze beperking efficiënt handhaven door een index aan te maken.
Grote datasets: Voor tabellen met een groot aantal records worden indexen steeds nuttiger. Ze stelden snelle zoektochten mogelijk, wat essentieel is voor het behoud van de prestaties terwijl uw gegevens groeien.
Composietindexen: Overweeg het maken van composietindexen voor queries die door meerdere kolommen worden gefilterd of gesorteerd. Bijvoorbeeld, als u vaak zoekt naar studenten op basis van naam en leeftijd, kan een index op beide kolommen deze queries optimaliseren.

Mogelijke nadelen van indexen

Alhoewel indexen significante voordelen bieden, zijn er wel enkele mogelijke nadelen:

Tragere invoer/bijwerkingen: Wanneer u records in een tabel met indexen invoert of bijwerkt, moet SQLite de index ook bijwerken, wat deze operaties kan vertragen. Dit komt omdat elke invoer of bijwerking extra overhead vereist om de indexstructuur te behouden.
Gerekende opslagvereisten: Indexen veroorzaken extra schijfruimte. Voor grote tabellen kan de opslagkost zijn aanzienlijk. Overweeg dit bij het ontwerpen van uw database schema, vooral voor systemen met beperkte opslagbronnen.
Complexe Indexbeheer: Te veel indexen kunnen het beheer van de database complexer maken. Het kan leiden tot situaties waarin er redundante indexen zijn, die de prestaties kunnen verminderen in plaats van te verhogen. Het regelmatig beoordelen en optimaliseren van uw indexen is een goed gebruik.

Indexen zijn krachtige tools voor het optimaliseren van databasequery’s, maar ze vereisen voorzichtige aanpak. Het vinden van een evenwicht tussen verbeterde leesprestaties en de potentiële overhead op schrijfoperaties is belangrijk. Hier zijn enkele strategieën voor het bereiken van dit evenwicht:

Monitoreer Queryprestaties: Gebruik SQLite’s EXPLAIN QUERY PLAN om te analyseren hoe uw query’s met en zonder indexen uitgevoerd worden. Dit kan helpen vaststellen welke indexen nuttig zijn en welke misschien onnodig zijn.
Regelmatige Onderhoud:周期性评审您的索引，以评估它们是否仍然需要。移除冗余的或很少使用的索引，以简化数据库操作。
Testen en evalueren: Voordat u indexen implementeert in een productieomgeving, voer uitgebreide testen uit om hun impact op zowel lees als schrijfoperaties te begrijpen.

door deze best practices te volgen, kunt u de voordelen van indexering benutten terwijl u potentieel nadelen minimaliseert, uiteindelijk de prestaties en efficiëntie van uw SQLite-database verbeteringen.

Hoe fouten en uitzonderingen af te handelen

In dit gedeelte zal gesproken worden over hoe fouten en uitzonderingen te behandelen bij het werken met SQLite in Python. Goed foutbehandelen is cruciaal voor het behoud van de integriteit van uw database en het voorrecht om uw toepassing voorspelbaar te laten functioneren.

Verschillende fouten bij SQLite-bewerkingen

Bij het interacteren met een SQLite-database kunnen verschillende algemeenne fouten optreden:

Violatie van de constraints: Dit gebeurt wanneer u probeert om gegevens in te voeren of bij te werken die de database-constraints overtreden, zoals primaire sleutelunieke of externe sleutelverplichtingen. Bijvoorbeeld, proberen om een dubbel bestaande primaire sleutel in te voeren zal een fout veroorzaken.
Type Mismatches: Het proberen invoeren van gegevens van het verkeerde type (bijvoorbeeld, het invoeren van een tekst waar een getal verwacht wordt) kan leiden tot een fout.
Database Vergrendelde Fouten: Als een database door een andere proces of verbinding wordt aangepast, kan het proberen deze te bereiken leiden tot een “database is vergrendeld”-fout.
Syntax Fouten: Fouten in uw SQL-syntaxis zullen leiden tot fouten als u probeert uw commando’s uit te voeren.

Hoe gebruikt u Python’s uitzonderingsbehandeling?

Python’s ingebouwde exceptionsbehandeling (try en except) is essentieel voor het beheren van fouten bij SQLite-operaties. Door deze constructies te gebruiken, kun je fouten vastleggen en adequate reacties uitvoeren zonder je programma te laten crashen.

Hier is een eenvoudig voorbeeld van hoe fouten te handelen bij het invoeren van data in de database:

import sqlite3


def add_customer_with_error_handling(name, balance):
    """Add a new customer with error handling."""
    try:
        with sqlite3.connect('my_database.db') as connection:
            cursor = connection.cursor()
            cursor.execute(
                "INSERT INTO Customers (name, balance) VALUES (?, ?);", (name, balance))
            connection.commit()
            print(f"Added customer: {name} with balance: {balance}")

    except sqlite3.IntegrityError as e:
        print(f"Error: Integrity constraint violated - {e}")

    except sqlite3.OperationalError as e:
        print(f"Error: Operational issue - {e}")

    except Exception as e:
        print(f"An unexpected error occurred: {e}")


# Voorbeeld van gebruik
add_customer_with_error_handling('Vishakha', 100.0)  # Geldig
add_customer_with_error_handling('Vishakha', 150.0)  # Dubbele invoer

In dit voorbeeld:

We vangen IntegrityError op, die wordt gegooid voor overtredingen zoals unieke voorwaarden.
We vangen OperationalError op voor algemene databasegerelateerde problemen (zoals database vergrendelingsfouten).
We hebben ook een algemene except-blok om elke onverwachte uitzondering af te handelen.

Uitvoer:

Added customer: Vishakha with balance: 100.0
Error: Integrity constraint violated - UNIQUE constraint failed: Customers.name

Best practices voor het waarborgen van databaseintegriteit

Gebruik Transacties

: Gebruik altijd transacties (zoals besproken in de vorige sectie) bij het uitvoeren van meerdere gerelateerde operationele taken. Dit helpt ervoor te zorgen dat of alle taken gelukkig zijn of geen zijn, het behoud van consistentie.
Valideer Invoergegevens: Voordat u SQL-commando’s uitvoert, valideer de invoergegevens om ervoor te zorgen dat ze aan de verwachte criteria voldoen (bijvoorbeeld correcte types, binnen toegestane bereiken).
Catch Specifieke Uitzonderingen: Valideer altijd specifieke uitzonderingen om verschillende typen fouten juist te behandelen. Dit maakt errorhandling en debugging duidelijker.
Log Fouten: In plaats van fouten alleen te printen naar het scherm, overweeg ze ook aan te loggen naar een bestand of een monitorningsysteem. Dit helpt u issues in de productie te volgen.
Gracieuze Degradatie: Ontwerp uw applicatie zodanig dat fouten op een gracieuze manier worden afgehandeld. Als een operatie mislukt, geef dan zinvolle feedback aan de gebruiker in plaats van dat de applicatie crasht.
Regelmatig Back-up van Gegevens: Maak regelmatig een back-up van uw database om gegevensverlies te voorkomen in geval van kritieke storingen of corruptie.
Gebruik Voorbereide Verklaringen: Voorbereide verklaringen helpen SQL-injectieaanvallen te voorkomen en kunnen ook betere prestaties bieden voor herhaalde query’s.

Hoe Gegevens Exporteren en Importeren [Bonussectie]

In deze sectie zullen we leren hoe gegevens uit een SQLite-database kunnen worden geëxporteerd naar gangbare formaten zoals CSV en JSON, en hoe gegevens in SQLite kunnen worden geïmporteerd vanuit deze formaten met behulp van Python. Dit is handig voor het delen van gegevens, back-up en integratie met andere applicaties.

Gegevens Exporteren van SQLite naar CSV

Het exporteren van gegevens naar een CSV (Comma-Separated Values) bestand is eenvoudig met de ingebouwde bibliotheken van Python. CSV-bestanden worden veel gebruikt voor gegevensopslag en -uitwisseling, waardoor ze een handig formaat zijn voor het exporteren van gegevens.

Zo kun je gegevens exporteren uit een SQLite-tabel naar een CSV-bestand:

import sqlite3
import csv

def export_to_csv(file_name):
    """Export data from the Customers table to a CSV file."""
    with sqlite3.connect('my_database.db') as connection:
        cursor = connection.cursor()

        # Voer een query uit om alle klantgegevens op te halen
        cursor.execute("SELECT * FROM Customers;")
        customers = cursor.fetchall()

        # Schrijf gegevens naar CSV
        with open(file_name, 'w', newline='') as csv_file:
            csv_writer = csv.writer(csv_file)
            csv_writer.writerow(['ID', 'Name', 'Balance'])  # Schrijf kopregel
            csv_writer.writerows(customers)  # Schrijf gegevensregels

        print(f"Data exported successfully to {file_name}.")

# Voorbeeld van gebruik
export_to_csv('customers.csv')

Hoe gegevens naar JSON te exporteren

Hetzelfde kan je doen om gegevens te exporteren naar een JSON-bestand (JavaScript Object Notation), wat een populair formaat is voor gegevensuitwisseling, vooral in webapplicaties.

Hieronder staat een voorbeeld van hoe gegevens naar JSON geëxporteerd kunnen worden:

import json
import sqlite3


def export_to_json(file_name):
    """Export data from the Customers table to a JSON file."""
    with sqlite3.connect('my_database.db') as connection:
        cursor = connection.cursor()

        # Voer een query uit om alle klantgegevens op te halen
        cursor.execute("SELECT * FROM Customers;")
        customers = cursor.fetchall()

        # Convert gegevens naar een lijst van woordenlijsten
        customers_list = [{'ID': customer[0], 'Name': customer[1],
                           'Balance': customer[2]} for customer in customers]

        # Schrijf gegevens naar JSON
        with open(file_name, 'w') as json_file:
            json.dump(customers_list, json_file, indent=4)

        print(f"Data exported successfully to {file_name}.")


# Voorbeeld van gebruik
export_to_json('customers.json')

Hoe gegevens uit CSV naar SQLite te importeren

Je kunt ook gegevens importeren vanuit een CSV-bestand naar een SQLite-database. Dit is handig voor het inpopuleren van je database met bestaande gegevenssets.

Hieronder staat hoe je gegevens uit een CSV-bestand kunt importeren:

import csv
import sqlite3


def import_from_csv(file_name):
    """Import data from a CSV file into the Customers table."""
    with sqlite3.connect('my_database.db') as connection:
        cursor = connection.cursor()

        # Open CSV bestand voor lezen
        with open(file_name, 'r') as csv_file:
            csv_reader = csv.reader(csv_file)
            next(csv_reader)  # Spring over de kopregel

            # Voeg elke regel toe aan de klanten tabel
            for row in csv_reader:
                cursor.execute(
                    "INSERT INTO Customers (name, balance) VALUES (?, ?);", (row[1], row[2]))

        connection.commit()
        print(f"Data imported successfully from {file_name}.")


# Voorbeeld gebruik
import_from_csv('customer_data.csv')

Hoe om gegevens van JSON in SQLite te importeren

Hetzelfde geldt voor het importeren van gegevens uit een JSON-bestand. U kunt het JSON-bestand lezen en de gegevens in uw SQLite-tabel invoeren.

Hier is hoe u dat doet:

import json
import sqlite3


def import_from_json(file_name):
    """Import data from a JSON file into the Customers table."""
    with sqlite3.connect('my_database.db') as connection:
        cursor = connection.cursor()

        # Open JSON-bestand voor lezen
        with open(file_name, 'r') as json_file:
            customers_list = json.load(json_file)

            # Voeg elke klant toe aan de klanten tabel
            for customer in customers_list:
                cursor.execute("INSERT INTO Customers (name, balance) VALUES (?, ?);", (customer['Name'], customer['Balance']))

        connection.commit()
        print(f"Data imported successfully from {file_name}.")


# Voorbeeld gebruik
import_from_json('customer_data.json')

Einde van het samenvattingsgedeelte

En dat is het! Deze gids heeft u geïnformeerd over de basis van het werken met SQLite in Python, inclusief het instellen van uw omgeving, het uitvoeren van query’s en het manipuleren van gegevens, evenals het exporteren en importeren van informatie. Ik hoop dat het nuttig vonden en dat het u geïnteresseerd heeft in het gebruik van SQLite voor uw projecten.

Nu is het tijd om uw nieuw gevonden kennis te testen! Ik rad u aan uw project met behulp van SQLite en Python te maken. Of het nu eenvoudig is om uw bibliotheek te beheren, een budgetgereedschap of iets unieks, de mogelijkheden zijn oneindig.

Als u uw project hebt voltooid, delen u het op Twitter en tag me! Ik zou het graag zien wat u heeft gecreëerd en uw succes vieren.

U kunt alle code uit deze handleiding vinden op GitHub. Bedankt voor het volgen en vrolijk programmeren!

Genereer gratis een inhoudsopgave voor uw freeCodeCamp-artikelen met behulp van de TOC Generator-tool.

Hoe je jouw Python-omgeving instelt

Python installeren

SQLite3-module installeren

Hoe u een virtuele omgeving creëert (Optioneel, maar aanbevolen)

Installeer noodzakelijke bibliotheken

Hoe u een SQLite-database kunt aanmaken

Hoe SQLite-databases werken

Hoe een Nieuwe SQLite Database Te Maken

Verbinding Maken Met de Database

Cursor Aanmaken

Verbinding Sluiten

Hoe gebruik je een contextbeheerder om verbindingen te openen en te sluiten

Hoe u database tabellen aanmaakt

Data Types in SQLite and Their Mapping to Python

Hoe u gegevens in een tabel kan invoeren

Hoe u een enkele record kunt invoeren

Hoe om Meerdere Records In te Voegen

Hoe te Handelen bij Algemene Problemen: SQL Injection

Hoe om Data Op te Vragen

Hoe alle records ophalen

Hoe een enkele record ophalen

Hoe om meerdere records op te halen

Hoe de pandas-bibliotheek gebruiken voor betere data-weergave

Hoe gegevens bijwerken en verwijderen

Bijwerken van bestaande records

Hoe te Verwijderen van records uit de tabel

Belangrijke Overwegingen

Hoe transacties te gebruiken

Waarom transacties gebruiken?

Wanneer moet je transacties gebruiken?

Hoe Transacties in Python Beheerst Worden

Starten van een Transactie

Hoe een Transactie Wordt vastgelegd

Terugdraaien van een Transactie

Voorbeeld van het gebruik van transacties in Python.

Hoe u SQLite-query prestaties optimaliseert met indexering

Hoe u de database met valse gegevens populeert

Hoe te Query Zonder Indexen

Inleiding tot het Query Plan

Hoe maak je een index?

Hoe query’s met indexen uit te voeren.

Vergelijken van Prestatiegegevens

Vergelijking van Uitvoeringstijd

Overzicht van Prestatieverbetering

Best Practices bij het Gebruik van Indexen

Wanneer en Waarom Indexen Te Gebruiken

Mogelijke nadelen van indexen

Hoe fouten en uitzonderingen af te handelen

Verschillende fouten bij SQLite-bewerkingen

Hoe gebruikt u Python’s uitzonderingsbehandeling?

Best practices voor het waarborgen van databaseintegriteit

Hoe Gegevens Exporteren en Importeren [Bonussectie]

Gegevens Exporteren van SQLite naar CSV

Hoe gegevens naar JSON te exporteren

Hoe gegevens uit CSV naar SQLite te importeren

Hoe om gegevens van JSON in SQLite te importeren

Einde van het samenvattingsgedeelte

Hoe de `pandas`-bibliotheek gebruiken voor betere data-weergave