SQLite ist eine der beliebtesten relationalen Datenbankmanagementsysteme (RDBMS). Es ist leichtgewichtig, was bedeutet, dass es auf Ihrem System nicht viel Platz in Anspruch nimmt. Eines seiner besten Features ist, dass es serverlos ist, also nicht notwendig ist, ein separates Server zu installieren oder zu verwalten, um es verwenden zu können.

Stattdessen werden alle Daten in einer einfachen Datei auf Ihrem Computer gespeichert. Es erfordert auch keine Konfiguration, also gibt es kein kompliziertes Setup-Verfahren, was es perfekt für Anfänger und kleine Projekte macht.

SQLite ist eine großartige Wahl für kleine bis mittlere Anwendungen, weil es einfach zu verwenden ist, schnell laufend und die meisten Aufgaben erledigen kann, die größere Datenbanken tun, ohne das Näseln von zusätzlicher Software. Egal, ob Sie ein persönliches Projekt aufbauen oder ein neues App-Prototypen entwickeln, ist SQLite eine solide Option, um schnell das Fahren der Dinge zu beginnen.

In diesem Leitfaden werden Sie erfahren, wie Sie SQLite mit Python verwenden. Hier ist, was wir in diesem Leitfaden behandeln werden:

Dieses Tutorial ist perfekt für jeden, der mit Datenbanken beginnen möchte, ohne in komplizierte Setups zu tauchen.

Wie du dein Python-Umfeld einrichtest

Bevor du mit SQLite arbeitest, lass uns sicherstellen, dass dein Python-Umfeld fertig ist. Hier ist, wie du alles einrichtest.

Python installieren

Wenn du noch kein Python auf deinem System installiert hast, kannst du es von der offiziellen Python-Website herunterladen. Folge den Installationsanweisungen für dein Betriebssystem (Windows, macOS oder Linux).

Um zu überprüfen, ob Python installiert ist, öffne deinen Terminal (oder den Befehlszeilenprompt) und tippe:

python --version

Das sollte die aktuelle Version von Python anzeigen, die installiert ist. Wenn es nicht installiert ist, folge den Anweisungen auf der Python-Website.

Installieren des SQLite3-Moduls

Die gute Nachricht ist, dass SQLite3 in Python integriert ist! Sie müssen es nicht separat installieren, da es in der Standardbibliothek von Python enthalten ist. Dies bedeutet, dass Sie es sofort verwenden können, ohne zusätzliche Einrichtung notwendig zu haben.

Es ist eine gute Idee, für jedes Projekt eine virtuelle Umgebung zu erstellen, um Ihre Abhängigkeiten organisiert zu halten. Eine virtuelle Umgebung ist wie ein sauberter Anfang, wo Sie Pakete installieren können, ohne Ihre globale Python-Installation zu beeinflussen.

Um eine virtuelle Umgebung zu erstellen, folgen Sie diesen Schritten:

  1. Öffnen Sie zuerst Ihren Terminal oder Command Prompt und navigieren Sie zum Verzeichnis, wo Sie Ihr Projekt erstellen möchten.

  2. Führen Sie den folgenden Befehl aus, um eine virtuelle Umgebung zu erstellen:

python -m venv env

Hierbei ist env der Name der virtuellen Umgebung. Sie können ihn frei wählen.

  1. Aktivieren Sie die virtuelle Umgebung:
# Verwenden Sie den Befehl für Windows
env\Scripts\activate

# Verwenden Sie den Befehl für macOS/Linux:
env/bin/activate

Nach der Aktivierung der virtuellen Umgebung merken Sie sich, dass Ihre Terminalbenachrichtigung sich ändert und den Namen der virtuellen Umgebung anzeigt. Dies bedeutet, dass Sie nun innerhalb der virtuellen Umgebung arbeiten.

Installieren Sie notwendige Bibliotheken

Wir benötigen für dieses Projekt einige zusätzliche Bibliotheken. Insbesondere werden wir Folgendes verwenden:

  • pandas: Dies ist eine optionale Bibliothek zum Handhaben und Anzeigen von Daten in Tabellenform, die für fortgeschrittene Anwendungsfälle nützlich ist.

  • faker: Diese Bibliothek hilft uns, gefälschte Daten zu generieren, wie zufällige Namen und Adressen, die wir für Testzwecke in unsere Datenbank einfügen können.

Um pandas und faker zu installieren, führen Sie einfach die folgenden Befehle aus:

pip install pandas faker

Das installiert sowohl pandas als auch faker in Ihrem virtuellen Umgebung. Damit ist Ihre Umgebung eingerichtet, und Sie können beginnen, Ihre SQLite-Datenbank in Python zu erstellen und zu verwalten!

So erstellen Sie eine SQLite-Datenbank

Eine Datenbank ist eine strukturierte Methode, um Daten zu speichern und zu verwalten, sodass sie leicht zugänglich, aktualisierbar und organisiert sind. Es ist wie ein digitales Dateiführungs-system, das Ihnen erlaubt, große Datenmengen effizient zu speichern, egal ob für eine einfache App oder ein komplexeres System. Datenbanken verwenden Tabellen, um Daten zu organisieren, wobei Zeilen und Spalten einzelne Datensätze und ihre Attribute darstellen.

Wie SQLite-Datenbanken funktionieren

Im Gegensatz zu den meisten anderen Datenbankensystemen ist SQLite ein serverloser Datenbanktreiber. Dies bedeutet, dass es keinen Server zum Einrichten oder Verwalten erfordert, was ihn leicht und einfach zu verwenden macht. Alle Daten werden in einer einzigen Datei auf Ihrem Computer gespeichert, die Sie leicht verschieben, teilen oder sichern können. Trotz ihrer einfachen Struktur ist SQLite dannoch in der Lage, viele gängige Datenbankaufgaben zu bewältigen und wird in mobilen App, eingebetteten Systemen und kleinen bis mittleren Projekten breit verwendet.

Wie man eine neue SQLite-Datenbank erstellt

Legen wir eine neue SQLite-Datenbank an und lernen, wie wir sie mit der Python-Bibliothek sqlite3 interagieren können.

Verbindung zur Datenbank

da sqlite3 bereits installiert ist, müssen Sie ihn nur in Ihrem Python-Skript importieren. Um eine neue Datenbank zu erstellen oder eine existierende zu verwenden, verwenden wir den sqlite3.connect() -Methode. Diese Methode nimmt den Namen der Datenbankdatei als Argument. Wenn die Datei nicht existiert, erstellt SQLite automatisch die Datei.

import sqlite3

# Verbinden Sie mit der SQLite-Datenbank (oder erstellen Sie sie, wenn sie nicht existiert)
connection = sqlite3.connect('my_database.db')

In diesem Beispiel wird eine Datei namens my_database.db erstellt, die im selben Verzeichnis wie Ihr Skript liegt. Wenn die Datei bereits existiert, öffnet SQLite nur die Verbindung zu dieser Datei.

Erstellen eines Cursors

Sobald Sie eine Verbindung hergestellt haben, ist der nächste Schritt das Erstellen eines Cursor-Objekts. Der Cursor ist verantwortlich für die Ausführung von SQL-Befehlen und Abfragen in der Datenbank.

