CODEWITHSUNDEEP
pythonsqlalchemy
Graeme Stuart
Graeme Stuart

Reputation: 6053

How to wrap a column in a CAST operation

I have an MSSQL database with tables which I cannot change and only ever interact with it as read only (SELECT statements). I am using sqlalchemy. What I need to do is to automatically wrap specific columns in CAST() SQL operations for every query. I want to do this at a low level so my code never needs to think about the problem. The reason I am doing this is explained in this question.

My table is something like this:

from sqlalchemy import Column, Integer, Sequence
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class myTable(Base):
    __tablename__ = u'mytable'
    id = Column(Integer, Sequence('table_id_seq'), primary_key=True)
    problem_field = Column(DECIMAL(12, 4), nullable=True)

I have been trying to use a TypeDecorator like this:

from sqlalchemy import Column, Integer, Sequence, types
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql.expression import cast

Base = declarative_base()

class CastToFloatType(types.TypeDecorator):
    '''Converts stored Decimal values to Floats via CAST operation
    '''
    impl = types.Numeric
    def column_expression(self, col):
        return cast(col, Float)

class myTable(Base):
    __tablename__ = u'mytable'
    id = Column(Integer, Sequence('table_id_seq'), primary_key=True)
    wrapped_field = Column(CastToFloatType, nullable=True)

But it doesn't seem to do anything.

Upvotes: 13

Views: 11800

Answers (1)

zzzeek
zzzeek

Reputation: 75127

I think you need to make sure you're on at least version 0.8 of SQLAlchemy, where the column_expression() feature was added. A simple test of your code works on this end:

from sqlalchemy import Column, Integer, Sequence, types, Float
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.sql.expression import cast

Base = declarative_base()

class CastToFloatType(types.TypeDecorator):
    '''Converts stored Decimal values to Floats via CAST operation
    '''
    impl = types.Numeric
    def column_expression(self, col):
        return cast(col, Float)

class myTable(Base):
    __tablename__ = u'mytable'
    id = Column(Integer, Sequence('table_id_seq'), primary_key=True)
    wrapped_field = Column(CastToFloatType, nullable=True)

from sqlalchemy.orm import Session
s = Session()
print s.query(myTable)

output:

SELECT mytable.id AS mytable_id, CAST(mytable.wrapped_field AS FLOAT) AS mytable_wrapped_field 
FROM mytable

Upvotes: 13

Related Questions