Sqlalchemy Selects Give Different Results On Sqlite Table, Raw Sql Versus Selectable

While reading SQLite tables using pandas and dask, I came across some unexpected behavior of SQLAlchemy when selecting from SQLite tables with datetimes (ISO formatted strings) sto

Solution 1:

SQLite has a very different type system from most SQL databases: it uses dynamic typing, and after conversion the typename you give a column determines its affinity, such as NUMERIC:

A column with NUMERIC affinity may contain values using all five storage classes. When text data is inserted into a NUMERIC column, the storage class of the text is converted to INTEGER or REAL (in order of preference) if such conversion is lossless and reversible. For conversions between TEXT and REAL storage classes, SQLite considers the conversion to be lossless and reversible if the first 15 significant decimal digits of the number are preserved. If the lossless conversion of TEXT to INTEGER or REAL is not possible then the value is stored using the TEXT storage class. No attempt is made to convert NULL or BLOB values.

Since you've inserted values for which a (lossless) conversion to INTEGER or REAL is not possible, your values use the TEXT storage class, and SQLAlchemy/pysqlite is unhappy since it on the other hand expected values that it can convert to float, which fails.

The typing system causes other similar issues, such as when reflecting the resulting table from a CREATE TABLE ... AS against a SELECT from a table using DATETIME typename, which is converted to NUMERIC affinity.

A shorter code example that demonstrates the issue:

In [2]: foo =Table('foo', metadata, Column('bar', NUMERIC))

In [3]: foo.create(engine)
CREATETABLE foo (
        bar NUMERIC
)

In [4]: engine.execute("insert into foo values ('not really a number, no')")
Out[4]: <sqlalchemy.engine.result.ResultProxy at0x7fbcd7ee8f98>In [5]: foo.select().execute().fetchall()
Out[5]: ---------------------------------------------------------------------------
TypeError                                 Traceback (most recent calllast)
  ...
~/Work/SO/lib/python3.6/site-packages/sqlalchemy/sql/util.py in __repr__(self)
    327         trunc = self.trunc
    328return "(%s%s)" % (
--> 329             ", ".join(trunc(value) for value in self.row),330             "," if len(self.row) ==1else ""
    331         )

TypeError: must be real number, not str

Probably the reason why the sqlite+pysqlite dialect does not support Decimal natively – neither does SQLite