Re: [sqlalchemy] Pass query with as parameter (avoid creating a method and hardcoding a query)
2011/1/16 Tamás Bajusz gbt...@gmail.com: Is your work available, or do you plan to put it public somewhere? Mmm... maybe... contact me privately if you're interested -- 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.
Re: [sqlalchemy] Pass query with as parameter (avoid creating a method and hardcoding a query)
Sorry for late reply, but I was rather busy with real life work. I believe your code will be useful for me and for others too. Anyhow, thank you very much for it! On Thu, Jan 27, 2011 at 5:17 PM, Hector Blanco white.li...@gmail.com wrote: 2011/1/16 Tamás Bajusz gbt...@gmail.com: Is your work available, or do you plan to put it public somewhere? Mmm... maybe... contact me privately if you're interested -- 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. -- 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.
Re: [sqlalchemy] Pass query with as parameter (avoid creating a method and hardcoding a query)
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.
Re: [sqlalchemy] Pass query with as parameter (avoid creating a method and hardcoding a query)
Thanks for your help! It was key knowing that I was going in the right direction. The problem was that I'm stup... erm... I mean... erm... that I made a bad mistake (beginner's one)... I was getting the comparator for the Product class: (getattr(Product, __eq__)) instead of the comparator for the field/synonym (if I wanted to check for model == 'foo', I needed to get: getattr(Product.model, __eq__). Yey!! It works! Thank you so much!! 2011/1/16 Michael Bayer mike...@zzzcomputing.com: 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. -- 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.
Re: [sqlalchemy] Pass query with as parameter (avoid creating a method and hardcoding a query)
Is your work available, or do you plan to put it public somewhere? On Sun, Jan 16, 2011 at 7:53 PM, Hector Blanco white.li...@gmail.com wrote: Thanks for your help! It was key knowing that I was going in the right direction. The problem was that I'm stup... erm... I mean... erm... that I made a bad mistake (beginner's one)... I was getting the comparator for the Product class: (getattr(Product, __eq__)) instead of the comparator for the field/synonym (if I wanted to check for model == 'foo', I needed to get: getattr(Product.model, __eq__). Yey!! It works! Thank you so much!! 2011/1/16 Michael Bayer mike...@zzzcomputing.com: 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. -- 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. -- 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
[sqlalchemy] Pass query with as parameter (avoid creating a method and hardcoding a query)
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 I could easily modify my query generator module to get the query string...: “and_(Product.model == 'foo', Product.manufacturer == 'bar')” so a call to eval(“and_(Product.model == 'foo', Product.manufacturer == 'bar')”) would probably work, but I'd like to avoid the use of eval because of the security issues it usually implies. 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 Thank you in advance. Every hint will be deeply appreciated. -- 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.
