Page Menu
Home
c4science
Search
Configure Global Search
Log In
Files
F86463845
SQLFusionEmboitement_v2.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
Sun, Oct 6, 15:48
Size
5 KB
Mime Type
text/x-python
Expires
Tue, Oct 8, 15:48 (1 d, 23 h)
Engine
blob
Format
Raw Data
Handle
21427680
Attached To
rSQLVAL SQLValidation
SQLFusionEmboitement_v2.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
* 2001 except 2003
* 2003 except 2001
* 2001 only
* 2003 only
"""
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.'
+
'
\n
You should get only the storks who departed BOTH in 2001 and in 2003.'
+
'
\n
Have you drawn a diagram on paper to determine which logical operator to use?'
)
# Misconception: use of EXCEPT
query
=
"select name from migration where departureyear=2001 except select name from migration where departureyear=2003"
if
run_query
(
studentquery
)
.
equals
(
run_query
(
query
)):
print
(
'You selected the storks who departed in 2001 and REMOVED the storks who also departed in 2003.'
+
'
\n
You should get those who departed BOTH in 2001 AND 2003.'
+
'
\n
Have you drawn a diagram on paper to determine which logical operator to use?'
)
# Misconception: use of EXCEPT (opposite direction)
query
=
"select name from migration where departureyear=2003 except select name from migration where departureyear=2001"
if
run_query
(
studentquery
)
.
equals
(
run_query
(
query
)):
print
(
'You selected the storks who departed in 2003 and REMOVED the storks who also departed in 2001.'
+
'
\n
You should get those who departed BOTH in 2001 AND 2003.'
+
'
\n
Have you drawn a diagram on paper to determine which logical operator to use?'
)
# Misconception: partial query
query
=
"select name from migration where departureyear=2001"
if
run_query
(
studentquery
)
.
equals
(
run_query
(
query
)):
print
(
'You selected only the storks who departed in 2001.'
+
'
\n
This is a good way to start writing this type of query but you are only halfway to the solution.'
+
'
\n
How could you get the storks who departed BOTH in 2001 AND 2003?'
+
'
\n
Maybe draw a diagram to determine which logical operator to use?'
)
# Misconception: partial query (other side)
query
=
"select name from migration where departureyear=2003"
if
run_query
(
studentquery
)
.
equals
(
run_query
(
query
)):
print
(
'You selected only the storks who departed in 2003.'
+
'
\n
This is a good way to start writing this type of query but you are only halfway to the solution.'
+
'
\n
How could you get the storks who departed BOTH in 2001 AND 2003?'
+
'
\n
Maybe draw a diagram 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 only compare with the correct answer
"""
def
question2_validation
(
studentquery
):
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 functions, as below:
# generate_hash(run_query("write the correct query here")
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!"
)
Event Timeline
Log In to Comment