Performance Script DF to SQL

Installation und Anwendung von Datenbankschnittstellen wie SQLite, PostgreSQL, MariaDB/MySQL, der DB-API 2.0 und sonstigen Datenbanksystemen.
Antworten
Luhikg
User
Beiträge: 3
Registriert: Mittwoch 5. Oktober 2022, 12:41

Hallo zusammen,

ich versuche mit dem folgenden script ein Pandas DF mit 9000 rows und 13 spalten in eine MS SQL Tabelle zu übergeben.

Grundsätzlich tut es auch was es soll dauert aber bis zu 6 min, also gehe ich wohl irgendetwas grundsätzlich falsch an.
Ich hoffe jemand kann mir einen Tipp geben. Mit GTP bin ich schon gescheitert :D

Code: Alles auswählen

from dataclasses import dataclass
from typing import Dict, Any, Iterable
from pandas import DataFrame
from sqlalchemy import create_engine, inspect
import urllib
import pandas as pd

import pyodbc



@dataclass(frozen=True)
class ConnectionSettings:
    """Connection Settings."""
    server: str
    database: str
    username: str
    password: str
    #driver: str = '{ODBC Driver 18 for SQL Server}'
    #driver: str = '{ODBC Driver 13 for SQL Server}'
    driver: str = '{ODBC Driver 18 for SQL Server}'
    timeout: int = 30
class AzureDbConnection:
    db = None
    """
    Azure SQL database connection.
    """
    def __init__(self, conn_settings: ConnectionSettings, echo: bool = False) -> None:
        conn_params = urllib.parse.quote_plus(
            'Driver=%s;' % conn_settings.driver +
            'Server=tcp:%s.database.windows.net,1433;' % conn_settings.server +
            'Database=%s;' % conn_settings.database +
            'Uid=%s;' % conn_settings.username +
            'Pwd=%s;' % conn_settings.password +
            'Encrypt=yes;' +
            'TrustServerCertificate=no;' +
            'Connection Timeout=%s;' % conn_settings.timeout
        )
        conn_string = f'mssql+pyodbc:///?odbc_connect={conn_params}'
        #self.db = create_engine(conn_string, echo=echo)
        AzureDbConnection.db = create_engine(conn_string, echo=echo)
    def connect(self) -> None:
        """Estimate connection."""
        self.conn = self.db.connect()
    def get_tables(self) -> Iterable[str]:
        """Get list of tables."""
        inspector = inspect(self.db)
        return [t for t in inspector.get_table_names()]
    def dispose(self) -> None:
        """Dispose opened connections."""
        self.conn.close()
        self.db.dispose()
    def execute(self, query: str) -> None:
        """Execute query."""
        self.conn.execute(query)
class SQL_TabellenLadenBearbeiten:
    '''Ermöglicht die Auswahl von Spalten aus bestimmten Tabellen
    sowie Datumsbereich
    und gibt diese als DataFrame zurück'''

    def verbinder():

        conn_settings = ConnectionSettings(    
        server = 'XXXXXXX',
        database= 'XXXX',
        username='XXX',
        password='XXXX')
        db_conn = AzureDbConnection(conn_settings)
        return db_conn
 
    def sql_test(tabellenName, df):
        # truncate
        # Einfügen der Werte
        db_conn = SQL_TabellenLadenBearbeiten.verbinder()
        db_conn.connect() 
        with AzureDbConnection.db.begin() as connection:
            connection.execute(f"TRUNCATE TABLE [{tabellenName}]")
            df.to_sql(tabellenName, connection, if_exists='append', index=False, chunksize=1000)
        return print(f'Tabelle {tabellenName} wurde geleert und neu befüllt')
einfachTobi
User
Beiträge: 491
Registriert: Mittwoch 13. November 2019, 08:38

