python - Flask 使用 where 表达式执行 mysql 查询

Question

我使用此函数来运行 SQL 查询:

@staticmethod
def find_users(query):
    search_job = query
    # search_formatted = ' & '.join(words)

    sql = ("""SELECT first_name,last_name, email, phone, image, diplome, latitude, longitude, description
    FROM users, jobs
    WHERE users.id_job = jobs.id
    AND jobs.description = ?
           """, [search_job])
    cursor = db.session.execute(sql)
                                # {'search_terms': search_job})

    # Returns a list of product tuples
    return cursor.fetchall()

但我收到此错误

2017-08-05 18:54:18,421 INFO sqlalchemy.engine.base.Engine (4L,) 2017-08-05 18:54:18,424 INFO sqlalchemy.engine.base.Engine COMMIT 127.0.0.1 - - [05/Aug/2017 18:54:18] "GET / HTTP/1.1" 200 - 127.0.0.1 - - [05/Aug/2017 18:54:19] "GET /static/img/markers_shadow.png HTTP/1.1" 404 - 127.0.0.1 - - [05/Aug/2017 18:54:19] "GET /static/fonts/glyphicons-halflings-regular.woff2 HTTP/1.1" 404 - 127.0.0.1 - - [05/Aug/2017 18:54:19] "GET /static/fonts/glyphicons-halflings-regular.woff HTTP/1.1" 404 - 127.0.0.1 - - [05/Aug/2017 18:54:19] "GET /static/fonts/glyphicons-halflings-regular.ttf HTTP/1.1" 404 - [2017-08-05 18:54:23,162] ERROR in app: Exception on /auth/search [GET] Traceback (most recent call last): File "/home/alaoui/Documents/ProjetHandy/venv-handy/lib/python2.7/site-packages/flask/app.py", line 1988, in wsgi_app response = self.full_dispatch_request() File "/home/alaoui/Documents/ProjetHandy/venv-handy/lib/python2.7/site-packages/flask/app.py", line 1641, in full_dispatch_request rv = self.handle_user_exception(e) File "/home/alaoui/Documents/ProjetHandy/venv-handy/lib/python2.7/site-packages/flask/app.py", line 1544, in handle_user_exception reraise(exc_type, exc_value, tb) File "/home/alaoui/Documents/ProjetHandy/venv-handy/lib/python2.7/site-packages/flask/app.py", line 1639, in full_dispatch_request rv = self.dispatch_request() File "/home/alaoui/Documents/ProjetHandy/venv-handy/lib/python2.7/site-packages/flask/app.py", line 1625, in dispatch_request return self.view_functionsrule.endpoint File "/home/alaoui/Documents/ProjetHandy/handy_2/app/auth/views.py", line 194, in search_handyman handymans = User.find_handymans(search_query) File "/home/alaoui/Documents/ProjetHandy/handy_2/app/models.py", line 88, in find_handymans cursor = db.session.execute(sql) File "/home/alaoui/Documents/ProjetHandy/venv-handy/lib/python2.7/site-packages/sqlalchemy/orm/scoping.py", line 157, in do return getattr(self.registry(), name)(*args, **kwargs) File "/home/alaoui/Documents/ProjetHandy/venv-handy/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1101, in execute clause = expression._literal_as_text(clause) File "/home/alaoui/Documents/ProjetHandy/venv-handy/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 4238, in _literal_as_text "instead" % type(element) ArgumentError: SQL expression object or string expected, got object of type instead

Answer 1

如果您查看 execute 的签名你发现:

execute(clause, params=None, mapper=None, bind=None, **kw)

然后，查看 doc ，你会发现:

参数:

• 子句 – 要执行的可执行语句(即可执行表达式，例如 expression.select())或字符串 SQL 语句。

• params – 可选字典或字典列表，包含绑定(bind)参数值。如果是单个字典，则发生单行执行；如果是字典列表，则将调用“executemany”。每个字典中的键必须与语句中存在的参数名称相对应。

• mapper – 可选的mapper() 或映射类，用于识别适当的绑定(bind)。定位绑定(bind)时，此参数优先于子句。有关详细信息，请参阅 Session.get_bind()。

• bind – 用作绑定(bind)的可选引擎。如果该引擎已参与正在进行的事务，则将使用该连接。定位绑定(bind)时，此参数优先于映射器和子句。

• **kw – 附加关键字参数被发送到 Session.get_bind() 以允许“绑定(bind)”方案的扩展性。

使用调试器单步执行代码并查看您实际作为参数传递给该签名的内容。你会发现你的第二个参数 [search_job]不满足任何预期参数，因此您得到

ArgumentError: SQL expression object or string expected, got object of type instead