On Oct 29, 2010, at 12:37 PM, Mark Erbaugh wrote: > How do people handle Decimal data with SA / SQLite? Newer versions of SA > give the following warning: > > SAWarning: Dialect sqlite+pysqlite does *not* support Decimal objects > natively, and SQLAlchemy must convert from floating point - rounding errors > and other issues may occur. Please consider storing Decimal numbers as > strings or integers on this platform for lossless storage. > > I assume that the gist of the message is similar to the reason that > decimal.Decimal objects in Python can't be initialized with a float, just > int's and string's. I'm working on an accounting application where I would > like to use Decimal fields to store monetary amounts. If I understand the > warning, I should convert the Python Decimal data and from ints to store in > the database. I think I can safely get away with ints if I design things so > that every data is stored with the same number of decimal digits. I could > also use strings in the database, but that would eliminate the ability to do > math operations, such as SUM in the SQL code. > > Also, is there an single place in the code to to the int / Decimal > conversion. Ideally, I would like the application to take advantage of > Decimal database objects in databases where they are supported, so it would > be nice to have one place in the code that changes depending on which > database is in use.
Decimals can actually be initialized with floats in Python 2.7. I looked at their code and i didn't immediately grok it - suffice to say it is significantly more elaborate than just '"%f" % num'. But anyway, a loss of precision is a given when dealing with native floating points, hence the warning. Storing monetary values as ints using a known exponent on the app side is the standard way to work around the issue. You'd use a TypeDecorator for this: http://www.sqlalchemy.org/docs/core/types.html?highlight=typedecorator#sqlalchemy.types.TypeDecorator . > > Thanks, > Mark > > -- > You received this message because you are subscribed to the Google Groups > "sqlalchemy" group. > To post to this group, send email to sqlalch...@googlegroups.com. > To unsubscribe from this group, send email to > sqlalchemy+unsubscr...@googlegroups.com. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalch...@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
