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 _CovariantTypeEngineArgument
108 from ._typing import _CreateDropBind
109 from ._typing import _DDLColumnArgument
110 from ._typing import _DDLColumnReferenceArgument
111 from ._typing import _InfoType
112 from ._typing import _TextCoercedExpressionArgument
113 from ._typing import _TypeEngineArgument
114 from .base import ColumnSet
115 from .base import ReadOnlyColumnCollection
116 from .compiler import DDLCompiler
117 from .ddl import TableCreateDDL
118 from .ddl import TableDropDDL
119 from .elements import BindParameter
120 from .elements import KeyedColumnElement
121 from .functions import Function
122 from .sqltypes import SchemaType
123 from .type_api import TypeEngine
124 from .visitors import anon_map
125 from ..engine import Connection
126 from ..engine import Engine
127 from ..engine.interfaces import _CoreMultiExecuteParams
128 from ..engine.interfaces import CoreExecuteOptionsParameter
129 from ..engine.interfaces import ExecutionContext
130 from ..engine.reflection import _ReflectionInfo
131 from ..sql.selectable import FromClause
133_SI = TypeVar("_SI", bound="SchemaItem")
134_TAB = TypeVar("_TAB", bound="Table")
137_ConstraintNameArgument = Optional[Union[str, _NoneName]]
139_ServerDefaultArgument = Union[
140 "FetchedValue", str, TextClause, ColumnElement[Any]
141]
143_ServerOnUpdateArgument = _ServerDefaultArgument
146class SchemaConst(Enum):
147 RETAIN_SCHEMA = 1
148 """Symbol indicating that a :class:`_schema.Table`, :class:`.Sequence`
149 or in some cases a :class:`_schema.ForeignKey` object, in situations
150 where the object is being copied for a :meth:`.Table.to_metadata`
151 operation, should retain the schema name that it already has.
153 """
155 BLANK_SCHEMA = 2
156 """Symbol indicating that a :class:`_schema.Table` or :class:`.Sequence`
157 should have 'None' for its schema, even if the parent
158 :class:`_schema.MetaData` has specified a schema.
160 .. seealso::
162 :paramref:`_schema.MetaData.schema`
164 :paramref:`_schema.Table.schema`
166 :paramref:`.Sequence.schema`
168 """
170 NULL_UNSPECIFIED = 3
171 """Symbol indicating the "nullable" keyword was not passed to a Column.
173 This is used to distinguish between the use case of passing
174 ``nullable=None`` to a :class:`.Column`, which has special meaning
175 on some backends such as SQL Server.
177 """
180RETAIN_SCHEMA: Final[Literal[SchemaConst.RETAIN_SCHEMA]] = (
181 SchemaConst.RETAIN_SCHEMA
182)
183BLANK_SCHEMA: Final[Literal[SchemaConst.BLANK_SCHEMA]] = (
184 SchemaConst.BLANK_SCHEMA
185)
186NULL_UNSPECIFIED: Final[Literal[SchemaConst.NULL_UNSPECIFIED]] = (
187 SchemaConst.NULL_UNSPECIFIED
188)
191def _get_table_key(name: str, schema: Optional[str]) -> str:
192 if schema is None:
193 return name
194 else:
195 return schema + "." + name
198# this should really be in sql/util.py but we'd have to
199# break an import cycle
200def _copy_expression(
201 expression: ColumnElement[Any],
202 source_table: Optional[Table],
203 target_table: Optional[Table],
204) -> ColumnElement[Any]:
205 if source_table is None or target_table is None:
206 return expression
208 fixed_source_table = source_table
209 fixed_target_table = target_table
211 def replace(
212 element: ExternallyTraversible, **kw: Any
213 ) -> Optional[ExternallyTraversible]:
214 if (
215 isinstance(element, Column)
216 and element.table is fixed_source_table
217 and element.key in fixed_source_table.c
218 ):
219 return fixed_target_table.c[element.key]
220 else:
221 return None
223 return cast(
224 ColumnElement[Any],
225 visitors.replacement_traverse(expression, {}, replace),
226 )
229@inspection._self_inspects
230class SchemaItem(SchemaVisitable):
231 """Base class for items that define a database schema."""
233 __visit_name__ = "schema_item"
235 create_drop_stringify_dialect = "default"
237 def _init_items(self, *args: SchemaItem, **kw: Any) -> None:
238 """Initialize the list of child items for this SchemaItem."""
239 for item in args:
240 if item is not None:
241 try:
242 spwd = item._set_parent_with_dispatch
243 except AttributeError as err:
244 raise exc.ArgumentError(
245 "'SchemaItem' object, such as a 'Column' or a "
246 f"'Constraint' expected, got {item!r}"
247 ) from err
248 else:
249 spwd(self, **kw)
251 def __repr__(self) -> str:
252 return util.generic_repr(self, omit_kwarg=["info"])
254 @util.memoized_property
255 def info(self) -> _InfoType:
256 """Info dictionary associated with the object, allowing user-defined
257 data to be associated with this :class:`.SchemaItem`.
259 The dictionary is automatically generated when first accessed.
260 It can also be specified in the constructor of some objects,
261 such as :class:`_schema.Table` and :class:`_schema.Column`.
263 """
264 return {}
266 def _schema_item_copy(self, schema_item: _SI) -> _SI:
267 if "info" in self.__dict__:
268 schema_item.info = self.info.copy()
269 if isinstance(self, DialectKWArgs):
270 assert isinstance(schema_item, DialectKWArgs)
271 self._copy_reflect_only_elements(schema_item)
272 schema_item.dispatch._update(self.dispatch)
273 return schema_item
275 _use_schema_map = True
278class HasConditionalDDL:
279 """define a class that includes the :meth:`.HasConditionalDDL.ddl_if`
280 method, allowing for conditional rendering of DDL.
282 Currently applies to constraints and indexes.
284 .. versionadded:: 2.0
287 """
289 _ddl_if: Optional[ddl.DDLIf] = None
291 def ddl_if(
292 self,
293 dialect: Optional[str] = None,
294 callable_: Optional[ddl.DDLIfCallable] = None,
295 state: Optional[Any] = None,
296 ) -> Self:
297 r"""apply a conditional DDL rule to this schema item.
299 These rules work in a similar manner to the
300 :meth:`.ExecutableDDLElement.execute_if` callable, with the added
301 feature that the criteria may be checked within the DDL compilation
302 phase for a construct such as :class:`.CreateTable`.
303 :meth:`.HasConditionalDDL.ddl_if` currently applies towards the
304 :class:`.Index` construct as well as all :class:`.Constraint`
305 constructs.
307 :param dialect: string name of a dialect, or a tuple of string names
308 to indicate multiple dialect types.
310 :param callable\_: a callable that is constructed using the same form
311 as that described in
312 :paramref:`.ExecutableDDLElement.execute_if.callable_`.
314 :param state: any arbitrary object that will be passed to the
315 callable, if present.
317 .. versionadded:: 2.0
319 .. seealso::
321 :ref:`schema_ddl_ddl_if` - background and usage examples
324 """
325 self._ddl_if = ddl.DDLIf(dialect, callable_, state)
326 return self
329class HasSchemaAttr(SchemaItem):
330 """schema item that includes a top-level schema name"""
332 schema: Optional[str]
335class Table(
336 DialectKWArgs,
337 HasSchemaAttr,
338 TableClause[_ColCC_co],
339 inspection.Inspectable["Table"],
340):
341 r"""Represent a table in a database.
343 e.g.::
345 from sqlalchemy import Table, MetaData, Integer, String, Column
347 metadata = MetaData()
349 mytable = Table(
350 "mytable",
351 metadata,
352 Column("mytable_id", Integer, primary_key=True),
353 Column("value", String(50)),
354 )
356 The :class:`_schema.Table`
357 object constructs a unique instance of itself based
358 on its name and optional schema name within the given
359 :class:`_schema.MetaData` object. Calling the :class:`_schema.Table`
360 constructor with the same name and same :class:`_schema.MetaData` argument
361 a second time will return the *same* :class:`_schema.Table`
362 object - in this way
363 the :class:`_schema.Table` constructor acts as a registry function.
365 May also be defined as "typed table" by passing a subclass of
366 :class:`_schema.TypedColumns` as the 3rd argument::
368 from sqlalchemy import TypedColumns, select
371 class user_cols(TypedColumns):
372 id = Column(Integer, primary_key=True)
373 name: Column[str]
374 age: Column[int]
375 middle_name: Column[str | None]
377 # optional, used to infer the select types when selecting the table
378 __row_pos__: tuple[int, str, int, str | None]
381 user = Table("user", metadata, user_cols)
383 # the columns are typed: the statement has type Select[int, str]
384 stmt = sa.select(user.c.id, user.c.name).where(user.c.age > 30)
386 # Inferred as Select[int, str, int, str | None] thanks to __row_pos__
387 stmt1 = user.select()
388 stmt2 = sa.select(user)
390 The :attr:`sqlalchemy.sql._annotated_cols.HasRowPos.__row_pos__`
391 annotation is optional, and it's used to infer the types in a
392 :class:`_sql.Select` when selecting the complete table.
393 If a :class:`_schema.TypedColumns` does not define it,
394 the default ``Select[*tuple[Any]]`` will be inferred.
396 An existing :class:`Table` can be casted as "typed table" using
397 the :meth:`Table.with_cols`::
399 class mytable_cols(TypedColumns):
400 mytable_id: Column[int]
401 value: Column[str | None]
404 typed_mytable = mytable.with_cols(mytable_cols)
406 .. seealso::
408 :ref:`metadata_describing` Introduction to database metadata
410 :class:`_schema.TypedColumns` More information about typed column
411 definition
413 .. versionchanged:: 2.1.0b2 - :class:`_schema.Table` is now generic to
414 support "typed tables"
415 """
417 __visit_name__ = "table"
419 if TYPE_CHECKING:
421 @util.ro_non_memoized_property
422 def primary_key(self) -> PrimaryKeyConstraint: ...
424 @util.ro_non_memoized_property
425 def foreign_keys(self) -> Set[ForeignKey]: ...
427 def with_cols(self, type_: type[_TC_co]) -> Table[_TC_co]: ...
429 _columns: DedupeColumnCollection[Column[Any]] # type: ignore[assignment]
431 _sentinel_column: Optional[Column[Any]]
433 constraints: Set[Constraint]
434 """A collection of all :class:`_schema.Constraint` objects associated with
435 this :class:`_schema.Table`.
437 Includes :class:`_schema.PrimaryKeyConstraint`,
438 :class:`_schema.ForeignKeyConstraint`, :class:`_schema.UniqueConstraint`,
439 :class:`_schema.CheckConstraint`. A separate collection
440 :attr:`_schema.Table.foreign_key_constraints` refers to the collection
441 of all :class:`_schema.ForeignKeyConstraint` objects, and the
442 :attr:`_schema.Table.primary_key` attribute refers to the single
443 :class:`_schema.PrimaryKeyConstraint` associated with the
444 :class:`_schema.Table`.
446 .. seealso::
448 :attr:`_schema.Table.constraints`
450 :attr:`_schema.Table.primary_key`
452 :attr:`_schema.Table.foreign_key_constraints`
454 :attr:`_schema.Table.indexes`
456 :class:`_reflection.Inspector`
459 """
461 indexes: Set[Index]
462 """A collection of all :class:`_schema.Index` objects associated with this
463 :class:`_schema.Table`.
465 .. seealso::
467 :meth:`_reflection.Inspector.get_indexes`
469 """
471 def _gen_cache_key(
472 self, anon_map: anon_map, bindparams: List[BindParameter[Any]]
473 ) -> Tuple[Any, ...]:
474 if self._annotations:
475 return (self,) + self._annotations_cache_key
476 else:
477 return (self,)
479 if not typing.TYPE_CHECKING:
480 # typing tools seem to be inconsistent in how they handle
481 # __new__, so suggest this pattern for classes that use
482 # __new__. apply typing to the __init__ method normally
483 @util.deprecated_params(
484 mustexist=(
485 "1.4",
486 "Deprecated alias of :paramref:`_schema.Table.must_exist`",
487 ),
488 )
489 def __new__(cls, *args: Any, **kw: Any) -> Any:
490 return cls._new(*args, **kw)
492 @classmethod
493 def _new(cls, *args: Any, **kw: Any) -> Any:
494 if not args and not kw:
495 # python3k pickle seems to call this
496 return object.__new__(cls)
498 try:
499 name, metadata, *other_args = args
500 except ValueError:
501 raise TypeError(
502 "Table() takes at least two positional-only "
503 "arguments 'name', and 'metadata'"
504 ) from None
505 if other_args and isinstance(other_args[0], type):
506 typed_columns_cls = other_args[0]
507 if not issubclass(typed_columns_cls, TypedColumns):
508 raise exc.InvalidRequestError(
509 "The ``typed_columns_cls`` argument requires a "
510 "TypedColumns subclass."
511 )
512 elif hasattr(typed_columns_cls, "_sa_class_manager"):
513 # an orm class subclassed with TypedColumns. Reject it
514 raise exc.InvalidRequestError(
515 "To get a typed table from an ORM class, use the "
516 "`as_typed_table()` function instead."
517 )
519 extracted_columns = _extract_columns_from_class(typed_columns_cls)
520 other_args = extracted_columns + other_args[1:]
521 elif "typed_columns_cls" in kw:
522 raise TypeError(
523 "The ``typed_columns_cls`` argument may be passed "
524 "only positionally"
525 )
527 schema = kw.get("schema", None)
528 if schema is None:
529 schema = metadata.schema
530 elif schema is BLANK_SCHEMA:
531 schema = None
532 keep_existing = kw.get("keep_existing", False)
533 extend_existing = kw.get("extend_existing", False)
535 if keep_existing and extend_existing:
536 msg = "keep_existing and extend_existing are mutually exclusive."
537 raise exc.ArgumentError(msg)
539 must_exist = kw.pop("must_exist", kw.pop("mustexist", False))
540 key = _get_table_key(name, schema)
541 if key in metadata.tables:
542 if not keep_existing and not extend_existing and bool(other_args):
543 raise exc.InvalidRequestError(
544 f"Table '{key}' is already defined for this MetaData "
545 "instance. Specify 'extend_existing=True' "
546 "to redefine "
547 "options and columns on an "
548 "existing Table object."
549 )
550 table = metadata.tables[key]
551 if extend_existing:
552 table._init_existing(*other_args, **kw)
553 return table
554 else:
555 if must_exist:
556 raise exc.InvalidRequestError(f"Table '{key}' not defined")
557 table = object.__new__(cls)
558 table.dispatch.before_parent_attach(table, metadata)
559 metadata._add_table(name, schema, table)
560 try:
561 table.__init__(name, metadata, *other_args, _no_init=False, **kw) # type: ignore[misc] # noqa: E501
562 table.dispatch.after_parent_attach(table, metadata)
563 return table
564 except Exception:
565 with util.safe_reraise():
566 metadata._remove_table(name, schema)
568 @overload
569 def __init__(
570 self: Table[_TC_co],
571 name: str,
572 metadata: MetaData,
573 typed_columns_cls: type[_TC_co],
574 /,
575 *args: SchemaItem,
576 schema: str | Literal[SchemaConst.BLANK_SCHEMA] | None = None,
577 quote: bool | None = None,
578 quote_schema: bool | None = None,
579 keep_existing: bool = False,
580 extend_existing: bool = False,
581 implicit_returning: bool = True,
582 comment: str | None = None,
583 info: dict[Any, Any] | None = None,
584 listeners: (
585 _typing_Sequence[tuple[str, Callable[..., Any]]] | None
586 ) = None,
587 prefixes: _typing_Sequence[str] | None = None,
588 **kw: Any,
589 ) -> None: ...
591 @overload
592 def __init__(
593 self: Table[ReadOnlyColumnCollection[str, Column[Any]]],
594 name: str,
595 metadata: MetaData,
596 /,
597 *args: SchemaItem,
598 schema: str | Literal[SchemaConst.BLANK_SCHEMA] | None = None,
599 quote: bool | None = None,
600 quote_schema: Optional[bool] = None,
601 autoload_with: Optional[Union[Engine, Connection]] = None,
602 autoload_replace: bool = True,
603 keep_existing: bool = False,
604 extend_existing: bool = False,
605 resolve_fks: bool = True,
606 include_columns: Optional[Collection[str]] = None,
607 implicit_returning: bool = True,
608 comment: str | None = None,
609 info: dict[Any, Any] | None = None,
610 listeners: (
611 _typing_Sequence[tuple[str, Callable[..., Any]]] | None
612 ) = None,
613 prefixes: _typing_Sequence[str] | None = None,
614 _creator_ddl: TableCreateDDL | None = None,
615 _dropper_ddl: TableDropDDL | None = None,
616 # used internally in the metadata.reflect() process
617 _extend_on: Optional[Set[Table]] = None,
618 # used by __new__ to bypass __init__
619 _no_init: bool = True,
620 # dialect-specific keyword args
621 **kw: Any,
622 ) -> None: ...
624 def __init__(
625 self,
626 name: str,
627 metadata: MetaData,
628 /,
629 *args: Any,
630 schema: str | Literal[SchemaConst.BLANK_SCHEMA] | None = None,
631 quote: bool | None = None,
632 quote_schema: Optional[bool] = None,
633 autoload_with: Optional[Union[Engine, Connection]] = None,
634 autoload_replace: bool = True,
635 keep_existing: bool = False,
636 extend_existing: bool = False,
637 resolve_fks: bool = True,
638 include_columns: Optional[Collection[str]] = None,
639 implicit_returning: bool = True,
640 comment: str | None = None,
641 info: dict[Any, Any] | None = None,
642 listeners: (
643 _typing_Sequence[tuple[str, Callable[..., Any]]] | None
644 ) = None,
645 prefixes: _typing_Sequence[str] | None = None,
646 _creator_ddl: TableCreateDDL | None = None,
647 _dropper_ddl: TableDropDDL | None = None,
648 # used internally in the metadata.reflect() process
649 _extend_on: Optional[Set[Table]] = None,
650 # used by __new__ to bypass __init__
651 _no_init: bool = True,
652 # dialect-specific keyword args
653 **kw: Any,
654 ) -> None:
655 r"""Constructor for :class:`_schema.Table`.
658 :param name: The name of this table as represented in the database.
660 The table name, along with the value of the ``schema`` parameter,
661 forms a key which uniquely identifies this :class:`_schema.Table`
662 within
663 the owning :class:`_schema.MetaData` collection.
664 Additional calls to :class:`_schema.Table` with the same name,
665 metadata,
666 and schema name will return the same :class:`_schema.Table` object.
668 Names which contain no upper case characters
669 will be treated as case insensitive names, and will not be quoted
670 unless they are a reserved word or contain special characters.
671 A name with any number of upper case characters is considered
672 to be case sensitive, and will be sent as quoted.
674 To enable unconditional quoting for the table name, specify the flag
675 ``quote=True`` to the constructor, or use the :class:`.quoted_name`
676 construct to specify the name.
678 :param metadata: a :class:`_schema.MetaData`
679 object which will contain this
680 table. The metadata is used as a point of association of this table
681 with other tables which are referenced via foreign key. It also
682 may be used to associate this table with a particular
683 :class:`.Connection` or :class:`.Engine`.
685 :param table_columns_cls: a subclass of :class:`_schema.TypedColumns`
686 that defines the columns that will be "typed" when accessing
687 them from the :attr:`_schema.Table.c` attribute.
689 .. versionadded:: 2.1.0b2
691 :param \*args: Additional positional arguments are used primarily
692 to add the list of :class:`_schema.Column`
693 objects contained within this
694 table. Similar to the style of a CREATE TABLE statement, other
695 :class:`.SchemaItem` constructs may be added here, including
696 :class:`.PrimaryKeyConstraint`, and
697 :class:`_schema.ForeignKeyConstraint`.
698 Additional columns may be provided also when using a
699 :paramref:`_schema.Table.table_columns_cls` class; they will
700 be appended to the "typed" columns and will appear as untyped
701 when accessing them via the :attr:`_schema.Table.c` collection.
703 :param autoload_replace: Defaults to ``True``; when using
704 :paramref:`_schema.Table.autoload_with`
705 in conjunction with :paramref:`_schema.Table.extend_existing`,
706 indicates
707 that :class:`_schema.Column` objects present in the already-existing
708 :class:`_schema.Table`
709 object should be replaced with columns of the same
710 name retrieved from the autoload process. When ``False``, columns
711 already present under existing names will be omitted from the
712 reflection process.
714 Note that this setting does not impact :class:`_schema.Column` objects
715 specified programmatically within the call to :class:`_schema.Table`
716 that
717 also is autoloading; those :class:`_schema.Column` objects will always
718 replace existing columns of the same name when
719 :paramref:`_schema.Table.extend_existing` is ``True``.
721 .. seealso::
723 :paramref:`_schema.Table.autoload_with`
725 :paramref:`_schema.Table.extend_existing`
727 :param autoload_with: An :class:`_engine.Engine` or
728 :class:`_engine.Connection` object,
729 or a :class:`_reflection.Inspector` object as returned by
730 :func:`_sa.inspect`
731 against one, with which this :class:`_schema.Table`
732 object will be reflected.
733 When set to a non-None value, the autoload process will take place
734 for this table against the given engine or connection.
736 .. seealso::
738 :ref:`metadata_reflection_toplevel`
740 :meth:`_events.DDLEvents.column_reflect`
742 :ref:`metadata_reflection_dbagnostic_types`
744 :param extend_existing: When ``True``, indicates that if this
745 :class:`_schema.Table` is already present in the given
746 :class:`_schema.MetaData`,
747 apply further arguments within the constructor to the existing
748 :class:`_schema.Table`.
750 If :paramref:`_schema.Table.extend_existing` or
751 :paramref:`_schema.Table.keep_existing` are not set,
752 and the given name
753 of the new :class:`_schema.Table` refers to a :class:`_schema.Table`
754 that is
755 already present in the target :class:`_schema.MetaData` collection,
756 and
757 this :class:`_schema.Table`
758 specifies additional columns or other constructs
759 or flags that modify the table's state, an
760 error is raised. The purpose of these two mutually-exclusive flags
761 is to specify what action should be taken when a
762 :class:`_schema.Table`
763 is specified that matches an existing :class:`_schema.Table`,
764 yet specifies
765 additional constructs.
767 :paramref:`_schema.Table.extend_existing`
768 will also work in conjunction
769 with :paramref:`_schema.Table.autoload_with` to run a new reflection
770 operation against the database, even if a :class:`_schema.Table`
771 of the same name is already present in the target
772 :class:`_schema.MetaData`; newly reflected :class:`_schema.Column`
773 objects
774 and other options will be added into the state of the
775 :class:`_schema.Table`, potentially overwriting existing columns
776 and options of the same name.
778 As is always the case with :paramref:`_schema.Table.autoload_with`,
779 :class:`_schema.Column` objects can be specified in the same
780 :class:`_schema.Table`
781 constructor, which will take precedence. Below, the existing
782 table ``mytable`` will be augmented with :class:`_schema.Column`
783 objects
784 both reflected from the database, as well as the given
785 :class:`_schema.Column`
786 named "y"::
788 Table(
789 "mytable",
790 metadata,
791 Column("y", Integer),
792 extend_existing=True,
793 autoload_with=engine,
794 )
796 .. seealso::
798 :paramref:`_schema.Table.autoload_with`
800 :paramref:`_schema.Table.autoload_replace`
802 :paramref:`_schema.Table.keep_existing`
805 :param implicit_returning: True by default - indicates that
806 RETURNING can be used, typically by the ORM, in order to fetch
807 server-generated values such as primary key values and
808 server side defaults, on those backends which support RETURNING.
810 In modern SQLAlchemy there is generally no reason to alter this
811 setting, except for some backend specific cases
812 (see :ref:`mssql_triggers` in the SQL Server dialect documentation
813 for one such example).
815 :param include_columns: A list of strings indicating a subset of
816 columns to be loaded via the ``autoload`` operation; table columns who
817 aren't present in this list will not be represented on the resulting
818 ``Table`` object. Defaults to ``None`` which indicates all columns
819 should be reflected.
821 :param resolve_fks: Whether or not to reflect :class:`_schema.Table`
822 objects
823 related to this one via :class:`_schema.ForeignKey` objects, when
824 :paramref:`_schema.Table.autoload_with` is
825 specified. Defaults to True. Set to False to disable reflection of
826 related tables as :class:`_schema.ForeignKey`
827 objects are encountered; may be
828 used either to save on SQL calls or to avoid issues with related tables
829 that can't be accessed. Note that if a related table is already present
830 in the :class:`_schema.MetaData` collection, or becomes present later,
831 a
832 :class:`_schema.ForeignKey` object associated with this
833 :class:`_schema.Table` will
834 resolve to that table normally.
836 .. seealso::
838 :paramref:`.MetaData.reflect.resolve_fks`
841 :param info: Optional data dictionary which will be populated into the
842 :attr:`.SchemaItem.info` attribute of this object.
844 :param keep_existing: When ``True``, indicates that if this Table
845 is already present in the given :class:`_schema.MetaData`, ignore
846 further arguments within the constructor to the existing
847 :class:`_schema.Table`, and return the :class:`_schema.Table`
848 object as
849 originally created. This is to allow a function that wishes
850 to define a new :class:`_schema.Table` on first call, but on
851 subsequent calls will return the same :class:`_schema.Table`,
852 without any of the declarations (particularly constraints)
853 being applied a second time.
855 If :paramref:`_schema.Table.extend_existing` or
856 :paramref:`_schema.Table.keep_existing` are not set,
857 and the given name
858 of the new :class:`_schema.Table` refers to a :class:`_schema.Table`
859 that is
860 already present in the target :class:`_schema.MetaData` collection,
861 and
862 this :class:`_schema.Table`
863 specifies additional columns or other constructs
864 or flags that modify the table's state, an
865 error is raised. The purpose of these two mutually-exclusive flags
866 is to specify what action should be taken when a
867 :class:`_schema.Table`
868 is specified that matches an existing :class:`_schema.Table`,
869 yet specifies
870 additional constructs.
872 .. seealso::
874 :paramref:`_schema.Table.extend_existing`
876 :param listeners: A list of tuples of the form ``(<eventname>, <fn>)``
877 which will be passed to :func:`.event.listen` upon construction.
878 This alternate hook to :func:`.event.listen` allows the establishment
879 of a listener function specific to this :class:`_schema.Table` before
880 the "autoload" process begins. Historically this has been intended
881 for use with the :meth:`.DDLEvents.column_reflect` event, however
882 note that this event hook may now be associated with the
883 :class:`_schema.MetaData` object directly::
885 def listen_for_reflect(table, column_info):
886 "handle the column reflection event"
887 # ...
890 t = Table(
891 "sometable",
892 autoload_with=engine,
893 listeners=[("column_reflect", listen_for_reflect)],
894 )
896 .. seealso::
898 :meth:`_events.DDLEvents.column_reflect`
900 :param must_exist: When ``True``, indicates that this Table must already
901 be present in the given :class:`_schema.MetaData` collection, else
902 an exception is raised.
904 :param prefixes:
905 A list of strings to insert after CREATE in the CREATE TABLE
906 statement. They will be separated by spaces.
908 :param quote: Force quoting of this table's name on or off, corresponding
909 to ``True`` or ``False``. When left at its default of ``None``,
910 the column identifier will be quoted according to whether the name is
911 case sensitive (identifiers with at least one upper case character are
912 treated as case sensitive), or if it's a reserved word. This flag
913 is only needed to force quoting of a reserved word which is not known
914 by the SQLAlchemy dialect.
916 .. note:: setting this flag to ``False`` will not provide
917 case-insensitive behavior for table reflection; table reflection
918 will always search for a mixed-case name in a case sensitive
919 fashion. Case insensitive names are specified in SQLAlchemy only
920 by stating the name with all lower case characters.
922 :param quote_schema: same as 'quote' but applies to the schema identifier.
924 :param schema: The schema name for this table, which is required if
925 the table resides in a schema other than the default selected schema
926 for the engine's database connection. Defaults to ``None``.
928 If the owning :class:`_schema.MetaData` of this :class:`_schema.Table`
929 specifies its
930 own :paramref:`_schema.MetaData.schema` parameter,
931 then that schema name will
932 be applied to this :class:`_schema.Table`
933 if the schema parameter here is set
934 to ``None``. To set a blank schema name on a :class:`_schema.Table`
935 that
936 would otherwise use the schema set on the owning
937 :class:`_schema.MetaData`,
938 specify the special symbol :attr:`.BLANK_SCHEMA`.
940 The quoting rules for the schema name are the same as those for the
941 ``name`` parameter, in that quoting is applied for reserved words or
942 case-sensitive names; to enable unconditional quoting for the schema
943 name, specify the flag ``quote_schema=True`` to the constructor, or use
944 the :class:`.quoted_name` construct to specify the name.
946 :param comment: Optional string that will render an SQL comment on table
947 creation.
949 :param \**kw: Additional keyword arguments not mentioned above are
950 dialect specific, and passed in the form ``<dialectname>_<argname>``.
951 See the documentation regarding an individual dialect at
952 :ref:`dialect_toplevel` for detail on documented arguments.
954 """ # noqa: E501
955 if _no_init:
956 # don't run __init__ from __new__ by default;
957 # __new__ has a specific place that __init__ is called
958 return
959 if args:
960 # this is the call done by `__new__` that should have resolved
961 # TypedColumns to the individual columns
962 assert not (
963 isinstance(args[0], type) and issubclass(args[0], TypedColumns)
964 )
966 super().__init__(quoted_name(name, quote))
967 self.metadata = metadata
969 if schema is None:
970 self.schema = metadata.schema
971 elif schema is BLANK_SCHEMA:
972 self.schema = None
973 else:
974 quote_schema = quote_schema
975 assert isinstance(schema, str)
976 self.schema = quoted_name(schema, quote_schema)
978 self._sentinel_column = None
979 self._creator_ddl = _creator_ddl
980 self._dropper_ddl = _dropper_ddl
982 self.indexes = set()
983 self.constraints = set()
984 PrimaryKeyConstraint(
985 _implicit_generated=True
986 )._set_parent_with_dispatch(self)
987 self.foreign_keys = set() # type: ignore[misc]
988 self._extra_dependencies: Set[Table] = set()
989 if self.schema is not None:
990 self.fullname = "%s.%s" % (self.schema, self.name)
991 else:
992 self.fullname = self.name
994 self.implicit_returning = implicit_returning
995 _reflect_info = kw.pop("_reflect_info", None)
997 self.comment = comment
999 if info is not None:
1000 self.info = info
1002 if listeners is not None:
1003 for evt, fn in listeners:
1004 event.listen(self, evt, fn)
1006 self._prefixes = prefixes if prefixes else []
1008 self._extra_kwargs(**kw)
1010 # load column definitions from the database if 'autoload' is defined
1011 # we do it after the table is in the singleton dictionary to support
1012 # circular foreign keys
1013 if autoload_with is not None:
1014 self._autoload(
1015 metadata,
1016 autoload_with,
1017 include_columns,
1018 _extend_on=_extend_on,
1019 _reflect_info=_reflect_info,
1020 resolve_fks=resolve_fks,
1021 )
1023 # initialize all the column, etc. objects. done after reflection to
1024 # allow user-overrides
1026 self._init_items(
1027 *args,
1028 allow_replacements=extend_existing
1029 or keep_existing
1030 or autoload_with,
1031 all_names={},
1032 )
1034 def set_creator_ddl(self, ddl: TableCreateDDL) -> None:
1035 """Set the table create DDL for this :class:`.Table`.
1037 This allows the CREATE TABLE statement to be controlled or replaced
1038 entirely when :meth:`.Table.create` or :meth:`.MetaData.create_all` is
1039 used.
1041 E.g.::
1043 from sqlalchemy.schema import CreateTable
1045 table.set_creator_ddl(CreateTable(table, if_not_exists=True))
1047 .. versionadded:: 2.1
1049 .. seealso::
1051 :meth:`.Table.set_dropper_ddl`
1053 """
1054 self._creator_ddl = ddl
1056 def set_dropper_ddl(self, ddl: TableDropDDL) -> None:
1057 """Set the table drop DDL for this :class:`.Table`.
1059 This allows the DROP TABLE statement to be controlled or replaced
1060 entirely when :meth:`.Table.drop` or :meth:`.MetaData.drop_all` is
1061 used.
1063 E.g.::
1065 from sqlalchemy.schema import DropTable
1067 table.set_dropper_ddl(DropTable(table, if_exists=True))
1069 .. versionadded:: 2.1
1071 .. seealso::
1073 :meth:`.Table.set_creator_ddl`
1075 """
1076 self._dropper_ddl = ddl
1078 @property
1079 def is_view(self) -> bool:
1080 """True if this table, when DDL for CREATE is emitted, will emit
1081 CREATE VIEW rather than CREATE TABLE.
1083 .. versionadded:: 2.1
1085 """
1086 return isinstance(self._creator_ddl, ddl.CreateView)
1088 def _autoload(
1089 self,
1090 metadata: MetaData,
1091 autoload_with: Union[Engine, Connection],
1092 include_columns: Optional[Collection[str]],
1093 exclude_columns: Collection[str] = (),
1094 resolve_fks: bool = True,
1095 _extend_on: Optional[Set[Table]] = None,
1096 _reflect_info: _ReflectionInfo | None = None,
1097 ) -> None:
1098 insp = inspection.inspect(autoload_with)
1099 with insp._inspection_context() as conn_insp:
1100 conn_insp.reflect_table(
1101 self,
1102 include_columns,
1103 exclude_columns,
1104 resolve_fks,
1105 _extend_on=_extend_on,
1106 _reflect_info=_reflect_info,
1107 )
1109 @property
1110 def _sorted_constraints(self) -> List[Constraint]:
1111 """Return the set of constraints as a list, sorted by creation
1112 order.
1114 """
1116 return sorted(self.constraints, key=lambda c: c._creation_order)
1118 @property
1119 def foreign_key_constraints(self) -> Set[ForeignKeyConstraint]:
1120 """:class:`_schema.ForeignKeyConstraint` objects referred to by this
1121 :class:`_schema.Table`.
1123 This list is produced from the collection of
1124 :class:`_schema.ForeignKey`
1125 objects currently associated.
1128 .. seealso::
1130 :attr:`_schema.Table.constraints`
1132 :attr:`_schema.Table.foreign_keys`
1134 :attr:`_schema.Table.indexes`
1136 """
1137 return {
1138 fkc.constraint
1139 for fkc in self.foreign_keys
1140 if fkc.constraint is not None
1141 }
1143 def _init_existing(self, *args: Any, **kwargs: Any) -> None:
1144 autoload_with = kwargs.pop("autoload_with", None)
1145 autoload = kwargs.pop("autoload", autoload_with is not None)
1146 autoload_replace = kwargs.pop("autoload_replace", True)
1147 schema = kwargs.pop("schema", None)
1148 _extend_on = kwargs.pop("_extend_on", None)
1149 _reflect_info = kwargs.pop("_reflect_info", None)
1151 # these arguments are only used with _init()
1152 extend_existing = kwargs.pop("extend_existing", False)
1153 keep_existing = kwargs.pop("keep_existing", False)
1155 assert extend_existing
1156 assert not keep_existing
1158 if schema and schema != self.schema:
1159 raise exc.ArgumentError(
1160 f"Can't change schema of existing table "
1161 f"from '{self.schema}' to '{schema}'",
1162 )
1164 include_columns = kwargs.pop("include_columns", None)
1165 if include_columns is not None:
1166 for c in self.c:
1167 if c.name not in include_columns:
1168 self._columns.remove(c)
1170 resolve_fks = kwargs.pop("resolve_fks", True)
1172 for key in ("quote", "quote_schema"):
1173 if key in kwargs:
1174 raise exc.ArgumentError(
1175 "Can't redefine 'quote' or 'quote_schema' arguments"
1176 )
1178 # update `self` with these kwargs, if provided
1179 self.comment = kwargs.pop("comment", self.comment)
1180 self.implicit_returning = kwargs.pop(
1181 "implicit_returning", self.implicit_returning
1182 )
1183 self.info = kwargs.pop("info", self.info)
1185 exclude_columns: _typing_Sequence[str]
1187 if autoload:
1188 if not autoload_replace:
1189 # don't replace columns already present.
1190 # we'd like to do this for constraints also however we don't
1191 # have simple de-duping for unnamed constraints.
1192 exclude_columns = [c.name for c in self.c]
1193 else:
1194 exclude_columns = ()
1195 self._autoload(
1196 self.metadata,
1197 autoload_with,
1198 include_columns,
1199 exclude_columns,
1200 resolve_fks,
1201 _extend_on=_extend_on,
1202 _reflect_info=_reflect_info,
1203 )
1205 all_names = {c.name: c for c in self.c}
1206 self._extra_kwargs(**kwargs)
1207 self._init_items(*args, allow_replacements=True, all_names=all_names)
1209 def _extra_kwargs(self, **kwargs: Any) -> None:
1210 self._validate_dialect_kwargs(kwargs)
1212 def _init_collections(self) -> None:
1213 pass
1215 def _reset_exported(self) -> None:
1216 pass
1218 @util.ro_non_memoized_property
1219 def _autoincrement_column(self) -> Optional[Column[int]]:
1220 return self.primary_key._autoincrement_column
1222 @util.ro_memoized_property
1223 def _sentinel_column_characteristics(
1224 self,
1225 ) -> _SentinelColumnCharacterization:
1226 """determine a candidate column (or columns, in case of a client
1227 generated composite primary key) which can be used as an
1228 "insert sentinel" for an INSERT statement.
1230 The returned structure, :class:`_SentinelColumnCharacterization`,
1231 includes all the details needed by :class:`.Dialect` and
1232 :class:`.SQLCompiler` to determine if these column(s) can be used
1233 as an INSERT..RETURNING sentinel for a particular database
1234 dialect.
1236 .. versionadded:: 2.0.10
1238 """
1240 sentinel_is_explicit = False
1241 sentinel_is_autoinc = False
1242 the_sentinel: Optional[_typing_Sequence[Column[Any]]] = None
1244 # see if a column was explicitly marked "insert_sentinel=True".
1245 explicit_sentinel_col = self._sentinel_column
1247 if explicit_sentinel_col is not None:
1248 the_sentinel = (explicit_sentinel_col,)
1249 sentinel_is_explicit = True
1251 autoinc_col = self._autoincrement_column
1252 if sentinel_is_explicit and explicit_sentinel_col is autoinc_col:
1253 assert autoinc_col is not None
1254 sentinel_is_autoinc = True
1255 elif explicit_sentinel_col is None and autoinc_col is not None:
1256 the_sentinel = (autoinc_col,)
1257 sentinel_is_autoinc = True
1259 default_characterization = _SentinelDefaultCharacterization.UNKNOWN
1261 if the_sentinel:
1262 the_sentinel_zero = the_sentinel[0]
1263 if the_sentinel_zero.identity:
1264 if the_sentinel_zero.identity._increment_is_negative:
1265 if sentinel_is_explicit:
1266 raise exc.InvalidRequestError(
1267 "Can't use IDENTITY default with negative "
1268 "increment as an explicit sentinel column"
1269 )
1270 else:
1271 if sentinel_is_autoinc:
1272 autoinc_col = None
1273 sentinel_is_autoinc = False
1274 the_sentinel = None
1275 else:
1276 default_characterization = (
1277 _SentinelDefaultCharacterization.IDENTITY
1278 )
1279 elif (
1280 the_sentinel_zero.default is None
1281 and the_sentinel_zero.server_default is None
1282 ):
1283 if the_sentinel_zero.nullable:
1284 raise exc.InvalidRequestError(
1285 f"Column {the_sentinel_zero} has been marked as a "
1286 "sentinel "
1287 "column with no default generation function; it "
1288 "at least needs to be marked nullable=False assuming "
1289 "user-populated sentinel values will be used."
1290 )
1291 default_characterization = (
1292 _SentinelDefaultCharacterization.NONE
1293 )
1294 elif the_sentinel_zero.default is not None:
1295 if the_sentinel_zero.default.is_sentinel:
1296 default_characterization = (
1297 _SentinelDefaultCharacterization.SENTINEL_DEFAULT
1298 )
1299 elif the_sentinel_zero.default._is_monotonic_fn:
1300 default_characterization = (
1301 _SentinelDefaultCharacterization.MONOTONIC_FUNCTION
1302 )
1303 elif default_is_sequence(the_sentinel_zero.default):
1304 if the_sentinel_zero.default._increment_is_negative:
1305 if sentinel_is_explicit:
1306 raise exc.InvalidRequestError(
1307 "Can't use SEQUENCE default with negative "
1308 "increment as an explicit sentinel column"
1309 )
1310 else:
1311 if sentinel_is_autoinc:
1312 autoinc_col = None
1313 sentinel_is_autoinc = False
1314 the_sentinel = None
1316 default_characterization = (
1317 _SentinelDefaultCharacterization.SEQUENCE
1318 )
1319 elif the_sentinel_zero.default.is_callable:
1320 default_characterization = (
1321 _SentinelDefaultCharacterization.CLIENTSIDE
1322 )
1323 elif the_sentinel_zero.server_default is not None:
1324 if sentinel_is_explicit:
1325 if not the_sentinel_zero.server_default._is_monotonic_fn:
1326 raise exc.InvalidRequestError(
1327 f"Column {the_sentinel[0]} can't be a sentinel "
1328 "column "
1329 "because it uses an explicit server side default "
1330 "that's not the Identity() default."
1331 )
1332 else:
1333 default_characterization = (
1334 _SentinelDefaultCharacterization.MONOTONIC_FUNCTION
1335 )
1336 else:
1337 default_characterization = (
1338 _SentinelDefaultCharacterization.SERVERSIDE
1339 )
1341 if the_sentinel is None and self.primary_key:
1342 assert autoinc_col is None
1344 # determine for non-autoincrement pk if all elements are
1345 # client side
1346 for _pkc in self.primary_key:
1347 if (
1348 _pkc.server_default is not None
1349 and not _pkc.server_default._is_monotonic_fn
1350 ):
1351 break
1353 if (
1354 _pkc.default
1355 and not _pkc.default.is_callable
1356 and not _pkc.default._is_monotonic_fn
1357 ):
1358 break
1359 else:
1360 the_sentinel = tuple(self.primary_key)
1361 default_characterization = (
1362 _SentinelDefaultCharacterization.CLIENTSIDE
1363 )
1365 return _SentinelColumnCharacterization(
1366 the_sentinel,
1367 sentinel_is_explicit,
1368 sentinel_is_autoinc,
1369 default_characterization,
1370 )
1372 @property
1373 def autoincrement_column(self) -> Optional[Column[int]]:
1374 """Returns the :class:`.Column` object which currently represents
1375 the "auto increment" column, if any, else returns None.
1377 This is based on the rules for :class:`.Column` as defined by the
1378 :paramref:`.Column.autoincrement` parameter, which generally means the
1379 column within a single integer column primary key constraint that is
1380 not constrained by a foreign key. If the table does not have such
1381 a primary key constraint, then there's no "autoincrement" column.
1382 A :class:`.Table` may have only one column defined as the
1383 "autoincrement" column.
1385 .. versionadded:: 2.0.4
1387 .. seealso::
1389 :paramref:`.Column.autoincrement`
1391 """
1392 return self._autoincrement_column
1394 @property
1395 def key(self) -> str:
1396 """Return the 'key' for this :class:`_schema.Table`.
1398 This value is used as the dictionary key within the
1399 :attr:`_schema.MetaData.tables` collection. It is typically the same
1400 as that of :attr:`_schema.Table.name` for a table with no
1401 :attr:`_schema.Table.schema`
1402 set; otherwise it is typically of the form
1403 ``schemaname.tablename``.
1405 """
1406 return _get_table_key(self.name, self.schema)
1408 def __repr__(self) -> str:
1409 return "Table(%s)" % ", ".join(
1410 [repr(self.name)]
1411 + [repr(self.metadata)]
1412 + [repr(x) for x in self.columns]
1413 + ["%s=%s" % (k, repr(getattr(self, k))) for k in ["schema"]]
1414 )
1416 def __str__(self) -> str:
1417 return _get_table_key(self.description, self.schema)
1419 def add_is_dependent_on(self, table: Table) -> None:
1420 """Add a 'dependency' for this Table.
1422 This is another Table object which must be created
1423 first before this one can, or dropped after this one.
1425 Usually, dependencies between tables are determined via
1426 ForeignKey objects. However, for other situations that
1427 create dependencies outside of foreign keys (rules, inheriting),
1428 this method can manually establish such a link.
1430 """
1431 self._extra_dependencies.add(table)
1433 def _insert_col_impl(
1434 self,
1435 column: ColumnClause[Any],
1436 *,
1437 index: Optional[int] = None,
1438 replace_existing: bool = False,
1439 ) -> None:
1440 try:
1441 column._set_parent_with_dispatch(
1442 self,
1443 allow_replacements=replace_existing,
1444 all_names={c.name: c for c in self.c},
1445 index=index,
1446 )
1447 except exc.DuplicateColumnError as de:
1448 raise exc.DuplicateColumnError(
1449 f"{de.args[0]} Specify replace_existing=True to "
1450 "Table.append_column() or Table.insert_column() to replace an "
1451 "existing column."
1452 ) from de
1454 def insert_column(
1455 self,
1456 column: ColumnClause[Any],
1457 index: int,
1458 *,
1459 replace_existing: bool = False,
1460 ) -> None:
1461 """Insert a :class:`_schema.Column` to this :class:`_schema.Table` at
1462 a specific position.
1464 Behavior is identical to :meth:`.Table.append_column` except that
1465 the index position can be controlled using the
1466 :paramref:`.Table.insert_column.index`
1467 parameter.
1469 :param replace_existing:
1470 see :paramref:`.Table.append_column.replace_existing`
1471 :param index: integer index to insert the new column.
1473 .. versionadded:: 2.1
1475 """
1476 self._insert_col_impl(
1477 column, index=index, replace_existing=replace_existing
1478 )
1480 def append_column(
1481 self, column: ColumnClause[Any], *, replace_existing: bool = False
1482 ) -> None:
1483 """Append a :class:`_schema.Column` to this :class:`_schema.Table`.
1485 The "key" of the newly added :class:`_schema.Column`, i.e. the
1486 value of its ``.key`` attribute, will then be available
1487 in the ``.c`` collection of this :class:`_schema.Table`, and the
1488 column definition will be included in any CREATE TABLE, SELECT,
1489 UPDATE, etc. statements generated from this :class:`_schema.Table`
1490 construct.
1492 Note that this does **not** change the definition of the table
1493 as it exists within any underlying database, assuming that
1494 table has already been created in the database. Relational
1495 databases support the addition of columns to existing tables
1496 using the SQL ALTER command, which would need to be
1497 emitted for an already-existing table that doesn't contain
1498 the newly added column.
1500 :param replace_existing: When ``True``, allows replacing existing
1501 columns. When ``False``, the default, an warning will be raised
1502 if a column with the same ``.key`` already exists. A future
1503 version of sqlalchemy will instead rise a warning.
1505 .. versionadded:: 1.4.0
1507 .. seealso::
1509 :meth:`.Table.insert_column`
1511 """
1512 self._insert_col_impl(column, replace_existing=replace_existing)
1514 def append_constraint(self, constraint: Union[Index, Constraint]) -> None:
1515 """Append a :class:`_schema.Constraint` to this
1516 :class:`_schema.Table`.
1518 This has the effect of the constraint being included in any
1519 future CREATE TABLE statement, assuming specific DDL creation
1520 events have not been associated with the given
1521 :class:`_schema.Constraint` object.
1523 Note that this does **not** produce the constraint within the
1524 relational database automatically, for a table that already exists
1525 in the database. To add a constraint to an
1526 existing relational database table, the SQL ALTER command must
1527 be used. SQLAlchemy also provides the
1528 :class:`.AddConstraint` construct which can produce this SQL when
1529 invoked as an executable clause.
1531 """
1533 constraint._set_parent_with_dispatch(self)
1535 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
1536 metadata = parent
1537 assert isinstance(metadata, MetaData)
1538 metadata._add_table(self.name, self.schema, self)
1539 self.metadata = metadata
1541 def create(
1542 self,
1543 bind: _CreateDropBind,
1544 checkfirst: Union[bool, CheckFirst] = CheckFirst.TYPES,
1545 ) -> None:
1546 """Issue a ``CREATE`` statement for this
1547 :class:`_schema.Table`, using the given
1548 :class:`.Connection` or :class:`.Engine`
1549 for connectivity.
1551 .. seealso::
1553 :meth:`_schema.MetaData.create_all`.
1555 """
1557 # the default is to only check for schema objects
1558 bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
1560 def drop(
1561 self,
1562 bind: _CreateDropBind,
1563 checkfirst: Union[bool, CheckFirst] = CheckFirst.NONE,
1564 ) -> None:
1565 """Issue a ``DROP`` statement for this
1566 :class:`_schema.Table`, using the given
1567 :class:`.Connection` or :class:`.Engine` for connectivity.
1569 .. seealso::
1571 :meth:`_schema.MetaData.drop_all`.
1573 """
1574 bind._run_ddl_visitor(ddl.SchemaDropper, self, checkfirst=checkfirst)
1576 @util.deprecated(
1577 "1.4",
1578 ":meth:`_schema.Table.tometadata` is renamed to "
1579 ":meth:`_schema.Table.to_metadata`",
1580 )
1581 def tometadata(
1582 self,
1583 metadata: MetaData,
1584 schema: Union[str, Literal[SchemaConst.RETAIN_SCHEMA]] = RETAIN_SCHEMA,
1585 referred_schema_fn: Optional[
1586 Callable[
1587 [Table, Optional[str], ForeignKeyConstraint, Optional[str]],
1588 Optional[str],
1589 ]
1590 ] = None,
1591 name: Optional[str] = None,
1592 ) -> Table[_ColCC_co]:
1593 """Return a copy of this :class:`_schema.Table`
1594 associated with a different
1595 :class:`_schema.MetaData`.
1597 See :meth:`_schema.Table.to_metadata` for a full description.
1599 """
1600 return self.to_metadata(
1601 metadata,
1602 schema=schema,
1603 referred_schema_fn=referred_schema_fn,
1604 name=name,
1605 )
1607 def to_metadata(
1608 self,
1609 metadata: MetaData,
1610 schema: Union[str, Literal[SchemaConst.RETAIN_SCHEMA]] = RETAIN_SCHEMA,
1611 referred_schema_fn: Optional[
1612 Callable[
1613 [Table, Optional[str], ForeignKeyConstraint, Optional[str]],
1614 Optional[str],
1615 ]
1616 ] = None,
1617 name: Optional[str] = None,
1618 ) -> Table[_ColCC_co]:
1619 """Return a copy of this :class:`_schema.Table` associated with a
1620 different :class:`_schema.MetaData`.
1622 E.g.::
1624 m1 = MetaData()
1626 user = Table("user", m1, Column("id", Integer, primary_key=True))
1628 m2 = MetaData()
1629 user_copy = user.to_metadata(m2)
1631 .. versionchanged:: 1.4 The :meth:`_schema.Table.to_metadata` function
1632 was renamed from :meth:`_schema.Table.tometadata`.
1635 :param metadata: Target :class:`_schema.MetaData` object,
1636 into which the
1637 new :class:`_schema.Table` object will be created.
1639 :param schema: optional string name indicating the target schema.
1640 Defaults to the special symbol :attr:`.RETAIN_SCHEMA` which indicates
1641 that no change to the schema name should be made in the new
1642 :class:`_schema.Table`. If set to a string name, the new
1643 :class:`_schema.Table`
1644 will have this new name as the ``.schema``. If set to ``None``, the
1645 schema will be set to that of the schema set on the target
1646 :class:`_schema.MetaData`, which is typically ``None`` as well,
1647 unless
1648 set explicitly::
1650 m2 = MetaData(schema="newschema")
1652 # user_copy_one will have "newschema" as the schema name
1653 user_copy_one = user.to_metadata(m2, schema=None)
1655 m3 = MetaData() # schema defaults to None
1657 # user_copy_two will have None as the schema name
1658 user_copy_two = user.to_metadata(m3, schema=None)
1660 :param referred_schema_fn: optional callable which can be supplied
1661 in order to provide for the schema name that should be assigned
1662 to the referenced table of a :class:`_schema.ForeignKeyConstraint`.
1663 The callable accepts this parent :class:`_schema.Table`, the
1664 target schema that we are changing to, the
1665 :class:`_schema.ForeignKeyConstraint` object, and the existing
1666 "target schema" of that constraint. The function should return the
1667 string schema name that should be applied. To reset the schema
1668 to "none", return the symbol :data:`.BLANK_SCHEMA`. To effect no
1669 change, return ``None`` or :data:`.RETAIN_SCHEMA`.
1671 .. versionchanged:: 1.4.33 The ``referred_schema_fn`` function
1672 may return the :data:`.BLANK_SCHEMA` or :data:`.RETAIN_SCHEMA`
1673 symbols.
1675 E.g.::
1677 def referred_schema_fn(table, to_schema, constraint, referred_schema):
1678 if referred_schema == "base_tables":
1679 return referred_schema
1680 else:
1681 return to_schema
1684 new_table = table.to_metadata(
1685 m2, schema="alt_schema", referred_schema_fn=referred_schema_fn
1686 )
1688 :param name: optional string name indicating the target table name.
1689 If not specified or None, the table name is retained. This allows
1690 a :class:`_schema.Table` to be copied to the same
1691 :class:`_schema.MetaData` target
1692 with a new name.
1694 """ # noqa: E501
1695 if name is None:
1696 name = self.name
1698 actual_schema: Optional[str]
1700 if schema is RETAIN_SCHEMA:
1701 actual_schema = self.schema
1702 elif schema is None:
1703 actual_schema = metadata.schema
1704 else:
1705 actual_schema = schema
1706 key = _get_table_key(name, actual_schema)
1707 if key in metadata.tables:
1708 util.warn(
1709 f"Table '{self.description}' already exists within the given "
1710 "MetaData - not copying."
1711 )
1712 return metadata.tables[key]
1714 args = []
1715 for col in self.columns:
1716 args.append(col._copy(schema=actual_schema, _to_metadata=metadata))
1718 table: Table[_ColCC_co] = Table( # type: ignore[assignment]
1719 name,
1720 metadata,
1721 schema=actual_schema,
1722 comment=self.comment,
1723 *args,
1724 **self.kwargs,
1725 )
1727 if self._creator_ddl is not None:
1728 table._creator_ddl = self._creator_ddl.to_metadata(metadata, table)
1729 if self._dropper_ddl is not None:
1730 table._dropper_ddl = self._dropper_ddl.to_metadata(metadata, table)
1732 for const in self.constraints:
1733 if isinstance(const, ForeignKeyConstraint):
1734 referred_schema = const._referred_schema
1735 if referred_schema_fn:
1736 fk_constraint_schema = referred_schema_fn(
1737 self, actual_schema, const, referred_schema
1738 )
1739 else:
1740 fk_constraint_schema = (
1741 actual_schema
1742 if referred_schema == self.schema
1743 else None
1744 )
1745 table.append_constraint(
1746 const._copy(
1747 schema=fk_constraint_schema, target_table=table
1748 )
1749 )
1750 elif not const._type_bound:
1751 # skip unique constraints that would be generated
1752 # by the 'unique' flag on Column
1753 if const._column_flag:
1754 continue
1756 table.append_constraint(
1757 const._copy(schema=actual_schema, target_table=table)
1758 )
1759 for index in self.indexes:
1760 # skip indexes that would be generated
1761 # by the 'index' flag on Column
1762 if index._column_flag:
1763 continue
1764 Index(
1765 index.name,
1766 unique=index.unique,
1767 *[
1768 _copy_expression(expr, self, table)
1769 for expr in index._table_bound_expressions
1770 ],
1771 _table=table,
1772 **index.kwargs,
1773 **index._reflect_only_kwargs,
1774 )
1775 return self._schema_item_copy(table)
1778class Column(DialectKWArgs, SchemaItem, ColumnClause[_T], Named[_T]):
1779 """Represents a column in a database table."""
1781 __visit_name__ = "column"
1783 inherit_cache = True
1784 key: str
1786 server_default: Optional[FetchedValue]
1788 def __init__(
1789 self,
1790 __name_pos: Optional[
1791 Union[str, _CovariantTypeEngineArgument[_T], SchemaEventTarget]
1792 ] = None,
1793 __type_pos: Optional[
1794 Union[_CovariantTypeEngineArgument[_T], SchemaEventTarget]
1795 ] = None,
1796 /,
1797 *args: SchemaEventTarget,
1798 name: Optional[str] = None,
1799 type_: Optional[_CovariantTypeEngineArgument[_T]] = None,
1800 autoincrement: _AutoIncrementType = "auto",
1801 default: Optional[Any] = _NoArg.NO_ARG,
1802 insert_default: Optional[Any] = _NoArg.NO_ARG,
1803 doc: Optional[str] = None,
1804 key: Optional[str] = None,
1805 index: Optional[bool] = None,
1806 unique: Optional[bool] = None,
1807 info: Optional[_InfoType] = None,
1808 nullable: Optional[
1809 Union[bool, Literal[SchemaConst.NULL_UNSPECIFIED]]
1810 ] = SchemaConst.NULL_UNSPECIFIED,
1811 onupdate: Optional[Any] = None,
1812 primary_key: bool = False,
1813 server_default: Optional[_ServerDefaultArgument] = None,
1814 server_onupdate: Optional[_ServerOnUpdateArgument] = None,
1815 quote: Optional[bool] = None,
1816 system: bool = False,
1817 comment: Optional[str] = None,
1818 insert_sentinel: bool = False,
1819 _omit_from_statements: bool = False,
1820 _proxies: Optional[Any] = None,
1821 **dialect_kwargs: Any,
1822 ):
1823 r"""
1824 Construct a new ``Column`` object.
1826 :param name: The name of this column as represented in the database.
1827 This argument may be the first positional argument, or specified
1828 via keyword.
1830 Names which contain no upper case characters
1831 will be treated as case insensitive names, and will not be quoted
1832 unless they are a reserved word. Names with any number of upper
1833 case characters will be quoted and sent exactly. Note that this
1834 behavior applies even for databases which standardize upper
1835 case names as case insensitive such as Oracle Database.
1837 The name field may be omitted at construction time and applied
1838 later, at any time before the Column is associated with a
1839 :class:`_schema.Table`. This is to support convenient
1840 usage within the :mod:`~sqlalchemy.ext.declarative` extension.
1842 :param type\_: The column's type, indicated using an instance which
1843 subclasses :class:`~sqlalchemy.types.TypeEngine`. If no arguments
1844 are required for the type, the class of the type can be sent
1845 as well, e.g.::
1847 # use a type with arguments
1848 Column("data", String(50))
1850 # use no arguments
1851 Column("level", Integer)
1853 The ``type`` argument may be the second positional argument
1854 or specified by keyword.
1856 If the ``type`` is ``None`` or is omitted, it will first default to
1857 the special type :class:`.NullType`. If and when this
1858 :class:`_schema.Column` is made to refer to another column using
1859 :class:`_schema.ForeignKey` and/or
1860 :class:`_schema.ForeignKeyConstraint`, the type
1861 of the remote-referenced column will be copied to this column as
1862 well, at the moment that the foreign key is resolved against that
1863 remote :class:`_schema.Column` object.
1865 :param \*args: Additional positional arguments include various
1866 :class:`.SchemaItem` derived constructs which will be applied
1867 as options to the column. These include instances of
1868 :class:`.Constraint`, :class:`_schema.ForeignKey`,
1869 :class:`.ColumnDefault`, :class:`.Sequence`, :class:`.Computed`
1870 :class:`.Identity`. In some cases an
1871 equivalent keyword argument is available such as ``server_default``,
1872 ``default`` and ``unique``.
1874 :param autoincrement: Set up "auto increment" semantics for an
1875 **integer primary key column with no foreign key dependencies**
1876 (see later in this docstring for a more specific definition).
1877 This may influence the :term:`DDL` that will be emitted for
1878 this column during a table create, as well as how the column
1879 will be considered when INSERT statements are compiled and
1880 executed.
1882 The default value is the string ``"auto"``,
1883 which indicates that a single-column (i.e. non-composite) primary key
1884 that is of an INTEGER type with no other client-side or server-side
1885 default constructs indicated should receive auto increment semantics
1886 automatically. Other values include ``True`` (force this column to
1887 have auto-increment semantics for a :term:`composite primary key` as
1888 well), ``False`` (this column should never have auto-increment
1889 semantics), and the string ``"ignore_fk"`` (special-case for foreign
1890 key columns, see below).
1892 The term "auto increment semantics" refers both to the kind of DDL
1893 that will be emitted for the column within a CREATE TABLE statement,
1894 when methods such as :meth:`.MetaData.create_all` and
1895 :meth:`.Table.create` are invoked, as well as how the column will be
1896 considered when an INSERT statement is compiled and emitted to the
1897 database:
1899 * **DDL rendering** (i.e. :meth:`.MetaData.create_all`,
1900 :meth:`.Table.create`): When used on a :class:`.Column` that has
1901 no other
1902 default-generating construct associated with it (such as a
1903 :class:`.Sequence` or :class:`.Identity` construct), the parameter
1904 will imply that database-specific keywords such as PostgreSQL
1905 ``SERIAL``, MySQL ``AUTO_INCREMENT``, or ``IDENTITY`` on SQL Server
1906 should also be rendered. Not every database backend has an
1907 "implied" default generator available; for example the Oracle Database
1908 backends always needs an explicit construct such as
1909 :class:`.Identity` to be included with a :class:`.Column` in order
1910 for the DDL rendered to include auto-generating constructs to also
1911 be produced in the database.
1913 * **INSERT semantics** (i.e. when a :func:`_sql.insert` construct is
1914 compiled into a SQL string and is then executed on a database using
1915 :meth:`_engine.Connection.execute` or equivalent): A single-row
1916 INSERT statement will be known to produce a new integer primary key
1917 value automatically for this column, which will be accessible
1918 after the statement is invoked via the
1919 :attr:`.CursorResult.inserted_primary_key` attribute upon the
1920 :class:`_result.Result` object. This also applies towards use of the
1921 ORM when ORM-mapped objects are persisted to the database,
1922 indicating that a new integer primary key will be available to
1923 become part of the :term:`identity key` for that object. This
1924 behavior takes place regardless of what DDL constructs are
1925 associated with the :class:`_schema.Column` and is independent
1926 of the "DDL Rendering" behavior discussed in the previous note
1927 above.
1929 The parameter may be set to ``True`` to indicate that a column which
1930 is part of a composite (i.e. multi-column) primary key should
1931 have autoincrement semantics, though note that only one column
1932 within a primary key may have this setting. It can also
1933 be set to ``True`` to indicate autoincrement semantics on a
1934 column that has a client-side or server-side default configured,
1935 however note that not all dialects can accommodate all styles
1936 of default as an "autoincrement". It can also be
1937 set to ``False`` on a single-column primary key that has a
1938 datatype of INTEGER in order to disable auto increment semantics
1939 for that column.
1941 The setting *only* has an effect for columns which are:
1943 * Integer derived (i.e. INT, SMALLINT, BIGINT).
1945 * Part of the primary key
1947 * Not referring to another column via :class:`_schema.ForeignKey`,
1948 unless
1949 the value is specified as ``'ignore_fk'``::
1951 # turn on autoincrement for this column despite
1952 # the ForeignKey()
1953 Column(
1954 "id",
1955 ForeignKey("other.id"),
1956 primary_key=True,
1957 autoincrement="ignore_fk",
1958 )
1960 It is typically not desirable to have "autoincrement" enabled on a
1961 column that refers to another via foreign key, as such a column is
1962 required to refer to a value that originates from elsewhere.
1964 The setting has these effects on columns that meet the
1965 above criteria:
1967 * DDL issued for the column, if the column does not already include
1968 a default generating construct supported by the backend such as
1969 :class:`.Identity`, will include database-specific
1970 keywords intended to signify this column as an
1971 "autoincrement" column for specific backends. Behavior for
1972 primary SQLAlchemy dialects includes:
1974 * AUTO INCREMENT on MySQL and MariaDB
1975 * SERIAL on PostgreSQL
1976 * IDENTITY on MS-SQL - this occurs even without the
1977 :class:`.Identity` construct as the
1978 :paramref:`.Column.autoincrement` parameter pre-dates this
1979 construct.
1980 * SQLite - SQLite integer primary key columns are implicitly
1981 "auto incrementing" and no additional keywords are rendered;
1982 to render the special SQLite keyword ``AUTOINCREMENT``
1983 is not included as this is unnecessary and not recommended
1984 by the database vendor. See the section
1985 :ref:`sqlite_autoincrement` for more background.
1986 * Oracle Database - The Oracle Database dialects have no default "autoincrement"
1987 feature available at this time, instead the :class:`.Identity`
1988 construct is recommended to achieve this (the :class:`.Sequence`
1989 construct may also be used).
1990 * Third-party dialects - consult those dialects' documentation
1991 for details on their specific behaviors.
1993 * When a single-row :func:`_sql.insert` construct is compiled and
1994 executed, which does not set the :meth:`_sql.Insert.inline`
1995 modifier, newly generated primary key values for this column
1996 will be automatically retrieved upon statement execution
1997 using a method specific to the database driver in use:
1999 * MySQL, SQLite - calling upon ``cursor.lastrowid()``
2000 (see
2001 `https://www.python.org/dev/peps/pep-0249/#lastrowid
2002 <https://www.python.org/dev/peps/pep-0249/#lastrowid>`_)
2003 * PostgreSQL, SQL Server, Oracle Database - use RETURNING or an equivalent
2004 construct when rendering an INSERT statement, and then retrieving
2005 the newly generated primary key values after execution
2006 * PostgreSQL, Oracle Database for :class:`_schema.Table` objects that
2007 set :paramref:`_schema.Table.implicit_returning` to False -
2008 for a :class:`.Sequence` only, the :class:`.Sequence` is invoked
2009 explicitly before the INSERT statement takes place so that the
2010 newly generated primary key value is available to the client
2011 * SQL Server for :class:`_schema.Table` objects that
2012 set :paramref:`_schema.Table.implicit_returning` to False -
2013 the ``SELECT scope_identity()`` construct is used after the
2014 INSERT statement is invoked to retrieve the newly generated
2015 primary key value.
2016 * Third-party dialects - consult those dialects' documentation
2017 for details on their specific behaviors.
2019 * For multiple-row :func:`_sql.insert` constructs invoked with
2020 a list of parameters (i.e. "executemany" semantics), primary-key
2021 retrieving behaviors are generally disabled, however there may
2022 be special APIs that may be used to retrieve lists of new
2023 primary key values for an "executemany", such as the psycopg2
2024 "fast insertmany" feature. Such features are very new and
2025 may not yet be well covered in documentation.
2027 :param default: A scalar, Python callable, or
2028 :class:`_expression.ColumnElement` expression representing the
2029 *default value* for this column, which will be invoked upon insert
2030 if this column is otherwise not specified in the VALUES clause of
2031 the insert. This is a shortcut to using :class:`.ColumnDefault` as
2032 a positional argument; see that class for full detail on the
2033 structure of the argument.
2035 Contrast this argument to
2036 :paramref:`_schema.Column.server_default`
2037 which creates a default generator on the database side.
2039 .. seealso::
2041 :ref:`metadata_defaults_toplevel`
2043 :param insert_default: An alias of :paramref:`.Column.default`
2044 for compatibility with :func:`_orm.mapped_column`.
2046 .. versionadded:: 2.0.31
2048 :param doc: optional String that can be used by the ORM or similar
2049 to document attributes on the Python side. This attribute does
2050 **not** render SQL comments; use the
2051 :paramref:`_schema.Column.comment`
2052 parameter for this purpose.
2054 :param key: An optional string identifier which will identify this
2055 ``Column`` object on the :class:`_schema.Table`.
2056 When a key is provided,
2057 this is the only identifier referencing the ``Column`` within the
2058 application, including ORM attribute mapping; the ``name`` field
2059 is used only when rendering SQL.
2061 :param index: When ``True``, indicates that a :class:`_schema.Index`
2062 construct will be automatically generated for this
2063 :class:`_schema.Column`, which will result in a "CREATE INDEX"
2064 statement being emitted for the :class:`_schema.Table` when the DDL
2065 create operation is invoked.
2067 Using this flag is equivalent to making use of the
2068 :class:`_schema.Index` construct explicitly at the level of the
2069 :class:`_schema.Table` construct itself::
2071 Table(
2072 "some_table",
2073 metadata,
2074 Column("x", Integer),
2075 Index("ix_some_table_x", "x"),
2076 )
2078 To add the :paramref:`_schema.Index.unique` flag to the
2079 :class:`_schema.Index`, set both the
2080 :paramref:`_schema.Column.unique` and
2081 :paramref:`_schema.Column.index` flags to True simultaneously,
2082 which will have the effect of rendering the "CREATE UNIQUE INDEX"
2083 DDL instruction instead of "CREATE INDEX".
2085 The name of the index is generated using the
2086 :ref:`default naming convention <constraint_default_naming_convention>`
2087 which for the :class:`_schema.Index` construct is of the form
2088 ``ix_<tablename>_<columnname>``.
2090 As this flag is intended only as a convenience for the common case
2091 of adding a single-column, default configured index to a table
2092 definition, explicit use of the :class:`_schema.Index` construct
2093 should be preferred for most use cases, including composite indexes
2094 that encompass more than one column, indexes with SQL expressions
2095 or ordering, backend-specific index configuration options, and
2096 indexes that use a specific name.
2098 .. note:: the :attr:`_schema.Column.index` attribute on
2099 :class:`_schema.Column`
2100 **does not indicate** if this column is indexed or not, only
2101 if this flag was explicitly set here. To view indexes on
2102 a column, view the :attr:`_schema.Table.indexes` collection
2103 or use :meth:`_reflection.Inspector.get_indexes`.
2105 .. seealso::
2107 :ref:`schema_indexes`
2109 :ref:`constraint_naming_conventions`
2111 :paramref:`_schema.Column.unique`
2113 :param info: Optional data dictionary which will be populated into the
2114 :attr:`.SchemaItem.info` attribute of this object.
2116 :param nullable: When set to ``False``, will cause the "NOT NULL"
2117 phrase to be added when generating DDL for the column. When
2118 ``True``, will normally generate nothing (in SQL this defaults to
2119 "NULL"), except in some very specific backend-specific edge cases
2120 where "NULL" may render explicitly.
2121 Defaults to ``True`` unless :paramref:`_schema.Column.primary_key`
2122 is also ``True`` or the column specifies a :class:`_sql.Identity`,
2123 in which case it defaults to ``False``.
2124 This parameter is only used when issuing CREATE TABLE statements.
2126 .. note::
2128 When the column specifies a :class:`_sql.Identity` this
2129 parameter is in general ignored by the DDL compiler. The
2130 PostgreSQL database allows nullable identity column by
2131 setting this parameter to ``True`` explicitly.
2133 :param onupdate: A scalar, Python callable, or
2134 :class:`~sqlalchemy.sql.expression.ClauseElement` representing a
2135 default value to be applied to the column within UPDATE
2136 statements, which will be invoked upon update if this column is not
2137 present in the SET clause of the update. This is a shortcut to
2138 using :class:`.ColumnDefault` as a positional argument with
2139 ``for_update=True``.
2141 .. seealso::
2143 :ref:`metadata_defaults` - complete discussion of onupdate
2145 :param primary_key: If ``True``, marks this column as a primary key
2146 column. Multiple columns can have this flag set to specify
2147 composite primary keys. As an alternative, the primary key of a
2148 :class:`_schema.Table` can be specified via an explicit
2149 :class:`.PrimaryKeyConstraint` object.
2151 :param server_default: A :class:`.FetchedValue` instance, str, Unicode
2152 or :func:`~sqlalchemy.sql.expression.text` construct representing
2153 the DDL DEFAULT value for the column.
2155 String types will be emitted as-is, surrounded by single quotes::
2157 Column("x", Text, server_default="val")
2159 will render:
2161 .. sourcecode:: sql
2163 x TEXT DEFAULT 'val'
2165 A :func:`~sqlalchemy.sql.expression.text` expression will be
2166 rendered as-is, without quotes::
2168 Column("y", DateTime, server_default=text("NOW()"))
2170 will render:
2172 .. sourcecode:: sql
2174 y DATETIME DEFAULT NOW()
2176 Strings and text() will be converted into a
2177 :class:`.DefaultClause` object upon initialization.
2179 This parameter can also accept complex combinations of contextually
2180 valid SQLAlchemy expressions or constructs::
2182 from sqlalchemy import create_engine
2183 from sqlalchemy import Table, Column, MetaData, ARRAY, Text
2184 from sqlalchemy.dialects.postgresql import array
2186 engine = create_engine(
2187 "postgresql+psycopg2://scott:tiger@localhost/mydatabase"
2188 )
2189 metadata_obj = MetaData()
2190 tbl = Table(
2191 "foo",
2192 metadata_obj,
2193 Column(
2194 "bar", ARRAY(Text), server_default=array(["biz", "bang", "bash"])
2195 ),
2196 )
2197 metadata_obj.create_all(engine)
2199 The above results in a table created with the following SQL:
2201 .. sourcecode:: sql
2203 CREATE TABLE foo (
2204 bar TEXT[] DEFAULT ARRAY['biz', 'bang', 'bash']
2205 )
2207 Use :class:`.FetchedValue` to indicate that an already-existing
2208 column will generate a default value on the database side which
2209 will be available to SQLAlchemy for post-fetch after inserts. This
2210 construct does not specify any DDL and the implementation is left
2211 to the database, such as via a trigger.
2213 .. seealso::
2215 :ref:`server_defaults` - complete discussion of server side
2216 defaults
2218 :param server_onupdate: A :class:`.FetchedValue` instance
2219 representing a database-side default generation function,
2220 such as a trigger. This
2221 indicates to SQLAlchemy that a newly generated value will be
2222 available after updates. This construct does not actually
2223 implement any kind of generation function within the database,
2224 which instead must be specified separately.
2227 .. warning:: This directive **does not** currently produce MySQL's
2228 "ON UPDATE CURRENT_TIMESTAMP()" clause. See
2229 :ref:`mysql_timestamp_onupdate` for background on how to
2230 produce this clause.
2232 .. seealso::
2234 :ref:`triggered_columns`
2236 :param quote: Force quoting of this column's name on or off,
2237 corresponding to ``True`` or ``False``. When left at its default
2238 of ``None``, the column identifier will be quoted according to
2239 whether the name is case sensitive (identifiers with at least one
2240 upper case character are treated as case sensitive), or if it's a
2241 reserved word. This flag is only needed to force quoting of a
2242 reserved word which is not known by the SQLAlchemy dialect.
2244 :param unique: When ``True``, and the :paramref:`_schema.Column.index`
2245 parameter is left at its default value of ``False``,
2246 indicates that a :class:`_schema.UniqueConstraint`
2247 construct will be automatically generated for this
2248 :class:`_schema.Column`,
2249 which will result in a "UNIQUE CONSTRAINT" clause referring
2250 to this column being included
2251 in the ``CREATE TABLE`` statement emitted, when the DDL create
2252 operation for the :class:`_schema.Table` object is invoked.
2254 When this flag is ``True`` while the
2255 :paramref:`_schema.Column.index` parameter is simultaneously
2256 set to ``True``, the effect instead is that a
2257 :class:`_schema.Index` construct which includes the
2258 :paramref:`_schema.Index.unique` parameter set to ``True``
2259 is generated. See the documentation for
2260 :paramref:`_schema.Column.index` for additional detail.
2262 Using this flag is equivalent to making use of the
2263 :class:`_schema.UniqueConstraint` construct explicitly at the
2264 level of the :class:`_schema.Table` construct itself::
2266 Table("some_table", metadata, Column("x", Integer), UniqueConstraint("x"))
2268 The :paramref:`_schema.UniqueConstraint.name` parameter
2269 of the unique constraint object is left at its default value
2270 of ``None``; in the absence of a :ref:`naming convention <constraint_naming_conventions>`
2271 for the enclosing :class:`_schema.MetaData`, the UNIQUE CONSTRAINT
2272 construct will be emitted as unnamed, which typically invokes
2273 a database-specific naming convention to take place.
2275 As this flag is intended only as a convenience for the common case
2276 of adding a single-column, default configured unique constraint to a table
2277 definition, explicit use of the :class:`_schema.UniqueConstraint` construct
2278 should be preferred for most use cases, including composite constraints
2279 that encompass more than one column, backend-specific index configuration options, and
2280 constraints that use a specific name.
2282 .. note:: the :attr:`_schema.Column.unique` attribute on
2283 :class:`_schema.Column`
2284 **does not indicate** if this column has a unique constraint or
2285 not, only if this flag was explicitly set here. To view
2286 indexes and unique constraints that may involve this column,
2287 view the
2288 :attr:`_schema.Table.indexes` and/or
2289 :attr:`_schema.Table.constraints` collections or use
2290 :meth:`_reflection.Inspector.get_indexes` and/or
2291 :meth:`_reflection.Inspector.get_unique_constraints`
2293 .. seealso::
2295 :ref:`schema_unique_constraint`
2297 :ref:`constraint_naming_conventions`
2299 :paramref:`_schema.Column.index`
2301 :param system: When ``True``, indicates this is a "system" column,
2302 that is a column which is automatically made available by the
2303 database, and should not be included in the columns list for a
2304 ``CREATE TABLE`` statement.
2306 For more elaborate scenarios where columns should be
2307 conditionally rendered differently on different backends,
2308 consider custom compilation rules for :class:`.CreateColumn`.
2310 :param comment: Optional string that will render an SQL comment on
2311 table creation.
2313 :param insert_sentinel: Marks this :class:`_schema.Column` as an
2314 :term:`insert sentinel` used for optimizing the performance of the
2315 :term:`insertmanyvalues` feature for tables that don't
2316 otherwise have qualifying primary key configurations.
2318 .. versionadded:: 2.0.10
2320 .. seealso::
2322 :func:`_schema.insert_sentinel` - all in one helper for declaring
2323 sentinel columns
2325 :ref:`engine_insertmanyvalues`
2327 :ref:`engine_insertmanyvalues_sentinel_columns`
2330 """ # noqa: E501, RST201, RST202
2332 l_args = [__name_pos, __type_pos] + list(args)
2333 del args
2335 if isinstance(l_args[0], str):
2336 if name is not None:
2337 raise exc.ArgumentError(
2338 "May not pass name positionally and as a keyword."
2339 )
2340 name = l_args.pop(0) # type: ignore[assignment]
2341 elif l_args[0] is None:
2342 l_args.pop(0)
2343 if l_args:
2344 coltype = l_args[0]
2346 if hasattr(coltype, "_sqla_type"):
2347 if type_ is not None:
2348 raise exc.ArgumentError(
2349 "May not pass type_ positionally and as a keyword."
2350 )
2351 type_ = l_args.pop(0) # type: ignore[assignment]
2352 elif l_args[0] is None:
2353 l_args.pop(0)
2355 if name is not None:
2356 name = quoted_name(name, quote)
2357 elif quote is not None:
2358 raise exc.ArgumentError(
2359 "Explicit 'name' is required when sending 'quote' argument"
2360 )
2362 # name = None is expected to be an interim state
2363 # note this use case is legacy now that ORM declarative has a
2364 # dedicated "column" construct local to the ORM
2365 super().__init__(name, type_) # type: ignore[arg-type]
2367 self.key = key if key is not None else name # type: ignore[assignment]
2368 self.primary_key = primary_key
2369 self._insert_sentinel = insert_sentinel
2370 self._omit_from_statements = _omit_from_statements
2371 self._user_defined_nullable = udn = nullable
2372 if udn is not NULL_UNSPECIFIED:
2373 self.nullable = udn
2374 else:
2375 self.nullable = not primary_key
2377 # these default to None because .index and .unique is *not*
2378 # an informational flag about Column - there can still be an
2379 # Index or UniqueConstraint referring to this Column.
2380 self.index = index
2381 self.unique = unique
2383 self.system = system
2384 self.doc = doc
2385 self.autoincrement: _AutoIncrementType = autoincrement
2386 self.constraints = set()
2387 self.foreign_keys = set()
2388 self.comment = comment
2389 self.computed = None
2390 self.identity = None
2392 # check if this Column is proxying another column
2394 if _proxies is not None:
2395 self._proxies = _proxies
2396 else:
2397 # otherwise, add DDL-related events
2398 self._set_type(self.type)
2400 if insert_default is not _NoArg.NO_ARG:
2401 if default is not _NoArg.NO_ARG:
2402 raise exc.ArgumentError(
2403 "The 'default' and 'insert_default' parameters "
2404 "of Column are mutually exclusive"
2405 )
2406 resolved_default = insert_default
2407 elif default is not _NoArg.NO_ARG:
2408 resolved_default = default
2409 else:
2410 resolved_default = None
2412 if resolved_default is not None:
2413 if not isinstance(resolved_default, (ColumnDefault, Sequence)):
2414 resolved_default = ColumnDefault(resolved_default)
2416 self.default = resolved_default
2417 l_args.append(resolved_default)
2418 else:
2419 self.default = None
2421 if onupdate is not None:
2422 if not isinstance(onupdate, (ColumnDefault, Sequence)):
2423 onupdate = ColumnDefault(onupdate, for_update=True)
2425 self.onupdate = onupdate
2426 l_args.append(onupdate)
2427 else:
2428 self.onupdate = None
2430 if server_default is not None:
2431 if isinstance(server_default, FetchedValue):
2432 server_default = server_default._as_for_update(False)
2433 l_args.append(server_default)
2434 else:
2435 server_default = DefaultClause(server_default)
2436 l_args.append(server_default)
2437 self.server_default = server_default
2439 if server_onupdate is not None:
2440 if isinstance(server_onupdate, FetchedValue):
2441 server_onupdate = server_onupdate._as_for_update(True)
2442 l_args.append(server_onupdate)
2443 else:
2444 server_onupdate = DefaultClause(
2445 server_onupdate, for_update=True
2446 )
2447 l_args.append(server_onupdate)
2448 self.server_onupdate = server_onupdate
2450 self._init_items(*cast(_typing_Sequence[SchemaItem], l_args))
2452 util.set_creation_order(self)
2454 if info is not None:
2455 self.info = info
2457 self._extra_kwargs(**dialect_kwargs)
2459 table: Table
2461 constraints: Set[Constraint]
2463 foreign_keys: Set[ForeignKey]
2464 """A collection of all :class:`_schema.ForeignKey` marker objects
2465 associated with this :class:`_schema.Column`.
2467 Each object is a member of a :class:`_schema.Table`-wide
2468 :class:`_schema.ForeignKeyConstraint`.
2470 .. seealso::
2472 :attr:`_schema.Table.foreign_keys`
2474 """
2476 index: Optional[bool]
2477 """The value of the :paramref:`_schema.Column.index` parameter.
2479 Does not indicate if this :class:`_schema.Column` is actually indexed
2480 or not; use :attr:`_schema.Table.indexes`.
2482 .. seealso::
2484 :attr:`_schema.Table.indexes`
2485 """
2487 unique: Optional[bool]
2488 """The value of the :paramref:`_schema.Column.unique` parameter.
2490 Does not indicate if this :class:`_schema.Column` is actually subject to
2491 a unique constraint or not; use :attr:`_schema.Table.indexes` and
2492 :attr:`_schema.Table.constraints`.
2494 .. seealso::
2496 :attr:`_schema.Table.indexes`
2498 :attr:`_schema.Table.constraints`.
2500 """
2502 computed: Optional[Computed]
2504 identity: Optional[Identity]
2506 def _set_type(self, type_: TypeEngine[Any]) -> None:
2507 assert self.type._isnull or type_ is self.type
2509 self.type = type_
2510 if isinstance(self.type, SchemaEventTarget):
2511 self.type._set_parent_with_dispatch(self)
2512 for impl in self.type._variant_mapping.values():
2513 if isinstance(impl, SchemaEventTarget):
2514 impl._set_parent_with_dispatch(self)
2516 @HasMemoized.memoized_attribute
2517 def _default_description_tuple(self) -> _DefaultDescriptionTuple:
2518 """used by default.py -> _process_execute_defaults()"""
2520 return _DefaultDescriptionTuple._from_column_default(self.default)
2522 @HasMemoized.memoized_attribute
2523 def _onupdate_description_tuple(self) -> _DefaultDescriptionTuple:
2524 """used by default.py -> _process_execute_defaults()"""
2525 return _DefaultDescriptionTuple._from_column_default(self.onupdate)
2527 @util.memoized_property
2528 def _gen_static_annotations_cache_key(self) -> bool:
2529 """special attribute used by cache key gen, if true, we will
2530 use a static cache key for the annotations dictionary, else we
2531 will generate a new cache key for annotations each time.
2533 Added for #8790
2535 """
2536 return self.table is not None and self.table._is_table
2538 def _extra_kwargs(self, **kwargs: Any) -> None:
2539 self._validate_dialect_kwargs(kwargs)
2541 def __str__(self) -> str:
2542 if self.name is None:
2543 return "(no name)"
2544 elif self.table is not None:
2545 if self.table.named_with_column:
2546 return self.table.description + "." + self.description
2547 else:
2548 return self.description
2549 else:
2550 return self.description
2552 def references(self, column: Column[Any]) -> bool:
2553 """Return True if this Column references the given column via foreign
2554 key."""
2556 for fk in self.foreign_keys:
2557 if fk.column.proxy_set.intersection(column.proxy_set):
2558 return True
2559 else:
2560 return False
2562 def append_foreign_key(self, fk: ForeignKey) -> None:
2563 fk._set_parent_with_dispatch(self)
2565 def __repr__(self) -> str:
2566 kwarg = []
2567 if self.key != self.name:
2568 kwarg.append("key")
2569 if self.primary_key:
2570 kwarg.append("primary_key")
2571 if not self.nullable:
2572 kwarg.append("nullable")
2573 if self.onupdate:
2574 kwarg.append("onupdate")
2575 if self.default:
2576 kwarg.append("default")
2577 if self.server_default:
2578 kwarg.append("server_default")
2579 if self.comment:
2580 kwarg.append("comment")
2581 return "Column(%s)" % ", ".join(
2582 [repr(self.name)]
2583 + [repr(self.type)]
2584 + [repr(x) for x in self.foreign_keys if x is not None]
2585 + [repr(x) for x in self.constraints]
2586 + [
2587 (
2588 self.table is not None
2589 and "table=<%s>" % self.table.description
2590 or "table=None"
2591 )
2592 ]
2593 + ["%s=%s" % (k, repr(getattr(self, k))) for k in kwarg]
2594 )
2596 def _set_parent( # type: ignore[override]
2597 self,
2598 parent: SchemaEventTarget,
2599 *,
2600 all_names: Dict[str, Column[Any]],
2601 allow_replacements: bool,
2602 index: Optional[int] = None,
2603 **kw: Any,
2604 ) -> None:
2605 table = parent
2606 assert isinstance(table, Table)
2607 if not self.name:
2608 raise exc.ArgumentError(
2609 "Column must be constructed with a non-blank name or "
2610 "assign a non-blank .name before adding to a Table."
2611 )
2613 self._reset_memoizations()
2615 if self.key is None:
2616 self.key = self.name
2618 existing = getattr(self, "table", None)
2619 if existing is not None and existing is not table:
2620 raise exc.ArgumentError(
2621 f"Column object '{self.key}' already "
2622 f"assigned to Table '{existing.description}'"
2623 )
2625 extra_remove = None
2626 existing_col = None
2627 conflicts_on = ""
2629 if self.key in table._columns:
2630 existing_col = table._columns[self.key]
2631 if self.key == self.name:
2632 conflicts_on = "name"
2633 else:
2634 conflicts_on = "key"
2635 elif self.name in all_names:
2636 existing_col = all_names[self.name]
2637 extra_remove = {existing_col}
2638 conflicts_on = "name"
2640 if existing_col is not None:
2641 if existing_col is not self:
2642 if not allow_replacements:
2643 raise exc.DuplicateColumnError(
2644 f"A column with {conflicts_on} " f"""'{
2645 self.key if conflicts_on == 'key' else self.name
2646 }' """ f"is already present in table '{table.name}'."
2647 )
2648 for fk in existing_col.foreign_keys:
2649 table.foreign_keys.remove(fk)
2650 if fk.constraint in table.constraints:
2651 # this might have been removed
2652 # already, if it's a composite constraint
2653 # and more than one col being replaced
2654 table.constraints.remove(fk.constraint)
2656 if extra_remove and existing_col is not None and self.key == self.name:
2657 util.warn(
2658 f'Column with user-specified key "{existing_col.key}" is '
2659 "being replaced with "
2660 f'plain named column "{self.name}", '
2661 f'key "{existing_col.key}" is being removed. If this is a '
2662 "reflection operation, specify autoload_replace=False to "
2663 "prevent this replacement."
2664 )
2665 table._columns.replace(self, extra_remove=extra_remove, index=index)
2666 all_names[self.name] = self
2667 self.table = table
2669 if self._insert_sentinel:
2670 if self.table._sentinel_column is not None:
2671 raise exc.ArgumentError(
2672 "a Table may have only one explicit sentinel column"
2673 )
2674 self.table._sentinel_column = self
2676 if self.primary_key:
2677 table.primary_key._replace(self)
2678 elif self.key in table.primary_key:
2679 raise exc.ArgumentError(
2680 f"Trying to redefine primary-key column '{self.key}' as a "
2681 f"non-primary-key column on table '{table.fullname}'"
2682 )
2684 if self.index:
2685 if isinstance(self.index, str):
2686 raise exc.ArgumentError(
2687 "The 'index' keyword argument on Column is boolean only. "
2688 "To create indexes with a specific name, create an "
2689 "explicit Index object external to the Table."
2690 )
2691 table.append_constraint(
2692 Index(
2693 None, self.key, unique=bool(self.unique), _column_flag=True
2694 )
2695 )
2697 elif self.unique:
2698 if isinstance(self.unique, str):
2699 raise exc.ArgumentError(
2700 "The 'unique' keyword argument on Column is boolean "
2701 "only. To create unique constraints or indexes with a "
2702 "specific name, append an explicit UniqueConstraint to "
2703 "the Table's list of elements, or create an explicit "
2704 "Index object external to the Table."
2705 )
2706 table.append_constraint(
2707 UniqueConstraint(self.key, _column_flag=True)
2708 )
2710 self._setup_on_memoized_fks(lambda fk: fk._set_remote_table(table))
2712 if self.identity and (
2713 isinstance(self.default, Sequence)
2714 or isinstance(self.onupdate, Sequence)
2715 ):
2716 raise exc.ArgumentError(
2717 "An column cannot specify both Identity and Sequence."
2718 )
2720 def _setup_on_memoized_fks(self, fn: Callable[..., Any]) -> None:
2721 fk_keys = [
2722 ((self.table.key, self.key), False),
2723 ((self.table.key, self.name), True),
2724 ]
2725 for fk_key, link_to_name in fk_keys:
2726 if fk_key in self.table.metadata._fk_memos:
2727 for fk in self.table.metadata._fk_memos[fk_key]:
2728 if fk.link_to_name is link_to_name:
2729 fn(fk)
2731 def _on_table_attach(self, fn: Callable[..., Any]) -> None:
2732 if self.table is not None:
2733 fn(self, self.table)
2734 else:
2735 event.listen(self, "after_parent_attach", fn)
2737 @util.deprecated(
2738 "1.4",
2739 "The :meth:`_schema.Column.copy` method is deprecated "
2740 "and will be removed in a future release.",
2741 )
2742 def copy(self, **kw: Any) -> Column[Any]:
2743 return self._copy(**kw)
2745 def _copy(self, **kw: Any) -> Column[Any]:
2746 """Create a copy of this ``Column``, uninitialized.
2748 This is used in :meth:`_schema.Table.to_metadata` and by the ORM.
2750 """
2752 # Constraint objects plus non-constraint-bound ForeignKey objects
2753 args: List[SchemaItem] = [
2754 c._copy(**kw) for c in self.constraints if not c._type_bound
2755 ] + [c._copy(**kw) for c in self.foreign_keys if not c.constraint]
2757 # ticket #5276
2758 column_kwargs = {}
2759 for dialect_name in self.dialect_options:
2760 dialect_options = self.dialect_options[dialect_name]._non_defaults
2761 for (
2762 dialect_option_key,
2763 dialect_option_value,
2764 ) in dialect_options.items():
2765 column_kwargs[dialect_name + "_" + dialect_option_key] = (
2766 dialect_option_value
2767 )
2769 default = self.default
2770 if default is not None:
2771 default = default._copy()
2772 onupdate = self.onupdate
2773 if onupdate is not None:
2774 onupdate = onupdate._copy()
2775 server_default = self.server_default
2776 server_onupdate = self.server_onupdate
2777 if isinstance(server_default, (Computed, Identity)):
2778 args.append(server_default._copy(**kw))
2779 server_default = server_onupdate = None
2780 else:
2781 if server_default is not None:
2782 server_default = server_default._copy()
2783 if server_onupdate is not None:
2784 server_onupdate = server_onupdate._copy()
2786 type_ = self.type
2787 if isinstance(type_, SchemaEventTarget):
2788 type_ = type_.copy(**kw)
2790 c = self._constructor(
2791 name=self.name,
2792 type_=type_,
2793 key=self.key,
2794 primary_key=self.primary_key,
2795 unique=self.unique,
2796 system=self.system,
2797 # quote=self.quote, # disabled 2013-08-27 (commit 031ef080)
2798 index=self.index,
2799 autoincrement=self.autoincrement,
2800 default=default,
2801 server_default=server_default,
2802 onupdate=onupdate,
2803 server_onupdate=server_onupdate,
2804 doc=self.doc,
2805 comment=self.comment,
2806 _omit_from_statements=self._omit_from_statements,
2807 insert_sentinel=self._insert_sentinel,
2808 *args,
2809 **column_kwargs,
2810 )
2812 # copy the state of "nullable" exactly, to accommodate for
2813 # ORM flipping the .nullable flag directly
2814 c.nullable = self.nullable
2815 c._user_defined_nullable = self._user_defined_nullable
2817 return self._schema_item_copy(c)
2819 def _merge(
2820 self, other: Column[Any], *, omit_defaults: bool = False
2821 ) -> None:
2822 """merge the elements of this column onto "other"
2824 this is used by ORM pep-593 merge and will likely need a lot
2825 of fixes.
2828 """
2830 if self.primary_key:
2831 other.primary_key = True
2833 if self.autoincrement != "auto" and other.autoincrement == "auto":
2834 other.autoincrement = self.autoincrement
2836 if self.system:
2837 other.system = self.system
2839 if self.info:
2840 other.info.update(self.info)
2842 type_ = self.type
2843 if not type_._isnull and other.type._isnull:
2844 if isinstance(type_, SchemaEventTarget):
2845 type_ = type_.copy()
2847 other.type = type_
2849 if isinstance(type_, SchemaEventTarget):
2850 type_._set_parent_with_dispatch(other)
2852 for impl in type_._variant_mapping.values():
2853 if isinstance(impl, SchemaEventTarget):
2854 impl._set_parent_with_dispatch(other)
2856 if (
2857 self._user_defined_nullable is not NULL_UNSPECIFIED
2858 and other._user_defined_nullable is NULL_UNSPECIFIED
2859 ):
2860 other.nullable = self.nullable
2861 other._user_defined_nullable = self._user_defined_nullable
2863 if (
2864 not omit_defaults
2865 and self.default is not None
2866 and other.default is None
2867 ):
2868 new_default = self.default._copy()
2869 new_default._set_parent(other)
2871 if self.server_default and other.server_default is None:
2872 new_server_default = self.server_default
2873 if isinstance(new_server_default, FetchedValue):
2874 new_server_default = new_server_default._copy()
2875 new_server_default._set_parent(other)
2876 else:
2877 other.server_default = new_server_default
2879 if self.server_onupdate and other.server_onupdate is None:
2880 new_server_onupdate = self.server_onupdate
2881 new_server_onupdate = new_server_onupdate._copy()
2882 new_server_onupdate._set_parent(other)
2884 if self.onupdate and other.onupdate is None:
2885 new_onupdate = self.onupdate._copy()
2886 new_onupdate._set_parent(other)
2888 if self.index in (True, False) and other.index is None:
2889 other.index = self.index
2891 if self.unique in (True, False) and other.unique is None:
2892 other.unique = self.unique
2894 if self.doc and other.doc is None:
2895 other.doc = self.doc
2897 if self.comment and other.comment is None:
2898 other.comment = self.comment
2900 for const in self.constraints:
2901 if not const._type_bound:
2902 new_const = const._copy()
2903 new_const._set_parent(other)
2905 for fk in self.foreign_keys:
2906 if not fk.constraint:
2907 new_fk = fk._copy()
2908 new_fk._set_parent(other)
2910 def _make_proxy(
2911 self,
2912 selectable: FromClause,
2913 primary_key: ColumnSet,
2914 foreign_keys: Set[KeyedColumnElement[Any]],
2915 name: Optional[str] = None,
2916 key: Optional[str] = None,
2917 name_is_truncatable: bool = False,
2918 compound_select_cols: Optional[
2919 _typing_Sequence[ColumnElement[Any]]
2920 ] = None,
2921 **kw: Any,
2922 ) -> Tuple[str, ColumnClause[_T]]:
2923 """Create a *proxy* for this column.
2925 This is a copy of this ``Column`` referenced by a different parent
2926 (such as an alias or select statement). The column should
2927 be used only in select scenarios, as its full DDL/default
2928 information is not transferred.
2930 """
2932 fk = [
2933 ForeignKey(
2934 col if col is not None else f.target_tokens,
2935 _unresolvable=col is None,
2936 _constraint=f.constraint,
2937 )
2938 for f, col in [
2939 (fk, fk._resolve_column(raiseerr=False))
2940 for fk in self.foreign_keys
2941 ]
2942 ]
2944 if name is None and self.name is None:
2945 raise exc.InvalidRequestError(
2946 "Cannot initialize a sub-selectable"
2947 " with this Column object until its 'name' has "
2948 "been assigned."
2949 )
2950 try:
2951 c = self._constructor(
2952 (
2953 coercions.expect(
2954 roles.TruncatedLabelRole, name if name else self.name
2955 )
2956 if name_is_truncatable
2957 else (name or self.name)
2958 ),
2959 self.type,
2960 # this may actually be ._proxy_key when the key is incoming
2961 key=key if key else name if name else self.key,
2962 primary_key=self.primary_key,
2963 nullable=self.nullable,
2964 _proxies=(
2965 list(compound_select_cols)
2966 if compound_select_cols
2967 else [self]
2968 ),
2969 *fk,
2970 )
2971 except TypeError as err:
2972 raise TypeError(
2973 "Could not create a copy of this %r object. "
2974 "Ensure the class includes a _constructor() "
2975 "attribute or method which accepts the "
2976 "standard Column constructor arguments, or "
2977 "references the Column class itself." % self.__class__
2978 ) from err
2980 c.table = selectable
2981 c._propagate_attrs = selectable._propagate_attrs
2982 if selectable._is_clone_of is not None:
2983 c._is_clone_of = selectable._is_clone_of.columns.get(c.key)
2985 if self.primary_key:
2986 primary_key.add(c)
2988 if fk:
2989 foreign_keys.update(fk) # type: ignore[arg-type]
2991 return c.key, c
2994def insert_sentinel(
2995 name: Optional[str] = None,
2996 type_: Optional[_TypeEngineArgument[_T]] = None,
2997 *,
2998 default: Optional[Any] = None,
2999 omit_from_statements: bool = True,
3000) -> Column[Any]:
3001 """Provides a surrogate :class:`_schema.Column` that will act as a
3002 dedicated insert :term:`sentinel` column, allowing efficient bulk
3003 inserts with deterministic RETURNING sorting for tables that
3004 don't otherwise have qualifying primary key configurations.
3006 Adding this column to a :class:`.Table` object requires that a
3007 corresponding database table actually has this column present, so if adding
3008 it to an existing model, existing database tables would need to be migrated
3009 (e.g. using ALTER TABLE or similar) to include this column.
3011 For background on how this object is used, see the section
3012 :ref:`engine_insertmanyvalues_sentinel_columns` as part of the
3013 section :ref:`engine_insertmanyvalues`.
3015 The :class:`_schema.Column` returned will be a nullable integer column by
3016 default and make use of a sentinel-specific default generator used only in
3017 "insertmanyvalues" operations.
3019 .. seealso::
3021 :func:`_orm.orm_insert_sentinel`
3023 :paramref:`_schema.Column.insert_sentinel`
3025 :ref:`engine_insertmanyvalues`
3027 :ref:`engine_insertmanyvalues_sentinel_columns`
3030 .. versionadded:: 2.0.10
3032 """
3033 return Column(
3034 name=name,
3035 type_=type_api.INTEGERTYPE if type_ is None else type_,
3036 default=(
3037 default if default is not None else _InsertSentinelColumnDefault()
3038 ),
3039 _omit_from_statements=omit_from_statements,
3040 insert_sentinel=True,
3041 )
3044class ForeignKeyTarget(NamedTuple):
3045 """Represents the target of a :class:`_schema.ForeignKey` as three
3046 individual name tokens.
3048 This is the return value of :attr:`_schema.ForeignKey.target_tokens`, and
3049 may also be passed directly to the :class:`_schema.ForeignKey` constructor
3050 as well as to the :paramref:`_schema.ForeignKeyConstraint.refcolumns`
3051 parameter, as either a three-token tuple ``(schema, table_name,
3052 column_name)`` or a two-token tuple ``(table_name, column_name)``.
3054 The token form is the only representation of a foreign key target that is
3055 unambiguous in all cases; the dotted string form, available at
3056 :attr:`_schema.ForeignKey.target_fullname`, cannot represent a target
3057 where the table or column name itself contains a dot.
3059 .. versionadded:: 2.1
3061 .. seealso::
3063 :attr:`_schema.ForeignKey.target_tokens`
3065 """
3067 schema: Optional[str]
3068 """The schema name of the target, or ``None`` for the default schema."""
3070 table_name: str
3071 """The name of the target table."""
3073 column_name: Optional[str]
3074 """The key of the target column.
3076 This is ``None`` for a :class:`_schema.ForeignKey` that was given a table
3077 name only, in which case the local column's key is used to locate the
3078 target column.
3080 Note that this is the ``key`` of the target column rather than its name,
3081 unless :paramref:`_schema.ForeignKey.link_to_name` is ``True``.
3083 """
3085 def _tokens_no_dots(self) -> _typing_Sequence[str] | None:
3086 """return a sequence of non-None tokens to create a dotted name.
3088 returns None if either of table_name or column_name already have an
3089 embedded dot, making dotted name string impossible.
3091 """
3092 tokens = []
3093 for i, token in enumerate(
3094 [self.schema, self.table_name, self.column_name]
3095 ):
3096 if token is not None:
3097 # dot in the table name or column name; a dotted name
3098 # would be ambiguous
3099 if i != 0 and "." in token:
3100 return None
3101 tokens.append(token)
3102 elif i == 1:
3103 # table name is None; not renderable
3104 return None
3105 elif i == 2 and self.schema:
3106 # column name is None and there's a schema; a dotted
3107 # name would be ambiguous
3108 return None
3110 return tokens
3112 def _as_string(self) -> str:
3113 """Render these tokens as a single dotted string if possible,
3114 else raise :class:`.InvalidRequestError` if no unambiguous string form
3115 exists and a string is required.
3117 """
3119 tokens_no_dots = self._tokens_no_dots()
3121 if tokens_no_dots is None:
3123 if not self.table_name:
3124 reason = (
3125 "the target has no table name; a ForeignKey to a Column "
3126 "which is not yet associated with a Table has no names "
3127 "to render until that Column is attached"
3128 )
3129 elif "." in self.table_name:
3130 reason = (
3131 f"the table name {self.table_name!r} contains a dot, "
3132 f"which can't be told apart from the separator between "
3133 f"a schema name and a table name"
3134 )
3135 elif self.column_name is not None and "." in self.column_name:
3136 reason = (
3137 f"the column name {self.column_name!r} contains a dot, "
3138 f"which can't be told apart from the separator between "
3139 f"a table name and a column name"
3140 )
3141 elif self.column_name is None:
3142 reason = (
3143 f"a schema name {self.schema!r} is present with no "
3144 f"column name, so the schema name can't be told apart "
3145 f"from a table name"
3146 )
3147 else:
3148 # this is currently unreachable based on the current behavior
3149 # of tokens_no_dots
3150 assert False
3152 raise exc.InvalidRequestError(
3153 f"Can't render a single string representation for foreign "
3154 f"key target {self._description()}; {reason}. Use "
3155 f"ForeignKey.target_tokens to receive the schema, table and "
3156 f"column names individually."
3157 )
3159 return ".".join(tokens_no_dots)
3161 def _description(self) -> str:
3162 """Render a description of these tokens which never raises.
3164 The dotted string form is preferred when it's available, falling
3165 back to naming the tokens individually when it isn't.
3167 """
3169 tokens_no_dots = self._tokens_no_dots()
3170 if tokens_no_dots:
3171 return repr(".".join(tokens_no_dots))
3172 else:
3173 return (
3174 f"(schema={self.schema!r}, "
3175 f"table_name={self.table_name!r}, "
3176 f"column_name={self.column_name!r})"
3177 )
3179 @classmethod
3180 def _from_string(cls, spec: str) -> ForeignKeyTarget:
3181 """Parse a dotted string colspec into its component tokens.
3183 A FK between column 'bar' and table 'foo' can be specified as 'foo',
3184 'foo.bar', 'dbo.foo.bar', 'otherdb.dbo.foo.bar'. Once we have the
3185 column name and the table name, treat everything else as the schema
3186 name. Some databases (e.g. Sybase) support inter-database foreign
3187 keys. See tickets #1341 and -- indirectly related -- #594.
3189 This assumes that '.' will never appear *within* the table or column
3190 name; a target which does contain such a dot has no string form and
3191 must be given as a :class:`.ForeignKeyTarget` instead.
3193 """
3194 m = spec.split(".")
3195 if len(m) == 1:
3196 return cls(None, m[0], None)
3198 colname = m.pop()
3199 tname = m.pop()
3200 return cls(".".join(m) if m else None, tname, colname)
3202 @classmethod
3203 def _from_argument(
3204 cls, argument: _typing_Sequence[Any]
3205 ) -> ForeignKeyTarget:
3206 """Coerce a two or three token sequence passed by the user."""
3208 if len(argument) == 3:
3209 schema, table_name, column_name = argument
3210 elif len(argument) == 2:
3211 schema = None
3212 table_name, column_name = argument
3213 else:
3214 raise exc.ArgumentError(
3215 f"ForeignKey target given as a tuple must have two tokens "
3216 f"(table_name, column_name) or three tokens "
3217 f"(schema, table_name, column_name); got {len(argument)}"
3218 )
3220 if not table_name:
3221 raise exc.ArgumentError(
3222 "ForeignKey target table_name must be a non-empty string"
3223 )
3225 return cls(schema, table_name, column_name)
3228class ForeignKey(DialectKWArgs, SchemaItem):
3229 """Defines a dependency between two columns.
3231 ``ForeignKey`` is specified as an argument to a :class:`_schema.Column`
3232 object,
3233 e.g.::
3235 t = Table(
3236 "remote_table",
3237 metadata,
3238 Column("remote_id", ForeignKey("main_table.id")),
3239 )
3241 Note that ``ForeignKey`` is only a marker object that defines
3242 a dependency between two columns. The actual constraint
3243 is in all cases represented by the :class:`_schema.ForeignKeyConstraint`
3244 object. This object will be generated automatically when
3245 a ``ForeignKey`` is associated with a :class:`_schema.Column` which
3246 in turn is associated with a :class:`_schema.Table`. Conversely,
3247 when :class:`_schema.ForeignKeyConstraint` is applied to a
3248 :class:`_schema.Table`,
3249 ``ForeignKey`` markers are automatically generated to be
3250 present on each associated :class:`_schema.Column`, which are also
3251 associated with the constraint object.
3253 Note that you cannot define a "composite" foreign key constraint,
3254 that is a constraint between a grouping of multiple parent/child
3255 columns, using ``ForeignKey`` objects. To define this grouping,
3256 the :class:`_schema.ForeignKeyConstraint` object must be used, and applied
3257 to the :class:`_schema.Table`. The associated ``ForeignKey`` objects
3258 are created automatically.
3260 The ``ForeignKey`` objects associated with an individual
3261 :class:`_schema.Column`
3262 object are available in the `foreign_keys` collection
3263 of that column.
3265 The target of a ``ForeignKey`` is described by three names -- a schema
3266 name, a table name and a column name -- which are available at
3267 :attr:`_schema.ForeignKey.target_tokens` as a
3268 :class:`_schema.ForeignKeyTarget` named tuple::
3270 >>> fk = ForeignKey("main_table.id")
3271 >>> fk.target_tokens
3272 ForeignKeyTarget(schema=None, table_name='main_table', column_name='id')
3274 The same three names may be given to ``ForeignKey`` in place of the
3275 dotted string, as either a two or three token tuple. This is the only
3276 way to name a target whose table or column name itself contains a dot,
3277 as the dotted string form has no way of telling such a dot apart from
3278 the separator between two names::
3280 ForeignKey(("my.tbl", "id"))
3281 ForeignKey(("my_schema", "my.tbl", "id"))
3283 .. versionadded:: 2.1 :attr:`_schema.ForeignKey.target_tokens`, and the
3284 tuple form of the target.
3286 Where the target was given as a :class:`_schema.Column` rather than by
3287 name, that column is available at
3288 :attr:`_schema.ForeignKey.target_column`; this is distinct from
3289 :attr:`_schema.ForeignKey.column`, which is the *resolved* target however
3290 it was specified. :attr:`_schema.ForeignKey.target_table_key` gives the
3291 key under which the referenced :class:`_schema.Table` is, or would be,
3292 registered in :attr:`_schema.MetaData.tables`.
3294 Further examples of foreign key configuration are in
3295 :ref:`metadata_foreignkeys`.
3297 """ # noqa: E501
3299 __visit_name__ = "foreign_key"
3301 parent: Column[Any]
3303 _table_column: Optional[Column[Any]]
3304 """Storage for :attr:`.ForeignKey.target_column`; read that instead.
3306 ``None`` when the target was given by name, in which case
3307 :attr:`.ForeignKey._given_tokens` holds those names.
3309 """
3311 _given_tokens: Optional[ForeignKeyTarget]
3312 """The names of the target, when the target was given by name.
3314 ``None`` when the target was given as a :class:`.Column`, in which case
3315 the names are derived from that column on demand -- read
3316 :attr:`.ForeignKey.target_tokens` rather than this.
3318 """
3320 def __init__(
3321 self,
3322 column: _DDLColumnReferenceArgument,
3323 _constraint: Optional[ForeignKeyConstraint] = None,
3324 use_alter: bool = False,
3325 name: _ConstraintNameArgument = None,
3326 onupdate: Optional[str] = None,
3327 ondelete: Optional[str] = None,
3328 deferrable: Optional[bool] = None,
3329 initially: Optional[str] = None,
3330 link_to_name: bool = False,
3331 match: Optional[str] = None,
3332 info: Optional[_InfoType] = None,
3333 comment: Optional[str] = None,
3334 _unresolvable: bool = False,
3335 **dialect_kw: Any,
3336 ):
3337 r"""
3338 Construct a column-level FOREIGN KEY.
3340 The :class:`_schema.ForeignKey` object when constructed generates a
3341 :class:`_schema.ForeignKeyConstraint`
3342 which is associated with the parent
3343 :class:`_schema.Table` object's collection of constraints.
3345 :param column: A single target column for the key relationship. A
3346 :class:`_schema.Column` object or a column name as a string:
3347 ``tablename.columnkey`` or ``schema.tablename.columnkey``.
3348 ``columnkey`` is the ``key`` which has been assigned to the column
3349 (defaults to the column name itself), unless ``link_to_name`` is
3350 ``True`` in which case the rendered name of the column is used.
3352 The target may also be given as a tuple of individual name
3353 tokens, either ``(table_name, column_name)`` or
3354 ``(schema, table_name, column_name)``. This is the only form
3355 which can refer to a name that itself contains a dot, as the
3356 dotted string form has no way of telling such a dot apart from
3357 the separator between two names::
3359 ForeignKey(("my.tbl", "z"))
3361 .. versionadded:: 2.1 The tuple form.
3363 :param name: Optional string. An in-database name for the key if
3364 `constraint` is not provided.
3366 :param onupdate: Optional string. If set, emit ON UPDATE <value> when
3367 issuing DDL for this constraint. Typical values include CASCADE,
3368 DELETE and RESTRICT.
3370 .. seealso::
3372 :ref:`on_update_on_delete`
3374 :param ondelete: Optional string. If set, emit ON DELETE <value> when
3375 issuing DDL for this constraint. Typical values include CASCADE,
3376 SET NULL and RESTRICT. Some dialects may allow for additional
3377 syntaxes.
3379 .. seealso::
3381 :ref:`on_update_on_delete`
3383 :param deferrable: Optional bool. If set, emit DEFERRABLE or NOT
3384 DEFERRABLE when issuing DDL for this constraint.
3386 :param initially: Optional string. If set, emit INITIALLY <value> when
3387 issuing DDL for this constraint.
3389 :param link_to_name: if True, the string name given in ``column`` is
3390 the rendered name of the referenced column, not its locally
3391 assigned ``key``.
3393 :param use_alter: passed to the underlying
3394 :class:`_schema.ForeignKeyConstraint`
3395 to indicate the constraint should
3396 be generated/dropped externally from the CREATE TABLE/ DROP TABLE
3397 statement. See :paramref:`_schema.ForeignKeyConstraint.use_alter`
3398 for further description.
3400 .. seealso::
3402 :paramref:`_schema.ForeignKeyConstraint.use_alter`
3404 :ref:`use_alter`
3406 :param match: Optional string. If set, emit MATCH <value> when issuing
3407 DDL for this constraint. Typical values include SIMPLE, PARTIAL
3408 and FULL.
3410 :param info: Optional data dictionary which will be populated into the
3411 :attr:`.SchemaItem.info` attribute of this object.
3413 :param comment: Optional string that will render an SQL comment on
3414 foreign key constraint creation.
3416 .. versionadded:: 2.0
3418 :param \**dialect_kw: Additional keyword arguments are dialect
3419 specific, and passed in the form ``<dialectname>_<argname>``. The
3420 arguments are ultimately handled by a corresponding
3421 :class:`_schema.ForeignKeyConstraint`.
3422 See the documentation regarding
3423 an individual dialect at :ref:`dialect_toplevel` for detail on
3424 documented arguments.
3426 """
3428 self._unresolvable = _unresolvable
3430 self._table_column, self._given_tokens = self._parse_colspec_argument(
3431 column
3432 )
3434 # the linked ForeignKeyConstraint.
3435 # ForeignKey will create this when parent Column
3436 # is attached to a Table, *or* ForeignKeyConstraint
3437 # object passes itself in when creating ForeignKey
3438 # markers.
3439 self.constraint = _constraint
3441 # .parent is not Optional under normal use
3442 self.parent = None # type: ignore[assignment]
3444 self.use_alter = use_alter
3445 self.name = name
3446 self.onupdate = onupdate
3447 self.ondelete = ondelete
3448 self.deferrable = deferrable
3449 self.initially = initially
3450 self.link_to_name = link_to_name
3451 self.match = match
3452 self.comment = comment
3453 if info:
3454 self.info = info
3455 self._unvalidated_dialect_kw = dialect_kw
3457 def _parse_colspec_argument(
3458 self,
3459 argument: _DDLColumnReferenceArgument,
3460 ) -> Tuple[Optional[Column[Any]], ForeignKeyTarget]:
3461 """Coerce the ``column`` argument into the target
3462 :class:`.ForeignKeyTarget`, along with the target :class:`.Column`
3463 itself if that's how the target was given.
3465 """
3466 if isinstance(argument, tuple):
3467 return None, ForeignKeyTarget._from_argument(argument)
3469 _colspec = coercions.expect(roles.DDLReferredColumnRole, argument)
3471 if isinstance(_colspec, str):
3472 return None, ForeignKeyTarget._from_string(_colspec)
3474 assert isinstance(_colspec, ColumnClause)
3476 table = _colspec.table
3477 if not isinstance(table, (type(None), TableClause)):
3478 # a Column of some other FromClause, e.g. a subquery or a join;
3479 # the target of a ForeignKey has to be a Table (or a TableClause,
3480 # or a Column not yet associated with either)
3481 raise exc.ArgumentError(
3482 f"ForeignKey target Column {_colspec!r} is associated with "
3483 f"{table!r}, which is not a Table; a foreign key may only "
3484 f"target a Column of a Table"
3485 )
3486 elif table is None:
3487 # a Column not yet associated with a Table; this is the
3488 # declarative mixin + declared_attr case, where the target column
3489 # is attached to its Table after this ForeignKey is constructed
3490 return _colspec, ForeignKeyTarget(None, _colspec.key, None)
3491 else:
3492 return _colspec, ForeignKeyTarget(
3493 table.schema, table.name, _colspec.key
3494 )
3496 def __repr__(self) -> str:
3497 tokens = self.target_tokens
3498 tokens_no_dots = self.target_tokens._tokens_no_dots()
3500 if tokens_no_dots:
3501 return f"ForeignKey({'.'.join(tokens_no_dots)!r})"
3502 else:
3503 # no string form for this target, so name the tokens; this still
3504 # round trips, as ForeignKeyTarget is accepted by the constructor
3505 return f"ForeignKey({tokens!r})"
3507 @util.deprecated(
3508 "1.4",
3509 "The :meth:`_schema.ForeignKey.copy` method is deprecated "
3510 "and will be removed in a future release.",
3511 )
3512 def copy(self, *, schema: Optional[str] = None, **kw: Any) -> ForeignKey:
3513 return self._copy(schema=schema, **kw)
3515 def _copy(self, *, schema: Optional[str] = None, **kw: Any) -> ForeignKey:
3516 """Produce a copy of this :class:`_schema.ForeignKey` object.
3518 The new :class:`_schema.ForeignKey` will not be bound
3519 to any :class:`_schema.Column`.
3521 This method is usually used by the internal
3522 copy procedures of :class:`_schema.Column`, :class:`_schema.Table`,
3523 and :class:`_schema.MetaData`.
3525 :param schema: The returned :class:`_schema.ForeignKey` will
3526 reference the original table and column name, qualified
3527 by the given string schema name.
3529 """
3530 fk = ForeignKey(
3531 self._copy_tokens(schema=schema),
3532 use_alter=self.use_alter,
3533 name=self.name,
3534 onupdate=self.onupdate,
3535 ondelete=self.ondelete,
3536 deferrable=self.deferrable,
3537 initially=self.initially,
3538 link_to_name=self.link_to_name,
3539 match=self.match,
3540 comment=self.comment,
3541 **self._unvalidated_dialect_kw,
3542 )
3543 return self._schema_item_copy(fk)
3545 @property
3546 def target_tokens(self) -> ForeignKeyTarget:
3547 """Return the target of this :class:`_schema.ForeignKey` as three
3548 individual name tokens.
3550 The return value is a :class:`_schema.ForeignKeyTarget` named tuple of
3551 ``(schema, table_name, column_name)``. This is the representation
3552 SQLAlchemy itself computes against; unlike
3553 :attr:`_schema.ForeignKey.target_fullname` it is available in all
3554 cases, including when one of the names contains a dot::
3556 >>> from sqlalchemy import Column, ForeignKey, Integer, MetaData, Table
3557 >>> m = MetaData()
3558 >>> r = Table("my.tbl", m, Column("z", Integer, primary_key=True))
3559 >>> t = Table("t", m, Column("a", Integer, ForeignKey(r.c.z)))
3560 >>> list(t.c.a.foreign_keys)[0].target_tokens
3561 ForeignKeyTarget(schema=None, table_name='my.tbl', column_name='z')
3563 The tokens describe the target as it was specified, and are available
3564 whether or not the target :class:`_schema.Column` has been resolved;
3565 for the resolved column, see :attr:`_schema.ForeignKey.column`.
3567 .. versionadded:: 2.1
3569 .. seealso::
3571 :class:`_schema.ForeignKeyTarget`
3573 """ # noqa: E501
3575 col = self._table_column
3576 if col is None:
3577 assert self._given_tokens is not None
3578 return self._given_tokens
3580 # derived on demand rather than captured up front: a target Column
3581 # may gain both its name and its Table *after* this ForeignKey is
3582 # constructed, which is what a declarative mixin using declared_attr
3583 # does (see test_fk_mixin_self_referential_declared_attr)
3584 table = col.table
3585 if table is None:
3586 return ForeignKeyTarget(None, col.key, None)
3588 return ForeignKeyTarget(table.schema, table.name, col.key)
3590 @property
3591 def target_column(self) -> Optional[Column[Any]]:
3592 """Return the target :class:`_schema.Column` of this
3593 :class:`_schema.ForeignKey`, if the target was given as one.
3595 Returns ``None`` when the target was instead given by name, as a
3596 string or as :class:`_schema.ForeignKeyTarget`; in that case only
3597 :attr:`_schema.ForeignKey.target_tokens` describes the target until
3598 it is resolved.
3600 This is distinct from :attr:`_schema.ForeignKey.column`, which is the
3601 *resolved* target however it was specified, and which raises if the
3602 target can't be resolved. Note also that a target given as a
3603 :class:`_schema.Column` need not be associated with a
3604 :class:`_schema.Table` yet.
3606 .. versionadded:: 2.1
3608 .. seealso::
3610 :attr:`_schema.ForeignKey.target_tokens`
3612 :attr:`_schema.ForeignKey.column`
3614 """
3615 return self._table_column
3617 @property
3618 def _column_tokens(self) -> ForeignKeyTarget:
3619 """legacy private name for :attr:`.ForeignKey.target_tokens`"""
3621 return self.target_tokens
3623 @property
3624 def _colspec(self) -> Union[str, Column[Any]]:
3625 """Legacy accessor for the target in its pre-2.1 form, either the
3626 target :class:`.Column` or a dotted string.
3628 Raises :class:`.InvalidRequestError` for a target which has no dotted
3629 string form. Kept for the benefit of third party code which reads it;
3630 :attr:`.ForeignKey.target_tokens` is what SQLAlchemy itself uses.
3632 """
3633 col = self.target_column
3634 return self.target_tokens._as_string() if col is None else col
3636 def _copy_tokens(
3637 self,
3638 schema: Optional[
3639 Union[
3640 str,
3641 Literal[SchemaConst.RETAIN_SCHEMA, SchemaConst.BLANK_SCHEMA],
3642 ]
3643 ] = None,
3644 table_name: Optional[str] = None,
3645 _is_copy: bool = False,
3646 ) -> ForeignKeyTarget:
3647 """Return the tokens for a copy of this :class:`_schema.ForeignKey`,
3648 optionally rewriting the schema and/or table name.
3650 """
3652 col = self.target_column
3654 if _is_copy and col is not None and col.table is None:
3655 raise exc.InvalidRequestError(
3656 f"Can't copy ForeignKey object which refers to "
3657 f"non-table bound Column {col!r}"
3658 )
3660 tokens = self.target_tokens
3662 if schema not in (None, RETAIN_SCHEMA):
3663 return ForeignKeyTarget(
3664 None if schema is BLANK_SCHEMA else schema,
3665 table_name if table_name is not None else tokens.table_name,
3666 tokens.column_name,
3667 )
3668 elif table_name:
3669 return tokens._replace(table_name=table_name)
3670 else:
3671 return tokens
3673 def _get_colspec(self) -> str:
3674 """legacy method for :attr:`.ForeignKey.target_fullname`.
3676 Retained as third party code makes use of it; new code should use
3677 :attr:`.ForeignKey.target_tokens`.
3679 """
3681 return self.target_fullname
3683 @property
3684 def _referred_schema(self) -> Optional[str]:
3685 return self.target_tokens.schema
3687 @property
3688 def target_table_key(self) -> Optional[str]:
3689 """Return the key under which the target :class:`_schema.Table` is,
3690 or would be, registered in :attr:`_schema.MetaData.tables`.
3692 This is derived from :attr:`_schema.ForeignKey.target_tokens`, and is
3693 available whether or not the target table exists yet, making it
3694 usable while a :class:`_schema.Table` is still being constructed.
3696 Returns ``None`` in the one case where no key can be named: the
3697 target was given as a :class:`_schema.Column` which is not yet
3698 associated with a :class:`_schema.Table`.
3700 .. versionadded:: 2.1
3702 .. seealso::
3704 :attr:`_schema.ForeignKey.target_tokens`
3706 """
3707 col = self.target_column
3708 if col is not None and col.table is None:
3709 # target Column not yet associated with a Table, so there is no
3710 # key to report; the tokens name the column, not a table
3711 return None
3713 schema, tname, colname = self.target_tokens
3714 return _get_table_key(tname, schema)
3716 @property
3717 def target_fullname(self) -> str:
3718 """Return the target of this :class:`_schema.ForeignKey` as a single
3719 dotted string, e.g. ``"schema.tablename.columnname"``.
3721 This is usually the equivalent of the string-based
3722 ``"tablename.colname"`` argument first passed to the object's
3723 constructor.
3725 .. versionchanged:: 2.1 This attribute raises
3726 :class:`.InvalidRequestError` when the target table or column name
3727 itself contains a dot, as a dotted string can't distinguish such a
3728 name from the separator between names. The dotted form is now a
3729 legacy convenience; :attr:`_schema.ForeignKey.target_tokens` is the
3730 representation that is available in all cases, and is what
3731 SQLAlchemy itself makes use of.
3733 .. seealso::
3735 :attr:`_schema.ForeignKey.target_tokens`
3737 """
3738 return self.target_tokens._as_string()
3740 def references(self, table: Table) -> bool:
3741 """Return True if the given :class:`_schema.Table`
3742 is referenced by this
3743 :class:`_schema.ForeignKey`."""
3745 return table.corresponding_column(self.column) is not None
3747 def get_referent(self, table: FromClause) -> Optional[Column[Any]]:
3748 """Return the :class:`_schema.Column` in the given
3749 :class:`_schema.Table` (or any :class:`.FromClause`)
3750 referenced by this :class:`_schema.ForeignKey`.
3752 Returns None if this :class:`_schema.ForeignKey`
3753 does not reference the given
3754 :class:`_schema.Table`.
3756 """
3757 # our column is a Column, and any subquery etc. proxying us
3758 # would be doing so via another Column, so that's what would
3759 # be returned here
3760 return table.columns.corresponding_column(self.column) # type: ignore[return-value] # noqa: E501
3762 def _resolve_col_tokens(self) -> Tuple[Table, str, Optional[str]]:
3763 if self.parent is None:
3764 raise exc.InvalidRequestError(
3765 "this ForeignKey object does not yet have a "
3766 "parent Column associated with it."
3767 )
3769 elif self.parent.table is None:
3770 raise exc.InvalidRequestError(
3771 "this ForeignKey's parent column is not yet associated "
3772 "with a Table."
3773 )
3775 parenttable = self.parent.table
3777 if self._unresolvable:
3778 schema, tname, colname = self.target_tokens
3779 tablekey = _get_table_key(tname, schema)
3780 return parenttable, tablekey, colname
3782 # assertion
3783 # basically Column._make_proxy() sends the actual
3784 # target Column to the ForeignKey object, so the
3785 # string resolution here is never called.
3786 for c in self.parent.base_columns:
3787 if isinstance(c, Column):
3788 assert c.table is parenttable
3789 break
3790 else:
3791 assert False
3792 ######################
3794 schema, tname, colname = self.target_tokens
3796 if schema is None and parenttable.metadata.schema is not None:
3797 schema = parenttable.metadata.schema
3799 tablekey = _get_table_key(tname, schema)
3800 return parenttable, tablekey, colname
3802 def _link_to_col_by_colstring(
3803 self, parenttable: Table, table: Table, colname: Optional[str]
3804 ) -> Column[Any]:
3805 _column = None
3806 if colname is None:
3807 # colname is None in the case that ForeignKey argument
3808 # was specified as table name only, in which case we
3809 # match the column name to the same column on the
3810 # parent.
3811 # this use case wasn't working in later 1.x series
3812 # as it had no test coverage; fixed in 2.0
3813 parent = self.parent
3814 assert parent is not None
3815 key = parent.key
3816 _column = table.c.get(key, None)
3817 elif self.link_to_name:
3818 key = colname
3819 for c in table.c:
3820 if c.name == colname:
3821 _column = c
3822 else:
3823 key = colname
3824 _column = table.c.get(colname, None)
3826 if _column is None:
3827 raise exc.NoReferencedColumnError(
3828 "Could not initialize target column "
3829 f"for ForeignKey {self.target_tokens._description()} "
3830 f"on table '{parenttable.name}': "
3831 f"table '{table.name}' has no column named '{key}'",
3832 table.name,
3833 key,
3834 )
3836 return _column
3838 def _set_target_column(self, column: Column[Any]) -> None:
3839 assert self.parent is not None
3841 # propagate TypeEngine to parent if it didn't have one
3842 if self.parent.type._isnull:
3843 self.parent.type = column.type
3845 # super-edgy case, if other FKs point to our column,
3846 # they'd get the type propagated out also.
3848 def set_type(fk: ForeignKey) -> None:
3849 if fk.parent.type._isnull:
3850 fk.parent.type = column.type
3852 self.parent._setup_on_memoized_fks(set_type)
3854 self.column = column # type: ignore[misc]
3856 @util.ro_memoized_property
3857 def column(self) -> Column[Any]:
3858 """Return the target :class:`_schema.Column` referenced by this
3859 :class:`_schema.ForeignKey`.
3861 If no target column has been established, an exception
3862 is raised.
3864 """
3865 return self._resolve_column()
3867 @overload
3868 def _resolve_column(
3869 self, *, raiseerr: Literal[True] = ...
3870 ) -> Column[Any]: ...
3872 @overload
3873 def _resolve_column(
3874 self, *, raiseerr: bool = ...
3875 ) -> Optional[Column[Any]]: ...
3877 def _resolve_column(
3878 self, *, raiseerr: bool = True
3879 ) -> Optional[Column[Any]]:
3880 target_column = self.target_column
3882 if target_column is None:
3883 parenttable, tablekey, colname = self._resolve_col_tokens()
3885 if self._unresolvable or tablekey not in parenttable.metadata:
3886 if not raiseerr:
3887 return None
3888 raise exc.NoReferencedTableError(
3889 f"Foreign key associated with column "
3890 f"'{self.parent}' could not find "
3891 f"table '{tablekey}' with which to generate a "
3892 f"foreign key to target column '{colname}'",
3893 tablekey,
3894 )
3895 elif parenttable.key not in parenttable.metadata:
3896 if not raiseerr:
3897 return None
3898 raise exc.InvalidRequestError(
3899 f"Table {parenttable} is no longer associated with its "
3900 "parent MetaData"
3901 )
3902 else:
3903 table = parenttable.metadata.tables[tablekey]
3904 return self._link_to_col_by_colstring(
3905 parenttable, table, colname
3906 )
3908 else:
3909 assert target_column is not None
3910 return target_column
3912 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
3913 assert isinstance(parent, Column)
3915 if self.parent is not None and self.parent is not parent:
3916 raise exc.InvalidRequestError(
3917 "This ForeignKey already has a parent !"
3918 )
3919 self.parent = parent
3920 self.parent.foreign_keys.add(self)
3921 self.parent._on_table_attach(self._set_table)
3923 def _set_remote_table(self, table: Table) -> None:
3924 parenttable, _, colname = self._resolve_col_tokens()
3925 _column = self._link_to_col_by_colstring(parenttable, table, colname)
3926 self._set_target_column(_column)
3927 assert self.constraint is not None
3928 self.constraint._validate_dest_table(table)
3930 def _remove_from_metadata(self, metadata: MetaData) -> None:
3931 parenttable, table_key, colname = self._resolve_col_tokens()
3932 fk_key = (table_key, colname)
3934 if self in metadata._fk_memos[fk_key]:
3935 # TODO: no test coverage for self not in memos
3936 metadata._fk_memos[fk_key].remove(self)
3938 def _set_table(self, column: Column[Any], table: Table) -> None:
3939 # standalone ForeignKey - create ForeignKeyConstraint
3940 # on the hosting Table when attached to the Table.
3941 assert isinstance(table, Table)
3942 if self.constraint is None:
3943 self.constraint = ForeignKeyConstraint(
3944 [],
3945 [],
3946 use_alter=self.use_alter,
3947 name=self.name,
3948 onupdate=self.onupdate,
3949 ondelete=self.ondelete,
3950 deferrable=self.deferrable,
3951 initially=self.initially,
3952 match=self.match,
3953 comment=self.comment,
3954 **self._unvalidated_dialect_kw,
3955 )
3956 self.constraint._append_element(column, self)
3957 self.constraint._set_parent_with_dispatch(table)
3958 table.foreign_keys.add(self)
3959 # set up remote ".column" attribute, or a note to pick it
3960 # up when the other Table/Column shows up
3962 target_column = self.target_column
3963 if target_column is None:
3964 parenttable, table_key, colname = self._resolve_col_tokens()
3965 fk_key = (table_key, colname)
3966 if table_key in parenttable.metadata.tables:
3967 table = parenttable.metadata.tables[table_key]
3968 try:
3969 _column = self._link_to_col_by_colstring(
3970 parenttable, table, colname
3971 )
3972 except exc.NoReferencedColumnError:
3973 # this is OK, we'll try later
3974 pass
3975 else:
3976 self._set_target_column(_column)
3978 parenttable.metadata._fk_memos[fk_key].append(self)
3979 else:
3980 self._set_target_column(target_column)
3983if TYPE_CHECKING:
3985 def default_is_sequence(
3986 obj: Optional[DefaultGenerator],
3987 ) -> TypeGuard[Sequence]: ...
3989 def default_is_clause_element(
3990 obj: Optional[DefaultGenerator],
3991 ) -> TypeGuard[ColumnElementColumnDefault]: ...
3993 def default_is_scalar(
3994 obj: Optional[DefaultGenerator],
3995 ) -> TypeGuard[ScalarElementColumnDefault]: ...
3997else:
3998 default_is_sequence = operator.attrgetter("is_sequence")
4000 default_is_clause_element = operator.attrgetter("is_clause_element")
4002 default_is_scalar = operator.attrgetter("is_scalar")
4005class DefaultGenerator(Executable, SchemaItem):
4006 """Base class for column *default* values.
4008 This object is only present on column.default or column.onupdate.
4009 It's not valid as a server default.
4011 """
4013 __visit_name__ = "default_generator"
4015 _is_default_generator = True
4016 is_sequence = False
4017 is_identity = False
4018 is_server_default = False
4019 is_clause_element = False
4020 is_callable = False
4021 is_scalar = False
4022 has_arg = False
4023 is_sentinel = False
4024 _is_monotonic_fn = False
4025 column: Optional[Column[Any]]
4027 def __init__(self, for_update: bool = False) -> None:
4028 self.for_update = for_update
4030 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4031 if TYPE_CHECKING:
4032 assert isinstance(parent, Column)
4033 self.column = parent
4034 if self.for_update:
4035 self.column.onupdate = self
4036 else:
4037 self.column.default = self
4039 def _copy(self) -> DefaultGenerator:
4040 raise NotImplementedError()
4042 def _execute_on_connection(
4043 self,
4044 connection: Connection,
4045 distilled_params: _CoreMultiExecuteParams,
4046 execution_options: CoreExecuteOptionsParameter,
4047 ) -> Any:
4048 util.warn_deprecated(
4049 "Using the .execute() method to invoke a "
4050 "DefaultGenerator object is deprecated; please use "
4051 "the .scalar() method.",
4052 "2.0",
4053 )
4054 return self._execute_on_scalar(
4055 connection, distilled_params, execution_options
4056 )
4058 def _execute_on_scalar(
4059 self,
4060 connection: Connection,
4061 distilled_params: _CoreMultiExecuteParams,
4062 execution_options: CoreExecuteOptionsParameter,
4063 ) -> Any:
4064 return connection._execute_default(
4065 self, distilled_params, execution_options
4066 )
4069class ColumnDefault(DefaultGenerator, ABC):
4070 """A plain default value on a column.
4072 This could correspond to a constant, a callable function,
4073 or a SQL clause.
4075 :class:`.ColumnDefault` is generated automatically
4076 whenever the ``default``, ``onupdate`` arguments of
4077 :class:`_schema.Column` are used. A :class:`.ColumnDefault`
4078 can be passed positionally as well.
4080 For example, the following::
4082 Column("foo", Integer, default=50)
4084 Is equivalent to::
4086 Column("foo", Integer, ColumnDefault(50))
4088 """
4090 arg: Any
4092 _is_monotonic_fn = False
4094 @overload
4095 def __new__(
4096 cls, arg: Callable[..., Any], for_update: bool = ...
4097 ) -> CallableColumnDefault: ...
4099 @overload
4100 def __new__(
4101 cls, arg: ColumnElement[Any], for_update: bool = ...
4102 ) -> ColumnElementColumnDefault: ...
4104 # if I return ScalarElementColumnDefault here, which is what's actually
4105 # returned, mypy complains that
4106 # overloads overlap w/ incompatible return types.
4107 @overload
4108 def __new__(cls, arg: object, for_update: bool = ...) -> ColumnDefault: ...
4110 def __new__(
4111 cls, arg: Any = None, for_update: bool = False
4112 ) -> ColumnDefault:
4113 """Construct a new :class:`.ColumnDefault`.
4116 :param arg: argument representing the default value.
4117 May be one of the following:
4119 * a plain non-callable Python value, such as a
4120 string, integer, boolean, or other simple type.
4121 The default value will be used as is each time.
4122 * a SQL expression, that is one which derives from
4123 :class:`_expression.ColumnElement`. The SQL expression will
4124 be rendered into the INSERT or UPDATE statement,
4125 or in the case of a primary key column when
4126 RETURNING is not used may be
4127 pre-executed before an INSERT within a SELECT.
4128 * A Python callable. The function will be invoked for each
4129 new row subject to an INSERT or UPDATE.
4130 The callable must accept exactly
4131 zero or one positional arguments. The one-argument form
4132 will receive an instance of the :class:`.ExecutionContext`,
4133 which provides contextual information as to the current
4134 :class:`_engine.Connection` in use as well as the current
4135 statement and parameters.
4137 """
4139 if isinstance(arg, FetchedValue):
4140 raise exc.ArgumentError(
4141 "ColumnDefault may not be a server-side default type."
4142 )
4143 elif callable(arg):
4144 cls = CallableColumnDefault
4145 elif isinstance(arg, ClauseElement):
4146 cls = ColumnElementColumnDefault
4147 elif arg is not None:
4148 cls = ScalarElementColumnDefault
4150 return object.__new__(cls)
4152 def compare(self, other: ColumnDefault) -> bool:
4153 """Compare this :class:`.ColumnDefault` to another
4154 :class:`.ColumnDefault`.
4156 Returns True if both objects are of the same type, both apply to
4157 INSERT or both apply to UPDATE, and both represent the same default
4158 as determined by :meth:`.ColumnDefault.compare_argument`.
4160 .. versionadded:: 2.1.7
4162 """
4163 return (
4164 self.__class__ is other.__class__
4165 and self.for_update == other.for_update
4166 and self.compare_argument(other._given_arg)
4167 )
4169 def compare_argument(self, argument: Any) -> bool:
4170 """Compare the default represented by this :class:`.ColumnDefault`
4171 to the given argument, in the form that would be passed to
4172 :class:`.ColumnDefault` or :paramref:`_schema.Column.default`.
4174 Returns True for a plain Python value that is equal to this
4175 default's value, a SQL expression that compares as equal to this
4176 default's expression using :meth:`_expression.ClauseElement.compare`,
4177 or the same Python callable as this default's callable.
4179 .. versionadded:: 2.1.7
4181 """
4182 raise NotImplementedError()
4184 @property
4185 def _given_arg(self) -> Any:
4186 """the argument as it was passed to the constructor"""
4187 return self.arg
4189 def __repr__(self) -> str:
4190 return f"{self.__class__.__name__}({self.arg!r})"
4193class ScalarElementColumnDefault(ColumnDefault):
4194 """default generator for a fixed scalar Python value
4196 .. versionadded:: 2.0
4198 """
4200 is_scalar = True
4201 has_arg = True
4203 def __init__(self, arg: Any, for_update: bool = False) -> None:
4204 self.for_update = for_update
4205 self.arg = arg
4207 def _copy(self) -> ScalarElementColumnDefault:
4208 return ScalarElementColumnDefault(
4209 arg=self.arg, for_update=self.for_update
4210 )
4212 def compare_argument(self, argument: Any) -> bool:
4213 return self.arg is argument or (
4214 not callable(argument)
4215 and not isinstance(argument, ClauseElement)
4216 and bool(self.arg == argument)
4217 )
4220class _InsertSentinelColumnDefault(ColumnDefault):
4221 """Default generator that's specific to the use of a "sentinel" column
4222 when using the insertmanyvalues feature.
4224 This default is used as part of the :func:`_schema.insert_sentinel`
4225 construct.
4227 """
4229 is_sentinel = True
4230 for_update = False
4231 arg = None
4233 def __new__(cls) -> _InsertSentinelColumnDefault:
4234 return object.__new__(cls)
4236 def __init__(self) -> None:
4237 pass
4239 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4240 col = cast("Column[Any]", parent)
4241 if not col._insert_sentinel:
4242 raise exc.ArgumentError(
4243 "The _InsertSentinelColumnDefault may only be applied to a "
4244 "Column marked as insert_sentinel=True"
4245 )
4246 elif not col.nullable:
4247 raise exc.ArgumentError(
4248 "The _InsertSentinelColumnDefault may only be applied to a "
4249 "Column that is nullable"
4250 )
4252 super()._set_parent(parent, **kw)
4254 def _copy(self) -> _InsertSentinelColumnDefault:
4255 return _InsertSentinelColumnDefault()
4257 def compare_argument(self, argument: Any) -> bool:
4258 return argument is None
4261_SQLExprDefault = Union["ColumnElement[Any]", "TextClause"]
4264class ColumnElementColumnDefault(ColumnDefault):
4265 """default generator for a SQL expression
4267 .. versionadded:: 2.0
4269 """
4271 is_clause_element = True
4272 has_arg = True
4273 arg: _SQLExprDefault
4275 def __init__(
4276 self,
4277 arg: _SQLExprDefault,
4278 for_update: bool = False,
4279 ) -> None:
4280 self.for_update = for_update
4281 self.arg = arg
4283 def _copy(self) -> ColumnElementColumnDefault:
4284 return ColumnElementColumnDefault(
4285 arg=self.arg, for_update=self.for_update
4286 )
4288 def compare_argument(self, argument: Any) -> bool:
4289 return isinstance(argument, ClauseElement) and self.arg.compare(
4290 argument
4291 )
4293 @util.memoized_property
4294 @util.preload_module("sqlalchemy.sql.functions")
4295 def _is_monotonic_fn(self) -> bool:
4296 functions = util.preloaded.sql_functions
4297 return (
4298 isinstance(self.arg, functions.FunctionElement)
4299 and self.arg.monotonic
4300 )
4302 @util.memoized_property
4303 @util.preload_module("sqlalchemy.sql.sqltypes")
4304 def _arg_is_typed(self) -> bool:
4305 sqltypes = util.preloaded.sql_sqltypes
4307 return not isinstance(self.arg.type, sqltypes.NullType)
4310class _CallableColumnDefaultProtocol(Protocol):
4311 def __call__(self, context: ExecutionContext) -> Any: ...
4314class CallableColumnDefault(ColumnDefault):
4315 """default generator for a callable Python function
4317 .. versionadded:: 2.0
4319 """
4321 is_callable = True
4322 arg: _CallableColumnDefaultProtocol
4323 has_arg = True
4325 _fn: Union[_CallableColumnDefaultProtocol, Callable[[], Any]]
4327 def __init__(
4328 self,
4329 arg: Union[_CallableColumnDefaultProtocol, Callable[[], Any]],
4330 for_update: bool = False,
4331 ) -> None:
4332 self.for_update = for_update
4334 # the callable as given, before it's possibly wrapped to accept
4335 # a context
4336 self._fn = arg
4337 self.arg = self._maybe_wrap_callable(arg)
4339 def _copy(self) -> CallableColumnDefault:
4340 return CallableColumnDefault(arg=self._fn, for_update=self.for_update)
4342 def compare_argument(self, argument: Any) -> bool:
4343 return argument is self._fn
4345 @property
4346 def _given_arg(self) -> Any:
4347 return self._fn
4349 def _maybe_wrap_callable(
4350 self, fn: Union[_CallableColumnDefaultProtocol, Callable[[], Any]]
4351 ) -> _CallableColumnDefaultProtocol:
4352 """Wrap callables that don't accept a context.
4354 This is to allow easy compatibility with default callables
4355 that aren't specific to accepting of a context.
4357 """
4359 try:
4360 argspec = util.get_callable_argspec(fn, no_self=True)
4361 except TypeError:
4362 return util.wrap_callable(lambda ctx: fn(), fn) # type: ignore[call-arg, no-any-return, no-untyped-call] # noqa: E501
4364 defaulted = argspec[3] is not None and len(argspec[3]) or 0
4365 positionals = len(argspec[0]) - defaulted
4367 if positionals == 0:
4368 return util.wrap_callable(lambda ctx: fn(), fn) # type: ignore[call-arg, no-any-return, no-untyped-call] # noqa: E501
4370 elif positionals == 1:
4371 return fn # type: ignore[return-value]
4372 else:
4373 raise exc.ArgumentError(
4374 "ColumnDefault Python function takes zero or one "
4375 "positional arguments"
4376 )
4379class IdentityOptions(DialectKWArgs):
4380 """Defines options for a named database sequence or an identity column.
4382 .. seealso::
4384 :class:`.Sequence`
4386 """
4388 def __init__(
4389 self,
4390 start: Optional[int] = None,
4391 increment: Optional[int] = None,
4392 minvalue: Optional[int] = None,
4393 maxvalue: Optional[int] = None,
4394 nominvalue: Optional[bool] = None,
4395 nomaxvalue: Optional[bool] = None,
4396 cycle: Optional[bool] = None,
4397 cache: Optional[int] = None,
4398 order: Optional[bool] = None,
4399 **dialect_kw: Any,
4400 ) -> None:
4401 """Construct a :class:`.IdentityOptions` object.
4403 See the :class:`.Sequence` documentation for a complete description
4404 of the parameters.
4406 :param start: the starting index of the sequence.
4407 :param increment: the increment value of the sequence.
4408 :param minvalue: the minimum value of the sequence.
4409 :param maxvalue: the maximum value of the sequence.
4410 :param nominvalue: no minimum value of the sequence.
4411 :param nomaxvalue: no maximum value of the sequence.
4412 :param cycle: allows the sequence to wrap around when the maxvalue
4413 or minvalue has been reached.
4414 :param cache: optional integer value; number of future values in the
4415 sequence which are calculated in advance.
4416 :param order: optional boolean value; if ``True``, renders the
4417 ORDER keyword.
4419 .. deprecated:: 2.1 Use ``oracle_order`` instead.
4421 """
4422 self.start = start
4423 self.increment = increment
4424 self.minvalue = minvalue
4425 self.maxvalue = maxvalue
4426 self.nominvalue = nominvalue
4427 self.nomaxvalue = nomaxvalue
4428 self.cycle = cycle
4429 self.cache = cache
4430 if order is not None:
4431 if "oracle_order" in dialect_kw:
4432 raise exc.ArgumentError(
4433 "Cannot specify both 'order' and 'oracle_order'. "
4434 "Please use only 'oracle_order'."
4435 )
4436 dialect_kw["oracle_order"] = order
4437 self._validate_dialect_kwargs(dialect_kw)
4439 @property
4440 def _increment_is_negative(self) -> bool:
4441 return self.increment is not None and self.increment < 0
4443 @property
4444 def order(self) -> Optional[bool]:
4445 """Alias of the ``dialect_kwargs`` ``'oracle_order'``.
4447 .. deprecated:: 2.1 The 'order' attribute is deprecated.
4448 """
4449 value: Optional[bool] = self.dialect_kwargs.get("oracle_order")
4450 return value
4452 def _as_dict(self) -> Dict[str, Any]:
4453 return {
4454 k: v
4455 for k, v in {
4456 "start": self.start,
4457 "increment": self.increment,
4458 "minvalue": self.minvalue,
4459 "maxvalue": self.maxvalue,
4460 "nominvalue": self.nominvalue,
4461 "nomaxvalue": self.nomaxvalue,
4462 "cycle": self.cycle,
4463 "cache": self.cache,
4464 }.items()
4465 if v != None
4466 }
4469class Sequence(HasSchemaAttr, IdentityOptions, DefaultGenerator):
4470 """Represents a named database sequence.
4472 The :class:`.Sequence` object represents the name and configurational
4473 parameters of a database sequence. It also represents
4474 a construct that can be "executed" by a SQLAlchemy :class:`_engine.Engine`
4475 or :class:`_engine.Connection`,
4476 rendering the appropriate "next value" function
4477 for the target database and returning a result.
4479 The :class:`.Sequence` is typically associated with a primary key column::
4481 some_table = Table(
4482 "some_table",
4483 metadata,
4484 Column(
4485 "id",
4486 Integer,
4487 Sequence("some_table_seq", start=1),
4488 primary_key=True,
4489 ),
4490 )
4492 When CREATE TABLE is emitted for the above :class:`_schema.Table`, if the
4493 target platform supports sequences, a CREATE SEQUENCE statement will
4494 be emitted as well. For platforms that don't support sequences,
4495 the :class:`.Sequence` construct is ignored.
4497 .. seealso::
4499 :ref:`defaults_sequences`
4501 :class:`.CreateSequence`
4503 :class:`.DropSequence`
4505 """
4507 __visit_name__ = "sequence"
4509 is_sequence = True
4511 column: Optional[Column[Any]]
4512 data_type: Optional[TypeEngine[int]]
4514 metadata: Optional[MetaData]
4516 @util.deprecated_params(
4517 order=(
4518 "2.1",
4519 "This parameter is supported only by Oracle Database, "
4520 "use ``oracle_order`` instead.",
4521 )
4522 )
4523 def __init__(
4524 self,
4525 name: str,
4526 start: Optional[int] = None,
4527 increment: Optional[int] = None,
4528 minvalue: Optional[int] = None,
4529 maxvalue: Optional[int] = None,
4530 nominvalue: Optional[bool] = None,
4531 nomaxvalue: Optional[bool] = None,
4532 cycle: Optional[bool] = None,
4533 schema: Optional[Union[str, Literal[SchemaConst.BLANK_SCHEMA]]] = None,
4534 cache: Optional[int] = None,
4535 order: Optional[bool] = None,
4536 data_type: Optional[_TypeEngineArgument[int]] = None,
4537 optional: bool = False,
4538 quote: Optional[bool] = None,
4539 metadata: Optional[MetaData] = None,
4540 quote_schema: Optional[bool] = None,
4541 for_update: bool = False,
4542 **dialect_kw: Any,
4543 ) -> None:
4544 """Construct a :class:`.Sequence` object.
4546 :param name: the name of the sequence.
4548 :param start: the starting index of the sequence. This value is
4549 used when the CREATE SEQUENCE command is emitted to the database
4550 as the value of the "START WITH" clause. If ``None``, the
4551 clause is omitted, which on most platforms indicates a starting
4552 value of 1.
4554 .. versionchanged:: 2.0 The :paramref:`.Sequence.start` parameter
4555 is required in order to have DDL emit "START WITH". This is a
4556 reversal of a change made in version 1.4 which would implicitly
4557 render "START WITH 1" if the :paramref:`.Sequence.start` were
4558 not included. See :ref:`change_7211` for more detail.
4560 :param increment: the increment value of the sequence. This
4561 value is used when the CREATE SEQUENCE command is emitted to
4562 the database as the value of the "INCREMENT BY" clause. If ``None``,
4563 the clause is omitted, which on most platforms indicates an
4564 increment of 1.
4565 :param minvalue: the minimum value of the sequence. This
4566 value is used when the CREATE SEQUENCE command is emitted to
4567 the database as the value of the "MINVALUE" clause. If ``None``,
4568 the clause is omitted, which on most platforms indicates a
4569 minvalue of 1 and -2^63-1 for ascending and descending sequences,
4570 respectively.
4572 :param maxvalue: the maximum value of the sequence. This
4573 value is used when the CREATE SEQUENCE command is emitted to
4574 the database as the value of the "MAXVALUE" clause. If ``None``,
4575 the clause is omitted, which on most platforms indicates a
4576 maxvalue of 2^63-1 and -1 for ascending and descending sequences,
4577 respectively.
4579 :param nominvalue: no minimum value of the sequence. This
4580 value is used when the CREATE SEQUENCE command is emitted to
4581 the database as the value of the "NO MINVALUE" clause. If ``None``,
4582 the clause is omitted, which on most platforms indicates a
4583 minvalue of 1 and -2^63-1 for ascending and descending sequences,
4584 respectively.
4586 :param nomaxvalue: no maximum value of the sequence. This
4587 value is used when the CREATE SEQUENCE command is emitted to
4588 the database as the value of the "NO MAXVALUE" clause. If ``None``,
4589 the clause is omitted, which on most platforms indicates a
4590 maxvalue of 2^63-1 and -1 for ascending and descending sequences,
4591 respectively.
4593 :param cycle: allows the sequence to wrap around when the maxvalue
4594 or minvalue has been reached by an ascending or descending sequence
4595 respectively. This value is used when the CREATE SEQUENCE command
4596 is emitted to the database as the "CYCLE" clause. If the limit is
4597 reached, the next number generated will be the minvalue or maxvalue,
4598 respectively. If cycle=False (the default) any calls to nextval
4599 after the sequence has reached its maximum value will return an
4600 error.
4602 :param schema: optional schema name for the sequence, if located
4603 in a schema other than the default. The rules for selecting the
4604 schema name when a :class:`_schema.MetaData`
4605 is also present are the same
4606 as that of :paramref:`_schema.Table.schema`.
4608 :param cache: optional integer value; number of future values in the
4609 sequence which are calculated in advance. Renders the CACHE keyword
4610 understood by Oracle Database and PostgreSQL.
4612 :param order: optional boolean value; if ``True``, renders the
4613 ORDER keyword, understood by Oracle Database, indicating the sequence
4614 is definitively ordered. May be necessary to provide deterministic
4615 ordering using Oracle RAC.
4617 :param data_type: The type to be returned by the sequence, for
4618 dialects that allow us to choose between INTEGER, BIGINT, etc.
4619 (e.g., mssql).
4621 .. versionadded:: 1.4.0
4623 :param optional: boolean value, when ``True``, indicates that this
4624 :class:`.Sequence` object only needs to be explicitly generated
4625 on backends that don't provide another way to generate primary
4626 key identifiers. Currently, it essentially means, "don't create
4627 this sequence on the PostgreSQL backend, where the SERIAL keyword
4628 creates a sequence for us automatically".
4629 :param quote: boolean value, when ``True`` or ``False``, explicitly
4630 forces quoting of the :paramref:`_schema.Sequence.name` on or off.
4631 When left at its default of ``None``, normal quoting rules based
4632 on casing and reserved words take place.
4633 :param quote_schema: Set the quoting preferences for the ``schema``
4634 name.
4636 :param metadata: optional :class:`_schema.MetaData` object which this
4637 :class:`.Sequence` will be associated with. A :class:`.Sequence`
4638 that is associated with a :class:`_schema.MetaData`
4639 gains the following
4640 capabilities:
4642 * The :class:`.Sequence` will inherit the
4643 :paramref:`_schema.MetaData.schema`
4644 parameter specified to the target :class:`_schema.MetaData`, which
4645 affects the production of CREATE / DROP DDL, if any.
4647 * The :meth:`.Sequence.create` and :meth:`.Sequence.drop` methods
4648 automatically use the engine bound to the :class:`_schema.MetaData`
4649 object, if any.
4651 * The :meth:`_schema.MetaData.create_all` and
4652 :meth:`_schema.MetaData.drop_all`
4653 methods will emit CREATE / DROP for this :class:`.Sequence`,
4654 even if the :class:`.Sequence` is not associated with any
4655 :class:`_schema.Table` / :class:`_schema.Column`
4656 that's a member of this
4657 :class:`_schema.MetaData`.
4659 The above behaviors can only occur if the :class:`.Sequence` is
4660 explicitly associated with the :class:`_schema.MetaData`
4661 via this parameter.
4663 .. seealso::
4665 :ref:`sequence_metadata` - full discussion of the
4666 :paramref:`.Sequence.metadata` parameter.
4668 :param for_update: Indicates this :class:`.Sequence`, when associated
4669 with a :class:`_schema.Column`,
4670 should be invoked for UPDATE statements
4671 on that column's table, rather than for INSERT statements, when
4672 no value is otherwise present for that column in the statement.
4674 """
4675 DefaultGenerator.__init__(self, for_update=for_update)
4676 IdentityOptions.__init__(
4677 self,
4678 start=start,
4679 increment=increment,
4680 minvalue=minvalue,
4681 maxvalue=maxvalue,
4682 nominvalue=nominvalue,
4683 nomaxvalue=nomaxvalue,
4684 cycle=cycle,
4685 cache=cache,
4686 order=order,
4687 **dialect_kw,
4688 )
4689 self.column = None
4690 self.name = quoted_name(name, quote)
4691 self.optional = optional
4692 if schema is BLANK_SCHEMA:
4693 self.schema = schema = None
4694 elif metadata is not None and schema is None and metadata.schema:
4695 self.schema = schema = metadata.schema
4696 else:
4697 self.schema = quoted_name.construct(schema, quote_schema)
4698 self._key = _get_table_key(name, schema)
4699 if data_type is not None:
4700 self.data_type = to_instance(data_type)
4701 else:
4702 self.data_type = None
4704 if metadata:
4705 self._set_metadata(metadata)
4706 else:
4707 self.metadata = None
4709 @util.preload_module("sqlalchemy.sql.functions")
4710 def next_value(self) -> Function[int]:
4711 """Return a :class:`.next_value` function element
4712 which will render the appropriate increment function
4713 for this :class:`.Sequence` within any SQL expression.
4715 """
4716 return util.preloaded.sql_functions.func.next_value(self)
4718 def _copy(self) -> Sequence:
4719 return Sequence(
4720 name=self.name,
4721 schema=self.schema,
4722 data_type=self.data_type,
4723 optional=self.optional,
4724 metadata=None,
4725 for_update=self.for_update,
4726 **self._as_dict(),
4727 **self.dialect_kwargs,
4728 **self._reflect_only_kwargs,
4729 )
4731 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4732 assert isinstance(parent, Column)
4733 super()._set_parent(parent, **kw)
4734 parent._on_table_attach(self._set_table)
4736 def _set_table(self, column: Column[Any], table: Table) -> None:
4737 self._set_metadata(table.metadata)
4739 def _set_metadata(self, metadata: MetaData) -> None:
4740 self.metadata = metadata
4741 self.metadata._register_object(self)
4742 metadata._sequences[self._key] = self
4744 def create(
4745 self,
4746 bind: _CreateDropBind,
4747 checkfirst: Union[bool, CheckFirst] = CheckFirst.SEQUENCES,
4748 ) -> None:
4749 """Creates this sequence in the database."""
4751 bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
4753 def drop(
4754 self,
4755 bind: _CreateDropBind,
4756 checkfirst: Union[bool, CheckFirst] = CheckFirst.SEQUENCES,
4757 ) -> None:
4758 """Drops this sequence from the database."""
4760 bind._run_ddl_visitor(ddl.SchemaDropper, self, checkfirst=checkfirst)
4762 def _not_a_column_expr(self) -> NoReturn:
4763 raise exc.InvalidRequestError(
4764 f"This {self.__class__.__name__} cannot be used directly "
4765 "as a column expression. Use func.next_value(sequence) "
4766 "to produce a 'next value' function that's usable "
4767 "as a column element."
4768 )
4771@inspection._self_inspects
4772class FetchedValue(SchemaEventTarget):
4773 """A marker for a transparent database-side default.
4775 Use :class:`.FetchedValue` when the database is configured
4776 to provide some automatic default for a column.
4778 E.g.::
4780 Column("foo", Integer, FetchedValue())
4782 Would indicate that some trigger or default generator
4783 will create a new value for the ``foo`` column during an
4784 INSERT.
4786 .. seealso::
4788 :ref:`triggered_columns`
4790 """
4792 is_server_default = True
4793 reflected = False
4794 has_argument = False
4795 is_clause_element = False
4796 is_identity = False
4797 _is_monotonic_fn = False
4799 column: Optional[Column[Any]]
4801 def __init__(self, for_update: bool = False) -> None:
4802 self.for_update = for_update
4804 def _as_for_update(self, for_update: bool) -> FetchedValue:
4805 if for_update == self.for_update:
4806 return self
4807 else:
4808 return self._clone(for_update)
4810 def _copy(self) -> Self:
4811 return self._clone(self.for_update)
4813 def _clone(self, for_update: bool) -> Self:
4814 n = self.__class__.__new__(self.__class__)
4815 n.__dict__.update(self.__dict__)
4816 n.__dict__.pop("column", None)
4817 n.for_update = for_update
4818 return n
4820 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4821 column = parent
4822 assert isinstance(column, Column)
4823 self.column = column
4824 if self.for_update:
4825 self.column.server_onupdate = self
4826 else:
4827 self.column.server_default = self
4829 def __repr__(self) -> str:
4830 return util.generic_repr(self)
4833class DefaultClause(FetchedValue):
4834 """A DDL-specified DEFAULT column value.
4836 :class:`.DefaultClause` is a :class:`.FetchedValue`
4837 that also generates a "DEFAULT" clause when
4838 "CREATE TABLE" is emitted.
4840 :class:`.DefaultClause` is generated automatically
4841 whenever the ``server_default``, ``server_onupdate`` arguments of
4842 :class:`_schema.Column` are used. A :class:`.DefaultClause`
4843 can be passed positionally as well.
4845 For example, the following::
4847 Column("foo", Integer, server_default="50")
4849 Is equivalent to::
4851 Column("foo", Integer, DefaultClause("50"))
4853 """
4855 has_argument = True
4857 def __init__(
4858 self,
4859 arg: Union[str, ClauseElement, TextClause],
4860 for_update: bool = False,
4861 _reflected: bool = False,
4862 ) -> None:
4863 util.assert_arg_type(arg, (str, ClauseElement, TextClause), "arg")
4864 super().__init__(for_update)
4865 self.arg = arg
4866 self.reflected = _reflected
4868 @util.memoized_property
4869 @util.preload_module("sqlalchemy.sql.functions")
4870 def _is_monotonic_fn(self) -> bool:
4871 functions = util.preloaded.sql_functions
4872 return (
4873 isinstance(self.arg, functions.FunctionElement)
4874 and self.arg.monotonic
4875 )
4877 def __repr__(self) -> str:
4878 return "DefaultClause(%r, for_update=%r)" % (self.arg, self.for_update)
4881class Constraint(DialectKWArgs, HasConditionalDDL, SchemaItem):
4882 """A table-level SQL constraint.
4884 :class:`_schema.Constraint` serves as the base class for the series of
4885 constraint objects that can be associated with :class:`_schema.Table`
4886 objects, including :class:`_schema.PrimaryKeyConstraint`,
4887 :class:`_schema.ForeignKeyConstraint`
4888 :class:`_schema.UniqueConstraint`, and
4889 :class:`_schema.CheckConstraint`.
4891 """
4893 __visit_name__ = "constraint"
4895 _creation_order: int
4896 _column_flag: bool
4898 def __init__(
4899 self,
4900 name: _ConstraintNameArgument = None,
4901 deferrable: Optional[bool] = None,
4902 initially: Optional[str] = None,
4903 info: Optional[_InfoType] = None,
4904 comment: Optional[str] = None,
4905 _create_rule: Optional[Any] = None,
4906 _type_bound: bool = False,
4907 **dialect_kw: Any,
4908 ) -> None:
4909 r"""Create a SQL constraint.
4911 :param name:
4912 Optional, the in-database name of this ``Constraint``.
4914 :param deferrable:
4915 Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
4916 issuing DDL for this constraint.
4918 :param initially:
4919 Optional string. If set, emit INITIALLY <value> when issuing DDL
4920 for this constraint.
4922 :param info: Optional data dictionary which will be populated into the
4923 :attr:`.SchemaItem.info` attribute of this object.
4925 :param comment: Optional string that will render an SQL comment on
4926 foreign key constraint creation.
4928 .. versionadded:: 2.0
4930 :param \**dialect_kw: Additional keyword arguments are dialect
4931 specific, and passed in the form ``<dialectname>_<argname>``. See
4932 the documentation regarding an individual dialect at
4933 :ref:`dialect_toplevel` for detail on documented arguments.
4935 :param _create_rule:
4936 used internally by some datatypes that also create constraints.
4938 :param _type_bound:
4939 used internally to indicate that this constraint is associated with
4940 a specific datatype.
4942 """
4944 self.name = name
4945 self.deferrable = deferrable
4946 self.initially = initially
4947 if info:
4948 self.info = info
4949 self._create_rule = _create_rule
4950 self._type_bound = _type_bound
4951 util.set_creation_order(self)
4952 self._validate_dialect_kwargs(dialect_kw)
4953 self.comment = comment
4955 def _should_create_for_compiler(
4956 self, compiler: DDLCompiler, **kw: Any
4957 ) -> bool:
4958 if self._create_rule is not None and not self._create_rule(compiler):
4959 return False
4960 elif self._ddl_if is not None:
4961 return self._ddl_if._should_execute(
4962 ddl.CreateConstraint(self), self, None, compiler=compiler, **kw
4963 )
4964 else:
4965 return True
4967 @property
4968 def table(self) -> Table:
4969 try:
4970 if isinstance(self.parent, Table):
4971 return self.parent
4972 except AttributeError:
4973 pass
4974 raise exc.InvalidRequestError(
4975 "This constraint is not bound to a table. Did you "
4976 "mean to call table.append_constraint(constraint) ?"
4977 )
4979 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4980 assert isinstance(parent, (Table, Column))
4981 self.parent = parent
4982 parent.constraints.add(self)
4984 @util.deprecated(
4985 "1.4",
4986 "The :meth:`_schema.Constraint.copy` method is deprecated "
4987 "and will be removed in a future release.",
4988 )
4989 def copy(self, **kw: Any) -> Self:
4990 return self._copy(**kw)
4992 def _copy(self, **kw: Any) -> Self:
4993 raise NotImplementedError()
4996class ColumnCollectionMixin:
4997 """A :class:`_expression.ColumnCollection` of :class:`_schema.Column`
4998 objects.
5000 This collection represents the columns which are referred to by
5001 this object.
5003 """
5005 _columns: WriteableColumnCollection[str, Column[Any]]
5007 _column_collection_class: ClassVar[
5008 Type[WriteableColumnCollection[Any, Any]]
5009 ] = DedupeColumnCollection
5011 _allow_multiple_tables = False
5013 _pending_colargs: List[Optional[Union[str, Column[Any]]]]
5015 if TYPE_CHECKING:
5017 def _set_parent_with_dispatch(
5018 self, parent: SchemaEventTarget, **kw: Any
5019 ) -> None: ...
5021 def __init__(
5022 self,
5023 *columns: _DDLColumnArgument,
5024 _autoattach: bool = True,
5025 _column_flag: bool = False,
5026 _gather_expressions: Optional[
5027 List[Union[str, ColumnElement[Any]]]
5028 ] = None,
5029 ) -> None:
5030 self._column_flag = _column_flag
5031 self._columns = self._column_collection_class()
5033 processed_expressions: Optional[
5034 List[Union[ColumnElement[Any], str]]
5035 ] = _gather_expressions
5037 if processed_expressions is not None:
5039 # this is expected to be an empty list
5040 assert not processed_expressions
5042 self._pending_colargs = []
5043 for (
5044 expr,
5045 _,
5046 _,
5047 add_element,
5048 ) in coercions.expect_col_expression_collection(
5049 roles.DDLConstraintColumnRole, columns
5050 ):
5051 self._pending_colargs.append(add_element)
5052 processed_expressions.append(expr)
5053 else:
5054 self._pending_colargs = [
5055 coercions.expect(roles.DDLConstraintColumnRole, column)
5056 for column in columns
5057 ]
5059 if _autoattach and self._pending_colargs:
5060 self._check_attach()
5062 def _check_attach(self, evt: bool = False) -> None:
5063 col_objs = [c for c in self._pending_colargs if isinstance(c, Column)]
5065 cols_w_table = [c for c in col_objs if isinstance(c.table, Table)]
5067 cols_wo_table = set(col_objs).difference(cols_w_table)
5068 if cols_wo_table:
5069 # feature #3341 - place event listeners for Column objects
5070 # such that when all those cols are attached, we autoattach.
5071 assert not evt, "Should not reach here on event call"
5073 # issue #3411 - don't do the per-column auto-attach if some of the
5074 # columns are specified as strings.
5075 has_string_cols = {
5076 c for c in self._pending_colargs if c is not None
5077 }.difference(col_objs)
5078 if not has_string_cols:
5080 def _col_attached(column: Column[Any], table: Table) -> None:
5081 # this isinstance() corresponds with the
5082 # isinstance() above; only want to count Table-bound
5083 # columns
5084 if isinstance(table, Table):
5085 cols_wo_table.discard(column)
5086 if not cols_wo_table:
5087 self._check_attach(evt=True)
5089 self._cols_wo_table = cols_wo_table
5090 for col in cols_wo_table:
5091 col._on_table_attach(_col_attached)
5092 return
5094 columns = cols_w_table
5096 tables = {c.table for c in columns}
5097 if len(tables) == 1:
5098 self._set_parent_with_dispatch(tables.pop())
5099 elif len(tables) > 1 and not self._allow_multiple_tables:
5100 table = columns[0].table
5101 others = [c for c in columns[1:] if c.table is not table]
5102 if others:
5103 # black could not format this inline
5104 other_str = ", ".join("'%s'" % c for c in others)
5105 raise exc.ArgumentError(
5106 f"Column(s) {other_str} "
5107 f"are not part of table '{table.description}'."
5108 )
5110 @util.ro_memoized_property
5111 def columns(self) -> ReadOnlyColumnCollection[str, Column[Any]]:
5112 return self._columns.as_readonly()
5114 @util.ro_memoized_property
5115 def c(self) -> ReadOnlyColumnCollection[str, Column[Any]]:
5116 return self._columns.as_readonly()
5118 def _col_expressions(
5119 self, parent: Union[Table, Column[Any]]
5120 ) -> List[Optional[Column[Any]]]:
5121 if isinstance(parent, Column):
5122 result: List[Optional[Column[Any]]] = [
5123 c for c in self._pending_colargs if isinstance(c, Column)
5124 ]
5125 assert len(result) == len(self._pending_colargs)
5126 return result
5127 else:
5128 try:
5129 return [
5130 parent.c[col] if isinstance(col, str) else col
5131 for col in self._pending_colargs
5132 ]
5133 except KeyError as ke:
5134 raise exc.ConstraintColumnNotFoundError(
5135 f"Can't create {self.__class__.__name__} "
5136 f"on table '{parent.description}': no column "
5137 f"named '{ke.args[0]}' is present."
5138 ) from ke
5140 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
5141 assert isinstance(parent, (Table, Column))
5143 for col in self._col_expressions(parent):
5144 if col is not None:
5145 self._columns.add(col)
5148class ColumnCollectionConstraint(ColumnCollectionMixin, Constraint):
5149 """A constraint that proxies a ColumnCollection."""
5151 def __init__(
5152 self,
5153 *columns: _DDLColumnArgument,
5154 name: _ConstraintNameArgument = None,
5155 deferrable: Optional[bool] = None,
5156 initially: Optional[str] = None,
5157 info: Optional[_InfoType] = None,
5158 _autoattach: bool = True,
5159 _column_flag: bool = False,
5160 _gather_expressions: Optional[List[_DDLColumnArgument]] = None,
5161 **dialect_kw: Any,
5162 ) -> None:
5163 r"""
5164 :param \*columns:
5165 A sequence of column names or Column objects.
5167 :param name:
5168 Optional, the in-database name of this constraint.
5170 :param deferrable:
5171 Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
5172 issuing DDL for this constraint.
5174 :param initially:
5175 Optional string. If set, emit INITIALLY <value> when issuing DDL
5176 for this constraint.
5178 :param \**dialect_kw: other keyword arguments including
5179 dialect-specific arguments are propagated to the :class:`.Constraint`
5180 superclass.
5182 """
5183 Constraint.__init__(
5184 self,
5185 name=name,
5186 deferrable=deferrable,
5187 initially=initially,
5188 info=info,
5189 **dialect_kw,
5190 )
5191 ColumnCollectionMixin.__init__(
5192 self, *columns, _autoattach=_autoattach, _column_flag=_column_flag
5193 )
5195 columns: ReadOnlyColumnCollection[str, Column[Any]]
5196 """A :class:`_expression.ColumnCollection` representing the set of columns
5197 for this constraint.
5199 """
5201 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
5202 assert isinstance(parent, (Column, Table))
5203 Constraint._set_parent(self, parent)
5204 ColumnCollectionMixin._set_parent(self, parent)
5206 def __contains__(self, x: Any) -> bool:
5207 return x in self._columns
5209 @util.deprecated(
5210 "1.4",
5211 "The :meth:`_schema.ColumnCollectionConstraint.copy` method "
5212 "is deprecated and will be removed in a future release.",
5213 )
5214 def copy(
5215 self,
5216 *,
5217 target_table: Optional[Table] = None,
5218 **kw: Any,
5219 ) -> ColumnCollectionConstraint:
5220 return self._copy(target_table=target_table, **kw)
5222 def _copy(
5223 self,
5224 *,
5225 target_table: Optional[Table] = None,
5226 **kw: Any,
5227 ) -> ColumnCollectionConstraint:
5228 # ticket #5276
5229 constraint_kwargs = {}
5230 for dialect_name in self.dialect_options:
5231 dialect_options = self.dialect_options[dialect_name]._non_defaults
5232 for (
5233 dialect_option_key,
5234 dialect_option_value,
5235 ) in dialect_options.items():
5236 constraint_kwargs[dialect_name + "_" + dialect_option_key] = (
5237 dialect_option_value
5238 )
5240 assert isinstance(self.parent, Table)
5241 c = self.__class__(
5242 name=self.name,
5243 deferrable=self.deferrable,
5244 initially=self.initially,
5245 *[
5246 _copy_expression(expr, self.parent, target_table)
5247 for expr in self._columns
5248 ],
5249 comment=self.comment,
5250 **constraint_kwargs,
5251 )
5252 return self._schema_item_copy(c)
5254 def contains_column(self, col: Column[Any]) -> bool:
5255 """Return True if this constraint contains the given column.
5257 Note that this object also contains an attribute ``.columns``
5258 which is a :class:`_expression.ColumnCollection` of
5259 :class:`_schema.Column` objects.
5261 """
5263 return self._columns.contains_column(col)
5265 def __iter__(self) -> Iterator[Column[Any]]:
5266 return iter(self._columns)
5268 def __len__(self) -> int:
5269 return len(self._columns)
5272class CheckConstraint(ColumnCollectionConstraint):
5273 """A table- or column-level CHECK constraint.
5275 Can be included in the definition of a Table or Column.
5276 """
5278 _allow_multiple_tables = True
5280 __visit_name__ = "table_or_column_check_constraint"
5282 @_document_text_coercion(
5283 "sqltext",
5284 ":class:`.CheckConstraint`",
5285 ":paramref:`.CheckConstraint.sqltext`",
5286 )
5287 def __init__(
5288 self,
5289 sqltext: _TextCoercedExpressionArgument[Any],
5290 name: _ConstraintNameArgument = None,
5291 deferrable: Optional[bool] = None,
5292 initially: Optional[str] = None,
5293 table: Optional[Table] = None,
5294 info: Optional[_InfoType] = None,
5295 _create_rule: Optional[Any] = None,
5296 _autoattach: bool = True,
5297 _type_bound: bool = False,
5298 **dialect_kw: Any,
5299 ) -> None:
5300 r"""Construct a CHECK constraint.
5302 :param sqltext:
5303 A string containing the constraint definition, which will be used
5304 verbatim, or a SQL expression construct. If given as a string,
5305 the object is converted to a :func:`_expression.text` object.
5306 If the textual
5307 string includes a colon character, escape this using a backslash::
5309 CheckConstraint(r"foo ~ E'a(?\:b|c)d")
5311 :param name:
5312 Optional, the in-database name of the constraint.
5314 :param deferrable:
5315 Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
5316 issuing DDL for this constraint.
5318 :param initially:
5319 Optional string. If set, emit INITIALLY <value> when issuing DDL
5320 for this constraint.
5322 :param info: Optional data dictionary which will be populated into the
5323 :attr:`.SchemaItem.info` attribute of this object.
5325 """
5327 self.sqltext = coercions.expect(roles.DDLExpressionRole, sqltext)
5328 columns: List[Column[Any]] = []
5329 visitors.traverse(self.sqltext, {}, {"column": columns.append})
5331 super().__init__(
5332 name=name,
5333 deferrable=deferrable,
5334 initially=initially,
5335 _create_rule=_create_rule,
5336 info=info,
5337 _type_bound=_type_bound,
5338 _autoattach=_autoattach,
5339 *columns,
5340 **dialect_kw,
5341 )
5342 if table is not None:
5343 self._set_parent_with_dispatch(table)
5345 @property
5346 def is_column_level(self) -> bool:
5347 return not isinstance(self.parent, Table)
5349 @util.deprecated(
5350 "1.4",
5351 "The :meth:`_schema.CheckConstraint.copy` method is deprecated "
5352 "and will be removed in a future release.",
5353 )
5354 def copy(
5355 self, *, target_table: Optional[Table] = None, **kw: Any
5356 ) -> CheckConstraint:
5357 return self._copy(target_table=target_table, **kw)
5359 def _copy(
5360 self, *, target_table: Optional[Table] = None, **kw: Any
5361 ) -> CheckConstraint:
5362 if target_table is not None:
5363 # note that target_table is None for the copy process of
5364 # a column-bound CheckConstraint, so this path is not reached
5365 # in that case.
5366 sqltext = _copy_expression(self.sqltext, self.table, target_table)
5367 else:
5368 sqltext = self.sqltext
5369 c = CheckConstraint(
5370 sqltext,
5371 name=self.name,
5372 initially=self.initially,
5373 deferrable=self.deferrable,
5374 _create_rule=self._create_rule,
5375 table=target_table,
5376 comment=self.comment,
5377 _autoattach=False,
5378 _type_bound=self._type_bound,
5379 **self.dialect_kwargs,
5380 )
5381 return self._schema_item_copy(c)
5384class ForeignKeyConstraint(ColumnCollectionConstraint):
5385 """A table-level FOREIGN KEY constraint.
5387 Defines a single column or composite FOREIGN KEY ... REFERENCES
5388 constraint. For a no-frills, single column foreign key, adding a
5389 :class:`_schema.ForeignKey` to the definition of a :class:`_schema.Column`
5390 is a
5391 shorthand equivalent for an unnamed, single column
5392 :class:`_schema.ForeignKeyConstraint`.
5394 Examples of foreign key configuration are in :ref:`metadata_foreignkeys`.
5396 """
5398 __visit_name__ = "foreign_key_constraint"
5400 # a FOREIGN KEY may name the same local column more than once, e.g.
5401 # FOREIGN KEY (a, a) REFERENCES r (b, c). the collection is therefore
5402 # positional and parallel to self.elements, not deduplicating.
5403 _column_collection_class = WriteableColumnCollection
5405 def __init__(
5406 self,
5407 columns: _typing_Sequence[_DDLColumnArgument],
5408 refcolumns: _typing_Sequence[_DDLColumnReferenceArgument],
5409 name: _ConstraintNameArgument = None,
5410 onupdate: Optional[str] = None,
5411 ondelete: Optional[str] = None,
5412 deferrable: Optional[bool] = None,
5413 initially: Optional[str] = None,
5414 use_alter: bool = False,
5415 link_to_name: bool = False,
5416 match: Optional[str] = None,
5417 table: Optional[Table] = None,
5418 info: Optional[_InfoType] = None,
5419 comment: Optional[str] = None,
5420 **dialect_kw: Any,
5421 ) -> None:
5422 r"""Construct a composite-capable FOREIGN KEY.
5424 :param columns: A sequence of local column names. The named columns
5425 must be defined and present in the parent Table. The names should
5426 match the ``key`` given to each column (defaults to the name) unless
5427 ``link_to_name`` is True. The same column may be named more than
5428 once, e.g. ``FOREIGN KEY (a, a) REFERENCES r (b, c)``, which
5429 constrains the referenced row so that its ``b`` and ``c`` values
5430 are equal.
5432 .. versionchanged:: 2.1 The same local column may be named more
5433 than once.
5435 :param refcolumns: A sequence of foreign column names or Column
5436 objects. The columns must all be located within the same Table.
5437 The number of entries must match that of
5438 :paramref:`_schema.ForeignKeyConstraint.columns`. Each entry
5439 accepts the same forms as :paramref:`_schema.ForeignKey.column`,
5440 including the ``(table_name, column_name)`` and
5441 ``(schema, table_name, column_name)`` tuple forms.
5443 .. versionchanged:: 2.1 Individual entries may be given as a
5444 tuple of name tokens.
5446 :param name: Optional, the in-database name of the key.
5448 :param onupdate: Optional string. If set, emit ON UPDATE <value> when
5449 issuing DDL for this constraint. Typical values include CASCADE,
5450 DELETE and RESTRICT.
5452 .. seealso::
5454 :ref:`on_update_on_delete`
5456 :param ondelete: Optional string. If set, emit ON DELETE <value> when
5457 issuing DDL for this constraint. Typical values include CASCADE,
5458 SET NULL and RESTRICT. Some dialects may allow for additional
5459 syntaxes.
5461 .. seealso::
5463 :ref:`on_update_on_delete`
5465 :param deferrable: Optional bool. If set, emit DEFERRABLE or NOT
5466 DEFERRABLE when issuing DDL for this constraint.
5468 :param initially: Optional string. If set, emit INITIALLY <value> when
5469 issuing DDL for this constraint.
5471 :param link_to_name: if True, the string name given in ``column`` is
5472 the rendered name of the referenced column, not its locally assigned
5473 ``key``.
5475 :param use_alter: If True, do not emit the DDL for this constraint as
5476 part of the CREATE TABLE definition. Instead, generate it via an
5477 ALTER TABLE statement issued after the full collection of tables
5478 have been created, and drop it via an ALTER TABLE statement before
5479 the full collection of tables are dropped.
5481 The use of :paramref:`_schema.ForeignKeyConstraint.use_alter` is
5482 particularly geared towards the case where two or more tables
5483 are established within a mutually-dependent foreign key constraint
5484 relationship; however, the :meth:`_schema.MetaData.create_all` and
5485 :meth:`_schema.MetaData.drop_all`
5486 methods will perform this resolution
5487 automatically, so the flag is normally not needed.
5489 .. seealso::
5491 :ref:`use_alter`
5493 :param match: Optional string. If set, emit MATCH <value> when issuing
5494 DDL for this constraint. Typical values include SIMPLE, PARTIAL
5495 and FULL.
5497 :param info: Optional data dictionary which will be populated into the
5498 :attr:`.SchemaItem.info` attribute of this object.
5500 :param comment: Optional string that will render an SQL comment on
5501 foreign key constraint creation.
5503 .. versionadded:: 2.0
5505 :param \**dialect_kw: Additional keyword arguments are dialect
5506 specific, and passed in the form ``<dialectname>_<argname>``. See
5507 the documentation regarding an individual dialect at
5508 :ref:`dialect_toplevel` for detail on documented arguments.
5510 """
5512 Constraint.__init__(
5513 self,
5514 name=name,
5515 deferrable=deferrable,
5516 initially=initially,
5517 info=info,
5518 comment=comment,
5519 **dialect_kw,
5520 )
5521 self.onupdate = onupdate
5522 self.ondelete = ondelete
5523 self.link_to_name = link_to_name
5524 self.use_alter = use_alter
5525 self.match = match
5527 if len(columns) != len(refcolumns):
5528 # e.g. FOREIGN KEY (a) REFERENCES r (b, c)
5529 # paraphrasing
5530 # https://www.postgresql.org/docs/current/static/ddl-constraints.html
5531 raise exc.ArgumentError(
5532 "ForeignKeyConstraint number "
5533 "of constrained columns must match the number of "
5534 "referenced columns."
5535 )
5537 # standalone ForeignKeyConstraint - create
5538 # associated ForeignKey objects which will be applied to hosted
5539 # Column objects (in col.foreign_keys), either now or when attached
5540 # to the Table for string-specified names
5541 self.elements = [
5542 ForeignKey(
5543 refcol,
5544 _constraint=self,
5545 name=self.name,
5546 onupdate=self.onupdate,
5547 ondelete=self.ondelete,
5548 use_alter=self.use_alter,
5549 link_to_name=self.link_to_name,
5550 match=self.match,
5551 deferrable=self.deferrable,
5552 initially=self.initially,
5553 **self.dialect_kwargs,
5554 )
5555 for refcol in refcolumns
5556 ]
5558 ColumnCollectionMixin.__init__(self, *columns)
5559 if table is not None:
5560 if hasattr(self, "parent"):
5561 assert table is self.parent
5562 self._set_parent_with_dispatch(table)
5564 def _append_element(self, column: Column[Any], fk: ForeignKey) -> None:
5565 self._columns.add(column)
5566 self.elements.append(fk)
5568 columns: ReadOnlyColumnCollection[str, Column[Any]]
5569 """A :class:`_expression.ColumnCollection` representing the local columns
5570 of this constraint, in the order given and parallel to
5571 :attr:`.ForeignKeyConstraint.elements`.
5573 Unlike the column collection of other constraint types, this collection
5574 does not deduplicate; a constraint which names the same column more than
5575 once, such as ``FOREIGN KEY (a, a) REFERENCES r (b, c)``, includes that
5576 column once per position.
5578 .. versionchanged:: 2.1 A :class:`_schema.ForeignKeyConstraint` may name
5579 the same local column more than once, and this collection retains the
5580 repeated entries.
5582 """
5584 elements: List[ForeignKey]
5585 """A sequence of :class:`_schema.ForeignKey` objects.
5587 Each :class:`_schema.ForeignKey`
5588 represents a single referring column/referred
5589 column pair.
5591 This collection is intended to be read-only.
5593 """
5595 @property
5596 def _referred_schema(self) -> Optional[str]:
5597 for elem in self.elements:
5598 return elem._referred_schema
5599 else:
5600 return None
5602 @property
5603 def referred_table(self) -> Table:
5604 """The :class:`_schema.Table` object to which this
5605 :class:`_schema.ForeignKeyConstraint` references.
5607 This is a dynamically calculated attribute which may not be available
5608 if the constraint and/or parent table is not yet associated with
5609 a metadata collection that contains the referred table.
5611 """
5612 return self.elements[0].column.table
5614 def _validate_dest_table(self, table: Table) -> None:
5615 table_keys = {elem.target_table_key for elem in self.elements}
5616 if None not in table_keys and len(table_keys) > 1:
5617 elem0, elem1 = sorted(cast("Set[str]", table_keys))[0:2]
5618 raise exc.ArgumentError(
5619 f"ForeignKeyConstraint on "
5620 f"{table.fullname}({self._col_description}) refers to "
5621 f"multiple remote tables: {elem0} and {elem1}"
5622 )
5624 @property
5625 def column_keys(self) -> _typing_Sequence[str]:
5626 """Return a list of string keys representing the local
5627 columns in this :class:`_schema.ForeignKeyConstraint`.
5629 This list is either the original string arguments sent
5630 to the constructor of the :class:`_schema.ForeignKeyConstraint`,
5631 or if the constraint has been initialized with :class:`_schema.Column`
5632 objects, is the string ``.key`` of each element.
5634 """
5635 if hasattr(self, "parent"):
5636 return self._columns.keys()
5637 else:
5638 return [
5639 col.key if isinstance(col, ColumnElement) else str(col)
5640 for col in self._pending_colargs
5641 ]
5643 @property
5644 def _col_description(self) -> str:
5645 return ", ".join(self.column_keys)
5647 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
5648 table = parent
5649 assert isinstance(table, Table)
5650 Constraint._set_parent(self, table)
5652 if self._pending_colargs:
5653 # this collection is positional and parallel to self.elements,
5654 # retaining duplicate entries for a constraint such as
5655 # FOREIGN KEY (a, a) REFERENCES r (b, c). _set_parent may run
5656 # more than once for the same table, e.g. for an inline-declared
5657 # constraint that also auto-attaches when its Column objects
5658 # are attached, so populate rather than accumulate.
5659 self._columns._populate_separate_keys(
5660 (col.key, col)
5661 for col in self._col_expressions(table)
5662 if col is not None
5663 )
5665 for col, fk in zip(self._columns, self.elements):
5666 if not hasattr(fk, "parent") or fk.parent is not col:
5667 fk._set_parent_with_dispatch(col)
5669 self._validate_dest_table(table)
5671 @util.deprecated(
5672 "1.4",
5673 "The :meth:`_schema.ForeignKeyConstraint.copy` method is deprecated "
5674 "and will be removed in a future release.",
5675 )
5676 def copy(
5677 self,
5678 *,
5679 schema: Optional[str] = None,
5680 target_table: Optional[Table] = None,
5681 **kw: Any,
5682 ) -> ForeignKeyConstraint:
5683 return self._copy(schema=schema, target_table=target_table, **kw)
5685 def _copy(
5686 self,
5687 *,
5688 schema: Optional[str] = None,
5689 target_table: Optional[Table] = None,
5690 **kw: Any,
5691 ) -> ForeignKeyConstraint:
5692 fkc = ForeignKeyConstraint(
5693 [x.parent.key for x in self.elements],
5694 [
5695 x._copy_tokens(
5696 schema=schema,
5697 table_name=(
5698 target_table.name
5699 if target_table is not None
5700 and x.target_table_key == x.parent.table.key
5701 else None
5702 ),
5703 _is_copy=True,
5704 )
5705 for x in self.elements
5706 ],
5707 name=self.name,
5708 onupdate=self.onupdate,
5709 ondelete=self.ondelete,
5710 use_alter=self.use_alter,
5711 deferrable=self.deferrable,
5712 initially=self.initially,
5713 link_to_name=self.link_to_name,
5714 match=self.match,
5715 comment=self.comment,
5716 **self.dialect_kwargs,
5717 )
5718 for self_fk, other_fk in zip(self.elements, fkc.elements):
5719 self_fk._schema_item_copy(other_fk)
5720 return self._schema_item_copy(fkc)
5723class PrimaryKeyConstraint(ColumnCollectionConstraint):
5724 """A table-level PRIMARY KEY constraint.
5726 The :class:`.PrimaryKeyConstraint` object is present automatically
5727 on any :class:`_schema.Table` object; it is assigned a set of
5728 :class:`_schema.Column` objects corresponding to those marked with
5729 the :paramref:`_schema.Column.primary_key` flag::
5731 >>> my_table = Table(
5732 ... "mytable",
5733 ... metadata,
5734 ... Column("id", Integer, primary_key=True),
5735 ... Column("version_id", Integer, primary_key=True),
5736 ... Column("data", String(50)),
5737 ... )
5738 >>> my_table.primary_key
5739 PrimaryKeyConstraint(
5740 Column('id', Integer(), table=<mytable>,
5741 primary_key=True, nullable=False),
5742 Column('version_id', Integer(), table=<mytable>,
5743 primary_key=True, nullable=False)
5744 )
5746 The primary key of a :class:`_schema.Table` can also be specified by using
5747 a :class:`.PrimaryKeyConstraint` object explicitly; in this mode of usage,
5748 the "name" of the constraint can also be specified, as well as other
5749 options which may be recognized by dialects::
5751 my_table = Table(
5752 "mytable",
5753 metadata,
5754 Column("id", Integer),
5755 Column("version_id", Integer),
5756 Column("data", String(50)),
5757 PrimaryKeyConstraint("id", "version_id", name="mytable_pk"),
5758 )
5760 The two styles of column-specification should generally not be mixed.
5761 An warning is emitted if the columns present in the
5762 :class:`.PrimaryKeyConstraint`
5763 don't match the columns that were marked as ``primary_key=True``, if both
5764 are present; in this case, the columns are taken strictly from the
5765 :class:`.PrimaryKeyConstraint` declaration, and those columns otherwise
5766 marked as ``primary_key=True`` are ignored. This behavior is intended to
5767 be backwards compatible with previous behavior.
5769 For the use case where specific options are to be specified on the
5770 :class:`.PrimaryKeyConstraint`, but the usual style of using
5771 ``primary_key=True`` flags is still desirable, an empty
5772 :class:`.PrimaryKeyConstraint` may be specified, which will take on the
5773 primary key column collection from the :class:`_schema.Table` based on the
5774 flags::
5776 my_table = Table(
5777 "mytable",
5778 metadata,
5779 Column("id", Integer, primary_key=True),
5780 Column("version_id", Integer, primary_key=True),
5781 Column("data", String(50)),
5782 PrimaryKeyConstraint(name="mytable_pk", mssql_clustered=True),
5783 )
5785 """
5787 __visit_name__ = "primary_key_constraint"
5789 _columns: DedupeColumnCollection[Column[Any]]
5791 def __init__(
5792 self,
5793 *columns: _DDLColumnArgument,
5794 name: Optional[str] = None,
5795 deferrable: Optional[bool] = None,
5796 initially: Optional[str] = None,
5797 info: Optional[_InfoType] = None,
5798 _implicit_generated: bool = False,
5799 **dialect_kw: Any,
5800 ) -> None:
5801 self._implicit_generated = _implicit_generated
5802 super().__init__(
5803 *columns,
5804 name=name,
5805 deferrable=deferrable,
5806 initially=initially,
5807 info=info,
5808 **dialect_kw,
5809 )
5811 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
5812 table = parent
5813 assert isinstance(table, Table)
5814 super()._set_parent(table)
5816 if table.primary_key is not self:
5817 table.constraints.discard(table.primary_key)
5818 table.primary_key = self # type: ignore[misc]
5819 table.constraints.add(self)
5821 table_pks = [c for c in table.c if c.primary_key]
5822 if (
5823 self._columns
5824 and table_pks
5825 and set(table_pks) != set(self._columns)
5826 ):
5827 # black could not format these inline
5828 table_pk_str = ", ".join("'%s'" % c.name for c in table_pks)
5829 col_str = ", ".join("'%s'" % c.name for c in self._columns)
5831 util.warn(
5832 f"Table '{table.name}' specifies columns "
5833 f"{table_pk_str} as "
5834 f"primary_key=True, "
5835 f"not matching locally specified columns {col_str}; "
5836 f"setting the "
5837 f"current primary key columns to "
5838 f"{col_str}. "
5839 f"This warning "
5840 f"may become an exception in a future release"
5841 )
5842 table_pks[:] = []
5844 for c in self._columns:
5845 c.primary_key = True
5846 if c._user_defined_nullable is NULL_UNSPECIFIED:
5847 c.nullable = False
5848 if table_pks:
5849 self._columns.extend(table_pks)
5851 def _reload(self, columns: Iterable[Column[Any]]) -> None:
5852 """repopulate this :class:`.PrimaryKeyConstraint` given
5853 a set of columns.
5855 Existing columns in the table that are marked as primary_key=True
5856 are maintained.
5858 Also fires a new event.
5860 This is basically like putting a whole new
5861 :class:`.PrimaryKeyConstraint` object on the parent
5862 :class:`_schema.Table` object without actually replacing the object.
5864 The ordering of the given list of columns is also maintained; these
5865 columns will be appended to the list of columns after any which
5866 are already present.
5868 """
5869 # set the primary key flag on new columns.
5870 # note any existing PK cols on the table also have their
5871 # flag still set.
5872 for col in columns:
5873 col.primary_key = True
5875 self._columns.extend(columns)
5877 PrimaryKeyConstraint._autoincrement_column._reset(self) # type: ignore[attr-defined] # noqa: E501
5878 self._set_parent_with_dispatch(self.table)
5880 def _replace(self, col: Column[Any]) -> None:
5881 PrimaryKeyConstraint._autoincrement_column._reset(self) # type: ignore[attr-defined] # noqa: E501
5882 self._columns.replace(col)
5884 self.dispatch._sa_event_column_added_to_pk_constraint(self, col)
5886 @property
5887 def columns_autoinc_first(self) -> List[Column[Any]]:
5888 autoinc = self._autoincrement_column
5890 if autoinc is not None:
5891 return [autoinc] + [c for c in self._columns if c is not autoinc]
5892 else:
5893 return list(self._columns)
5895 @util.ro_memoized_property
5896 def _autoincrement_column(self) -> Optional[Column[int]]:
5897 def _validate_autoinc(col: Column[Any], autoinc_true: bool) -> bool:
5898 if col.type._type_affinity is not None and issubclass(
5899 col.type._type_affinity, type_api.NUMERICTYPE._type_affinity
5900 ):
5901 scale = col.type.scale # type: ignore[attr-defined]
5902 if scale != 0 and autoinc_true:
5903 raise exc.ArgumentError(
5904 f"Column type {col.type} with non-zero scale "
5905 f"{scale} on column '{col}' is not "
5906 f"compatible with autoincrement=True"
5907 )
5908 elif not autoinc_true:
5909 return False
5910 elif col.type._type_affinity is None or not issubclass(
5911 col.type._type_affinity, type_api.INTEGERTYPE._type_affinity
5912 ):
5913 if autoinc_true:
5914 raise exc.ArgumentError(
5915 f"Column type {col.type} on column '{col}' is not "
5916 f"compatible with autoincrement=True"
5917 )
5918 else:
5919 return False
5920 elif (
5921 col.default is not None
5922 and not isinstance(col.default, Sequence)
5923 and not autoinc_true
5924 ):
5925 return False
5926 elif (
5927 col.server_default is not None
5928 and not isinstance(col.server_default, Identity)
5929 and not autoinc_true
5930 ):
5931 return False
5932 elif col.foreign_keys and col.autoincrement not in (
5933 True,
5934 "ignore_fk",
5935 ):
5936 return False
5937 return True
5939 if len(self._columns) == 1:
5940 col = list(self._columns)[0]
5942 if col.autoincrement is True:
5943 _validate_autoinc(col, True)
5944 return col
5945 elif col.autoincrement in (
5946 "auto",
5947 "ignore_fk",
5948 ) and _validate_autoinc(col, False):
5949 return col
5950 else:
5951 return None
5953 else:
5954 autoinc = None
5955 for col in self._columns:
5956 if col.autoincrement is True:
5957 _validate_autoinc(col, True)
5958 if autoinc is not None:
5959 raise exc.ArgumentError(
5960 f"Only one Column may be marked "
5961 f"autoincrement=True, found both "
5962 f"{col.name} and {autoinc.name}."
5963 )
5964 else:
5965 autoinc = col
5967 return autoinc
5970class UniqueConstraint(ColumnCollectionConstraint):
5971 """A table-level UNIQUE constraint.
5973 Defines a single column or composite UNIQUE constraint. For a no-frills,
5974 single column constraint, adding ``unique=True`` to the ``Column``
5975 definition is a shorthand equivalent for an unnamed, single column
5976 UniqueConstraint.
5977 """
5979 __visit_name__ = "unique_constraint"
5982class Index(
5983 DialectKWArgs, ColumnCollectionMixin, HasConditionalDDL, SchemaItem
5984):
5985 """A table-level INDEX.
5987 Defines a composite (one or more column) INDEX.
5989 E.g.::
5991 sometable = Table(
5992 "sometable",
5993 metadata,
5994 Column("name", String(50)),
5995 Column("address", String(100)),
5996 )
5998 Index("some_index", sometable.c.name)
6000 For a no-frills, single column index, adding
6001 :class:`_schema.Column` also supports ``index=True``::
6003 sometable = Table(
6004 "sometable", metadata, Column("name", String(50), index=True)
6005 )
6007 For a composite index, multiple columns can be specified::
6009 Index("some_index", sometable.c.name, sometable.c.address)
6011 Functional indexes are supported as well, typically by using the
6012 :data:`.func` construct in conjunction with table-bound
6013 :class:`_schema.Column` objects::
6015 Index("some_index", func.lower(sometable.c.name))
6017 An :class:`.Index` can also be manually associated with a
6018 :class:`_schema.Table`,
6019 either through inline declaration or using
6020 :meth:`_schema.Table.append_constraint`. When this approach is used,
6021 the names
6022 of the indexed columns can be specified as strings::
6024 Table(
6025 "sometable",
6026 metadata,
6027 Column("name", String(50)),
6028 Column("address", String(100)),
6029 Index("some_index", "name", "address"),
6030 )
6032 To support functional or expression-based indexes in this form, the
6033 :func:`_expression.text` construct may be used::
6035 from sqlalchemy import text
6037 Table(
6038 "sometable",
6039 metadata,
6040 Column("name", String(50)),
6041 Column("address", String(100)),
6042 Index("some_index", text("lower(name)")),
6043 )
6045 .. seealso::
6047 :ref:`schema_indexes` - General information on :class:`.Index`.
6049 :ref:`postgresql_indexes` - PostgreSQL-specific options available for
6050 the :class:`.Index` construct.
6052 :ref:`mysql_indexes` - MySQL-specific options available for the
6053 :class:`.Index` construct.
6055 :ref:`mssql_indexes` - MSSQL-specific options available for the
6056 :class:`.Index` construct.
6058 """
6060 __visit_name__ = "index"
6062 table: Optional[Table]
6063 expressions: _typing_Sequence[Union[str, ColumnElement[Any]]]
6064 _table_bound_expressions: _typing_Sequence[ColumnElement[Any]]
6066 def __init__(
6067 self,
6068 name: Optional[str],
6069 *expressions: _DDLColumnArgument,
6070 unique: bool = False,
6071 quote: Optional[bool] = None,
6072 info: Optional[_InfoType] = None,
6073 _table: Optional[Table] = None,
6074 _column_flag: bool = False,
6075 **dialect_kw: Any,
6076 ) -> None:
6077 r"""Construct an index object.
6079 :param name:
6080 The name of the index
6082 :param \*expressions:
6083 Column expressions to include in the index. The expressions
6084 are normally instances of :class:`_schema.Column`, but may also
6085 be arbitrary SQL expressions which ultimately refer to a
6086 :class:`_schema.Column`.
6088 :param unique=False:
6089 Keyword only argument; if True, create a unique index.
6091 :param quote=None:
6092 Keyword only argument; whether to apply quoting to the name of
6093 the index. Works in the same manner as that of
6094 :paramref:`_schema.Column.quote`.
6096 :param info=None: Optional data dictionary which will be populated
6097 into the :attr:`.SchemaItem.info` attribute of this object.
6099 :param \**dialect_kw: Additional keyword arguments not mentioned above
6100 are dialect specific, and passed in the form
6101 ``<dialectname>_<argname>``. See the documentation regarding an
6102 individual dialect at :ref:`dialect_toplevel` for detail on
6103 documented arguments.
6105 """
6106 self.table = table = None
6108 self.name = quoted_name.construct(name, quote)
6109 self.unique = unique
6110 if info is not None:
6111 self.info = info
6113 # TODO: consider "table" argument being public, but for
6114 # the purpose of the fix here, it starts as private.
6115 if _table is not None:
6116 table = _table
6118 self._validate_dialect_kwargs(dialect_kw)
6120 self.expressions = []
6121 # will call _set_parent() if table-bound column
6122 # objects are present
6123 ColumnCollectionMixin.__init__(
6124 self,
6125 *expressions,
6126 _column_flag=_column_flag,
6127 _gather_expressions=self.expressions,
6128 )
6129 if table is not None:
6130 self._set_parent(table)
6132 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
6133 table = parent
6134 assert isinstance(table, Table)
6135 ColumnCollectionMixin._set_parent(self, table)
6137 if self.table is not None and table is not self.table:
6138 raise exc.ArgumentError(
6139 f"Index '{self.name}' is against table "
6140 f"'{self.table.description}', and "
6141 f"cannot be associated with table '{table.description}'."
6142 )
6143 self.table = table
6144 table.indexes.add(self)
6146 expressions = self.expressions
6147 col_expressions = self._col_expressions(table)
6148 assert len(expressions) == len(col_expressions)
6150 exprs = []
6151 for expr, colexpr in zip(expressions, col_expressions):
6152 if isinstance(expr, ClauseElement):
6153 exprs.append(expr)
6154 elif colexpr is not None:
6155 exprs.append(colexpr)
6156 else:
6157 assert False
6158 self.expressions = self._table_bound_expressions = exprs
6160 def create(
6161 self,
6162 bind: _CreateDropBind,
6163 checkfirst: Union[bool, CheckFirst] = CheckFirst.NONE,
6164 ) -> None:
6165 """Issue a ``CREATE`` statement for this
6166 :class:`.Index`, using the given
6167 :class:`.Connection` or :class:`.Engine`` for connectivity.
6169 .. seealso::
6171 :meth:`_schema.MetaData.create_all`.
6173 """
6174 bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
6176 def drop(
6177 self,
6178 bind: _CreateDropBind,
6179 checkfirst: Union[bool, CheckFirst] = CheckFirst.NONE,
6180 ) -> None:
6181 """Issue a ``DROP`` statement for this
6182 :class:`.Index`, using the given
6183 :class:`.Connection` or :class:`.Engine` for connectivity.
6185 .. seealso::
6187 :meth:`_schema.MetaData.drop_all`.
6189 """
6190 bind._run_ddl_visitor(ddl.SchemaDropper, self, checkfirst=checkfirst)
6192 def __repr__(self) -> str:
6193 exprs: _typing_Sequence[Any] # noqa: F842
6195 return "Index(%s)" % (
6196 ", ".join(
6197 [repr(self.name)]
6198 + [repr(e) for e in self.expressions]
6199 + (self.unique and ["unique=True"] or [])
6200 )
6201 )
6204_NamingSchemaCallable = Union[
6205 Callable[[Constraint, Table], str],
6206 Callable[[Index, Table], str],
6207]
6208_NamingSchemaDirective = Union[str, _NamingSchemaCallable]
6211class _NamingSchemaTD(TypedDict, total=False):
6212 fk: _NamingSchemaDirective
6213 pk: _NamingSchemaDirective
6214 ix: _NamingSchemaDirective
6215 ck: _NamingSchemaDirective
6216 uq: _NamingSchemaDirective
6219_NamingSchemaParameter = Union[
6220 # it seems like the TypedDict here is useful for pylance typeahead,
6221 # and not much else
6222 _NamingSchemaTD,
6223 # there is no form that allows Union[Type[Any], str] to work in all
6224 # cases, including breaking out Mapping[] entries for each combination
6225 # even, therefore keys must be `Any` (see #10264)
6226 Mapping[Any, _NamingSchemaDirective],
6227]
6230DEFAULT_NAMING_CONVENTION: _NamingSchemaParameter = util.immutabledict(
6231 {"ix": "ix_%(column_0_label)s"}
6232)
6235class MetaData(HasSchemaAttr):
6236 """A collection of :class:`_schema.Table`
6237 objects and their associated schema
6238 constructs.
6240 Holds a collection of :class:`_schema.Table` objects as well as
6241 an optional binding to an :class:`_engine.Engine` or
6242 :class:`_engine.Connection`. If bound, the :class:`_schema.Table` objects
6243 in the collection and their columns may participate in implicit SQL
6244 execution.
6246 The :class:`_schema.Table` objects themselves are stored in the
6247 :attr:`_schema.MetaData.tables` dictionary.
6249 :class:`_schema.MetaData` is a thread-safe object for read operations.
6250 Construction of new tables within a single :class:`_schema.MetaData`
6251 object,
6252 either explicitly or via reflection, may not be completely thread-safe.
6254 .. seealso::
6256 :ref:`metadata_describing` - Introduction to database metadata
6258 """
6260 __visit_name__ = "metadata"
6262 def __init__(
6263 self,
6264 schema: Optional[str] = None,
6265 quote_schema: Optional[bool] = None,
6266 naming_convention: Optional[_NamingSchemaParameter] = None,
6267 info: Optional[_InfoType] = None,
6268 ) -> None:
6269 """Create a new MetaData object.
6271 :param schema:
6272 The default schema to use for the :class:`_schema.Table`,
6273 :class:`.Sequence`, and potentially other objects associated with
6274 this :class:`_schema.MetaData`. Defaults to ``None``.
6276 .. seealso::
6278 :ref:`schema_metadata_schema_name` - details on how the
6279 :paramref:`_schema.MetaData.schema` parameter is used.
6281 :paramref:`_schema.Table.schema`
6283 :paramref:`.Sequence.schema`
6285 :param quote_schema:
6286 Sets the ``quote_schema`` flag for those :class:`_schema.Table`,
6287 :class:`.Sequence`, and other objects which make usage of the
6288 local ``schema`` name.
6290 :param info: Optional data dictionary which will be populated into the
6291 :attr:`.SchemaItem.info` attribute of this object.
6293 :param naming_convention: a dictionary referring to values which
6294 will establish default naming conventions for :class:`.Constraint`
6295 and :class:`.Index` objects, for those objects which are not given
6296 a name explicitly.
6298 The keys of this dictionary may be:
6300 * a constraint or Index class, e.g. the :class:`.UniqueConstraint`,
6301 :class:`_schema.ForeignKeyConstraint` class, the :class:`.Index`
6302 class
6304 * a string mnemonic for one of the known constraint classes;
6305 ``"fk"``, ``"pk"``, ``"ix"``, ``"ck"``, ``"uq"`` for foreign key,
6306 primary key, index, check, and unique constraint, respectively.
6308 * the string name of a user-defined "token" that can be used
6309 to define new naming tokens.
6311 The values associated with each "constraint class" or "constraint
6312 mnemonic" key are string naming templates, such as
6313 ``"uq_%(table_name)s_%(column_0_name)s"``,
6314 which describe how the name should be composed. The values
6315 associated with user-defined "token" keys should be callables of the
6316 form ``fn(constraint, table)``, which accepts the constraint/index
6317 object and :class:`_schema.Table` as arguments, returning a string
6318 result.
6320 The built-in names are as follows, some of which may only be
6321 available for certain types of constraint:
6323 * ``%(table_name)s`` - the name of the :class:`_schema.Table`
6324 object
6325 associated with the constraint.
6327 * ``%(referred_table_name)s`` - the name of the
6328 :class:`_schema.Table`
6329 object associated with the referencing target of a
6330 :class:`_schema.ForeignKeyConstraint`.
6332 * ``%(column_0_name)s`` - the name of the :class:`_schema.Column`
6333 at
6334 index position "0" within the constraint.
6336 * ``%(column_0N_name)s`` - the name of all :class:`_schema.Column`
6337 objects in order within the constraint, joined without a
6338 separator.
6340 * ``%(column_0_N_name)s`` - the name of all
6341 :class:`_schema.Column`
6342 objects in order within the constraint, joined with an
6343 underscore as a separator.
6345 * ``%(column_0_label)s``, ``%(column_0N_label)s``,
6346 ``%(column_0_N_label)s`` - the label of either the zeroth
6347 :class:`_schema.Column` or all :class:`.Columns`, separated with
6348 or without an underscore
6350 * ``%(column_0_key)s``, ``%(column_0N_key)s``,
6351 ``%(column_0_N_key)s`` - the key of either the zeroth
6352 :class:`_schema.Column` or all :class:`.Columns`, separated with
6353 or without an underscore
6355 * ``%(referred_column_0_name)s``, ``%(referred_column_0N_name)s``
6356 ``%(referred_column_0_N_name)s``, ``%(referred_column_0_key)s``,
6357 ``%(referred_column_0N_key)s``, ... column tokens which
6358 render the names/keys/labels of columns that are referenced
6359 by a :class:`_schema.ForeignKeyConstraint`.
6361 * ``%(constraint_name)s`` - a special key that refers to the
6362 existing name given to the constraint. When this key is
6363 present, the :class:`.Constraint` object's existing name will be
6364 replaced with one that is composed from template string that
6365 uses this token. When this token is present, it is required that
6366 the :class:`.Constraint` is given an explicit name ahead of time.
6368 * user-defined: any additional token may be implemented by passing
6369 it along with a ``fn(constraint, table)`` callable to the
6370 naming_convention dictionary.
6372 .. seealso::
6374 :ref:`constraint_naming_conventions` - for detailed usage
6375 examples.
6377 """
6378 if schema is not None and not isinstance(schema, str):
6379 raise exc.ArgumentError(
6380 "expected schema argument to be a string, "
6381 f"got {type(schema)}."
6382 )
6383 self.tables = util.FacadeDict()
6384 self.schema = quoted_name.construct(schema, quote_schema)
6385 self.naming_convention = (
6386 naming_convention
6387 if naming_convention
6388 else DEFAULT_NAMING_CONVENTION
6389 )
6390 if info:
6391 self.info = info
6392 self._schemas: Set[str] = set()
6393 self._sequences: Dict[str, Sequence] = {}
6394 self._fk_memos: Dict[Tuple[str, Optional[str]], List[ForeignKey]] = (
6395 collections.defaultdict(list)
6396 )
6397 self._objects: Set[Union[HasSchemaAttr, SchemaType]] = set()
6399 tables: util.FacadeDict[str, Table]
6400 """A dictionary of :class:`_schema.Table`
6401 objects keyed to their name or "table key".
6403 The exact key is that determined by the :attr:`_schema.Table.key`
6404 attribute;
6405 for a table with no :attr:`_schema.Table.schema` attribute,
6406 this is the same
6407 as :attr:`_schema.Table.name`. For a table with a schema,
6408 it is typically of the
6409 form ``schemaname.tablename``.
6411 .. seealso::
6413 :attr:`_schema.MetaData.sorted_tables`
6415 """
6417 def __repr__(self) -> str:
6418 return "MetaData()"
6420 def __contains__(self, table_or_key: Union[str, Table]) -> bool:
6421 if not isinstance(table_or_key, str):
6422 table_or_key = table_or_key.key
6423 return table_or_key in self.tables
6425 def _add_table(
6426 self, name: str, schema: Optional[str], table: Table
6427 ) -> None:
6428 key = _get_table_key(name, schema)
6429 self.tables._insert_item(key, table)
6430 if schema:
6431 self._schemas.add(schema)
6433 def _remove_table(self, name: str, schema: Optional[str]) -> None:
6434 key = _get_table_key(name, schema)
6435 removed = dict.pop(self.tables, key, None)
6436 if removed is not None:
6437 for fk in removed.foreign_keys:
6438 fk._remove_from_metadata(self)
6439 if self._schemas:
6440 self._schemas = {
6441 t.schema for t in self.tables.values() if t.schema is not None
6442 }
6444 def __getstate__(self) -> Dict[str, Any]:
6445 return {
6446 "tables": self.tables,
6447 "schema": self.schema,
6448 "schemas": self._schemas,
6449 "sequences": self._sequences,
6450 "fk_memos": self._fk_memos,
6451 "naming_convention": self.naming_convention,
6452 "objects": self._objects,
6453 }
6455 def __setstate__(self, state: Dict[str, Any]) -> None:
6456 self.tables = state["tables"]
6457 self.schema = state["schema"]
6458 self.naming_convention = state["naming_convention"]
6459 self._sequences = state["sequences"]
6460 self._schemas = state["schemas"]
6461 self._fk_memos = state["fk_memos"]
6462 self._objects = state.get("objects", set())
6464 def clear(self) -> None:
6465 """Clear all objects from this MetaData."""
6467 dict.clear(self.tables)
6468 self._schemas.clear()
6469 self._fk_memos.clear()
6470 self._sequences.clear()
6471 self._objects.clear()
6473 def remove(self, table: Table) -> None:
6474 """Remove the given Table object from this MetaData."""
6476 self._remove_table(table.name, table.schema)
6478 @property
6479 def sorted_tables(self) -> List[Table]:
6480 """Returns a list of :class:`_schema.Table` objects sorted in order of
6481 foreign key dependency.
6483 The sorting will place :class:`_schema.Table`
6484 objects that have dependencies
6485 first, before the dependencies themselves, representing the
6486 order in which they can be created. To get the order in which
6487 the tables would be dropped, use the ``reversed()`` Python built-in.
6489 .. warning::
6491 The :attr:`.MetaData.sorted_tables` attribute cannot by itself
6492 accommodate automatic resolution of dependency cycles between
6493 tables, which are usually caused by mutually dependent foreign key
6494 constraints. When these cycles are detected, the foreign keys
6495 of these tables are omitted from consideration in the sort.
6496 A warning is emitted when this condition occurs, which will be an
6497 exception raise in a future release. Tables which are not part
6498 of the cycle will still be returned in dependency order.
6500 To resolve these cycles, the
6501 :paramref:`_schema.ForeignKeyConstraint.use_alter` parameter may be
6502 applied to those constraints which create a cycle. Alternatively,
6503 the :func:`_schema.sort_tables_and_constraints` function will
6504 automatically return foreign key constraints in a separate
6505 collection when cycles are detected so that they may be applied
6506 to a schema separately.
6508 .. seealso::
6510 :func:`_schema.sort_tables`
6512 :func:`_schema.sort_tables_and_constraints`
6514 :attr:`_schema.MetaData.tables`
6516 :meth:`_reflection.Inspector.get_table_names`
6518 :meth:`_reflection.Inspector.get_sorted_table_and_fkc_names`
6521 """
6522 return ddl.sort_tables(
6523 sorted(self.tables.values(), key=lambda t: t.key) # type: ignore[attr-defined] # noqa: E501
6524 )
6526 # overload needed to work around mypy this mypy
6527 # https://github.com/python/mypy/issues/17093
6528 @overload
6529 def reflect(
6530 self,
6531 bind: Engine,
6532 schema: Optional[str] = ...,
6533 views: bool = ...,
6534 only: Union[
6535 _typing_Sequence[str], Callable[[str, MetaData], bool], None
6536 ] = ...,
6537 extend_existing: bool = ...,
6538 autoload_replace: bool = ...,
6539 resolve_fks: bool = ...,
6540 **dialect_kwargs: Any,
6541 ) -> None: ...
6543 @overload
6544 def reflect(
6545 self,
6546 bind: Connection,
6547 schema: Optional[str] = ...,
6548 views: bool = ...,
6549 only: Union[
6550 _typing_Sequence[str], Callable[[str, MetaData], bool], None
6551 ] = ...,
6552 extend_existing: bool = ...,
6553 autoload_replace: bool = ...,
6554 resolve_fks: bool = ...,
6555 **dialect_kwargs: Any,
6556 ) -> None: ...
6558 @util.preload_module("sqlalchemy.engine.reflection")
6559 def reflect(
6560 self,
6561 bind: Union[Engine, Connection],
6562 schema: Optional[str] = None,
6563 views: bool = False,
6564 only: Union[
6565 _typing_Sequence[str], Callable[[str, MetaData], bool], None
6566 ] = None,
6567 extend_existing: bool = False,
6568 autoload_replace: bool = True,
6569 resolve_fks: bool = True,
6570 **dialect_kwargs: Any,
6571 ) -> None:
6572 r"""Load all available table definitions from the database.
6574 Automatically creates ``Table`` entries in this ``MetaData`` for any
6575 table available in the database but not yet present in the
6576 ``MetaData``. May be called multiple times to pick up tables recently
6577 added to the database, however no special action is taken if a table
6578 in this ``MetaData`` no longer exists in the database.
6580 :param bind:
6581 A :class:`.Connection` or :class:`.Engine` used to access the
6582 database.
6584 :param schema:
6585 Optional, query and reflect tables from an alternate schema.
6586 If None, the schema associated with this :class:`_schema.MetaData`
6587 is used, if any.
6589 :param views:
6590 If True, also reflect views (materialized and plain).
6592 :param only:
6593 Optional. Load only a sub-set of available named tables. May be
6594 specified as a sequence of names or a callable.
6596 If a sequence of names is provided, only those tables will be
6597 reflected. An error is raised if a table is requested but not
6598 available. Named tables already present in this ``MetaData`` are
6599 ignored.
6601 If a callable is provided, it will be used as a boolean predicate to
6602 filter the list of potential table names. The callable is called
6603 with a table name and this ``MetaData`` instance as positional
6604 arguments and should return a true value for any table to reflect.
6606 :param extend_existing: Passed along to each :class:`_schema.Table` as
6607 :paramref:`_schema.Table.extend_existing`.
6609 :param autoload_replace: Passed along to each :class:`_schema.Table`
6610 as
6611 :paramref:`_schema.Table.autoload_replace`.
6613 :param resolve_fks: if True, reflect :class:`_schema.Table`
6614 objects linked
6615 to :class:`_schema.ForeignKey` objects located in each
6616 :class:`_schema.Table`.
6617 For :meth:`_schema.MetaData.reflect`,
6618 this has the effect of reflecting
6619 related tables that might otherwise not be in the list of tables
6620 being reflected, for example if the referenced table is in a
6621 different schema or is omitted via the
6622 :paramref:`.MetaData.reflect.only` parameter. When False,
6623 :class:`_schema.ForeignKey` objects are not followed to the
6624 :class:`_schema.Table`
6625 in which they link, however if the related table is also part of the
6626 list of tables that would be reflected in any case, the
6627 :class:`_schema.ForeignKey` object will still resolve to its related
6628 :class:`_schema.Table` after the :meth:`_schema.MetaData.reflect`
6629 operation is
6630 complete. Defaults to True.
6632 .. seealso::
6634 :paramref:`_schema.Table.resolve_fks`
6636 :param \**dialect_kwargs: Additional keyword arguments not mentioned
6637 above are dialect specific, and passed in the form
6638 ``<dialectname>_<argname>``. See the documentation regarding an
6639 individual dialect at :ref:`dialect_toplevel` for detail on
6640 documented arguments.
6642 .. seealso::
6644 :ref:`metadata_reflection_toplevel`
6646 :meth:`_events.DDLEvents.column_reflect` - Event used to customize
6647 the reflected columns. Usually used to generalize the types using
6648 :meth:`_types.TypeEngine.as_generic`
6650 :ref:`metadata_reflection_dbagnostic_types` - describes how to
6651 reflect tables using general types.
6653 """
6655 with inspection.inspect(bind)._inspection_context() as insp:
6656 reflect_opts: Any = {
6657 "autoload_with": insp,
6658 "extend_existing": extend_existing,
6659 "autoload_replace": autoload_replace,
6660 "resolve_fks": resolve_fks,
6661 "_extend_on": set(),
6662 }
6664 reflect_opts.update(dialect_kwargs)
6666 if schema is None:
6667 schema = self.schema
6669 if schema is not None:
6670 reflect_opts["schema"] = schema
6672 kind = util.preloaded.engine_reflection.ObjectKind.TABLE
6673 available: util.OrderedSet[str] = util.OrderedSet(
6674 insp.get_table_names(schema, **dialect_kwargs)
6675 )
6676 if views:
6677 kind = util.preloaded.engine_reflection.ObjectKind.ANY
6678 available.update(insp.get_view_names(schema, **dialect_kwargs))
6679 try:
6680 available.update(
6681 insp.get_materialized_view_names(
6682 schema, **dialect_kwargs
6683 )
6684 )
6685 except NotImplementedError:
6686 pass
6688 if schema is not None:
6689 available_w_schema: util.OrderedSet[str] = util.OrderedSet(
6690 [f"{schema}.{name}" for name in available]
6691 )
6692 else:
6693 available_w_schema = available
6695 current = set(self.tables)
6697 if only is None:
6698 load = [
6699 name
6700 for name, schname in zip(available, available_w_schema)
6701 if extend_existing or schname not in current
6702 ]
6703 elif callable(only):
6704 load = [
6705 name
6706 for name, schname in zip(available, available_w_schema)
6707 if (extend_existing or schname not in current)
6708 and only(name, self)
6709 ]
6710 else:
6711 missing = [name for name in only if name not in available]
6712 if missing:
6713 s = schema and (" schema '%s'" % schema) or ""
6714 missing_str = ", ".join(missing)
6715 raise exc.InvalidRequestError(
6716 f"Could not reflect: requested table(s) not available "
6717 f"in {bind.engine!r}{s}: ({missing_str})"
6718 )
6719 load = [
6720 name
6721 for name in only
6722 if extend_existing or name not in current
6723 ]
6724 # pass the available tables so the inspector can
6725 # choose to ignore the filter_names
6726 _reflect_info = insp._get_reflection_info(
6727 schema=schema,
6728 filter_names=load,
6729 available=available,
6730 kind=kind,
6731 scope=util.preloaded.engine_reflection.ObjectScope.ANY,
6732 **dialect_kwargs,
6733 )
6734 reflect_opts["_reflect_info"] = _reflect_info
6736 for name in load:
6737 try:
6738 Table(name, self, **reflect_opts)
6739 except exc.UnreflectableTableError as uerr:
6740 util.warn(f"Skipping table {name}: {uerr}")
6742 def create_all(
6743 self,
6744 bind: _CreateDropBind,
6745 tables: Optional[_typing_Sequence[Table]] = None,
6746 checkfirst: Union[bool, CheckFirst] = CheckFirst.ALL,
6747 ) -> None:
6748 """Create all tables stored in this metadata.
6750 Conditional by default, will not attempt to recreate tables already
6751 present in the target database.
6753 :param bind:
6754 A :class:`.Connection` or :class:`.Engine` used to access the
6755 database.
6757 :param tables:
6758 Optional list of ``Table`` objects, which is a subset of the total
6759 tables in the ``MetaData`` (others are ignored).
6761 :param checkfirst: A boolean value or instance of :class:`.CheckFirst`.
6762 Indicates which objects should be checked for within a separate pass
6763 before creating schema objects.
6765 """
6766 bind._run_ddl_visitor(
6767 ddl.SchemaGenerator, self, checkfirst=checkfirst, tables=tables
6768 )
6770 def drop_all(
6771 self,
6772 bind: _CreateDropBind,
6773 tables: Optional[_typing_Sequence[Table]] = None,
6774 checkfirst: Union[bool, CheckFirst] = CheckFirst.ALL,
6775 ) -> None:
6776 """Drop all tables stored in this metadata.
6778 Conditional by default, will not attempt to drop tables not present in
6779 the target database.
6781 :param bind:
6782 A :class:`.Connection` or :class:`.Engine` used to access the
6783 database.
6785 :param tables:
6786 Optional list of ``Table`` objects, which is a subset of the
6787 total tables in the ``MetaData`` (others are ignored).
6789 :param checkfirst: A boolean value or instance of :class:`.CheckFirst`.
6790 Indicates which objects should be checked for within a separate pass
6791 before dropping schema objects.
6793 """
6794 bind._run_ddl_visitor(
6795 ddl.SchemaDropper, self, checkfirst=checkfirst, tables=tables
6796 )
6798 @property
6799 def schemas(self) -> _typing_Sequence[str]:
6800 """A sequence of schema names that are present in this MetaData."""
6801 schemas = self._schemas
6802 if self.schema:
6803 schemas = schemas | {self.schema}
6804 return tuple(schemas)
6806 def get_schema_objects(
6807 self,
6808 kind: Type[_T],
6809 *,
6810 schema: Union[str, None, Literal[_NoArg.NO_ARG]] = _NoArg.NO_ARG,
6811 ) -> _typing_Sequence[_T]:
6812 """Return a sequence of schema objects of the given kind.
6814 This method can be used to return :class:`_sqltypes.Enum`,
6815 :class:`.Sequence`, etc. objects registered in this
6816 :class:`_schema.MetaData`.
6818 :param kind: a type that indicates what object to return, such as
6819 :class:`Enum` or :class:`Sequence`.
6820 :param schema: Optional, a schema name to filter the objects by. If
6821 not provided the default schema of the metadata is used.
6823 """
6825 if schema is _NoArg.NO_ARG:
6826 schema = self.schema
6827 return tuple(
6828 obj
6829 for obj in self._objects
6830 if isinstance(obj, kind) and obj.schema == schema
6831 )
6833 def get_schema_object_by_name(
6834 self,
6835 kind: Type[_T],
6836 name: str,
6837 *,
6838 schema: Union[str, None, Literal[_NoArg.NO_ARG]] = _NoArg.NO_ARG,
6839 ) -> Optional[_T]:
6840 """Return a schema objects of the given kind and name if found.
6842 This method can be used to return :class:`_sqltypes.Enum`,
6843 :class:`.Sequence`, etc. objects registered in this
6844 :class:`_schema.MetaData`.
6846 :param kind: a type that indicates what object to return, such as
6847 :class:`Enum` or :class:`Sequence`.
6848 :param name: the name of the object to return.
6849 :param schema: Optional, a schema name to filter the objects by. If
6850 not provided the default schema of the metadata is used.
6852 """
6854 for obj in self.get_schema_objects(kind, schema=schema):
6855 if getattr(obj, "name", None) == name:
6856 return obj
6857 return None
6859 def _register_object(self, obj: Union[HasSchemaAttr, SchemaType]) -> None:
6860 self._objects.add(obj)
6863class Computed(FetchedValue, SchemaItem):
6864 """Defines a generated column, i.e. "GENERATED ALWAYS AS" syntax.
6866 The :class:`.Computed` construct is an inline construct added to the
6867 argument list of a :class:`_schema.Column` object::
6869 from sqlalchemy import Computed
6871 Table(
6872 "square",
6873 metadata_obj,
6874 Column("side", Float, nullable=False),
6875 Column("area", Float, Computed("side * side")),
6876 )
6878 See the linked documentation below for complete details.
6880 .. seealso::
6882 :ref:`computed_ddl`
6884 """
6886 __visit_name__ = "computed_column"
6888 column: Optional[Column[Any]]
6890 @_document_text_coercion(
6891 "sqltext", ":class:`.Computed`", ":paramref:`.Computed.sqltext`"
6892 )
6893 def __init__(
6894 self, sqltext: _DDLColumnArgument, persisted: Optional[bool] = None
6895 ) -> None:
6896 """Construct a GENERATED ALWAYS AS DDL construct to accompany a
6897 :class:`_schema.Column`.
6899 :param sqltext:
6900 A string containing the column generation expression, which will be
6901 used verbatim, or a SQL expression construct, such as a
6902 :func:`_expression.text`
6903 object. If given as a string, the object is converted to a
6904 :func:`_expression.text` object.
6906 :param persisted:
6907 Optional, controls how this column should be persisted by the
6908 database. Possible values are:
6910 * ``None``, the default, it will use the default persistence
6911 defined by the database.
6912 * ``True``, will render ``GENERATED ALWAYS AS ... STORED``, or the
6913 equivalent for the target database if supported.
6914 * ``False``, will render ``GENERATED ALWAYS AS ... VIRTUAL``, or
6915 the equivalent for the target database if supported.
6917 Specifying ``True`` or ``False`` may raise an error when the DDL
6918 is emitted to the target database if the database does not support
6919 that persistence option. Leaving this parameter at its default
6920 of ``None`` is guaranteed to succeed for all databases that support
6921 ``GENERATED ALWAYS AS``.
6923 """
6924 self.sqltext = coercions.expect(roles.DDLExpressionRole, sqltext)
6925 self.persisted = persisted
6926 self.column = None
6928 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
6929 assert isinstance(parent, Column)
6931 if not isinstance(
6932 parent.server_default, (type(None), Computed)
6933 ) or not isinstance(parent.server_onupdate, (type(None), Computed)):
6934 raise exc.ArgumentError(
6935 "A generated column cannot specify a server_default or a "
6936 "server_onupdate argument"
6937 )
6938 self.column = parent
6939 parent.computed = self
6940 self.column.server_onupdate = self
6941 self.column.server_default = self
6943 def _as_for_update(self, for_update: bool) -> FetchedValue:
6944 return self
6946 @util.deprecated(
6947 "1.4",
6948 "The :meth:`_schema.Computed.copy` method is deprecated "
6949 "and will be removed in a future release.",
6950 )
6951 def copy(
6952 self, *, target_table: Optional[Table] = None, **kw: Any
6953 ) -> Computed:
6954 return self._copy(target_table=target_table, **kw)
6956 def _copy(
6957 self, *, target_table: Optional[Table] = None, **kw: Any
6958 ) -> Computed:
6959 sqltext = _copy_expression(
6960 self.sqltext,
6961 self.column.table if self.column is not None else None,
6962 target_table,
6963 )
6964 g = Computed(sqltext, persisted=self.persisted)
6966 return self._schema_item_copy(g)
6969class Identity(IdentityOptions, FetchedValue, SchemaItem):
6970 """Defines an identity column, i.e. "GENERATED { ALWAYS | BY DEFAULT }
6971 AS IDENTITY" syntax.
6973 The :class:`.Identity` construct is an inline construct added to the
6974 argument list of a :class:`_schema.Column` object::
6976 from sqlalchemy import Identity
6978 Table(
6979 "foo",
6980 metadata_obj,
6981 Column("id", Integer, Identity()),
6982 Column("description", Text),
6983 )
6985 See the linked documentation below for complete details.
6987 .. versionadded:: 1.4
6989 .. seealso::
6991 :ref:`identity_ddl`
6993 """
6995 __visit_name__ = "identity_column"
6997 is_identity = True
6999 @util.deprecated_params(
7000 order=(
7001 "2.1",
7002 "This parameter is supported only by Oracle Database, "
7003 "use ``oracle_order`` instead.",
7004 ),
7005 on_null=(
7006 "2.1",
7007 "This parameter is supported only by Oracle Database, "
7008 "use ``oracle_on_null`` instead.",
7009 ),
7010 )
7011 def __init__(
7012 self,
7013 always: Optional[bool] = False,
7014 on_null: Optional[bool] = None,
7015 start: Optional[int] = None,
7016 increment: Optional[int] = None,
7017 minvalue: Optional[int] = None,
7018 maxvalue: Optional[int] = None,
7019 nominvalue: Optional[bool] = None,
7020 nomaxvalue: Optional[bool] = None,
7021 cycle: Optional[bool] = None,
7022 cache: Optional[int] = None,
7023 order: Optional[bool] = None,
7024 **dialect_kw: Any,
7025 ) -> None:
7026 """Construct a GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY DDL
7027 construct to accompany a :class:`_schema.Column`.
7029 See the :class:`.Sequence` documentation for a complete description
7030 of most parameters.
7032 .. note::
7033 MSSQL supports this construct as the preferred alternative to
7034 generate an IDENTITY on a column, but it uses non standard
7035 syntax that only support :paramref:`_schema.Identity.start`
7036 and :paramref:`_schema.Identity.increment`.
7037 All other parameters are ignored.
7039 :param always:
7040 A boolean, that indicates the type of identity column.
7041 If ``False`` is specified, the default, then the user-specified
7042 value takes precedence.
7043 If ``True`` is specified, a user-specified value is not accepted (
7044 on some backends, like PostgreSQL, OVERRIDING SYSTEM VALUE, or
7045 similar, may be specified in an INSERT to override the sequence
7046 value).
7047 Some backends also have a default value for this parameter,
7048 ``None`` can be used to omit rendering this part in the DDL. It
7049 will be treated as ``False`` if a backend does not have a default
7050 value.
7052 :param on_null:
7053 Set to ``True`` to specify ON NULL in conjunction with a
7054 ``always=False`` identity column. This option is only supported on
7055 some backends, like Oracle Database.
7057 :param start: the starting index of the sequence.
7058 :param increment: the increment value of the sequence.
7059 :param minvalue: the minimum value of the sequence.
7060 :param maxvalue: the maximum value of the sequence.
7061 :param nominvalue: no minimum value of the sequence.
7062 :param nomaxvalue: no maximum value of the sequence.
7063 :param cycle: allows the sequence to wrap around when the maxvalue
7064 or minvalue has been reached.
7065 :param cache: optional integer value; number of future values in the
7066 sequence which are calculated in advance.
7067 :param order: optional boolean value; if true, renders the
7068 ORDER keyword.
7070 """
7071 self.dialect_options
7072 if on_null is not None:
7073 if "oracle_on_null" in dialect_kw:
7074 raise exc.ArgumentError(
7075 "Cannot specify both 'on_null' and 'oracle_on_null'. "
7076 "Please use only 'oracle_on_null'."
7077 )
7078 dialect_kw["oracle_on_null"] = on_null
7080 IdentityOptions.__init__(
7081 self,
7082 start=start,
7083 increment=increment,
7084 minvalue=minvalue,
7085 maxvalue=maxvalue,
7086 nominvalue=nominvalue,
7087 nomaxvalue=nomaxvalue,
7088 cycle=cycle,
7089 cache=cache,
7090 order=order,
7091 **dialect_kw,
7092 )
7093 self.always = always
7094 self.column = None
7096 @property
7097 def on_null(self) -> Optional[bool]:
7098 """Alias of the ``dialect_kwargs`` ``'oracle_on_null'``.
7100 .. deprecated:: 2.1 The 'on_null' attribute is deprecated.
7101 """
7102 value: Optional[bool] = self.dialect_kwargs.get("oracle_on_null")
7103 return value
7105 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
7106 assert isinstance(parent, Column)
7107 if not isinstance(
7108 parent.server_default, (type(None), Identity)
7109 ) or not isinstance(parent.server_onupdate, type(None)):
7110 raise exc.ArgumentError(
7111 "A column with an Identity object cannot specify a "
7112 "server_default or a server_onupdate argument"
7113 )
7114 if parent.autoincrement is False:
7115 raise exc.ArgumentError(
7116 "A column with an Identity object cannot specify "
7117 "autoincrement=False"
7118 )
7119 self.column = parent
7121 parent.identity = self
7122 if parent._user_defined_nullable is NULL_UNSPECIFIED:
7123 parent.nullable = False
7125 parent.server_default = self
7127 def _as_for_update(self, for_update: bool) -> FetchedValue:
7128 return self
7130 @util.deprecated(
7131 "1.4",
7132 "The :meth:`_schema.Identity.copy` method is deprecated "
7133 "and will be removed in a future release.",
7134 )
7135 def copy(self, **kw: Any) -> Identity:
7136 return self._copy(**kw)
7138 def _copy(self, **kw: Any) -> Identity:
7139 i = Identity(**self._as_dict(), **self.dialect_kwargs)
7141 return self._schema_item_copy(i)
7143 def _as_dict(self) -> Dict[str, Any]:
7144 return {
7145 # always=None means something different than always=False
7146 "always": self.always,
7147 **super()._as_dict(),
7148 }