如何在SQLAlchemy中利用SQLite的清单类型/类型亲和性？

Question

我很喜欢sqlite的类型定义和类型亲和力的想法：

http://www.sqlite.org/datatype3.html

简单来说，如果我把某一列的类型设置为“数字”，那么它会自动识别整数或浮点数并以这些格式存储，但我仍然可以选择存储字符串。如果我不确定要在这列中存储什么数据，这似乎是最好的“默认”类型。

所以我开始尝试：

metadata = MetaData()
new_table = Table(table_name, metadata )
for col_name in column_headings:
    new_table.append_column(Column(col_name, 
                                   sqlite.NUMERIC, #this should duck-type numbers but can handle strings as well
                                   primary_key=col_name in primary_key_columns))
new_table.create(self.engine, checkfirst=False)

但是当我试着在表中存储一些字符串值，比如“abc”时，sqlalchemy就出问题了：

  File "[...]\sqlalchemy\processors.py", line 79, in to_float
    return float(value)
ValueError: invalid literal for float(): abc

真让人失望。那么，有没有办法让我说服sqlalchemy让sqlite来处理类型呢？也许我可以使用sqlalchemy.types中的某种类型，而不是sqlalchemy.dialects.sqlite？

[编辑:] 额外加分：我需要能够通过反射来访问表。所以能让这个和meta.reflect()一起工作的方法会很棒！;-)

Answer 1

简单来说，如果我把某一列的类型设置为“数字”，那么它会自动把整数或小数当作数字来存储，但我仍然可以选择存储字符串。

如果你根本不声明列的类型，SQLite会允许你存储任何类型的数据，而不会进行转换。如果你想区分123和'123'，这样做可能更合适。

Answer 2

好的，我想出了这个方案：

首先，定义一个自定义的列类型，具体可以参考这个链接：http://www.sqlalchemy.org/docs/reference/sqlalchemy/types.html#custom-types

通过查阅文档和一些试验，我得到了以下代码：

class MyDuckType(sqlalchemy.types.TypeDecorator):
    """
    SQLALchemy custom column type, designed to let sqlite handle the typing 
    using 'numeric affinity' which intelligently handles both numbers and strings
    """
    impl = sqlite.NUMERIC

    def bind_processor(self, dialect):
        #function for type coercion during db write
        return None #ie pass value as-is, let sqlite do the typing

    def result_processor(self, dialect, coltype):
        #function for type coercion during db read
        return None #ie pass value as sqlite has stored it, should be ducktyped already

    def process_bind_param(self, value, dialect):
        #any changes to an individual value before store in DN
        return value

    def process_result_value(self, value, dialect):
        #any changes to an individual value after retrieve from DB
        return value

    def copy(self):
        #not quite sure what this is for
        return MyDuckType()

目前的sqlalchemy方言类型在bind_processor中返回to_float，这就是我之前出现错误的原因。根据我的理解，这算是一个bug。

为了获得额外的分数：在我的metadata.reflect()代码中手动将列类型设置为MyDuckType：

def get_database_tables(engine):
    meta = MetaData()
    meta.reflect(bind=engine)
    tables = meta.raw_tables
    for tbl in tables.values():
        for col in tbl.c:
            col.type = MyDuckType()
    return tables

这对我来说似乎有效。你有什么建议或改进的地方吗？