Re: [sqlalchemy] SQLite / Decimal
On Oct 29, 2010, at 6:18 PM, Michael Bayer 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 > . > > Thank you for the pointer on the type decorator. 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.
Re: [sqlalchemy] SQLite / Decimal
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.
[sqlalchemy] SQLite / Decimal
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. 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.
