diff --git a/Alternative.ipynb b/Alternative.ipynb
index 363e518..d6aa3ac 100644
--- a/Alternative.ipynb
+++ b/Alternative.ipynb
@@ -1,165 +1,1192 @@
{
"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,
+ "execution_count": 1,
"id": "b6fd0f45-e221-4acb-b22d-2b49828a6e84",
"metadata": {},
"outputs": [],
"source": [
- "%%capture\n",
+ "# 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": "bad4d047-383b-48fd-be20-9baefa12e5e8",
+ "id": "fdee5329-d2b0-48c8-863c-e88a9b282f72",
"metadata": {},
"source": [
- "## Create a new table:"
+ "## Execute queries\n",
+ "\n",
+ "Simplest method:"
]
},
{
"cell_type": "code",
- "execution_count": null,
- "id": "9397fb73-ee94-4e7e-af8b-b2a5b2b7f10b",
+ "execution_count": 2,
+ "id": "7528bbf3-bf15-40b7-a3d0-ac09b07c29e2",
"metadata": {},
- "outputs": [],
+ "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"
+ }
+ ],
"source": [
- "%sql CREATE TABLE test ('date' date, 'storkname' text)\n",
- "#%sql DROP TABLE test"
+ "%sql SELECT * FROM migration "
]
},
{
"cell_type": "markdown",
- "id": "b53b09d9-e0d0-4ebc-9631-db29736eaa1e",
+ "id": "f3823928-6993-4b8e-9fc4-0347991b1aca",
"metadata": {},
"source": [
- "## Insert values in the table"
+ "# Automatic validation with ipython-sql\n",
+ "\n",
+ "## Validating queries"
]
},
{
- "cell_type": "code",
- "execution_count": null,
- "id": "eab04aa5-69b2-47d4-8e06-d387164e8a06",
+ "cell_type": "markdown",
+ "id": "8e3fd147-d4eb-45c2-9725-c3de3cecf425",
"metadata": {},
- "outputs": [],
"source": [
- "%sql INSERT INTO test ('date', 'storkname') VALUES (\"2021-10-14\", \"stork1\")"
+ "To automatically validate the query you first need to store the query into a Python variable:"
]
},
{
- "cell_type": "markdown",
- "id": "8c7276a8-e263-43bb-8f6f-b78ffcc201df",
+ "cell_type": "code",
+ "execution_count": 3,
+ "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",
+ " 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"
+ }
+ ],
"source": [
- "## Display the table - Method 1"
+ "# 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": "b2a6f890-8f11-4079-b4cb-ec1ad18bbaf9",
+ "id": "6e6d7200-2ef8-4963-af22-5aa8d0f1b030",
"metadata": {},
"source": [
- "### Option A: display only"
+ "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": "71866394-ca03-45a3-9731-72f56c42b0c3",
+ "execution_count": 4,
+ "id": "d8278589-c24b-4ea6-9022-9dd09439bcb9",
"metadata": {},
- "outputs": [],
+ "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."
+ ]
+ }
+ ],
"source": [
- "%sql SELECT * FROM test"
+ "from SQLFusionEmboitement_v2 import *\n",
+ "question1_validation(query)"
]
},
{
"cell_type": "markdown",
- "id": "8fc1b60d-eb6f-4b7b-bfad-d0170430941e",
+ "id": "46acfe9a-dace-4dde-95f8-4b99fe89225d",
"metadata": {},
"source": [
- "### Option B: store the result in a dataframe variable, to reuse after"
+ "## 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": "5eaa2a50-88ad-4c5c-a8e6-b19a7c86cb04",
+ "execution_count": 5,
+ "id": "817b6339-123d-426c-b399-ea08b2d76591",
"metadata": {},
- "outputs": [],
+ "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"
+ }
+ ],
"source": [
+ "# Ecrire la requête\n",
+ "query = \"SELECT * FROM migration\"\n",
+ "\n",
"# Récupérer le résultat de la requête\n",
- "result = %sql SELECT * FROM test\n",
+ "resulttable = %sql {query}\n",
"\n",
"# Transformer en dataframe\n",
- "result = result.DataFrame()\n",
+ "resulttable = resulttable.DataFrame()\n",
"\n",
"# Afficher la table résultat\n",
"display(resulttable)"
]
},
{
"cell_type": "markdown",
- "id": "d6ad4489-fb31-4883-b745-a67eca0ab932",
+ "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": 6,
+ "id": "15aa666b-6489-41da-a13d-27fbefbf8194",
+ "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)"
+ ]
+ }
+ ],
+ "source": [
+ "correctresult = pandas.DataFrame(data=[\"Annamarie\", \"Felix\", \"Jonas\", \"Prinzesschen\"], columns=[\"name\"])\n",
+ "assert_frame_equal(resulttable, correctresult)"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "59034ee8-5468-412f-b662-a3efbed163ee",
"metadata": {},
"source": [
- "## Display the table - Method 2 (with Pandas)"
+ "---\n",
+ "\n",
+ "# Creating tables"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "bad4d047-383b-48fd-be20-9baefa12e5e8",
+ "metadata": {},
+ "source": [
+ "## Create a new table:"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 7,
+ "id": "9397fb73-ee94-4e7e-af8b-b2a5b2b7f10b",
+ "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"
+ }
+ ],
+ "source": [
+ "# Exécuter une requête\n",
+ "%sql CREATE TABLE test ('date' date, 'storkname' text)\n",
+ "#%sql DROP TABLE test"
+ ]
+ },
+ {
+ "cell_type": "markdown",
+ "id": "b53b09d9-e0d0-4ebc-9631-db29736eaa1e",
+ "metadata": {},
+ "source": [
+ "## Insert values in the table"
+ ]
+ },
+ {
+ "cell_type": "code",
+ "execution_count": 8,
+ "id": "eab04aa5-69b2-47d4-8e06-d387164e8a06",
+ "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"
+ }
+ ],
+ "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": 9,
+ "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",
+ " 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"
+ }
+ ],
+ "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": "code",
"execution_count": null,
"id": "4ad204cc-ec86-46f2-aeb5-2d287c769f0c",
"metadata": {},
"outputs": [],
"source": [
"# Importing libraries\n",
"import sqlite3\n",
"import pandas\n",
"\n",
"# Etablir la connexion à la base de données\n",
"con = sqlite3.connect(\"stork-mooc.sqlite\")\n",
"\n",
"# Donner la requête à executer\n",
"query = \"SELECT * FROM test\"\n",
"\n",
"# Executer la requête et récupérer la table résultat\n",
"resulttable = pandas.read_sql_query(query, con)\n",
"\n",
"# Afficher la table résultat\n",
"display(resulttable)"
]
}
],
"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
}