Python for Data Analytics: Phase 2 Training

Notebook 2.4: Database Integration and Use Case -- SQLite3

---

Author: Prakash Ukhalkar
Role: Assistant Professor (MCA) | Researcher in Data Science and Machine Learning

Training Date: Monday, 06th July 2026
Notebook Scope: End-to-end SQLite3 workflow -- create a database, define tables, insert records, run SQL queries, and bridge results into Pandas for analysis and visualization.

---

Phase 2 -- Database Integration and Use Case (SQLite3)

Learning Objectives

• Understand when and why to use a local SQLite database
• Create a database, define table schemas, and insert records using Python's sqlite3 module
• Execute SELECT, WHERE, GROUP BY, and JOIN queries from Python
• Load SQL query results directly into Pandas DataFrames
• Visualize database-sourced data using Matplotlib and Seaborn

Training Date: Monday, 06th July 2026
Estimated Duration: 60 minutes
Prerequisites: Notebooks 2.1-2.3 completed

---

Why SQLite for Data Analytics?

SQLite is a file-based relational database -- the entire database lives in a single .db file on disk.

Feature	SQLite	Full Database Server (PostgreSQL, MySQL)
Installation	None -- built into Python	Requires server setup and network access
File location	Local .db file	Server-side storage
Concurrent users	One writer at a time	Many concurrent users
Best for	Local analytics, prototyping, training	Production multi-user systems

When to use SQLite in industry:

• Store cleaned sensor data from a single line or cell
• Prototype a reporting database before deploying to a shared server
• Build offline dashboards that run on a local laptop during audits

---

Environment Setup

Concept and Code Explanation (Before Use)

• sqlite3 is part of the Python standard library -- no pip install required.
• pathlib.Path provides cross-platform file-path handling.
• DB_PATH is defined once and reused by every function -- change one variable to redirect the entire notebook.

import sqlite3
from pathlib import Path

import numpy as np
import pandas as pd
import matplotlib.pyplot as plt
import matplotlib.dates as mdates
import seaborn as sns

%matplotlib inline
sns.set_theme(style="whitegrid", palette="tab10")
plt.rcParams["figure.figsize"] = (10, 5)
np.random.seed(42)

NOTEBOOK_DIR = Path.cwd()
DATA_DIR     = NOTEBOOK_DIR / "data"
DATA_DIR.mkdir(parents=True, exist_ok=True)
DB_PATH      = DATA_DIR / "phase2_factory.db"

print(f"Notebook dir : {NOTEBOOK_DIR}")
print(f"Data dir     : {DATA_DIR}")
print(f"Database path: {DB_PATH}")

Result Interpretation: Environment Setup

• Printed paths confirm the database will be written to the data/ folder.
• mkdir(parents=True, exist_ok=True) creates the directory silently if it does not already exist.
• The database file does not yet exist on disk -- SQLite creates it automatically on the first connect() call.

Helper Functions

Concept and Code Explanation (Before Use)

Encapsulating database operations in small functions keeps each section readable and centralises error handling.

get_connection() -- returns a live sqlite3.Connection object.
run_query() -- accepts any SELECT statement and returns a DataFrame (the SQL-to-Pandas bridge).
execute_ddl() -- runs DDL/DML statements that do not return rows.

def get_connection(db_path: Path = DB_PATH) -> sqlite3.Connection:
    """Open and return a SQLite connection."""
    return sqlite3.connect(db_path)


def run_query(conn: sqlite3.Connection, query: str) -> pd.DataFrame:
    """Execute a SELECT query and return the result as a DataFrame."""
    return pd.read_sql_query(query, conn)


def execute_ddl(conn: sqlite3.Connection, sql: str, params=None) -> None:
    """Execute a DDL or DML statement and commit."""
    cursor = conn.cursor()
    if params:
        cursor.execute(sql, params)
    else:
        cursor.execute(sql)
    conn.commit()


print("Helper functions defined.")

Helper functions defined.

Result Interpretation: Helper Functions

• pd.read_sql_query() handles cursor creation, column naming, and type inference.
• conn.commit() after every write operation ensures data is persisted to disk.
• Defining these once means no boilerplate in each section of the notebook.

Section 1 -- Create the Database Schema

Concept and Code Explanation (Before Use)

A schema defines the structure of a database: which tables exist, what columns they have, and the constraints on each column.

We create two related tables:

• machines -- a reference table of machine identifiers and locations
• production_log -- daily production records linked to machines via a foreign key

SQL Data Types:

SQL Type	Python equivalent	Example
INTEGER	int	log_id, defects
TEXT	str	machine_name, location
REAL	float	temperature_c
DATE	str (ISO format)	"2026-07-01"

IF NOT EXISTS prevents an error if you rerun the cell.

conn = get_connection()

