On May 24, 2008, at 7:00 AM, Michael Bayer wrote:
> On May 24, 2008, at 12:25 AM, Jeff Putsch wrote:
>
>> Now, when I add the filter like this:
>>
>> print NisAccount.query().select_from(a1.join(a2, (a1.c.eid ==
>> a2.c.eid) & (a1.c.uid != a2.c.uid))).filter(a1.c.eid.in_(s3) &
>> (a1.c.domain_id == 41)).compile()
>>
>> I get an infinite recursion error.
>
> 0.5 may very well have the same issue. try closing in the join using
> an alias, like a.join(a2, ...).select().alias().
Thanks, that seems to do the trick.
Doing the join like this: a.join(a2, ...).select(use_labels=True) also
gets rid of the infinite recursion error.
> send along a script that includes sample versions of the Table's in
> question so I can reproduce the same conditions locally.
I've attached a script that demonstrates the problem.
Jeff.
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
from sqlalchemy import *
from sqlalchemy.orm import *
engine = create_engine('sqlite:///:memory:', echo=False)
conn = engine.connect()
metadata = MetaData()
Session = sessionmaker(bind=engine, autoflush=True, transactional=True)
session = Session()
domains_table = Table('domains', metadata,
Column('id', Integer, primary_key = True),
Column('name', String(40), nullable = False, unique = True),
Column('master', String(128), nullable = False),
)
nis_users_table = Table('nis_users', metadata,
Column('id', Integer, primary_key = True),
Column('eid', String(32), default=""),
Column('uid', Integer, nullable = False),
Column('uname', String(256), nullable = False),
UniqueConstraint('eid', 'uid', 'uname')
)
nis_accounts_table = Table('nis_accounts', metadata,
Column('id', Integer, primary_key = True),
Column('domain_id', Integer, ForeignKey('domains.id')),
Column('nis_user_id', Integer, nullable = False),
Column('gid', Integer, default=60001),
Column('gcos', String(256)),
Column('shell', String(256)),
Column('home', String(256)),
Column('terminated', Boolean, default = False),
Column('reassigned_uid', Boolean, default = False),
Column('active', Boolean, default = True),
UniqueConstraint('domain_id', 'nis_user_id'),
ForeignKeyConstraint(['nis_user_id'],['nis_users.id'], ondelete="CASCADE")
)
metadata.create_all(engine)
conn.execute(domains_table.insert(), [
{'id':40, 'name':'dom_40', 'master':'dom_40.example.com'},
{'id':41, 'name':'dom_41', 'master':'dom_41.example.com'},
{'id':54, 'name':'dom_54', 'master':'dom_54.example.com'},
{'id':55, 'name':'dom_55', 'master':'dom_55.example.com'},
])
conn.execute(nis_users_table.insert(), [
{'id':2308, 'eid':'E14093', 'uid':24093, 'uname':'uname_2308', },
{'id':2307, 'eid':'E12337', 'uid':12337, 'uname':'uname_2307', },
{'id':6643, 'eid':'E017783', 'uid':17783, 'uname':'uname_6643', },
{'id':4677, 'eid':'E017607', 'uid':1405, 'uname':'uname_4677', },
{'id':4277, 'eid':'E018290', 'uid':14291, 'uname':'uname_4277', },
{'id':4699, 'eid':'E015679', 'uid':1276, 'uname':'uname_4699', },
{'id':5250, 'eid':'E015318', 'uid':3144, 'uname':'uname_5250', },
{'id':4536, 'eid':'E002616', 'uid':692, 'uname':'uname_4536', },
{'id':7135, 'eid':'E003510', 'uid':43510, 'uname':'uname_7135', },
{'id':4561, 'eid':'E003657', 'uid':10019, 'uname':'uname_4561', },
{'id':2309, 'eid':'E14093', 'uid':14093, 'uname':'uname_2309', },
{'id':2306, 'eid':'E13678', 'uid':23678, 'uname':'uname_2306', },
{'id':5010, 'eid':'E016126', 'uid':1561, 'uname':'uname_5010', },
{'id':4508, 'eid':'E018290', 'uid':768, 'uname':'uname_4508', },
{'id':5075, 'eid':'E016126', 'uid':1322, 'uname':'uname_5075', },
{'id':4871, 'eid':'E003657', 'uid':936, 'uname':'uname_4871', },
{'id':2334, 'eid':'E13678', 'uid':13678, 'uname':'uname_2334', },
{'id':5258, 'eid':'E003510', 'uid':901, 'uname':'uname_5258', },
{'id':4947, 'eid':'E015679', 'uid':723, 'uname':'uname_4947', },
{'id':4626, 'eid':'E002616', 'uid':513, 'uname':'uname_4626', },
{'id':4361, 'eid':'E017783', 'uid':1406, 'uname':'uname_4361', },
{'id':2289, 'eid':'E12337', 'uid':65037, 'uname':'uname_2289', },
{'id':4991, 'eid':'E015318', 'uid':1237, 'uname':'uname_4991', },
{'id':7241, 'eid':'E017607', 'uid':17607, 'uname':'uname_7241', },
]);
conn.execute(nis_accounts_table.insert(), [
{'id':8259, 'nis_user_id':4561, 'active':1, 'reassigned_uid':'', 'domain_id':41, 'shell':'/bin/csh', 'home':'/home/uname_4561', 'gid':20, 'gcos':'Nis User 8259;E003657', },
{'id':12395, 'nis_user_id':7241, 'active':1, 'reassigned_uid':'', 'domain_id':40, 'shell':'/bin/csh', 'home':'/home/uname_7241', 'gid':4402, 'gcos':'Nis User 12395;E017607', },
{'id':8910, 'nis_user_id':5075, 'active':1, 'reassigned_uid':'', 'domain_id':41, 'shell':'/bin/csh', 'home':'/home/uname_5075', 'gid':20, 'gcos':'Nis User 8910;E016126', },
{'id':4406, 'nis_user_id':2334, 'active':1, 'reassigned_uid':'', 'domain_id':54, 'shell':'/bin/csh', 'home':'/home/uname_2334', 'gid':7600, 'gcos':'Nis User 4406;E13678', },
{'id':8805, 'nis_user_id':4991, 'active':1, 'reassigned_uid':'', 'domain_id':41, 'shell':'/bin/csh', 'home':'/home/uname_4991', 'gid':20, 'gcos':'Nis User 8805;E015318', },
{'id':8193, 'nis_user_id':4508, 'active':1, 'reassigned_uid':'', 'domain_id':41, 'shell':'/bin/csh', 'home':'/home/uname_4508', 'gid':20, 'gcos':'Nis User 8193;E018290', },
{'id':5349, 'nis_user_id':2309, 'active':1, 'reassigned_uid':'', 'domain_id':55, 'shell':'/bin/csh', 'home':'/home/uname_2309', 'gid':7600, 'gcos':'Nis User 5349;E14093', },
{'id':8004, 'nis_user_id':4361, 'active':1, 'reassigned_uid':'', 'domain_id':41, 'shell':'/bin/csh', 'home':'/home/uname_4361', 'gid':20, 'gcos':'Nis User 8004;E017783', },
{'id':12273, 'nis_user_id':7135, 'active':1, 'reassigned_uid':'', 'domain_id':40, 'shell':'/bin/csh', 'home':'/home/uname_7135', 'gid':4402, 'gcos':'Nis User 12273;E003510', },
{'id':4369, 'nis_user_id':2308, 'active':1, 'reassigned_uid':'', 'domain_id':54, 'shell':'/bin/csh', 'home':'/home/uname_2308', 'gid':7600, 'gcos':'Nis User 4369;E14093', },
{'id':8227, 'nis_user_id':4536, 'active':1, 'reassigned_uid':'', 'domain_id':41, 'shell':'/bin/csh', 'home':'/home/uname_4536', 'gid':20, 'gcos':'Nis User 8227;E002616', },
{'id':4367, 'nis_user_id':2306, 'active':1, 'reassigned_uid':'', 'domain_id':54, 'shell':'/bin/csh', 'home':'/home/uname_2306', 'gid':7600, 'gcos':'Nis User 4367;E13678', },
{'id':8411, 'nis_user_id':4677, 'active':1, 'reassigned_uid':'', 'domain_id':41, 'shell':'/bin/csh', 'home':'/home/uname_4677', 'gid':20, 'gcos':'Nis User 8411;E017607', },
{'id':9117, 'nis_user_id':5250, 'active':1, 'reassigned_uid':'', 'domain_id':41, 'shell':'/bin/csh', 'home':'/home/uname_5250', 'gid':20, 'gcos':'Nis User 9117;E015318', },
{'id':7899, 'nis_user_id':4277, 'active':1, 'reassigned_uid':'', 'domain_id':41, 'shell':'/bin/csh', 'home':'/home/uname_4277', 'gid':20, 'gcos':'Nis User 7899;E018290', },
{'id':8441, 'nis_user_id':4699, 'active':1, 'reassigned_uid':'', 'domain_id':41, 'shell':'/bin/csh', 'home':'/home/uname_4699', 'gid':20, 'gcos':'Nis User 8441;E015679', },
{'id':4368, 'nis_user_id':2307, 'active':1, 'reassigned_uid':'', 'domain_id':54, 'shell':'/bin/csh', 'home':'/home/uname_2307', 'gid':7600, 'gcos':'Nis User 4368;E12337', },
{'id':8651, 'nis_user_id':4871, 'active':1, 'reassigned_uid':'', 'domain_id':41, 'shell':'/bin/csh', 'home':'/home/uname_4871', 'gid':20, 'gcos':'Nis User 8651;E003657', },
{'id':8827, 'nis_user_id':5010, 'active':1, 'reassigned_uid':'', 'domain_id':41, 'shell':'/bin/csh', 'home':'/home/uname_5010', 'gid':20, 'gcos':'Nis User 8827;E016126', },
{'id':8749, 'nis_user_id':4947, 'active':1, 'reassigned_uid':'', 'domain_id':41, 'shell':'/bin/csh', 'home':'/home/uname_4947', 'gid':20, 'gcos':'Nis User 8749;E015679', },
{'id':9127, 'nis_user_id':5258, 'active':1, 'reassigned_uid':'', 'domain_id':41, 'shell':'/bin/csh', 'home':'/home/uname_5258', 'gid':20, 'gcos':'Nis User 9127;E003510', },
{'id':8346, 'nis_user_id':4626, 'active':1, 'reassigned_uid':'', 'domain_id':41, 'shell':'/bin/csh', 'home':'/home/uname_4626', 'gid':20, 'gcos':'Nis User 8346;E002616', },
{'id':11123, 'nis_user_id':2309, 'active':1, 'reassigned_uid':'', 'domain_id':40, 'shell':'/bin/csh', 'home':'/home/uname_2309', 'gid':7600, 'gcos':'Nis User 11123;E14093', },
{'id':11728, 'nis_user_id':6643, 'active':1, 'reassigned_uid':'', 'domain_id':40, 'shell':'/bin/csh', 'home':'/home/uname_6643', 'gid':4402, 'gcos':'Nis User 11728;E017783', },
{'id':4370, 'nis_user_id':2309, 'active':1, 'reassigned_uid':'', 'domain_id':54, 'shell':'/bin/csh', 'home':'/home/uname_2309', 'gid':7600, 'gcos':'Nis User 4370;E14093', },
{'id':4349, 'nis_user_id':2289, 'active':1, 'reassigned_uid':'', 'domain_id':54, 'shell':'/bin/csh', 'home':'/home/uname_2289', 'gid':7600, 'gcos':'Nis User 4349;E12337', },
]);
class Domain(object):
pass
class NisUser(object):
pass
class NisAccount(object):
pass
mapper(Domain, domains_table, properties = {
'accounts':relation(NisAccount, backref='domain'),
});
mapper(NisUser, nis_users_table, properties = {
'accounts':relation(NisAccount,
primaryjoin=nis_users_table.c.id == nis_accounts_table.c.nis_user_id,
backref='user',
lazy=False)
});
mapper(NisAccount, nis_accounts_table);
# The target query
# select *
# from
# ( select *
# from nis_accounts a1 inner join nis_users u1 on (a1.nis_user_id = u1.id)
# where u1.eid != ''
# ) ndu1
# inner join
# ( select *
# from nis_accounts a2 inner join nis_users u2 on (a2.nis_user_id = u2.id)
# where u2.eid != ''
# ) ndu2
# on (ndu1.eid = ndu2.eid and ndu1.uid != ndu2.uid)
# where ndu1.eid in (
# select eid from nis_users
# group by eid
# having count(uid) > 1 and eid != '' and uname not like '%_old'
# ) and ndu1.domain_id = 41
# What would be ideal is selecting two NisAccount objects...
# nisA_1, nisA_2 = session.query(NisAccount).some_query...
print "=== The 'inner selects' ==="
s = select([nis_accounts_table, nis_users_table],
from_obj=[nis_accounts_table.join(nis_users_table)]).where(
nis_users_table.c.eid != ''
)
print s.compile()
a1 = s.correlate(None).alias()
a2 = s.correlate(None).alias()
# s2 = select([a1, a2], from_obj=[a1.join(a2, (a1.c.eid == a2.c.eid) & (a1.c.uid != a2.c.uid))])
print "=== The set of nis_users objects that have more than one uid for an eid ==="
s3 = select([nis_users_table.c.eid], group_by = nis_users_table.c.eid,
having = (func.count(nis_users_table.c.uid) > 1)
& (nis_users_table.c.eid != '')
& ~(nis_users_table.c.uname.like('%_old'))
)
print s3.compile()
# This is close to what is needed, it joins the two inner selects
# and gets the "on" clause right, but the selected set of columns comes
# from a1 (the left part of the join) and we'd really like all the columns
# or the columns from the right side of the join.
# I'm too new to SQLAlchemy to understand what to do about it.
print "=== Join of two inner selects, no labels, generates a warning ==="
print session.query(NisAccount).select_from(a1.join(a2, (a1.c.eid == a2.c.eid)
& (a1.c.uid != a2.c.uid))).compile()
print len(session.query(NisAccount).select_from(a1.join(a2, (a1.c.eid == a2.c.eid)
& (a1.c.uid != a2.c.uid))).all()), "objects returned"
print "=== Join of two inner selects, with labels, no warning ==="
print session.query(NisAccount).select_from(a1.join(a2, (a1.c.eid == a2.c.eid)
& (a1.c.uid != a2.c.uid)).select(use_labels=True)).compile()
print len(session.query(NisAccount).select_from(a1.join(a2, (a1.c.eid == a2.c.eid)
& (a1.c.uid != a2.c.uid)).select(use_labels=True)).all()), "objects returned"
# Now, lets try adding a filter on "s3" ... N.B. this doesn't work
# (we get maximum recursion depth exceeded.
print "=== Max Recursion error ==="
# print session.query(NisAccount).select_from(a1.join(a2, (a1.c.eid == a2.c.eid)
# & (a1.c.uid != a2.c.uid))).filter(a1.c.eid.in_(s3) &
# (a1.c.domain_id == 41)).compile()
print "=== labels get rid of Max Recursion error ==="
print session.query(NisAccount).select_from(a1.join(a2, (a1.c.eid == a2.c.eid)
& (a1.c.uid != a2.c.uid)).select(use_labels=True)).filter(a1.c.eid.in_(s3) &
(a1.c.domain_id == 41)).compile()
print len(session.query(NisAccount).select_from(a1.join(a2, (a1.c.eid == a2.c.eid)
& (a1.c.uid != a2.c.uid)).select(use_labels=True)).filter(a1.c.eid.in_(s3) &
(a1.c.domain_id == 41)).all()), "objects returned"
# So try, closing the join with an alias:
print "=== Query with Alias get rid of Max Recursin error ==="
print session.query(NisAccount).select_from(a1.join(a2, (a1.c.eid == a2.c.eid)
& (a1.c.uid != a2.c.uid)).select().alias()).compile()
print len(session.query(NisAccount).select_from(a1.join(a2, (a1.c.eid == a2.c.eid)
& (a1.c.uid != a2.c.uid)).select().alias()).all()), "objects returned"
# So far, so good.
# Now add the filter...
print "=== Now filter the results with labels ==="
print session.query(NisAccount).select_from(a1.join(a2, (a1.c.eid == a2.c.eid)
& (a1.c.uid != a2.c.uid)).select(use_labels=True)).filter(a1.c.eid.in_(s3) &
(a1.c.domain_id == 41)).compile()
print session.query(NisAccount).select_from(a1.join(a2, (a1.c.eid == a2.c.eid)
& (a1.c.uid != a2.c.uid)).select(use_labels=True)).filter(a1.c.eid.in_(s3) &
(a1.c.domain_id == 41)).all()
print len(session.query(NisAccount).select_from(a1.join(a2, (a1.c.eid == a2.c.eid)
& (a1.c.uid != a2.c.uid)).select(use_labels=True)).filter(a1.c.eid.in_(s3) &
(a1.c.domain_id == 41)).all())
print "=== Now filter the results with alias ==="
print session.query(NisAccount).select_from(a1.join(a2, (a1.c.eid == a2.c.eid)
& (a1.c.uid != a2.c.uid)).select().alias()).filter(a1.c.eid.in_(s3) &
(a1.c.domain_id == 41)).compile()
# And run the query:
print session.query(NisAccount).select_from(a1.join(a2, (a1.c.eid == a2.c.eid)
& (a1.c.uid != a2.c.uid)).select().alias()).filter(a1.c.eid.in_(s3) &
(a1.c.domain_id == 41)).all()
print len(session.query(NisAccount).select_from(a1.join(a2, (a1.c.eid == a2.c.eid)
& (a1.c.uid != a2.c.uid)).select().alias()).filter(a1.c.eid.in_(s3) &
(a1.c.domain_id == 41)).all())
# the alias() and the "use_label" both seem to be doing the trick!
#
# We're getting the right number of NisAccount objects back an array of (26 objects)
# Is there any way to get back an array of 26 (NisAccount-a1, NisAccount-a2) objects?
#
# Now the question is how to get the all the columns back out of the select, or if not
# all of the columns, then the columns from a2. I suppose since this is an inner join
# then I could use "a2.c.domain_id == 41" int the filter, it should be the equivalent
# to getting the columsns from "a2" returned.
