Hi,

With direct sql statement, dividing 2 columns with CAST provide a good result (--> 1/2 = 0.5 and not 0). """SELECT * , CAST(Mytable.colB AS FLOAT) / CAST(Mytable.colC AS FLOAT) AS CALCUL FROM Mytable"""

When using a SA query with add_column, the result is not correct whether we use cast() or not (and seems equivalent to the direct sql query without CAST). In that case, results of the division is erroneous: 1/2 = 0 and not 0.5, no matter you use cast or not. Query with cast(): session.query(Mytable).add_column(cast(Mytable.colB,Float) / cast(Mytable.colC,Float)).all() Direct sql: sql = """SELECT *, (Mytable.colB / Mytable.colC) AS CALCUL FROM Mytable"""

Even if the figures are floats, it doesn't seem to use or consider them as floats for the division. 1 / 2 = 1 / 2.0 = 1 / 2.00000 all result in 0 Only in this case 1 / 2.000001 will it work.

Run the attached snippet to check the example. Everything works just like the classic division in C or python , when not using from __future__ import division. Results are the same whether you use this statement or not.

Can someone tell me if I'm missing something and in this case how to write the SA query. Or is the cast() function not correctly used or working in certain cases ?

Thanks in advance for your answer Dominique

#! /usr/bin/env python # -*- coding: utf-8 -*- #from __future__ import division

from sqlalchemy import * from sqlalchemy.orm import * from sqlalchemy.sql import * import time

metadata = MetaData() engine = create_engine('sqlite:///:memory:', encoding = 'utf8', echo=False)

mytable = Table('mytable', metadata, Column('id', Integer, primary_key=True), Column('colA', Float), Column('colB', Float), Column('colC', Float) )

class Mytable(object): def __init__(self, colA, colB, colC): self.colA = colA self.colB = colB self.colC = colC

def __repr__(self): return "<Mytable('%s','%s', '%s')>" % (self.colA, self.colB, self.colC)

metadata.create_all(engine) mapper(Mytable, mytable) e0=Mytable(0, 0, 0) e1=Mytable(1, 1, 0) e2=Mytable(2, 2, 0) e3=Mytable(3, 0, 10)#0 e4=Mytable(4, 1, 10)#0.1 e5=Mytable(5, 2, 10)#0.2 e6=Mytable(6, 2, 4)#0.5 e7=Mytable(7, 3, 4.000000001)#0.75 e8=Mytable(8, 3, 8.000000001)#0.375 e9=Mytable(9, 4, 8)#0.5 e10=Mytable(10, 5, 8.0000000001)#0.625 e11 = Mytable(11, 11, 10)#1.1 e12=Mytable(12,10,10)#1 e13=Mytable(13, 3,10)#0.3

Session = sessionmaker(bind=engine, autoflush=True, transactional=True) session = Session() for i in [e0,e1,e2,e3,e4,e5,e6,e7,e8,e9,e10,e11,e12,e13]: session.save(i) session.commit()

mycase = cast(Mytable.colB,Float) / cast(Mytable.colC,Float) Query1 = session.query(Mytable).add_column(mycase).all() print "Query1 = ",Query1 for row in Query1: print row

sql = """SELECT *, (Mytable.colB / Mytable.colC) AS CALCUL FROM Mytable""" sql2 = """SELECT * , CAST(Mytable.colB AS FLOAT) / CAST(Mytable.colC AS FLOAT)AS CALCUL FROM Mytable""" Query2 = session.execute(sql) print "Query2 = ",Query2 for row in Query2: print row

session.clear() session.close()