I am trying to get all of the rows in table A that do not have a match
in table B. I believe the problem is that I am using a text foreign key,
and for the rows I am looking for the field will still have a value, it
just won't match anything in table B.
To make things confusing the table 'qstatus' is mapped to a class called
'JobInfo', also the qstatus table is a temporary table populated with
values of what is currently running, so there are no foreign keys or
indexes on the table, the qstat_table.c.job_number field is unique but
isn't a primary key as far as PostgreSQL is concerned.
This is what I am trying to do:
SELECT farm.qstatus.* FROM farm.qstatus
LEFT OUTER JOIN farm.job ON qstatus.job_name=job.name
WHERE job IS NULL
ORDER BY qstatus.job_name;
This is what my mapper looks like:
mapper(JobInfo, qstat_table, primary_key=[qstat_table.c.job_number],
properties={
'Chunk':relation(Chunk, lazy=True, uselist=False),
'Job' : relation(Job,
primaryjoin=(qstat_table.c.job_name==job_table.c.name),
foreign_keys=[qstat_table.c.job_name], lazy=True, viewonly=True,
uselist=False)
}, save_on_init=False)
This is my python code:
>>> from farmdb import * #where my mappers live
>>> session=create_session()
>>> db.echo=True
>>>
session.query(JobInfo).outerjoin(JobInfo.Job).filter(JobInfo.Job==None).order_by(JobInfo.job_name).all()
2009-04-06 16:49:26,922 INFO sqlalchemy.engine.base.Engine.0x...0210
SELECT farm.qstatus.state AS farm_qstatus_state, farm.qstatus.job_name
AS farm_qstatus_job_name, farm.qstatus.priority AS
farm_qstatus_priority, farm.qstatus.job_number AS
farm_qstatus_job_number, farm.qstatus.owner AS farm_qstatus_owner,
farm.qstatus.start_time AS farm_qstatus_start_time, farm.qstatus.queue
AS farm_qstatus_queue, farm.qstatus.slots AS farm_qstatus_slots,
farm.qstatus.req_que AS farm_qstatus_req_que, farm.qstatus.s_rt AS
farm_qstatus_s_rt, farm.qstatus.h_rt AS farm_qstatus_h_rt
FROM farm.qstatus LEFT OUTER JOIN farm.job ON farm.qstatus.job_name =
farm.job.name
WHERE farm.qstatus.job_name IS NULL ORDER BY farm.qstatus.job_name
2009-04-06 16:49:26,922 INFO sqlalchemy.engine.base.Engine.0x...0210 {}
[]
--
David Gardner
Pipeline Tools Programmer, "Sid the Science Kid"
Jim Henson Creature Shop
dgard...@creatureshop.com
--~--~---------~--~----~------------~-------~--~----~
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
