On 9/15/15 2:47 PM, Katie Wurman wrote:
Ok thanks! Adding a bind_expression to my CastToInteger type ensures
that when Person.id is included in a WHERE clause, the param is cast
to varchar.
This is ok, except now I have the following situation:
class CastToIntegerType(types.TypeDecorator):
impl = types.Numeric
def column_expression(self, col):
return func.cast(col, Integer)
def bind_expression(self,col):
return func.cast(col, String)
I think the "impl" here probably needs to be String, since "impl" is to
represent the actual underlying datatype in the DB.
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.
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]
<javascript:>.
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].
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.
