Page Menu
Home
c4science
Search
Configure Global Search
Log In
Files
F60972188
SQL_fusion.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
Fri, May 3, 17:25
Size
9 KB
Mime Type
text/x-python
Expires
Sun, May 5, 17:25 (2 d)
Engine
blob
Format
Raw Data
Handle
17398619
Attached To
R11938 GIS-1_SQL_Exercises
SQL_fusion.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
(
"stork-mooc.sqlite"
)
return
pandas
.
read_sql_query
(
q
,
con
)
def
generate_hash
(
dataframe
):
return
hashlib
.
md5
(
pickle
.
dumps
(
dataframe
))
.
hexdigest
()
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
=
'b27b60409c967bcb8a93dbbc75ba33cd'
assert
check_hash
(
run_query
(
studentquery
),
response_hash
),
"Incorrect query."
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 has passed the validation tests and is evaluated as correct, well done!"
)
"""
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
=
'41af0d89e1ead68b5ed15fa9013a3493'
assert
check_hash
(
run_query
(
studentquery
),
response_hash
),
"Incorrect query."
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 has passed the validation tests and is evaluated as correct, well done!"
)
"""
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
=
'b27b60409c967bcb8a93dbbc75ba33cd'
assert
check_hash
(
run_query
(
studentquery
),
response_hash
),
"Incorrect query."
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 has passed the validation tests and is evaluated as correct, well done!"
)
"""
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
=
'54caca6dd78ac6240be397455424bbda'
assert
check_hash
(
run_query
(
studentquery
),
response_hash
),
"Incorrect query."
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 has passed the validation tests and is evaluated as correct, well done!"
)
"""
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
=
'54caca6dd78ac6240be397455424bbda'
assert
check_hash
(
run_query
(
studentquery
),
response_hash
),
"Incorrect query."
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 has passed the validation tests and is evaluated as correct, well done!"
)
Event Timeline
Log In to Comment