Database Programming

Every meaningful application needs to store data permanently and reliably — user accounts, orders, student records. While File Handling (covered earlier) works for simple cases, real applications use databases, which are built specifically for storing, searching, and managing large amounts of structured data efficiently and safely.

This file first teaches SQL fundamentals — the language databases understand — and then shows how to use it from Python.

1. What is a Database?

What is it?

A database is an organized collection of structured data, stored so it can be efficiently accessed, managed, and updated.

Definition: A database is a structured collection of data that can be efficiently accessed, managed, and updated.

Why do we use it?

• Reliability — databases are built to avoid data loss or corruption, even during crashes.
• Efficiency — finding one record among millions is fast, thanks to how databases organize and index data.
• Concurrent access — multiple users/programs can safely read and write data at the same time.
• Structured relationships — data like "students" and "courses" can be linked together meaningfully.

Tables, Rows, and Columns

A relational database organizes data into tables — similar to a spreadsheet.

Example — a `students` table:

Primary Keys

What is it?

A primary key is a column that uniquely identifies each row in a table — no two rows can share the same primary key value.

sql
CREATE TABLE students (
    id INTEGER PRIMARY KEY,
    name TEXT,
    age INTEGER,
    course TEXT
);

Important Points

• Every table should have a primary key (often an auto-incrementing id).
• Primary keys are how databases uniquely identify and link rows to each other.

2. CRUD Operations in SQL

CRUD stands for Create, Read, Update, Delete — the four fundamental operations performed on data.

2.1 INSERT (Create)

sql
INSERT INTO students (name, age, course)
VALUES ('Aditi', 21, 'Computer Science');

Explanation: This adds a new row to the students table. The id column is usually left out and auto-generated by the database.

2.2 SELECT (Read)

sql
SELECT * FROM students;                    -- get all columns, all rows
SELECT name, age FROM students;            -- get specific columns only
SELECT * FROM students WHERE age > 21;     -- get rows matching a condition

2.3 UPDATE

sql
UPDATE students
SET age = 22
WHERE name = 'Aditi';

Explanation: This changes existing data. The WHERE clause is critical — without it, every row in the table would be updated.

2.4 DELETE

sql
DELETE FROM students
WHERE name = 'Rohan';

Explanation: Removes matching rows. Just like UPDATE, forgetting WHERE here would delete every row in the table.

Common Mistakes

• Forgetting the WHERE clause in UPDATE or DELETE — this is one of the most dangerous and common real-world SQL mistakes, potentially wiping or overwriting an entire table.
• Forgetting the semicolon ; at the end of SQL statements (required by most database tools).

Important Points

• Always double-check your WHERE clause before running UPDATE or DELETE in a real system.
• SELECT * retrieves all columns — in large tables/production systems, it's often better to select only the specific columns you need.

3. WHERE — Filtering Data

What is it?

WHERE filters which rows are affected by SELECT, UPDATE, or DELETE.

Simple Example

sql
SELECT * FROM students WHERE course = 'Computer Science';
SELECT * FROM students WHERE age >= 21 AND course = 'Data Science';
SELECT * FROM students WHERE name LIKE 'A%';    -- names starting with "A"

Explanation: LIKE 'A%' is a pattern match — % acts as a wildcard for "any characters," so this finds every name starting with "A."

Comparison Operators in SQL

4. ORDER BY — Sorting Results

Simple Example

sql
SELECT * FROM students ORDER BY age;             -- ascending (default)
SELECT * FROM students ORDER BY age DESC;         -- descending
SELECT * FROM students ORDER BY course, age DESC; -- multiple columns

5. GROUP BY — Aggregating Data

What is it?

GROUP BY groups rows sharing a common value, usually combined with aggregate functions like COUNT(), SUM(), AVG().

Simple Example

sql
SELECT course, COUNT(*) AS total_students
FROM students
GROUP BY course;

Sample Result:

course             | total_students
Computer Science   | 15
Data Science       | 10

Explanation: This counts how many students belong to each course, grouping rows by the course column.

Common Aggregate Functions

6. JOIN — Combining Data From Multiple Tables

What is it?

A JOIN combines rows from two related tables based on a shared column (typically a primary key in one table matching a "foreign key" in another).

Example Setup

sql
CREATE TABLE students (
    id INTEGER PRIMARY KEY,
    name TEXT
);

CREATE TABLE enrollments (
    id INTEGER PRIMARY KEY,
    student_id INTEGER,
    course TEXT,
    FOREIGN KEY (student_id) REFERENCES students(id)
);

INNER JOIN — Get Matching Rows From Both Tables

