Senior Project Capstone

Let’s Take A Step Back

The last couple of posts were post-graduation work I completed. However, this project was completed during the first half of the semester for COMP 490 - Senior Design and Development. It was a solo project. It taught me a lot about software development, especially using an API in conjunction with Python code and an SQLite database.

Objective

The project goal was simple: give the university’s Collaborative University Business Experiences a working piece of software that allows professors to pick projects created by students to sponsor them.

The project was divided into 4 sprints, each with their own specific deliverables needed.

Scope

Since this is a Software Development project all good coding practices were followed. Hence slack was used for communication, jira was used for task tracking. Furthermore tests were needed on the deliverables for each sprint and the project was to be presented to ‘stakeholders’ (my awesome professor).

This makes for a real world level project at a capstone course for a Computer Science degreee!

Tools Used

• Python 3.8
• Wufoo Forms
• SQLite
• GitHub CI/CD (Actions)
• PyQt

Sprint 1

The goal of the first sprint was to setup a Wufoo Forms account to store entries for project ideas from students.

Then grab those entries from the Wufoo website via their API and return a valid JSON response.

Code to retrieve responses

This code uses the requests.auth import HTTPBasicAuth package to send an HTTP request with my API key wufoo_key and basic password pass since no authentication was enabled.

If the response code is not 200 indicating a succesful response then the error will be handled gracefully.

def get_wufoo_data() -> dict:
    response = requests.get(url, auth=HTTPBasicAuth(wufoo_key, 'pass'))
    if response.status_code != 200:
        print(f"Failed to get data, response code:{response.status_code} and error message: {response.reason} ")
        sys.exit(-1)
    jsonresponse = response.json()
    return jsonresponse

With some more code to save the responses in a text file made the Sprint complete.

EntryId: 11
Field1: Alex
Field2: De Castro
Field426: Dentist Extrodinaire
Field428: 
Field10: 3210987654
Field12: 32423423
Field430: 
Field123: Course Project
Field124: 
Field125: Site Visit
Field126: 
Field127: Career Panel
Field223: 
Field224: 
Field225: 
Field226: Summer 2023 (June 2023- August 2023)
Field227: 
Field423: Further discussion needed
DateCreated: 2023-02-07 16:38:22
CreatedBy: public
DateUpdated: 
UpdatedBy: None
_______________________________________________

Sprint 2

The objective of Sprint 2 was not to store the entries in a text file but rather in a SQLite database. SQLite was chosen since it is lightweight and can easily run in the PyCharm IDE.

First I created a Function to setup a database for my entries when needed.

 cursor.execute('''CREATE TABLE IF NOT EXISTS entries(
EntryId INTEGER UNIQUE NOT NULL, first_name TEXT NOT NULL, last_name TEXT NOT NULL,
job_title TEXT NOT NULL, org_name TEXT NOT NULL, phone_num INTEGER NOT NULL,
school_id INTEGER NOT NULL, org_site TEXT, course TEXT, speaker TEXT, siteVisit TEXT,
job_shadow TEXT,
carreer_panel TEXT, summer_2022 TEXT, fall_2022 TEXT, spring_2023 TEXT, summer_2023 TEXT, other TEXT,
permission TEXT, date_created TEXT,
created_by TEXT, date_update TEXT, updated_by TEXT);''')

More methods were created to access and close the database connection as well.

def open_db(filename: str) -> Tuple[sqlite3.Connection, sqlite3.Cursor]:
    db_connection = sqlite3.connect(filename)
    cursor = db_connection.cursor()
    return db_connection, cursor

def close_db(connection: sqlite3.Connection):
    connection.commit()
    connection.close()

Then in this code I call my previously made function get_wufoo_data() to grab the JSON responses, store it in an array data and then insert that in a database named demo_db. Finally the database will be closed to follow proper guidelines.

def main():
    data = get_wufoo_data()
    data1 = data['Entries']
    conn, cursor = open_db("demo_db.sqlite")
    print(type(conn))
    setup_db(cursor)
    insert_db(cursor, data1)
    close_db(conn)

Consequently a test was made to ensure these functions behaved proerly.

