Search code examples
pythonsqlpyodbcsqlanywhere

Python list of ints in prepared sql statement

My question is somewhat the same as Python list of String to SQL IN parameter but I have a list of integers. The python code I use is:

ids = [1000032, 1000048]
sql = 'SELECT CompNo, CompName, CompType FROM Component WHERE DeptID IN (?)'    
cursor.execute(sql, [','.join(ids)])

And the Sybase database engine responds with:

pyodbc.Error: ('07006', "[07006] [Sybase][ODBC Driver][SQL Anywhere]Cannot convert '1000032','1000048' to a numeric (-157) (SQLExecDirectW)")

What is the correct way to do this?

Solution

  • IMO a more readable way to build a dynamic query string with placeholders using str.format

    ids = [1000032, 1000048]
sql = 'SELECT CompNo, CompName, CompType FROM Component WHERE DeptID IN ({0})' 
sql = sql.format(','.join('?' * len(ids)))
cursor.execute(sql, (ids,))
...