On 2018-12-14 11:24 AM, Darren Duncan wrote: > > If yours is a financial application then you should be using exact numeric > types only, such as integers that represent multiples of whatever quantum you are using, such as cents; fractional numbers are a display or user input format only, and in those cases they are character strings. >
Thanks, Darren. In principle I agree with you, but I am experimenting with a different approach. My application supports SQL Server and PostgreSQL as well as sqlite3, and those databases do have exact numeric types for monetary use, and I am trying to stick to one code base as much as possible. The python sqlite3 module allows you to convert sqlite3 data to a python object, so my approach is to store decimal data as text in sqlite3, and convert it to a python Decimal object when reading it in. I find that this works ok. I do have a bit of trouble when using sqlite3 to ‘sum’ a column, as it then switches to floating point and can lose precision, but provided I convert the result back to a Decimal object with appropriate rounding it also works. Having said that, I am still testing, and I may decide that I have to bite the bullet and store everything as integers, in which case I will use the same approach for the other databases as well. Simon Slavin says ‘Currency amounts should be stored as integers’. Does this apply to sqlite3 specifically, or is that your recommendation for all databases? Thanks Frank _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
