Learning SQL and Python Flask With a Vinyl Collection Part 1: Research and Back-End Creation

Intro:

Over the past few months I have been learning Python and have been making small scale assignments for myself to familiarize development with it. I am constantly on the lookout for real life scenarios that I can intertwine into my learning experiences. 

Recently, my brother took it upon himself to catalog our record collection in a spreadsheet. As an IT person I see scenarios like these as opportunities for growth. I set out to challenge myself to create a database to sharpen my SQL skills, as well as to try my hand at making a web app to assist guests with music selection.

My goal with this project is exactly as stated above, to make a small scale web app that can communicate with a SQL database to display search results in a readable format on a web page. A restriction I placed upon myself in this project was to limit access to any form of AI. I feel like for the purpose of this project I would like to gain a base level understanding of the technologies I am working with before employing any shortcuts. I would also like to acknowledge that with a data set this small in size I very well could hard-code it into the app itself, however this would not be indicative of how a larger scale app would function.

Research:

With all of the data already in a spreadsheet (thanks, Dave!), I knew I could export it into a CSV, making it a perfect asset to populate the table. The only problem was that I hadn't touched SQL since my undergrad classes. I needed to find a solution for working with a small scale database for personal app development.

After some research, I found that Microsoft has a great option that is free for developers, allowing me to create a database hosted on my desktop known as Microsoft SQL or MSSQL. First, I downloaded the SQL server software and started the MSSQLSERVER service.

With the service up and running, I needed a GUI to interact with. Thankfully, Microsoft also has SQL Server Management Studio (SSMS), which is also free for developers. I installed it and was greeted with the connection window. It was already per-configured to work with my localhost MSSQLSERVER service. All I had to do was click 'connect,' and I was in.

I took a moment to glance around the screen and familiarize myself with the GUI. My goal at this point was to create a blank table and see if I could load a CSV to populate it. After a moment, I got a new database created along with a new table. Thanks to my past work experience with bulk DNS record updates, I knew there had to be a way to get the table to accept a CSV. After a right-click on the table, I found the option to do just that.

I got the CSV uploaded and took a look at the records in the database and noticed I had run into my first problem…

The data set, for the sake of a more cohesive alphabetization, used commas in artist names that start with the word "the." With a CSV being values separated by commas, this would not work for populating my table. After much deliberation (and not wanting to manually find and delete commas in over 300 entries), I realized that there was another option. I could save the file as what is called a TSV (tab-separated values). This was perfect, and SSMS had an option to accept TSV files.

Now that I had a fully populated table with our collection, it was time to try my hand at interacting with the records and fields. I deferred to a resource I used a lot in school: w3schools.com. They have some great, free, and interactive courses spanning a wide range of technologies. So, if you are thinking about learning some new skills, I cannot endorse them enough as a great starting point on your journey.

I navigated over to their tutorial for SQL (https://www.w3schools.com/sql/default.asp) and began reading all about SQL, from syntax to how to structure queries and what other operations one can perform. I took that information back over to my database and began trying some out. I found it much more interesting because I was personally invested in the data I could get back, from how many albums from a certain artist to how many unique artists we have.

After familiarizing myself with basic operations and query structure, I thought back to my time in school and how a many-to-many relationship is one of the biggest sins in relational database structure. So, I knew if I wanted to make a functional web app, I would need a primary key to distinguish each record. After some research I came back with an operation to add a simple ID number as a new field in the database that increments n+1 for each record.

Python Integration:

While it is great to have a database I can interact with through SSMS, if I wanted to have something that could potentially be hosted on the internet, I would need a different way to manipulate it. Thankfully, I have also been learning Python. So, I decided to make a simple script that would connect to the database and allow a user to input a query.

I again set out to research what tools Python had for interacting with SQL databases and came across Pyodbc. With this handy tool, I could open a connection to my database within my Python script, execute a query or operation, then close the connection when the user is done. I watched some video tutorials on how the syntax works and then tried it for myself. After figuring out how to direct the script to connect to a localhost service, I ran the script. To my amazement, my very first query worked. I now had a way to hook into my database and get output in a PREDICTABLE form within Python! That sounds like the basis for a functional back-end if I have ever heard of one.

Conclusion:

Overall, this project is off to a great start and I am looking forward to the challenges I can overcome in the future. I saw this as a stepping stone to creating a functional, small scale web app. Plus, I now have a populated database table and a way to pull records into a Python application to interact and manipulate them. Thank you for taking the time to read this and I will see you on my next post!

My next goals:

• Create a script that takes the album name, appends it with "artwork," and then executes a Google search and takes the link of the first image result and adds it to a new field within the table.
• Create a wire-frame of the webpage.
• Create a webpage from the wire-frame.
• Include error handling in the code
• Create a user-friendly search function that doesn't require knowledge of SQL syntax to use.
• Create a script that takes the output of my above script and formats it in HTML.
• Implement Bootstrap to make the webpage responsive and readable on mobile devices.
• Create a banner on the homepage that cycles through random albums in our collection.
• Create a random album pick functionality