错误的日期时间值:"2012-07-14 23:00:00" [英] Incorrect datetime value: ''2012-07-14 23:00:00''

问题描述

我在使用Python/MySQL的日期时间格式时遇到麻烦.

I'm having some trouble with the datetime format with Python/MySQL.

我使用以下脚本(由Python词典提供)计算日期时间:

I calculate the datetime using the following script (fed by a Python dictionary):

tempDate  = str(eachday.get("date").get("year")).zfill(4) + "-" + 
str(eachday.get("date").get("month")).zfill(2) + "-" + 
str(eachday.get("date").get("day")).zfill(2)   + " " + 
str(eachday.get("date").get("hour")).zfill(2)  + ":" + 
str(eachday.get("date").get("min")).zfill(2)   + ":" + 
str(eachday.get("date").get("sec")).zfill(2)

哪个产生的值看起来像2012-04-02 04:04:23.

Which yields a value that looks like 2012-04-02 04:04:23.

我可以毫无问题地插入MySQL.

I can insert into MySQL without any problems.

sql.execute("""INSERT INTO `db`.`table`(`id`, `fk_id`, `time`, `field1`, `field2`) VALUES (NULL, %s, %s, %s, %s);""", (fk_value, tempDate, value1, value2)) 
DB_CONN.commit()

但是当我尝试删除该日期时间的任何内容时，

But when I try to delete anything with that datetime,

sql.execute("""DELETE FROM `db`.`table` WHERE `time` = "%s";""", (tempDate))
DB_CONN.commit()

它返回有关日期时间值不正确的警告:

it returns a warning about an incorrect datetime value:

Warning: Incorrect datetime value: ''2012-07-17 23:00:00'' for column 'time' at row 1

如何在Python中按日期时间删除?尤其令人困惑的是，插入相同的变量(什么都没做)可以完美地工作.

How can I delete by datetime in Python? This is especially confusing since inserting the same variable (nothing changed) works perfectly.

推荐答案

查询字符串中不能包含"%s"中的引号； mysqldb模块为您添加了它们(与您在INSERT中所做的相同，在这里您不尝试在值周围加上引号...)

You can't include the quotes in "%s" in the query string; the mysqldb module adds them for you (same as it does in your INSERT where you don't try to put quotes around the values...)

这篇关于错误的日期时间值:"2012-07-14 23:00:00"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！