MySQLdb raises "execute() first" error even though I execute before calling fetchall

Question

I'm getting the following error while trying to use MySQLdb with Flask. _mysql_exceptions.ProgrammingError: execute() first

The print output shows me that "After fetch" never gets printed, meaning that the script breaks down at row = g.db.cursor().fetchall(). Why am I getting this error and how do I fix it?

def connect_db():
    return mysql.connection

@app.before_request
def before_request():
    g.db = connect_db()

@app.teardown_request
def teardown_request(exception):
    g.db.cursor().close()

@app.route('/login', methods=['GET', 'POST'])
def login():
    if request.method == 'POST':
        g.db.cursor().execute('SELECT * FROM users WHERE email = %s', [request.form['email']])
        print('Before fetch')
        row = g.db.cursor().fetchall()
        print('After fetch')

        if not row:
            flash('No such user exists!')
            return redirect(url_for('show_home'))
        elif request.form['password'] == row[0][1]:
            return redirect(url_for('show_dashboard'))
        else:
            flash('Wrong password / login error.')
            return redirect(url_for('show_home'))

File "/media/common/code/python/projects/up2date/venv/lib/python3.4/site-packages/flask/app.py", line 1836, in __call__
return self.wsgi_app(environ, start_response)
File "/media/common/code/python/projects/up2date/venv/lib/python3.4/site-packages/flask/app.py", line 1820, in wsgi_app
response = self.make_response(self.handle_exception(e))
File "/media/common/code/python/projects/up2date/venv/lib/python3.4/site-packages/flask/app.py", line 1403, in handle_exception
reraise(exc_type, exc_value, tb)
File "/media/common/code/python/projects/up2date/venv/lib/python3.4/site-packages/flask/_compat.py", line 33, in reraise
raise value
File "/media/common/code/python/projects/up2date/venv/lib/python3.4/site-packages/flask/app.py", line 1817, in wsgi_app
response = self.full_dispatch_request()
File "/media/common/code/python/projects/up2date/venv/lib/python3.4/site-packages/flask/app.py", line 1477, in full_dispatch_request
rv = self.handle_user_exception(e)
File "/media/common/code/python/projects/up2date/venv/lib/python3.4/site-packages/flask/app.py", line 1381, in handle_user_exception
reraise(exc_type, exc_value, tb)
File "/media/common/code/python/projects/up2date/venv/lib/python3.4/site-packages/flask/_compat.py", line 33, in reraise
raise value
File "/media/common/code/python/projects/up2date/venv/lib/python3.4/site-packages/flask/app.py", line 1475, in full_dispatch_request
rv = self.dispatch_request()
File "/media/common/code/python/projects/up2date/venv/lib/python3.4/site-packages/flask/app.py", line 1461, in dispatch_request
return self.view_functions[rule.endpoint](**req.view_args)
File "/media/common/code/python/projects/up2date/up2date.py", line 47, in login
row = g.db.cursor().fetchall()
File "/media/common/code/python/projects/up2date/venv/lib/python3.4/site-packages/MySQLdb/cursors.py", line 406, in fetchall
self._check_executed()
File "/media/common/code/python/projects/up2date/venv/lib/python3.4/site-packages/MySQLdb/cursors.py", line 107, in _check_executed
self.errorhandler(self, ProgrammingError, "execute() first")
File "/media/common/code/python/projects/up2date/venv/lib/python3.4/site-packages/MySQLdb/connections.py", line 38, in defaulterrorhandler
Open an interactive python shell in this frameraise errorclass(errorvalue)

Answer 1

You're calling cursor multiple times, which creates multiple cursors. You execute the query on the first cursor, then fetch from the second. Since you didn't execute anything on the second, you get this error. Use only one cursor.

cursor = g.db.cursor()
cursor.execute(...)
rows = cursor.fetchall()