CODEWITHSUNDEEP
pythonsqlpandaspyodbc
doppelherz7
doppelherz7

Reputation: 11

Inserting huge pandas dataframe into SQL Server table

I am looking for a way to insert a big set of data into a SQL Server table in Python. The problem is that my dataframe in Python has over 200 columns, currently I am using this code:

import pyodbc
import pandas as pd
server = 'yourservername' 
database = 'AdventureWorks' 
username = 'username' 
password = 'yourpassword'

cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER='+server+';DATABASE='+database+'UID='+username+';PWD='+ password)
cursor = cnxn.cursor()

for index, row in df.iterrows():
cursor.execute("INSERT INTO dbo.mytable (A,B,C)values(?,?,?)", row.A, row.B, row.C)
cnxn.commit()
cursor.close()

The problem is in INSERT INTO dbo.mytable (A, B, C) VALUES (?,?,?)", row.A, row.B, row.C as I need to insert a data with over 200 columns and specifying each of these columns is not really time efficient :(

I would appreciate any help!

Upvotes: 0

Views: 6090

Answers (2)

doppelherz7
doppelherz7

Reputation: 11

Ok, I finally found a way:

serverName = 'xxx'
dataBase = 'zzz'
conn_str = urllib.parse.quote_plus(r'DRIVER={SQL Server};SERVER=' + serverName + r';DATABASE=' + dataBase + r';TRUSTED_CONNECTION=yes')
conn = 'mssql+pyodbc:///?odbc_connect={}'.format(conn_str)
engine = sqlalchemy.create_engine(conn,poolclass=NullPool)
connection = engine.connect()
df.to_sql("TableName", engine, schema='SchemaName', if_exists='append', index= True, chunksize=200)
connection.close()

Upvotes: 0

Jackssn
Jackssn

Reputation: 1624

  1. Create connection in SqlAlchemy
  2. Use df.to_sql() with chunksize param. Link to doc

ps. in my cases connection not in sqlalchemy not working in to_sql - function

Upvotes: 1

Related Questions