[sqlalchemy] Re: Implementing saved searches
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 . 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). --~--~-~--~~~---~--~~ 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: Implementing saved searches
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 -~--~~~~--~~--~--~---
[sqlalchemy] Re: Implementing saved searches
On Jul 22, 2008, at 3:55 PM, Philip Semanchuk wrote: 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. you can pickle clause expressions. On Query, its available as query.whereclause. Though if you dont greatly restrict and parse the expressions you're storing then you still have dependencies on table structure. --~--~-~--~~~---~--~~ 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 -~--~~~~--~~--~--~---
