to_sql is too slow

Code Sample,

df_name.to_sql('table_name',
                          schema = 'public',
                          con = engine,
                          index = False,
                          if_exists = 'replace')

Problem description

Im writing a 500,000 row dataframe to a postgres AWS database and it takes a very, very long time to push the data through.

It is a fairly large SQL server and my internet connection is excellent so I’ve ruled those out as contributing to the problem.

In comparison, csv2sql or using cat and piping into psql on the command line is much quicker.

Issue Analytics

State:
Created 7 years ago
Comments:25 (3 by maintainers)

Top GitHub Comments

26reactions

llautertcommented, Nov 29, 2018

Add this code below engine = create_engine(connection_string):

from sqlalchemy import event

@event.listens_for(e, 'before_cursor_execute')
def receive_before_cursor_execute(conn, cursor, statement, params, context, executemany):
    if executemany:
        cursor.fast_executemany = True
        cursor.commit()

In my code, to_sql function was taking 7 min to execute, and now it takes only 5 seconds 😉

25reactions

tim-sauchukcommented, Dec 12, 2018

I’ve attempted to run this fix, but run into an error message:

AttributeError: 'psycopg2.extensions.cursor' object has no attribute 'fast_executemany'

Anyone know what’s going on?

Top Results From Across the Web

python pandas to_sql with sqlalchemy : how to speed up ...

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...

Troubleshoot slow SQL Server performance caused by I/O ...

Applies to: SQL Server. This article provides guidance on what I/O issues cause slow SQL Server performance and how to troubleshoot the ...

Very very slow query...how to speed up - Laracasts

Some general steps that I use to troubleshoot database performance issues (on MySQL):. In artisan tinker chain your select statement into ->toSql() like...

Why is My Database Application so Slow? - Simple Talk

Application problems: slow processing times. Whenever the client sends a request to SQL Server, to retrieve the required data set, the total ...

Users time out (or it's very slow) authenticating to SQL Server ...

This wait accumulates while SQL Server is waiting for an Active Directory query to complete. It occurs while listing group members like in ......