codekingpro/portable-devtools
114k
1# ext/automap.py
2# Copyright (C) 2005-2026 the SQLAlchemy authors and contributors
3# <see AUTHORS file>
4#
5# This module is part of SQLAlchemy and is released under
6# the MIT License: https://www.opensource.org/licenses/mit-license.php
7
8r"""Define an extension to the :mod:`sqlalchemy.ext.declarative` system
9which automatically generates mapped classes and relationships from a database
10schema, typically though not necessarily one which is reflected.
11
12It is hoped that the :class:`.AutomapBase` system provides a quick
13and modernized solution to the problem that the very famous
14`SQLSoup <https://pypi.org/project/sqlsoup/>`_
15also tries to solve, that of generating a quick and rudimentary object
16model from an existing database on the fly. By addressing the issue strictly
17at the mapper configuration level, and integrating fully with existing
18Declarative class techniques, :class:`.AutomapBase` seeks to provide
19a well-integrated approach to the issue of expediently auto-generating ad-hoc
20mappings.
21
22.. tip:: The :ref:`automap_toplevel` extension is geared towards a
23 "zero declaration" approach, where a complete ORM model including classes
24 and pre-named relationships can be generated on the fly from a database
25 schema. For applications that still want to use explicit class declarations
26 including explicit relationship definitions in conjunction with reflection
27 of tables, the :class:`.DeferredReflection` class, described at
28 :ref:`orm_declarative_reflected_deferred_reflection`, is a better choice.
29
30.. _automap_basic_use:
31
32Basic Use
33=========
34
35The simplest usage is to reflect an existing database into a new model.
36We create a new :class:`.AutomapBase` class in a similar manner as to how
37we create a declarative base class, using :func:`.automap_base`.
38We then call :meth:`.AutomapBase.prepare` on the resulting base class,
39asking it to reflect the schema and produce mappings::
40
41 from sqlalchemy.ext.automap import automap_base
42 from sqlalchemy.orm import Session
43 from sqlalchemy import create_engine
44
45 Base = automap_base()
46
47 # engine, suppose it has two tables 'user' and 'address' set up
48 engine = create_engine("sqlite:///mydatabase.db")
49
50 # reflect the tables
51 Base.prepare(autoload_with=engine)
52
53 # mapped classes are now created with names by default
54 # matching that of the table name.
55 User = Base.classes.user
56 Address = Base.classes.address
57
58 session = Session(engine)
59
60 # rudimentary relationships are produced
61 session.add(Address(email_address="foo@bar.com", user=User(name="foo")))
62 session.commit()
63
64 # collection-based relationships are by default named
65 # "<classname>_collection"
66 u1 = session.query(User).first()
67 print(u1.address_collection)
68
69Above, calling :meth:`.AutomapBase.prepare` while passing along the
70:paramref:`.AutomapBase.prepare.reflect` parameter indicates that the
71:meth:`_schema.MetaData.reflect`
72method will be called on this declarative base
73classes' :class:`_schema.MetaData` collection; then, each **viable**
74:class:`_schema.Table` within the :class:`_schema.MetaData`
75will get a new mapped class
76generated automatically. The :class:`_schema.ForeignKeyConstraint`
77objects which
78link the various tables together will be used to produce new, bidirectional
79:func:`_orm.relationship` objects between classes.
80The classes and relationships
81follow along a default naming scheme that we can customize. At this point,
82our basic mapping consisting of related ``User`` and ``Address`` classes is
83ready to use in the traditional way.
84
85.. note:: By **viable**, we mean that for a table to be mapped, it must
86 specify a primary key. Additionally, if the table is detected as being
87 a pure association table between two other tables, it will not be directly
88 mapped and will instead be configured as a many-to-many table between
89 the mappings for the two referring tables.
90
91Generating Mappings from an Existing MetaData
92=============================================
93
94We can pass a pre-declared :class:`_schema.MetaData` object to
95:func:`.automap_base`.
96This object can be constructed in any way, including programmatically, from
97a serialized file, or from itself being reflected using
98:meth:`_schema.MetaData.reflect`.
99Below we illustrate a combination of reflection and
100explicit table declaration::
101
102 from sqlalchemy import create_engine, MetaData, Table, Column, ForeignKey
103 from sqlalchemy.ext.automap import automap_base
104
105 engine = create_engine("sqlite:///mydatabase.db")
106
107 # produce our own MetaData object
108 metadata = MetaData()
109
110 # we can reflect it ourselves from a database, using options
111 # such as 'only' to limit what tables we look at...
112 metadata.reflect(engine, only=["user", "address"])
113
114 # ... or just define our own Table objects with it (or combine both)
115 Table(
116 "user_order",
117 metadata,
118 Column("id", Integer, primary_key=True),
119 Column("user_id", ForeignKey("user.id")),
120 )
121
122 # we can then produce a set of mappings from this MetaData.
123 Base = automap_base(metadata=metadata)
124
125 # calling prepare() just sets up mapped classes and relationships.
126 Base.prepare()
127
128 # mapped classes are ready
129 User = Base.classes.user
130 Address = Base.classes.address
131 Order = Base.classes.user_order
132
133.. _automap_by_module:
134
135Generating Mappings from Multiple Schemas
136=========================================
137
138The :meth:`.AutomapBase.prepare` method when used with reflection may reflect
139tables from one schema at a time at most, using the
140:paramref:`.AutomapBase.prepare.schema` parameter to indicate the name of a
141schema to be reflected from. In order to populate the :class:`.AutomapBase`
142with tables from multiple schemas, :meth:`.AutomapBase.prepare` may be invoked
143multiple times, each time passing a different name to the
144:paramref:`.AutomapBase.prepare.schema` parameter. The
145:meth:`.AutomapBase.prepare` method keeps an internal list of
146:class:`_schema.Table` objects that have already been mapped, and will add new
147mappings only for those :class:`_schema.Table` objects that are new since the
148last time :meth:`.AutomapBase.prepare` was run::
149
150 e = create_engine("postgresql://scott:tiger@localhost/test")
151
152 Base.metadata.create_all(e)
153
154 Base = automap_base()
155
156 Base.prepare(e)
157 Base.prepare(e, schema="test_schema")
158 Base.prepare(e, schema="test_schema_2")
159
160.. versionadded:: 2.0 The :meth:`.AutomapBase.prepare` method may be called
161 any number of times; only newly added tables will be mapped
162 on each run. Previously in version 1.4 and earlier, multiple calls would
163 cause errors as it would attempt to re-map an already mapped class.
164 The previous workaround approach of invoking
165 :meth:`_schema.MetaData.reflect` directly remains available as well.
166
167Automapping same-named tables across multiple schemas
168-----------------------------------------------------
169
170For the common case where multiple schemas may have same-named tables and
171therefore would generate same-named classes, conflicts can be resolved either
172through use of the :paramref:`.AutomapBase.prepare.classname_for_table` hook to
173apply different classnames on a per-schema basis, or by using the
174:paramref:`.AutomapBase.prepare.modulename_for_table` hook, which allows
175disambiguation of same-named classes by changing their effective ``__module__``
176attribute. In the example below, this hook is used to create a ``__module__``
177attribute for all classes that is of the form ``mymodule.<schemaname>``, where
178the schema name ``default`` is used if no schema is present::
179
180 e = create_engine("postgresql://scott:tiger@localhost/test")
181
182 Base.metadata.create_all(e)
183
184
185 def module_name_for_table(cls, tablename, table):
186 if table.schema is not None:
187 return f"mymodule.{table.schema}"
188 else:
189 return f"mymodule.default"
190
191
192 Base = automap_base()
193
194 Base.prepare(e, modulename_for_table=module_name_for_table)
195 Base.prepare(
196 e, schema="test_schema", modulename_for_table=module_name_for_table
197 )
198 Base.prepare(
199 e, schema="test_schema_2", modulename_for_table=module_name_for_table
200 )
201
202The same named-classes are organized into a hierarchical collection available
203at :attr:`.AutomapBase.by_module`. This collection is traversed using the
204dot-separated name of a particular package/module down into the desired
205class name.
206
207.. note:: When using the :paramref:`.AutomapBase.prepare.modulename_for_table`
208 hook to return a new ``__module__`` that is not ``None``, the class is
209 **not** placed into the :attr:`.AutomapBase.classes` collection; only
210 classes that were not given an explicit modulename are placed here, as the
211 collection cannot represent same-named classes individually.
212
213In the example above, if the database contained a table named ``accounts`` in
214all three of the default schema, the ``test_schema`` schema, and the
215``test_schema_2`` schema, three separate classes will be available as::
216
217 Base.by_module.mymodule.default.accounts
218 Base.by_module.mymodule.test_schema.accounts
219 Base.by_module.mymodule.test_schema_2.accounts
220
221The default module namespace generated for all :class:`.AutomapBase` classes is
222``sqlalchemy.ext.automap``. If no
223:paramref:`.AutomapBase.prepare.modulename_for_table` hook is used, the
224contents of :attr:`.AutomapBase.by_module` will be entirely within the
225``sqlalchemy.ext.automap`` namespace (e.g.
226``MyBase.by_module.sqlalchemy.ext.automap.<classname>``), which would contain
227the same series of classes as what would be seen in
228:attr:`.AutomapBase.classes`. Therefore it's generally only necessary to use
229:attr:`.AutomapBase.by_module` when explicit ``__module__`` conventions are
230present.
231
232.. versionadded: 2.0
233
234 Added the :attr:`.AutomapBase.by_module` collection, which stores
235 classes within a named hierarchy based on dot-separated module names,
236 as well as the :paramref:`.Automap.prepare.modulename_for_table` parameter
237 which allows for custom ``__module__`` schemes for automapped
238 classes.
239
240
241
242Specifying Classes Explicitly
243=============================
244
245.. tip:: If explicit classes are expected to be prominent in an application,
246 consider using :class:`.DeferredReflection` instead.
247
248The :mod:`.sqlalchemy.ext.automap` extension allows classes to be defined
249explicitly, in a way similar to that of the :class:`.DeferredReflection` class.
250Classes that extend from :class:`.AutomapBase` act like regular declarative
251classes, but are not immediately mapped after their construction, and are
252instead mapped when we call :meth:`.AutomapBase.prepare`. The
253:meth:`.AutomapBase.prepare` method will make use of the classes we've
254established based on the table name we use. If our schema contains tables
255``user`` and ``address``, we can define one or both of the classes to be used::
256
257 from sqlalchemy.ext.automap import automap_base
258 from sqlalchemy import create_engine
259
260 # automap base
261 Base = automap_base()
262
263
264 # pre-declare User for the 'user' table
265 class User(Base):
266 __tablename__ = "user"
267
268 # override schema elements like Columns
269 user_name = Column("name", String)
270
271 # override relationships too, if desired.
272 # we must use the same name that automap would use for the
273 # relationship, and also must refer to the class name that automap will
274 # generate for "address"
275 address_collection = relationship("address", collection_class=set)
276
277
278 # reflect
279 engine = create_engine("sqlite:///mydatabase.db")
280 Base.prepare(autoload_with=engine)
281
282 # we still have Address generated from the tablename "address",
283 # but User is the same as Base.classes.User now
284
285 Address = Base.classes.address
286
287 u1 = session.query(User).first()
288 print(u1.address_collection)
289
290 # the backref is still there:
291 a1 = session.query(Address).first()
292 print(a1.user)
293
294Above, one of the more intricate details is that we illustrated overriding
295one of the :func:`_orm.relationship` objects that automap would have created.
296To do this, we needed to make sure the names match up with what automap
297would normally generate, in that the relationship name would be
298``User.address_collection`` and the name of the class referred to, from
299automap's perspective, is called ``address``, even though we are referring to
300it as ``Address`` within our usage of this class.
301
302Overriding Naming Schemes
303=========================
304
305:mod:`.sqlalchemy.ext.automap` is tasked with producing mapped classes and
306relationship names based on a schema, which means it has decision points in how
307these names are determined. These three decision points are provided using
308functions which can be passed to the :meth:`.AutomapBase.prepare` method, and
309are known as :func:`.classname_for_table`,
310:func:`.name_for_scalar_relationship`,
311and :func:`.name_for_collection_relationship`. Any or all of these
312functions are provided as in the example below, where we use a "camel case"
313scheme for class names and a "pluralizer" for collection names using the
314`Inflect <https://pypi.org/project/inflect>`_ package::
315
316 import re
317 import inflect
318
319
320 def camelize_classname(base, tablename, table):
321 "Produce a 'camelized' class name, e.g."
322 "'words_and_underscores' -> 'WordsAndUnderscores'"
323
324 return str(
325 tablename[0].upper()
326 + re.sub(
327 r"_([a-z])",
328 lambda m: m.group(1).upper(),
329 tablename[1:],
330 )
331 )
332
333
334 _pluralizer = inflect.engine()
335
336
337 def pluralize_collection(base, local_cls, referred_cls, constraint):
338 "Produce an 'uncamelized', 'pluralized' class name, e.g."
339 "'SomeTerm' -> 'some_terms'"
340
341 referred_name = referred_cls.__name__
342 uncamelized = re.sub(
343 r"[A-Z]",
344 lambda m: "_%s" % m.group(0).lower(),
345 referred_name,
346 )[1:]
347 pluralized = _pluralizer.plural(uncamelized)
348 return pluralized
349
350
351 from sqlalchemy.ext.automap import automap_base
352
353 Base = automap_base()
354
355 engine = create_engine("sqlite:///mydatabase.db")
356
357 Base.prepare(
358 autoload_with=engine,
359 classname_for_table=camelize_classname,
360 name_for_collection_relationship=pluralize_collection,
361 )
362
363From the above mapping, we would now have classes ``User`` and ``Address``,
364where the collection from ``User`` to ``Address`` is called
365``User.addresses``::
366
367 User, Address = Base.classes.User, Base.classes.Address
368
369 u1 = User(addresses=[Address(email="foo@bar.com")])
370
371Relationship Detection
372======================
373
374The vast majority of what automap accomplishes is the generation of
375:func:`_orm.relationship` structures based on foreign keys. The mechanism
376by which this works for many-to-one and one-to-many relationships is as
377follows:
378
3791. A given :class:`_schema.Table`, known to be mapped to a particular class,
380 is examined for :class:`_schema.ForeignKeyConstraint` objects.
381
3822. From each :class:`_schema.ForeignKeyConstraint`, the remote
383 :class:`_schema.Table`
384 object present is matched up to the class to which it is to be mapped,
385 if any, else it is skipped.
386
3873. As the :class:`_schema.ForeignKeyConstraint`
388 we are examining corresponds to a
389 reference from the immediate mapped class, the relationship will be set up
390 as a many-to-one referring to the referred class; a corresponding
391 one-to-many backref will be created on the referred class referring
392 to this class.
393
3944. If any of the columns that are part of the
395 :class:`_schema.ForeignKeyConstraint`
396 are not nullable (e.g. ``nullable=False``), a
397 :paramref:`_orm.relationship.cascade` keyword argument
398 of ``all, delete-orphan`` will be added to the keyword arguments to
399 be passed to the relationship or backref. If the
400 :class:`_schema.ForeignKeyConstraint` reports that
401 :paramref:`_schema.ForeignKeyConstraint.ondelete`
402 is set to ``CASCADE`` for a not null or ``SET NULL`` for a nullable
403 set of columns, the option :paramref:`_orm.relationship.passive_deletes`
404 flag is set to ``True`` in the set of relationship keyword arguments.
405 Note that not all backends support reflection of ON DELETE.
406
4075. The names of the relationships are determined using the
408 :paramref:`.AutomapBase.prepare.name_for_scalar_relationship` and
409 :paramref:`.AutomapBase.prepare.name_for_collection_relationship`
410 callable functions. It is important to note that the default relationship
411 naming derives the name from the **the actual class name**. If you've
412 given a particular class an explicit name by declaring it, or specified an
413 alternate class naming scheme, that's the name from which the relationship
414 name will be derived.
415
4166. The classes are inspected for an existing mapped property matching these
417 names. If one is detected on one side, but none on the other side,
418 :class:`.AutomapBase` attempts to create a relationship on the missing side,
419 then uses the :paramref:`_orm.relationship.back_populates`
420 parameter in order to
421 point the new relationship to the other side.
422
4237. In the usual case where no relationship is on either side,
424 :meth:`.AutomapBase.prepare` produces a :func:`_orm.relationship` on the
425 "many-to-one" side and matches it to the other using the
426 :paramref:`_orm.relationship.backref` parameter.
427
4288. Production of the :func:`_orm.relationship` and optionally the
429 :func:`.backref`
430 is handed off to the :paramref:`.AutomapBase.prepare.generate_relationship`
431 function, which can be supplied by the end-user in order to augment
432 the arguments passed to :func:`_orm.relationship` or :func:`.backref` or to
433 make use of custom implementations of these functions.
434
435Custom Relationship Arguments
436-----------------------------
437
438The :paramref:`.AutomapBase.prepare.generate_relationship` hook can be used
439to add parameters to relationships. For most cases, we can make use of the
440existing :func:`.automap.generate_relationship` function to return
441the object, after augmenting the given keyword dictionary with our own
442arguments.
443
444Below is an illustration of how to send
445:paramref:`_orm.relationship.cascade` and
446:paramref:`_orm.relationship.passive_deletes`
447options along to all one-to-many relationships::
448
449 from sqlalchemy.ext.automap import generate_relationship
450 from sqlalchemy.orm import interfaces
451
452
453 def _gen_relationship(
454 base, direction, return_fn, attrname, local_cls, referred_cls, **kw
455 ):
456 if direction is interfaces.ONETOMANY:
457 kw["cascade"] = "all, delete-orphan"
458 kw["passive_deletes"] = True
459 # make use of the built-in function to actually return
460 # the result.
461 return generate_relationship(
462 base, direction, return_fn, attrname, local_cls, referred_cls, **kw
463 )
464
465
466 from sqlalchemy.ext.automap import automap_base
467 from sqlalchemy import create_engine
468
469 # automap base
470 Base = automap_base()
471
472 engine = create_engine("sqlite:///mydatabase.db")
473 Base.prepare(autoload_with=engine, generate_relationship=_gen_relationship)
474
475Many-to-Many relationships
476--------------------------
477
478:mod:`.sqlalchemy.ext.automap` will generate many-to-many relationships, e.g.
479those which contain a ``secondary`` argument. The process for producing these
480is as follows:
481
4821. A given :class:`_schema.Table` is examined for
483 :class:`_schema.ForeignKeyConstraint`
484 objects, before any mapped class has been assigned to it.
485
4862. If the table contains two and exactly two
487 :class:`_schema.ForeignKeyConstraint`
488 objects, and all columns within this table are members of these two
489 :class:`_schema.ForeignKeyConstraint` objects, the table is assumed to be a
490 "secondary" table, and will **not be mapped directly**.
491
4923. The two (or one, for self-referential) external tables to which the
493 :class:`_schema.Table`
494 refers to are matched to the classes to which they will be
495 mapped, if any.
496
4974. If mapped classes for both sides are located, a many-to-many bi-directional
498 :func:`_orm.relationship` / :func:`.backref`
499 pair is created between the two
500 classes.
501
5025. The override logic for many-to-many works the same as that of one-to-many/
503 many-to-one; the :func:`.generate_relationship` function is called upon
504 to generate the structures and existing attributes will be maintained.
505
506Relationships with Inheritance
507------------------------------
508
509:mod:`.sqlalchemy.ext.automap` will not generate any relationships between
510two classes that are in an inheritance relationship. That is, with two
511classes given as follows::
512
513 class Employee(Base):
514 __tablename__ = "employee"
515 id = Column(Integer, primary_key=True)
516 type = Column(String(50))
517 __mapper_args__ = {
518 "polymorphic_identity": "employee",
519 "polymorphic_on": type,
520 }
521
522
523 class Engineer(Employee):
524 __tablename__ = "engineer"
525 id = Column(Integer, ForeignKey("employee.id"), primary_key=True)
526 __mapper_args__ = {
527 "polymorphic_identity": "engineer",
528 }
529
530The foreign key from ``Engineer`` to ``Employee`` is used not for a
531relationship, but to establish joined inheritance between the two classes.
532
533Note that this means automap will not generate *any* relationships
534for foreign keys that link from a subclass to a superclass. If a mapping
535has actual relationships from subclass to superclass as well, those
536need to be explicit. Below, as we have two separate foreign keys
537from ``Engineer`` to ``Employee``, we need to set up both the relationship
538we want as well as the ``inherit_condition``, as these are not things
539SQLAlchemy can guess::
540
541 class Employee(Base):
542 __tablename__ = "employee"
543 id = Column(Integer, primary_key=True)
544 type = Column(String(50))
545
546 __mapper_args__ = {
547 "polymorphic_identity": "employee",
548 "polymorphic_on": type,
549 }
550
551
552 class Engineer(Employee):
553 __tablename__ = "engineer"
554 id = Column(Integer, ForeignKey("employee.id"), primary_key=True)
555 favorite_employee_id = Column(Integer, ForeignKey("employee.id"))
556
557 favorite_employee = relationship(
558 Employee, foreign_keys=favorite_employee_id
559 )
560
561 __mapper_args__ = {
562 "polymorphic_identity": "engineer",
563 "inherit_condition": id == Employee.id,
564 }
565
566Handling Simple Naming Conflicts
567--------------------------------
568
569In the case of naming conflicts during mapping, override any of
570:func:`.classname_for_table`, :func:`.name_for_scalar_relationship`,
571and :func:`.name_for_collection_relationship` as needed. For example, if
572automap is attempting to name a many-to-one relationship the same as an
573existing column, an alternate convention can be conditionally selected. Given
574a schema:
575
576.. sourcecode:: sql
577
578 CREATE TABLE table_a (
579 id INTEGER PRIMARY KEY
580 );
581
582 CREATE TABLE table_b (
583 id INTEGER PRIMARY KEY,
584 table_a INTEGER,
585 FOREIGN KEY(table_a) REFERENCES table_a(id)
586 );
587
588The above schema will first automap the ``table_a`` table as a class named
589``table_a``; it will then automap a relationship onto the class for ``table_b``
590with the same name as this related class, e.g. ``table_a``. This
591relationship name conflicts with the mapping column ``table_b.table_a``,
592and will emit an error on mapping.
593
594We can resolve this conflict by using an underscore as follows::
595
596 def name_for_scalar_relationship(
597 base, local_cls, referred_cls, constraint
598 ):
599 name = referred_cls.__name__.lower()
600 local_table = local_cls.__table__
601 if name in local_table.columns:
602 newname = name + "_"
603 warnings.warn(
604 "Already detected name %s present. using %s" % (name, newname)
605 )
606 return newname
607 return name
608
609
610 Base.prepare(
611 autoload_with=engine,
612 name_for_scalar_relationship=name_for_scalar_relationship,
613 )
614
615Alternatively, we can change the name on the column side. The columns
616that are mapped can be modified using the technique described at
617:ref:`mapper_column_distinct_names`, by assigning the column explicitly
618to a new name::
619
620 Base = automap_base()
621
622
623 class TableB(Base):
624 __tablename__ = "table_b"
625 _table_a = Column("table_a", ForeignKey("table_a.id"))
626
627
628 Base.prepare(autoload_with=engine)
629
630Using Automap with Explicit Declarations
631========================================
632
633As noted previously, automap has no dependency on reflection, and can make
634use of any collection of :class:`_schema.Table` objects within a
635:class:`_schema.MetaData`
636collection. From this, it follows that automap can also be used
637generate missing relationships given an otherwise complete model that fully
638defines table metadata::
639
640 from sqlalchemy.ext.automap import automap_base
641 from sqlalchemy import Column, Integer, String, ForeignKey
642
643 Base = automap_base()
644
645
646 class User(Base):
647 __tablename__ = "user"
648
649 id = Column(Integer, primary_key=True)
650 name = Column(String)
651
652
653 class Address(Base):
654 __tablename__ = "address"
655
656 id = Column(Integer, primary_key=True)
657 email = Column(String)
658 user_id = Column(ForeignKey("user.id"))
659
660
661 # produce relationships
662 Base.prepare()
663
664 # mapping is complete, with "address_collection" and
665 # "user" relationships
666 a1 = Address(email="u1")
667 a2 = Address(email="u2")
668 u1 = User(address_collection=[a1, a2])
669 assert a1.user is u1
670
671Above, given mostly complete ``User`` and ``Address`` mappings, the
672:class:`_schema.ForeignKey` which we defined on ``Address.user_id`` allowed a
673bidirectional relationship pair ``Address.user`` and
674``User.address_collection`` to be generated on the mapped classes.
675
676Note that when subclassing :class:`.AutomapBase`,
677the :meth:`.AutomapBase.prepare` method is required; if not called, the classes
678we've declared are in an un-mapped state.
679
680
681.. _automap_intercepting_columns:
682
683Intercepting Column Definitions
684===============================
685
686The :class:`_schema.MetaData` and :class:`_schema.Table` objects support an
687event hook :meth:`_events.DDLEvents.column_reflect` that may be used to intercept
688the information reflected about a database column before the :class:`_schema.Column`
689object is constructed. For example if we wanted to map columns using a
690naming convention such as ``"attr_<columnname>"``, the event could
691be applied as::
692
693 @event.listens_for(Base.metadata, "column_reflect")
694 def column_reflect(inspector, table, column_info):
695 # set column.key = "attr_<lower_case_name>"
696 column_info["key"] = "attr_%s" % column_info["name"].lower()
697
698
699 # run reflection
700 Base.prepare(autoload_with=engine)
701
702.. versionadded:: 1.4.0b2 the :meth:`_events.DDLEvents.column_reflect` event
703 may be applied to a :class:`_schema.MetaData` object.
704
705.. seealso::
706
707 :meth:`_events.DDLEvents.column_reflect`
708
709 :ref:`mapper_automated_reflection_schemes` - in the ORM mapping documentation
710
711
712""" # noqa
713from __future__ import annotations
714
715import dataclasses
716from typing import Any
717from typing import Callable
718from typing import cast
719from typing import ClassVar
720from typing import Dict
721from typing import List
722from typing import NoReturn
723from typing import Optional
724from typing import overload
725from typing import Set
726from typing import Tuple
727from typing import Type
728from typing import TYPE_CHECKING
729from typing import TypeVar
730from typing import Union
731
732from .. import util
733from ..orm import backref
734from ..orm import declarative_base as _declarative_base
735from ..orm import exc as orm_exc
736from ..orm import interfaces
737from ..orm import relationship
738from ..orm.decl_base import _DeferredMapperConfig
739from ..orm.mapper import _CONFIGURE_MUTEX
740from ..schema import ForeignKeyConstraint
741from ..sql import and_
742from ..util import Properties
743from ..util.typing import Protocol
744
745if TYPE_CHECKING:
746 from ..engine.base import Engine
747 from ..orm.base import RelationshipDirection
748 from ..orm.relationships import ORMBackrefArgument
749 from ..orm.relationships import Relationship
750 from ..sql.schema import Column
751 from ..sql.schema import MetaData
752 from ..sql.schema import Table
753 from ..util import immutabledict
754
755
756_KT = TypeVar("_KT", bound=Any)
757_VT = TypeVar("_VT", bound=Any)
758
759
760class PythonNameForTableType(Protocol):
761 def __call__(
762 self, base: Type[Any], tablename: str, table: Table
763 ) -> str: ...
764
765
766def classname_for_table(
767 base: Type[Any],
768 tablename: str,
769 table: Table,
770) -> str:
771 """Return the class name that should be used, given the name
772 of a table.
773
774 The default implementation is::
775
776 return str(tablename)
777
778 Alternate implementations can be specified using the
779 :paramref:`.AutomapBase.prepare.classname_for_table`
780 parameter.
781
782 :param base: the :class:`.AutomapBase` class doing the prepare.
783
784 :param tablename: string name of the :class:`_schema.Table`.
785
786 :param table: the :class:`_schema.Table` object itself.
787
788 :return: a string class name.
789
790 .. note::
791
792 In Python 2, the string used for the class name **must** be a
793 non-Unicode object, e.g. a ``str()`` object. The ``.name`` attribute
794 of :class:`_schema.Table` is typically a Python unicode subclass,
795 so the
796 ``str()`` function should be applied to this name, after accounting for
797 any non-ASCII characters.
798
799 """
800 return str(tablename)
801
802
803class NameForScalarRelationshipType(Protocol):
804 def __call__(
805 self,
806 base: Type[Any],
807 local_cls: Type[Any],
808 referred_cls: Type[Any],
809 constraint: ForeignKeyConstraint,
810 ) -> str: ...
811
812
813def name_for_scalar_relationship(
814 base: Type[Any],
815 local_cls: Type[Any],
816 referred_cls: Type[Any],
817 constraint: ForeignKeyConstraint,
818) -> str:
819 """Return the attribute name that should be used to refer from one
820 class to another, for a scalar object reference.
821
822 The default implementation is::
823
824 return referred_cls.__name__.lower()
825
826 Alternate implementations can be specified using the
827 :paramref:`.AutomapBase.prepare.name_for_scalar_relationship`
828 parameter.
829
830 :param base: the :class:`.AutomapBase` class doing the prepare.
831
832 :param local_cls: the class to be mapped on the local side.
833
834 :param referred_cls: the class to be mapped on the referring side.
835
836 :param constraint: the :class:`_schema.ForeignKeyConstraint` that is being
837 inspected to produce this relationship.
838
839 """
840 return referred_cls.__name__.lower()
841
842
843class NameForCollectionRelationshipType(Protocol):
844 def __call__(
845 self,
846 base: Type[Any],
847 local_cls: Type[Any],
848 referred_cls: Type[Any],
849 constraint: ForeignKeyConstraint,
850 ) -> str: ...
851
852
853def name_for_collection_relationship(
854 base: Type[Any],
855 local_cls: Type[Any],
856 referred_cls: Type[Any],
857 constraint: ForeignKeyConstraint,
858) -> str:
859 """Return the attribute name that should be used to refer from one
860 class to another, for a collection reference.
861
862 The default implementation is::
863
864 return referred_cls.__name__.lower() + "_collection"
865
866 Alternate implementations
867 can be specified using the
868 :paramref:`.AutomapBase.prepare.name_for_collection_relationship`
869 parameter.
870
871 :param base: the :class:`.AutomapBase` class doing the prepare.
872
873 :param local_cls: the class to be mapped on the local side.
874
875 :param referred_cls: the class to be mapped on the referring side.
876
877 :param constraint: the :class:`_schema.ForeignKeyConstraint` that is being
878 inspected to produce this relationship.
879
880 """
881 return referred_cls.__name__.lower() + "_collection"
882
883
884class GenerateRelationshipType(Protocol):
885 @overload
886 def __call__(
887 self,
888 base: Type[Any],
889 direction: RelationshipDirection,
890 return_fn: Callable[..., Relationship[Any]],
891 attrname: str,
892 local_cls: Type[Any],
893 referred_cls: Type[Any],
894 **kw: Any,
895 ) -> Relationship[Any]: ...
896
897 @overload
898 def __call__(
899 self,
900 base: Type[Any],
901 direction: RelationshipDirection,
902 return_fn: Callable[..., ORMBackrefArgument],
903 attrname: str,
904 local_cls: Type[Any],
905 referred_cls: Type[Any],
906 **kw: Any,
907 ) -> ORMBackrefArgument: ...
908
909 def __call__(
910 self,
911 base: Type[Any],
912 direction: RelationshipDirection,
913 return_fn: Union[
914 Callable[..., Relationship[Any]], Callable[..., ORMBackrefArgument]
915 ],
916 attrname: str,
917 local_cls: Type[Any],
918 referred_cls: Type[Any],
919 **kw: Any,
920 ) -> Union[ORMBackrefArgument, Relationship[Any]]: ...
921
922
923@overload
924def generate_relationship(
925 base: Type[Any],
926 direction: RelationshipDirection,
927 return_fn: Callable[..., Relationship[Any]],
928 attrname: str,
929 local_cls: Type[Any],
930 referred_cls: Type[Any],
931 **kw: Any,
932) -> Relationship[Any]: ...
933
934
935@overload
936def generate_relationship(
937 base: Type[Any],
938 direction: RelationshipDirection,
939 return_fn: Callable[..., ORMBackrefArgument],
940 attrname: str,
941 local_cls: Type[Any],
942 referred_cls: Type[Any],
943 **kw: Any,
944) -> ORMBackrefArgument: ...
945
946
947def generate_relationship(
948 base: Type[Any],
949 direction: RelationshipDirection,
950 return_fn: Union[
951 Callable[..., Relationship[Any]], Callable[..., ORMBackrefArgument]
952 ],
953 attrname: str,
954 local_cls: Type[Any],
955 referred_cls: Type[Any],
956 **kw: Any,
957) -> Union[Relationship[Any], ORMBackrefArgument]:
958 r"""Generate a :func:`_orm.relationship` or :func:`.backref`
959 on behalf of two
960 mapped classes.
961
962 An alternate implementation of this function can be specified using the
963 :paramref:`.AutomapBase.prepare.generate_relationship` parameter.
964
965 The default implementation of this function is as follows::
966
967 if return_fn is backref:
968 return return_fn(attrname, **kw)
969 elif return_fn is relationship:
970 return return_fn(referred_cls, **kw)
971 else:
972 raise TypeError("Unknown relationship function: %s" % return_fn)
973
974 :param base: the :class:`.AutomapBase` class doing the prepare.
975
976 :param direction: indicate the "direction" of the relationship; this will
977 be one of :data:`.ONETOMANY`, :data:`.MANYTOONE`, :data:`.MANYTOMANY`.
978
979 :param return_fn: the function that is used by default to create the
980 relationship. This will be either :func:`_orm.relationship` or
981 :func:`.backref`. The :func:`.backref` function's result will be used to
982 produce a new :func:`_orm.relationship` in a second step,
983 so it is critical
984 that user-defined implementations correctly differentiate between the two
985 functions, if a custom relationship function is being used.
986
987 :param attrname: the attribute name to which this relationship is being
988 assigned. If the value of :paramref:`.generate_relationship.return_fn` is
989 the :func:`.backref` function, then this name is the name that is being
990 assigned to the backref.
991
992 :param local_cls: the "local" class to which this relationship or backref
993 will be locally present.
994
995 :param referred_cls: the "referred" class to which the relationship or
996 backref refers to.
997
998 :param \**kw: all additional keyword arguments are passed along to the
999 function.
1000
1001 :return: a :func:`_orm.relationship` or :func:`.backref` construct,
1002 as dictated
1003 by the :paramref:`.generate_relationship.return_fn` parameter.
1004
1005 """
1006
1007 if return_fn is backref:
1008 return return_fn(attrname, **kw)
1009 elif return_fn is relationship:
1010 return return_fn(referred_cls, **kw)
1011 else:
1012 raise TypeError("Unknown relationship function: %s" % return_fn)
1013
1014
1015ByModuleProperties = Properties[Union["ByModuleProperties", Type[Any]]]
1016
1017
1018class AutomapBase:
1019 """Base class for an "automap" schema.
1020
1021 The :class:`.AutomapBase` class can be compared to the "declarative base"
1022 class that is produced by the :func:`.declarative.declarative_base`
1023 function. In practice, the :class:`.AutomapBase` class is always used
1024 as a mixin along with an actual declarative base.
1025
1026 A new subclassable :class:`.AutomapBase` is typically instantiated
1027 using the :func:`.automap_base` function.
1028
1029 .. seealso::
1030
1031 :ref:`automap_toplevel`
1032
1033 """
1034
1035 __abstract__ = True
1036
1037 classes: ClassVar[Properties[Type[Any]]]
1038 """An instance of :class:`.util.Properties` containing classes.
1039
1040 This object behaves much like the ``.c`` collection on a table. Classes
1041 are present under the name they were given, e.g.::
1042
1043 Base = automap_base()
1044 Base.prepare(autoload_with=some_engine)
1045
1046 User, Address = Base.classes.User, Base.classes.Address
1047
1048 For class names that overlap with a method name of
1049 :class:`.util.Properties`, such as ``items()``, the getitem form
1050 is also supported::
1051
1052 Item = Base.classes["items"]
1053
1054 """
1055
1056 by_module: ClassVar[ByModuleProperties]
1057 """An instance of :class:`.util.Properties` containing a hierarchal
1058 structure of dot-separated module names linked to classes.
1059
1060 This collection is an alternative to the :attr:`.AutomapBase.classes`
1061 collection that is useful when making use of the
1062 :paramref:`.AutomapBase.prepare.modulename_for_table` parameter, which will
1063 apply distinct ``__module__`` attributes to generated classes.
1064
1065 The default ``__module__`` an automap-generated class is
1066 ``sqlalchemy.ext.automap``; to access this namespace using
1067 :attr:`.AutomapBase.by_module` looks like::
1068
1069 User = Base.by_module.sqlalchemy.ext.automap.User
1070
1071 If a class had a ``__module__`` of ``mymodule.account``, accessing
1072 this namespace looks like::
1073
1074 MyClass = Base.by_module.mymodule.account.MyClass
1075
1076 .. versionadded:: 2.0
1077
1078 .. seealso::
1079
1080 :ref:`automap_by_module`
1081
1082 """
1083
1084 metadata: ClassVar[MetaData]
1085 """Refers to the :class:`_schema.MetaData` collection that will be used
1086 for new :class:`_schema.Table` objects.
1087
1088 .. seealso::
1089
1090 :ref:`orm_declarative_metadata`
1091
1092 """
1093
1094 _sa_automapbase_bookkeeping: ClassVar[_Bookkeeping]
1095
1096 @classmethod
1097 @util.deprecated_params(
1098 engine=(
1099 "2.0",
1100 "The :paramref:`_automap.AutomapBase.prepare.engine` parameter "
1101 "is deprecated and will be removed in a future release. "
1102 "Please use the "
1103 ":paramref:`_automap.AutomapBase.prepare.autoload_with` "
1104 "parameter.",
1105 ),
1106 reflect=(
1107 "2.0",
1108 "The :paramref:`_automap.AutomapBase.prepare.reflect` "
1109 "parameter is deprecated and will be removed in a future "
1110 "release. Reflection is enabled when "
1111 ":paramref:`_automap.AutomapBase.prepare.autoload_with` "
1112 "is passed.",
1113 ),
1114 )
1115 def prepare(
1116 cls: Type[AutomapBase],
1117 autoload_with: Optional[Engine] = None,
1118 engine: Optional[Any] = None,
1119 reflect: bool = False,
1120 schema: Optional[str] = None,
1121 classname_for_table: Optional[PythonNameForTableType] = None,
1122 modulename_for_table: Optional[PythonNameForTableType] = None,
1123 collection_class: Optional[Any] = None,
1124 name_for_scalar_relationship: Optional[
1125 NameForScalarRelationshipType
1126 ] = None,
1127 name_for_collection_relationship: Optional[
1128 NameForCollectionRelationshipType
1129 ] = None,
1130 generate_relationship: Optional[GenerateRelationshipType] = None,
1131 reflection_options: Union[
1132 Dict[_KT, _VT], immutabledict[_KT, _VT]
1133 ] = util.EMPTY_DICT,
1134 ) -> None:
1135 """Extract mapped classes and relationships from the
1136 :class:`_schema.MetaData` and perform mappings.
1137
1138 For full documentation and examples see
1139 :ref:`automap_basic_use`.
1140
1141 :param autoload_with: an :class:`_engine.Engine` or
1142 :class:`_engine.Connection` with which
1143 to perform schema reflection; when specified, the
1144 :meth:`_schema.MetaData.reflect` method will be invoked within
1145 the scope of this method.
1146
1147 :param engine: legacy; use :paramref:`.AutomapBase.autoload_with`.
1148 Used to indicate the :class:`_engine.Engine` or
1149 :class:`_engine.Connection` with which to reflect tables with,
1150 if :paramref:`.AutomapBase.reflect` is True.
1151
1152 :param reflect: legacy; use :paramref:`.AutomapBase.autoload_with`.
1153 Indicates that :meth:`_schema.MetaData.reflect` should be invoked.
1154
1155 :param classname_for_table: callable function which will be used to
1156 produce new class names, given a table name. Defaults to
1157 :func:`.classname_for_table`.
1158
1159 :param modulename_for_table: callable function which will be used to
1160 produce the effective ``__module__`` for an internally generated
1161 class, to allow for multiple classes of the same name in a single
1162 automap base which would be in different "modules".
1163
1164 Defaults to ``None``, which will indicate that ``__module__`` will not
1165 be set explicitly; the Python runtime will use the value
1166 ``sqlalchemy.ext.automap`` for these classes.
1167
1168 When assigning ``__module__`` to generated classes, they can be
1169 accessed based on dot-separated module names using the
1170 :attr:`.AutomapBase.by_module` collection. Classes that have
1171 an explicit ``__module_`` assigned using this hook do **not** get
1172 placed into the :attr:`.AutomapBase.classes` collection, only
1173 into :attr:`.AutomapBase.by_module`.
1174
1175 .. versionadded:: 2.0
1176
1177 .. seealso::
1178
1179 :ref:`automap_by_module`
1180
1181 :param name_for_scalar_relationship: callable function which will be
1182 used to produce relationship names for scalar relationships. Defaults
1183 to :func:`.name_for_scalar_relationship`.
1184
1185 :param name_for_collection_relationship: callable function which will
1186 be used to produce relationship names for collection-oriented
1187 relationships. Defaults to :func:`.name_for_collection_relationship`.
1188
1189 :param generate_relationship: callable function which will be used to
1190 actually generate :func:`_orm.relationship` and :func:`.backref`
1191 constructs. Defaults to :func:`.generate_relationship`.
1192
1193 :param collection_class: the Python collection class that will be used
1194 when a new :func:`_orm.relationship`
1195 object is created that represents a
1196 collection. Defaults to ``list``.
1197
1198 :param schema: Schema name to reflect when reflecting tables using
1199 the :paramref:`.AutomapBase.prepare.autoload_with` parameter. The name
1200 is passed to the :paramref:`_schema.MetaData.reflect.schema` parameter