SQLite and Pandas quick intro

import pandas as pd
import sqlite3
import cartopy.crs as ccrs
import matplotlib.pyplot as plt
pd.set_option("max_colwidth", 10)
pd.set_option("display.max_rows", 5)


con = sqlite3.connect("flights.db")
airdf = pd.read_sql_query("select * from airports;", con)
airdf

candf = pd.read_sql_query("select * from airports where country = 'Canada';", con);
candf

airdf.loc[airdf.country == "Canada"]

routes = pd.read_sql_query("""
select sa.code as sacode, da.code as dacode, cast(sa.longitude as float) as source_lon,
    cast(sa.latitude as float) as source_lat,
    cast(da.longitude as float) as dest_lon,
    cast(da.latitude as float) as dest_lat
from routes
    inner join airports sa on sa.id = routes.source_id
    inner join airports da on da.id = routes.dest_id;
""",con)
routes

ax = plt.axes(projection=ccrs.PlateCarree())
ax.coastlines()

for name, row in routes[:3000].iterrows():
    distcolor = "blue" if abs(row["source_lon"] - row["dest_lon"]) < 30  else "red"
    plt.plot([row.source_lon, row.dest_lon], [row.source_lat, row.dest_lat],
             color=distcolor, linewidth=1,
             transform=ccrs.Geodetic(),)
plt.show()