sql
SELECT students.name, enrollments.course
FROM students
INNER JOIN enrollments ON students.id = enrollments.student_id;

Sample Result:

name  | course
Aditi | Computer Science
Rohan | Data Science

Explanation: This links each student to their course by matching students.id with enrollments.student_id — without a JOIN, you'd have to manually cross-reference two separate tables yourself.

Important Points

• JOIN is central to relational databases — data is often deliberately split across multiple related tables (this is called "normalization") to avoid repetition.
• A "foreign key" is a column in one table that refers to the primary key of another table, defining the relationship between them.

7. Indexes

What is it?

An index is a special data structure that dramatically speeds up searches on a specific column — similar to an index at the back of a textbook, letting you find information without scanning every single page.

sql
CREATE INDEX idx_student_name ON students(name);

Important Points

• Indexes speed up SELECT/WHERE queries on the indexed column significantly.
• Indexes have a cost too — they slow down INSERT/UPDATE slightly, since the index itself needs updating. Use them on columns you search frequently, not on every column.

8. Transactions

What is it?

A transaction is a group of database operations treated as a single, all-or-nothing unit — either all of them succeed together, or none of them take effect at all.

Definition: A transaction is a sequence of database operations that are executed as a single unit, ensuring data consistency even if something fails partway through.

Real-World Example — Bank Transfer

Transferring money from Account A to Account B involves two steps: subtract from A, add to B. If the program crashes after subtracting from A but before adding to B, money would simply vanish — unless both steps are wrapped in a single transaction, which ensures either both happen or neither does.

sql
BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

COMMIT;

Explanation: COMMIT finalizes both changes together. If something goes wrong before COMMIT, a ROLLBACK would undo everything back to how it was before the transaction started.

Important Points

• Transactions guarantee consistency, especially for operations involving multiple related steps.
• COMMIT finalizes changes; ROLLBACK undoes them if something goes wrong.

Comparison Table — SQL Concepts Summary

9. Python + SQLite

What is it?

SQLite is a lightweight, file-based database — no separate server needed, making it perfect for learning, small applications, and even many production use cases. Python includes built-in support via the sqlite3 module — no installation required.

Full CRUD Example

python
import sqlite3

# Connect (creates the file if it doesn't already exist)
connection = sqlite3.connect("school.db")
cursor = connection.cursor()

# Create a table
cursor.execute("""
CREATE TABLE IF NOT EXISTS students (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    age INTEGER,
    course TEXT
)
""")

# INSERT (Create)
cursor.execute(
    "INSERT INTO students (name, age, course) VALUES (?, ?, ?)",
    ("Aditi", 21, "Computer Science")
)
connection.commit()   # save the change

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

# UPDATE
cursor.execute(
    "UPDATE students SET age = ? WHERE name = ?",
    (22, "Aditi")
)
connection.commit()

# DELETE
cursor.execute("DELETE FROM students WHERE name = ?", ("Rohan",))
connection.commit()

connection.close()

Explanation of the Code

• sqlite3.connect("school.db") opens (or creates) the database file, and cursor is used to actually execute SQL commands.
• The ? placeholders in each query are parameterized queries — Python safely inserts the actual values, protecting against SQL injection attacks (a serious security topic, covered in the Security file later).
• connection.commit() saves any changes (INSERT, UPDATE, DELETE) permanently — without it, changes are lost when the connection closes.
• cursor.fetchall() retrieves every row from the last SELECT query as a list of tuples.

Common Mistakes

• Never build SQL queries using plain string formatting/f-strings with user input (e.g., f"SELECT * FROM students WHERE name = '{name}'") — this is a serious security vulnerability (SQL injection). Always use ? placeholders instead.
• Forgetting connection.commit() after INSERT/UPDATE/DELETE, causing changes to silently not be saved.
• Forgetting to connection.close() when done.

Important Points

• sqlite3 requires no separate installation or server — perfect for learning and small projects.
• Always use parameterized queries (? placeholders) — never insert user input directly into a query string.

Practice

1. Create a SQLite database with a products table (id, name, price), insert 3 products, then query and print all of them.

10. Python + MySQL and PostgreSQL

What is it?

MySQL and PostgreSQL are full-featured database servers, commonly used in production applications (unlike SQLite, they run as a separate server process, supporting many simultaneous users).

Connecting to MySQL

python
# pip install mysql-connector-python
import mysql.connector

connection = mysql.connector.connect(
    host="localhost",
    user="root",
    password="your_password",
    database="school_db"
)
cursor = connection.cursor()

cursor.execute("SELECT * FROM students")
for row in cursor.fetchall():
    print(row)

