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

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 2

if self.ids is an array, you will have a problem doing the conversion within the execute statement. Instead, you should do it as a string operation before calling the execute statement.

Try this:

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

Answer 3

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.