Python - writing to SQL server database using sqlalchemy from a pandas dataframe

Question

I have a pandas dataframe of approx 300,000 rows (20mb), and want to write to a SQL server database.

I have the following code but it is very very slow to execute. Wondering if there is a better way?

import pandas
import sqlalchemy

engine = sqlalchemy.create_engine('mssql+pyodbc://rea-eqx-dwpb/BIWorkArea? 
driver=SQL+Server')

df.to_sql(name='LeadGen Imps&Clicks', con=engine, schema='BIWorkArea', 
if_exists='replace', index=False)

stackoverflow.com/questions/33816918/… : I would have closed as duplicate but I didn't find linked answer right away. — Mitch Wheat
– Mitch Wheat, Commented Jun 20, 2018 at 4:35
Possible duplicate of Write Large Pandas DataFrames to SQL Server database @MitchWheat Got it for you. — jpmc26
– jpmc26, Commented Jun 20, 2018 at 4:51
Possible duplicate of Speeding up pandas.DataFrame.to_sql with fast_executemany of pyODBC — Ilja Everilä
– Ilja Everilä, Commented Jun 20, 2018 at 7:25
pyodbc used to have issues with large executemany() batches, which to_sql() uses under the hood. The fast_executemany flag should solve that to some degree (>100x speedups). — Ilja Everilä
– Ilja Everilä, Commented Jun 20, 2018 at 7:29

BENY · Accepted Answer · 2018-06-20 04:45:50Z

2

If you want to speed up you process with writing into the sql database , you can per-setting the dtypes of the table in your database by the data type of your pandas DataFrame

from sqlalchemy import types, create_engine
d={}
for k,v in zip(df.dtypes.index,df.dtypes):
    if v=='object':
       d[k]=types.VARCHAR(df[k].str.len().max())
    elif v=='float64':
       d[k]=types.FLOAT(126)
    elif v=='int64':
       d[k] = types.INTEGER()

Then

df.to_sql(name='LeadGen Imps&Clicks', con=engine, schema='BIWorkArea', if_exists='replace', index=False,dtype=d)

answered Jun 20, 2018 at 4:45

BENY

324k22 gold badges176 silver badges250 bronze badges

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

Collectives™ on Stack Overflow

Python - writing to SQL server database using sqlalchemy from a pandas dataframe

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related