5

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?

Community
  • 1
  • 1
Martijn de Munnik
  • 924
  • 12
  • 23

6 Answers6

5

You cannot provide the IN-list as one argument. You need to provide the exact number of place holders in the SQL IN clause, and then provide the array to the execute method:

ids = [1000032, 1000048]
sql = 'SELECT CompNo, CompName, CompType FROM Component WHERE DeptID IN (' \
       + (',?' * len(ids))[1:] + ')'
cursor.execute(sql, ids)
trincot
  • 317,000
  • 35
  • 244
  • 286
5

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,))
...
Bryan
  • 17,112
  • 7
  • 57
  • 80
2

with f-string:

ids = [1000032, 1000048]
sql = f'SELECT * FROM Component WHERE DeptID IN {*ids,}'
cursor.execute(sql)

result of sql:

'SELECT * FROM Component WHERE DeptID IN (1000032, 1000048)'
Jano
  • 311
  • 2
  • 10
1

The more simpler and clean way to do it

ids = [1000032, 1000048]
sql = 'SELECT CompNo, CompName, CompType FROM Component WHERE DeptID IN %(ids)s' % {"ids": tuple(ids)}
cursor.execute(sql)
abhishek
  • 301
  • 1
  • 5
  • 29
1

I really like Abhishek and Jano's answers as the cleanest here, but they each leave a trailing comma if ids only has one element (e.g. WHERE DeptID IN (1000032,)), which throws an error when passed to SQL.

Here's how I like to approach this type of set up, which I use often:

base_sql = 'SELECT CompNo, CompName, CompType FROM Component'
where_clause = "" if IDs is None else f"where DeptID in ({','.join([str(x) for x in IDs])})"
final_sql = base_sql + where_clause

Below are the final_sql strings we get for different IDs lists/params:

IDs = None
> final_sql
'SELECT CompNo, CompName, CompType FROM Component'

,

IDs = [1,2]
> final_sql
'SELECT CompNo, CompName, CompType FROM Componentwhere DeptID in (1,2)'

and, crucially vs some other answers here, no trailing comma for single-ID lists:

IDs = [1]
> final_sql
'SELECT CompNo, CompName, CompType FROM Componentwhere DeptID in (1)'
Max Power
  • 8,265
  • 13
  • 50
  • 91
0

you should cast the array of int to array of strings:

",".join(map(str,ids))
Mostafa Wattad
  • 61
  • 5