python - Peewee 具有多个编程过滤器参数的原始 SELECT sql

Question

我面临着从此 get_users() 函数返回用户的问题。这是代码:我正在使用 Peewee、Pymysql 和 MySQL

    def get_users(self, filter_columns=None,  parameters=[], operator=None, ):
    #Define the operator to be used in the WHERE statement
    if operator:
        operator = operator
    else:
        operator = '&'

    #Contruct the sql_where statement
    where = ''
    if filter_columns:
        for field in filter_columns:
            where = where + field + '=%s, '
    if len(where)>2:
        where = where[:-2]
    #Build Parameter list
    param_list = ''
    for param in parameters:
        param_list = param_list + param + ", "
    if len(param_list)>2:
        param_list = param_list[:-2]

    #Select the users and return.
    sql = "SELECT * FROM user WHERE " + where
    user = U.raw(sql, param_list)
    return user

当我像这样调用函数时:

  users = user.get_users(filter_columns=['first_name', 'status'], parameters=['awa', 'active'], operator='|')
print(users)

 for u in users:
     print(u.first_name, u.last_name)

这就是我得到的结果:

    Traceback (most recent call last):
  File "D:/projects/micro bank/tests/smanager/randomtest.py", line 10, in <module>
    for u in users:
  File "C:\Python34\lib\site-packages\peewee.py", line 2963, in __iter__
    return iter(self.execute())
  File "C:\Python34\lib\site-packages\peewee.py", line 2959, in execute
    self._qr = QRW(self.model_class, self._execute(), None)
  File "C:\Python34\lib\site-packages\peewee.py", line 2902, in _execute
    return self.database.execute_sql(sql, params, self.require_commit)
  File "C:\Python34\lib\site-packages\peewee.py", line 3758, in execute_sql
    cursor.execute(sql, params or ())
  File "C:\Python34\lib\site-packages\pymysql\cursors.py", line 164, in execute
    query = self.mogrify(query, args)
  File "C:\Python34\lib\site-packages\pymysql\cursors.py", line 143, in mogrify
    query = query % self._escape_args(args, conn)
TypeError: not enough arguments for format string

当我打印出返回的用户时，我得到:

<class 'common.models.User'> SELECT * FROM user WHERE first_name=%s, status=%s ['awa, active']

从观察来看，问题出在最后一个区域

['awa, active']

，应该是

['awa', 'active']

现在的问题是建立一个parameter_list，当我使用它时，它应该像这样打印出来['awa', 'active']

感谢您的帮助。

Answer 1

我假设您需要 param_list 为 ['awa', 'active']

试试这个:

param_list = []
for param in parameters:
    param_list.append(param)
# Since `param_list` in needed in the format `'"awa", "active"'`
param_list = ', '.join('"{0}"'.format(w) for w in param_list)

而不是

param_list = ''
for param in parameters:
    param_list = param_list + param + ", "