I am trying to create a prepared statement with the following pattern:
SELECT * FROM table WHERE key IN (value1, value2, value3, ..., valueN)
With pyodbc I tried the following syntax:
values = set(...)
cursor.execute('SELECT * FROM table WHERE key IN (?)', values)
Error:
The SQL contains <N> paramter markers, but 1 parameters were supplied
Do I have to generate a (?, ?, <...>, ?) for N ? myself or can I somehow insert a list/set into a single field of a prepared statement.
You are trying to bind a list/set/collection to a ? placeholder which can only receive a single scalar value. One option here is to build a dynamic WHERE key IN (?,?,?) clause based on the length of the list:
values = [value1, value2, value3, ...]
query = '(?' + ', ?' * (len(values) - 1) + ')'
sql = 'SELECT * FROM table WHERE key IN ' + query
cursor.execute(sql)
The key point here is that if the number of ? placeholders match the size of the list you are trying to bind, then it works.