[Pandas-dev] Which one is the fastest one for getting data into PostgreSQL

[Simon Gibbons]

Hi David,

This is likely slow as to_sql dy default produces one INSERT statement for
every row in the dataframe. This means that writing your dataframe to the
database will need many network round trips.

Try adding the argument

method='multi'

to your call, which will batch up the inserts and should be faster (see
https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.to_sql.html
)

Best,

Simon

On Wed, Oct 14, 2020 at 8:43 PM Shaozhong SHI <shishaozhong at gmail.com>
wrote:

> I am using the following code to get data into PostgreSQL.
>
> However, it appears to be very slow.
>
> from sqlalchemy import create_engine
> import psycopg2
> engine = create_engine('postgresql+psycopg2://username:password at host
> :5432/mydatabase')
> out.to_sql('table_name2', engine, if_exists='replace', index=None)
>
> Are there any fast way to do it?
>
> Looking forward to hearing from you.
>
> Regards,
>
> David
> _______________________________________________
> Pandas-dev mailing list
> Pandas-dev at python.org
> https://mail.python.org/mailman/listinfo/pandas-dev
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://mail.python.org/pipermail/pandas-dev/attachments/20201014/1ae29f9f/attachment.html>