CODEWITHSUNDEEP
pythonsqldatabasedatepyodbc
eclairs
eclairs

Reputation: 1695

Python Querying on database with where clause

I am new to Python and want to understand the syntax for connecting to MS sql database. Have successfully created the connection but want to select a conditional data. Please suggest how can below be done in python:

import pyodbc as odbc
cursor.execute("SELECT Col1,Col2 FROM Data where date between sysdate and (sysdate-1) ;")

I have already assigned Start and end as :

End = dt.datetime.today()
Start = End - dt.timedelta(days=1)

How can this be incorporated in the query above?? Also, it would be really helpful if you could point me to some documentation where other such related things might be mentioned.

Upvotes: 0

Views: 4298

Answers (3)

Sathiyam Medicals
Sathiyam Medicals

Reputation: 1

Start = StringVar()
End = StringVar()

End = dt.datetime.today()
Start = End - dt.timedelta(days=1)

sql = "SELECT [Col1],[Col2] FROM [Data] WHERE [date] BETWEEN ? AND ?"
cursor.execute(sql, Start, End)
rows = cursor.fetchall()

Upvotes: 0

Alexis.Rolland
Alexis.Rolland

Reputation: 6363

I believe Gord's answer is the correct one but in case this is useful, you can also prepare dynamically you SQL statement within your Python code and provide the SQL statement already updated with the parameters to your cursor.

This should solve your error:

Error: ('HYC00', '[HYC00] [Microsoft][ODBC SQL Server Driver]Optional feature not implemented (0) (SQLBindParameter)')

end = dt.datetime.today()
start = End - dt.timedelta(days=1)
sql = "SELECT [Col1],[Col2] FROM [Data] WHERE [date] BETWEEN '{}' AND '{}'".format(start, end)
cursor.execute(sql)
rows = cursor.fetchall()

Upvotes: 0

Gord Thompson
Gord Thompson

Reputation: 123829

As explained in the pyodbc documentation for .execute, you can use ? as parameter placeholders in your SQL command text and pass the parameter values to .execute like so:

End = dt.datetime.today()
Start = End - dt.timedelta(days=1)
sql = "SELECT [Col1],[Col2] FROM [Data] WHERE [date] BETWEEN ? AND ?"
cursor.execute(sql, Start, End)
rows = cursor.fetchall()

Upvotes: 2

Related Questions