从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这样检索数字，而忽略任何科学计数法.

推荐答案

这似乎是Single或Double字段(列)中检索大"或小"数字时，'pypyodbc'"rel =" tag> 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

• Single值的小数点左边有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

小"的意思是

• Single值的小数点后立即有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:\Users\Public\Database1.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屋！