Page MenuHomec4science
Files F85313460

SQL_DML.py
No OneTemporary
Actions

File Metadata

Created
Sat, Sep 28, 06:13

SQL_DML.py
View Options

import sqlite3
import pandas
import hashlib
import pickle
from sqlalchemy import create_engine
from pandas.io.sql import DatabaseError
"""
Utility functions
"""
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 run_query(q):
conn = sqlite3.connect("stork-mooc.sqlite")
return pandas.read_sql_query(q, conn)
def display_observations_table():
query = "select * from observations"
table = pandas.DataFrame()
try:
table = run_query(query)
except DatabaseError as e:
print(f"Looks like the 'observations' table does not exist...\n")
return table
"""
HOW TO GENERATE THE CORRECT HASH (to be done in a separate notebook)
correctresult = pandas.DataFrame(data=[["2021-10-14", "stork1"],["2021-11-19", "stork2"]], columns=['date', 'storkname'])
display(correctresult)
correctresponse_hash = generate_hash(correctresult)
correctresponse_hash
"""
"""
Validation function for Question 1
"""
def question1_validation():
correctresponse_hash = '354457a15f87002c212b4f230550efba'
selectresult = display_observations_table()
"display(selectresult)"
studentresult_hash = generate_hash(selectresult)
"studentresult_hash"
try:
message = "Incorrect query."
assert studentresult_hash == correctresponse_hash
except AssertionError as 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!")
return
#try:
# assert studentresult_hash == correctresponse_hash, "Incorrect query."
#except AssertionError as ae:
# print("Your query didn't pass the validation tests.")
# raise ae
#print("Your query has passed the validation tests and is evaluated as correct, well done!")
"""
Validation function for Question 2
"""
def question2_validation():
correctresponse_hash = 'ff6fcf9d9395ea04e209dd3b012aa26e'
selectresult = display_observations_table()
"display(selectresult)"
studentresult_hash = generate_hash(selectresult)
"studentresult_hash"
try:
message = "Incorrect query."
assert studentresult_hash == correctresponse_hash
except AssertionError as 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!")
return
#try:
# assert studentresult_hash == correctresponse_hash, "Incorrect query."
#except AssertionError as ae:
# print("Your query didn't pass the validation tests.")
# raise ae
#print("Your query has passed the validation tests and is evaluated as correct, well done!")
"""
Validation function for Question 3
"""
def question3_validation():
correctresponse_hash = '664b73c3d54992595a313e19cca4cdfd'
selectresult = display_observations_table()
"display(selectresult)"
studentresult_hash = generate_hash(selectresult)
"studentresult_hash"
try:
message = "Incorrect query."
assert studentresult_hash == correctresponse_hash
except AssertionError as 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!")
return
#try:
# assert studentresult_hash == correctresponse_hash, "Incorrect query."
#except AssertionError as ae:
# print("Your query didn't pass the validation tests.")
# raise ae
#print("Your query has passed the validation tests and is evaluated as correct, well done!")
"""
Validation function for Question 4
"""
def question4_validation():
correctresponse_hash = 'f5a014d652984f3efb396fa29c3473bc'
selectresult = display_observations_table()
"display(selectresult)"
studentresult_hash = generate_hash(selectresult)
"studentresult_hash"
try:
message = "Incorrect query."
assert studentresult_hash == correctresponse_hash
except AssertionError as 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!")
return
#try:
# assert studentresult_hash == correctresponse_hash, "Incorrect query."
#except AssertionError as ae:
# print("Your query didn't pass the validation tests.")
# raise ae
#print("Your query has passed the validation tests and is evaluated as correct, well done!")
"""
Validation function for Question 5
"""
def question5_validation():
correctresponse_hash = '1ccb35f50108eecdbc8f672682f68d30'
selectresult = display_observations_table()
"display(selectresult)"
studentresult_hash = generate_hash(selectresult)
"studentresult_hash"
try:
message = "Incorrect query."
assert studentresult_hash == correctresponse_hash
except AssertionError as 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!")
return
#try:
# assert studentresult_hash == correctresponse_hash, "Incorrect query."
#except AssertionError as ae:
# print("Your query didn't pass the validation tests.")
# raise ae
#print("Your query has passed the validation tests and is evaluated as correct, well done!")

Event Timeline

c4science · Help