how do i take advantage of sqlite manifest typing / type affinity using sqlalchemy?

Question

I like the idea of sqlite's manifest typing / type affinity:

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

Essentially, if I set a column's affinity as 'numeric', it will duck type integers or floats to store them as such, but still allow me to store strings if I want to. Seems to me this is the best 'default' type for a column when i'm not sure ahead of time of what data i want to store in it.

so off i go:

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)

but when i try and store some string values, eg "abc" in the table, sqlalchemy falls over:

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

Boo, hiss. So, is there any way I can convince sqlalchemy to let sqlite do the typing? perhaps i can use a type from sqlalchemy.types instead of sqlachemy.dialects.sqlite?

[edit:] for bonus points: i need to be able to access tables via introspection / reflection. so some kind of way of having this work with meta.reflect() would be great! ;-)

Answer 1

OK, here's what I've come up with:

Define a custom column type, as per http://www.sqlalchemy.org/docs/reference/sqlalchemy/types.html#custom-types

a combination of the documentation and some trial & error have given me this:

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()

The current sqlalchemy dialect type returns to_float in bind_processor, which is why I was getting the errors before. i.m.v.v.h.o., this is a bug.

for my bonus points: manually setting column type to MyDuckType in my metadata.reflect() code:

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

seems to work for me. Any suggestions / improvements?

Answer 2

Essentially, if I set a column's affinity as 'numeric', it will duck type integers or floats to store them as such, but still allow me to store strings if I want to.

If you don't declare a column type at all, SQLite will let you store any type without performing conversions. This may be a better choice if you want to distinguish 123 from '123'.