Web Relational Database Using Flask and Python

Project Overview

This project involves the creation of a web relational database application using Flask, Python, and MySQL. The application allows users to perform CRUD (Create, Read, Update, Delete) operations on the database through a web interface.

Schema Design

A database schema was devised that would accomodate for functionalities such as account creation, playlist creation, and playlist editing.

• Table 1: artists
  • ArtistID: Integer, Primary Key
  • name: String
  • monthly_listeners: Integer
  • country: String
• Table 2: users
  • UserID: Integer, Primary Key
  • password: String
  • username: String
  • email_address: String
• Table 3: playlists
  • PlaylistID: Integer, Primary Key
  • owner_UserID: Integer, Foreign Key
  • playlist_name: String
• Table 4: songs
  • songID: Integer, Primary Key
  • song_artistID: Integer, Foreign Key
  • name: String
  • genre: String
  • decade: Integer
• Table 5: playlist_songs
  • PlaylistID: Integer, Primary Key(composite), Foreign Key
  • songID: Integer, Primary Key(composite), Foreign Key

Initialization

A Flask app environment and an instance of the database are created:

A full database dump .sql file is available on the GitHub repository with the CREATE statements for all other tables.

Implementing CRUD functions

The account functionality was implemented, allowing users to create accounts and sign in:

Users can create a new account, which stores their credentials in MySQL, and sign in to access their playlists:

Once signed in, users can create and manage playlists:

Filtered Reports

The final functionality implemented was allowing users to filter playlist songs based on attributes like genre and decade. A stored procedure was used for querying the data:

Table Indexing and Transaction Concurrency

To enhance performance, indexing was applied to certain columns like PlaylistID, SongID, and UserID. This reduces query execution time. Additionally, transaction isolation levels were considered to ensure the database maintains integrity.

Code

The code for this project is available on GitHub. The following files are included:

• main.py: The main Flask application file.
• templates/: Directory containing the HTML templates.
• CreateReport.sql: Stored procedure for filtering report data.
• musicdb_dump.sql: Database dump to create a copy of the DB.

Future Work

Future improvements could include integrating the Spotify API to fetch real user data and improving the front-end for a better user experience.

Project Takeaways

This project helped me gain a deeper understanding of Flask, web development, relational databases, and SQL injection prevention.