IN clause with integers in sqlalchemy/psycopg2

Question

Just a beginner with the python/postgres combo so forgive me if this is trivial. I'm executing a raw SQL query with sqlalchemy along the lines of:

SELECT * FROM table WHERE pk_table_id IN ()

For the example below I tried self.ids as a tuple containing string or integers as well as an array containing string or integers. Either way it didn't work.

When I use this line:

my_connection.execute('SELECT * FROM public.table WHERE pk_table_id IN (%s)', self.ids)

I get the error:

TypeError: not all arguments converted during string formatting

Any suggestions?

Answer 1

I ended up ditching SqlAlchemy for straight psycopg2, so I don't know if it applies 100%. What I found out was that psycopg2 will correctly compile the IN clause if you pass it a tuple rather than an array/list. I passed a tuple of integers and it worked just fine.

Answer 2

You can use the cur.mogrify method:

cur = my_connection.cursor()
cur.execute(cur.mogrify('SELECT * FROM public.table WHERE pk_table_id IN %s', (tuple(self.ids),)))

Answer 3

The %s placeholder in execute expects a scalar, not a tuple. You either need to replace it with ','.join(('%s',) * len(mytuple)), or use string substitution instead!

Answer 4

if your ids are in a list you can use list adaptation:

my_connection.execute('SELECT * FROM public.table WHERE pk_table_id = ANY(%s)', (self.ids,))

Taken from lists-adaptation