Let’s create a Python tutorial for a simple customer management project using an SQL database. We’ll use SQLite for our database, which is lightweight and easy to set up.
Here’s a step-by-step guide:
Step 1: Setting up the Database
First, let’s design the database schema. We’ll create a table called customers to store customer information. Each customer will have an id, name, email, and phone_number
CREATE TABLE customers (
id INTEGER PRIMARY KEY,
name TEXT,
email TEXT,
phone_number TEXT
);
Step 2: Python Code to Interact with the Database
We’ll use the sqlite3 module in Python to interact with the SQLite database. Here’s a Python script to create a connection to the database and perform basic CRUD (Create, Read, Update, Delete) operations on the customers table.
import sqlite3
# Function to create a connection to the database
def create_connection(db_file):
conn = None
try:
conn = sqlite3.connect(db_file)
print(f"Connected to database: {db_file}")
return conn
except sqlite3.Error as e:
print(e)
return conn
# Function to create a new customer
def create_customer(conn, customer):
sql = ''' INSERT INTO customers(name,email,phone_number)
VALUES(?,?,?) '''
cur = conn.cursor()
cur.execute(sql, customer)
conn.commit()
return cur.lastrowid
# Function to retrieve all customers
def get_all_customers(conn):
cur = conn.cursor()
cur.execute("SELECT * FROM customers")
rows = cur.fetchall()
return rows
# Function to update customer details
def update_customer(conn, customer):
sql = ''' UPDATE customers
SET name = ?, email = ?, phone_number = ?
WHERE id = ?'''
cur = conn.cursor()
cur.execute(sql, customer)
conn.commit()
# Function to delete a customer
def delete_customer(conn, id):
sql = 'DELETE FROM customers WHERE id=?'
cur = conn.cursor()
cur.execute(sql, (id,))
conn.commit()
def main():
database = "customer_management.db"
conn = create_connection(database)
# Example usage
with conn:
# Create a new customer
customer = ('John Doe', 'john@example.com', '1234567890')
customer_id = create_customer(conn, customer)
print(f'New customer ID: {customer_id}')
# Retrieve all customers
all_customers = get_all_customers(conn)
print('All Customers:')
for customer in all_customers:
print(customer)
# Update customer details
update_customer(conn, ('Jane Smith', 'jane@example.com', '0987654321', customer_id))
# Retrieve all customers after update
all_customers = get_all_customers(conn)
print('All Customers after update:')
for customer in all_customers:
print(customer)
# Delete a customer
delete_customer(conn, customer_id)
print(f'Deleted customer with ID: {customer_id}')
if __name__ == '__main__':
main()
Step 3: Running the Python Script
Save the Python code in a file, let’s say customer_management.py, and make sure you have SQLite installed. Then run the script:
python customer_management.py
This script will create a new SQLite database file named customer_management.db and perform CRUD operations on the customers table.
Explanation:
This Python script interacts with a SQLite database for managing customer information. Let me break down the code for you:
Importing Libraries: The script starts by importing the
sqlite3module, which provides an interface for interacting with SQLite databases.Function Definitions:
create_connection(db_file): This function takes the path to the SQLite database file as input and establishes a connection to it. If successful, it prints a confirmation message and returns the connection object.create_customer(conn, customer): This function inserts a new customer into the database. It takes a connection object (conn) and a tuple representing the customer’s information (customer) as input. It then executes an SQLINSERTquery to add the customer to the database and returns the ID of the newly inserted customer.get_all_customers(conn): This function retrieves all customers from the database. It takes a connection object (conn) as input, executes an SQLSELECTquery to fetch all rows from thecustomerstable, and returns them as a list of tuples.update_customer(conn, customer): This function updates the details of an existing customer in the database. It takes a connection object (conn) and a tuple representing the updated customer information (customer) as input. It executes an SQLUPDATEquery to modify the corresponding record in thecustomerstable.delete_customer(conn, id): This function deletes a customer from the database based on their ID. It takes a connection object (conn) and the ID of the customer to be deleted (id) as input. It executes an SQLDELETEquery to remove the corresponding record from thecustomerstable.main(): This function serves as the entry point of the script. It establishes a connection to the database, performs various operations (creating, retrieving, updating, and deleting customers), and demonstrates the usage of the defined functions.
Execution: Finally, the script calls the
main()function if it’s directly executed (i.e., not imported as a module), initiating the database operations.
This script demonstrates basic CRUD (Create, Read, Update, Delete) operations for managing customer data using SQLite in Python.