Zunächst sei mal gesagt, dass Klassen in Python kein Selbstzweck sind. Solange du nicht sinnvoll Daten und Methoden auf ihnen bündeln willst, gibt es keinen Grund solche Klassen zu bauen. Momentan führst du für jede neue Zeile ein INSERT aus. Das dauert natürlich lange. Das lässt sich durch DataFrame.to_sql(..., method="multi") beheben. Wie hast du die Chunksize festgelegt? Warum nur 1000 auf einmal und nicht alle?
Luhikg
User
Beiträge: 3
Registriert: Mittwoch 5. Oktober 2022, 12:41

Hallo Tobi,

erstmal danke für deine Antwort. Zum Hintergrund ich bin "frischling" und erst seit einer kurzen Zeit dabei mit Python zu arbeiten.
Ich bin hauptsächlich damit beschäftigt pandas zu lernen um Ergebnisse in Streamlit zu präsentieren. Im Try and Error mode.....
Daher bin ich nicht so firm im Bereich Klassen und deren Verwendung. Ich arbeite aber an mir :idea:

Wenn ich das auf DataFrame.to_sql(..., method="multi") umstelle und Chunksize weglasse (hatte gelesen in der Doku das dies helfen könnte den wert 1000 hatte ich mal random festgelegt) bekomme ich folgenden Fehler.

Code: Alles auswählen

ProgrammingError: (pyodbc.ProgrammingError) ('The SQL contains -31859 parameter markers, but 164749 parameters were supplied', 'HY000') 
Sirius3
User
Beiträge: 17710
Registriert: Sonntag 21. Oktober 2012, 17:20

Lustig, dass MS-SQL noch in der 16bit-Welt lebt, muß wohl Windows 3.11 kompatibel sein.
Benutzeravatar
__blackjack__
User
Beiträge: 13003
Registriert: Samstag 2. Juni 2018, 10:21
Wohnort: 127.0.0.1
Kontaktdaten:

@Luhikg: Da wird so einiges importiert was nicht verwendet wird. Alles aus `typing`, `DataFrame`, `pandas`, und `pyodbc`.

`SQL_TabellenLadenBearbeiten` ist keine Klasse. Das lässt schon der Name vermuten weil der kein ”Ding” im weitesten Sinne beschreibt. Und dann sind da gar keine Methoden drin, sondern einfach nur Funktionen. Klassen sind dazu da Zustand und Funktionen die darauf operieren zu einem Objekt mit Attributen und Methoden zusammen zu fassen. Dazu braucht es eine `__init__()` die das Objekt initialisiert. Und selbst wenn man eine `__init__()` und nur eine Methode hat, ist das ein Warnzeichen das man schauen sollte ob man da nicht eine einfache Funktion unnötig kompliziert als Klasse ausgedrückt hat.

`verbinder` wäre mit grossem V ein Name der auf eine Klasse hindeutet und mit kleinem V wäre das ein naheliegender Name für ein Objekt vom Typ `Verbinder`. Auf eine Funktion würde man da eher nicht kommen, weil Funktionen und Methoden üblicherweise nach *Tätigkeiten* benannt sind. Damit der Leser weiss was die Funktion/Methode macht, und um sie leicht von eher passiven Werten unterscheiden zu können. Hier wäre beispielsweise `verbinden()` ein passender Name. Wobei da aber auch gar nichts verbunden wird, denn da wird ja letztlich ein `Engine`-Objekt erstellt, das in einer `AzureDbConnection` gekapselt wird. Oder sollte, denn das ist ein globales Klassenattribut. Was es auf keinen Fall sein sollte. Das ``db = None`` auf Klassenebene ist falsch, das hat da nichts zu suchen.

Man muss auch nicht jedes kleine Zwischenergebnis an einen Namen binden. In `verbinder()` braucht man keinen einzigen lokalen Namen wirklich.

