Cast session.query column

[Db]

I have following class

class stockhistory(DeclarativeBase):
    __tablename__ = 'stockhistory'
    __table_args__ = {}
    #column definitions
    shrno = Column(u'shrno', BIGINT(), primary_key=True)
    sprice = Column(u'sprice', FLOAT())
    sstockex = Column(u'sstockex', VARCHAR(length=10))
    ssymbol = Column(u'ssymbol', VARCHAR(length=10))
    svolume = Column(u'svolume', BIGINT())


and a query which works fine and output is later
displayed in an paginated datagrid :

data =  session.query(stockhistory).filter_by(ssymbol = muid).order_by(desc('shrno')).limit(1000).all()

Now I want to improve the query by having the sprice column return
a precision of 2 decimal places.

In raw sql (Firebird) I would do this like :

cast(sprice as decimal(18,2)) as sprice

in sqlalchemy it maybe:

cast(sprice,Numeric(18,2))

but how to tell this to above query eludes me.


Thanks for any ideas.



 

[Michael Bayer]

your options are:

at query time, individually:

session.query(stockhistory, cast(stockhistory.sprice, Numeric(18, 2)).label('sprice_numeric'))

at mapper time:

class stockhistory(..):

    ...

   sprice_numeric = column_property(cast(sprice, Numeric(18, 2)))

using a combination:

class stockhistory(..):

    ...

   sprice_numeric = deferred(cast(sprice, Numeric(18, 2)))

session.query(stockhistory).options(undefer('sprice_numeric'))

http://docs.sqlalchemy.org/en/rel_0_7/orm/mapper_config.html#using-column-property

http://docs.sqlalchemy.org/en/rel_0_7/orm/mapper_config.html#deferred-column-loading

Thanks for any ideas.



 

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/O4O44UlCB8IJ.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.