python - 将日期插入 Python MySQLdb

Question

我正在尝试使用 Python 及其 MySQLdb 模块将当前日期插入 MySQL。我可以成功插入数据，如下所示:

insert = "INSERT INTO table(utdate) VALUES('2015-12-31')"

但是，我不想对日期进行硬编码，而宁愿使用变量或函数，例如:

today = time.strftime('%Y-%m-%d')

我已尝试了以下所有查询，但没有成功。成功输入数据库应显示为 datetime.date(2016, 01, 01)。每个查询下面是错误消息或数据库中的结果条目。

insert = "INSERT INTO table(utdate) VALUES(today)"
_mysql_exceptions.OperationalError: (1054, "Unknown column 'today' in 'field list'")

insert = "INSERT INTO table(utdate) VALUES('today')"
(None)

insert = "INSERT INTO table(utdate) VALUES('%s')" % (today)
(None)

insert = "INSERT INTO table(utdate) VALUES(%s)" % (today)
(None)

我的预感是这个问题必须对 Today 变量做一些事情，因为它是一个字符串，我必须使用引号来插入它。您有什么想法和建议？

提前致谢。

Answer 1

使用准备好的语句，数据库 API 将为您处理类型映射。来自 http://mysql-python.sourceforge.net/MySQLdb.html#some-examples 的文档

import MySQLdb
db=MySQLdb.connect(passwd="moonpie",db="thangs")

To perform a query, you first need a cursor, and then you can execute queries on it:

c=db.cursor()
max_price=5
c.execute("""SELECT spam, eggs, sausage FROM breakfast
          WHERE price < %s""", (max_price,))

In this example, max_price=5 Why, then, use %s in the string? Because MySQLdb will convert it to a SQL literal value, which is the string '5'. When it's finished, the query will actually say, "...WHERE price < 5".

Why the tuple? Because the DB API requires you to pass in any parameters as a sequence. Due to the design of the parser, (max_price) is interpreted as using algebraic grouping and simply as max_price and not a tuple. Adding a comma, i.e. (max_price,) forces it to make a tuple.