# CSC120 Lab 9 Database functions from typing import TextIO, List import sqlite3, csv # This helper function helps you run a SQL query. # Use it inside of your query functions. def run_query(db: str, db_query: str, args: tuple = None) -> List[tuple]: """Return the results of running query db_query on database with name db. Optional query argumnts for can be included in args. """ con = sqlite3.connect(db) cur = con.cursor() if args is None: cur.execute(db_query) else: cur.execute(db_query, args) data = cur.fetchall() cur.close() con.close() return data ## Handout part 2 # Query 1: Get all of the flights def get_all_flights(db: str) -> List[tuple]: """Return all flight records with all columns from the flights table in database db. """ query = 'put your query here' return run_query(db, query) # Query 2: Get airline code and flight number def get_airline_info(db: str) -> List[tuple]: """Return the airline code and flight number columns of all rows from the flights table in database db. """ query = 'put your query here' return run_query(db, query) # Query 3: Get origin and destination def get_origin_destination(db: str) -> List[tuple]: """Return the origin and destination columns of all rows from the flights table in database db. """ query = 'put your query here' return run_query(db, query) # Query 4: Get destination and origin (columns swapped from above query) def get_destination_origin(db: str) -> List[tuple]: """Return the destination and origin columns of all rows from the flights table in database db. """ query = 'put your query here' return run_query(db, query) # Query 5: Get destination and origin (columns swapped from above query) def get_minutes(db: str) -> List[tuple]: """Return the minutes column of all rows from the flights table in database db. """ query = 'put your query here' return run_query(db, query) ## Continue reading handout # Query 6: Minutes less than 230 def get_min_less_230(db: str) -> List[tuple]: """Return all columns of rows with minutes less than 230 from the flights table in database db. """ query = 'put your query here' return run_query(db, query, args) # Query 7: Minutes greater than 185 def get_min_greater_185(db: str) -> List[tuple]: """Return all columns of rows with minutes greater than 185 from the flights table in database db. """ query = 'put your query here' return run_query(db, query, args) # Query 8: Airline is 'DL' def get_airline_dl(db: str) -> List[tuple]: """Return all columns of rows where the airline is 'DL' from the flights table in database db. """ query = 'put your query here' return run_query(db, query, args) # Query 9: Columns and where clause def get_number_greater_210(db: str) -> List[tuple]: """Return only the flight number column of the rows with minutes greater than *or equal to* 210 from the flights table in database db. """ query = 'put your query here' return run_query(db, query, args) # Query 10: Columns and where clause def get_origin_minutes_range(db: str) -> List[tuple]: """Return the origin column of the rows with minutes greater than 200 but less than 240 from the flights table in database db. """ query = 'put your query here' return run_query(db, query, args) ## Section 3 # This function was used to create the flights table in the database. # You do NOT have to call this function. # You can use it as a model to write the next function. def create_flights_table(db: str, flight_data_file: TextIO) -> None: '''Create and populate the flights table in database with name db with the data from the open file data_file. ''' # Connect to the database con = sqlite3.connect(db) # Create a cursor cur = con.cursor() # Create the Flights table cur.execute('''CREATE TABLE flights ( airline TEXT, flight_number INTEGER, origin TEXT, destination TEXT, minutes NUMBER )''') reader = csv.reader(flight_data_file) next(reader) # read off the header before inserting recrod # Populate the flights Table # Loop through each line in the csv file: for line in reader: # Get the data from the row list airline = line[0] flight_number = line[1] origin = line[2] destination = line[3] minutes = line[4] # Now, we execute a 'INSERT' SQL query cur.execute('''INSERT INTO flights VALUES(?, ?, ?, ?, ?)''', (airline, flight_number, origin, destination, minutes)) #commit the changes con.commit() #close the cursor. cur.close() # close the connection con.close() # Write this function (use the one above as an example) def create_airlines_table(db: str, airline_data_file: TextIO) -> None: '''Create and populate the airline table in database with name db with the data from the open file data_file. ''' # Add your code here ## Section 4 (do this after you've put the airline table into the database) # Query 11: def airline_names(db: str) -> List[tuple]: """Return the full airline names of all flights in the flights table in database db. Airline names are stored in the airlines table. """ query = 'put your query here' return run_query(db, query) # Query 12: def airline_names_origin_dest(db: str) -> List[tuple]: """Return the full airline names and origin and destination of all flights in the flights table in database db. Airline names are stored in the airlines table. Remember that you have to specifiy which table each column is coming from. """ query = 'put your query here' return run_query(db, query) # Query 13: def airline_names_minutes(db: str) -> List[tuple]: """Return the full airline names and minutes of the flights that took exactly 210 minutes in the flights table in database db. Airline names are stored in the airlines table. """ query = 'put your query here' return run_query(db, query, args)