How should I handle decimal in SQLalchemy & SQLite
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
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.
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.