#airport-codes.alaska/us

from numpy import genfromtxt
from time import time
from datetime import datetime
from sqlalchemy import Column, Integer, Float, Date, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

def Load_Data(file_name):
    f = lambda s: str(s)
    data = genfromtxt(file_name, delimiter=',', skiprows=1, converters={0: f, 1:f,
    2:f, 6:f, 7:f, 8:f, 9:f, 10:f})
    return data.tolist()

Base = declarative_base()

class AirportCode(Base):

__tablename__ = 'AirportCode'
__table_args__ = {'sqlite_autoincrement': True}
#tell SQLAlchemy the name of column and its attributes:
id = Column(Integer, primary_key=True, nullable=False)
ident = Column(String)
type = Column(String)
name = Column(String)
latitude_deg = Column(String)
longitude_deg = Column(String)
elevation_ft = Column(String)
continent = Column(String)
iso_country = Column(String)
iso_region = Column(String)
municipality = Column(String)
gps_code = Column(String)


def __repr__(self):
        #return "<AirportCode(name='%s', municipality='%s')>n" % (self.name, self.municipality)
        return "name:{} municipality:{}n".format(self.name, self.municipality)

if __name__ == "__main__":
t = time()

#Create the database
engine = create_engine('sqlite:///airport-codes.db')
Base.metadata.create_all(engine)

#Create the session
session = sessionmaker()
session.configure(bind=engine)
s = session()

try:
    records_to_commit = 0
    file_name = "airport-codes.us"       #23,000 records fails at next line
    #file_name = "airport-codes.alaska      250 records works fine"
    print file_name #for debugging
    data = Load_Data(file_name)  # fails here on large files and triggers the except: below
    print 'file loaded' #for debugging

    for i in data:
        records_to_commit += 1
        print i

        record = AirportCode(**{

            'ident' : i[0].lower(),
            'type' : i[1].lower(),
            'name' : i[2].lower(),
            'latitude_deg' : i[3],
            'longitude_deg' : i[4],
            'elevation_ft' : i[5],
            'continent' : i[6],
            'iso_country' : i[7],
            'iso_region' : i[8],
            'municipality' : i[9].lower(),
            'gps_code' : i[10].lower()


        })

        s.add(record) #Add all the records
        #print 'hi'
        if records_to_commit == 1000:
            s.flush() #Attempt to commit batch of 1000 records
            records_to_commit = 0
    s.commit() # flushes everything remaining + commits
    print 'commited'
except:

    print 'except'

    #s.rollback() #Rollback the changes on error
finally:
    s.close() #Close the connection
print "Time elapsed: " + str(time() - t) + " s."

data = Load_Data(file_name)