Databases store structured data permanently. Business analysts use databases to query sales history, customer records, inventory levels, and more. Master SQL basics and you can pull data from any system, build reports, and power dashboards.

Estimated reading time: 25–30 minutes

Database Basics

Tables → rows and columns like Excel
SQL → language to query databases
Primary key → unique ID for each row
CRUD → Create, Read, Update, Delete

Great for: persistent data storage, queries

Python + Databases

sqlite3 → built-in, no server needed
Parameterized queries → prevent SQL injection
Transactions → commit to save changes
pandas integration → query to DataFrame

Great for: data analysis, automation

Creating a Database and Table

SQLite is perfect for learning—no server setup required.

python

import sqlite3

# Connect (creates file if not exists)
conn = sqlite3.connect('sales.db')
cursor = conn.cursor()

# Create table
cursor.execute('''
    CREATE TABLE IF NOT EXISTS customers (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        email TEXT UNIQUE,
        created_date TEXT
    )
''')

conn.commit()
print("Table created")

Inserting Data (CREATE)

Use parameterized queries (?) to prevent SQL injection.

python

# Insert single record
cursor.execute('''
    INSERT INTO customers (name, email, created_date)
    VALUES (?, ?, ?)
''', ('Ana Garcia', 'ana@company.com', '2024-01-15'))

# Insert multiple records
customers = [
    ('Bob Smith', 'bob@company.com', '2024-01-16'),
    ('Carol Jones', 'carol@company.com', '2024-01-17')
]

cursor.executemany('''
    INSERT INTO customers (name, email, created_date)
    VALUES (?, ?, ?)
''', customers)

conn.commit()
print("Inserted", cursor.rowcount, "rows")

Querying Data (READ)

SELECT retrieves data from tables.

python

# Fetch all records
cursor.execute('SELECT * FROM customers')
rows = cursor.fetchall()
for row in rows:
    print(row)

# Fetch with WHERE clause
cursor.execute('''
    SELECT name, email FROM customers
    WHERE name LIKE ?
''', ('%Garcia%',))
results = cursor.fetchall()
print(results)

# Fetch one record
cursor.execute('SELECT * FROM customers WHERE id = ?', (1,))
customer = cursor.fetchone()
print(customer)

Updating Data (UPDATE)

Modify existing records.

python

# Update single record
cursor.execute('''
    UPDATE customers
    SET email = ?
    WHERE name = ?
''', ('ana.garcia@newcompany.com', 'Ana Garcia'))

conn.commit()
print("Updated", cursor.rowcount, "rows")

Deleting Data (DELETE)

Remove records from table.

python

# Delete specific record
cursor.execute('''
    DELETE FROM customers
    WHERE email = ?
''', ('bob@company.com',))

conn.commit()
print("Deleted", cursor.rowcount, "rows")

Using pandas with SQL

Query directly into DataFrame for analysis.

python

import pandas as pd
import sqlite3

conn = sqlite3.connect('sales.db')

# Query to DataFrame
df = pd.read_sql_query('SELECT * FROM customers', conn)
print(df)

# Write DataFrame to database
new_customers = pd.DataFrame({
    'name': ['David Lee', 'Eve Chen'],
    'email': ['david@co.com', 'eve@co.com'],
    'created_date': ['2024-01-18', '2024-01-19']
})

new_customers.to_sql('customers', conn, if_exists='append', index=False)
print("Inserted from DataFrame")

Cornerstone Project — Customer Database Manager (step-by-step)

Build a customer management system with database backend. Add customers, search by name/email, update records, generate reports. This is a foundation for CRM tools, order systems, or any data-driven application.

Step 1 — Initialize database

python

import sqlite3
from datetime import datetime

