Python MySQLdb返回datetime.date和十进制 [英] Python MySQLdb returns datetime.date and decimal

问题描述

我有一个MySQL查询，例如:

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')

有没有一种方法可以获取常规数据，以便将其传递给UI进行处理?普通数据，我的意思是:

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

推荐答案

默认转换器MySQLdb.converters.conversions是具有以下条目的字典:

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

您可以更改转换器，并将其传递给connect方法，如下所示:

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
    )

通过在交互式Python会话中检查MySQLdb.converters.conversions并根据默认值进行有根据的猜测可以找到键10和246.

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

顺便问一下，您是如何通过SQL查询解决此问题的?请添加为答案.

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

这篇关于Python MySQLdb返回datetime.date和十进制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！