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

[Uwe L. Korn]

Hello,

for Postgres there is actually an example in the documentation on how you
can get a much better INSERT performance by using the COPY statement:
https://pandas.pydata.org/pandas-docs/stable/user_guide/io.html#insertion-method

This should perform even better than using `multi`.

Cheers
Uwe

Am Do., 15. Okt. 2020 um 11:17 Uhr schrieb Shaozhong SHI <
shishaozhong at gmail.com>:

> Hi, Simon,
>
> Has anyone tested how much faster it can be, when method='multi' is
> applied?
>
> Regards,
>
> David
>
> On Wed, 14 Oct 2020 at 21:49, Simon Gibbons <simongibbons at gmail.com>
> wrote:
>
>> 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
>>>
>> _______________________________________________
> 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/20201015/8f9af6e9/attachment-0001.html>