Incorrect datetime value: ''2012-07-14 23:00:00''
Question: 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("date").get("year")).zfill(4) + "-" + 
    str(eachday.get("date").get("month")).zfill(2) + "-" + 
    str(eachday.get("date").get("day")).zfill(2)   + " " + 
    str(eachday.get("date").get("hour")).zfill(2)  + ":" + 
    str(eachday.get("date").get("min")).zfill(2)   + ":" + 
    str(eachday.get("date").get("sec")).zfill(2)
    

Which yields a value that looks like `2012-04-02 04:04:23`.

I can insert into MySQL without any problems.

    
    
    sql.execute("""INSERT INTO `db`.`table`(`id`, `fk_id`, `time`, `field1`, `field2`) VALUES (NULL, %s, %s, %s, %s);""", (fk_value, tempDate, value1, value2)) 
    DB_CONN.commit()
    

But when I try to delete anything with that datetime,

    
    
    sql.execute("""DELETE FROM `db`.`table` WHERE `time` = "%s";""", (tempDate))
    DB_CONN.commit()
    

it returns a warning about an incorrect datetime value:

    
    
    Warning: Incorrect datetime value: ''2012-07-17 23:00:00'' for column 'time' at row 1
    

How can I delete by datetime in Python? This is especially confusing since
inserting the same variable (nothing changed) works perfectly.


Answer: 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.

