从 Access 数据库查询问题:“无法将字符串转换为浮点数:E+6" [英] Issue querying from Access database: "could not convert string to float: E+6"

问题描述

我在 MS Access 中有一个数据库.我正在尝试使用 pypyodbc 向 Python 查询一张表.我收到以下错误消息:

I have a database in MS Access. I am trying to query one table to Python using pypyodbc. I get the following error message:

ValueError: 无法将字符串转换为浮点数:E+6

ValueError: could not convert string to float: E+6

表中的数字相当大，最多有十位有效数字.错误消息告诉我 MSAccess 正在用科学记数法格式化它们，而 Python 正在将它们作为字符串读取.

The numbers in the table are fairly big, with up to ten significant figures. The error message tells me that MSAccess is formatting them in scientific notation and Python is reading them as strings.

表格中的字段格式为带两位小数的单数.当我看到数据库表中的数字时，它们没有使用科学记数法进行格式化.但错误消息似乎表明它们是.

The fields in the table are formatted as singles with two decimal places. When I see the numbers in the table in the database they are not formatted using scientific notation. but the error message seems to indicate that they are.

此外，如果我将表中的数字(至少对于测试行)更改为小数字(从 1 到 5 的整数)，查询将运行.这支持我的理论，即问题是大数的科学格式化.

Furthermore, if I change the numbers in the table (at lest for a test row) to small numbers (integers from 1 to 5) the query runs. Which supports my theory that the problem is scientific formatting of big number.

任何关于如何操作的想法:

Any ideas of how to:

1. 以不使用科学计数法格式化数字的方式写入数据库表，或
2. 使 pypyodbc 像这样检索数字并忽略任何科学记数法.

推荐答案

这似乎是 pypyodbc 和 Access ODBC 驱动程序(列)，其中大"是指

This appears to be a compatibility issue between pypyodbc and the Access ODBC driver when retrieving "large" or "small" numbers from a Single or Double field (column), where "large" means

• 单个值的小数点左侧超过 6 位有效数字，或
• Double 小数点左边超过 14 位有效数字的值

• Single values with more than 6 significant digits to the left of the decimal point, or
• Double values with more than 14 significant digits to the left of the decimal point

和小"的意思

• 单个值，紧邻小数点右侧有 6 个以上的零，或
• Double 小数点右侧有超过 14 个零的值

• Single values with more than 6 zeros immediately to the right of the decimal point, or
• Double values with more than 14 zeros immediately to the right of the decimal point

当数字表示为正常"小数时(即不采用科学计数法).

when the numbers are represented as "normal" decimals (i.e., not in scientific notation).

要重新创建的代码:

import pypyodbc
cnxn = pypyodbc.connect(
    r"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};"
    r"DBQ=C:UsersPublicDatabase1.accdb")
crsr = cnxn.cursor()
try:
    crsr.execute("DROP TABLE tblJunk")
except pypyodbc.ProgrammingError as pe:
    # ignore "table does not exist"
    if pe.value[0] != '42S02':
        raise
crsr.execute("CREATE TABLE tblJunk (ID INT PRIMARY KEY, DoubleField DOUBLE, SingleField SINGLE)")
crsr.execute("INSERT INTO tblJunk (ID, DoubleField) VALUES (1, 12345678.9)")
crsr.execute("SELECT DoubleField, SingleField FROM tblJunk WHERE ID=1")
row = crsr.fetchone()
print(row)
# prints: (12345678.9, None)
crsr.execute("UPDATE tblJunk SET SingleField = DoubleField WHERE ID=1")
crsr.execute("SELECT DoubleField, SingleField FROM tblJunk WHERE ID=1")
row = crsr.fetchone()
# ValueError: could not convert string to float: E+7

解决方法1:对于Single值，使用CDbl()函数可以避免错误:

Workaround 1: For Single values, using the CDbl() function can avoid the error:

crsr.execute("SELECT DoubleField, CDbl(SingleField) AS foo FROM tblJunk WHERE ID=1")
row = crsr.fetchone()
print(row)
# prints: (12345678.9, 12345679.0)

解决方法 2: 使用 CStr() 函数将值作为字符串返回，然后将其转换为浮点数(适用于 Single 和 Double):

Workaround 2: Use the CStr() function to return the value as a string and then convert it to a float afterwards (works for both Single and Double):

crsr.execute("SELECT DoubleField, CStr(SingleField) AS foo FROM tblJunk WHERE ID=1")
row = crsr.fetchone()
print(row)
# prints: (12345678.9, u'1.234568E+07')
print(float(row[1]))
# prints: 12345680.0

解决方法 3: 使用 pyodbc 而不是 pypyodbc.

Workaround 3: Use pyodbc instead of pypyodbc.

这篇关于从 Access 数据库查询问题:“无法将字符串转换为浮点数:E+6"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！