python - Issue querying from Access database: "could not convert string to float: E+6"

Question

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: could not convert string to float: E+6

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.

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. write into the database table in a way that the numbers are not formatted in scientific notation, or
2. make pypyodbc retrieve numbers as such and ignore any scientific notation.

Answer 1

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

and "small" means

• 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).

Code to recreate:

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

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)

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

Workaround 3: Use pyodbc instead of pypyodbc.