更新日期 &通过python进入MySQL的时间
我正在尝试更新我的日期 &我的 MySQLdb 数据库中的时间列.我有 2 列名称为 date1 和 time1,并且都是 TEXT 类型.这是我的python代码
I am trying to update my date & time columns in my MySQLdb database. I have 2 columns by the name date1 and time1 and both are type TEXT. This is my python code
import MySQLdb
import datetime
db = MySQLdb.connect('localhost','admin','admin','dbname')
cur = db.cursor()
a = datetime.datetime.strftime(datetime.date.today(),'%Y-%m-%d'))
cur.execute('update Data set date1={0}'.format(a))
db.commit()
但在此代码执行后,我看到列中的值设置为2000".
But after this code executes I see the value in the column set as '2000'.
我在 MySQL 中尝试了几种日期类型 - varchar2、date、datetime,但它们总是显示为2000"或0000-00-00".
I tried several datetypes in MySQL - varchar2, date, datetime but they always show as '2000' or '0000-00-00'.
当我直接运行这个 sql 查询时,它工作正常:
When I run this sql query directly it works fine:
UPDATE `Data` SET `date1`='22-04-1994'
我用谷歌搜索了这个错误并尝试了这个:
I googled this error and tried this:
a = datetime.date.today()
a = a.isoformat()
但它在数据库中显示为 2000.
But it shows in the database as 2000.
我尝试使用 {0} 而不是 +a.仍然是 2000 年.
I tried using {0} instead of +a. Still coming as 2000.
我使用的是 Mysql 版本 5.5.51-38.2
I am using Mysql version 5.5.51-38.2
想想你的查询做了什么,因为你连接字符串.你甚至可以打印出来.由于您没有将日期放入引号中,因此将被视为计算.所以你是说:
Think what your query does, since you concatenate strings. You can even print it out. Since you don't put the date into quotes it will be considered a calculation. So you're saying:
date1=2016-11-5
这当然是 2000,但你想要
This is of course 2000, but you want
date1='2016-11-5'
最好的方法是使用参数,以便底层系统为您完成所有这些工作,而不是您连接字符串并尝试正确转义它们.
The best way is to use parameters so the underlying system is doing all this for you rather than you concatenating strings and trying to escape them correctly.