# Create machines reference table
execute_ddl(conn, """
    CREATE TABLE IF NOT EXISTS machines (
        machine_id   TEXT PRIMARY KEY,
        machine_name TEXT NOT NULL,
        location     TEXT NOT NULL,
        install_year INTEGER
    );
""")

# Create production_log fact table
execute_ddl(conn, """
    CREATE TABLE IF NOT EXISTS production_log (
        log_id          INTEGER PRIMARY KEY AUTOINCREMENT,
        machine_id      TEXT    NOT NULL,
        log_date        DATE    NOT NULL,
        shift           TEXT    NOT NULL,
        units_produced  INTEGER NOT NULL,
        defects         INTEGER DEFAULT 0,
        temperature_c   REAL,
        FOREIGN KEY (machine_id) REFERENCES machines(machine_id)
    );
""")

tables = run_query(conn, "SELECT name, type FROM sqlite_master WHERE type='table' ORDER BY name;")
print("Tables in database:")
display(tables)
conn.close()

Tables in database:

	name	type
0	machines	table
1	production_log	table
2	sqlite_sequence	table

Result Interpretation: Schema Creation

• Two table names should appear: machines and production_log.
• AUTOINCREMENT on log_id generates a unique integer ID for every inserted row.
• FOREIGN KEY creates a referential integrity constraint.
• DEFAULT 0 on defects means if no defect count is provided, the database stores 0 rather than NULL.

Section 2 -- Insert Records

Concept and Code Explanation (Before Use)

Parameterised queries use ? placeholders instead of embedding values directly in SQL.

Never build SQL strings by concatenating user-supplied values -- this creates an SQL injection vulnerability:

# WRONG -- vulnerable to SQL injection
cursor.execute(f"INSERT INTO machines VALUES ('{machine_id}', ...)")

# CORRECT -- parameterised
cursor.execute("INSERT INTO machines VALUES (?, ?, ?, ?)", (machine_id, ...))

executemany() inserts a list of tuples in a single call -- much faster than a loop.

conn = get_connection()

# Clear existing data so the cell is safely re-runnable
execute_ddl(conn, "DELETE FROM production_log;")
execute_ddl(conn, "DELETE FROM machines;")

# Insert machine reference records
machine_records = [
    ("M-101", "CNC Lathe 1",    "Cell A", 2018),
    ("M-102", "CNC Lathe 2",    "Cell A", 2019),
    ("M-103", "Milling Centre", "Cell B", 2017),
    ("M-104", "Drill Press",    "Cell B", 2021),
]
conn.cursor().executemany(
    "INSERT INTO machines (machine_id, machine_name, location, install_year) VALUES (?,?,?,?)",
    machine_records
)
conn.commit()
print(f"Inserted {len(machine_records)} machine records.")

# Generate and insert 240 production log records (60 days x 4 machines)
machine_ids = ["M-101", "M-102", "M-103", "M-104"]
shifts      = ["Morning", "Afternoon", "Night"]
dates       = pd.date_range("2026-07-01", periods=60, freq="B")

log_records = []
for date in dates:
    for machine in machine_ids:
        shift   = np.random.choice(shifts)
        units   = int(np.random.normal(500, 20))
        defects = int(np.random.poisson(3 if machine != "M-103" else 6))
        temp    = round(float(np.random.normal(72, 2)), 1)
        log_records.append((machine, str(date.date()), shift, units, defects, temp))

conn.cursor().executemany(
    "INSERT INTO production_log (machine_id, log_date, shift, units_produced, defects, temperature_c) VALUES (?,?,?,?,?,?)",
    log_records
)
conn.commit()
print(f"Inserted {len(log_records)} production log records.")
conn.close()

Inserted 4 machine records.
Inserted 240 production log records.

Result Interpretation: Record Insertion

• 240 records (60 days x 4 machines) should be inserted.
• M-103 uses lam=6 (double the baseline) to simulate its elevated defect rate.
• Separating machine reference data into a machines table follows database normalisation -- machine names are stored once, not repeated in every production row.

Section 3 -- SQL Queries

Concept and Code Explanation (Before Use)

Each query is passed to run_query() which returns a DataFrame for immediate display or further analysis.

SQL query structure:

SELECT  columns_or_expressions
FROM    table_name
JOIN    other_table ON join_condition
WHERE   filter_condition
GROUP BY grouping_column
ORDER BY sort_column DESC
LIMIT   row_count;

conn = get_connection()

# Query 1: First 10 rows
q1 = run_query(conn, """
    SELECT log_id, machine_id, log_date, shift, units_produced, defects
    FROM   production_log
    ORDER  BY log_date, machine_id
    LIMIT  10;
""")
print("Query 1 -- First 10 records:")
display(q1)

