Why does flask stop working after mysql restart?

Question

Flask

Here is the test code:

from flask import Flask
import pymysql
app = Flask(__name__)
conn = pymysql.connect(host='localhost',
                       user='xxx',
                       passwd='yyy',
                       db='zzz',
                       charset='utf8')

cur = conn.cursor(pymysql.cursors.DictCursor)


@app.route('/')
def hello_world():
    try:
        cur.execute("select * from news where id=209")
    except Exception as e:
        print(e)
    print(cur.fetchall())
    return "aaa"


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

Everything works fine until you restart Maysquel:

/etc/init.d/mysqld restart
Stopping mysqld:                                         [  OK  ]
Starting mysqld:                                           [  OK  ]

And in the logs I get this:

127.0.0.1 - - [28/Jun/2014 12:53:01] "GET / HTTP/1.1" 200 -
(2006, "MySQL server has gone away (BrokenPipeError(32, 'Broken pipe'))")

How to win it?

Answer 1

I solved it like this:

from flask import Flask, g 
import pymysql
app = Flask(__name__)


@app.before_request
def before_request():
    g.conn = pymysql.connect(host='localhost',
                             user='pm',
                             passwd='wccme',
                             db='pcm',
                             charset='utf8')

    g.cur = g.conn.cursor(pymysql.cursors.DictCursor)


@app.teardown_request
def close_mysql(exception=None):
    g.conn.close()


@app.route('/')
def hello_world():
    try:
        g.cur.execute("select * from news where id=209")
    except Exception as e:
        print(e)

    print(g.cur.fetchall())
    return "aaa"


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

I put the connection to the database in before_request, and closing the connections in teardown_request.
After that, you can restart mysql as you like.

Answer 2

What is not clear? You launched the application, it connected to the database. The connection is gone. Auto-reconnect not implemented. It doesn't need to be won. The base is the critical piece for the attachment. What if the database restarts for 2 minutes? What to show to the user? It is easiest to stop and start the uwsgi daemon when starting and stopping the base. For example, opensimulator.org/wiki/Autorestart_With_Upstart.
If you really want to, then the advice above is sqlalchemy - able to automatically connect.