Float precision loss with pyformat

I am using pymssql 1.0.2 and SQL Server 2005. SQLAlchemy creates the SQL statements using pyformat %%(%(name)s)s. This causes float values to be converted to string with str(), and that conversion causes precision loss even when both the server and Python could handle more precise floats. I switched the %%(%(name)s)s format to %%(%(name)s)r in sqlalchemy.sql.compiler.BIND_TEMPLATES, and got better results. Of course this is not the real solution since it breaks other string escapes.

Before switching to SQLAlchemy, I created my query statements manually and used simple type checking for bind values and added %(name)s, %(name)i and %(name)r formatting accordingly. Maybe SQLAlchemy could do the same?

Code example:

import sqlalchemy as sa
from sqlalchemy import orm

engine = sa.create_engine('mssql+pymssql:// ...')
metadata = sa.MetaData(bind=engine)

test_table = sa.Table('TEST', metadata,
    sa.Column('ID', sa.Integer, primary_key=True),
    sa.Column('FLT', sa.Float),
    )

class Foo(object):
    pass

orm.mapper(Foo, test_table)

Session = orm.sessionmaker(autoflush=False, autocommit=False)
session = Session()

Results with default SQLAlchemy 0.6:

>>> o = Foo()
>>> o.FLT = 12345678.123456789
>>> session.add(o)
>>> session.commit()
>>> o.FLT
12345678.123500001

After I changed the bind template:

>>> o = Foo()
>>> o.FLT = 12345678.123456789
>>> session.add(o)
>>> session.commit()
>>> o.FLT
12345678.123456787

I know Python's float isn't perfect, but str conversion loses too much precision in this case.

Comments (3)