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
```