Insert error in a Mysql query Flask web app-CodePudding

My app.py code :

@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)

This is the error I get :

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")

I am using python 3.10.2 and flask 2.2.2 in my virtual environment. And I am using Visual Studio IDE. It seems like I am doing something wrong in the MYSQL query. can you guys tell me what am i doing wrong here.

CodePudding user response：

You are missing a closing bracket in your cursor.execute() statement and the last comma is not needed. Also, you can specify the column names.

Here is a solution with the query and values separated into variables for readability:

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