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