Igor Tandetnik <[EMAIL PROTECTED]> writes:
> Would it be possible to get SQLAlchemy to cast to REAL, FLOAT or DOUBLE
> rather than NUMERIC?
Hello Igor,
Thanks for answering.
>Casting to NUMERIC leaves integers as integers;
> basically, it's a no-op in your query.
Are you sure ?
When I do in my Query1 10/2, it gives 5. Fine
If I do 11/2, it gives 5 and not 5.5. It seems it's a classic problem of
division.
Since i can't see the file that I attached in the first mail, I copy it
below.
Enjoy!!
Thanks again
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=True)
mytable = Table('mytable', metadata,
Column('id', Integer, primary_key=True),
Column('colA', Integer),
Column('colB', Integer),
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.00)#0.75
e8=Mytable(8, 3, 4.01)#~0.75
e9=Mytable(9, 4, 8)#0.5
e10=Mytable(10, 5, 8.0000000001)#~0.625
e11 = Mytable(11, 10, 2)#5
e12=Mytable(12,11, 2)# 5.5
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()
Query0 = session.query(Mytable).all()
print " Printing initial table:"
for row in Query0:
print row
print""
Query1 = session.query(Mytable).add_column(cast(Mytable.colB,Float) /
cast(Mytable.colC,Float)).all()
print " Query1 --> Use of the SQLAlchemy query object"
print "\t--> Adding a column that represents colC values that should be
converted into floats with CAST"
print """ The query translated by SQLAlchemy is:\nSELECT mytable.id AS
mytable_id, mytable."colA" AS "mytable_colA", mytable."colB" AS
"mytable_colB",
mytable."colC" AS "mytable_colC", CAST(mytable."colB" AS NUMERIC(10, 2)) /
CAST(mytable."colC" AS NUMERIC(10, 2)) AS anon_1
FROM mytable ORDER BY mytable.oid"""
for row in Query1:
print row
print"Conclusion: Does not give a correct result (1/10 = 0.1 and not 0
...)\n"
Query2 = session.execute("""SELECT * , CAST(Mytable.colB AS FLOAT) /
CAST(Mytable.colC AS FLOAT)AS CALCUL FROM Mytable""")
print " Query2 --> Direct use of sql statement, ie not using the sqlalchemy
query object"
print "\t--> Adding a column representing the division of colB / colC using
CAST "
print """ The query translated by SQLAlchemy is:\nSELECT * ,
CAST(Mytable.colB
AS FLOAT) / CAST(Mytable.colC AS FLOAT)AS CALCUL FROM Mytable"""
for row in Query2:
print row
print "Conclusion: Correct results. "
session.clear()
session.close()
#########################################################
When run, the program gives:
2008-07-02 18:21:52,296 INFO sqlalchemy.engine.base.Engine.0x..70 PRAGMA
table_info("mytable")
2008-07-02 18:21:52,312 INFO sqlalchemy.engine.base.Engine.0x..70 {}
2008-07-02 18:21:52,328 INFO sqlalchemy.engine.base.Engine.0x..70
CREATE TABLE mytable (
id INTEGER NOT NULL,
"colA" INTEGER,
"colB" INTEGER,
"colC" NUMERIC(10, 2),
PRIMARY KEY (id)
)
2008-07-02 18:21:52,328 INFO sqlalchemy.engine.base.Engine.0x..70 {}
2008-07-02 18:21:52,342 INFO sqlalchemy.engine.base.Engine.0x..70 COMMIT
2008-07-02 18:21:52,358 INFO sqlalchemy.engine.base.Engine.0x..70 BEGIN
2008-07-02 18:21:52,358 INFO sqlalchemy.engine.base.Engine.0x..70 INSERT
INTO
mytable ("colA", "colB", "colC") VALUES (?, ?, ?)
2008-07-02 18:21:52,375 INFO sqlalchemy.engine.base.Engine.0x..70 [0, 0,
0.0]
2008-07-02 18:21:52,375 INFO sqlalchemy.engine.base.Engine.0x..70 INSERT
INTO
mytable ("colA", "colB", "colC") VALUES (?, ?, ?)
2008-07-02 18:21:52,375 INFO sqlalchemy.engine.base.Engine.0x..70 [1, 1,
0.0]
2008-07-02 18:21:52,390 INFO sqlalchemy.engine.base.Engine.0x..70 INSERT
INTO
mytable ("colA", "colB", "colC") VALUES (?, ?, ?)
2008-07-02 18:21:52,390 INFO sqlalchemy.engine.base.Engine.0x..70 [2, 2,
0.0]
2008-07-02 18:21:52,405 INFO sqlalchemy.engine.base.Engine.0x..70 INSERT
INTO
mytable ("colA", "colB", "colC") VALUES (?, ?, ?)
2008-07-02 18:21:52,405 INFO sqlalchemy.engine.base.Engine.0x..70 [3, 0,
10.0]
2008-07-02 18:21:52,421 INFO sqlalchemy.engine.base.Engine.0x..70 INSERT
INTO
mytable ("colA", "colB", "colC") VALUES (?, ?, ?)
2008-07-02 18:21:52,421 INFO sqlalchemy.engine.base.Engine.0x..70 [4, 1,
10.0]
2008-07-02 18:21:52,437 INFO sqlalchemy.engine.base.Engine.0x..70 INSERT
INTO
mytable ("colA", "colB", "colC") VALUES (?, ?, ?)
2008-07-02 18:21:52,437 INFO sqlalchemy.engine.base.Engine.0x..70 [5, 2,
10.0]
2008-07-02 18:21:52,453 INFO sqlalchemy.engine.base.Engine.0x..70 INSERT
INTO
mytable ("colA", "colB", "colC") VALUES (?, ?, ?)
2008-07-02 18:21:52,453 INFO sqlalchemy.engine.base.Engine.0x..70 [6, 2,
4.0]
2008-07-02 18:21:52,467 INFO sqlalchemy.engine.base.Engine.0x..70 INSERT
INTO
mytable ("colA", "colB", "colC") VALUES (?, ?, ?)
2008-07-02 18:21:52,467 INFO sqlalchemy.engine.base.Engine.0x..70 [7, 3,
4.0]
2008-07-02 18:21:52,483 INFO sqlalchemy.engine.base.Engine.0x..70 INSERT
INTO
mytable ("colA", "colB", "colC") VALUES (?, ?, ?)
2008-07-02 18:21:52,483 INFO sqlalchemy.engine.base.Engine.0x..70 [8, 3,
4.0099999999999998]
2008-07-02 18:21:52,500 INFO sqlalchemy.engine.base.Engine.0x..70 INSERT
INTO
mytable ("colA", "colB", "colC") VALUES (?, ?, ?)
2008-07-02 18:21:52,500 INFO sqlalchemy.engine.base.Engine.0x..70 [9, 4,
8.0]
2008-07-02 18:21:52,515 INFO sqlalchemy.engine.base.Engine.0x..70 INSERT
INTO
mytable ("colA", "colB", "colC") VALUES (?, ?, ?)
2008-07-02 18:21:52,515 INFO sqlalchemy.engine.base.Engine.0x..70 [10, 5,
8.0000000001]
2008-07-02 18:21:52,530 INFO sqlalchemy.engine.base.Engine.0x..70 INSERT
INTO
mytable ("colA", "colB", "colC") VALUES (?, ?, ?)
2008-07-02 18:21:52,530 INFO sqlalchemy.engine.base.Engine.0x..70 [11, 10,
2.0]
2008-07-02 18:21:52,546 INFO sqlalchemy.engine.base.Engine.0x..70 INSERT
INTO
mytable ("colA", "colB", "colC") VALUES (?, ?, ?)
2008-07-02 18:21:52,546 INFO sqlalchemy.engine.base.Engine.0x..70 [12, 11,
2.0]
2008-07-02 18:21:52,562 INFO sqlalchemy.engine.base.Engine.0x..70 INSERT
INTO
mytable ("colA", "colB", "colC") VALUES (?, ?, ?)
2008-07-02 18:21:52,562 INFO sqlalchemy.engine.base.Engine.0x..70 [13, 3,
10.0]
2008-07-02 18:21:52,578 INFO sqlalchemy.engine.base.Engine.0x..70 COMMIT
2008-07-02 18:21:52,592 INFO sqlalchemy.engine.base.Engine.0x..70 BEGIN
2008-07-02 18:21:52,592 INFO sqlalchemy.engine.base.Engine.0x..70 SELECT
mytable.id AS mytable_id, mytable."colA" AS "mytable_colA", mytable."colB"
AS
"mytable_colB", mytable."colC" AS "mytable_colC"
FROM mytable ORDER BY mytable.oid
2008-07-02 18:21:52,608 INFO sqlalchemy.engine.base.Engine.0x..70 []
Printing initial table:
<Mytable('0','0', '0')>
<Mytable('1','1', '0')>
<Mytable('2','2', '0')>
<Mytable('3','0', '10')>
<Mytable('4','1', '10')>
<Mytable('5','2', '10')>
<Mytable('6','2', '4')>
<Mytable('7','3', '4.0')>
<Mytable('8','3', '4.01')>
<Mytable('9','4', '8')>
<Mytable('10','5', '8.0000000001')>
<Mytable('11','10', '2')>
<Mytable('12','11', '2')>
<Mytable('13','3', '10')>
2008-07-02 18:21:52,765 INFO sqlalchemy.engine.base.Engine.0x..70 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
NUMERIC(10, 2)) / CAST(mytable."colC" AS NUMERIC(10, 2)) AS anon_1
FROM mytable ORDER BY mytable.oid
2008-07-02 18:21:52,765 INFO sqlalchemy.engine.base.Engine.0x..70 []
Query1 --> Use of the SQLAlchemy query object
--> Adding a column that represents colC values that should be converted
into
floats with CAST
The query translated by SQLAlchemy is:
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 NUMERIC(10, 2)) / CAST(mytable."colC" AS NUMERIC(10,
2))
AS anon_1
FROM mytable ORDER BY mytable.oid
(<Mytable('0','0', '0')>, None)
(<Mytable('1','1', '0')>, None)
(<Mytable('2','2', '0')>, None)
(<Mytable('3','0', '10')>, 0)
(<Mytable('4','1', '10')>, 0)
(<Mytable('5','2', '10')>, 0)
(<Mytable('6','2', '4')>, 0)
(<Mytable('7','3', '4.0')>, 0)
(<Mytable('8','3', '4.01')>, 0.74812967581047385)
(<Mytable('9','4', '8')>, 0)
(<Mytable('10','5', '8.0000000001')>, 0.62499999999218747)
(<Mytable('11','10', '2')>, 5)
(<Mytable('12','11', '2')>, 5)
(<Mytable('13','3', '10')>, 0)
Conclusion: Does not give a correct result (1/10 = 0.1 and not 0 ...)
2008-07-02 18:21:52,937 INFO sqlalchemy.engine.base.Engine.0x..70 SELECT * ,
CAST(Mytable.colB AS FLOAT) / CAST(Mytable.colC AS FLOAT)AS CALCUL FROM
Mytable
2008-07-02 18:21:52,953 INFO sqlalchemy.engine.base.Engine.0x..70 {}
Query2 --> Direct use of sql statement, ie not using the sqlalchemy query
object
--> Adding a column representing the division of colB / colC using CAST
The query translated by SQLAlchemy is:
SELECT * , CAST(Mytable.colB AS FLOAT) / CAST(Mytable.colC AS FLOAT)AS
CALCUL
FROM Mytable
(1, 0, 0, 0, None)
(2, 1, 1, 0, None)
(3, 2, 2, 0, None)
(4, 3, 0, 10, 0.0)
(5, 4, 1, 10, 0.10000000000000001)
(6, 5, 2, 10, 0.20000000000000001)
(7, 6, 2, 4, 0.5)
(8, 7, 3, 4, 0.75)
(9, 8, 3, 4.0099999999999998, 0.74812967581047385)
(10, 9, 4, 8, 0.5)
(11, 10, 5, 8.0000000001, 0.62499999999218747)
(12, 11, 10, 2, 5.0)
(13, 12, 11, 2, 5.5)
(14, 13, 3, 10, 0.29999999999999999)
Conclusion: Correct results.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
