Build a Powerful SQLite CLI App for Sales Generation
In the first part of this article, we explored Python’s basic syntax: variables, data types, loops, conditionals, functions, and working with collections like lists, tuples, and JSON. Now, it’s time to put that knowledge into practice by building something useful.
We’ll be creating a Sales Generator CLI (Command Line Interface) Application using Python and SQLite. This project will allow us to:
- Add a sale (store details like product, quantity, and price).
- View all sales (see the list of sales we’ve added).
- Update a sale (change details if we entered something wrong).
- Delete a sale (remove unwanted sales records).
- Generate a sales report (calculate total sales).
- Exit the application when we’re done.
By the end of this section, you’ll understand how to:
– Work with SQLite, a lightweight database that comes bundled with Python.
– Write functions that interact with a database.
– Use loops and conditionals to create a menu-driven CLI app.
Why SQLite in Python?
SQLite is a database engine that comes pre-installed with Python. Unlike larger databases like MySQL or PostgreSQL, it doesn’t require setting up a server. Instead, it stores data in a single file on your computer, making it perfect for small projects and learning.
Project Setup
Before we dive into coding, let’s set up our project folder:
- Create a new folder called sales_generator.
- Inside it, create a file named app.py. This will contain our application code.
- We’ll also create a database file automatically when we run the app for the first time.
Your folder should look like this:
sales_generator/
└── app.py
Step 1: Setting Up the Database
Before we can add or view sales, we need a place to store our data. That’s where SQLite comes in.
Python provides a built-in library called sqlite3, which we’ll use to:
- Connect to a database file (if it doesn’t exist, it will be created).
- Create a sales table with columns for ID, product, quantity, and price.
Code: Database Initialization
Inside your app.py file, write the following code:
import sqlite3
# Function to initialize the database and create the table if it does not exist
def init_db():
# Connect to SQLite database (creates file if it doesn’t exist)
conn = sqlite3.connect(“sales.db”)
cursor = conn.cursor()
# Create table
cursor.execute(”’
CREATE TABLE IF NOT EXISTS sales (
id INTEGER PRIMARY KEY AUTOINCREMENT,
product TEXT NOT NULL,
quantity INTEGER NOT NULL,
price REAL NOT NULL
)
”’)
# Save changes and close connection
conn.commit()
conn.close()
# Run this function once at start
if __name__ == “__main__”:
init_db()
print(“Database initialized successfully!”)
Explanation
- sqlite3.connect(“sales.db”) → Connects to a database file named sales.db. If it doesn’t exist, Python creates it automatically.
- cursor.execute(…) → Executes SQL commands (in this case, creating a table).
- CREATE TABLE IF NOT EXISTS → Makes sure we don’t accidentally recreate the table if it already exists.
- commit() → Saves changes.
- close() → Closes the connection to avoid memory leaks.
Try it Yourself
- Save the file and run:
python app.py
- You should see:
Database initialized successfully!
3. A new file sales.db will appear in your project folder. This is your SQLite database file.
Step 2: Add a Sale
Now that we have a database with a sales table, we can start adding new sales records. A sale will have:
- Product name (string)
- Quantity (integer)
- Price (float/decimal)
We’ll ask the user to enter these details, then store them in the database.
Code: Add Sale Function
Add this function to your app.py (below init_db()):
def add_sale():
# Take input from user
product = input(“Enter product name: “)
quantity = int(input(“Enter quantity: “))
price = float(input(“Enter price: “))
# Connect to database
conn = sqlite3.connect(“sales.db”)
cursor = conn.cursor()
# Insert data into sales table
cursor.execute(
“INSERT INTO sales (product, quantity, price) VALUES (?, ?, ?)”,
(product, quantity, price)
)
# Save changes and close
conn.commit()
conn.close()
print(“✅ Sale added successfully!”)
How to use (?) placeholder in SQL Query?
Instead of directly inserting variables into the SQL string, we use placeholders (?). This prevents SQL injection attacks and makes our code safer.
For example, in Python’s sqlite3:
import sqlite3
conn = sqlite3.connect(“sales.db”)
cursor = conn.cursor()
#Using ? placeholder
cursor.execute(“INSERT INTO sales (product, quantity, price) VALUES (?, ?, ?)”,
(“Laptop”, 3, 1800.75))
conn.commit()
conn.close()
Updating Main Program Flow
Now, let’s add a simple menu so we can call add_sale() when the user selects the option.
Update the bottom of your file:
if __name__ == “__main__”:
init_db()
while True:
print(“\n— Sales Generator CLI —“)
print(“1. Add a Sale”)
print(“6. Exit”)
choice = input(“Enter your choice: “)
if choice == “1”:
add_sale()
elif choice == “6”:
print(“Exiting… Goodbye!”)
break
else:
print(“❌ Invalid choice, please try again.”)
Try it Yourself
- Run your program again:
python app.py
- You should see the menu:
— Sales Generator CLI —
1. Add a Sale
6. Exit
- If you select 1, the app will ask for product, quantity, and price, then save it to the database.
- You can exit with 6.
Step 3: View Sales
This feature will:
- Connect to the sales.db database.
- Fetch all records from the sales table.
- Display them in a nice format.
Code: View Sales Function
Add this function to your app.py:
def view_sales():
# Connect to database
conn = sqlite3.connect(“sales.db”)
cursor = conn.cursor()
# Fetch all sales
cursor.execute(“SELECT * FROM sales”)
sales = cursor.fetchall()
conn.close()
if sales:
print(“\n— Sales Records —“)
for sale in sales:
print(f”ID: {sale[0]} | Product: {sale[1]} | Quantity: {sale[2]} | Price: {sale[3]}”)
else:
print(“\n⚠️ No sales found.”)
Updating the Menu
Now update the loop at the bottom of your file to include this new option: view sales function
if __name__ == “__main__”:
init_db()
while True:
print(“\n— Sales Generator CLI —“)
print(“1. Add a Sale”)
print(“2. View Sales”)
print(“6. Exit”)
choice = input(“Enter your choice: “)
if choice == “1”:
add_sale()
elif choice == “2”:
view_sales()
elif choice == “6”:
print(“Exiting… Goodbye!”)
break
else:
print(“❌ Invalid choice, please try again.”)
Try it Yourself
- Run the app:
python app.py
- Add a few sales (choose option 1).
- Then select option 2 (View Sales) → You should see something like:
— Sales Records —
ID: 1 | Product: Apple | Quantity: 10 | Price: 5.5
ID: 2 | Product: Banana | Quantity: 20 | Price: 2.0
Step 4: Update a Sale
Sometimes, we might enter the wrong product name, quantity, or price. Instead of deleting and re-adding, we’ll allow the user to update an existing record.
The flow will be:
- Show existing sales (so the user knows the ID).
- Ask for the ID of the sale to update.
- Ask for new values (product, quantity, price).
- Update the record in the database.
Code: Update Sale Function
Add this function to your app.py:
def update_sale():
view_sales() # Show current sales so user can pick an ID
try:
sale_id = int(input(“\nEnter the ID of the sale to update: “))
new_product = input(“Enter new product name: “)
new_quantity = int(input(“Enter new quantity: “))
new_price = float(input(“Enter new price: “))
conn = sqlite3.connect(“sales.db”)
cursor = conn.cursor()
cursor.execute(
“UPDATE sales SET product = ?, quantity = ?, price = ? WHERE id = ?”,
(new_product, new_quantity, new_price, sale_id)
)
if cursor.rowcount == 0:
print(“❌ No sale found with that ID.”)
else:
print(“✅ Sale updated successfully!”)
conn.commit()
conn.close()
except ValueError:
print(“⚠️ Invalid input. Please enter correct values.”)
Updating the Menu
Add an option for Update Sale:
if __name__ == “__main__”:
init_db()
while True:
print(“\n— Sales Generator CLI —“)
print(“1. Add a Sale”)
print(“2. View Sales”)
print(“3. Update a Sale”)
print(“6. Exit”)
choice = input(“Enter your choice: “)
if choice == “1”:
add_sale()
elif choice == “2”:
view_sales()
elif choice == “3”:
update_sale()
elif choice == “6”:
print(“Exiting… Goodbye!”)
break
else:
print(“❌ Invalid choice, please try again.”)
Try it Yourself
- Run the program:
python app.py
- Add a couple of sales (option 1).
- View sales (option 2) and note an ID.
- Update that sale using option 3.
- View sales again → You should see the updated record.
Step 5: Delete a Sale
If a sale was added by mistake, we need a way to remove it. The flow will be:
- Show existing sales (so the user can see IDs).
- Ask for the ID of the sale to delete.
- Delete that record from the database.
Code: Delete Sale Function
Add this function to your app.py:
def delete_sale():
view_sales() # Show current sales so user can pick an ID
try:
sale_id = int(input(“\nEnter the ID of the sale to delete: “))
conn = sqlite3.connect(“sales.db”)
cursor = conn.cursor()
cursor.execute(“DELETE FROM sales WHERE id = ?”, (sale_id,))
if cursor.rowcount == 0:
print(“❌ No sale found with that ID.”)
else:
print(“✅ Sale deleted successfully!”)
conn.commit()
conn.close()
except ValueError:
print(“⚠️ Invalid input. Please enter a valid ID.”)
Updating the Menu
Now add Delete a Sale as option 4:
if __name__ == “__main__”:
init_db()
while True:
print(“\n— Sales Generator CLI —“)
print(“1. Add a Sale”)
print(“2. View Sales”)
print(“3. Update a Sale”)
print(“4. Delete a Sale”)
print(“6. Exit”)
choice = input(“Enter your choice: “)
if choice == “1”:
add_sale()
elif choice == “2”:
view_sales()
elif choice == “3”:
update_sale()
elif choice == “4”:
delete_sale()
elif choice == “6”:
print(“Exiting… Goodbye!”)
break
else:
print(“❌ Invalid choice, please try again.”)
Try it Yourself
- Run the program.
- Add a few sales (option 1).
- View them (option 2) to check IDs.
- Delete one sale using option 4.
- View sales again → That sale should be gone.
Step 6: Sales Report
A report will help us see:
- Total number of sales
- Total items sold (sum of all quantities)
- Total revenue (sum of quantity × price)
Code: Sales Report Function
- Add this function to your app.py:
def sales_report():
conn = sqlite3.connect(“sales.db”)
cursor = conn.cursor()
# Get the total number of sales
cursor.execute(“SELECT COUNT(*) FROM sales”)
total_sales = cursor.fetchone()[0]
# Get total items sold
cursor.execute(“SELECT SUM(quantity) FROM sales”)
total_items = cursor.fetchone()[0] or 0 # handle None if no sales
# Get total revenue
cursor.execute(“SELECT SUM(quantity * price) FROM sales”)
total_revenue = cursor.fetchone()[0] or 0.0
conn.close()
print(“\n— Sales Report —“)
print(f” Total Sales Records: {total_sales}”)
print(f” Total Items Sold: {total_items}”)
print(f” Total Revenue: {total_revenue:.2f}”)
Updating the Menu
Add Sales Report as option 5:
if __name__ == “__main__”:
init_db()
while True:
print(“\n— Sales Generator CLI —“)
print(“1. Add a Sale”)
print(“2. View Sales”)
print(“3. Update a Sale”)
print(“4. Delete a Sale”)
print(“5. Sales Report”)
print(“6. Exit”)
choice = input(“Enter your choice: “)
if choice == “1”:
add_sale()
elif choice == “2”:
view_sales()
elif choice == “3”:
update_sale()
elif choice == “4”:
delete_sale()
elif choice == “5”:
sales_report()
elif choice == “6”:
print(“Exiting… Goodbye!”)
break
else:
print(“❌Invalid choice, please try again.”)
Try it Yourself
Run the app.
- Add a few sales (example: Apple → qty 20, price 5.5).
- Add another (Banana → qty 20, price 2.0).
- Select option 6 (Sales Report) → You should see something like:
— Sales Report —
- Total Sales Records: 2
- Total Items Sold: 40
- Total Revenue: 150.00
Now we have a fully functional CLI app with:
- Add Sale
- View Sales
- Update Sale
- Delete Sale
- Sales Report
- Exit
About Author
Gurnav
I am Gurnav Chaudhary, a passionate full stack developer who loves building real-world applications and sharing knowledge through writing. I work with technologies like React, Node.js, MongoDB, Express, and Python, creating projects such as e-commerce platforms, CRM dashboards, and healthcare tools. I enjoy solving problems with clean, scalable code and exploring modern frameworks to improve my craft. Alongside development, I contribute to open-source projects and constantly learn new skills. Through my blogs, I aim to simplify Python and full stack development, helping beginners and professionals grow in their coding journey with practical insights and real examples. My profile on Github.