Learning Python - Persistence & Databases

#Introduction

Almost every real application stores data. Episode 9 introduces you to the world of persistence: how Python talks to relational databases through SQLAlchemy, manages schema changes with Alembic, and interacts with NoSQL databases like Redis and MongoDB.

We'll also cover connection pooling and transaction patterns that are the foundation of production-scale applications. This is the bridge to episode 13, where you build an API that actually stores data.

#SQLAlchemy Core and ORM

#Understanding the Two Styles

SQLAlchemy offers two programming styles:

• Core: writing SQL explicitly with Python expressions.
• ORM: mapping Python objects to database tables.

Both share the same foundation, so you can mix them. Let's see the difference.

#SQLAlchemy Core

Core uses SQL expressions as Python objects:

SQLAlchemy Core

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String
 
engine = create_engine("sqlite:///buku.db")
metadata = MetaData()
 
buku = Table(
    "buku",
    metadata,
    Column("id", Integer, primary_key=True),
    Column("judul", String),
)
 
metadata.create_all(engine)
print("tabel dibuat")

create_engine("sqlite:///buku.db") creates a connection to the database. Table defines the table structure explicitly. Core gives you full control over the SQL produced.

#SQLAlchemy ORM

The ORM maps Python classes to tables:

SQLAlchemy ORM

from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
from sqlalchemy import create_engine
 
class Base(DeclarativeBase):
    pass
 
class Buku(Base):
    __tablename__ = "buku"
    id: Mapped[int] = mapped_column(primary_key=True)
    judul: Mapped[str]
 
engine = create_engine("sqlite:///buku.db")
Base.metadata.create_all(engine)
print(Buku.__tablename__)

class Buku(Base) defines a model that inherits from DeclarativeBase. The Mapped[int] and Mapped[str] annotations determine the columns. The ORM reduces boilerplate and makes queries more natural, great for standard CRUD.

#Sessions and Transactions

#Creating a Session

A session is the ORM unit of work that manages transactions:

Menggunakan Session

from sqlalchemy.orm import Session
from sqlalchemy import create_engine
 
engine = create_engine("sqlite:///buku.db")
 
with Session(engine) as sesi:
    buku_baru = Buku(judul="Belajar Python")
    sesi.add(buku_baru)
    sesi.commit()
    print("disimpan")

with Session(engine) as sesi: opens a session that manages the connection and transactions. sesi.add(buku_baru) marks the object for saving, and sesi.commit() writes it to the database. The context manager guarantees the session is closed properly.

#Migrations with Alembic

#Creating and Applying Migrations

The migration workflow:

alembic revision --autogenerate -m "tambah tabel buku"
alembic upgrade head

alembic revision --autogenerate -m "tambah tabel buku" generates a revision file automatically from your models. alembic upgrade head applies all not-yet-run revisions. This way, schema changes can be applied consistently across all environments.

#Redis and MongoDB

#Redis: A Key-Value Store

Redis is an in-memory key-value store that's extremely fast for caching and queues. Its official driver is redis-py:

Menggunakan Redis

import redis
 
r = redis.Redis(host="localhost", port=6379, decode_responses=True)
r.set("nama", "Arman")
print(r.get("nama"))
 
r.rpush("antrian", "tugas-1")
r.rpush("antrian", "tugas-2")
print(r.lpop("antrian"))

redis.Redis(host="localhost", port=6379) creates a Redis client. r.set and r.get manage key-value pairs, while r.rpush and r.lpop manage queues. Redis is ideal for caching and task queues.

#MongoDB: A Document Store

MongoDB stores JSON-like documents. Its official driver is pymongo:

Menggunakan MongoDB

from pymongo import MongoClient
 
klien = MongoClient("mongodb://localhost:27017")
db = klien["belajar"]
koleksi = db["produk"]
 
koleksi.insert_one({"nama": "laptop", "harga": 15000000})
hasil = koleksi.find_one({"nama": "laptop"})
print(hasil["harga"])

MongoClient("mongodb://localhost:27017") connects to the MongoDB server. insert_one stores a document and find_one retrieves it by criteria. MongoDB suits flexible, semi-structured data.

#Connection Pooling

#Why Pooling Matters

Opening a database connection for every operation is expensive. Connection pooling reuses existing connections:

Pooling dengan SQLAlchemy

from sqlalchemy import create_engine
 
engine = create_engine(
    "postgresql+psycopg://user:pass@localhost/db",
    pool_size=10,
    max_overflow=20,
    pool_timeout=30,
)
print("pool dikonfigurasi")

pool_size=10 sets the base number of connections, max_overflow=20 the cap for additional connections, and pool_timeout=30 the wait time when the pool is full. Pooling prevents the application from running out of connections during high traffic — important for the API in episode 13.

#Closing

Key takeaways:

• SQLAlchemy Core gives explicit control; the ORM maps objects to tables.
• A Session manages transactions with commit and rollback.
• Alembic records schema changes as sequential migrations.
• Redis for fast key-value and queues; MongoDB for flexible documents.
• Connection pooling reuses connections for efficiency.
• Transactions keep the database consistent across multiple tables.

In the next episode, episode 10, we'll cover I/O, serialization, and data formats — file I/O with pathlib, processing CSV, JSON, and YAML, the security risks of pickle, and streaming data with generators and chunked processing. Your project's data starts flowing in and out of the system!