flask - When to close mysql connection?

Question

What is the right way of closing connection to mysql? In the tutorial I was following, the author closed the connection with the first way, but that does not seem right. Because it will try to close after return. So which one of the following is the proper closing?

First way, the way author does it:

@app.route("/dashboard")
@login_required
def dashboard():
    cur = mysql.connection.cursor()
    result = cur.execute("SELECT * FROM articles")

    articles = cur.fetchall()

    if result > 0:
        return render_template("dashboard.html", articles = articles)
    else:
        msg = "No Articles Yet"
        return render_template("dashboard.html", msg = msg)
    cur.close() // wrong

My suggestion, the way i think is correct:

@app.route("/dashboard")
@login_required
def dashboard():
    cur = mysql.connection.cursor()
    result = cur.execute("SELECT * FROM articles")

    articles = cur.fetchall()
    cur.close() // correct

    if result > 0:
        return render_template("dashboard.html", articles = articles)
    else:
        msg = "No Articles Yet"
        return render_template("dashboard.html", msg = msg)

Answer 1

One reason, not to close the cursor where you did, could be, that cur.fetchall() could return an iterator what could cause troubles, if the iterator is not fully processed before the cursor is closed. In SQLAlchemy, this seems to be the case, since fetchall really seems to return an iterator (according to docs).

So you could do better this:

articles = list(cur.fetchall())
cur.close()

This will make sure, that the iterator is exhausted before you close the cursor.

Only in the case you deal with really big databases, you could do a little better with this (save space for the list -- but the render result will be most likely very big anyway):

articles = cur.fetchall()
cur.close()

if result > 0:
   value = render_template("dashboard.html", articles = articles)
   cur.close()
   return value
else:
   cur.close()
   msg = "No Articles Yet"
   return render_template("dashboard.html", msg = msg)

And back to your original question: Yes, the original code was totally wrong, because the close will never be reached, when the method is left with return before. The intention might be that what I described above, but this was just not right.