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 ClassVar
43from typing import Collection
44from typing import Dict
45from typing import Final
46from typing import Iterable
47from typing import Iterator
48from typing import List
49from typing import Literal
50from typing import Mapping
51from typing import NamedTuple
52from typing import NoReturn
53from typing import Optional
54from typing import overload
55from typing import Protocol
56from typing import Sequence as _typing_Sequence
57from typing import Set
58from typing import Tuple
59from typing import Type
60from typing import TYPE_CHECKING
61from typing import TypedDict
62from typing import TypeGuard
63from typing import TypeVar
64from typing import Union
66from . import coercions
67from . import ddl
68from . import roles
69from . import type_api
70from . import visitors
71from ._annotated_cols import _ColCC_co
72from ._annotated_cols import _extract_columns_from_class
73from ._annotated_cols import _TC_co
74from ._annotated_cols import Named
75from ._annotated_cols import TypedColumns
76from ._typing import _T
77from .base import _DefaultDescriptionTuple
78from .base import _NoArg
79from .base import _NoneName
80from .base import _SentinelColumnCharacterization
81from .base import _SentinelDefaultCharacterization
82from .base import DedupeColumnCollection
83from .base import DialectKWArgs
84from .base import Executable
85from .base import SchemaEventTarget as SchemaEventTarget
86from .base import SchemaVisitable as SchemaVisitable
87from .base import WriteableColumnCollection
88from .coercions import _document_text_coercion
89from .ddl import CheckFirst
90from .elements import ClauseElement
91from .elements import ColumnClause
92from .elements import ColumnElement
93from .elements import quoted_name
94from .elements import TextClause
95from .selectable import TableClause
96from .type_api import to_instance
97from .visitors import ExternallyTraversible
98from .. import event
99from .. import exc
100from .. import inspection
101from .. import util
102from ..util import HasMemoized
103from ..util.typing import Self
105if typing.TYPE_CHECKING:
106 from ._typing import _AutoIncrementType
107 from ._typing import _CreateDropBind
108 from ._typing import _DDLColumnArgument
109 from ._typing import _DDLColumnReferenceArgument
110 from ._typing import _InfoType
111 from ._typing import _TextCoercedExpressionArgument
112 from ._typing import _TypeEngineArgument
113 from .base import ColumnSet
114 from .base import ReadOnlyColumnCollection
115 from .compiler import DDLCompiler
116 from .ddl import TableCreateDDL
117 from .ddl import TableDropDDL
118 from .elements import BindParameter
119 from .elements import KeyedColumnElement
120 from .functions import Function
121 from .sqltypes import SchemaType
122 from .type_api import TypeEngine
123 from .visitors import anon_map
124 from ..engine import Connection
125 from ..engine import Engine
126 from ..engine.interfaces import _CoreMultiExecuteParams
127 from ..engine.interfaces import CoreExecuteOptionsParameter
128 from ..engine.interfaces import ExecutionContext
129 from ..engine.reflection import _ReflectionInfo
130 from ..sql.selectable import FromClause
132_SI = TypeVar("_SI", bound="SchemaItem")
133_TAB = TypeVar("_TAB", bound="Table")
136_ConstraintNameArgument = Optional[Union[str, _NoneName]]
138_ServerDefaultArgument = Union[
139 "FetchedValue", str, TextClause, ColumnElement[Any]
140]
142_ServerOnUpdateArgument = _ServerDefaultArgument
145class SchemaConst(Enum):
146 RETAIN_SCHEMA = 1
147 """Symbol indicating that a :class:`_schema.Table`, :class:`.Sequence`
148 or in some cases a :class:`_schema.ForeignKey` object, in situations
149 where the object is being copied for a :meth:`.Table.to_metadata`
150 operation, should retain the schema name that it already has.
152 """
154 BLANK_SCHEMA = 2
155 """Symbol indicating that a :class:`_schema.Table` or :class:`.Sequence`
156 should have 'None' for its schema, even if the parent
157 :class:`_schema.MetaData` has specified a schema.
159 .. seealso::
161 :paramref:`_schema.MetaData.schema`
163 :paramref:`_schema.Table.schema`
165 :paramref:`.Sequence.schema`
167 """
169 NULL_UNSPECIFIED = 3
170 """Symbol indicating the "nullable" keyword was not passed to a Column.
172 This is used to distinguish between the use case of passing
173 ``nullable=None`` to a :class:`.Column`, which has special meaning
174 on some backends such as SQL Server.
176 """
179RETAIN_SCHEMA: Final[Literal[SchemaConst.RETAIN_SCHEMA]] = (
180 SchemaConst.RETAIN_SCHEMA
181)
182BLANK_SCHEMA: Final[Literal[SchemaConst.BLANK_SCHEMA]] = (
183 SchemaConst.BLANK_SCHEMA
184)
185NULL_UNSPECIFIED: Final[Literal[SchemaConst.NULL_UNSPECIFIED]] = (
186 SchemaConst.NULL_UNSPECIFIED
187)
190def _get_table_key(name: str, schema: Optional[str]) -> str:
191 if schema is None:
192 return name
193 else:
194 return schema + "." + name
197# this should really be in sql/util.py but we'd have to
198# break an import cycle
199def _copy_expression(
200 expression: ColumnElement[Any],
201 source_table: Optional[Table],
202 target_table: Optional[Table],
203) -> ColumnElement[Any]:
204 if source_table is None or target_table is None:
205 return expression
207 fixed_source_table = source_table
208 fixed_target_table = target_table
210 def replace(
211 element: ExternallyTraversible, **kw: Any
212 ) -> Optional[ExternallyTraversible]:
213 if (
214 isinstance(element, Column)
215 and element.table is fixed_source_table
216 and element.key in fixed_source_table.c
217 ):
218 return fixed_target_table.c[element.key]
219 else:
220 return None
222 return cast(
223 ColumnElement[Any],
224 visitors.replacement_traverse(expression, {}, replace),
225 )
228@inspection._self_inspects
229class SchemaItem(SchemaVisitable):
230 """Base class for items that define a database schema."""
232 __visit_name__ = "schema_item"
234 create_drop_stringify_dialect = "default"
236 def _init_items(self, *args: SchemaItem, **kw: Any) -> None:
237 """Initialize the list of child items for this SchemaItem."""
238 for item in args:
239 if item is not None:
240 try:
241 spwd = item._set_parent_with_dispatch
242 except AttributeError as err:
243 raise exc.ArgumentError(
244 "'SchemaItem' object, such as a 'Column' or a "
245 f"'Constraint' expected, got {item!r}"
246 ) from err
247 else:
248 spwd(self, **kw)
250 def __repr__(self) -> str:
251 return util.generic_repr(self, omit_kwarg=["info"])
253 @util.memoized_property
254 def info(self) -> _InfoType:
255 """Info dictionary associated with the object, allowing user-defined
256 data to be associated with this :class:`.SchemaItem`.
258 The dictionary is automatically generated when first accessed.
259 It can also be specified in the constructor of some objects,
260 such as :class:`_schema.Table` and :class:`_schema.Column`.
262 """
263 return {}
265 def _schema_item_copy(self, schema_item: _SI) -> _SI:
266 if "info" in self.__dict__:
267 schema_item.info = self.info.copy()
268 if isinstance(self, DialectKWArgs):
269 assert isinstance(schema_item, DialectKWArgs)
270 self._copy_reflect_only_elements(schema_item)
271 schema_item.dispatch._update(self.dispatch)
272 return schema_item
274 _use_schema_map = True
277class HasConditionalDDL:
278 """define a class that includes the :meth:`.HasConditionalDDL.ddl_if`
279 method, allowing for conditional rendering of DDL.
281 Currently applies to constraints and indexes.
283 .. versionadded:: 2.0
286 """
288 _ddl_if: Optional[ddl.DDLIf] = None
290 def ddl_if(
291 self,
292 dialect: Optional[str] = None,
293 callable_: Optional[ddl.DDLIfCallable] = None,
294 state: Optional[Any] = None,
295 ) -> Self:
296 r"""apply a conditional DDL rule to this schema item.
298 These rules work in a similar manner to the
299 :meth:`.ExecutableDDLElement.execute_if` callable, with the added
300 feature that the criteria may be checked within the DDL compilation
301 phase for a construct such as :class:`.CreateTable`.
302 :meth:`.HasConditionalDDL.ddl_if` currently applies towards the
303 :class:`.Index` construct as well as all :class:`.Constraint`
304 constructs.
306 :param dialect: string name of a dialect, or a tuple of string names
307 to indicate multiple dialect types.
309 :param callable\_: a callable that is constructed using the same form
310 as that described in
311 :paramref:`.ExecutableDDLElement.execute_if.callable_`.
313 :param state: any arbitrary object that will be passed to the
314 callable, if present.
316 .. versionadded:: 2.0
318 .. seealso::
320 :ref:`schema_ddl_ddl_if` - background and usage examples
323 """
324 self._ddl_if = ddl.DDLIf(dialect, callable_, state)
325 return self
328class HasSchemaAttr(SchemaItem):
329 """schema item that includes a top-level schema name"""
331 schema: Optional[str]
334class Table(
335 DialectKWArgs,
336 HasSchemaAttr,
337 TableClause[_ColCC_co],
338 inspection.Inspectable["Table"],
339):
340 r"""Represent a table in a database.
342 e.g.::
344 from sqlalchemy import Table, MetaData, Integer, String, Column
346 metadata = MetaData()
348 mytable = Table(
349 "mytable",
350 metadata,
351 Column("mytable_id", Integer, primary_key=True),
352 Column("value", String(50)),
353 )
355 The :class:`_schema.Table`
356 object constructs a unique instance of itself based
357 on its name and optional schema name within the given
358 :class:`_schema.MetaData` object. Calling the :class:`_schema.Table`
359 constructor with the same name and same :class:`_schema.MetaData` argument
360 a second time will return the *same* :class:`_schema.Table`
361 object - in this way
362 the :class:`_schema.Table` constructor acts as a registry function.
364 May also be defined as "typed table" by passing a subclass of
365 :class:`_schema.TypedColumns` as the 3rd argument::
367 from sqlalchemy import TypedColumns, select
370 class user_cols(TypedColumns):
371 id = Column(Integer, primary_key=True)
372 name: Column[str]
373 age: Column[int]
374 middle_name: Column[str | None]
376 # optional, used to infer the select types when selecting the table
377 __row_pos__: tuple[int, str, int, str | None]
380 user = Table("user", metadata, user_cols)
382 # the columns are typed: the statement has type Select[int, str]
383 stmt = sa.select(user.c.id, user.c.name).where(user.c.age > 30)
385 # Inferred as Select[int, str, int, str | None] thanks to __row_pos__
386 stmt1 = user.select()
387 stmt2 = sa.select(user)
389 The :attr:`sqlalchemy.sql._annotated_cols.HasRowPos.__row_pos__`
390 annotation is optional, and it's used to infer the types in a
391 :class:`_sql.Select` when selecting the complete table.
392 If a :class:`_schema.TypedColumns` does not define it,
393 the default ``Select[*tuple[Any]]`` will be inferred.
395 An existing :class:`Table` can be casted as "typed table" using
396 the :meth:`Table.with_cols`::
398 class mytable_cols(TypedColumns):
399 mytable_id: Column[int]
400 value: Column[str | None]
403 typed_mytable = mytable.with_cols(mytable_cols)
405 .. seealso::
407 :ref:`metadata_describing` Introduction to database metadata
409 :class:`_schema.TypedColumns` More information about typed column
410 definition
412 .. versionchanged:: 2.1.0b2 - :class:`_schema.Table` is now generic to
413 support "typed tables"
414 """
416 __visit_name__ = "table"
418 if TYPE_CHECKING:
420 @util.ro_non_memoized_property
421 def primary_key(self) -> PrimaryKeyConstraint: ...
423 @util.ro_non_memoized_property
424 def foreign_keys(self) -> Set[ForeignKey]: ...
426 def with_cols(self, type_: type[_TC_co]) -> Table[_TC_co]: ...
428 _columns: DedupeColumnCollection[Column[Any]] # type: ignore[assignment]
430 _sentinel_column: Optional[Column[Any]]
432 constraints: Set[Constraint]
433 """A collection of all :class:`_schema.Constraint` objects associated with
434 this :class:`_schema.Table`.
436 Includes :class:`_schema.PrimaryKeyConstraint`,
437 :class:`_schema.ForeignKeyConstraint`, :class:`_schema.UniqueConstraint`,
438 :class:`_schema.CheckConstraint`. A separate collection
439 :attr:`_schema.Table.foreign_key_constraints` refers to the collection
440 of all :class:`_schema.ForeignKeyConstraint` objects, and the
441 :attr:`_schema.Table.primary_key` attribute refers to the single
442 :class:`_schema.PrimaryKeyConstraint` associated with the
443 :class:`_schema.Table`.
445 .. seealso::
447 :attr:`_schema.Table.constraints`
449 :attr:`_schema.Table.primary_key`
451 :attr:`_schema.Table.foreign_key_constraints`
453 :attr:`_schema.Table.indexes`
455 :class:`_reflection.Inspector`
458 """
460 indexes: Set[Index]
461 """A collection of all :class:`_schema.Index` objects associated with this
462 :class:`_schema.Table`.
464 .. seealso::
466 :meth:`_reflection.Inspector.get_indexes`
468 """
470 def _gen_cache_key(
471 self, anon_map: anon_map, bindparams: List[BindParameter[Any]]
472 ) -> Tuple[Any, ...]:
473 if self._annotations:
474 return (self,) + self._annotations_cache_key
475 else:
476 return (self,)
478 if not typing.TYPE_CHECKING:
479 # typing tools seem to be inconsistent in how they handle
480 # __new__, so suggest this pattern for classes that use
481 # __new__. apply typing to the __init__ method normally
482 @util.deprecated_params(
483 mustexist=(
484 "1.4",
485 "Deprecated alias of :paramref:`_schema.Table.must_exist`",
486 ),
487 )
488 def __new__(cls, *args: Any, **kw: Any) -> Any:
489 return cls._new(*args, **kw)
491 @classmethod
492 def _new(cls, *args: Any, **kw: Any) -> Any:
493 if not args and not kw:
494 # python3k pickle seems to call this
495 return object.__new__(cls)
497 try:
498 name, metadata, *other_args = args
499 except ValueError:
500 raise TypeError(
501 "Table() takes at least two positional-only "
502 "arguments 'name', and 'metadata'"
503 ) from None
504 if other_args and isinstance(other_args[0], type):
505 typed_columns_cls = other_args[0]
506 if not issubclass(typed_columns_cls, TypedColumns):
507 raise exc.InvalidRequestError(
508 "The ``typed_columns_cls`` argument requires a "
509 "TypedColumns subclass."
510 )
511 elif hasattr(typed_columns_cls, "_sa_class_manager"):
512 # an orm class subclassed with TypedColumns. Reject it
513 raise exc.InvalidRequestError(
514 "To get a typed table from an ORM class, use the "
515 "`as_typed_table()` function instead."
516 )
518 extracted_columns = _extract_columns_from_class(typed_columns_cls)
519 other_args = extracted_columns + other_args[1:]
520 elif "typed_columns_cls" in kw:
521 raise TypeError(
522 "The ``typed_columns_cls`` argument may be passed "
523 "only positionally"
524 )
526 schema = kw.get("schema", None)
527 if schema is None:
528 schema = metadata.schema
529 elif schema is BLANK_SCHEMA:
530 schema = None
531 keep_existing = kw.get("keep_existing", False)
532 extend_existing = kw.get("extend_existing", False)
534 if keep_existing and extend_existing:
535 msg = "keep_existing and extend_existing are mutually exclusive."
536 raise exc.ArgumentError(msg)
538 must_exist = kw.pop("must_exist", kw.pop("mustexist", False))
539 key = _get_table_key(name, schema)
540 if key in metadata.tables:
541 if not keep_existing and not extend_existing and bool(other_args):
542 raise exc.InvalidRequestError(
543 f"Table '{key}' is already defined for this MetaData "
544 "instance. Specify 'extend_existing=True' "
545 "to redefine "
546 "options and columns on an "
547 "existing Table object."
548 )
549 table = metadata.tables[key]
550 if extend_existing:
551 table._init_existing(*other_args, **kw)
552 return table
553 else:
554 if must_exist:
555 raise exc.InvalidRequestError(f"Table '{key}' not defined")
556 table = object.__new__(cls)
557 table.dispatch.before_parent_attach(table, metadata)
558 metadata._add_table(name, schema, table)
559 try:
560 table.__init__(name, metadata, *other_args, _no_init=False, **kw) # type: ignore[misc] # noqa: E501
561 table.dispatch.after_parent_attach(table, metadata)
562 return table
563 except Exception:
564 with util.safe_reraise():
565 metadata._remove_table(name, schema)
567 @overload
568 def __init__(
569 self: Table[_TC_co],
570 name: str,
571 metadata: MetaData,
572 typed_columns_cls: type[_TC_co],
573 /,
574 *args: SchemaItem,
575 schema: str | Literal[SchemaConst.BLANK_SCHEMA] | None = None,
576 quote: bool | None = None,
577 quote_schema: bool | None = None,
578 keep_existing: bool = False,
579 extend_existing: bool = False,
580 implicit_returning: bool = True,
581 comment: str | None = None,
582 info: dict[Any, Any] | None = None,
583 listeners: (
584 _typing_Sequence[tuple[str, Callable[..., Any]]] | None
585 ) = None,
586 prefixes: _typing_Sequence[str] | None = None,
587 **kw: Any,
588 ) -> None: ...
590 @overload
591 def __init__(
592 self: Table[ReadOnlyColumnCollection[str, Column[Any]]],
593 name: str,
594 metadata: MetaData,
595 /,
596 *args: SchemaItem,
597 schema: str | Literal[SchemaConst.BLANK_SCHEMA] | None = None,
598 quote: bool | None = None,
599 quote_schema: Optional[bool] = None,
600 autoload_with: Optional[Union[Engine, Connection]] = None,
601 autoload_replace: bool = True,
602 keep_existing: bool = False,
603 extend_existing: bool = False,
604 resolve_fks: bool = True,
605 include_columns: Optional[Collection[str]] = None,
606 implicit_returning: bool = True,
607 comment: str | None = None,
608 info: dict[Any, Any] | None = None,
609 listeners: (
610 _typing_Sequence[tuple[str, Callable[..., Any]]] | None
611 ) = None,
612 prefixes: _typing_Sequence[str] | None = None,
613 _creator_ddl: TableCreateDDL | None = None,
614 _dropper_ddl: TableDropDDL | None = None,
615 # used internally in the metadata.reflect() process
616 _extend_on: Optional[Set[Table]] = None,
617 # used by __new__ to bypass __init__
618 _no_init: bool = True,
619 # dialect-specific keyword args
620 **kw: Any,
621 ) -> None: ...
623 def __init__(
624 self,
625 name: str,
626 metadata: MetaData,
627 /,
628 *args: Any,
629 schema: str | Literal[SchemaConst.BLANK_SCHEMA] | None = None,
630 quote: bool | None = None,
631 quote_schema: Optional[bool] = None,
632 autoload_with: Optional[Union[Engine, Connection]] = None,
633 autoload_replace: bool = True,
634 keep_existing: bool = False,
635 extend_existing: bool = False,
636 resolve_fks: bool = True,
637 include_columns: Optional[Collection[str]] = None,
638 implicit_returning: bool = True,
639 comment: str | None = None,
640 info: dict[Any, Any] | None = None,
641 listeners: (
642 _typing_Sequence[tuple[str, Callable[..., Any]]] | None
643 ) = None,
644 prefixes: _typing_Sequence[str] | None = None,
645 _creator_ddl: TableCreateDDL | None = None,
646 _dropper_ddl: TableDropDDL | None = None,
647 # used internally in the metadata.reflect() process
648 _extend_on: Optional[Set[Table]] = None,
649 # used by __new__ to bypass __init__
650 _no_init: bool = True,
651 # dialect-specific keyword args
652 **kw: Any,
653 ) -> None:
654 r"""Constructor for :class:`_schema.Table`.
657 :param name: The name of this table as represented in the database.
659 The table name, along with the value of the ``schema`` parameter,
660 forms a key which uniquely identifies this :class:`_schema.Table`
661 within
662 the owning :class:`_schema.MetaData` collection.
663 Additional calls to :class:`_schema.Table` with the same name,
664 metadata,
665 and schema name will return the same :class:`_schema.Table` object.
667 Names which contain no upper case characters
668 will be treated as case insensitive names, and will not be quoted
669 unless they are a reserved word or contain special characters.
670 A name with any number of upper case characters is considered
671 to be case sensitive, and will be sent as quoted.
673 To enable unconditional quoting for the table name, specify the flag
674 ``quote=True`` to the constructor, or use the :class:`.quoted_name`
675 construct to specify the name.
677 :param metadata: a :class:`_schema.MetaData`
678 object which will contain this
679 table. The metadata is used as a point of association of this table
680 with other tables which are referenced via foreign key. It also
681 may be used to associate this table with a particular
682 :class:`.Connection` or :class:`.Engine`.
684 :param table_columns_cls: a subclass of :class:`_schema.TypedColumns`
685 that defines the columns that will be "typed" when accessing
686 them from the :attr:`_schema.Table.c` attribute.
688 .. versionadded:: 2.1.0b2
690 :param \*args: Additional positional arguments are used primarily
691 to add the list of :class:`_schema.Column`
692 objects contained within this
693 table. Similar to the style of a CREATE TABLE statement, other
694 :class:`.SchemaItem` constructs may be added here, including
695 :class:`.PrimaryKeyConstraint`, and
696 :class:`_schema.ForeignKeyConstraint`.
697 Additional columns may be provided also when using a
698 :paramref:`_schema.Table.table_columns_cls` class; they will
699 be appended to the "typed" columns and will appear as untyped
700 when accessing them via the :attr:`_schema.Table.c` collection.
702 :param autoload_replace: Defaults to ``True``; when using
703 :paramref:`_schema.Table.autoload_with`
704 in conjunction with :paramref:`_schema.Table.extend_existing`,
705 indicates
706 that :class:`_schema.Column` objects present in the already-existing
707 :class:`_schema.Table`
708 object should be replaced with columns of the same
709 name retrieved from the autoload process. When ``False``, columns
710 already present under existing names will be omitted from the
711 reflection process.
713 Note that this setting does not impact :class:`_schema.Column` objects
714 specified programmatically within the call to :class:`_schema.Table`
715 that
716 also is autoloading; those :class:`_schema.Column` objects will always
717 replace existing columns of the same name when
718 :paramref:`_schema.Table.extend_existing` is ``True``.
720 .. seealso::
722 :paramref:`_schema.Table.autoload_with`
724 :paramref:`_schema.Table.extend_existing`
726 :param autoload_with: An :class:`_engine.Engine` or
727 :class:`_engine.Connection` object,
728 or a :class:`_reflection.Inspector` object as returned by
729 :func:`_sa.inspect`
730 against one, with which this :class:`_schema.Table`
731 object will be reflected.
732 When set to a non-None value, the autoload process will take place
733 for this table against the given engine or connection.
735 .. seealso::
737 :ref:`metadata_reflection_toplevel`
739 :meth:`_events.DDLEvents.column_reflect`
741 :ref:`metadata_reflection_dbagnostic_types`
743 :param extend_existing: When ``True``, indicates that if this
744 :class:`_schema.Table` is already present in the given
745 :class:`_schema.MetaData`,
746 apply further arguments within the constructor to the existing
747 :class:`_schema.Table`.
749 If :paramref:`_schema.Table.extend_existing` or
750 :paramref:`_schema.Table.keep_existing` are not set,
751 and the given name
752 of the new :class:`_schema.Table` refers to a :class:`_schema.Table`
753 that is
754 already present in the target :class:`_schema.MetaData` collection,
755 and
756 this :class:`_schema.Table`
757 specifies additional columns or other constructs
758 or flags that modify the table's state, an
759 error is raised. The purpose of these two mutually-exclusive flags
760 is to specify what action should be taken when a
761 :class:`_schema.Table`
762 is specified that matches an existing :class:`_schema.Table`,
763 yet specifies
764 additional constructs.
766 :paramref:`_schema.Table.extend_existing`
767 will also work in conjunction
768 with :paramref:`_schema.Table.autoload_with` to run a new reflection
769 operation against the database, even if a :class:`_schema.Table`
770 of the same name is already present in the target
771 :class:`_schema.MetaData`; newly reflected :class:`_schema.Column`
772 objects
773 and other options will be added into the state of the
774 :class:`_schema.Table`, potentially overwriting existing columns
775 and options of the same name.
777 As is always the case with :paramref:`_schema.Table.autoload_with`,
778 :class:`_schema.Column` objects can be specified in the same
779 :class:`_schema.Table`
780 constructor, which will take precedence. Below, the existing
781 table ``mytable`` will be augmented with :class:`_schema.Column`
782 objects
783 both reflected from the database, as well as the given
784 :class:`_schema.Column`
785 named "y"::
787 Table(
788 "mytable",
789 metadata,
790 Column("y", Integer),
791 extend_existing=True,
792 autoload_with=engine,
793 )
795 .. seealso::
797 :paramref:`_schema.Table.autoload_with`
799 :paramref:`_schema.Table.autoload_replace`
801 :paramref:`_schema.Table.keep_existing`
804 :param implicit_returning: True by default - indicates that
805 RETURNING can be used, typically by the ORM, in order to fetch
806 server-generated values such as primary key values and
807 server side defaults, on those backends which support RETURNING.
809 In modern SQLAlchemy there is generally no reason to alter this
810 setting, except for some backend specific cases
811 (see :ref:`mssql_triggers` in the SQL Server dialect documentation
812 for one such example).
814 :param include_columns: A list of strings indicating a subset of
815 columns to be loaded via the ``autoload`` operation; table columns who
816 aren't present in this list will not be represented on the resulting
817 ``Table`` object. Defaults to ``None`` which indicates all columns
818 should be reflected.
820 :param resolve_fks: Whether or not to reflect :class:`_schema.Table`
821 objects
822 related to this one via :class:`_schema.ForeignKey` objects, when
823 :paramref:`_schema.Table.autoload_with` is
824 specified. Defaults to True. Set to False to disable reflection of
825 related tables as :class:`_schema.ForeignKey`
826 objects are encountered; may be
827 used either to save on SQL calls or to avoid issues with related tables
828 that can't be accessed. Note that if a related table is already present
829 in the :class:`_schema.MetaData` collection, or becomes present later,
830 a
831 :class:`_schema.ForeignKey` object associated with this
832 :class:`_schema.Table` will
833 resolve to that table normally.
835 .. seealso::
837 :paramref:`.MetaData.reflect.resolve_fks`
840 :param info: Optional data dictionary which will be populated into the
841 :attr:`.SchemaItem.info` attribute of this object.
843 :param keep_existing: When ``True``, indicates that if this Table
844 is already present in the given :class:`_schema.MetaData`, ignore
845 further arguments within the constructor to the existing
846 :class:`_schema.Table`, and return the :class:`_schema.Table`
847 object as
848 originally created. This is to allow a function that wishes
849 to define a new :class:`_schema.Table` on first call, but on
850 subsequent calls will return the same :class:`_schema.Table`,
851 without any of the declarations (particularly constraints)
852 being applied a second time.
854 If :paramref:`_schema.Table.extend_existing` or
855 :paramref:`_schema.Table.keep_existing` are not set,
856 and the given name
857 of the new :class:`_schema.Table` refers to a :class:`_schema.Table`
858 that is
859 already present in the target :class:`_schema.MetaData` collection,
860 and
861 this :class:`_schema.Table`
862 specifies additional columns or other constructs
863 or flags that modify the table's state, an
864 error is raised. The purpose of these two mutually-exclusive flags
865 is to specify what action should be taken when a
866 :class:`_schema.Table`
867 is specified that matches an existing :class:`_schema.Table`,
868 yet specifies
869 additional constructs.
871 .. seealso::
873 :paramref:`_schema.Table.extend_existing`
875 :param listeners: A list of tuples of the form ``(<eventname>, <fn>)``
876 which will be passed to :func:`.event.listen` upon construction.
877 This alternate hook to :func:`.event.listen` allows the establishment
878 of a listener function specific to this :class:`_schema.Table` before
879 the "autoload" process begins. Historically this has been intended
880 for use with the :meth:`.DDLEvents.column_reflect` event, however
881 note that this event hook may now be associated with the
882 :class:`_schema.MetaData` object directly::
884 def listen_for_reflect(table, column_info):
885 "handle the column reflection event"
886 # ...
889 t = Table(
890 "sometable",
891 autoload_with=engine,
892 listeners=[("column_reflect", listen_for_reflect)],
893 )
895 .. seealso::
897 :meth:`_events.DDLEvents.column_reflect`
899 :param must_exist: When ``True``, indicates that this Table must already
900 be present in the given :class:`_schema.MetaData` collection, else
901 an exception is raised.
903 :param prefixes:
904 A list of strings to insert after CREATE in the CREATE TABLE
905 statement. They will be separated by spaces.
907 :param quote: Force quoting of this table's name on or off, corresponding
908 to ``True`` or ``False``. When left at its default of ``None``,
909 the column identifier will be quoted according to whether the name is
910 case sensitive (identifiers with at least one upper case character are
911 treated as case sensitive), or if it's a reserved word. This flag
912 is only needed to force quoting of a reserved word which is not known
913 by the SQLAlchemy dialect.
915 .. note:: setting this flag to ``False`` will not provide
916 case-insensitive behavior for table reflection; table reflection
917 will always search for a mixed-case name in a case sensitive
918 fashion. Case insensitive names are specified in SQLAlchemy only
919 by stating the name with all lower case characters.
921 :param quote_schema: same as 'quote' but applies to the schema identifier.
923 :param schema: The schema name for this table, which is required if
924 the table resides in a schema other than the default selected schema
925 for the engine's database connection. Defaults to ``None``.
927 If the owning :class:`_schema.MetaData` of this :class:`_schema.Table`
928 specifies its
929 own :paramref:`_schema.MetaData.schema` parameter,
930 then that schema name will
931 be applied to this :class:`_schema.Table`
932 if the schema parameter here is set
933 to ``None``. To set a blank schema name on a :class:`_schema.Table`
934 that
935 would otherwise use the schema set on the owning
936 :class:`_schema.MetaData`,
937 specify the special symbol :attr:`.BLANK_SCHEMA`.
939 The quoting rules for the schema name are the same as those for the
940 ``name`` parameter, in that quoting is applied for reserved words or
941 case-sensitive names; to enable unconditional quoting for the schema
942 name, specify the flag ``quote_schema=True`` to the constructor, or use
943 the :class:`.quoted_name` construct to specify the name.
945 :param comment: Optional string that will render an SQL comment on table
946 creation.
948 :param \**kw: Additional keyword arguments not mentioned above are
949 dialect specific, and passed in the form ``<dialectname>_<argname>``.
950 See the documentation regarding an individual dialect at
951 :ref:`dialect_toplevel` for detail on documented arguments.
953 """ # noqa: E501
954 if _no_init:
955 # don't run __init__ from __new__ by default;
956 # __new__ has a specific place that __init__ is called
957 return
958 if args:
959 # this is the call done by `__new__` that should have resolved
960 # TypedColumns to the individual columns
961 assert not (
962 isinstance(args[0], type) and issubclass(args[0], TypedColumns)
963 )
965 super().__init__(quoted_name(name, quote))
966 self.metadata = metadata
968 if schema is None:
969 self.schema = metadata.schema
970 elif schema is BLANK_SCHEMA:
971 self.schema = None
972 else:
973 quote_schema = quote_schema
974 assert isinstance(schema, str)
975 self.schema = quoted_name(schema, quote_schema)
977 self._sentinel_column = None
978 self._creator_ddl = _creator_ddl
979 self._dropper_ddl = _dropper_ddl
981 self.indexes = set()
982 self.constraints = set()
983 PrimaryKeyConstraint(
984 _implicit_generated=True
985 )._set_parent_with_dispatch(self)
986 self.foreign_keys = set() # type: ignore[misc]
987 self._extra_dependencies: Set[Table] = set()
988 if self.schema is not None:
989 self.fullname = "%s.%s" % (self.schema, self.name)
990 else:
991 self.fullname = self.name
993 self.implicit_returning = implicit_returning
994 _reflect_info = kw.pop("_reflect_info", None)
996 self.comment = comment
998 if info is not None:
999 self.info = info
1001 if listeners is not None:
1002 for evt, fn in listeners:
1003 event.listen(self, evt, fn)
1005 self._prefixes = prefixes if prefixes else []
1007 self._extra_kwargs(**kw)
1009 # load column definitions from the database if 'autoload' is defined
1010 # we do it after the table is in the singleton dictionary to support
1011 # circular foreign keys
1012 if autoload_with is not None:
1013 self._autoload(
1014 metadata,
1015 autoload_with,
1016 include_columns,
1017 _extend_on=_extend_on,
1018 _reflect_info=_reflect_info,
1019 resolve_fks=resolve_fks,
1020 )
1022 # initialize all the column, etc. objects. done after reflection to
1023 # allow user-overrides
1025 self._init_items(
1026 *args,
1027 allow_replacements=extend_existing
1028 or keep_existing
1029 or autoload_with,
1030 all_names={},
1031 )
1033 def set_creator_ddl(self, ddl: TableCreateDDL) -> None:
1034 """Set the table create DDL for this :class:`.Table`.
1036 This allows the CREATE TABLE statement to be controlled or replaced
1037 entirely when :meth:`.Table.create` or :meth:`.MetaData.create_all` is
1038 used.
1040 E.g.::
1042 from sqlalchemy.schema import CreateTable
1044 table.set_creator_ddl(CreateTable(table, if_not_exists=True))
1046 .. versionadded:: 2.1
1048 .. seealso::
1050 :meth:`.Table.set_dropper_ddl`
1052 """
1053 self._creator_ddl = ddl
1055 def set_dropper_ddl(self, ddl: TableDropDDL) -> None:
1056 """Set the table drop DDL for this :class:`.Table`.
1058 This allows the DROP TABLE statement to be controlled or replaced
1059 entirely when :meth:`.Table.drop` or :meth:`.MetaData.drop_all` is
1060 used.
1062 E.g.::
1064 from sqlalchemy.schema import DropTable
1066 table.set_dropper_ddl(DropTable(table, if_exists=True))
1068 .. versionadded:: 2.1
1070 .. seealso::
1072 :meth:`.Table.set_creator_ddl`
1074 """
1075 self._dropper_ddl = ddl
1077 @property
1078 def is_view(self) -> bool:
1079 """True if this table, when DDL for CREATE is emitted, will emit
1080 CREATE VIEW rather than CREATE TABLE.
1082 .. versionadded:: 2.1
1084 """
1085 return isinstance(self._creator_ddl, ddl.CreateView)
1087 def _autoload(
1088 self,
1089 metadata: MetaData,
1090 autoload_with: Union[Engine, Connection],
1091 include_columns: Optional[Collection[str]],
1092 exclude_columns: Collection[str] = (),
1093 resolve_fks: bool = True,
1094 _extend_on: Optional[Set[Table]] = None,
1095 _reflect_info: _ReflectionInfo | None = None,
1096 ) -> None:
1097 insp = inspection.inspect(autoload_with)
1098 with insp._inspection_context() as conn_insp:
1099 conn_insp.reflect_table(
1100 self,
1101 include_columns,
1102 exclude_columns,
1103 resolve_fks,
1104 _extend_on=_extend_on,
1105 _reflect_info=_reflect_info,
1106 )
1108 @property
1109 def _sorted_constraints(self) -> List[Constraint]:
1110 """Return the set of constraints as a list, sorted by creation
1111 order.
1113 """
1115 return sorted(self.constraints, key=lambda c: c._creation_order)
1117 @property
1118 def foreign_key_constraints(self) -> Set[ForeignKeyConstraint]:
1119 """:class:`_schema.ForeignKeyConstraint` objects referred to by this
1120 :class:`_schema.Table`.
1122 This list is produced from the collection of
1123 :class:`_schema.ForeignKey`
1124 objects currently associated.
1127 .. seealso::
1129 :attr:`_schema.Table.constraints`
1131 :attr:`_schema.Table.foreign_keys`
1133 :attr:`_schema.Table.indexes`
1135 """
1136 return {
1137 fkc.constraint
1138 for fkc in self.foreign_keys
1139 if fkc.constraint is not None
1140 }
1142 def _init_existing(self, *args: Any, **kwargs: Any) -> None:
1143 autoload_with = kwargs.pop("autoload_with", None)
1144 autoload = kwargs.pop("autoload", autoload_with is not None)
1145 autoload_replace = kwargs.pop("autoload_replace", True)
1146 schema = kwargs.pop("schema", None)
1147 _extend_on = kwargs.pop("_extend_on", None)
1148 _reflect_info = kwargs.pop("_reflect_info", None)
1150 # these arguments are only used with _init()
1151 extend_existing = kwargs.pop("extend_existing", False)
1152 keep_existing = kwargs.pop("keep_existing", False)
1154 assert extend_existing
1155 assert not keep_existing
1157 if schema and schema != self.schema:
1158 raise exc.ArgumentError(
1159 f"Can't change schema of existing table "
1160 f"from '{self.schema}' to '{schema}'",
1161 )
1163 include_columns = kwargs.pop("include_columns", None)
1164 if include_columns is not None:
1165 for c in self.c:
1166 if c.name not in include_columns:
1167 self._columns.remove(c)
1169 resolve_fks = kwargs.pop("resolve_fks", True)
1171 for key in ("quote", "quote_schema"):
1172 if key in kwargs:
1173 raise exc.ArgumentError(
1174 "Can't redefine 'quote' or 'quote_schema' arguments"
1175 )
1177 # update `self` with these kwargs, if provided
1178 self.comment = kwargs.pop("comment", self.comment)
1179 self.implicit_returning = kwargs.pop(
1180 "implicit_returning", self.implicit_returning
1181 )
1182 self.info = kwargs.pop("info", self.info)
1184 exclude_columns: _typing_Sequence[str]
1186 if autoload:
1187 if not autoload_replace:
1188 # don't replace columns already present.
1189 # we'd like to do this for constraints also however we don't
1190 # have simple de-duping for unnamed constraints.
1191 exclude_columns = [c.name for c in self.c]
1192 else:
1193 exclude_columns = ()
1194 self._autoload(
1195 self.metadata,
1196 autoload_with,
1197 include_columns,
1198 exclude_columns,
1199 resolve_fks,
1200 _extend_on=_extend_on,
1201 _reflect_info=_reflect_info,
1202 )
1204 all_names = {c.name: c for c in self.c}
1205 self._extra_kwargs(**kwargs)
1206 self._init_items(*args, allow_replacements=True, all_names=all_names)
1208 def _extra_kwargs(self, **kwargs: Any) -> None:
1209 self._validate_dialect_kwargs(kwargs)
1211 def _init_collections(self) -> None:
1212 pass
1214 def _reset_exported(self) -> None:
1215 pass
1217 @util.ro_non_memoized_property
1218 def _autoincrement_column(self) -> Optional[Column[int]]:
1219 return self.primary_key._autoincrement_column
1221 @util.ro_memoized_property
1222 def _sentinel_column_characteristics(
1223 self,
1224 ) -> _SentinelColumnCharacterization:
1225 """determine a candidate column (or columns, in case of a client
1226 generated composite primary key) which can be used as an
1227 "insert sentinel" for an INSERT statement.
1229 The returned structure, :class:`_SentinelColumnCharacterization`,
1230 includes all the details needed by :class:`.Dialect` and
1231 :class:`.SQLCompiler` to determine if these column(s) can be used
1232 as an INSERT..RETURNING sentinel for a particular database
1233 dialect.
1235 .. versionadded:: 2.0.10
1237 """
1239 sentinel_is_explicit = False
1240 sentinel_is_autoinc = False
1241 the_sentinel: Optional[_typing_Sequence[Column[Any]]] = None
1243 # see if a column was explicitly marked "insert_sentinel=True".
1244 explicit_sentinel_col = self._sentinel_column
1246 if explicit_sentinel_col is not None:
1247 the_sentinel = (explicit_sentinel_col,)
1248 sentinel_is_explicit = True
1250 autoinc_col = self._autoincrement_column
1251 if sentinel_is_explicit and explicit_sentinel_col is autoinc_col:
1252 assert autoinc_col is not None
1253 sentinel_is_autoinc = True
1254 elif explicit_sentinel_col is None and autoinc_col is not None:
1255 the_sentinel = (autoinc_col,)
1256 sentinel_is_autoinc = True
1258 default_characterization = _SentinelDefaultCharacterization.UNKNOWN
1260 if the_sentinel:
1261 the_sentinel_zero = the_sentinel[0]
1262 if the_sentinel_zero.identity:
1263 if the_sentinel_zero.identity._increment_is_negative:
1264 if sentinel_is_explicit:
1265 raise exc.InvalidRequestError(
1266 "Can't use IDENTITY default with negative "
1267 "increment as an explicit sentinel column"
1268 )
1269 else:
1270 if sentinel_is_autoinc:
1271 autoinc_col = None
1272 sentinel_is_autoinc = False
1273 the_sentinel = None
1274 else:
1275 default_characterization = (
1276 _SentinelDefaultCharacterization.IDENTITY
1277 )
1278 elif (
1279 the_sentinel_zero.default is None
1280 and the_sentinel_zero.server_default is None
1281 ):
1282 if the_sentinel_zero.nullable:
1283 raise exc.InvalidRequestError(
1284 f"Column {the_sentinel_zero} has been marked as a "
1285 "sentinel "
1286 "column with no default generation function; it "
1287 "at least needs to be marked nullable=False assuming "
1288 "user-populated sentinel values will be used."
1289 )
1290 default_characterization = (
1291 _SentinelDefaultCharacterization.NONE
1292 )
1293 elif the_sentinel_zero.default is not None:
1294 if the_sentinel_zero.default.is_sentinel:
1295 default_characterization = (
1296 _SentinelDefaultCharacterization.SENTINEL_DEFAULT
1297 )
1298 elif the_sentinel_zero.default._is_monotonic_fn:
1299 default_characterization = (
1300 _SentinelDefaultCharacterization.MONOTONIC_FUNCTION
1301 )
1302 elif default_is_sequence(the_sentinel_zero.default):
1303 if the_sentinel_zero.default._increment_is_negative:
1304 if sentinel_is_explicit:
1305 raise exc.InvalidRequestError(
1306 "Can't use SEQUENCE default with negative "
1307 "increment as an explicit sentinel column"
1308 )
1309 else:
1310 if sentinel_is_autoinc:
1311 autoinc_col = None
1312 sentinel_is_autoinc = False
1313 the_sentinel = None
1315 default_characterization = (
1316 _SentinelDefaultCharacterization.SEQUENCE
1317 )
1318 elif the_sentinel_zero.default.is_callable:
1319 default_characterization = (
1320 _SentinelDefaultCharacterization.CLIENTSIDE
1321 )
1322 elif the_sentinel_zero.server_default is not None:
1323 if sentinel_is_explicit:
1324 if not the_sentinel_zero.server_default._is_monotonic_fn:
1325 raise exc.InvalidRequestError(
1326 f"Column {the_sentinel[0]} can't be a sentinel "
1327 "column "
1328 "because it uses an explicit server side default "
1329 "that's not the Identity() default."
1330 )
1331 else:
1332 default_characterization = (
1333 _SentinelDefaultCharacterization.MONOTONIC_FUNCTION
1334 )
1335 else:
1336 default_characterization = (
1337 _SentinelDefaultCharacterization.SERVERSIDE
1338 )
1340 if the_sentinel is None and self.primary_key:
1341 assert autoinc_col is None
1343 # determine for non-autoincrement pk if all elements are
1344 # client side
1345 for _pkc in self.primary_key:
1346 if (
1347 _pkc.server_default is not None
1348 and not _pkc.server_default._is_monotonic_fn
1349 ):
1350 break
1352 if (
1353 _pkc.default
1354 and not _pkc.default.is_callable
1355 and not _pkc.default._is_monotonic_fn
1356 ):
1357 break
1358 else:
1359 the_sentinel = tuple(self.primary_key)
1360 default_characterization = (
1361 _SentinelDefaultCharacterization.CLIENTSIDE
1362 )
1364 return _SentinelColumnCharacterization(
1365 the_sentinel,
1366 sentinel_is_explicit,
1367 sentinel_is_autoinc,
1368 default_characterization,
1369 )
1371 @property
1372 def autoincrement_column(self) -> Optional[Column[int]]:
1373 """Returns the :class:`.Column` object which currently represents
1374 the "auto increment" column, if any, else returns None.
1376 This is based on the rules for :class:`.Column` as defined by the
1377 :paramref:`.Column.autoincrement` parameter, which generally means the
1378 column within a single integer column primary key constraint that is
1379 not constrained by a foreign key. If the table does not have such
1380 a primary key constraint, then there's no "autoincrement" column.
1381 A :class:`.Table` may have only one column defined as the
1382 "autoincrement" column.
1384 .. versionadded:: 2.0.4
1386 .. seealso::
1388 :paramref:`.Column.autoincrement`
1390 """
1391 return self._autoincrement_column
1393 @property
1394 def key(self) -> str:
1395 """Return the 'key' for this :class:`_schema.Table`.
1397 This value is used as the dictionary key within the
1398 :attr:`_schema.MetaData.tables` collection. It is typically the same
1399 as that of :attr:`_schema.Table.name` for a table with no
1400 :attr:`_schema.Table.schema`
1401 set; otherwise it is typically of the form
1402 ``schemaname.tablename``.
1404 """
1405 return _get_table_key(self.name, self.schema)
1407 def __repr__(self) -> str:
1408 return "Table(%s)" % ", ".join(
1409 [repr(self.name)]
1410 + [repr(self.metadata)]
1411 + [repr(x) for x in self.columns]
1412 + ["%s=%s" % (k, repr(getattr(self, k))) for k in ["schema"]]
1413 )
1415 def __str__(self) -> str:
1416 return _get_table_key(self.description, self.schema)
1418 def add_is_dependent_on(self, table: Table) -> None:
1419 """Add a 'dependency' for this Table.
1421 This is another Table object which must be created
1422 first before this one can, or dropped after this one.
1424 Usually, dependencies between tables are determined via
1425 ForeignKey objects. However, for other situations that
1426 create dependencies outside of foreign keys (rules, inheriting),
1427 this method can manually establish such a link.
1429 """
1430 self._extra_dependencies.add(table)
1432 def _insert_col_impl(
1433 self,
1434 column: ColumnClause[Any],
1435 *,
1436 index: Optional[int] = None,
1437 replace_existing: bool = False,
1438 ) -> None:
1439 try:
1440 column._set_parent_with_dispatch(
1441 self,
1442 allow_replacements=replace_existing,
1443 all_names={c.name: c for c in self.c},
1444 index=index,
1445 )
1446 except exc.DuplicateColumnError as de:
1447 raise exc.DuplicateColumnError(
1448 f"{de.args[0]} Specify replace_existing=True to "
1449 "Table.append_column() or Table.insert_column() to replace an "
1450 "existing column."
1451 ) from de
1453 def insert_column(
1454 self,
1455 column: ColumnClause[Any],
1456 index: int,
1457 *,
1458 replace_existing: bool = False,
1459 ) -> None:
1460 """Insert a :class:`_schema.Column` to this :class:`_schema.Table` at
1461 a specific position.
1463 Behavior is identical to :meth:`.Table.append_column` except that
1464 the index position can be controlled using the
1465 :paramref:`.Table.insert_column.index`
1466 parameter.
1468 :param replace_existing:
1469 see :paramref:`.Table.append_column.replace_existing`
1470 :param index: integer index to insert the new column.
1472 .. versionadded:: 2.1
1474 """
1475 self._insert_col_impl(
1476 column, index=index, replace_existing=replace_existing
1477 )
1479 def append_column(
1480 self, column: ColumnClause[Any], *, replace_existing: bool = False
1481 ) -> None:
1482 """Append a :class:`_schema.Column` to this :class:`_schema.Table`.
1484 The "key" of the newly added :class:`_schema.Column`, i.e. the
1485 value of its ``.key`` attribute, will then be available
1486 in the ``.c`` collection of this :class:`_schema.Table`, and the
1487 column definition will be included in any CREATE TABLE, SELECT,
1488 UPDATE, etc. statements generated from this :class:`_schema.Table`
1489 construct.
1491 Note that this does **not** change the definition of the table
1492 as it exists within any underlying database, assuming that
1493 table has already been created in the database. Relational
1494 databases support the addition of columns to existing tables
1495 using the SQL ALTER command, which would need to be
1496 emitted for an already-existing table that doesn't contain
1497 the newly added column.
1499 :param replace_existing: When ``True``, allows replacing existing
1500 columns. When ``False``, the default, an warning will be raised
1501 if a column with the same ``.key`` already exists. A future
1502 version of sqlalchemy will instead rise a warning.
1504 .. versionadded:: 1.4.0
1506 .. seealso::
1508 :meth:`.Table.insert_column`
1510 """
1511 self._insert_col_impl(column, replace_existing=replace_existing)
1513 def append_constraint(self, constraint: Union[Index, Constraint]) -> None:
1514 """Append a :class:`_schema.Constraint` to this
1515 :class:`_schema.Table`.
1517 This has the effect of the constraint being included in any
1518 future CREATE TABLE statement, assuming specific DDL creation
1519 events have not been associated with the given
1520 :class:`_schema.Constraint` object.
1522 Note that this does **not** produce the constraint within the
1523 relational database automatically, for a table that already exists
1524 in the database. To add a constraint to an
1525 existing relational database table, the SQL ALTER command must
1526 be used. SQLAlchemy also provides the
1527 :class:`.AddConstraint` construct which can produce this SQL when
1528 invoked as an executable clause.
1530 """
1532 constraint._set_parent_with_dispatch(self)
1534 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
1535 metadata = parent
1536 assert isinstance(metadata, MetaData)
1537 metadata._add_table(self.name, self.schema, self)
1538 self.metadata = metadata
1540 def create(
1541 self,
1542 bind: _CreateDropBind,
1543 checkfirst: Union[bool, CheckFirst] = CheckFirst.TYPES,
1544 ) -> None:
1545 """Issue a ``CREATE`` statement for this
1546 :class:`_schema.Table`, using the given
1547 :class:`.Connection` or :class:`.Engine`
1548 for connectivity.
1550 .. seealso::
1552 :meth:`_schema.MetaData.create_all`.
1554 """
1556 # the default is to only check for schema objects
1557 bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
1559 def drop(
1560 self,
1561 bind: _CreateDropBind,
1562 checkfirst: Union[bool, CheckFirst] = CheckFirst.NONE,
1563 ) -> None:
1564 """Issue a ``DROP`` statement for this
1565 :class:`_schema.Table`, using the given
1566 :class:`.Connection` or :class:`.Engine` for connectivity.
1568 .. seealso::
1570 :meth:`_schema.MetaData.drop_all`.
1572 """
1573 bind._run_ddl_visitor(ddl.SchemaDropper, self, checkfirst=checkfirst)
1575 @util.deprecated(
1576 "1.4",
1577 ":meth:`_schema.Table.tometadata` is renamed to "
1578 ":meth:`_schema.Table.to_metadata`",
1579 )
1580 def tometadata(
1581 self,
1582 metadata: MetaData,
1583 schema: Union[str, Literal[SchemaConst.RETAIN_SCHEMA]] = RETAIN_SCHEMA,
1584 referred_schema_fn: Optional[
1585 Callable[
1586 [Table, Optional[str], ForeignKeyConstraint, Optional[str]],
1587 Optional[str],
1588 ]
1589 ] = None,
1590 name: Optional[str] = None,
1591 ) -> Table[_ColCC_co]:
1592 """Return a copy of this :class:`_schema.Table`
1593 associated with a different
1594 :class:`_schema.MetaData`.
1596 See :meth:`_schema.Table.to_metadata` for a full description.
1598 """
1599 return self.to_metadata(
1600 metadata,
1601 schema=schema,
1602 referred_schema_fn=referred_schema_fn,
1603 name=name,
1604 )
1606 def to_metadata(
1607 self,
1608 metadata: MetaData,
1609 schema: Union[str, Literal[SchemaConst.RETAIN_SCHEMA]] = RETAIN_SCHEMA,
1610 referred_schema_fn: Optional[
1611 Callable[
1612 [Table, Optional[str], ForeignKeyConstraint, Optional[str]],
1613 Optional[str],
1614 ]
1615 ] = None,
1616 name: Optional[str] = None,
1617 ) -> Table[_ColCC_co]:
1618 """Return a copy of this :class:`_schema.Table` associated with a
1619 different :class:`_schema.MetaData`.
1621 E.g.::
1623 m1 = MetaData()
1625 user = Table("user", m1, Column("id", Integer, primary_key=True))
1627 m2 = MetaData()
1628 user_copy = user.to_metadata(m2)
1630 .. versionchanged:: 1.4 The :meth:`_schema.Table.to_metadata` function
1631 was renamed from :meth:`_schema.Table.tometadata`.
1634 :param metadata: Target :class:`_schema.MetaData` object,
1635 into which the
1636 new :class:`_schema.Table` object will be created.
1638 :param schema: optional string name indicating the target schema.
1639 Defaults to the special symbol :attr:`.RETAIN_SCHEMA` which indicates
1640 that no change to the schema name should be made in the new
1641 :class:`_schema.Table`. If set to a string name, the new
1642 :class:`_schema.Table`
1643 will have this new name as the ``.schema``. If set to ``None``, the
1644 schema will be set to that of the schema set on the target
1645 :class:`_schema.MetaData`, which is typically ``None`` as well,
1646 unless
1647 set explicitly::
1649 m2 = MetaData(schema="newschema")
1651 # user_copy_one will have "newschema" as the schema name
1652 user_copy_one = user.to_metadata(m2, schema=None)
1654 m3 = MetaData() # schema defaults to None
1656 # user_copy_two will have None as the schema name
1657 user_copy_two = user.to_metadata(m3, schema=None)
1659 :param referred_schema_fn: optional callable which can be supplied
1660 in order to provide for the schema name that should be assigned
1661 to the referenced table of a :class:`_schema.ForeignKeyConstraint`.
1662 The callable accepts this parent :class:`_schema.Table`, the
1663 target schema that we are changing to, the
1664 :class:`_schema.ForeignKeyConstraint` object, and the existing
1665 "target schema" of that constraint. The function should return the
1666 string schema name that should be applied. To reset the schema
1667 to "none", return the symbol :data:`.BLANK_SCHEMA`. To effect no
1668 change, return ``None`` or :data:`.RETAIN_SCHEMA`.
1670 .. versionchanged:: 1.4.33 The ``referred_schema_fn`` function
1671 may return the :data:`.BLANK_SCHEMA` or :data:`.RETAIN_SCHEMA`
1672 symbols.
1674 E.g.::
1676 def referred_schema_fn(table, to_schema, constraint, referred_schema):
1677 if referred_schema == "base_tables":
1678 return referred_schema
1679 else:
1680 return to_schema
1683 new_table = table.to_metadata(
1684 m2, schema="alt_schema", referred_schema_fn=referred_schema_fn
1685 )
1687 :param name: optional string name indicating the target table name.
1688 If not specified or None, the table name is retained. This allows
1689 a :class:`_schema.Table` to be copied to the same
1690 :class:`_schema.MetaData` target
1691 with a new name.
1693 """ # noqa: E501
1694 if name is None:
1695 name = self.name
1697 actual_schema: Optional[str]
1699 if schema is RETAIN_SCHEMA:
1700 actual_schema = self.schema
1701 elif schema is None:
1702 actual_schema = metadata.schema
1703 else:
1704 actual_schema = schema
1705 key = _get_table_key(name, actual_schema)
1706 if key in metadata.tables:
1707 util.warn(
1708 f"Table '{self.description}' already exists within the given "
1709 "MetaData - not copying."
1710 )
1711 return metadata.tables[key]
1713 args = []
1714 for col in self.columns:
1715 args.append(col._copy(schema=actual_schema, _to_metadata=metadata))
1717 table: Table[_ColCC_co] = Table( # type: ignore[assignment]
1718 name,
1719 metadata,
1720 schema=actual_schema,
1721 comment=self.comment,
1722 *args,
1723 **self.kwargs,
1724 )
1726 if self._creator_ddl is not None:
1727 table._creator_ddl = self._creator_ddl.to_metadata(metadata, table)
1728 if self._dropper_ddl is not None:
1729 table._dropper_ddl = self._dropper_ddl.to_metadata(metadata, table)
1731 for const in self.constraints:
1732 if isinstance(const, ForeignKeyConstraint):
1733 referred_schema = const._referred_schema
1734 if referred_schema_fn:
1735 fk_constraint_schema = referred_schema_fn(
1736 self, actual_schema, const, referred_schema
1737 )
1738 else:
1739 fk_constraint_schema = (
1740 actual_schema
1741 if referred_schema == self.schema
1742 else None
1743 )
1744 table.append_constraint(
1745 const._copy(
1746 schema=fk_constraint_schema, target_table=table
1747 )
1748 )
1749 elif not const._type_bound:
1750 # skip unique constraints that would be generated
1751 # by the 'unique' flag on Column
1752 if const._column_flag:
1753 continue
1755 table.append_constraint(
1756 const._copy(schema=actual_schema, target_table=table)
1757 )
1758 for index in self.indexes:
1759 # skip indexes that would be generated
1760 # by the 'index' flag on Column
1761 if index._column_flag:
1762 continue
1763 Index(
1764 index.name,
1765 unique=index.unique,
1766 *[
1767 _copy_expression(expr, self, table)
1768 for expr in index._table_bound_expressions
1769 ],
1770 _table=table,
1771 **index.kwargs,
1772 **index._reflect_only_kwargs,
1773 )
1774 return self._schema_item_copy(table)
1777class Column(DialectKWArgs, SchemaItem, ColumnClause[_T], Named[_T]):
1778 """Represents a column in a database table."""
1780 __visit_name__ = "column"
1782 inherit_cache = True
1783 key: str
1785 server_default: Optional[FetchedValue]
1787 def __init__(
1788 self,
1789 __name_pos: Optional[
1790 Union[str, _TypeEngineArgument[_T], SchemaEventTarget]
1791 ] = None,
1792 __type_pos: Optional[
1793 Union[_TypeEngineArgument[_T], SchemaEventTarget]
1794 ] = None,
1795 /,
1796 *args: SchemaEventTarget,
1797 name: Optional[str] = None,
1798 type_: Optional[_TypeEngineArgument[_T]] = None,
1799 autoincrement: _AutoIncrementType = "auto",
1800 default: Optional[Any] = _NoArg.NO_ARG,
1801 insert_default: Optional[Any] = _NoArg.NO_ARG,
1802 doc: Optional[str] = None,
1803 key: Optional[str] = None,
1804 index: Optional[bool] = None,
1805 unique: Optional[bool] = None,
1806 info: Optional[_InfoType] = None,
1807 nullable: Optional[
1808 Union[bool, Literal[SchemaConst.NULL_UNSPECIFIED]]
1809 ] = SchemaConst.NULL_UNSPECIFIED,
1810 onupdate: Optional[Any] = None,
1811 primary_key: bool = False,
1812 server_default: Optional[_ServerDefaultArgument] = None,
1813 server_onupdate: Optional[_ServerOnUpdateArgument] = None,
1814 quote: Optional[bool] = None,
1815 system: bool = False,
1816 comment: Optional[str] = None,
1817 insert_sentinel: bool = False,
1818 _omit_from_statements: bool = False,
1819 _proxies: Optional[Any] = None,
1820 **dialect_kwargs: Any,
1821 ):
1822 r"""
1823 Construct a new ``Column`` object.
1825 :param name: The name of this column as represented in the database.
1826 This argument may be the first positional argument, or specified
1827 via keyword.
1829 Names which contain no upper case characters
1830 will be treated as case insensitive names, and will not be quoted
1831 unless they are a reserved word. Names with any number of upper
1832 case characters will be quoted and sent exactly. Note that this
1833 behavior applies even for databases which standardize upper
1834 case names as case insensitive such as Oracle Database.
1836 The name field may be omitted at construction time and applied
1837 later, at any time before the Column is associated with a
1838 :class:`_schema.Table`. This is to support convenient
1839 usage within the :mod:`~sqlalchemy.ext.declarative` extension.
1841 :param type\_: The column's type, indicated using an instance which
1842 subclasses :class:`~sqlalchemy.types.TypeEngine`. If no arguments
1843 are required for the type, the class of the type can be sent
1844 as well, e.g.::
1846 # use a type with arguments
1847 Column("data", String(50))
1849 # use no arguments
1850 Column("level", Integer)
1852 The ``type`` argument may be the second positional argument
1853 or specified by keyword.
1855 If the ``type`` is ``None`` or is omitted, it will first default to
1856 the special type :class:`.NullType`. If and when this
1857 :class:`_schema.Column` is made to refer to another column using
1858 :class:`_schema.ForeignKey` and/or
1859 :class:`_schema.ForeignKeyConstraint`, the type
1860 of the remote-referenced column will be copied to this column as
1861 well, at the moment that the foreign key is resolved against that
1862 remote :class:`_schema.Column` object.
1864 :param \*args: Additional positional arguments include various
1865 :class:`.SchemaItem` derived constructs which will be applied
1866 as options to the column. These include instances of
1867 :class:`.Constraint`, :class:`_schema.ForeignKey`,
1868 :class:`.ColumnDefault`, :class:`.Sequence`, :class:`.Computed`
1869 :class:`.Identity`. In some cases an
1870 equivalent keyword argument is available such as ``server_default``,
1871 ``default`` and ``unique``.
1873 :param autoincrement: Set up "auto increment" semantics for an
1874 **integer primary key column with no foreign key dependencies**
1875 (see later in this docstring for a more specific definition).
1876 This may influence the :term:`DDL` that will be emitted for
1877 this column during a table create, as well as how the column
1878 will be considered when INSERT statements are compiled and
1879 executed.
1881 The default value is the string ``"auto"``,
1882 which indicates that a single-column (i.e. non-composite) primary key
1883 that is of an INTEGER type with no other client-side or server-side
1884 default constructs indicated should receive auto increment semantics
1885 automatically. Other values include ``True`` (force this column to
1886 have auto-increment semantics for a :term:`composite primary key` as
1887 well), ``False`` (this column should never have auto-increment
1888 semantics), and the string ``"ignore_fk"`` (special-case for foreign
1889 key columns, see below).
1891 The term "auto increment semantics" refers both to the kind of DDL
1892 that will be emitted for the column within a CREATE TABLE statement,
1893 when methods such as :meth:`.MetaData.create_all` and
1894 :meth:`.Table.create` are invoked, as well as how the column will be
1895 considered when an INSERT statement is compiled and emitted to the
1896 database:
1898 * **DDL rendering** (i.e. :meth:`.MetaData.create_all`,
1899 :meth:`.Table.create`): When used on a :class:`.Column` that has
1900 no other
1901 default-generating construct associated with it (such as a
1902 :class:`.Sequence` or :class:`.Identity` construct), the parameter
1903 will imply that database-specific keywords such as PostgreSQL
1904 ``SERIAL``, MySQL ``AUTO_INCREMENT``, or ``IDENTITY`` on SQL Server
1905 should also be rendered. Not every database backend has an
1906 "implied" default generator available; for example the Oracle Database
1907 backends always needs an explicit construct such as
1908 :class:`.Identity` to be included with a :class:`.Column` in order
1909 for the DDL rendered to include auto-generating constructs to also
1910 be produced in the database.
1912 * **INSERT semantics** (i.e. when a :func:`_sql.insert` construct is
1913 compiled into a SQL string and is then executed on a database using
1914 :meth:`_engine.Connection.execute` or equivalent): A single-row
1915 INSERT statement will be known to produce a new integer primary key
1916 value automatically for this column, which will be accessible
1917 after the statement is invoked via the
1918 :attr:`.CursorResult.inserted_primary_key` attribute upon the
1919 :class:`_result.Result` object. This also applies towards use of the
1920 ORM when ORM-mapped objects are persisted to the database,
1921 indicating that a new integer primary key will be available to
1922 become part of the :term:`identity key` for that object. This
1923 behavior takes place regardless of what DDL constructs are
1924 associated with the :class:`_schema.Column` and is independent
1925 of the "DDL Rendering" behavior discussed in the previous note
1926 above.
1928 The parameter may be set to ``True`` to indicate that a column which
1929 is part of a composite (i.e. multi-column) primary key should
1930 have autoincrement semantics, though note that only one column
1931 within a primary key may have this setting. It can also
1932 be set to ``True`` to indicate autoincrement semantics on a
1933 column that has a client-side or server-side default configured,
1934 however note that not all dialects can accommodate all styles
1935 of default as an "autoincrement". It can also be
1936 set to ``False`` on a single-column primary key that has a
1937 datatype of INTEGER in order to disable auto increment semantics
1938 for that column.
1940 The setting *only* has an effect for columns which are:
1942 * Integer derived (i.e. INT, SMALLINT, BIGINT).
1944 * Part of the primary key
1946 * Not referring to another column via :class:`_schema.ForeignKey`,
1947 unless
1948 the value is specified as ``'ignore_fk'``::
1950 # turn on autoincrement for this column despite
1951 # the ForeignKey()
1952 Column(
1953 "id",
1954 ForeignKey("other.id"),
1955 primary_key=True,
1956 autoincrement="ignore_fk",
1957 )
1959 It is typically not desirable to have "autoincrement" enabled on a
1960 column that refers to another via foreign key, as such a column is
1961 required to refer to a value that originates from elsewhere.
1963 The setting has these effects on columns that meet the
1964 above criteria:
1966 * DDL issued for the column, if the column does not already include
1967 a default generating construct supported by the backend such as
1968 :class:`.Identity`, will include database-specific
1969 keywords intended to signify this column as an
1970 "autoincrement" column for specific backends. Behavior for
1971 primary SQLAlchemy dialects includes:
1973 * AUTO INCREMENT on MySQL and MariaDB
1974 * SERIAL on PostgreSQL
1975 * IDENTITY on MS-SQL - this occurs even without the
1976 :class:`.Identity` construct as the
1977 :paramref:`.Column.autoincrement` parameter pre-dates this
1978 construct.
1979 * SQLite - SQLite integer primary key columns are implicitly
1980 "auto incrementing" and no additional keywords are rendered;
1981 to render the special SQLite keyword ``AUTOINCREMENT``
1982 is not included as this is unnecessary and not recommended
1983 by the database vendor. See the section
1984 :ref:`sqlite_autoincrement` for more background.
1985 * Oracle Database - The Oracle Database dialects have no default "autoincrement"
1986 feature available at this time, instead the :class:`.Identity`
1987 construct is recommended to achieve this (the :class:`.Sequence`
1988 construct may also be used).
1989 * Third-party dialects - consult those dialects' documentation
1990 for details on their specific behaviors.
1992 * When a single-row :func:`_sql.insert` construct is compiled and
1993 executed, which does not set the :meth:`_sql.Insert.inline`
1994 modifier, newly generated primary key values for this column
1995 will be automatically retrieved upon statement execution
1996 using a method specific to the database driver in use:
1998 * MySQL, SQLite - calling upon ``cursor.lastrowid()``
1999 (see
2000 `https://www.python.org/dev/peps/pep-0249/#lastrowid
2001 <https://www.python.org/dev/peps/pep-0249/#lastrowid>`_)
2002 * PostgreSQL, SQL Server, Oracle Database - use RETURNING or an equivalent
2003 construct when rendering an INSERT statement, and then retrieving
2004 the newly generated primary key values after execution
2005 * PostgreSQL, Oracle Database for :class:`_schema.Table` objects that
2006 set :paramref:`_schema.Table.implicit_returning` to False -
2007 for a :class:`.Sequence` only, the :class:`.Sequence` is invoked
2008 explicitly before the INSERT statement takes place so that the
2009 newly generated primary key value is available to the client
2010 * SQL Server for :class:`_schema.Table` objects that
2011 set :paramref:`_schema.Table.implicit_returning` to False -
2012 the ``SELECT scope_identity()`` construct is used after the
2013 INSERT statement is invoked to retrieve the newly generated
2014 primary key value.
2015 * Third-party dialects - consult those dialects' documentation
2016 for details on their specific behaviors.
2018 * For multiple-row :func:`_sql.insert` constructs invoked with
2019 a list of parameters (i.e. "executemany" semantics), primary-key
2020 retrieving behaviors are generally disabled, however there may
2021 be special APIs that may be used to retrieve lists of new
2022 primary key values for an "executemany", such as the psycopg2
2023 "fast insertmany" feature. Such features are very new and
2024 may not yet be well covered in documentation.
2026 :param default: A scalar, Python callable, or
2027 :class:`_expression.ColumnElement` expression representing the
2028 *default value* for this column, which will be invoked upon insert
2029 if this column is otherwise not specified in the VALUES clause of
2030 the insert. This is a shortcut to using :class:`.ColumnDefault` as
2031 a positional argument; see that class for full detail on the
2032 structure of the argument.
2034 Contrast this argument to
2035 :paramref:`_schema.Column.server_default`
2036 which creates a default generator on the database side.
2038 .. seealso::
2040 :ref:`metadata_defaults_toplevel`
2042 :param insert_default: An alias of :paramref:`.Column.default`
2043 for compatibility with :func:`_orm.mapped_column`.
2045 .. versionadded:: 2.0.31
2047 :param doc: optional String that can be used by the ORM or similar
2048 to document attributes on the Python side. This attribute does
2049 **not** render SQL comments; use the
2050 :paramref:`_schema.Column.comment`
2051 parameter for this purpose.
2053 :param key: An optional string identifier which will identify this
2054 ``Column`` object on the :class:`_schema.Table`.
2055 When a key is provided,
2056 this is the only identifier referencing the ``Column`` within the
2057 application, including ORM attribute mapping; the ``name`` field
2058 is used only when rendering SQL.
2060 :param index: When ``True``, indicates that a :class:`_schema.Index`
2061 construct will be automatically generated for this
2062 :class:`_schema.Column`, which will result in a "CREATE INDEX"
2063 statement being emitted for the :class:`_schema.Table` when the DDL
2064 create operation is invoked.
2066 Using this flag is equivalent to making use of the
2067 :class:`_schema.Index` construct explicitly at the level of the
2068 :class:`_schema.Table` construct itself::
2070 Table(
2071 "some_table",
2072 metadata,
2073 Column("x", Integer),
2074 Index("ix_some_table_x", "x"),
2075 )
2077 To add the :paramref:`_schema.Index.unique` flag to the
2078 :class:`_schema.Index`, set both the
2079 :paramref:`_schema.Column.unique` and
2080 :paramref:`_schema.Column.index` flags to True simultaneously,
2081 which will have the effect of rendering the "CREATE UNIQUE INDEX"
2082 DDL instruction instead of "CREATE INDEX".
2084 The name of the index is generated using the
2085 :ref:`default naming convention <constraint_default_naming_convention>`
2086 which for the :class:`_schema.Index` construct is of the form
2087 ``ix_<tablename>_<columnname>``.
2089 As this flag is intended only as a convenience for the common case
2090 of adding a single-column, default configured index to a table
2091 definition, explicit use of the :class:`_schema.Index` construct
2092 should be preferred for most use cases, including composite indexes
2093 that encompass more than one column, indexes with SQL expressions
2094 or ordering, backend-specific index configuration options, and
2095 indexes that use a specific name.
2097 .. note:: the :attr:`_schema.Column.index` attribute on
2098 :class:`_schema.Column`
2099 **does not indicate** if this column is indexed or not, only
2100 if this flag was explicitly set here. To view indexes on
2101 a column, view the :attr:`_schema.Table.indexes` collection
2102 or use :meth:`_reflection.Inspector.get_indexes`.
2104 .. seealso::
2106 :ref:`schema_indexes`
2108 :ref:`constraint_naming_conventions`
2110 :paramref:`_schema.Column.unique`
2112 :param info: Optional data dictionary which will be populated into the
2113 :attr:`.SchemaItem.info` attribute of this object.
2115 :param nullable: When set to ``False``, will cause the "NOT NULL"
2116 phrase to be added when generating DDL for the column. When
2117 ``True``, will normally generate nothing (in SQL this defaults to
2118 "NULL"), except in some very specific backend-specific edge cases
2119 where "NULL" may render explicitly.
2120 Defaults to ``True`` unless :paramref:`_schema.Column.primary_key`
2121 is also ``True`` or the column specifies a :class:`_sql.Identity`,
2122 in which case it defaults to ``False``.
2123 This parameter is only used when issuing CREATE TABLE statements.
2125 .. note::
2127 When the column specifies a :class:`_sql.Identity` this
2128 parameter is in general ignored by the DDL compiler. The
2129 PostgreSQL database allows nullable identity column by
2130 setting this parameter to ``True`` explicitly.
2132 :param onupdate: A scalar, Python callable, or
2133 :class:`~sqlalchemy.sql.expression.ClauseElement` representing a
2134 default value to be applied to the column within UPDATE
2135 statements, which will be invoked upon update if this column is not
2136 present in the SET clause of the update. This is a shortcut to
2137 using :class:`.ColumnDefault` as a positional argument with
2138 ``for_update=True``.
2140 .. seealso::
2142 :ref:`metadata_defaults` - complete discussion of onupdate
2144 :param primary_key: If ``True``, marks this column as a primary key
2145 column. Multiple columns can have this flag set to specify
2146 composite primary keys. As an alternative, the primary key of a
2147 :class:`_schema.Table` can be specified via an explicit
2148 :class:`.PrimaryKeyConstraint` object.
2150 :param server_default: A :class:`.FetchedValue` instance, str, Unicode
2151 or :func:`~sqlalchemy.sql.expression.text` construct representing
2152 the DDL DEFAULT value for the column.
2154 String types will be emitted as-is, surrounded by single quotes::
2156 Column("x", Text, server_default="val")
2158 will render:
2160 .. sourcecode:: sql
2162 x TEXT DEFAULT 'val'
2164 A :func:`~sqlalchemy.sql.expression.text` expression will be
2165 rendered as-is, without quotes::
2167 Column("y", DateTime, server_default=text("NOW()"))
2169 will render:
2171 .. sourcecode:: sql
2173 y DATETIME DEFAULT NOW()
2175 Strings and text() will be converted into a
2176 :class:`.DefaultClause` object upon initialization.
2178 This parameter can also accept complex combinations of contextually
2179 valid SQLAlchemy expressions or constructs::
2181 from sqlalchemy import create_engine
2182 from sqlalchemy import Table, Column, MetaData, ARRAY, Text
2183 from sqlalchemy.dialects.postgresql import array
2185 engine = create_engine(
2186 "postgresql+psycopg2://scott:tiger@localhost/mydatabase"
2187 )
2188 metadata_obj = MetaData()
2189 tbl = Table(
2190 "foo",
2191 metadata_obj,
2192 Column(
2193 "bar", ARRAY(Text), server_default=array(["biz", "bang", "bash"])
2194 ),
2195 )
2196 metadata_obj.create_all(engine)
2198 The above results in a table created with the following SQL:
2200 .. sourcecode:: sql
2202 CREATE TABLE foo (
2203 bar TEXT[] DEFAULT ARRAY['biz', 'bang', 'bash']
2204 )
2206 Use :class:`.FetchedValue` to indicate that an already-existing
2207 column will generate a default value on the database side which
2208 will be available to SQLAlchemy for post-fetch after inserts. This
2209 construct does not specify any DDL and the implementation is left
2210 to the database, such as via a trigger.
2212 .. seealso::
2214 :ref:`server_defaults` - complete discussion of server side
2215 defaults
2217 :param server_onupdate: A :class:`.FetchedValue` instance
2218 representing a database-side default generation function,
2219 such as a trigger. This
2220 indicates to SQLAlchemy that a newly generated value will be
2221 available after updates. This construct does not actually
2222 implement any kind of generation function within the database,
2223 which instead must be specified separately.
2226 .. warning:: This directive **does not** currently produce MySQL's
2227 "ON UPDATE CURRENT_TIMESTAMP()" clause. See
2228 :ref:`mysql_timestamp_onupdate` for background on how to
2229 produce this clause.
2231 .. seealso::
2233 :ref:`triggered_columns`
2235 :param quote: Force quoting of this column's name on or off,
2236 corresponding to ``True`` or ``False``. When left at its default
2237 of ``None``, the column identifier will be quoted according to
2238 whether the name is case sensitive (identifiers with at least one
2239 upper case character are treated as case sensitive), or if it's a
2240 reserved word. This flag is only needed to force quoting of a
2241 reserved word which is not known by the SQLAlchemy dialect.
2243 :param unique: When ``True``, and the :paramref:`_schema.Column.index`
2244 parameter is left at its default value of ``False``,
2245 indicates that a :class:`_schema.UniqueConstraint`
2246 construct will be automatically generated for this
2247 :class:`_schema.Column`,
2248 which will result in a "UNIQUE CONSTRAINT" clause referring
2249 to this column being included
2250 in the ``CREATE TABLE`` statement emitted, when the DDL create
2251 operation for the :class:`_schema.Table` object is invoked.
2253 When this flag is ``True`` while the
2254 :paramref:`_schema.Column.index` parameter is simultaneously
2255 set to ``True``, the effect instead is that a
2256 :class:`_schema.Index` construct which includes the
2257 :paramref:`_schema.Index.unique` parameter set to ``True``
2258 is generated. See the documentation for
2259 :paramref:`_schema.Column.index` for additional detail.
2261 Using this flag is equivalent to making use of the
2262 :class:`_schema.UniqueConstraint` construct explicitly at the
2263 level of the :class:`_schema.Table` construct itself::
2265 Table("some_table", metadata, Column("x", Integer), UniqueConstraint("x"))
2267 The :paramref:`_schema.UniqueConstraint.name` parameter
2268 of the unique constraint object is left at its default value
2269 of ``None``; in the absence of a :ref:`naming convention <constraint_naming_conventions>`
2270 for the enclosing :class:`_schema.MetaData`, the UNIQUE CONSTRAINT
2271 construct will be emitted as unnamed, which typically invokes
2272 a database-specific naming convention to take place.
2274 As this flag is intended only as a convenience for the common case
2275 of adding a single-column, default configured unique constraint to a table
2276 definition, explicit use of the :class:`_schema.UniqueConstraint` construct
2277 should be preferred for most use cases, including composite constraints
2278 that encompass more than one column, backend-specific index configuration options, and
2279 constraints that use a specific name.
2281 .. note:: the :attr:`_schema.Column.unique` attribute on
2282 :class:`_schema.Column`
2283 **does not indicate** if this column has a unique constraint or
2284 not, only if this flag was explicitly set here. To view
2285 indexes and unique constraints that may involve this column,
2286 view the
2287 :attr:`_schema.Table.indexes` and/or
2288 :attr:`_schema.Table.constraints` collections or use
2289 :meth:`_reflection.Inspector.get_indexes` and/or
2290 :meth:`_reflection.Inspector.get_unique_constraints`
2292 .. seealso::
2294 :ref:`schema_unique_constraint`
2296 :ref:`constraint_naming_conventions`
2298 :paramref:`_schema.Column.index`
2300 :param system: When ``True``, indicates this is a "system" column,
2301 that is a column which is automatically made available by the
2302 database, and should not be included in the columns list for a
2303 ``CREATE TABLE`` statement.
2305 For more elaborate scenarios where columns should be
2306 conditionally rendered differently on different backends,
2307 consider custom compilation rules for :class:`.CreateColumn`.
2309 :param comment: Optional string that will render an SQL comment on
2310 table creation.
2312 :param insert_sentinel: Marks this :class:`_schema.Column` as an
2313 :term:`insert sentinel` used for optimizing the performance of the
2314 :term:`insertmanyvalues` feature for tables that don't
2315 otherwise have qualifying primary key configurations.
2317 .. versionadded:: 2.0.10
2319 .. seealso::
2321 :func:`_schema.insert_sentinel` - all in one helper for declaring
2322 sentinel columns
2324 :ref:`engine_insertmanyvalues`
2326 :ref:`engine_insertmanyvalues_sentinel_columns`
2329 """ # noqa: E501, RST201, RST202
2331 l_args = [__name_pos, __type_pos] + list(args)
2332 del args
2334 if isinstance(l_args[0], str):
2335 if name is not None:
2336 raise exc.ArgumentError(
2337 "May not pass name positionally and as a keyword."
2338 )
2339 name = l_args.pop(0) # type: ignore[assignment]
2340 elif l_args[0] is None:
2341 l_args.pop(0)
2342 if l_args:
2343 coltype = l_args[0]
2345 if hasattr(coltype, "_sqla_type"):
2346 if type_ is not None:
2347 raise exc.ArgumentError(
2348 "May not pass type_ positionally and as a keyword."
2349 )
2350 type_ = l_args.pop(0) # type: ignore[assignment]
2351 elif l_args[0] is None:
2352 l_args.pop(0)
2354 if name is not None:
2355 name = quoted_name(name, quote)
2356 elif quote is not None:
2357 raise exc.ArgumentError(
2358 "Explicit 'name' is required when sending 'quote' argument"
2359 )
2361 # name = None is expected to be an interim state
2362 # note this use case is legacy now that ORM declarative has a
2363 # dedicated "column" construct local to the ORM
2364 super().__init__(name, type_) # type: ignore[arg-type]
2366 self.key = key if key is not None else name # type: ignore[assignment]
2367 self.primary_key = primary_key
2368 self._insert_sentinel = insert_sentinel
2369 self._omit_from_statements = _omit_from_statements
2370 self._user_defined_nullable = udn = nullable
2371 if udn is not NULL_UNSPECIFIED:
2372 self.nullable = udn
2373 else:
2374 self.nullable = not primary_key
2376 # these default to None because .index and .unique is *not*
2377 # an informational flag about Column - there can still be an
2378 # Index or UniqueConstraint referring to this Column.
2379 self.index = index
2380 self.unique = unique
2382 self.system = system
2383 self.doc = doc
2384 self.autoincrement: _AutoIncrementType = autoincrement
2385 self.constraints = set()
2386 self.foreign_keys = set()
2387 self.comment = comment
2388 self.computed = None
2389 self.identity = None
2391 # check if this Column is proxying another column
2393 if _proxies is not None:
2394 self._proxies = _proxies
2395 else:
2396 # otherwise, add DDL-related events
2397 self._set_type(self.type)
2399 if insert_default is not _NoArg.NO_ARG:
2400 if default is not _NoArg.NO_ARG:
2401 raise exc.ArgumentError(
2402 "The 'default' and 'insert_default' parameters "
2403 "of Column are mutually exclusive"
2404 )
2405 resolved_default = insert_default
2406 elif default is not _NoArg.NO_ARG:
2407 resolved_default = default
2408 else:
2409 resolved_default = None
2411 if resolved_default is not None:
2412 if not isinstance(resolved_default, (ColumnDefault, Sequence)):
2413 resolved_default = ColumnDefault(resolved_default)
2415 self.default = resolved_default
2416 l_args.append(resolved_default)
2417 else:
2418 self.default = None
2420 if onupdate is not None:
2421 if not isinstance(onupdate, (ColumnDefault, Sequence)):
2422 onupdate = ColumnDefault(onupdate, for_update=True)
2424 self.onupdate = onupdate
2425 l_args.append(onupdate)
2426 else:
2427 self.onupdate = None
2429 if server_default is not None:
2430 if isinstance(server_default, FetchedValue):
2431 server_default = server_default._as_for_update(False)
2432 l_args.append(server_default)
2433 else:
2434 server_default = DefaultClause(server_default)
2435 l_args.append(server_default)
2436 self.server_default = server_default
2438 if server_onupdate is not None:
2439 if isinstance(server_onupdate, FetchedValue):
2440 server_onupdate = server_onupdate._as_for_update(True)
2441 l_args.append(server_onupdate)
2442 else:
2443 server_onupdate = DefaultClause(
2444 server_onupdate, for_update=True
2445 )
2446 l_args.append(server_onupdate)
2447 self.server_onupdate = server_onupdate
2449 self._init_items(*cast(_typing_Sequence[SchemaItem], l_args))
2451 util.set_creation_order(self)
2453 if info is not None:
2454 self.info = info
2456 self._extra_kwargs(**dialect_kwargs)
2458 table: Table
2460 constraints: Set[Constraint]
2462 foreign_keys: Set[ForeignKey]
2463 """A collection of all :class:`_schema.ForeignKey` marker objects
2464 associated with this :class:`_schema.Column`.
2466 Each object is a member of a :class:`_schema.Table`-wide
2467 :class:`_schema.ForeignKeyConstraint`.
2469 .. seealso::
2471 :attr:`_schema.Table.foreign_keys`
2473 """
2475 index: Optional[bool]
2476 """The value of the :paramref:`_schema.Column.index` parameter.
2478 Does not indicate if this :class:`_schema.Column` is actually indexed
2479 or not; use :attr:`_schema.Table.indexes`.
2481 .. seealso::
2483 :attr:`_schema.Table.indexes`
2484 """
2486 unique: Optional[bool]
2487 """The value of the :paramref:`_schema.Column.unique` parameter.
2489 Does not indicate if this :class:`_schema.Column` is actually subject to
2490 a unique constraint or not; use :attr:`_schema.Table.indexes` and
2491 :attr:`_schema.Table.constraints`.
2493 .. seealso::
2495 :attr:`_schema.Table.indexes`
2497 :attr:`_schema.Table.constraints`.
2499 """
2501 computed: Optional[Computed]
2503 identity: Optional[Identity]
2505 def _set_type(self, type_: TypeEngine[Any]) -> None:
2506 assert self.type._isnull or type_ is self.type
2508 self.type = type_
2509 if isinstance(self.type, SchemaEventTarget):
2510 self.type._set_parent_with_dispatch(self)
2511 for impl in self.type._variant_mapping.values():
2512 if isinstance(impl, SchemaEventTarget):
2513 impl._set_parent_with_dispatch(self)
2515 @HasMemoized.memoized_attribute
2516 def _default_description_tuple(self) -> _DefaultDescriptionTuple:
2517 """used by default.py -> _process_execute_defaults()"""
2519 return _DefaultDescriptionTuple._from_column_default(self.default)
2521 @HasMemoized.memoized_attribute
2522 def _onupdate_description_tuple(self) -> _DefaultDescriptionTuple:
2523 """used by default.py -> _process_execute_defaults()"""
2524 return _DefaultDescriptionTuple._from_column_default(self.onupdate)
2526 @util.memoized_property
2527 def _gen_static_annotations_cache_key(self) -> bool:
2528 """special attribute used by cache key gen, if true, we will
2529 use a static cache key for the annotations dictionary, else we
2530 will generate a new cache key for annotations each time.
2532 Added for #8790
2534 """
2535 return self.table is not None and self.table._is_table
2537 def _extra_kwargs(self, **kwargs: Any) -> None:
2538 self._validate_dialect_kwargs(kwargs)
2540 def __str__(self) -> str:
2541 if self.name is None:
2542 return "(no name)"
2543 elif self.table is not None:
2544 if self.table.named_with_column:
2545 return self.table.description + "." + self.description
2546 else:
2547 return self.description
2548 else:
2549 return self.description
2551 def references(self, column: Column[Any]) -> bool:
2552 """Return True if this Column references the given column via foreign
2553 key."""
2555 for fk in self.foreign_keys:
2556 if fk.column.proxy_set.intersection(column.proxy_set):
2557 return True
2558 else:
2559 return False
2561 def append_foreign_key(self, fk: ForeignKey) -> None:
2562 fk._set_parent_with_dispatch(self)
2564 def __repr__(self) -> str:
2565 kwarg = []
2566 if self.key != self.name:
2567 kwarg.append("key")
2568 if self.primary_key:
2569 kwarg.append("primary_key")
2570 if not self.nullable:
2571 kwarg.append("nullable")
2572 if self.onupdate:
2573 kwarg.append("onupdate")
2574 if self.default:
2575 kwarg.append("default")
2576 if self.server_default:
2577 kwarg.append("server_default")
2578 if self.comment:
2579 kwarg.append("comment")
2580 return "Column(%s)" % ", ".join(
2581 [repr(self.name)]
2582 + [repr(self.type)]
2583 + [repr(x) for x in self.foreign_keys if x is not None]
2584 + [repr(x) for x in self.constraints]
2585 + [
2586 (
2587 self.table is not None
2588 and "table=<%s>" % self.table.description
2589 or "table=None"
2590 )
2591 ]
2592 + ["%s=%s" % (k, repr(getattr(self, k))) for k in kwarg]
2593 )
2595 def _set_parent( # type: ignore[override]
2596 self,
2597 parent: SchemaEventTarget,
2598 *,
2599 all_names: Dict[str, Column[Any]],
2600 allow_replacements: bool,
2601 index: Optional[int] = None,
2602 **kw: Any,
2603 ) -> None:
2604 table = parent
2605 assert isinstance(table, Table)
2606 if not self.name:
2607 raise exc.ArgumentError(
2608 "Column must be constructed with a non-blank name or "
2609 "assign a non-blank .name before adding to a Table."
2610 )
2612 self._reset_memoizations()
2614 if self.key is None:
2615 self.key = self.name
2617 existing = getattr(self, "table", None)
2618 if existing is not None and existing is not table:
2619 raise exc.ArgumentError(
2620 f"Column object '{self.key}' already "
2621 f"assigned to Table '{existing.description}'"
2622 )
2624 extra_remove = None
2625 existing_col = None
2626 conflicts_on = ""
2628 if self.key in table._columns:
2629 existing_col = table._columns[self.key]
2630 if self.key == self.name:
2631 conflicts_on = "name"
2632 else:
2633 conflicts_on = "key"
2634 elif self.name in all_names:
2635 existing_col = all_names[self.name]
2636 extra_remove = {existing_col}
2637 conflicts_on = "name"
2639 if existing_col is not None:
2640 if existing_col is not self:
2641 if not allow_replacements:
2642 raise exc.DuplicateColumnError(
2643 f"A column with {conflicts_on} " f"""'{
2644 self.key if conflicts_on == 'key' else self.name
2645 }' """ f"is already present in table '{table.name}'."
2646 )
2647 for fk in existing_col.foreign_keys:
2648 table.foreign_keys.remove(fk)
2649 if fk.constraint in table.constraints:
2650 # this might have been removed
2651 # already, if it's a composite constraint
2652 # and more than one col being replaced
2653 table.constraints.remove(fk.constraint)
2655 if extra_remove and existing_col is not None and self.key == self.name:
2656 util.warn(
2657 f'Column with user-specified key "{existing_col.key}" is '
2658 "being replaced with "
2659 f'plain named column "{self.name}", '
2660 f'key "{existing_col.key}" is being removed. If this is a '
2661 "reflection operation, specify autoload_replace=False to "
2662 "prevent this replacement."
2663 )
2664 table._columns.replace(self, extra_remove=extra_remove, index=index)
2665 all_names[self.name] = self
2666 self.table = table
2668 if self._insert_sentinel:
2669 if self.table._sentinel_column is not None:
2670 raise exc.ArgumentError(
2671 "a Table may have only one explicit sentinel column"
2672 )
2673 self.table._sentinel_column = self
2675 if self.primary_key:
2676 table.primary_key._replace(self)
2677 elif self.key in table.primary_key:
2678 raise exc.ArgumentError(
2679 f"Trying to redefine primary-key column '{self.key}' as a "
2680 f"non-primary-key column on table '{table.fullname}'"
2681 )
2683 if self.index:
2684 if isinstance(self.index, str):
2685 raise exc.ArgumentError(
2686 "The 'index' keyword argument on Column is boolean only. "
2687 "To create indexes with a specific name, create an "
2688 "explicit Index object external to the Table."
2689 )
2690 table.append_constraint(
2691 Index(
2692 None, self.key, unique=bool(self.unique), _column_flag=True
2693 )
2694 )
2696 elif self.unique:
2697 if isinstance(self.unique, str):
2698 raise exc.ArgumentError(
2699 "The 'unique' keyword argument on Column is boolean "
2700 "only. To create unique constraints or indexes with a "
2701 "specific name, append an explicit UniqueConstraint to "
2702 "the Table's list of elements, or create an explicit "
2703 "Index object external to the Table."
2704 )
2705 table.append_constraint(
2706 UniqueConstraint(self.key, _column_flag=True)
2707 )
2709 self._setup_on_memoized_fks(lambda fk: fk._set_remote_table(table))
2711 if self.identity and (
2712 isinstance(self.default, Sequence)
2713 or isinstance(self.onupdate, Sequence)
2714 ):
2715 raise exc.ArgumentError(
2716 "An column cannot specify both Identity and Sequence."
2717 )
2719 def _setup_on_memoized_fks(self, fn: Callable[..., Any]) -> None:
2720 fk_keys = [
2721 ((self.table.key, self.key), False),
2722 ((self.table.key, self.name), True),
2723 ]
2724 for fk_key, link_to_name in fk_keys:
2725 if fk_key in self.table.metadata._fk_memos:
2726 for fk in self.table.metadata._fk_memos[fk_key]:
2727 if fk.link_to_name is link_to_name:
2728 fn(fk)
2730 def _on_table_attach(self, fn: Callable[..., Any]) -> None:
2731 if self.table is not None:
2732 fn(self, self.table)
2733 else:
2734 event.listen(self, "after_parent_attach", fn)
2736 @util.deprecated(
2737 "1.4",
2738 "The :meth:`_schema.Column.copy` method is deprecated "
2739 "and will be removed in a future release.",
2740 )
2741 def copy(self, **kw: Any) -> Column[Any]:
2742 return self._copy(**kw)
2744 def _copy(self, **kw: Any) -> Column[Any]:
2745 """Create a copy of this ``Column``, uninitialized.
2747 This is used in :meth:`_schema.Table.to_metadata` and by the ORM.
2749 """
2751 # Constraint objects plus non-constraint-bound ForeignKey objects
2752 args: List[SchemaItem] = [
2753 c._copy(**kw) for c in self.constraints if not c._type_bound
2754 ] + [c._copy(**kw) for c in self.foreign_keys if not c.constraint]
2756 # ticket #5276
2757 column_kwargs = {}
2758 for dialect_name in self.dialect_options:
2759 dialect_options = self.dialect_options[dialect_name]._non_defaults
2760 for (
2761 dialect_option_key,
2762 dialect_option_value,
2763 ) in dialect_options.items():
2764 column_kwargs[dialect_name + "_" + dialect_option_key] = (
2765 dialect_option_value
2766 )
2768 default = self.default
2769 if default is not None:
2770 default = default._copy()
2771 onupdate = self.onupdate
2772 if onupdate is not None:
2773 onupdate = onupdate._copy()
2774 server_default = self.server_default
2775 server_onupdate = self.server_onupdate
2776 if isinstance(server_default, (Computed, Identity)):
2777 args.append(server_default._copy(**kw))
2778 server_default = server_onupdate = None
2779 else:
2780 if server_default is not None:
2781 server_default = server_default._copy()
2782 if server_onupdate is not None:
2783 server_onupdate = server_onupdate._copy()
2785 type_ = self.type
2786 if isinstance(type_, SchemaEventTarget):
2787 type_ = type_.copy(**kw)
2789 c = self._constructor(
2790 name=self.name,
2791 type_=type_,
2792 key=self.key,
2793 primary_key=self.primary_key,
2794 unique=self.unique,
2795 system=self.system,
2796 # quote=self.quote, # disabled 2013-08-27 (commit 031ef080)
2797 index=self.index,
2798 autoincrement=self.autoincrement,
2799 default=default,
2800 server_default=server_default,
2801 onupdate=onupdate,
2802 server_onupdate=server_onupdate,
2803 doc=self.doc,
2804 comment=self.comment,
2805 _omit_from_statements=self._omit_from_statements,
2806 insert_sentinel=self._insert_sentinel,
2807 *args,
2808 **column_kwargs,
2809 )
2811 # copy the state of "nullable" exactly, to accommodate for
2812 # ORM flipping the .nullable flag directly
2813 c.nullable = self.nullable
2814 c._user_defined_nullable = self._user_defined_nullable
2816 return self._schema_item_copy(c)
2818 def _merge(
2819 self, other: Column[Any], *, omit_defaults: bool = False
2820 ) -> None:
2821 """merge the elements of this column onto "other"
2823 this is used by ORM pep-593 merge and will likely need a lot
2824 of fixes.
2827 """
2829 if self.primary_key:
2830 other.primary_key = True
2832 if self.autoincrement != "auto" and other.autoincrement == "auto":
2833 other.autoincrement = self.autoincrement
2835 if self.system:
2836 other.system = self.system
2838 if self.info:
2839 other.info.update(self.info)
2841 type_ = self.type
2842 if not type_._isnull and other.type._isnull:
2843 if isinstance(type_, SchemaEventTarget):
2844 type_ = type_.copy()
2846 other.type = type_
2848 if isinstance(type_, SchemaEventTarget):
2849 type_._set_parent_with_dispatch(other)
2851 for impl in type_._variant_mapping.values():
2852 if isinstance(impl, SchemaEventTarget):
2853 impl._set_parent_with_dispatch(other)
2855 if (
2856 self._user_defined_nullable is not NULL_UNSPECIFIED
2857 and other._user_defined_nullable is NULL_UNSPECIFIED
2858 ):
2859 other.nullable = self.nullable
2860 other._user_defined_nullable = self._user_defined_nullable
2862 if (
2863 not omit_defaults
2864 and self.default is not None
2865 and other.default is None
2866 ):
2867 new_default = self.default._copy()
2868 new_default._set_parent(other)
2870 if self.server_default and other.server_default is None:
2871 new_server_default = self.server_default
2872 if isinstance(new_server_default, FetchedValue):
2873 new_server_default = new_server_default._copy()
2874 new_server_default._set_parent(other)
2875 else:
2876 other.server_default = new_server_default
2878 if self.server_onupdate and other.server_onupdate is None:
2879 new_server_onupdate = self.server_onupdate
2880 new_server_onupdate = new_server_onupdate._copy()
2881 new_server_onupdate._set_parent(other)
2883 if self.onupdate and other.onupdate is None:
2884 new_onupdate = self.onupdate._copy()
2885 new_onupdate._set_parent(other)
2887 if self.index in (True, False) and other.index is None:
2888 other.index = self.index
2890 if self.unique in (True, False) and other.unique is None:
2891 other.unique = self.unique
2893 if self.doc and other.doc is None:
2894 other.doc = self.doc
2896 if self.comment and other.comment is None:
2897 other.comment = self.comment
2899 for const in self.constraints:
2900 if not const._type_bound:
2901 new_const = const._copy()
2902 new_const._set_parent(other)
2904 for fk in self.foreign_keys:
2905 if not fk.constraint:
2906 new_fk = fk._copy()
2907 new_fk._set_parent(other)
2909 def _make_proxy(
2910 self,
2911 selectable: FromClause,
2912 primary_key: ColumnSet,
2913 foreign_keys: Set[KeyedColumnElement[Any]],
2914 name: Optional[str] = None,
2915 key: Optional[str] = None,
2916 name_is_truncatable: bool = False,
2917 compound_select_cols: Optional[
2918 _typing_Sequence[ColumnElement[Any]]
2919 ] = None,
2920 **kw: Any,
2921 ) -> Tuple[str, ColumnClause[_T]]:
2922 """Create a *proxy* for this column.
2924 This is a copy of this ``Column`` referenced by a different parent
2925 (such as an alias or select statement). The column should
2926 be used only in select scenarios, as its full DDL/default
2927 information is not transferred.
2929 """
2931 fk = [
2932 ForeignKey(
2933 col if col is not None else f.target_tokens,
2934 _unresolvable=col is None,
2935 _constraint=f.constraint,
2936 )
2937 for f, col in [
2938 (fk, fk._resolve_column(raiseerr=False))
2939 for fk in self.foreign_keys
2940 ]
2941 ]
2943 if name is None and self.name is None:
2944 raise exc.InvalidRequestError(
2945 "Cannot initialize a sub-selectable"
2946 " with this Column object until its 'name' has "
2947 "been assigned."
2948 )
2949 try:
2950 c = self._constructor(
2951 (
2952 coercions.expect(
2953 roles.TruncatedLabelRole, name if name else self.name
2954 )
2955 if name_is_truncatable
2956 else (name or self.name)
2957 ),
2958 self.type,
2959 # this may actually be ._proxy_key when the key is incoming
2960 key=key if key else name if name else self.key,
2961 primary_key=self.primary_key,
2962 nullable=self.nullable,
2963 _proxies=(
2964 list(compound_select_cols)
2965 if compound_select_cols
2966 else [self]
2967 ),
2968 *fk,
2969 )
2970 except TypeError as err:
2971 raise TypeError(
2972 "Could not create a copy of this %r object. "
2973 "Ensure the class includes a _constructor() "
2974 "attribute or method which accepts the "
2975 "standard Column constructor arguments, or "
2976 "references the Column class itself." % self.__class__
2977 ) from err
2979 c.table = selectable
2980 c._propagate_attrs = selectable._propagate_attrs
2981 if selectable._is_clone_of is not None:
2982 c._is_clone_of = selectable._is_clone_of.columns.get(c.key)
2984 if self.primary_key:
2985 primary_key.add(c)
2987 if fk:
2988 foreign_keys.update(fk) # type: ignore[arg-type]
2990 return c.key, c
2993def insert_sentinel(
2994 name: Optional[str] = None,
2995 type_: Optional[_TypeEngineArgument[_T]] = None,
2996 *,
2997 default: Optional[Any] = None,
2998 omit_from_statements: bool = True,
2999) -> Column[Any]:
3000 """Provides a surrogate :class:`_schema.Column` that will act as a
3001 dedicated insert :term:`sentinel` column, allowing efficient bulk
3002 inserts with deterministic RETURNING sorting for tables that
3003 don't otherwise have qualifying primary key configurations.
3005 Adding this column to a :class:`.Table` object requires that a
3006 corresponding database table actually has this column present, so if adding
3007 it to an existing model, existing database tables would need to be migrated
3008 (e.g. using ALTER TABLE or similar) to include this column.
3010 For background on how this object is used, see the section
3011 :ref:`engine_insertmanyvalues_sentinel_columns` as part of the
3012 section :ref:`engine_insertmanyvalues`.
3014 The :class:`_schema.Column` returned will be a nullable integer column by
3015 default and make use of a sentinel-specific default generator used only in
3016 "insertmanyvalues" operations.
3018 .. seealso::
3020 :func:`_orm.orm_insert_sentinel`
3022 :paramref:`_schema.Column.insert_sentinel`
3024 :ref:`engine_insertmanyvalues`
3026 :ref:`engine_insertmanyvalues_sentinel_columns`
3029 .. versionadded:: 2.0.10
3031 """
3032 return Column(
3033 name=name,
3034 type_=type_api.INTEGERTYPE if type_ is None else type_,
3035 default=(
3036 default if default is not None else _InsertSentinelColumnDefault()
3037 ),
3038 _omit_from_statements=omit_from_statements,
3039 insert_sentinel=True,
3040 )
3043class ForeignKeyTarget(NamedTuple):
3044 """Represents the target of a :class:`_schema.ForeignKey` as three
3045 individual name tokens.
3047 This is the return value of :attr:`_schema.ForeignKey.target_tokens`, and
3048 may also be passed directly to the :class:`_schema.ForeignKey` constructor
3049 as well as to the :paramref:`_schema.ForeignKeyConstraint.refcolumns`
3050 parameter, as either a three-token tuple ``(schema, table_name,
3051 column_name)`` or a two-token tuple ``(table_name, column_name)``.
3053 The token form is the only representation of a foreign key target that is
3054 unambiguous in all cases; the dotted string form, available at
3055 :attr:`_schema.ForeignKey.target_fullname`, cannot represent a target
3056 where the table or column name itself contains a dot.
3058 .. versionadded:: 2.1
3060 .. seealso::
3062 :attr:`_schema.ForeignKey.target_tokens`
3064 """
3066 schema: Optional[str]
3067 """The schema name of the target, or ``None`` for the default schema."""
3069 table_name: str
3070 """The name of the target table."""
3072 column_name: Optional[str]
3073 """The key of the target column.
3075 This is ``None`` for a :class:`_schema.ForeignKey` that was given a table
3076 name only, in which case the local column's key is used to locate the
3077 target column.
3079 Note that this is the ``key`` of the target column rather than its name,
3080 unless :paramref:`_schema.ForeignKey.link_to_name` is ``True``.
3082 """
3084 def _tokens_no_dots(self) -> _typing_Sequence[str] | None:
3085 """return a sequence of non-None tokens to create a dotted name.
3087 returns None if either of table_name or column_name already have an
3088 embedded dot, making dotted name string impossible.
3090 """
3091 tokens = []
3092 for i, token in enumerate(
3093 [self.schema, self.table_name, self.column_name]
3094 ):
3095 if token is not None:
3096 # dot in the table name or column name; a dotted name
3097 # would be ambiguous
3098 if i != 0 and "." in token:
3099 return None
3100 tokens.append(token)
3101 elif i == 1:
3102 # table name is None; not renderable
3103 return None
3104 elif i == 2 and self.schema:
3105 # column name is None and there's a schema; a dotted
3106 # name would be ambiguous
3107 return None
3109 return tokens
3111 def _as_string(self) -> str:
3112 """Render these tokens as a single dotted string if possible,
3113 else raise :class:`.InvalidRequestError` if no unambiguous string form
3114 exists and a string is required.
3116 """
3118 tokens_no_dots = self._tokens_no_dots()
3120 if tokens_no_dots is None:
3122 if not self.table_name:
3123 reason = (
3124 "the target has no table name; a ForeignKey to a Column "
3125 "which is not yet associated with a Table has no names "
3126 "to render until that Column is attached"
3127 )
3128 elif "." in self.table_name:
3129 reason = (
3130 f"the table name {self.table_name!r} contains a dot, "
3131 f"which can't be told apart from the separator between "
3132 f"a schema name and a table name"
3133 )
3134 elif self.column_name is not None and "." in self.column_name:
3135 reason = (
3136 f"the column name {self.column_name!r} contains a dot, "
3137 f"which can't be told apart from the separator between "
3138 f"a table name and a column name"
3139 )
3140 elif self.column_name is None:
3141 reason = (
3142 f"a schema name {self.schema!r} is present with no "
3143 f"column name, so the schema name can't be told apart "
3144 f"from a table name"
3145 )
3146 else:
3147 # this is currently unreachable based on the current behavior
3148 # of tokens_no_dots
3149 assert False
3151 raise exc.InvalidRequestError(
3152 f"Can't render a single string representation for foreign "
3153 f"key target {self._description()}; {reason}. Use "
3154 f"ForeignKey.target_tokens to receive the schema, table and "
3155 f"column names individually."
3156 )
3158 return ".".join(tokens_no_dots)
3160 def _description(self) -> str:
3161 """Render a description of these tokens which never raises.
3163 The dotted string form is preferred when it's available, falling
3164 back to naming the tokens individually when it isn't.
3166 """
3168 tokens_no_dots = self._tokens_no_dots()
3169 if tokens_no_dots:
3170 return repr(".".join(tokens_no_dots))
3171 else:
3172 return (
3173 f"(schema={self.schema!r}, "
3174 f"table_name={self.table_name!r}, "
3175 f"column_name={self.column_name!r})"
3176 )
3178 @classmethod
3179 def _from_string(cls, spec: str) -> ForeignKeyTarget:
3180 """Parse a dotted string colspec into its component tokens.
3182 A FK between column 'bar' and table 'foo' can be specified as 'foo',
3183 'foo.bar', 'dbo.foo.bar', 'otherdb.dbo.foo.bar'. Once we have the
3184 column name and the table name, treat everything else as the schema
3185 name. Some databases (e.g. Sybase) support inter-database foreign
3186 keys. See tickets #1341 and -- indirectly related -- #594.
3188 This assumes that '.' will never appear *within* the table or column
3189 name; a target which does contain such a dot has no string form and
3190 must be given as a :class:`.ForeignKeyTarget` instead.
3192 """
3193 m = spec.split(".")
3194 if len(m) == 1:
3195 return cls(None, m[0], None)
3197 colname = m.pop()
3198 tname = m.pop()
3199 return cls(".".join(m) if m else None, tname, colname)
3201 @classmethod
3202 def _from_argument(
3203 cls, argument: _typing_Sequence[Any]
3204 ) -> ForeignKeyTarget:
3205 """Coerce a two or three token sequence passed by the user."""
3207 if len(argument) == 3:
3208 schema, table_name, column_name = argument
3209 elif len(argument) == 2:
3210 schema = None
3211 table_name, column_name = argument
3212 else:
3213 raise exc.ArgumentError(
3214 f"ForeignKey target given as a tuple must have two tokens "
3215 f"(table_name, column_name) or three tokens "
3216 f"(schema, table_name, column_name); got {len(argument)}"
3217 )
3219 if not table_name:
3220 raise exc.ArgumentError(
3221 "ForeignKey target table_name must be a non-empty string"
3222 )
3224 return cls(schema, table_name, column_name)
3227class ForeignKey(DialectKWArgs, SchemaItem):
3228 """Defines a dependency between two columns.
3230 ``ForeignKey`` is specified as an argument to a :class:`_schema.Column`
3231 object,
3232 e.g.::
3234 t = Table(
3235 "remote_table",
3236 metadata,
3237 Column("remote_id", ForeignKey("main_table.id")),
3238 )
3240 Note that ``ForeignKey`` is only a marker object that defines
3241 a dependency between two columns. The actual constraint
3242 is in all cases represented by the :class:`_schema.ForeignKeyConstraint`
3243 object. This object will be generated automatically when
3244 a ``ForeignKey`` is associated with a :class:`_schema.Column` which
3245 in turn is associated with a :class:`_schema.Table`. Conversely,
3246 when :class:`_schema.ForeignKeyConstraint` is applied to a
3247 :class:`_schema.Table`,
3248 ``ForeignKey`` markers are automatically generated to be
3249 present on each associated :class:`_schema.Column`, which are also
3250 associated with the constraint object.
3252 Note that you cannot define a "composite" foreign key constraint,
3253 that is a constraint between a grouping of multiple parent/child
3254 columns, using ``ForeignKey`` objects. To define this grouping,
3255 the :class:`_schema.ForeignKeyConstraint` object must be used, and applied
3256 to the :class:`_schema.Table`. The associated ``ForeignKey`` objects
3257 are created automatically.
3259 The ``ForeignKey`` objects associated with an individual
3260 :class:`_schema.Column`
3261 object are available in the `foreign_keys` collection
3262 of that column.
3264 The target of a ``ForeignKey`` is described by three names -- a schema
3265 name, a table name and a column name -- which are available at
3266 :attr:`_schema.ForeignKey.target_tokens` as a
3267 :class:`_schema.ForeignKeyTarget` named tuple::
3269 >>> fk = ForeignKey("main_table.id")
3270 >>> fk.target_tokens
3271 ForeignKeyTarget(schema=None, table_name='main_table', column_name='id')
3273 The same three names may be given to ``ForeignKey`` in place of the
3274 dotted string, as either a two or three token tuple. This is the only
3275 way to name a target whose table or column name itself contains a dot,
3276 as the dotted string form has no way of telling such a dot apart from
3277 the separator between two names::
3279 ForeignKey(("my.tbl", "id"))
3280 ForeignKey(("my_schema", "my.tbl", "id"))
3282 .. versionadded:: 2.1 :attr:`_schema.ForeignKey.target_tokens`, and the
3283 tuple form of the target.
3285 Where the target was given as a :class:`_schema.Column` rather than by
3286 name, that column is available at
3287 :attr:`_schema.ForeignKey.target_column`; this is distinct from
3288 :attr:`_schema.ForeignKey.column`, which is the *resolved* target however
3289 it was specified. :attr:`_schema.ForeignKey.target_table_key` gives the
3290 key under which the referenced :class:`_schema.Table` is, or would be,
3291 registered in :attr:`_schema.MetaData.tables`.
3293 Further examples of foreign key configuration are in
3294 :ref:`metadata_foreignkeys`.
3296 """ # noqa: E501
3298 __visit_name__ = "foreign_key"
3300 parent: Column[Any]
3302 _table_column: Optional[Column[Any]]
3303 """Storage for :attr:`.ForeignKey.target_column`; read that instead.
3305 ``None`` when the target was given by name, in which case
3306 :attr:`.ForeignKey._given_tokens` holds those names.
3308 """
3310 _given_tokens: Optional[ForeignKeyTarget]
3311 """The names of the target, when the target was given by name.
3313 ``None`` when the target was given as a :class:`.Column`, in which case
3314 the names are derived from that column on demand -- read
3315 :attr:`.ForeignKey.target_tokens` rather than this.
3317 """
3319 def __init__(
3320 self,
3321 column: _DDLColumnReferenceArgument,
3322 _constraint: Optional[ForeignKeyConstraint] = None,
3323 use_alter: bool = False,
3324 name: _ConstraintNameArgument = None,
3325 onupdate: Optional[str] = None,
3326 ondelete: Optional[str] = None,
3327 deferrable: Optional[bool] = None,
3328 initially: Optional[str] = None,
3329 link_to_name: bool = False,
3330 match: Optional[str] = None,
3331 info: Optional[_InfoType] = None,
3332 comment: Optional[str] = None,
3333 _unresolvable: bool = False,
3334 **dialect_kw: Any,
3335 ):
3336 r"""
3337 Construct a column-level FOREIGN KEY.
3339 The :class:`_schema.ForeignKey` object when constructed generates a
3340 :class:`_schema.ForeignKeyConstraint`
3341 which is associated with the parent
3342 :class:`_schema.Table` object's collection of constraints.
3344 :param column: A single target column for the key relationship. A
3345 :class:`_schema.Column` object or a column name as a string:
3346 ``tablename.columnkey`` or ``schema.tablename.columnkey``.
3347 ``columnkey`` is the ``key`` which has been assigned to the column
3348 (defaults to the column name itself), unless ``link_to_name`` is
3349 ``True`` in which case the rendered name of the column is used.
3351 The target may also be given as a tuple of individual name
3352 tokens, either ``(table_name, column_name)`` or
3353 ``(schema, table_name, column_name)``. This is the only form
3354 which can refer to a name that itself contains a dot, as the
3355 dotted string form has no way of telling such a dot apart from
3356 the separator between two names::
3358 ForeignKey(("my.tbl", "z"))
3360 .. versionadded:: 2.1 The tuple form.
3362 :param name: Optional string. An in-database name for the key if
3363 `constraint` is not provided.
3365 :param onupdate: Optional string. If set, emit ON UPDATE <value> when
3366 issuing DDL for this constraint. Typical values include CASCADE,
3367 DELETE and RESTRICT.
3369 .. seealso::
3371 :ref:`on_update_on_delete`
3373 :param ondelete: Optional string. If set, emit ON DELETE <value> when
3374 issuing DDL for this constraint. Typical values include CASCADE,
3375 SET NULL and RESTRICT. Some dialects may allow for additional
3376 syntaxes.
3378 .. seealso::
3380 :ref:`on_update_on_delete`
3382 :param deferrable: Optional bool. If set, emit DEFERRABLE or NOT
3383 DEFERRABLE when issuing DDL for this constraint.
3385 :param initially: Optional string. If set, emit INITIALLY <value> when
3386 issuing DDL for this constraint.
3388 :param link_to_name: if True, the string name given in ``column`` is
3389 the rendered name of the referenced column, not its locally
3390 assigned ``key``.
3392 :param use_alter: passed to the underlying
3393 :class:`_schema.ForeignKeyConstraint`
3394 to indicate the constraint should
3395 be generated/dropped externally from the CREATE TABLE/ DROP TABLE
3396 statement. See :paramref:`_schema.ForeignKeyConstraint.use_alter`
3397 for further description.
3399 .. seealso::
3401 :paramref:`_schema.ForeignKeyConstraint.use_alter`
3403 :ref:`use_alter`
3405 :param match: Optional string. If set, emit MATCH <value> when issuing
3406 DDL for this constraint. Typical values include SIMPLE, PARTIAL
3407 and FULL.
3409 :param info: Optional data dictionary which will be populated into the
3410 :attr:`.SchemaItem.info` attribute of this object.
3412 :param comment: Optional string that will render an SQL comment on
3413 foreign key constraint creation.
3415 .. versionadded:: 2.0
3417 :param \**dialect_kw: Additional keyword arguments are dialect
3418 specific, and passed in the form ``<dialectname>_<argname>``. The
3419 arguments are ultimately handled by a corresponding
3420 :class:`_schema.ForeignKeyConstraint`.
3421 See the documentation regarding
3422 an individual dialect at :ref:`dialect_toplevel` for detail on
3423 documented arguments.
3425 """
3427 self._unresolvable = _unresolvable
3429 self._table_column, self._given_tokens = self._parse_colspec_argument(
3430 column
3431 )
3433 # the linked ForeignKeyConstraint.
3434 # ForeignKey will create this when parent Column
3435 # is attached to a Table, *or* ForeignKeyConstraint
3436 # object passes itself in when creating ForeignKey
3437 # markers.
3438 self.constraint = _constraint
3440 # .parent is not Optional under normal use
3441 self.parent = None # type: ignore[assignment]
3443 self.use_alter = use_alter
3444 self.name = name
3445 self.onupdate = onupdate
3446 self.ondelete = ondelete
3447 self.deferrable = deferrable
3448 self.initially = initially
3449 self.link_to_name = link_to_name
3450 self.match = match
3451 self.comment = comment
3452 if info:
3453 self.info = info
3454 self._unvalidated_dialect_kw = dialect_kw
3456 def _parse_colspec_argument(
3457 self,
3458 argument: _DDLColumnReferenceArgument,
3459 ) -> Tuple[Optional[Column[Any]], ForeignKeyTarget]:
3460 """Coerce the ``column`` argument into the target
3461 :class:`.ForeignKeyTarget`, along with the target :class:`.Column`
3462 itself if that's how the target was given.
3464 """
3465 if isinstance(argument, tuple):
3466 return None, ForeignKeyTarget._from_argument(argument)
3468 _colspec = coercions.expect(roles.DDLReferredColumnRole, argument)
3470 if isinstance(_colspec, str):
3471 return None, ForeignKeyTarget._from_string(_colspec)
3473 assert isinstance(_colspec, ColumnClause)
3475 table = _colspec.table
3476 if not isinstance(table, (type(None), TableClause)):
3477 # a Column of some other FromClause, e.g. a subquery or a join;
3478 # the target of a ForeignKey has to be a Table (or a TableClause,
3479 # or a Column not yet associated with either)
3480 raise exc.ArgumentError(
3481 f"ForeignKey target Column {_colspec!r} is associated with "
3482 f"{table!r}, which is not a Table; a foreign key may only "
3483 f"target a Column of a Table"
3484 )
3485 elif table is None:
3486 # a Column not yet associated with a Table; this is the
3487 # declarative mixin + declared_attr case, where the target column
3488 # is attached to its Table after this ForeignKey is constructed
3489 return _colspec, ForeignKeyTarget(None, _colspec.key, None)
3490 else:
3491 return _colspec, ForeignKeyTarget(
3492 table.schema, table.name, _colspec.key
3493 )
3495 def __repr__(self) -> str:
3496 tokens = self.target_tokens
3497 tokens_no_dots = self.target_tokens._tokens_no_dots()
3499 if tokens_no_dots:
3500 return f"ForeignKey({'.'.join(tokens_no_dots)!r})"
3501 else:
3502 # no string form for this target, so name the tokens; this still
3503 # round trips, as ForeignKeyTarget is accepted by the constructor
3504 return f"ForeignKey({tokens!r})"
3506 @util.deprecated(
3507 "1.4",
3508 "The :meth:`_schema.ForeignKey.copy` method is deprecated "
3509 "and will be removed in a future release.",
3510 )
3511 def copy(self, *, schema: Optional[str] = None, **kw: Any) -> ForeignKey:
3512 return self._copy(schema=schema, **kw)
3514 def _copy(self, *, schema: Optional[str] = None, **kw: Any) -> ForeignKey:
3515 """Produce a copy of this :class:`_schema.ForeignKey` object.
3517 The new :class:`_schema.ForeignKey` will not be bound
3518 to any :class:`_schema.Column`.
3520 This method is usually used by the internal
3521 copy procedures of :class:`_schema.Column`, :class:`_schema.Table`,
3522 and :class:`_schema.MetaData`.
3524 :param schema: The returned :class:`_schema.ForeignKey` will
3525 reference the original table and column name, qualified
3526 by the given string schema name.
3528 """
3529 fk = ForeignKey(
3530 self._copy_tokens(schema=schema),
3531 use_alter=self.use_alter,
3532 name=self.name,
3533 onupdate=self.onupdate,
3534 ondelete=self.ondelete,
3535 deferrable=self.deferrable,
3536 initially=self.initially,
3537 link_to_name=self.link_to_name,
3538 match=self.match,
3539 comment=self.comment,
3540 **self._unvalidated_dialect_kw,
3541 )
3542 return self._schema_item_copy(fk)
3544 @property
3545 def target_tokens(self) -> ForeignKeyTarget:
3546 """Return the target of this :class:`_schema.ForeignKey` as three
3547 individual name tokens.
3549 The return value is a :class:`_schema.ForeignKeyTarget` named tuple of
3550 ``(schema, table_name, column_name)``. This is the representation
3551 SQLAlchemy itself computes against; unlike
3552 :attr:`_schema.ForeignKey.target_fullname` it is available in all
3553 cases, including when one of the names contains a dot::
3555 >>> from sqlalchemy import Column, ForeignKey, Integer, MetaData, Table
3556 >>> m = MetaData()
3557 >>> r = Table("my.tbl", m, Column("z", Integer, primary_key=True))
3558 >>> t = Table("t", m, Column("a", Integer, ForeignKey(r.c.z)))
3559 >>> list(t.c.a.foreign_keys)[0].target_tokens
3560 ForeignKeyTarget(schema=None, table_name='my.tbl', column_name='z')
3562 The tokens describe the target as it was specified, and are available
3563 whether or not the target :class:`_schema.Column` has been resolved;
3564 for the resolved column, see :attr:`_schema.ForeignKey.column`.
3566 .. versionadded:: 2.1
3568 .. seealso::
3570 :class:`_schema.ForeignKeyTarget`
3572 """ # noqa: E501
3574 col = self._table_column
3575 if col is None:
3576 assert self._given_tokens is not None
3577 return self._given_tokens
3579 # derived on demand rather than captured up front: a target Column
3580 # may gain both its name and its Table *after* this ForeignKey is
3581 # constructed, which is what a declarative mixin using declared_attr
3582 # does (see test_fk_mixin_self_referential_declared_attr)
3583 table = col.table
3584 if table is None:
3585 return ForeignKeyTarget(None, col.key, None)
3587 return ForeignKeyTarget(table.schema, table.name, col.key)
3589 @property
3590 def target_column(self) -> Optional[Column[Any]]:
3591 """Return the target :class:`_schema.Column` of this
3592 :class:`_schema.ForeignKey`, if the target was given as one.
3594 Returns ``None`` when the target was instead given by name, as a
3595 string or as :class:`_schema.ForeignKeyTarget`; in that case only
3596 :attr:`_schema.ForeignKey.target_tokens` describes the target until
3597 it is resolved.
3599 This is distinct from :attr:`_schema.ForeignKey.column`, which is the
3600 *resolved* target however it was specified, and which raises if the
3601 target can't be resolved. Note also that a target given as a
3602 :class:`_schema.Column` need not be associated with a
3603 :class:`_schema.Table` yet.
3605 .. versionadded:: 2.1
3607 .. seealso::
3609 :attr:`_schema.ForeignKey.target_tokens`
3611 :attr:`_schema.ForeignKey.column`
3613 """
3614 return self._table_column
3616 @property
3617 def _column_tokens(self) -> ForeignKeyTarget:
3618 """legacy private name for :attr:`.ForeignKey.target_tokens`"""
3620 return self.target_tokens
3622 @property
3623 def _colspec(self) -> Union[str, Column[Any]]:
3624 """Legacy accessor for the target in its pre-2.1 form, either the
3625 target :class:`.Column` or a dotted string.
3627 Raises :class:`.InvalidRequestError` for a target which has no dotted
3628 string form. Kept for the benefit of third party code which reads it;
3629 :attr:`.ForeignKey.target_tokens` is what SQLAlchemy itself uses.
3631 """
3632 col = self.target_column
3633 return self.target_tokens._as_string() if col is None else col
3635 def _copy_tokens(
3636 self,
3637 schema: Optional[
3638 Union[
3639 str,
3640 Literal[SchemaConst.RETAIN_SCHEMA, SchemaConst.BLANK_SCHEMA],
3641 ]
3642 ] = None,
3643 table_name: Optional[str] = None,
3644 _is_copy: bool = False,
3645 ) -> ForeignKeyTarget:
3646 """Return the tokens for a copy of this :class:`_schema.ForeignKey`,
3647 optionally rewriting the schema and/or table name.
3649 """
3651 col = self.target_column
3653 if _is_copy and col is not None and col.table is None:
3654 raise exc.InvalidRequestError(
3655 f"Can't copy ForeignKey object which refers to "
3656 f"non-table bound Column {col!r}"
3657 )
3659 tokens = self.target_tokens
3661 if schema not in (None, RETAIN_SCHEMA):
3662 return ForeignKeyTarget(
3663 None if schema is BLANK_SCHEMA else schema,
3664 table_name if table_name is not None else tokens.table_name,
3665 tokens.column_name,
3666 )
3667 elif table_name:
3668 return tokens._replace(table_name=table_name)
3669 else:
3670 return tokens
3672 def _get_colspec(self) -> str:
3673 """legacy method for :attr:`.ForeignKey.target_fullname`.
3675 Retained as third party code makes use of it; new code should use
3676 :attr:`.ForeignKey.target_tokens`.
3678 """
3680 return self.target_fullname
3682 @property
3683 def _referred_schema(self) -> Optional[str]:
3684 return self.target_tokens.schema
3686 @property
3687 def target_table_key(self) -> Optional[str]:
3688 """Return the key under which the target :class:`_schema.Table` is,
3689 or would be, registered in :attr:`_schema.MetaData.tables`.
3691 This is derived from :attr:`_schema.ForeignKey.target_tokens`, and is
3692 available whether or not the target table exists yet, making it
3693 usable while a :class:`_schema.Table` is still being constructed.
3695 Returns ``None`` in the one case where no key can be named: the
3696 target was given as a :class:`_schema.Column` which is not yet
3697 associated with a :class:`_schema.Table`.
3699 .. versionadded:: 2.1
3701 .. seealso::
3703 :attr:`_schema.ForeignKey.target_tokens`
3705 """
3706 col = self.target_column
3707 if col is not None and col.table is None:
3708 # target Column not yet associated with a Table, so there is no
3709 # key to report; the tokens name the column, not a table
3710 return None
3712 schema, tname, colname = self.target_tokens
3713 return _get_table_key(tname, schema)
3715 @property
3716 def target_fullname(self) -> str:
3717 """Return the target of this :class:`_schema.ForeignKey` as a single
3718 dotted string, e.g. ``"schema.tablename.columnname"``.
3720 This is usually the equivalent of the string-based
3721 ``"tablename.colname"`` argument first passed to the object's
3722 constructor.
3724 .. versionchanged:: 2.1 This attribute raises
3725 :class:`.InvalidRequestError` when the target table or column name
3726 itself contains a dot, as a dotted string can't distinguish such a
3727 name from the separator between names. The dotted form is now a
3728 legacy convenience; :attr:`_schema.ForeignKey.target_tokens` is the
3729 representation that is available in all cases, and is what
3730 SQLAlchemy itself makes use of.
3732 .. seealso::
3734 :attr:`_schema.ForeignKey.target_tokens`
3736 """
3737 return self.target_tokens._as_string()
3739 def references(self, table: Table) -> bool:
3740 """Return True if the given :class:`_schema.Table`
3741 is referenced by this
3742 :class:`_schema.ForeignKey`."""
3744 return table.corresponding_column(self.column) is not None
3746 def get_referent(self, table: FromClause) -> Optional[Column[Any]]:
3747 """Return the :class:`_schema.Column` in the given
3748 :class:`_schema.Table` (or any :class:`.FromClause`)
3749 referenced by this :class:`_schema.ForeignKey`.
3751 Returns None if this :class:`_schema.ForeignKey`
3752 does not reference the given
3753 :class:`_schema.Table`.
3755 """
3756 # our column is a Column, and any subquery etc. proxying us
3757 # would be doing so via another Column, so that's what would
3758 # be returned here
3759 return table.columns.corresponding_column(self.column) # type: ignore[return-value] # noqa: E501
3761 def _resolve_col_tokens(self) -> Tuple[Table, str, Optional[str]]:
3762 if self.parent is None:
3763 raise exc.InvalidRequestError(
3764 "this ForeignKey object does not yet have a "
3765 "parent Column associated with it."
3766 )
3768 elif self.parent.table is None:
3769 raise exc.InvalidRequestError(
3770 "this ForeignKey's parent column is not yet associated "
3771 "with a Table."
3772 )
3774 parenttable = self.parent.table
3776 if self._unresolvable:
3777 schema, tname, colname = self.target_tokens
3778 tablekey = _get_table_key(tname, schema)
3779 return parenttable, tablekey, colname
3781 # assertion
3782 # basically Column._make_proxy() sends the actual
3783 # target Column to the ForeignKey object, so the
3784 # string resolution here is never called.
3785 for c in self.parent.base_columns:
3786 if isinstance(c, Column):
3787 assert c.table is parenttable
3788 break
3789 else:
3790 assert False
3791 ######################
3793 schema, tname, colname = self.target_tokens
3795 if schema is None and parenttable.metadata.schema is not None:
3796 schema = parenttable.metadata.schema
3798 tablekey = _get_table_key(tname, schema)
3799 return parenttable, tablekey, colname
3801 def _link_to_col_by_colstring(
3802 self, parenttable: Table, table: Table, colname: Optional[str]
3803 ) -> Column[Any]:
3804 _column = None
3805 if colname is None:
3806 # colname is None in the case that ForeignKey argument
3807 # was specified as table name only, in which case we
3808 # match the column name to the same column on the
3809 # parent.
3810 # this use case wasn't working in later 1.x series
3811 # as it had no test coverage; fixed in 2.0
3812 parent = self.parent
3813 assert parent is not None
3814 key = parent.key
3815 _column = table.c.get(key, None)
3816 elif self.link_to_name:
3817 key = colname
3818 for c in table.c:
3819 if c.name == colname:
3820 _column = c
3821 else:
3822 key = colname
3823 _column = table.c.get(colname, None)
3825 if _column is None:
3826 raise exc.NoReferencedColumnError(
3827 "Could not initialize target column "
3828 f"for ForeignKey {self.target_tokens._description()} "
3829 f"on table '{parenttable.name}': "
3830 f"table '{table.name}' has no column named '{key}'",
3831 table.name,
3832 key,
3833 )
3835 return _column
3837 def _set_target_column(self, column: Column[Any]) -> None:
3838 assert self.parent is not None
3840 # propagate TypeEngine to parent if it didn't have one
3841 if self.parent.type._isnull:
3842 self.parent.type = column.type
3844 # super-edgy case, if other FKs point to our column,
3845 # they'd get the type propagated out also.
3847 def set_type(fk: ForeignKey) -> None:
3848 if fk.parent.type._isnull:
3849 fk.parent.type = column.type
3851 self.parent._setup_on_memoized_fks(set_type)
3853 self.column = column # type: ignore[misc]
3855 @util.ro_memoized_property
3856 def column(self) -> Column[Any]:
3857 """Return the target :class:`_schema.Column` referenced by this
3858 :class:`_schema.ForeignKey`.
3860 If no target column has been established, an exception
3861 is raised.
3863 """
3864 return self._resolve_column()
3866 @overload
3867 def _resolve_column(
3868 self, *, raiseerr: Literal[True] = ...
3869 ) -> Column[Any]: ...
3871 @overload
3872 def _resolve_column(
3873 self, *, raiseerr: bool = ...
3874 ) -> Optional[Column[Any]]: ...
3876 def _resolve_column(
3877 self, *, raiseerr: bool = True
3878 ) -> Optional[Column[Any]]:
3879 target_column = self.target_column
3881 if target_column is None:
3882 parenttable, tablekey, colname = self._resolve_col_tokens()
3884 if self._unresolvable or tablekey not in parenttable.metadata:
3885 if not raiseerr:
3886 return None
3887 raise exc.NoReferencedTableError(
3888 f"Foreign key associated with column "
3889 f"'{self.parent}' could not find "
3890 f"table '{tablekey}' with which to generate a "
3891 f"foreign key to target column '{colname}'",
3892 tablekey,
3893 )
3894 elif parenttable.key not in parenttable.metadata:
3895 if not raiseerr:
3896 return None
3897 raise exc.InvalidRequestError(
3898 f"Table {parenttable} is no longer associated with its "
3899 "parent MetaData"
3900 )
3901 else:
3902 table = parenttable.metadata.tables[tablekey]
3903 return self._link_to_col_by_colstring(
3904 parenttable, table, colname
3905 )
3907 else:
3908 assert target_column is not None
3909 return target_column
3911 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
3912 assert isinstance(parent, Column)
3914 if self.parent is not None and self.parent is not parent:
3915 raise exc.InvalidRequestError(
3916 "This ForeignKey already has a parent !"
3917 )
3918 self.parent = parent
3919 self.parent.foreign_keys.add(self)
3920 self.parent._on_table_attach(self._set_table)
3922 def _set_remote_table(self, table: Table) -> None:
3923 parenttable, _, colname = self._resolve_col_tokens()
3924 _column = self._link_to_col_by_colstring(parenttable, table, colname)
3925 self._set_target_column(_column)
3926 assert self.constraint is not None
3927 self.constraint._validate_dest_table(table)
3929 def _remove_from_metadata(self, metadata: MetaData) -> None:
3930 parenttable, table_key, colname = self._resolve_col_tokens()
3931 fk_key = (table_key, colname)
3933 if self in metadata._fk_memos[fk_key]:
3934 # TODO: no test coverage for self not in memos
3935 metadata._fk_memos[fk_key].remove(self)
3937 def _set_table(self, column: Column[Any], table: Table) -> None:
3938 # standalone ForeignKey - create ForeignKeyConstraint
3939 # on the hosting Table when attached to the Table.
3940 assert isinstance(table, Table)
3941 if self.constraint is None:
3942 self.constraint = ForeignKeyConstraint(
3943 [],
3944 [],
3945 use_alter=self.use_alter,
3946 name=self.name,
3947 onupdate=self.onupdate,
3948 ondelete=self.ondelete,
3949 deferrable=self.deferrable,
3950 initially=self.initially,
3951 match=self.match,
3952 comment=self.comment,
3953 **self._unvalidated_dialect_kw,
3954 )
3955 self.constraint._append_element(column, self)
3956 self.constraint._set_parent_with_dispatch(table)
3957 table.foreign_keys.add(self)
3958 # set up remote ".column" attribute, or a note to pick it
3959 # up when the other Table/Column shows up
3961 target_column = self.target_column
3962 if target_column is None:
3963 parenttable, table_key, colname = self._resolve_col_tokens()
3964 fk_key = (table_key, colname)
3965 if table_key in parenttable.metadata.tables:
3966 table = parenttable.metadata.tables[table_key]
3967 try:
3968 _column = self._link_to_col_by_colstring(
3969 parenttable, table, colname
3970 )
3971 except exc.NoReferencedColumnError:
3972 # this is OK, we'll try later
3973 pass
3974 else:
3975 self._set_target_column(_column)
3977 parenttable.metadata._fk_memos[fk_key].append(self)
3978 else:
3979 self._set_target_column(target_column)
3982if TYPE_CHECKING:
3984 def default_is_sequence(
3985 obj: Optional[DefaultGenerator],
3986 ) -> TypeGuard[Sequence]: ...
3988 def default_is_clause_element(
3989 obj: Optional[DefaultGenerator],
3990 ) -> TypeGuard[ColumnElementColumnDefault]: ...
3992 def default_is_scalar(
3993 obj: Optional[DefaultGenerator],
3994 ) -> TypeGuard[ScalarElementColumnDefault]: ...
3996else:
3997 default_is_sequence = operator.attrgetter("is_sequence")
3999 default_is_clause_element = operator.attrgetter("is_clause_element")
4001 default_is_scalar = operator.attrgetter("is_scalar")
4004class DefaultGenerator(Executable, SchemaItem):
4005 """Base class for column *default* values.
4007 This object is only present on column.default or column.onupdate.
4008 It's not valid as a server default.
4010 """
4012 __visit_name__ = "default_generator"
4014 _is_default_generator = True
4015 is_sequence = False
4016 is_identity = False
4017 is_server_default = False
4018 is_clause_element = False
4019 is_callable = False
4020 is_scalar = False
4021 has_arg = False
4022 is_sentinel = False
4023 _is_monotonic_fn = False
4024 column: Optional[Column[Any]]
4026 def __init__(self, for_update: bool = False) -> None:
4027 self.for_update = for_update
4029 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4030 if TYPE_CHECKING:
4031 assert isinstance(parent, Column)
4032 self.column = parent
4033 if self.for_update:
4034 self.column.onupdate = self
4035 else:
4036 self.column.default = self
4038 def _copy(self) -> DefaultGenerator:
4039 raise NotImplementedError()
4041 def _execute_on_connection(
4042 self,
4043 connection: Connection,
4044 distilled_params: _CoreMultiExecuteParams,
4045 execution_options: CoreExecuteOptionsParameter,
4046 ) -> Any:
4047 util.warn_deprecated(
4048 "Using the .execute() method to invoke a "
4049 "DefaultGenerator object is deprecated; please use "
4050 "the .scalar() method.",
4051 "2.0",
4052 )
4053 return self._execute_on_scalar(
4054 connection, distilled_params, execution_options
4055 )
4057 def _execute_on_scalar(
4058 self,
4059 connection: Connection,
4060 distilled_params: _CoreMultiExecuteParams,
4061 execution_options: CoreExecuteOptionsParameter,
4062 ) -> Any:
4063 return connection._execute_default(
4064 self, distilled_params, execution_options
4065 )
4068class ColumnDefault(DefaultGenerator, ABC):
4069 """A plain default value on a column.
4071 This could correspond to a constant, a callable function,
4072 or a SQL clause.
4074 :class:`.ColumnDefault` is generated automatically
4075 whenever the ``default``, ``onupdate`` arguments of
4076 :class:`_schema.Column` are used. A :class:`.ColumnDefault`
4077 can be passed positionally as well.
4079 For example, the following::
4081 Column("foo", Integer, default=50)
4083 Is equivalent to::
4085 Column("foo", Integer, ColumnDefault(50))
4087 """
4089 arg: Any
4091 _is_monotonic_fn = False
4093 @overload
4094 def __new__(
4095 cls, arg: Callable[..., Any], for_update: bool = ...
4096 ) -> CallableColumnDefault: ...
4098 @overload
4099 def __new__(
4100 cls, arg: ColumnElement[Any], for_update: bool = ...
4101 ) -> ColumnElementColumnDefault: ...
4103 # if I return ScalarElementColumnDefault here, which is what's actually
4104 # returned, mypy complains that
4105 # overloads overlap w/ incompatible return types.
4106 @overload
4107 def __new__(cls, arg: object, for_update: bool = ...) -> ColumnDefault: ...
4109 def __new__(
4110 cls, arg: Any = None, for_update: bool = False
4111 ) -> ColumnDefault:
4112 """Construct a new :class:`.ColumnDefault`.
4115 :param arg: argument representing the default value.
4116 May be one of the following:
4118 * a plain non-callable Python value, such as a
4119 string, integer, boolean, or other simple type.
4120 The default value will be used as is each time.
4121 * a SQL expression, that is one which derives from
4122 :class:`_expression.ColumnElement`. The SQL expression will
4123 be rendered into the INSERT or UPDATE statement,
4124 or in the case of a primary key column when
4125 RETURNING is not used may be
4126 pre-executed before an INSERT within a SELECT.
4127 * A Python callable. The function will be invoked for each
4128 new row subject to an INSERT or UPDATE.
4129 The callable must accept exactly
4130 zero or one positional arguments. The one-argument form
4131 will receive an instance of the :class:`.ExecutionContext`,
4132 which provides contextual information as to the current
4133 :class:`_engine.Connection` in use as well as the current
4134 statement and parameters.
4136 """
4138 if isinstance(arg, FetchedValue):
4139 raise exc.ArgumentError(
4140 "ColumnDefault may not be a server-side default type."
4141 )
4142 elif callable(arg):
4143 cls = CallableColumnDefault
4144 elif isinstance(arg, ClauseElement):
4145 cls = ColumnElementColumnDefault
4146 elif arg is not None:
4147 cls = ScalarElementColumnDefault
4149 return object.__new__(cls)
4151 def __repr__(self) -> str:
4152 return f"{self.__class__.__name__}({self.arg!r})"
4155class ScalarElementColumnDefault(ColumnDefault):
4156 """default generator for a fixed scalar Python value
4158 .. versionadded:: 2.0
4160 """
4162 is_scalar = True
4163 has_arg = True
4165 def __init__(self, arg: Any, for_update: bool = False) -> None:
4166 self.for_update = for_update
4167 self.arg = arg
4169 def _copy(self) -> ScalarElementColumnDefault:
4170 return ScalarElementColumnDefault(
4171 arg=self.arg, for_update=self.for_update
4172 )
4175class _InsertSentinelColumnDefault(ColumnDefault):
4176 """Default generator that's specific to the use of a "sentinel" column
4177 when using the insertmanyvalues feature.
4179 This default is used as part of the :func:`_schema.insert_sentinel`
4180 construct.
4182 """
4184 is_sentinel = True
4185 for_update = False
4186 arg = None
4188 def __new__(cls) -> _InsertSentinelColumnDefault:
4189 return object.__new__(cls)
4191 def __init__(self) -> None:
4192 pass
4194 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4195 col = cast("Column[Any]", parent)
4196 if not col._insert_sentinel:
4197 raise exc.ArgumentError(
4198 "The _InsertSentinelColumnDefault may only be applied to a "
4199 "Column marked as insert_sentinel=True"
4200 )
4201 elif not col.nullable:
4202 raise exc.ArgumentError(
4203 "The _InsertSentinelColumnDefault may only be applied to a "
4204 "Column that is nullable"
4205 )
4207 super()._set_parent(parent, **kw)
4209 def _copy(self) -> _InsertSentinelColumnDefault:
4210 return _InsertSentinelColumnDefault()
4213_SQLExprDefault = Union["ColumnElement[Any]", "TextClause"]
4216class ColumnElementColumnDefault(ColumnDefault):
4217 """default generator for a SQL expression
4219 .. versionadded:: 2.0
4221 """
4223 is_clause_element = True
4224 has_arg = True
4225 arg: _SQLExprDefault
4227 def __init__(
4228 self,
4229 arg: _SQLExprDefault,
4230 for_update: bool = False,
4231 ) -> None:
4232 self.for_update = for_update
4233 self.arg = arg
4235 def _copy(self) -> ColumnElementColumnDefault:
4236 return ColumnElementColumnDefault(
4237 arg=self.arg, for_update=self.for_update
4238 )
4240 @util.memoized_property
4241 @util.preload_module("sqlalchemy.sql.functions")
4242 def _is_monotonic_fn(self) -> bool:
4243 functions = util.preloaded.sql_functions
4244 return (
4245 isinstance(self.arg, functions.FunctionElement)
4246 and self.arg.monotonic
4247 )
4249 @util.memoized_property
4250 @util.preload_module("sqlalchemy.sql.sqltypes")
4251 def _arg_is_typed(self) -> bool:
4252 sqltypes = util.preloaded.sql_sqltypes
4254 return not isinstance(self.arg.type, sqltypes.NullType)
4257class _CallableColumnDefaultProtocol(Protocol):
4258 def __call__(self, context: ExecutionContext) -> Any: ...
4261class CallableColumnDefault(ColumnDefault):
4262 """default generator for a callable Python function
4264 .. versionadded:: 2.0
4266 """
4268 is_callable = True
4269 arg: _CallableColumnDefaultProtocol
4270 has_arg = True
4272 def __init__(
4273 self,
4274 arg: Union[_CallableColumnDefaultProtocol, Callable[[], Any]],
4275 for_update: bool = False,
4276 ) -> None:
4277 self.for_update = for_update
4278 self.arg = self._maybe_wrap_callable(arg)
4280 def _copy(self) -> CallableColumnDefault:
4281 return CallableColumnDefault(arg=self.arg, for_update=self.for_update)
4283 def _maybe_wrap_callable(
4284 self, fn: Union[_CallableColumnDefaultProtocol, Callable[[], Any]]
4285 ) -> _CallableColumnDefaultProtocol:
4286 """Wrap callables that don't accept a context.
4288 This is to allow easy compatibility with default callables
4289 that aren't specific to accepting of a context.
4291 """
4293 try:
4294 argspec = util.get_callable_argspec(fn, no_self=True)
4295 except TypeError:
4296 return util.wrap_callable(lambda ctx: fn(), fn) # type: ignore[call-arg, no-any-return, no-untyped-call] # noqa: E501
4298 defaulted = argspec[3] is not None and len(argspec[3]) or 0
4299 positionals = len(argspec[0]) - defaulted
4301 if positionals == 0:
4302 return util.wrap_callable(lambda ctx: fn(), fn) # type: ignore[call-arg, no-any-return, no-untyped-call] # noqa: E501
4304 elif positionals == 1:
4305 return fn # type: ignore[return-value]
4306 else:
4307 raise exc.ArgumentError(
4308 "ColumnDefault Python function takes zero or one "
4309 "positional arguments"
4310 )
4313class IdentityOptions(DialectKWArgs):
4314 """Defines options for a named database sequence or an identity column.
4316 .. seealso::
4318 :class:`.Sequence`
4320 """
4322 def __init__(
4323 self,
4324 start: Optional[int] = None,
4325 increment: Optional[int] = None,
4326 minvalue: Optional[int] = None,
4327 maxvalue: Optional[int] = None,
4328 nominvalue: Optional[bool] = None,
4329 nomaxvalue: Optional[bool] = None,
4330 cycle: Optional[bool] = None,
4331 cache: Optional[int] = None,
4332 order: Optional[bool] = None,
4333 **dialect_kw: Any,
4334 ) -> None:
4335 """Construct a :class:`.IdentityOptions` object.
4337 See the :class:`.Sequence` documentation for a complete description
4338 of the parameters.
4340 :param start: the starting index of the sequence.
4341 :param increment: the increment value of the sequence.
4342 :param minvalue: the minimum value of the sequence.
4343 :param maxvalue: the maximum value of the sequence.
4344 :param nominvalue: no minimum value of the sequence.
4345 :param nomaxvalue: no maximum value of the sequence.
4346 :param cycle: allows the sequence to wrap around when the maxvalue
4347 or minvalue has been reached.
4348 :param cache: optional integer value; number of future values in the
4349 sequence which are calculated in advance.
4350 :param order: optional boolean value; if ``True``, renders the
4351 ORDER keyword.
4353 .. deprecated:: 2.1 Use ``oracle_order`` instead.
4355 """
4356 self.start = start
4357 self.increment = increment
4358 self.minvalue = minvalue
4359 self.maxvalue = maxvalue
4360 self.nominvalue = nominvalue
4361 self.nomaxvalue = nomaxvalue
4362 self.cycle = cycle
4363 self.cache = cache
4364 if order is not None:
4365 if "oracle_order" in dialect_kw:
4366 raise exc.ArgumentError(
4367 "Cannot specify both 'order' and 'oracle_order'. "
4368 "Please use only 'oracle_order'."
4369 )
4370 dialect_kw["oracle_order"] = order
4371 self._validate_dialect_kwargs(dialect_kw)
4373 @property
4374 def _increment_is_negative(self) -> bool:
4375 return self.increment is not None and self.increment < 0
4377 @property
4378 def order(self) -> Optional[bool]:
4379 """Alias of the ``dialect_kwargs`` ``'oracle_order'``.
4381 .. deprecated:: 2.1 The 'order' attribute is deprecated.
4382 """
4383 value: Optional[bool] = self.dialect_kwargs.get("oracle_order")
4384 return value
4386 def _as_dict(self) -> Dict[str, Any]:
4387 return {
4388 k: v
4389 for k, v in {
4390 "start": self.start,
4391 "increment": self.increment,
4392 "minvalue": self.minvalue,
4393 "maxvalue": self.maxvalue,
4394 "nominvalue": self.nominvalue,
4395 "nomaxvalue": self.nomaxvalue,
4396 "cycle": self.cycle,
4397 "cache": self.cache,
4398 }.items()
4399 if v != None
4400 }
4403class Sequence(HasSchemaAttr, IdentityOptions, DefaultGenerator):
4404 """Represents a named database sequence.
4406 The :class:`.Sequence` object represents the name and configurational
4407 parameters of a database sequence. It also represents
4408 a construct that can be "executed" by a SQLAlchemy :class:`_engine.Engine`
4409 or :class:`_engine.Connection`,
4410 rendering the appropriate "next value" function
4411 for the target database and returning a result.
4413 The :class:`.Sequence` is typically associated with a primary key column::
4415 some_table = Table(
4416 "some_table",
4417 metadata,
4418 Column(
4419 "id",
4420 Integer,
4421 Sequence("some_table_seq", start=1),
4422 primary_key=True,
4423 ),
4424 )
4426 When CREATE TABLE is emitted for the above :class:`_schema.Table`, if the
4427 target platform supports sequences, a CREATE SEQUENCE statement will
4428 be emitted as well. For platforms that don't support sequences,
4429 the :class:`.Sequence` construct is ignored.
4431 .. seealso::
4433 :ref:`defaults_sequences`
4435 :class:`.CreateSequence`
4437 :class:`.DropSequence`
4439 """
4441 __visit_name__ = "sequence"
4443 is_sequence = True
4445 column: Optional[Column[Any]]
4446 data_type: Optional[TypeEngine[int]]
4448 metadata: Optional[MetaData]
4450 @util.deprecated_params(
4451 order=(
4452 "2.1",
4453 "This parameter is supported only by Oracle Database, "
4454 "use ``oracle_order`` instead.",
4455 )
4456 )
4457 def __init__(
4458 self,
4459 name: str,
4460 start: Optional[int] = None,
4461 increment: Optional[int] = None,
4462 minvalue: Optional[int] = None,
4463 maxvalue: Optional[int] = None,
4464 nominvalue: Optional[bool] = None,
4465 nomaxvalue: Optional[bool] = None,
4466 cycle: Optional[bool] = None,
4467 schema: Optional[Union[str, Literal[SchemaConst.BLANK_SCHEMA]]] = None,
4468 cache: Optional[int] = None,
4469 order: Optional[bool] = None,
4470 data_type: Optional[_TypeEngineArgument[int]] = None,
4471 optional: bool = False,
4472 quote: Optional[bool] = None,
4473 metadata: Optional[MetaData] = None,
4474 quote_schema: Optional[bool] = None,
4475 for_update: bool = False,
4476 **dialect_kw: Any,
4477 ) -> None:
4478 """Construct a :class:`.Sequence` object.
4480 :param name: the name of the sequence.
4482 :param start: the starting index of the sequence. This value is
4483 used when the CREATE SEQUENCE command is emitted to the database
4484 as the value of the "START WITH" clause. If ``None``, the
4485 clause is omitted, which on most platforms indicates a starting
4486 value of 1.
4488 .. versionchanged:: 2.0 The :paramref:`.Sequence.start` parameter
4489 is required in order to have DDL emit "START WITH". This is a
4490 reversal of a change made in version 1.4 which would implicitly
4491 render "START WITH 1" if the :paramref:`.Sequence.start` were
4492 not included. See :ref:`change_7211` for more detail.
4494 :param increment: the increment value of the sequence. This
4495 value is used when the CREATE SEQUENCE command is emitted to
4496 the database as the value of the "INCREMENT BY" clause. If ``None``,
4497 the clause is omitted, which on most platforms indicates an
4498 increment of 1.
4499 :param minvalue: the minimum value of the sequence. This
4500 value is used when the CREATE SEQUENCE command is emitted to
4501 the database as the value of the "MINVALUE" clause. If ``None``,
4502 the clause is omitted, which on most platforms indicates a
4503 minvalue of 1 and -2^63-1 for ascending and descending sequences,
4504 respectively.
4506 :param maxvalue: the maximum value of the sequence. This
4507 value is used when the CREATE SEQUENCE command is emitted to
4508 the database as the value of the "MAXVALUE" clause. If ``None``,
4509 the clause is omitted, which on most platforms indicates a
4510 maxvalue of 2^63-1 and -1 for ascending and descending sequences,
4511 respectively.
4513 :param nominvalue: no minimum value of the sequence. This
4514 value is used when the CREATE SEQUENCE command is emitted to
4515 the database as the value of the "NO MINVALUE" clause. If ``None``,
4516 the clause is omitted, which on most platforms indicates a
4517 minvalue of 1 and -2^63-1 for ascending and descending sequences,
4518 respectively.
4520 :param nomaxvalue: no maximum value of the sequence. This
4521 value is used when the CREATE SEQUENCE command is emitted to
4522 the database as the value of the "NO MAXVALUE" clause. If ``None``,
4523 the clause is omitted, which on most platforms indicates a
4524 maxvalue of 2^63-1 and -1 for ascending and descending sequences,
4525 respectively.
4527 :param cycle: allows the sequence to wrap around when the maxvalue
4528 or minvalue has been reached by an ascending or descending sequence
4529 respectively. This value is used when the CREATE SEQUENCE command
4530 is emitted to the database as the "CYCLE" clause. If the limit is
4531 reached, the next number generated will be the minvalue or maxvalue,
4532 respectively. If cycle=False (the default) any calls to nextval
4533 after the sequence has reached its maximum value will return an
4534 error.
4536 :param schema: optional schema name for the sequence, if located
4537 in a schema other than the default. The rules for selecting the
4538 schema name when a :class:`_schema.MetaData`
4539 is also present are the same
4540 as that of :paramref:`_schema.Table.schema`.
4542 :param cache: optional integer value; number of future values in the
4543 sequence which are calculated in advance. Renders the CACHE keyword
4544 understood by Oracle Database and PostgreSQL.
4546 :param order: optional boolean value; if ``True``, renders the
4547 ORDER keyword, understood by Oracle Database, indicating the sequence
4548 is definitively ordered. May be necessary to provide deterministic
4549 ordering using Oracle RAC.
4551 :param data_type: The type to be returned by the sequence, for
4552 dialects that allow us to choose between INTEGER, BIGINT, etc.
4553 (e.g., mssql).
4555 .. versionadded:: 1.4.0
4557 :param optional: boolean value, when ``True``, indicates that this
4558 :class:`.Sequence` object only needs to be explicitly generated
4559 on backends that don't provide another way to generate primary
4560 key identifiers. Currently, it essentially means, "don't create
4561 this sequence on the PostgreSQL backend, where the SERIAL keyword
4562 creates a sequence for us automatically".
4563 :param quote: boolean value, when ``True`` or ``False``, explicitly
4564 forces quoting of the :paramref:`_schema.Sequence.name` on or off.
4565 When left at its default of ``None``, normal quoting rules based
4566 on casing and reserved words take place.
4567 :param quote_schema: Set the quoting preferences for the ``schema``
4568 name.
4570 :param metadata: optional :class:`_schema.MetaData` object which this
4571 :class:`.Sequence` will be associated with. A :class:`.Sequence`
4572 that is associated with a :class:`_schema.MetaData`
4573 gains the following
4574 capabilities:
4576 * The :class:`.Sequence` will inherit the
4577 :paramref:`_schema.MetaData.schema`
4578 parameter specified to the target :class:`_schema.MetaData`, which
4579 affects the production of CREATE / DROP DDL, if any.
4581 * The :meth:`.Sequence.create` and :meth:`.Sequence.drop` methods
4582 automatically use the engine bound to the :class:`_schema.MetaData`
4583 object, if any.
4585 * The :meth:`_schema.MetaData.create_all` and
4586 :meth:`_schema.MetaData.drop_all`
4587 methods will emit CREATE / DROP for this :class:`.Sequence`,
4588 even if the :class:`.Sequence` is not associated with any
4589 :class:`_schema.Table` / :class:`_schema.Column`
4590 that's a member of this
4591 :class:`_schema.MetaData`.
4593 The above behaviors can only occur if the :class:`.Sequence` is
4594 explicitly associated with the :class:`_schema.MetaData`
4595 via this parameter.
4597 .. seealso::
4599 :ref:`sequence_metadata` - full discussion of the
4600 :paramref:`.Sequence.metadata` parameter.
4602 :param for_update: Indicates this :class:`.Sequence`, when associated
4603 with a :class:`_schema.Column`,
4604 should be invoked for UPDATE statements
4605 on that column's table, rather than for INSERT statements, when
4606 no value is otherwise present for that column in the statement.
4608 """
4609 DefaultGenerator.__init__(self, for_update=for_update)
4610 IdentityOptions.__init__(
4611 self,
4612 start=start,
4613 increment=increment,
4614 minvalue=minvalue,
4615 maxvalue=maxvalue,
4616 nominvalue=nominvalue,
4617 nomaxvalue=nomaxvalue,
4618 cycle=cycle,
4619 cache=cache,
4620 order=order,
4621 **dialect_kw,
4622 )
4623 self.column = None
4624 self.name = quoted_name(name, quote)
4625 self.optional = optional
4626 if schema is BLANK_SCHEMA:
4627 self.schema = schema = None
4628 elif metadata is not None and schema is None and metadata.schema:
4629 self.schema = schema = metadata.schema
4630 else:
4631 self.schema = quoted_name.construct(schema, quote_schema)
4632 self._key = _get_table_key(name, schema)
4633 if data_type is not None:
4634 self.data_type = to_instance(data_type)
4635 else:
4636 self.data_type = None
4638 if metadata:
4639 self._set_metadata(metadata)
4640 else:
4641 self.metadata = None
4643 @util.preload_module("sqlalchemy.sql.functions")
4644 def next_value(self) -> Function[int]:
4645 """Return a :class:`.next_value` function element
4646 which will render the appropriate increment function
4647 for this :class:`.Sequence` within any SQL expression.
4649 """
4650 return util.preloaded.sql_functions.func.next_value(self)
4652 def _copy(self) -> Sequence:
4653 return Sequence(
4654 name=self.name,
4655 schema=self.schema,
4656 data_type=self.data_type,
4657 optional=self.optional,
4658 metadata=None,
4659 for_update=self.for_update,
4660 **self._as_dict(),
4661 **self.dialect_kwargs,
4662 **self._reflect_only_kwargs,
4663 )
4665 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4666 assert isinstance(parent, Column)
4667 super()._set_parent(parent, **kw)
4668 parent._on_table_attach(self._set_table)
4670 def _set_table(self, column: Column[Any], table: Table) -> None:
4671 self._set_metadata(table.metadata)
4673 def _set_metadata(self, metadata: MetaData) -> None:
4674 self.metadata = metadata
4675 self.metadata._register_object(self)
4676 metadata._sequences[self._key] = self
4678 def create(
4679 self,
4680 bind: _CreateDropBind,
4681 checkfirst: Union[bool, CheckFirst] = CheckFirst.SEQUENCES,
4682 ) -> None:
4683 """Creates this sequence in the database."""
4685 bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
4687 def drop(
4688 self,
4689 bind: _CreateDropBind,
4690 checkfirst: Union[bool, CheckFirst] = CheckFirst.SEQUENCES,
4691 ) -> None:
4692 """Drops this sequence from the database."""
4694 bind._run_ddl_visitor(ddl.SchemaDropper, self, checkfirst=checkfirst)
4696 def _not_a_column_expr(self) -> NoReturn:
4697 raise exc.InvalidRequestError(
4698 f"This {self.__class__.__name__} cannot be used directly "
4699 "as a column expression. Use func.next_value(sequence) "
4700 "to produce a 'next value' function that's usable "
4701 "as a column element."
4702 )
4705@inspection._self_inspects
4706class FetchedValue(SchemaEventTarget):
4707 """A marker for a transparent database-side default.
4709 Use :class:`.FetchedValue` when the database is configured
4710 to provide some automatic default for a column.
4712 E.g.::
4714 Column("foo", Integer, FetchedValue())
4716 Would indicate that some trigger or default generator
4717 will create a new value for the ``foo`` column during an
4718 INSERT.
4720 .. seealso::
4722 :ref:`triggered_columns`
4724 """
4726 is_server_default = True
4727 reflected = False
4728 has_argument = False
4729 is_clause_element = False
4730 is_identity = False
4731 _is_monotonic_fn = False
4733 column: Optional[Column[Any]]
4735 def __init__(self, for_update: bool = False) -> None:
4736 self.for_update = for_update
4738 def _as_for_update(self, for_update: bool) -> FetchedValue:
4739 if for_update == self.for_update:
4740 return self
4741 else:
4742 return self._clone(for_update)
4744 def _copy(self) -> Self:
4745 return self._clone(self.for_update)
4747 def _clone(self, for_update: bool) -> Self:
4748 n = self.__class__.__new__(self.__class__)
4749 n.__dict__.update(self.__dict__)
4750 n.__dict__.pop("column", None)
4751 n.for_update = for_update
4752 return n
4754 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4755 column = parent
4756 assert isinstance(column, Column)
4757 self.column = column
4758 if self.for_update:
4759 self.column.server_onupdate = self
4760 else:
4761 self.column.server_default = self
4763 def __repr__(self) -> str:
4764 return util.generic_repr(self)
4767class DefaultClause(FetchedValue):
4768 """A DDL-specified DEFAULT column value.
4770 :class:`.DefaultClause` is a :class:`.FetchedValue`
4771 that also generates a "DEFAULT" clause when
4772 "CREATE TABLE" is emitted.
4774 :class:`.DefaultClause` is generated automatically
4775 whenever the ``server_default``, ``server_onupdate`` arguments of
4776 :class:`_schema.Column` are used. A :class:`.DefaultClause`
4777 can be passed positionally as well.
4779 For example, the following::
4781 Column("foo", Integer, server_default="50")
4783 Is equivalent to::
4785 Column("foo", Integer, DefaultClause("50"))
4787 """
4789 has_argument = True
4791 def __init__(
4792 self,
4793 arg: Union[str, ClauseElement, TextClause],
4794 for_update: bool = False,
4795 _reflected: bool = False,
4796 ) -> None:
4797 util.assert_arg_type(arg, (str, ClauseElement, TextClause), "arg")
4798 super().__init__(for_update)
4799 self.arg = arg
4800 self.reflected = _reflected
4802 @util.memoized_property
4803 @util.preload_module("sqlalchemy.sql.functions")
4804 def _is_monotonic_fn(self) -> bool:
4805 functions = util.preloaded.sql_functions
4806 return (
4807 isinstance(self.arg, functions.FunctionElement)
4808 and self.arg.monotonic
4809 )
4811 def __repr__(self) -> str:
4812 return "DefaultClause(%r, for_update=%r)" % (self.arg, self.for_update)
4815class Constraint(DialectKWArgs, HasConditionalDDL, SchemaItem):
4816 """A table-level SQL constraint.
4818 :class:`_schema.Constraint` serves as the base class for the series of
4819 constraint objects that can be associated with :class:`_schema.Table`
4820 objects, including :class:`_schema.PrimaryKeyConstraint`,
4821 :class:`_schema.ForeignKeyConstraint`
4822 :class:`_schema.UniqueConstraint`, and
4823 :class:`_schema.CheckConstraint`.
4825 """
4827 __visit_name__ = "constraint"
4829 _creation_order: int
4830 _column_flag: bool
4832 def __init__(
4833 self,
4834 name: _ConstraintNameArgument = None,
4835 deferrable: Optional[bool] = None,
4836 initially: Optional[str] = None,
4837 info: Optional[_InfoType] = None,
4838 comment: Optional[str] = None,
4839 _create_rule: Optional[Any] = None,
4840 _type_bound: bool = False,
4841 **dialect_kw: Any,
4842 ) -> None:
4843 r"""Create a SQL constraint.
4845 :param name:
4846 Optional, the in-database name of this ``Constraint``.
4848 :param deferrable:
4849 Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
4850 issuing DDL for this constraint.
4852 :param initially:
4853 Optional string. If set, emit INITIALLY <value> when issuing DDL
4854 for this constraint.
4856 :param info: Optional data dictionary which will be populated into the
4857 :attr:`.SchemaItem.info` attribute of this object.
4859 :param comment: Optional string that will render an SQL comment on
4860 foreign key constraint creation.
4862 .. versionadded:: 2.0
4864 :param \**dialect_kw: Additional keyword arguments are dialect
4865 specific, and passed in the form ``<dialectname>_<argname>``. See
4866 the documentation regarding an individual dialect at
4867 :ref:`dialect_toplevel` for detail on documented arguments.
4869 :param _create_rule:
4870 used internally by some datatypes that also create constraints.
4872 :param _type_bound:
4873 used internally to indicate that this constraint is associated with
4874 a specific datatype.
4876 """
4878 self.name = name
4879 self.deferrable = deferrable
4880 self.initially = initially
4881 if info:
4882 self.info = info
4883 self._create_rule = _create_rule
4884 self._type_bound = _type_bound
4885 util.set_creation_order(self)
4886 self._validate_dialect_kwargs(dialect_kw)
4887 self.comment = comment
4889 def _should_create_for_compiler(
4890 self, compiler: DDLCompiler, **kw: Any
4891 ) -> bool:
4892 if self._create_rule is not None and not self._create_rule(compiler):
4893 return False
4894 elif self._ddl_if is not None:
4895 return self._ddl_if._should_execute(
4896 ddl.CreateConstraint(self), self, None, compiler=compiler, **kw
4897 )
4898 else:
4899 return True
4901 @property
4902 def table(self) -> Table:
4903 try:
4904 if isinstance(self.parent, Table):
4905 return self.parent
4906 except AttributeError:
4907 pass
4908 raise exc.InvalidRequestError(
4909 "This constraint is not bound to a table. Did you "
4910 "mean to call table.append_constraint(constraint) ?"
4911 )
4913 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4914 assert isinstance(parent, (Table, Column))
4915 self.parent = parent
4916 parent.constraints.add(self)
4918 @util.deprecated(
4919 "1.4",
4920 "The :meth:`_schema.Constraint.copy` method is deprecated "
4921 "and will be removed in a future release.",
4922 )
4923 def copy(self, **kw: Any) -> Self:
4924 return self._copy(**kw)
4926 def _copy(self, **kw: Any) -> Self:
4927 raise NotImplementedError()
4930class ColumnCollectionMixin:
4931 """A :class:`_expression.ColumnCollection` of :class:`_schema.Column`
4932 objects.
4934 This collection represents the columns which are referred to by
4935 this object.
4937 """
4939 _columns: WriteableColumnCollection[str, Column[Any]]
4941 _column_collection_class: ClassVar[
4942 Type[WriteableColumnCollection[Any, Any]]
4943 ] = DedupeColumnCollection
4945 _allow_multiple_tables = False
4947 _pending_colargs: List[Optional[Union[str, Column[Any]]]]
4949 if TYPE_CHECKING:
4951 def _set_parent_with_dispatch(
4952 self, parent: SchemaEventTarget, **kw: Any
4953 ) -> None: ...
4955 def __init__(
4956 self,
4957 *columns: _DDLColumnArgument,
4958 _autoattach: bool = True,
4959 _column_flag: bool = False,
4960 _gather_expressions: Optional[
4961 List[Union[str, ColumnElement[Any]]]
4962 ] = None,
4963 ) -> None:
4964 self._column_flag = _column_flag
4965 self._columns = self._column_collection_class()
4967 processed_expressions: Optional[
4968 List[Union[ColumnElement[Any], str]]
4969 ] = _gather_expressions
4971 if processed_expressions is not None:
4973 # this is expected to be an empty list
4974 assert not processed_expressions
4976 self._pending_colargs = []
4977 for (
4978 expr,
4979 _,
4980 _,
4981 add_element,
4982 ) in coercions.expect_col_expression_collection(
4983 roles.DDLConstraintColumnRole, columns
4984 ):
4985 self._pending_colargs.append(add_element)
4986 processed_expressions.append(expr)
4987 else:
4988 self._pending_colargs = [
4989 coercions.expect(roles.DDLConstraintColumnRole, column)
4990 for column in columns
4991 ]
4993 if _autoattach and self._pending_colargs:
4994 self._check_attach()
4996 def _check_attach(self, evt: bool = False) -> None:
4997 col_objs = [c for c in self._pending_colargs if isinstance(c, Column)]
4999 cols_w_table = [c for c in col_objs if isinstance(c.table, Table)]
5001 cols_wo_table = set(col_objs).difference(cols_w_table)
5002 if cols_wo_table:
5003 # feature #3341 - place event listeners for Column objects
5004 # such that when all those cols are attached, we autoattach.
5005 assert not evt, "Should not reach here on event call"
5007 # issue #3411 - don't do the per-column auto-attach if some of the
5008 # columns are specified as strings.
5009 has_string_cols = {
5010 c for c in self._pending_colargs if c is not None
5011 }.difference(col_objs)
5012 if not has_string_cols:
5014 def _col_attached(column: Column[Any], table: Table) -> None:
5015 # this isinstance() corresponds with the
5016 # isinstance() above; only want to count Table-bound
5017 # columns
5018 if isinstance(table, Table):
5019 cols_wo_table.discard(column)
5020 if not cols_wo_table:
5021 self._check_attach(evt=True)
5023 self._cols_wo_table = cols_wo_table
5024 for col in cols_wo_table:
5025 col._on_table_attach(_col_attached)
5026 return
5028 columns = cols_w_table
5030 tables = {c.table for c in columns}
5031 if len(tables) == 1:
5032 self._set_parent_with_dispatch(tables.pop())
5033 elif len(tables) > 1 and not self._allow_multiple_tables:
5034 table = columns[0].table
5035 others = [c for c in columns[1:] if c.table is not table]
5036 if others:
5037 # black could not format this inline
5038 other_str = ", ".join("'%s'" % c for c in others)
5039 raise exc.ArgumentError(
5040 f"Column(s) {other_str} "
5041 f"are not part of table '{table.description}'."
5042 )
5044 @util.ro_memoized_property
5045 def columns(self) -> ReadOnlyColumnCollection[str, Column[Any]]:
5046 return self._columns.as_readonly()
5048 @util.ro_memoized_property
5049 def c(self) -> ReadOnlyColumnCollection[str, Column[Any]]:
5050 return self._columns.as_readonly()
5052 def _col_expressions(
5053 self, parent: Union[Table, Column[Any]]
5054 ) -> List[Optional[Column[Any]]]:
5055 if isinstance(parent, Column):
5056 result: List[Optional[Column[Any]]] = [
5057 c for c in self._pending_colargs if isinstance(c, Column)
5058 ]
5059 assert len(result) == len(self._pending_colargs)
5060 return result
5061 else:
5062 try:
5063 return [
5064 parent.c[col] if isinstance(col, str) else col
5065 for col in self._pending_colargs
5066 ]
5067 except KeyError as ke:
5068 raise exc.ConstraintColumnNotFoundError(
5069 f"Can't create {self.__class__.__name__} "
5070 f"on table '{parent.description}': no column "
5071 f"named '{ke.args[0]}' is present."
5072 ) from ke
5074 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
5075 assert isinstance(parent, (Table, Column))
5077 for col in self._col_expressions(parent):
5078 if col is not None:
5079 self._columns.add(col)
5082class ColumnCollectionConstraint(ColumnCollectionMixin, Constraint):
5083 """A constraint that proxies a ColumnCollection."""
5085 def __init__(
5086 self,
5087 *columns: _DDLColumnArgument,
5088 name: _ConstraintNameArgument = None,
5089 deferrable: Optional[bool] = None,
5090 initially: Optional[str] = None,
5091 info: Optional[_InfoType] = None,
5092 _autoattach: bool = True,
5093 _column_flag: bool = False,
5094 _gather_expressions: Optional[List[_DDLColumnArgument]] = None,
5095 **dialect_kw: Any,
5096 ) -> None:
5097 r"""
5098 :param \*columns:
5099 A sequence of column names or Column objects.
5101 :param name:
5102 Optional, the in-database name of this constraint.
5104 :param deferrable:
5105 Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
5106 issuing DDL for this constraint.
5108 :param initially:
5109 Optional string. If set, emit INITIALLY <value> when issuing DDL
5110 for this constraint.
5112 :param \**dialect_kw: other keyword arguments including
5113 dialect-specific arguments are propagated to the :class:`.Constraint`
5114 superclass.
5116 """
5117 Constraint.__init__(
5118 self,
5119 name=name,
5120 deferrable=deferrable,
5121 initially=initially,
5122 info=info,
5123 **dialect_kw,
5124 )
5125 ColumnCollectionMixin.__init__(
5126 self, *columns, _autoattach=_autoattach, _column_flag=_column_flag
5127 )
5129 columns: ReadOnlyColumnCollection[str, Column[Any]]
5130 """A :class:`_expression.ColumnCollection` representing the set of columns
5131 for this constraint.
5133 """
5135 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
5136 assert isinstance(parent, (Column, Table))
5137 Constraint._set_parent(self, parent)
5138 ColumnCollectionMixin._set_parent(self, parent)
5140 def __contains__(self, x: Any) -> bool:
5141 return x in self._columns
5143 @util.deprecated(
5144 "1.4",
5145 "The :meth:`_schema.ColumnCollectionConstraint.copy` method "
5146 "is deprecated and will be removed in a future release.",
5147 )
5148 def copy(
5149 self,
5150 *,
5151 target_table: Optional[Table] = None,
5152 **kw: Any,
5153 ) -> ColumnCollectionConstraint:
5154 return self._copy(target_table=target_table, **kw)
5156 def _copy(
5157 self,
5158 *,
5159 target_table: Optional[Table] = None,
5160 **kw: Any,
5161 ) -> ColumnCollectionConstraint:
5162 # ticket #5276
5163 constraint_kwargs = {}
5164 for dialect_name in self.dialect_options:
5165 dialect_options = self.dialect_options[dialect_name]._non_defaults
5166 for (
5167 dialect_option_key,
5168 dialect_option_value,
5169 ) in dialect_options.items():
5170 constraint_kwargs[dialect_name + "_" + dialect_option_key] = (
5171 dialect_option_value
5172 )
5174 assert isinstance(self.parent, Table)
5175 c = self.__class__(
5176 name=self.name,
5177 deferrable=self.deferrable,
5178 initially=self.initially,
5179 *[
5180 _copy_expression(expr, self.parent, target_table)
5181 for expr in self._columns
5182 ],
5183 comment=self.comment,
5184 **constraint_kwargs,
5185 )
5186 return self._schema_item_copy(c)
5188 def contains_column(self, col: Column[Any]) -> bool:
5189 """Return True if this constraint contains the given column.
5191 Note that this object also contains an attribute ``.columns``
5192 which is a :class:`_expression.ColumnCollection` of
5193 :class:`_schema.Column` objects.
5195 """
5197 return self._columns.contains_column(col)
5199 def __iter__(self) -> Iterator[Column[Any]]:
5200 return iter(self._columns)
5202 def __len__(self) -> int:
5203 return len(self._columns)
5206class CheckConstraint(ColumnCollectionConstraint):
5207 """A table- or column-level CHECK constraint.
5209 Can be included in the definition of a Table or Column.
5210 """
5212 _allow_multiple_tables = True
5214 __visit_name__ = "table_or_column_check_constraint"
5216 @_document_text_coercion(
5217 "sqltext",
5218 ":class:`.CheckConstraint`",
5219 ":paramref:`.CheckConstraint.sqltext`",
5220 )
5221 def __init__(
5222 self,
5223 sqltext: _TextCoercedExpressionArgument[Any],
5224 name: _ConstraintNameArgument = None,
5225 deferrable: Optional[bool] = None,
5226 initially: Optional[str] = None,
5227 table: Optional[Table] = None,
5228 info: Optional[_InfoType] = None,
5229 _create_rule: Optional[Any] = None,
5230 _autoattach: bool = True,
5231 _type_bound: bool = False,
5232 **dialect_kw: Any,
5233 ) -> None:
5234 r"""Construct a CHECK constraint.
5236 :param sqltext:
5237 A string containing the constraint definition, which will be used
5238 verbatim, or a SQL expression construct. If given as a string,
5239 the object is converted to a :func:`_expression.text` object.
5240 If the textual
5241 string includes a colon character, escape this using a backslash::
5243 CheckConstraint(r"foo ~ E'a(?\:b|c)d")
5245 :param name:
5246 Optional, the in-database name of the constraint.
5248 :param deferrable:
5249 Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
5250 issuing DDL for this constraint.
5252 :param initially:
5253 Optional string. If set, emit INITIALLY <value> when issuing DDL
5254 for this constraint.
5256 :param info: Optional data dictionary which will be populated into the
5257 :attr:`.SchemaItem.info` attribute of this object.
5259 """
5261 self.sqltext = coercions.expect(roles.DDLExpressionRole, sqltext)
5262 columns: List[Column[Any]] = []
5263 visitors.traverse(self.sqltext, {}, {"column": columns.append})
5265 super().__init__(
5266 name=name,
5267 deferrable=deferrable,
5268 initially=initially,
5269 _create_rule=_create_rule,
5270 info=info,
5271 _type_bound=_type_bound,
5272 _autoattach=_autoattach,
5273 *columns,
5274 **dialect_kw,
5275 )
5276 if table is not None:
5277 self._set_parent_with_dispatch(table)
5279 @property
5280 def is_column_level(self) -> bool:
5281 return not isinstance(self.parent, Table)
5283 @util.deprecated(
5284 "1.4",
5285 "The :meth:`_schema.CheckConstraint.copy` method is deprecated "
5286 "and will be removed in a future release.",
5287 )
5288 def copy(
5289 self, *, target_table: Optional[Table] = None, **kw: Any
5290 ) -> CheckConstraint:
5291 return self._copy(target_table=target_table, **kw)
5293 def _copy(
5294 self, *, target_table: Optional[Table] = None, **kw: Any
5295 ) -> CheckConstraint:
5296 if target_table is not None:
5297 # note that target_table is None for the copy process of
5298 # a column-bound CheckConstraint, so this path is not reached
5299 # in that case.
5300 sqltext = _copy_expression(self.sqltext, self.table, target_table)
5301 else:
5302 sqltext = self.sqltext
5303 c = CheckConstraint(
5304 sqltext,
5305 name=self.name,
5306 initially=self.initially,
5307 deferrable=self.deferrable,
5308 _create_rule=self._create_rule,
5309 table=target_table,
5310 comment=self.comment,
5311 _autoattach=False,
5312 _type_bound=self._type_bound,
5313 )
5314 return self._schema_item_copy(c)
5317class ForeignKeyConstraint(ColumnCollectionConstraint):
5318 """A table-level FOREIGN KEY constraint.
5320 Defines a single column or composite FOREIGN KEY ... REFERENCES
5321 constraint. For a no-frills, single column foreign key, adding a
5322 :class:`_schema.ForeignKey` to the definition of a :class:`_schema.Column`
5323 is a
5324 shorthand equivalent for an unnamed, single column
5325 :class:`_schema.ForeignKeyConstraint`.
5327 Examples of foreign key configuration are in :ref:`metadata_foreignkeys`.
5329 """
5331 __visit_name__ = "foreign_key_constraint"
5333 # a FOREIGN KEY may name the same local column more than once, e.g.
5334 # FOREIGN KEY (a, a) REFERENCES r (b, c). the collection is therefore
5335 # positional and parallel to self.elements, not deduplicating.
5336 _column_collection_class = WriteableColumnCollection
5338 def __init__(
5339 self,
5340 columns: _typing_Sequence[_DDLColumnArgument],
5341 refcolumns: _typing_Sequence[_DDLColumnReferenceArgument],
5342 name: _ConstraintNameArgument = None,
5343 onupdate: Optional[str] = None,
5344 ondelete: Optional[str] = None,
5345 deferrable: Optional[bool] = None,
5346 initially: Optional[str] = None,
5347 use_alter: bool = False,
5348 link_to_name: bool = False,
5349 match: Optional[str] = None,
5350 table: Optional[Table] = None,
5351 info: Optional[_InfoType] = None,
5352 comment: Optional[str] = None,
5353 **dialect_kw: Any,
5354 ) -> None:
5355 r"""Construct a composite-capable FOREIGN KEY.
5357 :param columns: A sequence of local column names. The named columns
5358 must be defined and present in the parent Table. The names should
5359 match the ``key`` given to each column (defaults to the name) unless
5360 ``link_to_name`` is True. The same column may be named more than
5361 once, e.g. ``FOREIGN KEY (a, a) REFERENCES r (b, c)``, which
5362 constrains the referenced row so that its ``b`` and ``c`` values
5363 are equal.
5365 .. versionchanged:: 2.1 The same local column may be named more
5366 than once.
5368 :param refcolumns: A sequence of foreign column names or Column
5369 objects. The columns must all be located within the same Table.
5370 The number of entries must match that of
5371 :paramref:`_schema.ForeignKeyConstraint.columns`. Each entry
5372 accepts the same forms as :paramref:`_schema.ForeignKey.column`,
5373 including the ``(table_name, column_name)`` and
5374 ``(schema, table_name, column_name)`` tuple forms.
5376 .. versionchanged:: 2.1 Individual entries may be given as a
5377 tuple of name tokens.
5379 :param name: Optional, the in-database name of the key.
5381 :param onupdate: Optional string. If set, emit ON UPDATE <value> when
5382 issuing DDL for this constraint. Typical values include CASCADE,
5383 DELETE and RESTRICT.
5385 .. seealso::
5387 :ref:`on_update_on_delete`
5389 :param ondelete: Optional string. If set, emit ON DELETE <value> when
5390 issuing DDL for this constraint. Typical values include CASCADE,
5391 SET NULL and RESTRICT. Some dialects may allow for additional
5392 syntaxes.
5394 .. seealso::
5396 :ref:`on_update_on_delete`
5398 :param deferrable: Optional bool. If set, emit DEFERRABLE or NOT
5399 DEFERRABLE when issuing DDL for this constraint.
5401 :param initially: Optional string. If set, emit INITIALLY <value> when
5402 issuing DDL for this constraint.
5404 :param link_to_name: if True, the string name given in ``column`` is
5405 the rendered name of the referenced column, not its locally assigned
5406 ``key``.
5408 :param use_alter: If True, do not emit the DDL for this constraint as
5409 part of the CREATE TABLE definition. Instead, generate it via an
5410 ALTER TABLE statement issued after the full collection of tables
5411 have been created, and drop it via an ALTER TABLE statement before
5412 the full collection of tables are dropped.
5414 The use of :paramref:`_schema.ForeignKeyConstraint.use_alter` is
5415 particularly geared towards the case where two or more tables
5416 are established within a mutually-dependent foreign key constraint
5417 relationship; however, the :meth:`_schema.MetaData.create_all` and
5418 :meth:`_schema.MetaData.drop_all`
5419 methods will perform this resolution
5420 automatically, so the flag is normally not needed.
5422 .. seealso::
5424 :ref:`use_alter`
5426 :param match: Optional string. If set, emit MATCH <value> when issuing
5427 DDL for this constraint. Typical values include SIMPLE, PARTIAL
5428 and FULL.
5430 :param info: Optional data dictionary which will be populated into the
5431 :attr:`.SchemaItem.info` attribute of this object.
5433 :param comment: Optional string that will render an SQL comment on
5434 foreign key constraint creation.
5436 .. versionadded:: 2.0
5438 :param \**dialect_kw: Additional keyword arguments are dialect
5439 specific, and passed in the form ``<dialectname>_<argname>``. See
5440 the documentation regarding an individual dialect at
5441 :ref:`dialect_toplevel` for detail on documented arguments.
5443 """
5445 Constraint.__init__(
5446 self,
5447 name=name,
5448 deferrable=deferrable,
5449 initially=initially,
5450 info=info,
5451 comment=comment,
5452 **dialect_kw,
5453 )
5454 self.onupdate = onupdate
5455 self.ondelete = ondelete
5456 self.link_to_name = link_to_name
5457 self.use_alter = use_alter
5458 self.match = match
5460 if len(columns) != len(refcolumns):
5461 # e.g. FOREIGN KEY (a) REFERENCES r (b, c)
5462 # paraphrasing
5463 # https://www.postgresql.org/docs/current/static/ddl-constraints.html
5464 raise exc.ArgumentError(
5465 "ForeignKeyConstraint number "
5466 "of constrained columns must match the number of "
5467 "referenced columns."
5468 )
5470 # standalone ForeignKeyConstraint - create
5471 # associated ForeignKey objects which will be applied to hosted
5472 # Column objects (in col.foreign_keys), either now or when attached
5473 # to the Table for string-specified names
5474 self.elements = [
5475 ForeignKey(
5476 refcol,
5477 _constraint=self,
5478 name=self.name,
5479 onupdate=self.onupdate,
5480 ondelete=self.ondelete,
5481 use_alter=self.use_alter,
5482 link_to_name=self.link_to_name,
5483 match=self.match,
5484 deferrable=self.deferrable,
5485 initially=self.initially,
5486 **self.dialect_kwargs,
5487 )
5488 for refcol in refcolumns
5489 ]
5491 ColumnCollectionMixin.__init__(self, *columns)
5492 if table is not None:
5493 if hasattr(self, "parent"):
5494 assert table is self.parent
5495 self._set_parent_with_dispatch(table)
5497 def _append_element(self, column: Column[Any], fk: ForeignKey) -> None:
5498 self._columns.add(column)
5499 self.elements.append(fk)
5501 columns: ReadOnlyColumnCollection[str, Column[Any]]
5502 """A :class:`_expression.ColumnCollection` representing the local columns
5503 of this constraint, in the order given and parallel to
5504 :attr:`.ForeignKeyConstraint.elements`.
5506 Unlike the column collection of other constraint types, this collection
5507 does not deduplicate; a constraint which names the same column more than
5508 once, such as ``FOREIGN KEY (a, a) REFERENCES r (b, c)``, includes that
5509 column once per position.
5511 .. versionchanged:: 2.1 A :class:`_schema.ForeignKeyConstraint` may name
5512 the same local column more than once, and this collection retains the
5513 repeated entries.
5515 """
5517 elements: List[ForeignKey]
5518 """A sequence of :class:`_schema.ForeignKey` objects.
5520 Each :class:`_schema.ForeignKey`
5521 represents a single referring column/referred
5522 column pair.
5524 This collection is intended to be read-only.
5526 """
5528 @property
5529 def _referred_schema(self) -> Optional[str]:
5530 for elem in self.elements:
5531 return elem._referred_schema
5532 else:
5533 return None
5535 @property
5536 def referred_table(self) -> Table:
5537 """The :class:`_schema.Table` object to which this
5538 :class:`_schema.ForeignKeyConstraint` references.
5540 This is a dynamically calculated attribute which may not be available
5541 if the constraint and/or parent table is not yet associated with
5542 a metadata collection that contains the referred table.
5544 """
5545 return self.elements[0].column.table
5547 def _validate_dest_table(self, table: Table) -> None:
5548 table_keys = {elem.target_table_key for elem in self.elements}
5549 if None not in table_keys and len(table_keys) > 1:
5550 elem0, elem1 = sorted(cast("Set[str]", table_keys))[0:2]
5551 raise exc.ArgumentError(
5552 f"ForeignKeyConstraint on "
5553 f"{table.fullname}({self._col_description}) refers to "
5554 f"multiple remote tables: {elem0} and {elem1}"
5555 )
5557 @property
5558 def column_keys(self) -> _typing_Sequence[str]:
5559 """Return a list of string keys representing the local
5560 columns in this :class:`_schema.ForeignKeyConstraint`.
5562 This list is either the original string arguments sent
5563 to the constructor of the :class:`_schema.ForeignKeyConstraint`,
5564 or if the constraint has been initialized with :class:`_schema.Column`
5565 objects, is the string ``.key`` of each element.
5567 """
5568 if hasattr(self, "parent"):
5569 return self._columns.keys()
5570 else:
5571 return [
5572 col.key if isinstance(col, ColumnElement) else str(col)
5573 for col in self._pending_colargs
5574 ]
5576 @property
5577 def _col_description(self) -> str:
5578 return ", ".join(self.column_keys)
5580 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
5581 table = parent
5582 assert isinstance(table, Table)
5583 Constraint._set_parent(self, table)
5585 if self._pending_colargs:
5586 # this collection is positional and parallel to self.elements,
5587 # retaining duplicate entries for a constraint such as
5588 # FOREIGN KEY (a, a) REFERENCES r (b, c). _set_parent may run
5589 # more than once for the same table, e.g. for an inline-declared
5590 # constraint that also auto-attaches when its Column objects
5591 # are attached, so populate rather than accumulate.
5592 self._columns._populate_separate_keys(
5593 (col.key, col)
5594 for col in self._col_expressions(table)
5595 if col is not None
5596 )
5598 for col, fk in zip(self._columns, self.elements):
5599 if not hasattr(fk, "parent") or fk.parent is not col:
5600 fk._set_parent_with_dispatch(col)
5602 self._validate_dest_table(table)
5604 @util.deprecated(
5605 "1.4",
5606 "The :meth:`_schema.ForeignKeyConstraint.copy` method is deprecated "
5607 "and will be removed in a future release.",
5608 )
5609 def copy(
5610 self,
5611 *,
5612 schema: Optional[str] = None,
5613 target_table: Optional[Table] = None,
5614 **kw: Any,
5615 ) -> ForeignKeyConstraint:
5616 return self._copy(schema=schema, target_table=target_table, **kw)
5618 def _copy(
5619 self,
5620 *,
5621 schema: Optional[str] = None,
5622 target_table: Optional[Table] = None,
5623 **kw: Any,
5624 ) -> ForeignKeyConstraint:
5625 fkc = ForeignKeyConstraint(
5626 [x.parent.key for x in self.elements],
5627 [
5628 x._copy_tokens(
5629 schema=schema,
5630 table_name=(
5631 target_table.name
5632 if target_table is not None
5633 and x.target_table_key == x.parent.table.key
5634 else None
5635 ),
5636 _is_copy=True,
5637 )
5638 for x in self.elements
5639 ],
5640 name=self.name,
5641 onupdate=self.onupdate,
5642 ondelete=self.ondelete,
5643 use_alter=self.use_alter,
5644 deferrable=self.deferrable,
5645 initially=self.initially,
5646 link_to_name=self.link_to_name,
5647 match=self.match,
5648 comment=self.comment,
5649 )
5650 for self_fk, other_fk in zip(self.elements, fkc.elements):
5651 self_fk._schema_item_copy(other_fk)
5652 return self._schema_item_copy(fkc)
5655class PrimaryKeyConstraint(ColumnCollectionConstraint):
5656 """A table-level PRIMARY KEY constraint.
5658 The :class:`.PrimaryKeyConstraint` object is present automatically
5659 on any :class:`_schema.Table` object; it is assigned a set of
5660 :class:`_schema.Column` objects corresponding to those marked with
5661 the :paramref:`_schema.Column.primary_key` flag::
5663 >>> my_table = Table(
5664 ... "mytable",
5665 ... metadata,
5666 ... Column("id", Integer, primary_key=True),
5667 ... Column("version_id", Integer, primary_key=True),
5668 ... Column("data", String(50)),
5669 ... )
5670 >>> my_table.primary_key
5671 PrimaryKeyConstraint(
5672 Column('id', Integer(), table=<mytable>,
5673 primary_key=True, nullable=False),
5674 Column('version_id', Integer(), table=<mytable>,
5675 primary_key=True, nullable=False)
5676 )
5678 The primary key of a :class:`_schema.Table` can also be specified by using
5679 a :class:`.PrimaryKeyConstraint` object explicitly; in this mode of usage,
5680 the "name" of the constraint can also be specified, as well as other
5681 options which may be recognized by dialects::
5683 my_table = Table(
5684 "mytable",
5685 metadata,
5686 Column("id", Integer),
5687 Column("version_id", Integer),
5688 Column("data", String(50)),
5689 PrimaryKeyConstraint("id", "version_id", name="mytable_pk"),
5690 )
5692 The two styles of column-specification should generally not be mixed.
5693 An warning is emitted if the columns present in the
5694 :class:`.PrimaryKeyConstraint`
5695 don't match the columns that were marked as ``primary_key=True``, if both
5696 are present; in this case, the columns are taken strictly from the
5697 :class:`.PrimaryKeyConstraint` declaration, and those columns otherwise
5698 marked as ``primary_key=True`` are ignored. This behavior is intended to
5699 be backwards compatible with previous behavior.
5701 For the use case where specific options are to be specified on the
5702 :class:`.PrimaryKeyConstraint`, but the usual style of using
5703 ``primary_key=True`` flags is still desirable, an empty
5704 :class:`.PrimaryKeyConstraint` may be specified, which will take on the
5705 primary key column collection from the :class:`_schema.Table` based on the
5706 flags::
5708 my_table = Table(
5709 "mytable",
5710 metadata,
5711 Column("id", Integer, primary_key=True),
5712 Column("version_id", Integer, primary_key=True),
5713 Column("data", String(50)),
5714 PrimaryKeyConstraint(name="mytable_pk", mssql_clustered=True),
5715 )
5717 """
5719 __visit_name__ = "primary_key_constraint"
5721 _columns: DedupeColumnCollection[Column[Any]]
5723 def __init__(
5724 self,
5725 *columns: _DDLColumnArgument,
5726 name: Optional[str] = None,
5727 deferrable: Optional[bool] = None,
5728 initially: Optional[str] = None,
5729 info: Optional[_InfoType] = None,
5730 _implicit_generated: bool = False,
5731 **dialect_kw: Any,
5732 ) -> None:
5733 self._implicit_generated = _implicit_generated
5734 super().__init__(
5735 *columns,
5736 name=name,
5737 deferrable=deferrable,
5738 initially=initially,
5739 info=info,
5740 **dialect_kw,
5741 )
5743 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
5744 table = parent
5745 assert isinstance(table, Table)
5746 super()._set_parent(table)
5748 if table.primary_key is not self:
5749 table.constraints.discard(table.primary_key)
5750 table.primary_key = self # type: ignore[misc]
5751 table.constraints.add(self)
5753 table_pks = [c for c in table.c if c.primary_key]
5754 if (
5755 self._columns
5756 and table_pks
5757 and set(table_pks) != set(self._columns)
5758 ):
5759 # black could not format these inline
5760 table_pk_str = ", ".join("'%s'" % c.name for c in table_pks)
5761 col_str = ", ".join("'%s'" % c.name for c in self._columns)
5763 util.warn(
5764 f"Table '{table.name}' specifies columns "
5765 f"{table_pk_str} as "
5766 f"primary_key=True, "
5767 f"not matching locally specified columns {col_str}; "
5768 f"setting the "
5769 f"current primary key columns to "
5770 f"{col_str}. "
5771 f"This warning "
5772 f"may become an exception in a future release"
5773 )
5774 table_pks[:] = []
5776 for c in self._columns:
5777 c.primary_key = True
5778 if c._user_defined_nullable is NULL_UNSPECIFIED:
5779 c.nullable = False
5780 if table_pks:
5781 self._columns.extend(table_pks)
5783 def _reload(self, columns: Iterable[Column[Any]]) -> None:
5784 """repopulate this :class:`.PrimaryKeyConstraint` given
5785 a set of columns.
5787 Existing columns in the table that are marked as primary_key=True
5788 are maintained.
5790 Also fires a new event.
5792 This is basically like putting a whole new
5793 :class:`.PrimaryKeyConstraint` object on the parent
5794 :class:`_schema.Table` object without actually replacing the object.
5796 The ordering of the given list of columns is also maintained; these
5797 columns will be appended to the list of columns after any which
5798 are already present.
5800 """
5801 # set the primary key flag on new columns.
5802 # note any existing PK cols on the table also have their
5803 # flag still set.
5804 for col in columns:
5805 col.primary_key = True
5807 self._columns.extend(columns)
5809 PrimaryKeyConstraint._autoincrement_column._reset(self) # type: ignore[attr-defined] # noqa: E501
5810 self._set_parent_with_dispatch(self.table)
5812 def _replace(self, col: Column[Any]) -> None:
5813 PrimaryKeyConstraint._autoincrement_column._reset(self) # type: ignore[attr-defined] # noqa: E501
5814 self._columns.replace(col)
5816 self.dispatch._sa_event_column_added_to_pk_constraint(self, col)
5818 @property
5819 def columns_autoinc_first(self) -> List[Column[Any]]:
5820 autoinc = self._autoincrement_column
5822 if autoinc is not None:
5823 return [autoinc] + [c for c in self._columns if c is not autoinc]
5824 else:
5825 return list(self._columns)
5827 @util.ro_memoized_property
5828 def _autoincrement_column(self) -> Optional[Column[int]]:
5829 def _validate_autoinc(col: Column[Any], autoinc_true: bool) -> bool:
5830 if col.type._type_affinity is not None and issubclass(
5831 col.type._type_affinity, type_api.NUMERICTYPE._type_affinity
5832 ):
5833 scale = col.type.scale # type: ignore[attr-defined]
5834 if scale != 0 and autoinc_true:
5835 raise exc.ArgumentError(
5836 f"Column type {col.type} with non-zero scale "
5837 f"{scale} on column '{col}' is not "
5838 f"compatible with autoincrement=True"
5839 )
5840 elif not autoinc_true:
5841 return False
5842 elif col.type._type_affinity is None or not issubclass(
5843 col.type._type_affinity, type_api.INTEGERTYPE._type_affinity
5844 ):
5845 if autoinc_true:
5846 raise exc.ArgumentError(
5847 f"Column type {col.type} on column '{col}' is not "
5848 f"compatible with autoincrement=True"
5849 )
5850 else:
5851 return False
5852 elif (
5853 col.default is not None
5854 and not isinstance(col.default, Sequence)
5855 and not autoinc_true
5856 ):
5857 return False
5858 elif (
5859 col.server_default is not None
5860 and not isinstance(col.server_default, Identity)
5861 and not autoinc_true
5862 ):
5863 return False
5864 elif col.foreign_keys and col.autoincrement not in (
5865 True,
5866 "ignore_fk",
5867 ):
5868 return False
5869 return True
5871 if len(self._columns) == 1:
5872 col = list(self._columns)[0]
5874 if col.autoincrement is True:
5875 _validate_autoinc(col, True)
5876 return col
5877 elif col.autoincrement in (
5878 "auto",
5879 "ignore_fk",
5880 ) and _validate_autoinc(col, False):
5881 return col
5882 else:
5883 return None
5885 else:
5886 autoinc = None
5887 for col in self._columns:
5888 if col.autoincrement is True:
5889 _validate_autoinc(col, True)
5890 if autoinc is not None:
5891 raise exc.ArgumentError(
5892 f"Only one Column may be marked "
5893 f"autoincrement=True, found both "
5894 f"{col.name} and {autoinc.name}."
5895 )
5896 else:
5897 autoinc = col
5899 return autoinc
5902class UniqueConstraint(ColumnCollectionConstraint):
5903 """A table-level UNIQUE constraint.
5905 Defines a single column or composite UNIQUE constraint. For a no-frills,
5906 single column constraint, adding ``unique=True`` to the ``Column``
5907 definition is a shorthand equivalent for an unnamed, single column
5908 UniqueConstraint.
5909 """
5911 __visit_name__ = "unique_constraint"
5914class Index(
5915 DialectKWArgs, ColumnCollectionMixin, HasConditionalDDL, SchemaItem
5916):
5917 """A table-level INDEX.
5919 Defines a composite (one or more column) INDEX.
5921 E.g.::
5923 sometable = Table(
5924 "sometable",
5925 metadata,
5926 Column("name", String(50)),
5927 Column("address", String(100)),
5928 )
5930 Index("some_index", sometable.c.name)
5932 For a no-frills, single column index, adding
5933 :class:`_schema.Column` also supports ``index=True``::
5935 sometable = Table(
5936 "sometable", metadata, Column("name", String(50), index=True)
5937 )
5939 For a composite index, multiple columns can be specified::
5941 Index("some_index", sometable.c.name, sometable.c.address)
5943 Functional indexes are supported as well, typically by using the
5944 :data:`.func` construct in conjunction with table-bound
5945 :class:`_schema.Column` objects::
5947 Index("some_index", func.lower(sometable.c.name))
5949 An :class:`.Index` can also be manually associated with a
5950 :class:`_schema.Table`,
5951 either through inline declaration or using
5952 :meth:`_schema.Table.append_constraint`. When this approach is used,
5953 the names
5954 of the indexed columns can be specified as strings::
5956 Table(
5957 "sometable",
5958 metadata,
5959 Column("name", String(50)),
5960 Column("address", String(100)),
5961 Index("some_index", "name", "address"),
5962 )
5964 To support functional or expression-based indexes in this form, the
5965 :func:`_expression.text` construct may be used::
5967 from sqlalchemy import text
5969 Table(
5970 "sometable",
5971 metadata,
5972 Column("name", String(50)),
5973 Column("address", String(100)),
5974 Index("some_index", text("lower(name)")),
5975 )
5977 .. seealso::
5979 :ref:`schema_indexes` - General information on :class:`.Index`.
5981 :ref:`postgresql_indexes` - PostgreSQL-specific options available for
5982 the :class:`.Index` construct.
5984 :ref:`mysql_indexes` - MySQL-specific options available for the
5985 :class:`.Index` construct.
5987 :ref:`mssql_indexes` - MSSQL-specific options available for the
5988 :class:`.Index` construct.
5990 """
5992 __visit_name__ = "index"
5994 table: Optional[Table]
5995 expressions: _typing_Sequence[Union[str, ColumnElement[Any]]]
5996 _table_bound_expressions: _typing_Sequence[ColumnElement[Any]]
5998 def __init__(
5999 self,
6000 name: Optional[str],
6001 *expressions: _DDLColumnArgument,
6002 unique: bool = False,
6003 quote: Optional[bool] = None,
6004 info: Optional[_InfoType] = None,
6005 _table: Optional[Table] = None,
6006 _column_flag: bool = False,
6007 **dialect_kw: Any,
6008 ) -> None:
6009 r"""Construct an index object.
6011 :param name:
6012 The name of the index
6014 :param \*expressions:
6015 Column expressions to include in the index. The expressions
6016 are normally instances of :class:`_schema.Column`, but may also
6017 be arbitrary SQL expressions which ultimately refer to a
6018 :class:`_schema.Column`.
6020 :param unique=False:
6021 Keyword only argument; if True, create a unique index.
6023 :param quote=None:
6024 Keyword only argument; whether to apply quoting to the name of
6025 the index. Works in the same manner as that of
6026 :paramref:`_schema.Column.quote`.
6028 :param info=None: Optional data dictionary which will be populated
6029 into the :attr:`.SchemaItem.info` attribute of this object.
6031 :param \**dialect_kw: Additional keyword arguments not mentioned above
6032 are dialect specific, and passed in the form
6033 ``<dialectname>_<argname>``. See the documentation regarding an
6034 individual dialect at :ref:`dialect_toplevel` for detail on
6035 documented arguments.
6037 """
6038 self.table = table = None
6040 self.name = quoted_name.construct(name, quote)
6041 self.unique = unique
6042 if info is not None:
6043 self.info = info
6045 # TODO: consider "table" argument being public, but for
6046 # the purpose of the fix here, it starts as private.
6047 if _table is not None:
6048 table = _table
6050 self._validate_dialect_kwargs(dialect_kw)
6052 self.expressions = []
6053 # will call _set_parent() if table-bound column
6054 # objects are present
6055 ColumnCollectionMixin.__init__(
6056 self,
6057 *expressions,
6058 _column_flag=_column_flag,
6059 _gather_expressions=self.expressions,
6060 )
6061 if table is not None:
6062 self._set_parent(table)
6064 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
6065 table = parent
6066 assert isinstance(table, Table)
6067 ColumnCollectionMixin._set_parent(self, table)
6069 if self.table is not None and table is not self.table:
6070 raise exc.ArgumentError(
6071 f"Index '{self.name}' is against table "
6072 f"'{self.table.description}', and "
6073 f"cannot be associated with table '{table.description}'."
6074 )
6075 self.table = table
6076 table.indexes.add(self)
6078 expressions = self.expressions
6079 col_expressions = self._col_expressions(table)
6080 assert len(expressions) == len(col_expressions)
6082 exprs = []
6083 for expr, colexpr in zip(expressions, col_expressions):
6084 if isinstance(expr, ClauseElement):
6085 exprs.append(expr)
6086 elif colexpr is not None:
6087 exprs.append(colexpr)
6088 else:
6089 assert False
6090 self.expressions = self._table_bound_expressions = exprs
6092 def create(
6093 self,
6094 bind: _CreateDropBind,
6095 checkfirst: Union[bool, CheckFirst] = CheckFirst.NONE,
6096 ) -> None:
6097 """Issue a ``CREATE`` statement for this
6098 :class:`.Index`, using the given
6099 :class:`.Connection` or :class:`.Engine`` for connectivity.
6101 .. seealso::
6103 :meth:`_schema.MetaData.create_all`.
6105 """
6106 bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
6108 def drop(
6109 self,
6110 bind: _CreateDropBind,
6111 checkfirst: Union[bool, CheckFirst] = CheckFirst.NONE,
6112 ) -> None:
6113 """Issue a ``DROP`` statement for this
6114 :class:`.Index`, using the given
6115 :class:`.Connection` or :class:`.Engine` for connectivity.
6117 .. seealso::
6119 :meth:`_schema.MetaData.drop_all`.
6121 """
6122 bind._run_ddl_visitor(ddl.SchemaDropper, self, checkfirst=checkfirst)
6124 def __repr__(self) -> str:
6125 exprs: _typing_Sequence[Any] # noqa: F842
6127 return "Index(%s)" % (
6128 ", ".join(
6129 [repr(self.name)]
6130 + [repr(e) for e in self.expressions]
6131 + (self.unique and ["unique=True"] or [])
6132 )
6133 )
6136_NamingSchemaCallable = Union[
6137 Callable[[Constraint, Table], str],
6138 Callable[[Index, Table], str],
6139]
6140_NamingSchemaDirective = Union[str, _NamingSchemaCallable]
6143class _NamingSchemaTD(TypedDict, total=False):
6144 fk: _NamingSchemaDirective
6145 pk: _NamingSchemaDirective
6146 ix: _NamingSchemaDirective
6147 ck: _NamingSchemaDirective
6148 uq: _NamingSchemaDirective
6151_NamingSchemaParameter = Union[
6152 # it seems like the TypedDict here is useful for pylance typeahead,
6153 # and not much else
6154 _NamingSchemaTD,
6155 # there is no form that allows Union[Type[Any], str] to work in all
6156 # cases, including breaking out Mapping[] entries for each combination
6157 # even, therefore keys must be `Any` (see #10264)
6158 Mapping[Any, _NamingSchemaDirective],
6159]
6162DEFAULT_NAMING_CONVENTION: _NamingSchemaParameter = util.immutabledict(
6163 {"ix": "ix_%(column_0_label)s"}
6164)
6167class MetaData(HasSchemaAttr):
6168 """A collection of :class:`_schema.Table`
6169 objects and their associated schema
6170 constructs.
6172 Holds a collection of :class:`_schema.Table` objects as well as
6173 an optional binding to an :class:`_engine.Engine` or
6174 :class:`_engine.Connection`. If bound, the :class:`_schema.Table` objects
6175 in the collection and their columns may participate in implicit SQL
6176 execution.
6178 The :class:`_schema.Table` objects themselves are stored in the
6179 :attr:`_schema.MetaData.tables` dictionary.
6181 :class:`_schema.MetaData` is a thread-safe object for read operations.
6182 Construction of new tables within a single :class:`_schema.MetaData`
6183 object,
6184 either explicitly or via reflection, may not be completely thread-safe.
6186 .. seealso::
6188 :ref:`metadata_describing` - Introduction to database metadata
6190 """
6192 __visit_name__ = "metadata"
6194 def __init__(
6195 self,
6196 schema: Optional[str] = None,
6197 quote_schema: Optional[bool] = None,
6198 naming_convention: Optional[_NamingSchemaParameter] = None,
6199 info: Optional[_InfoType] = None,
6200 ) -> None:
6201 """Create a new MetaData object.
6203 :param schema:
6204 The default schema to use for the :class:`_schema.Table`,
6205 :class:`.Sequence`, and potentially other objects associated with
6206 this :class:`_schema.MetaData`. Defaults to ``None``.
6208 .. seealso::
6210 :ref:`schema_metadata_schema_name` - details on how the
6211 :paramref:`_schema.MetaData.schema` parameter is used.
6213 :paramref:`_schema.Table.schema`
6215 :paramref:`.Sequence.schema`
6217 :param quote_schema:
6218 Sets the ``quote_schema`` flag for those :class:`_schema.Table`,
6219 :class:`.Sequence`, and other objects which make usage of the
6220 local ``schema`` name.
6222 :param info: Optional data dictionary which will be populated into the
6223 :attr:`.SchemaItem.info` attribute of this object.
6225 :param naming_convention: a dictionary referring to values which
6226 will establish default naming conventions for :class:`.Constraint`
6227 and :class:`.Index` objects, for those objects which are not given
6228 a name explicitly.
6230 The keys of this dictionary may be:
6232 * a constraint or Index class, e.g. the :class:`.UniqueConstraint`,
6233 :class:`_schema.ForeignKeyConstraint` class, the :class:`.Index`
6234 class
6236 * a string mnemonic for one of the known constraint classes;
6237 ``"fk"``, ``"pk"``, ``"ix"``, ``"ck"``, ``"uq"`` for foreign key,
6238 primary key, index, check, and unique constraint, respectively.
6240 * the string name of a user-defined "token" that can be used
6241 to define new naming tokens.
6243 The values associated with each "constraint class" or "constraint
6244 mnemonic" key are string naming templates, such as
6245 ``"uq_%(table_name)s_%(column_0_name)s"``,
6246 which describe how the name should be composed. The values
6247 associated with user-defined "token" keys should be callables of the
6248 form ``fn(constraint, table)``, which accepts the constraint/index
6249 object and :class:`_schema.Table` as arguments, returning a string
6250 result.
6252 The built-in names are as follows, some of which may only be
6253 available for certain types of constraint:
6255 * ``%(table_name)s`` - the name of the :class:`_schema.Table`
6256 object
6257 associated with the constraint.
6259 * ``%(referred_table_name)s`` - the name of the
6260 :class:`_schema.Table`
6261 object associated with the referencing target of a
6262 :class:`_schema.ForeignKeyConstraint`.
6264 * ``%(column_0_name)s`` - the name of the :class:`_schema.Column`
6265 at
6266 index position "0" within the constraint.
6268 * ``%(column_0N_name)s`` - the name of all :class:`_schema.Column`
6269 objects in order within the constraint, joined without a
6270 separator.
6272 * ``%(column_0_N_name)s`` - the name of all
6273 :class:`_schema.Column`
6274 objects in order within the constraint, joined with an
6275 underscore as a separator.
6277 * ``%(column_0_label)s``, ``%(column_0N_label)s``,
6278 ``%(column_0_N_label)s`` - the label of either the zeroth
6279 :class:`_schema.Column` or all :class:`.Columns`, separated with
6280 or without an underscore
6282 * ``%(column_0_key)s``, ``%(column_0N_key)s``,
6283 ``%(column_0_N_key)s`` - the key of either the zeroth
6284 :class:`_schema.Column` or all :class:`.Columns`, separated with
6285 or without an underscore
6287 * ``%(referred_column_0_name)s``, ``%(referred_column_0N_name)s``
6288 ``%(referred_column_0_N_name)s``, ``%(referred_column_0_key)s``,
6289 ``%(referred_column_0N_key)s``, ... column tokens which
6290 render the names/keys/labels of columns that are referenced
6291 by a :class:`_schema.ForeignKeyConstraint`.
6293 * ``%(constraint_name)s`` - a special key that refers to the
6294 existing name given to the constraint. When this key is
6295 present, the :class:`.Constraint` object's existing name will be
6296 replaced with one that is composed from template string that
6297 uses this token. When this token is present, it is required that
6298 the :class:`.Constraint` is given an explicit name ahead of time.
6300 * user-defined: any additional token may be implemented by passing
6301 it along with a ``fn(constraint, table)`` callable to the
6302 naming_convention dictionary.
6304 .. seealso::
6306 :ref:`constraint_naming_conventions` - for detailed usage
6307 examples.
6309 """
6310 if schema is not None and not isinstance(schema, str):
6311 raise exc.ArgumentError(
6312 "expected schema argument to be a string, "
6313 f"got {type(schema)}."
6314 )
6315 self.tables = util.FacadeDict()
6316 self.schema = quoted_name.construct(schema, quote_schema)
6317 self.naming_convention = (
6318 naming_convention
6319 if naming_convention
6320 else DEFAULT_NAMING_CONVENTION
6321 )
6322 if info:
6323 self.info = info
6324 self._schemas: Set[str] = set()
6325 self._sequences: Dict[str, Sequence] = {}
6326 self._fk_memos: Dict[Tuple[str, Optional[str]], List[ForeignKey]] = (
6327 collections.defaultdict(list)
6328 )
6329 self._objects: Set[Union[HasSchemaAttr, SchemaType]] = set()
6331 tables: util.FacadeDict[str, Table]
6332 """A dictionary of :class:`_schema.Table`
6333 objects keyed to their name or "table key".
6335 The exact key is that determined by the :attr:`_schema.Table.key`
6336 attribute;
6337 for a table with no :attr:`_schema.Table.schema` attribute,
6338 this is the same
6339 as :attr:`_schema.Table.name`. For a table with a schema,
6340 it is typically of the
6341 form ``schemaname.tablename``.
6343 .. seealso::
6345 :attr:`_schema.MetaData.sorted_tables`
6347 """
6349 def __repr__(self) -> str:
6350 return "MetaData()"
6352 def __contains__(self, table_or_key: Union[str, Table]) -> bool:
6353 if not isinstance(table_or_key, str):
6354 table_or_key = table_or_key.key
6355 return table_or_key in self.tables
6357 def _add_table(
6358 self, name: str, schema: Optional[str], table: Table
6359 ) -> None:
6360 key = _get_table_key(name, schema)
6361 self.tables._insert_item(key, table)
6362 if schema:
6363 self._schemas.add(schema)
6365 def _remove_table(self, name: str, schema: Optional[str]) -> None:
6366 key = _get_table_key(name, schema)
6367 removed = dict.pop(self.tables, key, None)
6368 if removed is not None:
6369 for fk in removed.foreign_keys:
6370 fk._remove_from_metadata(self)
6371 if self._schemas:
6372 self._schemas = {
6373 t.schema for t in self.tables.values() if t.schema is not None
6374 }
6376 def __getstate__(self) -> Dict[str, Any]:
6377 return {
6378 "tables": self.tables,
6379 "schema": self.schema,
6380 "schemas": self._schemas,
6381 "sequences": self._sequences,
6382 "fk_memos": self._fk_memos,
6383 "naming_convention": self.naming_convention,
6384 "objects": self._objects,
6385 }
6387 def __setstate__(self, state: Dict[str, Any]) -> None:
6388 self.tables = state["tables"]
6389 self.schema = state["schema"]
6390 self.naming_convention = state["naming_convention"]
6391 self._sequences = state["sequences"]
6392 self._schemas = state["schemas"]
6393 self._fk_memos = state["fk_memos"]
6394 self._objects = state.get("objects", set())
6396 def clear(self) -> None:
6397 """Clear all objects from this MetaData."""
6399 dict.clear(self.tables)
6400 self._schemas.clear()
6401 self._fk_memos.clear()
6402 self._sequences.clear()
6403 self._objects.clear()
6405 def remove(self, table: Table) -> None:
6406 """Remove the given Table object from this MetaData."""
6408 self._remove_table(table.name, table.schema)
6410 @property
6411 def sorted_tables(self) -> List[Table]:
6412 """Returns a list of :class:`_schema.Table` objects sorted in order of
6413 foreign key dependency.
6415 The sorting will place :class:`_schema.Table`
6416 objects that have dependencies
6417 first, before the dependencies themselves, representing the
6418 order in which they can be created. To get the order in which
6419 the tables would be dropped, use the ``reversed()`` Python built-in.
6421 .. warning::
6423 The :attr:`.MetaData.sorted_tables` attribute cannot by itself
6424 accommodate automatic resolution of dependency cycles between
6425 tables, which are usually caused by mutually dependent foreign key
6426 constraints. When these cycles are detected, the foreign keys
6427 of these tables are omitted from consideration in the sort.
6428 A warning is emitted when this condition occurs, which will be an
6429 exception raise in a future release. Tables which are not part
6430 of the cycle will still be returned in dependency order.
6432 To resolve these cycles, the
6433 :paramref:`_schema.ForeignKeyConstraint.use_alter` parameter may be
6434 applied to those constraints which create a cycle. Alternatively,
6435 the :func:`_schema.sort_tables_and_constraints` function will
6436 automatically return foreign key constraints in a separate
6437 collection when cycles are detected so that they may be applied
6438 to a schema separately.
6440 .. seealso::
6442 :func:`_schema.sort_tables`
6444 :func:`_schema.sort_tables_and_constraints`
6446 :attr:`_schema.MetaData.tables`
6448 :meth:`_reflection.Inspector.get_table_names`
6450 :meth:`_reflection.Inspector.get_sorted_table_and_fkc_names`
6453 """
6454 return ddl.sort_tables(
6455 sorted(self.tables.values(), key=lambda t: t.key) # type: ignore[attr-defined] # noqa: E501
6456 )
6458 # overload needed to work around mypy this mypy
6459 # https://github.com/python/mypy/issues/17093
6460 @overload
6461 def reflect(
6462 self,
6463 bind: Engine,
6464 schema: Optional[str] = ...,
6465 views: bool = ...,
6466 only: Union[
6467 _typing_Sequence[str], Callable[[str, MetaData], bool], None
6468 ] = ...,
6469 extend_existing: bool = ...,
6470 autoload_replace: bool = ...,
6471 resolve_fks: bool = ...,
6472 **dialect_kwargs: Any,
6473 ) -> None: ...
6475 @overload
6476 def reflect(
6477 self,
6478 bind: Connection,
6479 schema: Optional[str] = ...,
6480 views: bool = ...,
6481 only: Union[
6482 _typing_Sequence[str], Callable[[str, MetaData], bool], None
6483 ] = ...,
6484 extend_existing: bool = ...,
6485 autoload_replace: bool = ...,
6486 resolve_fks: bool = ...,
6487 **dialect_kwargs: Any,
6488 ) -> None: ...
6490 @util.preload_module("sqlalchemy.engine.reflection")
6491 def reflect(
6492 self,
6493 bind: Union[Engine, Connection],
6494 schema: Optional[str] = None,
6495 views: bool = False,
6496 only: Union[
6497 _typing_Sequence[str], Callable[[str, MetaData], bool], None
6498 ] = None,
6499 extend_existing: bool = False,
6500 autoload_replace: bool = True,
6501 resolve_fks: bool = True,
6502 **dialect_kwargs: Any,
6503 ) -> None:
6504 r"""Load all available table definitions from the database.
6506 Automatically creates ``Table`` entries in this ``MetaData`` for any
6507 table available in the database but not yet present in the
6508 ``MetaData``. May be called multiple times to pick up tables recently
6509 added to the database, however no special action is taken if a table
6510 in this ``MetaData`` no longer exists in the database.
6512 :param bind:
6513 A :class:`.Connection` or :class:`.Engine` used to access the
6514 database.
6516 :param schema:
6517 Optional, query and reflect tables from an alternate schema.
6518 If None, the schema associated with this :class:`_schema.MetaData`
6519 is used, if any.
6521 :param views:
6522 If True, also reflect views (materialized and plain).
6524 :param only:
6525 Optional. Load only a sub-set of available named tables. May be
6526 specified as a sequence of names or a callable.
6528 If a sequence of names is provided, only those tables will be
6529 reflected. An error is raised if a table is requested but not
6530 available. Named tables already present in this ``MetaData`` are
6531 ignored.
6533 If a callable is provided, it will be used as a boolean predicate to
6534 filter the list of potential table names. The callable is called
6535 with a table name and this ``MetaData`` instance as positional
6536 arguments and should return a true value for any table to reflect.
6538 :param extend_existing: Passed along to each :class:`_schema.Table` as
6539 :paramref:`_schema.Table.extend_existing`.
6541 :param autoload_replace: Passed along to each :class:`_schema.Table`
6542 as
6543 :paramref:`_schema.Table.autoload_replace`.
6545 :param resolve_fks: if True, reflect :class:`_schema.Table`
6546 objects linked
6547 to :class:`_schema.ForeignKey` objects located in each
6548 :class:`_schema.Table`.
6549 For :meth:`_schema.MetaData.reflect`,
6550 this has the effect of reflecting
6551 related tables that might otherwise not be in the list of tables
6552 being reflected, for example if the referenced table is in a
6553 different schema or is omitted via the
6554 :paramref:`.MetaData.reflect.only` parameter. When False,
6555 :class:`_schema.ForeignKey` objects are not followed to the
6556 :class:`_schema.Table`
6557 in which they link, however if the related table is also part of the
6558 list of tables that would be reflected in any case, the
6559 :class:`_schema.ForeignKey` object will still resolve to its related
6560 :class:`_schema.Table` after the :meth:`_schema.MetaData.reflect`
6561 operation is
6562 complete. Defaults to True.
6564 .. seealso::
6566 :paramref:`_schema.Table.resolve_fks`
6568 :param \**dialect_kwargs: Additional keyword arguments not mentioned
6569 above are dialect specific, and passed in the form
6570 ``<dialectname>_<argname>``. See the documentation regarding an
6571 individual dialect at :ref:`dialect_toplevel` for detail on
6572 documented arguments.
6574 .. seealso::
6576 :ref:`metadata_reflection_toplevel`
6578 :meth:`_events.DDLEvents.column_reflect` - Event used to customize
6579 the reflected columns. Usually used to generalize the types using
6580 :meth:`_types.TypeEngine.as_generic`
6582 :ref:`metadata_reflection_dbagnostic_types` - describes how to
6583 reflect tables using general types.
6585 """
6587 with inspection.inspect(bind)._inspection_context() as insp:
6588 reflect_opts: Any = {
6589 "autoload_with": insp,
6590 "extend_existing": extend_existing,
6591 "autoload_replace": autoload_replace,
6592 "resolve_fks": resolve_fks,
6593 "_extend_on": set(),
6594 }
6596 reflect_opts.update(dialect_kwargs)
6598 if schema is None:
6599 schema = self.schema
6601 if schema is not None:
6602 reflect_opts["schema"] = schema
6604 kind = util.preloaded.engine_reflection.ObjectKind.TABLE
6605 available: util.OrderedSet[str] = util.OrderedSet(
6606 insp.get_table_names(schema, **dialect_kwargs)
6607 )
6608 if views:
6609 kind = util.preloaded.engine_reflection.ObjectKind.ANY
6610 available.update(insp.get_view_names(schema, **dialect_kwargs))
6611 try:
6612 available.update(
6613 insp.get_materialized_view_names(
6614 schema, **dialect_kwargs
6615 )
6616 )
6617 except NotImplementedError:
6618 pass
6620 if schema is not None:
6621 available_w_schema: util.OrderedSet[str] = util.OrderedSet(
6622 [f"{schema}.{name}" for name in available]
6623 )
6624 else:
6625 available_w_schema = available
6627 current = set(self.tables)
6629 if only is None:
6630 load = [
6631 name
6632 for name, schname in zip(available, available_w_schema)
6633 if extend_existing or schname not in current
6634 ]
6635 elif callable(only):
6636 load = [
6637 name
6638 for name, schname in zip(available, available_w_schema)
6639 if (extend_existing or schname not in current)
6640 and only(name, self)
6641 ]
6642 else:
6643 missing = [name for name in only if name not in available]
6644 if missing:
6645 s = schema and (" schema '%s'" % schema) or ""
6646 missing_str = ", ".join(missing)
6647 raise exc.InvalidRequestError(
6648 f"Could not reflect: requested table(s) not available "
6649 f"in {bind.engine!r}{s}: ({missing_str})"
6650 )
6651 load = [
6652 name
6653 for name in only
6654 if extend_existing or name not in current
6655 ]
6656 # pass the available tables so the inspector can
6657 # choose to ignore the filter_names
6658 _reflect_info = insp._get_reflection_info(
6659 schema=schema,
6660 filter_names=load,
6661 available=available,
6662 kind=kind,
6663 scope=util.preloaded.engine_reflection.ObjectScope.ANY,
6664 **dialect_kwargs,
6665 )
6666 reflect_opts["_reflect_info"] = _reflect_info
6668 for name in load:
6669 try:
6670 Table(name, self, **reflect_opts)
6671 except exc.UnreflectableTableError as uerr:
6672 util.warn(f"Skipping table {name}: {uerr}")
6674 def create_all(
6675 self,
6676 bind: _CreateDropBind,
6677 tables: Optional[_typing_Sequence[Table]] = None,
6678 checkfirst: Union[bool, CheckFirst] = CheckFirst.ALL,
6679 ) -> None:
6680 """Create all tables stored in this metadata.
6682 Conditional by default, will not attempt to recreate tables already
6683 present in the target database.
6685 :param bind:
6686 A :class:`.Connection` or :class:`.Engine` used to access the
6687 database.
6689 :param tables:
6690 Optional list of ``Table`` objects, which is a subset of the total
6691 tables in the ``MetaData`` (others are ignored).
6693 :param checkfirst: A boolean value or instance of :class:`.CheckFirst`.
6694 Indicates which objects should be checked for within a separate pass
6695 before creating schema objects.
6697 """
6698 bind._run_ddl_visitor(
6699 ddl.SchemaGenerator, self, checkfirst=checkfirst, tables=tables
6700 )
6702 def drop_all(
6703 self,
6704 bind: _CreateDropBind,
6705 tables: Optional[_typing_Sequence[Table]] = None,
6706 checkfirst: Union[bool, CheckFirst] = CheckFirst.ALL,
6707 ) -> None:
6708 """Drop all tables stored in this metadata.
6710 Conditional by default, will not attempt to drop tables not present in
6711 the target database.
6713 :param bind:
6714 A :class:`.Connection` or :class:`.Engine` used to access the
6715 database.
6717 :param tables:
6718 Optional list of ``Table`` objects, which is a subset of the
6719 total tables in the ``MetaData`` (others are ignored).
6721 :param checkfirst: A boolean value or instance of :class:`.CheckFirst`.
6722 Indicates which objects should be checked for within a separate pass
6723 before dropping schema objects.
6725 """
6726 bind._run_ddl_visitor(
6727 ddl.SchemaDropper, self, checkfirst=checkfirst, tables=tables
6728 )
6730 @property
6731 def schemas(self) -> _typing_Sequence[str]:
6732 """A sequence of schema names that are present in this MetaData."""
6733 schemas = self._schemas
6734 if self.schema:
6735 schemas = schemas | {self.schema}
6736 return tuple(schemas)
6738 def get_schema_objects(
6739 self,
6740 kind: Type[_T],
6741 *,
6742 schema: Union[str, None, Literal[_NoArg.NO_ARG]] = _NoArg.NO_ARG,
6743 ) -> _typing_Sequence[_T]:
6744 """Return a sequence of schema objects of the given kind.
6746 This method can be used to return :class:`_sqltypes.Enum`,
6747 :class:`.Sequence`, etc. objects registered in this
6748 :class:`_schema.MetaData`.
6750 :param kind: a type that indicates what object to return, such as
6751 :class:`Enum` or :class:`Sequence`.
6752 :param schema: Optional, a schema name to filter the objects by. If
6753 not provided the default schema of the metadata is used.
6755 """
6757 if schema is _NoArg.NO_ARG:
6758 schema = self.schema
6759 return tuple(
6760 obj
6761 for obj in self._objects
6762 if isinstance(obj, kind) and obj.schema == schema
6763 )
6765 def get_schema_object_by_name(
6766 self,
6767 kind: Type[_T],
6768 name: str,
6769 *,
6770 schema: Union[str, None, Literal[_NoArg.NO_ARG]] = _NoArg.NO_ARG,
6771 ) -> Optional[_T]:
6772 """Return a schema objects of the given kind and name if found.
6774 This method can be used to return :class:`_sqltypes.Enum`,
6775 :class:`.Sequence`, etc. objects registered in this
6776 :class:`_schema.MetaData`.
6778 :param kind: a type that indicates what object to return, such as
6779 :class:`Enum` or :class:`Sequence`.
6780 :param name: the name of the object to return.
6781 :param schema: Optional, a schema name to filter the objects by. If
6782 not provided the default schema of the metadata is used.
6784 """
6786 for obj in self.get_schema_objects(kind, schema=schema):
6787 if getattr(obj, "name", None) == name:
6788 return obj
6789 return None
6791 def _register_object(self, obj: Union[HasSchemaAttr, SchemaType]) -> None:
6792 self._objects.add(obj)
6795class Computed(FetchedValue, SchemaItem):
6796 """Defines a generated column, i.e. "GENERATED ALWAYS AS" syntax.
6798 The :class:`.Computed` construct is an inline construct added to the
6799 argument list of a :class:`_schema.Column` object::
6801 from sqlalchemy import Computed
6803 Table(
6804 "square",
6805 metadata_obj,
6806 Column("side", Float, nullable=False),
6807 Column("area", Float, Computed("side * side")),
6808 )
6810 See the linked documentation below for complete details.
6812 .. seealso::
6814 :ref:`computed_ddl`
6816 """
6818 __visit_name__ = "computed_column"
6820 column: Optional[Column[Any]]
6822 @_document_text_coercion(
6823 "sqltext", ":class:`.Computed`", ":paramref:`.Computed.sqltext`"
6824 )
6825 def __init__(
6826 self, sqltext: _DDLColumnArgument, persisted: Optional[bool] = None
6827 ) -> None:
6828 """Construct a GENERATED ALWAYS AS DDL construct to accompany a
6829 :class:`_schema.Column`.
6831 :param sqltext:
6832 A string containing the column generation expression, which will be
6833 used verbatim, or a SQL expression construct, such as a
6834 :func:`_expression.text`
6835 object. If given as a string, the object is converted to a
6836 :func:`_expression.text` object.
6838 :param persisted:
6839 Optional, controls how this column should be persisted by the
6840 database. Possible values are:
6842 * ``None``, the default, it will use the default persistence
6843 defined by the database.
6844 * ``True``, will render ``GENERATED ALWAYS AS ... STORED``, or the
6845 equivalent for the target database if supported.
6846 * ``False``, will render ``GENERATED ALWAYS AS ... VIRTUAL``, or
6847 the equivalent for the target database if supported.
6849 Specifying ``True`` or ``False`` may raise an error when the DDL
6850 is emitted to the target database if the database does not support
6851 that persistence option. Leaving this parameter at its default
6852 of ``None`` is guaranteed to succeed for all databases that support
6853 ``GENERATED ALWAYS AS``.
6855 """
6856 self.sqltext = coercions.expect(roles.DDLExpressionRole, sqltext)
6857 self.persisted = persisted
6858 self.column = None
6860 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
6861 assert isinstance(parent, Column)
6863 if not isinstance(
6864 parent.server_default, (type(None), Computed)
6865 ) or not isinstance(parent.server_onupdate, (type(None), Computed)):
6866 raise exc.ArgumentError(
6867 "A generated column cannot specify a server_default or a "
6868 "server_onupdate argument"
6869 )
6870 self.column = parent
6871 parent.computed = self
6872 self.column.server_onupdate = self
6873 self.column.server_default = self
6875 def _as_for_update(self, for_update: bool) -> FetchedValue:
6876 return self
6878 @util.deprecated(
6879 "1.4",
6880 "The :meth:`_schema.Computed.copy` method is deprecated "
6881 "and will be removed in a future release.",
6882 )
6883 def copy(
6884 self, *, target_table: Optional[Table] = None, **kw: Any
6885 ) -> Computed:
6886 return self._copy(target_table=target_table, **kw)
6888 def _copy(
6889 self, *, target_table: Optional[Table] = None, **kw: Any
6890 ) -> Computed:
6891 sqltext = _copy_expression(
6892 self.sqltext,
6893 self.column.table if self.column is not None else None,
6894 target_table,
6895 )
6896 g = Computed(sqltext, persisted=self.persisted)
6898 return self._schema_item_copy(g)
6901class Identity(IdentityOptions, FetchedValue, SchemaItem):
6902 """Defines an identity column, i.e. "GENERATED { ALWAYS | BY DEFAULT }
6903 AS IDENTITY" syntax.
6905 The :class:`.Identity` construct is an inline construct added to the
6906 argument list of a :class:`_schema.Column` object::
6908 from sqlalchemy import Identity
6910 Table(
6911 "foo",
6912 metadata_obj,
6913 Column("id", Integer, Identity()),
6914 Column("description", Text),
6915 )
6917 See the linked documentation below for complete details.
6919 .. versionadded:: 1.4
6921 .. seealso::
6923 :ref:`identity_ddl`
6925 """
6927 __visit_name__ = "identity_column"
6929 is_identity = True
6931 @util.deprecated_params(
6932 order=(
6933 "2.1",
6934 "This parameter is supported only by Oracle Database, "
6935 "use ``oracle_order`` instead.",
6936 ),
6937 on_null=(
6938 "2.1",
6939 "This parameter is supported only by Oracle Database, "
6940 "use ``oracle_on_null`` instead.",
6941 ),
6942 )
6943 def __init__(
6944 self,
6945 always: Optional[bool] = False,
6946 on_null: Optional[bool] = None,
6947 start: Optional[int] = None,
6948 increment: Optional[int] = None,
6949 minvalue: Optional[int] = None,
6950 maxvalue: Optional[int] = None,
6951 nominvalue: Optional[bool] = None,
6952 nomaxvalue: Optional[bool] = None,
6953 cycle: Optional[bool] = None,
6954 cache: Optional[int] = None,
6955 order: Optional[bool] = None,
6956 **dialect_kw: Any,
6957 ) -> None:
6958 """Construct a GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY DDL
6959 construct to accompany a :class:`_schema.Column`.
6961 See the :class:`.Sequence` documentation for a complete description
6962 of most parameters.
6964 .. note::
6965 MSSQL supports this construct as the preferred alternative to
6966 generate an IDENTITY on a column, but it uses non standard
6967 syntax that only support :paramref:`_schema.Identity.start`
6968 and :paramref:`_schema.Identity.increment`.
6969 All other parameters are ignored.
6971 :param always:
6972 A boolean, that indicates the type of identity column.
6973 If ``False`` is specified, the default, then the user-specified
6974 value takes precedence.
6975 If ``True`` is specified, a user-specified value is not accepted (
6976 on some backends, like PostgreSQL, OVERRIDING SYSTEM VALUE, or
6977 similar, may be specified in an INSERT to override the sequence
6978 value).
6979 Some backends also have a default value for this parameter,
6980 ``None`` can be used to omit rendering this part in the DDL. It
6981 will be treated as ``False`` if a backend does not have a default
6982 value.
6984 :param on_null:
6985 Set to ``True`` to specify ON NULL in conjunction with a
6986 ``always=False`` identity column. This option is only supported on
6987 some backends, like Oracle Database.
6989 :param start: the starting index of the sequence.
6990 :param increment: the increment value of the sequence.
6991 :param minvalue: the minimum value of the sequence.
6992 :param maxvalue: the maximum value of the sequence.
6993 :param nominvalue: no minimum value of the sequence.
6994 :param nomaxvalue: no maximum value of the sequence.
6995 :param cycle: allows the sequence to wrap around when the maxvalue
6996 or minvalue has been reached.
6997 :param cache: optional integer value; number of future values in the
6998 sequence which are calculated in advance.
6999 :param order: optional boolean value; if true, renders the
7000 ORDER keyword.
7002 """
7003 self.dialect_options
7004 if on_null is not None:
7005 if "oracle_on_null" in dialect_kw:
7006 raise exc.ArgumentError(
7007 "Cannot specify both 'on_null' and 'oracle_on_null'. "
7008 "Please use only 'oracle_on_null'."
7009 )
7010 dialect_kw["oracle_on_null"] = on_null
7012 IdentityOptions.__init__(
7013 self,
7014 start=start,
7015 increment=increment,
7016 minvalue=minvalue,
7017 maxvalue=maxvalue,
7018 nominvalue=nominvalue,
7019 nomaxvalue=nomaxvalue,
7020 cycle=cycle,
7021 cache=cache,
7022 order=order,
7023 **dialect_kw,
7024 )
7025 self.always = always
7026 self.column = None
7028 @property
7029 def on_null(self) -> Optional[bool]:
7030 """Alias of the ``dialect_kwargs`` ``'oracle_on_null'``.
7032 .. deprecated:: 2.1 The 'on_null' attribute is deprecated.
7033 """
7034 value: Optional[bool] = self.dialect_kwargs.get("oracle_on_null")
7035 return value
7037 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
7038 assert isinstance(parent, Column)
7039 if not isinstance(
7040 parent.server_default, (type(None), Identity)
7041 ) or not isinstance(parent.server_onupdate, type(None)):
7042 raise exc.ArgumentError(
7043 "A column with an Identity object cannot specify a "
7044 "server_default or a server_onupdate argument"
7045 )
7046 if parent.autoincrement is False:
7047 raise exc.ArgumentError(
7048 "A column with an Identity object cannot specify "
7049 "autoincrement=False"
7050 )
7051 self.column = parent
7053 parent.identity = self
7054 if parent._user_defined_nullable is NULL_UNSPECIFIED:
7055 parent.nullable = False
7057 parent.server_default = self
7059 def _as_for_update(self, for_update: bool) -> FetchedValue:
7060 return self
7062 @util.deprecated(
7063 "1.4",
7064 "The :meth:`_schema.Identity.copy` method is deprecated "
7065 "and will be removed in a future release.",
7066 )
7067 def copy(self, **kw: Any) -> Identity:
7068 return self._copy(**kw)
7070 def _copy(self, **kw: Any) -> Identity:
7071 i = Identity(**self._as_dict(), **self.dialect_kwargs)
7073 return self._schema_item_copy(i)
7075 def _as_dict(self) -> Dict[str, Any]:
7076 return {
7077 # always=None means something different than always=False
7078 "always": self.always,
7079 **super()._as_dict(),
7080 }