- changed status to invalid
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.
Comments (4)
-
repo owner -
reporter - attached test_date.py
-
reporter - changed status to open
I've added a test case as I promised. It uses pytest.
-
repo owner - changed status to resolved
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!
- Log in to comment
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!