Incorrect Datetime Value: ''2012-07-14 23:00:00''

I'm having some trouble with the datetime format with Python/MySQL. I calculate the datetime using the following script (fed by a Python dictionary): tempDate = str(eachday.get('d

Solution 1:

You can't include the quotes in "%s" in the query string; the mysqldb module adds them for you (same as it does in your INSERT where you don't try to put quotes around the values...)

Solution 2:

You could easily make use of the datetime module, which is a lot more controlled for building date strings:

from datetime import datetime

eachday = {
    'date': {
        'day': 2, 
        'hour': 4, 
        'min': 4, 
        'month': 4, 
        'sec': 23, 
        'year': 2012
    }
}
keys = ('year','month','day','hour','min','sec')
dt = datetime(*(eachday['date'][k] for k in keys))
print dt.strftime('%Y-%m-%d %H:%M:%S')
# '2012-04-02 04:04:23'

The benefit of sticking with a datetime, is that its the native format you will be getting from MySQLdb anyways. And you can pass it as a value with NO conversions. So really, formatting the date string isn't even needed.

sql.execute("DELETE FROM `db`.`table` WHERE `time`=%s", (dt,))

It is best to keep the datetime value as a datetime object as long as possible instead of carrying around int components.