Re: [sqlalchemy] Cast model id column from String to Integer

[Mike Bayer]

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