O'Reilly logo

Managing & Using MySQL, 2nd Edition by Hugh E. Williams, Randy Yarger, George Reese, Tim King

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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,));

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required