On Apr 28, 2008, at 3:28 PM, Matthew Zwier wrote:
> > Hi all, > > I'm trying to run a (non-ORM) query of the form: > SELECT job_id, pathname FROM jobfilesTable WHERE (job_id, pathname) > NOT IN (SELECT job_id, pathname FROM tempTable) > > After searching the docs and the mailing list, the best I've been able > to come up with is something like: > > from sqlalchemy.sql.expression import _Grouping, ClauseList > > s = select([jobfilesTable.c.job_id, jobfilesTable.c.pathname]) > s2 = select([tempTable.c.job_id, tempTable.c.pathname]) > colgroup = _Grouping(ClauseList(jobfilesTable.c.job_id, > jobfilesTable.c.pathname)) > s = s.where(~colgroup.in_(s2)) > > It seems to generate the appropriate SQL, but I'd prefer not to have > to rely on _Grouping(), as it appears not to be part of the public > API. Any suggestions for a more elegant way of doing this? that's pretty good you came up with that. We haven't placed explicit support for multiple items as the subject of an IN statement. You can do what you have there without _Grouping by saying ClauseList(...).self_group(). I think you're safe with that for now though we should add a "public" function for this purpose at some point. > > P.S. Creating the temporary table seen in select "s2" was a bear, > involving a manual string substitution on a Table.create() bound to an > Engine with strategy="mock" then feeding the result to the DB. Are > there any plans for supporting temporary tables in SA? if its just a matter of saying "CREATE TEMPORARY TABLE" instead of "CREATE TABLE", we can accept a patch for "temporary=True", sure. > > > > --~--~---------~--~----~------------~-------~--~----~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---
