Inserting huge pandas dataframe into SQL Server table

Question

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!

Jackssn · Accepted Answer · 2021-08-27 16:20:46Z

1

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

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

answered Aug 27, 2021 at 16:20

Jackssn

1,6841 gold badge18 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gord Thompson Over a year ago

Yes, .to_sql() requires a SQLAlchemy Connectable (Engine or Connection) for all databases except SQLite.

doppelherz7 · Accepted Answer · 2021-08-27 16:54:16Z

0

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()

answered Aug 27, 2021 at 16:54

doppelherz7

111 silver badge3 bronze badges

Collectives™ on Stack Overflow

Inserting huge pandas dataframe into SQL Server table

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related