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()
I get a traceback:
Traceback (most recent call last):
File "<console>", line 1, in ?
File "/Users/jeff/src/web/uuid-tg/lib/python2.4/site-packages/
SQLAlchemy-0.4.6-py2.4.egg/sqlalchemy/util.py", line 1260, in
func_with_warning
return func(*args, **kwargs)
File "/Users/jeff/src/web/uuid-tg/lib/python2.4/site-packages/
SQLAlchemy-0.4.6-py2.4.egg/sqlalchemy/orm/query.py", line 1365, in
execute
return self._select_statement(clauseelement, params, **kwargs)
File "/Users/jeff/src/web/uuid-tg/lib/python2.4/site-packages/
SQLAlchemy-0.4.6-py2.4.egg/sqlalchemy/orm/query.py", line 1382, in
_select_statement
return list(q)
File "/Users/jeff/src/web/uuid-tg/lib/python2.4/site-packages/
SQLAlchemy-0.4.6-py2.4.egg/sqlalchemy/orm/query.py", line 984, in
iterate_instances
process[0](context, row, rows)
File "/Users/jeff/src/web/uuid-tg/lib/python2.4/site-packages/
SQLAlchemy-0.4.6-py2.4.egg/sqlalchemy/orm/query.py", line 1541, in
main
extension=context.extension,
only_load_props=context.only_load_props,
refresh_instance=context.refresh_instance
File "/Users/jeff/src/web/uuid-tg/lib/python2.4/site-packages/
SQLAlchemy-0.4.6-py2.4.egg/sqlalchemy/orm/mapper.py", line 1380, in
_instance
identitykey = self.identity_key_from_row(row)
File "/Users/jeff/src/web/uuid-tg/lib/python2.4/site-packages/
SQLAlchemy-0.4.6-py2.4.egg/sqlalchemy/orm/mapper.py", line 915, in
identity_key_from_row
return (self._identity_class, tuple([row[column] for column in
self.primary_key]), self.entity_name)
File "/Users/jeff/src/web/uuid-tg/lib/python2.4/site-packages/
SQLAlchemy-0.4.6-py2.4.egg/sqlalchemy/engine/base.py", line 1339, in
__getitem__
return self.__parent._get_col(self.__row, key)
File "/Users/jeff/src/web/uuid-tg/lib/python2.4/site-packages/
SQLAlchemy-0.4.6-py2.4.egg/sqlalchemy/engine/base.py", line 1594, in
_get_col
type_, processor, index = self._key_cache[key]
File "/Users/jeff/src/web/uuid-tg/lib/python2.4/site-packages/
SQLAlchemy-0.4.6-py2.4.egg/sqlalchemy/util.py", line 125, in
__getitem__
self[key] = value = self.creator(key)
File "/Users/jeff/src/web/uuid-tg/lib/python2.4/site-packages/
SQLAlchemy-0.4.6-py2.4.egg/sqlalchemy/engine/base.py", line 1491, in
lookup_key
raise exceptions.NoSuchColumnError("Could not locate column in row
for column '%s'" % (str(key)))
NoSuchColumnError: "Could not locate column in row for column
'nis_accounts.id'"
At this point on my traversal of the SQLAlchemy learning curve, I'm
not sure how to construct this query in a manner that I can use it to
return NisAccount objects.
Any help and or guidance will be greatly appreciated.
Thanks,
Jeff.
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
