034 - SQLAlchemy ORM.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SQLAlchemy ORM"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Comme précédemment, on aura besoin d'un *engine* pour parler à la base de données :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine\n",
    "\n",
    "engine = create_engine(\"sqlite:///:memory:\", echo=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Créer des tables\n",
    "\n",
    "Cette fois-ci on ne va pas déclarer les tables explicitements, mais les générer automatiquement à partir des classes Python correspondantes :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import Column, Integer, String\n",
    "from sqlalchemy.ext.declarative import declarative_base\n",
    "\n",
    "Base = declarative_base()\n",
    "\n",
    "class Fruit(Base):\n",
    "    __tablename__ = 'fruits'\n",
    "\n",
    "    id = Column(Integer, primary_key=True)\n",
    "    nom = Column(String)\n",
    "    \n",
    "    def __repr__(self):\n",
    "        return f\"<Fruit(nom={self.nom})>\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "On peut retrouver la définition de la table :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Table('fruits', MetaData(bind=None), Column('id', Integer(), table=<fruits>, primary_key=True, nullable=False), Column('nom', String(), table=<fruits>), schema=None)"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "Fruit.__table__"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Et pour créer les tables en base, comme précédemment :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2019-12-10 17:40:06,413 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1\n",
      "2019-12-10 17:40:06,415 INFO sqlalchemy.engine.base.Engine ()\n",
      "2019-12-10 17:40:06,416 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARCHAR(60)) AS anon_1\n",
      "2019-12-10 17:40:06,416 INFO sqlalchemy.engine.base.Engine ()\n",
      "2019-12-10 17:40:06,417 INFO sqlalchemy.engine.base.Engine PRAGMA main.table_info(\"fruits\")\n",
      "2019-12-10 17:40:06,418 INFO sqlalchemy.engine.base.Engine ()\n",
      "2019-12-10 17:40:06,419 INFO sqlalchemy.engine.base.Engine PRAGMA temp.table_info(\"fruits\")\n",
      "2019-12-10 17:40:06,420 INFO sqlalchemy.engine.base.Engine ()\n",
      "2019-12-10 17:40:06,421 INFO sqlalchemy.engine.base.Engine \n",
      "CREATE TABLE fruits (\n",
      "\tid INTEGER NOT NULL, \n",
      "\tnom VARCHAR, \n",
      "\tPRIMARY KEY (id)\n",
      ")\n",
      "\n",
      "\n",
      "2019-12-10 17:40:06,421 INFO sqlalchemy.engine.base.Engine ()\n",
      "2019-12-10 17:40:06,422 INFO sqlalchemy.engine.base.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "Base.metadata.create_all(engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## L'élément clé : la session\n",
    "\n",
    "Pour gérer la correspondance entre les lignes des tables dans la base et les objets Python, on va avoir besoin d'un objet qu'on appelle la **session**."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "from sqlalchemy.orm import sessionmaker\n",
    "\n",
    "Session = sessionmaker(bind=engine)  # étape 1 : créer une « session factory »\n",
    "\n",
    "db_session = Session()  # étape 2 : instancier une session"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Insérer des données"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "for nom in [\"pomme\", \"banane\", \"kiwi\"]:\n",
    "    fruit = Fruit(nom=nom)\n",
    "    db_session.add(fruit)  # la session enregistre cet objet comme étant nouveau"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2019-12-10 17:40:06,438 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2019-12-10 17:40:06,440 INFO sqlalchemy.engine.base.Engine INSERT INTO fruits (nom) VALUES (?)\n",
      "2019-12-10 17:40:06,440 INFO sqlalchemy.engine.base.Engine ('pomme',)\n",
      "2019-12-10 17:40:06,441 INFO sqlalchemy.engine.base.Engine INSERT INTO fruits (nom) VALUES (?)\n",
      "2019-12-10 17:40:06,442 INFO sqlalchemy.engine.base.Engine ('banane',)\n",
      "2019-12-10 17:40:06,442 INFO sqlalchemy.engine.base.Engine INSERT INTO fruits (nom) VALUES (?)\n",
      "2019-12-10 17:40:06,442 INFO sqlalchemy.engine.base.Engine ('kiwi',)\n",
      "2019-12-10 17:40:06,443 INFO sqlalchemy.engine.base.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "db_session.commit()  # les nouveau objets sont insérés en base"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Récupérer des données"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2019-12-10 17:40:06,449 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2019-12-10 17:40:06,450 INFO sqlalchemy.engine.base.Engine SELECT fruits.id AS fruits_id, fruits.nom AS fruits_nom \n",
      "FROM fruits \n",
      "WHERE fruits.nom = ?\n",
      "2019-12-10 17:40:06,450 INFO sqlalchemy.engine.base.Engine ('pomme',)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'0x1f266b4c9c8'"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pomme = db_session.query(Fruit).filter_by(nom=\"pomme\").one()\n",
    "hex(id(pomme))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2019-12-10 17:40:06,458 INFO sqlalchemy.engine.base.Engine SELECT fruits.id AS fruits_id, fruits.nom AS fruits_nom \n",
      "FROM fruits \n",
      "WHERE fruits.nom = ?\n",
      "2019-12-10 17:40:06,459 INFO sqlalchemy.engine.base.Engine ('pomme',)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'0x1f266b4c9c8'"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pomme = db_session.query(Fruit).filter_by(nom=\"pomme\").one()\n",
    "hex(id(pomme))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2019-12-10 17:40:06,468 INFO sqlalchemy.engine.base.Engine SELECT fruits.id AS fruits_id, fruits.nom AS fruits_nom \n",
      "FROM fruits \n",
      "WHERE fruits.nom = ?\n",
      "2019-12-10 17:40:06,468 INFO sqlalchemy.engine.base.Engine ('banane',)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<Fruit(nom=banane)>"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "banane = db_session.query(Fruit).filter_by(nom=\"banane\").one()\n",
    "banane"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2019-12-10 17:40:06,477 INFO sqlalchemy.engine.base.Engine SELECT fruits.id AS fruits_id, fruits.nom AS fruits_nom \n",
      "FROM fruits\n",
      "2019-12-10 17:40:06,478 INFO sqlalchemy.engine.base.Engine ()\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "[<Fruit(nom=pomme)>, <Fruit(nom=banane)>, <Fruit(nom=kiwi)>]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db_session.query(Fruit).all()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2019-12-10 17:40:06,485 INFO sqlalchemy.engine.base.Engine SELECT fruits.id AS fruits_id, fruits.nom AS fruits_nom \n",
      "FROM fruits \n",
      "WHERE fruits.nom = ?\n",
      "2019-12-10 17:40:06,486 INFO sqlalchemy.engine.base.Engine ('pomme',)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "(1, 'pomme')"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db_session.query(Fruit.id, Fruit.nom).filter_by(nom=\"pomme\").one()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Modifier des données"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "pomme.nom = \"poire\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "IdentitySet([<Fruit(nom=poire)>])"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db_session.dirty"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "banane.nom = \"ananas\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "IdentitySet([<Fruit(nom=poire)>, <Fruit(nom=ananas)>])"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db_session.dirty"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2019-12-10 17:40:06,517 INFO sqlalchemy.engine.base.Engine UPDATE fruits SET nom=? WHERE fruits.id = ?\n",
      "2019-12-10 17:40:06,517 INFO sqlalchemy.engine.base.Engine (('poire', 1), ('ananas', 2))\n",
      "2019-12-10 17:40:06,518 INFO sqlalchemy.engine.base.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "db_session.commit()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Relations\n",
    "\n",
    "Ajoutons une autre classe, et une relation avec la première :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2019-12-10 17:40:06,527 INFO sqlalchemy.engine.base.Engine PRAGMA main.table_info(\"fruits\")\n",
      "2019-12-10 17:40:06,527 INFO sqlalchemy.engine.base.Engine ()\n",
      "2019-12-10 17:40:06,529 INFO sqlalchemy.engine.base.Engine PRAGMA main.table_info(\"barquettes\")\n",
      "2019-12-10 17:40:06,529 INFO sqlalchemy.engine.base.Engine ()\n",
      "2019-12-10 17:40:06,530 INFO sqlalchemy.engine.base.Engine PRAGMA temp.table_info(\"barquettes\")\n",
      "2019-12-10 17:40:06,530 INFO sqlalchemy.engine.base.Engine ()\n",
      "2019-12-10 17:40:06,531 INFO sqlalchemy.engine.base.Engine \n",
      "CREATE TABLE barquettes (\n",
      "\tid INTEGER NOT NULL, \n",
      "\tfruit_id INTEGER NOT NULL, \n",
      "\tpoids INTEGER NOT NULL, \n",
      "\tPRIMARY KEY (id), \n",
      "\tFOREIGN KEY(fruit_id) REFERENCES fruits (id)\n",
      ")\n",
      "\n",
      "\n",
      "2019-12-10 17:40:06,531 INFO sqlalchemy.engine.base.Engine ()\n",
      "2019-12-10 17:40:06,532 INFO sqlalchemy.engine.base.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy import ForeignKey\n",
    "from sqlalchemy.orm import relationship\n",
    "\n",
    "class Barquette(Base):\n",
    "    __tablename__ = \"barquettes\"\n",
    "\n",
    "    id = Column(Integer, primary_key=True)\n",
    "    fruit_id = Column(Integer, ForeignKey(\"fruits.id\"), nullable=False)  # clé étrangère\n",
    "    poids = Column(Integer, nullable=False)\n",
    "\n",
    "    fruit = relationship(Fruit)  # chargé à la demande\n",
    "    fruit = relationship(Fruit, lazy=\"joined\")  # toujours pré-chargé avec un join\n",
    "\n",
    "Base.metadata.create_all(engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "kaki = Fruit(nom=\"kaki\")\n",
    "barquette = Barquette(fruit=kaki, poids=500)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "db_session.add(barquette)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2019-12-10 17:40:06,549 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2019-12-10 17:40:06,550 INFO sqlalchemy.engine.base.Engine INSERT INTO fruits (nom) VALUES (?)\n",
      "2019-12-10 17:40:06,550 INFO sqlalchemy.engine.base.Engine ('kaki',)\n",
      "2019-12-10 17:40:06,551 INFO sqlalchemy.engine.base.Engine INSERT INTO barquettes (fruit_id, poids) VALUES (?, ?)\n",
      "2019-12-10 17:40:06,552 INFO sqlalchemy.engine.base.Engine (4, 500)\n",
      "2019-12-10 17:40:06,552 INFO sqlalchemy.engine.base.Engine COMMIT\n"
     ]
    }
   ],
   "source": [
    "db_session.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2019-12-10 17:40:06,557 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)\n",
      "2019-12-10 17:40:06,558 INFO sqlalchemy.engine.base.Engine SELECT barquettes.id AS barquettes_id, barquettes.fruit_id AS barquettes_fruit_id, barquettes.poids AS barquettes_poids \n",
      "FROM barquettes \n",
      "WHERE barquettes.id = ?\n",
      "2019-12-10 17:40:06,559 INFO sqlalchemy.engine.base.Engine (1,)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "4"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "barquette.fruit_id"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2019-12-10 17:40:06,566 INFO sqlalchemy.engine.base.Engine SELECT fruits.id AS fruits_id, fruits.nom AS fruits_nom \n",
      "FROM fruits \n",
      "WHERE fruits.id = ?\n",
      "2019-12-10 17:40:06,566 INFO sqlalchemy.engine.base.Engine (4,)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<Fruit(nom=kaki)>"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "barquette.fruit"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "500g de kaki\n"
     ]
    }
   ],
   "source": [
    "print(f\"{barquette.poids}g de {barquette.fruit.nom}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2019-12-10 17:40:06,580 INFO sqlalchemy.engine.base.Engine SELECT barquettes.id AS barquettes_id, barquettes.fruit_id AS barquettes_fruit_id, barquettes.poids AS barquettes_poids, fruits_1.id AS fruits_1_id, fruits_1.nom AS fruits_1_nom \n",
      "FROM barquettes LEFT OUTER JOIN fruits AS fruits_1 ON fruits_1.id = barquettes.fruit_id\n",
      "2019-12-10 17:40:06,581 INFO sqlalchemy.engine.base.Engine ()\n",
      "500g de kaki\n"
     ]
    }
   ],
   "source": [
    "db_session.expunge_all()\n",
    "for barquette in db_session.query(Barquette):\n",
    "    print(f\"{barquette.poids}g de {barquette.fruit.nom}\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2019-12-10 17:40:06,589 INFO sqlalchemy.engine.base.Engine SELECT barquettes.id AS barquettes_id, barquettes.fruit_id AS barquettes_fruit_id, barquettes.poids AS barquettes_poids, fruits_1.id AS fruits_1_id, fruits_1.nom AS fruits_1_nom \n",
      "FROM barquettes LEFT OUTER JOIN fruits AS fruits_1 ON fruits_1.id = barquettes.fruit_id\n",
      "2019-12-10 17:40:06,590 INFO sqlalchemy.engine.base.Engine ()\n",
      "500g de kaki\n"
     ]
    }
   ],
   "source": [
    "from sqlalchemy.orm import joinedload\n",
    "\n",
    "db_session.expunge_all()\n",
    "for barquette in db_session.query(Barquette).options(joinedload(\"fruit\")):\n",
    "    print(f\"{barquette.poids}g de {barquette.fruit.nom}\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Parler à plusieurs bases"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import create_engine\n",
    "from sqlalchemy import Column, Integer, String\n",
    "from sqlalchemy.ext.declarative import declarative_base\n",
    "from sqlalchemy.orm import sessionmaker\n",
    "\n",
    "engine1 = create_engine(\"sqlite:///base1.db\")\n",
    "\n",
    "Base1 = declarative_base()\n",
    "\n",
    "class Fruit(Base1):\n",
    "    __tablename__ = 'fruits'\n",
    "\n",
    "    id = Column(Integer, primary_key=True)\n",
    "    nom = Column(String)\n",
    "    \n",
    "Base1.metadata.create_all(engine1)\n",
    "\n",
    "Session1 = sessionmaker(bind=engine1)\n",
    "\n",
    "db_session1 = Session1()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [],
   "source": [
    "engine2 = create_engine(\"sqlite:///base2.db\")\n",
    "\n",
    "Base2 = declarative_base()\n",
    "\n",
    "class Client(Base2):\n",
    "    __tablename__ = 'clients'\n",
    "\n",
    "    id = Column(Integer, primary_key=True)\n",
    "    nom = Column(String)\n",
    "    \n",
    "Base2.metadata.create_all(engine2)\n",
    "\n",
    "Session2 = sessionmaker(bind=engine2)\n",
    "\n",
    "db_session2 = Session2()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "db_session1.add(Fruit(nom=\"banane\"))\n",
    "db_session1.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "for fruit in db_session1.query(Fruit):\n",
    "    db_session2.add(Client(nom=f\"Le vendeur de {fruit.nom}\"))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "db_session2.commit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['Toto',\n",
       " 'Toto',\n",
       " 'Le vendeur de banane',\n",
       " 'Le vendeur de banane',\n",
       " 'Le vendeur de banane',\n",
       " 'Le vendeur de banane',\n",
       " 'Le vendeur de banane']"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "[c.nom for c in db_session2.query(Client)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Réflexion"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [],
   "source": [
    "engine = create_engine(\"sqlite:///base1.db\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [],
   "source": [
    "from sqlalchemy import MetaData\n",
    "\n",
    "meta = MetaData()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "meta.reflect(engine)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "immutabledict({'fruits': Table('fruits', MetaData(bind=None), Column('id', INTEGER(), table=<fruits>, primary_key=True, nullable=False), Column('nom', VARCHAR(), table=<fruits>), schema=None)})"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "meta.tables"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "fruits_table = meta.tables[\"fruits\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('banane',), ('banane',), ('banane',)]"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from sqlalchemy import select\n",
    "\n",
    "conn = engine.connect()\n",
    "conn.execute(select([fruits_table.c.nom])).fetchall()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Références\n",
    "\n",
    "- https://docs.sqlalchemy.org/en/13/orm/tutorial.html\n",
    "- https://www.martinfowler.com/eaaCatalog/unitOfWork.html\n",
    "- https://learning.oreilly.com/library/view/essential-sqlalchemy-2nd/9781491916544/"
   ]
  }
 ],
 "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.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}