Convert string in Python to insert into MySQL database column date

Question

I have an unusual date string that I want to insert into a MySQL database.

date = 'Thu, 14 Mar 2013 13:33:07 -0400' 

And here is the insert statement

 self.cursor.execute("INSERT INTO table1 (`DATE`)VALUES (%s);",(date)) 

When I do it like this, it appears in the database, for example:

 0000-00-00 00:00:00 

Even if I enter the SQL manual, it displays the same way.

How to convert date string to readable mysql date for insert?

Answer 1

pip install python-dateutil

 from dateutil.parser import parse date = 'Thu, 14 Mar 2013 13:33:07 -0400' parse(date).strftime("%Y-%m-%d %H:%M:%S") 

Answer 2

This date string is in RFC 2822 date format and can be parsed with email.utils.parsedate (which is part of the standard library):

 In [428]: import email.utils as eu In [429]: eu.parsedate('Thu, 14 Mar 2013 13:33:07 -0400') Out[429]: (2013, 3, 14, 13, 33, 7, 0, 1, -1) 

Once you have a datetime object, you can paste it into this form (without formatting) in MySQL:

 date = eu.parsedate('Thu, 14 Mar 2013 13:33:07 -0400') self.cursor.execute("INSERT INTO table1 (`DATE`) VALUES (%s)",(date,)) 

---

Note. The second argument to cursor.execute must be a sequence. So use tuple (date,) instead of the datetime (date) object.

Also, no semicolons are needed in your SQL strings.

Answer 3

I do not have enough reputation to comment on this question, so I will answer here.

If you get ImportError when it starts, you need to install the module:

 from dateutil.parser import parse 

Go here to download the parser: http://labix.org/python-dateutil#head-2f49784d6b27bae60cde1cff6a535663cf87497b

Then extract the folder and open cmd with administrator privileges. Go to the folder and enter the following command:

 python setup.py install