Python 11: SQL with Python

🎯 Goal:

By the end of this lesson, you’ll:

• Connect Python to an SQL database
• Run SQL queries directly from Python
• Load query results into Pandas for analysis
• Perform joins, filters, and aggregations

💡 1. Why Use SQL with Python?

• SQL is perfect for structured data (like sales, inventory, or employee records).
• Python is great for analysis.Combining both lets you:

🔎 Query large datasets with SQL, then analyze or visualize with Python.

🧰 2. Getting Started

We’ll use SQLite, a lightweight, file-based database built into Python. No install needed.

import sqlite3
import pandas as pd

🏗️ 3. Create and Connect to a Database

conn = sqlite3.connect("store.db")  # creates or connects to a DB file
cursor = conn.cursor()

🧱 4. Create a Table

cursor.execute("""
CREATE TABLE IF NOT EXISTS products (
    id INTEGER PRIMARY KEY,
    name TEXT,
    price REAL,
    stock INTEGER
)
""")
conn.commit()

➕ 5. Insert Data

cursor.execute("INSERT INTO products (name, price, stock) VALUES (?, ?, ?)", 
               ("Ring", 2500, 10))
cursor.execute("INSERT INTO products (name, price, stock) VALUES (?, ?, ?)", 
               ("Necklace", 1800, 5))
conn.commit()

🔍 6. Query Data from the Table

cursor.execute("SELECT * FROM products")
rows = cursor.fetchall()
for row in rows:
    print(row)

📊 7. Use Pandas to Query and Analyze

df = pd.read_sql("SELECT * FROM products", conn)
print(df)

# Filter products with low stock
print(df[df["stock"] < 6])

🧪 8. Practice Time

Try these:

• Create a sales table with product_id, date, quantity
• Write a query to join products and sales
• Calculate total revenue per product
• Use Pandas to show the top-selling product

✅ Summary

• sqlite3.connect() connects you to a database
• SQL handles inserts, filters, and joins
• Use pandas.read_sql() to turn your queries into DataFrames
• Combine SQL for structure + Python for insight