Verwenden von SQL mit Python: SQLAlchemy und Pandas

Bild vom Autor

Als Datenwissenschaftler benötigen Sie Python für detaillierte Datenanalyse, Datenvisualisierung und Modellierung. Wenn Ihre Daten jedoch in einer relationalen Datenbank gespeichert sind, müssen Sie SQL (Structured Question Language) verwenden, um die Daten zu extrahieren und zu bearbeiten. Aber wie integrieren Sie SQL mit Python, um das volle Potenzial Ihrer Daten auszuschöpfen?

In diesem Tutorial lernen wir, die Leistungsfähigkeit von SQL mit der Flexibilität von Python mithilfe von SQLAlchemy und Pandas zu kombinieren. Wir lernen, wie man eine Verbindung zu Datenbanken herstellt, SQL-Abfragen mit SQLAlchemy ausführt und Daten mit Pandas analysiert und visualisiert.

Installieren Sie Pandas und SQLAlchemy mit:

pip set up pandas sqlalchemy

1. Speichern des Pandas DataFrame als SQL-Tabelle

Um die SQL-Tabelle mit dem CSV-Datensatz zu erstellen, werden wir:

1. Erstellen Sie mit SQLAlchemy eine SQLite-Datenbank.
2. Laden Sie den CSV-Datensatz mit den Pandas. Länder_Bevölkerung Der Datensatz besteht aus dem Luftqualitätsindex (AQI) für alle Länder der Welt von 2017 bis 2023.
3. Konvertieren Sie alle AQI-Spalten von Objekten in numerische Spalten und löschen Sie Zeilen mit fehlenden Werten.

# Import vital packages
import pandas as pd
import psycopg2
from sqlalchemy import create_engine
 
# creating the brand new db
engine = create_engine(
    "sqlite:///kdnuggets.db")
 
# learn the CSV dataset
knowledge = pd.read_csv("/work/air_pollution new.csv")

col = ('2017', '2018', '2019', '2020', '2021', '2022', '2023')

for s in col:
    knowledge(s) = pd.to_numeric(knowledge(s), errors="coerce")

    knowledge = knowledge.dropna(subset=(s))

4. Speichern Sie den Pandas-Datenrahmen als SQL-Tabelle. Die Funktion „to_sql“ erfordert einen Tabellennamen und das Engine-Objekt.

# save the dataframe as a SQLite desk
knowledge.to_sql('countries_poluation', engine, if_exists="substitute")

Als Ergebnis wird Ihre SQLite-Datenbank in Ihrem Dateiverzeichnis gespeichert.

Notiz: Ich verwende für dieses Tutorial Deepnote, um den Python-Code nahtlos auszuführen. Deepnote ist ein kostenloses AI Cloud Pocket book, mit dem Sie jeden Knowledge Science-Code schnell ausführen können.

2. Laden der SQL-Tabelle mit Pandas

Um die gesamte Tabelle aus der SQL-Datenbank als Pandas-Datenrahmen zu laden, werden wir:

1. Stellen Sie die Verbindung mit unserer Datenbank her, indem Sie die Datenbank-URL angeben.
2. Verwenden Sie die Funktion „pd.read_sql_table“, um die gesamte Tabelle zu laden und in einen Pandas-Datenrahmen zu konvertieren. Die Funktion erfordert Tabellenanime, Engine-Objekte und Spaltennamen.
3. Zeigen Sie die obersten 5 Zeilen an.

import pandas as pd
import psycopg2
from sqlalchemy import create_engine
 
# set up a reference to the database
engine = create_engine("sqlite:///kdnuggets.db")
 
# learn the sqlite desk
table_df = pd.read_sql_table(
    "countries_poluation",
    con=engine,
    columns=('metropolis', 'nation', '2017', '2018', '2019', '2020', '2021', '2022',
       '2023')
)
 
table_df.head()

Die SQL-Tabelle wurde erfolgreich als Datenrahmen geladen. Das bedeutet, dass Sie sie jetzt verwenden können, um Datenanalysen und Visualisierungen mit gängigen Python-Paketen wie Seaborn, Matplotlib, Scipy, Numpy und anderen durchzuführen.

3. Ausführen der SQL-Abfrage mit Pandas

Anstatt uns auf eine Tabelle zu beschränken, können wir mit der Funktion `pd.read_sql` auf die gesamte Datenbank zugreifen. Schreiben Sie einfach eine einfache SQL-Abfrage und stellen Sie ihr das Engine-Objekt zur Verfügung.

Die SQL-Abfrage zeigt zwei Spalten aus der Tabelle „countries_population“ an, sortiert sie nach der Spalte „2023“ und zeigt die ersten fünf Ergebnisse an.

# learn desk knowledge utilizing sql question
sql_df = pd.read_sql(
    "SELECT metropolis,(2023) FROM countries_poluation ORDER BY (2023) DESC LIMIT 5",
    con=engine
)
 
print(sql_df)

Wir haben die High 5 der Städte mit der schlechtesten Luftqualität weltweit ermittelt.

         metropolis  2023
0       Lahore  97.4
1        Hotan  95.0
2      Bhiwadi  93.3
3  Delhi (NCT)  92.7
4     Peshawar  91.9

4. Verwenden des SQL-Abfrageergebnisses mit Pandas

Wir können auch die Ergebnisse der SQL-Abfrage verwenden und weitere Analysen durchführen. Berechnen Sie beispielsweise mit Pandas den Durchschnitt der fünf größten Städte.

average_air = sql_df('2023').imply()
print(f"The common of prime 5 cities: {average_air:.2f}")

Ausgabe:

The common of prime 5 cities: 94.06

Oder erstellen Sie ein Balkendiagramm, indem Sie die X- und Y-Argumente und den Diagrammtyp angeben.

sql_df.plot(x="metropolis",y="2023",type = "barh");

Abschluss

Die Möglichkeiten, SQLAlchemy mit Pandas zu verwenden, sind endlos. Sie können mit der SQL-Abfrage einfache Datenanalysen durchführen, aber um die Ergebnisse zu visualisieren oder sogar das maschinelle Lernmodell zu trainieren, müssen Sie es in einen Pandas-Datenrahmen konvertieren.

In diesem Tutorial haben wir gelernt, wie man eine SQL-Datenbank in Python lädt, Datenanalysen durchführt und Visualisierungen erstellt. Wenn Ihnen dieser Leitfaden gefallen hat, werden Sie auch Folgendes zu schätzen wissen:Eine Anleitung zum Arbeiten mit SQLite-Datenbanken in Python“, das eine ausführliche Erkundung der Verwendung des in Python integrierten SQLite3-Moduls bietet.

Abid Ali Awan (@1abidaliawan) ist ein zertifizierter Datenwissenschaftler, der gerne Modelle für maschinelles Lernen erstellt. Derzeit konzentriert er sich auf die Erstellung von Inhalten und das Schreiben technischer Blogs zu Technologien für maschinelles Lernen und Datenwissenschaft. Abid hat einen Grasp-Abschluss in Technologiemanagement und einen Bachelor-Abschluss in Telekommunikationstechnik. Seine Imaginative and prescient ist es, mithilfe eines Graph-Neural-Networks ein KI-Produkt für Studenten zu entwickeln, die mit psychischen Erkrankungen zu kämpfen haben.