Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Convert String In Python to insert into MySQL DB date column date

Tags:

python

date

mysql

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

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 shows up in the database like :

0000-00-00 00:00:00

Even if I enter the SQL manualy it shows up the same way.

How do I convert the date string into a mysql readable date for insert?

like image 279
Marc Avatar asked Mar 15 '13 00:03

Marc


3 Answers

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")
like image 185
Michael Benjamin Avatar answered Oct 03 '22 16:10

Michael Benjamin


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 insert it in that form (without formatting) into 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 the tuple (date,) instead of the datetime object (date).

Also, no semicolons are necessary in your SQL strings.

like image 20
unutbu Avatar answered Oct 03 '22 16:10

unutbu


I don't have enough reputation to comment to the question above, so I'll put the answer here.

If you get an ImportError when running this, you need to install the module:

from dateutil.parser import parse

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

Next, extract the folder and open cmd with admin privileges. Navigate to the folder and type this:

python setup.py install
like image 29
User Avatar answered Oct 03 '22 15:10

User