Databases in Python: SQL, ORM, CRUD, Transactions & NoSQL

TAKEAWAYS:

• Understand SQL vs. NoSQL databases, their use cases, and how Python interacts with them using libraries like sqlite3, psycopg2, and pymongo.
• Master CRUD operations (Create, Read, Update, Delete) in databases using Python, including parameterized queries to prevent SQL injection.
• Learn how to use transactions to ensure data integrity when multiple operations must succeed or fail together.
• Explore ORM (Object-Relational Mapping) tools like SQLAlchemy to map Python objects to database tables, reducing boilerplate SQL code.
• Know how to handle database errors and exceptions gracefully in Python applications.
• Apply database concepts to real-world scenarios like user authentication, inventory management, and financial transactions.

Introduction to Databases and Python

Databases are structured collections of data stored electronically, optimized for efficient retrieval, insertion, and deletion. Python provides multiple libraries to interact with databases, including SQLite (lightweight, file-based), PostgreSQL (powerful, open-source), MySQL (popular for web apps), and MongoDB (NoSQL document database).

Why Use Databases in Python?

• Persistence: Store data beyond the lifetime of a program.
• Scalability: Handle large volumes of data efficiently.
• Concurrency: Allow multiple users to access data simultaneously.
• Security: Protect sensitive data with access controls and encryption.

SQL vs. NoSQL Databases

Databases are broadly categorized into two types: relational (SQL) and non-relational (NoSQL).

SQL Databases (Relational)

• Use structured query language (SQL) for defining and manipulating data.
• Store data in tables with rows and columns.
• Enforce strict schemas (data types, constraints).
• Examples: MySQL, PostgreSQL, SQLite.

NoSQL Databases (Non-Relational)

• Do not use SQL; instead, use flexible data models like documents, key-value pairs, graphs, or wide-column stores.
• Schema-less or dynamic schemas.
• Scalable horizontally (across multiple servers).
• Examples: MongoDB, Cassandra, Redis.

Connecting Python to Databases

Python provides libraries to connect to databases:

1. SQLite: Built into Python, no server required. Useful for small applications or testing.

   import sqlite3
   conn = sqlite3.connect('example.db')
   cursor = conn.cursor()
   cursor.execute('''CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, name TEXT, email TEXT)''')
   conn.commit()
   conn.close()
   

2. PostgreSQL/MySQL: Requires external libraries like psycopg2 or mysql-connector.

   import psycopg2
   conn = psycopg2.connect(
       host="localhost",
       database="mydatabase",
       user="myuser",
       password="mypassword"
   )
   cursor = conn.cursor()
   cursor.execute('SELECT * FROM users')
   rows = cursor.fetchall()
   conn.close()
   

3. MongoDB: Use pymongo for NoSQL databases.

   from pymongo import MongoClient
   client = MongoClient('mongodb://localhost:27017/')
   db = client['mydatabase']
   collection = db['users']
   collection.insert_one({'name': 'Alice', 'email': 'alice@example.com'})
   

CRUD Operations in Databases

CRUD stands for Create, Read, Update, Delete—the four basic operations for managing data.

1. Create (Insert)

Add new records to a database table or collection.

# SQLite Example
cursor.execute("INSERT INTO users (name, email) VALUES (?, ?)", ('Bob', 'bob@example.com'))
conn.commit()

# MongoDB Example
collection.insert_one({'name': 'Charlie', 'email': 'charlie@example.com'})

2. Read (Query)

Retrieve data from a database.

# SQLite Example
cursor.execute("SELECT * FROM users WHERE name=?", ('Bob',))
rows = cursor.fetchall()
for row in rows:
    print(row)

# MongoDB Example
results = collection.find({'name': 'Alice'})
for doc in results:
    print(doc)

3. Update

Modify existing records.

# SQLite Example
cursor.execute("UPDATE users SET email=? WHERE name=?", ('new_email@example.com', 'Bob'))
conn.commit()

# MongoDB Example
collection.update_one({'name': 'Charlie'}, {'$set': {'email': 'new_charlie@example.com'}})

4. Delete

Remove records from a database.

# SQLite Example
cursor.execute("DELETE FROM users WHERE name=?", ('Bob',))
conn.commit()

# MongoDB Example
collection.delete_one({'name': 'Charlie'})

Parameterized Queries and SQL Injection

SQL Injection is a security vulnerability where malicious SQL code is inserted into a query. Always use parameterized queries to prevent this.

