Chapitre 12 - Jointures SQL et Update.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Chapitre 11 - Jointures SQL et Update\n",
    "===\n",
    "\n",
    "Maintenant que nous pouvons faire des insertions, il nous revient de pouvoir faire les actions suivantes :\n",
    "- lier les auteurs à une publication\n",
    "- mettre à jour les données d'un objet\n",
    "\n",
    "Pour commencer, voici une petite idée :\n",
    "\n",
    "- nous allons rajouter un formulaire de création de données\n",
    "    - nous ajouterons la possibilité de lier les utilisateurs qui les rédigent à cet objet\n",
    "- nous allons rajouter un formulaire d'édition\n",
    "\n",
    "## Commencer par le commencement : la jointure\n",
    "\n",
    "En SQL, une jointure est une requête effectuée sur plusieurs tables ensemble. Cela permet ainsi d'éviter de multiplier les communications avec le serveur SQL. Cependant, mal faites, elles peuvent rapidement poser des problèmes de performance. \n",
    "\n",
    "L'avantage de SQLAlchemy est le suivant : les jointures sont faciles à effectuer. La jointure que nous voulons faire est simple : nos utilisateurs pouvant créer ou éditer des objets, on a une relation de n-n (ou *many-to-many*) entre notre Place et notre User. Dans ce genre de conditions, on crée une table de liaison entre nos objets: c'est la table authorship !\n",
    "\n",
    "**Note:** La documentation des jointures est disponible à cette adresse : http://docs.sqlalchemy.org/en/latest/orm/basic_relationships.html\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "# On remet en place la configuration Flask / MySQL\n",
    "\n",
    "from flask import Flask\n",
    "from flask_sqlalchemy import SQLAlchemy\n",
    "\n",
    "app = Flask(\"Nom\")\n",
    "app.config['SQLALCHEMY_DATABASE_URI'] = 'sqlite:///cours-flask/db.sqlite'\n",
    "app.config[\"SQLALCHEMY_TRACK_MODIFICATIONS\"] = False\n",
    "db = SQLAlchemy(app)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Code nécessaire\n",
    "import datetime\n",
    "\n",
    "\n",
    "class Authorship(db.Model):\n",
    "    __tablename__ = \"authorship\"\n",
    "    authorship_id = db.Column(db.Integer, nullable=True, autoincrement=True, primary_key=True)\n",
    "    authorship_place_id = db.Column(db.Integer, db.ForeignKey('place.place_id'))\n",
    "    authorship_user_id = db.Column(db.Integer, db.ForeignKey('user.user_id'))\n",
    "    authorship_date = db.Column(db.DateTime, default=datetime.datetime.utcnow)\n",
    "    user = db.relationship(\"User\", back_populates=\"authorships\")\n",
    "    place = db.relationship(\"Place\", back_populates=\"authorships\")\n",
    "\n",
    "class User(db.Model):\n",
    "    user_id = db.Column(db.Integer, unique=True, nullable=False, primary_key=True, autoincrement=True)\n",
    "    user_nom = db.Column(db.Text, nullable=False)\n",
    "    user_login = db.Column(db.String(45), nullable=False, unique=True)\n",
    "    user_email = db.Column(db.Text, nullable=False)\n",
    "    user_password = db.Column(db.String(100), nullable=False)\n",
    "    # Seul changement pour user\n",
    "    authorships = db.relationship(\"Authorship\", back_populates=\"user\")\n",
    "    \n",
    "class Place(db.Model):\n",
    "    place_id = db.Column(db.Integer, unique=True, nullable=False, primary_key=True, autoincrement=True)\n",
    "    place_nom = db.Column(db.Text)\n",
    "    place_description = db.Column(db.Text)\n",
    "    place_longitude = db.Column(db.Float)\n",
    "    place_latitude = db.Column(db.Float)\n",
    "    place_type = db.Column(db.String(45))\n",
    "    # Seul changement pour lieu\n",
    "    authorships = db.relationship(\"Authorship\", back_populates=\"place\")\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Lecture du code\n",
    "\n",
    "#### Construction \"normale\" du modèle\n",
    "\n",
    "```python\n",
    "class Authorship(db.Model):\n",
    "    __tablename__ = \"authorship\"\n",
    "    authorship_id = db.Column(db.Integer, nullable=True, autoincrement=True, primary_key=True)\n",
    "    authorship_place_id = db.Column(db.Integer, db.ForeignKey('place.place_id'))\n",
    "    authorship_user_id = db.Column(db.Integer, db.ForeignKey('user.user_id'))\n",
    "    authorship_date = db.Column(db.DateTime, default=datetime.datetime.utcnow)\n",
    "```\n",
    "\n",
    "- On crée un `db.Model` `Authorship`\n",
    "    - On en profite pour introduire `__tablename__` qui permet de spécifier un nom de table pour SQLAlchemy. Par défaut, SQLAlchemy utilise la version minuscule de la classe.\n",
    "- On rajoute un champ `authorship_id` qui n'est pas très novateur\n",
    "- On rajoute un champ `authorship_date` :\n",
    "    - comme défaut, on utilise la fonction `datetime.utcnow`. Elle sera executée à chaque insertion\n",
    "- On rajoute un champ `authorship_place_id`\n",
    "    - Ce champ comporte une option `ForeignKey` qui lie au champ `place.place_id` qui permettra de les liens ensemble\n",
    "    - On utilise les guillemets pour la `ForeignKey` qui correspondentà la syntaxe MySQL et non à la syntaxe python (`nomdetable.champ`)\n",
    "- On rajoute un champ `authorship_user_id` construit en mirroir du champ précédent\n",
    "\n",
    "##### Ce que l'on a appris en plus :\n",
    "\n",
    "- `default` peut prendre une fonction. Pour que la fonction soit exécutée à l'enregistrement, on fournit simplement le nom de la fonction.\n",
    "- `ForeignKey` permet de lier un champ à un autre champ d'une autre table\n",
    "- On peut utiliser `__tablename__` pour imposer un nom de table\n",
    "\n",
    "#### Construction des relations en python\n",
    "\n",
    "On distingue 4 nouveautés, dans les trois classes, introduites via `db.relationship()`.\n",
    "\n",
    "```python\n",
    "class Authorship(db.Model):\n",
    "    # ...\n",
    "    user = db.relationship(\"User\", back_populates=\"authorships\")\n",
    "    place = db.relationship(\"Place\", back_populates=\"authorships\")\n",
    "\n",
    "class User(db.Model):\n",
    "    # ...\n",
    "    authorships = db.relationship(\"Authorship\", back_populates=\"user\")\n",
    "    \n",
    "class Place(db.Model):\n",
    "    # ...\n",
    "    authorships = db.relationship(\"Authorship\", back_populates=\"place\")\n",
    "```\n",
    "\n",
    "**Réflexion:** Avant de lire les exemples, avez-vous une idée de leur effet ?\n",
    "\n",
    "##### Exemple"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Exécuter une seule fois si possible\n",
    "\n",
    "# On récupère un lieu\n",
    "place_2 = Place.query.get(2)\n",
    "# On récupère un utilisateur\n",
    "user_1 = User.query.get(1)\n",
    "# On crée un lien d'autorité\n",
    "a_ecrit = Authorship(user=user_1, place=place_2)\n",
    "# On enregistre\n",
    "db.session.add(a_ecrit)\n",
    "db.session.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "for authorship in Place.query.get(2).authorships:\n",
    "    print(\"{auteur} a écrit à {moment}\".format(\n",
    "        auteur=authorship.user.user_nom,\n",
    "        moment=authorship.authorship_date\n",
    "    ))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "###  `db.relationship`\n",
    "`db.relationship()` permet de construire des relations directes entre les objets et de naviguer entre eux. Ainsi, dans l'insertion, on n'utilise pas `place_id` ou `user_id`, mais bien `place` et `user`. \n",
    "\n",
    "La fonction `db.relationship` est tout à fait différente de `db.Column()` en ce qu'elle n'intervient pas sur la structure MySQL de vos classes, mais permet simplement de connecter vos différentes classes. Elle est construite sur deux paramètres:\n",
    "- le premier paramètre correspond au nom de la classe Python qui est liée à la propriété\n",
    "- le second paramètre (`back_populates`), qui est optionnel, correspond à la propriété dans la classe cible qui contient la même information en miroir. Ainsi `Authorship.user` lie vers `User` tandis que `User.authorships` lie vers `Authorship`.\n",
    "\n",
    "En résumé, on a en syntaxe :\n",
    "\n",
    "```python\n",
    "class A(db.Model):\n",
    "    propriete_de_relation = db.relationship(\n",
    "        \"NomDeLaClasseLiée\",\n",
    "        back_populates=\"a\"\n",
    "    )\n",
    "class NomDeLaClasseLiée(db.Model):\n",
    "    a = db.relationship(\n",
    "        \"A\",\n",
    "        back_populates=\"propriete_de_relation\"\n",
    "    )\n",
    "```\n",
    "\n",
    "![Schema](images/BackPopulate.png)\n",
    "\n",
    "### Exercice\n",
    "\n",
    "L'exercice se fait dans un notebook externe : [lien vers l'exercice](./Chapitre%2012%20-%20Jointures%20SQL%20et%20Update%20-%20Exercice%20Jointure.ipynb)\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Update, vous avez dit Update ?\n",
    "\n",
    "En SQL\n",
    "et en gestion de données, on parle généralement de CRUD : Create-Read-Update-Delete. Nous avons vu les deux premiers, le quatrième s'écrite simplement `Place.query.get(1).delete()` ce qui n'est pas très compliqué... Alors, comment corriger un enregistrement ?\n",
    "\n",
    "La modification d'une ligne SQL se fait assez simplement :\n",
    "1. On récupère l'objet\n",
    "2. On modifie les propriétés que l'on souhaite modifier\n",
    "3. On ajoute l'objet dans à la session de changement\n",
    "4. On commit\n",
    "\n",
    "Soit :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# 1.\n",
    "place = Place.query.filter(Place.place_nom.like(\"%settlement%\")).first()\n",
    "print(place.place_nom)\n",
    "# 2.\n",
    "place.place_nom = \"Lipara\"\n",
    "# 3. \n",
    "db.session.add(place)\n",
    "# 4.\n",
    "db.session.commit()\n",
    "\n",
    "# Résultats:\n",
    "\n",
    "print(Place.query.filter(Place.place_nom.like(\"%settlement%\")).count())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Exercide\n",
    "\n",
    "Ajouter un formulaire d'édition de lieu pour le projet Boatlas. \n",
    "\n",
    "1. On ne pourra pas changer l'id\n",
    "2. La mise à jour rajoutera un lien d'autorité vers l'éditeur"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.6.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}