Shortcuts on this page
r m x toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1# sql/schema.py
2# Copyright (C) 2005-2025 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
8"""The schema module provides the building blocks for database metadata.
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.
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.
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.
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.
30"""
31from __future__ import annotations
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 Final
44from typing import Iterable
45from typing import Iterator
46from typing import List
47from typing import Literal
48from typing import Mapping
49from typing import NoReturn
50from typing import Optional
51from typing import overload
52from typing import Protocol
53from typing import Sequence as _typing_Sequence
54from typing import Set
55from typing import Tuple
56from typing import Type
57from typing import TYPE_CHECKING
58from typing import TypedDict
59from typing import TypeGuard
60from typing import TypeVar
61from typing import Union
63from . import coercions
64from . import ddl
65from . import roles
66from . import type_api
67from . import visitors
68from .base import _DefaultDescriptionTuple
69from .base import _NoArg
70from .base import _NoneName
71from .base import _SentinelColumnCharacterization
72from .base import _SentinelDefaultCharacterization
73from .base import DedupeColumnCollection
74from .base import DialectKWArgs
75from .base import Executable
76from .base import SchemaEventTarget as SchemaEventTarget
77from .base import SchemaVisitable as SchemaVisitable
78from .coercions import _document_text_coercion
79from .ddl import CheckFirst
80from .elements import ClauseElement
81from .elements import ColumnClause
82from .elements import ColumnElement
83from .elements import quoted_name
84from .elements import TextClause
85from .selectable import TableClause
86from .type_api import to_instance
87from .visitors import ExternallyTraversible
88from .. import event
89from .. import exc
90from .. import inspection
91from .. import util
92from ..util import HasMemoized
93from ..util.typing import Self
95if typing.TYPE_CHECKING:
96 from ._typing import _AutoIncrementType
97 from ._typing import _CreateDropBind
98 from ._typing import _DDLColumnArgument
99 from ._typing import _DDLColumnReferenceArgument
100 from ._typing import _InfoType
101 from ._typing import _TextCoercedExpressionArgument
102 from ._typing import _TypeEngineArgument
103 from .base import ColumnSet
104 from .base import ReadOnlyColumnCollection
105 from .compiler import DDLCompiler
106 from .elements import BindParameter
107 from .elements import KeyedColumnElement
108 from .functions import Function
109 from .sqltypes import SchemaType
110 from .type_api import TypeEngine
111 from .visitors import anon_map
112 from ..engine import Connection
113 from ..engine import Engine
114 from ..engine.interfaces import _CoreMultiExecuteParams
115 from ..engine.interfaces import CoreExecuteOptionsParameter
116 from ..engine.interfaces import ExecutionContext
117 from ..engine.reflection import _ReflectionInfo
118 from ..sql.selectable import FromClause
120_T = TypeVar("_T", bound="Any")
121_SI = TypeVar("_SI", bound="SchemaItem")
122_TAB = TypeVar("_TAB", bound="Table")
125_ConstraintNameArgument = Optional[Union[str, _NoneName]]
127_ServerDefaultArgument = Union[
128 "FetchedValue", str, TextClause, ColumnElement[Any]
129]
131_ServerOnUpdateArgument = _ServerDefaultArgument
134class SchemaConst(Enum):
135 RETAIN_SCHEMA = 1
136 """Symbol indicating that a :class:`_schema.Table`, :class:`.Sequence`
137 or in some cases a :class:`_schema.ForeignKey` object, in situations
138 where the object is being copied for a :meth:`.Table.to_metadata`
139 operation, should retain the schema name that it already has.
141 """
143 BLANK_SCHEMA = 2
144 """Symbol indicating that a :class:`_schema.Table` or :class:`.Sequence`
145 should have 'None' for its schema, even if the parent
146 :class:`_schema.MetaData` has specified a schema.
148 .. seealso::
150 :paramref:`_schema.MetaData.schema`
152 :paramref:`_schema.Table.schema`
154 :paramref:`.Sequence.schema`
156 """
158 NULL_UNSPECIFIED = 3
159 """Symbol indicating the "nullable" keyword was not passed to a Column.
161 This is used to distinguish between the use case of passing
162 ``nullable=None`` to a :class:`.Column`, which has special meaning
163 on some backends such as SQL Server.
165 """
168RETAIN_SCHEMA: Final[Literal[SchemaConst.RETAIN_SCHEMA]] = (
169 SchemaConst.RETAIN_SCHEMA
170)
171BLANK_SCHEMA: Final[Literal[SchemaConst.BLANK_SCHEMA]] = (
172 SchemaConst.BLANK_SCHEMA
173)
174NULL_UNSPECIFIED: Final[Literal[SchemaConst.NULL_UNSPECIFIED]] = (
175 SchemaConst.NULL_UNSPECIFIED
176)
179def _get_table_key(name: str, schema: Optional[str]) -> str:
180 if schema is None:
181 return name
182 else:
183 return schema + "." + name
186# this should really be in sql/util.py but we'd have to
187# break an import cycle
188def _copy_expression(
189 expression: ColumnElement[Any],
190 source_table: Optional[Table],
191 target_table: Optional[Table],
192) -> ColumnElement[Any]:
193 if source_table is None or target_table is None:
194 return expression
196 fixed_source_table = source_table
197 fixed_target_table = target_table
199 def replace(
200 element: ExternallyTraversible, **kw: Any
201 ) -> Optional[ExternallyTraversible]:
202 if (
203 isinstance(element, Column)
204 and element.table is fixed_source_table
205 and element.key in fixed_source_table.c
206 ):
207 return fixed_target_table.c[element.key]
208 else:
209 return None
211 return cast(
212 ColumnElement[Any],
213 visitors.replacement_traverse(expression, {}, replace),
214 )
217@inspection._self_inspects
218class SchemaItem(SchemaVisitable):
219 """Base class for items that define a database schema."""
221 __visit_name__ = "schema_item"
223 create_drop_stringify_dialect = "default"
225 def _init_items(self, *args: SchemaItem, **kw: Any) -> None:
226 """Initialize the list of child items for this SchemaItem."""
227 for item in args:
228 if item is not None:
229 try:
230 spwd = item._set_parent_with_dispatch
231 except AttributeError as err:
232 raise exc.ArgumentError(
233 "'SchemaItem' object, such as a 'Column' or a "
234 f"'Constraint' expected, got {item!r}"
235 ) from err
236 else:
237 spwd(self, **kw)
239 def __repr__(self) -> str:
240 return util.generic_repr(self, omit_kwarg=["info"])
242 @util.memoized_property
243 def info(self) -> _InfoType:
244 """Info dictionary associated with the object, allowing user-defined
245 data to be associated with this :class:`.SchemaItem`.
247 The dictionary is automatically generated when first accessed.
248 It can also be specified in the constructor of some objects,
249 such as :class:`_schema.Table` and :class:`_schema.Column`.
251 """
252 return {}
254 def _schema_item_copy(self, schema_item: _SI) -> _SI:
255 if "info" in self.__dict__:
256 schema_item.info = self.info.copy()
257 schema_item.dispatch._update(self.dispatch)
258 return schema_item
260 _use_schema_map = True
263class HasConditionalDDL:
264 """define a class that includes the :meth:`.HasConditionalDDL.ddl_if`
265 method, allowing for conditional rendering of DDL.
267 Currently applies to constraints and indexes.
269 .. versionadded:: 2.0
272 """
274 _ddl_if: Optional[ddl.DDLIf] = None
276 def ddl_if(
277 self,
278 dialect: Optional[str] = None,
279 callable_: Optional[ddl.DDLIfCallable] = None,
280 state: Optional[Any] = None,
281 ) -> Self:
282 r"""apply a conditional DDL rule to this schema item.
284 These rules work in a similar manner to the
285 :meth:`.ExecutableDDLElement.execute_if` callable, with the added
286 feature that the criteria may be checked within the DDL compilation
287 phase for a construct such as :class:`.CreateTable`.
288 :meth:`.HasConditionalDDL.ddl_if` currently applies towards the
289 :class:`.Index` construct as well as all :class:`.Constraint`
290 constructs.
292 :param dialect: string name of a dialect, or a tuple of string names
293 to indicate multiple dialect types.
295 :param callable\_: a callable that is constructed using the same form
296 as that described in
297 :paramref:`.ExecutableDDLElement.execute_if.callable_`.
299 :param state: any arbitrary object that will be passed to the
300 callable, if present.
302 .. versionadded:: 2.0
304 .. seealso::
306 :ref:`schema_ddl_ddl_if` - background and usage examples
309 """
310 self._ddl_if = ddl.DDLIf(dialect, callable_, state)
311 return self
314class HasSchemaAttr(SchemaItem):
315 """schema item that includes a top-level schema name"""
317 schema: Optional[str]
320class Table(
321 DialectKWArgs, HasSchemaAttr, TableClause, inspection.Inspectable["Table"]
322):
323 r"""Represent a table in a database.
325 e.g.::
327 mytable = Table(
328 "mytable",
329 metadata,
330 Column("mytable_id", Integer, primary_key=True),
331 Column("value", String(50)),
332 )
334 The :class:`_schema.Table`
335 object constructs a unique instance of itself based
336 on its name and optional schema name within the given
337 :class:`_schema.MetaData` object. Calling the :class:`_schema.Table`
338 constructor with the same name and same :class:`_schema.MetaData` argument
339 a second time will return the *same* :class:`_schema.Table`
340 object - in this way
341 the :class:`_schema.Table` constructor acts as a registry function.
343 .. seealso::
345 :ref:`metadata_describing` - Introduction to database metadata
347 """
349 __visit_name__ = "table"
351 if TYPE_CHECKING:
353 @util.ro_non_memoized_property
354 def primary_key(self) -> PrimaryKeyConstraint: ...
356 @util.ro_non_memoized_property
357 def foreign_keys(self) -> Set[ForeignKey]: ...
359 _columns: DedupeColumnCollection[Column[Any]] # type: ignore[assignment]
361 _sentinel_column: Optional[Column[Any]]
363 constraints: Set[Constraint]
364 """A collection of all :class:`_schema.Constraint` objects associated with
365 this :class:`_schema.Table`.
367 Includes :class:`_schema.PrimaryKeyConstraint`,
368 :class:`_schema.ForeignKeyConstraint`, :class:`_schema.UniqueConstraint`,
369 :class:`_schema.CheckConstraint`. A separate collection
370 :attr:`_schema.Table.foreign_key_constraints` refers to the collection
371 of all :class:`_schema.ForeignKeyConstraint` objects, and the
372 :attr:`_schema.Table.primary_key` attribute refers to the single
373 :class:`_schema.PrimaryKeyConstraint` associated with the
374 :class:`_schema.Table`.
376 .. seealso::
378 :attr:`_schema.Table.constraints`
380 :attr:`_schema.Table.primary_key`
382 :attr:`_schema.Table.foreign_key_constraints`
384 :attr:`_schema.Table.indexes`
386 :class:`_reflection.Inspector`
389 """
391 indexes: Set[Index]
392 """A collection of all :class:`_schema.Index` objects associated with this
393 :class:`_schema.Table`.
395 .. seealso::
397 :meth:`_reflection.Inspector.get_indexes`
399 """
401 if TYPE_CHECKING:
403 @util.ro_non_memoized_property
404 def columns(self) -> ReadOnlyColumnCollection[str, Column[Any]]: ...
406 @util.ro_non_memoized_property
407 def exported_columns(
408 self,
409 ) -> ReadOnlyColumnCollection[str, Column[Any]]: ...
411 @util.ro_non_memoized_property
412 def c(self) -> ReadOnlyColumnCollection[str, Column[Any]]: ...
414 def _gen_cache_key(
415 self, anon_map: anon_map, bindparams: List[BindParameter[Any]]
416 ) -> Tuple[Any, ...]:
417 if self._annotations:
418 return (self,) + self._annotations_cache_key
419 else:
420 return (self,)
422 if not typing.TYPE_CHECKING:
423 # typing tools seem to be inconsistent in how they handle
424 # __new__, so suggest this pattern for classes that use
425 # __new__. apply typing to the __init__ method normally
426 @util.deprecated_params(
427 mustexist=(
428 "1.4",
429 "Deprecated alias of :paramref:`_schema.Table.must_exist`",
430 ),
431 )
432 def __new__(cls, *args: Any, **kw: Any) -> Any:
433 return cls._new(*args, **kw)
435 @classmethod
436 def _new(cls, *args: Any, **kw: Any) -> Any:
437 if not args and not kw:
438 # python3k pickle seems to call this
439 return object.__new__(cls)
441 try:
442 name, metadata, args = args[0], args[1], args[2:]
443 except IndexError:
444 raise TypeError(
445 "Table() takes at least two positional-only "
446 "arguments 'name' and 'metadata'"
447 )
449 schema = kw.get("schema", None)
450 if schema is None:
451 schema = metadata.schema
452 elif schema is BLANK_SCHEMA:
453 schema = None
454 keep_existing = kw.get("keep_existing", False)
455 extend_existing = kw.get("extend_existing", False)
457 if keep_existing and extend_existing:
458 msg = "keep_existing and extend_existing are mutually exclusive."
459 raise exc.ArgumentError(msg)
461 must_exist = kw.pop("must_exist", kw.pop("mustexist", False))
462 key = _get_table_key(name, schema)
463 if key in metadata.tables:
464 if not keep_existing and not extend_existing and bool(args):
465 raise exc.InvalidRequestError(
466 f"Table '{key}' is already defined for this MetaData "
467 "instance. Specify 'extend_existing=True' "
468 "to redefine "
469 "options and columns on an "
470 "existing Table object."
471 )
472 table = metadata.tables[key]
473 if extend_existing:
474 table._init_existing(*args, **kw)
475 return table
476 else:
477 if must_exist:
478 raise exc.InvalidRequestError(f"Table '{key}' not defined")
479 table = object.__new__(cls)
480 table.dispatch.before_parent_attach(table, metadata)
481 metadata._add_table(name, schema, table)
482 try:
483 table.__init__(name, metadata, *args, _no_init=False, **kw) # type: ignore[misc] # noqa: E501
484 table.dispatch.after_parent_attach(table, metadata)
485 return table
486 except Exception:
487 with util.safe_reraise():
488 metadata._remove_table(name, schema)
490 def __init__(
491 self,
492 name: str,
493 metadata: MetaData,
494 *args: SchemaItem,
495 schema: Optional[Union[str, Literal[SchemaConst.BLANK_SCHEMA]]] = None,
496 quote: Optional[bool] = None,
497 quote_schema: Optional[bool] = None,
498 autoload_with: Optional[Union[Engine, Connection]] = None,
499 autoload_replace: bool = True,
500 keep_existing: bool = False,
501 extend_existing: bool = False,
502 resolve_fks: bool = True,
503 include_columns: Optional[Collection[str]] = None,
504 implicit_returning: bool = True,
505 comment: Optional[str] = None,
506 info: Optional[Dict[Any, Any]] = None,
507 listeners: Optional[
508 _typing_Sequence[Tuple[str, Callable[..., Any]]]
509 ] = None,
510 prefixes: Optional[_typing_Sequence[str]] = None,
511 # used internally in the metadata.reflect() process
512 _extend_on: Optional[Set[Table]] = None,
513 # used by __new__ to bypass __init__
514 _no_init: bool = True,
515 # dialect-specific keyword args
516 **kw: Any,
517 ) -> None:
518 r"""Constructor for :class:`_schema.Table`.
521 :param name: The name of this table as represented in the database.
523 The table name, along with the value of the ``schema`` parameter,
524 forms a key which uniquely identifies this :class:`_schema.Table`
525 within
526 the owning :class:`_schema.MetaData` collection.
527 Additional calls to :class:`_schema.Table` with the same name,
528 metadata,
529 and schema name will return the same :class:`_schema.Table` object.
531 Names which contain no upper case characters
532 will be treated as case insensitive names, and will not be quoted
533 unless they are a reserved word or contain special characters.
534 A name with any number of upper case characters is considered
535 to be case sensitive, and will be sent as quoted.
537 To enable unconditional quoting for the table name, specify the flag
538 ``quote=True`` to the constructor, or use the :class:`.quoted_name`
539 construct to specify the name.
541 :param metadata: a :class:`_schema.MetaData`
542 object which will contain this
543 table. The metadata is used as a point of association of this table
544 with other tables which are referenced via foreign key. It also
545 may be used to associate this table with a particular
546 :class:`.Connection` or :class:`.Engine`.
548 :param \*args: Additional positional arguments are used primarily
549 to add the list of :class:`_schema.Column`
550 objects contained within this
551 table. Similar to the style of a CREATE TABLE statement, other
552 :class:`.SchemaItem` constructs may be added here, including
553 :class:`.PrimaryKeyConstraint`, and
554 :class:`_schema.ForeignKeyConstraint`.
556 :param autoload_replace: Defaults to ``True``; when using
557 :paramref:`_schema.Table.autoload_with`
558 in conjunction with :paramref:`_schema.Table.extend_existing`,
559 indicates
560 that :class:`_schema.Column` objects present in the already-existing
561 :class:`_schema.Table`
562 object should be replaced with columns of the same
563 name retrieved from the autoload process. When ``False``, columns
564 already present under existing names will be omitted from the
565 reflection process.
567 Note that this setting does not impact :class:`_schema.Column` objects
568 specified programmatically within the call to :class:`_schema.Table`
569 that
570 also is autoloading; those :class:`_schema.Column` objects will always
571 replace existing columns of the same name when
572 :paramref:`_schema.Table.extend_existing` is ``True``.
574 .. seealso::
576 :paramref:`_schema.Table.autoload_with`
578 :paramref:`_schema.Table.extend_existing`
580 :param autoload_with: An :class:`_engine.Engine` or
581 :class:`_engine.Connection` object,
582 or a :class:`_reflection.Inspector` object as returned by
583 :func:`_sa.inspect`
584 against one, with which this :class:`_schema.Table`
585 object will be reflected.
586 When set to a non-None value, the autoload process will take place
587 for this table against the given engine or connection.
589 .. seealso::
591 :ref:`metadata_reflection_toplevel`
593 :meth:`_events.DDLEvents.column_reflect`
595 :ref:`metadata_reflection_dbagnostic_types`
597 :param extend_existing: When ``True``, indicates that if this
598 :class:`_schema.Table` is already present in the given
599 :class:`_schema.MetaData`,
600 apply further arguments within the constructor to the existing
601 :class:`_schema.Table`.
603 If :paramref:`_schema.Table.extend_existing` or
604 :paramref:`_schema.Table.keep_existing` are not set,
605 and the given name
606 of the new :class:`_schema.Table` refers to a :class:`_schema.Table`
607 that is
608 already present in the target :class:`_schema.MetaData` collection,
609 and
610 this :class:`_schema.Table`
611 specifies additional columns or other constructs
612 or flags that modify the table's state, an
613 error is raised. The purpose of these two mutually-exclusive flags
614 is to specify what action should be taken when a
615 :class:`_schema.Table`
616 is specified that matches an existing :class:`_schema.Table`,
617 yet specifies
618 additional constructs.
620 :paramref:`_schema.Table.extend_existing`
621 will also work in conjunction
622 with :paramref:`_schema.Table.autoload_with` to run a new reflection
623 operation against the database, even if a :class:`_schema.Table`
624 of the same name is already present in the target
625 :class:`_schema.MetaData`; newly reflected :class:`_schema.Column`
626 objects
627 and other options will be added into the state of the
628 :class:`_schema.Table`, potentially overwriting existing columns
629 and options of the same name.
631 As is always the case with :paramref:`_schema.Table.autoload_with`,
632 :class:`_schema.Column` objects can be specified in the same
633 :class:`_schema.Table`
634 constructor, which will take precedence. Below, the existing
635 table ``mytable`` will be augmented with :class:`_schema.Column`
636 objects
637 both reflected from the database, as well as the given
638 :class:`_schema.Column`
639 named "y"::
641 Table(
642 "mytable",
643 metadata,
644 Column("y", Integer),
645 extend_existing=True,
646 autoload_with=engine,
647 )
649 .. seealso::
651 :paramref:`_schema.Table.autoload_with`
653 :paramref:`_schema.Table.autoload_replace`
655 :paramref:`_schema.Table.keep_existing`
658 :param implicit_returning: True by default - indicates that
659 RETURNING can be used, typically by the ORM, in order to fetch
660 server-generated values such as primary key values and
661 server side defaults, on those backends which support RETURNING.
663 In modern SQLAlchemy there is generally no reason to alter this
664 setting, except for some backend specific cases
665 (see :ref:`mssql_triggers` in the SQL Server dialect documentation
666 for one such example).
668 :param include_columns: A list of strings indicating a subset of
669 columns to be loaded via the ``autoload`` operation; table columns who
670 aren't present in this list will not be represented on the resulting
671 ``Table`` object. Defaults to ``None`` which indicates all columns
672 should be reflected.
674 :param resolve_fks: Whether or not to reflect :class:`_schema.Table`
675 objects
676 related to this one via :class:`_schema.ForeignKey` objects, when
677 :paramref:`_schema.Table.autoload_with` is
678 specified. Defaults to True. Set to False to disable reflection of
679 related tables as :class:`_schema.ForeignKey`
680 objects are encountered; may be
681 used either to save on SQL calls or to avoid issues with related tables
682 that can't be accessed. Note that if a related table is already present
683 in the :class:`_schema.MetaData` collection, or becomes present later,
684 a
685 :class:`_schema.ForeignKey` object associated with this
686 :class:`_schema.Table` will
687 resolve to that table normally.
689 .. seealso::
691 :paramref:`.MetaData.reflect.resolve_fks`
694 :param info: Optional data dictionary which will be populated into the
695 :attr:`.SchemaItem.info` attribute of this object.
697 :param keep_existing: When ``True``, indicates that if this Table
698 is already present in the given :class:`_schema.MetaData`, ignore
699 further arguments within the constructor to the existing
700 :class:`_schema.Table`, and return the :class:`_schema.Table`
701 object as
702 originally created. This is to allow a function that wishes
703 to define a new :class:`_schema.Table` on first call, but on
704 subsequent calls will return the same :class:`_schema.Table`,
705 without any of the declarations (particularly constraints)
706 being applied a second time.
708 If :paramref:`_schema.Table.extend_existing` or
709 :paramref:`_schema.Table.keep_existing` are not set,
710 and the given name
711 of the new :class:`_schema.Table` refers to a :class:`_schema.Table`
712 that is
713 already present in the target :class:`_schema.MetaData` collection,
714 and
715 this :class:`_schema.Table`
716 specifies additional columns or other constructs
717 or flags that modify the table's state, an
718 error is raised. The purpose of these two mutually-exclusive flags
719 is to specify what action should be taken when a
720 :class:`_schema.Table`
721 is specified that matches an existing :class:`_schema.Table`,
722 yet specifies
723 additional constructs.
725 .. seealso::
727 :paramref:`_schema.Table.extend_existing`
729 :param listeners: A list of tuples of the form ``(<eventname>, <fn>)``
730 which will be passed to :func:`.event.listen` upon construction.
731 This alternate hook to :func:`.event.listen` allows the establishment
732 of a listener function specific to this :class:`_schema.Table` before
733 the "autoload" process begins. Historically this has been intended
734 for use with the :meth:`.DDLEvents.column_reflect` event, however
735 note that this event hook may now be associated with the
736 :class:`_schema.MetaData` object directly::
738 def listen_for_reflect(table, column_info):
739 "handle the column reflection event"
740 # ...
743 t = Table(
744 "sometable",
745 autoload_with=engine,
746 listeners=[("column_reflect", listen_for_reflect)],
747 )
749 .. seealso::
751 :meth:`_events.DDLEvents.column_reflect`
753 :param must_exist: When ``True``, indicates that this Table must already
754 be present in the given :class:`_schema.MetaData` collection, else
755 an exception is raised.
757 :param prefixes:
758 A list of strings to insert after CREATE in the CREATE TABLE
759 statement. They will be separated by spaces.
761 :param quote: Force quoting of this table's name on or off, corresponding
762 to ``True`` or ``False``. When left at its default of ``None``,
763 the column identifier will be quoted according to whether the name is
764 case sensitive (identifiers with at least one upper case character are
765 treated as case sensitive), or if it's a reserved word. This flag
766 is only needed to force quoting of a reserved word which is not known
767 by the SQLAlchemy dialect.
769 .. note:: setting this flag to ``False`` will not provide
770 case-insensitive behavior for table reflection; table reflection
771 will always search for a mixed-case name in a case sensitive
772 fashion. Case insensitive names are specified in SQLAlchemy only
773 by stating the name with all lower case characters.
775 :param quote_schema: same as 'quote' but applies to the schema identifier.
777 :param schema: The schema name for this table, which is required if
778 the table resides in a schema other than the default selected schema
779 for the engine's database connection. Defaults to ``None``.
781 If the owning :class:`_schema.MetaData` of this :class:`_schema.Table`
782 specifies its
783 own :paramref:`_schema.MetaData.schema` parameter,
784 then that schema name will
785 be applied to this :class:`_schema.Table`
786 if the schema parameter here is set
787 to ``None``. To set a blank schema name on a :class:`_schema.Table`
788 that
789 would otherwise use the schema set on the owning
790 :class:`_schema.MetaData`,
791 specify the special symbol :attr:`.BLANK_SCHEMA`.
793 The quoting rules for the schema name are the same as those for the
794 ``name`` parameter, in that quoting is applied for reserved words or
795 case-sensitive names; to enable unconditional quoting for the schema
796 name, specify the flag ``quote_schema=True`` to the constructor, or use
797 the :class:`.quoted_name` construct to specify the name.
799 :param comment: Optional string that will render an SQL comment on table
800 creation.
802 :param \**kw: Additional keyword arguments not mentioned above are
803 dialect specific, and passed in the form ``<dialectname>_<argname>``.
804 See the documentation regarding an individual dialect at
805 :ref:`dialect_toplevel` for detail on documented arguments.
807 """ # noqa: E501
808 if _no_init:
809 # don't run __init__ from __new__ by default;
810 # __new__ has a specific place that __init__ is called
811 return
813 super().__init__(quoted_name(name, quote))
814 self.metadata = metadata
816 if schema is None:
817 self.schema = metadata.schema
818 elif schema is BLANK_SCHEMA:
819 self.schema = None
820 else:
821 quote_schema = quote_schema
822 assert isinstance(schema, str)
823 self.schema = quoted_name(schema, quote_schema)
825 self._sentinel_column = None
827 self.indexes = set()
828 self.constraints = set()
829 PrimaryKeyConstraint(
830 _implicit_generated=True
831 )._set_parent_with_dispatch(self)
832 self.foreign_keys = set() # type: ignore
833 self._extra_dependencies: Set[Table] = set()
834 if self.schema is not None:
835 self.fullname = "%s.%s" % (self.schema, self.name)
836 else:
837 self.fullname = self.name
839 self.implicit_returning = implicit_returning
840 _reflect_info = kw.pop("_reflect_info", None)
842 self.comment = comment
844 if info is not None:
845 self.info = info
847 if listeners is not None:
848 for evt, fn in listeners:
849 event.listen(self, evt, fn)
851 self._prefixes = prefixes if prefixes else []
853 self._extra_kwargs(**kw)
855 # load column definitions from the database if 'autoload' is defined
856 # we do it after the table is in the singleton dictionary to support
857 # circular foreign keys
858 if autoload_with is not None:
859 self._autoload(
860 metadata,
861 autoload_with,
862 include_columns,
863 _extend_on=_extend_on,
864 _reflect_info=_reflect_info,
865 resolve_fks=resolve_fks,
866 )
868 # initialize all the column, etc. objects. done after reflection to
869 # allow user-overrides
871 self._init_items(
872 *args,
873 allow_replacements=extend_existing
874 or keep_existing
875 or autoload_with,
876 all_names={},
877 )
879 def _autoload(
880 self,
881 metadata: MetaData,
882 autoload_with: Union[Engine, Connection],
883 include_columns: Optional[Collection[str]],
884 exclude_columns: Collection[str] = (),
885 resolve_fks: bool = True,
886 _extend_on: Optional[Set[Table]] = None,
887 _reflect_info: _ReflectionInfo | None = None,
888 ) -> None:
889 insp = inspection.inspect(autoload_with)
890 with insp._inspection_context() as conn_insp:
891 conn_insp.reflect_table(
892 self,
893 include_columns,
894 exclude_columns,
895 resolve_fks,
896 _extend_on=_extend_on,
897 _reflect_info=_reflect_info,
898 )
900 @property
901 def _sorted_constraints(self) -> List[Constraint]:
902 """Return the set of constraints as a list, sorted by creation
903 order.
905 """
907 return sorted(self.constraints, key=lambda c: c._creation_order)
909 @property
910 def foreign_key_constraints(self) -> Set[ForeignKeyConstraint]:
911 """:class:`_schema.ForeignKeyConstraint` objects referred to by this
912 :class:`_schema.Table`.
914 This list is produced from the collection of
915 :class:`_schema.ForeignKey`
916 objects currently associated.
919 .. seealso::
921 :attr:`_schema.Table.constraints`
923 :attr:`_schema.Table.foreign_keys`
925 :attr:`_schema.Table.indexes`
927 """
928 return {
929 fkc.constraint
930 for fkc in self.foreign_keys
931 if fkc.constraint is not None
932 }
934 def _init_existing(self, *args: Any, **kwargs: Any) -> None:
935 autoload_with = kwargs.pop("autoload_with", None)
936 autoload = kwargs.pop("autoload", autoload_with is not None)
937 autoload_replace = kwargs.pop("autoload_replace", True)
938 schema = kwargs.pop("schema", None)
939 _extend_on = kwargs.pop("_extend_on", None)
940 _reflect_info = kwargs.pop("_reflect_info", None)
942 # these arguments are only used with _init()
943 extend_existing = kwargs.pop("extend_existing", False)
944 keep_existing = kwargs.pop("keep_existing", False)
946 assert extend_existing
947 assert not keep_existing
949 if schema and schema != self.schema:
950 raise exc.ArgumentError(
951 f"Can't change schema of existing table "
952 f"from '{self.schema}' to '{schema}'",
953 )
955 include_columns = kwargs.pop("include_columns", None)
956 if include_columns is not None:
957 for c in self.c:
958 if c.name not in include_columns:
959 self._columns.remove(c)
961 resolve_fks = kwargs.pop("resolve_fks", True)
963 for key in ("quote", "quote_schema"):
964 if key in kwargs:
965 raise exc.ArgumentError(
966 "Can't redefine 'quote' or 'quote_schema' arguments"
967 )
969 # update `self` with these kwargs, if provided
970 self.comment = kwargs.pop("comment", self.comment)
971 self.implicit_returning = kwargs.pop(
972 "implicit_returning", self.implicit_returning
973 )
974 self.info = kwargs.pop("info", self.info)
976 exclude_columns: _typing_Sequence[str]
978 if autoload:
979 if not autoload_replace:
980 # don't replace columns already present.
981 # we'd like to do this for constraints also however we don't
982 # have simple de-duping for unnamed constraints.
983 exclude_columns = [c.name for c in self.c]
984 else:
985 exclude_columns = ()
986 self._autoload(
987 self.metadata,
988 autoload_with,
989 include_columns,
990 exclude_columns,
991 resolve_fks,
992 _extend_on=_extend_on,
993 _reflect_info=_reflect_info,
994 )
996 all_names = {c.name: c for c in self.c}
997 self._extra_kwargs(**kwargs)
998 self._init_items(*args, allow_replacements=True, all_names=all_names)
1000 def _extra_kwargs(self, **kwargs: Any) -> None:
1001 self._validate_dialect_kwargs(kwargs)
1003 def _init_collections(self) -> None:
1004 pass
1006 def _reset_exported(self) -> None:
1007 pass
1009 @util.ro_non_memoized_property
1010 def _autoincrement_column(self) -> Optional[Column[int]]:
1011 return self.primary_key._autoincrement_column
1013 @util.ro_memoized_property
1014 def _sentinel_column_characteristics(
1015 self,
1016 ) -> _SentinelColumnCharacterization:
1017 """determine a candidate column (or columns, in case of a client
1018 generated composite primary key) which can be used as an
1019 "insert sentinel" for an INSERT statement.
1021 The returned structure, :class:`_SentinelColumnCharacterization`,
1022 includes all the details needed by :class:`.Dialect` and
1023 :class:`.SQLCompiler` to determine if these column(s) can be used
1024 as an INSERT..RETURNING sentinel for a particular database
1025 dialect.
1027 .. versionadded:: 2.0.10
1029 """
1031 sentinel_is_explicit = False
1032 sentinel_is_autoinc = False
1033 the_sentinel: Optional[_typing_Sequence[Column[Any]]] = None
1035 # see if a column was explicitly marked "insert_sentinel=True".
1036 explicit_sentinel_col = self._sentinel_column
1038 if explicit_sentinel_col is not None:
1039 the_sentinel = (explicit_sentinel_col,)
1040 sentinel_is_explicit = True
1042 autoinc_col = self._autoincrement_column
1043 if sentinel_is_explicit and explicit_sentinel_col is autoinc_col:
1044 assert autoinc_col is not None
1045 sentinel_is_autoinc = True
1046 elif explicit_sentinel_col is None and autoinc_col is not None:
1047 the_sentinel = (autoinc_col,)
1048 sentinel_is_autoinc = True
1050 default_characterization = _SentinelDefaultCharacterization.UNKNOWN
1052 if the_sentinel:
1053 the_sentinel_zero = the_sentinel[0]
1054 if the_sentinel_zero.identity:
1055 if the_sentinel_zero.identity._increment_is_negative:
1056 if sentinel_is_explicit:
1057 raise exc.InvalidRequestError(
1058 "Can't use IDENTITY default with negative "
1059 "increment as an explicit sentinel column"
1060 )
1061 else:
1062 if sentinel_is_autoinc:
1063 autoinc_col = None
1064 sentinel_is_autoinc = False
1065 the_sentinel = None
1066 else:
1067 default_characterization = (
1068 _SentinelDefaultCharacterization.IDENTITY
1069 )
1070 elif (
1071 the_sentinel_zero.default is None
1072 and the_sentinel_zero.server_default is None
1073 ):
1074 if the_sentinel_zero.nullable:
1075 raise exc.InvalidRequestError(
1076 f"Column {the_sentinel_zero} has been marked as a "
1077 "sentinel "
1078 "column with no default generation function; it "
1079 "at least needs to be marked nullable=False assuming "
1080 "user-populated sentinel values will be used."
1081 )
1082 default_characterization = (
1083 _SentinelDefaultCharacterization.NONE
1084 )
1085 elif the_sentinel_zero.default is not None:
1086 if the_sentinel_zero.default.is_sentinel:
1087 default_characterization = (
1088 _SentinelDefaultCharacterization.SENTINEL_DEFAULT
1089 )
1090 elif default_is_sequence(the_sentinel_zero.default):
1091 if the_sentinel_zero.default._increment_is_negative:
1092 if sentinel_is_explicit:
1093 raise exc.InvalidRequestError(
1094 "Can't use SEQUENCE default with negative "
1095 "increment as an explicit sentinel column"
1096 )
1097 else:
1098 if sentinel_is_autoinc:
1099 autoinc_col = None
1100 sentinel_is_autoinc = False
1101 the_sentinel = None
1103 default_characterization = (
1104 _SentinelDefaultCharacterization.SEQUENCE
1105 )
1106 elif the_sentinel_zero.default.is_callable:
1107 default_characterization = (
1108 _SentinelDefaultCharacterization.CLIENTSIDE
1109 )
1110 elif the_sentinel_zero.server_default is not None:
1111 if sentinel_is_explicit:
1112 raise exc.InvalidRequestError(
1113 f"Column {the_sentinel[0]} can't be a sentinel column "
1114 "because it uses an explicit server side default "
1115 "that's not the Identity() default."
1116 )
1118 default_characterization = (
1119 _SentinelDefaultCharacterization.SERVERSIDE
1120 )
1122 if the_sentinel is None and self.primary_key:
1123 assert autoinc_col is None
1125 # determine for non-autoincrement pk if all elements are
1126 # client side
1127 for _pkc in self.primary_key:
1128 if _pkc.server_default is not None or (
1129 _pkc.default and not _pkc.default.is_callable
1130 ):
1131 break
1132 else:
1133 the_sentinel = tuple(self.primary_key)
1134 default_characterization = (
1135 _SentinelDefaultCharacterization.CLIENTSIDE
1136 )
1138 return _SentinelColumnCharacterization(
1139 the_sentinel,
1140 sentinel_is_explicit,
1141 sentinel_is_autoinc,
1142 default_characterization,
1143 )
1145 @property
1146 def autoincrement_column(self) -> Optional[Column[int]]:
1147 """Returns the :class:`.Column` object which currently represents
1148 the "auto increment" column, if any, else returns None.
1150 This is based on the rules for :class:`.Column` as defined by the
1151 :paramref:`.Column.autoincrement` parameter, which generally means the
1152 column within a single integer column primary key constraint that is
1153 not constrained by a foreign key. If the table does not have such
1154 a primary key constraint, then there's no "autoincrement" column.
1155 A :class:`.Table` may have only one column defined as the
1156 "autoincrement" column.
1158 .. versionadded:: 2.0.4
1160 .. seealso::
1162 :paramref:`.Column.autoincrement`
1164 """
1165 return self._autoincrement_column
1167 @property
1168 def key(self) -> str:
1169 """Return the 'key' for this :class:`_schema.Table`.
1171 This value is used as the dictionary key within the
1172 :attr:`_schema.MetaData.tables` collection. It is typically the same
1173 as that of :attr:`_schema.Table.name` for a table with no
1174 :attr:`_schema.Table.schema`
1175 set; otherwise it is typically of the form
1176 ``schemaname.tablename``.
1178 """
1179 return _get_table_key(self.name, self.schema)
1181 def __repr__(self) -> str:
1182 return "Table(%s)" % ", ".join(
1183 [repr(self.name)]
1184 + [repr(self.metadata)]
1185 + [repr(x) for x in self.columns]
1186 + ["%s=%s" % (k, repr(getattr(self, k))) for k in ["schema"]]
1187 )
1189 def __str__(self) -> str:
1190 return _get_table_key(self.description, self.schema)
1192 def add_is_dependent_on(self, table: Table) -> None:
1193 """Add a 'dependency' for this Table.
1195 This is another Table object which must be created
1196 first before this one can, or dropped after this one.
1198 Usually, dependencies between tables are determined via
1199 ForeignKey objects. However, for other situations that
1200 create dependencies outside of foreign keys (rules, inheriting),
1201 this method can manually establish such a link.
1203 """
1204 self._extra_dependencies.add(table)
1206 def _insert_col_impl(
1207 self,
1208 column: ColumnClause[Any],
1209 *,
1210 index: Optional[int] = None,
1211 replace_existing: bool = False,
1212 ) -> None:
1213 try:
1214 column._set_parent_with_dispatch(
1215 self,
1216 allow_replacements=replace_existing,
1217 all_names={c.name: c for c in self.c},
1218 index=index,
1219 )
1220 except exc.DuplicateColumnError as de:
1221 raise exc.DuplicateColumnError(
1222 f"{de.args[0]} Specify replace_existing=True to "
1223 "Table.append_column() or Table.insert_column() to replace an "
1224 "existing column."
1225 ) from de
1227 def insert_column(
1228 self,
1229 column: ColumnClause[Any],
1230 index: int,
1231 *,
1232 replace_existing: bool = False,
1233 ) -> None:
1234 """Insert a :class:`_schema.Column` to this :class:`_schema.Table` at
1235 a specific position.
1237 Behavior is identical to :meth:`.Table.append_column` except that
1238 the index position can be controlled using the
1239 :paramref:`.Table.insert_column.index`
1240 parameter.
1242 :param replace_existing:
1243 see :paramref:`.Table.append_column.replace_existing`
1244 :param index: integer index to insert the new column.
1246 .. versionadded:: 2.1
1248 """
1249 self._insert_col_impl(
1250 column, index=index, replace_existing=replace_existing
1251 )
1253 def append_column(
1254 self, column: ColumnClause[Any], *, replace_existing: bool = False
1255 ) -> None:
1256 """Append a :class:`_schema.Column` to this :class:`_schema.Table`.
1258 The "key" of the newly added :class:`_schema.Column`, i.e. the
1259 value of its ``.key`` attribute, will then be available
1260 in the ``.c`` collection of this :class:`_schema.Table`, and the
1261 column definition will be included in any CREATE TABLE, SELECT,
1262 UPDATE, etc. statements generated from this :class:`_schema.Table`
1263 construct.
1265 Note that this does **not** change the definition of the table
1266 as it exists within any underlying database, assuming that
1267 table has already been created in the database. Relational
1268 databases support the addition of columns to existing tables
1269 using the SQL ALTER command, which would need to be
1270 emitted for an already-existing table that doesn't contain
1271 the newly added column.
1273 :param replace_existing: When ``True``, allows replacing existing
1274 columns. When ``False``, the default, an warning will be raised
1275 if a column with the same ``.key`` already exists. A future
1276 version of sqlalchemy will instead rise a warning.
1278 .. versionadded:: 1.4.0
1280 .. seealso::
1282 :meth:`.Table.insert_column`
1284 """
1285 self._insert_col_impl(column, replace_existing=replace_existing)
1287 def append_constraint(self, constraint: Union[Index, Constraint]) -> None:
1288 """Append a :class:`_schema.Constraint` to this
1289 :class:`_schema.Table`.
1291 This has the effect of the constraint being included in any
1292 future CREATE TABLE statement, assuming specific DDL creation
1293 events have not been associated with the given
1294 :class:`_schema.Constraint` object.
1296 Note that this does **not** produce the constraint within the
1297 relational database automatically, for a table that already exists
1298 in the database. To add a constraint to an
1299 existing relational database table, the SQL ALTER command must
1300 be used. SQLAlchemy also provides the
1301 :class:`.AddConstraint` construct which can produce this SQL when
1302 invoked as an executable clause.
1304 """
1306 constraint._set_parent_with_dispatch(self)
1308 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
1309 metadata = parent
1310 assert isinstance(metadata, MetaData)
1311 metadata._add_table(self.name, self.schema, self)
1312 self.metadata = metadata
1314 def create(
1315 self,
1316 bind: _CreateDropBind,
1317 checkfirst: Union[bool, CheckFirst] = CheckFirst.TYPES,
1318 ) -> None:
1319 """Issue a ``CREATE`` statement for this
1320 :class:`_schema.Table`, using the given
1321 :class:`.Connection` or :class:`.Engine`
1322 for connectivity.
1324 .. seealso::
1326 :meth:`_schema.MetaData.create_all`.
1328 """
1330 # the default is to only check for schema objects
1331 bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
1333 def drop(
1334 self,
1335 bind: _CreateDropBind,
1336 checkfirst: Union[bool, CheckFirst] = CheckFirst.NONE,
1337 ) -> None:
1338 """Issue a ``DROP`` statement for this
1339 :class:`_schema.Table`, using the given
1340 :class:`.Connection` or :class:`.Engine` for connectivity.
1342 .. seealso::
1344 :meth:`_schema.MetaData.drop_all`.
1346 """
1347 bind._run_ddl_visitor(ddl.SchemaDropper, self, checkfirst=checkfirst)
1349 @util.deprecated(
1350 "1.4",
1351 ":meth:`_schema.Table.tometadata` is renamed to "
1352 ":meth:`_schema.Table.to_metadata`",
1353 )
1354 def tometadata(
1355 self,
1356 metadata: MetaData,
1357 schema: Union[str, Literal[SchemaConst.RETAIN_SCHEMA]] = RETAIN_SCHEMA,
1358 referred_schema_fn: Optional[
1359 Callable[
1360 [Table, Optional[str], ForeignKeyConstraint, Optional[str]],
1361 Optional[str],
1362 ]
1363 ] = None,
1364 name: Optional[str] = None,
1365 ) -> Table:
1366 """Return a copy of this :class:`_schema.Table`
1367 associated with a different
1368 :class:`_schema.MetaData`.
1370 See :meth:`_schema.Table.to_metadata` for a full description.
1372 """
1373 return self.to_metadata(
1374 metadata,
1375 schema=schema,
1376 referred_schema_fn=referred_schema_fn,
1377 name=name,
1378 )
1380 def to_metadata(
1381 self,
1382 metadata: MetaData,
1383 schema: Union[str, Literal[SchemaConst.RETAIN_SCHEMA]] = RETAIN_SCHEMA,
1384 referred_schema_fn: Optional[
1385 Callable[
1386 [Table, Optional[str], ForeignKeyConstraint, Optional[str]],
1387 Optional[str],
1388 ]
1389 ] = None,
1390 name: Optional[str] = None,
1391 ) -> Table:
1392 """Return a copy of this :class:`_schema.Table` associated with a
1393 different :class:`_schema.MetaData`.
1395 E.g.::
1397 m1 = MetaData()
1399 user = Table("user", m1, Column("id", Integer, primary_key=True))
1401 m2 = MetaData()
1402 user_copy = user.to_metadata(m2)
1404 .. versionchanged:: 1.4 The :meth:`_schema.Table.to_metadata` function
1405 was renamed from :meth:`_schema.Table.tometadata`.
1408 :param metadata: Target :class:`_schema.MetaData` object,
1409 into which the
1410 new :class:`_schema.Table` object will be created.
1412 :param schema: optional string name indicating the target schema.
1413 Defaults to the special symbol :attr:`.RETAIN_SCHEMA` which indicates
1414 that no change to the schema name should be made in the new
1415 :class:`_schema.Table`. If set to a string name, the new
1416 :class:`_schema.Table`
1417 will have this new name as the ``.schema``. If set to ``None``, the
1418 schema will be set to that of the schema set on the target
1419 :class:`_schema.MetaData`, which is typically ``None`` as well,
1420 unless
1421 set explicitly::
1423 m2 = MetaData(schema="newschema")
1425 # user_copy_one will have "newschema" as the schema name
1426 user_copy_one = user.to_metadata(m2, schema=None)
1428 m3 = MetaData() # schema defaults to None
1430 # user_copy_two will have None as the schema name
1431 user_copy_two = user.to_metadata(m3, schema=None)
1433 :param referred_schema_fn: optional callable which can be supplied
1434 in order to provide for the schema name that should be assigned
1435 to the referenced table of a :class:`_schema.ForeignKeyConstraint`.
1436 The callable accepts this parent :class:`_schema.Table`, the
1437 target schema that we are changing to, the
1438 :class:`_schema.ForeignKeyConstraint` object, and the existing
1439 "target schema" of that constraint. The function should return the
1440 string schema name that should be applied. To reset the schema
1441 to "none", return the symbol :data:`.BLANK_SCHEMA`. To effect no
1442 change, return ``None`` or :data:`.RETAIN_SCHEMA`.
1444 .. versionchanged:: 1.4.33 The ``referred_schema_fn`` function
1445 may return the :data:`.BLANK_SCHEMA` or :data:`.RETAIN_SCHEMA`
1446 symbols.
1448 E.g.::
1450 def referred_schema_fn(table, to_schema, constraint, referred_schema):
1451 if referred_schema == "base_tables":
1452 return referred_schema
1453 else:
1454 return to_schema
1457 new_table = table.to_metadata(
1458 m2, schema="alt_schema", referred_schema_fn=referred_schema_fn
1459 )
1461 :param name: optional string name indicating the target table name.
1462 If not specified or None, the table name is retained. This allows
1463 a :class:`_schema.Table` to be copied to the same
1464 :class:`_schema.MetaData` target
1465 with a new name.
1467 """ # noqa: E501
1468 if name is None:
1469 name = self.name
1471 actual_schema: Optional[str]
1473 if schema is RETAIN_SCHEMA:
1474 actual_schema = self.schema
1475 elif schema is None:
1476 actual_schema = metadata.schema
1477 else:
1478 actual_schema = schema
1479 key = _get_table_key(name, actual_schema)
1480 if key in metadata.tables:
1481 util.warn(
1482 f"Table '{self.description}' already exists within the given "
1483 "MetaData - not copying."
1484 )
1485 return metadata.tables[key]
1487 args = []
1488 for col in self.columns:
1489 args.append(col._copy(schema=actual_schema, _to_metadata=metadata))
1490 table = Table(
1491 name,
1492 metadata,
1493 schema=actual_schema,
1494 comment=self.comment,
1495 *args,
1496 **self.kwargs,
1497 )
1498 for const in self.constraints:
1499 if isinstance(const, ForeignKeyConstraint):
1500 referred_schema = const._referred_schema
1501 if referred_schema_fn:
1502 fk_constraint_schema = referred_schema_fn(
1503 self, actual_schema, const, referred_schema
1504 )
1505 else:
1506 fk_constraint_schema = (
1507 actual_schema
1508 if referred_schema == self.schema
1509 else None
1510 )
1511 table.append_constraint(
1512 const._copy(
1513 schema=fk_constraint_schema, target_table=table
1514 )
1515 )
1516 elif not const._type_bound:
1517 # skip unique constraints that would be generated
1518 # by the 'unique' flag on Column
1519 if const._column_flag:
1520 continue
1522 table.append_constraint(
1523 const._copy(schema=actual_schema, target_table=table)
1524 )
1525 for index in self.indexes:
1526 # skip indexes that would be generated
1527 # by the 'index' flag on Column
1528 if index._column_flag:
1529 continue
1530 Index(
1531 index.name,
1532 unique=index.unique,
1533 *[
1534 _copy_expression(expr, self, table)
1535 for expr in index._table_bound_expressions
1536 ],
1537 _table=table,
1538 **index.kwargs,
1539 )
1540 return self._schema_item_copy(table)
1543class Column(DialectKWArgs, SchemaItem, ColumnClause[_T]):
1544 """Represents a column in a database table."""
1546 __visit_name__ = "column"
1548 inherit_cache = True
1549 key: str
1551 server_default: Optional[FetchedValue]
1553 def __init__(
1554 self,
1555 __name_pos: Optional[
1556 Union[str, _TypeEngineArgument[_T], SchemaEventTarget]
1557 ] = None,
1558 __type_pos: Optional[
1559 Union[_TypeEngineArgument[_T], SchemaEventTarget]
1560 ] = None,
1561 /,
1562 *args: SchemaEventTarget,
1563 name: Optional[str] = None,
1564 type_: Optional[_TypeEngineArgument[_T]] = None,
1565 autoincrement: _AutoIncrementType = "auto",
1566 default: Optional[Any] = _NoArg.NO_ARG,
1567 insert_default: Optional[Any] = _NoArg.NO_ARG,
1568 doc: Optional[str] = None,
1569 key: Optional[str] = None,
1570 index: Optional[bool] = None,
1571 unique: Optional[bool] = None,
1572 info: Optional[_InfoType] = None,
1573 nullable: Optional[
1574 Union[bool, Literal[SchemaConst.NULL_UNSPECIFIED]]
1575 ] = SchemaConst.NULL_UNSPECIFIED,
1576 onupdate: Optional[Any] = None,
1577 primary_key: bool = False,
1578 server_default: Optional[_ServerDefaultArgument] = None,
1579 server_onupdate: Optional[_ServerOnUpdateArgument] = None,
1580 quote: Optional[bool] = None,
1581 system: bool = False,
1582 comment: Optional[str] = None,
1583 insert_sentinel: bool = False,
1584 _omit_from_statements: bool = False,
1585 _proxies: Optional[Any] = None,
1586 **dialect_kwargs: Any,
1587 ):
1588 r"""
1589 Construct a new ``Column`` object.
1591 :param name: The name of this column as represented in the database.
1592 This argument may be the first positional argument, or specified
1593 via keyword.
1595 Names which contain no upper case characters
1596 will be treated as case insensitive names, and will not be quoted
1597 unless they are a reserved word. Names with any number of upper
1598 case characters will be quoted and sent exactly. Note that this
1599 behavior applies even for databases which standardize upper
1600 case names as case insensitive such as Oracle Database.
1602 The name field may be omitted at construction time and applied
1603 later, at any time before the Column is associated with a
1604 :class:`_schema.Table`. This is to support convenient
1605 usage within the :mod:`~sqlalchemy.ext.declarative` extension.
1607 :param type\_: The column's type, indicated using an instance which
1608 subclasses :class:`~sqlalchemy.types.TypeEngine`. If no arguments
1609 are required for the type, the class of the type can be sent
1610 as well, e.g.::
1612 # use a type with arguments
1613 Column("data", String(50))
1615 # use no arguments
1616 Column("level", Integer)
1618 The ``type`` argument may be the second positional argument
1619 or specified by keyword.
1621 If the ``type`` is ``None`` or is omitted, it will first default to
1622 the special type :class:`.NullType`. If and when this
1623 :class:`_schema.Column` is made to refer to another column using
1624 :class:`_schema.ForeignKey` and/or
1625 :class:`_schema.ForeignKeyConstraint`, the type
1626 of the remote-referenced column will be copied to this column as
1627 well, at the moment that the foreign key is resolved against that
1628 remote :class:`_schema.Column` object.
1630 :param \*args: Additional positional arguments include various
1631 :class:`.SchemaItem` derived constructs which will be applied
1632 as options to the column. These include instances of
1633 :class:`.Constraint`, :class:`_schema.ForeignKey`,
1634 :class:`.ColumnDefault`, :class:`.Sequence`, :class:`.Computed`
1635 :class:`.Identity`. In some cases an
1636 equivalent keyword argument is available such as ``server_default``,
1637 ``default`` and ``unique``.
1639 :param autoincrement: Set up "auto increment" semantics for an
1640 **integer primary key column with no foreign key dependencies**
1641 (see later in this docstring for a more specific definition).
1642 This may influence the :term:`DDL` that will be emitted for
1643 this column during a table create, as well as how the column
1644 will be considered when INSERT statements are compiled and
1645 executed.
1647 The default value is the string ``"auto"``,
1648 which indicates that a single-column (i.e. non-composite) primary key
1649 that is of an INTEGER type with no other client-side or server-side
1650 default constructs indicated should receive auto increment semantics
1651 automatically. Other values include ``True`` (force this column to
1652 have auto-increment semantics for a :term:`composite primary key` as
1653 well), ``False`` (this column should never have auto-increment
1654 semantics), and the string ``"ignore_fk"`` (special-case for foreign
1655 key columns, see below).
1657 The term "auto increment semantics" refers both to the kind of DDL
1658 that will be emitted for the column within a CREATE TABLE statement,
1659 when methods such as :meth:`.MetaData.create_all` and
1660 :meth:`.Table.create` are invoked, as well as how the column will be
1661 considered when an INSERT statement is compiled and emitted to the
1662 database:
1664 * **DDL rendering** (i.e. :meth:`.MetaData.create_all`,
1665 :meth:`.Table.create`): When used on a :class:`.Column` that has
1666 no other
1667 default-generating construct associated with it (such as a
1668 :class:`.Sequence` or :class:`.Identity` construct), the parameter
1669 will imply that database-specific keywords such as PostgreSQL
1670 ``SERIAL``, MySQL ``AUTO_INCREMENT``, or ``IDENTITY`` on SQL Server
1671 should also be rendered. Not every database backend has an
1672 "implied" default generator available; for example the Oracle Database
1673 backends alway needs an explicit construct such as
1674 :class:`.Identity` to be included with a :class:`.Column` in order
1675 for the DDL rendered to include auto-generating constructs to also
1676 be produced in the database.
1678 * **INSERT semantics** (i.e. when a :func:`_sql.insert` construct is
1679 compiled into a SQL string and is then executed on a database using
1680 :meth:`_engine.Connection.execute` or equivalent): A single-row
1681 INSERT statement will be known to produce a new integer primary key
1682 value automatically for this column, which will be accessible
1683 after the statement is invoked via the
1684 :attr:`.CursorResult.inserted_primary_key` attribute upon the
1685 :class:`_result.Result` object. This also applies towards use of the
1686 ORM when ORM-mapped objects are persisted to the database,
1687 indicating that a new integer primary key will be available to
1688 become part of the :term:`identity key` for that object. This
1689 behavior takes place regardless of what DDL constructs are
1690 associated with the :class:`_schema.Column` and is independent
1691 of the "DDL Rendering" behavior discussed in the previous note
1692 above.
1694 The parameter may be set to ``True`` to indicate that a column which
1695 is part of a composite (i.e. multi-column) primary key should
1696 have autoincrement semantics, though note that only one column
1697 within a primary key may have this setting. It can also
1698 be set to ``True`` to indicate autoincrement semantics on a
1699 column that has a client-side or server-side default configured,
1700 however note that not all dialects can accommodate all styles
1701 of default as an "autoincrement". It can also be
1702 set to ``False`` on a single-column primary key that has a
1703 datatype of INTEGER in order to disable auto increment semantics
1704 for that column.
1706 The setting *only* has an effect for columns which are:
1708 * Integer derived (i.e. INT, SMALLINT, BIGINT).
1710 * Part of the primary key
1712 * Not referring to another column via :class:`_schema.ForeignKey`,
1713 unless
1714 the value is specified as ``'ignore_fk'``::
1716 # turn on autoincrement for this column despite
1717 # the ForeignKey()
1718 Column(
1719 "id",
1720 ForeignKey("other.id"),
1721 primary_key=True,
1722 autoincrement="ignore_fk",
1723 )
1725 It is typically not desirable to have "autoincrement" enabled on a
1726 column that refers to another via foreign key, as such a column is
1727 required to refer to a value that originates from elsewhere.
1729 The setting has these effects on columns that meet the
1730 above criteria:
1732 * DDL issued for the column, if the column does not already include
1733 a default generating construct supported by the backend such as
1734 :class:`.Identity`, will include database-specific
1735 keywords intended to signify this column as an
1736 "autoincrement" column for specific backends. Behavior for
1737 primary SQLAlchemy dialects includes:
1739 * AUTO INCREMENT on MySQL and MariaDB
1740 * SERIAL on PostgreSQL
1741 * IDENTITY on MS-SQL - this occurs even without the
1742 :class:`.Identity` construct as the
1743 :paramref:`.Column.autoincrement` parameter pre-dates this
1744 construct.
1745 * SQLite - SQLite integer primary key columns are implicitly
1746 "auto incrementing" and no additional keywords are rendered;
1747 to render the special SQLite keyword ``AUTOINCREMENT``
1748 is not included as this is unnecessary and not recommended
1749 by the database vendor. See the section
1750 :ref:`sqlite_autoincrement` for more background.
1751 * Oracle Database - The Oracle Database dialects have no default "autoincrement"
1752 feature available at this time, instead the :class:`.Identity`
1753 construct is recommended to achieve this (the :class:`.Sequence`
1754 construct may also be used).
1755 * Third-party dialects - consult those dialects' documentation
1756 for details on their specific behaviors.
1758 * When a single-row :func:`_sql.insert` construct is compiled and
1759 executed, which does not set the :meth:`_sql.Insert.inline`
1760 modifier, newly generated primary key values for this column
1761 will be automatically retrieved upon statement execution
1762 using a method specific to the database driver in use:
1764 * MySQL, SQLite - calling upon ``cursor.lastrowid()``
1765 (see
1766 `https://www.python.org/dev/peps/pep-0249/#lastrowid
1767 <https://www.python.org/dev/peps/pep-0249/#lastrowid>`_)
1768 * PostgreSQL, SQL Server, Oracle Database - use RETURNING or an equivalent
1769 construct when rendering an INSERT statement, and then retrieving
1770 the newly generated primary key values after execution
1771 * PostgreSQL, Oracle Database for :class:`_schema.Table` objects that
1772 set :paramref:`_schema.Table.implicit_returning` to False -
1773 for a :class:`.Sequence` only, the :class:`.Sequence` is invoked
1774 explicitly before the INSERT statement takes place so that the
1775 newly generated primary key value is available to the client
1776 * SQL Server for :class:`_schema.Table` objects that
1777 set :paramref:`_schema.Table.implicit_returning` to False -
1778 the ``SELECT scope_identity()`` construct is used after the
1779 INSERT statement is invoked to retrieve the newly generated
1780 primary key value.
1781 * Third-party dialects - consult those dialects' documentation
1782 for details on their specific behaviors.
1784 * For multiple-row :func:`_sql.insert` constructs invoked with
1785 a list of parameters (i.e. "executemany" semantics), primary-key
1786 retrieving behaviors are generally disabled, however there may
1787 be special APIs that may be used to retrieve lists of new
1788 primary key values for an "executemany", such as the psycopg2
1789 "fast insertmany" feature. Such features are very new and
1790 may not yet be well covered in documentation.
1792 :param default: A scalar, Python callable, or
1793 :class:`_expression.ColumnElement` expression representing the
1794 *default value* for this column, which will be invoked upon insert
1795 if this column is otherwise not specified in the VALUES clause of
1796 the insert. This is a shortcut to using :class:`.ColumnDefault` as
1797 a positional argument; see that class for full detail on the
1798 structure of the argument.
1800 Contrast this argument to
1801 :paramref:`_schema.Column.server_default`
1802 which creates a default generator on the database side.
1804 .. seealso::
1806 :ref:`metadata_defaults_toplevel`
1808 :param insert_default: An alias of :paramref:`.Column.default`
1809 for compatibility with :func:`_orm.mapped_column`.
1811 .. versionadded:: 2.0.31
1813 :param doc: optional String that can be used by the ORM or similar
1814 to document attributes on the Python side. This attribute does
1815 **not** render SQL comments; use the
1816 :paramref:`_schema.Column.comment`
1817 parameter for this purpose.
1819 :param key: An optional string identifier which will identify this
1820 ``Column`` object on the :class:`_schema.Table`.
1821 When a key is provided,
1822 this is the only identifier referencing the ``Column`` within the
1823 application, including ORM attribute mapping; the ``name`` field
1824 is used only when rendering SQL.
1826 :param index: When ``True``, indicates that a :class:`_schema.Index`
1827 construct will be automatically generated for this
1828 :class:`_schema.Column`, which will result in a "CREATE INDEX"
1829 statement being emitted for the :class:`_schema.Table` when the DDL
1830 create operation is invoked.
1832 Using this flag is equivalent to making use of the
1833 :class:`_schema.Index` construct explicitly at the level of the
1834 :class:`_schema.Table` construct itself::
1836 Table(
1837 "some_table",
1838 metadata,
1839 Column("x", Integer),
1840 Index("ix_some_table_x", "x"),
1841 )
1843 To add the :paramref:`_schema.Index.unique` flag to the
1844 :class:`_schema.Index`, set both the
1845 :paramref:`_schema.Column.unique` and
1846 :paramref:`_schema.Column.index` flags to True simultaneously,
1847 which will have the effect of rendering the "CREATE UNIQUE INDEX"
1848 DDL instruction instead of "CREATE INDEX".
1850 The name of the index is generated using the
1851 :ref:`default naming convention <constraint_default_naming_convention>`
1852 which for the :class:`_schema.Index` construct is of the form
1853 ``ix_<tablename>_<columnname>``.
1855 As this flag is intended only as a convenience for the common case
1856 of adding a single-column, default configured index to a table
1857 definition, explicit use of the :class:`_schema.Index` construct
1858 should be preferred for most use cases, including composite indexes
1859 that encompass more than one column, indexes with SQL expressions
1860 or ordering, backend-specific index configuration options, and
1861 indexes that use a specific name.
1863 .. note:: the :attr:`_schema.Column.index` attribute on
1864 :class:`_schema.Column`
1865 **does not indicate** if this column is indexed or not, only
1866 if this flag was explicitly set here. To view indexes on
1867 a column, view the :attr:`_schema.Table.indexes` collection
1868 or use :meth:`_reflection.Inspector.get_indexes`.
1870 .. seealso::
1872 :ref:`schema_indexes`
1874 :ref:`constraint_naming_conventions`
1876 :paramref:`_schema.Column.unique`
1878 :param info: Optional data dictionary which will be populated into the
1879 :attr:`.SchemaItem.info` attribute of this object.
1881 :param nullable: When set to ``False``, will cause the "NOT NULL"
1882 phrase to be added when generating DDL for the column. When
1883 ``True``, will normally generate nothing (in SQL this defaults to
1884 "NULL"), except in some very specific backend-specific edge cases
1885 where "NULL" may render explicitly.
1886 Defaults to ``True`` unless :paramref:`_schema.Column.primary_key`
1887 is also ``True`` or the column specifies a :class:`_sql.Identity`,
1888 in which case it defaults to ``False``.
1889 This parameter is only used when issuing CREATE TABLE statements.
1891 .. note::
1893 When the column specifies a :class:`_sql.Identity` this
1894 parameter is in general ignored by the DDL compiler. The
1895 PostgreSQL database allows nullable identity column by
1896 setting this parameter to ``True`` explicitly.
1898 :param onupdate: A scalar, Python callable, or
1899 :class:`~sqlalchemy.sql.expression.ClauseElement` representing a
1900 default value to be applied to the column within UPDATE
1901 statements, which will be invoked upon update if this column is not
1902 present in the SET clause of the update. This is a shortcut to
1903 using :class:`.ColumnDefault` as a positional argument with
1904 ``for_update=True``.
1906 .. seealso::
1908 :ref:`metadata_defaults` - complete discussion of onupdate
1910 :param primary_key: If ``True``, marks this column as a primary key
1911 column. Multiple columns can have this flag set to specify
1912 composite primary keys. As an alternative, the primary key of a
1913 :class:`_schema.Table` can be specified via an explicit
1914 :class:`.PrimaryKeyConstraint` object.
1916 :param server_default: A :class:`.FetchedValue` instance, str, Unicode
1917 or :func:`~sqlalchemy.sql.expression.text` construct representing
1918 the DDL DEFAULT value for the column.
1920 String types will be emitted as-is, surrounded by single quotes::
1922 Column("x", Text, server_default="val")
1924 will render:
1926 .. sourcecode:: sql
1928 x TEXT DEFAULT 'val'
1930 A :func:`~sqlalchemy.sql.expression.text` expression will be
1931 rendered as-is, without quotes::
1933 Column("y", DateTime, server_default=text("NOW()"))
1935 will render:
1937 .. sourcecode:: sql
1939 y DATETIME DEFAULT NOW()
1941 Strings and text() will be converted into a
1942 :class:`.DefaultClause` object upon initialization.
1944 This parameter can also accept complex combinations of contextually
1945 valid SQLAlchemy expressions or constructs::
1947 from sqlalchemy import create_engine
1948 from sqlalchemy import Table, Column, MetaData, ARRAY, Text
1949 from sqlalchemy.dialects.postgresql import array
1951 engine = create_engine(
1952 "postgresql+psycopg2://scott:tiger@localhost/mydatabase"
1953 )
1954 metadata_obj = MetaData()
1955 tbl = Table(
1956 "foo",
1957 metadata_obj,
1958 Column(
1959 "bar", ARRAY(Text), server_default=array(["biz", "bang", "bash"])
1960 ),
1961 )
1962 metadata_obj.create_all(engine)
1964 The above results in a table created with the following SQL:
1966 .. sourcecode:: sql
1968 CREATE TABLE foo (
1969 bar TEXT[] DEFAULT ARRAY['biz', 'bang', 'bash']
1970 )
1972 Use :class:`.FetchedValue` to indicate that an already-existing
1973 column will generate a default value on the database side which
1974 will be available to SQLAlchemy for post-fetch after inserts. This
1975 construct does not specify any DDL and the implementation is left
1976 to the database, such as via a trigger.
1978 .. seealso::
1980 :ref:`server_defaults` - complete discussion of server side
1981 defaults
1983 :param server_onupdate: A :class:`.FetchedValue` instance
1984 representing a database-side default generation function,
1985 such as a trigger. This
1986 indicates to SQLAlchemy that a newly generated value will be
1987 available after updates. This construct does not actually
1988 implement any kind of generation function within the database,
1989 which instead must be specified separately.
1992 .. warning:: This directive **does not** currently produce MySQL's
1993 "ON UPDATE CURRENT_TIMESTAMP()" clause. See
1994 :ref:`mysql_timestamp_onupdate` for background on how to
1995 produce this clause.
1997 .. seealso::
1999 :ref:`triggered_columns`
2001 :param quote: Force quoting of this column's name on or off,
2002 corresponding to ``True`` or ``False``. When left at its default
2003 of ``None``, the column identifier will be quoted according to
2004 whether the name is case sensitive (identifiers with at least one
2005 upper case character are treated as case sensitive), or if it's a
2006 reserved word. This flag is only needed to force quoting of a
2007 reserved word which is not known by the SQLAlchemy dialect.
2009 :param unique: When ``True``, and the :paramref:`_schema.Column.index`
2010 parameter is left at its default value of ``False``,
2011 indicates that a :class:`_schema.UniqueConstraint`
2012 construct will be automatically generated for this
2013 :class:`_schema.Column`,
2014 which will result in a "UNIQUE CONSTRAINT" clause referring
2015 to this column being included
2016 in the ``CREATE TABLE`` statement emitted, when the DDL create
2017 operation for the :class:`_schema.Table` object is invoked.
2019 When this flag is ``True`` while the
2020 :paramref:`_schema.Column.index` parameter is simultaneously
2021 set to ``True``, the effect instead is that a
2022 :class:`_schema.Index` construct which includes the
2023 :paramref:`_schema.Index.unique` parameter set to ``True``
2024 is generated. See the documentation for
2025 :paramref:`_schema.Column.index` for additional detail.
2027 Using this flag is equivalent to making use of the
2028 :class:`_schema.UniqueConstraint` construct explicitly at the
2029 level of the :class:`_schema.Table` construct itself::
2031 Table("some_table", metadata, Column("x", Integer), UniqueConstraint("x"))
2033 The :paramref:`_schema.UniqueConstraint.name` parameter
2034 of the unique constraint object is left at its default value
2035 of ``None``; in the absence of a :ref:`naming convention <constraint_naming_conventions>`
2036 for the enclosing :class:`_schema.MetaData`, the UNIQUE CONSTRAINT
2037 construct will be emitted as unnamed, which typically invokes
2038 a database-specific naming convention to take place.
2040 As this flag is intended only as a convenience for the common case
2041 of adding a single-column, default configured unique constraint to a table
2042 definition, explicit use of the :class:`_schema.UniqueConstraint` construct
2043 should be preferred for most use cases, including composite constraints
2044 that encompass more than one column, backend-specific index configuration options, and
2045 constraints that use a specific name.
2047 .. note:: the :attr:`_schema.Column.unique` attribute on
2048 :class:`_schema.Column`
2049 **does not indicate** if this column has a unique constraint or
2050 not, only if this flag was explicitly set here. To view
2051 indexes and unique constraints that may involve this column,
2052 view the
2053 :attr:`_schema.Table.indexes` and/or
2054 :attr:`_schema.Table.constraints` collections or use
2055 :meth:`_reflection.Inspector.get_indexes` and/or
2056 :meth:`_reflection.Inspector.get_unique_constraints`
2058 .. seealso::
2060 :ref:`schema_unique_constraint`
2062 :ref:`constraint_naming_conventions`
2064 :paramref:`_schema.Column.index`
2066 :param system: When ``True``, indicates this is a "system" column,
2067 that is a column which is automatically made available by the
2068 database, and should not be included in the columns list for a
2069 ``CREATE TABLE`` statement.
2071 For more elaborate scenarios where columns should be
2072 conditionally rendered differently on different backends,
2073 consider custom compilation rules for :class:`.CreateColumn`.
2075 :param comment: Optional string that will render an SQL comment on
2076 table creation.
2078 :param insert_sentinel: Marks this :class:`_schema.Column` as an
2079 :term:`insert sentinel` used for optimizing the performance of the
2080 :term:`insertmanyvalues` feature for tables that don't
2081 otherwise have qualifying primary key configurations.
2083 .. versionadded:: 2.0.10
2085 .. seealso::
2087 :func:`_schema.insert_sentinel` - all in one helper for declaring
2088 sentinel columns
2090 :ref:`engine_insertmanyvalues`
2092 :ref:`engine_insertmanyvalues_sentinel_columns`
2095 """ # noqa: E501, RST201, RST202
2097 l_args = [__name_pos, __type_pos] + list(args)
2098 del args
2100 if l_args:
2101 if isinstance(l_args[0], str):
2102 if name is not None:
2103 raise exc.ArgumentError(
2104 "May not pass name positionally and as a keyword."
2105 )
2106 name = l_args.pop(0) # type: ignore
2107 elif l_args[0] is None:
2108 l_args.pop(0)
2109 if l_args:
2110 coltype = l_args[0]
2112 if hasattr(coltype, "_sqla_type"):
2113 if type_ is not None:
2114 raise exc.ArgumentError(
2115 "May not pass type_ positionally and as a keyword."
2116 )
2117 type_ = l_args.pop(0) # type: ignore
2118 elif l_args[0] is None:
2119 l_args.pop(0)
2121 if name is not None:
2122 name = quoted_name(name, quote)
2123 elif quote is not None:
2124 raise exc.ArgumentError(
2125 "Explicit 'name' is required when sending 'quote' argument"
2126 )
2128 # name = None is expected to be an interim state
2129 # note this use case is legacy now that ORM declarative has a
2130 # dedicated "column" construct local to the ORM
2131 super().__init__(name, type_) # type: ignore
2133 self.key = key if key is not None else name # type: ignore
2134 self.primary_key = primary_key
2135 self._insert_sentinel = insert_sentinel
2136 self._omit_from_statements = _omit_from_statements
2137 self._user_defined_nullable = udn = nullable
2138 if udn is not NULL_UNSPECIFIED:
2139 self.nullable = udn
2140 else:
2141 self.nullable = not primary_key
2143 # these default to None because .index and .unique is *not*
2144 # an informational flag about Column - there can still be an
2145 # Index or UniqueConstraint referring to this Column.
2146 self.index = index
2147 self.unique = unique
2149 self.system = system
2150 self.doc = doc
2151 self.autoincrement: _AutoIncrementType = autoincrement
2152 self.constraints = set()
2153 self.foreign_keys = set()
2154 self.comment = comment
2155 self.computed = None
2156 self.identity = None
2158 # check if this Column is proxying another column
2160 if _proxies is not None:
2161 self._proxies = _proxies
2162 else:
2163 # otherwise, add DDL-related events
2164 self._set_type(self.type)
2166 if insert_default is not _NoArg.NO_ARG:
2167 if default is not _NoArg.NO_ARG:
2168 raise exc.ArgumentError(
2169 "The 'default' and 'insert_default' parameters "
2170 "of Column are mutually exclusive"
2171 )
2172 resolved_default = insert_default
2173 elif default is not _NoArg.NO_ARG:
2174 resolved_default = default
2175 else:
2176 resolved_default = None
2178 if resolved_default is not None:
2179 if not isinstance(resolved_default, (ColumnDefault, Sequence)):
2180 resolved_default = ColumnDefault(resolved_default)
2182 self.default = resolved_default
2183 l_args.append(resolved_default)
2184 else:
2185 self.default = None
2187 if onupdate is not None:
2188 if not isinstance(onupdate, (ColumnDefault, Sequence)):
2189 onupdate = ColumnDefault(onupdate, for_update=True)
2191 self.onupdate = onupdate
2192 l_args.append(onupdate)
2193 else:
2194 self.onupdate = None
2196 if server_default is not None:
2197 if isinstance(server_default, FetchedValue):
2198 server_default = server_default._as_for_update(False)
2199 l_args.append(server_default)
2200 else:
2201 server_default = DefaultClause(server_default)
2202 l_args.append(server_default)
2203 self.server_default = server_default
2205 if server_onupdate is not None:
2206 if isinstance(server_onupdate, FetchedValue):
2207 server_onupdate = server_onupdate._as_for_update(True)
2208 l_args.append(server_onupdate)
2209 else:
2210 server_onupdate = DefaultClause(
2211 server_onupdate, for_update=True
2212 )
2213 l_args.append(server_onupdate)
2214 self.server_onupdate = server_onupdate
2216 self._init_items(*cast(_typing_Sequence[SchemaItem], l_args))
2218 util.set_creation_order(self)
2220 if info is not None:
2221 self.info = info
2223 self._extra_kwargs(**dialect_kwargs)
2225 table: Table
2227 constraints: Set[Constraint]
2229 foreign_keys: Set[ForeignKey]
2230 """A collection of all :class:`_schema.ForeignKey` marker objects
2231 associated with this :class:`_schema.Column`.
2233 Each object is a member of a :class:`_schema.Table`-wide
2234 :class:`_schema.ForeignKeyConstraint`.
2236 .. seealso::
2238 :attr:`_schema.Table.foreign_keys`
2240 """
2242 index: Optional[bool]
2243 """The value of the :paramref:`_schema.Column.index` parameter.
2245 Does not indicate if this :class:`_schema.Column` is actually indexed
2246 or not; use :attr:`_schema.Table.indexes`.
2248 .. seealso::
2250 :attr:`_schema.Table.indexes`
2251 """
2253 unique: Optional[bool]
2254 """The value of the :paramref:`_schema.Column.unique` parameter.
2256 Does not indicate if this :class:`_schema.Column` is actually subject to
2257 a unique constraint or not; use :attr:`_schema.Table.indexes` and
2258 :attr:`_schema.Table.constraints`.
2260 .. seealso::
2262 :attr:`_schema.Table.indexes`
2264 :attr:`_schema.Table.constraints`.
2266 """
2268 computed: Optional[Computed]
2270 identity: Optional[Identity]
2272 def _set_type(self, type_: TypeEngine[Any]) -> None:
2273 assert self.type._isnull or type_ is self.type
2275 self.type = type_
2276 if isinstance(self.type, SchemaEventTarget):
2277 self.type._set_parent_with_dispatch(self)
2278 for impl in self.type._variant_mapping.values():
2279 if isinstance(impl, SchemaEventTarget):
2280 impl._set_parent_with_dispatch(self)
2282 @HasMemoized.memoized_attribute
2283 def _default_description_tuple(self) -> _DefaultDescriptionTuple:
2284 """used by default.py -> _process_execute_defaults()"""
2286 return _DefaultDescriptionTuple._from_column_default(self.default)
2288 @HasMemoized.memoized_attribute
2289 def _onupdate_description_tuple(self) -> _DefaultDescriptionTuple:
2290 """used by default.py -> _process_execute_defaults()"""
2291 return _DefaultDescriptionTuple._from_column_default(self.onupdate)
2293 @util.memoized_property
2294 def _gen_static_annotations_cache_key(self) -> bool:
2295 """special attribute used by cache key gen, if true, we will
2296 use a static cache key for the annotations dictionary, else we
2297 will generate a new cache key for annotations each time.
2299 Added for #8790
2301 """
2302 return self.table is not None and self.table._is_table
2304 def _extra_kwargs(self, **kwargs: Any) -> None:
2305 self._validate_dialect_kwargs(kwargs)
2307 def __str__(self) -> str:
2308 if self.name is None:
2309 return "(no name)"
2310 elif self.table is not None:
2311 if self.table.named_with_column:
2312 return self.table.description + "." + self.description
2313 else:
2314 return self.description
2315 else:
2316 return self.description
2318 def references(self, column: Column[Any]) -> bool:
2319 """Return True if this Column references the given column via foreign
2320 key."""
2322 for fk in self.foreign_keys:
2323 if fk.column.proxy_set.intersection(column.proxy_set):
2324 return True
2325 else:
2326 return False
2328 def append_foreign_key(self, fk: ForeignKey) -> None:
2329 fk._set_parent_with_dispatch(self)
2331 def __repr__(self) -> str:
2332 kwarg = []
2333 if self.key != self.name:
2334 kwarg.append("key")
2335 if self.primary_key:
2336 kwarg.append("primary_key")
2337 if not self.nullable:
2338 kwarg.append("nullable")
2339 if self.onupdate:
2340 kwarg.append("onupdate")
2341 if self.default:
2342 kwarg.append("default")
2343 if self.server_default:
2344 kwarg.append("server_default")
2345 if self.comment:
2346 kwarg.append("comment")
2347 return "Column(%s)" % ", ".join(
2348 [repr(self.name)]
2349 + [repr(self.type)]
2350 + [repr(x) for x in self.foreign_keys if x is not None]
2351 + [repr(x) for x in self.constraints]
2352 + [
2353 (
2354 self.table is not None
2355 and "table=<%s>" % self.table.description
2356 or "table=None"
2357 )
2358 ]
2359 + ["%s=%s" % (k, repr(getattr(self, k))) for k in kwarg]
2360 )
2362 def _set_parent( # type: ignore[override]
2363 self,
2364 parent: SchemaEventTarget,
2365 *,
2366 all_names: Dict[str, Column[Any]],
2367 allow_replacements: bool,
2368 index: Optional[int] = None,
2369 **kw: Any,
2370 ) -> None:
2371 table = parent
2372 assert isinstance(table, Table)
2373 if not self.name:
2374 raise exc.ArgumentError(
2375 "Column must be constructed with a non-blank name or "
2376 "assign a non-blank .name before adding to a Table."
2377 )
2379 self._reset_memoizations()
2381 if self.key is None:
2382 self.key = self.name
2384 existing = getattr(self, "table", None)
2385 if existing is not None and existing is not table:
2386 raise exc.ArgumentError(
2387 f"Column object '{self.key}' already "
2388 f"assigned to Table '{existing.description}'"
2389 )
2391 extra_remove = None
2392 existing_col = None
2393 conflicts_on = ""
2395 if self.key in table._columns:
2396 existing_col = table._columns[self.key]
2397 if self.key == self.name:
2398 conflicts_on = "name"
2399 else:
2400 conflicts_on = "key"
2401 elif self.name in all_names:
2402 existing_col = all_names[self.name]
2403 extra_remove = {existing_col}
2404 conflicts_on = "name"
2406 if existing_col is not None:
2407 if existing_col is not self:
2408 if not allow_replacements:
2409 raise exc.DuplicateColumnError(
2410 f"A column with {conflicts_on} "
2411 f"""'{
2412 self.key if conflicts_on == 'key' else self.name
2413 }' """
2414 f"is already present in table '{table.name}'."
2415 )
2416 for fk in existing_col.foreign_keys:
2417 table.foreign_keys.remove(fk)
2418 if fk.constraint in table.constraints:
2419 # this might have been removed
2420 # already, if it's a composite constraint
2421 # and more than one col being replaced
2422 table.constraints.remove(fk.constraint)
2424 if extra_remove and existing_col is not None and self.key == self.name:
2425 util.warn(
2426 f'Column with user-specified key "{existing_col.key}" is '
2427 "being replaced with "
2428 f'plain named column "{self.name}", '
2429 f'key "{existing_col.key}" is being removed. If this is a '
2430 "reflection operation, specify autoload_replace=False to "
2431 "prevent this replacement."
2432 )
2433 table._columns.replace(self, extra_remove=extra_remove, index=index)
2434 all_names[self.name] = self
2435 self.table = table
2437 if self._insert_sentinel:
2438 if self.table._sentinel_column is not None:
2439 raise exc.ArgumentError(
2440 "a Table may have only one explicit sentinel column"
2441 )
2442 self.table._sentinel_column = self
2444 if self.primary_key:
2445 table.primary_key._replace(self)
2446 elif self.key in table.primary_key:
2447 raise exc.ArgumentError(
2448 f"Trying to redefine primary-key column '{self.key}' as a "
2449 f"non-primary-key column on table '{table.fullname}'"
2450 )
2452 if self.index:
2453 if isinstance(self.index, str):
2454 raise exc.ArgumentError(
2455 "The 'index' keyword argument on Column is boolean only. "
2456 "To create indexes with a specific name, create an "
2457 "explicit Index object external to the Table."
2458 )
2459 table.append_constraint(
2460 Index(
2461 None, self.key, unique=bool(self.unique), _column_flag=True
2462 )
2463 )
2465 elif self.unique:
2466 if isinstance(self.unique, str):
2467 raise exc.ArgumentError(
2468 "The 'unique' keyword argument on Column is boolean "
2469 "only. To create unique constraints or indexes with a "
2470 "specific name, append an explicit UniqueConstraint to "
2471 "the Table's list of elements, or create an explicit "
2472 "Index object external to the Table."
2473 )
2474 table.append_constraint(
2475 UniqueConstraint(self.key, _column_flag=True)
2476 )
2478 self._setup_on_memoized_fks(lambda fk: fk._set_remote_table(table))
2480 if self.identity and (
2481 isinstance(self.default, Sequence)
2482 or isinstance(self.onupdate, Sequence)
2483 ):
2484 raise exc.ArgumentError(
2485 "An column cannot specify both Identity and Sequence."
2486 )
2488 def _setup_on_memoized_fks(self, fn: Callable[..., Any]) -> None:
2489 fk_keys = [
2490 ((self.table.key, self.key), False),
2491 ((self.table.key, self.name), True),
2492 ]
2493 for fk_key, link_to_name in fk_keys:
2494 if fk_key in self.table.metadata._fk_memos:
2495 for fk in self.table.metadata._fk_memos[fk_key]:
2496 if fk.link_to_name is link_to_name:
2497 fn(fk)
2499 def _on_table_attach(self, fn: Callable[..., Any]) -> None:
2500 if self.table is not None:
2501 fn(self, self.table)
2502 else:
2503 event.listen(self, "after_parent_attach", fn)
2505 @util.deprecated(
2506 "1.4",
2507 "The :meth:`_schema.Column.copy` method is deprecated "
2508 "and will be removed in a future release.",
2509 )
2510 def copy(self, **kw: Any) -> Column[Any]:
2511 return self._copy(**kw)
2513 def _copy(self, **kw: Any) -> Column[Any]:
2514 """Create a copy of this ``Column``, uninitialized.
2516 This is used in :meth:`_schema.Table.to_metadata` and by the ORM.
2518 """
2520 # Constraint objects plus non-constraint-bound ForeignKey objects
2521 args: List[SchemaItem] = [
2522 c._copy(**kw) for c in self.constraints if not c._type_bound
2523 ] + [c._copy(**kw) for c in self.foreign_keys if not c.constraint]
2525 # ticket #5276
2526 column_kwargs = {}
2527 for dialect_name in self.dialect_options:
2528 dialect_options = self.dialect_options[dialect_name]._non_defaults
2529 for (
2530 dialect_option_key,
2531 dialect_option_value,
2532 ) in dialect_options.items():
2533 column_kwargs[dialect_name + "_" + dialect_option_key] = (
2534 dialect_option_value
2535 )
2537 server_default = self.server_default
2538 server_onupdate = self.server_onupdate
2539 if isinstance(server_default, (Computed, Identity)):
2540 # TODO: likely should be copied in all cases
2541 # TODO: if a Sequence, we would need to transfer the Sequence
2542 # .metadata as well
2543 args.append(server_default._copy(**kw))
2544 server_default = server_onupdate = None
2546 type_ = self.type
2547 if isinstance(type_, SchemaEventTarget):
2548 type_ = type_.copy(**kw)
2550 # TODO: DefaultGenerator is not copied here! it's just used again
2551 # with _set_parent() pointing to the old column. see the new
2552 # use of _copy() in the new _merge() method
2554 c = self._constructor(
2555 name=self.name,
2556 type_=type_,
2557 key=self.key,
2558 primary_key=self.primary_key,
2559 unique=self.unique,
2560 system=self.system,
2561 # quote=self.quote, # disabled 2013-08-27 (commit 031ef080)
2562 index=self.index,
2563 autoincrement=self.autoincrement,
2564 default=self.default,
2565 server_default=server_default,
2566 onupdate=self.onupdate,
2567 server_onupdate=server_onupdate,
2568 doc=self.doc,
2569 comment=self.comment,
2570 _omit_from_statements=self._omit_from_statements,
2571 insert_sentinel=self._insert_sentinel,
2572 *args,
2573 **column_kwargs,
2574 )
2576 # copy the state of "nullable" exactly, to accommodate for
2577 # ORM flipping the .nullable flag directly
2578 c.nullable = self.nullable
2579 c._user_defined_nullable = self._user_defined_nullable
2581 return self._schema_item_copy(c)
2583 def _merge(
2584 self, other: Column[Any], *, omit_defaults: bool = False
2585 ) -> None:
2586 """merge the elements of this column onto "other"
2588 this is used by ORM pep-593 merge and will likely need a lot
2589 of fixes.
2592 """
2594 if self.primary_key:
2595 other.primary_key = True
2597 if self.autoincrement != "auto" and other.autoincrement == "auto":
2598 other.autoincrement = self.autoincrement
2600 if self.system:
2601 other.system = self.system
2603 if self.info:
2604 other.info.update(self.info)
2606 type_ = self.type
2607 if not type_._isnull and other.type._isnull:
2608 if isinstance(type_, SchemaEventTarget):
2609 type_ = type_.copy()
2611 other.type = type_
2613 if isinstance(type_, SchemaEventTarget):
2614 type_._set_parent_with_dispatch(other)
2616 for impl in type_._variant_mapping.values():
2617 if isinstance(impl, SchemaEventTarget):
2618 impl._set_parent_with_dispatch(other)
2620 if (
2621 self._user_defined_nullable is not NULL_UNSPECIFIED
2622 and other._user_defined_nullable is NULL_UNSPECIFIED
2623 ):
2624 other.nullable = self.nullable
2625 other._user_defined_nullable = self._user_defined_nullable
2627 if (
2628 not omit_defaults
2629 and self.default is not None
2630 and other.default is None
2631 ):
2632 new_default = self.default._copy()
2633 new_default._set_parent(other)
2635 if self.server_default and other.server_default is None:
2636 new_server_default = self.server_default
2637 if isinstance(new_server_default, FetchedValue):
2638 new_server_default = new_server_default._copy()
2639 new_server_default._set_parent(other)
2640 else:
2641 other.server_default = new_server_default
2643 if self.server_onupdate and other.server_onupdate is None:
2644 new_server_onupdate = self.server_onupdate
2645 new_server_onupdate = new_server_onupdate._copy()
2646 new_server_onupdate._set_parent(other)
2648 if self.onupdate and other.onupdate is None:
2649 new_onupdate = self.onupdate._copy()
2650 new_onupdate._set_parent(other)
2652 if self.index in (True, False) and other.index is None:
2653 other.index = self.index
2655 if self.unique in (True, False) and other.unique is None:
2656 other.unique = self.unique
2658 if self.doc and other.doc is None:
2659 other.doc = self.doc
2661 if self.comment and other.comment is None:
2662 other.comment = self.comment
2664 for const in self.constraints:
2665 if not const._type_bound:
2666 new_const = const._copy()
2667 new_const._set_parent(other)
2669 for fk in self.foreign_keys:
2670 if not fk.constraint:
2671 new_fk = fk._copy()
2672 new_fk._set_parent(other)
2674 def _make_proxy(
2675 self,
2676 selectable: FromClause,
2677 primary_key: ColumnSet,
2678 foreign_keys: Set[KeyedColumnElement[Any]],
2679 name: Optional[str] = None,
2680 key: Optional[str] = None,
2681 name_is_truncatable: bool = False,
2682 compound_select_cols: Optional[
2683 _typing_Sequence[ColumnElement[Any]]
2684 ] = None,
2685 **kw: Any,
2686 ) -> Tuple[str, ColumnClause[_T]]:
2687 """Create a *proxy* for this column.
2689 This is a copy of this ``Column`` referenced by a different parent
2690 (such as an alias or select statement). The column should
2691 be used only in select scenarios, as its full DDL/default
2692 information is not transferred.
2694 """
2696 fk = [
2697 ForeignKey(
2698 col if col is not None else f._colspec,
2699 _unresolvable=col is None,
2700 _constraint=f.constraint,
2701 )
2702 for f, col in [
2703 (fk, fk._resolve_column(raiseerr=False))
2704 for fk in self.foreign_keys
2705 ]
2706 ]
2708 if name is None and self.name is None:
2709 raise exc.InvalidRequestError(
2710 "Cannot initialize a sub-selectable"
2711 " with this Column object until its 'name' has "
2712 "been assigned."
2713 )
2714 try:
2715 c = self._constructor(
2716 (
2717 coercions.expect(
2718 roles.TruncatedLabelRole, name if name else self.name
2719 )
2720 if name_is_truncatable
2721 else (name or self.name)
2722 ),
2723 self.type,
2724 # this may actually be ._proxy_key when the key is incoming
2725 key=key if key else name if name else self.key,
2726 primary_key=self.primary_key,
2727 nullable=self.nullable,
2728 _proxies=(
2729 list(compound_select_cols)
2730 if compound_select_cols
2731 else [self]
2732 ),
2733 *fk,
2734 )
2735 except TypeError as err:
2736 raise TypeError(
2737 "Could not create a copy of this %r object. "
2738 "Ensure the class includes a _constructor() "
2739 "attribute or method which accepts the "
2740 "standard Column constructor arguments, or "
2741 "references the Column class itself." % self.__class__
2742 ) from err
2744 c.table = selectable
2745 c._propagate_attrs = selectable._propagate_attrs
2746 if selectable._is_clone_of is not None:
2747 c._is_clone_of = selectable._is_clone_of.columns.get(c.key)
2749 if self.primary_key:
2750 primary_key.add(c)
2752 if fk:
2753 foreign_keys.update(fk) # type: ignore
2755 return c.key, c
2758def insert_sentinel(
2759 name: Optional[str] = None,
2760 type_: Optional[_TypeEngineArgument[_T]] = None,
2761 *,
2762 default: Optional[Any] = None,
2763 omit_from_statements: bool = True,
2764) -> Column[Any]:
2765 """Provides a surrogate :class:`_schema.Column` that will act as a
2766 dedicated insert :term:`sentinel` column, allowing efficient bulk
2767 inserts with deterministic RETURNING sorting for tables that
2768 don't otherwise have qualifying primary key configurations.
2770 Adding this column to a :class:`.Table` object requires that a
2771 corresponding database table actually has this column present, so if adding
2772 it to an existing model, existing database tables would need to be migrated
2773 (e.g. using ALTER TABLE or similar) to include this column.
2775 For background on how this object is used, see the section
2776 :ref:`engine_insertmanyvalues_sentinel_columns` as part of the
2777 section :ref:`engine_insertmanyvalues`.
2779 The :class:`_schema.Column` returned will be a nullable integer column by
2780 default and make use of a sentinel-specific default generator used only in
2781 "insertmanyvalues" operations.
2783 .. seealso::
2785 :func:`_orm.orm_insert_sentinel`
2787 :paramref:`_schema.Column.insert_sentinel`
2789 :ref:`engine_insertmanyvalues`
2791 :ref:`engine_insertmanyvalues_sentinel_columns`
2794 .. versionadded:: 2.0.10
2796 """
2797 return Column(
2798 name=name,
2799 type_=type_api.INTEGERTYPE if type_ is None else type_,
2800 default=(
2801 default if default is not None else _InsertSentinelColumnDefault()
2802 ),
2803 _omit_from_statements=omit_from_statements,
2804 insert_sentinel=True,
2805 )
2808class ForeignKey(DialectKWArgs, SchemaItem):
2809 """Defines a dependency between two columns.
2811 ``ForeignKey`` is specified as an argument to a :class:`_schema.Column`
2812 object,
2813 e.g.::
2815 t = Table(
2816 "remote_table",
2817 metadata,
2818 Column("remote_id", ForeignKey("main_table.id")),
2819 )
2821 Note that ``ForeignKey`` is only a marker object that defines
2822 a dependency between two columns. The actual constraint
2823 is in all cases represented by the :class:`_schema.ForeignKeyConstraint`
2824 object. This object will be generated automatically when
2825 a ``ForeignKey`` is associated with a :class:`_schema.Column` which
2826 in turn is associated with a :class:`_schema.Table`. Conversely,
2827 when :class:`_schema.ForeignKeyConstraint` is applied to a
2828 :class:`_schema.Table`,
2829 ``ForeignKey`` markers are automatically generated to be
2830 present on each associated :class:`_schema.Column`, which are also
2831 associated with the constraint object.
2833 Note that you cannot define a "composite" foreign key constraint,
2834 that is a constraint between a grouping of multiple parent/child
2835 columns, using ``ForeignKey`` objects. To define this grouping,
2836 the :class:`_schema.ForeignKeyConstraint` object must be used, and applied
2837 to the :class:`_schema.Table`. The associated ``ForeignKey`` objects
2838 are created automatically.
2840 The ``ForeignKey`` objects associated with an individual
2841 :class:`_schema.Column`
2842 object are available in the `foreign_keys` collection
2843 of that column.
2845 Further examples of foreign key configuration are in
2846 :ref:`metadata_foreignkeys`.
2848 """
2850 __visit_name__ = "foreign_key"
2852 parent: Column[Any]
2854 _table_column: Optional[Column[Any]]
2856 _colspec: Union[str, Column[Any]]
2858 def __init__(
2859 self,
2860 column: _DDLColumnReferenceArgument,
2861 _constraint: Optional[ForeignKeyConstraint] = None,
2862 use_alter: bool = False,
2863 name: _ConstraintNameArgument = None,
2864 onupdate: Optional[str] = None,
2865 ondelete: Optional[str] = None,
2866 deferrable: Optional[bool] = None,
2867 initially: Optional[str] = None,
2868 link_to_name: bool = False,
2869 match: Optional[str] = None,
2870 info: Optional[_InfoType] = None,
2871 comment: Optional[str] = None,
2872 _unresolvable: bool = False,
2873 **dialect_kw: Any,
2874 ):
2875 r"""
2876 Construct a column-level FOREIGN KEY.
2878 The :class:`_schema.ForeignKey` object when constructed generates a
2879 :class:`_schema.ForeignKeyConstraint`
2880 which is associated with the parent
2881 :class:`_schema.Table` object's collection of constraints.
2883 :param column: A single target column for the key relationship. A
2884 :class:`_schema.Column` object or a column name as a string:
2885 ``tablename.columnkey`` or ``schema.tablename.columnkey``.
2886 ``columnkey`` is the ``key`` which has been assigned to the column
2887 (defaults to the column name itself), unless ``link_to_name`` is
2888 ``True`` in which case the rendered name of the column is used.
2890 :param name: Optional string. An in-database name for the key if
2891 `constraint` is not provided.
2893 :param onupdate: Optional string. If set, emit ON UPDATE <value> when
2894 issuing DDL for this constraint. Typical values include CASCADE,
2895 DELETE and RESTRICT.
2897 .. seealso::
2899 :ref:`on_update_on_delete`
2901 :param ondelete: Optional string. If set, emit ON DELETE <value> when
2902 issuing DDL for this constraint. Typical values include CASCADE,
2903 SET NULL and RESTRICT. Some dialects may allow for additional
2904 syntaxes.
2906 .. seealso::
2908 :ref:`on_update_on_delete`
2910 :param deferrable: Optional bool. If set, emit DEFERRABLE or NOT
2911 DEFERRABLE when issuing DDL for this constraint.
2913 :param initially: Optional string. If set, emit INITIALLY <value> when
2914 issuing DDL for this constraint.
2916 :param link_to_name: if True, the string name given in ``column`` is
2917 the rendered name of the referenced column, not its locally
2918 assigned ``key``.
2920 :param use_alter: passed to the underlying
2921 :class:`_schema.ForeignKeyConstraint`
2922 to indicate the constraint should
2923 be generated/dropped externally from the CREATE TABLE/ DROP TABLE
2924 statement. See :paramref:`_schema.ForeignKeyConstraint.use_alter`
2925 for further description.
2927 .. seealso::
2929 :paramref:`_schema.ForeignKeyConstraint.use_alter`
2931 :ref:`use_alter`
2933 :param match: Optional string. If set, emit MATCH <value> when issuing
2934 DDL for this constraint. Typical values include SIMPLE, PARTIAL
2935 and FULL.
2937 :param info: Optional data dictionary which will be populated into the
2938 :attr:`.SchemaItem.info` attribute of this object.
2940 :param comment: Optional string that will render an SQL comment on
2941 foreign key constraint creation.
2943 .. versionadded:: 2.0
2945 :param \**dialect_kw: Additional keyword arguments are dialect
2946 specific, and passed in the form ``<dialectname>_<argname>``. The
2947 arguments are ultimately handled by a corresponding
2948 :class:`_schema.ForeignKeyConstraint`.
2949 See the documentation regarding
2950 an individual dialect at :ref:`dialect_toplevel` for detail on
2951 documented arguments.
2953 """
2955 self._unresolvable = _unresolvable
2957 self._colspec, self._table_column = self._parse_colspec_argument(
2958 column
2959 )
2961 # the linked ForeignKeyConstraint.
2962 # ForeignKey will create this when parent Column
2963 # is attached to a Table, *or* ForeignKeyConstraint
2964 # object passes itself in when creating ForeignKey
2965 # markers.
2966 self.constraint = _constraint
2968 # .parent is not Optional under normal use
2969 self.parent = None # type: ignore
2971 self.use_alter = use_alter
2972 self.name = name
2973 self.onupdate = onupdate
2974 self.ondelete = ondelete
2975 self.deferrable = deferrable
2976 self.initially = initially
2977 self.link_to_name = link_to_name
2978 self.match = match
2979 self.comment = comment
2980 if info:
2981 self.info = info
2982 self._unvalidated_dialect_kw = dialect_kw
2984 def _resolve_colspec_argument(
2985 self,
2986 ) -> Tuple[
2987 Union[str, Column[Any]],
2988 Optional[Column[Any]],
2989 ]:
2990 argument = self._colspec
2992 return self._parse_colspec_argument(argument)
2994 def _parse_colspec_argument(
2995 self,
2996 argument: _DDLColumnArgument,
2997 ) -> Tuple[
2998 Union[str, Column[Any]],
2999 Optional[Column[Any]],
3000 ]:
3001 _colspec = coercions.expect(roles.DDLReferredColumnRole, argument)
3003 if isinstance(_colspec, str):
3004 _table_column = None
3005 else:
3006 assert isinstance(_colspec, ColumnClause)
3007 _table_column = _colspec
3009 if not isinstance(_table_column.table, (type(None), TableClause)):
3010 raise exc.ArgumentError(
3011 "ForeignKey received Column not bound "
3012 "to a Table, got: %r" % _table_column.table
3013 )
3015 return _colspec, _table_column
3017 def __repr__(self) -> str:
3018 return "ForeignKey(%r)" % self._get_colspec()
3020 @util.deprecated(
3021 "1.4",
3022 "The :meth:`_schema.ForeignKey.copy` method is deprecated "
3023 "and will be removed in a future release.",
3024 )
3025 def copy(self, *, schema: Optional[str] = None, **kw: Any) -> ForeignKey:
3026 return self._copy(schema=schema, **kw)
3028 def _copy(self, *, schema: Optional[str] = None, **kw: Any) -> ForeignKey:
3029 """Produce a copy of this :class:`_schema.ForeignKey` object.
3031 The new :class:`_schema.ForeignKey` will not be bound
3032 to any :class:`_schema.Column`.
3034 This method is usually used by the internal
3035 copy procedures of :class:`_schema.Column`, :class:`_schema.Table`,
3036 and :class:`_schema.MetaData`.
3038 :param schema: The returned :class:`_schema.ForeignKey` will
3039 reference the original table and column name, qualified
3040 by the given string schema name.
3042 """
3043 fk = ForeignKey(
3044 self._get_colspec(schema=schema),
3045 use_alter=self.use_alter,
3046 name=self.name,
3047 onupdate=self.onupdate,
3048 ondelete=self.ondelete,
3049 deferrable=self.deferrable,
3050 initially=self.initially,
3051 link_to_name=self.link_to_name,
3052 match=self.match,
3053 comment=self.comment,
3054 **self._unvalidated_dialect_kw,
3055 )
3056 return self._schema_item_copy(fk)
3058 def _get_colspec(
3059 self,
3060 schema: Optional[
3061 Union[
3062 str,
3063 Literal[SchemaConst.RETAIN_SCHEMA, SchemaConst.BLANK_SCHEMA],
3064 ]
3065 ] = None,
3066 table_name: Optional[str] = None,
3067 _is_copy: bool = False,
3068 ) -> str:
3069 """Return a string based 'column specification' for this
3070 :class:`_schema.ForeignKey`.
3072 This is usually the equivalent of the string-based "tablename.colname"
3073 argument first passed to the object's constructor.
3075 """
3077 _colspec, effective_table_column = self._resolve_colspec_argument()
3079 if schema not in (None, RETAIN_SCHEMA):
3080 _schema, tname, colname = self._column_tokens
3081 if table_name is not None:
3082 tname = table_name
3083 if schema is BLANK_SCHEMA:
3084 return "%s.%s" % (tname, colname)
3085 else:
3086 return "%s.%s.%s" % (schema, tname, colname)
3087 elif table_name:
3088 schema, tname, colname = self._column_tokens
3089 if schema:
3090 return "%s.%s.%s" % (schema, table_name, colname)
3091 else:
3092 return "%s.%s" % (table_name, colname)
3093 elif effective_table_column is not None:
3094 if effective_table_column.table is None:
3095 if _is_copy:
3096 raise exc.InvalidRequestError(
3097 f"Can't copy ForeignKey object which refers to "
3098 f"non-table bound Column {effective_table_column!r}"
3099 )
3100 else:
3101 return effective_table_column.key
3102 return "%s.%s" % (
3103 effective_table_column.table.fullname,
3104 effective_table_column.key,
3105 )
3106 else:
3107 assert isinstance(_colspec, str)
3108 return _colspec
3110 @property
3111 def _referred_schema(self) -> Optional[str]:
3112 return self._column_tokens[0]
3114 def _table_key_within_construction(self) -> Any:
3115 """get the table key but only safely"""
3117 if self._table_column is not None:
3118 if self._table_column.table is None:
3119 return None
3120 else:
3121 return self._table_column.table.key
3122 else:
3123 schema, tname, colname = self._column_tokens
3124 return _get_table_key(tname, schema)
3126 target_fullname = property(_get_colspec)
3128 def references(self, table: Table) -> bool:
3129 """Return True if the given :class:`_schema.Table`
3130 is referenced by this
3131 :class:`_schema.ForeignKey`."""
3133 return table.corresponding_column(self.column) is not None
3135 def get_referent(self, table: FromClause) -> Optional[Column[Any]]:
3136 """Return the :class:`_schema.Column` in the given
3137 :class:`_schema.Table` (or any :class:`.FromClause`)
3138 referenced by this :class:`_schema.ForeignKey`.
3140 Returns None if this :class:`_schema.ForeignKey`
3141 does not reference the given
3142 :class:`_schema.Table`.
3144 """
3145 # our column is a Column, and any subquery etc. proxying us
3146 # would be doing so via another Column, so that's what would
3147 # be returned here
3148 return table.columns.corresponding_column(self.column) # type: ignore
3150 @util.memoized_property
3151 def _column_tokens(self) -> Tuple[Optional[str], str, Optional[str]]:
3152 """parse a string-based _colspec into its component parts."""
3154 m = self._get_colspec().split(".")
3155 if len(m) == 1:
3156 tname = m.pop()
3157 colname = None
3158 else:
3159 colname = m.pop()
3160 tname = m.pop()
3162 # A FK between column 'bar' and table 'foo' can be
3163 # specified as 'foo', 'foo.bar', 'dbo.foo.bar',
3164 # 'otherdb.dbo.foo.bar'. Once we have the column name and
3165 # the table name, treat everything else as the schema
3166 # name. Some databases (e.g. Sybase) support
3167 # inter-database foreign keys. See tickets#1341 and --
3168 # indirectly related -- Ticket #594. This assumes that '.'
3169 # will never appear *within* any component of the FK.
3171 if len(m) > 0:
3172 schema = ".".join(m)
3173 else:
3174 schema = None
3175 return schema, tname, colname
3177 def _resolve_col_tokens(self) -> Tuple[Table, str, Optional[str]]:
3178 if self.parent is None:
3179 raise exc.InvalidRequestError(
3180 "this ForeignKey object does not yet have a "
3181 "parent Column associated with it."
3182 )
3184 elif self.parent.table is None:
3185 raise exc.InvalidRequestError(
3186 "this ForeignKey's parent column is not yet associated "
3187 "with a Table."
3188 )
3190 parenttable = self.parent.table
3192 if self._unresolvable:
3193 schema, tname, colname = self._column_tokens
3194 tablekey = _get_table_key(tname, schema)
3195 return parenttable, tablekey, colname
3197 # assertion
3198 # basically Column._make_proxy() sends the actual
3199 # target Column to the ForeignKey object, so the
3200 # string resolution here is never called.
3201 for c in self.parent.base_columns:
3202 if isinstance(c, Column):
3203 assert c.table is parenttable
3204 break
3205 else:
3206 assert False
3207 ######################
3209 schema, tname, colname = self._column_tokens
3211 if schema is None and parenttable.metadata.schema is not None:
3212 schema = parenttable.metadata.schema
3214 tablekey = _get_table_key(tname, schema)
3215 return parenttable, tablekey, colname
3217 def _link_to_col_by_colstring(
3218 self, parenttable: Table, table: Table, colname: Optional[str]
3219 ) -> Column[Any]:
3220 _column = None
3221 if colname is None:
3222 # colname is None in the case that ForeignKey argument
3223 # was specified as table name only, in which case we
3224 # match the column name to the same column on the
3225 # parent.
3226 # this use case wasn't working in later 1.x series
3227 # as it had no test coverage; fixed in 2.0
3228 parent = self.parent
3229 assert parent is not None
3230 key = parent.key
3231 _column = table.c.get(key, None)
3232 elif self.link_to_name:
3233 key = colname
3234 for c in table.c:
3235 if c.name == colname:
3236 _column = c
3237 else:
3238 key = colname
3239 _column = table.c.get(colname, None)
3241 if _column is None:
3242 raise exc.NoReferencedColumnError(
3243 "Could not initialize target column "
3244 f"for ForeignKey '{self._get_colspec()}' "
3245 f"on table '{parenttable.name}': "
3246 f"table '{table.name}' has no column named '{key}'",
3247 table.name,
3248 key,
3249 )
3251 return _column
3253 def _set_target_column(self, column: Column[Any]) -> None:
3254 assert self.parent is not None
3256 # propagate TypeEngine to parent if it didn't have one
3257 if self.parent.type._isnull:
3258 self.parent.type = column.type
3260 # super-edgy case, if other FKs point to our column,
3261 # they'd get the type propagated out also.
3263 def set_type(fk: ForeignKey) -> None:
3264 if fk.parent.type._isnull:
3265 fk.parent.type = column.type
3267 self.parent._setup_on_memoized_fks(set_type)
3269 self.column = column # type: ignore
3271 @util.ro_memoized_property
3272 def column(self) -> Column[Any]:
3273 """Return the target :class:`_schema.Column` referenced by this
3274 :class:`_schema.ForeignKey`.
3276 If no target column has been established, an exception
3277 is raised.
3279 """
3280 return self._resolve_column()
3282 @overload
3283 def _resolve_column(
3284 self, *, raiseerr: Literal[True] = ...
3285 ) -> Column[Any]: ...
3287 @overload
3288 def _resolve_column(
3289 self, *, raiseerr: bool = ...
3290 ) -> Optional[Column[Any]]: ...
3292 def _resolve_column(
3293 self, *, raiseerr: bool = True
3294 ) -> Optional[Column[Any]]:
3295 _column: Column[Any]
3297 _colspec, effective_table_column = self._resolve_colspec_argument()
3299 if isinstance(_colspec, str):
3300 parenttable, tablekey, colname = self._resolve_col_tokens()
3302 if self._unresolvable or tablekey not in parenttable.metadata:
3303 if not raiseerr:
3304 return None
3305 raise exc.NoReferencedTableError(
3306 f"Foreign key associated with column "
3307 f"'{self.parent}' could not find "
3308 f"table '{tablekey}' with which to generate a "
3309 f"foreign key to target column '{colname}'",
3310 tablekey,
3311 )
3312 elif parenttable.key not in parenttable.metadata:
3313 if not raiseerr:
3314 return None
3315 raise exc.InvalidRequestError(
3316 f"Table {parenttable} is no longer associated with its "
3317 "parent MetaData"
3318 )
3319 else:
3320 table = parenttable.metadata.tables[tablekey]
3321 return self._link_to_col_by_colstring(
3322 parenttable, table, colname
3323 )
3325 elif hasattr(_colspec, "__clause_element__"):
3326 _column = _colspec.__clause_element__()
3327 return _column
3328 else:
3329 assert isinstance(_colspec, Column)
3330 _column = _colspec
3331 return _column
3333 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
3334 assert isinstance(parent, Column)
3336 if self.parent is not None and self.parent is not parent:
3337 raise exc.InvalidRequestError(
3338 "This ForeignKey already has a parent !"
3339 )
3340 self.parent = parent
3341 self.parent.foreign_keys.add(self)
3342 self.parent._on_table_attach(self._set_table)
3344 def _set_remote_table(self, table: Table) -> None:
3345 parenttable, _, colname = self._resolve_col_tokens()
3346 _column = self._link_to_col_by_colstring(parenttable, table, colname)
3347 self._set_target_column(_column)
3348 assert self.constraint is not None
3349 self.constraint._validate_dest_table(table)
3351 def _remove_from_metadata(self, metadata: MetaData) -> None:
3352 parenttable, table_key, colname = self._resolve_col_tokens()
3353 fk_key = (table_key, colname)
3355 if self in metadata._fk_memos[fk_key]:
3356 # TODO: no test coverage for self not in memos
3357 metadata._fk_memos[fk_key].remove(self)
3359 def _set_table(self, column: Column[Any], table: Table) -> None:
3360 # standalone ForeignKey - create ForeignKeyConstraint
3361 # on the hosting Table when attached to the Table.
3362 assert isinstance(table, Table)
3363 if self.constraint is None:
3364 self.constraint = ForeignKeyConstraint(
3365 [],
3366 [],
3367 use_alter=self.use_alter,
3368 name=self.name,
3369 onupdate=self.onupdate,
3370 ondelete=self.ondelete,
3371 deferrable=self.deferrable,
3372 initially=self.initially,
3373 match=self.match,
3374 comment=self.comment,
3375 **self._unvalidated_dialect_kw,
3376 )
3377 self.constraint._append_element(column, self)
3378 self.constraint._set_parent_with_dispatch(table)
3379 table.foreign_keys.add(self)
3380 # set up remote ".column" attribute, or a note to pick it
3381 # up when the other Table/Column shows up
3383 _colspec, _ = self._resolve_colspec_argument()
3384 if isinstance(_colspec, str):
3385 parenttable, table_key, colname = self._resolve_col_tokens()
3386 fk_key = (table_key, colname)
3387 if table_key in parenttable.metadata.tables:
3388 table = parenttable.metadata.tables[table_key]
3389 try:
3390 _column = self._link_to_col_by_colstring(
3391 parenttable, table, colname
3392 )
3393 except exc.NoReferencedColumnError:
3394 # this is OK, we'll try later
3395 pass
3396 else:
3397 self._set_target_column(_column)
3399 parenttable.metadata._fk_memos[fk_key].append(self)
3400 elif hasattr(_colspec, "__clause_element__"):
3401 _column = _colspec.__clause_element__()
3402 self._set_target_column(_column)
3403 else:
3404 self._set_target_column(_colspec)
3407if TYPE_CHECKING:
3409 def default_is_sequence(
3410 obj: Optional[DefaultGenerator],
3411 ) -> TypeGuard[Sequence]: ...
3413 def default_is_clause_element(
3414 obj: Optional[DefaultGenerator],
3415 ) -> TypeGuard[ColumnElementColumnDefault]: ...
3417 def default_is_scalar(
3418 obj: Optional[DefaultGenerator],
3419 ) -> TypeGuard[ScalarElementColumnDefault]: ...
3421else:
3422 default_is_sequence = operator.attrgetter("is_sequence")
3424 default_is_clause_element = operator.attrgetter("is_clause_element")
3426 default_is_scalar = operator.attrgetter("is_scalar")
3429class DefaultGenerator(Executable, SchemaItem):
3430 """Base class for column *default* values.
3432 This object is only present on column.default or column.onupdate.
3433 It's not valid as a server default.
3435 """
3437 __visit_name__ = "default_generator"
3439 _is_default_generator = True
3440 is_sequence = False
3441 is_identity = False
3442 is_server_default = False
3443 is_clause_element = False
3444 is_callable = False
3445 is_scalar = False
3446 has_arg = False
3447 is_sentinel = False
3448 column: Optional[Column[Any]]
3450 def __init__(self, for_update: bool = False) -> None:
3451 self.for_update = for_update
3453 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
3454 if TYPE_CHECKING:
3455 assert isinstance(parent, Column)
3456 self.column = parent
3457 if self.for_update:
3458 self.column.onupdate = self
3459 else:
3460 self.column.default = self
3462 def _copy(self) -> DefaultGenerator:
3463 raise NotImplementedError()
3465 def _execute_on_connection(
3466 self,
3467 connection: Connection,
3468 distilled_params: _CoreMultiExecuteParams,
3469 execution_options: CoreExecuteOptionsParameter,
3470 ) -> Any:
3471 util.warn_deprecated(
3472 "Using the .execute() method to invoke a "
3473 "DefaultGenerator object is deprecated; please use "
3474 "the .scalar() method.",
3475 "2.0",
3476 )
3477 return self._execute_on_scalar(
3478 connection, distilled_params, execution_options
3479 )
3481 def _execute_on_scalar(
3482 self,
3483 connection: Connection,
3484 distilled_params: _CoreMultiExecuteParams,
3485 execution_options: CoreExecuteOptionsParameter,
3486 ) -> Any:
3487 return connection._execute_default(
3488 self, distilled_params, execution_options
3489 )
3492class ColumnDefault(DefaultGenerator, ABC):
3493 """A plain default value on a column.
3495 This could correspond to a constant, a callable function,
3496 or a SQL clause.
3498 :class:`.ColumnDefault` is generated automatically
3499 whenever the ``default``, ``onupdate`` arguments of
3500 :class:`_schema.Column` are used. A :class:`.ColumnDefault`
3501 can be passed positionally as well.
3503 For example, the following::
3505 Column("foo", Integer, default=50)
3507 Is equivalent to::
3509 Column("foo", Integer, ColumnDefault(50))
3511 """
3513 arg: Any
3515 @overload
3516 def __new__(
3517 cls, arg: Callable[..., Any], for_update: bool = ...
3518 ) -> CallableColumnDefault: ...
3520 @overload
3521 def __new__(
3522 cls, arg: ColumnElement[Any], for_update: bool = ...
3523 ) -> ColumnElementColumnDefault: ...
3525 # if I return ScalarElementColumnDefault here, which is what's actually
3526 # returned, mypy complains that
3527 # overloads overlap w/ incompatible return types.
3528 @overload
3529 def __new__(cls, arg: object, for_update: bool = ...) -> ColumnDefault: ...
3531 def __new__(
3532 cls, arg: Any = None, for_update: bool = False
3533 ) -> ColumnDefault:
3534 """Construct a new :class:`.ColumnDefault`.
3537 :param arg: argument representing the default value.
3538 May be one of the following:
3540 * a plain non-callable Python value, such as a
3541 string, integer, boolean, or other simple type.
3542 The default value will be used as is each time.
3543 * a SQL expression, that is one which derives from
3544 :class:`_expression.ColumnElement`. The SQL expression will
3545 be rendered into the INSERT or UPDATE statement,
3546 or in the case of a primary key column when
3547 RETURNING is not used may be
3548 pre-executed before an INSERT within a SELECT.
3549 * A Python callable. The function will be invoked for each
3550 new row subject to an INSERT or UPDATE.
3551 The callable must accept exactly
3552 zero or one positional arguments. The one-argument form
3553 will receive an instance of the :class:`.ExecutionContext`,
3554 which provides contextual information as to the current
3555 :class:`_engine.Connection` in use as well as the current
3556 statement and parameters.
3558 """
3560 if isinstance(arg, FetchedValue):
3561 raise exc.ArgumentError(
3562 "ColumnDefault may not be a server-side default type."
3563 )
3564 elif callable(arg):
3565 cls = CallableColumnDefault
3566 elif isinstance(arg, ClauseElement):
3567 cls = ColumnElementColumnDefault
3568 elif arg is not None:
3569 cls = ScalarElementColumnDefault
3571 return object.__new__(cls)
3573 def __repr__(self) -> str:
3574 return f"{self.__class__.__name__}({self.arg!r})"
3577class ScalarElementColumnDefault(ColumnDefault):
3578 """default generator for a fixed scalar Python value
3580 .. versionadded:: 2.0
3582 """
3584 is_scalar = True
3585 has_arg = True
3587 def __init__(self, arg: Any, for_update: bool = False) -> None:
3588 self.for_update = for_update
3589 self.arg = arg
3591 def _copy(self) -> ScalarElementColumnDefault:
3592 return ScalarElementColumnDefault(
3593 arg=self.arg, for_update=self.for_update
3594 )
3597class _InsertSentinelColumnDefault(ColumnDefault):
3598 """Default generator that's specific to the use of a "sentinel" column
3599 when using the insertmanyvalues feature.
3601 This default is used as part of the :func:`_schema.insert_sentinel`
3602 construct.
3604 """
3606 is_sentinel = True
3607 for_update = False
3608 arg = None
3610 def __new__(cls) -> _InsertSentinelColumnDefault:
3611 return object.__new__(cls)
3613 def __init__(self) -> None:
3614 pass
3616 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
3617 col = cast("Column[Any]", parent)
3618 if not col._insert_sentinel:
3619 raise exc.ArgumentError(
3620 "The _InsertSentinelColumnDefault may only be applied to a "
3621 "Column marked as insert_sentinel=True"
3622 )
3623 elif not col.nullable:
3624 raise exc.ArgumentError(
3625 "The _InsertSentinelColumnDefault may only be applied to a "
3626 "Column that is nullable"
3627 )
3629 super()._set_parent(parent, **kw)
3631 def _copy(self) -> _InsertSentinelColumnDefault:
3632 return _InsertSentinelColumnDefault()
3635_SQLExprDefault = Union["ColumnElement[Any]", "TextClause"]
3638class ColumnElementColumnDefault(ColumnDefault):
3639 """default generator for a SQL expression
3641 .. versionadded:: 2.0
3643 """
3645 is_clause_element = True
3646 has_arg = True
3647 arg: _SQLExprDefault
3649 def __init__(
3650 self,
3651 arg: _SQLExprDefault,
3652 for_update: bool = False,
3653 ) -> None:
3654 self.for_update = for_update
3655 self.arg = arg
3657 def _copy(self) -> ColumnElementColumnDefault:
3658 return ColumnElementColumnDefault(
3659 arg=self.arg, for_update=self.for_update
3660 )
3662 @util.memoized_property
3663 @util.preload_module("sqlalchemy.sql.sqltypes")
3664 def _arg_is_typed(self) -> bool:
3665 sqltypes = util.preloaded.sql_sqltypes
3667 return not isinstance(self.arg.type, sqltypes.NullType)
3670class _CallableColumnDefaultProtocol(Protocol):
3671 def __call__(self, context: ExecutionContext) -> Any: ...
3674class CallableColumnDefault(ColumnDefault):
3675 """default generator for a callable Python function
3677 .. versionadded:: 2.0
3679 """
3681 is_callable = True
3682 arg: _CallableColumnDefaultProtocol
3683 has_arg = True
3685 def __init__(
3686 self,
3687 arg: Union[_CallableColumnDefaultProtocol, Callable[[], Any]],
3688 for_update: bool = False,
3689 ) -> None:
3690 self.for_update = for_update
3691 self.arg = self._maybe_wrap_callable(arg)
3693 def _copy(self) -> CallableColumnDefault:
3694 return CallableColumnDefault(arg=self.arg, for_update=self.for_update)
3696 def _maybe_wrap_callable(
3697 self, fn: Union[_CallableColumnDefaultProtocol, Callable[[], Any]]
3698 ) -> _CallableColumnDefaultProtocol:
3699 """Wrap callables that don't accept a context.
3701 This is to allow easy compatibility with default callables
3702 that aren't specific to accepting of a context.
3704 """
3706 try:
3707 argspec = util.get_callable_argspec(fn, no_self=True)
3708 except TypeError:
3709 return util.wrap_callable(lambda ctx: fn(), fn) # type: ignore
3711 defaulted = argspec[3] is not None and len(argspec[3]) or 0
3712 positionals = len(argspec[0]) - defaulted
3714 if positionals == 0:
3715 return util.wrap_callable(lambda ctx: fn(), fn) # type: ignore
3717 elif positionals == 1:
3718 return fn # type: ignore
3719 else:
3720 raise exc.ArgumentError(
3721 "ColumnDefault Python function takes zero or one "
3722 "positional arguments"
3723 )
3726class IdentityOptions(DialectKWArgs):
3727 """Defines options for a named database sequence or an identity column.
3729 .. seealso::
3731 :class:`.Sequence`
3733 """
3735 def __init__(
3736 self,
3737 start: Optional[int] = None,
3738 increment: Optional[int] = None,
3739 minvalue: Optional[int] = None,
3740 maxvalue: Optional[int] = None,
3741 nominvalue: Optional[bool] = None,
3742 nomaxvalue: Optional[bool] = None,
3743 cycle: Optional[bool] = None,
3744 cache: Optional[int] = None,
3745 order: Optional[bool] = None,
3746 **dialect_kw: Any,
3747 ) -> None:
3748 """Construct a :class:`.IdentityOptions` object.
3750 See the :class:`.Sequence` documentation for a complete description
3751 of the parameters.
3753 :param start: the starting index of the sequence.
3754 :param increment: the increment value of the sequence.
3755 :param minvalue: the minimum value of the sequence.
3756 :param maxvalue: the maximum value of the sequence.
3757 :param nominvalue: no minimum value of the sequence.
3758 :param nomaxvalue: no maximum value of the sequence.
3759 :param cycle: allows the sequence to wrap around when the maxvalue
3760 or minvalue has been reached.
3761 :param cache: optional integer value; number of future values in the
3762 sequence which are calculated in advance.
3763 :param order: optional boolean value; if ``True``, renders the
3764 ORDER keyword.
3766 .. deprecated:: 2.1 Use ``oracle_order`` instead.
3768 """
3769 self.start = start
3770 self.increment = increment
3771 self.minvalue = minvalue
3772 self.maxvalue = maxvalue
3773 self.nominvalue = nominvalue
3774 self.nomaxvalue = nomaxvalue
3775 self.cycle = cycle
3776 self.cache = cache
3777 if order is not None:
3778 if "oracle_order" in dialect_kw:
3779 raise exc.ArgumentError(
3780 "Cannot specify both 'order' and 'oracle_order'. "
3781 "Plese use only 'oracle_order'."
3782 )
3783 dialect_kw["oracle_order"] = order
3784 self._validate_dialect_kwargs(dialect_kw)
3786 @property
3787 def _increment_is_negative(self) -> bool:
3788 return self.increment is not None and self.increment < 0
3790 @property
3791 def order(self) -> Optional[bool]:
3792 """Alias of the ``dialect_kwargs`` ``'oracle_order'``.
3794 .. deprecated:: 2.1 The 'order' attribute is deprecated.
3795 """
3796 value: Optional[bool] = self.dialect_kwargs.get("oracle_order")
3797 return value
3799 def _as_dict(self) -> Dict[str, Any]:
3800 return {
3801 k: v
3802 for k, v in {
3803 "start": self.start,
3804 "increment": self.increment,
3805 "minvalue": self.minvalue,
3806 "maxvalue": self.maxvalue,
3807 "nominvalue": self.nominvalue,
3808 "nomaxvalue": self.nomaxvalue,
3809 "cycle": self.cycle,
3810 "cache": self.cache,
3811 }.items()
3812 if v != None
3813 }
3816class Sequence(HasSchemaAttr, IdentityOptions, DefaultGenerator):
3817 """Represents a named database sequence.
3819 The :class:`.Sequence` object represents the name and configurational
3820 parameters of a database sequence. It also represents
3821 a construct that can be "executed" by a SQLAlchemy :class:`_engine.Engine`
3822 or :class:`_engine.Connection`,
3823 rendering the appropriate "next value" function
3824 for the target database and returning a result.
3826 The :class:`.Sequence` is typically associated with a primary key column::
3828 some_table = Table(
3829 "some_table",
3830 metadata,
3831 Column(
3832 "id",
3833 Integer,
3834 Sequence("some_table_seq", start=1),
3835 primary_key=True,
3836 ),
3837 )
3839 When CREATE TABLE is emitted for the above :class:`_schema.Table`, if the
3840 target platform supports sequences, a CREATE SEQUENCE statement will
3841 be emitted as well. For platforms that don't support sequences,
3842 the :class:`.Sequence` construct is ignored.
3844 .. seealso::
3846 :ref:`defaults_sequences`
3848 :class:`.CreateSequence`
3850 :class:`.DropSequence`
3852 """
3854 __visit_name__ = "sequence"
3856 is_sequence = True
3858 column: Optional[Column[Any]]
3859 data_type: Optional[TypeEngine[int]]
3861 metadata: Optional[MetaData]
3863 @util.deprecated_params(
3864 order=(
3865 "2.1",
3866 "This parameter is supported only by Oracle Database, "
3867 "use ``oracle_order`` instead.",
3868 )
3869 )
3870 def __init__(
3871 self,
3872 name: str,
3873 start: Optional[int] = None,
3874 increment: Optional[int] = None,
3875 minvalue: Optional[int] = None,
3876 maxvalue: Optional[int] = None,
3877 nominvalue: Optional[bool] = None,
3878 nomaxvalue: Optional[bool] = None,
3879 cycle: Optional[bool] = None,
3880 schema: Optional[Union[str, Literal[SchemaConst.BLANK_SCHEMA]]] = None,
3881 cache: Optional[int] = None,
3882 order: Optional[bool] = None,
3883 data_type: Optional[_TypeEngineArgument[int]] = None,
3884 optional: bool = False,
3885 quote: Optional[bool] = None,
3886 metadata: Optional[MetaData] = None,
3887 quote_schema: Optional[bool] = None,
3888 for_update: bool = False,
3889 **dialect_kw: Any,
3890 ) -> None:
3891 """Construct a :class:`.Sequence` object.
3893 :param name: the name of the sequence.
3895 :param start: the starting index of the sequence. This value is
3896 used when the CREATE SEQUENCE command is emitted to the database
3897 as the value of the "START WITH" clause. If ``None``, the
3898 clause is omitted, which on most platforms indicates a starting
3899 value of 1.
3901 .. versionchanged:: 2.0 The :paramref:`.Sequence.start` parameter
3902 is required in order to have DDL emit "START WITH". This is a
3903 reversal of a change made in version 1.4 which would implicitly
3904 render "START WITH 1" if the :paramref:`.Sequence.start` were
3905 not included. See :ref:`change_7211` for more detail.
3907 :param increment: the increment value of the sequence. This
3908 value is used when the CREATE SEQUENCE command is emitted to
3909 the database as the value of the "INCREMENT BY" clause. If ``None``,
3910 the clause is omitted, which on most platforms indicates an
3911 increment of 1.
3912 :param minvalue: the minimum value of the sequence. This
3913 value is used when the CREATE SEQUENCE command is emitted to
3914 the database as the value of the "MINVALUE" clause. If ``None``,
3915 the clause is omitted, which on most platforms indicates a
3916 minvalue of 1 and -2^63-1 for ascending and descending sequences,
3917 respectively.
3919 :param maxvalue: the maximum value of the sequence. This
3920 value is used when the CREATE SEQUENCE command is emitted to
3921 the database as the value of the "MAXVALUE" clause. If ``None``,
3922 the clause is omitted, which on most platforms indicates a
3923 maxvalue of 2^63-1 and -1 for ascending and descending sequences,
3924 respectively.
3926 :param nominvalue: no minimum value of the sequence. This
3927 value is used when the CREATE SEQUENCE command is emitted to
3928 the database as the value of the "NO MINVALUE" clause. If ``None``,
3929 the clause is omitted, which on most platforms indicates a
3930 minvalue of 1 and -2^63-1 for ascending and descending sequences,
3931 respectively.
3933 :param nomaxvalue: no maximum value of the sequence. This
3934 value is used when the CREATE SEQUENCE command is emitted to
3935 the database as the value of the "NO MAXVALUE" clause. If ``None``,
3936 the clause is omitted, which on most platforms indicates a
3937 maxvalue of 2^63-1 and -1 for ascending and descending sequences,
3938 respectively.
3940 :param cycle: allows the sequence to wrap around when the maxvalue
3941 or minvalue has been reached by an ascending or descending sequence
3942 respectively. This value is used when the CREATE SEQUENCE command
3943 is emitted to the database as the "CYCLE" clause. If the limit is
3944 reached, the next number generated will be the minvalue or maxvalue,
3945 respectively. If cycle=False (the default) any calls to nextval
3946 after the sequence has reached its maximum value will return an
3947 error.
3949 :param schema: optional schema name for the sequence, if located
3950 in a schema other than the default. The rules for selecting the
3951 schema name when a :class:`_schema.MetaData`
3952 is also present are the same
3953 as that of :paramref:`_schema.Table.schema`.
3955 :param cache: optional integer value; number of future values in the
3956 sequence which are calculated in advance. Renders the CACHE keyword
3957 understood by Oracle Database and PostgreSQL.
3959 :param order: optional boolean value; if ``True``, renders the
3960 ORDER keyword, understood by Oracle Database, indicating the sequence
3961 is definitively ordered. May be necessary to provide deterministic
3962 ordering using Oracle RAC.
3964 :param data_type: The type to be returned by the sequence, for
3965 dialects that allow us to choose between INTEGER, BIGINT, etc.
3966 (e.g., mssql).
3968 .. versionadded:: 1.4.0
3970 :param optional: boolean value, when ``True``, indicates that this
3971 :class:`.Sequence` object only needs to be explicitly generated
3972 on backends that don't provide another way to generate primary
3973 key identifiers. Currently, it essentially means, "don't create
3974 this sequence on the PostgreSQL backend, where the SERIAL keyword
3975 creates a sequence for us automatically".
3976 :param quote: boolean value, when ``True`` or ``False``, explicitly
3977 forces quoting of the :paramref:`_schema.Sequence.name` on or off.
3978 When left at its default of ``None``, normal quoting rules based
3979 on casing and reserved words take place.
3980 :param quote_schema: Set the quoting preferences for the ``schema``
3981 name.
3983 :param metadata: optional :class:`_schema.MetaData` object which this
3984 :class:`.Sequence` will be associated with. A :class:`.Sequence`
3985 that is associated with a :class:`_schema.MetaData`
3986 gains the following
3987 capabilities:
3989 * The :class:`.Sequence` will inherit the
3990 :paramref:`_schema.MetaData.schema`
3991 parameter specified to the target :class:`_schema.MetaData`, which
3992 affects the production of CREATE / DROP DDL, if any.
3994 * The :meth:`.Sequence.create` and :meth:`.Sequence.drop` methods
3995 automatically use the engine bound to the :class:`_schema.MetaData`
3996 object, if any.
3998 * The :meth:`_schema.MetaData.create_all` and
3999 :meth:`_schema.MetaData.drop_all`
4000 methods will emit CREATE / DROP for this :class:`.Sequence`,
4001 even if the :class:`.Sequence` is not associated with any
4002 :class:`_schema.Table` / :class:`_schema.Column`
4003 that's a member of this
4004 :class:`_schema.MetaData`.
4006 The above behaviors can only occur if the :class:`.Sequence` is
4007 explicitly associated with the :class:`_schema.MetaData`
4008 via this parameter.
4010 .. seealso::
4012 :ref:`sequence_metadata` - full discussion of the
4013 :paramref:`.Sequence.metadata` parameter.
4015 :param for_update: Indicates this :class:`.Sequence`, when associated
4016 with a :class:`_schema.Column`,
4017 should be invoked for UPDATE statements
4018 on that column's table, rather than for INSERT statements, when
4019 no value is otherwise present for that column in the statement.
4021 """
4022 DefaultGenerator.__init__(self, for_update=for_update)
4023 IdentityOptions.__init__(
4024 self,
4025 start=start,
4026 increment=increment,
4027 minvalue=minvalue,
4028 maxvalue=maxvalue,
4029 nominvalue=nominvalue,
4030 nomaxvalue=nomaxvalue,
4031 cycle=cycle,
4032 cache=cache,
4033 order=order,
4034 **dialect_kw,
4035 )
4036 self.column = None
4037 self.name = quoted_name(name, quote)
4038 self.optional = optional
4039 if schema is BLANK_SCHEMA:
4040 self.schema = schema = None
4041 elif metadata is not None and schema is None and metadata.schema:
4042 self.schema = schema = metadata.schema
4043 else:
4044 self.schema = quoted_name.construct(schema, quote_schema)
4045 self._key = _get_table_key(name, schema)
4046 if data_type is not None:
4047 self.data_type = to_instance(data_type)
4048 else:
4049 self.data_type = None
4051 if metadata:
4052 self._set_metadata(metadata)
4053 else:
4054 self.metadata = None
4056 @util.preload_module("sqlalchemy.sql.functions")
4057 def next_value(self) -> Function[int]:
4058 """Return a :class:`.next_value` function element
4059 which will render the appropriate increment function
4060 for this :class:`.Sequence` within any SQL expression.
4062 """
4063 return util.preloaded.sql_functions.func.next_value(self)
4065 def _copy(self) -> Sequence:
4066 return Sequence(
4067 name=self.name,
4068 schema=self.schema,
4069 data_type=self.data_type,
4070 optional=self.optional,
4071 metadata=self.metadata,
4072 for_update=self.for_update,
4073 **self._as_dict(),
4074 **self.dialect_kwargs,
4075 )
4077 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4078 assert isinstance(parent, Column)
4079 super()._set_parent(parent, **kw)
4080 parent._on_table_attach(self._set_table)
4082 def _set_table(self, column: Column[Any], table: Table) -> None:
4083 self._set_metadata(table.metadata)
4085 def _set_metadata(self, metadata: MetaData) -> None:
4086 self.metadata = metadata
4087 self.metadata._register_object(self)
4088 metadata._sequences[self._key] = self
4090 def create(
4091 self,
4092 bind: _CreateDropBind,
4093 checkfirst: Union[bool, CheckFirst] = CheckFirst.SEQUENCES,
4094 ) -> None:
4095 """Creates this sequence in the database."""
4097 bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
4099 def drop(
4100 self,
4101 bind: _CreateDropBind,
4102 checkfirst: Union[bool, CheckFirst] = CheckFirst.SEQUENCES,
4103 ) -> None:
4104 """Drops this sequence from the database."""
4106 bind._run_ddl_visitor(ddl.SchemaDropper, self, checkfirst=checkfirst)
4108 def _not_a_column_expr(self) -> NoReturn:
4109 raise exc.InvalidRequestError(
4110 f"This {self.__class__.__name__} cannot be used directly "
4111 "as a column expression. Use func.next_value(sequence) "
4112 "to produce a 'next value' function that's usable "
4113 "as a column element."
4114 )
4117@inspection._self_inspects
4118class FetchedValue(SchemaEventTarget):
4119 """A marker for a transparent database-side default.
4121 Use :class:`.FetchedValue` when the database is configured
4122 to provide some automatic default for a column.
4124 E.g.::
4126 Column("foo", Integer, FetchedValue())
4128 Would indicate that some trigger or default generator
4129 will create a new value for the ``foo`` column during an
4130 INSERT.
4132 .. seealso::
4134 :ref:`triggered_columns`
4136 """
4138 is_server_default = True
4139 reflected = False
4140 has_argument = False
4141 is_clause_element = False
4142 is_identity = False
4144 column: Optional[Column[Any]]
4146 def __init__(self, for_update: bool = False) -> None:
4147 self.for_update = for_update
4149 def _as_for_update(self, for_update: bool) -> FetchedValue:
4150 if for_update == self.for_update:
4151 return self
4152 else:
4153 return self._clone(for_update)
4155 def _copy(self) -> FetchedValue:
4156 return FetchedValue(self.for_update)
4158 def _clone(self, for_update: bool) -> Self:
4159 n = self.__class__.__new__(self.__class__)
4160 n.__dict__.update(self.__dict__)
4161 n.__dict__.pop("column", None)
4162 n.for_update = for_update
4163 return n
4165 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4166 column = parent
4167 assert isinstance(column, Column)
4168 self.column = column
4169 if self.for_update:
4170 self.column.server_onupdate = self
4171 else:
4172 self.column.server_default = self
4174 def __repr__(self) -> str:
4175 return util.generic_repr(self)
4178class DefaultClause(FetchedValue):
4179 """A DDL-specified DEFAULT column value.
4181 :class:`.DefaultClause` is a :class:`.FetchedValue`
4182 that also generates a "DEFAULT" clause when
4183 "CREATE TABLE" is emitted.
4185 :class:`.DefaultClause` is generated automatically
4186 whenever the ``server_default``, ``server_onupdate`` arguments of
4187 :class:`_schema.Column` are used. A :class:`.DefaultClause`
4188 can be passed positionally as well.
4190 For example, the following::
4192 Column("foo", Integer, server_default="50")
4194 Is equivalent to::
4196 Column("foo", Integer, DefaultClause("50"))
4198 """
4200 has_argument = True
4202 def __init__(
4203 self,
4204 arg: Union[str, ClauseElement, TextClause],
4205 for_update: bool = False,
4206 _reflected: bool = False,
4207 ) -> None:
4208 util.assert_arg_type(arg, (str, ClauseElement, TextClause), "arg")
4209 super().__init__(for_update)
4210 self.arg = arg
4211 self.reflected = _reflected
4213 def _copy(self) -> DefaultClause:
4214 return DefaultClause(
4215 arg=self.arg, for_update=self.for_update, _reflected=self.reflected
4216 )
4218 def __repr__(self) -> str:
4219 return "DefaultClause(%r, for_update=%r)" % (self.arg, self.for_update)
4222class Constraint(DialectKWArgs, HasConditionalDDL, SchemaItem):
4223 """A table-level SQL constraint.
4225 :class:`_schema.Constraint` serves as the base class for the series of
4226 constraint objects that can be associated with :class:`_schema.Table`
4227 objects, including :class:`_schema.PrimaryKeyConstraint`,
4228 :class:`_schema.ForeignKeyConstraint`
4229 :class:`_schema.UniqueConstraint`, and
4230 :class:`_schema.CheckConstraint`.
4232 """
4234 __visit_name__ = "constraint"
4236 _creation_order: int
4237 _column_flag: bool
4239 def __init__(
4240 self,
4241 name: _ConstraintNameArgument = None,
4242 deferrable: Optional[bool] = None,
4243 initially: Optional[str] = None,
4244 info: Optional[_InfoType] = None,
4245 comment: Optional[str] = None,
4246 _create_rule: Optional[Any] = None,
4247 _type_bound: bool = False,
4248 **dialect_kw: Any,
4249 ) -> None:
4250 r"""Create a SQL constraint.
4252 :param name:
4253 Optional, the in-database name of this ``Constraint``.
4255 :param deferrable:
4256 Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
4257 issuing DDL for this constraint.
4259 :param initially:
4260 Optional string. If set, emit INITIALLY <value> when issuing DDL
4261 for this constraint.
4263 :param info: Optional data dictionary which will be populated into the
4264 :attr:`.SchemaItem.info` attribute of this object.
4266 :param comment: Optional string that will render an SQL comment on
4267 foreign key constraint creation.
4269 .. versionadded:: 2.0
4271 :param \**dialect_kw: Additional keyword arguments are dialect
4272 specific, and passed in the form ``<dialectname>_<argname>``. See
4273 the documentation regarding an individual dialect at
4274 :ref:`dialect_toplevel` for detail on documented arguments.
4276 :param _create_rule:
4277 used internally by some datatypes that also create constraints.
4279 :param _type_bound:
4280 used internally to indicate that this constraint is associated with
4281 a specific datatype.
4283 """
4285 self.name = name
4286 self.deferrable = deferrable
4287 self.initially = initially
4288 if info:
4289 self.info = info
4290 self._create_rule = _create_rule
4291 self._type_bound = _type_bound
4292 util.set_creation_order(self)
4293 self._validate_dialect_kwargs(dialect_kw)
4294 self.comment = comment
4296 def _should_create_for_compiler(
4297 self, compiler: DDLCompiler, **kw: Any
4298 ) -> bool:
4299 if self._create_rule is not None and not self._create_rule(compiler):
4300 return False
4301 elif self._ddl_if is not None:
4302 return self._ddl_if._should_execute(
4303 ddl.CreateConstraint(self), self, None, compiler=compiler, **kw
4304 )
4305 else:
4306 return True
4308 @property
4309 def table(self) -> Table:
4310 try:
4311 if isinstance(self.parent, Table):
4312 return self.parent
4313 except AttributeError:
4314 pass
4315 raise exc.InvalidRequestError(
4316 "This constraint is not bound to a table. Did you "
4317 "mean to call table.append_constraint(constraint) ?"
4318 )
4320 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4321 assert isinstance(parent, (Table, Column))
4322 self.parent = parent
4323 parent.constraints.add(self)
4325 @util.deprecated(
4326 "1.4",
4327 "The :meth:`_schema.Constraint.copy` method is deprecated "
4328 "and will be removed in a future release.",
4329 )
4330 def copy(self, **kw: Any) -> Self:
4331 return self._copy(**kw)
4333 def _copy(self, **kw: Any) -> Self:
4334 raise NotImplementedError()
4337class ColumnCollectionMixin:
4338 """A :class:`_expression.ColumnCollection` of :class:`_schema.Column`
4339 objects.
4341 This collection represents the columns which are referred to by
4342 this object.
4344 """
4346 _columns: DedupeColumnCollection[Column[Any]]
4348 _allow_multiple_tables = False
4350 _pending_colargs: List[Optional[Union[str, Column[Any]]]]
4352 if TYPE_CHECKING:
4354 def _set_parent_with_dispatch(
4355 self, parent: SchemaEventTarget, **kw: Any
4356 ) -> None: ...
4358 def __init__(
4359 self,
4360 *columns: _DDLColumnArgument,
4361 _autoattach: bool = True,
4362 _column_flag: bool = False,
4363 _gather_expressions: Optional[
4364 List[Union[str, ColumnElement[Any]]]
4365 ] = None,
4366 ) -> None:
4367 self._column_flag = _column_flag
4368 self._columns = DedupeColumnCollection()
4370 processed_expressions: Optional[
4371 List[Union[ColumnElement[Any], str]]
4372 ] = _gather_expressions
4374 if processed_expressions is not None:
4376 # this is expected to be an empty list
4377 assert not processed_expressions
4379 self._pending_colargs = []
4380 for (
4381 expr,
4382 _,
4383 _,
4384 add_element,
4385 ) in coercions.expect_col_expression_collection(
4386 roles.DDLConstraintColumnRole, columns
4387 ):
4388 self._pending_colargs.append(add_element)
4389 processed_expressions.append(expr)
4390 else:
4391 self._pending_colargs = [
4392 coercions.expect(roles.DDLConstraintColumnRole, column)
4393 for column in columns
4394 ]
4396 if _autoattach and self._pending_colargs:
4397 self._check_attach()
4399 def _check_attach(self, evt: bool = False) -> None:
4400 col_objs = [c for c in self._pending_colargs if isinstance(c, Column)]
4402 cols_w_table = [c for c in col_objs if isinstance(c.table, Table)]
4404 cols_wo_table = set(col_objs).difference(cols_w_table)
4405 if cols_wo_table:
4406 # feature #3341 - place event listeners for Column objects
4407 # such that when all those cols are attached, we autoattach.
4408 assert not evt, "Should not reach here on event call"
4410 # issue #3411 - don't do the per-column auto-attach if some of the
4411 # columns are specified as strings.
4412 has_string_cols = {
4413 c for c in self._pending_colargs if c is not None
4414 }.difference(col_objs)
4415 if not has_string_cols:
4417 def _col_attached(column: Column[Any], table: Table) -> None:
4418 # this isinstance() corresponds with the
4419 # isinstance() above; only want to count Table-bound
4420 # columns
4421 if isinstance(table, Table):
4422 cols_wo_table.discard(column)
4423 if not cols_wo_table:
4424 self._check_attach(evt=True)
4426 self._cols_wo_table = cols_wo_table
4427 for col in cols_wo_table:
4428 col._on_table_attach(_col_attached)
4429 return
4431 columns = cols_w_table
4433 tables = {c.table for c in columns}
4434 if len(tables) == 1:
4435 self._set_parent_with_dispatch(tables.pop())
4436 elif len(tables) > 1 and not self._allow_multiple_tables:
4437 table = columns[0].table
4438 others = [c for c in columns[1:] if c.table is not table]
4439 if others:
4440 # black could not format this inline
4441 other_str = ", ".join("'%s'" % c for c in others)
4442 raise exc.ArgumentError(
4443 f"Column(s) {other_str} "
4444 f"are not part of table '{table.description}'."
4445 )
4447 @util.ro_memoized_property
4448 def columns(self) -> ReadOnlyColumnCollection[str, Column[Any]]:
4449 return self._columns.as_readonly()
4451 @util.ro_memoized_property
4452 def c(self) -> ReadOnlyColumnCollection[str, Column[Any]]:
4453 return self._columns.as_readonly()
4455 def _col_expressions(
4456 self, parent: Union[Table, Column[Any]]
4457 ) -> List[Optional[Column[Any]]]:
4458 if isinstance(parent, Column):
4459 result: List[Optional[Column[Any]]] = [
4460 c for c in self._pending_colargs if isinstance(c, Column)
4461 ]
4462 assert len(result) == len(self._pending_colargs)
4463 return result
4464 else:
4465 try:
4466 return [
4467 parent.c[col] if isinstance(col, str) else col
4468 for col in self._pending_colargs
4469 ]
4470 except KeyError as ke:
4471 raise exc.ConstraintColumnNotFoundError(
4472 f"Can't create {self.__class__.__name__} "
4473 f"on table '{parent.description}': no column "
4474 f"named '{ke.args[0]}' is present."
4475 ) from ke
4477 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4478 assert isinstance(parent, (Table, Column))
4480 for col in self._col_expressions(parent):
4481 if col is not None:
4482 self._columns.add(col)
4485class ColumnCollectionConstraint(ColumnCollectionMixin, Constraint):
4486 """A constraint that proxies a ColumnCollection."""
4488 def __init__(
4489 self,
4490 *columns: _DDLColumnArgument,
4491 name: _ConstraintNameArgument = None,
4492 deferrable: Optional[bool] = None,
4493 initially: Optional[str] = None,
4494 info: Optional[_InfoType] = None,
4495 _autoattach: bool = True,
4496 _column_flag: bool = False,
4497 _gather_expressions: Optional[List[_DDLColumnArgument]] = None,
4498 **dialect_kw: Any,
4499 ) -> None:
4500 r"""
4501 :param \*columns:
4502 A sequence of column names or Column objects.
4504 :param name:
4505 Optional, the in-database name of this constraint.
4507 :param deferrable:
4508 Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
4509 issuing DDL for this constraint.
4511 :param initially:
4512 Optional string. If set, emit INITIALLY <value> when issuing DDL
4513 for this constraint.
4515 :param \**dialect_kw: other keyword arguments including
4516 dialect-specific arguments are propagated to the :class:`.Constraint`
4517 superclass.
4519 """
4520 Constraint.__init__(
4521 self,
4522 name=name,
4523 deferrable=deferrable,
4524 initially=initially,
4525 info=info,
4526 **dialect_kw,
4527 )
4528 ColumnCollectionMixin.__init__(
4529 self, *columns, _autoattach=_autoattach, _column_flag=_column_flag
4530 )
4532 columns: ReadOnlyColumnCollection[str, Column[Any]]
4533 """A :class:`_expression.ColumnCollection` representing the set of columns
4534 for this constraint.
4536 """
4538 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4539 assert isinstance(parent, (Column, Table))
4540 Constraint._set_parent(self, parent)
4541 ColumnCollectionMixin._set_parent(self, parent)
4543 def __contains__(self, x: Any) -> bool:
4544 return x in self._columns
4546 @util.deprecated(
4547 "1.4",
4548 "The :meth:`_schema.ColumnCollectionConstraint.copy` method "
4549 "is deprecated and will be removed in a future release.",
4550 )
4551 def copy(
4552 self,
4553 *,
4554 target_table: Optional[Table] = None,
4555 **kw: Any,
4556 ) -> ColumnCollectionConstraint:
4557 return self._copy(target_table=target_table, **kw)
4559 def _copy(
4560 self,
4561 *,
4562 target_table: Optional[Table] = None,
4563 **kw: Any,
4564 ) -> ColumnCollectionConstraint:
4565 # ticket #5276
4566 constraint_kwargs = {}
4567 for dialect_name in self.dialect_options:
4568 dialect_options = self.dialect_options[dialect_name]._non_defaults
4569 for (
4570 dialect_option_key,
4571 dialect_option_value,
4572 ) in dialect_options.items():
4573 constraint_kwargs[dialect_name + "_" + dialect_option_key] = (
4574 dialect_option_value
4575 )
4577 assert isinstance(self.parent, Table)
4578 c = self.__class__(
4579 name=self.name,
4580 deferrable=self.deferrable,
4581 initially=self.initially,
4582 *[
4583 _copy_expression(expr, self.parent, target_table)
4584 for expr in self._columns
4585 ],
4586 comment=self.comment,
4587 **constraint_kwargs,
4588 )
4589 return self._schema_item_copy(c)
4591 def contains_column(self, col: Column[Any]) -> bool:
4592 """Return True if this constraint contains the given column.
4594 Note that this object also contains an attribute ``.columns``
4595 which is a :class:`_expression.ColumnCollection` of
4596 :class:`_schema.Column` objects.
4598 """
4600 return self._columns.contains_column(col)
4602 def __iter__(self) -> Iterator[Column[Any]]:
4603 return iter(self._columns)
4605 def __len__(self) -> int:
4606 return len(self._columns)
4609class CheckConstraint(ColumnCollectionConstraint):
4610 """A table- or column-level CHECK constraint.
4612 Can be included in the definition of a Table or Column.
4613 """
4615 _allow_multiple_tables = True
4617 __visit_name__ = "table_or_column_check_constraint"
4619 @_document_text_coercion(
4620 "sqltext",
4621 ":class:`.CheckConstraint`",
4622 ":paramref:`.CheckConstraint.sqltext`",
4623 )
4624 def __init__(
4625 self,
4626 sqltext: _TextCoercedExpressionArgument[Any],
4627 name: _ConstraintNameArgument = None,
4628 deferrable: Optional[bool] = None,
4629 initially: Optional[str] = None,
4630 table: Optional[Table] = None,
4631 info: Optional[_InfoType] = None,
4632 _create_rule: Optional[Any] = None,
4633 _autoattach: bool = True,
4634 _type_bound: bool = False,
4635 **dialect_kw: Any,
4636 ) -> None:
4637 r"""Construct a CHECK constraint.
4639 :param sqltext:
4640 A string containing the constraint definition, which will be used
4641 verbatim, or a SQL expression construct. If given as a string,
4642 the object is converted to a :func:`_expression.text` object.
4643 If the textual
4644 string includes a colon character, escape this using a backslash::
4646 CheckConstraint(r"foo ~ E'a(?\:b|c)d")
4648 :param name:
4649 Optional, the in-database name of the constraint.
4651 :param deferrable:
4652 Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
4653 issuing DDL for this constraint.
4655 :param initially:
4656 Optional string. If set, emit INITIALLY <value> when issuing DDL
4657 for this constraint.
4659 :param info: Optional data dictionary which will be populated into the
4660 :attr:`.SchemaItem.info` attribute of this object.
4662 """
4664 self.sqltext = coercions.expect(roles.DDLExpressionRole, sqltext)
4665 columns: List[Column[Any]] = []
4666 visitors.traverse(self.sqltext, {}, {"column": columns.append})
4668 super().__init__(
4669 name=name,
4670 deferrable=deferrable,
4671 initially=initially,
4672 _create_rule=_create_rule,
4673 info=info,
4674 _type_bound=_type_bound,
4675 _autoattach=_autoattach,
4676 *columns,
4677 **dialect_kw,
4678 )
4679 if table is not None:
4680 self._set_parent_with_dispatch(table)
4682 @property
4683 def is_column_level(self) -> bool:
4684 return not isinstance(self.parent, Table)
4686 @util.deprecated(
4687 "1.4",
4688 "The :meth:`_schema.CheckConstraint.copy` method is deprecated "
4689 "and will be removed in a future release.",
4690 )
4691 def copy(
4692 self, *, target_table: Optional[Table] = None, **kw: Any
4693 ) -> CheckConstraint:
4694 return self._copy(target_table=target_table, **kw)
4696 def _copy(
4697 self, *, target_table: Optional[Table] = None, **kw: Any
4698 ) -> CheckConstraint:
4699 if target_table is not None:
4700 # note that target_table is None for the copy process of
4701 # a column-bound CheckConstraint, so this path is not reached
4702 # in that case.
4703 sqltext = _copy_expression(self.sqltext, self.table, target_table)
4704 else:
4705 sqltext = self.sqltext
4706 c = CheckConstraint(
4707 sqltext,
4708 name=self.name,
4709 initially=self.initially,
4710 deferrable=self.deferrable,
4711 _create_rule=self._create_rule,
4712 table=target_table,
4713 comment=self.comment,
4714 _autoattach=False,
4715 _type_bound=self._type_bound,
4716 )
4717 return self._schema_item_copy(c)
4720class ForeignKeyConstraint(ColumnCollectionConstraint):
4721 """A table-level FOREIGN KEY constraint.
4723 Defines a single column or composite FOREIGN KEY ... REFERENCES
4724 constraint. For a no-frills, single column foreign key, adding a
4725 :class:`_schema.ForeignKey` to the definition of a :class:`_schema.Column`
4726 is a
4727 shorthand equivalent for an unnamed, single column
4728 :class:`_schema.ForeignKeyConstraint`.
4730 Examples of foreign key configuration are in :ref:`metadata_foreignkeys`.
4732 """
4734 __visit_name__ = "foreign_key_constraint"
4736 def __init__(
4737 self,
4738 columns: _typing_Sequence[_DDLColumnArgument],
4739 refcolumns: _typing_Sequence[_DDLColumnReferenceArgument],
4740 name: _ConstraintNameArgument = None,
4741 onupdate: Optional[str] = None,
4742 ondelete: Optional[str] = None,
4743 deferrable: Optional[bool] = None,
4744 initially: Optional[str] = None,
4745 use_alter: bool = False,
4746 link_to_name: bool = False,
4747 match: Optional[str] = None,
4748 table: Optional[Table] = None,
4749 info: Optional[_InfoType] = None,
4750 comment: Optional[str] = None,
4751 **dialect_kw: Any,
4752 ) -> None:
4753 r"""Construct a composite-capable FOREIGN KEY.
4755 :param columns: A sequence of local column names. The named columns
4756 must be defined and present in the parent Table. The names should
4757 match the ``key`` given to each column (defaults to the name) unless
4758 ``link_to_name`` is True.
4760 :param refcolumns: A sequence of foreign column names or Column
4761 objects. The columns must all be located within the same Table.
4763 :param name: Optional, the in-database name of the key.
4765 :param onupdate: Optional string. If set, emit ON UPDATE <value> when
4766 issuing DDL for this constraint. Typical values include CASCADE,
4767 DELETE and RESTRICT.
4769 .. seealso::
4771 :ref:`on_update_on_delete`
4773 :param ondelete: Optional string. If set, emit ON DELETE <value> when
4774 issuing DDL for this constraint. Typical values include CASCADE,
4775 SET NULL and RESTRICT. Some dialects may allow for additional
4776 syntaxes.
4778 .. seealso::
4780 :ref:`on_update_on_delete`
4782 :param deferrable: Optional bool. If set, emit DEFERRABLE or NOT
4783 DEFERRABLE when issuing DDL for this constraint.
4785 :param initially: Optional string. If set, emit INITIALLY <value> when
4786 issuing DDL for this constraint.
4788 :param link_to_name: if True, the string name given in ``column`` is
4789 the rendered name of the referenced column, not its locally assigned
4790 ``key``.
4792 :param use_alter: If True, do not emit the DDL for this constraint as
4793 part of the CREATE TABLE definition. Instead, generate it via an
4794 ALTER TABLE statement issued after the full collection of tables
4795 have been created, and drop it via an ALTER TABLE statement before
4796 the full collection of tables are dropped.
4798 The use of :paramref:`_schema.ForeignKeyConstraint.use_alter` is
4799 particularly geared towards the case where two or more tables
4800 are established within a mutually-dependent foreign key constraint
4801 relationship; however, the :meth:`_schema.MetaData.create_all` and
4802 :meth:`_schema.MetaData.drop_all`
4803 methods will perform this resolution
4804 automatically, so the flag is normally not needed.
4806 .. seealso::
4808 :ref:`use_alter`
4810 :param match: Optional string. If set, emit MATCH <value> when issuing
4811 DDL for this constraint. Typical values include SIMPLE, PARTIAL
4812 and FULL.
4814 :param info: Optional data dictionary which will be populated into the
4815 :attr:`.SchemaItem.info` attribute of this object.
4817 :param comment: Optional string that will render an SQL comment on
4818 foreign key constraint creation.
4820 .. versionadded:: 2.0
4822 :param \**dialect_kw: Additional keyword arguments are dialect
4823 specific, and passed in the form ``<dialectname>_<argname>``. See
4824 the documentation regarding an individual dialect at
4825 :ref:`dialect_toplevel` for detail on documented arguments.
4827 """
4829 Constraint.__init__(
4830 self,
4831 name=name,
4832 deferrable=deferrable,
4833 initially=initially,
4834 info=info,
4835 comment=comment,
4836 **dialect_kw,
4837 )
4838 self.onupdate = onupdate
4839 self.ondelete = ondelete
4840 self.link_to_name = link_to_name
4841 self.use_alter = use_alter
4842 self.match = match
4844 if len(set(columns)) != len(refcolumns):
4845 if len(set(columns)) != len(columns):
4846 # e.g. FOREIGN KEY (a, a) REFERENCES r (b, c)
4847 raise exc.ArgumentError(
4848 "ForeignKeyConstraint with duplicate source column "
4849 "references are not supported."
4850 )
4851 else:
4852 # e.g. FOREIGN KEY (a) REFERENCES r (b, c)
4853 # paraphrasing
4854 # https://www.postgresql.org/docs/current/static/ddl-constraints.html
4855 raise exc.ArgumentError(
4856 "ForeignKeyConstraint number "
4857 "of constrained columns must match the number of "
4858 "referenced columns."
4859 )
4861 # standalone ForeignKeyConstraint - create
4862 # associated ForeignKey objects which will be applied to hosted
4863 # Column objects (in col.foreign_keys), either now or when attached
4864 # to the Table for string-specified names
4865 self.elements = [
4866 ForeignKey(
4867 refcol,
4868 _constraint=self,
4869 name=self.name,
4870 onupdate=self.onupdate,
4871 ondelete=self.ondelete,
4872 use_alter=self.use_alter,
4873 link_to_name=self.link_to_name,
4874 match=self.match,
4875 deferrable=self.deferrable,
4876 initially=self.initially,
4877 **self.dialect_kwargs,
4878 )
4879 for refcol in refcolumns
4880 ]
4882 ColumnCollectionMixin.__init__(self, *columns)
4883 if table is not None:
4884 if hasattr(self, "parent"):
4885 assert table is self.parent
4886 self._set_parent_with_dispatch(table)
4888 def _append_element(self, column: Column[Any], fk: ForeignKey) -> None:
4889 self._columns.add(column)
4890 self.elements.append(fk)
4892 columns: ReadOnlyColumnCollection[str, Column[Any]]
4893 """A :class:`_expression.ColumnCollection` representing the set of columns
4894 for this constraint.
4896 """
4898 elements: List[ForeignKey]
4899 """A sequence of :class:`_schema.ForeignKey` objects.
4901 Each :class:`_schema.ForeignKey`
4902 represents a single referring column/referred
4903 column pair.
4905 This collection is intended to be read-only.
4907 """
4909 @property
4910 def _elements(self) -> util.OrderedDict[str, ForeignKey]:
4911 # legacy - provide a dictionary view of (column_key, fk)
4912 return util.OrderedDict(zip(self.column_keys, self.elements))
4914 @property
4915 def _referred_schema(self) -> Optional[str]:
4916 for elem in self.elements:
4917 return elem._referred_schema
4918 else:
4919 return None
4921 @property
4922 def referred_table(self) -> Table:
4923 """The :class:`_schema.Table` object to which this
4924 :class:`_schema.ForeignKeyConstraint` references.
4926 This is a dynamically calculated attribute which may not be available
4927 if the constraint and/or parent table is not yet associated with
4928 a metadata collection that contains the referred table.
4930 """
4931 return self.elements[0].column.table
4933 def _validate_dest_table(self, table: Table) -> None:
4934 table_keys = {
4935 elem._table_key_within_construction() for elem in self.elements
4936 }
4937 if None not in table_keys and len(table_keys) > 1:
4938 elem0, elem1 = sorted(table_keys)[0:2]
4939 raise exc.ArgumentError(
4940 f"ForeignKeyConstraint on "
4941 f"{table.fullname}({self._col_description}) refers to "
4942 f"multiple remote tables: {elem0} and {elem1}"
4943 )
4945 @property
4946 def column_keys(self) -> _typing_Sequence[str]:
4947 """Return a list of string keys representing the local
4948 columns in this :class:`_schema.ForeignKeyConstraint`.
4950 This list is either the original string arguments sent
4951 to the constructor of the :class:`_schema.ForeignKeyConstraint`,
4952 or if the constraint has been initialized with :class:`_schema.Column`
4953 objects, is the string ``.key`` of each element.
4955 """
4956 if hasattr(self, "parent"):
4957 return self._columns.keys()
4958 else:
4959 return [
4960 col.key if isinstance(col, ColumnElement) else str(col)
4961 for col in self._pending_colargs
4962 ]
4964 @property
4965 def _col_description(self) -> str:
4966 return ", ".join(self.column_keys)
4968 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4969 table = parent
4970 assert isinstance(table, Table)
4971 Constraint._set_parent(self, table)
4973 ColumnCollectionConstraint._set_parent(self, table)
4975 for col, fk in zip(self._columns, self.elements):
4976 if not hasattr(fk, "parent") or fk.parent is not col:
4977 fk._set_parent_with_dispatch(col)
4979 self._validate_dest_table(table)
4981 @util.deprecated(
4982 "1.4",
4983 "The :meth:`_schema.ForeignKeyConstraint.copy` method is deprecated "
4984 "and will be removed in a future release.",
4985 )
4986 def copy(
4987 self,
4988 *,
4989 schema: Optional[str] = None,
4990 target_table: Optional[Table] = None,
4991 **kw: Any,
4992 ) -> ForeignKeyConstraint:
4993 return self._copy(schema=schema, target_table=target_table, **kw)
4995 def _copy(
4996 self,
4997 *,
4998 schema: Optional[str] = None,
4999 target_table: Optional[Table] = None,
5000 **kw: Any,
5001 ) -> ForeignKeyConstraint:
5002 fkc = ForeignKeyConstraint(
5003 [x.parent.key for x in self.elements],
5004 [
5005 x._get_colspec(
5006 schema=schema,
5007 table_name=(
5008 target_table.name
5009 if target_table is not None
5010 and x._table_key_within_construction()
5011 == x.parent.table.key
5012 else None
5013 ),
5014 _is_copy=True,
5015 )
5016 for x in self.elements
5017 ],
5018 name=self.name,
5019 onupdate=self.onupdate,
5020 ondelete=self.ondelete,
5021 use_alter=self.use_alter,
5022 deferrable=self.deferrable,
5023 initially=self.initially,
5024 link_to_name=self.link_to_name,
5025 match=self.match,
5026 comment=self.comment,
5027 )
5028 for self_fk, other_fk in zip(self.elements, fkc.elements):
5029 self_fk._schema_item_copy(other_fk)
5030 return self._schema_item_copy(fkc)
5033class PrimaryKeyConstraint(ColumnCollectionConstraint):
5034 """A table-level PRIMARY KEY constraint.
5036 The :class:`.PrimaryKeyConstraint` object is present automatically
5037 on any :class:`_schema.Table` object; it is assigned a set of
5038 :class:`_schema.Column` objects corresponding to those marked with
5039 the :paramref:`_schema.Column.primary_key` flag::
5041 >>> my_table = Table(
5042 ... "mytable",
5043 ... metadata,
5044 ... Column("id", Integer, primary_key=True),
5045 ... Column("version_id", Integer, primary_key=True),
5046 ... Column("data", String(50)),
5047 ... )
5048 >>> my_table.primary_key
5049 PrimaryKeyConstraint(
5050 Column('id', Integer(), table=<mytable>,
5051 primary_key=True, nullable=False),
5052 Column('version_id', Integer(), table=<mytable>,
5053 primary_key=True, nullable=False)
5054 )
5056 The primary key of a :class:`_schema.Table` can also be specified by using
5057 a :class:`.PrimaryKeyConstraint` object explicitly; in this mode of usage,
5058 the "name" of the constraint can also be specified, as well as other
5059 options which may be recognized by dialects::
5061 my_table = Table(
5062 "mytable",
5063 metadata,
5064 Column("id", Integer),
5065 Column("version_id", Integer),
5066 Column("data", String(50)),
5067 PrimaryKeyConstraint("id", "version_id", name="mytable_pk"),
5068 )
5070 The two styles of column-specification should generally not be mixed.
5071 An warning is emitted if the columns present in the
5072 :class:`.PrimaryKeyConstraint`
5073 don't match the columns that were marked as ``primary_key=True``, if both
5074 are present; in this case, the columns are taken strictly from the
5075 :class:`.PrimaryKeyConstraint` declaration, and those columns otherwise
5076 marked as ``primary_key=True`` are ignored. This behavior is intended to
5077 be backwards compatible with previous behavior.
5079 For the use case where specific options are to be specified on the
5080 :class:`.PrimaryKeyConstraint`, but the usual style of using
5081 ``primary_key=True`` flags is still desirable, an empty
5082 :class:`.PrimaryKeyConstraint` may be specified, which will take on the
5083 primary key column collection from the :class:`_schema.Table` based on the
5084 flags::
5086 my_table = Table(
5087 "mytable",
5088 metadata,
5089 Column("id", Integer, primary_key=True),
5090 Column("version_id", Integer, primary_key=True),
5091 Column("data", String(50)),
5092 PrimaryKeyConstraint(name="mytable_pk", mssql_clustered=True),
5093 )
5095 """
5097 __visit_name__ = "primary_key_constraint"
5099 def __init__(
5100 self,
5101 *columns: _DDLColumnArgument,
5102 name: Optional[str] = None,
5103 deferrable: Optional[bool] = None,
5104 initially: Optional[str] = None,
5105 info: Optional[_InfoType] = None,
5106 _implicit_generated: bool = False,
5107 **dialect_kw: Any,
5108 ) -> None:
5109 self._implicit_generated = _implicit_generated
5110 super().__init__(
5111 *columns,
5112 name=name,
5113 deferrable=deferrable,
5114 initially=initially,
5115 info=info,
5116 **dialect_kw,
5117 )
5119 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
5120 table = parent
5121 assert isinstance(table, Table)
5122 super()._set_parent(table)
5124 if table.primary_key is not self:
5125 table.constraints.discard(table.primary_key)
5126 table.primary_key = self # type: ignore
5127 table.constraints.add(self)
5129 table_pks = [c for c in table.c if c.primary_key]
5130 if (
5131 self._columns
5132 and table_pks
5133 and set(table_pks) != set(self._columns)
5134 ):
5135 # black could not format these inline
5136 table_pk_str = ", ".join("'%s'" % c.name for c in table_pks)
5137 col_str = ", ".join("'%s'" % c.name for c in self._columns)
5139 util.warn(
5140 f"Table '{table.name}' specifies columns "
5141 f"{table_pk_str} as "
5142 f"primary_key=True, "
5143 f"not matching locally specified columns {col_str}; "
5144 f"setting the "
5145 f"current primary key columns to "
5146 f"{col_str}. "
5147 f"This warning "
5148 f"may become an exception in a future release"
5149 )
5150 table_pks[:] = []
5152 for c in self._columns:
5153 c.primary_key = True
5154 if c._user_defined_nullable is NULL_UNSPECIFIED:
5155 c.nullable = False
5156 if table_pks:
5157 self._columns.extend(table_pks)
5159 def _reload(self, columns: Iterable[Column[Any]]) -> None:
5160 """repopulate this :class:`.PrimaryKeyConstraint` given
5161 a set of columns.
5163 Existing columns in the table that are marked as primary_key=True
5164 are maintained.
5166 Also fires a new event.
5168 This is basically like putting a whole new
5169 :class:`.PrimaryKeyConstraint` object on the parent
5170 :class:`_schema.Table` object without actually replacing the object.
5172 The ordering of the given list of columns is also maintained; these
5173 columns will be appended to the list of columns after any which
5174 are already present.
5176 """
5177 # set the primary key flag on new columns.
5178 # note any existing PK cols on the table also have their
5179 # flag still set.
5180 for col in columns:
5181 col.primary_key = True
5183 self._columns.extend(columns)
5185 PrimaryKeyConstraint._autoincrement_column._reset(self) # type: ignore
5186 self._set_parent_with_dispatch(self.table)
5188 def _replace(self, col: Column[Any]) -> None:
5189 PrimaryKeyConstraint._autoincrement_column._reset(self) # type: ignore
5190 self._columns.replace(col)
5192 self.dispatch._sa_event_column_added_to_pk_constraint(self, col)
5194 @property
5195 def columns_autoinc_first(self) -> List[Column[Any]]:
5196 autoinc = self._autoincrement_column
5198 if autoinc is not None:
5199 return [autoinc] + [c for c in self._columns if c is not autoinc]
5200 else:
5201 return list(self._columns)
5203 @util.ro_memoized_property
5204 def _autoincrement_column(self) -> Optional[Column[int]]:
5205 def _validate_autoinc(col: Column[Any], autoinc_true: bool) -> bool:
5206 if col.type._type_affinity is not None and issubclass(
5207 col.type._type_affinity, type_api.NUMERICTYPE._type_affinity
5208 ):
5209 scale = col.type.scale # type: ignore[attr-defined]
5210 if scale != 0 and autoinc_true:
5211 raise exc.ArgumentError(
5212 f"Column type {col.type} with non-zero scale "
5213 f"{scale} on column '{col}' is not "
5214 f"compatible with autoincrement=True"
5215 )
5216 elif not autoinc_true:
5217 return False
5218 elif col.type._type_affinity is None or not issubclass(
5219 col.type._type_affinity, type_api.INTEGERTYPE._type_affinity
5220 ):
5221 if autoinc_true:
5222 raise exc.ArgumentError(
5223 f"Column type {col.type} on column '{col}' is not "
5224 f"compatible with autoincrement=True"
5225 )
5226 else:
5227 return False
5228 elif (
5229 col.default is not None
5230 and not isinstance(col.default, Sequence)
5231 and not autoinc_true
5232 ):
5233 return False
5234 elif (
5235 col.server_default is not None
5236 and not isinstance(col.server_default, Identity)
5237 and not autoinc_true
5238 ):
5239 return False
5240 elif col.foreign_keys and col.autoincrement not in (
5241 True,
5242 "ignore_fk",
5243 ):
5244 return False
5245 return True
5247 if len(self._columns) == 1:
5248 col = list(self._columns)[0]
5250 if col.autoincrement is True:
5251 _validate_autoinc(col, True)
5252 return col
5253 elif col.autoincrement in (
5254 "auto",
5255 "ignore_fk",
5256 ) and _validate_autoinc(col, False):
5257 return col
5258 else:
5259 return None
5261 else:
5262 autoinc = None
5263 for col in self._columns:
5264 if col.autoincrement is True:
5265 _validate_autoinc(col, True)
5266 if autoinc is not None:
5267 raise exc.ArgumentError(
5268 f"Only one Column may be marked "
5269 f"autoincrement=True, found both "
5270 f"{col.name} and {autoinc.name}."
5271 )
5272 else:
5273 autoinc = col
5275 return autoinc
5278class UniqueConstraint(ColumnCollectionConstraint):
5279 """A table-level UNIQUE constraint.
5281 Defines a single column or composite UNIQUE constraint. For a no-frills,
5282 single column constraint, adding ``unique=True`` to the ``Column``
5283 definition is a shorthand equivalent for an unnamed, single column
5284 UniqueConstraint.
5285 """
5287 __visit_name__ = "unique_constraint"
5290class Index(
5291 DialectKWArgs, ColumnCollectionMixin, HasConditionalDDL, SchemaItem
5292):
5293 """A table-level INDEX.
5295 Defines a composite (one or more column) INDEX.
5297 E.g.::
5299 sometable = Table(
5300 "sometable",
5301 metadata,
5302 Column("name", String(50)),
5303 Column("address", String(100)),
5304 )
5306 Index("some_index", sometable.c.name)
5308 For a no-frills, single column index, adding
5309 :class:`_schema.Column` also supports ``index=True``::
5311 sometable = Table(
5312 "sometable", metadata, Column("name", String(50), index=True)
5313 )
5315 For a composite index, multiple columns can be specified::
5317 Index("some_index", sometable.c.name, sometable.c.address)
5319 Functional indexes are supported as well, typically by using the
5320 :data:`.func` construct in conjunction with table-bound
5321 :class:`_schema.Column` objects::
5323 Index("some_index", func.lower(sometable.c.name))
5325 An :class:`.Index` can also be manually associated with a
5326 :class:`_schema.Table`,
5327 either through inline declaration or using
5328 :meth:`_schema.Table.append_constraint`. When this approach is used,
5329 the names
5330 of the indexed columns can be specified as strings::
5332 Table(
5333 "sometable",
5334 metadata,
5335 Column("name", String(50)),
5336 Column("address", String(100)),
5337 Index("some_index", "name", "address"),
5338 )
5340 To support functional or expression-based indexes in this form, the
5341 :func:`_expression.text` construct may be used::
5343 from sqlalchemy import text
5345 Table(
5346 "sometable",
5347 metadata,
5348 Column("name", String(50)),
5349 Column("address", String(100)),
5350 Index("some_index", text("lower(name)")),
5351 )
5353 .. seealso::
5355 :ref:`schema_indexes` - General information on :class:`.Index`.
5357 :ref:`postgresql_indexes` - PostgreSQL-specific options available for
5358 the :class:`.Index` construct.
5360 :ref:`mysql_indexes` - MySQL-specific options available for the
5361 :class:`.Index` construct.
5363 :ref:`mssql_indexes` - MSSQL-specific options available for the
5364 :class:`.Index` construct.
5366 """
5368 __visit_name__ = "index"
5370 table: Optional[Table]
5371 expressions: _typing_Sequence[Union[str, ColumnElement[Any]]]
5372 _table_bound_expressions: _typing_Sequence[ColumnElement[Any]]
5374 def __init__(
5375 self,
5376 name: Optional[str],
5377 *expressions: _DDLColumnArgument,
5378 unique: bool = False,
5379 quote: Optional[bool] = None,
5380 info: Optional[_InfoType] = None,
5381 _table: Optional[Table] = None,
5382 _column_flag: bool = False,
5383 **dialect_kw: Any,
5384 ) -> None:
5385 r"""Construct an index object.
5387 :param name:
5388 The name of the index
5390 :param \*expressions:
5391 Column expressions to include in the index. The expressions
5392 are normally instances of :class:`_schema.Column`, but may also
5393 be arbitrary SQL expressions which ultimately refer to a
5394 :class:`_schema.Column`.
5396 :param unique=False:
5397 Keyword only argument; if True, create a unique index.
5399 :param quote=None:
5400 Keyword only argument; whether to apply quoting to the name of
5401 the index. Works in the same manner as that of
5402 :paramref:`_schema.Column.quote`.
5404 :param info=None: Optional data dictionary which will be populated
5405 into the :attr:`.SchemaItem.info` attribute of this object.
5407 :param \**dialect_kw: Additional keyword arguments not mentioned above
5408 are dialect specific, and passed in the form
5409 ``<dialectname>_<argname>``. See the documentation regarding an
5410 individual dialect at :ref:`dialect_toplevel` for detail on
5411 documented arguments.
5413 """
5414 self.table = table = None
5416 self.name = quoted_name.construct(name, quote)
5417 self.unique = unique
5418 if info is not None:
5419 self.info = info
5421 # TODO: consider "table" argument being public, but for
5422 # the purpose of the fix here, it starts as private.
5423 if _table is not None:
5424 table = _table
5426 self._validate_dialect_kwargs(dialect_kw)
5428 self.expressions = []
5429 # will call _set_parent() if table-bound column
5430 # objects are present
5431 ColumnCollectionMixin.__init__(
5432 self,
5433 *expressions,
5434 _column_flag=_column_flag,
5435 _gather_expressions=self.expressions,
5436 )
5437 if table is not None:
5438 self._set_parent(table)
5440 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
5441 table = parent
5442 assert isinstance(table, Table)
5443 ColumnCollectionMixin._set_parent(self, table)
5445 if self.table is not None and table is not self.table:
5446 raise exc.ArgumentError(
5447 f"Index '{self.name}' is against table "
5448 f"'{self.table.description}', and "
5449 f"cannot be associated with table '{table.description}'."
5450 )
5451 self.table = table
5452 table.indexes.add(self)
5454 expressions = self.expressions
5455 col_expressions = self._col_expressions(table)
5456 assert len(expressions) == len(col_expressions)
5458 exprs = []
5459 for expr, colexpr in zip(expressions, col_expressions):
5460 if isinstance(expr, ClauseElement):
5461 exprs.append(expr)
5462 elif colexpr is not None:
5463 exprs.append(colexpr)
5464 else:
5465 assert False
5466 self.expressions = self._table_bound_expressions = exprs
5468 def create(
5469 self,
5470 bind: _CreateDropBind,
5471 checkfirst: Union[bool, CheckFirst] = CheckFirst.NONE,
5472 ) -> None:
5473 """Issue a ``CREATE`` statement for this
5474 :class:`.Index`, using the given
5475 :class:`.Connection` or :class:`.Engine`` for connectivity.
5477 .. seealso::
5479 :meth:`_schema.MetaData.create_all`.
5481 """
5482 bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
5484 def drop(
5485 self,
5486 bind: _CreateDropBind,
5487 checkfirst: Union[bool, CheckFirst] = CheckFirst.NONE,
5488 ) -> None:
5489 """Issue a ``DROP`` statement for this
5490 :class:`.Index`, using the given
5491 :class:`.Connection` or :class:`.Engine` for connectivity.
5493 .. seealso::
5495 :meth:`_schema.MetaData.drop_all`.
5497 """
5498 bind._run_ddl_visitor(ddl.SchemaDropper, self, checkfirst=checkfirst)
5500 def __repr__(self) -> str:
5501 exprs: _typing_Sequence[Any] # noqa: F842
5503 return "Index(%s)" % (
5504 ", ".join(
5505 [repr(self.name)]
5506 + [repr(e) for e in self.expressions]
5507 + (self.unique and ["unique=True"] or [])
5508 )
5509 )
5512_NamingSchemaCallable = Callable[[Constraint, Table], str]
5513_NamingSchemaDirective = Union[str, _NamingSchemaCallable]
5516class _NamingSchemaTD(TypedDict, total=False):
5517 fk: _NamingSchemaDirective
5518 pk: _NamingSchemaDirective
5519 ix: _NamingSchemaDirective
5520 ck: _NamingSchemaDirective
5521 uq: _NamingSchemaDirective
5524_NamingSchemaParameter = Union[
5525 # it seems like the TypedDict here is useful for pylance typeahead,
5526 # and not much else
5527 _NamingSchemaTD,
5528 # there is no form that allows Union[Type[Any], str] to work in all
5529 # cases, including breaking out Mapping[] entries for each combination
5530 # even, therefore keys must be `Any` (see #10264)
5531 Mapping[Any, _NamingSchemaDirective],
5532]
5535DEFAULT_NAMING_CONVENTION: _NamingSchemaParameter = util.immutabledict(
5536 {"ix": "ix_%(column_0_label)s"}
5537)
5540class MetaData(HasSchemaAttr):
5541 """A collection of :class:`_schema.Table`
5542 objects and their associated schema
5543 constructs.
5545 Holds a collection of :class:`_schema.Table` objects as well as
5546 an optional binding to an :class:`_engine.Engine` or
5547 :class:`_engine.Connection`. If bound, the :class:`_schema.Table` objects
5548 in the collection and their columns may participate in implicit SQL
5549 execution.
5551 The :class:`_schema.Table` objects themselves are stored in the
5552 :attr:`_schema.MetaData.tables` dictionary.
5554 :class:`_schema.MetaData` is a thread-safe object for read operations.
5555 Construction of new tables within a single :class:`_schema.MetaData`
5556 object,
5557 either explicitly or via reflection, may not be completely thread-safe.
5559 .. seealso::
5561 :ref:`metadata_describing` - Introduction to database metadata
5563 """
5565 __visit_name__ = "metadata"
5567 def __init__(
5568 self,
5569 schema: Optional[str] = None,
5570 quote_schema: Optional[bool] = None,
5571 naming_convention: Optional[_NamingSchemaParameter] = None,
5572 info: Optional[_InfoType] = None,
5573 ) -> None:
5574 """Create a new MetaData object.
5576 :param schema:
5577 The default schema to use for the :class:`_schema.Table`,
5578 :class:`.Sequence`, and potentially other objects associated with
5579 this :class:`_schema.MetaData`. Defaults to ``None``.
5581 .. seealso::
5583 :ref:`schema_metadata_schema_name` - details on how the
5584 :paramref:`_schema.MetaData.schema` parameter is used.
5586 :paramref:`_schema.Table.schema`
5588 :paramref:`.Sequence.schema`
5590 :param quote_schema:
5591 Sets the ``quote_schema`` flag for those :class:`_schema.Table`,
5592 :class:`.Sequence`, and other objects which make usage of the
5593 local ``schema`` name.
5595 :param info: Optional data dictionary which will be populated into the
5596 :attr:`.SchemaItem.info` attribute of this object.
5598 :param naming_convention: a dictionary referring to values which
5599 will establish default naming conventions for :class:`.Constraint`
5600 and :class:`.Index` objects, for those objects which are not given
5601 a name explicitly.
5603 The keys of this dictionary may be:
5605 * a constraint or Index class, e.g. the :class:`.UniqueConstraint`,
5606 :class:`_schema.ForeignKeyConstraint` class, the :class:`.Index`
5607 class
5609 * a string mnemonic for one of the known constraint classes;
5610 ``"fk"``, ``"pk"``, ``"ix"``, ``"ck"``, ``"uq"`` for foreign key,
5611 primary key, index, check, and unique constraint, respectively.
5613 * the string name of a user-defined "token" that can be used
5614 to define new naming tokens.
5616 The values associated with each "constraint class" or "constraint
5617 mnemonic" key are string naming templates, such as
5618 ``"uq_%(table_name)s_%(column_0_name)s"``,
5619 which describe how the name should be composed. The values
5620 associated with user-defined "token" keys should be callables of the
5621 form ``fn(constraint, table)``, which accepts the constraint/index
5622 object and :class:`_schema.Table` as arguments, returning a string
5623 result.
5625 The built-in names are as follows, some of which may only be
5626 available for certain types of constraint:
5628 * ``%(table_name)s`` - the name of the :class:`_schema.Table`
5629 object
5630 associated with the constraint.
5632 * ``%(referred_table_name)s`` - the name of the
5633 :class:`_schema.Table`
5634 object associated with the referencing target of a
5635 :class:`_schema.ForeignKeyConstraint`.
5637 * ``%(column_0_name)s`` - the name of the :class:`_schema.Column`
5638 at
5639 index position "0" within the constraint.
5641 * ``%(column_0N_name)s`` - the name of all :class:`_schema.Column`
5642 objects in order within the constraint, joined without a
5643 separator.
5645 * ``%(column_0_N_name)s`` - the name of all
5646 :class:`_schema.Column`
5647 objects in order within the constraint, joined with an
5648 underscore as a separator.
5650 * ``%(column_0_label)s``, ``%(column_0N_label)s``,
5651 ``%(column_0_N_label)s`` - the label of either the zeroth
5652 :class:`_schema.Column` or all :class:`.Columns`, separated with
5653 or without an underscore
5655 * ``%(column_0_key)s``, ``%(column_0N_key)s``,
5656 ``%(column_0_N_key)s`` - the key of either the zeroth
5657 :class:`_schema.Column` or all :class:`.Columns`, separated with
5658 or without an underscore
5660 * ``%(referred_column_0_name)s``, ``%(referred_column_0N_name)s``
5661 ``%(referred_column_0_N_name)s``, ``%(referred_column_0_key)s``,
5662 ``%(referred_column_0N_key)s``, ... column tokens which
5663 render the names/keys/labels of columns that are referenced
5664 by a :class:`_schema.ForeignKeyConstraint`.
5666 * ``%(constraint_name)s`` - a special key that refers to the
5667 existing name given to the constraint. When this key is
5668 present, the :class:`.Constraint` object's existing name will be
5669 replaced with one that is composed from template string that
5670 uses this token. When this token is present, it is required that
5671 the :class:`.Constraint` is given an explicit name ahead of time.
5673 * user-defined: any additional token may be implemented by passing
5674 it along with a ``fn(constraint, table)`` callable to the
5675 naming_convention dictionary.
5677 .. seealso::
5679 :ref:`constraint_naming_conventions` - for detailed usage
5680 examples.
5682 """
5683 if schema is not None and not isinstance(schema, str):
5684 raise exc.ArgumentError(
5685 "expected schema argument to be a string, "
5686 f"got {type(schema)}."
5687 )
5688 self.tables = util.FacadeDict()
5689 self.schema = quoted_name.construct(schema, quote_schema)
5690 self.naming_convention = (
5691 naming_convention
5692 if naming_convention
5693 else DEFAULT_NAMING_CONVENTION
5694 )
5695 if info:
5696 self.info = info
5697 self._schemas: Set[str] = set()
5698 self._sequences: Dict[str, Sequence] = {}
5699 self._fk_memos: Dict[Tuple[str, Optional[str]], List[ForeignKey]] = (
5700 collections.defaultdict(list)
5701 )
5702 self._objects: Set[Union[HasSchemaAttr, SchemaType]] = set()
5704 tables: util.FacadeDict[str, Table]
5705 """A dictionary of :class:`_schema.Table`
5706 objects keyed to their name or "table key".
5708 The exact key is that determined by the :attr:`_schema.Table.key`
5709 attribute;
5710 for a table with no :attr:`_schema.Table.schema` attribute,
5711 this is the same
5712 as :attr:`_schema.Table.name`. For a table with a schema,
5713 it is typically of the
5714 form ``schemaname.tablename``.
5716 .. seealso::
5718 :attr:`_schema.MetaData.sorted_tables`
5720 """
5722 def __repr__(self) -> str:
5723 return "MetaData()"
5725 def __contains__(self, table_or_key: Union[str, Table]) -> bool:
5726 if not isinstance(table_or_key, str):
5727 table_or_key = table_or_key.key
5728 return table_or_key in self.tables
5730 def _add_table(
5731 self, name: str, schema: Optional[str], table: Table
5732 ) -> None:
5733 key = _get_table_key(name, schema)
5734 self.tables._insert_item(key, table)
5735 if schema:
5736 self._schemas.add(schema)
5738 def _remove_table(self, name: str, schema: Optional[str]) -> None:
5739 key = _get_table_key(name, schema)
5740 removed = dict.pop(self.tables, key, None)
5741 if removed is not None:
5742 for fk in removed.foreign_keys:
5743 fk._remove_from_metadata(self)
5744 if self._schemas:
5745 self._schemas = {
5746 t.schema for t in self.tables.values() if t.schema is not None
5747 }
5749 def __getstate__(self) -> Dict[str, Any]:
5750 return {
5751 "tables": self.tables,
5752 "schema": self.schema,
5753 "schemas": self._schemas,
5754 "sequences": self._sequences,
5755 "fk_memos": self._fk_memos,
5756 "naming_convention": self.naming_convention,
5757 "objects": self._objects,
5758 }
5760 def __setstate__(self, state: Dict[str, Any]) -> None:
5761 self.tables = state["tables"]
5762 self.schema = state["schema"]
5763 self.naming_convention = state["naming_convention"]
5764 self._sequences = state["sequences"]
5765 self._schemas = state["schemas"]
5766 self._fk_memos = state["fk_memos"]
5767 self._objects = state.get("objects", set())
5769 def clear(self) -> None:
5770 """Clear all objects from this MetaData."""
5772 dict.clear(self.tables)
5773 self._schemas.clear()
5774 self._fk_memos.clear()
5775 self._sequences.clear()
5776 self._objects.clear()
5778 def remove(self, table: Table) -> None:
5779 """Remove the given Table object from this MetaData."""
5781 self._remove_table(table.name, table.schema)
5783 @property
5784 def sorted_tables(self) -> List[Table]:
5785 """Returns a list of :class:`_schema.Table` objects sorted in order of
5786 foreign key dependency.
5788 The sorting will place :class:`_schema.Table`
5789 objects that have dependencies
5790 first, before the dependencies themselves, representing the
5791 order in which they can be created. To get the order in which
5792 the tables would be dropped, use the ``reversed()`` Python built-in.
5794 .. warning::
5796 The :attr:`.MetaData.sorted_tables` attribute cannot by itself
5797 accommodate automatic resolution of dependency cycles between
5798 tables, which are usually caused by mutually dependent foreign key
5799 constraints. When these cycles are detected, the foreign keys
5800 of these tables are omitted from consideration in the sort.
5801 A warning is emitted when this condition occurs, which will be an
5802 exception raise in a future release. Tables which are not part
5803 of the cycle will still be returned in dependency order.
5805 To resolve these cycles, the
5806 :paramref:`_schema.ForeignKeyConstraint.use_alter` parameter may be
5807 applied to those constraints which create a cycle. Alternatively,
5808 the :func:`_schema.sort_tables_and_constraints` function will
5809 automatically return foreign key constraints in a separate
5810 collection when cycles are detected so that they may be applied
5811 to a schema separately.
5813 .. seealso::
5815 :func:`_schema.sort_tables`
5817 :func:`_schema.sort_tables_and_constraints`
5819 :attr:`_schema.MetaData.tables`
5821 :meth:`_reflection.Inspector.get_table_names`
5823 :meth:`_reflection.Inspector.get_sorted_table_and_fkc_names`
5826 """
5827 return ddl.sort_tables(
5828 sorted(self.tables.values(), key=lambda t: t.key) # type: ignore
5829 )
5831 # overload needed to work around mypy this mypy
5832 # https://github.com/python/mypy/issues/17093
5833 @overload
5834 def reflect(
5835 self,
5836 bind: Engine,
5837 schema: Optional[str] = ...,
5838 views: bool = ...,
5839 only: Union[
5840 _typing_Sequence[str], Callable[[str, MetaData], bool], None
5841 ] = ...,
5842 extend_existing: bool = ...,
5843 autoload_replace: bool = ...,
5844 resolve_fks: bool = ...,
5845 **dialect_kwargs: Any,
5846 ) -> None: ...
5848 @overload
5849 def reflect(
5850 self,
5851 bind: Connection,
5852 schema: Optional[str] = ...,
5853 views: bool = ...,
5854 only: Union[
5855 _typing_Sequence[str], Callable[[str, MetaData], bool], None
5856 ] = ...,
5857 extend_existing: bool = ...,
5858 autoload_replace: bool = ...,
5859 resolve_fks: bool = ...,
5860 **dialect_kwargs: Any,
5861 ) -> None: ...
5863 @util.preload_module("sqlalchemy.engine.reflection")
5864 def reflect(
5865 self,
5866 bind: Union[Engine, Connection],
5867 schema: Optional[str] = None,
5868 views: bool = False,
5869 only: Union[
5870 _typing_Sequence[str], Callable[[str, MetaData], bool], None
5871 ] = None,
5872 extend_existing: bool = False,
5873 autoload_replace: bool = True,
5874 resolve_fks: bool = True,
5875 **dialect_kwargs: Any,
5876 ) -> None:
5877 r"""Load all available table definitions from the database.
5879 Automatically creates ``Table`` entries in this ``MetaData`` for any
5880 table available in the database but not yet present in the
5881 ``MetaData``. May be called multiple times to pick up tables recently
5882 added to the database, however no special action is taken if a table
5883 in this ``MetaData`` no longer exists in the database.
5885 :param bind:
5886 A :class:`.Connection` or :class:`.Engine` used to access the
5887 database.
5889 :param schema:
5890 Optional, query and reflect tables from an alternate schema.
5891 If None, the schema associated with this :class:`_schema.MetaData`
5892 is used, if any.
5894 :param views:
5895 If True, also reflect views (materialized and plain).
5897 :param only:
5898 Optional. Load only a sub-set of available named tables. May be
5899 specified as a sequence of names or a callable.
5901 If a sequence of names is provided, only those tables will be
5902 reflected. An error is raised if a table is requested but not
5903 available. Named tables already present in this ``MetaData`` are
5904 ignored.
5906 If a callable is provided, it will be used as a boolean predicate to
5907 filter the list of potential table names. The callable is called
5908 with a table name and this ``MetaData`` instance as positional
5909 arguments and should return a true value for any table to reflect.
5911 :param extend_existing: Passed along to each :class:`_schema.Table` as
5912 :paramref:`_schema.Table.extend_existing`.
5914 :param autoload_replace: Passed along to each :class:`_schema.Table`
5915 as
5916 :paramref:`_schema.Table.autoload_replace`.
5918 :param resolve_fks: if True, reflect :class:`_schema.Table`
5919 objects linked
5920 to :class:`_schema.ForeignKey` objects located in each
5921 :class:`_schema.Table`.
5922 For :meth:`_schema.MetaData.reflect`,
5923 this has the effect of reflecting
5924 related tables that might otherwise not be in the list of tables
5925 being reflected, for example if the referenced table is in a
5926 different schema or is omitted via the
5927 :paramref:`.MetaData.reflect.only` parameter. When False,
5928 :class:`_schema.ForeignKey` objects are not followed to the
5929 :class:`_schema.Table`
5930 in which they link, however if the related table is also part of the
5931 list of tables that would be reflected in any case, the
5932 :class:`_schema.ForeignKey` object will still resolve to its related
5933 :class:`_schema.Table` after the :meth:`_schema.MetaData.reflect`
5934 operation is
5935 complete. Defaults to True.
5937 .. seealso::
5939 :paramref:`_schema.Table.resolve_fks`
5941 :param \**dialect_kwargs: Additional keyword arguments not mentioned
5942 above are dialect specific, and passed in the form
5943 ``<dialectname>_<argname>``. See the documentation regarding an
5944 individual dialect at :ref:`dialect_toplevel` for detail on
5945 documented arguments.
5947 .. seealso::
5949 :ref:`metadata_reflection_toplevel`
5951 :meth:`_events.DDLEvents.column_reflect` - Event used to customize
5952 the reflected columns. Usually used to generalize the types using
5953 :meth:`_types.TypeEngine.as_generic`
5955 :ref:`metadata_reflection_dbagnostic_types` - describes how to
5956 reflect tables using general types.
5958 """
5960 with inspection.inspect(bind)._inspection_context() as insp:
5961 reflect_opts: Any = {
5962 "autoload_with": insp,
5963 "extend_existing": extend_existing,
5964 "autoload_replace": autoload_replace,
5965 "resolve_fks": resolve_fks,
5966 "_extend_on": set(),
5967 }
5969 reflect_opts.update(dialect_kwargs)
5971 if schema is None:
5972 schema = self.schema
5974 if schema is not None:
5975 reflect_opts["schema"] = schema
5977 kind = util.preloaded.engine_reflection.ObjectKind.TABLE
5978 available: util.OrderedSet[str] = util.OrderedSet(
5979 insp.get_table_names(schema, **dialect_kwargs)
5980 )
5981 if views:
5982 kind = util.preloaded.engine_reflection.ObjectKind.ANY
5983 available.update(insp.get_view_names(schema, **dialect_kwargs))
5984 try:
5985 available.update(
5986 insp.get_materialized_view_names(
5987 schema, **dialect_kwargs
5988 )
5989 )
5990 except NotImplementedError:
5991 pass
5993 if schema is not None:
5994 available_w_schema: util.OrderedSet[str] = util.OrderedSet(
5995 [f"{schema}.{name}" for name in available]
5996 )
5997 else:
5998 available_w_schema = available
6000 current = set(self.tables)
6002 if only is None:
6003 load = [
6004 name
6005 for name, schname in zip(available, available_w_schema)
6006 if extend_existing or schname not in current
6007 ]
6008 elif callable(only):
6009 load = [
6010 name
6011 for name, schname in zip(available, available_w_schema)
6012 if (extend_existing or schname not in current)
6013 and only(name, self)
6014 ]
6015 else:
6016 missing = [name for name in only if name not in available]
6017 if missing:
6018 s = schema and (" schema '%s'" % schema) or ""
6019 missing_str = ", ".join(missing)
6020 raise exc.InvalidRequestError(
6021 f"Could not reflect: requested table(s) not available "
6022 f"in {bind.engine!r}{s}: ({missing_str})"
6023 )
6024 load = [
6025 name
6026 for name in only
6027 if extend_existing or name not in current
6028 ]
6029 # pass the available tables so the inspector can
6030 # choose to ignore the filter_names
6031 _reflect_info = insp._get_reflection_info(
6032 schema=schema,
6033 filter_names=load,
6034 available=available,
6035 kind=kind,
6036 scope=util.preloaded.engine_reflection.ObjectScope.ANY,
6037 **dialect_kwargs,
6038 )
6039 reflect_opts["_reflect_info"] = _reflect_info
6041 for name in load:
6042 try:
6043 Table(name, self, **reflect_opts)
6044 except exc.UnreflectableTableError as uerr:
6045 util.warn(f"Skipping table {name}: {uerr}")
6047 def create_all(
6048 self,
6049 bind: _CreateDropBind,
6050 tables: Optional[_typing_Sequence[Table]] = None,
6051 checkfirst: Union[bool, CheckFirst] = CheckFirst.ALL,
6052 ) -> None:
6053 """Create all tables stored in this metadata.
6055 Conditional by default, will not attempt to recreate tables already
6056 present in the target database.
6058 :param bind:
6059 A :class:`.Connection` or :class:`.Engine` used to access the
6060 database.
6062 :param tables:
6063 Optional list of ``Table`` objects, which is a subset of the total
6064 tables in the ``MetaData`` (others are ignored).
6066 :param checkfirst: A boolean value or instance of :class:`.CheckFirst`.
6067 Indicates which objects should be checked for within a separate pass
6068 before creating schema objects.
6070 """
6071 bind._run_ddl_visitor(
6072 ddl.SchemaGenerator, self, checkfirst=checkfirst, tables=tables
6073 )
6075 def drop_all(
6076 self,
6077 bind: _CreateDropBind,
6078 tables: Optional[_typing_Sequence[Table]] = None,
6079 checkfirst: Union[bool, CheckFirst] = CheckFirst.ALL,
6080 ) -> None:
6081 """Drop all tables stored in this metadata.
6083 Conditional by default, will not attempt to drop tables not present in
6084 the target database.
6086 :param bind:
6087 A :class:`.Connection` or :class:`.Engine` used to access the
6088 database.
6090 :param tables:
6091 Optional list of ``Table`` objects, which is a subset of the
6092 total tables in the ``MetaData`` (others are ignored).
6094 :param checkfirst: A boolean value or instance of :class:`.CheckFirst`.
6095 Indicates which objects should be checked for within a separate pass
6096 before dropping schema objects.
6098 """
6099 bind._run_ddl_visitor(
6100 ddl.SchemaDropper, self, checkfirst=checkfirst, tables=tables
6101 )
6103 @property
6104 def schemas(self) -> _typing_Sequence[str]:
6105 """A sequence of schema names that are present in this MetaData."""
6106 schemas = self._schemas
6107 if self.schema:
6108 schemas = schemas | {self.schema}
6109 return tuple(schemas)
6111 def get_schema_objects(
6112 self,
6113 kind: Type[_T],
6114 *,
6115 schema: Union[str, None, Literal[_NoArg.NO_ARG]] = _NoArg.NO_ARG,
6116 ) -> _typing_Sequence[_T]:
6117 """Return a sequence of schema objects of the given kind.
6119 This method can be used to return :class:`_sqltypes.Enum`,
6120 :class:`.Sequence`, etc. objects registered in this
6121 :class:`_schema.MetaData`.
6123 :param kind: a type that indicates what object to return, such as
6124 :class:`Enum` or :class:`Sequence`.
6125 :param schema: Optional, a schema name to filter the objects by. If
6126 not provided the default schema of the metadata is used.
6128 """
6130 if schema is _NoArg.NO_ARG:
6131 schema = self.schema
6132 return tuple(
6133 obj
6134 for obj in self._objects
6135 if isinstance(obj, kind) and obj.schema == schema
6136 )
6138 def get_schema_object_by_name(
6139 self,
6140 kind: Type[_T],
6141 name: str,
6142 *,
6143 schema: Union[str, None, Literal[_NoArg.NO_ARG]] = _NoArg.NO_ARG,
6144 ) -> Optional[_T]:
6145 """Return a schema objects of the given kind and name if found.
6147 This method can be used to return :class:`_sqltypes.Enum`,
6148 :class:`.Sequence`, etc. objects registered in this
6149 :class:`_schema.MetaData`.
6151 :param kind: a type that indicates what object to return, such as
6152 :class:`Enum` or :class:`Sequence`.
6153 :param name: the name of the object to return.
6154 :param schema: Optional, a schema name to filter the objects by. If
6155 not provided the default schema of the metadata is used.
6157 """
6159 for obj in self.get_schema_objects(kind, schema=schema):
6160 if getattr(obj, "name", None) == name:
6161 return obj
6162 return None
6164 def _register_object(self, obj: Union[HasSchemaAttr, SchemaType]) -> None:
6165 self._objects.add(obj)
6168class Computed(FetchedValue, SchemaItem):
6169 """Defines a generated column, i.e. "GENERATED ALWAYS AS" syntax.
6171 The :class:`.Computed` construct is an inline construct added to the
6172 argument list of a :class:`_schema.Column` object::
6174 from sqlalchemy import Computed
6176 Table(
6177 "square",
6178 metadata_obj,
6179 Column("side", Float, nullable=False),
6180 Column("area", Float, Computed("side * side")),
6181 )
6183 See the linked documentation below for complete details.
6185 .. seealso::
6187 :ref:`computed_ddl`
6189 """
6191 __visit_name__ = "computed_column"
6193 column: Optional[Column[Any]]
6195 @_document_text_coercion(
6196 "sqltext", ":class:`.Computed`", ":paramref:`.Computed.sqltext`"
6197 )
6198 def __init__(
6199 self, sqltext: _DDLColumnArgument, persisted: Optional[bool] = None
6200 ) -> None:
6201 """Construct a GENERATED ALWAYS AS DDL construct to accompany a
6202 :class:`_schema.Column`.
6204 :param sqltext:
6205 A string containing the column generation expression, which will be
6206 used verbatim, or a SQL expression construct, such as a
6207 :func:`_expression.text`
6208 object. If given as a string, the object is converted to a
6209 :func:`_expression.text` object.
6211 :param persisted:
6212 Optional, controls how this column should be persisted by the
6213 database. Possible values are:
6215 * ``None``, the default, it will use the default persistence
6216 defined by the database.
6217 * ``True``, will render ``GENERATED ALWAYS AS ... STORED``, or the
6218 equivalent for the target database if supported.
6219 * ``False``, will render ``GENERATED ALWAYS AS ... VIRTUAL``, or
6220 the equivalent for the target database if supported.
6222 Specifying ``True`` or ``False`` may raise an error when the DDL
6223 is emitted to the target database if the database does not support
6224 that persistence option. Leaving this parameter at its default
6225 of ``None`` is guaranteed to succeed for all databases that support
6226 ``GENERATED ALWAYS AS``.
6228 """
6229 self.sqltext = coercions.expect(roles.DDLExpressionRole, sqltext)
6230 self.persisted = persisted
6231 self.column = None
6233 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
6234 assert isinstance(parent, Column)
6236 if not isinstance(
6237 parent.server_default, (type(None), Computed)
6238 ) or not isinstance(parent.server_onupdate, (type(None), Computed)):
6239 raise exc.ArgumentError(
6240 "A generated column cannot specify a server_default or a "
6241 "server_onupdate argument"
6242 )
6243 self.column = parent
6244 parent.computed = self
6245 self.column.server_onupdate = self
6246 self.column.server_default = self
6248 def _as_for_update(self, for_update: bool) -> FetchedValue:
6249 return self
6251 @util.deprecated(
6252 "1.4",
6253 "The :meth:`_schema.Computed.copy` method is deprecated "
6254 "and will be removed in a future release.",
6255 )
6256 def copy(
6257 self, *, target_table: Optional[Table] = None, **kw: Any
6258 ) -> Computed:
6259 return self._copy(target_table=target_table, **kw)
6261 def _copy(
6262 self, *, target_table: Optional[Table] = None, **kw: Any
6263 ) -> Computed:
6264 sqltext = _copy_expression(
6265 self.sqltext,
6266 self.column.table if self.column is not None else None,
6267 target_table,
6268 )
6269 g = Computed(sqltext, persisted=self.persisted)
6271 return self._schema_item_copy(g)
6274class Identity(IdentityOptions, FetchedValue, SchemaItem):
6275 """Defines an identity column, i.e. "GENERATED { ALWAYS | BY DEFAULT }
6276 AS IDENTITY" syntax.
6278 The :class:`.Identity` construct is an inline construct added to the
6279 argument list of a :class:`_schema.Column` object::
6281 from sqlalchemy import Identity
6283 Table(
6284 "foo",
6285 metadata_obj,
6286 Column("id", Integer, Identity()),
6287 Column("description", Text),
6288 )
6290 See the linked documentation below for complete details.
6292 .. versionadded:: 1.4
6294 .. seealso::
6296 :ref:`identity_ddl`
6298 """
6300 __visit_name__ = "identity_column"
6302 is_identity = True
6304 @util.deprecated_params(
6305 order=(
6306 "2.1",
6307 "This parameter is supported only by Oracle Database, "
6308 "use ``oracle_order`` instead.",
6309 ),
6310 on_null=(
6311 "2.1",
6312 "This parameter is supported only by Oracle Database, "
6313 "use ``oracle_on_null`` instead.",
6314 ),
6315 )
6316 def __init__(
6317 self,
6318 always: Optional[bool] = False,
6319 on_null: Optional[bool] = None,
6320 start: Optional[int] = None,
6321 increment: Optional[int] = None,
6322 minvalue: Optional[int] = None,
6323 maxvalue: Optional[int] = None,
6324 nominvalue: Optional[bool] = None,
6325 nomaxvalue: Optional[bool] = None,
6326 cycle: Optional[bool] = None,
6327 cache: Optional[int] = None,
6328 order: Optional[bool] = None,
6329 **dialect_kw: Any,
6330 ) -> None:
6331 """Construct a GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY DDL
6332 construct to accompany a :class:`_schema.Column`.
6334 See the :class:`.Sequence` documentation for a complete description
6335 of most parameters.
6337 .. note::
6338 MSSQL supports this construct as the preferred alternative to
6339 generate an IDENTITY on a column, but it uses non standard
6340 syntax that only support :paramref:`_schema.Identity.start`
6341 and :paramref:`_schema.Identity.increment`.
6342 All other parameters are ignored.
6344 :param always:
6345 A boolean, that indicates the type of identity column.
6346 If ``False`` is specified, the default, then the user-specified
6347 value takes precedence.
6348 If ``True`` is specified, a user-specified value is not accepted (
6349 on some backends, like PostgreSQL, OVERRIDING SYSTEM VALUE, or
6350 similar, may be specified in an INSERT to override the sequence
6351 value).
6352 Some backends also have a default value for this parameter,
6353 ``None`` can be used to omit rendering this part in the DDL. It
6354 will be treated as ``False`` if a backend does not have a default
6355 value.
6357 :param on_null:
6358 Set to ``True`` to specify ON NULL in conjunction with a
6359 ``always=False`` identity column. This option is only supported on
6360 some backends, like Oracle Database.
6362 :param start: the starting index of the sequence.
6363 :param increment: the increment value of the sequence.
6364 :param minvalue: the minimum value of the sequence.
6365 :param maxvalue: the maximum value of the sequence.
6366 :param nominvalue: no minimum value of the sequence.
6367 :param nomaxvalue: no maximum value of the sequence.
6368 :param cycle: allows the sequence to wrap around when the maxvalue
6369 or minvalue has been reached.
6370 :param cache: optional integer value; number of future values in the
6371 sequence which are calculated in advance.
6372 :param order: optional boolean value; if true, renders the
6373 ORDER keyword.
6375 """
6376 self.dialect_options
6377 if on_null is not None:
6378 if "oracle_on_null" in dialect_kw:
6379 raise exc.ArgumentError(
6380 "Cannot specify both 'on_null' and 'oracle_on_null'. "
6381 "Plese use only 'oracle_on_null'."
6382 )
6383 dialect_kw["oracle_on_null"] = on_null
6385 IdentityOptions.__init__(
6386 self,
6387 start=start,
6388 increment=increment,
6389 minvalue=minvalue,
6390 maxvalue=maxvalue,
6391 nominvalue=nominvalue,
6392 nomaxvalue=nomaxvalue,
6393 cycle=cycle,
6394 cache=cache,
6395 order=order,
6396 **dialect_kw,
6397 )
6398 self.always = always
6399 self.column = None
6401 @property
6402 def on_null(self) -> Optional[bool]:
6403 """Alias of the ``dialect_kwargs`` ``'oracle_on_null'``.
6405 .. deprecated:: 2.1 The 'on_null' attribute is deprecated.
6406 """
6407 value: Optional[bool] = self.dialect_kwargs.get("oracle_on_null")
6408 return value
6410 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
6411 assert isinstance(parent, Column)
6412 if not isinstance(
6413 parent.server_default, (type(None), Identity)
6414 ) or not isinstance(parent.server_onupdate, type(None)):
6415 raise exc.ArgumentError(
6416 "A column with an Identity object cannot specify a "
6417 "server_default or a server_onupdate argument"
6418 )
6419 if parent.autoincrement is False:
6420 raise exc.ArgumentError(
6421 "A column with an Identity object cannot specify "
6422 "autoincrement=False"
6423 )
6424 self.column = parent
6426 parent.identity = self
6427 if parent._user_defined_nullable is NULL_UNSPECIFIED:
6428 parent.nullable = False
6430 parent.server_default = self
6432 def _as_for_update(self, for_update: bool) -> FetchedValue:
6433 return self
6435 @util.deprecated(
6436 "1.4",
6437 "The :meth:`_schema.Identity.copy` method is deprecated "
6438 "and will be removed in a future release.",
6439 )
6440 def copy(self, **kw: Any) -> Identity:
6441 return self._copy(**kw)
6443 def _copy(self, **kw: Any) -> Identity:
6444 i = Identity(**self._as_dict(), **self.dialect_kwargs)
6446 return self._schema_item_copy(i)
6448 def _as_dict(self) -> Dict[str, Any]:
6449 return {
6450 # always=None means something different than always=False
6451 "always": self.always,
6452 **super()._as_dict(),
6453 }