This guide will show you how to create a simple CRUD (Create, Read, Update, Delete) application using Python and SQLite. We’ll begin by inserting initial data and then use synonyms for common operations like add, create, delete, remove, update, modify, read, find, and query. This allows users to interact naturally while performing CRUD operations. The idea is you can add any synonyms you wish to test databases instead of writing out SQL commands.
Step 1: Create the Database, Table, and Insert Initial Data
First, we create the SQLite database and insert some initial product data. We’ll define columns for ProductID, ProductName, Quantity, PurchasePrice, SalePrice, and automatically calculate the Profit Margin and Unrealized Gain.
Code to Create Database, Table, and Insert Initial Data
import sqlite3
# Connect to SQLite database (or create it if it doesn't exist)
conn = sqlite3.connect('products.db')
cursor = conn.cursor()
# Create the products table
cursor.execute('''
CREATE TABLE IF NOT EXISTS products (
ProductID INTEGER PRIMARY KEY,
ProductName TEXT,
Quantity INTEGER,
PurchasePrice REAL,
SalePrice REAL,
ProfitMargin REAL,
UnrealizedGain REAL
)
''')
# Insert initial data
initial_data = [
(1, 'Logo Aluminum Car Decal', 20, 2.29, 10.99),
(2, 'Logo Black Colorway Fitted Hat', 50, 11.21, 44.99),
(3, 'Logo Vanilla Scented Air Freshener', 20, 0.26, 5.99),
(4, 'Logo Distressed T-Shirt', 50, 8.52, 21.99),
(5, 'Logo Athletic Socks', 30, 0.89, 5.99),
(6, 'Logo Plush Doll', 20, 2.28, 14.99),
(7, 'Logo Ceramic Mug', 25, 2.25, 14.99),
(8, 'Logo Enamel Pin', 70, 0.52, 6.99),
(9, 'Logo Backpack', 20, 18.82, 59.99),
(10, 'Logo Magnetic Bookmarks', 25, 1.15, 7.99)
]
for product in initial_data:
product_id, product_name, quantity, purchase_price, sale_price = product
profit_margin = sale_price - purchase_price
unrealized_gain = profit_margin * quantity
cursor.execute('''
INSERT INTO products (ProductID, ProductName, Quantity, PurchasePrice, SalePrice, ProfitMargin, UnrealizedGain)
VALUES (?, ?, ?, ?, ?, ?, ?)
''', (product_id, product_name, quantity, purchase_price, sale_price, profit_margin, unrealized_gain))
conn.commit()
print("Initial data added successfully!")This creates the products table and inserts initial data, calculating Profit Margin and Unrealized Gain for each product.
Step 2: CRUD Operations with Synonym Commands
Now, we’ll implement CRUD operations (Create, Read, Update, Delete) using various synonym commands. For instance, you can use add or create to insert new products, and read, query, or find to retrieve products with optional filtering.
Create New Product (Add/Create)
Users can create new products by typing either add or create followed by product details:
add "Logo Pencils", 20, 0.24, 1.99
This command inserts a new product with the calculated Profit Margin and Unrealized Gain.
def add_or_create_product(command):
# Parse the input command to get product details
parts = command.split(',')
product_name = parts[0].split(' ', 1)[1].strip().strip('"')
quantity = int(parts[1].strip())
purchase_price = float(parts[2].strip())
sale_price = float(parts[3].strip())
profit_margin = sale_price - purchase_price
unrealized_gain = profit_margin * quantity
cursor.execute('''
INSERT INTO products (ProductName, Quantity, PurchasePrice, SalePrice, ProfitMargin, UnrealizedGain)
VALUES (?, ?, ?, ?, ?, ?, ?)
''', (product_name, quantity, purchase_price, sale_price, profit_margin, unrealized_gain))
conn.commit()
print(f"Product '{product_name}' added/created successfully!")Read (Query/Find) Products with Optional Filters
Users can retrieve all products by typing read, query, or find. Additionally, they can add conditions to filter the results. For example:
query where ProductName = "Logo Pencils"
or
find where Quantity > 10
This will retrieve only the products that match the specified condition. If no condition is provided, it will return all products.
def read_or_query_products(command):
base_query = "SELECT * FROM products"
if "where" in command:
condition = command.split("where", 1)[1].strip()
query = f"{base_query} WHERE {condition}"
else:
query = base_query
cursor.execute(query)
rows = cursor.fetchall()
if rows:
for row in rows:
print(row)
else:
print("No products found with the given condition.")Update Product (Update/Modify/Change)
Users can update a product’s details by typing update, modify, or change. For example:
update 1 quantity = 30
or
modify 1 sale_price = 15.99
This updates the specified field for the product with ProductID = 1.
def update_product(command):
parts = command.split()
product_id = int(parts[1])
field = parts[2].split('=')[0].strip()
new_value = parts[2].split('=')[1].strip()
query = f"UPDATE products SET {field} = ? WHERE ProductID = ?"
cursor.execute(query, (new_value, product_id))
conn.commit()
print(f"Product with ID {product_id} updated successfully!")Delete Product (Delete/Erase/Remove)
To delete a product, users can type delete, erase, or remove. For example:
delete 1
or
remove 1
The program will delete the product with ProductID = 1 after asking for confirmation.
def delete_product(command):
product_id = int(command.split()[1])
confirmation = input(f"Are you sure you want to delete the product with ID {product_id}? (yes/no): ")
if confirmation.lower() == 'yes':
cursor.execute("DELETE FROM products WHERE ProductID = ?", (product_id,))
conn.commit()
print(f"Product with ID {product_id} deleted successfully!")
else:
print("Deletion cancelled.")Step 3: Bringing It All Together
The following loop listens for user commands and handles synonyms for each CRUD operation. It will allow users to query the database with optional WHERE conditions.
while True:
command = input("Enter command: ").lower()
# Synonyms for "add" and "create"
if command.startswith('add') or command.startswith('create'):
add_or_create_product(command)
# Synonyms for "read", "query", and "find"
elif command.startswith('read') or command.startswith('query') or command.startswith('find'):
read_or_query_products(command)
# Synonyms for "update", "modify", and "change"
elif command.startswith('update') or command.startswith('modify') or command.startswith('change'):
update_product(command)
# Synonyms for "delete", "erase", and "remove"
elif command.startswith('delete') or command.startswith('erase') or command.startswith('remove'):
delete_product(command)
# Exit the program
elif command == 'exit':
print("Exiting...")
breakFull Code:
import sqlite3
# Connect to SQLite database (or create it if it doesn't exist)
conn = sqlite3.connect('products.db')
cursor = conn.cursor()
# Create the products table
cursor.execute('''
CREATE TABLE IF NOT EXISTS products (
ProductID INTEGER PRIMARY KEY,
ProductName TEXT,
Quantity INTEGER,
PurchasePrice REAL,
SalePrice REAL,
ProfitMargin REAL,
UnrealizedGain REAL
)
''')
# Insert initial data
initial_data = [
(1, 'Logo Aluminum Car Decal', 20, 2.29, 10.99),
(2, 'Logo Black Colorway Fitted Hat', 50, 11.21, 44.99),
(3, 'Logo Vanilla Scented Air Freshener', 20, 0.26, 5.99),
(4, 'Logo Distressed T-Shirt', 50, 8.52, 21.99),
(5, 'Logo Athletic Socks', 30, 0.89, 5.99),
(6, 'Logo Plush Doll', 20, 2.28, 14.99),
(7, 'Logo Ceramic Mug', 25, 2.25, 14.99),
(8, 'Logo Enamel Pin', 70, 0.52, 6.99),
(9, 'Logo Backpack', 20, 18.82, 59.99),
(10, 'Logo Magnetic Bookmarks', 25, 1.15, 7.99)
]
# Insert initial data and calculate Profit Margin and Unrealized Gain
for product in initial_data:
product_id, product_name, quantity, purchase_price, sale_price = product
profit_margin = sale_price - purchase_price
unrealized_gain = profit_margin * quantity
cursor.execute('''
INSERT INTO products (ProductID, ProductName, Quantity, PurchasePrice, SalePrice, ProfitMargin, UnrealizedGain)
VALUES (?, ?, ?, ?, ?, ?, ?)
''', (product_id, product_name, quantity, purchase_price, sale_price, profit_margin, unrealized_gain))
conn.commit()
print("Initial data added successfully!")
# Function to add or create a product
def add_or_create_product(command):
# Parse the input command to get product details
parts = command.split(',')
product_name = parts[0].split(' ', 1)[1].strip().strip('"')
quantity = int(parts[1].strip())
purchase_price = float(parts[2].strip())
sale_price = float(parts[3].strip())
profit_margin = sale_price - purchase_price
unrealized_gain = profit_margin * quantity
cursor.execute('''
INSERT INTO products (ProductName, Quantity, PurchasePrice, SalePrice, ProfitMargin, UnrealizedGain)
VALUES (?, ?, ?, ?, ?, ?, ?)
''', (product_name, quantity, purchase_price, sale_price, profit_margin, unrealized_gain))
conn.commit()
print(f"Product '{product_name}' added/created successfully!")
# Function to read or query products with an optional WHERE clause
def read_or_query_products(command):
base_query = "SELECT * FROM products"
if "where" in command:
condition = command.split("where", 1)[1].strip()
query = f"{base_query} WHERE {condition}"
else:
query = base_query
cursor.execute(query)
rows = cursor.fetchall()
if rows:
for row in rows:
print(row)
else:
print("No products found with the given condition.")
# Function to update a product
def update_product(command):
parts = command.split()
product_id = int(parts[1])
field = parts[2].split('=')[0].strip()
new_value = parts[2].split('=')[1].strip()
query = f"UPDATE products SET {field} = ? WHERE ProductID = ?"
cursor.execute(query, (new_value, product_id))
conn.commit()
print(f"Product with ID {product_id} updated successfully!")
# Function to delete a product
def delete_product(command):
product_id = int(command.split()[1])
confirmation = input(f"Are you sure you want to delete the product with ID {product_id}? (yes/no): ")
if confirmation.lower() == 'yes':
cursor.execute("DELETE FROM products WHERE ProductID = ?", (product_id,))
conn.commit()
print(f"Product with ID {product_id} deleted successfully!")
else:
print("Deletion cancelled.")
# Main loop to listen for user commands and handle synonyms
while True:
command = input("Enter command: ").lower()
# Synonyms for "add" and "create"
if command.startswith('add') or command.startswith('create'):
add_or_create_product(command)
# Synonyms for "read", "query", and "find"
elif command.startswith('read') or command.startswith('query') or command.startswith('find'):
read_or_query_products(command)
# Synonyms for "update", "modify", and "change"
elif command.startswith('update') or command.startswith('modify') or command.startswith('change'):
update_product(command)
# Synonyms for "delete", "erase", and "remove"
elif command.startswith('delete') or command.startswith('erase') or command.startswith('remove'):
delete_product(command)
# Exit the program
elif command == 'exit':
print("Exiting...")
breakThank you reading this article. We hope you found it helpful and informative. If you have any questions, or if you would like to suggest new Python code examples or topics for future tutorials/articles, please feel free to join and comment. Your feedback and suggestions are always welcome!
You can find the same tutorial on Medium.com.
