In this blog post, we will explore the development of a Python Command-Line Interface (CLI) application that serves as a bike database. This app is a follow up to a previous project where we used JavaScript and a bike API to create a front end application allowing users to see stolen bikes within defined search ranges. This CLI, allows users to manage their bikes, search for stolen bikes, report stolen bikes, and update their bike information. We will walk through the main file of the app, cli.py, and showcase its key features with examples.
Setting Up the Bike Database CLI App
To begin, let's understand the setup and dependencies of our bike database CLI app. The app utilizes SQLAlchemy, a popular Object-Relational Mapping (ORM) library, to interact with the SQLite database. In a nutshell, SQLAlchemy builds SQL database tables based on Python classes. As such, the first step in building this project was determining which classes and tables I would need and what their relationships would be with each other. In this project, I opted to have a User, Bike, and StolenBike class.
from sqlalchemy import (
create_engine,
Column,
Integer,
String,
Boolean,
UniqueConstraint,
ForeignKey,
)
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, backref
import click
import re
import ipdb
Base = declarative_base()
class User(Base):
__tablename__ = "users"
id = Column(Integer, primary_key=True)
username = Column(String, unique=True)
email = Column(String, unique=True)
__table_args__ = (
UniqueConstraint("username", name="uq_username"),
UniqueConstraint("email", name="uq_email"),
)
bikes = relationship("Bike", backref="user")
stolen_bikes = relationship("StolenBike", backref="user")
def __repr__(self):
return "<User(username='%s', email='%s')>" % (self.username, self.email)
class Bike(Base):
__tablename__ = "bikes"
id = Column(Integer, primary_key=True)
brand = Column(String)
model = Column(String)
year = Column(Integer)
serial_number = Column(String)
stolen = Column(Boolean, default=False)
user_id = Column(Integer, ForeignKey("users.id"))
def __repr__(self):
return (
"<Bike(brand='%s', model='%s', year='%s', serial_number='%s', stolen='%s')>"
% (
self.brand,
self.model,
self.year,
self.serial_number,
self.stolen,
)
)
class StolenBike(Base):
__tablename__ = "stolen_bikes"
id = Column(Integer, primary_key=True)
date_stolen = Column(String)
city = Column(String)
state = Column(String(2))
zip_code = Column(String(5))
user_id = Column(Integer, ForeignKey("users.id"))
bike_id = Column(Integer, ForeignKey("bikes.id"))
def __repr__(self):
return (
"<StolenBike(date_stolen='%s', city='%s', state='%s', zip_code='%s')>"
% (
self.date_stolen,
self.city,
self.state,
self.zip_code,
)
)
bike = relationship("Bike", backref=backref("stolen_bikes", uselist=False))
SQLAlchemy, then used these three classes to create corresponding tables of users, bikes, and stolen_bikes. The relationships between each table are defined within the Python classes using SQLAlchemy's realtionship() method:
From the User class
bikes = relationship("Bike", backref="user")
stolen_bikes = relationship("StolenBike", backref="user")
From the StolenBike class
bike = relationship("Bike", backref=backref("stolen_bikes", uselist=False))
The relationship established in the User class is one-to-many relationship to bikes and stolen bikes. This means a single user can have multiple bikes and multiple stolen bikes. Likewise, bike data and stolen bike data is accessible through a user instance. The relationship established within the StolenBike class creates a one-to-one relationship between Bike and StolenBike. A stolen bike can only have one bike and vice-versa.
The next step is establishing a connection to our database so we can query and interact with it using Python. The main components to establish this connection are:
- create_engine: Creates a database engine using SQLAlchemy and specifies the path to the SQLite database file.
- Session: Defines a session class that serves as an interface to interact with the database.
- session: Creates an instance of the session class for database operations.
database_path = "db/spracket.db"
engine = create_engine(f"sqlite:///{database_path}")
Session = sessionmaker(bind=engine)
session = Session()
Main Features and Functionality
The bike database CLI app offers several features for managing bike information. Let's dive into each feature and provide examples of their usage.
Displaying User's Bikes
The display_users_bikes function retrieves and displays all bikes associated with the current user. If the user has registered bikes, their details, including brand, model, year, serial number, and stolen status, are presented in a tabular format. Since I was using click, the current_user was stored via a global variable set in either the create_new_user function or the validate existing user function. In the end, I would like to re-work this code and move this variable out of the global scope but for the purposes of this project, it provides the necessary functionality.
def display_users_bikes():
bikes = current_user.bikes
if bikes:
table_data = [
(
bike.id,
bike.brand,
bike.model,
bike.year,
bike.serial_number,
bike.stolen,
)
for bike in bikes
]
headers = ["ID", "Brand", "Model", "Year", "Serial Number", "Stolen"]
table = tabulate.tabulate(table_data, headers=headers, tablefmt="fancy_grid")
click.echo(f"\n{current_user.username}'s Bikes:")
click.echo(
click.style("\n" + (table) + "\n", fg="green", bg="black", bold=True)
)
else:
click.echo("You have no bikes in your profile.")
Would you like to view your profile, register a new bike, remove a bike from your profile, update one of your bikes, report one of your bikes stolen or search our stolen database (view, register, remove, update, report, search): view
ekline's Bikes:
╒══════╤════════════════════════════╤══════════╤════════╤═════════════════╤══════════╕
│ ID │ Brand │ Model │ Year │ Serial Number │ Stolen │
╞══════╪════════════════════════════╪══════════╪════════╪═════════════════╪══════════╡
│ 14 │ Hill, Dawson and Hernandez │ family │ 1995 │ 73263739 │ True │
├──────┼────────────────────────────┼──────────┼────────┼─────────────────┼──────────┤
│ 55 │ Harris-Christensen │ rule │ 2002 │ 69092985 │ True │
├──────┼────────────────────────────┼──────────┼────────┼─────────────────┼──────────┤
│ 56 │ Ross-Wilkerson │ return │ 2008 │ 28058472 │ False │
├──────┼────────────────────────────┼──────────┼────────┼─────────────────┼──────────┤
│ 60 │ Marsh-Vazquez │ recently │ 1972 │ 78964785 │ True │
├──────┼────────────────────────────┼──────────┼────────┼─────────────────┼──────────┤
│ 65 │ Smith-Swanson │ site │ 2005 │ 27040331 │ False │
├──────┼────────────────────────────┼──────────┼────────┼─────────────────┼──────────┤
│ 78 │ Walter-White │ budget │ 1985 │ 28449713 │ False │
╘══════╧════════════════════════════╧══════════╧════════╧═════════════════╧══════════╛
Removing a Bike
The remove_bike function allows users to remove a bike from their profile. Users are prompted to enter the ID of the bike they want to remove. If the ID is valid and the bike belongs to the user, it will be deleted from the database.
@click.command()
@click.option(
"--id",
prompt="Looking at the bike ID's, which bike would you like to remove?",
type=int,
help="Specify the ID of the bike you would like to remove.",
)
def remove_bike(id):
if id in [bike.id for bike in current_user.bikes]:
bike = session.query(Bike).filter_by(id=id).first()
session.delete(bike)
session.commit()
click.clear()
click.echo("Bike successfully removed.")
display_users_bikes()
main_menu()
else:
click.clear()
click.echo("Bike not found.")
main_menu()
matthew10's Bikes:
matthew10's Bikes:
╒══════╤══════════════╤═════════╤════════╤═════════════════╤══════════╕
│ ID │ Brand │ Model │ Year │ Serial Number │ Stolen │
╞══════╪══════════════╪═════════╪════════╪═════════════════╪══════════╡
│ 30 │ Martin Group │ into │ 1993 │ 73585237 │ False │
╘══════╧══════════════╧═════════╧════════╧═════════════════╧══════════╛
Looking at the bike ID's, which bike would you like to remove?: 30
Bike successfully removed.
You have no bikes in your profile.
Would you like to view your profile, register a new bike, remove a bike from your profile, update one of your bikes, report one of your bikes stolen or search our stolen database (view, register, remove, update, report, search):
Updating Bike Information
The update_bike function enables users to modify the information of a specific bike in their profile. Users are prompted to enter the ID of the bike they want to update, followed by the field they wish to modify (brand, model, year, or serial number) and the new value.
@click.command()
@click.option(
"--id",
prompt="Using the bike ID's, which bike would you like to update?",
type=int,
callback=validate_bike_id,
help="Specify the ID of the bike you would like to update.",
)
@click.option(
"--option",
prompt="What would you like to update?",
type=click.Choice(["brand", "model", "year", "serial_number", "stolen"]),
)
@click.option(
"--value",
prompt="What would you like to update it to?",
callback=validate_value,
help="Specify what you want to update to.",
)
def update_bike(id, option, value):
bike = session.query(Bike).filter_by(id=id).first()
if option == "brand":
bike.brand = value
session.commit()
click.clear()
click.echo("Bike brand successfully updated.")
display_users_bikes()
main_menu()
elif option == "model":
bike.model = value
session.commit()
click.clear()
click.echo("Bike model successfully updated.")
display_users_bikes()
main_menu()
elif option == "year":
while True:
try:
value = validate_year(
None, None, value
) # Validate year using the callback
break # Break the loop if validation succeeds
except click.BadParameter as e:
click.echo(str(e)) # Print the error message
value = click.prompt(
"What would you like to update it to?"
) # Re-prompt for the value
bike.year = value
session.commit()
click.clear()
click.echo("Bike year successfully updated.")
display_users_bikes()
main_menu()
elif option == "serial_number":
bike.serial_number = value
session.commit()
click.clear()
click.echo("Bike serial number successfully updated.")
display_users_bikes()
main_menu()
elif option == "stolen":
bike.stolen = value
session.commit()
click.clear()
click.echo("Bike stolen status successfully updated.")
display_users_bikes()
main_menu()
dcollier's Bikes:
╒══════╤══════════════╤═════════╤════════╤═════════════════╤══════════╕
│ ID │ Brand │ Model │ Year │ Serial Number │ Stolen │
╞══════╪══════════════╪═════════╪════════╪═════════════════╪══════════╡
│ 81 │ Park-Houston │ know │ 1970 │ 29602575 │ True │
╘══════╧══════════════╧═════════╧════════╧═════════════════╧══════════╛
Using the bike ID's, which bike would you like to update?: 81
What would you like to update? (brand, model, year, serial_number, stolen): model
What would you like to update it to?: trx
Bike model successfully updated.
dcollier's Bikes:
╒══════╤══════════════╤═════════╤════════╤═════════════════╤══════════╕
│ ID │ Brand │ Model │ Year │ Serial Number │ Stolen │
╞══════╪══════════════╪═════════╪════════╪═════════════════╪══════════╡
│ 81 │ Park-Houston │ trx │ 1970 │ 29602575 │ True │
╘══════╧══════════════╧═════════╧════════╧═════════════════╧══════════╛
Reporting a Stolen Bike
The report_stolen function allows users to report a bike as stolen. Users are prompted to enter the ID of the stolen bike, and the corresponding record is updated to reflect the stolen status.
ekline's Bikes:
╒══════╤════════════════════════════╤══════════╤════════╤═════════════════╤══════════╕
│ ID │ Brand │ Model │ Year │ Serial Number │ Stolen │
╞══════╪════════════════════════════╪══════════╪════════╪═════════════════╪══════════╡
│ 14 │ Hill, Dawson and Hernandez │ family │ 1995 │ 73263739 │ True │
├──────┼────────────────────────────┼──────────┼────────┼─────────────────┼──────────┤
│ 55 │ Harris-Christensen │ rule │ 2002 │ 69092985 │ True │
├──────┼────────────────────────────┼──────────┼────────┼─────────────────┼──────────┤
│ 56 │ Ross-Wilkerson │ return │ 2008 │ 28058472 │ False │
├──────┼────────────────────────────┼──────────┼────────┼─────────────────┼──────────┤
│ 60 │ Marsh-Vazquez │ recently │ 1972 │ 78964785 │ False │
├──────┼────────────────────────────┼──────────┼────────┼─────────────────┼──────────┤
│ 65 │ Smith-Swanson │ site │ 2005 │ 27040331 │ False │
├──────┼────────────────────────────┼──────────┼────────┼─────────────────┼──────────┤
│ 78 │ Walter-White │ budget │ 1985 │ 28449713 │ False │
╘══════╧════════════════════════════╧══════════╧════════╧═════════════════╧══════════╛
Using the bike ID's, which bike would you like to report stolen?: 60
What date was the bike stolen(MM-DD-YYYY)?: 05-22-2023
What city was the bike stolen in?: Denver
What state was the bike stolen in?: CO
What ZIP code was the bike stolen in?: 80237
│ 60 │ Marsh-Vazquez │ recently │ 1972 │ 78964785 │ True
Searching For Stolen Bikes
The search_stolen_bikes function enables users to search the stolen_bikes table based on different criteria such as city, state, and zip_code. It pulls data from multiple tables using the relationships established in the classes above.
@click.command()
@click.option(
"--action",
prompt="Would you like to view all bikes or search by city, state, or ZIP code?",
type=click.Choice(["all", "city", "state", "zip_code"]),
)
def search_stolen_bikes(action):
if action == "all":
stolen_bikes = session.query(StolenBike).all()
if stolen_bikes:
table_data = [
(
stolen_bike.bike.id,
stolen_bike.date_stolen,
stolen_bike.bike.brand,
stolen_bike.bike.model,
stolen_bike.bike.year,
stolen_bike.city,
stolen_bike.state,
stolen_bike.zip_code,
)
for stolen_bike in stolen_bikes
]
headers = [
"ID",
"Date Stolen",
"Brand",
"Model",
"Year",
"City",
"State",
"ZIP Code",
]
table = tabulate.tabulate(table_data, headers, tablefmt="fancy_grid")
click.echo(f"\nStolen Bikes:\n")
click.echo(
click.style("\n" + table + "\n", fg="green", bg="black", bold=True)
)
main_menu()
else:
click.echo("No stolen bikes found.")
main_menu()
elif action == "city":
city = click.prompt("What city would you like to search for?")
stolen_bikes = session.query(StolenBike).filter_by(city=city).all()
if stolen_bikes:
table_data = [
(
stolen_bike.bike.id,
stolen_bike.date_stolen,
stolen_bike.bike.brand,
stolen_bike.bike.model,
stolen_bike.bike.year,
stolen_bike.bike.serial_number,
stolen_bike.city,
stolen_bike.state,
stolen_bike.zip_code,
)
for stolen_bike in stolen_bikes
]
headers = [
"ID",
"Date Stolen",
"Brand",
"Model",
"Year",
"Serial Number",
"City",
"State",
"ZIP Code",
]
table = tabulate.tabulate(table_data, headers, tablefmt="fancy_grid")
click.echo(f"\nStolen Bikes:\n")
click.echo(
click.style("\n" + table + "\n", fg="green", bg="black", bold=True)
)
main_menu()
else:
click.echo(f"No stolen bikes found in {city}.")
main_menu()
elif action == "state":
state = click.prompt("What state would you like to search for?")
stolen_bikes = session.query(StolenBike).filter_by(state=state).all()
if stolen_bikes:
table_data = [
(
stolen_bike.bike.id,
stolen_bike.date_stolen,
stolen_bike.bike.brand,
stolen_bike.bike.model,
stolen_bike.bike.year,
stolen_bike.bike.serial_number,
stolen_bike.city,
stolen_bike.state,
stolen_bike.zip_code,
)
for stolen_bike in stolen_bikes
]
headers = [
"ID",
"Date Stolen",
"Brand",
"Model",
"Year",
"Serial Number",
"City",
"State",
"ZIP Code",
]
table = tabulate.tabulate(table_data, headers, tablefmt="fancy_grid")
click.echo(f"\nStolen Bikes:\n")
click.echo(
click.style("\n" + table + "\n", fg="green", bg="black", bold=True)
)
main_menu()
else:
click.echo(f"No stolen bikes found in {state}.")
main_menu()
elif action == "zip_code":
zip_code = click.prompt("What ZIP code would you like to search for?")
stolen_bikes = session.query(StolenBike).filter_by(zip_code=zip_code).all()
if stolen_bikes:
table_data = [
(
stolen_bike.bike.id,
stolen_bike.date_stolen,
stolen_bike.bike.brand,
stolen_bike.bike.model,
stolen_bike.bike.year,
stolen_bike.bike.serial_number,
stolen_bike.city,
stolen_bike.state,
stolen_bike.zip_code,
)
for stolen_bike in stolen_bikes
]
headers = [
"ID",
"Date Stolen",
"Brand",
"Model",
"Year",
"Serial Number",
"City",
"State",
"ZIP Code",
]
table = tabulate.tabulate(table_data, headers, tablefmt="fancy_grid")
click.echo(f"\nStolen Bikes:\n")
click.echo(
click.style("\n" + table + "\n", fg="green", bg="black", bold=True)
)
main_menu()
else:
click.echo(f"No stolen bikes found in {zip_code}.")
main_menu()
Would you like to view your profile, register a new bike, remove a bike from your profile, update one of your bikes, report one of your bikes stolen or search our stolen database (view, register, remove, update, report, search): search
Would you like to view all bikes or search by city, state, or ZIP code? (all, city, state, zip_code): state
What state would you like to search for?: CO
Stolen Bikes:
╒══════╤═════════════════════╤════════════════════════╤════════════╤════════╤═════════════════╤═════════════════╤═════════╤════════════╕
│ ID │ Date Stolen │ Brand │ Model │ Year │ Serial Number │ City │ State │ ZIP Code │
╞══════╪═════════════════════╪════════════════════════╪════════════╪════════╪═════════════════╪═════════════════╪═════════╪════════════╡
│ 23 │ 12-31-1995 │ Carr, Nolan and Snyder │ management │ 1984 │ 79114073 │ Nashville │ CO │ 81078 │
├──────┼─────────────────────┼────────────────────────┼────────────┼────────┼─────────────────┼─────────────────┼─────────┼────────────┤
│ 43 │ 05-11-2010 │ Wilcox-Alexander │ treat │ 2019 │ 15076632 │ Stricklandmouth │ CO │ 80580 │
├──────┼─────────────────────┼────────────────────────┼────────────┼────────┼─────────────────┼─────────────────┼─────────┼────────────┤
│ 44 │ 01-24-1980 │ Day-Reynolds │ that │ 1981 │ 34812471 │ South Jaredton │ CO │ 81517 │
├──────┼─────────────────────┼────────────────────────┼────────────┼────────┼─────────────────┼─────────────────┼─────────┼────────────┤
│ 75 │ 2023-05-21 00:00:00 │ Hood Inc │ specific │ 1971 │ 93030168 │ Denver │ CO │ 80237 │
├──────┼─────────────────────┼────────────────────────┼────────────┼────────┼─────────────────┼─────────────────┼─────────┼────────────┤
│ 81 │ 2023-05-20 00:00:00 │ Park-Houston │ trx │ 1970 │ 29602575 │ Denver │ CO │ 80237 │
├──────┼─────────────────────┼────────────────────────┼────────────┼────────┼─────────────────┼─────────────────┼─────────┼────────────┤
│ 60 │ 2023-05-22 00:00:00 │ Marsh-Vazquez │ recently │ 1972 │ 78964785 │ Denver │ CO │ 80237 │
╘══════╧═════════════════════╧════════════════════════╧════════════╧════════╧═════════════════╧═════════════════╧═════════╧════════════╛
Using Python Click
You may have noticed a lot of the functions in the CLI are decorated in @click.command() and @click.option() functions.
Python Click is a popular library for creating command-line interfaces (CLIs) in Python. It provides a simple and elegant way to define and organize CLI commands, options, and arguments. Let's discuss some of the benefits and potential drawbacks of using Python Click in the development of our bike database CLI app.
Benefits of Python Click
Simplicity: Python Click offers a straightforward and intuitive syntax for defining CLI commands, making it easy to understand and use. It follows the principle of "convention over configuration," which means it provides sensible defaults and handles much of the CLI plumbing, allowing developers to focus on writing the actual functionality.
Command Organization: Click allows you to structure your CLI app into logical commands and subcommands, providing a clear and organized structure. This makes it easier for users to navigate and discover available functionality, especially when dealing with complex applications with multiple commands and options.
Automatic Help Generation: Click automatically generates help messages and usage instructions for your CLI commands based on their definitions. This eliminates the need for manually documenting each command and provides users with a convenient way to explore available options and arguments.
Option Parsing and Validation: Click simplifies the process of parsing command-line options and arguments, handling conversions, and providing built-in validation mechanisms. It supports a wide range of option types, including flags, strings, integers, floats, and more. This streamlines the development process and reduces the likelihood of errors.
Integration with Python Ecosystem: Python Click seamlessly integrates with other Python libraries and frameworks, making it a versatile choice for CLI development. It works well with popular libraries such as argparse, docopt, and Flask, allowing you to reuse code and integrate with existing projects effortlessly.
Drawbacks of Python Click
Learning Curve: While Click strives to provide a simple and intuitive API, there is still a learning curve involved in understanding its concepts and best practices. If you are new to Click or CLI development in general, you may need to spend some time familiarizing yourself with the library and its documentation.
Additional Dependency: Python Click is an external dependency that needs to be installed alongside your project. If you prefer to keep your project's dependencies minimal, adding another library might be seen as a downside. However, Click has become a popular choice in the Python community, and its benefits often outweigh this consideration.
Customization Limitations: Click offers a wide range of functionality out-of-the-box, but there might be cases where you require custom behavior that is not readily available. While Click is highly extensible, and you can subclass and customize its components, there could be scenarios where you may need to work around the library's constraints.
Despite these potential drawbacks, Python Click provides a powerful and flexible framework for building command-line interfaces in Python. Its simplicity, organization capabilities, and integration with the Python ecosystem make it a popular choice among developers.
By leveraging Python Click in the development of our bike database CLI app, we were able to easily define and structure commands, handle input parsing and validation, and generate helpful usage instructions. This resulted in a user-friendly and efficient CLI tool for managing bike information.
Conclusion
In this blog post, we explored the development of a Python CLI app for managing a bike database. We discussed the setup and dependencies, as well as the app's key features such as displaying, removing, updating, and reporting stolen bikes.
Top comments (1)
Excellent post