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?

@ClodoaldoNeto: the whole point is that I want to use COPY for speed improvement. Using insert and execute_values is slow. — Agargara
– Agargara, Commented Sep 21, 2017 at 8:12
The whole point of execute_values is that it is much faster then insert. — Clodoaldo Neto
– Clodoaldo Neto, Commented Sep 21, 2017 at 8:17
If using csv, you have to use copy_expert() so that you can pass the required format argument(s). Search a bit, lots of examples. — Ilja Everilä
– Ilja Everilä, Commented Sep 21, 2017 at 8:21

Nir Shapira · Accepted Answer · 2019-08-13 13:06:45Z

2

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.

answered Aug 13, 2019 at 13:06

Nir Shapira

493 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Using COPY instead of INSERT within python for postgresql

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related