I recently built my first Command Line Interface project utilizing Python, SQLAlchemy, and Alembic. The most challenging parts of this project were setting up my initial file structure and determining what code needed to belong in each file. My hope is this tutorial will save other new Python coders time when developing their own CLI projects and can serve as a starting point for developers building their first CLI! I am sharing code snippets from my project and have included the GitHub repo to the full code at the end of this post.

First Considerations

Applications can be most effective when they solve real-world problems. Before starting my project, I identified an issue I had as a music teacher - tracking locker and musical instrument assignments for my high school students - and then designed a CLI project to address this need.

When building a CLI connected to a database, one of the first considerations are the tables and relationships the database will consist of. For my music locker room database, I needed three tables: Lockers, Instruments, and Students. I determined these tables would be related through a many-to-many relationship through the Student table, as a Student could have many instruments and many lockers, but a Locker and an Instrument could each only have one Student assigned.

File Structure

I set up my project with the following folder tree:

Within the db folder, I created 3 files:

• debug.py (for testing my instances)
• models.py (where to write my table classes)
• seed.py (where to create all of my instances to seed the database)

The top level bando.py file is where I wrote all of my actual CLI code. I utilized the subfunctions folder and additional .py files within this folder to refactor out my CLI code later on.

The db folder is where I created my Alembic migrations, and the db/models.py file is where I created all of my database table models.

Imports

Once establishing this initial file structure, I opened up my project and installed SQLAlchemy and Alembic through pipenv:

pipenv install sqlalchemy alembic

Later, I imported additional packages to help me build out my CLI functionality, including Inquirer, Pandas, Faker, and Redux.

Next, I ran the following command to enter the virtual environment:

pipenv shell

Database Set Up

In order to utilize alembic to manage migrations, I needed to add some code to the db/alembic.ini and db/migrations/env.py files generated when I installed alembic.

alembic.ini
Line 63:
sqlalchemy.url = sqlite:///band_lockers.db
This line needed to be changed to the name of the database file I wanted to set up.

env.py
After line 20 add:
from models import Base
target_metadata=Base.metadata

Next I set up my tables using the db/models.py file. After creating each table, I ran the following commands in my terminal:
alembic autogenerate revision -m "message here"
followed by:
alembic upgrade head
Each revision created a migration version in the db/migrations/versions folder. Utilizing Alembic for migrations can be really helpful in case the need to revert to a previous revision arises.

After running the first migration, a new .db file should be added to the tree. This is the file to reference when instantiating any other sessions (like in the top level .py file where I set up my CLI!)

After setting up my tables (and relationships utilizing backref) in the models.py file, I moved to the seed.py file to create instances of my classes and seeded my database.

Seeding the Database

SQLAlchemy relies on a couple of imports: Session and create_engine

Here is an example of how I set up my seed file to use SQLAlchemy:

seed.py

from sqlalchemy import create_engine
from sqlalchemy.orm import Session

engine = create_engine("sqlite:///band_lockers.db")
session = Session(engine, future=True)

_Create instances of classes here..._

session.close()
session.commit()

Instantiating a CLI

In the bando.py file, I imported create_engine and Session from SQLAlchemy again and set up a Cli class (which included all of my logic to run my CLI!).
I instantiated my Cli class in the if name == "main" block like so:

if __name__ == "__main__":
engine = create_engine("sqlite:///db/band_lockers.db")
session = Session(engine, future=True)
Cli()

This block of code runs the defined Cli class when the session is instantiated - aka when the file is run!

Conclusion

Beyond this point, the CLI class can be defined to include whatever logic the developer would like. I chose to have my logic complete full CRUD operations to allow the user complete control of the database from the command line. Feel free to peruse my project code to see examples of how I handled this logic!

GitHub Repo
Band Locker CLI Walkthrough