Postgres/psycopg2 - Inserting array of strings


Question

I'm using Postgres 9 and Python 2.7.2 along with psycopg2 and am trying to insert an array of string values with properly escaped quotation marks. Sample:

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

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

which throws the exception:

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

I've also tried using Postgres' E to escape along with backslashes, but haven't found the correct combination yet. Ideas?

1
11
7/28/2011 1:22:31 AM

Accepted Answer

You have to let psycopg do parameters binding for you: don't try to quote them yourself.

Psycopg automatically converts a python list of strings into a postgres array. Check http://initd.org/psycopg/docs/usage.html

20
7/28/2011 11:51:47 AM

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))

Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Icon