CODEWITHSUNDEEP
pythonmysqlpython-2.7
Alan Coromano
Alan Coromano

Reputation: 26048

Inserting datetime into MySql db

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?

Upvotes: 7

Views: 33570

Answers (1)

Martijn Pieters
Martijn Pieters

Reputation: 1125018

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

Upvotes: 10

Related Questions