SQL.cpp

''' Introduction to Algorithms (122COM) Assignment
    Task 1 - SQL
    Genaro Bedenko - Student ID 7060234'''

import sqlite3 as sql # Import SQLite to be able to work with SQL statements

def insertData(data, databaseName):
    '''Takes a sequence of tuples and database name as input
       Performs a multiple insert of data into the database table'''

    connection = sql.connect(databaseName) # Opens a connection to the database

    cursor = connection.cursor() # Creates a cursor on the connection to be able to execute SQL commands

    cursor.executemany('''INSERT INTO staff (forename, surname, job)\
				    	            VALUES (?,?,?);''', data) # Multiple insert statement to add the data in one go
    
    connection.commit() # Saves the insert of the data
   
def viewDatabase(databaseName):
    '''Takes a database name as input
       Prints out all rows of the table in a database'''
                       
    connection = sql.connect(databaseName) # Opens a connection to the database. ## needs to be a sqlite file?

    cursor = connection.cursor() # Creates a cursor on the connection to be able to execute SQL commands
                       
    cursor.execute('''SELECT * FROM staff;''')                      
                 
    for row in cursor:
                       
        print(row)           
    
def readInData(fileName):
    '''Takes a file name as input
       Reads in from the file line by line and takes 3 elements from each line 
       Outputs them into a list of tuples ready to be put into a database'''
    
    file = open(fileName, "r") # Opens the file in read mode
    
    listOfData = []    # Creates an empty list for each line to be entered into
    
    file.readline() # Ignores the first line of the file - the headings
                       
    for line in file:  # For each line in the file,            

        data = tuple(line.split(",")) # Seperate the strings where there is a comma, add all strings to a tuple       
        data = data[0:3]              # Only want the first 3 elemnts for each line in the file
        listOfData.append(data)       # Add the tuple to the list        
    
    file.close() # Close the file
    
    return(listOfData)                   
    
filename = "newcrew.csv"
databaseName = "firefly.sqlite"    

data = readInData(filename)
insertData(data, databaseName)
viewDatabase(databaseName)