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", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + "
namedepartureyear
Annamarie2001
Annamarie2002
Annamarie2003
Annamarie2004
Annamarie2005
Annamarie2006
Anneliese2000
Anneliese2001
Christina2002
Christina2003
Donna1999
Donna2000
Felix2001
Felix2002
Felix2003
Felix2004
Johann2002
Jonas2001
Jonas2002
Jonas2003
Jonas2004
Louka1999
Louka2000
Max2000
Moritz2000
Penelope1998
Penelope1999
Penelope2000
Penelope2001
Peterchen1998
Peterchen1999
Peterchen2000
Prinzesschen2001
Prinzesschen2002
Prinzesschen2003
Prinzesschen2004
Prinzesschen2005
Prinzesschen2006
Prinzessin1998
Prinzessin1999
Sohn von Prinzessin1998
Sohn von Prinzessin1999
Sophia2001
Sophia2002
Valinka2001
" + ], + "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", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + "
namedepartureyear
Annamarie2001
Annamarie2002
Annamarie2003
Annamarie2004
Annamarie2005
Annamarie2006
Anneliese2000
Anneliese2001
Christina2002
Christina2003
Donna1999
Donna2000
Felix2001
Felix2002
Felix2003
Felix2004
Johann2002
Jonas2001
Jonas2002
Jonas2003
Jonas2004
Louka1999
Louka2000
Max2000
Moritz2000
Penelope1998
Penelope1999
Penelope2000
Penelope2001
Peterchen1998
Peterchen1999
Peterchen2000
Prinzesschen2001
Prinzesschen2002
Prinzesschen2003
Prinzesschen2004
Prinzesschen2005
Prinzesschen2006
Prinzessin1998
Prinzessin1999
Sohn von Prinzessin1998
Sohn von Prinzessin1999
Sophia2001
Sophia2002
Valinka2001
" + ], + "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", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + "
namedepartureyear
0Annamarie2001
1Annamarie2002
2Annamarie2003
3Annamarie2004
4Annamarie2005
5Annamarie2006
6Anneliese2000
7Anneliese2001
8Christina2002
9Christina2003
10Donna1999
11Donna2000
12Felix2001
13Felix2002
14Felix2003
15Felix2004
16Johann2002
17Jonas2001
18Jonas2002
19Jonas2003
20Jonas2004
21Louka1999
22Louka2000
23Max2000
24Moritz2000
25Penelope1998
26Penelope1999
27Penelope2000
28Penelope2001
29Peterchen1998
30Peterchen1999
31Peterchen2000
32Prinzesschen2001
33Prinzesschen2002
34Prinzesschen2003
35Prinzesschen2004
36Prinzesschen2005
37Prinzesschen2006
38Prinzessin1998
39Prinzessin1999
40Sohn von Prinzessin1998
41Sohn von Prinzessin1999
42Sophia2001
43Sophia2002
44Valinka2001
\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", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + " \n", + "
datestorkname
2021-10-14stork1
" + ], + "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 }