Python list of ints in prepared sql statement

Question

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?

Bryan · Accepted Answer · 2016-01-28 16:35:10Z

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,))
...

edited Jan 28, 2016 at 16:35

answered Jan 28, 2016 at 15:26

Bryan

17.7k7 gold badges59 silver badges81 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jing He Over a year ago

what is {0} for?

trincot · Accepted Answer · 2016-01-28 12:55:50Z

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)

answered Jan 28, 2016 at 12:55

trincot

357k38 gold badges282 silver badges338 bronze badges

Comments

Jano · Accepted Answer · 2022-06-23 07:00:37Z

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)'

answered Jun 23, 2022 at 7:00

Jano

3583 silver badges13 bronze badges

1 Comment

Roger Marlow Over a year ago

Doesn't work if the array only has one element. You get an extraneous comma in the where clause.

Abhishek Jain · Accepted Answer · 2021-10-06 07:49:41Z

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)

answered Oct 6, 2021 at 7:49

Abhishek Jain

3151 gold badge6 silver badges35 bronze badges

Comments

Max Power · Accepted Answer · 2022-06-28 15:57:04Z

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)'

Mostafa Wattad · Accepted Answer · 2016-01-28 12:55:42Z

0

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

",".join(map(str,ids))

answered Jan 28, 2016 at 12:55

Mostafa Wattad

615 bronze badges

Collectives™ on Stack Overflow

Python list of ints in prepared sql statement

6 Answers 6

1 Comment

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related