# ---
# jupyter:
#   jupytext:
#     text_representation:
#       extension: .py
#       format_name: light
#       format_version: '1.5'
#       jupytext_version: 1.14.4
#   kernelspec:
#     display_name: Python 3
#     language: python
#     name: python3
# ---

# # Step 2 - Database Engineering

# +
# Use SQLAlchemy to model your table schemas and create a sqlite database for your tables. 
# You will need one table for measurements and one for stations.

# Create a Jupyter Notebook called database_engineering.ipynb and use this to complete 
# all of your Database Engineering work.

# Use Pandas to read your cleaned measurements and stations CSV data.
# Use the engine and connection string to create a database called hawaii.sqlite.

# Use declarative_base and create ORM classes for each table.

# You will need a class for Measurement and for Station.
# Make sure to define your primary keys.

# Once you have your ORM classes defined, create the tables in the database using create_all.
# -

# ### Load cleaned csv's into pandas DataFrames 

# Import dependencies 
import pandas as pd

csv_path = "clean_hawaii_stations.csv"
stations_df = pd.read_csv(csv_path)
stations_df = stations_df.rename(columns={'Unnamed: 0':'id'})
stations_df.head()

csv_path = "clean_hawaii_measurements.csv"
weather_df = pd.read_csv(csv_path)
weather_df = weather_df.rename(columns={'Unnamed: 0':'id'})
weather_df.head()

# # Database Creation

# SQLAlchemy
import sqlalchemy
from sqlalchemy import create_engine, MetaData
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Numeric, Text, Float
from sqlalchemy.orm import Session

# Create an engine to a SQLite database file 
engine = create_engine("sqlite:///hawaii.sqlite")

# Create a connection to the engine
conn = engine.connect()

# +
# Use `declarative_base` from SQLAlchemy to model the demographics table as an ORM class

Base = declarative_base()

class Measurement (Base):
    __tablename__ = 'measurements'
    id = Column(Integer, primary_key=True)
    station = Column(String)
    date = Column(String)
    prcp = Column(Float)
    tobs = Column(Integer)

# Define a Station class
class Station (Base):
    __tablename__ = 'stations'
    id = Column(Integer, primary_key=True)
    station = Column(String)
    name = Column(String)
    latitude = Column(Float)
    longitude = Column(Float)
    elevation = Column(Float)



# -

# Use `create_all` to create table in database
Base.metadata.create_all(engine)

# Create a list of data to write
data1 = stations_df.to_dict(orient='records')
data2 = weather_df.to_dict(orient='records')

# Check list of dictionaries 
print(data1[:5])
print("------------")
print(data2[:5])

# Use MetaData from SQLAlchemy to reflect the tables
metadata = MetaData(bind=engine)
metadata.reflect()

# Save the reference to the `measurements` table as a variable called `table`
table1 = sqlalchemy.Table('stations', metadata, autoload=True)
table2 = sqlalchemy.Table('measurements', metadata, autoload=True)

# Use `table.insert()` to insert data into tables
# The SQL table is populated during this step
conn.execute(table1.insert(), data1)
conn.execute(table2.insert(), data2)

# Check data 
conn.execute("select * from stations limit 5").fetchall()

# Check data 
conn.execute("select * from measurements limit 5").fetchall()

# ### Database engineering is complete 

StarcoderdataJupyterScriptsDedupFiltered