UnboundLocalError: local variable 'cursor' referenced before assignment

Question

So I am a newbie but working on a registration system form in flask/MYSQL

I am receiving this error (UnboundLocalError: local variable 'cursor' referenced before assignment)

After hours of playing with the code and research I need your help.

This is my file, please let me know if theres anything else I need to share. thank you

from flask import Flask, render_template, json, request
from flask.ext.mysqldb import MySQL
from werkzeug import generate_password_hash, check_password_hash

app = Flask(__name__)
mysql = MySQL()


app.config['MYSQL_DATABASE_USER'] = 'x'
app.config['MYSQL_DATABASE_PASSWORD'] = 'x'
app.config['MYSQL_DATABASE_DB'] = 'x'
app.config['MYSQL_DATABASE_HOST'] = 'x'
mysql.init_app(app)

@app.route('/')
def main():
    return render_template('index.html')

@app.route('/login')
def login():
    return render_template('login.html')

@app.route('/showSignUp')
def showSignUp():
    return render_template('signup.html')


@app.route('/signUp',methods=['POST','GET'])
def signUp():
    try:
        _name = request.form['inputName']
        _email = request.form['inputEmail']
        _password = request.form['inputPassword']

        # validate the received values
        if _name and _email and _password:

            # All Good, let's call the MySQL

            conn = mysql.connect()
            cursor = conn.cursor()
            _hashed_password = generate_password_hash(_password)
            cursor.callproc('sp_createUser',(_name,_email,_hashed_password))
            data = cursor.fetchall()

            if len(data) is 0:
                conn.commit()
                return json.dumps({'message':'User created successfully !'})
            else:
                return json.dumps({'error':str(data[0])})
        else:
            return json.dumps({'html':'<span>Enter the required fields</span>'})

    except Exception as e:
        return json.dumps({'error':str(e)})
    finally:
        cursor.close()
    conn.close()

if __name__ == '__main__':
    app.run()

Answer 1

You only define conn and cursor inside the if block checking the form values. If the block is not entered, they're not defined, but you still try to reference them to close them anyway. You should only call close on both if you've defined them. Either move conn = and cursor = to before the if block, or move the close calls to within the block.

However, the bigger problem is that you're misunderstanding/overcomplicating how to use Flask-MySQLdb. It will automatically create the connection and close it when the request is done, which also closes the cursor. Simply use the extension as described in the docs.

...
cur = mysql.connection.cursor()
cur.callproc('sp_createUser', (name, email, hashed_password))
data = cur.fetchall()
...

Answer 2

Personally I would recommend using a context manager to handle opening and closing of your cursor and connection. You can achieve this fairly simply and it is cleaner and easier to debug. This also would eliminate the problem of trying to close a connection or cursor before it is opened in your giant try except block.

from contextlib import closing

# do a bunch of stuff prior to opening connection
with closing(mysql.connect()) as conn:
    with closing(conn.cursor()) as cursor:
        # do a bunch of stuff and don't worry about running .close()

You can view the docs for closing here.

Using the closing would change your code to be something like this. Although it could use more refactoring, but that is a question for the code review site.

from flask import Flask, render_template, json, request
from flask.ext.mysqldb import MySQL
from werkzeug import generate_password_hash, check_password_hash
from contextlib import closing

app = Flask(__name__)
mysql = MySQL()


app.config['MYSQL_DATABASE_USER'] = 'x'
app.config['MYSQL_DATABASE_PASSWORD'] = 'x'
app.config['MYSQL_DATABASE_DB'] = 'x'
app.config['MYSQL_DATABASE_HOST'] = 'x'
mysql.init_app(app)

@app.route('/')
def main():
    return render_template('index.html')

@app.route('/login')
def login():
    return render_template('login.html')

@app.route('/showSignUp')
def showSignUp():
    return render_template('signup.html')


@app.route('/signUp',methods=['POST','GET'])
def signUp():
    try:
        _name = request.form['inputName']
        _email = request.form['inputEmail']
        _password = request.form['inputPassword']

        # validate the received values
        if _name and _email and _password:

            # All Good, let's call the MySQL

            with closing(mysql.connect()) as conn:
                with closing(conn.cursor()) as cursor:
                    _hashed_password = generate_password_hash(_password)
                    cursor.callproc('sp_createUser',(_name,_email,_hashed_password))
                    data = cursor.fetchall()

                    if len(data) is 0:
                        conn.commit()
                        return json.dumps({'message':'User created successfully !'})
                    else:
                        return json.dumps({'error':str(data[0])})
        else:
            return json.dumps({'html':'<span>Enter the required fields</span>'})

    except Exception as e:
        return json.dumps({'error':str(e)})

if __name__ == '__main__':
    app.run()