SQLite en Python

Op deze pagina:

SQLite is een complete SQL database in een bestand. SQLite wordt standaard meegeleverd met Python en kan eenvoudig gebruikt worden in Python scripts zonder dat je extra software hoeft te installeren.

Verbinding maken met een SQLite database

Wanneer je een verbinding maakt met een SQLite database, gebruik je gegevens die in een bestand op je computer staan. Als je een databasenaam opgeeft die nog niet bestaat, dan wordt deze database aangemaakt. Je kunt verbinden met een SQLite database via de sqlite3 module, deze moet je importeren aan het begin van je Python script. De naam voor de database mag je zelf kiezen, dit wordt de naam van het database bestand. Deze naam mag ook een pad bevatten, maar de directories in dat pad moeten al bestaan, SQLite maakt ze niet zelf aan.In het voorbeeld hieronder wordt als databasenaam database_naam.db gebruikt.

import sqlite3

connection = sqlite3.connect("database_naam.db")

De sqlite3.connect() functie geeft een Connection object terug dat gebruikt kan worden om te werken met de SQLite database die in het bestand database_naam.db is opgeslagen.

In plaats van een database in een SQLite bestand, kun je ook een tijdelijke database gebruiken die geheel in het geheugen van de computer staat en die verdwijnt zodra het Python script is afgelopen. Om een SQLite database te gebruiken die geheel in het gehuegen staat, moet je in plaats van een bestandsnaam de tekst ":memory:" opgeven:

import sqlite3

connection = sqlite3.connect(":memory:")

De gegevens die je in een database opslaat die geheel in het geheugen staat gaat verloren aan het einde van het Python script.

Een functie voor het verbinding maken met een SQLite database met controle op fouten:

import sqlite3
from sqlite3 import Error

dbnaam = "database_naam.db"

def maak_verbinding(dbnaam):
    connection = None
    try:
        connection = sqlite3.connect(dbnaam)
        print("Verbinden met SQLite database gelukt")
    except Error as e:
        print(f"Deze fout '{e}' trad op")
    return connection

connection = maak_verbinding(dbnaam)

Bovenstaande functie geeft aan of de verbinding met de SQLite database is gelukt, als het niet is gelukt dan laat het de functie de foutmelidng zien.

De functie maak_verbinding kan worden aangeroepen met de naam van de database. Deze naam kan ook een bestandspad bevatten, bijvoorbeeld data/database_naam.db, de database database_naam.db staat dan in de directory data.

Tabel aanmaken in een SQLite database

Een voorbeeld van het aanmaken van een tabel in een SQLite database, als tabelnaam in dit voorbeeld personen:

cursor = connection.cursor()
cursor.execute("""
    CREATE TABLE personen (
        naam TEXT, 
        email TEXT, 
        leeftijd INTEGER)
    """)

De functie connection.cursor() geeft een Cursor object terug. Met een Cursor object kun je SQL opdrachten naar een SQLite database sturen met gebruik van cursor.execute().

Het gedeelte dat tussen de """ en """ staat is de SQL opdracht die een tabel maakt met drie kolommen: naam, email en leeftijd. De kolommen naam en email zijn hierbij van het type TEXT en leeftijd is van het type INTEGER (geheel getal).

Je kunt ook een functie maken die een query uitvoert:

def query_uitvoeren(connection, query):
    cursor = connection.cursor()
    try:
        cursor.execute(query)
        connection.commit()
        print("Query met succes uitgevoerd")
    except Error as e:
        print(f"Er was een fout bij het uitvoeren van de query: '{e}'")

maak_personen_tabel = ("""
CREATE TABLE IF NOT EXISTS personen (
        naam TEXT, 
        email TEXT, 
        leeftijd INTEGER)
);
""")

query_uitvoeren(connection, maak_personen_tabel)

SQLite Datatypen

SQLite heeft slechts vijf datatypen:

NULL De waarde is een NULL waarde.

INTEGER De waarde is een integer met teken, opgeslagen in 0, 1, 2, 3, 4, 6, of 8 bytes afhankelijk van de grootte van de waarde.

REAL De waarde is een floating point (drijvende komma) waarde, opgeslagen als een 8-byte IEEE floating point nummer.

TEXT De waarde is a tekst string, opgeslagen met de database codering (UTF-8, UTF-16BE or UTF-16LE).

BLOB De waarde is een blob met data, opgeslagen zoals het is ingevoerd.

Primary key en rowid

Je kunt bij het aanmaken van een tabel één of meerdere kolommen aanwijzen als primary key door PRIMARY KEY toe te voegen achter het type.

