How to Build a Command Line Interface Tool with Python, Pandas, Typer, and Tabulate for Data Analysis

Recently, I have been spending a lot of time looking at data. At first, it started off with running queries in a database client. However, I found myself running the same series of queries over and over again. Once a task becomes repetitive enough, automation becomes the next rational step.

I needed a way to quickly run a parameterized SQL query and see the results. I started off with a basic Python script using the built-in argparse library. This worked fine initially when I had a few queries to run, but as the number of commands and subcommands grew, it became harder to maintain.

As a fan of FastAPI, I decided to try Typer, a CLI framework created by the same author. It worked extremely well to simplify the creation and standardization of my growing library of CLI tools. Creating subcommands with Typer requires no thinking at all. Doing the same thing with vanilla Python and argparse is not as straightforward.

I have added Pandas and Tabulate to my list of essential Python libraries for working with data. In this short tutorial, I will provide a starter template on how to use these libraries together for simple data analysis. I’ll be using PostgreSQL for the database, but this would work with any other database.

First, let’s create a directory and start the virtualenv.

$ mkdir how-to-build-cli
$ cd how-to-build-cli
$ python3 -m venv venv…