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 .ddl import TableCreateDDL
107 from .ddl import TableDropDDL
108 from .elements import BindParameter
109 from .elements import KeyedColumnElement
110 from .functions import Function
111 from .sqltypes import SchemaType
112 from .type_api import TypeEngine
113 from .visitors import anon_map
114 from ..engine import Connection
115 from ..engine import Engine
116 from ..engine.interfaces import _CoreMultiExecuteParams
117 from ..engine.interfaces import CoreExecuteOptionsParameter
118 from ..engine.interfaces import ExecutionContext
119 from ..engine.reflection import _ReflectionInfo
120 from ..sql.selectable import FromClause
122_T = TypeVar("_T", bound="Any")
123_SI = TypeVar("_SI", bound="SchemaItem")
124_TAB = TypeVar("_TAB", bound="Table")
127_ConstraintNameArgument = Optional[Union[str, _NoneName]]
129_ServerDefaultArgument = Union[
130 "FetchedValue", str, TextClause, ColumnElement[Any]
131]
133_ServerOnUpdateArgument = _ServerDefaultArgument
136class SchemaConst(Enum):
137 RETAIN_SCHEMA = 1
138 """Symbol indicating that a :class:`_schema.Table`, :class:`.Sequence`
139 or in some cases a :class:`_schema.ForeignKey` object, in situations
140 where the object is being copied for a :meth:`.Table.to_metadata`
141 operation, should retain the schema name that it already has.
143 """
145 BLANK_SCHEMA = 2
146 """Symbol indicating that a :class:`_schema.Table` or :class:`.Sequence`
147 should have 'None' for its schema, even if the parent
148 :class:`_schema.MetaData` has specified a schema.
150 .. seealso::
152 :paramref:`_schema.MetaData.schema`
154 :paramref:`_schema.Table.schema`
156 :paramref:`.Sequence.schema`
158 """
160 NULL_UNSPECIFIED = 3
161 """Symbol indicating the "nullable" keyword was not passed to a Column.
163 This is used to distinguish between the use case of passing
164 ``nullable=None`` to a :class:`.Column`, which has special meaning
165 on some backends such as SQL Server.
167 """
170RETAIN_SCHEMA: Final[Literal[SchemaConst.RETAIN_SCHEMA]] = (
171 SchemaConst.RETAIN_SCHEMA
172)
173BLANK_SCHEMA: Final[Literal[SchemaConst.BLANK_SCHEMA]] = (
174 SchemaConst.BLANK_SCHEMA
175)
176NULL_UNSPECIFIED: Final[Literal[SchemaConst.NULL_UNSPECIFIED]] = (
177 SchemaConst.NULL_UNSPECIFIED
178)
181def _get_table_key(name: str, schema: Optional[str]) -> str:
182 if schema is None:
183 return name
184 else:
185 return schema + "." + name
188# this should really be in sql/util.py but we'd have to
189# break an import cycle
190def _copy_expression(
191 expression: ColumnElement[Any],
192 source_table: Optional[Table],
193 target_table: Optional[Table],
194) -> ColumnElement[Any]:
195 if source_table is None or target_table is None:
196 return expression
198 fixed_source_table = source_table
199 fixed_target_table = target_table
201 def replace(
202 element: ExternallyTraversible, **kw: Any
203 ) -> Optional[ExternallyTraversible]:
204 if (
205 isinstance(element, Column)
206 and element.table is fixed_source_table
207 and element.key in fixed_source_table.c
208 ):
209 return fixed_target_table.c[element.key]
210 else:
211 return None
213 return cast(
214 ColumnElement[Any],
215 visitors.replacement_traverse(expression, {}, replace),
216 )
219@inspection._self_inspects
220class SchemaItem(SchemaVisitable):
221 """Base class for items that define a database schema."""
223 __visit_name__ = "schema_item"
225 create_drop_stringify_dialect = "default"
227 def _init_items(self, *args: SchemaItem, **kw: Any) -> None:
228 """Initialize the list of child items for this SchemaItem."""
229 for item in args:
230 if item is not None:
231 try:
232 spwd = item._set_parent_with_dispatch
233 except AttributeError as err:
234 raise exc.ArgumentError(
235 "'SchemaItem' object, such as a 'Column' or a "
236 f"'Constraint' expected, got {item!r}"
237 ) from err
238 else:
239 spwd(self, **kw)
241 def __repr__(self) -> str:
242 return util.generic_repr(self, omit_kwarg=["info"])
244 @util.memoized_property
245 def info(self) -> _InfoType:
246 """Info dictionary associated with the object, allowing user-defined
247 data to be associated with this :class:`.SchemaItem`.
249 The dictionary is automatically generated when first accessed.
250 It can also be specified in the constructor of some objects,
251 such as :class:`_schema.Table` and :class:`_schema.Column`.
253 """
254 return {}
256 def _schema_item_copy(self, schema_item: _SI) -> _SI:
257 if "info" in self.__dict__:
258 schema_item.info = self.info.copy()
259 schema_item.dispatch._update(self.dispatch)
260 return schema_item
262 _use_schema_map = True
265class HasConditionalDDL:
266 """define a class that includes the :meth:`.HasConditionalDDL.ddl_if`
267 method, allowing for conditional rendering of DDL.
269 Currently applies to constraints and indexes.
271 .. versionadded:: 2.0
274 """
276 _ddl_if: Optional[ddl.DDLIf] = None
278 def ddl_if(
279 self,
280 dialect: Optional[str] = None,
281 callable_: Optional[ddl.DDLIfCallable] = None,
282 state: Optional[Any] = None,
283 ) -> Self:
284 r"""apply a conditional DDL rule to this schema item.
286 These rules work in a similar manner to the
287 :meth:`.ExecutableDDLElement.execute_if` callable, with the added
288 feature that the criteria may be checked within the DDL compilation
289 phase for a construct such as :class:`.CreateTable`.
290 :meth:`.HasConditionalDDL.ddl_if` currently applies towards the
291 :class:`.Index` construct as well as all :class:`.Constraint`
292 constructs.
294 :param dialect: string name of a dialect, or a tuple of string names
295 to indicate multiple dialect types.
297 :param callable\_: a callable that is constructed using the same form
298 as that described in
299 :paramref:`.ExecutableDDLElement.execute_if.callable_`.
301 :param state: any arbitrary object that will be passed to the
302 callable, if present.
304 .. versionadded:: 2.0
306 .. seealso::
308 :ref:`schema_ddl_ddl_if` - background and usage examples
311 """
312 self._ddl_if = ddl.DDLIf(dialect, callable_, state)
313 return self
316class HasSchemaAttr(SchemaItem):
317 """schema item that includes a top-level schema name"""
319 schema: Optional[str]
322class Table(
323 DialectKWArgs, HasSchemaAttr, TableClause, inspection.Inspectable["Table"]
324):
325 r"""Represent a table in a database.
327 e.g.::
329 mytable = Table(
330 "mytable",
331 metadata,
332 Column("mytable_id", Integer, primary_key=True),
333 Column("value", String(50)),
334 )
336 The :class:`_schema.Table`
337 object constructs a unique instance of itself based
338 on its name and optional schema name within the given
339 :class:`_schema.MetaData` object. Calling the :class:`_schema.Table`
340 constructor with the same name and same :class:`_schema.MetaData` argument
341 a second time will return the *same* :class:`_schema.Table`
342 object - in this way
343 the :class:`_schema.Table` constructor acts as a registry function.
345 .. seealso::
347 :ref:`metadata_describing` - Introduction to database metadata
349 """
351 __visit_name__ = "table"
353 if TYPE_CHECKING:
355 @util.ro_non_memoized_property
356 def primary_key(self) -> PrimaryKeyConstraint: ...
358 @util.ro_non_memoized_property
359 def foreign_keys(self) -> Set[ForeignKey]: ...
361 _columns: DedupeColumnCollection[Column[Any]] # type: ignore[assignment]
363 _sentinel_column: Optional[Column[Any]]
365 constraints: Set[Constraint]
366 """A collection of all :class:`_schema.Constraint` objects associated with
367 this :class:`_schema.Table`.
369 Includes :class:`_schema.PrimaryKeyConstraint`,
370 :class:`_schema.ForeignKeyConstraint`, :class:`_schema.UniqueConstraint`,
371 :class:`_schema.CheckConstraint`. A separate collection
372 :attr:`_schema.Table.foreign_key_constraints` refers to the collection
373 of all :class:`_schema.ForeignKeyConstraint` objects, and the
374 :attr:`_schema.Table.primary_key` attribute refers to the single
375 :class:`_schema.PrimaryKeyConstraint` associated with the
376 :class:`_schema.Table`.
378 .. seealso::
380 :attr:`_schema.Table.constraints`
382 :attr:`_schema.Table.primary_key`
384 :attr:`_schema.Table.foreign_key_constraints`
386 :attr:`_schema.Table.indexes`
388 :class:`_reflection.Inspector`
391 """
393 indexes: Set[Index]
394 """A collection of all :class:`_schema.Index` objects associated with this
395 :class:`_schema.Table`.
397 .. seealso::
399 :meth:`_reflection.Inspector.get_indexes`
401 """
403 if TYPE_CHECKING:
405 @util.ro_non_memoized_property
406 def columns(self) -> ReadOnlyColumnCollection[str, Column[Any]]: ...
408 @util.ro_non_memoized_property
409 def exported_columns(
410 self,
411 ) -> ReadOnlyColumnCollection[str, Column[Any]]: ...
413 @util.ro_non_memoized_property
414 def c(self) -> ReadOnlyColumnCollection[str, Column[Any]]: ...
416 def _gen_cache_key(
417 self, anon_map: anon_map, bindparams: List[BindParameter[Any]]
418 ) -> Tuple[Any, ...]:
419 if self._annotations:
420 return (self,) + self._annotations_cache_key
421 else:
422 return (self,)
424 if not typing.TYPE_CHECKING:
425 # typing tools seem to be inconsistent in how they handle
426 # __new__, so suggest this pattern for classes that use
427 # __new__. apply typing to the __init__ method normally
428 @util.deprecated_params(
429 mustexist=(
430 "1.4",
431 "Deprecated alias of :paramref:`_schema.Table.must_exist`",
432 ),
433 )
434 def __new__(cls, *args: Any, **kw: Any) -> Any:
435 return cls._new(*args, **kw)
437 @classmethod
438 def _new(cls, *args: Any, **kw: Any) -> Any:
439 if not args and not kw:
440 # python3k pickle seems to call this
441 return object.__new__(cls)
443 try:
444 name, metadata, args = args[0], args[1], args[2:]
445 except IndexError:
446 raise TypeError(
447 "Table() takes at least two positional-only "
448 "arguments 'name' and 'metadata'"
449 )
451 schema = kw.get("schema", None)
452 if schema is None:
453 schema = metadata.schema
454 elif schema is BLANK_SCHEMA:
455 schema = None
456 keep_existing = kw.get("keep_existing", False)
457 extend_existing = kw.get("extend_existing", False)
459 if keep_existing and extend_existing:
460 msg = "keep_existing and extend_existing are mutually exclusive."
461 raise exc.ArgumentError(msg)
463 must_exist = kw.pop("must_exist", kw.pop("mustexist", False))
464 key = _get_table_key(name, schema)
465 if key in metadata.tables:
466 if not keep_existing and not extend_existing and bool(args):
467 raise exc.InvalidRequestError(
468 f"Table '{key}' is already defined for this MetaData "
469 "instance. Specify 'extend_existing=True' "
470 "to redefine "
471 "options and columns on an "
472 "existing Table object."
473 )
474 table = metadata.tables[key]
475 if extend_existing:
476 table._init_existing(*args, **kw)
477 return table
478 else:
479 if must_exist:
480 raise exc.InvalidRequestError(f"Table '{key}' not defined")
481 table = object.__new__(cls)
482 table.dispatch.before_parent_attach(table, metadata)
483 metadata._add_table(name, schema, table)
484 try:
485 table.__init__(name, metadata, *args, _no_init=False, **kw) # type: ignore[misc] # noqa: E501
486 table.dispatch.after_parent_attach(table, metadata)
487 return table
488 except Exception:
489 with util.safe_reraise():
490 metadata._remove_table(name, schema)
492 def __init__(
493 self,
494 name: str,
495 metadata: MetaData,
496 *args: SchemaItem,
497 schema: Optional[Union[str, Literal[SchemaConst.BLANK_SCHEMA]]] = None,
498 quote: Optional[bool] = None,
499 quote_schema: Optional[bool] = None,
500 autoload_with: Optional[Union[Engine, Connection]] = None,
501 autoload_replace: bool = True,
502 keep_existing: bool = False,
503 extend_existing: bool = False,
504 resolve_fks: bool = True,
505 include_columns: Optional[Collection[str]] = None,
506 implicit_returning: bool = True,
507 comment: Optional[str] = None,
508 info: Optional[Dict[Any, Any]] = None,
509 listeners: Optional[
510 _typing_Sequence[Tuple[str, Callable[..., Any]]]
511 ] = None,
512 prefixes: Optional[_typing_Sequence[str]] = None,
513 _creator_ddl: TableCreateDDL | None = None,
514 _dropper_ddl: TableDropDDL | None = None,
515 # used internally in the metadata.reflect() process
516 _extend_on: Optional[Set[Table]] = None,
517 # used by __new__ to bypass __init__
518 _no_init: bool = True,
519 # dialect-specific keyword args
520 **kw: Any,
521 ) -> None:
522 r"""Constructor for :class:`_schema.Table`.
525 :param name: The name of this table as represented in the database.
527 The table name, along with the value of the ``schema`` parameter,
528 forms a key which uniquely identifies this :class:`_schema.Table`
529 within
530 the owning :class:`_schema.MetaData` collection.
531 Additional calls to :class:`_schema.Table` with the same name,
532 metadata,
533 and schema name will return the same :class:`_schema.Table` object.
535 Names which contain no upper case characters
536 will be treated as case insensitive names, and will not be quoted
537 unless they are a reserved word or contain special characters.
538 A name with any number of upper case characters is considered
539 to be case sensitive, and will be sent as quoted.
541 To enable unconditional quoting for the table name, specify the flag
542 ``quote=True`` to the constructor, or use the :class:`.quoted_name`
543 construct to specify the name.
545 :param metadata: a :class:`_schema.MetaData`
546 object which will contain this
547 table. The metadata is used as a point of association of this table
548 with other tables which are referenced via foreign key. It also
549 may be used to associate this table with a particular
550 :class:`.Connection` or :class:`.Engine`.
552 :param \*args: Additional positional arguments are used primarily
553 to add the list of :class:`_schema.Column`
554 objects contained within this
555 table. Similar to the style of a CREATE TABLE statement, other
556 :class:`.SchemaItem` constructs may be added here, including
557 :class:`.PrimaryKeyConstraint`, and
558 :class:`_schema.ForeignKeyConstraint`.
560 :param autoload_replace: Defaults to ``True``; when using
561 :paramref:`_schema.Table.autoload_with`
562 in conjunction with :paramref:`_schema.Table.extend_existing`,
563 indicates
564 that :class:`_schema.Column` objects present in the already-existing
565 :class:`_schema.Table`
566 object should be replaced with columns of the same
567 name retrieved from the autoload process. When ``False``, columns
568 already present under existing names will be omitted from the
569 reflection process.
571 Note that this setting does not impact :class:`_schema.Column` objects
572 specified programmatically within the call to :class:`_schema.Table`
573 that
574 also is autoloading; those :class:`_schema.Column` objects will always
575 replace existing columns of the same name when
576 :paramref:`_schema.Table.extend_existing` is ``True``.
578 .. seealso::
580 :paramref:`_schema.Table.autoload_with`
582 :paramref:`_schema.Table.extend_existing`
584 :param autoload_with: An :class:`_engine.Engine` or
585 :class:`_engine.Connection` object,
586 or a :class:`_reflection.Inspector` object as returned by
587 :func:`_sa.inspect`
588 against one, with which this :class:`_schema.Table`
589 object will be reflected.
590 When set to a non-None value, the autoload process will take place
591 for this table against the given engine or connection.
593 .. seealso::
595 :ref:`metadata_reflection_toplevel`
597 :meth:`_events.DDLEvents.column_reflect`
599 :ref:`metadata_reflection_dbagnostic_types`
601 :param extend_existing: When ``True``, indicates that if this
602 :class:`_schema.Table` is already present in the given
603 :class:`_schema.MetaData`,
604 apply further arguments within the constructor to the existing
605 :class:`_schema.Table`.
607 If :paramref:`_schema.Table.extend_existing` or
608 :paramref:`_schema.Table.keep_existing` are not set,
609 and the given name
610 of the new :class:`_schema.Table` refers to a :class:`_schema.Table`
611 that is
612 already present in the target :class:`_schema.MetaData` collection,
613 and
614 this :class:`_schema.Table`
615 specifies additional columns or other constructs
616 or flags that modify the table's state, an
617 error is raised. The purpose of these two mutually-exclusive flags
618 is to specify what action should be taken when a
619 :class:`_schema.Table`
620 is specified that matches an existing :class:`_schema.Table`,
621 yet specifies
622 additional constructs.
624 :paramref:`_schema.Table.extend_existing`
625 will also work in conjunction
626 with :paramref:`_schema.Table.autoload_with` to run a new reflection
627 operation against the database, even if a :class:`_schema.Table`
628 of the same name is already present in the target
629 :class:`_schema.MetaData`; newly reflected :class:`_schema.Column`
630 objects
631 and other options will be added into the state of the
632 :class:`_schema.Table`, potentially overwriting existing columns
633 and options of the same name.
635 As is always the case with :paramref:`_schema.Table.autoload_with`,
636 :class:`_schema.Column` objects can be specified in the same
637 :class:`_schema.Table`
638 constructor, which will take precedence. Below, the existing
639 table ``mytable`` will be augmented with :class:`_schema.Column`
640 objects
641 both reflected from the database, as well as the given
642 :class:`_schema.Column`
643 named "y"::
645 Table(
646 "mytable",
647 metadata,
648 Column("y", Integer),
649 extend_existing=True,
650 autoload_with=engine,
651 )
653 .. seealso::
655 :paramref:`_schema.Table.autoload_with`
657 :paramref:`_schema.Table.autoload_replace`
659 :paramref:`_schema.Table.keep_existing`
662 :param implicit_returning: True by default - indicates that
663 RETURNING can be used, typically by the ORM, in order to fetch
664 server-generated values such as primary key values and
665 server side defaults, on those backends which support RETURNING.
667 In modern SQLAlchemy there is generally no reason to alter this
668 setting, except for some backend specific cases
669 (see :ref:`mssql_triggers` in the SQL Server dialect documentation
670 for one such example).
672 :param include_columns: A list of strings indicating a subset of
673 columns to be loaded via the ``autoload`` operation; table columns who
674 aren't present in this list will not be represented on the resulting
675 ``Table`` object. Defaults to ``None`` which indicates all columns
676 should be reflected.
678 :param resolve_fks: Whether or not to reflect :class:`_schema.Table`
679 objects
680 related to this one via :class:`_schema.ForeignKey` objects, when
681 :paramref:`_schema.Table.autoload_with` is
682 specified. Defaults to True. Set to False to disable reflection of
683 related tables as :class:`_schema.ForeignKey`
684 objects are encountered; may be
685 used either to save on SQL calls or to avoid issues with related tables
686 that can't be accessed. Note that if a related table is already present
687 in the :class:`_schema.MetaData` collection, or becomes present later,
688 a
689 :class:`_schema.ForeignKey` object associated with this
690 :class:`_schema.Table` will
691 resolve to that table normally.
693 .. seealso::
695 :paramref:`.MetaData.reflect.resolve_fks`
698 :param info: Optional data dictionary which will be populated into the
699 :attr:`.SchemaItem.info` attribute of this object.
701 :param keep_existing: When ``True``, indicates that if this Table
702 is already present in the given :class:`_schema.MetaData`, ignore
703 further arguments within the constructor to the existing
704 :class:`_schema.Table`, and return the :class:`_schema.Table`
705 object as
706 originally created. This is to allow a function that wishes
707 to define a new :class:`_schema.Table` on first call, but on
708 subsequent calls will return the same :class:`_schema.Table`,
709 without any of the declarations (particularly constraints)
710 being applied a second time.
712 If :paramref:`_schema.Table.extend_existing` or
713 :paramref:`_schema.Table.keep_existing` are not set,
714 and the given name
715 of the new :class:`_schema.Table` refers to a :class:`_schema.Table`
716 that is
717 already present in the target :class:`_schema.MetaData` collection,
718 and
719 this :class:`_schema.Table`
720 specifies additional columns or other constructs
721 or flags that modify the table's state, an
722 error is raised. The purpose of these two mutually-exclusive flags
723 is to specify what action should be taken when a
724 :class:`_schema.Table`
725 is specified that matches an existing :class:`_schema.Table`,
726 yet specifies
727 additional constructs.
729 .. seealso::
731 :paramref:`_schema.Table.extend_existing`
733 :param listeners: A list of tuples of the form ``(<eventname>, <fn>)``
734 which will be passed to :func:`.event.listen` upon construction.
735 This alternate hook to :func:`.event.listen` allows the establishment
736 of a listener function specific to this :class:`_schema.Table` before
737 the "autoload" process begins. Historically this has been intended
738 for use with the :meth:`.DDLEvents.column_reflect` event, however
739 note that this event hook may now be associated with the
740 :class:`_schema.MetaData` object directly::
742 def listen_for_reflect(table, column_info):
743 "handle the column reflection event"
744 # ...
747 t = Table(
748 "sometable",
749 autoload_with=engine,
750 listeners=[("column_reflect", listen_for_reflect)],
751 )
753 .. seealso::
755 :meth:`_events.DDLEvents.column_reflect`
757 :param must_exist: When ``True``, indicates that this Table must already
758 be present in the given :class:`_schema.MetaData` collection, else
759 an exception is raised.
761 :param prefixes:
762 A list of strings to insert after CREATE in the CREATE TABLE
763 statement. They will be separated by spaces.
765 :param quote: Force quoting of this table's name on or off, corresponding
766 to ``True`` or ``False``. When left at its default of ``None``,
767 the column identifier will be quoted according to whether the name is
768 case sensitive (identifiers with at least one upper case character are
769 treated as case sensitive), or if it's a reserved word. This flag
770 is only needed to force quoting of a reserved word which is not known
771 by the SQLAlchemy dialect.
773 .. note:: setting this flag to ``False`` will not provide
774 case-insensitive behavior for table reflection; table reflection
775 will always search for a mixed-case name in a case sensitive
776 fashion. Case insensitive names are specified in SQLAlchemy only
777 by stating the name with all lower case characters.
779 :param quote_schema: same as 'quote' but applies to the schema identifier.
781 :param schema: The schema name for this table, which is required if
782 the table resides in a schema other than the default selected schema
783 for the engine's database connection. Defaults to ``None``.
785 If the owning :class:`_schema.MetaData` of this :class:`_schema.Table`
786 specifies its
787 own :paramref:`_schema.MetaData.schema` parameter,
788 then that schema name will
789 be applied to this :class:`_schema.Table`
790 if the schema parameter here is set
791 to ``None``. To set a blank schema name on a :class:`_schema.Table`
792 that
793 would otherwise use the schema set on the owning
794 :class:`_schema.MetaData`,
795 specify the special symbol :attr:`.BLANK_SCHEMA`.
797 The quoting rules for the schema name are the same as those for the
798 ``name`` parameter, in that quoting is applied for reserved words or
799 case-sensitive names; to enable unconditional quoting for the schema
800 name, specify the flag ``quote_schema=True`` to the constructor, or use
801 the :class:`.quoted_name` construct to specify the name.
803 :param comment: Optional string that will render an SQL comment on table
804 creation.
806 :param \**kw: Additional keyword arguments not mentioned above are
807 dialect specific, and passed in the form ``<dialectname>_<argname>``.
808 See the documentation regarding an individual dialect at
809 :ref:`dialect_toplevel` for detail on documented arguments.
811 """ # noqa: E501
812 if _no_init:
813 # don't run __init__ from __new__ by default;
814 # __new__ has a specific place that __init__ is called
815 return
817 super().__init__(quoted_name(name, quote))
818 self.metadata = metadata
820 if schema is None:
821 self.schema = metadata.schema
822 elif schema is BLANK_SCHEMA:
823 self.schema = None
824 else:
825 quote_schema = quote_schema
826 assert isinstance(schema, str)
827 self.schema = quoted_name(schema, quote_schema)
829 self._sentinel_column = None
830 self._creator_ddl = _creator_ddl
831 self._dropper_ddl = _dropper_ddl
833 self.indexes = set()
834 self.constraints = set()
835 PrimaryKeyConstraint(
836 _implicit_generated=True
837 )._set_parent_with_dispatch(self)
838 self.foreign_keys = set() # type: ignore
839 self._extra_dependencies: Set[Table] = set()
840 if self.schema is not None:
841 self.fullname = "%s.%s" % (self.schema, self.name)
842 else:
843 self.fullname = self.name
845 self.implicit_returning = implicit_returning
846 _reflect_info = kw.pop("_reflect_info", None)
848 self.comment = comment
850 if info is not None:
851 self.info = info
853 if listeners is not None:
854 for evt, fn in listeners:
855 event.listen(self, evt, fn)
857 self._prefixes = prefixes if prefixes else []
859 self._extra_kwargs(**kw)
861 # load column definitions from the database if 'autoload' is defined
862 # we do it after the table is in the singleton dictionary to support
863 # circular foreign keys
864 if autoload_with is not None:
865 self._autoload(
866 metadata,
867 autoload_with,
868 include_columns,
869 _extend_on=_extend_on,
870 _reflect_info=_reflect_info,
871 resolve_fks=resolve_fks,
872 )
874 # initialize all the column, etc. objects. done after reflection to
875 # allow user-overrides
877 self._init_items(
878 *args,
879 allow_replacements=extend_existing
880 or keep_existing
881 or autoload_with,
882 all_names={},
883 )
885 def set_creator_ddl(self, ddl: TableCreateDDL) -> None:
886 """Set the table create DDL for this :class:`.Table`.
888 This allows the CREATE TABLE statement to be controlled or replaced
889 entirely when :meth:`.Table.create` or :meth:`.MetaData.create_all` is
890 used.
892 E.g.::
894 from sqlalchemy.schema import CreateTable
896 table.set_creator_ddl(CreateTable(table, if_not_exists=True))
898 .. versionadded:: 2.1
900 .. seealso::
902 :meth:`.Table.set_dropper_ddl`
904 """
905 self._creator_ddl = ddl
907 def set_dropper_ddl(self, ddl: TableDropDDL) -> None:
908 """Set the table drop DDL for this :class:`.Table`.
910 This allows the DROP TABLE statement to be controlled or replaced
911 entirely when :meth:`.Table.drop` or :meth:`.MetaData.drop_all` is
912 used.
914 E.g.::
916 from sqlalchemy.schema import DropTable
918 table.set_dropper_ddl(DropTable(table, if_exists=True))
920 .. versionadded:: 2.1
922 .. seealso::
924 :meth:`.Table.set_creator_ddl`
926 """
927 self._dropper_ddl = ddl
929 @property
930 def is_view(self) -> bool:
931 """True if this table, when DDL for CREATE is emitted, will emit
932 CREATE VIEW rather than CREATE TABLE.
934 .. versionadded:: 2.1
936 """
937 return isinstance(self._creator_ddl, ddl.CreateView)
939 def _autoload(
940 self,
941 metadata: MetaData,
942 autoload_with: Union[Engine, Connection],
943 include_columns: Optional[Collection[str]],
944 exclude_columns: Collection[str] = (),
945 resolve_fks: bool = True,
946 _extend_on: Optional[Set[Table]] = None,
947 _reflect_info: _ReflectionInfo | None = None,
948 ) -> None:
949 insp = inspection.inspect(autoload_with)
950 with insp._inspection_context() as conn_insp:
951 conn_insp.reflect_table(
952 self,
953 include_columns,
954 exclude_columns,
955 resolve_fks,
956 _extend_on=_extend_on,
957 _reflect_info=_reflect_info,
958 )
960 @property
961 def _sorted_constraints(self) -> List[Constraint]:
962 """Return the set of constraints as a list, sorted by creation
963 order.
965 """
967 return sorted(self.constraints, key=lambda c: c._creation_order)
969 @property
970 def foreign_key_constraints(self) -> Set[ForeignKeyConstraint]:
971 """:class:`_schema.ForeignKeyConstraint` objects referred to by this
972 :class:`_schema.Table`.
974 This list is produced from the collection of
975 :class:`_schema.ForeignKey`
976 objects currently associated.
979 .. seealso::
981 :attr:`_schema.Table.constraints`
983 :attr:`_schema.Table.foreign_keys`
985 :attr:`_schema.Table.indexes`
987 """
988 return {
989 fkc.constraint
990 for fkc in self.foreign_keys
991 if fkc.constraint is not None
992 }
994 def _init_existing(self, *args: Any, **kwargs: Any) -> None:
995 autoload_with = kwargs.pop("autoload_with", None)
996 autoload = kwargs.pop("autoload", autoload_with is not None)
997 autoload_replace = kwargs.pop("autoload_replace", True)
998 schema = kwargs.pop("schema", None)
999 _extend_on = kwargs.pop("_extend_on", None)
1000 _reflect_info = kwargs.pop("_reflect_info", None)
1002 # these arguments are only used with _init()
1003 extend_existing = kwargs.pop("extend_existing", False)
1004 keep_existing = kwargs.pop("keep_existing", False)
1006 assert extend_existing
1007 assert not keep_existing
1009 if schema and schema != self.schema:
1010 raise exc.ArgumentError(
1011 f"Can't change schema of existing table "
1012 f"from '{self.schema}' to '{schema}'",
1013 )
1015 include_columns = kwargs.pop("include_columns", None)
1016 if include_columns is not None:
1017 for c in self.c:
1018 if c.name not in include_columns:
1019 self._columns.remove(c)
1021 resolve_fks = kwargs.pop("resolve_fks", True)
1023 for key in ("quote", "quote_schema"):
1024 if key in kwargs:
1025 raise exc.ArgumentError(
1026 "Can't redefine 'quote' or 'quote_schema' arguments"
1027 )
1029 # update `self` with these kwargs, if provided
1030 self.comment = kwargs.pop("comment", self.comment)
1031 self.implicit_returning = kwargs.pop(
1032 "implicit_returning", self.implicit_returning
1033 )
1034 self.info = kwargs.pop("info", self.info)
1036 exclude_columns: _typing_Sequence[str]
1038 if autoload:
1039 if not autoload_replace:
1040 # don't replace columns already present.
1041 # we'd like to do this for constraints also however we don't
1042 # have simple de-duping for unnamed constraints.
1043 exclude_columns = [c.name for c in self.c]
1044 else:
1045 exclude_columns = ()
1046 self._autoload(
1047 self.metadata,
1048 autoload_with,
1049 include_columns,
1050 exclude_columns,
1051 resolve_fks,
1052 _extend_on=_extend_on,
1053 _reflect_info=_reflect_info,
1054 )
1056 all_names = {c.name: c for c in self.c}
1057 self._extra_kwargs(**kwargs)
1058 self._init_items(*args, allow_replacements=True, all_names=all_names)
1060 def _extra_kwargs(self, **kwargs: Any) -> None:
1061 self._validate_dialect_kwargs(kwargs)
1063 def _init_collections(self) -> None:
1064 pass
1066 def _reset_exported(self) -> None:
1067 pass
1069 @util.ro_non_memoized_property
1070 def _autoincrement_column(self) -> Optional[Column[int]]:
1071 return self.primary_key._autoincrement_column
1073 @util.ro_memoized_property
1074 def _sentinel_column_characteristics(
1075 self,
1076 ) -> _SentinelColumnCharacterization:
1077 """determine a candidate column (or columns, in case of a client
1078 generated composite primary key) which can be used as an
1079 "insert sentinel" for an INSERT statement.
1081 The returned structure, :class:`_SentinelColumnCharacterization`,
1082 includes all the details needed by :class:`.Dialect` and
1083 :class:`.SQLCompiler` to determine if these column(s) can be used
1084 as an INSERT..RETURNING sentinel for a particular database
1085 dialect.
1087 .. versionadded:: 2.0.10
1089 """
1091 sentinel_is_explicit = False
1092 sentinel_is_autoinc = False
1093 the_sentinel: Optional[_typing_Sequence[Column[Any]]] = None
1095 # see if a column was explicitly marked "insert_sentinel=True".
1096 explicit_sentinel_col = self._sentinel_column
1098 if explicit_sentinel_col is not None:
1099 the_sentinel = (explicit_sentinel_col,)
1100 sentinel_is_explicit = True
1102 autoinc_col = self._autoincrement_column
1103 if sentinel_is_explicit and explicit_sentinel_col is autoinc_col:
1104 assert autoinc_col is not None
1105 sentinel_is_autoinc = True
1106 elif explicit_sentinel_col is None and autoinc_col is not None:
1107 the_sentinel = (autoinc_col,)
1108 sentinel_is_autoinc = True
1110 default_characterization = _SentinelDefaultCharacterization.UNKNOWN
1112 if the_sentinel:
1113 the_sentinel_zero = the_sentinel[0]
1114 if the_sentinel_zero.identity:
1115 if the_sentinel_zero.identity._increment_is_negative:
1116 if sentinel_is_explicit:
1117 raise exc.InvalidRequestError(
1118 "Can't use IDENTITY default with negative "
1119 "increment as an explicit sentinel column"
1120 )
1121 else:
1122 if sentinel_is_autoinc:
1123 autoinc_col = None
1124 sentinel_is_autoinc = False
1125 the_sentinel = None
1126 else:
1127 default_characterization = (
1128 _SentinelDefaultCharacterization.IDENTITY
1129 )
1130 elif (
1131 the_sentinel_zero.default is None
1132 and the_sentinel_zero.server_default is None
1133 ):
1134 if the_sentinel_zero.nullable:
1135 raise exc.InvalidRequestError(
1136 f"Column {the_sentinel_zero} has been marked as a "
1137 "sentinel "
1138 "column with no default generation function; it "
1139 "at least needs to be marked nullable=False assuming "
1140 "user-populated sentinel values will be used."
1141 )
1142 default_characterization = (
1143 _SentinelDefaultCharacterization.NONE
1144 )
1145 elif the_sentinel_zero.default is not None:
1146 if the_sentinel_zero.default.is_sentinel:
1147 default_characterization = (
1148 _SentinelDefaultCharacterization.SENTINEL_DEFAULT
1149 )
1150 elif the_sentinel_zero.default._is_monotonic_fn:
1151 default_characterization = (
1152 _SentinelDefaultCharacterization.MONOTONIC_FUNCTION
1153 )
1154 elif default_is_sequence(the_sentinel_zero.default):
1155 if the_sentinel_zero.default._increment_is_negative:
1156 if sentinel_is_explicit:
1157 raise exc.InvalidRequestError(
1158 "Can't use SEQUENCE default with negative "
1159 "increment as an explicit sentinel column"
1160 )
1161 else:
1162 if sentinel_is_autoinc:
1163 autoinc_col = None
1164 sentinel_is_autoinc = False
1165 the_sentinel = None
1167 default_characterization = (
1168 _SentinelDefaultCharacterization.SEQUENCE
1169 )
1170 elif the_sentinel_zero.default.is_callable:
1171 default_characterization = (
1172 _SentinelDefaultCharacterization.CLIENTSIDE
1173 )
1174 elif the_sentinel_zero.server_default is not None:
1175 if sentinel_is_explicit:
1176 if not the_sentinel_zero.server_default._is_monotonic_fn:
1177 raise exc.InvalidRequestError(
1178 f"Column {the_sentinel[0]} can't be a sentinel "
1179 "column "
1180 "because it uses an explicit server side default "
1181 "that's not the Identity() default."
1182 )
1183 else:
1184 default_characterization = (
1185 _SentinelDefaultCharacterization.MONOTONIC_FUNCTION
1186 )
1187 else:
1188 default_characterization = (
1189 _SentinelDefaultCharacterization.SERVERSIDE
1190 )
1192 if the_sentinel is None and self.primary_key:
1193 assert autoinc_col is None
1195 # determine for non-autoincrement pk if all elements are
1196 # client side
1197 for _pkc in self.primary_key:
1198 if (
1199 _pkc.server_default is not None
1200 and not _pkc.server_default._is_monotonic_fn
1201 ):
1202 break
1204 if (
1205 _pkc.default
1206 and not _pkc.default.is_callable
1207 and not _pkc.default._is_monotonic_fn
1208 ):
1209 break
1210 else:
1211 the_sentinel = tuple(self.primary_key)
1212 default_characterization = (
1213 _SentinelDefaultCharacterization.CLIENTSIDE
1214 )
1216 return _SentinelColumnCharacterization(
1217 the_sentinel,
1218 sentinel_is_explicit,
1219 sentinel_is_autoinc,
1220 default_characterization,
1221 )
1223 @property
1224 def autoincrement_column(self) -> Optional[Column[int]]:
1225 """Returns the :class:`.Column` object which currently represents
1226 the "auto increment" column, if any, else returns None.
1228 This is based on the rules for :class:`.Column` as defined by the
1229 :paramref:`.Column.autoincrement` parameter, which generally means the
1230 column within a single integer column primary key constraint that is
1231 not constrained by a foreign key. If the table does not have such
1232 a primary key constraint, then there's no "autoincrement" column.
1233 A :class:`.Table` may have only one column defined as the
1234 "autoincrement" column.
1236 .. versionadded:: 2.0.4
1238 .. seealso::
1240 :paramref:`.Column.autoincrement`
1242 """
1243 return self._autoincrement_column
1245 @property
1246 def key(self) -> str:
1247 """Return the 'key' for this :class:`_schema.Table`.
1249 This value is used as the dictionary key within the
1250 :attr:`_schema.MetaData.tables` collection. It is typically the same
1251 as that of :attr:`_schema.Table.name` for a table with no
1252 :attr:`_schema.Table.schema`
1253 set; otherwise it is typically of the form
1254 ``schemaname.tablename``.
1256 """
1257 return _get_table_key(self.name, self.schema)
1259 def __repr__(self) -> str:
1260 return "Table(%s)" % ", ".join(
1261 [repr(self.name)]
1262 + [repr(self.metadata)]
1263 + [repr(x) for x in self.columns]
1264 + ["%s=%s" % (k, repr(getattr(self, k))) for k in ["schema"]]
1265 )
1267 def __str__(self) -> str:
1268 return _get_table_key(self.description, self.schema)
1270 def add_is_dependent_on(self, table: Table) -> None:
1271 """Add a 'dependency' for this Table.
1273 This is another Table object which must be created
1274 first before this one can, or dropped after this one.
1276 Usually, dependencies between tables are determined via
1277 ForeignKey objects. However, for other situations that
1278 create dependencies outside of foreign keys (rules, inheriting),
1279 this method can manually establish such a link.
1281 """
1282 self._extra_dependencies.add(table)
1284 def _insert_col_impl(
1285 self,
1286 column: ColumnClause[Any],
1287 *,
1288 index: Optional[int] = None,
1289 replace_existing: bool = False,
1290 ) -> None:
1291 try:
1292 column._set_parent_with_dispatch(
1293 self,
1294 allow_replacements=replace_existing,
1295 all_names={c.name: c for c in self.c},
1296 index=index,
1297 )
1298 except exc.DuplicateColumnError as de:
1299 raise exc.DuplicateColumnError(
1300 f"{de.args[0]} Specify replace_existing=True to "
1301 "Table.append_column() or Table.insert_column() to replace an "
1302 "existing column."
1303 ) from de
1305 def insert_column(
1306 self,
1307 column: ColumnClause[Any],
1308 index: int,
1309 *,
1310 replace_existing: bool = False,
1311 ) -> None:
1312 """Insert a :class:`_schema.Column` to this :class:`_schema.Table` at
1313 a specific position.
1315 Behavior is identical to :meth:`.Table.append_column` except that
1316 the index position can be controlled using the
1317 :paramref:`.Table.insert_column.index`
1318 parameter.
1320 :param replace_existing:
1321 see :paramref:`.Table.append_column.replace_existing`
1322 :param index: integer index to insert the new column.
1324 .. versionadded:: 2.1
1326 """
1327 self._insert_col_impl(
1328 column, index=index, replace_existing=replace_existing
1329 )
1331 def append_column(
1332 self, column: ColumnClause[Any], *, replace_existing: bool = False
1333 ) -> None:
1334 """Append a :class:`_schema.Column` to this :class:`_schema.Table`.
1336 The "key" of the newly added :class:`_schema.Column`, i.e. the
1337 value of its ``.key`` attribute, will then be available
1338 in the ``.c`` collection of this :class:`_schema.Table`, and the
1339 column definition will be included in any CREATE TABLE, SELECT,
1340 UPDATE, etc. statements generated from this :class:`_schema.Table`
1341 construct.
1343 Note that this does **not** change the definition of the table
1344 as it exists within any underlying database, assuming that
1345 table has already been created in the database. Relational
1346 databases support the addition of columns to existing tables
1347 using the SQL ALTER command, which would need to be
1348 emitted for an already-existing table that doesn't contain
1349 the newly added column.
1351 :param replace_existing: When ``True``, allows replacing existing
1352 columns. When ``False``, the default, an warning will be raised
1353 if a column with the same ``.key`` already exists. A future
1354 version of sqlalchemy will instead rise a warning.
1356 .. versionadded:: 1.4.0
1358 .. seealso::
1360 :meth:`.Table.insert_column`
1362 """
1363 self._insert_col_impl(column, replace_existing=replace_existing)
1365 def append_constraint(self, constraint: Union[Index, Constraint]) -> None:
1366 """Append a :class:`_schema.Constraint` to this
1367 :class:`_schema.Table`.
1369 This has the effect of the constraint being included in any
1370 future CREATE TABLE statement, assuming specific DDL creation
1371 events have not been associated with the given
1372 :class:`_schema.Constraint` object.
1374 Note that this does **not** produce the constraint within the
1375 relational database automatically, for a table that already exists
1376 in the database. To add a constraint to an
1377 existing relational database table, the SQL ALTER command must
1378 be used. SQLAlchemy also provides the
1379 :class:`.AddConstraint` construct which can produce this SQL when
1380 invoked as an executable clause.
1382 """
1384 constraint._set_parent_with_dispatch(self)
1386 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
1387 metadata = parent
1388 assert isinstance(metadata, MetaData)
1389 metadata._add_table(self.name, self.schema, self)
1390 self.metadata = metadata
1392 def create(
1393 self,
1394 bind: _CreateDropBind,
1395 checkfirst: Union[bool, CheckFirst] = CheckFirst.TYPES,
1396 ) -> None:
1397 """Issue a ``CREATE`` statement for this
1398 :class:`_schema.Table`, using the given
1399 :class:`.Connection` or :class:`.Engine`
1400 for connectivity.
1402 .. seealso::
1404 :meth:`_schema.MetaData.create_all`.
1406 """
1408 # the default is to only check for schema objects
1409 bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
1411 def drop(
1412 self,
1413 bind: _CreateDropBind,
1414 checkfirst: Union[bool, CheckFirst] = CheckFirst.NONE,
1415 ) -> None:
1416 """Issue a ``DROP`` statement for this
1417 :class:`_schema.Table`, using the given
1418 :class:`.Connection` or :class:`.Engine` for connectivity.
1420 .. seealso::
1422 :meth:`_schema.MetaData.drop_all`.
1424 """
1425 bind._run_ddl_visitor(ddl.SchemaDropper, self, checkfirst=checkfirst)
1427 @util.deprecated(
1428 "1.4",
1429 ":meth:`_schema.Table.tometadata` is renamed to "
1430 ":meth:`_schema.Table.to_metadata`",
1431 )
1432 def tometadata(
1433 self,
1434 metadata: MetaData,
1435 schema: Union[str, Literal[SchemaConst.RETAIN_SCHEMA]] = RETAIN_SCHEMA,
1436 referred_schema_fn: Optional[
1437 Callable[
1438 [Table, Optional[str], ForeignKeyConstraint, Optional[str]],
1439 Optional[str],
1440 ]
1441 ] = None,
1442 name: Optional[str] = None,
1443 ) -> Table:
1444 """Return a copy of this :class:`_schema.Table`
1445 associated with a different
1446 :class:`_schema.MetaData`.
1448 See :meth:`_schema.Table.to_metadata` for a full description.
1450 """
1451 return self.to_metadata(
1452 metadata,
1453 schema=schema,
1454 referred_schema_fn=referred_schema_fn,
1455 name=name,
1456 )
1458 def to_metadata(
1459 self,
1460 metadata: MetaData,
1461 schema: Union[str, Literal[SchemaConst.RETAIN_SCHEMA]] = RETAIN_SCHEMA,
1462 referred_schema_fn: Optional[
1463 Callable[
1464 [Table, Optional[str], ForeignKeyConstraint, Optional[str]],
1465 Optional[str],
1466 ]
1467 ] = None,
1468 name: Optional[str] = None,
1469 ) -> Table:
1470 """Return a copy of this :class:`_schema.Table` associated with a
1471 different :class:`_schema.MetaData`.
1473 E.g.::
1475 m1 = MetaData()
1477 user = Table("user", m1, Column("id", Integer, primary_key=True))
1479 m2 = MetaData()
1480 user_copy = user.to_metadata(m2)
1482 .. versionchanged:: 1.4 The :meth:`_schema.Table.to_metadata` function
1483 was renamed from :meth:`_schema.Table.tometadata`.
1486 :param metadata: Target :class:`_schema.MetaData` object,
1487 into which the
1488 new :class:`_schema.Table` object will be created.
1490 :param schema: optional string name indicating the target schema.
1491 Defaults to the special symbol :attr:`.RETAIN_SCHEMA` which indicates
1492 that no change to the schema name should be made in the new
1493 :class:`_schema.Table`. If set to a string name, the new
1494 :class:`_schema.Table`
1495 will have this new name as the ``.schema``. If set to ``None``, the
1496 schema will be set to that of the schema set on the target
1497 :class:`_schema.MetaData`, which is typically ``None`` as well,
1498 unless
1499 set explicitly::
1501 m2 = MetaData(schema="newschema")
1503 # user_copy_one will have "newschema" as the schema name
1504 user_copy_one = user.to_metadata(m2, schema=None)
1506 m3 = MetaData() # schema defaults to None
1508 # user_copy_two will have None as the schema name
1509 user_copy_two = user.to_metadata(m3, schema=None)
1511 :param referred_schema_fn: optional callable which can be supplied
1512 in order to provide for the schema name that should be assigned
1513 to the referenced table of a :class:`_schema.ForeignKeyConstraint`.
1514 The callable accepts this parent :class:`_schema.Table`, the
1515 target schema that we are changing to, the
1516 :class:`_schema.ForeignKeyConstraint` object, and the existing
1517 "target schema" of that constraint. The function should return the
1518 string schema name that should be applied. To reset the schema
1519 to "none", return the symbol :data:`.BLANK_SCHEMA`. To effect no
1520 change, return ``None`` or :data:`.RETAIN_SCHEMA`.
1522 .. versionchanged:: 1.4.33 The ``referred_schema_fn`` function
1523 may return the :data:`.BLANK_SCHEMA` or :data:`.RETAIN_SCHEMA`
1524 symbols.
1526 E.g.::
1528 def referred_schema_fn(table, to_schema, constraint, referred_schema):
1529 if referred_schema == "base_tables":
1530 return referred_schema
1531 else:
1532 return to_schema
1535 new_table = table.to_metadata(
1536 m2, schema="alt_schema", referred_schema_fn=referred_schema_fn
1537 )
1539 :param name: optional string name indicating the target table name.
1540 If not specified or None, the table name is retained. This allows
1541 a :class:`_schema.Table` to be copied to the same
1542 :class:`_schema.MetaData` target
1543 with a new name.
1545 """ # noqa: E501
1546 if name is None:
1547 name = self.name
1549 actual_schema: Optional[str]
1551 if schema is RETAIN_SCHEMA:
1552 actual_schema = self.schema
1553 elif schema is None:
1554 actual_schema = metadata.schema
1555 else:
1556 actual_schema = schema
1557 key = _get_table_key(name, actual_schema)
1558 if key in metadata.tables:
1559 util.warn(
1560 f"Table '{self.description}' already exists within the given "
1561 "MetaData - not copying."
1562 )
1563 return metadata.tables[key]
1565 args = []
1566 for col in self.columns:
1567 args.append(col._copy(schema=actual_schema, _to_metadata=metadata))
1569 table = Table(
1570 name,
1571 metadata,
1572 schema=actual_schema,
1573 comment=self.comment,
1574 *args,
1575 **self.kwargs,
1576 )
1578 if self._creator_ddl is not None:
1579 table._creator_ddl = self._creator_ddl.to_metadata(metadata, table)
1580 if self._dropper_ddl is not None:
1581 table._dropper_ddl = self._dropper_ddl.to_metadata(metadata, table)
1583 for const in self.constraints:
1584 if isinstance(const, ForeignKeyConstraint):
1585 referred_schema = const._referred_schema
1586 if referred_schema_fn:
1587 fk_constraint_schema = referred_schema_fn(
1588 self, actual_schema, const, referred_schema
1589 )
1590 else:
1591 fk_constraint_schema = (
1592 actual_schema
1593 if referred_schema == self.schema
1594 else None
1595 )
1596 table.append_constraint(
1597 const._copy(
1598 schema=fk_constraint_schema, target_table=table
1599 )
1600 )
1601 elif not const._type_bound:
1602 # skip unique constraints that would be generated
1603 # by the 'unique' flag on Column
1604 if const._column_flag:
1605 continue
1607 table.append_constraint(
1608 const._copy(schema=actual_schema, target_table=table)
1609 )
1610 for index in self.indexes:
1611 # skip indexes that would be generated
1612 # by the 'index' flag on Column
1613 if index._column_flag:
1614 continue
1615 Index(
1616 index.name,
1617 unique=index.unique,
1618 *[
1619 _copy_expression(expr, self, table)
1620 for expr in index._table_bound_expressions
1621 ],
1622 _table=table,
1623 **index.kwargs,
1624 )
1625 return self._schema_item_copy(table)
1628class Column(DialectKWArgs, SchemaItem, ColumnClause[_T]):
1629 """Represents a column in a database table."""
1631 __visit_name__ = "column"
1633 inherit_cache = True
1634 key: str
1636 server_default: Optional[FetchedValue]
1638 def __init__(
1639 self,
1640 __name_pos: Optional[
1641 Union[str, _TypeEngineArgument[_T], SchemaEventTarget]
1642 ] = None,
1643 __type_pos: Optional[
1644 Union[_TypeEngineArgument[_T], SchemaEventTarget]
1645 ] = None,
1646 /,
1647 *args: SchemaEventTarget,
1648 name: Optional[str] = None,
1649 type_: Optional[_TypeEngineArgument[_T]] = None,
1650 autoincrement: _AutoIncrementType = "auto",
1651 default: Optional[Any] = _NoArg.NO_ARG,
1652 insert_default: Optional[Any] = _NoArg.NO_ARG,
1653 doc: Optional[str] = None,
1654 key: Optional[str] = None,
1655 index: Optional[bool] = None,
1656 unique: Optional[bool] = None,
1657 info: Optional[_InfoType] = None,
1658 nullable: Optional[
1659 Union[bool, Literal[SchemaConst.NULL_UNSPECIFIED]]
1660 ] = SchemaConst.NULL_UNSPECIFIED,
1661 onupdate: Optional[Any] = None,
1662 primary_key: bool = False,
1663 server_default: Optional[_ServerDefaultArgument] = None,
1664 server_onupdate: Optional[_ServerOnUpdateArgument] = None,
1665 quote: Optional[bool] = None,
1666 system: bool = False,
1667 comment: Optional[str] = None,
1668 insert_sentinel: bool = False,
1669 _omit_from_statements: bool = False,
1670 _proxies: Optional[Any] = None,
1671 **dialect_kwargs: Any,
1672 ):
1673 r"""
1674 Construct a new ``Column`` object.
1676 :param name: The name of this column as represented in the database.
1677 This argument may be the first positional argument, or specified
1678 via keyword.
1680 Names which contain no upper case characters
1681 will be treated as case insensitive names, and will not be quoted
1682 unless they are a reserved word. Names with any number of upper
1683 case characters will be quoted and sent exactly. Note that this
1684 behavior applies even for databases which standardize upper
1685 case names as case insensitive such as Oracle Database.
1687 The name field may be omitted at construction time and applied
1688 later, at any time before the Column is associated with a
1689 :class:`_schema.Table`. This is to support convenient
1690 usage within the :mod:`~sqlalchemy.ext.declarative` extension.
1692 :param type\_: The column's type, indicated using an instance which
1693 subclasses :class:`~sqlalchemy.types.TypeEngine`. If no arguments
1694 are required for the type, the class of the type can be sent
1695 as well, e.g.::
1697 # use a type with arguments
1698 Column("data", String(50))
1700 # use no arguments
1701 Column("level", Integer)
1703 The ``type`` argument may be the second positional argument
1704 or specified by keyword.
1706 If the ``type`` is ``None`` or is omitted, it will first default to
1707 the special type :class:`.NullType`. If and when this
1708 :class:`_schema.Column` is made to refer to another column using
1709 :class:`_schema.ForeignKey` and/or
1710 :class:`_schema.ForeignKeyConstraint`, the type
1711 of the remote-referenced column will be copied to this column as
1712 well, at the moment that the foreign key is resolved against that
1713 remote :class:`_schema.Column` object.
1715 :param \*args: Additional positional arguments include various
1716 :class:`.SchemaItem` derived constructs which will be applied
1717 as options to the column. These include instances of
1718 :class:`.Constraint`, :class:`_schema.ForeignKey`,
1719 :class:`.ColumnDefault`, :class:`.Sequence`, :class:`.Computed`
1720 :class:`.Identity`. In some cases an
1721 equivalent keyword argument is available such as ``server_default``,
1722 ``default`` and ``unique``.
1724 :param autoincrement: Set up "auto increment" semantics for an
1725 **integer primary key column with no foreign key dependencies**
1726 (see later in this docstring for a more specific definition).
1727 This may influence the :term:`DDL` that will be emitted for
1728 this column during a table create, as well as how the column
1729 will be considered when INSERT statements are compiled and
1730 executed.
1732 The default value is the string ``"auto"``,
1733 which indicates that a single-column (i.e. non-composite) primary key
1734 that is of an INTEGER type with no other client-side or server-side
1735 default constructs indicated should receive auto increment semantics
1736 automatically. Other values include ``True`` (force this column to
1737 have auto-increment semantics for a :term:`composite primary key` as
1738 well), ``False`` (this column should never have auto-increment
1739 semantics), and the string ``"ignore_fk"`` (special-case for foreign
1740 key columns, see below).
1742 The term "auto increment semantics" refers both to the kind of DDL
1743 that will be emitted for the column within a CREATE TABLE statement,
1744 when methods such as :meth:`.MetaData.create_all` and
1745 :meth:`.Table.create` are invoked, as well as how the column will be
1746 considered when an INSERT statement is compiled and emitted to the
1747 database:
1749 * **DDL rendering** (i.e. :meth:`.MetaData.create_all`,
1750 :meth:`.Table.create`): When used on a :class:`.Column` that has
1751 no other
1752 default-generating construct associated with it (such as a
1753 :class:`.Sequence` or :class:`.Identity` construct), the parameter
1754 will imply that database-specific keywords such as PostgreSQL
1755 ``SERIAL``, MySQL ``AUTO_INCREMENT``, or ``IDENTITY`` on SQL Server
1756 should also be rendered. Not every database backend has an
1757 "implied" default generator available; for example the Oracle Database
1758 backends alway needs an explicit construct such as
1759 :class:`.Identity` to be included with a :class:`.Column` in order
1760 for the DDL rendered to include auto-generating constructs to also
1761 be produced in the database.
1763 * **INSERT semantics** (i.e. when a :func:`_sql.insert` construct is
1764 compiled into a SQL string and is then executed on a database using
1765 :meth:`_engine.Connection.execute` or equivalent): A single-row
1766 INSERT statement will be known to produce a new integer primary key
1767 value automatically for this column, which will be accessible
1768 after the statement is invoked via the
1769 :attr:`.CursorResult.inserted_primary_key` attribute upon the
1770 :class:`_result.Result` object. This also applies towards use of the
1771 ORM when ORM-mapped objects are persisted to the database,
1772 indicating that a new integer primary key will be available to
1773 become part of the :term:`identity key` for that object. This
1774 behavior takes place regardless of what DDL constructs are
1775 associated with the :class:`_schema.Column` and is independent
1776 of the "DDL Rendering" behavior discussed in the previous note
1777 above.
1779 The parameter may be set to ``True`` to indicate that a column which
1780 is part of a composite (i.e. multi-column) primary key should
1781 have autoincrement semantics, though note that only one column
1782 within a primary key may have this setting. It can also
1783 be set to ``True`` to indicate autoincrement semantics on a
1784 column that has a client-side or server-side default configured,
1785 however note that not all dialects can accommodate all styles
1786 of default as an "autoincrement". It can also be
1787 set to ``False`` on a single-column primary key that has a
1788 datatype of INTEGER in order to disable auto increment semantics
1789 for that column.
1791 The setting *only* has an effect for columns which are:
1793 * Integer derived (i.e. INT, SMALLINT, BIGINT).
1795 * Part of the primary key
1797 * Not referring to another column via :class:`_schema.ForeignKey`,
1798 unless
1799 the value is specified as ``'ignore_fk'``::
1801 # turn on autoincrement for this column despite
1802 # the ForeignKey()
1803 Column(
1804 "id",
1805 ForeignKey("other.id"),
1806 primary_key=True,
1807 autoincrement="ignore_fk",
1808 )
1810 It is typically not desirable to have "autoincrement" enabled on a
1811 column that refers to another via foreign key, as such a column is
1812 required to refer to a value that originates from elsewhere.
1814 The setting has these effects on columns that meet the
1815 above criteria:
1817 * DDL issued for the column, if the column does not already include
1818 a default generating construct supported by the backend such as
1819 :class:`.Identity`, will include database-specific
1820 keywords intended to signify this column as an
1821 "autoincrement" column for specific backends. Behavior for
1822 primary SQLAlchemy dialects includes:
1824 * AUTO INCREMENT on MySQL and MariaDB
1825 * SERIAL on PostgreSQL
1826 * IDENTITY on MS-SQL - this occurs even without the
1827 :class:`.Identity` construct as the
1828 :paramref:`.Column.autoincrement` parameter pre-dates this
1829 construct.
1830 * SQLite - SQLite integer primary key columns are implicitly
1831 "auto incrementing" and no additional keywords are rendered;
1832 to render the special SQLite keyword ``AUTOINCREMENT``
1833 is not included as this is unnecessary and not recommended
1834 by the database vendor. See the section
1835 :ref:`sqlite_autoincrement` for more background.
1836 * Oracle Database - The Oracle Database dialects have no default "autoincrement"
1837 feature available at this time, instead the :class:`.Identity`
1838 construct is recommended to achieve this (the :class:`.Sequence`
1839 construct may also be used).
1840 * Third-party dialects - consult those dialects' documentation
1841 for details on their specific behaviors.
1843 * When a single-row :func:`_sql.insert` construct is compiled and
1844 executed, which does not set the :meth:`_sql.Insert.inline`
1845 modifier, newly generated primary key values for this column
1846 will be automatically retrieved upon statement execution
1847 using a method specific to the database driver in use:
1849 * MySQL, SQLite - calling upon ``cursor.lastrowid()``
1850 (see
1851 `https://www.python.org/dev/peps/pep-0249/#lastrowid
1852 <https://www.python.org/dev/peps/pep-0249/#lastrowid>`_)
1853 * PostgreSQL, SQL Server, Oracle Database - use RETURNING or an equivalent
1854 construct when rendering an INSERT statement, and then retrieving
1855 the newly generated primary key values after execution
1856 * PostgreSQL, Oracle Database for :class:`_schema.Table` objects that
1857 set :paramref:`_schema.Table.implicit_returning` to False -
1858 for a :class:`.Sequence` only, the :class:`.Sequence` is invoked
1859 explicitly before the INSERT statement takes place so that the
1860 newly generated primary key value is available to the client
1861 * SQL Server for :class:`_schema.Table` objects that
1862 set :paramref:`_schema.Table.implicit_returning` to False -
1863 the ``SELECT scope_identity()`` construct is used after the
1864 INSERT statement is invoked to retrieve the newly generated
1865 primary key value.
1866 * Third-party dialects - consult those dialects' documentation
1867 for details on their specific behaviors.
1869 * For multiple-row :func:`_sql.insert` constructs invoked with
1870 a list of parameters (i.e. "executemany" semantics), primary-key
1871 retrieving behaviors are generally disabled, however there may
1872 be special APIs that may be used to retrieve lists of new
1873 primary key values for an "executemany", such as the psycopg2
1874 "fast insertmany" feature. Such features are very new and
1875 may not yet be well covered in documentation.
1877 :param default: A scalar, Python callable, or
1878 :class:`_expression.ColumnElement` expression representing the
1879 *default value* for this column, which will be invoked upon insert
1880 if this column is otherwise not specified in the VALUES clause of
1881 the insert. This is a shortcut to using :class:`.ColumnDefault` as
1882 a positional argument; see that class for full detail on the
1883 structure of the argument.
1885 Contrast this argument to
1886 :paramref:`_schema.Column.server_default`
1887 which creates a default generator on the database side.
1889 .. seealso::
1891 :ref:`metadata_defaults_toplevel`
1893 :param insert_default: An alias of :paramref:`.Column.default`
1894 for compatibility with :func:`_orm.mapped_column`.
1896 .. versionadded:: 2.0.31
1898 :param doc: optional String that can be used by the ORM or similar
1899 to document attributes on the Python side. This attribute does
1900 **not** render SQL comments; use the
1901 :paramref:`_schema.Column.comment`
1902 parameter for this purpose.
1904 :param key: An optional string identifier which will identify this
1905 ``Column`` object on the :class:`_schema.Table`.
1906 When a key is provided,
1907 this is the only identifier referencing the ``Column`` within the
1908 application, including ORM attribute mapping; the ``name`` field
1909 is used only when rendering SQL.
1911 :param index: When ``True``, indicates that a :class:`_schema.Index`
1912 construct will be automatically generated for this
1913 :class:`_schema.Column`, which will result in a "CREATE INDEX"
1914 statement being emitted for the :class:`_schema.Table` when the DDL
1915 create operation is invoked.
1917 Using this flag is equivalent to making use of the
1918 :class:`_schema.Index` construct explicitly at the level of the
1919 :class:`_schema.Table` construct itself::
1921 Table(
1922 "some_table",
1923 metadata,
1924 Column("x", Integer),
1925 Index("ix_some_table_x", "x"),
1926 )
1928 To add the :paramref:`_schema.Index.unique` flag to the
1929 :class:`_schema.Index`, set both the
1930 :paramref:`_schema.Column.unique` and
1931 :paramref:`_schema.Column.index` flags to True simultaneously,
1932 which will have the effect of rendering the "CREATE UNIQUE INDEX"
1933 DDL instruction instead of "CREATE INDEX".
1935 The name of the index is generated using the
1936 :ref:`default naming convention <constraint_default_naming_convention>`
1937 which for the :class:`_schema.Index` construct is of the form
1938 ``ix_<tablename>_<columnname>``.
1940 As this flag is intended only as a convenience for the common case
1941 of adding a single-column, default configured index to a table
1942 definition, explicit use of the :class:`_schema.Index` construct
1943 should be preferred for most use cases, including composite indexes
1944 that encompass more than one column, indexes with SQL expressions
1945 or ordering, backend-specific index configuration options, and
1946 indexes that use a specific name.
1948 .. note:: the :attr:`_schema.Column.index` attribute on
1949 :class:`_schema.Column`
1950 **does not indicate** if this column is indexed or not, only
1951 if this flag was explicitly set here. To view indexes on
1952 a column, view the :attr:`_schema.Table.indexes` collection
1953 or use :meth:`_reflection.Inspector.get_indexes`.
1955 .. seealso::
1957 :ref:`schema_indexes`
1959 :ref:`constraint_naming_conventions`
1961 :paramref:`_schema.Column.unique`
1963 :param info: Optional data dictionary which will be populated into the
1964 :attr:`.SchemaItem.info` attribute of this object.
1966 :param nullable: When set to ``False``, will cause the "NOT NULL"
1967 phrase to be added when generating DDL for the column. When
1968 ``True``, will normally generate nothing (in SQL this defaults to
1969 "NULL"), except in some very specific backend-specific edge cases
1970 where "NULL" may render explicitly.
1971 Defaults to ``True`` unless :paramref:`_schema.Column.primary_key`
1972 is also ``True`` or the column specifies a :class:`_sql.Identity`,
1973 in which case it defaults to ``False``.
1974 This parameter is only used when issuing CREATE TABLE statements.
1976 .. note::
1978 When the column specifies a :class:`_sql.Identity` this
1979 parameter is in general ignored by the DDL compiler. The
1980 PostgreSQL database allows nullable identity column by
1981 setting this parameter to ``True`` explicitly.
1983 :param onupdate: A scalar, Python callable, or
1984 :class:`~sqlalchemy.sql.expression.ClauseElement` representing a
1985 default value to be applied to the column within UPDATE
1986 statements, which will be invoked upon update if this column is not
1987 present in the SET clause of the update. This is a shortcut to
1988 using :class:`.ColumnDefault` as a positional argument with
1989 ``for_update=True``.
1991 .. seealso::
1993 :ref:`metadata_defaults` - complete discussion of onupdate
1995 :param primary_key: If ``True``, marks this column as a primary key
1996 column. Multiple columns can have this flag set to specify
1997 composite primary keys. As an alternative, the primary key of a
1998 :class:`_schema.Table` can be specified via an explicit
1999 :class:`.PrimaryKeyConstraint` object.
2001 :param server_default: A :class:`.FetchedValue` instance, str, Unicode
2002 or :func:`~sqlalchemy.sql.expression.text` construct representing
2003 the DDL DEFAULT value for the column.
2005 String types will be emitted as-is, surrounded by single quotes::
2007 Column("x", Text, server_default="val")
2009 will render:
2011 .. sourcecode:: sql
2013 x TEXT DEFAULT 'val'
2015 A :func:`~sqlalchemy.sql.expression.text` expression will be
2016 rendered as-is, without quotes::
2018 Column("y", DateTime, server_default=text("NOW()"))
2020 will render:
2022 .. sourcecode:: sql
2024 y DATETIME DEFAULT NOW()
2026 Strings and text() will be converted into a
2027 :class:`.DefaultClause` object upon initialization.
2029 This parameter can also accept complex combinations of contextually
2030 valid SQLAlchemy expressions or constructs::
2032 from sqlalchemy import create_engine
2033 from sqlalchemy import Table, Column, MetaData, ARRAY, Text
2034 from sqlalchemy.dialects.postgresql import array
2036 engine = create_engine(
2037 "postgresql+psycopg2://scott:tiger@localhost/mydatabase"
2038 )
2039 metadata_obj = MetaData()
2040 tbl = Table(
2041 "foo",
2042 metadata_obj,
2043 Column(
2044 "bar", ARRAY(Text), server_default=array(["biz", "bang", "bash"])
2045 ),
2046 )
2047 metadata_obj.create_all(engine)
2049 The above results in a table created with the following SQL:
2051 .. sourcecode:: sql
2053 CREATE TABLE foo (
2054 bar TEXT[] DEFAULT ARRAY['biz', 'bang', 'bash']
2055 )
2057 Use :class:`.FetchedValue` to indicate that an already-existing
2058 column will generate a default value on the database side which
2059 will be available to SQLAlchemy for post-fetch after inserts. This
2060 construct does not specify any DDL and the implementation is left
2061 to the database, such as via a trigger.
2063 .. seealso::
2065 :ref:`server_defaults` - complete discussion of server side
2066 defaults
2068 :param server_onupdate: A :class:`.FetchedValue` instance
2069 representing a database-side default generation function,
2070 such as a trigger. This
2071 indicates to SQLAlchemy that a newly generated value will be
2072 available after updates. This construct does not actually
2073 implement any kind of generation function within the database,
2074 which instead must be specified separately.
2077 .. warning:: This directive **does not** currently produce MySQL's
2078 "ON UPDATE CURRENT_TIMESTAMP()" clause. See
2079 :ref:`mysql_timestamp_onupdate` for background on how to
2080 produce this clause.
2082 .. seealso::
2084 :ref:`triggered_columns`
2086 :param quote: Force quoting of this column's name on or off,
2087 corresponding to ``True`` or ``False``. When left at its default
2088 of ``None``, the column identifier will be quoted according to
2089 whether the name is case sensitive (identifiers with at least one
2090 upper case character are treated as case sensitive), or if it's a
2091 reserved word. This flag is only needed to force quoting of a
2092 reserved word which is not known by the SQLAlchemy dialect.
2094 :param unique: When ``True``, and the :paramref:`_schema.Column.index`
2095 parameter is left at its default value of ``False``,
2096 indicates that a :class:`_schema.UniqueConstraint`
2097 construct will be automatically generated for this
2098 :class:`_schema.Column`,
2099 which will result in a "UNIQUE CONSTRAINT" clause referring
2100 to this column being included
2101 in the ``CREATE TABLE`` statement emitted, when the DDL create
2102 operation for the :class:`_schema.Table` object is invoked.
2104 When this flag is ``True`` while the
2105 :paramref:`_schema.Column.index` parameter is simultaneously
2106 set to ``True``, the effect instead is that a
2107 :class:`_schema.Index` construct which includes the
2108 :paramref:`_schema.Index.unique` parameter set to ``True``
2109 is generated. See the documentation for
2110 :paramref:`_schema.Column.index` for additional detail.
2112 Using this flag is equivalent to making use of the
2113 :class:`_schema.UniqueConstraint` construct explicitly at the
2114 level of the :class:`_schema.Table` construct itself::
2116 Table("some_table", metadata, Column("x", Integer), UniqueConstraint("x"))
2118 The :paramref:`_schema.UniqueConstraint.name` parameter
2119 of the unique constraint object is left at its default value
2120 of ``None``; in the absence of a :ref:`naming convention <constraint_naming_conventions>`
2121 for the enclosing :class:`_schema.MetaData`, the UNIQUE CONSTRAINT
2122 construct will be emitted as unnamed, which typically invokes
2123 a database-specific naming convention to take place.
2125 As this flag is intended only as a convenience for the common case
2126 of adding a single-column, default configured unique constraint to a table
2127 definition, explicit use of the :class:`_schema.UniqueConstraint` construct
2128 should be preferred for most use cases, including composite constraints
2129 that encompass more than one column, backend-specific index configuration options, and
2130 constraints that use a specific name.
2132 .. note:: the :attr:`_schema.Column.unique` attribute on
2133 :class:`_schema.Column`
2134 **does not indicate** if this column has a unique constraint or
2135 not, only if this flag was explicitly set here. To view
2136 indexes and unique constraints that may involve this column,
2137 view the
2138 :attr:`_schema.Table.indexes` and/or
2139 :attr:`_schema.Table.constraints` collections or use
2140 :meth:`_reflection.Inspector.get_indexes` and/or
2141 :meth:`_reflection.Inspector.get_unique_constraints`
2143 .. seealso::
2145 :ref:`schema_unique_constraint`
2147 :ref:`constraint_naming_conventions`
2149 :paramref:`_schema.Column.index`
2151 :param system: When ``True``, indicates this is a "system" column,
2152 that is a column which is automatically made available by the
2153 database, and should not be included in the columns list for a
2154 ``CREATE TABLE`` statement.
2156 For more elaborate scenarios where columns should be
2157 conditionally rendered differently on different backends,
2158 consider custom compilation rules for :class:`.CreateColumn`.
2160 :param comment: Optional string that will render an SQL comment on
2161 table creation.
2163 :param insert_sentinel: Marks this :class:`_schema.Column` as an
2164 :term:`insert sentinel` used for optimizing the performance of the
2165 :term:`insertmanyvalues` feature for tables that don't
2166 otherwise have qualifying primary key configurations.
2168 .. versionadded:: 2.0.10
2170 .. seealso::
2172 :func:`_schema.insert_sentinel` - all in one helper for declaring
2173 sentinel columns
2175 :ref:`engine_insertmanyvalues`
2177 :ref:`engine_insertmanyvalues_sentinel_columns`
2180 """ # noqa: E501, RST201, RST202
2182 l_args = [__name_pos, __type_pos] + list(args)
2183 del args
2185 if l_args:
2186 if isinstance(l_args[0], str):
2187 if name is not None:
2188 raise exc.ArgumentError(
2189 "May not pass name positionally and as a keyword."
2190 )
2191 name = l_args.pop(0) # type: ignore
2192 elif l_args[0] is None:
2193 l_args.pop(0)
2194 if l_args:
2195 coltype = l_args[0]
2197 if hasattr(coltype, "_sqla_type"):
2198 if type_ is not None:
2199 raise exc.ArgumentError(
2200 "May not pass type_ positionally and as a keyword."
2201 )
2202 type_ = l_args.pop(0) # type: ignore
2203 elif l_args[0] is None:
2204 l_args.pop(0)
2206 if name is not None:
2207 name = quoted_name(name, quote)
2208 elif quote is not None:
2209 raise exc.ArgumentError(
2210 "Explicit 'name' is required when sending 'quote' argument"
2211 )
2213 # name = None is expected to be an interim state
2214 # note this use case is legacy now that ORM declarative has a
2215 # dedicated "column" construct local to the ORM
2216 super().__init__(name, type_) # type: ignore
2218 self.key = key if key is not None else name # type: ignore
2219 self.primary_key = primary_key
2220 self._insert_sentinel = insert_sentinel
2221 self._omit_from_statements = _omit_from_statements
2222 self._user_defined_nullable = udn = nullable
2223 if udn is not NULL_UNSPECIFIED:
2224 self.nullable = udn
2225 else:
2226 self.nullable = not primary_key
2228 # these default to None because .index and .unique is *not*
2229 # an informational flag about Column - there can still be an
2230 # Index or UniqueConstraint referring to this Column.
2231 self.index = index
2232 self.unique = unique
2234 self.system = system
2235 self.doc = doc
2236 self.autoincrement: _AutoIncrementType = autoincrement
2237 self.constraints = set()
2238 self.foreign_keys = set()
2239 self.comment = comment
2240 self.computed = None
2241 self.identity = None
2243 # check if this Column is proxying another column
2245 if _proxies is not None:
2246 self._proxies = _proxies
2247 else:
2248 # otherwise, add DDL-related events
2249 self._set_type(self.type)
2251 if insert_default is not _NoArg.NO_ARG:
2252 if default is not _NoArg.NO_ARG:
2253 raise exc.ArgumentError(
2254 "The 'default' and 'insert_default' parameters "
2255 "of Column are mutually exclusive"
2256 )
2257 resolved_default = insert_default
2258 elif default is not _NoArg.NO_ARG:
2259 resolved_default = default
2260 else:
2261 resolved_default = None
2263 if resolved_default is not None:
2264 if not isinstance(resolved_default, (ColumnDefault, Sequence)):
2265 resolved_default = ColumnDefault(resolved_default)
2267 self.default = resolved_default
2268 l_args.append(resolved_default)
2269 else:
2270 self.default = None
2272 if onupdate is not None:
2273 if not isinstance(onupdate, (ColumnDefault, Sequence)):
2274 onupdate = ColumnDefault(onupdate, for_update=True)
2276 self.onupdate = onupdate
2277 l_args.append(onupdate)
2278 else:
2279 self.onupdate = None
2281 if server_default is not None:
2282 if isinstance(server_default, FetchedValue):
2283 server_default = server_default._as_for_update(False)
2284 l_args.append(server_default)
2285 else:
2286 server_default = DefaultClause(server_default)
2287 l_args.append(server_default)
2288 self.server_default = server_default
2290 if server_onupdate is not None:
2291 if isinstance(server_onupdate, FetchedValue):
2292 server_onupdate = server_onupdate._as_for_update(True)
2293 l_args.append(server_onupdate)
2294 else:
2295 server_onupdate = DefaultClause(
2296 server_onupdate, for_update=True
2297 )
2298 l_args.append(server_onupdate)
2299 self.server_onupdate = server_onupdate
2301 self._init_items(*cast(_typing_Sequence[SchemaItem], l_args))
2303 util.set_creation_order(self)
2305 if info is not None:
2306 self.info = info
2308 self._extra_kwargs(**dialect_kwargs)
2310 table: Table
2312 constraints: Set[Constraint]
2314 foreign_keys: Set[ForeignKey]
2315 """A collection of all :class:`_schema.ForeignKey` marker objects
2316 associated with this :class:`_schema.Column`.
2318 Each object is a member of a :class:`_schema.Table`-wide
2319 :class:`_schema.ForeignKeyConstraint`.
2321 .. seealso::
2323 :attr:`_schema.Table.foreign_keys`
2325 """
2327 index: Optional[bool]
2328 """The value of the :paramref:`_schema.Column.index` parameter.
2330 Does not indicate if this :class:`_schema.Column` is actually indexed
2331 or not; use :attr:`_schema.Table.indexes`.
2333 .. seealso::
2335 :attr:`_schema.Table.indexes`
2336 """
2338 unique: Optional[bool]
2339 """The value of the :paramref:`_schema.Column.unique` parameter.
2341 Does not indicate if this :class:`_schema.Column` is actually subject to
2342 a unique constraint or not; use :attr:`_schema.Table.indexes` and
2343 :attr:`_schema.Table.constraints`.
2345 .. seealso::
2347 :attr:`_schema.Table.indexes`
2349 :attr:`_schema.Table.constraints`.
2351 """
2353 computed: Optional[Computed]
2355 identity: Optional[Identity]
2357 def _set_type(self, type_: TypeEngine[Any]) -> None:
2358 assert self.type._isnull or type_ is self.type
2360 self.type = type_
2361 if isinstance(self.type, SchemaEventTarget):
2362 self.type._set_parent_with_dispatch(self)
2363 for impl in self.type._variant_mapping.values():
2364 if isinstance(impl, SchemaEventTarget):
2365 impl._set_parent_with_dispatch(self)
2367 @HasMemoized.memoized_attribute
2368 def _default_description_tuple(self) -> _DefaultDescriptionTuple:
2369 """used by default.py -> _process_execute_defaults()"""
2371 return _DefaultDescriptionTuple._from_column_default(self.default)
2373 @HasMemoized.memoized_attribute
2374 def _onupdate_description_tuple(self) -> _DefaultDescriptionTuple:
2375 """used by default.py -> _process_execute_defaults()"""
2376 return _DefaultDescriptionTuple._from_column_default(self.onupdate)
2378 @util.memoized_property
2379 def _gen_static_annotations_cache_key(self) -> bool:
2380 """special attribute used by cache key gen, if true, we will
2381 use a static cache key for the annotations dictionary, else we
2382 will generate a new cache key for annotations each time.
2384 Added for #8790
2386 """
2387 return self.table is not None and self.table._is_table
2389 def _extra_kwargs(self, **kwargs: Any) -> None:
2390 self._validate_dialect_kwargs(kwargs)
2392 def __str__(self) -> str:
2393 if self.name is None:
2394 return "(no name)"
2395 elif self.table is not None:
2396 if self.table.named_with_column:
2397 return self.table.description + "." + self.description
2398 else:
2399 return self.description
2400 else:
2401 return self.description
2403 def references(self, column: Column[Any]) -> bool:
2404 """Return True if this Column references the given column via foreign
2405 key."""
2407 for fk in self.foreign_keys:
2408 if fk.column.proxy_set.intersection(column.proxy_set):
2409 return True
2410 else:
2411 return False
2413 def append_foreign_key(self, fk: ForeignKey) -> None:
2414 fk._set_parent_with_dispatch(self)
2416 def __repr__(self) -> str:
2417 kwarg = []
2418 if self.key != self.name:
2419 kwarg.append("key")
2420 if self.primary_key:
2421 kwarg.append("primary_key")
2422 if not self.nullable:
2423 kwarg.append("nullable")
2424 if self.onupdate:
2425 kwarg.append("onupdate")
2426 if self.default:
2427 kwarg.append("default")
2428 if self.server_default:
2429 kwarg.append("server_default")
2430 if self.comment:
2431 kwarg.append("comment")
2432 return "Column(%s)" % ", ".join(
2433 [repr(self.name)]
2434 + [repr(self.type)]
2435 + [repr(x) for x in self.foreign_keys if x is not None]
2436 + [repr(x) for x in self.constraints]
2437 + [
2438 (
2439 self.table is not None
2440 and "table=<%s>" % self.table.description
2441 or "table=None"
2442 )
2443 ]
2444 + ["%s=%s" % (k, repr(getattr(self, k))) for k in kwarg]
2445 )
2447 def _set_parent( # type: ignore[override]
2448 self,
2449 parent: SchemaEventTarget,
2450 *,
2451 all_names: Dict[str, Column[Any]],
2452 allow_replacements: bool,
2453 index: Optional[int] = None,
2454 **kw: Any,
2455 ) -> None:
2456 table = parent
2457 assert isinstance(table, Table)
2458 if not self.name:
2459 raise exc.ArgumentError(
2460 "Column must be constructed with a non-blank name or "
2461 "assign a non-blank .name before adding to a Table."
2462 )
2464 self._reset_memoizations()
2466 if self.key is None:
2467 self.key = self.name
2469 existing = getattr(self, "table", None)
2470 if existing is not None and existing is not table:
2471 raise exc.ArgumentError(
2472 f"Column object '{self.key}' already "
2473 f"assigned to Table '{existing.description}'"
2474 )
2476 extra_remove = None
2477 existing_col = None
2478 conflicts_on = ""
2480 if self.key in table._columns:
2481 existing_col = table._columns[self.key]
2482 if self.key == self.name:
2483 conflicts_on = "name"
2484 else:
2485 conflicts_on = "key"
2486 elif self.name in all_names:
2487 existing_col = all_names[self.name]
2488 extra_remove = {existing_col}
2489 conflicts_on = "name"
2491 if existing_col is not None:
2492 if existing_col is not self:
2493 if not allow_replacements:
2494 raise exc.DuplicateColumnError(
2495 f"A column with {conflicts_on} "
2496 f"""'{
2497 self.key if conflicts_on == 'key' else self.name
2498 }' """
2499 f"is already present in table '{table.name}'."
2500 )
2501 for fk in existing_col.foreign_keys:
2502 table.foreign_keys.remove(fk)
2503 if fk.constraint in table.constraints:
2504 # this might have been removed
2505 # already, if it's a composite constraint
2506 # and more than one col being replaced
2507 table.constraints.remove(fk.constraint)
2509 if extra_remove and existing_col is not None and self.key == self.name:
2510 util.warn(
2511 f'Column with user-specified key "{existing_col.key}" is '
2512 "being replaced with "
2513 f'plain named column "{self.name}", '
2514 f'key "{existing_col.key}" is being removed. If this is a '
2515 "reflection operation, specify autoload_replace=False to "
2516 "prevent this replacement."
2517 )
2518 table._columns.replace(self, extra_remove=extra_remove, index=index)
2519 all_names[self.name] = self
2520 self.table = table
2522 if self._insert_sentinel:
2523 if self.table._sentinel_column is not None:
2524 raise exc.ArgumentError(
2525 "a Table may have only one explicit sentinel column"
2526 )
2527 self.table._sentinel_column = self
2529 if self.primary_key:
2530 table.primary_key._replace(self)
2531 elif self.key in table.primary_key:
2532 raise exc.ArgumentError(
2533 f"Trying to redefine primary-key column '{self.key}' as a "
2534 f"non-primary-key column on table '{table.fullname}'"
2535 )
2537 if self.index:
2538 if isinstance(self.index, str):
2539 raise exc.ArgumentError(
2540 "The 'index' keyword argument on Column is boolean only. "
2541 "To create indexes with a specific name, create an "
2542 "explicit Index object external to the Table."
2543 )
2544 table.append_constraint(
2545 Index(
2546 None, self.key, unique=bool(self.unique), _column_flag=True
2547 )
2548 )
2550 elif self.unique:
2551 if isinstance(self.unique, str):
2552 raise exc.ArgumentError(
2553 "The 'unique' keyword argument on Column is boolean "
2554 "only. To create unique constraints or indexes with a "
2555 "specific name, append an explicit UniqueConstraint to "
2556 "the Table's list of elements, or create an explicit "
2557 "Index object external to the Table."
2558 )
2559 table.append_constraint(
2560 UniqueConstraint(self.key, _column_flag=True)
2561 )
2563 self._setup_on_memoized_fks(lambda fk: fk._set_remote_table(table))
2565 if self.identity and (
2566 isinstance(self.default, Sequence)
2567 or isinstance(self.onupdate, Sequence)
2568 ):
2569 raise exc.ArgumentError(
2570 "An column cannot specify both Identity and Sequence."
2571 )
2573 def _setup_on_memoized_fks(self, fn: Callable[..., Any]) -> None:
2574 fk_keys = [
2575 ((self.table.key, self.key), False),
2576 ((self.table.key, self.name), True),
2577 ]
2578 for fk_key, link_to_name in fk_keys:
2579 if fk_key in self.table.metadata._fk_memos:
2580 for fk in self.table.metadata._fk_memos[fk_key]:
2581 if fk.link_to_name is link_to_name:
2582 fn(fk)
2584 def _on_table_attach(self, fn: Callable[..., Any]) -> None:
2585 if self.table is not None:
2586 fn(self, self.table)
2587 else:
2588 event.listen(self, "after_parent_attach", fn)
2590 @util.deprecated(
2591 "1.4",
2592 "The :meth:`_schema.Column.copy` method is deprecated "
2593 "and will be removed in a future release.",
2594 )
2595 def copy(self, **kw: Any) -> Column[Any]:
2596 return self._copy(**kw)
2598 def _copy(self, **kw: Any) -> Column[Any]:
2599 """Create a copy of this ``Column``, uninitialized.
2601 This is used in :meth:`_schema.Table.to_metadata` and by the ORM.
2603 """
2605 # Constraint objects plus non-constraint-bound ForeignKey objects
2606 args: List[SchemaItem] = [
2607 c._copy(**kw) for c in self.constraints if not c._type_bound
2608 ] + [c._copy(**kw) for c in self.foreign_keys if not c.constraint]
2610 # ticket #5276
2611 column_kwargs = {}
2612 for dialect_name in self.dialect_options:
2613 dialect_options = self.dialect_options[dialect_name]._non_defaults
2614 for (
2615 dialect_option_key,
2616 dialect_option_value,
2617 ) in dialect_options.items():
2618 column_kwargs[dialect_name + "_" + dialect_option_key] = (
2619 dialect_option_value
2620 )
2622 server_default = self.server_default
2623 server_onupdate = self.server_onupdate
2624 if isinstance(server_default, (Computed, Identity)):
2625 # TODO: likely should be copied in all cases
2626 # TODO: if a Sequence, we would need to transfer the Sequence
2627 # .metadata as well
2628 args.append(server_default._copy(**kw))
2629 server_default = server_onupdate = None
2631 type_ = self.type
2632 if isinstance(type_, SchemaEventTarget):
2633 type_ = type_.copy(**kw)
2635 # TODO: DefaultGenerator is not copied here! it's just used again
2636 # with _set_parent() pointing to the old column. see the new
2637 # use of _copy() in the new _merge() method
2639 c = self._constructor(
2640 name=self.name,
2641 type_=type_,
2642 key=self.key,
2643 primary_key=self.primary_key,
2644 unique=self.unique,
2645 system=self.system,
2646 # quote=self.quote, # disabled 2013-08-27 (commit 031ef080)
2647 index=self.index,
2648 autoincrement=self.autoincrement,
2649 default=self.default,
2650 server_default=server_default,
2651 onupdate=self.onupdate,
2652 server_onupdate=server_onupdate,
2653 doc=self.doc,
2654 comment=self.comment,
2655 _omit_from_statements=self._omit_from_statements,
2656 insert_sentinel=self._insert_sentinel,
2657 *args,
2658 **column_kwargs,
2659 )
2661 # copy the state of "nullable" exactly, to accommodate for
2662 # ORM flipping the .nullable flag directly
2663 c.nullable = self.nullable
2664 c._user_defined_nullable = self._user_defined_nullable
2666 return self._schema_item_copy(c)
2668 def _merge(
2669 self, other: Column[Any], *, omit_defaults: bool = False
2670 ) -> None:
2671 """merge the elements of this column onto "other"
2673 this is used by ORM pep-593 merge and will likely need a lot
2674 of fixes.
2677 """
2679 if self.primary_key:
2680 other.primary_key = True
2682 if self.autoincrement != "auto" and other.autoincrement == "auto":
2683 other.autoincrement = self.autoincrement
2685 if self.system:
2686 other.system = self.system
2688 if self.info:
2689 other.info.update(self.info)
2691 type_ = self.type
2692 if not type_._isnull and other.type._isnull:
2693 if isinstance(type_, SchemaEventTarget):
2694 type_ = type_.copy()
2696 other.type = type_
2698 if isinstance(type_, SchemaEventTarget):
2699 type_._set_parent_with_dispatch(other)
2701 for impl in type_._variant_mapping.values():
2702 if isinstance(impl, SchemaEventTarget):
2703 impl._set_parent_with_dispatch(other)
2705 if (
2706 self._user_defined_nullable is not NULL_UNSPECIFIED
2707 and other._user_defined_nullable is NULL_UNSPECIFIED
2708 ):
2709 other.nullable = self.nullable
2710 other._user_defined_nullable = self._user_defined_nullable
2712 if (
2713 not omit_defaults
2714 and self.default is not None
2715 and other.default is None
2716 ):
2717 new_default = self.default._copy()
2718 new_default._set_parent(other)
2720 if self.server_default and other.server_default is None:
2721 new_server_default = self.server_default
2722 if isinstance(new_server_default, FetchedValue):
2723 new_server_default = new_server_default._copy()
2724 new_server_default._set_parent(other)
2725 else:
2726 other.server_default = new_server_default
2728 if self.server_onupdate and other.server_onupdate is None:
2729 new_server_onupdate = self.server_onupdate
2730 new_server_onupdate = new_server_onupdate._copy()
2731 new_server_onupdate._set_parent(other)
2733 if self.onupdate and other.onupdate is None:
2734 new_onupdate = self.onupdate._copy()
2735 new_onupdate._set_parent(other)
2737 if self.index in (True, False) and other.index is None:
2738 other.index = self.index
2740 if self.unique in (True, False) and other.unique is None:
2741 other.unique = self.unique
2743 if self.doc and other.doc is None:
2744 other.doc = self.doc
2746 if self.comment and other.comment is None:
2747 other.comment = self.comment
2749 for const in self.constraints:
2750 if not const._type_bound:
2751 new_const = const._copy()
2752 new_const._set_parent(other)
2754 for fk in self.foreign_keys:
2755 if not fk.constraint:
2756 new_fk = fk._copy()
2757 new_fk._set_parent(other)
2759 def _make_proxy(
2760 self,
2761 selectable: FromClause,
2762 primary_key: ColumnSet,
2763 foreign_keys: Set[KeyedColumnElement[Any]],
2764 name: Optional[str] = None,
2765 key: Optional[str] = None,
2766 name_is_truncatable: bool = False,
2767 compound_select_cols: Optional[
2768 _typing_Sequence[ColumnElement[Any]]
2769 ] = None,
2770 **kw: Any,
2771 ) -> Tuple[str, ColumnClause[_T]]:
2772 """Create a *proxy* for this column.
2774 This is a copy of this ``Column`` referenced by a different parent
2775 (such as an alias or select statement). The column should
2776 be used only in select scenarios, as its full DDL/default
2777 information is not transferred.
2779 """
2781 fk = [
2782 ForeignKey(
2783 col if col is not None else f._colspec,
2784 _unresolvable=col is None,
2785 _constraint=f.constraint,
2786 )
2787 for f, col in [
2788 (fk, fk._resolve_column(raiseerr=False))
2789 for fk in self.foreign_keys
2790 ]
2791 ]
2793 if name is None and self.name is None:
2794 raise exc.InvalidRequestError(
2795 "Cannot initialize a sub-selectable"
2796 " with this Column object until its 'name' has "
2797 "been assigned."
2798 )
2799 try:
2800 c = self._constructor(
2801 (
2802 coercions.expect(
2803 roles.TruncatedLabelRole, name if name else self.name
2804 )
2805 if name_is_truncatable
2806 else (name or self.name)
2807 ),
2808 self.type,
2809 # this may actually be ._proxy_key when the key is incoming
2810 key=key if key else name if name else self.key,
2811 primary_key=self.primary_key,
2812 nullable=self.nullable,
2813 _proxies=(
2814 list(compound_select_cols)
2815 if compound_select_cols
2816 else [self]
2817 ),
2818 *fk,
2819 )
2820 except TypeError as err:
2821 raise TypeError(
2822 "Could not create a copy of this %r object. "
2823 "Ensure the class includes a _constructor() "
2824 "attribute or method which accepts the "
2825 "standard Column constructor arguments, or "
2826 "references the Column class itself." % self.__class__
2827 ) from err
2829 c.table = selectable
2830 c._propagate_attrs = selectable._propagate_attrs
2831 if selectable._is_clone_of is not None:
2832 c._is_clone_of = selectable._is_clone_of.columns.get(c.key)
2834 if self.primary_key:
2835 primary_key.add(c)
2837 if fk:
2838 foreign_keys.update(fk) # type: ignore
2840 return c.key, c
2843def insert_sentinel(
2844 name: Optional[str] = None,
2845 type_: Optional[_TypeEngineArgument[_T]] = None,
2846 *,
2847 default: Optional[Any] = None,
2848 omit_from_statements: bool = True,
2849) -> Column[Any]:
2850 """Provides a surrogate :class:`_schema.Column` that will act as a
2851 dedicated insert :term:`sentinel` column, allowing efficient bulk
2852 inserts with deterministic RETURNING sorting for tables that
2853 don't otherwise have qualifying primary key configurations.
2855 Adding this column to a :class:`.Table` object requires that a
2856 corresponding database table actually has this column present, so if adding
2857 it to an existing model, existing database tables would need to be migrated
2858 (e.g. using ALTER TABLE or similar) to include this column.
2860 For background on how this object is used, see the section
2861 :ref:`engine_insertmanyvalues_sentinel_columns` as part of the
2862 section :ref:`engine_insertmanyvalues`.
2864 The :class:`_schema.Column` returned will be a nullable integer column by
2865 default and make use of a sentinel-specific default generator used only in
2866 "insertmanyvalues" operations.
2868 .. seealso::
2870 :func:`_orm.orm_insert_sentinel`
2872 :paramref:`_schema.Column.insert_sentinel`
2874 :ref:`engine_insertmanyvalues`
2876 :ref:`engine_insertmanyvalues_sentinel_columns`
2879 .. versionadded:: 2.0.10
2881 """
2882 return Column(
2883 name=name,
2884 type_=type_api.INTEGERTYPE if type_ is None else type_,
2885 default=(
2886 default if default is not None else _InsertSentinelColumnDefault()
2887 ),
2888 _omit_from_statements=omit_from_statements,
2889 insert_sentinel=True,
2890 )
2893class ForeignKey(DialectKWArgs, SchemaItem):
2894 """Defines a dependency between two columns.
2896 ``ForeignKey`` is specified as an argument to a :class:`_schema.Column`
2897 object,
2898 e.g.::
2900 t = Table(
2901 "remote_table",
2902 metadata,
2903 Column("remote_id", ForeignKey("main_table.id")),
2904 )
2906 Note that ``ForeignKey`` is only a marker object that defines
2907 a dependency between two columns. The actual constraint
2908 is in all cases represented by the :class:`_schema.ForeignKeyConstraint`
2909 object. This object will be generated automatically when
2910 a ``ForeignKey`` is associated with a :class:`_schema.Column` which
2911 in turn is associated with a :class:`_schema.Table`. Conversely,
2912 when :class:`_schema.ForeignKeyConstraint` is applied to a
2913 :class:`_schema.Table`,
2914 ``ForeignKey`` markers are automatically generated to be
2915 present on each associated :class:`_schema.Column`, which are also
2916 associated with the constraint object.
2918 Note that you cannot define a "composite" foreign key constraint,
2919 that is a constraint between a grouping of multiple parent/child
2920 columns, using ``ForeignKey`` objects. To define this grouping,
2921 the :class:`_schema.ForeignKeyConstraint` object must be used, and applied
2922 to the :class:`_schema.Table`. The associated ``ForeignKey`` objects
2923 are created automatically.
2925 The ``ForeignKey`` objects associated with an individual
2926 :class:`_schema.Column`
2927 object are available in the `foreign_keys` collection
2928 of that column.
2930 Further examples of foreign key configuration are in
2931 :ref:`metadata_foreignkeys`.
2933 """
2935 __visit_name__ = "foreign_key"
2937 parent: Column[Any]
2939 _table_column: Optional[Column[Any]]
2941 _colspec: Union[str, Column[Any]]
2943 def __init__(
2944 self,
2945 column: _DDLColumnReferenceArgument,
2946 _constraint: Optional[ForeignKeyConstraint] = None,
2947 use_alter: bool = False,
2948 name: _ConstraintNameArgument = None,
2949 onupdate: Optional[str] = None,
2950 ondelete: Optional[str] = None,
2951 deferrable: Optional[bool] = None,
2952 initially: Optional[str] = None,
2953 link_to_name: bool = False,
2954 match: Optional[str] = None,
2955 info: Optional[_InfoType] = None,
2956 comment: Optional[str] = None,
2957 _unresolvable: bool = False,
2958 **dialect_kw: Any,
2959 ):
2960 r"""
2961 Construct a column-level FOREIGN KEY.
2963 The :class:`_schema.ForeignKey` object when constructed generates a
2964 :class:`_schema.ForeignKeyConstraint`
2965 which is associated with the parent
2966 :class:`_schema.Table` object's collection of constraints.
2968 :param column: A single target column for the key relationship. A
2969 :class:`_schema.Column` object or a column name as a string:
2970 ``tablename.columnkey`` or ``schema.tablename.columnkey``.
2971 ``columnkey`` is the ``key`` which has been assigned to the column
2972 (defaults to the column name itself), unless ``link_to_name`` is
2973 ``True`` in which case the rendered name of the column is used.
2975 :param name: Optional string. An in-database name for the key if
2976 `constraint` is not provided.
2978 :param onupdate: Optional string. If set, emit ON UPDATE <value> when
2979 issuing DDL for this constraint. Typical values include CASCADE,
2980 DELETE and RESTRICT.
2982 .. seealso::
2984 :ref:`on_update_on_delete`
2986 :param ondelete: Optional string. If set, emit ON DELETE <value> when
2987 issuing DDL for this constraint. Typical values include CASCADE,
2988 SET NULL and RESTRICT. Some dialects may allow for additional
2989 syntaxes.
2991 .. seealso::
2993 :ref:`on_update_on_delete`
2995 :param deferrable: Optional bool. If set, emit DEFERRABLE or NOT
2996 DEFERRABLE when issuing DDL for this constraint.
2998 :param initially: Optional string. If set, emit INITIALLY <value> when
2999 issuing DDL for this constraint.
3001 :param link_to_name: if True, the string name given in ``column`` is
3002 the rendered name of the referenced column, not its locally
3003 assigned ``key``.
3005 :param use_alter: passed to the underlying
3006 :class:`_schema.ForeignKeyConstraint`
3007 to indicate the constraint should
3008 be generated/dropped externally from the CREATE TABLE/ DROP TABLE
3009 statement. See :paramref:`_schema.ForeignKeyConstraint.use_alter`
3010 for further description.
3012 .. seealso::
3014 :paramref:`_schema.ForeignKeyConstraint.use_alter`
3016 :ref:`use_alter`
3018 :param match: Optional string. If set, emit MATCH <value> when issuing
3019 DDL for this constraint. Typical values include SIMPLE, PARTIAL
3020 and FULL.
3022 :param info: Optional data dictionary which will be populated into the
3023 :attr:`.SchemaItem.info` attribute of this object.
3025 :param comment: Optional string that will render an SQL comment on
3026 foreign key constraint creation.
3028 .. versionadded:: 2.0
3030 :param \**dialect_kw: Additional keyword arguments are dialect
3031 specific, and passed in the form ``<dialectname>_<argname>``. The
3032 arguments are ultimately handled by a corresponding
3033 :class:`_schema.ForeignKeyConstraint`.
3034 See the documentation regarding
3035 an individual dialect at :ref:`dialect_toplevel` for detail on
3036 documented arguments.
3038 """
3040 self._unresolvable = _unresolvable
3042 self._colspec, self._table_column = self._parse_colspec_argument(
3043 column
3044 )
3046 # the linked ForeignKeyConstraint.
3047 # ForeignKey will create this when parent Column
3048 # is attached to a Table, *or* ForeignKeyConstraint
3049 # object passes itself in when creating ForeignKey
3050 # markers.
3051 self.constraint = _constraint
3053 # .parent is not Optional under normal use
3054 self.parent = None # type: ignore
3056 self.use_alter = use_alter
3057 self.name = name
3058 self.onupdate = onupdate
3059 self.ondelete = ondelete
3060 self.deferrable = deferrable
3061 self.initially = initially
3062 self.link_to_name = link_to_name
3063 self.match = match
3064 self.comment = comment
3065 if info:
3066 self.info = info
3067 self._unvalidated_dialect_kw = dialect_kw
3069 def _resolve_colspec_argument(
3070 self,
3071 ) -> Tuple[
3072 Union[str, Column[Any]],
3073 Optional[Column[Any]],
3074 ]:
3075 argument = self._colspec
3077 return self._parse_colspec_argument(argument)
3079 def _parse_colspec_argument(
3080 self,
3081 argument: _DDLColumnArgument,
3082 ) -> Tuple[
3083 Union[str, Column[Any]],
3084 Optional[Column[Any]],
3085 ]:
3086 _colspec = coercions.expect(roles.DDLReferredColumnRole, argument)
3088 if isinstance(_colspec, str):
3089 _table_column = None
3090 else:
3091 assert isinstance(_colspec, ColumnClause)
3092 _table_column = _colspec
3094 if not isinstance(_table_column.table, (type(None), TableClause)):
3095 raise exc.ArgumentError(
3096 "ForeignKey received Column not bound "
3097 "to a Table, got: %r" % _table_column.table
3098 )
3100 return _colspec, _table_column
3102 def __repr__(self) -> str:
3103 return "ForeignKey(%r)" % self._get_colspec()
3105 @util.deprecated(
3106 "1.4",
3107 "The :meth:`_schema.ForeignKey.copy` method is deprecated "
3108 "and will be removed in a future release.",
3109 )
3110 def copy(self, *, schema: Optional[str] = None, **kw: Any) -> ForeignKey:
3111 return self._copy(schema=schema, **kw)
3113 def _copy(self, *, schema: Optional[str] = None, **kw: Any) -> ForeignKey:
3114 """Produce a copy of this :class:`_schema.ForeignKey` object.
3116 The new :class:`_schema.ForeignKey` will not be bound
3117 to any :class:`_schema.Column`.
3119 This method is usually used by the internal
3120 copy procedures of :class:`_schema.Column`, :class:`_schema.Table`,
3121 and :class:`_schema.MetaData`.
3123 :param schema: The returned :class:`_schema.ForeignKey` will
3124 reference the original table and column name, qualified
3125 by the given string schema name.
3127 """
3128 fk = ForeignKey(
3129 self._get_colspec(schema=schema),
3130 use_alter=self.use_alter,
3131 name=self.name,
3132 onupdate=self.onupdate,
3133 ondelete=self.ondelete,
3134 deferrable=self.deferrable,
3135 initially=self.initially,
3136 link_to_name=self.link_to_name,
3137 match=self.match,
3138 comment=self.comment,
3139 **self._unvalidated_dialect_kw,
3140 )
3141 return self._schema_item_copy(fk)
3143 def _get_colspec(
3144 self,
3145 schema: Optional[
3146 Union[
3147 str,
3148 Literal[SchemaConst.RETAIN_SCHEMA, SchemaConst.BLANK_SCHEMA],
3149 ]
3150 ] = None,
3151 table_name: Optional[str] = None,
3152 _is_copy: bool = False,
3153 ) -> str:
3154 """Return a string based 'column specification' for this
3155 :class:`_schema.ForeignKey`.
3157 This is usually the equivalent of the string-based "tablename.colname"
3158 argument first passed to the object's constructor.
3160 """
3162 _colspec, effective_table_column = self._resolve_colspec_argument()
3164 if schema not in (None, RETAIN_SCHEMA):
3165 _schema, tname, colname = self._column_tokens
3166 if table_name is not None:
3167 tname = table_name
3168 if schema is BLANK_SCHEMA:
3169 return "%s.%s" % (tname, colname)
3170 else:
3171 return "%s.%s.%s" % (schema, tname, colname)
3172 elif table_name:
3173 schema, tname, colname = self._column_tokens
3174 if schema:
3175 return "%s.%s.%s" % (schema, table_name, colname)
3176 else:
3177 return "%s.%s" % (table_name, colname)
3178 elif effective_table_column is not None:
3179 if effective_table_column.table is None:
3180 if _is_copy:
3181 raise exc.InvalidRequestError(
3182 f"Can't copy ForeignKey object which refers to "
3183 f"non-table bound Column {effective_table_column!r}"
3184 )
3185 else:
3186 return effective_table_column.key
3187 return "%s.%s" % (
3188 effective_table_column.table.fullname,
3189 effective_table_column.key,
3190 )
3191 else:
3192 assert isinstance(_colspec, str)
3193 return _colspec
3195 @property
3196 def _referred_schema(self) -> Optional[str]:
3197 return self._column_tokens[0]
3199 def _table_key_within_construction(self) -> Any:
3200 """get the table key but only safely"""
3202 if self._table_column is not None:
3203 if self._table_column.table is None:
3204 return None
3205 else:
3206 return self._table_column.table.key
3207 else:
3208 schema, tname, colname = self._column_tokens
3209 return _get_table_key(tname, schema)
3211 target_fullname = property(_get_colspec)
3213 def references(self, table: Table) -> bool:
3214 """Return True if the given :class:`_schema.Table`
3215 is referenced by this
3216 :class:`_schema.ForeignKey`."""
3218 return table.corresponding_column(self.column) is not None
3220 def get_referent(self, table: FromClause) -> Optional[Column[Any]]:
3221 """Return the :class:`_schema.Column` in the given
3222 :class:`_schema.Table` (or any :class:`.FromClause`)
3223 referenced by this :class:`_schema.ForeignKey`.
3225 Returns None if this :class:`_schema.ForeignKey`
3226 does not reference the given
3227 :class:`_schema.Table`.
3229 """
3230 # our column is a Column, and any subquery etc. proxying us
3231 # would be doing so via another Column, so that's what would
3232 # be returned here
3233 return table.columns.corresponding_column(self.column) # type: ignore
3235 @util.memoized_property
3236 def _column_tokens(self) -> Tuple[Optional[str], str, Optional[str]]:
3237 """parse a string-based _colspec into its component parts."""
3239 m = self._get_colspec().split(".")
3240 if len(m) == 1:
3241 tname = m.pop()
3242 colname = None
3243 else:
3244 colname = m.pop()
3245 tname = m.pop()
3247 # A FK between column 'bar' and table 'foo' can be
3248 # specified as 'foo', 'foo.bar', 'dbo.foo.bar',
3249 # 'otherdb.dbo.foo.bar'. Once we have the column name and
3250 # the table name, treat everything else as the schema
3251 # name. Some databases (e.g. Sybase) support
3252 # inter-database foreign keys. See tickets#1341 and --
3253 # indirectly related -- Ticket #594. This assumes that '.'
3254 # will never appear *within* any component of the FK.
3256 if len(m) > 0:
3257 schema = ".".join(m)
3258 else:
3259 schema = None
3260 return schema, tname, colname
3262 def _resolve_col_tokens(self) -> Tuple[Table, str, Optional[str]]:
3263 if self.parent is None:
3264 raise exc.InvalidRequestError(
3265 "this ForeignKey object does not yet have a "
3266 "parent Column associated with it."
3267 )
3269 elif self.parent.table is None:
3270 raise exc.InvalidRequestError(
3271 "this ForeignKey's parent column is not yet associated "
3272 "with a Table."
3273 )
3275 parenttable = self.parent.table
3277 if self._unresolvable:
3278 schema, tname, colname = self._column_tokens
3279 tablekey = _get_table_key(tname, schema)
3280 return parenttable, tablekey, colname
3282 # assertion
3283 # basically Column._make_proxy() sends the actual
3284 # target Column to the ForeignKey object, so the
3285 # string resolution here is never called.
3286 for c in self.parent.base_columns:
3287 if isinstance(c, Column):
3288 assert c.table is parenttable
3289 break
3290 else:
3291 assert False
3292 ######################
3294 schema, tname, colname = self._column_tokens
3296 if schema is None and parenttable.metadata.schema is not None:
3297 schema = parenttable.metadata.schema
3299 tablekey = _get_table_key(tname, schema)
3300 return parenttable, tablekey, colname
3302 def _link_to_col_by_colstring(
3303 self, parenttable: Table, table: Table, colname: Optional[str]
3304 ) -> Column[Any]:
3305 _column = None
3306 if colname is None:
3307 # colname is None in the case that ForeignKey argument
3308 # was specified as table name only, in which case we
3309 # match the column name to the same column on the
3310 # parent.
3311 # this use case wasn't working in later 1.x series
3312 # as it had no test coverage; fixed in 2.0
3313 parent = self.parent
3314 assert parent is not None
3315 key = parent.key
3316 _column = table.c.get(key, None)
3317 elif self.link_to_name:
3318 key = colname
3319 for c in table.c:
3320 if c.name == colname:
3321 _column = c
3322 else:
3323 key = colname
3324 _column = table.c.get(colname, None)
3326 if _column is None:
3327 raise exc.NoReferencedColumnError(
3328 "Could not initialize target column "
3329 f"for ForeignKey '{self._get_colspec()}' "
3330 f"on table '{parenttable.name}': "
3331 f"table '{table.name}' has no column named '{key}'",
3332 table.name,
3333 key,
3334 )
3336 return _column
3338 def _set_target_column(self, column: Column[Any]) -> None:
3339 assert self.parent is not None
3341 # propagate TypeEngine to parent if it didn't have one
3342 if self.parent.type._isnull:
3343 self.parent.type = column.type
3345 # super-edgy case, if other FKs point to our column,
3346 # they'd get the type propagated out also.
3348 def set_type(fk: ForeignKey) -> None:
3349 if fk.parent.type._isnull:
3350 fk.parent.type = column.type
3352 self.parent._setup_on_memoized_fks(set_type)
3354 self.column = column # type: ignore
3356 @util.ro_memoized_property
3357 def column(self) -> Column[Any]:
3358 """Return the target :class:`_schema.Column` referenced by this
3359 :class:`_schema.ForeignKey`.
3361 If no target column has been established, an exception
3362 is raised.
3364 """
3365 return self._resolve_column()
3367 @overload
3368 def _resolve_column(
3369 self, *, raiseerr: Literal[True] = ...
3370 ) -> Column[Any]: ...
3372 @overload
3373 def _resolve_column(
3374 self, *, raiseerr: bool = ...
3375 ) -> Optional[Column[Any]]: ...
3377 def _resolve_column(
3378 self, *, raiseerr: bool = True
3379 ) -> Optional[Column[Any]]:
3380 _column: Column[Any]
3382 _colspec, effective_table_column = self._resolve_colspec_argument()
3384 if isinstance(_colspec, str):
3385 parenttable, tablekey, colname = self._resolve_col_tokens()
3387 if self._unresolvable or tablekey not in parenttable.metadata:
3388 if not raiseerr:
3389 return None
3390 raise exc.NoReferencedTableError(
3391 f"Foreign key associated with column "
3392 f"'{self.parent}' could not find "
3393 f"table '{tablekey}' with which to generate a "
3394 f"foreign key to target column '{colname}'",
3395 tablekey,
3396 )
3397 elif parenttable.key not in parenttable.metadata:
3398 if not raiseerr:
3399 return None
3400 raise exc.InvalidRequestError(
3401 f"Table {parenttable} is no longer associated with its "
3402 "parent MetaData"
3403 )
3404 else:
3405 table = parenttable.metadata.tables[tablekey]
3406 return self._link_to_col_by_colstring(
3407 parenttable, table, colname
3408 )
3410 elif hasattr(_colspec, "__clause_element__"):
3411 _column = _colspec.__clause_element__()
3412 return _column
3413 else:
3414 assert isinstance(_colspec, Column)
3415 _column = _colspec
3416 return _column
3418 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
3419 assert isinstance(parent, Column)
3421 if self.parent is not None and self.parent is not parent:
3422 raise exc.InvalidRequestError(
3423 "This ForeignKey already has a parent !"
3424 )
3425 self.parent = parent
3426 self.parent.foreign_keys.add(self)
3427 self.parent._on_table_attach(self._set_table)
3429 def _set_remote_table(self, table: Table) -> None:
3430 parenttable, _, colname = self._resolve_col_tokens()
3431 _column = self._link_to_col_by_colstring(parenttable, table, colname)
3432 self._set_target_column(_column)
3433 assert self.constraint is not None
3434 self.constraint._validate_dest_table(table)
3436 def _remove_from_metadata(self, metadata: MetaData) -> None:
3437 parenttable, table_key, colname = self._resolve_col_tokens()
3438 fk_key = (table_key, colname)
3440 if self in metadata._fk_memos[fk_key]:
3441 # TODO: no test coverage for self not in memos
3442 metadata._fk_memos[fk_key].remove(self)
3444 def _set_table(self, column: Column[Any], table: Table) -> None:
3445 # standalone ForeignKey - create ForeignKeyConstraint
3446 # on the hosting Table when attached to the Table.
3447 assert isinstance(table, Table)
3448 if self.constraint is None:
3449 self.constraint = ForeignKeyConstraint(
3450 [],
3451 [],
3452 use_alter=self.use_alter,
3453 name=self.name,
3454 onupdate=self.onupdate,
3455 ondelete=self.ondelete,
3456 deferrable=self.deferrable,
3457 initially=self.initially,
3458 match=self.match,
3459 comment=self.comment,
3460 **self._unvalidated_dialect_kw,
3461 )
3462 self.constraint._append_element(column, self)
3463 self.constraint._set_parent_with_dispatch(table)
3464 table.foreign_keys.add(self)
3465 # set up remote ".column" attribute, or a note to pick it
3466 # up when the other Table/Column shows up
3468 _colspec, _ = self._resolve_colspec_argument()
3469 if isinstance(_colspec, str):
3470 parenttable, table_key, colname = self._resolve_col_tokens()
3471 fk_key = (table_key, colname)
3472 if table_key in parenttable.metadata.tables:
3473 table = parenttable.metadata.tables[table_key]
3474 try:
3475 _column = self._link_to_col_by_colstring(
3476 parenttable, table, colname
3477 )
3478 except exc.NoReferencedColumnError:
3479 # this is OK, we'll try later
3480 pass
3481 else:
3482 self._set_target_column(_column)
3484 parenttable.metadata._fk_memos[fk_key].append(self)
3485 elif hasattr(_colspec, "__clause_element__"):
3486 _column = _colspec.__clause_element__()
3487 self._set_target_column(_column)
3488 else:
3489 self._set_target_column(_colspec)
3492if TYPE_CHECKING:
3494 def default_is_sequence(
3495 obj: Optional[DefaultGenerator],
3496 ) -> TypeGuard[Sequence]: ...
3498 def default_is_clause_element(
3499 obj: Optional[DefaultGenerator],
3500 ) -> TypeGuard[ColumnElementColumnDefault]: ...
3502 def default_is_scalar(
3503 obj: Optional[DefaultGenerator],
3504 ) -> TypeGuard[ScalarElementColumnDefault]: ...
3506else:
3507 default_is_sequence = operator.attrgetter("is_sequence")
3509 default_is_clause_element = operator.attrgetter("is_clause_element")
3511 default_is_scalar = operator.attrgetter("is_scalar")
3514class DefaultGenerator(Executable, SchemaItem):
3515 """Base class for column *default* values.
3517 This object is only present on column.default or column.onupdate.
3518 It's not valid as a server default.
3520 """
3522 __visit_name__ = "default_generator"
3524 _is_default_generator = True
3525 is_sequence = False
3526 is_identity = False
3527 is_server_default = False
3528 is_clause_element = False
3529 is_callable = False
3530 is_scalar = False
3531 has_arg = False
3532 is_sentinel = False
3533 _is_monotonic_fn = False
3534 column: Optional[Column[Any]]
3536 def __init__(self, for_update: bool = False) -> None:
3537 self.for_update = for_update
3539 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
3540 if TYPE_CHECKING:
3541 assert isinstance(parent, Column)
3542 self.column = parent
3543 if self.for_update:
3544 self.column.onupdate = self
3545 else:
3546 self.column.default = self
3548 def _copy(self) -> DefaultGenerator:
3549 raise NotImplementedError()
3551 def _execute_on_connection(
3552 self,
3553 connection: Connection,
3554 distilled_params: _CoreMultiExecuteParams,
3555 execution_options: CoreExecuteOptionsParameter,
3556 ) -> Any:
3557 util.warn_deprecated(
3558 "Using the .execute() method to invoke a "
3559 "DefaultGenerator object is deprecated; please use "
3560 "the .scalar() method.",
3561 "2.0",
3562 )
3563 return self._execute_on_scalar(
3564 connection, distilled_params, execution_options
3565 )
3567 def _execute_on_scalar(
3568 self,
3569 connection: Connection,
3570 distilled_params: _CoreMultiExecuteParams,
3571 execution_options: CoreExecuteOptionsParameter,
3572 ) -> Any:
3573 return connection._execute_default(
3574 self, distilled_params, execution_options
3575 )
3578class ColumnDefault(DefaultGenerator, ABC):
3579 """A plain default value on a column.
3581 This could correspond to a constant, a callable function,
3582 or a SQL clause.
3584 :class:`.ColumnDefault` is generated automatically
3585 whenever the ``default``, ``onupdate`` arguments of
3586 :class:`_schema.Column` are used. A :class:`.ColumnDefault`
3587 can be passed positionally as well.
3589 For example, the following::
3591 Column("foo", Integer, default=50)
3593 Is equivalent to::
3595 Column("foo", Integer, ColumnDefault(50))
3597 """
3599 arg: Any
3601 _is_monotonic_fn = False
3603 @overload
3604 def __new__(
3605 cls, arg: Callable[..., Any], for_update: bool = ...
3606 ) -> CallableColumnDefault: ...
3608 @overload
3609 def __new__(
3610 cls, arg: ColumnElement[Any], for_update: bool = ...
3611 ) -> ColumnElementColumnDefault: ...
3613 # if I return ScalarElementColumnDefault here, which is what's actually
3614 # returned, mypy complains that
3615 # overloads overlap w/ incompatible return types.
3616 @overload
3617 def __new__(cls, arg: object, for_update: bool = ...) -> ColumnDefault: ...
3619 def __new__(
3620 cls, arg: Any = None, for_update: bool = False
3621 ) -> ColumnDefault:
3622 """Construct a new :class:`.ColumnDefault`.
3625 :param arg: argument representing the default value.
3626 May be one of the following:
3628 * a plain non-callable Python value, such as a
3629 string, integer, boolean, or other simple type.
3630 The default value will be used as is each time.
3631 * a SQL expression, that is one which derives from
3632 :class:`_expression.ColumnElement`. The SQL expression will
3633 be rendered into the INSERT or UPDATE statement,
3634 or in the case of a primary key column when
3635 RETURNING is not used may be
3636 pre-executed before an INSERT within a SELECT.
3637 * A Python callable. The function will be invoked for each
3638 new row subject to an INSERT or UPDATE.
3639 The callable must accept exactly
3640 zero or one positional arguments. The one-argument form
3641 will receive an instance of the :class:`.ExecutionContext`,
3642 which provides contextual information as to the current
3643 :class:`_engine.Connection` in use as well as the current
3644 statement and parameters.
3646 """
3648 if isinstance(arg, FetchedValue):
3649 raise exc.ArgumentError(
3650 "ColumnDefault may not be a server-side default type."
3651 )
3652 elif callable(arg):
3653 cls = CallableColumnDefault
3654 elif isinstance(arg, ClauseElement):
3655 cls = ColumnElementColumnDefault
3656 elif arg is not None:
3657 cls = ScalarElementColumnDefault
3659 return object.__new__(cls)
3661 def __repr__(self) -> str:
3662 return f"{self.__class__.__name__}({self.arg!r})"
3665class ScalarElementColumnDefault(ColumnDefault):
3666 """default generator for a fixed scalar Python value
3668 .. versionadded:: 2.0
3670 """
3672 is_scalar = True
3673 has_arg = True
3675 def __init__(self, arg: Any, for_update: bool = False) -> None:
3676 self.for_update = for_update
3677 self.arg = arg
3679 def _copy(self) -> ScalarElementColumnDefault:
3680 return ScalarElementColumnDefault(
3681 arg=self.arg, for_update=self.for_update
3682 )
3685class _InsertSentinelColumnDefault(ColumnDefault):
3686 """Default generator that's specific to the use of a "sentinel" column
3687 when using the insertmanyvalues feature.
3689 This default is used as part of the :func:`_schema.insert_sentinel`
3690 construct.
3692 """
3694 is_sentinel = True
3695 for_update = False
3696 arg = None
3698 def __new__(cls) -> _InsertSentinelColumnDefault:
3699 return object.__new__(cls)
3701 def __init__(self) -> None:
3702 pass
3704 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
3705 col = cast("Column[Any]", parent)
3706 if not col._insert_sentinel:
3707 raise exc.ArgumentError(
3708 "The _InsertSentinelColumnDefault may only be applied to a "
3709 "Column marked as insert_sentinel=True"
3710 )
3711 elif not col.nullable:
3712 raise exc.ArgumentError(
3713 "The _InsertSentinelColumnDefault may only be applied to a "
3714 "Column that is nullable"
3715 )
3717 super()._set_parent(parent, **kw)
3719 def _copy(self) -> _InsertSentinelColumnDefault:
3720 return _InsertSentinelColumnDefault()
3723_SQLExprDefault = Union["ColumnElement[Any]", "TextClause"]
3726class ColumnElementColumnDefault(ColumnDefault):
3727 """default generator for a SQL expression
3729 .. versionadded:: 2.0
3731 """
3733 is_clause_element = True
3734 has_arg = True
3735 arg: _SQLExprDefault
3737 def __init__(
3738 self,
3739 arg: _SQLExprDefault,
3740 for_update: bool = False,
3741 ) -> None:
3742 self.for_update = for_update
3743 self.arg = arg
3745 def _copy(self) -> ColumnElementColumnDefault:
3746 return ColumnElementColumnDefault(
3747 arg=self.arg, for_update=self.for_update
3748 )
3750 @util.memoized_property
3751 @util.preload_module("sqlalchemy.sql.functions")
3752 def _is_monotonic_fn(self) -> bool:
3753 functions = util.preloaded.sql_functions
3754 return (
3755 isinstance(self.arg, functions.FunctionElement)
3756 and self.arg.monotonic
3757 )
3759 @util.memoized_property
3760 @util.preload_module("sqlalchemy.sql.sqltypes")
3761 def _arg_is_typed(self) -> bool:
3762 sqltypes = util.preloaded.sql_sqltypes
3764 return not isinstance(self.arg.type, sqltypes.NullType)
3767class _CallableColumnDefaultProtocol(Protocol):
3768 def __call__(self, context: ExecutionContext) -> Any: ...
3771class CallableColumnDefault(ColumnDefault):
3772 """default generator for a callable Python function
3774 .. versionadded:: 2.0
3776 """
3778 is_callable = True
3779 arg: _CallableColumnDefaultProtocol
3780 has_arg = True
3782 def __init__(
3783 self,
3784 arg: Union[_CallableColumnDefaultProtocol, Callable[[], Any]],
3785 for_update: bool = False,
3786 ) -> None:
3787 self.for_update = for_update
3788 self.arg = self._maybe_wrap_callable(arg)
3790 def _copy(self) -> CallableColumnDefault:
3791 return CallableColumnDefault(arg=self.arg, for_update=self.for_update)
3793 def _maybe_wrap_callable(
3794 self, fn: Union[_CallableColumnDefaultProtocol, Callable[[], Any]]
3795 ) -> _CallableColumnDefaultProtocol:
3796 """Wrap callables that don't accept a context.
3798 This is to allow easy compatibility with default callables
3799 that aren't specific to accepting of a context.
3801 """
3803 try:
3804 argspec = util.get_callable_argspec(fn, no_self=True)
3805 except TypeError:
3806 return util.wrap_callable(lambda ctx: fn(), fn) # type: ignore
3808 defaulted = argspec[3] is not None and len(argspec[3]) or 0
3809 positionals = len(argspec[0]) - defaulted
3811 if positionals == 0:
3812 return util.wrap_callable(lambda ctx: fn(), fn) # type: ignore
3814 elif positionals == 1:
3815 return fn # type: ignore
3816 else:
3817 raise exc.ArgumentError(
3818 "ColumnDefault Python function takes zero or one "
3819 "positional arguments"
3820 )
3823class IdentityOptions(DialectKWArgs):
3824 """Defines options for a named database sequence or an identity column.
3826 .. seealso::
3828 :class:`.Sequence`
3830 """
3832 def __init__(
3833 self,
3834 start: Optional[int] = None,
3835 increment: Optional[int] = None,
3836 minvalue: Optional[int] = None,
3837 maxvalue: Optional[int] = None,
3838 nominvalue: Optional[bool] = None,
3839 nomaxvalue: Optional[bool] = None,
3840 cycle: Optional[bool] = None,
3841 cache: Optional[int] = None,
3842 order: Optional[bool] = None,
3843 **dialect_kw: Any,
3844 ) -> None:
3845 """Construct a :class:`.IdentityOptions` object.
3847 See the :class:`.Sequence` documentation for a complete description
3848 of the parameters.
3850 :param start: the starting index of the sequence.
3851 :param increment: the increment value of the sequence.
3852 :param minvalue: the minimum value of the sequence.
3853 :param maxvalue: the maximum value of the sequence.
3854 :param nominvalue: no minimum value of the sequence.
3855 :param nomaxvalue: no maximum value of the sequence.
3856 :param cycle: allows the sequence to wrap around when the maxvalue
3857 or minvalue has been reached.
3858 :param cache: optional integer value; number of future values in the
3859 sequence which are calculated in advance.
3860 :param order: optional boolean value; if ``True``, renders the
3861 ORDER keyword.
3863 .. deprecated:: 2.1 Use ``oracle_order`` instead.
3865 """
3866 self.start = start
3867 self.increment = increment
3868 self.minvalue = minvalue
3869 self.maxvalue = maxvalue
3870 self.nominvalue = nominvalue
3871 self.nomaxvalue = nomaxvalue
3872 self.cycle = cycle
3873 self.cache = cache
3874 if order is not None:
3875 if "oracle_order" in dialect_kw:
3876 raise exc.ArgumentError(
3877 "Cannot specify both 'order' and 'oracle_order'. "
3878 "Plese use only 'oracle_order'."
3879 )
3880 dialect_kw["oracle_order"] = order
3881 self._validate_dialect_kwargs(dialect_kw)
3883 @property
3884 def _increment_is_negative(self) -> bool:
3885 return self.increment is not None and self.increment < 0
3887 @property
3888 def order(self) -> Optional[bool]:
3889 """Alias of the ``dialect_kwargs`` ``'oracle_order'``.
3891 .. deprecated:: 2.1 The 'order' attribute is deprecated.
3892 """
3893 value: Optional[bool] = self.dialect_kwargs.get("oracle_order")
3894 return value
3896 def _as_dict(self) -> Dict[str, Any]:
3897 return {
3898 k: v
3899 for k, v in {
3900 "start": self.start,
3901 "increment": self.increment,
3902 "minvalue": self.minvalue,
3903 "maxvalue": self.maxvalue,
3904 "nominvalue": self.nominvalue,
3905 "nomaxvalue": self.nomaxvalue,
3906 "cycle": self.cycle,
3907 "cache": self.cache,
3908 }.items()
3909 if v != None
3910 }
3913class Sequence(HasSchemaAttr, IdentityOptions, DefaultGenerator):
3914 """Represents a named database sequence.
3916 The :class:`.Sequence` object represents the name and configurational
3917 parameters of a database sequence. It also represents
3918 a construct that can be "executed" by a SQLAlchemy :class:`_engine.Engine`
3919 or :class:`_engine.Connection`,
3920 rendering the appropriate "next value" function
3921 for the target database and returning a result.
3923 The :class:`.Sequence` is typically associated with a primary key column::
3925 some_table = Table(
3926 "some_table",
3927 metadata,
3928 Column(
3929 "id",
3930 Integer,
3931 Sequence("some_table_seq", start=1),
3932 primary_key=True,
3933 ),
3934 )
3936 When CREATE TABLE is emitted for the above :class:`_schema.Table`, if the
3937 target platform supports sequences, a CREATE SEQUENCE statement will
3938 be emitted as well. For platforms that don't support sequences,
3939 the :class:`.Sequence` construct is ignored.
3941 .. seealso::
3943 :ref:`defaults_sequences`
3945 :class:`.CreateSequence`
3947 :class:`.DropSequence`
3949 """
3951 __visit_name__ = "sequence"
3953 is_sequence = True
3955 column: Optional[Column[Any]]
3956 data_type: Optional[TypeEngine[int]]
3958 metadata: Optional[MetaData]
3960 @util.deprecated_params(
3961 order=(
3962 "2.1",
3963 "This parameter is supported only by Oracle Database, "
3964 "use ``oracle_order`` instead.",
3965 )
3966 )
3967 def __init__(
3968 self,
3969 name: str,
3970 start: Optional[int] = None,
3971 increment: Optional[int] = None,
3972 minvalue: Optional[int] = None,
3973 maxvalue: Optional[int] = None,
3974 nominvalue: Optional[bool] = None,
3975 nomaxvalue: Optional[bool] = None,
3976 cycle: Optional[bool] = None,
3977 schema: Optional[Union[str, Literal[SchemaConst.BLANK_SCHEMA]]] = None,
3978 cache: Optional[int] = None,
3979 order: Optional[bool] = None,
3980 data_type: Optional[_TypeEngineArgument[int]] = None,
3981 optional: bool = False,
3982 quote: Optional[bool] = None,
3983 metadata: Optional[MetaData] = None,
3984 quote_schema: Optional[bool] = None,
3985 for_update: bool = False,
3986 **dialect_kw: Any,
3987 ) -> None:
3988 """Construct a :class:`.Sequence` object.
3990 :param name: the name of the sequence.
3992 :param start: the starting index of the sequence. This value is
3993 used when the CREATE SEQUENCE command is emitted to the database
3994 as the value of the "START WITH" clause. If ``None``, the
3995 clause is omitted, which on most platforms indicates a starting
3996 value of 1.
3998 .. versionchanged:: 2.0 The :paramref:`.Sequence.start` parameter
3999 is required in order to have DDL emit "START WITH". This is a
4000 reversal of a change made in version 1.4 which would implicitly
4001 render "START WITH 1" if the :paramref:`.Sequence.start` were
4002 not included. See :ref:`change_7211` for more detail.
4004 :param increment: the increment value of the sequence. This
4005 value is used when the CREATE SEQUENCE command is emitted to
4006 the database as the value of the "INCREMENT BY" clause. If ``None``,
4007 the clause is omitted, which on most platforms indicates an
4008 increment of 1.
4009 :param minvalue: the minimum value of the sequence. This
4010 value is used when the CREATE SEQUENCE command is emitted to
4011 the database as the value of the "MINVALUE" clause. If ``None``,
4012 the clause is omitted, which on most platforms indicates a
4013 minvalue of 1 and -2^63-1 for ascending and descending sequences,
4014 respectively.
4016 :param maxvalue: the maximum value of the sequence. This
4017 value is used when the CREATE SEQUENCE command is emitted to
4018 the database as the value of the "MAXVALUE" clause. If ``None``,
4019 the clause is omitted, which on most platforms indicates a
4020 maxvalue of 2^63-1 and -1 for ascending and descending sequences,
4021 respectively.
4023 :param nominvalue: no minimum value of the sequence. This
4024 value is used when the CREATE SEQUENCE command is emitted to
4025 the database as the value of the "NO MINVALUE" clause. If ``None``,
4026 the clause is omitted, which on most platforms indicates a
4027 minvalue of 1 and -2^63-1 for ascending and descending sequences,
4028 respectively.
4030 :param nomaxvalue: no maximum value of the sequence. This
4031 value is used when the CREATE SEQUENCE command is emitted to
4032 the database as the value of the "NO MAXVALUE" clause. If ``None``,
4033 the clause is omitted, which on most platforms indicates a
4034 maxvalue of 2^63-1 and -1 for ascending and descending sequences,
4035 respectively.
4037 :param cycle: allows the sequence to wrap around when the maxvalue
4038 or minvalue has been reached by an ascending or descending sequence
4039 respectively. This value is used when the CREATE SEQUENCE command
4040 is emitted to the database as the "CYCLE" clause. If the limit is
4041 reached, the next number generated will be the minvalue or maxvalue,
4042 respectively. If cycle=False (the default) any calls to nextval
4043 after the sequence has reached its maximum value will return an
4044 error.
4046 :param schema: optional schema name for the sequence, if located
4047 in a schema other than the default. The rules for selecting the
4048 schema name when a :class:`_schema.MetaData`
4049 is also present are the same
4050 as that of :paramref:`_schema.Table.schema`.
4052 :param cache: optional integer value; number of future values in the
4053 sequence which are calculated in advance. Renders the CACHE keyword
4054 understood by Oracle Database and PostgreSQL.
4056 :param order: optional boolean value; if ``True``, renders the
4057 ORDER keyword, understood by Oracle Database, indicating the sequence
4058 is definitively ordered. May be necessary to provide deterministic
4059 ordering using Oracle RAC.
4061 :param data_type: The type to be returned by the sequence, for
4062 dialects that allow us to choose between INTEGER, BIGINT, etc.
4063 (e.g., mssql).
4065 .. versionadded:: 1.4.0
4067 :param optional: boolean value, when ``True``, indicates that this
4068 :class:`.Sequence` object only needs to be explicitly generated
4069 on backends that don't provide another way to generate primary
4070 key identifiers. Currently, it essentially means, "don't create
4071 this sequence on the PostgreSQL backend, where the SERIAL keyword
4072 creates a sequence for us automatically".
4073 :param quote: boolean value, when ``True`` or ``False``, explicitly
4074 forces quoting of the :paramref:`_schema.Sequence.name` on or off.
4075 When left at its default of ``None``, normal quoting rules based
4076 on casing and reserved words take place.
4077 :param quote_schema: Set the quoting preferences for the ``schema``
4078 name.
4080 :param metadata: optional :class:`_schema.MetaData` object which this
4081 :class:`.Sequence` will be associated with. A :class:`.Sequence`
4082 that is associated with a :class:`_schema.MetaData`
4083 gains the following
4084 capabilities:
4086 * The :class:`.Sequence` will inherit the
4087 :paramref:`_schema.MetaData.schema`
4088 parameter specified to the target :class:`_schema.MetaData`, which
4089 affects the production of CREATE / DROP DDL, if any.
4091 * The :meth:`.Sequence.create` and :meth:`.Sequence.drop` methods
4092 automatically use the engine bound to the :class:`_schema.MetaData`
4093 object, if any.
4095 * The :meth:`_schema.MetaData.create_all` and
4096 :meth:`_schema.MetaData.drop_all`
4097 methods will emit CREATE / DROP for this :class:`.Sequence`,
4098 even if the :class:`.Sequence` is not associated with any
4099 :class:`_schema.Table` / :class:`_schema.Column`
4100 that's a member of this
4101 :class:`_schema.MetaData`.
4103 The above behaviors can only occur if the :class:`.Sequence` is
4104 explicitly associated with the :class:`_schema.MetaData`
4105 via this parameter.
4107 .. seealso::
4109 :ref:`sequence_metadata` - full discussion of the
4110 :paramref:`.Sequence.metadata` parameter.
4112 :param for_update: Indicates this :class:`.Sequence`, when associated
4113 with a :class:`_schema.Column`,
4114 should be invoked for UPDATE statements
4115 on that column's table, rather than for INSERT statements, when
4116 no value is otherwise present for that column in the statement.
4118 """
4119 DefaultGenerator.__init__(self, for_update=for_update)
4120 IdentityOptions.__init__(
4121 self,
4122 start=start,
4123 increment=increment,
4124 minvalue=minvalue,
4125 maxvalue=maxvalue,
4126 nominvalue=nominvalue,
4127 nomaxvalue=nomaxvalue,
4128 cycle=cycle,
4129 cache=cache,
4130 order=order,
4131 **dialect_kw,
4132 )
4133 self.column = None
4134 self.name = quoted_name(name, quote)
4135 self.optional = optional
4136 if schema is BLANK_SCHEMA:
4137 self.schema = schema = None
4138 elif metadata is not None and schema is None and metadata.schema:
4139 self.schema = schema = metadata.schema
4140 else:
4141 self.schema = quoted_name.construct(schema, quote_schema)
4142 self._key = _get_table_key(name, schema)
4143 if data_type is not None:
4144 self.data_type = to_instance(data_type)
4145 else:
4146 self.data_type = None
4148 if metadata:
4149 self._set_metadata(metadata)
4150 else:
4151 self.metadata = None
4153 @util.preload_module("sqlalchemy.sql.functions")
4154 def next_value(self) -> Function[int]:
4155 """Return a :class:`.next_value` function element
4156 which will render the appropriate increment function
4157 for this :class:`.Sequence` within any SQL expression.
4159 """
4160 return util.preloaded.sql_functions.func.next_value(self)
4162 def _copy(self) -> Sequence:
4163 return Sequence(
4164 name=self.name,
4165 schema=self.schema,
4166 data_type=self.data_type,
4167 optional=self.optional,
4168 metadata=self.metadata,
4169 for_update=self.for_update,
4170 **self._as_dict(),
4171 **self.dialect_kwargs,
4172 )
4174 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4175 assert isinstance(parent, Column)
4176 super()._set_parent(parent, **kw)
4177 parent._on_table_attach(self._set_table)
4179 def _set_table(self, column: Column[Any], table: Table) -> None:
4180 self._set_metadata(table.metadata)
4182 def _set_metadata(self, metadata: MetaData) -> None:
4183 self.metadata = metadata
4184 self.metadata._register_object(self)
4185 metadata._sequences[self._key] = self
4187 def create(
4188 self,
4189 bind: _CreateDropBind,
4190 checkfirst: Union[bool, CheckFirst] = CheckFirst.SEQUENCES,
4191 ) -> None:
4192 """Creates this sequence in the database."""
4194 bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
4196 def drop(
4197 self,
4198 bind: _CreateDropBind,
4199 checkfirst: Union[bool, CheckFirst] = CheckFirst.SEQUENCES,
4200 ) -> None:
4201 """Drops this sequence from the database."""
4203 bind._run_ddl_visitor(ddl.SchemaDropper, self, checkfirst=checkfirst)
4205 def _not_a_column_expr(self) -> NoReturn:
4206 raise exc.InvalidRequestError(
4207 f"This {self.__class__.__name__} cannot be used directly "
4208 "as a column expression. Use func.next_value(sequence) "
4209 "to produce a 'next value' function that's usable "
4210 "as a column element."
4211 )
4214@inspection._self_inspects
4215class FetchedValue(SchemaEventTarget):
4216 """A marker for a transparent database-side default.
4218 Use :class:`.FetchedValue` when the database is configured
4219 to provide some automatic default for a column.
4221 E.g.::
4223 Column("foo", Integer, FetchedValue())
4225 Would indicate that some trigger or default generator
4226 will create a new value for the ``foo`` column during an
4227 INSERT.
4229 .. seealso::
4231 :ref:`triggered_columns`
4233 """
4235 is_server_default = True
4236 reflected = False
4237 has_argument = False
4238 is_clause_element = False
4239 is_identity = False
4240 _is_monotonic_fn = False
4242 column: Optional[Column[Any]]
4244 def __init__(self, for_update: bool = False) -> None:
4245 self.for_update = for_update
4247 def _as_for_update(self, for_update: bool) -> FetchedValue:
4248 if for_update == self.for_update:
4249 return self
4250 else:
4251 return self._clone(for_update)
4253 def _copy(self) -> FetchedValue:
4254 return FetchedValue(self.for_update)
4256 def _clone(self, for_update: bool) -> Self:
4257 n = self.__class__.__new__(self.__class__)
4258 n.__dict__.update(self.__dict__)
4259 n.__dict__.pop("column", None)
4260 n.for_update = for_update
4261 return n
4263 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4264 column = parent
4265 assert isinstance(column, Column)
4266 self.column = column
4267 if self.for_update:
4268 self.column.server_onupdate = self
4269 else:
4270 self.column.server_default = self
4272 def __repr__(self) -> str:
4273 return util.generic_repr(self)
4276class DefaultClause(FetchedValue):
4277 """A DDL-specified DEFAULT column value.
4279 :class:`.DefaultClause` is a :class:`.FetchedValue`
4280 that also generates a "DEFAULT" clause when
4281 "CREATE TABLE" is emitted.
4283 :class:`.DefaultClause` is generated automatically
4284 whenever the ``server_default``, ``server_onupdate`` arguments of
4285 :class:`_schema.Column` are used. A :class:`.DefaultClause`
4286 can be passed positionally as well.
4288 For example, the following::
4290 Column("foo", Integer, server_default="50")
4292 Is equivalent to::
4294 Column("foo", Integer, DefaultClause("50"))
4296 """
4298 has_argument = True
4300 def __init__(
4301 self,
4302 arg: Union[str, ClauseElement, TextClause],
4303 for_update: bool = False,
4304 _reflected: bool = False,
4305 ) -> None:
4306 util.assert_arg_type(arg, (str, ClauseElement, TextClause), "arg")
4307 super().__init__(for_update)
4308 self.arg = arg
4309 self.reflected = _reflected
4311 @util.memoized_property
4312 @util.preload_module("sqlalchemy.sql.functions")
4313 def _is_monotonic_fn(self) -> bool:
4314 functions = util.preloaded.sql_functions
4315 return (
4316 isinstance(self.arg, functions.FunctionElement)
4317 and self.arg.monotonic
4318 )
4320 def _copy(self) -> DefaultClause:
4321 return DefaultClause(
4322 arg=self.arg, for_update=self.for_update, _reflected=self.reflected
4323 )
4325 def __repr__(self) -> str:
4326 return "DefaultClause(%r, for_update=%r)" % (self.arg, self.for_update)
4329class Constraint(DialectKWArgs, HasConditionalDDL, SchemaItem):
4330 """A table-level SQL constraint.
4332 :class:`_schema.Constraint` serves as the base class for the series of
4333 constraint objects that can be associated with :class:`_schema.Table`
4334 objects, including :class:`_schema.PrimaryKeyConstraint`,
4335 :class:`_schema.ForeignKeyConstraint`
4336 :class:`_schema.UniqueConstraint`, and
4337 :class:`_schema.CheckConstraint`.
4339 """
4341 __visit_name__ = "constraint"
4343 _creation_order: int
4344 _column_flag: bool
4346 def __init__(
4347 self,
4348 name: _ConstraintNameArgument = None,
4349 deferrable: Optional[bool] = None,
4350 initially: Optional[str] = None,
4351 info: Optional[_InfoType] = None,
4352 comment: Optional[str] = None,
4353 _create_rule: Optional[Any] = None,
4354 _type_bound: bool = False,
4355 **dialect_kw: Any,
4356 ) -> None:
4357 r"""Create a SQL constraint.
4359 :param name:
4360 Optional, the in-database name of this ``Constraint``.
4362 :param deferrable:
4363 Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
4364 issuing DDL for this constraint.
4366 :param initially:
4367 Optional string. If set, emit INITIALLY <value> when issuing DDL
4368 for this constraint.
4370 :param info: Optional data dictionary which will be populated into the
4371 :attr:`.SchemaItem.info` attribute of this object.
4373 :param comment: Optional string that will render an SQL comment on
4374 foreign key constraint creation.
4376 .. versionadded:: 2.0
4378 :param \**dialect_kw: Additional keyword arguments are dialect
4379 specific, and passed in the form ``<dialectname>_<argname>``. See
4380 the documentation regarding an individual dialect at
4381 :ref:`dialect_toplevel` for detail on documented arguments.
4383 :param _create_rule:
4384 used internally by some datatypes that also create constraints.
4386 :param _type_bound:
4387 used internally to indicate that this constraint is associated with
4388 a specific datatype.
4390 """
4392 self.name = name
4393 self.deferrable = deferrable
4394 self.initially = initially
4395 if info:
4396 self.info = info
4397 self._create_rule = _create_rule
4398 self._type_bound = _type_bound
4399 util.set_creation_order(self)
4400 self._validate_dialect_kwargs(dialect_kw)
4401 self.comment = comment
4403 def _should_create_for_compiler(
4404 self, compiler: DDLCompiler, **kw: Any
4405 ) -> bool:
4406 if self._create_rule is not None and not self._create_rule(compiler):
4407 return False
4408 elif self._ddl_if is not None:
4409 return self._ddl_if._should_execute(
4410 ddl.CreateConstraint(self), self, None, compiler=compiler, **kw
4411 )
4412 else:
4413 return True
4415 @property
4416 def table(self) -> Table:
4417 try:
4418 if isinstance(self.parent, Table):
4419 return self.parent
4420 except AttributeError:
4421 pass
4422 raise exc.InvalidRequestError(
4423 "This constraint is not bound to a table. Did you "
4424 "mean to call table.append_constraint(constraint) ?"
4425 )
4427 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4428 assert isinstance(parent, (Table, Column))
4429 self.parent = parent
4430 parent.constraints.add(self)
4432 @util.deprecated(
4433 "1.4",
4434 "The :meth:`_schema.Constraint.copy` method is deprecated "
4435 "and will be removed in a future release.",
4436 )
4437 def copy(self, **kw: Any) -> Self:
4438 return self._copy(**kw)
4440 def _copy(self, **kw: Any) -> Self:
4441 raise NotImplementedError()
4444class ColumnCollectionMixin:
4445 """A :class:`_expression.ColumnCollection` of :class:`_schema.Column`
4446 objects.
4448 This collection represents the columns which are referred to by
4449 this object.
4451 """
4453 _columns: DedupeColumnCollection[Column[Any]]
4455 _allow_multiple_tables = False
4457 _pending_colargs: List[Optional[Union[str, Column[Any]]]]
4459 if TYPE_CHECKING:
4461 def _set_parent_with_dispatch(
4462 self, parent: SchemaEventTarget, **kw: Any
4463 ) -> None: ...
4465 def __init__(
4466 self,
4467 *columns: _DDLColumnArgument,
4468 _autoattach: bool = True,
4469 _column_flag: bool = False,
4470 _gather_expressions: Optional[
4471 List[Union[str, ColumnElement[Any]]]
4472 ] = None,
4473 ) -> None:
4474 self._column_flag = _column_flag
4475 self._columns = DedupeColumnCollection()
4477 processed_expressions: Optional[
4478 List[Union[ColumnElement[Any], str]]
4479 ] = _gather_expressions
4481 if processed_expressions is not None:
4483 # this is expected to be an empty list
4484 assert not processed_expressions
4486 self._pending_colargs = []
4487 for (
4488 expr,
4489 _,
4490 _,
4491 add_element,
4492 ) in coercions.expect_col_expression_collection(
4493 roles.DDLConstraintColumnRole, columns
4494 ):
4495 self._pending_colargs.append(add_element)
4496 processed_expressions.append(expr)
4497 else:
4498 self._pending_colargs = [
4499 coercions.expect(roles.DDLConstraintColumnRole, column)
4500 for column in columns
4501 ]
4503 if _autoattach and self._pending_colargs:
4504 self._check_attach()
4506 def _check_attach(self, evt: bool = False) -> None:
4507 col_objs = [c for c in self._pending_colargs if isinstance(c, Column)]
4509 cols_w_table = [c for c in col_objs if isinstance(c.table, Table)]
4511 cols_wo_table = set(col_objs).difference(cols_w_table)
4512 if cols_wo_table:
4513 # feature #3341 - place event listeners for Column objects
4514 # such that when all those cols are attached, we autoattach.
4515 assert not evt, "Should not reach here on event call"
4517 # issue #3411 - don't do the per-column auto-attach if some of the
4518 # columns are specified as strings.
4519 has_string_cols = {
4520 c for c in self._pending_colargs if c is not None
4521 }.difference(col_objs)
4522 if not has_string_cols:
4524 def _col_attached(column: Column[Any], table: Table) -> None:
4525 # this isinstance() corresponds with the
4526 # isinstance() above; only want to count Table-bound
4527 # columns
4528 if isinstance(table, Table):
4529 cols_wo_table.discard(column)
4530 if not cols_wo_table:
4531 self._check_attach(evt=True)
4533 self._cols_wo_table = cols_wo_table
4534 for col in cols_wo_table:
4535 col._on_table_attach(_col_attached)
4536 return
4538 columns = cols_w_table
4540 tables = {c.table for c in columns}
4541 if len(tables) == 1:
4542 self._set_parent_with_dispatch(tables.pop())
4543 elif len(tables) > 1 and not self._allow_multiple_tables:
4544 table = columns[0].table
4545 others = [c for c in columns[1:] if c.table is not table]
4546 if others:
4547 # black could not format this inline
4548 other_str = ", ".join("'%s'" % c for c in others)
4549 raise exc.ArgumentError(
4550 f"Column(s) {other_str} "
4551 f"are not part of table '{table.description}'."
4552 )
4554 @util.ro_memoized_property
4555 def columns(self) -> ReadOnlyColumnCollection[str, Column[Any]]:
4556 return self._columns.as_readonly()
4558 @util.ro_memoized_property
4559 def c(self) -> ReadOnlyColumnCollection[str, Column[Any]]:
4560 return self._columns.as_readonly()
4562 def _col_expressions(
4563 self, parent: Union[Table, Column[Any]]
4564 ) -> List[Optional[Column[Any]]]:
4565 if isinstance(parent, Column):
4566 result: List[Optional[Column[Any]]] = [
4567 c for c in self._pending_colargs if isinstance(c, Column)
4568 ]
4569 assert len(result) == len(self._pending_colargs)
4570 return result
4571 else:
4572 try:
4573 return [
4574 parent.c[col] if isinstance(col, str) else col
4575 for col in self._pending_colargs
4576 ]
4577 except KeyError as ke:
4578 raise exc.ConstraintColumnNotFoundError(
4579 f"Can't create {self.__class__.__name__} "
4580 f"on table '{parent.description}': no column "
4581 f"named '{ke.args[0]}' is present."
4582 ) from ke
4584 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4585 assert isinstance(parent, (Table, Column))
4587 for col in self._col_expressions(parent):
4588 if col is not None:
4589 self._columns.add(col)
4592class ColumnCollectionConstraint(ColumnCollectionMixin, Constraint):
4593 """A constraint that proxies a ColumnCollection."""
4595 def __init__(
4596 self,
4597 *columns: _DDLColumnArgument,
4598 name: _ConstraintNameArgument = None,
4599 deferrable: Optional[bool] = None,
4600 initially: Optional[str] = None,
4601 info: Optional[_InfoType] = None,
4602 _autoattach: bool = True,
4603 _column_flag: bool = False,
4604 _gather_expressions: Optional[List[_DDLColumnArgument]] = None,
4605 **dialect_kw: Any,
4606 ) -> None:
4607 r"""
4608 :param \*columns:
4609 A sequence of column names or Column objects.
4611 :param name:
4612 Optional, the in-database name of this constraint.
4614 :param deferrable:
4615 Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
4616 issuing DDL for this constraint.
4618 :param initially:
4619 Optional string. If set, emit INITIALLY <value> when issuing DDL
4620 for this constraint.
4622 :param \**dialect_kw: other keyword arguments including
4623 dialect-specific arguments are propagated to the :class:`.Constraint`
4624 superclass.
4626 """
4627 Constraint.__init__(
4628 self,
4629 name=name,
4630 deferrable=deferrable,
4631 initially=initially,
4632 info=info,
4633 **dialect_kw,
4634 )
4635 ColumnCollectionMixin.__init__(
4636 self, *columns, _autoattach=_autoattach, _column_flag=_column_flag
4637 )
4639 columns: ReadOnlyColumnCollection[str, Column[Any]]
4640 """A :class:`_expression.ColumnCollection` representing the set of columns
4641 for this constraint.
4643 """
4645 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4646 assert isinstance(parent, (Column, Table))
4647 Constraint._set_parent(self, parent)
4648 ColumnCollectionMixin._set_parent(self, parent)
4650 def __contains__(self, x: Any) -> bool:
4651 return x in self._columns
4653 @util.deprecated(
4654 "1.4",
4655 "The :meth:`_schema.ColumnCollectionConstraint.copy` method "
4656 "is deprecated and will be removed in a future release.",
4657 )
4658 def copy(
4659 self,
4660 *,
4661 target_table: Optional[Table] = None,
4662 **kw: Any,
4663 ) -> ColumnCollectionConstraint:
4664 return self._copy(target_table=target_table, **kw)
4666 def _copy(
4667 self,
4668 *,
4669 target_table: Optional[Table] = None,
4670 **kw: Any,
4671 ) -> ColumnCollectionConstraint:
4672 # ticket #5276
4673 constraint_kwargs = {}
4674 for dialect_name in self.dialect_options:
4675 dialect_options = self.dialect_options[dialect_name]._non_defaults
4676 for (
4677 dialect_option_key,
4678 dialect_option_value,
4679 ) in dialect_options.items():
4680 constraint_kwargs[dialect_name + "_" + dialect_option_key] = (
4681 dialect_option_value
4682 )
4684 assert isinstance(self.parent, Table)
4685 c = self.__class__(
4686 name=self.name,
4687 deferrable=self.deferrable,
4688 initially=self.initially,
4689 *[
4690 _copy_expression(expr, self.parent, target_table)
4691 for expr in self._columns
4692 ],
4693 comment=self.comment,
4694 **constraint_kwargs,
4695 )
4696 return self._schema_item_copy(c)
4698 def contains_column(self, col: Column[Any]) -> bool:
4699 """Return True if this constraint contains the given column.
4701 Note that this object also contains an attribute ``.columns``
4702 which is a :class:`_expression.ColumnCollection` of
4703 :class:`_schema.Column` objects.
4705 """
4707 return self._columns.contains_column(col)
4709 def __iter__(self) -> Iterator[Column[Any]]:
4710 return iter(self._columns)
4712 def __len__(self) -> int:
4713 return len(self._columns)
4716class CheckConstraint(ColumnCollectionConstraint):
4717 """A table- or column-level CHECK constraint.
4719 Can be included in the definition of a Table or Column.
4720 """
4722 _allow_multiple_tables = True
4724 __visit_name__ = "table_or_column_check_constraint"
4726 @_document_text_coercion(
4727 "sqltext",
4728 ":class:`.CheckConstraint`",
4729 ":paramref:`.CheckConstraint.sqltext`",
4730 )
4731 def __init__(
4732 self,
4733 sqltext: _TextCoercedExpressionArgument[Any],
4734 name: _ConstraintNameArgument = None,
4735 deferrable: Optional[bool] = None,
4736 initially: Optional[str] = None,
4737 table: Optional[Table] = None,
4738 info: Optional[_InfoType] = None,
4739 _create_rule: Optional[Any] = None,
4740 _autoattach: bool = True,
4741 _type_bound: bool = False,
4742 **dialect_kw: Any,
4743 ) -> None:
4744 r"""Construct a CHECK constraint.
4746 :param sqltext:
4747 A string containing the constraint definition, which will be used
4748 verbatim, or a SQL expression construct. If given as a string,
4749 the object is converted to a :func:`_expression.text` object.
4750 If the textual
4751 string includes a colon character, escape this using a backslash::
4753 CheckConstraint(r"foo ~ E'a(?\:b|c)d")
4755 :param name:
4756 Optional, the in-database name of the constraint.
4758 :param deferrable:
4759 Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
4760 issuing DDL for this constraint.
4762 :param initially:
4763 Optional string. If set, emit INITIALLY <value> when issuing DDL
4764 for this constraint.
4766 :param info: Optional data dictionary which will be populated into the
4767 :attr:`.SchemaItem.info` attribute of this object.
4769 """
4771 self.sqltext = coercions.expect(roles.DDLExpressionRole, sqltext)
4772 columns: List[Column[Any]] = []
4773 visitors.traverse(self.sqltext, {}, {"column": columns.append})
4775 super().__init__(
4776 name=name,
4777 deferrable=deferrable,
4778 initially=initially,
4779 _create_rule=_create_rule,
4780 info=info,
4781 _type_bound=_type_bound,
4782 _autoattach=_autoattach,
4783 *columns,
4784 **dialect_kw,
4785 )
4786 if table is not None:
4787 self._set_parent_with_dispatch(table)
4789 @property
4790 def is_column_level(self) -> bool:
4791 return not isinstance(self.parent, Table)
4793 @util.deprecated(
4794 "1.4",
4795 "The :meth:`_schema.CheckConstraint.copy` method is deprecated "
4796 "and will be removed in a future release.",
4797 )
4798 def copy(
4799 self, *, target_table: Optional[Table] = None, **kw: Any
4800 ) -> CheckConstraint:
4801 return self._copy(target_table=target_table, **kw)
4803 def _copy(
4804 self, *, target_table: Optional[Table] = None, **kw: Any
4805 ) -> CheckConstraint:
4806 if target_table is not None:
4807 # note that target_table is None for the copy process of
4808 # a column-bound CheckConstraint, so this path is not reached
4809 # in that case.
4810 sqltext = _copy_expression(self.sqltext, self.table, target_table)
4811 else:
4812 sqltext = self.sqltext
4813 c = CheckConstraint(
4814 sqltext,
4815 name=self.name,
4816 initially=self.initially,
4817 deferrable=self.deferrable,
4818 _create_rule=self._create_rule,
4819 table=target_table,
4820 comment=self.comment,
4821 _autoattach=False,
4822 _type_bound=self._type_bound,
4823 )
4824 return self._schema_item_copy(c)
4827class ForeignKeyConstraint(ColumnCollectionConstraint):
4828 """A table-level FOREIGN KEY constraint.
4830 Defines a single column or composite FOREIGN KEY ... REFERENCES
4831 constraint. For a no-frills, single column foreign key, adding a
4832 :class:`_schema.ForeignKey` to the definition of a :class:`_schema.Column`
4833 is a
4834 shorthand equivalent for an unnamed, single column
4835 :class:`_schema.ForeignKeyConstraint`.
4837 Examples of foreign key configuration are in :ref:`metadata_foreignkeys`.
4839 """
4841 __visit_name__ = "foreign_key_constraint"
4843 def __init__(
4844 self,
4845 columns: _typing_Sequence[_DDLColumnArgument],
4846 refcolumns: _typing_Sequence[_DDLColumnReferenceArgument],
4847 name: _ConstraintNameArgument = None,
4848 onupdate: Optional[str] = None,
4849 ondelete: Optional[str] = None,
4850 deferrable: Optional[bool] = None,
4851 initially: Optional[str] = None,
4852 use_alter: bool = False,
4853 link_to_name: bool = False,
4854 match: Optional[str] = None,
4855 table: Optional[Table] = None,
4856 info: Optional[_InfoType] = None,
4857 comment: Optional[str] = None,
4858 **dialect_kw: Any,
4859 ) -> None:
4860 r"""Construct a composite-capable FOREIGN KEY.
4862 :param columns: A sequence of local column names. The named columns
4863 must be defined and present in the parent Table. The names should
4864 match the ``key`` given to each column (defaults to the name) unless
4865 ``link_to_name`` is True.
4867 :param refcolumns: A sequence of foreign column names or Column
4868 objects. The columns must all be located within the same Table.
4870 :param name: Optional, the in-database name of the key.
4872 :param onupdate: Optional string. If set, emit ON UPDATE <value> when
4873 issuing DDL for this constraint. Typical values include CASCADE,
4874 DELETE and RESTRICT.
4876 .. seealso::
4878 :ref:`on_update_on_delete`
4880 :param ondelete: Optional string. If set, emit ON DELETE <value> when
4881 issuing DDL for this constraint. Typical values include CASCADE,
4882 SET NULL and RESTRICT. Some dialects may allow for additional
4883 syntaxes.
4885 .. seealso::
4887 :ref:`on_update_on_delete`
4889 :param deferrable: Optional bool. If set, emit DEFERRABLE or NOT
4890 DEFERRABLE when issuing DDL for this constraint.
4892 :param initially: Optional string. If set, emit INITIALLY <value> when
4893 issuing DDL for this constraint.
4895 :param link_to_name: if True, the string name given in ``column`` is
4896 the rendered name of the referenced column, not its locally assigned
4897 ``key``.
4899 :param use_alter: If True, do not emit the DDL for this constraint as
4900 part of the CREATE TABLE definition. Instead, generate it via an
4901 ALTER TABLE statement issued after the full collection of tables
4902 have been created, and drop it via an ALTER TABLE statement before
4903 the full collection of tables are dropped.
4905 The use of :paramref:`_schema.ForeignKeyConstraint.use_alter` is
4906 particularly geared towards the case where two or more tables
4907 are established within a mutually-dependent foreign key constraint
4908 relationship; however, the :meth:`_schema.MetaData.create_all` and
4909 :meth:`_schema.MetaData.drop_all`
4910 methods will perform this resolution
4911 automatically, so the flag is normally not needed.
4913 .. seealso::
4915 :ref:`use_alter`
4917 :param match: Optional string. If set, emit MATCH <value> when issuing
4918 DDL for this constraint. Typical values include SIMPLE, PARTIAL
4919 and FULL.
4921 :param info: Optional data dictionary which will be populated into the
4922 :attr:`.SchemaItem.info` attribute of this object.
4924 :param comment: Optional string that will render an SQL comment on
4925 foreign key constraint creation.
4927 .. versionadded:: 2.0
4929 :param \**dialect_kw: Additional keyword arguments are dialect
4930 specific, and passed in the form ``<dialectname>_<argname>``. See
4931 the documentation regarding an individual dialect at
4932 :ref:`dialect_toplevel` for detail on documented arguments.
4934 """
4936 Constraint.__init__(
4937 self,
4938 name=name,
4939 deferrable=deferrable,
4940 initially=initially,
4941 info=info,
4942 comment=comment,
4943 **dialect_kw,
4944 )
4945 self.onupdate = onupdate
4946 self.ondelete = ondelete
4947 self.link_to_name = link_to_name
4948 self.use_alter = use_alter
4949 self.match = match
4951 if len(set(columns)) != len(refcolumns):
4952 if len(set(columns)) != len(columns):
4953 # e.g. FOREIGN KEY (a, a) REFERENCES r (b, c)
4954 raise exc.ArgumentError(
4955 "ForeignKeyConstraint with duplicate source column "
4956 "references are not supported."
4957 )
4958 else:
4959 # e.g. FOREIGN KEY (a) REFERENCES r (b, c)
4960 # paraphrasing
4961 # https://www.postgresql.org/docs/current/static/ddl-constraints.html
4962 raise exc.ArgumentError(
4963 "ForeignKeyConstraint number "
4964 "of constrained columns must match the number of "
4965 "referenced columns."
4966 )
4968 # standalone ForeignKeyConstraint - create
4969 # associated ForeignKey objects which will be applied to hosted
4970 # Column objects (in col.foreign_keys), either now or when attached
4971 # to the Table for string-specified names
4972 self.elements = [
4973 ForeignKey(
4974 refcol,
4975 _constraint=self,
4976 name=self.name,
4977 onupdate=self.onupdate,
4978 ondelete=self.ondelete,
4979 use_alter=self.use_alter,
4980 link_to_name=self.link_to_name,
4981 match=self.match,
4982 deferrable=self.deferrable,
4983 initially=self.initially,
4984 **self.dialect_kwargs,
4985 )
4986 for refcol in refcolumns
4987 ]
4989 ColumnCollectionMixin.__init__(self, *columns)
4990 if table is not None:
4991 if hasattr(self, "parent"):
4992 assert table is self.parent
4993 self._set_parent_with_dispatch(table)
4995 def _append_element(self, column: Column[Any], fk: ForeignKey) -> None:
4996 self._columns.add(column)
4997 self.elements.append(fk)
4999 columns: ReadOnlyColumnCollection[str, Column[Any]]
5000 """A :class:`_expression.ColumnCollection` representing the set of columns
5001 for this constraint.
5003 """
5005 elements: List[ForeignKey]
5006 """A sequence of :class:`_schema.ForeignKey` objects.
5008 Each :class:`_schema.ForeignKey`
5009 represents a single referring column/referred
5010 column pair.
5012 This collection is intended to be read-only.
5014 """
5016 @property
5017 def _elements(self) -> util.OrderedDict[str, ForeignKey]:
5018 # legacy - provide a dictionary view of (column_key, fk)
5019 return util.OrderedDict(zip(self.column_keys, self.elements))
5021 @property
5022 def _referred_schema(self) -> Optional[str]:
5023 for elem in self.elements:
5024 return elem._referred_schema
5025 else:
5026 return None
5028 @property
5029 def referred_table(self) -> Table:
5030 """The :class:`_schema.Table` object to which this
5031 :class:`_schema.ForeignKeyConstraint` references.
5033 This is a dynamically calculated attribute which may not be available
5034 if the constraint and/or parent table is not yet associated with
5035 a metadata collection that contains the referred table.
5037 """
5038 return self.elements[0].column.table
5040 def _validate_dest_table(self, table: Table) -> None:
5041 table_keys = {
5042 elem._table_key_within_construction() for elem in self.elements
5043 }
5044 if None not in table_keys and len(table_keys) > 1:
5045 elem0, elem1 = sorted(table_keys)[0:2]
5046 raise exc.ArgumentError(
5047 f"ForeignKeyConstraint on "
5048 f"{table.fullname}({self._col_description}) refers to "
5049 f"multiple remote tables: {elem0} and {elem1}"
5050 )
5052 @property
5053 def column_keys(self) -> _typing_Sequence[str]:
5054 """Return a list of string keys representing the local
5055 columns in this :class:`_schema.ForeignKeyConstraint`.
5057 This list is either the original string arguments sent
5058 to the constructor of the :class:`_schema.ForeignKeyConstraint`,
5059 or if the constraint has been initialized with :class:`_schema.Column`
5060 objects, is the string ``.key`` of each element.
5062 """
5063 if hasattr(self, "parent"):
5064 return self._columns.keys()
5065 else:
5066 return [
5067 col.key if isinstance(col, ColumnElement) else str(col)
5068 for col in self._pending_colargs
5069 ]
5071 @property
5072 def _col_description(self) -> str:
5073 return ", ".join(self.column_keys)
5075 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
5076 table = parent
5077 assert isinstance(table, Table)
5078 Constraint._set_parent(self, table)
5080 ColumnCollectionConstraint._set_parent(self, table)
5082 for col, fk in zip(self._columns, self.elements):
5083 if not hasattr(fk, "parent") or fk.parent is not col:
5084 fk._set_parent_with_dispatch(col)
5086 self._validate_dest_table(table)
5088 @util.deprecated(
5089 "1.4",
5090 "The :meth:`_schema.ForeignKeyConstraint.copy` method is deprecated "
5091 "and will be removed in a future release.",
5092 )
5093 def copy(
5094 self,
5095 *,
5096 schema: Optional[str] = None,
5097 target_table: Optional[Table] = None,
5098 **kw: Any,
5099 ) -> ForeignKeyConstraint:
5100 return self._copy(schema=schema, target_table=target_table, **kw)
5102 def _copy(
5103 self,
5104 *,
5105 schema: Optional[str] = None,
5106 target_table: Optional[Table] = None,
5107 **kw: Any,
5108 ) -> ForeignKeyConstraint:
5109 fkc = ForeignKeyConstraint(
5110 [x.parent.key for x in self.elements],
5111 [
5112 x._get_colspec(
5113 schema=schema,
5114 table_name=(
5115 target_table.name
5116 if target_table is not None
5117 and x._table_key_within_construction()
5118 == x.parent.table.key
5119 else None
5120 ),
5121 _is_copy=True,
5122 )
5123 for x in self.elements
5124 ],
5125 name=self.name,
5126 onupdate=self.onupdate,
5127 ondelete=self.ondelete,
5128 use_alter=self.use_alter,
5129 deferrable=self.deferrable,
5130 initially=self.initially,
5131 link_to_name=self.link_to_name,
5132 match=self.match,
5133 comment=self.comment,
5134 )
5135 for self_fk, other_fk in zip(self.elements, fkc.elements):
5136 self_fk._schema_item_copy(other_fk)
5137 return self._schema_item_copy(fkc)
5140class PrimaryKeyConstraint(ColumnCollectionConstraint):
5141 """A table-level PRIMARY KEY constraint.
5143 The :class:`.PrimaryKeyConstraint` object is present automatically
5144 on any :class:`_schema.Table` object; it is assigned a set of
5145 :class:`_schema.Column` objects corresponding to those marked with
5146 the :paramref:`_schema.Column.primary_key` flag::
5148 >>> my_table = Table(
5149 ... "mytable",
5150 ... metadata,
5151 ... Column("id", Integer, primary_key=True),
5152 ... Column("version_id", Integer, primary_key=True),
5153 ... Column("data", String(50)),
5154 ... )
5155 >>> my_table.primary_key
5156 PrimaryKeyConstraint(
5157 Column('id', Integer(), table=<mytable>,
5158 primary_key=True, nullable=False),
5159 Column('version_id', Integer(), table=<mytable>,
5160 primary_key=True, nullable=False)
5161 )
5163 The primary key of a :class:`_schema.Table` can also be specified by using
5164 a :class:`.PrimaryKeyConstraint` object explicitly; in this mode of usage,
5165 the "name" of the constraint can also be specified, as well as other
5166 options which may be recognized by dialects::
5168 my_table = Table(
5169 "mytable",
5170 metadata,
5171 Column("id", Integer),
5172 Column("version_id", Integer),
5173 Column("data", String(50)),
5174 PrimaryKeyConstraint("id", "version_id", name="mytable_pk"),
5175 )
5177 The two styles of column-specification should generally not be mixed.
5178 An warning is emitted if the columns present in the
5179 :class:`.PrimaryKeyConstraint`
5180 don't match the columns that were marked as ``primary_key=True``, if both
5181 are present; in this case, the columns are taken strictly from the
5182 :class:`.PrimaryKeyConstraint` declaration, and those columns otherwise
5183 marked as ``primary_key=True`` are ignored. This behavior is intended to
5184 be backwards compatible with previous behavior.
5186 For the use case where specific options are to be specified on the
5187 :class:`.PrimaryKeyConstraint`, but the usual style of using
5188 ``primary_key=True`` flags is still desirable, an empty
5189 :class:`.PrimaryKeyConstraint` may be specified, which will take on the
5190 primary key column collection from the :class:`_schema.Table` based on the
5191 flags::
5193 my_table = Table(
5194 "mytable",
5195 metadata,
5196 Column("id", Integer, primary_key=True),
5197 Column("version_id", Integer, primary_key=True),
5198 Column("data", String(50)),
5199 PrimaryKeyConstraint(name="mytable_pk", mssql_clustered=True),
5200 )
5202 """
5204 __visit_name__ = "primary_key_constraint"
5206 def __init__(
5207 self,
5208 *columns: _DDLColumnArgument,
5209 name: Optional[str] = None,
5210 deferrable: Optional[bool] = None,
5211 initially: Optional[str] = None,
5212 info: Optional[_InfoType] = None,
5213 _implicit_generated: bool = False,
5214 **dialect_kw: Any,
5215 ) -> None:
5216 self._implicit_generated = _implicit_generated
5217 super().__init__(
5218 *columns,
5219 name=name,
5220 deferrable=deferrable,
5221 initially=initially,
5222 info=info,
5223 **dialect_kw,
5224 )
5226 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
5227 table = parent
5228 assert isinstance(table, Table)
5229 super()._set_parent(table)
5231 if table.primary_key is not self:
5232 table.constraints.discard(table.primary_key)
5233 table.primary_key = self # type: ignore
5234 table.constraints.add(self)
5236 table_pks = [c for c in table.c if c.primary_key]
5237 if (
5238 self._columns
5239 and table_pks
5240 and set(table_pks) != set(self._columns)
5241 ):
5242 # black could not format these inline
5243 table_pk_str = ", ".join("'%s'" % c.name for c in table_pks)
5244 col_str = ", ".join("'%s'" % c.name for c in self._columns)
5246 util.warn(
5247 f"Table '{table.name}' specifies columns "
5248 f"{table_pk_str} as "
5249 f"primary_key=True, "
5250 f"not matching locally specified columns {col_str}; "
5251 f"setting the "
5252 f"current primary key columns to "
5253 f"{col_str}. "
5254 f"This warning "
5255 f"may become an exception in a future release"
5256 )
5257 table_pks[:] = []
5259 for c in self._columns:
5260 c.primary_key = True
5261 if c._user_defined_nullable is NULL_UNSPECIFIED:
5262 c.nullable = False
5263 if table_pks:
5264 self._columns.extend(table_pks)
5266 def _reload(self, columns: Iterable[Column[Any]]) -> None:
5267 """repopulate this :class:`.PrimaryKeyConstraint` given
5268 a set of columns.
5270 Existing columns in the table that are marked as primary_key=True
5271 are maintained.
5273 Also fires a new event.
5275 This is basically like putting a whole new
5276 :class:`.PrimaryKeyConstraint` object on the parent
5277 :class:`_schema.Table` object without actually replacing the object.
5279 The ordering of the given list of columns is also maintained; these
5280 columns will be appended to the list of columns after any which
5281 are already present.
5283 """
5284 # set the primary key flag on new columns.
5285 # note any existing PK cols on the table also have their
5286 # flag still set.
5287 for col in columns:
5288 col.primary_key = True
5290 self._columns.extend(columns)
5292 PrimaryKeyConstraint._autoincrement_column._reset(self) # type: ignore
5293 self._set_parent_with_dispatch(self.table)
5295 def _replace(self, col: Column[Any]) -> None:
5296 PrimaryKeyConstraint._autoincrement_column._reset(self) # type: ignore
5297 self._columns.replace(col)
5299 self.dispatch._sa_event_column_added_to_pk_constraint(self, col)
5301 @property
5302 def columns_autoinc_first(self) -> List[Column[Any]]:
5303 autoinc = self._autoincrement_column
5305 if autoinc is not None:
5306 return [autoinc] + [c for c in self._columns if c is not autoinc]
5307 else:
5308 return list(self._columns)
5310 @util.ro_memoized_property
5311 def _autoincrement_column(self) -> Optional[Column[int]]:
5312 def _validate_autoinc(col: Column[Any], autoinc_true: bool) -> bool:
5313 if col.type._type_affinity is not None and issubclass(
5314 col.type._type_affinity, type_api.NUMERICTYPE._type_affinity
5315 ):
5316 scale = col.type.scale # type: ignore[attr-defined]
5317 if scale != 0 and autoinc_true:
5318 raise exc.ArgumentError(
5319 f"Column type {col.type} with non-zero scale "
5320 f"{scale} on column '{col}' is not "
5321 f"compatible with autoincrement=True"
5322 )
5323 elif not autoinc_true:
5324 return False
5325 elif col.type._type_affinity is None or not issubclass(
5326 col.type._type_affinity, type_api.INTEGERTYPE._type_affinity
5327 ):
5328 if autoinc_true:
5329 raise exc.ArgumentError(
5330 f"Column type {col.type} on column '{col}' is not "
5331 f"compatible with autoincrement=True"
5332 )
5333 else:
5334 return False
5335 elif (
5336 col.default is not None
5337 and not isinstance(col.default, Sequence)
5338 and not autoinc_true
5339 ):
5340 return False
5341 elif (
5342 col.server_default is not None
5343 and not isinstance(col.server_default, Identity)
5344 and not autoinc_true
5345 ):
5346 return False
5347 elif col.foreign_keys and col.autoincrement not in (
5348 True,
5349 "ignore_fk",
5350 ):
5351 return False
5352 return True
5354 if len(self._columns) == 1:
5355 col = list(self._columns)[0]
5357 if col.autoincrement is True:
5358 _validate_autoinc(col, True)
5359 return col
5360 elif col.autoincrement in (
5361 "auto",
5362 "ignore_fk",
5363 ) and _validate_autoinc(col, False):
5364 return col
5365 else:
5366 return None
5368 else:
5369 autoinc = None
5370 for col in self._columns:
5371 if col.autoincrement is True:
5372 _validate_autoinc(col, True)
5373 if autoinc is not None:
5374 raise exc.ArgumentError(
5375 f"Only one Column may be marked "
5376 f"autoincrement=True, found both "
5377 f"{col.name} and {autoinc.name}."
5378 )
5379 else:
5380 autoinc = col
5382 return autoinc
5385class UniqueConstraint(ColumnCollectionConstraint):
5386 """A table-level UNIQUE constraint.
5388 Defines a single column or composite UNIQUE constraint. For a no-frills,
5389 single column constraint, adding ``unique=True`` to the ``Column``
5390 definition is a shorthand equivalent for an unnamed, single column
5391 UniqueConstraint.
5392 """
5394 __visit_name__ = "unique_constraint"
5397class Index(
5398 DialectKWArgs, ColumnCollectionMixin, HasConditionalDDL, SchemaItem
5399):
5400 """A table-level INDEX.
5402 Defines a composite (one or more column) INDEX.
5404 E.g.::
5406 sometable = Table(
5407 "sometable",
5408 metadata,
5409 Column("name", String(50)),
5410 Column("address", String(100)),
5411 )
5413 Index("some_index", sometable.c.name)
5415 For a no-frills, single column index, adding
5416 :class:`_schema.Column` also supports ``index=True``::
5418 sometable = Table(
5419 "sometable", metadata, Column("name", String(50), index=True)
5420 )
5422 For a composite index, multiple columns can be specified::
5424 Index("some_index", sometable.c.name, sometable.c.address)
5426 Functional indexes are supported as well, typically by using the
5427 :data:`.func` construct in conjunction with table-bound
5428 :class:`_schema.Column` objects::
5430 Index("some_index", func.lower(sometable.c.name))
5432 An :class:`.Index` can also be manually associated with a
5433 :class:`_schema.Table`,
5434 either through inline declaration or using
5435 :meth:`_schema.Table.append_constraint`. When this approach is used,
5436 the names
5437 of the indexed columns can be specified as strings::
5439 Table(
5440 "sometable",
5441 metadata,
5442 Column("name", String(50)),
5443 Column("address", String(100)),
5444 Index("some_index", "name", "address"),
5445 )
5447 To support functional or expression-based indexes in this form, the
5448 :func:`_expression.text` construct may be used::
5450 from sqlalchemy import text
5452 Table(
5453 "sometable",
5454 metadata,
5455 Column("name", String(50)),
5456 Column("address", String(100)),
5457 Index("some_index", text("lower(name)")),
5458 )
5460 .. seealso::
5462 :ref:`schema_indexes` - General information on :class:`.Index`.
5464 :ref:`postgresql_indexes` - PostgreSQL-specific options available for
5465 the :class:`.Index` construct.
5467 :ref:`mysql_indexes` - MySQL-specific options available for the
5468 :class:`.Index` construct.
5470 :ref:`mssql_indexes` - MSSQL-specific options available for the
5471 :class:`.Index` construct.
5473 """
5475 __visit_name__ = "index"
5477 table: Optional[Table]
5478 expressions: _typing_Sequence[Union[str, ColumnElement[Any]]]
5479 _table_bound_expressions: _typing_Sequence[ColumnElement[Any]]
5481 def __init__(
5482 self,
5483 name: Optional[str],
5484 *expressions: _DDLColumnArgument,
5485 unique: bool = False,
5486 quote: Optional[bool] = None,
5487 info: Optional[_InfoType] = None,
5488 _table: Optional[Table] = None,
5489 _column_flag: bool = False,
5490 **dialect_kw: Any,
5491 ) -> None:
5492 r"""Construct an index object.
5494 :param name:
5495 The name of the index
5497 :param \*expressions:
5498 Column expressions to include in the index. The expressions
5499 are normally instances of :class:`_schema.Column`, but may also
5500 be arbitrary SQL expressions which ultimately refer to a
5501 :class:`_schema.Column`.
5503 :param unique=False:
5504 Keyword only argument; if True, create a unique index.
5506 :param quote=None:
5507 Keyword only argument; whether to apply quoting to the name of
5508 the index. Works in the same manner as that of
5509 :paramref:`_schema.Column.quote`.
5511 :param info=None: Optional data dictionary which will be populated
5512 into the :attr:`.SchemaItem.info` attribute of this object.
5514 :param \**dialect_kw: Additional keyword arguments not mentioned above
5515 are dialect specific, and passed in the form
5516 ``<dialectname>_<argname>``. See the documentation regarding an
5517 individual dialect at :ref:`dialect_toplevel` for detail on
5518 documented arguments.
5520 """
5521 self.table = table = None
5523 self.name = quoted_name.construct(name, quote)
5524 self.unique = unique
5525 if info is not None:
5526 self.info = info
5528 # TODO: consider "table" argument being public, but for
5529 # the purpose of the fix here, it starts as private.
5530 if _table is not None:
5531 table = _table
5533 self._validate_dialect_kwargs(dialect_kw)
5535 self.expressions = []
5536 # will call _set_parent() if table-bound column
5537 # objects are present
5538 ColumnCollectionMixin.__init__(
5539 self,
5540 *expressions,
5541 _column_flag=_column_flag,
5542 _gather_expressions=self.expressions,
5543 )
5544 if table is not None:
5545 self._set_parent(table)
5547 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
5548 table = parent
5549 assert isinstance(table, Table)
5550 ColumnCollectionMixin._set_parent(self, table)
5552 if self.table is not None and table is not self.table:
5553 raise exc.ArgumentError(
5554 f"Index '{self.name}' is against table "
5555 f"'{self.table.description}', and "
5556 f"cannot be associated with table '{table.description}'."
5557 )
5558 self.table = table
5559 table.indexes.add(self)
5561 expressions = self.expressions
5562 col_expressions = self._col_expressions(table)
5563 assert len(expressions) == len(col_expressions)
5565 exprs = []
5566 for expr, colexpr in zip(expressions, col_expressions):
5567 if isinstance(expr, ClauseElement):
5568 exprs.append(expr)
5569 elif colexpr is not None:
5570 exprs.append(colexpr)
5571 else:
5572 assert False
5573 self.expressions = self._table_bound_expressions = exprs
5575 def create(
5576 self,
5577 bind: _CreateDropBind,
5578 checkfirst: Union[bool, CheckFirst] = CheckFirst.NONE,
5579 ) -> None:
5580 """Issue a ``CREATE`` statement for this
5581 :class:`.Index`, using the given
5582 :class:`.Connection` or :class:`.Engine`` for connectivity.
5584 .. seealso::
5586 :meth:`_schema.MetaData.create_all`.
5588 """
5589 bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
5591 def drop(
5592 self,
5593 bind: _CreateDropBind,
5594 checkfirst: Union[bool, CheckFirst] = CheckFirst.NONE,
5595 ) -> None:
5596 """Issue a ``DROP`` statement for this
5597 :class:`.Index`, using the given
5598 :class:`.Connection` or :class:`.Engine` for connectivity.
5600 .. seealso::
5602 :meth:`_schema.MetaData.drop_all`.
5604 """
5605 bind._run_ddl_visitor(ddl.SchemaDropper, self, checkfirst=checkfirst)
5607 def __repr__(self) -> str:
5608 exprs: _typing_Sequence[Any] # noqa: F842
5610 return "Index(%s)" % (
5611 ", ".join(
5612 [repr(self.name)]
5613 + [repr(e) for e in self.expressions]
5614 + (self.unique and ["unique=True"] or [])
5615 )
5616 )
5619_NamingSchemaCallable = Callable[[Constraint, Table], str]
5620_NamingSchemaDirective = Union[str, _NamingSchemaCallable]
5623class _NamingSchemaTD(TypedDict, total=False):
5624 fk: _NamingSchemaDirective
5625 pk: _NamingSchemaDirective
5626 ix: _NamingSchemaDirective
5627 ck: _NamingSchemaDirective
5628 uq: _NamingSchemaDirective
5631_NamingSchemaParameter = Union[
5632 # it seems like the TypedDict here is useful for pylance typeahead,
5633 # and not much else
5634 _NamingSchemaTD,
5635 # there is no form that allows Union[Type[Any], str] to work in all
5636 # cases, including breaking out Mapping[] entries for each combination
5637 # even, therefore keys must be `Any` (see #10264)
5638 Mapping[Any, _NamingSchemaDirective],
5639]
5642DEFAULT_NAMING_CONVENTION: _NamingSchemaParameter = util.immutabledict(
5643 {"ix": "ix_%(column_0_label)s"}
5644)
5647class MetaData(HasSchemaAttr):
5648 """A collection of :class:`_schema.Table`
5649 objects and their associated schema
5650 constructs.
5652 Holds a collection of :class:`_schema.Table` objects as well as
5653 an optional binding to an :class:`_engine.Engine` or
5654 :class:`_engine.Connection`. If bound, the :class:`_schema.Table` objects
5655 in the collection and their columns may participate in implicit SQL
5656 execution.
5658 The :class:`_schema.Table` objects themselves are stored in the
5659 :attr:`_schema.MetaData.tables` dictionary.
5661 :class:`_schema.MetaData` is a thread-safe object for read operations.
5662 Construction of new tables within a single :class:`_schema.MetaData`
5663 object,
5664 either explicitly or via reflection, may not be completely thread-safe.
5666 .. seealso::
5668 :ref:`metadata_describing` - Introduction to database metadata
5670 """
5672 __visit_name__ = "metadata"
5674 def __init__(
5675 self,
5676 schema: Optional[str] = None,
5677 quote_schema: Optional[bool] = None,
5678 naming_convention: Optional[_NamingSchemaParameter] = None,
5679 info: Optional[_InfoType] = None,
5680 ) -> None:
5681 """Create a new MetaData object.
5683 :param schema:
5684 The default schema to use for the :class:`_schema.Table`,
5685 :class:`.Sequence`, and potentially other objects associated with
5686 this :class:`_schema.MetaData`. Defaults to ``None``.
5688 .. seealso::
5690 :ref:`schema_metadata_schema_name` - details on how the
5691 :paramref:`_schema.MetaData.schema` parameter is used.
5693 :paramref:`_schema.Table.schema`
5695 :paramref:`.Sequence.schema`
5697 :param quote_schema:
5698 Sets the ``quote_schema`` flag for those :class:`_schema.Table`,
5699 :class:`.Sequence`, and other objects which make usage of the
5700 local ``schema`` name.
5702 :param info: Optional data dictionary which will be populated into the
5703 :attr:`.SchemaItem.info` attribute of this object.
5705 :param naming_convention: a dictionary referring to values which
5706 will establish default naming conventions for :class:`.Constraint`
5707 and :class:`.Index` objects, for those objects which are not given
5708 a name explicitly.
5710 The keys of this dictionary may be:
5712 * a constraint or Index class, e.g. the :class:`.UniqueConstraint`,
5713 :class:`_schema.ForeignKeyConstraint` class, the :class:`.Index`
5714 class
5716 * a string mnemonic for one of the known constraint classes;
5717 ``"fk"``, ``"pk"``, ``"ix"``, ``"ck"``, ``"uq"`` for foreign key,
5718 primary key, index, check, and unique constraint, respectively.
5720 * the string name of a user-defined "token" that can be used
5721 to define new naming tokens.
5723 The values associated with each "constraint class" or "constraint
5724 mnemonic" key are string naming templates, such as
5725 ``"uq_%(table_name)s_%(column_0_name)s"``,
5726 which describe how the name should be composed. The values
5727 associated with user-defined "token" keys should be callables of the
5728 form ``fn(constraint, table)``, which accepts the constraint/index
5729 object and :class:`_schema.Table` as arguments, returning a string
5730 result.
5732 The built-in names are as follows, some of which may only be
5733 available for certain types of constraint:
5735 * ``%(table_name)s`` - the name of the :class:`_schema.Table`
5736 object
5737 associated with the constraint.
5739 * ``%(referred_table_name)s`` - the name of the
5740 :class:`_schema.Table`
5741 object associated with the referencing target of a
5742 :class:`_schema.ForeignKeyConstraint`.
5744 * ``%(column_0_name)s`` - the name of the :class:`_schema.Column`
5745 at
5746 index position "0" within the constraint.
5748 * ``%(column_0N_name)s`` - the name of all :class:`_schema.Column`
5749 objects in order within the constraint, joined without a
5750 separator.
5752 * ``%(column_0_N_name)s`` - the name of all
5753 :class:`_schema.Column`
5754 objects in order within the constraint, joined with an
5755 underscore as a separator.
5757 * ``%(column_0_label)s``, ``%(column_0N_label)s``,
5758 ``%(column_0_N_label)s`` - the label of either the zeroth
5759 :class:`_schema.Column` or all :class:`.Columns`, separated with
5760 or without an underscore
5762 * ``%(column_0_key)s``, ``%(column_0N_key)s``,
5763 ``%(column_0_N_key)s`` - the key of either the zeroth
5764 :class:`_schema.Column` or all :class:`.Columns`, separated with
5765 or without an underscore
5767 * ``%(referred_column_0_name)s``, ``%(referred_column_0N_name)s``
5768 ``%(referred_column_0_N_name)s``, ``%(referred_column_0_key)s``,
5769 ``%(referred_column_0N_key)s``, ... column tokens which
5770 render the names/keys/labels of columns that are referenced
5771 by a :class:`_schema.ForeignKeyConstraint`.
5773 * ``%(constraint_name)s`` - a special key that refers to the
5774 existing name given to the constraint. When this key is
5775 present, the :class:`.Constraint` object's existing name will be
5776 replaced with one that is composed from template string that
5777 uses this token. When this token is present, it is required that
5778 the :class:`.Constraint` is given an explicit name ahead of time.
5780 * user-defined: any additional token may be implemented by passing
5781 it along with a ``fn(constraint, table)`` callable to the
5782 naming_convention dictionary.
5784 .. seealso::
5786 :ref:`constraint_naming_conventions` - for detailed usage
5787 examples.
5789 """
5790 if schema is not None and not isinstance(schema, str):
5791 raise exc.ArgumentError(
5792 "expected schema argument to be a string, "
5793 f"got {type(schema)}."
5794 )
5795 self.tables = util.FacadeDict()
5796 self.schema = quoted_name.construct(schema, quote_schema)
5797 self.naming_convention = (
5798 naming_convention
5799 if naming_convention
5800 else DEFAULT_NAMING_CONVENTION
5801 )
5802 if info:
5803 self.info = info
5804 self._schemas: Set[str] = set()
5805 self._sequences: Dict[str, Sequence] = {}
5806 self._fk_memos: Dict[Tuple[str, Optional[str]], List[ForeignKey]] = (
5807 collections.defaultdict(list)
5808 )
5809 self._objects: Set[Union[HasSchemaAttr, SchemaType]] = set()
5811 tables: util.FacadeDict[str, Table]
5812 """A dictionary of :class:`_schema.Table`
5813 objects keyed to their name or "table key".
5815 The exact key is that determined by the :attr:`_schema.Table.key`
5816 attribute;
5817 for a table with no :attr:`_schema.Table.schema` attribute,
5818 this is the same
5819 as :attr:`_schema.Table.name`. For a table with a schema,
5820 it is typically of the
5821 form ``schemaname.tablename``.
5823 .. seealso::
5825 :attr:`_schema.MetaData.sorted_tables`
5827 """
5829 def __repr__(self) -> str:
5830 return "MetaData()"
5832 def __contains__(self, table_or_key: Union[str, Table]) -> bool:
5833 if not isinstance(table_or_key, str):
5834 table_or_key = table_or_key.key
5835 return table_or_key in self.tables
5837 def _add_table(
5838 self, name: str, schema: Optional[str], table: Table
5839 ) -> None:
5840 key = _get_table_key(name, schema)
5841 self.tables._insert_item(key, table)
5842 if schema:
5843 self._schemas.add(schema)
5845 def _remove_table(self, name: str, schema: Optional[str]) -> None:
5846 key = _get_table_key(name, schema)
5847 removed = dict.pop(self.tables, key, None)
5848 if removed is not None:
5849 for fk in removed.foreign_keys:
5850 fk._remove_from_metadata(self)
5851 if self._schemas:
5852 self._schemas = {
5853 t.schema for t in self.tables.values() if t.schema is not None
5854 }
5856 def __getstate__(self) -> Dict[str, Any]:
5857 return {
5858 "tables": self.tables,
5859 "schema": self.schema,
5860 "schemas": self._schemas,
5861 "sequences": self._sequences,
5862 "fk_memos": self._fk_memos,
5863 "naming_convention": self.naming_convention,
5864 "objects": self._objects,
5865 }
5867 def __setstate__(self, state: Dict[str, Any]) -> None:
5868 self.tables = state["tables"]
5869 self.schema = state["schema"]
5870 self.naming_convention = state["naming_convention"]
5871 self._sequences = state["sequences"]
5872 self._schemas = state["schemas"]
5873 self._fk_memos = state["fk_memos"]
5874 self._objects = state.get("objects", set())
5876 def clear(self) -> None:
5877 """Clear all objects from this MetaData."""
5879 dict.clear(self.tables)
5880 self._schemas.clear()
5881 self._fk_memos.clear()
5882 self._sequences.clear()
5883 self._objects.clear()
5885 def remove(self, table: Table) -> None:
5886 """Remove the given Table object from this MetaData."""
5888 self._remove_table(table.name, table.schema)
5890 @property
5891 def sorted_tables(self) -> List[Table]:
5892 """Returns a list of :class:`_schema.Table` objects sorted in order of
5893 foreign key dependency.
5895 The sorting will place :class:`_schema.Table`
5896 objects that have dependencies
5897 first, before the dependencies themselves, representing the
5898 order in which they can be created. To get the order in which
5899 the tables would be dropped, use the ``reversed()`` Python built-in.
5901 .. warning::
5903 The :attr:`.MetaData.sorted_tables` attribute cannot by itself
5904 accommodate automatic resolution of dependency cycles between
5905 tables, which are usually caused by mutually dependent foreign key
5906 constraints. When these cycles are detected, the foreign keys
5907 of these tables are omitted from consideration in the sort.
5908 A warning is emitted when this condition occurs, which will be an
5909 exception raise in a future release. Tables which are not part
5910 of the cycle will still be returned in dependency order.
5912 To resolve these cycles, the
5913 :paramref:`_schema.ForeignKeyConstraint.use_alter` parameter may be
5914 applied to those constraints which create a cycle. Alternatively,
5915 the :func:`_schema.sort_tables_and_constraints` function will
5916 automatically return foreign key constraints in a separate
5917 collection when cycles are detected so that they may be applied
5918 to a schema separately.
5920 .. seealso::
5922 :func:`_schema.sort_tables`
5924 :func:`_schema.sort_tables_and_constraints`
5926 :attr:`_schema.MetaData.tables`
5928 :meth:`_reflection.Inspector.get_table_names`
5930 :meth:`_reflection.Inspector.get_sorted_table_and_fkc_names`
5933 """
5934 return ddl.sort_tables(
5935 sorted(self.tables.values(), key=lambda t: t.key) # type: ignore
5936 )
5938 # overload needed to work around mypy this mypy
5939 # https://github.com/python/mypy/issues/17093
5940 @overload
5941 def reflect(
5942 self,
5943 bind: Engine,
5944 schema: Optional[str] = ...,
5945 views: bool = ...,
5946 only: Union[
5947 _typing_Sequence[str], Callable[[str, MetaData], bool], None
5948 ] = ...,
5949 extend_existing: bool = ...,
5950 autoload_replace: bool = ...,
5951 resolve_fks: bool = ...,
5952 **dialect_kwargs: Any,
5953 ) -> None: ...
5955 @overload
5956 def reflect(
5957 self,
5958 bind: Connection,
5959 schema: Optional[str] = ...,
5960 views: bool = ...,
5961 only: Union[
5962 _typing_Sequence[str], Callable[[str, MetaData], bool], None
5963 ] = ...,
5964 extend_existing: bool = ...,
5965 autoload_replace: bool = ...,
5966 resolve_fks: bool = ...,
5967 **dialect_kwargs: Any,
5968 ) -> None: ...
5970 @util.preload_module("sqlalchemy.engine.reflection")
5971 def reflect(
5972 self,
5973 bind: Union[Engine, Connection],
5974 schema: Optional[str] = None,
5975 views: bool = False,
5976 only: Union[
5977 _typing_Sequence[str], Callable[[str, MetaData], bool], None
5978 ] = None,
5979 extend_existing: bool = False,
5980 autoload_replace: bool = True,
5981 resolve_fks: bool = True,
5982 **dialect_kwargs: Any,
5983 ) -> None:
5984 r"""Load all available table definitions from the database.
5986 Automatically creates ``Table`` entries in this ``MetaData`` for any
5987 table available in the database but not yet present in the
5988 ``MetaData``. May be called multiple times to pick up tables recently
5989 added to the database, however no special action is taken if a table
5990 in this ``MetaData`` no longer exists in the database.
5992 :param bind:
5993 A :class:`.Connection` or :class:`.Engine` used to access the
5994 database.
5996 :param schema:
5997 Optional, query and reflect tables from an alternate schema.
5998 If None, the schema associated with this :class:`_schema.MetaData`
5999 is used, if any.
6001 :param views:
6002 If True, also reflect views (materialized and plain).
6004 :param only:
6005 Optional. Load only a sub-set of available named tables. May be
6006 specified as a sequence of names or a callable.
6008 If a sequence of names is provided, only those tables will be
6009 reflected. An error is raised if a table is requested but not
6010 available. Named tables already present in this ``MetaData`` are
6011 ignored.
6013 If a callable is provided, it will be used as a boolean predicate to
6014 filter the list of potential table names. The callable is called
6015 with a table name and this ``MetaData`` instance as positional
6016 arguments and should return a true value for any table to reflect.
6018 :param extend_existing: Passed along to each :class:`_schema.Table` as
6019 :paramref:`_schema.Table.extend_existing`.
6021 :param autoload_replace: Passed along to each :class:`_schema.Table`
6022 as
6023 :paramref:`_schema.Table.autoload_replace`.
6025 :param resolve_fks: if True, reflect :class:`_schema.Table`
6026 objects linked
6027 to :class:`_schema.ForeignKey` objects located in each
6028 :class:`_schema.Table`.
6029 For :meth:`_schema.MetaData.reflect`,
6030 this has the effect of reflecting
6031 related tables that might otherwise not be in the list of tables
6032 being reflected, for example if the referenced table is in a
6033 different schema or is omitted via the
6034 :paramref:`.MetaData.reflect.only` parameter. When False,
6035 :class:`_schema.ForeignKey` objects are not followed to the
6036 :class:`_schema.Table`
6037 in which they link, however if the related table is also part of the
6038 list of tables that would be reflected in any case, the
6039 :class:`_schema.ForeignKey` object will still resolve to its related
6040 :class:`_schema.Table` after the :meth:`_schema.MetaData.reflect`
6041 operation is
6042 complete. Defaults to True.
6044 .. seealso::
6046 :paramref:`_schema.Table.resolve_fks`
6048 :param \**dialect_kwargs: Additional keyword arguments not mentioned
6049 above are dialect specific, and passed in the form
6050 ``<dialectname>_<argname>``. See the documentation regarding an
6051 individual dialect at :ref:`dialect_toplevel` for detail on
6052 documented arguments.
6054 .. seealso::
6056 :ref:`metadata_reflection_toplevel`
6058 :meth:`_events.DDLEvents.column_reflect` - Event used to customize
6059 the reflected columns. Usually used to generalize the types using
6060 :meth:`_types.TypeEngine.as_generic`
6062 :ref:`metadata_reflection_dbagnostic_types` - describes how to
6063 reflect tables using general types.
6065 """
6067 with inspection.inspect(bind)._inspection_context() as insp:
6068 reflect_opts: Any = {
6069 "autoload_with": insp,
6070 "extend_existing": extend_existing,
6071 "autoload_replace": autoload_replace,
6072 "resolve_fks": resolve_fks,
6073 "_extend_on": set(),
6074 }
6076 reflect_opts.update(dialect_kwargs)
6078 if schema is None:
6079 schema = self.schema
6081 if schema is not None:
6082 reflect_opts["schema"] = schema
6084 kind = util.preloaded.engine_reflection.ObjectKind.TABLE
6085 available: util.OrderedSet[str] = util.OrderedSet(
6086 insp.get_table_names(schema, **dialect_kwargs)
6087 )
6088 if views:
6089 kind = util.preloaded.engine_reflection.ObjectKind.ANY
6090 available.update(insp.get_view_names(schema, **dialect_kwargs))
6091 try:
6092 available.update(
6093 insp.get_materialized_view_names(
6094 schema, **dialect_kwargs
6095 )
6096 )
6097 except NotImplementedError:
6098 pass
6100 if schema is not None:
6101 available_w_schema: util.OrderedSet[str] = util.OrderedSet(
6102 [f"{schema}.{name}" for name in available]
6103 )
6104 else:
6105 available_w_schema = available
6107 current = set(self.tables)
6109 if only is None:
6110 load = [
6111 name
6112 for name, schname in zip(available, available_w_schema)
6113 if extend_existing or schname not in current
6114 ]
6115 elif callable(only):
6116 load = [
6117 name
6118 for name, schname in zip(available, available_w_schema)
6119 if (extend_existing or schname not in current)
6120 and only(name, self)
6121 ]
6122 else:
6123 missing = [name for name in only if name not in available]
6124 if missing:
6125 s = schema and (" schema '%s'" % schema) or ""
6126 missing_str = ", ".join(missing)
6127 raise exc.InvalidRequestError(
6128 f"Could not reflect: requested table(s) not available "
6129 f"in {bind.engine!r}{s}: ({missing_str})"
6130 )
6131 load = [
6132 name
6133 for name in only
6134 if extend_existing or name not in current
6135 ]
6136 # pass the available tables so the inspector can
6137 # choose to ignore the filter_names
6138 _reflect_info = insp._get_reflection_info(
6139 schema=schema,
6140 filter_names=load,
6141 available=available,
6142 kind=kind,
6143 scope=util.preloaded.engine_reflection.ObjectScope.ANY,
6144 **dialect_kwargs,
6145 )
6146 reflect_opts["_reflect_info"] = _reflect_info
6148 for name in load:
6149 try:
6150 Table(name, self, **reflect_opts)
6151 except exc.UnreflectableTableError as uerr:
6152 util.warn(f"Skipping table {name}: {uerr}")
6154 def create_all(
6155 self,
6156 bind: _CreateDropBind,
6157 tables: Optional[_typing_Sequence[Table]] = None,
6158 checkfirst: Union[bool, CheckFirst] = CheckFirst.ALL,
6159 ) -> None:
6160 """Create all tables stored in this metadata.
6162 Conditional by default, will not attempt to recreate tables already
6163 present in the target database.
6165 :param bind:
6166 A :class:`.Connection` or :class:`.Engine` used to access the
6167 database.
6169 :param tables:
6170 Optional list of ``Table`` objects, which is a subset of the total
6171 tables in the ``MetaData`` (others are ignored).
6173 :param checkfirst: A boolean value or instance of :class:`.CheckFirst`.
6174 Indicates which objects should be checked for within a separate pass
6175 before creating schema objects.
6177 """
6178 bind._run_ddl_visitor(
6179 ddl.SchemaGenerator, self, checkfirst=checkfirst, tables=tables
6180 )
6182 def drop_all(
6183 self,
6184 bind: _CreateDropBind,
6185 tables: Optional[_typing_Sequence[Table]] = None,
6186 checkfirst: Union[bool, CheckFirst] = CheckFirst.ALL,
6187 ) -> None:
6188 """Drop all tables stored in this metadata.
6190 Conditional by default, will not attempt to drop tables not present in
6191 the target database.
6193 :param bind:
6194 A :class:`.Connection` or :class:`.Engine` used to access the
6195 database.
6197 :param tables:
6198 Optional list of ``Table`` objects, which is a subset of the
6199 total tables in the ``MetaData`` (others are ignored).
6201 :param checkfirst: A boolean value or instance of :class:`.CheckFirst`.
6202 Indicates which objects should be checked for within a separate pass
6203 before dropping schema objects.
6205 """
6206 bind._run_ddl_visitor(
6207 ddl.SchemaDropper, self, checkfirst=checkfirst, tables=tables
6208 )
6210 @property
6211 def schemas(self) -> _typing_Sequence[str]:
6212 """A sequence of schema names that are present in this MetaData."""
6213 schemas = self._schemas
6214 if self.schema:
6215 schemas = schemas | {self.schema}
6216 return tuple(schemas)
6218 def get_schema_objects(
6219 self,
6220 kind: Type[_T],
6221 *,
6222 schema: Union[str, None, Literal[_NoArg.NO_ARG]] = _NoArg.NO_ARG,
6223 ) -> _typing_Sequence[_T]:
6224 """Return a sequence of schema objects of the given kind.
6226 This method can be used to return :class:`_sqltypes.Enum`,
6227 :class:`.Sequence`, etc. objects registered in this
6228 :class:`_schema.MetaData`.
6230 :param kind: a type that indicates what object to return, such as
6231 :class:`Enum` or :class:`Sequence`.
6232 :param schema: Optional, a schema name to filter the objects by. If
6233 not provided the default schema of the metadata is used.
6235 """
6237 if schema is _NoArg.NO_ARG:
6238 schema = self.schema
6239 return tuple(
6240 obj
6241 for obj in self._objects
6242 if isinstance(obj, kind) and obj.schema == schema
6243 )
6245 def get_schema_object_by_name(
6246 self,
6247 kind: Type[_T],
6248 name: str,
6249 *,
6250 schema: Union[str, None, Literal[_NoArg.NO_ARG]] = _NoArg.NO_ARG,
6251 ) -> Optional[_T]:
6252 """Return a schema objects of the given kind and name if found.
6254 This method can be used to return :class:`_sqltypes.Enum`,
6255 :class:`.Sequence`, etc. objects registered in this
6256 :class:`_schema.MetaData`.
6258 :param kind: a type that indicates what object to return, such as
6259 :class:`Enum` or :class:`Sequence`.
6260 :param name: the name of the object to return.
6261 :param schema: Optional, a schema name to filter the objects by. If
6262 not provided the default schema of the metadata is used.
6264 """
6266 for obj in self.get_schema_objects(kind, schema=schema):
6267 if getattr(obj, "name", None) == name:
6268 return obj
6269 return None
6271 def _register_object(self, obj: Union[HasSchemaAttr, SchemaType]) -> None:
6272 self._objects.add(obj)
6275class Computed(FetchedValue, SchemaItem):
6276 """Defines a generated column, i.e. "GENERATED ALWAYS AS" syntax.
6278 The :class:`.Computed` construct is an inline construct added to the
6279 argument list of a :class:`_schema.Column` object::
6281 from sqlalchemy import Computed
6283 Table(
6284 "square",
6285 metadata_obj,
6286 Column("side", Float, nullable=False),
6287 Column("area", Float, Computed("side * side")),
6288 )
6290 See the linked documentation below for complete details.
6292 .. seealso::
6294 :ref:`computed_ddl`
6296 """
6298 __visit_name__ = "computed_column"
6300 column: Optional[Column[Any]]
6302 @_document_text_coercion(
6303 "sqltext", ":class:`.Computed`", ":paramref:`.Computed.sqltext`"
6304 )
6305 def __init__(
6306 self, sqltext: _DDLColumnArgument, persisted: Optional[bool] = None
6307 ) -> None:
6308 """Construct a GENERATED ALWAYS AS DDL construct to accompany a
6309 :class:`_schema.Column`.
6311 :param sqltext:
6312 A string containing the column generation expression, which will be
6313 used verbatim, or a SQL expression construct, such as a
6314 :func:`_expression.text`
6315 object. If given as a string, the object is converted to a
6316 :func:`_expression.text` object.
6318 :param persisted:
6319 Optional, controls how this column should be persisted by the
6320 database. Possible values are:
6322 * ``None``, the default, it will use the default persistence
6323 defined by the database.
6324 * ``True``, will render ``GENERATED ALWAYS AS ... STORED``, or the
6325 equivalent for the target database if supported.
6326 * ``False``, will render ``GENERATED ALWAYS AS ... VIRTUAL``, or
6327 the equivalent for the target database if supported.
6329 Specifying ``True`` or ``False`` may raise an error when the DDL
6330 is emitted to the target database if the database does not support
6331 that persistence option. Leaving this parameter at its default
6332 of ``None`` is guaranteed to succeed for all databases that support
6333 ``GENERATED ALWAYS AS``.
6335 """
6336 self.sqltext = coercions.expect(roles.DDLExpressionRole, sqltext)
6337 self.persisted = persisted
6338 self.column = None
6340 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
6341 assert isinstance(parent, Column)
6343 if not isinstance(
6344 parent.server_default, (type(None), Computed)
6345 ) or not isinstance(parent.server_onupdate, (type(None), Computed)):
6346 raise exc.ArgumentError(
6347 "A generated column cannot specify a server_default or a "
6348 "server_onupdate argument"
6349 )
6350 self.column = parent
6351 parent.computed = self
6352 self.column.server_onupdate = self
6353 self.column.server_default = self
6355 def _as_for_update(self, for_update: bool) -> FetchedValue:
6356 return self
6358 @util.deprecated(
6359 "1.4",
6360 "The :meth:`_schema.Computed.copy` method is deprecated "
6361 "and will be removed in a future release.",
6362 )
6363 def copy(
6364 self, *, target_table: Optional[Table] = None, **kw: Any
6365 ) -> Computed:
6366 return self._copy(target_table=target_table, **kw)
6368 def _copy(
6369 self, *, target_table: Optional[Table] = None, **kw: Any
6370 ) -> Computed:
6371 sqltext = _copy_expression(
6372 self.sqltext,
6373 self.column.table if self.column is not None else None,
6374 target_table,
6375 )
6376 g = Computed(sqltext, persisted=self.persisted)
6378 return self._schema_item_copy(g)
6381class Identity(IdentityOptions, FetchedValue, SchemaItem):
6382 """Defines an identity column, i.e. "GENERATED { ALWAYS | BY DEFAULT }
6383 AS IDENTITY" syntax.
6385 The :class:`.Identity` construct is an inline construct added to the
6386 argument list of a :class:`_schema.Column` object::
6388 from sqlalchemy import Identity
6390 Table(
6391 "foo",
6392 metadata_obj,
6393 Column("id", Integer, Identity()),
6394 Column("description", Text),
6395 )
6397 See the linked documentation below for complete details.
6399 .. versionadded:: 1.4
6401 .. seealso::
6403 :ref:`identity_ddl`
6405 """
6407 __visit_name__ = "identity_column"
6409 is_identity = True
6411 @util.deprecated_params(
6412 order=(
6413 "2.1",
6414 "This parameter is supported only by Oracle Database, "
6415 "use ``oracle_order`` instead.",
6416 ),
6417 on_null=(
6418 "2.1",
6419 "This parameter is supported only by Oracle Database, "
6420 "use ``oracle_on_null`` instead.",
6421 ),
6422 )
6423 def __init__(
6424 self,
6425 always: Optional[bool] = False,
6426 on_null: Optional[bool] = None,
6427 start: Optional[int] = None,
6428 increment: Optional[int] = None,
6429 minvalue: Optional[int] = None,
6430 maxvalue: Optional[int] = None,
6431 nominvalue: Optional[bool] = None,
6432 nomaxvalue: Optional[bool] = None,
6433 cycle: Optional[bool] = None,
6434 cache: Optional[int] = None,
6435 order: Optional[bool] = None,
6436 **dialect_kw: Any,
6437 ) -> None:
6438 """Construct a GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY DDL
6439 construct to accompany a :class:`_schema.Column`.
6441 See the :class:`.Sequence` documentation for a complete description
6442 of most parameters.
6444 .. note::
6445 MSSQL supports this construct as the preferred alternative to
6446 generate an IDENTITY on a column, but it uses non standard
6447 syntax that only support :paramref:`_schema.Identity.start`
6448 and :paramref:`_schema.Identity.increment`.
6449 All other parameters are ignored.
6451 :param always:
6452 A boolean, that indicates the type of identity column.
6453 If ``False`` is specified, the default, then the user-specified
6454 value takes precedence.
6455 If ``True`` is specified, a user-specified value is not accepted (
6456 on some backends, like PostgreSQL, OVERRIDING SYSTEM VALUE, or
6457 similar, may be specified in an INSERT to override the sequence
6458 value).
6459 Some backends also have a default value for this parameter,
6460 ``None`` can be used to omit rendering this part in the DDL. It
6461 will be treated as ``False`` if a backend does not have a default
6462 value.
6464 :param on_null:
6465 Set to ``True`` to specify ON NULL in conjunction with a
6466 ``always=False`` identity column. This option is only supported on
6467 some backends, like Oracle Database.
6469 :param start: the starting index of the sequence.
6470 :param increment: the increment value of the sequence.
6471 :param minvalue: the minimum value of the sequence.
6472 :param maxvalue: the maximum value of the sequence.
6473 :param nominvalue: no minimum value of the sequence.
6474 :param nomaxvalue: no maximum value of the sequence.
6475 :param cycle: allows the sequence to wrap around when the maxvalue
6476 or minvalue has been reached.
6477 :param cache: optional integer value; number of future values in the
6478 sequence which are calculated in advance.
6479 :param order: optional boolean value; if true, renders the
6480 ORDER keyword.
6482 """
6483 self.dialect_options
6484 if on_null is not None:
6485 if "oracle_on_null" in dialect_kw:
6486 raise exc.ArgumentError(
6487 "Cannot specify both 'on_null' and 'oracle_on_null'. "
6488 "Plese use only 'oracle_on_null'."
6489 )
6490 dialect_kw["oracle_on_null"] = on_null
6492 IdentityOptions.__init__(
6493 self,
6494 start=start,
6495 increment=increment,
6496 minvalue=minvalue,
6497 maxvalue=maxvalue,
6498 nominvalue=nominvalue,
6499 nomaxvalue=nomaxvalue,
6500 cycle=cycle,
6501 cache=cache,
6502 order=order,
6503 **dialect_kw,
6504 )
6505 self.always = always
6506 self.column = None
6508 @property
6509 def on_null(self) -> Optional[bool]:
6510 """Alias of the ``dialect_kwargs`` ``'oracle_on_null'``.
6512 .. deprecated:: 2.1 The 'on_null' attribute is deprecated.
6513 """
6514 value: Optional[bool] = self.dialect_kwargs.get("oracle_on_null")
6515 return value
6517 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
6518 assert isinstance(parent, Column)
6519 if not isinstance(
6520 parent.server_default, (type(None), Identity)
6521 ) or not isinstance(parent.server_onupdate, type(None)):
6522 raise exc.ArgumentError(
6523 "A column with an Identity object cannot specify a "
6524 "server_default or a server_onupdate argument"
6525 )
6526 if parent.autoincrement is False:
6527 raise exc.ArgumentError(
6528 "A column with an Identity object cannot specify "
6529 "autoincrement=False"
6530 )
6531 self.column = parent
6533 parent.identity = self
6534 if parent._user_defined_nullable is NULL_UNSPECIFIED:
6535 parent.nullable = False
6537 parent.server_default = self
6539 def _as_for_update(self, for_update: bool) -> FetchedValue:
6540 return self
6542 @util.deprecated(
6543 "1.4",
6544 "The :meth:`_schema.Identity.copy` method is deprecated "
6545 "and will be removed in a future release.",
6546 )
6547 def copy(self, **kw: Any) -> Identity:
6548 return self._copy(**kw)
6550 def _copy(self, **kw: Any) -> Identity:
6551 i = Identity(**self._as_dict(), **self.dialect_kwargs)
6553 return self._schema_item_copy(i)
6555 def _as_dict(self) -> Dict[str, Any]:
6556 return {
6557 # always=None means something different than always=False
6558 "always": self.always,
6559 **super()._as_dict(),
6560 }