Cast model id column from String to Integer

[Katie Wurman]

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? 

Thanks!

[Mike Bayer]

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 sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[Katie Wurman]

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!

[Mike Bayer]

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

[Mike Bayer]

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.

[Mike Bayer]

OK, new day, new perspectives. This is the best way to do the mapping /
type:

class CastToIntegerType(TypeDecorator):
impl = String

def column_expression(self, col):
return cast(col, Integer)

def process_bind_param(self, value, dialect):
return str(value)

class Person(Base):
__tablename__ = 'person'
id = Column('id_string', CastToIntegerType, primary_key=True)

pets = relationship(
'Pets',

primaryjoin='foreign(Pets.person_id)==cast(Person.id, Integer)')



we keep the Python conversion of int to string in Python, then cast in
the primaryjoin. That way lazyload gives us:

SELECT pets.id AS pets_id, pets.person_id AS pets_person_id
FROM pets
WHERE pets.person_id = CAST(%(param_1)s AS INTEGER)
2015-09-16 14:37:00,449 INFO sqlalchemy.engine.base.Engine {'param_1': '5'}

joinedload gives us:

SELECT CAST(anon_1.person_id_string AS INTEGER) AS
anon_1_person_id_string, pets_1.id AS pets_1_id, pets_1.person_id AS
pets_1_person_id
FROM (SELECT person.id_string AS person_id_string
FROM person
LIMIT %(param_1)s) AS anon_1 LEFT OUTER JOIN pets AS pets_1 ON
pets_1.person_id = CAST(anon_1.person_id_string AS INTEGER)


so no double cast, and also the fact that we have string coersion still
happening in the lazyload is appropriate, as the type converters take
place the same way as they would in the joined load.

[Katie Wurman]

This is a perfectly usable solution. Thanks so much!