Project3-Group7.draft1.py

from sqlalchemy import create_engine
from sqlalchemy import Table, Column, Integer, String, MetaData, ForeignKey
from sqlalchemy import DateTime, Boolean
from sqlalchemy import exists
from sqlalchemy import sql, select, join, desc
import csv
import pandas as pd
import datetime


# Create a sqlite database 
engine = create_engine('sqlite:///project3group7.sqlite')

metadata = MetaData(engine)


# Create the Airports Table
try:
    Airports = Table('Airports', metadata, autoload=True)
except:
    Airports = Table ('Airports', metadata,
                Column('ID', Integer, autoincrement=True),
                Column('Code', String, primary_key=True),
                Column('City', String),
                Column('State', String)
               )


# Create the Flights table
try:
    Flights = Table('Flights', metadata, autoload=True)
except:
    Flights = Table ('Flights', metadata,
                 Column('ID', Integer, autoincrement=True),
		 Column('Time', String, primary_key=True),
                 Column('Fl_date', DateTime),
                 Column('Airline_ID', String),				 
                 Column('Origin', String, ForeignKey("Airports.Code")),
                 Column('Destination', String),
                 Column('Dep_Time', String),
                 Column('Dep_Delay_New', Integer),
		 Column('Dep_Del15', Integer),
                 Column('Arr_Time', String),
                 Column('Arr_Delay_New', Integer),
		 Column('Arr_Del15', Integer),
                 Column('Cancelled', Boolean),
                 Column('Cancellation_Code', String),
                 Column('Diverted', Boolean),
                 Column('Air_Time', Integer),
                 Column('Flights', Integer),
                 Column('Distance', Integer),
                 Column('Carrier_Delay', Integer),
                 Column('Weather_Delay', Integer),
                 Column('NAS_Delay', Integer),
                 Column('Security_Delay', Integer),
                 Column('Late_Aircraft_Delay', Integer)
                )
                                  
metadata.create_all(engine)


# Read the csv file
flights = open("flights.1K.csv")
reader = csv.DictReader(flights)

Airport_dict = {}


# Create a dictionary of unique airport codes
for Line in reader:
    if Line['ORIGIN'] not in Airport_dict:
        Airport_dict[Line['ORIGIN']]=[Line['ORIGIN_CITY_NAME'], Line['ORIGIN_STATE_ABR']]

    if Line['DEST'] not in Airport_dict:
        Airport_dict[Line['DEST']]=[Line['DEST_CITY_NAME'], Line['DEST_STATE_ABR']]
		
conn = engine.connect()


# Add the Airport_dict codes to the Airports table
def insert_airport(code,city,state):
    ins = Airports.insert().values(Code=code,
                                  City=city,
                                  State=state)
    
    result = conn.execute(ins)

for key, value in Airport_dict.items(): 
    insert_airport(key, value[0], value[1])


# Close the file	
flights.close()

flights = open("flights.1K.csv")

reader = csv.DictReader(flights)

Flight_dict = {}

def to_date(dates, lookup=False, **args):
	if lookup:
		return dates.map({v: pd.to_datetime(v, **args) for v in dates.unique()})
	return pd.to_datetime(dates, **args)

# Create a dictionary of Time using the departure time and arrival time
# Add data to the database
for Line in reader:
	Time = (Line['DEP_TIME'] + Line['ARR_TIME'])
	if (Line['DEP_TIME'] + Line['ARR_TIME']) not in Flight_dict:
		Flight_dict[Time]=[to_date(Line['FL_DATE']), Line['AIRLINE_ID'], Line['ORIGIN'], Line['DEST'],
		Line['DEP_TIME'], Line['DEP_DELAY_NEW'], Line['DEP_DEL15'], 
		Line['ARR_TIME'], Line['ARR_DELAY_NEW'], Line['ARR_DEL15'],
		int(float((Line['CANCELLED']))), Line['CANCELLATION_CODE'], int(float((Line['DIVERTED']))), Line['AIR_TIME'], Line['FLIGHTS'],
		Line['DISTANCE'], Line['CARRIER_DELAY'], Line['WEATHER_DELAY'], Line['NAS_DELAY'], Line['SECURITY_DELAY'], Line['LATE_AIRCRAFT_DELAY']]
	
conn = engine.connect()


def insert_flight(time, fl_date, airline_id, origin, destination, dep_time, dep_delay_new,
                   dep_del15, arr_time, arr_delay_new, arr_del15, cancelled, cancellation_code,
		   diverted, air_time, flights, distance, carrier_delay, weather_delay,
			    nas_delay, security_delay, late_aircraft_delay):
					
    ins = Flights.insert().values(Time = time,
				    Fl_date = fl_date,
				    Airline_ID = airline_id,
				    Origin = origin,
				    Destination = destination,
				    Dep_Time = dep_time,
				    Dep_Delay_New = dep_delay_new,
				    Dep_Del15 = dep_del15,
				    Arr_Time = arr_time,
				    Arr_Delay_New = arr_delay_new,
				    Arr_Del15 = arr_del15,
				    Cancelled = cancelled,
				    Cancellation_Code = cancellation_code,
				    Diverted = diverted,
				    Air_Time = air_time,
				    Flights = flights,
				    Distance = distance,
				    Carrier_Delay = carrier_delay,
				    Weather_Delay = weather_delay,
				    NAS_Delay = nas_delay,
				    Security_Delay = security_delay,
				    Late_Aircraft_Delay = late_aircraft_delay)
    
    result = conn.execute(ins)

for key, value in Flight_dict.items(): 
    insert_flight(key, value[0], value[1], value[2], value[3], value[4], value[5], value[6],
					value[7], value[8], value[9], value[10], value[11], value[12], value[13],
					value[14], value[15], value[16], value[17], value[18], value[19], value[20])
	



# Query 1 for select
# Select 25 flights with limit function and show their time for departure and arrival
Query1 = select([Flights.c.Dep_Time, Flights.c.Arr_Time]).limit(25)
             
result1 = conn.execute(Query1)
for row in result1:
    print(row)


# Query 2 for where
# Show the Airline ID, Origin Airport ID and Destination Airport ID for short-distance(within 200 miles) flights
Query2 = select([Flights.c.Airline_ID, Flights.c.Origin, Flights.c.Destination]).where(Flights.c.Distance < 200)

result2 = conn.execute(Query2)
for row in result2:
    print(row)

	
# Query 3 for join
# Join the same Airport Code from both the Destination in Flights table and Code in Airports table, and show the airline ID, oriin airport code, destination airport code, city name and state name
join_obj = Flights.join(Airports,
                         Flights.c.Destination==Airports.c.Code)

Query3 = select(
   [Flights.c.Airline_ID, Flights.c.Origin, Flights.c.Destination, Airports.c.City, Airports.c.State]).select_from(join_obj)

result3 = conn.execute(Query3)

for row in result3:
    print(row)