Hands-On Activity Lesson 1

Objective:

• Explore and interact with a sample SQLite database to understand its structure and data.

Steps:

1. Set Up the SQLite Database:

   • Download the employees.db sample database provided in the resources.

2. Install Required Libraries:

   • Ensure you have Python installed (3.8+ recommended).

Install the SQLite library (if not already included with Python):
bash
Copy code
pip install sqlite3

3. Write a Python Script to Explore the Database Schema:

   • Connect to the database.

   • Query the schema to identify available tables and columns.

   • Fetch a sample of the data to understand its structure.

Sample Script:

python

Copy code

import sqlite3

# Connect to the SQLite database

db_path = "employees.db"  # Replace with your database path

connection = sqlite3.connect(db_path)

# Create a cursor object to execute SQL queries

cursor = connection.cursor()

# Fetch and display all tables in the database

cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")

tables = cursor.fetchall()

print("Tables in the database:", tables)

# Explore the schema of a specific table

table_name = "employees"  # Replace with your table name

cursor.execute(f"PRAGMA table_info({table_name});")

schema = cursor.fetchall()

print(f"Schema of the '{table_name}' table:", schema)

# Fetch and display a sample of the data

cursor.execute(f"SELECT * FROM {table_name} LIMIT 5;")

sample_data = cursor.fetchall()

print(f"Sample data from '{table_name}' table:", sample_data)

# Close the database connection

connection.close()

Activity Deliverable

• A Python script that connects to the SQLite database, retrieves the schema, and displays a sample of the data.

Learning Outcomes

• Understand the structure of data stored in databases.

• Gain hands-on experience interacting with a database using Python.

• Differentiate between real-time and batch pipelines to select the right approach for specific use cases.

This activity focuses on gaining hands-on experience with Python and SQLite by exploring the structure (schema) of a database and retrieving sample data.

Goals:

1. Understand the database schema: Learn the structure of the database, including table names, columns, and their data types.

2. Fetch and inspect data: Retrieve and display sample data from tables to understand the stored information.

Steps:

1. Set Up the Environment

• Install Python (3.8+ recommended).

SQLite is included with Python by default. If needed, verify the installation by running:
bash
Copy code
python -c "import sqlite3; print('SQLite is ready')"

2. Download the Sample Database

• Obtain the employees.db file from the course resources or create a sample SQLite database for testing.

3. Write the Python Script

• Follow these steps in your script:

Python Script

python

Copy code

import sqlite3

# Step 1: Connect to the SQLite database

db_path = "employees.db"  # Path to your SQLite database

try:

    connection = sqlite3.connect(db_path)

    print("Database connection successful!")

except sqlite3.Error as e:

    print(f"Error connecting to database: {e}")

    exit()

# Step 2: Create a cursor object to execute SQL queries

cursor = connection.cursor()

# Step 3: Retrieve and display all table names in the database

print("\n--- Tables in the Database ---")

cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")

tables = cursor.fetchall()

for table in tables:

    print(f"Table: {table[0]}")

# Step 4: Explore the schema of a specific table

table_name = "employees"  # Replace with your desired table name

print(f"\n--- Schema of Table: {table_name} ---")

cursor.execute(f"PRAGMA table_info({table_name});")

schema = cursor.fetchall()

for column in schema:

    print(f"Column: {column[1]}, Type: {column[2]}, Nullable: {bool(column[3])}")

# Step 5: Fetch and display sample data from the table

print(f"\n--- Sample Data from Table: {table_name} ---")

cursor.execute(f"SELECT * FROM {table_name} LIMIT 5;")

sample_data = cursor.fetchall()

for row in sample_data:

    print(row)

# Step 6: Close the database connection

connection.close()

print("\nDatabase connection closed.")

Explanation of the Script

1. Connect to the Database:

   • The sqlite3.connect() function establishes a connection to the database file.

   • A try-except block is used to handle connection errors.

2. Retrieve Table Names:

   • sqlite_master contains metadata about the database.

   • The query SELECT name FROM sqlite_master WHERE type='table' lists all table names.

3. Explore Table Schema:

   • The PRAGMA table_info command returns details about a table's schema, including column names, data types, and nullability.

4. Fetch Sample Data:

   • Use SELECT * FROM <table_name> LIMIT 5 to fetch the first 5 rows of data.

5. Close Connection:

   • Properly close the database connection to free resources.

Expected Output

For a database containing an employees table:

sql

Copy code

Database connection successful!

--- Tables in the Database ---

Table: employees

--- Schema of Table: employees ---

Column: id, Type: INTEGER, Nullable: False

Column: name, Type: TEXT, Nullable: False

Column: role, Type: TEXT, Nullable: True

--- Sample Data from Table: employees ---

(1, 'Alice', 'Engineer')

(2, 'Bob', 'Manager')

(3, 'Charlie', 'Analyst')

(4, 'Diana', 'Developer')

(5, 'Eve', 'HR')

Database connection closed.

Enhancements

1. Add command-line arguments to specify the database path and table name.

2. Format the output for better readability using libraries like tabulate or pandas.