# Query 2: Total production per machine
q2 = run_query(conn, """
    SELECT machine_id,
           COUNT(*)                      AS days_recorded,
           SUM(units_produced)           AS total_units,
           SUM(defects)                  AS total_defects,
           ROUND(AVG(units_produced), 1) AS avg_daily_units,
           ROUND(AVG(defects), 2)        AS avg_daily_defects
    FROM   production_log
    GROUP  BY machine_id
    ORDER  BY total_units DESC;
""")
print("\nQuery 2 -- Production totals per machine:")
display(q2)
conn.close()

Query 1 -- First 10 records:

	log_id	machine_id	log_date	shift	units_produced	defects
0	1	M-101	2026-07-01	Night	488	3
1	2	M-102	2026-07-01	Night	505	1
2	3	M-103	2026-07-01	Afternoon	504	0
3	4	M-104	2026-07-01	Morning	529	0
4	5	M-101	2026-07-02	Morning	516	1
5	6	M-102	2026-07-02	Night	529	3
6	7	M-103	2026-07-02	Night	489	4
7	8	M-104	2026-07-02	Afternoon	487	2
8	9	M-101	2026-07-03	Afternoon	476	3
9	10	M-102	2026-07-03	Morning	514	1

Query 2 -- Production totals per machine:

	machine_id	days_recorded	total_units	total_defects	avg_daily_units	avg_daily_defects
0	M-102	60	30175	193	502.9	3.22
1	M-104	60	30119	159	502.0	2.65
2	M-103	60	29889	331	498.2	5.52
3	M-101	60	29771	175	496.2	2.92

conn = get_connection()

# Query 3: JOIN machine names with production summary
q3 = run_query(conn, """
    SELECT m.machine_id,
           m.machine_name,
           m.location,
           COUNT(p.log_id)       AS records,
           SUM(p.units_produced) AS total_units,
           SUM(p.defects)        AS total_defects,
           ROUND(
               100.0 * (SUM(p.units_produced) - SUM(p.defects))
               / SUM(p.units_produced), 2
           ) AS overall_yield_pct
    FROM   machines       AS m
    JOIN   production_log AS p ON m.machine_id = p.machine_id
    GROUP  BY m.machine_id
    ORDER  BY overall_yield_pct DESC;
""")
print("Query 3 -- Machine names joined with production summary:")
display(q3)

# Query 4: High-defect days on M-103
q4 = run_query(conn, """
    SELECT log_date, machine_id, shift, units_produced, defects
    FROM   production_log
    WHERE  machine_id = 'M-103'
      AND  defects    > 8
    ORDER  BY defects DESC;
""")
print(f"\nQuery 4 -- M-103 high-defect days (>8 defects): {len(q4)} rows")
display(q4.head(8))
conn.close()

Query 3 -- Machine names joined with production summary:

	machine_id	machine_name	location	records	total_units	total_defects	overall_yield_pct
0	M-104	Drill Press	Cell B	60	30119	159	99.47
1	M-101	CNC Lathe 1	Cell A	60	29771	175	99.41
2	M-102	CNC Lathe 2	Cell A	60	30175	193	99.36
3	M-103	Milling Centre	Cell B	60	29889	331	98.89

Query 4 -- M-103 high-defect days (>8 defects): 6 rows

	log_date	machine_id	shift	units_produced	defects
0	2026-08-05	M-103	Afternoon	495	13
1	2026-09-22	M-103	Morning	517	11
2	2026-08-07	M-103	Morning	471	9
3	2026-08-26	M-103	Night	473	9
4	2026-08-31	M-103	Night	468	9
5	2026-09-14	M-103	Morning	483	9

conn = get_connection()

# Query 5: Monthly aggregation using strftime
q5 = run_query(conn, """
    SELECT strftime('%Y-%m', log_date)  AS month,
           machine_id,
           SUM(units_produced)          AS monthly_units,
           SUM(defects)                 AS monthly_defects,
           ROUND(AVG(temperature_c), 1) AS avg_temp_c
    FROM   production_log
    GROUP  BY strftime('%Y-%m', log_date), machine_id
    ORDER  BY month, machine_id;
""")
print("Query 5 -- Monthly summary per machine:")
display(q5)
conn.close()

Query 5 -- Monthly summary per machine:

	month	machine_id	monthly_units	monthly_defects	avg_temp_c
0	2026-07	M-101	11401	78	71.7
1	2026-07	M-102	11532	73	71.7
2	2026-07	M-103	11459	113	72.5
3	2026-07	M-104	11539	55	72.8
4	2026-08	M-101	10384	56	72.0
5	2026-08	M-102	10610	66	71.8
6	2026-08	M-103	10427	119	72.8
7	2026-08	M-104	10573	63	72.2
8	2026-09	M-101	7986	41	71.8
9	2026-09	M-102	8033	54	72.0
10	2026-09	M-103	8003	99	71.4
11	2026-09	M-104	8007	41	72.1

