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?

fog · Accepted Answer · 2013-11-06 15:24:34Z

9

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.

answered Nov 6, 2013 at 15:24

fog

3,4411 gold badge27 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

psycopg2 - Inserting an integer array

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related