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:
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")
Out[5]:
<sqlalchemy.engine.result.ResultProxy at 0x10d0926a0>
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)
Out[6]:
<sqlalchemy.engine.result.ResultProxy at 0x10d08da58>
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)
Out[10]:
| country_code | max_temp | min_temp | |
|---|---|---|---|
| 0 | GB | 26.9 | 23 |