CH2 COURS requetes SQL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# CH2   : Le langage SQL\n",
    "\n",
    "\n",
    "# 1. des cellules bien utiles \n",
    "Ces cellules sont à exécuter en début ou fin de TP , au besoin"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [],
   "source": [
    "# importation du module sqlite3\n",
    "import sqlite3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [],
   "source": [
    "# pour ouvrir/créer la base de données\n",
    "conn = sqlite3.connect(\"./SF2.db\")  # connection à la base : si elle n'existe pas, elle est créée\n",
    "cur = conn.cursor() # creation d'un curseur"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [],
   "source": [
    "# pour fermer la base\n",
    "cur.close()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "def requete(myText):\n",
    "    '''\n",
    "    une fonction bien utile !\n",
    "    text -> list ou ...\n",
    "    exécute la requete sql myText et renvoie le résultat de celle-ci '''\n",
    "    cur.execute(myText)\n",
    "    results = cur.fetchall()\n",
    "    return results"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 2. Création d'une base de données SF\n",
    "\n",
    "La commande sqlite3.connect permet de créer une base de données, il s'agit maintenant de créer les tables, et d'y insérer des **enregistrements**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.1 Création de table\n",
    "syntaxe SQL de la création d'une table :\n",
    "\n",
    "    CREATE TABLE nomtable ( attribut1 TYPE1, attribut2 TYPE2 ...) ;\n",
    "\n",
    "le type peut être INT, TEXT, DATE etc ..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {},
   "outputs": [
    {
     "ename": "OperationalError",
     "evalue": "table AUTEURS already exists",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mOperationalError\u001b[0m                          Traceback (most recent call last)",
      "\u001b[1;32m<ipython-input-47-2401f0dd2814>\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      1\u001b[0m requete(' CREATE TABLE AUTEURS \\\n\u001b[1;32m----> 2\u001b[1;33m (id INT, nom TEXT, prenom TEXT, ann_naissance INT, langue_ecriture TEXT);')\n\u001b[0m",
      "\u001b[1;32m<ipython-input-11-9b930e692cf9>\u001b[0m in \u001b[0;36mrequete\u001b[1;34m(myText)\u001b[0m\n\u001b[0;32m      4\u001b[0m     \u001b[0mtext\u001b[0m \u001b[1;33m->\u001b[0m \u001b[0mlist\u001b[0m \u001b[0mou\u001b[0m \u001b[1;33m...\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      5\u001b[0m     exécute la requete sql myText et renvoie le résultat de celle-ci '''\n\u001b[1;32m----> 6\u001b[1;33m     \u001b[0mcur\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mexecute\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mmyText\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m      7\u001b[0m     \u001b[0mresults\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mcur\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mfetchall\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      8\u001b[0m     \u001b[1;32mreturn\u001b[0m \u001b[0mresults\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mOperationalError\u001b[0m: table AUTEURS already exists"
     ]
    }
   ],
   "source": [
    "requete(' CREATE TABLE AUTEURS \\\n",
    "(id INT, nom TEXT, prenom TEXT, ann_naissance INT, langue_ecriture TEXT);')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Plus prudent pour éviter les doublons :\n",
    "\n",
    "``CREATE TABLE nomtable IF NOT EXISTS( attribut1 TYPE1, attribut2 TYPE2 ...) ;``"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.2 Insertion d'enregistrements \n",
    "\n",
    "La syntaxe SQL est la suivante :\n",
    "\n",
    "    INSERT INTO nomtable (attribut1, attribut2, ...) VALUES (valeur,valeur, ...), (valeur,valeur) .... ;\n",
    "    \n",
    "à noter qu'on peut mettre autant d'enregistrement que voulus\n",
    "    \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "requete(\"INSERT INTO AUTEURS \\\n",
    "(id,nom,prenom,ann_naissance,langue_ecriture) \\\n",
    "VALUES \\\n",
    "(1,'Orwell','George',1903,'anglais'), \\\n",
    "(2,'Herbert','Frank',1920,'anglais'), \\\n",
    "(3,'Asimov','Isaac',1920,'anglais'), \\\n",
    "(4,'Huxley','Aldous',1894,'anglais'), \\\n",
    "(5,'Bradbury','Ray',1920,'anglais'), \\\n",
    "(6,'K.Dick','Philip',1928,'anglais'), \\\n",
    "(7,'Barjavel','René',1911,'français'), \\\n",
    "(8,'Boulle','Pierre',1912,'français'), \\\n",
    "(9,'Van Vogt','Alfred Elton',1912,'anglais'), \\\n",
    "(10,'Verne','Jules',1828,'français');\") "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "La commande ***select***  permet de récupérer des enregistrements, utilisons-la pour vérifier si la table est bien fournie (* signifie tous les attributs). Nous reviendrons sur la commande ***select*** plus tard."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1, 'Orwell', 'George', 1903, 'anglais'),\n",
       " (2, 'Herbert', 'Frank', 1920, 'anglais'),\n",
       " (3, 'Asimov', 'Isaac', 1920, 'anglais'),\n",
       " (4, 'Huxley', 'Aldous', 1894, 'anglais'),\n",
       " (5, 'Bradbury', 'Ray', 1920, 'anglais'),\n",
       " (6, 'K.Dick', 'Philip', 1928, 'anglais'),\n",
       " (7, 'Barjavel', 'René', 1911, 'français'),\n",
       " (8, 'Boulle', 'Pierre', 1912, 'français'),\n",
       " (9, 'Van Vogt', 'Alfred Elton', 1912, 'anglais'),\n",
       " (10, 'Verne', 'Jules', 1828, 'français')]"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "requete('select * from \"AUTEURS\"')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Passons maintenant à la table LIVRES \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "requete('CREATE TABLE LIVRES \\\n",
    "(id INT, titre TEXT, id_auteur INT, ann_publi INT, note INT);')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "requete(\"INSERT INTO LIVRES\\\n",
    "(id,titre,id_auteur,ann_publi,note)\\\n",
    "VALUES\\\n",
    "(1,'1984',1,1949,10),\\\n",
    "(2,'Dune',2,1965,8),\\\n",
    "(3,'Fondation',3,1951,9),\\\n",
    "(4,'Le meilleur des mondes',4,1931,7),\\\n",
    "(5,'Fahrenheit 451',5,1953,7),\\\n",
    "(6,'Ubik',6,1969,9),\\\n",
    "(7,'Chroniques martiennes',5,1950,8),\\\n",
    "(8,'La nuit des temps',7,1968,7),\\\n",
    "(9,'Blade Runner',6,1968,8),\\\n",
    "(10,'Les Robots',3,1950,9),\\\n",
    "(11,'La Planète des singes',8,1963,8),\\\n",
    "(12,'Ravage',7,1943,8),\\\n",
    "(13,'Le Maître du Haut Château',6,1962,8),\\\n",
    "(14,'Le monde des Ā',9,1945,7),\\\n",
    "(15,'La Fin de l’éternité',3,1955,8),\\\n",
    "(16,'De la Terre à la Lune',10,1865,10);\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1, '1984', 1, 1949, 10),\n",
       " (2, 'Dune', 2, 1965, 8),\n",
       " (3, 'Fondation', 3, 1951, 9),\n",
       " (4, 'Le meilleur des mondes', 4, 1931, 7),\n",
       " (5, 'Fahrenheit 451', 5, 1953, 7),\n",
       " (6, 'Ubik', 6, 1969, 9),\n",
       " (7, 'Chroniques martiennes', 5, 1950, 8),\n",
       " (8, 'La nuit des temps', 7, 1968, 7),\n",
       " (9, 'Blade Runner', 6, 1968, 8),\n",
       " (10, 'Les Robots', 3, 1950, 9),\n",
       " (11, 'La Planète des singes', 8, 1963, 8),\n",
       " (12, 'Ravage', 7, 1943, 8),\n",
       " (13, 'Le Maître du Haut Château', 6, 1962, 8),\n",
       " (14, 'Le monde des Ā', 9, 1945, 7),\n",
       " (15, 'La Fin de l’éternité', 3, 1955, 8),\n",
       " (16, 'De la Terre à la Lune', 10, 1865, 10)]"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# vérification\n",
    "requete('select * from \"LIVRES\"')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.3 La commande select\n",
    "\n",
    "Il s'agit maintenant de consulter la table. Il est essentiel de savoir sélectionner uniquement ce qui nous intéresse, n'oublions pas qu'une base de données peut contenir  des millions de données. "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.3.1 sélectionner des attributs\n",
    "seuls le nom et le prénom des auteurs m'intéresse :\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('Orwell', 'George'),\n",
       " ('Herbert', 'Frank'),\n",
       " ('Asimov', 'Isaac'),\n",
       " ('Huxley', 'Aldous'),\n",
       " ('Bradbury', 'Ray'),\n",
       " ('K.Dick', 'Philip'),\n",
       " ('Barjavel', 'René'),\n",
       " ('Boulle', 'Pierre'),\n",
       " ('Van Vogt', 'Alfred Elton'),\n",
       " ('Verne', 'Jules')]"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "requete('SELECT nom,prenom FROM AUTEURS;')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2.3.2 sélectionner des enregistrements selon des critères\n",
    "\n",
    "Je ne veux que les auteurs nés au XIXème siècle"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(4, 'Huxley', 'Aldous', 1894, 'anglais'),\n",
       " (10, 'Verne', 'Jules', 1828, 'français')]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "requete('SELECT * FROM AUTEURS WHERE ann_naissance < 1900')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "On peut évidemment cumuler des critères avec AND , OR etc ..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2.3.3 Fonctions de groupes et tri\n",
    "\n",
    "Quelques exemples valent mieux qu'un long discours"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(2,)]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#COUNT on peut aussi utiliser MAX,MIN,AVG(moyenne),SUM\n",
    "requete('SELECT COUNT(*) FROM AUTEURS WHERE ann_naissance < 1900')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(10, 'Verne', 'Jules', 1828, 'français'),\n",
       " (4, 'Huxley', 'Aldous', 1894, 'anglais'),\n",
       " (1, 'Orwell', 'George', 1903, 'anglais'),\n",
       " (7, 'Barjavel', 'René', 1911, 'français'),\n",
       " (8, 'Boulle', 'Pierre', 1912, 'français'),\n",
       " (9, 'Van Vogt', 'Alfred Elton', 1912, 'anglais'),\n",
       " (2, 'Herbert', 'Frank', 1920, 'anglais'),\n",
       " (3, 'Asimov', 'Isaac', 1920, 'anglais'),\n",
       " (5, 'Bradbury', 'Ray', 1920, 'anglais'),\n",
       " (6, 'K.Dick', 'Philip', 1928, 'anglais')]"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#TRI\n",
    "requete('SELECT * FROM AUTEURS ORDER BY ann_naissance')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. La jointure \n",
    "\n",
    "Nous arrivons maintenant au point central du modèle relationnel. Je veux la liste des livres de Barjavel. Mais le nom Barjavel est à trouver dans la table AUTEURS et les livres dans la table LIVRES. \n",
    "Il faut donc réaliser une jointure sur l'attribut qu'ils ont en commun, c'est à dire l'identifiant de l'auteur."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(8, 'La nuit des temps', 7, 1968, 7, 7, 'Barjavel', 'René', 1911, 'français'),\n",
       " (12, 'Ravage', 7, 1943, 8, 7, 'Barjavel', 'René', 1911, 'français')]"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "requete('SELECT * FROM LIVRES \\\n",
    "INNER JOIN AUTEURS ON LIVRES.id_auteur= AUTEURS.id \\\n",
    "WHERE AUTEURS.nom = \"Barjavel\";')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "On voit que la jointure crée un table temporaire qui fusionne les enregistrements des deux tables. \n",
    "Pour simplifier la réponse : "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('La nuit des temps',), ('Ravage',)]"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "requete('SELECT titre FROM LIVRES \\\n",
    "INNER JOIN AUTEURS ON LIVRES.id_auteur= AUTEURS.id \\\n",
    "WHERE AUTEURS.nom = \"Barjavel\";')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Un autre exemple : je souhaite la liste des livres post 1950. Mais j'ai besoin d'un jointure pour afficher en même temps le nom de son auteur."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('Dune', 'Herbert', 'Frank'),\n",
       " ('Fondation', 'Asimov', 'Isaac'),\n",
       " ('Fahrenheit 451', 'Bradbury', 'Ray'),\n",
       " ('Ubik', 'K.Dick', 'Philip'),\n",
       " ('La nuit des temps', 'Barjavel', 'René'),\n",
       " ('Blade Runner', 'K.Dick', 'Philip'),\n",
       " ('La Planète des singes', 'Boulle', 'Pierre'),\n",
       " ('Le Maître du Haut Château', 'K.Dick', 'Philip'),\n",
       " ('La Fin de l’éternité', 'Asimov', 'Isaac')]"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "requete('SELECT titre,nom, prenom FROM LIVRES INNER JOIN AUTEURS ON LIVRES.id_auteur = AUTEURS.id WHERE ann_publi>1950 ')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4  APPENDICE : effacements et mises à jour"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.1 Suppression d'une table ( à vos risques et périls !) : La commande DROP TABLE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "requete('DROP TABLE AUTEURS';)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.2 Suppression d'un enregistrement  :  la commande DELETE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "requete('DELETE FROM LIVRES \\\n",
    "WHERE titre=\"Blade Runner\";')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 4.3 Mise à jour d'une donnée : la commande UPDATE"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "requete('UPDATE LIVRES \\\n",
    "SET note=7 \\\n",
    "WHERE titre = \"Hypérion\"';)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "On peut modifier la valeur de plusieurs attribus dans la ligne SET en les espaçant par des virgules."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5 Filtres "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(4, 'Le meilleur des mondes', 4, 1931, 7),\n",
       " (8, 'La nuit des temps', 7, 1968, 7),\n",
       " (10, 'Les Robots', 3, 1950, 9),\n",
       " (11, 'La Planète des singes', 8, 1963, 8),\n",
       " (13, 'Le Maître du Haut Château', 6, 1962, 8),\n",
       " (14, 'Le monde des Ā', 9, 1945, 7),\n",
       " (15, 'La Fin de l’éternité', 3, 1955, 8)]"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# commançant par\n",
    "requete('SELECT * FROM LIVRES WHERE titre LIKE \"L%\";')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(4, 'Le meilleur des mondes', 4, 1931, 7),\n",
       " (7, 'Chroniques martiennes', 5, 1950, 8),\n",
       " (8, 'La nuit des temps', 7, 1968, 7),\n",
       " (10, 'Les Robots', 3, 1950, 9),\n",
       " (11, 'La Planète des singes', 8, 1963, 8)]"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# finissant par \n",
    "requete('SELECT * FROM LIVRES WHERE titre LIKE \"%s\";')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(7, 'Barjavel', 'René', 1911, 'français')]"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#contenant\n",
    "requete('SELECT * FROM AUTEURS WHERE nom LIKE \"%ja%\";')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(1, '1984', 1, 1949, 10),\n",
       " (4, 'Le meilleur des mondes', 4, 1931, 7),\n",
       " (7, 'Chroniques martiennes', 5, 1950, 8),\n",
       " (10, 'Les Robots', 3, 1950, 9),\n",
       " (12, 'Ravage', 7, 1943, 8),\n",
       " (14, 'Le monde des Ā', 9, 1945, 7)]"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#entre \n",
    "requete('SELECT * FROM LIVRES WHERE ann_publi BETWEEN 1900 AND 1950;')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[(3, 'Fondation', 3, 1951, 9),\n",
       " (5, 'Fahrenheit 451', 5, 1953, 7),\n",
       " (7, 'Chroniques martiennes', 5, 1950, 8),\n",
       " (10, 'Les Robots', 3, 1950, 9)]"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#liste\n",
    "requete('SELECT * FROM LIVRES WHERE ann_publi IN (1950,1951,1953);')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}