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-2026 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"""
32from __future__ import annotations
34from abc import ABC
35import collections
36from enum import Enum
37import operator
38import typing
39from typing import Any
40from typing import Callable
41from typing import cast
42from typing import Collection
43from typing import Dict
44from typing import Iterable
45from typing import Iterator
46from typing import List
47from typing import Mapping
48from typing import NoReturn
49from typing import Optional
50from typing import overload
51from typing import Sequence as _typing_Sequence
52from typing import Set
53from typing import Tuple
54from typing import TYPE_CHECKING
55from typing import TypeVar
56from typing import Union
58from . import coercions
59from . import ddl
60from . import roles
61from . import type_api
62from . import visitors
63from .base import _DefaultDescriptionTuple
64from .base import _NoArg
65from .base import _NoneName
66from .base import _SentinelColumnCharacterization
67from .base import _SentinelDefaultCharacterization
68from .base import DedupeColumnCollection
69from .base import DialectKWArgs
70from .base import Executable
71from .base import SchemaEventTarget as SchemaEventTarget
72from .base import SchemaVisitable as SchemaVisitable
73from .coercions import _document_text_coercion
74from .elements import ClauseElement
75from .elements import ColumnClause
76from .elements import ColumnElement
77from .elements import quoted_name
78from .elements import TextClause
79from .selectable import TableClause
80from .type_api import to_instance
81from .visitors import ExternallyTraversible
82from .. import event
83from .. import exc
84from .. import inspection
85from .. import util
86from ..util import HasMemoized
87from ..util.typing import Final
88from ..util.typing import Literal
89from ..util.typing import Protocol
90from ..util.typing import Self
91from ..util.typing import TypedDict
92from ..util.typing import TypeGuard
94if typing.TYPE_CHECKING:
95 from ._typing import _AutoIncrementType
96 from ._typing import _CreateDropBind
97 from ._typing import _DDLColumnArgument
98 from ._typing import _InfoType
99 from ._typing import _TextCoercedExpressionArgument
100 from ._typing import _TypeEngineArgument
101 from .base import ColumnSet
102 from .base import ReadOnlyColumnCollection
103 from .compiler import DDLCompiler
104 from .elements import BindParameter
105 from .elements import KeyedColumnElement
106 from .functions import Function
107 from .type_api import TypeEngine
108 from .visitors import anon_map
109 from ..engine import Connection
110 from ..engine import Engine
111 from ..engine.interfaces import _CoreMultiExecuteParams
112 from ..engine.interfaces import CoreExecuteOptionsParameter
113 from ..engine.interfaces import ExecutionContext
114 from ..engine.reflection import _ReflectionInfo
115 from ..sql.selectable import FromClause
117_T = TypeVar("_T", bound="Any")
118_SI = TypeVar("_SI", bound="SchemaItem")
119_TAB = TypeVar("_TAB", bound="Table")
122_ConstraintNameArgument = Optional[Union[str, _NoneName]]
124_ServerDefaultArgument = Union[
125 "FetchedValue", str, TextClause, ColumnElement[Any]
126]
128_ServerOnUpdateArgument = _ServerDefaultArgument
131class SchemaConst(Enum):
132 RETAIN_SCHEMA = 1
133 """Symbol indicating that a :class:`_schema.Table`, :class:`.Sequence`
134 or in some cases a :class:`_schema.ForeignKey` object, in situations
135 where the object is being copied for a :meth:`.Table.to_metadata`
136 operation, should retain the schema name that it already has.
138 """
140 BLANK_SCHEMA = 2
141 """Symbol indicating that a :class:`_schema.Table` or :class:`.Sequence`
142 should have 'None' for its schema, even if the parent
143 :class:`_schema.MetaData` has specified a schema.
145 .. seealso::
147 :paramref:`_schema.MetaData.schema`
149 :paramref:`_schema.Table.schema`
151 :paramref:`.Sequence.schema`
153 """
155 NULL_UNSPECIFIED = 3
156 """Symbol indicating the "nullable" keyword was not passed to a Column.
158 This is used to distinguish between the use case of passing
159 ``nullable=None`` to a :class:`.Column`, which has special meaning
160 on some backends such as SQL Server.
162 """
165RETAIN_SCHEMA: Final[Literal[SchemaConst.RETAIN_SCHEMA]] = (
166 SchemaConst.RETAIN_SCHEMA
167)
168BLANK_SCHEMA: Final[Literal[SchemaConst.BLANK_SCHEMA]] = (
169 SchemaConst.BLANK_SCHEMA
170)
171NULL_UNSPECIFIED: Final[Literal[SchemaConst.NULL_UNSPECIFIED]] = (
172 SchemaConst.NULL_UNSPECIFIED
173)
176def _get_table_key(name: str, schema: Optional[str]) -> str:
177 if schema is None:
178 return name
179 else:
180 return schema + "." + name
183# this should really be in sql/util.py but we'd have to
184# break an import cycle
185def _copy_expression(
186 expression: ColumnElement[Any],
187 source_table: Optional[Table],
188 target_table: Optional[Table],
189) -> ColumnElement[Any]:
190 if source_table is None or target_table is None:
191 return expression
193 fixed_source_table = source_table
194 fixed_target_table = target_table
196 def replace(
197 element: ExternallyTraversible, **kw: Any
198 ) -> Optional[ExternallyTraversible]:
199 if (
200 isinstance(element, Column)
201 and element.table is fixed_source_table
202 and element.key in fixed_source_table.c
203 ):
204 return fixed_target_table.c[element.key]
205 else:
206 return None
208 return cast(
209 ColumnElement[Any],
210 visitors.replacement_traverse(expression, {}, replace),
211 )
214@inspection._self_inspects
215class SchemaItem(SchemaVisitable):
216 """Base class for items that define a database schema."""
218 __visit_name__ = "schema_item"
220 create_drop_stringify_dialect = "default"
222 def _init_items(self, *args: SchemaItem, **kw: Any) -> None:
223 """Initialize the list of child items for this SchemaItem."""
224 for item in args:
225 if item is not None:
226 try:
227 spwd = item._set_parent_with_dispatch
228 except AttributeError as err:
229 raise exc.ArgumentError(
230 "'SchemaItem' object, such as a 'Column' or a "
231 f"'Constraint' expected, got {item!r}"
232 ) from err
233 else:
234 spwd(self, **kw)
236 def __repr__(self) -> str:
237 return util.generic_repr(self, omit_kwarg=["info"])
239 @util.memoized_property
240 def info(self) -> _InfoType:
241 """Info dictionary associated with the object, allowing user-defined
242 data to be associated with this :class:`.SchemaItem`.
244 The dictionary is automatically generated when first accessed.
245 It can also be specified in the constructor of some objects,
246 such as :class:`_schema.Table` and :class:`_schema.Column`.
248 """
249 return {}
251 def _schema_item_copy(self, schema_item: _SI) -> _SI:
252 if "info" in self.__dict__:
253 schema_item.info = self.info.copy()
254 schema_item.dispatch._update(self.dispatch)
255 return schema_item
257 _use_schema_map = True
260class HasConditionalDDL:
261 """define a class that includes the :meth:`.HasConditionalDDL.ddl_if`
262 method, allowing for conditional rendering of DDL.
264 Currently applies to constraints and indexes.
266 .. versionadded:: 2.0
269 """
271 _ddl_if: Optional[ddl.DDLIf] = None
273 def ddl_if(
274 self,
275 dialect: Optional[str] = None,
276 callable_: Optional[ddl.DDLIfCallable] = None,
277 state: Optional[Any] = None,
278 ) -> Self:
279 r"""apply a conditional DDL rule to this schema item.
281 These rules work in a similar manner to the
282 :meth:`.ExecutableDDLElement.execute_if` callable, with the added
283 feature that the criteria may be checked within the DDL compilation
284 phase for a construct such as :class:`.CreateTable`.
285 :meth:`.HasConditionalDDL.ddl_if` currently applies towards the
286 :class:`.Index` construct as well as all :class:`.Constraint`
287 constructs.
289 :param dialect: string name of a dialect, or a tuple of string names
290 to indicate multiple dialect types.
292 :param callable\_: a callable that is constructed using the same form
293 as that described in
294 :paramref:`.ExecutableDDLElement.execute_if.callable_`.
296 :param state: any arbitrary object that will be passed to the
297 callable, if present.
299 .. versionadded:: 2.0
301 .. seealso::
303 :ref:`schema_ddl_ddl_if` - background and usage examples
306 """
307 self._ddl_if = ddl.DDLIf(dialect, callable_, state)
308 return self
311class HasSchemaAttr(SchemaItem):
312 """schema item that includes a top-level schema name"""
314 schema: Optional[str]
317class Table(
318 DialectKWArgs, HasSchemaAttr, TableClause, inspection.Inspectable["Table"]
319):
320 r"""Represent a table in a database.
322 e.g.::
324 mytable = Table(
325 "mytable",
326 metadata,
327 Column("mytable_id", Integer, primary_key=True),
328 Column("value", String(50)),
329 )
331 The :class:`_schema.Table`
332 object constructs a unique instance of itself based
333 on its name and optional schema name within the given
334 :class:`_schema.MetaData` object. Calling the :class:`_schema.Table`
335 constructor with the same name and same :class:`_schema.MetaData` argument
336 a second time will return the *same* :class:`_schema.Table`
337 object - in this way
338 the :class:`_schema.Table` constructor acts as a registry function.
340 .. seealso::
342 :ref:`metadata_describing` - Introduction to database metadata
344 """
346 __visit_name__ = "table"
348 if TYPE_CHECKING:
350 @util.ro_non_memoized_property
351 def primary_key(self) -> PrimaryKeyConstraint: ...
353 @util.ro_non_memoized_property
354 def foreign_keys(self) -> Set[ForeignKey]: ...
356 _columns: DedupeColumnCollection[Column[Any]]
358 _sentinel_column: Optional[Column[Any]]
360 constraints: Set[Constraint]
361 """A collection of all :class:`_schema.Constraint` objects associated with
362 this :class:`_schema.Table`.
364 Includes :class:`_schema.PrimaryKeyConstraint`,
365 :class:`_schema.ForeignKeyConstraint`, :class:`_schema.UniqueConstraint`,
366 :class:`_schema.CheckConstraint`. A separate collection
367 :attr:`_schema.Table.foreign_key_constraints` refers to the collection
368 of all :class:`_schema.ForeignKeyConstraint` objects, and the
369 :attr:`_schema.Table.primary_key` attribute refers to the single
370 :class:`_schema.PrimaryKeyConstraint` associated with the
371 :class:`_schema.Table`.
373 .. seealso::
375 :attr:`_schema.Table.constraints`
377 :attr:`_schema.Table.primary_key`
379 :attr:`_schema.Table.foreign_key_constraints`
381 :attr:`_schema.Table.indexes`
383 :class:`_reflection.Inspector`
386 """
388 indexes: Set[Index]
389 """A collection of all :class:`_schema.Index` objects associated with this
390 :class:`_schema.Table`.
392 .. seealso::
394 :meth:`_reflection.Inspector.get_indexes`
396 """
398 if TYPE_CHECKING:
400 @util.ro_non_memoized_property
401 def columns(self) -> ReadOnlyColumnCollection[str, Column[Any]]: ...
403 @util.ro_non_memoized_property
404 def exported_columns(
405 self,
406 ) -> ReadOnlyColumnCollection[str, Column[Any]]: ...
408 @util.ro_non_memoized_property
409 def c(self) -> ReadOnlyColumnCollection[str, Column[Any]]: ...
411 def _gen_cache_key(
412 self, anon_map: anon_map, bindparams: List[BindParameter[Any]]
413 ) -> Tuple[Any, ...]:
414 if self._annotations:
415 return (self,) + self._annotations_cache_key
416 else:
417 return (self,)
419 if not typing.TYPE_CHECKING:
420 # typing tools seem to be inconsistent in how they handle
421 # __new__, so suggest this pattern for classes that use
422 # __new__. apply typing to the __init__ method normally
423 @util.deprecated_params(
424 mustexist=(
425 "1.4",
426 "Deprecated alias of :paramref:`_schema.Table.must_exist`",
427 ),
428 )
429 def __new__(cls, *args: Any, **kw: Any) -> Any:
430 return cls._new(*args, **kw)
432 @classmethod
433 def _new(cls, *args: Any, **kw: Any) -> Any:
434 if not args and not kw:
435 # python3k pickle seems to call this
436 return object.__new__(cls)
438 try:
439 name, metadata, args = args[0], args[1], args[2:]
440 except IndexError:
441 raise TypeError(
442 "Table() takes at least two positional-only "
443 "arguments 'name' and 'metadata'"
444 )
446 schema = kw.get("schema", None)
447 if schema is None:
448 schema = metadata.schema
449 elif schema is BLANK_SCHEMA:
450 schema = None
451 keep_existing = kw.get("keep_existing", False)
452 extend_existing = kw.get("extend_existing", False)
454 if keep_existing and extend_existing:
455 msg = "keep_existing and extend_existing are mutually exclusive."
456 raise exc.ArgumentError(msg)
458 must_exist = kw.pop("must_exist", kw.pop("mustexist", False))
459 key = _get_table_key(name, schema)
460 if key in metadata.tables:
461 if not keep_existing and not extend_existing and bool(args):
462 raise exc.InvalidRequestError(
463 f"Table '{key}' is already defined for this MetaData "
464 "instance. Specify 'extend_existing=True' "
465 "to redefine "
466 "options and columns on an "
467 "existing Table object."
468 )
469 table = metadata.tables[key]
470 if extend_existing:
471 table._init_existing(*args, **kw)
472 return table
473 else:
474 if must_exist:
475 raise exc.InvalidRequestError(f"Table '{key}' not defined")
476 table = object.__new__(cls)
477 table.dispatch.before_parent_attach(table, metadata)
478 metadata._add_table(name, schema, table)
479 try:
480 table.__init__(name, metadata, *args, _no_init=False, **kw) # type: ignore[misc] # noqa: E501
481 table.dispatch.after_parent_attach(table, metadata)
482 return table
483 except Exception:
484 with util.safe_reraise():
485 metadata._remove_table(name, schema)
487 def __init__(
488 self,
489 name: str,
490 metadata: MetaData,
491 *args: SchemaItem,
492 schema: Optional[Union[str, Literal[SchemaConst.BLANK_SCHEMA]]] = None,
493 quote: Optional[bool] = None,
494 quote_schema: Optional[bool] = None,
495 autoload_with: Optional[Union[Engine, Connection]] = None,
496 autoload_replace: bool = True,
497 keep_existing: bool = False,
498 extend_existing: bool = False,
499 resolve_fks: bool = True,
500 include_columns: Optional[Collection[str]] = None,
501 implicit_returning: bool = True,
502 comment: Optional[str] = None,
503 info: Optional[Dict[Any, Any]] = None,
504 listeners: Optional[
505 _typing_Sequence[Tuple[str, Callable[..., Any]]]
506 ] = None,
507 prefixes: Optional[_typing_Sequence[str]] = None,
508 # used internally in the metadata.reflect() process
509 _extend_on: Optional[Set[Table]] = None,
510 # used by __new__ to bypass __init__
511 _no_init: bool = True,
512 # dialect-specific keyword args
513 **kw: Any,
514 ) -> None:
515 r"""Constructor for :class:`_schema.Table`.
518 :param name: The name of this table as represented in the database.
520 The table name, along with the value of the ``schema`` parameter,
521 forms a key which uniquely identifies this :class:`_schema.Table`
522 within
523 the owning :class:`_schema.MetaData` collection.
524 Additional calls to :class:`_schema.Table` with the same name,
525 metadata,
526 and schema name will return the same :class:`_schema.Table` object.
528 Names which contain no upper case characters
529 will be treated as case insensitive names, and will not be quoted
530 unless they are a reserved word or contain special characters.
531 A name with any number of upper case characters is considered
532 to be case sensitive, and will be sent as quoted.
534 To enable unconditional quoting for the table name, specify the flag
535 ``quote=True`` to the constructor, or use the :class:`.quoted_name`
536 construct to specify the name.
538 :param metadata: a :class:`_schema.MetaData`
539 object which will contain this
540 table. The metadata is used as a point of association of this table
541 with other tables which are referenced via foreign key. It also
542 may be used to associate this table with a particular
543 :class:`.Connection` or :class:`.Engine`.
545 :param \*args: Additional positional arguments are used primarily
546 to add the list of :class:`_schema.Column`
547 objects contained within this
548 table. Similar to the style of a CREATE TABLE statement, other
549 :class:`.SchemaItem` constructs may be added here, including
550 :class:`.PrimaryKeyConstraint`, and
551 :class:`_schema.ForeignKeyConstraint`.
553 :param autoload_replace: Defaults to ``True``; when using
554 :paramref:`_schema.Table.autoload_with`
555 in conjunction with :paramref:`_schema.Table.extend_existing`,
556 indicates
557 that :class:`_schema.Column` objects present in the already-existing
558 :class:`_schema.Table`
559 object should be replaced with columns of the same
560 name retrieved from the autoload process. When ``False``, columns
561 already present under existing names will be omitted from the
562 reflection process.
564 Note that this setting does not impact :class:`_schema.Column` objects
565 specified programmatically within the call to :class:`_schema.Table`
566 that
567 also is autoloading; those :class:`_schema.Column` objects will always
568 replace existing columns of the same name when
569 :paramref:`_schema.Table.extend_existing` is ``True``.
571 .. seealso::
573 :paramref:`_schema.Table.autoload_with`
575 :paramref:`_schema.Table.extend_existing`
577 :param autoload_with: An :class:`_engine.Engine` or
578 :class:`_engine.Connection` object,
579 or a :class:`_reflection.Inspector` object as returned by
580 :func:`_sa.inspect`
581 against one, with which this :class:`_schema.Table`
582 object will be reflected.
583 When set to a non-None value, the autoload process will take place
584 for this table against the given engine or connection.
586 .. seealso::
588 :ref:`metadata_reflection_toplevel`
590 :meth:`_events.DDLEvents.column_reflect`
592 :ref:`metadata_reflection_dbagnostic_types`
594 :param extend_existing: When ``True``, indicates that if this
595 :class:`_schema.Table` is already present in the given
596 :class:`_schema.MetaData`,
597 apply further arguments within the constructor to the existing
598 :class:`_schema.Table`.
600 If :paramref:`_schema.Table.extend_existing` or
601 :paramref:`_schema.Table.keep_existing` are not set,
602 and the given name
603 of the new :class:`_schema.Table` refers to a :class:`_schema.Table`
604 that is
605 already present in the target :class:`_schema.MetaData` collection,
606 and
607 this :class:`_schema.Table`
608 specifies additional columns or other constructs
609 or flags that modify the table's state, an
610 error is raised. The purpose of these two mutually-exclusive flags
611 is to specify what action should be taken when a
612 :class:`_schema.Table`
613 is specified that matches an existing :class:`_schema.Table`,
614 yet specifies
615 additional constructs.
617 :paramref:`_schema.Table.extend_existing`
618 will also work in conjunction
619 with :paramref:`_schema.Table.autoload_with` to run a new reflection
620 operation against the database, even if a :class:`_schema.Table`
621 of the same name is already present in the target
622 :class:`_schema.MetaData`; newly reflected :class:`_schema.Column`
623 objects
624 and other options will be added into the state of the
625 :class:`_schema.Table`, potentially overwriting existing columns
626 and options of the same name.
628 As is always the case with :paramref:`_schema.Table.autoload_with`,
629 :class:`_schema.Column` objects can be specified in the same
630 :class:`_schema.Table`
631 constructor, which will take precedence. Below, the existing
632 table ``mytable`` will be augmented with :class:`_schema.Column`
633 objects
634 both reflected from the database, as well as the given
635 :class:`_schema.Column`
636 named "y"::
638 Table(
639 "mytable",
640 metadata,
641 Column("y", Integer),
642 extend_existing=True,
643 autoload_with=engine,
644 )
646 .. seealso::
648 :paramref:`_schema.Table.autoload_with`
650 :paramref:`_schema.Table.autoload_replace`
652 :paramref:`_schema.Table.keep_existing`
655 :param implicit_returning: True by default - indicates that
656 RETURNING can be used, typically by the ORM, in order to fetch
657 server-generated values such as primary key values and
658 server side defaults, on those backends which support RETURNING.
660 In modern SQLAlchemy there is generally no reason to alter this
661 setting, except for some backend specific cases
662 (see :ref:`mssql_triggers` in the SQL Server dialect documentation
663 for one such example).
665 :param include_columns: A list of strings indicating a subset of
666 columns to be loaded via the ``autoload`` operation; table columns who
667 aren't present in this list will not be represented on the resulting
668 ``Table`` object. Defaults to ``None`` which indicates all columns
669 should be reflected.
671 :param resolve_fks: Whether or not to reflect :class:`_schema.Table`
672 objects
673 related to this one via :class:`_schema.ForeignKey` objects, when
674 :paramref:`_schema.Table.autoload_with` is
675 specified. Defaults to True. Set to False to disable reflection of
676 related tables as :class:`_schema.ForeignKey`
677 objects are encountered; may be
678 used either to save on SQL calls or to avoid issues with related tables
679 that can't be accessed. Note that if a related table is already present
680 in the :class:`_schema.MetaData` collection, or becomes present later,
681 a
682 :class:`_schema.ForeignKey` object associated with this
683 :class:`_schema.Table` will
684 resolve to that table normally.
686 .. versionadded:: 1.3
688 .. seealso::
690 :paramref:`.MetaData.reflect.resolve_fks`
693 :param info: Optional data dictionary which will be populated into the
694 :attr:`.SchemaItem.info` attribute of this object.
696 :param keep_existing: When ``True``, indicates that if this Table
697 is already present in the given :class:`_schema.MetaData`, ignore
698 further arguments within the constructor to the existing
699 :class:`_schema.Table`, and return the :class:`_schema.Table`
700 object as
701 originally created. This is to allow a function that wishes
702 to define a new :class:`_schema.Table` on first call, but on
703 subsequent calls will return the same :class:`_schema.Table`,
704 without any of the declarations (particularly constraints)
705 being applied a second time.
707 If :paramref:`_schema.Table.extend_existing` or
708 :paramref:`_schema.Table.keep_existing` are not set,
709 and the given name
710 of the new :class:`_schema.Table` refers to a :class:`_schema.Table`
711 that is
712 already present in the target :class:`_schema.MetaData` collection,
713 and
714 this :class:`_schema.Table`
715 specifies additional columns or other constructs
716 or flags that modify the table's state, an
717 error is raised. The purpose of these two mutually-exclusive flags
718 is to specify what action should be taken when a
719 :class:`_schema.Table`
720 is specified that matches an existing :class:`_schema.Table`,
721 yet specifies
722 additional constructs.
724 .. seealso::
726 :paramref:`_schema.Table.extend_existing`
728 :param listeners: A list of tuples of the form ``(<eventname>, <fn>)``
729 which will be passed to :func:`.event.listen` upon construction.
730 This alternate hook to :func:`.event.listen` allows the establishment
731 of a listener function specific to this :class:`_schema.Table` before
732 the "autoload" process begins. Historically this has been intended
733 for use with the :meth:`.DDLEvents.column_reflect` event, however
734 note that this event hook may now be associated with the
735 :class:`_schema.MetaData` object directly::
737 def listen_for_reflect(table, column_info):
738 "handle the column reflection event"
739 # ...
742 t = Table(
743 "sometable",
744 autoload_with=engine,
745 listeners=[("column_reflect", listen_for_reflect)],
746 )
748 .. seealso::
750 :meth:`_events.DDLEvents.column_reflect`
752 :param must_exist: When ``True``, indicates that this Table must already
753 be present in the given :class:`_schema.MetaData` collection, else
754 an exception is raised.
756 :param prefixes:
757 A list of strings to insert after CREATE in the CREATE TABLE
758 statement. They will be separated by spaces.
760 :param quote: Force quoting of this table's name on or off, corresponding
761 to ``True`` or ``False``. When left at its default of ``None``,
762 the column identifier will be quoted according to whether the name is
763 case sensitive (identifiers with at least one upper case character are
764 treated as case sensitive), or if it's a reserved word. This flag
765 is only needed to force quoting of a reserved word which is not known
766 by the SQLAlchemy dialect.
768 .. note:: setting this flag to ``False`` will not provide
769 case-insensitive behavior for table reflection; table reflection
770 will always search for a mixed-case name in a case sensitive
771 fashion. Case insensitive names are specified in SQLAlchemy only
772 by stating the name with all lower case characters.
774 :param quote_schema: same as 'quote' but applies to the schema identifier.
776 :param schema: The schema name for this table, which is required if
777 the table resides in a schema other than the default selected schema
778 for the engine's database connection. Defaults to ``None``.
780 If the owning :class:`_schema.MetaData` of this :class:`_schema.Table`
781 specifies its
782 own :paramref:`_schema.MetaData.schema` parameter,
783 then that schema name will
784 be applied to this :class:`_schema.Table`
785 if the schema parameter here is set
786 to ``None``. To set a blank schema name on a :class:`_schema.Table`
787 that
788 would otherwise use the schema set on the owning
789 :class:`_schema.MetaData`,
790 specify the special symbol :attr:`.BLANK_SCHEMA`.
792 The quoting rules for the schema name are the same as those for the
793 ``name`` parameter, in that quoting is applied for reserved words or
794 case-sensitive names; to enable unconditional quoting for the schema
795 name, specify the flag ``quote_schema=True`` to the constructor, or use
796 the :class:`.quoted_name` construct to specify the name.
798 :param comment: Optional string that will render an SQL comment on table
799 creation.
801 .. versionadded:: 1.2 Added the :paramref:`_schema.Table.comment`
802 parameter
803 to :class:`_schema.Table`.
805 :param \**kw: Additional keyword arguments not mentioned above are
806 dialect specific, and passed in the form ``<dialectname>_<argname>``.
807 See the documentation regarding an individual dialect at
808 :ref:`dialect_toplevel` for detail on documented arguments.
810 """ # noqa: E501
811 if _no_init:
812 # don't run __init__ from __new__ by default;
813 # __new__ has a specific place that __init__ is called
814 return
816 super().__init__(quoted_name(name, quote))
817 self.metadata = metadata
819 if schema is None:
820 self.schema = metadata.schema
821 elif schema is BLANK_SCHEMA:
822 self.schema = None
823 else:
824 quote_schema = quote_schema
825 assert isinstance(schema, str)
826 self.schema = quoted_name(schema, quote_schema)
828 self._sentinel_column = None
830 self.indexes = set()
831 self.constraints = set()
832 PrimaryKeyConstraint(
833 _implicit_generated=True
834 )._set_parent_with_dispatch(self)
835 self.foreign_keys = set() # type: ignore
836 self._extra_dependencies: Set[Table] = set()
837 if self.schema is not None:
838 self.fullname = "%s.%s" % (self.schema, self.name)
839 else:
840 self.fullname = self.name
842 self.implicit_returning = implicit_returning
843 _reflect_info = kw.pop("_reflect_info", None)
845 self.comment = comment
847 if info is not None:
848 self.info = info
850 if listeners is not None:
851 for evt, fn in listeners:
852 event.listen(self, evt, fn)
854 self._prefixes = prefixes if prefixes else []
856 self._extra_kwargs(**kw)
858 # load column definitions from the database if 'autoload' is defined
859 # we do it after the table is in the singleton dictionary to support
860 # circular foreign keys
861 if autoload_with is not None:
862 self._autoload(
863 metadata,
864 autoload_with,
865 include_columns,
866 _extend_on=_extend_on,
867 _reflect_info=_reflect_info,
868 resolve_fks=resolve_fks,
869 )
871 # initialize all the column, etc. objects. done after reflection to
872 # allow user-overrides
874 self._init_items(
875 *args,
876 allow_replacements=extend_existing
877 or keep_existing
878 or autoload_with,
879 all_names={},
880 )
882 def _autoload(
883 self,
884 metadata: MetaData,
885 autoload_with: Union[Engine, Connection],
886 include_columns: Optional[Collection[str]],
887 exclude_columns: Collection[str] = (),
888 resolve_fks: bool = True,
889 _extend_on: Optional[Set[Table]] = None,
890 _reflect_info: _ReflectionInfo | None = None,
891 ) -> None:
892 insp = inspection.inspect(autoload_with)
893 with insp._inspection_context() as conn_insp:
894 conn_insp.reflect_table(
895 self,
896 include_columns,
897 exclude_columns,
898 resolve_fks,
899 _extend_on=_extend_on,
900 _reflect_info=_reflect_info,
901 )
903 @property
904 def _sorted_constraints(self) -> List[Constraint]:
905 """Return the set of constraints as a list, sorted by creation
906 order.
908 """
910 return sorted(self.constraints, key=lambda c: c._creation_order)
912 @property
913 def foreign_key_constraints(self) -> Set[ForeignKeyConstraint]:
914 """:class:`_schema.ForeignKeyConstraint` objects referred to by this
915 :class:`_schema.Table`.
917 This list is produced from the collection of
918 :class:`_schema.ForeignKey`
919 objects currently associated.
922 .. seealso::
924 :attr:`_schema.Table.constraints`
926 :attr:`_schema.Table.foreign_keys`
928 :attr:`_schema.Table.indexes`
930 """
931 return {
932 fkc.constraint
933 for fkc in self.foreign_keys
934 if fkc.constraint is not None
935 }
937 def _init_existing(self, *args: Any, **kwargs: Any) -> None:
938 autoload_with = kwargs.pop("autoload_with", None)
939 autoload = kwargs.pop("autoload", autoload_with is not None)
940 autoload_replace = kwargs.pop("autoload_replace", True)
941 schema = kwargs.pop("schema", None)
942 _extend_on = kwargs.pop("_extend_on", None)
943 _reflect_info = kwargs.pop("_reflect_info", None)
945 # these arguments are only used with _init()
946 extend_existing = kwargs.pop("extend_existing", False)
947 keep_existing = kwargs.pop("keep_existing", False)
949 assert extend_existing
950 assert not keep_existing
952 if schema and schema != self.schema:
953 raise exc.ArgumentError(
954 f"Can't change schema of existing table "
955 f"from '{self.schema}' to '{schema}'",
956 )
958 include_columns = kwargs.pop("include_columns", None)
959 if include_columns is not None:
960 for c in self.c:
961 if c.name not in include_columns:
962 self._columns.remove(c)
964 resolve_fks = kwargs.pop("resolve_fks", True)
966 for key in ("quote", "quote_schema"):
967 if key in kwargs:
968 raise exc.ArgumentError(
969 "Can't redefine 'quote' or 'quote_schema' arguments"
970 )
972 # update `self` with these kwargs, if provided
973 self.comment = kwargs.pop("comment", self.comment)
974 self.implicit_returning = kwargs.pop(
975 "implicit_returning", self.implicit_returning
976 )
977 self.info = kwargs.pop("info", self.info)
979 exclude_columns: _typing_Sequence[str]
981 if autoload:
982 if not autoload_replace:
983 # don't replace columns already present.
984 # we'd like to do this for constraints also however we don't
985 # have simple de-duping for unnamed constraints.
986 exclude_columns = [c.name for c in self.c]
987 else:
988 exclude_columns = ()
989 self._autoload(
990 self.metadata,
991 autoload_with,
992 include_columns,
993 exclude_columns,
994 resolve_fks,
995 _extend_on=_extend_on,
996 _reflect_info=_reflect_info,
997 )
999 all_names = {c.name: c for c in self.c}
1000 self._extra_kwargs(**kwargs)
1001 self._init_items(*args, allow_replacements=True, all_names=all_names)
1003 def _extra_kwargs(self, **kwargs: Any) -> None:
1004 self._validate_dialect_kwargs(kwargs)
1006 def _init_collections(self) -> None:
1007 pass
1009 def _reset_exported(self) -> None:
1010 pass
1012 @util.ro_non_memoized_property
1013 def _autoincrement_column(self) -> Optional[Column[int]]:
1014 return self.primary_key._autoincrement_column
1016 @util.ro_memoized_property
1017 def _sentinel_column_characteristics(
1018 self,
1019 ) -> _SentinelColumnCharacterization:
1020 """determine a candidate column (or columns, in case of a client
1021 generated composite primary key) which can be used as an
1022 "insert sentinel" for an INSERT statement.
1024 The returned structure, :class:`_SentinelColumnCharacterization`,
1025 includes all the details needed by :class:`.Dialect` and
1026 :class:`.SQLCompiler` to determine if these column(s) can be used
1027 as an INSERT..RETURNING sentinel for a particular database
1028 dialect.
1030 .. versionadded:: 2.0.10
1032 """
1034 sentinel_is_explicit = False
1035 sentinel_is_autoinc = False
1036 the_sentinel: Optional[_typing_Sequence[Column[Any]]] = None
1038 # see if a column was explicitly marked "insert_sentinel=True".
1039 explicit_sentinel_col = self._sentinel_column
1041 if explicit_sentinel_col is not None:
1042 the_sentinel = (explicit_sentinel_col,)
1043 sentinel_is_explicit = True
1045 autoinc_col = self._autoincrement_column
1046 if sentinel_is_explicit and explicit_sentinel_col is autoinc_col:
1047 assert autoinc_col is not None
1048 sentinel_is_autoinc = True
1049 elif explicit_sentinel_col is None and autoinc_col is not None:
1050 the_sentinel = (autoinc_col,)
1051 sentinel_is_autoinc = True
1053 default_characterization = _SentinelDefaultCharacterization.UNKNOWN
1055 if the_sentinel:
1056 the_sentinel_zero = the_sentinel[0]
1057 if the_sentinel_zero.identity:
1058 if the_sentinel_zero.identity._increment_is_negative:
1059 if sentinel_is_explicit:
1060 raise exc.InvalidRequestError(
1061 "Can't use IDENTITY default with negative "
1062 "increment as an explicit sentinel column"
1063 )
1064 else:
1065 if sentinel_is_autoinc:
1066 autoinc_col = None
1067 sentinel_is_autoinc = False
1068 the_sentinel = None
1069 else:
1070 default_characterization = (
1071 _SentinelDefaultCharacterization.IDENTITY
1072 )
1073 elif (
1074 the_sentinel_zero.default is None
1075 and the_sentinel_zero.server_default is None
1076 ):
1077 if the_sentinel_zero.nullable:
1078 raise exc.InvalidRequestError(
1079 f"Column {the_sentinel_zero} has been marked as a "
1080 "sentinel "
1081 "column with no default generation function; it "
1082 "at least needs to be marked nullable=False assuming "
1083 "user-populated sentinel values will be used."
1084 )
1085 default_characterization = (
1086 _SentinelDefaultCharacterization.NONE
1087 )
1088 elif the_sentinel_zero.default is not None:
1089 if the_sentinel_zero.default.is_sentinel:
1090 default_characterization = (
1091 _SentinelDefaultCharacterization.SENTINEL_DEFAULT
1092 )
1093 elif default_is_sequence(the_sentinel_zero.default):
1094 if the_sentinel_zero.default._increment_is_negative:
1095 if sentinel_is_explicit:
1096 raise exc.InvalidRequestError(
1097 "Can't use SEQUENCE default with negative "
1098 "increment as an explicit sentinel column"
1099 )
1100 else:
1101 if sentinel_is_autoinc:
1102 autoinc_col = None
1103 sentinel_is_autoinc = False
1104 the_sentinel = None
1106 default_characterization = (
1107 _SentinelDefaultCharacterization.SEQUENCE
1108 )
1109 elif the_sentinel_zero.default.is_callable:
1110 default_characterization = (
1111 _SentinelDefaultCharacterization.CLIENTSIDE
1112 )
1113 elif the_sentinel_zero.server_default is not None:
1114 if sentinel_is_explicit:
1115 raise exc.InvalidRequestError(
1116 f"Column {the_sentinel[0]} can't be a sentinel column "
1117 "because it uses an explicit server side default "
1118 "that's not the Identity() default."
1119 )
1121 default_characterization = (
1122 _SentinelDefaultCharacterization.SERVERSIDE
1123 )
1125 if the_sentinel is None and self.primary_key:
1126 assert autoinc_col is None
1128 # determine for non-autoincrement pk if all elements are
1129 # client side
1130 for _pkc in self.primary_key:
1131 if _pkc.server_default is not None or (
1132 _pkc.default and not _pkc.default.is_callable
1133 ):
1134 break
1135 else:
1136 the_sentinel = tuple(self.primary_key)
1137 default_characterization = (
1138 _SentinelDefaultCharacterization.CLIENTSIDE
1139 )
1141 return _SentinelColumnCharacterization(
1142 the_sentinel,
1143 sentinel_is_explicit,
1144 sentinel_is_autoinc,
1145 default_characterization,
1146 )
1148 @property
1149 def autoincrement_column(self) -> Optional[Column[int]]:
1150 """Returns the :class:`.Column` object which currently represents
1151 the "auto increment" column, if any, else returns None.
1153 This is based on the rules for :class:`.Column` as defined by the
1154 :paramref:`.Column.autoincrement` parameter, which generally means the
1155 column within a single integer column primary key constraint that is
1156 not constrained by a foreign key. If the table does not have such
1157 a primary key constraint, then there's no "autoincrement" column.
1158 A :class:`.Table` may have only one column defined as the
1159 "autoincrement" column.
1161 .. versionadded:: 2.0.4
1163 .. seealso::
1165 :paramref:`.Column.autoincrement`
1167 """
1168 return self._autoincrement_column
1170 @property
1171 def key(self) -> str:
1172 """Return the 'key' for this :class:`_schema.Table`.
1174 This value is used as the dictionary key within the
1175 :attr:`_schema.MetaData.tables` collection. It is typically the same
1176 as that of :attr:`_schema.Table.name` for a table with no
1177 :attr:`_schema.Table.schema`
1178 set; otherwise it is typically of the form
1179 ``schemaname.tablename``.
1181 """
1182 return _get_table_key(self.name, self.schema)
1184 def __repr__(self) -> str:
1185 return "Table(%s)" % ", ".join(
1186 [repr(self.name)]
1187 + [repr(self.metadata)]
1188 + [repr(x) for x in self.columns]
1189 + ["%s=%s" % (k, repr(getattr(self, k))) for k in ["schema"]]
1190 )
1192 def __str__(self) -> str:
1193 return _get_table_key(self.description, self.schema)
1195 def add_is_dependent_on(self, table: Table) -> None:
1196 """Add a 'dependency' for this Table.
1198 This is another Table object which must be created
1199 first before this one can, or dropped after this one.
1201 Usually, dependencies between tables are determined via
1202 ForeignKey objects. However, for other situations that
1203 create dependencies outside of foreign keys (rules, inheriting),
1204 this method can manually establish such a link.
1206 """
1207 self._extra_dependencies.add(table)
1209 def append_column(
1210 self, column: ColumnClause[Any], replace_existing: bool = False
1211 ) -> None:
1212 """Append a :class:`_schema.Column` to this :class:`_schema.Table`.
1214 The "key" of the newly added :class:`_schema.Column`, i.e. the
1215 value of its ``.key`` attribute, will then be available
1216 in the ``.c`` collection of this :class:`_schema.Table`, and the
1217 column definition will be included in any CREATE TABLE, SELECT,
1218 UPDATE, etc. statements generated from this :class:`_schema.Table`
1219 construct.
1221 Note that this does **not** change the definition of the table
1222 as it exists within any underlying database, assuming that
1223 table has already been created in the database. Relational
1224 databases support the addition of columns to existing tables
1225 using the SQL ALTER command, which would need to be
1226 emitted for an already-existing table that doesn't contain
1227 the newly added column.
1229 :param replace_existing: When ``True``, allows replacing existing
1230 columns. When ``False``, the default, an warning will be raised
1231 if a column with the same ``.key`` already exists. A future
1232 version of sqlalchemy will instead rise a warning.
1234 .. versionadded:: 1.4.0
1235 """
1237 try:
1238 column._set_parent_with_dispatch(
1239 self,
1240 allow_replacements=replace_existing,
1241 all_names={c.name: c for c in self.c},
1242 )
1243 except exc.DuplicateColumnError as de:
1244 raise exc.DuplicateColumnError(
1245 f"{de.args[0]} Specify replace_existing=True to "
1246 "Table.append_column() to replace an "
1247 "existing column."
1248 ) from de
1250 def append_constraint(self, constraint: Union[Index, Constraint]) -> None:
1251 """Append a :class:`_schema.Constraint` to this
1252 :class:`_schema.Table`.
1254 This has the effect of the constraint being included in any
1255 future CREATE TABLE statement, assuming specific DDL creation
1256 events have not been associated with the given
1257 :class:`_schema.Constraint` object.
1259 Note that this does **not** produce the constraint within the
1260 relational database automatically, for a table that already exists
1261 in the database. To add a constraint to an
1262 existing relational database table, the SQL ALTER command must
1263 be used. SQLAlchemy also provides the
1264 :class:`.AddConstraint` construct which can produce this SQL when
1265 invoked as an executable clause.
1267 """
1269 constraint._set_parent_with_dispatch(self)
1271 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
1272 metadata = parent
1273 assert isinstance(metadata, MetaData)
1274 metadata._add_table(self.name, self.schema, self)
1275 self.metadata = metadata
1277 def create(self, bind: _CreateDropBind, checkfirst: bool = False) -> None:
1278 """Issue a ``CREATE`` statement for this
1279 :class:`_schema.Table`, using the given
1280 :class:`.Connection` or :class:`.Engine`
1281 for connectivity.
1283 .. seealso::
1285 :meth:`_schema.MetaData.create_all`.
1287 """
1289 bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
1291 def drop(self, bind: _CreateDropBind, checkfirst: bool = False) -> None:
1292 """Issue a ``DROP`` statement for this
1293 :class:`_schema.Table`, using the given
1294 :class:`.Connection` or :class:`.Engine` for connectivity.
1296 .. seealso::
1298 :meth:`_schema.MetaData.drop_all`.
1300 """
1301 bind._run_ddl_visitor(ddl.SchemaDropper, self, checkfirst=checkfirst)
1303 @util.deprecated(
1304 "1.4",
1305 ":meth:`_schema.Table.tometadata` is renamed to "
1306 ":meth:`_schema.Table.to_metadata`",
1307 )
1308 def tometadata(
1309 self,
1310 metadata: MetaData,
1311 schema: Union[str, Literal[SchemaConst.RETAIN_SCHEMA]] = RETAIN_SCHEMA,
1312 referred_schema_fn: Optional[
1313 Callable[
1314 [Table, Optional[str], ForeignKeyConstraint, Optional[str]],
1315 Optional[str],
1316 ]
1317 ] = None,
1318 name: Optional[str] = None,
1319 ) -> Table:
1320 """Return a copy of this :class:`_schema.Table`
1321 associated with a different
1322 :class:`_schema.MetaData`.
1324 See :meth:`_schema.Table.to_metadata` for a full description.
1326 """
1327 return self.to_metadata(
1328 metadata,
1329 schema=schema,
1330 referred_schema_fn=referred_schema_fn,
1331 name=name,
1332 )
1334 def to_metadata(
1335 self,
1336 metadata: MetaData,
1337 schema: Union[str, Literal[SchemaConst.RETAIN_SCHEMA]] = RETAIN_SCHEMA,
1338 referred_schema_fn: Optional[
1339 Callable[
1340 [Table, Optional[str], ForeignKeyConstraint, Optional[str]],
1341 Optional[str],
1342 ]
1343 ] = None,
1344 name: Optional[str] = None,
1345 ) -> Table:
1346 """Return a copy of this :class:`_schema.Table` associated with a
1347 different :class:`_schema.MetaData`.
1349 E.g.::
1351 m1 = MetaData()
1353 user = Table("user", m1, Column("id", Integer, primary_key=True))
1355 m2 = MetaData()
1356 user_copy = user.to_metadata(m2)
1358 .. versionchanged:: 1.4 The :meth:`_schema.Table.to_metadata` function
1359 was renamed from :meth:`_schema.Table.tometadata`.
1362 :param metadata: Target :class:`_schema.MetaData` object,
1363 into which the
1364 new :class:`_schema.Table` object will be created.
1366 :param schema: optional string name indicating the target schema.
1367 Defaults to the special symbol :attr:`.RETAIN_SCHEMA` which indicates
1368 that no change to the schema name should be made in the new
1369 :class:`_schema.Table`. If set to a string name, the new
1370 :class:`_schema.Table`
1371 will have this new name as the ``.schema``. If set to ``None``, the
1372 schema will be set to that of the schema set on the target
1373 :class:`_schema.MetaData`, which is typically ``None`` as well,
1374 unless
1375 set explicitly::
1377 m2 = MetaData(schema="newschema")
1379 # user_copy_one will have "newschema" as the schema name
1380 user_copy_one = user.to_metadata(m2, schema=None)
1382 m3 = MetaData() # schema defaults to None
1384 # user_copy_two will have None as the schema name
1385 user_copy_two = user.to_metadata(m3, schema=None)
1387 :param referred_schema_fn: optional callable which can be supplied
1388 in order to provide for the schema name that should be assigned
1389 to the referenced table of a :class:`_schema.ForeignKeyConstraint`.
1390 The callable accepts this parent :class:`_schema.Table`, the
1391 target schema that we are changing to, the
1392 :class:`_schema.ForeignKeyConstraint` object, and the existing
1393 "target schema" of that constraint. The function should return the
1394 string schema name that should be applied. To reset the schema
1395 to "none", return the symbol :data:`.BLANK_SCHEMA`. To effect no
1396 change, return ``None`` or :data:`.RETAIN_SCHEMA`.
1398 .. versionchanged:: 1.4.33 The ``referred_schema_fn`` function
1399 may return the :data:`.BLANK_SCHEMA` or :data:`.RETAIN_SCHEMA`
1400 symbols.
1402 E.g.::
1404 def referred_schema_fn(table, to_schema, constraint, referred_schema):
1405 if referred_schema == "base_tables":
1406 return referred_schema
1407 else:
1408 return to_schema
1411 new_table = table.to_metadata(
1412 m2, schema="alt_schema", referred_schema_fn=referred_schema_fn
1413 )
1415 :param name: optional string name indicating the target table name.
1416 If not specified or None, the table name is retained. This allows
1417 a :class:`_schema.Table` to be copied to the same
1418 :class:`_schema.MetaData` target
1419 with a new name.
1421 """ # noqa: E501
1422 if name is None:
1423 name = self.name
1425 actual_schema: Optional[str]
1427 if schema is RETAIN_SCHEMA:
1428 actual_schema = self.schema
1429 elif schema is None:
1430 actual_schema = metadata.schema
1431 else:
1432 actual_schema = schema
1433 key = _get_table_key(name, actual_schema)
1434 if key in metadata.tables:
1435 util.warn(
1436 f"Table '{self.description}' already exists within the given "
1437 "MetaData - not copying."
1438 )
1439 return metadata.tables[key]
1441 args = []
1442 for col in self.columns:
1443 args.append(col._copy(schema=actual_schema, _to_metadata=metadata))
1444 table = Table(
1445 name,
1446 metadata,
1447 schema=actual_schema,
1448 comment=self.comment,
1449 *args,
1450 **self.kwargs,
1451 )
1452 for const in self.constraints:
1453 if isinstance(const, ForeignKeyConstraint):
1454 referred_schema = const._referred_schema
1455 if referred_schema_fn:
1456 fk_constraint_schema = referred_schema_fn(
1457 self, actual_schema, const, referred_schema
1458 )
1459 else:
1460 fk_constraint_schema = (
1461 actual_schema
1462 if referred_schema == self.schema
1463 else None
1464 )
1465 table.append_constraint(
1466 const._copy(
1467 schema=fk_constraint_schema, target_table=table
1468 )
1469 )
1470 elif not const._type_bound:
1471 # skip unique constraints that would be generated
1472 # by the 'unique' flag on Column
1473 if const._column_flag:
1474 continue
1476 table.append_constraint(
1477 const._copy(schema=actual_schema, target_table=table)
1478 )
1479 for index in self.indexes:
1480 # skip indexes that would be generated
1481 # by the 'index' flag on Column
1482 if index._column_flag:
1483 continue
1484 Index(
1485 index.name,
1486 unique=index.unique,
1487 *[
1488 _copy_expression(expr, self, table)
1489 for expr in index._table_bound_expressions
1490 ],
1491 _table=table,
1492 **index.kwargs,
1493 )
1494 return self._schema_item_copy(table)
1497class Column(DialectKWArgs, SchemaItem, ColumnClause[_T]):
1498 """Represents a column in a database table."""
1500 __visit_name__ = "column"
1502 inherit_cache = True
1503 key: str
1505 server_default: Optional[FetchedValue]
1507 def __init__(
1508 self,
1509 __name_pos: Optional[
1510 Union[str, _TypeEngineArgument[_T], SchemaEventTarget]
1511 ] = None,
1512 __type_pos: Optional[
1513 Union[_TypeEngineArgument[_T], SchemaEventTarget]
1514 ] = None,
1515 *args: SchemaEventTarget,
1516 name: Optional[str] = None,
1517 type_: Optional[_TypeEngineArgument[_T]] = None,
1518 autoincrement: _AutoIncrementType = "auto",
1519 default: Optional[Any] = _NoArg.NO_ARG,
1520 insert_default: Optional[Any] = _NoArg.NO_ARG,
1521 doc: Optional[str] = None,
1522 key: Optional[str] = None,
1523 index: Optional[bool] = None,
1524 unique: Optional[bool] = None,
1525 info: Optional[_InfoType] = None,
1526 nullable: Optional[
1527 Union[bool, Literal[SchemaConst.NULL_UNSPECIFIED]]
1528 ] = SchemaConst.NULL_UNSPECIFIED,
1529 onupdate: Optional[Any] = None,
1530 primary_key: bool = False,
1531 server_default: Optional[_ServerDefaultArgument] = None,
1532 server_onupdate: Optional[_ServerOnUpdateArgument] = None,
1533 quote: Optional[bool] = None,
1534 system: bool = False,
1535 comment: Optional[str] = None,
1536 insert_sentinel: bool = False,
1537 _omit_from_statements: bool = False,
1538 _proxies: Optional[Any] = None,
1539 **dialect_kwargs: Any,
1540 ):
1541 r"""
1542 Construct a new ``Column`` object.
1544 :param name: The name of this column as represented in the database.
1545 This argument may be the first positional argument, or specified
1546 via keyword.
1548 Names which contain no upper case characters
1549 will be treated as case insensitive names, and will not be quoted
1550 unless they are a reserved word. Names with any number of upper
1551 case characters will be quoted and sent exactly. Note that this
1552 behavior applies even for databases which standardize upper
1553 case names as case insensitive such as Oracle Database.
1555 The name field may be omitted at construction time and applied
1556 later, at any time before the Column is associated with a
1557 :class:`_schema.Table`. This is to support convenient
1558 usage within the :mod:`~sqlalchemy.ext.declarative` extension.
1560 :param type\_: The column's type, indicated using an instance which
1561 subclasses :class:`~sqlalchemy.types.TypeEngine`. If no arguments
1562 are required for the type, the class of the type can be sent
1563 as well, e.g.::
1565 # use a type with arguments
1566 Column("data", String(50))
1568 # use no arguments
1569 Column("level", Integer)
1571 The ``type`` argument may be the second positional argument
1572 or specified by keyword.
1574 If the ``type`` is ``None`` or is omitted, it will first default to
1575 the special type :class:`.NullType`. If and when this
1576 :class:`_schema.Column` is made to refer to another column using
1577 :class:`_schema.ForeignKey` and/or
1578 :class:`_schema.ForeignKeyConstraint`, the type
1579 of the remote-referenced column will be copied to this column as
1580 well, at the moment that the foreign key is resolved against that
1581 remote :class:`_schema.Column` object.
1583 :param \*args: Additional positional arguments include various
1584 :class:`.SchemaItem` derived constructs which will be applied
1585 as options to the column. These include instances of
1586 :class:`.Constraint`, :class:`_schema.ForeignKey`,
1587 :class:`.ColumnDefault`, :class:`.Sequence`, :class:`.Computed`
1588 :class:`.Identity`. In some cases an
1589 equivalent keyword argument is available such as ``server_default``,
1590 ``default`` and ``unique``.
1592 :param autoincrement: Set up "auto increment" semantics for an
1593 **integer primary key column with no foreign key dependencies**
1594 (see later in this docstring for a more specific definition).
1595 This may influence the :term:`DDL` that will be emitted for
1596 this column during a table create, as well as how the column
1597 will be considered when INSERT statements are compiled and
1598 executed.
1600 The default value is the string ``"auto"``,
1601 which indicates that a single-column (i.e. non-composite) primary key
1602 that is of an INTEGER type with no other client-side or server-side
1603 default constructs indicated should receive auto increment semantics
1604 automatically. Other values include ``True`` (force this column to
1605 have auto-increment semantics for a :term:`composite primary key` as
1606 well), ``False`` (this column should never have auto-increment
1607 semantics), and the string ``"ignore_fk"`` (special-case for foreign
1608 key columns, see below).
1610 The term "auto increment semantics" refers both to the kind of DDL
1611 that will be emitted for the column within a CREATE TABLE statement,
1612 when methods such as :meth:`.MetaData.create_all` and
1613 :meth:`.Table.create` are invoked, as well as how the column will be
1614 considered when an INSERT statement is compiled and emitted to the
1615 database:
1617 * **DDL rendering** (i.e. :meth:`.MetaData.create_all`,
1618 :meth:`.Table.create`): When used on a :class:`.Column` that has
1619 no other
1620 default-generating construct associated with it (such as a
1621 :class:`.Sequence` or :class:`.Identity` construct), the parameter
1622 will imply that database-specific keywords such as PostgreSQL
1623 ``SERIAL``, MySQL ``AUTO_INCREMENT``, or ``IDENTITY`` on SQL Server
1624 should also be rendered. Not every database backend has an
1625 "implied" default generator available; for example the Oracle Database
1626 backends always needs an explicit construct such as
1627 :class:`.Identity` to be included with a :class:`.Column` in order
1628 for the DDL rendered to include auto-generating constructs to also
1629 be produced in the database.
1631 * **INSERT semantics** (i.e. when a :func:`_sql.insert` construct is
1632 compiled into a SQL string and is then executed on a database using
1633 :meth:`_engine.Connection.execute` or equivalent): A single-row
1634 INSERT statement will be known to produce a new integer primary key
1635 value automatically for this column, which will be accessible
1636 after the statement is invoked via the
1637 :attr:`.CursorResult.inserted_primary_key` attribute upon the
1638 :class:`_result.Result` object. This also applies towards use of the
1639 ORM when ORM-mapped objects are persisted to the database,
1640 indicating that a new integer primary key will be available to
1641 become part of the :term:`identity key` for that object. This
1642 behavior takes place regardless of what DDL constructs are
1643 associated with the :class:`_schema.Column` and is independent
1644 of the "DDL Rendering" behavior discussed in the previous note
1645 above.
1647 The parameter may be set to ``True`` to indicate that a column which
1648 is part of a composite (i.e. multi-column) primary key should
1649 have autoincrement semantics, though note that only one column
1650 within a primary key may have this setting. It can also
1651 be set to ``True`` to indicate autoincrement semantics on a
1652 column that has a client-side or server-side default configured,
1653 however note that not all dialects can accommodate all styles
1654 of default as an "autoincrement". It can also be
1655 set to ``False`` on a single-column primary key that has a
1656 datatype of INTEGER in order to disable auto increment semantics
1657 for that column.
1659 The setting *only* has an effect for columns which are:
1661 * Integer derived (i.e. INT, SMALLINT, BIGINT).
1663 * Part of the primary key
1665 * Not referring to another column via :class:`_schema.ForeignKey`,
1666 unless
1667 the value is specified as ``'ignore_fk'``::
1669 # turn on autoincrement for this column despite
1670 # the ForeignKey()
1671 Column(
1672 "id",
1673 ForeignKey("other.id"),
1674 primary_key=True,
1675 autoincrement="ignore_fk",
1676 )
1678 It is typically not desirable to have "autoincrement" enabled on a
1679 column that refers to another via foreign key, as such a column is
1680 required to refer to a value that originates from elsewhere.
1682 The setting has these effects on columns that meet the
1683 above criteria:
1685 * DDL issued for the column, if the column does not already include
1686 a default generating construct supported by the backend such as
1687 :class:`.Identity`, will include database-specific
1688 keywords intended to signify this column as an
1689 "autoincrement" column for specific backends. Behavior for
1690 primary SQLAlchemy dialects includes:
1692 * AUTO INCREMENT on MySQL and MariaDB
1693 * SERIAL on PostgreSQL
1694 * IDENTITY on MS-SQL - this occurs even without the
1695 :class:`.Identity` construct as the
1696 :paramref:`.Column.autoincrement` parameter pre-dates this
1697 construct.
1698 * SQLite - SQLite integer primary key columns are implicitly
1699 "auto incrementing" and no additional keywords are rendered;
1700 to render the special SQLite keyword ``AUTOINCREMENT``
1701 is not included as this is unnecessary and not recommended
1702 by the database vendor. See the section
1703 :ref:`sqlite_autoincrement` for more background.
1704 * Oracle Database - The Oracle Database dialects have no default "autoincrement"
1705 feature available at this time, instead the :class:`.Identity`
1706 construct is recommended to achieve this (the :class:`.Sequence`
1707 construct may also be used).
1708 * Third-party dialects - consult those dialects' documentation
1709 for details on their specific behaviors.
1711 * When a single-row :func:`_sql.insert` construct is compiled and
1712 executed, which does not set the :meth:`_sql.Insert.inline`
1713 modifier, newly generated primary key values for this column
1714 will be automatically retrieved upon statement execution
1715 using a method specific to the database driver in use:
1717 * MySQL, SQLite - calling upon ``cursor.lastrowid()``
1718 (see
1719 `https://www.python.org/dev/peps/pep-0249/#lastrowid
1720 <https://www.python.org/dev/peps/pep-0249/#lastrowid>`_)
1721 * PostgreSQL, SQL Server, Oracle Database - use RETURNING or an equivalent
1722 construct when rendering an INSERT statement, and then retrieving
1723 the newly generated primary key values after execution
1724 * PostgreSQL, Oracle Database for :class:`_schema.Table` objects that
1725 set :paramref:`_schema.Table.implicit_returning` to False -
1726 for a :class:`.Sequence` only, the :class:`.Sequence` is invoked
1727 explicitly before the INSERT statement takes place so that the
1728 newly generated primary key value is available to the client
1729 * SQL Server for :class:`_schema.Table` objects that
1730 set :paramref:`_schema.Table.implicit_returning` to False -
1731 the ``SELECT scope_identity()`` construct is used after the
1732 INSERT statement is invoked to retrieve the newly generated
1733 primary key value.
1734 * Third-party dialects - consult those dialects' documentation
1735 for details on their specific behaviors.
1737 * For multiple-row :func:`_sql.insert` constructs invoked with
1738 a list of parameters (i.e. "executemany" semantics), primary-key
1739 retrieving behaviors are generally disabled, however there may
1740 be special APIs that may be used to retrieve lists of new
1741 primary key values for an "executemany", such as the psycopg2
1742 "fast insertmany" feature. Such features are very new and
1743 may not yet be well covered in documentation.
1745 :param default: A scalar, Python callable, or
1746 :class:`_expression.ColumnElement` expression representing the
1747 *default value* for this column, which will be invoked upon insert
1748 if this column is otherwise not specified in the VALUES clause of
1749 the insert. This is a shortcut to using :class:`.ColumnDefault` as
1750 a positional argument; see that class for full detail on the
1751 structure of the argument.
1753 Contrast this argument to
1754 :paramref:`_schema.Column.server_default`
1755 which creates a default generator on the database side.
1757 .. seealso::
1759 :ref:`metadata_defaults_toplevel`
1761 :param insert_default: An alias of :paramref:`.Column.default`
1762 for compatibility with :func:`_orm.mapped_column`.
1764 .. versionadded: 2.0.31
1766 :param doc: optional String that can be used by the ORM or similar
1767 to document attributes on the Python side. This attribute does
1768 **not** render SQL comments; use the
1769 :paramref:`_schema.Column.comment`
1770 parameter for this purpose.
1772 :param key: An optional string identifier which will identify this
1773 ``Column`` object on the :class:`_schema.Table`.
1774 When a key is provided,
1775 this is the only identifier referencing the ``Column`` within the
1776 application, including ORM attribute mapping; the ``name`` field
1777 is used only when rendering SQL.
1779 :param index: When ``True``, indicates that a :class:`_schema.Index`
1780 construct will be automatically generated for this
1781 :class:`_schema.Column`, which will result in a "CREATE INDEX"
1782 statement being emitted for the :class:`_schema.Table` when the DDL
1783 create operation is invoked.
1785 Using this flag is equivalent to making use of the
1786 :class:`_schema.Index` construct explicitly at the level of the
1787 :class:`_schema.Table` construct itself::
1789 Table(
1790 "some_table",
1791 metadata,
1792 Column("x", Integer),
1793 Index("ix_some_table_x", "x"),
1794 )
1796 To add the :paramref:`_schema.Index.unique` flag to the
1797 :class:`_schema.Index`, set both the
1798 :paramref:`_schema.Column.unique` and
1799 :paramref:`_schema.Column.index` flags to True simultaneously,
1800 which will have the effect of rendering the "CREATE UNIQUE INDEX"
1801 DDL instruction instead of "CREATE INDEX".
1803 The name of the index is generated using the
1804 :ref:`default naming convention <constraint_default_naming_convention>`
1805 which for the :class:`_schema.Index` construct is of the form
1806 ``ix_<tablename>_<columnname>``.
1808 As this flag is intended only as a convenience for the common case
1809 of adding a single-column, default configured index to a table
1810 definition, explicit use of the :class:`_schema.Index` construct
1811 should be preferred for most use cases, including composite indexes
1812 that encompass more than one column, indexes with SQL expressions
1813 or ordering, backend-specific index configuration options, and
1814 indexes that use a specific name.
1816 .. note:: the :attr:`_schema.Column.index` attribute on
1817 :class:`_schema.Column`
1818 **does not indicate** if this column is indexed or not, only
1819 if this flag was explicitly set here. To view indexes on
1820 a column, view the :attr:`_schema.Table.indexes` collection
1821 or use :meth:`_reflection.Inspector.get_indexes`.
1823 .. seealso::
1825 :ref:`schema_indexes`
1827 :ref:`constraint_naming_conventions`
1829 :paramref:`_schema.Column.unique`
1831 :param info: Optional data dictionary which will be populated into the
1832 :attr:`.SchemaItem.info` attribute of this object.
1834 :param nullable: When set to ``False``, will cause the "NOT NULL"
1835 phrase to be added when generating DDL for the column. When
1836 ``True``, will normally generate nothing (in SQL this defaults to
1837 "NULL"), except in some very specific backend-specific edge cases
1838 where "NULL" may render explicitly.
1839 Defaults to ``True`` unless :paramref:`_schema.Column.primary_key`
1840 is also ``True`` or the column specifies a :class:`_sql.Identity`,
1841 in which case it defaults to ``False``.
1842 This parameter is only used when issuing CREATE TABLE statements.
1844 .. note::
1846 When the column specifies a :class:`_sql.Identity` this
1847 parameter is in general ignored by the DDL compiler. The
1848 PostgreSQL database allows nullable identity column by
1849 setting this parameter to ``True`` explicitly.
1851 :param onupdate: A scalar, Python callable, or
1852 :class:`~sqlalchemy.sql.expression.ClauseElement` representing a
1853 default value to be applied to the column within UPDATE
1854 statements, which will be invoked upon update if this column is not
1855 present in the SET clause of the update. This is a shortcut to
1856 using :class:`.ColumnDefault` as a positional argument with
1857 ``for_update=True``.
1859 .. seealso::
1861 :ref:`metadata_defaults` - complete discussion of onupdate
1863 :param primary_key: If ``True``, marks this column as a primary key
1864 column. Multiple columns can have this flag set to specify
1865 composite primary keys. As an alternative, the primary key of a
1866 :class:`_schema.Table` can be specified via an explicit
1867 :class:`.PrimaryKeyConstraint` object.
1869 :param server_default: A :class:`.FetchedValue` instance, str, Unicode
1870 or :func:`~sqlalchemy.sql.expression.text` construct representing
1871 the DDL DEFAULT value for the column.
1873 String types will be emitted as-is, surrounded by single quotes::
1875 Column("x", Text, server_default="val")
1877 will render:
1879 .. sourcecode:: sql
1881 x TEXT DEFAULT 'val'
1883 A :func:`~sqlalchemy.sql.expression.text` expression will be
1884 rendered as-is, without quotes::
1886 Column("y", DateTime, server_default=text("NOW()"))
1888 will render:
1890 .. sourcecode:: sql
1892 y DATETIME DEFAULT NOW()
1894 Strings and text() will be converted into a
1895 :class:`.DefaultClause` object upon initialization.
1897 This parameter can also accept complex combinations of contextually
1898 valid SQLAlchemy expressions or constructs::
1900 from sqlalchemy import create_engine
1901 from sqlalchemy import Table, Column, MetaData, ARRAY, Text
1902 from sqlalchemy.dialects.postgresql import array
1904 engine = create_engine(
1905 "postgresql+psycopg2://scott:tiger@localhost/mydatabase"
1906 )
1907 metadata_obj = MetaData()
1908 tbl = Table(
1909 "foo",
1910 metadata_obj,
1911 Column(
1912 "bar", ARRAY(Text), server_default=array(["biz", "bang", "bash"])
1913 ),
1914 )
1915 metadata_obj.create_all(engine)
1917 The above results in a table created with the following SQL:
1919 .. sourcecode:: sql
1921 CREATE TABLE foo (
1922 bar TEXT[] DEFAULT ARRAY['biz', 'bang', 'bash']
1923 )
1925 Use :class:`.FetchedValue` to indicate that an already-existing
1926 column will generate a default value on the database side which
1927 will be available to SQLAlchemy for post-fetch after inserts. This
1928 construct does not specify any DDL and the implementation is left
1929 to the database, such as via a trigger.
1931 .. seealso::
1933 :ref:`server_defaults` - complete discussion of server side
1934 defaults
1936 :param server_onupdate: A :class:`.FetchedValue` instance
1937 representing a database-side default generation function,
1938 such as a trigger. This
1939 indicates to SQLAlchemy that a newly generated value will be
1940 available after updates. This construct does not actually
1941 implement any kind of generation function within the database,
1942 which instead must be specified separately.
1945 .. warning:: This directive **does not** currently produce MySQL's
1946 "ON UPDATE CURRENT_TIMESTAMP()" clause. See
1947 :ref:`mysql_timestamp_onupdate` for background on how to
1948 produce this clause.
1950 .. seealso::
1952 :ref:`triggered_columns`
1954 :param quote: Force quoting of this column's name on or off,
1955 corresponding to ``True`` or ``False``. When left at its default
1956 of ``None``, the column identifier will be quoted according to
1957 whether the name is case sensitive (identifiers with at least one
1958 upper case character are treated as case sensitive), or if it's a
1959 reserved word. This flag is only needed to force quoting of a
1960 reserved word which is not known by the SQLAlchemy dialect.
1962 :param unique: When ``True``, and the :paramref:`_schema.Column.index`
1963 parameter is left at its default value of ``False``,
1964 indicates that a :class:`_schema.UniqueConstraint`
1965 construct will be automatically generated for this
1966 :class:`_schema.Column`,
1967 which will result in a "UNIQUE CONSTRAINT" clause referring
1968 to this column being included
1969 in the ``CREATE TABLE`` statement emitted, when the DDL create
1970 operation for the :class:`_schema.Table` object is invoked.
1972 When this flag is ``True`` while the
1973 :paramref:`_schema.Column.index` parameter is simultaneously
1974 set to ``True``, the effect instead is that a
1975 :class:`_schema.Index` construct which includes the
1976 :paramref:`_schema.Index.unique` parameter set to ``True``
1977 is generated. See the documentation for
1978 :paramref:`_schema.Column.index` for additional detail.
1980 Using this flag is equivalent to making use of the
1981 :class:`_schema.UniqueConstraint` construct explicitly at the
1982 level of the :class:`_schema.Table` construct itself::
1984 Table("some_table", metadata, Column("x", Integer), UniqueConstraint("x"))
1986 The :paramref:`_schema.UniqueConstraint.name` parameter
1987 of the unique constraint object is left at its default value
1988 of ``None``; in the absence of a :ref:`naming convention <constraint_naming_conventions>`
1989 for the enclosing :class:`_schema.MetaData`, the UNIQUE CONSTRAINT
1990 construct will be emitted as unnamed, which typically invokes
1991 a database-specific naming convention to take place.
1993 As this flag is intended only as a convenience for the common case
1994 of adding a single-column, default configured unique constraint to a table
1995 definition, explicit use of the :class:`_schema.UniqueConstraint` construct
1996 should be preferred for most use cases, including composite constraints
1997 that encompass more than one column, backend-specific index configuration options, and
1998 constraints that use a specific name.
2000 .. note:: the :attr:`_schema.Column.unique` attribute on
2001 :class:`_schema.Column`
2002 **does not indicate** if this column has a unique constraint or
2003 not, only if this flag was explicitly set here. To view
2004 indexes and unique constraints that may involve this column,
2005 view the
2006 :attr:`_schema.Table.indexes` and/or
2007 :attr:`_schema.Table.constraints` collections or use
2008 :meth:`_reflection.Inspector.get_indexes` and/or
2009 :meth:`_reflection.Inspector.get_unique_constraints`
2011 .. seealso::
2013 :ref:`schema_unique_constraint`
2015 :ref:`constraint_naming_conventions`
2017 :paramref:`_schema.Column.index`
2019 :param system: When ``True``, indicates this is a "system" column,
2020 that is a column which is automatically made available by the
2021 database, and should not be included in the columns list for a
2022 ``CREATE TABLE`` statement.
2024 For more elaborate scenarios where columns should be
2025 conditionally rendered differently on different backends,
2026 consider custom compilation rules for :class:`.CreateColumn`.
2028 :param comment: Optional string that will render an SQL comment on
2029 table creation.
2031 .. versionadded:: 1.2 Added the
2032 :paramref:`_schema.Column.comment`
2033 parameter to :class:`_schema.Column`.
2035 :param insert_sentinel: Marks this :class:`_schema.Column` as an
2036 :term:`insert sentinel` used for optimizing the performance of the
2037 :term:`insertmanyvalues` feature for tables that don't
2038 otherwise have qualifying primary key configurations.
2040 .. versionadded:: 2.0.10
2042 .. seealso::
2044 :func:`_schema.insert_sentinel` - all in one helper for declaring
2045 sentinel columns
2047 :ref:`engine_insertmanyvalues`
2049 :ref:`engine_insertmanyvalues_sentinel_columns`
2052 """ # noqa: E501, RST201, RST202
2054 l_args = [__name_pos, __type_pos] + list(args)
2055 del args
2057 if l_args:
2058 if isinstance(l_args[0], str):
2059 if name is not None:
2060 raise exc.ArgumentError(
2061 "May not pass name positionally and as a keyword."
2062 )
2063 name = l_args.pop(0) # type: ignore
2064 elif l_args[0] is None:
2065 l_args.pop(0)
2066 if l_args:
2067 coltype = l_args[0]
2069 if hasattr(coltype, "_sqla_type"):
2070 if type_ is not None:
2071 raise exc.ArgumentError(
2072 "May not pass type_ positionally and as a keyword."
2073 )
2074 type_ = l_args.pop(0) # type: ignore
2075 elif l_args[0] is None:
2076 l_args.pop(0)
2078 if name is not None:
2079 name = quoted_name(name, quote)
2080 elif quote is not None:
2081 raise exc.ArgumentError(
2082 "Explicit 'name' is required when sending 'quote' argument"
2083 )
2085 # name = None is expected to be an interim state
2086 # note this use case is legacy now that ORM declarative has a
2087 # dedicated "column" construct local to the ORM
2088 super().__init__(name, type_) # type: ignore
2090 self.key = key if key is not None else name # type: ignore
2091 self.primary_key = primary_key
2092 self._insert_sentinel = insert_sentinel
2093 self._omit_from_statements = _omit_from_statements
2094 self._user_defined_nullable = udn = nullable
2095 if udn is not NULL_UNSPECIFIED:
2096 self.nullable = udn
2097 else:
2098 self.nullable = not primary_key
2100 # these default to None because .index and .unique is *not*
2101 # an informational flag about Column - there can still be an
2102 # Index or UniqueConstraint referring to this Column.
2103 self.index = index
2104 self.unique = unique
2106 self.system = system
2107 self.doc = doc
2108 self.autoincrement: _AutoIncrementType = autoincrement
2109 self.constraints = set()
2110 self.foreign_keys = set()
2111 self.comment = comment
2112 self.computed = None
2113 self.identity = None
2115 # check if this Column is proxying another column
2117 if _proxies is not None:
2118 self._proxies = _proxies
2119 else:
2120 # otherwise, add DDL-related events
2121 self._set_type(self.type)
2123 if insert_default is not _NoArg.NO_ARG:
2124 resolved_default = insert_default
2125 elif default is not _NoArg.NO_ARG:
2126 resolved_default = default
2127 else:
2128 resolved_default = None
2130 if resolved_default is not None:
2131 if not isinstance(resolved_default, (ColumnDefault, Sequence)):
2132 resolved_default = ColumnDefault(resolved_default)
2134 self.default = resolved_default
2135 l_args.append(resolved_default)
2136 else:
2137 self.default = None
2139 if onupdate is not None:
2140 if not isinstance(onupdate, (ColumnDefault, Sequence)):
2141 onupdate = ColumnDefault(onupdate, for_update=True)
2143 self.onupdate = onupdate
2144 l_args.append(onupdate)
2145 else:
2146 self.onupdate = None
2148 if server_default is not None:
2149 if isinstance(server_default, FetchedValue):
2150 server_default = server_default._as_for_update(False)
2151 l_args.append(server_default)
2152 else:
2153 server_default = DefaultClause(server_default)
2154 l_args.append(server_default)
2155 self.server_default = server_default
2157 if server_onupdate is not None:
2158 if isinstance(server_onupdate, FetchedValue):
2159 server_onupdate = server_onupdate._as_for_update(True)
2160 l_args.append(server_onupdate)
2161 else:
2162 server_onupdate = DefaultClause(
2163 server_onupdate, for_update=True
2164 )
2165 l_args.append(server_onupdate)
2166 self.server_onupdate = server_onupdate
2168 self._init_items(*cast(_typing_Sequence[SchemaItem], l_args))
2170 util.set_creation_order(self)
2172 if info is not None:
2173 self.info = info
2175 self._extra_kwargs(**dialect_kwargs)
2177 table: Table
2179 constraints: Set[Constraint]
2181 foreign_keys: Set[ForeignKey]
2182 """A collection of all :class:`_schema.ForeignKey` marker objects
2183 associated with this :class:`_schema.Column`.
2185 Each object is a member of a :class:`_schema.Table`-wide
2186 :class:`_schema.ForeignKeyConstraint`.
2188 .. seealso::
2190 :attr:`_schema.Table.foreign_keys`
2192 """
2194 index: Optional[bool]
2195 """The value of the :paramref:`_schema.Column.index` parameter.
2197 Does not indicate if this :class:`_schema.Column` is actually indexed
2198 or not; use :attr:`_schema.Table.indexes`.
2200 .. seealso::
2202 :attr:`_schema.Table.indexes`
2203 """
2205 unique: Optional[bool]
2206 """The value of the :paramref:`_schema.Column.unique` parameter.
2208 Does not indicate if this :class:`_schema.Column` is actually subject to
2209 a unique constraint or not; use :attr:`_schema.Table.indexes` and
2210 :attr:`_schema.Table.constraints`.
2212 .. seealso::
2214 :attr:`_schema.Table.indexes`
2216 :attr:`_schema.Table.constraints`.
2218 """
2220 computed: Optional[Computed]
2222 identity: Optional[Identity]
2224 def _set_type(self, type_: TypeEngine[Any]) -> None:
2225 assert self.type._isnull or type_ is self.type
2227 self.type = type_
2228 if isinstance(self.type, SchemaEventTarget):
2229 self.type._set_parent_with_dispatch(self)
2230 for impl in self.type._variant_mapping.values():
2231 if isinstance(impl, SchemaEventTarget):
2232 impl._set_parent_with_dispatch(self)
2234 @HasMemoized.memoized_attribute
2235 def _default_description_tuple(self) -> _DefaultDescriptionTuple:
2236 """used by default.py -> _process_execute_defaults()"""
2238 return _DefaultDescriptionTuple._from_column_default(self.default)
2240 @HasMemoized.memoized_attribute
2241 def _onupdate_description_tuple(self) -> _DefaultDescriptionTuple:
2242 """used by default.py -> _process_execute_defaults()"""
2243 return _DefaultDescriptionTuple._from_column_default(self.onupdate)
2245 @util.memoized_property
2246 def _gen_static_annotations_cache_key(self) -> bool:
2247 """special attribute used by cache key gen, if true, we will
2248 use a static cache key for the annotations dictionary, else we
2249 will generate a new cache key for annotations each time.
2251 Added for #8790
2253 """
2254 return self.table is not None and self.table._is_table
2256 def _extra_kwargs(self, **kwargs: Any) -> None:
2257 self._validate_dialect_kwargs(kwargs)
2259 def __str__(self) -> str:
2260 if self.name is None:
2261 return "(no name)"
2262 elif self.table is not None:
2263 if self.table.named_with_column:
2264 return self.table.description + "." + self.description
2265 else:
2266 return self.description
2267 else:
2268 return self.description
2270 def references(self, column: Column[Any]) -> bool:
2271 """Return True if this Column references the given column via foreign
2272 key."""
2274 for fk in self.foreign_keys:
2275 if fk.column.proxy_set.intersection(column.proxy_set):
2276 return True
2277 else:
2278 return False
2280 def append_foreign_key(self, fk: ForeignKey) -> None:
2281 fk._set_parent_with_dispatch(self)
2283 def __repr__(self) -> str:
2284 kwarg = []
2285 if self.key != self.name:
2286 kwarg.append("key")
2287 if self.primary_key:
2288 kwarg.append("primary_key")
2289 if not self.nullable:
2290 kwarg.append("nullable")
2291 if self.onupdate:
2292 kwarg.append("onupdate")
2293 if self.default:
2294 kwarg.append("default")
2295 if self.server_default:
2296 kwarg.append("server_default")
2297 if self.comment:
2298 kwarg.append("comment")
2299 return "Column(%s)" % ", ".join(
2300 [repr(self.name)]
2301 + [repr(self.type)]
2302 + [repr(x) for x in self.foreign_keys if x is not None]
2303 + [repr(x) for x in self.constraints]
2304 + [
2305 (
2306 self.table is not None
2307 and "table=<%s>" % self.table.description
2308 or "table=None"
2309 )
2310 ]
2311 + ["%s=%s" % (k, repr(getattr(self, k))) for k in kwarg]
2312 )
2314 def _set_parent( # type: ignore[override]
2315 self,
2316 parent: SchemaEventTarget,
2317 *,
2318 all_names: Dict[str, Column[Any]],
2319 allow_replacements: bool,
2320 **kw: Any,
2321 ) -> None:
2322 table = parent
2323 assert isinstance(table, Table)
2324 if not self.name:
2325 raise exc.ArgumentError(
2326 "Column must be constructed with a non-blank name or "
2327 "assign a non-blank .name before adding to a Table."
2328 )
2330 self._reset_memoizations()
2332 if self.key is None:
2333 self.key = self.name
2335 existing = getattr(self, "table", None)
2336 if existing is not None and existing is not table:
2337 raise exc.ArgumentError(
2338 f"Column object '{self.key}' already "
2339 f"assigned to Table '{existing.description}'"
2340 )
2342 extra_remove = None
2343 existing_col = None
2344 conflicts_on = ""
2346 if self.key in table._columns:
2347 existing_col = table._columns[self.key]
2348 if self.key == self.name:
2349 conflicts_on = "name"
2350 else:
2351 conflicts_on = "key"
2352 elif self.name in all_names:
2353 existing_col = all_names[self.name]
2354 extra_remove = {existing_col}
2355 conflicts_on = "name"
2357 if existing_col is not None:
2358 if existing_col is not self:
2359 if not allow_replacements:
2360 raise exc.DuplicateColumnError(
2361 f"A column with {conflicts_on} " f"""'{
2362 self.key if conflicts_on == 'key' else self.name
2363 }' """ f"is already present in table '{table.name}'."
2364 )
2365 for fk in existing_col.foreign_keys:
2366 table.foreign_keys.remove(fk)
2367 if fk.constraint in table.constraints:
2368 # this might have been removed
2369 # already, if it's a composite constraint
2370 # and more than one col being replaced
2371 table.constraints.remove(fk.constraint)
2373 if extra_remove and existing_col is not None and self.key == self.name:
2374 util.warn(
2375 f'Column with user-specified key "{existing_col.key}" is '
2376 "being replaced with "
2377 f'plain named column "{self.name}", '
2378 f'key "{existing_col.key}" is being removed. If this is a '
2379 "reflection operation, specify autoload_replace=False to "
2380 "prevent this replacement."
2381 )
2382 table._columns.replace(self, extra_remove=extra_remove)
2383 all_names[self.name] = self
2384 self.table = table
2386 if self._insert_sentinel:
2387 if self.table._sentinel_column is not None:
2388 raise exc.ArgumentError(
2389 "a Table may have only one explicit sentinel column"
2390 )
2391 self.table._sentinel_column = self
2393 if self.primary_key:
2394 table.primary_key._replace(self)
2395 elif self.key in table.primary_key:
2396 raise exc.ArgumentError(
2397 f"Trying to redefine primary-key column '{self.key}' as a "
2398 f"non-primary-key column on table '{table.fullname}'"
2399 )
2401 if self.index:
2402 if isinstance(self.index, str):
2403 raise exc.ArgumentError(
2404 "The 'index' keyword argument on Column is boolean only. "
2405 "To create indexes with a specific name, create an "
2406 "explicit Index object external to the Table."
2407 )
2408 table.append_constraint(
2409 Index(
2410 None, self.key, unique=bool(self.unique), _column_flag=True
2411 )
2412 )
2414 elif self.unique:
2415 if isinstance(self.unique, str):
2416 raise exc.ArgumentError(
2417 "The 'unique' keyword argument on Column is boolean "
2418 "only. To create unique constraints or indexes with a "
2419 "specific name, append an explicit UniqueConstraint to "
2420 "the Table's list of elements, or create an explicit "
2421 "Index object external to the Table."
2422 )
2423 table.append_constraint(
2424 UniqueConstraint(self.key, _column_flag=True)
2425 )
2427 self._setup_on_memoized_fks(lambda fk: fk._set_remote_table(table))
2429 if self.identity and (
2430 isinstance(self.default, Sequence)
2431 or isinstance(self.onupdate, Sequence)
2432 ):
2433 raise exc.ArgumentError(
2434 "An column cannot specify both Identity and Sequence."
2435 )
2437 def _setup_on_memoized_fks(self, fn: Callable[..., Any]) -> None:
2438 fk_keys = [
2439 ((self.table.key, self.key), False),
2440 ((self.table.key, self.name), True),
2441 ]
2442 for fk_key, link_to_name in fk_keys:
2443 if fk_key in self.table.metadata._fk_memos:
2444 for fk in self.table.metadata._fk_memos[fk_key]:
2445 if fk.link_to_name is link_to_name:
2446 fn(fk)
2448 def _on_table_attach(self, fn: Callable[..., Any]) -> None:
2449 if self.table is not None:
2450 fn(self, self.table)
2451 else:
2452 event.listen(self, "after_parent_attach", fn)
2454 @util.deprecated(
2455 "1.4",
2456 "The :meth:`_schema.Column.copy` method is deprecated "
2457 "and will be removed in a future release.",
2458 )
2459 def copy(self, **kw: Any) -> Column[Any]:
2460 return self._copy(**kw)
2462 def _copy(self, **kw: Any) -> Column[Any]:
2463 """Create a copy of this ``Column``, uninitialized.
2465 This is used in :meth:`_schema.Table.to_metadata` and by the ORM.
2467 """
2469 # Constraint objects plus non-constraint-bound ForeignKey objects
2470 args: List[SchemaItem] = [
2471 c._copy(**kw) for c in self.constraints if not c._type_bound
2472 ] + [c._copy(**kw) for c in self.foreign_keys if not c.constraint]
2474 # ticket #5276
2475 column_kwargs = {}
2476 for dialect_name in self.dialect_options:
2477 dialect_options = self.dialect_options[dialect_name]._non_defaults
2478 for (
2479 dialect_option_key,
2480 dialect_option_value,
2481 ) in dialect_options.items():
2482 column_kwargs[dialect_name + "_" + dialect_option_key] = (
2483 dialect_option_value
2484 )
2486 default = self.default
2487 if default is not None:
2488 default = default._copy()
2489 onupdate = self.onupdate
2490 if onupdate is not None:
2491 onupdate = onupdate._copy()
2492 server_default = self.server_default
2493 server_onupdate = self.server_onupdate
2494 if isinstance(server_default, (Computed, Identity)):
2495 args.append(server_default._copy(**kw))
2496 server_default = server_onupdate = None
2497 else:
2498 if server_default is not None:
2499 server_default = server_default._copy()
2500 if server_onupdate is not None:
2501 server_onupdate = server_onupdate._copy()
2503 type_ = self.type
2504 if isinstance(type_, SchemaEventTarget):
2505 type_ = type_.copy(**kw)
2507 c = self._constructor(
2508 name=self.name,
2509 type_=type_,
2510 key=self.key,
2511 primary_key=self.primary_key,
2512 unique=self.unique,
2513 system=self.system,
2514 # quote=self.quote, # disabled 2013-08-27 (commit 031ef080)
2515 index=self.index,
2516 autoincrement=self.autoincrement,
2517 default=default,
2518 server_default=server_default,
2519 onupdate=onupdate,
2520 server_onupdate=server_onupdate,
2521 doc=self.doc,
2522 comment=self.comment,
2523 _omit_from_statements=self._omit_from_statements,
2524 insert_sentinel=self._insert_sentinel,
2525 *args,
2526 **column_kwargs,
2527 )
2529 # copy the state of "nullable" exactly, to accommodate for
2530 # ORM flipping the .nullable flag directly
2531 c.nullable = self.nullable
2532 c._user_defined_nullable = self._user_defined_nullable
2534 return self._schema_item_copy(c)
2536 def _merge(self, other: Column[Any]) -> None:
2537 """merge the elements of another column into this one.
2539 this is used by ORM pep-593 merge and will likely need a lot
2540 of fixes.
2543 """
2545 if self.primary_key:
2546 other.primary_key = True
2548 if self.autoincrement != "auto" and other.autoincrement == "auto":
2549 other.autoincrement = self.autoincrement
2551 if self.system:
2552 other.system = self.system
2554 if self.info:
2555 other.info.update(self.info)
2557 type_ = self.type
2558 if not type_._isnull and other.type._isnull:
2559 if isinstance(type_, SchemaEventTarget):
2560 type_ = type_.copy()
2562 other.type = type_
2564 if isinstance(type_, SchemaEventTarget):
2565 type_._set_parent_with_dispatch(other)
2567 for impl in type_._variant_mapping.values():
2568 if isinstance(impl, SchemaEventTarget):
2569 impl._set_parent_with_dispatch(other)
2571 if (
2572 self._user_defined_nullable is not NULL_UNSPECIFIED
2573 and other._user_defined_nullable is NULL_UNSPECIFIED
2574 ):
2575 other.nullable = self.nullable
2576 other._user_defined_nullable = self._user_defined_nullable
2578 if self.default is not None and other.default is None:
2579 new_default = self.default._copy()
2580 new_default._set_parent(other)
2582 if self.server_default and other.server_default is None:
2583 new_server_default = self.server_default
2584 if isinstance(new_server_default, FetchedValue):
2585 new_server_default = new_server_default._copy()
2586 new_server_default._set_parent(other)
2587 else:
2588 other.server_default = new_server_default
2590 if self.server_onupdate and other.server_onupdate is None:
2591 new_server_onupdate = self.server_onupdate
2592 new_server_onupdate = new_server_onupdate._copy()
2593 new_server_onupdate._set_parent(other)
2595 if self.onupdate and other.onupdate is None:
2596 new_onupdate = self.onupdate._copy()
2597 new_onupdate._set_parent(other)
2599 if self.index in (True, False) and other.index is None:
2600 other.index = self.index
2602 if self.unique in (True, False) and other.unique is None:
2603 other.unique = self.unique
2605 if self.doc and other.doc is None:
2606 other.doc = self.doc
2608 if self.comment and other.comment is None:
2609 other.comment = self.comment
2611 for const in self.constraints:
2612 if not const._type_bound:
2613 new_const = const._copy()
2614 new_const._set_parent(other)
2616 for fk in self.foreign_keys:
2617 if not fk.constraint:
2618 new_fk = fk._copy()
2619 new_fk._set_parent(other)
2621 def _make_proxy(
2622 self,
2623 selectable: FromClause,
2624 primary_key: ColumnSet,
2625 foreign_keys: Set[KeyedColumnElement[Any]],
2626 name: Optional[str] = None,
2627 key: Optional[str] = None,
2628 name_is_truncatable: bool = False,
2629 compound_select_cols: Optional[
2630 _typing_Sequence[ColumnElement[Any]]
2631 ] = None,
2632 **kw: Any,
2633 ) -> Tuple[str, ColumnClause[_T]]:
2634 """Create a *proxy* for this column.
2636 This is a copy of this ``Column`` referenced by a different parent
2637 (such as an alias or select statement). The column should
2638 be used only in select scenarios, as its full DDL/default
2639 information is not transferred.
2641 """
2643 fk = [
2644 ForeignKey(
2645 col if col is not None else f._colspec,
2646 _unresolvable=col is None,
2647 _constraint=f.constraint,
2648 )
2649 for f, col in [
2650 (fk, fk._resolve_column(raiseerr=False))
2651 for fk in self.foreign_keys
2652 ]
2653 ]
2655 if name is None and self.name is None:
2656 raise exc.InvalidRequestError(
2657 "Cannot initialize a sub-selectable"
2658 " with this Column object until its 'name' has "
2659 "been assigned."
2660 )
2661 try:
2662 c = self._constructor(
2663 (
2664 coercions.expect(
2665 roles.TruncatedLabelRole, name if name else self.name
2666 )
2667 if name_is_truncatable
2668 else (name or self.name)
2669 ),
2670 self.type,
2671 # this may actually be ._proxy_key when the key is incoming
2672 key=key if key else name if name else self.key,
2673 primary_key=self.primary_key,
2674 nullable=self.nullable,
2675 _proxies=(
2676 list(compound_select_cols)
2677 if compound_select_cols
2678 else [self]
2679 ),
2680 *fk,
2681 )
2682 except TypeError as err:
2683 raise TypeError(
2684 "Could not create a copy of this %r object. "
2685 "Ensure the class includes a _constructor() "
2686 "attribute or method which accepts the "
2687 "standard Column constructor arguments, or "
2688 "references the Column class itself." % self.__class__
2689 ) from err
2691 c.table = selectable
2692 c._propagate_attrs = selectable._propagate_attrs
2693 if selectable._is_clone_of is not None:
2694 c._is_clone_of = selectable._is_clone_of.columns.get(c.key)
2696 if self.primary_key:
2697 primary_key.add(c)
2699 if fk:
2700 foreign_keys.update(fk) # type: ignore
2702 return c.key, c
2705def insert_sentinel(
2706 name: Optional[str] = None,
2707 type_: Optional[_TypeEngineArgument[_T]] = None,
2708 *,
2709 default: Optional[Any] = None,
2710 omit_from_statements: bool = True,
2711) -> Column[Any]:
2712 """Provides a surrogate :class:`_schema.Column` that will act as a
2713 dedicated insert :term:`sentinel` column, allowing efficient bulk
2714 inserts with deterministic RETURNING sorting for tables that
2715 don't otherwise have qualifying primary key configurations.
2717 Adding this column to a :class:`.Table` object requires that a
2718 corresponding database table actually has this column present, so if adding
2719 it to an existing model, existing database tables would need to be migrated
2720 (e.g. using ALTER TABLE or similar) to include this column.
2722 For background on how this object is used, see the section
2723 :ref:`engine_insertmanyvalues_sentinel_columns` as part of the
2724 section :ref:`engine_insertmanyvalues`.
2726 The :class:`_schema.Column` returned will be a nullable integer column by
2727 default and make use of a sentinel-specific default generator used only in
2728 "insertmanyvalues" operations.
2730 .. seealso::
2732 :func:`_orm.orm_insert_sentinel`
2734 :paramref:`_schema.Column.insert_sentinel`
2736 :ref:`engine_insertmanyvalues`
2738 :ref:`engine_insertmanyvalues_sentinel_columns`
2741 .. versionadded:: 2.0.10
2743 """
2744 return Column(
2745 name=name,
2746 type_=type_api.INTEGERTYPE if type_ is None else type_,
2747 default=(
2748 default if default is not None else _InsertSentinelColumnDefault()
2749 ),
2750 _omit_from_statements=omit_from_statements,
2751 insert_sentinel=True,
2752 )
2755class ForeignKey(DialectKWArgs, SchemaItem):
2756 """Defines a dependency between two columns.
2758 ``ForeignKey`` is specified as an argument to a :class:`_schema.Column`
2759 object,
2760 e.g.::
2762 t = Table(
2763 "remote_table",
2764 metadata,
2765 Column("remote_id", ForeignKey("main_table.id")),
2766 )
2768 Note that ``ForeignKey`` is only a marker object that defines
2769 a dependency between two columns. The actual constraint
2770 is in all cases represented by the :class:`_schema.ForeignKeyConstraint`
2771 object. This object will be generated automatically when
2772 a ``ForeignKey`` is associated with a :class:`_schema.Column` which
2773 in turn is associated with a :class:`_schema.Table`. Conversely,
2774 when :class:`_schema.ForeignKeyConstraint` is applied to a
2775 :class:`_schema.Table`,
2776 ``ForeignKey`` markers are automatically generated to be
2777 present on each associated :class:`_schema.Column`, which are also
2778 associated with the constraint object.
2780 Note that you cannot define a "composite" foreign key constraint,
2781 that is a constraint between a grouping of multiple parent/child
2782 columns, using ``ForeignKey`` objects. To define this grouping,
2783 the :class:`_schema.ForeignKeyConstraint` object must be used, and applied
2784 to the :class:`_schema.Table`. The associated ``ForeignKey`` objects
2785 are created automatically.
2787 The ``ForeignKey`` objects associated with an individual
2788 :class:`_schema.Column`
2789 object are available in the `foreign_keys` collection
2790 of that column.
2792 Further examples of foreign key configuration are in
2793 :ref:`metadata_foreignkeys`.
2795 """
2797 __visit_name__ = "foreign_key"
2799 parent: Column[Any]
2801 _table_column: Optional[Column[Any]]
2803 def __init__(
2804 self,
2805 column: _DDLColumnArgument,
2806 _constraint: Optional[ForeignKeyConstraint] = None,
2807 use_alter: bool = False,
2808 name: _ConstraintNameArgument = None,
2809 onupdate: Optional[str] = None,
2810 ondelete: Optional[str] = None,
2811 deferrable: Optional[bool] = None,
2812 initially: Optional[str] = None,
2813 link_to_name: bool = False,
2814 match: Optional[str] = None,
2815 info: Optional[_InfoType] = None,
2816 comment: Optional[str] = None,
2817 _unresolvable: bool = False,
2818 **dialect_kw: Any,
2819 ):
2820 r"""
2821 Construct a column-level FOREIGN KEY.
2823 The :class:`_schema.ForeignKey` object when constructed generates a
2824 :class:`_schema.ForeignKeyConstraint`
2825 which is associated with the parent
2826 :class:`_schema.Table` object's collection of constraints.
2828 :param column: A single target column for the key relationship. A
2829 :class:`_schema.Column` object or a column name as a string:
2830 ``tablename.columnkey`` or ``schema.tablename.columnkey``.
2831 ``columnkey`` is the ``key`` which has been assigned to the column
2832 (defaults to the column name itself), unless ``link_to_name`` is
2833 ``True`` in which case the rendered name of the column is used.
2835 :param name: Optional string. An in-database name for the key if
2836 `constraint` is not provided.
2838 :param onupdate: Optional string. If set, emit ON UPDATE <value> when
2839 issuing DDL for this constraint. Typical values include CASCADE,
2840 DELETE and RESTRICT.
2842 .. seealso::
2844 :ref:`on_update_on_delete`
2846 :param ondelete: Optional string. If set, emit ON DELETE <value> when
2847 issuing DDL for this constraint. Typical values include CASCADE,
2848 SET NULL and RESTRICT. Some dialects may allow for additional
2849 syntaxes.
2851 .. seealso::
2853 :ref:`on_update_on_delete`
2855 :param deferrable: Optional bool. If set, emit DEFERRABLE or NOT
2856 DEFERRABLE when issuing DDL for this constraint.
2858 :param initially: Optional string. If set, emit INITIALLY <value> when
2859 issuing DDL for this constraint.
2861 :param link_to_name: if True, the string name given in ``column`` is
2862 the rendered name of the referenced column, not its locally
2863 assigned ``key``.
2865 :param use_alter: passed to the underlying
2866 :class:`_schema.ForeignKeyConstraint`
2867 to indicate the constraint should
2868 be generated/dropped externally from the CREATE TABLE/ DROP TABLE
2869 statement. See :paramref:`_schema.ForeignKeyConstraint.use_alter`
2870 for further description.
2872 .. seealso::
2874 :paramref:`_schema.ForeignKeyConstraint.use_alter`
2876 :ref:`use_alter`
2878 :param match: Optional string. If set, emit MATCH <value> when issuing
2879 DDL for this constraint. Typical values include SIMPLE, PARTIAL
2880 and FULL.
2882 :param info: Optional data dictionary which will be populated into the
2883 :attr:`.SchemaItem.info` attribute of this object.
2885 :param comment: Optional string that will render an SQL comment on
2886 foreign key constraint creation.
2888 .. versionadded:: 2.0
2890 :param \**dialect_kw: Additional keyword arguments are dialect
2891 specific, and passed in the form ``<dialectname>_<argname>``. The
2892 arguments are ultimately handled by a corresponding
2893 :class:`_schema.ForeignKeyConstraint`.
2894 See the documentation regarding
2895 an individual dialect at :ref:`dialect_toplevel` for detail on
2896 documented arguments.
2898 """
2900 self._colspec = coercions.expect(roles.DDLReferredColumnRole, column)
2901 self._unresolvable = _unresolvable
2903 if isinstance(self._colspec, str):
2904 self._table_column = None
2905 else:
2906 self._table_column = self._colspec
2908 if not isinstance(
2909 self._table_column.table, (type(None), TableClause)
2910 ):
2911 raise exc.ArgumentError(
2912 "ForeignKey received Column not bound "
2913 "to a Table, got: %r" % self._table_column.table
2914 )
2916 # the linked ForeignKeyConstraint.
2917 # ForeignKey will create this when parent Column
2918 # is attached to a Table, *or* ForeignKeyConstraint
2919 # object passes itself in when creating ForeignKey
2920 # markers.
2921 self.constraint = _constraint
2923 # .parent is not Optional under normal use
2924 self.parent = None # type: ignore
2926 self.use_alter = use_alter
2927 self.name = name
2928 self.onupdate = onupdate
2929 self.ondelete = ondelete
2930 self.deferrable = deferrable
2931 self.initially = initially
2932 self.link_to_name = link_to_name
2933 self.match = match
2934 self.comment = comment
2935 if info:
2936 self.info = info
2937 self._unvalidated_dialect_kw = dialect_kw
2939 def __repr__(self) -> str:
2940 return "ForeignKey(%r)" % self._get_colspec()
2942 @util.deprecated(
2943 "1.4",
2944 "The :meth:`_schema.ForeignKey.copy` method is deprecated "
2945 "and will be removed in a future release.",
2946 )
2947 def copy(self, *, schema: Optional[str] = None, **kw: Any) -> ForeignKey:
2948 return self._copy(schema=schema, **kw)
2950 def _copy(self, *, schema: Optional[str] = None, **kw: Any) -> ForeignKey:
2951 """Produce a copy of this :class:`_schema.ForeignKey` object.
2953 The new :class:`_schema.ForeignKey` will not be bound
2954 to any :class:`_schema.Column`.
2956 This method is usually used by the internal
2957 copy procedures of :class:`_schema.Column`, :class:`_schema.Table`,
2958 and :class:`_schema.MetaData`.
2960 :param schema: The returned :class:`_schema.ForeignKey` will
2961 reference the original table and column name, qualified
2962 by the given string schema name.
2964 """
2965 fk = ForeignKey(
2966 self._get_colspec(schema=schema),
2967 use_alter=self.use_alter,
2968 name=self.name,
2969 onupdate=self.onupdate,
2970 ondelete=self.ondelete,
2971 deferrable=self.deferrable,
2972 initially=self.initially,
2973 link_to_name=self.link_to_name,
2974 match=self.match,
2975 comment=self.comment,
2976 **self._unvalidated_dialect_kw,
2977 )
2978 return self._schema_item_copy(fk)
2980 def _get_colspec(
2981 self,
2982 schema: Optional[
2983 Union[
2984 str,
2985 Literal[SchemaConst.RETAIN_SCHEMA, SchemaConst.BLANK_SCHEMA],
2986 ]
2987 ] = None,
2988 table_name: Optional[str] = None,
2989 _is_copy: bool = False,
2990 ) -> str:
2991 """Return a string based 'column specification' for this
2992 :class:`_schema.ForeignKey`.
2994 This is usually the equivalent of the string-based "tablename.colname"
2995 argument first passed to the object's constructor.
2997 """
2998 if schema not in (None, RETAIN_SCHEMA):
2999 _schema, tname, colname = self._column_tokens
3000 if table_name is not None:
3001 tname = table_name
3002 if schema is BLANK_SCHEMA:
3003 return "%s.%s" % (tname, colname)
3004 else:
3005 return "%s.%s.%s" % (schema, tname, colname)
3006 elif table_name:
3007 schema, tname, colname = self._column_tokens
3008 if schema:
3009 return "%s.%s.%s" % (schema, table_name, colname)
3010 else:
3011 return "%s.%s" % (table_name, colname)
3012 elif self._table_column is not None:
3013 if self._table_column.table is None:
3014 if _is_copy:
3015 raise exc.InvalidRequestError(
3016 f"Can't copy ForeignKey object which refers to "
3017 f"non-table bound Column {self._table_column!r}"
3018 )
3019 else:
3020 return self._table_column.key
3021 return "%s.%s" % (
3022 self._table_column.table.fullname,
3023 self._table_column.key,
3024 )
3025 else:
3026 assert isinstance(self._colspec, str)
3027 return self._colspec
3029 @property
3030 def _referred_schema(self) -> Optional[str]:
3031 return self._column_tokens[0]
3033 def _table_key(self) -> Any:
3034 if self._table_column is not None:
3035 if self._table_column.table is None:
3036 return None
3037 else:
3038 return self._table_column.table.key
3039 else:
3040 schema, tname, colname = self._column_tokens
3041 return _get_table_key(tname, schema)
3043 target_fullname = property(_get_colspec)
3045 def references(self, table: Table) -> bool:
3046 """Return True if the given :class:`_schema.Table`
3047 is referenced by this
3048 :class:`_schema.ForeignKey`."""
3050 return table.corresponding_column(self.column) is not None
3052 def get_referent(self, table: FromClause) -> Optional[Column[Any]]:
3053 """Return the :class:`_schema.Column` in the given
3054 :class:`_schema.Table` (or any :class:`.FromClause`)
3055 referenced by this :class:`_schema.ForeignKey`.
3057 Returns None if this :class:`_schema.ForeignKey`
3058 does not reference the given
3059 :class:`_schema.Table`.
3061 """
3062 # our column is a Column, and any subquery etc. proxying us
3063 # would be doing so via another Column, so that's what would
3064 # be returned here
3065 return table.columns.corresponding_column(self.column) # type: ignore
3067 @util.memoized_property
3068 def _column_tokens(self) -> Tuple[Optional[str], str, Optional[str]]:
3069 """parse a string-based _colspec into its component parts."""
3071 m = self._get_colspec().split(".")
3072 if m is None:
3073 raise exc.ArgumentError(
3074 f"Invalid foreign key column specification: {self._colspec}"
3075 )
3076 if len(m) == 1:
3077 tname = m.pop()
3078 colname = None
3079 else:
3080 colname = m.pop()
3081 tname = m.pop()
3083 # A FK between column 'bar' and table 'foo' can be
3084 # specified as 'foo', 'foo.bar', 'dbo.foo.bar',
3085 # 'otherdb.dbo.foo.bar'. Once we have the column name and
3086 # the table name, treat everything else as the schema
3087 # name. Some databases (e.g. Sybase) support
3088 # inter-database foreign keys. See tickets#1341 and --
3089 # indirectly related -- Ticket #594. This assumes that '.'
3090 # will never appear *within* any component of the FK.
3092 if len(m) > 0:
3093 schema = ".".join(m)
3094 else:
3095 schema = None
3096 return schema, tname, colname
3098 def _resolve_col_tokens(self) -> Tuple[Table, str, Optional[str]]:
3099 if self.parent is None:
3100 raise exc.InvalidRequestError(
3101 "this ForeignKey object does not yet have a "
3102 "parent Column associated with it."
3103 )
3105 elif self.parent.table is None:
3106 raise exc.InvalidRequestError(
3107 "this ForeignKey's parent column is not yet associated "
3108 "with a Table."
3109 )
3111 parenttable = self.parent.table
3113 if self._unresolvable:
3114 schema, tname, colname = self._column_tokens
3115 tablekey = _get_table_key(tname, schema)
3116 return parenttable, tablekey, colname
3118 # assertion
3119 # basically Column._make_proxy() sends the actual
3120 # target Column to the ForeignKey object, so the
3121 # string resolution here is never called.
3122 for c in self.parent.base_columns:
3123 if isinstance(c, Column):
3124 assert c.table is parenttable
3125 break
3126 else:
3127 assert False
3128 ######################
3130 schema, tname, colname = self._column_tokens
3132 if schema is None and parenttable.metadata.schema is not None:
3133 schema = parenttable.metadata.schema
3135 tablekey = _get_table_key(tname, schema)
3136 return parenttable, tablekey, colname
3138 def _link_to_col_by_colstring(
3139 self, parenttable: Table, table: Table, colname: Optional[str]
3140 ) -> Column[Any]:
3141 _column = None
3142 if colname is None:
3143 # colname is None in the case that ForeignKey argument
3144 # was specified as table name only, in which case we
3145 # match the column name to the same column on the
3146 # parent.
3147 # this use case wasn't working in later 1.x series
3148 # as it had no test coverage; fixed in 2.0
3149 parent = self.parent
3150 assert parent is not None
3151 key = parent.key
3152 _column = table.c.get(key, None)
3153 elif self.link_to_name:
3154 key = colname
3155 for c in table.c:
3156 if c.name == colname:
3157 _column = c
3158 else:
3159 key = colname
3160 _column = table.c.get(colname, None)
3162 if _column is None:
3163 raise exc.NoReferencedColumnError(
3164 "Could not initialize target column "
3165 f"for ForeignKey '{self._colspec}' "
3166 f"on table '{parenttable.name}': "
3167 f"table '{table.name}' has no column named '{key}'",
3168 table.name,
3169 key,
3170 )
3172 return _column
3174 def _set_target_column(self, column: Column[Any]) -> None:
3175 assert self.parent is not None
3177 # propagate TypeEngine to parent if it didn't have one
3178 if self.parent.type._isnull:
3179 self.parent.type = column.type
3181 # super-edgy case, if other FKs point to our column,
3182 # they'd get the type propagated out also.
3184 def set_type(fk: ForeignKey) -> None:
3185 if fk.parent.type._isnull:
3186 fk.parent.type = column.type
3188 self.parent._setup_on_memoized_fks(set_type)
3190 self.column = column # type: ignore
3192 @util.ro_memoized_property
3193 def column(self) -> Column[Any]:
3194 """Return the target :class:`_schema.Column` referenced by this
3195 :class:`_schema.ForeignKey`.
3197 If no target column has been established, an exception
3198 is raised.
3200 """
3202 return self._resolve_column()
3204 @overload
3205 def _resolve_column(
3206 self, *, raiseerr: Literal[True] = ...
3207 ) -> Column[Any]: ...
3209 @overload
3210 def _resolve_column(
3211 self, *, raiseerr: bool = ...
3212 ) -> Optional[Column[Any]]: ...
3214 def _resolve_column(
3215 self, *, raiseerr: bool = True
3216 ) -> Optional[Column[Any]]:
3217 _column: Column[Any]
3219 if isinstance(self._colspec, str):
3220 parenttable, tablekey, colname = self._resolve_col_tokens()
3222 if self._unresolvable or tablekey not in parenttable.metadata:
3223 if not raiseerr:
3224 return None
3225 raise exc.NoReferencedTableError(
3226 f"Foreign key associated with column "
3227 f"'{self.parent}' could not find "
3228 f"table '{tablekey}' with which to generate a "
3229 f"foreign key to target column '{colname}'",
3230 tablekey,
3231 )
3232 elif parenttable.key not in parenttable.metadata:
3233 if not raiseerr:
3234 return None
3235 raise exc.InvalidRequestError(
3236 f"Table {parenttable} is no longer associated with its "
3237 "parent MetaData"
3238 )
3239 else:
3240 table = parenttable.metadata.tables[tablekey]
3241 return self._link_to_col_by_colstring(
3242 parenttable, table, colname
3243 )
3245 elif hasattr(self._colspec, "__clause_element__"):
3246 _column = self._colspec.__clause_element__()
3247 return _column
3248 else:
3249 _column = self._colspec
3250 return _column
3252 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
3253 assert isinstance(parent, Column)
3255 if self.parent is not None and self.parent is not parent:
3256 raise exc.InvalidRequestError(
3257 "This ForeignKey already has a parent !"
3258 )
3259 self.parent = parent
3260 self.parent.foreign_keys.add(self)
3261 self.parent._on_table_attach(self._set_table)
3263 def _set_remote_table(self, table: Table) -> None:
3264 parenttable, _, colname = self._resolve_col_tokens()
3265 _column = self._link_to_col_by_colstring(parenttable, table, colname)
3266 self._set_target_column(_column)
3267 assert self.constraint is not None
3268 self.constraint._validate_dest_table(table)
3270 def _remove_from_metadata(self, metadata: MetaData) -> None:
3271 parenttable, table_key, colname = self._resolve_col_tokens()
3272 fk_key = (table_key, colname)
3274 if self in metadata._fk_memos[fk_key]:
3275 # TODO: no test coverage for self not in memos
3276 metadata._fk_memos[fk_key].remove(self)
3278 def _set_table(self, column: Column[Any], table: Table) -> None:
3279 # standalone ForeignKey - create ForeignKeyConstraint
3280 # on the hosting Table when attached to the Table.
3281 assert isinstance(table, Table)
3282 if self.constraint is None:
3283 self.constraint = ForeignKeyConstraint(
3284 [],
3285 [],
3286 use_alter=self.use_alter,
3287 name=self.name,
3288 onupdate=self.onupdate,
3289 ondelete=self.ondelete,
3290 deferrable=self.deferrable,
3291 initially=self.initially,
3292 match=self.match,
3293 comment=self.comment,
3294 **self._unvalidated_dialect_kw,
3295 )
3296 self.constraint._append_element(column, self)
3297 self.constraint._set_parent_with_dispatch(table)
3298 table.foreign_keys.add(self)
3299 # set up remote ".column" attribute, or a note to pick it
3300 # up when the other Table/Column shows up
3301 if isinstance(self._colspec, str):
3302 parenttable, table_key, colname = self._resolve_col_tokens()
3303 fk_key = (table_key, colname)
3304 if table_key in parenttable.metadata.tables:
3305 table = parenttable.metadata.tables[table_key]
3306 try:
3307 _column = self._link_to_col_by_colstring(
3308 parenttable, table, colname
3309 )
3310 except exc.NoReferencedColumnError:
3311 # this is OK, we'll try later
3312 pass
3313 else:
3314 self._set_target_column(_column)
3316 parenttable.metadata._fk_memos[fk_key].append(self)
3317 elif hasattr(self._colspec, "__clause_element__"):
3318 _column = self._colspec.__clause_element__()
3319 self._set_target_column(_column)
3320 else:
3321 _column = self._colspec
3322 self._set_target_column(_column)
3325if TYPE_CHECKING:
3327 def default_is_sequence(
3328 obj: Optional[DefaultGenerator],
3329 ) -> TypeGuard[Sequence]: ...
3331 def default_is_clause_element(
3332 obj: Optional[DefaultGenerator],
3333 ) -> TypeGuard[ColumnElementColumnDefault]: ...
3335 def default_is_scalar(
3336 obj: Optional[DefaultGenerator],
3337 ) -> TypeGuard[ScalarElementColumnDefault]: ...
3339else:
3340 default_is_sequence = operator.attrgetter("is_sequence")
3342 default_is_clause_element = operator.attrgetter("is_clause_element")
3344 default_is_scalar = operator.attrgetter("is_scalar")
3347class DefaultGenerator(Executable, SchemaItem):
3348 """Base class for column *default* values.
3350 This object is only present on column.default or column.onupdate.
3351 It's not valid as a server default.
3353 """
3355 __visit_name__ = "default_generator"
3357 _is_default_generator = True
3358 is_sequence = False
3359 is_identity = False
3360 is_server_default = False
3361 is_clause_element = False
3362 is_callable = False
3363 is_scalar = False
3364 has_arg = False
3365 is_sentinel = False
3366 column: Optional[Column[Any]]
3368 def __init__(self, for_update: bool = False) -> None:
3369 self.for_update = for_update
3371 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
3372 if TYPE_CHECKING:
3373 assert isinstance(parent, Column)
3374 self.column = parent
3375 if self.for_update:
3376 self.column.onupdate = self
3377 else:
3378 self.column.default = self
3380 def _copy(self) -> DefaultGenerator:
3381 raise NotImplementedError()
3383 def _execute_on_connection(
3384 self,
3385 connection: Connection,
3386 distilled_params: _CoreMultiExecuteParams,
3387 execution_options: CoreExecuteOptionsParameter,
3388 ) -> Any:
3389 util.warn_deprecated(
3390 "Using the .execute() method to invoke a "
3391 "DefaultGenerator object is deprecated; please use "
3392 "the .scalar() method.",
3393 "2.0",
3394 )
3395 return self._execute_on_scalar(
3396 connection, distilled_params, execution_options
3397 )
3399 def _execute_on_scalar(
3400 self,
3401 connection: Connection,
3402 distilled_params: _CoreMultiExecuteParams,
3403 execution_options: CoreExecuteOptionsParameter,
3404 ) -> Any:
3405 return connection._execute_default(
3406 self, distilled_params, execution_options
3407 )
3410class ColumnDefault(DefaultGenerator, ABC):
3411 """A plain default value on a column.
3413 This could correspond to a constant, a callable function,
3414 or a SQL clause.
3416 :class:`.ColumnDefault` is generated automatically
3417 whenever the ``default``, ``onupdate`` arguments of
3418 :class:`_schema.Column` are used. A :class:`.ColumnDefault`
3419 can be passed positionally as well.
3421 For example, the following::
3423 Column("foo", Integer, default=50)
3425 Is equivalent to::
3427 Column("foo", Integer, ColumnDefault(50))
3429 """
3431 arg: Any
3433 @overload
3434 def __new__(
3435 cls, arg: Callable[..., Any], for_update: bool = ...
3436 ) -> CallableColumnDefault: ...
3438 @overload
3439 def __new__(
3440 cls, arg: ColumnElement[Any], for_update: bool = ...
3441 ) -> ColumnElementColumnDefault: ...
3443 # if I return ScalarElementColumnDefault here, which is what's actually
3444 # returned, mypy complains that
3445 # overloads overlap w/ incompatible return types.
3446 @overload
3447 def __new__(cls, arg: object, for_update: bool = ...) -> ColumnDefault: ...
3449 def __new__(
3450 cls, arg: Any = None, for_update: bool = False
3451 ) -> ColumnDefault:
3452 """Construct a new :class:`.ColumnDefault`.
3455 :param arg: argument representing the default value.
3456 May be one of the following:
3458 * a plain non-callable Python value, such as a
3459 string, integer, boolean, or other simple type.
3460 The default value will be used as is each time.
3461 * a SQL expression, that is one which derives from
3462 :class:`_expression.ColumnElement`. The SQL expression will
3463 be rendered into the INSERT or UPDATE statement,
3464 or in the case of a primary key column when
3465 RETURNING is not used may be
3466 pre-executed before an INSERT within a SELECT.
3467 * A Python callable. The function will be invoked for each
3468 new row subject to an INSERT or UPDATE.
3469 The callable must accept exactly
3470 zero or one positional arguments. The one-argument form
3471 will receive an instance of the :class:`.ExecutionContext`,
3472 which provides contextual information as to the current
3473 :class:`_engine.Connection` in use as well as the current
3474 statement and parameters.
3476 """
3478 if isinstance(arg, FetchedValue):
3479 raise exc.ArgumentError(
3480 "ColumnDefault may not be a server-side default type."
3481 )
3482 elif callable(arg):
3483 cls = CallableColumnDefault
3484 elif isinstance(arg, ClauseElement):
3485 cls = ColumnElementColumnDefault
3486 elif arg is not None:
3487 cls = ScalarElementColumnDefault
3489 return object.__new__(cls)
3491 def __repr__(self) -> str:
3492 return f"{self.__class__.__name__}({self.arg!r})"
3495class ScalarElementColumnDefault(ColumnDefault):
3496 """default generator for a fixed scalar Python value
3498 .. versionadded: 2.0
3500 """
3502 is_scalar = True
3503 has_arg = True
3505 def __init__(self, arg: Any, for_update: bool = False) -> None:
3506 self.for_update = for_update
3507 self.arg = arg
3509 def _copy(self) -> ScalarElementColumnDefault:
3510 return ScalarElementColumnDefault(
3511 arg=self.arg, for_update=self.for_update
3512 )
3515class _InsertSentinelColumnDefault(ColumnDefault):
3516 """Default generator that's specific to the use of a "sentinel" column
3517 when using the insertmanyvalues feature.
3519 This default is used as part of the :func:`_schema.insert_sentinel`
3520 construct.
3522 """
3524 is_sentinel = True
3525 for_update = False
3526 arg = None
3528 def __new__(cls) -> _InsertSentinelColumnDefault:
3529 return object.__new__(cls)
3531 def __init__(self) -> None:
3532 pass
3534 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
3535 col = cast("Column[Any]", parent)
3536 if not col._insert_sentinel:
3537 raise exc.ArgumentError(
3538 "The _InsertSentinelColumnDefault may only be applied to a "
3539 "Column marked as insert_sentinel=True"
3540 )
3541 elif not col.nullable:
3542 raise exc.ArgumentError(
3543 "The _InsertSentinelColumnDefault may only be applied to a "
3544 "Column that is nullable"
3545 )
3547 super()._set_parent(parent, **kw)
3549 def _copy(self) -> _InsertSentinelColumnDefault:
3550 return _InsertSentinelColumnDefault()
3553_SQLExprDefault = Union["ColumnElement[Any]", "TextClause"]
3556class ColumnElementColumnDefault(ColumnDefault):
3557 """default generator for a SQL expression
3559 .. versionadded:: 2.0
3561 """
3563 is_clause_element = True
3564 has_arg = True
3565 arg: _SQLExprDefault
3567 def __init__(
3568 self,
3569 arg: _SQLExprDefault,
3570 for_update: bool = False,
3571 ) -> None:
3572 self.for_update = for_update
3573 self.arg = arg
3575 def _copy(self) -> ColumnElementColumnDefault:
3576 return ColumnElementColumnDefault(
3577 arg=self.arg, for_update=self.for_update
3578 )
3580 @util.memoized_property
3581 @util.preload_module("sqlalchemy.sql.sqltypes")
3582 def _arg_is_typed(self) -> bool:
3583 sqltypes = util.preloaded.sql_sqltypes
3585 return not isinstance(self.arg.type, sqltypes.NullType)
3588class _CallableColumnDefaultProtocol(Protocol):
3589 def __call__(self, context: ExecutionContext) -> Any: ...
3592class CallableColumnDefault(ColumnDefault):
3593 """default generator for a callable Python function
3595 .. versionadded:: 2.0
3597 """
3599 is_callable = True
3600 arg: _CallableColumnDefaultProtocol
3601 has_arg = True
3603 def __init__(
3604 self,
3605 arg: Union[_CallableColumnDefaultProtocol, Callable[[], Any]],
3606 for_update: bool = False,
3607 ) -> None:
3608 self.for_update = for_update
3609 self.arg = self._maybe_wrap_callable(arg)
3611 def _copy(self) -> CallableColumnDefault:
3612 return CallableColumnDefault(arg=self.arg, for_update=self.for_update)
3614 def _maybe_wrap_callable(
3615 self, fn: Union[_CallableColumnDefaultProtocol, Callable[[], Any]]
3616 ) -> _CallableColumnDefaultProtocol:
3617 """Wrap callables that don't accept a context.
3619 This is to allow easy compatibility with default callables
3620 that aren't specific to accepting of a context.
3622 """
3624 try:
3625 argspec = util.get_callable_argspec(fn, no_self=True)
3626 except TypeError:
3627 return util.wrap_callable(lambda ctx: fn(), fn) # type: ignore
3629 defaulted = argspec[3] is not None and len(argspec[3]) or 0
3630 positionals = len(argspec[0]) - defaulted
3632 if positionals == 0:
3633 return util.wrap_callable(lambda ctx: fn(), fn) # type: ignore
3635 elif positionals == 1:
3636 return fn # type: ignore
3637 else:
3638 raise exc.ArgumentError(
3639 "ColumnDefault Python function takes zero or one "
3640 "positional arguments"
3641 )
3644class IdentityOptions:
3645 """Defines options for a named database sequence or an identity column.
3647 .. versionadded:: 1.3.18
3649 .. seealso::
3651 :class:`.Sequence`
3653 """
3655 def __init__(
3656 self,
3657 start: Optional[int] = None,
3658 increment: Optional[int] = None,
3659 minvalue: Optional[int] = None,
3660 maxvalue: Optional[int] = None,
3661 nominvalue: Optional[bool] = None,
3662 nomaxvalue: Optional[bool] = None,
3663 cycle: Optional[bool] = None,
3664 cache: Optional[int] = None,
3665 order: Optional[bool] = None,
3666 ) -> None:
3667 """Construct a :class:`.IdentityOptions` object.
3669 See the :class:`.Sequence` documentation for a complete description
3670 of the parameters.
3672 :param start: the starting index of the sequence.
3673 :param increment: the increment value of the sequence.
3674 :param minvalue: the minimum value of the sequence.
3675 :param maxvalue: the maximum value of the sequence.
3676 :param nominvalue: no minimum value of the sequence.
3677 :param nomaxvalue: no maximum value of the sequence.
3678 :param cycle: allows the sequence to wrap around when the maxvalue
3679 or minvalue has been reached.
3680 :param cache: optional integer value; number of future values in the
3681 sequence which are calculated in advance.
3682 :param order: optional boolean value; if ``True``, renders the
3683 ORDER keyword.
3685 """
3686 self.start = start
3687 self.increment = increment
3688 self.minvalue = minvalue
3689 self.maxvalue = maxvalue
3690 self.nominvalue = nominvalue
3691 self.nomaxvalue = nomaxvalue
3692 self.cycle = cycle
3693 self.cache = cache
3694 self.order = order
3696 @property
3697 def _increment_is_negative(self) -> bool:
3698 return self.increment is not None and self.increment < 0
3701class Sequence(HasSchemaAttr, IdentityOptions, DefaultGenerator):
3702 """Represents a named database sequence.
3704 The :class:`.Sequence` object represents the name and configurational
3705 parameters of a database sequence. It also represents
3706 a construct that can be "executed" by a SQLAlchemy :class:`_engine.Engine`
3707 or :class:`_engine.Connection`,
3708 rendering the appropriate "next value" function
3709 for the target database and returning a result.
3711 The :class:`.Sequence` is typically associated with a primary key column::
3713 some_table = Table(
3714 "some_table",
3715 metadata,
3716 Column(
3717 "id",
3718 Integer,
3719 Sequence("some_table_seq", start=1),
3720 primary_key=True,
3721 ),
3722 )
3724 When CREATE TABLE is emitted for the above :class:`_schema.Table`, if the
3725 target platform supports sequences, a CREATE SEQUENCE statement will
3726 be emitted as well. For platforms that don't support sequences,
3727 the :class:`.Sequence` construct is ignored.
3729 .. seealso::
3731 :ref:`defaults_sequences`
3733 :class:`.CreateSequence`
3735 :class:`.DropSequence`
3737 """
3739 __visit_name__ = "sequence"
3741 is_sequence = True
3743 column: Optional[Column[Any]]
3744 data_type: Optional[TypeEngine[int]]
3746 def __init__(
3747 self,
3748 name: str,
3749 start: Optional[int] = None,
3750 increment: Optional[int] = None,
3751 minvalue: Optional[int] = None,
3752 maxvalue: Optional[int] = None,
3753 nominvalue: Optional[bool] = None,
3754 nomaxvalue: Optional[bool] = None,
3755 cycle: Optional[bool] = None,
3756 schema: Optional[Union[str, Literal[SchemaConst.BLANK_SCHEMA]]] = None,
3757 cache: Optional[int] = None,
3758 order: Optional[bool] = None,
3759 data_type: Optional[_TypeEngineArgument[int]] = None,
3760 optional: bool = False,
3761 quote: Optional[bool] = None,
3762 metadata: Optional[MetaData] = None,
3763 quote_schema: Optional[bool] = None,
3764 for_update: bool = False,
3765 ) -> None:
3766 """Construct a :class:`.Sequence` object.
3768 :param name: the name of the sequence.
3770 :param start: the starting index of the sequence. This value is
3771 used when the CREATE SEQUENCE command is emitted to the database
3772 as the value of the "START WITH" clause. If ``None``, the
3773 clause is omitted, which on most platforms indicates a starting
3774 value of 1.
3776 .. versionchanged:: 2.0 The :paramref:`.Sequence.start` parameter
3777 is required in order to have DDL emit "START WITH". This is a
3778 reversal of a change made in version 1.4 which would implicitly
3779 render "START WITH 1" if the :paramref:`.Sequence.start` were
3780 not included. See :ref:`change_7211` for more detail.
3782 :param increment: the increment value of the sequence. This
3783 value is used when the CREATE SEQUENCE command is emitted to
3784 the database as the value of the "INCREMENT BY" clause. If ``None``,
3785 the clause is omitted, which on most platforms indicates an
3786 increment of 1.
3787 :param minvalue: the minimum value of the sequence. This
3788 value is used when the CREATE SEQUENCE command is emitted to
3789 the database as the value of the "MINVALUE" clause. If ``None``,
3790 the clause is omitted, which on most platforms indicates a
3791 minvalue of 1 and -2^63-1 for ascending and descending sequences,
3792 respectively.
3794 :param maxvalue: the maximum value of the sequence. This
3795 value is used when the CREATE SEQUENCE command is emitted to
3796 the database as the value of the "MAXVALUE" clause. If ``None``,
3797 the clause is omitted, which on most platforms indicates a
3798 maxvalue of 2^63-1 and -1 for ascending and descending sequences,
3799 respectively.
3801 :param nominvalue: no minimum value of the sequence. This
3802 value is used when the CREATE SEQUENCE command is emitted to
3803 the database as the value of the "NO MINVALUE" clause. If ``None``,
3804 the clause is omitted, which on most platforms indicates a
3805 minvalue of 1 and -2^63-1 for ascending and descending sequences,
3806 respectively.
3808 :param nomaxvalue: no maximum value of the sequence. This
3809 value is used when the CREATE SEQUENCE command is emitted to
3810 the database as the value of the "NO MAXVALUE" clause. If ``None``,
3811 the clause is omitted, which on most platforms indicates a
3812 maxvalue of 2^63-1 and -1 for ascending and descending sequences,
3813 respectively.
3815 :param cycle: allows the sequence to wrap around when the maxvalue
3816 or minvalue has been reached by an ascending or descending sequence
3817 respectively. This value is used when the CREATE SEQUENCE command
3818 is emitted to the database as the "CYCLE" clause. If the limit is
3819 reached, the next number generated will be the minvalue or maxvalue,
3820 respectively. If cycle=False (the default) any calls to nextval
3821 after the sequence has reached its maximum value will return an
3822 error.
3824 :param schema: optional schema name for the sequence, if located
3825 in a schema other than the default. The rules for selecting the
3826 schema name when a :class:`_schema.MetaData`
3827 is also present are the same
3828 as that of :paramref:`_schema.Table.schema`.
3830 :param cache: optional integer value; number of future values in the
3831 sequence which are calculated in advance. Renders the CACHE keyword
3832 understood by Oracle Database and PostgreSQL.
3834 :param order: optional boolean value; if ``True``, renders the
3835 ORDER keyword, understood by Oracle Database, indicating the sequence
3836 is definitively ordered. May be necessary to provide deterministic
3837 ordering using Oracle RAC.
3839 :param data_type: The type to be returned by the sequence, for
3840 dialects that allow us to choose between INTEGER, BIGINT, etc.
3841 (e.g., mssql).
3843 .. versionadded:: 1.4.0
3845 :param optional: boolean value, when ``True``, indicates that this
3846 :class:`.Sequence` object only needs to be explicitly generated
3847 on backends that don't provide another way to generate primary
3848 key identifiers. Currently, it essentially means, "don't create
3849 this sequence on the PostgreSQL backend, where the SERIAL keyword
3850 creates a sequence for us automatically".
3851 :param quote: boolean value, when ``True`` or ``False``, explicitly
3852 forces quoting of the :paramref:`_schema.Sequence.name` on or off.
3853 When left at its default of ``None``, normal quoting rules based
3854 on casing and reserved words take place.
3855 :param quote_schema: Set the quoting preferences for the ``schema``
3856 name.
3858 :param metadata: optional :class:`_schema.MetaData` object which this
3859 :class:`.Sequence` will be associated with. A :class:`.Sequence`
3860 that is associated with a :class:`_schema.MetaData`
3861 gains the following
3862 capabilities:
3864 * The :class:`.Sequence` will inherit the
3865 :paramref:`_schema.MetaData.schema`
3866 parameter specified to the target :class:`_schema.MetaData`, which
3867 affects the production of CREATE / DROP DDL, if any.
3869 * The :meth:`.Sequence.create` and :meth:`.Sequence.drop` methods
3870 automatically use the engine bound to the :class:`_schema.MetaData`
3871 object, if any.
3873 * The :meth:`_schema.MetaData.create_all` and
3874 :meth:`_schema.MetaData.drop_all`
3875 methods will emit CREATE / DROP for this :class:`.Sequence`,
3876 even if the :class:`.Sequence` is not associated with any
3877 :class:`_schema.Table` / :class:`_schema.Column`
3878 that's a member of this
3879 :class:`_schema.MetaData`.
3881 The above behaviors can only occur if the :class:`.Sequence` is
3882 explicitly associated with the :class:`_schema.MetaData`
3883 via this parameter.
3885 .. seealso::
3887 :ref:`sequence_metadata` - full discussion of the
3888 :paramref:`.Sequence.metadata` parameter.
3890 :param for_update: Indicates this :class:`.Sequence`, when associated
3891 with a :class:`_schema.Column`,
3892 should be invoked for UPDATE statements
3893 on that column's table, rather than for INSERT statements, when
3894 no value is otherwise present for that column in the statement.
3896 """
3897 DefaultGenerator.__init__(self, for_update=for_update)
3898 IdentityOptions.__init__(
3899 self,
3900 start=start,
3901 increment=increment,
3902 minvalue=minvalue,
3903 maxvalue=maxvalue,
3904 nominvalue=nominvalue,
3905 nomaxvalue=nomaxvalue,
3906 cycle=cycle,
3907 cache=cache,
3908 order=order,
3909 )
3910 self.column = None
3911 self.name = quoted_name(name, quote)
3912 self.optional = optional
3913 if schema is BLANK_SCHEMA:
3914 self.schema = schema = None
3915 elif metadata is not None and schema is None and metadata.schema:
3916 self.schema = schema = metadata.schema
3917 else:
3918 self.schema = quoted_name.construct(schema, quote_schema)
3919 self.metadata = metadata
3920 self._key = _get_table_key(name, schema)
3921 if metadata:
3922 self._set_metadata(metadata)
3923 if data_type is not None:
3924 self.data_type = to_instance(data_type)
3925 else:
3926 self.data_type = None
3928 @util.preload_module("sqlalchemy.sql.functions")
3929 def next_value(self) -> Function[int]:
3930 """Return a :class:`.next_value` function element
3931 which will render the appropriate increment function
3932 for this :class:`.Sequence` within any SQL expression.
3934 """
3935 return util.preloaded.sql_functions.func.next_value(self)
3937 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
3938 column = parent
3939 assert isinstance(column, Column)
3940 super()._set_parent(column)
3941 column._on_table_attach(self._set_table)
3943 def _copy(self) -> Sequence:
3944 return Sequence(
3945 name=self.name,
3946 start=self.start,
3947 increment=self.increment,
3948 minvalue=self.minvalue,
3949 maxvalue=self.maxvalue,
3950 nominvalue=self.nominvalue,
3951 nomaxvalue=self.nomaxvalue,
3952 cycle=self.cycle,
3953 schema=self.schema,
3954 cache=self.cache,
3955 order=self.order,
3956 data_type=self.data_type,
3957 optional=self.optional,
3958 metadata=None,
3959 for_update=self.for_update,
3960 )
3962 def _set_table(self, column: Column[Any], table: Table) -> None:
3963 self._set_metadata(table.metadata)
3965 def _set_metadata(self, metadata: MetaData) -> None:
3966 self.metadata = metadata
3967 self.metadata._sequences[self._key] = self
3969 def create(self, bind: _CreateDropBind, checkfirst: bool = True) -> None:
3970 """Creates this sequence in the database."""
3972 bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
3974 def drop(self, bind: _CreateDropBind, checkfirst: bool = True) -> None:
3975 """Drops this sequence from the database."""
3977 bind._run_ddl_visitor(ddl.SchemaDropper, self, checkfirst=checkfirst)
3979 def _not_a_column_expr(self) -> NoReturn:
3980 raise exc.InvalidRequestError(
3981 f"This {self.__class__.__name__} cannot be used directly "
3982 "as a column expression. Use func.next_value(sequence) "
3983 "to produce a 'next value' function that's usable "
3984 "as a column element."
3985 )
3988@inspection._self_inspects
3989class FetchedValue(SchemaEventTarget):
3990 """A marker for a transparent database-side default.
3992 Use :class:`.FetchedValue` when the database is configured
3993 to provide some automatic default for a column.
3995 E.g.::
3997 Column("foo", Integer, FetchedValue())
3999 Would indicate that some trigger or default generator
4000 will create a new value for the ``foo`` column during an
4001 INSERT.
4003 .. seealso::
4005 :ref:`triggered_columns`
4007 """
4009 is_server_default = True
4010 reflected = False
4011 has_argument = False
4012 is_clause_element = False
4013 is_identity = False
4015 column: Optional[Column[Any]]
4017 def __init__(self, for_update: bool = False) -> None:
4018 self.for_update = for_update
4020 def _as_for_update(self, for_update: bool) -> FetchedValue:
4021 if for_update == self.for_update:
4022 return self
4023 else:
4024 return self._clone(for_update)
4026 def _copy(self) -> Self:
4027 return self._clone(self.for_update)
4029 def _clone(self, for_update: bool) -> Self:
4030 n = self.__class__.__new__(self.__class__)
4031 n.__dict__.update(self.__dict__)
4032 n.__dict__.pop("column", None)
4033 n.for_update = for_update
4034 return n
4036 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4037 column = parent
4038 assert isinstance(column, Column)
4039 self.column = column
4040 if self.for_update:
4041 self.column.server_onupdate = self
4042 else:
4043 self.column.server_default = self
4045 def __repr__(self) -> str:
4046 return util.generic_repr(self)
4049class DefaultClause(FetchedValue):
4050 """A DDL-specified DEFAULT column value.
4052 :class:`.DefaultClause` is a :class:`.FetchedValue`
4053 that also generates a "DEFAULT" clause when
4054 "CREATE TABLE" is emitted.
4056 :class:`.DefaultClause` is generated automatically
4057 whenever the ``server_default``, ``server_onupdate`` arguments of
4058 :class:`_schema.Column` are used. A :class:`.DefaultClause`
4059 can be passed positionally as well.
4061 For example, the following::
4063 Column("foo", Integer, server_default="50")
4065 Is equivalent to::
4067 Column("foo", Integer, DefaultClause("50"))
4069 """
4071 has_argument = True
4073 def __init__(
4074 self,
4075 arg: Union[str, ClauseElement, TextClause],
4076 for_update: bool = False,
4077 _reflected: bool = False,
4078 ) -> None:
4079 util.assert_arg_type(arg, (str, ClauseElement, TextClause), "arg")
4080 super().__init__(for_update)
4081 self.arg = arg
4082 self.reflected = _reflected
4084 def __repr__(self) -> str:
4085 return "DefaultClause(%r, for_update=%r)" % (self.arg, self.for_update)
4088class Constraint(DialectKWArgs, HasConditionalDDL, SchemaItem):
4089 """A table-level SQL constraint.
4091 :class:`_schema.Constraint` serves as the base class for the series of
4092 constraint objects that can be associated with :class:`_schema.Table`
4093 objects, including :class:`_schema.PrimaryKeyConstraint`,
4094 :class:`_schema.ForeignKeyConstraint`
4095 :class:`_schema.UniqueConstraint`, and
4096 :class:`_schema.CheckConstraint`.
4098 """
4100 __visit_name__ = "constraint"
4102 _creation_order: int
4103 _column_flag: bool
4105 def __init__(
4106 self,
4107 name: _ConstraintNameArgument = None,
4108 deferrable: Optional[bool] = None,
4109 initially: Optional[str] = None,
4110 info: Optional[_InfoType] = None,
4111 comment: Optional[str] = None,
4112 _create_rule: Optional[Any] = None,
4113 _type_bound: bool = False,
4114 **dialect_kw: Any,
4115 ) -> None:
4116 r"""Create a SQL constraint.
4118 :param name:
4119 Optional, the in-database name of this ``Constraint``.
4121 :param deferrable:
4122 Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
4123 issuing DDL for this constraint.
4125 :param initially:
4126 Optional string. If set, emit INITIALLY <value> when issuing DDL
4127 for this constraint.
4129 :param info: Optional data dictionary which will be populated into the
4130 :attr:`.SchemaItem.info` attribute of this object.
4132 :param comment: Optional string that will render an SQL comment on
4133 foreign key constraint creation.
4135 .. versionadded:: 2.0
4137 :param \**dialect_kw: Additional keyword arguments are dialect
4138 specific, and passed in the form ``<dialectname>_<argname>``. See
4139 the documentation regarding an individual dialect at
4140 :ref:`dialect_toplevel` for detail on documented arguments.
4142 :param _create_rule:
4143 used internally by some datatypes that also create constraints.
4145 :param _type_bound:
4146 used internally to indicate that this constraint is associated with
4147 a specific datatype.
4149 """
4151 self.name = name
4152 self.deferrable = deferrable
4153 self.initially = initially
4154 if info:
4155 self.info = info
4156 self._create_rule = _create_rule
4157 self._type_bound = _type_bound
4158 util.set_creation_order(self)
4159 self._validate_dialect_kwargs(dialect_kw)
4160 self.comment = comment
4162 def _should_create_for_compiler(
4163 self, compiler: DDLCompiler, **kw: Any
4164 ) -> bool:
4165 if self._create_rule is not None and not self._create_rule(compiler):
4166 return False
4167 elif self._ddl_if is not None:
4168 return self._ddl_if._should_execute(
4169 ddl.CreateConstraint(self), self, None, compiler=compiler, **kw
4170 )
4171 else:
4172 return True
4174 @property
4175 def table(self) -> Table:
4176 try:
4177 if isinstance(self.parent, Table):
4178 return self.parent
4179 except AttributeError:
4180 pass
4181 raise exc.InvalidRequestError(
4182 "This constraint is not bound to a table. Did you "
4183 "mean to call table.append_constraint(constraint) ?"
4184 )
4186 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4187 assert isinstance(parent, (Table, Column))
4188 self.parent = parent
4189 parent.constraints.add(self)
4191 @util.deprecated(
4192 "1.4",
4193 "The :meth:`_schema.Constraint.copy` method is deprecated "
4194 "and will be removed in a future release.",
4195 )
4196 def copy(self, **kw: Any) -> Self:
4197 return self._copy(**kw)
4199 def _copy(self, **kw: Any) -> Self:
4200 raise NotImplementedError()
4203class ColumnCollectionMixin:
4204 """A :class:`_expression.ColumnCollection` of :class:`_schema.Column`
4205 objects.
4207 This collection represents the columns which are referred to by
4208 this object.
4210 """
4212 _columns: DedupeColumnCollection[Column[Any]]
4214 _allow_multiple_tables = False
4216 _pending_colargs: List[Optional[Union[str, Column[Any]]]]
4218 if TYPE_CHECKING:
4220 def _set_parent_with_dispatch(
4221 self, parent: SchemaEventTarget, **kw: Any
4222 ) -> None: ...
4224 def __init__(
4225 self,
4226 *columns: _DDLColumnArgument,
4227 _autoattach: bool = True,
4228 _column_flag: bool = False,
4229 _gather_expressions: Optional[
4230 List[Union[str, ColumnElement[Any]]]
4231 ] = None,
4232 ) -> None:
4233 self._column_flag = _column_flag
4234 self._columns = DedupeColumnCollection()
4236 processed_expressions: Optional[
4237 List[Union[ColumnElement[Any], str]]
4238 ] = _gather_expressions
4240 if processed_expressions is not None:
4242 # this is expected to be an empty list
4243 assert not processed_expressions
4245 self._pending_colargs = []
4246 for (
4247 expr,
4248 _,
4249 _,
4250 add_element,
4251 ) in coercions.expect_col_expression_collection(
4252 roles.DDLConstraintColumnRole, columns
4253 ):
4254 self._pending_colargs.append(add_element)
4255 processed_expressions.append(expr)
4256 else:
4257 self._pending_colargs = [
4258 coercions.expect(roles.DDLConstraintColumnRole, column)
4259 for column in columns
4260 ]
4262 if _autoattach and self._pending_colargs:
4263 self._check_attach()
4265 def _check_attach(self, evt: bool = False) -> None:
4266 col_objs = [c for c in self._pending_colargs if isinstance(c, Column)]
4268 cols_w_table = [c for c in col_objs if isinstance(c.table, Table)]
4270 cols_wo_table = set(col_objs).difference(cols_w_table)
4271 if cols_wo_table:
4272 # feature #3341 - place event listeners for Column objects
4273 # such that when all those cols are attached, we autoattach.
4274 assert not evt, "Should not reach here on event call"
4276 # issue #3411 - don't do the per-column auto-attach if some of the
4277 # columns are specified as strings.
4278 has_string_cols = {
4279 c for c in self._pending_colargs if c is not None
4280 }.difference(col_objs)
4281 if not has_string_cols:
4283 def _col_attached(column: Column[Any], table: Table) -> None:
4284 # this isinstance() corresponds with the
4285 # isinstance() above; only want to count Table-bound
4286 # columns
4287 if isinstance(table, Table):
4288 cols_wo_table.discard(column)
4289 if not cols_wo_table:
4290 self._check_attach(evt=True)
4292 self._cols_wo_table = cols_wo_table
4293 for col in cols_wo_table:
4294 col._on_table_attach(_col_attached)
4295 return
4297 columns = cols_w_table
4299 tables = {c.table for c in columns}
4300 if len(tables) == 1:
4301 self._set_parent_with_dispatch(tables.pop())
4302 elif len(tables) > 1 and not self._allow_multiple_tables:
4303 table = columns[0].table
4304 others = [c for c in columns[1:] if c.table is not table]
4305 if others:
4306 # black could not format this inline
4307 other_str = ", ".join("'%s'" % c for c in others)
4308 raise exc.ArgumentError(
4309 f"Column(s) {other_str} "
4310 f"are not part of table '{table.description}'."
4311 )
4313 @util.ro_memoized_property
4314 def columns(self) -> ReadOnlyColumnCollection[str, Column[Any]]:
4315 return self._columns.as_readonly()
4317 @util.ro_memoized_property
4318 def c(self) -> ReadOnlyColumnCollection[str, Column[Any]]:
4319 return self._columns.as_readonly()
4321 def _col_expressions(
4322 self, parent: Union[Table, Column[Any]]
4323 ) -> List[Optional[Column[Any]]]:
4324 if isinstance(parent, Column):
4325 result: List[Optional[Column[Any]]] = [
4326 c for c in self._pending_colargs if isinstance(c, Column)
4327 ]
4328 assert len(result) == len(self._pending_colargs)
4329 return result
4330 else:
4331 try:
4332 return [
4333 parent.c[col] if isinstance(col, str) else col
4334 for col in self._pending_colargs
4335 ]
4336 except KeyError as ke:
4337 raise exc.ConstraintColumnNotFoundError(
4338 f"Can't create {self.__class__.__name__} "
4339 f"on table '{parent.description}': no column "
4340 f"named '{ke.args[0]}' is present."
4341 ) from ke
4343 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4344 assert isinstance(parent, (Table, Column))
4346 for col in self._col_expressions(parent):
4347 if col is not None:
4348 self._columns.add(col)
4351class ColumnCollectionConstraint(ColumnCollectionMixin, Constraint):
4352 """A constraint that proxies a ColumnCollection."""
4354 def __init__(
4355 self,
4356 *columns: _DDLColumnArgument,
4357 name: _ConstraintNameArgument = None,
4358 deferrable: Optional[bool] = None,
4359 initially: Optional[str] = None,
4360 info: Optional[_InfoType] = None,
4361 _autoattach: bool = True,
4362 _column_flag: bool = False,
4363 _gather_expressions: Optional[List[_DDLColumnArgument]] = None,
4364 **dialect_kw: Any,
4365 ) -> None:
4366 r"""
4367 :param \*columns:
4368 A sequence of column names or Column objects.
4370 :param name:
4371 Optional, the in-database name of this constraint.
4373 :param deferrable:
4374 Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
4375 issuing DDL for this constraint.
4377 :param initially:
4378 Optional string. If set, emit INITIALLY <value> when issuing DDL
4379 for this constraint.
4381 :param \**dialect_kw: other keyword arguments including
4382 dialect-specific arguments are propagated to the :class:`.Constraint`
4383 superclass.
4385 """
4386 Constraint.__init__(
4387 self,
4388 name=name,
4389 deferrable=deferrable,
4390 initially=initially,
4391 info=info,
4392 **dialect_kw,
4393 )
4394 ColumnCollectionMixin.__init__(
4395 self, *columns, _autoattach=_autoattach, _column_flag=_column_flag
4396 )
4398 columns: ReadOnlyColumnCollection[str, Column[Any]]
4399 """A :class:`_expression.ColumnCollection` representing the set of columns
4400 for this constraint.
4402 """
4404 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4405 assert isinstance(parent, (Column, Table))
4406 Constraint._set_parent(self, parent)
4407 ColumnCollectionMixin._set_parent(self, parent)
4409 def __contains__(self, x: Any) -> bool:
4410 return x in self._columns
4412 @util.deprecated(
4413 "1.4",
4414 "The :meth:`_schema.ColumnCollectionConstraint.copy` method "
4415 "is deprecated and will be removed in a future release.",
4416 )
4417 def copy(
4418 self,
4419 *,
4420 target_table: Optional[Table] = None,
4421 **kw: Any,
4422 ) -> ColumnCollectionConstraint:
4423 return self._copy(target_table=target_table, **kw)
4425 def _copy(
4426 self,
4427 *,
4428 target_table: Optional[Table] = None,
4429 **kw: Any,
4430 ) -> ColumnCollectionConstraint:
4431 # ticket #5276
4432 constraint_kwargs = {}
4433 for dialect_name in self.dialect_options:
4434 dialect_options = self.dialect_options[dialect_name]._non_defaults
4435 for (
4436 dialect_option_key,
4437 dialect_option_value,
4438 ) in dialect_options.items():
4439 constraint_kwargs[dialect_name + "_" + dialect_option_key] = (
4440 dialect_option_value
4441 )
4443 assert isinstance(self.parent, Table)
4444 c = self.__class__(
4445 name=self.name,
4446 deferrable=self.deferrable,
4447 initially=self.initially,
4448 *[
4449 _copy_expression(expr, self.parent, target_table)
4450 for expr in self._columns
4451 ],
4452 comment=self.comment,
4453 **constraint_kwargs,
4454 )
4455 return self._schema_item_copy(c)
4457 def contains_column(self, col: Column[Any]) -> bool:
4458 """Return True if this constraint contains the given column.
4460 Note that this object also contains an attribute ``.columns``
4461 which is a :class:`_expression.ColumnCollection` of
4462 :class:`_schema.Column` objects.
4464 """
4466 return self._columns.contains_column(col)
4468 def __iter__(self) -> Iterator[Column[Any]]:
4469 return iter(self._columns)
4471 def __len__(self) -> int:
4472 return len(self._columns)
4475class CheckConstraint(ColumnCollectionConstraint):
4476 """A table- or column-level CHECK constraint.
4478 Can be included in the definition of a Table or Column.
4479 """
4481 _allow_multiple_tables = True
4483 __visit_name__ = "table_or_column_check_constraint"
4485 @_document_text_coercion(
4486 "sqltext",
4487 ":class:`.CheckConstraint`",
4488 ":paramref:`.CheckConstraint.sqltext`",
4489 )
4490 def __init__(
4491 self,
4492 sqltext: _TextCoercedExpressionArgument[Any],
4493 name: _ConstraintNameArgument = None,
4494 deferrable: Optional[bool] = None,
4495 initially: Optional[str] = None,
4496 table: Optional[Table] = None,
4497 info: Optional[_InfoType] = None,
4498 _create_rule: Optional[Any] = None,
4499 _autoattach: bool = True,
4500 _type_bound: bool = False,
4501 **dialect_kw: Any,
4502 ) -> None:
4503 r"""Construct a CHECK constraint.
4505 :param sqltext:
4506 A string containing the constraint definition, which will be used
4507 verbatim, or a SQL expression construct. If given as a string,
4508 the object is converted to a :func:`_expression.text` object.
4509 If the textual
4510 string includes a colon character, escape this using a backslash::
4512 CheckConstraint(r"foo ~ E'a(?\:b|c)d")
4514 :param name:
4515 Optional, the in-database name of the constraint.
4517 :param deferrable:
4518 Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
4519 issuing DDL for this constraint.
4521 :param initially:
4522 Optional string. If set, emit INITIALLY <value> when issuing DDL
4523 for this constraint.
4525 :param info: Optional data dictionary which will be populated into the
4526 :attr:`.SchemaItem.info` attribute of this object.
4528 """
4530 self.sqltext = coercions.expect(roles.DDLExpressionRole, sqltext)
4531 columns: List[Column[Any]] = []
4532 visitors.traverse(self.sqltext, {}, {"column": columns.append})
4534 super().__init__(
4535 name=name,
4536 deferrable=deferrable,
4537 initially=initially,
4538 _create_rule=_create_rule,
4539 info=info,
4540 _type_bound=_type_bound,
4541 _autoattach=_autoattach,
4542 *columns,
4543 **dialect_kw,
4544 )
4545 if table is not None:
4546 self._set_parent_with_dispatch(table)
4548 @property
4549 def is_column_level(self) -> bool:
4550 return not isinstance(self.parent, Table)
4552 @util.deprecated(
4553 "1.4",
4554 "The :meth:`_schema.CheckConstraint.copy` method is deprecated "
4555 "and will be removed in a future release.",
4556 )
4557 def copy(
4558 self, *, target_table: Optional[Table] = None, **kw: Any
4559 ) -> CheckConstraint:
4560 return self._copy(target_table=target_table, **kw)
4562 def _copy(
4563 self, *, target_table: Optional[Table] = None, **kw: Any
4564 ) -> CheckConstraint:
4565 if target_table is not None:
4566 # note that target_table is None for the copy process of
4567 # a column-bound CheckConstraint, so this path is not reached
4568 # in that case.
4569 sqltext = _copy_expression(self.sqltext, self.table, target_table)
4570 else:
4571 sqltext = self.sqltext
4572 c = CheckConstraint(
4573 sqltext,
4574 name=self.name,
4575 initially=self.initially,
4576 deferrable=self.deferrable,
4577 _create_rule=self._create_rule,
4578 table=target_table,
4579 comment=self.comment,
4580 _autoattach=False,
4581 _type_bound=self._type_bound,
4582 )
4583 return self._schema_item_copy(c)
4586class ForeignKeyConstraint(ColumnCollectionConstraint):
4587 """A table-level FOREIGN KEY constraint.
4589 Defines a single column or composite FOREIGN KEY ... REFERENCES
4590 constraint. For a no-frills, single column foreign key, adding a
4591 :class:`_schema.ForeignKey` to the definition of a :class:`_schema.Column`
4592 is a
4593 shorthand equivalent for an unnamed, single column
4594 :class:`_schema.ForeignKeyConstraint`.
4596 Examples of foreign key configuration are in :ref:`metadata_foreignkeys`.
4598 """
4600 __visit_name__ = "foreign_key_constraint"
4602 def __init__(
4603 self,
4604 columns: _typing_Sequence[_DDLColumnArgument],
4605 refcolumns: _typing_Sequence[_DDLColumnArgument],
4606 name: _ConstraintNameArgument = None,
4607 onupdate: Optional[str] = None,
4608 ondelete: Optional[str] = None,
4609 deferrable: Optional[bool] = None,
4610 initially: Optional[str] = None,
4611 use_alter: bool = False,
4612 link_to_name: bool = False,
4613 match: Optional[str] = None,
4614 table: Optional[Table] = None,
4615 info: Optional[_InfoType] = None,
4616 comment: Optional[str] = None,
4617 **dialect_kw: Any,
4618 ) -> None:
4619 r"""Construct a composite-capable FOREIGN KEY.
4621 :param columns: A sequence of local column names. The named columns
4622 must be defined and present in the parent Table. The names should
4623 match the ``key`` given to each column (defaults to the name) unless
4624 ``link_to_name`` is True.
4626 :param refcolumns: A sequence of foreign column names or Column
4627 objects. The columns must all be located within the same Table.
4629 :param name: Optional, the in-database name of the key.
4631 :param onupdate: Optional string. If set, emit ON UPDATE <value> when
4632 issuing DDL for this constraint. Typical values include CASCADE,
4633 DELETE and RESTRICT.
4635 .. seealso::
4637 :ref:`on_update_on_delete`
4639 :param ondelete: Optional string. If set, emit ON DELETE <value> when
4640 issuing DDL for this constraint. Typical values include CASCADE,
4641 SET NULL and RESTRICT. Some dialects may allow for additional
4642 syntaxes.
4644 .. seealso::
4646 :ref:`on_update_on_delete`
4648 :param deferrable: Optional bool. If set, emit DEFERRABLE or NOT
4649 DEFERRABLE when issuing DDL for this constraint.
4651 :param initially: Optional string. If set, emit INITIALLY <value> when
4652 issuing DDL for this constraint.
4654 :param link_to_name: if True, the string name given in ``column`` is
4655 the rendered name of the referenced column, not its locally assigned
4656 ``key``.
4658 :param use_alter: If True, do not emit the DDL for this constraint as
4659 part of the CREATE TABLE definition. Instead, generate it via an
4660 ALTER TABLE statement issued after the full collection of tables
4661 have been created, and drop it via an ALTER TABLE statement before
4662 the full collection of tables are dropped.
4664 The use of :paramref:`_schema.ForeignKeyConstraint.use_alter` is
4665 particularly geared towards the case where two or more tables
4666 are established within a mutually-dependent foreign key constraint
4667 relationship; however, the :meth:`_schema.MetaData.create_all` and
4668 :meth:`_schema.MetaData.drop_all`
4669 methods will perform this resolution
4670 automatically, so the flag is normally not needed.
4672 .. seealso::
4674 :ref:`use_alter`
4676 :param match: Optional string. If set, emit MATCH <value> when issuing
4677 DDL for this constraint. Typical values include SIMPLE, PARTIAL
4678 and FULL.
4680 :param info: Optional data dictionary which will be populated into the
4681 :attr:`.SchemaItem.info` attribute of this object.
4683 :param comment: Optional string that will render an SQL comment on
4684 foreign key constraint creation.
4686 .. versionadded:: 2.0
4688 :param \**dialect_kw: Additional keyword arguments are dialect
4689 specific, and passed in the form ``<dialectname>_<argname>``. See
4690 the documentation regarding an individual dialect at
4691 :ref:`dialect_toplevel` for detail on documented arguments.
4693 """
4695 Constraint.__init__(
4696 self,
4697 name=name,
4698 deferrable=deferrable,
4699 initially=initially,
4700 info=info,
4701 comment=comment,
4702 **dialect_kw,
4703 )
4704 self.onupdate = onupdate
4705 self.ondelete = ondelete
4706 self.link_to_name = link_to_name
4707 self.use_alter = use_alter
4708 self.match = match
4710 if len(set(columns)) != len(refcolumns):
4711 if len(set(columns)) != len(columns):
4712 # e.g. FOREIGN KEY (a, a) REFERENCES r (b, c)
4713 raise exc.ArgumentError(
4714 "ForeignKeyConstraint with duplicate source column "
4715 "references are not supported."
4716 )
4717 else:
4718 # e.g. FOREIGN KEY (a) REFERENCES r (b, c)
4719 # paraphrasing
4720 # https://www.postgresql.org/docs/current/static/ddl-constraints.html
4721 raise exc.ArgumentError(
4722 "ForeignKeyConstraint number "
4723 "of constrained columns must match the number of "
4724 "referenced columns."
4725 )
4727 # standalone ForeignKeyConstraint - create
4728 # associated ForeignKey objects which will be applied to hosted
4729 # Column objects (in col.foreign_keys), either now or when attached
4730 # to the Table for string-specified names
4731 self.elements = [
4732 ForeignKey(
4733 refcol,
4734 _constraint=self,
4735 name=self.name,
4736 onupdate=self.onupdate,
4737 ondelete=self.ondelete,
4738 use_alter=self.use_alter,
4739 link_to_name=self.link_to_name,
4740 match=self.match,
4741 deferrable=self.deferrable,
4742 initially=self.initially,
4743 **self.dialect_kwargs,
4744 )
4745 for refcol in refcolumns
4746 ]
4748 ColumnCollectionMixin.__init__(self, *columns)
4749 if table is not None:
4750 if hasattr(self, "parent"):
4751 assert table is self.parent
4752 self._set_parent_with_dispatch(table)
4754 def _append_element(self, column: Column[Any], fk: ForeignKey) -> None:
4755 self._columns.add(column)
4756 self.elements.append(fk)
4758 columns: ReadOnlyColumnCollection[str, Column[Any]]
4759 """A :class:`_expression.ColumnCollection` representing the set of columns
4760 for this constraint.
4762 """
4764 elements: List[ForeignKey]
4765 """A sequence of :class:`_schema.ForeignKey` objects.
4767 Each :class:`_schema.ForeignKey`
4768 represents a single referring column/referred
4769 column pair.
4771 This collection is intended to be read-only.
4773 """
4775 @property
4776 def _elements(self) -> util.OrderedDict[str, ForeignKey]:
4777 # legacy - provide a dictionary view of (column_key, fk)
4778 return util.OrderedDict(zip(self.column_keys, self.elements))
4780 @property
4781 def _referred_schema(self) -> Optional[str]:
4782 for elem in self.elements:
4783 return elem._referred_schema
4784 else:
4785 return None
4787 @property
4788 def referred_table(self) -> Table:
4789 """The :class:`_schema.Table` object to which this
4790 :class:`_schema.ForeignKeyConstraint` references.
4792 This is a dynamically calculated attribute which may not be available
4793 if the constraint and/or parent table is not yet associated with
4794 a metadata collection that contains the referred table.
4796 """
4797 return self.elements[0].column.table
4799 def _validate_dest_table(self, table: Table) -> None:
4800 table_keys = {elem._table_key() for elem in self.elements}
4801 if None not in table_keys and len(table_keys) > 1:
4802 elem0, elem1 = sorted(table_keys)[0:2]
4803 raise exc.ArgumentError(
4804 f"ForeignKeyConstraint on "
4805 f"{table.fullname}({self._col_description}) refers to "
4806 f"multiple remote tables: {elem0} and {elem1}"
4807 )
4809 @property
4810 def column_keys(self) -> _typing_Sequence[str]:
4811 """Return a list of string keys representing the local
4812 columns in this :class:`_schema.ForeignKeyConstraint`.
4814 This list is either the original string arguments sent
4815 to the constructor of the :class:`_schema.ForeignKeyConstraint`,
4816 or if the constraint has been initialized with :class:`_schema.Column`
4817 objects, is the string ``.key`` of each element.
4819 """
4820 if hasattr(self, "parent"):
4821 return self._columns.keys()
4822 else:
4823 return [
4824 col.key if isinstance(col, ColumnElement) else str(col)
4825 for col in self._pending_colargs
4826 ]
4828 @property
4829 def _col_description(self) -> str:
4830 return ", ".join(self.column_keys)
4832 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4833 table = parent
4834 assert isinstance(table, Table)
4835 Constraint._set_parent(self, table)
4837 ColumnCollectionConstraint._set_parent(self, table)
4839 for col, fk in zip(self._columns, self.elements):
4840 if not hasattr(fk, "parent") or fk.parent is not col:
4841 fk._set_parent_with_dispatch(col)
4843 self._validate_dest_table(table)
4845 @util.deprecated(
4846 "1.4",
4847 "The :meth:`_schema.ForeignKeyConstraint.copy` method is deprecated "
4848 "and will be removed in a future release.",
4849 )
4850 def copy(
4851 self,
4852 *,
4853 schema: Optional[str] = None,
4854 target_table: Optional[Table] = None,
4855 **kw: Any,
4856 ) -> ForeignKeyConstraint:
4857 return self._copy(schema=schema, target_table=target_table, **kw)
4859 def _copy(
4860 self,
4861 *,
4862 schema: Optional[str] = None,
4863 target_table: Optional[Table] = None,
4864 **kw: Any,
4865 ) -> ForeignKeyConstraint:
4866 fkc = ForeignKeyConstraint(
4867 [x.parent.key for x in self.elements],
4868 [
4869 x._get_colspec(
4870 schema=schema,
4871 table_name=(
4872 target_table.name
4873 if target_table is not None
4874 and x._table_key() == x.parent.table.key
4875 else None
4876 ),
4877 _is_copy=True,
4878 )
4879 for x in self.elements
4880 ],
4881 name=self.name,
4882 onupdate=self.onupdate,
4883 ondelete=self.ondelete,
4884 use_alter=self.use_alter,
4885 deferrable=self.deferrable,
4886 initially=self.initially,
4887 link_to_name=self.link_to_name,
4888 match=self.match,
4889 comment=self.comment,
4890 )
4891 for self_fk, other_fk in zip(self.elements, fkc.elements):
4892 self_fk._schema_item_copy(other_fk)
4893 return self._schema_item_copy(fkc)
4896class PrimaryKeyConstraint(ColumnCollectionConstraint):
4897 """A table-level PRIMARY KEY constraint.
4899 The :class:`.PrimaryKeyConstraint` object is present automatically
4900 on any :class:`_schema.Table` object; it is assigned a set of
4901 :class:`_schema.Column` objects corresponding to those marked with
4902 the :paramref:`_schema.Column.primary_key` flag::
4904 >>> my_table = Table(
4905 ... "mytable",
4906 ... metadata,
4907 ... Column("id", Integer, primary_key=True),
4908 ... Column("version_id", Integer, primary_key=True),
4909 ... Column("data", String(50)),
4910 ... )
4911 >>> my_table.primary_key
4912 PrimaryKeyConstraint(
4913 Column('id', Integer(), table=<mytable>,
4914 primary_key=True, nullable=False),
4915 Column('version_id', Integer(), table=<mytable>,
4916 primary_key=True, nullable=False)
4917 )
4919 The primary key of a :class:`_schema.Table` can also be specified by using
4920 a :class:`.PrimaryKeyConstraint` object explicitly; in this mode of usage,
4921 the "name" of the constraint can also be specified, as well as other
4922 options which may be recognized by dialects::
4924 my_table = Table(
4925 "mytable",
4926 metadata,
4927 Column("id", Integer),
4928 Column("version_id", Integer),
4929 Column("data", String(50)),
4930 PrimaryKeyConstraint("id", "version_id", name="mytable_pk"),
4931 )
4933 The two styles of column-specification should generally not be mixed.
4934 An warning is emitted if the columns present in the
4935 :class:`.PrimaryKeyConstraint`
4936 don't match the columns that were marked as ``primary_key=True``, if both
4937 are present; in this case, the columns are taken strictly from the
4938 :class:`.PrimaryKeyConstraint` declaration, and those columns otherwise
4939 marked as ``primary_key=True`` are ignored. This behavior is intended to
4940 be backwards compatible with previous behavior.
4942 For the use case where specific options are to be specified on the
4943 :class:`.PrimaryKeyConstraint`, but the usual style of using
4944 ``primary_key=True`` flags is still desirable, an empty
4945 :class:`.PrimaryKeyConstraint` may be specified, which will take on the
4946 primary key column collection from the :class:`_schema.Table` based on the
4947 flags::
4949 my_table = Table(
4950 "mytable",
4951 metadata,
4952 Column("id", Integer, primary_key=True),
4953 Column("version_id", Integer, primary_key=True),
4954 Column("data", String(50)),
4955 PrimaryKeyConstraint(name="mytable_pk", mssql_clustered=True),
4956 )
4958 """
4960 __visit_name__ = "primary_key_constraint"
4962 def __init__(
4963 self,
4964 *columns: _DDLColumnArgument,
4965 name: Optional[str] = None,
4966 deferrable: Optional[bool] = None,
4967 initially: Optional[str] = None,
4968 info: Optional[_InfoType] = None,
4969 _implicit_generated: bool = False,
4970 **dialect_kw: Any,
4971 ) -> None:
4972 self._implicit_generated = _implicit_generated
4973 super().__init__(
4974 *columns,
4975 name=name,
4976 deferrable=deferrable,
4977 initially=initially,
4978 info=info,
4979 **dialect_kw,
4980 )
4982 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4983 table = parent
4984 assert isinstance(table, Table)
4985 super()._set_parent(table)
4987 if table.primary_key is not self:
4988 table.constraints.discard(table.primary_key)
4989 table.primary_key = self # type: ignore
4990 table.constraints.add(self)
4992 table_pks = [c for c in table.c if c.primary_key]
4993 if (
4994 self._columns
4995 and table_pks
4996 and set(table_pks) != set(self._columns)
4997 ):
4998 # black could not format these inline
4999 table_pk_str = ", ".join("'%s'" % c.name for c in table_pks)
5000 col_str = ", ".join("'%s'" % c.name for c in self._columns)
5002 util.warn(
5003 f"Table '{table.name}' specifies columns "
5004 f"{table_pk_str} as "
5005 f"primary_key=True, "
5006 f"not matching locally specified columns {col_str}; "
5007 f"setting the "
5008 f"current primary key columns to "
5009 f"{col_str}. "
5010 f"This warning "
5011 f"may become an exception in a future release"
5012 )
5013 table_pks[:] = []
5015 for c in self._columns:
5016 c.primary_key = True
5017 if c._user_defined_nullable is NULL_UNSPECIFIED:
5018 c.nullable = False
5019 if table_pks:
5020 self._columns.extend(table_pks)
5022 def _reload(self, columns: Iterable[Column[Any]]) -> None:
5023 """repopulate this :class:`.PrimaryKeyConstraint` given
5024 a set of columns.
5026 Existing columns in the table that are marked as primary_key=True
5027 are maintained.
5029 Also fires a new event.
5031 This is basically like putting a whole new
5032 :class:`.PrimaryKeyConstraint` object on the parent
5033 :class:`_schema.Table` object without actually replacing the object.
5035 The ordering of the given list of columns is also maintained; these
5036 columns will be appended to the list of columns after any which
5037 are already present.
5039 """
5040 # set the primary key flag on new columns.
5041 # note any existing PK cols on the table also have their
5042 # flag still set.
5043 for col in columns:
5044 col.primary_key = True
5046 self._columns.extend(columns)
5048 PrimaryKeyConstraint._autoincrement_column._reset(self) # type: ignore
5049 self._set_parent_with_dispatch(self.table)
5051 def _replace(self, col: Column[Any]) -> None:
5052 PrimaryKeyConstraint._autoincrement_column._reset(self) # type: ignore
5053 self._columns.replace(col)
5055 self.dispatch._sa_event_column_added_to_pk_constraint(self, col)
5057 @property
5058 def columns_autoinc_first(self) -> List[Column[Any]]:
5059 autoinc = self._autoincrement_column
5061 if autoinc is not None:
5062 return [autoinc] + [c for c in self._columns if c is not autoinc]
5063 else:
5064 return list(self._columns)
5066 @util.ro_memoized_property
5067 def _autoincrement_column(self) -> Optional[Column[int]]:
5068 def _validate_autoinc(col: Column[Any], autoinc_true: bool) -> bool:
5069 if col.type._type_affinity is None or not issubclass(
5070 col.type._type_affinity,
5071 (
5072 type_api.INTEGERTYPE._type_affinity,
5073 type_api.NUMERICTYPE._type_affinity,
5074 ),
5075 ):
5076 if autoinc_true:
5077 raise exc.ArgumentError(
5078 f"Column type {col.type} on column '{col}' is not "
5079 f"compatible with autoincrement=True"
5080 )
5081 else:
5082 return False
5083 elif (
5084 not isinstance(col.default, (type(None), Sequence))
5085 and not autoinc_true
5086 ):
5087 return False
5088 elif (
5089 col.server_default is not None
5090 and not isinstance(col.server_default, Identity)
5091 and not autoinc_true
5092 ):
5093 return False
5094 elif col.foreign_keys and col.autoincrement not in (
5095 True,
5096 "ignore_fk",
5097 ):
5098 return False
5099 return True
5101 if len(self._columns) == 1:
5102 col = list(self._columns)[0]
5104 if col.autoincrement is True:
5105 _validate_autoinc(col, True)
5106 return col
5107 elif col.autoincrement in (
5108 "auto",
5109 "ignore_fk",
5110 ) and _validate_autoinc(col, False):
5111 return col
5112 else:
5113 return None
5115 else:
5116 autoinc = None
5117 for col in self._columns:
5118 if col.autoincrement is True:
5119 _validate_autoinc(col, True)
5120 if autoinc is not None:
5121 raise exc.ArgumentError(
5122 f"Only one Column may be marked "
5123 f"autoincrement=True, found both "
5124 f"{col.name} and {autoinc.name}."
5125 )
5126 else:
5127 autoinc = col
5129 return autoinc
5132class UniqueConstraint(ColumnCollectionConstraint):
5133 """A table-level UNIQUE constraint.
5135 Defines a single column or composite UNIQUE constraint. For a no-frills,
5136 single column constraint, adding ``unique=True`` to the ``Column``
5137 definition is a shorthand equivalent for an unnamed, single column
5138 UniqueConstraint.
5139 """
5141 __visit_name__ = "unique_constraint"
5144class Index(
5145 DialectKWArgs, ColumnCollectionMixin, HasConditionalDDL, SchemaItem
5146):
5147 """A table-level INDEX.
5149 Defines a composite (one or more column) INDEX.
5151 E.g.::
5153 sometable = Table(
5154 "sometable",
5155 metadata,
5156 Column("name", String(50)),
5157 Column("address", String(100)),
5158 )
5160 Index("some_index", sometable.c.name)
5162 For a no-frills, single column index, adding
5163 :class:`_schema.Column` also supports ``index=True``::
5165 sometable = Table(
5166 "sometable", metadata, Column("name", String(50), index=True)
5167 )
5169 For a composite index, multiple columns can be specified::
5171 Index("some_index", sometable.c.name, sometable.c.address)
5173 Functional indexes are supported as well, typically by using the
5174 :data:`.func` construct in conjunction with table-bound
5175 :class:`_schema.Column` objects::
5177 Index("some_index", func.lower(sometable.c.name))
5179 An :class:`.Index` can also be manually associated with a
5180 :class:`_schema.Table`,
5181 either through inline declaration or using
5182 :meth:`_schema.Table.append_constraint`. When this approach is used,
5183 the names
5184 of the indexed columns can be specified as strings::
5186 Table(
5187 "sometable",
5188 metadata,
5189 Column("name", String(50)),
5190 Column("address", String(100)),
5191 Index("some_index", "name", "address"),
5192 )
5194 To support functional or expression-based indexes in this form, the
5195 :func:`_expression.text` construct may be used::
5197 from sqlalchemy import text
5199 Table(
5200 "sometable",
5201 metadata,
5202 Column("name", String(50)),
5203 Column("address", String(100)),
5204 Index("some_index", text("lower(name)")),
5205 )
5207 .. seealso::
5209 :ref:`schema_indexes` - General information on :class:`.Index`.
5211 :ref:`postgresql_indexes` - PostgreSQL-specific options available for
5212 the :class:`.Index` construct.
5214 :ref:`mysql_indexes` - MySQL-specific options available for the
5215 :class:`.Index` construct.
5217 :ref:`mssql_indexes` - MSSQL-specific options available for the
5218 :class:`.Index` construct.
5220 """
5222 __visit_name__ = "index"
5224 table: Optional[Table]
5225 expressions: _typing_Sequence[Union[str, ColumnElement[Any]]]
5226 _table_bound_expressions: _typing_Sequence[ColumnElement[Any]]
5228 def __init__(
5229 self,
5230 name: Optional[str],
5231 *expressions: _DDLColumnArgument,
5232 unique: bool = False,
5233 quote: Optional[bool] = None,
5234 info: Optional[_InfoType] = None,
5235 _table: Optional[Table] = None,
5236 _column_flag: bool = False,
5237 **dialect_kw: Any,
5238 ) -> None:
5239 r"""Construct an index object.
5241 :param name:
5242 The name of the index
5244 :param \*expressions:
5245 Column expressions to include in the index. The expressions
5246 are normally instances of :class:`_schema.Column`, but may also
5247 be arbitrary SQL expressions which ultimately refer to a
5248 :class:`_schema.Column`.
5250 :param unique=False:
5251 Keyword only argument; if True, create a unique index.
5253 :param quote=None:
5254 Keyword only argument; whether to apply quoting to the name of
5255 the index. Works in the same manner as that of
5256 :paramref:`_schema.Column.quote`.
5258 :param info=None: Optional data dictionary which will be populated
5259 into the :attr:`.SchemaItem.info` attribute of this object.
5261 :param \**dialect_kw: Additional keyword arguments not mentioned above
5262 are dialect specific, and passed in the form
5263 ``<dialectname>_<argname>``. See the documentation regarding an
5264 individual dialect at :ref:`dialect_toplevel` for detail on
5265 documented arguments.
5267 """
5268 self.table = table = None
5270 self.name = quoted_name.construct(name, quote)
5271 self.unique = unique
5272 if info is not None:
5273 self.info = info
5275 # TODO: consider "table" argument being public, but for
5276 # the purpose of the fix here, it starts as private.
5277 if _table is not None:
5278 table = _table
5280 self._validate_dialect_kwargs(dialect_kw)
5282 self.expressions = []
5283 # will call _set_parent() if table-bound column
5284 # objects are present
5285 ColumnCollectionMixin.__init__(
5286 self,
5287 *expressions,
5288 _column_flag=_column_flag,
5289 _gather_expressions=self.expressions,
5290 )
5291 if table is not None:
5292 self._set_parent(table)
5294 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
5295 table = parent
5296 assert isinstance(table, Table)
5297 ColumnCollectionMixin._set_parent(self, table)
5299 if self.table is not None and table is not self.table:
5300 raise exc.ArgumentError(
5301 f"Index '{self.name}' is against table "
5302 f"'{self.table.description}', and "
5303 f"cannot be associated with table '{table.description}'."
5304 )
5305 self.table = table
5306 table.indexes.add(self)
5308 expressions = self.expressions
5309 col_expressions = self._col_expressions(table)
5310 assert len(expressions) == len(col_expressions)
5312 exprs = []
5313 for expr, colexpr in zip(expressions, col_expressions):
5314 if isinstance(expr, ClauseElement):
5315 exprs.append(expr)
5316 elif colexpr is not None:
5317 exprs.append(colexpr)
5318 else:
5319 assert False
5320 self.expressions = self._table_bound_expressions = exprs
5322 def create(self, bind: _CreateDropBind, checkfirst: bool = False) -> None:
5323 """Issue a ``CREATE`` statement for this
5324 :class:`.Index`, using the given
5325 :class:`.Connection` or :class:`.Engine`` for connectivity.
5327 .. seealso::
5329 :meth:`_schema.MetaData.create_all`.
5331 """
5332 bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
5334 def drop(self, bind: _CreateDropBind, checkfirst: bool = False) -> None:
5335 """Issue a ``DROP`` statement for this
5336 :class:`.Index`, using the given
5337 :class:`.Connection` or :class:`.Engine` for connectivity.
5339 .. seealso::
5341 :meth:`_schema.MetaData.drop_all`.
5343 """
5344 bind._run_ddl_visitor(ddl.SchemaDropper, self, checkfirst=checkfirst)
5346 def __repr__(self) -> str:
5347 exprs: _typing_Sequence[Any] # noqa: F842
5349 return "Index(%s)" % (
5350 ", ".join(
5351 [repr(self.name)]
5352 + [repr(e) for e in self.expressions]
5353 + (self.unique and ["unique=True"] or [])
5354 )
5355 )
5358_NamingSchemaCallable = Union[
5359 Callable[[Constraint, Table], str],
5360 Callable[[Index, Table], str],
5361]
5362_NamingSchemaDirective = Union[str, _NamingSchemaCallable]
5365class _NamingSchemaTD(TypedDict, total=False):
5366 fk: _NamingSchemaDirective
5367 pk: _NamingSchemaDirective
5368 ix: _NamingSchemaDirective
5369 ck: _NamingSchemaDirective
5370 uq: _NamingSchemaDirective
5373_NamingSchemaParameter = Union[
5374 # it seems like the TypedDict here is useful for pylance typeahead,
5375 # and not much else
5376 _NamingSchemaTD,
5377 # there is no form that allows Union[Type[Any], str] to work in all
5378 # cases, including breaking out Mapping[] entries for each combination
5379 # even, therefore keys must be `Any` (see #10264)
5380 Mapping[Any, _NamingSchemaDirective],
5381]
5384DEFAULT_NAMING_CONVENTION: _NamingSchemaParameter = util.immutabledict(
5385 {"ix": "ix_%(column_0_label)s"}
5386)
5389class MetaData(HasSchemaAttr):
5390 """A collection of :class:`_schema.Table`
5391 objects and their associated schema
5392 constructs.
5394 Holds a collection of :class:`_schema.Table` objects as well as
5395 an optional binding to an :class:`_engine.Engine` or
5396 :class:`_engine.Connection`. If bound, the :class:`_schema.Table` objects
5397 in the collection and their columns may participate in implicit SQL
5398 execution.
5400 The :class:`_schema.Table` objects themselves are stored in the
5401 :attr:`_schema.MetaData.tables` dictionary.
5403 :class:`_schema.MetaData` is a thread-safe object for read operations.
5404 Construction of new tables within a single :class:`_schema.MetaData`
5405 object,
5406 either explicitly or via reflection, may not be completely thread-safe.
5408 .. seealso::
5410 :ref:`metadata_describing` - Introduction to database metadata
5412 """
5414 __visit_name__ = "metadata"
5416 def __init__(
5417 self,
5418 schema: Optional[str] = None,
5419 quote_schema: Optional[bool] = None,
5420 naming_convention: Optional[_NamingSchemaParameter] = None,
5421 info: Optional[_InfoType] = None,
5422 ) -> None:
5423 """Create a new MetaData object.
5425 :param schema:
5426 The default schema to use for the :class:`_schema.Table`,
5427 :class:`.Sequence`, and potentially other objects associated with
5428 this :class:`_schema.MetaData`. Defaults to ``None``.
5430 .. seealso::
5432 :ref:`schema_metadata_schema_name` - details on how the
5433 :paramref:`_schema.MetaData.schema` parameter is used.
5435 :paramref:`_schema.Table.schema`
5437 :paramref:`.Sequence.schema`
5439 :param quote_schema:
5440 Sets the ``quote_schema`` flag for those :class:`_schema.Table`,
5441 :class:`.Sequence`, and other objects which make usage of the
5442 local ``schema`` name.
5444 :param info: Optional data dictionary which will be populated into the
5445 :attr:`.SchemaItem.info` attribute of this object.
5447 :param naming_convention: a dictionary referring to values which
5448 will establish default naming conventions for :class:`.Constraint`
5449 and :class:`.Index` objects, for those objects which are not given
5450 a name explicitly.
5452 The keys of this dictionary may be:
5454 * a constraint or Index class, e.g. the :class:`.UniqueConstraint`,
5455 :class:`_schema.ForeignKeyConstraint` class, the :class:`.Index`
5456 class
5458 * a string mnemonic for one of the known constraint classes;
5459 ``"fk"``, ``"pk"``, ``"ix"``, ``"ck"``, ``"uq"`` for foreign key,
5460 primary key, index, check, and unique constraint, respectively.
5462 * the string name of a user-defined "token" that can be used
5463 to define new naming tokens.
5465 The values associated with each "constraint class" or "constraint
5466 mnemonic" key are string naming templates, such as
5467 ``"uq_%(table_name)s_%(column_0_name)s"``,
5468 which describe how the name should be composed. The values
5469 associated with user-defined "token" keys should be callables of the
5470 form ``fn(constraint, table)``, which accepts the constraint/index
5471 object and :class:`_schema.Table` as arguments, returning a string
5472 result.
5474 The built-in names are as follows, some of which may only be
5475 available for certain types of constraint:
5477 * ``%(table_name)s`` - the name of the :class:`_schema.Table`
5478 object
5479 associated with the constraint.
5481 * ``%(referred_table_name)s`` - the name of the
5482 :class:`_schema.Table`
5483 object associated with the referencing target of a
5484 :class:`_schema.ForeignKeyConstraint`.
5486 * ``%(column_0_name)s`` - the name of the :class:`_schema.Column`
5487 at
5488 index position "0" within the constraint.
5490 * ``%(column_0N_name)s`` - the name of all :class:`_schema.Column`
5491 objects in order within the constraint, joined without a
5492 separator.
5494 * ``%(column_0_N_name)s`` - the name of all
5495 :class:`_schema.Column`
5496 objects in order within the constraint, joined with an
5497 underscore as a separator.
5499 * ``%(column_0_label)s``, ``%(column_0N_label)s``,
5500 ``%(column_0_N_label)s`` - the label of either the zeroth
5501 :class:`_schema.Column` or all :class:`.Columns`, separated with
5502 or without an underscore
5504 * ``%(column_0_key)s``, ``%(column_0N_key)s``,
5505 ``%(column_0_N_key)s`` - the key of either the zeroth
5506 :class:`_schema.Column` or all :class:`.Columns`, separated with
5507 or without an underscore
5509 * ``%(referred_column_0_name)s``, ``%(referred_column_0N_name)s``
5510 ``%(referred_column_0_N_name)s``, ``%(referred_column_0_key)s``,
5511 ``%(referred_column_0N_key)s``, ... column tokens which
5512 render the names/keys/labels of columns that are referenced
5513 by a :class:`_schema.ForeignKeyConstraint`.
5515 * ``%(constraint_name)s`` - a special key that refers to the
5516 existing name given to the constraint. When this key is
5517 present, the :class:`.Constraint` object's existing name will be
5518 replaced with one that is composed from template string that
5519 uses this token. When this token is present, it is required that
5520 the :class:`.Constraint` is given an explicit name ahead of time.
5522 * user-defined: any additional token may be implemented by passing
5523 it along with a ``fn(constraint, table)`` callable to the
5524 naming_convention dictionary.
5526 .. versionadded:: 1.3.0 - added new ``%(column_0N_name)s``,
5527 ``%(column_0_N_name)s``, and related tokens that produce
5528 concatenations of names, keys, or labels for all columns referred
5529 to by a given constraint.
5531 .. seealso::
5533 :ref:`constraint_naming_conventions` - for detailed usage
5534 examples.
5536 """
5537 if schema is not None and not isinstance(schema, str):
5538 raise exc.ArgumentError(
5539 "expected schema argument to be a string, "
5540 f"got {type(schema)}."
5541 )
5542 self.tables = util.FacadeDict()
5543 self.schema = quoted_name.construct(schema, quote_schema)
5544 self.naming_convention = (
5545 naming_convention
5546 if naming_convention
5547 else DEFAULT_NAMING_CONVENTION
5548 )
5549 if info:
5550 self.info = info
5551 self._schemas: Set[str] = set()
5552 self._sequences: Dict[str, Sequence] = {}
5553 self._fk_memos: Dict[Tuple[str, Optional[str]], List[ForeignKey]] = (
5554 collections.defaultdict(list)
5555 )
5557 tables: util.FacadeDict[str, Table]
5558 """A dictionary of :class:`_schema.Table`
5559 objects keyed to their name or "table key".
5561 The exact key is that determined by the :attr:`_schema.Table.key`
5562 attribute;
5563 for a table with no :attr:`_schema.Table.schema` attribute,
5564 this is the same
5565 as :attr:`_schema.Table.name`. For a table with a schema,
5566 it is typically of the
5567 form ``schemaname.tablename``.
5569 .. seealso::
5571 :attr:`_schema.MetaData.sorted_tables`
5573 """
5575 def __repr__(self) -> str:
5576 return "MetaData()"
5578 def __contains__(self, table_or_key: Union[str, Table]) -> bool:
5579 if not isinstance(table_or_key, str):
5580 table_or_key = table_or_key.key
5581 return table_or_key in self.tables
5583 def _add_table(
5584 self, name: str, schema: Optional[str], table: Table
5585 ) -> None:
5586 key = _get_table_key(name, schema)
5587 self.tables._insert_item(key, table)
5588 if schema:
5589 self._schemas.add(schema)
5591 def _remove_table(self, name: str, schema: Optional[str]) -> None:
5592 key = _get_table_key(name, schema)
5593 removed = dict.pop(self.tables, key, None)
5594 if removed is not None:
5595 for fk in removed.foreign_keys:
5596 fk._remove_from_metadata(self)
5597 if self._schemas:
5598 self._schemas = {
5599 t.schema for t in self.tables.values() if t.schema is not None
5600 }
5602 def __getstate__(self) -> Dict[str, Any]:
5603 return {
5604 "tables": self.tables,
5605 "schema": self.schema,
5606 "schemas": self._schemas,
5607 "sequences": self._sequences,
5608 "fk_memos": self._fk_memos,
5609 "naming_convention": self.naming_convention,
5610 }
5612 def __setstate__(self, state: Dict[str, Any]) -> None:
5613 self.tables = state["tables"]
5614 self.schema = state["schema"]
5615 self.naming_convention = state["naming_convention"]
5616 self._sequences = state["sequences"]
5617 self._schemas = state["schemas"]
5618 self._fk_memos = state["fk_memos"]
5620 def clear(self) -> None:
5621 """Clear all Table objects from this MetaData."""
5623 dict.clear(self.tables)
5624 self._schemas.clear()
5625 self._fk_memos.clear()
5627 def remove(self, table: Table) -> None:
5628 """Remove the given Table object from this MetaData."""
5630 self._remove_table(table.name, table.schema)
5632 @property
5633 def sorted_tables(self) -> List[Table]:
5634 """Returns a list of :class:`_schema.Table` objects sorted in order of
5635 foreign key dependency.
5637 The sorting will place :class:`_schema.Table`
5638 objects that have dependencies
5639 first, before the dependencies themselves, representing the
5640 order in which they can be created. To get the order in which
5641 the tables would be dropped, use the ``reversed()`` Python built-in.
5643 .. warning::
5645 The :attr:`.MetaData.sorted_tables` attribute cannot by itself
5646 accommodate automatic resolution of dependency cycles between
5647 tables, which are usually caused by mutually dependent foreign key
5648 constraints. When these cycles are detected, the foreign keys
5649 of these tables are omitted from consideration in the sort.
5650 A warning is emitted when this condition occurs, which will be an
5651 exception raise in a future release. Tables which are not part
5652 of the cycle will still be returned in dependency order.
5654 To resolve these cycles, the
5655 :paramref:`_schema.ForeignKeyConstraint.use_alter` parameter may be
5656 applied to those constraints which create a cycle. Alternatively,
5657 the :func:`_schema.sort_tables_and_constraints` function will
5658 automatically return foreign key constraints in a separate
5659 collection when cycles are detected so that they may be applied
5660 to a schema separately.
5662 .. versionchanged:: 1.3.17 - a warning is emitted when
5663 :attr:`.MetaData.sorted_tables` cannot perform a proper sort
5664 due to cyclical dependencies. This will be an exception in a
5665 future release. Additionally, the sort will continue to return
5666 other tables not involved in the cycle in dependency order which
5667 was not the case previously.
5669 .. seealso::
5671 :func:`_schema.sort_tables`
5673 :func:`_schema.sort_tables_and_constraints`
5675 :attr:`_schema.MetaData.tables`
5677 :meth:`_reflection.Inspector.get_table_names`
5679 :meth:`_reflection.Inspector.get_sorted_table_and_fkc_names`
5682 """
5683 return ddl.sort_tables(
5684 sorted(self.tables.values(), key=lambda t: t.key) # type: ignore
5685 )
5687 # overload needed to work around mypy this mypy
5688 # https://github.com/python/mypy/issues/17093
5689 @overload
5690 def reflect(
5691 self,
5692 bind: Engine,
5693 schema: Optional[str] = ...,
5694 views: bool = ...,
5695 only: Union[
5696 _typing_Sequence[str], Callable[[str, MetaData], bool], None
5697 ] = ...,
5698 extend_existing: bool = ...,
5699 autoload_replace: bool = ...,
5700 resolve_fks: bool = ...,
5701 **dialect_kwargs: Any,
5702 ) -> None: ...
5704 @overload
5705 def reflect(
5706 self,
5707 bind: Connection,
5708 schema: Optional[str] = ...,
5709 views: bool = ...,
5710 only: Union[
5711 _typing_Sequence[str], Callable[[str, MetaData], bool], None
5712 ] = ...,
5713 extend_existing: bool = ...,
5714 autoload_replace: bool = ...,
5715 resolve_fks: bool = ...,
5716 **dialect_kwargs: Any,
5717 ) -> None: ...
5719 @util.preload_module("sqlalchemy.engine.reflection")
5720 def reflect(
5721 self,
5722 bind: Union[Engine, Connection],
5723 schema: Optional[str] = None,
5724 views: bool = False,
5725 only: Union[
5726 _typing_Sequence[str], Callable[[str, MetaData], bool], None
5727 ] = None,
5728 extend_existing: bool = False,
5729 autoload_replace: bool = True,
5730 resolve_fks: bool = True,
5731 **dialect_kwargs: Any,
5732 ) -> None:
5733 r"""Load all available table definitions from the database.
5735 Automatically creates ``Table`` entries in this ``MetaData`` for any
5736 table available in the database but not yet present in the
5737 ``MetaData``. May be called multiple times to pick up tables recently
5738 added to the database, however no special action is taken if a table
5739 in this ``MetaData`` no longer exists in the database.
5741 :param bind:
5742 A :class:`.Connection` or :class:`.Engine` used to access the
5743 database.
5745 :param schema:
5746 Optional, query and reflect tables from an alternate schema.
5747 If None, the schema associated with this :class:`_schema.MetaData`
5748 is used, if any.
5750 :param views:
5751 If True, also reflect views (materialized and plain).
5753 :param only:
5754 Optional. Load only a sub-set of available named tables. May be
5755 specified as a sequence of names or a callable.
5757 If a sequence of names is provided, only those tables will be
5758 reflected. An error is raised if a table is requested but not
5759 available. Named tables already present in this ``MetaData`` are
5760 ignored.
5762 If a callable is provided, it will be used as a boolean predicate to
5763 filter the list of potential table names. The callable is called
5764 with a table name and this ``MetaData`` instance as positional
5765 arguments and should return a true value for any table to reflect.
5767 :param extend_existing: Passed along to each :class:`_schema.Table` as
5768 :paramref:`_schema.Table.extend_existing`.
5770 :param autoload_replace: Passed along to each :class:`_schema.Table`
5771 as
5772 :paramref:`_schema.Table.autoload_replace`.
5774 :param resolve_fks: if True, reflect :class:`_schema.Table`
5775 objects linked
5776 to :class:`_schema.ForeignKey` objects located in each
5777 :class:`_schema.Table`.
5778 For :meth:`_schema.MetaData.reflect`,
5779 this has the effect of reflecting
5780 related tables that might otherwise not be in the list of tables
5781 being reflected, for example if the referenced table is in a
5782 different schema or is omitted via the
5783 :paramref:`.MetaData.reflect.only` parameter. When False,
5784 :class:`_schema.ForeignKey` objects are not followed to the
5785 :class:`_schema.Table`
5786 in which they link, however if the related table is also part of the
5787 list of tables that would be reflected in any case, the
5788 :class:`_schema.ForeignKey` object will still resolve to its related
5789 :class:`_schema.Table` after the :meth:`_schema.MetaData.reflect`
5790 operation is
5791 complete. Defaults to True.
5793 .. versionadded:: 1.3.0
5795 .. seealso::
5797 :paramref:`_schema.Table.resolve_fks`
5799 :param \**dialect_kwargs: Additional keyword arguments not mentioned
5800 above are dialect specific, and passed in the form
5801 ``<dialectname>_<argname>``. See the documentation regarding an
5802 individual dialect at :ref:`dialect_toplevel` for detail on
5803 documented arguments.
5805 .. seealso::
5807 :ref:`metadata_reflection_toplevel`
5809 :meth:`_events.DDLEvents.column_reflect` - Event used to customize
5810 the reflected columns. Usually used to generalize the types using
5811 :meth:`_types.TypeEngine.as_generic`
5813 :ref:`metadata_reflection_dbagnostic_types` - describes how to
5814 reflect tables using general types.
5816 """
5818 with inspection.inspect(bind)._inspection_context() as insp:
5819 reflect_opts: Any = {
5820 "autoload_with": insp,
5821 "extend_existing": extend_existing,
5822 "autoload_replace": autoload_replace,
5823 "resolve_fks": resolve_fks,
5824 "_extend_on": set(),
5825 }
5827 reflect_opts.update(dialect_kwargs)
5829 if schema is None:
5830 schema = self.schema
5832 if schema is not None:
5833 reflect_opts["schema"] = schema
5835 kind = util.preloaded.engine_reflection.ObjectKind.TABLE
5836 available: util.OrderedSet[str] = util.OrderedSet(
5837 insp.get_table_names(schema, **dialect_kwargs)
5838 )
5839 if views:
5840 kind = util.preloaded.engine_reflection.ObjectKind.ANY
5841 available.update(insp.get_view_names(schema, **dialect_kwargs))
5842 try:
5843 available.update(
5844 insp.get_materialized_view_names(
5845 schema, **dialect_kwargs
5846 )
5847 )
5848 except NotImplementedError:
5849 pass
5851 if schema is not None:
5852 available_w_schema: util.OrderedSet[str] = util.OrderedSet(
5853 [f"{schema}.{name}" for name in available]
5854 )
5855 else:
5856 available_w_schema = available
5858 current = set(self.tables)
5860 if only is None:
5861 load = [
5862 name
5863 for name, schname in zip(available, available_w_schema)
5864 if extend_existing or schname not in current
5865 ]
5866 elif callable(only):
5867 load = [
5868 name
5869 for name, schname in zip(available, available_w_schema)
5870 if (extend_existing or schname not in current)
5871 and only(name, self)
5872 ]
5873 else:
5874 missing = [name for name in only if name not in available]
5875 if missing:
5876 s = schema and (" schema '%s'" % schema) or ""
5877 missing_str = ", ".join(missing)
5878 raise exc.InvalidRequestError(
5879 f"Could not reflect: requested table(s) not available "
5880 f"in {bind.engine!r}{s}: ({missing_str})"
5881 )
5882 load = [
5883 name
5884 for name in only
5885 if extend_existing or name not in current
5886 ]
5887 # pass the available tables so the inspector can
5888 # choose to ignore the filter_names
5889 _reflect_info = insp._get_reflection_info(
5890 schema=schema,
5891 filter_names=load,
5892 available=available,
5893 kind=kind,
5894 scope=util.preloaded.engine_reflection.ObjectScope.ANY,
5895 **dialect_kwargs,
5896 )
5897 reflect_opts["_reflect_info"] = _reflect_info
5899 for name in load:
5900 try:
5901 Table(name, self, **reflect_opts)
5902 except exc.UnreflectableTableError as uerr:
5903 util.warn(f"Skipping table {name}: {uerr}")
5905 def create_all(
5906 self,
5907 bind: _CreateDropBind,
5908 tables: Optional[_typing_Sequence[Table]] = None,
5909 checkfirst: bool = True,
5910 ) -> None:
5911 """Create all tables stored in this metadata.
5913 Conditional by default, will not attempt to recreate tables already
5914 present in the target database.
5916 :param bind:
5917 A :class:`.Connection` or :class:`.Engine` used to access the
5918 database.
5920 :param tables:
5921 Optional list of ``Table`` objects, which is a subset of the total
5922 tables in the ``MetaData`` (others are ignored).
5924 :param checkfirst:
5925 Defaults to True, don't issue CREATEs for tables already present
5926 in the target database.
5928 """
5929 bind._run_ddl_visitor(
5930 ddl.SchemaGenerator, self, checkfirst=checkfirst, tables=tables
5931 )
5933 def drop_all(
5934 self,
5935 bind: _CreateDropBind,
5936 tables: Optional[_typing_Sequence[Table]] = None,
5937 checkfirst: bool = True,
5938 ) -> None:
5939 """Drop all tables stored in this metadata.
5941 Conditional by default, will not attempt to drop tables not present in
5942 the target database.
5944 :param bind:
5945 A :class:`.Connection` or :class:`.Engine` used to access the
5946 database.
5948 :param tables:
5949 Optional list of ``Table`` objects, which is a subset of the
5950 total tables in the ``MetaData`` (others are ignored).
5952 :param checkfirst:
5953 Defaults to True, only issue DROPs for tables confirmed to be
5954 present in the target database.
5956 """
5957 bind._run_ddl_visitor(
5958 ddl.SchemaDropper, self, checkfirst=checkfirst, tables=tables
5959 )
5962class Computed(FetchedValue, SchemaItem):
5963 """Defines a generated column, i.e. "GENERATED ALWAYS AS" syntax.
5965 The :class:`.Computed` construct is an inline construct added to the
5966 argument list of a :class:`_schema.Column` object::
5968 from sqlalchemy import Computed
5970 Table(
5971 "square",
5972 metadata_obj,
5973 Column("side", Float, nullable=False),
5974 Column("area", Float, Computed("side * side")),
5975 )
5977 See the linked documentation below for complete details.
5979 .. versionadded:: 1.3.11
5981 .. seealso::
5983 :ref:`computed_ddl`
5985 """
5987 __visit_name__ = "computed_column"
5989 column: Optional[Column[Any]]
5991 @_document_text_coercion(
5992 "sqltext", ":class:`.Computed`", ":paramref:`.Computed.sqltext`"
5993 )
5994 def __init__(
5995 self, sqltext: _DDLColumnArgument, persisted: Optional[bool] = None
5996 ) -> None:
5997 """Construct a GENERATED ALWAYS AS DDL construct to accompany a
5998 :class:`_schema.Column`.
6000 :param sqltext:
6001 A string containing the column generation expression, which will be
6002 used verbatim, or a SQL expression construct, such as a
6003 :func:`_expression.text`
6004 object. If given as a string, the object is converted to a
6005 :func:`_expression.text` object.
6007 :param persisted:
6008 Optional, controls how this column should be persisted by the
6009 database. Possible values are:
6011 * ``None``, the default, it will use the default persistence
6012 defined by the database.
6013 * ``True``, will render ``GENERATED ALWAYS AS ... STORED``, or the
6014 equivalent for the target database if supported.
6015 * ``False``, will render ``GENERATED ALWAYS AS ... VIRTUAL``, or
6016 the equivalent for the target database if supported.
6018 Specifying ``True`` or ``False`` may raise an error when the DDL
6019 is emitted to the target database if the database does not support
6020 that persistence option. Leaving this parameter at its default
6021 of ``None`` is guaranteed to succeed for all databases that support
6022 ``GENERATED ALWAYS AS``.
6024 """
6025 self.sqltext = coercions.expect(roles.DDLExpressionRole, sqltext)
6026 self.persisted = persisted
6027 self.column = None
6029 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
6030 assert isinstance(parent, Column)
6032 if not isinstance(
6033 parent.server_default, (type(None), Computed)
6034 ) or not isinstance(parent.server_onupdate, (type(None), Computed)):
6035 raise exc.ArgumentError(
6036 "A generated column cannot specify a server_default or a "
6037 "server_onupdate argument"
6038 )
6039 self.column = parent
6040 parent.computed = self
6041 self.column.server_onupdate = self
6042 self.column.server_default = self
6044 def _as_for_update(self, for_update: bool) -> FetchedValue:
6045 return self
6047 @util.deprecated(
6048 "1.4",
6049 "The :meth:`_schema.Computed.copy` method is deprecated "
6050 "and will be removed in a future release.",
6051 )
6052 def copy(
6053 self, *, target_table: Optional[Table] = None, **kw: Any
6054 ) -> Computed:
6055 return self._copy(target_table=target_table, **kw)
6057 def _copy(
6058 self, *, target_table: Optional[Table] = None, **kw: Any
6059 ) -> Computed:
6060 sqltext = _copy_expression(
6061 self.sqltext,
6062 self.column.table if self.column is not None else None,
6063 target_table,
6064 )
6065 g = Computed(sqltext, persisted=self.persisted)
6067 return self._schema_item_copy(g)
6070class Identity(IdentityOptions, FetchedValue, SchemaItem):
6071 """Defines an identity column, i.e. "GENERATED { ALWAYS | BY DEFAULT }
6072 AS IDENTITY" syntax.
6074 The :class:`.Identity` construct is an inline construct added to the
6075 argument list of a :class:`_schema.Column` object::
6077 from sqlalchemy import Identity
6079 Table(
6080 "foo",
6081 metadata_obj,
6082 Column("id", Integer, Identity()),
6083 Column("description", Text),
6084 )
6086 See the linked documentation below for complete details.
6088 .. versionadded:: 1.4
6090 .. seealso::
6092 :ref:`identity_ddl`
6094 """
6096 __visit_name__ = "identity_column"
6098 is_identity = True
6100 def __init__(
6101 self,
6102 always: bool = False,
6103 on_null: Optional[bool] = None,
6104 start: Optional[int] = None,
6105 increment: Optional[int] = None,
6106 minvalue: Optional[int] = None,
6107 maxvalue: Optional[int] = None,
6108 nominvalue: Optional[bool] = None,
6109 nomaxvalue: Optional[bool] = None,
6110 cycle: Optional[bool] = None,
6111 cache: Optional[int] = None,
6112 order: Optional[bool] = None,
6113 ) -> None:
6114 """Construct a GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY DDL
6115 construct to accompany a :class:`_schema.Column`.
6117 See the :class:`.Sequence` documentation for a complete description
6118 of most parameters.
6120 .. note::
6121 MSSQL supports this construct as the preferred alternative to
6122 generate an IDENTITY on a column, but it uses non standard
6123 syntax that only support :paramref:`_schema.Identity.start`
6124 and :paramref:`_schema.Identity.increment`.
6125 All other parameters are ignored.
6127 :param always:
6128 A boolean, that indicates the type of identity column.
6129 If ``False`` is specified, the default, then the user-specified
6130 value takes precedence.
6131 If ``True`` is specified, a user-specified value is not accepted (
6132 on some backends, like PostgreSQL, OVERRIDING SYSTEM VALUE, or
6133 similar, may be specified in an INSERT to override the sequence
6134 value).
6135 Some backends also have a default value for this parameter,
6136 ``None`` can be used to omit rendering this part in the DDL. It
6137 will be treated as ``False`` if a backend does not have a default
6138 value.
6140 :param on_null:
6141 Set to ``True`` to specify ON NULL in conjunction with a
6142 ``always=False`` identity column. This option is only supported on
6143 some backends, like Oracle Database.
6145 :param start: the starting index of the sequence.
6146 :param increment: the increment value of the sequence.
6147 :param minvalue: the minimum value of the sequence.
6148 :param maxvalue: the maximum value of the sequence.
6149 :param nominvalue: no minimum value of the sequence.
6150 :param nomaxvalue: no maximum value of the sequence.
6151 :param cycle: allows the sequence to wrap around when the maxvalue
6152 or minvalue has been reached.
6153 :param cache: optional integer value; number of future values in the
6154 sequence which are calculated in advance.
6155 :param order: optional boolean value; if true, renders the
6156 ORDER keyword.
6158 """
6159 IdentityOptions.__init__(
6160 self,
6161 start=start,
6162 increment=increment,
6163 minvalue=minvalue,
6164 maxvalue=maxvalue,
6165 nominvalue=nominvalue,
6166 nomaxvalue=nomaxvalue,
6167 cycle=cycle,
6168 cache=cache,
6169 order=order,
6170 )
6171 self.always = always
6172 self.on_null = on_null
6173 self.column = None
6175 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
6176 assert isinstance(parent, Column)
6177 if not isinstance(
6178 parent.server_default, (type(None), Identity)
6179 ) or not isinstance(parent.server_onupdate, type(None)):
6180 raise exc.ArgumentError(
6181 "A column with an Identity object cannot specify a "
6182 "server_default or a server_onupdate argument"
6183 )
6184 if parent.autoincrement is False:
6185 raise exc.ArgumentError(
6186 "A column with an Identity object cannot specify "
6187 "autoincrement=False"
6188 )
6189 self.column = parent
6191 parent.identity = self
6192 if parent._user_defined_nullable is NULL_UNSPECIFIED:
6193 parent.nullable = False
6195 parent.server_default = self
6197 def _as_for_update(self, for_update: bool) -> FetchedValue:
6198 return self
6200 @util.deprecated(
6201 "1.4",
6202 "The :meth:`_schema.Identity.copy` method is deprecated "
6203 "and will be removed in a future release.",
6204 )
6205 def copy(self, **kw: Any) -> Identity:
6206 return self._copy(**kw)
6208 def _copy(self, **kw: Any) -> Identity:
6209 i = Identity(
6210 always=self.always,
6211 on_null=self.on_null,
6212 start=self.start,
6213 increment=self.increment,
6214 minvalue=self.minvalue,
6215 maxvalue=self.maxvalue,
6216 nominvalue=self.nominvalue,
6217 nomaxvalue=self.nomaxvalue,
6218 cycle=self.cycle,
6219 cache=self.cache,
6220 order=self.order,
6221 )
6223 return self._schema_item_copy(i)