Inserting Python arrays into Postgres queries with psycopg2 leads to Type Error

Question

I have a list called people2 like so:

['xloS4ooQOT',
 '3s4LyNyHs3',
 'NRL6zNePCT',
 '7hkLogfk8T',
 '5JcUkJ8FLO',
 'LZ6DMUfnEA',
 'CmBaomzMXC',
 'M5OPb0yf09',
 'CqG2XYGPxk']

I am trying to use it as the basis for a postgres query via the psycopg2 module:

query = "SELECT userid, measurementvalue FROM db WHERE userid IN (%s)"
cur.execute(query, people2[1:5])

That produces the following error:

---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
<ipython-input-30-5825c6e2c3fa> in <module>()
      1 query = "SELECT userid, measurementvalue, displaydate, datatype FROM parse_dataobject WHERE userid IN (%s)"
----> 2 cur.execute(query, people2[1:5])
      3 for r in rows[:5]:
      4     print(r)

TypeError: not all arguments converted during string formatting

I also tried removing the parentheses, but this leads to the same result:

query = "SELECT userid, measurementvalue, displaydate, datatype FROM parse_dataobject WHERE userid IN %s"

I am simply trying to follow the docs + previous posts (Python List to PostgreSQL Array), but I seem to be missing something. What is wrong with what I'm doing?

Answer 1

From psycopg2 doc on Lists adaptation, your code should look like this:

query = "SELECT userid, measurementvalue FROM db WHERE userid = any(%s)"
cur.execute(query, (people2[1:5],))

Answer 2

Python tuples are converted to sql lists in psycopg2:

cur.mogrify("SELECT * FROM table WHERE column IN %s;", ((1,2,3),))

would output

SELECT * FROM table WHERE column IN (1,2,3);

you need convert the list in tuple and after use this tuple, for example:

people2 = ['xloS4ooQOT', '3s4LyNyHs3', 'NRL6zNePCT', '7hkLogfk8T', '5JcUkJ8FLO', 'LZ6DMUfnEA', 'CmBaomzMXC', 'M5OPb0yf09', 'CqG2XYGPxk']

query = "SELECT userid, measurementvalue FROM db WHERE userid IN %s" cur.execute(query, tuple(people2[1:5]))