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], ...]
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.