0
# SQLAlchemy
1

2
SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that gives application developers the full power and flexibility of SQL. It provides an industrial-strength ORM built on the identity map, unit of work, and data mapper patterns for transparent object persistence using declarative configuration.
3

4
## Package Information
5

6
- **Package Name**: SQLAlchemy
7
- **Language**: Python
8
- **Installation**: `pip install sqlalchemy`
9
- **Documentation**: https://docs.sqlalchemy.org
10

11
## Core Imports
12

13
```python
14
import sqlalchemy
15
```
16

17
Common patterns for Core (SQL Expression Language):
18

19
```python
20
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, select, insert, update, delete
21
```
22

23
Common patterns for ORM:
24

25
```python
26
from sqlalchemy import create_engine
27
from sqlalchemy.orm import declarative_base, sessionmaker, Session, relationship
28
```
29

30
Modern ORM with 2.0 style:
31

32
```python
33
from sqlalchemy import create_engine
34
from sqlalchemy.orm import DeclarativeBase, mapped_column, Mapped, Session
35
```
36

37
## Basic Usage
38

39
### Core SQL Expression Language
40

41
```python
42
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, select
43

44
# Create engine and define table
45
engine = create_engine("sqlite:///example.db")
46
metadata = MetaData()
47

48
users = Table('users', metadata,
49
    Column('id', Integer, primary_key=True),
50
    Column('name', String(50)),
51
    Column('email', String(100))
52
)
53

54
# Create table
55
metadata.create_all(engine)
56

57
# Execute queries
58
with engine.connect() as conn:
59
    # Insert data
60
    result = conn.execute(
61
        users.insert().values(name='John Doe', email='john@example.com')
62
    )
63
    
64
    # Select data
65
    result = conn.execute(select(users).where(users.c.name == 'John Doe'))
66
    row = result.fetchone()
67
    print(f"User: {row.name}, Email: {row.email}")
68
```
69

70
### ORM Usage
71

72
```python
73
from sqlalchemy import create_engine, String, Integer
74
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session
75

76
# Define base and model
77
class Base(DeclarativeBase):
78
    pass
79

80
class User(Base):
81
    __tablename__ = 'users'
82
    
83
    id: Mapped[int] = mapped_column(primary_key=True)
84
    name: Mapped[str] = mapped_column(String(50))
85
    email: Mapped[str] = mapped_column(String(100))
86

87
# Create engine and tables
88
engine = create_engine("sqlite:///example.db")
89
Base.metadata.create_all(engine)
90

91
# Use session for ORM operations
92
with Session(engine) as session:
93
    # Create and add new user
94
    new_user = User(name="Jane Smith", email="jane@example.com")
95
    session.add(new_user)
96
    session.commit()
97
    
98
    # Query users
99
    users = session.query(User).filter(User.name.like('%Jane%')).all()
100
    for user in users:
101
        print(f"User: {user.name}, Email: {user.email}")
102
```
103

104
## Architecture
105

106
SQLAlchemy consists of two main layers:
107

108
- **Core**: SQL expression language providing database abstraction, connection management, and SQL construction
109
- **ORM**: Object-relational mapping layer built on Core, providing declarative mapping, session management, and relationship handling
110

111
Key components:
112

113
- **Engine**: Database connection management and execution
114
- **MetaData**: Schema definition and reflection
115
- **Session**: ORM unit of work and identity map
116
- **Dialect**: Database-specific SQL generation and behavior
117

118
## Capabilities
119

120
### Core Engine and Connectivity
121

122
Database engine creation, connection management, URL handling, connection pooling, and transaction management. The foundation for all SQLAlchemy database operations.
123

124
```python { .api }
125
def create_engine(url, **kwargs): ...
126
def make_url(name_or_url): ...
127

128
class Engine:
129
    def connect(self): ...
130
    def execute(self, statement, parameters=None): ...
131

132
class Connection:
133
    def execute(self, statement, parameters=None): ...
134
    def begin(self): ...
135
    def commit(self): ...
136
    def rollback(self): ...
137
```
138

139
[Core Engine](./core-engine.md)
140

141
### SQL Expression Language
142

143
Programmatic SQL construction with Python expressions, including SELECT, INSERT, UPDATE, DELETE statements, joins, subqueries, functions, and complex query composition.
144

