{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "aa699f6a-9234-4115-b5cb-19b55fe1e837",
   "metadata": {},
   "source": [
    "# General idea\n",
    "\n",
    "The first proposal was to use Pandas since it offers validation functions that allow to compare tables.  \n",
    "However, there are other ways to send SQL queries to a sqlite database, for instance using the `%sql` magic command from the `ipython-sql` library:  \n",
    "https://pypi.org/project/ipython-sql/\n",
    "\n",
    "\n",
    "## Connect to the database:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b6fd0f45-e221-4acb-b22d-2b49828a6e84",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Charger la magic commande %sql\n",
    "%load_ext sql\n",
    "\n",
    "# Etablir la connexion à la base de données\n",
    "%sql sqlite:///stork-mooc.sqlite"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fdee5329-d2b0-48c8-863c-e88a9b282f72",
   "metadata": {},
   "source": [
    "## Execute queries\n",
    "\n",
    "Simplest method:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "7528bbf3-bf15-40b7-a3d0-ac09b07c29e2",
   "metadata": {},
   "outputs": [],
   "source": [
    "%sql SELECT * FROM migration "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "59034ee8-5468-412f-b662-a3efbed163ee",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "# Creating tables"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bad4d047-383b-48fd-be20-9baefa12e5e8",
   "metadata": {},
   "source": [
    "## Create a new table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9397fb73-ee94-4e7e-af8b-b2a5b2b7f10b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Exécuter une requête\n",
    "%sql CREATE TABLE test ('date' date, 'storkname' text)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b53b09d9-e0d0-4ebc-9631-db29736eaa1e",
   "metadata": {},
   "source": [
    "## Insert values in the table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "eab04aa5-69b2-47d4-8e06-d387164e8a06",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Exécuter une requête\n",
    "%sql INSERT INTO test ('date', 'storkname') VALUES (\"2021-10-14\", \"stork1\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8c7276a8-e263-43bb-8f6f-b78ffcc201df",
   "metadata": {},
   "source": [
    "## Display the table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "71866394-ca03-45a3-9731-72f56c42b0c3",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Ecrire la requête\n",
    "query = \"SELECT * FROM test \"\n",
    "\n",
    "# Afficher le résultat de la requête\n",
    "%sql {query}"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "205801eb-d573-4ac8-ba27-369dac009f35",
   "metadata": {},
   "source": [
    "<br/>\n",
    "To clean the database for new tests:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ad360653-c647-4197-a353-86e970cab111",
   "metadata": {},
   "outputs": [],
   "source": [
    "#%sql DROP TABLE test"
   ]
  },
  {
   "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 (to do in separate file)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "80fa2de4-17ca-48c9-987d-485c1d31a7d4",
   "metadata": {},
   "outputs": [],
   "source": [
    "from SQLFusionEmboitement_v2 import *\n",
    "\n",
    "correctresult = pandas.DataFrame(data=[[\"2021-10-14\", \"stork1\"]], columns=['date', 'storkname'])\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": [
    "selectquery = \"SELECT * FROM test\"\n",
    "selectresult = %sql {selectquery}\n",
    "studentresult_hash = generate_hash(selectresult.DataFrame())\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": "markdown",
   "id": "f3823928-6993-4b8e-9fc4-0347991b1aca",
   "metadata": {
    "tags": []
   },
   "source": [
    "---\n",
    "\n",
    "# Other validation possibilities with ipython-sql (for other types of queries)\n",
    "\n",
    "## Validating queries"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8e3fd147-d4eb-45c2-9725-c3de3cecf425",
   "metadata": {},
   "source": [
    "To automatically validate the query you first need to store the query into a Python variable:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "1f659ce0-ad77-421c-ab1b-81bc9a6c39b5",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Ecrire la requête\n",
    "query = \"SELECT * FROM migration \"\n",
    "\n",
    "# Afficher le résultat de la requête\n",
    "%sql {query}"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6e6d7200-2ef8-4963-af22-5aa8d0f1b030",
   "metadata": {},
   "source": [
    "Then you can use the validation function as before (the validation functions MUST use Pandas as the %sql magic command does not work in an external python script):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d8278589-c24b-4ea6-9022-9dd09439bcb9",
   "metadata": {},
   "outputs": [],
   "source": [
    "from SQLFusionEmboitement_v2 import *\n",
    "question1_validation(query)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "46acfe9a-dace-4dde-95f8-4b99fe89225d",
   "metadata": {},
   "source": [
    "## Validating tables\n",
    "\n",
    "To check that the *table* resulting from a query is correct, then you have to use Pandas.  \n",
    "This implies that you store the result from the query into a variable, and then transform it into a Pandas DataFrame, before comparing it:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "817b6339-123d-426c-b399-ea08b2d76591",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Ecrire la requête\n",
    "query = \"SELECT * FROM migration\"\n",
    "\n",
    "# Récupérer le résultat de la requête\n",
    "resulttable = %sql {query}\n",
    "\n",
    "# Transformer en dataframe\n",
    "resulttable = resulttable.DataFrame()\n",
    "\n",
    "# Afficher la table résultat\n",
    "display(resulttable)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "924791a4-5faf-42b9-9c86-314b4fdd459c",
   "metadata": {},
   "source": [
    "Then you can compare the resulting table to some other Pandas table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "15aa666b-6489-41da-a13d-27fbefbf8194",
   "metadata": {},
   "outputs": [],
   "source": [
    "correctresult = pandas.DataFrame(data=[\"Annamarie\", \"Felix\", \"Jonas\", \"Prinzesschen\"], columns=[\"name\"])\n",
    "assert_frame_equal(resulttable, correctresult)"
   ]
  }
 ],
 "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
}