#!/usr/bin/env python
# coding: utf-8

# # Create A View
# 
# We can store a query in the database as a view.
# 
# Let's imagine that we often need the record set that gives maximum and minimum pressure by country.
# 
# Here's the SQL from the previous chapter that generated it:
# 
# ```sql
# SELECT
#     f.country_code,
#     MAX(r.temp) AS max_temp,
#     MIN(r.temp) AS min_temp
# FROM readings AS r JOIN flights AS f
#     ON r.flight = f.name
# GROUP BY f.country_code
# ```
# 
# Let's use this to create view. First we connect to our database:

# In[5]:


import sqlalchemy as sa
import pandas as pd
engine = sa.create_engine('sqlite:///flight.db')
connection = engine.connect()
connection.execute("PRAGMA foreign_keys=on")


# And then, with one extra clause, we create our view:

# In[6]:


sql = """
    CREATE VIEW max_min_temp_by_country AS
    SELECT
        f.country_code,
        MAX(r.temp) AS max_temp,
        MIN(r.temp) AS min_temp
    FROM readings AS r JOIN flights AS f
        ON r.flight = f.name
    GROUP BY f.country_code
"""
connection.execute(sql)


# We can include the view in a SELECT statement in exactly the same way as a table:

# In[10]:


sql = """
    SELECT *
    FROM max_min_temp_by_country
    WHERE country_code = 'GB'
"""
pd.read_sql(sql, connection)