!hive create_features.sql

import warnings
warnings.filterwarnings('ignore')

!conda install -c conda-forge ipython-sql -y

%load_ext sql

%config SqlMagic.autopandas=True

import pandas as pd
import sqlite3

"""
Explanation: SQL
Accessing data stored in databases is a routine exercise. I demonstrate a few helpful methods in the Jupyter Notebook.
End of explanation
"""


%%sql sqlite://
CREATE TABLE presidents (first_name, last_name, year_of_birth);
INSERT INTO presidents VALUES ('George', 'Washington', 1732);
INSERT INTO presidents VALUES ('John', 'Adams', 1735);
INSERT INTO presidents VALUES ('Thomas', 'Jefferson', 1743);
INSERT INTO presidents VALUES ('James', 'Madison', 1751);
INSERT INTO presidents VALUES ('James', 'Monroe', 1758);
INSERT INTO presidents VALUES ('Zachary', 'Taylor', 1784);
INSERT INTO presidents VALUES ('Abraham', 'Lincoln', 1809);
INSERT INTO presidents VALUES ('Theodore', 'Roosevelt', 1858);
INSERT INTO presidents VALUES ('Richard', 'Nixon', 1913);
INSERT INTO presidents VALUES ('Barack', 'Obama', 1961);

later_presidents = %sql SELECT * FROM presidents WHERE year_of_birth > 1825
later_presidents

type(later_presidents)

con = sqlite3.connect("presidents.sqlite")
later_presidents.to_sql("presidents", con, if_exists='replace')

"""
Explanation: SQL
CREATE TABLE presidents (first_name, last_name, year_of_birth);
INSERT INTO presidents VALUES ('George', 'Washington', 1732);
INSERT INTO presidents VALUES ('John', 'Adams', 1735);
INSERT INTO presidents VALUES ('Thomas', 'Jefferson', 1743);
INSERT INTO presidents VALUES ('James', 'Madison', 1751);
INSERT INTO presidents VALUES ('James', 'Monroe', 1758);
INSERT INTO presidents VALUES ('Zachary', 'Taylor', 1784);
INSERT INTO presidents VALUES ('Abraham', 'Lincoln', 1809);
INSERT INTO presidents VALUES ('Theodore', 'Roosevelt', 1858);
INSERT INTO presidents VALUES ('Richard', 'Nixon', 1913);
INSERT INTO presidents VALUES ('Barack', 'Obama', 1961);
End of explanation
"""


%%sql
SELECT first_name,
       last_name,
       year_of_birth
FROM presidents
WHERE year_of_birth > 1800;

"""
Explanation: Through pandas directly
End of explanation
"""


con = sqlite3.connect("presidents.sqlite")
cur = con.cursor()

new_dataframe = pd.read_sql("""SELECT first_name, last_name, year_of_birth
                               FROM presidents
                               WHERE year_of_birth > 1800
                               """, 
                            con=con)

con.close()

new_dataframe

type(new_dataframe)

"""
Explanation: SQL
SELECT first_name,
       last_name,
       year_of_birth
FROM presidents
WHERE year_of_birth &gt; 1800;
End of explanation
"""


