Crée ta propre application de bibliothèque personnelle avec Flask + MySQL

python -m venv venv

.\venv\Scripts\activate

source venv/bin/activate

pip install -r requirements.txt

bibliotheque/
├── app.py                # Initialise Flask et PyMySQL
├── run.py                # Lance l’appli en mode debug
├── config.py             # Paramètres & secrets centralisés
├── .env                  # Variables sensibles (non commitées)
├── requirements.txt      # Dépendances Python

├── db/
│   └── init_db.sql       # Script SQL de création + données de test

├── controllers/
│   └── book_controller.py  # Routes & logique métier

├── models/
│   └── book_model.py       # Requêtes SQL vers la base

├── utils/
│   └── helpers.py          # Fonctions utilitaires (ex : dictfetchall)

├── templates/            # HTML + Jinja2 (structure des vues)
│   ├── layout.html
│   ├── index.html
│   ├── add_book.html
│   ├── edit_book.html
│   └── stats.html

└── static/               # Fichiers statiques (CSS, JS, images)
    ├── css/style.css
    └── js/chart.js

import os
from dotenv import load_dotenv

load_dotenv()  # Charge automatiquement le fichier .env

class Config:
    SECRET_KEY        = os.getenv("SECRET_KEY", "devkey")
    MYSQL_HOST        = os.getenv("MYSQL_HOST", "localhost")
    MYSQL_USER        = os.getenv("MYSQL_USER", "root")
    MYSQL_PASSWORD    = os.getenv("MYSQL_PASSWORD", "")
    MYSQL_DB          = os.getenv("MYSQL_DB", "bibliotheque")
    MYSQL_CURSORCLASS = "DictCursor"  # Retourne les résultats en dictionnaires

SECRET_KEY=une_vraie_clef_ultra_secrete
MYSQL_USER=root
MYSQL_PASSWORD=monMDP
MYSQL_DB=bibliotheque

# app.py
from flask import Flask
from flask_mysqldb import MySQL
from config import Config
from models.book_model import BookModel
from controllers.book_controller import book_bp

mysql = MySQL()  # instance unique

def create_app() -> Flask:
    app = Flask(__name__)
    app.config.from_object(Config)
    mysql.init_app(app)  # Connexion MySQL via flask_mysqldb

    # Injection du modèle dans le contrôleur
    model = BookModel(mysql)

    import controllers.book_controller as book_controller
    book_controller.model = model

    app.register_blueprint(book_bp)
    return app

app = create_app()

# run.py
from app import app

if __name__ == "__main__":
    app.run(debug=True)  # Auto‑reload et affichage des erreurs en clair

from utils.helpers import dictfetchall
from datetime import date, timedelta

class BookModel:
    def __init__(self, mysql):
        self.db = mysql

    # --- CRUD --------------------------------------------------
    def get_all_books(self):
        cur = self.db.connection.cursor()
        cur.execute("SELECT * FROM books ORDER BY id DESC")
        res = dictfetchall(cur)
        cur.close()
        return res

    def get_filtered_books(self, status=None, author=None):
        sql, params = "SELECT * FROM books WHERE 1=1", []
        if status:
            sql += " AND status=%s"; params.append(status)
        if author:
            sql += " AND author LIKE %s"; params.append(f"%{author}%")
        sql += " ORDER BY id DESC"

        cur = self.db.connection.cursor()
        cur.execute(sql, params)
        res = dictfetchall(cur)
        cur.close()
        return res

    def insert_book(self, data):
        cur = self.db.connection.cursor()
        cur.execute("""
            INSERT INTO books (title, author, summary, read_date, note, status)
            VALUES (%s,%s,%s,%s,%s,%s)
        """, (
            data['title'],
            data['author'],
            data['summary'],
            data['read_date'] or None,
            data['note'] or None,
            data['status']
        ))
        self.db.connection.commit()
        cur.close()

    # ... méthodes update_book, delete_book à l’identique ...

    # --- Statistiques ------------------------------------------
    def stats_summary(self):
        cur = self.db.connection.cursor()
        cur.execute("""
            SELECT COUNT(*) total,
                   SUM(status='à lire')  to_read,
                   SUM(status='en cours') reading,
                   SUM(status='lu')      finished,
                   ROUND(AVG(note),2)    avg_note
            FROM books
        """)
        row = cur.fetchone()
        cur.close()
        return row

    def stats_status(self):
        cur = self.db.connection.cursor()
        cur.execute("SELECT status, COUNT(*) nb FROM books GROUP BY status")
        rows = dictfetchall(cur)
        cur.close()
        return {
            "labels": [r['status'] for r in rows],
            "values": [r['nb'] for r in rows]
        }

    def stats_monthly(self):
        cur = self.db.connection.cursor()
        cur.execute("""
            SELECT DATE_FORMAT(read_date,'%Y-%m') ym, COUNT(*) nb
            FROM books
            WHERE status='lu' AND read_date IS NOT NULL
              AND read_date >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
            GROUP BY ym ORDER BY ym
        """)
        rows = dictfetchall(cur)
        cur.close()

        labels, values = [], []
        today = date.today().replace(day=1)
        for i in range(11, -1, -1):
            label = (today - timedelta(days=i * 30)).strftime("%Y-%m")
            labels.append(label)
            values.append(next((r['nb'] for r in rows if r['ym'] == label), 0))

        return {"labels": labels, "values": values}

from flask import Blueprint, render_template, request, redirect, url_for, flash

book_bp = Blueprint("book_bp", __name__)
model = None  # Injecté depuis app.py

