CRUD in Python SQLite

In almost all programs there is a need to create, read, update, and delete information. All of these operations can be performed with Python code that interfaces with SQLite. The first thing that needs to be done to use CRUD is to create relational databases. This CLI Template was provided in the curriculum of the software development course at Flatiron School, and is a good starting point for someone creating their first project. Once you have forked and cloned the repository, you need to make sure that sqlite is installed on your machine. You can run sqlite3 --version to check. If it is not, run brew install sqlite on Mac to install it. It is also recommended to install VSCode's SQLite extension so that you can view the databases that you create. Once these are installed, you can proceed to creating your tables. For the purposes of this walkthrough, I will skip to CRUD operations once the tables have been created in the models.py file.

Suppose you have three tables: a cheeses table, a dairy plants table, and a consumer table. In this instance, the cheeses table would be the join table for a many-to-many relationship, because there are many dairy plants that produces many cheeses, and there are many consumers that consume many cheeses. After creating the tables with appropriate columns, such as "id", "location" and "name" for the dairy plants, "id", "type" , "dairy plant id", and "consumer id" for the cheeses, and "id", "name" and "age" for the customer. In the cli.py file, there could be conditional selections to create instances (rows) of each table. For example:

from db.models import DairyPlant, Cheese, Consumer
from db.dairy_plant import add_dairy_plant
from db.cheese import add_cheese
from db.consumer import add_consumer
if __name__ == '__main__':
    print("Welcome to the Dairyland CLI!")
while True:
    print("Choose an Option:")
    print("Add Dairy Plant")
    print("Add Cheese")
    print("Add Consumer")

if selection == '1':
    name = input("Enter Dairy Plant Name: ")
    location = input("Enter Dairy Plant Location: ")

Similar logic can be added to the other two categories to create additional entries. Moving on to read functions, a fourth option could be added to the CLI list to view existing entries. Within this option, there can be sub-options to update each table:

if selection == '4':
    print("Choose a Table to View")
    print("1. View Dairy Plants")
    print("2. View Consumers")
    print("3. View Cheeses")

    view_option = input("Enter your choice.")
    if view_option == '1':
        session = Session()
        dairy_plants = session.query(DairyPlant).all()
        session.close()

        print("Dairy Plants: ")
        for dairy in dairy_plants:
            print(f"ID: {dairy.id}, Name: {dairy.name} 
            Location: {dairy.location}")
## continue the same pattern for the remaining tables.

To update existing entries, a fifth option could be added to the CLI, and the format would be the same as the read until the update logic:

if update_option == '1':
   dairy_id = input("Provide the ID of the dairy plant you 
   want to update")
   dairy = 
   session.query(DairyPlant).filter_by(id=dairy_id).first()

   if dairy:
       print("Current Dairy Plant: "
       print(f"Name: {dairy.name}, Location: 
       {dairy.location}")

       new_name = input("Enter new name or press Enter to 
       skip")
       new_location = input ("Enter new location or press 
       Enter to skip")

       if new_name:
           dairy.name = new_name
       if new_location:
           dairy.location = new_location
       session.commit()
       session.close()
       print("Dairy Plant updated.")
  else:
      print("Dairy Plant does not exist."

Finally, you can create an option to delete existing items:

if selection == '6':
    print("choose an option :")
    print("1. Delete Dairy Plant")
     ...
    delete_option = input("Enter your choice:")

    if delete_option == '1':
        dairy_id = input ("Enter the ID of the plant to 
        delete")
        session = Session()
        dairy = 
        session.query(DairyPlant).filter_by 
        (id=dairy_id).first()

        if dairy:
            session.delete(dairy)
            session.commit()
            session.close()
            print("Dairy plant deleted successfully.")
        else:
            print("Dairy plant does not exist.")

This is a basic overview of how to complete CRUD operations in a simple CLI project using Python and SQLite. It is important to remember that if you are going to make changes to the structure of the tables after instantiating them, it is necessary to run alembic upgrade head or the simpler (and my preference) method of deleting the .db database files and regenerating them after making the required changes. Once you have a large amount of data that you don't want to lose stored in the tables, it makes sense to spend the time and effort on mastering alembic so that changes can be made without starting from scratch. However, for a simple learning project it is much more straightforward to delete the whole table. You will need to exit the CLI interface, which you can do with ctrl + d. after that, you will need to run python cli.db again to enter the interface again to check your work. Make sure that updates are included in the models.py file, where the tables are designed, the cli.py file where the CRUD methods are defined, and in your seed.py file(to make test data).