Erfahren Sie, wie Sie mit Python-Objekten auf alle Arten relationaler Datenbanken zugreifen und SQL-Abfragen ausführen.
Was ist SQLAlchemy?
SQLAlchemy ist das Python SQL-Toolkit, mit dem Entwickler mithilfe der Python-Domänensprache auf SQL-Datenbanken zugreifen und diese verwalten können. Sie können eine Abfrage in Form einer Zeichenfolge schreiben oder Python-Objekte für ähnliche Abfragen verketten. Das Arbeiten mit Objekten bietet Entwicklern Flexibilität und ermöglicht ihnen die Erstellung leistungsstarker SQL-basierter Anwendungen.
Einfach ausgedrückt ermöglicht es Benutzern, Datenbanken mithilfe der Programmiersprache Python zu verbinden, SQL-Abfragen mithilfe der objektbasierten Programmierung auszuführen und den Arbeitsablauf zu optimieren.
Installieren Sie SQLAlchemy
Es ist ziemlich einfach, das Paket zu installieren und mit der Codierung zu beginnen.
Sie können SQLAlchemy mit dem Python Package Manager (pip) installieren:
Falls Sie die Anaconda-Distribution von Python verwenden, versuchen Sie, den Befehl im Conda-Terminal einzugeben:
Lassen Sie uns überprüfen, ob das Paket erfolgreich installiert wurde:
Ausgezeichnet, wir haben SQLAlchemy Version 1.4.41 erfolgreich installiert.
Erste Schritte
In diesem Abschnitt lernen wir, SQLite-Datenbanken zu verbinden, Tabellenobjekte zu erstellen und sie zum Ausführen der SQL-Abfrage zu verwenden.
Verbinden der Datenbank
Wir verwenden die SQLite-Datenbank „European Football“ von Kaggle. Sie enthält zwei Tabellen: „ Divisionen“ und „Spiele“ .
Zuerst erstellen wir SQLite-Engine-Objekte mit „create_object“ und übergeben die Standortadresse der Datenbank. Dann erstellen wir ein Verbindungsobjekt, indem wir die Engine verbinden. Wir verwenden das „conn“-Objekt, um alle Arten von SQL-Abfragen auszuführen.
Wenn Sie PostgreSQL-, MySQL-, Oracle- und Microsoft SQL Server-Datenbanken verbinden möchten, überprüfen Sie die Engine-Konfiguration für eine reibungslose Verbindung mit dem Server.
Dieses SQLAlchemy-Tutorial setzt voraus, dass Sie die Grundlagen von Python und SQL verstehen. Wenn nicht, ist das völlig in Ordnung. Sie können die Kurse „SQL Fundamentals“ und „Python Fundamentals“ belegen, um eine solide Basis aufzubauen.
Zugriff auf die Tabelle
Um ein Tabellenobjekt zu erstellen, müssen wir Tabellennamen und Metadaten angeben. Sie können Metadaten mit der Funktion `MetaData()` von SQLAlchemy erstellen.
Drucken wir die Metadaten der „Abteilungen“ aus.
Die Metadaten enthalten den Tabellennamen, Spaltennamen mit Typ und Schema.
Verwenden wir das Tabellenobjekt „Division“, um Spaltennamen zu drucken.
Die Tabelle besteht aus den Spalten „Abteilung“, „Name“ und „Land“.
Einfache SQL-Abfrage
Jetzt kommt der spaßige Teil. Wir werden das Tabellenobjekt verwenden, um die Abfrage auszuführen und die Ergebnisse zu extrahieren.
Im folgenden Code wählen wir alle Spalten für die Tabelle „Division“ aus.
Hinweis : Sie können den Auswahlbefehl auch als „db.select([division])“ schreiben.
Um die Abfrage anzuzeigen, drucken Sie das Abfrageobjekt aus. Der SQL-Befehl wird dann angezeigt.
SQL-Abfrageergebnis
Wir führen die Abfrage jetzt mit dem Verbindungsobjekt aus und extrahieren die ersten fünf Zeilen.
- fetchone() : es wird jeweils eine einzelne Zeile extrahiert.
- fetchmany(n) : Es werden jeweils n Zeilen extrahiert.
- fetchall() : es werden alle Zeilen extrahiert.
Das Ergebnis zeigt die ersten fünf Zeilen der Tabelle.
Python SQLAlchemy Beispiele
In diesem Abschnitt sehen wir uns verschiedene SQLAlchemy-Beispiele zum Erstellen von Tabellen, Einfügen von Werten, Ausführen von SQL-Abfragen, Datenanalyse und Tabellenverwaltung an.
Sie können mitmachen oder sich dieses DataLab-Arbeitsbuch ansehen . Es enthält eine Datenbank, Quellcode und Ergebnisse.
Tabellen erstellen
Zuerst erstellen wir eine neue Datenbank namens „datacamp.sqlite“. Die create_engine erstellt automatisch eine neue Datenbank, wenn keine Datenbank mit demselben Namen vorhanden ist. Das Erstellen und Verbinden sind also ziemlich ähnlich.
Danach verbinden wir die Datenbank und erstellen ein Metadatenobjekt.
Wir werden die Tabellenfunktion von SQLAlchmy verwenden, um eine Tabelle namens „Student“ zu erstellen.
Es besteht aus den Spalten:
- Id : Ganzzahl und Primärschlüssel
- Name : Zeichenfolge und nicht nullwertfähig
- Major : Zeichenfolge und Standard = „Mathematik“
- Pass : Boolean und Standard = True
Wir haben die Struktur der Tabelle erstellt. Fügen wir sie mit `metadata.create_all(engine)` der Datenbank hinzu.
Fügen Sie einen
Um eine einzelne Zeile hinzuzufügen, verwenden wir zuerst `insert` und fügen das Tabellenobjekt hinzu. Verwenden Sie danach `values` und fügen Sie den Spalten manuell Werte hinzu. Dies funktioniert ähnlich wie das Hinzufügen von Argumenten zu Python-Funktionen.
Abschließend führen wir die Abfrage über die Verbindung aus, um die Funktion auszuführen.
Lassen Sie uns überprüfen, ob wir die Zeile zur Tabelle „Student“ hinzufügen, indem wir eine Auswahlabfrage ausführen und alle Zeilen abrufen.
Wir haben die Werte erfolgreich hinzugefügt.
Fügen Sie viele
Das Hinzufügen einzelner Werte ist keine praktische Methode zum Füllen der Datenbank. Fügen wir mehrere Werte mithilfe von Listen hinzu.
- Erstellen Sie eine Einfügeabfrage für die Student-Tabelle.
- Erstellen Sie eine Liste mit mehreren Zeilen mit Spaltennamen und Werten.
- Führen Sie die Abfrage mit einem zweiten Argument als values_list aus.
Um unsere Ergebnisse zu validieren, führen Sie die einfache Auswahlabfrage aus.
Die Tabelle enthält jetzt mehr Zeilen.
Einfache SQL-Abfrage mit SQLAlchemy
Anstatt Python-Objekte zu verwenden, können wir SQL-Abfragen auch mit String ausführen.
Fügen Sie das Argument einfach als Zeichenfolge zur Funktion „Ausführen“ hinzu und zeigen Sie das Ergebnis mit „Fetchall“ an.
Ausgabe:
Sie können sogar komplexere SQL-Abfragen übergeben. In unserem Fall wählen wir die Spalten „Name“ und „Hauptfach“ aus, in denen die Studenten die Prüfung bestanden haben.
Ausgabe:
Verwenden der SQLAlchemy-API
In den vorherigen Abschnitten haben wir einfache SQLAlchemy-API/-Objekte verwendet. Tauchen wir nun in komplexere und mehrstufige Abfragen ein.
Im folgenden Beispiel wählen wir alle Spalten aus, in denen das Hauptfach des Studenten Englisch ist.
Ausgabe:
Wenden wir die UND-Logik auf die WHERE-Abfrage an.
In unserem Fall suchen wir nach Studierenden, die Englisch als Hauptfach studiert haben und durchgefallen sind.
Hinweis : ungleich „!=“ True ist False.
Nur Ben hat die Prüfung mit Hauptfach Englisch nicht bestanden.
Mithilfe einer ähnlichen Tabelle können wir alle möglichen Befehle ausführen, wie in der folgenden Tabelle gezeigt.
Sie können diese Befehle kopieren und einfügen, um die Ergebnisse selbst zu testen. Sehen Sie sich die DataLab-Arbeitsmappe an , wenn Sie bei einem der angegebenen Befehle nicht weiterkommen.
Befehle | API |
In | Student.select(). where (Student.columns.Major.in_ ([‘Englisch’,’Mathe’])) |
und, oder, nicht | Student.select().where(db. oder_ (Student.columns.Major == ‘Englisch’, Student.columns.Pass = True)) |
Sortieren nach | Student.select(). order_by (db.desc(Student.columns.Name)) |
Grenze | Student.select(). grenze (3) |
Summe, Durchschnitt, Anzahl, Minimum, Maximum | db.select([db.func.sum ( Student.columns.Id)]) |
gruppiere nach | db.select([db.func.sum(Student.columns.Id),Student.columns.Major]). group_by (Student.columns.Pass) |
unterscheidbar | db.select([Student.Spalten.Hauptfach.unterschiedliche ( )]) |
Weitere Informationen zu anderen Funktionen und Befehlen finden Sie in der offiziellen Dokumentation der SQL Statements and Expressions API .
Ausgabe an Pandas DataFrame
Datenwissenschaftler und -analysten schätzen Pandas- Dataframes und arbeiten gerne mit ihnen. In diesem Teil lernen wir, wie man ein SQLAlchemy-Abfrageergebnis in einen Pandas-Dataframe konvertiert.
Führen Sie zunächst die Abfrage aus und speichern Sie die Ergebnisse.
Verwenden Sie dann die DataFrame-Funktion und geben Sie die SQL-Ergebnisse als Argument an. Fügen Sie abschließend die Spaltennamen mit dem Ergebnis der ersten Zeile `results[0]` und `.keys()` hinzu.
Hinweis : Sie können jede gültige Zeile angeben, um die Namen der Spalten mit „keys()“ zu extrahieren.
Datenanalyse mit SQLAlchemy
In diesem Teil werden wir die europäische Fußballdatenbank verbinden, komplexe Abfragen durchführen und die Ergebnisse visualisieren.
Verbinden zweier Tabellen
Wie üblich verbinden wir die Datenbank mit den Funktionen „create_engine“ und „connect“.
In unserem Fall werden wir zwei Tabellen verbinden, also müssen wir zwei Tabellenobjekte erstellen: Division und Match.
Ausführen einer komplexen Abfrage
- Wir wählen sowohl die Divisions- als auch die Match-Spalten aus.
- Verbinden Sie sie mit einer gemeinsamen Spalte: division.division und match.Div.
- Wählen Sie alle Spalten aus, bei denen die Division E1 und die Saison 2009 ist.
- Sortieren Sie das Ergebnis nach HomeTeam.
Sie können durch Hinzufügen zusätzlicher Module sogar noch komplexere Abfragen erstellen.
Hinweis : Zum automatischen Verbinden zweier Tabellen können Sie auch Folgendes verwenden: `db.select([division.columns.division,match.columns.Div])`
Nach der Ausführung der Abfrage haben wir das Ergebnis in einen Pandas-Datenrahmen konvertiert.
Beide Tabellen sind verknüpft und die Ergebnisse zeigen nur die E1-Division für die Saison 2009, sortiert nach der Spalte „HomeTeam“.
Datenvisualisierung
Da wir nun einen Datenrahmen haben, können wir die Ergebnisse mit Seaborn in Form eines Balkendiagramms visualisieren .
Wir werden :
- Stellen Sie das Thema auf „Whitegrid“ ein.
- Ändern Sie die Visualisierungsgröße auf 15 x 6
- X-Achsen-Teilstriche um 90 Grad drehen
- Stellen Sie die Farbpalette auf „Pastell“ ein
- Zeichnen Sie ein Balkendiagramm von „HomeTeam“ vs. „FTHG“ in der Farbe Blau.
- Zeichnen Sie ein Balkendiagramm von „HomeTeam“ vs. „FTAG“ mit der Farbe Rot.
- Zeigen Sie die Legende oben links an.
- Entfernen Sie die x- und y-Beschriftungen.
- Despine links und unten.
Der Hauptzweck dieses Teils besteht darin, Ihnen zu zeigen, wie Sie die Ausgabe der SQL-Abfrage verwenden und eine erstaunliche Datenvisualisierung erstellen können.
Ergebnisse im CSV-Format speichern
Nachdem Sie das Abfrageergebnis in einen Pandas-Datenrahmen konvertiert haben, können Sie einfach die Funktion „.to_csv“ mit dem Dateinamen verwenden.
Vermeiden Sie das Hinzufügen einer Spalte namens „Index“, indem Sie „index=False“ verwenden.
CSV-Datei in SQL-Tabelle
In diesem Teil konvertieren wir die CSV-Datei mit den Börsendaten in eine SQL-Tabelle.
Stellen Sie zunächst eine Verbindung zur Datacamp-SQLite-Datenbank her.
Importieren Sie dann die CSV-Datei mit der Funktion read_csv. Verwenden Sie zum Schluss die Funktion `to_sql`, um den Pandas-Datenrahmen als SQL-Tabelle zu speichern.
In erster Linie erfordert die Funktion `to_sql` Verbindung und Tabellennamen als Argument. Sie können auch `if_exisits` verwenden, um eine vorhandene Tabelle mit demselben Namen zu ersetzen, und `index`, um die Indexspalte zu löschen.
Um die Ergebnisse zu validieren, müssen wir eine Verbindung zur Datenbank herstellen und ein Tabellenobjekt erstellen.
Führen Sie dann die Abfrage aus und zeigen Sie die Ergebnisse an.
Wie Sie sehen, haben wir alle Werte erfolgreich aus der CSV-Datei in die SQL-Tabelle übertragen.
SQL-Tabellenverwaltung
Aktualisieren der Werte in der Tabelle
Das Aktualisieren von Werten ist unkompliziert. Wir verwenden die Funktionen update , values und where , um den jeweiligen Wert in der Tabelle zu aktualisieren.
In unserem Fall haben wir den Wert „Bestanden“ von „ False “ in „True“ geändert, wobei der Name der Studentin „Nisha“ ist.
Um die Ergebnisse zu validieren, führen wir eine einfache Abfrage aus und zeigen die Ergebnisse in Form eines Pandas-Datenrahmens an.
Wir haben den Wert „Bestanden“ für den Studentennamen „Nisha“ erfolgreich in „ True “ geändert.
Löschen der Datensätze
Das Löschen der Zeilen ähnelt dem Aktualisieren. Es erfordert die Lösch- und Where-Funktion.
In unserem Fall löschen wir den Datensatz des Studenten namens „Ben“.
Um die Ergebnisse zu validieren, führen wir eine schnelle Abfrage aus und zeigen die Ergebnisse in Form eines Datenrahmens an. Wie Sie sehen, haben wir die Zeile mit dem Studentennamen „Ben“ gelöscht stream of consciousness.
Tabellen löschen
Wenn Sie SQLite verwenden, wird beim Löschen der Tabelle der Fehler „Datenbank ist gesperrt“ ausgegeben. Warum? Weil SQLite eine sehr leichte Version ist. Es kann jeweils nur eine Funktion ausführen. Derzeit führt es eine Auswahlabfrage aus. Wir müssen die gesamte Ausführung beenden, bevor wir die Tabelle löschen.
Verwenden Sie anschließend die drop_all-Funktion der Metadaten und wählen Sie ein Tabellenobjekt aus, um die einzelne Tabelle zu löschen. Sie können auch den Befehl `Student.drop(engine)` verwenden, um eine einzelne Tabelle zu löschen.
Wenn Sie für die Funktion „drop_all“ keine Tabelle angeben, werden alle Tabellen in der Datenbank gelöscht.
Fazit
Das SQLAlchemy-Tutorial behandelt verschiedene Funktionen von SQLAlchemy, vom Verbinden der Datenbank bis zum Ändern von Tabellen. Wenn Sie mehr erfahren möchten, versuchen Sie, den interaktiven Kurs „Einführung in Datenbanken in Python“ abzuschließen . Sie lernen die Grundlagen relationaler Datenbanken, Filtern, Ordnen und Gruppieren kennen. Darüber hinaus lernen Sie erweiterte SQLAlchemy-Funktionen zur Datenmanipulation kennen.
Wenn Sie beim Befolgen des Tutorials auf Probleme stoßen, gehen Sie zur DataLab-Arbeitsmappe und vergleichen Sie Ihren Code damit. Sie können auch eine Kopie der Arbeitsmappe erstellen und diese direkt in DataLab ausführen.