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()
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
