Python通过pyodbc在Access的十进制数中插入冒号 [英] Python inserts a colon in a Decimal number from Access via pyodbc

问题描述

我正在使用与此人相同的问题，可能还有

I'm having the same problem as this guy and possibly this guy, but I am around to share some code and respond to questions!

批处理作业中有一些代码，可以通过pyodbc从Microsoft Access数据库中读取字段，并准备输出以供显示.

I have some code in a batch job that reads fields from a Microsoft Access database via pyodbc and prepares the output for display.

这是一个代码段.请注意断言.

Here is a snippet. Note the assert.

def format_currency(amount):
    if amount is None:
        return ""
    else:
        result = "$%.2f" % amount
        assert ":" not in result, (
            "That's weird. The value %r of class %s is represented as %s" %
             (amount, amount.__class__, result))
        return result

当我运行它时，它成功处理了100,000行，然后失败:

When I run it, it successfully processes 100,000 rows and then fails:

AssertionError: That's weird. The value Decimal('54871.0000') of class <class
'decimal.Decimal'> is represented as $54870.:0

注意异常结肠.这种情况很少发生-在300,000条记录中大约发生一次.

Note the aberrant colon. It occurs rarely - about one time in 300,000 records.

当我尝试隔离它时，它当然可以工作.

When I try to isolate it, of course it works.

from decimal import Decimal
print "$%.2f" % Decimal('54871.0000')

$ 54871.00

$54871.00

Access中的字段类型为:

The type of the field in Access is:

• 数据类型:货币
• 小数位:2
• 输入掩码:
• 默认值:
• 验证规则:
• 文本对齐:常规

基于不足的证据，我模糊地指责了怀疑:pyodbc正在使用Decimal的内部结构，可能是由于Access损坏而混淆了. @ecatmur 指出:

My vague finger-pointing suspicion based on insufficient evidence: pyodbc is poking with the internals of Decimal, perhaps confused by an Access corruption. As @ecatmur points out:

':'是ASCII中的'9'+ 1

':' is '9' + 1 in ASCII

有人看到并解决了吗?

版本:

• Python 2.7.4
• pyodbc 3.0.6(最新)
• 访问2010
• Windows 7

进一步挖掘:

decimal模块是用Python实现的.根据我的阅读，这些值由四个属性描述:_exp，_int，_sign，_is_special

The decimal module is implemented in Python. From my reading, the values are described by four attributes: _exp, _int, _sign, _is_special

怀疑是腐败，我打印了这些字段的值.

Suspecting corruption, I printed out the values of these fields.

令人惊讶的是，对于都是的错误版本和正常工作版本，我得到了:

Surprisingly, for both the faulty and the working version, I get:

_exp: -4
_int: 548710000
_sign: 0
_is_special: False

那很奇怪.

在decimal模块中，__float__函数的定义非常简单:

In the decimal module, the __float__ function is defined fairly simply:

def __float__(self):
    """Float representation."""
    return float(str(self))

但是当我对不良数据执行此操作时:

But when I do this with the bad data:

print "Str", str(amount)
print "Float", float(amount)

我得到:

Str 54871.0000

Str 54871.0000

浮点54870.:

我学得越多，没就越奇怪.

The more I learn, the less weird it doesn't get.

推荐答案

我能够重现该错误.我创建了一个Access表[pyData] ...

I was able to reproduce the error. I created an Access table [pyData]...

ID-自动编号
金额-货币(小数点后2位)

ID - AutoNumber
Amount - Currency (2 decimal places)

...并在其中填充一百万行介于50,000和60,000之间的随机值.当我运行测试脚本时，它在这里失败

...and filled it with a million rows of random values between 50,000 and 60,000. When I ran my test script it failed here

30815 : $50638.91
30816 : $52423.28
30817 :

Traceback (most recent call last):
  File "C:\__tmp\pyOdbcTest.py", line 20, in <module>
    print row.ID, ":", format_currency(row.Amount)
  File "C:\__tmp\pyOdbcTest.py", line 10, in format_currency
    (amount, amount.__class__, result))
