Exercise 7 - SQL Quiz Prep

# DO NOT MODIFY THE CODE IN THIS CELL
import unittest
import base64

tc = unittest.TestCase()

# YOUR CODE BEGINS

# YOUR CODE ENDS

# DO NOT CHANGE THE CODE IN THIS CELL
tc.assertTrue(
    "pd" in globals(), "Check whether you have correctly imported Pandas with an alias."
)
tc.assertTrue(
    "np" in globals(), "Check whether you have correctly imported NumPy with an alias."
)
tc.assertTrue(
    "sqlite3" in globals(),
    "Check whether you have correctly imported the sqlite3 package.",
)

conn = sqlite3.connect("lending-club-loan-results.db")
c = conn.cursor()

tables = list(
    pd.read_sql_query("SELECT * FROM sqlite_master WHERE type='table';", con=conn)[
        "tbl_name"
    ]
)

if "loans" in tables:
    c.execute(f"DELETE FROM loans")
    conn.commit()

pd.read_csv(
    "https://github.com/bdi475/datasets/raw/main/lending-club-loan-results.csv.gz",
    compression="gzip",
).to_sql(name="loans", index=False, con=conn, if_exists="append")

conn.close()

# DO NOT CHANGE THE CODE IN THIS CELL
conn_checker = sqlite3.connect("lending-club-loan-results.db")
table_to_check = "loans"

# Check if table exists
user_tables = list(
    pd.read_sql_query(
        'SELECT * FROM sqlite_master WHERE type="table";', con=conn_checker
    )["tbl_name"]
)
tc.assertTrue(
    table_to_check in user_tables,
    f"{table_to_check} does not exist in your NWT.db file!",
)

conn_checker.close()

conn = sqlite3.connect("lending-club-loan-results.db")
display(pd.read_sql_query("SELECT * FROM loans LIMIT 10", con=conn))
conn.close()

# YOUR CODE BEGINS

# YOUR CODE ENDS

conn = sqlite3.connect("lending-club-loan-results.db")
df_result = pd.read_sql_query(query_high_profile_defaults, con=conn)
display(df_result)
conn.close()

_test_case = "part-01"
_points = 6

conn = sqlite3.connect("lending-club-loan-results.db")
decoded_query = base64.b64decode(
    b"ClNFTEVDVCAqCkZST00gbG9hbnMKV0hFUkUgKGpvYl90aXRsZSA9ICJB\
dHRvcm5leSIpIEFORCAoYW5udWFsX2luYyA+IDMwMDAwMCkgQU5EIChkaWRfZGVmYXVsdCA9IDEpOwo="
).decode()
df_check = pd.read_sql_query(decoded_query, con=conn)
pd.testing.assert_frame_equal(
    df_result.sort_values(df_result.columns.tolist()).reset_index(drop=True),
    df_check.sort_values(df_check.columns.tolist()).reset_index(drop=True),
)
conn.close()

# YOUR CODE BEGINS

# YOUR CODE ENDS

conn = sqlite3.connect("lending-club-loan-results.db")
df_result = pd.read_sql_query(query_stats_by_grade, con=conn)
display(df_result)
conn.close()

_test_case = "part-02"
_points = 7

conn = sqlite3.connect("lending-club-loan-results.db")
decoded_query = base64.b64decode(
    b"ClNFTEVDVAogICAgZ3JhZGUsCiAgICBBVkcoaW\
50X3JhdGUpIEFTIGF2Z19pbnRfcmF0ZSwKICAgIEFWRyhkaWRfZGVmYXVsdCkgQVMgZGVmYXVsdF9yYXRlCkZST00gbG9hbnMKR1JPVVAgQlkgZ3JhZGUKT1JERVIgQlkgZ3JhZGU7Cg=="
).decode()
df_check = pd.read_sql_query(decoded_query, con=conn)
pd.testing.assert_frame_equal(
    df_result.reset_index(drop=True), df_check.reset_index(drop=True)
)
conn.close()

# YOUR CODE BEGINS

# YOUR CODE ENDS

conn = sqlite3.connect("lending-club-loan-results.db")
df_result = pd.read_sql_query(query_stats_by_purpose, con=conn)
display(df_result)
conn.close()

_test_case = "part-03"
_points = 7

conn = sqlite3.connect("lending-club-loan-results.db")
decoded_query = base64.b64decode(
    b"ClNFTEVDVAogICAgcHVycG9zZSwKICA\
    gIENPVU5UKCopIEFTIG51bV9sb2FucywKICAgIEFWRyhpbnRfcmF0ZSkgQVMgYXZnX\
    2ludF9yYXRlLAogICAgQVZHKGRpZF9kZWZhdWx0KSBBUyBkZWZhdWx0X3JhdGUKRlJ\
    PTSBsb2FucwpHUk9VUCBCWSBwdXJwb3NlCk9SREVSIEJZIG51bV9sb2FucyBERVNDOwo="
).decode()
df_check = pd.read_sql_query(decoded_query, con=conn)
pd.testing.assert_frame_equal(
    df_result.reset_index(drop=True), df_check.reset_index(drop=True)
)
conn.close()

print("🎯 Restart and run all successful")