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+unsubscr...@googlegroups.com
<mailto:sqlalchemy+unsubscr...@googlegroups.com>.
To post to this group, send email to sqlalchemy@googlegroups.com
<mailto:sqlalchemy@googlegroups.com>.
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.
