support for sqlite manifest typing - eg strings in NUMERIC column

[Former user]

sqlite supports manifest typing / type affinity:

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

Essentially, ie a column set as 'numeric' will duck type integers or floats to store them as such, but still allows storing strings.

however, attempting to pass strings fails to a column of type sqlite.NUMERIC fails:

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

As far as I can tell, this is because sqlite.NUMERIC inherits from the generic Numeric class, which assumes number-only values, and therefore specifies to_float as a type coercion function for all reads & writes.

have manually fixed this using a custom column type as per http://www.sqlalchemy.org/docs/reference/sqlalchemy/types.html#custom-types

with bind_processor and result_processor both set to return None instead - this lets sqlite do the typing.

cf http://stackoverflow.com/questions/3044518/how-do-i-take-advantage-of-sqlite-manifest-typing-type-affinity-using-sqlalchem/3046566#3046566

[Mike Bayer]

there's no bug here in my opinion. SQLAlchemy types are cross-platform, so if you have a NUMERIC, you should be sending numbers to it. Automatic type coercion cannot be assumed. Feel free to add your recipe for a SQLite-specific "catchall" type to the wiki at UsageRecipes.

[Mike Bayer]

and I would also add "type-agnostic behavior cannot be assumed either". DBAPIs want values whose Python types match that of what the database is sending/receiving, and that's the default.

[Mike Bayer]

While the sqlite.NUMERIC type is by definition not cross platform, since its imported from the sqlite dialect, the point of uppercase types refers to their behavior in DDL, i.e. you get NUMERIC and not some genericized type name. The Python behavior is still determined by class hierarchy, and it would be inconsistent for some dialect-specific types to produce raw DBAPI behavior while others don't. In this case the main purpose of types.Numeric is to accept and return python Decimal objects, which pysqlite can't do for us in an acceptable fashion, and the vast majority of users would want that behavior maintained even if they use sqlite.NUMERIC.