TypeError:不是所有的参数在字符串格式化游标时转换.(查询,to_filter)执行

Question

我试图与GCP建立一个简单的flask应用程序，并在部署应用程序的API响应后，当我根据ID, AUTHOR, PUBLISHED date搜索结果时，我得到TypeError:并非所有参数在字符串格式化期间转换为cursor.execute。这是我在这些情况下调用的函数:

错误出现在这一行:results =游标。执行(查询，to_filter)，我真的不明白哪个参数不是从语法SELECT * from条目WHERE id=转换?

它看起来像这样:

def filter_books():
conn = open_connection()
query_parameters = request.args
id = query_parameters.get('id')
published = query_parameters.get('published')
author = query_parameters.get('author')
with conn.cursor() as cursor: 
query = 'SELECT * FROM entries WHERE'
to_filter = []
if id:
query += ' id=?  AND'
to_filter.append(id)

if published:
query += ' published=?  AND'
to_filter.append(published)
if author:
query += ' author=?  AND'
to_filter.append(author)
if not(id or published or author):
return "Page not found"
query = query[:-4] + ';'
results = cursor.execute(query, to_filter).fetchall() **(line 63 - error line)**
return jsonify(f_results)

错误如下:

Traceback (most recent call last):
File "/layers/google.python.pip/pip/lib/python3.7/site-packages/flask/app.py", line 2073, in wsgi_app
response = self.full_dispatch_request()
File "/layers/google.python.pip/pip/lib/python3.7/site-packages/flask/app.py", line 1518, in full_dispatch_request
rv = self.handle_user_exception(e)
File "/layers/google.python.pip/pip/lib/python3.7/site-packages/flask/app.py", line 1516, in full_dispatch_request
rv = self.dispatch_request()
File "/layers/google.python.pip/pip/lib/python3.7/site-packages/flask/app.py", line 1502, in dispatch_request
return self.ensure_sync(self.view_functions[rule.endpoint])(**req.view_args)
File "/srv/main.py", line 29, in api_filter
return filter_books()
File "/srv/db.py", line 63, in filter_books
results = cursor.execute(query, to_filter)
File "/layers/google.python.pip/pip/lib/python3.7/site-packages/pymysql/cursors.py", line 146, in execute
query = self.mogrify(query, args)
File "/layers/google.python.pip/pip/lib/python3.7/site-packages/pymysql/cursors.py", line 125, in mogrify
query = query % self._escape_args(args, conn)
TypeError: not all arguments converted during string formatting 

这是对主文件。py中函数的调用(第27-29行)

@app.route('/api/v2/resources/books', methods=['GET'])
def api_filter():
return filter_books()

对于API (/API/v2/resources/books?)= 2010年出版的) query和to_filter有以下值:

query = 'SELECT * FROM entries WHERE published=?'to filter = [published]

游标。execute看起来像这样:results= cursor。execute('SELECT * FROM entries WHERE published=?",出版).fetchall ()

我试过让to_filter一个元组，在stackoverflow和谷歌上的每一个其他主题都接近这个，但没有成功。它看起来像一个简单的事情，我感到困惑或没有理解足够，但经过6个小时的尝试，我需要一些帮助。

谢谢!

main.py -函数调用Db.py -函数定义

Answer 1

我设法让它工作，我所做的修改是使用%s而不是?正如John Hanley所建议的那样，而不是对游标调用.fetchall()。因为返回的结果是int类型，所以不可能执行，所以我在游标上调用了.fetchall()。

所以最后的代码是这样的:

def filter_books():
conn = open_connection()
query_parameters = request.args
id = query_parameters.get('id')
published = query_parameters.get('published')
author = query_parameters.get('author')
with conn.cursor() as cursor: 
query = 'SELECT * FROM entries WHERE'
to_filter = []
if id:
query += ' id= %s  AND'
to_filter.append(id)
if published:
query += ' published= %s  AND'
to_filter.append(published)
if author:
query += ' author= %s  AND'
to_filter.append(author)
if not(id or published or author):
return "Page not found"
query = query[:-4] + ';'
results = cursor.execute(query, to_filter)
f_results=cursor.fetchall()
return jsonify(f_results)