On Jul 22, 2008, at 2:42 PM, Michael Bayer wrote:
> On Jul 22, 2:23 pm, Philip Semanchuk <[EMAIL PROTECTED]> wrote: >> I'm new to SqlAlchemy. >> >> I'm trying to implement saved searches, like a mail program folder >> that says, "Show me all emails received yesterday", or "All emails >> with 'grail' in the subject". One wrinkle is that my application >> permits user-supplied extensions, so I need to be able to define >> saved >> searches that involve arbitrary objects/tables that I didn't code. >> This is where I think SqlAlchemy can come to my rescue. Extension >> modules will be required to define their own mappers and I'll be able >> to use those maps to query the objects without knowing much about >> them >> in advance. >> >> Assuming I have code that creates a sqlalchemy.orm.query.Query object >> that describes the search I want to save, I can (almost) create a >> saved search via str(the_query.statement). I don't know where to find >> the parameters to that query, though. Can someone point me in the >> right direction? Obviously once I have the SQL + parameters it's not >> hard to save them a table somewhere. >> >> If anyone has done this type of thing before and has a better >> solution, I'm be happy to hear it. Storing SQL in the database seems >> inelegant, but if I'm to support searches on arbitrary objects/ >> tables, >> I don't see a better solution. >> > > > the binds for any SQL expression are present if you say > statement.compile().params. There's a little bit on this in the > tutorial at http://www.sqlalchemy.org/docs/05/sqlexpression.html#sql_insert Got it, thanks. > The downside of storing SQL in the DB is that you're bound to an exact > SQL dialect as well as table structure. A higher level concept of > stored "filters" and such would alleviate that issue but is more > complicated to implement. Might be worth thinking about though. > User- > defined extensions would also have to provide information regarding > their "filters" too. ("user-defined extension" is a vague term so its > not clear at what level these extensions are created, how strict of an > API/sandbox they have, etc). Thanks for your thoughts, and for SqlAlchemy. In our app, SQLite has big advantages over other databases so I don't mind deepening our ties to it. It won't be going away anytime soon. Being bound to a specific table structure is indeed less appealing and that's my main objection to my proposed solution. As you point out, a higher level filter concept would provide a layer of abstraction and insulation against schema changes. To that end, I tried pickling a Query object but it didn't seem to like it ("Can't pickle <class 'sqlalchemy.orm.properties.ColumnComparator'>: it's not found as sqlalchemy.orm.properties.ColumnComparator") which is OK. That was a shot in the dark, and I'm not sure it would be any wiser than just storing raw SQL. bye Philip --~--~---------~--~----~------------~-------~--~----~ 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 -~----------~----~----~----~------~----~------~--~---
