Build a Simple Tracker Program Using Python and SQL — Video
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
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.
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.