Use psycopg2 copy_from when dialect is postgresql

[edurand]

Hello,

I'm finding petl extremely useful and well design. I was wondering if it was possible to use postgres' copy abilities when moving a file to a postgres database. As you know, copy speeds things up enormously for such transfers.

Thanks!

[alimanfoo]

Hi there, glad you are finding petl useful. There is nothing presently in petl that allows to leverage postgres' copy functionality. If you want to use it, one way would be to use petl to build the data and write to a tab-separated file (i.e., petl.totsv()), then use the psycopg2 API directly to execute a copy_from() statement. Did you have some other idea about how this could be integrated with petl?

On Friday, September 1, 2017, edurand notifications@github.com wrote:

Hello,

I'm finding petl extremely useful and well design. I was wondering if it was possible to use postgres' copy abilities when moving a file to a postgres database. As you know, copy speeds things up enormously for such transfers.

Thanks!

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/alimanfoo/petl/issues/429, or mute the thread https://github.com/notifications/unsubscribe-auth/AAq8Qq7Skf5L3fLAkT9KBCU7_MtzLDQEks5sd8_qgaJpZM4PJ7JB .

-- Alistair Miles Head of Epidemiological Informatics Centre for Genomics and Global Health http://cggh.org Big Data Institute Building Old Road Campus Roosevelt Drive Oxford OX3 7LF United Kingdom Phone: +44 (0)1865 743596 Email: alimanfoo@googlemail.com Web: http://a http://purl.org/net/alimanlimanfoo.github.io/ Twitter: https://twitter.com/alimanfoo

[edurand]

Hi, In db.py you define SQL_INSERT_QUERY and SQL_TRUNCATE_QUERY. Couldn't one define a COPY_QUERY there, in the form "COPY {tablename} FROM STDIN", and use that in _todb_dbapi_cursor when dialect is postgres? Then one could use psycopg2 copy_expert function to issue the query and fill the table. What do you think?

[wonb168]

I used petl to export csv , and then use copy_expert to loading csv data to greenplum(same as postgre), but can petl do todb using copy_expert itself? thank you.

[juarezr]

Hi @wonb168,

• Currently from/todb() use the abstraction provided by DBAPI and the driver.
• It should be doable to specialize for bulk insert capabilities either on SQLAlchemy or the SGDB Driver
• Patches welcome! :)