Shortcuts on this page
r m x toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1# sql/schema.py
2# Copyright (C) 2005-2026 the SQLAlchemy authors and contributors
3# <see AUTHORS file>
4#
5# This module is part of SQLAlchemy and is released under
6# the MIT License: https://www.opensource.org/licenses/mit-license.php
8"""The schema module provides the building blocks for database metadata.
10Each element within this module describes a database entity which can be
11created and dropped, or is otherwise part of such an entity. Examples include
12tables, columns, sequences, and indexes.
14All entities are subclasses of :class:`~sqlalchemy.schema.SchemaItem`, and as
15defined in this module they are intended to be agnostic of any vendor-specific
16constructs.
18A collection of entities are grouped into a unit called
19:class:`~sqlalchemy.schema.MetaData`. MetaData serves as a logical grouping of
20schema elements, and can also be associated with an actual database connection
21such that operations involving the contained elements can contact the database
22as needed.
24Two of the elements here also build upon their "syntactic" counterparts, which
25are defined in :class:`~sqlalchemy.sql.expression.`, specifically
26:class:`~sqlalchemy.schema.Table` and :class:`~sqlalchemy.schema.Column`.
27Since these objects are part of the SQL expression language, they are usable
28as components in SQL expressions.
30"""
32from __future__ import annotations
34from abc import ABC
35import collections
36from enum import Enum
37import operator
38import typing
39from typing import Any
40from typing import Callable
41from typing import cast
42from typing import Collection
43from typing import Dict
44from typing import Final
45from typing import Iterable
46from typing import Iterator
47from typing import List
48from typing import Literal
49from typing import Mapping
50from typing import NoReturn
51from typing import Optional
52from typing import overload
53from typing import Protocol
54from typing import Sequence as _typing_Sequence
55from typing import Set
56from typing import Tuple
57from typing import Type
58from typing import TYPE_CHECKING
59from typing import TypedDict
60from typing import TypeGuard
61from typing import TypeVar
62from typing import Union
64from . import coercions
65from . import ddl
66from . import roles
67from . import type_api
68from . import visitors
69from ._annotated_cols import _ColCC_co
70from ._annotated_cols import _extract_columns_from_class
71from ._annotated_cols import _TC_co
72from ._annotated_cols import Named
73from ._annotated_cols import TypedColumns
74from ._typing import _T
75from .base import _DefaultDescriptionTuple
76from .base import _NoArg
77from .base import _NoneName
78from .base import _SentinelColumnCharacterization
79from .base import _SentinelDefaultCharacterization
80from .base import DedupeColumnCollection
81from .base import DialectKWArgs
82from .base import Executable
83from .base import SchemaEventTarget as SchemaEventTarget
84from .base import SchemaVisitable as SchemaVisitable
85from .coercions import _document_text_coercion
86from .ddl import CheckFirst
87from .elements import ClauseElement
88from .elements import ColumnClause
89from .elements import ColumnElement
90from .elements import quoted_name
91from .elements import TextClause
92from .selectable import TableClause
93from .type_api import to_instance
94from .visitors import ExternallyTraversible
95from .. import event
96from .. import exc
97from .. import inspection
98from .. import util
99from ..util import HasMemoized
100from ..util.typing import Self
102if typing.TYPE_CHECKING:
103 from ._typing import _AutoIncrementType
104 from ._typing import _CreateDropBind
105 from ._typing import _DDLColumnArgument
106 from ._typing import _DDLColumnReferenceArgument
107 from ._typing import _InfoType
108 from ._typing import _TextCoercedExpressionArgument
109 from ._typing import _TypeEngineArgument
110 from .base import ColumnSet
111 from .base import ReadOnlyColumnCollection
112 from .compiler import DDLCompiler
113 from .ddl import TableCreateDDL
114 from .ddl import TableDropDDL
115 from .elements import BindParameter
116 from .elements import KeyedColumnElement
117 from .functions import Function
118 from .sqltypes import SchemaType
119 from .type_api import TypeEngine
120 from .visitors import anon_map
121 from ..engine import Connection
122 from ..engine import Engine
123 from ..engine.interfaces import _CoreMultiExecuteParams
124 from ..engine.interfaces import CoreExecuteOptionsParameter
125 from ..engine.interfaces import ExecutionContext
126 from ..engine.reflection import _ReflectionInfo
127 from ..sql.selectable import FromClause
129_SI = TypeVar("_SI", bound="SchemaItem")
130_TAB = TypeVar("_TAB", bound="Table")
133_ConstraintNameArgument = Optional[Union[str, _NoneName]]
135_ServerDefaultArgument = Union[
136 "FetchedValue", str, TextClause, ColumnElement[Any]
137]
139_ServerOnUpdateArgument = _ServerDefaultArgument
142class SchemaConst(Enum):
143 RETAIN_SCHEMA = 1
144 """Symbol indicating that a :class:`_schema.Table`, :class:`.Sequence`
145 or in some cases a :class:`_schema.ForeignKey` object, in situations
146 where the object is being copied for a :meth:`.Table.to_metadata`
147 operation, should retain the schema name that it already has.
149 """
151 BLANK_SCHEMA = 2
152 """Symbol indicating that a :class:`_schema.Table` or :class:`.Sequence`
153 should have 'None' for its schema, even if the parent
154 :class:`_schema.MetaData` has specified a schema.
156 .. seealso::
158 :paramref:`_schema.MetaData.schema`
160 :paramref:`_schema.Table.schema`
162 :paramref:`.Sequence.schema`
164 """
166 NULL_UNSPECIFIED = 3
167 """Symbol indicating the "nullable" keyword was not passed to a Column.
169 This is used to distinguish between the use case of passing
170 ``nullable=None`` to a :class:`.Column`, which has special meaning
171 on some backends such as SQL Server.
173 """
176RETAIN_SCHEMA: Final[Literal[SchemaConst.RETAIN_SCHEMA]] = (
177 SchemaConst.RETAIN_SCHEMA
178)
179BLANK_SCHEMA: Final[Literal[SchemaConst.BLANK_SCHEMA]] = (
180 SchemaConst.BLANK_SCHEMA
181)
182NULL_UNSPECIFIED: Final[Literal[SchemaConst.NULL_UNSPECIFIED]] = (
183 SchemaConst.NULL_UNSPECIFIED
184)
187def _get_table_key(name: str, schema: Optional[str]) -> str:
188 if schema is None:
189 return name
190 else:
191 return schema + "." + name
194# this should really be in sql/util.py but we'd have to
195# break an import cycle
196def _copy_expression(
197 expression: ColumnElement[Any],
198 source_table: Optional[Table],
199 target_table: Optional[Table],
200) -> ColumnElement[Any]:
201 if source_table is None or target_table is None:
202 return expression
204 fixed_source_table = source_table
205 fixed_target_table = target_table
207 def replace(
208 element: ExternallyTraversible, **kw: Any
209 ) -> Optional[ExternallyTraversible]:
210 if (
211 isinstance(element, Column)
212 and element.table is fixed_source_table
213 and element.key in fixed_source_table.c
214 ):
215 return fixed_target_table.c[element.key]
216 else:
217 return None
219 return cast(
220 ColumnElement[Any],
221 visitors.replacement_traverse(expression, {}, replace),
222 )
225@inspection._self_inspects
226class SchemaItem(SchemaVisitable):
227 """Base class for items that define a database schema."""
229 __visit_name__ = "schema_item"
231 create_drop_stringify_dialect = "default"
233 def _init_items(self, *args: SchemaItem, **kw: Any) -> None:
234 """Initialize the list of child items for this SchemaItem."""
235 for item in args:
236 if item is not None:
237 try:
238 spwd = item._set_parent_with_dispatch
239 except AttributeError as err:
240 raise exc.ArgumentError(
241 "'SchemaItem' object, such as a 'Column' or a "
242 f"'Constraint' expected, got {item!r}"
243 ) from err
244 else:
245 spwd(self, **kw)
247 def __repr__(self) -> str:
248 return util.generic_repr(self, omit_kwarg=["info"])
250 @util.memoized_property
251 def info(self) -> _InfoType:
252 """Info dictionary associated with the object, allowing user-defined
253 data to be associated with this :class:`.SchemaItem`.
255 The dictionary is automatically generated when first accessed.
256 It can also be specified in the constructor of some objects,
257 such as :class:`_schema.Table` and :class:`_schema.Column`.
259 """
260 return {}
262 def _schema_item_copy(self, schema_item: _SI) -> _SI:
263 if "info" in self.__dict__:
264 schema_item.info = self.info.copy()
265 schema_item.dispatch._update(self.dispatch)
266 return schema_item
268 _use_schema_map = True
271class HasConditionalDDL:
272 """define a class that includes the :meth:`.HasConditionalDDL.ddl_if`
273 method, allowing for conditional rendering of DDL.
275 Currently applies to constraints and indexes.
277 .. versionadded:: 2.0
280 """
282 _ddl_if: Optional[ddl.DDLIf] = None
284 def ddl_if(
285 self,
286 dialect: Optional[str] = None,
287 callable_: Optional[ddl.DDLIfCallable] = None,
288 state: Optional[Any] = None,
289 ) -> Self:
290 r"""apply a conditional DDL rule to this schema item.
292 These rules work in a similar manner to the
293 :meth:`.ExecutableDDLElement.execute_if` callable, with the added
294 feature that the criteria may be checked within the DDL compilation
295 phase for a construct such as :class:`.CreateTable`.
296 :meth:`.HasConditionalDDL.ddl_if` currently applies towards the
297 :class:`.Index` construct as well as all :class:`.Constraint`
298 constructs.
300 :param dialect: string name of a dialect, or a tuple of string names
301 to indicate multiple dialect types.
303 :param callable\_: a callable that is constructed using the same form
304 as that described in
305 :paramref:`.ExecutableDDLElement.execute_if.callable_`.
307 :param state: any arbitrary object that will be passed to the
308 callable, if present.
310 .. versionadded:: 2.0
312 .. seealso::
314 :ref:`schema_ddl_ddl_if` - background and usage examples
317 """
318 self._ddl_if = ddl.DDLIf(dialect, callable_, state)
319 return self
322class HasSchemaAttr(SchemaItem):
323 """schema item that includes a top-level schema name"""
325 schema: Optional[str]
328class Table(
329 DialectKWArgs,
330 HasSchemaAttr,
331 TableClause[_ColCC_co],
332 inspection.Inspectable["Table"],
333):
334 r"""Represent a table in a database.
336 e.g.::
338 from sqlalchemy import Table, MetaData, Integer, String, Column
340 metadata = MetaData()
342 mytable = Table(
343 "mytable",
344 metadata,
345 Column("mytable_id", Integer, primary_key=True),
346 Column("value", String(50)),
347 )
349 The :class:`_schema.Table`
350 object constructs a unique instance of itself based
351 on its name and optional schema name within the given
352 :class:`_schema.MetaData` object. Calling the :class:`_schema.Table`
353 constructor with the same name and same :class:`_schema.MetaData` argument
354 a second time will return the *same* :class:`_schema.Table`
355 object - in this way
356 the :class:`_schema.Table` constructor acts as a registry function.
358 May also be defined as "typed table" by passing a subclass of
359 :class:`_schema.TypedColumns` as the 3rd argument::
361 from sqlalchemy import TypedColumns, select
364 class user_cols(TypedColumns):
365 id = Column(Integer, primary_key=True)
366 name: Column[str]
367 age: Column[int]
368 middle_name: Column[str | None]
370 # optional, used to infer the select types when selecting the table
371 __row_pos__: tuple[int, str, int, str | None]
374 user = Table("user", metadata, user_cols)
376 # the columns are typed: the statement has type Select[int, str]
377 stmt = sa.select(user.c.id, user.c.name).where(user.c.age > 30)
379 # Inferred as Select[int, str, int, str | None] thanks to __row_pos__
380 stmt1 = user.select()
381 stmt2 = sa.select(user)
383 The :attr:`sqlalchemy.sql._annotated_cols.HasRowPos.__row_pos__`
384 annotation is optional, and it's used to infer the types in a
385 :class:`_sql.Select` when selecting the complete table.
386 If a :class:`_schema.TypedColumns` does not define it,
387 the default ``Select[*tuple[Any]]`` will be inferred.
389 An existing :class:`Table` can be casted as "typed table" using
390 the :meth:`Table.with_cols`::
392 class mytable_cols(TypedColumns):
393 mytable_id: Column[int]
394 value: Column[str | None]
397 typed_mytable = mytable.with_cols(mytable_cols)
399 .. seealso::
401 :ref:`metadata_describing` Introduction to database metadata
403 :class:`_schema.TypedColumns` More information about typed column
404 definition
406 .. versionchanged:: 2.1.0b2 - :class:`_schema.Table` is now generic to
407 support "typed tables"
408 """
410 __visit_name__ = "table"
412 if TYPE_CHECKING:
414 @util.ro_non_memoized_property
415 def primary_key(self) -> PrimaryKeyConstraint: ...
417 @util.ro_non_memoized_property
418 def foreign_keys(self) -> Set[ForeignKey]: ...
420 def with_cols(self, type_: type[_TC_co]) -> Table[_TC_co]: ...
422 _columns: DedupeColumnCollection[Column[Any]] # type: ignore[assignment]
424 _sentinel_column: Optional[Column[Any]]
426 constraints: Set[Constraint]
427 """A collection of all :class:`_schema.Constraint` objects associated with
428 this :class:`_schema.Table`.
430 Includes :class:`_schema.PrimaryKeyConstraint`,
431 :class:`_schema.ForeignKeyConstraint`, :class:`_schema.UniqueConstraint`,
432 :class:`_schema.CheckConstraint`. A separate collection
433 :attr:`_schema.Table.foreign_key_constraints` refers to the collection
434 of all :class:`_schema.ForeignKeyConstraint` objects, and the
435 :attr:`_schema.Table.primary_key` attribute refers to the single
436 :class:`_schema.PrimaryKeyConstraint` associated with the
437 :class:`_schema.Table`.
439 .. seealso::
441 :attr:`_schema.Table.constraints`
443 :attr:`_schema.Table.primary_key`
445 :attr:`_schema.Table.foreign_key_constraints`
447 :attr:`_schema.Table.indexes`
449 :class:`_reflection.Inspector`
452 """
454 indexes: Set[Index]
455 """A collection of all :class:`_schema.Index` objects associated with this
456 :class:`_schema.Table`.
458 .. seealso::
460 :meth:`_reflection.Inspector.get_indexes`
462 """
464 def _gen_cache_key(
465 self, anon_map: anon_map, bindparams: List[BindParameter[Any]]
466 ) -> Tuple[Any, ...]:
467 if self._annotations:
468 return (self,) + self._annotations_cache_key
469 else:
470 return (self,)
472 if not typing.TYPE_CHECKING:
473 # typing tools seem to be inconsistent in how they handle
474 # __new__, so suggest this pattern for classes that use
475 # __new__. apply typing to the __init__ method normally
476 @util.deprecated_params(
477 mustexist=(
478 "1.4",
479 "Deprecated alias of :paramref:`_schema.Table.must_exist`",
480 ),
481 )
482 def __new__(cls, *args: Any, **kw: Any) -> Any:
483 return cls._new(*args, **kw)
485 @classmethod
486 def _new(cls, *args: Any, **kw: Any) -> Any:
487 if not args and not kw:
488 # python3k pickle seems to call this
489 return object.__new__(cls)
491 try:
492 name, metadata, *other_args = args
493 except ValueError:
494 raise TypeError(
495 "Table() takes at least two positional-only "
496 "arguments 'name', and 'metadata'"
497 ) from None
498 if other_args and isinstance(other_args[0], type):
499 typed_columns_cls = other_args[0]
500 if not issubclass(typed_columns_cls, TypedColumns):
501 raise exc.InvalidRequestError(
502 "The ``typed_columns_cls`` argument requires a "
503 "TypedColumns subclass."
504 )
505 elif hasattr(typed_columns_cls, "_sa_class_manager"):
506 # an orm class subclassed with TypedColumns. Reject it
507 raise exc.InvalidRequestError(
508 "To get a typed table from an ORM class, use the "
509 "`as_typed_table()` function instead."
510 )
512 extracted_columns = _extract_columns_from_class(typed_columns_cls)
513 other_args = extracted_columns + other_args[1:]
514 elif "typed_columns_cls" in kw:
515 raise TypeError(
516 "The ``typed_columns_cls`` argument may be passed "
517 "only positionally"
518 )
520 schema = kw.get("schema", None)
521 if schema is None:
522 schema = metadata.schema
523 elif schema is BLANK_SCHEMA:
524 schema = None
525 keep_existing = kw.get("keep_existing", False)
526 extend_existing = kw.get("extend_existing", False)
528 if keep_existing and extend_existing:
529 msg = "keep_existing and extend_existing are mutually exclusive."
530 raise exc.ArgumentError(msg)
532 must_exist = kw.pop("must_exist", kw.pop("mustexist", False))
533 key = _get_table_key(name, schema)
534 if key in metadata.tables:
535 if not keep_existing and not extend_existing and bool(other_args):
536 raise exc.InvalidRequestError(
537 f"Table '{key}' is already defined for this MetaData "
538 "instance. Specify 'extend_existing=True' "
539 "to redefine "
540 "options and columns on an "
541 "existing Table object."
542 )
543 table = metadata.tables[key]
544 if extend_existing:
545 table._init_existing(*other_args, **kw)
546 return table
547 else:
548 if must_exist:
549 raise exc.InvalidRequestError(f"Table '{key}' not defined")
550 table = object.__new__(cls)
551 table.dispatch.before_parent_attach(table, metadata)
552 metadata._add_table(name, schema, table)
553 try:
554 table.__init__(name, metadata, *other_args, _no_init=False, **kw) # type: ignore[misc] # noqa: E501
555 table.dispatch.after_parent_attach(table, metadata)
556 return table
557 except Exception:
558 with util.safe_reraise():
559 metadata._remove_table(name, schema)
561 @overload
562 def __init__(
563 self: Table[_TC_co],
564 name: str,
565 metadata: MetaData,
566 typed_columns_cls: type[_TC_co],
567 /,
568 *args: SchemaItem,
569 schema: str | Literal[SchemaConst.BLANK_SCHEMA] | None = None,
570 quote: bool | None = None,
571 quote_schema: bool | None = None,
572 keep_existing: bool = False,
573 extend_existing: bool = False,
574 implicit_returning: bool = True,
575 comment: str | None = None,
576 info: dict[Any, Any] | None = None,
577 listeners: (
578 _typing_Sequence[tuple[str, Callable[..., Any]]] | None
579 ) = None,
580 prefixes: _typing_Sequence[str] | None = None,
581 **kw: Any,
582 ) -> None: ...
584 @overload
585 def __init__(
586 self: Table[ReadOnlyColumnCollection[str, Column[Any]]],
587 name: str,
588 metadata: MetaData,
589 /,
590 *args: SchemaItem,
591 schema: str | Literal[SchemaConst.BLANK_SCHEMA] | None = None,
592 quote: bool | None = None,
593 quote_schema: Optional[bool] = None,
594 autoload_with: Optional[Union[Engine, Connection]] = None,
595 autoload_replace: bool = True,
596 keep_existing: bool = False,
597 extend_existing: bool = False,
598 resolve_fks: bool = True,
599 include_columns: Optional[Collection[str]] = None,
600 implicit_returning: bool = True,
601 comment: str | None = None,
602 info: dict[Any, Any] | None = None,
603 listeners: (
604 _typing_Sequence[tuple[str, Callable[..., Any]]] | None
605 ) = None,
606 prefixes: _typing_Sequence[str] | None = None,
607 _creator_ddl: TableCreateDDL | None = None,
608 _dropper_ddl: TableDropDDL | None = None,
609 # used internally in the metadata.reflect() process
610 _extend_on: Optional[Set[Table]] = None,
611 # used by __new__ to bypass __init__
612 _no_init: bool = True,
613 # dialect-specific keyword args
614 **kw: Any,
615 ) -> None: ...
617 def __init__(
618 self,
619 name: str,
620 metadata: MetaData,
621 /,
622 *args: Any,
623 schema: str | Literal[SchemaConst.BLANK_SCHEMA] | None = None,
624 quote: bool | None = None,
625 quote_schema: Optional[bool] = None,
626 autoload_with: Optional[Union[Engine, Connection]] = None,
627 autoload_replace: bool = True,
628 keep_existing: bool = False,
629 extend_existing: bool = False,
630 resolve_fks: bool = True,
631 include_columns: Optional[Collection[str]] = None,
632 implicit_returning: bool = True,
633 comment: str | None = None,
634 info: dict[Any, Any] | None = None,
635 listeners: (
636 _typing_Sequence[tuple[str, Callable[..., Any]]] | None
637 ) = None,
638 prefixes: _typing_Sequence[str] | None = None,
639 _creator_ddl: TableCreateDDL | None = None,
640 _dropper_ddl: TableDropDDL | None = None,
641 # used internally in the metadata.reflect() process
642 _extend_on: Optional[Set[Table]] = None,
643 # used by __new__ to bypass __init__
644 _no_init: bool = True,
645 # dialect-specific keyword args
646 **kw: Any,
647 ) -> None:
648 r"""Constructor for :class:`_schema.Table`.
651 :param name: The name of this table as represented in the database.
653 The table name, along with the value of the ``schema`` parameter,
654 forms a key which uniquely identifies this :class:`_schema.Table`
655 within
656 the owning :class:`_schema.MetaData` collection.
657 Additional calls to :class:`_schema.Table` with the same name,
658 metadata,
659 and schema name will return the same :class:`_schema.Table` object.
661 Names which contain no upper case characters
662 will be treated as case insensitive names, and will not be quoted
663 unless they are a reserved word or contain special characters.
664 A name with any number of upper case characters is considered
665 to be case sensitive, and will be sent as quoted.
667 To enable unconditional quoting for the table name, specify the flag
668 ``quote=True`` to the constructor, or use the :class:`.quoted_name`
669 construct to specify the name.
671 :param metadata: a :class:`_schema.MetaData`
672 object which will contain this
673 table. The metadata is used as a point of association of this table
674 with other tables which are referenced via foreign key. It also
675 may be used to associate this table with a particular
676 :class:`.Connection` or :class:`.Engine`.
678 :param table_columns_cls: a subclass of :class:`_schema.TypedColumns`
679 that defines the columns that will be "typed" when accessing
680 them from the :attr:`_schema.Table.c` attribute.
682 .. versionadded:: 2.1.0b2
684 :param \*args: Additional positional arguments are used primarily
685 to add the list of :class:`_schema.Column`
686 objects contained within this
687 table. Similar to the style of a CREATE TABLE statement, other
688 :class:`.SchemaItem` constructs may be added here, including
689 :class:`.PrimaryKeyConstraint`, and
690 :class:`_schema.ForeignKeyConstraint`.
691 Additional columns may be provided also when using a
692 :paramref:`_schema.Table.table_columns_cls` class; they will
693 be appended to the "typed" columns and will appear as untyped
694 when accessing them via the :attr:`_schema.Table.c` collection.
696 :param autoload_replace: Defaults to ``True``; when using
697 :paramref:`_schema.Table.autoload_with`
698 in conjunction with :paramref:`_schema.Table.extend_existing`,
699 indicates
700 that :class:`_schema.Column` objects present in the already-existing
701 :class:`_schema.Table`
702 object should be replaced with columns of the same
703 name retrieved from the autoload process. When ``False``, columns
704 already present under existing names will be omitted from the
705 reflection process.
707 Note that this setting does not impact :class:`_schema.Column` objects
708 specified programmatically within the call to :class:`_schema.Table`
709 that
710 also is autoloading; those :class:`_schema.Column` objects will always
711 replace existing columns of the same name when
712 :paramref:`_schema.Table.extend_existing` is ``True``.
714 .. seealso::
716 :paramref:`_schema.Table.autoload_with`
718 :paramref:`_schema.Table.extend_existing`
720 :param autoload_with: An :class:`_engine.Engine` or
721 :class:`_engine.Connection` object,
722 or a :class:`_reflection.Inspector` object as returned by
723 :func:`_sa.inspect`
724 against one, with which this :class:`_schema.Table`
725 object will be reflected.
726 When set to a non-None value, the autoload process will take place
727 for this table against the given engine or connection.
729 .. seealso::
731 :ref:`metadata_reflection_toplevel`
733 :meth:`_events.DDLEvents.column_reflect`
735 :ref:`metadata_reflection_dbagnostic_types`
737 :param extend_existing: When ``True``, indicates that if this
738 :class:`_schema.Table` is already present in the given
739 :class:`_schema.MetaData`,
740 apply further arguments within the constructor to the existing
741 :class:`_schema.Table`.
743 If :paramref:`_schema.Table.extend_existing` or
744 :paramref:`_schema.Table.keep_existing` are not set,
745 and the given name
746 of the new :class:`_schema.Table` refers to a :class:`_schema.Table`
747 that is
748 already present in the target :class:`_schema.MetaData` collection,
749 and
750 this :class:`_schema.Table`
751 specifies additional columns or other constructs
752 or flags that modify the table's state, an
753 error is raised. The purpose of these two mutually-exclusive flags
754 is to specify what action should be taken when a
755 :class:`_schema.Table`
756 is specified that matches an existing :class:`_schema.Table`,
757 yet specifies
758 additional constructs.
760 :paramref:`_schema.Table.extend_existing`
761 will also work in conjunction
762 with :paramref:`_schema.Table.autoload_with` to run a new reflection
763 operation against the database, even if a :class:`_schema.Table`
764 of the same name is already present in the target
765 :class:`_schema.MetaData`; newly reflected :class:`_schema.Column`
766 objects
767 and other options will be added into the state of the
768 :class:`_schema.Table`, potentially overwriting existing columns
769 and options of the same name.
771 As is always the case with :paramref:`_schema.Table.autoload_with`,
772 :class:`_schema.Column` objects can be specified in the same
773 :class:`_schema.Table`
774 constructor, which will take precedence. Below, the existing
775 table ``mytable`` will be augmented with :class:`_schema.Column`
776 objects
777 both reflected from the database, as well as the given
778 :class:`_schema.Column`
779 named "y"::
781 Table(
782 "mytable",
783 metadata,
784 Column("y", Integer),
785 extend_existing=True,
786 autoload_with=engine,
787 )
789 .. seealso::
791 :paramref:`_schema.Table.autoload_with`
793 :paramref:`_schema.Table.autoload_replace`
795 :paramref:`_schema.Table.keep_existing`
798 :param implicit_returning: True by default - indicates that
799 RETURNING can be used, typically by the ORM, in order to fetch
800 server-generated values such as primary key values and
801 server side defaults, on those backends which support RETURNING.
803 In modern SQLAlchemy there is generally no reason to alter this
804 setting, except for some backend specific cases
805 (see :ref:`mssql_triggers` in the SQL Server dialect documentation
806 for one such example).
808 :param include_columns: A list of strings indicating a subset of
809 columns to be loaded via the ``autoload`` operation; table columns who
810 aren't present in this list will not be represented on the resulting
811 ``Table`` object. Defaults to ``None`` which indicates all columns
812 should be reflected.
814 :param resolve_fks: Whether or not to reflect :class:`_schema.Table`
815 objects
816 related to this one via :class:`_schema.ForeignKey` objects, when
817 :paramref:`_schema.Table.autoload_with` is
818 specified. Defaults to True. Set to False to disable reflection of
819 related tables as :class:`_schema.ForeignKey`
820 objects are encountered; may be
821 used either to save on SQL calls or to avoid issues with related tables
822 that can't be accessed. Note that if a related table is already present
823 in the :class:`_schema.MetaData` collection, or becomes present later,
824 a
825 :class:`_schema.ForeignKey` object associated with this
826 :class:`_schema.Table` will
827 resolve to that table normally.
829 .. seealso::
831 :paramref:`.MetaData.reflect.resolve_fks`
834 :param info: Optional data dictionary which will be populated into the
835 :attr:`.SchemaItem.info` attribute of this object.
837 :param keep_existing: When ``True``, indicates that if this Table
838 is already present in the given :class:`_schema.MetaData`, ignore
839 further arguments within the constructor to the existing
840 :class:`_schema.Table`, and return the :class:`_schema.Table`
841 object as
842 originally created. This is to allow a function that wishes
843 to define a new :class:`_schema.Table` on first call, but on
844 subsequent calls will return the same :class:`_schema.Table`,
845 without any of the declarations (particularly constraints)
846 being applied a second time.
848 If :paramref:`_schema.Table.extend_existing` or
849 :paramref:`_schema.Table.keep_existing` are not set,
850 and the given name
851 of the new :class:`_schema.Table` refers to a :class:`_schema.Table`
852 that is
853 already present in the target :class:`_schema.MetaData` collection,
854 and
855 this :class:`_schema.Table`
856 specifies additional columns or other constructs
857 or flags that modify the table's state, an
858 error is raised. The purpose of these two mutually-exclusive flags
859 is to specify what action should be taken when a
860 :class:`_schema.Table`
861 is specified that matches an existing :class:`_schema.Table`,
862 yet specifies
863 additional constructs.
865 .. seealso::
867 :paramref:`_schema.Table.extend_existing`
869 :param listeners: A list of tuples of the form ``(<eventname>, <fn>)``
870 which will be passed to :func:`.event.listen` upon construction.
871 This alternate hook to :func:`.event.listen` allows the establishment
872 of a listener function specific to this :class:`_schema.Table` before
873 the "autoload" process begins. Historically this has been intended
874 for use with the :meth:`.DDLEvents.column_reflect` event, however
875 note that this event hook may now be associated with the
876 :class:`_schema.MetaData` object directly::
878 def listen_for_reflect(table, column_info):
879 "handle the column reflection event"
880 # ...
883 t = Table(
884 "sometable",
885 autoload_with=engine,
886 listeners=[("column_reflect", listen_for_reflect)],
887 )
889 .. seealso::
891 :meth:`_events.DDLEvents.column_reflect`
893 :param must_exist: When ``True``, indicates that this Table must already
894 be present in the given :class:`_schema.MetaData` collection, else
895 an exception is raised.
897 :param prefixes:
898 A list of strings to insert after CREATE in the CREATE TABLE
899 statement. They will be separated by spaces.
901 :param quote: Force quoting of this table's name on or off, corresponding
902 to ``True`` or ``False``. When left at its default of ``None``,
903 the column identifier will be quoted according to whether the name is
904 case sensitive (identifiers with at least one upper case character are
905 treated as case sensitive), or if it's a reserved word. This flag
906 is only needed to force quoting of a reserved word which is not known
907 by the SQLAlchemy dialect.
909 .. note:: setting this flag to ``False`` will not provide
910 case-insensitive behavior for table reflection; table reflection
911 will always search for a mixed-case name in a case sensitive
912 fashion. Case insensitive names are specified in SQLAlchemy only
913 by stating the name with all lower case characters.
915 :param quote_schema: same as 'quote' but applies to the schema identifier.
917 :param schema: The schema name for this table, which is required if
918 the table resides in a schema other than the default selected schema
919 for the engine's database connection. Defaults to ``None``.
921 If the owning :class:`_schema.MetaData` of this :class:`_schema.Table`
922 specifies its
923 own :paramref:`_schema.MetaData.schema` parameter,
924 then that schema name will
925 be applied to this :class:`_schema.Table`
926 if the schema parameter here is set
927 to ``None``. To set a blank schema name on a :class:`_schema.Table`
928 that
929 would otherwise use the schema set on the owning
930 :class:`_schema.MetaData`,
931 specify the special symbol :attr:`.BLANK_SCHEMA`.
933 The quoting rules for the schema name are the same as those for the
934 ``name`` parameter, in that quoting is applied for reserved words or
935 case-sensitive names; to enable unconditional quoting for the schema
936 name, specify the flag ``quote_schema=True`` to the constructor, or use
937 the :class:`.quoted_name` construct to specify the name.
939 :param comment: Optional string that will render an SQL comment on table
940 creation.
942 :param \**kw: Additional keyword arguments not mentioned above are
943 dialect specific, and passed in the form ``<dialectname>_<argname>``.
944 See the documentation regarding an individual dialect at
945 :ref:`dialect_toplevel` for detail on documented arguments.
947 """ # noqa: E501
948 if _no_init:
949 # don't run __init__ from __new__ by default;
950 # __new__ has a specific place that __init__ is called
951 return
952 if args:
953 # this is the call done by `__new__` that should have resolved
954 # TypedColumns to the individual columns
955 assert not (
956 isinstance(args[0], type) and issubclass(args[0], TypedColumns)
957 )
959 super().__init__(quoted_name(name, quote))
960 self.metadata = metadata
962 if schema is None:
963 self.schema = metadata.schema
964 elif schema is BLANK_SCHEMA:
965 self.schema = None
966 else:
967 quote_schema = quote_schema
968 assert isinstance(schema, str)
969 self.schema = quoted_name(schema, quote_schema)
971 self._sentinel_column = None
972 self._creator_ddl = _creator_ddl
973 self._dropper_ddl = _dropper_ddl
975 self.indexes = set()
976 self.constraints = set()
977 PrimaryKeyConstraint(
978 _implicit_generated=True
979 )._set_parent_with_dispatch(self)
980 self.foreign_keys = set() # type: ignore
981 self._extra_dependencies: Set[Table] = set()
982 if self.schema is not None:
983 self.fullname = "%s.%s" % (self.schema, self.name)
984 else:
985 self.fullname = self.name
987 self.implicit_returning = implicit_returning
988 _reflect_info = kw.pop("_reflect_info", None)
990 self.comment = comment
992 if info is not None:
993 self.info = info
995 if listeners is not None:
996 for evt, fn in listeners:
997 event.listen(self, evt, fn)
999 self._prefixes = prefixes if prefixes else []
1001 self._extra_kwargs(**kw)
1003 # load column definitions from the database if 'autoload' is defined
1004 # we do it after the table is in the singleton dictionary to support
1005 # circular foreign keys
1006 if autoload_with is not None:
1007 self._autoload(
1008 metadata,
1009 autoload_with,
1010 include_columns,
1011 _extend_on=_extend_on,
1012 _reflect_info=_reflect_info,
1013 resolve_fks=resolve_fks,
1014 )
1016 # initialize all the column, etc. objects. done after reflection to
1017 # allow user-overrides
1019 self._init_items(
1020 *args,
1021 allow_replacements=extend_existing
1022 or keep_existing
1023 or autoload_with,
1024 all_names={},
1025 )
1027 def set_creator_ddl(self, ddl: TableCreateDDL) -> None:
1028 """Set the table create DDL for this :class:`.Table`.
1030 This allows the CREATE TABLE statement to be controlled or replaced
1031 entirely when :meth:`.Table.create` or :meth:`.MetaData.create_all` is
1032 used.
1034 E.g.::
1036 from sqlalchemy.schema import CreateTable
1038 table.set_creator_ddl(CreateTable(table, if_not_exists=True))
1040 .. versionadded:: 2.1
1042 .. seealso::
1044 :meth:`.Table.set_dropper_ddl`
1046 """
1047 self._creator_ddl = ddl
1049 def set_dropper_ddl(self, ddl: TableDropDDL) -> None:
1050 """Set the table drop DDL for this :class:`.Table`.
1052 This allows the DROP TABLE statement to be controlled or replaced
1053 entirely when :meth:`.Table.drop` or :meth:`.MetaData.drop_all` is
1054 used.
1056 E.g.::
1058 from sqlalchemy.schema import DropTable
1060 table.set_dropper_ddl(DropTable(table, if_exists=True))
1062 .. versionadded:: 2.1
1064 .. seealso::
1066 :meth:`.Table.set_creator_ddl`
1068 """
1069 self._dropper_ddl = ddl
1071 @property
1072 def is_view(self) -> bool:
1073 """True if this table, when DDL for CREATE is emitted, will emit
1074 CREATE VIEW rather than CREATE TABLE.
1076 .. versionadded:: 2.1
1078 """
1079 return isinstance(self._creator_ddl, ddl.CreateView)
1081 def _autoload(
1082 self,
1083 metadata: MetaData,
1084 autoload_with: Union[Engine, Connection],
1085 include_columns: Optional[Collection[str]],
1086 exclude_columns: Collection[str] = (),
1087 resolve_fks: bool = True,
1088 _extend_on: Optional[Set[Table]] = None,
1089 _reflect_info: _ReflectionInfo | None = None,
1090 ) -> None:
1091 insp = inspection.inspect(autoload_with)
1092 with insp._inspection_context() as conn_insp:
1093 conn_insp.reflect_table(
1094 self,
1095 include_columns,
1096 exclude_columns,
1097 resolve_fks,
1098 _extend_on=_extend_on,
1099 _reflect_info=_reflect_info,
1100 )
1102 @property
1103 def _sorted_constraints(self) -> List[Constraint]:
1104 """Return the set of constraints as a list, sorted by creation
1105 order.
1107 """
1109 return sorted(self.constraints, key=lambda c: c._creation_order)
1111 @property
1112 def foreign_key_constraints(self) -> Set[ForeignKeyConstraint]:
1113 """:class:`_schema.ForeignKeyConstraint` objects referred to by this
1114 :class:`_schema.Table`.
1116 This list is produced from the collection of
1117 :class:`_schema.ForeignKey`
1118 objects currently associated.
1121 .. seealso::
1123 :attr:`_schema.Table.constraints`
1125 :attr:`_schema.Table.foreign_keys`
1127 :attr:`_schema.Table.indexes`
1129 """
1130 return {
1131 fkc.constraint
1132 for fkc in self.foreign_keys
1133 if fkc.constraint is not None
1134 }
1136 def _init_existing(self, *args: Any, **kwargs: Any) -> None:
1137 autoload_with = kwargs.pop("autoload_with", None)
1138 autoload = kwargs.pop("autoload", autoload_with is not None)
1139 autoload_replace = kwargs.pop("autoload_replace", True)
1140 schema = kwargs.pop("schema", None)
1141 _extend_on = kwargs.pop("_extend_on", None)
1142 _reflect_info = kwargs.pop("_reflect_info", None)
1144 # these arguments are only used with _init()
1145 extend_existing = kwargs.pop("extend_existing", False)
1146 keep_existing = kwargs.pop("keep_existing", False)
1148 assert extend_existing
1149 assert not keep_existing
1151 if schema and schema != self.schema:
1152 raise exc.ArgumentError(
1153 f"Can't change schema of existing table "
1154 f"from '{self.schema}' to '{schema}'",
1155 )
1157 include_columns = kwargs.pop("include_columns", None)
1158 if include_columns is not None:
1159 for c in self.c:
1160 if c.name not in include_columns:
1161 self._columns.remove(c)
1163 resolve_fks = kwargs.pop("resolve_fks", True)
1165 for key in ("quote", "quote_schema"):
1166 if key in kwargs:
1167 raise exc.ArgumentError(
1168 "Can't redefine 'quote' or 'quote_schema' arguments"
1169 )
1171 # update `self` with these kwargs, if provided
1172 self.comment = kwargs.pop("comment", self.comment)
1173 self.implicit_returning = kwargs.pop(
1174 "implicit_returning", self.implicit_returning
1175 )
1176 self.info = kwargs.pop("info", self.info)
1178 exclude_columns: _typing_Sequence[str]
1180 if autoload:
1181 if not autoload_replace:
1182 # don't replace columns already present.
1183 # we'd like to do this for constraints also however we don't
1184 # have simple de-duping for unnamed constraints.
1185 exclude_columns = [c.name for c in self.c]
1186 else:
1187 exclude_columns = ()
1188 self._autoload(
1189 self.metadata,
1190 autoload_with,
1191 include_columns,
1192 exclude_columns,
1193 resolve_fks,
1194 _extend_on=_extend_on,
1195 _reflect_info=_reflect_info,
1196 )
1198 all_names = {c.name: c for c in self.c}
1199 self._extra_kwargs(**kwargs)
1200 self._init_items(*args, allow_replacements=True, all_names=all_names)
1202 def _extra_kwargs(self, **kwargs: Any) -> None:
1203 self._validate_dialect_kwargs(kwargs)
1205 def _init_collections(self) -> None:
1206 pass
1208 def _reset_exported(self) -> None:
1209 pass
1211 @util.ro_non_memoized_property
1212 def _autoincrement_column(self) -> Optional[Column[int]]:
1213 return self.primary_key._autoincrement_column
1215 @util.ro_memoized_property
1216 def _sentinel_column_characteristics(
1217 self,
1218 ) -> _SentinelColumnCharacterization:
1219 """determine a candidate column (or columns, in case of a client
1220 generated composite primary key) which can be used as an
1221 "insert sentinel" for an INSERT statement.
1223 The returned structure, :class:`_SentinelColumnCharacterization`,
1224 includes all the details needed by :class:`.Dialect` and
1225 :class:`.SQLCompiler` to determine if these column(s) can be used
1226 as an INSERT..RETURNING sentinel for a particular database
1227 dialect.
1229 .. versionadded:: 2.0.10
1231 """
1233 sentinel_is_explicit = False
1234 sentinel_is_autoinc = False
1235 the_sentinel: Optional[_typing_Sequence[Column[Any]]] = None
1237 # see if a column was explicitly marked "insert_sentinel=True".
1238 explicit_sentinel_col = self._sentinel_column
1240 if explicit_sentinel_col is not None:
1241 the_sentinel = (explicit_sentinel_col,)
1242 sentinel_is_explicit = True
1244 autoinc_col = self._autoincrement_column
1245 if sentinel_is_explicit and explicit_sentinel_col is autoinc_col:
1246 assert autoinc_col is not None
1247 sentinel_is_autoinc = True
1248 elif explicit_sentinel_col is None and autoinc_col is not None:
1249 the_sentinel = (autoinc_col,)
1250 sentinel_is_autoinc = True
1252 default_characterization = _SentinelDefaultCharacterization.UNKNOWN
1254 if the_sentinel:
1255 the_sentinel_zero = the_sentinel[0]
1256 if the_sentinel_zero.identity:
1257 if the_sentinel_zero.identity._increment_is_negative:
1258 if sentinel_is_explicit:
1259 raise exc.InvalidRequestError(
1260 "Can't use IDENTITY default with negative "
1261 "increment as an explicit sentinel column"
1262 )
1263 else:
1264 if sentinel_is_autoinc:
1265 autoinc_col = None
1266 sentinel_is_autoinc = False
1267 the_sentinel = None
1268 else:
1269 default_characterization = (
1270 _SentinelDefaultCharacterization.IDENTITY
1271 )
1272 elif (
1273 the_sentinel_zero.default is None
1274 and the_sentinel_zero.server_default is None
1275 ):
1276 if the_sentinel_zero.nullable:
1277 raise exc.InvalidRequestError(
1278 f"Column {the_sentinel_zero} has been marked as a "
1279 "sentinel "
1280 "column with no default generation function; it "
1281 "at least needs to be marked nullable=False assuming "
1282 "user-populated sentinel values will be used."
1283 )
1284 default_characterization = (
1285 _SentinelDefaultCharacterization.NONE
1286 )
1287 elif the_sentinel_zero.default is not None:
1288 if the_sentinel_zero.default.is_sentinel:
1289 default_characterization = (
1290 _SentinelDefaultCharacterization.SENTINEL_DEFAULT
1291 )
1292 elif the_sentinel_zero.default._is_monotonic_fn:
1293 default_characterization = (
1294 _SentinelDefaultCharacterization.MONOTONIC_FUNCTION
1295 )
1296 elif default_is_sequence(the_sentinel_zero.default):
1297 if the_sentinel_zero.default._increment_is_negative:
1298 if sentinel_is_explicit:
1299 raise exc.InvalidRequestError(
1300 "Can't use SEQUENCE default with negative "
1301 "increment as an explicit sentinel column"
1302 )
1303 else:
1304 if sentinel_is_autoinc:
1305 autoinc_col = None
1306 sentinel_is_autoinc = False
1307 the_sentinel = None
1309 default_characterization = (
1310 _SentinelDefaultCharacterization.SEQUENCE
1311 )
1312 elif the_sentinel_zero.default.is_callable:
1313 default_characterization = (
1314 _SentinelDefaultCharacterization.CLIENTSIDE
1315 )
1316 elif the_sentinel_zero.server_default is not None:
1317 if sentinel_is_explicit:
1318 if not the_sentinel_zero.server_default._is_monotonic_fn:
1319 raise exc.InvalidRequestError(
1320 f"Column {the_sentinel[0]} can't be a sentinel "
1321 "column "
1322 "because it uses an explicit server side default "
1323 "that's not the Identity() default."
1324 )
1325 else:
1326 default_characterization = (
1327 _SentinelDefaultCharacterization.MONOTONIC_FUNCTION
1328 )
1329 else:
1330 default_characterization = (
1331 _SentinelDefaultCharacterization.SERVERSIDE
1332 )
1334 if the_sentinel is None and self.primary_key:
1335 assert autoinc_col is None
1337 # determine for non-autoincrement pk if all elements are
1338 # client side
1339 for _pkc in self.primary_key:
1340 if (
1341 _pkc.server_default is not None
1342 and not _pkc.server_default._is_monotonic_fn
1343 ):
1344 break
1346 if (
1347 _pkc.default
1348 and not _pkc.default.is_callable
1349 and not _pkc.default._is_monotonic_fn
1350 ):
1351 break
1352 else:
1353 the_sentinel = tuple(self.primary_key)
1354 default_characterization = (
1355 _SentinelDefaultCharacterization.CLIENTSIDE
1356 )
1358 return _SentinelColumnCharacterization(
1359 the_sentinel,
1360 sentinel_is_explicit,
1361 sentinel_is_autoinc,
1362 default_characterization,
1363 )
1365 @property
1366 def autoincrement_column(self) -> Optional[Column[int]]:
1367 """Returns the :class:`.Column` object which currently represents
1368 the "auto increment" column, if any, else returns None.
1370 This is based on the rules for :class:`.Column` as defined by the
1371 :paramref:`.Column.autoincrement` parameter, which generally means the
1372 column within a single integer column primary key constraint that is
1373 not constrained by a foreign key. If the table does not have such
1374 a primary key constraint, then there's no "autoincrement" column.
1375 A :class:`.Table` may have only one column defined as the
1376 "autoincrement" column.
1378 .. versionadded:: 2.0.4
1380 .. seealso::
1382 :paramref:`.Column.autoincrement`
1384 """
1385 return self._autoincrement_column
1387 @property
1388 def key(self) -> str:
1389 """Return the 'key' for this :class:`_schema.Table`.
1391 This value is used as the dictionary key within the
1392 :attr:`_schema.MetaData.tables` collection. It is typically the same
1393 as that of :attr:`_schema.Table.name` for a table with no
1394 :attr:`_schema.Table.schema`
1395 set; otherwise it is typically of the form
1396 ``schemaname.tablename``.
1398 """
1399 return _get_table_key(self.name, self.schema)
1401 def __repr__(self) -> str:
1402 return "Table(%s)" % ", ".join(
1403 [repr(self.name)]
1404 + [repr(self.metadata)]
1405 + [repr(x) for x in self.columns]
1406 + ["%s=%s" % (k, repr(getattr(self, k))) for k in ["schema"]]
1407 )
1409 def __str__(self) -> str:
1410 return _get_table_key(self.description, self.schema)
1412 def add_is_dependent_on(self, table: Table) -> None:
1413 """Add a 'dependency' for this Table.
1415 This is another Table object which must be created
1416 first before this one can, or dropped after this one.
1418 Usually, dependencies between tables are determined via
1419 ForeignKey objects. However, for other situations that
1420 create dependencies outside of foreign keys (rules, inheriting),
1421 this method can manually establish such a link.
1423 """
1424 self._extra_dependencies.add(table)
1426 def _insert_col_impl(
1427 self,
1428 column: ColumnClause[Any],
1429 *,
1430 index: Optional[int] = None,
1431 replace_existing: bool = False,
1432 ) -> None:
1433 try:
1434 column._set_parent_with_dispatch(
1435 self,
1436 allow_replacements=replace_existing,
1437 all_names={c.name: c for c in self.c},
1438 index=index,
1439 )
1440 except exc.DuplicateColumnError as de:
1441 raise exc.DuplicateColumnError(
1442 f"{de.args[0]} Specify replace_existing=True to "
1443 "Table.append_column() or Table.insert_column() to replace an "
1444 "existing column."
1445 ) from de
1447 def insert_column(
1448 self,
1449 column: ColumnClause[Any],
1450 index: int,
1451 *,
1452 replace_existing: bool = False,
1453 ) -> None:
1454 """Insert a :class:`_schema.Column` to this :class:`_schema.Table` at
1455 a specific position.
1457 Behavior is identical to :meth:`.Table.append_column` except that
1458 the index position can be controlled using the
1459 :paramref:`.Table.insert_column.index`
1460 parameter.
1462 :param replace_existing:
1463 see :paramref:`.Table.append_column.replace_existing`
1464 :param index: integer index to insert the new column.
1466 .. versionadded:: 2.1
1468 """
1469 self._insert_col_impl(
1470 column, index=index, replace_existing=replace_existing
1471 )
1473 def append_column(
1474 self, column: ColumnClause[Any], *, replace_existing: bool = False
1475 ) -> None:
1476 """Append a :class:`_schema.Column` to this :class:`_schema.Table`.
1478 The "key" of the newly added :class:`_schema.Column`, i.e. the
1479 value of its ``.key`` attribute, will then be available
1480 in the ``.c`` collection of this :class:`_schema.Table`, and the
1481 column definition will be included in any CREATE TABLE, SELECT,
1482 UPDATE, etc. statements generated from this :class:`_schema.Table`
1483 construct.
1485 Note that this does **not** change the definition of the table
1486 as it exists within any underlying database, assuming that
1487 table has already been created in the database. Relational
1488 databases support the addition of columns to existing tables
1489 using the SQL ALTER command, which would need to be
1490 emitted for an already-existing table that doesn't contain
1491 the newly added column.
1493 :param replace_existing: When ``True``, allows replacing existing
1494 columns. When ``False``, the default, an warning will be raised
1495 if a column with the same ``.key`` already exists. A future
1496 version of sqlalchemy will instead rise a warning.
1498 .. versionadded:: 1.4.0
1500 .. seealso::
1502 :meth:`.Table.insert_column`
1504 """
1505 self._insert_col_impl(column, replace_existing=replace_existing)
1507 def append_constraint(self, constraint: Union[Index, Constraint]) -> None:
1508 """Append a :class:`_schema.Constraint` to this
1509 :class:`_schema.Table`.
1511 This has the effect of the constraint being included in any
1512 future CREATE TABLE statement, assuming specific DDL creation
1513 events have not been associated with the given
1514 :class:`_schema.Constraint` object.
1516 Note that this does **not** produce the constraint within the
1517 relational database automatically, for a table that already exists
1518 in the database. To add a constraint to an
1519 existing relational database table, the SQL ALTER command must
1520 be used. SQLAlchemy also provides the
1521 :class:`.AddConstraint` construct which can produce this SQL when
1522 invoked as an executable clause.
1524 """
1526 constraint._set_parent_with_dispatch(self)
1528 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
1529 metadata = parent
1530 assert isinstance(metadata, MetaData)
1531 metadata._add_table(self.name, self.schema, self)
1532 self.metadata = metadata
1534 def create(
1535 self,
1536 bind: _CreateDropBind,
1537 checkfirst: Union[bool, CheckFirst] = CheckFirst.TYPES,
1538 ) -> None:
1539 """Issue a ``CREATE`` statement for this
1540 :class:`_schema.Table`, using the given
1541 :class:`.Connection` or :class:`.Engine`
1542 for connectivity.
1544 .. seealso::
1546 :meth:`_schema.MetaData.create_all`.
1548 """
1550 # the default is to only check for schema objects
1551 bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
1553 def drop(
1554 self,
1555 bind: _CreateDropBind,
1556 checkfirst: Union[bool, CheckFirst] = CheckFirst.NONE,
1557 ) -> None:
1558 """Issue a ``DROP`` statement for this
1559 :class:`_schema.Table`, using the given
1560 :class:`.Connection` or :class:`.Engine` for connectivity.
1562 .. seealso::
1564 :meth:`_schema.MetaData.drop_all`.
1566 """
1567 bind._run_ddl_visitor(ddl.SchemaDropper, self, checkfirst=checkfirst)
1569 @util.deprecated(
1570 "1.4",
1571 ":meth:`_schema.Table.tometadata` is renamed to "
1572 ":meth:`_schema.Table.to_metadata`",
1573 )
1574 def tometadata(
1575 self,
1576 metadata: MetaData,
1577 schema: Union[str, Literal[SchemaConst.RETAIN_SCHEMA]] = RETAIN_SCHEMA,
1578 referred_schema_fn: Optional[
1579 Callable[
1580 [Table, Optional[str], ForeignKeyConstraint, Optional[str]],
1581 Optional[str],
1582 ]
1583 ] = None,
1584 name: Optional[str] = None,
1585 ) -> Table[_ColCC_co]:
1586 """Return a copy of this :class:`_schema.Table`
1587 associated with a different
1588 :class:`_schema.MetaData`.
1590 See :meth:`_schema.Table.to_metadata` for a full description.
1592 """
1593 return self.to_metadata(
1594 metadata,
1595 schema=schema,
1596 referred_schema_fn=referred_schema_fn,
1597 name=name,
1598 )
1600 def to_metadata(
1601 self,
1602 metadata: MetaData,
1603 schema: Union[str, Literal[SchemaConst.RETAIN_SCHEMA]] = RETAIN_SCHEMA,
1604 referred_schema_fn: Optional[
1605 Callable[
1606 [Table, Optional[str], ForeignKeyConstraint, Optional[str]],
1607 Optional[str],
1608 ]
1609 ] = None,
1610 name: Optional[str] = None,
1611 ) -> Table[_ColCC_co]:
1612 """Return a copy of this :class:`_schema.Table` associated with a
1613 different :class:`_schema.MetaData`.
1615 E.g.::
1617 m1 = MetaData()
1619 user = Table("user", m1, Column("id", Integer, primary_key=True))
1621 m2 = MetaData()
1622 user_copy = user.to_metadata(m2)
1624 .. versionchanged:: 1.4 The :meth:`_schema.Table.to_metadata` function
1625 was renamed from :meth:`_schema.Table.tometadata`.
1628 :param metadata: Target :class:`_schema.MetaData` object,
1629 into which the
1630 new :class:`_schema.Table` object will be created.
1632 :param schema: optional string name indicating the target schema.
1633 Defaults to the special symbol :attr:`.RETAIN_SCHEMA` which indicates
1634 that no change to the schema name should be made in the new
1635 :class:`_schema.Table`. If set to a string name, the new
1636 :class:`_schema.Table`
1637 will have this new name as the ``.schema``. If set to ``None``, the
1638 schema will be set to that of the schema set on the target
1639 :class:`_schema.MetaData`, which is typically ``None`` as well,
1640 unless
1641 set explicitly::
1643 m2 = MetaData(schema="newschema")
1645 # user_copy_one will have "newschema" as the schema name
1646 user_copy_one = user.to_metadata(m2, schema=None)
1648 m3 = MetaData() # schema defaults to None
1650 # user_copy_two will have None as the schema name
1651 user_copy_two = user.to_metadata(m3, schema=None)
1653 :param referred_schema_fn: optional callable which can be supplied
1654 in order to provide for the schema name that should be assigned
1655 to the referenced table of a :class:`_schema.ForeignKeyConstraint`.
1656 The callable accepts this parent :class:`_schema.Table`, the
1657 target schema that we are changing to, the
1658 :class:`_schema.ForeignKeyConstraint` object, and the existing
1659 "target schema" of that constraint. The function should return the
1660 string schema name that should be applied. To reset the schema
1661 to "none", return the symbol :data:`.BLANK_SCHEMA`. To effect no
1662 change, return ``None`` or :data:`.RETAIN_SCHEMA`.
1664 .. versionchanged:: 1.4.33 The ``referred_schema_fn`` function
1665 may return the :data:`.BLANK_SCHEMA` or :data:`.RETAIN_SCHEMA`
1666 symbols.
1668 E.g.::
1670 def referred_schema_fn(table, to_schema, constraint, referred_schema):
1671 if referred_schema == "base_tables":
1672 return referred_schema
1673 else:
1674 return to_schema
1677 new_table = table.to_metadata(
1678 m2, schema="alt_schema", referred_schema_fn=referred_schema_fn
1679 )
1681 :param name: optional string name indicating the target table name.
1682 If not specified or None, the table name is retained. This allows
1683 a :class:`_schema.Table` to be copied to the same
1684 :class:`_schema.MetaData` target
1685 with a new name.
1687 """ # noqa: E501
1688 if name is None:
1689 name = self.name
1691 actual_schema: Optional[str]
1693 if schema is RETAIN_SCHEMA:
1694 actual_schema = self.schema
1695 elif schema is None:
1696 actual_schema = metadata.schema
1697 else:
1698 actual_schema = schema
1699 key = _get_table_key(name, actual_schema)
1700 if key in metadata.tables:
1701 util.warn(
1702 f"Table '{self.description}' already exists within the given "
1703 "MetaData - not copying."
1704 )
1705 return metadata.tables[key]
1707 args = []
1708 for col in self.columns:
1709 args.append(col._copy(schema=actual_schema, _to_metadata=metadata))
1711 table: Table[_ColCC_co] = Table( # type: ignore[assignment]
1712 name,
1713 metadata,
1714 schema=actual_schema,
1715 comment=self.comment,
1716 *args,
1717 **self.kwargs,
1718 )
1720 if self._creator_ddl is not None:
1721 table._creator_ddl = self._creator_ddl.to_metadata(metadata, table)
1722 if self._dropper_ddl is not None:
1723 table._dropper_ddl = self._dropper_ddl.to_metadata(metadata, table)
1725 for const in self.constraints:
1726 if isinstance(const, ForeignKeyConstraint):
1727 referred_schema = const._referred_schema
1728 if referred_schema_fn:
1729 fk_constraint_schema = referred_schema_fn(
1730 self, actual_schema, const, referred_schema
1731 )
1732 else:
1733 fk_constraint_schema = (
1734 actual_schema
1735 if referred_schema == self.schema
1736 else None
1737 )
1738 table.append_constraint(
1739 const._copy(
1740 schema=fk_constraint_schema, target_table=table
1741 )
1742 )
1743 elif not const._type_bound:
1744 # skip unique constraints that would be generated
1745 # by the 'unique' flag on Column
1746 if const._column_flag:
1747 continue
1749 table.append_constraint(
1750 const._copy(schema=actual_schema, target_table=table)
1751 )
1752 for index in self.indexes:
1753 # skip indexes that would be generated
1754 # by the 'index' flag on Column
1755 if index._column_flag:
1756 continue
1757 Index(
1758 index.name,
1759 unique=index.unique,
1760 *[
1761 _copy_expression(expr, self, table)
1762 for expr in index._table_bound_expressions
1763 ],
1764 _table=table,
1765 **index.kwargs,
1766 )
1767 return self._schema_item_copy(table)
1770class Column(DialectKWArgs, SchemaItem, ColumnClause[_T], Named[_T]):
1771 """Represents a column in a database table."""
1773 __visit_name__ = "column"
1775 inherit_cache = True
1776 key: str
1778 server_default: Optional[FetchedValue]
1780 def __init__(
1781 self,
1782 __name_pos: Optional[
1783 Union[str, _TypeEngineArgument[_T], SchemaEventTarget]
1784 ] = None,
1785 __type_pos: Optional[
1786 Union[_TypeEngineArgument[_T], SchemaEventTarget]
1787 ] = None,
1788 /,
1789 *args: SchemaEventTarget,
1790 name: Optional[str] = None,
1791 type_: Optional[_TypeEngineArgument[_T]] = None,
1792 autoincrement: _AutoIncrementType = "auto",
1793 default: Optional[Any] = _NoArg.NO_ARG,
1794 insert_default: Optional[Any] = _NoArg.NO_ARG,
1795 doc: Optional[str] = None,
1796 key: Optional[str] = None,
1797 index: Optional[bool] = None,
1798 unique: Optional[bool] = None,
1799 info: Optional[_InfoType] = None,
1800 nullable: Optional[
1801 Union[bool, Literal[SchemaConst.NULL_UNSPECIFIED]]
1802 ] = SchemaConst.NULL_UNSPECIFIED,
1803 onupdate: Optional[Any] = None,
1804 primary_key: bool = False,
1805 server_default: Optional[_ServerDefaultArgument] = None,
1806 server_onupdate: Optional[_ServerOnUpdateArgument] = None,
1807 quote: Optional[bool] = None,
1808 system: bool = False,
1809 comment: Optional[str] = None,
1810 insert_sentinel: bool = False,
1811 _omit_from_statements: bool = False,
1812 _proxies: Optional[Any] = None,
1813 **dialect_kwargs: Any,
1814 ):
1815 r"""
1816 Construct a new ``Column`` object.
1818 :param name: The name of this column as represented in the database.
1819 This argument may be the first positional argument, or specified
1820 via keyword.
1822 Names which contain no upper case characters
1823 will be treated as case insensitive names, and will not be quoted
1824 unless they are a reserved word. Names with any number of upper
1825 case characters will be quoted and sent exactly. Note that this
1826 behavior applies even for databases which standardize upper
1827 case names as case insensitive such as Oracle Database.
1829 The name field may be omitted at construction time and applied
1830 later, at any time before the Column is associated with a
1831 :class:`_schema.Table`. This is to support convenient
1832 usage within the :mod:`~sqlalchemy.ext.declarative` extension.
1834 :param type\_: The column's type, indicated using an instance which
1835 subclasses :class:`~sqlalchemy.types.TypeEngine`. If no arguments
1836 are required for the type, the class of the type can be sent
1837 as well, e.g.::
1839 # use a type with arguments
1840 Column("data", String(50))
1842 # use no arguments
1843 Column("level", Integer)
1845 The ``type`` argument may be the second positional argument
1846 or specified by keyword.
1848 If the ``type`` is ``None`` or is omitted, it will first default to
1849 the special type :class:`.NullType`. If and when this
1850 :class:`_schema.Column` is made to refer to another column using
1851 :class:`_schema.ForeignKey` and/or
1852 :class:`_schema.ForeignKeyConstraint`, the type
1853 of the remote-referenced column will be copied to this column as
1854 well, at the moment that the foreign key is resolved against that
1855 remote :class:`_schema.Column` object.
1857 :param \*args: Additional positional arguments include various
1858 :class:`.SchemaItem` derived constructs which will be applied
1859 as options to the column. These include instances of
1860 :class:`.Constraint`, :class:`_schema.ForeignKey`,
1861 :class:`.ColumnDefault`, :class:`.Sequence`, :class:`.Computed`
1862 :class:`.Identity`. In some cases an
1863 equivalent keyword argument is available such as ``server_default``,
1864 ``default`` and ``unique``.
1866 :param autoincrement: Set up "auto increment" semantics for an
1867 **integer primary key column with no foreign key dependencies**
1868 (see later in this docstring for a more specific definition).
1869 This may influence the :term:`DDL` that will be emitted for
1870 this column during a table create, as well as how the column
1871 will be considered when INSERT statements are compiled and
1872 executed.
1874 The default value is the string ``"auto"``,
1875 which indicates that a single-column (i.e. non-composite) primary key
1876 that is of an INTEGER type with no other client-side or server-side
1877 default constructs indicated should receive auto increment semantics
1878 automatically. Other values include ``True`` (force this column to
1879 have auto-increment semantics for a :term:`composite primary key` as
1880 well), ``False`` (this column should never have auto-increment
1881 semantics), and the string ``"ignore_fk"`` (special-case for foreign
1882 key columns, see below).
1884 The term "auto increment semantics" refers both to the kind of DDL
1885 that will be emitted for the column within a CREATE TABLE statement,
1886 when methods such as :meth:`.MetaData.create_all` and
1887 :meth:`.Table.create` are invoked, as well as how the column will be
1888 considered when an INSERT statement is compiled and emitted to the
1889 database:
1891 * **DDL rendering** (i.e. :meth:`.MetaData.create_all`,
1892 :meth:`.Table.create`): When used on a :class:`.Column` that has
1893 no other
1894 default-generating construct associated with it (such as a
1895 :class:`.Sequence` or :class:`.Identity` construct), the parameter
1896 will imply that database-specific keywords such as PostgreSQL
1897 ``SERIAL``, MySQL ``AUTO_INCREMENT``, or ``IDENTITY`` on SQL Server
1898 should also be rendered. Not every database backend has an
1899 "implied" default generator available; for example the Oracle Database
1900 backends always needs an explicit construct such as
1901 :class:`.Identity` to be included with a :class:`.Column` in order
1902 for the DDL rendered to include auto-generating constructs to also
1903 be produced in the database.
1905 * **INSERT semantics** (i.e. when a :func:`_sql.insert` construct is
1906 compiled into a SQL string and is then executed on a database using
1907 :meth:`_engine.Connection.execute` or equivalent): A single-row
1908 INSERT statement will be known to produce a new integer primary key
1909 value automatically for this column, which will be accessible
1910 after the statement is invoked via the
1911 :attr:`.CursorResult.inserted_primary_key` attribute upon the
1912 :class:`_result.Result` object. This also applies towards use of the
1913 ORM when ORM-mapped objects are persisted to the database,
1914 indicating that a new integer primary key will be available to
1915 become part of the :term:`identity key` for that object. This
1916 behavior takes place regardless of what DDL constructs are
1917 associated with the :class:`_schema.Column` and is independent
1918 of the "DDL Rendering" behavior discussed in the previous note
1919 above.
1921 The parameter may be set to ``True`` to indicate that a column which
1922 is part of a composite (i.e. multi-column) primary key should
1923 have autoincrement semantics, though note that only one column
1924 within a primary key may have this setting. It can also
1925 be set to ``True`` to indicate autoincrement semantics on a
1926 column that has a client-side or server-side default configured,
1927 however note that not all dialects can accommodate all styles
1928 of default as an "autoincrement". It can also be
1929 set to ``False`` on a single-column primary key that has a
1930 datatype of INTEGER in order to disable auto increment semantics
1931 for that column.
1933 The setting *only* has an effect for columns which are:
1935 * Integer derived (i.e. INT, SMALLINT, BIGINT).
1937 * Part of the primary key
1939 * Not referring to another column via :class:`_schema.ForeignKey`,
1940 unless
1941 the value is specified as ``'ignore_fk'``::
1943 # turn on autoincrement for this column despite
1944 # the ForeignKey()
1945 Column(
1946 "id",
1947 ForeignKey("other.id"),
1948 primary_key=True,
1949 autoincrement="ignore_fk",
1950 )
1952 It is typically not desirable to have "autoincrement" enabled on a
1953 column that refers to another via foreign key, as such a column is
1954 required to refer to a value that originates from elsewhere.
1956 The setting has these effects on columns that meet the
1957 above criteria:
1959 * DDL issued for the column, if the column does not already include
1960 a default generating construct supported by the backend such as
1961 :class:`.Identity`, will include database-specific
1962 keywords intended to signify this column as an
1963 "autoincrement" column for specific backends. Behavior for
1964 primary SQLAlchemy dialects includes:
1966 * AUTO INCREMENT on MySQL and MariaDB
1967 * SERIAL on PostgreSQL
1968 * IDENTITY on MS-SQL - this occurs even without the
1969 :class:`.Identity` construct as the
1970 :paramref:`.Column.autoincrement` parameter pre-dates this
1971 construct.
1972 * SQLite - SQLite integer primary key columns are implicitly
1973 "auto incrementing" and no additional keywords are rendered;
1974 to render the special SQLite keyword ``AUTOINCREMENT``
1975 is not included as this is unnecessary and not recommended
1976 by the database vendor. See the section
1977 :ref:`sqlite_autoincrement` for more background.
1978 * Oracle Database - The Oracle Database dialects have no default "autoincrement"
1979 feature available at this time, instead the :class:`.Identity`
1980 construct is recommended to achieve this (the :class:`.Sequence`
1981 construct may also be used).
1982 * Third-party dialects - consult those dialects' documentation
1983 for details on their specific behaviors.
1985 * When a single-row :func:`_sql.insert` construct is compiled and
1986 executed, which does not set the :meth:`_sql.Insert.inline`
1987 modifier, newly generated primary key values for this column
1988 will be automatically retrieved upon statement execution
1989 using a method specific to the database driver in use:
1991 * MySQL, SQLite - calling upon ``cursor.lastrowid()``
1992 (see
1993 `https://www.python.org/dev/peps/pep-0249/#lastrowid
1994 <https://www.python.org/dev/peps/pep-0249/#lastrowid>`_)
1995 * PostgreSQL, SQL Server, Oracle Database - use RETURNING or an equivalent
1996 construct when rendering an INSERT statement, and then retrieving
1997 the newly generated primary key values after execution
1998 * PostgreSQL, Oracle Database for :class:`_schema.Table` objects that
1999 set :paramref:`_schema.Table.implicit_returning` to False -
2000 for a :class:`.Sequence` only, the :class:`.Sequence` is invoked
2001 explicitly before the INSERT statement takes place so that the
2002 newly generated primary key value is available to the client
2003 * SQL Server for :class:`_schema.Table` objects that
2004 set :paramref:`_schema.Table.implicit_returning` to False -
2005 the ``SELECT scope_identity()`` construct is used after the
2006 INSERT statement is invoked to retrieve the newly generated
2007 primary key value.
2008 * Third-party dialects - consult those dialects' documentation
2009 for details on their specific behaviors.
2011 * For multiple-row :func:`_sql.insert` constructs invoked with
2012 a list of parameters (i.e. "executemany" semantics), primary-key
2013 retrieving behaviors are generally disabled, however there may
2014 be special APIs that may be used to retrieve lists of new
2015 primary key values for an "executemany", such as the psycopg2
2016 "fast insertmany" feature. Such features are very new and
2017 may not yet be well covered in documentation.
2019 :param default: A scalar, Python callable, or
2020 :class:`_expression.ColumnElement` expression representing the
2021 *default value* for this column, which will be invoked upon insert
2022 if this column is otherwise not specified in the VALUES clause of
2023 the insert. This is a shortcut to using :class:`.ColumnDefault` as
2024 a positional argument; see that class for full detail on the
2025 structure of the argument.
2027 Contrast this argument to
2028 :paramref:`_schema.Column.server_default`
2029 which creates a default generator on the database side.
2031 .. seealso::
2033 :ref:`metadata_defaults_toplevel`
2035 :param insert_default: An alias of :paramref:`.Column.default`
2036 for compatibility with :func:`_orm.mapped_column`.
2038 .. versionadded:: 2.0.31
2040 :param doc: optional String that can be used by the ORM or similar
2041 to document attributes on the Python side. This attribute does
2042 **not** render SQL comments; use the
2043 :paramref:`_schema.Column.comment`
2044 parameter for this purpose.
2046 :param key: An optional string identifier which will identify this
2047 ``Column`` object on the :class:`_schema.Table`.
2048 When a key is provided,
2049 this is the only identifier referencing the ``Column`` within the
2050 application, including ORM attribute mapping; the ``name`` field
2051 is used only when rendering SQL.
2053 :param index: When ``True``, indicates that a :class:`_schema.Index`
2054 construct will be automatically generated for this
2055 :class:`_schema.Column`, which will result in a "CREATE INDEX"
2056 statement being emitted for the :class:`_schema.Table` when the DDL
2057 create operation is invoked.
2059 Using this flag is equivalent to making use of the
2060 :class:`_schema.Index` construct explicitly at the level of the
2061 :class:`_schema.Table` construct itself::
2063 Table(
2064 "some_table",
2065 metadata,
2066 Column("x", Integer),
2067 Index("ix_some_table_x", "x"),
2068 )
2070 To add the :paramref:`_schema.Index.unique` flag to the
2071 :class:`_schema.Index`, set both the
2072 :paramref:`_schema.Column.unique` and
2073 :paramref:`_schema.Column.index` flags to True simultaneously,
2074 which will have the effect of rendering the "CREATE UNIQUE INDEX"
2075 DDL instruction instead of "CREATE INDEX".
2077 The name of the index is generated using the
2078 :ref:`default naming convention <constraint_default_naming_convention>`
2079 which for the :class:`_schema.Index` construct is of the form
2080 ``ix_<tablename>_<columnname>``.
2082 As this flag is intended only as a convenience for the common case
2083 of adding a single-column, default configured index to a table
2084 definition, explicit use of the :class:`_schema.Index` construct
2085 should be preferred for most use cases, including composite indexes
2086 that encompass more than one column, indexes with SQL expressions
2087 or ordering, backend-specific index configuration options, and
2088 indexes that use a specific name.
2090 .. note:: the :attr:`_schema.Column.index` attribute on
2091 :class:`_schema.Column`
2092 **does not indicate** if this column is indexed or not, only
2093 if this flag was explicitly set here. To view indexes on
2094 a column, view the :attr:`_schema.Table.indexes` collection
2095 or use :meth:`_reflection.Inspector.get_indexes`.
2097 .. seealso::
2099 :ref:`schema_indexes`
2101 :ref:`constraint_naming_conventions`
2103 :paramref:`_schema.Column.unique`
2105 :param info: Optional data dictionary which will be populated into the
2106 :attr:`.SchemaItem.info` attribute of this object.
2108 :param nullable: When set to ``False``, will cause the "NOT NULL"
2109 phrase to be added when generating DDL for the column. When
2110 ``True``, will normally generate nothing (in SQL this defaults to
2111 "NULL"), except in some very specific backend-specific edge cases
2112 where "NULL" may render explicitly.
2113 Defaults to ``True`` unless :paramref:`_schema.Column.primary_key`
2114 is also ``True`` or the column specifies a :class:`_sql.Identity`,
2115 in which case it defaults to ``False``.
2116 This parameter is only used when issuing CREATE TABLE statements.
2118 .. note::
2120 When the column specifies a :class:`_sql.Identity` this
2121 parameter is in general ignored by the DDL compiler. The
2122 PostgreSQL database allows nullable identity column by
2123 setting this parameter to ``True`` explicitly.
2125 :param onupdate: A scalar, Python callable, or
2126 :class:`~sqlalchemy.sql.expression.ClauseElement` representing a
2127 default value to be applied to the column within UPDATE
2128 statements, which will be invoked upon update if this column is not
2129 present in the SET clause of the update. This is a shortcut to
2130 using :class:`.ColumnDefault` as a positional argument with
2131 ``for_update=True``.
2133 .. seealso::
2135 :ref:`metadata_defaults` - complete discussion of onupdate
2137 :param primary_key: If ``True``, marks this column as a primary key
2138 column. Multiple columns can have this flag set to specify
2139 composite primary keys. As an alternative, the primary key of a
2140 :class:`_schema.Table` can be specified via an explicit
2141 :class:`.PrimaryKeyConstraint` object.
2143 :param server_default: A :class:`.FetchedValue` instance, str, Unicode
2144 or :func:`~sqlalchemy.sql.expression.text` construct representing
2145 the DDL DEFAULT value for the column.
2147 String types will be emitted as-is, surrounded by single quotes::
2149 Column("x", Text, server_default="val")
2151 will render:
2153 .. sourcecode:: sql
2155 x TEXT DEFAULT 'val'
2157 A :func:`~sqlalchemy.sql.expression.text` expression will be
2158 rendered as-is, without quotes::
2160 Column("y", DateTime, server_default=text("NOW()"))
2162 will render:
2164 .. sourcecode:: sql
2166 y DATETIME DEFAULT NOW()
2168 Strings and text() will be converted into a
2169 :class:`.DefaultClause` object upon initialization.
2171 This parameter can also accept complex combinations of contextually
2172 valid SQLAlchemy expressions or constructs::
2174 from sqlalchemy import create_engine
2175 from sqlalchemy import Table, Column, MetaData, ARRAY, Text
2176 from sqlalchemy.dialects.postgresql import array
2178 engine = create_engine(
2179 "postgresql+psycopg2://scott:tiger@localhost/mydatabase"
2180 )
2181 metadata_obj = MetaData()
2182 tbl = Table(
2183 "foo",
2184 metadata_obj,
2185 Column(
2186 "bar", ARRAY(Text), server_default=array(["biz", "bang", "bash"])
2187 ),
2188 )
2189 metadata_obj.create_all(engine)
2191 The above results in a table created with the following SQL:
2193 .. sourcecode:: sql
2195 CREATE TABLE foo (
2196 bar TEXT[] DEFAULT ARRAY['biz', 'bang', 'bash']
2197 )
2199 Use :class:`.FetchedValue` to indicate that an already-existing
2200 column will generate a default value on the database side which
2201 will be available to SQLAlchemy for post-fetch after inserts. This
2202 construct does not specify any DDL and the implementation is left
2203 to the database, such as via a trigger.
2205 .. seealso::
2207 :ref:`server_defaults` - complete discussion of server side
2208 defaults
2210 :param server_onupdate: A :class:`.FetchedValue` instance
2211 representing a database-side default generation function,
2212 such as a trigger. This
2213 indicates to SQLAlchemy that a newly generated value will be
2214 available after updates. This construct does not actually
2215 implement any kind of generation function within the database,
2216 which instead must be specified separately.
2219 .. warning:: This directive **does not** currently produce MySQL's
2220 "ON UPDATE CURRENT_TIMESTAMP()" clause. See
2221 :ref:`mysql_timestamp_onupdate` for background on how to
2222 produce this clause.
2224 .. seealso::
2226 :ref:`triggered_columns`
2228 :param quote: Force quoting of this column's name on or off,
2229 corresponding to ``True`` or ``False``. When left at its default
2230 of ``None``, the column identifier will be quoted according to
2231 whether the name is case sensitive (identifiers with at least one
2232 upper case character are treated as case sensitive), or if it's a
2233 reserved word. This flag is only needed to force quoting of a
2234 reserved word which is not known by the SQLAlchemy dialect.
2236 :param unique: When ``True``, and the :paramref:`_schema.Column.index`
2237 parameter is left at its default value of ``False``,
2238 indicates that a :class:`_schema.UniqueConstraint`
2239 construct will be automatically generated for this
2240 :class:`_schema.Column`,
2241 which will result in a "UNIQUE CONSTRAINT" clause referring
2242 to this column being included
2243 in the ``CREATE TABLE`` statement emitted, when the DDL create
2244 operation for the :class:`_schema.Table` object is invoked.
2246 When this flag is ``True`` while the
2247 :paramref:`_schema.Column.index` parameter is simultaneously
2248 set to ``True``, the effect instead is that a
2249 :class:`_schema.Index` construct which includes the
2250 :paramref:`_schema.Index.unique` parameter set to ``True``
2251 is generated. See the documentation for
2252 :paramref:`_schema.Column.index` for additional detail.
2254 Using this flag is equivalent to making use of the
2255 :class:`_schema.UniqueConstraint` construct explicitly at the
2256 level of the :class:`_schema.Table` construct itself::
2258 Table("some_table", metadata, Column("x", Integer), UniqueConstraint("x"))
2260 The :paramref:`_schema.UniqueConstraint.name` parameter
2261 of the unique constraint object is left at its default value
2262 of ``None``; in the absence of a :ref:`naming convention <constraint_naming_conventions>`
2263 for the enclosing :class:`_schema.MetaData`, the UNIQUE CONSTRAINT
2264 construct will be emitted as unnamed, which typically invokes
2265 a database-specific naming convention to take place.
2267 As this flag is intended only as a convenience for the common case
2268 of adding a single-column, default configured unique constraint to a table
2269 definition, explicit use of the :class:`_schema.UniqueConstraint` construct
2270 should be preferred for most use cases, including composite constraints
2271 that encompass more than one column, backend-specific index configuration options, and
2272 constraints that use a specific name.
2274 .. note:: the :attr:`_schema.Column.unique` attribute on
2275 :class:`_schema.Column`
2276 **does not indicate** if this column has a unique constraint or
2277 not, only if this flag was explicitly set here. To view
2278 indexes and unique constraints that may involve this column,
2279 view the
2280 :attr:`_schema.Table.indexes` and/or
2281 :attr:`_schema.Table.constraints` collections or use
2282 :meth:`_reflection.Inspector.get_indexes` and/or
2283 :meth:`_reflection.Inspector.get_unique_constraints`
2285 .. seealso::
2287 :ref:`schema_unique_constraint`
2289 :ref:`constraint_naming_conventions`
2291 :paramref:`_schema.Column.index`
2293 :param system: When ``True``, indicates this is a "system" column,
2294 that is a column which is automatically made available by the
2295 database, and should not be included in the columns list for a
2296 ``CREATE TABLE`` statement.
2298 For more elaborate scenarios where columns should be
2299 conditionally rendered differently on different backends,
2300 consider custom compilation rules for :class:`.CreateColumn`.
2302 :param comment: Optional string that will render an SQL comment on
2303 table creation.
2305 :param insert_sentinel: Marks this :class:`_schema.Column` as an
2306 :term:`insert sentinel` used for optimizing the performance of the
2307 :term:`insertmanyvalues` feature for tables that don't
2308 otherwise have qualifying primary key configurations.
2310 .. versionadded:: 2.0.10
2312 .. seealso::
2314 :func:`_schema.insert_sentinel` - all in one helper for declaring
2315 sentinel columns
2317 :ref:`engine_insertmanyvalues`
2319 :ref:`engine_insertmanyvalues_sentinel_columns`
2322 """ # noqa: E501, RST201, RST202
2324 l_args = [__name_pos, __type_pos] + list(args)
2325 del args
2327 if isinstance(l_args[0], str):
2328 if name is not None:
2329 raise exc.ArgumentError(
2330 "May not pass name positionally and as a keyword."
2331 )
2332 name = l_args.pop(0) # type: ignore
2333 elif l_args[0] is None:
2334 l_args.pop(0)
2335 if l_args:
2336 coltype = l_args[0]
2338 if hasattr(coltype, "_sqla_type"):
2339 if type_ is not None:
2340 raise exc.ArgumentError(
2341 "May not pass type_ positionally and as a keyword."
2342 )
2343 type_ = l_args.pop(0) # type: ignore
2344 elif l_args[0] is None:
2345 l_args.pop(0)
2347 if name is not None:
2348 name = quoted_name(name, quote)
2349 elif quote is not None:
2350 raise exc.ArgumentError(
2351 "Explicit 'name' is required when sending 'quote' argument"
2352 )
2354 # name = None is expected to be an interim state
2355 # note this use case is legacy now that ORM declarative has a
2356 # dedicated "column" construct local to the ORM
2357 super().__init__(name, type_) # type: ignore
2359 self.key = key if key is not None else name # type: ignore
2360 self.primary_key = primary_key
2361 self._insert_sentinel = insert_sentinel
2362 self._omit_from_statements = _omit_from_statements
2363 self._user_defined_nullable = udn = nullable
2364 if udn is not NULL_UNSPECIFIED:
2365 self.nullable = udn
2366 else:
2367 self.nullable = not primary_key
2369 # these default to None because .index and .unique is *not*
2370 # an informational flag about Column - there can still be an
2371 # Index or UniqueConstraint referring to this Column.
2372 self.index = index
2373 self.unique = unique
2375 self.system = system
2376 self.doc = doc
2377 self.autoincrement: _AutoIncrementType = autoincrement
2378 self.constraints = set()
2379 self.foreign_keys = set()
2380 self.comment = comment
2381 self.computed = None
2382 self.identity = None
2384 # check if this Column is proxying another column
2386 if _proxies is not None:
2387 self._proxies = _proxies
2388 else:
2389 # otherwise, add DDL-related events
2390 self._set_type(self.type)
2392 if insert_default is not _NoArg.NO_ARG:
2393 if default is not _NoArg.NO_ARG:
2394 raise exc.ArgumentError(
2395 "The 'default' and 'insert_default' parameters "
2396 "of Column are mutually exclusive"
2397 )
2398 resolved_default = insert_default
2399 elif default is not _NoArg.NO_ARG:
2400 resolved_default = default
2401 else:
2402 resolved_default = None
2404 if resolved_default is not None:
2405 if not isinstance(resolved_default, (ColumnDefault, Sequence)):
2406 resolved_default = ColumnDefault(resolved_default)
2408 self.default = resolved_default
2409 l_args.append(resolved_default)
2410 else:
2411 self.default = None
2413 if onupdate is not None:
2414 if not isinstance(onupdate, (ColumnDefault, Sequence)):
2415 onupdate = ColumnDefault(onupdate, for_update=True)
2417 self.onupdate = onupdate
2418 l_args.append(onupdate)
2419 else:
2420 self.onupdate = None
2422 if server_default is not None:
2423 if isinstance(server_default, FetchedValue):
2424 server_default = server_default._as_for_update(False)
2425 l_args.append(server_default)
2426 else:
2427 server_default = DefaultClause(server_default)
2428 l_args.append(server_default)
2429 self.server_default = server_default
2431 if server_onupdate is not None:
2432 if isinstance(server_onupdate, FetchedValue):
2433 server_onupdate = server_onupdate._as_for_update(True)
2434 l_args.append(server_onupdate)
2435 else:
2436 server_onupdate = DefaultClause(
2437 server_onupdate, for_update=True
2438 )
2439 l_args.append(server_onupdate)
2440 self.server_onupdate = server_onupdate
2442 self._init_items(*cast(_typing_Sequence[SchemaItem], l_args))
2444 util.set_creation_order(self)
2446 if info is not None:
2447 self.info = info
2449 self._extra_kwargs(**dialect_kwargs)
2451 table: Table
2453 constraints: Set[Constraint]
2455 foreign_keys: Set[ForeignKey]
2456 """A collection of all :class:`_schema.ForeignKey` marker objects
2457 associated with this :class:`_schema.Column`.
2459 Each object is a member of a :class:`_schema.Table`-wide
2460 :class:`_schema.ForeignKeyConstraint`.
2462 .. seealso::
2464 :attr:`_schema.Table.foreign_keys`
2466 """
2468 index: Optional[bool]
2469 """The value of the :paramref:`_schema.Column.index` parameter.
2471 Does not indicate if this :class:`_schema.Column` is actually indexed
2472 or not; use :attr:`_schema.Table.indexes`.
2474 .. seealso::
2476 :attr:`_schema.Table.indexes`
2477 """
2479 unique: Optional[bool]
2480 """The value of the :paramref:`_schema.Column.unique` parameter.
2482 Does not indicate if this :class:`_schema.Column` is actually subject to
2483 a unique constraint or not; use :attr:`_schema.Table.indexes` and
2484 :attr:`_schema.Table.constraints`.
2486 .. seealso::
2488 :attr:`_schema.Table.indexes`
2490 :attr:`_schema.Table.constraints`.
2492 """
2494 computed: Optional[Computed]
2496 identity: Optional[Identity]
2498 def _set_type(self, type_: TypeEngine[Any]) -> None:
2499 assert self.type._isnull or type_ is self.type
2501 self.type = type_
2502 if isinstance(self.type, SchemaEventTarget):
2503 self.type._set_parent_with_dispatch(self)
2504 for impl in self.type._variant_mapping.values():
2505 if isinstance(impl, SchemaEventTarget):
2506 impl._set_parent_with_dispatch(self)
2508 @HasMemoized.memoized_attribute
2509 def _default_description_tuple(self) -> _DefaultDescriptionTuple:
2510 """used by default.py -> _process_execute_defaults()"""
2512 return _DefaultDescriptionTuple._from_column_default(self.default)
2514 @HasMemoized.memoized_attribute
2515 def _onupdate_description_tuple(self) -> _DefaultDescriptionTuple:
2516 """used by default.py -> _process_execute_defaults()"""
2517 return _DefaultDescriptionTuple._from_column_default(self.onupdate)
2519 @util.memoized_property
2520 def _gen_static_annotations_cache_key(self) -> bool:
2521 """special attribute used by cache key gen, if true, we will
2522 use a static cache key for the annotations dictionary, else we
2523 will generate a new cache key for annotations each time.
2525 Added for #8790
2527 """
2528 return self.table is not None and self.table._is_table
2530 def _extra_kwargs(self, **kwargs: Any) -> None:
2531 self._validate_dialect_kwargs(kwargs)
2533 def __str__(self) -> str:
2534 if self.name is None:
2535 return "(no name)"
2536 elif self.table is not None:
2537 if self.table.named_with_column:
2538 return self.table.description + "." + self.description
2539 else:
2540 return self.description
2541 else:
2542 return self.description
2544 def references(self, column: Column[Any]) -> bool:
2545 """Return True if this Column references the given column via foreign
2546 key."""
2548 for fk in self.foreign_keys:
2549 if fk.column.proxy_set.intersection(column.proxy_set):
2550 return True
2551 else:
2552 return False
2554 def append_foreign_key(self, fk: ForeignKey) -> None:
2555 fk._set_parent_with_dispatch(self)
2557 def __repr__(self) -> str:
2558 kwarg = []
2559 if self.key != self.name:
2560 kwarg.append("key")
2561 if self.primary_key:
2562 kwarg.append("primary_key")
2563 if not self.nullable:
2564 kwarg.append("nullable")
2565 if self.onupdate:
2566 kwarg.append("onupdate")
2567 if self.default:
2568 kwarg.append("default")
2569 if self.server_default:
2570 kwarg.append("server_default")
2571 if self.comment:
2572 kwarg.append("comment")
2573 return "Column(%s)" % ", ".join(
2574 [repr(self.name)]
2575 + [repr(self.type)]
2576 + [repr(x) for x in self.foreign_keys if x is not None]
2577 + [repr(x) for x in self.constraints]
2578 + [
2579 (
2580 self.table is not None
2581 and "table=<%s>" % self.table.description
2582 or "table=None"
2583 )
2584 ]
2585 + ["%s=%s" % (k, repr(getattr(self, k))) for k in kwarg]
2586 )
2588 def _set_parent( # type: ignore[override]
2589 self,
2590 parent: SchemaEventTarget,
2591 *,
2592 all_names: Dict[str, Column[Any]],
2593 allow_replacements: bool,
2594 index: Optional[int] = None,
2595 **kw: Any,
2596 ) -> None:
2597 table = parent
2598 assert isinstance(table, Table)
2599 if not self.name:
2600 raise exc.ArgumentError(
2601 "Column must be constructed with a non-blank name or "
2602 "assign a non-blank .name before adding to a Table."
2603 )
2605 self._reset_memoizations()
2607 if self.key is None:
2608 self.key = self.name
2610 existing = getattr(self, "table", None)
2611 if existing is not None and existing is not table:
2612 raise exc.ArgumentError(
2613 f"Column object '{self.key}' already "
2614 f"assigned to Table '{existing.description}'"
2615 )
2617 extra_remove = None
2618 existing_col = None
2619 conflicts_on = ""
2621 if self.key in table._columns:
2622 existing_col = table._columns[self.key]
2623 if self.key == self.name:
2624 conflicts_on = "name"
2625 else:
2626 conflicts_on = "key"
2627 elif self.name in all_names:
2628 existing_col = all_names[self.name]
2629 extra_remove = {existing_col}
2630 conflicts_on = "name"
2632 if existing_col is not None:
2633 if existing_col is not self:
2634 if not allow_replacements:
2635 raise exc.DuplicateColumnError(
2636 f"A column with {conflicts_on} " f"""'{
2637 self.key if conflicts_on == 'key' else self.name
2638 }' """ f"is already present in table '{table.name}'."
2639 )
2640 for fk in existing_col.foreign_keys:
2641 table.foreign_keys.remove(fk)
2642 if fk.constraint in table.constraints:
2643 # this might have been removed
2644 # already, if it's a composite constraint
2645 # and more than one col being replaced
2646 table.constraints.remove(fk.constraint)
2648 if extra_remove and existing_col is not None and self.key == self.name:
2649 util.warn(
2650 f'Column with user-specified key "{existing_col.key}" is '
2651 "being replaced with "
2652 f'plain named column "{self.name}", '
2653 f'key "{existing_col.key}" is being removed. If this is a '
2654 "reflection operation, specify autoload_replace=False to "
2655 "prevent this replacement."
2656 )
2657 table._columns.replace(self, extra_remove=extra_remove, index=index)
2658 all_names[self.name] = self
2659 self.table = table
2661 if self._insert_sentinel:
2662 if self.table._sentinel_column is not None:
2663 raise exc.ArgumentError(
2664 "a Table may have only one explicit sentinel column"
2665 )
2666 self.table._sentinel_column = self
2668 if self.primary_key:
2669 table.primary_key._replace(self)
2670 elif self.key in table.primary_key:
2671 raise exc.ArgumentError(
2672 f"Trying to redefine primary-key column '{self.key}' as a "
2673 f"non-primary-key column on table '{table.fullname}'"
2674 )
2676 if self.index:
2677 if isinstance(self.index, str):
2678 raise exc.ArgumentError(
2679 "The 'index' keyword argument on Column is boolean only. "
2680 "To create indexes with a specific name, create an "
2681 "explicit Index object external to the Table."
2682 )
2683 table.append_constraint(
2684 Index(
2685 None, self.key, unique=bool(self.unique), _column_flag=True
2686 )
2687 )
2689 elif self.unique:
2690 if isinstance(self.unique, str):
2691 raise exc.ArgumentError(
2692 "The 'unique' keyword argument on Column is boolean "
2693 "only. To create unique constraints or indexes with a "
2694 "specific name, append an explicit UniqueConstraint to "
2695 "the Table's list of elements, or create an explicit "
2696 "Index object external to the Table."
2697 )
2698 table.append_constraint(
2699 UniqueConstraint(self.key, _column_flag=True)
2700 )
2702 self._setup_on_memoized_fks(lambda fk: fk._set_remote_table(table))
2704 if self.identity and (
2705 isinstance(self.default, Sequence)
2706 or isinstance(self.onupdate, Sequence)
2707 ):
2708 raise exc.ArgumentError(
2709 "An column cannot specify both Identity and Sequence."
2710 )
2712 def _setup_on_memoized_fks(self, fn: Callable[..., Any]) -> None:
2713 fk_keys = [
2714 ((self.table.key, self.key), False),
2715 ((self.table.key, self.name), True),
2716 ]
2717 for fk_key, link_to_name in fk_keys:
2718 if fk_key in self.table.metadata._fk_memos:
2719 for fk in self.table.metadata._fk_memos[fk_key]:
2720 if fk.link_to_name is link_to_name:
2721 fn(fk)
2723 def _on_table_attach(self, fn: Callable[..., Any]) -> None:
2724 if self.table is not None:
2725 fn(self, self.table)
2726 else:
2727 event.listen(self, "after_parent_attach", fn)
2729 @util.deprecated(
2730 "1.4",
2731 "The :meth:`_schema.Column.copy` method is deprecated "
2732 "and will be removed in a future release.",
2733 )
2734 def copy(self, **kw: Any) -> Column[Any]:
2735 return self._copy(**kw)
2737 def _copy(self, **kw: Any) -> Column[Any]:
2738 """Create a copy of this ``Column``, uninitialized.
2740 This is used in :meth:`_schema.Table.to_metadata` and by the ORM.
2742 """
2744 # Constraint objects plus non-constraint-bound ForeignKey objects
2745 args: List[SchemaItem] = [
2746 c._copy(**kw) for c in self.constraints if not c._type_bound
2747 ] + [c._copy(**kw) for c in self.foreign_keys if not c.constraint]
2749 # ticket #5276
2750 column_kwargs = {}
2751 for dialect_name in self.dialect_options:
2752 dialect_options = self.dialect_options[dialect_name]._non_defaults
2753 for (
2754 dialect_option_key,
2755 dialect_option_value,
2756 ) in dialect_options.items():
2757 column_kwargs[dialect_name + "_" + dialect_option_key] = (
2758 dialect_option_value
2759 )
2761 server_default = self.server_default
2762 server_onupdate = self.server_onupdate
2763 if isinstance(server_default, (Computed, Identity)):
2764 # TODO: likely should be copied in all cases
2765 # TODO: if a Sequence, we would need to transfer the Sequence
2766 # .metadata as well
2767 args.append(server_default._copy(**kw))
2768 server_default = server_onupdate = None
2770 type_ = self.type
2771 if isinstance(type_, SchemaEventTarget):
2772 type_ = type_.copy(**kw)
2774 # TODO: DefaultGenerator is not copied here! it's just used again
2775 # with _set_parent() pointing to the old column. see the new
2776 # use of _copy() in the new _merge() method
2778 c = self._constructor(
2779 name=self.name,
2780 type_=type_,
2781 key=self.key,
2782 primary_key=self.primary_key,
2783 unique=self.unique,
2784 system=self.system,
2785 # quote=self.quote, # disabled 2013-08-27 (commit 031ef080)
2786 index=self.index,
2787 autoincrement=self.autoincrement,
2788 default=self.default,
2789 server_default=server_default,
2790 onupdate=self.onupdate,
2791 server_onupdate=server_onupdate,
2792 doc=self.doc,
2793 comment=self.comment,
2794 _omit_from_statements=self._omit_from_statements,
2795 insert_sentinel=self._insert_sentinel,
2796 *args,
2797 **column_kwargs,
2798 )
2800 # copy the state of "nullable" exactly, to accommodate for
2801 # ORM flipping the .nullable flag directly
2802 c.nullable = self.nullable
2803 c._user_defined_nullable = self._user_defined_nullable
2805 return self._schema_item_copy(c)
2807 def _merge(
2808 self, other: Column[Any], *, omit_defaults: bool = False
2809 ) -> None:
2810 """merge the elements of this column onto "other"
2812 this is used by ORM pep-593 merge and will likely need a lot
2813 of fixes.
2816 """
2818 if self.primary_key:
2819 other.primary_key = True
2821 if self.autoincrement != "auto" and other.autoincrement == "auto":
2822 other.autoincrement = self.autoincrement
2824 if self.system:
2825 other.system = self.system
2827 if self.info:
2828 other.info.update(self.info)
2830 type_ = self.type
2831 if not type_._isnull and other.type._isnull:
2832 if isinstance(type_, SchemaEventTarget):
2833 type_ = type_.copy()
2835 other.type = type_
2837 if isinstance(type_, SchemaEventTarget):
2838 type_._set_parent_with_dispatch(other)
2840 for impl in type_._variant_mapping.values():
2841 if isinstance(impl, SchemaEventTarget):
2842 impl._set_parent_with_dispatch(other)
2844 if (
2845 self._user_defined_nullable is not NULL_UNSPECIFIED
2846 and other._user_defined_nullable is NULL_UNSPECIFIED
2847 ):
2848 other.nullable = self.nullable
2849 other._user_defined_nullable = self._user_defined_nullable
2851 if (
2852 not omit_defaults
2853 and self.default is not None
2854 and other.default is None
2855 ):
2856 new_default = self.default._copy()
2857 new_default._set_parent(other)
2859 if self.server_default and other.server_default is None:
2860 new_server_default = self.server_default
2861 if isinstance(new_server_default, FetchedValue):
2862 new_server_default = new_server_default._copy()
2863 new_server_default._set_parent(other)
2864 else:
2865 other.server_default = new_server_default
2867 if self.server_onupdate and other.server_onupdate is None:
2868 new_server_onupdate = self.server_onupdate
2869 new_server_onupdate = new_server_onupdate._copy()
2870 new_server_onupdate._set_parent(other)
2872 if self.onupdate and other.onupdate is None:
2873 new_onupdate = self.onupdate._copy()
2874 new_onupdate._set_parent(other)
2876 if self.index in (True, False) and other.index is None:
2877 other.index = self.index
2879 if self.unique in (True, False) and other.unique is None:
2880 other.unique = self.unique
2882 if self.doc and other.doc is None:
2883 other.doc = self.doc
2885 if self.comment and other.comment is None:
2886 other.comment = self.comment
2888 for const in self.constraints:
2889 if not const._type_bound:
2890 new_const = const._copy()
2891 new_const._set_parent(other)
2893 for fk in self.foreign_keys:
2894 if not fk.constraint:
2895 new_fk = fk._copy()
2896 new_fk._set_parent(other)
2898 def _make_proxy(
2899 self,
2900 selectable: FromClause,
2901 primary_key: ColumnSet,
2902 foreign_keys: Set[KeyedColumnElement[Any]],
2903 name: Optional[str] = None,
2904 key: Optional[str] = None,
2905 name_is_truncatable: bool = False,
2906 compound_select_cols: Optional[
2907 _typing_Sequence[ColumnElement[Any]]
2908 ] = None,
2909 **kw: Any,
2910 ) -> Tuple[str, ColumnClause[_T]]:
2911 """Create a *proxy* for this column.
2913 This is a copy of this ``Column`` referenced by a different parent
2914 (such as an alias or select statement). The column should
2915 be used only in select scenarios, as its full DDL/default
2916 information is not transferred.
2918 """
2920 fk = [
2921 ForeignKey(
2922 col if col is not None else f._colspec,
2923 _unresolvable=col is None,
2924 _constraint=f.constraint,
2925 )
2926 for f, col in [
2927 (fk, fk._resolve_column(raiseerr=False))
2928 for fk in self.foreign_keys
2929 ]
2930 ]
2932 if name is None and self.name is None:
2933 raise exc.InvalidRequestError(
2934 "Cannot initialize a sub-selectable"
2935 " with this Column object until its 'name' has "
2936 "been assigned."
2937 )
2938 try:
2939 c = self._constructor(
2940 (
2941 coercions.expect(
2942 roles.TruncatedLabelRole, name if name else self.name
2943 )
2944 if name_is_truncatable
2945 else (name or self.name)
2946 ),
2947 self.type,
2948 # this may actually be ._proxy_key when the key is incoming
2949 key=key if key else name if name else self.key,
2950 primary_key=self.primary_key,
2951 nullable=self.nullable,
2952 _proxies=(
2953 list(compound_select_cols)
2954 if compound_select_cols
2955 else [self]
2956 ),
2957 *fk,
2958 )
2959 except TypeError as err:
2960 raise TypeError(
2961 "Could not create a copy of this %r object. "
2962 "Ensure the class includes a _constructor() "
2963 "attribute or method which accepts the "
2964 "standard Column constructor arguments, or "
2965 "references the Column class itself." % self.__class__
2966 ) from err
2968 c.table = selectable
2969 c._propagate_attrs = selectable._propagate_attrs
2970 if selectable._is_clone_of is not None:
2971 c._is_clone_of = selectable._is_clone_of.columns.get(c.key)
2973 if self.primary_key:
2974 primary_key.add(c)
2976 if fk:
2977 foreign_keys.update(fk) # type: ignore
2979 return c.key, c
2982def insert_sentinel(
2983 name: Optional[str] = None,
2984 type_: Optional[_TypeEngineArgument[_T]] = None,
2985 *,
2986 default: Optional[Any] = None,
2987 omit_from_statements: bool = True,
2988) -> Column[Any]:
2989 """Provides a surrogate :class:`_schema.Column` that will act as a
2990 dedicated insert :term:`sentinel` column, allowing efficient bulk
2991 inserts with deterministic RETURNING sorting for tables that
2992 don't otherwise have qualifying primary key configurations.
2994 Adding this column to a :class:`.Table` object requires that a
2995 corresponding database table actually has this column present, so if adding
2996 it to an existing model, existing database tables would need to be migrated
2997 (e.g. using ALTER TABLE or similar) to include this column.
2999 For background on how this object is used, see the section
3000 :ref:`engine_insertmanyvalues_sentinel_columns` as part of the
3001 section :ref:`engine_insertmanyvalues`.
3003 The :class:`_schema.Column` returned will be a nullable integer column by
3004 default and make use of a sentinel-specific default generator used only in
3005 "insertmanyvalues" operations.
3007 .. seealso::
3009 :func:`_orm.orm_insert_sentinel`
3011 :paramref:`_schema.Column.insert_sentinel`
3013 :ref:`engine_insertmanyvalues`
3015 :ref:`engine_insertmanyvalues_sentinel_columns`
3018 .. versionadded:: 2.0.10
3020 """
3021 return Column(
3022 name=name,
3023 type_=type_api.INTEGERTYPE if type_ is None else type_,
3024 default=(
3025 default if default is not None else _InsertSentinelColumnDefault()
3026 ),
3027 _omit_from_statements=omit_from_statements,
3028 insert_sentinel=True,
3029 )
3032class ForeignKey(DialectKWArgs, SchemaItem):
3033 """Defines a dependency between two columns.
3035 ``ForeignKey`` is specified as an argument to a :class:`_schema.Column`
3036 object,
3037 e.g.::
3039 t = Table(
3040 "remote_table",
3041 metadata,
3042 Column("remote_id", ForeignKey("main_table.id")),
3043 )
3045 Note that ``ForeignKey`` is only a marker object that defines
3046 a dependency between two columns. The actual constraint
3047 is in all cases represented by the :class:`_schema.ForeignKeyConstraint`
3048 object. This object will be generated automatically when
3049 a ``ForeignKey`` is associated with a :class:`_schema.Column` which
3050 in turn is associated with a :class:`_schema.Table`. Conversely,
3051 when :class:`_schema.ForeignKeyConstraint` is applied to a
3052 :class:`_schema.Table`,
3053 ``ForeignKey`` markers are automatically generated to be
3054 present on each associated :class:`_schema.Column`, which are also
3055 associated with the constraint object.
3057 Note that you cannot define a "composite" foreign key constraint,
3058 that is a constraint between a grouping of multiple parent/child
3059 columns, using ``ForeignKey`` objects. To define this grouping,
3060 the :class:`_schema.ForeignKeyConstraint` object must be used, and applied
3061 to the :class:`_schema.Table`. The associated ``ForeignKey`` objects
3062 are created automatically.
3064 The ``ForeignKey`` objects associated with an individual
3065 :class:`_schema.Column`
3066 object are available in the `foreign_keys` collection
3067 of that column.
3069 Further examples of foreign key configuration are in
3070 :ref:`metadata_foreignkeys`.
3072 """
3074 __visit_name__ = "foreign_key"
3076 parent: Column[Any]
3078 _table_column: Optional[Column[Any]]
3080 _colspec: Union[str, Column[Any]]
3082 def __init__(
3083 self,
3084 column: _DDLColumnReferenceArgument,
3085 _constraint: Optional[ForeignKeyConstraint] = None,
3086 use_alter: bool = False,
3087 name: _ConstraintNameArgument = None,
3088 onupdate: Optional[str] = None,
3089 ondelete: Optional[str] = None,
3090 deferrable: Optional[bool] = None,
3091 initially: Optional[str] = None,
3092 link_to_name: bool = False,
3093 match: Optional[str] = None,
3094 info: Optional[_InfoType] = None,
3095 comment: Optional[str] = None,
3096 _unresolvable: bool = False,
3097 **dialect_kw: Any,
3098 ):
3099 r"""
3100 Construct a column-level FOREIGN KEY.
3102 The :class:`_schema.ForeignKey` object when constructed generates a
3103 :class:`_schema.ForeignKeyConstraint`
3104 which is associated with the parent
3105 :class:`_schema.Table` object's collection of constraints.
3107 :param column: A single target column for the key relationship. A
3108 :class:`_schema.Column` object or a column name as a string:
3109 ``tablename.columnkey`` or ``schema.tablename.columnkey``.
3110 ``columnkey`` is the ``key`` which has been assigned to the column
3111 (defaults to the column name itself), unless ``link_to_name`` is
3112 ``True`` in which case the rendered name of the column is used.
3114 :param name: Optional string. An in-database name for the key if
3115 `constraint` is not provided.
3117 :param onupdate: Optional string. If set, emit ON UPDATE <value> when
3118 issuing DDL for this constraint. Typical values include CASCADE,
3119 DELETE and RESTRICT.
3121 .. seealso::
3123 :ref:`on_update_on_delete`
3125 :param ondelete: Optional string. If set, emit ON DELETE <value> when
3126 issuing DDL for this constraint. Typical values include CASCADE,
3127 SET NULL and RESTRICT. Some dialects may allow for additional
3128 syntaxes.
3130 .. seealso::
3132 :ref:`on_update_on_delete`
3134 :param deferrable: Optional bool. If set, emit DEFERRABLE or NOT
3135 DEFERRABLE when issuing DDL for this constraint.
3137 :param initially: Optional string. If set, emit INITIALLY <value> when
3138 issuing DDL for this constraint.
3140 :param link_to_name: if True, the string name given in ``column`` is
3141 the rendered name of the referenced column, not its locally
3142 assigned ``key``.
3144 :param use_alter: passed to the underlying
3145 :class:`_schema.ForeignKeyConstraint`
3146 to indicate the constraint should
3147 be generated/dropped externally from the CREATE TABLE/ DROP TABLE
3148 statement. See :paramref:`_schema.ForeignKeyConstraint.use_alter`
3149 for further description.
3151 .. seealso::
3153 :paramref:`_schema.ForeignKeyConstraint.use_alter`
3155 :ref:`use_alter`
3157 :param match: Optional string. If set, emit MATCH <value> when issuing
3158 DDL for this constraint. Typical values include SIMPLE, PARTIAL
3159 and FULL.
3161 :param info: Optional data dictionary which will be populated into the
3162 :attr:`.SchemaItem.info` attribute of this object.
3164 :param comment: Optional string that will render an SQL comment on
3165 foreign key constraint creation.
3167 .. versionadded:: 2.0
3169 :param \**dialect_kw: Additional keyword arguments are dialect
3170 specific, and passed in the form ``<dialectname>_<argname>``. The
3171 arguments are ultimately handled by a corresponding
3172 :class:`_schema.ForeignKeyConstraint`.
3173 See the documentation regarding
3174 an individual dialect at :ref:`dialect_toplevel` for detail on
3175 documented arguments.
3177 """
3179 self._unresolvable = _unresolvable
3181 self._colspec, self._table_column = self._parse_colspec_argument(
3182 column
3183 )
3185 # the linked ForeignKeyConstraint.
3186 # ForeignKey will create this when parent Column
3187 # is attached to a Table, *or* ForeignKeyConstraint
3188 # object passes itself in when creating ForeignKey
3189 # markers.
3190 self.constraint = _constraint
3192 # .parent is not Optional under normal use
3193 self.parent = None # type: ignore
3195 self.use_alter = use_alter
3196 self.name = name
3197 self.onupdate = onupdate
3198 self.ondelete = ondelete
3199 self.deferrable = deferrable
3200 self.initially = initially
3201 self.link_to_name = link_to_name
3202 self.match = match
3203 self.comment = comment
3204 if info:
3205 self.info = info
3206 self._unvalidated_dialect_kw = dialect_kw
3208 def _resolve_colspec_argument(
3209 self,
3210 ) -> Tuple[
3211 Union[str, Column[Any]],
3212 Optional[Column[Any]],
3213 ]:
3214 argument = self._colspec
3216 return self._parse_colspec_argument(argument)
3218 def _parse_colspec_argument(
3219 self,
3220 argument: _DDLColumnArgument,
3221 ) -> Tuple[
3222 Union[str, Column[Any]],
3223 Optional[Column[Any]],
3224 ]:
3225 _colspec = coercions.expect(roles.DDLReferredColumnRole, argument)
3227 if isinstance(_colspec, str):
3228 _table_column = None
3229 else:
3230 assert isinstance(_colspec, ColumnClause)
3231 _table_column = _colspec
3233 if not isinstance(_table_column.table, (type(None), TableClause)):
3234 raise exc.ArgumentError(
3235 "ForeignKey received Column not bound "
3236 "to a Table, got: %r" % _table_column.table
3237 )
3239 return _colspec, _table_column
3241 def __repr__(self) -> str:
3242 return "ForeignKey(%r)" % self._get_colspec()
3244 @util.deprecated(
3245 "1.4",
3246 "The :meth:`_schema.ForeignKey.copy` method is deprecated "
3247 "and will be removed in a future release.",
3248 )
3249 def copy(self, *, schema: Optional[str] = None, **kw: Any) -> ForeignKey:
3250 return self._copy(schema=schema, **kw)
3252 def _copy(self, *, schema: Optional[str] = None, **kw: Any) -> ForeignKey:
3253 """Produce a copy of this :class:`_schema.ForeignKey` object.
3255 The new :class:`_schema.ForeignKey` will not be bound
3256 to any :class:`_schema.Column`.
3258 This method is usually used by the internal
3259 copy procedures of :class:`_schema.Column`, :class:`_schema.Table`,
3260 and :class:`_schema.MetaData`.
3262 :param schema: The returned :class:`_schema.ForeignKey` will
3263 reference the original table and column name, qualified
3264 by the given string schema name.
3266 """
3267 fk = ForeignKey(
3268 self._get_colspec(schema=schema),
3269 use_alter=self.use_alter,
3270 name=self.name,
3271 onupdate=self.onupdate,
3272 ondelete=self.ondelete,
3273 deferrable=self.deferrable,
3274 initially=self.initially,
3275 link_to_name=self.link_to_name,
3276 match=self.match,
3277 comment=self.comment,
3278 **self._unvalidated_dialect_kw,
3279 )
3280 return self._schema_item_copy(fk)
3282 def _get_colspec(
3283 self,
3284 schema: Optional[
3285 Union[
3286 str,
3287 Literal[SchemaConst.RETAIN_SCHEMA, SchemaConst.BLANK_SCHEMA],
3288 ]
3289 ] = None,
3290 table_name: Optional[str] = None,
3291 _is_copy: bool = False,
3292 ) -> str:
3293 """Return a string based 'column specification' for this
3294 :class:`_schema.ForeignKey`.
3296 This is usually the equivalent of the string-based "tablename.colname"
3297 argument first passed to the object's constructor.
3299 """
3301 _colspec, effective_table_column = self._resolve_colspec_argument()
3303 if schema not in (None, RETAIN_SCHEMA):
3304 _schema, tname, colname = self._column_tokens
3305 if table_name is not None:
3306 tname = table_name
3307 if schema is BLANK_SCHEMA:
3308 return "%s.%s" % (tname, colname)
3309 else:
3310 return "%s.%s.%s" % (schema, tname, colname)
3311 elif table_name:
3312 schema, tname, colname = self._column_tokens
3313 if schema:
3314 return "%s.%s.%s" % (schema, table_name, colname)
3315 else:
3316 return "%s.%s" % (table_name, colname)
3317 elif effective_table_column is not None:
3318 if effective_table_column.table is None:
3319 if _is_copy:
3320 raise exc.InvalidRequestError(
3321 f"Can't copy ForeignKey object which refers to "
3322 f"non-table bound Column {effective_table_column!r}"
3323 )
3324 else:
3325 return effective_table_column.key
3326 return "%s.%s" % (
3327 effective_table_column.table.fullname,
3328 effective_table_column.key,
3329 )
3330 else:
3331 assert isinstance(_colspec, str)
3332 return _colspec
3334 @property
3335 def _referred_schema(self) -> Optional[str]:
3336 return self._column_tokens[0]
3338 def _table_key_within_construction(self) -> Any:
3339 """get the table key but only safely"""
3341 if self._table_column is not None:
3342 if self._table_column.table is None:
3343 return None
3344 else:
3345 return self._table_column.table.key
3346 else:
3347 schema, tname, colname = self._column_tokens
3348 return _get_table_key(tname, schema)
3350 target_fullname = property(_get_colspec)
3352 def references(self, table: Table) -> bool:
3353 """Return True if the given :class:`_schema.Table`
3354 is referenced by this
3355 :class:`_schema.ForeignKey`."""
3357 return table.corresponding_column(self.column) is not None
3359 def get_referent(self, table: FromClause) -> Optional[Column[Any]]:
3360 """Return the :class:`_schema.Column` in the given
3361 :class:`_schema.Table` (or any :class:`.FromClause`)
3362 referenced by this :class:`_schema.ForeignKey`.
3364 Returns None if this :class:`_schema.ForeignKey`
3365 does not reference the given
3366 :class:`_schema.Table`.
3368 """
3369 # our column is a Column, and any subquery etc. proxying us
3370 # would be doing so via another Column, so that's what would
3371 # be returned here
3372 return table.columns.corresponding_column(self.column) # type: ignore
3374 @util.memoized_property
3375 def _column_tokens(self) -> Tuple[Optional[str], str, Optional[str]]:
3376 """parse a string-based _colspec into its component parts."""
3378 m = self._get_colspec().split(".")
3379 if len(m) == 1:
3380 tname = m.pop()
3381 colname = None
3382 else:
3383 colname = m.pop()
3384 tname = m.pop()
3386 # A FK between column 'bar' and table 'foo' can be
3387 # specified as 'foo', 'foo.bar', 'dbo.foo.bar',
3388 # 'otherdb.dbo.foo.bar'. Once we have the column name and
3389 # the table name, treat everything else as the schema
3390 # name. Some databases (e.g. Sybase) support
3391 # inter-database foreign keys. See tickets#1341 and --
3392 # indirectly related -- Ticket #594. This assumes that '.'
3393 # will never appear *within* any component of the FK.
3395 if len(m) > 0:
3396 schema = ".".join(m)
3397 else:
3398 schema = None
3399 return schema, tname, colname
3401 def _resolve_col_tokens(self) -> Tuple[Table, str, Optional[str]]:
3402 if self.parent is None:
3403 raise exc.InvalidRequestError(
3404 "this ForeignKey object does not yet have a "
3405 "parent Column associated with it."
3406 )
3408 elif self.parent.table is None:
3409 raise exc.InvalidRequestError(
3410 "this ForeignKey's parent column is not yet associated "
3411 "with a Table."
3412 )
3414 parenttable = self.parent.table
3416 if self._unresolvable:
3417 schema, tname, colname = self._column_tokens
3418 tablekey = _get_table_key(tname, schema)
3419 return parenttable, tablekey, colname
3421 # assertion
3422 # basically Column._make_proxy() sends the actual
3423 # target Column to the ForeignKey object, so the
3424 # string resolution here is never called.
3425 for c in self.parent.base_columns:
3426 if isinstance(c, Column):
3427 assert c.table is parenttable
3428 break
3429 else:
3430 assert False
3431 ######################
3433 schema, tname, colname = self._column_tokens
3435 if schema is None and parenttable.metadata.schema is not None:
3436 schema = parenttable.metadata.schema
3438 tablekey = _get_table_key(tname, schema)
3439 return parenttable, tablekey, colname
3441 def _link_to_col_by_colstring(
3442 self, parenttable: Table, table: Table, colname: Optional[str]
3443 ) -> Column[Any]:
3444 _column = None
3445 if colname is None:
3446 # colname is None in the case that ForeignKey argument
3447 # was specified as table name only, in which case we
3448 # match the column name to the same column on the
3449 # parent.
3450 # this use case wasn't working in later 1.x series
3451 # as it had no test coverage; fixed in 2.0
3452 parent = self.parent
3453 assert parent is not None
3454 key = parent.key
3455 _column = table.c.get(key, None)
3456 elif self.link_to_name:
3457 key = colname
3458 for c in table.c:
3459 if c.name == colname:
3460 _column = c
3461 else:
3462 key = colname
3463 _column = table.c.get(colname, None)
3465 if _column is None:
3466 raise exc.NoReferencedColumnError(
3467 "Could not initialize target column "
3468 f"for ForeignKey '{self._get_colspec()}' "
3469 f"on table '{parenttable.name}': "
3470 f"table '{table.name}' has no column named '{key}'",
3471 table.name,
3472 key,
3473 )
3475 return _column
3477 def _set_target_column(self, column: Column[Any]) -> None:
3478 assert self.parent is not None
3480 # propagate TypeEngine to parent if it didn't have one
3481 if self.parent.type._isnull:
3482 self.parent.type = column.type
3484 # super-edgy case, if other FKs point to our column,
3485 # they'd get the type propagated out also.
3487 def set_type(fk: ForeignKey) -> None:
3488 if fk.parent.type._isnull:
3489 fk.parent.type = column.type
3491 self.parent._setup_on_memoized_fks(set_type)
3493 self.column = column # type: ignore
3495 @util.ro_memoized_property
3496 def column(self) -> Column[Any]:
3497 """Return the target :class:`_schema.Column` referenced by this
3498 :class:`_schema.ForeignKey`.
3500 If no target column has been established, an exception
3501 is raised.
3503 """
3504 return self._resolve_column()
3506 @overload
3507 def _resolve_column(
3508 self, *, raiseerr: Literal[True] = ...
3509 ) -> Column[Any]: ...
3511 @overload
3512 def _resolve_column(
3513 self, *, raiseerr: bool = ...
3514 ) -> Optional[Column[Any]]: ...
3516 def _resolve_column(
3517 self, *, raiseerr: bool = True
3518 ) -> Optional[Column[Any]]:
3519 _column: Column[Any]
3521 _colspec, effective_table_column = self._resolve_colspec_argument()
3523 if isinstance(_colspec, str):
3524 parenttable, tablekey, colname = self._resolve_col_tokens()
3526 if self._unresolvable or tablekey not in parenttable.metadata:
3527 if not raiseerr:
3528 return None
3529 raise exc.NoReferencedTableError(
3530 f"Foreign key associated with column "
3531 f"'{self.parent}' could not find "
3532 f"table '{tablekey}' with which to generate a "
3533 f"foreign key to target column '{colname}'",
3534 tablekey,
3535 )
3536 elif parenttable.key not in parenttable.metadata:
3537 if not raiseerr:
3538 return None
3539 raise exc.InvalidRequestError(
3540 f"Table {parenttable} is no longer associated with its "
3541 "parent MetaData"
3542 )
3543 else:
3544 table = parenttable.metadata.tables[tablekey]
3545 return self._link_to_col_by_colstring(
3546 parenttable, table, colname
3547 )
3549 elif hasattr(_colspec, "__clause_element__"):
3550 _column = _colspec.__clause_element__()
3551 return _column
3552 else:
3553 assert isinstance(_colspec, Column)
3554 _column = _colspec
3555 return _column
3557 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
3558 assert isinstance(parent, Column)
3560 if self.parent is not None and self.parent is not parent:
3561 raise exc.InvalidRequestError(
3562 "This ForeignKey already has a parent !"
3563 )
3564 self.parent = parent
3565 self.parent.foreign_keys.add(self)
3566 self.parent._on_table_attach(self._set_table)
3568 def _set_remote_table(self, table: Table) -> None:
3569 parenttable, _, colname = self._resolve_col_tokens()
3570 _column = self._link_to_col_by_colstring(parenttable, table, colname)
3571 self._set_target_column(_column)
3572 assert self.constraint is not None
3573 self.constraint._validate_dest_table(table)
3575 def _remove_from_metadata(self, metadata: MetaData) -> None:
3576 parenttable, table_key, colname = self._resolve_col_tokens()
3577 fk_key = (table_key, colname)
3579 if self in metadata._fk_memos[fk_key]:
3580 # TODO: no test coverage for self not in memos
3581 metadata._fk_memos[fk_key].remove(self)
3583 def _set_table(self, column: Column[Any], table: Table) -> None:
3584 # standalone ForeignKey - create ForeignKeyConstraint
3585 # on the hosting Table when attached to the Table.
3586 assert isinstance(table, Table)
3587 if self.constraint is None:
3588 self.constraint = ForeignKeyConstraint(
3589 [],
3590 [],
3591 use_alter=self.use_alter,
3592 name=self.name,
3593 onupdate=self.onupdate,
3594 ondelete=self.ondelete,
3595 deferrable=self.deferrable,
3596 initially=self.initially,
3597 match=self.match,
3598 comment=self.comment,
3599 **self._unvalidated_dialect_kw,
3600 )
3601 self.constraint._append_element(column, self)
3602 self.constraint._set_parent_with_dispatch(table)
3603 table.foreign_keys.add(self)
3604 # set up remote ".column" attribute, or a note to pick it
3605 # up when the other Table/Column shows up
3607 _colspec, _ = self._resolve_colspec_argument()
3608 if isinstance(_colspec, str):
3609 parenttable, table_key, colname = self._resolve_col_tokens()
3610 fk_key = (table_key, colname)
3611 if table_key in parenttable.metadata.tables:
3612 table = parenttable.metadata.tables[table_key]
3613 try:
3614 _column = self._link_to_col_by_colstring(
3615 parenttable, table, colname
3616 )
3617 except exc.NoReferencedColumnError:
3618 # this is OK, we'll try later
3619 pass
3620 else:
3621 self._set_target_column(_column)
3623 parenttable.metadata._fk_memos[fk_key].append(self)
3624 elif hasattr(_colspec, "__clause_element__"):
3625 _column = _colspec.__clause_element__()
3626 self._set_target_column(_column)
3627 else:
3628 self._set_target_column(_colspec)
3631if TYPE_CHECKING:
3633 def default_is_sequence(
3634 obj: Optional[DefaultGenerator],
3635 ) -> TypeGuard[Sequence]: ...
3637 def default_is_clause_element(
3638 obj: Optional[DefaultGenerator],
3639 ) -> TypeGuard[ColumnElementColumnDefault]: ...
3641 def default_is_scalar(
3642 obj: Optional[DefaultGenerator],
3643 ) -> TypeGuard[ScalarElementColumnDefault]: ...
3645else:
3646 default_is_sequence = operator.attrgetter("is_sequence")
3648 default_is_clause_element = operator.attrgetter("is_clause_element")
3650 default_is_scalar = operator.attrgetter("is_scalar")
3653class DefaultGenerator(Executable, SchemaItem):
3654 """Base class for column *default* values.
3656 This object is only present on column.default or column.onupdate.
3657 It's not valid as a server default.
3659 """
3661 __visit_name__ = "default_generator"
3663 _is_default_generator = True
3664 is_sequence = False
3665 is_identity = False
3666 is_server_default = False
3667 is_clause_element = False
3668 is_callable = False
3669 is_scalar = False
3670 has_arg = False
3671 is_sentinel = False
3672 _is_monotonic_fn = False
3673 column: Optional[Column[Any]]
3675 def __init__(self, for_update: bool = False) -> None:
3676 self.for_update = for_update
3678 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
3679 if TYPE_CHECKING:
3680 assert isinstance(parent, Column)
3681 self.column = parent
3682 if self.for_update:
3683 self.column.onupdate = self
3684 else:
3685 self.column.default = self
3687 def _copy(self) -> DefaultGenerator:
3688 raise NotImplementedError()
3690 def _execute_on_connection(
3691 self,
3692 connection: Connection,
3693 distilled_params: _CoreMultiExecuteParams,
3694 execution_options: CoreExecuteOptionsParameter,
3695 ) -> Any:
3696 util.warn_deprecated(
3697 "Using the .execute() method to invoke a "
3698 "DefaultGenerator object is deprecated; please use "
3699 "the .scalar() method.",
3700 "2.0",
3701 )
3702 return self._execute_on_scalar(
3703 connection, distilled_params, execution_options
3704 )
3706 def _execute_on_scalar(
3707 self,
3708 connection: Connection,
3709 distilled_params: _CoreMultiExecuteParams,
3710 execution_options: CoreExecuteOptionsParameter,
3711 ) -> Any:
3712 return connection._execute_default(
3713 self, distilled_params, execution_options
3714 )
3717class ColumnDefault(DefaultGenerator, ABC):
3718 """A plain default value on a column.
3720 This could correspond to a constant, a callable function,
3721 or a SQL clause.
3723 :class:`.ColumnDefault` is generated automatically
3724 whenever the ``default``, ``onupdate`` arguments of
3725 :class:`_schema.Column` are used. A :class:`.ColumnDefault`
3726 can be passed positionally as well.
3728 For example, the following::
3730 Column("foo", Integer, default=50)
3732 Is equivalent to::
3734 Column("foo", Integer, ColumnDefault(50))
3736 """
3738 arg: Any
3740 _is_monotonic_fn = False
3742 @overload
3743 def __new__(
3744 cls, arg: Callable[..., Any], for_update: bool = ...
3745 ) -> CallableColumnDefault: ...
3747 @overload
3748 def __new__(
3749 cls, arg: ColumnElement[Any], for_update: bool = ...
3750 ) -> ColumnElementColumnDefault: ...
3752 # if I return ScalarElementColumnDefault here, which is what's actually
3753 # returned, mypy complains that
3754 # overloads overlap w/ incompatible return types.
3755 @overload
3756 def __new__(cls, arg: object, for_update: bool = ...) -> ColumnDefault: ...
3758 def __new__(
3759 cls, arg: Any = None, for_update: bool = False
3760 ) -> ColumnDefault:
3761 """Construct a new :class:`.ColumnDefault`.
3764 :param arg: argument representing the default value.
3765 May be one of the following:
3767 * a plain non-callable Python value, such as a
3768 string, integer, boolean, or other simple type.
3769 The default value will be used as is each time.
3770 * a SQL expression, that is one which derives from
3771 :class:`_expression.ColumnElement`. The SQL expression will
3772 be rendered into the INSERT or UPDATE statement,
3773 or in the case of a primary key column when
3774 RETURNING is not used may be
3775 pre-executed before an INSERT within a SELECT.
3776 * A Python callable. The function will be invoked for each
3777 new row subject to an INSERT or UPDATE.
3778 The callable must accept exactly
3779 zero or one positional arguments. The one-argument form
3780 will receive an instance of the :class:`.ExecutionContext`,
3781 which provides contextual information as to the current
3782 :class:`_engine.Connection` in use as well as the current
3783 statement and parameters.
3785 """
3787 if isinstance(arg, FetchedValue):
3788 raise exc.ArgumentError(
3789 "ColumnDefault may not be a server-side default type."
3790 )
3791 elif callable(arg):
3792 cls = CallableColumnDefault
3793 elif isinstance(arg, ClauseElement):
3794 cls = ColumnElementColumnDefault
3795 elif arg is not None:
3796 cls = ScalarElementColumnDefault
3798 return object.__new__(cls)
3800 def __repr__(self) -> str:
3801 return f"{self.__class__.__name__}({self.arg!r})"
3804class ScalarElementColumnDefault(ColumnDefault):
3805 """default generator for a fixed scalar Python value
3807 .. versionadded:: 2.0
3809 """
3811 is_scalar = True
3812 has_arg = True
3814 def __init__(self, arg: Any, for_update: bool = False) -> None:
3815 self.for_update = for_update
3816 self.arg = arg
3818 def _copy(self) -> ScalarElementColumnDefault:
3819 return ScalarElementColumnDefault(
3820 arg=self.arg, for_update=self.for_update
3821 )
3824class _InsertSentinelColumnDefault(ColumnDefault):
3825 """Default generator that's specific to the use of a "sentinel" column
3826 when using the insertmanyvalues feature.
3828 This default is used as part of the :func:`_schema.insert_sentinel`
3829 construct.
3831 """
3833 is_sentinel = True
3834 for_update = False
3835 arg = None
3837 def __new__(cls) -> _InsertSentinelColumnDefault:
3838 return object.__new__(cls)
3840 def __init__(self) -> None:
3841 pass
3843 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
3844 col = cast("Column[Any]", parent)
3845 if not col._insert_sentinel:
3846 raise exc.ArgumentError(
3847 "The _InsertSentinelColumnDefault may only be applied to a "
3848 "Column marked as insert_sentinel=True"
3849 )
3850 elif not col.nullable:
3851 raise exc.ArgumentError(
3852 "The _InsertSentinelColumnDefault may only be applied to a "
3853 "Column that is nullable"
3854 )
3856 super()._set_parent(parent, **kw)
3858 def _copy(self) -> _InsertSentinelColumnDefault:
3859 return _InsertSentinelColumnDefault()
3862_SQLExprDefault = Union["ColumnElement[Any]", "TextClause"]
3865class ColumnElementColumnDefault(ColumnDefault):
3866 """default generator for a SQL expression
3868 .. versionadded:: 2.0
3870 """
3872 is_clause_element = True
3873 has_arg = True
3874 arg: _SQLExprDefault
3876 def __init__(
3877 self,
3878 arg: _SQLExprDefault,
3879 for_update: bool = False,
3880 ) -> None:
3881 self.for_update = for_update
3882 self.arg = arg
3884 def _copy(self) -> ColumnElementColumnDefault:
3885 return ColumnElementColumnDefault(
3886 arg=self.arg, for_update=self.for_update
3887 )
3889 @util.memoized_property
3890 @util.preload_module("sqlalchemy.sql.functions")
3891 def _is_monotonic_fn(self) -> bool:
3892 functions = util.preloaded.sql_functions
3893 return (
3894 isinstance(self.arg, functions.FunctionElement)
3895 and self.arg.monotonic
3896 )
3898 @util.memoized_property
3899 @util.preload_module("sqlalchemy.sql.sqltypes")
3900 def _arg_is_typed(self) -> bool:
3901 sqltypes = util.preloaded.sql_sqltypes
3903 return not isinstance(self.arg.type, sqltypes.NullType)
3906class _CallableColumnDefaultProtocol(Protocol):
3907 def __call__(self, context: ExecutionContext) -> Any: ...
3910class CallableColumnDefault(ColumnDefault):
3911 """default generator for a callable Python function
3913 .. versionadded:: 2.0
3915 """
3917 is_callable = True
3918 arg: _CallableColumnDefaultProtocol
3919 has_arg = True
3921 def __init__(
3922 self,
3923 arg: Union[_CallableColumnDefaultProtocol, Callable[[], Any]],
3924 for_update: bool = False,
3925 ) -> None:
3926 self.for_update = for_update
3927 self.arg = self._maybe_wrap_callable(arg)
3929 def _copy(self) -> CallableColumnDefault:
3930 return CallableColumnDefault(arg=self.arg, for_update=self.for_update)
3932 def _maybe_wrap_callable(
3933 self, fn: Union[_CallableColumnDefaultProtocol, Callable[[], Any]]
3934 ) -> _CallableColumnDefaultProtocol:
3935 """Wrap callables that don't accept a context.
3937 This is to allow easy compatibility with default callables
3938 that aren't specific to accepting of a context.
3940 """
3942 try:
3943 argspec = util.get_callable_argspec(fn, no_self=True)
3944 except TypeError:
3945 return util.wrap_callable(lambda ctx: fn(), fn) # type: ignore
3947 defaulted = argspec[3] is not None and len(argspec[3]) or 0
3948 positionals = len(argspec[0]) - defaulted
3950 if positionals == 0:
3951 return util.wrap_callable(lambda ctx: fn(), fn) # type: ignore
3953 elif positionals == 1:
3954 return fn # type: ignore
3955 else:
3956 raise exc.ArgumentError(
3957 "ColumnDefault Python function takes zero or one "
3958 "positional arguments"
3959 )
3962class IdentityOptions(DialectKWArgs):
3963 """Defines options for a named database sequence or an identity column.
3965 .. seealso::
3967 :class:`.Sequence`
3969 """
3971 def __init__(
3972 self,
3973 start: Optional[int] = None,
3974 increment: Optional[int] = None,
3975 minvalue: Optional[int] = None,
3976 maxvalue: Optional[int] = None,
3977 nominvalue: Optional[bool] = None,
3978 nomaxvalue: Optional[bool] = None,
3979 cycle: Optional[bool] = None,
3980 cache: Optional[int] = None,
3981 order: Optional[bool] = None,
3982 **dialect_kw: Any,
3983 ) -> None:
3984 """Construct a :class:`.IdentityOptions` object.
3986 See the :class:`.Sequence` documentation for a complete description
3987 of the parameters.
3989 :param start: the starting index of the sequence.
3990 :param increment: the increment value of the sequence.
3991 :param minvalue: the minimum value of the sequence.
3992 :param maxvalue: the maximum value of the sequence.
3993 :param nominvalue: no minimum value of the sequence.
3994 :param nomaxvalue: no maximum value of the sequence.
3995 :param cycle: allows the sequence to wrap around when the maxvalue
3996 or minvalue has been reached.
3997 :param cache: optional integer value; number of future values in the
3998 sequence which are calculated in advance.
3999 :param order: optional boolean value; if ``True``, renders the
4000 ORDER keyword.
4002 .. deprecated:: 2.1 Use ``oracle_order`` instead.
4004 """
4005 self.start = start
4006 self.increment = increment
4007 self.minvalue = minvalue
4008 self.maxvalue = maxvalue
4009 self.nominvalue = nominvalue
4010 self.nomaxvalue = nomaxvalue
4011 self.cycle = cycle
4012 self.cache = cache
4013 if order is not None:
4014 if "oracle_order" in dialect_kw:
4015 raise exc.ArgumentError(
4016 "Cannot specify both 'order' and 'oracle_order'. "
4017 "Please use only 'oracle_order'."
4018 )
4019 dialect_kw["oracle_order"] = order
4020 self._validate_dialect_kwargs(dialect_kw)
4022 @property
4023 def _increment_is_negative(self) -> bool:
4024 return self.increment is not None and self.increment < 0
4026 @property
4027 def order(self) -> Optional[bool]:
4028 """Alias of the ``dialect_kwargs`` ``'oracle_order'``.
4030 .. deprecated:: 2.1 The 'order' attribute is deprecated.
4031 """
4032 value: Optional[bool] = self.dialect_kwargs.get("oracle_order")
4033 return value
4035 def _as_dict(self) -> Dict[str, Any]:
4036 return {
4037 k: v
4038 for k, v in {
4039 "start": self.start,
4040 "increment": self.increment,
4041 "minvalue": self.minvalue,
4042 "maxvalue": self.maxvalue,
4043 "nominvalue": self.nominvalue,
4044 "nomaxvalue": self.nomaxvalue,
4045 "cycle": self.cycle,
4046 "cache": self.cache,
4047 }.items()
4048 if v != None
4049 }
4052class Sequence(HasSchemaAttr, IdentityOptions, DefaultGenerator):
4053 """Represents a named database sequence.
4055 The :class:`.Sequence` object represents the name and configurational
4056 parameters of a database sequence. It also represents
4057 a construct that can be "executed" by a SQLAlchemy :class:`_engine.Engine`
4058 or :class:`_engine.Connection`,
4059 rendering the appropriate "next value" function
4060 for the target database and returning a result.
4062 The :class:`.Sequence` is typically associated with a primary key column::
4064 some_table = Table(
4065 "some_table",
4066 metadata,
4067 Column(
4068 "id",
4069 Integer,
4070 Sequence("some_table_seq", start=1),
4071 primary_key=True,
4072 ),
4073 )
4075 When CREATE TABLE is emitted for the above :class:`_schema.Table`, if the
4076 target platform supports sequences, a CREATE SEQUENCE statement will
4077 be emitted as well. For platforms that don't support sequences,
4078 the :class:`.Sequence` construct is ignored.
4080 .. seealso::
4082 :ref:`defaults_sequences`
4084 :class:`.CreateSequence`
4086 :class:`.DropSequence`
4088 """
4090 __visit_name__ = "sequence"
4092 is_sequence = True
4094 column: Optional[Column[Any]]
4095 data_type: Optional[TypeEngine[int]]
4097 metadata: Optional[MetaData]
4099 @util.deprecated_params(
4100 order=(
4101 "2.1",
4102 "This parameter is supported only by Oracle Database, "
4103 "use ``oracle_order`` instead.",
4104 )
4105 )
4106 def __init__(
4107 self,
4108 name: str,
4109 start: Optional[int] = None,
4110 increment: Optional[int] = None,
4111 minvalue: Optional[int] = None,
4112 maxvalue: Optional[int] = None,
4113 nominvalue: Optional[bool] = None,
4114 nomaxvalue: Optional[bool] = None,
4115 cycle: Optional[bool] = None,
4116 schema: Optional[Union[str, Literal[SchemaConst.BLANK_SCHEMA]]] = None,
4117 cache: Optional[int] = None,
4118 order: Optional[bool] = None,
4119 data_type: Optional[_TypeEngineArgument[int]] = None,
4120 optional: bool = False,
4121 quote: Optional[bool] = None,
4122 metadata: Optional[MetaData] = None,
4123 quote_schema: Optional[bool] = None,
4124 for_update: bool = False,
4125 **dialect_kw: Any,
4126 ) -> None:
4127 """Construct a :class:`.Sequence` object.
4129 :param name: the name of the sequence.
4131 :param start: the starting index of the sequence. This value is
4132 used when the CREATE SEQUENCE command is emitted to the database
4133 as the value of the "START WITH" clause. If ``None``, the
4134 clause is omitted, which on most platforms indicates a starting
4135 value of 1.
4137 .. versionchanged:: 2.0 The :paramref:`.Sequence.start` parameter
4138 is required in order to have DDL emit "START WITH". This is a
4139 reversal of a change made in version 1.4 which would implicitly
4140 render "START WITH 1" if the :paramref:`.Sequence.start` were
4141 not included. See :ref:`change_7211` for more detail.
4143 :param increment: the increment value of the sequence. This
4144 value is used when the CREATE SEQUENCE command is emitted to
4145 the database as the value of the "INCREMENT BY" clause. If ``None``,
4146 the clause is omitted, which on most platforms indicates an
4147 increment of 1.
4148 :param minvalue: the minimum value of the sequence. This
4149 value is used when the CREATE SEQUENCE command is emitted to
4150 the database as the value of the "MINVALUE" clause. If ``None``,
4151 the clause is omitted, which on most platforms indicates a
4152 minvalue of 1 and -2^63-1 for ascending and descending sequences,
4153 respectively.
4155 :param maxvalue: the maximum value of the sequence. This
4156 value is used when the CREATE SEQUENCE command is emitted to
4157 the database as the value of the "MAXVALUE" clause. If ``None``,
4158 the clause is omitted, which on most platforms indicates a
4159 maxvalue of 2^63-1 and -1 for ascending and descending sequences,
4160 respectively.
4162 :param nominvalue: no minimum value of the sequence. This
4163 value is used when the CREATE SEQUENCE command is emitted to
4164 the database as the value of the "NO MINVALUE" clause. If ``None``,
4165 the clause is omitted, which on most platforms indicates a
4166 minvalue of 1 and -2^63-1 for ascending and descending sequences,
4167 respectively.
4169 :param nomaxvalue: no maximum value of the sequence. This
4170 value is used when the CREATE SEQUENCE command is emitted to
4171 the database as the value of the "NO MAXVALUE" clause. If ``None``,
4172 the clause is omitted, which on most platforms indicates a
4173 maxvalue of 2^63-1 and -1 for ascending and descending sequences,
4174 respectively.
4176 :param cycle: allows the sequence to wrap around when the maxvalue
4177 or minvalue has been reached by an ascending or descending sequence
4178 respectively. This value is used when the CREATE SEQUENCE command
4179 is emitted to the database as the "CYCLE" clause. If the limit is
4180 reached, the next number generated will be the minvalue or maxvalue,
4181 respectively. If cycle=False (the default) any calls to nextval
4182 after the sequence has reached its maximum value will return an
4183 error.
4185 :param schema: optional schema name for the sequence, if located
4186 in a schema other than the default. The rules for selecting the
4187 schema name when a :class:`_schema.MetaData`
4188 is also present are the same
4189 as that of :paramref:`_schema.Table.schema`.
4191 :param cache: optional integer value; number of future values in the
4192 sequence which are calculated in advance. Renders the CACHE keyword
4193 understood by Oracle Database and PostgreSQL.
4195 :param order: optional boolean value; if ``True``, renders the
4196 ORDER keyword, understood by Oracle Database, indicating the sequence
4197 is definitively ordered. May be necessary to provide deterministic
4198 ordering using Oracle RAC.
4200 :param data_type: The type to be returned by the sequence, for
4201 dialects that allow us to choose between INTEGER, BIGINT, etc.
4202 (e.g., mssql).
4204 .. versionadded:: 1.4.0
4206 :param optional: boolean value, when ``True``, indicates that this
4207 :class:`.Sequence` object only needs to be explicitly generated
4208 on backends that don't provide another way to generate primary
4209 key identifiers. Currently, it essentially means, "don't create
4210 this sequence on the PostgreSQL backend, where the SERIAL keyword
4211 creates a sequence for us automatically".
4212 :param quote: boolean value, when ``True`` or ``False``, explicitly
4213 forces quoting of the :paramref:`_schema.Sequence.name` on or off.
4214 When left at its default of ``None``, normal quoting rules based
4215 on casing and reserved words take place.
4216 :param quote_schema: Set the quoting preferences for the ``schema``
4217 name.
4219 :param metadata: optional :class:`_schema.MetaData` object which this
4220 :class:`.Sequence` will be associated with. A :class:`.Sequence`
4221 that is associated with a :class:`_schema.MetaData`
4222 gains the following
4223 capabilities:
4225 * The :class:`.Sequence` will inherit the
4226 :paramref:`_schema.MetaData.schema`
4227 parameter specified to the target :class:`_schema.MetaData`, which
4228 affects the production of CREATE / DROP DDL, if any.
4230 * The :meth:`.Sequence.create` and :meth:`.Sequence.drop` methods
4231 automatically use the engine bound to the :class:`_schema.MetaData`
4232 object, if any.
4234 * The :meth:`_schema.MetaData.create_all` and
4235 :meth:`_schema.MetaData.drop_all`
4236 methods will emit CREATE / DROP for this :class:`.Sequence`,
4237 even if the :class:`.Sequence` is not associated with any
4238 :class:`_schema.Table` / :class:`_schema.Column`
4239 that's a member of this
4240 :class:`_schema.MetaData`.
4242 The above behaviors can only occur if the :class:`.Sequence` is
4243 explicitly associated with the :class:`_schema.MetaData`
4244 via this parameter.
4246 .. seealso::
4248 :ref:`sequence_metadata` - full discussion of the
4249 :paramref:`.Sequence.metadata` parameter.
4251 :param for_update: Indicates this :class:`.Sequence`, when associated
4252 with a :class:`_schema.Column`,
4253 should be invoked for UPDATE statements
4254 on that column's table, rather than for INSERT statements, when
4255 no value is otherwise present for that column in the statement.
4257 """
4258 DefaultGenerator.__init__(self, for_update=for_update)
4259 IdentityOptions.__init__(
4260 self,
4261 start=start,
4262 increment=increment,
4263 minvalue=minvalue,
4264 maxvalue=maxvalue,
4265 nominvalue=nominvalue,
4266 nomaxvalue=nomaxvalue,
4267 cycle=cycle,
4268 cache=cache,
4269 order=order,
4270 **dialect_kw,
4271 )
4272 self.column = None
4273 self.name = quoted_name(name, quote)
4274 self.optional = optional
4275 if schema is BLANK_SCHEMA:
4276 self.schema = schema = None
4277 elif metadata is not None and schema is None and metadata.schema:
4278 self.schema = schema = metadata.schema
4279 else:
4280 self.schema = quoted_name.construct(schema, quote_schema)
4281 self._key = _get_table_key(name, schema)
4282 if data_type is not None:
4283 self.data_type = to_instance(data_type)
4284 else:
4285 self.data_type = None
4287 if metadata:
4288 self._set_metadata(metadata)
4289 else:
4290 self.metadata = None
4292 @util.preload_module("sqlalchemy.sql.functions")
4293 def next_value(self) -> Function[int]:
4294 """Return a :class:`.next_value` function element
4295 which will render the appropriate increment function
4296 for this :class:`.Sequence` within any SQL expression.
4298 """
4299 return util.preloaded.sql_functions.func.next_value(self)
4301 def _copy(self) -> Sequence:
4302 return Sequence(
4303 name=self.name,
4304 schema=self.schema,
4305 data_type=self.data_type,
4306 optional=self.optional,
4307 metadata=self.metadata,
4308 for_update=self.for_update,
4309 **self._as_dict(),
4310 **self.dialect_kwargs,
4311 )
4313 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4314 assert isinstance(parent, Column)
4315 super()._set_parent(parent, **kw)
4316 parent._on_table_attach(self._set_table)
4318 def _set_table(self, column: Column[Any], table: Table) -> None:
4319 self._set_metadata(table.metadata)
4321 def _set_metadata(self, metadata: MetaData) -> None:
4322 self.metadata = metadata
4323 self.metadata._register_object(self)
4324 metadata._sequences[self._key] = self
4326 def create(
4327 self,
4328 bind: _CreateDropBind,
4329 checkfirst: Union[bool, CheckFirst] = CheckFirst.SEQUENCES,
4330 ) -> None:
4331 """Creates this sequence in the database."""
4333 bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
4335 def drop(
4336 self,
4337 bind: _CreateDropBind,
4338 checkfirst: Union[bool, CheckFirst] = CheckFirst.SEQUENCES,
4339 ) -> None:
4340 """Drops this sequence from the database."""
4342 bind._run_ddl_visitor(ddl.SchemaDropper, self, checkfirst=checkfirst)
4344 def _not_a_column_expr(self) -> NoReturn:
4345 raise exc.InvalidRequestError(
4346 f"This {self.__class__.__name__} cannot be used directly "
4347 "as a column expression. Use func.next_value(sequence) "
4348 "to produce a 'next value' function that's usable "
4349 "as a column element."
4350 )
4353@inspection._self_inspects
4354class FetchedValue(SchemaEventTarget):
4355 """A marker for a transparent database-side default.
4357 Use :class:`.FetchedValue` when the database is configured
4358 to provide some automatic default for a column.
4360 E.g.::
4362 Column("foo", Integer, FetchedValue())
4364 Would indicate that some trigger or default generator
4365 will create a new value for the ``foo`` column during an
4366 INSERT.
4368 .. seealso::
4370 :ref:`triggered_columns`
4372 """
4374 is_server_default = True
4375 reflected = False
4376 has_argument = False
4377 is_clause_element = False
4378 is_identity = False
4379 _is_monotonic_fn = False
4381 column: Optional[Column[Any]]
4383 def __init__(self, for_update: bool = False) -> None:
4384 self.for_update = for_update
4386 def _as_for_update(self, for_update: bool) -> FetchedValue:
4387 if for_update == self.for_update:
4388 return self
4389 else:
4390 return self._clone(for_update)
4392 def _copy(self) -> FetchedValue:
4393 return FetchedValue(self.for_update)
4395 def _clone(self, for_update: bool) -> Self:
4396 n = self.__class__.__new__(self.__class__)
4397 n.__dict__.update(self.__dict__)
4398 n.__dict__.pop("column", None)
4399 n.for_update = for_update
4400 return n
4402 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4403 column = parent
4404 assert isinstance(column, Column)
4405 self.column = column
4406 if self.for_update:
4407 self.column.server_onupdate = self
4408 else:
4409 self.column.server_default = self
4411 def __repr__(self) -> str:
4412 return util.generic_repr(self)
4415class DefaultClause(FetchedValue):
4416 """A DDL-specified DEFAULT column value.
4418 :class:`.DefaultClause` is a :class:`.FetchedValue`
4419 that also generates a "DEFAULT" clause when
4420 "CREATE TABLE" is emitted.
4422 :class:`.DefaultClause` is generated automatically
4423 whenever the ``server_default``, ``server_onupdate`` arguments of
4424 :class:`_schema.Column` are used. A :class:`.DefaultClause`
4425 can be passed positionally as well.
4427 For example, the following::
4429 Column("foo", Integer, server_default="50")
4431 Is equivalent to::
4433 Column("foo", Integer, DefaultClause("50"))
4435 """
4437 has_argument = True
4439 def __init__(
4440 self,
4441 arg: Union[str, ClauseElement, TextClause],
4442 for_update: bool = False,
4443 _reflected: bool = False,
4444 ) -> None:
4445 util.assert_arg_type(arg, (str, ClauseElement, TextClause), "arg")
4446 super().__init__(for_update)
4447 self.arg = arg
4448 self.reflected = _reflected
4450 @util.memoized_property
4451 @util.preload_module("sqlalchemy.sql.functions")
4452 def _is_monotonic_fn(self) -> bool:
4453 functions = util.preloaded.sql_functions
4454 return (
4455 isinstance(self.arg, functions.FunctionElement)
4456 and self.arg.monotonic
4457 )
4459 def _copy(self) -> DefaultClause:
4460 return DefaultClause(
4461 arg=self.arg, for_update=self.for_update, _reflected=self.reflected
4462 )
4464 def __repr__(self) -> str:
4465 return "DefaultClause(%r, for_update=%r)" % (self.arg, self.for_update)
4468class Constraint(DialectKWArgs, HasConditionalDDL, SchemaItem):
4469 """A table-level SQL constraint.
4471 :class:`_schema.Constraint` serves as the base class for the series of
4472 constraint objects that can be associated with :class:`_schema.Table`
4473 objects, including :class:`_schema.PrimaryKeyConstraint`,
4474 :class:`_schema.ForeignKeyConstraint`
4475 :class:`_schema.UniqueConstraint`, and
4476 :class:`_schema.CheckConstraint`.
4478 """
4480 __visit_name__ = "constraint"
4482 _creation_order: int
4483 _column_flag: bool
4485 def __init__(
4486 self,
4487 name: _ConstraintNameArgument = None,
4488 deferrable: Optional[bool] = None,
4489 initially: Optional[str] = None,
4490 info: Optional[_InfoType] = None,
4491 comment: Optional[str] = None,
4492 _create_rule: Optional[Any] = None,
4493 _type_bound: bool = False,
4494 **dialect_kw: Any,
4495 ) -> None:
4496 r"""Create a SQL constraint.
4498 :param name:
4499 Optional, the in-database name of this ``Constraint``.
4501 :param deferrable:
4502 Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
4503 issuing DDL for this constraint.
4505 :param initially:
4506 Optional string. If set, emit INITIALLY <value> when issuing DDL
4507 for this constraint.
4509 :param info: Optional data dictionary which will be populated into the
4510 :attr:`.SchemaItem.info` attribute of this object.
4512 :param comment: Optional string that will render an SQL comment on
4513 foreign key constraint creation.
4515 .. versionadded:: 2.0
4517 :param \**dialect_kw: Additional keyword arguments are dialect
4518 specific, and passed in the form ``<dialectname>_<argname>``. See
4519 the documentation regarding an individual dialect at
4520 :ref:`dialect_toplevel` for detail on documented arguments.
4522 :param _create_rule:
4523 used internally by some datatypes that also create constraints.
4525 :param _type_bound:
4526 used internally to indicate that this constraint is associated with
4527 a specific datatype.
4529 """
4531 self.name = name
4532 self.deferrable = deferrable
4533 self.initially = initially
4534 if info:
4535 self.info = info
4536 self._create_rule = _create_rule
4537 self._type_bound = _type_bound
4538 util.set_creation_order(self)
4539 self._validate_dialect_kwargs(dialect_kw)
4540 self.comment = comment
4542 def _should_create_for_compiler(
4543 self, compiler: DDLCompiler, **kw: Any
4544 ) -> bool:
4545 if self._create_rule is not None and not self._create_rule(compiler):
4546 return False
4547 elif self._ddl_if is not None:
4548 return self._ddl_if._should_execute(
4549 ddl.CreateConstraint(self), self, None, compiler=compiler, **kw
4550 )
4551 else:
4552 return True
4554 @property
4555 def table(self) -> Table:
4556 try:
4557 if isinstance(self.parent, Table):
4558 return self.parent
4559 except AttributeError:
4560 pass
4561 raise exc.InvalidRequestError(
4562 "This constraint is not bound to a table. Did you "
4563 "mean to call table.append_constraint(constraint) ?"
4564 )
4566 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4567 assert isinstance(parent, (Table, Column))
4568 self.parent = parent
4569 parent.constraints.add(self)
4571 @util.deprecated(
4572 "1.4",
4573 "The :meth:`_schema.Constraint.copy` method is deprecated "
4574 "and will be removed in a future release.",
4575 )
4576 def copy(self, **kw: Any) -> Self:
4577 return self._copy(**kw)
4579 def _copy(self, **kw: Any) -> Self:
4580 raise NotImplementedError()
4583class ColumnCollectionMixin:
4584 """A :class:`_expression.ColumnCollection` of :class:`_schema.Column`
4585 objects.
4587 This collection represents the columns which are referred to by
4588 this object.
4590 """
4592 _columns: DedupeColumnCollection[Column[Any]]
4594 _allow_multiple_tables = False
4596 _pending_colargs: List[Optional[Union[str, Column[Any]]]]
4598 if TYPE_CHECKING:
4600 def _set_parent_with_dispatch(
4601 self, parent: SchemaEventTarget, **kw: Any
4602 ) -> None: ...
4604 def __init__(
4605 self,
4606 *columns: _DDLColumnArgument,
4607 _autoattach: bool = True,
4608 _column_flag: bool = False,
4609 _gather_expressions: Optional[
4610 List[Union[str, ColumnElement[Any]]]
4611 ] = None,
4612 ) -> None:
4613 self._column_flag = _column_flag
4614 self._columns = DedupeColumnCollection()
4616 processed_expressions: Optional[
4617 List[Union[ColumnElement[Any], str]]
4618 ] = _gather_expressions
4620 if processed_expressions is not None:
4622 # this is expected to be an empty list
4623 assert not processed_expressions
4625 self._pending_colargs = []
4626 for (
4627 expr,
4628 _,
4629 _,
4630 add_element,
4631 ) in coercions.expect_col_expression_collection(
4632 roles.DDLConstraintColumnRole, columns
4633 ):
4634 self._pending_colargs.append(add_element)
4635 processed_expressions.append(expr)
4636 else:
4637 self._pending_colargs = [
4638 coercions.expect(roles.DDLConstraintColumnRole, column)
4639 for column in columns
4640 ]
4642 if _autoattach and self._pending_colargs:
4643 self._check_attach()
4645 def _check_attach(self, evt: bool = False) -> None:
4646 col_objs = [c for c in self._pending_colargs if isinstance(c, Column)]
4648 cols_w_table = [c for c in col_objs if isinstance(c.table, Table)]
4650 cols_wo_table = set(col_objs).difference(cols_w_table)
4651 if cols_wo_table:
4652 # feature #3341 - place event listeners for Column objects
4653 # such that when all those cols are attached, we autoattach.
4654 assert not evt, "Should not reach here on event call"
4656 # issue #3411 - don't do the per-column auto-attach if some of the
4657 # columns are specified as strings.
4658 has_string_cols = {
4659 c for c in self._pending_colargs if c is not None
4660 }.difference(col_objs)
4661 if not has_string_cols:
4663 def _col_attached(column: Column[Any], table: Table) -> None:
4664 # this isinstance() corresponds with the
4665 # isinstance() above; only want to count Table-bound
4666 # columns
4667 if isinstance(table, Table):
4668 cols_wo_table.discard(column)
4669 if not cols_wo_table:
4670 self._check_attach(evt=True)
4672 self._cols_wo_table = cols_wo_table
4673 for col in cols_wo_table:
4674 col._on_table_attach(_col_attached)
4675 return
4677 columns = cols_w_table
4679 tables = {c.table for c in columns}
4680 if len(tables) == 1:
4681 self._set_parent_with_dispatch(tables.pop())
4682 elif len(tables) > 1 and not self._allow_multiple_tables:
4683 table = columns[0].table
4684 others = [c for c in columns[1:] if c.table is not table]
4685 if others:
4686 # black could not format this inline
4687 other_str = ", ".join("'%s'" % c for c in others)
4688 raise exc.ArgumentError(
4689 f"Column(s) {other_str} "
4690 f"are not part of table '{table.description}'."
4691 )
4693 @util.ro_memoized_property
4694 def columns(self) -> ReadOnlyColumnCollection[str, Column[Any]]:
4695 return self._columns.as_readonly()
4697 @util.ro_memoized_property
4698 def c(self) -> ReadOnlyColumnCollection[str, Column[Any]]:
4699 return self._columns.as_readonly()
4701 def _col_expressions(
4702 self, parent: Union[Table, Column[Any]]
4703 ) -> List[Optional[Column[Any]]]:
4704 if isinstance(parent, Column):
4705 result: List[Optional[Column[Any]]] = [
4706 c for c in self._pending_colargs if isinstance(c, Column)
4707 ]
4708 assert len(result) == len(self._pending_colargs)
4709 return result
4710 else:
4711 try:
4712 return [
4713 parent.c[col] if isinstance(col, str) else col
4714 for col in self._pending_colargs
4715 ]
4716 except KeyError as ke:
4717 raise exc.ConstraintColumnNotFoundError(
4718 f"Can't create {self.__class__.__name__} "
4719 f"on table '{parent.description}': no column "
4720 f"named '{ke.args[0]}' is present."
4721 ) from ke
4723 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4724 assert isinstance(parent, (Table, Column))
4726 for col in self._col_expressions(parent):
4727 if col is not None:
4728 self._columns.add(col)
4731class ColumnCollectionConstraint(ColumnCollectionMixin, Constraint):
4732 """A constraint that proxies a ColumnCollection."""
4734 def __init__(
4735 self,
4736 *columns: _DDLColumnArgument,
4737 name: _ConstraintNameArgument = None,
4738 deferrable: Optional[bool] = None,
4739 initially: Optional[str] = None,
4740 info: Optional[_InfoType] = None,
4741 _autoattach: bool = True,
4742 _column_flag: bool = False,
4743 _gather_expressions: Optional[List[_DDLColumnArgument]] = None,
4744 **dialect_kw: Any,
4745 ) -> None:
4746 r"""
4747 :param \*columns:
4748 A sequence of column names or Column objects.
4750 :param name:
4751 Optional, the in-database name of this constraint.
4753 :param deferrable:
4754 Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
4755 issuing DDL for this constraint.
4757 :param initially:
4758 Optional string. If set, emit INITIALLY <value> when issuing DDL
4759 for this constraint.
4761 :param \**dialect_kw: other keyword arguments including
4762 dialect-specific arguments are propagated to the :class:`.Constraint`
4763 superclass.
4765 """
4766 Constraint.__init__(
4767 self,
4768 name=name,
4769 deferrable=deferrable,
4770 initially=initially,
4771 info=info,
4772 **dialect_kw,
4773 )
4774 ColumnCollectionMixin.__init__(
4775 self, *columns, _autoattach=_autoattach, _column_flag=_column_flag
4776 )
4778 columns: ReadOnlyColumnCollection[str, Column[Any]]
4779 """A :class:`_expression.ColumnCollection` representing the set of columns
4780 for this constraint.
4782 """
4784 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4785 assert isinstance(parent, (Column, Table))
4786 Constraint._set_parent(self, parent)
4787 ColumnCollectionMixin._set_parent(self, parent)
4789 def __contains__(self, x: Any) -> bool:
4790 return x in self._columns
4792 @util.deprecated(
4793 "1.4",
4794 "The :meth:`_schema.ColumnCollectionConstraint.copy` method "
4795 "is deprecated and will be removed in a future release.",
4796 )
4797 def copy(
4798 self,
4799 *,
4800 target_table: Optional[Table] = None,
4801 **kw: Any,
4802 ) -> ColumnCollectionConstraint:
4803 return self._copy(target_table=target_table, **kw)
4805 def _copy(
4806 self,
4807 *,
4808 target_table: Optional[Table] = None,
4809 **kw: Any,
4810 ) -> ColumnCollectionConstraint:
4811 # ticket #5276
4812 constraint_kwargs = {}
4813 for dialect_name in self.dialect_options:
4814 dialect_options = self.dialect_options[dialect_name]._non_defaults
4815 for (
4816 dialect_option_key,
4817 dialect_option_value,
4818 ) in dialect_options.items():
4819 constraint_kwargs[dialect_name + "_" + dialect_option_key] = (
4820 dialect_option_value
4821 )
4823 assert isinstance(self.parent, Table)
4824 c = self.__class__(
4825 name=self.name,
4826 deferrable=self.deferrable,
4827 initially=self.initially,
4828 *[
4829 _copy_expression(expr, self.parent, target_table)
4830 for expr in self._columns
4831 ],
4832 comment=self.comment,
4833 **constraint_kwargs,
4834 )
4835 return self._schema_item_copy(c)
4837 def contains_column(self, col: Column[Any]) -> bool:
4838 """Return True if this constraint contains the given column.
4840 Note that this object also contains an attribute ``.columns``
4841 which is a :class:`_expression.ColumnCollection` of
4842 :class:`_schema.Column` objects.
4844 """
4846 return self._columns.contains_column(col)
4848 def __iter__(self) -> Iterator[Column[Any]]:
4849 return iter(self._columns)
4851 def __len__(self) -> int:
4852 return len(self._columns)
4855class CheckConstraint(ColumnCollectionConstraint):
4856 """A table- or column-level CHECK constraint.
4858 Can be included in the definition of a Table or Column.
4859 """
4861 _allow_multiple_tables = True
4863 __visit_name__ = "table_or_column_check_constraint"
4865 @_document_text_coercion(
4866 "sqltext",
4867 ":class:`.CheckConstraint`",
4868 ":paramref:`.CheckConstraint.sqltext`",
4869 )
4870 def __init__(
4871 self,
4872 sqltext: _TextCoercedExpressionArgument[Any],
4873 name: _ConstraintNameArgument = None,
4874 deferrable: Optional[bool] = None,
4875 initially: Optional[str] = None,
4876 table: Optional[Table] = None,
4877 info: Optional[_InfoType] = None,
4878 _create_rule: Optional[Any] = None,
4879 _autoattach: bool = True,
4880 _type_bound: bool = False,
4881 **dialect_kw: Any,
4882 ) -> None:
4883 r"""Construct a CHECK constraint.
4885 :param sqltext:
4886 A string containing the constraint definition, which will be used
4887 verbatim, or a SQL expression construct. If given as a string,
4888 the object is converted to a :func:`_expression.text` object.
4889 If the textual
4890 string includes a colon character, escape this using a backslash::
4892 CheckConstraint(r"foo ~ E'a(?\:b|c)d")
4894 :param name:
4895 Optional, the in-database name of the constraint.
4897 :param deferrable:
4898 Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
4899 issuing DDL for this constraint.
4901 :param initially:
4902 Optional string. If set, emit INITIALLY <value> when issuing DDL
4903 for this constraint.
4905 :param info: Optional data dictionary which will be populated into the
4906 :attr:`.SchemaItem.info` attribute of this object.
4908 """
4910 self.sqltext = coercions.expect(roles.DDLExpressionRole, sqltext)
4911 columns: List[Column[Any]] = []
4912 visitors.traverse(self.sqltext, {}, {"column": columns.append})
4914 super().__init__(
4915 name=name,
4916 deferrable=deferrable,
4917 initially=initially,
4918 _create_rule=_create_rule,
4919 info=info,
4920 _type_bound=_type_bound,
4921 _autoattach=_autoattach,
4922 *columns,
4923 **dialect_kw,
4924 )
4925 if table is not None:
4926 self._set_parent_with_dispatch(table)
4928 @property
4929 def is_column_level(self) -> bool:
4930 return not isinstance(self.parent, Table)
4932 @util.deprecated(
4933 "1.4",
4934 "The :meth:`_schema.CheckConstraint.copy` method is deprecated "
4935 "and will be removed in a future release.",
4936 )
4937 def copy(
4938 self, *, target_table: Optional[Table] = None, **kw: Any
4939 ) -> CheckConstraint:
4940 return self._copy(target_table=target_table, **kw)
4942 def _copy(
4943 self, *, target_table: Optional[Table] = None, **kw: Any
4944 ) -> CheckConstraint:
4945 if target_table is not None:
4946 # note that target_table is None for the copy process of
4947 # a column-bound CheckConstraint, so this path is not reached
4948 # in that case.
4949 sqltext = _copy_expression(self.sqltext, self.table, target_table)
4950 else:
4951 sqltext = self.sqltext
4952 c = CheckConstraint(
4953 sqltext,
4954 name=self.name,
4955 initially=self.initially,
4956 deferrable=self.deferrable,
4957 _create_rule=self._create_rule,
4958 table=target_table,
4959 comment=self.comment,
4960 _autoattach=False,
4961 _type_bound=self._type_bound,
4962 )
4963 return self._schema_item_copy(c)
4966class ForeignKeyConstraint(ColumnCollectionConstraint):
4967 """A table-level FOREIGN KEY constraint.
4969 Defines a single column or composite FOREIGN KEY ... REFERENCES
4970 constraint. For a no-frills, single column foreign key, adding a
4971 :class:`_schema.ForeignKey` to the definition of a :class:`_schema.Column`
4972 is a
4973 shorthand equivalent for an unnamed, single column
4974 :class:`_schema.ForeignKeyConstraint`.
4976 Examples of foreign key configuration are in :ref:`metadata_foreignkeys`.
4978 """
4980 __visit_name__ = "foreign_key_constraint"
4982 def __init__(
4983 self,
4984 columns: _typing_Sequence[_DDLColumnArgument],
4985 refcolumns: _typing_Sequence[_DDLColumnReferenceArgument],
4986 name: _ConstraintNameArgument = None,
4987 onupdate: Optional[str] = None,
4988 ondelete: Optional[str] = None,
4989 deferrable: Optional[bool] = None,
4990 initially: Optional[str] = None,
4991 use_alter: bool = False,
4992 link_to_name: bool = False,
4993 match: Optional[str] = None,
4994 table: Optional[Table] = None,
4995 info: Optional[_InfoType] = None,
4996 comment: Optional[str] = None,
4997 **dialect_kw: Any,
4998 ) -> None:
4999 r"""Construct a composite-capable FOREIGN KEY.
5001 :param columns: A sequence of local column names. The named columns
5002 must be defined and present in the parent Table. The names should
5003 match the ``key`` given to each column (defaults to the name) unless
5004 ``link_to_name`` is True.
5006 :param refcolumns: A sequence of foreign column names or Column
5007 objects. The columns must all be located within the same Table.
5009 :param name: Optional, the in-database name of the key.
5011 :param onupdate: Optional string. If set, emit ON UPDATE <value> when
5012 issuing DDL for this constraint. Typical values include CASCADE,
5013 DELETE and RESTRICT.
5015 .. seealso::
5017 :ref:`on_update_on_delete`
5019 :param ondelete: Optional string. If set, emit ON DELETE <value> when
5020 issuing DDL for this constraint. Typical values include CASCADE,
5021 SET NULL and RESTRICT. Some dialects may allow for additional
5022 syntaxes.
5024 .. seealso::
5026 :ref:`on_update_on_delete`
5028 :param deferrable: Optional bool. If set, emit DEFERRABLE or NOT
5029 DEFERRABLE when issuing DDL for this constraint.
5031 :param initially: Optional string. If set, emit INITIALLY <value> when
5032 issuing DDL for this constraint.
5034 :param link_to_name: if True, the string name given in ``column`` is
5035 the rendered name of the referenced column, not its locally assigned
5036 ``key``.
5038 :param use_alter: If True, do not emit the DDL for this constraint as
5039 part of the CREATE TABLE definition. Instead, generate it via an
5040 ALTER TABLE statement issued after the full collection of tables
5041 have been created, and drop it via an ALTER TABLE statement before
5042 the full collection of tables are dropped.
5044 The use of :paramref:`_schema.ForeignKeyConstraint.use_alter` is
5045 particularly geared towards the case where two or more tables
5046 are established within a mutually-dependent foreign key constraint
5047 relationship; however, the :meth:`_schema.MetaData.create_all` and
5048 :meth:`_schema.MetaData.drop_all`
5049 methods will perform this resolution
5050 automatically, so the flag is normally not needed.
5052 .. seealso::
5054 :ref:`use_alter`
5056 :param match: Optional string. If set, emit MATCH <value> when issuing
5057 DDL for this constraint. Typical values include SIMPLE, PARTIAL
5058 and FULL.
5060 :param info: Optional data dictionary which will be populated into the
5061 :attr:`.SchemaItem.info` attribute of this object.
5063 :param comment: Optional string that will render an SQL comment on
5064 foreign key constraint creation.
5066 .. versionadded:: 2.0
5068 :param \**dialect_kw: Additional keyword arguments are dialect
5069 specific, and passed in the form ``<dialectname>_<argname>``. See
5070 the documentation regarding an individual dialect at
5071 :ref:`dialect_toplevel` for detail on documented arguments.
5073 """
5075 Constraint.__init__(
5076 self,
5077 name=name,
5078 deferrable=deferrable,
5079 initially=initially,
5080 info=info,
5081 comment=comment,
5082 **dialect_kw,
5083 )
5084 self.onupdate = onupdate
5085 self.ondelete = ondelete
5086 self.link_to_name = link_to_name
5087 self.use_alter = use_alter
5088 self.match = match
5090 if len(set(columns)) != len(refcolumns):
5091 if len(set(columns)) != len(columns):
5092 # e.g. FOREIGN KEY (a, a) REFERENCES r (b, c)
5093 raise exc.ArgumentError(
5094 "ForeignKeyConstraint with duplicate source column "
5095 "references are not supported."
5096 )
5097 else:
5098 # e.g. FOREIGN KEY (a) REFERENCES r (b, c)
5099 # paraphrasing
5100 # https://www.postgresql.org/docs/current/static/ddl-constraints.html
5101 raise exc.ArgumentError(
5102 "ForeignKeyConstraint number "
5103 "of constrained columns must match the number of "
5104 "referenced columns."
5105 )
5107 # standalone ForeignKeyConstraint - create
5108 # associated ForeignKey objects which will be applied to hosted
5109 # Column objects (in col.foreign_keys), either now or when attached
5110 # to the Table for string-specified names
5111 self.elements = [
5112 ForeignKey(
5113 refcol,
5114 _constraint=self,
5115 name=self.name,
5116 onupdate=self.onupdate,
5117 ondelete=self.ondelete,
5118 use_alter=self.use_alter,
5119 link_to_name=self.link_to_name,
5120 match=self.match,
5121 deferrable=self.deferrable,
5122 initially=self.initially,
5123 **self.dialect_kwargs,
5124 )
5125 for refcol in refcolumns
5126 ]
5128 ColumnCollectionMixin.__init__(self, *columns)
5129 if table is not None:
5130 if hasattr(self, "parent"):
5131 assert table is self.parent
5132 self._set_parent_with_dispatch(table)
5134 def _append_element(self, column: Column[Any], fk: ForeignKey) -> None:
5135 self._columns.add(column)
5136 self.elements.append(fk)
5138 columns: ReadOnlyColumnCollection[str, Column[Any]]
5139 """A :class:`_expression.ColumnCollection` representing the set of columns
5140 for this constraint.
5142 """
5144 elements: List[ForeignKey]
5145 """A sequence of :class:`_schema.ForeignKey` objects.
5147 Each :class:`_schema.ForeignKey`
5148 represents a single referring column/referred
5149 column pair.
5151 This collection is intended to be read-only.
5153 """
5155 @property
5156 def _elements(self) -> util.OrderedDict[str, ForeignKey]:
5157 # legacy - provide a dictionary view of (column_key, fk)
5158 return util.OrderedDict(zip(self.column_keys, self.elements))
5160 @property
5161 def _referred_schema(self) -> Optional[str]:
5162 for elem in self.elements:
5163 return elem._referred_schema
5164 else:
5165 return None
5167 @property
5168 def referred_table(self) -> Table:
5169 """The :class:`_schema.Table` object to which this
5170 :class:`_schema.ForeignKeyConstraint` references.
5172 This is a dynamically calculated attribute which may not be available
5173 if the constraint and/or parent table is not yet associated with
5174 a metadata collection that contains the referred table.
5176 """
5177 return self.elements[0].column.table
5179 def _validate_dest_table(self, table: Table) -> None:
5180 table_keys = {
5181 elem._table_key_within_construction() for elem in self.elements
5182 }
5183 if None not in table_keys and len(table_keys) > 1:
5184 elem0, elem1 = sorted(table_keys)[0:2]
5185 raise exc.ArgumentError(
5186 f"ForeignKeyConstraint on "
5187 f"{table.fullname}({self._col_description}) refers to "
5188 f"multiple remote tables: {elem0} and {elem1}"
5189 )
5191 @property
5192 def column_keys(self) -> _typing_Sequence[str]:
5193 """Return a list of string keys representing the local
5194 columns in this :class:`_schema.ForeignKeyConstraint`.
5196 This list is either the original string arguments sent
5197 to the constructor of the :class:`_schema.ForeignKeyConstraint`,
5198 or if the constraint has been initialized with :class:`_schema.Column`
5199 objects, is the string ``.key`` of each element.
5201 """
5202 if hasattr(self, "parent"):
5203 return self._columns.keys()
5204 else:
5205 return [
5206 col.key if isinstance(col, ColumnElement) else str(col)
5207 for col in self._pending_colargs
5208 ]
5210 @property
5211 def _col_description(self) -> str:
5212 return ", ".join(self.column_keys)
5214 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
5215 table = parent
5216 assert isinstance(table, Table)
5217 Constraint._set_parent(self, table)
5219 ColumnCollectionConstraint._set_parent(self, table)
5221 for col, fk in zip(self._columns, self.elements):
5222 if not hasattr(fk, "parent") or fk.parent is not col:
5223 fk._set_parent_with_dispatch(col)
5225 self._validate_dest_table(table)
5227 @util.deprecated(
5228 "1.4",
5229 "The :meth:`_schema.ForeignKeyConstraint.copy` method is deprecated "
5230 "and will be removed in a future release.",
5231 )
5232 def copy(
5233 self,
5234 *,
5235 schema: Optional[str] = None,
5236 target_table: Optional[Table] = None,
5237 **kw: Any,
5238 ) -> ForeignKeyConstraint:
5239 return self._copy(schema=schema, target_table=target_table, **kw)
5241 def _copy(
5242 self,
5243 *,
5244 schema: Optional[str] = None,
5245 target_table: Optional[Table] = None,
5246 **kw: Any,
5247 ) -> ForeignKeyConstraint:
5248 fkc = ForeignKeyConstraint(
5249 [x.parent.key for x in self.elements],
5250 [
5251 x._get_colspec(
5252 schema=schema,
5253 table_name=(
5254 target_table.name
5255 if target_table is not None
5256 and x._table_key_within_construction()
5257 == x.parent.table.key
5258 else None
5259 ),
5260 _is_copy=True,
5261 )
5262 for x in self.elements
5263 ],
5264 name=self.name,
5265 onupdate=self.onupdate,
5266 ondelete=self.ondelete,
5267 use_alter=self.use_alter,
5268 deferrable=self.deferrable,
5269 initially=self.initially,
5270 link_to_name=self.link_to_name,
5271 match=self.match,
5272 comment=self.comment,
5273 )
5274 for self_fk, other_fk in zip(self.elements, fkc.elements):
5275 self_fk._schema_item_copy(other_fk)
5276 return self._schema_item_copy(fkc)
5279class PrimaryKeyConstraint(ColumnCollectionConstraint):
5280 """A table-level PRIMARY KEY constraint.
5282 The :class:`.PrimaryKeyConstraint` object is present automatically
5283 on any :class:`_schema.Table` object; it is assigned a set of
5284 :class:`_schema.Column` objects corresponding to those marked with
5285 the :paramref:`_schema.Column.primary_key` flag::
5287 >>> my_table = Table(
5288 ... "mytable",
5289 ... metadata,
5290 ... Column("id", Integer, primary_key=True),
5291 ... Column("version_id", Integer, primary_key=True),
5292 ... Column("data", String(50)),
5293 ... )
5294 >>> my_table.primary_key
5295 PrimaryKeyConstraint(
5296 Column('id', Integer(), table=<mytable>,
5297 primary_key=True, nullable=False),
5298 Column('version_id', Integer(), table=<mytable>,
5299 primary_key=True, nullable=False)
5300 )
5302 The primary key of a :class:`_schema.Table` can also be specified by using
5303 a :class:`.PrimaryKeyConstraint` object explicitly; in this mode of usage,
5304 the "name" of the constraint can also be specified, as well as other
5305 options which may be recognized by dialects::
5307 my_table = Table(
5308 "mytable",
5309 metadata,
5310 Column("id", Integer),
5311 Column("version_id", Integer),
5312 Column("data", String(50)),
5313 PrimaryKeyConstraint("id", "version_id", name="mytable_pk"),
5314 )
5316 The two styles of column-specification should generally not be mixed.
5317 An warning is emitted if the columns present in the
5318 :class:`.PrimaryKeyConstraint`
5319 don't match the columns that were marked as ``primary_key=True``, if both
5320 are present; in this case, the columns are taken strictly from the
5321 :class:`.PrimaryKeyConstraint` declaration, and those columns otherwise
5322 marked as ``primary_key=True`` are ignored. This behavior is intended to
5323 be backwards compatible with previous behavior.
5325 For the use case where specific options are to be specified on the
5326 :class:`.PrimaryKeyConstraint`, but the usual style of using
5327 ``primary_key=True`` flags is still desirable, an empty
5328 :class:`.PrimaryKeyConstraint` may be specified, which will take on the
5329 primary key column collection from the :class:`_schema.Table` based on the
5330 flags::
5332 my_table = Table(
5333 "mytable",
5334 metadata,
5335 Column("id", Integer, primary_key=True),
5336 Column("version_id", Integer, primary_key=True),
5337 Column("data", String(50)),
5338 PrimaryKeyConstraint(name="mytable_pk", mssql_clustered=True),
5339 )
5341 """
5343 __visit_name__ = "primary_key_constraint"
5345 def __init__(
5346 self,
5347 *columns: _DDLColumnArgument,
5348 name: Optional[str] = None,
5349 deferrable: Optional[bool] = None,
5350 initially: Optional[str] = None,
5351 info: Optional[_InfoType] = None,
5352 _implicit_generated: bool = False,
5353 **dialect_kw: Any,
5354 ) -> None:
5355 self._implicit_generated = _implicit_generated
5356 super().__init__(
5357 *columns,
5358 name=name,
5359 deferrable=deferrable,
5360 initially=initially,
5361 info=info,
5362 **dialect_kw,
5363 )
5365 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
5366 table = parent
5367 assert isinstance(table, Table)
5368 super()._set_parent(table)
5370 if table.primary_key is not self:
5371 table.constraints.discard(table.primary_key)
5372 table.primary_key = self # type: ignore
5373 table.constraints.add(self)
5375 table_pks = [c for c in table.c if c.primary_key]
5376 if (
5377 self._columns
5378 and table_pks
5379 and set(table_pks) != set(self._columns)
5380 ):
5381 # black could not format these inline
5382 table_pk_str = ", ".join("'%s'" % c.name for c in table_pks)
5383 col_str = ", ".join("'%s'" % c.name for c in self._columns)
5385 util.warn(
5386 f"Table '{table.name}' specifies columns "
5387 f"{table_pk_str} as "
5388 f"primary_key=True, "
5389 f"not matching locally specified columns {col_str}; "
5390 f"setting the "
5391 f"current primary key columns to "
5392 f"{col_str}. "
5393 f"This warning "
5394 f"may become an exception in a future release"
5395 )
5396 table_pks[:] = []
5398 for c in self._columns:
5399 c.primary_key = True
5400 if c._user_defined_nullable is NULL_UNSPECIFIED:
5401 c.nullable = False
5402 if table_pks:
5403 self._columns.extend(table_pks)
5405 def _reload(self, columns: Iterable[Column[Any]]) -> None:
5406 """repopulate this :class:`.PrimaryKeyConstraint` given
5407 a set of columns.
5409 Existing columns in the table that are marked as primary_key=True
5410 are maintained.
5412 Also fires a new event.
5414 This is basically like putting a whole new
5415 :class:`.PrimaryKeyConstraint` object on the parent
5416 :class:`_schema.Table` object without actually replacing the object.
5418 The ordering of the given list of columns is also maintained; these
5419 columns will be appended to the list of columns after any which
5420 are already present.
5422 """
5423 # set the primary key flag on new columns.
5424 # note any existing PK cols on the table also have their
5425 # flag still set.
5426 for col in columns:
5427 col.primary_key = True
5429 self._columns.extend(columns)
5431 PrimaryKeyConstraint._autoincrement_column._reset(self) # type: ignore
5432 self._set_parent_with_dispatch(self.table)
5434 def _replace(self, col: Column[Any]) -> None:
5435 PrimaryKeyConstraint._autoincrement_column._reset(self) # type: ignore
5436 self._columns.replace(col)
5438 self.dispatch._sa_event_column_added_to_pk_constraint(self, col)
5440 @property
5441 def columns_autoinc_first(self) -> List[Column[Any]]:
5442 autoinc = self._autoincrement_column
5444 if autoinc is not None:
5445 return [autoinc] + [c for c in self._columns if c is not autoinc]
5446 else:
5447 return list(self._columns)
5449 @util.ro_memoized_property
5450 def _autoincrement_column(self) -> Optional[Column[int]]:
5451 def _validate_autoinc(col: Column[Any], autoinc_true: bool) -> bool:
5452 if col.type._type_affinity is not None and issubclass(
5453 col.type._type_affinity, type_api.NUMERICTYPE._type_affinity
5454 ):
5455 scale = col.type.scale # type: ignore[attr-defined]
5456 if scale != 0 and autoinc_true:
5457 raise exc.ArgumentError(
5458 f"Column type {col.type} with non-zero scale "
5459 f"{scale} on column '{col}' is not "
5460 f"compatible with autoincrement=True"
5461 )
5462 elif not autoinc_true:
5463 return False
5464 elif col.type._type_affinity is None or not issubclass(
5465 col.type._type_affinity, type_api.INTEGERTYPE._type_affinity
5466 ):
5467 if autoinc_true:
5468 raise exc.ArgumentError(
5469 f"Column type {col.type} on column '{col}' is not "
5470 f"compatible with autoincrement=True"
5471 )
5472 else:
5473 return False
5474 elif (
5475 col.default is not None
5476 and not isinstance(col.default, Sequence)
5477 and not autoinc_true
5478 ):
5479 return False
5480 elif (
5481 col.server_default is not None
5482 and not isinstance(col.server_default, Identity)
5483 and not autoinc_true
5484 ):
5485 return False
5486 elif col.foreign_keys and col.autoincrement not in (
5487 True,
5488 "ignore_fk",
5489 ):
5490 return False
5491 return True
5493 if len(self._columns) == 1:
5494 col = list(self._columns)[0]
5496 if col.autoincrement is True:
5497 _validate_autoinc(col, True)
5498 return col
5499 elif col.autoincrement in (
5500 "auto",
5501 "ignore_fk",
5502 ) and _validate_autoinc(col, False):
5503 return col
5504 else:
5505 return None
5507 else:
5508 autoinc = None
5509 for col in self._columns:
5510 if col.autoincrement is True:
5511 _validate_autoinc(col, True)
5512 if autoinc is not None:
5513 raise exc.ArgumentError(
5514 f"Only one Column may be marked "
5515 f"autoincrement=True, found both "
5516 f"{col.name} and {autoinc.name}."
5517 )
5518 else:
5519 autoinc = col
5521 return autoinc
5524class UniqueConstraint(ColumnCollectionConstraint):
5525 """A table-level UNIQUE constraint.
5527 Defines a single column or composite UNIQUE constraint. For a no-frills,
5528 single column constraint, adding ``unique=True`` to the ``Column``
5529 definition is a shorthand equivalent for an unnamed, single column
5530 UniqueConstraint.
5531 """
5533 __visit_name__ = "unique_constraint"
5536class Index(
5537 DialectKWArgs, ColumnCollectionMixin, HasConditionalDDL, SchemaItem
5538):
5539 """A table-level INDEX.
5541 Defines a composite (one or more column) INDEX.
5543 E.g.::
5545 sometable = Table(
5546 "sometable",
5547 metadata,
5548 Column("name", String(50)),
5549 Column("address", String(100)),
5550 )
5552 Index("some_index", sometable.c.name)
5554 For a no-frills, single column index, adding
5555 :class:`_schema.Column` also supports ``index=True``::
5557 sometable = Table(
5558 "sometable", metadata, Column("name", String(50), index=True)
5559 )
5561 For a composite index, multiple columns can be specified::
5563 Index("some_index", sometable.c.name, sometable.c.address)
5565 Functional indexes are supported as well, typically by using the
5566 :data:`.func` construct in conjunction with table-bound
5567 :class:`_schema.Column` objects::
5569 Index("some_index", func.lower(sometable.c.name))
5571 An :class:`.Index` can also be manually associated with a
5572 :class:`_schema.Table`,
5573 either through inline declaration or using
5574 :meth:`_schema.Table.append_constraint`. When this approach is used,
5575 the names
5576 of the indexed columns can be specified as strings::
5578 Table(
5579 "sometable",
5580 metadata,
5581 Column("name", String(50)),
5582 Column("address", String(100)),
5583 Index("some_index", "name", "address"),
5584 )
5586 To support functional or expression-based indexes in this form, the
5587 :func:`_expression.text` construct may be used::
5589 from sqlalchemy import text
5591 Table(
5592 "sometable",
5593 metadata,
5594 Column("name", String(50)),
5595 Column("address", String(100)),
5596 Index("some_index", text("lower(name)")),
5597 )
5599 .. seealso::
5601 :ref:`schema_indexes` - General information on :class:`.Index`.
5603 :ref:`postgresql_indexes` - PostgreSQL-specific options available for
5604 the :class:`.Index` construct.
5606 :ref:`mysql_indexes` - MySQL-specific options available for the
5607 :class:`.Index` construct.
5609 :ref:`mssql_indexes` - MSSQL-specific options available for the
5610 :class:`.Index` construct.
5612 """
5614 __visit_name__ = "index"
5616 table: Optional[Table]
5617 expressions: _typing_Sequence[Union[str, ColumnElement[Any]]]
5618 _table_bound_expressions: _typing_Sequence[ColumnElement[Any]]
5620 def __init__(
5621 self,
5622 name: Optional[str],
5623 *expressions: _DDLColumnArgument,
5624 unique: bool = False,
5625 quote: Optional[bool] = None,
5626 info: Optional[_InfoType] = None,
5627 _table: Optional[Table] = None,
5628 _column_flag: bool = False,
5629 **dialect_kw: Any,
5630 ) -> None:
5631 r"""Construct an index object.
5633 :param name:
5634 The name of the index
5636 :param \*expressions:
5637 Column expressions to include in the index. The expressions
5638 are normally instances of :class:`_schema.Column`, but may also
5639 be arbitrary SQL expressions which ultimately refer to a
5640 :class:`_schema.Column`.
5642 :param unique=False:
5643 Keyword only argument; if True, create a unique index.
5645 :param quote=None:
5646 Keyword only argument; whether to apply quoting to the name of
5647 the index. Works in the same manner as that of
5648 :paramref:`_schema.Column.quote`.
5650 :param info=None: Optional data dictionary which will be populated
5651 into the :attr:`.SchemaItem.info` attribute of this object.
5653 :param \**dialect_kw: Additional keyword arguments not mentioned above
5654 are dialect specific, and passed in the form
5655 ``<dialectname>_<argname>``. See the documentation regarding an
5656 individual dialect at :ref:`dialect_toplevel` for detail on
5657 documented arguments.
5659 """
5660 self.table = table = None
5662 self.name = quoted_name.construct(name, quote)
5663 self.unique = unique
5664 if info is not None:
5665 self.info = info
5667 # TODO: consider "table" argument being public, but for
5668 # the purpose of the fix here, it starts as private.
5669 if _table is not None:
5670 table = _table
5672 self._validate_dialect_kwargs(dialect_kw)
5674 self.expressions = []
5675 # will call _set_parent() if table-bound column
5676 # objects are present
5677 ColumnCollectionMixin.__init__(
5678 self,
5679 *expressions,
5680 _column_flag=_column_flag,
5681 _gather_expressions=self.expressions,
5682 )
5683 if table is not None:
5684 self._set_parent(table)
5686 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
5687 table = parent
5688 assert isinstance(table, Table)
5689 ColumnCollectionMixin._set_parent(self, table)
5691 if self.table is not None and table is not self.table:
5692 raise exc.ArgumentError(
5693 f"Index '{self.name}' is against table "
5694 f"'{self.table.description}', and "
5695 f"cannot be associated with table '{table.description}'."
5696 )
5697 self.table = table
5698 table.indexes.add(self)
5700 expressions = self.expressions
5701 col_expressions = self._col_expressions(table)
5702 assert len(expressions) == len(col_expressions)
5704 exprs = []
5705 for expr, colexpr in zip(expressions, col_expressions):
5706 if isinstance(expr, ClauseElement):
5707 exprs.append(expr)
5708 elif colexpr is not None:
5709 exprs.append(colexpr)
5710 else:
5711 assert False
5712 self.expressions = self._table_bound_expressions = exprs
5714 def create(
5715 self,
5716 bind: _CreateDropBind,
5717 checkfirst: Union[bool, CheckFirst] = CheckFirst.NONE,
5718 ) -> None:
5719 """Issue a ``CREATE`` statement for this
5720 :class:`.Index`, using the given
5721 :class:`.Connection` or :class:`.Engine`` for connectivity.
5723 .. seealso::
5725 :meth:`_schema.MetaData.create_all`.
5727 """
5728 bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
5730 def drop(
5731 self,
5732 bind: _CreateDropBind,
5733 checkfirst: Union[bool, CheckFirst] = CheckFirst.NONE,
5734 ) -> None:
5735 """Issue a ``DROP`` statement for this
5736 :class:`.Index`, using the given
5737 :class:`.Connection` or :class:`.Engine` for connectivity.
5739 .. seealso::
5741 :meth:`_schema.MetaData.drop_all`.
5743 """
5744 bind._run_ddl_visitor(ddl.SchemaDropper, self, checkfirst=checkfirst)
5746 def __repr__(self) -> str:
5747 exprs: _typing_Sequence[Any] # noqa: F842
5749 return "Index(%s)" % (
5750 ", ".join(
5751 [repr(self.name)]
5752 + [repr(e) for e in self.expressions]
5753 + (self.unique and ["unique=True"] or [])
5754 )
5755 )
5758_NamingSchemaCallable = Union[
5759 Callable[[Constraint, Table], str],
5760 Callable[[Index, Table], str],
5761]
5762_NamingSchemaDirective = Union[str, _NamingSchemaCallable]
5765class _NamingSchemaTD(TypedDict, total=False):
5766 fk: _NamingSchemaDirective
5767 pk: _NamingSchemaDirective
5768 ix: _NamingSchemaDirective
5769 ck: _NamingSchemaDirective
5770 uq: _NamingSchemaDirective
5773_NamingSchemaParameter = Union[
5774 # it seems like the TypedDict here is useful for pylance typeahead,
5775 # and not much else
5776 _NamingSchemaTD,
5777 # there is no form that allows Union[Type[Any], str] to work in all
5778 # cases, including breaking out Mapping[] entries for each combination
5779 # even, therefore keys must be `Any` (see #10264)
5780 Mapping[Any, _NamingSchemaDirective],
5781]
5784DEFAULT_NAMING_CONVENTION: _NamingSchemaParameter = util.immutabledict(
5785 {"ix": "ix_%(column_0_label)s"}
5786)
5789class MetaData(HasSchemaAttr):
5790 """A collection of :class:`_schema.Table`
5791 objects and their associated schema
5792 constructs.
5794 Holds a collection of :class:`_schema.Table` objects as well as
5795 an optional binding to an :class:`_engine.Engine` or
5796 :class:`_engine.Connection`. If bound, the :class:`_schema.Table` objects
5797 in the collection and their columns may participate in implicit SQL
5798 execution.
5800 The :class:`_schema.Table` objects themselves are stored in the
5801 :attr:`_schema.MetaData.tables` dictionary.
5803 :class:`_schema.MetaData` is a thread-safe object for read operations.
5804 Construction of new tables within a single :class:`_schema.MetaData`
5805 object,
5806 either explicitly or via reflection, may not be completely thread-safe.
5808 .. seealso::
5810 :ref:`metadata_describing` - Introduction to database metadata
5812 """
5814 __visit_name__ = "metadata"
5816 def __init__(
5817 self,
5818 schema: Optional[str] = None,
5819 quote_schema: Optional[bool] = None,
5820 naming_convention: Optional[_NamingSchemaParameter] = None,
5821 info: Optional[_InfoType] = None,
5822 ) -> None:
5823 """Create a new MetaData object.
5825 :param schema:
5826 The default schema to use for the :class:`_schema.Table`,
5827 :class:`.Sequence`, and potentially other objects associated with
5828 this :class:`_schema.MetaData`. Defaults to ``None``.
5830 .. seealso::
5832 :ref:`schema_metadata_schema_name` - details on how the
5833 :paramref:`_schema.MetaData.schema` parameter is used.
5835 :paramref:`_schema.Table.schema`
5837 :paramref:`.Sequence.schema`
5839 :param quote_schema:
5840 Sets the ``quote_schema`` flag for those :class:`_schema.Table`,
5841 :class:`.Sequence`, and other objects which make usage of the
5842 local ``schema`` name.
5844 :param info: Optional data dictionary which will be populated into the
5845 :attr:`.SchemaItem.info` attribute of this object.
5847 :param naming_convention: a dictionary referring to values which
5848 will establish default naming conventions for :class:`.Constraint`
5849 and :class:`.Index` objects, for those objects which are not given
5850 a name explicitly.
5852 The keys of this dictionary may be:
5854 * a constraint or Index class, e.g. the :class:`.UniqueConstraint`,
5855 :class:`_schema.ForeignKeyConstraint` class, the :class:`.Index`
5856 class
5858 * a string mnemonic for one of the known constraint classes;
5859 ``"fk"``, ``"pk"``, ``"ix"``, ``"ck"``, ``"uq"`` for foreign key,
5860 primary key, index, check, and unique constraint, respectively.
5862 * the string name of a user-defined "token" that can be used
5863 to define new naming tokens.
5865 The values associated with each "constraint class" or "constraint
5866 mnemonic" key are string naming templates, such as
5867 ``"uq_%(table_name)s_%(column_0_name)s"``,
5868 which describe how the name should be composed. The values
5869 associated with user-defined "token" keys should be callables of the
5870 form ``fn(constraint, table)``, which accepts the constraint/index
5871 object and :class:`_schema.Table` as arguments, returning a string
5872 result.
5874 The built-in names are as follows, some of which may only be
5875 available for certain types of constraint:
5877 * ``%(table_name)s`` - the name of the :class:`_schema.Table`
5878 object
5879 associated with the constraint.
5881 * ``%(referred_table_name)s`` - the name of the
5882 :class:`_schema.Table`
5883 object associated with the referencing target of a
5884 :class:`_schema.ForeignKeyConstraint`.
5886 * ``%(column_0_name)s`` - the name of the :class:`_schema.Column`
5887 at
5888 index position "0" within the constraint.
5890 * ``%(column_0N_name)s`` - the name of all :class:`_schema.Column`
5891 objects in order within the constraint, joined without a
5892 separator.
5894 * ``%(column_0_N_name)s`` - the name of all
5895 :class:`_schema.Column`
5896 objects in order within the constraint, joined with an
5897 underscore as a separator.
5899 * ``%(column_0_label)s``, ``%(column_0N_label)s``,
5900 ``%(column_0_N_label)s`` - the label of either the zeroth
5901 :class:`_schema.Column` or all :class:`.Columns`, separated with
5902 or without an underscore
5904 * ``%(column_0_key)s``, ``%(column_0N_key)s``,
5905 ``%(column_0_N_key)s`` - the key of either the zeroth
5906 :class:`_schema.Column` or all :class:`.Columns`, separated with
5907 or without an underscore
5909 * ``%(referred_column_0_name)s``, ``%(referred_column_0N_name)s``
5910 ``%(referred_column_0_N_name)s``, ``%(referred_column_0_key)s``,
5911 ``%(referred_column_0N_key)s``, ... column tokens which
5912 render the names/keys/labels of columns that are referenced
5913 by a :class:`_schema.ForeignKeyConstraint`.
5915 * ``%(constraint_name)s`` - a special key that refers to the
5916 existing name given to the constraint. When this key is
5917 present, the :class:`.Constraint` object's existing name will be
5918 replaced with one that is composed from template string that
5919 uses this token. When this token is present, it is required that
5920 the :class:`.Constraint` is given an explicit name ahead of time.
5922 * user-defined: any additional token may be implemented by passing
5923 it along with a ``fn(constraint, table)`` callable to the
5924 naming_convention dictionary.
5926 .. seealso::
5928 :ref:`constraint_naming_conventions` - for detailed usage
5929 examples.
5931 """
5932 if schema is not None and not isinstance(schema, str):
5933 raise exc.ArgumentError(
5934 "expected schema argument to be a string, "
5935 f"got {type(schema)}."
5936 )
5937 self.tables = util.FacadeDict()
5938 self.schema = quoted_name.construct(schema, quote_schema)
5939 self.naming_convention = (
5940 naming_convention
5941 if naming_convention
5942 else DEFAULT_NAMING_CONVENTION
5943 )
5944 if info:
5945 self.info = info
5946 self._schemas: Set[str] = set()
5947 self._sequences: Dict[str, Sequence] = {}
5948 self._fk_memos: Dict[Tuple[str, Optional[str]], List[ForeignKey]] = (
5949 collections.defaultdict(list)
5950 )
5951 self._objects: Set[Union[HasSchemaAttr, SchemaType]] = set()
5953 tables: util.FacadeDict[str, Table]
5954 """A dictionary of :class:`_schema.Table`
5955 objects keyed to their name or "table key".
5957 The exact key is that determined by the :attr:`_schema.Table.key`
5958 attribute;
5959 for a table with no :attr:`_schema.Table.schema` attribute,
5960 this is the same
5961 as :attr:`_schema.Table.name`. For a table with a schema,
5962 it is typically of the
5963 form ``schemaname.tablename``.
5965 .. seealso::
5967 :attr:`_schema.MetaData.sorted_tables`
5969 """
5971 def __repr__(self) -> str:
5972 return "MetaData()"
5974 def __contains__(self, table_or_key: Union[str, Table]) -> bool:
5975 if not isinstance(table_or_key, str):
5976 table_or_key = table_or_key.key
5977 return table_or_key in self.tables
5979 def _add_table(
5980 self, name: str, schema: Optional[str], table: Table
5981 ) -> None:
5982 key = _get_table_key(name, schema)
5983 self.tables._insert_item(key, table)
5984 if schema:
5985 self._schemas.add(schema)
5987 def _remove_table(self, name: str, schema: Optional[str]) -> None:
5988 key = _get_table_key(name, schema)
5989 removed = dict.pop(self.tables, key, None)
5990 if removed is not None:
5991 for fk in removed.foreign_keys:
5992 fk._remove_from_metadata(self)
5993 if self._schemas:
5994 self._schemas = {
5995 t.schema for t in self.tables.values() if t.schema is not None
5996 }
5998 def __getstate__(self) -> Dict[str, Any]:
5999 return {
6000 "tables": self.tables,
6001 "schema": self.schema,
6002 "schemas": self._schemas,
6003 "sequences": self._sequences,
6004 "fk_memos": self._fk_memos,
6005 "naming_convention": self.naming_convention,
6006 "objects": self._objects,
6007 }
6009 def __setstate__(self, state: Dict[str, Any]) -> None:
6010 self.tables = state["tables"]
6011 self.schema = state["schema"]
6012 self.naming_convention = state["naming_convention"]
6013 self._sequences = state["sequences"]
6014 self._schemas = state["schemas"]
6015 self._fk_memos = state["fk_memos"]
6016 self._objects = state.get("objects", set())
6018 def clear(self) -> None:
6019 """Clear all objects from this MetaData."""
6021 dict.clear(self.tables)
6022 self._schemas.clear()
6023 self._fk_memos.clear()
6024 self._sequences.clear()
6025 self._objects.clear()
6027 def remove(self, table: Table) -> None:
6028 """Remove the given Table object from this MetaData."""
6030 self._remove_table(table.name, table.schema)
6032 @property
6033 def sorted_tables(self) -> List[Table]:
6034 """Returns a list of :class:`_schema.Table` objects sorted in order of
6035 foreign key dependency.
6037 The sorting will place :class:`_schema.Table`
6038 objects that have dependencies
6039 first, before the dependencies themselves, representing the
6040 order in which they can be created. To get the order in which
6041 the tables would be dropped, use the ``reversed()`` Python built-in.
6043 .. warning::
6045 The :attr:`.MetaData.sorted_tables` attribute cannot by itself
6046 accommodate automatic resolution of dependency cycles between
6047 tables, which are usually caused by mutually dependent foreign key
6048 constraints. When these cycles are detected, the foreign keys
6049 of these tables are omitted from consideration in the sort.
6050 A warning is emitted when this condition occurs, which will be an
6051 exception raise in a future release. Tables which are not part
6052 of the cycle will still be returned in dependency order.
6054 To resolve these cycles, the
6055 :paramref:`_schema.ForeignKeyConstraint.use_alter` parameter may be
6056 applied to those constraints which create a cycle. Alternatively,
6057 the :func:`_schema.sort_tables_and_constraints` function will
6058 automatically return foreign key constraints in a separate
6059 collection when cycles are detected so that they may be applied
6060 to a schema separately.
6062 .. seealso::
6064 :func:`_schema.sort_tables`
6066 :func:`_schema.sort_tables_and_constraints`
6068 :attr:`_schema.MetaData.tables`
6070 :meth:`_reflection.Inspector.get_table_names`
6072 :meth:`_reflection.Inspector.get_sorted_table_and_fkc_names`
6075 """
6076 return ddl.sort_tables(
6077 sorted(self.tables.values(), key=lambda t: t.key) # type: ignore
6078 )
6080 # overload needed to work around mypy this mypy
6081 # https://github.com/python/mypy/issues/17093
6082 @overload
6083 def reflect(
6084 self,
6085 bind: Engine,
6086 schema: Optional[str] = ...,
6087 views: bool = ...,
6088 only: Union[
6089 _typing_Sequence[str], Callable[[str, MetaData], bool], None
6090 ] = ...,
6091 extend_existing: bool = ...,
6092 autoload_replace: bool = ...,
6093 resolve_fks: bool = ...,
6094 **dialect_kwargs: Any,
6095 ) -> None: ...
6097 @overload
6098 def reflect(
6099 self,
6100 bind: Connection,
6101 schema: Optional[str] = ...,
6102 views: bool = ...,
6103 only: Union[
6104 _typing_Sequence[str], Callable[[str, MetaData], bool], None
6105 ] = ...,
6106 extend_existing: bool = ...,
6107 autoload_replace: bool = ...,
6108 resolve_fks: bool = ...,
6109 **dialect_kwargs: Any,
6110 ) -> None: ...
6112 @util.preload_module("sqlalchemy.engine.reflection")
6113 def reflect(
6114 self,
6115 bind: Union[Engine, Connection],
6116 schema: Optional[str] = None,
6117 views: bool = False,
6118 only: Union[
6119 _typing_Sequence[str], Callable[[str, MetaData], bool], None
6120 ] = None,
6121 extend_existing: bool = False,
6122 autoload_replace: bool = True,
6123 resolve_fks: bool = True,
6124 **dialect_kwargs: Any,
6125 ) -> None:
6126 r"""Load all available table definitions from the database.
6128 Automatically creates ``Table`` entries in this ``MetaData`` for any
6129 table available in the database but not yet present in the
6130 ``MetaData``. May be called multiple times to pick up tables recently
6131 added to the database, however no special action is taken if a table
6132 in this ``MetaData`` no longer exists in the database.
6134 :param bind:
6135 A :class:`.Connection` or :class:`.Engine` used to access the
6136 database.
6138 :param schema:
6139 Optional, query and reflect tables from an alternate schema.
6140 If None, the schema associated with this :class:`_schema.MetaData`
6141 is used, if any.
6143 :param views:
6144 If True, also reflect views (materialized and plain).
6146 :param only:
6147 Optional. Load only a sub-set of available named tables. May be
6148 specified as a sequence of names or a callable.
6150 If a sequence of names is provided, only those tables will be
6151 reflected. An error is raised if a table is requested but not
6152 available. Named tables already present in this ``MetaData`` are
6153 ignored.
6155 If a callable is provided, it will be used as a boolean predicate to
6156 filter the list of potential table names. The callable is called
6157 with a table name and this ``MetaData`` instance as positional
6158 arguments and should return a true value for any table to reflect.
6160 :param extend_existing: Passed along to each :class:`_schema.Table` as
6161 :paramref:`_schema.Table.extend_existing`.
6163 :param autoload_replace: Passed along to each :class:`_schema.Table`
6164 as
6165 :paramref:`_schema.Table.autoload_replace`.
6167 :param resolve_fks: if True, reflect :class:`_schema.Table`
6168 objects linked
6169 to :class:`_schema.ForeignKey` objects located in each
6170 :class:`_schema.Table`.
6171 For :meth:`_schema.MetaData.reflect`,
6172 this has the effect of reflecting
6173 related tables that might otherwise not be in the list of tables
6174 being reflected, for example if the referenced table is in a
6175 different schema or is omitted via the
6176 :paramref:`.MetaData.reflect.only` parameter. When False,
6177 :class:`_schema.ForeignKey` objects are not followed to the
6178 :class:`_schema.Table`
6179 in which they link, however if the related table is also part of the
6180 list of tables that would be reflected in any case, the
6181 :class:`_schema.ForeignKey` object will still resolve to its related
6182 :class:`_schema.Table` after the :meth:`_schema.MetaData.reflect`
6183 operation is
6184 complete. Defaults to True.
6186 .. seealso::
6188 :paramref:`_schema.Table.resolve_fks`
6190 :param \**dialect_kwargs: Additional keyword arguments not mentioned
6191 above are dialect specific, and passed in the form
6192 ``<dialectname>_<argname>``. See the documentation regarding an
6193 individual dialect at :ref:`dialect_toplevel` for detail on
6194 documented arguments.
6196 .. seealso::
6198 :ref:`metadata_reflection_toplevel`
6200 :meth:`_events.DDLEvents.column_reflect` - Event used to customize
6201 the reflected columns. Usually used to generalize the types using
6202 :meth:`_types.TypeEngine.as_generic`
6204 :ref:`metadata_reflection_dbagnostic_types` - describes how to
6205 reflect tables using general types.
6207 """
6209 with inspection.inspect(bind)._inspection_context() as insp:
6210 reflect_opts: Any = {
6211 "autoload_with": insp,
6212 "extend_existing": extend_existing,
6213 "autoload_replace": autoload_replace,
6214 "resolve_fks": resolve_fks,
6215 "_extend_on": set(),
6216 }
6218 reflect_opts.update(dialect_kwargs)
6220 if schema is None:
6221 schema = self.schema
6223 if schema is not None:
6224 reflect_opts["schema"] = schema
6226 kind = util.preloaded.engine_reflection.ObjectKind.TABLE
6227 available: util.OrderedSet[str] = util.OrderedSet(
6228 insp.get_table_names(schema, **dialect_kwargs)
6229 )
6230 if views:
6231 kind = util.preloaded.engine_reflection.ObjectKind.ANY
6232 available.update(insp.get_view_names(schema, **dialect_kwargs))
6233 try:
6234 available.update(
6235 insp.get_materialized_view_names(
6236 schema, **dialect_kwargs
6237 )
6238 )
6239 except NotImplementedError:
6240 pass
6242 if schema is not None:
6243 available_w_schema: util.OrderedSet[str] = util.OrderedSet(
6244 [f"{schema}.{name}" for name in available]
6245 )
6246 else:
6247 available_w_schema = available
6249 current = set(self.tables)
6251 if only is None:
6252 load = [
6253 name
6254 for name, schname in zip(available, available_w_schema)
6255 if extend_existing or schname not in current
6256 ]
6257 elif callable(only):
6258 load = [
6259 name
6260 for name, schname in zip(available, available_w_schema)
6261 if (extend_existing or schname not in current)
6262 and only(name, self)
6263 ]
6264 else:
6265 missing = [name for name in only if name not in available]
6266 if missing:
6267 s = schema and (" schema '%s'" % schema) or ""
6268 missing_str = ", ".join(missing)
6269 raise exc.InvalidRequestError(
6270 f"Could not reflect: requested table(s) not available "
6271 f"in {bind.engine!r}{s}: ({missing_str})"
6272 )
6273 load = [
6274 name
6275 for name in only
6276 if extend_existing or name not in current
6277 ]
6278 # pass the available tables so the inspector can
6279 # choose to ignore the filter_names
6280 _reflect_info = insp._get_reflection_info(
6281 schema=schema,
6282 filter_names=load,
6283 available=available,
6284 kind=kind,
6285 scope=util.preloaded.engine_reflection.ObjectScope.ANY,
6286 **dialect_kwargs,
6287 )
6288 reflect_opts["_reflect_info"] = _reflect_info
6290 for name in load:
6291 try:
6292 Table(name, self, **reflect_opts)
6293 except exc.UnreflectableTableError as uerr:
6294 util.warn(f"Skipping table {name}: {uerr}")
6296 def create_all(
6297 self,
6298 bind: _CreateDropBind,
6299 tables: Optional[_typing_Sequence[Table]] = None,
6300 checkfirst: Union[bool, CheckFirst] = CheckFirst.ALL,
6301 ) -> None:
6302 """Create all tables stored in this metadata.
6304 Conditional by default, will not attempt to recreate tables already
6305 present in the target database.
6307 :param bind:
6308 A :class:`.Connection` or :class:`.Engine` used to access the
6309 database.
6311 :param tables:
6312 Optional list of ``Table`` objects, which is a subset of the total
6313 tables in the ``MetaData`` (others are ignored).
6315 :param checkfirst: A boolean value or instance of :class:`.CheckFirst`.
6316 Indicates which objects should be checked for within a separate pass
6317 before creating schema objects.
6319 """
6320 bind._run_ddl_visitor(
6321 ddl.SchemaGenerator, self, checkfirst=checkfirst, tables=tables
6322 )
6324 def drop_all(
6325 self,
6326 bind: _CreateDropBind,
6327 tables: Optional[_typing_Sequence[Table]] = None,
6328 checkfirst: Union[bool, CheckFirst] = CheckFirst.ALL,
6329 ) -> None:
6330 """Drop all tables stored in this metadata.
6332 Conditional by default, will not attempt to drop tables not present in
6333 the target database.
6335 :param bind:
6336 A :class:`.Connection` or :class:`.Engine` used to access the
6337 database.
6339 :param tables:
6340 Optional list of ``Table`` objects, which is a subset of the
6341 total tables in the ``MetaData`` (others are ignored).
6343 :param checkfirst: A boolean value or instance of :class:`.CheckFirst`.
6344 Indicates which objects should be checked for within a separate pass
6345 before dropping schema objects.
6347 """
6348 bind._run_ddl_visitor(
6349 ddl.SchemaDropper, self, checkfirst=checkfirst, tables=tables
6350 )
6352 @property
6353 def schemas(self) -> _typing_Sequence[str]:
6354 """A sequence of schema names that are present in this MetaData."""
6355 schemas = self._schemas
6356 if self.schema:
6357 schemas = schemas | {self.schema}
6358 return tuple(schemas)
6360 def get_schema_objects(
6361 self,
6362 kind: Type[_T],
6363 *,
6364 schema: Union[str, None, Literal[_NoArg.NO_ARG]] = _NoArg.NO_ARG,
6365 ) -> _typing_Sequence[_T]:
6366 """Return a sequence of schema objects of the given kind.
6368 This method can be used to return :class:`_sqltypes.Enum`,
6369 :class:`.Sequence`, etc. objects registered in this
6370 :class:`_schema.MetaData`.
6372 :param kind: a type that indicates what object to return, such as
6373 :class:`Enum` or :class:`Sequence`.
6374 :param schema: Optional, a schema name to filter the objects by. If
6375 not provided the default schema of the metadata is used.
6377 """
6379 if schema is _NoArg.NO_ARG:
6380 schema = self.schema
6381 return tuple(
6382 obj
6383 for obj in self._objects
6384 if isinstance(obj, kind) and obj.schema == schema
6385 )
6387 def get_schema_object_by_name(
6388 self,
6389 kind: Type[_T],
6390 name: str,
6391 *,
6392 schema: Union[str, None, Literal[_NoArg.NO_ARG]] = _NoArg.NO_ARG,
6393 ) -> Optional[_T]:
6394 """Return a schema objects of the given kind and name if found.
6396 This method can be used to return :class:`_sqltypes.Enum`,
6397 :class:`.Sequence`, etc. objects registered in this
6398 :class:`_schema.MetaData`.
6400 :param kind: a type that indicates what object to return, such as
6401 :class:`Enum` or :class:`Sequence`.
6402 :param name: the name of the object to return.
6403 :param schema: Optional, a schema name to filter the objects by. If
6404 not provided the default schema of the metadata is used.
6406 """
6408 for obj in self.get_schema_objects(kind, schema=schema):
6409 if getattr(obj, "name", None) == name:
6410 return obj
6411 return None
6413 def _register_object(self, obj: Union[HasSchemaAttr, SchemaType]) -> None:
6414 self._objects.add(obj)
6417class Computed(FetchedValue, SchemaItem):
6418 """Defines a generated column, i.e. "GENERATED ALWAYS AS" syntax.
6420 The :class:`.Computed` construct is an inline construct added to the
6421 argument list of a :class:`_schema.Column` object::
6423 from sqlalchemy import Computed
6425 Table(
6426 "square",
6427 metadata_obj,
6428 Column("side", Float, nullable=False),
6429 Column("area", Float, Computed("side * side")),
6430 )
6432 See the linked documentation below for complete details.
6434 .. seealso::
6436 :ref:`computed_ddl`
6438 """
6440 __visit_name__ = "computed_column"
6442 column: Optional[Column[Any]]
6444 @_document_text_coercion(
6445 "sqltext", ":class:`.Computed`", ":paramref:`.Computed.sqltext`"
6446 )
6447 def __init__(
6448 self, sqltext: _DDLColumnArgument, persisted: Optional[bool] = None
6449 ) -> None:
6450 """Construct a GENERATED ALWAYS AS DDL construct to accompany a
6451 :class:`_schema.Column`.
6453 :param sqltext:
6454 A string containing the column generation expression, which will be
6455 used verbatim, or a SQL expression construct, such as a
6456 :func:`_expression.text`
6457 object. If given as a string, the object is converted to a
6458 :func:`_expression.text` object.
6460 :param persisted:
6461 Optional, controls how this column should be persisted by the
6462 database. Possible values are:
6464 * ``None``, the default, it will use the default persistence
6465 defined by the database.
6466 * ``True``, will render ``GENERATED ALWAYS AS ... STORED``, or the
6467 equivalent for the target database if supported.
6468 * ``False``, will render ``GENERATED ALWAYS AS ... VIRTUAL``, or
6469 the equivalent for the target database if supported.
6471 Specifying ``True`` or ``False`` may raise an error when the DDL
6472 is emitted to the target database if the database does not support
6473 that persistence option. Leaving this parameter at its default
6474 of ``None`` is guaranteed to succeed for all databases that support
6475 ``GENERATED ALWAYS AS``.
6477 """
6478 self.sqltext = coercions.expect(roles.DDLExpressionRole, sqltext)
6479 self.persisted = persisted
6480 self.column = None
6482 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
6483 assert isinstance(parent, Column)
6485 if not isinstance(
6486 parent.server_default, (type(None), Computed)
6487 ) or not isinstance(parent.server_onupdate, (type(None), Computed)):
6488 raise exc.ArgumentError(
6489 "A generated column cannot specify a server_default or a "
6490 "server_onupdate argument"
6491 )
6492 self.column = parent
6493 parent.computed = self
6494 self.column.server_onupdate = self
6495 self.column.server_default = self
6497 def _as_for_update(self, for_update: bool) -> FetchedValue:
6498 return self
6500 @util.deprecated(
6501 "1.4",
6502 "The :meth:`_schema.Computed.copy` method is deprecated "
6503 "and will be removed in a future release.",
6504 )
6505 def copy(
6506 self, *, target_table: Optional[Table] = None, **kw: Any
6507 ) -> Computed:
6508 return self._copy(target_table=target_table, **kw)
6510 def _copy(
6511 self, *, target_table: Optional[Table] = None, **kw: Any
6512 ) -> Computed:
6513 sqltext = _copy_expression(
6514 self.sqltext,
6515 self.column.table if self.column is not None else None,
6516 target_table,
6517 )
6518 g = Computed(sqltext, persisted=self.persisted)
6520 return self._schema_item_copy(g)
6523class Identity(IdentityOptions, FetchedValue, SchemaItem):
6524 """Defines an identity column, i.e. "GENERATED { ALWAYS | BY DEFAULT }
6525 AS IDENTITY" syntax.
6527 The :class:`.Identity` construct is an inline construct added to the
6528 argument list of a :class:`_schema.Column` object::
6530 from sqlalchemy import Identity
6532 Table(
6533 "foo",
6534 metadata_obj,
6535 Column("id", Integer, Identity()),
6536 Column("description", Text),
6537 )
6539 See the linked documentation below for complete details.
6541 .. versionadded:: 1.4
6543 .. seealso::
6545 :ref:`identity_ddl`
6547 """
6549 __visit_name__ = "identity_column"
6551 is_identity = True
6553 @util.deprecated_params(
6554 order=(
6555 "2.1",
6556 "This parameter is supported only by Oracle Database, "
6557 "use ``oracle_order`` instead.",
6558 ),
6559 on_null=(
6560 "2.1",
6561 "This parameter is supported only by Oracle Database, "
6562 "use ``oracle_on_null`` instead.",
6563 ),
6564 )
6565 def __init__(
6566 self,
6567 always: Optional[bool] = False,
6568 on_null: Optional[bool] = None,
6569 start: Optional[int] = None,
6570 increment: Optional[int] = None,
6571 minvalue: Optional[int] = None,
6572 maxvalue: Optional[int] = None,
6573 nominvalue: Optional[bool] = None,
6574 nomaxvalue: Optional[bool] = None,
6575 cycle: Optional[bool] = None,
6576 cache: Optional[int] = None,
6577 order: Optional[bool] = None,
6578 **dialect_kw: Any,
6579 ) -> None:
6580 """Construct a GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY DDL
6581 construct to accompany a :class:`_schema.Column`.
6583 See the :class:`.Sequence` documentation for a complete description
6584 of most parameters.
6586 .. note::
6587 MSSQL supports this construct as the preferred alternative to
6588 generate an IDENTITY on a column, but it uses non standard
6589 syntax that only support :paramref:`_schema.Identity.start`
6590 and :paramref:`_schema.Identity.increment`.
6591 All other parameters are ignored.
6593 :param always:
6594 A boolean, that indicates the type of identity column.
6595 If ``False`` is specified, the default, then the user-specified
6596 value takes precedence.
6597 If ``True`` is specified, a user-specified value is not accepted (
6598 on some backends, like PostgreSQL, OVERRIDING SYSTEM VALUE, or
6599 similar, may be specified in an INSERT to override the sequence
6600 value).
6601 Some backends also have a default value for this parameter,
6602 ``None`` can be used to omit rendering this part in the DDL. It
6603 will be treated as ``False`` if a backend does not have a default
6604 value.
6606 :param on_null:
6607 Set to ``True`` to specify ON NULL in conjunction with a
6608 ``always=False`` identity column. This option is only supported on
6609 some backends, like Oracle Database.
6611 :param start: the starting index of the sequence.
6612 :param increment: the increment value of the sequence.
6613 :param minvalue: the minimum value of the sequence.
6614 :param maxvalue: the maximum value of the sequence.
6615 :param nominvalue: no minimum value of the sequence.
6616 :param nomaxvalue: no maximum value of the sequence.
6617 :param cycle: allows the sequence to wrap around when the maxvalue
6618 or minvalue has been reached.
6619 :param cache: optional integer value; number of future values in the
6620 sequence which are calculated in advance.
6621 :param order: optional boolean value; if true, renders the
6622 ORDER keyword.
6624 """
6625 self.dialect_options
6626 if on_null is not None:
6627 if "oracle_on_null" in dialect_kw:
6628 raise exc.ArgumentError(
6629 "Cannot specify both 'on_null' and 'oracle_on_null'. "
6630 "Please use only 'oracle_on_null'."
6631 )
6632 dialect_kw["oracle_on_null"] = on_null
6634 IdentityOptions.__init__(
6635 self,
6636 start=start,
6637 increment=increment,
6638 minvalue=minvalue,
6639 maxvalue=maxvalue,
6640 nominvalue=nominvalue,
6641 nomaxvalue=nomaxvalue,
6642 cycle=cycle,
6643 cache=cache,
6644 order=order,
6645 **dialect_kw,
6646 )
6647 self.always = always
6648 self.column = None
6650 @property
6651 def on_null(self) -> Optional[bool]:
6652 """Alias of the ``dialect_kwargs`` ``'oracle_on_null'``.
6654 .. deprecated:: 2.1 The 'on_null' attribute is deprecated.
6655 """
6656 value: Optional[bool] = self.dialect_kwargs.get("oracle_on_null")
6657 return value
6659 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
6660 assert isinstance(parent, Column)
6661 if not isinstance(
6662 parent.server_default, (type(None), Identity)
6663 ) or not isinstance(parent.server_onupdate, type(None)):
6664 raise exc.ArgumentError(
6665 "A column with an Identity object cannot specify a "
6666 "server_default or a server_onupdate argument"
6667 )
6668 if parent.autoincrement is False:
6669 raise exc.ArgumentError(
6670 "A column with an Identity object cannot specify "
6671 "autoincrement=False"
6672 )
6673 self.column = parent
6675 parent.identity = self
6676 if parent._user_defined_nullable is NULL_UNSPECIFIED:
6677 parent.nullable = False
6679 parent.server_default = self
6681 def _as_for_update(self, for_update: bool) -> FetchedValue:
6682 return self
6684 @util.deprecated(
6685 "1.4",
6686 "The :meth:`_schema.Identity.copy` method is deprecated "
6687 "and will be removed in a future release.",
6688 )
6689 def copy(self, **kw: Any) -> Identity:
6690 return self._copy(**kw)
6692 def _copy(self, **kw: Any) -> Identity:
6693 i = Identity(**self._as_dict(), **self.dialect_kwargs)
6695 return self._schema_item_copy(i)
6697 def _as_dict(self) -> Dict[str, Any]:
6698 return {
6699 # always=None means something different than always=False
6700 "always": self.always,
6701 **super()._as_dict(),
6702 }