◾️はじめに

Python の ORM（Object-Relational Mapping）の
SQLAlchemyについて、扱う

【０】Python ORM

ORMライブラリ	特徴
SQLAlchemy	最も有名・柔軟性が高い・本格的なORM
SQLModel	FastAPIの作者。SQLAlchemy + Pydantic の良いとこ取りをした設計。SQLAlchemyに統合される？
Prisma ORM	TypeScript/Node.js で人気の ORM。Python 版もあり、FastAPI との親和性が高いモダンな ORM
Django ORM	Django フレームワーク専用
Tortoise ORM	asyncio対応・軽量
Peewee	軽量・学習コストが低い

【１】SQLAlchemy

* Pythonで一番メジャーなORM

cf. Alchemy (アルケミー) = 錬金術

https://www.sqlalchemy.org

【２】インストール

pip install sqlalchemy

# For PostgreSQL
pip install psycopg2-binary

【３】サンプル

* 「例１：SQLite」と「例２：PostgreSQL」との大きな違いは、
　Connection情報だけなので、これだけでもORMを使うメリットがある
　（DBに依存しない実装が可能という意味で）

例１：SQLite

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


# Define table
class Base(DeclarativeBase):
    pass

class User(Base):
    __tablename__ = "users"

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

# Connect DB for SQLite
engine = create_engine("sqlite:///example.db", echo=True)

# Create table
Base.metadata.create_all(engine)

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

# Insert data
new_user = User(name="Mike", age=30)
session.add(new_user)
session.commit()

# Select
users = session.query(User).all()
for user in users:
    print(user.name, user.age)

session.close()

出力結果

2025-10-06 03:49:16,891 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2025-10-06 03:49:16,891 INFO sqlalchemy.engine.Engine PRAGMA main.table_info("users")
2025-10-06 03:49:16,892 INFO sqlalchemy.engine.Engine [raw sql] ()
2025-10-06 03:49:16,892 INFO sqlalchemy.engine.Engine PRAGMA temp.table_info("users")
2025-10-06 03:49:16,892 INFO sqlalchemy.engine.Engine [raw sql] ()
2025-10-06 03:49:16,892 INFO sqlalchemy.engine.Engine 
CREATE TABLE users (
        id INTEGER NOT NULL, 
        name VARCHAR, 
        age INTEGER, 
        PRIMARY KEY (id)
)


2025-10-06 03:49:16,892 INFO sqlalchemy.engine.Engine [no key 0.00003s] ()
2025-10-06 03:49:16,893 INFO sqlalchemy.engine.Engine COMMIT
2025-10-06 03:49:16,894 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2025-10-06 03:49:16,894 INFO sqlalchemy.engine.Engine INSERT INTO users (name, age) VALUES (?, ?)
2025-10-06 03:49:16,894 INFO sqlalchemy.engine.Engine [generated in 0.00006s] ('Mike', 30)
2025-10-06 03:49:16,895 INFO sqlalchemy.engine.Engine COMMIT
2025-10-06 03:49:16,896 INFO sqlalchemy.engine.Engine BEGIN (implicit)
2025-10-06 03:49:16,896 INFO sqlalchemy.engine.Engine SELECT users.id AS users_id, users.name AS users_name, users.age AS users_age 
FROM users
2025-10-06 03:49:16,896 INFO sqlalchemy.engine.Engine [generated in 0.00006s] ()
Mike 30

例２：PostgreSQL

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


# Define table
class Base(DeclarativeBase):
    pass

class User(Base):
    __tablename__ = "users"

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

DATABASE_URL = "postgresql://user:password@localhost:5432/demo_db"
engine = create_engine(DATABASE_URL, echo=True)
print("DATABASE_URL:", DATABASE_URL)
# Create table
Base.metadata.create_all(engine)

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

# Insert data
new_user = User(name="Mike", age=30)
session.add(new_user)
session.commit()

# Select
users = session.query(User).all()
for user in users:
    print(user.name, user.age)

session.close()