Mastering Python and Databases: Unleash the Power of Data with Real-World Applications and Hands-On Examples

Photo by Hitesh Choudhary on Unsplash

Objectives:

1. Understand the basics of Python and databases.
2. Learn how to connect Python with SQLite.
3. Learn how to interact with databases using SQL commands.
4. Apply learned concepts to real-world use cases related to Apple Inc.

Materials:

1. Python 3.6 or later
2. SQLite
3. SQLite Browser (optional)

Module 1: Introduction

Duration: 10 minutes

Objective: Provide an overview of Python, databases, and SQLite, and explain why they are essential for real-world use cases.

Python is a widely-used, high-level, and versatile programming language. Its readability and simplicity make it an excellent choice for various applications, including web development, data analysis, artificial intelligence, and more. One of Python’s strengths is its ability to interact with various databases easily, allowing developers to store, retrieve, and analyze data.

A database is a structured set of data, and it is vital for managing and organizing information in a scalable and efficient way. Many organizations, including Apple Inc., rely on databases to store and process vast amounts of data, from product inventory to sales transactions.

SQLite is a software library that provides a relational database management system (RDBMS). Unlike other RDBMSs, SQLite is serverless, self-contained, and zero-configuration, making it an ideal choice for many applications, including embedded systems, mobile devices, and desktop applications. SQLite is widely used due to its ease of use, portability, and low resource requirements.

In this lesson, we will learn how to connect Python with SQLite, create tables, insert data, and perform various database operations using SQL commands. We will apply these concepts to real-world use cases related to Apple Inc., such as analyzing product sales and identifying top-selling products.

By the end of this module, you should have a basic understanding of Python, databases, SQLite, and their relevance to real-world scenarios.

Module 2: Setting up Python and SQLite

Duration: 10 minutes

Objective: Learn how to set up SQLite and install the necessary libraries to interact with SQLite databases using Python.

Content:

SQLite comes pre-installed with Python since version 2.5.x. For this lesson, we will use Python 3.6 or later. To check if SQLite is installed and ready to use, open a Python shell and type the following commands:

import sqlite3
print(sqlite3.version)

This code imports the sqlite3 module and prints the version of SQLite. If it outputs a version number, it means SQLite is installed and ready to use.

Now, let’s create a new Python file called apple_database.py to store our code. We will start by importing the necessary library and establishing a connection to an SQLite database.

# Importing SQLite3 module
import sqlite3
# Establishing a connection to the database
conn = sqlite3.connect('apple.db')
# Closing the connection
conn.close()

In this code snippet, we import the sqlite3 module and use the connect() function to establish a connection to an SQLite database called 'apple.db'. If the database does not exist, it will be created automatically. Finally, we close the connection using the close() method.

In the next module, we will learn how to create tables and insert data into them.

Module 3: Creating a database and tables

Duration: 15 minutes

Objective: Learn how to create an SQLite database and define tables for Apple products and sales data.

Now that we have set up Python and SQLite, let’s create tables to store Apple product and sales data. We will create two tables: one for product information and another for sales transactions.

In apple_database.py, add the following code to create the tables:

# Function to create tables
def create_tables(connection):
    cursor = connection.cursor()
    
    # Creating the 'products' table
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS products (
        id INTEGER PRIMARY KEY,
        name TEXT NOT NULL,
        category TEXT NOT NULL,
        price REAL NOT NULL
    );
    ''')
    
    # Creating the 'sales' table
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS sales (
        id INTEGER PRIMARY KEY,
        product_id INTEGER NOT NULL,
        quantity INTEGER NOT NULL,
        sale_date TEXT NOT NULL,
        FOREIGN KEY (product_id) REFERENCES products (id)
    );
    ''')
    
    # Committing the changes and closing the cursor
    connection.commit()
    cursor.close()

# Establishing a connection to the database
conn = sqlite3.connect('apple.db')
# Creating tables
create_tables(conn)
# Closing the connection
conn.close()

In this code, we define a function called create_tables() that takes a database connection as an argument. Inside the function, we create a cursor object using the cursor() method. We then execute two SQL commands to create the 'products' and 'sales' tables, respectively.

The ‘products’ table has the following columns:

• id: A unique identifier for each product (integer, primary key)
• name: The name of the product (text, not null)
• category: The product’s category (text, not null)
• price: The price of the product (real, not null)

