Hello,
Thanks to both of you.
1) Case combined with null() works fine to order one column by
replacing zero with NULL. -- See Query1 in the attached snippet
Does nullif exist with SA ?
2) When dividing 2 columns, where zeros of the divisor are replaced by
NULL, the order doesn't work. See Query2
I tried to put floats, import future division, ... without success.
May be I am doing something wrong ? Any ideas ?
I am using sqlite (will also investigate sqlite docs)
Thanks in advance
Dominique
#! /usr/bin/env python
# -*- coding: utf-8 -*-
#from __future__ import division
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.sql import *
metadata = MetaData()
engine = create_engine('sqlite:///:memory:', encoding = 'utf8',
echo=True)
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, 0.0)
e1=Mytable(1, 1.0, 0.0)
e2=Mytable(2, 2.0, 0.0)
e3=Mytable(3, 0.0, 10.0)#0
e4=Mytable(4, 1.0, 10.0)#0.1
e5=Mytable(5, 2.0, 10.0)#0.2
e6=Mytable(6, 2.0, 4.0)#0.5
e7=Mytable(7, 3.0, 4.0)#0.75
e8=Mytable(8, 3.0, 8.0)#0.375
e9=Mytable(9, 4.0, 8.0)#0.5
e10=Mytable(10, 5.0, 8.0)#0.625
Session = sessionmaker(bind=engine, autoflush=True,
transactional=True)
session = Session()
for i in [e0,e1,e2,e3,e4,e5,e6,e7,e8,e9,e10]:
session.save(i)
session.commit()
# order should be: 0,1,2,6,7,8,9,10,3,4,5
mycase = case([(Mytable.colC==0,null())],else_=Mytable.colC)
Query1 = session.query(Mytable).order_by(asc(mycase))
print Query1
Query1 = Query1.all()
print Query1
for qq in Query1 :
print qq.colA
print 50*'*'
# order should be: 0,1,2,3,4,5,8,6and9,10,7
mycase2 = (Mytable.colB /
case([(Mytable.colC==0,null())],else_=Mytable.colC))
Query2 = session.query(Mytable).order_by(asc(mycase2))
print Query2
Query2 =Query2.all()
print Query2
for pp in Query2 :
print pp.colA
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 [email protected]
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
-~----------~----~----~----~------~----~------~--~---