# Erstellen Sie ein Cursor-Objekt
cursor = connection.cursor()

Schließen der Verbindung

Nachdem du mit der Datenbank befasst hast, ist es wichtig, die Verbindung zu schließen, um jegliche Ressourcen freizugeben. Du kannst die Verbindung mit dem folgenden Befehl schließen:

# Verbindung zur Datenbank schließen
connection.close()

Wichtig ist jedoch, die Verbindung erst zu schließen, wenn du alle Deine Operationen abgeschlossen hast.

Wenn du dein Python-Skript ausführst, wird eine Datei namens my_database.db in deinem aktuellen Arbeitsverzeichnis erstellt. Du hast nun erfolgreich deine erste SQLite-Datenbank erstellt!

Wie man Verbindungen mit Hilfe eines Kontext-Managers öffnet und schließt

Python bietet eine effizientere und sauberere Methode zur Verwaltung von Datenbankverbindungen anhand des with-Ausdrucks, der auch als Kontext-Manager bezeichnet wird. Der with-Ausdruck öffnet und schließt automatisch die Verbindung, sicherstellend, dass die Verbindung ordnungsgemäß geschlossen wird, auch wenn ein Fehler während der Datenbankoperationen auftritt. Dies eliminiert die Notwendigkeit, connection.close() manuell aufzurufen.

Hier ist wie du den with-Ausdruck nutzen kannst, um Verbindungen mit der Datenbank zu verwalten:

import sqlite3

# Schritt 1: Verwende 'with' um eine Verbindung mit der Datenbank herzustellen (oder eine zu erstellen) und automatisch zu schließen, wenn fertig
with sqlite3.connect('my_database.db') as connection:

    # Schritt 2: Erstelle ein Cursor-Objekt, um mit der Datenbank zu interagieren
    cursor = connection.cursor()

    print("Database created and connected successfully!")

# Es ist keine Notwendigkeit, connection.close() aufzurufen; das wird automatisch gemacht!

Ab jetzt werden wir in den folgenden Codebeispielen den with-Ausdruck verwenden, um Datenbankverbindungen effizient zu verwalten. Dies macht den Code kürzer und einfacher zu pflegen.

Wie man Datenbanktabellen erstellt

Nachdem wir eine SQLite-Datenbank erstellt und mit ihr verbunden haben, geht es nun darum, Tabellen innerhalb der Datenbank zu erstellen. Eine Tabelle ist die Stelle, an der wir unsere Daten aufbewahren, die in Zeilen (Datensätze) und Spalten (Attribute) organisiert sind. In diesem Beispiel erstellen wir eine Tabelle namens Students, um Informationen über Studenten aufzubewahren, die wir in den folgenden Abschnitten wiederverwenden werden.

Zum Erstellen einer Tabelle verwenden wir den SQL-Befehl CREATE TABLE. Dieser Befehl definiert die Tabellenstruktur, einschließlich der Spaltenbezeichnungen und den Datentypen für jede Spalte.

Hier ist ein einfacher SQL-Befehl, um eine Students-Tabelle mit folgenden Feldern zu erstellen:

  • id: Ein eindeutiger Identifikator für jeden Studenten (ein Integer).

  • name: Der Name des Studenten (Text).

  • age: Die Alters des Studenten (ein Integer).

  • email: Die E-Mail-Adresse des Studenten (Text).

Der SQL-Befehl, um diese Tabelle zu erstellen, würde wie folgt aussehen:

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

Wir können diesen SQL-Befehl CREATE TABLE in Python mit der Bibliothek sqlite3 ausführen. Sehen wir wie das geht.

import sqlite3

# Verwende 'with' um zu verbinden und die Verbindung automatisch zu schließen
with sqlite3.connect('my_database.db') as connection:

    # Erstelle ein Cursor-Objekt
    cursor = connection.cursor()

    # Schreibe den SQL-Befehl um die Tabelle 'Students' zu erzeugen
    create_table_query = '''
    CREATE TABLE IF NOT EXISTS Students (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        age INTEGER,
        email TEXT
    );
    '''

    # Führe den SQL-Befehl aus
    cursor.execute(create_table_query)

    # Commitiere die Änderungen
    connection.commit()

    # Zeige eine Bestätigungsnachricht an
    print("Table 'Students' created successfully!")

  • IF NOT EXISTS: Dies stellt sicher, dass die Tabelle nur erstellt wird, wenn sie nicht bereits existiert, um Fehler zu vermeiden, wenn die Tabelle zuvor erstellt wurde.

  • connection.commit(): Dies speichert (commitiert) die Änderungen in die Datenbank.

Wenn du die obenstehende Python-Datei ausführst, wird in der Datenbankdatei my_database.db die Tabelle Students erstellt. Du wirst auch in der Konsole eine Nachricht sehen, die bestätigt, dass die Tabelle erfolgreich erstellt wurde.

Wenn Sie Visual Studio Code verwenden, können Sie die SQLite Viewer Erweiterung installieren, um SQLite-Datenbanken anzuzeigen.

Datentypen in SQLite und ihre Zuordnung zu Python

SQLite unterstützt verschiedene Datentypen, die wir verstehen müssen, wenn wir unsere Tabellen definieren. Hier ist eine kurze Übersicht der häufig verwendeten SQLite-Datentypen und ihrer Zuordnung zu Python-Typen:

SQLite-Datentyp Beschreibung Python-Entsprechung
INTEGER Ganze Zahlen int
TEXT Textzeichenketten str
REAL Fließkommazahlen float
BLOB Binärdaten (z.B. Bilder, Dateien) bytes
NULL Repräsentiert keinen Wert oder fehlende Daten None

In unserer Students-Tabelle:

  • id ist vom Typ INTEGER, der auf Pythons int abgebildet wird.

  • name und email sind vom Typ TEXT, der auf Pythons str abgebildet wird.

  • age ist auch vom Typ INTEGER, der auf Pythons int abbildet.

Wie man Daten in eine Tabelle einfügt

Jetzt, da wir unsere Students-Tabelle erstellt haben, ist es Zeit, Daten in die Datenbank einzufügen. In diesem Abschnitt werden wir erklären, wie man sowohl einzelne als auch mehrere Datensätze mit Python und SQLite einfügt und wie man gemeinsame Sicherheitsprobleme wie SQL-Injection durch die Verwendung von parametrisierten Abfragen vermeidet.

Wie man einen einzelnen Datensatz einfügt

Um Daten in die Datenbank einzufügen, verwenden wir den SQL-Befehl INSERT INTO. Lassen Sie uns beginnen, indem wir einen einzelnen Datensatz in unsere Students-Tabelle einfügen.

Hier ist die grundlegende SQL-Syntax zum Einfügen eines einzelnen Datensatzes:

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

Allerdings schreiben wir keinen SQL-Befehl direkt in unserem Python-Skript mit fest kodierten Werten, sondern verwenden parametrisierte Abfragen, um unser Code sicherer und flexibler zu machen. Parametrisierte Abfragen helfen, SQL-Injection zu vermeiden, einer häufigen Attacke, bei der böswillige Benutzer durch das Übergeben schädlicher Eingaben die SQL-Abfrage manipulieren können.

So können wir einen einzelnen Datensatz in die Students-Tabelle mithilfe einer parametrisierten Abfrage einfügen:

