Python i PANDAS: Połączenie do bazy danych MSSQL

26-Lut-2019

Pyton jest dobrym środowiskiem do analizy danych, ale te dane skąś trzeba brać i gdzieś trzeba je zapisywać. Tu z pomocą przyjdzie moduł pyodbc. Oto przykład gdzie:

  • pobieramy dane z bazy danych AdventureWorks
  • zapisujemy te dane do obiektu data frame
  • tworzymy tabelę w bazie danych tempdb
  • zapisujemy wybrane dane z dataframe do tej tabeli

Po kolei:

-ładujemy moduły

-tworzymy obiekt połączenia – należy w odpowiedni sposób wykonać to połączenie. Jeśli Trusted_Connection=yes, to nie trzeba podawać user, bo zostanie wykorzystane uwierzytelnienie windows

zapytanie zapisujemy w postaci napisu:

query = "select CurrencyCode, Name, ModifiedDate from Sales.Currency"

dane pobiera się do data frame wykonując polecenie w ramach tego połączenia do bazy danych

A teraz w drugą stronę. Mamy już obiekt df i chcemy go zapisać w bazie tempdb. Interesują nas tylko 2 kolumny

-Znowu inicjuję połączenie, bo łączyć się będę do bazy tempdb

Polecenia jakie będą wykonywane to zapisy, które nie zwracają rekordów, więc trzeba mieć obiekt, który coś wykonuje, a takim obiektem jest cursor:

cursor = sql_conn.cursor()

Ja chcę móc wielokrotnie puszczać ten skrypt, więc sprawdzam czy jest w tempdb tabela dbo.currencies i jak jest to ją usuwam. Polecenie execute mówi co będzie wykonywane, a commit rzeczywiście wykonuje to polecenie:

a teraz tworzę tą tabelę na nowo:

Teraz przechodzę przez wszystkie wiersze data frame i każdy z nich zapisuję poleceniem insert w tabeli. Każdy insert jest commitowany:

Na końcu zamykamy cursor i połączenie (istotne ze względu na ograniczoną ilość zasobów – tu połączenia SQL)

U mnie zadziałało, zobacz jak będzie u ciebie. Zapisywanie rekord po rekordzie jest nieoptymalne. Zapisanie całego data frame w jednym commit może zapełnić log transakcyjny bazy danych. Idealnie byłoby to robić w paczkach np po 1000 lub po 10000 insertów w jednym commit.

Komentarze:

  1. Rafał Kraik napisał,

    Propozycja ładowania całego data frame od razu:

    import urllib
    from sqlalchemy import create_engine

    params = urllib.parse.quote_plus(„DRIVER={SQL Server};SERVER=server_sql;DATABASE=baza;UID=user;trusted_connection=yes”)
    engine = create_engine(„mssql+pyodbc:///?odbc_connect=%s” % params)

    df.to_sql(‚tabela_testowa’, engine, if_exists=’replace’)

Autor: Rafał Kraik