10.4 Python (Flask) with MySQL

# Amazon Linux 2023 / CentOS Stream 9
sudo yum install -y python3 python3-pip git
# Ubuntu
sudo apt install -y python3 python3-venv python3-pip git

sudo mkdir -p /opt/flaskapp && sudo chown $USER:$USER /opt/flaskapp
cd /opt/flaskapp
python3 -m venv venv
source venv/bin/activate
pip install --upgrade pip
pip install flask pymysql cryptography gunicorn
pip freeze > requirements.txt

cat > /opt/flaskapp/.env <<'EOF'
DB_HOST=127.0.0.1
DB_USER=appuser
DB_PASS=App@Pass123
DB_NAME=appdb
EOF
chmod 600 /opt/flaskapp/.env

cat > /opt/flaskapp/app.py <<'EOF'
import os
import pymysql
from flask import Flask, jsonify, redirect, render_template_string, request

app = Flask(__name__)

def get_db():
    return pymysql.connect(
        host=os.getenv("DB_HOST", "127.0.0.1"),
        user=os.getenv("DB_USER", "appuser"),
        password=os.getenv("DB_PASS", ""),
        database=os.getenv("DB_NAME", "appdb"),
        cursorclass=pymysql.cursors.DictCursor,
    )

PAGE = """
<!DOCTYPE html><html><head><meta charset="utf-8"><title>Flask + MySQL</title></head>
<body style="font-family:Arial;max-width:700px;margin:30px auto">
<h1>Students (Flask + MySQL)</h1>
<table border="1" cellpadding="6"><tr><th>ID</th><th>Name</th><th>City</th></tr>
{% for s in rows %}<tr><td>{{ s.id }}</td><td>{{ s.name }}</td><td>{{ s.city }}</td></tr>{% endfor %}
</table>
<h3>Add student</h3>
<form method="post" action="/add">
  <input name="name" placeholder="Name" required>
  <input name="city" placeholder="City" required>
  <button>Add</button>
</form>
</body></html>
"""

@app.route("/")
def index():
    conn = get_db()
    with conn, conn.cursor() as cur:
        cur.execute("SELECT id, name, city FROM students ORDER BY id")
        rows = cur.fetchall()
    return render_template_string(PAGE, rows=rows)

@app.route("/add", methods=["POST"])
def add():
    conn = get_db()
    with conn, conn.cursor() as cur:
        cur.execute("INSERT INTO students (name, city) VALUES (%s, %s)",
                    (request.form["name"], request.form["city"]))
        conn.commit()
    return redirect("/")

@app.route("/api/students")
def api_students():
    conn = get_db()
    with conn, conn.cursor() as cur:
        cur.execute("SELECT id, name, city FROM students")
        return jsonify(cur.fetchall())

@app.route("/health")
def health():
    return {"status": "ok"}

if __name__ == "__main__":
    app.run(host="127.0.0.1", port=5000, debug=False)
EOF

cd /opt/flaskapp && source venv/bin/activate
set -a && source .env && set +a            # load variables from .env into this shell
gunicorn --bind 127.0.0.1:5000 app:app &   # start in background for a quick test
sleep 2
curl -s http://127.0.0.1:5000/api/students
kill %1                                     # stop the test server
deactivate

sudo tee /etc/systemd/system/flaskapp.service > /dev/null <<EOF
[Unit]
Description=Flask app served by Gunicorn
After=network.target mariadb.service mysql.service

[Service]
User=$USER
Group=$USER
WorkingDirectory=/opt/flaskapp
EnvironmentFile=/opt/flaskapp/.env
ExecStart=/opt/flaskapp/venv/bin/gunicorn --workers 3 --bind 127.0.0.1:5000 app:app
Restart=always
RestartSec=3

[Install]
WantedBy=multi-user.target
EOF

sudo systemctl daemon-reload
sudo service flaskapp start
sudo systemctl enable flaskapp
sudo service flaskapp status
curl -s http://127.0.0.1:5000/health