“警告：列的超出范围值”将日期时间值插入MySQL

Pulling some data from MongoDB and inserting it into a MySQL database, using Python and the Mysqldb library.

使用Python和Mysqldb库从MongoDB中提取一些数据并将其插入MySQL数据库。

+-------------------+------------+------+-----+---------+----------------+
| Field             | Type       | Null | Key | Default | Extra          |
+-------------------+------------+------+-----+---------+----------------+
| id                | int(16)    | NO   | PRI | NULL    | auto_increment |
| subject           | tinytext   | NO   |     | NULL    |                |
| created_at        | datetime   | NO   |     | NULL    |                |
+-------------------+------------+------+-----+---------+----------------+

created_at  = "2012/06/08 11:47:40 -0700"
sql1 = ("INSERT INTO `items` (`description`, `created_at`) VALUES (%s, %s)", (description, created_at)
try:
    cursor.execute(*sql1)
except MySQLdb.Error, e:
    print "Error %s: %s" % (e.args[0], e.args[1])

For the first insert into the database I get a warning, however I don't receive that warning for subsequent inserts.

对于第一次插入数据库,我收到一个警告,但是我没有收到后续插入的警告。

conf/db_tools-import.py:53: Warning: Out of range value for column 'created_at' at row 1 cursor.execute(*sql1)

conf / db_tools-import.py:53:警告:第1行cursor.execute(* sql1)列'created_at'超出范围值

How can I resolve this? Thanks.

我该如何解决这个问题?谢谢。

1 个解决方案

#1

You are receiving an warning because you are inserting a long date into a date field. The date filed is "YYYY-MM-DD" (http://dev.mysql.com/doc/refman/5.1/en/datetime.html).

您收到警告是因为您在日期字段中插入了较长的日期。提交日期为“YYYY-MM-DD”(http://dev.mysql.com/doc/refman/5.1/en/datetime.html)。

I did my own test in Mysql, take a look:

我在Mysql中做了自己的测试,看看:

mysql> create table bigtable (created_at date);
Query OK, 0 rows affected (0.07 sec)

mysql> 

Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------+
| Level   | Code | Message                                         |
+---------+------+-------------------------------------------------+
| Warning | 1265 | Data truncated for column 'created_at' at row 1 |
+---------+------+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from bigtable;
+------------+
| created_at |
+------------+
| 2012-06-08 |
+------------+
1 row in set (0.00 sec)

if you want to avoid the warning, just pass "YYYY-MM-DD" or change the column to datetime,

如果你想避免警告,只需传递“YYYY-MM-DD”或将列更改为日期时间,

Good luck

#1