Geek Answers Handbook

The problem of using decimal numbers and money in sqlite3 with adapters

Using sqlite3 as a database, I want to save a couple of decimal values. Decimal values, one percent of the other dollar amount. However, I saw problems using the amount (s) for more than 20,000 records. It was a few dollars.

I was thinking about using an adapter to save the amount of cents, and then the units should work.

sqlite3.register_adapter(decimal.Decimal, lambda x:str(x))
sqlite3.register_adapter(decimal.Decimal, lambda x:int(x*100))

However, I now need two classes, since I cannot use the same class. Attempting to subclass Decimal has become a problem because it uses Decimal internally. Good. I just copy decimal.py and replace every Decimal | Decimal Using Money | money.

$ copy decimal.py money.py
$ sed -e "s / Decimal / Money / g" -e "s / decimal / money / g" -i money.py
$ money.py

All unit tests work. I am trying this now and I am getting a "probably unsupported type" error. I am changing around converters, using basic types. I just can't get it to work correctly, and I don't have a better idea for this problem.

I need help and you have a sample below. Ideas on how to make it work or better use standard libraries?

import decimal
import money
import sqlite3

sqlite3.register_adapter(decimal.Decimal, lambda x:str(x))
sqlite3.register_converter('decimal', decimal.Decimal)
sqlite3.register_adapter(money.Money, lambda x: int(value*100))
sqlite3.register_converter('intcents', lambda x: money.Money(x)/100)

conn = sqlite3.connect(":memory:",
        detect_types=sqlite3.PARSE_DECLTYPES|sqlite3.PARSE_COLNAMES)
cursor = conn.cursor()
cursor.executescript("CREATE TABLE example(rate decimal, amount intcents)")

for x in (1,1,1,2,2,2,3,3,3):
    rate = decimal.Decimal(str(x))/100
    amount = money.Money(str(rate))

    try:
        cursor.execute("INSERT INTO example VALUES(?,?)", (rate, amount))
    except:
        print (rate, amount)
        raise

cursor.execute("""SELECT sum(rate), sum(amount) FROM example""")
print cursor.fetchone()
cursor.execute("""SELECT sum(rate) as "t [decimal]", sum(amount)as "a [intcents]"FROM example""")
print cursor.fetchone()
+5
source share
2 answers

register_adapter. , , NameError. . register_adapter register_converter. .

0

, , - - :
1. DB
2. 100
. , .

+2

More articles:


All Articles