Page Menu
Home
c4science
Search
Configure Global Search
Log In
Files
F85313382
SQL_agregation.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, Sep 28, 06:12
Size
13 KB
Mime Type
text/x-python
Expires
Mon, Sep 30, 06:12 (2 d)
Engine
blob
Format
Raw Data
Handle
21157359
Attached To
R11932 SIG-1_Exercices_SQL
SQL_agregation.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
(
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
=
'1e4eb8d349e31c66dc9c2b0572c5065b'
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
=
'b72293088ed90148b5db49b4816ae846'
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
=
'e5d47c35768763cda30dc6f92a8439b9'
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
=
'25b61a0bfacb7317dad287b70d32ba28'
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
=
'9e521a5294de5ef59d9714da2193f78c'
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
=
'dbda7e8c843c7d87dc68bc398243e8f4'
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