Columns division: cast() doen't seem to work. Or how to use it ?

[Dominique]

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()

[Dominique]

Hi,

Another way to ask the same question:
How can I force SA to take floats into account rather than Numeric ?

Even when columns are declared (in classes or through cast() ) as
floats, SA seems to systematically convert them into Numeric, leading
to the previous question...

I tried making my own type as explained in
http://www.sqlalchemy.org/docs/05/types.html#types_custom, but without
success.

Any help would be much appreciated.
I would really like to improve my level in both python and SA ;-).
Some day, I'll help newbies !

Many thanks in advance

Dominique

[Michael Bayer]

the example program you posted is against SQLite. SQLite doesnt have
"floats" per se, it only has "REAL", and a type "affinity" called
NUMERIC. See http://www.sqlite.org/datatype3.html . So the sqlite
dialect in SA renders NUMERIC for the "Float" type. If you run it on
postgres, you get:

SELECT mytable.id AS mytable_id, mytable."colA" AS "mytable_colA",
mytable."colB" AS "mytable_colB", mytable."colC" AS "mytable_colC",
CAST(mytable."colB" AS FLOAT(10)) / CAST(mytable."colC" AS FLOAT(10))
AS anon_1
FROM mytable ORDER BY mytable.id

and then a division by zero error which seems to be related to the
specific data in use (I haven't looked closely at the data itself in
the example).

[Dominique]

Hello Mike,

Thank you very much for answering.
I have to admit that I don't understand.

session.query(Mytable).add_column(cast(Mytable.colB,Float) /
cast(Mytable.colC,Float)).all()
gives bad results while
session.execute("""SELECT * , CAST(Mytable.colB AS FLOAT) /
CAST(Mytable.colC AS FLOAT)AS CALCUL FROM Mytable""")
gives correct results.

Thanks

Dominique

[Michael Bayer]

SQLite has a separate NUMERIC affinity from REAL, and up til this
point we've only supported NUMERIC. its just a one liner in that doc
which explains a difference. r4889 in the 0.4 branch and r4890 in the
trunk adds a separate SLFloat type with FLOAT as the descriptor. If
your test is against 0.4 it requires a session.clear() to illustrate
identical results to raw SQL so that the integer-based values sent to
each MyTable instance come back as floating point.

[Dominique]

Mike,

I just had a quick look. See further in a moment.

Thank you very much for your time, your work and your help.
I really appreciate

Dominique