The ‘sales’ table has the following columns:

• id: A unique identifier for each sale (integer, primary key)
• product_id: The ID of the product being sold (integer, foreign key)
• quantity: The number of products sold (integer, not null)
• sale_date: The date of the sale (text, not null)

After defining the tables, we commit the changes using the commit() method and close the cursor. Finally, we connect to the 'apple.db' database, create the tables by calling create_tables(), and close the connection. Double click on apple.db to see the contents of the file and confirm the tables are created.

In the next module, we will learn how to insert data into these tables.

Module 4: Inserting data into the tables

Duration: 15 minutes

Objective: Learn how to insert Apple product data and sales data into the tables.

Now that we have created the ‘products’ and ‘sales’ tables, let’s populate them with some sample data. We will start by inserting Apple product data into the ‘products’ table.

In apple_database.py, add the following function to insert product data:

# Function to insert product data
def insert_product_data(connection, products):
    cursor = connection.cursor()
    
    for product in products:
        cursor.execute('''
        INSERT INTO products (name, category, price) VALUES (?, ?, ?)
        ''', product)
    
    # Committing the changes and closing the cursor
    connection.commit()
    cursor.close()
# Sample Apple product data
product_data = [
    ("iPhone 13", "smartphone", 799),
    ("iPhone 13 Pro", "smartphone", 999),
    ("MacBook Air", "laptop", 999),
    ("MacBook Pro", "laptop", 1299),
    ("iPad Air", "tablet", 599),
    ("iPad Pro", "tablet", 799),
]
# Establishing a connection to the database
conn = sqlite3.connect('apple.db')
# Inserting product data
insert_product_data(conn, product_data)
# Closing the connection
conn.close()

In this code, we define a function called insert_product_data() that takes a database connection and a list of product tuples as arguments. For each product tuple, we execute an SQL command to insert the data into the 'products' table. After inserting all product data, we commit the changes and close the cursor.

We also define a sample list of Apple products called product_data. Each tuple in the list contains the name, category , and price of a product. After defining the sample data, we connect to the ‘apple.db’ database, insert the product data by calling insert_product_data(), and close the connection.

Next, let’s insert some sample sales data into the ‘sales’ table. Add the following function to apple_database.py:

# Function to insert sales data
def insert_sales_data(connection, sales):
    cursor = connection.cursor()
    
    for sale in sales:
        cursor.execute('''
        INSERT INTO sales (product_id, quantity, sale_date) VALUES (?, ?, ?)
        ''', sale)
    
    # Committing the changes and closing the cursor
    connection.commit()
    cursor.close()
# Sample sales data
sales_data = [
    (1, 3, "2023-01-15"),
    (2, 2, "2023-01-17"),
    (3, 1, "2023-01-19"),
    (4, 5, "2023-01-22"),
    (5, 3, "2023-01-24"),
    (6, 2, "2023-01-26"),
]
# Establishing a connection to the database
conn = sqlite3.connect('apple.db')
# Inserting sales data
insert_sales_data(conn, sales_data)
# Closing the connection
conn.close()

In this code, we define a function called insert_sales_data() that takes a database connection and a list of sales tuples as arguments. For each sales tuple, we execute an SQL command to insert the data into the 'sales' table. After inserting all sales data, we commit the changes and close the cursor.

We also define a sample list of sales called sales_data. Each tuple in the list contains the product_id, quantity, and sale_date of a sales transaction. After defining the sample data, we connect to the ‘apple.db’ database, insert the sales data by calling insert_sales_data(), and close the connection.

At this point, we have successfully inserted sample Apple product and sales data into the ‘products’ and ‘sales’ tables. In the next module, we will learn how to query this data using various SQL commands.

Module 5: Querying data from the tables

Duration: 20 minutes

Objective: Learn how to fetch data from the tables using SQL commands like SELECT, WHERE, and ORDER BY.

Now that we have inserted data into the ‘products’ and ‘sales’ tables, let’s learn how to query the data using SQL commands. We will start by fetching all products from the ‘products’ table.

In apple_database.py, add the following function to fetch all products:

# Function to fetch all products
def fetch_all_products(connection):
    cursor = connection.cursor()
    
    cursor.execute('SELECT * FROM products')
    
    # Fetching all rows and printing them
    rows = cursor.fetchall()
    for row in rows:
        print(row)
    
    # Closing the cursor
    cursor.close()
