sqlalchemy core returns string instead of date object in mysql but date object in sqlite

[Михаил Доронин]

    dates_to_check = (
        from_date - timedelta(days=i)
        for i in xrange(1, days_limit + 1)
    )
    selects = tuple(
        select((literal(d).label('td'), literal(d.isoweekday()).label('wd')))
        for d in dates_to_check
    )
    data = union(*selects, use_labels=True).alias()

I'm creating dates like that and then using it this kind of temporary table to join on date and weekday with other tables. After fetching data in sqlite it returns date object to me, but in mysql sqlalchemy return plain string.

I can probably provide test case if needed at monday.

[Mike Bayer]

there is unlikely to be any SQLAlchemy bug here as the date support for MySQL is provided by the DBAPI itself - the SQL needs to return a date object so that the DBAPI can coerce this to Python datetime. would need a complete test case. Reopen when you have one, thanks!

[Михаил Доронин]

I've added a test case as I promised. It uses pytest.

[Mike Bayer]

yes, as I mentioned, MySQL does not automatically turn strings into dates, so because you are sending this value without any type information, it is misunderstood by the driver. Here's the working example:

engine = create_engine("mysql://scott:tiger@localhost/test", echo=True)

dates = (
    date(2016, 1, 1),
    date(2016, 1, 2),
)


session = Session(engine)

selects = tuple(select([cast(d, Date)]) for d in dates)
data = alias(union(*selects, use_labels=True), 'dates')
stmt = select((data,))
result = session.execute(stmt).fetchall()
assert tuple(chain.from_iterable(result)) == dates

unfortunately, the CAST confuses the SQLite driver, causing the value to be incorrectly truncated. this is a bug in SQLite:

sqlite> select cast('2016-01-01' as date);
2016

so on SQLite you'd need to skip the "cast()".

Overall however if you're selecting data from tables then none of this is necessary, the typing information will be already present. the select of a literal is not a commonly needed use case.

For further questions please email the mailing list as there is no bug here thanks!