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