Chapitre 12 - Jointures SQL et Update - Exercice Jointure.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Chapitre 11 - Jointures SQL et Update - Exercice Jointure\n",
    "==="
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "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": "markdown",
   "metadata": {},
   "source": [
    "Complétez le code ci-dessous pour ajouter une relation entre les noms de lieu et et les lieux en utilisant le schéma ci-dessous :\n",
    "\n",
    "\n",
    "![Modele](images/datamodel.png)\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "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",
    "    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",
    "    authorships = db.relationship(\"Authorship\", back_populates=\"place\")\n",
    "    # A compléter ?\n",
    "    \n",
    "    \n",
    "class Variante(db.Model):\n",
    "    # A compléter\n",
    "    variante_id\n",
    "    variante_nom\n",
    "    variante_lang_code\n",
    "    variante_place_id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "place = Place.query.get(13)\n",
    "print(place.place_nom)\n",
    "# Executer l'insertion une seule fois\n",
    "if Variante.query.count() == 0:\n",
    "    place.variantes_nom.append(Variante(variante_nom=\"Garonne\", variante_lang_code=\"fre\"))\n",
    "    place.variantes_nom.append(Variante(variante_nom=\"Garona\", variante_lang_code=\"esp\"))\n",
    "    db.session.add(place)\n",
    "    db.session.commit()\n",
    "# Cette ligne affiche \"Garonne\"\n",
    "print(Variante.query.get(1).variante_nom)\n",
    "# Cette ligne affiche ['Garonne', 'Garona']\n",
    "print([v.variante_nom for v in place.query.get(13).variantes_nom])"
   ]
  }
 ],
 "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
}