Inserting datetime into MySql db

Question

I have a datetime value which is made by strptime function

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

There is a column in MySql db of type DATETIME. When I try to insert this value into db, I, obviously, get the error of

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 can I insert this value into db?

Answer 1

You are now passing in a time.struct_time object, something MySQL knows nothing about. You'll need to format the timestamp to a format MySQL understands. Unfortunately the MySQLdb library doesn't do this for you.

It'll be easiest using the datetime module, but you can do this with the time module too:

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'),))

The .strftime() method call on the datetime.datetime object formats the information in such a way that MySQL will accept.

Doing the same task with just 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),))