Using COPY instead of INSERT within python for postgresql

Question

I have a python script that adds many entries to a Postgres table using INSERT. I want to use COPY instead for speed. This answer gets halfway there, but does not instruct how to format lists, booleans, etc.

With INSERT, psycopg2 handles formatting for you:

foo = [0,1,2]
bar = '"Hello," she said'
cur.execute("insert into table (foo, bar) values (%s, %s)", (foo, bar))

However, this doesn't work with copy, as you have to use csv formatted data:

foo = [0,1,2]
bar = '"Hello," she said'
csv_string = "\t".join(str(foo), str(bar))
buf = io.StringIO()
buf.write(csv_string)
buf.seek(0)
cur.copy_from(buf, 'table')
# Does not work, as data is not formatted properly

Formatting with csv writer doesn't work either:

writer = csv.writer(buf)
csv_writer.writerow([foo,bar])
buf.seek(0)
cur.copy_from(buf, 'table')
# Fails on lists which get formatted as [], fails on NULL values

How do I format my data into Postgres-compatible CSV strings? I tried cur.mogrify, but it formats lists as ARRAY[0,1,2] instead of {0,1,2}, and copy_from expects the latter.

I suppose I could try to roll my own string formatter, but surely there is a better way?

Answer 1

The following example works:

foo = [0,1,2]
bar = '"Hello," she said'
csv_string = str(foo)+"\t"+ str(bar)
print(csv_string)
buf = io.StringIO()
buf.write(csv_string)
buf.seek(0)
cur.copy_from(buf, 'table')

The difference between your code and the code above is line #3 (csv_string=...).

Anyway, I'm recommending using copy_expert instead of copy_from. This is more flexible option.