How to fix Syntax error at or near ")" when using psycopg2?

Question

I am working on a script that was made by someone else two years ago. In the meantime, the database used has changed from mySQLdb to psycopg2 which caused some errors. One of the error is a syntax error.

When trying to execute a query is get the error saying that there is a syntax error at or near ")". The code is as follows:

list = []
format_strings = ",".join([%s]) + len(list)
db.execute("SELECT * FROM table WHERE column IN (%s,%s)", (format_strings, tuple(list)))
input = db.fetchall()

The error says the syntaxerror is in the line: table WHERE column IN (' ', ())

Initially the code was as follows:

db.execute("SELECT & FROM table WHERE column IN (%s)" % format_strings, tuple(list))

This also gave the syntax error, and after searching online I changed it into the first code I mentioned.

I have tried many different trial and errors but nothing works. Does anyone know how to fix this?

Answer 1

Psycopg2 will automatically convert a tuple to a structure suitable for an IN clause, so you can use a bare %s for the IN and pass a single element list or tuple containing the values within a tuple to generate the desired SQL statement.

>>> values = [1, 2, 3]
>>> # Output the statement that will be sent to the database
>>> db.mogrify("""select * from t where col in %s""" , [tuple(values)])
b'select * from t where col in (1, 2, 3)'