On Jan 15, 2011, at 10:53 PM, Hector Blanco wrote: > Hello list... > > I would like to allow the users to perform certain queries without me > (or well... my server) knowing in advance what those queries are going > to be (without hard-coding the query). > > For instance: I have a “Product” class. One of it's fields is > "manufacturer" and another is "model" > > class Product(declarativeBase): > def __init__(self): > self.model = "" > self.manufacturer = "" > > I would like the user be able to input an string with a query, such as > “Product.model != 'foo' or Product.model != 'bar'” > or: > "Product.model == 'foo' && Product.manufacturer == 'bar'" > > I have created a little Python module (queryTree) that tokenizes the > string and generates a tree for that kind of queries. For the last one > mentioned above, it would be something like: > > > sqlalchemy.and_ > / \ > == == > / \ / \ > Product.model "foo" Product.manufacturer "bar" > > 1) The “&&” string can be converted to (stored as) the sqlalchemy.and_ method > 2) The fields of Product are sqlalchemy.orm.synonym(s). If I pass my > tree module the class I'm going to perform the query for, it can call > getattr(cls, "model") and get the synonym (I mean: get the > Product.model synonym itself instead of the “model” string) > 3) Equally, the comparators are get with getattr(Product, "__eq__") or > getattr(Product, "__ne__") so I can store in the tree node the > comparator function instead of the string “==” or “!=” > > But when I try to run the query: > from mylibs.product import Product > queryString = "Product.model == 'foo' && Product.manufacturer == 'bar'" > session.query(Product.Product).filter(queryTree.getQuery(queryString, > Product.Product)) > > I get an exception: > File "/home/hbr/Documents/my-cms/backlib/product/ProductManager.py", > line 62, in getByCustomFilter > retval = > Database.session.query(Product.Product).filter(queryTokenizer.getQuery()).all() > File "<string>", line 1, in <lambda> > File > "/home/hbr/.buildout/eggs/SQLAlchemy-0.6.5-py2.6.egg/sqlalchemy/orm/query.py", > line 52, in generate > fn(self, *args[1:], **kw) > File > "/home/hbr/.buildout/eggs/SQLAlchemy-0.6.5-py2.6.egg/sqlalchemy/orm/query.py", > line 942, in filter > "filter() argument must be of type " > ArgumentError: filter() argument must be of type > sqlalchemy.sql.ClauseElement or string
Well everything I can see is correct here, so you just have to ensure getQuery() is returning the root of your tree (which, if it's an "and_()", or a "x == y", is in fact an instance of ClauseElement). Don't do anything with eval() or strings, keep it as a tokenized structure on your end. SQLA's job is to make it into a string. > > With some other tests, I've got some other exceptions that made me > realize that I could possibly modify somehow the nodes of my tree > until getting "something" that is accepted by MySQL as a valid query, > but that's kind of cheating... I'd like to use pure SqlAlchemy if > possible (I trust SqlAlchemy more than my programming skills) :-D the system you've built to interpret user input into a SQL expression tree should have adequate constraints such that only valid expressions are built in the first place. -- 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 sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
