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 Iterable
44from typing import Iterator
45from typing import List
46from typing import Mapping
47from typing import NoReturn
48from typing import Optional
49from typing import overload
50from typing import Sequence as _typing_Sequence
51from typing import Set
52from typing import Tuple
53from typing import TYPE_CHECKING
54from typing import TypeVar
55from typing import Union
57from . import coercions
58from . import ddl
59from . import roles
60from . import type_api
61from . import visitors
62from .base import _DefaultDescriptionTuple
63from .base import _NoArg
64from .base import _NoneName
65from .base import _SentinelColumnCharacterization
66from .base import _SentinelDefaultCharacterization
67from .base import DedupeColumnCollection
68from .base import DialectKWArgs
69from .base import Executable
70from .base import SchemaEventTarget as SchemaEventTarget
71from .base import SchemaVisitable as SchemaVisitable
72from .coercions import _document_text_coercion
73from .elements import ClauseElement
74from .elements import ColumnClause
75from .elements import ColumnElement
76from .elements import quoted_name
77from .elements import TextClause
78from .selectable import TableClause
79from .type_api import to_instance
80from .visitors import ExternallyTraversible
81from .. import event
82from .. import exc
83from .. import inspection
84from .. import util
85from ..util import HasMemoized
86from ..util.typing import Final
87from ..util.typing import Literal
88from ..util.typing import Protocol
89from ..util.typing import Self
90from ..util.typing import TypedDict
91from ..util.typing import TypeGuard
93if typing.TYPE_CHECKING:
94 from ._typing import _AutoIncrementType
95 from ._typing import _CreateDropBind
96 from ._typing import _DDLColumnArgument
97 from ._typing import _InfoType
98 from ._typing import _TextCoercedExpressionArgument
99 from ._typing import _TypeEngineArgument
100 from .base import ColumnSet
101 from .base import ReadOnlyColumnCollection
102 from .compiler import DDLCompiler
103 from .elements import BindParameter
104 from .elements import KeyedColumnElement
105 from .functions import Function
106 from .type_api import TypeEngine
107 from .visitors import anon_map
108 from ..engine import Connection
109 from ..engine import Engine
110 from ..engine.interfaces import _CoreMultiExecuteParams
111 from ..engine.interfaces import CoreExecuteOptionsParameter
112 from ..engine.interfaces import ExecutionContext
113 from ..engine.reflection import _ReflectionInfo
114 from ..sql.selectable import FromClause
116_T = TypeVar("_T", bound="Any")
117_SI = TypeVar("_SI", bound="SchemaItem")
118_TAB = TypeVar("_TAB", bound="Table")
121_ConstraintNameArgument = Optional[Union[str, _NoneName]]
123_ServerDefaultArgument = Union[
124 "FetchedValue", str, TextClause, ColumnElement[Any]
125]
127_ServerOnUpdateArgument = _ServerDefaultArgument
130class SchemaConst(Enum):
131 RETAIN_SCHEMA = 1
132 """Symbol indicating that a :class:`_schema.Table`, :class:`.Sequence`
133 or in some cases a :class:`_schema.ForeignKey` object, in situations
134 where the object is being copied for a :meth:`.Table.to_metadata`
135 operation, should retain the schema name that it already has.
137 """
139 BLANK_SCHEMA = 2
140 """Symbol indicating that a :class:`_schema.Table` or :class:`.Sequence`
141 should have 'None' for its schema, even if the parent
142 :class:`_schema.MetaData` has specified a schema.
144 .. seealso::
146 :paramref:`_schema.MetaData.schema`
148 :paramref:`_schema.Table.schema`
150 :paramref:`.Sequence.schema`
152 """
154 NULL_UNSPECIFIED = 3
155 """Symbol indicating the "nullable" keyword was not passed to a Column.
157 This is used to distinguish between the use case of passing
158 ``nullable=None`` to a :class:`.Column`, which has special meaning
159 on some backends such as SQL Server.
161 """
164RETAIN_SCHEMA: Final[Literal[SchemaConst.RETAIN_SCHEMA]] = (
165 SchemaConst.RETAIN_SCHEMA
166)
167BLANK_SCHEMA: Final[Literal[SchemaConst.BLANK_SCHEMA]] = (
168 SchemaConst.BLANK_SCHEMA
169)
170NULL_UNSPECIFIED: Final[Literal[SchemaConst.NULL_UNSPECIFIED]] = (
171 SchemaConst.NULL_UNSPECIFIED
172)
175def _get_table_key(name: str, schema: Optional[str]) -> str:
176 if schema is None:
177 return name
178 else:
179 return schema + "." + name
182# this should really be in sql/util.py but we'd have to
183# break an import cycle
184def _copy_expression(
185 expression: ColumnElement[Any],
186 source_table: Optional[Table],
187 target_table: Optional[Table],
188) -> ColumnElement[Any]:
189 if source_table is None or target_table is None:
190 return expression
192 fixed_source_table = source_table
193 fixed_target_table = target_table
195 def replace(
196 element: ExternallyTraversible, **kw: Any
197 ) -> Optional[ExternallyTraversible]:
198 if (
199 isinstance(element, Column)
200 and element.table is fixed_source_table
201 and element.key in fixed_source_table.c
202 ):
203 return fixed_target_table.c[element.key]
204 else:
205 return None
207 return cast(
208 ColumnElement[Any],
209 visitors.replacement_traverse(expression, {}, replace),
210 )
213@inspection._self_inspects
214class SchemaItem(SchemaVisitable):
215 """Base class for items that define a database schema."""
217 __visit_name__ = "schema_item"
219 create_drop_stringify_dialect = "default"
221 def _init_items(self, *args: SchemaItem, **kw: Any) -> None:
222 """Initialize the list of child items for this SchemaItem."""
223 for item in args:
224 if item is not None:
225 try:
226 spwd = item._set_parent_with_dispatch
227 except AttributeError as err:
228 raise exc.ArgumentError(
229 "'SchemaItem' object, such as a 'Column' or a "
230 f"'Constraint' expected, got {item!r}"
231 ) from err
232 else:
233 spwd(self, **kw)
235 def __repr__(self) -> str:
236 return util.generic_repr(self, omit_kwarg=["info"])
238 @util.memoized_property
239 def info(self) -> _InfoType:
240 """Info dictionary associated with the object, allowing user-defined
241 data to be associated with this :class:`.SchemaItem`.
243 The dictionary is automatically generated when first accessed.
244 It can also be specified in the constructor of some objects,
245 such as :class:`_schema.Table` and :class:`_schema.Column`.
247 """
248 return {}
250 def _schema_item_copy(self, schema_item: _SI) -> _SI:
251 if "info" in self.__dict__:
252 schema_item.info = self.info.copy()
253 schema_item.dispatch._update(self.dispatch)
254 return schema_item
256 _use_schema_map = True
259class HasConditionalDDL:
260 """define a class that includes the :meth:`.HasConditionalDDL.ddl_if`
261 method, allowing for conditional rendering of DDL.
263 Currently applies to constraints and indexes.
265 .. versionadded:: 2.0
268 """
270 _ddl_if: Optional[ddl.DDLIf] = None
272 def ddl_if(
273 self,
274 dialect: Optional[str] = None,
275 callable_: Optional[ddl.DDLIfCallable] = None,
276 state: Optional[Any] = None,
277 ) -> Self:
278 r"""apply a conditional DDL rule to this schema item.
280 These rules work in a similar manner to the
281 :meth:`.ExecutableDDLElement.execute_if` callable, with the added
282 feature that the criteria may be checked within the DDL compilation
283 phase for a construct such as :class:`.CreateTable`.
284 :meth:`.HasConditionalDDL.ddl_if` currently applies towards the
285 :class:`.Index` construct as well as all :class:`.Constraint`
286 constructs.
288 :param dialect: string name of a dialect, or a tuple of string names
289 to indicate multiple dialect types.
291 :param callable\_: a callable that is constructed using the same form
292 as that described in
293 :paramref:`.ExecutableDDLElement.execute_if.callable_`.
295 :param state: any arbitrary object that will be passed to the
296 callable, if present.
298 .. versionadded:: 2.0
300 .. seealso::
302 :ref:`schema_ddl_ddl_if` - background and usage examples
305 """
306 self._ddl_if = ddl.DDLIf(dialect, callable_, state)
307 return self
310class HasSchemaAttr(SchemaItem):
311 """schema item that includes a top-level schema name"""
313 schema: Optional[str]
316class Table(
317 DialectKWArgs, HasSchemaAttr, TableClause, inspection.Inspectable["Table"]
318):
319 r"""Represent a table in a database.
321 e.g.::
323 mytable = Table(
324 "mytable",
325 metadata,
326 Column("mytable_id", Integer, primary_key=True),
327 Column("value", String(50)),
328 )
330 The :class:`_schema.Table`
331 object constructs a unique instance of itself based
332 on its name and optional schema name within the given
333 :class:`_schema.MetaData` object. Calling the :class:`_schema.Table`
334 constructor with the same name and same :class:`_schema.MetaData` argument
335 a second time will return the *same* :class:`_schema.Table`
336 object - in this way
337 the :class:`_schema.Table` constructor acts as a registry function.
339 .. seealso::
341 :ref:`metadata_describing` - Introduction to database metadata
343 """
345 __visit_name__ = "table"
347 if TYPE_CHECKING:
349 @util.ro_non_memoized_property
350 def primary_key(self) -> PrimaryKeyConstraint: ...
352 @util.ro_non_memoized_property
353 def foreign_keys(self) -> Set[ForeignKey]: ...
355 _columns: DedupeColumnCollection[Column[Any]]
357 _sentinel_column: Optional[Column[Any]]
359 constraints: Set[Constraint]
360 """A collection of all :class:`_schema.Constraint` objects associated with
361 this :class:`_schema.Table`.
363 Includes :class:`_schema.PrimaryKeyConstraint`,
364 :class:`_schema.ForeignKeyConstraint`, :class:`_schema.UniqueConstraint`,
365 :class:`_schema.CheckConstraint`. A separate collection
366 :attr:`_schema.Table.foreign_key_constraints` refers to the collection
367 of all :class:`_schema.ForeignKeyConstraint` objects, and the
368 :attr:`_schema.Table.primary_key` attribute refers to the single
369 :class:`_schema.PrimaryKeyConstraint` associated with the
370 :class:`_schema.Table`.
372 .. seealso::
374 :attr:`_schema.Table.constraints`
376 :attr:`_schema.Table.primary_key`
378 :attr:`_schema.Table.foreign_key_constraints`
380 :attr:`_schema.Table.indexes`
382 :class:`_reflection.Inspector`
385 """
387 indexes: Set[Index]
388 """A collection of all :class:`_schema.Index` objects associated with this
389 :class:`_schema.Table`.
391 .. seealso::
393 :meth:`_reflection.Inspector.get_indexes`
395 """
397 if TYPE_CHECKING:
399 @util.ro_non_memoized_property
400 def columns(self) -> ReadOnlyColumnCollection[str, Column[Any]]: ...
402 @util.ro_non_memoized_property
403 def exported_columns(
404 self,
405 ) -> ReadOnlyColumnCollection[str, Column[Any]]: ...
407 @util.ro_non_memoized_property
408 def c(self) -> ReadOnlyColumnCollection[str, Column[Any]]: ...
410 def _gen_cache_key(
411 self, anon_map: anon_map, bindparams: List[BindParameter[Any]]
412 ) -> Tuple[Any, ...]:
413 if self._annotations:
414 return (self,) + self._annotations_cache_key
415 else:
416 return (self,)
418 if not typing.TYPE_CHECKING:
419 # typing tools seem to be inconsistent in how they handle
420 # __new__, so suggest this pattern for classes that use
421 # __new__. apply typing to the __init__ method normally
422 @util.deprecated_params(
423 mustexist=(
424 "1.4",
425 "Deprecated alias of :paramref:`_schema.Table.must_exist`",
426 ),
427 )
428 def __new__(cls, *args: Any, **kw: Any) -> Any:
429 return cls._new(*args, **kw)
431 @classmethod
432 def _new(cls, *args: Any, **kw: Any) -> Any:
433 if not args and not kw:
434 # python3k pickle seems to call this
435 return object.__new__(cls)
437 try:
438 name, metadata, args = args[0], args[1], args[2:]
439 except IndexError:
440 raise TypeError(
441 "Table() takes at least two positional-only "
442 "arguments 'name' and 'metadata'"
443 )
445 schema = kw.get("schema", None)
446 if schema is None:
447 schema = metadata.schema
448 elif schema is BLANK_SCHEMA:
449 schema = None
450 keep_existing = kw.get("keep_existing", False)
451 extend_existing = kw.get("extend_existing", False)
453 if keep_existing and extend_existing:
454 msg = "keep_existing and extend_existing are mutually exclusive."
455 raise exc.ArgumentError(msg)
457 must_exist = kw.pop("must_exist", kw.pop("mustexist", False))
458 key = _get_table_key(name, schema)
459 if key in metadata.tables:
460 if not keep_existing and not extend_existing and bool(args):
461 raise exc.InvalidRequestError(
462 f"Table '{key}' is already defined for this MetaData "
463 "instance. Specify 'extend_existing=True' "
464 "to redefine "
465 "options and columns on an "
466 "existing Table object."
467 )
468 table = metadata.tables[key]
469 if extend_existing:
470 table._init_existing(*args, **kw)
471 return table
472 else:
473 if must_exist:
474 raise exc.InvalidRequestError(f"Table '{key}' not defined")
475 table = object.__new__(cls)
476 table.dispatch.before_parent_attach(table, metadata)
477 metadata._add_table(name, schema, table)
478 try:
479 table.__init__(name, metadata, *args, _no_init=False, **kw)
480 table.dispatch.after_parent_attach(table, metadata)
481 return table
482 except Exception:
483 with util.safe_reraise():
484 metadata._remove_table(name, schema)
486 def __init__(
487 self,
488 name: str,
489 metadata: MetaData,
490 *args: SchemaItem,
491 schema: Optional[Union[str, Literal[SchemaConst.BLANK_SCHEMA]]] = None,
492 quote: Optional[bool] = None,
493 quote_schema: Optional[bool] = None,
494 autoload_with: Optional[Union[Engine, Connection]] = None,
495 autoload_replace: bool = True,
496 keep_existing: bool = False,
497 extend_existing: bool = False,
498 resolve_fks: bool = True,
499 include_columns: Optional[Collection[str]] = None,
500 implicit_returning: bool = True,
501 comment: Optional[str] = None,
502 info: Optional[Dict[Any, Any]] = None,
503 listeners: Optional[
504 _typing_Sequence[Tuple[str, Callable[..., Any]]]
505 ] = None,
506 prefixes: Optional[_typing_Sequence[str]] = None,
507 # used internally in the metadata.reflect() process
508 _extend_on: Optional[Set[Table]] = None,
509 # used by __new__ to bypass __init__
510 _no_init: bool = True,
511 # dialect-specific keyword args
512 **kw: Any,
513 ) -> None:
514 r"""Constructor for :class:`_schema.Table`.
517 :param name: The name of this table as represented in the database.
519 The table name, along with the value of the ``schema`` parameter,
520 forms a key which uniquely identifies this :class:`_schema.Table`
521 within
522 the owning :class:`_schema.MetaData` collection.
523 Additional calls to :class:`_schema.Table` with the same name,
524 metadata,
525 and schema name will return the same :class:`_schema.Table` object.
527 Names which contain no upper case characters
528 will be treated as case insensitive names, and will not be quoted
529 unless they are a reserved word or contain special characters.
530 A name with any number of upper case characters is considered
531 to be case sensitive, and will be sent as quoted.
533 To enable unconditional quoting for the table name, specify the flag
534 ``quote=True`` to the constructor, or use the :class:`.quoted_name`
535 construct to specify the name.
537 :param metadata: a :class:`_schema.MetaData`
538 object which will contain this
539 table. The metadata is used as a point of association of this table
540 with other tables which are referenced via foreign key. It also
541 may be used to associate this table with a particular
542 :class:`.Connection` or :class:`.Engine`.
544 :param \*args: Additional positional arguments are used primarily
545 to add the list of :class:`_schema.Column`
546 objects contained within this
547 table. Similar to the style of a CREATE TABLE statement, other
548 :class:`.SchemaItem` constructs may be added here, including
549 :class:`.PrimaryKeyConstraint`, and
550 :class:`_schema.ForeignKeyConstraint`.
552 :param autoload_replace: Defaults to ``True``; when using
553 :paramref:`_schema.Table.autoload_with`
554 in conjunction with :paramref:`_schema.Table.extend_existing`,
555 indicates
556 that :class:`_schema.Column` objects present in the already-existing
557 :class:`_schema.Table`
558 object should be replaced with columns of the same
559 name retrieved from the autoload process. When ``False``, columns
560 already present under existing names will be omitted from the
561 reflection process.
563 Note that this setting does not impact :class:`_schema.Column` objects
564 specified programmatically within the call to :class:`_schema.Table`
565 that
566 also is autoloading; those :class:`_schema.Column` objects will always
567 replace existing columns of the same name when
568 :paramref:`_schema.Table.extend_existing` is ``True``.
570 .. seealso::
572 :paramref:`_schema.Table.autoload_with`
574 :paramref:`_schema.Table.extend_existing`
576 :param autoload_with: An :class:`_engine.Engine` or
577 :class:`_engine.Connection` object,
578 or a :class:`_reflection.Inspector` object as returned by
579 :func:`_sa.inspect`
580 against one, with which this :class:`_schema.Table`
581 object will be reflected.
582 When set to a non-None value, the autoload process will take place
583 for this table against the given engine or connection.
585 .. seealso::
587 :ref:`metadata_reflection_toplevel`
589 :meth:`_events.DDLEvents.column_reflect`
591 :ref:`metadata_reflection_dbagnostic_types`
593 :param extend_existing: When ``True``, indicates that if this
594 :class:`_schema.Table` is already present in the given
595 :class:`_schema.MetaData`,
596 apply further arguments within the constructor to the existing
597 :class:`_schema.Table`.
599 If :paramref:`_schema.Table.extend_existing` or
600 :paramref:`_schema.Table.keep_existing` are not set,
601 and the given name
602 of the new :class:`_schema.Table` refers to a :class:`_schema.Table`
603 that is
604 already present in the target :class:`_schema.MetaData` collection,
605 and
606 this :class:`_schema.Table`
607 specifies additional columns or other constructs
608 or flags that modify the table's state, an
609 error is raised. The purpose of these two mutually-exclusive flags
610 is to specify what action should be taken when a
611 :class:`_schema.Table`
612 is specified that matches an existing :class:`_schema.Table`,
613 yet specifies
614 additional constructs.
616 :paramref:`_schema.Table.extend_existing`
617 will also work in conjunction
618 with :paramref:`_schema.Table.autoload_with` to run a new reflection
619 operation against the database, even if a :class:`_schema.Table`
620 of the same name is already present in the target
621 :class:`_schema.MetaData`; newly reflected :class:`_schema.Column`
622 objects
623 and other options will be added into the state of the
624 :class:`_schema.Table`, potentially overwriting existing columns
625 and options of the same name.
627 As is always the case with :paramref:`_schema.Table.autoload_with`,
628 :class:`_schema.Column` objects can be specified in the same
629 :class:`_schema.Table`
630 constructor, which will take precedence. Below, the existing
631 table ``mytable`` will be augmented with :class:`_schema.Column`
632 objects
633 both reflected from the database, as well as the given
634 :class:`_schema.Column`
635 named "y"::
637 Table(
638 "mytable",
639 metadata,
640 Column("y", Integer),
641 extend_existing=True,
642 autoload_with=engine,
643 )
645 .. seealso::
647 :paramref:`_schema.Table.autoload_with`
649 :paramref:`_schema.Table.autoload_replace`
651 :paramref:`_schema.Table.keep_existing`
654 :param implicit_returning: True by default - indicates that
655 RETURNING can be used, typically by the ORM, in order to fetch
656 server-generated values such as primary key values and
657 server side defaults, on those backends which support RETURNING.
659 In modern SQLAlchemy there is generally no reason to alter this
660 setting, except for some backend specific cases
661 (see :ref:`mssql_triggers` in the SQL Server dialect documentation
662 for one such example).
664 :param include_columns: A list of strings indicating a subset of
665 columns to be loaded via the ``autoload`` operation; table columns who
666 aren't present in this list will not be represented on the resulting
667 ``Table`` object. Defaults to ``None`` which indicates all columns
668 should be reflected.
670 :param resolve_fks: Whether or not to reflect :class:`_schema.Table`
671 objects
672 related to this one via :class:`_schema.ForeignKey` objects, when
673 :paramref:`_schema.Table.autoload_with` is
674 specified. Defaults to True. Set to False to disable reflection of
675 related tables as :class:`_schema.ForeignKey`
676 objects are encountered; may be
677 used either to save on SQL calls or to avoid issues with related tables
678 that can't be accessed. Note that if a related table is already present
679 in the :class:`_schema.MetaData` collection, or becomes present later,
680 a
681 :class:`_schema.ForeignKey` object associated with this
682 :class:`_schema.Table` will
683 resolve to that table normally.
685 .. versionadded:: 1.3
687 .. seealso::
689 :paramref:`.MetaData.reflect.resolve_fks`
692 :param info: Optional data dictionary which will be populated into the
693 :attr:`.SchemaItem.info` attribute of this object.
695 :param keep_existing: When ``True``, indicates that if this Table
696 is already present in the given :class:`_schema.MetaData`, ignore
697 further arguments within the constructor to the existing
698 :class:`_schema.Table`, and return the :class:`_schema.Table`
699 object as
700 originally created. This is to allow a function that wishes
701 to define a new :class:`_schema.Table` on first call, but on
702 subsequent calls will return the same :class:`_schema.Table`,
703 without any of the declarations (particularly constraints)
704 being applied a second time.
706 If :paramref:`_schema.Table.extend_existing` or
707 :paramref:`_schema.Table.keep_existing` are not set,
708 and the given name
709 of the new :class:`_schema.Table` refers to a :class:`_schema.Table`
710 that is
711 already present in the target :class:`_schema.MetaData` collection,
712 and
713 this :class:`_schema.Table`
714 specifies additional columns or other constructs
715 or flags that modify the table's state, an
716 error is raised. The purpose of these two mutually-exclusive flags
717 is to specify what action should be taken when a
718 :class:`_schema.Table`
719 is specified that matches an existing :class:`_schema.Table`,
720 yet specifies
721 additional constructs.
723 .. seealso::
725 :paramref:`_schema.Table.extend_existing`
727 :param listeners: A list of tuples of the form ``(<eventname>, <fn>)``
728 which will be passed to :func:`.event.listen` upon construction.
729 This alternate hook to :func:`.event.listen` allows the establishment
730 of a listener function specific to this :class:`_schema.Table` before
731 the "autoload" process begins. Historically this has been intended
732 for use with the :meth:`.DDLEvents.column_reflect` event, however
733 note that this event hook may now be associated with the
734 :class:`_schema.MetaData` object directly::
736 def listen_for_reflect(table, column_info):
737 "handle the column reflection event"
738 # ...
741 t = Table(
742 "sometable",
743 autoload_with=engine,
744 listeners=[("column_reflect", listen_for_reflect)],
745 )
747 .. seealso::
749 :meth:`_events.DDLEvents.column_reflect`
751 :param must_exist: When ``True``, indicates that this Table must already
752 be present in the given :class:`_schema.MetaData` collection, else
753 an exception is raised.
755 :param prefixes:
756 A list of strings to insert after CREATE in the CREATE TABLE
757 statement. They will be separated by spaces.
759 :param quote: Force quoting of this table's name on or off, corresponding
760 to ``True`` or ``False``. When left at its default of ``None``,
761 the column identifier will be quoted according to whether the name is
762 case sensitive (identifiers with at least one upper case character are
763 treated as case sensitive), or if it's a reserved word. This flag
764 is only needed to force quoting of a reserved word which is not known
765 by the SQLAlchemy dialect.
767 .. note:: setting this flag to ``False`` will not provide
768 case-insensitive behavior for table reflection; table reflection
769 will always search for a mixed-case name in a case sensitive
770 fashion. Case insensitive names are specified in SQLAlchemy only
771 by stating the name with all lower case characters.
773 :param quote_schema: same as 'quote' but applies to the schema identifier.
775 :param schema: The schema name for this table, which is required if
776 the table resides in a schema other than the default selected schema
777 for the engine's database connection. Defaults to ``None``.
779 If the owning :class:`_schema.MetaData` of this :class:`_schema.Table`
780 specifies its
781 own :paramref:`_schema.MetaData.schema` parameter,
782 then that schema name will
783 be applied to this :class:`_schema.Table`
784 if the schema parameter here is set
785 to ``None``. To set a blank schema name on a :class:`_schema.Table`
786 that
787 would otherwise use the schema set on the owning
788 :class:`_schema.MetaData`,
789 specify the special symbol :attr:`.BLANK_SCHEMA`.
791 The quoting rules for the schema name are the same as those for the
792 ``name`` parameter, in that quoting is applied for reserved words or
793 case-sensitive names; to enable unconditional quoting for the schema
794 name, specify the flag ``quote_schema=True`` to the constructor, or use
795 the :class:`.quoted_name` construct to specify the name.
797 :param comment: Optional string that will render an SQL comment on table
798 creation.
800 .. versionadded:: 1.2 Added the :paramref:`_schema.Table.comment`
801 parameter
802 to :class:`_schema.Table`.
804 :param \**kw: Additional keyword arguments not mentioned above are
805 dialect specific, and passed in the form ``<dialectname>_<argname>``.
806 See the documentation regarding an individual dialect at
807 :ref:`dialect_toplevel` for detail on documented arguments.
809 """ # noqa: E501
810 if _no_init:
811 # don't run __init__ from __new__ by default;
812 # __new__ has a specific place that __init__ is called
813 return
815 super().__init__(quoted_name(name, quote))
816 self.metadata = metadata
818 if schema is None:
819 self.schema = metadata.schema
820 elif schema is BLANK_SCHEMA:
821 self.schema = None
822 else:
823 quote_schema = quote_schema
824 assert isinstance(schema, str)
825 self.schema = quoted_name(schema, quote_schema)
827 self._sentinel_column = None
829 self.indexes = set()
830 self.constraints = set()
831 PrimaryKeyConstraint(
832 _implicit_generated=True
833 )._set_parent_with_dispatch(self)
834 self.foreign_keys = set() # type: ignore
835 self._extra_dependencies: Set[Table] = set()
836 if self.schema is not None:
837 self.fullname = "%s.%s" % (self.schema, self.name)
838 else:
839 self.fullname = self.name
841 self.implicit_returning = implicit_returning
842 _reflect_info = kw.pop("_reflect_info", None)
844 self.comment = comment
846 if info is not None:
847 self.info = info
849 if listeners is not None:
850 for evt, fn in listeners:
851 event.listen(self, evt, fn)
853 self._prefixes = prefixes if prefixes else []
855 self._extra_kwargs(**kw)
857 # load column definitions from the database if 'autoload' is defined
858 # we do it after the table is in the singleton dictionary to support
859 # circular foreign keys
860 if autoload_with is not None:
861 self._autoload(
862 metadata,
863 autoload_with,
864 include_columns,
865 _extend_on=_extend_on,
866 _reflect_info=_reflect_info,
867 resolve_fks=resolve_fks,
868 )
870 # initialize all the column, etc. objects. done after reflection to
871 # allow user-overrides
873 self._init_items(
874 *args,
875 allow_replacements=extend_existing
876 or keep_existing
877 or autoload_with,
878 all_names={},
879 )
881 def _autoload(
882 self,
883 metadata: MetaData,
884 autoload_with: Union[Engine, Connection],
885 include_columns: Optional[Collection[str]],
886 exclude_columns: Collection[str] = (),
887 resolve_fks: bool = True,
888 _extend_on: Optional[Set[Table]] = None,
889 _reflect_info: _ReflectionInfo | None = None,
890 ) -> None:
891 insp = inspection.inspect(autoload_with)
892 with insp._inspection_context() as conn_insp:
893 conn_insp.reflect_table(
894 self,
895 include_columns,
896 exclude_columns,
897 resolve_fks,
898 _extend_on=_extend_on,
899 _reflect_info=_reflect_info,
900 )
902 @property
903 def _sorted_constraints(self) -> List[Constraint]:
904 """Return the set of constraints as a list, sorted by creation
905 order.
907 """
909 return sorted(self.constraints, key=lambda c: c._creation_order)
911 @property
912 def foreign_key_constraints(self) -> Set[ForeignKeyConstraint]:
913 """:class:`_schema.ForeignKeyConstraint` objects referred to by this
914 :class:`_schema.Table`.
916 This list is produced from the collection of
917 :class:`_schema.ForeignKey`
918 objects currently associated.
921 .. seealso::
923 :attr:`_schema.Table.constraints`
925 :attr:`_schema.Table.foreign_keys`
927 :attr:`_schema.Table.indexes`
929 """
930 return {
931 fkc.constraint
932 for fkc in self.foreign_keys
933 if fkc.constraint is not None
934 }
936 def _init_existing(self, *args: Any, **kwargs: Any) -> None:
937 autoload_with = kwargs.pop("autoload_with", None)
938 autoload = kwargs.pop("autoload", autoload_with is not None)
939 autoload_replace = kwargs.pop("autoload_replace", True)
940 schema = kwargs.pop("schema", None)
941 _extend_on = kwargs.pop("_extend_on", None)
942 _reflect_info = kwargs.pop("_reflect_info", None)
944 # these arguments are only used with _init()
945 extend_existing = kwargs.pop("extend_existing", False)
946 keep_existing = kwargs.pop("keep_existing", False)
948 assert extend_existing
949 assert not keep_existing
951 if schema and schema != self.schema:
952 raise exc.ArgumentError(
953 f"Can't change schema of existing table "
954 f"from '{self.schema}' to '{schema}'",
955 )
957 include_columns = kwargs.pop("include_columns", None)
958 if include_columns is not None:
959 for c in self.c:
960 if c.name not in include_columns:
961 self._columns.remove(c)
963 resolve_fks = kwargs.pop("resolve_fks", True)
965 for key in ("quote", "quote_schema"):
966 if key in kwargs:
967 raise exc.ArgumentError(
968 "Can't redefine 'quote' or 'quote_schema' arguments"
969 )
971 # update `self` with these kwargs, if provided
972 self.comment = kwargs.pop("comment", self.comment)
973 self.implicit_returning = kwargs.pop(
974 "implicit_returning", self.implicit_returning
975 )
976 self.info = kwargs.pop("info", self.info)
978 exclude_columns: _typing_Sequence[str]
980 if autoload:
981 if not autoload_replace:
982 # don't replace columns already present.
983 # we'd like to do this for constraints also however we don't
984 # have simple de-duping for unnamed constraints.
985 exclude_columns = [c.name for c in self.c]
986 else:
987 exclude_columns = ()
988 self._autoload(
989 self.metadata,
990 autoload_with,
991 include_columns,
992 exclude_columns,
993 resolve_fks,
994 _extend_on=_extend_on,
995 _reflect_info=_reflect_info,
996 )
998 all_names = {c.name: c for c in self.c}
999 self._extra_kwargs(**kwargs)
1000 self._init_items(*args, allow_replacements=True, all_names=all_names)
1002 def _extra_kwargs(self, **kwargs: Any) -> None:
1003 self._validate_dialect_kwargs(kwargs)
1005 def _init_collections(self) -> None:
1006 pass
1008 def _reset_exported(self) -> None:
1009 pass
1011 @util.ro_non_memoized_property
1012 def _autoincrement_column(self) -> Optional[Column[int]]:
1013 return self.primary_key._autoincrement_column
1015 @util.ro_memoized_property
1016 def _sentinel_column_characteristics(
1017 self,
1018 ) -> _SentinelColumnCharacterization:
1019 """determine a candidate column (or columns, in case of a client
1020 generated composite primary key) which can be used as an
1021 "insert sentinel" for an INSERT statement.
1023 The returned structure, :class:`_SentinelColumnCharacterization`,
1024 includes all the details needed by :class:`.Dialect` and
1025 :class:`.SQLCompiler` to determine if these column(s) can be used
1026 as an INSERT..RETURNING sentinel for a particular database
1027 dialect.
1029 .. versionadded:: 2.0.10
1031 """
1033 sentinel_is_explicit = False
1034 sentinel_is_autoinc = False
1035 the_sentinel: Optional[_typing_Sequence[Column[Any]]] = None
1037 # see if a column was explicitly marked "insert_sentinel=True".
1038 explicit_sentinel_col = self._sentinel_column
1040 if explicit_sentinel_col is not None:
1041 the_sentinel = (explicit_sentinel_col,)
1042 sentinel_is_explicit = True
1044 autoinc_col = self._autoincrement_column
1045 if sentinel_is_explicit and explicit_sentinel_col is autoinc_col:
1046 assert autoinc_col is not None
1047 sentinel_is_autoinc = True
1048 elif explicit_sentinel_col is None and autoinc_col is not None:
1049 the_sentinel = (autoinc_col,)
1050 sentinel_is_autoinc = True
1052 default_characterization = _SentinelDefaultCharacterization.UNKNOWN
1054 if the_sentinel:
1055 the_sentinel_zero = the_sentinel[0]
1056 if the_sentinel_zero.identity:
1057 if the_sentinel_zero.identity._increment_is_negative:
1058 if sentinel_is_explicit:
1059 raise exc.InvalidRequestError(
1060 "Can't use IDENTITY default with negative "
1061 "increment as an explicit sentinel column"
1062 )
1063 else:
1064 if sentinel_is_autoinc:
1065 autoinc_col = None
1066 sentinel_is_autoinc = False
1067 the_sentinel = None
1068 else:
1069 default_characterization = (
1070 _SentinelDefaultCharacterization.IDENTITY
1071 )
1072 elif (
1073 the_sentinel_zero.default is None
1074 and the_sentinel_zero.server_default is None
1075 ):
1076 if the_sentinel_zero.nullable:
1077 raise exc.InvalidRequestError(
1078 f"Column {the_sentinel_zero} has been marked as a "
1079 "sentinel "
1080 "column with no default generation function; it "
1081 "at least needs to be marked nullable=False assuming "
1082 "user-populated sentinel values will be used."
1083 )
1084 default_characterization = (
1085 _SentinelDefaultCharacterization.NONE
1086 )
1087 elif the_sentinel_zero.default is not None:
1088 if the_sentinel_zero.default.is_sentinel:
1089 default_characterization = (
1090 _SentinelDefaultCharacterization.SENTINEL_DEFAULT
1091 )
1092 elif default_is_sequence(the_sentinel_zero.default):
1093 if the_sentinel_zero.default._increment_is_negative:
1094 if sentinel_is_explicit:
1095 raise exc.InvalidRequestError(
1096 "Can't use SEQUENCE default with negative "
1097 "increment as an explicit sentinel column"
1098 )
1099 else:
1100 if sentinel_is_autoinc:
1101 autoinc_col = None
1102 sentinel_is_autoinc = False
1103 the_sentinel = None
1105 default_characterization = (
1106 _SentinelDefaultCharacterization.SEQUENCE
1107 )
1108 elif the_sentinel_zero.default.is_callable:
1109 default_characterization = (
1110 _SentinelDefaultCharacterization.CLIENTSIDE
1111 )
1112 elif the_sentinel_zero.server_default is not None:
1113 if sentinel_is_explicit:
1114 raise exc.InvalidRequestError(
1115 f"Column {the_sentinel[0]} can't be a sentinel column "
1116 "because it uses an explicit server side default "
1117 "that's not the Identity() default."
1118 )
1120 default_characterization = (
1121 _SentinelDefaultCharacterization.SERVERSIDE
1122 )
1124 if the_sentinel is None and self.primary_key:
1125 assert autoinc_col is None
1127 # determine for non-autoincrement pk if all elements are
1128 # client side
1129 for _pkc in self.primary_key:
1130 if _pkc.server_default is not None or (
1131 _pkc.default and not _pkc.default.is_callable
1132 ):
1133 break
1134 else:
1135 the_sentinel = tuple(self.primary_key)
1136 default_characterization = (
1137 _SentinelDefaultCharacterization.CLIENTSIDE
1138 )
1140 return _SentinelColumnCharacterization(
1141 the_sentinel,
1142 sentinel_is_explicit,
1143 sentinel_is_autoinc,
1144 default_characterization,
1145 )
1147 @property
1148 def autoincrement_column(self) -> Optional[Column[int]]:
1149 """Returns the :class:`.Column` object which currently represents
1150 the "auto increment" column, if any, else returns None.
1152 This is based on the rules for :class:`.Column` as defined by the
1153 :paramref:`.Column.autoincrement` parameter, which generally means the
1154 column within a single integer column primary key constraint that is
1155 not constrained by a foreign key. If the table does not have such
1156 a primary key constraint, then there's no "autoincrement" column.
1157 A :class:`.Table` may have only one column defined as the
1158 "autoincrement" column.
1160 .. versionadded:: 2.0.4
1162 .. seealso::
1164 :paramref:`.Column.autoincrement`
1166 """
1167 return self._autoincrement_column
1169 @property
1170 def key(self) -> str:
1171 """Return the 'key' for this :class:`_schema.Table`.
1173 This value is used as the dictionary key within the
1174 :attr:`_schema.MetaData.tables` collection. It is typically the same
1175 as that of :attr:`_schema.Table.name` for a table with no
1176 :attr:`_schema.Table.schema`
1177 set; otherwise it is typically of the form
1178 ``schemaname.tablename``.
1180 """
1181 return _get_table_key(self.name, self.schema)
1183 def __repr__(self) -> str:
1184 return "Table(%s)" % ", ".join(
1185 [repr(self.name)]
1186 + [repr(self.metadata)]
1187 + [repr(x) for x in self.columns]
1188 + ["%s=%s" % (k, repr(getattr(self, k))) for k in ["schema"]]
1189 )
1191 def __str__(self) -> str:
1192 return _get_table_key(self.description, self.schema)
1194 def add_is_dependent_on(self, table: Table) -> None:
1195 """Add a 'dependency' for this Table.
1197 This is another Table object which must be created
1198 first before this one can, or dropped after this one.
1200 Usually, dependencies between tables are determined via
1201 ForeignKey objects. However, for other situations that
1202 create dependencies outside of foreign keys (rules, inheriting),
1203 this method can manually establish such a link.
1205 """
1206 self._extra_dependencies.add(table)
1208 def append_column(
1209 self, column: ColumnClause[Any], replace_existing: bool = False
1210 ) -> None:
1211 """Append a :class:`_schema.Column` to this :class:`_schema.Table`.
1213 The "key" of the newly added :class:`_schema.Column`, i.e. the
1214 value of its ``.key`` attribute, will then be available
1215 in the ``.c`` collection of this :class:`_schema.Table`, and the
1216 column definition will be included in any CREATE TABLE, SELECT,
1217 UPDATE, etc. statements generated from this :class:`_schema.Table`
1218 construct.
1220 Note that this does **not** change the definition of the table
1221 as it exists within any underlying database, assuming that
1222 table has already been created in the database. Relational
1223 databases support the addition of columns to existing tables
1224 using the SQL ALTER command, which would need to be
1225 emitted for an already-existing table that doesn't contain
1226 the newly added column.
1228 :param replace_existing: When ``True``, allows replacing existing
1229 columns. When ``False``, the default, an warning will be raised
1230 if a column with the same ``.key`` already exists. A future
1231 version of sqlalchemy will instead rise a warning.
1233 .. versionadded:: 1.4.0
1234 """
1236 try:
1237 column._set_parent_with_dispatch(
1238 self,
1239 allow_replacements=replace_existing,
1240 all_names={c.name: c for c in self.c},
1241 )
1242 except exc.DuplicateColumnError as de:
1243 raise exc.DuplicateColumnError(
1244 f"{de.args[0]} Specify replace_existing=True to "
1245 "Table.append_column() to replace an "
1246 "existing column."
1247 ) from de
1249 def append_constraint(self, constraint: Union[Index, Constraint]) -> None:
1250 """Append a :class:`_schema.Constraint` to this
1251 :class:`_schema.Table`.
1253 This has the effect of the constraint being included in any
1254 future CREATE TABLE statement, assuming specific DDL creation
1255 events have not been associated with the given
1256 :class:`_schema.Constraint` object.
1258 Note that this does **not** produce the constraint within the
1259 relational database automatically, for a table that already exists
1260 in the database. To add a constraint to an
1261 existing relational database table, the SQL ALTER command must
1262 be used. SQLAlchemy also provides the
1263 :class:`.AddConstraint` construct which can produce this SQL when
1264 invoked as an executable clause.
1266 """
1268 constraint._set_parent_with_dispatch(self)
1270 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
1271 metadata = parent
1272 assert isinstance(metadata, MetaData)
1273 metadata._add_table(self.name, self.schema, self)
1274 self.metadata = metadata
1276 def create(self, bind: _CreateDropBind, checkfirst: bool = False) -> None:
1277 """Issue a ``CREATE`` statement for this
1278 :class:`_schema.Table`, using the given
1279 :class:`.Connection` or :class:`.Engine`
1280 for connectivity.
1282 .. seealso::
1284 :meth:`_schema.MetaData.create_all`.
1286 """
1288 bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
1290 def drop(self, bind: _CreateDropBind, checkfirst: bool = False) -> None:
1291 """Issue a ``DROP`` statement for this
1292 :class:`_schema.Table`, using the given
1293 :class:`.Connection` or :class:`.Engine` for connectivity.
1295 .. seealso::
1297 :meth:`_schema.MetaData.drop_all`.
1299 """
1300 bind._run_ddl_visitor(ddl.SchemaDropper, self, checkfirst=checkfirst)
1302 @util.deprecated(
1303 "1.4",
1304 ":meth:`_schema.Table.tometadata` is renamed to "
1305 ":meth:`_schema.Table.to_metadata`",
1306 )
1307 def tometadata(
1308 self,
1309 metadata: MetaData,
1310 schema: Union[str, Literal[SchemaConst.RETAIN_SCHEMA]] = RETAIN_SCHEMA,
1311 referred_schema_fn: Optional[
1312 Callable[
1313 [Table, Optional[str], ForeignKeyConstraint, Optional[str]],
1314 Optional[str],
1315 ]
1316 ] = None,
1317 name: Optional[str] = None,
1318 ) -> Table:
1319 """Return a copy of this :class:`_schema.Table`
1320 associated with a different
1321 :class:`_schema.MetaData`.
1323 See :meth:`_schema.Table.to_metadata` for a full description.
1325 """
1326 return self.to_metadata(
1327 metadata,
1328 schema=schema,
1329 referred_schema_fn=referred_schema_fn,
1330 name=name,
1331 )
1333 def to_metadata(
1334 self,
1335 metadata: MetaData,
1336 schema: Union[str, Literal[SchemaConst.RETAIN_SCHEMA]] = RETAIN_SCHEMA,
1337 referred_schema_fn: Optional[
1338 Callable[
1339 [Table, Optional[str], ForeignKeyConstraint, Optional[str]],
1340 Optional[str],
1341 ]
1342 ] = None,
1343 name: Optional[str] = None,
1344 ) -> Table:
1345 """Return a copy of this :class:`_schema.Table` associated with a
1346 different :class:`_schema.MetaData`.
1348 E.g.::
1350 m1 = MetaData()
1352 user = Table("user", m1, Column("id", Integer, primary_key=True))
1354 m2 = MetaData()
1355 user_copy = user.to_metadata(m2)
1357 .. versionchanged:: 1.4 The :meth:`_schema.Table.to_metadata` function
1358 was renamed from :meth:`_schema.Table.tometadata`.
1361 :param metadata: Target :class:`_schema.MetaData` object,
1362 into which the
1363 new :class:`_schema.Table` object will be created.
1365 :param schema: optional string name indicating the target schema.
1366 Defaults to the special symbol :attr:`.RETAIN_SCHEMA` which indicates
1367 that no change to the schema name should be made in the new
1368 :class:`_schema.Table`. If set to a string name, the new
1369 :class:`_schema.Table`
1370 will have this new name as the ``.schema``. If set to ``None``, the
1371 schema will be set to that of the schema set on the target
1372 :class:`_schema.MetaData`, which is typically ``None`` as well,
1373 unless
1374 set explicitly::
1376 m2 = MetaData(schema="newschema")
1378 # user_copy_one will have "newschema" as the schema name
1379 user_copy_one = user.to_metadata(m2, schema=None)
1381 m3 = MetaData() # schema defaults to None
1383 # user_copy_two will have None as the schema name
1384 user_copy_two = user.to_metadata(m3, schema=None)
1386 :param referred_schema_fn: optional callable which can be supplied
1387 in order to provide for the schema name that should be assigned
1388 to the referenced table of a :class:`_schema.ForeignKeyConstraint`.
1389 The callable accepts this parent :class:`_schema.Table`, the
1390 target schema that we are changing to, the
1391 :class:`_schema.ForeignKeyConstraint` object, and the existing
1392 "target schema" of that constraint. The function should return the
1393 string schema name that should be applied. To reset the schema
1394 to "none", return the symbol :data:`.BLANK_SCHEMA`. To effect no
1395 change, return ``None`` or :data:`.RETAIN_SCHEMA`.
1397 .. versionchanged:: 1.4.33 The ``referred_schema_fn`` function
1398 may return the :data:`.BLANK_SCHEMA` or :data:`.RETAIN_SCHEMA`
1399 symbols.
1401 E.g.::
1403 def referred_schema_fn(table, to_schema, constraint, referred_schema):
1404 if referred_schema == "base_tables":
1405 return referred_schema
1406 else:
1407 return to_schema
1410 new_table = table.to_metadata(
1411 m2, schema="alt_schema", referred_schema_fn=referred_schema_fn
1412 )
1414 :param name: optional string name indicating the target table name.
1415 If not specified or None, the table name is retained. This allows
1416 a :class:`_schema.Table` to be copied to the same
1417 :class:`_schema.MetaData` target
1418 with a new name.
1420 """ # noqa: E501
1421 if name is None:
1422 name = self.name
1424 actual_schema: Optional[str]
1426 if schema is RETAIN_SCHEMA:
1427 actual_schema = self.schema
1428 elif schema is None:
1429 actual_schema = metadata.schema
1430 else:
1431 actual_schema = schema
1432 key = _get_table_key(name, actual_schema)
1433 if key in metadata.tables:
1434 util.warn(
1435 f"Table '{self.description}' already exists within the given "
1436 "MetaData - not copying."
1437 )
1438 return metadata.tables[key]
1440 args = []
1441 for col in self.columns:
1442 args.append(col._copy(schema=actual_schema, _to_metadata=metadata))
1443 table = Table(
1444 name,
1445 metadata,
1446 schema=actual_schema,
1447 comment=self.comment,
1448 *args,
1449 **self.kwargs,
1450 )
1451 for const in self.constraints:
1452 if isinstance(const, ForeignKeyConstraint):
1453 referred_schema = const._referred_schema
1454 if referred_schema_fn:
1455 fk_constraint_schema = referred_schema_fn(
1456 self, actual_schema, const, referred_schema
1457 )
1458 else:
1459 fk_constraint_schema = (
1460 actual_schema
1461 if referred_schema == self.schema
1462 else None
1463 )
1464 table.append_constraint(
1465 const._copy(
1466 schema=fk_constraint_schema, target_table=table
1467 )
1468 )
1469 elif not const._type_bound:
1470 # skip unique constraints that would be generated
1471 # by the 'unique' flag on Column
1472 if const._column_flag:
1473 continue
1475 table.append_constraint(
1476 const._copy(schema=actual_schema, target_table=table)
1477 )
1478 for index in self.indexes:
1479 # skip indexes that would be generated
1480 # by the 'index' flag on Column
1481 if index._column_flag:
1482 continue
1483 Index(
1484 index.name,
1485 unique=index.unique,
1486 *[
1487 _copy_expression(expr, self, table)
1488 for expr in index._table_bound_expressions
1489 ],
1490 _table=table,
1491 **index.kwargs,
1492 )
1493 return self._schema_item_copy(table)
1496class Column(DialectKWArgs, SchemaItem, ColumnClause[_T]):
1497 """Represents a column in a database table."""
1499 __visit_name__ = "column"
1501 inherit_cache = True
1502 key: str
1504 server_default: Optional[FetchedValue]
1506 def __init__(
1507 self,
1508 __name_pos: Optional[
1509 Union[str, _TypeEngineArgument[_T], SchemaEventTarget]
1510 ] = None,
1511 __type_pos: Optional[
1512 Union[_TypeEngineArgument[_T], SchemaEventTarget]
1513 ] = None,
1514 *args: SchemaEventTarget,
1515 name: Optional[str] = None,
1516 type_: Optional[_TypeEngineArgument[_T]] = None,
1517 autoincrement: _AutoIncrementType = "auto",
1518 default: Optional[Any] = _NoArg.NO_ARG,
1519 insert_default: Optional[Any] = _NoArg.NO_ARG,
1520 doc: Optional[str] = None,
1521 key: Optional[str] = None,
1522 index: Optional[bool] = None,
1523 unique: Optional[bool] = None,
1524 info: Optional[_InfoType] = None,
1525 nullable: Optional[
1526 Union[bool, Literal[SchemaConst.NULL_UNSPECIFIED]]
1527 ] = SchemaConst.NULL_UNSPECIFIED,
1528 onupdate: Optional[Any] = None,
1529 primary_key: bool = False,
1530 server_default: Optional[_ServerDefaultArgument] = None,
1531 server_onupdate: Optional[_ServerOnUpdateArgument] = None,
1532 quote: Optional[bool] = None,
1533 system: bool = False,
1534 comment: Optional[str] = None,
1535 insert_sentinel: bool = False,
1536 _omit_from_statements: bool = False,
1537 _proxies: Optional[Any] = None,
1538 **dialect_kwargs: Any,
1539 ):
1540 r"""
1541 Construct a new ``Column`` object.
1543 :param name: The name of this column as represented in the database.
1544 This argument may be the first positional argument, or specified
1545 via keyword.
1547 Names which contain no upper case characters
1548 will be treated as case insensitive names, and will not be quoted
1549 unless they are a reserved word. Names with any number of upper
1550 case characters will be quoted and sent exactly. Note that this
1551 behavior applies even for databases which standardize upper
1552 case names as case insensitive such as Oracle Database.
1554 The name field may be omitted at construction time and applied
1555 later, at any time before the Column is associated with a
1556 :class:`_schema.Table`. This is to support convenient
1557 usage within the :mod:`~sqlalchemy.ext.declarative` extension.
1559 :param type\_: The column's type, indicated using an instance which
1560 subclasses :class:`~sqlalchemy.types.TypeEngine`. If no arguments
1561 are required for the type, the class of the type can be sent
1562 as well, e.g.::
1564 # use a type with arguments
1565 Column("data", String(50))
1567 # use no arguments
1568 Column("level", Integer)
1570 The ``type`` argument may be the second positional argument
1571 or specified by keyword.
1573 If the ``type`` is ``None`` or is omitted, it will first default to
1574 the special type :class:`.NullType`. If and when this
1575 :class:`_schema.Column` is made to refer to another column using
1576 :class:`_schema.ForeignKey` and/or
1577 :class:`_schema.ForeignKeyConstraint`, the type
1578 of the remote-referenced column will be copied to this column as
1579 well, at the moment that the foreign key is resolved against that
1580 remote :class:`_schema.Column` object.
1582 :param \*args: Additional positional arguments include various
1583 :class:`.SchemaItem` derived constructs which will be applied
1584 as options to the column. These include instances of
1585 :class:`.Constraint`, :class:`_schema.ForeignKey`,
1586 :class:`.ColumnDefault`, :class:`.Sequence`, :class:`.Computed`
1587 :class:`.Identity`. In some cases an
1588 equivalent keyword argument is available such as ``server_default``,
1589 ``default`` and ``unique``.
1591 :param autoincrement: Set up "auto increment" semantics for an
1592 **integer primary key column with no foreign key dependencies**
1593 (see later in this docstring for a more specific definition).
1594 This may influence the :term:`DDL` that will be emitted for
1595 this column during a table create, as well as how the column
1596 will be considered when INSERT statements are compiled and
1597 executed.
1599 The default value is the string ``"auto"``,
1600 which indicates that a single-column (i.e. non-composite) primary key
1601 that is of an INTEGER type with no other client-side or server-side
1602 default constructs indicated should receive auto increment semantics
1603 automatically. Other values include ``True`` (force this column to
1604 have auto-increment semantics for a :term:`composite primary key` as
1605 well), ``False`` (this column should never have auto-increment
1606 semantics), and the string ``"ignore_fk"`` (special-case for foreign
1607 key columns, see below).
1609 The term "auto increment semantics" refers both to the kind of DDL
1610 that will be emitted for the column within a CREATE TABLE statement,
1611 when methods such as :meth:`.MetaData.create_all` and
1612 :meth:`.Table.create` are invoked, as well as how the column will be
1613 considered when an INSERT statement is compiled and emitted to the
1614 database:
1616 * **DDL rendering** (i.e. :meth:`.MetaData.create_all`,
1617 :meth:`.Table.create`): When used on a :class:`.Column` that has
1618 no other
1619 default-generating construct associated with it (such as a
1620 :class:`.Sequence` or :class:`.Identity` construct), the parameter
1621 will imply that database-specific keywords such as PostgreSQL
1622 ``SERIAL``, MySQL ``AUTO_INCREMENT``, or ``IDENTITY`` on SQL Server
1623 should also be rendered. Not every database backend has an
1624 "implied" default generator available; for example the Oracle Database
1625 backends alway needs an explicit construct such as
1626 :class:`.Identity` to be included with a :class:`.Column` in order
1627 for the DDL rendered to include auto-generating constructs to also
1628 be produced in the database.
1630 * **INSERT semantics** (i.e. when a :func:`_sql.insert` construct is
1631 compiled into a SQL string and is then executed on a database using
1632 :meth:`_engine.Connection.execute` or equivalent): A single-row
1633 INSERT statement will be known to produce a new integer primary key
1634 value automatically for this column, which will be accessible
1635 after the statement is invoked via the
1636 :attr:`.CursorResult.inserted_primary_key` attribute upon the
1637 :class:`_result.Result` object. This also applies towards use of the
1638 ORM when ORM-mapped objects are persisted to the database,
1639 indicating that a new integer primary key will be available to
1640 become part of the :term:`identity key` for that object. This
1641 behavior takes place regardless of what DDL constructs are
1642 associated with the :class:`_schema.Column` and is independent
1643 of the "DDL Rendering" behavior discussed in the previous note
1644 above.
1646 The parameter may be set to ``True`` to indicate that a column which
1647 is part of a composite (i.e. multi-column) primary key should
1648 have autoincrement semantics, though note that only one column
1649 within a primary key may have this setting. It can also
1650 be set to ``True`` to indicate autoincrement semantics on a
1651 column that has a client-side or server-side default configured,
1652 however note that not all dialects can accommodate all styles
1653 of default as an "autoincrement". It can also be
1654 set to ``False`` on a single-column primary key that has a
1655 datatype of INTEGER in order to disable auto increment semantics
1656 for that column.
1658 The setting *only* has an effect for columns which are:
1660 * Integer derived (i.e. INT, SMALLINT, BIGINT).
1662 * Part of the primary key
1664 * Not referring to another column via :class:`_schema.ForeignKey`,
1665 unless
1666 the value is specified as ``'ignore_fk'``::
1668 # turn on autoincrement for this column despite
1669 # the ForeignKey()
1670 Column(
1671 "id",
1672 ForeignKey("other.id"),
1673 primary_key=True,
1674 autoincrement="ignore_fk",
1675 )
1677 It is typically not desirable to have "autoincrement" enabled on a
1678 column that refers to another via foreign key, as such a column is
1679 required to refer to a value that originates from elsewhere.
1681 The setting has these effects on columns that meet the
1682 above criteria:
1684 * DDL issued for the column, if the column does not already include
1685 a default generating construct supported by the backend such as
1686 :class:`.Identity`, will include database-specific
1687 keywords intended to signify this column as an
1688 "autoincrement" column for specific backends. Behavior for
1689 primary SQLAlchemy dialects includes:
1691 * AUTO INCREMENT on MySQL and MariaDB
1692 * SERIAL on PostgreSQL
1693 * IDENTITY on MS-SQL - this occurs even without the
1694 :class:`.Identity` construct as the
1695 :paramref:`.Column.autoincrement` parameter pre-dates this
1696 construct.
1697 * SQLite - SQLite integer primary key columns are implicitly
1698 "auto incrementing" and no additional keywords are rendered;
1699 to render the special SQLite keyword ``AUTOINCREMENT``
1700 is not included as this is unnecessary and not recommended
1701 by the database vendor. See the section
1702 :ref:`sqlite_autoincrement` for more background.
1703 * Oracle Database - The Oracle Database dialects have no default "autoincrement"
1704 feature available at this time, instead the :class:`.Identity`
1705 construct is recommended to achieve this (the :class:`.Sequence`
1706 construct may also be used).
1707 * Third-party dialects - consult those dialects' documentation
1708 for details on their specific behaviors.
1710 * When a single-row :func:`_sql.insert` construct is compiled and
1711 executed, which does not set the :meth:`_sql.Insert.inline`
1712 modifier, newly generated primary key values for this column
1713 will be automatically retrieved upon statement execution
1714 using a method specific to the database driver in use:
1716 * MySQL, SQLite - calling upon ``cursor.lastrowid()``
1717 (see
1718 `https://www.python.org/dev/peps/pep-0249/#lastrowid
1719 <https://www.python.org/dev/peps/pep-0249/#lastrowid>`_)
1720 * PostgreSQL, SQL Server, Oracle Database - use RETURNING or an equivalent
1721 construct when rendering an INSERT statement, and then retrieving
1722 the newly generated primary key values after execution
1723 * PostgreSQL, Oracle Database for :class:`_schema.Table` objects that
1724 set :paramref:`_schema.Table.implicit_returning` to False -
1725 for a :class:`.Sequence` only, the :class:`.Sequence` is invoked
1726 explicitly before the INSERT statement takes place so that the
1727 newly generated primary key value is available to the client
1728 * SQL Server for :class:`_schema.Table` objects that
1729 set :paramref:`_schema.Table.implicit_returning` to False -
1730 the ``SELECT scope_identity()`` construct is used after the
1731 INSERT statement is invoked to retrieve the newly generated
1732 primary key value.
1733 * Third-party dialects - consult those dialects' documentation
1734 for details on their specific behaviors.
1736 * For multiple-row :func:`_sql.insert` constructs invoked with
1737 a list of parameters (i.e. "executemany" semantics), primary-key
1738 retrieving behaviors are generally disabled, however there may
1739 be special APIs that may be used to retrieve lists of new
1740 primary key values for an "executemany", such as the psycopg2
1741 "fast insertmany" feature. Such features are very new and
1742 may not yet be well covered in documentation.
1744 :param default: A scalar, Python callable, or
1745 :class:`_expression.ColumnElement` expression representing the
1746 *default value* for this column, which will be invoked upon insert
1747 if this column is otherwise not specified in the VALUES clause of
1748 the insert. This is a shortcut to using :class:`.ColumnDefault` as
1749 a positional argument; see that class for full detail on the
1750 structure of the argument.
1752 Contrast this argument to
1753 :paramref:`_schema.Column.server_default`
1754 which creates a default generator on the database side.
1756 .. seealso::
1758 :ref:`metadata_defaults_toplevel`
1760 :param insert_default: An alias of :paramref:`.Column.default`
1761 for compatibility with :func:`_orm.mapped_column`.
1763 .. versionadded: 2.0.31
1765 :param doc: optional String that can be used by the ORM or similar
1766 to document attributes on the Python side. This attribute does
1767 **not** render SQL comments; use the
1768 :paramref:`_schema.Column.comment`
1769 parameter for this purpose.
1771 :param key: An optional string identifier which will identify this
1772 ``Column`` object on the :class:`_schema.Table`.
1773 When a key is provided,
1774 this is the only identifier referencing the ``Column`` within the
1775 application, including ORM attribute mapping; the ``name`` field
1776 is used only when rendering SQL.
1778 :param index: When ``True``, indicates that a :class:`_schema.Index`
1779 construct will be automatically generated for this
1780 :class:`_schema.Column`, which will result in a "CREATE INDEX"
1781 statement being emitted for the :class:`_schema.Table` when the DDL
1782 create operation is invoked.
1784 Using this flag is equivalent to making use of the
1785 :class:`_schema.Index` construct explicitly at the level of the
1786 :class:`_schema.Table` construct itself::
1788 Table(
1789 "some_table",
1790 metadata,
1791 Column("x", Integer),
1792 Index("ix_some_table_x", "x"),
1793 )
1795 To add the :paramref:`_schema.Index.unique` flag to the
1796 :class:`_schema.Index`, set both the
1797 :paramref:`_schema.Column.unique` and
1798 :paramref:`_schema.Column.index` flags to True simultaneously,
1799 which will have the effect of rendering the "CREATE UNIQUE INDEX"
1800 DDL instruction instead of "CREATE INDEX".
1802 The name of the index is generated using the
1803 :ref:`default naming convention <constraint_default_naming_convention>`
1804 which for the :class:`_schema.Index` construct is of the form
1805 ``ix_<tablename>_<columnname>``.
1807 As this flag is intended only as a convenience for the common case
1808 of adding a single-column, default configured index to a table
1809 definition, explicit use of the :class:`_schema.Index` construct
1810 should be preferred for most use cases, including composite indexes
1811 that encompass more than one column, indexes with SQL expressions
1812 or ordering, backend-specific index configuration options, and
1813 indexes that use a specific name.
1815 .. note:: the :attr:`_schema.Column.index` attribute on
1816 :class:`_schema.Column`
1817 **does not indicate** if this column is indexed or not, only
1818 if this flag was explicitly set here. To view indexes on
1819 a column, view the :attr:`_schema.Table.indexes` collection
1820 or use :meth:`_reflection.Inspector.get_indexes`.
1822 .. seealso::
1824 :ref:`schema_indexes`
1826 :ref:`constraint_naming_conventions`
1828 :paramref:`_schema.Column.unique`
1830 :param info: Optional data dictionary which will be populated into the
1831 :attr:`.SchemaItem.info` attribute of this object.
1833 :param nullable: When set to ``False``, will cause the "NOT NULL"
1834 phrase to be added when generating DDL for the column. When
1835 ``True``, will normally generate nothing (in SQL this defaults to
1836 "NULL"), except in some very specific backend-specific edge cases
1837 where "NULL" may render explicitly.
1838 Defaults to ``True`` unless :paramref:`_schema.Column.primary_key`
1839 is also ``True`` or the column specifies a :class:`_sql.Identity`,
1840 in which case it defaults to ``False``.
1841 This parameter is only used when issuing CREATE TABLE statements.
1843 .. note::
1845 When the column specifies a :class:`_sql.Identity` this
1846 parameter is in general ignored by the DDL compiler. The
1847 PostgreSQL database allows nullable identity column by
1848 setting this parameter to ``True`` explicitly.
1850 :param onupdate: A scalar, Python callable, or
1851 :class:`~sqlalchemy.sql.expression.ClauseElement` representing a
1852 default value to be applied to the column within UPDATE
1853 statements, which will be invoked upon update if this column is not
1854 present in the SET clause of the update. This is a shortcut to
1855 using :class:`.ColumnDefault` as a positional argument with
1856 ``for_update=True``.
1858 .. seealso::
1860 :ref:`metadata_defaults` - complete discussion of onupdate
1862 :param primary_key: If ``True``, marks this column as a primary key
1863 column. Multiple columns can have this flag set to specify
1864 composite primary keys. As an alternative, the primary key of a
1865 :class:`_schema.Table` can be specified via an explicit
1866 :class:`.PrimaryKeyConstraint` object.
1868 :param server_default: A :class:`.FetchedValue` instance, str, Unicode
1869 or :func:`~sqlalchemy.sql.expression.text` construct representing
1870 the DDL DEFAULT value for the column.
1872 String types will be emitted as-is, surrounded by single quotes::
1874 Column("x", Text, server_default="val")
1876 will render:
1878 .. sourcecode:: sql
1880 x TEXT DEFAULT 'val'
1882 A :func:`~sqlalchemy.sql.expression.text` expression will be
1883 rendered as-is, without quotes::
1885 Column("y", DateTime, server_default=text("NOW()"))
1887 will render:
1889 .. sourcecode:: sql
1891 y DATETIME DEFAULT NOW()
1893 Strings and text() will be converted into a
1894 :class:`.DefaultClause` object upon initialization.
1896 This parameter can also accept complex combinations of contextually
1897 valid SQLAlchemy expressions or constructs::
1899 from sqlalchemy import create_engine
1900 from sqlalchemy import Table, Column, MetaData, ARRAY, Text
1901 from sqlalchemy.dialects.postgresql import array
1903 engine = create_engine(
1904 "postgresql+psycopg2://scott:tiger@localhost/mydatabase"
1905 )
1906 metadata_obj = MetaData()
1907 tbl = Table(
1908 "foo",
1909 metadata_obj,
1910 Column(
1911 "bar", ARRAY(Text), server_default=array(["biz", "bang", "bash"])
1912 ),
1913 )
1914 metadata_obj.create_all(engine)
1916 The above results in a table created with the following SQL:
1918 .. sourcecode:: sql
1920 CREATE TABLE foo (
1921 bar TEXT[] DEFAULT ARRAY['biz', 'bang', 'bash']
1922 )
1924 Use :class:`.FetchedValue` to indicate that an already-existing
1925 column will generate a default value on the database side which
1926 will be available to SQLAlchemy for post-fetch after inserts. This
1927 construct does not specify any DDL and the implementation is left
1928 to the database, such as via a trigger.
1930 .. seealso::
1932 :ref:`server_defaults` - complete discussion of server side
1933 defaults
1935 :param server_onupdate: A :class:`.FetchedValue` instance
1936 representing a database-side default generation function,
1937 such as a trigger. This
1938 indicates to SQLAlchemy that a newly generated value will be
1939 available after updates. This construct does not actually
1940 implement any kind of generation function within the database,
1941 which instead must be specified separately.
1944 .. warning:: This directive **does not** currently produce MySQL's
1945 "ON UPDATE CURRENT_TIMESTAMP()" clause. See
1946 :ref:`mysql_timestamp_onupdate` for background on how to
1947 produce this clause.
1949 .. seealso::
1951 :ref:`triggered_columns`
1953 :param quote: Force quoting of this column's name on or off,
1954 corresponding to ``True`` or ``False``. When left at its default
1955 of ``None``, the column identifier will be quoted according to
1956 whether the name is case sensitive (identifiers with at least one
1957 upper case character are treated as case sensitive), or if it's a
1958 reserved word. This flag is only needed to force quoting of a
1959 reserved word which is not known by the SQLAlchemy dialect.
1961 :param unique: When ``True``, and the :paramref:`_schema.Column.index`
1962 parameter is left at its default value of ``False``,
1963 indicates that a :class:`_schema.UniqueConstraint`
1964 construct will be automatically generated for this
1965 :class:`_schema.Column`,
1966 which will result in a "UNIQUE CONSTRAINT" clause referring
1967 to this column being included
1968 in the ``CREATE TABLE`` statement emitted, when the DDL create
1969 operation for the :class:`_schema.Table` object is invoked.
1971 When this flag is ``True`` while the
1972 :paramref:`_schema.Column.index` parameter is simultaneously
1973 set to ``True``, the effect instead is that a
1974 :class:`_schema.Index` construct which includes the
1975 :paramref:`_schema.Index.unique` parameter set to ``True``
1976 is generated. See the documentation for
1977 :paramref:`_schema.Column.index` for additional detail.
1979 Using this flag is equivalent to making use of the
1980 :class:`_schema.UniqueConstraint` construct explicitly at the
1981 level of the :class:`_schema.Table` construct itself::
1983 Table("some_table", metadata, Column("x", Integer), UniqueConstraint("x"))
1985 The :paramref:`_schema.UniqueConstraint.name` parameter
1986 of the unique constraint object is left at its default value
1987 of ``None``; in the absence of a :ref:`naming convention <constraint_naming_conventions>`
1988 for the enclosing :class:`_schema.MetaData`, the UNIQUE CONSTRAINT
1989 construct will be emitted as unnamed, which typically invokes
1990 a database-specific naming convention to take place.
1992 As this flag is intended only as a convenience for the common case
1993 of adding a single-column, default configured unique constraint to a table
1994 definition, explicit use of the :class:`_schema.UniqueConstraint` construct
1995 should be preferred for most use cases, including composite constraints
1996 that encompass more than one column, backend-specific index configuration options, and
1997 constraints that use a specific name.
1999 .. note:: the :attr:`_schema.Column.unique` attribute on
2000 :class:`_schema.Column`
2001 **does not indicate** if this column has a unique constraint or
2002 not, only if this flag was explicitly set here. To view
2003 indexes and unique constraints that may involve this column,
2004 view the
2005 :attr:`_schema.Table.indexes` and/or
2006 :attr:`_schema.Table.constraints` collections or use
2007 :meth:`_reflection.Inspector.get_indexes` and/or
2008 :meth:`_reflection.Inspector.get_unique_constraints`
2010 .. seealso::
2012 :ref:`schema_unique_constraint`
2014 :ref:`constraint_naming_conventions`
2016 :paramref:`_schema.Column.index`
2018 :param system: When ``True``, indicates this is a "system" column,
2019 that is a column which is automatically made available by the
2020 database, and should not be included in the columns list for a
2021 ``CREATE TABLE`` statement.
2023 For more elaborate scenarios where columns should be
2024 conditionally rendered differently on different backends,
2025 consider custom compilation rules for :class:`.CreateColumn`.
2027 :param comment: Optional string that will render an SQL comment on
2028 table creation.
2030 .. versionadded:: 1.2 Added the
2031 :paramref:`_schema.Column.comment`
2032 parameter to :class:`_schema.Column`.
2034 :param insert_sentinel: Marks this :class:`_schema.Column` as an
2035 :term:`insert sentinel` used for optimizing the performance of the
2036 :term:`insertmanyvalues` feature for tables that don't
2037 otherwise have qualifying primary key configurations.
2039 .. versionadded:: 2.0.10
2041 .. seealso::
2043 :func:`_schema.insert_sentinel` - all in one helper for declaring
2044 sentinel columns
2046 :ref:`engine_insertmanyvalues`
2048 :ref:`engine_insertmanyvalues_sentinel_columns`
2051 """ # noqa: E501, RST201, RST202
2053 l_args = [__name_pos, __type_pos] + list(args)
2054 del args
2056 if l_args:
2057 if isinstance(l_args[0], str):
2058 if name is not None:
2059 raise exc.ArgumentError(
2060 "May not pass name positionally and as a keyword."
2061 )
2062 name = l_args.pop(0) # type: ignore
2063 elif l_args[0] is None:
2064 l_args.pop(0)
2065 if l_args:
2066 coltype = l_args[0]
2068 if hasattr(coltype, "_sqla_type"):
2069 if type_ is not None:
2070 raise exc.ArgumentError(
2071 "May not pass type_ positionally and as a keyword."
2072 )
2073 type_ = l_args.pop(0) # type: ignore
2074 elif l_args[0] is None:
2075 l_args.pop(0)
2077 if name is not None:
2078 name = quoted_name(name, quote)
2079 elif quote is not None:
2080 raise exc.ArgumentError(
2081 "Explicit 'name' is required when sending 'quote' argument"
2082 )
2084 # name = None is expected to be an interim state
2085 # note this use case is legacy now that ORM declarative has a
2086 # dedicated "column" construct local to the ORM
2087 super().__init__(name, type_) # type: ignore
2089 self.key = key if key is not None else name # type: ignore
2090 self.primary_key = primary_key
2091 self._insert_sentinel = insert_sentinel
2092 self._omit_from_statements = _omit_from_statements
2093 self._user_defined_nullable = udn = nullable
2094 if udn is not NULL_UNSPECIFIED:
2095 self.nullable = udn
2096 else:
2097 self.nullable = not primary_key
2099 # these default to None because .index and .unique is *not*
2100 # an informational flag about Column - there can still be an
2101 # Index or UniqueConstraint referring to this Column.
2102 self.index = index
2103 self.unique = unique
2105 self.system = system
2106 self.doc = doc
2107 self.autoincrement: _AutoIncrementType = autoincrement
2108 self.constraints = set()
2109 self.foreign_keys = set()
2110 self.comment = comment
2111 self.computed = None
2112 self.identity = None
2114 # check if this Column is proxying another column
2116 if _proxies is not None:
2117 self._proxies = _proxies
2118 else:
2119 # otherwise, add DDL-related events
2120 self._set_type(self.type)
2122 if insert_default is not _NoArg.NO_ARG:
2123 resolved_default = insert_default
2124 elif default is not _NoArg.NO_ARG:
2125 resolved_default = default
2126 else:
2127 resolved_default = None
2129 if resolved_default is not None:
2130 if not isinstance(resolved_default, (ColumnDefault, Sequence)):
2131 resolved_default = ColumnDefault(resolved_default)
2133 self.default = resolved_default
2134 l_args.append(resolved_default)
2135 else:
2136 self.default = None
2138 if onupdate is not None:
2139 if not isinstance(onupdate, (ColumnDefault, Sequence)):
2140 onupdate = ColumnDefault(onupdate, for_update=True)
2142 self.onupdate = onupdate
2143 l_args.append(onupdate)
2144 else:
2145 self.onupdate = None
2147 if server_default is not None:
2148 if isinstance(server_default, FetchedValue):
2149 server_default = server_default._as_for_update(False)
2150 l_args.append(server_default)
2151 else:
2152 server_default = DefaultClause(server_default)
2153 l_args.append(server_default)
2154 self.server_default = server_default
2156 if server_onupdate is not None:
2157 if isinstance(server_onupdate, FetchedValue):
2158 server_onupdate = server_onupdate._as_for_update(True)
2159 l_args.append(server_onupdate)
2160 else:
2161 server_onupdate = DefaultClause(
2162 server_onupdate, for_update=True
2163 )
2164 l_args.append(server_onupdate)
2165 self.server_onupdate = server_onupdate
2167 self._init_items(*cast(_typing_Sequence[SchemaItem], l_args))
2169 util.set_creation_order(self)
2171 if info is not None:
2172 self.info = info
2174 self._extra_kwargs(**dialect_kwargs)
2176 table: Table
2178 constraints: Set[Constraint]
2180 foreign_keys: Set[ForeignKey]
2181 """A collection of all :class:`_schema.ForeignKey` marker objects
2182 associated with this :class:`_schema.Column`.
2184 Each object is a member of a :class:`_schema.Table`-wide
2185 :class:`_schema.ForeignKeyConstraint`.
2187 .. seealso::
2189 :attr:`_schema.Table.foreign_keys`
2191 """
2193 index: Optional[bool]
2194 """The value of the :paramref:`_schema.Column.index` parameter.
2196 Does not indicate if this :class:`_schema.Column` is actually indexed
2197 or not; use :attr:`_schema.Table.indexes`.
2199 .. seealso::
2201 :attr:`_schema.Table.indexes`
2202 """
2204 unique: Optional[bool]
2205 """The value of the :paramref:`_schema.Column.unique` parameter.
2207 Does not indicate if this :class:`_schema.Column` is actually subject to
2208 a unique constraint or not; use :attr:`_schema.Table.indexes` and
2209 :attr:`_schema.Table.constraints`.
2211 .. seealso::
2213 :attr:`_schema.Table.indexes`
2215 :attr:`_schema.Table.constraints`.
2217 """
2219 computed: Optional[Computed]
2221 identity: Optional[Identity]
2223 def _set_type(self, type_: TypeEngine[Any]) -> None:
2224 assert self.type._isnull or type_ is self.type
2226 self.type = type_
2227 if isinstance(self.type, SchemaEventTarget):
2228 self.type._set_parent_with_dispatch(self)
2229 for impl in self.type._variant_mapping.values():
2230 if isinstance(impl, SchemaEventTarget):
2231 impl._set_parent_with_dispatch(self)
2233 @HasMemoized.memoized_attribute
2234 def _default_description_tuple(self) -> _DefaultDescriptionTuple:
2235 """used by default.py -> _process_execute_defaults()"""
2237 return _DefaultDescriptionTuple._from_column_default(self.default)
2239 @HasMemoized.memoized_attribute
2240 def _onupdate_description_tuple(self) -> _DefaultDescriptionTuple:
2241 """used by default.py -> _process_execute_defaults()"""
2242 return _DefaultDescriptionTuple._from_column_default(self.onupdate)
2244 @util.memoized_property
2245 def _gen_static_annotations_cache_key(self) -> bool: # type: ignore
2246 """special attribute used by cache key gen, if true, we will
2247 use a static cache key for the annotations dictionary, else we
2248 will generate a new cache key for annotations each time.
2250 Added for #8790
2252 """
2253 return self.table is not None and self.table._is_table
2255 def _extra_kwargs(self, **kwargs: Any) -> None:
2256 self._validate_dialect_kwargs(kwargs)
2258 def __str__(self) -> str:
2259 if self.name is None:
2260 return "(no name)"
2261 elif self.table is not None:
2262 if self.table.named_with_column:
2263 return self.table.description + "." + self.description
2264 else:
2265 return self.description
2266 else:
2267 return self.description
2269 def references(self, column: Column[Any]) -> bool:
2270 """Return True if this Column references the given column via foreign
2271 key."""
2273 for fk in self.foreign_keys:
2274 if fk.column.proxy_set.intersection(column.proxy_set):
2275 return True
2276 else:
2277 return False
2279 def append_foreign_key(self, fk: ForeignKey) -> None:
2280 fk._set_parent_with_dispatch(self)
2282 def __repr__(self) -> str:
2283 kwarg = []
2284 if self.key != self.name:
2285 kwarg.append("key")
2286 if self.primary_key:
2287 kwarg.append("primary_key")
2288 if not self.nullable:
2289 kwarg.append("nullable")
2290 if self.onupdate:
2291 kwarg.append("onupdate")
2292 if self.default:
2293 kwarg.append("default")
2294 if self.server_default:
2295 kwarg.append("server_default")
2296 if self.comment:
2297 kwarg.append("comment")
2298 return "Column(%s)" % ", ".join(
2299 [repr(self.name)]
2300 + [repr(self.type)]
2301 + [repr(x) for x in self.foreign_keys if x is not None]
2302 + [repr(x) for x in self.constraints]
2303 + [
2304 (
2305 self.table is not None
2306 and "table=<%s>" % self.table.description
2307 or "table=None"
2308 )
2309 ]
2310 + ["%s=%s" % (k, repr(getattr(self, k))) for k in kwarg]
2311 )
2313 def _set_parent( # type: ignore[override]
2314 self,
2315 parent: SchemaEventTarget,
2316 *,
2317 all_names: Dict[str, Column[Any]],
2318 allow_replacements: bool,
2319 **kw: Any,
2320 ) -> None:
2321 table = parent
2322 assert isinstance(table, Table)
2323 if not self.name:
2324 raise exc.ArgumentError(
2325 "Column must be constructed with a non-blank name or "
2326 "assign a non-blank .name before adding to a Table."
2327 )
2329 self._reset_memoizations()
2331 if self.key is None:
2332 self.key = self.name
2334 existing = getattr(self, "table", None)
2335 if existing is not None and existing is not table:
2336 raise exc.ArgumentError(
2337 f"Column object '{self.key}' already "
2338 f"assigned to Table '{existing.description}'"
2339 )
2341 extra_remove = None
2342 existing_col = None
2343 conflicts_on = ""
2345 if self.key in table._columns:
2346 existing_col = table._columns[self.key]
2347 if self.key == self.name:
2348 conflicts_on = "name"
2349 else:
2350 conflicts_on = "key"
2351 elif self.name in all_names:
2352 existing_col = all_names[self.name]
2353 extra_remove = {existing_col}
2354 conflicts_on = "name"
2356 if existing_col is not None:
2357 if existing_col is not self:
2358 if not allow_replacements:
2359 raise exc.DuplicateColumnError(
2360 f"A column with {conflicts_on} "
2361 f"""'{
2362 self.key if conflicts_on == 'key' else self.name
2363 }' """
2364 f"is already present in table '{table.name}'."
2365 )
2366 for fk in existing_col.foreign_keys:
2367 table.foreign_keys.remove(fk)
2368 if fk.constraint in table.constraints:
2369 # this might have been removed
2370 # already, if it's a composite constraint
2371 # and more than one col being replaced
2372 table.constraints.remove(fk.constraint)
2374 if extra_remove and existing_col is not None and self.key == self.name:
2375 util.warn(
2376 f'Column with user-specified key "{existing_col.key}" is '
2377 "being replaced with "
2378 f'plain named column "{self.name}", '
2379 f'key "{existing_col.key}" is being removed. If this is a '
2380 "reflection operation, specify autoload_replace=False to "
2381 "prevent this replacement."
2382 )
2383 table._columns.replace(self, extra_remove=extra_remove)
2384 all_names[self.name] = self
2385 self.table = table
2387 if self._insert_sentinel:
2388 if self.table._sentinel_column is not None:
2389 raise exc.ArgumentError(
2390 "a Table may have only one explicit sentinel column"
2391 )
2392 self.table._sentinel_column = self
2394 if self.primary_key:
2395 table.primary_key._replace(self)
2396 elif self.key in table.primary_key:
2397 raise exc.ArgumentError(
2398 f"Trying to redefine primary-key column '{self.key}' as a "
2399 f"non-primary-key column on table '{table.fullname}'"
2400 )
2402 if self.index:
2403 if isinstance(self.index, str):
2404 raise exc.ArgumentError(
2405 "The 'index' keyword argument on Column is boolean only. "
2406 "To create indexes with a specific name, create an "
2407 "explicit Index object external to the Table."
2408 )
2409 table.append_constraint(
2410 Index(
2411 None, self.key, unique=bool(self.unique), _column_flag=True
2412 )
2413 )
2415 elif self.unique:
2416 if isinstance(self.unique, str):
2417 raise exc.ArgumentError(
2418 "The 'unique' keyword argument on Column is boolean "
2419 "only. To create unique constraints or indexes with a "
2420 "specific name, append an explicit UniqueConstraint to "
2421 "the Table's list of elements, or create an explicit "
2422 "Index object external to the Table."
2423 )
2424 table.append_constraint(
2425 UniqueConstraint(self.key, _column_flag=True)
2426 )
2428 self._setup_on_memoized_fks(lambda fk: fk._set_remote_table(table))
2430 if self.identity and (
2431 isinstance(self.default, Sequence)
2432 or isinstance(self.onupdate, Sequence)
2433 ):
2434 raise exc.ArgumentError(
2435 "An column cannot specify both Identity and Sequence."
2436 )
2438 def _setup_on_memoized_fks(self, fn: Callable[..., Any]) -> None:
2439 fk_keys = [
2440 ((self.table.key, self.key), False),
2441 ((self.table.key, self.name), True),
2442 ]
2443 for fk_key, link_to_name in fk_keys:
2444 if fk_key in self.table.metadata._fk_memos:
2445 for fk in self.table.metadata._fk_memos[fk_key]:
2446 if fk.link_to_name is link_to_name:
2447 fn(fk)
2449 def _on_table_attach(self, fn: Callable[..., Any]) -> None:
2450 if self.table is not None:
2451 fn(self, self.table)
2452 else:
2453 event.listen(self, "after_parent_attach", fn)
2455 @util.deprecated(
2456 "1.4",
2457 "The :meth:`_schema.Column.copy` method is deprecated "
2458 "and will be removed in a future release.",
2459 )
2460 def copy(self, **kw: Any) -> Column[Any]:
2461 return self._copy(**kw)
2463 def _copy(self, **kw: Any) -> Column[Any]:
2464 """Create a copy of this ``Column``, uninitialized.
2466 This is used in :meth:`_schema.Table.to_metadata` and by the ORM.
2468 """
2470 # Constraint objects plus non-constraint-bound ForeignKey objects
2471 args: List[SchemaItem] = [
2472 c._copy(**kw) for c in self.constraints if not c._type_bound
2473 ] + [c._copy(**kw) for c in self.foreign_keys if not c.constraint]
2475 # ticket #5276
2476 column_kwargs = {}
2477 for dialect_name in self.dialect_options:
2478 dialect_options = self.dialect_options[dialect_name]._non_defaults
2479 for (
2480 dialect_option_key,
2481 dialect_option_value,
2482 ) in dialect_options.items():
2483 column_kwargs[dialect_name + "_" + dialect_option_key] = (
2484 dialect_option_value
2485 )
2487 server_default = self.server_default
2488 server_onupdate = self.server_onupdate
2489 if isinstance(server_default, (Computed, Identity)):
2490 # TODO: likely should be copied in all cases
2491 # TODO: if a Sequence, we would need to transfer the Sequence
2492 # .metadata as well
2493 args.append(server_default._copy(**kw))
2494 server_default = server_onupdate = None
2496 type_ = self.type
2497 if isinstance(type_, SchemaEventTarget):
2498 type_ = type_.copy(**kw)
2500 # TODO: DefaultGenerator is not copied here! it's just used again
2501 # with _set_parent() pointing to the old column. see the new
2502 # use of _copy() in the new _merge() method
2504 c = self._constructor(
2505 name=self.name,
2506 type_=type_,
2507 key=self.key,
2508 primary_key=self.primary_key,
2509 unique=self.unique,
2510 system=self.system,
2511 # quote=self.quote, # disabled 2013-08-27 (commit 031ef080)
2512 index=self.index,
2513 autoincrement=self.autoincrement,
2514 default=self.default,
2515 server_default=server_default,
2516 onupdate=self.onupdate,
2517 server_onupdate=server_onupdate,
2518 doc=self.doc,
2519 comment=self.comment,
2520 _omit_from_statements=self._omit_from_statements,
2521 insert_sentinel=self._insert_sentinel,
2522 *args,
2523 **column_kwargs,
2524 )
2526 # copy the state of "nullable" exactly, to accommodate for
2527 # ORM flipping the .nullable flag directly
2528 c.nullable = self.nullable
2529 c._user_defined_nullable = self._user_defined_nullable
2531 return self._schema_item_copy(c)
2533 def _merge(self, other: Column[Any]) -> None:
2534 """merge the elements of another column into this one.
2536 this is used by ORM pep-593 merge and will likely need a lot
2537 of fixes.
2540 """
2542 if self.primary_key:
2543 other.primary_key = True
2545 if self.autoincrement != "auto" and other.autoincrement == "auto":
2546 other.autoincrement = self.autoincrement
2548 if self.system:
2549 other.system = self.system
2551 if self.info:
2552 other.info.update(self.info)
2554 type_ = self.type
2555 if not type_._isnull and other.type._isnull:
2556 if isinstance(type_, SchemaEventTarget):
2557 type_ = type_.copy()
2559 other.type = type_
2561 if isinstance(type_, SchemaEventTarget):
2562 type_._set_parent_with_dispatch(other)
2564 for impl in type_._variant_mapping.values():
2565 if isinstance(impl, SchemaEventTarget):
2566 impl._set_parent_with_dispatch(other)
2568 if (
2569 self._user_defined_nullable is not NULL_UNSPECIFIED
2570 and other._user_defined_nullable is NULL_UNSPECIFIED
2571 ):
2572 other.nullable = self.nullable
2573 other._user_defined_nullable = self._user_defined_nullable
2575 if self.default is not None and other.default is None:
2576 new_default = self.default._copy()
2577 new_default._set_parent(other)
2579 if self.server_default and other.server_default is None:
2580 new_server_default = self.server_default
2581 if isinstance(new_server_default, FetchedValue):
2582 new_server_default = new_server_default._copy()
2583 new_server_default._set_parent(other)
2584 else:
2585 other.server_default = new_server_default
2587 if self.server_onupdate and other.server_onupdate is None:
2588 new_server_onupdate = self.server_onupdate
2589 new_server_onupdate = new_server_onupdate._copy()
2590 new_server_onupdate._set_parent(other)
2592 if self.onupdate and other.onupdate is None:
2593 new_onupdate = self.onupdate._copy()
2594 new_onupdate._set_parent(other)
2596 if self.index in (True, False) and other.index is None:
2597 other.index = self.index
2599 if self.unique in (True, False) and other.unique is None:
2600 other.unique = self.unique
2602 if self.doc and other.doc is None:
2603 other.doc = self.doc
2605 if self.comment and other.comment is None:
2606 other.comment = self.comment
2608 for const in self.constraints:
2609 if not const._type_bound:
2610 new_const = const._copy()
2611 new_const._set_parent(other)
2613 for fk in self.foreign_keys:
2614 if not fk.constraint:
2615 new_fk = fk._copy()
2616 new_fk._set_parent(other)
2618 def _make_proxy(
2619 self,
2620 selectable: FromClause,
2621 primary_key: ColumnSet,
2622 foreign_keys: Set[KeyedColumnElement[Any]],
2623 name: Optional[str] = None,
2624 key: Optional[str] = None,
2625 name_is_truncatable: bool = False,
2626 compound_select_cols: Optional[
2627 _typing_Sequence[ColumnElement[Any]]
2628 ] = None,
2629 **kw: Any,
2630 ) -> Tuple[str, ColumnClause[_T]]:
2631 """Create a *proxy* for this column.
2633 This is a copy of this ``Column`` referenced by a different parent
2634 (such as an alias or select statement). The column should
2635 be used only in select scenarios, as its full DDL/default
2636 information is not transferred.
2638 """
2640 fk = [
2641 ForeignKey(
2642 col if col is not None else f._colspec,
2643 _unresolvable=col is None,
2644 _constraint=f.constraint,
2645 )
2646 for f, col in [
2647 (fk, fk._resolve_column(raiseerr=False))
2648 for fk in self.foreign_keys
2649 ]
2650 ]
2652 if name is None and self.name is None:
2653 raise exc.InvalidRequestError(
2654 "Cannot initialize a sub-selectable"
2655 " with this Column object until its 'name' has "
2656 "been assigned."
2657 )
2658 try:
2659 c = self._constructor(
2660 (
2661 coercions.expect(
2662 roles.TruncatedLabelRole, name if name else self.name
2663 )
2664 if name_is_truncatable
2665 else (name or self.name)
2666 ),
2667 self.type,
2668 # this may actually be ._proxy_key when the key is incoming
2669 key=key if key else name if name else self.key,
2670 primary_key=self.primary_key,
2671 nullable=self.nullable,
2672 _proxies=(
2673 list(compound_select_cols)
2674 if compound_select_cols
2675 else [self]
2676 ),
2677 *fk,
2678 )
2679 except TypeError as err:
2680 raise TypeError(
2681 "Could not create a copy of this %r object. "
2682 "Ensure the class includes a _constructor() "
2683 "attribute or method which accepts the "
2684 "standard Column constructor arguments, or "
2685 "references the Column class itself." % self.__class__
2686 ) from err
2688 c.table = selectable
2689 c._propagate_attrs = selectable._propagate_attrs
2690 if selectable._is_clone_of is not None:
2691 c._is_clone_of = selectable._is_clone_of.columns.get(c.key)
2693 if self.primary_key:
2694 primary_key.add(c)
2696 if fk:
2697 foreign_keys.update(fk) # type: ignore
2699 return c.key, c
2702def insert_sentinel(
2703 name: Optional[str] = None,
2704 type_: Optional[_TypeEngineArgument[_T]] = None,
2705 *,
2706 default: Optional[Any] = None,
2707 omit_from_statements: bool = True,
2708) -> Column[Any]:
2709 """Provides a surrogate :class:`_schema.Column` that will act as a
2710 dedicated insert :term:`sentinel` column, allowing efficient bulk
2711 inserts with deterministic RETURNING sorting for tables that
2712 don't otherwise have qualifying primary key configurations.
2714 Adding this column to a :class:`.Table` object requires that a
2715 corresponding database table actually has this column present, so if adding
2716 it to an existing model, existing database tables would need to be migrated
2717 (e.g. using ALTER TABLE or similar) to include this column.
2719 For background on how this object is used, see the section
2720 :ref:`engine_insertmanyvalues_sentinel_columns` as part of the
2721 section :ref:`engine_insertmanyvalues`.
2723 The :class:`_schema.Column` returned will be a nullable integer column by
2724 default and make use of a sentinel-specific default generator used only in
2725 "insertmanyvalues" operations.
2727 .. seealso::
2729 :func:`_orm.orm_insert_sentinel`
2731 :paramref:`_schema.Column.insert_sentinel`
2733 :ref:`engine_insertmanyvalues`
2735 :ref:`engine_insertmanyvalues_sentinel_columns`
2738 .. versionadded:: 2.0.10
2740 """
2741 return Column(
2742 name=name,
2743 type_=type_api.INTEGERTYPE if type_ is None else type_,
2744 default=(
2745 default if default is not None else _InsertSentinelColumnDefault()
2746 ),
2747 _omit_from_statements=omit_from_statements,
2748 insert_sentinel=True,
2749 )
2752class ForeignKey(DialectKWArgs, SchemaItem):
2753 """Defines a dependency between two columns.
2755 ``ForeignKey`` is specified as an argument to a :class:`_schema.Column`
2756 object,
2757 e.g.::
2759 t = Table(
2760 "remote_table",
2761 metadata,
2762 Column("remote_id", ForeignKey("main_table.id")),
2763 )
2765 Note that ``ForeignKey`` is only a marker object that defines
2766 a dependency between two columns. The actual constraint
2767 is in all cases represented by the :class:`_schema.ForeignKeyConstraint`
2768 object. This object will be generated automatically when
2769 a ``ForeignKey`` is associated with a :class:`_schema.Column` which
2770 in turn is associated with a :class:`_schema.Table`. Conversely,
2771 when :class:`_schema.ForeignKeyConstraint` is applied to a
2772 :class:`_schema.Table`,
2773 ``ForeignKey`` markers are automatically generated to be
2774 present on each associated :class:`_schema.Column`, which are also
2775 associated with the constraint object.
2777 Note that you cannot define a "composite" foreign key constraint,
2778 that is a constraint between a grouping of multiple parent/child
2779 columns, using ``ForeignKey`` objects. To define this grouping,
2780 the :class:`_schema.ForeignKeyConstraint` object must be used, and applied
2781 to the :class:`_schema.Table`. The associated ``ForeignKey`` objects
2782 are created automatically.
2784 The ``ForeignKey`` objects associated with an individual
2785 :class:`_schema.Column`
2786 object are available in the `foreign_keys` collection
2787 of that column.
2789 Further examples of foreign key configuration are in
2790 :ref:`metadata_foreignkeys`.
2792 """
2794 __visit_name__ = "foreign_key"
2796 parent: Column[Any]
2798 _table_column: Optional[Column[Any]]
2800 def __init__(
2801 self,
2802 column: _DDLColumnArgument,
2803 _constraint: Optional[ForeignKeyConstraint] = None,
2804 use_alter: bool = False,
2805 name: _ConstraintNameArgument = None,
2806 onupdate: Optional[str] = None,
2807 ondelete: Optional[str] = None,
2808 deferrable: Optional[bool] = None,
2809 initially: Optional[str] = None,
2810 link_to_name: bool = False,
2811 match: Optional[str] = None,
2812 info: Optional[_InfoType] = None,
2813 comment: Optional[str] = None,
2814 _unresolvable: bool = False,
2815 **dialect_kw: Any,
2816 ):
2817 r"""
2818 Construct a column-level FOREIGN KEY.
2820 The :class:`_schema.ForeignKey` object when constructed generates a
2821 :class:`_schema.ForeignKeyConstraint`
2822 which is associated with the parent
2823 :class:`_schema.Table` object's collection of constraints.
2825 :param column: A single target column for the key relationship. A
2826 :class:`_schema.Column` object or a column name as a string:
2827 ``tablename.columnkey`` or ``schema.tablename.columnkey``.
2828 ``columnkey`` is the ``key`` which has been assigned to the column
2829 (defaults to the column name itself), unless ``link_to_name`` is
2830 ``True`` in which case the rendered name of the column is used.
2832 :param name: Optional string. An in-database name for the key if
2833 `constraint` is not provided.
2835 :param onupdate: Optional string. If set, emit ON UPDATE <value> when
2836 issuing DDL for this constraint. Typical values include CASCADE,
2837 DELETE and RESTRICT.
2839 .. seealso::
2841 :ref:`on_update_on_delete`
2843 :param ondelete: Optional string. If set, emit ON DELETE <value> when
2844 issuing DDL for this constraint. Typical values include CASCADE,
2845 SET NULL and RESTRICT. Some dialects may allow for additional
2846 syntaxes.
2848 .. seealso::
2850 :ref:`on_update_on_delete`
2852 :param deferrable: Optional bool. If set, emit DEFERRABLE or NOT
2853 DEFERRABLE when issuing DDL for this constraint.
2855 :param initially: Optional string. If set, emit INITIALLY <value> when
2856 issuing DDL for this constraint.
2858 :param link_to_name: if True, the string name given in ``column`` is
2859 the rendered name of the referenced column, not its locally
2860 assigned ``key``.
2862 :param use_alter: passed to the underlying
2863 :class:`_schema.ForeignKeyConstraint`
2864 to indicate the constraint should
2865 be generated/dropped externally from the CREATE TABLE/ DROP TABLE
2866 statement. See :paramref:`_schema.ForeignKeyConstraint.use_alter`
2867 for further description.
2869 .. seealso::
2871 :paramref:`_schema.ForeignKeyConstraint.use_alter`
2873 :ref:`use_alter`
2875 :param match: Optional string. If set, emit MATCH <value> when issuing
2876 DDL for this constraint. Typical values include SIMPLE, PARTIAL
2877 and FULL.
2879 :param info: Optional data dictionary which will be populated into the
2880 :attr:`.SchemaItem.info` attribute of this object.
2882 :param comment: Optional string that will render an SQL comment on
2883 foreign key constraint creation.
2885 .. versionadded:: 2.0
2887 :param \**dialect_kw: Additional keyword arguments are dialect
2888 specific, and passed in the form ``<dialectname>_<argname>``. The
2889 arguments are ultimately handled by a corresponding
2890 :class:`_schema.ForeignKeyConstraint`.
2891 See the documentation regarding
2892 an individual dialect at :ref:`dialect_toplevel` for detail on
2893 documented arguments.
2895 """
2897 self._colspec = coercions.expect(roles.DDLReferredColumnRole, column)
2898 self._unresolvable = _unresolvable
2900 if isinstance(self._colspec, str):
2901 self._table_column = None
2902 else:
2903 self._table_column = self._colspec
2905 if not isinstance(
2906 self._table_column.table, (type(None), TableClause)
2907 ):
2908 raise exc.ArgumentError(
2909 "ForeignKey received Column not bound "
2910 "to a Table, got: %r" % self._table_column.table
2911 )
2913 # the linked ForeignKeyConstraint.
2914 # ForeignKey will create this when parent Column
2915 # is attached to a Table, *or* ForeignKeyConstraint
2916 # object passes itself in when creating ForeignKey
2917 # markers.
2918 self.constraint = _constraint
2920 # .parent is not Optional under normal use
2921 self.parent = None # type: ignore
2923 self.use_alter = use_alter
2924 self.name = name
2925 self.onupdate = onupdate
2926 self.ondelete = ondelete
2927 self.deferrable = deferrable
2928 self.initially = initially
2929 self.link_to_name = link_to_name
2930 self.match = match
2931 self.comment = comment
2932 if info:
2933 self.info = info
2934 self._unvalidated_dialect_kw = dialect_kw
2936 def __repr__(self) -> str:
2937 return "ForeignKey(%r)" % self._get_colspec()
2939 @util.deprecated(
2940 "1.4",
2941 "The :meth:`_schema.ForeignKey.copy` method is deprecated "
2942 "and will be removed in a future release.",
2943 )
2944 def copy(self, *, schema: Optional[str] = None, **kw: Any) -> ForeignKey:
2945 return self._copy(schema=schema, **kw)
2947 def _copy(self, *, schema: Optional[str] = None, **kw: Any) -> ForeignKey:
2948 """Produce a copy of this :class:`_schema.ForeignKey` object.
2950 The new :class:`_schema.ForeignKey` will not be bound
2951 to any :class:`_schema.Column`.
2953 This method is usually used by the internal
2954 copy procedures of :class:`_schema.Column`, :class:`_schema.Table`,
2955 and :class:`_schema.MetaData`.
2957 :param schema: The returned :class:`_schema.ForeignKey` will
2958 reference the original table and column name, qualified
2959 by the given string schema name.
2961 """
2962 fk = ForeignKey(
2963 self._get_colspec(schema=schema),
2964 use_alter=self.use_alter,
2965 name=self.name,
2966 onupdate=self.onupdate,
2967 ondelete=self.ondelete,
2968 deferrable=self.deferrable,
2969 initially=self.initially,
2970 link_to_name=self.link_to_name,
2971 match=self.match,
2972 comment=self.comment,
2973 **self._unvalidated_dialect_kw,
2974 )
2975 return self._schema_item_copy(fk)
2977 def _get_colspec(
2978 self,
2979 schema: Optional[
2980 Union[
2981 str,
2982 Literal[SchemaConst.RETAIN_SCHEMA, SchemaConst.BLANK_SCHEMA],
2983 ]
2984 ] = None,
2985 table_name: Optional[str] = None,
2986 _is_copy: bool = False,
2987 ) -> str:
2988 """Return a string based 'column specification' for this
2989 :class:`_schema.ForeignKey`.
2991 This is usually the equivalent of the string-based "tablename.colname"
2992 argument first passed to the object's constructor.
2994 """
2995 if schema not in (None, RETAIN_SCHEMA):
2996 _schema, tname, colname = self._column_tokens
2997 if table_name is not None:
2998 tname = table_name
2999 if schema is BLANK_SCHEMA:
3000 return "%s.%s" % (tname, colname)
3001 else:
3002 return "%s.%s.%s" % (schema, tname, colname)
3003 elif table_name:
3004 schema, tname, colname = self._column_tokens
3005 if schema:
3006 return "%s.%s.%s" % (schema, table_name, colname)
3007 else:
3008 return "%s.%s" % (table_name, colname)
3009 elif self._table_column is not None:
3010 if self._table_column.table is None:
3011 if _is_copy:
3012 raise exc.InvalidRequestError(
3013 f"Can't copy ForeignKey object which refers to "
3014 f"non-table bound Column {self._table_column!r}"
3015 )
3016 else:
3017 return self._table_column.key
3018 return "%s.%s" % (
3019 self._table_column.table.fullname,
3020 self._table_column.key,
3021 )
3022 else:
3023 assert isinstance(self._colspec, str)
3024 return self._colspec
3026 @property
3027 def _referred_schema(self) -> Optional[str]:
3028 return self._column_tokens[0]
3030 def _table_key(self) -> Any:
3031 if self._table_column is not None:
3032 if self._table_column.table is None:
3033 return None
3034 else:
3035 return self._table_column.table.key
3036 else:
3037 schema, tname, colname = self._column_tokens
3038 return _get_table_key(tname, schema)
3040 target_fullname = property(_get_colspec)
3042 def references(self, table: Table) -> bool:
3043 """Return True if the given :class:`_schema.Table`
3044 is referenced by this
3045 :class:`_schema.ForeignKey`."""
3047 return table.corresponding_column(self.column) is not None
3049 def get_referent(self, table: FromClause) -> Optional[Column[Any]]:
3050 """Return the :class:`_schema.Column` in the given
3051 :class:`_schema.Table` (or any :class:`.FromClause`)
3052 referenced by this :class:`_schema.ForeignKey`.
3054 Returns None if this :class:`_schema.ForeignKey`
3055 does not reference the given
3056 :class:`_schema.Table`.
3058 """
3059 # our column is a Column, and any subquery etc. proxying us
3060 # would be doing so via another Column, so that's what would
3061 # be returned here
3062 return table.columns.corresponding_column(self.column) # type: ignore
3064 @util.memoized_property
3065 def _column_tokens(self) -> Tuple[Optional[str], str, Optional[str]]:
3066 """parse a string-based _colspec into its component parts."""
3068 m = self._get_colspec().split(".")
3069 if m is None:
3070 raise exc.ArgumentError(
3071 f"Invalid foreign key column specification: {self._colspec}"
3072 )
3073 if len(m) == 1:
3074 tname = m.pop()
3075 colname = None
3076 else:
3077 colname = m.pop()
3078 tname = m.pop()
3080 # A FK between column 'bar' and table 'foo' can be
3081 # specified as 'foo', 'foo.bar', 'dbo.foo.bar',
3082 # 'otherdb.dbo.foo.bar'. Once we have the column name and
3083 # the table name, treat everything else as the schema
3084 # name. Some databases (e.g. Sybase) support
3085 # inter-database foreign keys. See tickets#1341 and --
3086 # indirectly related -- Ticket #594. This assumes that '.'
3087 # will never appear *within* any component of the FK.
3089 if len(m) > 0:
3090 schema = ".".join(m)
3091 else:
3092 schema = None
3093 return schema, tname, colname
3095 def _resolve_col_tokens(self) -> Tuple[Table, str, Optional[str]]:
3096 if self.parent is None:
3097 raise exc.InvalidRequestError(
3098 "this ForeignKey object does not yet have a "
3099 "parent Column associated with it."
3100 )
3102 elif self.parent.table is None:
3103 raise exc.InvalidRequestError(
3104 "this ForeignKey's parent column is not yet associated "
3105 "with a Table."
3106 )
3108 parenttable = self.parent.table
3110 if self._unresolvable:
3111 schema, tname, colname = self._column_tokens
3112 tablekey = _get_table_key(tname, schema)
3113 return parenttable, tablekey, colname
3115 # assertion
3116 # basically Column._make_proxy() sends the actual
3117 # target Column to the ForeignKey object, so the
3118 # string resolution here is never called.
3119 for c in self.parent.base_columns:
3120 if isinstance(c, Column):
3121 assert c.table is parenttable
3122 break
3123 else:
3124 assert False
3125 ######################
3127 schema, tname, colname = self._column_tokens
3129 if schema is None and parenttable.metadata.schema is not None:
3130 schema = parenttable.metadata.schema
3132 tablekey = _get_table_key(tname, schema)
3133 return parenttable, tablekey, colname
3135 def _link_to_col_by_colstring(
3136 self, parenttable: Table, table: Table, colname: Optional[str]
3137 ) -> Column[Any]:
3138 _column = None
3139 if colname is None:
3140 # colname is None in the case that ForeignKey argument
3141 # was specified as table name only, in which case we
3142 # match the column name to the same column on the
3143 # parent.
3144 # this use case wasn't working in later 1.x series
3145 # as it had no test coverage; fixed in 2.0
3146 parent = self.parent
3147 assert parent is not None
3148 key = parent.key
3149 _column = table.c.get(key, None)
3150 elif self.link_to_name:
3151 key = colname
3152 for c in table.c:
3153 if c.name == colname:
3154 _column = c
3155 else:
3156 key = colname
3157 _column = table.c.get(colname, None)
3159 if _column is None:
3160 raise exc.NoReferencedColumnError(
3161 "Could not initialize target column "
3162 f"for ForeignKey '{self._colspec}' "
3163 f"on table '{parenttable.name}': "
3164 f"table '{table.name}' has no column named '{key}'",
3165 table.name,
3166 key,
3167 )
3169 return _column
3171 def _set_target_column(self, column: Column[Any]) -> None:
3172 assert self.parent is not None
3174 # propagate TypeEngine to parent if it didn't have one
3175 if self.parent.type._isnull:
3176 self.parent.type = column.type
3178 # super-edgy case, if other FKs point to our column,
3179 # they'd get the type propagated out also.
3181 def set_type(fk: ForeignKey) -> None:
3182 if fk.parent.type._isnull:
3183 fk.parent.type = column.type
3185 self.parent._setup_on_memoized_fks(set_type)
3187 self.column = column # type: ignore
3189 @util.ro_memoized_property
3190 def column(self) -> Column[Any]:
3191 """Return the target :class:`_schema.Column` referenced by this
3192 :class:`_schema.ForeignKey`.
3194 If no target column has been established, an exception
3195 is raised.
3197 """
3199 return self._resolve_column()
3201 @overload
3202 def _resolve_column(
3203 self, *, raiseerr: Literal[True] = ...
3204 ) -> Column[Any]: ...
3206 @overload
3207 def _resolve_column(
3208 self, *, raiseerr: bool = ...
3209 ) -> Optional[Column[Any]]: ...
3211 def _resolve_column(
3212 self, *, raiseerr: bool = True
3213 ) -> Optional[Column[Any]]:
3214 _column: Column[Any]
3216 if isinstance(self._colspec, str):
3217 parenttable, tablekey, colname = self._resolve_col_tokens()
3219 if self._unresolvable or tablekey not in parenttable.metadata:
3220 if not raiseerr:
3221 return None
3222 raise exc.NoReferencedTableError(
3223 f"Foreign key associated with column "
3224 f"'{self.parent}' could not find "
3225 f"table '{tablekey}' with which to generate a "
3226 f"foreign key to target column '{colname}'",
3227 tablekey,
3228 )
3229 elif parenttable.key not in parenttable.metadata:
3230 if not raiseerr:
3231 return None
3232 raise exc.InvalidRequestError(
3233 f"Table {parenttable} is no longer associated with its "
3234 "parent MetaData"
3235 )
3236 else:
3237 table = parenttable.metadata.tables[tablekey]
3238 return self._link_to_col_by_colstring(
3239 parenttable, table, colname
3240 )
3242 elif hasattr(self._colspec, "__clause_element__"):
3243 _column = self._colspec.__clause_element__()
3244 return _column
3245 else:
3246 _column = self._colspec
3247 return _column
3249 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
3250 assert isinstance(parent, Column)
3252 if self.parent is not None and self.parent is not parent:
3253 raise exc.InvalidRequestError(
3254 "This ForeignKey already has a parent !"
3255 )
3256 self.parent = parent
3257 self.parent.foreign_keys.add(self)
3258 self.parent._on_table_attach(self._set_table)
3260 def _set_remote_table(self, table: Table) -> None:
3261 parenttable, _, colname = self._resolve_col_tokens()
3262 _column = self._link_to_col_by_colstring(parenttable, table, colname)
3263 self._set_target_column(_column)
3264 assert self.constraint is not None
3265 self.constraint._validate_dest_table(table)
3267 def _remove_from_metadata(self, metadata: MetaData) -> None:
3268 parenttable, table_key, colname = self._resolve_col_tokens()
3269 fk_key = (table_key, colname)
3271 if self in metadata._fk_memos[fk_key]:
3272 # TODO: no test coverage for self not in memos
3273 metadata._fk_memos[fk_key].remove(self)
3275 def _set_table(self, column: Column[Any], table: Table) -> None:
3276 # standalone ForeignKey - create ForeignKeyConstraint
3277 # on the hosting Table when attached to the Table.
3278 assert isinstance(table, Table)
3279 if self.constraint is None:
3280 self.constraint = ForeignKeyConstraint(
3281 [],
3282 [],
3283 use_alter=self.use_alter,
3284 name=self.name,
3285 onupdate=self.onupdate,
3286 ondelete=self.ondelete,
3287 deferrable=self.deferrable,
3288 initially=self.initially,
3289 match=self.match,
3290 comment=self.comment,
3291 **self._unvalidated_dialect_kw,
3292 )
3293 self.constraint._append_element(column, self)
3294 self.constraint._set_parent_with_dispatch(table)
3295 table.foreign_keys.add(self)
3296 # set up remote ".column" attribute, or a note to pick it
3297 # up when the other Table/Column shows up
3298 if isinstance(self._colspec, str):
3299 parenttable, table_key, colname = self._resolve_col_tokens()
3300 fk_key = (table_key, colname)
3301 if table_key in parenttable.metadata.tables:
3302 table = parenttable.metadata.tables[table_key]
3303 try:
3304 _column = self._link_to_col_by_colstring(
3305 parenttable, table, colname
3306 )
3307 except exc.NoReferencedColumnError:
3308 # this is OK, we'll try later
3309 pass
3310 else:
3311 self._set_target_column(_column)
3313 parenttable.metadata._fk_memos[fk_key].append(self)
3314 elif hasattr(self._colspec, "__clause_element__"):
3315 _column = self._colspec.__clause_element__()
3316 self._set_target_column(_column)
3317 else:
3318 _column = self._colspec
3319 self._set_target_column(_column)
3322if TYPE_CHECKING:
3324 def default_is_sequence(
3325 obj: Optional[DefaultGenerator],
3326 ) -> TypeGuard[Sequence]: ...
3328 def default_is_clause_element(
3329 obj: Optional[DefaultGenerator],
3330 ) -> TypeGuard[ColumnElementColumnDefault]: ...
3332 def default_is_scalar(
3333 obj: Optional[DefaultGenerator],
3334 ) -> TypeGuard[ScalarElementColumnDefault]: ...
3336else:
3337 default_is_sequence = operator.attrgetter("is_sequence")
3339 default_is_clause_element = operator.attrgetter("is_clause_element")
3341 default_is_scalar = operator.attrgetter("is_scalar")
3344class DefaultGenerator(Executable, SchemaItem):
3345 """Base class for column *default* values.
3347 This object is only present on column.default or column.onupdate.
3348 It's not valid as a server default.
3350 """
3352 __visit_name__ = "default_generator"
3354 _is_default_generator = True
3355 is_sequence = False
3356 is_identity = False
3357 is_server_default = False
3358 is_clause_element = False
3359 is_callable = False
3360 is_scalar = False
3361 has_arg = False
3362 is_sentinel = False
3363 column: Optional[Column[Any]]
3365 def __init__(self, for_update: bool = False) -> None:
3366 self.for_update = for_update
3368 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
3369 if TYPE_CHECKING:
3370 assert isinstance(parent, Column)
3371 self.column = parent
3372 if self.for_update:
3373 self.column.onupdate = self
3374 else:
3375 self.column.default = self
3377 def _copy(self) -> DefaultGenerator:
3378 raise NotImplementedError()
3380 def _execute_on_connection(
3381 self,
3382 connection: Connection,
3383 distilled_params: _CoreMultiExecuteParams,
3384 execution_options: CoreExecuteOptionsParameter,
3385 ) -> Any:
3386 util.warn_deprecated(
3387 "Using the .execute() method to invoke a "
3388 "DefaultGenerator object is deprecated; please use "
3389 "the .scalar() method.",
3390 "2.0",
3391 )
3392 return self._execute_on_scalar(
3393 connection, distilled_params, execution_options
3394 )
3396 def _execute_on_scalar(
3397 self,
3398 connection: Connection,
3399 distilled_params: _CoreMultiExecuteParams,
3400 execution_options: CoreExecuteOptionsParameter,
3401 ) -> Any:
3402 return connection._execute_default(
3403 self, distilled_params, execution_options
3404 )
3407class ColumnDefault(DefaultGenerator, ABC):
3408 """A plain default value on a column.
3410 This could correspond to a constant, a callable function,
3411 or a SQL clause.
3413 :class:`.ColumnDefault` is generated automatically
3414 whenever the ``default``, ``onupdate`` arguments of
3415 :class:`_schema.Column` are used. A :class:`.ColumnDefault`
3416 can be passed positionally as well.
3418 For example, the following::
3420 Column("foo", Integer, default=50)
3422 Is equivalent to::
3424 Column("foo", Integer, ColumnDefault(50))
3426 """
3428 arg: Any
3430 @overload
3431 def __new__(
3432 cls, arg: Callable[..., Any], for_update: bool = ...
3433 ) -> CallableColumnDefault: ...
3435 @overload
3436 def __new__(
3437 cls, arg: ColumnElement[Any], for_update: bool = ...
3438 ) -> ColumnElementColumnDefault: ...
3440 # if I return ScalarElementColumnDefault here, which is what's actually
3441 # returned, mypy complains that
3442 # overloads overlap w/ incompatible return types.
3443 @overload
3444 def __new__(cls, arg: object, for_update: bool = ...) -> ColumnDefault: ...
3446 def __new__(
3447 cls, arg: Any = None, for_update: bool = False
3448 ) -> ColumnDefault:
3449 """Construct a new :class:`.ColumnDefault`.
3452 :param arg: argument representing the default value.
3453 May be one of the following:
3455 * a plain non-callable Python value, such as a
3456 string, integer, boolean, or other simple type.
3457 The default value will be used as is each time.
3458 * a SQL expression, that is one which derives from
3459 :class:`_expression.ColumnElement`. The SQL expression will
3460 be rendered into the INSERT or UPDATE statement,
3461 or in the case of a primary key column when
3462 RETURNING is not used may be
3463 pre-executed before an INSERT within a SELECT.
3464 * A Python callable. The function will be invoked for each
3465 new row subject to an INSERT or UPDATE.
3466 The callable must accept exactly
3467 zero or one positional arguments. The one-argument form
3468 will receive an instance of the :class:`.ExecutionContext`,
3469 which provides contextual information as to the current
3470 :class:`_engine.Connection` in use as well as the current
3471 statement and parameters.
3473 """
3475 if isinstance(arg, FetchedValue):
3476 raise exc.ArgumentError(
3477 "ColumnDefault may not be a server-side default type."
3478 )
3479 elif callable(arg):
3480 cls = CallableColumnDefault
3481 elif isinstance(arg, ClauseElement):
3482 cls = ColumnElementColumnDefault
3483 elif arg is not None:
3484 cls = ScalarElementColumnDefault
3486 return object.__new__(cls)
3488 def __repr__(self) -> str:
3489 return f"{self.__class__.__name__}({self.arg!r})"
3492class ScalarElementColumnDefault(ColumnDefault):
3493 """default generator for a fixed scalar Python value
3495 .. versionadded: 2.0
3497 """
3499 is_scalar = True
3500 has_arg = True
3502 def __init__(self, arg: Any, for_update: bool = False) -> None:
3503 self.for_update = for_update
3504 self.arg = arg
3506 def _copy(self) -> ScalarElementColumnDefault:
3507 return ScalarElementColumnDefault(
3508 arg=self.arg, for_update=self.for_update
3509 )
3512class _InsertSentinelColumnDefault(ColumnDefault):
3513 """Default generator that's specific to the use of a "sentinel" column
3514 when using the insertmanyvalues feature.
3516 This default is used as part of the :func:`_schema.insert_sentinel`
3517 construct.
3519 """
3521 is_sentinel = True
3522 for_update = False
3523 arg = None
3525 def __new__(cls) -> _InsertSentinelColumnDefault:
3526 return object.__new__(cls)
3528 def __init__(self) -> None:
3529 pass
3531 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
3532 col = cast("Column[Any]", parent)
3533 if not col._insert_sentinel:
3534 raise exc.ArgumentError(
3535 "The _InsertSentinelColumnDefault may only be applied to a "
3536 "Column marked as insert_sentinel=True"
3537 )
3538 elif not col.nullable:
3539 raise exc.ArgumentError(
3540 "The _InsertSentinelColumnDefault may only be applied to a "
3541 "Column that is nullable"
3542 )
3544 super()._set_parent(parent, **kw)
3546 def _copy(self) -> _InsertSentinelColumnDefault:
3547 return _InsertSentinelColumnDefault()
3550_SQLExprDefault = Union["ColumnElement[Any]", "TextClause"]
3553class ColumnElementColumnDefault(ColumnDefault):
3554 """default generator for a SQL expression
3556 .. versionadded:: 2.0
3558 """
3560 is_clause_element = True
3561 has_arg = True
3562 arg: _SQLExprDefault
3564 def __init__(
3565 self,
3566 arg: _SQLExprDefault,
3567 for_update: bool = False,
3568 ) -> None:
3569 self.for_update = for_update
3570 self.arg = arg
3572 def _copy(self) -> ColumnElementColumnDefault:
3573 return ColumnElementColumnDefault(
3574 arg=self.arg, for_update=self.for_update
3575 )
3577 @util.memoized_property
3578 @util.preload_module("sqlalchemy.sql.sqltypes")
3579 def _arg_is_typed(self) -> bool:
3580 sqltypes = util.preloaded.sql_sqltypes
3582 return not isinstance(self.arg.type, sqltypes.NullType)
3585class _CallableColumnDefaultProtocol(Protocol):
3586 def __call__(self, context: ExecutionContext) -> Any: ...
3589class CallableColumnDefault(ColumnDefault):
3590 """default generator for a callable Python function
3592 .. versionadded:: 2.0
3594 """
3596 is_callable = True
3597 arg: _CallableColumnDefaultProtocol
3598 has_arg = True
3600 def __init__(
3601 self,
3602 arg: Union[_CallableColumnDefaultProtocol, Callable[[], Any]],
3603 for_update: bool = False,
3604 ) -> None:
3605 self.for_update = for_update
3606 self.arg = self._maybe_wrap_callable(arg)
3608 def _copy(self) -> CallableColumnDefault:
3609 return CallableColumnDefault(arg=self.arg, for_update=self.for_update)
3611 def _maybe_wrap_callable(
3612 self, fn: Union[_CallableColumnDefaultProtocol, Callable[[], Any]]
3613 ) -> _CallableColumnDefaultProtocol:
3614 """Wrap callables that don't accept a context.
3616 This is to allow easy compatibility with default callables
3617 that aren't specific to accepting of a context.
3619 """
3621 try:
3622 argspec = util.get_callable_argspec(fn, no_self=True)
3623 except TypeError:
3624 return util.wrap_callable(lambda ctx: fn(), fn) # type: ignore
3626 defaulted = argspec[3] is not None and len(argspec[3]) or 0
3627 positionals = len(argspec[0]) - defaulted
3629 if positionals == 0:
3630 return util.wrap_callable(lambda ctx: fn(), fn) # type: ignore
3632 elif positionals == 1:
3633 return fn # type: ignore
3634 else:
3635 raise exc.ArgumentError(
3636 "ColumnDefault Python function takes zero or one "
3637 "positional arguments"
3638 )
3641class IdentityOptions:
3642 """Defines options for a named database sequence or an identity column.
3644 .. versionadded:: 1.3.18
3646 .. seealso::
3648 :class:`.Sequence`
3650 """
3652 def __init__(
3653 self,
3654 start: Optional[int] = None,
3655 increment: Optional[int] = None,
3656 minvalue: Optional[int] = None,
3657 maxvalue: Optional[int] = None,
3658 nominvalue: Optional[bool] = None,
3659 nomaxvalue: Optional[bool] = None,
3660 cycle: Optional[bool] = None,
3661 cache: Optional[int] = None,
3662 order: Optional[bool] = None,
3663 ) -> None:
3664 """Construct a :class:`.IdentityOptions` object.
3666 See the :class:`.Sequence` documentation for a complete description
3667 of the parameters.
3669 :param start: the starting index of the sequence.
3670 :param increment: the increment value of the sequence.
3671 :param minvalue: the minimum value of the sequence.
3672 :param maxvalue: the maximum value of the sequence.
3673 :param nominvalue: no minimum value of the sequence.
3674 :param nomaxvalue: no maximum value of the sequence.
3675 :param cycle: allows the sequence to wrap around when the maxvalue
3676 or minvalue has been reached.
3677 :param cache: optional integer value; number of future values in the
3678 sequence which are calculated in advance.
3679 :param order: optional boolean value; if ``True``, renders the
3680 ORDER keyword.
3682 """
3683 self.start = start
3684 self.increment = increment
3685 self.minvalue = minvalue
3686 self.maxvalue = maxvalue
3687 self.nominvalue = nominvalue
3688 self.nomaxvalue = nomaxvalue
3689 self.cycle = cycle
3690 self.cache = cache
3691 self.order = order
3693 @property
3694 def _increment_is_negative(self) -> bool:
3695 return self.increment is not None and self.increment < 0
3698class Sequence(HasSchemaAttr, IdentityOptions, DefaultGenerator):
3699 """Represents a named database sequence.
3701 The :class:`.Sequence` object represents the name and configurational
3702 parameters of a database sequence. It also represents
3703 a construct that can be "executed" by a SQLAlchemy :class:`_engine.Engine`
3704 or :class:`_engine.Connection`,
3705 rendering the appropriate "next value" function
3706 for the target database and returning a result.
3708 The :class:`.Sequence` is typically associated with a primary key column::
3710 some_table = Table(
3711 "some_table",
3712 metadata,
3713 Column(
3714 "id",
3715 Integer,
3716 Sequence("some_table_seq", start=1),
3717 primary_key=True,
3718 ),
3719 )
3721 When CREATE TABLE is emitted for the above :class:`_schema.Table`, if the
3722 target platform supports sequences, a CREATE SEQUENCE statement will
3723 be emitted as well. For platforms that don't support sequences,
3724 the :class:`.Sequence` construct is ignored.
3726 .. seealso::
3728 :ref:`defaults_sequences`
3730 :class:`.CreateSequence`
3732 :class:`.DropSequence`
3734 """
3736 __visit_name__ = "sequence"
3738 is_sequence = True
3740 column: Optional[Column[Any]]
3741 data_type: Optional[TypeEngine[int]]
3743 def __init__(
3744 self,
3745 name: str,
3746 start: Optional[int] = None,
3747 increment: Optional[int] = None,
3748 minvalue: Optional[int] = None,
3749 maxvalue: Optional[int] = None,
3750 nominvalue: Optional[bool] = None,
3751 nomaxvalue: Optional[bool] = None,
3752 cycle: Optional[bool] = None,
3753 schema: Optional[Union[str, Literal[SchemaConst.BLANK_SCHEMA]]] = None,
3754 cache: Optional[int] = None,
3755 order: Optional[bool] = None,
3756 data_type: Optional[_TypeEngineArgument[int]] = None,
3757 optional: bool = False,
3758 quote: Optional[bool] = None,
3759 metadata: Optional[MetaData] = None,
3760 quote_schema: Optional[bool] = None,
3761 for_update: bool = False,
3762 ) -> None:
3763 """Construct a :class:`.Sequence` object.
3765 :param name: the name of the sequence.
3767 :param start: the starting index of the sequence. This value is
3768 used when the CREATE SEQUENCE command is emitted to the database
3769 as the value of the "START WITH" clause. If ``None``, the
3770 clause is omitted, which on most platforms indicates a starting
3771 value of 1.
3773 .. versionchanged:: 2.0 The :paramref:`.Sequence.start` parameter
3774 is required in order to have DDL emit "START WITH". This is a
3775 reversal of a change made in version 1.4 which would implicitly
3776 render "START WITH 1" if the :paramref:`.Sequence.start` were
3777 not included. See :ref:`change_7211` for more detail.
3779 :param increment: the increment value of the sequence. This
3780 value is used when the CREATE SEQUENCE command is emitted to
3781 the database as the value of the "INCREMENT BY" clause. If ``None``,
3782 the clause is omitted, which on most platforms indicates an
3783 increment of 1.
3784 :param minvalue: the minimum value of the sequence. This
3785 value is used when the CREATE SEQUENCE command is emitted to
3786 the database as the value of the "MINVALUE" clause. If ``None``,
3787 the clause is omitted, which on most platforms indicates a
3788 minvalue of 1 and -2^63-1 for ascending and descending sequences,
3789 respectively.
3791 :param maxvalue: the maximum value of the sequence. This
3792 value is used when the CREATE SEQUENCE command is emitted to
3793 the database as the value of the "MAXVALUE" clause. If ``None``,
3794 the clause is omitted, which on most platforms indicates a
3795 maxvalue of 2^63-1 and -1 for ascending and descending sequences,
3796 respectively.
3798 :param nominvalue: no minimum value of the sequence. This
3799 value is used when the CREATE SEQUENCE command is emitted to
3800 the database as the value of the "NO MINVALUE" clause. If ``None``,
3801 the clause is omitted, which on most platforms indicates a
3802 minvalue of 1 and -2^63-1 for ascending and descending sequences,
3803 respectively.
3805 :param nomaxvalue: no maximum value of the sequence. This
3806 value is used when the CREATE SEQUENCE command is emitted to
3807 the database as the value of the "NO MAXVALUE" clause. If ``None``,
3808 the clause is omitted, which on most platforms indicates a
3809 maxvalue of 2^63-1 and -1 for ascending and descending sequences,
3810 respectively.
3812 :param cycle: allows the sequence to wrap around when the maxvalue
3813 or minvalue has been reached by an ascending or descending sequence
3814 respectively. This value is used when the CREATE SEQUENCE command
3815 is emitted to the database as the "CYCLE" clause. If the limit is
3816 reached, the next number generated will be the minvalue or maxvalue,
3817 respectively. If cycle=False (the default) any calls to nextval
3818 after the sequence has reached its maximum value will return an
3819 error.
3821 :param schema: optional schema name for the sequence, if located
3822 in a schema other than the default. The rules for selecting the
3823 schema name when a :class:`_schema.MetaData`
3824 is also present are the same
3825 as that of :paramref:`_schema.Table.schema`.
3827 :param cache: optional integer value; number of future values in the
3828 sequence which are calculated in advance. Renders the CACHE keyword
3829 understood by Oracle Database and PostgreSQL.
3831 :param order: optional boolean value; if ``True``, renders the
3832 ORDER keyword, understood by Oracle Database, indicating the sequence
3833 is definitively ordered. May be necessary to provide deterministic
3834 ordering using Oracle RAC.
3836 :param data_type: The type to be returned by the sequence, for
3837 dialects that allow us to choose between INTEGER, BIGINT, etc.
3838 (e.g., mssql).
3840 .. versionadded:: 1.4.0
3842 :param optional: boolean value, when ``True``, indicates that this
3843 :class:`.Sequence` object only needs to be explicitly generated
3844 on backends that don't provide another way to generate primary
3845 key identifiers. Currently, it essentially means, "don't create
3846 this sequence on the PostgreSQL backend, where the SERIAL keyword
3847 creates a sequence for us automatically".
3848 :param quote: boolean value, when ``True`` or ``False``, explicitly
3849 forces quoting of the :paramref:`_schema.Sequence.name` on or off.
3850 When left at its default of ``None``, normal quoting rules based
3851 on casing and reserved words take place.
3852 :param quote_schema: Set the quoting preferences for the ``schema``
3853 name.
3855 :param metadata: optional :class:`_schema.MetaData` object which this
3856 :class:`.Sequence` will be associated with. A :class:`.Sequence`
3857 that is associated with a :class:`_schema.MetaData`
3858 gains the following
3859 capabilities:
3861 * The :class:`.Sequence` will inherit the
3862 :paramref:`_schema.MetaData.schema`
3863 parameter specified to the target :class:`_schema.MetaData`, which
3864 affects the production of CREATE / DROP DDL, if any.
3866 * The :meth:`.Sequence.create` and :meth:`.Sequence.drop` methods
3867 automatically use the engine bound to the :class:`_schema.MetaData`
3868 object, if any.
3870 * The :meth:`_schema.MetaData.create_all` and
3871 :meth:`_schema.MetaData.drop_all`
3872 methods will emit CREATE / DROP for this :class:`.Sequence`,
3873 even if the :class:`.Sequence` is not associated with any
3874 :class:`_schema.Table` / :class:`_schema.Column`
3875 that's a member of this
3876 :class:`_schema.MetaData`.
3878 The above behaviors can only occur if the :class:`.Sequence` is
3879 explicitly associated with the :class:`_schema.MetaData`
3880 via this parameter.
3882 .. seealso::
3884 :ref:`sequence_metadata` - full discussion of the
3885 :paramref:`.Sequence.metadata` parameter.
3887 :param for_update: Indicates this :class:`.Sequence`, when associated
3888 with a :class:`_schema.Column`,
3889 should be invoked for UPDATE statements
3890 on that column's table, rather than for INSERT statements, when
3891 no value is otherwise present for that column in the statement.
3893 """
3894 DefaultGenerator.__init__(self, for_update=for_update)
3895 IdentityOptions.__init__(
3896 self,
3897 start=start,
3898 increment=increment,
3899 minvalue=minvalue,
3900 maxvalue=maxvalue,
3901 nominvalue=nominvalue,
3902 nomaxvalue=nomaxvalue,
3903 cycle=cycle,
3904 cache=cache,
3905 order=order,
3906 )
3907 self.column = None
3908 self.name = quoted_name(name, quote)
3909 self.optional = optional
3910 if schema is BLANK_SCHEMA:
3911 self.schema = schema = None
3912 elif metadata is not None and schema is None and metadata.schema:
3913 self.schema = schema = metadata.schema
3914 else:
3915 self.schema = quoted_name.construct(schema, quote_schema)
3916 self.metadata = metadata
3917 self._key = _get_table_key(name, schema)
3918 if metadata:
3919 self._set_metadata(metadata)
3920 if data_type is not None:
3921 self.data_type = to_instance(data_type)
3922 else:
3923 self.data_type = None
3925 @util.preload_module("sqlalchemy.sql.functions")
3926 def next_value(self) -> Function[int]:
3927 """Return a :class:`.next_value` function element
3928 which will render the appropriate increment function
3929 for this :class:`.Sequence` within any SQL expression.
3931 """
3932 return util.preloaded.sql_functions.func.next_value(self)
3934 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
3935 column = parent
3936 assert isinstance(column, Column)
3937 super()._set_parent(column)
3938 column._on_table_attach(self._set_table)
3940 def _copy(self) -> Sequence:
3941 return Sequence(
3942 name=self.name,
3943 start=self.start,
3944 increment=self.increment,
3945 minvalue=self.minvalue,
3946 maxvalue=self.maxvalue,
3947 nominvalue=self.nominvalue,
3948 nomaxvalue=self.nomaxvalue,
3949 cycle=self.cycle,
3950 schema=self.schema,
3951 cache=self.cache,
3952 order=self.order,
3953 data_type=self.data_type,
3954 optional=self.optional,
3955 metadata=self.metadata,
3956 for_update=self.for_update,
3957 )
3959 def _set_table(self, column: Column[Any], table: Table) -> None:
3960 self._set_metadata(table.metadata)
3962 def _set_metadata(self, metadata: MetaData) -> None:
3963 self.metadata = metadata
3964 self.metadata._sequences[self._key] = self
3966 def create(self, bind: _CreateDropBind, checkfirst: bool = True) -> None:
3967 """Creates this sequence in the database."""
3969 bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
3971 def drop(self, bind: _CreateDropBind, checkfirst: bool = True) -> None:
3972 """Drops this sequence from the database."""
3974 bind._run_ddl_visitor(ddl.SchemaDropper, self, checkfirst=checkfirst)
3976 def _not_a_column_expr(self) -> NoReturn:
3977 raise exc.InvalidRequestError(
3978 f"This {self.__class__.__name__} cannot be used directly "
3979 "as a column expression. Use func.next_value(sequence) "
3980 "to produce a 'next value' function that's usable "
3981 "as a column element."
3982 )
3985@inspection._self_inspects
3986class FetchedValue(SchemaEventTarget):
3987 """A marker for a transparent database-side default.
3989 Use :class:`.FetchedValue` when the database is configured
3990 to provide some automatic default for a column.
3992 E.g.::
3994 Column("foo", Integer, FetchedValue())
3996 Would indicate that some trigger or default generator
3997 will create a new value for the ``foo`` column during an
3998 INSERT.
4000 .. seealso::
4002 :ref:`triggered_columns`
4004 """
4006 is_server_default = True
4007 reflected = False
4008 has_argument = False
4009 is_clause_element = False
4010 is_identity = False
4012 column: Optional[Column[Any]]
4014 def __init__(self, for_update: bool = False) -> None:
4015 self.for_update = for_update
4017 def _as_for_update(self, for_update: bool) -> FetchedValue:
4018 if for_update == self.for_update:
4019 return self
4020 else:
4021 return self._clone(for_update)
4023 def _copy(self) -> FetchedValue:
4024 return FetchedValue(self.for_update)
4026 def _clone(self, for_update: bool) -> Self:
4027 n = self.__class__.__new__(self.__class__)
4028 n.__dict__.update(self.__dict__)
4029 n.__dict__.pop("column", None)
4030 n.for_update = for_update
4031 return n
4033 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4034 column = parent
4035 assert isinstance(column, Column)
4036 self.column = column
4037 if self.for_update:
4038 self.column.server_onupdate = self
4039 else:
4040 self.column.server_default = self
4042 def __repr__(self) -> str:
4043 return util.generic_repr(self)
4046class DefaultClause(FetchedValue):
4047 """A DDL-specified DEFAULT column value.
4049 :class:`.DefaultClause` is a :class:`.FetchedValue`
4050 that also generates a "DEFAULT" clause when
4051 "CREATE TABLE" is emitted.
4053 :class:`.DefaultClause` is generated automatically
4054 whenever the ``server_default``, ``server_onupdate`` arguments of
4055 :class:`_schema.Column` are used. A :class:`.DefaultClause`
4056 can be passed positionally as well.
4058 For example, the following::
4060 Column("foo", Integer, server_default="50")
4062 Is equivalent to::
4064 Column("foo", Integer, DefaultClause("50"))
4066 """
4068 has_argument = True
4070 def __init__(
4071 self,
4072 arg: Union[str, ClauseElement, TextClause],
4073 for_update: bool = False,
4074 _reflected: bool = False,
4075 ) -> None:
4076 util.assert_arg_type(arg, (str, ClauseElement, TextClause), "arg")
4077 super().__init__(for_update)
4078 self.arg = arg
4079 self.reflected = _reflected
4081 def _copy(self) -> DefaultClause:
4082 return DefaultClause(
4083 arg=self.arg, for_update=self.for_update, _reflected=self.reflected
4084 )
4086 def __repr__(self) -> str:
4087 return "DefaultClause(%r, for_update=%r)" % (self.arg, self.for_update)
4090class Constraint(DialectKWArgs, HasConditionalDDL, SchemaItem):
4091 """A table-level SQL constraint.
4093 :class:`_schema.Constraint` serves as the base class for the series of
4094 constraint objects that can be associated with :class:`_schema.Table`
4095 objects, including :class:`_schema.PrimaryKeyConstraint`,
4096 :class:`_schema.ForeignKeyConstraint`
4097 :class:`_schema.UniqueConstraint`, and
4098 :class:`_schema.CheckConstraint`.
4100 """
4102 __visit_name__ = "constraint"
4104 _creation_order: int
4105 _column_flag: bool
4107 def __init__(
4108 self,
4109 name: _ConstraintNameArgument = None,
4110 deferrable: Optional[bool] = None,
4111 initially: Optional[str] = None,
4112 info: Optional[_InfoType] = None,
4113 comment: Optional[str] = None,
4114 _create_rule: Optional[Any] = None,
4115 _type_bound: bool = False,
4116 **dialect_kw: Any,
4117 ) -> None:
4118 r"""Create a SQL constraint.
4120 :param name:
4121 Optional, the in-database name of this ``Constraint``.
4123 :param deferrable:
4124 Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
4125 issuing DDL for this constraint.
4127 :param initially:
4128 Optional string. If set, emit INITIALLY <value> when issuing DDL
4129 for this constraint.
4131 :param info: Optional data dictionary which will be populated into the
4132 :attr:`.SchemaItem.info` attribute of this object.
4134 :param comment: Optional string that will render an SQL comment on
4135 foreign key constraint creation.
4137 .. versionadded:: 2.0
4139 :param \**dialect_kw: Additional keyword arguments are dialect
4140 specific, and passed in the form ``<dialectname>_<argname>``. See
4141 the documentation regarding an individual dialect at
4142 :ref:`dialect_toplevel` for detail on documented arguments.
4144 :param _create_rule:
4145 used internally by some datatypes that also create constraints.
4147 :param _type_bound:
4148 used internally to indicate that this constraint is associated with
4149 a specific datatype.
4151 """
4153 self.name = name
4154 self.deferrable = deferrable
4155 self.initially = initially
4156 if info:
4157 self.info = info
4158 self._create_rule = _create_rule
4159 self._type_bound = _type_bound
4160 util.set_creation_order(self)
4161 self._validate_dialect_kwargs(dialect_kw)
4162 self.comment = comment
4164 def _should_create_for_compiler(
4165 self, compiler: DDLCompiler, **kw: Any
4166 ) -> bool:
4167 if self._create_rule is not None and not self._create_rule(compiler):
4168 return False
4169 elif self._ddl_if is not None:
4170 return self._ddl_if._should_execute(
4171 ddl.CreateConstraint(self), self, None, compiler=compiler, **kw
4172 )
4173 else:
4174 return True
4176 @property
4177 def table(self) -> Table:
4178 try:
4179 if isinstance(self.parent, Table):
4180 return self.parent
4181 except AttributeError:
4182 pass
4183 raise exc.InvalidRequestError(
4184 "This constraint is not bound to a table. Did you "
4185 "mean to call table.append_constraint(constraint) ?"
4186 )
4188 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4189 assert isinstance(parent, (Table, Column))
4190 self.parent = parent
4191 parent.constraints.add(self)
4193 @util.deprecated(
4194 "1.4",
4195 "The :meth:`_schema.Constraint.copy` method is deprecated "
4196 "and will be removed in a future release.",
4197 )
4198 def copy(self, **kw: Any) -> Self:
4199 return self._copy(**kw)
4201 def _copy(self, **kw: Any) -> Self:
4202 raise NotImplementedError()
4205class ColumnCollectionMixin:
4206 """A :class:`_expression.ColumnCollection` of :class:`_schema.Column`
4207 objects.
4209 This collection represents the columns which are referred to by
4210 this object.
4212 """
4214 _columns: DedupeColumnCollection[Column[Any]]
4216 _allow_multiple_tables = False
4218 _pending_colargs: List[Optional[Union[str, Column[Any]]]]
4220 if TYPE_CHECKING:
4222 def _set_parent_with_dispatch(
4223 self, parent: SchemaEventTarget, **kw: Any
4224 ) -> None: ...
4226 def __init__(
4227 self,
4228 *columns: _DDLColumnArgument,
4229 _autoattach: bool = True,
4230 _column_flag: bool = False,
4231 _gather_expressions: Optional[
4232 List[Union[str, ColumnElement[Any]]]
4233 ] = None,
4234 ) -> None:
4235 self._column_flag = _column_flag
4236 self._columns = DedupeColumnCollection()
4238 processed_expressions: Optional[
4239 List[Union[ColumnElement[Any], str]]
4240 ] = _gather_expressions
4242 if processed_expressions is not None:
4244 # this is expected to be an empty list
4245 assert not processed_expressions
4247 self._pending_colargs = []
4248 for (
4249 expr,
4250 _,
4251 _,
4252 add_element,
4253 ) in coercions.expect_col_expression_collection(
4254 roles.DDLConstraintColumnRole, columns
4255 ):
4256 self._pending_colargs.append(add_element)
4257 processed_expressions.append(expr)
4258 else:
4259 self._pending_colargs = [
4260 coercions.expect(roles.DDLConstraintColumnRole, column)
4261 for column in columns
4262 ]
4264 if _autoattach and self._pending_colargs:
4265 self._check_attach()
4267 def _check_attach(self, evt: bool = False) -> None:
4268 col_objs = [c for c in self._pending_colargs if isinstance(c, Column)]
4270 cols_w_table = [c for c in col_objs if isinstance(c.table, Table)]
4272 cols_wo_table = set(col_objs).difference(cols_w_table)
4273 if cols_wo_table:
4274 # feature #3341 - place event listeners for Column objects
4275 # such that when all those cols are attached, we autoattach.
4276 assert not evt, "Should not reach here on event call"
4278 # issue #3411 - don't do the per-column auto-attach if some of the
4279 # columns are specified as strings.
4280 has_string_cols = {
4281 c for c in self._pending_colargs if c is not None
4282 }.difference(col_objs)
4283 if not has_string_cols:
4285 def _col_attached(column: Column[Any], table: Table) -> None:
4286 # this isinstance() corresponds with the
4287 # isinstance() above; only want to count Table-bound
4288 # columns
4289 if isinstance(table, Table):
4290 cols_wo_table.discard(column)
4291 if not cols_wo_table:
4292 self._check_attach(evt=True)
4294 self._cols_wo_table = cols_wo_table
4295 for col in cols_wo_table:
4296 col._on_table_attach(_col_attached)
4297 return
4299 columns = cols_w_table
4301 tables = {c.table for c in columns}
4302 if len(tables) == 1:
4303 self._set_parent_with_dispatch(tables.pop())
4304 elif len(tables) > 1 and not self._allow_multiple_tables:
4305 table = columns[0].table
4306 others = [c for c in columns[1:] if c.table is not table]
4307 if others:
4308 # black could not format this inline
4309 other_str = ", ".join("'%s'" % c for c in others)
4310 raise exc.ArgumentError(
4311 f"Column(s) {other_str} "
4312 f"are not part of table '{table.description}'."
4313 )
4315 @util.ro_memoized_property
4316 def columns(self) -> ReadOnlyColumnCollection[str, Column[Any]]:
4317 return self._columns.as_readonly()
4319 @util.ro_memoized_property
4320 def c(self) -> ReadOnlyColumnCollection[str, Column[Any]]:
4321 return self._columns.as_readonly()
4323 def _col_expressions(
4324 self, parent: Union[Table, Column[Any]]
4325 ) -> List[Optional[Column[Any]]]:
4326 if isinstance(parent, Column):
4327 result: List[Optional[Column[Any]]] = [
4328 c for c in self._pending_colargs if isinstance(c, Column)
4329 ]
4330 assert len(result) == len(self._pending_colargs)
4331 return result
4332 else:
4333 try:
4334 return [
4335 parent.c[col] if isinstance(col, str) else col
4336 for col in self._pending_colargs
4337 ]
4338 except KeyError as ke:
4339 raise exc.ConstraintColumnNotFoundError(
4340 f"Can't create {self.__class__.__name__} "
4341 f"on table '{parent.description}': no column "
4342 f"named '{ke.args[0]}' is present."
4343 ) from ke
4345 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4346 assert isinstance(parent, (Table, Column))
4348 for col in self._col_expressions(parent):
4349 if col is not None:
4350 self._columns.add(col)
4353class ColumnCollectionConstraint(ColumnCollectionMixin, Constraint):
4354 """A constraint that proxies a ColumnCollection."""
4356 def __init__(
4357 self,
4358 *columns: _DDLColumnArgument,
4359 name: _ConstraintNameArgument = None,
4360 deferrable: Optional[bool] = None,
4361 initially: Optional[str] = None,
4362 info: Optional[_InfoType] = None,
4363 _autoattach: bool = True,
4364 _column_flag: bool = False,
4365 _gather_expressions: Optional[List[_DDLColumnArgument]] = None,
4366 **dialect_kw: Any,
4367 ) -> None:
4368 r"""
4369 :param \*columns:
4370 A sequence of column names or Column objects.
4372 :param name:
4373 Optional, the in-database name of this constraint.
4375 :param deferrable:
4376 Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
4377 issuing DDL for this constraint.
4379 :param initially:
4380 Optional string. If set, emit INITIALLY <value> when issuing DDL
4381 for this constraint.
4383 :param \**dialect_kw: other keyword arguments including
4384 dialect-specific arguments are propagated to the :class:`.Constraint`
4385 superclass.
4387 """
4388 Constraint.__init__(
4389 self,
4390 name=name,
4391 deferrable=deferrable,
4392 initially=initially,
4393 info=info,
4394 **dialect_kw,
4395 )
4396 ColumnCollectionMixin.__init__(
4397 self, *columns, _autoattach=_autoattach, _column_flag=_column_flag
4398 )
4400 columns: ReadOnlyColumnCollection[str, Column[Any]]
4401 """A :class:`_expression.ColumnCollection` representing the set of columns
4402 for this constraint.
4404 """
4406 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4407 assert isinstance(parent, (Column, Table))
4408 Constraint._set_parent(self, parent)
4409 ColumnCollectionMixin._set_parent(self, parent)
4411 def __contains__(self, x: Any) -> bool:
4412 return x in self._columns
4414 @util.deprecated(
4415 "1.4",
4416 "The :meth:`_schema.ColumnCollectionConstraint.copy` method "
4417 "is deprecated and will be removed in a future release.",
4418 )
4419 def copy(
4420 self,
4421 *,
4422 target_table: Optional[Table] = None,
4423 **kw: Any,
4424 ) -> ColumnCollectionConstraint:
4425 return self._copy(target_table=target_table, **kw)
4427 def _copy(
4428 self,
4429 *,
4430 target_table: Optional[Table] = None,
4431 **kw: Any,
4432 ) -> ColumnCollectionConstraint:
4433 # ticket #5276
4434 constraint_kwargs = {}
4435 for dialect_name in self.dialect_options:
4436 dialect_options = self.dialect_options[dialect_name]._non_defaults
4437 for (
4438 dialect_option_key,
4439 dialect_option_value,
4440 ) in dialect_options.items():
4441 constraint_kwargs[dialect_name + "_" + dialect_option_key] = (
4442 dialect_option_value
4443 )
4445 assert isinstance(self.parent, Table)
4446 c = self.__class__(
4447 name=self.name,
4448 deferrable=self.deferrable,
4449 initially=self.initially,
4450 *[
4451 _copy_expression(expr, self.parent, target_table)
4452 for expr in self._columns
4453 ],
4454 comment=self.comment,
4455 **constraint_kwargs,
4456 )
4457 return self._schema_item_copy(c)
4459 def contains_column(self, col: Column[Any]) -> bool:
4460 """Return True if this constraint contains the given column.
4462 Note that this object also contains an attribute ``.columns``
4463 which is a :class:`_expression.ColumnCollection` of
4464 :class:`_schema.Column` objects.
4466 """
4468 return self._columns.contains_column(col)
4470 def __iter__(self) -> Iterator[Column[Any]]:
4471 return iter(self._columns)
4473 def __len__(self) -> int:
4474 return len(self._columns)
4477class CheckConstraint(ColumnCollectionConstraint):
4478 """A table- or column-level CHECK constraint.
4480 Can be included in the definition of a Table or Column.
4481 """
4483 _allow_multiple_tables = True
4485 __visit_name__ = "table_or_column_check_constraint"
4487 @_document_text_coercion(
4488 "sqltext",
4489 ":class:`.CheckConstraint`",
4490 ":paramref:`.CheckConstraint.sqltext`",
4491 )
4492 def __init__(
4493 self,
4494 sqltext: _TextCoercedExpressionArgument[Any],
4495 name: _ConstraintNameArgument = None,
4496 deferrable: Optional[bool] = None,
4497 initially: Optional[str] = None,
4498 table: Optional[Table] = None,
4499 info: Optional[_InfoType] = None,
4500 _create_rule: Optional[Any] = None,
4501 _autoattach: bool = True,
4502 _type_bound: bool = False,
4503 **dialect_kw: Any,
4504 ) -> None:
4505 r"""Construct a CHECK constraint.
4507 :param sqltext:
4508 A string containing the constraint definition, which will be used
4509 verbatim, or a SQL expression construct. If given as a string,
4510 the object is converted to a :func:`_expression.text` object.
4511 If the textual
4512 string includes a colon character, escape this using a backslash::
4514 CheckConstraint(r"foo ~ E'a(?\:b|c)d")
4516 :param name:
4517 Optional, the in-database name of the constraint.
4519 :param deferrable:
4520 Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
4521 issuing DDL for this constraint.
4523 :param initially:
4524 Optional string. If set, emit INITIALLY <value> when issuing DDL
4525 for this constraint.
4527 :param info: Optional data dictionary which will be populated into the
4528 :attr:`.SchemaItem.info` attribute of this object.
4530 """
4532 self.sqltext = coercions.expect(roles.DDLExpressionRole, sqltext)
4533 columns: List[Column[Any]] = []
4534 visitors.traverse(self.sqltext, {}, {"column": columns.append})
4536 super().__init__(
4537 name=name,
4538 deferrable=deferrable,
4539 initially=initially,
4540 _create_rule=_create_rule,
4541 info=info,
4542 _type_bound=_type_bound,
4543 _autoattach=_autoattach,
4544 *columns,
4545 **dialect_kw,
4546 )
4547 if table is not None:
4548 self._set_parent_with_dispatch(table)
4550 @property
4551 def is_column_level(self) -> bool:
4552 return not isinstance(self.parent, Table)
4554 @util.deprecated(
4555 "1.4",
4556 "The :meth:`_schema.CheckConstraint.copy` method is deprecated "
4557 "and will be removed in a future release.",
4558 )
4559 def copy(
4560 self, *, target_table: Optional[Table] = None, **kw: Any
4561 ) -> CheckConstraint:
4562 return self._copy(target_table=target_table, **kw)
4564 def _copy(
4565 self, *, target_table: Optional[Table] = None, **kw: Any
4566 ) -> CheckConstraint:
4567 if target_table is not None:
4568 # note that target_table is None for the copy process of
4569 # a column-bound CheckConstraint, so this path is not reached
4570 # in that case.
4571 sqltext = _copy_expression(self.sqltext, self.table, target_table)
4572 else:
4573 sqltext = self.sqltext
4574 c = CheckConstraint(
4575 sqltext,
4576 name=self.name,
4577 initially=self.initially,
4578 deferrable=self.deferrable,
4579 _create_rule=self._create_rule,
4580 table=target_table,
4581 comment=self.comment,
4582 _autoattach=False,
4583 _type_bound=self._type_bound,
4584 )
4585 return self._schema_item_copy(c)
4588class ForeignKeyConstraint(ColumnCollectionConstraint):
4589 """A table-level FOREIGN KEY constraint.
4591 Defines a single column or composite FOREIGN KEY ... REFERENCES
4592 constraint. For a no-frills, single column foreign key, adding a
4593 :class:`_schema.ForeignKey` to the definition of a :class:`_schema.Column`
4594 is a
4595 shorthand equivalent for an unnamed, single column
4596 :class:`_schema.ForeignKeyConstraint`.
4598 Examples of foreign key configuration are in :ref:`metadata_foreignkeys`.
4600 """
4602 __visit_name__ = "foreign_key_constraint"
4604 def __init__(
4605 self,
4606 columns: _typing_Sequence[_DDLColumnArgument],
4607 refcolumns: _typing_Sequence[_DDLColumnArgument],
4608 name: _ConstraintNameArgument = None,
4609 onupdate: Optional[str] = None,
4610 ondelete: Optional[str] = None,
4611 deferrable: Optional[bool] = None,
4612 initially: Optional[str] = None,
4613 use_alter: bool = False,
4614 link_to_name: bool = False,
4615 match: Optional[str] = None,
4616 table: Optional[Table] = None,
4617 info: Optional[_InfoType] = None,
4618 comment: Optional[str] = None,
4619 **dialect_kw: Any,
4620 ) -> None:
4621 r"""Construct a composite-capable FOREIGN KEY.
4623 :param columns: A sequence of local column names. The named columns
4624 must be defined and present in the parent Table. The names should
4625 match the ``key`` given to each column (defaults to the name) unless
4626 ``link_to_name`` is True.
4628 :param refcolumns: A sequence of foreign column names or Column
4629 objects. The columns must all be located within the same Table.
4631 :param name: Optional, the in-database name of the key.
4633 :param onupdate: Optional string. If set, emit ON UPDATE <value> when
4634 issuing DDL for this constraint. Typical values include CASCADE,
4635 DELETE and RESTRICT.
4637 .. seealso::
4639 :ref:`on_update_on_delete`
4641 :param ondelete: Optional string. If set, emit ON DELETE <value> when
4642 issuing DDL for this constraint. Typical values include CASCADE,
4643 SET NULL and RESTRICT. Some dialects may allow for additional
4644 syntaxes.
4646 .. seealso::
4648 :ref:`on_update_on_delete`
4650 :param deferrable: Optional bool. If set, emit DEFERRABLE or NOT
4651 DEFERRABLE when issuing DDL for this constraint.
4653 :param initially: Optional string. If set, emit INITIALLY <value> when
4654 issuing DDL for this constraint.
4656 :param link_to_name: if True, the string name given in ``column`` is
4657 the rendered name of the referenced column, not its locally assigned
4658 ``key``.
4660 :param use_alter: If True, do not emit the DDL for this constraint as
4661 part of the CREATE TABLE definition. Instead, generate it via an
4662 ALTER TABLE statement issued after the full collection of tables
4663 have been created, and drop it via an ALTER TABLE statement before
4664 the full collection of tables are dropped.
4666 The use of :paramref:`_schema.ForeignKeyConstraint.use_alter` is
4667 particularly geared towards the case where two or more tables
4668 are established within a mutually-dependent foreign key constraint
4669 relationship; however, the :meth:`_schema.MetaData.create_all` and
4670 :meth:`_schema.MetaData.drop_all`
4671 methods will perform this resolution
4672 automatically, so the flag is normally not needed.
4674 .. seealso::
4676 :ref:`use_alter`
4678 :param match: Optional string. If set, emit MATCH <value> when issuing
4679 DDL for this constraint. Typical values include SIMPLE, PARTIAL
4680 and FULL.
4682 :param info: Optional data dictionary which will be populated into the
4683 :attr:`.SchemaItem.info` attribute of this object.
4685 :param comment: Optional string that will render an SQL comment on
4686 foreign key constraint creation.
4688 .. versionadded:: 2.0
4690 :param \**dialect_kw: Additional keyword arguments are dialect
4691 specific, and passed in the form ``<dialectname>_<argname>``. See
4692 the documentation regarding an individual dialect at
4693 :ref:`dialect_toplevel` for detail on documented arguments.
4695 """
4697 Constraint.__init__(
4698 self,
4699 name=name,
4700 deferrable=deferrable,
4701 initially=initially,
4702 info=info,
4703 comment=comment,
4704 **dialect_kw,
4705 )
4706 self.onupdate = onupdate
4707 self.ondelete = ondelete
4708 self.link_to_name = link_to_name
4709 self.use_alter = use_alter
4710 self.match = match
4712 if len(set(columns)) != len(refcolumns):
4713 if len(set(columns)) != len(columns):
4714 # e.g. FOREIGN KEY (a, a) REFERENCES r (b, c)
4715 raise exc.ArgumentError(
4716 "ForeignKeyConstraint with duplicate source column "
4717 "references are not supported."
4718 )
4719 else:
4720 # e.g. FOREIGN KEY (a) REFERENCES r (b, c)
4721 # paraphrasing
4722 # https://www.postgresql.org/docs/current/static/ddl-constraints.html
4723 raise exc.ArgumentError(
4724 "ForeignKeyConstraint number "
4725 "of constrained columns must match the number of "
4726 "referenced columns."
4727 )
4729 # standalone ForeignKeyConstraint - create
4730 # associated ForeignKey objects which will be applied to hosted
4731 # Column objects (in col.foreign_keys), either now or when attached
4732 # to the Table for string-specified names
4733 self.elements = [
4734 ForeignKey(
4735 refcol,
4736 _constraint=self,
4737 name=self.name,
4738 onupdate=self.onupdate,
4739 ondelete=self.ondelete,
4740 use_alter=self.use_alter,
4741 link_to_name=self.link_to_name,
4742 match=self.match,
4743 deferrable=self.deferrable,
4744 initially=self.initially,
4745 **self.dialect_kwargs,
4746 )
4747 for refcol in refcolumns
4748 ]
4750 ColumnCollectionMixin.__init__(self, *columns)
4751 if table is not None:
4752 if hasattr(self, "parent"):
4753 assert table is self.parent
4754 self._set_parent_with_dispatch(table)
4756 def _append_element(self, column: Column[Any], fk: ForeignKey) -> None:
4757 self._columns.add(column)
4758 self.elements.append(fk)
4760 columns: ReadOnlyColumnCollection[str, Column[Any]]
4761 """A :class:`_expression.ColumnCollection` representing the set of columns
4762 for this constraint.
4764 """
4766 elements: List[ForeignKey]
4767 """A sequence of :class:`_schema.ForeignKey` objects.
4769 Each :class:`_schema.ForeignKey`
4770 represents a single referring column/referred
4771 column pair.
4773 This collection is intended to be read-only.
4775 """
4777 @property
4778 def _elements(self) -> util.OrderedDict[str, ForeignKey]:
4779 # legacy - provide a dictionary view of (column_key, fk)
4780 return util.OrderedDict(zip(self.column_keys, self.elements))
4782 @property
4783 def _referred_schema(self) -> Optional[str]:
4784 for elem in self.elements:
4785 return elem._referred_schema
4786 else:
4787 return None
4789 @property
4790 def referred_table(self) -> Table:
4791 """The :class:`_schema.Table` object to which this
4792 :class:`_schema.ForeignKeyConstraint` references.
4794 This is a dynamically calculated attribute which may not be available
4795 if the constraint and/or parent table is not yet associated with
4796 a metadata collection that contains the referred table.
4798 """
4799 return self.elements[0].column.table
4801 def _validate_dest_table(self, table: Table) -> None:
4802 table_keys = {elem._table_key() for elem in self.elements}
4803 if None not in table_keys and len(table_keys) > 1:
4804 elem0, elem1 = sorted(table_keys)[0:2]
4805 raise exc.ArgumentError(
4806 f"ForeignKeyConstraint on "
4807 f"{table.fullname}({self._col_description}) refers to "
4808 f"multiple remote tables: {elem0} and {elem1}"
4809 )
4811 @property
4812 def column_keys(self) -> _typing_Sequence[str]:
4813 """Return a list of string keys representing the local
4814 columns in this :class:`_schema.ForeignKeyConstraint`.
4816 This list is either the original string arguments sent
4817 to the constructor of the :class:`_schema.ForeignKeyConstraint`,
4818 or if the constraint has been initialized with :class:`_schema.Column`
4819 objects, is the string ``.key`` of each element.
4821 """
4822 if hasattr(self, "parent"):
4823 return self._columns.keys()
4824 else:
4825 return [
4826 col.key if isinstance(col, ColumnElement) else str(col)
4827 for col in self._pending_colargs
4828 ]
4830 @property
4831 def _col_description(self) -> str:
4832 return ", ".join(self.column_keys)
4834 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4835 table = parent
4836 assert isinstance(table, Table)
4837 Constraint._set_parent(self, table)
4839 ColumnCollectionConstraint._set_parent(self, table)
4841 for col, fk in zip(self._columns, self.elements):
4842 if not hasattr(fk, "parent") or fk.parent is not col:
4843 fk._set_parent_with_dispatch(col)
4845 self._validate_dest_table(table)
4847 @util.deprecated(
4848 "1.4",
4849 "The :meth:`_schema.ForeignKeyConstraint.copy` method is deprecated "
4850 "and will be removed in a future release.",
4851 )
4852 def copy(
4853 self,
4854 *,
4855 schema: Optional[str] = None,
4856 target_table: Optional[Table] = None,
4857 **kw: Any,
4858 ) -> ForeignKeyConstraint:
4859 return self._copy(schema=schema, target_table=target_table, **kw)
4861 def _copy(
4862 self,
4863 *,
4864 schema: Optional[str] = None,
4865 target_table: Optional[Table] = None,
4866 **kw: Any,
4867 ) -> ForeignKeyConstraint:
4868 fkc = ForeignKeyConstraint(
4869 [x.parent.key for x in self.elements],
4870 [
4871 x._get_colspec(
4872 schema=schema,
4873 table_name=(
4874 target_table.name
4875 if target_table is not None
4876 and x._table_key() == x.parent.table.key
4877 else None
4878 ),
4879 _is_copy=True,
4880 )
4881 for x in self.elements
4882 ],
4883 name=self.name,
4884 onupdate=self.onupdate,
4885 ondelete=self.ondelete,
4886 use_alter=self.use_alter,
4887 deferrable=self.deferrable,
4888 initially=self.initially,
4889 link_to_name=self.link_to_name,
4890 match=self.match,
4891 comment=self.comment,
4892 )
4893 for self_fk, other_fk in zip(self.elements, fkc.elements):
4894 self_fk._schema_item_copy(other_fk)
4895 return self._schema_item_copy(fkc)
4898class PrimaryKeyConstraint(ColumnCollectionConstraint):
4899 """A table-level PRIMARY KEY constraint.
4901 The :class:`.PrimaryKeyConstraint` object is present automatically
4902 on any :class:`_schema.Table` object; it is assigned a set of
4903 :class:`_schema.Column` objects corresponding to those marked with
4904 the :paramref:`_schema.Column.primary_key` flag::
4906 >>> my_table = Table(
4907 ... "mytable",
4908 ... metadata,
4909 ... Column("id", Integer, primary_key=True),
4910 ... Column("version_id", Integer, primary_key=True),
4911 ... Column("data", String(50)),
4912 ... )
4913 >>> my_table.primary_key
4914 PrimaryKeyConstraint(
4915 Column('id', Integer(), table=<mytable>,
4916 primary_key=True, nullable=False),
4917 Column('version_id', Integer(), table=<mytable>,
4918 primary_key=True, nullable=False)
4919 )
4921 The primary key of a :class:`_schema.Table` can also be specified by using
4922 a :class:`.PrimaryKeyConstraint` object explicitly; in this mode of usage,
4923 the "name" of the constraint can also be specified, as well as other
4924 options which may be recognized by dialects::
4926 my_table = Table(
4927 "mytable",
4928 metadata,
4929 Column("id", Integer),
4930 Column("version_id", Integer),
4931 Column("data", String(50)),
4932 PrimaryKeyConstraint("id", "version_id", name="mytable_pk"),
4933 )
4935 The two styles of column-specification should generally not be mixed.
4936 An warning is emitted if the columns present in the
4937 :class:`.PrimaryKeyConstraint`
4938 don't match the columns that were marked as ``primary_key=True``, if both
4939 are present; in this case, the columns are taken strictly from the
4940 :class:`.PrimaryKeyConstraint` declaration, and those columns otherwise
4941 marked as ``primary_key=True`` are ignored. This behavior is intended to
4942 be backwards compatible with previous behavior.
4944 For the use case where specific options are to be specified on the
4945 :class:`.PrimaryKeyConstraint`, but the usual style of using
4946 ``primary_key=True`` flags is still desirable, an empty
4947 :class:`.PrimaryKeyConstraint` may be specified, which will take on the
4948 primary key column collection from the :class:`_schema.Table` based on the
4949 flags::
4951 my_table = Table(
4952 "mytable",
4953 metadata,
4954 Column("id", Integer, primary_key=True),
4955 Column("version_id", Integer, primary_key=True),
4956 Column("data", String(50)),
4957 PrimaryKeyConstraint(name="mytable_pk", mssql_clustered=True),
4958 )
4960 """
4962 __visit_name__ = "primary_key_constraint"
4964 def __init__(
4965 self,
4966 *columns: _DDLColumnArgument,
4967 name: Optional[str] = None,
4968 deferrable: Optional[bool] = None,
4969 initially: Optional[str] = None,
4970 info: Optional[_InfoType] = None,
4971 _implicit_generated: bool = False,
4972 **dialect_kw: Any,
4973 ) -> None:
4974 self._implicit_generated = _implicit_generated
4975 super().__init__(
4976 *columns,
4977 name=name,
4978 deferrable=deferrable,
4979 initially=initially,
4980 info=info,
4981 **dialect_kw,
4982 )
4984 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4985 table = parent
4986 assert isinstance(table, Table)
4987 super()._set_parent(table)
4989 if table.primary_key is not self:
4990 table.constraints.discard(table.primary_key)
4991 table.primary_key = self # type: ignore
4992 table.constraints.add(self)
4994 table_pks = [c for c in table.c if c.primary_key]
4995 if (
4996 self._columns
4997 and table_pks
4998 and set(table_pks) != set(self._columns)
4999 ):
5000 # black could not format these inline
5001 table_pk_str = ", ".join("'%s'" % c.name for c in table_pks)
5002 col_str = ", ".join("'%s'" % c.name for c in self._columns)
5004 util.warn(
5005 f"Table '{table.name}' specifies columns "
5006 f"{table_pk_str} as "
5007 f"primary_key=True, "
5008 f"not matching locally specified columns {col_str}; "
5009 f"setting the "
5010 f"current primary key columns to "
5011 f"{col_str}. "
5012 f"This warning "
5013 f"may become an exception in a future release"
5014 )
5015 table_pks[:] = []
5017 for c in self._columns:
5018 c.primary_key = True
5019 if c._user_defined_nullable is NULL_UNSPECIFIED:
5020 c.nullable = False
5021 if table_pks:
5022 self._columns.extend(table_pks)
5024 def _reload(self, columns: Iterable[Column[Any]]) -> None:
5025 """repopulate this :class:`.PrimaryKeyConstraint` given
5026 a set of columns.
5028 Existing columns in the table that are marked as primary_key=True
5029 are maintained.
5031 Also fires a new event.
5033 This is basically like putting a whole new
5034 :class:`.PrimaryKeyConstraint` object on the parent
5035 :class:`_schema.Table` object without actually replacing the object.
5037 The ordering of the given list of columns is also maintained; these
5038 columns will be appended to the list of columns after any which
5039 are already present.
5041 """
5042 # set the primary key flag on new columns.
5043 # note any existing PK cols on the table also have their
5044 # flag still set.
5045 for col in columns:
5046 col.primary_key = True
5048 self._columns.extend(columns)
5050 PrimaryKeyConstraint._autoincrement_column._reset(self) # type: ignore
5051 self._set_parent_with_dispatch(self.table)
5053 def _replace(self, col: Column[Any]) -> None:
5054 PrimaryKeyConstraint._autoincrement_column._reset(self) # type: ignore
5055 self._columns.replace(col)
5057 self.dispatch._sa_event_column_added_to_pk_constraint(self, col)
5059 @property
5060 def columns_autoinc_first(self) -> List[Column[Any]]:
5061 autoinc = self._autoincrement_column
5063 if autoinc is not None:
5064 return [autoinc] + [c for c in self._columns if c is not autoinc]
5065 else:
5066 return list(self._columns)
5068 @util.ro_memoized_property
5069 def _autoincrement_column(self) -> Optional[Column[int]]:
5070 def _validate_autoinc(col: Column[Any], autoinc_true: bool) -> bool:
5071 if col.type._type_affinity is None or not issubclass(
5072 col.type._type_affinity,
5073 (
5074 type_api.INTEGERTYPE._type_affinity,
5075 type_api.NUMERICTYPE._type_affinity,
5076 ),
5077 ):
5078 if autoinc_true:
5079 raise exc.ArgumentError(
5080 f"Column type {col.type} on column '{col}' is not "
5081 f"compatible with autoincrement=True"
5082 )
5083 else:
5084 return False
5085 elif (
5086 not isinstance(col.default, (type(None), Sequence))
5087 and not autoinc_true
5088 ):
5089 return False
5090 elif (
5091 col.server_default is not None
5092 and not isinstance(col.server_default, Identity)
5093 and not autoinc_true
5094 ):
5095 return False
5096 elif col.foreign_keys and col.autoincrement not in (
5097 True,
5098 "ignore_fk",
5099 ):
5100 return False
5101 return True
5103 if len(self._columns) == 1:
5104 col = list(self._columns)[0]
5106 if col.autoincrement is True:
5107 _validate_autoinc(col, True)
5108 return col
5109 elif col.autoincrement in (
5110 "auto",
5111 "ignore_fk",
5112 ) and _validate_autoinc(col, False):
5113 return col
5114 else:
5115 return None
5117 else:
5118 autoinc = None
5119 for col in self._columns:
5120 if col.autoincrement is True:
5121 _validate_autoinc(col, True)
5122 if autoinc is not None:
5123 raise exc.ArgumentError(
5124 f"Only one Column may be marked "
5125 f"autoincrement=True, found both "
5126 f"{col.name} and {autoinc.name}."
5127 )
5128 else:
5129 autoinc = col
5131 return autoinc
5134class UniqueConstraint(ColumnCollectionConstraint):
5135 """A table-level UNIQUE constraint.
5137 Defines a single column or composite UNIQUE constraint. For a no-frills,
5138 single column constraint, adding ``unique=True`` to the ``Column``
5139 definition is a shorthand equivalent for an unnamed, single column
5140 UniqueConstraint.
5141 """
5143 __visit_name__ = "unique_constraint"
5146class Index(
5147 DialectKWArgs, ColumnCollectionMixin, HasConditionalDDL, SchemaItem
5148):
5149 """A table-level INDEX.
5151 Defines a composite (one or more column) INDEX.
5153 E.g.::
5155 sometable = Table(
5156 "sometable",
5157 metadata,
5158 Column("name", String(50)),
5159 Column("address", String(100)),
5160 )
5162 Index("some_index", sometable.c.name)
5164 For a no-frills, single column index, adding
5165 :class:`_schema.Column` also supports ``index=True``::
5167 sometable = Table(
5168 "sometable", metadata, Column("name", String(50), index=True)
5169 )
5171 For a composite index, multiple columns can be specified::
5173 Index("some_index", sometable.c.name, sometable.c.address)
5175 Functional indexes are supported as well, typically by using the
5176 :data:`.func` construct in conjunction with table-bound
5177 :class:`_schema.Column` objects::
5179 Index("some_index", func.lower(sometable.c.name))
5181 An :class:`.Index` can also be manually associated with a
5182 :class:`_schema.Table`,
5183 either through inline declaration or using
5184 :meth:`_schema.Table.append_constraint`. When this approach is used,
5185 the names
5186 of the indexed columns can be specified as strings::
5188 Table(
5189 "sometable",
5190 metadata,
5191 Column("name", String(50)),
5192 Column("address", String(100)),
5193 Index("some_index", "name", "address"),
5194 )
5196 To support functional or expression-based indexes in this form, the
5197 :func:`_expression.text` construct may be used::
5199 from sqlalchemy import text
5201 Table(
5202 "sometable",
5203 metadata,
5204 Column("name", String(50)),
5205 Column("address", String(100)),
5206 Index("some_index", text("lower(name)")),
5207 )
5209 .. seealso::
5211 :ref:`schema_indexes` - General information on :class:`.Index`.
5213 :ref:`postgresql_indexes` - PostgreSQL-specific options available for
5214 the :class:`.Index` construct.
5216 :ref:`mysql_indexes` - MySQL-specific options available for the
5217 :class:`.Index` construct.
5219 :ref:`mssql_indexes` - MSSQL-specific options available for the
5220 :class:`.Index` construct.
5222 """
5224 __visit_name__ = "index"
5226 table: Optional[Table]
5227 expressions: _typing_Sequence[Union[str, ColumnElement[Any]]]
5228 _table_bound_expressions: _typing_Sequence[ColumnElement[Any]]
5230 def __init__(
5231 self,
5232 name: Optional[str],
5233 *expressions: _DDLColumnArgument,
5234 unique: bool = False,
5235 quote: Optional[bool] = None,
5236 info: Optional[_InfoType] = None,
5237 _table: Optional[Table] = None,
5238 _column_flag: bool = False,
5239 **dialect_kw: Any,
5240 ) -> None:
5241 r"""Construct an index object.
5243 :param name:
5244 The name of the index
5246 :param \*expressions:
5247 Column expressions to include in the index. The expressions
5248 are normally instances of :class:`_schema.Column`, but may also
5249 be arbitrary SQL expressions which ultimately refer to a
5250 :class:`_schema.Column`.
5252 :param unique=False:
5253 Keyword only argument; if True, create a unique index.
5255 :param quote=None:
5256 Keyword only argument; whether to apply quoting to the name of
5257 the index. Works in the same manner as that of
5258 :paramref:`_schema.Column.quote`.
5260 :param info=None: Optional data dictionary which will be populated
5261 into the :attr:`.SchemaItem.info` attribute of this object.
5263 :param \**dialect_kw: Additional keyword arguments not mentioned above
5264 are dialect specific, and passed in the form
5265 ``<dialectname>_<argname>``. See the documentation regarding an
5266 individual dialect at :ref:`dialect_toplevel` for detail on
5267 documented arguments.
5269 """
5270 self.table = table = None
5272 self.name = quoted_name.construct(name, quote)
5273 self.unique = unique
5274 if info is not None:
5275 self.info = info
5277 # TODO: consider "table" argument being public, but for
5278 # the purpose of the fix here, it starts as private.
5279 if _table is not None:
5280 table = _table
5282 self._validate_dialect_kwargs(dialect_kw)
5284 self.expressions = []
5285 # will call _set_parent() if table-bound column
5286 # objects are present
5287 ColumnCollectionMixin.__init__(
5288 self,
5289 *expressions,
5290 _column_flag=_column_flag,
5291 _gather_expressions=self.expressions,
5292 )
5293 if table is not None:
5294 self._set_parent(table)
5296 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
5297 table = parent
5298 assert isinstance(table, Table)
5299 ColumnCollectionMixin._set_parent(self, table)
5301 if self.table is not None and table is not self.table:
5302 raise exc.ArgumentError(
5303 f"Index '{self.name}' is against table "
5304 f"'{self.table.description}', and "
5305 f"cannot be associated with table '{table.description}'."
5306 )
5307 self.table = table
5308 table.indexes.add(self)
5310 expressions = self.expressions
5311 col_expressions = self._col_expressions(table)
5312 assert len(expressions) == len(col_expressions)
5314 exprs = []
5315 for expr, colexpr in zip(expressions, col_expressions):
5316 if isinstance(expr, ClauseElement):
5317 exprs.append(expr)
5318 elif colexpr is not None:
5319 exprs.append(colexpr)
5320 else:
5321 assert False
5322 self.expressions = self._table_bound_expressions = exprs
5324 def create(self, bind: _CreateDropBind, checkfirst: bool = False) -> None:
5325 """Issue a ``CREATE`` statement for this
5326 :class:`.Index`, using the given
5327 :class:`.Connection` or :class:`.Engine`` for connectivity.
5329 .. seealso::
5331 :meth:`_schema.MetaData.create_all`.
5333 """
5334 bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
5336 def drop(self, bind: _CreateDropBind, checkfirst: bool = False) -> None:
5337 """Issue a ``DROP`` statement for this
5338 :class:`.Index`, using the given
5339 :class:`.Connection` or :class:`.Engine` for connectivity.
5341 .. seealso::
5343 :meth:`_schema.MetaData.drop_all`.
5345 """
5346 bind._run_ddl_visitor(ddl.SchemaDropper, self, checkfirst=checkfirst)
5348 def __repr__(self) -> str:
5349 exprs: _typing_Sequence[Any] # noqa: F842
5351 return "Index(%s)" % (
5352 ", ".join(
5353 [repr(self.name)]
5354 + [repr(e) for e in self.expressions]
5355 + (self.unique and ["unique=True"] or [])
5356 )
5357 )
5360_NamingSchemaCallable = Callable[[Constraint, Table], str]
5361_NamingSchemaDirective = Union[str, _NamingSchemaCallable]
5364class _NamingSchemaTD(TypedDict, total=False):
5365 fk: _NamingSchemaDirective
5366 pk: _NamingSchemaDirective
5367 ix: _NamingSchemaDirective
5368 ck: _NamingSchemaDirective
5369 uq: _NamingSchemaDirective
5372_NamingSchemaParameter = Union[
5373 # it seems like the TypedDict here is useful for pylance typeahead,
5374 # and not much else
5375 _NamingSchemaTD,
5376 # there is no form that allows Union[Type[Any], str] to work in all
5377 # cases, including breaking out Mapping[] entries for each combination
5378 # even, therefore keys must be `Any` (see #10264)
5379 Mapping[Any, _NamingSchemaDirective],
5380]
5383DEFAULT_NAMING_CONVENTION: _NamingSchemaParameter = util.immutabledict(
5384 {"ix": "ix_%(column_0_label)s"}
5385)
5388class MetaData(HasSchemaAttr):
5389 """A collection of :class:`_schema.Table`
5390 objects and their associated schema
5391 constructs.
5393 Holds a collection of :class:`_schema.Table` objects as well as
5394 an optional binding to an :class:`_engine.Engine` or
5395 :class:`_engine.Connection`. If bound, the :class:`_schema.Table` objects
5396 in the collection and their columns may participate in implicit SQL
5397 execution.
5399 The :class:`_schema.Table` objects themselves are stored in the
5400 :attr:`_schema.MetaData.tables` dictionary.
5402 :class:`_schema.MetaData` is a thread-safe object for read operations.
5403 Construction of new tables within a single :class:`_schema.MetaData`
5404 object,
5405 either explicitly or via reflection, may not be completely thread-safe.
5407 .. seealso::
5409 :ref:`metadata_describing` - Introduction to database metadata
5411 """
5413 __visit_name__ = "metadata"
5415 def __init__(
5416 self,
5417 schema: Optional[str] = None,
5418 quote_schema: Optional[bool] = None,
5419 naming_convention: Optional[_NamingSchemaParameter] = None,
5420 info: Optional[_InfoType] = None,
5421 ) -> None:
5422 """Create a new MetaData object.
5424 :param schema:
5425 The default schema to use for the :class:`_schema.Table`,
5426 :class:`.Sequence`, and potentially other objects associated with
5427 this :class:`_schema.MetaData`. Defaults to ``None``.
5429 .. seealso::
5431 :ref:`schema_metadata_schema_name` - details on how the
5432 :paramref:`_schema.MetaData.schema` parameter is used.
5434 :paramref:`_schema.Table.schema`
5436 :paramref:`.Sequence.schema`
5438 :param quote_schema:
5439 Sets the ``quote_schema`` flag for those :class:`_schema.Table`,
5440 :class:`.Sequence`, and other objects which make usage of the
5441 local ``schema`` name.
5443 :param info: Optional data dictionary which will be populated into the
5444 :attr:`.SchemaItem.info` attribute of this object.
5446 :param naming_convention: a dictionary referring to values which
5447 will establish default naming conventions for :class:`.Constraint`
5448 and :class:`.Index` objects, for those objects which are not given
5449 a name explicitly.
5451 The keys of this dictionary may be:
5453 * a constraint or Index class, e.g. the :class:`.UniqueConstraint`,
5454 :class:`_schema.ForeignKeyConstraint` class, the :class:`.Index`
5455 class
5457 * a string mnemonic for one of the known constraint classes;
5458 ``"fk"``, ``"pk"``, ``"ix"``, ``"ck"``, ``"uq"`` for foreign key,
5459 primary key, index, check, and unique constraint, respectively.
5461 * the string name of a user-defined "token" that can be used
5462 to define new naming tokens.
5464 The values associated with each "constraint class" or "constraint
5465 mnemonic" key are string naming templates, such as
5466 ``"uq_%(table_name)s_%(column_0_name)s"``,
5467 which describe how the name should be composed. The values
5468 associated with user-defined "token" keys should be callables of the
5469 form ``fn(constraint, table)``, which accepts the constraint/index
5470 object and :class:`_schema.Table` as arguments, returning a string
5471 result.
5473 The built-in names are as follows, some of which may only be
5474 available for certain types of constraint:
5476 * ``%(table_name)s`` - the name of the :class:`_schema.Table`
5477 object
5478 associated with the constraint.
5480 * ``%(referred_table_name)s`` - the name of the
5481 :class:`_schema.Table`
5482 object associated with the referencing target of a
5483 :class:`_schema.ForeignKeyConstraint`.
5485 * ``%(column_0_name)s`` - the name of the :class:`_schema.Column`
5486 at
5487 index position "0" within the constraint.
5489 * ``%(column_0N_name)s`` - the name of all :class:`_schema.Column`
5490 objects in order within the constraint, joined without a
5491 separator.
5493 * ``%(column_0_N_name)s`` - the name of all
5494 :class:`_schema.Column`
5495 objects in order within the constraint, joined with an
5496 underscore as a separator.
5498 * ``%(column_0_label)s``, ``%(column_0N_label)s``,
5499 ``%(column_0_N_label)s`` - the label of either the zeroth
5500 :class:`_schema.Column` or all :class:`.Columns`, separated with
5501 or without an underscore
5503 * ``%(column_0_key)s``, ``%(column_0N_key)s``,
5504 ``%(column_0_N_key)s`` - the key of either the zeroth
5505 :class:`_schema.Column` or all :class:`.Columns`, separated with
5506 or without an underscore
5508 * ``%(referred_column_0_name)s``, ``%(referred_column_0N_name)s``
5509 ``%(referred_column_0_N_name)s``, ``%(referred_column_0_key)s``,
5510 ``%(referred_column_0N_key)s``, ... column tokens which
5511 render the names/keys/labels of columns that are referenced
5512 by a :class:`_schema.ForeignKeyConstraint`.
5514 * ``%(constraint_name)s`` - a special key that refers to the
5515 existing name given to the constraint. When this key is
5516 present, the :class:`.Constraint` object's existing name will be
5517 replaced with one that is composed from template string that
5518 uses this token. When this token is present, it is required that
5519 the :class:`.Constraint` is given an explicit name ahead of time.
5521 * user-defined: any additional token may be implemented by passing
5522 it along with a ``fn(constraint, table)`` callable to the
5523 naming_convention dictionary.
5525 .. versionadded:: 1.3.0 - added new ``%(column_0N_name)s``,
5526 ``%(column_0_N_name)s``, and related tokens that produce
5527 concatenations of names, keys, or labels for all columns referred
5528 to by a given constraint.
5530 .. seealso::
5532 :ref:`constraint_naming_conventions` - for detailed usage
5533 examples.
5535 """
5536 if schema is not None and not isinstance(schema, str):
5537 raise exc.ArgumentError(
5538 "expected schema argument to be a string, "
5539 f"got {type(schema)}."
5540 )
5541 self.tables = util.FacadeDict()
5542 self.schema = quoted_name.construct(schema, quote_schema)
5543 self.naming_convention = (
5544 naming_convention
5545 if naming_convention
5546 else DEFAULT_NAMING_CONVENTION
5547 )
5548 if info:
5549 self.info = info
5550 self._schemas: Set[str] = set()
5551 self._sequences: Dict[str, Sequence] = {}
5552 self._fk_memos: Dict[Tuple[str, Optional[str]], List[ForeignKey]] = (
5553 collections.defaultdict(list)
5554 )
5556 tables: util.FacadeDict[str, Table]
5557 """A dictionary of :class:`_schema.Table`
5558 objects keyed to their name or "table key".
5560 The exact key is that determined by the :attr:`_schema.Table.key`
5561 attribute;
5562 for a table with no :attr:`_schema.Table.schema` attribute,
5563 this is the same
5564 as :attr:`_schema.Table.name`. For a table with a schema,
5565 it is typically of the
5566 form ``schemaname.tablename``.
5568 .. seealso::
5570 :attr:`_schema.MetaData.sorted_tables`
5572 """
5574 def __repr__(self) -> str:
5575 return "MetaData()"
5577 def __contains__(self, table_or_key: Union[str, Table]) -> bool:
5578 if not isinstance(table_or_key, str):
5579 table_or_key = table_or_key.key
5580 return table_or_key in self.tables
5582 def _add_table(
5583 self, name: str, schema: Optional[str], table: Table
5584 ) -> None:
5585 key = _get_table_key(name, schema)
5586 self.tables._insert_item(key, table)
5587 if schema:
5588 self._schemas.add(schema)
5590 def _remove_table(self, name: str, schema: Optional[str]) -> None:
5591 key = _get_table_key(name, schema)
5592 removed = dict.pop(self.tables, key, None)
5593 if removed is not None:
5594 for fk in removed.foreign_keys:
5595 fk._remove_from_metadata(self)
5596 if self._schemas:
5597 self._schemas = {
5598 t.schema for t in self.tables.values() if t.schema is not None
5599 }
5601 def __getstate__(self) -> Dict[str, Any]:
5602 return {
5603 "tables": self.tables,
5604 "schema": self.schema,
5605 "schemas": self._schemas,
5606 "sequences": self._sequences,
5607 "fk_memos": self._fk_memos,
5608 "naming_convention": self.naming_convention,
5609 }
5611 def __setstate__(self, state: Dict[str, Any]) -> None:
5612 self.tables = state["tables"]
5613 self.schema = state["schema"]
5614 self.naming_convention = state["naming_convention"]
5615 self._sequences = state["sequences"]
5616 self._schemas = state["schemas"]
5617 self._fk_memos = state["fk_memos"]
5619 def clear(self) -> None:
5620 """Clear all Table objects from this MetaData."""
5622 dict.clear(self.tables) # type: ignore
5623 self._schemas.clear()
5624 self._fk_memos.clear()
5626 def remove(self, table: Table) -> None:
5627 """Remove the given Table object from this MetaData."""
5629 self._remove_table(table.name, table.schema)
5631 @property
5632 def sorted_tables(self) -> List[Table]:
5633 """Returns a list of :class:`_schema.Table` objects sorted in order of
5634 foreign key dependency.
5636 The sorting will place :class:`_schema.Table`
5637 objects that have dependencies
5638 first, before the dependencies themselves, representing the
5639 order in which they can be created. To get the order in which
5640 the tables would be dropped, use the ``reversed()`` Python built-in.
5642 .. warning::
5644 The :attr:`.MetaData.sorted_tables` attribute cannot by itself
5645 accommodate automatic resolution of dependency cycles between
5646 tables, which are usually caused by mutually dependent foreign key
5647 constraints. When these cycles are detected, the foreign keys
5648 of these tables are omitted from consideration in the sort.
5649 A warning is emitted when this condition occurs, which will be an
5650 exception raise in a future release. Tables which are not part
5651 of the cycle will still be returned in dependency order.
5653 To resolve these cycles, the
5654 :paramref:`_schema.ForeignKeyConstraint.use_alter` parameter may be
5655 applied to those constraints which create a cycle. Alternatively,
5656 the :func:`_schema.sort_tables_and_constraints` function will
5657 automatically return foreign key constraints in a separate
5658 collection when cycles are detected so that they may be applied
5659 to a schema separately.
5661 .. versionchanged:: 1.3.17 - a warning is emitted when
5662 :attr:`.MetaData.sorted_tables` cannot perform a proper sort
5663 due to cyclical dependencies. This will be an exception in a
5664 future release. Additionally, the sort will continue to return
5665 other tables not involved in the cycle in dependency order which
5666 was not the case previously.
5668 .. seealso::
5670 :func:`_schema.sort_tables`
5672 :func:`_schema.sort_tables_and_constraints`
5674 :attr:`_schema.MetaData.tables`
5676 :meth:`_reflection.Inspector.get_table_names`
5678 :meth:`_reflection.Inspector.get_sorted_table_and_fkc_names`
5681 """
5682 return ddl.sort_tables(
5683 sorted(self.tables.values(), key=lambda t: t.key) # type: ignore
5684 )
5686 # overload needed to work around mypy this mypy
5687 # https://github.com/python/mypy/issues/17093
5688 @overload
5689 def reflect(
5690 self,
5691 bind: Engine,
5692 schema: Optional[str] = ...,
5693 views: bool = ...,
5694 only: Union[
5695 _typing_Sequence[str], Callable[[str, MetaData], bool], None
5696 ] = ...,
5697 extend_existing: bool = ...,
5698 autoload_replace: bool = ...,
5699 resolve_fks: bool = ...,
5700 **dialect_kwargs: Any,
5701 ) -> None: ...
5703 @overload
5704 def reflect(
5705 self,
5706 bind: Connection,
5707 schema: Optional[str] = ...,
5708 views: bool = ...,
5709 only: Union[
5710 _typing_Sequence[str], Callable[[str, MetaData], bool], None
5711 ] = ...,
5712 extend_existing: bool = ...,
5713 autoload_replace: bool = ...,
5714 resolve_fks: bool = ...,
5715 **dialect_kwargs: Any,
5716 ) -> None: ...
5718 @util.preload_module("sqlalchemy.engine.reflection")
5719 def reflect(
5720 self,
5721 bind: Union[Engine, Connection],
5722 schema: Optional[str] = None,
5723 views: bool = False,
5724 only: Union[
5725 _typing_Sequence[str], Callable[[str, MetaData], bool], None
5726 ] = None,
5727 extend_existing: bool = False,
5728 autoload_replace: bool = True,
5729 resolve_fks: bool = True,
5730 **dialect_kwargs: Any,
5731 ) -> None:
5732 r"""Load all available table definitions from the database.
5734 Automatically creates ``Table`` entries in this ``MetaData`` for any
5735 table available in the database but not yet present in the
5736 ``MetaData``. May be called multiple times to pick up tables recently
5737 added to the database, however no special action is taken if a table
5738 in this ``MetaData`` no longer exists in the database.
5740 :param bind:
5741 A :class:`.Connection` or :class:`.Engine` used to access the
5742 database.
5744 :param schema:
5745 Optional, query and reflect tables from an alternate schema.
5746 If None, the schema associated with this :class:`_schema.MetaData`
5747 is used, if any.
5749 :param views:
5750 If True, also reflect views (materialized and plain).
5752 :param only:
5753 Optional. Load only a sub-set of available named tables. May be
5754 specified as a sequence of names or a callable.
5756 If a sequence of names is provided, only those tables will be
5757 reflected. An error is raised if a table is requested but not
5758 available. Named tables already present in this ``MetaData`` are
5759 ignored.
5761 If a callable is provided, it will be used as a boolean predicate to
5762 filter the list of potential table names. The callable is called
5763 with a table name and this ``MetaData`` instance as positional
5764 arguments and should return a true value for any table to reflect.
5766 :param extend_existing: Passed along to each :class:`_schema.Table` as
5767 :paramref:`_schema.Table.extend_existing`.
5769 :param autoload_replace: Passed along to each :class:`_schema.Table`
5770 as
5771 :paramref:`_schema.Table.autoload_replace`.
5773 :param resolve_fks: if True, reflect :class:`_schema.Table`
5774 objects linked
5775 to :class:`_schema.ForeignKey` objects located in each
5776 :class:`_schema.Table`.
5777 For :meth:`_schema.MetaData.reflect`,
5778 this has the effect of reflecting
5779 related tables that might otherwise not be in the list of tables
5780 being reflected, for example if the referenced table is in a
5781 different schema or is omitted via the
5782 :paramref:`.MetaData.reflect.only` parameter. When False,
5783 :class:`_schema.ForeignKey` objects are not followed to the
5784 :class:`_schema.Table`
5785 in which they link, however if the related table is also part of the
5786 list of tables that would be reflected in any case, the
5787 :class:`_schema.ForeignKey` object will still resolve to its related
5788 :class:`_schema.Table` after the :meth:`_schema.MetaData.reflect`
5789 operation is
5790 complete. Defaults to True.
5792 .. versionadded:: 1.3.0
5794 .. seealso::
5796 :paramref:`_schema.Table.resolve_fks`
5798 :param \**dialect_kwargs: Additional keyword arguments not mentioned
5799 above are dialect specific, and passed in the form
5800 ``<dialectname>_<argname>``. See the documentation regarding an
5801 individual dialect at :ref:`dialect_toplevel` for detail on
5802 documented arguments.
5804 .. seealso::
5806 :ref:`metadata_reflection_toplevel`
5808 :meth:`_events.DDLEvents.column_reflect` - Event used to customize
5809 the reflected columns. Usually used to generalize the types using
5810 :meth:`_types.TypeEngine.as_generic`
5812 :ref:`metadata_reflection_dbagnostic_types` - describes how to
5813 reflect tables using general types.
5815 """
5817 with inspection.inspect(bind)._inspection_context() as insp:
5818 reflect_opts: Any = {
5819 "autoload_with": insp,
5820 "extend_existing": extend_existing,
5821 "autoload_replace": autoload_replace,
5822 "resolve_fks": resolve_fks,
5823 "_extend_on": set(),
5824 }
5826 reflect_opts.update(dialect_kwargs)
5828 if schema is None:
5829 schema = self.schema
5831 if schema is not None:
5832 reflect_opts["schema"] = schema
5834 kind = util.preloaded.engine_reflection.ObjectKind.TABLE
5835 available: util.OrderedSet[str] = util.OrderedSet(
5836 insp.get_table_names(schema)
5837 )
5838 if views:
5839 kind = util.preloaded.engine_reflection.ObjectKind.ANY
5840 available.update(insp.get_view_names(schema))
5841 try:
5842 available.update(insp.get_materialized_view_names(schema))
5843 except NotImplementedError:
5844 pass
5846 if schema is not None:
5847 available_w_schema: util.OrderedSet[str] = util.OrderedSet(
5848 [f"{schema}.{name}" for name in available]
5849 )
5850 else:
5851 available_w_schema = available
5853 current = set(self.tables)
5855 if only is None:
5856 load = [
5857 name
5858 for name, schname in zip(available, available_w_schema)
5859 if extend_existing or schname not in current
5860 ]
5861 elif callable(only):
5862 load = [
5863 name
5864 for name, schname in zip(available, available_w_schema)
5865 if (extend_existing or schname not in current)
5866 and only(name, self)
5867 ]
5868 else:
5869 missing = [name for name in only if name not in available]
5870 if missing:
5871 s = schema and (" schema '%s'" % schema) or ""
5872 missing_str = ", ".join(missing)
5873 raise exc.InvalidRequestError(
5874 f"Could not reflect: requested table(s) not available "
5875 f"in {bind.engine!r}{s}: ({missing_str})"
5876 )
5877 load = [
5878 name
5879 for name in only
5880 if extend_existing or name not in current
5881 ]
5882 # pass the available tables so the inspector can
5883 # choose to ignore the filter_names
5884 _reflect_info = insp._get_reflection_info(
5885 schema=schema,
5886 filter_names=load,
5887 available=available,
5888 kind=kind,
5889 scope=util.preloaded.engine_reflection.ObjectScope.ANY,
5890 **dialect_kwargs,
5891 )
5892 reflect_opts["_reflect_info"] = _reflect_info
5894 for name in load:
5895 try:
5896 Table(name, self, **reflect_opts)
5897 except exc.UnreflectableTableError as uerr:
5898 util.warn(f"Skipping table {name}: {uerr}")
5900 def create_all(
5901 self,
5902 bind: _CreateDropBind,
5903 tables: Optional[_typing_Sequence[Table]] = None,
5904 checkfirst: bool = True,
5905 ) -> None:
5906 """Create all tables stored in this metadata.
5908 Conditional by default, will not attempt to recreate tables already
5909 present in the target database.
5911 :param bind:
5912 A :class:`.Connection` or :class:`.Engine` used to access the
5913 database.
5915 :param tables:
5916 Optional list of ``Table`` objects, which is a subset of the total
5917 tables in the ``MetaData`` (others are ignored).
5919 :param checkfirst:
5920 Defaults to True, don't issue CREATEs for tables already present
5921 in the target database.
5923 """
5924 bind._run_ddl_visitor(
5925 ddl.SchemaGenerator, self, checkfirst=checkfirst, tables=tables
5926 )
5928 def drop_all(
5929 self,
5930 bind: _CreateDropBind,
5931 tables: Optional[_typing_Sequence[Table]] = None,
5932 checkfirst: bool = True,
5933 ) -> None:
5934 """Drop all tables stored in this metadata.
5936 Conditional by default, will not attempt to drop tables not present in
5937 the target database.
5939 :param bind:
5940 A :class:`.Connection` or :class:`.Engine` used to access the
5941 database.
5943 :param tables:
5944 Optional list of ``Table`` objects, which is a subset of the
5945 total tables in the ``MetaData`` (others are ignored).
5947 :param checkfirst:
5948 Defaults to True, only issue DROPs for tables confirmed to be
5949 present in the target database.
5951 """
5952 bind._run_ddl_visitor(
5953 ddl.SchemaDropper, self, checkfirst=checkfirst, tables=tables
5954 )
5957class Computed(FetchedValue, SchemaItem):
5958 """Defines a generated column, i.e. "GENERATED ALWAYS AS" syntax.
5960 The :class:`.Computed` construct is an inline construct added to the
5961 argument list of a :class:`_schema.Column` object::
5963 from sqlalchemy import Computed
5965 Table(
5966 "square",
5967 metadata_obj,
5968 Column("side", Float, nullable=False),
5969 Column("area", Float, Computed("side * side")),
5970 )
5972 See the linked documentation below for complete details.
5974 .. versionadded:: 1.3.11
5976 .. seealso::
5978 :ref:`computed_ddl`
5980 """
5982 __visit_name__ = "computed_column"
5984 column: Optional[Column[Any]]
5986 @_document_text_coercion(
5987 "sqltext", ":class:`.Computed`", ":paramref:`.Computed.sqltext`"
5988 )
5989 def __init__(
5990 self, sqltext: _DDLColumnArgument, persisted: Optional[bool] = None
5991 ) -> None:
5992 """Construct a GENERATED ALWAYS AS DDL construct to accompany a
5993 :class:`_schema.Column`.
5995 :param sqltext:
5996 A string containing the column generation expression, which will be
5997 used verbatim, or a SQL expression construct, such as a
5998 :func:`_expression.text`
5999 object. If given as a string, the object is converted to a
6000 :func:`_expression.text` object.
6002 :param persisted:
6003 Optional, controls how this column should be persisted by the
6004 database. Possible values are:
6006 * ``None``, the default, it will use the default persistence
6007 defined by the database.
6008 * ``True``, will render ``GENERATED ALWAYS AS ... STORED``, or the
6009 equivalent for the target database if supported.
6010 * ``False``, will render ``GENERATED ALWAYS AS ... VIRTUAL``, or
6011 the equivalent for the target database if supported.
6013 Specifying ``True`` or ``False`` may raise an error when the DDL
6014 is emitted to the target database if the database does not support
6015 that persistence option. Leaving this parameter at its default
6016 of ``None`` is guaranteed to succeed for all databases that support
6017 ``GENERATED ALWAYS AS``.
6019 """
6020 self.sqltext = coercions.expect(roles.DDLExpressionRole, sqltext)
6021 self.persisted = persisted
6022 self.column = None
6024 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
6025 assert isinstance(parent, Column)
6027 if not isinstance(
6028 parent.server_default, (type(None), Computed)
6029 ) or not isinstance(parent.server_onupdate, (type(None), Computed)):
6030 raise exc.ArgumentError(
6031 "A generated column cannot specify a server_default or a "
6032 "server_onupdate argument"
6033 )
6034 self.column = parent
6035 parent.computed = self
6036 self.column.server_onupdate = self
6037 self.column.server_default = self
6039 def _as_for_update(self, for_update: bool) -> FetchedValue:
6040 return self
6042 @util.deprecated(
6043 "1.4",
6044 "The :meth:`_schema.Computed.copy` method is deprecated "
6045 "and will be removed in a future release.",
6046 )
6047 def copy(
6048 self, *, target_table: Optional[Table] = None, **kw: Any
6049 ) -> Computed:
6050 return self._copy(target_table=target_table, **kw)
6052 def _copy(
6053 self, *, target_table: Optional[Table] = None, **kw: Any
6054 ) -> Computed:
6055 sqltext = _copy_expression(
6056 self.sqltext,
6057 self.column.table if self.column is not None else None,
6058 target_table,
6059 )
6060 g = Computed(sqltext, persisted=self.persisted)
6062 return self._schema_item_copy(g)
6065class Identity(IdentityOptions, FetchedValue, SchemaItem):
6066 """Defines an identity column, i.e. "GENERATED { ALWAYS | BY DEFAULT }
6067 AS IDENTITY" syntax.
6069 The :class:`.Identity` construct is an inline construct added to the
6070 argument list of a :class:`_schema.Column` object::
6072 from sqlalchemy import Identity
6074 Table(
6075 "foo",
6076 metadata_obj,
6077 Column("id", Integer, Identity()),
6078 Column("description", Text),
6079 )
6081 See the linked documentation below for complete details.
6083 .. versionadded:: 1.4
6085 .. seealso::
6087 :ref:`identity_ddl`
6089 """
6091 __visit_name__ = "identity_column"
6093 is_identity = True
6095 def __init__(
6096 self,
6097 always: bool = False,
6098 on_null: Optional[bool] = None,
6099 start: Optional[int] = None,
6100 increment: Optional[int] = None,
6101 minvalue: Optional[int] = None,
6102 maxvalue: Optional[int] = None,
6103 nominvalue: Optional[bool] = None,
6104 nomaxvalue: Optional[bool] = None,
6105 cycle: Optional[bool] = None,
6106 cache: Optional[int] = None,
6107 order: Optional[bool] = None,
6108 ) -> None:
6109 """Construct a GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY DDL
6110 construct to accompany a :class:`_schema.Column`.
6112 See the :class:`.Sequence` documentation for a complete description
6113 of most parameters.
6115 .. note::
6116 MSSQL supports this construct as the preferred alternative to
6117 generate an IDENTITY on a column, but it uses non standard
6118 syntax that only support :paramref:`_schema.Identity.start`
6119 and :paramref:`_schema.Identity.increment`.
6120 All other parameters are ignored.
6122 :param always:
6123 A boolean, that indicates the type of identity column.
6124 If ``False`` is specified, the default, then the user-specified
6125 value takes precedence.
6126 If ``True`` is specified, a user-specified value is not accepted (
6127 on some backends, like PostgreSQL, OVERRIDING SYSTEM VALUE, or
6128 similar, may be specified in an INSERT to override the sequence
6129 value).
6130 Some backends also have a default value for this parameter,
6131 ``None`` can be used to omit rendering this part in the DDL. It
6132 will be treated as ``False`` if a backend does not have a default
6133 value.
6135 :param on_null:
6136 Set to ``True`` to specify ON NULL in conjunction with a
6137 ``always=False`` identity column. This option is only supported on
6138 some backends, like Oracle Database.
6140 :param start: the starting index of the sequence.
6141 :param increment: the increment value of the sequence.
6142 :param minvalue: the minimum value of the sequence.
6143 :param maxvalue: the maximum value of the sequence.
6144 :param nominvalue: no minimum value of the sequence.
6145 :param nomaxvalue: no maximum value of the sequence.
6146 :param cycle: allows the sequence to wrap around when the maxvalue
6147 or minvalue has been reached.
6148 :param cache: optional integer value; number of future values in the
6149 sequence which are calculated in advance.
6150 :param order: optional boolean value; if true, renders the
6151 ORDER keyword.
6153 """
6154 IdentityOptions.__init__(
6155 self,
6156 start=start,
6157 increment=increment,
6158 minvalue=minvalue,
6159 maxvalue=maxvalue,
6160 nominvalue=nominvalue,
6161 nomaxvalue=nomaxvalue,
6162 cycle=cycle,
6163 cache=cache,
6164 order=order,
6165 )
6166 self.always = always
6167 self.on_null = on_null
6168 self.column = None
6170 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
6171 assert isinstance(parent, Column)
6172 if not isinstance(
6173 parent.server_default, (type(None), Identity)
6174 ) or not isinstance(parent.server_onupdate, type(None)):
6175 raise exc.ArgumentError(
6176 "A column with an Identity object cannot specify a "
6177 "server_default or a server_onupdate argument"
6178 )
6179 if parent.autoincrement is False:
6180 raise exc.ArgumentError(
6181 "A column with an Identity object cannot specify "
6182 "autoincrement=False"
6183 )
6184 self.column = parent
6186 parent.identity = self
6187 if parent._user_defined_nullable is NULL_UNSPECIFIED:
6188 parent.nullable = False
6190 parent.server_default = self
6192 def _as_for_update(self, for_update: bool) -> FetchedValue:
6193 return self
6195 @util.deprecated(
6196 "1.4",
6197 "The :meth:`_schema.Identity.copy` method is deprecated "
6198 "and will be removed in a future release.",
6199 )
6200 def copy(self, **kw: Any) -> Identity:
6201 return self._copy(**kw)
6203 def _copy(self, **kw: Any) -> Identity:
6204 i = Identity(
6205 always=self.always,
6206 on_null=self.on_null,
6207 start=self.start,
6208 increment=self.increment,
6209 minvalue=self.minvalue,
6210 maxvalue=self.maxvalue,
6211 nominvalue=self.nominvalue,
6212 nomaxvalue=self.nomaxvalue,
6213 cycle=self.cycle,
6214 cache=self.cache,
6215 order=self.order,
6216 )
6218 return self._schema_item_copy(i)