在Python MySQLdb中插入日期

Question

我试图使用Python及其MySQLdb模块将当前日期插入MySQL。我可以这样成功地插入数据：

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

但是，我不想硬编码日期，而是使用变量或函数，如：

^{pr2}$

我尝试了以下所有的查询，但没有成功。成功进入数据库应显示为日期时间.日期（2016年1月1日）。在每个查询的下面是错误消息或生成的数据库条目。在

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

使用准备好的语句，dbapi将为您处理类型映射。来自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:

^{2}$

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.