Maintaining MySQL connection with peewee in a Flask web app

Question

I have a simple Flask web app that uses peewee to manage the MySQL connection. Unfortunately it seems I missed something important from my code, therefore I'm getting an pymysql.err.OperationalError: (2006, "MySQL server has gone away.. error after 10-20 minutes of using the site. If I restart the app it works fine again, so I assume I handle the connection/connections wrong.

I have some basic queries that I'm using to display lists on my UI. Since I'm new to Python it's possible that my whole logic is wrong, so I would be really happy if somebody could me explain what is the right way to manage (connect-close connection) queries with peewee in my case (simple website without any user functions).

You can see on the below code how I'm doing it now. It's fine until I connect, but after I connected to the db I'm just calling the queries without dealing the connection. I assume I should close the connection based on the query that has been called, but couldn't find a proper way. Calling the myDB.close() isn't enough or I'm using it in wrong.

# this is how I connect to the MySQL
import peewee as pw
from flask import Flask, request, session, g, redirect, url_for, abort, render_template, flash

myDB = pw.MySQLDatabase("", host="", user="", passwd="")

class MySQLModel(pw.Model):
    """A base model that will use our MySQL database"""
    class Meta:
        database = myDB

class city(MySQLModel):

    ...

class building(MySQLModel):
    ...
myDB.connect()

# this is how I manage the homepage
cityList = []
cityList = city.select().order_by(city.objectId).limit(15)
buildings = []
buildings = building.select().order_by(building.buildingName).limit(180)

def getSearchResult (content_from_url):
    searchResultList = []
    searchResultList = city.select().where(city.objTitle.contains(content_from_url))
    return searchResultList

@app.route('/', methods=['GET', 'POST'])
def main_page():

    search = request.args.get('search')
    if search:
        return render_template("results.html",  search = getSearchResult(search), str = search)
    else:
        return render_template("home.html", name=cityList, buildList=buildings)

# and this is how the subpage
relatedCityList = []
slugObj = []

buildings2 = []
buildings2 = building.select().order_by(building.buildingName).limit(180)

def getLink (title_for_link):
    slugObj = city.get(city.urlSlug == title_for_link)
    return slugObj

def displayRelatedCity (city_to_filter):

    resultCity = city.get(city.urlSlug == city_to_filter)
    relatedCityList = city.select().where(city.objTitle == resultCity.objTitle).limit(20)
    return relatedCityList    

@app.route('/city-list/<content>', methods=['GET', 'POST'])
def city_page(content):

    linkText = getLink(content)

    return render_template("details.html", relatedCity = displayRelatedCity(content), buildingName = linkText.buildingName, buildz = buildings2)

myDB.close()

Answer 1

You need to be reconnecting on request/response. http://docs.peewee-orm.com/en/latest/peewee/database.html#adding-request-hooks

There's even a section on Flask.