Postgres / psycopg2 - Insert an array of strings

Question

I am using Postgres 9 and Python 2.7.2 along with psycopg2 and trying to insert an array of string values ​​with correctly escaped quotes. Example:

metadata = {"Name": "Guest", "Details": "['One', 'Two', 'Three']"} cur.execute("insert into meta values ('%s');" % metadata) 

which throws an exception:

 psycopg2.ProgrammingError: syntax error at or near "One" LINE 1: "Details": "['One... ^ 

I also tried using Postgres' E to run along with backslashes, but have not yet found the right combination. Ideas?

Answer 1

You must let psycopg bind the parameters to you: do not try to quote them.

Psycopg will automatically convert a list of strings in python to a postgres array. Check out http://initd.org/psycopg/docs/usage.html

Answer 2

 def lst2pgarr(alist): return '{' + ','.join(alist) + '}' pyarray = ['pippo', 'minni', 1, 2] conn = psycopg2.connection ( HERE PUT YOUR CONNECTION STRING ) c = conn.cursor() c.execute('select ... where pgarray_attr = %r' % (lst2pgarr(pyarray)) c.execute('insert into tab(pgarray_attr) values (%r)' % (lst2pgarr(pyarray)) 

Answer 3

If you are going to dump all the metadata as a row into a table, you can simply do:

 cur.execute("insert into meta values (%s);", (str(metadata),)) 

Answer 4

If you want to insert an array into the postgreSQL database through SQL, follow these steps:

 INSERT INTO tablename VALUES ('{value1,value2,value3}'); 

ATTENTION: You need single quotes to surround braces! So, you pass the string / Varchar special grammar "array" to DB

If I inject my code into the python parser, I will get something like this:

 '{'Name': 'Guest', 'Details': "['One', 'Two', 'Three']"}' 

But PostgreSQL expects something like this:

 '{"Name","Guest","Details",{"One","Two","Three"}}' 

Check out the array guide: http://www.postgresql.org/docs/9.0/static/arrays.html

So, either you format the String according to PostgreSQL "array-grammar" by writing a helper function, or you use a library that does this for you.