import sqlite3

# Verwende 'with', um die Verbindung automatisch zu öffnen und zu schließen
with sqlite3.connect('my_database.db') as connection:
    cursor = connection.cursor()

    # Füge einen Eintrag in die Students-Tabelle ein
    insert_query = '''
    INSERT INTO Students (name, age, email) 
    VALUES (?, ?, ?);
    '''
    student_data = ('Jane Doe', 23, '[email protected]')

    cursor.execute(insert_query, student_data)

    # Schließe die Änderungen automatisch
    connection.commit()

    # Es ist nicht notwendig, connection.close(); es geschieht automatisch!
    print("Record inserted successfully!")

Die ? Platzhalter repräsentieren die Werte, die in die Tabelle eingefügt werden sollen. Die tatsächlichen Werte werden als Tupel (student_data) im cursor.execute()-Methode übergeben.

Wie man mehrere Einträge hintereinander einfügt

Wenn du mehrere Einträge gleichzeitig einfügen möchtest, kannst du die executemany() Methode in Python verwenden. Diese Methode nimmt eine Liste von Tupeln entgegen, wobei jedes Tupel einen Eintrag repräsentiert.

Um unser Beispiel dynamischer zu machen, kannst du die Faker Bibliothek verwenden, um zufällige Studentendaten zu generieren. Dies ist nützlich für die Testung und die Simulierung von realen Welt Situationen.

from faker import Faker
import sqlite3

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

# Verwende 'with' zum automatischen Öffnen und Schließen der Verbindung
with sqlite3.connect('my_database.db') as connection:
    cursor = connection.cursor()

    # Füge einen Eintrag in die Students-Tabelle ein
    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)]

    # Führe die Abfrage für mehrere Einträge aus
    cursor.executemany(insert_query, students_data)

    # Schließe die Änderungen
    connection.commit()

    # Gebe Bestätigungsnachricht aus
    print("Fake student records inserted successfully!")

In diesem Code:

  • Faker() generiert zufällige Namen, Altersangaben und E-Mails für Studenten. Der locale-Parameter ([‘de_DE’]) ist optional.

  • cursor.executemany(): Diese Methode ermöglicht es uns, mehrere Datensätze auf einmal einzufügen, was den Code effizienter macht.

  • students_data: Eine Liste von Tupeln, in denen jedes Tupel die Daten eines Studenten repräsentiert.

Wie man mit häufigen Problemen umgeht: SQL-Injection

SQL-Injection ist eine Sicherheitslücke, bei der Angreifer SQL-Abfragen durch gefährliche Eingaben einfügen oder manipulieren können. Zum Beispiel könnte ein Angreifer versuchen, den Code wie '; DROP TABLE Students; -- einzuschleusen, um die Tabelle zu löschen.

Durch die Verwendung von parameterisierten Abfragen (wie oben gezeigt) kann dieses Problem vermieden werden. Die ? Platzhalter in parameterisierten Abfragen stellen sicher, dass Eingabewerte als Daten behandelt werden und nicht als Teil der SQL-Anweisung. Dies macht es unmöglich, dass böswillige Code ausgeführt wird.

Wie man Daten abfragt

Nun, da wir einige Daten in unsere Students-Tabelle eingefügt haben, lernen wir, wie wir die Daten aus der Tabelle abrufen können. Wir werden verschiedene Methoden zur Datenabholung in Python veranschaulichen, einschließlich von fetchone(), fetchall() und fetchmany().

Um Daten aus einer Tabelle abzufragen, verwenden wir die SELECT-Anweisung. Hier ist ein einfacher SQL-Befehl, um alle Spalten aus der Students-Tabelle auszuwählen:

SELECT * FROM Students;

Dieser Befehl ruft alle Datensätze und -spalten aus der Students-Tabelle auf. Wir können diesen SELECT-Befehl in Python ausführen und die Ergebnisse abrufen.

Wie man alle Datensätze abruft

So können wir alle Datensätze aus der Students-Tabelle abrufen:

import sqlite3

# Verwende 'with', um mit der SQLite-Datenbank verbunden zu sein
with sqlite3.connect('my_database.db') as connection:

    # Erstelle ein Cursor-Objekt
    cursor = connection.cursor()

    # Schreibe den SQL-Befehl, um alle Datensätze aus der Students-Tabelle auszuwählen
    select_query = "SELECT * FROM Students;"

    # Führe den SQL-Befehl aus
    cursor.execute(select_query)

    # hole alle Datensätze
    all_students = cursor.fetchall()

    # zeige die Ergebnisse im Terminal an
    print("All Students:")
    for student in all_students:
        print(student)

In diesem Beispiel holt die fetchall()-Methode alle Zeilen, die vom Abfrageergebnis zurückgegeben werden, als Liste von Tupeln.

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]')

Wie man einen einzelnen Datensatz abruft

Wenn du nur einen einzigen Datensatz abrufen möchtest, kannst du die fetchone()-Methode verwenden:

import sqlite3

# Verwende 'with' zur Verbindung mit der SQLite-Datenbank
with sqlite3.connect('my_database.db') as connection:

    # Erstelle ein Cursor-Objekt
    cursor = connection.cursor()

    # Schreibe den SQL-Befehl, um alle Datensätze aus der Tabelle Students zu selektieren
    select_query = "SELECT * FROM Students;"

    # Führe den SQL-Befehl aus
    cursor.execute(select_query)

    #hole einen Datensatz
    student = cursor.fetchone()

    #Zeige das Ergebnis an
    print("First Student:")
    print(student)

Ausgabe:

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

Wie man mehrere Datensätze abruft

Um eine bestimmte Anzahl von Datensätzen abzurufen, kannst du fetchmany(size) verwenden:

import sqlite3

# Verwende 'with' zur Verbindung mit der SQLite-Datenbank
with sqlite3.connect('my_database.db') as connection:

    # Erstelle ein Cursor-Objekt
    cursor = connection.cursor()

    # Schreibe den SQL-Befehl, um alle Datensätze aus der Tabelle Students zu selektieren
    select_query = "SELECT * FROM Students;"

    # Führe den SQL-Befehl aus
    cursor.execute(select_query)

    # hole drei Datensätze
    three_students = cursor.fetchmany(3)

    # Zeige Ergebnisse an
    print("Three Students:")
    for student in three_students:
        print(student)

Ausgabe:

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

Wie man die pandas-Bibliothek zur besseren Datenpräsentation verwendet

Für eine bessere Datenpräsentation kann die pandas-Bibliothek verwendet werden, um ein DataFrame aus den Abfrageergebnissen zu erstellen. Dies erleichtert die Manipulation und Visualisierung der Daten.

Hier ist der Schritt, um alle Datensätze abzurufen und sie als ein pandas DataFrame anzuzeigen:

import sqlite3
import pandas as pd

# Verwenden Sie 'with', um eine Verbindung zur SQLite-Datenbank herzustellen
with sqlite3.connect('my_database.db') as connection:
    # Schreiben Sie den SQL-Befehl, um alle Datensätze aus der Tabelle Students auszuwählen
    select_query = "SELECT * FROM Students;"

    # Verwenden Sie pandas, um die SQL-Abfrage direkt in ein DataFrame zu lesen
    df = pd.read_sql_query(select_query, connection)

