ponyORM: 查询问题

Question

我有一个查询，它的条件是动态的，也就是说，

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

所以我为此写了代码：

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'))[:]

但是在执行这个函数的时候，我遇到了这个错误：

 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 1

如果你还是想用字符串来过滤数据，那你需要为每一个键值对都应用新的过滤条件。

大概是这样的：

def search(self,**kwargs):
    q = select(m for m in Product)
    for field,value in kwargs.iteritems():
        value = unicode(value, 'utf-8')
        field = unicode(field, 'utf-8')
        flt = u"m.{0} == {1}".format(value, field)
        q = q.filter(flt)
    # return q # return Query which can be further modified (for ex. paging, ordering, etc.)
    return q[:] # or return found products

希望这对你有帮助，汤姆

Answer 2

虽然可以用字符串来给查询添加条件，但这样做不太安全，因为有可能会遭遇SQL注入攻击。更好的方法是使用filter()这个方法来添加条件。你可以从https://github.com/ponyorm/pony这个仓库下载最新版本的Pony ORM，并试试下面提供的一些例子。

首先，我们定义一些实体，并创建几个对象：

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)

接下来，我们将通过关键字参数来应用过滤器：

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

还有一种选择是使用lambda表达式来指定条件：

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

如你所见，过滤器可以动态应用。你可以通过这个链接找到更多关于使用过滤器的信息：http://doc.ponyorm.com/queries.html#Query.filter