sqlalchemy
SQL toolkit and Object-Relational Mapping (ORM) library for Python — provides both Core (SQL expression language) and ORM (mapped classes) layers. SQLAlchemy 2.x features: declarative_base() / DeclarativeBase for model definition, relationship() for associations, Session for unit-of-work ORM, select()/insert()/update()/delete() for Core SQL, engine = create_engine(url) for connection management, Alembic integration for migrations, AsyncSession/create_async_engine for async support, Inspector for schema reflection, Connection.execute() for raw SQL, and type annotations with Mapped[] in 2.0 style.
Score Breakdown
⚙ Agent Friendliness
🔒 Security
ORM library. Use parameterized queries (select(), text() with parameters) — never string format SQL. Connection URL contains credentials — use environment variables, not hardcoded. Connection pooling: set pool_recycle for long-running apps with MySQL. Encrypt connection with SSL: create_engine(url, connect_args={'sslmode': 'require'}).
⚡ Reliability
Best When
Production Python applications needing SQL database access — SQLAlchemy 2.x is the standard for Python ORM with excellent performance, async support, and connection pooling for PostgreSQL, MySQL, and SQLite.
Avoid When
NoSQL databases (use motor/pymongo), simple one-time scripts (use sqlite3), or when peewee/tortoise-orm simpler API suffices.
Use Cases
- • Agent ORM models — from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship; class Base(DeclarativeBase): pass; class User(Base): __tablename__ = 'users'; id: Mapped[int] = mapped_column(primary_key=True); name: Mapped[str]; posts: Mapped[list['Post']] = relationship(back_populates='user') — model definition; agent defines database schema as Python classes
- • Agent CRUD operations — with Session(engine) as session: user = User(name='Alice'); session.add(user); session.commit(); users = session.execute(select(User).where(User.name == 'Alice')).scalars().all() — ORM CRUD; agent creates, reads, updates, deletes records; Session handles transactions; commit() persists
- • Agent async ORM — from sqlalchemy.ext.asyncio import AsyncSession, create_async_engine; engine = create_async_engine('postgresql+asyncpg://...'); async with AsyncSession(engine) as session: result = await session.execute(select(User)); users = result.scalars().all() — async; agent uses async SQLAlchemy with asyncpg/aiosqlite
- • Agent SQL Core — from sqlalchemy import create_engine, text; engine = create_engine(url); with engine.connect() as conn: result = conn.execute(text('SELECT * FROM users WHERE id = :id'), {'id': user_id}); rows = result.fetchall() — raw SQL; agent executes parameterized queries safely; text() is safe (parameterized)
- • Agent schema reflection — from sqlalchemy import inspect; inspector = inspect(engine); tables = inspector.get_table_names(); columns = inspector.get_columns('users') — reflection; agent introspects existing database schema; useful for dynamic queries on unknown schemas or database tooling
Not For
- • NoSQL databases — SQLAlchemy is SQL-only; for MongoDB use motor/pymongo, for Redis use redis-py
- • Simple SQLite scripts — for simple SQLite, stdlib sqlite3 or peewee is lighter
- • Async MongoDB — use motor; SQLAlchemy does not support document stores
Interface
Authentication
No auth — ORM library. Database credentials passed via connection URL.
Pricing
SQLAlchemy is MIT licensed. Free for all use.
Agent Metadata
Known Gotchas
- ⚠ SQLAlchemy 2.0 style vs 1.x legacy — session.query(User).all() is 1.x legacy; 2.x style: session.execute(select(User)).scalars().all(); both work in 2.x but 2.x style is preferred; agent code: use select() + session.execute() for new code; result.scalars() for ORM objects; result.mappings() for dict-like rows
- ⚠ Lazy loading causes N+1 in non-async — session.execute(select(User)).scalars().all() then user.posts for each user triggers one query per user (N+1); use eager loading: select(User).options(selectinload(User.posts)); or joinedload() for JOIN; agent code: always specify loading strategy for relationships used in loops
- ⚠ AsyncSession cannot use lazy loading — async SQLAlchemy with AsyncSession raises greenlet error on lazy relationship access; must use selectinload/joinedload eagerly; agent async code: select(User).options(selectinload(User.posts)) before executing; lazy loading is sync-only; greenlet_spawn error = forgot eager load
- ⚠ Session must be scoped to request — creating global Session causes cross-request data sharing; agent code: create new Session per request/operation using context manager: with Session(engine) as session:; scoped_session for thread-local scoping in multi-threaded apps; AsyncSession for async
- ⚠ commit() required to persist changes — session.add(obj) schedges for insert but does not execute; session.commit() flushes and commits transaction; session.flush() executes SQL without committing; agent code: after add/modify: session.commit() to persist; forget commit = data not saved, visible to same session but not others
- ⚠ Connection strings vary by driver — SQLite: 'sqlite:///db.sqlite3'; PostgreSQL+psycopg2: 'postgresql+psycopg2://user:pass@host/db'; PostgreSQL+asyncpg: 'postgresql+asyncpg://user:pass@host/db'; MySQL: 'mysql+pymysql://user:pass@host/db'; agent code: install driver separately (pip install psycopg2-binary or asyncpg); SQLAlchemy itself is driver-agnostic
Alternatives
Full Evaluation Report
Comprehensive deep-dive: security analysis, reliability audit, agent experience review, cost modeling, competitive positioning, and improvement roadmap for sqlalchemy.
AI-powered analysis · PDF + markdown · Delivered within 30 minutes
Package Brief
Quick verdict, integration guide, cost projections, gotchas with workarounds, and alternatives comparison.
Delivered within 10 minutes
Score Monitoring
Get alerted when this package's AF, security, or reliability scores change significantly. Stay ahead of regressions.
Continuous monitoring
Scores are editorial opinions as of 2026-03-06.