Python - 将日期时间参数传递给 SQL 命令 [英] Python - Passing datetime parameters into a SQL Command
问题描述
我正在尝试用 Python 做这样的事情,
I am trying to do something like this in Python,
SQLCommand = ("Delete From %s where [Date] >= %s and [Date] <= %s", (calendar_table_name, required_starting_date, required_ending_date))
cursor.execute(SQLCommand)
calendar_table_name
是一个 string
变量
required_starting_date
是一个 datetime
变量
required_ending_date
是一个 datetime
变量
尝试这个会给我一个错误:
Trying this gives me an error:
要执行的第一个参数必须是字符串或 Unicode 查询.
The first argument to execute must be a string or unicode query.
试过这个,它给了我同样的错误:
Tried this and it gives me the same error:
SQLCommand = ("Delete From " + calendar_table_name + " where [Date] >= %s and [Date] <= %s", ( required_starting_date, required_ending_date))
cursor.execute(SQLCommand)
type(required_ending_date)
Out[103]: pandas._libs.tslibs.timestamps.Timestamp
type(required_starting_date)
Out[103]: pandas._libs.tslibs.timestamps.Timestamp
这对我来说适用于 SSMS,
This works in SSMS for me,
delete from [table_test] where [Date] >= '2007-01-01' and [Date] <= '2021-01-01';
更新:- 这是我正在尝试的代码
Delete_SQLCommand = f"Delete FROM [{calendar_table_name}] WHERE [Date]>=? And [Date]<=?"
params = (required_starting_date, required_ending_date)
required_starting_date &required_ending_date 是TimeStamp"格式
required_starting_date & required_ending_date are of "TimeStamp" formats
calendar_tbl_connection = pyodbc.connect(driver=driver, server=required_server, database=database_name,
trusted_connection='yes')
calendar_tbl_cursor = calendar_tbl_connection.cursor()
calendar_tbl_cursor.execute(Delete_SQLCommand,params)
calendar_tbl_connection.commit
calendar_tbl_connection.close()
推荐答案
pyodbc 可以将 pandas 的 Timestamp
值作为适当的参数化查询的输入处理:
pyodbc has no problem dealing with pandas' Timestamp
values as inputs to a proper parameterized query:
# test data
calendar_table_name = "#calendar_table"
crsr.execute(f"CREATE TABLE [{calendar_table_name}] ([Date] date)")
crsr.execute(f"INSERT INTO [{calendar_table_name}] VALUES ('2019-08-22'),('2019-08-24')")
df = pd.DataFrame(
[(datetime(2019, 8, 23, 0, 0), datetime(2019, 8, 25, 0, 0))],
columns=['required_starting_date', 'required_ending_date'])
required_starting_date = df.iloc[0][0]
required_ending_date = df.iloc[0][1]
print(type(required_starting_date)) # <class 'pandas._libs.tslibs.timestamps.Timestamp'>
# test
sql = f"DELETE FROM [{calendar_table_name}] WHERE [Date]>=? AND [Date]<=?"
params = (required_starting_date, required_ending_date)
crsr.execute(sql, params)
cnxn.commit()
#verify
rows = crsr.execute(f"SELECT * FROM [{calendar_table_name}]").fetchall()
print(rows) # [(datetime.date(2019, 8, 22), )]
这篇关于Python - 将日期时间参数传递给 SQL 命令的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!