Das zusammbasteln der Verbindungs-URL mit ``+`` und ``%`` ist sehr umständlich und unübersichtlich. Du benutzt doch an anderen Stellen schon f-Zeichenkettenliterale‽ Letztlich ist das aber insgesamt ziemlich umständlich mit der zusätzlichen `ConnectionSettings`-Klasse. Das man bei SQLAlchemy die Argumente für die Datenbankverbindung als URL angeben kann, die man beispielsweise auch in einer Umgebundvariable oder einer Konfigurationsdatei speichern kann ist doch schon praktisch, wozu Code der das noch aus Werten zusammenbastelt? Wenn man das unbedingt machen will, hat SQLAlchemy eine `URL`-Klasse dafür.

Wenn man die Verbindungsdaten gleich als URL kodiert, dann bleibt bei `verbinder()` nur noch das hier übrig:

Code: Alles auswählen

def verbinder():
    return AzureDbConnection(DATABASE_URL)
Das rechtfertigt IMHO keine eigene Funktion mehr.

Die `__init__()` einer Klasse ist dazu da das Objekt zu initialisieren. Danach sollte man ein komplettes, benutzbares Objekt haben, mit allen Attributen die das jemals haben wird. Attribute sollten nicht in anderen Methoden später hinzugefügt werden. Man kann beim `AzureDbConnection`-Objekt nach dem erstellen beispielsweise nicht `dispose()` aufrufen, ohne einen `AttributeError` zu bekommen, weil es das `conn`-Attribut dann noch gar nicht gibt.

Das Attribut macht aber auch gar keinen Sinn, denn die Verbindungen verwaltet ja schon das `Engine`-Objekt. Warum holst Du da *eine* Verbindung raus und speicherst die als Attribut? Die wird im ganzen Programm auch überhaupt gar nicht verwendet. Und auch gar nicht wieder sauber geschlossen, weil `dispose()` nirgends aufgerufen wird.

Letztlich kapselt (wenn man es richtig macht) `AzureDbConnection` nur ein `Engine`-Objekt ohne wirklichen Mehrwert. Aber mit sehr verirrenden Namen. Denn die Zeile ``with db_conn.db.begin() as connection:`` wirft Fragezeichen auf. Da steht ja, das man von der Datenbankverbindung (`db_conn`) über die Datenbank (`db`) mit `begin()` eine Verbindung (`connection`) bekommt. Von der Verbindung bekommt man eine Verbindung klingt unsinnig. Die Klasse kann also weg.

`get_tables()` ist die einzige Methode die dann übrig bleibt, und die wird zu einer einfachen Funktion der man das `Engine`-Objekt übergibt.

Die Funktion sollte eher `get_table_names()` heissen, wie die Methode die darin aufgerufen wird, denn bei `tables` erwartet der Leser, dass `Table`-Objekte geliefert werden, die SQLAlchemy ja auch hat.

„List comprehensions“ der Form ``[x for x in iterable]`` sind unnötig, da kann man einfach ``list(iterable)`` für schreiben. Aber die Methode gibt bereits eine Liste zurück, warum also die Werte noch mal in eine neue Liste umkopieren‽

Den Rückgabewert von `print()` zurück zu geben ist sinnfrei — das ist `None`.

Bleibt am Ende das hier übrig (ungetestet):

Code: Alles auswählen

#!/usr/bin/env python3
from sqlalchemy import create_engine, inspect

DATABASE_URL = (
    "mssql+pyodbc://user:password@tcp:XXXX.database.windows.net:1433/database"
    "?driver={ODBC+Driver+18+for+SQL+Server}"
    "&encrypt=yes"
    "&trustservercertificate=no"
    "&connection+timeout=30"
)


def get_table_names(engine):
    return inspect(engine).get_table_names()


def sql_test(tabellenname, dataframe):
    with create_engine(DATABASE_URL).begin() as connection:
        connection.execute(f"TRUNCATE TABLE [{tabellenname}]")
        dataframe.to_sql(
            tabellenname,
            connection,
            if_exists="append",
            index=False,
            chunksize=1000,
        )
    print(f"Tabelle {tabellenname!r} wurde geleert und neu befüllt")
“Most people find the concept of programming obvious, but the doing impossible.” — Alan J. Perlis
Antworten