Completely forgot! Thank you, Mike. That works. On Tuesday, 17 May 2016 00:43:07 UTC+3, Mike Bayer wrote: > > > > On 05/16/2016 04:34 PM, Михаил Доронин wrote: > > I've opened issue here > > < > https://bitbucket.org/zzzeek/sqlalchemy/issues/3714/sqlalchemy-core-returns-string-instead-of>, > > > > please read it, it's very brief. > > > > Now Mike Bayer says that it's not a sqlalchemy bug, and says that I can > > use cast to hint to mysql to return datetime.date, but this won't work > > with sqlite which we're trying to use for tests. So it's doesn't solve > > my problem as I really want to use sqlite for tests. > > > > So I got a few questions > > > > Why ``` literal(d, Date)``` isn't working with mysql? > > the DBAPI turns it into a string, and on the MySQL side it is as though > you said: > > "SELECT '2016-01-01'" > > nothing about a date there. A string is sent back, the driver sees > string, you get a string. > > > > I've explicitly > > stated that this column is of type Date. > > your test case did not include any Column objects so there's nothing on > the database side that tells MySQL this is a date. If you are dealing > with regular tables and columns and selecting from those, rather from > free-standing literals which is always an awkward use case due to the > lack of typing information, both backends would work equally well. > > > But mysql still returns > > strings. What is the use of this type parameter in ```literal``` > function? > > that tells SQLAlchemy what datatype to treat this as on the python side > before passing it in. In the case of MySQL Date, this means, "do > nothing, the driver handles it". > > > > > Could someone point me to a sqlite bug report which says that cast isn't > > working correctly with casting string to date? Maybe I can fix this... > > There's no bug that I know of, only that if you go into the sqlite > console, CAST acts in a totally useless, non-SQL way: > > sqlite> select CAST ('2016-06-05' AS DATE); > 2016 > > There's probably some esoteric reason in SQLite's extremely weird typing > system that causes this. The reason you get a date back when you don't > use cast is because for SQLite, SQLAlchemy's Date type actually does a > string->date conversion. SQLite doesn't have a native "date" type > which is probably why the cast acts weird. > > > > > Are there any other workarounds that are actually portable? I mean > > besides changing my own functions logic to deal with corner cases like > > if result is string, than convert it to date etc. > > your test was extremely specific, and is attempting to do a round trip > of a date literal. > > To make that exact thing work you need to build a @compiles recipe using > a form such as that at > > http://docs.sqlalchemy.org/en/rel_1_0/core/compiler.html#utc-timestamp-function > > > from sqlalchemy.ext.compiler import compiles > from sqlalchemy.sql.elements import Cast > > > class MakeADate(Cast): > def __init__(self, elem): > super(MakeADate, self).__init__(elem, Date) > > > @compiles(MakeADate) > def _default_date(elem, compiler, **kw): > return compiler.visit_cast(elem, **kw) > > > @compiles(MakeADate, "sqlite") > def _sqlite_date(elem, compiler, **kw): > return compiler.process(elem.clause, **kw) > > > > full example: > > > from sqlalchemy import ( > create_engine, > Column, > String, > Integer, > Date > ) > from sqlalchemy.ext.declarative import declarative_base > from sqlalchemy.orm import Session > from sqlalchemy.sql import select, cast > from sqlalchemy.sql.expression import union, literal, alias > from itertools import chain > from datetime import date > > Base = declarative_base() > > e1 = create_engine("mysql://scott:tiger@localhost/test", echo=True) > > e2 = create_engine("sqlite://", echo='debug') > > > from sqlalchemy.ext.compiler import compiles > from sqlalchemy.sql.elements import Cast > > > class MakeADate(Cast): > def __init__(self, elem): > super(MakeADate, self).__init__(elem, Date) > > > @compiles(MakeADate) > def _default_date(elem, compiler, **kw): > return compiler.visit_cast(elem, **kw) > > > @compiles(MakeADate, "sqlite") > def _sqlite_date(elem, compiler, **kw): > return compiler.process(elem.clause, **kw) > > > dates = ( > date(2016, 1, 1), > date(2016, 1, 2), > ) > > > for engine in e1, e2: > session = Session(engine) > > selects = tuple(select([MakeADate(d)]) 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 > > > > > > > > > > -- > > You received this message because you are subscribed to the Google > > Groups "sqlalchemy" group. > > To unsubscribe from this group and stop receiving emails from it, send > > an email to sqlalchemy+...@googlegroups.com <javascript:> > > <mailto:sqlalchemy+unsubscr...@googlegroups.com <javascript:>>. > > To post to this group, send email to sqlal...@googlegroups.com > <javascript:> > > <mailto:sqlal...@googlegroups.com <javascript:>>. > > Visit this group at https://groups.google.com/group/sqlalchemy. > > For more options, visit https://groups.google.com/d/optout. >
-- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
