Filtering and Removing Data with the SQLModel API and Typer Options

person pouring hot water on white coffee filter

In the previous post, we saw how to use SQLModel to implement the Event data model class and create a command to accept user input to add new Events to the database. We also saw how to retrieve those events and format them using Rich tables.

This post will look at how to use SQLModel to filter the Events by adding options to the CLI to show or hide private and past Events. And it will look at how to remove individual Events and remove Events in bulk.

Suppose that there are the following events in the database:

TitleTimestampPrivate
Normal EventFebruary 2, 2025No
Past EventJanuary 1, 2025No
Private EventMarch 3, 2025Yes

Keep in mind that this post is being written on January 11, 2025. Thus the “Past Event” on January 1, 2025 is the only event in the past on the date this post was written.

Filtering Data

Let’s create a couple of options to filter the private events and the past events. These options will be passed as parameters to the show_events command function. Let’s start with show_past:

from typing import Annotated

@app.command()
def show_events(
  show_past: Annotated[bool, typer.Option()] = False,
)

There is a lot going here so let’s take it apart. The first thing is this Annotated type from the typing module. It let’s us add metadata to a type. In this case we are using it to tell Typer that show_past is of type bool and that it should be a CLI option with the typer.Option() function. In addition, the default value of show_past is True. Since the option is a bool, Typer will make some assumptions for us. It will create command line options: --show-past and --no-show-past. If --show-past is present in the command, the show_past parameter will receive a value of True. If --no-show-past is present, the show_past parameter will receive a value of False. Since the default value of show_past is True, omitting both options will be the same as --show-past being present in the show-events command.

For show_private, we can do the same thing:

@app.command()
def show_events(
  show_past: Annotated[bool, typer.Option()] = True,
  show_private: Annotated[bool, typer.Option()] = False
)

The show_private parameter has a default value of False. Therefore, of the two CLI options, --show-private and --no-show-private, --no-show-private will be the default if neither is included in the show-events command.

TLDR: By default the show-events command will show past events, but not private events. Now let’s make it work.

In show_events, remove the code that executes the select statement that retrieves all of the events. Replace it with the select statement and order the events by timestamp in ascending order:

query = select(Event).order_by(Event.timestamp.asc())

This statement selects all events regardless of whether they are past or private. Now we can add where clauses to the statement to filter the data based on the options. If the --no-show-past option is present, we should only include events with a timestamp of today or later:

query = select(Event).order_by(Event.timestamp.asc())
if not show_past:
  query = query.where(Event.timestamp >= datetime.date.today())

And if the --no-show-private option is present, we should only include events where private is False:

query = select(Event).order_by(Event.timestamp.asc())
if not show_past:
  query = query.where(Event.timestamp >= datetime.date.today())
if not show_private:
  query = query.where(Event.private == False)

And now you can execute the statement and retrieve the results with the session:

query = select(Event).order_by(Event.timestamp.asc())
if not show_past:
  query = query.where(Event.timestamp >= datetime.date.today())
if not show_private:
  query = query.where(Event.private == False)
events = session.exec(query).all()

The result of the code remains the same!

At the command line, get the help for the show-events command:

$ python main.py show-events --help
                                                                                                                                             
 Usage: main.py show-events [OPTIONS]                                                                                                        
                                                                                                                                             
╭─Options─────────────────────────────────────────────────────────────────╮
 --show-past       --no-show-past         [default: show-past]           
 --show-private    --no-show-private      [default: no-show-private]                                                                       
 --help                                   Show this message and exit.                                                                      
╰─────────────────────────────────────────────────────────────────────────╯

As explained about the default options are --show-past and --no-show-private. So invoking the show-events command without any options will display all events that are not private. Let’s see if it does:

$ python main.py show-events
                  Events                   
┏━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━┳━━━━━━━━━┓
 Title         Days Remaining  Private 
┡━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━╇━━━━━━━━━┩
 Past Event    -10 remaining   No      
 Normal Event  22 remaining    No      
└──────────────┴────────────────┴─────────┘

The output shows the one past event (again, as of the date of writing this post) and does not show the private event. To show the private event, invoke the show-events command with the --show-private option:

$ python main.py show-events --show-private
                   Events                   
┏━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━┳━━━━━━━━━┓
 Title          Days Remaining  Private 
┡━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━╇━━━━━━━━━┩
 Past Event     -10 remaining   No      
 Normal Event   22 remaining    No      
 Private Event  51 remaining    Yes     
└───────────────┴────────────────┴─────────┘

This is the equivalent of showing all events. And we can add the --no-show-past option to omit past events:

$ python main.py show-events --show-private --no-show-past
                   Events                   
┏━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━┳━━━━━━━━━┓
 Title          Days Remaining  Private 
┡━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━╇━━━━━━━━━┩
 Normal Event   22 remaining    No      
 Private Event  51 remaining    Yes     
└───────────────┴────────────────┴─────────┘

Note that you cannot use both --show-private and --no-show-private (or --show-past and --no-show-past) at the same time.

And since showing private events is controlled by the options, you can remove the Private column from the table. Again, in a later post we’ll show how to use styles to format the events in the table.

Removing

I’ve added a few more events to the database:

TitleTimestampPrivate
Normal EventFebruary 2, 2025No
Past EventJanuary 1, 2025No
Private EventMarch 3, 2025Yes
YesterdayJanuary 10, 2025No
Secret EventApril 4, 2025Yes
Cinco de MayoMay 5, 2025No

In this section we will add a new command, remove-event. By default, it will list all future events in a table and ask the user to select one to remove. It will also have an option to remove all past events. Let’s start with removing a single event.

Write the command function and in a session, create and execute a select statement for all events in the future.

