Page Menu
Home
c4science
Search
Configure Global Search
Log In
Files
F63143645
SQL_requetes_geometriques.py
No One
Temporary
Actions
Download File
Edit File
Delete File
View Transforms
Subscribe
Mute Notifications
Award Token
Subscribers
None
File Metadata
Details
File Info
Storage
Attached
Created
Sat, May 18, 01:31
Size
15 KB
Mime Type
text/x-python
Expires
Mon, May 20, 01:31 (1 d, 23 h)
Engine
blob
Format
Raw Data
Handle
17736993
Attached To
R11932 SIG-1_Exercices_SQL
SQL_requetes_geometriques.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
=
'2c35771572b931662737eca58d64671c'
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
=
'7a9d0f6358dc6b20574573b5eba3daff'
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
=
'7ab0edb51bfb33aac2100caf334f0014'
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
=
'bbf25e2e93f636e94727f410ccbf6569'
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 5
=> 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
question5_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
=
'00bb75d340e2a5752650bd933f2a7aff'
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 6
=> 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
question6_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
=
'864921c271f65adb88d3fb965bec3c83'
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 7
=> 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
question7_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
=
'cad419c804eeba32a4f89b3ed8a8cac9'
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
Event Timeline
Log In to Comment