How to adjust postgresql+python for copy from?

database postgresql python sql sqlbulkcopy

Question

What function can I use to adapt values for suitable use in a COPY FROM statement? I tried this adapt function:

from psycopg2.extensions import adapt

However, it gives the wrong thing for datetimes (appends ::timestsamp to the string, postgres doesn't like it) and string (wraps them in single quotes, e.g. empty string is '', where it seems no quotes should be used).

1
1
11/3/2011 10:13:48 PM

Accepted Answer

You shouldn't just use adapt with copy_from.

copy_from expects a format different than SQL quoting.

For strings, I'd suggest to write your own copy_adapt function, which should escape tabs with \t, CR with \r and LF with \n.

Values for timestamp / date columns should be formatted (with strftime?) like the string which you see when you SELECT now() in Postgres.

Tuple elements should be saparated by tabs, and whole tuple finished with newline.

1
11/5/2011 12:20:32 PM


Related Questions





Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow