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?

This should help you: stackoverflow.com/questions/1136437/… — alecxe
– alecxe, Commented May 3, 2013 at 12:58

Martijn Pieters · Accepted Answer · 2014-04-18 18:37:21Z

10

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

edited Apr 18, 2014 at 18:37

answered May 3, 2013 at 13:06

Martijn Pieters

1.1m326 gold badges4.2k silver badges3.4k bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Martijn Pieters Over a year ago

@Antimony: That was not a typo; there is a a.strftime() call in the sample code posted. I am a little upset with all the reviewers that accepted that suggested edit now.

Antimony Over a year ago

I realized that. Sorry. I changed it back.

Collectives™ on Stack Overflow

Inserting datetime into MySql db

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related