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?

What error do you get with `"[\'One\', \'Two\', \'Three\']"`? — agf, Jul 28 '11 at 01:28

piro · Accepted Answer · 2022-11-04T21:34:15.340

35

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 https://www.psycopg.org/docs/usage.html#lists-adaptation

edited Nov 04 '22 at 21:34

answered Jul 28 '11 at 11:51

piro

13,378
5
34
38

2

Ah I missed the slight format change. Using cur.execute("insert into meta values %s", metadata) worked for me. Thank you very much. – Growth Mindset Jul 28 '11 at 16:25
1

Answer link is dead, future searchers can see "Link adaptation" section over here: https://www.psycopg.org/docs/usage.html. Basically you need to pass the Python list like so: `cur.execute("SELECT * FROM data WHERE id = ANY(%s);", (yourList,))` – English Rain Nov 03 '22 at 09:55
1

+1 to @EnglishRain's solution. Just linking the exact section of the docs: https://www.psycopg.org/docs/usage.html#adapt-list – Alexander Otavka Nov 03 '22 at 23:06
1

Thank you, link fixed – piro Nov 04 '22 at 21:35
@EnglishRain solution works for a list. – Marcos Santana Jul 06 '23 at 09:30

das_weezul · Answer 2 · 2011-07-28T12:03:57.847

When you want to insert an array into a postgreSQL DB via SQL you do it like this:

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

ATTENTION: You need the single quotes to surround the curly braces! So actually you're passing a String/Varchar of a special "array" grammar to the DB

If I enter your code into a python parser I get something like this:

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

But PostgreSQL expects something like this:

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

Check the manual on Arrays: http://www.postgresql.org/docs/9.0/static/arrays.html

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

LittleEaster · Answer 3 · 2015-06-04T16:01:11.580

1

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

edited Jun 04 '15 at 16:01

answered Mar 18 '15 at 15:22

LittleEaster

527
7
10

3

And what if pyarray contains a string with a comma, `'ciao,mondo'`? – piro Mar 03 '20 at 15:57
It would be a single object (out of topic). After 5 year my approaches has been changed. I guess that for instance you should declare if the comma-separated will be a 2nd dimension array or expanded, then a recursive split could be a fast-fix. Currently your suggestion is good: https://www.psycopg.org/docs/usage.html#lists-adaptation, now I like to use json datatype and, when I do not need transactions, I use postgRest.ogr – LittleEaster Mar 14 '20 at 14:48

score -4 · Answer 4 · answered Jul 28 '11 at 05:04

-4

If you are going to dump the whole metadata as a string into the table, you can just do:

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

answered Jul 28 '11 at 05:04

sayap

6,169
2
36
40

Postgres/psycopg2 - Inserting array of strings

4 Answers4

Linked