Flatiron Phase 3: SQL

SQL is a critical tool for organizing and managing databases. SQL provides an easy way to parse, modify, and link data between different tables. Most of the functions provided by SQL could be performed in Python or R alone, but these languages are not designed with that specific purpose in mind. SQL is a powerful and lightweight tool, making it ideal for large and constantly changing datasets.

For our purposes, SQLite was used to maintain a database that did not require an external host. SQLite is a lightweight C-library that is generally used for internal data storage of applications. Most importantly, the SQLite module allowed us to manipulate our databases directly within the terminal.

For most applications, SQL is a great method of sharing data. We use relational databases when data from one table needs to be directly accessible to another table. The easiest way to visualize this connection was through the many real-world examples used throughout phase 3 of Flatiron. For example, companies are strategically divided into a system of hierarchy to ensure a system of checks and balances at every level. These hierarchies typically employ a range of departments, each with managers supervising lower employees. In Python, and other object-oriented programming languages, we are taught to create objects to represent these real-world dynamics. For our example, a department, and an employee. An employee object could have attributes including but not limited to an ID, name, job title, salary, and crucially a department. Most of these attributes would be unique to the employee, however in a SQL database, the department ID could directly reference a department instance to allow for a one-to-many relationship.

An organized database can be useful on its own, but the options for data manipulation past this point are endless. Python is a powerful tool for creating object-oriented applications as was mentioned above, but these objects can be modeled and stored directly within a database. This is called object-relational mapping. Within python, we were able to execute SQL commands abstracted into methods that can be used repeatedly with constantly changing data. This collaboration allowed us to create applications for interacting with our data, such as locating the employees under each manager (as they continually change) or creating new employees.

My final project for phase 3 was a typing game. This idea is not novel, but the implementation was unique and surprisingly coherent. My models included a User, Test, and Sentence. Tests stored tests taken by users (which always include a sentence) these are two examples of one-to-many relationships as mentioned above. A user would be prompted to re-type a given sentence, their words-per-minute (WPM) would be recorded and weighted against the length of the sentence, and the Test would be saved alongside the user’s statistics.

One aspect of object-relational mapping (ORM) that confused me was the organization of the methods in my code. I broke my command-line interface (CLI ) application into 3 main sections: CLI, Helpers, and Models. The CLI was meant to contain only the menu options for each page, this means it would display the options in the command prompt, and directly call a helper method from Helpers. The helper methods were much more dense, containing most of the math and secondary prompts (any user inputs aside from menus). Helpers were also responsible for calling object methods. These methods were majorly SQL commands wrapped in methods, though there were some cases where I implemented instance methods to properly compartmentalize my code. As I wrote more and more methods for my project, I found myself becoming disorganized with where my methods lived. Some code that should have been in helpers was being written in CLI and helpers were calling class methods. Nearing the end of my project, I found myself spending time re-organizing and often re-writing these methods to ensure they were in the proper file. Below is breakdown of a method that I had to rewrite in the helpers module:

# Countdown to test, and initialize test instance
def begin_test(username):
    cprint("\n""3...", "red")
    time.sleep(1)
    cprint("\n""2...", "yellow")
    time.sleep(1)
    cprint("\n""1...", "white")
    time.sleep(1)
    cprint("\n""GO""\n", "green")
    test(username)
    cprint("\n""Press ENTER to return to the menu...", "light_blue")
    input("> ") test_sentence.id)

The code above is the countdown to taking a test. This block could have lived in either module, cli or helpers. I decided to keep it next to the main test block to keep the code as readable as possible.

# Select a random test sentence, start a timer, prompt input, end timer 
# Calculate time, accuracy, and wpm; create a new test with data
def test(username):
    #intitialize test variables
    user = User.find_by_name(username)
    sentences = Sentence.get_all()
    test_sentence = sentences[random.randint(0, (len(sentences)-1))]
    test_string = test_sentence.string

    #run test
    cprint(f"{test_string}""\n", "yellow", attrs=["bold", "underline"])
    time.sleep(.2)
    start = time.time()
    user_input = input()
    end = time.time()
    test_time = round((end - start), 1)
    
    #calculate misses, accuracy, and wpm
    test_string_length = len(test_string)
    user_input_length = len(user_input)
    if test_string_length > user_input_length:
        diff = test_string_length - user_input_length
        user_input += "#"*diff
    elif user_input_length > test_string_length:
        diff = user_input_length - test_string_length
        test_string += "#"*diff
    misses = sum(1 for a, b in zip(test_string, user_input) if a != b)
    accuracy = round((((test_string_length-misses)/test_string_length)*100), 1)
    wpm = round((((test_string_length/5)/(test_time/60))*(accuracy/100)), 1)
    
    #display results
    cprint("\n""      Test Results:      ""\n", attrs=["underline"])
    if wpm > user.record_wpm():
        cprint(f"       WPM: {wpm} WPM (PB)", "light_yellow")
    else:
        cprint(f"       WPM: {wpm} WPM")
    print(f"      time: {test_time}s")
    print(f"  accuracy: {accuracy}%")

    #create Test instance with values
    Test.create(user_input, test_time, accuracy, wpm, user.id, test_sentence.id)

Above is the code for a test in my application. This block is responsible for selecting a random sentence from the database, prompting the user for an input (repeating the sentence and recording the total time), and calculating all the user’s statistics. This was originally written in the cli because of the number of prints to the terminal, however the calculations and calls to the Test object caused me to rethink this decision. Any method that referenced an object method was likely making use of SQL. In this example, the final line of code is creating a new Test instance with all of the data collected.

Coming from learning React in JavaScript, Python and SQL were a breath of fresh air. These two environments are very similar but there was a great deal of control and ease of use that came with Python that I enjoyed. Initially I had a hard time restructuring my thinking to work with these datasets, but over time the workflow began to make much more sense. I am excited to see what is to come for the final two phases of Flatiron school.