我应该如何在 SQLalchemy & 中处理十进制SQLite

当我将数值列与 SQLite 数据库引擎一起使用时,SQLalchemy 会给我以下警告.

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

SAWarning:方言 sqlite+pysqlite 本身不支持 Decimal 对象

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

我正在尝试找出在 SQLalchemy 中使用 pkgPrice = Column(Numeric(12,2)) 的最佳方法,同时仍在使用 SQLite.

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

这个问题[1] 如何将Python十进制转换为SQLite数字? 展示了一种使用 sqlite3.register_adapter(D, adapt_decimal) 让 SQLite 接收和返回 Decimal,但存储字符串的方法,但我不知道如何深入 SQLAlchemy 核心做这件事呢.类型装饰器看起来是正确的方法,但我还没有理解它们.

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.

有没有人有一个 SQLAlchemy 类型装饰器配方,它将在 SQLAlchemy 模型中包含数字或十进制数,但将它们作为字符串存储在 SQLite 中?

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?

推荐答案

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

相关文章