diff --git a/Alternative.ipynb b/Alternative.ipynb
index d6aa3ac..8cbf1a9 100644
--- a/Alternative.ipynb
+++ b/Alternative.ipynb
@@ -1,1192 +1,342 @@
{
"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": 1,
+ "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": 2,
+ "execution_count": null,
"id": "7528bbf3-bf15-40b7-a3d0-ac09b07c29e2",
"metadata": {},
- "outputs": [
- {
- "name": "stdout",
- "output_type": "stream",
- "text": [
- " * sqlite:///stork-mooc.sqlite\n",
- "Done.\n"
- ]
- },
- {
- "data": {
- "text/html": [
- "
\n",
- " \n",
- " name | \n",
- " departureyear | \n",
- "
\n",
- " \n",
- " Annamarie | \n",
- " 2001 | \n",
- "
\n",
- " \n",
- " Annamarie | \n",
- " 2002 | \n",
- "
\n",
- " \n",
- " Annamarie | \n",
- " 2003 | \n",
- "
\n",
- " \n",
- " Annamarie | \n",
- " 2004 | \n",
- "
\n",
- " \n",
- " Annamarie | \n",
- " 2005 | \n",
- "
\n",
- " \n",
- " Annamarie | \n",
- " 2006 | \n",
- "
\n",
- " \n",
- " Anneliese | \n",
- " 2000 | \n",
- "
\n",
- " \n",
- " Anneliese | \n",
- " 2001 | \n",
- "
\n",
- " \n",
- " Christina | \n",
- " 2002 | \n",
- "
\n",
- " \n",
- " Christina | \n",
- " 2003 | \n",
- "
\n",
- " \n",
- " Donna | \n",
- " 1999 | \n",
- "
\n",
- " \n",
- " Donna | \n",
- " 2000 | \n",
- "
\n",
- " \n",
- " Felix | \n",
- " 2001 | \n",
- "
\n",
- " \n",
- " Felix | \n",
- " 2002 | \n",
- "
\n",
- " \n",
- " Felix | \n",
- " 2003 | \n",
- "
\n",
- " \n",
- " Felix | \n",
- " 2004 | \n",
- "
\n",
- " \n",
- " Johann | \n",
- " 2002 | \n",
- "
\n",
- " \n",
- " Jonas | \n",
- " 2001 | \n",
- "
\n",
- " \n",
- " Jonas | \n",
- " 2002 | \n",
- "
\n",
- " \n",
- " Jonas | \n",
- " 2003 | \n",
- "
\n",
- " \n",
- " Jonas | \n",
- " 2004 | \n",
- "
\n",
- " \n",
- " Louka | \n",
- " 1999 | \n",
- "
\n",
- " \n",
- " Louka | \n",
- " 2000 | \n",
- "
\n",
- " \n",
- " Max | \n",
- " 2000 | \n",
- "
\n",
- " \n",
- " Moritz | \n",
- " 2000 | \n",
- "
\n",
- " \n",
- " Penelope | \n",
- " 1998 | \n",
- "
\n",
- " \n",
- " Penelope | \n",
- " 1999 | \n",
- "
\n",
- " \n",
- " Penelope | \n",
- " 2000 | \n",
- "
\n",
- " \n",
- " Penelope | \n",
- " 2001 | \n",
- "
\n",
- " \n",
- " Peterchen | \n",
- " 1998 | \n",
- "
\n",
- " \n",
- " Peterchen | \n",
- " 1999 | \n",
- "
\n",
- " \n",
- " Peterchen | \n",
- " 2000 | \n",
- "
\n",
- " \n",
- " Prinzesschen | \n",
- " 2001 | \n",
- "
\n",
- " \n",
- " Prinzesschen | \n",
- " 2002 | \n",
- "
\n",
- " \n",
- " Prinzesschen | \n",
- " 2003 | \n",
- "
\n",
- " \n",
- " Prinzesschen | \n",
- " 2004 | \n",
- "
\n",
- " \n",
- " Prinzesschen | \n",
- " 2005 | \n",
- "
\n",
- " \n",
- " Prinzesschen | \n",
- " 2006 | \n",
- "
\n",
- " \n",
- " Prinzessin | \n",
- " 1998 | \n",
- "
\n",
- " \n",
- " Prinzessin | \n",
- " 1999 | \n",
- "
\n",
- " \n",
- " Sohn von Prinzessin | \n",
- " 1998 | \n",
- "
\n",
- " \n",
- " Sohn von Prinzessin | \n",
- " 1999 | \n",
- "
\n",
- " \n",
- " Sophia | \n",
- " 2001 | \n",
- "
\n",
- " \n",
- " Sophia | \n",
- " 2002 | \n",
- "
\n",
- " \n",
- " Valinka | \n",
- " 2001 | \n",
- "
\n",
- "
"
- ],
- "text/plain": [
- "[('Annamarie', 2001),\n",
- " ('Annamarie', 2002),\n",
- " ('Annamarie', 2003),\n",
- " ('Annamarie', 2004),\n",
- " ('Annamarie', 2005),\n",
- " ('Annamarie', 2006),\n",
- " ('Anneliese', 2000),\n",
- " ('Anneliese', 2001),\n",
- " ('Christina', 2002),\n",
- " ('Christina', 2003),\n",
- " ('Donna', 1999),\n",
- " ('Donna', 2000),\n",
- " ('Felix', 2001),\n",
- " ('Felix', 2002),\n",
- " ('Felix', 2003),\n",
- " ('Felix', 2004),\n",
- " ('Johann', 2002),\n",
- " ('Jonas', 2001),\n",
- " ('Jonas', 2002),\n",
- " ('Jonas', 2003),\n",
- " ('Jonas', 2004),\n",
- " ('Louka', 1999),\n",
- " ('Louka', 2000),\n",
- " ('Max', 2000),\n",
- " ('Moritz', 2000),\n",
- " ('Penelope', 1998),\n",
- " ('Penelope', 1999),\n",
- " ('Penelope', 2000),\n",
- " ('Penelope', 2001),\n",
- " ('Peterchen', 1998),\n",
- " ('Peterchen', 1999),\n",
- " ('Peterchen', 2000),\n",
- " ('Prinzesschen', 2001),\n",
- " ('Prinzesschen', 2002),\n",
- " ('Prinzesschen', 2003),\n",
- " ('Prinzesschen', 2004),\n",
- " ('Prinzesschen', 2005),\n",
- " ('Prinzesschen', 2006),\n",
- " ('Prinzessin', 1998),\n",
- " ('Prinzessin', 1999),\n",
- " ('Sohn von Prinzessin', 1998),\n",
- " ('Sohn von Prinzessin', 1999),\n",
- " ('Sophia', 2001),\n",
- " ('Sophia', 2002),\n",
- " ('Valinka', 2001)]"
- ]
- },
- "execution_count": 2,
- "metadata": {},
- "output_type": "execute_result"
- }
- ],
+ "outputs": [],
"source": [
"%sql SELECT * FROM migration "
]
},
{
"cell_type": "markdown",
- "id": "f3823928-6993-4b8e-9fc4-0347991b1aca",
+ "id": "59034ee8-5468-412f-b662-a3efbed163ee",
"metadata": {},
"source": [
- "# Automatic validation with ipython-sql\n",
+ "---\n",
"\n",
- "## Validating queries"
+ "# Creating tables"
]
},
{
"cell_type": "markdown",
- "id": "8e3fd147-d4eb-45c2-9725-c3de3cecf425",
+ "id": "bad4d047-383b-48fd-be20-9baefa12e5e8",
"metadata": {},
"source": [
- "To automatically validate the query you first need to store the query into a Python variable:"
+ "## Create a new table:"
]
},
{
"cell_type": "code",
- "execution_count": 3,
- "id": "1f659ce0-ad77-421c-ab1b-81bc9a6c39b5",
+ "execution_count": null,
+ "id": "9397fb73-ee94-4e7e-af8b-b2a5b2b7f10b",
"metadata": {},
- "outputs": [
- {
- "name": "stdout",
- "output_type": "stream",
- "text": [
- " * sqlite:///stork-mooc.sqlite\n",
- "Done.\n"
- ]
- },
- {
- "data": {
- "text/html": [
- "\n",
- " \n",
- " name | \n",
- " departureyear | \n",
- "
\n",
- " \n",
- " Annamarie | \n",
- " 2001 | \n",
- "
\n",
- " \n",
- " Annamarie | \n",
- " 2002 | \n",
- "
\n",
- " \n",
- " Annamarie | \n",
- " 2003 | \n",
- "
\n",
- " \n",
- " Annamarie | \n",
- " 2004 | \n",
- "
\n",
- " \n",
- " Annamarie | \n",
- " 2005 | \n",
- "
\n",
- " \n",
- " Annamarie | \n",
- " 2006 | \n",
- "
\n",
- " \n",
- " Anneliese | \n",
- " 2000 | \n",
- "
\n",
- " \n",
- " Anneliese | \n",
- " 2001 | \n",
- "
\n",
- " \n",
- " Christina | \n",
- " 2002 | \n",
- "
\n",
- " \n",
- " Christina | \n",
- " 2003 | \n",
- "
\n",
- " \n",
- " Donna | \n",
- " 1999 | \n",
- "
\n",
- " \n",
- " Donna | \n",
- " 2000 | \n",
- "
\n",
- " \n",
- " Felix | \n",
- " 2001 | \n",
- "
\n",
- " \n",
- " Felix | \n",
- " 2002 | \n",
- "
\n",
- " \n",
- " Felix | \n",
- " 2003 | \n",
- "
\n",
- " \n",
- " Felix | \n",
- " 2004 | \n",
- "
\n",
- " \n",
- " Johann | \n",
- " 2002 | \n",
- "
\n",
- " \n",
- " Jonas | \n",
- " 2001 | \n",
- "
\n",
- " \n",
- " Jonas | \n",
- " 2002 | \n",
- "
\n",
- " \n",
- " Jonas | \n",
- " 2003 | \n",
- "
\n",
- " \n",
- " Jonas | \n",
- " 2004 | \n",
- "
\n",
- " \n",
- " Louka | \n",
- " 1999 | \n",
- "
\n",
- " \n",
- " Louka | \n",
- " 2000 | \n",
- "
\n",
- " \n",
- " Max | \n",
- " 2000 | \n",
- "
\n",
- " \n",
- " Moritz | \n",
- " 2000 | \n",
- "
\n",
- " \n",
- " Penelope | \n",
- " 1998 | \n",
- "
\n",
- " \n",
- " Penelope | \n",
- " 1999 | \n",
- "
\n",
- " \n",
- " Penelope | \n",
- " 2000 | \n",
- "
\n",
- " \n",
- " Penelope | \n",
- " 2001 | \n",
- "
\n",
- " \n",
- " Peterchen | \n",
- " 1998 | \n",
- "
\n",
- " \n",
- " Peterchen | \n",
- " 1999 | \n",
- "
\n",
- " \n",
- " Peterchen | \n",
- " 2000 | \n",
- "
\n",
- " \n",
- " Prinzesschen | \n",
- " 2001 | \n",
- "
\n",
- " \n",
- " Prinzesschen | \n",
- " 2002 | \n",
- "
\n",
- " \n",
- " Prinzesschen | \n",
- " 2003 | \n",
- "
\n",
- " \n",
- " Prinzesschen | \n",
- " 2004 | \n",
- "
\n",
- " \n",
- " Prinzesschen | \n",
- " 2005 | \n",
- "
\n",
- " \n",
- " Prinzesschen | \n",
- " 2006 | \n",
- "
\n",
- " \n",
- " Prinzessin | \n",
- " 1998 | \n",
- "
\n",
- " \n",
- " Prinzessin | \n",
- " 1999 | \n",
- "
\n",
- " \n",
- " Sohn von Prinzessin | \n",
- " 1998 | \n",
- "
\n",
- " \n",
- " Sohn von Prinzessin | \n",
- " 1999 | \n",
- "
\n",
- " \n",
- " Sophia | \n",
- " 2001 | \n",
- "
\n",
- " \n",
- " Sophia | \n",
- " 2002 | \n",
- "
\n",
- " \n",
- " Valinka | \n",
- " 2001 | \n",
- "
\n",
- "
"
- ],
- "text/plain": [
- "[('Annamarie', 2001),\n",
- " ('Annamarie', 2002),\n",
- " ('Annamarie', 2003),\n",
- " ('Annamarie', 2004),\n",
- " ('Annamarie', 2005),\n",
- " ('Annamarie', 2006),\n",
- " ('Anneliese', 2000),\n",
- " ('Anneliese', 2001),\n",
- " ('Christina', 2002),\n",
- " ('Christina', 2003),\n",
- " ('Donna', 1999),\n",
- " ('Donna', 2000),\n",
- " ('Felix', 2001),\n",
- " ('Felix', 2002),\n",
- " ('Felix', 2003),\n",
- " ('Felix', 2004),\n",
- " ('Johann', 2002),\n",
- " ('Jonas', 2001),\n",
- " ('Jonas', 2002),\n",
- " ('Jonas', 2003),\n",
- " ('Jonas', 2004),\n",
- " ('Louka', 1999),\n",
- " ('Louka', 2000),\n",
- " ('Max', 2000),\n",
- " ('Moritz', 2000),\n",
- " ('Penelope', 1998),\n",
- " ('Penelope', 1999),\n",
- " ('Penelope', 2000),\n",
- " ('Penelope', 2001),\n",
- " ('Peterchen', 1998),\n",
- " ('Peterchen', 1999),\n",
- " ('Peterchen', 2000),\n",
- " ('Prinzesschen', 2001),\n",
- " ('Prinzesschen', 2002),\n",
- " ('Prinzesschen', 2003),\n",
- " ('Prinzesschen', 2004),\n",
- " ('Prinzesschen', 2005),\n",
- " ('Prinzesschen', 2006),\n",
- " ('Prinzessin', 1998),\n",
- " ('Prinzessin', 1999),\n",
- " ('Sohn von Prinzessin', 1998),\n",
- " ('Sohn von Prinzessin', 1999),\n",
- " ('Sophia', 2001),\n",
- " ('Sophia', 2002),\n",
- " ('Valinka', 2001)]"
- ]
- },
- "execution_count": 3,
- "metadata": {},
- "output_type": "execute_result"
- }
- ],
+ "outputs": [],
"source": [
- "# Ecrire la requête\n",
- "query = \"SELECT * FROM migration \"\n",
- "\n",
- "# Afficher le résultat de la requête\n",
- "%sql {query}"
+ "# Exécuter une requête\n",
+ "%sql CREATE TABLE test ('date' date, 'storkname' text)"
]
},
{
"cell_type": "markdown",
- "id": "6e6d7200-2ef8-4963-af22-5aa8d0f1b030",
+ "id": "b53b09d9-e0d0-4ebc-9631-db29736eaa1e",
"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):"
+ "## Insert values in the table"
]
},
{
"cell_type": "code",
- "execution_count": 4,
- "id": "d8278589-c24b-4ea6-9022-9dd09439bcb9",
+ "execution_count": null,
+ "id": "eab04aa5-69b2-47d4-8e06-d387164e8a06",
"metadata": {},
- "outputs": [
- {
- "name": "stdout",
- "output_type": "stream",
- "text": [
- "Your query didn't pass the validation tests.\n"
- ]
- },
- {
- "ename": "AssertionError",
- "evalue": "Incorrect query.",
- "output_type": "error",
- "traceback": [
- "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
- "\u001b[0;31mAssertionError\u001b[0m Traceback (most recent call last)",
- "\u001b[0;32m/tmp/ipykernel_160/693865606.py\u001b[0m in \u001b[0;36m\u001b[0;34m\u001b[0m\n\u001b[1;32m 1\u001b[0m \u001b[0;32mfrom\u001b[0m \u001b[0mSQLFusionEmboitement_v2\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0;34m*\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 2\u001b[0;31m \u001b[0mquestion1_validation\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mquery\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
- "\u001b[0;32m~/git_Noto/SQLValidation/SQLFusionEmboitement_v2.py\u001b[0m in \u001b[0;36mquestion1_validation\u001b[0;34m(studentquery)\u001b[0m\n\u001b[1;32m 84\u001b[0m \u001b[0mprint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"Your query didn't pass the validation tests.\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 85\u001b[0m \u001b[0;31m# We raise the exception here to stop execution and prevent the success message to be printed\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 86\u001b[0;31m \u001b[0;32mraise\u001b[0m \u001b[0mae\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 87\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 88\u001b[0m \u001b[0mprint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"Your query has passed the validation tests and is evaluated as correct, well done!\"\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
- "\u001b[0;32m~/git_Noto/SQLValidation/SQLFusionEmboitement_v2.py\u001b[0m in \u001b[0;36mquestion1_validation\u001b[0;34m(studentquery)\u001b[0m\n\u001b[1;32m 79\u001b[0m \u001b[0;31m# generate_hash(run_query(correctquery)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 80\u001b[0m \u001b[0mresponse_hash\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m'b27b60409c967bcb8a93dbbc75ba33cd'\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 81\u001b[0;31m \u001b[0;32massert\u001b[0m \u001b[0mcheck_hash\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mrun_query\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstudentquery\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mresponse_hash\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m\"Incorrect query.\"\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 82\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 83\u001b[0m \u001b[0;32mexcept\u001b[0m \u001b[0mAssertionError\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0mae\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
- "\u001b[0;31mAssertionError\u001b[0m: Incorrect query."
- ]
- }
- ],
+ "outputs": [],
"source": [
- "from SQLFusionEmboitement_v2 import *\n",
- "question1_validation(query)"
+ "# Exécuter une requête\n",
+ "%sql INSERT INTO test ('date', 'storkname') VALUES (\"2021-10-14\", \"stork1\")"
]
},
{
"cell_type": "markdown",
- "id": "46acfe9a-dace-4dde-95f8-4b99fe89225d",
+ "id": "8c7276a8-e263-43bb-8f6f-b78ffcc201df",
"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:"
+ "## Display the table"
]
},
{
"cell_type": "code",
- "execution_count": 5,
- "id": "817b6339-123d-426c-b399-ea08b2d76591",
+ "execution_count": null,
+ "id": "71866394-ca03-45a3-9731-72f56c42b0c3",
"metadata": {},
- "outputs": [
- {
- "name": "stdout",
- "output_type": "stream",
- "text": [
- " * sqlite:///stork-mooc.sqlite\n",
- "Done.\n"
- ]
- },
- {
- "data": {
- "text/html": [
- "\n",
- "\n",
- "
\n",
- " \n",
- " \n",
- " | \n",
- " name | \n",
- " departureyear | \n",
- "
\n",
- " \n",
- " \n",
- " \n",
- " 0 | \n",
- " Annamarie | \n",
- " 2001 | \n",
- "
\n",
- " \n",
- " 1 | \n",
- " Annamarie | \n",
- " 2002 | \n",
- "
\n",
- " \n",
- " 2 | \n",
- " Annamarie | \n",
- " 2003 | \n",
- "
\n",
- " \n",
- " 3 | \n",
- " Annamarie | \n",
- " 2004 | \n",
- "
\n",
- " \n",
- " 4 | \n",
- " Annamarie | \n",
- " 2005 | \n",
- "
\n",
- " \n",
- " 5 | \n",
- " Annamarie | \n",
- " 2006 | \n",
- "
\n",
- " \n",
- " 6 | \n",
- " Anneliese | \n",
- " 2000 | \n",
- "
\n",
- " \n",
- " 7 | \n",
- " Anneliese | \n",
- " 2001 | \n",
- "
\n",
- " \n",
- " 8 | \n",
- " Christina | \n",
- " 2002 | \n",
- "
\n",
- " \n",
- " 9 | \n",
- " Christina | \n",
- " 2003 | \n",
- "
\n",
- " \n",
- " 10 | \n",
- " Donna | \n",
- " 1999 | \n",
- "
\n",
- " \n",
- " 11 | \n",
- " Donna | \n",
- " 2000 | \n",
- "
\n",
- " \n",
- " 12 | \n",
- " Felix | \n",
- " 2001 | \n",
- "
\n",
- " \n",
- " 13 | \n",
- " Felix | \n",
- " 2002 | \n",
- "
\n",
- " \n",
- " 14 | \n",
- " Felix | \n",
- " 2003 | \n",
- "
\n",
- " \n",
- " 15 | \n",
- " Felix | \n",
- " 2004 | \n",
- "
\n",
- " \n",
- " 16 | \n",
- " Johann | \n",
- " 2002 | \n",
- "
\n",
- " \n",
- " 17 | \n",
- " Jonas | \n",
- " 2001 | \n",
- "
\n",
- " \n",
- " 18 | \n",
- " Jonas | \n",
- " 2002 | \n",
- "
\n",
- " \n",
- " 19 | \n",
- " Jonas | \n",
- " 2003 | \n",
- "
\n",
- " \n",
- " 20 | \n",
- " Jonas | \n",
- " 2004 | \n",
- "
\n",
- " \n",
- " 21 | \n",
- " Louka | \n",
- " 1999 | \n",
- "
\n",
- " \n",
- " 22 | \n",
- " Louka | \n",
- " 2000 | \n",
- "
\n",
- " \n",
- " 23 | \n",
- " Max | \n",
- " 2000 | \n",
- "
\n",
- " \n",
- " 24 | \n",
- " Moritz | \n",
- " 2000 | \n",
- "
\n",
- " \n",
- " 25 | \n",
- " Penelope | \n",
- " 1998 | \n",
- "
\n",
- " \n",
- " 26 | \n",
- " Penelope | \n",
- " 1999 | \n",
- "
\n",
- " \n",
- " 27 | \n",
- " Penelope | \n",
- " 2000 | \n",
- "
\n",
- " \n",
- " 28 | \n",
- " Penelope | \n",
- " 2001 | \n",
- "
\n",
- " \n",
- " 29 | \n",
- " Peterchen | \n",
- " 1998 | \n",
- "
\n",
- " \n",
- " 30 | \n",
- " Peterchen | \n",
- " 1999 | \n",
- "
\n",
- " \n",
- " 31 | \n",
- " Peterchen | \n",
- " 2000 | \n",
- "
\n",
- " \n",
- " 32 | \n",
- " Prinzesschen | \n",
- " 2001 | \n",
- "
\n",
- " \n",
- " 33 | \n",
- " Prinzesschen | \n",
- " 2002 | \n",
- "
\n",
- " \n",
- " 34 | \n",
- " Prinzesschen | \n",
- " 2003 | \n",
- "
\n",
- " \n",
- " 35 | \n",
- " Prinzesschen | \n",
- " 2004 | \n",
- "
\n",
- " \n",
- " 36 | \n",
- " Prinzesschen | \n",
- " 2005 | \n",
- "
\n",
- " \n",
- " 37 | \n",
- " Prinzesschen | \n",
- " 2006 | \n",
- "
\n",
- " \n",
- " 38 | \n",
- " Prinzessin | \n",
- " 1998 | \n",
- "
\n",
- " \n",
- " 39 | \n",
- " Prinzessin | \n",
- " 1999 | \n",
- "
\n",
- " \n",
- " 40 | \n",
- " Sohn von Prinzessin | \n",
- " 1998 | \n",
- "
\n",
- " \n",
- " 41 | \n",
- " Sohn von Prinzessin | \n",
- " 1999 | \n",
- "
\n",
- " \n",
- " 42 | \n",
- " Sophia | \n",
- " 2001 | \n",
- "
\n",
- " \n",
- " 43 | \n",
- " Sophia | \n",
- " 2002 | \n",
- "
\n",
- " \n",
- " 44 | \n",
- " Valinka | \n",
- " 2001 | \n",
- "
\n",
- " \n",
- "
\n",
- "
"
- ],
- "text/plain": [
- " name departureyear\n",
- "0 Annamarie 2001\n",
- "1 Annamarie 2002\n",
- "2 Annamarie 2003\n",
- "3 Annamarie 2004\n",
- "4 Annamarie 2005\n",
- "5 Annamarie 2006\n",
- "6 Anneliese 2000\n",
- "7 Anneliese 2001\n",
- "8 Christina 2002\n",
- "9 Christina 2003\n",
- "10 Donna 1999\n",
- "11 Donna 2000\n",
- "12 Felix 2001\n",
- "13 Felix 2002\n",
- "14 Felix 2003\n",
- "15 Felix 2004\n",
- "16 Johann 2002\n",
- "17 Jonas 2001\n",
- "18 Jonas 2002\n",
- "19 Jonas 2003\n",
- "20 Jonas 2004\n",
- "21 Louka 1999\n",
- "22 Louka 2000\n",
- "23 Max 2000\n",
- "24 Moritz 2000\n",
- "25 Penelope 1998\n",
- "26 Penelope 1999\n",
- "27 Penelope 2000\n",
- "28 Penelope 2001\n",
- "29 Peterchen 1998\n",
- "30 Peterchen 1999\n",
- "31 Peterchen 2000\n",
- "32 Prinzesschen 2001\n",
- "33 Prinzesschen 2002\n",
- "34 Prinzesschen 2003\n",
- "35 Prinzesschen 2004\n",
- "36 Prinzesschen 2005\n",
- "37 Prinzesschen 2006\n",
- "38 Prinzessin 1998\n",
- "39 Prinzessin 1999\n",
- "40 Sohn von Prinzessin 1998\n",
- "41 Sohn von Prinzessin 1999\n",
- "42 Sophia 2001\n",
- "43 Sophia 2002\n",
- "44 Valinka 2001"
- ]
- },
- "metadata": {},
- "output_type": "display_data"
- }
- ],
+ "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",
+ "query = \"SELECT * FROM test \"\n",
"\n",
- "# Afficher la table résultat\n",
- "display(resulttable)"
+ "# Afficher le résultat de la requête\n",
+ "%sql {query}"
]
},
{
"cell_type": "markdown",
- "id": "924791a4-5faf-42b9-9c86-314b4fdd459c",
+ "id": "205801eb-d573-4ac8-ba27-369dac009f35",
"metadata": {},
"source": [
- "Then you can compare the resulting table to some other Pandas table:"
+ "
\n",
+ "To clean the database for new tests:"
]
},
{
"cell_type": "code",
- "execution_count": 6,
- "id": "15aa666b-6489-41da-a13d-27fbefbf8194",
+ "execution_count": null,
+ "id": "ad360653-c647-4197-a353-86e970cab111",
"metadata": {},
- "outputs": [
- {
- "ename": "AssertionError",
- "evalue": "DataFrame are different\n\nDataFrame shape mismatch\n[left]: (45, 2)\n[right]: (4, 1)",
- "output_type": "error",
- "traceback": [
- "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
- "\u001b[0;31mAssertionError\u001b[0m Traceback (most recent call last)",
- "\u001b[0;32m/tmp/ipykernel_160/3402427415.py\u001b[0m in \u001b[0;36m\u001b[0;34m\u001b[0m\n\u001b[1;32m 1\u001b[0m \u001b[0mcorrectresult\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mpandas\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mDataFrame\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mdata\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m\"Annamarie\"\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m\"Felix\"\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m\"Jonas\"\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m\"Prinzesschen\"\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcolumns\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m\"name\"\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 2\u001b[0;31m \u001b[0massert_frame_equal\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mresulttable\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcorrectresult\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
- " \u001b[0;31m[... skipping hidden 1 frame]\u001b[0m\n",
- "\u001b[0;32m/usr/local/lib/python3.8/dist-packages/pandas/_testing/asserters.py\u001b[0m in \u001b[0;36mraise_assert_detail\u001b[0;34m(obj, message, left, right, diff, index_values)\u001b[0m\n\u001b[1;32m 663\u001b[0m \u001b[0mmsg\u001b[0m \u001b[0;34m+=\u001b[0m \u001b[0;34mf\"\\n[diff]: {diff}\"\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 664\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 665\u001b[0;31m \u001b[0;32mraise\u001b[0m \u001b[0mAssertionError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mmsg\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m 666\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m 667\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
- "\u001b[0;31mAssertionError\u001b[0m: DataFrame are different\n\nDataFrame shape mismatch\n[left]: (45, 2)\n[right]: (4, 1)"
- ]
- }
- ],
+ "outputs": [],
"source": [
- "correctresult = pandas.DataFrame(data=[\"Annamarie\", \"Felix\", \"Jonas\", \"Prinzesschen\"], columns=[\"name\"])\n",
- "assert_frame_equal(resulttable, correctresult)"
+ "#%sql DROP TABLE test"
]
},
{
"cell_type": "markdown",
- "id": "59034ee8-5468-412f-b662-a3efbed163ee",
+ "id": "52d2a516-8490-4b7b-a4bf-93597d2a89ab",
"metadata": {},
"source": [
"---\n",
"\n",
- "# Creating tables"
+ "# Validate CREATE/INSERT etc. queries"
]
},
{
"cell_type": "markdown",
- "id": "bad4d047-383b-48fd-be20-9baefa12e5e8",
+ "id": "5aa4246c-bafa-41bd-8d83-f50652e738bd",
"metadata": {},
"source": [
- "## Create a new table:"
+ "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": 7,
- "id": "9397fb73-ee94-4e7e-af8b-b2a5b2b7f10b",
+ "execution_count": null,
+ "id": "80fa2de4-17ca-48c9-987d-485c1d31a7d4",
"metadata": {},
- "outputs": [
- {
- "name": "stdout",
- "output_type": "stream",
- "text": [
- " * sqlite:///stork-mooc.sqlite\n",
- "Done.\n"
- ]
- },
- {
- "data": {
- "text/plain": [
- "[]"
- ]
- },
- "execution_count": 7,
- "metadata": {},
- "output_type": "execute_result"
- }
- ],
+ "outputs": [],
"source": [
- "# Exécuter une requête\n",
- "%sql CREATE TABLE test ('date' date, 'storkname' text)\n",
- "#%sql DROP TABLE test"
+ "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": "b53b09d9-e0d0-4ebc-9631-db29736eaa1e",
+ "id": "65a8f41d-68c2-4fe3-b1f2-6c4281411d23",
"metadata": {},
"source": [
- "## Insert values in the table"
+ "## Select the table the student has created and compare the two hash"
]
},
{
"cell_type": "code",
- "execution_count": 8,
- "id": "eab04aa5-69b2-47d4-8e06-d387164e8a06",
+ "execution_count": null,
+ "id": "ab092c80-b326-4526-b0ad-5fa8ab6fcfa7",
"metadata": {},
- "outputs": [
- {
- "name": "stdout",
- "output_type": "stream",
- "text": [
- " * sqlite:///stork-mooc.sqlite\n",
- "1 rows affected.\n"
- ]
- },
- {
- "data": {
- "text/plain": [
- "[]"
- ]
- },
- "execution_count": 8,
- "metadata": {},
- "output_type": "execute_result"
- }
- ],
+ "outputs": [],
"source": [
- "# Exécuter une requête\n",
- "%sql INSERT INTO test ('date', 'storkname') VALUES (\"2021-10-14\", \"stork1\")"
+ "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": "8c7276a8-e263-43bb-8f6f-b78ffcc201df",
+ "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": [
- "## Display the table"
+ "To automatically validate the query you first need to store the query into a Python variable:"
]
},
{
"cell_type": "code",
- "execution_count": 9,
- "id": "71866394-ca03-45a3-9731-72f56c42b0c3",
+ "execution_count": null,
+ "id": "1f659ce0-ad77-421c-ab1b-81bc9a6c39b5",
"metadata": {},
- "outputs": [
- {
- "name": "stdout",
- "output_type": "stream",
- "text": [
- " * sqlite:///stork-mooc.sqlite\n",
- "Done.\n"
- ]
- },
- {
- "data": {
- "text/html": [
- "\n",
- " \n",
- " date | \n",
- " storkname | \n",
- "
\n",
- " \n",
- " 2021-10-14 | \n",
- " stork1 | \n",
- "
\n",
- "
"
- ],
- "text/plain": [
- "[('2021-10-14', 'stork1')]"
- ]
- },
- "execution_count": 9,
- "metadata": {},
- "output_type": "execute_result"
- }
- ],
+ "outputs": [],
"source": [
"# Ecrire la requête\n",
- "query = \"SELECT * FROM test \"\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": "4ad204cc-ec86-46f2-aeb5-2d287c769f0c",
+ "id": "d8278589-c24b-4ea6-9022-9dd09439bcb9",
"metadata": {},
"outputs": [],
"source": [
- "# Importing libraries\n",
- "import sqlite3\n",
- "import pandas\n",
+ "from SQLFusionEmboitement_v2 import *\n",
+ "question1_validation(query)"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "46acfe9a-dace-4dde-95f8-4b99fe89225d",
+ "metadata": {},
+ "source": [
+ "## Validating tables\n",
"\n",
- "# Etablir la connexion à la base de données\n",
- "con = sqlite3.connect(\"stork-mooc.sqlite\")\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",
- "# Donner la requête à executer\n",
- "query = \"SELECT * FROM test\"\n",
+ "# Récupérer le résultat de la requête\n",
+ "resulttable = %sql {query}\n",
"\n",
- "# Executer la requête et récupérer la table résultat\n",
- "resulttable = pandas.read_sql_query(query, con)\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
}