Python MySQLdb returns datetime.date and decimal

Question

I have a MySQL query like:

SELECT mydate, countryCode, qtySold from sales order mydate, countryCode

This returns tuples of tuples with values like:

((datetime.date(2011, 1, 3), 'PR', Decimal('1')), (datetime.date(2011, 1, 31), 'MX', Decimal('1')))

When I try printing this using a loop, it prints perfectly fine:

2011-1-3, PR, 1
2011-1-31, MX, 1

But when I try to return this value, it returns as

datetime.date(2011, 1, 3), 'PR', Decimal('1')

Is there a way that I can get normal data so that I can pass it to UI for processing? By normal data I mean:

[['2011-1-03', 'PR', 1], ...]

Answer 1

If you mean you want str types for your dates and int types for your numbers, look at datetime.date.strftime() and int():

>>> datetime.date(2011, 1, 3).strftime("%Y-%m-%d")
'2011-01-03'

>>> int(Decimal(2))
2

If you want to systematically change what types MySQLdb returns for various column types, see the conv keyword argument for MySQLdb.connect():

http://mysql-python.sourceforge.net/MySQLdb.html#mysqldb

Answer 2

The default converter, MySQLdb.converters.conversions is a dict with entries like this:

{0: <class 'decimal.Decimal'>,
 1: <type 'int'>,
 2: <type 'int'>,
 3: <type 'long'>,
 4: <type 'float'>,
 5: <type 'float'>,
 7: <function mysql_timestamp_converter at 0x89e4454>,
 8: <type 'long'>,
 9: <type 'int'>,
 10: <function Date_or_None at 0x89e43ac>,
 ...
}

You can change the converter and pass that to the connect method like this:

conv=converters.conversions.copy()
conv[246]=float    # convert decimals to floats
conv[10]=str       # convert dates to strings
connection=MySQLdb.connect(
    host=HOST,user=USER,
    passwd=PASS,db=DB,
    conv=conv
    )

The keys 10 and 246 were found by inspecting MySQLdb.converters.conversions in an interactive Python session and making an educated guess based on the default values.

The converter can also be changed after the connection is made:

connection.converter=conv

By the way, how did you resolve the issue with an SQL query? Please add that as an answer.