AssertionError: That's weird. The value Decimal('58510.0000') of class <class 'decimal.Decimal'> is represented as $5850:.00

我还测试了该值(58510.00)和为您失败的那个值(54871.00)，作为具有相同结构的单独表中的单个行，它们都失败了.因此，我们知道这不是早期ODBC调用中残留的垃圾"的功能.

I also tested that value (58510.00) and the one that failed for you (54871.00) as single rows in a separate table with the same structure, and they both failed. So we know that it's not a function of some leftover "junk" from an earlier ODBC call.

考虑到它可能与数字有一个"1"，然后在数字的末尾加零有关，我尝试了55871.00，但效果很好. 53871.00也很好.将数字改回54871.00可以恢复错误.

Thinking that it might be related to the number having a '1' followed by zeroes to the end of the number, I tried 55871.00, but that worked fine. 53871.00 worked fine, too. Changing the number back to 54871.00 revived the error.

我使用 pypyodbc 尝试了相同的测试，并得到了相同的错误.我有些乐观，因为pypyodbc包含许多特定于Access的功能，因此我认为它的一个用户以前可能曾遇到过此问题，但显然没有.

I tried the same test using pypyodbc and got the same error. I was somewhat optimistic because pypyodbc includes a number of Access-specific features, so I thought that one of its users may have encountered this problem before, but apparently not.

最后，我将测试表升级到SQL Server 2008 R2 Express，并使用{SQL Server Native Client 10.0}驱动程序尝试了相同的测试.从Access中读取失败的数字(货币"列类型)在从SQL Server表中读取(金钱"列类型)时 not 失败.

Finally, I upsized my test table to SQL Server 2008 R2 Express and tried the same test using the {SQL Server Native Client 10.0} driver. The numbers that failed when read from Access ("Currency" column type) did not fail when read from the SQL Server table ("money" column type).

因此，目前我能为答案"提供的最好的方法是:

So, the best I can offer for an "answer" at the moment is:

看起来是这样的:

• pyodbc(和pypyodbc，似乎与pyodbc密切相关)中的错误，或者

• a bug in pyodbc (and pypyodbc, which appears to be quite closely related to pyodbc), or

Microsoft Access ODBC驱动程序中的错误，或者

a bug in the Microsoft Access ODBC Driver, or

两者之间的不幸的交互作用"(如果ODBC规范过于宽松以至于两个组件在技术上都不是错误的").

an "unfortunate interaction" between the two (if the ODBC spec is loose enough that neither component is technically "wrong").

无论如何，至少现在看来，您似乎需要解决它.

In any case it looks like you'll need to work around it, at least for now.

由于我拥有大量的数字，所以我决定让脚本继续运行，并查看其他数字可能会以冒号格式化.结果列表似乎都是整数(没有几分钱)，所以我进行了另一次整数在1到100,000之间的测试.我在格式化的字符串中发现了260个数字，并以冒号结束:

Since I had that big batch of numbers I decided to let the script keep running and see what other numbers might get formatted with a colon in them. The resulting list all seemed to be whole numbers (no pennies), so I ran another test with whole numbers between 1 and 100,000. I found 260 numbers that wound up with a colon in the formatted string:

1451.0000 -> $1450.:0
1701.0000 -> $1700.:0
1821.0000 -> $1820.:0
1951.0000 -> $1950.:0
2091.0000 -> $2090.:0
...
98621.0000 -> $98620.:0
98710.0000 -> $9870:.00
99871.0000 -> $99870.:0

我在此处粘贴了整个列表.也许这会有所帮助.

I pasted the entire list here. Perhaps that might be helpful.

我以前的测试是在Python 2.7.3版下运行的.我刚刚将Python升级到2.7.5版(Win 32位)，而pyodbc仍然是3.0.6版，问题似乎已经消失了.

My previous tests were run under Python version 2.7.3. I just upgraded Python to version 2.7.5 (Win 32-bit) with pyodbc still at version 3.0.6 and the problem seems to have gone away.

这篇关于Python通过pyodbc在Access的十进制数中插入冒号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！