@book_bp.route("/")
def index():
    status = request.args.get("status")
    author = request.args.get("author")
    books = model.get_filtered_books(status, author) if (status or author) else model.get_all_books()
    return render_template("index.html", books=books)

@book_bp.route("/add", methods=["GET", "POST"])
def add_book():
    if request.method == "POST":
        model.insert_book(request.form)
        flash("Livre ajouté ✅")
        return redirect(url_for("book_bp.index"))
    return render_template("add_book.html")

@book_bp.route("/edit/<int:book_id>", methods=["GET", "POST"])
def edit_book(book_id):
    book = model.get_by_id(book_id)
    if not book:
        flash("Livre introuvable", "warning")
        return redirect(url_for("book_bp.index"))

    if request.method == "POST":
        model.update_book(book_id, request.form)
        flash("Mise à jour réussie 💾")
        return redirect(url_for("book_bp.index"))

    return render_template("edit_book.html", book=book)

@book_bp.route("/delete/<int:book_id>")
def delete_book(book_id):
    model.delete_book(book_id)
    flash("Livre supprimé 🗑️")
    return redirect(url_for("book_bp.index"))

@book_bp.route("/stats")
def stats():
    return render_template("stats.html",
        summary_cards=[
            {"label": "Total",       "value": model.stats_summary()["total"]},
            {"label": "À lire",      "value": model.stats_summary()["to_read"]},
            {"label": "En cours",    "value": model.stats_summary()["reading"]},
            {"label": "Terminés",    "value": model.stats_summary()["finished"]},
            {"label": "Note moy.",   "value": model.stats_summary()["avg_note"] or "–"},
        ],
        stats_status=model.stats_status(),
        stats_monthly=model.stats_monthly()
    )

# utils/helpers.py
def dictfetchall(cursor):
    """Transforme le résultat en liste de dictionnaires."""
    return [row for row in cursor.fetchall()]

<!-- templates/layout.html -->
<!DOCTYPE html>
<html lang="fr" data-bs-theme="dark">
<head>
  <meta charset="UTF-8">
  <meta name="viewport" content="width=device-width,initial-scale=1">
  <title>{% block title %}Ma Bibliothèque{% endblock %}</title>
  <link href="https://cdn.jsdelivr.net/npm/[email protected]/dist/css/bootstrap.min.css" rel="stylesheet">
  <link href="{{ url_for('static', filename='css/style.css') }}" rel="stylesheet">
  {% block extra_head %}{% endblock %}
</head>
<body>
  <nav class="navbar navbar-expand-lg navbar-dark bg-dark">
    <a class="navbar-brand ms-3" href="{{ url_for('book_bp.index') }}">📚 Bibliothèque</a>
    <button class="navbar-toggler" data-bs-toggle="collapse" data-bs-target="#nav">
      <span class="navbar-toggler-icon"></span>
    </button>
    <div class="collapse navbar-collapse" id="nav">
      <ul class="navbar-nav ms-auto me-3">
        <li class="nav-item">
          <a class="nav-link {% if request.endpoint=='book_bp.index' %}active{% endif %}" href="{{ url_for('book_bp.index') }}">Livres</a>
        </li>
        <li class="nav-item">
          <a class="nav-link {% if request.endpoint=='book_bp.stats' %}active{% endif %}" href="{{ url_for('book_bp.stats') }}">Statistiques</a>
        </li>
        <li class="nav-item">
          <a class="nav-link" href="{{ url_for('book_bp.add_book') }}">➕ Ajouter</a>
        </li>
      </ul>
    </div>
  </nav>

  <main class="container py-4">
    {% with messages = get_flashed_messages() %}
      {% if messages %}
        {% for m in messages %}
          <div class="alert alert-info">{{ m }}</div>
        {% endfor %}
      {% endif %}
    {% endwith %}
    {% block content %}{% endblock %}
  </main>

  <script src="https://cdn.jsdelivr.net/npm/[email protected]/dist/js/bootstrap.bundle.min.js"></script>
  {% block extra_js %}{% endblock %}
</body>
</html>

:root {
  --accent: #6366f1;
}
body {
  background: #111827;
  color: #f1f5f9;
}
.btn-accent {
  background: var(--accent);
  border: none;
}
.btn-accent:hover {
  background: #4f46e5;
}
.card-dark {
  background: #1f2937;
  border: 1px solid #334155;
  border-radius: 1rem;
}

document.addEventListener("DOMContentLoaded", () => {
  const parseJSON = id => JSON.parse(document.getElementById(id).textContent);

  // Doughnut chart : répartition par statut
  const st = parseJSON("jsonStatus");
  new Chart(document.getElementById("chartStatus"), {
    type: "doughnut",
    data: { labels: st.labels, datasets: [{ data: st.values }] },
    options: { plugins: { legend: { position: "bottom" } } }
  });

  // Line chart : progression mensuelle
  const mo = parseJSON("jsonMonthly");
  new Chart(document.getElementById("chartMonthly"), {
    type: "line",
    data: { labels: mo.labels, datasets: [{ data: mo.values, tension: .3, fill: true }] },
    options: {
      scales: { y: { beginAtZero: true } },
      plugins: { legend: { display: false } }
    }
  });
});

-- Création de la base
CREATE DATABASE IF NOT EXISTS bibliotheque CHARACTER SET utf8mb4;
USE bibliotheque;

DROP TABLE IF EXISTS books;

CREATE TABLE books (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  author VARCHAR(255),
  summary TEXT,
  read_date DATE,
  note TINYINT CHECK (note BETWEEN 0 AND 10),
  status ENUM('à lire','en cours','lu') DEFAULT 'à lire'
);

mysql -u root -p < db/init_db.sql

python run.py