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?

Answer 1

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