psycopg2 - Inserting an integer array

Question

I'm using Postgres 9.2, Python 2.7.3, psycopg2 2.5.1.

I have a table, with one of the fields declared as 'some_field int[] NOT NULL' and I need to insert some data, so I'm doing something like this:

cursor.execute('INSERT INTO some_table (some_field) VALUES (%s)', ([1, 2, 3], ))

but unexpectedly getting an error 'DataError: missing "]" in array dimensions', because the result query becames

INSERT INTO some_table (some_field) VALUES ('[1, 2, 3]')

instead of

INSERT INTO some_table (some_field) VALUES (ARRAY[1, 2, 3])

or

INSERT INTO some_table (some_field) VALUES ('{1, 2, 3}')

Am I missing something or it is a psycopg2 error?

Answer 1

The first snippet of code is the correct one. To check the SQL generated by psycopg2 you can always use the mogrify() method:

>>> curs.mogrify('INSERT INTO some_table (some_field) VALUES (%s)', ([1, 2, 3], ))
'INSERT INTO some_table (some_field) VALUES (ARRAY[1, 2, 3])'

Then you can try the SQL using psql and look for errors. If you find that psycopg2 generates a query that can't be executed in psql, please report a bug.