On May 23, 2008, at 7:42 PM, Jeff Putsch wrote:
>
> Howdy,
>
> I'm a newbie to sqlalchemy and am having trouble understanding how to
> turn selects into objects.
>
> I've got two tables mapped into objects like this:
>
> nis_accounts_table = Table( ... )
> nis_users_table = Table( ... )
>
> class NisAccount:
> pass
>
> class NisUser:
> pass
>
> 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)
> },
> order_by = nis_users_table.c.uid
> )
>
> mapper(NisAccount, nis_accounts_table)
>
> Then I define some selects and execute them:
>
> s = select([nis_accounts_table, nis_users_table],
> from_obj=[nis_accounts_table.join(nis_users_table)]).where(
> nis_users_table.c.eid != ''
> )
>
> a1 = s.correlate(None).alias()
> a2 = s.correlate(None).alias()
>
> s2 = select([a2.c.domain_id, a2.c.nis_user_id,
> a2.c.gid, a2.c.gcos, a2.c.shell, a2.c.home,
> a2.c.terminated, a2.c.reassigned_uid, a2.c.active
> ], from_obj=[a1.join(a2, (a1.c.eid == a2.c.eid) &
> (a1.c.uid != a2.c.uid))], use_labels=True)
>
> 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')))
>
> results = s2.where(a1.c.eid.in_(s3) & (a1.c.domain_id ==
> 41)).execute().fetchall()
>
> What I'd really like is for "results" to contain a list of NisAccount
> objects.
>
> If I run the select like this:
>
> NisAccount.query().execute(s2.where(a1.c.eid.in_(s3) & (a1.c.domain_id
> == 41))).fetchall()
execute() is a super ancient method that you should not be using
(that's what all the warnings mean when you call it). Instead, use
Query.from_statement(your statement).all().
The error means that the Query cannot locate one of the necessary
columns for NisAccount in the row. This is fairly easy to occur when
you are overriding Query's own statement compilation (which is what
from_statement() does) and providing it with your own list of
columns. In this case I can see that "s2" does not have any column
corresponding to "nis_accounts.id" in it, so that would be the issue
here.
I would advise trying not to rely upon Query.from_statement() so much
since it is harder to use. In this case you could say something along
the lines of:
query.select_from(a1.join(a2, <onclause>)).filter(a1.c.eid.in_(s3))....
Develop this query iteratively, starting simple with core units of the
desired SQL and working outwards until you get the results you want.
Also I'd recommend giving 0.5 a try which is better suited to this
level of Query shenanigans. With 0.5, you can forego the usage of
select() entirely and use Query to create all the subqueries too. The
tutorial has an example of this (albeit a lot simpler than what you're
trying).
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
