Python-在MySQL陈述式中使用.format()字串 [英] Python - Using .format() string in a MySQL statement

问题描述

我正在尝试使用今天的数据以及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)

这篇关于Python-在MySQL陈述式中使用.format()字串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！