codekingpro/portable-devtools
115k
1# sql/schema.py
2# Copyright (C) 2005-2024 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 _NoneName
64from .base import _SentinelColumnCharacterization
65from .base import _SentinelDefaultCharacterization
66from .base import DedupeColumnCollection
67from .base import DialectKWArgs
68from .base import Executable
69from .base import SchemaEventTarget as SchemaEventTarget
70from .coercions import _document_text_coercion
71from .elements import ClauseElement
72from .elements import ColumnClause
73from .elements import ColumnElement
74from .elements import quoted_name
75from .elements import TextClause
76from .selectable import TableClause
77from .type_api import to_instance
78from .visitors import ExternallyTraversible
79from .visitors import InternalTraversal
80from .. import event
81from .. import exc
82from .. import inspection
83from .. import util
84from ..util import HasMemoized
85from ..util.typing import Final
86from ..util.typing import Literal
87from ..util.typing import Protocol
88from ..util.typing import Self
89from ..util.typing import TypedDict
90from ..util.typing import TypeGuard
91
92if typing.TYPE_CHECKING:
93 from ._typing import _AutoIncrementType
94 from ._typing import _DDLColumnArgument
95 from ._typing import _InfoType
96 from ._typing import _TextCoercedExpressionArgument
97 from ._typing import _TypeEngineArgument
98 from .base import ReadOnlyColumnCollection
99 from .compiler import DDLCompiler
100 from .elements import BindParameter
101 from .functions import Function
102 from .type_api import TypeEngine
103 from .visitors import _TraverseInternalsType
104 from .visitors import anon_map
105 from ..engine import Connection
106 from ..engine import Engine
107 from ..engine.interfaces import _CoreMultiExecuteParams
108 from ..engine.interfaces import CoreExecuteOptionsParameter
109 from ..engine.interfaces import ExecutionContext
110 from ..engine.mock import MockConnection
111 from ..engine.reflection import _ReflectionInfo
112 from ..sql.selectable import FromClause
113
114_T = TypeVar("_T", bound="Any")
115_SI = TypeVar("_SI", bound="SchemaItem")
116_TAB = TypeVar("_TAB", bound="Table")
117
118
119_CreateDropBind = Union["Engine", "Connection", "MockConnection"]
120
121_ConstraintNameArgument = Optional[Union[str, _NoneName]]
122
123_ServerDefaultArgument = Union[
124 "FetchedValue", str, TextClause, ColumnElement[Any]
125]
126
127
128class SchemaConst(Enum):
129 RETAIN_SCHEMA = 1
130 """Symbol indicating that a :class:`_schema.Table`, :class:`.Sequence`
131 or in some cases a :class:`_schema.ForeignKey` object, in situations
132 where the object is being copied for a :meth:`.Table.to_metadata`
133 operation, should retain the schema name that it already has.
134
135 """
136
137 BLANK_SCHEMA = 2
138 """Symbol indicating that a :class:`_schema.Table` or :class:`.Sequence`
139 should have 'None' for its schema, even if the parent
140 :class:`_schema.MetaData` has specified a schema.
141
142 .. seealso::
143
144 :paramref:`_schema.MetaData.schema`
145
146 :paramref:`_schema.Table.schema`
147
148 :paramref:`.Sequence.schema`
149
150 """
151
152 NULL_UNSPECIFIED = 3
153 """Symbol indicating the "nullable" keyword was not passed to a Column.
154
155 This is used to distinguish between the use case of passing
156 ``nullable=None`` to a :class:`.Column`, which has special meaning
157 on some backends such as SQL Server.
158
159 """
160
161
162RETAIN_SCHEMA: Final[Literal[SchemaConst.RETAIN_SCHEMA]] = (
163 SchemaConst.RETAIN_SCHEMA
164)
165BLANK_SCHEMA: Final[Literal[SchemaConst.BLANK_SCHEMA]] = (
166 SchemaConst.BLANK_SCHEMA
167)
168NULL_UNSPECIFIED: Final[Literal[SchemaConst.NULL_UNSPECIFIED]] = (
169 SchemaConst.NULL_UNSPECIFIED
170)
171
172
173def _get_table_key(name: str, schema: Optional[str]) -> str:
174 if schema is None:
175 return name
176 else:
177 return schema + "." + name
178
179
180# this should really be in sql/util.py but we'd have to
181# break an import cycle
182def _copy_expression(
183 expression: ColumnElement[Any],
184 source_table: Optional[Table],
185 target_table: Optional[Table],
186) -> ColumnElement[Any]:
187 if source_table is None or target_table is None:
188 return expression
189
190 fixed_source_table = source_table
191 fixed_target_table = target_table
192
193 def replace(
194 element: ExternallyTraversible, **kw: Any
195 ) -> Optional[ExternallyTraversible]:
196 if (
197 isinstance(element, Column)
198 and element.table is fixed_source_table
199 and element.key in fixed_source_table.c
200 ):
201 return fixed_target_table.c[element.key]
202 else:
203 return None
204
205 return cast(
206 ColumnElement[Any],
207 visitors.replacement_traverse(expression, {}, replace),
208 )
209
210
211@inspection._self_inspects
212class SchemaItem(SchemaEventTarget, visitors.Visitable):
213 """Base class for items that define a database schema."""
214
215 __visit_name__ = "schema_item"
216
217 create_drop_stringify_dialect = "default"
218
219 def _init_items(self, *args: SchemaItem, **kw: Any) -> None:
220 """Initialize the list of child items for this SchemaItem."""
221 for item in args:
222 if item is not None:
223 try:
224 spwd = item._set_parent_with_dispatch
225 except AttributeError as err:
226 raise exc.ArgumentError(
227 "'SchemaItem' object, such as a 'Column' or a "
228 f"'Constraint' expected, got {item!r}"
229 ) from err
230 else:
231 spwd(self, **kw)
232
233 def __repr__(self) -> str:
234 return util.generic_repr(self, omit_kwarg=["info"])
235
236 @util.memoized_property
237 def info(self) -> _InfoType:
238 """Info dictionary associated with the object, allowing user-defined
239 data to be associated with this :class:`.SchemaItem`.
240
241 The dictionary is automatically generated when first accessed.
242 It can also be specified in the constructor of some objects,
243 such as :class:`_schema.Table` and :class:`_schema.Column`.
244
245 """
246 return {}
247
248 def _schema_item_copy(self, schema_item: _SI) -> _SI:
249 if "info" in self.__dict__:
250 schema_item.info = self.info.copy()
251 schema_item.dispatch._update(self.dispatch)
252 return schema_item
253
254 _use_schema_map = True
255
256
257class HasConditionalDDL:
258 """define a class that includes the :meth:`.HasConditionalDDL.ddl_if`
259 method, allowing for conditional rendering of DDL.
260
261 Currently applies to constraints and indexes.
262
263 .. versionadded:: 2.0
264
265
266 """
267
268 _ddl_if: Optional[ddl.DDLIf] = None
269
270 def ddl_if(
271 self,
272 dialect: Optional[str] = None,
273 callable_: Optional[ddl.DDLIfCallable] = None,
274 state: Optional[Any] = None,
275 ) -> Self:
276 r"""apply a conditional DDL rule to this schema item.
277
278 These rules work in a similar manner to the
279 :meth:`.ExecutableDDLElement.execute_if` callable, with the added
280 feature that the criteria may be checked within the DDL compilation
281 phase for a construct such as :class:`.CreateTable`.
282 :meth:`.HasConditionalDDL.ddl_if` currently applies towards the
283 :class:`.Index` construct as well as all :class:`.Constraint`
284 constructs.
285
286 :param dialect: string name of a dialect, or a tuple of string names
287 to indicate multiple dialect types.
288
289 :param callable\_: a callable that is constructed using the same form
290 as that described in
291 :paramref:`.ExecutableDDLElement.execute_if.callable_`.
292
293 :param state: any arbitrary object that will be passed to the
294 callable, if present.
295
296 .. versionadded:: 2.0
297
298 .. seealso::
299
300 :ref:`schema_ddl_ddl_if` - background and usage examples
301
302
303 """
304 self._ddl_if = ddl.DDLIf(dialect, callable_, state)
305 return self
306
307
308class HasSchemaAttr(SchemaItem):
309 """schema item that includes a top-level schema name"""
310
311 schema: Optional[str]
312
313
314class Table(
315 DialectKWArgs, HasSchemaAttr, TableClause, inspection.Inspectable["Table"]
316):
317 r"""Represent a table in a database.
318
319 e.g.::
320
321 mytable = Table(
322 "mytable", metadata,
323 Column('mytable_id', Integer, primary_key=True),
324 Column('value', String(50))
325 )
326
327 The :class:`_schema.Table`
328 object constructs a unique instance of itself based
329 on its name and optional schema name within the given
330 :class:`_schema.MetaData` object. Calling the :class:`_schema.Table`
331 constructor with the same name and same :class:`_schema.MetaData` argument
332 a second time will return the *same* :class:`_schema.Table`
333 object - in this way
334 the :class:`_schema.Table` constructor acts as a registry function.
335
336 .. seealso::
337
338 :ref:`metadata_describing` - Introduction to database metadata
339
340 """
341
342 __visit_name__ = "table"
343
344 if TYPE_CHECKING:
345
346 @util.ro_non_memoized_property
347 def primary_key(self) -> PrimaryKeyConstraint: ...
348
349 @util.ro_non_memoized_property
350 def foreign_keys(self) -> Set[ForeignKey]: ...
351
352 _columns: DedupeColumnCollection[Column[Any]]
353
354 _sentinel_column: Optional[Column[Any]]
355
356 constraints: Set[Constraint]
357 """A collection of all :class:`_schema.Constraint` objects associated with
358 this :class:`_schema.Table`.
359
360 Includes :class:`_schema.PrimaryKeyConstraint`,
361 :class:`_schema.ForeignKeyConstraint`, :class:`_schema.UniqueConstraint`,
362 :class:`_schema.CheckConstraint`. A separate collection
363 :attr:`_schema.Table.foreign_key_constraints` refers to the collection
364 of all :class:`_schema.ForeignKeyConstraint` objects, and the
365 :attr:`_schema.Table.primary_key` attribute refers to the single
366 :class:`_schema.PrimaryKeyConstraint` associated with the
367 :class:`_schema.Table`.
368
369 .. seealso::
370
371 :attr:`_schema.Table.constraints`
372
373 :attr:`_schema.Table.primary_key`
374
375 :attr:`_schema.Table.foreign_key_constraints`
376
377 :attr:`_schema.Table.indexes`
378
379 :class:`_reflection.Inspector`
380
381
382 """
383
384 indexes: Set[Index]
385 """A collection of all :class:`_schema.Index` objects associated with this
386 :class:`_schema.Table`.
387
388 .. seealso::
389
390 :meth:`_reflection.Inspector.get_indexes`
391
392 """
393
394 _traverse_internals: _TraverseInternalsType = (
395 TableClause._traverse_internals
396 + [("schema", InternalTraversal.dp_string)]
397 )
398
399 if TYPE_CHECKING:
400
401 @util.ro_non_memoized_property
402 def columns(self) -> ReadOnlyColumnCollection[str, Column[Any]]: ...
403
404 @util.ro_non_memoized_property
405 def exported_columns(
406 self,
407 ) -> ReadOnlyColumnCollection[str, Column[Any]]: ...
408
409 @util.ro_non_memoized_property
410 def c(self) -> ReadOnlyColumnCollection[str, Column[Any]]: ...
411
412 def _gen_cache_key(
413 self, anon_map: anon_map, bindparams: List[BindParameter[Any]]
414 ) -> Tuple[Any, ...]:
415 if self._annotations:
416 return (self,) + self._annotations_cache_key
417 else:
418 return (self,)
419
420 if not typing.TYPE_CHECKING:
421 # typing tools seem to be inconsistent in how they handle
422 # __new__, so suggest this pattern for classes that use
423 # __new__. apply typing to the __init__ method normally
424 @util.deprecated_params(
425 mustexist=(
426 "1.4",
427 "Deprecated alias of :paramref:`_schema.Table.must_exist`",
428 ),
429 )
430 def __new__(cls, *args: Any, **kw: Any) -> Any:
431 return cls._new(*args, **kw)
432
433 @classmethod
434 def _new(cls, *args: Any, **kw: Any) -> Any:
435 if not args and not kw:
436 # python3k pickle seems to call this
437 return object.__new__(cls)
438
439 try:
440 name, metadata, args = args[0], args[1], args[2:]
441 except IndexError:
442 raise TypeError(
443 "Table() takes at least two positional-only "
444 "arguments 'name' and 'metadata'"
445 )
446
447 schema = kw.get("schema", None)
448 if schema is None:
449 schema = metadata.schema
450 elif schema is BLANK_SCHEMA:
451 schema = None
452 keep_existing = kw.get("keep_existing", False)
453 extend_existing = kw.get("extend_existing", False)
454
455 if keep_existing and extend_existing:
456 msg = "keep_existing and extend_existing are mutually exclusive."
457 raise exc.ArgumentError(msg)
458
459 must_exist = kw.pop("must_exist", kw.pop("mustexist", False))
460 key = _get_table_key(name, schema)
461 if key in metadata.tables:
462 if not keep_existing and not extend_existing and bool(args):
463 raise exc.InvalidRequestError(
464 f"Table '{key}' is already defined for this MetaData "
465 "instance. Specify 'extend_existing=True' "
466 "to redefine "
467 "options and columns on an "
468 "existing Table object."
469 )
470 table = metadata.tables[key]
471 if extend_existing:
472 table._init_existing(*args, **kw)
473 return table
474 else:
475 if must_exist:
476 raise exc.InvalidRequestError(f"Table '{key}' not defined")
477 table = object.__new__(cls)
478 table.dispatch.before_parent_attach(table, metadata)
479 metadata._add_table(name, schema, table)
480 try:
481 table.__init__(name, metadata, *args, _no_init=False, **kw)
482 table.dispatch.after_parent_attach(table, metadata)
483 return table
484 except Exception:
485 with util.safe_reraise():
486 metadata._remove_table(name, schema)
487
488 def __init__(
489 self,
490 name: str,
491 metadata: MetaData,
492 *args: SchemaItem,
493 schema: Optional[Union[str, Literal[SchemaConst.BLANK_SCHEMA]]] = None,
494 quote: Optional[bool] = None,
495 quote_schema: Optional[bool] = None,
496 autoload_with: Optional[Union[Engine, Connection]] = None,
497 autoload_replace: bool = True,
498 keep_existing: bool = False,
499 extend_existing: bool = False,
500 resolve_fks: bool = True,
501 include_columns: Optional[Collection[str]] = None,
502 implicit_returning: bool = True,
503 comment: Optional[str] = None,
504 info: Optional[Dict[Any, Any]] = None,
505 listeners: Optional[
506 _typing_Sequence[Tuple[str, Callable[..., Any]]]
507 ] = None,
508 prefixes: Optional[_typing_Sequence[str]] = None,
509 # used internally in the metadata.reflect() process
510 _extend_on: Optional[Set[Table]] = None,
511 # used by __new__ to bypass __init__
512 _no_init: bool = True,
513 # dialect-specific keyword args
514 **kw: Any,
515 ) -> None:
516 r"""Constructor for :class:`_schema.Table`.
517
518
519 :param name: The name of this table as represented in the database.
520
521 The table name, along with the value of the ``schema`` parameter,
522 forms a key which uniquely identifies this :class:`_schema.Table`
523 within
524 the owning :class:`_schema.MetaData` collection.
525 Additional calls to :class:`_schema.Table` with the same name,
526 metadata,
527 and schema name will return the same :class:`_schema.Table` object.
528
529 Names which contain no upper case characters
530 will be treated as case insensitive names, and will not be quoted
531 unless they are a reserved word or contain special characters.
532 A name with any number of upper case characters is considered
533 to be case sensitive, and will be sent as quoted.
534
535 To enable unconditional quoting for the table name, specify the flag
536 ``quote=True`` to the constructor, or use the :class:`.quoted_name`
537 construct to specify the name.
538
539 :param metadata: a :class:`_schema.MetaData`
540 object which will contain this
541 table. The metadata is used as a point of association of this table
542 with other tables which are referenced via foreign key. It also
543 may be used to associate this table with a particular
544 :class:`.Connection` or :class:`.Engine`.
545
546 :param \*args: Additional positional arguments are used primarily
547 to add the list of :class:`_schema.Column`
548 objects contained within this
549 table. Similar to the style of a CREATE TABLE statement, other
550 :class:`.SchemaItem` constructs may be added here, including
551 :class:`.PrimaryKeyConstraint`, and
552 :class:`_schema.ForeignKeyConstraint`.
553
554 :param autoload_replace: Defaults to ``True``; when using
555 :paramref:`_schema.Table.autoload_with`
556 in conjunction with :paramref:`_schema.Table.extend_existing`,
557 indicates
558 that :class:`_schema.Column` objects present in the already-existing
559 :class:`_schema.Table`
560 object should be replaced with columns of the same
561 name retrieved from the autoload process. When ``False``, columns
562 already present under existing names will be omitted from the
563 reflection process.
564
565 Note that this setting does not impact :class:`_schema.Column` objects
566 specified programmatically within the call to :class:`_schema.Table`
567 that
568 also is autoloading; those :class:`_schema.Column` objects will always
569 replace existing columns of the same name when
570 :paramref:`_schema.Table.extend_existing` is ``True``.
571
572 .. seealso::
573
574 :paramref:`_schema.Table.autoload_with`
575
576 :paramref:`_schema.Table.extend_existing`
577
578 :param autoload_with: An :class:`_engine.Engine` or
579 :class:`_engine.Connection` object,
580 or a :class:`_reflection.Inspector` object as returned by
581 :func:`_sa.inspect`
582 against one, with which this :class:`_schema.Table`
583 object will be reflected.
584 When set to a non-None value, the autoload process will take place
585 for this table against the given engine or connection.
586
587 .. seealso::
588
589 :ref:`metadata_reflection_toplevel`
590
591 :meth:`_events.DDLEvents.column_reflect`
592
593 :ref:`metadata_reflection_dbagnostic_types`
594
595 :param extend_existing: When ``True``, indicates that if this
596 :class:`_schema.Table` is already present in the given
597 :class:`_schema.MetaData`,
598 apply further arguments within the constructor to the existing
599 :class:`_schema.Table`.
600
601 If :paramref:`_schema.Table.extend_existing` or
602 :paramref:`_schema.Table.keep_existing` are not set,
603 and the given name
604 of the new :class:`_schema.Table` refers to a :class:`_schema.Table`
605 that is
606 already present in the target :class:`_schema.MetaData` collection,
607 and
608 this :class:`_schema.Table`
609 specifies additional columns or other constructs
610 or flags that modify the table's state, an
611 error is raised. The purpose of these two mutually-exclusive flags
612 is to specify what action should be taken when a
613 :class:`_schema.Table`
614 is specified that matches an existing :class:`_schema.Table`,
615 yet specifies
616 additional constructs.
617
618 :paramref:`_schema.Table.extend_existing`
619 will also work in conjunction
620 with :paramref:`_schema.Table.autoload_with` to run a new reflection
621 operation against the database, even if a :class:`_schema.Table`
622 of the same name is already present in the target
623 :class:`_schema.MetaData`; newly reflected :class:`_schema.Column`
624 objects
625 and other options will be added into the state of the
626 :class:`_schema.Table`, potentially overwriting existing columns
627 and options of the same name.
628
629 As is always the case with :paramref:`_schema.Table.autoload_with`,
630 :class:`_schema.Column` objects can be specified in the same
631 :class:`_schema.Table`
632 constructor, which will take precedence. Below, the existing
633 table ``mytable`` will be augmented with :class:`_schema.Column`
634 objects
635 both reflected from the database, as well as the given
636 :class:`_schema.Column`
637 named "y"::
638
639 Table("mytable", 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 t = Table(
741 'sometable',
742 autoload_with=engine,
743 listeners=[
744 ('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