Music Track Database with SQLite and Python
Date: 2/12/2025
This was a fun assignment put together by the Python For Everyone course on Coursera. It is taught by Charles Russell Severance for the University of Michigan. He is a great teacher (even though he’s a Slytherin) and I recommend his courses. I will definitely look at taking other courses of his in the future.
High level overview: use python to create a sqlite database for music tracks that stores several pieces of information for each track in different tables and have those tables link to each other using primary and foreign keys.
This first section of python opens the empty database and creates new tables for Artist, Album, Track, and Genre. For each table, we need to define each column of information.
This next section of python opens a csv file with a large number of music tracks exported from a popular music app. The file is then parsed into the different pieces of information that is typically needed for each music track entry. At the same time, primary and foreign keys are designated to form relationships between the different tables. This is done to allow for quick navigation of a database table.
*Note: using an integer key and creating relationships between tables instead of putting all of the actual data in one table speeds up retrieval in large databases. While not necessary in this instance, it is good practice.
The python script was relatively short, but powerful. Here is a snippet of the database using an SQLite browser. You can see the use of foreign keys to match music tracks to their respective album, artist, or genre IDs.
Here is the end product showing a simple join query to visualize how everything comes together.
And I love it when a plan comes together.