# HIDDEN
# Clear previously defined variables
%reset -f

# Set directory for data loading to work properly
import os
os.chdir(os.path.expanduser('~/notebooks/09'))

# HIDDEN
import warnings
# Ignore numpy dtype warnings. These warnings are caused by an interaction
# between numpy and Cython and can be safely ignored.
# Reference: https://stackoverflow.com/a/40846742
warnings.filterwarnings("ignore", message="numpy.dtype size changed")
warnings.filterwarnings("ignore", message="numpy.ufunc size changed")

import numpy as np
import matplotlib.pyplot as plt
import pandas as pd
import seaborn as sns
%matplotlib inline
import ipywidgets as widgets
from ipywidgets import interact, interactive, fixed, interact_manual
import nbinteract as nbi

sns.set()
sns.set_context('talk')
np.set_printoptions(threshold=20, precision=2, suppress=True)
pd.options.display.max_rows = 7
pd.options.display.max_columns = 8
pd.set_option('precision', 2)
# This option stops scientific notation for pandas
# pd.set_option('display.float_format', '{:.2f}'.format)

# HIDDEN
# Make names table
sql_expr = """
CREATE TABLE names(
    cat_id INTEGER PRIMARY KEY,
    name TEXT);
"""
result = sqlite_engine.execute(sql_expr)

# Populate names table
sql_expr = """
INSERT INTO names VALUES 
(0, "Apricot"),
(1, "Boots"),
(2, "Cally"),
(4, "Eugene");
"""

result = sqlite_engine.execute(sql_expr)

# HIDDEN
# Make colors table
sql_expr = """
CREATE TABLE colors(
    cat_id INTEGER PRIMARY KEY,
    color TEXT);
"""
result = sqlite_engine.execute(sql_expr)

# Populate colors table
sql_expr = """
INSERT INTO colors VALUES 
(0, "orange"),
(1, "black"),
(2, "calico"),
(3, "white");
"""

result = sqlite_engine.execute(sql_expr)

# HIDDEN
# Make ages table
sql_expr = """
CREATE TABLE ages(
    cat_id INTEGER PRIMARY KEY,
    age INT);
"""
result = sqlite_engine.execute(sql_expr)

# Populate ages table
sql_expr = """
INSERT INTO ages VALUES 
(0, 4),
(1, 3),
(2, 9),
(4, 20);
"""

result = sqlite_engine.execute(sql_expr)

pd.merge(table1, table2, on='common_column')

SELECT ...
FROM <TABLE_1>
    INNER JOIN <TABLE_2>
    ON <...>

SELECT *
FROM names AS N
    INNER JOIN colors AS C
    ON N.cat_id = C.cat_id;

pd.merge(names, colors, how='inner', on='cat_id')

SELECT ...
FROM <TABLE_1>
    FULL JOIN <TABLE_2>
    ON <...>

SELECT name, color
FROM names N
    FULL JOIN colors C
    ON N.cat_id = C.cat_id;

pd.merge(names, colors, how='outer', on='cat_id')

SELECT ...
FROM <TABLE_1>
    LEFT JOIN <TABLE_2>
    ON <...>

SELECT name, color
FROM names N
    LEFT JOIN colors C
    ON N.cat_id = C.cat_id;

pd.merge(names, colors, how='left', on='cat_id')

SELECT ...
FROM <TABLE_1>
    RIGHT JOIN <TABLE_2>
    ON <...>

SELECT name, color
FROM names N
    RIGHT JOIN colors C
    ON N.cat_id = C.cat_id;

pd.merge(names, colors, how='right', on='cat_id')

SELECT *
FROM names AS N
    INNER JOIN colors AS C
    ON N.cat_id = C.cat_id;

SELECT *
FROM names AS N, colors AS C
WHERE N.cat_id = C.cat_id;

sql_expr = """
SELECT *
FROM names N, colors C
"""
pd.read_sql(sql_expr, sqlite_engine)

SELECT *
FROM names AS N, colors AS C
WHERE N.cat_id = C.cat_id;

# Joining three tables

sql_expr = """
SELECT name, color, age
    FROM names n
    INNER JOIN colors c ON n.cat_id = c.cat_id
    INNER JOIN ages a ON n.cat_id = a.cat_id;
"""
pd.read_sql(sql_expr, sqlite_engine)