Homec4science
Diffusion SIG-1_Exercices_SQL (master) Browse

R11932/7-Requetes_spatiales_topologiques/SQL_requetes_topologiques.py
7b3e5de9811emaster

Last Change
Raw File

SQL_requetes_topologiques.py
View Options

import sqlite3
import pandas
import hashlib
import pickle
from pandas._testing import assert_frame_equal
"""
Utility functions
"""
def run_query(q):
con = sqlite3.connect("india.sqlite")
con.enable_load_extension(True)
con.load_extension("mod_spatialite")
return pandas.read_sql_query(q, con)
def generate_hash(something):
# Old method:
#return hashlib.md5(pickle.dumps(dataframe)).hexdigest()
# New method:
if isinstance(something, pandas.core.frame.DataFrame):
to_hash = ""
# Get hash of column names, in order
for col in something.columns:
to_hash += generate_hash(col)
# Add hash of the dataframe content
to_hash += str(pandas.util.hash_pandas_object(something).sum())
# Calculate hash of the hashes
return(generate_hash(to_hash))
#return pandas.util.hash_pandas_object(something).sum()
elif isinstance(something, str):
return hashlib.md5(something.encode('utf-8')).hexdigest()
else:
return -1
def check_hash(dataframe, dataframehash):
return generate_hash(dataframe) == dataframehash
"""
Validation function for Question 1
=> We compare with the possible mistakes made by students
=> Then we finally compare with the correct answer
# Possible mistakes: we check that the result obtained by the student is different from:
# * the union of 2001 and 2003
"""
def question1_validation(studentquery):
# Misconception: use of UNION
#query = "select name from migration where departureyear=2001 union select name from migration where departureyear=2003"
#if run_query(studentquery).equals(run_query(query)):
# print('You selected the storks who departed in 2001 AS WELL AS the storks who departed in 2003.'
# +'\nYou should get only the storks who departed BOTH in 2001 and in 2003.'
# +'\nHave you drawn a diagram on paper to determine which logical operator to use?')
# Default case that catches all misconceptions (including the mistakes flagged above)
# => we compare with the correct answer
try:
# Compare with the MD5 hash of the result table, which means the student can neither know the correct query nor the correct result table
# (this also means that the error message cannot help students correct their answer)
# NB: the hash of the correct result table has to be generated beforehand (using the utility function)
# generate_hash(run_query(correctquery))
response_hash = 'f533571233ffe9ff250c0e61076418cb'
message = "Incorrect query."
assert check_hash(run_query(studentquery), response_hash)
except AssertionError as ae:
#print("Your query didn't pass the validation tests.")
# We raise the exception here to stop execution and prevent the success message to be printed
#raise ae
print("Your query didn't pass the validation tests:")
print(message)
return
#print("Your query has passed the validation tests and is evaluated as correct, well done!")
print("Your query has passed the validation tests and is evaluated as correct, well done!")
return
"""
Validation function for Question 2
=> We compare with the possible mistakes made by students
=> Then we finally compare with the correct answer
# Possible mistakes: we check that the result obtained by the student is different from:
# * the union of 2001 and 2003
"""
def question2_validation(studentquery):
# Misconception: use of UNION
#query = "select name from migration where departureyear=2001 union select name from migration where departureyear=2003"
#if run_query(studentquery).equals(run_query(query)):
# print('You selected the storks who departed in 2001 AS WELL AS the storks who departed in 2003.'
# +'\nYou should get only the storks who departed BOTH in 2001 and in 2003.'
# +'\nHave you drawn a diagram on paper to determine which logical operator to use?')
# Default case that catches all misconceptions (including the mistakes flagged above)
# => we compare with the correct answer
try:
# Compare with the MD5 hash of the result table, which means the student can neither know the correct query nor the correct result table
# (this also means that the error message cannot help students correct their answer)
# NB: the hash of the correct result table has to be generated beforehand (using the utility function)
# generate_hash(run_query(correctquery))
response_hash = 'c3889fb606a0c9d5e06c0a5bd0d02a50'
message = "Incorrect query."
assert check_hash(run_query(studentquery), response_hash)
except AssertionError as ae:
#print("Your query didn't pass the validation tests.")
# We raise the exception here to stop execution and prevent the success message to be printed
#raise ae
print("Your query didn't pass the validation tests:")
print(message)
return
#print("Your query has passed the validation tests and is evaluated as correct, well done!")
print("Your query has passed the validation tests and is evaluated as correct, well done!")
return
"""
Validation function for Question 3
=> We compare with the possible mistakes made by students
=> Then we finally compare with the correct answer
# Possible mistakes: we check that the result obtained by the student is different from:
# * the union of 2001 and 2003
"""
def question3_validation(studentquery):
# Misconception: use of UNION
#query = "select name from migration where departureyear=2001 union select name from migration where departureyear=2003"
#if run_query(studentquery).equals(run_query(query)):
# print('You selected the storks who departed in 2001 AS WELL AS the storks who departed in 2003.'
# +'\nYou should get only the storks who departed BOTH in 2001 and in 2003.'
# +'\nHave you drawn a diagram on paper to determine which logical operator to use?')
# Default case that catches all misconceptions (including the mistakes flagged above)
# => we compare with the correct answer
try:
# Compare with the MD5 hash of the result table, which means the student can neither know the correct query nor the correct result table
# (this also means that the error message cannot help students correct their answer)
# NB: the hash of the correct result table has to be generated beforehand (using the utility function)
# generate_hash(run_query(correctquery))
response_hash = 'fa3c7ad4a555eb18fd340465e63ba693'
message = "Incorrect query."
assert check_hash(run_query(studentquery), response_hash)
except AssertionError as ae:
#print("Your query didn't pass the validation tests.")
# We raise the exception here to stop execution and prevent the success message to be printed
#raise ae
print("Your query didn't pass the validation tests:")
print(message)
return
#print("Your query has passed the validation tests and is evaluated as correct, well done!")
print("Your query has passed the validation tests and is evaluated as correct, well done!")
return
"""
Validation function for Question 4
=> We compare with the possible mistakes made by students
=> Then we finally compare with the correct answer
# Possible mistakes: we check that the result obtained by the student is different from:
# * the union of 2001 and 2003
"""
def question4_validation(studentquery):
# Misconception: use of UNION
#query = "select name from migration where departureyear=2001 union select name from migration where departureyear=2003"
#if run_query(studentquery).equals(run_query(query)):
# print('You selected the storks who departed in 2001 AS WELL AS the storks who departed in 2003.'
# +'\nYou should get only the storks who departed BOTH in 2001 and in 2003.'
# +'\nHave you drawn a diagram on paper to determine which logical operator to use?')
# Default case that catches all misconceptions (including the mistakes flagged above)
# => we compare with the correct answer
try:
# Compare with the MD5 hash of the result table, which means the student can neither know the correct query nor the correct result table
# (this also means that the error message cannot help students correct their answer)
# NB: the hash of the correct result table has to be generated beforehand (using the utility function)
# generate_hash(run_query(correctquery))
response_hash = '045104975f8d478422f35d50ca52d3b8'
message = "Incorrect query."
assert check_hash(run_query(studentquery), response_hash)
except AssertionError as ae:
#print("Your query didn't pass the validation tests.")
# We raise the exception here to stop execution and prevent the success message to be printed
#raise ae
print("Your query didn't pass the validation tests:")
print(message)
return
#print("Your query has passed the validation tests and is evaluated as correct, well done!")
print("Your query has passed the validation tests and is evaluated as correct, well done!")
return
c4science · Help