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

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.

Answer 2

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 3

You can avoid to get a convertion by forcing the specific column to be cast as CHAR in your query string

import MySQLdb
import datetime 
conn = MySQLdb.connect(host= "mysql-server",user="weblogadmin", passwd="admin123",db="weblog_db") 
sqlcursor = conn.cursor() 
sqlcursor.execute("SELECT logrole,logserver,logtype,loglevel,CAST(logdatetime AS CHAR),logdetail,logaction,logread FROM logapp_logtable") 
row = sqlcursor.fetchall() 
for data in row:    
    print(str(data)) 
conn.close()

Answer 4

MySQLdb is trying to be helpful, giving you the Python versions of your SQL datatypes.

However if for some reason need the strings, you can just stringify everything you get back. So something like [[str(val) for val in row] for row in results] should do it.

Answer 5

In case you use pymysql instead of MySQLdb, the answer is similar. First you import the converters and constant types. Copy the general converter used, and change the decimal points to convert as floats.

import pymysql
from pymysql.constants import FIELD_TYPE
from pymysql.converters import conversions

conv = conversions.copy()
conv[FIELD_TYPE.DECIMAL] = float
conv[FIELD_TYPE.NEWDECIMAL] = float

After that, use the conv dict in your connection.

connection = pymysql.connect(host = db_host,     
                                 user = db_user,    
                                 passwd = db_pass,
                                 db = db_db,
                                 charset='utf8mb4',
                                 conv = conv,
                               max_allowed_packet = 33554432,
                                    cursorclass=pymysql.cursors.DictCursor)

Now decimal are being converted to float