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 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.