Mastering ORM with SQLAlchemy: A Hands‑On Python Database Guide

ORM Overview

ORM (Object‑Relational Mapping) uses metadata to describe how objects map to relational database tables. Business entities appear as objects in memory and as rows in a relational DB. Because most databases are relational, an ORM typically sits as a middle layer to handle object‑to‑table mapping.

When the underlying database changes (e.g., from MySQL to Oracle), rewriting large amounts of code is required. Using an ORM’s API abstracts the database, so only the API parameters need adjustment, avoiding extensive code changes.

SQLAlchemy Introduction

SQLAlchemy is an open‑source ORM for Python that provides high‑performance database access and a full‑featured enterprise‑grade model. It follows a mapping approach similar to Java’s Hibernate rather than the Active Record pattern used by some other ORMs.

Advantages

Flexible design, robust and adaptable code

Large community support

Rich plugins and extensions

Disadvantages

Heavy API with a steep learning curve

Complex syntax for advanced join queries

Setup and Initialization

Import required SQLAlchemy libraries

Initialize DBSession

Define Host object

Create all table structures

Base.metadata.create_all(engine)

Quick test

Basic Operations

Insert Data

Result displayed after insertion.

Commit Operation

# Rollback
# session.rollback()
# Commit changes
# session.commit()

Query Operations

1. Using query to retrieve data.

2. Filtering conditions (e.g., ==, !=, in, like, and_, or_). Note that and_ and or_ must be imported from SQLAlchemy. from sqlalchemy import or_, and_ 3. Returned values are lists.

4. Counting records requires importing func.

5. Additional useful methods:

group_by()   # group query
order_by()    # sort query

Update