def test_selects():
from dbcook import expression
session = sqlalchemy.orm.create_session()
empl_table = mybuild.tables[ Employee]
######## simple
age = 40
print '====== all Employee with age<=', age, ', plain SA'
prn( session.query( Employee).filter( empl_table.c.age <= age).all() )
print ' --- same, via expression'
prn( expression.query1( lambda self: self.age <= age, klas=Employee, session=session) )
expression._debug = 'dbg' in sys.argv
######## 1 level self-ref
age = 40
print '====== all Employee with manager of age<=', age, ', plain SA'
mgr = empl_table.alias()
prn( session.query( Employee).filter(
(mgr.c.age <= age)
& (empl_table.c.manager_id == mgr.c.db_id) ).all() )
print ' --- same, via expression/equivalent'
prn( expression.query1(
lambda self, selfm=Employee: (self.manager == selfm.db_id) & (selfm.age <= age),
klas=Employee, session=session) )
print ' --- same, via expression/automatic'
prn( expression.query1(
lambda self: self.manager.age <= age,
klas=Employee, session=session) )
######## multilevel self-ref
mgr2 = empl_table.alias()
print '====== multilevel: all Employee with manager with manager of age >', age, 'plain SA'
prn( session.query( Employee).filter(
(mgr2.c.age > age)
& (mgr.c.manager_id == mgr2.c.db_id)
& (empl_table.c.manager_id == mgr.c.db_id) ).all() )
print ' --- same, via expression/automatic'
prn( expression.query1(
lambda self: self.manager.manager.age > age,
klas=Employee, session=session) )
print ' --- 2-klas, via expression/automatic - ok if Work owns .name'
prn( expression.query1(
lambda self, ot=Work: (ot.name == 'dig') & (self.db_id == ot.assignee),
klas=Employee, session=session) )
e2.machine = 'deep'
print 22222222222
e2.a.x = ax = 'ewq'
e2.a_y = ay = 12
e2.b.x = 'eet'
e2.b.y = by = 123
assert e2.a.x == e2.a_x == ax
assert e2.b.y == e2.b_y == by
session = sqlalchemy.orm.create_session()
for a in locals().values(): #anything off Base, go to db
if isinstance( a, Base): session.save( a)
session.flush()
session.clear()
def prn(q):
print q
for x in q: print ' ', x
print
print ' --- xxxxxxxxxxxxxx'
print Engineer.a.x
prn( session.query( Engineer).filter( Engineer.a.x < 6 ) )
prn( expression.query1( lambda self: self.a.y > 5, klas=Engineer, session=session) )
prn( session.query( Engineer).filter( Engineer.a == sqlalchemy.orm.aliased(Engineer).b ) )
prn( session.query( Engineer).filter( Engineer.a == ( 6,4) ) )
prn( session.query( Engineer).filter( Engineer.a == dict( x= 6, y=4) ) )
# vim:ts=4:sw=4:expandtab
from dbcook import expression
age= 30
print '--------------------, all Employee with lover.age<',age
# XXX XXX
## - self=Engineer works, self=Employee ->wrong clause
## - where clause gives wrong results??? age=30 ->no items; age=100 -> 2 items incl. popo None ????
def myquery( self =Engineer, min_age =20, max_age =age ):
# return self.age < max_age
return self.lover.age < max_age
# return self.lover.age.between( min_age, max_age)
# return self.age.between( min_age, max_age)
# return (self.lover.age >= min_age) #& (self.lover.age <= max_age)
# return (self.age >= min_age)
expression._debug=1
#expr-query
r = expression.query1( myquery, klas=Employee, session=session)
for a in r: print ' ', a.name, '->', a.lover#.name, a.lover.age
if 1:
print '------, same, plain select'
ee = sa.tables[Employee]
aa = ee.alias()
q = ee.select(
(aa.c.db_id == ee.c.lover_id) &
(aa.c.age < age) ).execute()
for a in q: print ' ', a.name, a.lover_id
# vim:ts=4:sw=4:expandtab
def test_selects():
from dbcook import expression
session = sqlalchemy.orm.create_session()
empl_table = mybuild.tables[Employee]
######## simple
age = 40
print '====== all Employee with age<=', age, ', plain SA'
prn(session.query(Employee).filter(empl_table.c.age <= age).all())
print ' --- same, via expression'
prn(
expression.query1(lambda self: self.age <= age,
klas=Employee,
session=session))
expression._debug = 'dbg' in sys.argv
######## 1 level self-ref
age = 40
print '====== all Employee with manager of age<=', age, ', plain SA'
mgr = empl_table.alias()
prn(
session.query(Employee).filter(
(mgr.c.age <= age)
& (empl_table.c.manager_id == mgr.c.db_id)).all())
print ' --- same, via expression/equivalent'
prn(
expression.query1(lambda self, selfm=Employee:
(self.manager == selfm.db_id) & (selfm.age <= age),
klas=Employee,
session=session))
print ' --- same, via expression/automatic'
prn(
expression.query1(lambda self: self.manager.age <= age,
klas=Employee,
session=session))
######## multilevel self-ref
mgr2 = empl_table.alias()
print '====== multilevel: all Employee with manager with manager of age >', age, 'plain SA'
prn(
session.query(Employee).filter(
(mgr2.c.age > age)
& (mgr.c.manager_id == mgr2.c.db_id)
& (empl_table.c.manager_id == mgr.c.db_id)).all())
print ' --- same, via expression/automatic'
prn(
expression.query1(lambda self: self.manager.manager.age > age,
klas=Employee,
session=session))
print ' --- 2-klas, via expression/automatic - ok if Work owns .name'
prn(
expression.query1(lambda self, ot=Work:
(ot.name == 'dig') & (self.db_id == ot.assignee),
klas=Employee,
session=session))