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 default_is_sequence(the_sentinel_zero.default):
1151 if the_sentinel_zero.default._increment_is_negative:
1152 if sentinel_is_explicit:
1153 raise exc.InvalidRequestError(
1154 "Can't use SEQUENCE default with negative "
1155 "increment as an explicit sentinel column"
1156 )
1157 else:
1158 if sentinel_is_autoinc:
1159 autoinc_col = None
1160 sentinel_is_autoinc = False
1161 the_sentinel = None
1163 default_characterization = (
1164 _SentinelDefaultCharacterization.SEQUENCE
1165 )
1166 elif the_sentinel_zero.default.is_callable:
1167 default_characterization = (
1168 _SentinelDefaultCharacterization.CLIENTSIDE
1169 )
1170 elif the_sentinel_zero.server_default is not None:
1171 if sentinel_is_explicit:
1172 raise exc.InvalidRequestError(
1173 f"Column {the_sentinel[0]} can't be a sentinel column "
1174 "because it uses an explicit server side default "
1175 "that's not the Identity() default."
1176 )
1178 default_characterization = (
1179 _SentinelDefaultCharacterization.SERVERSIDE
1180 )
1182 if the_sentinel is None and self.primary_key:
1183 assert autoinc_col is None
1185 # determine for non-autoincrement pk if all elements are
1186 # client side
1187 for _pkc in self.primary_key:
1188 if _pkc.server_default is not None or (
1189 _pkc.default and not _pkc.default.is_callable
1190 ):
1191 break
1192 else:
1193 the_sentinel = tuple(self.primary_key)
1194 default_characterization = (
1195 _SentinelDefaultCharacterization.CLIENTSIDE
1196 )
1198 return _SentinelColumnCharacterization(
1199 the_sentinel,
1200 sentinel_is_explicit,
1201 sentinel_is_autoinc,
1202 default_characterization,
1203 )
1205 @property
1206 def autoincrement_column(self) -> Optional[Column[int]]:
1207 """Returns the :class:`.Column` object which currently represents
1208 the "auto increment" column, if any, else returns None.
1210 This is based on the rules for :class:`.Column` as defined by the
1211 :paramref:`.Column.autoincrement` parameter, which generally means the
1212 column within a single integer column primary key constraint that is
1213 not constrained by a foreign key. If the table does not have such
1214 a primary key constraint, then there's no "autoincrement" column.
1215 A :class:`.Table` may have only one column defined as the
1216 "autoincrement" column.
1218 .. versionadded:: 2.0.4
1220 .. seealso::
1222 :paramref:`.Column.autoincrement`
1224 """
1225 return self._autoincrement_column
1227 @property
1228 def key(self) -> str:
1229 """Return the 'key' for this :class:`_schema.Table`.
1231 This value is used as the dictionary key within the
1232 :attr:`_schema.MetaData.tables` collection. It is typically the same
1233 as that of :attr:`_schema.Table.name` for a table with no
1234 :attr:`_schema.Table.schema`
1235 set; otherwise it is typically of the form
1236 ``schemaname.tablename``.
1238 """
1239 return _get_table_key(self.name, self.schema)
1241 def __repr__(self) -> str:
1242 return "Table(%s)" % ", ".join(
1243 [repr(self.name)]
1244 + [repr(self.metadata)]
1245 + [repr(x) for x in self.columns]
1246 + ["%s=%s" % (k, repr(getattr(self, k))) for k in ["schema"]]
1247 )
1249 def __str__(self) -> str:
1250 return _get_table_key(self.description, self.schema)
1252 def add_is_dependent_on(self, table: Table) -> None:
1253 """Add a 'dependency' for this Table.
1255 This is another Table object which must be created
1256 first before this one can, or dropped after this one.
1258 Usually, dependencies between tables are determined via
1259 ForeignKey objects. However, for other situations that
1260 create dependencies outside of foreign keys (rules, inheriting),
1261 this method can manually establish such a link.
1263 """
1264 self._extra_dependencies.add(table)
1266 def _insert_col_impl(
1267 self,
1268 column: ColumnClause[Any],
1269 *,
1270 index: Optional[int] = None,
1271 replace_existing: bool = False,
1272 ) -> None:
1273 try:
1274 column._set_parent_with_dispatch(
1275 self,
1276 allow_replacements=replace_existing,
1277 all_names={c.name: c for c in self.c},
1278 index=index,
1279 )
1280 except exc.DuplicateColumnError as de:
1281 raise exc.DuplicateColumnError(
1282 f"{de.args[0]} Specify replace_existing=True to "
1283 "Table.append_column() or Table.insert_column() to replace an "
1284 "existing column."
1285 ) from de
1287 def insert_column(
1288 self,
1289 column: ColumnClause[Any],
1290 index: int,
1291 *,
1292 replace_existing: bool = False,
1293 ) -> None:
1294 """Insert a :class:`_schema.Column` to this :class:`_schema.Table` at
1295 a specific position.
1297 Behavior is identical to :meth:`.Table.append_column` except that
1298 the index position can be controlled using the
1299 :paramref:`.Table.insert_column.index`
1300 parameter.
1302 :param replace_existing:
1303 see :paramref:`.Table.append_column.replace_existing`
1304 :param index: integer index to insert the new column.
1306 .. versionadded:: 2.1
1308 """
1309 self._insert_col_impl(
1310 column, index=index, replace_existing=replace_existing
1311 )
1313 def append_column(
1314 self, column: ColumnClause[Any], *, replace_existing: bool = False
1315 ) -> None:
1316 """Append a :class:`_schema.Column` to this :class:`_schema.Table`.
1318 The "key" of the newly added :class:`_schema.Column`, i.e. the
1319 value of its ``.key`` attribute, will then be available
1320 in the ``.c`` collection of this :class:`_schema.Table`, and the
1321 column definition will be included in any CREATE TABLE, SELECT,
1322 UPDATE, etc. statements generated from this :class:`_schema.Table`
1323 construct.
1325 Note that this does **not** change the definition of the table
1326 as it exists within any underlying database, assuming that
1327 table has already been created in the database. Relational
1328 databases support the addition of columns to existing tables
1329 using the SQL ALTER command, which would need to be
1330 emitted for an already-existing table that doesn't contain
1331 the newly added column.
1333 :param replace_existing: When ``True``, allows replacing existing
1334 columns. When ``False``, the default, an warning will be raised
1335 if a column with the same ``.key`` already exists. A future
1336 version of sqlalchemy will instead rise a warning.
1338 .. versionadded:: 1.4.0
1340 .. seealso::
1342 :meth:`.Table.insert_column`
1344 """
1345 self._insert_col_impl(column, replace_existing=replace_existing)
1347 def append_constraint(self, constraint: Union[Index, Constraint]) -> None:
1348 """Append a :class:`_schema.Constraint` to this
1349 :class:`_schema.Table`.
1351 This has the effect of the constraint being included in any
1352 future CREATE TABLE statement, assuming specific DDL creation
1353 events have not been associated with the given
1354 :class:`_schema.Constraint` object.
1356 Note that this does **not** produce the constraint within the
1357 relational database automatically, for a table that already exists
1358 in the database. To add a constraint to an
1359 existing relational database table, the SQL ALTER command must
1360 be used. SQLAlchemy also provides the
1361 :class:`.AddConstraint` construct which can produce this SQL when
1362 invoked as an executable clause.
1364 """
1366 constraint._set_parent_with_dispatch(self)
1368 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
1369 metadata = parent
1370 assert isinstance(metadata, MetaData)
1371 metadata._add_table(self.name, self.schema, self)
1372 self.metadata = metadata
1374 def create(
1375 self,
1376 bind: _CreateDropBind,
1377 checkfirst: Union[bool, CheckFirst] = CheckFirst.TYPES,
1378 ) -> None:
1379 """Issue a ``CREATE`` statement for this
1380 :class:`_schema.Table`, using the given
1381 :class:`.Connection` or :class:`.Engine`
1382 for connectivity.
1384 .. seealso::
1386 :meth:`_schema.MetaData.create_all`.
1388 """
1390 # the default is to only check for schema objects
1391 bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
1393 def drop(
1394 self,
1395 bind: _CreateDropBind,
1396 checkfirst: Union[bool, CheckFirst] = CheckFirst.NONE,
1397 ) -> None:
1398 """Issue a ``DROP`` statement for this
1399 :class:`_schema.Table`, using the given
1400 :class:`.Connection` or :class:`.Engine` for connectivity.
1402 .. seealso::
1404 :meth:`_schema.MetaData.drop_all`.
1406 """
1407 bind._run_ddl_visitor(ddl.SchemaDropper, self, checkfirst=checkfirst)
1409 @util.deprecated(
1410 "1.4",
1411 ":meth:`_schema.Table.tometadata` is renamed to "
1412 ":meth:`_schema.Table.to_metadata`",
1413 )
1414 def tometadata(
1415 self,
1416 metadata: MetaData,
1417 schema: Union[str, Literal[SchemaConst.RETAIN_SCHEMA]] = RETAIN_SCHEMA,
1418 referred_schema_fn: Optional[
1419 Callable[
1420 [Table, Optional[str], ForeignKeyConstraint, Optional[str]],
1421 Optional[str],
1422 ]
1423 ] = None,
1424 name: Optional[str] = None,
1425 ) -> Table:
1426 """Return a copy of this :class:`_schema.Table`
1427 associated with a different
1428 :class:`_schema.MetaData`.
1430 See :meth:`_schema.Table.to_metadata` for a full description.
1432 """
1433 return self.to_metadata(
1434 metadata,
1435 schema=schema,
1436 referred_schema_fn=referred_schema_fn,
1437 name=name,
1438 )
1440 def to_metadata(
1441 self,
1442 metadata: MetaData,
1443 schema: Union[str, Literal[SchemaConst.RETAIN_SCHEMA]] = RETAIN_SCHEMA,
1444 referred_schema_fn: Optional[
1445 Callable[
1446 [Table, Optional[str], ForeignKeyConstraint, Optional[str]],
1447 Optional[str],
1448 ]
1449 ] = None,
1450 name: Optional[str] = None,
1451 ) -> Table:
1452 """Return a copy of this :class:`_schema.Table` associated with a
1453 different :class:`_schema.MetaData`.
1455 E.g.::
1457 m1 = MetaData()
1459 user = Table("user", m1, Column("id", Integer, primary_key=True))
1461 m2 = MetaData()
1462 user_copy = user.to_metadata(m2)
1464 .. versionchanged:: 1.4 The :meth:`_schema.Table.to_metadata` function
1465 was renamed from :meth:`_schema.Table.tometadata`.
1468 :param metadata: Target :class:`_schema.MetaData` object,
1469 into which the
1470 new :class:`_schema.Table` object will be created.
1472 :param schema: optional string name indicating the target schema.
1473 Defaults to the special symbol :attr:`.RETAIN_SCHEMA` which indicates
1474 that no change to the schema name should be made in the new
1475 :class:`_schema.Table`. If set to a string name, the new
1476 :class:`_schema.Table`
1477 will have this new name as the ``.schema``. If set to ``None``, the
1478 schema will be set to that of the schema set on the target
1479 :class:`_schema.MetaData`, which is typically ``None`` as well,
1480 unless
1481 set explicitly::
1483 m2 = MetaData(schema="newschema")
1485 # user_copy_one will have "newschema" as the schema name
1486 user_copy_one = user.to_metadata(m2, schema=None)
1488 m3 = MetaData() # schema defaults to None
1490 # user_copy_two will have None as the schema name
1491 user_copy_two = user.to_metadata(m3, schema=None)
1493 :param referred_schema_fn: optional callable which can be supplied
1494 in order to provide for the schema name that should be assigned
1495 to the referenced table of a :class:`_schema.ForeignKeyConstraint`.
1496 The callable accepts this parent :class:`_schema.Table`, the
1497 target schema that we are changing to, the
1498 :class:`_schema.ForeignKeyConstraint` object, and the existing
1499 "target schema" of that constraint. The function should return the
1500 string schema name that should be applied. To reset the schema
1501 to "none", return the symbol :data:`.BLANK_SCHEMA`. To effect no
1502 change, return ``None`` or :data:`.RETAIN_SCHEMA`.
1504 .. versionchanged:: 1.4.33 The ``referred_schema_fn`` function
1505 may return the :data:`.BLANK_SCHEMA` or :data:`.RETAIN_SCHEMA`
1506 symbols.
1508 E.g.::
1510 def referred_schema_fn(table, to_schema, constraint, referred_schema):
1511 if referred_schema == "base_tables":
1512 return referred_schema
1513 else:
1514 return to_schema
1517 new_table = table.to_metadata(
1518 m2, schema="alt_schema", referred_schema_fn=referred_schema_fn
1519 )
1521 :param name: optional string name indicating the target table name.
1522 If not specified or None, the table name is retained. This allows
1523 a :class:`_schema.Table` to be copied to the same
1524 :class:`_schema.MetaData` target
1525 with a new name.
1527 """ # noqa: E501
1528 if name is None:
1529 name = self.name
1531 actual_schema: Optional[str]
1533 if schema is RETAIN_SCHEMA:
1534 actual_schema = self.schema
1535 elif schema is None:
1536 actual_schema = metadata.schema
1537 else:
1538 actual_schema = schema
1539 key = _get_table_key(name, actual_schema)
1540 if key in metadata.tables:
1541 util.warn(
1542 f"Table '{self.description}' already exists within the given "
1543 "MetaData - not copying."
1544 )
1545 return metadata.tables[key]
1547 args = []
1548 for col in self.columns:
1549 args.append(col._copy(schema=actual_schema, _to_metadata=metadata))
1551 table = Table(
1552 name,
1553 metadata,
1554 schema=actual_schema,
1555 comment=self.comment,
1556 *args,
1557 **self.kwargs,
1558 )
1560 if self._creator_ddl is not None:
1561 table._creator_ddl = self._creator_ddl.to_metadata(metadata, table)
1562 if self._dropper_ddl is not None:
1563 table._dropper_ddl = self._dropper_ddl.to_metadata(metadata, table)
1565 for const in self.constraints:
1566 if isinstance(const, ForeignKeyConstraint):
1567 referred_schema = const._referred_schema
1568 if referred_schema_fn:
1569 fk_constraint_schema = referred_schema_fn(
1570 self, actual_schema, const, referred_schema
1571 )
1572 else:
1573 fk_constraint_schema = (
1574 actual_schema
1575 if referred_schema == self.schema
1576 else None
1577 )
1578 table.append_constraint(
1579 const._copy(
1580 schema=fk_constraint_schema, target_table=table
1581 )
1582 )
1583 elif not const._type_bound:
1584 # skip unique constraints that would be generated
1585 # by the 'unique' flag on Column
1586 if const._column_flag:
1587 continue
1589 table.append_constraint(
1590 const._copy(schema=actual_schema, target_table=table)
1591 )
1592 for index in self.indexes:
1593 # skip indexes that would be generated
1594 # by the 'index' flag on Column
1595 if index._column_flag:
1596 continue
1597 Index(
1598 index.name,
1599 unique=index.unique,
1600 *[
1601 _copy_expression(expr, self, table)
1602 for expr in index._table_bound_expressions
1603 ],
1604 _table=table,
1605 **index.kwargs,
1606 )
1607 return self._schema_item_copy(table)
1610class Column(DialectKWArgs, SchemaItem, ColumnClause[_T]):
1611 """Represents a column in a database table."""
1613 __visit_name__ = "column"
1615 inherit_cache = True
1616 key: str
1618 server_default: Optional[FetchedValue]
1620 def __init__(
1621 self,
1622 __name_pos: Optional[
1623 Union[str, _TypeEngineArgument[_T], SchemaEventTarget]
1624 ] = None,
1625 __type_pos: Optional[
1626 Union[_TypeEngineArgument[_T], SchemaEventTarget]
1627 ] = None,
1628 /,
1629 *args: SchemaEventTarget,
1630 name: Optional[str] = None,
1631 type_: Optional[_TypeEngineArgument[_T]] = None,
1632 autoincrement: _AutoIncrementType = "auto",
1633 default: Optional[Any] = _NoArg.NO_ARG,
1634 insert_default: Optional[Any] = _NoArg.NO_ARG,
1635 doc: Optional[str] = None,
1636 key: Optional[str] = None,
1637 index: Optional[bool] = None,
1638 unique: Optional[bool] = None,
1639 info: Optional[_InfoType] = None,
1640 nullable: Optional[
1641 Union[bool, Literal[SchemaConst.NULL_UNSPECIFIED]]
1642 ] = SchemaConst.NULL_UNSPECIFIED,
1643 onupdate: Optional[Any] = None,
1644 primary_key: bool = False,
1645 server_default: Optional[_ServerDefaultArgument] = None,
1646 server_onupdate: Optional[_ServerOnUpdateArgument] = None,
1647 quote: Optional[bool] = None,
1648 system: bool = False,
1649 comment: Optional[str] = None,
1650 insert_sentinel: bool = False,
1651 _omit_from_statements: bool = False,
1652 _proxies: Optional[Any] = None,
1653 **dialect_kwargs: Any,
1654 ):
1655 r"""
1656 Construct a new ``Column`` object.
1658 :param name: The name of this column as represented in the database.
1659 This argument may be the first positional argument, or specified
1660 via keyword.
1662 Names which contain no upper case characters
1663 will be treated as case insensitive names, and will not be quoted
1664 unless they are a reserved word. Names with any number of upper
1665 case characters will be quoted and sent exactly. Note that this
1666 behavior applies even for databases which standardize upper
1667 case names as case insensitive such as Oracle Database.
1669 The name field may be omitted at construction time and applied
1670 later, at any time before the Column is associated with a
1671 :class:`_schema.Table`. This is to support convenient
1672 usage within the :mod:`~sqlalchemy.ext.declarative` extension.
1674 :param type\_: The column's type, indicated using an instance which
1675 subclasses :class:`~sqlalchemy.types.TypeEngine`. If no arguments
1676 are required for the type, the class of the type can be sent
1677 as well, e.g.::
1679 # use a type with arguments
1680 Column("data", String(50))
1682 # use no arguments
1683 Column("level", Integer)
1685 The ``type`` argument may be the second positional argument
1686 or specified by keyword.
1688 If the ``type`` is ``None`` or is omitted, it will first default to
1689 the special type :class:`.NullType`. If and when this
1690 :class:`_schema.Column` is made to refer to another column using
1691 :class:`_schema.ForeignKey` and/or
1692 :class:`_schema.ForeignKeyConstraint`, the type
1693 of the remote-referenced column will be copied to this column as
1694 well, at the moment that the foreign key is resolved against that
1695 remote :class:`_schema.Column` object.
1697 :param \*args: Additional positional arguments include various
1698 :class:`.SchemaItem` derived constructs which will be applied
1699 as options to the column. These include instances of
1700 :class:`.Constraint`, :class:`_schema.ForeignKey`,
1701 :class:`.ColumnDefault`, :class:`.Sequence`, :class:`.Computed`
1702 :class:`.Identity`. In some cases an
1703 equivalent keyword argument is available such as ``server_default``,
1704 ``default`` and ``unique``.
1706 :param autoincrement: Set up "auto increment" semantics for an
1707 **integer primary key column with no foreign key dependencies**
1708 (see later in this docstring for a more specific definition).
1709 This may influence the :term:`DDL` that will be emitted for
1710 this column during a table create, as well as how the column
1711 will be considered when INSERT statements are compiled and
1712 executed.
1714 The default value is the string ``"auto"``,
1715 which indicates that a single-column (i.e. non-composite) primary key
1716 that is of an INTEGER type with no other client-side or server-side
1717 default constructs indicated should receive auto increment semantics
1718 automatically. Other values include ``True`` (force this column to
1719 have auto-increment semantics for a :term:`composite primary key` as
1720 well), ``False`` (this column should never have auto-increment
1721 semantics), and the string ``"ignore_fk"`` (special-case for foreign
1722 key columns, see below).
1724 The term "auto increment semantics" refers both to the kind of DDL
1725 that will be emitted for the column within a CREATE TABLE statement,
1726 when methods such as :meth:`.MetaData.create_all` and
1727 :meth:`.Table.create` are invoked, as well as how the column will be
1728 considered when an INSERT statement is compiled and emitted to the
1729 database:
1731 * **DDL rendering** (i.e. :meth:`.MetaData.create_all`,
1732 :meth:`.Table.create`): When used on a :class:`.Column` that has
1733 no other
1734 default-generating construct associated with it (such as a
1735 :class:`.Sequence` or :class:`.Identity` construct), the parameter
1736 will imply that database-specific keywords such as PostgreSQL
1737 ``SERIAL``, MySQL ``AUTO_INCREMENT``, or ``IDENTITY`` on SQL Server
1738 should also be rendered. Not every database backend has an
1739 "implied" default generator available; for example the Oracle Database
1740 backends alway needs an explicit construct such as
1741 :class:`.Identity` to be included with a :class:`.Column` in order
1742 for the DDL rendered to include auto-generating constructs to also
1743 be produced in the database.
1745 * **INSERT semantics** (i.e. when a :func:`_sql.insert` construct is
1746 compiled into a SQL string and is then executed on a database using
1747 :meth:`_engine.Connection.execute` or equivalent): A single-row
1748 INSERT statement will be known to produce a new integer primary key
1749 value automatically for this column, which will be accessible
1750 after the statement is invoked via the
1751 :attr:`.CursorResult.inserted_primary_key` attribute upon the
1752 :class:`_result.Result` object. This also applies towards use of the
1753 ORM when ORM-mapped objects are persisted to the database,
1754 indicating that a new integer primary key will be available to
1755 become part of the :term:`identity key` for that object. This
1756 behavior takes place regardless of what DDL constructs are
1757 associated with the :class:`_schema.Column` and is independent
1758 of the "DDL Rendering" behavior discussed in the previous note
1759 above.
1761 The parameter may be set to ``True`` to indicate that a column which
1762 is part of a composite (i.e. multi-column) primary key should
1763 have autoincrement semantics, though note that only one column
1764 within a primary key may have this setting. It can also
1765 be set to ``True`` to indicate autoincrement semantics on a
1766 column that has a client-side or server-side default configured,
1767 however note that not all dialects can accommodate all styles
1768 of default as an "autoincrement". It can also be
1769 set to ``False`` on a single-column primary key that has a
1770 datatype of INTEGER in order to disable auto increment semantics
1771 for that column.
1773 The setting *only* has an effect for columns which are:
1775 * Integer derived (i.e. INT, SMALLINT, BIGINT).
1777 * Part of the primary key
1779 * Not referring to another column via :class:`_schema.ForeignKey`,
1780 unless
1781 the value is specified as ``'ignore_fk'``::
1783 # turn on autoincrement for this column despite
1784 # the ForeignKey()
1785 Column(
1786 "id",
1787 ForeignKey("other.id"),
1788 primary_key=True,
1789 autoincrement="ignore_fk",
1790 )
1792 It is typically not desirable to have "autoincrement" enabled on a
1793 column that refers to another via foreign key, as such a column is
1794 required to refer to a value that originates from elsewhere.
1796 The setting has these effects on columns that meet the
1797 above criteria:
1799 * DDL issued for the column, if the column does not already include
1800 a default generating construct supported by the backend such as
1801 :class:`.Identity`, will include database-specific
1802 keywords intended to signify this column as an
1803 "autoincrement" column for specific backends. Behavior for
1804 primary SQLAlchemy dialects includes:
1806 * AUTO INCREMENT on MySQL and MariaDB
1807 * SERIAL on PostgreSQL
1808 * IDENTITY on MS-SQL - this occurs even without the
1809 :class:`.Identity` construct as the
1810 :paramref:`.Column.autoincrement` parameter pre-dates this
1811 construct.
1812 * SQLite - SQLite integer primary key columns are implicitly
1813 "auto incrementing" and no additional keywords are rendered;
1814 to render the special SQLite keyword ``AUTOINCREMENT``
1815 is not included as this is unnecessary and not recommended
1816 by the database vendor. See the section
1817 :ref:`sqlite_autoincrement` for more background.
1818 * Oracle Database - The Oracle Database dialects have no default "autoincrement"
1819 feature available at this time, instead the :class:`.Identity`
1820 construct is recommended to achieve this (the :class:`.Sequence`
1821 construct may also be used).
1822 * Third-party dialects - consult those dialects' documentation
1823 for details on their specific behaviors.
1825 * When a single-row :func:`_sql.insert` construct is compiled and
1826 executed, which does not set the :meth:`_sql.Insert.inline`
1827 modifier, newly generated primary key values for this column
1828 will be automatically retrieved upon statement execution
1829 using a method specific to the database driver in use:
1831 * MySQL, SQLite - calling upon ``cursor.lastrowid()``
1832 (see
1833 `https://www.python.org/dev/peps/pep-0249/#lastrowid
1834 <https://www.python.org/dev/peps/pep-0249/#lastrowid>`_)
1835 * PostgreSQL, SQL Server, Oracle Database - use RETURNING or an equivalent
1836 construct when rendering an INSERT statement, and then retrieving
1837 the newly generated primary key values after execution
1838 * PostgreSQL, Oracle Database for :class:`_schema.Table` objects that
1839 set :paramref:`_schema.Table.implicit_returning` to False -
1840 for a :class:`.Sequence` only, the :class:`.Sequence` is invoked
1841 explicitly before the INSERT statement takes place so that the
1842 newly generated primary key value is available to the client
1843 * SQL Server for :class:`_schema.Table` objects that
1844 set :paramref:`_schema.Table.implicit_returning` to False -
1845 the ``SELECT scope_identity()`` construct is used after the
1846 INSERT statement is invoked to retrieve the newly generated
1847 primary key value.
1848 * Third-party dialects - consult those dialects' documentation
1849 for details on their specific behaviors.
1851 * For multiple-row :func:`_sql.insert` constructs invoked with
1852 a list of parameters (i.e. "executemany" semantics), primary-key
1853 retrieving behaviors are generally disabled, however there may
1854 be special APIs that may be used to retrieve lists of new
1855 primary key values for an "executemany", such as the psycopg2
1856 "fast insertmany" feature. Such features are very new and
1857 may not yet be well covered in documentation.
1859 :param default: A scalar, Python callable, or
1860 :class:`_expression.ColumnElement` expression representing the
1861 *default value* for this column, which will be invoked upon insert
1862 if this column is otherwise not specified in the VALUES clause of
1863 the insert. This is a shortcut to using :class:`.ColumnDefault` as
1864 a positional argument; see that class for full detail on the
1865 structure of the argument.
1867 Contrast this argument to
1868 :paramref:`_schema.Column.server_default`
1869 which creates a default generator on the database side.
1871 .. seealso::
1873 :ref:`metadata_defaults_toplevel`
1875 :param insert_default: An alias of :paramref:`.Column.default`
1876 for compatibility with :func:`_orm.mapped_column`.
1878 .. versionadded:: 2.0.31
1880 :param doc: optional String that can be used by the ORM or similar
1881 to document attributes on the Python side. This attribute does
1882 **not** render SQL comments; use the
1883 :paramref:`_schema.Column.comment`
1884 parameter for this purpose.
1886 :param key: An optional string identifier which will identify this
1887 ``Column`` object on the :class:`_schema.Table`.
1888 When a key is provided,
1889 this is the only identifier referencing the ``Column`` within the
1890 application, including ORM attribute mapping; the ``name`` field
1891 is used only when rendering SQL.
1893 :param index: When ``True``, indicates that a :class:`_schema.Index`
1894 construct will be automatically generated for this
1895 :class:`_schema.Column`, which will result in a "CREATE INDEX"
1896 statement being emitted for the :class:`_schema.Table` when the DDL
1897 create operation is invoked.
1899 Using this flag is equivalent to making use of the
1900 :class:`_schema.Index` construct explicitly at the level of the
1901 :class:`_schema.Table` construct itself::
1903 Table(
1904 "some_table",
1905 metadata,
1906 Column("x", Integer),
1907 Index("ix_some_table_x", "x"),
1908 )
1910 To add the :paramref:`_schema.Index.unique` flag to the
1911 :class:`_schema.Index`, set both the
1912 :paramref:`_schema.Column.unique` and
1913 :paramref:`_schema.Column.index` flags to True simultaneously,
1914 which will have the effect of rendering the "CREATE UNIQUE INDEX"
1915 DDL instruction instead of "CREATE INDEX".
1917 The name of the index is generated using the
1918 :ref:`default naming convention <constraint_default_naming_convention>`
1919 which for the :class:`_schema.Index` construct is of the form
1920 ``ix_<tablename>_<columnname>``.
1922 As this flag is intended only as a convenience for the common case
1923 of adding a single-column, default configured index to a table
1924 definition, explicit use of the :class:`_schema.Index` construct
1925 should be preferred for most use cases, including composite indexes
1926 that encompass more than one column, indexes with SQL expressions
1927 or ordering, backend-specific index configuration options, and
1928 indexes that use a specific name.
1930 .. note:: the :attr:`_schema.Column.index` attribute on
1931 :class:`_schema.Column`
1932 **does not indicate** if this column is indexed or not, only
1933 if this flag was explicitly set here. To view indexes on
1934 a column, view the :attr:`_schema.Table.indexes` collection
1935 or use :meth:`_reflection.Inspector.get_indexes`.
1937 .. seealso::
1939 :ref:`schema_indexes`
1941 :ref:`constraint_naming_conventions`
1943 :paramref:`_schema.Column.unique`
1945 :param info: Optional data dictionary which will be populated into the
1946 :attr:`.SchemaItem.info` attribute of this object.
1948 :param nullable: When set to ``False``, will cause the "NOT NULL"
1949 phrase to be added when generating DDL for the column. When
1950 ``True``, will normally generate nothing (in SQL this defaults to
1951 "NULL"), except in some very specific backend-specific edge cases
1952 where "NULL" may render explicitly.
1953 Defaults to ``True`` unless :paramref:`_schema.Column.primary_key`
1954 is also ``True`` or the column specifies a :class:`_sql.Identity`,
1955 in which case it defaults to ``False``.
1956 This parameter is only used when issuing CREATE TABLE statements.
1958 .. note::
1960 When the column specifies a :class:`_sql.Identity` this
1961 parameter is in general ignored by the DDL compiler. The
1962 PostgreSQL database allows nullable identity column by
1963 setting this parameter to ``True`` explicitly.
1965 :param onupdate: A scalar, Python callable, or
1966 :class:`~sqlalchemy.sql.expression.ClauseElement` representing a
1967 default value to be applied to the column within UPDATE
1968 statements, which will be invoked upon update if this column is not
1969 present in the SET clause of the update. This is a shortcut to
1970 using :class:`.ColumnDefault` as a positional argument with
1971 ``for_update=True``.
1973 .. seealso::
1975 :ref:`metadata_defaults` - complete discussion of onupdate
1977 :param primary_key: If ``True``, marks this column as a primary key
1978 column. Multiple columns can have this flag set to specify
1979 composite primary keys. As an alternative, the primary key of a
1980 :class:`_schema.Table` can be specified via an explicit
1981 :class:`.PrimaryKeyConstraint` object.
1983 :param server_default: A :class:`.FetchedValue` instance, str, Unicode
1984 or :func:`~sqlalchemy.sql.expression.text` construct representing
1985 the DDL DEFAULT value for the column.
1987 String types will be emitted as-is, surrounded by single quotes::
1989 Column("x", Text, server_default="val")
1991 will render:
1993 .. sourcecode:: sql
1995 x TEXT DEFAULT 'val'
1997 A :func:`~sqlalchemy.sql.expression.text` expression will be
1998 rendered as-is, without quotes::
2000 Column("y", DateTime, server_default=text("NOW()"))
2002 will render:
2004 .. sourcecode:: sql
2006 y DATETIME DEFAULT NOW()
2008 Strings and text() will be converted into a
2009 :class:`.DefaultClause` object upon initialization.
2011 This parameter can also accept complex combinations of contextually
2012 valid SQLAlchemy expressions or constructs::
2014 from sqlalchemy import create_engine
2015 from sqlalchemy import Table, Column, MetaData, ARRAY, Text
2016 from sqlalchemy.dialects.postgresql import array
2018 engine = create_engine(
2019 "postgresql+psycopg2://scott:tiger@localhost/mydatabase"
2020 )
2021 metadata_obj = MetaData()
2022 tbl = Table(
2023 "foo",
2024 metadata_obj,
2025 Column(
2026 "bar", ARRAY(Text), server_default=array(["biz", "bang", "bash"])
2027 ),
2028 )
2029 metadata_obj.create_all(engine)
2031 The above results in a table created with the following SQL:
2033 .. sourcecode:: sql
2035 CREATE TABLE foo (
2036 bar TEXT[] DEFAULT ARRAY['biz', 'bang', 'bash']
2037 )
2039 Use :class:`.FetchedValue` to indicate that an already-existing
2040 column will generate a default value on the database side which
2041 will be available to SQLAlchemy for post-fetch after inserts. This
2042 construct does not specify any DDL and the implementation is left
2043 to the database, such as via a trigger.
2045 .. seealso::
2047 :ref:`server_defaults` - complete discussion of server side
2048 defaults
2050 :param server_onupdate: A :class:`.FetchedValue` instance
2051 representing a database-side default generation function,
2052 such as a trigger. This
2053 indicates to SQLAlchemy that a newly generated value will be
2054 available after updates. This construct does not actually
2055 implement any kind of generation function within the database,
2056 which instead must be specified separately.
2059 .. warning:: This directive **does not** currently produce MySQL's
2060 "ON UPDATE CURRENT_TIMESTAMP()" clause. See
2061 :ref:`mysql_timestamp_onupdate` for background on how to
2062 produce this clause.
2064 .. seealso::
2066 :ref:`triggered_columns`
2068 :param quote: Force quoting of this column's name on or off,
2069 corresponding to ``True`` or ``False``. When left at its default
2070 of ``None``, the column identifier will be quoted according to
2071 whether the name is case sensitive (identifiers with at least one
2072 upper case character are treated as case sensitive), or if it's a
2073 reserved word. This flag is only needed to force quoting of a
2074 reserved word which is not known by the SQLAlchemy dialect.
2076 :param unique: When ``True``, and the :paramref:`_schema.Column.index`
2077 parameter is left at its default value of ``False``,
2078 indicates that a :class:`_schema.UniqueConstraint`
2079 construct will be automatically generated for this
2080 :class:`_schema.Column`,
2081 which will result in a "UNIQUE CONSTRAINT" clause referring
2082 to this column being included
2083 in the ``CREATE TABLE`` statement emitted, when the DDL create
2084 operation for the :class:`_schema.Table` object is invoked.
2086 When this flag is ``True`` while the
2087 :paramref:`_schema.Column.index` parameter is simultaneously
2088 set to ``True``, the effect instead is that a
2089 :class:`_schema.Index` construct which includes the
2090 :paramref:`_schema.Index.unique` parameter set to ``True``
2091 is generated. See the documentation for
2092 :paramref:`_schema.Column.index` for additional detail.
2094 Using this flag is equivalent to making use of the
2095 :class:`_schema.UniqueConstraint` construct explicitly at the
2096 level of the :class:`_schema.Table` construct itself::
2098 Table("some_table", metadata, Column("x", Integer), UniqueConstraint("x"))
2100 The :paramref:`_schema.UniqueConstraint.name` parameter
2101 of the unique constraint object is left at its default value
2102 of ``None``; in the absence of a :ref:`naming convention <constraint_naming_conventions>`
2103 for the enclosing :class:`_schema.MetaData`, the UNIQUE CONSTRAINT
2104 construct will be emitted as unnamed, which typically invokes
2105 a database-specific naming convention to take place.
2107 As this flag is intended only as a convenience for the common case
2108 of adding a single-column, default configured unique constraint to a table
2109 definition, explicit use of the :class:`_schema.UniqueConstraint` construct
2110 should be preferred for most use cases, including composite constraints
2111 that encompass more than one column, backend-specific index configuration options, and
2112 constraints that use a specific name.
2114 .. note:: the :attr:`_schema.Column.unique` attribute on
2115 :class:`_schema.Column`
2116 **does not indicate** if this column has a unique constraint or
2117 not, only if this flag was explicitly set here. To view
2118 indexes and unique constraints that may involve this column,
2119 view the
2120 :attr:`_schema.Table.indexes` and/or
2121 :attr:`_schema.Table.constraints` collections or use
2122 :meth:`_reflection.Inspector.get_indexes` and/or
2123 :meth:`_reflection.Inspector.get_unique_constraints`
2125 .. seealso::
2127 :ref:`schema_unique_constraint`
2129 :ref:`constraint_naming_conventions`
2131 :paramref:`_schema.Column.index`
2133 :param system: When ``True``, indicates this is a "system" column,
2134 that is a column which is automatically made available by the
2135 database, and should not be included in the columns list for a
2136 ``CREATE TABLE`` statement.
2138 For more elaborate scenarios where columns should be
2139 conditionally rendered differently on different backends,
2140 consider custom compilation rules for :class:`.CreateColumn`.
2142 :param comment: Optional string that will render an SQL comment on
2143 table creation.
2145 :param insert_sentinel: Marks this :class:`_schema.Column` as an
2146 :term:`insert sentinel` used for optimizing the performance of the
2147 :term:`insertmanyvalues` feature for tables that don't
2148 otherwise have qualifying primary key configurations.
2150 .. versionadded:: 2.0.10
2152 .. seealso::
2154 :func:`_schema.insert_sentinel` - all in one helper for declaring
2155 sentinel columns
2157 :ref:`engine_insertmanyvalues`
2159 :ref:`engine_insertmanyvalues_sentinel_columns`
2162 """ # noqa: E501, RST201, RST202
2164 l_args = [__name_pos, __type_pos] + list(args)
2165 del args
2167 if l_args:
2168 if isinstance(l_args[0], str):
2169 if name is not None:
2170 raise exc.ArgumentError(
2171 "May not pass name positionally and as a keyword."
2172 )
2173 name = l_args.pop(0) # type: ignore
2174 elif l_args[0] is None:
2175 l_args.pop(0)
2176 if l_args:
2177 coltype = l_args[0]
2179 if hasattr(coltype, "_sqla_type"):
2180 if type_ is not None:
2181 raise exc.ArgumentError(
2182 "May not pass type_ positionally and as a keyword."
2183 )
2184 type_ = l_args.pop(0) # type: ignore
2185 elif l_args[0] is None:
2186 l_args.pop(0)
2188 if name is not None:
2189 name = quoted_name(name, quote)
2190 elif quote is not None:
2191 raise exc.ArgumentError(
2192 "Explicit 'name' is required when sending 'quote' argument"
2193 )
2195 # name = None is expected to be an interim state
2196 # note this use case is legacy now that ORM declarative has a
2197 # dedicated "column" construct local to the ORM
2198 super().__init__(name, type_) # type: ignore
2200 self.key = key if key is not None else name # type: ignore
2201 self.primary_key = primary_key
2202 self._insert_sentinel = insert_sentinel
2203 self._omit_from_statements = _omit_from_statements
2204 self._user_defined_nullable = udn = nullable
2205 if udn is not NULL_UNSPECIFIED:
2206 self.nullable = udn
2207 else:
2208 self.nullable = not primary_key
2210 # these default to None because .index and .unique is *not*
2211 # an informational flag about Column - there can still be an
2212 # Index or UniqueConstraint referring to this Column.
2213 self.index = index
2214 self.unique = unique
2216 self.system = system
2217 self.doc = doc
2218 self.autoincrement: _AutoIncrementType = autoincrement
2219 self.constraints = set()
2220 self.foreign_keys = set()
2221 self.comment = comment
2222 self.computed = None
2223 self.identity = None
2225 # check if this Column is proxying another column
2227 if _proxies is not None:
2228 self._proxies = _proxies
2229 else:
2230 # otherwise, add DDL-related events
2231 self._set_type(self.type)
2233 if insert_default is not _NoArg.NO_ARG:
2234 if default is not _NoArg.NO_ARG:
2235 raise exc.ArgumentError(
2236 "The 'default' and 'insert_default' parameters "
2237 "of Column are mutually exclusive"
2238 )
2239 resolved_default = insert_default
2240 elif default is not _NoArg.NO_ARG:
2241 resolved_default = default
2242 else:
2243 resolved_default = None
2245 if resolved_default is not None:
2246 if not isinstance(resolved_default, (ColumnDefault, Sequence)):
2247 resolved_default = ColumnDefault(resolved_default)
2249 self.default = resolved_default
2250 l_args.append(resolved_default)
2251 else:
2252 self.default = None
2254 if onupdate is not None:
2255 if not isinstance(onupdate, (ColumnDefault, Sequence)):
2256 onupdate = ColumnDefault(onupdate, for_update=True)
2258 self.onupdate = onupdate
2259 l_args.append(onupdate)
2260 else:
2261 self.onupdate = None
2263 if server_default is not None:
2264 if isinstance(server_default, FetchedValue):
2265 server_default = server_default._as_for_update(False)
2266 l_args.append(server_default)
2267 else:
2268 server_default = DefaultClause(server_default)
2269 l_args.append(server_default)
2270 self.server_default = server_default
2272 if server_onupdate is not None:
2273 if isinstance(server_onupdate, FetchedValue):
2274 server_onupdate = server_onupdate._as_for_update(True)
2275 l_args.append(server_onupdate)
2276 else:
2277 server_onupdate = DefaultClause(
2278 server_onupdate, for_update=True
2279 )
2280 l_args.append(server_onupdate)
2281 self.server_onupdate = server_onupdate
2283 self._init_items(*cast(_typing_Sequence[SchemaItem], l_args))
2285 util.set_creation_order(self)
2287 if info is not None:
2288 self.info = info
2290 self._extra_kwargs(**dialect_kwargs)
2292 table: Table
2294 constraints: Set[Constraint]
2296 foreign_keys: Set[ForeignKey]
2297 """A collection of all :class:`_schema.ForeignKey` marker objects
2298 associated with this :class:`_schema.Column`.
2300 Each object is a member of a :class:`_schema.Table`-wide
2301 :class:`_schema.ForeignKeyConstraint`.
2303 .. seealso::
2305 :attr:`_schema.Table.foreign_keys`
2307 """
2309 index: Optional[bool]
2310 """The value of the :paramref:`_schema.Column.index` parameter.
2312 Does not indicate if this :class:`_schema.Column` is actually indexed
2313 or not; use :attr:`_schema.Table.indexes`.
2315 .. seealso::
2317 :attr:`_schema.Table.indexes`
2318 """
2320 unique: Optional[bool]
2321 """The value of the :paramref:`_schema.Column.unique` parameter.
2323 Does not indicate if this :class:`_schema.Column` is actually subject to
2324 a unique constraint or not; use :attr:`_schema.Table.indexes` and
2325 :attr:`_schema.Table.constraints`.
2327 .. seealso::
2329 :attr:`_schema.Table.indexes`
2331 :attr:`_schema.Table.constraints`.
2333 """
2335 computed: Optional[Computed]
2337 identity: Optional[Identity]
2339 def _set_type(self, type_: TypeEngine[Any]) -> None:
2340 assert self.type._isnull or type_ is self.type
2342 self.type = type_
2343 if isinstance(self.type, SchemaEventTarget):
2344 self.type._set_parent_with_dispatch(self)
2345 for impl in self.type._variant_mapping.values():
2346 if isinstance(impl, SchemaEventTarget):
2347 impl._set_parent_with_dispatch(self)
2349 @HasMemoized.memoized_attribute
2350 def _default_description_tuple(self) -> _DefaultDescriptionTuple:
2351 """used by default.py -> _process_execute_defaults()"""
2353 return _DefaultDescriptionTuple._from_column_default(self.default)
2355 @HasMemoized.memoized_attribute
2356 def _onupdate_description_tuple(self) -> _DefaultDescriptionTuple:
2357 """used by default.py -> _process_execute_defaults()"""
2358 return _DefaultDescriptionTuple._from_column_default(self.onupdate)
2360 @util.memoized_property
2361 def _gen_static_annotations_cache_key(self) -> bool:
2362 """special attribute used by cache key gen, if true, we will
2363 use a static cache key for the annotations dictionary, else we
2364 will generate a new cache key for annotations each time.
2366 Added for #8790
2368 """
2369 return self.table is not None and self.table._is_table
2371 def _extra_kwargs(self, **kwargs: Any) -> None:
2372 self._validate_dialect_kwargs(kwargs)
2374 def __str__(self) -> str:
2375 if self.name is None:
2376 return "(no name)"
2377 elif self.table is not None:
2378 if self.table.named_with_column:
2379 return self.table.description + "." + self.description
2380 else:
2381 return self.description
2382 else:
2383 return self.description
2385 def references(self, column: Column[Any]) -> bool:
2386 """Return True if this Column references the given column via foreign
2387 key."""
2389 for fk in self.foreign_keys:
2390 if fk.column.proxy_set.intersection(column.proxy_set):
2391 return True
2392 else:
2393 return False
2395 def append_foreign_key(self, fk: ForeignKey) -> None:
2396 fk._set_parent_with_dispatch(self)
2398 def __repr__(self) -> str:
2399 kwarg = []
2400 if self.key != self.name:
2401 kwarg.append("key")
2402 if self.primary_key:
2403 kwarg.append("primary_key")
2404 if not self.nullable:
2405 kwarg.append("nullable")
2406 if self.onupdate:
2407 kwarg.append("onupdate")
2408 if self.default:
2409 kwarg.append("default")
2410 if self.server_default:
2411 kwarg.append("server_default")
2412 if self.comment:
2413 kwarg.append("comment")
2414 return "Column(%s)" % ", ".join(
2415 [repr(self.name)]
2416 + [repr(self.type)]
2417 + [repr(x) for x in self.foreign_keys if x is not None]
2418 + [repr(x) for x in self.constraints]
2419 + [
2420 (
2421 self.table is not None
2422 and "table=<%s>" % self.table.description
2423 or "table=None"
2424 )
2425 ]
2426 + ["%s=%s" % (k, repr(getattr(self, k))) for k in kwarg]
2427 )
2429 def _set_parent( # type: ignore[override]
2430 self,
2431 parent: SchemaEventTarget,
2432 *,
2433 all_names: Dict[str, Column[Any]],
2434 allow_replacements: bool,
2435 index: Optional[int] = None,
2436 **kw: Any,
2437 ) -> None:
2438 table = parent
2439 assert isinstance(table, Table)
2440 if not self.name:
2441 raise exc.ArgumentError(
2442 "Column must be constructed with a non-blank name or "
2443 "assign a non-blank .name before adding to a Table."
2444 )
2446 self._reset_memoizations()
2448 if self.key is None:
2449 self.key = self.name
2451 existing = getattr(self, "table", None)
2452 if existing is not None and existing is not table:
2453 raise exc.ArgumentError(
2454 f"Column object '{self.key}' already "
2455 f"assigned to Table '{existing.description}'"
2456 )
2458 extra_remove = None
2459 existing_col = None
2460 conflicts_on = ""
2462 if self.key in table._columns:
2463 existing_col = table._columns[self.key]
2464 if self.key == self.name:
2465 conflicts_on = "name"
2466 else:
2467 conflicts_on = "key"
2468 elif self.name in all_names:
2469 existing_col = all_names[self.name]
2470 extra_remove = {existing_col}
2471 conflicts_on = "name"
2473 if existing_col is not None:
2474 if existing_col is not self:
2475 if not allow_replacements:
2476 raise exc.DuplicateColumnError(
2477 f"A column with {conflicts_on} "
2478 f"""'{
2479 self.key if conflicts_on == 'key' else self.name
2480 }' """
2481 f"is already present in table '{table.name}'."
2482 )
2483 for fk in existing_col.foreign_keys:
2484 table.foreign_keys.remove(fk)
2485 if fk.constraint in table.constraints:
2486 # this might have been removed
2487 # already, if it's a composite constraint
2488 # and more than one col being replaced
2489 table.constraints.remove(fk.constraint)
2491 if extra_remove and existing_col is not None and self.key == self.name:
2492 util.warn(
2493 f'Column with user-specified key "{existing_col.key}" is '
2494 "being replaced with "
2495 f'plain named column "{self.name}", '
2496 f'key "{existing_col.key}" is being removed. If this is a '
2497 "reflection operation, specify autoload_replace=False to "
2498 "prevent this replacement."
2499 )
2500 table._columns.replace(self, extra_remove=extra_remove, index=index)
2501 all_names[self.name] = self
2502 self.table = table
2504 if self._insert_sentinel:
2505 if self.table._sentinel_column is not None:
2506 raise exc.ArgumentError(
2507 "a Table may have only one explicit sentinel column"
2508 )
2509 self.table._sentinel_column = self
2511 if self.primary_key:
2512 table.primary_key._replace(self)
2513 elif self.key in table.primary_key:
2514 raise exc.ArgumentError(
2515 f"Trying to redefine primary-key column '{self.key}' as a "
2516 f"non-primary-key column on table '{table.fullname}'"
2517 )
2519 if self.index:
2520 if isinstance(self.index, str):
2521 raise exc.ArgumentError(
2522 "The 'index' keyword argument on Column is boolean only. "
2523 "To create indexes with a specific name, create an "
2524 "explicit Index object external to the Table."
2525 )
2526 table.append_constraint(
2527 Index(
2528 None, self.key, unique=bool(self.unique), _column_flag=True
2529 )
2530 )
2532 elif self.unique:
2533 if isinstance(self.unique, str):
2534 raise exc.ArgumentError(
2535 "The 'unique' keyword argument on Column is boolean "
2536 "only. To create unique constraints or indexes with a "
2537 "specific name, append an explicit UniqueConstraint to "
2538 "the Table's list of elements, or create an explicit "
2539 "Index object external to the Table."
2540 )
2541 table.append_constraint(
2542 UniqueConstraint(self.key, _column_flag=True)
2543 )
2545 self._setup_on_memoized_fks(lambda fk: fk._set_remote_table(table))
2547 if self.identity and (
2548 isinstance(self.default, Sequence)
2549 or isinstance(self.onupdate, Sequence)
2550 ):
2551 raise exc.ArgumentError(
2552 "An column cannot specify both Identity and Sequence."
2553 )
2555 def _setup_on_memoized_fks(self, fn: Callable[..., Any]) -> None:
2556 fk_keys = [
2557 ((self.table.key, self.key), False),
2558 ((self.table.key, self.name), True),
2559 ]
2560 for fk_key, link_to_name in fk_keys:
2561 if fk_key in self.table.metadata._fk_memos:
2562 for fk in self.table.metadata._fk_memos[fk_key]:
2563 if fk.link_to_name is link_to_name:
2564 fn(fk)
2566 def _on_table_attach(self, fn: Callable[..., Any]) -> None:
2567 if self.table is not None:
2568 fn(self, self.table)
2569 else:
2570 event.listen(self, "after_parent_attach", fn)
2572 @util.deprecated(
2573 "1.4",
2574 "The :meth:`_schema.Column.copy` method is deprecated "
2575 "and will be removed in a future release.",
2576 )
2577 def copy(self, **kw: Any) -> Column[Any]:
2578 return self._copy(**kw)
2580 def _copy(self, **kw: Any) -> Column[Any]:
2581 """Create a copy of this ``Column``, uninitialized.
2583 This is used in :meth:`_schema.Table.to_metadata` and by the ORM.
2585 """
2587 # Constraint objects plus non-constraint-bound ForeignKey objects
2588 args: List[SchemaItem] = [
2589 c._copy(**kw) for c in self.constraints if not c._type_bound
2590 ] + [c._copy(**kw) for c in self.foreign_keys if not c.constraint]
2592 # ticket #5276
2593 column_kwargs = {}
2594 for dialect_name in self.dialect_options:
2595 dialect_options = self.dialect_options[dialect_name]._non_defaults
2596 for (
2597 dialect_option_key,
2598 dialect_option_value,
2599 ) in dialect_options.items():
2600 column_kwargs[dialect_name + "_" + dialect_option_key] = (
2601 dialect_option_value
2602 )
2604 server_default = self.server_default
2605 server_onupdate = self.server_onupdate
2606 if isinstance(server_default, (Computed, Identity)):
2607 # TODO: likely should be copied in all cases
2608 # TODO: if a Sequence, we would need to transfer the Sequence
2609 # .metadata as well
2610 args.append(server_default._copy(**kw))
2611 server_default = server_onupdate = None
2613 type_ = self.type
2614 if isinstance(type_, SchemaEventTarget):
2615 type_ = type_.copy(**kw)
2617 # TODO: DefaultGenerator is not copied here! it's just used again
2618 # with _set_parent() pointing to the old column. see the new
2619 # use of _copy() in the new _merge() method
2621 c = self._constructor(
2622 name=self.name,
2623 type_=type_,
2624 key=self.key,
2625 primary_key=self.primary_key,
2626 unique=self.unique,
2627 system=self.system,
2628 # quote=self.quote, # disabled 2013-08-27 (commit 031ef080)
2629 index=self.index,
2630 autoincrement=self.autoincrement,
2631 default=self.default,
2632 server_default=server_default,
2633 onupdate=self.onupdate,
2634 server_onupdate=server_onupdate,
2635 doc=self.doc,
2636 comment=self.comment,
2637 _omit_from_statements=self._omit_from_statements,
2638 insert_sentinel=self._insert_sentinel,
2639 *args,
2640 **column_kwargs,
2641 )
2643 # copy the state of "nullable" exactly, to accommodate for
2644 # ORM flipping the .nullable flag directly
2645 c.nullable = self.nullable
2646 c._user_defined_nullable = self._user_defined_nullable
2648 return self._schema_item_copy(c)
2650 def _merge(
2651 self, other: Column[Any], *, omit_defaults: bool = False
2652 ) -> None:
2653 """merge the elements of this column onto "other"
2655 this is used by ORM pep-593 merge and will likely need a lot
2656 of fixes.
2659 """
2661 if self.primary_key:
2662 other.primary_key = True
2664 if self.autoincrement != "auto" and other.autoincrement == "auto":
2665 other.autoincrement = self.autoincrement
2667 if self.system:
2668 other.system = self.system
2670 if self.info:
2671 other.info.update(self.info)
2673 type_ = self.type
2674 if not type_._isnull and other.type._isnull:
2675 if isinstance(type_, SchemaEventTarget):
2676 type_ = type_.copy()
2678 other.type = type_
2680 if isinstance(type_, SchemaEventTarget):
2681 type_._set_parent_with_dispatch(other)
2683 for impl in type_._variant_mapping.values():
2684 if isinstance(impl, SchemaEventTarget):
2685 impl._set_parent_with_dispatch(other)
2687 if (
2688 self._user_defined_nullable is not NULL_UNSPECIFIED
2689 and other._user_defined_nullable is NULL_UNSPECIFIED
2690 ):
2691 other.nullable = self.nullable
2692 other._user_defined_nullable = self._user_defined_nullable
2694 if (
2695 not omit_defaults
2696 and self.default is not None
2697 and other.default is None
2698 ):
2699 new_default = self.default._copy()
2700 new_default._set_parent(other)
2702 if self.server_default and other.server_default is None:
2703 new_server_default = self.server_default
2704 if isinstance(new_server_default, FetchedValue):
2705 new_server_default = new_server_default._copy()
2706 new_server_default._set_parent(other)
2707 else:
2708 other.server_default = new_server_default
2710 if self.server_onupdate and other.server_onupdate is None:
2711 new_server_onupdate = self.server_onupdate
2712 new_server_onupdate = new_server_onupdate._copy()
2713 new_server_onupdate._set_parent(other)
2715 if self.onupdate and other.onupdate is None:
2716 new_onupdate = self.onupdate._copy()
2717 new_onupdate._set_parent(other)
2719 if self.index in (True, False) and other.index is None:
2720 other.index = self.index
2722 if self.unique in (True, False) and other.unique is None:
2723 other.unique = self.unique
2725 if self.doc and other.doc is None:
2726 other.doc = self.doc
2728 if self.comment and other.comment is None:
2729 other.comment = self.comment
2731 for const in self.constraints:
2732 if not const._type_bound:
2733 new_const = const._copy()
2734 new_const._set_parent(other)
2736 for fk in self.foreign_keys:
2737 if not fk.constraint:
2738 new_fk = fk._copy()
2739 new_fk._set_parent(other)
2741 def _make_proxy(
2742 self,
2743 selectable: FromClause,
2744 primary_key: ColumnSet,
2745 foreign_keys: Set[KeyedColumnElement[Any]],
2746 name: Optional[str] = None,
2747 key: Optional[str] = None,
2748 name_is_truncatable: bool = False,
2749 compound_select_cols: Optional[
2750 _typing_Sequence[ColumnElement[Any]]
2751 ] = None,
2752 **kw: Any,
2753 ) -> Tuple[str, ColumnClause[_T]]:
2754 """Create a *proxy* for this column.
2756 This is a copy of this ``Column`` referenced by a different parent
2757 (such as an alias or select statement). The column should
2758 be used only in select scenarios, as its full DDL/default
2759 information is not transferred.
2761 """
2763 fk = [
2764 ForeignKey(
2765 col if col is not None else f._colspec,
2766 _unresolvable=col is None,
2767 _constraint=f.constraint,
2768 )
2769 for f, col in [
2770 (fk, fk._resolve_column(raiseerr=False))
2771 for fk in self.foreign_keys
2772 ]
2773 ]
2775 if name is None and self.name is None:
2776 raise exc.InvalidRequestError(
2777 "Cannot initialize a sub-selectable"
2778 " with this Column object until its 'name' has "
2779 "been assigned."
2780 )
2781 try:
2782 c = self._constructor(
2783 (
2784 coercions.expect(
2785 roles.TruncatedLabelRole, name if name else self.name
2786 )
2787 if name_is_truncatable
2788 else (name or self.name)
2789 ),
2790 self.type,
2791 # this may actually be ._proxy_key when the key is incoming
2792 key=key if key else name if name else self.key,
2793 primary_key=self.primary_key,
2794 nullable=self.nullable,
2795 _proxies=(
2796 list(compound_select_cols)
2797 if compound_select_cols
2798 else [self]
2799 ),
2800 *fk,
2801 )
2802 except TypeError as err:
2803 raise TypeError(
2804 "Could not create a copy of this %r object. "
2805 "Ensure the class includes a _constructor() "
2806 "attribute or method which accepts the "
2807 "standard Column constructor arguments, or "
2808 "references the Column class itself." % self.__class__
2809 ) from err
2811 c.table = selectable
2812 c._propagate_attrs = selectable._propagate_attrs
2813 if selectable._is_clone_of is not None:
2814 c._is_clone_of = selectable._is_clone_of.columns.get(c.key)
2816 if self.primary_key:
2817 primary_key.add(c)
2819 if fk:
2820 foreign_keys.update(fk) # type: ignore
2822 return c.key, c
2825def insert_sentinel(
2826 name: Optional[str] = None,
2827 type_: Optional[_TypeEngineArgument[_T]] = None,
2828 *,
2829 default: Optional[Any] = None,
2830 omit_from_statements: bool = True,
2831) -> Column[Any]:
2832 """Provides a surrogate :class:`_schema.Column` that will act as a
2833 dedicated insert :term:`sentinel` column, allowing efficient bulk
2834 inserts with deterministic RETURNING sorting for tables that
2835 don't otherwise have qualifying primary key configurations.
2837 Adding this column to a :class:`.Table` object requires that a
2838 corresponding database table actually has this column present, so if adding
2839 it to an existing model, existing database tables would need to be migrated
2840 (e.g. using ALTER TABLE or similar) to include this column.
2842 For background on how this object is used, see the section
2843 :ref:`engine_insertmanyvalues_sentinel_columns` as part of the
2844 section :ref:`engine_insertmanyvalues`.
2846 The :class:`_schema.Column` returned will be a nullable integer column by
2847 default and make use of a sentinel-specific default generator used only in
2848 "insertmanyvalues" operations.
2850 .. seealso::
2852 :func:`_orm.orm_insert_sentinel`
2854 :paramref:`_schema.Column.insert_sentinel`
2856 :ref:`engine_insertmanyvalues`
2858 :ref:`engine_insertmanyvalues_sentinel_columns`
2861 .. versionadded:: 2.0.10
2863 """
2864 return Column(
2865 name=name,
2866 type_=type_api.INTEGERTYPE if type_ is None else type_,
2867 default=(
2868 default if default is not None else _InsertSentinelColumnDefault()
2869 ),
2870 _omit_from_statements=omit_from_statements,
2871 insert_sentinel=True,
2872 )
2875class ForeignKey(DialectKWArgs, SchemaItem):
2876 """Defines a dependency between two columns.
2878 ``ForeignKey`` is specified as an argument to a :class:`_schema.Column`
2879 object,
2880 e.g.::
2882 t = Table(
2883 "remote_table",
2884 metadata,
2885 Column("remote_id", ForeignKey("main_table.id")),
2886 )
2888 Note that ``ForeignKey`` is only a marker object that defines
2889 a dependency between two columns. The actual constraint
2890 is in all cases represented by the :class:`_schema.ForeignKeyConstraint`
2891 object. This object will be generated automatically when
2892 a ``ForeignKey`` is associated with a :class:`_schema.Column` which
2893 in turn is associated with a :class:`_schema.Table`. Conversely,
2894 when :class:`_schema.ForeignKeyConstraint` is applied to a
2895 :class:`_schema.Table`,
2896 ``ForeignKey`` markers are automatically generated to be
2897 present on each associated :class:`_schema.Column`, which are also
2898 associated with the constraint object.
2900 Note that you cannot define a "composite" foreign key constraint,
2901 that is a constraint between a grouping of multiple parent/child
2902 columns, using ``ForeignKey`` objects. To define this grouping,
2903 the :class:`_schema.ForeignKeyConstraint` object must be used, and applied
2904 to the :class:`_schema.Table`. The associated ``ForeignKey`` objects
2905 are created automatically.
2907 The ``ForeignKey`` objects associated with an individual
2908 :class:`_schema.Column`
2909 object are available in the `foreign_keys` collection
2910 of that column.
2912 Further examples of foreign key configuration are in
2913 :ref:`metadata_foreignkeys`.
2915 """
2917 __visit_name__ = "foreign_key"
2919 parent: Column[Any]
2921 _table_column: Optional[Column[Any]]
2923 _colspec: Union[str, Column[Any]]
2925 def __init__(
2926 self,
2927 column: _DDLColumnReferenceArgument,
2928 _constraint: Optional[ForeignKeyConstraint] = None,
2929 use_alter: bool = False,
2930 name: _ConstraintNameArgument = None,
2931 onupdate: Optional[str] = None,
2932 ondelete: Optional[str] = None,
2933 deferrable: Optional[bool] = None,
2934 initially: Optional[str] = None,
2935 link_to_name: bool = False,
2936 match: Optional[str] = None,
2937 info: Optional[_InfoType] = None,
2938 comment: Optional[str] = None,
2939 _unresolvable: bool = False,
2940 **dialect_kw: Any,
2941 ):
2942 r"""
2943 Construct a column-level FOREIGN KEY.
2945 The :class:`_schema.ForeignKey` object when constructed generates a
2946 :class:`_schema.ForeignKeyConstraint`
2947 which is associated with the parent
2948 :class:`_schema.Table` object's collection of constraints.
2950 :param column: A single target column for the key relationship. A
2951 :class:`_schema.Column` object or a column name as a string:
2952 ``tablename.columnkey`` or ``schema.tablename.columnkey``.
2953 ``columnkey`` is the ``key`` which has been assigned to the column
2954 (defaults to the column name itself), unless ``link_to_name`` is
2955 ``True`` in which case the rendered name of the column is used.
2957 :param name: Optional string. An in-database name for the key if
2958 `constraint` is not provided.
2960 :param onupdate: Optional string. If set, emit ON UPDATE <value> when
2961 issuing DDL for this constraint. Typical values include CASCADE,
2962 DELETE and RESTRICT.
2964 .. seealso::
2966 :ref:`on_update_on_delete`
2968 :param ondelete: Optional string. If set, emit ON DELETE <value> when
2969 issuing DDL for this constraint. Typical values include CASCADE,
2970 SET NULL and RESTRICT. Some dialects may allow for additional
2971 syntaxes.
2973 .. seealso::
2975 :ref:`on_update_on_delete`
2977 :param deferrable: Optional bool. If set, emit DEFERRABLE or NOT
2978 DEFERRABLE when issuing DDL for this constraint.
2980 :param initially: Optional string. If set, emit INITIALLY <value> when
2981 issuing DDL for this constraint.
2983 :param link_to_name: if True, the string name given in ``column`` is
2984 the rendered name of the referenced column, not its locally
2985 assigned ``key``.
2987 :param use_alter: passed to the underlying
2988 :class:`_schema.ForeignKeyConstraint`
2989 to indicate the constraint should
2990 be generated/dropped externally from the CREATE TABLE/ DROP TABLE
2991 statement. See :paramref:`_schema.ForeignKeyConstraint.use_alter`
2992 for further description.
2994 .. seealso::
2996 :paramref:`_schema.ForeignKeyConstraint.use_alter`
2998 :ref:`use_alter`
3000 :param match: Optional string. If set, emit MATCH <value> when issuing
3001 DDL for this constraint. Typical values include SIMPLE, PARTIAL
3002 and FULL.
3004 :param info: Optional data dictionary which will be populated into the
3005 :attr:`.SchemaItem.info` attribute of this object.
3007 :param comment: Optional string that will render an SQL comment on
3008 foreign key constraint creation.
3010 .. versionadded:: 2.0
3012 :param \**dialect_kw: Additional keyword arguments are dialect
3013 specific, and passed in the form ``<dialectname>_<argname>``. The
3014 arguments are ultimately handled by a corresponding
3015 :class:`_schema.ForeignKeyConstraint`.
3016 See the documentation regarding
3017 an individual dialect at :ref:`dialect_toplevel` for detail on
3018 documented arguments.
3020 """
3022 self._unresolvable = _unresolvable
3024 self._colspec, self._table_column = self._parse_colspec_argument(
3025 column
3026 )
3028 # the linked ForeignKeyConstraint.
3029 # ForeignKey will create this when parent Column
3030 # is attached to a Table, *or* ForeignKeyConstraint
3031 # object passes itself in when creating ForeignKey
3032 # markers.
3033 self.constraint = _constraint
3035 # .parent is not Optional under normal use
3036 self.parent = None # type: ignore
3038 self.use_alter = use_alter
3039 self.name = name
3040 self.onupdate = onupdate
3041 self.ondelete = ondelete
3042 self.deferrable = deferrable
3043 self.initially = initially
3044 self.link_to_name = link_to_name
3045 self.match = match
3046 self.comment = comment
3047 if info:
3048 self.info = info
3049 self._unvalidated_dialect_kw = dialect_kw
3051 def _resolve_colspec_argument(
3052 self,
3053 ) -> Tuple[
3054 Union[str, Column[Any]],
3055 Optional[Column[Any]],
3056 ]:
3057 argument = self._colspec
3059 return self._parse_colspec_argument(argument)
3061 def _parse_colspec_argument(
3062 self,
3063 argument: _DDLColumnArgument,
3064 ) -> Tuple[
3065 Union[str, Column[Any]],
3066 Optional[Column[Any]],
3067 ]:
3068 _colspec = coercions.expect(roles.DDLReferredColumnRole, argument)
3070 if isinstance(_colspec, str):
3071 _table_column = None
3072 else:
3073 assert isinstance(_colspec, ColumnClause)
3074 _table_column = _colspec
3076 if not isinstance(_table_column.table, (type(None), TableClause)):
3077 raise exc.ArgumentError(
3078 "ForeignKey received Column not bound "
3079 "to a Table, got: %r" % _table_column.table
3080 )
3082 return _colspec, _table_column
3084 def __repr__(self) -> str:
3085 return "ForeignKey(%r)" % self._get_colspec()
3087 @util.deprecated(
3088 "1.4",
3089 "The :meth:`_schema.ForeignKey.copy` method is deprecated "
3090 "and will be removed in a future release.",
3091 )
3092 def copy(self, *, schema: Optional[str] = None, **kw: Any) -> ForeignKey:
3093 return self._copy(schema=schema, **kw)
3095 def _copy(self, *, schema: Optional[str] = None, **kw: Any) -> ForeignKey:
3096 """Produce a copy of this :class:`_schema.ForeignKey` object.
3098 The new :class:`_schema.ForeignKey` will not be bound
3099 to any :class:`_schema.Column`.
3101 This method is usually used by the internal
3102 copy procedures of :class:`_schema.Column`, :class:`_schema.Table`,
3103 and :class:`_schema.MetaData`.
3105 :param schema: The returned :class:`_schema.ForeignKey` will
3106 reference the original table and column name, qualified
3107 by the given string schema name.
3109 """
3110 fk = ForeignKey(
3111 self._get_colspec(schema=schema),
3112 use_alter=self.use_alter,
3113 name=self.name,
3114 onupdate=self.onupdate,
3115 ondelete=self.ondelete,
3116 deferrable=self.deferrable,
3117 initially=self.initially,
3118 link_to_name=self.link_to_name,
3119 match=self.match,
3120 comment=self.comment,
3121 **self._unvalidated_dialect_kw,
3122 )
3123 return self._schema_item_copy(fk)
3125 def _get_colspec(
3126 self,
3127 schema: Optional[
3128 Union[
3129 str,
3130 Literal[SchemaConst.RETAIN_SCHEMA, SchemaConst.BLANK_SCHEMA],
3131 ]
3132 ] = None,
3133 table_name: Optional[str] = None,
3134 _is_copy: bool = False,
3135 ) -> str:
3136 """Return a string based 'column specification' for this
3137 :class:`_schema.ForeignKey`.
3139 This is usually the equivalent of the string-based "tablename.colname"
3140 argument first passed to the object's constructor.
3142 """
3144 _colspec, effective_table_column = self._resolve_colspec_argument()
3146 if schema not in (None, RETAIN_SCHEMA):
3147 _schema, tname, colname = self._column_tokens
3148 if table_name is not None:
3149 tname = table_name
3150 if schema is BLANK_SCHEMA:
3151 return "%s.%s" % (tname, colname)
3152 else:
3153 return "%s.%s.%s" % (schema, tname, colname)
3154 elif table_name:
3155 schema, tname, colname = self._column_tokens
3156 if schema:
3157 return "%s.%s.%s" % (schema, table_name, colname)
3158 else:
3159 return "%s.%s" % (table_name, colname)
3160 elif effective_table_column is not None:
3161 if effective_table_column.table is None:
3162 if _is_copy:
3163 raise exc.InvalidRequestError(
3164 f"Can't copy ForeignKey object which refers to "
3165 f"non-table bound Column {effective_table_column!r}"
3166 )
3167 else:
3168 return effective_table_column.key
3169 return "%s.%s" % (
3170 effective_table_column.table.fullname,
3171 effective_table_column.key,
3172 )
3173 else:
3174 assert isinstance(_colspec, str)
3175 return _colspec
3177 @property
3178 def _referred_schema(self) -> Optional[str]:
3179 return self._column_tokens[0]
3181 def _table_key_within_construction(self) -> Any:
3182 """get the table key but only safely"""
3184 if self._table_column is not None:
3185 if self._table_column.table is None:
3186 return None
3187 else:
3188 return self._table_column.table.key
3189 else:
3190 schema, tname, colname = self._column_tokens
3191 return _get_table_key(tname, schema)
3193 target_fullname = property(_get_colspec)
3195 def references(self, table: Table) -> bool:
3196 """Return True if the given :class:`_schema.Table`
3197 is referenced by this
3198 :class:`_schema.ForeignKey`."""
3200 return table.corresponding_column(self.column) is not None
3202 def get_referent(self, table: FromClause) -> Optional[Column[Any]]:
3203 """Return the :class:`_schema.Column` in the given
3204 :class:`_schema.Table` (or any :class:`.FromClause`)
3205 referenced by this :class:`_schema.ForeignKey`.
3207 Returns None if this :class:`_schema.ForeignKey`
3208 does not reference the given
3209 :class:`_schema.Table`.
3211 """
3212 # our column is a Column, and any subquery etc. proxying us
3213 # would be doing so via another Column, so that's what would
3214 # be returned here
3215 return table.columns.corresponding_column(self.column) # type: ignore
3217 @util.memoized_property
3218 def _column_tokens(self) -> Tuple[Optional[str], str, Optional[str]]:
3219 """parse a string-based _colspec into its component parts."""
3221 m = self._get_colspec().split(".")
3222 if len(m) == 1:
3223 tname = m.pop()
3224 colname = None
3225 else:
3226 colname = m.pop()
3227 tname = m.pop()
3229 # A FK between column 'bar' and table 'foo' can be
3230 # specified as 'foo', 'foo.bar', 'dbo.foo.bar',
3231 # 'otherdb.dbo.foo.bar'. Once we have the column name and
3232 # the table name, treat everything else as the schema
3233 # name. Some databases (e.g. Sybase) support
3234 # inter-database foreign keys. See tickets#1341 and --
3235 # indirectly related -- Ticket #594. This assumes that '.'
3236 # will never appear *within* any component of the FK.
3238 if len(m) > 0:
3239 schema = ".".join(m)
3240 else:
3241 schema = None
3242 return schema, tname, colname
3244 def _resolve_col_tokens(self) -> Tuple[Table, str, Optional[str]]:
3245 if self.parent is None:
3246 raise exc.InvalidRequestError(
3247 "this ForeignKey object does not yet have a "
3248 "parent Column associated with it."
3249 )
3251 elif self.parent.table is None:
3252 raise exc.InvalidRequestError(
3253 "this ForeignKey's parent column is not yet associated "
3254 "with a Table."
3255 )
3257 parenttable = self.parent.table
3259 if self._unresolvable:
3260 schema, tname, colname = self._column_tokens
3261 tablekey = _get_table_key(tname, schema)
3262 return parenttable, tablekey, colname
3264 # assertion
3265 # basically Column._make_proxy() sends the actual
3266 # target Column to the ForeignKey object, so the
3267 # string resolution here is never called.
3268 for c in self.parent.base_columns:
3269 if isinstance(c, Column):
3270 assert c.table is parenttable
3271 break
3272 else:
3273 assert False
3274 ######################
3276 schema, tname, colname = self._column_tokens
3278 if schema is None and parenttable.metadata.schema is not None:
3279 schema = parenttable.metadata.schema
3281 tablekey = _get_table_key(tname, schema)
3282 return parenttable, tablekey, colname
3284 def _link_to_col_by_colstring(
3285 self, parenttable: Table, table: Table, colname: Optional[str]
3286 ) -> Column[Any]:
3287 _column = None
3288 if colname is None:
3289 # colname is None in the case that ForeignKey argument
3290 # was specified as table name only, in which case we
3291 # match the column name to the same column on the
3292 # parent.
3293 # this use case wasn't working in later 1.x series
3294 # as it had no test coverage; fixed in 2.0
3295 parent = self.parent
3296 assert parent is not None
3297 key = parent.key
3298 _column = table.c.get(key, None)
3299 elif self.link_to_name:
3300 key = colname
3301 for c in table.c:
3302 if c.name == colname:
3303 _column = c
3304 else:
3305 key = colname
3306 _column = table.c.get(colname, None)
3308 if _column is None:
3309 raise exc.NoReferencedColumnError(
3310 "Could not initialize target column "
3311 f"for ForeignKey '{self._get_colspec()}' "
3312 f"on table '{parenttable.name}': "
3313 f"table '{table.name}' has no column named '{key}'",
3314 table.name,
3315 key,
3316 )
3318 return _column
3320 def _set_target_column(self, column: Column[Any]) -> None:
3321 assert self.parent is not None
3323 # propagate TypeEngine to parent if it didn't have one
3324 if self.parent.type._isnull:
3325 self.parent.type = column.type
3327 # super-edgy case, if other FKs point to our column,
3328 # they'd get the type propagated out also.
3330 def set_type(fk: ForeignKey) -> None:
3331 if fk.parent.type._isnull:
3332 fk.parent.type = column.type
3334 self.parent._setup_on_memoized_fks(set_type)
3336 self.column = column # type: ignore
3338 @util.ro_memoized_property
3339 def column(self) -> Column[Any]:
3340 """Return the target :class:`_schema.Column` referenced by this
3341 :class:`_schema.ForeignKey`.
3343 If no target column has been established, an exception
3344 is raised.
3346 """
3347 return self._resolve_column()
3349 @overload
3350 def _resolve_column(
3351 self, *, raiseerr: Literal[True] = ...
3352 ) -> Column[Any]: ...
3354 @overload
3355 def _resolve_column(
3356 self, *, raiseerr: bool = ...
3357 ) -> Optional[Column[Any]]: ...
3359 def _resolve_column(
3360 self, *, raiseerr: bool = True
3361 ) -> Optional[Column[Any]]:
3362 _column: Column[Any]
3364 _colspec, effective_table_column = self._resolve_colspec_argument()
3366 if isinstance(_colspec, str):
3367 parenttable, tablekey, colname = self._resolve_col_tokens()
3369 if self._unresolvable or tablekey not in parenttable.metadata:
3370 if not raiseerr:
3371 return None
3372 raise exc.NoReferencedTableError(
3373 f"Foreign key associated with column "
3374 f"'{self.parent}' could not find "
3375 f"table '{tablekey}' with which to generate a "
3376 f"foreign key to target column '{colname}'",
3377 tablekey,
3378 )
3379 elif parenttable.key not in parenttable.metadata:
3380 if not raiseerr:
3381 return None
3382 raise exc.InvalidRequestError(
3383 f"Table {parenttable} is no longer associated with its "
3384 "parent MetaData"
3385 )
3386 else:
3387 table = parenttable.metadata.tables[tablekey]
3388 return self._link_to_col_by_colstring(
3389 parenttable, table, colname
3390 )
3392 elif hasattr(_colspec, "__clause_element__"):
3393 _column = _colspec.__clause_element__()
3394 return _column
3395 else:
3396 assert isinstance(_colspec, Column)
3397 _column = _colspec
3398 return _column
3400 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
3401 assert isinstance(parent, Column)
3403 if self.parent is not None and self.parent is not parent:
3404 raise exc.InvalidRequestError(
3405 "This ForeignKey already has a parent !"
3406 )
3407 self.parent = parent
3408 self.parent.foreign_keys.add(self)
3409 self.parent._on_table_attach(self._set_table)
3411 def _set_remote_table(self, table: Table) -> None:
3412 parenttable, _, colname = self._resolve_col_tokens()
3413 _column = self._link_to_col_by_colstring(parenttable, table, colname)
3414 self._set_target_column(_column)
3415 assert self.constraint is not None
3416 self.constraint._validate_dest_table(table)
3418 def _remove_from_metadata(self, metadata: MetaData) -> None:
3419 parenttable, table_key, colname = self._resolve_col_tokens()
3420 fk_key = (table_key, colname)
3422 if self in metadata._fk_memos[fk_key]:
3423 # TODO: no test coverage for self not in memos
3424 metadata._fk_memos[fk_key].remove(self)
3426 def _set_table(self, column: Column[Any], table: Table) -> None:
3427 # standalone ForeignKey - create ForeignKeyConstraint
3428 # on the hosting Table when attached to the Table.
3429 assert isinstance(table, Table)
3430 if self.constraint is None:
3431 self.constraint = ForeignKeyConstraint(
3432 [],
3433 [],
3434 use_alter=self.use_alter,
3435 name=self.name,
3436 onupdate=self.onupdate,
3437 ondelete=self.ondelete,
3438 deferrable=self.deferrable,
3439 initially=self.initially,
3440 match=self.match,
3441 comment=self.comment,
3442 **self._unvalidated_dialect_kw,
3443 )
3444 self.constraint._append_element(column, self)
3445 self.constraint._set_parent_with_dispatch(table)
3446 table.foreign_keys.add(self)
3447 # set up remote ".column" attribute, or a note to pick it
3448 # up when the other Table/Column shows up
3450 _colspec, _ = self._resolve_colspec_argument()
3451 if isinstance(_colspec, str):
3452 parenttable, table_key, colname = self._resolve_col_tokens()
3453 fk_key = (table_key, colname)
3454 if table_key in parenttable.metadata.tables:
3455 table = parenttable.metadata.tables[table_key]
3456 try:
3457 _column = self._link_to_col_by_colstring(
3458 parenttable, table, colname
3459 )
3460 except exc.NoReferencedColumnError:
3461 # this is OK, we'll try later
3462 pass
3463 else:
3464 self._set_target_column(_column)
3466 parenttable.metadata._fk_memos[fk_key].append(self)
3467 elif hasattr(_colspec, "__clause_element__"):
3468 _column = _colspec.__clause_element__()
3469 self._set_target_column(_column)
3470 else:
3471 self._set_target_column(_colspec)
3474if TYPE_CHECKING:
3476 def default_is_sequence(
3477 obj: Optional[DefaultGenerator],
3478 ) -> TypeGuard[Sequence]: ...
3480 def default_is_clause_element(
3481 obj: Optional[DefaultGenerator],
3482 ) -> TypeGuard[ColumnElementColumnDefault]: ...
3484 def default_is_scalar(
3485 obj: Optional[DefaultGenerator],
3486 ) -> TypeGuard[ScalarElementColumnDefault]: ...
3488else:
3489 default_is_sequence = operator.attrgetter("is_sequence")
3491 default_is_clause_element = operator.attrgetter("is_clause_element")
3493 default_is_scalar = operator.attrgetter("is_scalar")
3496class DefaultGenerator(Executable, SchemaItem):
3497 """Base class for column *default* values.
3499 This object is only present on column.default or column.onupdate.
3500 It's not valid as a server default.
3502 """
3504 __visit_name__ = "default_generator"
3506 _is_default_generator = True
3507 is_sequence = False
3508 is_identity = False
3509 is_server_default = False
3510 is_clause_element = False
3511 is_callable = False
3512 is_scalar = False
3513 has_arg = False
3514 is_sentinel = False
3515 column: Optional[Column[Any]]
3517 def __init__(self, for_update: bool = False) -> None:
3518 self.for_update = for_update
3520 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
3521 if TYPE_CHECKING:
3522 assert isinstance(parent, Column)
3523 self.column = parent
3524 if self.for_update:
3525 self.column.onupdate = self
3526 else:
3527 self.column.default = self
3529 def _copy(self) -> DefaultGenerator:
3530 raise NotImplementedError()
3532 def _execute_on_connection(
3533 self,
3534 connection: Connection,
3535 distilled_params: _CoreMultiExecuteParams,
3536 execution_options: CoreExecuteOptionsParameter,
3537 ) -> Any:
3538 util.warn_deprecated(
3539 "Using the .execute() method to invoke a "
3540 "DefaultGenerator object is deprecated; please use "
3541 "the .scalar() method.",
3542 "2.0",
3543 )
3544 return self._execute_on_scalar(
3545 connection, distilled_params, execution_options
3546 )
3548 def _execute_on_scalar(
3549 self,
3550 connection: Connection,
3551 distilled_params: _CoreMultiExecuteParams,
3552 execution_options: CoreExecuteOptionsParameter,
3553 ) -> Any:
3554 return connection._execute_default(
3555 self, distilled_params, execution_options
3556 )
3559class ColumnDefault(DefaultGenerator, ABC):
3560 """A plain default value on a column.
3562 This could correspond to a constant, a callable function,
3563 or a SQL clause.
3565 :class:`.ColumnDefault` is generated automatically
3566 whenever the ``default``, ``onupdate`` arguments of
3567 :class:`_schema.Column` are used. A :class:`.ColumnDefault`
3568 can be passed positionally as well.
3570 For example, the following::
3572 Column("foo", Integer, default=50)
3574 Is equivalent to::
3576 Column("foo", Integer, ColumnDefault(50))
3578 """
3580 arg: Any
3582 @overload
3583 def __new__(
3584 cls, arg: Callable[..., Any], for_update: bool = ...
3585 ) -> CallableColumnDefault: ...
3587 @overload
3588 def __new__(
3589 cls, arg: ColumnElement[Any], for_update: bool = ...
3590 ) -> ColumnElementColumnDefault: ...
3592 # if I return ScalarElementColumnDefault here, which is what's actually
3593 # returned, mypy complains that
3594 # overloads overlap w/ incompatible return types.
3595 @overload
3596 def __new__(cls, arg: object, for_update: bool = ...) -> ColumnDefault: ...
3598 def __new__(
3599 cls, arg: Any = None, for_update: bool = False
3600 ) -> ColumnDefault:
3601 """Construct a new :class:`.ColumnDefault`.
3604 :param arg: argument representing the default value.
3605 May be one of the following:
3607 * a plain non-callable Python value, such as a
3608 string, integer, boolean, or other simple type.
3609 The default value will be used as is each time.
3610 * a SQL expression, that is one which derives from
3611 :class:`_expression.ColumnElement`. The SQL expression will
3612 be rendered into the INSERT or UPDATE statement,
3613 or in the case of a primary key column when
3614 RETURNING is not used may be
3615 pre-executed before an INSERT within a SELECT.
3616 * A Python callable. The function will be invoked for each
3617 new row subject to an INSERT or UPDATE.
3618 The callable must accept exactly
3619 zero or one positional arguments. The one-argument form
3620 will receive an instance of the :class:`.ExecutionContext`,
3621 which provides contextual information as to the current
3622 :class:`_engine.Connection` in use as well as the current
3623 statement and parameters.
3625 """
3627 if isinstance(arg, FetchedValue):
3628 raise exc.ArgumentError(
3629 "ColumnDefault may not be a server-side default type."
3630 )
3631 elif callable(arg):
3632 cls = CallableColumnDefault
3633 elif isinstance(arg, ClauseElement):
3634 cls = ColumnElementColumnDefault
3635 elif arg is not None:
3636 cls = ScalarElementColumnDefault
3638 return object.__new__(cls)
3640 def __repr__(self) -> str:
3641 return f"{self.__class__.__name__}({self.arg!r})"
3644class ScalarElementColumnDefault(ColumnDefault):
3645 """default generator for a fixed scalar Python value
3647 .. versionadded:: 2.0
3649 """
3651 is_scalar = True
3652 has_arg = True
3654 def __init__(self, arg: Any, for_update: bool = False) -> None:
3655 self.for_update = for_update
3656 self.arg = arg
3658 def _copy(self) -> ScalarElementColumnDefault:
3659 return ScalarElementColumnDefault(
3660 arg=self.arg, for_update=self.for_update
3661 )
3664class _InsertSentinelColumnDefault(ColumnDefault):
3665 """Default generator that's specific to the use of a "sentinel" column
3666 when using the insertmanyvalues feature.
3668 This default is used as part of the :func:`_schema.insert_sentinel`
3669 construct.
3671 """
3673 is_sentinel = True
3674 for_update = False
3675 arg = None
3677 def __new__(cls) -> _InsertSentinelColumnDefault:
3678 return object.__new__(cls)
3680 def __init__(self) -> None:
3681 pass
3683 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
3684 col = cast("Column[Any]", parent)
3685 if not col._insert_sentinel:
3686 raise exc.ArgumentError(
3687 "The _InsertSentinelColumnDefault may only be applied to a "
3688 "Column marked as insert_sentinel=True"
3689 )
3690 elif not col.nullable:
3691 raise exc.ArgumentError(
3692 "The _InsertSentinelColumnDefault may only be applied to a "
3693 "Column that is nullable"
3694 )
3696 super()._set_parent(parent, **kw)
3698 def _copy(self) -> _InsertSentinelColumnDefault:
3699 return _InsertSentinelColumnDefault()
3702_SQLExprDefault = Union["ColumnElement[Any]", "TextClause"]
3705class ColumnElementColumnDefault(ColumnDefault):
3706 """default generator for a SQL expression
3708 .. versionadded:: 2.0
3710 """
3712 is_clause_element = True
3713 has_arg = True
3714 arg: _SQLExprDefault
3716 def __init__(
3717 self,
3718 arg: _SQLExprDefault,
3719 for_update: bool = False,
3720 ) -> None:
3721 self.for_update = for_update
3722 self.arg = arg
3724 def _copy(self) -> ColumnElementColumnDefault:
3725 return ColumnElementColumnDefault(
3726 arg=self.arg, for_update=self.for_update
3727 )
3729 @util.memoized_property
3730 @util.preload_module("sqlalchemy.sql.sqltypes")
3731 def _arg_is_typed(self) -> bool:
3732 sqltypes = util.preloaded.sql_sqltypes
3734 return not isinstance(self.arg.type, sqltypes.NullType)
3737class _CallableColumnDefaultProtocol(Protocol):
3738 def __call__(self, context: ExecutionContext) -> Any: ...
3741class CallableColumnDefault(ColumnDefault):
3742 """default generator for a callable Python function
3744 .. versionadded:: 2.0
3746 """
3748 is_callable = True
3749 arg: _CallableColumnDefaultProtocol
3750 has_arg = True
3752 def __init__(
3753 self,
3754 arg: Union[_CallableColumnDefaultProtocol, Callable[[], Any]],
3755 for_update: bool = False,
3756 ) -> None:
3757 self.for_update = for_update
3758 self.arg = self._maybe_wrap_callable(arg)
3760 def _copy(self) -> CallableColumnDefault:
3761 return CallableColumnDefault(arg=self.arg, for_update=self.for_update)
3763 def _maybe_wrap_callable(
3764 self, fn: Union[_CallableColumnDefaultProtocol, Callable[[], Any]]
3765 ) -> _CallableColumnDefaultProtocol:
3766 """Wrap callables that don't accept a context.
3768 This is to allow easy compatibility with default callables
3769 that aren't specific to accepting of a context.
3771 """
3773 try:
3774 argspec = util.get_callable_argspec(fn, no_self=True)
3775 except TypeError:
3776 return util.wrap_callable(lambda ctx: fn(), fn) # type: ignore
3778 defaulted = argspec[3] is not None and len(argspec[3]) or 0
3779 positionals = len(argspec[0]) - defaulted
3781 if positionals == 0:
3782 return util.wrap_callable(lambda ctx: fn(), fn) # type: ignore
3784 elif positionals == 1:
3785 return fn # type: ignore
3786 else:
3787 raise exc.ArgumentError(
3788 "ColumnDefault Python function takes zero or one "
3789 "positional arguments"
3790 )
3793class IdentityOptions(DialectKWArgs):
3794 """Defines options for a named database sequence or an identity column.
3796 .. seealso::
3798 :class:`.Sequence`
3800 """
3802 def __init__(
3803 self,
3804 start: Optional[int] = None,
3805 increment: Optional[int] = None,
3806 minvalue: Optional[int] = None,
3807 maxvalue: Optional[int] = None,
3808 nominvalue: Optional[bool] = None,
3809 nomaxvalue: Optional[bool] = None,
3810 cycle: Optional[bool] = None,
3811 cache: Optional[int] = None,
3812 order: Optional[bool] = None,
3813 **dialect_kw: Any,
3814 ) -> None:
3815 """Construct a :class:`.IdentityOptions` object.
3817 See the :class:`.Sequence` documentation for a complete description
3818 of the parameters.
3820 :param start: the starting index of the sequence.
3821 :param increment: the increment value of the sequence.
3822 :param minvalue: the minimum value of the sequence.
3823 :param maxvalue: the maximum value of the sequence.
3824 :param nominvalue: no minimum value of the sequence.
3825 :param nomaxvalue: no maximum value of the sequence.
3826 :param cycle: allows the sequence to wrap around when the maxvalue
3827 or minvalue has been reached.
3828 :param cache: optional integer value; number of future values in the
3829 sequence which are calculated in advance.
3830 :param order: optional boolean value; if ``True``, renders the
3831 ORDER keyword.
3833 .. deprecated:: 2.1 Use ``oracle_order`` instead.
3835 """
3836 self.start = start
3837 self.increment = increment
3838 self.minvalue = minvalue
3839 self.maxvalue = maxvalue
3840 self.nominvalue = nominvalue
3841 self.nomaxvalue = nomaxvalue
3842 self.cycle = cycle
3843 self.cache = cache
3844 if order is not None:
3845 if "oracle_order" in dialect_kw:
3846 raise exc.ArgumentError(
3847 "Cannot specify both 'order' and 'oracle_order'. "
3848 "Plese use only 'oracle_order'."
3849 )
3850 dialect_kw["oracle_order"] = order
3851 self._validate_dialect_kwargs(dialect_kw)
3853 @property
3854 def _increment_is_negative(self) -> bool:
3855 return self.increment is not None and self.increment < 0
3857 @property
3858 def order(self) -> Optional[bool]:
3859 """Alias of the ``dialect_kwargs`` ``'oracle_order'``.
3861 .. deprecated:: 2.1 The 'order' attribute is deprecated.
3862 """
3863 value: Optional[bool] = self.dialect_kwargs.get("oracle_order")
3864 return value
3866 def _as_dict(self) -> Dict[str, Any]:
3867 return {
3868 k: v
3869 for k, v in {
3870 "start": self.start,
3871 "increment": self.increment,
3872 "minvalue": self.minvalue,
3873 "maxvalue": self.maxvalue,
3874 "nominvalue": self.nominvalue,
3875 "nomaxvalue": self.nomaxvalue,
3876 "cycle": self.cycle,
3877 "cache": self.cache,
3878 }.items()
3879 if v != None
3880 }
3883class Sequence(HasSchemaAttr, IdentityOptions, DefaultGenerator):
3884 """Represents a named database sequence.
3886 The :class:`.Sequence` object represents the name and configurational
3887 parameters of a database sequence. It also represents
3888 a construct that can be "executed" by a SQLAlchemy :class:`_engine.Engine`
3889 or :class:`_engine.Connection`,
3890 rendering the appropriate "next value" function
3891 for the target database and returning a result.
3893 The :class:`.Sequence` is typically associated with a primary key column::
3895 some_table = Table(
3896 "some_table",
3897 metadata,
3898 Column(
3899 "id",
3900 Integer,
3901 Sequence("some_table_seq", start=1),
3902 primary_key=True,
3903 ),
3904 )
3906 When CREATE TABLE is emitted for the above :class:`_schema.Table`, if the
3907 target platform supports sequences, a CREATE SEQUENCE statement will
3908 be emitted as well. For platforms that don't support sequences,
3909 the :class:`.Sequence` construct is ignored.
3911 .. seealso::
3913 :ref:`defaults_sequences`
3915 :class:`.CreateSequence`
3917 :class:`.DropSequence`
3919 """
3921 __visit_name__ = "sequence"
3923 is_sequence = True
3925 column: Optional[Column[Any]]
3926 data_type: Optional[TypeEngine[int]]
3928 metadata: Optional[MetaData]
3930 @util.deprecated_params(
3931 order=(
3932 "2.1",
3933 "This parameter is supported only by Oracle Database, "
3934 "use ``oracle_order`` instead.",
3935 )
3936 )
3937 def __init__(
3938 self,
3939 name: str,
3940 start: Optional[int] = None,
3941 increment: Optional[int] = None,
3942 minvalue: Optional[int] = None,
3943 maxvalue: Optional[int] = None,
3944 nominvalue: Optional[bool] = None,
3945 nomaxvalue: Optional[bool] = None,
3946 cycle: Optional[bool] = None,
3947 schema: Optional[Union[str, Literal[SchemaConst.BLANK_SCHEMA]]] = None,
3948 cache: Optional[int] = None,
3949 order: Optional[bool] = None,
3950 data_type: Optional[_TypeEngineArgument[int]] = None,
3951 optional: bool = False,
3952 quote: Optional[bool] = None,
3953 metadata: Optional[MetaData] = None,
3954 quote_schema: Optional[bool] = None,
3955 for_update: bool = False,
3956 **dialect_kw: Any,
3957 ) -> None:
3958 """Construct a :class:`.Sequence` object.
3960 :param name: the name of the sequence.
3962 :param start: the starting index of the sequence. This value is
3963 used when the CREATE SEQUENCE command is emitted to the database
3964 as the value of the "START WITH" clause. If ``None``, the
3965 clause is omitted, which on most platforms indicates a starting
3966 value of 1.
3968 .. versionchanged:: 2.0 The :paramref:`.Sequence.start` parameter
3969 is required in order to have DDL emit "START WITH". This is a
3970 reversal of a change made in version 1.4 which would implicitly
3971 render "START WITH 1" if the :paramref:`.Sequence.start` were
3972 not included. See :ref:`change_7211` for more detail.
3974 :param increment: the increment value of the sequence. This
3975 value is used when the CREATE SEQUENCE command is emitted to
3976 the database as the value of the "INCREMENT BY" clause. If ``None``,
3977 the clause is omitted, which on most platforms indicates an
3978 increment of 1.
3979 :param minvalue: the minimum value of the sequence. This
3980 value is used when the CREATE SEQUENCE command is emitted to
3981 the database as the value of the "MINVALUE" clause. If ``None``,
3982 the clause is omitted, which on most platforms indicates a
3983 minvalue of 1 and -2^63-1 for ascending and descending sequences,
3984 respectively.
3986 :param maxvalue: the maximum value of the sequence. This
3987 value is used when the CREATE SEQUENCE command is emitted to
3988 the database as the value of the "MAXVALUE" clause. If ``None``,
3989 the clause is omitted, which on most platforms indicates a
3990 maxvalue of 2^63-1 and -1 for ascending and descending sequences,
3991 respectively.
3993 :param nominvalue: no minimum value of the sequence. This
3994 value is used when the CREATE SEQUENCE command is emitted to
3995 the database as the value of the "NO MINVALUE" clause. If ``None``,
3996 the clause is omitted, which on most platforms indicates a
3997 minvalue of 1 and -2^63-1 for ascending and descending sequences,
3998 respectively.
4000 :param nomaxvalue: no maximum value of the sequence. This
4001 value is used when the CREATE SEQUENCE command is emitted to
4002 the database as the value of the "NO MAXVALUE" clause. If ``None``,
4003 the clause is omitted, which on most platforms indicates a
4004 maxvalue of 2^63-1 and -1 for ascending and descending sequences,
4005 respectively.
4007 :param cycle: allows the sequence to wrap around when the maxvalue
4008 or minvalue has been reached by an ascending or descending sequence
4009 respectively. This value is used when the CREATE SEQUENCE command
4010 is emitted to the database as the "CYCLE" clause. If the limit is
4011 reached, the next number generated will be the minvalue or maxvalue,
4012 respectively. If cycle=False (the default) any calls to nextval
4013 after the sequence has reached its maximum value will return an
4014 error.
4016 :param schema: optional schema name for the sequence, if located
4017 in a schema other than the default. The rules for selecting the
4018 schema name when a :class:`_schema.MetaData`
4019 is also present are the same
4020 as that of :paramref:`_schema.Table.schema`.
4022 :param cache: optional integer value; number of future values in the
4023 sequence which are calculated in advance. Renders the CACHE keyword
4024 understood by Oracle Database and PostgreSQL.
4026 :param order: optional boolean value; if ``True``, renders the
4027 ORDER keyword, understood by Oracle Database, indicating the sequence
4028 is definitively ordered. May be necessary to provide deterministic
4029 ordering using Oracle RAC.
4031 :param data_type: The type to be returned by the sequence, for
4032 dialects that allow us to choose between INTEGER, BIGINT, etc.
4033 (e.g., mssql).
4035 .. versionadded:: 1.4.0
4037 :param optional: boolean value, when ``True``, indicates that this
4038 :class:`.Sequence` object only needs to be explicitly generated
4039 on backends that don't provide another way to generate primary
4040 key identifiers. Currently, it essentially means, "don't create
4041 this sequence on the PostgreSQL backend, where the SERIAL keyword
4042 creates a sequence for us automatically".
4043 :param quote: boolean value, when ``True`` or ``False``, explicitly
4044 forces quoting of the :paramref:`_schema.Sequence.name` on or off.
4045 When left at its default of ``None``, normal quoting rules based
4046 on casing and reserved words take place.
4047 :param quote_schema: Set the quoting preferences for the ``schema``
4048 name.
4050 :param metadata: optional :class:`_schema.MetaData` object which this
4051 :class:`.Sequence` will be associated with. A :class:`.Sequence`
4052 that is associated with a :class:`_schema.MetaData`
4053 gains the following
4054 capabilities:
4056 * The :class:`.Sequence` will inherit the
4057 :paramref:`_schema.MetaData.schema`
4058 parameter specified to the target :class:`_schema.MetaData`, which
4059 affects the production of CREATE / DROP DDL, if any.
4061 * The :meth:`.Sequence.create` and :meth:`.Sequence.drop` methods
4062 automatically use the engine bound to the :class:`_schema.MetaData`
4063 object, if any.
4065 * The :meth:`_schema.MetaData.create_all` and
4066 :meth:`_schema.MetaData.drop_all`
4067 methods will emit CREATE / DROP for this :class:`.Sequence`,
4068 even if the :class:`.Sequence` is not associated with any
4069 :class:`_schema.Table` / :class:`_schema.Column`
4070 that's a member of this
4071 :class:`_schema.MetaData`.
4073 The above behaviors can only occur if the :class:`.Sequence` is
4074 explicitly associated with the :class:`_schema.MetaData`
4075 via this parameter.
4077 .. seealso::
4079 :ref:`sequence_metadata` - full discussion of the
4080 :paramref:`.Sequence.metadata` parameter.
4082 :param for_update: Indicates this :class:`.Sequence`, when associated
4083 with a :class:`_schema.Column`,
4084 should be invoked for UPDATE statements
4085 on that column's table, rather than for INSERT statements, when
4086 no value is otherwise present for that column in the statement.
4088 """
4089 DefaultGenerator.__init__(self, for_update=for_update)
4090 IdentityOptions.__init__(
4091 self,
4092 start=start,
4093 increment=increment,
4094 minvalue=minvalue,
4095 maxvalue=maxvalue,
4096 nominvalue=nominvalue,
4097 nomaxvalue=nomaxvalue,
4098 cycle=cycle,
4099 cache=cache,
4100 order=order,
4101 **dialect_kw,
4102 )
4103 self.column = None
4104 self.name = quoted_name(name, quote)
4105 self.optional = optional
4106 if schema is BLANK_SCHEMA:
4107 self.schema = schema = None
4108 elif metadata is not None and schema is None and metadata.schema:
4109 self.schema = schema = metadata.schema
4110 else:
4111 self.schema = quoted_name.construct(schema, quote_schema)
4112 self._key = _get_table_key(name, schema)
4113 if data_type is not None:
4114 self.data_type = to_instance(data_type)
4115 else:
4116 self.data_type = None
4118 if metadata:
4119 self._set_metadata(metadata)
4120 else:
4121 self.metadata = None
4123 @util.preload_module("sqlalchemy.sql.functions")
4124 def next_value(self) -> Function[int]:
4125 """Return a :class:`.next_value` function element
4126 which will render the appropriate increment function
4127 for this :class:`.Sequence` within any SQL expression.
4129 """
4130 return util.preloaded.sql_functions.func.next_value(self)
4132 def _copy(self) -> Sequence:
4133 return Sequence(
4134 name=self.name,
4135 schema=self.schema,
4136 data_type=self.data_type,
4137 optional=self.optional,
4138 metadata=self.metadata,
4139 for_update=self.for_update,
4140 **self._as_dict(),
4141 **self.dialect_kwargs,
4142 )
4144 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4145 assert isinstance(parent, Column)
4146 super()._set_parent(parent, **kw)
4147 parent._on_table_attach(self._set_table)
4149 def _set_table(self, column: Column[Any], table: Table) -> None:
4150 self._set_metadata(table.metadata)
4152 def _set_metadata(self, metadata: MetaData) -> None:
4153 self.metadata = metadata
4154 self.metadata._register_object(self)
4155 metadata._sequences[self._key] = self
4157 def create(
4158 self,
4159 bind: _CreateDropBind,
4160 checkfirst: Union[bool, CheckFirst] = CheckFirst.SEQUENCES,
4161 ) -> None:
4162 """Creates this sequence in the database."""
4164 bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
4166 def drop(
4167 self,
4168 bind: _CreateDropBind,
4169 checkfirst: Union[bool, CheckFirst] = CheckFirst.SEQUENCES,
4170 ) -> None:
4171 """Drops this sequence from the database."""
4173 bind._run_ddl_visitor(ddl.SchemaDropper, self, checkfirst=checkfirst)
4175 def _not_a_column_expr(self) -> NoReturn:
4176 raise exc.InvalidRequestError(
4177 f"This {self.__class__.__name__} cannot be used directly "
4178 "as a column expression. Use func.next_value(sequence) "
4179 "to produce a 'next value' function that's usable "
4180 "as a column element."
4181 )
4184@inspection._self_inspects
4185class FetchedValue(SchemaEventTarget):
4186 """A marker for a transparent database-side default.
4188 Use :class:`.FetchedValue` when the database is configured
4189 to provide some automatic default for a column.
4191 E.g.::
4193 Column("foo", Integer, FetchedValue())
4195 Would indicate that some trigger or default generator
4196 will create a new value for the ``foo`` column during an
4197 INSERT.
4199 .. seealso::
4201 :ref:`triggered_columns`
4203 """
4205 is_server_default = True
4206 reflected = False
4207 has_argument = False
4208 is_clause_element = False
4209 is_identity = False
4211 column: Optional[Column[Any]]
4213 def __init__(self, for_update: bool = False) -> None:
4214 self.for_update = for_update
4216 def _as_for_update(self, for_update: bool) -> FetchedValue:
4217 if for_update == self.for_update:
4218 return self
4219 else:
4220 return self._clone(for_update)
4222 def _copy(self) -> FetchedValue:
4223 return FetchedValue(self.for_update)
4225 def _clone(self, for_update: bool) -> Self:
4226 n = self.__class__.__new__(self.__class__)
4227 n.__dict__.update(self.__dict__)
4228 n.__dict__.pop("column", None)
4229 n.for_update = for_update
4230 return n
4232 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4233 column = parent
4234 assert isinstance(column, Column)
4235 self.column = column
4236 if self.for_update:
4237 self.column.server_onupdate = self
4238 else:
4239 self.column.server_default = self
4241 def __repr__(self) -> str:
4242 return util.generic_repr(self)
4245class DefaultClause(FetchedValue):
4246 """A DDL-specified DEFAULT column value.
4248 :class:`.DefaultClause` is a :class:`.FetchedValue`
4249 that also generates a "DEFAULT" clause when
4250 "CREATE TABLE" is emitted.
4252 :class:`.DefaultClause` is generated automatically
4253 whenever the ``server_default``, ``server_onupdate`` arguments of
4254 :class:`_schema.Column` are used. A :class:`.DefaultClause`
4255 can be passed positionally as well.
4257 For example, the following::
4259 Column("foo", Integer, server_default="50")
4261 Is equivalent to::
4263 Column("foo", Integer, DefaultClause("50"))
4265 """
4267 has_argument = True
4269 def __init__(
4270 self,
4271 arg: Union[str, ClauseElement, TextClause],
4272 for_update: bool = False,
4273 _reflected: bool = False,
4274 ) -> None:
4275 util.assert_arg_type(arg, (str, ClauseElement, TextClause), "arg")
4276 super().__init__(for_update)
4277 self.arg = arg
4278 self.reflected = _reflected
4280 def _copy(self) -> DefaultClause:
4281 return DefaultClause(
4282 arg=self.arg, for_update=self.for_update, _reflected=self.reflected
4283 )
4285 def __repr__(self) -> str:
4286 return "DefaultClause(%r, for_update=%r)" % (self.arg, self.for_update)
4289class Constraint(DialectKWArgs, HasConditionalDDL, SchemaItem):
4290 """A table-level SQL constraint.
4292 :class:`_schema.Constraint` serves as the base class for the series of
4293 constraint objects that can be associated with :class:`_schema.Table`
4294 objects, including :class:`_schema.PrimaryKeyConstraint`,
4295 :class:`_schema.ForeignKeyConstraint`
4296 :class:`_schema.UniqueConstraint`, and
4297 :class:`_schema.CheckConstraint`.
4299 """
4301 __visit_name__ = "constraint"
4303 _creation_order: int
4304 _column_flag: bool
4306 def __init__(
4307 self,
4308 name: _ConstraintNameArgument = None,
4309 deferrable: Optional[bool] = None,
4310 initially: Optional[str] = None,
4311 info: Optional[_InfoType] = None,
4312 comment: Optional[str] = None,
4313 _create_rule: Optional[Any] = None,
4314 _type_bound: bool = False,
4315 **dialect_kw: Any,
4316 ) -> None:
4317 r"""Create a SQL constraint.
4319 :param name:
4320 Optional, the in-database name of this ``Constraint``.
4322 :param deferrable:
4323 Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
4324 issuing DDL for this constraint.
4326 :param initially:
4327 Optional string. If set, emit INITIALLY <value> when issuing DDL
4328 for this constraint.
4330 :param info: Optional data dictionary which will be populated into the
4331 :attr:`.SchemaItem.info` attribute of this object.
4333 :param comment: Optional string that will render an SQL comment on
4334 foreign key constraint creation.
4336 .. versionadded:: 2.0
4338 :param \**dialect_kw: Additional keyword arguments are dialect
4339 specific, and passed in the form ``<dialectname>_<argname>``. See
4340 the documentation regarding an individual dialect at
4341 :ref:`dialect_toplevel` for detail on documented arguments.
4343 :param _create_rule:
4344 used internally by some datatypes that also create constraints.
4346 :param _type_bound:
4347 used internally to indicate that this constraint is associated with
4348 a specific datatype.
4350 """
4352 self.name = name
4353 self.deferrable = deferrable
4354 self.initially = initially
4355 if info:
4356 self.info = info
4357 self._create_rule = _create_rule
4358 self._type_bound = _type_bound
4359 util.set_creation_order(self)
4360 self._validate_dialect_kwargs(dialect_kw)
4361 self.comment = comment
4363 def _should_create_for_compiler(
4364 self, compiler: DDLCompiler, **kw: Any
4365 ) -> bool:
4366 if self._create_rule is not None and not self._create_rule(compiler):
4367 return False
4368 elif self._ddl_if is not None:
4369 return self._ddl_if._should_execute(
4370 ddl.CreateConstraint(self), self, None, compiler=compiler, **kw
4371 )
4372 else:
4373 return True
4375 @property
4376 def table(self) -> Table:
4377 try:
4378 if isinstance(self.parent, Table):
4379 return self.parent
4380 except AttributeError:
4381 pass
4382 raise exc.InvalidRequestError(
4383 "This constraint is not bound to a table. Did you "
4384 "mean to call table.append_constraint(constraint) ?"
4385 )
4387 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4388 assert isinstance(parent, (Table, Column))
4389 self.parent = parent
4390 parent.constraints.add(self)
4392 @util.deprecated(
4393 "1.4",
4394 "The :meth:`_schema.Constraint.copy` method is deprecated "
4395 "and will be removed in a future release.",
4396 )
4397 def copy(self, **kw: Any) -> Self:
4398 return self._copy(**kw)
4400 def _copy(self, **kw: Any) -> Self:
4401 raise NotImplementedError()
4404class ColumnCollectionMixin:
4405 """A :class:`_expression.ColumnCollection` of :class:`_schema.Column`
4406 objects.
4408 This collection represents the columns which are referred to by
4409 this object.
4411 """
4413 _columns: DedupeColumnCollection[Column[Any]]
4415 _allow_multiple_tables = False
4417 _pending_colargs: List[Optional[Union[str, Column[Any]]]]
4419 if TYPE_CHECKING:
4421 def _set_parent_with_dispatch(
4422 self, parent: SchemaEventTarget, **kw: Any
4423 ) -> None: ...
4425 def __init__(
4426 self,
4427 *columns: _DDLColumnArgument,
4428 _autoattach: bool = True,
4429 _column_flag: bool = False,
4430 _gather_expressions: Optional[
4431 List[Union[str, ColumnElement[Any]]]
4432 ] = None,
4433 ) -> None:
4434 self._column_flag = _column_flag
4435 self._columns = DedupeColumnCollection()
4437 processed_expressions: Optional[
4438 List[Union[ColumnElement[Any], str]]
4439 ] = _gather_expressions
4441 if processed_expressions is not None:
4443 # this is expected to be an empty list
4444 assert not processed_expressions
4446 self._pending_colargs = []
4447 for (
4448 expr,
4449 _,
4450 _,
4451 add_element,
4452 ) in coercions.expect_col_expression_collection(
4453 roles.DDLConstraintColumnRole, columns
4454 ):
4455 self._pending_colargs.append(add_element)
4456 processed_expressions.append(expr)
4457 else:
4458 self._pending_colargs = [
4459 coercions.expect(roles.DDLConstraintColumnRole, column)
4460 for column in columns
4461 ]
4463 if _autoattach and self._pending_colargs:
4464 self._check_attach()
4466 def _check_attach(self, evt: bool = False) -> None:
4467 col_objs = [c for c in self._pending_colargs if isinstance(c, Column)]
4469 cols_w_table = [c for c in col_objs if isinstance(c.table, Table)]
4471 cols_wo_table = set(col_objs).difference(cols_w_table)
4472 if cols_wo_table:
4473 # feature #3341 - place event listeners for Column objects
4474 # such that when all those cols are attached, we autoattach.
4475 assert not evt, "Should not reach here on event call"
4477 # issue #3411 - don't do the per-column auto-attach if some of the
4478 # columns are specified as strings.
4479 has_string_cols = {
4480 c for c in self._pending_colargs if c is not None
4481 }.difference(col_objs)
4482 if not has_string_cols:
4484 def _col_attached(column: Column[Any], table: Table) -> None:
4485 # this isinstance() corresponds with the
4486 # isinstance() above; only want to count Table-bound
4487 # columns
4488 if isinstance(table, Table):
4489 cols_wo_table.discard(column)
4490 if not cols_wo_table:
4491 self._check_attach(evt=True)
4493 self._cols_wo_table = cols_wo_table
4494 for col in cols_wo_table:
4495 col._on_table_attach(_col_attached)
4496 return
4498 columns = cols_w_table
4500 tables = {c.table for c in columns}
4501 if len(tables) == 1:
4502 self._set_parent_with_dispatch(tables.pop())
4503 elif len(tables) > 1 and not self._allow_multiple_tables:
4504 table = columns[0].table
4505 others = [c for c in columns[1:] if c.table is not table]
4506 if others:
4507 # black could not format this inline
4508 other_str = ", ".join("'%s'" % c for c in others)
4509 raise exc.ArgumentError(
4510 f"Column(s) {other_str} "
4511 f"are not part of table '{table.description}'."
4512 )
4514 @util.ro_memoized_property
4515 def columns(self) -> ReadOnlyColumnCollection[str, Column[Any]]:
4516 return self._columns.as_readonly()
4518 @util.ro_memoized_property
4519 def c(self) -> ReadOnlyColumnCollection[str, Column[Any]]:
4520 return self._columns.as_readonly()
4522 def _col_expressions(
4523 self, parent: Union[Table, Column[Any]]
4524 ) -> List[Optional[Column[Any]]]:
4525 if isinstance(parent, Column):
4526 result: List[Optional[Column[Any]]] = [
4527 c for c in self._pending_colargs if isinstance(c, Column)
4528 ]
4529 assert len(result) == len(self._pending_colargs)
4530 return result
4531 else:
4532 try:
4533 return [
4534 parent.c[col] if isinstance(col, str) else col
4535 for col in self._pending_colargs
4536 ]
4537 except KeyError as ke:
4538 raise exc.ConstraintColumnNotFoundError(
4539 f"Can't create {self.__class__.__name__} "
4540 f"on table '{parent.description}': no column "
4541 f"named '{ke.args[0]}' is present."
4542 ) from ke
4544 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4545 assert isinstance(parent, (Table, Column))
4547 for col in self._col_expressions(parent):
4548 if col is not None:
4549 self._columns.add(col)
4552class ColumnCollectionConstraint(ColumnCollectionMixin, Constraint):
4553 """A constraint that proxies a ColumnCollection."""
4555 def __init__(
4556 self,
4557 *columns: _DDLColumnArgument,
4558 name: _ConstraintNameArgument = None,
4559 deferrable: Optional[bool] = None,
4560 initially: Optional[str] = None,
4561 info: Optional[_InfoType] = None,
4562 _autoattach: bool = True,
4563 _column_flag: bool = False,
4564 _gather_expressions: Optional[List[_DDLColumnArgument]] = None,
4565 **dialect_kw: Any,
4566 ) -> None:
4567 r"""
4568 :param \*columns:
4569 A sequence of column names or Column objects.
4571 :param name:
4572 Optional, the in-database name of this constraint.
4574 :param deferrable:
4575 Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
4576 issuing DDL for this constraint.
4578 :param initially:
4579 Optional string. If set, emit INITIALLY <value> when issuing DDL
4580 for this constraint.
4582 :param \**dialect_kw: other keyword arguments including
4583 dialect-specific arguments are propagated to the :class:`.Constraint`
4584 superclass.
4586 """
4587 Constraint.__init__(
4588 self,
4589 name=name,
4590 deferrable=deferrable,
4591 initially=initially,
4592 info=info,
4593 **dialect_kw,
4594 )
4595 ColumnCollectionMixin.__init__(
4596 self, *columns, _autoattach=_autoattach, _column_flag=_column_flag
4597 )
4599 columns: ReadOnlyColumnCollection[str, Column[Any]]
4600 """A :class:`_expression.ColumnCollection` representing the set of columns
4601 for this constraint.
4603 """
4605 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4606 assert isinstance(parent, (Column, Table))
4607 Constraint._set_parent(self, parent)
4608 ColumnCollectionMixin._set_parent(self, parent)
4610 def __contains__(self, x: Any) -> bool:
4611 return x in self._columns
4613 @util.deprecated(
4614 "1.4",
4615 "The :meth:`_schema.ColumnCollectionConstraint.copy` method "
4616 "is deprecated and will be removed in a future release.",
4617 )
4618 def copy(
4619 self,
4620 *,
4621 target_table: Optional[Table] = None,
4622 **kw: Any,
4623 ) -> ColumnCollectionConstraint:
4624 return self._copy(target_table=target_table, **kw)
4626 def _copy(
4627 self,
4628 *,
4629 target_table: Optional[Table] = None,
4630 **kw: Any,
4631 ) -> ColumnCollectionConstraint:
4632 # ticket #5276
4633 constraint_kwargs = {}
4634 for dialect_name in self.dialect_options:
4635 dialect_options = self.dialect_options[dialect_name]._non_defaults
4636 for (
4637 dialect_option_key,
4638 dialect_option_value,
4639 ) in dialect_options.items():
4640 constraint_kwargs[dialect_name + "_" + dialect_option_key] = (
4641 dialect_option_value
4642 )
4644 assert isinstance(self.parent, Table)
4645 c = self.__class__(
4646 name=self.name,
4647 deferrable=self.deferrable,
4648 initially=self.initially,
4649 *[
4650 _copy_expression(expr, self.parent, target_table)
4651 for expr in self._columns
4652 ],
4653 comment=self.comment,
4654 **constraint_kwargs,
4655 )
4656 return self._schema_item_copy(c)
4658 def contains_column(self, col: Column[Any]) -> bool:
4659 """Return True if this constraint contains the given column.
4661 Note that this object also contains an attribute ``.columns``
4662 which is a :class:`_expression.ColumnCollection` of
4663 :class:`_schema.Column` objects.
4665 """
4667 return self._columns.contains_column(col)
4669 def __iter__(self) -> Iterator[Column[Any]]:
4670 return iter(self._columns)
4672 def __len__(self) -> int:
4673 return len(self._columns)
4676class CheckConstraint(ColumnCollectionConstraint):
4677 """A table- or column-level CHECK constraint.
4679 Can be included in the definition of a Table or Column.
4680 """
4682 _allow_multiple_tables = True
4684 __visit_name__ = "table_or_column_check_constraint"
4686 @_document_text_coercion(
4687 "sqltext",
4688 ":class:`.CheckConstraint`",
4689 ":paramref:`.CheckConstraint.sqltext`",
4690 )
4691 def __init__(
4692 self,
4693 sqltext: _TextCoercedExpressionArgument[Any],
4694 name: _ConstraintNameArgument = None,
4695 deferrable: Optional[bool] = None,
4696 initially: Optional[str] = None,
4697 table: Optional[Table] = None,
4698 info: Optional[_InfoType] = None,
4699 _create_rule: Optional[Any] = None,
4700 _autoattach: bool = True,
4701 _type_bound: bool = False,
4702 **dialect_kw: Any,
4703 ) -> None:
4704 r"""Construct a CHECK constraint.
4706 :param sqltext:
4707 A string containing the constraint definition, which will be used
4708 verbatim, or a SQL expression construct. If given as a string,
4709 the object is converted to a :func:`_expression.text` object.
4710 If the textual
4711 string includes a colon character, escape this using a backslash::
4713 CheckConstraint(r"foo ~ E'a(?\:b|c)d")
4715 :param name:
4716 Optional, the in-database name of the constraint.
4718 :param deferrable:
4719 Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
4720 issuing DDL for this constraint.
4722 :param initially:
4723 Optional string. If set, emit INITIALLY <value> when issuing DDL
4724 for this constraint.
4726 :param info: Optional data dictionary which will be populated into the
4727 :attr:`.SchemaItem.info` attribute of this object.
4729 """
4731 self.sqltext = coercions.expect(roles.DDLExpressionRole, sqltext)
4732 columns: List[Column[Any]] = []
4733 visitors.traverse(self.sqltext, {}, {"column": columns.append})
4735 super().__init__(
4736 name=name,
4737 deferrable=deferrable,
4738 initially=initially,
4739 _create_rule=_create_rule,
4740 info=info,
4741 _type_bound=_type_bound,
4742 _autoattach=_autoattach,
4743 *columns,
4744 **dialect_kw,
4745 )
4746 if table is not None:
4747 self._set_parent_with_dispatch(table)
4749 @property
4750 def is_column_level(self) -> bool:
4751 return not isinstance(self.parent, Table)
4753 @util.deprecated(
4754 "1.4",
4755 "The :meth:`_schema.CheckConstraint.copy` method is deprecated "
4756 "and will be removed in a future release.",
4757 )
4758 def copy(
4759 self, *, target_table: Optional[Table] = None, **kw: Any
4760 ) -> CheckConstraint:
4761 return self._copy(target_table=target_table, **kw)
4763 def _copy(
4764 self, *, target_table: Optional[Table] = None, **kw: Any
4765 ) -> CheckConstraint:
4766 if target_table is not None:
4767 # note that target_table is None for the copy process of
4768 # a column-bound CheckConstraint, so this path is not reached
4769 # in that case.
4770 sqltext = _copy_expression(self.sqltext, self.table, target_table)
4771 else:
4772 sqltext = self.sqltext
4773 c = CheckConstraint(
4774 sqltext,
4775 name=self.name,
4776 initially=self.initially,
4777 deferrable=self.deferrable,
4778 _create_rule=self._create_rule,
4779 table=target_table,
4780 comment=self.comment,
4781 _autoattach=False,
4782 _type_bound=self._type_bound,
4783 )
4784 return self._schema_item_copy(c)
4787class ForeignKeyConstraint(ColumnCollectionConstraint):
4788 """A table-level FOREIGN KEY constraint.
4790 Defines a single column or composite FOREIGN KEY ... REFERENCES
4791 constraint. For a no-frills, single column foreign key, adding a
4792 :class:`_schema.ForeignKey` to the definition of a :class:`_schema.Column`
4793 is a
4794 shorthand equivalent for an unnamed, single column
4795 :class:`_schema.ForeignKeyConstraint`.
4797 Examples of foreign key configuration are in :ref:`metadata_foreignkeys`.
4799 """
4801 __visit_name__ = "foreign_key_constraint"
4803 def __init__(
4804 self,
4805 columns: _typing_Sequence[_DDLColumnArgument],
4806 refcolumns: _typing_Sequence[_DDLColumnReferenceArgument],
4807 name: _ConstraintNameArgument = None,
4808 onupdate: Optional[str] = None,
4809 ondelete: Optional[str] = None,
4810 deferrable: Optional[bool] = None,
4811 initially: Optional[str] = None,
4812 use_alter: bool = False,
4813 link_to_name: bool = False,
4814 match: Optional[str] = None,
4815 table: Optional[Table] = None,
4816 info: Optional[_InfoType] = None,
4817 comment: Optional[str] = None,
4818 **dialect_kw: Any,
4819 ) -> None:
4820 r"""Construct a composite-capable FOREIGN KEY.
4822 :param columns: A sequence of local column names. The named columns
4823 must be defined and present in the parent Table. The names should
4824 match the ``key`` given to each column (defaults to the name) unless
4825 ``link_to_name`` is True.
4827 :param refcolumns: A sequence of foreign column names or Column
4828 objects. The columns must all be located within the same Table.
4830 :param name: Optional, the in-database name of the key.
4832 :param onupdate: Optional string. If set, emit ON UPDATE <value> when
4833 issuing DDL for this constraint. Typical values include CASCADE,
4834 DELETE and RESTRICT.
4836 .. seealso::
4838 :ref:`on_update_on_delete`
4840 :param ondelete: Optional string. If set, emit ON DELETE <value> when
4841 issuing DDL for this constraint. Typical values include CASCADE,
4842 SET NULL and RESTRICT. Some dialects may allow for additional
4843 syntaxes.
4845 .. seealso::
4847 :ref:`on_update_on_delete`
4849 :param deferrable: Optional bool. If set, emit DEFERRABLE or NOT
4850 DEFERRABLE when issuing DDL for this constraint.
4852 :param initially: Optional string. If set, emit INITIALLY <value> when
4853 issuing DDL for this constraint.
4855 :param link_to_name: if True, the string name given in ``column`` is
4856 the rendered name of the referenced column, not its locally assigned
4857 ``key``.
4859 :param use_alter: If True, do not emit the DDL for this constraint as
4860 part of the CREATE TABLE definition. Instead, generate it via an
4861 ALTER TABLE statement issued after the full collection of tables
4862 have been created, and drop it via an ALTER TABLE statement before
4863 the full collection of tables are dropped.
4865 The use of :paramref:`_schema.ForeignKeyConstraint.use_alter` is
4866 particularly geared towards the case where two or more tables
4867 are established within a mutually-dependent foreign key constraint
4868 relationship; however, the :meth:`_schema.MetaData.create_all` and
4869 :meth:`_schema.MetaData.drop_all`
4870 methods will perform this resolution
4871 automatically, so the flag is normally not needed.
4873 .. seealso::
4875 :ref:`use_alter`
4877 :param match: Optional string. If set, emit MATCH <value> when issuing
4878 DDL for this constraint. Typical values include SIMPLE, PARTIAL
4879 and FULL.
4881 :param info: Optional data dictionary which will be populated into the
4882 :attr:`.SchemaItem.info` attribute of this object.
4884 :param comment: Optional string that will render an SQL comment on
4885 foreign key constraint creation.
4887 .. versionadded:: 2.0
4889 :param \**dialect_kw: Additional keyword arguments are dialect
4890 specific, and passed in the form ``<dialectname>_<argname>``. See
4891 the documentation regarding an individual dialect at
4892 :ref:`dialect_toplevel` for detail on documented arguments.
4894 """
4896 Constraint.__init__(
4897 self,
4898 name=name,
4899 deferrable=deferrable,
4900 initially=initially,
4901 info=info,
4902 comment=comment,
4903 **dialect_kw,
4904 )
4905 self.onupdate = onupdate
4906 self.ondelete = ondelete
4907 self.link_to_name = link_to_name
4908 self.use_alter = use_alter
4909 self.match = match
4911 if len(set(columns)) != len(refcolumns):
4912 if len(set(columns)) != len(columns):
4913 # e.g. FOREIGN KEY (a, a) REFERENCES r (b, c)
4914 raise exc.ArgumentError(
4915 "ForeignKeyConstraint with duplicate source column "
4916 "references are not supported."
4917 )
4918 else:
4919 # e.g. FOREIGN KEY (a) REFERENCES r (b, c)
4920 # paraphrasing
4921 # https://www.postgresql.org/docs/current/static/ddl-constraints.html
4922 raise exc.ArgumentError(
4923 "ForeignKeyConstraint number "
4924 "of constrained columns must match the number of "
4925 "referenced columns."
4926 )
4928 # standalone ForeignKeyConstraint - create
4929 # associated ForeignKey objects which will be applied to hosted
4930 # Column objects (in col.foreign_keys), either now or when attached
4931 # to the Table for string-specified names
4932 self.elements = [
4933 ForeignKey(
4934 refcol,
4935 _constraint=self,
4936 name=self.name,
4937 onupdate=self.onupdate,
4938 ondelete=self.ondelete,
4939 use_alter=self.use_alter,
4940 link_to_name=self.link_to_name,
4941 match=self.match,
4942 deferrable=self.deferrable,
4943 initially=self.initially,
4944 **self.dialect_kwargs,
4945 )
4946 for refcol in refcolumns
4947 ]
4949 ColumnCollectionMixin.__init__(self, *columns)
4950 if table is not None:
4951 if hasattr(self, "parent"):
4952 assert table is self.parent
4953 self._set_parent_with_dispatch(table)
4955 def _append_element(self, column: Column[Any], fk: ForeignKey) -> None:
4956 self._columns.add(column)
4957 self.elements.append(fk)
4959 columns: ReadOnlyColumnCollection[str, Column[Any]]
4960 """A :class:`_expression.ColumnCollection` representing the set of columns
4961 for this constraint.
4963 """
4965 elements: List[ForeignKey]
4966 """A sequence of :class:`_schema.ForeignKey` objects.
4968 Each :class:`_schema.ForeignKey`
4969 represents a single referring column/referred
4970 column pair.
4972 This collection is intended to be read-only.
4974 """
4976 @property
4977 def _elements(self) -> util.OrderedDict[str, ForeignKey]:
4978 # legacy - provide a dictionary view of (column_key, fk)
4979 return util.OrderedDict(zip(self.column_keys, self.elements))
4981 @property
4982 def _referred_schema(self) -> Optional[str]:
4983 for elem in self.elements:
4984 return elem._referred_schema
4985 else:
4986 return None
4988 @property
4989 def referred_table(self) -> Table:
4990 """The :class:`_schema.Table` object to which this
4991 :class:`_schema.ForeignKeyConstraint` references.
4993 This is a dynamically calculated attribute which may not be available
4994 if the constraint and/or parent table is not yet associated with
4995 a metadata collection that contains the referred table.
4997 """
4998 return self.elements[0].column.table
5000 def _validate_dest_table(self, table: Table) -> None:
5001 table_keys = {
5002 elem._table_key_within_construction() for elem in self.elements
5003 }
5004 if None not in table_keys and len(table_keys) > 1:
5005 elem0, elem1 = sorted(table_keys)[0:2]
5006 raise exc.ArgumentError(
5007 f"ForeignKeyConstraint on "
5008 f"{table.fullname}({self._col_description}) refers to "
5009 f"multiple remote tables: {elem0} and {elem1}"
5010 )
5012 @property
5013 def column_keys(self) -> _typing_Sequence[str]:
5014 """Return a list of string keys representing the local
5015 columns in this :class:`_schema.ForeignKeyConstraint`.
5017 This list is either the original string arguments sent
5018 to the constructor of the :class:`_schema.ForeignKeyConstraint`,
5019 or if the constraint has been initialized with :class:`_schema.Column`
5020 objects, is the string ``.key`` of each element.
5022 """
5023 if hasattr(self, "parent"):
5024 return self._columns.keys()
5025 else:
5026 return [
5027 col.key if isinstance(col, ColumnElement) else str(col)
5028 for col in self._pending_colargs
5029 ]
5031 @property
5032 def _col_description(self) -> str:
5033 return ", ".join(self.column_keys)
5035 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
5036 table = parent
5037 assert isinstance(table, Table)
5038 Constraint._set_parent(self, table)
5040 ColumnCollectionConstraint._set_parent(self, table)
5042 for col, fk in zip(self._columns, self.elements):
5043 if not hasattr(fk, "parent") or fk.parent is not col:
5044 fk._set_parent_with_dispatch(col)
5046 self._validate_dest_table(table)
5048 @util.deprecated(
5049 "1.4",
5050 "The :meth:`_schema.ForeignKeyConstraint.copy` method is deprecated "
5051 "and will be removed in a future release.",
5052 )
5053 def copy(
5054 self,
5055 *,
5056 schema: Optional[str] = None,
5057 target_table: Optional[Table] = None,
5058 **kw: Any,
5059 ) -> ForeignKeyConstraint:
5060 return self._copy(schema=schema, target_table=target_table, **kw)
5062 def _copy(
5063 self,
5064 *,
5065 schema: Optional[str] = None,
5066 target_table: Optional[Table] = None,
5067 **kw: Any,
5068 ) -> ForeignKeyConstraint:
5069 fkc = ForeignKeyConstraint(
5070 [x.parent.key for x in self.elements],
5071 [
5072 x._get_colspec(
5073 schema=schema,
5074 table_name=(
5075 target_table.name
5076 if target_table is not None
5077 and x._table_key_within_construction()
5078 == x.parent.table.key
5079 else None
5080 ),
5081 _is_copy=True,
5082 )
5083 for x in self.elements
5084 ],
5085 name=self.name,
5086 onupdate=self.onupdate,
5087 ondelete=self.ondelete,
5088 use_alter=self.use_alter,
5089 deferrable=self.deferrable,
5090 initially=self.initially,
5091 link_to_name=self.link_to_name,
5092 match=self.match,
5093 comment=self.comment,
5094 )
5095 for self_fk, other_fk in zip(self.elements, fkc.elements):
5096 self_fk._schema_item_copy(other_fk)
5097 return self._schema_item_copy(fkc)
5100class PrimaryKeyConstraint(ColumnCollectionConstraint):
5101 """A table-level PRIMARY KEY constraint.
5103 The :class:`.PrimaryKeyConstraint` object is present automatically
5104 on any :class:`_schema.Table` object; it is assigned a set of
5105 :class:`_schema.Column` objects corresponding to those marked with
5106 the :paramref:`_schema.Column.primary_key` flag::
5108 >>> my_table = Table(
5109 ... "mytable",
5110 ... metadata,
5111 ... Column("id", Integer, primary_key=True),
5112 ... Column("version_id", Integer, primary_key=True),
5113 ... Column("data", String(50)),
5114 ... )
5115 >>> my_table.primary_key
5116 PrimaryKeyConstraint(
5117 Column('id', Integer(), table=<mytable>,
5118 primary_key=True, nullable=False),
5119 Column('version_id', Integer(), table=<mytable>,
5120 primary_key=True, nullable=False)
5121 )
5123 The primary key of a :class:`_schema.Table` can also be specified by using
5124 a :class:`.PrimaryKeyConstraint` object explicitly; in this mode of usage,
5125 the "name" of the constraint can also be specified, as well as other
5126 options which may be recognized by dialects::
5128 my_table = Table(
5129 "mytable",
5130 metadata,
5131 Column("id", Integer),
5132 Column("version_id", Integer),
5133 Column("data", String(50)),
5134 PrimaryKeyConstraint("id", "version_id", name="mytable_pk"),
5135 )
5137 The two styles of column-specification should generally not be mixed.
5138 An warning is emitted if the columns present in the
5139 :class:`.PrimaryKeyConstraint`
5140 don't match the columns that were marked as ``primary_key=True``, if both
5141 are present; in this case, the columns are taken strictly from the
5142 :class:`.PrimaryKeyConstraint` declaration, and those columns otherwise
5143 marked as ``primary_key=True`` are ignored. This behavior is intended to
5144 be backwards compatible with previous behavior.
5146 For the use case where specific options are to be specified on the
5147 :class:`.PrimaryKeyConstraint`, but the usual style of using
5148 ``primary_key=True`` flags is still desirable, an empty
5149 :class:`.PrimaryKeyConstraint` may be specified, which will take on the
5150 primary key column collection from the :class:`_schema.Table` based on the
5151 flags::
5153 my_table = Table(
5154 "mytable",
5155 metadata,
5156 Column("id", Integer, primary_key=True),
5157 Column("version_id", Integer, primary_key=True),
5158 Column("data", String(50)),
5159 PrimaryKeyConstraint(name="mytable_pk", mssql_clustered=True),
5160 )
5162 """
5164 __visit_name__ = "primary_key_constraint"
5166 def __init__(
5167 self,
5168 *columns: _DDLColumnArgument,
5169 name: Optional[str] = None,
5170 deferrable: Optional[bool] = None,
5171 initially: Optional[str] = None,
5172 info: Optional[_InfoType] = None,
5173 _implicit_generated: bool = False,
5174 **dialect_kw: Any,
5175 ) -> None:
5176 self._implicit_generated = _implicit_generated
5177 super().__init__(
5178 *columns,
5179 name=name,
5180 deferrable=deferrable,
5181 initially=initially,
5182 info=info,
5183 **dialect_kw,
5184 )
5186 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
5187 table = parent
5188 assert isinstance(table, Table)
5189 super()._set_parent(table)
5191 if table.primary_key is not self:
5192 table.constraints.discard(table.primary_key)
5193 table.primary_key = self # type: ignore
5194 table.constraints.add(self)
5196 table_pks = [c for c in table.c if c.primary_key]
5197 if (
5198 self._columns
5199 and table_pks
5200 and set(table_pks) != set(self._columns)
5201 ):
5202 # black could not format these inline
5203 table_pk_str = ", ".join("'%s'" % c.name for c in table_pks)
5204 col_str = ", ".join("'%s'" % c.name for c in self._columns)
5206 util.warn(
5207 f"Table '{table.name}' specifies columns "
5208 f"{table_pk_str} as "
5209 f"primary_key=True, "
5210 f"not matching locally specified columns {col_str}; "
5211 f"setting the "
5212 f"current primary key columns to "
5213 f"{col_str}. "
5214 f"This warning "
5215 f"may become an exception in a future release"
5216 )
5217 table_pks[:] = []
5219 for c in self._columns:
5220 c.primary_key = True
5221 if c._user_defined_nullable is NULL_UNSPECIFIED:
5222 c.nullable = False
5223 if table_pks:
5224 self._columns.extend(table_pks)
5226 def _reload(self, columns: Iterable[Column[Any]]) -> None:
5227 """repopulate this :class:`.PrimaryKeyConstraint` given
5228 a set of columns.
5230 Existing columns in the table that are marked as primary_key=True
5231 are maintained.
5233 Also fires a new event.
5235 This is basically like putting a whole new
5236 :class:`.PrimaryKeyConstraint` object on the parent
5237 :class:`_schema.Table` object without actually replacing the object.
5239 The ordering of the given list of columns is also maintained; these
5240 columns will be appended to the list of columns after any which
5241 are already present.
5243 """
5244 # set the primary key flag on new columns.
5245 # note any existing PK cols on the table also have their
5246 # flag still set.
5247 for col in columns:
5248 col.primary_key = True
5250 self._columns.extend(columns)
5252 PrimaryKeyConstraint._autoincrement_column._reset(self) # type: ignore
5253 self._set_parent_with_dispatch(self.table)
5255 def _replace(self, col: Column[Any]) -> None:
5256 PrimaryKeyConstraint._autoincrement_column._reset(self) # type: ignore
5257 self._columns.replace(col)
5259 self.dispatch._sa_event_column_added_to_pk_constraint(self, col)
5261 @property
5262 def columns_autoinc_first(self) -> List[Column[Any]]:
5263 autoinc = self._autoincrement_column
5265 if autoinc is not None:
5266 return [autoinc] + [c for c in self._columns if c is not autoinc]
5267 else:
5268 return list(self._columns)
5270 @util.ro_memoized_property
5271 def _autoincrement_column(self) -> Optional[Column[int]]:
5272 def _validate_autoinc(col: Column[Any], autoinc_true: bool) -> bool:
5273 if col.type._type_affinity is not None and issubclass(
5274 col.type._type_affinity, type_api.NUMERICTYPE._type_affinity
5275 ):
5276 scale = col.type.scale # type: ignore[attr-defined]
5277 if scale != 0 and autoinc_true:
5278 raise exc.ArgumentError(
5279 f"Column type {col.type} with non-zero scale "
5280 f"{scale} on column '{col}' is not "
5281 f"compatible with autoincrement=True"
5282 )
5283 elif not autoinc_true:
5284 return False
5285 elif col.type._type_affinity is None or not issubclass(
5286 col.type._type_affinity, type_api.INTEGERTYPE._type_affinity
5287 ):
5288 if autoinc_true:
5289 raise exc.ArgumentError(
5290 f"Column type {col.type} on column '{col}' is not "
5291 f"compatible with autoincrement=True"
5292 )
5293 else:
5294 return False
5295 elif (
5296 col.default is not None
5297 and not isinstance(col.default, Sequence)
5298 and not autoinc_true
5299 ):
5300 return False
5301 elif (
5302 col.server_default is not None
5303 and not isinstance(col.server_default, Identity)
5304 and not autoinc_true
5305 ):
5306 return False
5307 elif col.foreign_keys and col.autoincrement not in (
5308 True,
5309 "ignore_fk",
5310 ):
5311 return False
5312 return True
5314 if len(self._columns) == 1:
5315 col = list(self._columns)[0]
5317 if col.autoincrement is True:
5318 _validate_autoinc(col, True)
5319 return col
5320 elif col.autoincrement in (
5321 "auto",
5322 "ignore_fk",
5323 ) and _validate_autoinc(col, False):
5324 return col
5325 else:
5326 return None
5328 else:
5329 autoinc = None
5330 for col in self._columns:
5331 if col.autoincrement is True:
5332 _validate_autoinc(col, True)
5333 if autoinc is not None:
5334 raise exc.ArgumentError(
5335 f"Only one Column may be marked "
5336 f"autoincrement=True, found both "
5337 f"{col.name} and {autoinc.name}."
5338 )
5339 else:
5340 autoinc = col
5342 return autoinc
5345class UniqueConstraint(ColumnCollectionConstraint):
5346 """A table-level UNIQUE constraint.
5348 Defines a single column or composite UNIQUE constraint. For a no-frills,
5349 single column constraint, adding ``unique=True`` to the ``Column``
5350 definition is a shorthand equivalent for an unnamed, single column
5351 UniqueConstraint.
5352 """
5354 __visit_name__ = "unique_constraint"
5357class Index(
5358 DialectKWArgs, ColumnCollectionMixin, HasConditionalDDL, SchemaItem
5359):
5360 """A table-level INDEX.
5362 Defines a composite (one or more column) INDEX.
5364 E.g.::
5366 sometable = Table(
5367 "sometable",
5368 metadata,
5369 Column("name", String(50)),
5370 Column("address", String(100)),
5371 )
5373 Index("some_index", sometable.c.name)
5375 For a no-frills, single column index, adding
5376 :class:`_schema.Column` also supports ``index=True``::
5378 sometable = Table(
5379 "sometable", metadata, Column("name", String(50), index=True)
5380 )
5382 For a composite index, multiple columns can be specified::
5384 Index("some_index", sometable.c.name, sometable.c.address)
5386 Functional indexes are supported as well, typically by using the
5387 :data:`.func` construct in conjunction with table-bound
5388 :class:`_schema.Column` objects::
5390 Index("some_index", func.lower(sometable.c.name))
5392 An :class:`.Index` can also be manually associated with a
5393 :class:`_schema.Table`,
5394 either through inline declaration or using
5395 :meth:`_schema.Table.append_constraint`. When this approach is used,
5396 the names
5397 of the indexed columns can be specified as strings::
5399 Table(
5400 "sometable",
5401 metadata,
5402 Column("name", String(50)),
5403 Column("address", String(100)),
5404 Index("some_index", "name", "address"),
5405 )
5407 To support functional or expression-based indexes in this form, the
5408 :func:`_expression.text` construct may be used::
5410 from sqlalchemy import text
5412 Table(
5413 "sometable",
5414 metadata,
5415 Column("name", String(50)),
5416 Column("address", String(100)),
5417 Index("some_index", text("lower(name)")),
5418 )
5420 .. seealso::
5422 :ref:`schema_indexes` - General information on :class:`.Index`.
5424 :ref:`postgresql_indexes` - PostgreSQL-specific options available for
5425 the :class:`.Index` construct.
5427 :ref:`mysql_indexes` - MySQL-specific options available for the
5428 :class:`.Index` construct.
5430 :ref:`mssql_indexes` - MSSQL-specific options available for the
5431 :class:`.Index` construct.
5433 """
5435 __visit_name__ = "index"
5437 table: Optional[Table]
5438 expressions: _typing_Sequence[Union[str, ColumnElement[Any]]]
5439 _table_bound_expressions: _typing_Sequence[ColumnElement[Any]]
5441 def __init__(
5442 self,
5443 name: Optional[str],
5444 *expressions: _DDLColumnArgument,
5445 unique: bool = False,
5446 quote: Optional[bool] = None,
5447 info: Optional[_InfoType] = None,
5448 _table: Optional[Table] = None,
5449 _column_flag: bool = False,
5450 **dialect_kw: Any,
5451 ) -> None:
5452 r"""Construct an index object.
5454 :param name:
5455 The name of the index
5457 :param \*expressions:
5458 Column expressions to include in the index. The expressions
5459 are normally instances of :class:`_schema.Column`, but may also
5460 be arbitrary SQL expressions which ultimately refer to a
5461 :class:`_schema.Column`.
5463 :param unique=False:
5464 Keyword only argument; if True, create a unique index.
5466 :param quote=None:
5467 Keyword only argument; whether to apply quoting to the name of
5468 the index. Works in the same manner as that of
5469 :paramref:`_schema.Column.quote`.
5471 :param info=None: Optional data dictionary which will be populated
5472 into the :attr:`.SchemaItem.info` attribute of this object.
5474 :param \**dialect_kw: Additional keyword arguments not mentioned above
5475 are dialect specific, and passed in the form
5476 ``<dialectname>_<argname>``. See the documentation regarding an
5477 individual dialect at :ref:`dialect_toplevel` for detail on
5478 documented arguments.
5480 """
5481 self.table = table = None
5483 self.name = quoted_name.construct(name, quote)
5484 self.unique = unique
5485 if info is not None:
5486 self.info = info
5488 # TODO: consider "table" argument being public, but for
5489 # the purpose of the fix here, it starts as private.
5490 if _table is not None:
5491 table = _table
5493 self._validate_dialect_kwargs(dialect_kw)
5495 self.expressions = []
5496 # will call _set_parent() if table-bound column
5497 # objects are present
5498 ColumnCollectionMixin.__init__(
5499 self,
5500 *expressions,
5501 _column_flag=_column_flag,
5502 _gather_expressions=self.expressions,
5503 )
5504 if table is not None:
5505 self._set_parent(table)
5507 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
5508 table = parent
5509 assert isinstance(table, Table)
5510 ColumnCollectionMixin._set_parent(self, table)
5512 if self.table is not None and table is not self.table:
5513 raise exc.ArgumentError(
5514 f"Index '{self.name}' is against table "
5515 f"'{self.table.description}', and "
5516 f"cannot be associated with table '{table.description}'."
5517 )
5518 self.table = table
5519 table.indexes.add(self)
5521 expressions = self.expressions
5522 col_expressions = self._col_expressions(table)
5523 assert len(expressions) == len(col_expressions)
5525 exprs = []
5526 for expr, colexpr in zip(expressions, col_expressions):
5527 if isinstance(expr, ClauseElement):
5528 exprs.append(expr)
5529 elif colexpr is not None:
5530 exprs.append(colexpr)
5531 else:
5532 assert False
5533 self.expressions = self._table_bound_expressions = exprs
5535 def create(
5536 self,
5537 bind: _CreateDropBind,
5538 checkfirst: Union[bool, CheckFirst] = CheckFirst.NONE,
5539 ) -> None:
5540 """Issue a ``CREATE`` statement for this
5541 :class:`.Index`, using the given
5542 :class:`.Connection` or :class:`.Engine`` for connectivity.
5544 .. seealso::
5546 :meth:`_schema.MetaData.create_all`.
5548 """
5549 bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
5551 def drop(
5552 self,
5553 bind: _CreateDropBind,
5554 checkfirst: Union[bool, CheckFirst] = CheckFirst.NONE,
5555 ) -> None:
5556 """Issue a ``DROP`` statement for this
5557 :class:`.Index`, using the given
5558 :class:`.Connection` or :class:`.Engine` for connectivity.
5560 .. seealso::
5562 :meth:`_schema.MetaData.drop_all`.
5564 """
5565 bind._run_ddl_visitor(ddl.SchemaDropper, self, checkfirst=checkfirst)
5567 def __repr__(self) -> str:
5568 exprs: _typing_Sequence[Any] # noqa: F842
5570 return "Index(%s)" % (
5571 ", ".join(
5572 [repr(self.name)]
5573 + [repr(e) for e in self.expressions]
5574 + (self.unique and ["unique=True"] or [])
5575 )
5576 )
5579_NamingSchemaCallable = Callable[[Constraint, Table], str]
5580_NamingSchemaDirective = Union[str, _NamingSchemaCallable]
5583class _NamingSchemaTD(TypedDict, total=False):
5584 fk: _NamingSchemaDirective
5585 pk: _NamingSchemaDirective
5586 ix: _NamingSchemaDirective
5587 ck: _NamingSchemaDirective
5588 uq: _NamingSchemaDirective
5591_NamingSchemaParameter = Union[
5592 # it seems like the TypedDict here is useful for pylance typeahead,
5593 # and not much else
5594 _NamingSchemaTD,
5595 # there is no form that allows Union[Type[Any], str] to work in all
5596 # cases, including breaking out Mapping[] entries for each combination
5597 # even, therefore keys must be `Any` (see #10264)
5598 Mapping[Any, _NamingSchemaDirective],
5599]
5602DEFAULT_NAMING_CONVENTION: _NamingSchemaParameter = util.immutabledict(
5603 {"ix": "ix_%(column_0_label)s"}
5604)
5607class MetaData(HasSchemaAttr):
5608 """A collection of :class:`_schema.Table`
5609 objects and their associated schema
5610 constructs.
5612 Holds a collection of :class:`_schema.Table` objects as well as
5613 an optional binding to an :class:`_engine.Engine` or
5614 :class:`_engine.Connection`. If bound, the :class:`_schema.Table` objects
5615 in the collection and their columns may participate in implicit SQL
5616 execution.
5618 The :class:`_schema.Table` objects themselves are stored in the
5619 :attr:`_schema.MetaData.tables` dictionary.
5621 :class:`_schema.MetaData` is a thread-safe object for read operations.
5622 Construction of new tables within a single :class:`_schema.MetaData`
5623 object,
5624 either explicitly or via reflection, may not be completely thread-safe.
5626 .. seealso::
5628 :ref:`metadata_describing` - Introduction to database metadata
5630 """
5632 __visit_name__ = "metadata"
5634 def __init__(
5635 self,
5636 schema: Optional[str] = None,
5637 quote_schema: Optional[bool] = None,
5638 naming_convention: Optional[_NamingSchemaParameter] = None,
5639 info: Optional[_InfoType] = None,
5640 ) -> None:
5641 """Create a new MetaData object.
5643 :param schema:
5644 The default schema to use for the :class:`_schema.Table`,
5645 :class:`.Sequence`, and potentially other objects associated with
5646 this :class:`_schema.MetaData`. Defaults to ``None``.
5648 .. seealso::
5650 :ref:`schema_metadata_schema_name` - details on how the
5651 :paramref:`_schema.MetaData.schema` parameter is used.
5653 :paramref:`_schema.Table.schema`
5655 :paramref:`.Sequence.schema`
5657 :param quote_schema:
5658 Sets the ``quote_schema`` flag for those :class:`_schema.Table`,
5659 :class:`.Sequence`, and other objects which make usage of the
5660 local ``schema`` name.
5662 :param info: Optional data dictionary which will be populated into the
5663 :attr:`.SchemaItem.info` attribute of this object.
5665 :param naming_convention: a dictionary referring to values which
5666 will establish default naming conventions for :class:`.Constraint`
5667 and :class:`.Index` objects, for those objects which are not given
5668 a name explicitly.
5670 The keys of this dictionary may be:
5672 * a constraint or Index class, e.g. the :class:`.UniqueConstraint`,
5673 :class:`_schema.ForeignKeyConstraint` class, the :class:`.Index`
5674 class
5676 * a string mnemonic for one of the known constraint classes;
5677 ``"fk"``, ``"pk"``, ``"ix"``, ``"ck"``, ``"uq"`` for foreign key,
5678 primary key, index, check, and unique constraint, respectively.
5680 * the string name of a user-defined "token" that can be used
5681 to define new naming tokens.
5683 The values associated with each "constraint class" or "constraint
5684 mnemonic" key are string naming templates, such as
5685 ``"uq_%(table_name)s_%(column_0_name)s"``,
5686 which describe how the name should be composed. The values
5687 associated with user-defined "token" keys should be callables of the
5688 form ``fn(constraint, table)``, which accepts the constraint/index
5689 object and :class:`_schema.Table` as arguments, returning a string
5690 result.
5692 The built-in names are as follows, some of which may only be
5693 available for certain types of constraint:
5695 * ``%(table_name)s`` - the name of the :class:`_schema.Table`
5696 object
5697 associated with the constraint.
5699 * ``%(referred_table_name)s`` - the name of the
5700 :class:`_schema.Table`
5701 object associated with the referencing target of a
5702 :class:`_schema.ForeignKeyConstraint`.
5704 * ``%(column_0_name)s`` - the name of the :class:`_schema.Column`
5705 at
5706 index position "0" within the constraint.
5708 * ``%(column_0N_name)s`` - the name of all :class:`_schema.Column`
5709 objects in order within the constraint, joined without a
5710 separator.
5712 * ``%(column_0_N_name)s`` - the name of all
5713 :class:`_schema.Column`
5714 objects in order within the constraint, joined with an
5715 underscore as a separator.
5717 * ``%(column_0_label)s``, ``%(column_0N_label)s``,
5718 ``%(column_0_N_label)s`` - the label of either the zeroth
5719 :class:`_schema.Column` or all :class:`.Columns`, separated with
5720 or without an underscore
5722 * ``%(column_0_key)s``, ``%(column_0N_key)s``,
5723 ``%(column_0_N_key)s`` - the key of either the zeroth
5724 :class:`_schema.Column` or all :class:`.Columns`, separated with
5725 or without an underscore
5727 * ``%(referred_column_0_name)s``, ``%(referred_column_0N_name)s``
5728 ``%(referred_column_0_N_name)s``, ``%(referred_column_0_key)s``,
5729 ``%(referred_column_0N_key)s``, ... column tokens which
5730 render the names/keys/labels of columns that are referenced
5731 by a :class:`_schema.ForeignKeyConstraint`.
5733 * ``%(constraint_name)s`` - a special key that refers to the
5734 existing name given to the constraint. When this key is
5735 present, the :class:`.Constraint` object's existing name will be
5736 replaced with one that is composed from template string that
5737 uses this token. When this token is present, it is required that
5738 the :class:`.Constraint` is given an explicit name ahead of time.
5740 * user-defined: any additional token may be implemented by passing
5741 it along with a ``fn(constraint, table)`` callable to the
5742 naming_convention dictionary.
5744 .. seealso::
5746 :ref:`constraint_naming_conventions` - for detailed usage
5747 examples.
5749 """
5750 if schema is not None and not isinstance(schema, str):
5751 raise exc.ArgumentError(
5752 "expected schema argument to be a string, "
5753 f"got {type(schema)}."
5754 )
5755 self.tables = util.FacadeDict()
5756 self.schema = quoted_name.construct(schema, quote_schema)
5757 self.naming_convention = (
5758 naming_convention
5759 if naming_convention
5760 else DEFAULT_NAMING_CONVENTION
5761 )
5762 if info:
5763 self.info = info
5764 self._schemas: Set[str] = set()
5765 self._sequences: Dict[str, Sequence] = {}
5766 self._fk_memos: Dict[Tuple[str, Optional[str]], List[ForeignKey]] = (
5767 collections.defaultdict(list)
5768 )
5769 self._objects: Set[Union[HasSchemaAttr, SchemaType]] = set()
5771 tables: util.FacadeDict[str, Table]
5772 """A dictionary of :class:`_schema.Table`
5773 objects keyed to their name or "table key".
5775 The exact key is that determined by the :attr:`_schema.Table.key`
5776 attribute;
5777 for a table with no :attr:`_schema.Table.schema` attribute,
5778 this is the same
5779 as :attr:`_schema.Table.name`. For a table with a schema,
5780 it is typically of the
5781 form ``schemaname.tablename``.
5783 .. seealso::
5785 :attr:`_schema.MetaData.sorted_tables`
5787 """
5789 def __repr__(self) -> str:
5790 return "MetaData()"
5792 def __contains__(self, table_or_key: Union[str, Table]) -> bool:
5793 if not isinstance(table_or_key, str):
5794 table_or_key = table_or_key.key
5795 return table_or_key in self.tables
5797 def _add_table(
5798 self, name: str, schema: Optional[str], table: Table
5799 ) -> None:
5800 key = _get_table_key(name, schema)
5801 self.tables._insert_item(key, table)
5802 if schema:
5803 self._schemas.add(schema)
5805 def _remove_table(self, name: str, schema: Optional[str]) -> None:
5806 key = _get_table_key(name, schema)
5807 removed = dict.pop(self.tables, key, None)
5808 if removed is not None:
5809 for fk in removed.foreign_keys:
5810 fk._remove_from_metadata(self)
5811 if self._schemas:
5812 self._schemas = {
5813 t.schema for t in self.tables.values() if t.schema is not None
5814 }
5816 def __getstate__(self) -> Dict[str, Any]:
5817 return {
5818 "tables": self.tables,
5819 "schema": self.schema,
5820 "schemas": self._schemas,
5821 "sequences": self._sequences,
5822 "fk_memos": self._fk_memos,
5823 "naming_convention": self.naming_convention,
5824 "objects": self._objects,
5825 }
5827 def __setstate__(self, state: Dict[str, Any]) -> None:
5828 self.tables = state["tables"]
5829 self.schema = state["schema"]
5830 self.naming_convention = state["naming_convention"]
5831 self._sequences = state["sequences"]
5832 self._schemas = state["schemas"]
5833 self._fk_memos = state["fk_memos"]
5834 self._objects = state.get("objects", set())
5836 def clear(self) -> None:
5837 """Clear all objects from this MetaData."""
5839 dict.clear(self.tables)
5840 self._schemas.clear()
5841 self._fk_memos.clear()
5842 self._sequences.clear()
5843 self._objects.clear()
5845 def remove(self, table: Table) -> None:
5846 """Remove the given Table object from this MetaData."""
5848 self._remove_table(table.name, table.schema)
5850 @property
5851 def sorted_tables(self) -> List[Table]:
5852 """Returns a list of :class:`_schema.Table` objects sorted in order of
5853 foreign key dependency.
5855 The sorting will place :class:`_schema.Table`
5856 objects that have dependencies
5857 first, before the dependencies themselves, representing the
5858 order in which they can be created. To get the order in which
5859 the tables would be dropped, use the ``reversed()`` Python built-in.
5861 .. warning::
5863 The :attr:`.MetaData.sorted_tables` attribute cannot by itself
5864 accommodate automatic resolution of dependency cycles between
5865 tables, which are usually caused by mutually dependent foreign key
5866 constraints. When these cycles are detected, the foreign keys
5867 of these tables are omitted from consideration in the sort.
5868 A warning is emitted when this condition occurs, which will be an
5869 exception raise in a future release. Tables which are not part
5870 of the cycle will still be returned in dependency order.
5872 To resolve these cycles, the
5873 :paramref:`_schema.ForeignKeyConstraint.use_alter` parameter may be
5874 applied to those constraints which create a cycle. Alternatively,
5875 the :func:`_schema.sort_tables_and_constraints` function will
5876 automatically return foreign key constraints in a separate
5877 collection when cycles are detected so that they may be applied
5878 to a schema separately.
5880 .. seealso::
5882 :func:`_schema.sort_tables`
5884 :func:`_schema.sort_tables_and_constraints`
5886 :attr:`_schema.MetaData.tables`
5888 :meth:`_reflection.Inspector.get_table_names`
5890 :meth:`_reflection.Inspector.get_sorted_table_and_fkc_names`
5893 """
5894 return ddl.sort_tables(
5895 sorted(self.tables.values(), key=lambda t: t.key) # type: ignore
5896 )
5898 # overload needed to work around mypy this mypy
5899 # https://github.com/python/mypy/issues/17093
5900 @overload
5901 def reflect(
5902 self,
5903 bind: Engine,
5904 schema: Optional[str] = ...,
5905 views: bool = ...,
5906 only: Union[
5907 _typing_Sequence[str], Callable[[str, MetaData], bool], None
5908 ] = ...,
5909 extend_existing: bool = ...,
5910 autoload_replace: bool = ...,
5911 resolve_fks: bool = ...,
5912 **dialect_kwargs: Any,
5913 ) -> None: ...
5915 @overload
5916 def reflect(
5917 self,
5918 bind: Connection,
5919 schema: Optional[str] = ...,
5920 views: bool = ...,
5921 only: Union[
5922 _typing_Sequence[str], Callable[[str, MetaData], bool], None
5923 ] = ...,
5924 extend_existing: bool = ...,
5925 autoload_replace: bool = ...,
5926 resolve_fks: bool = ...,
5927 **dialect_kwargs: Any,
5928 ) -> None: ...
5930 @util.preload_module("sqlalchemy.engine.reflection")
5931 def reflect(
5932 self,
5933 bind: Union[Engine, Connection],
5934 schema: Optional[str] = None,
5935 views: bool = False,
5936 only: Union[
5937 _typing_Sequence[str], Callable[[str, MetaData], bool], None
5938 ] = None,
5939 extend_existing: bool = False,
5940 autoload_replace: bool = True,
5941 resolve_fks: bool = True,
5942 **dialect_kwargs: Any,
5943 ) -> None:
5944 r"""Load all available table definitions from the database.
5946 Automatically creates ``Table`` entries in this ``MetaData`` for any
5947 table available in the database but not yet present in the
5948 ``MetaData``. May be called multiple times to pick up tables recently
5949 added to the database, however no special action is taken if a table
5950 in this ``MetaData`` no longer exists in the database.
5952 :param bind:
5953 A :class:`.Connection` or :class:`.Engine` used to access the
5954 database.
5956 :param schema:
5957 Optional, query and reflect tables from an alternate schema.
5958 If None, the schema associated with this :class:`_schema.MetaData`
5959 is used, if any.
5961 :param views:
5962 If True, also reflect views (materialized and plain).
5964 :param only:
5965 Optional. Load only a sub-set of available named tables. May be
5966 specified as a sequence of names or a callable.
5968 If a sequence of names is provided, only those tables will be
5969 reflected. An error is raised if a table is requested but not
5970 available. Named tables already present in this ``MetaData`` are
5971 ignored.
5973 If a callable is provided, it will be used as a boolean predicate to
5974 filter the list of potential table names. The callable is called
5975 with a table name and this ``MetaData`` instance as positional
5976 arguments and should return a true value for any table to reflect.
5978 :param extend_existing: Passed along to each :class:`_schema.Table` as
5979 :paramref:`_schema.Table.extend_existing`.
5981 :param autoload_replace: Passed along to each :class:`_schema.Table`
5982 as
5983 :paramref:`_schema.Table.autoload_replace`.
5985 :param resolve_fks: if True, reflect :class:`_schema.Table`
5986 objects linked
5987 to :class:`_schema.ForeignKey` objects located in each
5988 :class:`_schema.Table`.
5989 For :meth:`_schema.MetaData.reflect`,
5990 this has the effect of reflecting
5991 related tables that might otherwise not be in the list of tables
5992 being reflected, for example if the referenced table is in a
5993 different schema or is omitted via the
5994 :paramref:`.MetaData.reflect.only` parameter. When False,
5995 :class:`_schema.ForeignKey` objects are not followed to the
5996 :class:`_schema.Table`
5997 in which they link, however if the related table is also part of the
5998 list of tables that would be reflected in any case, the
5999 :class:`_schema.ForeignKey` object will still resolve to its related
6000 :class:`_schema.Table` after the :meth:`_schema.MetaData.reflect`
6001 operation is
6002 complete. Defaults to True.
6004 .. seealso::
6006 :paramref:`_schema.Table.resolve_fks`
6008 :param \**dialect_kwargs: Additional keyword arguments not mentioned
6009 above are dialect specific, and passed in the form
6010 ``<dialectname>_<argname>``. See the documentation regarding an
6011 individual dialect at :ref:`dialect_toplevel` for detail on
6012 documented arguments.
6014 .. seealso::
6016 :ref:`metadata_reflection_toplevel`
6018 :meth:`_events.DDLEvents.column_reflect` - Event used to customize
6019 the reflected columns. Usually used to generalize the types using
6020 :meth:`_types.TypeEngine.as_generic`
6022 :ref:`metadata_reflection_dbagnostic_types` - describes how to
6023 reflect tables using general types.
6025 """
6027 with inspection.inspect(bind)._inspection_context() as insp:
6028 reflect_opts: Any = {
6029 "autoload_with": insp,
6030 "extend_existing": extend_existing,
6031 "autoload_replace": autoload_replace,
6032 "resolve_fks": resolve_fks,
6033 "_extend_on": set(),
6034 }
6036 reflect_opts.update(dialect_kwargs)
6038 if schema is None:
6039 schema = self.schema
6041 if schema is not None:
6042 reflect_opts["schema"] = schema
6044 kind = util.preloaded.engine_reflection.ObjectKind.TABLE
6045 available: util.OrderedSet[str] = util.OrderedSet(
6046 insp.get_table_names(schema, **dialect_kwargs)
6047 )
6048 if views:
6049 kind = util.preloaded.engine_reflection.ObjectKind.ANY
6050 available.update(insp.get_view_names(schema, **dialect_kwargs))
6051 try:
6052 available.update(
6053 insp.get_materialized_view_names(
6054 schema, **dialect_kwargs
6055 )
6056 )
6057 except NotImplementedError:
6058 pass
6060 if schema is not None:
6061 available_w_schema: util.OrderedSet[str] = util.OrderedSet(
6062 [f"{schema}.{name}" for name in available]
6063 )
6064 else:
6065 available_w_schema = available
6067 current = set(self.tables)
6069 if only is None:
6070 load = [
6071 name
6072 for name, schname in zip(available, available_w_schema)
6073 if extend_existing or schname not in current
6074 ]
6075 elif callable(only):
6076 load = [
6077 name
6078 for name, schname in zip(available, available_w_schema)
6079 if (extend_existing or schname not in current)
6080 and only(name, self)
6081 ]
6082 else:
6083 missing = [name for name in only if name not in available]
6084 if missing:
6085 s = schema and (" schema '%s'" % schema) or ""
6086 missing_str = ", ".join(missing)
6087 raise exc.InvalidRequestError(
6088 f"Could not reflect: requested table(s) not available "
6089 f"in {bind.engine!r}{s}: ({missing_str})"
6090 )
6091 load = [
6092 name
6093 for name in only
6094 if extend_existing or name not in current
6095 ]
6096 # pass the available tables so the inspector can
6097 # choose to ignore the filter_names
6098 _reflect_info = insp._get_reflection_info(
6099 schema=schema,
6100 filter_names=load,
6101 available=available,
6102 kind=kind,
6103 scope=util.preloaded.engine_reflection.ObjectScope.ANY,
6104 **dialect_kwargs,
6105 )
6106 reflect_opts["_reflect_info"] = _reflect_info
6108 for name in load:
6109 try:
6110 Table(name, self, **reflect_opts)
6111 except exc.UnreflectableTableError as uerr:
6112 util.warn(f"Skipping table {name}: {uerr}")
6114 def create_all(
6115 self,
6116 bind: _CreateDropBind,
6117 tables: Optional[_typing_Sequence[Table]] = None,
6118 checkfirst: Union[bool, CheckFirst] = CheckFirst.ALL,
6119 ) -> None:
6120 """Create all tables stored in this metadata.
6122 Conditional by default, will not attempt to recreate tables already
6123 present in the target database.
6125 :param bind:
6126 A :class:`.Connection` or :class:`.Engine` used to access the
6127 database.
6129 :param tables:
6130 Optional list of ``Table`` objects, which is a subset of the total
6131 tables in the ``MetaData`` (others are ignored).
6133 :param checkfirst: A boolean value or instance of :class:`.CheckFirst`.
6134 Indicates which objects should be checked for within a separate pass
6135 before creating schema objects.
6137 """
6138 bind._run_ddl_visitor(
6139 ddl.SchemaGenerator, self, checkfirst=checkfirst, tables=tables
6140 )
6142 def drop_all(
6143 self,
6144 bind: _CreateDropBind,
6145 tables: Optional[_typing_Sequence[Table]] = None,
6146 checkfirst: Union[bool, CheckFirst] = CheckFirst.ALL,
6147 ) -> None:
6148 """Drop all tables stored in this metadata.
6150 Conditional by default, will not attempt to drop tables not present in
6151 the target database.
6153 :param bind:
6154 A :class:`.Connection` or :class:`.Engine` used to access the
6155 database.
6157 :param tables:
6158 Optional list of ``Table`` objects, which is a subset of the
6159 total tables in the ``MetaData`` (others are ignored).
6161 :param checkfirst: A boolean value or instance of :class:`.CheckFirst`.
6162 Indicates which objects should be checked for within a separate pass
6163 before dropping schema objects.
6165 """
6166 bind._run_ddl_visitor(
6167 ddl.SchemaDropper, self, checkfirst=checkfirst, tables=tables
6168 )
6170 @property
6171 def schemas(self) -> _typing_Sequence[str]:
6172 """A sequence of schema names that are present in this MetaData."""
6173 schemas = self._schemas
6174 if self.schema:
6175 schemas = schemas | {self.schema}
6176 return tuple(schemas)
6178 def get_schema_objects(
6179 self,
6180 kind: Type[_T],
6181 *,
6182 schema: Union[str, None, Literal[_NoArg.NO_ARG]] = _NoArg.NO_ARG,
6183 ) -> _typing_Sequence[_T]:
6184 """Return a sequence of schema objects of the given kind.
6186 This method can be used to return :class:`_sqltypes.Enum`,
6187 :class:`.Sequence`, etc. objects registered in this
6188 :class:`_schema.MetaData`.
6190 :param kind: a type that indicates what object to return, such as
6191 :class:`Enum` or :class:`Sequence`.
6192 :param schema: Optional, a schema name to filter the objects by. If
6193 not provided the default schema of the metadata is used.
6195 """
6197 if schema is _NoArg.NO_ARG:
6198 schema = self.schema
6199 return tuple(
6200 obj
6201 for obj in self._objects
6202 if isinstance(obj, kind) and obj.schema == schema
6203 )
6205 def get_schema_object_by_name(
6206 self,
6207 kind: Type[_T],
6208 name: str,
6209 *,
6210 schema: Union[str, None, Literal[_NoArg.NO_ARG]] = _NoArg.NO_ARG,
6211 ) -> Optional[_T]:
6212 """Return a schema objects of the given kind and name if found.
6214 This method can be used to return :class:`_sqltypes.Enum`,
6215 :class:`.Sequence`, etc. objects registered in this
6216 :class:`_schema.MetaData`.
6218 :param kind: a type that indicates what object to return, such as
6219 :class:`Enum` or :class:`Sequence`.
6220 :param name: the name of the object to return.
6221 :param schema: Optional, a schema name to filter the objects by. If
6222 not provided the default schema of the metadata is used.
6224 """
6226 for obj in self.get_schema_objects(kind, schema=schema):
6227 if getattr(obj, "name", None) == name:
6228 return obj
6229 return None
6231 def _register_object(self, obj: Union[HasSchemaAttr, SchemaType]) -> None:
6232 self._objects.add(obj)
6235class Computed(FetchedValue, SchemaItem):
6236 """Defines a generated column, i.e. "GENERATED ALWAYS AS" syntax.
6238 The :class:`.Computed` construct is an inline construct added to the
6239 argument list of a :class:`_schema.Column` object::
6241 from sqlalchemy import Computed
6243 Table(
6244 "square",
6245 metadata_obj,
6246 Column("side", Float, nullable=False),
6247 Column("area", Float, Computed("side * side")),
6248 )
6250 See the linked documentation below for complete details.
6252 .. seealso::
6254 :ref:`computed_ddl`
6256 """
6258 __visit_name__ = "computed_column"
6260 column: Optional[Column[Any]]
6262 @_document_text_coercion(
6263 "sqltext", ":class:`.Computed`", ":paramref:`.Computed.sqltext`"
6264 )
6265 def __init__(
6266 self, sqltext: _DDLColumnArgument, persisted: Optional[bool] = None
6267 ) -> None:
6268 """Construct a GENERATED ALWAYS AS DDL construct to accompany a
6269 :class:`_schema.Column`.
6271 :param sqltext:
6272 A string containing the column generation expression, which will be
6273 used verbatim, or a SQL expression construct, such as a
6274 :func:`_expression.text`
6275 object. If given as a string, the object is converted to a
6276 :func:`_expression.text` object.
6278 :param persisted:
6279 Optional, controls how this column should be persisted by the
6280 database. Possible values are:
6282 * ``None``, the default, it will use the default persistence
6283 defined by the database.
6284 * ``True``, will render ``GENERATED ALWAYS AS ... STORED``, or the
6285 equivalent for the target database if supported.
6286 * ``False``, will render ``GENERATED ALWAYS AS ... VIRTUAL``, or
6287 the equivalent for the target database if supported.
6289 Specifying ``True`` or ``False`` may raise an error when the DDL
6290 is emitted to the target database if the database does not support
6291 that persistence option. Leaving this parameter at its default
6292 of ``None`` is guaranteed to succeed for all databases that support
6293 ``GENERATED ALWAYS AS``.
6295 """
6296 self.sqltext = coercions.expect(roles.DDLExpressionRole, sqltext)
6297 self.persisted = persisted
6298 self.column = None
6300 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
6301 assert isinstance(parent, Column)
6303 if not isinstance(
6304 parent.server_default, (type(None), Computed)
6305 ) or not isinstance(parent.server_onupdate, (type(None), Computed)):
6306 raise exc.ArgumentError(
6307 "A generated column cannot specify a server_default or a "
6308 "server_onupdate argument"
6309 )
6310 self.column = parent
6311 parent.computed = self
6312 self.column.server_onupdate = self
6313 self.column.server_default = self
6315 def _as_for_update(self, for_update: bool) -> FetchedValue:
6316 return self
6318 @util.deprecated(
6319 "1.4",
6320 "The :meth:`_schema.Computed.copy` method is deprecated "
6321 "and will be removed in a future release.",
6322 )
6323 def copy(
6324 self, *, target_table: Optional[Table] = None, **kw: Any
6325 ) -> Computed:
6326 return self._copy(target_table=target_table, **kw)
6328 def _copy(
6329 self, *, target_table: Optional[Table] = None, **kw: Any
6330 ) -> Computed:
6331 sqltext = _copy_expression(
6332 self.sqltext,
6333 self.column.table if self.column is not None else None,
6334 target_table,
6335 )
6336 g = Computed(sqltext, persisted=self.persisted)
6338 return self._schema_item_copy(g)
6341class Identity(IdentityOptions, FetchedValue, SchemaItem):
6342 """Defines an identity column, i.e. "GENERATED { ALWAYS | BY DEFAULT }
6343 AS IDENTITY" syntax.
6345 The :class:`.Identity` construct is an inline construct added to the
6346 argument list of a :class:`_schema.Column` object::
6348 from sqlalchemy import Identity
6350 Table(
6351 "foo",
6352 metadata_obj,
6353 Column("id", Integer, Identity()),
6354 Column("description", Text),
6355 )
6357 See the linked documentation below for complete details.
6359 .. versionadded:: 1.4
6361 .. seealso::
6363 :ref:`identity_ddl`
6365 """
6367 __visit_name__ = "identity_column"
6369 is_identity = True
6371 @util.deprecated_params(
6372 order=(
6373 "2.1",
6374 "This parameter is supported only by Oracle Database, "
6375 "use ``oracle_order`` instead.",
6376 ),
6377 on_null=(
6378 "2.1",
6379 "This parameter is supported only by Oracle Database, "
6380 "use ``oracle_on_null`` instead.",
6381 ),
6382 )
6383 def __init__(
6384 self,
6385 always: Optional[bool] = False,
6386 on_null: Optional[bool] = None,
6387 start: Optional[int] = None,
6388 increment: Optional[int] = None,
6389 minvalue: Optional[int] = None,
6390 maxvalue: Optional[int] = None,
6391 nominvalue: Optional[bool] = None,
6392 nomaxvalue: Optional[bool] = None,
6393 cycle: Optional[bool] = None,
6394 cache: Optional[int] = None,
6395 order: Optional[bool] = None,
6396 **dialect_kw: Any,
6397 ) -> None:
6398 """Construct a GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY DDL
6399 construct to accompany a :class:`_schema.Column`.
6401 See the :class:`.Sequence` documentation for a complete description
6402 of most parameters.
6404 .. note::
6405 MSSQL supports this construct as the preferred alternative to
6406 generate an IDENTITY on a column, but it uses non standard
6407 syntax that only support :paramref:`_schema.Identity.start`
6408 and :paramref:`_schema.Identity.increment`.
6409 All other parameters are ignored.
6411 :param always:
6412 A boolean, that indicates the type of identity column.
6413 If ``False`` is specified, the default, then the user-specified
6414 value takes precedence.
6415 If ``True`` is specified, a user-specified value is not accepted (
6416 on some backends, like PostgreSQL, OVERRIDING SYSTEM VALUE, or
6417 similar, may be specified in an INSERT to override the sequence
6418 value).
6419 Some backends also have a default value for this parameter,
6420 ``None`` can be used to omit rendering this part in the DDL. It
6421 will be treated as ``False`` if a backend does not have a default
6422 value.
6424 :param on_null:
6425 Set to ``True`` to specify ON NULL in conjunction with a
6426 ``always=False`` identity column. This option is only supported on
6427 some backends, like Oracle Database.
6429 :param start: the starting index of the sequence.
6430 :param increment: the increment value of the sequence.
6431 :param minvalue: the minimum value of the sequence.
6432 :param maxvalue: the maximum value of the sequence.
6433 :param nominvalue: no minimum value of the sequence.
6434 :param nomaxvalue: no maximum value of the sequence.
6435 :param cycle: allows the sequence to wrap around when the maxvalue
6436 or minvalue has been reached.
6437 :param cache: optional integer value; number of future values in the
6438 sequence which are calculated in advance.
6439 :param order: optional boolean value; if true, renders the
6440 ORDER keyword.
6442 """
6443 self.dialect_options
6444 if on_null is not None:
6445 if "oracle_on_null" in dialect_kw:
6446 raise exc.ArgumentError(
6447 "Cannot specify both 'on_null' and 'oracle_on_null'. "
6448 "Plese use only 'oracle_on_null'."
6449 )
6450 dialect_kw["oracle_on_null"] = on_null
6452 IdentityOptions.__init__(
6453 self,
6454 start=start,
6455 increment=increment,
6456 minvalue=minvalue,
6457 maxvalue=maxvalue,
6458 nominvalue=nominvalue,
6459 nomaxvalue=nomaxvalue,
6460 cycle=cycle,
6461 cache=cache,
6462 order=order,
6463 **dialect_kw,
6464 )
6465 self.always = always
6466 self.column = None
6468 @property
6469 def on_null(self) -> Optional[bool]:
6470 """Alias of the ``dialect_kwargs`` ``'oracle_on_null'``.
6472 .. deprecated:: 2.1 The 'on_null' attribute is deprecated.
6473 """
6474 value: Optional[bool] = self.dialect_kwargs.get("oracle_on_null")
6475 return value
6477 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
6478 assert isinstance(parent, Column)
6479 if not isinstance(
6480 parent.server_default, (type(None), Identity)
6481 ) or not isinstance(parent.server_onupdate, type(None)):
6482 raise exc.ArgumentError(
6483 "A column with an Identity object cannot specify a "
6484 "server_default or a server_onupdate argument"
6485 )
6486 if parent.autoincrement is False:
6487 raise exc.ArgumentError(
6488 "A column with an Identity object cannot specify "
6489 "autoincrement=False"
6490 )
6491 self.column = parent
6493 parent.identity = self
6494 if parent._user_defined_nullable is NULL_UNSPECIFIED:
6495 parent.nullable = False
6497 parent.server_default = self
6499 def _as_for_update(self, for_update: bool) -> FetchedValue:
6500 return self
6502 @util.deprecated(
6503 "1.4",
6504 "The :meth:`_schema.Identity.copy` method is deprecated "
6505 "and will be removed in a future release.",
6506 )
6507 def copy(self, **kw: Any) -> Identity:
6508 return self._copy(**kw)
6510 def _copy(self, **kw: Any) -> Identity:
6511 i = Identity(**self._as_dict(), **self.dialect_kwargs)
6513 return self._schema_item_copy(i)
6515 def _as_dict(self) -> Dict[str, Any]:
6516 return {
6517 # always=None means something different than always=False
6518 "always": self.always,
6519 **super()._as_dict(),
6520 }