By Douglas Starnes 
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:
| Title | Timestamp | Private |
| Normal Event | February 2, 2025 | No |
| Past Event | January 1, 2025 | No |
| Private Event | March 3, 2025 | Yes |
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:
| Title | Timestamp | Private |
| Normal Event | February 2, 2025 | No |
| Past Event | January 1, 2025 | No |
| Private Event | March 3, 2025 | Yes |
| Yesterday | January 10, 2025 | No |
| Secret Event | April 4, 2025 | Yes |
| Cinco de Mayo | May 5, 2025 | No |
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 eventAgain 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, selectThe 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 eventsAnd 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.