SQLite maakt automatisch ook een rowid aan (64-bit signed integer), deze kun je ook zien als een primary key, je kunt deze ook in een select opdracht opvragen.

Constraints

SQLite ondersteund de volgende constraints bij het aanmaken van een tabel: UNIQUE, NOT NULL, CHECK en FOREIGN KEY

Gegevens in een tabel plaatsen

Als je een tabel hebt aangemaakt, dan kun je er ook gegevens in plaatsen.

cursor = connection.cursor()
cursor.execute("""
    INSERT INTO personen VALUES (
        'Jan', 'jan@mail.nl', 31
        )
    """)
cursor.execute("""
    INSERT INTO personen VALUES (
        'Piet', 'piet@mail.nl', 27
        )
    """)

Hierboven wordt cursor.execute() twee keer aangeroepen: één keer om Jan toe te voegen en daarna nog eens om Piet toe te voegen."INSERT INTO personenfish VALUES ..." is een SQL opdracht waarmee he een rij kunt toevoegen in ene tabel.

Je kunt dit ook in één doen:

cursor = connection.cursor()
cursor.execute("""
    INSERT INTO personen VALUES 
        ('Jan', 'jan@mail.nl', 31),
        ('Piet', 'piet@mail.nl', 27)
    """)

Ook dit kan weer in een try-except blok worden geplaatst om eventuele fouten af te vangen:

cursor = connection.cursor()
try:
    cursor.execute("""
        INSERT INTO personen VALUES 
            ('Jan', 'jan@mail.nl', 31),
            ('Piet', 'piet@mail.nl', 27)
        """)
except Error as e:
    print(f"De fout '{e}' trad op")

Gegevens opvragen uit een SQLite database

Gegevens ophalen kan met een SQL SELECT opdracht:

rows = cursor.execute("""
    SELECT naam, email, leeftijd 
    FROM personen""").fetchall()
print(rows)

Deze code zal de volgende uitvoer geven:

[('Jan', 'jan@mail.nl', 31), ('Piet', 'piet@mail.nl', 27)]

De cursor.execute() functie voert een SELECT opdracht uit om de waarden voor naam, email en leeftijd uit de kolommen van de personen tabel op te vragen.

De fetchall() functie haalt alle resultaten van de SELECT opdracht op. Met de print opdracht krijg je een lijst met tuples. Elke tuple bevat de gegevens van een rij uit de opgevraagde tabel.

In SQL kun je het opgevraagde resultaat beperken met een WHERE clausule.

gevraagde_naam = "Jan"
rows = cursor.execute(
    "SELECT naam, email, leeftijd FROM personen WHERE naam = ?",
    (gevraagde_naam,),
).fetchall()
print(rows)

Nu zal de uitvoer slechts één tuple bevatten:

[('Jan', 'jan@mail.nl', 31)]

De cursor.execute(SQL opdracht).fetchall() functie geeft alle resultaten van de SELECT opdracht. De WHERE clausule in de SELECT opdracht filtert de rijen en geeft alleen de rijen terug die aan de WHERE voorwaarde voldoen.

Het vraagteken in de SELECT opdracht wordt gebruikt om de waarde van de variabele gevraagde_naam in de SELECT opdracht te plaatsen. Gebruik altijd deze manier om een query samen te stellen, dus met het vraagteken al plaatshouder voor een variable, dit voorkomt SQL injectie aanvallen.

Je kunt dit ook weer in een try-except blok plaatsen om fouten af te vangen:

gevraagde_naam = "Jan"
try:
    rows = cursor.execute(
        "SELECT naam, email, leeftijd FROM personen WHERE naam = ?",
        (gevraagde_naam,),
    ).fetchall()
    print(rows)
except Error as e:
    print(f"De fout '{e}' trad op")

Gegevens aanpassen in de database

Rijen in een SQLite database kunnen worden aangepast met UPDATE en DELETE opdrachten.

Stel je wilt de email van Jan aanpassen van jan@mail.nl naar jan@hotmail.com:

nieuwe_email = 'jan@hotmail.com'
naam_van_persoon = "Jan"
cursor.execute(
    "UPDATE personen SET email = ? WHERE naam = ?",
    (nieuwe_email, naam_van_persoon)
)

Je kunt nu een SELECT opdracht uitvoeren om te controleren of de wijziging succesvol was:

rows = cursor.execute("""
    SELECT naam, email, leeftijd 
    FROM personen""").fetchall()
print(rows)

Deze code zal de volgende uitvoer geven:

[('Jan', 'jan@hotmail.com', 31), ('Piet', 'piet@mail.nl', 27)]

Stel dat je nu Piet wil verwijderen uit de tabel:

