# -*- coding: utf-8 -*-
#*********************************************************************
# Name:        LoadFlightDelay.py
# Created:     5/12/2016
# Modified:    5/12/2016
# Created By:  Peter McCutcheon
#
#---------------------------------------------------------------------
# Description:
#
#   This Python script reads a CSV file with flight delay information.
#   and using sQL loads a table with the data.
#
#**********************************************************************


import csv
import sqlite3

#
#   Connect to the FlightData database and create a cursor for SQL operations.
#
conn = sqlite3.connect('flightdata.db')
curs = conn.cursor()          

#
#   Open the CSV data file from DATA.GOV, read the data into a list,
#   Create an SQL insert and insert the read date into the AirportSeqID
#   table.
#
i = 0
newTime = ''
with open('c:/userfiles/peter/webdev/pythonfiles/FlightDelays2.csv','rt') as fin:
    cin = csv.reader(fin)
    insStr = 'INSERT INTO FlightDelay (SeqNumber, \
	FlightDate, \
	UniqueCarrier, \
	AirLineID, \
	Carrier, \
	TailNum, \
	FlightNumber, \
	OriginAirport, \
	OriginAirportSeq, \
	OriginCityMarketID, \
	Origin, \
	OriginCityNm, \
	OriginStateAbr, \
	OriginStateNm, \
	DestAirport, \
	DestAirportSeq, \
	DestCityMarketID, \
	Dest, \
	DestCityNM, \
	DestStateAbr, \
	DestStateNM, \
	DepartTime, \
	DepartDelay, \
	TaxiOutTime, \
	WheelsOff, \
	WheelsOn, \
	TaxiInTime, \
	AirTime) values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);'
                                        
    for row in cin:
        row.pop()
        row.insert(0,i)
        print(row)
        tmpTime = row[21]
        x = len(row[21])
        if x == 0:
            newTime = '00:00'
        elif x == 2:
            newTime = '00:' + tmpTime
        elif x == 3:
            newTime = '0' + tmpTime[0] + ':' + tmpTime[1:]
        elif x == 4:
            newTime = tmpTime[:2] + ':' + tmpTime[2:]
        else:
            print("Incoming time data error")
        row[21] = newTime
        try:
            curs.execute(insStr, (row))
            conn.commit()
        except Exception as er:
            print("Sqlite Error " + er.message)        
        i = i + 1
            
    curs.close()
    conn.close()