DATE/DATETIME/TIME names on SQLite use NUMERIC affinity

[Mike Bayer]

this NUMERIC affinity is bypassed when our ISO date format puts non-numerics in, but if the format is changed to be all digits, it fails. We can consider changing the DDL names on SQLite to DATE_CHAR, DATETIME_CHAR, TIME_CHAR or similar. would need to receive both names within a reflection context:

from sqlalchemy.dialects.sqlite import DATE, DATETIME, TIME
from sqlalchemy.ext.compiler import compiles

# fix by using CHAR_ (or TEXT, whatever)
#@compiles(DATE, 'sqlite')
#@compiles(DATETIME, 'sqlite')
#@compiles(TIME, 'sqlite')
def compile_date(element, compiler, **kw):
    return "CHAR_%s" % element.__class__.__name__

from sqlalchemy import Table, Column, create_engine, MetaData, select
import datetime


m = MetaData()
t = Table(
    'dates', m,
    Column('date', DATE(
        storage_format="%(year)04d%(month)02d%(day)02d",
        regexp=r"(\d{4})(\d{2})(\d{2})",
    )),
    Column('datetime', DATETIME(
        storage_format="%(year)04d%(month)02d%(day)02d%(hour)02d%(minute)02d%(second)02d",
        regexp=r"(\d{4})(\d{2})(\d{2})(\d{2})(\d{2})(\d{2})",
    )),
    Column('time', TIME(
        storage_format="%(hour)02d%(minute)02d%(second)02d",
        regexp=r"(\d{2})(\d{2})(\d{2})",
    ))
)

e = create_engine("sqlite://", echo='debug')
m.create_all(e)

now = datetime.datetime.today().replace(microsecond=0)
nowdate = now.date()
nowtime = now.time()

with e.begin() as conn:
    conn.execute(
        t.insert().values(
            date=nowdate,
            datetime=now,
            time=nowtime
        )
    )
    row = conn.execute(select([t.c.date, t.c.datetime, t.c.time])).first()
    assert row == (nowdate, now, nowtime), "%s %s" % (row, (nowdate, now, nowtime))

[Mike Bayer]

• The SQLite dialect, when using the :class:.sqlite.DATE, :class:.sqlite.TIME, or :class:.sqlite.DATETIME types, and given a storage_format that only renders numbers, will render the types in DDL as DATE_CHAR, TIME_CHAR, and DATETIME_CHAR, so that despite the lack of alpha characters in the values, the column will still deliver the "text affinity". Normally this is not needed, as the textual values within the default storage formats already imply text. fixes #3257

→ <<cset 0ce045bd853e>>