Flask Web应用MySQL插入查询报错，请求排查问题

我在虚拟环境中使用Python 3.10.2和Flask 2.2.2，以Visual Studio为IDE开发Web应用。编写的注册接口代码执行MySQL插入语句时出现语法错误，以下是相关代码和报错栈信息：

app.py代码 #

@app.route('/register' , methods = ['GET', 'POST'])
def register():
    msg = ''
    if request.method == 'POST' and 'username' in request.form and 'password' in request.form and 'email' in request.form:
        username = request.form['username']
        password = request.form['password']
        email = request.form['email']
        cursor = mysql.connection.cursor(MySQLdb.cursors.DictCursor)
        cursor.execute('SELECT * FROM accounts WHERE username =%s', (username, ))
        account = cursor.fetchone()
        if account:
            msg = "Account already exists !"
        elif not re.match(r'[^@]+@[^@]+\.[^@]+', email):
            msg = 'Invalid email adress !'

        elif not re.match(r'[A-Za-z0-9]+', username):
            msg = 'Username must contain only characters and numbers !'
        elif not username or not password or not email:
            msg = 'Please fill the form !'
        else:
            cursor.execute('INSERT INTO accounts VALUES (NULL ,%s,%s,%s', (username, password, email, ))
            mysql.connection.commit()
            msg = 'You have successfully registered !'
    elif request.method == 'POST':
        msg = 'Please fill out the form !'
    return render_template('register.html' , msg = msg)
    
if __name__ == '__main__':
    app.run(debug = True)

报错信息 #

Traceback (most recent call last):
  File "D:\Code\spoof\webapp2\env\lib\site-packages\flask\app.py", line 2548, in __call__
    return self.wsgi_app(environ, start_response)
  File "D:\Code\spoof\webapp2\env\lib\site-packages\flask\app.py", line 2528, in wsgi_app
    response = self.handle_exception(e)
  File "D:\Code\spoof\webapp2\env\lib\site-packages\flask\app.py", line 2525, in wsgi_app
    response = self.full_dispatch_request()
  File "D:\Code\spoof\webapp2\env\lib\site-packages\flask\app.py", line 1822, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "D:\Code\spoof\webapp2\env\lib\site-packages\flask\app.py", line 1820, in full_dispatch_request
    rv = self.dispatch_request()
  File "D:\Code\spoof\webapp2\env\lib\site-packages\flask\app.py", line 1796, in dispatch_request
    return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
  File "D:\Code\spoof\webapp2\app.py", line 68, in register
    cursor.execute('INSERT INTO accounts VALUES (NULL ,%s,%s,%s', (username, password, email, ))
  File "D:\Code\spoof\webapp2\env\lib\site-packages\MySQLdb\cursors.py", line 206, in execute
    res = self._query(query)
  File "D:\Code\spoof\webapp2\env\lib\site-packages\MySQLdb\cursors.py", line 319, in _query
    db.query(q)
  File "D:\Code\spoof\webapp2\env\lib\site-packages\MySQLdb\connections.py", line 254, in query
    _mysql.connection.query(self, query)
MySQLdb.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1")

问题排查与修复 #

• 直接原因：INSERT语句的括号不完整。原代码中'INSERT INTO accounts VALUES (NULL ,%s,%s,%s'末尾缺少一个闭合的)，导致SQL语法无效，触发1064错误。
• 修复后的代码：

  cursor.execute('INSERT INTO accounts VALUES (NULL ,%s,%s,%s)', (username, password, email, ))
  

• 优化建议：为了避免因表字段顺序变更引发问题，建议明确指定插入的字段名（假设表结构为id、username、password、email，且id是自增主键）：

  # 如果id是自增主键，可省略NULL，由数据库自动生成
  cursor.execute('INSERT INTO accounts (username, password, email) VALUES (%s, %s, %s)', (username, password, email, ))
  

内容的提问来源于stack exchange，提问作者Sahil Tariq Lone