cptnhaddock cptnhaddock - 1 year ago 137
MySQL Question

Flask-MySQL gives "closing a closed connection" error the second time a view runs

I am using Flask-MySQL to connect to my database in a view. The view works the first time I go to it, but when I go to it the second time it always crashes with the error:

ProgrammingError: closing a closed connection

Why am I getting this error? How do I connect successfully the second time?

from flask import Flask, render_template, request
from flaskext.mysql import MySQL

app = Flask(__name__)

@app.route('/hello/', methods=['POST'])
def hello():
app.config['MYSQL_DATABASE_USER'] = 'root'
app.config['MYSQL_DATABASE_PASSWORD'] = 'xxx'
app.config['MYSQL_DATABASE_DB'] = 'pies'
app.config['MYSQL_DATABASE_HOST'] = 'localhost'

query = request.form['yourname']

mysql = MySQL(app)
conn = mysql.connect()

with conn as cursor:
name = str(cursor.fetchone())
name = "SQL is wrong"

return render_template('form_action.html', name=name)

if __name__ == '__main__':

Answer Source

You should not be initializing the extension during every request. Create it during app setup, then use it during the requests. Setting configuration should also be done during app setup.

The extension adds a teardown function that executes after every request and closes the connection, which is stored as a threadlocal. Since on the second request you've registered the extension multiple times, it is trying to close the connection multiple times.

There's no need to call connect, the extension adds a before request function that does that. Use get_db to get the connection for the request. Since the extension closes this connection for you, don't call close either.

from flask import Flask
from flaskext.mysql import MySQL


app = Flask(__name__)

mysql = MySQL(app)

def hello():
    conn = mysql.get_db()
    # ...

Note that Flask-MySQL is using a very old extension pattern that is no longer supported by Flask. It also depends on an unsupported mysql package that does not support Python 3. Consider using Flask-MySQLdb instead.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download