Career Paths
The Database Manager
Learn the fundamental principles of relational databases and SQL by creating an inventory management program with Python's built-in sqlite3 library.
Our Project: Inventory Management Application
A command-line program that allows the user to add, view, update, and delete products in an SQLite database, learning the basic CRUD (Create, Read, Update, Delete) operations.
Core Technologies We'll Use:
Step 1: Introduction to Databases and SQLite
We understand what a relational database is, why it is useful, and why SQLite is the ideal starting point for a Python developer.
What is a Database?
Think of a database as a highly organized collection of electronic "filing cabinets" (tables). Each table contains data on a specific topic (e.g., "Products", "Customers") and is structured in rows and columns, just like an Excel spreadsheet. Their power lies in allowing us to store, retrieve, and manage huge amounts of data quickly and reliably.
Why SQLite?
SQLite is a database engine that is built into Python's Standard Library (via the sqlite3 module). This means that:
- No installation required: You don't need a separate server program (like MySQL or PostgreSQL).
- Serverless: The database is not a separate server, but a simple file on your computer (e.g.,
inventory.db). - Ideal for Learning: It's the perfect way to learn the basic principles of databases and the SQL language without the complexity of setting up a full server.
Project Setup
No installation is needed! Just create a new Python file, for example inventory_manager.py. We'll start by writing the code to connect to the database.
# inventory_manager.py
import sqlite3
def connect_to_db(db_name="inventory.db"):
"""
Connects to the SQLite database.
If the file doesn't exist, it's created automatically.
Returns the connection object.
"""
try:
conn = sqlite3.connect(db_name)
print(f"Successfully connected to database '{db_name}'")
return conn
except sqlite3.Error as e:
print(f"Database connection error: {e}")
return None
if __name__ == "__main__":
connection = connect_to_db()
if connection:
# We'll close the connection for now.
connection.close()
print("The connection was closed.")
Step 2: Creating a Table with SQL
We learn the basic SQL CREATE TABLE command and how to execute it via Python to define the structure of our products table.
After connecting, the next step is to define our data structure. This is done by creating a table. We will create a table named products with the following columns:
id: A unique, auto-incrementing number for each product. This is the Primary Key.name: The product name (Text). It must be unique (UNIQUE).quantity: The available quantity (Integer).price: The product price (Decimal -REALin SQL).
To execute SQL commands, we use a cursor object from our connection. However, for simple commands, Python allows us to execute them directly from the connection object. conn.commit() permanently saves the changes.
Using with conn: is an improved practice that automatically handles commit (if the transaction is successful) or rollback (if an error occurs), making our code safer and cleaner.
# (In the same file, inventory_manager.py)
def create_table(conn):
"""Creates the products table if it doesn't already exist."""
try:
# Using 'with conn' ensures automatic commit or rollback.
with conn:
conn.execute("""
CREATE TABLE IF NOT EXISTS products (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL UNIQUE,
quantity INTEGER NOT NULL,
price REAL NOT NULL
)
""")
print("The 'products' table is ready.")
except sqlite3.Error as e:
print(f"Error creating table: {e}")
# Update the main block to call the new function
if __name__ == "__main__":
connection = connect_to_db()
if connection:
create_table(connection)
connection.close()
Step 3: Inserting Data (INSERT)
We explore the SQL INSERT command to add new products to our database, emphasizing the secure use of parameters.
Now that we have the table, we need a way to add data to it. This is done with the SQL INSERT INTO command.
Safety First: Parameterized Queries
It is extremely important to never embed values directly into the SQL command string (e.g., with f-strings). This creates a security vulnerability known as SQL Injection. Instead, we use placeholders (question marks ?) in our command and pass the values as a second argument to cursor.execute(). The sqlite3 library will take care of safely "sanitizing" the values.
# (In the same file, inventory_manager.py)
def add_product(conn, name, quantity, price):
"""Adds a new product to the products table."""
sql = "INSERT INTO products (name, quantity, price) VALUES (?, ?, ?)"
try:
with conn:
conn.execute(sql, (name, quantity, price))
print(f"Successfully added product: {name}")
except sqlite3.IntegrityError:
# This error is raised if we try to add a product with a name that already exists (due to the UNIQUE constraint)
print(f"Error: Product '{name}' already exists in the database.")
except sqlite3.Error as e:
print(f"An SQL error occurred: {e}")
# Update the main block to test adding products
if __name__ == "__main__":
connection = connect_to_db()
if connection:
create_table(connection)
print("\nAdding products...")
add_product(connection, "Laptop", 10, 950.50)
add_product(connection, "Mouse", 100, 25.00)
add_product(connection, "Keyboard", 50, 45.99)
add_product(connection, "Laptop", 5, 999.00) # This will cause an IntegrityError
connection.close()
Step 4: Retrieving Data (SELECT)
We use the SELECT command to read data from our database and display it in a nicely formatted way.
The most common operation in a database is data retrieval. This is done with the SELECT command. We can select specific columns or all of them (with *), filter rows with the WHERE clause, and sort them with ORDER BY.
After executing a SELECT, the cursor contains the results. We can get them all at once with the .fetchall() method, which returns them as a list of tuples.
# (In the same file, inventory_manager.py)
def view_products(conn):
"""Displays all products from the table, sorted by name."""
try:
cursor = conn.cursor()
cursor.execute("SELECT id, name, quantity, price FROM products ORDER BY name")
products = cursor.fetchall()
if not products:
print("The inventory is empty.")
return
print("\n--- Product Inventory ---")
print(f"{'ID':<5}{'Name':<20}{'Quantity':<10}{'Price':<10}")
print("-" * 45)
for product in products:
# product is a tuple, e.g., (1, 'Laptop', 10, 950.5)
print(f"{product[0]:<5}{product[1]:<20}{product[2]:<10}{product[3]:.2f}€")
print("-" * 45)
except sqlite3.Error as e:
print(f"Data retrieval error: {e}")
# Update the main block to view the products
if __name__ == "__main__":
connection = connect_to_db()
if connection:
create_table(connection)
# Clear the table for a consistent starting state
with connection: connection.execute("DELETE FROM products")
add_product(connection, "Laptop", 10, 950.50)
add_product(connection, "Mouse", 100, 25.00)
add_product(connection, "Keyboard", 50, 45.99)
view_products(connection)
connection.close()
Step 5: Updating and Deleting Data (UPDATE, DELETE)
We learn the UPDATE and DELETE commands to modify existing records and remove products from our inventory.
The last two basic operations are update (UPDATE) and delete (DELETE). Both use a WHERE clause to specify exactly which rows of the table will be affected. It is crucial to always use the WHERE clause, otherwise the command will be applied to ALL rows of the table!
After executing an UPDATE or DELETE command, we can check the cursor.rowcount attribute to see how many rows were affected. This is useful to know if our operation was successful (e.g., if the product we wanted to update was found).
# (In the same file, inventory_manager.py)
def update_product_quantity(conn, name, new_quantity):
"""Updates the quantity of a product based on its name."""
sql = "UPDATE products SET quantity = ? WHERE name = ?"
try:
with conn:
cursor = conn.cursor()
cursor.execute(sql, (new_quantity, name))
if cursor.rowcount == 0:
print(f"Error: Product with name '{name}' not found.")
else:
print(f"The quantity of product '{name}' was updated to {new_quantity}.")
except sqlite3.Error as e:
print(f"Update error: {e}")
def delete_product(conn, name):
"""Deletes a product based on its name."""
sql = "DELETE FROM products WHERE name = ?"
try:
with conn:
cursor = conn.cursor()
cursor.execute(sql, (name,))
if cursor.rowcount == 0:
print(f"Error: Product with name '{name}' not found for deletion.")
else:
print(f"Product '{name}' was successfully deleted.")
except sqlite3.Error as e:
print(f"Deletion error: {e}")
# Update the main block to test the new functions
if __name__ == "__main__":
connection = connect_to_db()
if connection:
create_table(connection)
with connection: connection.execute("DELETE FROM products")
add_product(connection, "Laptop", 10, 950.50)
add_product(connection, "Mouse", 100, 25.00)
view_products(connection) # View before changes
print("\n--- Updating & Deleting ---")
update_product_quantity(connection, "Laptop", 8)
delete_product(connection, "Mouse")
delete_product(connection, "Monitor") # Test deleting a non-existent product
view_products(connection) # View after changes
connection.close()
Step 6: Complete Application with a User Menu
We combine all our functions into an interactive command-line menu, allowing the user to choose which action to perform.
Now we will combine all the functions we have created into a complete, interactive program. We will create a main_menu function that will run in an infinite loop (while True). In each iteration, it will display the available options to the user, read their choice with input(), and call the corresponding function. There will also be an exit option, which will use the break statement to terminate the loop.
This structure is very common for command-line applications and is the final step in creating our project.
# (Replace the old if __name__ == "__main__": block with this)
def main_menu():
"""Displays the main menu and handles user choices."""
conn = connect_to_db()
create_table(conn)
while True:
print("\n--- Inventory Management Main Menu ---")
print("1. View all products")
print("2. Add a new product")
print("3. Update product quantity")
print("4. Delete a product")
print("5. Exit")
choice = input("Select an action (1-5): ")
if choice == '1':
view_products(conn)
elif choice == '2':
try:
name = input("Enter product name: ")
quantity = int(input("Enter quantity: "))
price = float(input("Enter price: "))
add_product(conn, name, quantity, price)
except ValueError:
print("Error: Invalid input for quantity or price.")
elif choice == '3':
try:
name = input("Enter product name to update: ")
quantity = int(input("Enter new quantity: "))
update_product_quantity(conn, name, quantity)
except ValueError:
print("Error: Quantity must be a number.")
elif choice == '4':
name = input("Enter product name to delete: ")
delete_product(conn, name)
elif choice == '5':
print("Exiting program. Goodbye!")
break
else:
print("Invalid choice, please try again.")
conn.close()
if __name__ == "__main__":
main_menu()
Tip!
python inventory_manager.py in your terminal. You now have a fully functional CRUD application that interacts with a database!Project Completion & Next Steps
Congratulations! You have completed the path and now have the full code for the project.
This is the final, complete code for the application. You can copy it, run it locally on your computer (after installing the necessary libraries with `pip`), and experiment by adding your own features!
# inventory_manager.py
import sqlite3
def connect_to_db(db_name="inventory.db"):
"""Συνδέεται στη βάση δεδομένων και επιστρέφει το αντικείμενο σύνδεσης."""
conn = sqlite3.connect(db_name)
return conn
def create_table(conn):
"""Δημιουργεί τον πίνακα products αν δεν υπάρχει."""
with conn:
conn.execute("""
CREATE TABLE IF NOT EXISTS products (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL UNIQUE,
quantity INTEGER NOT NULL,
price REAL NOT NULL
)
""")
print("Ο πίνακας 'products' είναι έτοιμος.")
def add_product(conn, name, quantity, price):
"""Προσθέτει ένα νέο προϊόν στη βάση δεδομένων."""
try:
with conn:
conn.execute(
"INSERT INTO products (name, quantity, price) VALUES (?, ?, ?)",
(name, quantity, price)
)
print(f"Επιτυχής προσθήκη του προϊόντος: {name}")
except sqlite3.IntegrityError:
print(f"Σφάλμα: Το προϊόν '{name}' υπάρχει ήδη στη βάση δεδομένων.")
except Exception as e:
print(f"Προέκυψε σφάλμα: {e}")
def view_products(conn):
"""Εμφανίζει όλα τα προϊόντα από τη βάση δεδομένων."""
cursor = conn.cursor()
cursor.execute("SELECT id, name, quantity, price FROM products ORDER BY name")
products = cursor.fetchall()
if not products:
print("Η αποθήκη είναι άδεια.")
return
print("\n--- Απόθεμα Προϊόντων ---")
print(f"{'ID':<5}{'Όνομα':<20}{'Ποσότητα':<10}{'Τιμή':<10}")
print("-" * 45)
for product in products:
print(f"{product[0]:<5}{product[1]:<20}{product[2]:<10}{product[3]:.2f}€")
print("-" * 45)
def update_product_quantity(conn, name, new_quantity):
"""Ενημερώνει την ποσότητα ενός υπάρχοντος προϊόντος."""
with conn:
cursor = conn.cursor()
cursor.execute("UPDATE products SET quantity = ? WHERE name = ?", (new_quantity, name))
if cursor.rowcount == 0:
print(f"Σφάλμα: Δεν βρέθηκε προϊόν με όνομα '{name}'.")
else:
print(f"Η ποσότητα του προϊόντος '{name}' ενημερώθηκε σε {new_quantity}.")
def delete_product(conn, name):
"""Διαγράφει ένα προϊόν από τη βάση δεδομένων."""
with conn:
cursor = conn.cursor()
cursor.execute("DELETE FROM products WHERE name = ?", (name,))
if cursor.rowcount == 0:
print(f"Σφάλμα: Δεν βρέθηκε προϊόν με όνομα '{name}' για διαγραφή.")
else:
print(f"Το προϊόν '{name}' διαγράφηκε επιτυχώς.")
def main_menu():
"""Εμφανίζει το κύριο μενού και διαχειρίζεται τις επιλογές του χρήστη."""
conn = connect_to_db()
create_table(conn)
while True:
print("\n--- Κύριο Μενού ---")
print("1. Εμφάνιση όλων των προϊόντων")
print("2. Προσθήκη νέου προϊόντος")
print("3. Ενημέρωση ποσότητας προϊόντος")
print("4. Διαγραφή προϊόντος")
print("5. Έξοδος")
choice = input("Επιλέξτε μια ενέργεια (1-5): ")
if choice == '1':
view_products(conn)
elif choice == '2':
name = input("Όνομα προϊόντος: ")
quantity = int(input("Ποσότητα: "))
price = float(input("Τιμή: "))
add_product(conn, name, quantity, price)
elif choice == '3':
name = input("Όνομα προϊόντος προς ενημέρωση: ")
quantity = int(input("Νέα ποσότητα: "))
update_product_quantity(conn, name, quantity)
elif choice == '4':
name = input("Όνομα προϊόντος προς διαγραφή: ")
delete_product(conn, name)
elif choice == '5':
print("Έξοδος από το πρόγραμμα.")
break
else:
print("Μη έγκυρη επιλογή, παρακαλώ προσπαθήστε ξανά.")
conn.close()
if __name__ == "__main__":
main_menu()