MODULE 12: Working with Databases

Databases are used to store, retrieve, update, and manage data efficiently. Python provides excellent support for both SQL databases and ORM-based approaches.

1. Database Basics

Brief

A database is an organized collection of data stored electronically.

Key Concepts

• Table → Rows & columns
• Row (Record) → Single entry
• Column (Field) → Data attribute
• Primary Key → Unique identifier
• Foreign Key → Relationship between tables

Types of Databases

• Relational (SQL) → MySQL, PostgreSQL, SQLite
• Non-Relational (NoSQL) → MongoDB, Redis

Why Use Databases

• Persistent storage
• Fast querying
• Data integrity
• Multi-user access

---

2. SQLite with Python

Brief

SQLite is a lightweight, file-based database included with Python.

---

Connecting to SQLite

import sqlite3

conn = sqlite3.connect("students.db")
cursor = conn.cursor()

---

Create Table

cursor.execute("""
CREATE TABLE IF NOT EXISTS students (
    id INTEGER PRIMARY KEY,
    name TEXT,
    age INTEGER
)
""")
conn.commit()

---

Insert Data

cursor.execute("INSERT INTO students (name, age) VALUES (?, ?)", ("Rahul", 22))
conn.commit()

---

Fetch Data

cursor.execute("SELECT * FROM students")
print(cursor.fetchall())

---

Close Connection

conn.close()

---

3. MySQL with Python

Brief

MySQL is a popular open-source relational database widely used in production.

---

Install Connector

pip install mysql-connector-python

---

Connect to MySQL

import mysql.connector

conn = mysql.connector.connect(
    host="localhost",
    user="root",
    password="password",
    database="company"
)
cursor = conn.cursor()

---

Insert Record

cursor.execute(
    "INSERT INTO employees (name, salary) VALUES (%s, %s)",
    ("Amit", 50000)
)
conn.commit()

---

Fetch Records

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

---

4. PostgreSQL Integration

Brief

PostgreSQL is an advanced, enterprise-grade database.

---

Install psycopg2

pip install psycopg2-binary

---

Connect to PostgreSQL

import psycopg2

conn = psycopg2.connect(
    dbname="testdb",
    user="postgres",
    password="password",
    host="localhost"
)
cursor = conn.cursor()

---

Execute Query

cursor.execute("SELECT version()")
print(cursor.fetchone())

---

Commit & Close

conn.commit()
conn.close()

---

5. ORM Basics

Brief

ORM (Object Relational Mapping) lets you interact with databases using Python objects instead of SQL queries.

---

Why ORM

• Cleaner code
• Database-agnostic
• Prevents SQL injection
• Faster development

---

ORM vs SQL

SQL	ORM
Manual queries	Python objects
Error-prone	Safer
DB-specific	Portable

---

6. SQLAlchemy

Brief

SQLAlchemy is a powerful Python ORM.

---

Install SQLAlchemy

pip install sqlalchemy

---

Create Engine & Base

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

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

---

Define Model

class User(Base):
    __tablename__ = "users"

    id = Column(Integer, primary_key=True)
    name = Column(String)
    age = Column(Integer)

---

Create Table

Base.metadata.create_all(engine)

---

Create Session

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

---

7. CRUD Operations

Create

user = User(name="Prakash", age=25)
session.add(user)
session.commit()

---

Read

users = session.query(User).all()
for user in users:
    print(user.name)

---

Update

user = session.query(User).filter_by(name="Prakash").first()
user.age = 26
session.commit()

---

Delete

user = session.query(User).filter_by(name="Prakash").first()
session.delete(user)
session.commit()