Mysql时间戳数据列被截断 [英] Mysql timestamp Data truncated for column

问题描述

我正在尝试使用python将时间戳插入到mysql数据库的created_by列中.

I'm attempting to use python to insert a timestamp into column created_by of a mysql db.

这是我的数据库表设置.

Here is my database table setup..

CREATE TABLE temps (
temp1 FLOAT, temp2 FLOAT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

temp1和temp2并正确填充，但收到时间戳错误

temp1 and temp2 and populating correctly but receiving an error for the timestamp

 Warning: Data truncated for column 'created_at' at row 1
  cursor.execute("""INSERT INTO temps VALUES (%s,%s,%s)""",(avgtemperatures[0],avgtemperatures[1],st[2]))
((71.7116, 73.2494, None),)

这是python脚本的部分，它将信息插入db.

Here is the section of python script that inserts information into the db.

 #connect to db
db = MySQLdb.connect("localhost","user","password","temps" )

 #setup cursor
cursor = db.cursor()
ts = time.time()
st = datetime.datetime.fromtimestamp(ts).strftime('%Y-%m-%d %H:%M:%S')

sql = """CREATE TABLE IF NOT EXISTS temps (
  temp1 FLOAT,  
  temp2 FLOAT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP)"""
cursor.execute(sql)


 #insert to table
try:
    cursor.execute("""INSERT INTO temps VALUES (%s,%s,%s)""",(avgtemperatures[0],avgtemperatures[1],st[2]))
    db.commit()
except:     
    db.rollback()


 #show table
cursor.execute("""SELECT * FROM temps;""")

print cursor.fetchall()
((188L, 90L),)

db.close()

这是数据库的转储:

Dumping data for table temps
temp1   temp2   created_at
71.7116 73.2494 0000-00-00 00:00:00

推荐答案

您必须设置日期时间.插入时，您created_at列将自动更新为当前时间戳.请参见 TIMESTAMP的自动初始化和更新中的文档.

You should have to set the datetime. Your created_at column will automatically be updated to the current time stamp at insertion. See documentation at Automatic Initialization and Updating for TIMESTAMP.

您的声明应为

cursor.execute("""INSERT INTO temps VALUES (%s,%s,CURRENT_TIMESTAMP)""",(avgtemperatures[0],avgtemperatures[1]))

这篇关于Mysql时间戳数据列被截断的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！