Travis Luong

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
$ . venv/bin/activate

Next, install the dependencies.

$ pip3 install typer pandas tabulate psycopg2

Create a main.py file.

import pandas as pd
import psycopg2
import typer
from psycopg2.extras import NamedTupleCursor
from tabulate import tabulate

app = typer.Typer()

conn = psycopg2.connect(
    dbname="nfp_boilerplate_dev",
    user="nfp_boilerplate_user"
)

cur = conn.cursor(cursor_factory=NamedTupleCursor)

@app.command()
def example1():
    """fetch notes"""
    cur.execute("SELECT * FROM notes")
    records = cur.fetchall()
    print(tabulate(records, headers="keys", tablefmt="psql"))

@app.command()
def example2():
    """fetch notes and load into pandas"""
    cur.execute("SELECT * FROM notes")
    records = cur.fetchall()
    df = pd.DataFrame(records)
    print(tabulate(df, headers="keys", tablefmt="psql"))

@app.command()
def example3(id: int):
    """fetch note by id"""
    cur.execute("SELECT * FROM notes WHERE id=%s", (id,))
    records = cur.fetchall()
    df = pd.DataFrame(records)
    df = df.transpose()
    print(tabulate(df, headers="keys", tablefmt="psql"))

if __name__ == "__main__":
    app()

Run the cli tool.

$ python3 main.py
$ python3 main.py --help
$ python3 main.py example1
$ python3 main.py example2
$ python3 main.py example3 1

The python3 main.py --help command should give you a list of all subcommands along with their descriptions.

The example1 and example2 command should print an output like this:

+------+--------+-------------+
|   id | text   | completed   |
|------+--------+-------------|
|    1 | string | True        |
|    2 | foobar | True        |
|    3 | bar    | True        |
|    4 | asdf   | False       |
|    5 | zzz    | False       |
|    6 | qqq    | False       |
|    7 | asdf   | False       |
|    8 | zxcv   | False       |
+------+--------+-------------+

In example2 you can do whatever data transformations you need to do with pandas.

The example3 command should print something like this:

+-----------+--------+
|           | 0      |
|-----------+--------|
| id        | 1      |
| text      | string |
| completed | True   |
+-----------+--------+

The transpose method of pandas flips the axis of the dataframe to give you a vertical layout of the data. This is very useful when you have one or a small number of rows, but a lot of columns to print.

Congratulations. You have learned how to build a simple cli tool using Python, Pandas, Typer, and Tabulate to query and view data in a database.

Here is the completed code:

https://gist.github.com/travisluong/cd1983e295e18221990b914b880793c6