codekingpro/portable-devtools
115k
1# sql/schema.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
8"""The schema module provides the building blocks for database metadata.
9
10Each element within this module describes a database entity which can be
11created and dropped, or is otherwise part of such an entity. Examples include
12tables, columns, sequences, and indexes.
13
14All entities are subclasses of :class:`~sqlalchemy.schema.SchemaItem`, and as
15defined in this module they are intended to be agnostic of any vendor-specific
16constructs.
17
18A collection of entities are grouped into a unit called
19:class:`~sqlalchemy.schema.MetaData`. MetaData serves as a logical grouping of
20schema elements, and can also be associated with an actual database connection
21such that operations involving the contained elements can contact the database
22as needed.
23
24Two of the elements here also build upon their "syntactic" counterparts, which
25are defined in :class:`~sqlalchemy.sql.expression.`, specifically
26:class:`~sqlalchemy.schema.Table` and :class:`~sqlalchemy.schema.Column`.
27Since these objects are part of the SQL expression language, they are usable
28as components in SQL expressions.
29
30"""
31from __future__ import annotations
32
33from abc import ABC
34import collections
35from enum import Enum
36import operator
37import typing
38from typing import Any
39from typing import Callable
40from typing import cast
41from typing import Collection
42from typing import Dict
43from typing import Iterable
44from typing import Iterator
45from typing import List
46from typing import Mapping
47from typing import NoReturn
48from typing import Optional
49from typing import overload
50from typing import Sequence as _typing_Sequence
51from typing import Set
52from typing import Tuple
53from typing import TYPE_CHECKING
54from typing import TypeVar
55from typing import Union
56
57from . import coercions
58from . import ddl
59from . import roles
60from . import type_api
61from . import visitors
62from .base import _DefaultDescriptionTuple
63from .base import _NoArg
64from .base import _NoneName
65from .base import _SentinelColumnCharacterization
66from .base import _SentinelDefaultCharacterization
67from .base import DedupeColumnCollection
68from .base import DialectKWArgs
69from .base import Executable
70from .base import SchemaEventTarget as SchemaEventTarget
71from .base import SchemaVisitable as SchemaVisitable
72from .coercions import _document_text_coercion
73from .elements import ClauseElement
74from .elements import ColumnClause
75from .elements import ColumnElement
76from .elements import quoted_name
77from .elements import TextClause
78from .selectable import TableClause
79from .type_api import to_instance
80from .visitors import ExternallyTraversible
81from .. import event
82from .. import exc
83from .. import inspection
84from .. import util
85from ..util import HasMemoized
86from ..util.typing import Final
87from ..util.typing import Literal
88from ..util.typing import Protocol
89from ..util.typing import Self
90from ..util.typing import TypedDict
91from ..util.typing import TypeGuard
92
93if typing.TYPE_CHECKING:
94 from ._typing import _AutoIncrementType
95 from ._typing import _CreateDropBind
96 from ._typing import _DDLColumnArgument
97 from ._typing import _InfoType
98 from ._typing import _TextCoercedExpressionArgument
99 from ._typing import _TypeEngineArgument
100 from .base import ColumnSet
101 from .base import ReadOnlyColumnCollection
102 from .compiler import DDLCompiler
103 from .elements import BindParameter
104 from .elements import KeyedColumnElement
105 from .functions import Function
106 from .type_api import TypeEngine
107 from .visitors import anon_map
108 from ..engine import Connection
109 from ..engine import Engine
110 from ..engine.interfaces import _CoreMultiExecuteParams
111 from ..engine.interfaces import CoreExecuteOptionsParameter
112 from ..engine.interfaces import ExecutionContext
113 from ..engine.reflection import _ReflectionInfo
114 from ..sql.selectable import FromClause
115
116_T = TypeVar("_T", bound="Any")
117_SI = TypeVar("_SI", bound="SchemaItem")
118_TAB = TypeVar("_TAB", bound="Table")
119
120
121_ConstraintNameArgument = Optional[Union[str, _NoneName]]
122
123_ServerDefaultArgument = Union[
124 "FetchedValue", str, TextClause, ColumnElement[Any]
125]
126
127_ServerOnUpdateArgument = _ServerDefaultArgument
128
129
130class SchemaConst(Enum):
131 RETAIN_SCHEMA = 1
132 """Symbol indicating that a :class:`_schema.Table`, :class:`.Sequence`
133 or in some cases a :class:`_schema.ForeignKey` object, in situations
134 where the object is being copied for a :meth:`.Table.to_metadata`
135 operation, should retain the schema name that it already has.
136
137 """
138
139 BLANK_SCHEMA = 2
140 """Symbol indicating that a :class:`_schema.Table` or :class:`.Sequence`
141 should have 'None' for its schema, even if the parent
142 :class:`_schema.MetaData` has specified a schema.
143
144 .. seealso::
145
146 :paramref:`_schema.MetaData.schema`
147
148 :paramref:`_schema.Table.schema`
149
150 :paramref:`.Sequence.schema`
151
152 """
153
154 NULL_UNSPECIFIED = 3
155 """Symbol indicating the "nullable" keyword was not passed to a Column.
156
157 This is used to distinguish between the use case of passing
158 ``nullable=None`` to a :class:`.Column`, which has special meaning
159 on some backends such as SQL Server.
160
161 """
162
163
164RETAIN_SCHEMA: Final[Literal[SchemaConst.RETAIN_SCHEMA]] = (
165 SchemaConst.RETAIN_SCHEMA
166)
167BLANK_SCHEMA: Final[Literal[SchemaConst.BLANK_SCHEMA]] = (
168 SchemaConst.BLANK_SCHEMA
169)
170NULL_UNSPECIFIED: Final[Literal[SchemaConst.NULL_UNSPECIFIED]] = (
171 SchemaConst.NULL_UNSPECIFIED
172)
173
174
175def _get_table_key(name: str, schema: Optional[str]) -> str:
176 if schema is None:
177 return name
178 else:
179 return schema + "." + name
180
181
182# this should really be in sql/util.py but we'd have to
183# break an import cycle
184def _copy_expression(
185 expression: ColumnElement[Any],
186 source_table: Optional[Table],
187 target_table: Optional[Table],
188) -> ColumnElement[Any]:
189 if source_table is None or target_table is None:
190 return expression
191
192 fixed_source_table = source_table
193 fixed_target_table = target_table
194
195 def replace(
196 element: ExternallyTraversible, **kw: Any
197 ) -> Optional[ExternallyTraversible]:
198 if (
199 isinstance(element, Column)
200 and element.table is fixed_source_table
201 and element.key in fixed_source_table.c
202 ):
203 return fixed_target_table.c[element.key]
204 else:
205 return None
206
207 return cast(
208 ColumnElement[Any],
209 visitors.replacement_traverse(expression, {}, replace),
210 )
211
212
213@inspection._self_inspects
214class SchemaItem(SchemaVisitable):
215 """Base class for items that define a database schema."""
216
217 __visit_name__ = "schema_item"
218
219 create_drop_stringify_dialect = "default"
220
221 def _init_items(self, *args: SchemaItem, **kw: Any) -> None:
222 """Initialize the list of child items for this SchemaItem."""
223 for item in args:
224 if item is not None:
225 try:
226 spwd = item._set_parent_with_dispatch
227 except AttributeError as err:
228 raise exc.ArgumentError(
229 "'SchemaItem' object, such as a 'Column' or a "
230 f"'Constraint' expected, got {item!r}"
231 ) from err
232 else:
233 spwd(self, **kw)
234
235 def __repr__(self) -> str:
236 return util.generic_repr(self, omit_kwarg=["info"])
237
238 @util.memoized_property
239 def info(self) -> _InfoType:
240 """Info dictionary associated with the object, allowing user-defined
241 data to be associated with this :class:`.SchemaItem`.
242
243 The dictionary is automatically generated when first accessed.
244 It can also be specified in the constructor of some objects,
245 such as :class:`_schema.Table` and :class:`_schema.Column`.
246
247 """
248 return {}
249
250 def _schema_item_copy(self, schema_item: _SI) -> _SI:
251 if "info" in self.__dict__:
252 schema_item.info = self.info.copy()
253 schema_item.dispatch._update(self.dispatch)
254 return schema_item
255
256 _use_schema_map = True
257
258
259class HasConditionalDDL:
260 """define a class that includes the :meth:`.HasConditionalDDL.ddl_if`
261 method, allowing for conditional rendering of DDL.
262
263 Currently applies to constraints and indexes.
264
265 .. versionadded:: 2.0
266
267
268 """
269
270 _ddl_if: Optional[ddl.DDLIf] = None
271
272 def ddl_if(
273 self,
274 dialect: Optional[str] = None,
275 callable_: Optional[ddl.DDLIfCallable] = None,
276 state: Optional[Any] = None,
277 ) -> Self:
278 r"""apply a conditional DDL rule to this schema item.
279
280 These rules work in a similar manner to the
281 :meth:`.ExecutableDDLElement.execute_if` callable, with the added
282 feature that the criteria may be checked within the DDL compilation
283 phase for a construct such as :class:`.CreateTable`.
284 :meth:`.HasConditionalDDL.ddl_if` currently applies towards the
285 :class:`.Index` construct as well as all :class:`.Constraint`
286 constructs.
287
288 :param dialect: string name of a dialect, or a tuple of string names
289 to indicate multiple dialect types.
290
291 :param callable\_: a callable that is constructed using the same form
292 as that described in
293 :paramref:`.ExecutableDDLElement.execute_if.callable_`.
294
295 :param state: any arbitrary object that will be passed to the
296 callable, if present.
297
298 .. versionadded:: 2.0
299
300 .. seealso::
301
302 :ref:`schema_ddl_ddl_if` - background and usage examples
303
304
305 """
306 self._ddl_if = ddl.DDLIf(dialect, callable_, state)
307 return self
308
309
310class HasSchemaAttr(SchemaItem):
311 """schema item that includes a top-level schema name"""
312
313 schema: Optional[str]
314
315
316class Table(
317 DialectKWArgs, HasSchemaAttr, TableClause, inspection.Inspectable["Table"]
318):
319 r"""Represent a table in a database.
320
321 e.g.::
322
323 mytable = Table(
324 "mytable",
325 metadata,
326 Column("mytable_id", Integer, primary_key=True),
327 Column("value", String(50)),
328 )
329
330 The :class:`_schema.Table`
331 object constructs a unique instance of itself based
332 on its name and optional schema name within the given
333 :class:`_schema.MetaData` object. Calling the :class:`_schema.Table`
334 constructor with the same name and same :class:`_schema.MetaData` argument
335 a second time will return the *same* :class:`_schema.Table`
336 object - in this way
337 the :class:`_schema.Table` constructor acts as a registry function.
338
339 .. seealso::
340
341 :ref:`metadata_describing` - Introduction to database metadata
342
343 """
344
345 __visit_name__ = "table"
346
347 if TYPE_CHECKING:
348
349 @util.ro_non_memoized_property
350 def primary_key(self) -> PrimaryKeyConstraint: ...
351
352 @util.ro_non_memoized_property
353 def foreign_keys(self) -> Set[ForeignKey]: ...
354
355 _columns: DedupeColumnCollection[Column[Any]]
356
357 _sentinel_column: Optional[Column[Any]]
358
359 constraints: Set[Constraint]
360 """A collection of all :class:`_schema.Constraint` objects associated with
361 this :class:`_schema.Table`.
362
363 Includes :class:`_schema.PrimaryKeyConstraint`,
364 :class:`_schema.ForeignKeyConstraint`, :class:`_schema.UniqueConstraint`,
365 :class:`_schema.CheckConstraint`. A separate collection
366 :attr:`_schema.Table.foreign_key_constraints` refers to the collection
367 of all :class:`_schema.ForeignKeyConstraint` objects, and the
368 :attr:`_schema.Table.primary_key` attribute refers to the single
369 :class:`_schema.PrimaryKeyConstraint` associated with the
370 :class:`_schema.Table`.
371
372 .. seealso::
373
374 :attr:`_schema.Table.constraints`
375
376 :attr:`_schema.Table.primary_key`
377
378 :attr:`_schema.Table.foreign_key_constraints`
379
380 :attr:`_schema.Table.indexes`
381
382 :class:`_reflection.Inspector`
383
384
385 """
386
387 indexes: Set[Index]
388 """A collection of all :class:`_schema.Index` objects associated with this
389 :class:`_schema.Table`.
390
391 .. seealso::
392
393 :meth:`_reflection.Inspector.get_indexes`
394
395 """
396
397 if TYPE_CHECKING:
398
399 @util.ro_non_memoized_property
400 def columns(self) -> ReadOnlyColumnCollection[str, Column[Any]]: ...
401
402 @util.ro_non_memoized_property
403 def exported_columns(
404 self,
405 ) -> ReadOnlyColumnCollection[str, Column[Any]]: ...
406
407 @util.ro_non_memoized_property
408 def c(self) -> ReadOnlyColumnCollection[str, Column[Any]]: ...
409
410 def _gen_cache_key(
411 self, anon_map: anon_map, bindparams: List[BindParameter[Any]]
412 ) -> Tuple[Any, ...]:
413 if self._annotations:
414 return (self,) + self._annotations_cache_key
415 else:
416 return (self,)
417
418 if not typing.TYPE_CHECKING:
419 # typing tools seem to be inconsistent in how they handle
420 # __new__, so suggest this pattern for classes that use
421 # __new__. apply typing to the __init__ method normally
422 @util.deprecated_params(
423 mustexist=(
424 "1.4",
425 "Deprecated alias of :paramref:`_schema.Table.must_exist`",
426 ),
427 )
428 def __new__(cls, *args: Any, **kw: Any) -> Any:
429 return cls._new(*args, **kw)
430
431 @classmethod
432 def _new(cls, *args: Any, **kw: Any) -> Any:
433 if not args and not kw:
434 # python3k pickle seems to call this
435 return object.__new__(cls)
436
437 try:
438 name, metadata, args = args[0], args[1], args[2:]
439 except IndexError:
440 raise TypeError(
441 "Table() takes at least two positional-only "
442 "arguments 'name' and 'metadata'"
443 )
444
445 schema = kw.get("schema", None)
446 if schema is None:
447 schema = metadata.schema
448 elif schema is BLANK_SCHEMA:
449 schema = None
450 keep_existing = kw.get("keep_existing", False)
451 extend_existing = kw.get("extend_existing", False)
452
453 if keep_existing and extend_existing:
454 msg = "keep_existing and extend_existing are mutually exclusive."
455 raise exc.ArgumentError(msg)
456
457 must_exist = kw.pop("must_exist", kw.pop("mustexist", False))
458 key = _get_table_key(name, schema)
459 if key in metadata.tables:
460 if not keep_existing and not extend_existing and bool(args):
461 raise exc.InvalidRequestError(
462 f"Table '{key}' is already defined for this MetaData "
463 "instance. Specify 'extend_existing=True' "
464 "to redefine "
465 "options and columns on an "
466 "existing Table object."
467 )
468 table = metadata.tables[key]
469 if extend_existing:
470 table._init_existing(*args, **kw)
471 return table
472 else:
473 if must_exist:
474 raise exc.InvalidRequestError(f"Table '{key}' not defined")
475 table = object.__new__(cls)
476 table.dispatch.before_parent_attach(table, metadata)
477 metadata._add_table(name, schema, table)
478 try:
479 table.__init__(name, metadata, *args, _no_init=False, **kw) # type: ignore[misc] # noqa: E501
480 table.dispatch.after_parent_attach(table, metadata)
481 return table
482 except Exception:
483 with util.safe_reraise():
484 metadata._remove_table(name, schema)
485
486 def __init__(
487 self,
488 name: str,
489 metadata: MetaData,
490 *args: SchemaItem,
491 schema: Optional[Union[str, Literal[SchemaConst.BLANK_SCHEMA]]] = None,
492 quote: Optional[bool] = None,
493 quote_schema: Optional[bool] = None,
494 autoload_with: Optional[Union[Engine, Connection]] = None,
495 autoload_replace: bool = True,
496 keep_existing: bool = False,
497 extend_existing: bool = False,
498 resolve_fks: bool = True,
499 include_columns: Optional[Collection[str]] = None,
500 implicit_returning: bool = True,
501 comment: Optional[str] = None,
502 info: Optional[Dict[Any, Any]] = None,
503 listeners: Optional[
504 _typing_Sequence[Tuple[str, Callable[..., Any]]]
505 ] = None,
506 prefixes: Optional[_typing_Sequence[str]] = None,
507 # used internally in the metadata.reflect() process
508 _extend_on: Optional[Set[Table]] = None,
509 # used by __new__ to bypass __init__
510 _no_init: bool = True,
511 # dialect-specific keyword args
512 **kw: Any,
513 ) -> None:
514 r"""Constructor for :class:`_schema.Table`.
515
516
517 :param name: The name of this table as represented in the database.
518
519 The table name, along with the value of the ``schema`` parameter,
520 forms a key which uniquely identifies this :class:`_schema.Table`
521 within
522 the owning :class:`_schema.MetaData` collection.
523 Additional calls to :class:`_schema.Table` with the same name,
524 metadata,
525 and schema name will return the same :class:`_schema.Table` object.
526
527 Names which contain no upper case characters
528 will be treated as case insensitive names, and will not be quoted
529 unless they are a reserved word or contain special characters.
530 A name with any number of upper case characters is considered
531 to be case sensitive, and will be sent as quoted.
532
533 To enable unconditional quoting for the table name, specify the flag
534 ``quote=True`` to the constructor, or use the :class:`.quoted_name`
535 construct to specify the name.
536
537 :param metadata: a :class:`_schema.MetaData`
538 object which will contain this
539 table. The metadata is used as a point of association of this table
540 with other tables which are referenced via foreign key. It also
541 may be used to associate this table with a particular
542 :class:`.Connection` or :class:`.Engine`.
543
544 :param \*args: Additional positional arguments are used primarily
545 to add the list of :class:`_schema.Column`
546 objects contained within this
547 table. Similar to the style of a CREATE TABLE statement, other
548 :class:`.SchemaItem` constructs may be added here, including
549 :class:`.PrimaryKeyConstraint`, and
550 :class:`_schema.ForeignKeyConstraint`.
551
552 :param autoload_replace: Defaults to ``True``; when using
553 :paramref:`_schema.Table.autoload_with`
554 in conjunction with :paramref:`_schema.Table.extend_existing`,
555 indicates
556 that :class:`_schema.Column` objects present in the already-existing
557 :class:`_schema.Table`
558 object should be replaced with columns of the same
559 name retrieved from the autoload process. When ``False``, columns
560 already present under existing names will be omitted from the
561 reflection process.
562
563 Note that this setting does not impact :class:`_schema.Column` objects
564 specified programmatically within the call to :class:`_schema.Table`
565 that
566 also is autoloading; those :class:`_schema.Column` objects will always
567 replace existing columns of the same name when
568 :paramref:`_schema.Table.extend_existing` is ``True``.
569
570 .. seealso::
571
572 :paramref:`_schema.Table.autoload_with`
573
574 :paramref:`_schema.Table.extend_existing`
575
576 :param autoload_with: An :class:`_engine.Engine` or
577 :class:`_engine.Connection` object,
578 or a :class:`_reflection.Inspector` object as returned by
579 :func:`_sa.inspect`
580 against one, with which this :class:`_schema.Table`
581 object will be reflected.
582 When set to a non-None value, the autoload process will take place
583 for this table against the given engine or connection.
584
585 .. seealso::
586
587 :ref:`metadata_reflection_toplevel`
588
589 :meth:`_events.DDLEvents.column_reflect`
590
591 :ref:`metadata_reflection_dbagnostic_types`
592
593 :param extend_existing: When ``True``, indicates that if this
594 :class:`_schema.Table` is already present in the given
595 :class:`_schema.MetaData`,
596 apply further arguments within the constructor to the existing
597 :class:`_schema.Table`.
598
599 If :paramref:`_schema.Table.extend_existing` or
600 :paramref:`_schema.Table.keep_existing` are not set,
601 and the given name
602 of the new :class:`_schema.Table` refers to a :class:`_schema.Table`
603 that is
604 already present in the target :class:`_schema.MetaData` collection,
605 and
606 this :class:`_schema.Table`
607 specifies additional columns or other constructs
608 or flags that modify the table's state, an
609 error is raised. The purpose of these two mutually-exclusive flags
610 is to specify what action should be taken when a
611 :class:`_schema.Table`
612 is specified that matches an existing :class:`_schema.Table`,
613 yet specifies
614 additional constructs.
615
616 :paramref:`_schema.Table.extend_existing`
617 will also work in conjunction
618 with :paramref:`_schema.Table.autoload_with` to run a new reflection
619 operation against the database, even if a :class:`_schema.Table`
620 of the same name is already present in the target
621 :class:`_schema.MetaData`; newly reflected :class:`_schema.Column`
622 objects
623 and other options will be added into the state of the
624 :class:`_schema.Table`, potentially overwriting existing columns
625 and options of the same name.
626
627 As is always the case with :paramref:`_schema.Table.autoload_with`,
628 :class:`_schema.Column` objects can be specified in the same
629 :class:`_schema.Table`
630 constructor, which will take precedence. Below, the existing
631 table ``mytable`` will be augmented with :class:`_schema.Column`
632 objects
633 both reflected from the database, as well as the given
634 :class:`_schema.Column`
635 named "y"::
636
637 Table(
638 "mytable",
639 metadata,
640 Column("y", Integer),
641 extend_existing=True,
642 autoload_with=engine,
643 )
644
645 .. seealso::
646
647 :paramref:`_schema.Table.autoload_with`
648
649 :paramref:`_schema.Table.autoload_replace`
650
651 :paramref:`_schema.Table.keep_existing`
652
653
654 :param implicit_returning: True by default - indicates that
655 RETURNING can be used, typically by the ORM, in order to fetch
656 server-generated values such as primary key values and
657 server side defaults, on those backends which support RETURNING.
658
659 In modern SQLAlchemy there is generally no reason to alter this
660 setting, except for some backend specific cases
661 (see :ref:`mssql_triggers` in the SQL Server dialect documentation
662 for one such example).
663
664 :param include_columns: A list of strings indicating a subset of
665 columns to be loaded via the ``autoload`` operation; table columns who
666 aren't present in this list will not be represented on the resulting
667 ``Table`` object. Defaults to ``None`` which indicates all columns
668 should be reflected.
669
670 :param resolve_fks: Whether or not to reflect :class:`_schema.Table`
671 objects
672 related to this one via :class:`_schema.ForeignKey` objects, when
673 :paramref:`_schema.Table.autoload_with` is
674 specified. Defaults to True. Set to False to disable reflection of
675 related tables as :class:`_schema.ForeignKey`
676 objects are encountered; may be
677 used either to save on SQL calls or to avoid issues with related tables
678 that can't be accessed. Note that if a related table is already present
679 in the :class:`_schema.MetaData` collection, or becomes present later,
680 a
681 :class:`_schema.ForeignKey` object associated with this
682 :class:`_schema.Table` will
683 resolve to that table normally.
684
685 .. versionadded:: 1.3
686
687 .. seealso::
688
689 :paramref:`.MetaData.reflect.resolve_fks`
690
691
692 :param info: Optional data dictionary which will be populated into the
693 :attr:`.SchemaItem.info` attribute of this object.
694
695 :param keep_existing: When ``True``, indicates that if this Table
696 is already present in the given :class:`_schema.MetaData`, ignore
697 further arguments within the constructor to the existing
698 :class:`_schema.Table`, and return the :class:`_schema.Table`
699 object as
700 originally created. This is to allow a function that wishes
701 to define a new :class:`_schema.Table` on first call, but on
702 subsequent calls will return the same :class:`_schema.Table`,
703 without any of the declarations (particularly constraints)
704 being applied a second time.
705
706 If :paramref:`_schema.Table.extend_existing` or
707 :paramref:`_schema.Table.keep_existing` are not set,
708 and the given name
709 of the new :class:`_schema.Table` refers to a :class:`_schema.Table`
710 that is
711 already present in the target :class:`_schema.MetaData` collection,
712 and
713 this :class:`_schema.Table`
714 specifies additional columns or other constructs
715 or flags that modify the table's state, an
716 error is raised. The purpose of these two mutually-exclusive flags
717 is to specify what action should be taken when a
718 :class:`_schema.Table`
719 is specified that matches an existing :class:`_schema.Table`,
720 yet specifies
721 additional constructs.
722
723 .. seealso::
724
725 :paramref:`_schema.Table.extend_existing`
726
727 :param listeners: A list of tuples of the form ``(<eventname>, <fn>)``
728 which will be passed to :func:`.event.listen` upon construction.
729 This alternate hook to :func:`.event.listen` allows the establishment
730 of a listener function specific to this :class:`_schema.Table` before
731 the "autoload" process begins. Historically this has been intended
732 for use with the :meth:`.DDLEvents.column_reflect` event, however
733 note that this event hook may now be associated with the
734 :class:`_schema.MetaData` object directly::
735
736 def listen_for_reflect(table, column_info):
737 "handle the column reflection event"
738 # ...
739
740
741 t = Table(
742 "sometable",
743 autoload_with=engine,
744 listeners=[("column_reflect", listen_for_reflect)],
745 )
746
747 .. seealso::
748
749 :meth:`_events.DDLEvents.column_reflect`
750
751 :param must_exist: When ``True``, indicates that this Table must already
752 be present in the given :class:`_schema.MetaData` collection, else
753 an exception is raised.
754
755 :param prefixes:
756 A list of strings to insert after CREATE in the CREATE TABLE
757 statement. They will be separated by spaces.
758
759 :param quote: Force quoting of this table's name on or off, corresponding
760 to ``True`` or ``False``. When left at its default of ``None``,
761 the column identifier will be quoted according to whether the name is
762 case sensitive (identifiers with at least one upper case character are
763 treated as case sensitive), or if it's a reserved word. This flag
764 is only needed to force quoting of a reserved word which is not known
765 by the SQLAlchemy dialect.
766
767 .. note:: setting this flag to ``False`` will not provide
768 case-insensitive behavior for table reflection; table reflection
769 will always search for a mixed-case name in a case sensitive
770 fashion. Case insensitive names are specified in SQLAlchemy only
771 by stating the name with all lower case characters.
772
773 :param quote_schema: same as 'quote' but applies to the schema identifier.
774
775 :param schema: The schema name for this table, which is required if
776 the table resides in a schema other than the default selected schema
777 for the engine's database connection. Defaults to ``None``.
778
779 If the owning :class:`_schema.MetaData` of this :class:`_schema.Table`
780 specifies its
781 own :paramref:`_schema.MetaData.schema` parameter,
782 then that schema name will
783 be applied to this :class:`_schema.Table`
784 if the schema parameter here is set
785 to ``None``. To set a blank schema name on a :class:`_schema.Table`
786 that
787 would otherwise use the schema set on the owning
788 :class:`_schema.MetaData`,
789 specify the special symbol :attr:`.BLANK_SCHEMA`.
790
791 The quoting rules for the schema name are the same as those for the
792 ``name`` parameter, in that quoting is applied for reserved words or
793 case-sensitive names; to enable unconditional quoting for the schema
794 name, specify the flag ``quote_schema=True`` to the constructor, or use
795 the :class:`.quoted_name` construct to specify the name.
796
797 :param comment: Optional string that will render an SQL comment on table
798 creation.
799
800 .. versionadded:: 1.2 Added the :paramref:`_schema.Table.comment`
801 parameter
802 to :class:`_schema.Table`.
803
804 :param \**kw: Additional keyword arguments not mentioned above are
805 dialect specific, and passed in the form ``<dialectname>_<argname>``.
806 See the documentation regarding an individual dialect at
807 :ref:`dialect_toplevel` for detail on documented arguments.
808
809 """ # noqa: E501
810 if _no_init:
811 # don't run __init__ from __new__ by default;
812 # __new__ has a specific place that __init__ is called
813 return
814
815 super().__init__(quoted_name(name, quote))
816 self.metadata = metadata
817
818 if schema is None:
819 self.schema = metadata.schema
820 elif schema is BLANK_SCHEMA:
821 self.schema = None
822 else:
823 quote_schema = quote_schema
824 assert isinstance(schema, str)
825 self.schema = quoted_name(schema, quote_schema)
826
827 self._sentinel_column = None
828
829 self.indexes = set()
830 self.constraints = set()
831 PrimaryKeyConstraint(
832 _implicit_generated=True
833 )._set_parent_with_dispatch(self)
834 self.foreign_keys = set() # type: ignore
835 self._extra_dependencies: Set[Table] = set()
836 if self.schema is not None:
837 self.fullname = "%s.%s" % (self.schema, self.name)
838 else:
839 self.fullname = self.name
840
841 self.implicit_returning = implicit_returning
842 _reflect_info = kw.pop("_reflect_info", None)
843
844 self.comment = comment
845
846 if info is not None:
847 self.info = info
848
849 if listeners is not None:
850 for evt, fn in listeners:
851 event.listen(self, evt, fn)
852
853 self._prefixes = prefixes if prefixes else []
854
855 self._extra_kwargs(**kw)
856
857 # load column definitions from the database if 'autoload' is defined
858 # we do it after the table is in the singleton dictionary to support
859 # circular foreign keys
860 if autoload_with is not None:
861 self._autoload(
862 metadata,
863 autoload_with,
864 include_columns,
865 _extend_on=_extend_on,
866 _reflect_info=_reflect_info,
867 resolve_fks=resolve_fks,
868 )
869
870 # initialize all the column, etc. objects. done after reflection to
871 # allow user-overrides
872
873 self._init_items(
874 *args,
875 allow_replacements=extend_existing
876 or keep_existing
877 or autoload_with,
878 all_names={},
879 )
880
881 def _autoload(
882 self,
883 metadata: MetaData,
884 autoload_with: Union[Engine, Connection],
885 include_columns: Optional[Collection[str]],
886 exclude_columns: Collection[str] = (),
887 resolve_fks: bool = True,
888 _extend_on: Optional[Set[Table]] = None,
889 _reflect_info: _ReflectionInfo | None = None,
890 ) -> None:
891 insp = inspection.inspect(autoload_with)
892 with insp._inspection_context() as conn_insp:
893 conn_insp.reflect_table(
894 self,
895 include_columns,
896 exclude_columns,
897 resolve_fks,
898 _extend_on=_extend_on,
899 _reflect_info=_reflect_info,
900 )
901
902 @property
903 def _sorted_constraints(self) -> List[Constraint]:
904 """Return the set of constraints as a list, sorted by creation
905 order.
906
907 """
908
909 return sorted(self.constraints, key=lambda c: c._creation_order)
910
911 @property
912 def foreign_key_constraints(self) -> Set[ForeignKeyConstraint]:
913 """:class:`_schema.ForeignKeyConstraint` objects referred to by this
914 :class:`_schema.Table`.
915
916 This list is produced from the collection of
917 :class:`_schema.ForeignKey`
918 objects currently associated.
919
920
921 .. seealso::
922
923 :attr:`_schema.Table.constraints`
924
925 :attr:`_schema.Table.foreign_keys`
926
927 :attr:`_schema.Table.indexes`
928
929 """
930 return {
931 fkc.constraint
932 for fkc in self.foreign_keys
933 if fkc.constraint is not None
934 }
935
936 def _init_existing(self, *args: Any, **kwargs: Any) -> None:
937 autoload_with = kwargs.pop("autoload_with", None)
938 autoload = kwargs.pop("autoload", autoload_with is not None)
939 autoload_replace = kwargs.pop("autoload_replace", True)
940 schema = kwargs.pop("schema", None)
941 _extend_on = kwargs.pop("_extend_on", None)
942 _reflect_info = kwargs.pop("_reflect_info", None)
943
944 # these arguments are only used with _init()
945 extend_existing = kwargs.pop("extend_existing", False)
946 keep_existing = kwargs.pop("keep_existing", False)
947
948 assert extend_existing
949 assert not keep_existing
950
951 if schema and schema != self.schema:
952 raise exc.ArgumentError(
953 f"Can't change schema of existing table "
954 f"from '{self.schema}' to '{schema}'",
955 )
956
957 include_columns = kwargs.pop("include_columns", None)
958 if include_columns is not None:
959 for c in self.c:
960 if c.name not in include_columns:
961 self._columns.remove(c)
962
963 resolve_fks = kwargs.pop("resolve_fks", True)
964
965 for key in ("quote", "quote_schema"):
966 if key in kwargs:
967 raise exc.ArgumentError(
968 "Can't redefine 'quote' or 'quote_schema' arguments"
969 )
970
971 # update `self` with these kwargs, if provided
972 self.comment = kwargs.pop("comment", self.comment)
973 self.implicit_returning = kwargs.pop(
974 "implicit_returning", self.implicit_returning
975 )
976 self.info = kwargs.pop("info", self.info)
977
978 exclude_columns: _typing_Sequence[str]
979
980 if autoload:
981 if not autoload_replace:
982 # don't replace columns already present.
983 # we'd like to do this for constraints also however we don't
984 # have simple de-duping for unnamed constraints.
985 exclude_columns = [c.name for c in self.c]
986 else:
987 exclude_columns = ()
988 self._autoload(
989 self.metadata,
990 autoload_with,
991 include_columns,
992 exclude_columns,
993 resolve_fks,
994 _extend_on=_extend_on,
995 _reflect_info=_reflect_info,
996 )
997
998 all_names = {c.name: c for c in self.c}
999 self._extra_kwargs(**kwargs)
1000 self._init_items(*args, allow_replacements=True, all_names=all_names)
1001
1002 def _extra_kwargs(self, **kwargs: Any) -> None:
1003 self._validate_dialect_kwargs(kwargs)
1004
1005 def _init_collections(self) -> None:
1006 pass
1007
1008 def _reset_exported(self) -> None:
1009 pass
1010
1011 @util.ro_non_memoized_property
1012 def _autoincrement_column(self) -> Optional[Column[int]]:
1013 return self.primary_key._autoincrement_column
1014
1015 @util.ro_memoized_property
1016 def _sentinel_column_characteristics(
1017 self,
1018 ) -> _SentinelColumnCharacterization:
1019 """determine a candidate column (or columns, in case of a client
1020 generated composite primary key) which can be used as an
1021 "insert sentinel" for an INSERT statement.
1022
1023 The returned structure, :class:`_SentinelColumnCharacterization`,
1024 includes all the details needed by :class:`.Dialect` and
1025 :class:`.SQLCompiler` to determine if these column(s) can be used
1026 as an INSERT..RETURNING sentinel for a particular database
1027 dialect.
1028
1029 .. versionadded:: 2.0.10
1030
1031 """
1032
1033 sentinel_is_explicit = False
1034 sentinel_is_autoinc = False
1035 the_sentinel: Optional[_typing_Sequence[Column[Any]]] = None
1036
1037 # see if a column was explicitly marked "insert_sentinel=True".
1038 explicit_sentinel_col = self._sentinel_column
1039
1040 if explicit_sentinel_col is not None:
1041 the_sentinel = (explicit_sentinel_col,)
1042 sentinel_is_explicit = True
1043
1044 autoinc_col = self._autoincrement_column
1045 if sentinel_is_explicit and explicit_sentinel_col is autoinc_col:
1046 assert autoinc_col is not None
1047 sentinel_is_autoinc = True
1048 elif explicit_sentinel_col is None and autoinc_col is not None:
1049 the_sentinel = (autoinc_col,)
1050 sentinel_is_autoinc = True
1051
1052 default_characterization = _SentinelDefaultCharacterization.UNKNOWN
1053
1054 if the_sentinel:
1055 the_sentinel_zero = the_sentinel[0]
1056 if the_sentinel_zero.identity:
1057 if the_sentinel_zero.identity._increment_is_negative:
1058 if sentinel_is_explicit:
1059 raise exc.InvalidRequestError(
1060 "Can't use IDENTITY default with negative "
1061 "increment as an explicit sentinel column"
1062 )
1063 else:
1064 if sentinel_is_autoinc:
1065 autoinc_col = None
1066 sentinel_is_autoinc = False
1067 the_sentinel = None
1068 else:
1069 default_characterization = (
1070 _SentinelDefaultCharacterization.IDENTITY
1071 )
1072 elif (
1073 the_sentinel_zero.default is None
1074 and the_sentinel_zero.server_default is None
1075 ):
1076 if the_sentinel_zero.nullable:
1077 raise exc.InvalidRequestError(
1078 f"Column {the_sentinel_zero} has been marked as a "
1079 "sentinel "
1080 "column with no default generation function; it "
1081 "at least needs to be marked nullable=False assuming "
1082 "user-populated sentinel values will be used."
1083 )
1084 default_characterization = (
1085 _SentinelDefaultCharacterization.NONE
1086 )
1087 elif the_sentinel_zero.default is not None:
1088 if the_sentinel_zero.default.is_sentinel:
1089 default_characterization = (
1090 _SentinelDefaultCharacterization.SENTINEL_DEFAULT
1091 )
1092 elif default_is_sequence(the_sentinel_zero.default):
1093 if the_sentinel_zero.default._increment_is_negative:
1094 if sentinel_is_explicit:
1095 raise exc.InvalidRequestError(
1096 "Can't use SEQUENCE default with negative "
1097 "increment as an explicit sentinel column"
1098 )
1099 else:
1100 if sentinel_is_autoinc:
1101 autoinc_col = None
1102 sentinel_is_autoinc = False
1103 the_sentinel = None
1104
1105 default_characterization = (
1106 _SentinelDefaultCharacterization.SEQUENCE
1107 )
1108 elif the_sentinel_zero.default.is_callable:
1109 default_characterization = (
1110 _SentinelDefaultCharacterization.CLIENTSIDE
1111 )
1112 elif the_sentinel_zero.server_default is not None:
1113 if sentinel_is_explicit:
1114 raise exc.InvalidRequestError(
1115 f"Column {the_sentinel[0]} can't be a sentinel column "
1116 "because it uses an explicit server side default "
1117 "that's not the Identity() default."
1118 )
1119
1120 default_characterization = (
1121 _SentinelDefaultCharacterization.SERVERSIDE
1122 )
1123
1124 if the_sentinel is None and self.primary_key:
1125 assert autoinc_col is None
1126
1127 # determine for non-autoincrement pk if all elements are
1128 # client side
1129 for _pkc in self.primary_key:
1130 if _pkc.server_default is not None or (
1131 _pkc.default and not _pkc.default.is_callable
1132 ):
1133 break
1134 else:
1135 the_sentinel = tuple(self.primary_key)
1136 default_characterization = (
1137 _SentinelDefaultCharacterization.CLIENTSIDE
1138 )
1139
1140 return _SentinelColumnCharacterization(
1141 the_sentinel,
1142 sentinel_is_explicit,
1143 sentinel_is_autoinc,
1144 default_characterization,
1145 )
1146
1147 @property
1148 def autoincrement_column(self) -> Optional[Column[int]]:
1149 """Returns the :class:`.Column` object which currently represents
1150 the "auto increment" column, if any, else returns None.
1151
1152 This is based on the rules for :class:`.Column` as defined by the
1153 :paramref:`.Column.autoincrement` parameter, which generally means the
1154 column within a single integer column primary key constraint that is
1155 not constrained by a foreign key. If the table does not have such
1156 a primary key constraint, then there's no "autoincrement" column.
1157 A :class:`.Table` may have only one column defined as the
1158 "autoincrement" column.
1159
1160 .. versionadded:: 2.0.4
1161
1162 .. seealso::
1163
1164 :paramref:`.Column.autoincrement`
1165
1166 """
1167 return self._autoincrement_column
1168
1169 @property
1170 def key(self) -> str:
1171 """Return the 'key' for this :class:`_schema.Table`.
1172
1173 This value is used as the dictionary key within the
1174 :attr:`_schema.MetaData.tables` collection. It is typically the same
1175 as that of :attr:`_schema.Table.name` for a table with no
1176 :attr:`_schema.Table.schema`
1177 set; otherwise it is typically of the form
1178 ``schemaname.tablename``.
1179
1180 """
1181 return _get_table_key(self.name, self.schema)
1182
1183 def __repr__(self) -> str:
1184 return "Table(%s)" % ", ".join(
1185 [repr(self.name)]
1186 + [repr(self.metadata)]
1187 + [repr(x) for x in self.columns]
1188 + ["%s=%s" % (k, repr(getattr(self, k))) for k in ["schema"]]
1189 )
1190
1191 def __str__(self) -> str:
1192 return _get_table_key(self.description, self.schema)
1193
1194 def add_is_dependent_on(self, table: Table) -> None:
1195 """Add a 'dependency' for this Table.
1196
1197 This is another Table object which must be created
1198 first before this one can, or dropped after this one.
1199
1200 Usually, dependencies between tables are determined via