1

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)
Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127
Ben Sharkey
  • 313
  • 3
  • 15
  • https://stackoverflow.com/questions/33816918/write-large-pandas-dataframes-to-sql-server-database : I would have closed as duplicate but I didn't find linked answer right away. – Mitch Wheat Jun 20 '18 at 04:35
  • Possible duplicate of [Write Large Pandas DataFrames to SQL Server database](https://stackoverflow.com/questions/33816918/write-large-pandas-dataframes-to-sql-server-database) @MitchWheat Got it for you. – jpmc26 Jun 20 '18 at 04:51
  • Possible duplicate of [Speeding up pandas.DataFrame.to\_sql with fast\_executemany of pyODBC](https://stackoverflow.com/questions/48006551/speeding-up-pandas-dataframe-to-sql-with-fast-executemany-of-pyodbc) – Ilja Everilä Jun 20 '18 at 07: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ä Jun 20 '18 at 07:29

1 Answers1

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)
BENY
  • 317,841
  • 20
  • 164
  • 234