# Establishing a connection to the database
conn = sqlite3.connect('apple.db')
# Fetching and printing all products
fetch_all_products(conn)
# Closing the connection
conn.close()

# Establishing a connection to the database
conn = sqlite3.connect('apple.db')
# Fetching and printing all products
fetch_all_products(conn)
# Closing the connection
conn.close()

In this code, we define a function called fetch_all_products() that takes a database connection as an argument. We execute the SQL command 'SELECT * FROM products' to fetch all rows from the 'products' table. Then, we fetch all the rows using the fetchall() method and print them. Finally, we close the cursor.

Now let’s fetch sales data for a specific product. Add the following function to apple_database.py:

# Fetching all rows and printing them
    rows = cursor.fetchall()
    for row in rows:
        print(row)
    
    # Closing the cursor
    cursor.close()

# Establishing a connection to the database
conn = sqlite3.connect('apple.db')

# Fetching and printing sales data for a specific product (e.g., product_id = 1)
fetch_sales_by_product(conn, 1)

# Closing the connection
conn.close()

In this code, we define a function called fetch_sales_by_product() that takes a database connection and a product_id as arguments. We execute the SQL command 'SELECT * FROM sales WHERE product_id = ?' to fetch all rows from the 'sales' table with the specified product_id. Then, we fetch all the rows using the fetchall() method and print them. Finally, we close the cursor.

Lastly, let's fetch all products sorted by their price. Add the following function to apple_database.py:

# Function to fetch all products sorted by price
def fetch_products_sorted_by_price(connection):
    cursor = connection.cursor()
    
    cursor.execute('SELECT * FROM products ORDER BY price')
    
    # Fetching all rows and printing them
    rows = cursor.fetchall()
    for row in rows:
        print(row)
    
    # Closing the cursor
    cursor.close()

# Establishing a connection to the database
conn = sqlite3.connect('apple.db')

# Fetching and printing all products sorted by price
fetch_products_sorted_by_price(conn)

# Closing the connection
conn.close()

In this code, we define a function called fetch_products_sorted_by_price() that takes a database connection as an argument. We execute the SQL command 'SELECT * FROM products ORDER BY price' to fetch all rows from the 'products' table sorted by their price. Then, we fetch all the rows using the fetchall() method and print them. Finally, we close the cursor.

By the end of this module, you should be familiar with querying data from SQLite tables using various SQL commands such as SELECT, WHERE, and ORDER BY. You should also understand how to fetch all products, sales data for a specific product, and products sorted by their price. These skills can be applied to real-world use cases related to Apple Inc. or any other organization that uses databases to store and analyze product and sales data.

Module 6: Joining tables and aggregating data

Duration: 20 minutes

Objective: Learn how to join tables and aggregate data using SQL commands like INNER JOIN, GROUP BY, and aggregate functions such as SUM and COUNT.

In this module, we will learn how to join tables and aggregate data using SQL commands. This will help us to analyze Apple product and sales data more effectively.

First, let’s fetch the total sales for each product. In apple_database.py, add the following function to join the 'products' and 'sales' tables and calculate the total sales:

# Function to fetch total sales for each product
def fetch_total_sales_per_product(connection):
    cursor = connection.cursor()
    cursor.execute('''
        SELECT products.name, SUM(sales.quantity) as total_sales
        FROM products
        INNER JOIN sales ON products.id = sales.product_id
        GROUP BY products.id
        ''')
        # Fetching all rows and printing them
        rows = cursor.fetchall()
        for row in rows:
            print(row)
        # Closing the cursor
        cursor.close()
# Establishing a connection to the database
conn = sqlite3.connect('apple.db')
# Fetching and printing total sales for each product
fetch_total_sales_per_product(conn)
# Closing the connection
conn.close()

In this code, we define a function called fetch_total_sales_per_product() that takes a database connection as an argument. We execute the SQL command to join the 'products' and 'sales' tables using an INNER JOIN on the product_id column. We then group the results by the product id and calculate the total sales using the SUM() function.

Next, let’s fetch the top-selling products. Add the following function to apple_database.py:

