- author: Monkhaus
Converting a Python To-Do List Command Line App to Use Sqlite3 for Storage
Welcome back! In this tutorial, we will be running some more trash codes. If you recall from a couple of videos ago, I made a Python to-do list command line app using a text file for storage. Today, we will be converting it to use sqlite3 instead.
Our goal is to keep the to-do list command line app as a separate thing from the previous version. We will be writing all the new code from scratch. We want to create a new file called "todo.py" and use it to connect to a sqlite3 database called "todo.db". We will be creating a table function that we can call everytime we want to run this code. We will be using several functions to add items, mark items as done and list items in the database.
To begin with, we want to import the following libraries:
These libraries are part of the Python standard library, so you don't need to import or install anything new.
Next, we will create our database, using the "create table" function. We will be calling this function everytime we want to run this code. If the table already exists, we won't create it again.
Then, we will grab some data. If we are not able to grab any data, we create the table. If we can, we will say that the table already exists. In the previous version of the command line app, we only added a strike through to items that were completed. We will still be using this function, which contains an ASCII escape code that adds a strike through to your text. We won't be displaying this code or adding it to the database. We will only be using it when items are listed as "done".
Our table in the database consists of the following columns:
Number represents the number of the item in the list. If you have three items, the second item in the list would be numbered "2". The "Text" column is where the actual text of the to-do item is stored. The "Done" column is a boolean flag that is set to "0" or "1".
When we add an item, we will insert it into the database. We will connect to the database and insert the item's text into it.
Listing the items is quite simple. We select all the items from the database. If there are no rows, we will say "items not found". We will iterate through the rows and add a strike through text to any item that is "done". This is the advantage of using a database. We can just look up whether an item is "done" or not.
Marking an item as done is also simple. We will set the item's "Done" column to "1".
The command line arguments function in this new version is exactly the same as the previous version. We will use the same interface for this function.
This new version of the to-do list command line app is quite an improvement from the previous version. There is a lot we could still do to improve it. We could add some bash commands to interface with it more easily. That would make it less cloggy. For now, this is it. Thank you and have fun coding!