@app.command()
def remove_event():
  with Session(engine) as session:
    query = select(Event).where(
      Event.timestamp >= datetime.date.today()
    ).order_by(
      Event.timestamp.asc()
    )
    events = session.exec(query).all()

Display the events, with their IDs, in a table:

event_table = Table(title="Events", show_header=True)
event_table.add_column("ID")
event_table.add_column("Title")
event_table.add_column("Timestamp")
event_table.add_column("Private")

for event in events:
  event_table.add_row(
    str(event.id),
    event.title,
    event.timestamp.strftime("%Y-%m-%d"),
    "Yes" if event.private else "No"
  )
console.print(event_table)

Now prompt the user for the ID of the event to remove. Remember to response to an integer as the Prompt.ask function returns a string back to us. For now, we’ll assume the user enters a valid ID:

event_id = int(
  Prompt.ask(
    "What is the ID of the event you want to remove"
  )
)

Since this is a destructive action, we should confirm the user’s choice. This presents a case for the choices keyword argument to the Prompt.ask function. In this case, we want the user to respond with “y” or “n”. We can also mark one of the choices as the default value:

if Prompt.ask(
  f"Are you sure you want to remove the event with ID {event_id}", 
  choices=["y", "n"], 
  default="n"
) == "y":

If the user confirms get the event to be removed. For this we can use a special shortcut method of the session that selects an object by the primary key. The get method accepts the type of the object and the value of the primary key.

selected = session.get(Event, event_id)

Use the session to delete the object and then commit the changes

session.delete(selected)
session.commit()

Print a confirmation to the user and we’re done!

console.print(
  f"Removed event with ID {event_id}", 
  style=app_styles.success
)

Removing an event is simple:

$ python main.py remove-event
                   Events                    
┏━━━━┳━━━━━━━━━━━━━━━┳━━━━━━━━━━━━┳━━━━━━━━━┓
 ID  Title          Timestamp   Private 
┡━━━━╇━━━━━━━━━━━━━━━╇━━━━━━━━━━━━╇━━━━━━━━━┩
 2   Normal Event   2025-02-02  No      
 3   Private Event  2025-03-03  Yes     
 5   Secret Event   2025-04-04  Yes     
 6   Cinco de Mayo  2025-05-05  No      
└────┴───────────────┴────────────┴─────────┘
What is the ID of the event you want to remove: 2
Are you sure you want to remove the event with ID 2 [y/n] (n): y
Removing event with ID 2

$ python main.py show-events --show-private
                   Events                   
┏━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━┳━━━━━━━━━┓
 Title          Days Remaining  Private 
┡━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━╇━━━━━━━━━┩
 Past Event     -10 remaining   No      
 Yesterday      -1 remaining    No      
 Private Event  51 remaining    Yes     
 Secret Event   83 remaining    Yes     
 Cinco de Mayo  114 remaining   No      
└───────────────┴────────────────┴─────────┘

Now let’s add an option to remove all past events. First add a parameter to the command function using the Annotated type. The default value is False:

def remove_event(
  purge_past: Annotated[bool, typer.Option()] = False
)

Inside of the with block for the session, add a condition to check the value of purge_past. If it is True, delete the past events. If False, run the code to list the events and select one to remove by ID:

with Session(engine) as session:
  if purge_past:
    # delete past events
  else:
    # delete simgle event

Again this is a destructive action so we’ll ask the user to confirm:

if purge_past:
  if Prompt.ask("Are you sure you want to remove past events",
                choices=["y", "n"],
                default="n") == "y":

Since we want to delete multiple events we will use the delete function from the sqlmodel package:

from sqlmodel import Field, Session, SQLModel, create_engine, delete, select

The statement itself is just like a select statement, except using the delete function:

if purge_past:
  if Prompt.ask("Are you sure you want to remove past events",
                choices=["y", "n"],
                default="n") == "y":
    query = delete(Event).where(Event.timestamp < datetime.date.today())

To finish up, execute the statement, commit the changes and print a confirmation to the user:

if purge_past:
  if Prompt.ask("Are you sure you want to remove past events",
                choices=["y", "n"],
                default="n") == "y":
    query = delete(Event).where(Event.timestamp < datetime.date.today())
    session.exec(query)
    session.commit()
    console.print("Removed all past events", style=app_styles.success)

Currently, there are 5 events in the database, 2 are past events.

$ python main.py show-events --show-private
                   Events                   
┏━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━┳━━━━━━━━━┓
 Title          Days Remaining  Private 
┡━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━╇━━━━━━━━━┩
 Past Event     -10 remaining   No      
 Yesterday      -1 remaining    No      
 Private Event  51 remaining    Yes     
 Secret Event   83 remaining    Yes     
 Cinco de Mayo  114 remaining   No      
└───────────────┴────────────────┴─────────┘

Invoke the remove-event command with the --purge-past option:

$ python main.py remove-event --purge-past
Are you sure you want to remove past events [y/n] (n): y
Removed all past events

And see the past events are no longer in the list:

$ python main.py show-events --show-private
                   Events                   
┏━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━┳━━━━━━━━━┓
 Title          Days Remaining  Private 
┡━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━╇━━━━━━━━━┩
 Private Event  51 remaining    Yes     
 Secret Event   83 remaining    Yes     
 Cinco de Mayo  114 remaining   No      
└───────────────┴────────────────┴─────────┘

Summary

In this post, you saw how to create options for Typer commands. You used these options along with the where method in the SQLModel API to filter data in the database. You also saw two different ways to delete data. The first is to select a single object and delete it using a session. The second is to use the delete function the in SQLModel API to delete multiple objects at the same time.

By Douglas Starnes

Entrepreneur, 5x Microsoft MVP, AI/BI nerd, crypto investor, content creator, trained composer, challenging the status quo, proud American

Leave a Reply

Your email address will not be published. Required fields are marked *