def test_db():
    conn, cursor = open_db("test_db.sqlite")
    print(type(conn))
    response = get_wufoo_data()
    data1 = response['Entries']
    setup_db(cursor)
    insert_db(cursor, data1)
    cursor.execute("SELECT * FROM entries LIMIT 1")
    result = cursor.fetchone()
    assert result is not None, "No entry found in the first row of the table"
    close_db(conn)

Sprint 3

The goal of the third sprint is to create a GUI for the user (Bridgewater State Faculty) to interact with and be able to claim a student’s project.

This was the biggest file in my codebase as the visual component had a lot of dialog options needed. The logic and code behind the scenes was easier than presenting it.

PyQt was to be used for this, I saw it as easier to test than tkinter, but you may have a different preference. There was a lot of code needed to create this GUI so I will try to break it down the best I can.

Basic code to setup GUI window with PyQt.

 def setup(self):
        self.setWindowTitle("CUBES Project List")
        self.setGeometry(100, 100, 800, 650)

        self.entry_list = QListWidget(self)
        self.entry_list.setGeometry(10, 20, 320, 440)

Two options are given on the main menu when opening the program, they are to either show the listed projects or update the database with new Wufoo forms via API.

Main Manu

If update database is selected then a API call will be made to Wufoo and the SQLite database will be updated accordingly.

Alternatively if data visualization is chosen then the list of projects is shown to the user, each project has it’s own button to select it, upon selection it will present the student’s information and allow an “add entry” to be made.

The add entry is how the Professor will claim the project, it will be marked with their information upon selection.

Selection

The code to support this is shown below, if the user selects a project that is claimed already then a warning message box will be shown the user stating the project is already claimed.

if selectedItem is None:
            print("No button selected")
        try:
            self.cursor.execute("SELECT is_claimed FROM isClaimed WHERE entry_id=?", (self.project_index,))
        except sqlite3.IntegrityError:
            QMessageBox.warning(self, "Error", "Sqlite Integrity Error.")
        rowResult = self.cursor.fetchone()
        self.conn.close()

When the claim button is pressed an entry dialog appears asking the user to enter in their information with a submit button. The button will attempt to enter their information if there is no duplicate BSU email already that exists.

Also, the clear button simply wipes the fields in the dialog window. The project button in the list appears yellow only if the user successfully submits their faculty information. If it is successful a message box appears that closes after a short time stating their project was successfully claimed.

Claim Entry

And if the project is already claimed an error will be presented.

Error

def claimProject(self):
        self.conn = sqlite3.connect('demo_db.sqlite')
        self.cursor = self.conn.cursor()
        selectedItem = self.entry_list.currentItem()
        selectedProject = self.entry_list.itemWidget(selectedItem)
        self.project_index = self.entry_list.row(selectedItem) + 1

        if selectedItem is None:
            print("No button selected")
        try:
            self.cursor.execute("SELECT is_claimed FROM isClaimed WHERE entry_id=?", (self.project_index,))
        except sqlite3.IntegrityError:
            QMessageBox.warning(self, "Error", "Sqlite Integrity Error.")
        rowResult = self.cursor.fetchone()
        self.conn.close()

        if rowResult:
            QMessageBox.warning(self, "Error", "This project is already claimed.")
            return
        else:
            selectedProject.setStyleSheet("background-color: yellow")
            dialog = AddEntryDialog(self, self.project_index)
            dialog.exec()

Conclusion

The database structure for this project consists of four tables:

1. Entries: This table has the primary key entryId, which stores the Wufoo form responses retrieved via the API.
2. Records: Stores the name, email, title, and department of each faculty member.
3. Entry Records: Links the entryID to the faculty member’s email who claimed the project and includes a boolean isClaimed to indicate whether the project has been claimed.
4. Student Records: Connects the bsu_email with the corresponding entryID.

This well-organized database layout resolved the challenge of accurately presenting information within the GUI.

Overall, this project was an excellent learning experience, giving me a deeper understanding of the software development process in a solo setting with real-world objectives. My next project took this even further, as I worked in a group on solving issues for a GitHub security scanner in active use.

Thanks for reading! Stay tuned for more updates…