SQLite, CREATE TABLE AS and DATETIME: TypeError: must be real number, not str

[Brecht Machiels]

The following fails with SQLALchemy 1.1.13 and 1.2.0b2. I did not test earlier versions. I'm running CPython 3.6.1 (conda-forge, 64-bit on Windows 7).

from datetime import datetime

from sqlalchemy import create_engine, MetaData, Table, Column, DateTime


engine = create_engine('sqlite:///:memory:')
meta = MetaData(bind=engine)
mytable = Table('mytable', meta,
                Column('dt', DateTime))
meta.create_all()

engine.execute(mytable.insert().values(dt=datetime(1982, 10, 1, 10, 00, 00)))

for row in engine.execute(mytable.select()):
    print(row)


engine.execute('CREATE TABLE mytable2 AS SELECT * FROM mytable')


# (works) using the DBAPI connection
conn = engine.connect()
for row in conn.execute('SELECT * FROM mytable2'):
    print(row)

# (fails) using SQLAlchemy
mytable2 = Table('mytable2', meta, autoload=True)
for row in engine.execute(mytable2.select()):
    print(row)

Stack trace:

#!



C:\Users\bmachie\AppData\Local\Continuum\Miniconda3\envs\ml_irissearch\lib\site-packages\sqlalchemy\sql\sqltypes.py:596: SAWarning: Dialect sqlite+pysqlite does *not* support Decimal objects natively, and SQLAlchemy must convert from floating point - rounding errors and other issues may occur. Please consider storing Decimal numbers as strings or integers on this platform for lossless storage.
  'storage.' % (dialect.name, dialect.driver))
Traceback (most recent call last):
  File "C:/Users/bmachie/.PyCharmCE2017.2/config/scratches/scratch_6.py", line 30, in <module>
    print(row)
  File "C:\Users\bmachie\AppData\Local\Continuum\Miniconda3\envs\ml_irissearch\lib\site-packages\sqlalchemy\engine\result.py", line 156, in __repr__
    return repr(sql_util._repr_row(self))
  File "C:\Users\bmachie\AppData\Local\Continuum\Miniconda3\envs\ml_irissearch\lib\site-packages\sqlalchemy\sql\util.py", line 325, in __repr__
    ", ".join(trunc(value) for value in self.row),
TypeError: must be real number, not str

[Brecht Machiels]

This little hack makes things work:

mytable2.columns['dt'].type = sqlalchemy.sql.sqltypes.DATETIME()

[Mike Bayer]

this cannot be fixed by SQLAlchemy as it is a bug in SQLite:

sqlite> CREATE TABLE mytable (
   ...> dt DATETIME
   ...> );
sqlite> CREATE TABLE mytable2 AS SELECT * FROM mytable;
sqlite> pragma table_info("mytable");
0|dt|DATETIME|0||0
sqlite> pragma table_info("mytable2");
0|dt|NUM|0||0

SQLite is plainly returning the wrong datatype. SQLAlchemy has no way to know you in fact want this to be a DATETIME.

The type of the column can be overridden by expressing the column explicitly within the table or altering the type using the column_reflect event, but I can't see any way SQLAlchemy could recreate information that SQLite is choosing to lose.

[Brecht Machiels]

I see. This is indeed some very strange behavior of SQLite. As described in the documentation, columns with DATETIME entries will have the NUMERIC type affinity. Yet, when creating an empty table, the type affinity can be set to DATETIME (see mytable in the example code).

It's interesting to note that this works if mytable2 is created as a VIEW instead.

[Mike Bayer]

it might work with view. Also when I said this is a "bug in SQLite", i was 99% sure this is intended behavior on their end, so there's unfortunately no chance of them ever changing it. the "bug" is that their typing system is inconsistent and arbitrary.

There's also arguably a bug in SQLAlchemy that the DATETIME type renders the DATETIME name on SQLite, which has numeric affinity yet we plan to store text within it. Also not clear if that can really be fixed either. It still wouldn't fix this issue because we need that DATETIME name to know to apply the date processors to the result.

[Mike Bayer]

no resolution on the SQLAlchemy side, known behavior in SQLite