# Zeigen Sie das DataFrame an
print("All Students as DataFrame:")
print(df)

Output:

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]

Die Funktion pd.read_sql_query() führt die SQL-Abfrage aus und gibt die Ergebnisse direkt als pandas DataFrame zurück.

Wie man Daten aktualisiert und löscht

In diesem Abschnitt lernen wir, wie man vorhandene Datensätze aktualisiert und Datensätze aus unserer Tabelle Students mithilfe von SQL-Befehlen in Python löscht. Dies ist entscheidend für die effektive Verwaltung und Aufrechterhaltung Ihrer Daten.

Aktualisieren von vorhandenen Datensätzen

Um vorhandene Datensätze in einer Datenbank zu ändern, verwenden wir den SQL-Befehl UPDATE. Dieser Befehl ermöglicht es uns, die Werte bestimmter Spalten in einer oder mehreren Zeilen basierend auf einer angegebenen Bedingung zu ändern.

Zum Beispiel, wenn wir das Alter eines Studenten aktualisieren möchten, würde der SQL-Befehl wie folgt aussehen:

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

Jetzt schreiben wir Python-Code, um das Alter eines bestimmten Studenten in unserer Students Tabelle zu aktualisieren.

import sqlite3

# Verwende 'with' um mit der SQLite-Datenbank verbunden zu werden
with sqlite3.connect('my_database.db') as connection:
    cursor = connection.cursor()

    # SQL-Befehl um das Alter eines Studenten zu aktualisieren
    update_query = '''
    UPDATE Students 
    SET age = ? 
    WHERE name = ?;
    '''

    # Daten für die Aktualisierung
    new_age = 21
    student_name = 'Jane Doe'

    # Führe den SQL-Befehl mit den Daten aus
    cursor.execute(update_query, (new_age, student_name))

    # Übernehme die Änderungen, um die Aktualisierung zu speichern
    connection.commit()

    # Ziehe eine Bestätigungsnachricht aus
    print(f"Updated age for {student_name} to {new_age}.")

In diesem Beispiel haben wir parameterisierte Abfragen verwendet, um SQL-Injection zu vermeiden.

Wie man Datensätze aus der Tabelle löscht

Um Datensätze aus einer Datenbank zu entfernen, verwenden wir den SQL-Befehl DELETE. Dieser Befehl ermöglicht es uns, einen oder mehrere Zeilen basierend auf einer bestimmten Bedingung zu löschen.

Zum Beispiel, wenn wir einen Studenten namens ‚Jane Doe‘ löschen möchten, würde der SQL-Befehl so aussehen:

DELETE FROM Students 
WHERE name = 'Jane Doe';

Lass uns Python-Code schreiben, um einen bestimmten Studenten aus unserer Students-Tabelle mit dem with-Ausdruck zu löschen.

import sqlite3

# Verwende 'with' um mit der SQLite-Datenbank verbunden zu werden
with sqlite3.connect('my_database.db') as connection:
    cursor = connection.cursor()

    # SQL-Befehl, um einen Studenten zu löschen
    delete_query = '''
    DELETE FROM Students 
    WHERE name = ?;
    '''

    # Name des zu löschenden Studenten
    student_name = 'Jane Doe'

    # Führe den SQL-Befehl mit den Daten aus
    cursor.execute(delete_query, (student_name,))

    # Übernehme die Änderungen, um die Löschung zu speichern
    connection.commit()

    # Ziehe eine Bestätigungsnachricht aus
    print(f"Deleted student record for {student_name}.")

Wichtige Überlegungen

  • Bedingungen

    : Verwenden Sie immer die WHERE-Klausel beim Aktualisieren oder Löschen von Datensätzen, um zu vermeiden, dass alle Zeilen in der Tabelle geändert oder gelöscht werden. Ohne eine WHERE-Klausel betrifft die Anweisung jeder Zeile in der Tabelle.

  • Sicherung: Es ist eine gute Praxis, Ihre Datenbank vor der Durchführung von Aktualisierungen oder Löschungen zu sichern, insbesondere in Produktionsumgebungen.

Wie Transaktionen verwendet werden

Eine Transaktion ist eine Sequenz aus einer oder mehreren SQL-Operationen, die als ein einzelnes Arbeits单元 behandelt werden. Im Kontext einer Datenbank ermöglicht eine Transaktion die Durchführung mehrerer Operationen, die entweder alle erfolgreich oder gar nicht durchgeführt werden. Dies stellt sicher, dass Ihre Datenbank auch bei Fehlern oder unerwarteten Problemen in einem konsistenten Zustand bleibt.

Zum Beispiel, wenn Sie Geld zwischen zwei Bankkonten transferieren, möchten Sie sicherstellen, dass sowohl die Belastung eines Kontos als auch der Kredit auf dem anderen Konto gemeinsam erfolgreich oder fehlschlagen. Wenn eine Operation fehlschlägt, sollte die andere nicht ausgeführt werden, um die Konsistenz aufrechtzuerhalten.

Warum Transaktionen verwenden?

  1. Atomarität: Transaktionen stellen sicher, dass eine Reihe von Operationen als eine einzige Einheit behandelt wird. Wenn eine Operation fehlschlägt, werden keine der Operationen auf die Datenbank angewendet.

  2. Konsistenz: Transaktionen helfen, die Integrität der Datenbank zu wahren, indem sichergestellt wird, dass alle Regeln und Einschränkungen eingehalten werden.

  3. Isolation: Jede Transaktion arbeitet unabhängig von anderen, um unbeabsichtigte Störungen zu verhindern.

  4. Dauerhaftigkeit: Sobald eine Transaktion abgeschlossen ist, sind die Änderungen dauerhaft, auch im Falle eines Systemausfalls.

Wann sollten Transaktionen verwendet werden?

Sie sollten Transaktionen verwenden, wenn:

  • Mehrere zusammenhängende Operationen durchgeführt werden müssen, die gemeinsam erfolgreich oder fehlerhaft sein müssen.

  • Kritische Daten modifiziert werden, die Konsistenz und Integrität erfordern.

  • Arbeiten mit Operationen, die potenziell fehlschlagen können, wie z.B. Finanztransaktionen oder Datenmigrationen.

Wie man Transaktionen in Python verwaltet

In SQLite werden Transaktionen mit den Befehlen BEGIN, COMMIT und ROLLBACK verwaltet. Allerdings verwaltest du Transaktionen mit dem sqlite3-Modul in Python normalerweise über das Verbindungs-Objekt.

Eine Transaktion beginnen

Eine Transaktion beginnt implizit, wenn du jede SQL-Anweisung ausführst. Um eine Transaktion explizit zu starten, kannst du den BEGIN-Befehl verwenden:

cursor.execute("BEGIN;")

Allerdings ist es normalerweise unnötig, eine Transaktion manuell zu starten, da SQLite eine Transaktion automatisch startet, wenn du eine SQL-Anweisung ausführst.

Wie man eine Transaktion committet

Um alle während einer Transaktion durchgeführten Änderungen aufzuschließen, verwendest du den commit()-Methode. Dies macht alle Änderungen dauerhaft im Datenbank.

