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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
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
|
1 2 |
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
|
1 2 3 4 |
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
|
1 2 |
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
|
1 2 3 4 |
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:
|
1 2 |
cursor.execute("IF OBJECT_ID('dbo.currencies') IS NOT NULL DROP TABLE dbo.currencies") sql_conn.commit() |
a teraz tworzę tą tabelę na nowo:
|
1 2 3 4 |
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:
|
1 2 3 4 |
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.
Komentarze:
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’)