145
```python { .api }
146
def select(*columns): ...
147
def insert(table): ...
148
def update(table): ...
149
def delete(table): ...
150

151
def and_(*clauses): ...
152
def or_(*clauses): ...
153
def not_(clause): ...
154

155
class Select:
156
    def where(self, *criteria): ...
157
    def join(self, target, onclause=None): ...
158
    def order_by(self, *clauses): ...
159
    def group_by(self, *clauses): ...
160
```
161

162
[SQL Expression Language](./sql-expression.md)
163

164
### Object Relational Mapping (ORM)
165

166
Declarative mapping, session management, relationship definitions, query API, and advanced ORM features including inheritance, polymorphism, and events.
167

168
```python { .api }
169
class DeclarativeBase: ...
170
def declarative_base(): ...
171

172
class Session:
173
    def add(self, instance): ...
174
    def query(self, *entities): ...
175
    def commit(self): ...
176
    def rollback(self): ...
177

178
def relationship(argument, **kwargs): ...
179
def mapped_column(*args, **kwargs): ...
180
```
181

182
[Object Relational Mapping](./orm.md)
183

184
### Schema Definition and Reflection
185

186
Table, column, and constraint definition, database schema reflection, index management, and DDL generation for database structure management.
187

188
```python { .api }
189
class MetaData:
190
    def create_all(self, bind): ...
191
    def reflect(self, bind): ...
192

193
class Table:
194
    def __init__(self, name, metadata, *args, **kwargs): ...
195

196
class Column:
197
    def __init__(self, *args, **kwargs): ...
198

199
class Index:
200
    def __init__(self, name, *expressions, **kwargs): ...
201
```
202

203
[Schema Definition](./schema.md)
204

205
### Type System and Data Types
206

207
SQL data type system including numeric, string, date/time, binary, and specialized types with database-specific variants and custom type creation.
208

209
```python { .api }
210
class Integer: ...
211
class String: ...
212
class DateTime: ...
213
class Boolean: ...
214
class JSON: ...
215

216
class TypeDecorator:
217
    def process_bind_param(self, value, dialect): ...
218
    def process_result_value(self, value, dialect): ...
219
```
220

221
[Type System](./types.md)
222

223
### Database Dialects
224

225
Database-specific implementations for PostgreSQL, MySQL, SQLite, Oracle, SQL Server with specialized types, functions, and features for each database.
226

227
```python { .api }
228
# PostgreSQL
229
from sqlalchemy.dialects.postgresql import ARRAY, JSON, UUID
230

231
# MySQL  
232
from sqlalchemy.dialects.mysql import MEDIUMINT, SET
233

234
# SQLite
235
from sqlalchemy.dialects.sqlite import JSON
236
```
237

238
[Database Dialects](./dialects.md)
239

240
### Async Support
241

242
Asynchronous database operations with async engines, connections, sessions, and ORM support for modern async Python applications.
243

244
```python { .api }
245
async def create_async_engine(url, **kwargs): ...
246

247
class AsyncEngine:
248
    async def connect(self): ...
249

250
class AsyncSession:
251
    async def commit(self): ...
252
    async def execute(self, statement): ...
253
```
254

255
[Async Support](./async.md)
256

257
### Extensions
258

259
SQLAlchemy extensions providing additional functionality for association proxies, hybrid properties, mutable tracking, automap, and other advanced features.
260

261
```python { .api }
262
# Association proxy for simplified relationship access
263
from sqlalchemy.ext.associationproxy import association_proxy
264

265
# Hybrid properties for computed attributes
266
from sqlalchemy.ext.hybrid import hybrid_property, hybrid_method
267

268
# Mutable tracking for container types
269
from sqlalchemy.ext.mutable import Mutable, MutableDict, MutableList, MutableSet
270

271
# Automap for automatic class generation from schema
272
from sqlalchemy.ext.automap import automap_base
273

274
# Compiler extensions for custom SQL constructs
275
from sqlalchemy.ext.compiler import compiles, deregister
276

277
# Horizontal sharding support
278
from sqlalchemy.ext.horizontal_shard import ShardedQuery, ShardedSession
279

280
# Other utilities
281
from sqlalchemy.ext.indexable import index_property
282
from sqlalchemy.ext.orderinglist import ordering_list
283
from sqlalchemy.ext.serializer import loads, dumps
284
```
285