naam_van_te_verwijderen = "Piet"
cursor.execute(
    "DELETE FROM personen WHERE naam = ?",
    (naam_van_te_verwijderen,)
)

Je kunt nu een SELECT opdracht uitvoeren om te controleren of de wijziging succesvol was:

rows = cursor.execute("""
    SELECT naam, email, leeftijd 
    FROM personen""").fetchall()
print(rows)

Deze code zal de volgende uitvoer geven:

[('Jan', 'jan@hotmail.com', 31)]

Database verbinding automatisch afsluiten via with

Connection en Cursor objecten moeten worden afgesloten als je ze niet meer nodig hebt. Dit afsluiten kan automatisch via de with opdracht.

import sqlite3
from contextlib import closing

with closing(sqlite3.connect("database_naam.db")) as connection:
    with closing(connection.cursor()) as cursor:
        rows = cursor.execute("""
            SELECT naam, email, leeftijd 
            FROM personen""").fetchall()
        print(rows)

De functie closing wordt geleverd door de contextlib module. Als een with opdracht bestaat, dan zal closing ervoor zorgen dat close() wordt aangeroepen op het object dat via with wordt aangegeven.

De closing functie wordt in het bovenstaande voorbeeld tweemaal gebruikt: één keer voor het Connection object en een keer voor het Cursor object

Voorbeeld met meerdere query's:

import sqlite3
from contextlib import closing 

dbnaam = 'testdatabase.db'

with closing(sqlite3.connect(dbnaam)) as connection:
    with closing(connection.cursor()) as cursor:
        cursor.execute(("""
        CREATE TABLE IF NOT EXISTS personen (
        naam TEXT, 
        email TEXT, 
        leeftijd INTEGER );
        """))
        cursor.execute("""
        INSERT INTO personen VALUES 
            ('Jan', 'jan@mail.nl', 31),
            ('Piet', 'piet@mail.nl', 27)
        """)
        rows = cursor.execute("""
            SELECT naam, email, leeftijd 
            FROM personen""").fetchall()
        print(rows)

Het resultaat zal zijn:

[('Jan', 'jan@mail.nl', 31), ('Piet', 'piet@mail.nl', 27)]

Controleren of een tabel bestaat

In dit voorbeeld wordt opgevraagd of de tabel 'personen' bestaat

SELECT name FROM sqlite_master WHERE type='table' AND name='personen';""").fetchall()

Als de tabel 'personen' bestaat, dan zal dit het resultaat zijn:

[('personen',)]

Als je een SQLite database gebruikt die niet in een bestand wordt opgeslagen, maar die in het geheugen staat (':memory:'), dan kun je deze query gebruiken om te kijken of de tabel 'personen' bestaat:

SELECT name FROM sqlite_temp_master WHERE type='table' AND name='personen';""").fetchall()

Hier is sqlite_master vervangen door sqlite_temp_master

Connection Object Methods

db.close() : Sluit de verbinding met de database

db.commit() : Voert een eventueel wachtende SQL opdracht uit

db.cursor() : Geeft een database cursor object terug waarmee queries kunnen worden uitgevoerd

db.rollback() : Draait een SQL opdracht terug

Cursor Object Attributes and Methods

c.arraysize : Het (leesbaar/schrijfbaar) aantal rijen dat fetch-many() terug zal geven indien geen grootte is opgegeven

c.close() : Sluit de cursor, c; dit wordt automatisch gedaan als de cursor buiten bereik raakt

c.description : Een alleen-lezen reeks van 7-tuples ( name, type_code, display_size, internal_size, precision, scale, null_ok), die iedere opvolgende kolom van cursor c beschrijft

c.execute(sql,params) : Voert de SQL query in string sql uit en vervangt iedere plaatshouder met de overeenkomende parameter van de params reeks of mapping indien opgegeven

c.executemany(sql,seq_of_params) : Voert de SQL query één keer uit voor elk item in de seq_of_params reeks van reeksen of mappings; deze methode moet je niet gebruiken voor opdrachten die resultaat sets maken (zoals een SELECT opdracht)

c.fetchall() : Geeft een reeks van alle rijen die nog niet opgevraagd zijn, dit kunnen ook alle rijen zijn

c.fetchmany(size) : Geeft een reeks van rijen (iedere rij is hier zelf een reeks); grootte is standaard c.arraysize

c.fetchone() : Geeft de volgende rij van het resultaat van een query set als een reeks of None als er geen resultaaten meer zijn

c.rowcount : De alleen-lezen telling voor de laatse opdracht (bijvoorbeeld SELECT, INSERT, UPDATE, or DELETE) of -1 indien niet beschikbaar of niet van toepassing

Verwante artikelen