Pandas to_sql() Methode: Tipps für effizientes SQL-Schreiben
- Name
- Matt Popovic
Published on
Die to_sql() Methode in Pandas ist ein leistungsstolzes Tool zum Schreiben von Daten aus einem DataFrame in eine SQL-Datenbank. Sie wird häufig in Szenarien wie dem Senden von Daten aus Batch-Machine-Learning-Vorhersagen, der Verfolgung von Metriken und der Speicherung von vorbereiteten Datensätzen verwendet. Um to_sql() effektiv zu nutzen, ist jedoch ein gutes Verständnis seiner Parameter und möglicher Fallstricke erforderlich.
Dieser Artikel bietet einen umfassenden Leitfaden zur Verwendung der to_sql() Methode in Pandas, wobei der Schwerpunkt auf bewährten Verfahren und Tipps für sicheres und effizientes SQL-Schreiben liegt. Wir werden alles von der Verbindung zu Ihrer Datenbank bis zur Handhabung großer Datensätze und der Verbesserung der Leistung abdecken.
Möchten Sie schnell Data Visualization aus Python Pandas Dataframe ohne Code erstellen?
PyGWalker ist eine Python-Bibliothek für explorative Datenanalyse mit Visualisierung. PyGWalker (opens in a new tab) kann Ihren Workflow zur Datenanalyse und Datenvisualisierung in Jupyter Notebook vereinfachen, indem es Ihren Pandas-DataFrame (und Polars-DataFrame) in eine Tableau-ähnliche Benutzeroberfläche für visuelle Exploration umwandelt.
Teil 1: Installation der Anforderungen und Verbindung zur Datenbank
Bevor Sie die to_sql() Methode verwenden können, müssen Sie die benötigten Pakete installieren, nämlich pandas und sqlalchemy. Es wird empfohlen, für jedes Projekt eine separate Python-Umgebung zu erstellen. Dies können Sie mit Conda tun:
conda create -n env_name python=3.9 pandas sqlalchemy
conda activate env_nameNachdem Sie die erforderlichen Pakete installiert haben, besteht der nächste Schritt darin, eine Verbindung zu Ihrer Datenbank herzustellen. Dieses Verbindungsobjekt kann entweder von sqlite3 oder sqlalchemy bereitgestellt werden. In diesem Leitfaden werden wir sqlalchemy verwenden, da es der von Pandas empfohlene Ansatz ist.
from sqlalchemy import create_engine
# Verbindung zu Snowflake mit Ihrem Account und Benutzer erstellen
account_identifier = '<account_identifier>'
user = '<user_login_name>'
password = '<password>'
conn_string = f"snowflake://{user}:{password}@{account_identifier}/"
engine = create_engine(conn_string)Teil 2: Verständnis der Parameter für to_sql()
Die to_sql() Methode hat mehrere Parameter, die die Funktionalität und die Daten, die an die Datenbank gesendet werden, anpassen. Dazu gehören:
name: SQL-Tabellenname (erforderlich)con: Verbindung, bereitgestellt von sqlite oder sqlalchemy (erforderlich)schema: SQL-Datenbankschemaif_exists: Was tun, wenn die Tabelle bereits vorhanden ist, entweder 'fail', 'replace' oder 'append'index: Ob die DataFrame-Indexspalte in die Tabelle geschrieben werden soll oder nichtindex_label: Der Name, der der Indexspalte gegeben werden soll, wenn sie in die Tabelle geschrieben wirdchunksize: Anzahl der in einem Schritt zu schreibenden Zeilendtype: Wörterbuch aller Spalten und ihrer Datentypenmethod: SQL-Einfügeklausel angeben
Das Verständnis dieser Parameter ist entscheidend für die effektive Verwendung der to_sql() Methode.
Teil 3: Schreiben in eine Datenbank mit to_sql()
Jetzt, da wir unsere Anforderungen installiert, die Verbindung hergestellt und die beteiligten Parameter verstanden haben, können wir beginnen, Daten in unsere Datenbank zu schreiben. In diesem Beispiel schreiben wir ein sehr einfaches DataFrame in eine Tabelle in Snowflake:
import pandas as pd
from sqlalchemy import create
```python
# Erstellen Sie Ihr DataFrame
table_name = 'jobs'
df = pd.DataFrame(data=[['Stephen','Data scientist'],['Jane','Data analyst']],columns=['Name','Job'])
# Was tun, wenn die Tabelle bereits vorhanden ist? ersetzen, anhängen oder fehlschlagen?
if_exists = 'replace'
# Schreiben Sie die Daten in Snowflake
with engine.connect() as con:
df.to_sql(
name=table_name.lower(),
con=con,
if_exists=if_exists
)Dieser Code erstellt ein DataFrame mit zwei Spalten, 'Name' und 'Job', und zwei Datenzeilen. Anschließend schreibt er dieses DataFrame in eine Tabelle in Snowflake. Der if_exists Parameter ist auf 'replace' gesetzt, was bedeutet, dass die Tabelle bei Vorhandensein durch das neue DataFrame ersetzt wird.
Teil 4: Verbessern der Geschwindigkeit des Datenbankeinfügens
Obwohl die to_sql() Methode einfach zu verwenden und bequem ist, kann sie bei der Arbeit mit großen Tabellen langsam sein. Dies liegt daran, dass to_sql() standardmäßig Daten zeilenweise in die Datenbank schreibt. Dies kann für große Datensätze ineffizient sein, da jeder Einfügevorgang einen erheblichen Overhead mit sich bringt.
Eine Möglichkeit, die Einfügegeschwindigkeit zu verbessern, ist die Verwendung der fast_executemany Option, die in einigen Datenbankverbindern verfügbar ist. Diese Option ermöglicht es to_sql(), Daten in Batches anstatt zeilenweise in die Datenbank zu schreiben. Dadurch kann der Overhead von Einfügevorgängen erheblich reduziert und die Leistung dramatisch verbessert werden.
Um fast_executemany zu verwenden, müssen Sie Ihren Engine mit der Option fast_executemany=True erstellen, wie folgt:
engine = create_engine(conn_string, fast_executemany=True)Mit dieser aktivierten Option schreibt to_sql() Daten in Batches in die Datenbank, was für große Datensätze wesentlich schneller sein kann als das schrittweise Schreiben einer Zeile.
Teil 5: Umgang mit NULL- oder NaN-Werten
Beim Schreiben eines DataFrame in eine SQL-Datenbank mit der to_sql() Methode ist es wichtig, zu berücksichtigen, wie NULL- oder NaN-Werte behandelt werden. Standardmäßig werden NaN-Werte im DataFrame von Pandas beim Schreiben in die Datenbank in NULL-Werte umgewandelt. Dies ist in der Regel das gewünschte Verhalten, da es der Datenbank ermöglicht, fehlende Werte in einer Weise zu behandeln, die mit ihrem Datentyp und ihren Einschränkungen konsistent ist.
Jedoch können Sie in einigen Fällen NaN-Werte anders behandeln wollen. Zum Beispiel können Sie NaN-Werte durch einen bestimmten Wert ersetzen, bevor Sie sie in die Datenbank schreiben. Dies können Sie mit der fillna()-Methode in pandas tun:
df = df.fillna(value)Dieser Code ersetzt alle NaN-Werte im DataFrame mit dem angegebenen Wert.
Teil 6: Hinzufügen von Datensätzen zu einer vorhandenen Tabelle
Die to_sql()-Methode bietet eine bequeme Möglichkeit, Datensätze zu einer vorhandenen Tabelle in einer SQL-Datenbank hinzuzufügen. Dazu müssen Sie einfach den Parameter if_exists auf 'append' setzen:
df.to_sql(name=table_name, con=con, if_exists='append')Dieser Code fügt die Datensätze im DataFrame zur vorhandenen Tabelle hinzu. Falls die Tabelle nicht existiert, wird sie erstellt.
Teil 7: Aktualisierung vorhandener Datensätze
Während die to_sql()-Methode keine direkte Unterstützung für die Aktualisierung vorhandener Datensätze in einer SQL-Datenbank bietet, können Sie dies durch Kombination von to_sql() mit anderen SQL-Operationen erreichen.
Sie können zum Beispiel to_sql() verwenden, um den DataFrame in eine temporäre Tabelle in der Datenbank zu schreiben und dann eine SQL UPDATE-Anweisung verwenden, um die Datensätze in der Zieltabelle auf Grundlage der Datensätze in der temporären Tabelle zu aktualisieren.
Hier ist ein Beispiel, wie Sie dies tun können:
# DataFrame in temporäre Tabelle schreiben
df.to_sql(name='temp_table', con=con, if_exists='replace')
# SQL UPDATE-Anweisung erstellen
update_sql = """
UPDATE target_table
SET target_table.column1 = temp_table.column1,
target_table.column2 = temp_table.column2,
...
FROM temp_table
WHERE target_table.id = temp_table.id
"""
# UPDATE-Anweisung ausführen
with engine.connect() as con:
con.execute(update_sql)Dieser Code schreibt zunächst den DataFrame in eine temporäre Tabelle in der Datenbank. Anschließend wird eine SQL UPDATE-Anweisung konstruiert, die die Datensätze in der Zieltabelle auf Grundlage der Datensätze in der temporären Tabelle aktualisiert. Zuletzt wird die UPDATE-Anweisung mithilfe des Verbindungobjekts ausgeführt.
Häufig gestellte Fragen
- Was ist die
to_sql()-Methode in pandas?
Die to_sql()-Methode in pandas ist eine Funktion, die es Ihnen ermöglicht, Daten aus einem DataFrame in eine SQL-Datenbank zu schreiben. Sie bietet eine bequeme Möglichkeit, pandas-Daten in einer Datenbank für langfristige Speicherung, den Austausch mit anderen Systemen oder die weitere Verarbeitung mit SQL zu speichern.
- Wie behandelt man NULL- oder NaN-Werte bei Verwendung von
to_sql()?
Standardmäßig wandelt pandas NaN-Werte im DataFrame beim Schreiben in die Datenbank in NULL um. Wenn Sie NaN-Werte anders behandeln möchten, können Sie die fillna()-Methode in pandas verwenden, um NaN-Werte durch einen bestimmten Wert zu ersetzen, bevor Sie sie in die Datenbank schreiben.
- Kann man
to_sql()verwenden, um Datensätze zu einer vorhandenen Tabelle hinzuzufügen?
Ja, man kann to_sql() verwenden, um Datensätze zu einer vorhandenen Tabelle in einer SQL-Datenbank hinzuzufügen. Dazu müssen Sie einfach den if_exists-Parameter auf 'append' setzen, wenn Sie to_sql() aufrufen.