On 9/15/15 4:10 PM, Mike Bayer wrote:
On 9/15/15 2:47 PM, Katie Wurman wrote:
class Person(Base):
__tablename__ = 'person'
id = Column('id_string', CastToIntegerType, primary_key=True)
pets = relationship('Pets',
primaryjoin='foreign(Pets.person_id)==Person.id')
class Pets(Base):
__tablename__ = 'pets'
id = Column('id', Integer, primary_key=True)
person_id = Column('person_id',
Integer, ForeignKey('Person.id'), primary_key=True)
In this case, when I query for person.pets the join condition between
Person and Pets becomes: public.pets.person_id = CAST(%(param_1)s AS
VARCHAR)
pets.person_id is persisted as an Integer, so I would think a
comparison of pets.person_id to an integer value should be using
Integer as the type? Let's check...oh well, it does. It's because
that particular comparison is generated on a column-by-column basis.
That's now bug
https://bitbucket.org/zzzeek/sqlalchemy/issues/3530/bind-param-replacement-in-join_condition.
OK this one is throwing me off, but actually if you really intend for
this mapping to work in every way right now, you have to use CAST, else
the relationship will not work for eager loading. An eager load
renders both columns directly so you'd need CAST in the join condition.
That is, to run both of these queries:
p1 = s.query(Person).first()
p1 = s.query(Person).options(joinedload(Person.pets)).first()
the mapping has to look like this:
pets = relationship('Pets',
primaryjoin="foreign(Pets.person_id) == cast(Person.id, Integer)")
the eager join generates:
FROM person LEFT OUTER JOIN pets AS pets_1 ON pets_1.person_id =
CAST(person.id_string AS INTEGER)
the lazyload generates:
WHERE pets.person_id = CAST(CAST(%(param_1)s AS VARCHAR) AS INTEGER)
some kind of smartness could perhaps figure out that this is a double
CAST. But really, in other cases it might not be clear that "id" and
"id_string" here both deal with the same type on the Python side.
I'd probably stick with this approach for now. I have doubts about both
issues I've created.
So I'd stick a type_coerce in there and be done with it, which won't
generate the CAST either, if that was the problem....except....that
doesn't seem to be working either because of the way type_coerce
doesn't create a permanent construct, so that's bug
https://bitbucket.org/zzzeek/sqlalchemy/issues/3531/bind-param-replacement-in-join_condition.
Workaround now is at the bottom of 3531 which looks like:
from sqlalchemy.ext.compiler import compiles
from sqlalchemy.sql import ColumnElement
from sqlalchemy.sql.elements import _clone
class LateTypeCoerce(ColumnElement):
def __init__(self, element, type_):
self.element = element
self.type = type_
self._proxies = [element]
def self_group(self, against=None):
return
LateTypeCoerce(self.element.self_group(against=against), self.type)
def get_children(self, **kwargs):
return self.element,
def _copy_internals(self, clone=_clone, **kw):
self.element = clone(self.element, **kw)
@property
def _from_objects(self):
return self.element._from_objects
def _make_proxy(self, selectable, **kw):
return self.element._make_proxy(selectable, **kw)
then in mapping:
class Person(Base):
__tablename__ = 'person'
id = Column('id_string', CastToIntegerType, primary_key=True)
pets = relationship('Pets',
primaryjoin=lambda: foreign(Pets.person_id) ==
LateTypeCoerce(Person.id, Integer))
I can amend this by explicitly casting Person.id to Integer in the
primaryjoin expression, but that's a little redundant. Is there a
cleaner way to do this?
Thanks!
On Tuesday, September 15, 2015 at 10:49:51 AM UTC-7, Michael Bayer
wrote:
On 9/15/15 1:19 PM, Katie Wurman wrote:
Hi,
I'm having trouble implementing a model whose 'id' column needs
to be cast to Integer type. Below is the implementation I've got
so far:
class CastToIntegerType(types.TypeDecorator):
'''
Converts stored String values to Integer via CAST operation
'''
impl = types.Numeric
def column_expression(self, col):
return func.cast(col, Integer)
class Person(Base):
__tablename__ = "person"
id = Column('id_string', CastToIntegerType, primary_key=True)
Then when I run the query
>> person = Person.query.get(12345)
I see the following error:
sqlalchemy.exc.ProgrammingError: (ProgrammingError) operator
does not exist: text = integer
LINE 3: WHERE public.person.id_string = 12345
^
HINT: No operator matches the given name and argument type(s).
You might need to add explicit type casts.
Any ideas what I might be doing wrong?
OK, this is close, column_expression applies to how the column
list in the SELECT renders, e.g. "SELECT CAST(mycolumn, INT) as
mycolumn", for a SQL expression that wraps around a value you're
sending into the WHERE in a comparison expression, you'd use
bind_expression for that:
http://docs.sqlalchemy.org/en/rel_1_0/core/type_api.html#sqlalchemy.types.TypeEngine.bind_expression
<http://docs.sqlalchemy.org/en/rel_1_0/core/type_api.html#sqlalchemy.types.TypeEngine.bind_expression>,
and in this case you'd be CASTing to String so that the SQL
renders as "WHERE id_string = CAST(12345, VARCHAR)".
Thanks!
--
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 [email protected] <javascript:>.
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy
<http://groups.google.com/group/sqlalchemy>.
For more options, visit https://groups.google.com/d/optout
<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 [email protected]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
Visit this group at http://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 [email protected]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
Visit this group at http://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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
