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

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

import pandas as pd
import pyodbc

sql_conn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};\
                            SERVER=Dragon01;\
                            DATABASE=Adventureworks;\
                            Trusted_Connection=yes') 
query = "select CurrencyCode, Name, ModifiedDate from Sales.Currency"
df = pd.read_sql(query, sql_conn)
print(df.head(3))

sql_conn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};\
                            SERVER=Dragon01;\
                            DATABASE=tempdb;\
                            Trusted_Connection=yes')
cursor = sql_conn.cursor()

cursor.execute("IF OBJECT_ID('dbo.currencies') IS NOT NULL  DROP TABLE dbo.currencies")
sql_conn.commit()

cursor.execute("CREATE TABLE dbo.currencies(\
                             code VARCHAR(3),\
                             name VARCHAR(30))")
sql_conn.commit()

for index,row in df.iterrows():
  cursor.execute("INSERT INTO dbo.currencies(code,name) values (?,?)",
                      row['CurrencyCode'], row['Name']) 
  sql_conn.commit()

cursor.close()
sql_conn.close()

Po kolei:

-ładujemy moduły

import pandas as pd
import pyodbc

-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

sql_conn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};\
                            SERVER=Dragon01;\
                            DATABASE=Adventureworks;\
                            Trusted_Connection=yes')

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

df = pd.read_sql(query, sql_conn)
print(df.head(3))

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

sql_conn = pyodbc.connect('DRIVER={ODBC Driver 13 for SQL Server};\
                            SERVER=Dragon01;\
                            DATABASE=tempdb;\
                            Trusted_Connection=yes')

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:

cursor.execute("IF OBJECT_ID('dbo.currencies') IS NOT NULL  DROP TABLE dbo.currencies")
sql_conn.commit()

a teraz tworzę tą tabelę na nowo:

cursor.execute("CREATE TABLE dbo.currencies(\
                             code VARCHAR(3),\
                             name VARCHAR(30))")
sql_conn.commit()

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

for index,row in df.iterrows():
  cursor.execute("INSERT INTO dbo.currencies(code,name) values (?,?)",
                      row['CurrencyCode'], row['Name']) 
  sql_conn.commit()

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.