How should I handle decimal in SQLalchemy & SQLite

Question

SQLalchemy gives me the following warning when I use a Numeric column with an SQLite database engine.

SAWarning: Dialect sqlite+pysqlite does not support Decimal objects natively

I'm trying to figure out the best way to have pkgPrice = Column(Numeric(12,2)) in SQLalchemy while still using SQLite.

This question [1] How to convert Python decimal to SQLite numeric? shows a way to use sqlite3.register_adapter(D, adapt_decimal) to have SQLite receive and return Decimal, but store Strings, but I don't know how to dig into the SQLAlchemy core to do this yet. Type Decorators look like the right approach but I don't grok them yet.

Does anyone have a SQLAlchemy Type Decorator Recipe that will have Numeric or Decimal numbers in the SQLAlchemy model, but store them as strings in SQLite?

score 22 · Answer 1 · answered Apr 29 '12 at 09:49

22

Since it looks like you're using decimals for currency values, I'd suggest that you do the safe thing and store the value of currency in its lowest denomination, e.g. 1610 cents instead of 16.10 dollars. Then you can just use an Integer column type.

It might not be the answer you were expecting, but it solves your problem and is generally considered sane design.

answered Apr 29 '12 at 09:49

JosefAssad

4,018
28
37

1

My app is also dealing with stocks & mutual funds, e.g. 123.123456. – adamek May 01 '12 at 22:31
5

Your unit of counting currency doesn't have to be real, it just has to be equal to the smallest subdivision of the entity, be it a stock or mutual fund or chicken costume. e.g. 1231223456 gadagongs, where 1 gadagong gets converted to for example a Decimal type with 6 digits of precision for representation purposes. It's not a bad idea to use the Decimal type for external representation, but it's sane to use integers for the internal representation of currency. – JosefAssad May 02 '12 at 06:49

score 20 · Accepted Answer · answered Apr 30 '12 at 16:15

20

from decimal import Decimal as D
import sqlalchemy.types as types

class SqliteNumeric(types.TypeDecorator):
    impl = types.String
    def load_dialect_impl(self, dialect):
        return dialect.type_descriptor(types.VARCHAR(100))
    def process_bind_param(self, value, dialect):
        return str(value)
    def process_result_value(self, value, dialect):
        return D(value)

# can overwrite the imported type name
# @note: the TypeDecorator does not guarantie the scale and precision.
# you can do this with separate checks
Numeric = SqliteNumeric
class T(Base):
    __tablename__ = 't'
    id = Column(Integer, primary_key=True, nullable=False, unique=True)
    value = Column(Numeric(12, 2), nullable=False)
    #value = Column(SqliteNumeric(12, 2), nullable=False)

    def __init__(self, value):
        self.value = value

answered Apr 30 '12 at 16:15

van

74,297
13
168
171

2

I hope you won't mind if I suggest one small change in you code. :) Instead of just returning a value in process_result_value I'll suggest to make if branch: def process_result_value(self, value, dialect): if value != "None": return D(value) else: return None – Tiho Mar 01 '13 at 15:50
1

@van, Have you tried querying on your new SqliteNumeric columns? The result is not correct as string comparison is totally different than numeric comparison. If you do session.query(T).filter(T.value > 100) you will see. – Jinghui Niu Oct 02 '16 at 19:32
Also, I think the load_dialect_impl part is unnecessary, given that you have already specified impl = types.String – Jinghui Niu Oct 03 '16 at 03:44

zhukailei · Answer 3 · 2019-02-27T09:35:16.603

Here is a solution inspired by both @van and @JosefAssad.

class SqliteDecimal(TypeDecorator):
    # This TypeDecorator use Sqlalchemy Integer as impl. It converts Decimals
    # from Python to Integers which is later stored in Sqlite database.
    impl = Integer

    def __init__(self, scale):
        # It takes a 'scale' parameter, which specifies the number of digits
        # to the right of the decimal point of the number in the column.
        TypeDecorator.__init__(self)
        self.scale = scale
        self.multiplier_int = 10 ** self.scale

    def process_bind_param(self, value, dialect):
        # e.g. value = Column(SqliteDecimal(2)) means a value such as
        # Decimal('12.34') will be converted to 1234 in Sqlite
        if value is not None:
            value = int(Decimal(value) * self.multiplier_int)
        return value

    def process_result_value(self, value, dialect):
        # e.g. Integer 1234 in Sqlite will be converted to Decimal('12.34'),
        # when query takes place.
        if value is not None:
            value = Decimal(value) / self.multiplier_int
        return value

Like @Jinghui Niu mentioned, when decimal is stored as strings in sqlite, some query won't always function as expected, such as session.query(T).filter(T.value > 100), or things like sqlalchemy.sql.expression.func.min, or even order_by, because SQL compares strings (e.g. "9.2" > "19.2" in strings) instead of numerical values as we expected in these cases.

How should I handle decimal in SQLalchemy & SQLite

3 Answers3

Linked