Passing parameters from Python to SQL Server

Question

I have code like this:

conn = pyodbc.connect(<Connection Details>)
c = conn.cursor()
employee_id=(100,101)
query = "select * from employees where employeeid in ?"
c.execute(query,employee_id)

I am getting this error:

'The SQL contains 1 parameter markers, but 2 parameters were supplied', 'HY000'

Is there any way to pass this parameter? I don't want to create a dynamic array by concatenation.

Is there any way to name the parameter marker inside the query in case of several where conditions?

• M Z: each ? correlates to 1 parameter. You've passed 2 parameters. What do you expect to occur? If you want to do more than 1 query you should run something like executemany, or run executre more than 1 time
• Som-1: Check the accepted answer from here: stackoverflow.com/questions/4574609/…
• M Z: @Som-1 you're right, I misread the code which is why I deleted my comment
• Aditya: @MZ.there may be several employeeids. so we cant predetermine the number of '?'

Answer 1

If I remember correctly, the placeholder is %s and not ?.

Regardless, you can use the format method / string formatting to get the job done:

conn = pyodbc.connect(<Connection Details>)
c = conn.cursor()
employee_id=(100,101)
query = "select * from employees where employee_id in {}"
c.execute(query.format(employee_id))

• Som-1: It's not a secure way to do that. SQL injection is possible with this code.
• M Z: To add to what @Som-1 has said, prepared statements exist for a reason
• Aditya: @som-1 I understand it is not a secure way. I just wanted to know how it is done in case I want to do it. :)
• Som-1: @Aditya check my comment under the question - there is a link to similar question, where similar but more secure code provided: stackoverflow.com/questions/4574609/…