Invalid Literal from MySQLdb Query

[Ben Last]

> From Wesley Kincaid
> I'm attempting to run a simple query through MySQLdb's
> cursor.execute().  However, when the request includes a timestamp
> field, I'm getting "ValueError: invalid literal for int(): 9-."
If this is the same issue I encountered, I believe that the root issue is
that the MySQLdb module is attempting to convert the timestamp to an
appropriate Python type, choosing int as that type and then discovering that
the value of the timestamp won't convert.  This may be a version issue with
MySQLdb and MySQL.

However, you can tell MySQLdb explicitly how to convert types.  This is a
snippet of Quasi code (http://quasi-shell.sourceforge.net):

import MySQLdb.converters, MySQLdb.constants.FIELD_TYPE

        ##snipped code...

        #As per the MySQLdb comments, *copy* the exting converters dict.
        conversions = copy.copy(MySQLdb.converters.conversions)
        #Use our own converters for Unicode/String
        conversions[types.UnicodeType] = Unicode2Str
        conversions[MySQLdb.constants.FIELD_TYPE.STRING] = Str2Unicode
        conversions[MySQLdb.constants.FIELD_TYPE.VAR_STRING] = Str2Unicode

        #Deal with timestamps in a more rational way than forcing to int
        conversions[MySQLdb.constants.FIELD_TYPE.TIMESTAMP] = TSHandler

        #Install these for this connection
        dargs['conv'] = conversions
        QuasiSQL.sqlConnection = MySQLdb.connect(**dargs)

        ##snipped code....

def TSHandler(s):
    """Convert a TIMESTAMP type to something sensible."""
    #Depending on the version of MySQLdb, TIMESTAMPs can come
    #in a variety of formats.  Rather than check them carefully,
    #we cope with the MySQLdb >= 4.1 standard and allow failures
    #to result in a string.
    try:
        #Assume it's expressed in MySQL standard form
        return time.mktime(time.strptime(s,'%Y-%m-%d %H:%M:%S'))
    except ValueError:
        pass
    return str(s)

Incidentally, I'm not suggesting this is the best solution, but it
demonstrates how you can force type conversions in MySQLdb.  In my case,
this sufficed and will be expanded to a more general solution later ;)

demonstratively yours,
ben