python - 如何在 SQL-LIMIT 中传递动态值？

Question

在SQL-LIMIT中传递动态值时出错。

代码

import mysql.connector
from flask import (Flask, jsonify, request)
from waitress import serve

mydb = mysql.connector.connect(
    host="localhost",
    user="root",
    password="",
    database="deikho_app"
)

app = Flask(__name__)    

@app.route('/')
def home():
    return 'Flask Test'


@app.route('/api/foo/', methods=['GET'])
def foo():
    cursor = mydb.cursor(buffered=True)

    videoId = request.args.get('videoId')
    recordLimit = request.args.get('recordLimit')

    if recordLimit:
        query = """ SELECT * FROM cb_user_levels_permissions LIMIT %s """
        cursor.execute(query, (recordLimit,))
    else:
        query = """ SELECT * FROM cb_user_levels_permissions """
        cursor.execute(query)

    queryResult = cursor.fetchall()
    return jsonify(queryResult)

if __name__ == '__main__':
    serve(app, host='0.0.0.0', port=5000)

代码说明

• 导入模块，创建与数据库的连接，并创建默认 URL。
• 然后/api/foo/部分，其他条件工作正常。但是在 if 条件下，我在将动态值从 URL 传递到查询时出错。
• 网址： http://127.0.0.1:5000/api/foo/?recordLimit=1

错误

ERROR:flaskTest:Exception on /api/foo/ [GET]
Traceback (most recent call last):
  File "C:\Users\Ideation\AppData\Roaming\Python\Python39\site-packages\flask\app.py", line 2070, in wsgi_app
    response = self.full_dispatch_request()
  File "C:\Users\Ideation\AppData\Roaming\Python\Python39\site-packages\flask\app.py", line 1515, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "C:\Users\Ideation\AppData\Roaming\Python\Python39\site-packages\flask\app.py", line 1513, in full_dispatch_request
    rv = self.dispatch_request()
  File "C:\Users\Ideation\AppData\Roaming\Python\Python39\site-packages\flask\app.py", line 1499, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**req.view_args)
  File "E:\flaskTest.py", line 46, in foo
    cursor.execute(query, (recordLimit,))
  File "C:\Users\Ideation\AppData\Roaming\Python\Python39\site-packages\mysql\connector\cursor.py", line 551, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "C:\Users\Ideation\AppData\Roaming\Python\Python39\site-packages\mysql\connector\connection.py", line 490, in cmd_query
    result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))
  File "C:\Users\Ideation\AppData\Roaming\Python\Python39\site-packages\mysql\connector\connection.py", line 395, in _handle_result
    raise errors.get_exception(packet)
mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ''1'' at line 1

标签： pythonmysqlpython-3.xflasklimit