如何正确使用类SQL语句从Flask应用程序查询数据库

Question

我很难在Flask应用程序中使用LIKE语句来查询数据库。我总是遇到语法错误

我的应用程序是搜索书籍，我需要能够搜索所有列。我知道我需要使用LIKE，但是我的变量中的单引号似乎在妨碍我。然而，我不是100%肯定

我知道如果我想根据in id从表中选择all，格式如下：

id = request.form.get('id')
name = db.execute("SELECT first_name FROM users WHERE id=:id", {"id": current_id})

如果我尝试使用类似的格式，例如：

search = request.values.get('search')
books = db.execute("SELECT * FROM books WHERE author LIKE '%:search%'", {"search": search}).fetchall()

我得到这个错误：

sqlalchemy.exc.ProgrammingError: (psycopg2.errors.SyntaxError) syntax error at or near "Raymond"
LINE 1: SELECT * FROM books WHERE author LIKE '%'Raymond'%'
^

[SQL: SELECT * FROM books WHERE author LIKE '%%%(search)s%%']
[parameters: {'search': 'Raymond'}]
(Background on this error at: http://sqlalche.me/e/f405)

从这个错误中，我可以看到单引号被添加到我的变量值（搜索）中，但我没有成功地剥离或替换它们。所以我尝试了这种格式：

search = request.values.get('search')
books = db.execute("SELECT * FROM books WHERE author LIKE %s", ('%' + search + '%',)).fetchall()

但这也给了我一个错误： AttributeError: 'list' object has no attribute 'keys'

我已经为此工作了大约四天，但我尝试过的都没有成功。我不知所措。我知道我需要把这些单引号去掉，但我不确定该怎么做。我有一种感觉，这非常简单，这会让我很难过，但作为一名新手，我正在尽我最大的努力

如果您需要任何其他信息，请告诉我

编辑：包括回溯

对books = db.execute("SELECT * FROM books WHERE author LIKE %s", ('%' + search + '%',)).fetchall()的回溯

AttributeError
AttributeError: 'tuple' object has no attribute 'keys'

Traceback (most recent call last)
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\app.py", line 2464, in __call__
return self.wsgi_app(environ, start_response)
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\app.py", line 2450, in wsgi_app
response = self.handle_exception(e)
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\app.py", line 1867, in handle_exception
reraise(exc_type, exc_value, tb)
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\_compat.py", line 39, in reraise
raise value
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\app.py", line 2447, in wsgi_app
response = self.full_dispatch_request()
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\app.py", line 1952, in full_dispatch_request
rv = self.handle_user_exception(e)
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\app.py", line 1821, in handle_user_exception
reraise(exc_type, exc_value, tb)
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\_compat.py", line 39, in reraise
raise value
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\app.py", line 1950, in full_dispatch_request
rv = self.dispatch_request()
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\app.py", line 1936, in dispatch_request
return self.view_functions[rule.endpoint](**req.view_args)
File "C:\Users\Nilaja Williams\Desktop\Harvard Studies\nilajawill\project1\application.py", line 73, in current_search
books = db.execute("SELECT * FROM books WHERE author LIKE %s", ('%' + search + '%',)).fetchall()
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\sqlalchemy\orm\scoping.py", line 163, in do
return getattr(self.registry(), name)(*args, **kwargs)
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\sqlalchemy\orm\session.py", line 1291, in execute
return self._connection_for_bind(bind, close_with_result=True).execute(
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\sqlalchemy\engine\base.py", line 1020, in execute
return meth(self, multiparams, params)
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\sqlalchemy\sql\elements.py", line 298, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\sqlalchemy\engine\base.py", line 1099, in _execute_clauseelement
keys = list(distilled_params[0].keys())
AttributeError: 'tuple' object has no attribute 'keys'
The debugger caught an exception in your WSGI application. You can now look at the traceback which led to the error.
To switch between the interactive traceback and the plaintext one, you can click on the "Traceback" headline. From the text traceback you can also create a paste of it. For code execution mouse-over the frame you want to debug and click on the console icon on the right side.

You can execute arbitrary Python code in the stack frames and there are some extra helpers available for introspection:

dump() shows all variables in the frame
dump(obj) dumps all that's known about the object

对books = db.execute("SELECT * FROM books WHERE author LIKE '%:search%'", {"search": search}).fetchall()的回溯

sqlalchemy.exc.ProgrammingError
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.SyntaxError) syntax error at or near "Raymond"
LINE 1: SELECT * FROM books WHERE author LIKE '%'Raymond'%'
                                                 ^

[SQL: SELECT * FROM books WHERE author LIKE '%%%(search)s%%']
[parameters: {'search': 'Raymond'}]
(Background on this error at: http://sqlalche.me/e/f405)

Traceback (most recent call last)
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\sqlalchemy\engine\base.py", line 1247, in _execute_context
self.dialect.do_execute(
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\sqlalchemy\engine\default.py", line 590, in do_execute
cursor.execute(statement, parameters)
The above exception was the direct cause of the following exception:
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\app.py", line 2464, in __call__
return self.wsgi_app(environ, start_response)
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\app.py", line 2450, in wsgi_app
response = self.handle_exception(e)
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\app.py", line 1867, in handle_exception
reraise(exc_type, exc_value, tb)
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\_compat.py", line 39, in reraise
raise value
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\app.py", line 2447, in wsgi_app
response = self.full_dispatch_request()
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\app.py", line 1952, in full_dispatch_request
rv = self.handle_user_exception(e)
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\app.py", line 1821, in handle_user_exception
reraise(exc_type, exc_value, tb)
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\_compat.py", line 39, in reraise
raise value
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\app.py", line 1950, in full_dispatch_request
rv = self.dispatch_request()
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\flask\app.py", line 1936, in dispatch_request
return self.view_functions[rule.endpoint](**req.view_args)
File "C:\Users\Nilaja Williams\Desktop\Harvard Studies\nilajawill\project1\application.py", line 73, in current_search
books = db.execute("SELECT * FROM books WHERE author LIKE '%:search%'", {"search": search}).fetchall()
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\sqlalchemy\orm\scoping.py", line 162, in do
return getattr(self.registry(), name)(*args, **kwargs)
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\sqlalchemy\orm\session.py", line 1277, in execute
return self._connection_for_bind(bind, close_with_result=True).execute(
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\sqlalchemy\engine\base.py", line 984, in execute
return meth(self, multiparams, params)
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\sqlalchemy\sql\elements.py", line 293, in _execute_on_connection
return connection._execute_clauseelement(self, multiparams, params)
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\sqlalchemy\engine\base.py", line 1097, in _execute_clauseelement
ret = self._execute_context(
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\sqlalchemy\engine\base.py", line 1287, in _execute_context
self._handle_dbapi_exception(
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\sqlalchemy\engine\base.py", line 1481, in _handle_dbapi_exception
util.raise_(
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\sqlalchemy\util\compat.py", line 178, in raise_
raise exception
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\sqlalchemy\engine\base.py", line 1247, in _execute_context
self.dialect.do_execute(
File "C:\Users\Nilaja Williams\AppData\Local\Programs\Python\Python38-32\Lib\site-packages\sqlalchemy\engine\default.py", line 590, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.SyntaxError) syntax error at or near "Raymond"
LINE 1: SELECT * FROM books WHERE author LIKE '%'Raymond'%'
^

[SQL: SELECT * FROM books WHERE author LIKE '%%%(search)s%%']
[parameters: {'search': 'Raymond'}]
(Background on this error at: http://sqlalche.me/e/f405)
The debugger caught an exception in your WSGI application. You can now look at the traceback which led to the error.
To switch between the interactive traceback and the plaintext one, you can click on the "Traceback" headline. From the text traceback you can also create a paste of it. For code execution mouse-over the frame you want to debug and click on the console icon on the right side.

You can execute arbitrary Python code in the stack frames and there are some extra helpers available for introspection:

dump() shows all variables in the frame
dump(obj) dumps all that's known about the object

Answer 1

对于参数化查询，您不需要在语句中使用引号，它们将由db api添加。这应该起作用：

search = request.values.get('search')
books = db.execute("SELECT * FROM books WHERE author LIKE :search", {"search": '%' + search + '%'}).fetchall()

%符号需要是参数的一部分，以便:search最终可以替换为带引号的搜索字符串，如here所述

错误消息显示如果查询已经包含引号会发生什么：将添加另一组qoutes，从而中断查询

但请注意，这也可能存在here所述的问题：LIKE表达式中的反斜杠被视为转义字符，可用于将通配符作为其文字值处理到传递的字符串中，但如果使用不正确，可能会导致意外的搜索结果，或者可能导致错误（例如，如果类似图案以反斜杠结尾）。
如果您不打算允许用户在搜索字符串中插入通配符，则需要避免它们，或者考虑使用position()函数代替：

position(:search in author) > 0