SQL with Python

On This Page

• 18.1  Why Combine SQL with Python?
• 18.2  Connecting to a Database
• 18.3  Running Queries
• 18.4  Importing Data into Pandas
• 18.5  Exporting Results Back to SQL
• 18.6  Practical Examples
• 18.7  Practical Exercises
• 18.8  Module Quiz (MCQs)
• 18.9  Module Assignment
• 18.10  Interview Questions — Module 18
• 18.11  Student Notes Page

Module 18: SQL with Python

18.1  Why Combine SQL with Python?

Most real business data lives inside databases, not CSV files. Python lets you connect directly to a database, run SQL queries, and pull results straight into Pandas for analysis — combining SQL's data-storage power with Python's analytical flexibility.

import sqlite3       # lightweight built-in database - perfect for learning
import pandas as pd
# For production databases: pip install sqlalchemy pymysql / psycopg2

18.2  Connecting to a Database

This module uses SQLite — a lightweight, file-based database built into Python, ideal for learning. The same concepts apply directly to MySQL, PostgreSQL, or SQL Server in real jobs, just with a different connector library.

import sqlite3

conn = sqlite3.connect("institute.db")   # creates the file if it doesn't exist
cursor = conn.cursor()
print("Connected successfully!")

18.3  Running Queries

cursor.execute("""
CREATE TABLE IF NOT EXISTS students (
    id INTEGER PRIMARY KEY,
    name TEXT,
    course TEXT,
    fee INTEGER
)
""")

cursor.execute("INSERT INTO students VALUES (1, 'Priya', 'SEO', 12000)")
cursor.execute("INSERT INTO students VALUES (2, 'Rahul', 'Google Ads', 15000)")
conn.commit()   # save changes to the database

cursor.execute("SELECT * FROM students WHERE fee > 12000")
results = cursor.fetchall()
for row in results:
    print(row)

18.4  Importing Data into Pandas

Pandas can run a SQL query and load the results directly into a DataFrame in a single line — this is the most common way analysts pull data for analysis.

import pandas as pd

df = pd.read_sql_query("SELECT * FROM students", conn)
print(df)
print(df.describe())

18.5  Exporting Results Back to SQL

After cleaning or transforming data in Pandas, you can write it straight back into a SQL table.

df["fee_with_gst"] = df["fee"] * 1.18

df.to_sql("students_updated", conn, if_exists="replace", index=False)
print("Data written back to database successfully!")

18.6  Practical Examples

import sqlite3
import pandas as pd

conn = sqlite3.connect("institute.db")

query = """
SELECT course, SUM(fee) as total_revenue, COUNT(*) as student_count
FROM students
GROUP BY course
ORDER BY total_revenue DESC
"""

report = pd.read_sql_query(query, conn)
print(report)

conn.close()   # always close the connection when done

18.7  Practical Exercises

Basic (5 Questions)

1. Connect to a new SQLite database file and create a students table.

2. Insert 5 rows of sample student data into your table.

3. Write a SELECT query to fetch all rows from your table.

4. Write a SELECT query with a WHERE clause to filter by course.

5. Load your entire table into a Pandas DataFrame using read_sql_query().

Intermediate (5 Questions)

1. Write a query using GROUP BY to calculate total fee per course directly in SQL.

2. Load a table into Pandas, clean/transform it, and write it back using to_sql().

3. Use an UPDATE query to change one student's course, then verify the change.

4. Use a DELETE query to remove one row, then confirm using a SELECT.

5. Combine a SQL query with a Pandas groupby() to double-check the same aggregation both ways.

Advanced (5 Questions)

1. Build a small 'Institute Database' with 2 related tables (students, payments) and write a JOIN query between them.

2. Load data using a JOIN query directly into Pandas and calculate outstanding balances per student.

3. Build a Python function run_report(query) that takes any SQL string and returns a clean Pandas DataFrame.

4. Write a script that reads a CSV, cleans it in Pandas, and loads the clean version into a new SQL table.

5. Build a simple 'Revenue Dashboard Query Tool' that lets you type a course name and returns that course's total revenue from the database.

18.8  Module Quiz (MCQs)

18.9  Module Assignment

Assignment: Institute Database Revenue Report

• Create a SQLite database with a students table (id, name, course, fee, enrollment_month).
• Insert at least 12 sample student records via Python.
• Write a SQL query using GROUP BY to calculate total revenue per course.
• Load that query result into Pandas, add a Fee_with_GST column, and write it back as a new table.
• Export the final report as both a CSV file and a bar chart image.

Expected Output: A complete pipeline showing data flowing from SQL, into Pandas for transformation, and back out as both a database table and shareable files.

18.10  Interview Questions — Module 18

• Q: Why would a Data Analyst use both SQL and Python together?
  A: SQL is efficient for storing and querying large volumes of structured data directly from the source, while Python (via Pandas) adds cleaning, statistical analysis, visualization, and automation that SQL alone doesn't easily provide.
• Q: What does pd.read_sql_query() do?
  A: It executes a SQL query against a database connection and returns the results directly as a Pandas DataFrame, ready for analysis.
• Q: Why is conn.commit() necessary after modifying data?
  A: Without committing, changes from INSERT, UPDATE, or DELETE statements remain temporary and are not permanently saved to the database.
• Q: What is the difference between if_exists='replace' and if_exists='append' in to_sql()?
  A: 'replace' completely overwrites an existing table with new data, while 'append' adds the new rows on top of the existing table's data without deleting anything.

18.11  Student Notes Page

Use this space to write down key points, doubts, and your own examples from today's session.