python pandas to_sql with sqlalchemy : how to speed up exporting to MS SQL?

by

I recently had the same problem and feel like to add an answer to this for others.
to_sql seems to send an INSERT query for every row which makes it really slow. But since 0.24.0 there is a method parameter in pandas.to_sql() where you can define your own insertion function or just use method='multi' to tell pandas to pass multiple rows in a single INSERT query, which makes it a lot faster.

Note that your Database may has a parameter limit. In that case you also have to define a chunksize.

So the solution should simply look like to this:

my_data_frame.to_sql(TableName, engine, chunksize=<yourParameterLimit>, method='multi')

If you do not know your database parameter limit, just try it without the chunksize parameter. It will run or give you an error telling you your limit.

More Related Contents:

  1. Get data from pandas into a SQL server with PYODBC
  2. pyodbc.connect() works, but not sqlalchemy.create_engine().connect()
  3. How to execute raw SQL in Flask-SQLAlchemy app
  4. Pandas read_sql with parameters
  5. How to create a large pandas dataframe from an sql query without running out of memory?
  6. SQLAlchemy ORM conversion to pandas DataFrame
  7. How do I get a raw, compiled SQL query from a SQLAlchemy expression?
  8. Writing to MySQL database with pandas using SQLAlchemy, to_sql
  9. sqlalchemy: how to join several tables by one query?
  10. PYODBC–Data source name not found and no default driver specified
  11. Bulk Insert A Pandas DataFrame Using SQLAlchemy
  12. Pandas left outer join multiple dataframes on multiple columns
  13. Python Pandas write to sql with NaN values
  14. Complex foreign key constraint in SQLAlchemy
  15. How to upsert pandas DataFrame to Microsoft SQL Server table?
  16. pyodbc insert into sql
  17. to_sql pyodbc count field incorrect or syntax error
  18. How to get rows which match a list of 3-tuples conditions with SQLAlchemy
  19. SQLAlchemy: how to filter date field?
  20. How to execute “left outer join” in SqlAlchemy
  21. Getting random row through SQLAlchemy
  22. Select NULL Values in SQLAlchemy
  23. How to see the real SQL query in Python cursor.execute using pyodbc and MS-Access
  24. SQLAlchemy Obtain Primary Key With Autoincrement Before Commit
  25. pandas to_sql all columns as nvarchar
  26. pyodbc the sql contains 0 parameter markers but 1 parameters were supplied’ ‘hy000’
  27. Remote connection to MS SQL – Error using pyodbc vs success using SQL Server Management Studio
  28. Connecting to SQL Server 2012 using sqlalchemy and pyodbc
  29. VALUES clause in SQLAlchemy
  30. flask sqlalchemy query with keyword as variable

Leave a Comment