connection.close()

Connecting to PostgreSQL

python
# pip install psycopg2
import psycopg2

connection = psycopg2.connect(
    host="localhost",
    user="postgres",
    password="your_password",
    dbname="school_db"
)
cursor = connection.cursor()

cursor.execute("SELECT * FROM students")
for row in cursor.fetchall():
    print(row)

connection.close()

Comparison Table — SQLite vs MySQL vs PostgreSQL

Important Points

• The Python code pattern (connect, cursor, execute, fetchall, commit, close) is nearly identical across all three databases — learning one makes the others easy to pick up.
• Never hardcode real passwords directly in code — use environment variables instead (covered in the Security file).

11. SQLAlchemy (ORM)

What is it?

SQLAlchemy is an ORM (Object-Relational Mapper) — it lets you interact with a database using Python classes and objects instead of writing raw SQL directly.

Definition: An ORM lets you interact with database tables as if they were Python classes and objects, automatically translating your code into SQL behind the scenes.

Simple Example

python
from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import declarative_base, sessionmaker

engine = create_engine("sqlite:///school.db")
Base = declarative_base()

class Student(Base):
    __tablename__ = "students"
    id = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)
    course = Column(String)

Base.metadata.create_all(engine)   # creates the table if it doesn't exist

Session = sessionmaker(bind=engine)
session = session = Session()

# Create
new_student = Student(name="Zara", age=20, course="AI")
session.add(new_student)
session.commit()

# Read
students = session.query(Student).all()
for student in students:
    print(student.name, student.age, student.course)

# Update
student_to_update = session.query(Student).filter_by(name="Zara").first()
student_to_update.age = 21
session.commit()

# Delete
student_to_delete = session.query(Student).filter_by(name="Zara").first()
session.delete(student_to_delete)
session.commit()

Explanation of the Code

• class Student(Base): defines a Python class that maps directly to a students table — each attribute becomes a column.
• Instead of writing INSERT INTO ... manually, you just create a Student object and call session.add() — SQLAlchemy generates the actual SQL behind the scenes.
• .query(Student).filter_by(...) replaces writing a SELECT ... WHERE ... statement by hand.

Comparison Table — Raw SQL vs ORM (SQLAlchemy)

Important Points

• ORMs reduce repetitive SQL writing and make code more "Pythonic," but understanding underlying SQL fundamentals (covered earlier in this file) is still essential.
• SQLAlchemy is the most widely used ORM in the Python ecosystem, and is used heavily in frameworks like Flask and FastAPI.

Common Beginner Mistakes — Summary for This Section

• Forgetting the WHERE clause in UPDATE/DELETE, unintentionally affecting every row.
• Building SQL queries with raw string formatting instead of parameterized queries — a serious security risk (SQL injection).
• Forgetting connection.commit() after making changes.
• Forgetting to close database connections.

Cheat Sheet — Database Programming

sql
-- SQL
SELECT * FROM table WHERE condition ORDER BY column;
INSERT INTO table (col1, col2) VALUES (val1, val2);
UPDATE table SET col1 = val1 WHERE condition;
DELETE FROM table WHERE condition;
SELECT col, COUNT(*) FROM table GROUP BY col;
SELECT a.x, b.y FROM a INNER JOIN b ON a.id = b.a_id;

python
# Python + sqlite3
import sqlite3
conn = sqlite3.connect("db.db")
cursor = conn.cursor()
cursor.execute("SELECT * FROM table WHERE col = ?", (value,))
rows = cursor.fetchall()
conn.commit()
conn.close()

Mini Project: Student Management System

Objective

Build a command-line application that manages student records using a real SQLite database, with full CRUD functionality.

Requirements

• Add a student, view all students, update a student's marks, and delete a student.
• All data must persist in an actual SQLite database file.

Concepts Used

SQL (CREATE TABLE, INSERT, SELECT, UPDATE, DELETE), sqlite3 module, functions, parameterized queries.

Complete Code

python
import sqlite3

def connect_db():
    connection = sqlite3.connect("students.db")
    connection.execute("""
        CREATE TABLE IF NOT EXISTS students (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            marks REAL
        )
    """)
    return connection

def add_student(connection, name, marks):
    connection.execute("INSERT INTO students (name, marks) VALUES (?, ?)", (name, marks))
    connection.commit()
    print(f"Added student: {name}")

def view_students(connection):
    cursor = connection.execute("SELECT id, name, marks FROM students")
    rows = cursor.fetchall()
    if not rows:
        print("No students found.")
        return
    for row in rows:
        print(f"ID: {row[0]}, Name: {row[1]}, Marks: {row[2]}")

