Include variable in python script that invokes MS SQL Server

Question

I am attempting to include variable in python script that invokes MS SQL Server

import pyodbc

ip_addr = '10.10.10.10'

querystring = """SELECT USER_NAME
FROM sem6.sem_computer, [sem6].[V_SEM_COMPUTER], sem6.IDENTITY_MAP, sem6.SEM_CLIENT
WHERE [sem6].[V_SEM_COMPUTER].COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID
AND sem6.SEM_CLIENT.GROUP_ID = IDENTITY_MAP.ID
AND sem6.SEM_CLIENT.COMPUTER_ID = SEM_COMPUTER.COMPUTER_ID
AND [IP_ADDR1_TEXT] = %s
"""

params = (ip_addr)

con = pyodbc.connect('DRIVER={SQL Server};SERVER=10.10.10.100;DATABASE=database;UID=username;PWD=password')
cur = con.cursor()
cur.execute(querystring, params)
result = cur.fetchone()[0]
print result
con.commit()
con.close()

And it gives the following error

Traceback (most recent call last):
  File "database_test.py", line 17, in <module>
    cur.execute(querystring, params)
pyodbc.ProgrammingError: ('The SQL contains 0 parameter markers, but 1 parameter
s were supplied', 'HY000')

In your current code, params is not a tuple, it's a string equal to ip_addr. In order to make a one-item tuple, you need a trailing comma, as in params = (ip_addr,). — murgatroid99
– murgatroid99, Commented Jun 30, 2014 at 18:21
I didn't expect that was the cause of your problem. It was just an error I noticed. That's why it was a comment. — murgatroid99
– murgatroid99, Commented Jun 30, 2014 at 18:27

Matt · Accepted Answer · 2014-06-30 18:38:05Z

1

Use ? instead of %s in your original query.

answered Jun 30, 2014 at 18:38

Matt

3594 silver badges18 bronze badges

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

Collectives™ on Stack Overflow

Include variable in python script that invokes MS SQL Server

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related