Im getting an Internal Server error for my Flask app hosted at PythonAnywhere

Question

So when i first deployed my app, it worked really well. It showed all pages, i was able to write and read from my PythonAnywhere MySQL database. Then after 10 minutes I get the internl server error. Then when I reload the app, it works for 10 minutes then goes back to the internal server error. The details below show my code that communicates to my Database, the server.log error code and the error.log code i get as well.

Server Log:

2019-04-29 01:06:40 Mon Apr 29 01:06:40 2019 - SIGPIPE: writing to a closed pipe/socket/fd (probably the client disconnected) on request / (ip 10.0.0.162) !!!

==============================================================================

Error Log:

2019-04-29 01:06:40,959: Exception on / [GET]
Traceback (most recent call last):
  File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 2292, in wsgi_app
    response = self.full_dispatch_request()
  File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1815, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1718, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1813, in full_dispatch_request
    rv = self.dispatch_request()
  File "/usr/local/lib/python2.7/dist-packages/flask/app.py", line 1799, in dispatch_request
    return self.view_functions[rule.endpoint](**req.view_args)
  File "/home/chronic007/TheCronica/index.py", line 21, in home_page
    cursor.execute(query)
  File "/usr/local/lib/python2.7/dist-packages/mysql/connector/cursor.py", line 393, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "/usr/local/lib/python2.7/dist-packages/mysql/connector/connection.py", line 586, in cmd_query
statement))
  File "/usr/local/lib/python2.7/dist-packages/mysql/connector/connection.py", line 386, in _send_cmd
packet_number)
  File "/usr/local/lib/python2.7/dist-packages/mysql/connector/network.py", line 104, in send_plain
    raise errors.OperationalError(str(err))
OperationalError: [Errno 32] Broken pipe

=============================================================================

Here is my python flask file:

from flask import Flask
from flask import render_template
from flask import request
import mysql.connector

app = Flask(__name__)
db = mysql.connector.connect(
    host="xxxx.mysql.pythonanywhere-services.com",
    user="xxxxx",
    passwd="xxxxxx",
    database= "xxxxxx"
)

cursor = db.cursor()

@app.route('/')
def home_page():
    query = "SELECT * FROM Articles"
    cursor.execute(query)
    data = cursor.fetchall()
    return render_template('index.html', value=data)

@app.route('/static/index.html')
def static_home():
    query = "SELECT * FROM Articles"
    cursor.execute(query)
    data = cursor.fetchall()
    return render_template('index.html', value=data)

@app.route('/static/about.html')
def static_about():
    return render_template('about.html')

@app.route('/admin')
def admin_page():
    return render_template('admin.html')

@app.route('/admin', methods=['POST'])
def submit_form():
    query = "INSERT INTO Articles (Title, Author, Date, Description, Content, Link) VALUES (%s, %s, %s, %s, %s, %s)"
    if request.method == 'POST' :
        title = request.form.get('Title')
        author = request.form.get('Author')
        date = request.form.get('Date')
        description = request.form.get('Description')
        content = request.form.get('Content')
        link = request.form.get('Link')
    val = (title, author, date, description, content, link)
    cursor.execute(query, val)
    db.commit()
    return render_template('admin.html')

Answer 1

You're opening a DB connection at module level (outside your view functions) so it may no longer be valid by the time a view is called; database connections shut down when they're unused for a while, which would explain the issue you're seeing. You should either create the db and cursor objects inside every view function that uses them, or even better use a database connection manager like SQLAlchemy.

This PythonAnywhere help page has more information about connection management, and this blog post has a walkthough for creating a database-backed website using Flask and SQLAlchemy on PythonAnywhere.

Answer 2

First this code is outside of the if statement

    val = (title, author, date, description, content, link)
        cursor.execute(query, val)
        db.commit()

You don't need two views to handle admin just do this

@app.route('/admin', methods=['GET', 'POST'])
def submit_form():
    query = "INSERT INTO Articles (Title, Author, Date, Description, Content, Link) VALUES (%s, %s, %s, %s, %s, %s)"
    if request.method == 'POST' :
        title = request.form.get('Title')
        author = request.form.get('Author')
        date = request.form.get('Date')
        description = request.form.get('Description')
        content = request.form.get('Content')
        link = request.form.get('Link')
        val = (title, author, date, description, content, link)
        cursor.execute(query, val)
        db.commit()
        return render_template('admin.html', message="success")
    else:
        return render_template('admin.html')