def update_marks(connection, student_id, new_marks):
    connection.execute("UPDATE students SET marks = ? WHERE id = ?", (new_marks, student_id))
    connection.commit()
    print("Marks updated.")

def delete_student(connection, student_id):
    connection.execute("DELETE FROM students WHERE id = ?", (student_id,))
    connection.commit()
    print("Student deleted.")

connection = connect_db()

while True:
    print("\n1. Add Student  2. View Students  3. Update Marks  4. Delete Student  5. Exit")
    choice = input("Choose an option: ")

    if choice == "1":
        name = input("Name: ")
        marks = float(input("Marks: "))
        add_student(connection, name, marks)
    elif choice == "2":
        view_students(connection)
    elif choice == "3":
        student_id = int(input("Student ID: "))
        new_marks = float(input("New Marks: "))
        update_marks(connection, student_id, new_marks)
    elif choice == "4":
        student_id = int(input("Student ID to delete: "))
        delete_student(connection, student_id)
    elif choice == "5":
        connection.close()
        print("Goodbye!")
        break
    else:
        print("Invalid choice.")

Code Explanation

• connect_db() opens the database (creating students.db if it doesn't exist) and ensures the students table exists.
• Every write operation (add_student, update_marks, delete_student) uses parameterized ? placeholders — safe from SQL injection.
• The menu loop lets the user perform CRUD operations repeatedly until choosing to exit, at which point the connection is properly closed.

Sample Output

1. Add Student  2. View Students  3. Update Marks  4. Delete Student  5. Exit
Choose an option: 1
Name: Aditi
Marks: 88.5
Added student: Aditi

1. Add Student  2. View Students  3. Update Marks  4. Delete Student  5. Exit
Choose an option: 2
ID: 1, Name: Aditi, Marks: 88.5

Possible Improvements

• Add a search function to find a student by name using LIKE.
• Add a course column and a GROUP BY course summary report.
• Convert the raw SQL version into an SQLAlchemy ORM version as practice.

Challenge Task

Add a second table subjects linked to students via a foreign key, and use a JOIN to display each student alongside their enrolled subjects.

Interview Questions

Q1. What does CRUD stand for? Answer: Create, Read, Update, Delete — the four fundamental operations performed on data.

Q2. Why is forgetting the `WHERE` clause in an `UPDATE` or `DELETE` statement dangerous? Answer: Without WHERE, the operation applies to every row in the table, potentially overwriting or deleting all existing data unintentionally.

Q3. What is a primary key? Answer: A column (or set of columns) that uniquely identifies each row in a table — no two rows can share the same primary key value.

Q4. What is a `JOIN` used for? Answer: Combining related data stored across multiple tables, typically by matching a primary key in one table with a foreign key in another.

Q5. What is SQL injection, and how do parameterized queries prevent it? Answer: SQL injection is a security vulnerability where malicious input is inserted directly into a SQL query string, potentially altering its meaning or exposing/damaging data. Parameterized queries (using ? placeholders) keep user input strictly separate from the SQL command structure, preventing this.

Q6. What is a transaction, and why does it matter? Answer: A transaction groups multiple database operations into a single all-or-nothing unit, ensuring data stays consistent even if something fails partway through (e.g., a bank transfer where both the debit and credit must succeed together).

Q7. What is an ORM, and name one example in Python? Answer: An Object-Relational Mapper lets you interact with database tables using Python classes and objects instead of writing raw SQL directly. SQLAlchemy is the most widely used example in Python.

Practice Questions

Beginner

1. Write a SQL query to select all students older than 20.
2. Write a SQL query to update a student's course.
3. Write a SQL query to delete a student by their ID.
4. Using sqlite3, create a database with a books table and insert 3 books.
5. Write a query using ORDER BY to sort students by age, descending.

Intermediate

1. Write a SQL query using GROUP BY to count how many students are enrolled in each course.
2. Using sqlite3 in Python, write functions to add, view, and delete records from a books table.
3. Write a query using JOIN to combine a students table and an enrollments table.
4. Write a Python program using parameterized queries to search for students by name (using LIKE).
5. Explain, using an example, why parameterized queries are important for security.

Challenge

1. Design a small database schema (in SQL) for a library system with books, members, and borrowed_books tables, including appropriate foreign keys.
2. Rewrite the Student Management System mini project using SQLAlchemy instead of raw SQL.
3. Write a Python program that wraps a bank transfer (subtracting from one account, adding to another) inside a transaction, and explain what would happen if the program crashed mid-transfer without transaction handling.