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

Question

I have a table in postgresql with a JSON type column. I am trying to add data to a table.

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

Worked like a charm. But now I need to really increase the bandwidth. Here is the code that does not work:

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

json written to the buffer is not compatible with copy_from. For example, the characters "\" must be escaped, so "\ n" must be "\\ n".

How can I write a string to the buffer so copy_from puts the correct json in my table?

thanks

Answer 1

I found one solution that now works:

 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 like it because, as I know, there are no other problems? Maybe I should make a function request for psycopg2 guys?