3

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) stored as NUMERIC data type. An SQLAlchemy raw SQL query works fine, but a query using a selectable constructed from reflection fails. The two queries appear to be equivalent.

I have pasted an example below, along with the traceback. Can someone explain what is wrong with the third query in the example?

Set up table with NUMERIC datetime:

import sqlalchemy as sa
from sqlalchemy import text

connString = "sqlite:///c:\\temp\\test.db"
engine = sa.create_engine(connString)
conn = engine.connect()
conn.execute("create table testtable (uid INTEGER Primary Key, datetime NUMERIC)")
conn.execute("insert into testtable values (1, '2017-08-03 01:11:31')")
print(conn.execute('PRAGMA table_info(testtable)').fetchall())
# [(0, 'uid', 'INTEGER', 0, None, 1), (1, 'datetime', 'NUMERIC', 0, None, 0)]

Query with raw SQL works:

resultList1 = conn.execute("SELECT testtable.uid, testtable.datetime \nFROM testtable").fetchall()
print(resultList1)
# [(1, '2017-08-03 01:11:31')]

Query with this selectable works:

resultList2 = conn.execute(sa.sql.select(columns=[text('uid'),text('datetime')]).select_from(text('testtable'))).fetchall() 
print(resultList2)
# [(1, '2017-08-03 01:11:31')]

Query with this selectable fails:

m = sa.MetaData()
table = sa.Table('testtable', m, autoload=True, autoload_with=engine)
selectble = sa.sql.select(table.columns).select_from(table)
print(selectble.compile().string)
#  note: same raw sql query as above
# "SELECT testtable.uid, testtable.datetime \nFROM testtable"

resultList3 = conn.execute(sa.sql.select(table.columns).select_from(table)).fetchall()
# SAWarning: Dialect sqlite+pysqlite does *not* support Decimal objects natively...
print(resultList3)

conn.close()

The error:

Traceback (most recent call last):

  File "<ipython-input-20-188c84a35d95>", line 1, in <module>
    print(resultList3)

  File "c:\program files\python36\lib\site-packages\sqlalchemy\engine\result.py", line 156, in __repr__
    return repr(sql_util._repr_row(self))

  File "c:\program files\python36\lib\site-packages\sqlalchemy\sql\util.py", line 329, in __repr__
    ", ".join(trunc(value) for value in self.row),

TypeError: must be real number, not str
Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127
egold
  • 57
  • 1
  • 7
  • Related: https://stackoverflow.com/questions/35983172/why-does-sqlalchemy-boolean-column-fail-with-typeerror-an-integer-is-required – Ilja Everilä Jan 26 '18 at 21:58

1 Answers1

2

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 REAL1 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)
CREATE TABLE foo (
        bar NUMERIC
)

In [4]: engine.execute("insert into foo values ('not really a number, no')")
Out[4]: <sqlalchemy.engine.result.ResultProxy at 0x7fbcd7ee8f98>

In [5]: foo.select().execute().fetchall()
Out[5]: ---------------------------------------------------------------------------
TypeError                                 Traceback (most recent call last)
  ...
~/Work/SO/lib/python3.6/site-packages/sqlalchemy/sql/util.py in __repr__(self)
    327         trunc = self.trunc
    328         return "(%s%s)" % (
--> 329             ", ".join(trunc(value) for value in self.row),
    330             "," if len(self.row) == 1 else ""
    331         )

TypeError: must be real number, not str

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

Ilja Everilä
  • 50,538
  • 7
  • 126
  • 127
  • Thank you for the answer. The actual SQLite tables with NUMERIC datetimes that I am working with were, in fact, created by SQLAlchemy reflection of Oracle database tables. When the data was selected from Oracle and inserted into a SQLite table, SQLAlchemy automatically converted the datetimes (Oracle DATEs to be precise) into NUMERIC types. So, that leads me to another question: is it possible to force SQLAlchemy to convert a datetime into a SQLite TEXT type? – egold Jan 28 '18 at 17:21
  • If you use the SQLAlchemy provided `DateTime` or `sqlite.DATETIME` etc. types when defining your models, the values are stored using ISO format, which results in TEXT storage class: http://docs.sqlalchemy.org/en/latest/dialects/sqlite.html#date-and-time-types. Even though a DATETIME has NUMERIC affinity. – Ilja Everilä Jan 28 '18 at 19:02