Bad (Vulnerable to SQL Injection)

name = "Bob'; DROP TABLE users;--"
cursor.execute(f"SELECT * FROM users WHERE name='{name}'")

Good (Safe)

name = "Bob'; DROP TABLE users;--"
cursor.execute("SELECT * FROM users WHERE name=?", (name,))

Transactions in Databases

A transaction is a sequence of operations performed as a single logical unit of work. Transactions ensure ACID properties:

• Atomicity: All operations succeed or fail together.
• Consistency: Database remains in a valid state.
• Isolation: Transactions do not interfere with each other.
• Durability: Completed transactions persist even after failures.

stateDiagram-v2
    [*] --> TransferStart
    TransferStart --> Withdraw100
    Withdraw100 --> CheckBalance
    CheckBalance --> Deposit100
    Deposit100 --> Commit
    Commit --> [*]
    Withdraw100 --> Error
    Error --> Rollback
    Rollback --> [*]
    state TransferStart {
        [*]: Start
    }
    state Error {
        [*]: Insufficient Funds
    }

Example: Transferring Money Between Accounts

try:
    cursor.execute("UPDATE accounts SET balance=balance-100 WHERE id=1")
    cursor.execute("UPDATE accounts SET balance=balance+100 WHERE id=2")
    conn.commit()  # Commit if both succeed
except:
    conn.rollback()  # Rollback if any fail

Object-Relational Mapping (ORM)

ORM tools like SQLAlchemy allow you to interact with databases using Python objects instead of writing raw SQL.

Example: SQLAlchemy

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

Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String)
    email = Column(String)

engine = create_engine('sqlite:///example.db')
Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()

# Create
new_user = User(name='Dave', email='dave@example.com')
session.add(new_user)
session.commit()

# Read
users = session.query(User).filter_by(name='Dave').all()
for user in users:
    print(user.name, user.email)

Error Handling in Databases

Always handle exceptions when working with databases to avoid crashes.

try:
    cursor.execute("SELECT * FROM nonexistent_table")
except sqlite3.Error as e:
    print(f"Database error: {e}")
finally:
    conn.close()

Common exceptions:

• sqlite3.Error: General SQLite errors.
• psycopg2.Error: PostgreSQL errors.
• pymongo.errors.PyMongoError: MongoDB errors.

In the Real World

1. eSewa (Nepal):

   • Uses SQL databases (likely PostgreSQL or MySQL) to store user transactions, payment records, and service bookings.
   • CRUD operations handle real-time updates when users book services (e.g., electricity, water) or make payments.
   • Transactions ensure that money is deducted from the user's account and credited to the service provider atomically.

2. Khalti (Nepal):

   • Relies on NoSQL databases (like MongoDB) for flexible schema requirements, such as storing diverse transaction types (wallet, bank transfers, UPI).
   • ORM tools (e.g., Django ORM or SQLAlchemy) map Python objects to database tables for seamless integration with their web and mobile apps.
   • Parameterized queries prevent SQL injection when validating user inputs like payment amounts or merchant IDs.

3. Ncell (Nepal):

   • Uses SQL databases to manage customer data, call logs, and billing information.
   • Transactions ensure that airtime purchases or data bundle activations are processed correctly without partial updates.
   • Indexing on columns like customer_id or phone_number speeds up queries for customer service representatives.

Worked Example: Inventory Management System

Assume you are building an inventory system for a small shop using SQLite. The system tracks products, their quantities, and sales.

Database Schema

