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"""
31from __future__ import annotations
33from abc import ABC
34import collections
35from enum import Enum
36import operator
37import typing
38from typing import Any
39from typing import Callable
40from typing import cast
41from typing import Collection
42from typing import Dict
43from typing import Final
44from typing import Iterable
45from typing import Iterator
46from typing import List
47from typing import Literal
48from typing import Mapping
49from typing import NoReturn
50from typing import Optional
51from typing import overload
52from typing import Protocol
53from typing import Sequence as _typing_Sequence
54from typing import Set
55from typing import Tuple
56from typing import Type
57from typing import TYPE_CHECKING
58from typing import TypedDict
59from typing import TypeGuard
60from typing import TypeVar
61from typing import Union
63from . import coercions
64from . import ddl
65from . import roles
66from . import type_api
67from . import visitors
68from ._annotated_cols import _ColCC_co
69from ._annotated_cols import _extract_columns_from_class
70from ._annotated_cols import _TC_co
71from ._annotated_cols import Named
72from ._annotated_cols import TypedColumns
73from ._typing import _T
74from .base import _DefaultDescriptionTuple
75from .base import _NoArg
76from .base import _NoneName
77from .base import _SentinelColumnCharacterization
78from .base import _SentinelDefaultCharacterization
79from .base import DedupeColumnCollection
80from .base import DialectKWArgs
81from .base import Executable
82from .base import SchemaEventTarget as SchemaEventTarget
83from .base import SchemaVisitable as SchemaVisitable
84from .coercions import _document_text_coercion
85from .ddl import CheckFirst
86from .elements import ClauseElement
87from .elements import ColumnClause
88from .elements import ColumnElement
89from .elements import quoted_name
90from .elements import TextClause
91from .selectable import TableClause
92from .type_api import to_instance
93from .visitors import ExternallyTraversible
94from .. import event
95from .. import exc
96from .. import inspection
97from .. import util
98from ..util import HasMemoized
99from ..util.typing import Self
101if typing.TYPE_CHECKING:
102 from ._typing import _AutoIncrementType
103 from ._typing import _CreateDropBind
104 from ._typing import _DDLColumnArgument
105 from ._typing import _DDLColumnReferenceArgument
106 from ._typing import _InfoType
107 from ._typing import _TextCoercedExpressionArgument
108 from ._typing import _TypeEngineArgument
109 from .base import ColumnSet
110 from .base import ReadOnlyColumnCollection
111 from .compiler import DDLCompiler
112 from .ddl import TableCreateDDL
113 from .ddl import TableDropDDL
114 from .elements import BindParameter
115 from .elements import KeyedColumnElement
116 from .functions import Function
117 from .sqltypes import SchemaType
118 from .type_api import TypeEngine
119 from .visitors import anon_map
120 from ..engine import Connection
121 from ..engine import Engine
122 from ..engine.interfaces import _CoreMultiExecuteParams
123 from ..engine.interfaces import CoreExecuteOptionsParameter
124 from ..engine.interfaces import ExecutionContext
125 from ..engine.reflection import _ReflectionInfo
126 from ..sql.selectable import FromClause
128_SI = TypeVar("_SI", bound="SchemaItem")
129_TAB = TypeVar("_TAB", bound="Table")
132_ConstraintNameArgument = Optional[Union[str, _NoneName]]
134_ServerDefaultArgument = Union[
135 "FetchedValue", str, TextClause, ColumnElement[Any]
136]
138_ServerOnUpdateArgument = _ServerDefaultArgument
141class SchemaConst(Enum):
142 RETAIN_SCHEMA = 1
143 """Symbol indicating that a :class:`_schema.Table`, :class:`.Sequence`
144 or in some cases a :class:`_schema.ForeignKey` object, in situations
145 where the object is being copied for a :meth:`.Table.to_metadata`
146 operation, should retain the schema name that it already has.
148 """
150 BLANK_SCHEMA = 2
151 """Symbol indicating that a :class:`_schema.Table` or :class:`.Sequence`
152 should have 'None' for its schema, even if the parent
153 :class:`_schema.MetaData` has specified a schema.
155 .. seealso::
157 :paramref:`_schema.MetaData.schema`
159 :paramref:`_schema.Table.schema`
161 :paramref:`.Sequence.schema`
163 """
165 NULL_UNSPECIFIED = 3
166 """Symbol indicating the "nullable" keyword was not passed to a Column.
168 This is used to distinguish between the use case of passing
169 ``nullable=None`` to a :class:`.Column`, which has special meaning
170 on some backends such as SQL Server.
172 """
175RETAIN_SCHEMA: Final[Literal[SchemaConst.RETAIN_SCHEMA]] = (
176 SchemaConst.RETAIN_SCHEMA
177)
178BLANK_SCHEMA: Final[Literal[SchemaConst.BLANK_SCHEMA]] = (
179 SchemaConst.BLANK_SCHEMA
180)
181NULL_UNSPECIFIED: Final[Literal[SchemaConst.NULL_UNSPECIFIED]] = (
182 SchemaConst.NULL_UNSPECIFIED
183)
186def _get_table_key(name: str, schema: Optional[str]) -> str:
187 if schema is None:
188 return name
189 else:
190 return schema + "." + name
193# this should really be in sql/util.py but we'd have to
194# break an import cycle
195def _copy_expression(
196 expression: ColumnElement[Any],
197 source_table: Optional[Table],
198 target_table: Optional[Table],
199) -> ColumnElement[Any]:
200 if source_table is None or target_table is None:
201 return expression
203 fixed_source_table = source_table
204 fixed_target_table = target_table
206 def replace(
207 element: ExternallyTraversible, **kw: Any
208 ) -> Optional[ExternallyTraversible]:
209 if (
210 isinstance(element, Column)
211 and element.table is fixed_source_table
212 and element.key in fixed_source_table.c
213 ):
214 return fixed_target_table.c[element.key]
215 else:
216 return None
218 return cast(
219 ColumnElement[Any],
220 visitors.replacement_traverse(expression, {}, replace),
221 )
224@inspection._self_inspects
225class SchemaItem(SchemaVisitable):
226 """Base class for items that define a database schema."""
228 __visit_name__ = "schema_item"
230 create_drop_stringify_dialect = "default"
232 def _init_items(self, *args: SchemaItem, **kw: Any) -> None:
233 """Initialize the list of child items for this SchemaItem."""
234 for item in args:
235 if item is not None:
236 try:
237 spwd = item._set_parent_with_dispatch
238 except AttributeError as err:
239 raise exc.ArgumentError(
240 "'SchemaItem' object, such as a 'Column' or a "
241 f"'Constraint' expected, got {item!r}"
242 ) from err
243 else:
244 spwd(self, **kw)
246 def __repr__(self) -> str:
247 return util.generic_repr(self, omit_kwarg=["info"])
249 @util.memoized_property
250 def info(self) -> _InfoType:
251 """Info dictionary associated with the object, allowing user-defined
252 data to be associated with this :class:`.SchemaItem`.
254 The dictionary is automatically generated when first accessed.
255 It can also be specified in the constructor of some objects,
256 such as :class:`_schema.Table` and :class:`_schema.Column`.
258 """
259 return {}
261 def _schema_item_copy(self, schema_item: _SI) -> _SI:
262 if "info" in self.__dict__:
263 schema_item.info = self.info.copy()
264 schema_item.dispatch._update(self.dispatch)
265 return schema_item
267 _use_schema_map = True
270class HasConditionalDDL:
271 """define a class that includes the :meth:`.HasConditionalDDL.ddl_if`
272 method, allowing for conditional rendering of DDL.
274 Currently applies to constraints and indexes.
276 .. versionadded:: 2.0
279 """
281 _ddl_if: Optional[ddl.DDLIf] = None
283 def ddl_if(
284 self,
285 dialect: Optional[str] = None,
286 callable_: Optional[ddl.DDLIfCallable] = None,
287 state: Optional[Any] = None,
288 ) -> Self:
289 r"""apply a conditional DDL rule to this schema item.
291 These rules work in a similar manner to the
292 :meth:`.ExecutableDDLElement.execute_if` callable, with the added
293 feature that the criteria may be checked within the DDL compilation
294 phase for a construct such as :class:`.CreateTable`.
295 :meth:`.HasConditionalDDL.ddl_if` currently applies towards the
296 :class:`.Index` construct as well as all :class:`.Constraint`
297 constructs.
299 :param dialect: string name of a dialect, or a tuple of string names
300 to indicate multiple dialect types.
302 :param callable\_: a callable that is constructed using the same form
303 as that described in
304 :paramref:`.ExecutableDDLElement.execute_if.callable_`.
306 :param state: any arbitrary object that will be passed to the
307 callable, if present.
309 .. versionadded:: 2.0
311 .. seealso::
313 :ref:`schema_ddl_ddl_if` - background and usage examples
316 """
317 self._ddl_if = ddl.DDLIf(dialect, callable_, state)
318 return self
321class HasSchemaAttr(SchemaItem):
322 """schema item that includes a top-level schema name"""
324 schema: Optional[str]
327class Table(
328 DialectKWArgs,
329 HasSchemaAttr,
330 TableClause[_ColCC_co],
331 inspection.Inspectable["Table"],
332):
333 r"""Represent a table in a database.
335 e.g.::
337 from sqlalchemy import Table, MetaData, Integer, String, Column
339 metadata = MetaData()
341 mytable = Table(
342 "mytable",
343 metadata,
344 Column("mytable_id", Integer, primary_key=True),
345 Column("value", String(50)),
346 )
348 The :class:`_schema.Table`
349 object constructs a unique instance of itself based
350 on its name and optional schema name within the given
351 :class:`_schema.MetaData` object. Calling the :class:`_schema.Table`
352 constructor with the same name and same :class:`_schema.MetaData` argument
353 a second time will return the *same* :class:`_schema.Table`
354 object - in this way
355 the :class:`_schema.Table` constructor acts as a registry function.
357 May also be defined as "typed table" by passing a subclass of
358 :class:`_schema.TypedColumns` as the 3rd argument::
360 from sqlalchemy import TypedColumns, select
363 class user_cols(TypedColumns):
364 id = Column(Integer, primary_key=True)
365 name: Column[str]
366 age: Column[int]
367 middle_name: Column[str | None]
369 # optional, used to infer the select types when selecting the table
370 __row_pos__: tuple[int, str, int, str | None]
373 user = Table("user", metadata, user_cols)
375 # the columns are typed: the statement has type Select[int, str]
376 stmt = sa.select(user.c.id, user.c.name).where(user.c.age > 30)
378 # Inferred as Select[int, str, int, str | None] thanks to __row_pos__
379 stmt1 = user.select()
380 stmt2 = sa.select(user)
382 The :attr:`sqlalchemy.sql._annotated_cols.HasRowPos.__row_pos__`
383 annotation is optional, and it's used to infer the types in a
384 :class:`_sql.Select` when selecting the complete table.
385 If a :class:`_schema.TypedColumns` does not define it,
386 the default ``Select[*tuple[Any]]`` will be inferred.
388 An existing :class:`Table` can be casted as "typed table" using
389 the :meth:`Table.with_cols`::
391 class mytable_cols(TypedColumns):
392 mytable_id: Column[int]
393 value: Column[str | None]
396 typed_mytable = mytable.with_cols(mytable_cols)
398 .. seealso::
400 :ref:`metadata_describing` Introduction to database metadata
402 :class:`_schema.TypedColumns` More information about typed column
403 definition
405 .. versionchanged:: 2.1.0b2 - :class:`_schema.Table` is now generic to
406 support "typed tables"
407 """
409 __visit_name__ = "table"
411 if TYPE_CHECKING:
413 @util.ro_non_memoized_property
414 def primary_key(self) -> PrimaryKeyConstraint: ...
416 @util.ro_non_memoized_property
417 def foreign_keys(self) -> Set[ForeignKey]: ...
419 def with_cols(self, type_: type[_TC_co]) -> Table[_TC_co]: ...
421 _columns: DedupeColumnCollection[Column[Any]] # type: ignore[assignment]
423 _sentinel_column: Optional[Column[Any]]
425 constraints: Set[Constraint]
426 """A collection of all :class:`_schema.Constraint` objects associated with
427 this :class:`_schema.Table`.
429 Includes :class:`_schema.PrimaryKeyConstraint`,
430 :class:`_schema.ForeignKeyConstraint`, :class:`_schema.UniqueConstraint`,
431 :class:`_schema.CheckConstraint`. A separate collection
432 :attr:`_schema.Table.foreign_key_constraints` refers to the collection
433 of all :class:`_schema.ForeignKeyConstraint` objects, and the
434 :attr:`_schema.Table.primary_key` attribute refers to the single
435 :class:`_schema.PrimaryKeyConstraint` associated with the
436 :class:`_schema.Table`.
438 .. seealso::
440 :attr:`_schema.Table.constraints`
442 :attr:`_schema.Table.primary_key`
444 :attr:`_schema.Table.foreign_key_constraints`
446 :attr:`_schema.Table.indexes`
448 :class:`_reflection.Inspector`
451 """
453 indexes: Set[Index]
454 """A collection of all :class:`_schema.Index` objects associated with this
455 :class:`_schema.Table`.
457 .. seealso::
459 :meth:`_reflection.Inspector.get_indexes`
461 """
463 def _gen_cache_key(
464 self, anon_map: anon_map, bindparams: List[BindParameter[Any]]
465 ) -> Tuple[Any, ...]:
466 if self._annotations:
467 return (self,) + self._annotations_cache_key
468 else:
469 return (self,)
471 if not typing.TYPE_CHECKING:
472 # typing tools seem to be inconsistent in how they handle
473 # __new__, so suggest this pattern for classes that use
474 # __new__. apply typing to the __init__ method normally
475 @util.deprecated_params(
476 mustexist=(
477 "1.4",
478 "Deprecated alias of :paramref:`_schema.Table.must_exist`",
479 ),
480 )
481 def __new__(cls, *args: Any, **kw: Any) -> Any:
482 return cls._new(*args, **kw)
484 @classmethod
485 def _new(cls, *args: Any, **kw: Any) -> Any:
486 if not args and not kw:
487 # python3k pickle seems to call this
488 return object.__new__(cls)
490 try:
491 name, metadata, *other_args = args
492 except ValueError:
493 raise TypeError(
494 "Table() takes at least two positional-only "
495 "arguments 'name', and 'metadata'"
496 ) from None
497 if other_args and isinstance(other_args[0], type):
498 typed_columns_cls = other_args[0]
499 if not issubclass(typed_columns_cls, TypedColumns):
500 raise exc.InvalidRequestError(
501 "The ``typed_columns_cls`` argument requires a "
502 "TypedColumns subclass."
503 )
504 elif hasattr(typed_columns_cls, "_sa_class_manager"):
505 # an orm class subclassed with TypedColumns. Reject it
506 raise exc.InvalidRequestError(
507 "To get a typed table from an ORM class, use the "
508 "`as_typed_table()` function instead."
509 )
511 extracted_columns = _extract_columns_from_class(typed_columns_cls)
512 other_args = extracted_columns + other_args[1:]
513 elif "typed_columns_cls" in kw:
514 raise TypeError(
515 "The ``typed_columns_cls`` argument may be passed "
516 "only positionally"
517 )
519 schema = kw.get("schema", None)
520 if schema is None:
521 schema = metadata.schema
522 elif schema is BLANK_SCHEMA:
523 schema = None
524 keep_existing = kw.get("keep_existing", False)
525 extend_existing = kw.get("extend_existing", False)
527 if keep_existing and extend_existing:
528 msg = "keep_existing and extend_existing are mutually exclusive."
529 raise exc.ArgumentError(msg)
531 must_exist = kw.pop("must_exist", kw.pop("mustexist", False))
532 key = _get_table_key(name, schema)
533 if key in metadata.tables:
534 if not keep_existing and not extend_existing and bool(other_args):
535 raise exc.InvalidRequestError(
536 f"Table '{key}' is already defined for this MetaData "
537 "instance. Specify 'extend_existing=True' "
538 "to redefine "
539 "options and columns on an "
540 "existing Table object."
541 )
542 table = metadata.tables[key]
543 if extend_existing:
544 table._init_existing(*other_args, **kw)
545 return table
546 else:
547 if must_exist:
548 raise exc.InvalidRequestError(f"Table '{key}' not defined")
549 table = object.__new__(cls)
550 table.dispatch.before_parent_attach(table, metadata)
551 metadata._add_table(name, schema, table)
552 try:
553 table.__init__(name, metadata, *other_args, _no_init=False, **kw) # type: ignore[misc] # noqa: E501
554 table.dispatch.after_parent_attach(table, metadata)
555 return table
556 except Exception:
557 with util.safe_reraise():
558 metadata._remove_table(name, schema)
560 @overload
561 def __init__(
562 self: Table[_TC_co],
563 name: str,
564 metadata: MetaData,
565 typed_columns_cls: type[_TC_co],
566 /,
567 *args: SchemaItem,
568 schema: str | Literal[SchemaConst.BLANK_SCHEMA] | None = None,
569 quote: bool | None = None,
570 quote_schema: bool | None = None,
571 keep_existing: bool = False,
572 extend_existing: bool = False,
573 implicit_returning: bool = True,
574 comment: str | None = None,
575 info: dict[Any, Any] | None = None,
576 listeners: (
577 _typing_Sequence[tuple[str, Callable[..., Any]]] | None
578 ) = None,
579 prefixes: _typing_Sequence[str] | None = None,
580 **kw: Any,
581 ) -> None: ...
583 @overload
584 def __init__(
585 self: Table[ReadOnlyColumnCollection[str, Column[Any]]],
586 name: str,
587 metadata: MetaData,
588 /,
589 *args: SchemaItem,
590 schema: str | Literal[SchemaConst.BLANK_SCHEMA] | None = None,
591 quote: bool | None = None,
592 quote_schema: Optional[bool] = None,
593 autoload_with: Optional[Union[Engine, Connection]] = None,
594 autoload_replace: bool = True,
595 keep_existing: bool = False,
596 extend_existing: bool = False,
597 resolve_fks: bool = True,
598 include_columns: Optional[Collection[str]] = None,
599 implicit_returning: bool = True,
600 comment: str | None = None,
601 info: dict[Any, Any] | None = None,
602 listeners: (
603 _typing_Sequence[tuple[str, Callable[..., Any]]] | None
604 ) = None,
605 prefixes: _typing_Sequence[str] | None = None,
606 _creator_ddl: TableCreateDDL | None = None,
607 _dropper_ddl: TableDropDDL | None = None,
608 # used internally in the metadata.reflect() process
609 _extend_on: Optional[Set[Table]] = None,
610 # used by __new__ to bypass __init__
611 _no_init: bool = True,
612 # dialect-specific keyword args
613 **kw: Any,
614 ) -> None: ...
616 def __init__(
617 self,
618 name: str,
619 metadata: MetaData,
620 /,
621 *args: Any,
622 schema: str | Literal[SchemaConst.BLANK_SCHEMA] | None = None,
623 quote: bool | None = None,
624 quote_schema: Optional[bool] = None,
625 autoload_with: Optional[Union[Engine, Connection]] = None,
626 autoload_replace: bool = True,
627 keep_existing: bool = False,
628 extend_existing: bool = False,
629 resolve_fks: bool = True,
630 include_columns: Optional[Collection[str]] = None,
631 implicit_returning: bool = True,
632 comment: str | None = None,
633 info: dict[Any, Any] | None = None,
634 listeners: (
635 _typing_Sequence[tuple[str, Callable[..., Any]]] | None
636 ) = None,
637 prefixes: _typing_Sequence[str] | None = None,
638 _creator_ddl: TableCreateDDL | None = None,
639 _dropper_ddl: TableDropDDL | None = None,
640 # used internally in the metadata.reflect() process
641 _extend_on: Optional[Set[Table]] = None,
642 # used by __new__ to bypass __init__
643 _no_init: bool = True,
644 # dialect-specific keyword args
645 **kw: Any,
646 ) -> None:
647 r"""Constructor for :class:`_schema.Table`.
650 :param name: The name of this table as represented in the database.
652 The table name, along with the value of the ``schema`` parameter,
653 forms a key which uniquely identifies this :class:`_schema.Table`
654 within
655 the owning :class:`_schema.MetaData` collection.
656 Additional calls to :class:`_schema.Table` with the same name,
657 metadata,
658 and schema name will return the same :class:`_schema.Table` object.
660 Names which contain no upper case characters
661 will be treated as case insensitive names, and will not be quoted
662 unless they are a reserved word or contain special characters.
663 A name with any number of upper case characters is considered
664 to be case sensitive, and will be sent as quoted.
666 To enable unconditional quoting for the table name, specify the flag
667 ``quote=True`` to the constructor, or use the :class:`.quoted_name`
668 construct to specify the name.
670 :param metadata: a :class:`_schema.MetaData`
671 object which will contain this
672 table. The metadata is used as a point of association of this table
673 with other tables which are referenced via foreign key. It also
674 may be used to associate this table with a particular
675 :class:`.Connection` or :class:`.Engine`.
677 :param table_columns_cls: a subclass of :class:`_schema.TypedColumns`
678 that defines the columns that will be "typed" when accessing
679 them from the :attr:`_schema.Table.c` attribute.
681 .. versionadded:: 2.1.0b2
683 :param \*args: Additional positional arguments are used primarily
684 to add the list of :class:`_schema.Column`
685 objects contained within this
686 table. Similar to the style of a CREATE TABLE statement, other
687 :class:`.SchemaItem` constructs may be added here, including
688 :class:`.PrimaryKeyConstraint`, and
689 :class:`_schema.ForeignKeyConstraint`.
690 Additional columns may be provided also when using a
691 :paramref:`_schema.Table.table_columns_cls` class; they will
692 be appended to the "typed" columns and will appear as untyped
693 when accessing them via the :attr:`_schema.Table.c` collection.
695 :param autoload_replace: Defaults to ``True``; when using
696 :paramref:`_schema.Table.autoload_with`
697 in conjunction with :paramref:`_schema.Table.extend_existing`,
698 indicates
699 that :class:`_schema.Column` objects present in the already-existing
700 :class:`_schema.Table`
701 object should be replaced with columns of the same
702 name retrieved from the autoload process. When ``False``, columns
703 already present under existing names will be omitted from the
704 reflection process.
706 Note that this setting does not impact :class:`_schema.Column` objects
707 specified programmatically within the call to :class:`_schema.Table`
708 that
709 also is autoloading; those :class:`_schema.Column` objects will always
710 replace existing columns of the same name when
711 :paramref:`_schema.Table.extend_existing` is ``True``.
713 .. seealso::
715 :paramref:`_schema.Table.autoload_with`
717 :paramref:`_schema.Table.extend_existing`
719 :param autoload_with: An :class:`_engine.Engine` or
720 :class:`_engine.Connection` object,
721 or a :class:`_reflection.Inspector` object as returned by
722 :func:`_sa.inspect`
723 against one, with which this :class:`_schema.Table`
724 object will be reflected.
725 When set to a non-None value, the autoload process will take place
726 for this table against the given engine or connection.
728 .. seealso::
730 :ref:`metadata_reflection_toplevel`
732 :meth:`_events.DDLEvents.column_reflect`
734 :ref:`metadata_reflection_dbagnostic_types`
736 :param extend_existing: When ``True``, indicates that if this
737 :class:`_schema.Table` is already present in the given
738 :class:`_schema.MetaData`,
739 apply further arguments within the constructor to the existing
740 :class:`_schema.Table`.
742 If :paramref:`_schema.Table.extend_existing` or
743 :paramref:`_schema.Table.keep_existing` are not set,
744 and the given name
745 of the new :class:`_schema.Table` refers to a :class:`_schema.Table`
746 that is
747 already present in the target :class:`_schema.MetaData` collection,
748 and
749 this :class:`_schema.Table`
750 specifies additional columns or other constructs
751 or flags that modify the table's state, an
752 error is raised. The purpose of these two mutually-exclusive flags
753 is to specify what action should be taken when a
754 :class:`_schema.Table`
755 is specified that matches an existing :class:`_schema.Table`,
756 yet specifies
757 additional constructs.
759 :paramref:`_schema.Table.extend_existing`
760 will also work in conjunction
761 with :paramref:`_schema.Table.autoload_with` to run a new reflection
762 operation against the database, even if a :class:`_schema.Table`
763 of the same name is already present in the target
764 :class:`_schema.MetaData`; newly reflected :class:`_schema.Column`
765 objects
766 and other options will be added into the state of the
767 :class:`_schema.Table`, potentially overwriting existing columns
768 and options of the same name.
770 As is always the case with :paramref:`_schema.Table.autoload_with`,
771 :class:`_schema.Column` objects can be specified in the same
772 :class:`_schema.Table`
773 constructor, which will take precedence. Below, the existing
774 table ``mytable`` will be augmented with :class:`_schema.Column`
775 objects
776 both reflected from the database, as well as the given
777 :class:`_schema.Column`
778 named "y"::
780 Table(
781 "mytable",
782 metadata,
783 Column("y", Integer),
784 extend_existing=True,
785 autoload_with=engine,
786 )
788 .. seealso::
790 :paramref:`_schema.Table.autoload_with`
792 :paramref:`_schema.Table.autoload_replace`
794 :paramref:`_schema.Table.keep_existing`
797 :param implicit_returning: True by default - indicates that
798 RETURNING can be used, typically by the ORM, in order to fetch
799 server-generated values such as primary key values and
800 server side defaults, on those backends which support RETURNING.
802 In modern SQLAlchemy there is generally no reason to alter this
803 setting, except for some backend specific cases
804 (see :ref:`mssql_triggers` in the SQL Server dialect documentation
805 for one such example).
807 :param include_columns: A list of strings indicating a subset of
808 columns to be loaded via the ``autoload`` operation; table columns who
809 aren't present in this list will not be represented on the resulting
810 ``Table`` object. Defaults to ``None`` which indicates all columns
811 should be reflected.
813 :param resolve_fks: Whether or not to reflect :class:`_schema.Table`
814 objects
815 related to this one via :class:`_schema.ForeignKey` objects, when
816 :paramref:`_schema.Table.autoload_with` is
817 specified. Defaults to True. Set to False to disable reflection of
818 related tables as :class:`_schema.ForeignKey`
819 objects are encountered; may be
820 used either to save on SQL calls or to avoid issues with related tables
821 that can't be accessed. Note that if a related table is already present
822 in the :class:`_schema.MetaData` collection, or becomes present later,
823 a
824 :class:`_schema.ForeignKey` object associated with this
825 :class:`_schema.Table` will
826 resolve to that table normally.
828 .. seealso::
830 :paramref:`.MetaData.reflect.resolve_fks`
833 :param info: Optional data dictionary which will be populated into the
834 :attr:`.SchemaItem.info` attribute of this object.
836 :param keep_existing: When ``True``, indicates that if this Table
837 is already present in the given :class:`_schema.MetaData`, ignore
838 further arguments within the constructor to the existing
839 :class:`_schema.Table`, and return the :class:`_schema.Table`
840 object as
841 originally created. This is to allow a function that wishes
842 to define a new :class:`_schema.Table` on first call, but on
843 subsequent calls will return the same :class:`_schema.Table`,
844 without any of the declarations (particularly constraints)
845 being applied a second time.
847 If :paramref:`_schema.Table.extend_existing` or
848 :paramref:`_schema.Table.keep_existing` are not set,
849 and the given name
850 of the new :class:`_schema.Table` refers to a :class:`_schema.Table`
851 that is
852 already present in the target :class:`_schema.MetaData` collection,
853 and
854 this :class:`_schema.Table`
855 specifies additional columns or other constructs
856 or flags that modify the table's state, an
857 error is raised. The purpose of these two mutually-exclusive flags
858 is to specify what action should be taken when a
859 :class:`_schema.Table`
860 is specified that matches an existing :class:`_schema.Table`,
861 yet specifies
862 additional constructs.
864 .. seealso::
866 :paramref:`_schema.Table.extend_existing`
868 :param listeners: A list of tuples of the form ``(<eventname>, <fn>)``
869 which will be passed to :func:`.event.listen` upon construction.
870 This alternate hook to :func:`.event.listen` allows the establishment
871 of a listener function specific to this :class:`_schema.Table` before
872 the "autoload" process begins. Historically this has been intended
873 for use with the :meth:`.DDLEvents.column_reflect` event, however
874 note that this event hook may now be associated with the
875 :class:`_schema.MetaData` object directly::
877 def listen_for_reflect(table, column_info):
878 "handle the column reflection event"
879 # ...
882 t = Table(
883 "sometable",
884 autoload_with=engine,
885 listeners=[("column_reflect", listen_for_reflect)],
886 )
888 .. seealso::
890 :meth:`_events.DDLEvents.column_reflect`
892 :param must_exist: When ``True``, indicates that this Table must already
893 be present in the given :class:`_schema.MetaData` collection, else
894 an exception is raised.
896 :param prefixes:
897 A list of strings to insert after CREATE in the CREATE TABLE
898 statement. They will be separated by spaces.
900 :param quote: Force quoting of this table's name on or off, corresponding
901 to ``True`` or ``False``. When left at its default of ``None``,
902 the column identifier will be quoted according to whether the name is
903 case sensitive (identifiers with at least one upper case character are
904 treated as case sensitive), or if it's a reserved word. This flag
905 is only needed to force quoting of a reserved word which is not known
906 by the SQLAlchemy dialect.
908 .. note:: setting this flag to ``False`` will not provide
909 case-insensitive behavior for table reflection; table reflection
910 will always search for a mixed-case name in a case sensitive
911 fashion. Case insensitive names are specified in SQLAlchemy only
912 by stating the name with all lower case characters.
914 :param quote_schema: same as 'quote' but applies to the schema identifier.
916 :param schema: The schema name for this table, which is required if
917 the table resides in a schema other than the default selected schema
918 for the engine's database connection. Defaults to ``None``.
920 If the owning :class:`_schema.MetaData` of this :class:`_schema.Table`
921 specifies its
922 own :paramref:`_schema.MetaData.schema` parameter,
923 then that schema name will
924 be applied to this :class:`_schema.Table`
925 if the schema parameter here is set
926 to ``None``. To set a blank schema name on a :class:`_schema.Table`
927 that
928 would otherwise use the schema set on the owning
929 :class:`_schema.MetaData`,
930 specify the special symbol :attr:`.BLANK_SCHEMA`.
932 The quoting rules for the schema name are the same as those for the
933 ``name`` parameter, in that quoting is applied for reserved words or
934 case-sensitive names; to enable unconditional quoting for the schema
935 name, specify the flag ``quote_schema=True`` to the constructor, or use
936 the :class:`.quoted_name` construct to specify the name.
938 :param comment: Optional string that will render an SQL comment on table
939 creation.
941 :param \**kw: Additional keyword arguments not mentioned above are
942 dialect specific, and passed in the form ``<dialectname>_<argname>``.
943 See the documentation regarding an individual dialect at
944 :ref:`dialect_toplevel` for detail on documented arguments.
946 """ # noqa: E501
947 if _no_init:
948 # don't run __init__ from __new__ by default;
949 # __new__ has a specific place that __init__ is called
950 return
951 if args:
952 # this is the call done by `__new__` that should have resolved
953 # TypedColumns to the individual columns
954 assert not (
955 isinstance(args[0], type) and issubclass(args[0], TypedColumns)
956 )
958 super().__init__(quoted_name(name, quote))
959 self.metadata = metadata
961 if schema is None:
962 self.schema = metadata.schema
963 elif schema is BLANK_SCHEMA:
964 self.schema = None
965 else:
966 quote_schema = quote_schema
967 assert isinstance(schema, str)
968 self.schema = quoted_name(schema, quote_schema)
970 self._sentinel_column = None
971 self._creator_ddl = _creator_ddl
972 self._dropper_ddl = _dropper_ddl
974 self.indexes = set()
975 self.constraints = set()
976 PrimaryKeyConstraint(
977 _implicit_generated=True
978 )._set_parent_with_dispatch(self)
979 self.foreign_keys = set() # type: ignore
980 self._extra_dependencies: Set[Table] = set()
981 if self.schema is not None:
982 self.fullname = "%s.%s" % (self.schema, self.name)
983 else:
984 self.fullname = self.name
986 self.implicit_returning = implicit_returning
987 _reflect_info = kw.pop("_reflect_info", None)
989 self.comment = comment
991 if info is not None:
992 self.info = info
994 if listeners is not None:
995 for evt, fn in listeners:
996 event.listen(self, evt, fn)
998 self._prefixes = prefixes if prefixes else []
1000 self._extra_kwargs(**kw)
1002 # load column definitions from the database if 'autoload' is defined
1003 # we do it after the table is in the singleton dictionary to support
1004 # circular foreign keys
1005 if autoload_with is not None:
1006 self._autoload(
1007 metadata,
1008 autoload_with,
1009 include_columns,
1010 _extend_on=_extend_on,
1011 _reflect_info=_reflect_info,
1012 resolve_fks=resolve_fks,
1013 )
1015 # initialize all the column, etc. objects. done after reflection to
1016 # allow user-overrides
1018 self._init_items(
1019 *args,
1020 allow_replacements=extend_existing
1021 or keep_existing
1022 or autoload_with,
1023 all_names={},
1024 )
1026 def set_creator_ddl(self, ddl: TableCreateDDL) -> None:
1027 """Set the table create DDL for this :class:`.Table`.
1029 This allows the CREATE TABLE statement to be controlled or replaced
1030 entirely when :meth:`.Table.create` or :meth:`.MetaData.create_all` is
1031 used.
1033 E.g.::
1035 from sqlalchemy.schema import CreateTable
1037 table.set_creator_ddl(CreateTable(table, if_not_exists=True))
1039 .. versionadded:: 2.1
1041 .. seealso::
1043 :meth:`.Table.set_dropper_ddl`
1045 """
1046 self._creator_ddl = ddl
1048 def set_dropper_ddl(self, ddl: TableDropDDL) -> None:
1049 """Set the table drop DDL for this :class:`.Table`.
1051 This allows the DROP TABLE statement to be controlled or replaced
1052 entirely when :meth:`.Table.drop` or :meth:`.MetaData.drop_all` is
1053 used.
1055 E.g.::
1057 from sqlalchemy.schema import DropTable
1059 table.set_dropper_ddl(DropTable(table, if_exists=True))
1061 .. versionadded:: 2.1
1063 .. seealso::
1065 :meth:`.Table.set_creator_ddl`
1067 """
1068 self._dropper_ddl = ddl
1070 @property
1071 def is_view(self) -> bool:
1072 """True if this table, when DDL for CREATE is emitted, will emit
1073 CREATE VIEW rather than CREATE TABLE.
1075 .. versionadded:: 2.1
1077 """
1078 return isinstance(self._creator_ddl, ddl.CreateView)
1080 def _autoload(
1081 self,
1082 metadata: MetaData,
1083 autoload_with: Union[Engine, Connection],
1084 include_columns: Optional[Collection[str]],
1085 exclude_columns: Collection[str] = (),
1086 resolve_fks: bool = True,
1087 _extend_on: Optional[Set[Table]] = None,
1088 _reflect_info: _ReflectionInfo | None = None,
1089 ) -> None:
1090 insp = inspection.inspect(autoload_with)
1091 with insp._inspection_context() as conn_insp:
1092 conn_insp.reflect_table(
1093 self,
1094 include_columns,
1095 exclude_columns,
1096 resolve_fks,
1097 _extend_on=_extend_on,
1098 _reflect_info=_reflect_info,
1099 )
1101 @property
1102 def _sorted_constraints(self) -> List[Constraint]:
1103 """Return the set of constraints as a list, sorted by creation
1104 order.
1106 """
1108 return sorted(self.constraints, key=lambda c: c._creation_order)
1110 @property
1111 def foreign_key_constraints(self) -> Set[ForeignKeyConstraint]:
1112 """:class:`_schema.ForeignKeyConstraint` objects referred to by this
1113 :class:`_schema.Table`.
1115 This list is produced from the collection of
1116 :class:`_schema.ForeignKey`
1117 objects currently associated.
1120 .. seealso::
1122 :attr:`_schema.Table.constraints`
1124 :attr:`_schema.Table.foreign_keys`
1126 :attr:`_schema.Table.indexes`
1128 """
1129 return {
1130 fkc.constraint
1131 for fkc in self.foreign_keys
1132 if fkc.constraint is not None
1133 }
1135 def _init_existing(self, *args: Any, **kwargs: Any) -> None:
1136 autoload_with = kwargs.pop("autoload_with", None)
1137 autoload = kwargs.pop("autoload", autoload_with is not None)
1138 autoload_replace = kwargs.pop("autoload_replace", True)
1139 schema = kwargs.pop("schema", None)
1140 _extend_on = kwargs.pop("_extend_on", None)
1141 _reflect_info = kwargs.pop("_reflect_info", None)
1143 # these arguments are only used with _init()
1144 extend_existing = kwargs.pop("extend_existing", False)
1145 keep_existing = kwargs.pop("keep_existing", False)
1147 assert extend_existing
1148 assert not keep_existing
1150 if schema and schema != self.schema:
1151 raise exc.ArgumentError(
1152 f"Can't change schema of existing table "
1153 f"from '{self.schema}' to '{schema}'",
1154 )
1156 include_columns = kwargs.pop("include_columns", None)
1157 if include_columns is not None:
1158 for c in self.c:
1159 if c.name not in include_columns:
1160 self._columns.remove(c)
1162 resolve_fks = kwargs.pop("resolve_fks", True)
1164 for key in ("quote", "quote_schema"):
1165 if key in kwargs:
1166 raise exc.ArgumentError(
1167 "Can't redefine 'quote' or 'quote_schema' arguments"
1168 )
1170 # update `self` with these kwargs, if provided
1171 self.comment = kwargs.pop("comment", self.comment)
1172 self.implicit_returning = kwargs.pop(
1173 "implicit_returning", self.implicit_returning
1174 )
1175 self.info = kwargs.pop("info", self.info)
1177 exclude_columns: _typing_Sequence[str]
1179 if autoload:
1180 if not autoload_replace:
1181 # don't replace columns already present.
1182 # we'd like to do this for constraints also however we don't
1183 # have simple de-duping for unnamed constraints.
1184 exclude_columns = [c.name for c in self.c]
1185 else:
1186 exclude_columns = ()
1187 self._autoload(
1188 self.metadata,
1189 autoload_with,
1190 include_columns,
1191 exclude_columns,
1192 resolve_fks,
1193 _extend_on=_extend_on,
1194 _reflect_info=_reflect_info,
1195 )
1197 all_names = {c.name: c for c in self.c}
1198 self._extra_kwargs(**kwargs)
1199 self._init_items(*args, allow_replacements=True, all_names=all_names)
1201 def _extra_kwargs(self, **kwargs: Any) -> None:
1202 self._validate_dialect_kwargs(kwargs)
1204 def _init_collections(self) -> None:
1205 pass
1207 def _reset_exported(self) -> None:
1208 pass
1210 @util.ro_non_memoized_property
1211 def _autoincrement_column(self) -> Optional[Column[int]]:
1212 return self.primary_key._autoincrement_column
1214 @util.ro_memoized_property
1215 def _sentinel_column_characteristics(
1216 self,
1217 ) -> _SentinelColumnCharacterization:
1218 """determine a candidate column (or columns, in case of a client
1219 generated composite primary key) which can be used as an
1220 "insert sentinel" for an INSERT statement.
1222 The returned structure, :class:`_SentinelColumnCharacterization`,
1223 includes all the details needed by :class:`.Dialect` and
1224 :class:`.SQLCompiler` to determine if these column(s) can be used
1225 as an INSERT..RETURNING sentinel for a particular database
1226 dialect.
1228 .. versionadded:: 2.0.10
1230 """
1232 sentinel_is_explicit = False
1233 sentinel_is_autoinc = False
1234 the_sentinel: Optional[_typing_Sequence[Column[Any]]] = None
1236 # see if a column was explicitly marked "insert_sentinel=True".
1237 explicit_sentinel_col = self._sentinel_column
1239 if explicit_sentinel_col is not None:
1240 the_sentinel = (explicit_sentinel_col,)
1241 sentinel_is_explicit = True
1243 autoinc_col = self._autoincrement_column
1244 if sentinel_is_explicit and explicit_sentinel_col is autoinc_col:
1245 assert autoinc_col is not None
1246 sentinel_is_autoinc = True
1247 elif explicit_sentinel_col is None and autoinc_col is not None:
1248 the_sentinel = (autoinc_col,)
1249 sentinel_is_autoinc = True
1251 default_characterization = _SentinelDefaultCharacterization.UNKNOWN
1253 if the_sentinel:
1254 the_sentinel_zero = the_sentinel[0]
1255 if the_sentinel_zero.identity:
1256 if the_sentinel_zero.identity._increment_is_negative:
1257 if sentinel_is_explicit:
1258 raise exc.InvalidRequestError(
1259 "Can't use IDENTITY default with negative "
1260 "increment as an explicit sentinel column"
1261 )
1262 else:
1263 if sentinel_is_autoinc:
1264 autoinc_col = None
1265 sentinel_is_autoinc = False
1266 the_sentinel = None
1267 else:
1268 default_characterization = (
1269 _SentinelDefaultCharacterization.IDENTITY
1270 )
1271 elif (
1272 the_sentinel_zero.default is None
1273 and the_sentinel_zero.server_default is None
1274 ):
1275 if the_sentinel_zero.nullable:
1276 raise exc.InvalidRequestError(
1277 f"Column {the_sentinel_zero} has been marked as a "
1278 "sentinel "
1279 "column with no default generation function; it "
1280 "at least needs to be marked nullable=False assuming "
1281 "user-populated sentinel values will be used."
1282 )
1283 default_characterization = (
1284 _SentinelDefaultCharacterization.NONE
1285 )
1286 elif the_sentinel_zero.default is not None:
1287 if the_sentinel_zero.default.is_sentinel:
1288 default_characterization = (
1289 _SentinelDefaultCharacterization.SENTINEL_DEFAULT
1290 )
1291 elif the_sentinel_zero.default._is_monotonic_fn:
1292 default_characterization = (
1293 _SentinelDefaultCharacterization.MONOTONIC_FUNCTION
1294 )
1295 elif default_is_sequence(the_sentinel_zero.default):
1296 if the_sentinel_zero.default._increment_is_negative:
1297 if sentinel_is_explicit:
1298 raise exc.InvalidRequestError(
1299 "Can't use SEQUENCE default with negative "
1300 "increment as an explicit sentinel column"
1301 )
1302 else:
1303 if sentinel_is_autoinc:
1304 autoinc_col = None
1305 sentinel_is_autoinc = False
1306 the_sentinel = None
1308 default_characterization = (
1309 _SentinelDefaultCharacterization.SEQUENCE
1310 )
1311 elif the_sentinel_zero.default.is_callable:
1312 default_characterization = (
1313 _SentinelDefaultCharacterization.CLIENTSIDE
1314 )
1315 elif the_sentinel_zero.server_default is not None:
1316 if sentinel_is_explicit:
1317 if not the_sentinel_zero.server_default._is_monotonic_fn:
1318 raise exc.InvalidRequestError(
1319 f"Column {the_sentinel[0]} can't be a sentinel "
1320 "column "
1321 "because it uses an explicit server side default "
1322 "that's not the Identity() default."
1323 )
1324 else:
1325 default_characterization = (
1326 _SentinelDefaultCharacterization.MONOTONIC_FUNCTION
1327 )
1328 else:
1329 default_characterization = (
1330 _SentinelDefaultCharacterization.SERVERSIDE
1331 )
1333 if the_sentinel is None and self.primary_key:
1334 assert autoinc_col is None
1336 # determine for non-autoincrement pk if all elements are
1337 # client side
1338 for _pkc in self.primary_key:
1339 if (
1340 _pkc.server_default is not None
1341 and not _pkc.server_default._is_monotonic_fn
1342 ):
1343 break
1345 if (
1346 _pkc.default
1347 and not _pkc.default.is_callable
1348 and not _pkc.default._is_monotonic_fn
1349 ):
1350 break
1351 else:
1352 the_sentinel = tuple(self.primary_key)
1353 default_characterization = (
1354 _SentinelDefaultCharacterization.CLIENTSIDE
1355 )
1357 return _SentinelColumnCharacterization(
1358 the_sentinel,
1359 sentinel_is_explicit,
1360 sentinel_is_autoinc,
1361 default_characterization,
1362 )
1364 @property
1365 def autoincrement_column(self) -> Optional[Column[int]]:
1366 """Returns the :class:`.Column` object which currently represents
1367 the "auto increment" column, if any, else returns None.
1369 This is based on the rules for :class:`.Column` as defined by the
1370 :paramref:`.Column.autoincrement` parameter, which generally means the
1371 column within a single integer column primary key constraint that is
1372 not constrained by a foreign key. If the table does not have such
1373 a primary key constraint, then there's no "autoincrement" column.
1374 A :class:`.Table` may have only one column defined as the
1375 "autoincrement" column.
1377 .. versionadded:: 2.0.4
1379 .. seealso::
1381 :paramref:`.Column.autoincrement`
1383 """
1384 return self._autoincrement_column
1386 @property
1387 def key(self) -> str:
1388 """Return the 'key' for this :class:`_schema.Table`.
1390 This value is used as the dictionary key within the
1391 :attr:`_schema.MetaData.tables` collection. It is typically the same
1392 as that of :attr:`_schema.Table.name` for a table with no
1393 :attr:`_schema.Table.schema`
1394 set; otherwise it is typically of the form
1395 ``schemaname.tablename``.
1397 """
1398 return _get_table_key(self.name, self.schema)
1400 def __repr__(self) -> str:
1401 return "Table(%s)" % ", ".join(
1402 [repr(self.name)]
1403 + [repr(self.metadata)]
1404 + [repr(x) for x in self.columns]
1405 + ["%s=%s" % (k, repr(getattr(self, k))) for k in ["schema"]]
1406 )
1408 def __str__(self) -> str:
1409 return _get_table_key(self.description, self.schema)
1411 def add_is_dependent_on(self, table: Table) -> None:
1412 """Add a 'dependency' for this Table.
1414 This is another Table object which must be created
1415 first before this one can, or dropped after this one.
1417 Usually, dependencies between tables are determined via
1418 ForeignKey objects. However, for other situations that
1419 create dependencies outside of foreign keys (rules, inheriting),
1420 this method can manually establish such a link.
1422 """
1423 self._extra_dependencies.add(table)
1425 def _insert_col_impl(
1426 self,
1427 column: ColumnClause[Any],
1428 *,
1429 index: Optional[int] = None,
1430 replace_existing: bool = False,
1431 ) -> None:
1432 try:
1433 column._set_parent_with_dispatch(
1434 self,
1435 allow_replacements=replace_existing,
1436 all_names={c.name: c for c in self.c},
1437 index=index,
1438 )
1439 except exc.DuplicateColumnError as de:
1440 raise exc.DuplicateColumnError(
1441 f"{de.args[0]} Specify replace_existing=True to "
1442 "Table.append_column() or Table.insert_column() to replace an "
1443 "existing column."
1444 ) from de
1446 def insert_column(
1447 self,
1448 column: ColumnClause[Any],
1449 index: int,
1450 *,
1451 replace_existing: bool = False,
1452 ) -> None:
1453 """Insert a :class:`_schema.Column` to this :class:`_schema.Table` at
1454 a specific position.
1456 Behavior is identical to :meth:`.Table.append_column` except that
1457 the index position can be controlled using the
1458 :paramref:`.Table.insert_column.index`
1459 parameter.
1461 :param replace_existing:
1462 see :paramref:`.Table.append_column.replace_existing`
1463 :param index: integer index to insert the new column.
1465 .. versionadded:: 2.1
1467 """
1468 self._insert_col_impl(
1469 column, index=index, replace_existing=replace_existing
1470 )
1472 def append_column(
1473 self, column: ColumnClause[Any], *, replace_existing: bool = False
1474 ) -> None:
1475 """Append a :class:`_schema.Column` to this :class:`_schema.Table`.
1477 The "key" of the newly added :class:`_schema.Column`, i.e. the
1478 value of its ``.key`` attribute, will then be available
1479 in the ``.c`` collection of this :class:`_schema.Table`, and the
1480 column definition will be included in any CREATE TABLE, SELECT,
1481 UPDATE, etc. statements generated from this :class:`_schema.Table`
1482 construct.
1484 Note that this does **not** change the definition of the table
1485 as it exists within any underlying database, assuming that
1486 table has already been created in the database. Relational
1487 databases support the addition of columns to existing tables
1488 using the SQL ALTER command, which would need to be
1489 emitted for an already-existing table that doesn't contain
1490 the newly added column.
1492 :param replace_existing: When ``True``, allows replacing existing
1493 columns. When ``False``, the default, an warning will be raised
1494 if a column with the same ``.key`` already exists. A future
1495 version of sqlalchemy will instead rise a warning.
1497 .. versionadded:: 1.4.0
1499 .. seealso::
1501 :meth:`.Table.insert_column`
1503 """
1504 self._insert_col_impl(column, replace_existing=replace_existing)
1506 def append_constraint(self, constraint: Union[Index, Constraint]) -> None:
1507 """Append a :class:`_schema.Constraint` to this
1508 :class:`_schema.Table`.
1510 This has the effect of the constraint being included in any
1511 future CREATE TABLE statement, assuming specific DDL creation
1512 events have not been associated with the given
1513 :class:`_schema.Constraint` object.
1515 Note that this does **not** produce the constraint within the
1516 relational database automatically, for a table that already exists
1517 in the database. To add a constraint to an
1518 existing relational database table, the SQL ALTER command must
1519 be used. SQLAlchemy also provides the
1520 :class:`.AddConstraint` construct which can produce this SQL when
1521 invoked as an executable clause.
1523 """
1525 constraint._set_parent_with_dispatch(self)
1527 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
1528 metadata = parent
1529 assert isinstance(metadata, MetaData)
1530 metadata._add_table(self.name, self.schema, self)
1531 self.metadata = metadata
1533 def create(
1534 self,
1535 bind: _CreateDropBind,
1536 checkfirst: Union[bool, CheckFirst] = CheckFirst.TYPES,
1537 ) -> None:
1538 """Issue a ``CREATE`` statement for this
1539 :class:`_schema.Table`, using the given
1540 :class:`.Connection` or :class:`.Engine`
1541 for connectivity.
1543 .. seealso::
1545 :meth:`_schema.MetaData.create_all`.
1547 """
1549 # the default is to only check for schema objects
1550 bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
1552 def drop(
1553 self,
1554 bind: _CreateDropBind,
1555 checkfirst: Union[bool, CheckFirst] = CheckFirst.NONE,
1556 ) -> None:
1557 """Issue a ``DROP`` statement for this
1558 :class:`_schema.Table`, using the given
1559 :class:`.Connection` or :class:`.Engine` for connectivity.
1561 .. seealso::
1563 :meth:`_schema.MetaData.drop_all`.
1565 """
1566 bind._run_ddl_visitor(ddl.SchemaDropper, self, checkfirst=checkfirst)
1568 @util.deprecated(
1569 "1.4",
1570 ":meth:`_schema.Table.tometadata` is renamed to "
1571 ":meth:`_schema.Table.to_metadata`",
1572 )
1573 def tometadata(
1574 self,
1575 metadata: MetaData,
1576 schema: Union[str, Literal[SchemaConst.RETAIN_SCHEMA]] = RETAIN_SCHEMA,
1577 referred_schema_fn: Optional[
1578 Callable[
1579 [Table, Optional[str], ForeignKeyConstraint, Optional[str]],
1580 Optional[str],
1581 ]
1582 ] = None,
1583 name: Optional[str] = None,
1584 ) -> Table[_ColCC_co]:
1585 """Return a copy of this :class:`_schema.Table`
1586 associated with a different
1587 :class:`_schema.MetaData`.
1589 See :meth:`_schema.Table.to_metadata` for a full description.
1591 """
1592 return self.to_metadata(
1593 metadata,
1594 schema=schema,
1595 referred_schema_fn=referred_schema_fn,
1596 name=name,
1597 )
1599 def to_metadata(
1600 self,
1601 metadata: MetaData,
1602 schema: Union[str, Literal[SchemaConst.RETAIN_SCHEMA]] = RETAIN_SCHEMA,
1603 referred_schema_fn: Optional[
1604 Callable[
1605 [Table, Optional[str], ForeignKeyConstraint, Optional[str]],
1606 Optional[str],
1607 ]
1608 ] = None,
1609 name: Optional[str] = None,
1610 ) -> Table[_ColCC_co]:
1611 """Return a copy of this :class:`_schema.Table` associated with a
1612 different :class:`_schema.MetaData`.
1614 E.g.::
1616 m1 = MetaData()
1618 user = Table("user", m1, Column("id", Integer, primary_key=True))
1620 m2 = MetaData()
1621 user_copy = user.to_metadata(m2)
1623 .. versionchanged:: 1.4 The :meth:`_schema.Table.to_metadata` function
1624 was renamed from :meth:`_schema.Table.tometadata`.
1627 :param metadata: Target :class:`_schema.MetaData` object,
1628 into which the
1629 new :class:`_schema.Table` object will be created.
1631 :param schema: optional string name indicating the target schema.
1632 Defaults to the special symbol :attr:`.RETAIN_SCHEMA` which indicates
1633 that no change to the schema name should be made in the new
1634 :class:`_schema.Table`. If set to a string name, the new
1635 :class:`_schema.Table`
1636 will have this new name as the ``.schema``. If set to ``None``, the
1637 schema will be set to that of the schema set on the target
1638 :class:`_schema.MetaData`, which is typically ``None`` as well,
1639 unless
1640 set explicitly::
1642 m2 = MetaData(schema="newschema")
1644 # user_copy_one will have "newschema" as the schema name
1645 user_copy_one = user.to_metadata(m2, schema=None)
1647 m3 = MetaData() # schema defaults to None
1649 # user_copy_two will have None as the schema name
1650 user_copy_two = user.to_metadata(m3, schema=None)
1652 :param referred_schema_fn: optional callable which can be supplied
1653 in order to provide for the schema name that should be assigned
1654 to the referenced table of a :class:`_schema.ForeignKeyConstraint`.
1655 The callable accepts this parent :class:`_schema.Table`, the
1656 target schema that we are changing to, the
1657 :class:`_schema.ForeignKeyConstraint` object, and the existing
1658 "target schema" of that constraint. The function should return the
1659 string schema name that should be applied. To reset the schema
1660 to "none", return the symbol :data:`.BLANK_SCHEMA`. To effect no
1661 change, return ``None`` or :data:`.RETAIN_SCHEMA`.
1663 .. versionchanged:: 1.4.33 The ``referred_schema_fn`` function
1664 may return the :data:`.BLANK_SCHEMA` or :data:`.RETAIN_SCHEMA`
1665 symbols.
1667 E.g.::
1669 def referred_schema_fn(table, to_schema, constraint, referred_schema):
1670 if referred_schema == "base_tables":
1671 return referred_schema
1672 else:
1673 return to_schema
1676 new_table = table.to_metadata(
1677 m2, schema="alt_schema", referred_schema_fn=referred_schema_fn
1678 )
1680 :param name: optional string name indicating the target table name.
1681 If not specified or None, the table name is retained. This allows
1682 a :class:`_schema.Table` to be copied to the same
1683 :class:`_schema.MetaData` target
1684 with a new name.
1686 """ # noqa: E501
1687 if name is None:
1688 name = self.name
1690 actual_schema: Optional[str]
1692 if schema is RETAIN_SCHEMA:
1693 actual_schema = self.schema
1694 elif schema is None:
1695 actual_schema = metadata.schema
1696 else:
1697 actual_schema = schema
1698 key = _get_table_key(name, actual_schema)
1699 if key in metadata.tables:
1700 util.warn(
1701 f"Table '{self.description}' already exists within the given "
1702 "MetaData - not copying."
1703 )
1704 return metadata.tables[key]
1706 args = []
1707 for col in self.columns:
1708 args.append(col._copy(schema=actual_schema, _to_metadata=metadata))
1710 table: Table[_ColCC_co] = Table( # type: ignore[assignment]
1711 name,
1712 metadata,
1713 schema=actual_schema,
1714 comment=self.comment,
1715 *args,
1716 **self.kwargs,
1717 )
1719 if self._creator_ddl is not None:
1720 table._creator_ddl = self._creator_ddl.to_metadata(metadata, table)
1721 if self._dropper_ddl is not None:
1722 table._dropper_ddl = self._dropper_ddl.to_metadata(metadata, table)
1724 for const in self.constraints:
1725 if isinstance(const, ForeignKeyConstraint):
1726 referred_schema = const._referred_schema
1727 if referred_schema_fn:
1728 fk_constraint_schema = referred_schema_fn(
1729 self, actual_schema, const, referred_schema
1730 )
1731 else:
1732 fk_constraint_schema = (
1733 actual_schema
1734 if referred_schema == self.schema
1735 else None
1736 )
1737 table.append_constraint(
1738 const._copy(
1739 schema=fk_constraint_schema, target_table=table
1740 )
1741 )
1742 elif not const._type_bound:
1743 # skip unique constraints that would be generated
1744 # by the 'unique' flag on Column
1745 if const._column_flag:
1746 continue
1748 table.append_constraint(
1749 const._copy(schema=actual_schema, target_table=table)
1750 )
1751 for index in self.indexes:
1752 # skip indexes that would be generated
1753 # by the 'index' flag on Column
1754 if index._column_flag:
1755 continue
1756 Index(
1757 index.name,
1758 unique=index.unique,
1759 *[
1760 _copy_expression(expr, self, table)
1761 for expr in index._table_bound_expressions
1762 ],
1763 _table=table,
1764 **index.kwargs,
1765 )
1766 return self._schema_item_copy(table)
1769class Column(DialectKWArgs, SchemaItem, ColumnClause[_T], Named[_T]):
1770 """Represents a column in a database table."""
1772 __visit_name__ = "column"
1774 inherit_cache = True
1775 key: str
1777 server_default: Optional[FetchedValue]
1779 def __init__(
1780 self,
1781 __name_pos: Optional[
1782 Union[str, _TypeEngineArgument[_T], SchemaEventTarget]
1783 ] = None,
1784 __type_pos: Optional[
1785 Union[_TypeEngineArgument[_T], SchemaEventTarget]
1786 ] = None,
1787 /,
1788 *args: SchemaEventTarget,
1789 name: Optional[str] = None,
1790 type_: Optional[_TypeEngineArgument[_T]] = None,
1791 autoincrement: _AutoIncrementType = "auto",
1792 default: Optional[Any] = _NoArg.NO_ARG,
1793 insert_default: Optional[Any] = _NoArg.NO_ARG,
1794 doc: Optional[str] = None,
1795 key: Optional[str] = None,
1796 index: Optional[bool] = None,
1797 unique: Optional[bool] = None,
1798 info: Optional[_InfoType] = None,
1799 nullable: Optional[
1800 Union[bool, Literal[SchemaConst.NULL_UNSPECIFIED]]
1801 ] = SchemaConst.NULL_UNSPECIFIED,
1802 onupdate: Optional[Any] = None,
1803 primary_key: bool = False,
1804 server_default: Optional[_ServerDefaultArgument] = None,
1805 server_onupdate: Optional[_ServerOnUpdateArgument] = None,
1806 quote: Optional[bool] = None,
1807 system: bool = False,
1808 comment: Optional[str] = None,
1809 insert_sentinel: bool = False,
1810 _omit_from_statements: bool = False,
1811 _proxies: Optional[Any] = None,
1812 **dialect_kwargs: Any,
1813 ):
1814 r"""
1815 Construct a new ``Column`` object.
1817 :param name: The name of this column as represented in the database.
1818 This argument may be the first positional argument, or specified
1819 via keyword.
1821 Names which contain no upper case characters
1822 will be treated as case insensitive names, and will not be quoted
1823 unless they are a reserved word. Names with any number of upper
1824 case characters will be quoted and sent exactly. Note that this
1825 behavior applies even for databases which standardize upper
1826 case names as case insensitive such as Oracle Database.
1828 The name field may be omitted at construction time and applied
1829 later, at any time before the Column is associated with a
1830 :class:`_schema.Table`. This is to support convenient
1831 usage within the :mod:`~sqlalchemy.ext.declarative` extension.
1833 :param type\_: The column's type, indicated using an instance which
1834 subclasses :class:`~sqlalchemy.types.TypeEngine`. If no arguments
1835 are required for the type, the class of the type can be sent
1836 as well, e.g.::
1838 # use a type with arguments
1839 Column("data", String(50))
1841 # use no arguments
1842 Column("level", Integer)
1844 The ``type`` argument may be the second positional argument
1845 or specified by keyword.
1847 If the ``type`` is ``None`` or is omitted, it will first default to
1848 the special type :class:`.NullType`. If and when this
1849 :class:`_schema.Column` is made to refer to another column using
1850 :class:`_schema.ForeignKey` and/or
1851 :class:`_schema.ForeignKeyConstraint`, the type
1852 of the remote-referenced column will be copied to this column as
1853 well, at the moment that the foreign key is resolved against that
1854 remote :class:`_schema.Column` object.
1856 :param \*args: Additional positional arguments include various
1857 :class:`.SchemaItem` derived constructs which will be applied
1858 as options to the column. These include instances of
1859 :class:`.Constraint`, :class:`_schema.ForeignKey`,
1860 :class:`.ColumnDefault`, :class:`.Sequence`, :class:`.Computed`
1861 :class:`.Identity`. In some cases an
1862 equivalent keyword argument is available such as ``server_default``,
1863 ``default`` and ``unique``.
1865 :param autoincrement: Set up "auto increment" semantics for an
1866 **integer primary key column with no foreign key dependencies**
1867 (see later in this docstring for a more specific definition).
1868 This may influence the :term:`DDL` that will be emitted for
1869 this column during a table create, as well as how the column
1870 will be considered when INSERT statements are compiled and
1871 executed.
1873 The default value is the string ``"auto"``,
1874 which indicates that a single-column (i.e. non-composite) primary key
1875 that is of an INTEGER type with no other client-side or server-side
1876 default constructs indicated should receive auto increment semantics
1877 automatically. Other values include ``True`` (force this column to
1878 have auto-increment semantics for a :term:`composite primary key` as
1879 well), ``False`` (this column should never have auto-increment
1880 semantics), and the string ``"ignore_fk"`` (special-case for foreign
1881 key columns, see below).
1883 The term "auto increment semantics" refers both to the kind of DDL
1884 that will be emitted for the column within a CREATE TABLE statement,
1885 when methods such as :meth:`.MetaData.create_all` and
1886 :meth:`.Table.create` are invoked, as well as how the column will be
1887 considered when an INSERT statement is compiled and emitted to the
1888 database:
1890 * **DDL rendering** (i.e. :meth:`.MetaData.create_all`,
1891 :meth:`.Table.create`): When used on a :class:`.Column` that has
1892 no other
1893 default-generating construct associated with it (such as a
1894 :class:`.Sequence` or :class:`.Identity` construct), the parameter
1895 will imply that database-specific keywords such as PostgreSQL
1896 ``SERIAL``, MySQL ``AUTO_INCREMENT``, or ``IDENTITY`` on SQL Server
1897 should also be rendered. Not every database backend has an
1898 "implied" default generator available; for example the Oracle Database
1899 backends always needs an explicit construct such as
1900 :class:`.Identity` to be included with a :class:`.Column` in order
1901 for the DDL rendered to include auto-generating constructs to also
1902 be produced in the database.
1904 * **INSERT semantics** (i.e. when a :func:`_sql.insert` construct is
1905 compiled into a SQL string and is then executed on a database using
1906 :meth:`_engine.Connection.execute` or equivalent): A single-row
1907 INSERT statement will be known to produce a new integer primary key
1908 value automatically for this column, which will be accessible
1909 after the statement is invoked via the
1910 :attr:`.CursorResult.inserted_primary_key` attribute upon the
1911 :class:`_result.Result` object. This also applies towards use of the
1912 ORM when ORM-mapped objects are persisted to the database,
1913 indicating that a new integer primary key will be available to
1914 become part of the :term:`identity key` for that object. This
1915 behavior takes place regardless of what DDL constructs are
1916 associated with the :class:`_schema.Column` and is independent
1917 of the "DDL Rendering" behavior discussed in the previous note
1918 above.
1920 The parameter may be set to ``True`` to indicate that a column which
1921 is part of a composite (i.e. multi-column) primary key should
1922 have autoincrement semantics, though note that only one column
1923 within a primary key may have this setting. It can also
1924 be set to ``True`` to indicate autoincrement semantics on a
1925 column that has a client-side or server-side default configured,
1926 however note that not all dialects can accommodate all styles
1927 of default as an "autoincrement". It can also be
1928 set to ``False`` on a single-column primary key that has a
1929 datatype of INTEGER in order to disable auto increment semantics
1930 for that column.
1932 The setting *only* has an effect for columns which are:
1934 * Integer derived (i.e. INT, SMALLINT, BIGINT).
1936 * Part of the primary key
1938 * Not referring to another column via :class:`_schema.ForeignKey`,
1939 unless
1940 the value is specified as ``'ignore_fk'``::
1942 # turn on autoincrement for this column despite
1943 # the ForeignKey()
1944 Column(
1945 "id",
1946 ForeignKey("other.id"),
1947 primary_key=True,
1948 autoincrement="ignore_fk",
1949 )
1951 It is typically not desirable to have "autoincrement" enabled on a
1952 column that refers to another via foreign key, as such a column is
1953 required to refer to a value that originates from elsewhere.
1955 The setting has these effects on columns that meet the
1956 above criteria:
1958 * DDL issued for the column, if the column does not already include
1959 a default generating construct supported by the backend such as
1960 :class:`.Identity`, will include database-specific
1961 keywords intended to signify this column as an
1962 "autoincrement" column for specific backends. Behavior for
1963 primary SQLAlchemy dialects includes:
1965 * AUTO INCREMENT on MySQL and MariaDB
1966 * SERIAL on PostgreSQL
1967 * IDENTITY on MS-SQL - this occurs even without the
1968 :class:`.Identity` construct as the
1969 :paramref:`.Column.autoincrement` parameter pre-dates this
1970 construct.
1971 * SQLite - SQLite integer primary key columns are implicitly
1972 "auto incrementing" and no additional keywords are rendered;
1973 to render the special SQLite keyword ``AUTOINCREMENT``
1974 is not included as this is unnecessary and not recommended
1975 by the database vendor. See the section
1976 :ref:`sqlite_autoincrement` for more background.
1977 * Oracle Database - The Oracle Database dialects have no default "autoincrement"
1978 feature available at this time, instead the :class:`.Identity`
1979 construct is recommended to achieve this (the :class:`.Sequence`
1980 construct may also be used).
1981 * Third-party dialects - consult those dialects' documentation
1982 for details on their specific behaviors.
1984 * When a single-row :func:`_sql.insert` construct is compiled and
1985 executed, which does not set the :meth:`_sql.Insert.inline`
1986 modifier, newly generated primary key values for this column
1987 will be automatically retrieved upon statement execution
1988 using a method specific to the database driver in use:
1990 * MySQL, SQLite - calling upon ``cursor.lastrowid()``
1991 (see
1992 `https://www.python.org/dev/peps/pep-0249/#lastrowid
1993 <https://www.python.org/dev/peps/pep-0249/#lastrowid>`_)
1994 * PostgreSQL, SQL Server, Oracle Database - use RETURNING or an equivalent
1995 construct when rendering an INSERT statement, and then retrieving
1996 the newly generated primary key values after execution
1997 * PostgreSQL, Oracle Database for :class:`_schema.Table` objects that
1998 set :paramref:`_schema.Table.implicit_returning` to False -
1999 for a :class:`.Sequence` only, the :class:`.Sequence` is invoked
2000 explicitly before the INSERT statement takes place so that the
2001 newly generated primary key value is available to the client
2002 * SQL Server for :class:`_schema.Table` objects that
2003 set :paramref:`_schema.Table.implicit_returning` to False -
2004 the ``SELECT scope_identity()`` construct is used after the
2005 INSERT statement is invoked to retrieve the newly generated
2006 primary key value.
2007 * Third-party dialects - consult those dialects' documentation
2008 for details on their specific behaviors.
2010 * For multiple-row :func:`_sql.insert` constructs invoked with
2011 a list of parameters (i.e. "executemany" semantics), primary-key
2012 retrieving behaviors are generally disabled, however there may
2013 be special APIs that may be used to retrieve lists of new
2014 primary key values for an "executemany", such as the psycopg2
2015 "fast insertmany" feature. Such features are very new and
2016 may not yet be well covered in documentation.
2018 :param default: A scalar, Python callable, or
2019 :class:`_expression.ColumnElement` expression representing the
2020 *default value* for this column, which will be invoked upon insert
2021 if this column is otherwise not specified in the VALUES clause of
2022 the insert. This is a shortcut to using :class:`.ColumnDefault` as
2023 a positional argument; see that class for full detail on the
2024 structure of the argument.
2026 Contrast this argument to
2027 :paramref:`_schema.Column.server_default`
2028 which creates a default generator on the database side.
2030 .. seealso::
2032 :ref:`metadata_defaults_toplevel`
2034 :param insert_default: An alias of :paramref:`.Column.default`
2035 for compatibility with :func:`_orm.mapped_column`.
2037 .. versionadded:: 2.0.31
2039 :param doc: optional String that can be used by the ORM or similar
2040 to document attributes on the Python side. This attribute does
2041 **not** render SQL comments; use the
2042 :paramref:`_schema.Column.comment`
2043 parameter for this purpose.
2045 :param key: An optional string identifier which will identify this
2046 ``Column`` object on the :class:`_schema.Table`.
2047 When a key is provided,
2048 this is the only identifier referencing the ``Column`` within the
2049 application, including ORM attribute mapping; the ``name`` field
2050 is used only when rendering SQL.
2052 :param index: When ``True``, indicates that a :class:`_schema.Index`
2053 construct will be automatically generated for this
2054 :class:`_schema.Column`, which will result in a "CREATE INDEX"
2055 statement being emitted for the :class:`_schema.Table` when the DDL
2056 create operation is invoked.
2058 Using this flag is equivalent to making use of the
2059 :class:`_schema.Index` construct explicitly at the level of the
2060 :class:`_schema.Table` construct itself::
2062 Table(
2063 "some_table",
2064 metadata,
2065 Column("x", Integer),
2066 Index("ix_some_table_x", "x"),
2067 )
2069 To add the :paramref:`_schema.Index.unique` flag to the
2070 :class:`_schema.Index`, set both the
2071 :paramref:`_schema.Column.unique` and
2072 :paramref:`_schema.Column.index` flags to True simultaneously,
2073 which will have the effect of rendering the "CREATE UNIQUE INDEX"
2074 DDL instruction instead of "CREATE INDEX".
2076 The name of the index is generated using the
2077 :ref:`default naming convention <constraint_default_naming_convention>`
2078 which for the :class:`_schema.Index` construct is of the form
2079 ``ix_<tablename>_<columnname>``.
2081 As this flag is intended only as a convenience for the common case
2082 of adding a single-column, default configured index to a table
2083 definition, explicit use of the :class:`_schema.Index` construct
2084 should be preferred for most use cases, including composite indexes
2085 that encompass more than one column, indexes with SQL expressions
2086 or ordering, backend-specific index configuration options, and
2087 indexes that use a specific name.
2089 .. note:: the :attr:`_schema.Column.index` attribute on
2090 :class:`_schema.Column`
2091 **does not indicate** if this column is indexed or not, only
2092 if this flag was explicitly set here. To view indexes on
2093 a column, view the :attr:`_schema.Table.indexes` collection
2094 or use :meth:`_reflection.Inspector.get_indexes`.
2096 .. seealso::
2098 :ref:`schema_indexes`
2100 :ref:`constraint_naming_conventions`
2102 :paramref:`_schema.Column.unique`
2104 :param info: Optional data dictionary which will be populated into the
2105 :attr:`.SchemaItem.info` attribute of this object.
2107 :param nullable: When set to ``False``, will cause the "NOT NULL"
2108 phrase to be added when generating DDL for the column. When
2109 ``True``, will normally generate nothing (in SQL this defaults to
2110 "NULL"), except in some very specific backend-specific edge cases
2111 where "NULL" may render explicitly.
2112 Defaults to ``True`` unless :paramref:`_schema.Column.primary_key`
2113 is also ``True`` or the column specifies a :class:`_sql.Identity`,
2114 in which case it defaults to ``False``.
2115 This parameter is only used when issuing CREATE TABLE statements.
2117 .. note::
2119 When the column specifies a :class:`_sql.Identity` this
2120 parameter is in general ignored by the DDL compiler. The
2121 PostgreSQL database allows nullable identity column by
2122 setting this parameter to ``True`` explicitly.
2124 :param onupdate: A scalar, Python callable, or
2125 :class:`~sqlalchemy.sql.expression.ClauseElement` representing a
2126 default value to be applied to the column within UPDATE
2127 statements, which will be invoked upon update if this column is not
2128 present in the SET clause of the update. This is a shortcut to
2129 using :class:`.ColumnDefault` as a positional argument with
2130 ``for_update=True``.
2132 .. seealso::
2134 :ref:`metadata_defaults` - complete discussion of onupdate
2136 :param primary_key: If ``True``, marks this column as a primary key
2137 column. Multiple columns can have this flag set to specify
2138 composite primary keys. As an alternative, the primary key of a
2139 :class:`_schema.Table` can be specified via an explicit
2140 :class:`.PrimaryKeyConstraint` object.
2142 :param server_default: A :class:`.FetchedValue` instance, str, Unicode
2143 or :func:`~sqlalchemy.sql.expression.text` construct representing
2144 the DDL DEFAULT value for the column.
2146 String types will be emitted as-is, surrounded by single quotes::
2148 Column("x", Text, server_default="val")
2150 will render:
2152 .. sourcecode:: sql
2154 x TEXT DEFAULT 'val'
2156 A :func:`~sqlalchemy.sql.expression.text` expression will be
2157 rendered as-is, without quotes::
2159 Column("y", DateTime, server_default=text("NOW()"))
2161 will render:
2163 .. sourcecode:: sql
2165 y DATETIME DEFAULT NOW()
2167 Strings and text() will be converted into a
2168 :class:`.DefaultClause` object upon initialization.
2170 This parameter can also accept complex combinations of contextually
2171 valid SQLAlchemy expressions or constructs::
2173 from sqlalchemy import create_engine
2174 from sqlalchemy import Table, Column, MetaData, ARRAY, Text
2175 from sqlalchemy.dialects.postgresql import array
2177 engine = create_engine(
2178 "postgresql+psycopg2://scott:tiger@localhost/mydatabase"
2179 )
2180 metadata_obj = MetaData()
2181 tbl = Table(
2182 "foo",
2183 metadata_obj,
2184 Column(
2185 "bar", ARRAY(Text), server_default=array(["biz", "bang", "bash"])
2186 ),
2187 )
2188 metadata_obj.create_all(engine)
2190 The above results in a table created with the following SQL:
2192 .. sourcecode:: sql
2194 CREATE TABLE foo (
2195 bar TEXT[] DEFAULT ARRAY['biz', 'bang', 'bash']
2196 )
2198 Use :class:`.FetchedValue` to indicate that an already-existing
2199 column will generate a default value on the database side which
2200 will be available to SQLAlchemy for post-fetch after inserts. This
2201 construct does not specify any DDL and the implementation is left
2202 to the database, such as via a trigger.
2204 .. seealso::
2206 :ref:`server_defaults` - complete discussion of server side
2207 defaults
2209 :param server_onupdate: A :class:`.FetchedValue` instance
2210 representing a database-side default generation function,
2211 such as a trigger. This
2212 indicates to SQLAlchemy that a newly generated value will be
2213 available after updates. This construct does not actually
2214 implement any kind of generation function within the database,
2215 which instead must be specified separately.
2218 .. warning:: This directive **does not** currently produce MySQL's
2219 "ON UPDATE CURRENT_TIMESTAMP()" clause. See
2220 :ref:`mysql_timestamp_onupdate` for background on how to
2221 produce this clause.
2223 .. seealso::
2225 :ref:`triggered_columns`
2227 :param quote: Force quoting of this column's name on or off,
2228 corresponding to ``True`` or ``False``. When left at its default
2229 of ``None``, the column identifier will be quoted according to
2230 whether the name is case sensitive (identifiers with at least one
2231 upper case character are treated as case sensitive), or if it's a
2232 reserved word. This flag is only needed to force quoting of a
2233 reserved word which is not known by the SQLAlchemy dialect.
2235 :param unique: When ``True``, and the :paramref:`_schema.Column.index`
2236 parameter is left at its default value of ``False``,
2237 indicates that a :class:`_schema.UniqueConstraint`
2238 construct will be automatically generated for this
2239 :class:`_schema.Column`,
2240 which will result in a "UNIQUE CONSTRAINT" clause referring
2241 to this column being included
2242 in the ``CREATE TABLE`` statement emitted, when the DDL create
2243 operation for the :class:`_schema.Table` object is invoked.
2245 When this flag is ``True`` while the
2246 :paramref:`_schema.Column.index` parameter is simultaneously
2247 set to ``True``, the effect instead is that a
2248 :class:`_schema.Index` construct which includes the
2249 :paramref:`_schema.Index.unique` parameter set to ``True``
2250 is generated. See the documentation for
2251 :paramref:`_schema.Column.index` for additional detail.
2253 Using this flag is equivalent to making use of the
2254 :class:`_schema.UniqueConstraint` construct explicitly at the
2255 level of the :class:`_schema.Table` construct itself::
2257 Table("some_table", metadata, Column("x", Integer), UniqueConstraint("x"))
2259 The :paramref:`_schema.UniqueConstraint.name` parameter
2260 of the unique constraint object is left at its default value
2261 of ``None``; in the absence of a :ref:`naming convention <constraint_naming_conventions>`
2262 for the enclosing :class:`_schema.MetaData`, the UNIQUE CONSTRAINT
2263 construct will be emitted as unnamed, which typically invokes
2264 a database-specific naming convention to take place.
2266 As this flag is intended only as a convenience for the common case
2267 of adding a single-column, default configured unique constraint to a table
2268 definition, explicit use of the :class:`_schema.UniqueConstraint` construct
2269 should be preferred for most use cases, including composite constraints
2270 that encompass more than one column, backend-specific index configuration options, and
2271 constraints that use a specific name.
2273 .. note:: the :attr:`_schema.Column.unique` attribute on
2274 :class:`_schema.Column`
2275 **does not indicate** if this column has a unique constraint or
2276 not, only if this flag was explicitly set here. To view
2277 indexes and unique constraints that may involve this column,
2278 view the
2279 :attr:`_schema.Table.indexes` and/or
2280 :attr:`_schema.Table.constraints` collections or use
2281 :meth:`_reflection.Inspector.get_indexes` and/or
2282 :meth:`_reflection.Inspector.get_unique_constraints`
2284 .. seealso::
2286 :ref:`schema_unique_constraint`
2288 :ref:`constraint_naming_conventions`
2290 :paramref:`_schema.Column.index`
2292 :param system: When ``True``, indicates this is a "system" column,
2293 that is a column which is automatically made available by the
2294 database, and should not be included in the columns list for a
2295 ``CREATE TABLE`` statement.
2297 For more elaborate scenarios where columns should be
2298 conditionally rendered differently on different backends,
2299 consider custom compilation rules for :class:`.CreateColumn`.
2301 :param comment: Optional string that will render an SQL comment on
2302 table creation.
2304 :param insert_sentinel: Marks this :class:`_schema.Column` as an
2305 :term:`insert sentinel` used for optimizing the performance of the
2306 :term:`insertmanyvalues` feature for tables that don't
2307 otherwise have qualifying primary key configurations.
2309 .. versionadded:: 2.0.10
2311 .. seealso::
2313 :func:`_schema.insert_sentinel` - all in one helper for declaring
2314 sentinel columns
2316 :ref:`engine_insertmanyvalues`
2318 :ref:`engine_insertmanyvalues_sentinel_columns`
2321 """ # noqa: E501, RST201, RST202
2323 l_args = [__name_pos, __type_pos] + list(args)
2324 del args
2326 if isinstance(l_args[0], str):
2327 if name is not None:
2328 raise exc.ArgumentError(
2329 "May not pass name positionally and as a keyword."
2330 )
2331 name = l_args.pop(0) # type: ignore
2332 elif l_args[0] is None:
2333 l_args.pop(0)
2334 if l_args:
2335 coltype = l_args[0]
2337 if hasattr(coltype, "_sqla_type"):
2338 if type_ is not None:
2339 raise exc.ArgumentError(
2340 "May not pass type_ positionally and as a keyword."
2341 )
2342 type_ = l_args.pop(0) # type: ignore
2343 elif l_args[0] is None:
2344 l_args.pop(0)
2346 if name is not None:
2347 name = quoted_name(name, quote)
2348 elif quote is not None:
2349 raise exc.ArgumentError(
2350 "Explicit 'name' is required when sending 'quote' argument"
2351 )
2353 # name = None is expected to be an interim state
2354 # note this use case is legacy now that ORM declarative has a
2355 # dedicated "column" construct local to the ORM
2356 super().__init__(name, type_) # type: ignore
2358 self.key = key if key is not None else name # type: ignore
2359 self.primary_key = primary_key
2360 self._insert_sentinel = insert_sentinel
2361 self._omit_from_statements = _omit_from_statements
2362 self._user_defined_nullable = udn = nullable
2363 if udn is not NULL_UNSPECIFIED:
2364 self.nullable = udn
2365 else:
2366 self.nullable = not primary_key
2368 # these default to None because .index and .unique is *not*
2369 # an informational flag about Column - there can still be an
2370 # Index or UniqueConstraint referring to this Column.
2371 self.index = index
2372 self.unique = unique
2374 self.system = system
2375 self.doc = doc
2376 self.autoincrement: _AutoIncrementType = autoincrement
2377 self.constraints = set()
2378 self.foreign_keys = set()
2379 self.comment = comment
2380 self.computed = None
2381 self.identity = None
2383 # check if this Column is proxying another column
2385 if _proxies is not None:
2386 self._proxies = _proxies
2387 else:
2388 # otherwise, add DDL-related events
2389 self._set_type(self.type)
2391 if insert_default is not _NoArg.NO_ARG:
2392 if default is not _NoArg.NO_ARG:
2393 raise exc.ArgumentError(
2394 "The 'default' and 'insert_default' parameters "
2395 "of Column are mutually exclusive"
2396 )
2397 resolved_default = insert_default
2398 elif default is not _NoArg.NO_ARG:
2399 resolved_default = default
2400 else:
2401 resolved_default = None
2403 if resolved_default is not None:
2404 if not isinstance(resolved_default, (ColumnDefault, Sequence)):
2405 resolved_default = ColumnDefault(resolved_default)
2407 self.default = resolved_default
2408 l_args.append(resolved_default)
2409 else:
2410 self.default = None
2412 if onupdate is not None:
2413 if not isinstance(onupdate, (ColumnDefault, Sequence)):
2414 onupdate = ColumnDefault(onupdate, for_update=True)
2416 self.onupdate = onupdate
2417 l_args.append(onupdate)
2418 else:
2419 self.onupdate = None
2421 if server_default is not None:
2422 if isinstance(server_default, FetchedValue):
2423 server_default = server_default._as_for_update(False)
2424 l_args.append(server_default)
2425 else:
2426 server_default = DefaultClause(server_default)
2427 l_args.append(server_default)
2428 self.server_default = server_default
2430 if server_onupdate is not None:
2431 if isinstance(server_onupdate, FetchedValue):
2432 server_onupdate = server_onupdate._as_for_update(True)
2433 l_args.append(server_onupdate)
2434 else:
2435 server_onupdate = DefaultClause(
2436 server_onupdate, for_update=True
2437 )
2438 l_args.append(server_onupdate)
2439 self.server_onupdate = server_onupdate
2441 self._init_items(*cast(_typing_Sequence[SchemaItem], l_args))
2443 util.set_creation_order(self)
2445 if info is not None:
2446 self.info = info
2448 self._extra_kwargs(**dialect_kwargs)
2450 table: Table
2452 constraints: Set[Constraint]
2454 foreign_keys: Set[ForeignKey]
2455 """A collection of all :class:`_schema.ForeignKey` marker objects
2456 associated with this :class:`_schema.Column`.
2458 Each object is a member of a :class:`_schema.Table`-wide
2459 :class:`_schema.ForeignKeyConstraint`.
2461 .. seealso::
2463 :attr:`_schema.Table.foreign_keys`
2465 """
2467 index: Optional[bool]
2468 """The value of the :paramref:`_schema.Column.index` parameter.
2470 Does not indicate if this :class:`_schema.Column` is actually indexed
2471 or not; use :attr:`_schema.Table.indexes`.
2473 .. seealso::
2475 :attr:`_schema.Table.indexes`
2476 """
2478 unique: Optional[bool]
2479 """The value of the :paramref:`_schema.Column.unique` parameter.
2481 Does not indicate if this :class:`_schema.Column` is actually subject to
2482 a unique constraint or not; use :attr:`_schema.Table.indexes` and
2483 :attr:`_schema.Table.constraints`.
2485 .. seealso::
2487 :attr:`_schema.Table.indexes`
2489 :attr:`_schema.Table.constraints`.
2491 """
2493 computed: Optional[Computed]
2495 identity: Optional[Identity]
2497 def _set_type(self, type_: TypeEngine[Any]) -> None:
2498 assert self.type._isnull or type_ is self.type
2500 self.type = type_
2501 if isinstance(self.type, SchemaEventTarget):
2502 self.type._set_parent_with_dispatch(self)
2503 for impl in self.type._variant_mapping.values():
2504 if isinstance(impl, SchemaEventTarget):
2505 impl._set_parent_with_dispatch(self)
2507 @HasMemoized.memoized_attribute
2508 def _default_description_tuple(self) -> _DefaultDescriptionTuple:
2509 """used by default.py -> _process_execute_defaults()"""
2511 return _DefaultDescriptionTuple._from_column_default(self.default)
2513 @HasMemoized.memoized_attribute
2514 def _onupdate_description_tuple(self) -> _DefaultDescriptionTuple:
2515 """used by default.py -> _process_execute_defaults()"""
2516 return _DefaultDescriptionTuple._from_column_default(self.onupdate)
2518 @util.memoized_property
2519 def _gen_static_annotations_cache_key(self) -> bool:
2520 """special attribute used by cache key gen, if true, we will
2521 use a static cache key for the annotations dictionary, else we
2522 will generate a new cache key for annotations each time.
2524 Added for #8790
2526 """
2527 return self.table is not None and self.table._is_table
2529 def _extra_kwargs(self, **kwargs: Any) -> None:
2530 self._validate_dialect_kwargs(kwargs)
2532 def __str__(self) -> str:
2533 if self.name is None:
2534 return "(no name)"
2535 elif self.table is not None:
2536 if self.table.named_with_column:
2537 return self.table.description + "." + self.description
2538 else:
2539 return self.description
2540 else:
2541 return self.description
2543 def references(self, column: Column[Any]) -> bool:
2544 """Return True if this Column references the given column via foreign
2545 key."""
2547 for fk in self.foreign_keys:
2548 if fk.column.proxy_set.intersection(column.proxy_set):
2549 return True
2550 else:
2551 return False
2553 def append_foreign_key(self, fk: ForeignKey) -> None:
2554 fk._set_parent_with_dispatch(self)
2556 def __repr__(self) -> str:
2557 kwarg = []
2558 if self.key != self.name:
2559 kwarg.append("key")
2560 if self.primary_key:
2561 kwarg.append("primary_key")
2562 if not self.nullable:
2563 kwarg.append("nullable")
2564 if self.onupdate:
2565 kwarg.append("onupdate")
2566 if self.default:
2567 kwarg.append("default")
2568 if self.server_default:
2569 kwarg.append("server_default")
2570 if self.comment:
2571 kwarg.append("comment")
2572 return "Column(%s)" % ", ".join(
2573 [repr(self.name)]
2574 + [repr(self.type)]
2575 + [repr(x) for x in self.foreign_keys if x is not None]
2576 + [repr(x) for x in self.constraints]
2577 + [
2578 (
2579 self.table is not None
2580 and "table=<%s>" % self.table.description
2581 or "table=None"
2582 )
2583 ]
2584 + ["%s=%s" % (k, repr(getattr(self, k))) for k in kwarg]
2585 )
2587 def _set_parent( # type: ignore[override]
2588 self,
2589 parent: SchemaEventTarget,
2590 *,
2591 all_names: Dict[str, Column[Any]],
2592 allow_replacements: bool,
2593 index: Optional[int] = None,
2594 **kw: Any,
2595 ) -> None:
2596 table = parent
2597 assert isinstance(table, Table)
2598 if not self.name:
2599 raise exc.ArgumentError(
2600 "Column must be constructed with a non-blank name or "
2601 "assign a non-blank .name before adding to a Table."
2602 )
2604 self._reset_memoizations()
2606 if self.key is None:
2607 self.key = self.name
2609 existing = getattr(self, "table", None)
2610 if existing is not None and existing is not table:
2611 raise exc.ArgumentError(
2612 f"Column object '{self.key}' already "
2613 f"assigned to Table '{existing.description}'"
2614 )
2616 extra_remove = None
2617 existing_col = None
2618 conflicts_on = ""
2620 if self.key in table._columns:
2621 existing_col = table._columns[self.key]
2622 if self.key == self.name:
2623 conflicts_on = "name"
2624 else:
2625 conflicts_on = "key"
2626 elif self.name in all_names:
2627 existing_col = all_names[self.name]
2628 extra_remove = {existing_col}
2629 conflicts_on = "name"
2631 if existing_col is not None:
2632 if existing_col is not self:
2633 if not allow_replacements:
2634 raise exc.DuplicateColumnError(
2635 f"A column with {conflicts_on} "
2636 f"""'{
2637 self.key if conflicts_on == 'key' else self.name
2638 }' """
2639 f"is already present in table '{table.name}'."
2640 )
2641 for fk in existing_col.foreign_keys:
2642 table.foreign_keys.remove(fk)
2643 if fk.constraint in table.constraints:
2644 # this might have been removed
2645 # already, if it's a composite constraint
2646 # and more than one col being replaced
2647 table.constraints.remove(fk.constraint)
2649 if extra_remove and existing_col is not None and self.key == self.name:
2650 util.warn(
2651 f'Column with user-specified key "{existing_col.key}" is '
2652 "being replaced with "
2653 f'plain named column "{self.name}", '
2654 f'key "{existing_col.key}" is being removed. If this is a '
2655 "reflection operation, specify autoload_replace=False to "
2656 "prevent this replacement."
2657 )
2658 table._columns.replace(self, extra_remove=extra_remove, index=index)
2659 all_names[self.name] = self
2660 self.table = table
2662 if self._insert_sentinel:
2663 if self.table._sentinel_column is not None:
2664 raise exc.ArgumentError(
2665 "a Table may have only one explicit sentinel column"
2666 )
2667 self.table._sentinel_column = self
2669 if self.primary_key:
2670 table.primary_key._replace(self)
2671 elif self.key in table.primary_key:
2672 raise exc.ArgumentError(
2673 f"Trying to redefine primary-key column '{self.key}' as a "
2674 f"non-primary-key column on table '{table.fullname}'"
2675 )
2677 if self.index:
2678 if isinstance(self.index, str):
2679 raise exc.ArgumentError(
2680 "The 'index' keyword argument on Column is boolean only. "
2681 "To create indexes with a specific name, create an "
2682 "explicit Index object external to the Table."
2683 )
2684 table.append_constraint(
2685 Index(
2686 None, self.key, unique=bool(self.unique), _column_flag=True
2687 )
2688 )
2690 elif self.unique:
2691 if isinstance(self.unique, str):
2692 raise exc.ArgumentError(
2693 "The 'unique' keyword argument on Column is boolean "
2694 "only. To create unique constraints or indexes with a "
2695 "specific name, append an explicit UniqueConstraint to "
2696 "the Table's list of elements, or create an explicit "
2697 "Index object external to the Table."
2698 )
2699 table.append_constraint(
2700 UniqueConstraint(self.key, _column_flag=True)
2701 )
2703 self._setup_on_memoized_fks(lambda fk: fk._set_remote_table(table))
2705 if self.identity and (
2706 isinstance(self.default, Sequence)
2707 or isinstance(self.onupdate, Sequence)
2708 ):
2709 raise exc.ArgumentError(
2710 "An column cannot specify both Identity and Sequence."
2711 )
2713 def _setup_on_memoized_fks(self, fn: Callable[..., Any]) -> None:
2714 fk_keys = [
2715 ((self.table.key, self.key), False),
2716 ((self.table.key, self.name), True),
2717 ]
2718 for fk_key, link_to_name in fk_keys:
2719 if fk_key in self.table.metadata._fk_memos:
2720 for fk in self.table.metadata._fk_memos[fk_key]:
2721 if fk.link_to_name is link_to_name:
2722 fn(fk)
2724 def _on_table_attach(self, fn: Callable[..., Any]) -> None:
2725 if self.table is not None:
2726 fn(self, self.table)
2727 else:
2728 event.listen(self, "after_parent_attach", fn)
2730 @util.deprecated(
2731 "1.4",
2732 "The :meth:`_schema.Column.copy` method is deprecated "
2733 "and will be removed in a future release.",
2734 )
2735 def copy(self, **kw: Any) -> Column[Any]:
2736 return self._copy(**kw)
2738 def _copy(self, **kw: Any) -> Column[Any]:
2739 """Create a copy of this ``Column``, uninitialized.
2741 This is used in :meth:`_schema.Table.to_metadata` and by the ORM.
2743 """
2745 # Constraint objects plus non-constraint-bound ForeignKey objects
2746 args: List[SchemaItem] = [
2747 c._copy(**kw) for c in self.constraints if not c._type_bound
2748 ] + [c._copy(**kw) for c in self.foreign_keys if not c.constraint]
2750 # ticket #5276
2751 column_kwargs = {}
2752 for dialect_name in self.dialect_options:
2753 dialect_options = self.dialect_options[dialect_name]._non_defaults
2754 for (
2755 dialect_option_key,
2756 dialect_option_value,
2757 ) in dialect_options.items():
2758 column_kwargs[dialect_name + "_" + dialect_option_key] = (
2759 dialect_option_value
2760 )
2762 server_default = self.server_default
2763 server_onupdate = self.server_onupdate
2764 if isinstance(server_default, (Computed, Identity)):
2765 # TODO: likely should be copied in all cases
2766 # TODO: if a Sequence, we would need to transfer the Sequence
2767 # .metadata as well
2768 args.append(server_default._copy(**kw))
2769 server_default = server_onupdate = None
2771 type_ = self.type
2772 if isinstance(type_, SchemaEventTarget):
2773 type_ = type_.copy(**kw)
2775 # TODO: DefaultGenerator is not copied here! it's just used again
2776 # with _set_parent() pointing to the old column. see the new
2777 # use of _copy() in the new _merge() method
2779 c = self._constructor(
2780 name=self.name,
2781 type_=type_,
2782 key=self.key,
2783 primary_key=self.primary_key,
2784 unique=self.unique,
2785 system=self.system,
2786 # quote=self.quote, # disabled 2013-08-27 (commit 031ef080)
2787 index=self.index,
2788 autoincrement=self.autoincrement,
2789 default=self.default,
2790 server_default=server_default,
2791 onupdate=self.onupdate,
2792 server_onupdate=server_onupdate,
2793 doc=self.doc,
2794 comment=self.comment,
2795 _omit_from_statements=self._omit_from_statements,
2796 insert_sentinel=self._insert_sentinel,
2797 *args,
2798 **column_kwargs,
2799 )
2801 # copy the state of "nullable" exactly, to accommodate for
2802 # ORM flipping the .nullable flag directly
2803 c.nullable = self.nullable
2804 c._user_defined_nullable = self._user_defined_nullable
2806 return self._schema_item_copy(c)
2808 def _merge(
2809 self, other: Column[Any], *, omit_defaults: bool = False
2810 ) -> None:
2811 """merge the elements of this column onto "other"
2813 this is used by ORM pep-593 merge and will likely need a lot
2814 of fixes.
2817 """
2819 if self.primary_key:
2820 other.primary_key = True
2822 if self.autoincrement != "auto" and other.autoincrement == "auto":
2823 other.autoincrement = self.autoincrement
2825 if self.system:
2826 other.system = self.system
2828 if self.info:
2829 other.info.update(self.info)
2831 type_ = self.type
2832 if not type_._isnull and other.type._isnull:
2833 if isinstance(type_, SchemaEventTarget):
2834 type_ = type_.copy()
2836 other.type = type_
2838 if isinstance(type_, SchemaEventTarget):
2839 type_._set_parent_with_dispatch(other)
2841 for impl in type_._variant_mapping.values():
2842 if isinstance(impl, SchemaEventTarget):
2843 impl._set_parent_with_dispatch(other)
2845 if (
2846 self._user_defined_nullable is not NULL_UNSPECIFIED
2847 and other._user_defined_nullable is NULL_UNSPECIFIED
2848 ):
2849 other.nullable = self.nullable
2850 other._user_defined_nullable = self._user_defined_nullable
2852 if (
2853 not omit_defaults
2854 and self.default is not None
2855 and other.default is None
2856 ):
2857 new_default = self.default._copy()
2858 new_default._set_parent(other)
2860 if self.server_default and other.server_default is None:
2861 new_server_default = self.server_default
2862 if isinstance(new_server_default, FetchedValue):
2863 new_server_default = new_server_default._copy()
2864 new_server_default._set_parent(other)
2865 else:
2866 other.server_default = new_server_default
2868 if self.server_onupdate and other.server_onupdate is None:
2869 new_server_onupdate = self.server_onupdate
2870 new_server_onupdate = new_server_onupdate._copy()
2871 new_server_onupdate._set_parent(other)
2873 if self.onupdate and other.onupdate is None:
2874 new_onupdate = self.onupdate._copy()
2875 new_onupdate._set_parent(other)
2877 if self.index in (True, False) and other.index is None:
2878 other.index = self.index
2880 if self.unique in (True, False) and other.unique is None:
2881 other.unique = self.unique
2883 if self.doc and other.doc is None:
2884 other.doc = self.doc
2886 if self.comment and other.comment is None:
2887 other.comment = self.comment
2889 for const in self.constraints:
2890 if not const._type_bound:
2891 new_const = const._copy()
2892 new_const._set_parent(other)
2894 for fk in self.foreign_keys:
2895 if not fk.constraint:
2896 new_fk = fk._copy()
2897 new_fk._set_parent(other)
2899 def _make_proxy(
2900 self,
2901 selectable: FromClause,
2902 primary_key: ColumnSet,
2903 foreign_keys: Set[KeyedColumnElement[Any]],
2904 name: Optional[str] = None,
2905 key: Optional[str] = None,
2906 name_is_truncatable: bool = False,
2907 compound_select_cols: Optional[
2908 _typing_Sequence[ColumnElement[Any]]
2909 ] = None,
2910 **kw: Any,
2911 ) -> Tuple[str, ColumnClause[_T]]:
2912 """Create a *proxy* for this column.
2914 This is a copy of this ``Column`` referenced by a different parent
2915 (such as an alias or select statement). The column should
2916 be used only in select scenarios, as its full DDL/default
2917 information is not transferred.
2919 """
2921 fk = [
2922 ForeignKey(
2923 col if col is not None else f._colspec,
2924 _unresolvable=col is None,
2925 _constraint=f.constraint,
2926 )
2927 for f, col in [
2928 (fk, fk._resolve_column(raiseerr=False))
2929 for fk in self.foreign_keys
2930 ]
2931 ]
2933 if name is None and self.name is None:
2934 raise exc.InvalidRequestError(
2935 "Cannot initialize a sub-selectable"
2936 " with this Column object until its 'name' has "
2937 "been assigned."
2938 )
2939 try:
2940 c = self._constructor(
2941 (
2942 coercions.expect(
2943 roles.TruncatedLabelRole, name if name else self.name
2944 )
2945 if name_is_truncatable
2946 else (name or self.name)
2947 ),
2948 self.type,
2949 # this may actually be ._proxy_key when the key is incoming
2950 key=key if key else name if name else self.key,
2951 primary_key=self.primary_key,
2952 nullable=self.nullable,
2953 _proxies=(
2954 list(compound_select_cols)
2955 if compound_select_cols
2956 else [self]
2957 ),
2958 *fk,
2959 )
2960 except TypeError as err:
2961 raise TypeError(
2962 "Could not create a copy of this %r object. "
2963 "Ensure the class includes a _constructor() "
2964 "attribute or method which accepts the "
2965 "standard Column constructor arguments, or "
2966 "references the Column class itself." % self.__class__
2967 ) from err
2969 c.table = selectable
2970 c._propagate_attrs = selectable._propagate_attrs
2971 if selectable._is_clone_of is not None:
2972 c._is_clone_of = selectable._is_clone_of.columns.get(c.key)
2974 if self.primary_key:
2975 primary_key.add(c)
2977 if fk:
2978 foreign_keys.update(fk) # type: ignore
2980 return c.key, c
2983def insert_sentinel(
2984 name: Optional[str] = None,
2985 type_: Optional[_TypeEngineArgument[_T]] = None,
2986 *,
2987 default: Optional[Any] = None,
2988 omit_from_statements: bool = True,
2989) -> Column[Any]:
2990 """Provides a surrogate :class:`_schema.Column` that will act as a
2991 dedicated insert :term:`sentinel` column, allowing efficient bulk
2992 inserts with deterministic RETURNING sorting for tables that
2993 don't otherwise have qualifying primary key configurations.
2995 Adding this column to a :class:`.Table` object requires that a
2996 corresponding database table actually has this column present, so if adding
2997 it to an existing model, existing database tables would need to be migrated
2998 (e.g. using ALTER TABLE or similar) to include this column.
3000 For background on how this object is used, see the section
3001 :ref:`engine_insertmanyvalues_sentinel_columns` as part of the
3002 section :ref:`engine_insertmanyvalues`.
3004 The :class:`_schema.Column` returned will be a nullable integer column by
3005 default and make use of a sentinel-specific default generator used only in
3006 "insertmanyvalues" operations.
3008 .. seealso::
3010 :func:`_orm.orm_insert_sentinel`
3012 :paramref:`_schema.Column.insert_sentinel`
3014 :ref:`engine_insertmanyvalues`
3016 :ref:`engine_insertmanyvalues_sentinel_columns`
3019 .. versionadded:: 2.0.10
3021 """
3022 return Column(
3023 name=name,
3024 type_=type_api.INTEGERTYPE if type_ is None else type_,
3025 default=(
3026 default if default is not None else _InsertSentinelColumnDefault()
3027 ),
3028 _omit_from_statements=omit_from_statements,
3029 insert_sentinel=True,
3030 )
3033class ForeignKey(DialectKWArgs, SchemaItem):
3034 """Defines a dependency between two columns.
3036 ``ForeignKey`` is specified as an argument to a :class:`_schema.Column`
3037 object,
3038 e.g.::
3040 t = Table(
3041 "remote_table",
3042 metadata,
3043 Column("remote_id", ForeignKey("main_table.id")),
3044 )
3046 Note that ``ForeignKey`` is only a marker object that defines
3047 a dependency between two columns. The actual constraint
3048 is in all cases represented by the :class:`_schema.ForeignKeyConstraint`
3049 object. This object will be generated automatically when
3050 a ``ForeignKey`` is associated with a :class:`_schema.Column` which
3051 in turn is associated with a :class:`_schema.Table`. Conversely,
3052 when :class:`_schema.ForeignKeyConstraint` is applied to a
3053 :class:`_schema.Table`,
3054 ``ForeignKey`` markers are automatically generated to be
3055 present on each associated :class:`_schema.Column`, which are also
3056 associated with the constraint object.
3058 Note that you cannot define a "composite" foreign key constraint,
3059 that is a constraint between a grouping of multiple parent/child
3060 columns, using ``ForeignKey`` objects. To define this grouping,
3061 the :class:`_schema.ForeignKeyConstraint` object must be used, and applied
3062 to the :class:`_schema.Table`. The associated ``ForeignKey`` objects
3063 are created automatically.
3065 The ``ForeignKey`` objects associated with an individual
3066 :class:`_schema.Column`
3067 object are available in the `foreign_keys` collection
3068 of that column.
3070 Further examples of foreign key configuration are in
3071 :ref:`metadata_foreignkeys`.
3073 """
3075 __visit_name__ = "foreign_key"
3077 parent: Column[Any]
3079 _table_column: Optional[Column[Any]]
3081 _colspec: Union[str, Column[Any]]
3083 def __init__(
3084 self,
3085 column: _DDLColumnReferenceArgument,
3086 _constraint: Optional[ForeignKeyConstraint] = None,
3087 use_alter: bool = False,
3088 name: _ConstraintNameArgument = None,
3089 onupdate: Optional[str] = None,
3090 ondelete: Optional[str] = None,
3091 deferrable: Optional[bool] = None,
3092 initially: Optional[str] = None,
3093 link_to_name: bool = False,
3094 match: Optional[str] = None,
3095 info: Optional[_InfoType] = None,
3096 comment: Optional[str] = None,
3097 _unresolvable: bool = False,
3098 **dialect_kw: Any,
3099 ):
3100 r"""
3101 Construct a column-level FOREIGN KEY.
3103 The :class:`_schema.ForeignKey` object when constructed generates a
3104 :class:`_schema.ForeignKeyConstraint`
3105 which is associated with the parent
3106 :class:`_schema.Table` object's collection of constraints.
3108 :param column: A single target column for the key relationship. A
3109 :class:`_schema.Column` object or a column name as a string:
3110 ``tablename.columnkey`` or ``schema.tablename.columnkey``.
3111 ``columnkey`` is the ``key`` which has been assigned to the column
3112 (defaults to the column name itself), unless ``link_to_name`` is
3113 ``True`` in which case the rendered name of the column is used.
3115 :param name: Optional string. An in-database name for the key if
3116 `constraint` is not provided.
3118 :param onupdate: Optional string. If set, emit ON UPDATE <value> when
3119 issuing DDL for this constraint. Typical values include CASCADE,
3120 DELETE and RESTRICT.
3122 .. seealso::
3124 :ref:`on_update_on_delete`
3126 :param ondelete: Optional string. If set, emit ON DELETE <value> when
3127 issuing DDL for this constraint. Typical values include CASCADE,
3128 SET NULL and RESTRICT. Some dialects may allow for additional
3129 syntaxes.
3131 .. seealso::
3133 :ref:`on_update_on_delete`
3135 :param deferrable: Optional bool. If set, emit DEFERRABLE or NOT
3136 DEFERRABLE when issuing DDL for this constraint.
3138 :param initially: Optional string. If set, emit INITIALLY <value> when
3139 issuing DDL for this constraint.
3141 :param link_to_name: if True, the string name given in ``column`` is
3142 the rendered name of the referenced column, not its locally
3143 assigned ``key``.
3145 :param use_alter: passed to the underlying
3146 :class:`_schema.ForeignKeyConstraint`
3147 to indicate the constraint should
3148 be generated/dropped externally from the CREATE TABLE/ DROP TABLE
3149 statement. See :paramref:`_schema.ForeignKeyConstraint.use_alter`
3150 for further description.
3152 .. seealso::
3154 :paramref:`_schema.ForeignKeyConstraint.use_alter`
3156 :ref:`use_alter`
3158 :param match: Optional string. If set, emit MATCH <value> when issuing
3159 DDL for this constraint. Typical values include SIMPLE, PARTIAL
3160 and FULL.
3162 :param info: Optional data dictionary which will be populated into the
3163 :attr:`.SchemaItem.info` attribute of this object.
3165 :param comment: Optional string that will render an SQL comment on
3166 foreign key constraint creation.
3168 .. versionadded:: 2.0
3170 :param \**dialect_kw: Additional keyword arguments are dialect
3171 specific, and passed in the form ``<dialectname>_<argname>``. The
3172 arguments are ultimately handled by a corresponding
3173 :class:`_schema.ForeignKeyConstraint`.
3174 See the documentation regarding
3175 an individual dialect at :ref:`dialect_toplevel` for detail on
3176 documented arguments.
3178 """
3180 self._unresolvable = _unresolvable
3182 self._colspec, self._table_column = self._parse_colspec_argument(
3183 column
3184 )
3186 # the linked ForeignKeyConstraint.
3187 # ForeignKey will create this when parent Column
3188 # is attached to a Table, *or* ForeignKeyConstraint
3189 # object passes itself in when creating ForeignKey
3190 # markers.
3191 self.constraint = _constraint
3193 # .parent is not Optional under normal use
3194 self.parent = None # type: ignore
3196 self.use_alter = use_alter
3197 self.name = name
3198 self.onupdate = onupdate
3199 self.ondelete = ondelete
3200 self.deferrable = deferrable
3201 self.initially = initially
3202 self.link_to_name = link_to_name
3203 self.match = match
3204 self.comment = comment
3205 if info:
3206 self.info = info
3207 self._unvalidated_dialect_kw = dialect_kw
3209 def _resolve_colspec_argument(
3210 self,
3211 ) -> Tuple[
3212 Union[str, Column[Any]],
3213 Optional[Column[Any]],
3214 ]:
3215 argument = self._colspec
3217 return self._parse_colspec_argument(argument)
3219 def _parse_colspec_argument(
3220 self,
3221 argument: _DDLColumnArgument,
3222 ) -> Tuple[
3223 Union[str, Column[Any]],
3224 Optional[Column[Any]],
3225 ]:
3226 _colspec = coercions.expect(roles.DDLReferredColumnRole, argument)
3228 if isinstance(_colspec, str):
3229 _table_column = None
3230 else:
3231 assert isinstance(_colspec, ColumnClause)
3232 _table_column = _colspec
3234 if not isinstance(_table_column.table, (type(None), TableClause)):
3235 raise exc.ArgumentError(
3236 "ForeignKey received Column not bound "
3237 "to a Table, got: %r" % _table_column.table
3238 )
3240 return _colspec, _table_column
3242 def __repr__(self) -> str:
3243 return "ForeignKey(%r)" % self._get_colspec()
3245 @util.deprecated(
3246 "1.4",
3247 "The :meth:`_schema.ForeignKey.copy` method is deprecated "
3248 "and will be removed in a future release.",
3249 )
3250 def copy(self, *, schema: Optional[str] = None, **kw: Any) -> ForeignKey:
3251 return self._copy(schema=schema, **kw)
3253 def _copy(self, *, schema: Optional[str] = None, **kw: Any) -> ForeignKey:
3254 """Produce a copy of this :class:`_schema.ForeignKey` object.
3256 The new :class:`_schema.ForeignKey` will not be bound
3257 to any :class:`_schema.Column`.
3259 This method is usually used by the internal
3260 copy procedures of :class:`_schema.Column`, :class:`_schema.Table`,
3261 and :class:`_schema.MetaData`.
3263 :param schema: The returned :class:`_schema.ForeignKey` will
3264 reference the original table and column name, qualified
3265 by the given string schema name.
3267 """
3268 fk = ForeignKey(
3269 self._get_colspec(schema=schema),
3270 use_alter=self.use_alter,
3271 name=self.name,
3272 onupdate=self.onupdate,
3273 ondelete=self.ondelete,
3274 deferrable=self.deferrable,
3275 initially=self.initially,
3276 link_to_name=self.link_to_name,
3277 match=self.match,
3278 comment=self.comment,
3279 **self._unvalidated_dialect_kw,
3280 )
3281 return self._schema_item_copy(fk)
3283 def _get_colspec(
3284 self,
3285 schema: Optional[
3286 Union[
3287 str,
3288 Literal[SchemaConst.RETAIN_SCHEMA, SchemaConst.BLANK_SCHEMA],
3289 ]
3290 ] = None,
3291 table_name: Optional[str] = None,
3292 _is_copy: bool = False,
3293 ) -> str:
3294 """Return a string based 'column specification' for this
3295 :class:`_schema.ForeignKey`.
3297 This is usually the equivalent of the string-based "tablename.colname"
3298 argument first passed to the object's constructor.
3300 """
3302 _colspec, effective_table_column = self._resolve_colspec_argument()
3304 if schema not in (None, RETAIN_SCHEMA):
3305 _schema, tname, colname = self._column_tokens
3306 if table_name is not None:
3307 tname = table_name
3308 if schema is BLANK_SCHEMA:
3309 return "%s.%s" % (tname, colname)
3310 else:
3311 return "%s.%s.%s" % (schema, tname, colname)
3312 elif table_name:
3313 schema, tname, colname = self._column_tokens
3314 if schema:
3315 return "%s.%s.%s" % (schema, table_name, colname)
3316 else:
3317 return "%s.%s" % (table_name, colname)
3318 elif effective_table_column is not None:
3319 if effective_table_column.table is None:
3320 if _is_copy:
3321 raise exc.InvalidRequestError(
3322 f"Can't copy ForeignKey object which refers to "
3323 f"non-table bound Column {effective_table_column!r}"
3324 )
3325 else:
3326 return effective_table_column.key
3327 return "%s.%s" % (
3328 effective_table_column.table.fullname,
3329 effective_table_column.key,
3330 )
3331 else:
3332 assert isinstance(_colspec, str)
3333 return _colspec
3335 @property
3336 def _referred_schema(self) -> Optional[str]:
3337 return self._column_tokens[0]
3339 def _table_key_within_construction(self) -> Any:
3340 """get the table key but only safely"""
3342 if self._table_column is not None:
3343 if self._table_column.table is None:
3344 return None
3345 else:
3346 return self._table_column.table.key
3347 else:
3348 schema, tname, colname = self._column_tokens
3349 return _get_table_key(tname, schema)
3351 target_fullname = property(_get_colspec)
3353 def references(self, table: Table) -> bool:
3354 """Return True if the given :class:`_schema.Table`
3355 is referenced by this
3356 :class:`_schema.ForeignKey`."""
3358 return table.corresponding_column(self.column) is not None
3360 def get_referent(self, table: FromClause) -> Optional[Column[Any]]:
3361 """Return the :class:`_schema.Column` in the given
3362 :class:`_schema.Table` (or any :class:`.FromClause`)
3363 referenced by this :class:`_schema.ForeignKey`.
3365 Returns None if this :class:`_schema.ForeignKey`
3366 does not reference the given
3367 :class:`_schema.Table`.
3369 """
3370 # our column is a Column, and any subquery etc. proxying us
3371 # would be doing so via another Column, so that's what would
3372 # be returned here
3373 return table.columns.corresponding_column(self.column) # type: ignore
3375 @util.memoized_property
3376 def _column_tokens(self) -> Tuple[Optional[str], str, Optional[str]]:
3377 """parse a string-based _colspec into its component parts."""
3379 m = self._get_colspec().split(".")
3380 if len(m) == 1:
3381 tname = m.pop()
3382 colname = None
3383 else:
3384 colname = m.pop()
3385 tname = m.pop()
3387 # A FK between column 'bar' and table 'foo' can be
3388 # specified as 'foo', 'foo.bar', 'dbo.foo.bar',
3389 # 'otherdb.dbo.foo.bar'. Once we have the column name and
3390 # the table name, treat everything else as the schema
3391 # name. Some databases (e.g. Sybase) support
3392 # inter-database foreign keys. See tickets#1341 and --
3393 # indirectly related -- Ticket #594. This assumes that '.'
3394 # will never appear *within* any component of the FK.
3396 if len(m) > 0:
3397 schema = ".".join(m)
3398 else:
3399 schema = None
3400 return schema, tname, colname
3402 def _resolve_col_tokens(self) -> Tuple[Table, str, Optional[str]]:
3403 if self.parent is None:
3404 raise exc.InvalidRequestError(
3405 "this ForeignKey object does not yet have a "
3406 "parent Column associated with it."
3407 )
3409 elif self.parent.table is None:
3410 raise exc.InvalidRequestError(
3411 "this ForeignKey's parent column is not yet associated "
3412 "with a Table."
3413 )
3415 parenttable = self.parent.table
3417 if self._unresolvable:
3418 schema, tname, colname = self._column_tokens
3419 tablekey = _get_table_key(tname, schema)
3420 return parenttable, tablekey, colname
3422 # assertion
3423 # basically Column._make_proxy() sends the actual
3424 # target Column to the ForeignKey object, so the
3425 # string resolution here is never called.
3426 for c in self.parent.base_columns:
3427 if isinstance(c, Column):
3428 assert c.table is parenttable
3429 break
3430 else:
3431 assert False
3432 ######################
3434 schema, tname, colname = self._column_tokens
3436 if schema is None and parenttable.metadata.schema is not None:
3437 schema = parenttable.metadata.schema
3439 tablekey = _get_table_key(tname, schema)
3440 return parenttable, tablekey, colname
3442 def _link_to_col_by_colstring(
3443 self, parenttable: Table, table: Table, colname: Optional[str]
3444 ) -> Column[Any]:
3445 _column = None
3446 if colname is None:
3447 # colname is None in the case that ForeignKey argument
3448 # was specified as table name only, in which case we
3449 # match the column name to the same column on the
3450 # parent.
3451 # this use case wasn't working in later 1.x series
3452 # as it had no test coverage; fixed in 2.0
3453 parent = self.parent
3454 assert parent is not None
3455 key = parent.key
3456 _column = table.c.get(key, None)
3457 elif self.link_to_name:
3458 key = colname
3459 for c in table.c:
3460 if c.name == colname:
3461 _column = c
3462 else:
3463 key = colname
3464 _column = table.c.get(colname, None)
3466 if _column is None:
3467 raise exc.NoReferencedColumnError(
3468 "Could not initialize target column "
3469 f"for ForeignKey '{self._get_colspec()}' "
3470 f"on table '{parenttable.name}': "
3471 f"table '{table.name}' has no column named '{key}'",
3472 table.name,
3473 key,
3474 )
3476 return _column
3478 def _set_target_column(self, column: Column[Any]) -> None:
3479 assert self.parent is not None
3481 # propagate TypeEngine to parent if it didn't have one
3482 if self.parent.type._isnull:
3483 self.parent.type = column.type
3485 # super-edgy case, if other FKs point to our column,
3486 # they'd get the type propagated out also.
3488 def set_type(fk: ForeignKey) -> None:
3489 if fk.parent.type._isnull:
3490 fk.parent.type = column.type
3492 self.parent._setup_on_memoized_fks(set_type)
3494 self.column = column # type: ignore
3496 @util.ro_memoized_property
3497 def column(self) -> Column[Any]:
3498 """Return the target :class:`_schema.Column` referenced by this
3499 :class:`_schema.ForeignKey`.
3501 If no target column has been established, an exception
3502 is raised.
3504 """
3505 return self._resolve_column()
3507 @overload
3508 def _resolve_column(
3509 self, *, raiseerr: Literal[True] = ...
3510 ) -> Column[Any]: ...
3512 @overload
3513 def _resolve_column(
3514 self, *, raiseerr: bool = ...
3515 ) -> Optional[Column[Any]]: ...
3517 def _resolve_column(
3518 self, *, raiseerr: bool = True
3519 ) -> Optional[Column[Any]]:
3520 _column: Column[Any]
3522 _colspec, effective_table_column = self._resolve_colspec_argument()
3524 if isinstance(_colspec, str):
3525 parenttable, tablekey, colname = self._resolve_col_tokens()
3527 if self._unresolvable or tablekey not in parenttable.metadata:
3528 if not raiseerr:
3529 return None
3530 raise exc.NoReferencedTableError(
3531 f"Foreign key associated with column "
3532 f"'{self.parent}' could not find "
3533 f"table '{tablekey}' with which to generate a "
3534 f"foreign key to target column '{colname}'",
3535 tablekey,
3536 )
3537 elif parenttable.key not in parenttable.metadata:
3538 if not raiseerr:
3539 return None
3540 raise exc.InvalidRequestError(
3541 f"Table {parenttable} is no longer associated with its "
3542 "parent MetaData"
3543 )
3544 else:
3545 table = parenttable.metadata.tables[tablekey]
3546 return self._link_to_col_by_colstring(
3547 parenttable, table, colname
3548 )
3550 elif hasattr(_colspec, "__clause_element__"):
3551 _column = _colspec.__clause_element__()
3552 return _column
3553 else:
3554 assert isinstance(_colspec, Column)
3555 _column = _colspec
3556 return _column
3558 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
3559 assert isinstance(parent, Column)
3561 if self.parent is not None and self.parent is not parent:
3562 raise exc.InvalidRequestError(
3563 "This ForeignKey already has a parent !"
3564 )
3565 self.parent = parent
3566 self.parent.foreign_keys.add(self)
3567 self.parent._on_table_attach(self._set_table)
3569 def _set_remote_table(self, table: Table) -> None:
3570 parenttable, _, colname = self._resolve_col_tokens()
3571 _column = self._link_to_col_by_colstring(parenttable, table, colname)
3572 self._set_target_column(_column)
3573 assert self.constraint is not None
3574 self.constraint._validate_dest_table(table)
3576 def _remove_from_metadata(self, metadata: MetaData) -> None:
3577 parenttable, table_key, colname = self._resolve_col_tokens()
3578 fk_key = (table_key, colname)
3580 if self in metadata._fk_memos[fk_key]:
3581 # TODO: no test coverage for self not in memos
3582 metadata._fk_memos[fk_key].remove(self)
3584 def _set_table(self, column: Column[Any], table: Table) -> None:
3585 # standalone ForeignKey - create ForeignKeyConstraint
3586 # on the hosting Table when attached to the Table.
3587 assert isinstance(table, Table)
3588 if self.constraint is None:
3589 self.constraint = ForeignKeyConstraint(
3590 [],
3591 [],
3592 use_alter=self.use_alter,
3593 name=self.name,
3594 onupdate=self.onupdate,
3595 ondelete=self.ondelete,
3596 deferrable=self.deferrable,
3597 initially=self.initially,
3598 match=self.match,
3599 comment=self.comment,
3600 **self._unvalidated_dialect_kw,
3601 )
3602 self.constraint._append_element(column, self)
3603 self.constraint._set_parent_with_dispatch(table)
3604 table.foreign_keys.add(self)
3605 # set up remote ".column" attribute, or a note to pick it
3606 # up when the other Table/Column shows up
3608 _colspec, _ = self._resolve_colspec_argument()
3609 if isinstance(_colspec, str):
3610 parenttable, table_key, colname = self._resolve_col_tokens()
3611 fk_key = (table_key, colname)
3612 if table_key in parenttable.metadata.tables:
3613 table = parenttable.metadata.tables[table_key]
3614 try:
3615 _column = self._link_to_col_by_colstring(
3616 parenttable, table, colname
3617 )
3618 except exc.NoReferencedColumnError:
3619 # this is OK, we'll try later
3620 pass
3621 else:
3622 self._set_target_column(_column)
3624 parenttable.metadata._fk_memos[fk_key].append(self)
3625 elif hasattr(_colspec, "__clause_element__"):
3626 _column = _colspec.__clause_element__()
3627 self._set_target_column(_column)
3628 else:
3629 self._set_target_column(_colspec)
3632if TYPE_CHECKING:
3634 def default_is_sequence(
3635 obj: Optional[DefaultGenerator],
3636 ) -> TypeGuard[Sequence]: ...
3638 def default_is_clause_element(
3639 obj: Optional[DefaultGenerator],
3640 ) -> TypeGuard[ColumnElementColumnDefault]: ...
3642 def default_is_scalar(
3643 obj: Optional[DefaultGenerator],
3644 ) -> TypeGuard[ScalarElementColumnDefault]: ...
3646else:
3647 default_is_sequence = operator.attrgetter("is_sequence")
3649 default_is_clause_element = operator.attrgetter("is_clause_element")
3651 default_is_scalar = operator.attrgetter("is_scalar")
3654class DefaultGenerator(Executable, SchemaItem):
3655 """Base class for column *default* values.
3657 This object is only present on column.default or column.onupdate.
3658 It's not valid as a server default.
3660 """
3662 __visit_name__ = "default_generator"
3664 _is_default_generator = True
3665 is_sequence = False
3666 is_identity = False
3667 is_server_default = False
3668 is_clause_element = False
3669 is_callable = False
3670 is_scalar = False
3671 has_arg = False
3672 is_sentinel = False
3673 _is_monotonic_fn = False
3674 column: Optional[Column[Any]]
3676 def __init__(self, for_update: bool = False) -> None:
3677 self.for_update = for_update
3679 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
3680 if TYPE_CHECKING:
3681 assert isinstance(parent, Column)
3682 self.column = parent
3683 if self.for_update:
3684 self.column.onupdate = self
3685 else:
3686 self.column.default = self
3688 def _copy(self) -> DefaultGenerator:
3689 raise NotImplementedError()
3691 def _execute_on_connection(
3692 self,
3693 connection: Connection,
3694 distilled_params: _CoreMultiExecuteParams,
3695 execution_options: CoreExecuteOptionsParameter,
3696 ) -> Any:
3697 util.warn_deprecated(
3698 "Using the .execute() method to invoke a "
3699 "DefaultGenerator object is deprecated; please use "
3700 "the .scalar() method.",
3701 "2.0",
3702 )
3703 return self._execute_on_scalar(
3704 connection, distilled_params, execution_options
3705 )
3707 def _execute_on_scalar(
3708 self,
3709 connection: Connection,
3710 distilled_params: _CoreMultiExecuteParams,
3711 execution_options: CoreExecuteOptionsParameter,
3712 ) -> Any:
3713 return connection._execute_default(
3714 self, distilled_params, execution_options
3715 )
3718class ColumnDefault(DefaultGenerator, ABC):
3719 """A plain default value on a column.
3721 This could correspond to a constant, a callable function,
3722 or a SQL clause.
3724 :class:`.ColumnDefault` is generated automatically
3725 whenever the ``default``, ``onupdate`` arguments of
3726 :class:`_schema.Column` are used. A :class:`.ColumnDefault`
3727 can be passed positionally as well.
3729 For example, the following::
3731 Column("foo", Integer, default=50)
3733 Is equivalent to::
3735 Column("foo", Integer, ColumnDefault(50))
3737 """
3739 arg: Any
3741 _is_monotonic_fn = False
3743 @overload
3744 def __new__(
3745 cls, arg: Callable[..., Any], for_update: bool = ...
3746 ) -> CallableColumnDefault: ...
3748 @overload
3749 def __new__(
3750 cls, arg: ColumnElement[Any], for_update: bool = ...
3751 ) -> ColumnElementColumnDefault: ...
3753 # if I return ScalarElementColumnDefault here, which is what's actually
3754 # returned, mypy complains that
3755 # overloads overlap w/ incompatible return types.
3756 @overload
3757 def __new__(cls, arg: object, for_update: bool = ...) -> ColumnDefault: ...
3759 def __new__(
3760 cls, arg: Any = None, for_update: bool = False
3761 ) -> ColumnDefault:
3762 """Construct a new :class:`.ColumnDefault`.
3765 :param arg: argument representing the default value.
3766 May be one of the following:
3768 * a plain non-callable Python value, such as a
3769 string, integer, boolean, or other simple type.
3770 The default value will be used as is each time.
3771 * a SQL expression, that is one which derives from
3772 :class:`_expression.ColumnElement`. The SQL expression will
3773 be rendered into the INSERT or UPDATE statement,
3774 or in the case of a primary key column when
3775 RETURNING is not used may be
3776 pre-executed before an INSERT within a SELECT.
3777 * A Python callable. The function will be invoked for each
3778 new row subject to an INSERT or UPDATE.
3779 The callable must accept exactly
3780 zero or one positional arguments. The one-argument form
3781 will receive an instance of the :class:`.ExecutionContext`,
3782 which provides contextual information as to the current
3783 :class:`_engine.Connection` in use as well as the current
3784 statement and parameters.
3786 """
3788 if isinstance(arg, FetchedValue):
3789 raise exc.ArgumentError(
3790 "ColumnDefault may not be a server-side default type."
3791 )
3792 elif callable(arg):
3793 cls = CallableColumnDefault
3794 elif isinstance(arg, ClauseElement):
3795 cls = ColumnElementColumnDefault
3796 elif arg is not None:
3797 cls = ScalarElementColumnDefault
3799 return object.__new__(cls)
3801 def __repr__(self) -> str:
3802 return f"{self.__class__.__name__}({self.arg!r})"
3805class ScalarElementColumnDefault(ColumnDefault):
3806 """default generator for a fixed scalar Python value
3808 .. versionadded:: 2.0
3810 """
3812 is_scalar = True
3813 has_arg = True
3815 def __init__(self, arg: Any, for_update: bool = False) -> None:
3816 self.for_update = for_update
3817 self.arg = arg
3819 def _copy(self) -> ScalarElementColumnDefault:
3820 return ScalarElementColumnDefault(
3821 arg=self.arg, for_update=self.for_update
3822 )
3825class _InsertSentinelColumnDefault(ColumnDefault):
3826 """Default generator that's specific to the use of a "sentinel" column
3827 when using the insertmanyvalues feature.
3829 This default is used as part of the :func:`_schema.insert_sentinel`
3830 construct.
3832 """
3834 is_sentinel = True
3835 for_update = False
3836 arg = None
3838 def __new__(cls) -> _InsertSentinelColumnDefault:
3839 return object.__new__(cls)
3841 def __init__(self) -> None:
3842 pass
3844 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
3845 col = cast("Column[Any]", parent)
3846 if not col._insert_sentinel:
3847 raise exc.ArgumentError(
3848 "The _InsertSentinelColumnDefault may only be applied to a "
3849 "Column marked as insert_sentinel=True"
3850 )
3851 elif not col.nullable:
3852 raise exc.ArgumentError(
3853 "The _InsertSentinelColumnDefault may only be applied to a "
3854 "Column that is nullable"
3855 )
3857 super()._set_parent(parent, **kw)
3859 def _copy(self) -> _InsertSentinelColumnDefault:
3860 return _InsertSentinelColumnDefault()
3863_SQLExprDefault = Union["ColumnElement[Any]", "TextClause"]
3866class ColumnElementColumnDefault(ColumnDefault):
3867 """default generator for a SQL expression
3869 .. versionadded:: 2.0
3871 """
3873 is_clause_element = True
3874 has_arg = True
3875 arg: _SQLExprDefault
3877 def __init__(
3878 self,
3879 arg: _SQLExprDefault,
3880 for_update: bool = False,
3881 ) -> None:
3882 self.for_update = for_update
3883 self.arg = arg
3885 def _copy(self) -> ColumnElementColumnDefault:
3886 return ColumnElementColumnDefault(
3887 arg=self.arg, for_update=self.for_update
3888 )
3890 @util.memoized_property
3891 @util.preload_module("sqlalchemy.sql.functions")
3892 def _is_monotonic_fn(self) -> bool:
3893 functions = util.preloaded.sql_functions
3894 return (
3895 isinstance(self.arg, functions.FunctionElement)
3896 and self.arg.monotonic
3897 )
3899 @util.memoized_property
3900 @util.preload_module("sqlalchemy.sql.sqltypes")
3901 def _arg_is_typed(self) -> bool:
3902 sqltypes = util.preloaded.sql_sqltypes
3904 return not isinstance(self.arg.type, sqltypes.NullType)
3907class _CallableColumnDefaultProtocol(Protocol):
3908 def __call__(self, context: ExecutionContext) -> Any: ...
3911class CallableColumnDefault(ColumnDefault):
3912 """default generator for a callable Python function
3914 .. versionadded:: 2.0
3916 """
3918 is_callable = True
3919 arg: _CallableColumnDefaultProtocol
3920 has_arg = True
3922 def __init__(
3923 self,
3924 arg: Union[_CallableColumnDefaultProtocol, Callable[[], Any]],
3925 for_update: bool = False,
3926 ) -> None:
3927 self.for_update = for_update
3928 self.arg = self._maybe_wrap_callable(arg)
3930 def _copy(self) -> CallableColumnDefault:
3931 return CallableColumnDefault(arg=self.arg, for_update=self.for_update)
3933 def _maybe_wrap_callable(
3934 self, fn: Union[_CallableColumnDefaultProtocol, Callable[[], Any]]
3935 ) -> _CallableColumnDefaultProtocol:
3936 """Wrap callables that don't accept a context.
3938 This is to allow easy compatibility with default callables
3939 that aren't specific to accepting of a context.
3941 """
3943 try:
3944 argspec = util.get_callable_argspec(fn, no_self=True)
3945 except TypeError:
3946 return util.wrap_callable(lambda ctx: fn(), fn) # type: ignore
3948 defaulted = argspec[3] is not None and len(argspec[3]) or 0
3949 positionals = len(argspec[0]) - defaulted
3951 if positionals == 0:
3952 return util.wrap_callable(lambda ctx: fn(), fn) # type: ignore
3954 elif positionals == 1:
3955 return fn # type: ignore
3956 else:
3957 raise exc.ArgumentError(
3958 "ColumnDefault Python function takes zero or one "
3959 "positional arguments"
3960 )
3963class IdentityOptions(DialectKWArgs):
3964 """Defines options for a named database sequence or an identity column.
3966 .. seealso::
3968 :class:`.Sequence`
3970 """
3972 def __init__(
3973 self,
3974 start: Optional[int] = None,
3975 increment: Optional[int] = None,
3976 minvalue: Optional[int] = None,
3977 maxvalue: Optional[int] = None,
3978 nominvalue: Optional[bool] = None,
3979 nomaxvalue: Optional[bool] = None,
3980 cycle: Optional[bool] = None,
3981 cache: Optional[int] = None,
3982 order: Optional[bool] = None,
3983 **dialect_kw: Any,
3984 ) -> None:
3985 """Construct a :class:`.IdentityOptions` object.
3987 See the :class:`.Sequence` documentation for a complete description
3988 of the parameters.
3990 :param start: the starting index of the sequence.
3991 :param increment: the increment value of the sequence.
3992 :param minvalue: the minimum value of the sequence.
3993 :param maxvalue: the maximum value of the sequence.
3994 :param nominvalue: no minimum value of the sequence.
3995 :param nomaxvalue: no maximum value of the sequence.
3996 :param cycle: allows the sequence to wrap around when the maxvalue
3997 or minvalue has been reached.
3998 :param cache: optional integer value; number of future values in the
3999 sequence which are calculated in advance.
4000 :param order: optional boolean value; if ``True``, renders the
4001 ORDER keyword.
4003 .. deprecated:: 2.1 Use ``oracle_order`` instead.
4005 """
4006 self.start = start
4007 self.increment = increment
4008 self.minvalue = minvalue
4009 self.maxvalue = maxvalue
4010 self.nominvalue = nominvalue
4011 self.nomaxvalue = nomaxvalue
4012 self.cycle = cycle
4013 self.cache = cache
4014 if order is not None:
4015 if "oracle_order" in dialect_kw:
4016 raise exc.ArgumentError(
4017 "Cannot specify both 'order' and 'oracle_order'. "
4018 "Please use only 'oracle_order'."
4019 )
4020 dialect_kw["oracle_order"] = order
4021 self._validate_dialect_kwargs(dialect_kw)
4023 @property
4024 def _increment_is_negative(self) -> bool:
4025 return self.increment is not None and self.increment < 0
4027 @property
4028 def order(self) -> Optional[bool]:
4029 """Alias of the ``dialect_kwargs`` ``'oracle_order'``.
4031 .. deprecated:: 2.1 The 'order' attribute is deprecated.
4032 """
4033 value: Optional[bool] = self.dialect_kwargs.get("oracle_order")
4034 return value
4036 def _as_dict(self) -> Dict[str, Any]:
4037 return {
4038 k: v
4039 for k, v in {
4040 "start": self.start,
4041 "increment": self.increment,
4042 "minvalue": self.minvalue,
4043 "maxvalue": self.maxvalue,
4044 "nominvalue": self.nominvalue,
4045 "nomaxvalue": self.nomaxvalue,
4046 "cycle": self.cycle,
4047 "cache": self.cache,
4048 }.items()
4049 if v != None
4050 }
4053class Sequence(HasSchemaAttr, IdentityOptions, DefaultGenerator):
4054 """Represents a named database sequence.
4056 The :class:`.Sequence` object represents the name and configurational
4057 parameters of a database sequence. It also represents
4058 a construct that can be "executed" by a SQLAlchemy :class:`_engine.Engine`
4059 or :class:`_engine.Connection`,
4060 rendering the appropriate "next value" function
4061 for the target database and returning a result.
4063 The :class:`.Sequence` is typically associated with a primary key column::
4065 some_table = Table(
4066 "some_table",
4067 metadata,
4068 Column(
4069 "id",
4070 Integer,
4071 Sequence("some_table_seq", start=1),
4072 primary_key=True,
4073 ),
4074 )
4076 When CREATE TABLE is emitted for the above :class:`_schema.Table`, if the
4077 target platform supports sequences, a CREATE SEQUENCE statement will
4078 be emitted as well. For platforms that don't support sequences,
4079 the :class:`.Sequence` construct is ignored.
4081 .. seealso::
4083 :ref:`defaults_sequences`
4085 :class:`.CreateSequence`
4087 :class:`.DropSequence`
4089 """
4091 __visit_name__ = "sequence"
4093 is_sequence = True
4095 column: Optional[Column[Any]]
4096 data_type: Optional[TypeEngine[int]]
4098 metadata: Optional[MetaData]
4100 @util.deprecated_params(
4101 order=(
4102 "2.1",
4103 "This parameter is supported only by Oracle Database, "
4104 "use ``oracle_order`` instead.",
4105 )
4106 )
4107 def __init__(
4108 self,
4109 name: str,
4110 start: Optional[int] = None,
4111 increment: Optional[int] = None,
4112 minvalue: Optional[int] = None,
4113 maxvalue: Optional[int] = None,
4114 nominvalue: Optional[bool] = None,
4115 nomaxvalue: Optional[bool] = None,
4116 cycle: Optional[bool] = None,
4117 schema: Optional[Union[str, Literal[SchemaConst.BLANK_SCHEMA]]] = None,
4118 cache: Optional[int] = None,
4119 order: Optional[bool] = None,
4120 data_type: Optional[_TypeEngineArgument[int]] = None,
4121 optional: bool = False,
4122 quote: Optional[bool] = None,
4123 metadata: Optional[MetaData] = None,
4124 quote_schema: Optional[bool] = None,
4125 for_update: bool = False,
4126 **dialect_kw: Any,
4127 ) -> None:
4128 """Construct a :class:`.Sequence` object.
4130 :param name: the name of the sequence.
4132 :param start: the starting index of the sequence. This value is
4133 used when the CREATE SEQUENCE command is emitted to the database
4134 as the value of the "START WITH" clause. If ``None``, the
4135 clause is omitted, which on most platforms indicates a starting
4136 value of 1.
4138 .. versionchanged:: 2.0 The :paramref:`.Sequence.start` parameter
4139 is required in order to have DDL emit "START WITH". This is a
4140 reversal of a change made in version 1.4 which would implicitly
4141 render "START WITH 1" if the :paramref:`.Sequence.start` were
4142 not included. See :ref:`change_7211` for more detail.
4144 :param increment: the increment value of the sequence. This
4145 value is used when the CREATE SEQUENCE command is emitted to
4146 the database as the value of the "INCREMENT BY" clause. If ``None``,
4147 the clause is omitted, which on most platforms indicates an
4148 increment of 1.
4149 :param minvalue: the minimum value of the sequence. This
4150 value is used when the CREATE SEQUENCE command is emitted to
4151 the database as the value of the "MINVALUE" clause. If ``None``,
4152 the clause is omitted, which on most platforms indicates a
4153 minvalue of 1 and -2^63-1 for ascending and descending sequences,
4154 respectively.
4156 :param maxvalue: the maximum value of the sequence. This
4157 value is used when the CREATE SEQUENCE command is emitted to
4158 the database as the value of the "MAXVALUE" clause. If ``None``,
4159 the clause is omitted, which on most platforms indicates a
4160 maxvalue of 2^63-1 and -1 for ascending and descending sequences,
4161 respectively.
4163 :param nominvalue: no minimum value of the sequence. This
4164 value is used when the CREATE SEQUENCE command is emitted to
4165 the database as the value of the "NO MINVALUE" clause. If ``None``,
4166 the clause is omitted, which on most platforms indicates a
4167 minvalue of 1 and -2^63-1 for ascending and descending sequences,
4168 respectively.
4170 :param nomaxvalue: no maximum value of the sequence. This
4171 value is used when the CREATE SEQUENCE command is emitted to
4172 the database as the value of the "NO MAXVALUE" clause. If ``None``,
4173 the clause is omitted, which on most platforms indicates a
4174 maxvalue of 2^63-1 and -1 for ascending and descending sequences,
4175 respectively.
4177 :param cycle: allows the sequence to wrap around when the maxvalue
4178 or minvalue has been reached by an ascending or descending sequence
4179 respectively. This value is used when the CREATE SEQUENCE command
4180 is emitted to the database as the "CYCLE" clause. If the limit is
4181 reached, the next number generated will be the minvalue or maxvalue,
4182 respectively. If cycle=False (the default) any calls to nextval
4183 after the sequence has reached its maximum value will return an
4184 error.
4186 :param schema: optional schema name for the sequence, if located
4187 in a schema other than the default. The rules for selecting the
4188 schema name when a :class:`_schema.MetaData`
4189 is also present are the same
4190 as that of :paramref:`_schema.Table.schema`.
4192 :param cache: optional integer value; number of future values in the
4193 sequence which are calculated in advance. Renders the CACHE keyword
4194 understood by Oracle Database and PostgreSQL.
4196 :param order: optional boolean value; if ``True``, renders the
4197 ORDER keyword, understood by Oracle Database, indicating the sequence
4198 is definitively ordered. May be necessary to provide deterministic
4199 ordering using Oracle RAC.
4201 :param data_type: The type to be returned by the sequence, for
4202 dialects that allow us to choose between INTEGER, BIGINT, etc.
4203 (e.g., mssql).
4205 .. versionadded:: 1.4.0
4207 :param optional: boolean value, when ``True``, indicates that this
4208 :class:`.Sequence` object only needs to be explicitly generated
4209 on backends that don't provide another way to generate primary
4210 key identifiers. Currently, it essentially means, "don't create
4211 this sequence on the PostgreSQL backend, where the SERIAL keyword
4212 creates a sequence for us automatically".
4213 :param quote: boolean value, when ``True`` or ``False``, explicitly
4214 forces quoting of the :paramref:`_schema.Sequence.name` on or off.
4215 When left at its default of ``None``, normal quoting rules based
4216 on casing and reserved words take place.
4217 :param quote_schema: Set the quoting preferences for the ``schema``
4218 name.
4220 :param metadata: optional :class:`_schema.MetaData` object which this
4221 :class:`.Sequence` will be associated with. A :class:`.Sequence`
4222 that is associated with a :class:`_schema.MetaData`
4223 gains the following
4224 capabilities:
4226 * The :class:`.Sequence` will inherit the
4227 :paramref:`_schema.MetaData.schema`
4228 parameter specified to the target :class:`_schema.MetaData`, which
4229 affects the production of CREATE / DROP DDL, if any.
4231 * The :meth:`.Sequence.create` and :meth:`.Sequence.drop` methods
4232 automatically use the engine bound to the :class:`_schema.MetaData`
4233 object, if any.
4235 * The :meth:`_schema.MetaData.create_all` and
4236 :meth:`_schema.MetaData.drop_all`
4237 methods will emit CREATE / DROP for this :class:`.Sequence`,
4238 even if the :class:`.Sequence` is not associated with any
4239 :class:`_schema.Table` / :class:`_schema.Column`
4240 that's a member of this
4241 :class:`_schema.MetaData`.
4243 The above behaviors can only occur if the :class:`.Sequence` is
4244 explicitly associated with the :class:`_schema.MetaData`
4245 via this parameter.
4247 .. seealso::
4249 :ref:`sequence_metadata` - full discussion of the
4250 :paramref:`.Sequence.metadata` parameter.
4252 :param for_update: Indicates this :class:`.Sequence`, when associated
4253 with a :class:`_schema.Column`,
4254 should be invoked for UPDATE statements
4255 on that column's table, rather than for INSERT statements, when
4256 no value is otherwise present for that column in the statement.
4258 """
4259 DefaultGenerator.__init__(self, for_update=for_update)
4260 IdentityOptions.__init__(
4261 self,
4262 start=start,
4263 increment=increment,
4264 minvalue=minvalue,
4265 maxvalue=maxvalue,
4266 nominvalue=nominvalue,
4267 nomaxvalue=nomaxvalue,
4268 cycle=cycle,
4269 cache=cache,
4270 order=order,
4271 **dialect_kw,
4272 )
4273 self.column = None
4274 self.name = quoted_name(name, quote)
4275 self.optional = optional
4276 if schema is BLANK_SCHEMA:
4277 self.schema = schema = None
4278 elif metadata is not None and schema is None and metadata.schema:
4279 self.schema = schema = metadata.schema
4280 else:
4281 self.schema = quoted_name.construct(schema, quote_schema)
4282 self._key = _get_table_key(name, schema)
4283 if data_type is not None:
4284 self.data_type = to_instance(data_type)
4285 else:
4286 self.data_type = None
4288 if metadata:
4289 self._set_metadata(metadata)
4290 else:
4291 self.metadata = None
4293 @util.preload_module("sqlalchemy.sql.functions")
4294 def next_value(self) -> Function[int]:
4295 """Return a :class:`.next_value` function element
4296 which will render the appropriate increment function
4297 for this :class:`.Sequence` within any SQL expression.
4299 """
4300 return util.preloaded.sql_functions.func.next_value(self)
4302 def _copy(self) -> Sequence:
4303 return Sequence(
4304 name=self.name,
4305 schema=self.schema,
4306 data_type=self.data_type,
4307 optional=self.optional,
4308 metadata=self.metadata,
4309 for_update=self.for_update,
4310 **self._as_dict(),
4311 **self.dialect_kwargs,
4312 )
4314 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4315 assert isinstance(parent, Column)
4316 super()._set_parent(parent, **kw)
4317 parent._on_table_attach(self._set_table)
4319 def _set_table(self, column: Column[Any], table: Table) -> None:
4320 self._set_metadata(table.metadata)
4322 def _set_metadata(self, metadata: MetaData) -> None:
4323 self.metadata = metadata
4324 self.metadata._register_object(self)
4325 metadata._sequences[self._key] = self
4327 def create(
4328 self,
4329 bind: _CreateDropBind,
4330 checkfirst: Union[bool, CheckFirst] = CheckFirst.SEQUENCES,
4331 ) -> None:
4332 """Creates this sequence in the database."""
4334 bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
4336 def drop(
4337 self,
4338 bind: _CreateDropBind,
4339 checkfirst: Union[bool, CheckFirst] = CheckFirst.SEQUENCES,
4340 ) -> None:
4341 """Drops this sequence from the database."""
4343 bind._run_ddl_visitor(ddl.SchemaDropper, self, checkfirst=checkfirst)
4345 def _not_a_column_expr(self) -> NoReturn:
4346 raise exc.InvalidRequestError(
4347 f"This {self.__class__.__name__} cannot be used directly "
4348 "as a column expression. Use func.next_value(sequence) "
4349 "to produce a 'next value' function that's usable "
4350 "as a column element."
4351 )
4354@inspection._self_inspects
4355class FetchedValue(SchemaEventTarget):
4356 """A marker for a transparent database-side default.
4358 Use :class:`.FetchedValue` when the database is configured
4359 to provide some automatic default for a column.
4361 E.g.::
4363 Column("foo", Integer, FetchedValue())
4365 Would indicate that some trigger or default generator
4366 will create a new value for the ``foo`` column during an
4367 INSERT.
4369 .. seealso::
4371 :ref:`triggered_columns`
4373 """
4375 is_server_default = True
4376 reflected = False
4377 has_argument = False
4378 is_clause_element = False
4379 is_identity = False
4380 _is_monotonic_fn = False
4382 column: Optional[Column[Any]]
4384 def __init__(self, for_update: bool = False) -> None:
4385 self.for_update = for_update
4387 def _as_for_update(self, for_update: bool) -> FetchedValue:
4388 if for_update == self.for_update:
4389 return self
4390 else:
4391 return self._clone(for_update)
4393 def _copy(self) -> FetchedValue:
4394 return FetchedValue(self.for_update)
4396 def _clone(self, for_update: bool) -> Self:
4397 n = self.__class__.__new__(self.__class__)
4398 n.__dict__.update(self.__dict__)
4399 n.__dict__.pop("column", None)
4400 n.for_update = for_update
4401 return n
4403 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4404 column = parent
4405 assert isinstance(column, Column)
4406 self.column = column
4407 if self.for_update:
4408 self.column.server_onupdate = self
4409 else:
4410 self.column.server_default = self
4412 def __repr__(self) -> str:
4413 return util.generic_repr(self)
4416class DefaultClause(FetchedValue):
4417 """A DDL-specified DEFAULT column value.
4419 :class:`.DefaultClause` is a :class:`.FetchedValue`
4420 that also generates a "DEFAULT" clause when
4421 "CREATE TABLE" is emitted.
4423 :class:`.DefaultClause` is generated automatically
4424 whenever the ``server_default``, ``server_onupdate`` arguments of
4425 :class:`_schema.Column` are used. A :class:`.DefaultClause`
4426 can be passed positionally as well.
4428 For example, the following::
4430 Column("foo", Integer, server_default="50")
4432 Is equivalent to::
4434 Column("foo", Integer, DefaultClause("50"))
4436 """
4438 has_argument = True
4440 def __init__(
4441 self,
4442 arg: Union[str, ClauseElement, TextClause],
4443 for_update: bool = False,
4444 _reflected: bool = False,
4445 ) -> None:
4446 util.assert_arg_type(arg, (str, ClauseElement, TextClause), "arg")
4447 super().__init__(for_update)
4448 self.arg = arg
4449 self.reflected = _reflected
4451 @util.memoized_property
4452 @util.preload_module("sqlalchemy.sql.functions")
4453 def _is_monotonic_fn(self) -> bool:
4454 functions = util.preloaded.sql_functions
4455 return (
4456 isinstance(self.arg, functions.FunctionElement)
4457 and self.arg.monotonic
4458 )
4460 def _copy(self) -> DefaultClause:
4461 return DefaultClause(
4462 arg=self.arg, for_update=self.for_update, _reflected=self.reflected
4463 )
4465 def __repr__(self) -> str:
4466 return "DefaultClause(%r, for_update=%r)" % (self.arg, self.for_update)
4469class Constraint(DialectKWArgs, HasConditionalDDL, SchemaItem):
4470 """A table-level SQL constraint.
4472 :class:`_schema.Constraint` serves as the base class for the series of
4473 constraint objects that can be associated with :class:`_schema.Table`
4474 objects, including :class:`_schema.PrimaryKeyConstraint`,
4475 :class:`_schema.ForeignKeyConstraint`
4476 :class:`_schema.UniqueConstraint`, and
4477 :class:`_schema.CheckConstraint`.
4479 """
4481 __visit_name__ = "constraint"
4483 _creation_order: int
4484 _column_flag: bool
4486 def __init__(
4487 self,
4488 name: _ConstraintNameArgument = None,
4489 deferrable: Optional[bool] = None,
4490 initially: Optional[str] = None,
4491 info: Optional[_InfoType] = None,
4492 comment: Optional[str] = None,
4493 _create_rule: Optional[Any] = None,
4494 _type_bound: bool = False,
4495 **dialect_kw: Any,
4496 ) -> None:
4497 r"""Create a SQL constraint.
4499 :param name:
4500 Optional, the in-database name of this ``Constraint``.
4502 :param deferrable:
4503 Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
4504 issuing DDL for this constraint.
4506 :param initially:
4507 Optional string. If set, emit INITIALLY <value> when issuing DDL
4508 for this constraint.
4510 :param info: Optional data dictionary which will be populated into the
4511 :attr:`.SchemaItem.info` attribute of this object.
4513 :param comment: Optional string that will render an SQL comment on
4514 foreign key constraint creation.
4516 .. versionadded:: 2.0
4518 :param \**dialect_kw: Additional keyword arguments are dialect
4519 specific, and passed in the form ``<dialectname>_<argname>``. See
4520 the documentation regarding an individual dialect at
4521 :ref:`dialect_toplevel` for detail on documented arguments.
4523 :param _create_rule:
4524 used internally by some datatypes that also create constraints.
4526 :param _type_bound:
4527 used internally to indicate that this constraint is associated with
4528 a specific datatype.
4530 """
4532 self.name = name
4533 self.deferrable = deferrable
4534 self.initially = initially
4535 if info:
4536 self.info = info
4537 self._create_rule = _create_rule
4538 self._type_bound = _type_bound
4539 util.set_creation_order(self)
4540 self._validate_dialect_kwargs(dialect_kw)
4541 self.comment = comment
4543 def _should_create_for_compiler(
4544 self, compiler: DDLCompiler, **kw: Any
4545 ) -> bool:
4546 if self._create_rule is not None and not self._create_rule(compiler):
4547 return False
4548 elif self._ddl_if is not None:
4549 return self._ddl_if._should_execute(
4550 ddl.CreateConstraint(self), self, None, compiler=compiler, **kw
4551 )
4552 else:
4553 return True
4555 @property
4556 def table(self) -> Table:
4557 try:
4558 if isinstance(self.parent, Table):
4559 return self.parent
4560 except AttributeError:
4561 pass
4562 raise exc.InvalidRequestError(
4563 "This constraint is not bound to a table. Did you "
4564 "mean to call table.append_constraint(constraint) ?"
4565 )
4567 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4568 assert isinstance(parent, (Table, Column))
4569 self.parent = parent
4570 parent.constraints.add(self)
4572 @util.deprecated(
4573 "1.4",
4574 "The :meth:`_schema.Constraint.copy` method is deprecated "
4575 "and will be removed in a future release.",
4576 )
4577 def copy(self, **kw: Any) -> Self:
4578 return self._copy(**kw)
4580 def _copy(self, **kw: Any) -> Self:
4581 raise NotImplementedError()
4584class ColumnCollectionMixin:
4585 """A :class:`_expression.ColumnCollection` of :class:`_schema.Column`
4586 objects.
4588 This collection represents the columns which are referred to by
4589 this object.
4591 """
4593 _columns: DedupeColumnCollection[Column[Any]]
4595 _allow_multiple_tables = False
4597 _pending_colargs: List[Optional[Union[str, Column[Any]]]]
4599 if TYPE_CHECKING:
4601 def _set_parent_with_dispatch(
4602 self, parent: SchemaEventTarget, **kw: Any
4603 ) -> None: ...
4605 def __init__(
4606 self,
4607 *columns: _DDLColumnArgument,
4608 _autoattach: bool = True,
4609 _column_flag: bool = False,
4610 _gather_expressions: Optional[
4611 List[Union[str, ColumnElement[Any]]]
4612 ] = None,
4613 ) -> None:
4614 self._column_flag = _column_flag
4615 self._columns = DedupeColumnCollection()
4617 processed_expressions: Optional[
4618 List[Union[ColumnElement[Any], str]]
4619 ] = _gather_expressions
4621 if processed_expressions is not None:
4623 # this is expected to be an empty list
4624 assert not processed_expressions
4626 self._pending_colargs = []
4627 for (
4628 expr,
4629 _,
4630 _,
4631 add_element,
4632 ) in coercions.expect_col_expression_collection(
4633 roles.DDLConstraintColumnRole, columns
4634 ):
4635 self._pending_colargs.append(add_element)
4636 processed_expressions.append(expr)
4637 else:
4638 self._pending_colargs = [
4639 coercions.expect(roles.DDLConstraintColumnRole, column)
4640 for column in columns
4641 ]
4643 if _autoattach and self._pending_colargs:
4644 self._check_attach()
4646 def _check_attach(self, evt: bool = False) -> None:
4647 col_objs = [c for c in self._pending_colargs if isinstance(c, Column)]
4649 cols_w_table = [c for c in col_objs if isinstance(c.table, Table)]
4651 cols_wo_table = set(col_objs).difference(cols_w_table)
4652 if cols_wo_table:
4653 # feature #3341 - place event listeners for Column objects
4654 # such that when all those cols are attached, we autoattach.
4655 assert not evt, "Should not reach here on event call"
4657 # issue #3411 - don't do the per-column auto-attach if some of the
4658 # columns are specified as strings.
4659 has_string_cols = {
4660 c for c in self._pending_colargs if c is not None
4661 }.difference(col_objs)
4662 if not has_string_cols:
4664 def _col_attached(column: Column[Any], table: Table) -> None:
4665 # this isinstance() corresponds with the
4666 # isinstance() above; only want to count Table-bound
4667 # columns
4668 if isinstance(table, Table):
4669 cols_wo_table.discard(column)
4670 if not cols_wo_table:
4671 self._check_attach(evt=True)
4673 self._cols_wo_table = cols_wo_table
4674 for col in cols_wo_table:
4675 col._on_table_attach(_col_attached)
4676 return
4678 columns = cols_w_table
4680 tables = {c.table for c in columns}
4681 if len(tables) == 1:
4682 self._set_parent_with_dispatch(tables.pop())
4683 elif len(tables) > 1 and not self._allow_multiple_tables:
4684 table = columns[0].table
4685 others = [c for c in columns[1:] if c.table is not table]
4686 if others:
4687 # black could not format this inline
4688 other_str = ", ".join("'%s'" % c for c in others)
4689 raise exc.ArgumentError(
4690 f"Column(s) {other_str} "
4691 f"are not part of table '{table.description}'."
4692 )
4694 @util.ro_memoized_property
4695 def columns(self) -> ReadOnlyColumnCollection[str, Column[Any]]:
4696 return self._columns.as_readonly()
4698 @util.ro_memoized_property
4699 def c(self) -> ReadOnlyColumnCollection[str, Column[Any]]:
4700 return self._columns.as_readonly()
4702 def _col_expressions(
4703 self, parent: Union[Table, Column[Any]]
4704 ) -> List[Optional[Column[Any]]]:
4705 if isinstance(parent, Column):
4706 result: List[Optional[Column[Any]]] = [
4707 c for c in self._pending_colargs if isinstance(c, Column)
4708 ]
4709 assert len(result) == len(self._pending_colargs)
4710 return result
4711 else:
4712 try:
4713 return [
4714 parent.c[col] if isinstance(col, str) else col
4715 for col in self._pending_colargs
4716 ]
4717 except KeyError as ke:
4718 raise exc.ConstraintColumnNotFoundError(
4719 f"Can't create {self.__class__.__name__} "
4720 f"on table '{parent.description}': no column "
4721 f"named '{ke.args[0]}' is present."
4722 ) from ke
4724 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4725 assert isinstance(parent, (Table, Column))
4727 for col in self._col_expressions(parent):
4728 if col is not None:
4729 self._columns.add(col)
4732class ColumnCollectionConstraint(ColumnCollectionMixin, Constraint):
4733 """A constraint that proxies a ColumnCollection."""
4735 def __init__(
4736 self,
4737 *columns: _DDLColumnArgument,
4738 name: _ConstraintNameArgument = None,
4739 deferrable: Optional[bool] = None,
4740 initially: Optional[str] = None,
4741 info: Optional[_InfoType] = None,
4742 _autoattach: bool = True,
4743 _column_flag: bool = False,
4744 _gather_expressions: Optional[List[_DDLColumnArgument]] = None,
4745 **dialect_kw: Any,
4746 ) -> None:
4747 r"""
4748 :param \*columns:
4749 A sequence of column names or Column objects.
4751 :param name:
4752 Optional, the in-database name of this constraint.
4754 :param deferrable:
4755 Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
4756 issuing DDL for this constraint.
4758 :param initially:
4759 Optional string. If set, emit INITIALLY <value> when issuing DDL
4760 for this constraint.
4762 :param \**dialect_kw: other keyword arguments including
4763 dialect-specific arguments are propagated to the :class:`.Constraint`
4764 superclass.
4766 """
4767 Constraint.__init__(
4768 self,
4769 name=name,
4770 deferrable=deferrable,
4771 initially=initially,
4772 info=info,
4773 **dialect_kw,
4774 )
4775 ColumnCollectionMixin.__init__(
4776 self, *columns, _autoattach=_autoattach, _column_flag=_column_flag
4777 )
4779 columns: ReadOnlyColumnCollection[str, Column[Any]]
4780 """A :class:`_expression.ColumnCollection` representing the set of columns
4781 for this constraint.
4783 """
4785 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4786 assert isinstance(parent, (Column, Table))
4787 Constraint._set_parent(self, parent)
4788 ColumnCollectionMixin._set_parent(self, parent)
4790 def __contains__(self, x: Any) -> bool:
4791 return x in self._columns
4793 @util.deprecated(
4794 "1.4",
4795 "The :meth:`_schema.ColumnCollectionConstraint.copy` method "
4796 "is deprecated and will be removed in a future release.",
4797 )
4798 def copy(
4799 self,
4800 *,
4801 target_table: Optional[Table] = None,
4802 **kw: Any,
4803 ) -> ColumnCollectionConstraint:
4804 return self._copy(target_table=target_table, **kw)
4806 def _copy(
4807 self,
4808 *,
4809 target_table: Optional[Table] = None,
4810 **kw: Any,
4811 ) -> ColumnCollectionConstraint:
4812 # ticket #5276
4813 constraint_kwargs = {}
4814 for dialect_name in self.dialect_options:
4815 dialect_options = self.dialect_options[dialect_name]._non_defaults
4816 for (
4817 dialect_option_key,
4818 dialect_option_value,
4819 ) in dialect_options.items():
4820 constraint_kwargs[dialect_name + "_" + dialect_option_key] = (
4821 dialect_option_value
4822 )
4824 assert isinstance(self.parent, Table)
4825 c = self.__class__(
4826 name=self.name,
4827 deferrable=self.deferrable,
4828 initially=self.initially,
4829 *[
4830 _copy_expression(expr, self.parent, target_table)
4831 for expr in self._columns
4832 ],
4833 comment=self.comment,
4834 **constraint_kwargs,
4835 )
4836 return self._schema_item_copy(c)
4838 def contains_column(self, col: Column[Any]) -> bool:
4839 """Return True if this constraint contains the given column.
4841 Note that this object also contains an attribute ``.columns``
4842 which is a :class:`_expression.ColumnCollection` of
4843 :class:`_schema.Column` objects.
4845 """
4847 return self._columns.contains_column(col)
4849 def __iter__(self) -> Iterator[Column[Any]]:
4850 return iter(self._columns)
4852 def __len__(self) -> int:
4853 return len(self._columns)
4856class CheckConstraint(ColumnCollectionConstraint):
4857 """A table- or column-level CHECK constraint.
4859 Can be included in the definition of a Table or Column.
4860 """
4862 _allow_multiple_tables = True
4864 __visit_name__ = "table_or_column_check_constraint"
4866 @_document_text_coercion(
4867 "sqltext",
4868 ":class:`.CheckConstraint`",
4869 ":paramref:`.CheckConstraint.sqltext`",
4870 )
4871 def __init__(
4872 self,
4873 sqltext: _TextCoercedExpressionArgument[Any],
4874 name: _ConstraintNameArgument = None,
4875 deferrable: Optional[bool] = None,
4876 initially: Optional[str] = None,
4877 table: Optional[Table] = None,
4878 info: Optional[_InfoType] = None,
4879 _create_rule: Optional[Any] = None,
4880 _autoattach: bool = True,
4881 _type_bound: bool = False,
4882 **dialect_kw: Any,
4883 ) -> None:
4884 r"""Construct a CHECK constraint.
4886 :param sqltext:
4887 A string containing the constraint definition, which will be used
4888 verbatim, or a SQL expression construct. If given as a string,
4889 the object is converted to a :func:`_expression.text` object.
4890 If the textual
4891 string includes a colon character, escape this using a backslash::
4893 CheckConstraint(r"foo ~ E'a(?\:b|c)d")
4895 :param name:
4896 Optional, the in-database name of the constraint.
4898 :param deferrable:
4899 Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
4900 issuing DDL for this constraint.
4902 :param initially:
4903 Optional string. If set, emit INITIALLY <value> when issuing DDL
4904 for this constraint.
4906 :param info: Optional data dictionary which will be populated into the
4907 :attr:`.SchemaItem.info` attribute of this object.
4909 """
4911 self.sqltext = coercions.expect(roles.DDLExpressionRole, sqltext)
4912 columns: List[Column[Any]] = []
4913 visitors.traverse(self.sqltext, {}, {"column": columns.append})
4915 super().__init__(
4916 name=name,
4917 deferrable=deferrable,
4918 initially=initially,
4919 _create_rule=_create_rule,
4920 info=info,
4921 _type_bound=_type_bound,
4922 _autoattach=_autoattach,
4923 *columns,
4924 **dialect_kw,
4925 )
4926 if table is not None:
4927 self._set_parent_with_dispatch(table)
4929 @property
4930 def is_column_level(self) -> bool:
4931 return not isinstance(self.parent, Table)
4933 @util.deprecated(
4934 "1.4",
4935 "The :meth:`_schema.CheckConstraint.copy` method is deprecated "
4936 "and will be removed in a future release.",
4937 )
4938 def copy(
4939 self, *, target_table: Optional[Table] = None, **kw: Any
4940 ) -> CheckConstraint:
4941 return self._copy(target_table=target_table, **kw)
4943 def _copy(
4944 self, *, target_table: Optional[Table] = None, **kw: Any
4945 ) -> CheckConstraint:
4946 if target_table is not None:
4947 # note that target_table is None for the copy process of
4948 # a column-bound CheckConstraint, so this path is not reached
4949 # in that case.
4950 sqltext = _copy_expression(self.sqltext, self.table, target_table)
4951 else:
4952 sqltext = self.sqltext
4953 c = CheckConstraint(
4954 sqltext,
4955 name=self.name,
4956 initially=self.initially,
4957 deferrable=self.deferrable,
4958 _create_rule=self._create_rule,
4959 table=target_table,
4960 comment=self.comment,
4961 _autoattach=False,
4962 _type_bound=self._type_bound,
4963 )
4964 return self._schema_item_copy(c)
4967class ForeignKeyConstraint(ColumnCollectionConstraint):
4968 """A table-level FOREIGN KEY constraint.
4970 Defines a single column or composite FOREIGN KEY ... REFERENCES
4971 constraint. For a no-frills, single column foreign key, adding a
4972 :class:`_schema.ForeignKey` to the definition of a :class:`_schema.Column`
4973 is a
4974 shorthand equivalent for an unnamed, single column
4975 :class:`_schema.ForeignKeyConstraint`.
4977 Examples of foreign key configuration are in :ref:`metadata_foreignkeys`.
4979 """
4981 __visit_name__ = "foreign_key_constraint"
4983 def __init__(
4984 self,
4985 columns: _typing_Sequence[_DDLColumnArgument],
4986 refcolumns: _typing_Sequence[_DDLColumnReferenceArgument],
4987 name: _ConstraintNameArgument = None,
4988 onupdate: Optional[str] = None,
4989 ondelete: Optional[str] = None,
4990 deferrable: Optional[bool] = None,
4991 initially: Optional[str] = None,
4992 use_alter: bool = False,
4993 link_to_name: bool = False,
4994 match: Optional[str] = None,
4995 table: Optional[Table] = None,
4996 info: Optional[_InfoType] = None,
4997 comment: Optional[str] = None,
4998 **dialect_kw: Any,
4999 ) -> None:
5000 r"""Construct a composite-capable FOREIGN KEY.
5002 :param columns: A sequence of local column names. The named columns
5003 must be defined and present in the parent Table. The names should
5004 match the ``key`` given to each column (defaults to the name) unless
5005 ``link_to_name`` is True.
5007 :param refcolumns: A sequence of foreign column names or Column
5008 objects. The columns must all be located within the same Table.
5010 :param name: Optional, the in-database name of the key.
5012 :param onupdate: Optional string. If set, emit ON UPDATE <value> when
5013 issuing DDL for this constraint. Typical values include CASCADE,
5014 DELETE and RESTRICT.
5016 .. seealso::
5018 :ref:`on_update_on_delete`
5020 :param ondelete: Optional string. If set, emit ON DELETE <value> when
5021 issuing DDL for this constraint. Typical values include CASCADE,
5022 SET NULL and RESTRICT. Some dialects may allow for additional
5023 syntaxes.
5025 .. seealso::
5027 :ref:`on_update_on_delete`
5029 :param deferrable: Optional bool. If set, emit DEFERRABLE or NOT
5030 DEFERRABLE when issuing DDL for this constraint.
5032 :param initially: Optional string. If set, emit INITIALLY <value> when
5033 issuing DDL for this constraint.
5035 :param link_to_name: if True, the string name given in ``column`` is
5036 the rendered name of the referenced column, not its locally assigned
5037 ``key``.
5039 :param use_alter: If True, do not emit the DDL for this constraint as
5040 part of the CREATE TABLE definition. Instead, generate it via an
5041 ALTER TABLE statement issued after the full collection of tables
5042 have been created, and drop it via an ALTER TABLE statement before
5043 the full collection of tables are dropped.
5045 The use of :paramref:`_schema.ForeignKeyConstraint.use_alter` is
5046 particularly geared towards the case where two or more tables
5047 are established within a mutually-dependent foreign key constraint
5048 relationship; however, the :meth:`_schema.MetaData.create_all` and
5049 :meth:`_schema.MetaData.drop_all`
5050 methods will perform this resolution
5051 automatically, so the flag is normally not needed.
5053 .. seealso::
5055 :ref:`use_alter`
5057 :param match: Optional string. If set, emit MATCH <value> when issuing
5058 DDL for this constraint. Typical values include SIMPLE, PARTIAL
5059 and FULL.
5061 :param info: Optional data dictionary which will be populated into the
5062 :attr:`.SchemaItem.info` attribute of this object.
5064 :param comment: Optional string that will render an SQL comment on
5065 foreign key constraint creation.
5067 .. versionadded:: 2.0
5069 :param \**dialect_kw: Additional keyword arguments are dialect
5070 specific, and passed in the form ``<dialectname>_<argname>``. See
5071 the documentation regarding an individual dialect at
5072 :ref:`dialect_toplevel` for detail on documented arguments.
5074 """
5076 Constraint.__init__(
5077 self,
5078 name=name,
5079 deferrable=deferrable,
5080 initially=initially,
5081 info=info,
5082 comment=comment,
5083 **dialect_kw,
5084 )
5085 self.onupdate = onupdate
5086 self.ondelete = ondelete
5087 self.link_to_name = link_to_name
5088 self.use_alter = use_alter
5089 self.match = match
5091 if len(set(columns)) != len(refcolumns):
5092 if len(set(columns)) != len(columns):
5093 # e.g. FOREIGN KEY (a, a) REFERENCES r (b, c)
5094 raise exc.ArgumentError(
5095 "ForeignKeyConstraint with duplicate source column "
5096 "references are not supported."
5097 )
5098 else:
5099 # e.g. FOREIGN KEY (a) REFERENCES r (b, c)
5100 # paraphrasing
5101 # https://www.postgresql.org/docs/current/static/ddl-constraints.html
5102 raise exc.ArgumentError(
5103 "ForeignKeyConstraint number "
5104 "of constrained columns must match the number of "
5105 "referenced columns."
5106 )
5108 # standalone ForeignKeyConstraint - create
5109 # associated ForeignKey objects which will be applied to hosted
5110 # Column objects (in col.foreign_keys), either now or when attached
5111 # to the Table for string-specified names
5112 self.elements = [
5113 ForeignKey(
5114 refcol,
5115 _constraint=self,
5116 name=self.name,
5117 onupdate=self.onupdate,
5118 ondelete=self.ondelete,
5119 use_alter=self.use_alter,
5120 link_to_name=self.link_to_name,
5121 match=self.match,
5122 deferrable=self.deferrable,
5123 initially=self.initially,
5124 **self.dialect_kwargs,
5125 )
5126 for refcol in refcolumns
5127 ]
5129 ColumnCollectionMixin.__init__(self, *columns)
5130 if table is not None:
5131 if hasattr(self, "parent"):
5132 assert table is self.parent
5133 self._set_parent_with_dispatch(table)
5135 def _append_element(self, column: Column[Any], fk: ForeignKey) -> None:
5136 self._columns.add(column)
5137 self.elements.append(fk)
5139 columns: ReadOnlyColumnCollection[str, Column[Any]]
5140 """A :class:`_expression.ColumnCollection` representing the set of columns
5141 for this constraint.
5143 """
5145 elements: List[ForeignKey]
5146 """A sequence of :class:`_schema.ForeignKey` objects.
5148 Each :class:`_schema.ForeignKey`
5149 represents a single referring column/referred
5150 column pair.
5152 This collection is intended to be read-only.
5154 """
5156 @property
5157 def _elements(self) -> util.OrderedDict[str, ForeignKey]:
5158 # legacy - provide a dictionary view of (column_key, fk)
5159 return util.OrderedDict(zip(self.column_keys, self.elements))
5161 @property
5162 def _referred_schema(self) -> Optional[str]:
5163 for elem in self.elements:
5164 return elem._referred_schema
5165 else:
5166 return None
5168 @property
5169 def referred_table(self) -> Table:
5170 """The :class:`_schema.Table` object to which this
5171 :class:`_schema.ForeignKeyConstraint` references.
5173 This is a dynamically calculated attribute which may not be available
5174 if the constraint and/or parent table is not yet associated with
5175 a metadata collection that contains the referred table.
5177 """
5178 return self.elements[0].column.table
5180 def _validate_dest_table(self, table: Table) -> None:
5181 table_keys = {
5182 elem._table_key_within_construction() for elem in self.elements
5183 }
5184 if None not in table_keys and len(table_keys) > 1:
5185 elem0, elem1 = sorted(table_keys)[0:2]
5186 raise exc.ArgumentError(
5187 f"ForeignKeyConstraint on "
5188 f"{table.fullname}({self._col_description}) refers to "
5189 f"multiple remote tables: {elem0} and {elem1}"
5190 )
5192 @property
5193 def column_keys(self) -> _typing_Sequence[str]:
5194 """Return a list of string keys representing the local
5195 columns in this :class:`_schema.ForeignKeyConstraint`.
5197 This list is either the original string arguments sent
5198 to the constructor of the :class:`_schema.ForeignKeyConstraint`,
5199 or if the constraint has been initialized with :class:`_schema.Column`
5200 objects, is the string ``.key`` of each element.
5202 """
5203 if hasattr(self, "parent"):
5204 return self._columns.keys()
5205 else:
5206 return [
5207 col.key if isinstance(col, ColumnElement) else str(col)
5208 for col in self._pending_colargs
5209 ]
5211 @property
5212 def _col_description(self) -> str:
5213 return ", ".join(self.column_keys)
5215 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
5216 table = parent
5217 assert isinstance(table, Table)
5218 Constraint._set_parent(self, table)
5220 ColumnCollectionConstraint._set_parent(self, table)
5222 for col, fk in zip(self._columns, self.elements):
5223 if not hasattr(fk, "parent") or fk.parent is not col:
5224 fk._set_parent_with_dispatch(col)
5226 self._validate_dest_table(table)
5228 @util.deprecated(
5229 "1.4",
5230 "The :meth:`_schema.ForeignKeyConstraint.copy` method is deprecated "
5231 "and will be removed in a future release.",
5232 )
5233 def copy(
5234 self,
5235 *,
5236 schema: Optional[str] = None,
5237 target_table: Optional[Table] = None,
5238 **kw: Any,
5239 ) -> ForeignKeyConstraint:
5240 return self._copy(schema=schema, target_table=target_table, **kw)
5242 def _copy(
5243 self,
5244 *,
5245 schema: Optional[str] = None,
5246 target_table: Optional[Table] = None,
5247 **kw: Any,
5248 ) -> ForeignKeyConstraint:
5249 fkc = ForeignKeyConstraint(
5250 [x.parent.key for x in self.elements],
5251 [
5252 x._get_colspec(
5253 schema=schema,
5254 table_name=(
5255 target_table.name
5256 if target_table is not None
5257 and x._table_key_within_construction()
5258 == x.parent.table.key
5259 else None
5260 ),
5261 _is_copy=True,
5262 )
5263 for x in self.elements
5264 ],
5265 name=self.name,
5266 onupdate=self.onupdate,
5267 ondelete=self.ondelete,
5268 use_alter=self.use_alter,
5269 deferrable=self.deferrable,
5270 initially=self.initially,
5271 link_to_name=self.link_to_name,
5272 match=self.match,
5273 comment=self.comment,
5274 )
5275 for self_fk, other_fk in zip(self.elements, fkc.elements):
5276 self_fk._schema_item_copy(other_fk)
5277 return self._schema_item_copy(fkc)
5280class PrimaryKeyConstraint(ColumnCollectionConstraint):
5281 """A table-level PRIMARY KEY constraint.
5283 The :class:`.PrimaryKeyConstraint` object is present automatically
5284 on any :class:`_schema.Table` object; it is assigned a set of
5285 :class:`_schema.Column` objects corresponding to those marked with
5286 the :paramref:`_schema.Column.primary_key` flag::
5288 >>> my_table = Table(
5289 ... "mytable",
5290 ... metadata,
5291 ... Column("id", Integer, primary_key=True),
5292 ... Column("version_id", Integer, primary_key=True),
5293 ... Column("data", String(50)),
5294 ... )
5295 >>> my_table.primary_key
5296 PrimaryKeyConstraint(
5297 Column('id', Integer(), table=<mytable>,
5298 primary_key=True, nullable=False),
5299 Column('version_id', Integer(), table=<mytable>,
5300 primary_key=True, nullable=False)
5301 )
5303 The primary key of a :class:`_schema.Table` can also be specified by using
5304 a :class:`.PrimaryKeyConstraint` object explicitly; in this mode of usage,
5305 the "name" of the constraint can also be specified, as well as other
5306 options which may be recognized by dialects::
5308 my_table = Table(
5309 "mytable",
5310 metadata,
5311 Column("id", Integer),
5312 Column("version_id", Integer),
5313 Column("data", String(50)),
5314 PrimaryKeyConstraint("id", "version_id", name="mytable_pk"),
5315 )
5317 The two styles of column-specification should generally not be mixed.
5318 An warning is emitted if the columns present in the
5319 :class:`.PrimaryKeyConstraint`
5320 don't match the columns that were marked as ``primary_key=True``, if both
5321 are present; in this case, the columns are taken strictly from the
5322 :class:`.PrimaryKeyConstraint` declaration, and those columns otherwise
5323 marked as ``primary_key=True`` are ignored. This behavior is intended to
5324 be backwards compatible with previous behavior.
5326 For the use case where specific options are to be specified on the
5327 :class:`.PrimaryKeyConstraint`, but the usual style of using
5328 ``primary_key=True`` flags is still desirable, an empty
5329 :class:`.PrimaryKeyConstraint` may be specified, which will take on the
5330 primary key column collection from the :class:`_schema.Table` based on the
5331 flags::
5333 my_table = Table(
5334 "mytable",
5335 metadata,
5336 Column("id", Integer, primary_key=True),
5337 Column("version_id", Integer, primary_key=True),
5338 Column("data", String(50)),
5339 PrimaryKeyConstraint(name="mytable_pk", mssql_clustered=True),
5340 )
5342 """
5344 __visit_name__ = "primary_key_constraint"
5346 def __init__(
5347 self,
5348 *columns: _DDLColumnArgument,
5349 name: Optional[str] = None,
5350 deferrable: Optional[bool] = None,
5351 initially: Optional[str] = None,
5352 info: Optional[_InfoType] = None,
5353 _implicit_generated: bool = False,
5354 **dialect_kw: Any,
5355 ) -> None:
5356 self._implicit_generated = _implicit_generated
5357 super().__init__(
5358 *columns,
5359 name=name,
5360 deferrable=deferrable,
5361 initially=initially,
5362 info=info,
5363 **dialect_kw,
5364 )
5366 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
5367 table = parent
5368 assert isinstance(table, Table)
5369 super()._set_parent(table)
5371 if table.primary_key is not self:
5372 table.constraints.discard(table.primary_key)
5373 table.primary_key = self # type: ignore
5374 table.constraints.add(self)
5376 table_pks = [c for c in table.c if c.primary_key]
5377 if (
5378 self._columns
5379 and table_pks
5380 and set(table_pks) != set(self._columns)
5381 ):
5382 # black could not format these inline
5383 table_pk_str = ", ".join("'%s'" % c.name for c in table_pks)
5384 col_str = ", ".join("'%s'" % c.name for c in self._columns)
5386 util.warn(
5387 f"Table '{table.name}' specifies columns "
5388 f"{table_pk_str} as "
5389 f"primary_key=True, "
5390 f"not matching locally specified columns {col_str}; "
5391 f"setting the "
5392 f"current primary key columns to "
5393 f"{col_str}. "
5394 f"This warning "
5395 f"may become an exception in a future release"
5396 )
5397 table_pks[:] = []
5399 for c in self._columns:
5400 c.primary_key = True
5401 if c._user_defined_nullable is NULL_UNSPECIFIED:
5402 c.nullable = False
5403 if table_pks:
5404 self._columns.extend(table_pks)
5406 def _reload(self, columns: Iterable[Column[Any]]) -> None:
5407 """repopulate this :class:`.PrimaryKeyConstraint` given
5408 a set of columns.
5410 Existing columns in the table that are marked as primary_key=True
5411 are maintained.
5413 Also fires a new event.
5415 This is basically like putting a whole new
5416 :class:`.PrimaryKeyConstraint` object on the parent
5417 :class:`_schema.Table` object without actually replacing the object.
5419 The ordering of the given list of columns is also maintained; these
5420 columns will be appended to the list of columns after any which
5421 are already present.
5423 """
5424 # set the primary key flag on new columns.
5425 # note any existing PK cols on the table also have their
5426 # flag still set.
5427 for col in columns:
5428 col.primary_key = True
5430 self._columns.extend(columns)
5432 PrimaryKeyConstraint._autoincrement_column._reset(self) # type: ignore
5433 self._set_parent_with_dispatch(self.table)
5435 def _replace(self, col: Column[Any]) -> None:
5436 PrimaryKeyConstraint._autoincrement_column._reset(self) # type: ignore
5437 self._columns.replace(col)
5439 self.dispatch._sa_event_column_added_to_pk_constraint(self, col)
5441 @property
5442 def columns_autoinc_first(self) -> List[Column[Any]]:
5443 autoinc = self._autoincrement_column
5445 if autoinc is not None:
5446 return [autoinc] + [c for c in self._columns if c is not autoinc]
5447 else:
5448 return list(self._columns)
5450 @util.ro_memoized_property
5451 def _autoincrement_column(self) -> Optional[Column[int]]:
5452 def _validate_autoinc(col: Column[Any], autoinc_true: bool) -> bool:
5453 if col.type._type_affinity is not None and issubclass(
5454 col.type._type_affinity, type_api.NUMERICTYPE._type_affinity
5455 ):
5456 scale = col.type.scale # type: ignore[attr-defined]
5457 if scale != 0 and autoinc_true:
5458 raise exc.ArgumentError(
5459 f"Column type {col.type} with non-zero scale "
5460 f"{scale} on column '{col}' is not "
5461 f"compatible with autoincrement=True"
5462 )
5463 elif not autoinc_true:
5464 return False
5465 elif col.type._type_affinity is None or not issubclass(
5466 col.type._type_affinity, type_api.INTEGERTYPE._type_affinity
5467 ):
5468 if autoinc_true:
5469 raise exc.ArgumentError(
5470 f"Column type {col.type} on column '{col}' is not "
5471 f"compatible with autoincrement=True"
5472 )
5473 else:
5474 return False
5475 elif (
5476 col.default is not None
5477 and not isinstance(col.default, Sequence)
5478 and not autoinc_true
5479 ):
5480 return False
5481 elif (
5482 col.server_default is not None
5483 and not isinstance(col.server_default, Identity)
5484 and not autoinc_true
5485 ):
5486 return False
5487 elif col.foreign_keys and col.autoincrement not in (
5488 True,
5489 "ignore_fk",
5490 ):
5491 return False
5492 return True
5494 if len(self._columns) == 1:
5495 col = list(self._columns)[0]
5497 if col.autoincrement is True:
5498 _validate_autoinc(col, True)
5499 return col
5500 elif col.autoincrement in (
5501 "auto",
5502 "ignore_fk",
5503 ) and _validate_autoinc(col, False):
5504 return col
5505 else:
5506 return None
5508 else:
5509 autoinc = None
5510 for col in self._columns:
5511 if col.autoincrement is True:
5512 _validate_autoinc(col, True)
5513 if autoinc is not None:
5514 raise exc.ArgumentError(
5515 f"Only one Column may be marked "
5516 f"autoincrement=True, found both "
5517 f"{col.name} and {autoinc.name}."
5518 )
5519 else:
5520 autoinc = col
5522 return autoinc
5525class UniqueConstraint(ColumnCollectionConstraint):
5526 """A table-level UNIQUE constraint.
5528 Defines a single column or composite UNIQUE constraint. For a no-frills,
5529 single column constraint, adding ``unique=True`` to the ``Column``
5530 definition is a shorthand equivalent for an unnamed, single column
5531 UniqueConstraint.
5532 """
5534 __visit_name__ = "unique_constraint"
5537class Index(
5538 DialectKWArgs, ColumnCollectionMixin, HasConditionalDDL, SchemaItem
5539):
5540 """A table-level INDEX.
5542 Defines a composite (one or more column) INDEX.
5544 E.g.::
5546 sometable = Table(
5547 "sometable",
5548 metadata,
5549 Column("name", String(50)),
5550 Column("address", String(100)),
5551 )
5553 Index("some_index", sometable.c.name)
5555 For a no-frills, single column index, adding
5556 :class:`_schema.Column` also supports ``index=True``::
5558 sometable = Table(
5559 "sometable", metadata, Column("name", String(50), index=True)
5560 )
5562 For a composite index, multiple columns can be specified::
5564 Index("some_index", sometable.c.name, sometable.c.address)
5566 Functional indexes are supported as well, typically by using the
5567 :data:`.func` construct in conjunction with table-bound
5568 :class:`_schema.Column` objects::
5570 Index("some_index", func.lower(sometable.c.name))
5572 An :class:`.Index` can also be manually associated with a
5573 :class:`_schema.Table`,
5574 either through inline declaration or using
5575 :meth:`_schema.Table.append_constraint`. When this approach is used,
5576 the names
5577 of the indexed columns can be specified as strings::
5579 Table(
5580 "sometable",
5581 metadata,
5582 Column("name", String(50)),
5583 Column("address", String(100)),
5584 Index("some_index", "name", "address"),
5585 )
5587 To support functional or expression-based indexes in this form, the
5588 :func:`_expression.text` construct may be used::
5590 from sqlalchemy import text
5592 Table(
5593 "sometable",
5594 metadata,
5595 Column("name", String(50)),
5596 Column("address", String(100)),
5597 Index("some_index", text("lower(name)")),
5598 )
5600 .. seealso::
5602 :ref:`schema_indexes` - General information on :class:`.Index`.
5604 :ref:`postgresql_indexes` - PostgreSQL-specific options available for
5605 the :class:`.Index` construct.
5607 :ref:`mysql_indexes` - MySQL-specific options available for the
5608 :class:`.Index` construct.
5610 :ref:`mssql_indexes` - MSSQL-specific options available for the
5611 :class:`.Index` construct.
5613 """
5615 __visit_name__ = "index"
5617 table: Optional[Table]
5618 expressions: _typing_Sequence[Union[str, ColumnElement[Any]]]
5619 _table_bound_expressions: _typing_Sequence[ColumnElement[Any]]
5621 def __init__(
5622 self,
5623 name: Optional[str],
5624 *expressions: _DDLColumnArgument,
5625 unique: bool = False,
5626 quote: Optional[bool] = None,
5627 info: Optional[_InfoType] = None,
5628 _table: Optional[Table] = None,
5629 _column_flag: bool = False,
5630 **dialect_kw: Any,
5631 ) -> None:
5632 r"""Construct an index object.
5634 :param name:
5635 The name of the index
5637 :param \*expressions:
5638 Column expressions to include in the index. The expressions
5639 are normally instances of :class:`_schema.Column`, but may also
5640 be arbitrary SQL expressions which ultimately refer to a
5641 :class:`_schema.Column`.
5643 :param unique=False:
5644 Keyword only argument; if True, create a unique index.
5646 :param quote=None:
5647 Keyword only argument; whether to apply quoting to the name of
5648 the index. Works in the same manner as that of
5649 :paramref:`_schema.Column.quote`.
5651 :param info=None: Optional data dictionary which will be populated
5652 into the :attr:`.SchemaItem.info` attribute of this object.
5654 :param \**dialect_kw: Additional keyword arguments not mentioned above
5655 are dialect specific, and passed in the form
5656 ``<dialectname>_<argname>``. See the documentation regarding an
5657 individual dialect at :ref:`dialect_toplevel` for detail on
5658 documented arguments.
5660 """
5661 self.table = table = None
5663 self.name = quoted_name.construct(name, quote)
5664 self.unique = unique
5665 if info is not None:
5666 self.info = info
5668 # TODO: consider "table" argument being public, but for
5669 # the purpose of the fix here, it starts as private.
5670 if _table is not None:
5671 table = _table
5673 self._validate_dialect_kwargs(dialect_kw)
5675 self.expressions = []
5676 # will call _set_parent() if table-bound column
5677 # objects are present
5678 ColumnCollectionMixin.__init__(
5679 self,
5680 *expressions,
5681 _column_flag=_column_flag,
5682 _gather_expressions=self.expressions,
5683 )
5684 if table is not None:
5685 self._set_parent(table)
5687 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
5688 table = parent
5689 assert isinstance(table, Table)
5690 ColumnCollectionMixin._set_parent(self, table)
5692 if self.table is not None and table is not self.table:
5693 raise exc.ArgumentError(
5694 f"Index '{self.name}' is against table "
5695 f"'{self.table.description}', and "
5696 f"cannot be associated with table '{table.description}'."
5697 )
5698 self.table = table
5699 table.indexes.add(self)
5701 expressions = self.expressions
5702 col_expressions = self._col_expressions(table)
5703 assert len(expressions) == len(col_expressions)
5705 exprs = []
5706 for expr, colexpr in zip(expressions, col_expressions):
5707 if isinstance(expr, ClauseElement):
5708 exprs.append(expr)
5709 elif colexpr is not None:
5710 exprs.append(colexpr)
5711 else:
5712 assert False
5713 self.expressions = self._table_bound_expressions = exprs
5715 def create(
5716 self,
5717 bind: _CreateDropBind,
5718 checkfirst: Union[bool, CheckFirst] = CheckFirst.NONE,
5719 ) -> None:
5720 """Issue a ``CREATE`` statement for this
5721 :class:`.Index`, using the given
5722 :class:`.Connection` or :class:`.Engine`` for connectivity.
5724 .. seealso::
5726 :meth:`_schema.MetaData.create_all`.
5728 """
5729 bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
5731 def drop(
5732 self,
5733 bind: _CreateDropBind,
5734 checkfirst: Union[bool, CheckFirst] = CheckFirst.NONE,
5735 ) -> None:
5736 """Issue a ``DROP`` statement for this
5737 :class:`.Index`, using the given
5738 :class:`.Connection` or :class:`.Engine` for connectivity.
5740 .. seealso::
5742 :meth:`_schema.MetaData.drop_all`.
5744 """
5745 bind._run_ddl_visitor(ddl.SchemaDropper, self, checkfirst=checkfirst)
5747 def __repr__(self) -> str:
5748 exprs: _typing_Sequence[Any] # noqa: F842
5750 return "Index(%s)" % (
5751 ", ".join(
5752 [repr(self.name)]
5753 + [repr(e) for e in self.expressions]
5754 + (self.unique and ["unique=True"] or [])
5755 )
5756 )
5759_NamingSchemaCallable = Union[
5760 Callable[[Constraint, Table], str],
5761 Callable[[Index, Table], str],
5762]
5763_NamingSchemaDirective = Union[str, _NamingSchemaCallable]
5766class _NamingSchemaTD(TypedDict, total=False):
5767 fk: _NamingSchemaDirective
5768 pk: _NamingSchemaDirective
5769 ix: _NamingSchemaDirective
5770 ck: _NamingSchemaDirective
5771 uq: _NamingSchemaDirective
5774_NamingSchemaParameter = Union[
5775 # it seems like the TypedDict here is useful for pylance typeahead,
5776 # and not much else
5777 _NamingSchemaTD,
5778 # there is no form that allows Union[Type[Any], str] to work in all
5779 # cases, including breaking out Mapping[] entries for each combination
5780 # even, therefore keys must be `Any` (see #10264)
5781 Mapping[Any, _NamingSchemaDirective],
5782]
5785DEFAULT_NAMING_CONVENTION: _NamingSchemaParameter = util.immutabledict(
5786 {"ix": "ix_%(column_0_label)s"}
5787)
5790class MetaData(HasSchemaAttr):
5791 """A collection of :class:`_schema.Table`
5792 objects and their associated schema
5793 constructs.
5795 Holds a collection of :class:`_schema.Table` objects as well as
5796 an optional binding to an :class:`_engine.Engine` or
5797 :class:`_engine.Connection`. If bound, the :class:`_schema.Table` objects
5798 in the collection and their columns may participate in implicit SQL
5799 execution.
5801 The :class:`_schema.Table` objects themselves are stored in the
5802 :attr:`_schema.MetaData.tables` dictionary.
5804 :class:`_schema.MetaData` is a thread-safe object for read operations.
5805 Construction of new tables within a single :class:`_schema.MetaData`
5806 object,
5807 either explicitly or via reflection, may not be completely thread-safe.
5809 .. seealso::
5811 :ref:`metadata_describing` - Introduction to database metadata
5813 """
5815 __visit_name__ = "metadata"
5817 def __init__(
5818 self,
5819 schema: Optional[str] = None,
5820 quote_schema: Optional[bool] = None,
5821 naming_convention: Optional[_NamingSchemaParameter] = None,
5822 info: Optional[_InfoType] = None,
5823 ) -> None:
5824 """Create a new MetaData object.
5826 :param schema:
5827 The default schema to use for the :class:`_schema.Table`,
5828 :class:`.Sequence`, and potentially other objects associated with
5829 this :class:`_schema.MetaData`. Defaults to ``None``.
5831 .. seealso::
5833 :ref:`schema_metadata_schema_name` - details on how the
5834 :paramref:`_schema.MetaData.schema` parameter is used.
5836 :paramref:`_schema.Table.schema`
5838 :paramref:`.Sequence.schema`
5840 :param quote_schema:
5841 Sets the ``quote_schema`` flag for those :class:`_schema.Table`,
5842 :class:`.Sequence`, and other objects which make usage of the
5843 local ``schema`` name.
5845 :param info: Optional data dictionary which will be populated into the
5846 :attr:`.SchemaItem.info` attribute of this object.
5848 :param naming_convention: a dictionary referring to values which
5849 will establish default naming conventions for :class:`.Constraint`
5850 and :class:`.Index` objects, for those objects which are not given
5851 a name explicitly.
5853 The keys of this dictionary may be:
5855 * a constraint or Index class, e.g. the :class:`.UniqueConstraint`,
5856 :class:`_schema.ForeignKeyConstraint` class, the :class:`.Index`
5857 class
5859 * a string mnemonic for one of the known constraint classes;
5860 ``"fk"``, ``"pk"``, ``"ix"``, ``"ck"``, ``"uq"`` for foreign key,
5861 primary key, index, check, and unique constraint, respectively.
5863 * the string name of a user-defined "token" that can be used
5864 to define new naming tokens.
5866 The values associated with each "constraint class" or "constraint
5867 mnemonic" key are string naming templates, such as
5868 ``"uq_%(table_name)s_%(column_0_name)s"``,
5869 which describe how the name should be composed. The values
5870 associated with user-defined "token" keys should be callables of the
5871 form ``fn(constraint, table)``, which accepts the constraint/index
5872 object and :class:`_schema.Table` as arguments, returning a string
5873 result.
5875 The built-in names are as follows, some of which may only be
5876 available for certain types of constraint:
5878 * ``%(table_name)s`` - the name of the :class:`_schema.Table`
5879 object
5880 associated with the constraint.
5882 * ``%(referred_table_name)s`` - the name of the
5883 :class:`_schema.Table`
5884 object associated with the referencing target of a
5885 :class:`_schema.ForeignKeyConstraint`.
5887 * ``%(column_0_name)s`` - the name of the :class:`_schema.Column`
5888 at
5889 index position "0" within the constraint.
5891 * ``%(column_0N_name)s`` - the name of all :class:`_schema.Column`
5892 objects in order within the constraint, joined without a
5893 separator.
5895 * ``%(column_0_N_name)s`` - the name of all
5896 :class:`_schema.Column`
5897 objects in order within the constraint, joined with an
5898 underscore as a separator.
5900 * ``%(column_0_label)s``, ``%(column_0N_label)s``,
5901 ``%(column_0_N_label)s`` - the label of either the zeroth
5902 :class:`_schema.Column` or all :class:`.Columns`, separated with
5903 or without an underscore
5905 * ``%(column_0_key)s``, ``%(column_0N_key)s``,
5906 ``%(column_0_N_key)s`` - the key of either the zeroth
5907 :class:`_schema.Column` or all :class:`.Columns`, separated with
5908 or without an underscore
5910 * ``%(referred_column_0_name)s``, ``%(referred_column_0N_name)s``
5911 ``%(referred_column_0_N_name)s``, ``%(referred_column_0_key)s``,
5912 ``%(referred_column_0N_key)s``, ... column tokens which
5913 render the names/keys/labels of columns that are referenced
5914 by a :class:`_schema.ForeignKeyConstraint`.
5916 * ``%(constraint_name)s`` - a special key that refers to the
5917 existing name given to the constraint. When this key is
5918 present, the :class:`.Constraint` object's existing name will be
5919 replaced with one that is composed from template string that
5920 uses this token. When this token is present, it is required that
5921 the :class:`.Constraint` is given an explicit name ahead of time.
5923 * user-defined: any additional token may be implemented by passing
5924 it along with a ``fn(constraint, table)`` callable to the
5925 naming_convention dictionary.
5927 .. seealso::
5929 :ref:`constraint_naming_conventions` - for detailed usage
5930 examples.
5932 """
5933 if schema is not None and not isinstance(schema, str):
5934 raise exc.ArgumentError(
5935 "expected schema argument to be a string, "
5936 f"got {type(schema)}."
5937 )
5938 self.tables = util.FacadeDict()
5939 self.schema = quoted_name.construct(schema, quote_schema)
5940 self.naming_convention = (
5941 naming_convention
5942 if naming_convention
5943 else DEFAULT_NAMING_CONVENTION
5944 )
5945 if info:
5946 self.info = info
5947 self._schemas: Set[str] = set()
5948 self._sequences: Dict[str, Sequence] = {}
5949 self._fk_memos: Dict[Tuple[str, Optional[str]], List[ForeignKey]] = (
5950 collections.defaultdict(list)
5951 )
5952 self._objects: Set[Union[HasSchemaAttr, SchemaType]] = set()
5954 tables: util.FacadeDict[str, Table]
5955 """A dictionary of :class:`_schema.Table`
5956 objects keyed to their name or "table key".
5958 The exact key is that determined by the :attr:`_schema.Table.key`
5959 attribute;
5960 for a table with no :attr:`_schema.Table.schema` attribute,
5961 this is the same
5962 as :attr:`_schema.Table.name`. For a table with a schema,
5963 it is typically of the
5964 form ``schemaname.tablename``.
5966 .. seealso::
5968 :attr:`_schema.MetaData.sorted_tables`
5970 """
5972 def __repr__(self) -> str:
5973 return "MetaData()"
5975 def __contains__(self, table_or_key: Union[str, Table]) -> bool:
5976 if not isinstance(table_or_key, str):
5977 table_or_key = table_or_key.key
5978 return table_or_key in self.tables
5980 def _add_table(
5981 self, name: str, schema: Optional[str], table: Table
5982 ) -> None:
5983 key = _get_table_key(name, schema)
5984 self.tables._insert_item(key, table)
5985 if schema:
5986 self._schemas.add(schema)
5988 def _remove_table(self, name: str, schema: Optional[str]) -> None:
5989 key = _get_table_key(name, schema)
5990 removed = dict.pop(self.tables, key, None)
5991 if removed is not None:
5992 for fk in removed.foreign_keys:
5993 fk._remove_from_metadata(self)
5994 if self._schemas:
5995 self._schemas = {
5996 t.schema for t in self.tables.values() if t.schema is not None
5997 }
5999 def __getstate__(self) -> Dict[str, Any]:
6000 return {
6001 "tables": self.tables,
6002 "schema": self.schema,
6003 "schemas": self._schemas,
6004 "sequences": self._sequences,
6005 "fk_memos": self._fk_memos,
6006 "naming_convention": self.naming_convention,
6007 "objects": self._objects,
6008 }
6010 def __setstate__(self, state: Dict[str, Any]) -> None:
6011 self.tables = state["tables"]
6012 self.schema = state["schema"]
6013 self.naming_convention = state["naming_convention"]
6014 self._sequences = state["sequences"]
6015 self._schemas = state["schemas"]
6016 self._fk_memos = state["fk_memos"]
6017 self._objects = state.get("objects", set())
6019 def clear(self) -> None:
6020 """Clear all objects from this MetaData."""
6022 dict.clear(self.tables)
6023 self._schemas.clear()
6024 self._fk_memos.clear()
6025 self._sequences.clear()
6026 self._objects.clear()
6028 def remove(self, table: Table) -> None:
6029 """Remove the given Table object from this MetaData."""
6031 self._remove_table(table.name, table.schema)
6033 @property
6034 def sorted_tables(self) -> List[Table]:
6035 """Returns a list of :class:`_schema.Table` objects sorted in order of
6036 foreign key dependency.
6038 The sorting will place :class:`_schema.Table`
6039 objects that have dependencies
6040 first, before the dependencies themselves, representing the
6041 order in which they can be created. To get the order in which
6042 the tables would be dropped, use the ``reversed()`` Python built-in.
6044 .. warning::
6046 The :attr:`.MetaData.sorted_tables` attribute cannot by itself
6047 accommodate automatic resolution of dependency cycles between
6048 tables, which are usually caused by mutually dependent foreign key
6049 constraints. When these cycles are detected, the foreign keys
6050 of these tables are omitted from consideration in the sort.
6051 A warning is emitted when this condition occurs, which will be an
6052 exception raise in a future release. Tables which are not part
6053 of the cycle will still be returned in dependency order.
6055 To resolve these cycles, the
6056 :paramref:`_schema.ForeignKeyConstraint.use_alter` parameter may be
6057 applied to those constraints which create a cycle. Alternatively,
6058 the :func:`_schema.sort_tables_and_constraints` function will
6059 automatically return foreign key constraints in a separate
6060 collection when cycles are detected so that they may be applied
6061 to a schema separately.
6063 .. seealso::
6065 :func:`_schema.sort_tables`
6067 :func:`_schema.sort_tables_and_constraints`
6069 :attr:`_schema.MetaData.tables`
6071 :meth:`_reflection.Inspector.get_table_names`
6073 :meth:`_reflection.Inspector.get_sorted_table_and_fkc_names`
6076 """
6077 return ddl.sort_tables(
6078 sorted(self.tables.values(), key=lambda t: t.key) # type: ignore
6079 )
6081 # overload needed to work around mypy this mypy
6082 # https://github.com/python/mypy/issues/17093
6083 @overload
6084 def reflect(
6085 self,
6086 bind: Engine,
6087 schema: Optional[str] = ...,
6088 views: bool = ...,
6089 only: Union[
6090 _typing_Sequence[str], Callable[[str, MetaData], bool], None
6091 ] = ...,
6092 extend_existing: bool = ...,
6093 autoload_replace: bool = ...,
6094 resolve_fks: bool = ...,
6095 **dialect_kwargs: Any,
6096 ) -> None: ...
6098 @overload
6099 def reflect(
6100 self,
6101 bind: Connection,
6102 schema: Optional[str] = ...,
6103 views: bool = ...,
6104 only: Union[
6105 _typing_Sequence[str], Callable[[str, MetaData], bool], None
6106 ] = ...,
6107 extend_existing: bool = ...,
6108 autoload_replace: bool = ...,
6109 resolve_fks: bool = ...,
6110 **dialect_kwargs: Any,
6111 ) -> None: ...
6113 @util.preload_module("sqlalchemy.engine.reflection")
6114 def reflect(
6115 self,
6116 bind: Union[Engine, Connection],
6117 schema: Optional[str] = None,
6118 views: bool = False,
6119 only: Union[
6120 _typing_Sequence[str], Callable[[str, MetaData], bool], None
6121 ] = None,
6122 extend_existing: bool = False,
6123 autoload_replace: bool = True,
6124 resolve_fks: bool = True,
6125 **dialect_kwargs: Any,
6126 ) -> None:
6127 r"""Load all available table definitions from the database.
6129 Automatically creates ``Table`` entries in this ``MetaData`` for any
6130 table available in the database but not yet present in the
6131 ``MetaData``. May be called multiple times to pick up tables recently
6132 added to the database, however no special action is taken if a table
6133 in this ``MetaData`` no longer exists in the database.
6135 :param bind:
6136 A :class:`.Connection` or :class:`.Engine` used to access the
6137 database.
6139 :param schema:
6140 Optional, query and reflect tables from an alternate schema.
6141 If None, the schema associated with this :class:`_schema.MetaData`
6142 is used, if any.
6144 :param views:
6145 If True, also reflect views (materialized and plain).
6147 :param only:
6148 Optional. Load only a sub-set of available named tables. May be
6149 specified as a sequence of names or a callable.
6151 If a sequence of names is provided, only those tables will be
6152 reflected. An error is raised if a table is requested but not
6153 available. Named tables already present in this ``MetaData`` are
6154 ignored.
6156 If a callable is provided, it will be used as a boolean predicate to
6157 filter the list of potential table names. The callable is called
6158 with a table name and this ``MetaData`` instance as positional
6159 arguments and should return a true value for any table to reflect.
6161 :param extend_existing: Passed along to each :class:`_schema.Table` as
6162 :paramref:`_schema.Table.extend_existing`.
6164 :param autoload_replace: Passed along to each :class:`_schema.Table`
6165 as
6166 :paramref:`_schema.Table.autoload_replace`.
6168 :param resolve_fks: if True, reflect :class:`_schema.Table`
6169 objects linked
6170 to :class:`_schema.ForeignKey` objects located in each
6171 :class:`_schema.Table`.
6172 For :meth:`_schema.MetaData.reflect`,
6173 this has the effect of reflecting
6174 related tables that might otherwise not be in the list of tables
6175 being reflected, for example if the referenced table is in a
6176 different schema or is omitted via the
6177 :paramref:`.MetaData.reflect.only` parameter. When False,
6178 :class:`_schema.ForeignKey` objects are not followed to the
6179 :class:`_schema.Table`
6180 in which they link, however if the related table is also part of the
6181 list of tables that would be reflected in any case, the
6182 :class:`_schema.ForeignKey` object will still resolve to its related
6183 :class:`_schema.Table` after the :meth:`_schema.MetaData.reflect`
6184 operation is
6185 complete. Defaults to True.
6187 .. seealso::
6189 :paramref:`_schema.Table.resolve_fks`
6191 :param \**dialect_kwargs: Additional keyword arguments not mentioned
6192 above are dialect specific, and passed in the form
6193 ``<dialectname>_<argname>``. See the documentation regarding an
6194 individual dialect at :ref:`dialect_toplevel` for detail on
6195 documented arguments.
6197 .. seealso::
6199 :ref:`metadata_reflection_toplevel`
6201 :meth:`_events.DDLEvents.column_reflect` - Event used to customize
6202 the reflected columns. Usually used to generalize the types using
6203 :meth:`_types.TypeEngine.as_generic`
6205 :ref:`metadata_reflection_dbagnostic_types` - describes how to
6206 reflect tables using general types.
6208 """
6210 with inspection.inspect(bind)._inspection_context() as insp:
6211 reflect_opts: Any = {
6212 "autoload_with": insp,
6213 "extend_existing": extend_existing,
6214 "autoload_replace": autoload_replace,
6215 "resolve_fks": resolve_fks,
6216 "_extend_on": set(),
6217 }
6219 reflect_opts.update(dialect_kwargs)
6221 if schema is None:
6222 schema = self.schema
6224 if schema is not None:
6225 reflect_opts["schema"] = schema
6227 kind = util.preloaded.engine_reflection.ObjectKind.TABLE
6228 available: util.OrderedSet[str] = util.OrderedSet(
6229 insp.get_table_names(schema, **dialect_kwargs)
6230 )
6231 if views:
6232 kind = util.preloaded.engine_reflection.ObjectKind.ANY
6233 available.update(insp.get_view_names(schema, **dialect_kwargs))
6234 try:
6235 available.update(
6236 insp.get_materialized_view_names(
6237 schema, **dialect_kwargs
6238 )
6239 )
6240 except NotImplementedError:
6241 pass
6243 if schema is not None:
6244 available_w_schema: util.OrderedSet[str] = util.OrderedSet(
6245 [f"{schema}.{name}" for name in available]
6246 )
6247 else:
6248 available_w_schema = available
6250 current = set(self.tables)
6252 if only is None:
6253 load = [
6254 name
6255 for name, schname in zip(available, available_w_schema)
6256 if extend_existing or schname not in current
6257 ]
6258 elif callable(only):
6259 load = [
6260 name
6261 for name, schname in zip(available, available_w_schema)
6262 if (extend_existing or schname not in current)
6263 and only(name, self)
6264 ]
6265 else:
6266 missing = [name for name in only if name not in available]
6267 if missing:
6268 s = schema and (" schema '%s'" % schema) or ""
6269 missing_str = ", ".join(missing)
6270 raise exc.InvalidRequestError(
6271 f"Could not reflect: requested table(s) not available "
6272 f"in {bind.engine!r}{s}: ({missing_str})"
6273 )
6274 load = [
6275 name
6276 for name in only
6277 if extend_existing or name not in current
6278 ]
6279 # pass the available tables so the inspector can
6280 # choose to ignore the filter_names
6281 _reflect_info = insp._get_reflection_info(
6282 schema=schema,
6283 filter_names=load,
6284 available=available,
6285 kind=kind,
6286 scope=util.preloaded.engine_reflection.ObjectScope.ANY,
6287 **dialect_kwargs,
6288 )
6289 reflect_opts["_reflect_info"] = _reflect_info
6291 for name in load:
6292 try:
6293 Table(name, self, **reflect_opts)
6294 except exc.UnreflectableTableError as uerr:
6295 util.warn(f"Skipping table {name}: {uerr}")
6297 def create_all(
6298 self,
6299 bind: _CreateDropBind,
6300 tables: Optional[_typing_Sequence[Table]] = None,
6301 checkfirst: Union[bool, CheckFirst] = CheckFirst.ALL,
6302 ) -> None:
6303 """Create all tables stored in this metadata.
6305 Conditional by default, will not attempt to recreate tables already
6306 present in the target database.
6308 :param bind:
6309 A :class:`.Connection` or :class:`.Engine` used to access the
6310 database.
6312 :param tables:
6313 Optional list of ``Table`` objects, which is a subset of the total
6314 tables in the ``MetaData`` (others are ignored).
6316 :param checkfirst: A boolean value or instance of :class:`.CheckFirst`.
6317 Indicates which objects should be checked for within a separate pass
6318 before creating schema objects.
6320 """
6321 bind._run_ddl_visitor(
6322 ddl.SchemaGenerator, self, checkfirst=checkfirst, tables=tables
6323 )
6325 def drop_all(
6326 self,
6327 bind: _CreateDropBind,
6328 tables: Optional[_typing_Sequence[Table]] = None,
6329 checkfirst: Union[bool, CheckFirst] = CheckFirst.ALL,
6330 ) -> None:
6331 """Drop all tables stored in this metadata.
6333 Conditional by default, will not attempt to drop tables not present in
6334 the target database.
6336 :param bind:
6337 A :class:`.Connection` or :class:`.Engine` used to access the
6338 database.
6340 :param tables:
6341 Optional list of ``Table`` objects, which is a subset of the
6342 total tables in the ``MetaData`` (others are ignored).
6344 :param checkfirst: A boolean value or instance of :class:`.CheckFirst`.
6345 Indicates which objects should be checked for within a separate pass
6346 before dropping schema objects.
6348 """
6349 bind._run_ddl_visitor(
6350 ddl.SchemaDropper, self, checkfirst=checkfirst, tables=tables
6351 )
6353 @property
6354 def schemas(self) -> _typing_Sequence[str]:
6355 """A sequence of schema names that are present in this MetaData."""
6356 schemas = self._schemas
6357 if self.schema:
6358 schemas = schemas | {self.schema}
6359 return tuple(schemas)
6361 def get_schema_objects(
6362 self,
6363 kind: Type[_T],
6364 *,
6365 schema: Union[str, None, Literal[_NoArg.NO_ARG]] = _NoArg.NO_ARG,
6366 ) -> _typing_Sequence[_T]:
6367 """Return a sequence of schema objects of the given kind.
6369 This method can be used to return :class:`_sqltypes.Enum`,
6370 :class:`.Sequence`, etc. objects registered in this
6371 :class:`_schema.MetaData`.
6373 :param kind: a type that indicates what object to return, such as
6374 :class:`Enum` or :class:`Sequence`.
6375 :param schema: Optional, a schema name to filter the objects by. If
6376 not provided the default schema of the metadata is used.
6378 """
6380 if schema is _NoArg.NO_ARG:
6381 schema = self.schema
6382 return tuple(
6383 obj
6384 for obj in self._objects
6385 if isinstance(obj, kind) and obj.schema == schema
6386 )
6388 def get_schema_object_by_name(
6389 self,
6390 kind: Type[_T],
6391 name: str,
6392 *,
6393 schema: Union[str, None, Literal[_NoArg.NO_ARG]] = _NoArg.NO_ARG,
6394 ) -> Optional[_T]:
6395 """Return a schema objects of the given kind and name if found.
6397 This method can be used to return :class:`_sqltypes.Enum`,
6398 :class:`.Sequence`, etc. objects registered in this
6399 :class:`_schema.MetaData`.
6401 :param kind: a type that indicates what object to return, such as
6402 :class:`Enum` or :class:`Sequence`.
6403 :param name: the name of the object to return.
6404 :param schema: Optional, a schema name to filter the objects by. If
6405 not provided the default schema of the metadata is used.
6407 """
6409 for obj in self.get_schema_objects(kind, schema=schema):
6410 if getattr(obj, "name", None) == name:
6411 return obj
6412 return None
6414 def _register_object(self, obj: Union[HasSchemaAttr, SchemaType]) -> None:
6415 self._objects.add(obj)
6418class Computed(FetchedValue, SchemaItem):
6419 """Defines a generated column, i.e. "GENERATED ALWAYS AS" syntax.
6421 The :class:`.Computed` construct is an inline construct added to the
6422 argument list of a :class:`_schema.Column` object::
6424 from sqlalchemy import Computed
6426 Table(
6427 "square",
6428 metadata_obj,
6429 Column("side", Float, nullable=False),
6430 Column("area", Float, Computed("side * side")),
6431 )
6433 See the linked documentation below for complete details.
6435 .. seealso::
6437 :ref:`computed_ddl`
6439 """
6441 __visit_name__ = "computed_column"
6443 column: Optional[Column[Any]]
6445 @_document_text_coercion(
6446 "sqltext", ":class:`.Computed`", ":paramref:`.Computed.sqltext`"
6447 )
6448 def __init__(
6449 self, sqltext: _DDLColumnArgument, persisted: Optional[bool] = None
6450 ) -> None:
6451 """Construct a GENERATED ALWAYS AS DDL construct to accompany a
6452 :class:`_schema.Column`.
6454 :param sqltext:
6455 A string containing the column generation expression, which will be
6456 used verbatim, or a SQL expression construct, such as a
6457 :func:`_expression.text`
6458 object. If given as a string, the object is converted to a
6459 :func:`_expression.text` object.
6461 :param persisted:
6462 Optional, controls how this column should be persisted by the
6463 database. Possible values are:
6465 * ``None``, the default, it will use the default persistence
6466 defined by the database.
6467 * ``True``, will render ``GENERATED ALWAYS AS ... STORED``, or the
6468 equivalent for the target database if supported.
6469 * ``False``, will render ``GENERATED ALWAYS AS ... VIRTUAL``, or
6470 the equivalent for the target database if supported.
6472 Specifying ``True`` or ``False`` may raise an error when the DDL
6473 is emitted to the target database if the database does not support
6474 that persistence option. Leaving this parameter at its default
6475 of ``None`` is guaranteed to succeed for all databases that support
6476 ``GENERATED ALWAYS AS``.
6478 """
6479 self.sqltext = coercions.expect(roles.DDLExpressionRole, sqltext)
6480 self.persisted = persisted
6481 self.column = None
6483 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
6484 assert isinstance(parent, Column)
6486 if not isinstance(
6487 parent.server_default, (type(None), Computed)
6488 ) or not isinstance(parent.server_onupdate, (type(None), Computed)):
6489 raise exc.ArgumentError(
6490 "A generated column cannot specify a server_default or a "
6491 "server_onupdate argument"
6492 )
6493 self.column = parent
6494 parent.computed = self
6495 self.column.server_onupdate = self
6496 self.column.server_default = self
6498 def _as_for_update(self, for_update: bool) -> FetchedValue:
6499 return self
6501 @util.deprecated(
6502 "1.4",
6503 "The :meth:`_schema.Computed.copy` method is deprecated "
6504 "and will be removed in a future release.",
6505 )
6506 def copy(
6507 self, *, target_table: Optional[Table] = None, **kw: Any
6508 ) -> Computed:
6509 return self._copy(target_table=target_table, **kw)
6511 def _copy(
6512 self, *, target_table: Optional[Table] = None, **kw: Any
6513 ) -> Computed:
6514 sqltext = _copy_expression(
6515 self.sqltext,
6516 self.column.table if self.column is not None else None,
6517 target_table,
6518 )
6519 g = Computed(sqltext, persisted=self.persisted)
6521 return self._schema_item_copy(g)
6524class Identity(IdentityOptions, FetchedValue, SchemaItem):
6525 """Defines an identity column, i.e. "GENERATED { ALWAYS | BY DEFAULT }
6526 AS IDENTITY" syntax.
6528 The :class:`.Identity` construct is an inline construct added to the
6529 argument list of a :class:`_schema.Column` object::
6531 from sqlalchemy import Identity
6533 Table(
6534 "foo",
6535 metadata_obj,
6536 Column("id", Integer, Identity()),
6537 Column("description", Text),
6538 )
6540 See the linked documentation below for complete details.
6542 .. versionadded:: 1.4
6544 .. seealso::
6546 :ref:`identity_ddl`
6548 """
6550 __visit_name__ = "identity_column"
6552 is_identity = True
6554 @util.deprecated_params(
6555 order=(
6556 "2.1",
6557 "This parameter is supported only by Oracle Database, "
6558 "use ``oracle_order`` instead.",
6559 ),
6560 on_null=(
6561 "2.1",
6562 "This parameter is supported only by Oracle Database, "
6563 "use ``oracle_on_null`` instead.",
6564 ),
6565 )
6566 def __init__(
6567 self,
6568 always: Optional[bool] = False,
6569 on_null: Optional[bool] = None,
6570 start: Optional[int] = None,
6571 increment: Optional[int] = None,
6572 minvalue: Optional[int] = None,
6573 maxvalue: Optional[int] = None,
6574 nominvalue: Optional[bool] = None,
6575 nomaxvalue: Optional[bool] = None,
6576 cycle: Optional[bool] = None,
6577 cache: Optional[int] = None,
6578 order: Optional[bool] = None,
6579 **dialect_kw: Any,
6580 ) -> None:
6581 """Construct a GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY DDL
6582 construct to accompany a :class:`_schema.Column`.
6584 See the :class:`.Sequence` documentation for a complete description
6585 of most parameters.
6587 .. note::
6588 MSSQL supports this construct as the preferred alternative to
6589 generate an IDENTITY on a column, but it uses non standard
6590 syntax that only support :paramref:`_schema.Identity.start`
6591 and :paramref:`_schema.Identity.increment`.
6592 All other parameters are ignored.
6594 :param always:
6595 A boolean, that indicates the type of identity column.
6596 If ``False`` is specified, the default, then the user-specified
6597 value takes precedence.
6598 If ``True`` is specified, a user-specified value is not accepted (
6599 on some backends, like PostgreSQL, OVERRIDING SYSTEM VALUE, or
6600 similar, may be specified in an INSERT to override the sequence
6601 value).
6602 Some backends also have a default value for this parameter,
6603 ``None`` can be used to omit rendering this part in the DDL. It
6604 will be treated as ``False`` if a backend does not have a default
6605 value.
6607 :param on_null:
6608 Set to ``True`` to specify ON NULL in conjunction with a
6609 ``always=False`` identity column. This option is only supported on
6610 some backends, like Oracle Database.
6612 :param start: the starting index of the sequence.
6613 :param increment: the increment value of the sequence.
6614 :param minvalue: the minimum value of the sequence.
6615 :param maxvalue: the maximum value of the sequence.
6616 :param nominvalue: no minimum value of the sequence.
6617 :param nomaxvalue: no maximum value of the sequence.
6618 :param cycle: allows the sequence to wrap around when the maxvalue
6619 or minvalue has been reached.
6620 :param cache: optional integer value; number of future values in the
6621 sequence which are calculated in advance.
6622 :param order: optional boolean value; if true, renders the
6623 ORDER keyword.
6625 """
6626 self.dialect_options
6627 if on_null is not None:
6628 if "oracle_on_null" in dialect_kw:
6629 raise exc.ArgumentError(
6630 "Cannot specify both 'on_null' and 'oracle_on_null'. "
6631 "Please use only 'oracle_on_null'."
6632 )
6633 dialect_kw["oracle_on_null"] = on_null
6635 IdentityOptions.__init__(
6636 self,
6637 start=start,
6638 increment=increment,
6639 minvalue=minvalue,
6640 maxvalue=maxvalue,
6641 nominvalue=nominvalue,
6642 nomaxvalue=nomaxvalue,
6643 cycle=cycle,
6644 cache=cache,
6645 order=order,
6646 **dialect_kw,
6647 )
6648 self.always = always
6649 self.column = None
6651 @property
6652 def on_null(self) -> Optional[bool]:
6653 """Alias of the ``dialect_kwargs`` ``'oracle_on_null'``.
6655 .. deprecated:: 2.1 The 'on_null' attribute is deprecated.
6656 """
6657 value: Optional[bool] = self.dialect_kwargs.get("oracle_on_null")
6658 return value
6660 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
6661 assert isinstance(parent, Column)
6662 if not isinstance(
6663 parent.server_default, (type(None), Identity)
6664 ) or not isinstance(parent.server_onupdate, type(None)):
6665 raise exc.ArgumentError(
6666 "A column with an Identity object cannot specify a "
6667 "server_default or a server_onupdate argument"
6668 )
6669 if parent.autoincrement is False:
6670 raise exc.ArgumentError(
6671 "A column with an Identity object cannot specify "
6672 "autoincrement=False"
6673 )
6674 self.column = parent
6676 parent.identity = self
6677 if parent._user_defined_nullable is NULL_UNSPECIFIED:
6678 parent.nullable = False
6680 parent.server_default = self
6682 def _as_for_update(self, for_update: bool) -> FetchedValue:
6683 return self
6685 @util.deprecated(
6686 "1.4",
6687 "The :meth:`_schema.Identity.copy` method is deprecated "
6688 "and will be removed in a future release.",
6689 )
6690 def copy(self, **kw: Any) -> Identity:
6691 return self._copy(**kw)
6693 def _copy(self, **kw: Any) -> Identity:
6694 i = Identity(**self._as_dict(), **self.dialect_kwargs)
6696 return self._schema_item_copy(i)
6698 def _as_dict(self) -> Dict[str, Any]:
6699 return {
6700 # always=None means something different than always=False
6701 "always": self.always,
6702 **super()._as_dict(),
6703 }