connection.commit()

Wir haben die commit()-Methode bereits in den oben gegebenen Beispielen verwendet.

Rückgängig machen einer Transaktion

Wenn etwas schief geht und du die während einer Transaktion durchgeführten Änderungen rückgängig machen willst, kannst du die rollback()-Methode verwenden. Dies wird alle seit dem Beginn der Transaktion vorgenommenen Änderungen rückgängig machen.

connection.rollback()

Beispiel der Verwendung von Transaktionen in Python

Zum Illustrieren der Verwendung von Transaktionen in einer realen Welt Situation, erstellen wir eine neue Tabelle namens Kunden, um Kundenkonten zu verwalten. In diesem Beispiel gehen wir davon aus, dass jedes Kunden einen Kontostand hat. Wir werden zwei Kunden zu dieser Tabelle hinzufügen und eine Geldtransfers Operation zwischen ihnen durchführen.

Zuerst lassen Sie uns die Kunden Tabelle erstellen und zwei Kunden hinzufügen:

import sqlite3

# Erstellen der Kunden Tabelle und Hinzufügen von zwei Kunden
with sqlite3.connect('my_database.db') as connection:
    cursor = connection.cursor()

    # Erstellen der Kunden Tabelle
    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)

    # Hinzufügen von zwei Kunden
    cursor.execute(
        "INSERT INTO Customers (name, balance) VALUES (?, ?);", ('Ashutosh', 100.0))
    cursor.execute(
        "INSERT INTO Customers (name, balance) VALUES (?, ?);", ('Krishna', 50.0))

    connection.commit()

Nun werden wir die Geldtransfers Operation zwischen Ashutosh und Krishna durchführen:

import sqlite3


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

        try:
            # Starte eine Transaktion
            cursor.execute("BEGIN;")

            # Abziehen des Betrags vom Absender
            cursor.execute(
                "UPDATE Customers SET balance = balance - ? WHERE name = ?;", (amount, from_customer))
            # Füge Betrag dem Empfänger hinzu
            cursor.execute(
                "UPDATE Customers SET balance = balance + ? WHERE name = ?;", (amount, to_customer))

            # Commitiere die Änderungen
            connection.commit()
            print(
                f"Transferred {amount} from {from_customer} to {to_customer}.")

        except Exception as e:
            # Wenn ein Fehler auftritt, rolliere die Transaktion zurück
            connection.rollback()
            print(f"Transaction failed: {e}")


# Beispielhafte Verwendung
transfer_funds('Ashutosh', 'Krishna', 80.0)

In diesem Beispiel haben wir zunächst eine Kunden-Tabelle erstellt und zwei Kunden eingefügt: Ashutosh mit einem Kontostand von ₹100 und Krishna mit einem Kontostand von ₹50. Daraufhin haben wir eine Geldtransaktion von ₹80 von Ashutosh zu Krishna durchgeführt. Durch die Verwendung von Transaktionen stellen wir sicher, dass sowohl der Abzug von Ashutoshs Konto als auch der Zuschlag von Krishnas Konto als ein einzelnes atomare Operation ausgeführt werden, um Datenintegrität auch bei Fehlern aufrechtzuerhalten. Wenn die Überweisung fehlschlägt (z.B. wegen unzureichender Mittel), wird die Transaktion zurückgenommen, ohne die Konten zu ändern.

Wie man die SQLite-Abfrageleistung mit Indizierung optimiert

Indizierung ist eine kraftvolle Technik, die in Datenbanken zur Verbesserung der Abfrageleistung verwendet wird. Ein Index ist essentiell eine Datenstruktur, die die Lage von Zeilen basierend auf bestimmten Spaltenwerten speichert, ähnlich wie ein Index im Buch ist, der hilft, ein Thema schnell zu finden.

Ohne Index muss SQLite die gesamte Tabelle Zeile für Zeile durchsuchen, um die relevanten Daten zu finden, was ineffizient wird, wenn die Datenmenge zunimmt. Durch die Verwendung eines Indexes kann SQLite direkt zu den erforderlichen Zeilen springen, was die Ausführung von Abfragen wesentlich beschleunigt.

Wie man die Datenbank mit Falscher Daten füllt

Um die Auswirkungen von Indizierung effektiv zu testen, müssen wir eine größere Datenmenge haben. Anstatt die Datensätze manuell hinzuzufügen, können wir die faker-Bibliothek verwenden, um schnell Falsche Daten zu generieren. In diesem Abschnitt generieren wir 10.000 Falsche Datensätze und fügen sie in unsere Schüler-Tabelle ein. Dies simuliert eine reale Welt Situation, in der Datenbanken groß werden und die Abfrageleistung wichtig wird.

Wir werden die Methode executemany() verwenden, um die Datensätze wie unten zu inserten:

import sqlite3
from faker import Faker

# Initialize the Faker library
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)]

    # Use 'with' to handle the database connection
    with sqlite3.connect('my_database.db') as connection:
        cursor = connection.cursor()

        # Insert fake data into the Students table
        cursor.executemany('''
        INSERT INTO Students (name, age, email) 
        VALUES (?, ?, ?);
        ''', fake_data)

        connection.commit()

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


# Insert 10,000 fake records into the Students table
insert_fake_students(10000)

Durch das Ausführen dieses Skripts werden 10.000 fake Studentendaten in die Students-Tabelle eingefügt. In der nächsten Abschnitt, werden wir die Datenbank abfragen und die Leistung von Abfragen mit und ohne Indizierung vergleichen.

Wie ohne Indizes abfragen

In diesem Abschnitt werden wir die Students-Tabelle ohne jegliche Indizes abfragen, um zu beobachten, wie SQLite mit keiner Optimierung funktioniert. Dies wird als Basisline für die Leistung verwendet, wenn wir später Indizes hinzufügen.

Ohne Indizes führt SQLite eine vollständige Tabellenübersicht durch, was bedeutet, dass es jeden Eintrag in der Tabelle prüfen muss, um passende Ergebnisse zu finden. Für kleine Datenmengen ist dies verwaltbar, aber mit zunehmender Anzahl von Datensätzen steigt die Zeit, die für die Suche bentigt wird, dramatisch. Lassen Sie uns diesen Prozess in Aktion sehen, indem wir eine grundlegende SELECT-Abfrage für die Suche nach einem bestimmten Studenten nach Namen ausführen und die Zeit messen, die die Abfrage benötigt.

Zunächst werden wir die Students-Tabelle abfragen, indem wir nach einem Studenten mit einem bestimmten Namen suchen. Wir werden die Ausführungszeit der Abfrage mit Python’s time-Modul protokollieren, um die Leistung zu messen.

import sqlite3
import time


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

    # Verbindung mit der Datenbank mit 'with' herstellen
    with sqlite3.connect('my_database.db') as connection:
        cursor = connection.cursor()

        # Startzeit messen
        start_time = time.perf_counter_ns()

        # SELECT-Abfrage durchführen, um einen Studenten nach Name zu finden
        cursor.execute('''
        SELECT * FROM Students WHERE name = ?;
        ''', (search_name,))

        # Alle Ergebnisse abholen (im Praxisshouldteilweise nur ein oder wenige)
        results = cursor.fetchall()

        # Endezeit messen
        end_time = time.perf_counter_ns()

        # Gesamter benötigter Zeit berechnen
        elapsed_time = (end_time - start_time) / 1000

        # Ergebnisse und die verwendete Zeit anzeigen
        print(f"Query completed in {elapsed_time:.5f} microseconds.")
        print("Results:", results)