def init_database():
    conn = sqlite3.connect('crm.db')
    cursor = conn.cursor()
    
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS customers (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            email TEXT UNIQUE NOT NULL,
            phone TEXT,
            company TEXT,
            created_date TEXT,
            last_contact TEXT
        )
    ''')
    
    conn.commit()
    return conn

conn = init_database()
print("Database initialized")

Step 2 — Add customer function

python

def add_customer(conn, name, email, phone=None, company=None):
    cursor = conn.cursor()
    created_date = datetime.now().strftime('%Y-%m-%d')
    
    try:
        cursor.execute('''
            INSERT INTO customers (name, email, phone, company, created_date)
            VALUES (?, ?, ?, ?, ?)
        ''', (name, email, phone, company, created_date))
        
        conn.commit()
        print("Added customer:", name)
        return cursor.lastrowid
    except sqlite3.IntegrityError:
        print("Error: Email already exists")
        return None

# Test it
add_customer(conn, "Ana Garcia", "ana@company.com", "555-0001", "TechCorp")
add_customer(conn, "Bob Smith", "bob@company.com", "555-0002", "DataInc")

Step 3 — Search customers

python

def search_customers(conn, search_term):
    cursor = conn.cursor()
    cursor.execute('''
        SELECT * FROM customers
        WHERE name LIKE ? OR email LIKE ? OR company LIKE ?
    ''', ('%' + search_term + '%', '%' + search_term + '%', '%' + search_term + '%'))
    
    return cursor.fetchall()

# Test it
results = search_customers(conn, "Garcia")
for customer in results:
    print(customer)

Step 4 — Update last contact

python

def update_last_contact(conn, customer_id):
    cursor = conn.cursor()
    contact_date = datetime.now().strftime('%Y-%m-%d')
    
    cursor.execute('''
        UPDATE customers
        SET last_contact = ?
        WHERE id = ?
    ''', (contact_date, customer_id))
    
    conn.commit()
    print("Updated last contact for customer", customer_id)

# Test it
update_last_contact(conn, 1)

Step 5 — Generate customer report

python

def generate_report(conn):
    cursor = conn.cursor()
    
    # Total customers
    cursor.execute('SELECT COUNT(*) FROM customers')
    total = cursor.fetchone()[0]
    
    # Customers by company
    cursor.execute('''
        SELECT company, COUNT(*) as count
        FROM customers
        WHERE company IS NOT NULL
        GROUP BY company
        ORDER BY count DESC
    ''')
    by_company = cursor.fetchall()
    
    # Recent customers
    cursor.execute('''
        SELECT name, email, created_date
        FROM customers
        ORDER BY created_date DESC
        LIMIT 5
    ''')
    recent = cursor.fetchall()
    
    print("Customer Report")
    print("=" * 40)
    print("Total customers:", total)
    print("\nBy company:")
    for company, count in by_company:
        print(" •", company, ":", count)
    print("\nRecent customers:")
    for name, email, date in recent:
        print(" •", name, "-", email, "(", date, ")")

# Test it
generate_report(conn)

Step 6 — Export to CSV

python

import csv

def export_to_csv(conn, filename='customers.csv'):
    cursor = conn.cursor()
    cursor.execute('SELECT * FROM customers')
    
    with open(filename, 'w', newline='') as f:
        writer = csv.writer(f)
        writer.writerow(['ID', 'Name', 'Email', 'Phone', 'Company', 'Created', 'Last Contact'])
        writer.writerows(cursor.fetchall())
    
    print("Exported to", filename)

# Test it
export_to_csv(conn)

How this helps at work

Persistent storage → data survives between script runs
Fast queries → find customers instantly by any field
Reporting → generate insights from stored data
Foundation → extend to orders, products, invoices

Key Takeaways

sqlite3 → built-in database, no server needed
SQL basics → SELECT, INSERT, UPDATE, DELETE
Parameterized queries → use ? placeholders for safety
pandas integration → query to DataFrame for analysis
Transactions → commit() to save changes
Cornerstone → customer database manager with CRUD operations

Next Steps

You have mastered database basics. Next, explore PostgreSQL or MySQL for production databases, or dive into ORMs like SQLAlchemy for object-oriented database access.

Introduction to Databases

Database Basics

Python + Databases

Creating a Database and Table

Inserting Data (CREATE)

Querying Data (READ)

Updating Data (UPDATE)

Deleting Data (DELETE)

Using pandas with SQL

Cornerstone Project — Customer Database Manager (step-by-step)

Step 1 — Initialize database

Step 2 — Add customer function

Step 3 — Search customers

Step 4 — Update last contact

Step 5 — Generate customer report

Step 6 — Export to CSV

How this helps at work

Key Takeaways

Next Steps

On This Page