diff --git a/SQLCreate_Pandas.ipynb b/SQLCreate_Pandas.ipynb index a142d68..65c5d41 100644 --- a/SQLCreate_Pandas.ipynb +++ b/SQLCreate_Pandas.ipynb @@ -1,194 +1,221 @@ { "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", - "import pandas\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 = \"DROP TABLE observations\"\n", - "query = \"CREATE TABLE observations ('date' date,'storkname' text)\"\n", - "#query = \"INSERT INTO observations ('date', 'storkname') VALUES ('2021-10-14', 'stork1')\"\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 } diff --git a/SQLCreate_Pandas.py b/SQLCreate_Pandas.py index 5e905ef..56173df 100644 --- a/SQLCreate_Pandas.py +++ b/SQLCreate_Pandas.py @@ -1,42 +1,58 @@ import sqlite3 import pandas import hashlib import pickle from sqlalchemy import create_engine -from pandas.io.sql import DatabaseError +from pandas.io.sql import DatabaseError """ Utility functions """ def generate_hash(dataframe): return hashlib.md5(pickle.dumps(dataframe)).hexdigest() def run_query(q): conn = sqlite3.connect("stork-mooc.sqlite") return pandas.read_sql_query(q, conn) def display_observations_table(): query = "select * from observations" table = pandas.DataFrame() try: table = run_query(query) except DatabaseError as e: - print(f"Looks like the 'observations' table does not exist...\n\n{e}") + print(f"Looks like the 'observations' table does not exist...") return table +def delete_observations_table(): + query = "DROP TABLE observations" + + conn = sqlite3.connect("stork-mooc.sqlite") + try: + conn.cursor().execute(query) + conn.commit() + print("Table deleted.") + + except sqlite3.OperationalError as e: + print(f"Looks like the 'observations' table does not exist...") + + + + + ## Alternative way to send queries with auto-closing connection ## Issue: the hash obtained for the returned table does not match... def display_observations_table_engine(): engine = create_engine("sqlite:///stork-mooc.sqlite") conn = sqlite3.connect("stork-mooc.sqlite") table = pandas.DataFrame() try: with engine.connect() as conn, conn.begin(): table = pandas.read_sql_table("observations", conn) except ValueError: print("Looks like the 'observations' table does not exist...") return table