!pip install ddlgenerator

!pip install ipython_sql

!ddlgenerator --inserts sqlite http://en.wikipedia.org/wiki/List_of_airports_in_the_United_States > airports.sql

!rm airports.sqlite

!sqlite3 airports.sqlite ".read airports.sql"

!sqlite3 airports.sqlite "alter table list_of_airports_in_the_united_states rename to airports"

%load_ext sql

%sql sqlite:///airports.sqlite

%sql select * from airports limit 10

base_url = 'http://image.weather.com/web/radar/us_%s_closeradar_large_usen.jpg'

faa = %sql select faa, city from airports where faa is not null

faa[:10]

iata = %sql select iata, city from airports where iata is not null

results = set(tuple(f) for f in faa) | set(tuple(i) for i in iata)


len(results)

urls = [(base_url % r[0].lower(), r[1]) for r in results]

urls[:10]

import requests

response = requests.get(base_url % 'atl')

response

response.status_code

import time

valid_urls = []

for (url, city) in urls:
    response = requests.get(url)
    if response.status_code == 200:
        valid_urls.append((url, city))
    print(url, city, response.status_code)
    time.sleep(2)

len(valid_urls)

with open('valid_600mi_urls.txt', 'w') as outfile:
    for (city, url) in valid_urls:
        outfile.write("%s: %s\n" % (city, url))

!cat valid_600mi_urls.txt

%sql select * from airports