286
### Events System
287

288
Comprehensive event system for hooking into SQLAlchemy operations at various points in the lifecycle.
289

290
```python { .api }
291
from sqlalchemy import event
292

293
# Event registration
294
def listen(target, identifier, fn, **kwargs): ...
295
def listens_for(target, identifier, **kwargs): ...  # decorator
296
def remove(target, identifier, fn): ...
297
def contains(target, identifier, fn): ...
298

299
# Engine and connection events
300
class PoolEvents:
301
    def connect(self, dbapi_connection, connection_record): ...
302
    def first_connect(self, dbapi_connection, connection_record): ...
303
    def checkout(self, dbapi_connection, connection_record, connection_proxy): ...
304
    def checkin(self, dbapi_connection, connection_record): ...
305
    def close(self, dbapi_connection, connection_record): ...
306

307
class ConnectionEvents:
308
    def before_cursor_execute(self, conn, cursor, statement, parameters, context, executemany): ...
309
    def after_cursor_execute(self, conn, cursor, statement, parameters, context, executemany): ...
310
    def begin(self, conn): ...
311
    def commit(self, conn): ...
312
    def rollback(self, conn): ...
313

314
# ORM events
315
class SessionEvents:
316
    def before_commit(self, session): ...
317
    def after_commit(self, session): ...
318
    def after_transaction_create(self, session, transaction): ...
319
    def before_flush(self, session, flush_context, instances): ...
320
    def after_flush(self, session, flush_context): ...
321

322
class MapperEvents:
323
    def before_insert(self, mapper, connection, target): ...
324
    def after_insert(self, mapper, connection, target): ...
325
    def before_update(self, mapper, connection, target): ...
326
    def after_update(self, mapper, connection, target): ...
327
    def before_delete(self, mapper, connection, target): ...
328
    def after_delete(self, mapper, connection, target): ...
329

330
class AttributeEvents:
331
    def set(self, target, value, oldvalue, initiator): ...
332
    def append(self, target, value, initiator): ...
333
    def remove(self, target, value, initiator): ...
334
```
335

336
### Exception Handling
337

338
Comprehensive exception hierarchy for error handling and debugging in database operations.
339

340
```python { .api }
341
# Base exceptions
342
class SQLAlchemyError(Exception): ...
343
class ArgumentError(SQLAlchemyError): ...
344
class InvalidRequestError(SQLAlchemyError): ...
345
class CompileError(SQLAlchemyError): ...
346

347
# Database API errors  
348
class DBAPIError(SQLAlchemyError): ...
349
class IntegrityError(DBAPIError): ...
350
class OperationalError(DBAPIError): ...
351
class ProgrammingError(DBAPIError): ...
352
class DataError(DBAPIError): ...
353
class InterfaceError(DBAPIError): ...
354
class DatabaseError(DBAPIError): ...
355
class InternalError(DBAPIError): ...
356
class NotSupportedError(DBAPIError): ...
357
```
358

359
### Inspection System
360

361
Database and ORM introspection capabilities for examining database schemas and ORM configurations.
362

363
```python { .api }
364
def inspect(subject):
365
    """
366
    Provide an inspection interface for various SQLAlchemy objects.
367
    
368
    Parameters:
369
    - subject: Object to inspect (Engine, Connection, mapped class, etc.)
370
    
371
    Returns:
372
    Inspector or other inspection interface for the subject
373
    """
374
```
375

376
## Types
377

378
### Core Types
379

380
```python { .api }
381
from typing import Any, Optional, Union, Dict
382

383
# URL and connectivity
384
class URL:
385
    def __init__(self, drivername: str, **kwargs): ...
386
    
387
# Engine types
388
EngineType = Union[Engine, AsyncEngine]
389
ConnectArgs = Dict[str, Any]
390

391
# Result types
392
Row = Any  # Row-like object with column access
393
Result = Any  # Query result iterator
394
```
395

396
### ORM Types
397

398
```python { .api }
399
from typing import TypeVar, Type, List
400

401
# Generic mapped class type
402
_T = TypeVar('_T')
403
MappedClassType = Type[_T]
404

405
# Relationship types
406
RelationshipProperty = Any
407
MappedCollection = List[Any]
408
```