Result Interpretation: SQL Queries

• Query 2 reveals which machine has the most total defects -- M-103 should lead.
• Query 3 demonstrates a JOIN: machine names from machines combined with aggregated totals from production_log.
• Query 4 filters to M-103's worst days -- the direct counterpart of anomaly detection in Notebook 2.3.
• Query 5 uses SQLite's strftime('%Y-%m', date) to group records by calendar month.

Section 4 -- Visualizing Database Results

Concept and Code Explanation (Before Use)

SQL query results stored as DataFrames can be immediately passed to Matplotlib or Seaborn.

This section builds two production-ready charts from database query output:

1. A line chart of rolling average defects by machine
2. A Seaborn box plot of yield rate by machine and shift

The complete data pipeline is: SQL query -> DataFrame -> Matplotlib/Seaborn.

conn = get_connection()

# Load full production log joined with machine names
df_full = run_query(conn, """
    SELECT p.log_date, p.machine_id, m.machine_name, m.location,
           p.shift, p.units_produced, p.defects, p.temperature_c
    FROM   production_log AS p
    JOIN   machines       AS m ON p.machine_id = m.machine_id
    ORDER  BY p.log_date, p.machine_id;
""")

df_full["log_date"] = pd.to_datetime(df_full["log_date"])
df_full["yield_pct"] = (
    (df_full["units_produced"] - df_full["defects"]) / df_full["units_produced"] * 100
).round(2)

conn.close()
print(f"Loaded {len(df_full)} rows from database.")
display(df_full.head())

Loaded 240 rows from database.

	log_date	machine_id	machine_name	location	shift	units_produced	defects	temperature_c	yield_pct
0	2026-07-01	M-101	CNC Lathe 1	Cell A	Night	488	3	73.0	99.39
1	2026-07-01	M-102	CNC Lathe 2	Cell A	Night	505	1	74.0	99.80
2	2026-07-01	M-103	Milling Centre	Cell B	Afternoon	504	0	72.2	100.00
3	2026-07-01	M-104	Drill Press	Cell B	Morning	529	0	75.1	100.00
4	2026-07-02	M-101	CNC Lathe 1	Cell A	Morning	516	1	69.0	99.81

fig, axes = plt.subplots(1, 2, figsize=(14, 5))

# Chart A: 5-day rolling average defects per machine
ax = axes[0]
for machine, group in df_full.groupby("machine_id"):
    g = group.sort_values("log_date")
    rolling = g.set_index("log_date")["defects"].rolling(5).mean()
    ax.plot(rolling.index, rolling.values, linewidth=1.8, label=machine)

ax.set_title("5-Day Rolling Avg Defects by Machine", fontsize=12, fontweight="bold")
ax.set_xlabel("Date")
ax.set_ylabel("Avg Daily Defects")
ax.xaxis.set_major_formatter(mdates.DateFormatter("%b"))
ax.xaxis.set_major_locator(mdates.MonthLocator())
ax.legend(title="Machine", fontsize=9)
plt.setp(ax.get_xticklabels(), rotation=20, ha="right")

# Chart B: Yield per machine per shift
ax2 = axes[1]
sns.boxplot(data=df_full, x="machine_id", y="yield_pct",
            hue="shift", palette="Set2", linewidth=1.0, ax=ax2)
ax2.axhline(99.0, color="red", linestyle="--", linewidth=1.2, label="Target 99%")
ax2.set_title("Yield Rate by Machine and Shift", fontsize=12, fontweight="bold")
ax2.set_xlabel("Machine")
ax2.set_ylabel("Yield Rate (%)")
handles, labels = ax2.get_legend_handles_labels()
ax2.legend(handles, labels, title="Shift", fontsize=8, title_fontsize=8)

plt.suptitle("Production Database -- Visual Summary", fontsize=13, y=1.02)
plt.tight_layout()
plt.savefig("db_charts.png", dpi=150, bbox_inches="tight")
plt.show()
print("Charts saved.")

Charts saved.

Result Interpretation: Database Visualization

• The rolling average line chart shows trends invisible in raw daily data -- M-103's line should sit consistently higher.
• The shift-grouped box plot answers: Does the defect rate vary by shift on the same machine?
• These charts are built entirely from database query results -- the same pattern used in every production analytics environment.

---

About This Material

Author	Prakash Ukhalkar
Role	Assistant Professor (MCA) · Researcher in Data Science and Machine Learning
GitHub	@prakash-ukhalkar
Repository	mfg-python-sql-training
License	MIT

Provided for educational use in Python Data Analytics Phase 2 training programmes.
For questions, corrections, or contributions, open an issue or pull request on GitHub.

---

_{© 2026 Prakash Ukhalkar · Python for Data Analytics · MIT License}