# Beispiel: Suche nach einem Studenten nach Namen
query_without_index('Ojasvi Dhawan')

Hier das Ergebnis:

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

Durch Ausführen des oben stehenden Skripts kann man sehen, wie lange es dauert, um die Students Tabelle ohne irgendwelche Indizes zu durchsuchen. Wenn es z.B. 10.000 Datensätze in der Tabelle gibt, kann die Abfrage 1000-2000 Nanosekunden dauern, je nach der Größe der Tabelle und Ihrer Hardware. Dies mag für einen kleinen Datensatz nicht langsam erscheinen, aber die Leistung wird mit zunehmendem Datenbestand verschlechtert.

Wir verwenden time.perf_counter_ns() , um die Ausführungszeit der Abfrage in Nanosekunden zu messen. Diese Methode ist sehr präzise, um Benchmarks für kleine Zeitintervalle durchzuführen. Wir verwenden die Zeit in Mikrosekunden (us), um sie leichter lesbar zu machen.

Einführung des Abfrageplans

Beim Arbeiten mit Datenbanken kann das Verständnis, wie Abfragen ausgeführt werden, dabei helfen, Leistungsschwierigkeiten zu identifizieren und Ihren Code zu optimieren. SQLite stellt dafür ein nützliches Werkzeug bereit, den EXPLAIN QUERY PLAN, der es Ihnen erlaubt, die Schritte zu analysieren, die SQLite unternimmt, um Daten abzurufen.

In diesem Abschnitt werden wir erläutern, wie man den EXPLAIN QUERY PLAN verwendet, um die Innenarbeit einer Abfrage zu visualisieren und zu verstehen – insbesondere, wie SQLite bei der Feh absence eines Indexes eine volle Tabelle durchsucht.

Lassen Sie uns EXPLAIN QUERY PLAN verwenden, um zu sehen, wie SQLite Daten aus der Students-Tabelle ohne Indexe holt. Wir suchen nach einem Studenten nach Namen, und der Abfrageplan wird die Schritte aufzeigen, die SQLite unternimmt, um die passenden Zeilen zu finden.

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()

        # Verwenden Sie EXPLAIN QUERY PLAN, um zu analysieren, wie die Abfrage ausgeführt wird
        cursor.execute('''
        EXPLAIN QUERY PLAN
        SELECT * FROM Students WHERE name = ?;
        ''', (search_name,))

        # Holen und anzeigen Sie den Abfrageplan
        query_plan = cursor.fetchall()

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


# Beispiel: Analyse des Abfrageplans für die Suche per Name
explain_query('Ojasvi Dhawan')

Wenn Sie diesen Code ausführen, wird SQLite eine Zusammenfassung der Ausführung der Abfrage zurückgeben. Hier ist ein Beispiel, was die Ausgabe möglicherweise aussehen kann:

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

Dies zeigt an, dass SQLite die gesamte Students-Tabelle durchsucht (eine volle Tabelle durchsuchen), um die Zeilen zu finden, in denen die Spalte name den gegebenen Wert deckt (Ojasvi Dhawan). Da es kein Index auf der Spalte name gibt, muss SQLite jede Zeile in der Tabelle untersuchen.

Wie man einen Index erstellt

Die Erstellung eines Indexes in einer Spalte erlaubt es SQLite, Zeilen schneller zu finden, während sich auf Abfrageoperationen befindet. Anstatt die gesamte Tabelle zu durchsuchen, kann SQLite den Index verwenden, um direkt zu den relevanten Zeilen zu springen, was die Abfragen erheblich beschleunigt – insbesondere jene, die große Datenmengen betreffen.

Um einen Index zu erstellen, verwendet man den folgenden SQL-Befehl:

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

In diesem Beispiel wird ein Index auf der Spalte name der Tabelle Students erstellt. Hier ist wie du das mit Python tun kannst:

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-Befehl, um auf der name-Spalte einen Index zu erzeugen
        create_index_query = '''
        CREATE INDEX IF NOT EXISTS idx_name ON Students (name);
        '''

        # Messung des Startzeitpunkts
        start_time = time.perf_counter_ns()

        # Ausführen des SQL-Befehls, um den Index zu erzeugen
        cursor.execute(create_index_query)

        # Messung des Startzeitpunkts
        end_time = time.perf_counter_ns()

        # Speichern der Änderungen
        connection.commit()

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

        # Berechnen der insgesamt verwendeten Zeit
        elapsed_time = (end_time - start_time) / 1000

        # Anzeigen der Ergebnisse und der verwendeten Zeit
        print(f"Query completed in {elapsed_time:.5f} microseconds.")


# Aufruf der Funktion, um den Index zu erstellen
create_index()

Ausgabe:

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

Obwohl die Erstellung des Indexes so lange dauert (102768,6 Mikrosekunden), ist es eine einmalige Operation. Du wird immer noch eine erhebliche Beschleunigung erhalten, wenn du mehrere Abfragen ausführen lässt. In den folgenden Abschnitten werden wir die Datenbank erneut abfragen, um die Leistungsverbesserungen zu beobachten, die durch diesen Index möglich sind.

Wie man mit Indexen abfragt

In diesem Abschnitt führen wir die gleiche SELECT-Abfrage durch, die wir zuvor durchgeführt haben, aber diesmal nutzen wir die Index-Schicht, die wir auf der Spalte name der Students-Tabelle erstellt haben. Wir messen und protokollieren die Ausführungszeit, um die Leistungsverbesserungen durch den Index zu beobachten.

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-Befehl, um einen Studenten nach Namen auszuwählen
        select_query = 'SELECT * FROM Students WHERE name = ?;'

        # Messen der Ausführungszeit
        start_time = time.perf_counter_ns()  # Start der Zeitmessung

        # Führe die Abfrage mit dem angegebenen Studentennamen aus
        cursor.execute(select_query, (student_name,))
        result = cursor.fetchall()  # Holze alle Ergebnisse

        end_time = time.perf_counter_ns()  # Beende die Zeitmessung

        # Berechne die vergangene Zeit in Mikrosekunden
        execution_time = (end_time - start_time) / 1000

        # Zeige die Ergebnisse und die Ausführungszeit an
        print(f"Query result: {result}")
        print(f"Execution time with index: {execution_time:.5f} microseconds")


# Beispiel: Suche nach einem Studenten nach Namen
query_with_index('Ojasvi Dhawan')

Das folgende ist das Ergebnis der Ausgabe:

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

Wir können eine erhebliche Verringerung der Ausführungszeit beobachten, verglichen mit dem Zeitpunkt, als die Abfrage ohne Index durchgeführt wurde.

Lassen Sie uns den Abfrageausführungsplan für die Abfrage mit dem Index auf der Spalte name der Students-Tabelle analysieren. Wenn Sie das gleiche Skript erneut ausführen, um die Abfrage zu erklären, erhalten Sie das untenstehende Ergebnis:

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

