ponyORM: trouble with query
Question: I have query with dynamic conditions,i.e.

    
    
    select (lambda obj:obj.A = 'a' and obj.B = 'b' and ...)  
    

So i write code for this:

    
    
    def search(self,**kwargs):
            q = unicode('lambda obj:', 'utf-8')
        for field,value in kwargs.iteritems():
                value = unicode(value, 'utf-8')
                field = unicode(field, 'utf-8')
                q+=u" obj.%s == '%s' and" % (field,value
    
        q = q[0:q.rfind('and')] 
        res = select(q.encode('utf-8'))[:]
    

But i have this error during execution of function:

    
    
     tasks.search(title='Задача 1',url='test.com')
     res = select(q.encode('utf-8'))[:]
     File "<string>", line 2, in select
     File ".../local/lib/python2.7/site-packages/pony/utils.py", line 96, in      cut_traceback
    return func(*args, **kwargs)
      File ".../local/lib/python2.7/site-packages/pony/orm/core.py", line 3844, in select
    if not isinstance(tree, ast.GenExpr): throw(TypeError)
      File "...local/lib/python2.7/site-packages/pony/utils.py", line 123, in throw
      raise exc
      TypeError
    


Answer: While it is possible to use strings in order to apply conditions to a query,
it can be unsafe, because of the risk of SQL injection. The better way for
applying conditions to a query is using the `filter()` method. You can take
the latest version of Pony ORM from <https://github.com/ponyorm/pony>
repository and try a couple of examples provided below.

First we define entities and create a couple of objects:

    
    
    from decimal import Decimal
    from pony.orm import *
    
    db = Database('sqlite', ':memory:')
    
    class Product(db.Entity):
        name = Required(unicode)
        description = Required(unicode)
        price = Required(Decimal)
        quantity = Required(int, default=0)
    
    db.generate_mapping(create_tables=True)
    
    with db_session:
        Product(name='iPad', description='Air, 16GB', price=Decimal('478.99'), quantity=10)
        Product(name='iPad', description='Mini, 16GB', price=Decimal('284.95'), quantity=15)
        Product(name='iPad', description='16GB', price=Decimal('299.00'), quantity=10)
    

Now we'll apply filters passing them as keyword arguments:

    
    
    def find_by_kwargs(**kwargs):
        q = select(p for p in Product)
        q = q.filter(**kwargs)
        return list(q)
    
    with db_session:
        products = find_by_kwargs(name='iPad', quantity=10)
        for p in products:
            print p.name, p.description, p.price, p.quantity
    

Another option is to use lambdas in order to specify the conditions:

    
    
    def find_by_params(name=None, min_price=None, max_price=None):
        q = select(p for p in Product)
        if name is not None:
            q = q.filter(lambda p: p.name.startswith(name))
        if min_price is not None:
            q = q.filter(lambda p: p.price >= min_price)
        if max_price is not None:
            q = q.filter(lambda p: p.price <= max_price)
        return list(q)
    
    with db_session:
        products = find_by_params(name='iPad', max_price=400)
        for p in products:
            print p.name, p.description, p.price, p.quantity
    

As you can see filters can be applied dynamically. You can find more
information about using filters following by this link:
<http://doc.ponyorm.com/queries.html#Query.filter>

