Cannot declare date variable in correct format for sql query in Python

Question

Executing an sql query using Python and feeding in a date variable for the select. But I am getting the error 'datetime2 is incompatible with int (206)' I have mucked around with the variable but can't seem to get it into the correct format. Code below:

import pyodbc
import pandas as pd
import datetime

dt = datetime.date(2021, 10, 1)
print (dt)

server = 'testServer'
database = 'testDatabase'
username = 'test'
password = '{test}'   
driver= '{ODBC Driver 13 for SQL Server}'

conn = pyodbc.connect('DRIVER='+driver+';SERVER=tcp:'+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password)

conn.autocommit = True

select_sql = """Select * From test Where Inception <= {x}""".format(x=dt)

sql_query = pd.read_sql_query(select_sql, conn)

That is because you arent quoting the date. Look at the output of select_sql and run that query in sql server to see why the error is. Also, you should use parameterized statement instead of string concatenation. — shahkalpesh
– shahkalpesh, Commented Oct 15, 2021 at 16:30
Why not use parameters, rather than injection? Injection is dangerous. — Thom A
– Thom A ♦, Commented Oct 15, 2021 at 16:44
Also, if you do parametrise your parameters (seems so silly when you say that doesn't it?) this problem doesn't even exist. — Thom A
– Thom A ♦, Commented Oct 15, 2021 at 16:51
This link will help you write parameterized query to get the records from DB. Why you are using pandas to query is beyond me. I dont know if pandas support reading data from sql server using pyodbc. — shahkalpesh
– shahkalpesh, Commented Oct 15, 2021 at 17:10
@CGarden: My bad. I think you may use pandas to read data into a dataframe. I hope my comment helps you in getting the data out of sql server using parameterized statement. — shahkalpesh
– shahkalpesh, Commented Oct 15, 2021 at 17:36

CGarden · Accepted Answer · 2021-10-16 13:57:16Z

3

Pointed significantly in the right direction by @shahkalpesh (thank you), using this parametrization solved the issue with the date. """Select * From test Where Inception <= ?""" sql_query = pd.read_sql_query(select_sql, conn, params=[dt])

answered Oct 16, 2021 at 13:57

CGarden

3494 silver badges22 bronze badges

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

Collectives™ on Stack Overflow

Cannot declare date variable in correct format for sql query in Python

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