A SQLite Tutorial with Python

Einleitung

in Diesem tutorial werden mithilfe von SQLite in Kombination mit Python-sqlite3-Schnittstelle. SQLite ist eine einzelne Datei relationale Datenbank mit den meisten Standard-Python-Installationen gebündelt. SQLite ist häufig die Technologie der Wahl für kleine Anwendungen, insbesondere für eingebettete Systeme und Geräte wie Telefone und Tablets, intelligente Geräte und Instrumente. Es ist jedoch nicht ungewöhnlich, dass es für kleine bis mittlere Web-und Desktop-Anwendungen verwendet wird.,

Erstellen einer Datenbank und Herstellen einer Verbindung

Das Erstellen einer neuen SQLite-Datenbank ist so einfach wie das Erstellen einer Verbindung mit dem Modul sqlite3 in der Python-Standardbibliothek. Um eine Verbindung herzustellen, müssen Sie lediglich einen Dateipfad an die connect(...) – Methode im sqlite3-Modul übergeben, und wenn die durch die Datei dargestellte Datenbank nicht vorhanden ist, wird eine erstellt an diesem Pfad.,

import sqlite3con = sqlite3.connect('/path/to/file/db.sqlite3')

Sie werden feststellen, dass Sie in der täglichen Datenbankprogrammierung ständig Verbindungen zu Ihrer Datenbank herstellen, daher ist es eine gute Idee, diese einfache Verbindungsanweisung in eine wiederverwendbare generalisierte Funktion zu verpacken.

Tabellen erstellen

Um Datenbanktabellen zu erstellen, benötigen Sie eine Vorstellung von der Struktur der Daten, die Sie speichern möchten. Es gibt viele Designüberlegungen, die die Tabellen einer relationalen Datenbank definieren, über die ganze Bücher geschrieben wurden., Ich werde nicht auf die Details dieser Praxis eingehen und es stattdessen dem Leser überlassen, sie weiter zu untersuchen.

Um unsere Diskussion über die SQLite-Datenbankprogrammierung mit Python zu unterstützen, werde ich jedoch von der Prämisse ausgehen, dass eine Datenbank für einen fiktiven Buchladen erstellt werden muss, in dem die folgenden Daten bereits gesammelt wurden Buchverkauf.,

customer	date	product	price
Alan Turing	2/22/1944	Introduction to Combinatorics	7.99
Donald Knuth	7/3/1967	A Guide to Writing Short Stories	17.99
Donald Knuth	7/3/1967	Data Structures and Algorithms	11.99
Edgar Codd	1/12/1969	Advanced Set Theory	16.,99

Nach der Inspektion dieser Daten ist es offensichtlich, dass es enthält Informationen über Kunden, Produkte und Bestellungen. Ein gängiges Muster im Datenbankdesign für Transaktionssysteme dieses Typs besteht darin, die Bestellungen in zwei zusätzliche Tabellen, Bestellungen und Positionen (manchmal auch als Bestelldetails bezeichnet) aufzuteilen, um eine größere Normalisierung zu erreichen.

In einem Python-Interpreter im selben Verzeichnis wie die db_utils.,py-Modul zuvor definiert, geben Sie die SQL für die Erstellung der Kunden und Produkte Tabellen folgt:

Der obige Code erstellt ein Verbindungsobjekt dann verwendet es einen Cursor-Objekt zu instanziieren. Das Cursor-Objekt wird zum Ausführen von SQL-Anweisungen in der SQLite-Datenbank verwendet.

Mit dem erstellten Cursor habe ich dann die SQL geschrieben, um die Kundentabelle zu erstellen, ihr einen Primärschlüssel zusammen mit einem Textfeld für Vor-und Nachnamen zu geben und sie einer Variablen namens customers_sqlzuzuweisen., Ich rufe dann die execute(...) – Methode des Cursorobjekts auf und übergebe ihr die Variable customers_sql. Ich erstelle dann auf ähnliche Weise eine Produkttabelle.

Sie können die sqlite_master-Tabelle, eine integrierte SQLite-Metadatentabelle, abfragen, um zu überprüfen, ob die obigen Befehle erfolgreich waren.

Um alle Tabellen in der aktuell verbundenen Datenbank anzuzeigen, fragen Sie die Spalte name der sqlite_master – Tabelle ab, wobei die type gleich „table“ist.,

Um sich das Schema der Tabellen anzusehen, fragen Sie die Spalte sql derselben Tabelle ab, in der die type immer noch „table“ und die name gleich „customers“ und/oder „products“ist.

