--- jupytext: text_representation: extension: .md format_name: myst format_version: 0.13 jupytext_version: 1.16.0 kernelspec: name: python3 display_name: Python 3 --- # ORM et Python avec SQLAlchemy ```{code-cell} python :tags: [hide-input] import matplotlib.pyplot as plt import matplotlib.patches as patches import numpy as np import seaborn as sns sns.set_theme(style="whitegrid", palette="muted", font_scale=1.1) ``` Écrire du SQL dans du code Python, c'est mélanger deux langages aux paradigmes opposés : SQL opère sur des ensembles de lignes, Python manipule des objets. Les ORM (*Object-Relational Mappers*) comblent ce fossé en projetant les tables sur des classes Python et les lignes sur des instances d'objets. **SQLAlchemy** est l'ORM de référence de l'écosystème Python : puissant, flexible, utilisé par Flask, FastAPI et des milliers de projets en production. SQLAlchemy propose deux niveaux d'abstraction complémentaires : - **SQLAlchemy Core** : constructeur de requêtes SQL expressif en Python, proche du SQL mais sans mapping objet. - **SQLAlchemy ORM** : mapping objet-relationnel complet, avec sessions, relations et chargement automatique. ## Pourquoi un ORM ? ```{prf:definition} :label: ch18-def-orm Un **ORM** (*Object-Relational Mapper*) est une bibliothèque qui établit une correspondance bidirectionnelle entre les tables d'une base relationnelle et les classes d'un langage orienté objet. Il traduit les opérations CRUD sur des objets en requêtes SQL, gère les connexions et les transactions, et matérialise les lignes en instances typées. L'ORM joue le rôle d'une couche d'abstraction entre le code métier et la base de données. ``` ```{prf:remark} :label: ch18-rem-orm-tradeoffs **Avantages** d'un ORM : productivité (moins de SQL boilerplate), portabilité entre SGBD (SQLite, PostgreSQL, MySQL), sécurité (requêtes paramétrées automatiques), navigation objet des relations. **Inconvénients** : abstraction qui fuit (le SQL généré peut être sous-optimal), courbe d'apprentissage, risque de N+1 queries si mal utilisé. La règle pragmatique : utiliser l'ORM pour le CRUD courant, écrire du SQL brut pour les requêtes analytiques complexes. ``` ## SQLAlchemy Core SQLAlchemy Core permet de construire des requêtes SQL de façon programmatique sans définir de classes de modèles. ```{prf:definition} :label: ch18-def-engine L'**Engine** est le point d'entrée de SQLAlchemy. Il encapsule une URL de connexion, un dialecte SQL (sqlite, postgresql, mysql…) et un pool de connexions. On le crée avec `create_engine(url)`. L'Engine ne crée pas de connexion immédiatement — il est *lazy*. La connexion est établie lors du premier contexte `with engine.connect() as conn`. ``` ```{code-cell} python from sqlalchemy import ( create_engine, MetaData, Table, Column, Integer, String, Float, ForeignKey, Date, Text, select, insert, update, delete, func, and_, or_ ) from sqlalchemy.orm import ( DeclarativeBase, Session, relationship, selectinload, joinedload, mapped_column, Mapped ) from datetime import date, timedelta import random # Engine SQLite en mémoire engine = create_engine("sqlite:///:memory:", echo=False) metadata = MetaData() # Définition des tables avec SQLAlchemy Core auteurs_table = Table("auteurs", metadata, Column("id", Integer, primary_key=True, autoincrement=True), Column("nom", String(100), nullable=False), Column("prenom", String(100), nullable=False), Column("nationalite", String(50)), ) livres_table = Table("livres", metadata, Column("id", Integer, primary_key=True, autoincrement=True), Column("titre", String(200), nullable=False), Column("annee", Integer), Column("prix", Float), Column("auteur_id", Integer, ForeignKey("auteurs.id"), nullable=False), ) emprunts_table = Table("emprunts", metadata, Column("id", Integer, primary_key=True, autoincrement=True), Column("livre_id", Integer, ForeignKey("livres.id"), nullable=False), Column("emprunteur", String(100), nullable=False), Column("date_debut", Date, nullable=False), Column("date_retour", Date), ) metadata.create_all(engine) # Insertion avec Core with engine.connect() as conn: conn.execute(insert(auteurs_table), [ {"nom": "Knuth", "prenom": "Donald", "nationalite": "Américain"}, {"nom": "Martin", "prenom": "Robert", "nationalite": "Américain"}, {"nom": "Fowler", "prenom": "Martin", "nationalite": "Britannique"}, {"nom": "Kleppmann","prenom": "Martin", "nationalite": "Allemand"}, ]) conn.execute(insert(livres_table), [ {"titre": "The Art of Computer Programming", "annee": 1968, "prix": 89.99, "auteur_id": 1}, {"titre": "Clean Code", "annee": 2008, "prix": 34.99, "auteur_id": 2}, {"titre": "The Pragmatic Programmer", "annee": 1999, "prix": 39.99, "auteur_id": 3}, {"titre": "Designing Data-Intensive Applications", "annee": 2017, "prix": 49.99, "auteur_id": 4}, {"titre": "Refactoring", "annee": 2018, "prix": 44.99, "auteur_id": 3}, ]) conn.execute(insert(emprunts_table), [ {"livre_id": 1, "emprunteur": "Alice", "date_debut": date(2024, 1, 10), "date_retour": date(2024, 2, 5)}, {"livre_id": 2, "emprunteur": "Bob", "date_debut": date(2024, 2, 1), "date_retour": date(2024, 2, 28)}, {"livre_id": 2, "emprunteur": "Alice", "date_debut": date(2024, 3, 5), "date_retour": None}, {"livre_id": 4, "emprunteur": "Clara", "date_debut": date(2024, 3, 12), "date_retour": None}, {"livre_id": 3, "emprunteur": "David", "date_debut": date(2024, 1, 20), "date_retour": date(2024, 2, 15)}, ]) conn.commit() # Requête Core : jointure avec alias stmt = ( select( livres_table.c.titre, auteurs_table.c.nom.label("auteur_nom"), livres_table.c.prix, ) .join(auteurs_table, livres_table.c.auteur_id == auteurs_table.c.id) .order_by(livres_table.c.prix.desc()) ) with engine.connect() as conn: result = conn.execute(stmt) print("Livres par prix décroissant :") for row in result: print(f" {row.titre[:40]:<40} | {row.auteur_nom:<10} | {row.prix:.2f} €") ``` ## SQLAlchemy ORM : modèles et sessions ```{prf:definition} :label: ch18-def-declarativebase **DeclarativeBase** est la classe de base de l'ORM moderne (SQLAlchemy 2.x). On en hérite pour définir des modèles : chaque sous-classe représente une table, chaque attribut `mapped_column(...)` représente une colonne. L'ORM maintient un registre interne qui associe les classes aux tables et permet l'introspection. ``` ```{prf:definition} :label: ch18-def-session La **Session** est l'unité de travail de l'ORM. Elle maintient un **cache d'identité** (chaque objet est chargé une seule fois par identifiant) et une **unit of work** (elle accumule les modifications et les envoie en bloc à la base lors du `flush` ou du `commit`). On l'instancie avec `Session(engine)` et on l'utilise dans un contexte `with`. ``` ```{code-cell} python # Définition des modèles ORM (base séparée, SQLite en mémoire) engine_orm = create_engine("sqlite:///:memory:", echo=False) class Base(DeclarativeBase): pass class Auteur(Base): __tablename__ = "auteurs" id: Mapped[int] = mapped_column(Integer, primary_key=True) nom: Mapped[str] = mapped_column(String(100), nullable=False) prenom: Mapped[str] = mapped_column(String(100), nullable=False) nationalite: Mapped[str] = mapped_column(String(50), nullable=True) livres: Mapped[list["Livre"]] = relationship("Livre", back_populates="auteur", cascade="all, delete-orphan") def __repr__(self): return f"Auteur({self.prenom} {self.nom})" class Livre(Base): __tablename__ = "livres" id: Mapped[int] = mapped_column(Integer, primary_key=True) titre: Mapped[str] = mapped_column(String(200), nullable=False) annee: Mapped[int] = mapped_column(Integer, nullable=True) prix: Mapped[float] = mapped_column(Float, nullable=True) auteur_id: Mapped[int] = mapped_column(ForeignKey("auteurs.id"), nullable=False) auteur: Mapped["Auteur"] = relationship("Auteur", back_populates="livres") emprunts: Mapped[list["Emprunt"]] = relationship("Emprunt", back_populates="livre") def __repr__(self): return f"Livre({self.titre[:30]})" class Emprunt(Base): __tablename__ = "emprunts" id: Mapped[int] = mapped_column(Integer, primary_key=True) livre_id: Mapped[int] = mapped_column(ForeignKey("livres.id"), nullable=False) emprunteur: Mapped[str] = mapped_column(String(100), nullable=False) date_debut: Mapped[date] = mapped_column(Date, nullable=False) date_retour: Mapped[date] = mapped_column(Date, nullable=True) livre: Mapped["Livre"] = relationship("Livre", back_populates="emprunts") def __repr__(self): return f"Emprunt({self.emprunteur}, {self.date_debut})" Base.metadata.create_all(engine_orm) # Peuplement with Session(engine_orm) as session: auteurs = [ Auteur(nom="Knuth", prenom="Donald", nationalite="Américain"), Auteur(nom="Martin", prenom="Robert", nationalite="Américain"), Auteur(nom="Fowler", prenom="Martin", nationalite="Britannique"), Auteur(nom="Kleppmann",prenom="Martin", nationalite="Allemand"), ] session.add_all(auteurs) session.flush() # obtenir les IDs livres = [ Livre(titre="The Art of Computer Programming", annee=1968, prix=89.99, auteur=auteurs[0]), Livre(titre="Clean Code", annee=2008, prix=34.99, auteur=auteurs[1]), Livre(titre="The Pragmatic Programmer", annee=1999, prix=39.99, auteur=auteurs[2]), Livre(titre="Designing Data-Intensive Applications", annee=2017, prix=49.99, auteur=auteurs[3]), Livre(titre="Refactoring", annee=2018, prix=44.99, auteur=auteurs[2]), ] session.add_all(livres) session.flush() emprunts = [ Emprunt(livre=livres[0], emprunteur="Alice", date_debut=date(2024,1,10), date_retour=date(2024,2,5)), Emprunt(livre=livres[1], emprunteur="Bob", date_debut=date(2024,2,1), date_retour=date(2024,2,28)), Emprunt(livre=livres[1], emprunteur="Alice", date_debut=date(2024,3,5), date_retour=None), Emprunt(livre=livres[3], emprunteur="Clara", date_debut=date(2024,3,12), date_retour=None), ] session.add_all(emprunts) session.commit() print("Base ORM créée et peuplée.") ``` ## Requêtes ORM ```{prf:example} :label: ch18-ex-requetes-orm Les requêtes ORM utilisent `select()` avec des classes plutôt que des tables. ``` ```{code-cell} python # Requêtes ORM illustratives with Session(engine_orm) as session: # 1. Tous les livres d'un auteur stmt = select(Livre).join(Auteur).where(Auteur.nom == "Fowler").order_by(Livre.annee) livres_fowler = session.scalars(stmt).all() print("Livres de Fowler :") for l in livres_fowler: print(f" - {l.titre} ({l.annee})") # 2. Agrégation : nombre de livres et prix moyen par nationalité stmt2 = ( select( Auteur.nationalite, func.count(Livre.id).label("nb_livres"), func.avg(Livre.prix).label("prix_moyen"), ) .join(Livre, Auteur.id == Livre.auteur_id) .group_by(Auteur.nationalite) .order_by(func.count(Livre.id).desc()) ) print("\nNb livres et prix moyen par nationalité :") with engine_orm.connect() as conn: for row in conn.execute(stmt2): print(f" {row.nationalite:<15} {row.nb_livres} livre(s) — {row.prix_moyen:.2f} € moyen") # 3. Livres actuellement empruntés (date_retour IS NULL) stmt3 = ( select(Livre.titre, Emprunt.emprunteur, Emprunt.date_debut) .join(Emprunt, Livre.id == Emprunt.livre_id) .where(Emprunt.date_retour == None) .order_by(Emprunt.date_debut) ) print("\nLivres actuellement empruntés :") with engine_orm.connect() as conn: for row in conn.execute(stmt3): print(f" '{row.titre[:35]}' — {row.emprunteur} depuis {row.date_debut}") # 4. Mise à jour (update) stmt4 = update(Livre).where(Livre.id == 1).values(prix=79.99) session.execute(stmt4) session.commit() l = session.get(Livre, 1) print(f"\nPrix mis à jour : {l.titre[:30]} → {l.prix} €") ``` ## Relations : one-to-many et many-to-many ```{prf:definition} :label: ch18-def-relations SQLAlchemy modélise les relations entre tables via `relationship()` : - **One-to-many** : `Auteur.livres` → un auteur a plusieurs livres. Le côté "many" porte la clé étrangère. - **Many-to-many** : via une **table d'association** (secondary table). Par exemple, un livre peut avoir plusieurs genres et un genre peut s'appliquer à plusieurs livres. ``` ```{prf:example} :label: ch18-ex-many-to-many Modélisation d'un lien Livre ↔ Genre (many-to-many) avec une table d'association : Une table `livre_genre` contient deux colonnes de clés étrangères : `livre_id` et `genre_id`. SQLAlchemy gère automatiquement les insertions dans cette table lorsqu'on manipule `livre.genres.append(genre)`. ``` ```{code-cell} python :tags: [hide-input] # Diagramme classe -> table fig, axes = plt.subplots(1, 2, figsize=(14, 7)) palette_orm = sns.color_palette("muted", 4) # --- Côté gauche : classes Python --- ax = axes[0] ax.set_xlim(0, 6); ax.set_ylim(0, 10); ax.axis('off') ax.set_title("Classes Python (ORM)", fontsize=12, fontweight='bold') classes = [ (0.3, 7.2, "Auteur", ["id: int", "nom: str", "prenom: str", "nationalite: str", "livres: List[Livre]"], palette_orm[0]), (0.3, 3.8, "Livre", ["id: int", "titre: str", "annee: int", "prix: float", "auteur: Auteur", "emprunts: List[Emprunt]"], palette_orm[1]), (0.3, 0.3, "Emprunt",["id: int", "emprunteur: str", "date_debut: date", "date_retour: date", "livre: Livre"], palette_orm[2]), ] for x, y, name, attrs, color in classes: header = patches.FancyBboxPatch((x, y + 0.6), 5.4, 0.65, boxstyle="round,pad=0.08", fc=color, alpha=0.85, ec="none") ax.add_patch(header) ax.text(x + 2.7, y + 0.95, name, ha='center', va='center', fontsize=10, color='white', fontweight='bold') body = patches.FancyBboxPatch((x, y - len(attrs)*0.45 + 0.45), 5.4, len(attrs)*0.45, boxstyle="round,pad=0.08", fc=color, alpha=0.25, ec=color, linewidth=0.8) ax.add_patch(body) for i, attr in enumerate(attrs): ax.text(x + 0.3, y + 0.25 - i*0.45, attr, va='center', fontsize=7.5, color='#333') # Flèches de relation ax.annotate('', xy=(3.0, 4.42), xytext=(3.0, 7.22), arrowprops=dict(arrowstyle='<->', color='#888', lw=1.5)) ax.text(3.3, 5.8, "1 → N", fontsize=8, color='#666') ax.annotate('', xy=(3.0, 1.05), xytext=(3.0, 3.82), arrowprops=dict(arrowstyle='<->', color='#888', lw=1.5)) ax.text(3.3, 2.4, "1 → N", fontsize=8, color='#666') # --- Côté droit : tables SQL --- ax2 = axes[1] ax2.set_xlim(0, 6); ax2.set_ylim(0, 10); ax2.axis('off') ax2.set_title("Tables SQL (base de données)", fontsize=12, fontweight='bold') tables = [ (0.3, 7.2, "auteurs", ["PK id INTEGER", "nom VARCHAR(100)", "prenom VARCHAR(100)", "nationalite VARCHAR(50)"], palette_orm[0]), (0.3, 3.8, "livres", ["PK id INTEGER", "titre VARCHAR(200)", "annee INTEGER", "prix FLOAT", "FK auteur_id -> auteurs"], palette_orm[1]), (0.3, 0.5, "emprunts",["PK id INTEGER", "emprunteur VARCHAR", "date_debut DATE", "date_retour DATE", "FK livre_id -> livres"], palette_orm[2]), ] for x, y, name, cols, color in tables: header = patches.FancyBboxPatch((x, y + 0.6), 5.4, 0.65, boxstyle="round,pad=0.08", fc=color, alpha=0.85, ec="none") ax2.add_patch(header) ax2.text(x + 2.7, y + 0.95, name, ha='center', va='center', fontsize=10, color='white', fontweight='bold') body = patches.FancyBboxPatch((x, y - len(cols)*0.45 + 0.45), 5.4, len(cols)*0.45, boxstyle="round,pad=0.08", fc=color, alpha=0.25, ec=color, linewidth=0.8) ax2.add_patch(body) for i, col in enumerate(cols): ax2.text(x + 0.3, y + 0.25 - i*0.45, col, va='center', fontsize=7.5, color='#c0392b' if col.startswith('PK') or col.startswith('FK') else '#333') ax2.annotate('', xy=(3.0, 4.42), xytext=(3.0, 7.22), arrowprops=dict(arrowstyle='<->', color='#888', lw=1.5)) ax2.annotate('', xy=(3.0, 1.22), xytext=(3.0, 3.82), arrowprops=dict(arrowstyle='<->', color='#888', lw=1.5)) # Flèche centrale reliant les deux diagrammes fig.text(0.5, 0.5, "↔ mapping ORM ↔", ha='center', va='center', fontsize=12, color='#555', style='italic') plt.suptitle("Mapping ORM : classes Python ↔ tables SQL", fontsize=13, fontweight='bold') plt.tight_layout() plt.show() ``` ## Lazy vs Eager loading et le problème N+1 ```{prf:definition} :label: ch18-def-lazy-eager Le **lazy loading** (chargement paresseux) est le comportement par défaut de SQLAlchemy ORM : les attributs de relation ne sont chargés que lorsqu'on y accède. Si on itère sur N objets et accède à la relation de chacun, cela génère N+1 requêtes SQL (1 pour la liste, 1 par objet pour la relation) — le **problème N+1**. L'**eager loading** charge les relations en une seule requête supplémentaire dès le départ : - `selectinload` : émet une deuxième requête `SELECT ... WHERE id IN (...)`. - `joinedload` : fait une jointure SQL et charge tout en une seule requête. ``` ```{prf:remark} :label: ch18-rem-n-plus-un Le problème N+1 est l'antipattern ORM le plus fréquent. Symptôme : le log SQL montre une requête principale suivie de dizaines de requêtes identiques (une par objet). Solution : utiliser `selectinload()` ou `joinedload()` dans les `select()`, ou configurer `lazy='selectin'` au niveau du modèle. ``` ```{code-cell} python import time # Simulation du problème N+1 vs eager loading with Session(engine_orm) as session: # -- N+1 (lazy loading simulé) -- t0 = time.perf_counter() livres_lazy = session.scalars(select(Livre)).all() # Accéder à auteur déclenche 1 requête par livre for l in livres_lazy: _ = l.auteur.nom # N requêtes supplémentaires t_lazy = (time.perf_counter() - t0) * 1000 session.expire_all() # vider le cache # -- Eager loading avec selectinload -- t0 = time.perf_counter() livres_eager = session.scalars( select(Livre).options(selectinload(Livre.auteur)) ).all() for l in livres_eager: _ = l.auteur.nom # données déjà chargées, pas de requête t_eager = (time.perf_counter() - t0) * 1000 print(f"Lazy loading (N+1) : {t_lazy:.2f} ms") print(f"Eager (selectinload) : {t_eager:.2f} ms") print(f"\nRelations chargées avec selectinload :") for l in livres_eager: print(f" {l.titre[:35]:<35} → {l.auteur.prenom} {l.auteur.nom}") ``` ```{code-cell} python :tags: [hide-input] # Graphique comparatif N+1 vs eager sur différentes tailles de catalogue n_livres_values = [5, 10, 20, 50, 100] # Simulation : N+1 croît linéairement, eager est quasi constant temps_n1 = [n * 0.18 + 0.5 for n in n_livres_values] # ms simulés temps_eager = [0.8 + n * 0.003 for n in n_livres_values] # ms simulés fig, ax = plt.subplots(figsize=(10, 5)) palette = sns.color_palette("muted", 2) ax.plot(n_livres_values, temps_n1, 'o-', color=palette[0], lw=2.2, ms=8, label='Lazy loading (N+1)') ax.plot(n_livres_values, temps_eager, 's-', color=palette[1], lw=2.2, ms=8, label='selectinload (eager)') ax.fill_between(n_livres_values, temps_n1, temps_eager, alpha=0.12, color=palette[0], label='Surcoût N+1') ax.set_xlabel("Nombre d'objets chargés") ax.set_ylabel("Temps total (ms, simulé)") ax.set_title("Problème N+1 vs Eager Loading", fontsize=13, fontweight='bold') ax.legend() ax.yaxis.grid(True, alpha=0.4) ax.set_axisbelow(True) plt.show() ``` ## Alembic : migrations de schéma ```{prf:definition} :label: ch18-def-alembic **Alembic** est l'outil de migration de schéma officiel de SQLAlchemy. Il gère l'évolution du schéma dans le temps sous forme de scripts versionnés, à la manière de Git pour les fichiers. Chaque migration est un fichier Python avec deux fonctions : `upgrade()` (appliquer le changement) et `downgrade()` (l'annuler). Alembic maintient une table `alembic_version` dans la base pour savoir quelle migration a été appliquée en dernier. ``` ```{prf:remark} :label: ch18-rem-alembic-workflow Workflow Alembic standard : 1. `alembic init alembic` — initialiser le répertoire de migrations. 2. Configurer `alembic.ini` avec l'URL de connexion. 3. `alembic revision --autogenerate -m "add colonne isbn"` — générer automatiquement le script de migration en comparant les modèles aux tables existantes. 4. Relire et ajuster le fichier généré (autogenerate n'est pas parfait). 5. `alembic upgrade head` — appliquer toutes les migrations en attente. 6. `alembic downgrade -1` — annuler la dernière migration. ``` Les blocs suivants illustrent la structure d'un fichier de migration Alembic. ```python # alembic/versions/001_add_isbn_to_livres.py """add colonne isbn to livres Revision ID: a1b2c3d4e5f6 Down revision: None Create Date: 2024-03-15 """ from alembic import op import sqlalchemy as sa def upgrade(): op.add_column( 'livres', sa.Column('isbn', sa.String(20), nullable=True) ) op.create_index('ix_livres_isbn', 'livres', ['isbn']) def downgrade(): op.drop_index('ix_livres_isbn', table_name='livres') op.drop_column('livres', 'isbn') ``` ```{prf:example} :label: ch18-ex-alembic-rename Renommer une colonne sans perte de données avec Alembic (PostgreSQL) : Dans le script `upgrade()`, on appelle `op.alter_column('livres', 'auteur_id', new_column_name='fk_auteur')`. Dans `downgrade()`, on inverse. Alembic génère le SQL adapté au dialecte cible — la syntaxe est différente selon que l'on cible SQLite, PostgreSQL ou MySQL. ``` ## Résumé SQLAlchemy offre deux modes d'accès complémentaires à une base de données relationnelle : Core pour le contrôle SQL fin, ORM pour la productivité et la navigation objet. ```{prf:remark} :label: ch18-rem-synthese Les points clés à retenir : - L'Engine encapsule l'URL et le pool de connexions ; la Session est l'unité de travail de l'ORM. - `DeclarativeBase` et `mapped_column` définissent le mapping classe → table de façon déclarative et typée (SQLAlchemy 2.x). - `relationship()` avec `back_populates` permet la navigation bidirectionnelle ; `cascade="all, delete-orphan"` propage les suppressions. - Le problème N+1 est l'antipattern ORM le plus courant ; `selectinload()` le résout en émettant une seule requête IN. - Alembic versionne les migrations de schéma ; `autogenerate` accélère la rédaction des scripts. - Règle d'or : ORM pour le CRUD, SQL brut (via `text()` ou Core) pour les requêtes analytiques complexes. ```