On Aug 22, 2011, at 12:58 AM, Shantanu Pavgi wrote:
>
> Hi,
>
> I am using SQLAlchemy 0.5.6 with PostgreSQL 8.4 database. The database
> contains two tables Job and User with user_id as a foreign key in the Job
> table. I am trying to do a join-query between these two tables as follows:
>
> {{{
> ... <snip>
>
> Job = Table('job', metadata, autoload=True)
> User = Table('user', metadata, autoload=True)
>
> # for each line in the job id file - find id, time and corresponding user's
> email
> for line in f:
> for j in session.query(Job,User).filter(Job.c.job_runner_external_id
> == line.rstrip).filter(Job.c.user_id==User.c.id):
> print j.id, j.job_runner_external_id, j.create_time,
> j.update_time, j.email
>
>
> </snip>...
> }}}
>
> I am trying to filter results that match particular job_runner_external_id
> and join Job and User tables based user_id foreign key in Job table. The Job
> and User tables have following column names common - id, create_time and
> update_time. The returning query results contain id , create_time and
> update_time from User table and not the Job table.
querying from a full entity like query(Job) always prefixes the column names
with that of the table name so that conflicts aren't possible. The above
query is against (Job, User) so you'd be getting back a two-tuple.
>
> I tried specifying two temporary row-tuple variables (not sure whether that's
> correct way to describe it) in the for loop as shown below, but that didn't
> work:
> {{{
> for j, u in
> session.query(Job,User).filter(Job.c.job_runner_external_id ==
> line.rstrip).filter(Job.c.user_id==User.c.id):
>
> }}}
the above is the correct form.
>
> {{{
> Traceback (most recent call last):
> File "./query_sge_killed_jobs_adv.py", line 42, in ?
> for j,u in session.query(Job,User).filter(Job.c.job_runner_external_id ==
> line.rstrip).filter(Job.c.user_id==User.c.id):
> ValueError: too many values to unpack
> }}}
>
> Am I missing some really basic concept here? Any help?
that error as a result of the calling form shown makes no sense - query(A, B)
definitely returns a 2-tuple. Attached is a demonstration of querying two
entities which works fine in 0.5.6:
from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
# needed in 0.5 to use Session directly
from sqlalchemy.orm.session import Session
Base = declarative_base()
class A(Base):
__tablename__ = 'a'
id = Column(Integer, primary_key=True)
class B(Base):
__tablename__ = 'b'
id = Column(Integer, primary_key=True)
e = create_engine('sqlite://')
Base.metadata.create_all(e)
s = Session(e)
s.add_all([
A(id=1),
B(id=1),
A(id=2),
B(id=2)
])
s.commit()
for a, b in s.query(A, B).filter(A.id==B.id):
print a, b
output:
<__main__.A object at 0x1351f10> <__main__.B object at 0x1351df0>
<__main__.A object at 0x1351e90> <__main__.B object at 0x1351ef0>
--
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.