Der Plan zeigt nun, dass die Abfrage den Index idx_name verwendet, was die Anzahl der zu durchsuchenden Zeilen erheblich reduziert, was zu einer schnelleren Abfrageausführung führt.

Vergleich von Leistungsresultaten

Nun lassen wir uns die Leistungsresultate zusammenfassen, die wir erhalten haben, wenn wir mit und ohne Indizes abgefragt haben.

Vergleich der Ausführungszeiten

Abfragetyp Ausführungszeit (Mikrosekunden)
Ohne Index 1578,1
Mit Index 390,7

Zusammenfassung der Leistungsverbesserung

  • Die Abfrage mit dem Index ist etwa 4,04 Mal schneller als die Abfrage ohne den Index.

  • Die Ausführungszeit hat sich durch den Index um etwa 75,24% verbessert.

Best Practices für die Verwendung von Indizes

Indizes können die Leistung Ihrer SQLite-Datenbank erheblich steigern, aber sie sollten vorsichtig eingesetzt werden. Hier sind einige Best Practices, die Sie bei der Arbeit mit Indizes berücksichtigen sollten:

Wann und Warum Indizes verwenden

  1. Häufig abgefragte Spalten: Verwenden Sie Indizes auf Spalten, die oft in SELECT-Abfragen verwendet werden, besonders in那些, die in WHERE, JOIN und ORDER BY-Klauseln stehen. Dies liegt daran, dass die Indizierung dieser Spalten die Ausführungszeit der Abfragen erheblich verringern kann.

  2. Eindeutigkeitsbedingungen: Wenn Sie Kolonnen haben, die eindeutige Werte aufnehmen müssen (wie Benutzernamen oder E-Mail-Adressen), kann die Erstellung eines Indexes diese Bedingung effizient durchsetzen.

  3. Große Datenmengen: Für Tabellen mit einer großen Anzahl von Datensätzen werden Indizes zunehmend vorteilhaft. Sie ermöglichen schnelle Suchoperationen, was für die Performance wichtig ist, wenn Ihre Daten zunehmend werden.

  4. Komposite Indizes: Erwägen Sie die Erstellung von Komposite-Indizes für Abfragen, die nach mehreren Kolonnen gefiltert oder sortiert werden. Zum Beispiel kann ein Index auf beiden Kolonne für Abfragen optimiert werden, die häufig nach Studenten mit Name und Alter suchen.

Mögliche Nachteile von Indizes

Obwohl Indizes erhebliche Vorteile bieten, gibt es einige möglichen Nachteile:

  1. langsamere Einfüge-/Aktualisierungsoperationen: Wenn Sie in einer mit Indizes versehenen Tabelle Datensätze einfügen oder aktualisieren, muss SQLite auch den Index aktualisieren, was diese Operationen verlangsamen kann. Dies ist aufgrund der zusätzlichen Overhead-Aufwendungen notwendig, um die Indizestruktur zu pflegen.

  2. erhöhte Speicheranforderungen: Indizes verbrauchen zusätzlichen Festplattenplatz. Für große Tabellen kann die Speicherkosten erheblich sein. Beachte dies beim Entwerfen Ihrer Datenbankschemas, insbesondere für Systeme mit begrenzten Speicherressourcen.

  3. Komplexes Indexmanagement : Zu viele Indexe können die Datenbankverwaltung komplizieren. Es kann zu Situationen führen, in denen Sie doppelte Indexe haben, die das Performance erhöhen statt reduzieren können. Die regelmäßige Überprüfung und Optimierung Ihrer Indexe ist eine gute Praxis.

Indexe sind starke Werkzeuge zur Optimierung von Datenbankabfragen, aber sie erfordern sorgfältige Überlegungen. Der Schwerpunkt zwischen verbesserter Lesebetriebsschnelligkeit und dem potenziellen Overhead bei Schreiboperationen ist Schlüssel. Hier sind einige Strategien, um diesen Schwerpunkt zu erreichen:

  • Überwachen Sie die Abfrageleistung : Verwenden Sie SQLite’s EXPLAIN QUERY PLAN, um zu analysieren, wie Ihre Abfragen mit und ohne Indexe funktionieren. Dies kann helfen, zu identifizieren, welche Indexe nützlich sind und welche möglicherweise nicht notwendig sind.

  • Regelmäßiges Wartungsarbeit : Periodisch Überprüfen Sie Ihre Indexe und prüfen Sie, ob sie immer noch notwendig sind. Entfernen Sie doppelte oder selten verwendete Indexe, um Ihre Datenbankoperationen zu vereinfachen.

  • Testen und Bewerten: Bevor Sie Indizes in einer Produktionsumgebung implementieren, führen Sie gründliche Tests durch, um ihren Einfluss auf Lesen und Schreiben zu verstehen.

Wenn Sie diese Best Practices befolgen, können Sie die Vorteile von Indizierung nutzten und potenzielle Nachteile minimieren, was letztendlich die Leistung und Effizienz Ihrer SQLite-Datenbank verbessert.

Umgang mit Fehlern und Ausnahmen

In diesem Abschnitt werden wir erklären, wie Sie mit Fehlern und Ausnahmen in der Arbeit mit SQLite in Python umgehen sollen. Eine korrekte Fehlerbehandlung ist entscheidend für die Wahrung der Integrität Ihrer Datenbank und die sichere Funktion Ihrer Anwendung.

Gebräuchliche Fehler in SQLite-Operationen

Beim Interagieren mit einer SQLite-Datenbank können verschiedene häufige Fehler auftreten:

  1. Constraint-Verletzungen: Dies tritt ein, wenn Sie versuchen, Daten einzufügen oder zu aktualisieren, die gegen eine Datenbankbedingung verstoßen, wie z.B. die Eindeutigkeit von Primärschlüsseln oder Fremdschlüsselbedingungen. Zum Beispiel wird ein Fehler ausgelöst, wenn Sie versuchen, einen doppelten Primärschlüssel einzufügen.

  2. Datentyp-Unterschiede: Der Versuch, Daten eines falschen Typs einzufügen (beispielsweise ein String anstelle einer Zahl einzufügen) kann zu einem Fehler führen.

  3. Datenbankverriegelungsfehler: Wenn eine Datenbank von einem anderen Prozess oder Verbindung bearbeitet wird, kann der Versuch, auf sie zuzugreifen, zu einem „Datenbank ist gesperrt“-Fehler führen.

  4. Syntaxfehler: Fehler in Ihrer SQL-Syntax führen zu Fehlern, wenn Sie versuchen, Ihre Befehle auszuführen.

Wie verwenden Sie die Ausnahmebehandlung in Python?

Pythons integriertes Fehlerbehandlungssystem (try und except) ist entscheidend, um Fehler während von SQLite-Operationen zu verwalten. Durch Verwendung dieser Strukturen können Sie Ausnahmen aufspüren und angemessen reagieren, ohne Ihr Programm abzuschalten.

Hier ist ein grundlegendes Beispiel, wie man bei Datenbankoperationen auf Fehler reagieren kann:

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}")


# Beispiel Nutzung
add_customer_with_error_handling('Vishakha', 100.0)  # Gültig
add_customer_with_error_handling('Vishakha', 150.0)  # Doppelter Eintrag

