0
# Object Relational Mapping (ORM)
1
2
Declarative mapping, session management, relationship definitions, query API, and advanced ORM features including inheritance, polymorphism, and events. The ORM provides transparent object persistence using the identity map and unit of work patterns.
3
4
## Capabilities
5
6
### Declarative Base and Registry
7
8
Foundation classes for declarative ORM mapping.
9
10
```python { .api }
11
class DeclarativeBase:
12
"""Modern declarative base class for SQLAlchemy 2.0+."""
13
14
metadata: MetaData
15
registry: registry
16
17
@classmethod
18
def __init_subclass__(cls, **kwargs):
19
"""Handle subclass registration with registry."""
20
21
def declarative_base(cls=None, bind=None, metadata=None, mapper=None, **kw):
22
"""
23
Create declarative base class (legacy approach).
24
25
Parameters:
26
- cls: base class to extend (default object)
27
- bind: Engine to bind metadata
28
- metadata: MetaData instance to use
29
- mapper: custom mapper class
30
31
Returns:
32
type: Declarative base class
33
"""
34
35
class registry:
36
"""Mapper registry for declarative configurations."""
37
38
def __init__(self, metadata=None, **kwargs):
39
"""
40
Create mapper registry.
41
42
Parameters:
43
- metadata: MetaData instance for tables
44
"""
45
46
def configure(self):
47
"""Configure all pending mappers in registry."""
48
49
def dispose(self):
50
"""Dispose all mappers and clear registry."""
51
```
52
53
### Session Management
54
55
Session creation, configuration, and lifecycle management.
56
57
```python { .api }
58
class Session:
59
"""ORM session with identity map and unit of work."""
60
61
def __init__(self, bind=None, **kwargs):
62
"""
63
Create ORM session.
64
65
Parameters:
66
- bind: Engine or Connection for database operations
67
- autoflush: bool, auto-flush before queries (default True)
68
- autocommit: bool, deprecated in 2.0
69
- expire_on_commit: bool, expire objects after commit (default True)
70
"""
71
72
def add(self, instance):
73
"""
74
Add object instance to session.
75
76
Parameters:
77
- instance: mapped object to add
78
"""
79
80
def add_all(self, instances):
81
"""
82
Add multiple object instances to session.
83
84
Parameters:
85
- instances: iterable of mapped objects
86
"""
87
88
def delete(self, instance):
89
"""
90
Mark object instance for deletion.
91
92
Parameters:
93
- instance: mapped object to delete
94
"""
95
96
def commit(self):
97
"""Flush pending changes and commit transaction."""
98
99
def rollback(self):
100
"""Rollback current transaction and expire all objects."""
101
102
def flush(self):
103
"""Flush pending changes to database without committing."""
104
105
def expunge(self, instance):
106
"""
107
Remove instance from session without deleting.
108
109
Parameters:
110
- instance: mapped object to remove from session
111
"""
112
113
def expunge_all(self):
114
"""Remove all instances from session."""
115
116
def refresh(self, instance, attribute_names=None):
117
"""
118
Refresh object from database.
119
120
Parameters:
121
- instance: mapped object to refresh
122
- attribute_names: specific attributes to refresh
123
"""
124
125
def merge(self, instance):
126
"""
127
Merge detached instance into session.
128
129
Parameters:
130
- instance: detached mapped object
131
132
Returns:
133
object: Merged persistent instance
134
"""
135
136
def execute(self, statement, parameters=None, **kwargs):
137
"""
138
Execute statement with ORM-level processing.
139
140
Parameters:
141
- statement: SQL statement or ORM query
142
- parameters: bind parameters
143
144
Returns:
145
Result: Query results
146
"""
147
148
def scalar(self, statement, parameters=None, **kwargs):
149
"""
150
Execute statement and return scalar result.
151
152
Parameters:
153
- statement: SQL statement or ORM query
154
- parameters: bind parameters
155
156
Returns:
157
Any: Scalar result value
158
"""
159
160
def get(self, entity, ident):
161
"""
162
Get object by primary key.
163
164
Parameters:
165
- entity: mapped class
166
- ident: primary key value or tuple
167
168
Returns:
169
object or None: Object instance or None if not found
170
"""
171
172
def sessionmaker(bind=None, **kwargs):
173
"""
174
Create Session factory.
175
176
Parameters:
177
- bind: Engine for database operations
178
- kwargs: Session configuration options
179
180
Returns:
181
sessionmaker: Session factory class
182
"""
183
184
def scoped_session(session_factory):
185
"""
186
Create thread-local scoped session.
187
188
Parameters:
189
- session_factory: sessionmaker instance
190
191
Returns:
192
scoped_session: Thread-local session proxy
193
"""
194
```
195
196
### Column Mapping
197
198
Modern column mapping with type annotations and configuration.
199
200
```python { .api }
201
def mapped_column(*args, **kwargs):
202
"""
203
Define mapped column with type annotations.
204
205
Parameters:
206
- type_: SQLAlchemy type or Python type for annotation
207
- primary_key: bool, column is primary key
208
- nullable: bool, column allows NULL values
209
- default: default value or callable
210
- server_default: server-side default expression
211
- unique: bool, column has unique constraint
212
- index: bool, create index on column
213
- autoincrement: bool or str, auto-increment behavior
214
215
Returns:
216
MappedColumn: Configured mapped column
217
"""
218
219
def column_property(*columns, **kwargs):
220
"""
221
Create property from SQL expression.
222
223
Parameters:
224
- columns: Column or SQL expression
225
- deferred: bool, defer loading until accessed
226
- group: str, deferred loading group name
227
228
Returns:
229
ColumnProperty: SQL expression property
230
"""
231
232
def deferred(column, **kwargs):
233
"""
234
Mark column for deferred loading.
235
236
Parameters:
237
- column: Column to defer
238
- group: str, deferred loading group
239
240
Returns:
241
ColumnProperty: Deferred column property
242
"""
243
244
def synonym(name, **kwargs):
245
"""
246
Create property synonym.
247
248
Parameters:
249
- name: str, name of target property
250
- map_column: bool, create column mapping
251
252
Returns:
253
SynonymProperty: Property synonym
254
"""
255
```
256
257
### Relationships
258
259
Relationship definition and configuration between mapped classes.
260
261
```python { .api }
262
def relationship(argument, **kwargs):
263
"""
264
Define relationship between mapped classes.
265
266
Parameters:
267
- argument: str or class, related class or class name
268
- back_populates: str, bidirectional relationship property
269
- backref: str or backref(), automatic reverse relationship
270
- foreign_keys: list, foreign key columns for relationship
271
- remote_side: Column or list, remote side for self-referential
272
- primaryjoin: join condition for relationship
273
- secondaryjoin: join condition for many-to-many
274
- secondary: Table for many-to-many association
275
- cascade: str, cascade behavior ('all, delete-orphan', etc.)
276
- lazy: str, loading strategy ('select', 'joined', 'subquery', 'dynamic')
277
- uselist: bool, relationship is one-to-many/many-to-many
278
- order_by: ordering for collection relationships
279
280
Returns:
281
RelationshipProperty: Configured relationship
282
"""
283
284
def backref(name, **kwargs):
285
"""
286
Create automatic reverse relationship.
287
288
Parameters:
289
- name: str, name of reverse relationship property
290
- kwargs: relationship configuration for reverse side
291
292
Returns:
293
backref: Reverse relationship configuration
294
"""
295
296
def foreign(expr):
297
"""
298
Mark expression as foreign key side of relationship.
299
300
Parameters:
301
- expr: Column expression
302
303
Returns:
304
_AnnotatedColumn: Annotated foreign column
305
"""
306
307
def remote(expr):
308
"""
309
Mark expression as remote side of relationship.
310
311
Parameters:
312
- expr: Column expression
313
314
Returns:
315
_AnnotatedColumn: Annotated remote column
316
"""
317
```
318
319
### Query and Loading Strategies
320
321
Query execution and relationship loading optimization.
322
323
```python { .api }
324
def joinedload(attr):
325
"""
326
Eagerly load relationship using JOIN.
327
328
Parameters:
329
- attr: relationship attribute to load
330
331
Returns:
332
Load: Joined loading option
333
"""
334
335
def selectinload(attr):
336
"""
337
Eagerly load relationship using separate SELECT.
338
339
Parameters:
340
- attr: relationship attribute to load
341
342
Returns:
343
Load: Select-in loading option
344
"""
345
346
def subqueryload(attr):
347
"""
348
Eagerly load relationship using subquery.
349
350
Parameters:
351
- attr: relationship attribute to load
352
353
Returns:
354
Load: Subquery loading option
355
"""
356
357
def lazyload(attr):
358
"""
359
Lazily load relationship (default behavior).
360
361
Parameters:
362
- attr: relationship attribute to load
363
364
Returns:
365
Load: Lazy loading option
366
"""
367
368
def noload(attr):
369
"""
370
Prevent automatic loading of relationship.
371
372
Parameters:
373
- attr: relationship attribute
374
375
Returns:
376
Load: No-load option
377
"""
378
379
def raiseload(attr):
380
"""
381
Raise exception if relationship accessed.
382
383
Parameters:
384
- attr: relationship attribute
385
386
Returns:
387
Load: Raise-on-access option
388
"""
389
390
def contains_eager(attr):
391
"""
392
Mark relationship as already loaded in query.
393
394
Parameters:
395
- attr: relationship attribute
396
397
Returns:
398
Load: Contains-eager option
399
"""
400
```
401
402
### Object State and Inspection
403
404
Object state management and ORM inspection utilities.
405
406
```python { .api }
407
def object_session(instance):
408
"""
409
Get session associated with object instance.
410
411
Parameters:
412
- instance: mapped object
413
414
Returns:
415
Session or None: Associated session
416
"""
417
418
def make_transient(instance):
419
"""
420
Make persistent object transient (unsaved).
421
422
Parameters:
423
- instance: persistent mapped object
424
"""
425
426
def make_transient_to_detached(instance):
427
"""
428
Move transient object to detached state.
429
430
Parameters:
431
- instance: transient mapped object
432
"""
433
434
def class_mapper(class_):
435
"""
436
Get mapper for mapped class.
437
438
Parameters:
439
- class_: mapped class
440
441
Returns:
442
Mapper: Class mapper
443
"""
444
445
def inspect(subject):
446
"""
447
Inspect mapped object, class, or attribute.
448
449
Parameters:
450
- subject: mapped object, class, or attribute
451
452
Returns:
453
Inspector: Appropriate inspector object
454
"""
455
456
def was_deleted(object):
457
"""
458
Check if object was deleted in current session.
459
460
Parameters:
461
- object: mapped object instance
462
463
Returns:
464
bool: True if object was deleted
465
"""
466
```
467
468
### Advanced ORM Features
469
470
Polymorphism, aliasing, and advanced query techniques.
471
472
```python { .api }
473
def aliased(element, alias=None, name=None):
474
"""
475
Create alias for mapped class or selectable.
476
477
Parameters:
478
- element: mapped class or selectable
479
- alias: specific alias to use
480
- name: name for alias
481
482
Returns:
483
AliasedClass: Aliased class or selectable
484
"""
485
486
def with_polymorphic(base, classes, selectable=None, **kwargs):
487
"""
488
Create polymorphic selectable for inheritance queries.
489
490
Parameters:
491
- base: base mapped class
492
- classes: subclasses to include
493
- selectable: custom selectable for polymorphic loading
494
495
Returns:
496
AliasedClass: Polymorphic selectable
497
"""
498
499
def polymorphic_union(table_map, typecolname, **kwargs):
500
"""
501
Create UNION for polymorphic mapping.
502
503
Parameters:
504
- table_map: dict of discriminator->table mappings
505
- typecolname: name of discriminator column
506
507
Returns:
508
Alias: UNION selectable for polymorphic queries
509
"""
510
511
class Bundle:
512
"""Bundle multiple columns or expressions."""
513
514
def __init__(self, name, *exprs, **kwargs):
515
"""
516
Create column bundle.
517
518
Parameters:
519
- name: str, bundle name
520
- exprs: expressions to bundle
521
- single_entity: bool, bundle represents single entity
522
"""
523
```
524
525
### Composite and Hybrid Properties
526
527
Advanced property types for complex attribute mapping.
528
529
```python { .api }
530
def composite(class_, *attrs, **kwargs):
531
"""
532
Create composite property from multiple columns.
533
534
Parameters:
535
- class_: composite value class
536
- attrs: column attributes for composite
537
- group: str, deferred loading group
538
539
Returns:
540
CompositeProperty: Composite property mapping
541
"""
542
543
class hybrid_property:
544
"""Property with different SQL and Python implementations."""
545
546
def __init__(self, fget, fset=None, fdel=None, expr=None):
547
"""
548
Create hybrid property.
549
550
Parameters:
551
- fget: Python getter function
552
- fset: Python setter function
553
- fdel: Python deleter function
554
- expr: SQL expression function
555
"""
556
557
def expression(self, func):
558
"""
559
Decorator to define SQL expression.
560
561
Parameters:
562
- func: function returning SQL expression
563
564
Returns:
565
hybrid_property: Property with SQL expression
566
"""
567
568
class hybrid_method:
569
"""Method with different SQL and Python implementations."""
570
571
def __init__(self, func, expr=None):
572
"""
573
Create hybrid method.
574
575
Parameters:
576
- func: Python method implementation
577
- expr: SQL expression method
578
"""
579
```
580
581
### Session Events
582
583
Event handling for session lifecycle and object state changes.
584
585
```python { .api }
586
class SessionEvents:
587
"""Session-level event hooks."""
588
589
@staticmethod
590
def after_transaction_create(session, transaction):
591
"""Called after transaction creation."""
592
593
@staticmethod
594
def after_transaction_end(session, transaction):
595
"""Called after transaction end."""
596
597
@staticmethod
598
def before_commit(session):
599
"""Called before transaction commit."""
600
601
@staticmethod
602
def after_commit(session):
603
"""Called after transaction commit."""
604
605
@staticmethod
606
def after_rollback(session):
607
"""Called after transaction rollback."""
608
609
@staticmethod
610
def before_flush(session, flush_context, instances):
611
"""Called before session flush."""
612
613
@staticmethod
614
def after_flush(session, flush_context):
615
"""Called after session flush."""
616
617
class AttributeEvents:
618
"""Attribute-level event hooks."""
619
620
@staticmethod
621
def set(target, value, oldvalue, initiator):
622
"""Called when attribute is set."""
623
624
@staticmethod
625
def append(target, value, initiator):
626
"""Called when item appended to collection."""
627
628
@staticmethod
629
def remove(target, value, initiator):
630
"""Called when item removed from collection."""
631
```
632
633
## Usage Examples
634
635
### Basic ORM Usage
636
637
```python
638
from sqlalchemy import create_engine, String, Integer
639
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, Session
640
641
class Base(DeclarativeBase):
642
pass
643
644
class User(Base):
645
__tablename__ = 'users'
646
647
id: Mapped[int] = mapped_column(primary_key=True)
648
name: Mapped[str] = mapped_column(String(50))
649
email: Mapped[str] = mapped_column(String(100))
650
651
engine = create_engine("sqlite:///example.db")
652
Base.metadata.create_all(engine)
653
654
# Session usage
655
with Session(engine) as session:
656
user = User(name="John Doe", email="john@example.com")
657
session.add(user)
658
session.commit()
659
660
# Query
661
users = session.execute(
662
select(User).where(User.name.like('%John%'))
663
).scalars().all()
664
```
665
666
### Relationships Example
667
668
```python
669
from sqlalchemy import ForeignKey
670
from sqlalchemy.orm import relationship
671
672
class User(Base):
673
__tablename__ = 'users'
674
675
id: Mapped[int] = mapped_column(primary_key=True)
676
name: Mapped[str] = mapped_column(String(50))
677
678
orders: Mapped[List["Order"]] = relationship(back_populates="user")
679
680
class Order(Base):
681
__tablename__ = 'orders'
682
683
id: Mapped[int] = mapped_column(primary_key=True)
684
user_id: Mapped[int] = mapped_column(ForeignKey('users.id'))
685
amount: Mapped[Decimal] = mapped_column()
686
687
user: Mapped["User"] = relationship(back_populates="orders")
688
689
# Usage with eager loading
690
with Session(engine) as session:
691
users = session.execute(
692
select(User).options(selectinload(User.orders))
693
).scalars().all()
694
```
695
696
### Advanced Query Patterns
697
698
```python
699
# Aliased queries
700
UserAlias = aliased(User)
701
702
stmt = select(User, UserAlias).join(
703
UserAlias, User.manager_id == UserAlias.id
704
)
705
706
# Polymorphic queries
707
class Employee(User):
708
__tablename__ = 'employees'
709
id: Mapped[int] = mapped_column(ForeignKey('users.id'), primary_key=True)
710
employee_number: Mapped[str] = mapped_column()
711
712
stmt = select(User).where(User.type == 'employee')
713
employees = session.execute(stmt).scalars().all()
714
```
715
716
### Loading Strategies
717
718
Control how related objects are loaded with various loading strategies.
719
720
```python { .api }
721
# Eager loading strategies
722
def joinedload(attr, **kwargs):
723
"""Load related objects using LEFT OUTER JOIN."""
724
725
def selectinload(attr, **kwargs):
726
"""Load related objects using separate SELECT IN query."""
727
728
def subqueryload(attr, **kwargs):
729
"""Load related objects using correlated subquery."""
730
731
def immediateload(attr, **kwargs):
732
"""Load related objects immediately during parent load."""
733
734
# Lazy loading control
735
def lazyload(attr, **kwargs):
736
"""Enable lazy loading for relationship (default)."""
737
738
def noload(attr, **kwargs):
739
"""Disable loading of relationship entirely."""
740
741
def raiseload(attr, **kwargs):
742
"""Raise exception when relationship is accessed."""
743
744
# Column loading strategies
745
def defer(attr, **kwargs):
746
"""Defer loading of column until accessed."""
747
748
def undefer(attr, **kwargs):
749
"""Undefer previously deferred column."""
750
751
def load_only(*attrs, **kwargs):
752
"""Load only specified columns, defer others."""
753
754
# Relationship utilities
755
def contains_eager(attr, **kwargs):
756
"""Mark relationship as already loaded in query result."""
757
758
def with_polymorphic(base, classes, **kwargs):
759
"""Query polymorphic classes together."""
760
```
761
762
### Utility Functions
763
764
Helper functions for ORM state management and inspection.
765
766
```python { .api }
767
# Object state management
768
def make_transient(instance):
769
"""Make persistent object transient (remove from session)."""
770
771
def make_transient_to_detached(instance):
772
"""Make transient object detached with persistent identity."""
773
774
def object_session(instance):
775
"""Get session containing the given object instance."""
776
777
# Session utilities
778
def close_all_sessions():
779
"""Close all sessions in current thread."""
780
781
# Mapper inspection
782
def class_mapper(class_):
783
"""Get mapper for mapped class."""
784
785
def object_mapper(instance):
786
"""Get mapper for object instance."""
787
788
def configure_mappers():
789
"""Configure all pending mappers."""
790
791
def clear_mappers():
792
"""Clear all mapper configuration."""
793
794
# Relationship configuration helpers
795
def foreign(expr):
796
"""Mark column expression as foreign key side of relationship."""
797
798
def remote(expr):
799
"""Mark column expression as remote side of relationship."""
800
801
def backref(name, **kwargs):
802
"""Create bidirectional relationship."""
803
```