[sqlalchemy] Re: Columns division: cast() doen't seem to work. Or how to use it ?
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 --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Columns division: cast() doen't seem to work. Or how to use it ?
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 --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Columns division: cast() doen't seem to work. Or how to use it ?
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 --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Columns division: cast() doen't seem to work. Or how to use it ?
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.0 all result in 0
Only in this case 1 / 2.01 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.1)#0.75
e8=Mytable(8, 3, 8.1)#0.375
e9=Mytable(9, 4, 8)#0.5
e10=Mytable(10, 5, 8.01)#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
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Divide columns with possible zeroDivisionError
I just want to add some significant fact: it really seems that the ordering is not correct only because the result of the columns division is (or at least should ) a float between 0 and 1. If I put figures that give a result to 1, it seems worling fine (ie 10 / 2 -- 5, 12 / 2 -- 6 will be correctly ordered) If this can give you hints to help me... Thanks Dominique --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Divide columns with possible zeroDivisionError
For those who may get stuck on this kind of things in the future:
This works fine:
session.execute(SELECT * ,(CASE WHEN Mytable.ColC = :i THEN :i WHEN
Mytable.ColC :i THEN CAST(Mytable.ColB AS FLOAT) / Mytable.ColC END)
AS Calcul FROM Mytable ORDER BY Calcul, {'i':0})
Thanks
Dominique
--~--~-~--~~~---~--~~
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
-~--~~~~--~~--~--~---
[sqlalchemy] Divide columns with possible zeroDivisionError
Hello, A beginner question: I have a table with 4 columns: id, colA, colB, colC. I want to order (and make other operations as well ) the table asc or desc using the result of colB / colC knowing that colB and colC may equal to 0. When I try using query, the returned results are not correctly ordered: session.query(Mytable).order_by(asc(Mytable.colB / Mytable.colC)).all() I guess that the zero present in colB and colC may also interfere Is it possible to do this with queries with SA 0.4.6 ? Should I use select() and how or is it preferable to go for SA 0.5 ? How should I do to get what I want ? Additional question: I ordered Essential SqlAlchemy on the French Amazon, but haven't received it yet (may be still on print ?). Does anybody know if it has been issued already in the US ? Many thanks in advance for helping me Dominique --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Cannot abort wxPython thread with SQLAlchemy
Thanks Peter for your answer. On 11 juin, 16:16, Peter Hansen [EMAIL PROTECTED] wrote: Aside from that, you don't have many options. What about changing the query so that it will return its results in increments, rather than all at once? If it's a long-running query but you can break it up that way, then the check event flag approach you're using would be able to work. -Peter That's exactly what I am going to do. Many thanks for your help Dominique --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Cannot abort wxPython thread with SQLAlchemy
Hello All, I am using delayedresult (which is a class to do threading in wxPython) for a query with SQLAlchemy, using SQLite. I have an 'opened' session in the main App thread. I create another session under the delayedresult thread. When I try to stop this thread with a dedicated button, the thread doesn't abort and goes on till it sends the result. Does anybody knows how to tackle this issue ? Should I close the first session under the main App ? Something else ? Many thanks in advance for any hints Dominique --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Cannot abort wxPython thread with SQLAlchemy
Hi Peter, Thank you very much for answering. On 10 juin, 02:38, Peter Hansen [EMAIL PROTECTED] wrote: As Python has no way to actually terminate a thread, can you explain what you mean by stop this thread? Are you simply cloning the code from the wxPython example, with the delayedresult.AbortEvent() object, and calling .set() on it? That's exactly what I do. My Abort button is linked to an abort function which calls abortEvent.set(), like in the demo. In the producer function, I launch the query. What I'd like to do is to be able to stop the thread, while the query is being done. Is it possible or am I trying to do something impossible ? Thanks Dominique --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
