{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "ea40630c-479e-42e5-8309-62e29e3ac620",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Import des libraries Python\n",
    "Pour manipuler une base de donnée avec Python nous avons besoin des librairies `sqlite3` et `pandas`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "efec001b-5646-49cf-9e3c-78ee17aa9903",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Importing libraries\n",
    "import sqlite3\n",
    "\n",
    "# Librairie permettant d'afficher la table \"observation\"\n",
    "from SQLCreate_Pandas import *"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "383ceffd-833e-40ce-8c8b-6be1f5a09aec",
   "metadata": {},
   "source": [
    "## Connexion à la base de données \n",
    "D'abord on établit une connexion à la base de données :"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "4873ba3f-1c0a-42be-99b1-9a09c8993f10",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Etablir la connexion à la base de données\n",
    "conn = sqlite3.connect(\"stork-mooc.sqlite\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a04e523f-a0f7-4fa5-8f13-62033f65249c",
   "metadata": {},
   "source": [
    "## Regardons l'état actuel de la table \"observations\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7f6f1cb5-78c6-4e62-adbe-48f2074ebb8e",
   "metadata": {},
   "outputs": [],
   "source": [
    "display_observations_table()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "661bfeca-4324-45db-aa33-8694d489379c",
   "metadata": {},
   "source": [
    "## Travaillons sur la table \"observations\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a53b0fff-4ca9-4017-8ddc-d97509ee8ad3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Donner la requête à executer\n",
    "#query = \"CREATE TABLE observations ('date' date,'storkname' text)\"\n",
    "query = \"INSERT INTO observations ('date', 'storkname') VALUES ('2021-10-14', 'stork1')\"\n",
    "#query = \"INSERT INTO observations ('date', 'storkname') VALUES ('2021-11-19', 'stork2')\"\n",
    "#query = \"DROP TABLE observations\"\n",
    "#query = \"SELECT * FROM observations\"\n",
    "\n",
    "# Executer la requête\n",
    "conn.cursor().execute(query)\n",
    "conn.commit() # IMPORTANT\n",
    "\n",
    "# Afficher la table\n",
    "display_observations_table()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bcad41c3-4cb3-4300-878c-5d64bf377e28",
   "metadata": {
    "tags": []
   },
   "source": [
    "## Supprimons la table \"observations\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "857c88e6-bad5-49c5-8eb3-b69f494bd078",
   "metadata": {},
   "outputs": [],
   "source": [
    "delete_observations_table()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "52d2a516-8490-4b7b-a4bf-93597d2a89ab",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "# Validate CREATE/INSERT etc. queries"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5aa4246c-bafa-41bd-8d83-f50652e738bd",
   "metadata": {},
   "source": [
    "To validate queries which create tables, I think what you need to do in the validation function is:\n",
    "* use a SELECT query to select the table the student has created\n",
    "* compare the result with the hash of the table they should have created (to generate in a separate file)\n",
    "\n",
    "## Generate the hash of the table they should create \n",
    "\n",
    "**to do in separate file**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "80fa2de4-17ca-48c9-987d-485c1d31a7d4",
   "metadata": {},
   "outputs": [],
   "source": [
    "correctresult = pandas.DataFrame(data=[[\"2021-10-14\", \"stork1\"],[\"2021-11-19\", \"stork2\"]], columns=['date', 'storkname'])\n",
    "display(correctresult)\n",
    "correctresponse_hash = generate_hash(correctresult)\n",
    "correctresponse_hash"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "65a8f41d-68c2-4fe3-b1f2-6c4281411d23",
   "metadata": {},
   "source": [
    "## Select the table the student has created and compare the two hash"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ab092c80-b326-4526-b0ad-5fa8ab6fcfa7",
   "metadata": {},
   "outputs": [],
   "source": [
    "selectresult = display_observations_table()\n",
    "display(selectresult)\n",
    "studentresult_hash = generate_hash(selectresult)\n",
    "studentresult_hash"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a141954a-5fc4-44aa-abc7-16aa94a902ed",
   "metadata": {},
   "outputs": [],
   "source": [
    "try:\n",
    "    assert studentresult_hash == correctresponse_hash, \"Incorrect query.\"\n",
    "\n",
    "except AssertionError as ae:\n",
    "    print(\"Your query didn't pass the validation tests.\")\n",
    "    raise ae \n",
    "\n",
    "print(\"Your query has passed the validation tests and is evaluated as correct, well done!\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "538bf19b-9409-4394-955d-77582e2d54ce",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python",
   "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.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}