Managing & Using MySQL, 2nd Edition

Other Objects

DB-API provides a host of other objects to help encapsulate common SQL data types so they may be passed as parameters to execute( ) and executemany( ) and relieve developers of the burden of formatting them for different databases. These objects include Date , Time , Timestamp , and Binary . MySQLdb supports these objects up to a point. Specifically, when MySQLdb binds parameters, it converts each paramter to a string (via __str__) and places it in the SQL. The Timestamp object, in particular, includes fractional seconds, which MySQL considers illegal input.

It is important to note that MySQLdb does not properly implement the Date( ), Time( ), and Timestamp( ) constructors for their respective objects. You instead have to use the DateFromTicks( ) , TimeFromTicks( ) , and TimestampFromTicks( ) methods to get a reference to the desired object. The argument for each of these methods is the number of seconds since the epoch.

The following code creates a Date for the current time and updates the database:

import time;
d = MySQLdb.DateFromTicks(time.time(  ));
cursor.execute("UPDATE test SET test_date = %s WHERE test_id = 1", (d,));