# Function to fetch the top-selling products
def fetch_top_selling_products(connection, limit=3):
    cursor = connection.cursor()

    cursor.execute('''
    SELECT products.name, SUM(sales.quantity) as total_sales
    FROM products
    INNER JOIN sales ON products.id = sales.product_id
    GROUP BY products.id
    ORDER BY total_sales DESC
    LIMIT ?
    ''', (limit,))

    # Fetching all rows and printing them
    rows = cursor.fetchall()
    for row in rows:
        print(row)

    # Closing the cursor
    cursor.close()

# Establishing a connection to the database
conn = sqlite3.connect('apple.db')

# Fetching and printing top-selling products (e.g., top 3)
fetch_top_selling_products(conn, 3)

# Closing the connection
conn.close()

In this code, we define a function called fetch_top_selling_products() that takes a database connection and an optional limit argument as input. We execute the SQL command to join the 'products' and 'sales' tables using an INNER JOIN on the product_id column, group the results by the product id, and calculate the total sales using the SUM() function. We then order the results by total sales in descending order and limit the results to the specified number of top-selling products.

By the end of this module, you should be familiar with joining tables, aggregating data, and using SQL commands like INNER JOIN, GROUP BY, and aggregate functions such as SUM and COUNT. You should also understand how to fetch total sales per product and the top-selling products. These skills can be applied to real-world use cases related to Apple Inc. or any other organization that uses databases to store and analyze product and sales data.

Module 7: Updating and deleting records

Duration: 15 minutes

Objective: Learn how to update and delete records in the tables using SQL commands like UPDATE and DELETE.

In this module, we will learn how to update and delete records in the ‘products’ and ‘sales’ tables using SQL commands.

First, let’s update the price of a specific product. In apple_database.py, add the following function to update the product price:

# Function to update product price
def update_product_price(connection, product_id, new_price):
    cursor = connection.cursor()
    cursor.execute('''
    UPDATE products
    SET price = ?
    WHERE id = ?
    ''', (new_price, product_id))
    # Committing the changes and closing the cursor
    connection.commit()
    cursor.close()
# Establishing a connection to the database
conn = sqlite3.connect('apple.db')
# Updating the price of a specific product (e.g., product_id = 1)
update_product_price(conn, 1, 849)
# Closing the connection
conn.close()

In this code, we define a function called update_product_price() that takes a database connection, a product_id, and a new_price as arguments. We execute the SQL command 'UPDATE products SET price = ? WHERE id = ?' to update the price of the product with the specified product_id. After updating the product price, we commit the changes and close the cursor.

Next, let’s delete a specific sales record. Add the following function to apple_database.py:

# Function to delete a sales record
def delete_sales_record(connection, sale_id):
    cursor = connection.cursor()
    cursor.execute('''
    DELETE FROM sales
    WHERE id = ?
    ''', (sale_id,))
    # Committing the changes and closing the cursor
    connection.commit()
    cursor.close()
# Establishing a connection to the database
conn = sqlite3.connect('apple.db')
# Deleting a specific sales record (e.g., sale_id = 1)
delete_sales_record(conn, 1)
# Closing the connection
conn.close()

In this code, we define a function called delete_sales_record() that takes a database connection and a sale_id as arguments. We execute the SQL command 'DELETE FROM sales WHERE id = ?' to delete the sales record with the specified sale_id. After deleting the record, we commit the changes and close the cursor.

By the end of this module, you should be familiar with updating and deleting records in SQLite tables using SQL commands like UPDATE and DELETE. These skills can be applied to real-world use cases related to Apple Inc. or any other organization that uses databases to store and analyze product and sales data.

Module 8: Introduction to SQLAlchemy

Duration: 20 minutes

Objective: Learn the basics of SQLAlchemy, an Object Relational Mapper (ORM) for Python that simplifies database operations.

SQLAlchemy is a powerful and flexible Object Relational Mapper (ORM) for Python. It provides a set of high-level API for connecting to relational databases and performing common database operations without writing SQL queries directly. In this module, we will go through the basics of SQLAlchemy and provide hands-on examples.

First, install SQLAlchemy using pip:

!pip install sqlalchemy

Now, let’s create a Python file called apple_sqlalchemy.py and import the necessary libraries:

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker

Next, create an engine and a base class for the ORM:

engine = create_engine("sqlite:///apple_sqlalchemy.db", echo=False)
Base = declarative_base()

