import randdata as rd
conobj = DB_UTIL.db_connect() #Connect to the DB
#So now that we're connected, lets start adding things.
#for i in range(0, len(customers)):
# DB_UTIL.create_customer(conobj, customers[i])
for i in range(0, len(rd.airports)):
DB_UTIL.create_airports(rd.airports[i][0], rd.airports[i][1], rd.airports[i][2])
for i in range(0, len(rd.airlines)):
DB_UTIL.create_airlines(rd.airlines[i])
for i in range(0, len(rd.flights)):
DB_UTIL.create_flights(rd.flights[i][0], rd.flights[i][1], rd.flights[i][2], rd.flights[i][3], rd.flights[i][4], rd.flights[i][5], rd.flights[i][6], rd.flights[i][7])
#Now, lets see if we've successfully added all these to the DB.
print(DB_UTIL.queryexec('SELECT * FROM Flights')) #This is hardcoded SQL, not the best way to do this, but it'll give us a glimpse at whether this works or not. Do avoid sing this though.
"""
If console returns something like:
$ python ex1.py
2.6.0
[(1, 'Paul', 'Pluszczewicz'), (2, 'Martin', 'Sendrowicz'), (3, 'Mohammed', 'Rahat'), (4, 'Sateesh', 'Mane'), (5, 'Joe', 'Schmo'), (6, 'Duncan', 'Donut'), (7, 'Bill', 'Gates')]
This means all is working well. Excellent!
"""
#conobj.commit()
conobj.close()
import DB_UTIL
drops = [
'DROP TABLE IF EXISTS Customer;', 'DROP TABLE IF EXISTS Airports;',
'DROP TABLE IF EXISTS Airlines;', 'DROP TABLE IF EXISTS Flights;',
'DROP TABLE IF EXISTS Cust_Flight;'
]
creates = [
#'CREATE TABLE Customer(CustID INTEGER PRIMARY KEY AUTOINCREMENT, UserName NVARCHAR(50) NOT NULL);',
'CREATE TABLE Airports(AirportID INTEGER PRIMARY KEY AUTOINCREMENT, AirportName NVARCHAR(50) NOT NULL, CityName NVARCHAR(50) NOT NULL, Country NVARCHAR(50) NOT NULL);',
'CREATE TABLE Airlines(AirlineID INTEGER PRIMARY KEY AUTOINCREMENT, AirlineName NVARCHAR(50) NOT NULL);',
#'CREATE TABLE Flights(FlightID INTEGER PRIMARY KEY AUTOINCREMENT, AirlineID INT NOT NULL, Origin INT NOT NULL, Destination INT NOT NULL, CurrentPassengers INT DEFAULT 0, Capacity INT NOT NULL, FlightStatus NUMERIC DEFAULT 1, Fare INT NOT NULL, FlightDate NUMERIC NOT NULL, DepartureTime NUMERIC NOT NULL, FlightTime NUMERIC NOT NULL, FOREIGN KEY(Origin) REFERENCES Airports(AirportID), FOREIGN KEY(Destination) REFERENCES Airports(AirportID), FOREIGN KEY(AirlineID) REFERENCES Airlines(AirlineID));',
'CREATE TABLE Flights(Flight_ID INTEGER PRIMARY KEY AUTOINCREMENT, Airline_Name NVARCHAR(50), Date NUMERIC, Time NUMERIC, From_Airport NVARCHAR(50), To_Airport NVARCHAR(50), Capacity INT, Fare INT, Status NVARCHAR(50));',
#'CREATE TABLE Cust_Flight(CustID INT NOT NULL, FlightID INT NOT NULL, GuiSearch NUMERIC NOT NULL, FOREIGN KEY (CustID) REFERENCES Customer(CustID) ON DELETE CASCADE, FOREIGN KEY (FlightID) REFERENCES Flights(FlightID) ON DELETE CASCADE);'
'CREATE TABLE Cust_Flight(UserName NVARCHAR(50) NOT NULL, Flight_ID INT NOT NULL, Method NVARCHAR(50), FOREIGN KEY (Flight_ID) REFERENCES Flights(Flight_ID) ON DELETE CASCADE, PRIMARY KEY(UserName, Flight_ID));'
]
#myconnectionobject = DB_UTIL.db_connect()
for i in range(0, len(drops)):
DB_UTIL.cd_table(drops[i])
for i in range(0, len(creates)):
DB_UTIL.cd_table(creates[i])
get_tables = "SELECT name FROM sqlite_master WHERE type='table'"
print(DB_UTIL.queryexec(get_tables))
