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?

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 — M Z
– M Z, Commented Jan 11, 2021 at 6:47
Check the accepted answer from here: stackoverflow.com/questions/4574609/… — Som-1
– Som-1, Commented Jan 11, 2021 at 6:57
@Som-1 you're right, I misread the code which is why I deleted my comment — M Z
– M Z, Commented Jan 11, 2021 at 7:02
@MZ.there may be several employeeids. so we cant predetermine the number of '?' — Aditya
– Aditya, Commented Jan 11, 2021 at 7:54

OneCoolBoi · Accepted Answer · 2021-01-11 06:57:07Z

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

edited Jan 11, 2021 at 6:57

answered Jan 11, 2021 at 6:54

OneCoolBoi

965 bronze badges

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

4 Comments

Som-1 Over a year ago

It's not a secure way to do that. SQL injection is possible with this code.

M Z Over a year ago

To add to what @Som-1 has said, prepared statements exist for a reason

Aditya Over a year ago

@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 Over a year ago

@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/…

Collectives™ on Stack Overflow

Passing parameters from Python to SQL Server

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related