I'm trying to create a subselect that has a where condition using an
identically named column as the outer select. I'm not able to figure
out how to tell SQLAlchemy that I need two parameters--one for each
query. Here is what my SQL would look like if I wrote it by hand:
SELECT signals.*
FROM module_outputs
WHERE module_id = :module_id
AND signal_id NOT IN (
SELECT signal_id
FROM module_inputs
WHERE module_id = :module_id)
This is how the joining tables are defined:
self.module_inputs = sqla.Table('module_inputs', metadata,
sqla.Column('module_id',
sqla.Integer,
sqla.ForeignKey('modules.module_id'),
nullable = False),
sqla.Column('signal_id',
sqla.Integer,
sqla.ForeignKey('signals.signal_id'),
nullable = False),
sqla.PrimaryKeyConstraint('module_id', 'signal_id')
)
self.module_outputs = sqla.Table('module_outputs', metadata,
sqla.Column('module_id',
sqla.Integer,
sqla.ForeignKey('modules.module_id'),
nullable = False),
sqla.Column('signal_id',
sqla.Integer,
sqla.ForeignKey('signals.signal_id'),
nullable = False),
sqla.PrimaryKeyConstraint('module_id', 'signal_id')
)
Below is some code that attempts to pass the necessary parameters.
I'd like to place both parameters in the execute() line, but it seems
I may need alias one or both parameters. So far, I'm stumped on what
to and what I'm doing wrong.
db = model.db.tables
# Construct the subselect
not_in_join = sql.join(db.signals, db.module_outputs)
not_in_sel = sql.select([db.signals.c.signal_id],
db.module_outputs.c.module_id == m.module_id,
from_obj=[not_in_join])
# Construct the primary query to return signal_ids
sel = sql.select([db.signals.c.signal_id],
sql.not_(db.signals.c.signal_id.in_(not_in_sel)),
from_obj=[sql.join(db.signals, db.module_inputs)])
# >>> print sel
# SELECT signals.signal_id
# FROM signals
# JOIN module_inputs ON signals.signal_id =
# module_inputs.signal_id
# WHERE signals.signal_id NOT IN (
# SELECT signals.signal_id AS signal_id
# FROM signals
# JOIN module_outputs ON signals.signal_id =
# module_outputs.signal_id
# WHERE module_outputs.module_id = ?)
# The above generated query would be fine if execute() would work.
id_res = sel.execute(module_id = m.module_id)
# This does not add an extra
# AND module_inputs.module_id = ?
sel = sql.select([db.signals.c.signal_id],
sql.and_(sql.not_(db.signals.c.signal_id.in_(not_in_sel)),
db.module_inputs.c.module_id == m.module_id),
from_obj=[sql.join(db.signals, db.module_inputs)])
# The above doesn't work either
--~--~---------~--~----~------------~-------~--~----~
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
-~----------~----~----~----~------~----~------~--~---