erDiagram
    PRODUCTS ||--o{ SALES : contains
    PRODUCTS {
        int id PK
        string name
        float price
        int quantity
    }
    SALES {
        int id PK
        int product_id FK
        int quantity_sold
        datetime sale_time
    }

Python Code for CRUD Operations

import sqlite3

def init_db():
    conn = sqlite3.connect('inventory.db')
    cursor = conn.cursor()
    cursor.execute('''CREATE TABLE IF NOT EXISTS products
                      (id INTEGER PRIMARY KEY, name TEXT, price REAL, quantity INTEGER)''')
    cursor.execute('''CREATE TABLE IF NOT EXISTS sales
                      (id INTEGER PRIMARY KEY, product_id INTEGER, quantity_sold INTEGER, sale_time DATETIME)''')
    conn.commit()
    conn.close()

def add_product(name, price, quantity):
    conn = sqlite3.connect('inventory.db')
    cursor = conn.cursor()
    cursor.execute("INSERT INTO products (name, price, quantity) VALUES (?, ?, ?)", (name, price, quantity))
    conn.commit()
    conn.close()

def sell_product(product_id, quantity_sold):
    conn = sqlite3.connect('inventory.db')
    cursor = conn.cursor()
    try:
        # Update product quantity
        cursor.execute("UPDATE products SET quantity=quantity-? WHERE id=?", (quantity_sold, product_id))
        # Record sale
        cursor.execute("INSERT INTO sales (product_id, quantity_sold, sale_time) VALUES (?, ?, datetime('now'))", (product_id, quantity_sold))
        conn.commit()
        print("Sale recorded successfully!")
    except sqlite3.Error as e:
        conn.rollback()
        print(f"Error: {e}")
    finally:
        conn.close()

# Example usage
init_db()
add_product("Laptop", 50000, 10)
sell_product(1, 2)  # Sell 2 laptops

State After Each Operation

1. After init_db():

   • Tables products and sales are created if they don’t exist.

2. After add_product("Laptop", 50000, 10):

3. After sell_product(1, 2):

Indexing in Databases

An index is a data structure that improves the speed of data retrieval operations on a database table. Indexes are similar to the index in a book, allowing you to find data quickly without scanning the entire table.

When to Use Indexes

• Columns frequently used in WHERE, JOIN, or ORDER BY clauses.
• Columns with high cardinality (many unique values).

Example: Indexing a Product ID

cursor.execute("CREATE INDEX idx_product_id ON products(id)")

Impact of Indexing

Normalization and Database Design

Normalization is the process of organizing data in a database to minimize redundancy and dependency. The goal is to isolate data so that updates can be made in just one place.

Normal Forms

1. First Normal Form (1NF): Each table cell should contain a single value, and each record needs to be unique.
2. Second Normal Form (2NF): Meet 1NF and all non-key attributes must depend on the entire primary key (no partial dependencies).
3. Third Normal Form (3NF): Meet 2NF and no transitive dependencies (non-key attributes should not depend on other non-key attributes).

Example: Unnormalized vs. Normalized Database

Unnormalized (Redundant):

Normalized (3NF):

erDiagram
    CUSTOMERS ||--o{ ORDERS : places
    ORDERS ||--o{ ORDER_ITEMS : contains
    PRODUCTS ||--o{ ORDER_ITEMS : includes

    CUSTOMERS {
        int id PK
        string name
    }
    ORDERS {
        int id PK
        int customer_id FK
        datetime order_time
    }
    ORDER_ITEMS {
        int id PK
        int order_id FK
        int product_id FK
        int quantity
    }
    PRODUCTS {
        int id PK
        string name
        float price
    }

Exam Tip

1. Understand SQL vs. NoSQL: Know when to use each type of database. SQL is better for structured data with relationships, while NoSQL is ideal for flexible, unstructured data.

2. CRUD Operations: Be able to write Python code for all four operations (Create, Read, Update, Delete) using parameterized queries to avoid SQL injection.

3. Transactions: Practice writing code that uses commit() and rollback() to ensure data integrity.

4. ORM Tools: Familiarize yourself with SQLAlchemy or Django ORM to map Python objects to database tables.

5. Error Handling: Always include try-except blocks when working with databases to handle potential errors gracefully.

6. Real-World Scenarios: Relate database concepts to real-world applications like e-commerce (Daraz), banking (Nabil Bank), or ride-sharing (Pathao). For example:

   • Daraz: Uses SQL databases to manage product catalogs, user orders, and inventory. Transactions ensure that stock is updated and orders are processed correctly.
   • Nabil Bank: Relies on SQL databases for account management, loans, and transactions. Indexes on columns like account_number speed up queries for customer service.
   • Pathao: Uses NoSQL databases (like MongoDB) for dynamic data like driver locations and ride requests, which change frequently and don’t fit a rigid schema.

7. Diagrams: Be prepared to draw ER diagrams or explain database schemas in exams. Practice visualizing relationships between tables.

Summary

• Use SQLite for lightweight applications and PostgreSQL/MySQL for larger-scale projects.
• Always use parameterized queries to prevent SQL injection.
• Transactions ensure data consistency when multiple operations are involved.
• ORM tools like SQLAlchemy simplify database interactions by mapping Python objects to tables.
• Normalization reduces redundancy and improves data integrity.
• Indexing speeds up queries but can slow down writes. Use it wisely on frequently queried columns.