Build a Simple Tracker Program Using Python and SQL — Video

Hello everyone,

I’ve been meaning to dive deeper into SQL. So I thought of a fun build idea that could use Python and SQL to keep track of materials in my favorite old school game Phantasy Star Online. Welcome to Simple Python Programs — Simple Tracker

Photo by Michael Dziedzic on Unsplash

Video tutorial for simple tracker program.

In the video above I run through the creation of the program end to end.
The program uses sqlite3 and Python3. I use two other inbuilt libraries (sys, and subprocess) to clear the screen between prints and exit the program if needed.

I’m a really big fan of building simple utilities you can run from your terminal

This particular program was the first in the series that used Visual Studio code. Initially, I began programming in the terminal using the Nano code editor but very quickly realised that I could do with something more robust this time. Visual Studio Code offers ‘Intellisense’ a code completion system that guesses and fills out your code. It also has useful highlighting and language-specific syntax and indenting which is useful when programming in Python.

Initialising the tracker program returns this user interface

I'm a really big fan of building simple utilities you can run from your terminal. I've created a simple calculator and a simple timer so far. I use them regularly.

The interface between Python and my database is sqlite3 and it is very simple to use. Learning just a few commands goes a long way.

This particular project tested me in a number of ways. I had to revise the SQL syntax for querying a database, also the code to connect and establish a database as well as populate it with data.

Referencing a table in a database to retrieve values for a python print statement was also new to me. It really made me see the difference between the cursor object and a fetch object.

The parameterisation practices that are encouraged were really interesting. Using tuples to feed the values into the placeholders for INSERT and UPDATE statements was really interesting. I learned a lot from making this program!

Here is the Feature list used to build the program:

The interface between Python and my database is sqlite3 and it is very simple to use. Learning just a few commands goes a long way. As for the state of the code after the first run well you can see it on my Twitch live coding sessions it was a mess!

During the recording of the tutorial video, I chose to refactor the code as I went along. There were definitely many hiccups! In fact, I think I even mistyped the database name at one point but thankfully sqlite3 is easy to work with and I was able to get back on track quickly enough.

Live Coding Sessions occur every Monday — Thursday at 2pm — 4pm
(times are subject to change) Schedule: Link

Check out more simple Python programs on my youtube playlist: Link

References

1. Sqlite3 — docs
2. Simple program tutorials — Youtube playlist
3. Live coding sessions — Twitch