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)
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)
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,
>
> 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.
> 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.