Die nächste zu definierende Tabelle ist die Bestelltabelle, die Kunden Bestellungen über einen Fremdschlüssel und das Datum ihres Kaufs zuordnet. Da SQLite keinen tatsächlichen Datums – /Uhrzeitdatentyp unterstützt (oder eine Datenklasse, die mit der SQLite-Umgangssprache übereinstimmt), werden alle Daten als Textwerte dargestellt.,

Die letzte zu definierende Tabelle ist die Linientabelle, die eine detaillierte Abrechnung der Produkte in jeder Bestellung enthält.

Laden der Daten

In diesem Abschnitt werde ich zeigen, wie wir unsere Beispieldaten in die soeben erstellten Tabellen einfügen. Ein natürlicher Ausgangspunkt wäre, zuerst die Produkttabelle zu füllen, da wir ohne Produkte keinen Verkauf haben können und daher nicht die Fremdschlüssel hätten, die sich auf die Positionen und Bestellungen beziehen., Wenn ich mir die Beispieldaten ansehe, sehe ich, dass es vier Produkte gibt:

Der Workflow zum Ausführen von INSERT-Anweisungen ist einfach:

1. Verbinden Sie sich mit der Datenbank
2. Erstellen Sie ein Cursor-Objekt
3. Schreiben Sie eine parametrisierte insert SQL-Anweisung und speichern Sie sie als Variable
4. Rufen Sie die execute-Methode für das Cursor-Objekt auf und übergeben Sie ihm die SQL-Variable und die Werte, die als Tupel in die Tabelle

etwas mehr Code.,

Der obige Code scheint wahrscheinlich ziemlich offensichtlich zu sein, aber lassen Sie mich ein wenig darüber diskutieren, da hier einige wichtige Dinge vor sich gehen. Die insert-Anweisung folgt der Standard-SQL-Syntax mit Ausnahme des Bits ?. Die ?sind eigentlich Platzhalter in einer sogenannten“parametrisierten Abfrage“.

Parametrisierte Abfragen sind ein wichtiges Merkmal im Wesentlichen aller Datenbankschnittstellen zu modernen High-Level-Programmiersprachen wie dem sqlite3-Modul in Python., Diese Art von Abfrage dient dazu, die Effizienz von Abfragen zu verbessern, die mehrmals wiederholt werden. Vielleicht noch wichtiger ist, dass sie auch Eingaben bereinigen, die an die Stelle der Platzhalter ? treten, die während des Aufrufs der execute-Methode des Cursor-Objekts übergeben werden, um schändliche Eingaben zu verhindern, die zur SQL-Injektion führen. Das Folgende ist ein Comic aus dem beliebten xkcd.com blog beschreibt die Gefahren der SQL-Injection.

Um die verbleibenden Tabellen zu füllen, werden wir einem etwas anderen Muster folgen, um die Dinge ein wenig zu ändern., Der Workflow für jede Bestellung, identifiziert durch eine Kombination aus Vor-und Nachname des Kunden und dem Kaufdatum, lautet:

1. Fügen Sie den neuen Kunden in die Kundentabelle ein und rufen Sie seine Primärschlüssel-ID ab
2. Erstellen Sie einen Bestelleintrag basierend auf der Kunden-ID und dem Kaufdatum Dann rufen Sie seine Primärschlüssel-ID ab
3. Für jedes Produkt in der Bestellung Bestimmen Sie seine Primärschlüssel-ID und erstellen Sie einen Zeileneintrag, der die Bestellung und das Produkt verknüpft

Um die Dinge auf uns selbst zu vereinfachen, lassen Sie uns alle unsere Produkte nachschlagen., Machen Sie sich vorerst keine Sorgen über die Mechanik der SELECT SQL-Anweisung, da wir ihr in Kürze einen Abschnitt widmen werden.

Der erste Auftrag wurde am 22.02.1944 von Alan Turing erteilt, der Introduction to Combinatorics für 7,99 $erwarb.

Erstellen Sie zunächst einen neuen Kundendatensatz für Mr. Turing und bestimmen Sie dann seine Primärschlüssel-ID, indem Sie auf das Feld lastrowid des Cursorobjekts zugreifen.

Wir können jetzt einen Bestelleintrag erstellen, den neuen Bestellkennungswert erfassen und zusammen mit dem bestellten Produkt Herrn Turing einem Artikeleintrag zuordnen.,

