[sqlalchemy] Re: SQL for (col1, col2) NOT IN (SELECT ...)
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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQL for (col1, col2) NOT IN (SELECT ...)
Thanks for the quick reply! I've always been quite impressed with the quality of SA and its support. I'm a bit swamped at work at the moment but I'll see about putting a 'CREATE TEMPORARY TABLE' patch together. MZ On Mon, Apr 28, 2008 at 4:09 PM, Michael Bayer [EMAIL PROTECTED] wrote: 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. 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQL for (col1, col2) NOT IN (SELECT ...)
This could be expanded slightly to include 'prefixes=[]' support ala select() and insert(). Sqlite could use that for creating full text tables, e.g. 'CREATE VIRTUAL TABLE foo (...) USING ...'. I haven't thought about this extensively but I think I'd prefer prefixes=['TEMPORARY'] to a temporary=True flag until such a time as we can guarantee that the temp tables are cleaned up when the defining connection is returned to the pool. Matthew Zwier wrote: Thanks for the quick reply! I've always been quite impressed with the quality of SA and its support. I'm a bit swamped at work at the moment but I'll see about putting a 'CREATE TEMPORARY TABLE' patch together. MZ On Mon, Apr 28, 2008 at 4:09 PM, Michael Bayer [EMAIL PROTECTED] wrote: 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. 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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: SQL for (col1, col2) NOT IN (SELECT ...)
On Apr 28, 2008, at 4:41 PM, jason kirtland wrote: This could be expanded slightly to include 'prefixes=[]' support ala select() and insert(). Sqlite could use that for creating full text tables, e.g. 'CREATE VIRTUAL TABLE foo (...) USING ...'. I haven't thought about this extensively but I think I'd prefer prefixes=['TEMPORARY'] to a temporary=True flag until such a time as we can guarantee that the temp tables are cleaned up when the defining connection is returned to the pool. This is all fine with me... --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
