How to pass a date variable to an SQL query in Python

Question

Python3.5 Sql Server 2012 Standard

package is pypyodbc

This Code Works

myConnection = pypyodbc.connect('Driver={SQL Server};'
                                'Server=myserver;'
                                'Database=mydatabase;'
                                'TrustedConnection=yes')
myCursor = myConnection.cursor()
sqlstr = ("Select * From DB.Table1 Where DB.Table1.Date <= '7/21/2016'")
myCursor.execute(sqlstr)
results = myCursor.fetchall()

However, Date must be a variable passed in by users. I made several mods to sqlstr but continue to error on myCursor.execute: "TypeError: bytes or integer address expected instead of tuple instance"

sqlstr = ("Select * From DB.Table1 Where DB.Table1.Date <= %s", '7/21/2016')

Error

sqlstr = ("Select * From DB.Table1 Where DB.Table1.Date <= '%s'", '7/21/2016')

Error

sqlstr = ("Select * From DB.Table1 Where DB.Table1.Date <= ?", "'7/21/2016'")

Error

var1 = "'7/21/2016'"
sqlstr = ("Select * From DB.Table1 Where DB.Table1.Date <= %s", var1)

and several more. However, I am sure there is one correct way...

Thanks for any help!

Gord Thompson · Accepted Answer · 2016-07-26 13:08:21Z

2

I am sure there is one correct way

Yes, it's a parameterized query:

date_var = datetime(2016, 7, 21)
sql = """\
SELECT [ID], [LastName], [DOB] FROM [Clients] WHERE [DOB]<?
"""
params = [date_var]  # list of parameter values
crsr.execute(sql, params)
for row in crsr.fetchall():
    print(row)

answered Jul 26, 2016 at 13:08

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

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

1 Comment

pbean Over a year ago

Thank you, that did the trick. For anyone curious about multiple parameters, simply separate with comma [param1, param2, param3]. Also, I did not have to edit the entire query to be parameterized, only the variables needed to be wrapped in brackets.

Collectives™ on Stack Overflow

How to pass a date variable to an SQL query in Python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest