如何在 MySQL 语句中使用 .format() 字符串? [英] How to use .format() string in a MySQL statement?
本文介绍了如何在 MySQL 语句中使用 .format() 字符串?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试使用今天的数据以及 7 天前的数据来格式化 MySQL 语句.我很确定日期格式正确,所以我不认为这是问题所在.
I'm trying to format a MySQL statement with today's data, as well as 7 days back. I'm pretty sure the date is in the correct format, so I don't think that's the issue.
错误报告为:
Warning: (1292, "Incorrect datetime value: '{} 16:00:00' for column 'run_start_date' at row 1")
result = self._query(query)
Traceback (most recent call last):
AttributeError: 'int' object has no attribute 'format'
例如
today = DT.date.today()
week_ago = today - DT.timedelta(days=7)
print(today.strftime('%Y-%m-%d'))
print(week_ago.strftime('%Y-%m-%d'))
cursor.execute(SELECT * FROM db WHERE run_start_date BETWEEN '{} 16:00:00' AND '{} 16:00:00'format(week_ago, today)
推荐答案
使用准备好的语句(安全方式):
qry = """
SELECT *
FROM db
WHERE run_start_date BETWEEN '%s 16:00:00' AND '%s 16:00:00'
""" #
today = DT.date.today()
week_ago = today - DT.timedelta(days=7)
today = str(today.strftime('%Y-%m-%d')) # Convert to string
week_ago = str(week_ago.strftime('%Y-%m-%d')) # Convert to string
cursor.execute(qry, [today, week_ago])
使用 .format() 会使您面临 sql 注入的风险(如果您将用户输入传递给 .format() 例如)
qry = """
SELECT *
FROM db
WHERE run_start_date BETWEEN '{today} 16:00:00' AND '{week_ago} 16:00:00'
""" # Use named placeholders, nicer to read, prevents you having to repeat variables multiple time when calling .format()
today = DT.date.today()
week_ago = today - DT.timedelta(days=7)
today = str(today.strftime('%Y-%m-%d')) # Convert to string
week_ago = str(week_ago.strftime('%Y-%m-%d')) # Convert to string
qry = qry.format(today=today, week_ago=week_ago)
cursor.execute(qry)
这篇关于如何在 MySQL 语句中使用 .format() 字符串?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文