# SQLAlchemy — The Python SQL Toolkit and ORM

> The most widely used Python database library, providing both a low-level SQL expression language and a high-level ORM with unit of work, identity map, and eager loading.

## Install

Save as a script file and run:

# SQLAlchemy — The Python SQL Toolkit and ORM

## Quick Use
```bash
pip install sqlalchemy psycopg2-binary
python -c "
from sqlalchemy import create_engine, text
engine = create_engine('postgresql://user:pass@localhost/mydb')
with engine.connect() as conn:
    result = conn.execute(text('SELECT 1'))
    print(result.scalar())
"
```

## Introduction
SQLAlchemy is the standard database toolkit for Python, used by Flask, FastAPI, and thousands of production applications. It uniquely provides two complementary layers: a Core expression language for SQL power users and an ORM for developers who prefer working with Python objects.

## What SQLAlchemy Does
- Provides a SQL expression language that compiles Python expressions into parameterized SQL
- Offers a full ORM with unit of work pattern, identity map, and relationship loading strategies
- Supports PostgreSQL, MySQL, MariaDB, SQLite, Oracle, and SQL Server through dialects
- Handles connection pooling, transaction management, and database migrations via Alembic
- Supports async I/O with asyncio through asyncpg, aiomysql, and aiosqlite drivers

## Architecture Overview
SQLAlchemy is organized into Core and ORM layers. Core provides Engine (connection management), MetaData (schema representation), and the SQL Expression Language (query construction). The ORM adds the Session (unit of work), Mapper (class-to-table mapping), and Query/Select (object-level querying). The Engine delegates to a Pool of DBAPI connections, and Dialect objects translate SQL expressions into database-specific syntax.

## Self-Hosting & Configuration
- Install via pip alongside a DBAPI driver (psycopg2, mysqlclient, pymysql)
- Create an Engine with a connection URL specifying driver, host, database, and pool settings
- Configure pool_size, max_overflow, and pool_recycle for production workloads
- Use Alembic for migration management with auto-generated migration scripts
- Enable echo=True on the Engine for SQL logging during development

## Key Features
- Dual-layer architecture letting you mix raw SQL power with ORM convenience
- Alembic integration for production-grade schema migration management
- Async support with native asyncio engine and session
- Comprehensive relationship loading: lazy, eager, subquery, selectin, and joined strategies
- Event system with hooks into connection, session, mapper, and attribute events

## Comparison with Similar Tools
- **Django ORM** — tightly coupled to Django; SQLAlchemy works with any Python framework
- **Peewee** — simpler and lighter; SQLAlchemy handles more complex query patterns
- **Tortoise ORM** — async-first Python ORM; SQLAlchemy added async support while keeping sync compatibility
- **Prisma (Python)** — generated client approach; SQLAlchemy is code-first with more flexibility
- **SQLModel** — built on SQLAlchemy with Pydantic integration; SQLAlchemy is the lower-level foundation

## FAQ
**Q: Should I use Core or ORM?**
A: Use the ORM for application data modeling with relationships. Use Core for ETL, bulk operations, or when you want explicit SQL control. You can mix both.

**Q: How does SQLAlchemy handle the N+1 problem?**
A: Configure relationship loading strategies: joinedload for JOIN-based eager loading, selectinload for separate IN queries, or subqueryload for subquery-based loading.

**Q: Is SQLAlchemy async-ready?**
A: Yes. SQLAlchemy 2.0 supports native async with create_async_engine and AsyncSession, using asyncpg or aiomysql under the hood.

**Q: What is Alembic?**
A: Alembic is SQLAlchemy's migration tool. It auto-generates migration scripts by comparing your models to the database schema and applies them in order.

## Sources
- https://github.com/sqlalchemy/sqlalchemy
- https://www.sqlalchemy.org

---
Source: https://tokrepo.com/en/workflows/8fa9195c-39e1-11f1-9bc6-00163e2b0d79
Author: Script Depot