Die restlichen Datensätze werden genau gleich geladen, mit Ausnahme der Bestellung von Donald Knuth, der zwei Zeileneinträge erhält. Die sich wiederholende Natur einer solchen Aufgabe besteht jedoch darin, dass diese Funktionalitäten in wiederverwendbare Funktionen umgewandelt werden müssen. In der db_utils.py modul fügen Sie den folgenden Code hinzu:

Awh, jetzt können wir mit etwas Effizienz arbeiten!

Sie müssen Ihren Python-Interpreter exit() und neu laden, damit Ihre neuen Funktionen im Interpreter zugänglich werden.,

Ich fühle mich gezwungen, als Student der Software-Handwerkskunst einen zusätzlichen Ratschlag zu geben. Wenn Sie mehrere Datenbankmanipulationen (in diesem Fall Einfügungen) durchführen, um eine kumulative Aufgabe zu erfüllen (dh eine Bestellung zu erstellen), wickeln Sie die Unteraufgaben (Erstellen eines Kunden, einer Bestellung und dann von Zeilenelementen) am besten in eine einzelne Datenbanktransaktion, damit Sie entweder auf Erfolg festschreiben oder ein Rollback durchführen können, wenn unterwegs ein Fehler auftritt.,

Das würde ungefähr so aussehen:

Ich möchte diesen Abschnitt mit einer kurzen Demonstration beenden, wie ein vorhandener Datensatz in der Datenbank aktualisiert wird. Lassen Sie uns den Leitfaden zum Schreiben von Kurzgeschichten‘ Preis auf 10,99 (going on sale) aktualisieren.

>>> update_sql = "UPDATE products SET price = ? WHERE id = ?">>> cur.execute(update_sql, (10.99, 2))

Abfragen der Datenbank

Im Allgemeinen ist die häufigste Aktion, die in einer Datenbank ausgeführt wird, das Abrufen einiger der darin gespeicherten Daten über eine SELECT-Anweisung. In diesem Abschnitt werde ich zeigen, wie Sie mit der sqlite3-Schnittstelle einfache Auswahlabfragen ausführen.,

Um eine grundlegende Multirow-Abfrage der customers-Tabelle durchzuführen, übergeben Sie eine SELECT-Anweisung an dieexecute(...) – Methode des Cursor-Objekts. Danach können Sie die Ergebnisse der Abfrage durchlaufen, indem Sie die Methode fetchall() desselben Cursorobjekts aufrufen.

Angenommen, Sie möchten stattdessen nur einen Datensatz aus der Datenbank abrufen. Sie können dies tun, indem Sie eine spezifischere Abfrage schreiben, z. B. für Donald Knuths ID von 2, und anschließend die Methode des Cursorobjekts aufrufen.,

>>> cur.execute("SELECT id, first_name, last_name FROM customers WHERE id = 2")>>> result = cur.fetchone()>>> print(result)(2, 'Donald', 'Knuth')

Sehen Sie, wie die einzelne Zeile jedes Ergebnisses in Form eines Tupels ist? Nun, während Tupel eine sehr nützliche pythonische Datenstruktur für einige Programmieranwendungsfälle sind, finden viele Leute sie ein bisschen hinderlich, wenn es um die Aufgabe des Datenabrufs geht. Es passiert einfach so, dass es eine Möglichkeit gibt, die Daten auf eine Weise darzustellen, die für einige vielleicht flexibler ist. Sie müssen lediglich die row_factory – Methode des Verbindungsobjekts auf etwas Geeigneteres setzen, z. B. sqlite3.Row., Auf diese Weise können Sie nach Position oder Schlüsselwortwert auf die einzelnen Elemente einer Zeile zugreifen.

Fazit

In diesem Artikel habe ich eine kurze Demonstration dessen gegeben, was meiner Meinung nach die wichtigsten Funktionen und Funktionen der sqlite3-Python-Schnittstelle für die leichte SQLite-Datenbank mit einzelnen Dateien sind, die mit den meisten Python-Installationen vorgebündelt ist., Ich habe auch versucht, einige Ratschläge zu Best Practices in Bezug auf die Datenbankprogrammierung zu geben, aber ich warne den Neuankömmling, dass die Feinheiten der Datenbankprogrammierung im Allgemeinen eine der anfälligsten für Sicherheitslücken auf Unternehmensebene sind und weitere Kenntnisse vor einem solchen Unternehmen erforderlich sind.