Insert date and time in MySql db

Question

I have a datetime value that is created by the strptime function

import MySQLdb a = time.strptime('my date', "%b %d %Y %H:%M") 

There is a DATETIME type in mysql db. When I try to insert this value in db, I obviously get an error

 mysql_exceptions.OperationalError: (1305, 'FUNCTION time.struct_time does not exist') INSERT INTO myTable(Date......) VALUES(time.struct_time(tm_year=2222, tm_mon=4, tm_mday=1, tm_hour=1, tm_min=2, tm_sec=4, tm_wday=1, tm_yday=118, tm_isdst=-1), ......) 

How to insert this value in db?

Answer 1

Now you are passing the time.struct_time object, MySQL knows nothing about something. You need to format the timestamp in a format that MySQL understands. Unfortunately, the MySQLdb library MySQLdb not do this for you.

This will be easiest with the datetime module, but you can also do this with the time module:

 import datetime a = datetime.datetime.strptime('my date', "%b %d %Y %H:%M") cursor.execute('INSERT INTO myTable (Date) VALUES(%s)', (a.strftime('%Y-%m-%d %H:%M:%S'),)) 

Calling the .strftime() method of the datetime.datetime object formats the information in such a way that MySQL will accept.

Performing one task using only the time module:

 import time a = time.strptime('my date', "%b %d %Y %H:%M") cursor.execute('INSERT INTO myTable (Date) VALUES(%s)', (time.strftime('%Y-%m-%d %H:%M:%S', a),))