In diesem Beispiel:

  • Wir fangen IntegrityError ab, der ausgelöst wird, wenn z.B. ein einzigartigkeitsbedingtes Vergehen auftritt.

  • Wir fangen OperationalError für allgemeine Datenbankprobleme ab (wie z.B. Datenbankversperrungsfehler).

  • Wir haben auch einen allgemeinen except-Block, um unerwartete Ausnahmen zu behandeln.

Ausgabe:

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

Best Practices für die Sicherung der Datenbankintegrität

  1. Verwenden Sie Transaktionen: Verwenden Sie immer Transaktionen (wie im vorherigen Abschnitt besprochen), wenn mehrere zusammenhängende Operationen durchgeführt werden. Dies hilft sicherzustellen, dass entweder alle Operationen erfolgreich sind oder keine, um die Konsistenz zu gewährleisten.

  2. Validieren Sie Eingabedaten: Überprüfen Sie vor der Ausführung von SQL-Befehlen die Eingabedaten, um sicherzustellen, dass sie den erwarteten Kriterien entsprechen (zum Beispiel korrekte Typen, innerhalb zulässiger Bereiche).

  3. Fangen Sie spezifische Ausnahmen ab: Fangen Sie immer spezifische Ausnahmen ab, um verschiedene Arten von Fehlern angemessen zu behandeln. Dies ermöglicht eine klarere Fehlerbehandlung und Debugging.

  4. Fehler protokollieren: Statt Fehler nur auf der Konsole auszugeben, sollten Sie sie in eine Datei oder ein Überwachungssystem protokollieren. Dies hilft Ihnen, Probleme in der Produktion nachzuverfolgen.

  5. Sanfte Degradierung: Entwerfen Sie Ihre Anwendung so, dass sie Fehler sanft behandelt. Wenn eine Operation fehlschlägt, geben Sie dem Benutzer sinnvolle Rückmeldung anstatt die Anwendung abzustürzen.

  6. Regelmäßiges Backup von Daten: Machen Sie regelmäßig Backups Ihrer Datenbank, um Datenverlust infolge kritischer Fehler oder Korruption zu vermeiden.

  7. Verwenden Sie vorbereitete Anweisungen: Vorbereitete Anweisungen helfen, SQL-Injektionsangriffe zu vermeiden und können auch für wiederholte Abfragen eine bessere Leistung bieten.

Wie Daten exportieren und importieren [Bonusbereich]

In diesem Abschnitt lernen wir, wie man Daten aus einer SQLite-Datenbank in gemeinsame Formate wie CSV und JSON exportiert以及如何使用Python从这些格式将数据导入SQLite。这对于数据共享、备份和与其他应用程序的集成非常有用。

Daten aus SQLite nach CSV exportieren

Den Export von Daten in eine CSV-Datei (Kommagetrennte Werte) ist mit den in Python integrierten Bibliotheken einfach. CSV-Dateien werden breit für DatenSpeicherung und Austausch verwendet, was sie zu einem praktischen Format für den Datenexport macht.

So kannst du Daten aus einer SQLite-Tabelle in eine CSV-Datei exportieren:

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()

        # Führe einen Query aus, um alle Kunden Daten abzurufen
        cursor.execute("SELECT * FROM Customers;")
        customers = cursor.fetchall()

        # Schreibe Daten in eine CSV-Datei
        with open(file_name, 'w', newline='') as csv_file:
            csv_writer = csv.writer(csv_file)
            csv_writer.writerow(['ID', 'Name', 'Balance'])  # Schreibe Kopfzeile
            csv_writer.writerows(customers)  # Schreibe Datenzeilen

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

# Beispielhafte Verwendung
export_to_csv('customers.csv')

Wie du Daten in eine JSON-Datei exportieren kannst:

Ähnlich kannst du auch Daten in eine JSON (JavaScript Object Notation) Datei exportieren, die ein beliebtes Format für Datenaustausch ist, insbesondere in Webanwendungen.

Hier ist ein Beispiel, wie du Daten in eine JSON-Datei exportieren kannst:

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()

        # Führe einen Query aus, um alle Kunden Daten abzurufen
        cursor.execute("SELECT * FROM Customers;")
        customers = cursor.fetchall()

        # wandle Daten in eine Liste von Dictionaries um
        customers_list = [{'ID': customer[0], 'Name': customer[1],
                           'Balance': customer[2]} for customer in customers]

        # Schreibe Daten in eine JSON-Datei
        with open(file_name, 'w') as json_file:
            json.dump(customers_list, json_file, indent=4)

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


# Beispielhafte Verwendung
export_to_json('customers.json')

Wie du Daten aus einer CSV-Datei in eine SQLite-Datenbank importieren kannst:

Du kannst auch Daten aus einer CSV-Datei in eine SQLite-Datenbank importieren. Dies ist nützlich, wenn du deine Datenbank mit bestehenden Datensätzen befüllen möchtest.

Hier ist, wie du Daten aus einer CSV-Datei importieren kannst:

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()

        # Datei zum Lesen öffnen
        with open(file_name, 'r') as csv_file:
            csv_reader = csv.reader(csv_file)
            next(csv_reader)  # Kopfzeile überspringen

            # Jeder Datensatz in die Kunden-Tabelle einfügen
            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}.")


# Beispiel für die Verwendung
import_from_csv('customer_data.csv')

Wie man Daten in SQLite aus JSON importiert

Der Import von Daten aus einer JSON-Datei ist ebenfalls einfach. Man kann die JSON-Datei lesen und die Daten in seine SQLite-Tabelle einfügen.

Hier ist wie man das tut:

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()

        # Datei zum Lesen öffnen
        with open(file_name, 'r') as json_file:
            customers_list = json.load(json_file)

            # Jeder Kunde in die Kunden-Tabelle einfügen
            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}.")


# Beispiel für die Verwendung
import_from_json('customer_data.json')

Schließlich

Und das ist das Ende! Dieser Leitfaden hat Ihnen die Grundlagen der Arbeit mit SQLite in Python gezeigt, einschließlich der Einrichtung Ihrer Umgebung, der Abfrage und Manipulation von Daten sowie des Export- und Importvorganges. Ich hoffe, es war Ihnen hilfreich und hat Ihr Interesse an der Verwendung von SQLite für Ihre Projekte geweckt.

Jetzt ist es Zeit, Ihr neu erworbenes Wissen in die Praxis umzusetzen! Ich ermutige Sie, Ihr Projekt mit SQLite und Python zu erstellen. Ob es ein einfaches Programm für die Verwaltung Ihrer Bibliothek, ein Budgets tool oder etwas Unikums ist, die Möglichkeiten sind unbegrenzt.

Sobald Sie Ihr Projekt fertiggestellt haben, teilen Sie es auf Twitter mit und lassen Sie mich mitmachen! Ich würde mich freuen, das zu sehen, das du geschafft hast und deine Errungenschaften zu feiern.

Sie können all den Code aus diesem Tutorial auf GitHub finden. Vielen Dank, dass Sie mitgegangen sind, und viel Spass mit dem Programmieren!

Erstellen Sie kostenlos eine Inhaltsverzeichnis für Ihre freeCodeCamp-Artikel mit dem Inhaltsverzeichnis-Generator-Tool.