user2726634 user2726634 - 1 month ago 7
Python Question

Updating Date & Time into MySQL via 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()


But after this code executes I see the value in the column set as '2000'.

I tried several datetypes in MySQL - varchar2, date, datetime but they always show as '2000' or '0000-00-00'.

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()


But it shows in the database as 2000.

Edit:
I tried using {0} instead of +a. Still coming as 2000.

I am using Mysql version 5.5.51-38.2

Answer

You should be using parameters. But, if not, you need to include single dates around date constants:

cur.update('update Data set date1 = '{0}'".format(a))