psycopg2: Writing JSON objects using copy_from. How to format the json string?

Question

I have a table in postgresql with a column of type JSON. I'm trying to append data to the table.

cursor.execute("""INSERT INTO my_table VALUES(%s);""",(json.dumps(myobject))

Has been working like a charm. But now I need to really increase the throughput. Here is the code which doesn't work:

import StringIO,psycopg2,json

buffer = StringIO.StringIO(json.dumps(myobject))
cursor.copy_from(buffer,'my_table')
connection.commit()

The json written to the buffer is not compatible with copy_from. For example, '\' characters need to be escaped so '\n' needs to be '\\n'.

How can I write a string to the buffer so that copy_from will put the correct json into my table?

Thanks

Answer 1

I found one solution which seems to work for now:

import StringIO,psycopg2,json

json_to_write = json.dumps(myobject).replace('\\','\\\\')
buffer = StringIO.StringIO(json_to_write)
cursor.copy_from(buffer,'my_table')
connection.commit()

I don't love this because how do I know there are not other issues? Maybe I should make a feature request to the psycopg2 guys?