Now, let’s define a simple Product class that maps to the ‘products’ table:

class Product(Base):
    __tablename__ = "products"
    id = Column(Integer, primary_key=True)
    name = Column(String)
    category = Column(String)
    price = Column(Integer)
    def __repr__(self):
        return f"Product(id={self.id}, name={self.name}, category={self.category}, price={self.price})"

With the Product class defined, create the ‘products’ table in the database:

Base.metadata.create_all(engine)

Next, let’s create a session factory and a session to interact with the database:

Session = sessionmaker(bind=engine)
session = Session()

With the session created, we can now perform various operations on the ‘products’ table. Let’s start by inserting a new product:

new_product = Product(name="iPhone 13", category="Smartphone", price=799)
session.add(new_product)
session.commit()

To query the ‘products’ table, we can use the session object as follows:

products = session.query(Product).all()
for product in products:
    print(product)

To update a record, you can modify the object attributes and commit the changes:

product_to_update = session.query(Product).filter_by(name="iPhone 13").first()
product_to_update.price = 849
session.commit()

Finally, to delete a record, you can use the delete() method:

product_to_delete = session.query(Product).filter_by(name="iPhone 13").first()
session.delete(product_to_delete)
session.commit()

Remember to close the session when you’re done with the database operations:

session.close()

In this module, we have covered the basics of SQLAlchemy, including creating an engine, defining a table schema using Python classes, and performing common database operations like insert, query, update, and delete using the ORM. SQLAlchemy allows for a more Pythonic way of handling databases and can simplify complex database operations, making it an invaluable tool for managing and analyzing data in real-world scenarios.

Assignment: Creating and Manipulating a Sample Database

Objective: Students will create a new database, add a table, insert sample records, and perform various operations, including using loops to add and remove items from the database.

Sample Solution to the Assignment

To accomplish this task, we will use the pandas, sqlite3, and requests libraries. Make sure to install pandas and requests before running the code using:

!pip install pandas requests

Now, here’s the code:

import pandas as pd
import sqlite3
import requests
# Download data from an internet source (using a CSV file as an example)
url = "https://people.sc.fsu.edu/~jburkardt/data/csv/hw_200.csv"
response = requests.get(url)
open("hw_200.csv", "w").write(response.text)
# Read the CSV file using pandas
data = pd.read_csv("hw_200.csv")
# Establish a connection to the SQLite database
conn = sqlite3.connect("hw_200.db")
# Load the data into a table called 'sample_data' in the SQLite database
data.to_sql("sample_data", conn, if_exists="replace", index=False)
# Define a function to query the database
def query_database(connection, query):
    cursor = connection.cursor()
    cursor.execute(query)
    # Fetch all rows and print them
    rows = cursor.fetchall()
    for row in rows:
        print(row)
    # Close the cursor
    cursor.close()
# Query the database
query = "SELECT * FROM sample_data WHERE Index1 > 50"
query_database(conn, query)
# Close the database connection
conn.close()

In this code, we first download a CSV file from the given URL and save it locally as “hw_200.csv”. We then use pandas to read the CSV file and store it in a DataFrame called data. After establishing a connection to an SQLite database named "hw_200.db", we load the data from the DataFrame into a table called 'sample_data' in the database.

We define a function called query_database() that takes a database connection and an SQL query as arguments. Inside the function, we execute the query, fetch all rows from the result, and print them. Finally, we close the cursor.

We then call the query_database() function with a query that selects all rows from the 'sample_data' table where the Index1 value is greater than 50. After querying the database, we close the connection.

This code demonstrates how to download data from an internet source, read it into a pandas DataFrame, load the data into an SQLite database, and query the database.

Conclusion

In conclusion, this comprehensive tutorial on Python and databases, specifically SQLite, has equipped you with the knowledge and skills to efficiently manage and analyze data in real-world scenarios. By covering a wide range of topics, from database and table creation to data manipulation and aggregation, we’ve provided you with a strong foundation for working with databases in Python. The practical examples, centered around a hypothetical Apple Inc. use case, have demonstrated the applicability of these techniques in real-life situations. As you progress in your data-driven journey, we hope you’ll continue to build upon these skills and unlock the full potential of Python and databases to make informed decisions, optimize processes, and drive success in your personal and professional endeavors.