Shortcuts on this page
r m x toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1# sql/schema.py
2# Copyright (C) 2005-2026 the SQLAlchemy authors and contributors
3# <see AUTHORS file>
4#
5# This module is part of SQLAlchemy and is released under
6# the MIT License: https://www.opensource.org/licenses/mit-license.php
8"""The schema module provides the building blocks for database metadata.
10Each element within this module describes a database entity which can be
11created and dropped, or is otherwise part of such an entity. Examples include
12tables, columns, sequences, and indexes.
14All entities are subclasses of :class:`~sqlalchemy.schema.SchemaItem`, and as
15defined in this module they are intended to be agnostic of any vendor-specific
16constructs.
18A collection of entities are grouped into a unit called
19:class:`~sqlalchemy.schema.MetaData`. MetaData serves as a logical grouping of
20schema elements, and can also be associated with an actual database connection
21such that operations involving the contained elements can contact the database
22as needed.
24Two of the elements here also build upon their "syntactic" counterparts, which
25are defined in :class:`~sqlalchemy.sql.expression.`, specifically
26:class:`~sqlalchemy.schema.Table` and :class:`~sqlalchemy.schema.Column`.
27Since these objects are part of the SQL expression language, they are usable
28as components in SQL expressions.
30"""
32from __future__ import annotations
34from abc import ABC
35import collections
36from enum import Enum
37import operator
38import typing
39from typing import Any
40from typing import Callable
41from typing import cast
42from typing import ClassVar
43from typing import Collection
44from typing import Dict
45from typing import Final
46from typing import Iterable
47from typing import Iterator
48from typing import List
49from typing import Literal
50from typing import Mapping
51from typing import NamedTuple
52from typing import NoReturn
53from typing import Optional
54from typing import overload
55from typing import Protocol
56from typing import Sequence as _typing_Sequence
57from typing import Set
58from typing import Tuple
59from typing import Type
60from typing import TYPE_CHECKING
61from typing import TypedDict
62from typing import TypeGuard
63from typing import TypeVar
64from typing import Union
66from . import coercions
67from . import ddl
68from . import roles
69from . import type_api
70from . import visitors
71from ._annotated_cols import _ColCC_co
72from ._annotated_cols import _extract_columns_from_class
73from ._annotated_cols import _TC_co
74from ._annotated_cols import Named
75from ._annotated_cols import TypedColumns
76from ._typing import _T
77from .base import _DefaultDescriptionTuple
78from .base import _NoArg
79from .base import _NoneName
80from .base import _SentinelColumnCharacterization
81from .base import _SentinelDefaultCharacterization
82from .base import DedupeColumnCollection
83from .base import DialectKWArgs
84from .base import Executable
85from .base import SchemaEventTarget as SchemaEventTarget
86from .base import SchemaVisitable as SchemaVisitable
87from .base import WriteableColumnCollection
88from .coercions import _document_text_coercion
89from .ddl import CheckFirst
90from .elements import ClauseElement
91from .elements import ColumnClause
92from .elements import ColumnElement
93from .elements import quoted_name
94from .elements import TextClause
95from .selectable import TableClause
96from .type_api import to_instance
97from .visitors import ExternallyTraversible
98from .. import event
99from .. import exc
100from .. import inspection
101from .. import util
102from ..util import HasMemoized
103from ..util.typing import Self
105if typing.TYPE_CHECKING:
106 from ._typing import _AutoIncrementType
107 from ._typing import _CreateDropBind
108 from ._typing import _DDLColumnArgument
109 from ._typing import _DDLColumnReferenceArgument
110 from ._typing import _InfoType
111 from ._typing import _TextCoercedExpressionArgument
112 from ._typing import _TypeEngineArgument
113 from .base import ColumnSet
114 from .base import ReadOnlyColumnCollection
115 from .compiler import DDLCompiler
116 from .ddl import TableCreateDDL
117 from .ddl import TableDropDDL
118 from .elements import BindParameter
119 from .elements import KeyedColumnElement
120 from .functions import Function
121 from .sqltypes import SchemaType
122 from .type_api import TypeEngine
123 from .visitors import anon_map
124 from ..engine import Connection
125 from ..engine import Engine
126 from ..engine.interfaces import _CoreMultiExecuteParams
127 from ..engine.interfaces import CoreExecuteOptionsParameter
128 from ..engine.interfaces import ExecutionContext
129 from ..engine.reflection import _ReflectionInfo
130 from ..sql.selectable import FromClause
132_SI = TypeVar("_SI", bound="SchemaItem")
133_TAB = TypeVar("_TAB", bound="Table")
136_ConstraintNameArgument = Optional[Union[str, _NoneName]]
138_ServerDefaultArgument = Union[
139 "FetchedValue", str, TextClause, ColumnElement[Any]
140]
142_ServerOnUpdateArgument = _ServerDefaultArgument
145class SchemaConst(Enum):
146 RETAIN_SCHEMA = 1
147 """Symbol indicating that a :class:`_schema.Table`, :class:`.Sequence`
148 or in some cases a :class:`_schema.ForeignKey` object, in situations
149 where the object is being copied for a :meth:`.Table.to_metadata`
150 operation, should retain the schema name that it already has.
152 """
154 BLANK_SCHEMA = 2
155 """Symbol indicating that a :class:`_schema.Table` or :class:`.Sequence`
156 should have 'None' for its schema, even if the parent
157 :class:`_schema.MetaData` has specified a schema.
159 .. seealso::
161 :paramref:`_schema.MetaData.schema`
163 :paramref:`_schema.Table.schema`
165 :paramref:`.Sequence.schema`
167 """
169 NULL_UNSPECIFIED = 3
170 """Symbol indicating the "nullable" keyword was not passed to a Column.
172 This is used to distinguish between the use case of passing
173 ``nullable=None`` to a :class:`.Column`, which has special meaning
174 on some backends such as SQL Server.
176 """
179RETAIN_SCHEMA: Final[Literal[SchemaConst.RETAIN_SCHEMA]] = (
180 SchemaConst.RETAIN_SCHEMA
181)
182BLANK_SCHEMA: Final[Literal[SchemaConst.BLANK_SCHEMA]] = (
183 SchemaConst.BLANK_SCHEMA
184)
185NULL_UNSPECIFIED: Final[Literal[SchemaConst.NULL_UNSPECIFIED]] = (
186 SchemaConst.NULL_UNSPECIFIED
187)
190def _get_table_key(name: str, schema: Optional[str]) -> str:
191 if schema is None:
192 return name
193 else:
194 return schema + "." + name
197# this should really be in sql/util.py but we'd have to
198# break an import cycle
199def _copy_expression(
200 expression: ColumnElement[Any],
201 source_table: Optional[Table],
202 target_table: Optional[Table],
203) -> ColumnElement[Any]:
204 if source_table is None or target_table is None:
205 return expression
207 fixed_source_table = source_table
208 fixed_target_table = target_table
210 def replace(
211 element: ExternallyTraversible, **kw: Any
212 ) -> Optional[ExternallyTraversible]:
213 if (
214 isinstance(element, Column)
215 and element.table is fixed_source_table
216 and element.key in fixed_source_table.c
217 ):
218 return fixed_target_table.c[element.key]
219 else:
220 return None
222 return cast(
223 ColumnElement[Any],
224 visitors.replacement_traverse(expression, {}, replace),
225 )
228@inspection._self_inspects
229class SchemaItem(SchemaVisitable):
230 """Base class for items that define a database schema."""
232 __visit_name__ = "schema_item"
234 create_drop_stringify_dialect = "default"
236 def _init_items(self, *args: SchemaItem, **kw: Any) -> None:
237 """Initialize the list of child items for this SchemaItem."""
238 for item in args:
239 if item is not None:
240 try:
241 spwd = item._set_parent_with_dispatch
242 except AttributeError as err:
243 raise exc.ArgumentError(
244 "'SchemaItem' object, such as a 'Column' or a "
245 f"'Constraint' expected, got {item!r}"
246 ) from err
247 else:
248 spwd(self, **kw)
250 def __repr__(self) -> str:
251 return util.generic_repr(self, omit_kwarg=["info"])
253 @util.memoized_property
254 def info(self) -> _InfoType:
255 """Info dictionary associated with the object, allowing user-defined
256 data to be associated with this :class:`.SchemaItem`.
258 The dictionary is automatically generated when first accessed.
259 It can also be specified in the constructor of some objects,
260 such as :class:`_schema.Table` and :class:`_schema.Column`.
262 """
263 return {}
265 def _schema_item_copy(self, schema_item: _SI) -> _SI:
266 if "info" in self.__dict__:
267 schema_item.info = self.info.copy()
268 schema_item.dispatch._update(self.dispatch)
269 return schema_item
271 _use_schema_map = True
274class HasConditionalDDL:
275 """define a class that includes the :meth:`.HasConditionalDDL.ddl_if`
276 method, allowing for conditional rendering of DDL.
278 Currently applies to constraints and indexes.
280 .. versionadded:: 2.0
283 """
285 _ddl_if: Optional[ddl.DDLIf] = None
287 def ddl_if(
288 self,
289 dialect: Optional[str] = None,
290 callable_: Optional[ddl.DDLIfCallable] = None,
291 state: Optional[Any] = None,
292 ) -> Self:
293 r"""apply a conditional DDL rule to this schema item.
295 These rules work in a similar manner to the
296 :meth:`.ExecutableDDLElement.execute_if` callable, with the added
297 feature that the criteria may be checked within the DDL compilation
298 phase for a construct such as :class:`.CreateTable`.
299 :meth:`.HasConditionalDDL.ddl_if` currently applies towards the
300 :class:`.Index` construct as well as all :class:`.Constraint`
301 constructs.
303 :param dialect: string name of a dialect, or a tuple of string names
304 to indicate multiple dialect types.
306 :param callable\_: a callable that is constructed using the same form
307 as that described in
308 :paramref:`.ExecutableDDLElement.execute_if.callable_`.
310 :param state: any arbitrary object that will be passed to the
311 callable, if present.
313 .. versionadded:: 2.0
315 .. seealso::
317 :ref:`schema_ddl_ddl_if` - background and usage examples
320 """
321 self._ddl_if = ddl.DDLIf(dialect, callable_, state)
322 return self
325class HasSchemaAttr(SchemaItem):
326 """schema item that includes a top-level schema name"""
328 schema: Optional[str]
331class Table(
332 DialectKWArgs,
333 HasSchemaAttr,
334 TableClause[_ColCC_co],
335 inspection.Inspectable["Table"],
336):
337 r"""Represent a table in a database.
339 e.g.::
341 from sqlalchemy import Table, MetaData, Integer, String, Column
343 metadata = MetaData()
345 mytable = Table(
346 "mytable",
347 metadata,
348 Column("mytable_id", Integer, primary_key=True),
349 Column("value", String(50)),
350 )
352 The :class:`_schema.Table`
353 object constructs a unique instance of itself based
354 on its name and optional schema name within the given
355 :class:`_schema.MetaData` object. Calling the :class:`_schema.Table`
356 constructor with the same name and same :class:`_schema.MetaData` argument
357 a second time will return the *same* :class:`_schema.Table`
358 object - in this way
359 the :class:`_schema.Table` constructor acts as a registry function.
361 May also be defined as "typed table" by passing a subclass of
362 :class:`_schema.TypedColumns` as the 3rd argument::
364 from sqlalchemy import TypedColumns, select
367 class user_cols(TypedColumns):
368 id = Column(Integer, primary_key=True)
369 name: Column[str]
370 age: Column[int]
371 middle_name: Column[str | None]
373 # optional, used to infer the select types when selecting the table
374 __row_pos__: tuple[int, str, int, str | None]
377 user = Table("user", metadata, user_cols)
379 # the columns are typed: the statement has type Select[int, str]
380 stmt = sa.select(user.c.id, user.c.name).where(user.c.age > 30)
382 # Inferred as Select[int, str, int, str | None] thanks to __row_pos__
383 stmt1 = user.select()
384 stmt2 = sa.select(user)
386 The :attr:`sqlalchemy.sql._annotated_cols.HasRowPos.__row_pos__`
387 annotation is optional, and it's used to infer the types in a
388 :class:`_sql.Select` when selecting the complete table.
389 If a :class:`_schema.TypedColumns` does not define it,
390 the default ``Select[*tuple[Any]]`` will be inferred.
392 An existing :class:`Table` can be casted as "typed table" using
393 the :meth:`Table.with_cols`::
395 class mytable_cols(TypedColumns):
396 mytable_id: Column[int]
397 value: Column[str | None]
400 typed_mytable = mytable.with_cols(mytable_cols)
402 .. seealso::
404 :ref:`metadata_describing` Introduction to database metadata
406 :class:`_schema.TypedColumns` More information about typed column
407 definition
409 .. versionchanged:: 2.1.0b2 - :class:`_schema.Table` is now generic to
410 support "typed tables"
411 """
413 __visit_name__ = "table"
415 if TYPE_CHECKING:
417 @util.ro_non_memoized_property
418 def primary_key(self) -> PrimaryKeyConstraint: ...
420 @util.ro_non_memoized_property
421 def foreign_keys(self) -> Set[ForeignKey]: ...
423 def with_cols(self, type_: type[_TC_co]) -> Table[_TC_co]: ...
425 _columns: DedupeColumnCollection[Column[Any]] # type: ignore[assignment]
427 _sentinel_column: Optional[Column[Any]]
429 constraints: Set[Constraint]
430 """A collection of all :class:`_schema.Constraint` objects associated with
431 this :class:`_schema.Table`.
433 Includes :class:`_schema.PrimaryKeyConstraint`,
434 :class:`_schema.ForeignKeyConstraint`, :class:`_schema.UniqueConstraint`,
435 :class:`_schema.CheckConstraint`. A separate collection
436 :attr:`_schema.Table.foreign_key_constraints` refers to the collection
437 of all :class:`_schema.ForeignKeyConstraint` objects, and the
438 :attr:`_schema.Table.primary_key` attribute refers to the single
439 :class:`_schema.PrimaryKeyConstraint` associated with the
440 :class:`_schema.Table`.
442 .. seealso::
444 :attr:`_schema.Table.constraints`
446 :attr:`_schema.Table.primary_key`
448 :attr:`_schema.Table.foreign_key_constraints`
450 :attr:`_schema.Table.indexes`
452 :class:`_reflection.Inspector`
455 """
457 indexes: Set[Index]
458 """A collection of all :class:`_schema.Index` objects associated with this
459 :class:`_schema.Table`.
461 .. seealso::
463 :meth:`_reflection.Inspector.get_indexes`
465 """
467 def _gen_cache_key(
468 self, anon_map: anon_map, bindparams: List[BindParameter[Any]]
469 ) -> Tuple[Any, ...]:
470 if self._annotations:
471 return (self,) + self._annotations_cache_key
472 else:
473 return (self,)
475 if not typing.TYPE_CHECKING:
476 # typing tools seem to be inconsistent in how they handle
477 # __new__, so suggest this pattern for classes that use
478 # __new__. apply typing to the __init__ method normally
479 @util.deprecated_params(
480 mustexist=(
481 "1.4",
482 "Deprecated alias of :paramref:`_schema.Table.must_exist`",
483 ),
484 )
485 def __new__(cls, *args: Any, **kw: Any) -> Any:
486 return cls._new(*args, **kw)
488 @classmethod
489 def _new(cls, *args: Any, **kw: Any) -> Any:
490 if not args and not kw:
491 # python3k pickle seems to call this
492 return object.__new__(cls)
494 try:
495 name, metadata, *other_args = args
496 except ValueError:
497 raise TypeError(
498 "Table() takes at least two positional-only "
499 "arguments 'name', and 'metadata'"
500 ) from None
501 if other_args and isinstance(other_args[0], type):
502 typed_columns_cls = other_args[0]
503 if not issubclass(typed_columns_cls, TypedColumns):
504 raise exc.InvalidRequestError(
505 "The ``typed_columns_cls`` argument requires a "
506 "TypedColumns subclass."
507 )
508 elif hasattr(typed_columns_cls, "_sa_class_manager"):
509 # an orm class subclassed with TypedColumns. Reject it
510 raise exc.InvalidRequestError(
511 "To get a typed table from an ORM class, use the "
512 "`as_typed_table()` function instead."
513 )
515 extracted_columns = _extract_columns_from_class(typed_columns_cls)
516 other_args = extracted_columns + other_args[1:]
517 elif "typed_columns_cls" in kw:
518 raise TypeError(
519 "The ``typed_columns_cls`` argument may be passed "
520 "only positionally"
521 )
523 schema = kw.get("schema", None)
524 if schema is None:
525 schema = metadata.schema
526 elif schema is BLANK_SCHEMA:
527 schema = None
528 keep_existing = kw.get("keep_existing", False)
529 extend_existing = kw.get("extend_existing", False)
531 if keep_existing and extend_existing:
532 msg = "keep_existing and extend_existing are mutually exclusive."
533 raise exc.ArgumentError(msg)
535 must_exist = kw.pop("must_exist", kw.pop("mustexist", False))
536 key = _get_table_key(name, schema)
537 if key in metadata.tables:
538 if not keep_existing and not extend_existing and bool(other_args):
539 raise exc.InvalidRequestError(
540 f"Table '{key}' is already defined for this MetaData "
541 "instance. Specify 'extend_existing=True' "
542 "to redefine "
543 "options and columns on an "
544 "existing Table object."
545 )
546 table = metadata.tables[key]
547 if extend_existing:
548 table._init_existing(*other_args, **kw)
549 return table
550 else:
551 if must_exist:
552 raise exc.InvalidRequestError(f"Table '{key}' not defined")
553 table = object.__new__(cls)
554 table.dispatch.before_parent_attach(table, metadata)
555 metadata._add_table(name, schema, table)
556 try:
557 table.__init__(name, metadata, *other_args, _no_init=False, **kw) # type: ignore[misc] # noqa: E501
558 table.dispatch.after_parent_attach(table, metadata)
559 return table
560 except Exception:
561 with util.safe_reraise():
562 metadata._remove_table(name, schema)
564 @overload
565 def __init__(
566 self: Table[_TC_co],
567 name: str,
568 metadata: MetaData,
569 typed_columns_cls: type[_TC_co],
570 /,
571 *args: SchemaItem,
572 schema: str | Literal[SchemaConst.BLANK_SCHEMA] | None = None,
573 quote: bool | None = None,
574 quote_schema: bool | None = None,
575 keep_existing: bool = False,
576 extend_existing: bool = False,
577 implicit_returning: bool = True,
578 comment: str | None = None,
579 info: dict[Any, Any] | None = None,
580 listeners: (
581 _typing_Sequence[tuple[str, Callable[..., Any]]] | None
582 ) = None,
583 prefixes: _typing_Sequence[str] | None = None,
584 **kw: Any,
585 ) -> None: ...
587 @overload
588 def __init__(
589 self: Table[ReadOnlyColumnCollection[str, Column[Any]]],
590 name: str,
591 metadata: MetaData,
592 /,
593 *args: SchemaItem,
594 schema: str | Literal[SchemaConst.BLANK_SCHEMA] | None = None,
595 quote: bool | None = None,
596 quote_schema: Optional[bool] = None,
597 autoload_with: Optional[Union[Engine, Connection]] = None,
598 autoload_replace: bool = True,
599 keep_existing: bool = False,
600 extend_existing: bool = False,
601 resolve_fks: bool = True,
602 include_columns: Optional[Collection[str]] = None,
603 implicit_returning: bool = True,
604 comment: str | None = None,
605 info: dict[Any, Any] | None = None,
606 listeners: (
607 _typing_Sequence[tuple[str, Callable[..., Any]]] | None
608 ) = None,
609 prefixes: _typing_Sequence[str] | None = None,
610 _creator_ddl: TableCreateDDL | None = None,
611 _dropper_ddl: TableDropDDL | None = None,
612 # used internally in the metadata.reflect() process
613 _extend_on: Optional[Set[Table]] = None,
614 # used by __new__ to bypass __init__
615 _no_init: bool = True,
616 # dialect-specific keyword args
617 **kw: Any,
618 ) -> None: ...
620 def __init__(
621 self,
622 name: str,
623 metadata: MetaData,
624 /,
625 *args: Any,
626 schema: str | Literal[SchemaConst.BLANK_SCHEMA] | None = None,
627 quote: bool | None = None,
628 quote_schema: Optional[bool] = None,
629 autoload_with: Optional[Union[Engine, Connection]] = None,
630 autoload_replace: bool = True,
631 keep_existing: bool = False,
632 extend_existing: bool = False,
633 resolve_fks: bool = True,
634 include_columns: Optional[Collection[str]] = None,
635 implicit_returning: bool = True,
636 comment: str | None = None,
637 info: dict[Any, Any] | None = None,
638 listeners: (
639 _typing_Sequence[tuple[str, Callable[..., Any]]] | None
640 ) = None,
641 prefixes: _typing_Sequence[str] | None = None,
642 _creator_ddl: TableCreateDDL | None = None,
643 _dropper_ddl: TableDropDDL | None = None,
644 # used internally in the metadata.reflect() process
645 _extend_on: Optional[Set[Table]] = None,
646 # used by __new__ to bypass __init__
647 _no_init: bool = True,
648 # dialect-specific keyword args
649 **kw: Any,
650 ) -> None:
651 r"""Constructor for :class:`_schema.Table`.
654 :param name: The name of this table as represented in the database.
656 The table name, along with the value of the ``schema`` parameter,
657 forms a key which uniquely identifies this :class:`_schema.Table`
658 within
659 the owning :class:`_schema.MetaData` collection.
660 Additional calls to :class:`_schema.Table` with the same name,
661 metadata,
662 and schema name will return the same :class:`_schema.Table` object.
664 Names which contain no upper case characters
665 will be treated as case insensitive names, and will not be quoted
666 unless they are a reserved word or contain special characters.
667 A name with any number of upper case characters is considered
668 to be case sensitive, and will be sent as quoted.
670 To enable unconditional quoting for the table name, specify the flag
671 ``quote=True`` to the constructor, or use the :class:`.quoted_name`
672 construct to specify the name.
674 :param metadata: a :class:`_schema.MetaData`
675 object which will contain this
676 table. The metadata is used as a point of association of this table
677 with other tables which are referenced via foreign key. It also
678 may be used to associate this table with a particular
679 :class:`.Connection` or :class:`.Engine`.
681 :param table_columns_cls: a subclass of :class:`_schema.TypedColumns`
682 that defines the columns that will be "typed" when accessing
683 them from the :attr:`_schema.Table.c` attribute.
685 .. versionadded:: 2.1.0b2
687 :param \*args: Additional positional arguments are used primarily
688 to add the list of :class:`_schema.Column`
689 objects contained within this
690 table. Similar to the style of a CREATE TABLE statement, other
691 :class:`.SchemaItem` constructs may be added here, including
692 :class:`.PrimaryKeyConstraint`, and
693 :class:`_schema.ForeignKeyConstraint`.
694 Additional columns may be provided also when using a
695 :paramref:`_schema.Table.table_columns_cls` class; they will
696 be appended to the "typed" columns and will appear as untyped
697 when accessing them via the :attr:`_schema.Table.c` collection.
699 :param autoload_replace: Defaults to ``True``; when using
700 :paramref:`_schema.Table.autoload_with`
701 in conjunction with :paramref:`_schema.Table.extend_existing`,
702 indicates
703 that :class:`_schema.Column` objects present in the already-existing
704 :class:`_schema.Table`
705 object should be replaced with columns of the same
706 name retrieved from the autoload process. When ``False``, columns
707 already present under existing names will be omitted from the
708 reflection process.
710 Note that this setting does not impact :class:`_schema.Column` objects
711 specified programmatically within the call to :class:`_schema.Table`
712 that
713 also is autoloading; those :class:`_schema.Column` objects will always
714 replace existing columns of the same name when
715 :paramref:`_schema.Table.extend_existing` is ``True``.
717 .. seealso::
719 :paramref:`_schema.Table.autoload_with`
721 :paramref:`_schema.Table.extend_existing`
723 :param autoload_with: An :class:`_engine.Engine` or
724 :class:`_engine.Connection` object,
725 or a :class:`_reflection.Inspector` object as returned by
726 :func:`_sa.inspect`
727 against one, with which this :class:`_schema.Table`
728 object will be reflected.
729 When set to a non-None value, the autoload process will take place
730 for this table against the given engine or connection.
732 .. seealso::
734 :ref:`metadata_reflection_toplevel`
736 :meth:`_events.DDLEvents.column_reflect`
738 :ref:`metadata_reflection_dbagnostic_types`
740 :param extend_existing: When ``True``, indicates that if this
741 :class:`_schema.Table` is already present in the given
742 :class:`_schema.MetaData`,
743 apply further arguments within the constructor to the existing
744 :class:`_schema.Table`.
746 If :paramref:`_schema.Table.extend_existing` or
747 :paramref:`_schema.Table.keep_existing` are not set,
748 and the given name
749 of the new :class:`_schema.Table` refers to a :class:`_schema.Table`
750 that is
751 already present in the target :class:`_schema.MetaData` collection,
752 and
753 this :class:`_schema.Table`
754 specifies additional columns or other constructs
755 or flags that modify the table's state, an
756 error is raised. The purpose of these two mutually-exclusive flags
757 is to specify what action should be taken when a
758 :class:`_schema.Table`
759 is specified that matches an existing :class:`_schema.Table`,
760 yet specifies
761 additional constructs.
763 :paramref:`_schema.Table.extend_existing`
764 will also work in conjunction
765 with :paramref:`_schema.Table.autoload_with` to run a new reflection
766 operation against the database, even if a :class:`_schema.Table`
767 of the same name is already present in the target
768 :class:`_schema.MetaData`; newly reflected :class:`_schema.Column`
769 objects
770 and other options will be added into the state of the
771 :class:`_schema.Table`, potentially overwriting existing columns
772 and options of the same name.
774 As is always the case with :paramref:`_schema.Table.autoload_with`,
775 :class:`_schema.Column` objects can be specified in the same
776 :class:`_schema.Table`
777 constructor, which will take precedence. Below, the existing
778 table ``mytable`` will be augmented with :class:`_schema.Column`
779 objects
780 both reflected from the database, as well as the given
781 :class:`_schema.Column`
782 named "y"::
784 Table(
785 "mytable",
786 metadata,
787 Column("y", Integer),
788 extend_existing=True,
789 autoload_with=engine,
790 )
792 .. seealso::
794 :paramref:`_schema.Table.autoload_with`
796 :paramref:`_schema.Table.autoload_replace`
798 :paramref:`_schema.Table.keep_existing`
801 :param implicit_returning: True by default - indicates that
802 RETURNING can be used, typically by the ORM, in order to fetch
803 server-generated values such as primary key values and
804 server side defaults, on those backends which support RETURNING.
806 In modern SQLAlchemy there is generally no reason to alter this
807 setting, except for some backend specific cases
808 (see :ref:`mssql_triggers` in the SQL Server dialect documentation
809 for one such example).
811 :param include_columns: A list of strings indicating a subset of
812 columns to be loaded via the ``autoload`` operation; table columns who
813 aren't present in this list will not be represented on the resulting
814 ``Table`` object. Defaults to ``None`` which indicates all columns
815 should be reflected.
817 :param resolve_fks: Whether or not to reflect :class:`_schema.Table`
818 objects
819 related to this one via :class:`_schema.ForeignKey` objects, when
820 :paramref:`_schema.Table.autoload_with` is
821 specified. Defaults to True. Set to False to disable reflection of
822 related tables as :class:`_schema.ForeignKey`
823 objects are encountered; may be
824 used either to save on SQL calls or to avoid issues with related tables
825 that can't be accessed. Note that if a related table is already present
826 in the :class:`_schema.MetaData` collection, or becomes present later,
827 a
828 :class:`_schema.ForeignKey` object associated with this
829 :class:`_schema.Table` will
830 resolve to that table normally.
832 .. seealso::
834 :paramref:`.MetaData.reflect.resolve_fks`
837 :param info: Optional data dictionary which will be populated into the
838 :attr:`.SchemaItem.info` attribute of this object.
840 :param keep_existing: When ``True``, indicates that if this Table
841 is already present in the given :class:`_schema.MetaData`, ignore
842 further arguments within the constructor to the existing
843 :class:`_schema.Table`, and return the :class:`_schema.Table`
844 object as
845 originally created. This is to allow a function that wishes
846 to define a new :class:`_schema.Table` on first call, but on
847 subsequent calls will return the same :class:`_schema.Table`,
848 without any of the declarations (particularly constraints)
849 being applied a second time.
851 If :paramref:`_schema.Table.extend_existing` or
852 :paramref:`_schema.Table.keep_existing` are not set,
853 and the given name
854 of the new :class:`_schema.Table` refers to a :class:`_schema.Table`
855 that is
856 already present in the target :class:`_schema.MetaData` collection,
857 and
858 this :class:`_schema.Table`
859 specifies additional columns or other constructs
860 or flags that modify the table's state, an
861 error is raised. The purpose of these two mutually-exclusive flags
862 is to specify what action should be taken when a
863 :class:`_schema.Table`
864 is specified that matches an existing :class:`_schema.Table`,
865 yet specifies
866 additional constructs.
868 .. seealso::
870 :paramref:`_schema.Table.extend_existing`
872 :param listeners: A list of tuples of the form ``(<eventname>, <fn>)``
873 which will be passed to :func:`.event.listen` upon construction.
874 This alternate hook to :func:`.event.listen` allows the establishment
875 of a listener function specific to this :class:`_schema.Table` before
876 the "autoload" process begins. Historically this has been intended
877 for use with the :meth:`.DDLEvents.column_reflect` event, however
878 note that this event hook may now be associated with the
879 :class:`_schema.MetaData` object directly::
881 def listen_for_reflect(table, column_info):
882 "handle the column reflection event"
883 # ...
886 t = Table(
887 "sometable",
888 autoload_with=engine,
889 listeners=[("column_reflect", listen_for_reflect)],
890 )
892 .. seealso::
894 :meth:`_events.DDLEvents.column_reflect`
896 :param must_exist: When ``True``, indicates that this Table must already
897 be present in the given :class:`_schema.MetaData` collection, else
898 an exception is raised.
900 :param prefixes:
901 A list of strings to insert after CREATE in the CREATE TABLE
902 statement. They will be separated by spaces.
904 :param quote: Force quoting of this table's name on or off, corresponding
905 to ``True`` or ``False``. When left at its default of ``None``,
906 the column identifier will be quoted according to whether the name is
907 case sensitive (identifiers with at least one upper case character are
908 treated as case sensitive), or if it's a reserved word. This flag
909 is only needed to force quoting of a reserved word which is not known
910 by the SQLAlchemy dialect.
912 .. note:: setting this flag to ``False`` will not provide
913 case-insensitive behavior for table reflection; table reflection
914 will always search for a mixed-case name in a case sensitive
915 fashion. Case insensitive names are specified in SQLAlchemy only
916 by stating the name with all lower case characters.
918 :param quote_schema: same as 'quote' but applies to the schema identifier.
920 :param schema: The schema name for this table, which is required if
921 the table resides in a schema other than the default selected schema
922 for the engine's database connection. Defaults to ``None``.
924 If the owning :class:`_schema.MetaData` of this :class:`_schema.Table`
925 specifies its
926 own :paramref:`_schema.MetaData.schema` parameter,
927 then that schema name will
928 be applied to this :class:`_schema.Table`
929 if the schema parameter here is set
930 to ``None``. To set a blank schema name on a :class:`_schema.Table`
931 that
932 would otherwise use the schema set on the owning
933 :class:`_schema.MetaData`,
934 specify the special symbol :attr:`.BLANK_SCHEMA`.
936 The quoting rules for the schema name are the same as those for the
937 ``name`` parameter, in that quoting is applied for reserved words or
938 case-sensitive names; to enable unconditional quoting for the schema
939 name, specify the flag ``quote_schema=True`` to the constructor, or use
940 the :class:`.quoted_name` construct to specify the name.
942 :param comment: Optional string that will render an SQL comment on table
943 creation.
945 :param \**kw: Additional keyword arguments not mentioned above are
946 dialect specific, and passed in the form ``<dialectname>_<argname>``.
947 See the documentation regarding an individual dialect at
948 :ref:`dialect_toplevel` for detail on documented arguments.
950 """ # noqa: E501
951 if _no_init:
952 # don't run __init__ from __new__ by default;
953 # __new__ has a specific place that __init__ is called
954 return
955 if args:
956 # this is the call done by `__new__` that should have resolved
957 # TypedColumns to the individual columns
958 assert not (
959 isinstance(args[0], type) and issubclass(args[0], TypedColumns)
960 )
962 super().__init__(quoted_name(name, quote))
963 self.metadata = metadata
965 if schema is None:
966 self.schema = metadata.schema
967 elif schema is BLANK_SCHEMA:
968 self.schema = None
969 else:
970 quote_schema = quote_schema
971 assert isinstance(schema, str)
972 self.schema = quoted_name(schema, quote_schema)
974 self._sentinel_column = None
975 self._creator_ddl = _creator_ddl
976 self._dropper_ddl = _dropper_ddl
978 self.indexes = set()
979 self.constraints = set()
980 PrimaryKeyConstraint(
981 _implicit_generated=True
982 )._set_parent_with_dispatch(self)
983 self.foreign_keys = set() # type: ignore[misc]
984 self._extra_dependencies: Set[Table] = set()
985 if self.schema is not None:
986 self.fullname = "%s.%s" % (self.schema, self.name)
987 else:
988 self.fullname = self.name
990 self.implicit_returning = implicit_returning
991 _reflect_info = kw.pop("_reflect_info", None)
993 self.comment = comment
995 if info is not None:
996 self.info = info
998 if listeners is not None:
999 for evt, fn in listeners:
1000 event.listen(self, evt, fn)
1002 self._prefixes = prefixes if prefixes else []
1004 self._extra_kwargs(**kw)
1006 # load column definitions from the database if 'autoload' is defined
1007 # we do it after the table is in the singleton dictionary to support
1008 # circular foreign keys
1009 if autoload_with is not None:
1010 self._autoload(
1011 metadata,
1012 autoload_with,
1013 include_columns,
1014 _extend_on=_extend_on,
1015 _reflect_info=_reflect_info,
1016 resolve_fks=resolve_fks,
1017 )
1019 # initialize all the column, etc. objects. done after reflection to
1020 # allow user-overrides
1022 self._init_items(
1023 *args,
1024 allow_replacements=extend_existing
1025 or keep_existing
1026 or autoload_with,
1027 all_names={},
1028 )
1030 def set_creator_ddl(self, ddl: TableCreateDDL) -> None:
1031 """Set the table create DDL for this :class:`.Table`.
1033 This allows the CREATE TABLE statement to be controlled or replaced
1034 entirely when :meth:`.Table.create` or :meth:`.MetaData.create_all` is
1035 used.
1037 E.g.::
1039 from sqlalchemy.schema import CreateTable
1041 table.set_creator_ddl(CreateTable(table, if_not_exists=True))
1043 .. versionadded:: 2.1
1045 .. seealso::
1047 :meth:`.Table.set_dropper_ddl`
1049 """
1050 self._creator_ddl = ddl
1052 def set_dropper_ddl(self, ddl: TableDropDDL) -> None:
1053 """Set the table drop DDL for this :class:`.Table`.
1055 This allows the DROP TABLE statement to be controlled or replaced
1056 entirely when :meth:`.Table.drop` or :meth:`.MetaData.drop_all` is
1057 used.
1059 E.g.::
1061 from sqlalchemy.schema import DropTable
1063 table.set_dropper_ddl(DropTable(table, if_exists=True))
1065 .. versionadded:: 2.1
1067 .. seealso::
1069 :meth:`.Table.set_creator_ddl`
1071 """
1072 self._dropper_ddl = ddl
1074 @property
1075 def is_view(self) -> bool:
1076 """True if this table, when DDL for CREATE is emitted, will emit
1077 CREATE VIEW rather than CREATE TABLE.
1079 .. versionadded:: 2.1
1081 """
1082 return isinstance(self._creator_ddl, ddl.CreateView)
1084 def _autoload(
1085 self,
1086 metadata: MetaData,
1087 autoload_with: Union[Engine, Connection],
1088 include_columns: Optional[Collection[str]],
1089 exclude_columns: Collection[str] = (),
1090 resolve_fks: bool = True,
1091 _extend_on: Optional[Set[Table]] = None,
1092 _reflect_info: _ReflectionInfo | None = None,
1093 ) -> None:
1094 insp = inspection.inspect(autoload_with)
1095 with insp._inspection_context() as conn_insp:
1096 conn_insp.reflect_table(
1097 self,
1098 include_columns,
1099 exclude_columns,
1100 resolve_fks,
1101 _extend_on=_extend_on,
1102 _reflect_info=_reflect_info,
1103 )
1105 @property
1106 def _sorted_constraints(self) -> List[Constraint]:
1107 """Return the set of constraints as a list, sorted by creation
1108 order.
1110 """
1112 return sorted(self.constraints, key=lambda c: c._creation_order)
1114 @property
1115 def foreign_key_constraints(self) -> Set[ForeignKeyConstraint]:
1116 """:class:`_schema.ForeignKeyConstraint` objects referred to by this
1117 :class:`_schema.Table`.
1119 This list is produced from the collection of
1120 :class:`_schema.ForeignKey`
1121 objects currently associated.
1124 .. seealso::
1126 :attr:`_schema.Table.constraints`
1128 :attr:`_schema.Table.foreign_keys`
1130 :attr:`_schema.Table.indexes`
1132 """
1133 return {
1134 fkc.constraint
1135 for fkc in self.foreign_keys
1136 if fkc.constraint is not None
1137 }
1139 def _init_existing(self, *args: Any, **kwargs: Any) -> None:
1140 autoload_with = kwargs.pop("autoload_with", None)
1141 autoload = kwargs.pop("autoload", autoload_with is not None)
1142 autoload_replace = kwargs.pop("autoload_replace", True)
1143 schema = kwargs.pop("schema", None)
1144 _extend_on = kwargs.pop("_extend_on", None)
1145 _reflect_info = kwargs.pop("_reflect_info", None)
1147 # these arguments are only used with _init()
1148 extend_existing = kwargs.pop("extend_existing", False)
1149 keep_existing = kwargs.pop("keep_existing", False)
1151 assert extend_existing
1152 assert not keep_existing
1154 if schema and schema != self.schema:
1155 raise exc.ArgumentError(
1156 f"Can't change schema of existing table "
1157 f"from '{self.schema}' to '{schema}'",
1158 )
1160 include_columns = kwargs.pop("include_columns", None)
1161 if include_columns is not None:
1162 for c in self.c:
1163 if c.name not in include_columns:
1164 self._columns.remove(c)
1166 resolve_fks = kwargs.pop("resolve_fks", True)
1168 for key in ("quote", "quote_schema"):
1169 if key in kwargs:
1170 raise exc.ArgumentError(
1171 "Can't redefine 'quote' or 'quote_schema' arguments"
1172 )
1174 # update `self` with these kwargs, if provided
1175 self.comment = kwargs.pop("comment", self.comment)
1176 self.implicit_returning = kwargs.pop(
1177 "implicit_returning", self.implicit_returning
1178 )
1179 self.info = kwargs.pop("info", self.info)
1181 exclude_columns: _typing_Sequence[str]
1183 if autoload:
1184 if not autoload_replace:
1185 # don't replace columns already present.
1186 # we'd like to do this for constraints also however we don't
1187 # have simple de-duping for unnamed constraints.
1188 exclude_columns = [c.name for c in self.c]
1189 else:
1190 exclude_columns = ()
1191 self._autoload(
1192 self.metadata,
1193 autoload_with,
1194 include_columns,
1195 exclude_columns,
1196 resolve_fks,
1197 _extend_on=_extend_on,
1198 _reflect_info=_reflect_info,
1199 )
1201 all_names = {c.name: c for c in self.c}
1202 self._extra_kwargs(**kwargs)
1203 self._init_items(*args, allow_replacements=True, all_names=all_names)
1205 def _extra_kwargs(self, **kwargs: Any) -> None:
1206 self._validate_dialect_kwargs(kwargs)
1208 def _init_collections(self) -> None:
1209 pass
1211 def _reset_exported(self) -> None:
1212 pass
1214 @util.ro_non_memoized_property
1215 def _autoincrement_column(self) -> Optional[Column[int]]:
1216 return self.primary_key._autoincrement_column
1218 @util.ro_memoized_property
1219 def _sentinel_column_characteristics(
1220 self,
1221 ) -> _SentinelColumnCharacterization:
1222 """determine a candidate column (or columns, in case of a client
1223 generated composite primary key) which can be used as an
1224 "insert sentinel" for an INSERT statement.
1226 The returned structure, :class:`_SentinelColumnCharacterization`,
1227 includes all the details needed by :class:`.Dialect` and
1228 :class:`.SQLCompiler` to determine if these column(s) can be used
1229 as an INSERT..RETURNING sentinel for a particular database
1230 dialect.
1232 .. versionadded:: 2.0.10
1234 """
1236 sentinel_is_explicit = False
1237 sentinel_is_autoinc = False
1238 the_sentinel: Optional[_typing_Sequence[Column[Any]]] = None
1240 # see if a column was explicitly marked "insert_sentinel=True".
1241 explicit_sentinel_col = self._sentinel_column
1243 if explicit_sentinel_col is not None:
1244 the_sentinel = (explicit_sentinel_col,)
1245 sentinel_is_explicit = True
1247 autoinc_col = self._autoincrement_column
1248 if sentinel_is_explicit and explicit_sentinel_col is autoinc_col:
1249 assert autoinc_col is not None
1250 sentinel_is_autoinc = True
1251 elif explicit_sentinel_col is None and autoinc_col is not None:
1252 the_sentinel = (autoinc_col,)
1253 sentinel_is_autoinc = True
1255 default_characterization = _SentinelDefaultCharacterization.UNKNOWN
1257 if the_sentinel:
1258 the_sentinel_zero = the_sentinel[0]
1259 if the_sentinel_zero.identity:
1260 if the_sentinel_zero.identity._increment_is_negative:
1261 if sentinel_is_explicit:
1262 raise exc.InvalidRequestError(
1263 "Can't use IDENTITY default with negative "
1264 "increment as an explicit sentinel column"
1265 )
1266 else:
1267 if sentinel_is_autoinc:
1268 autoinc_col = None
1269 sentinel_is_autoinc = False
1270 the_sentinel = None
1271 else:
1272 default_characterization = (
1273 _SentinelDefaultCharacterization.IDENTITY
1274 )
1275 elif (
1276 the_sentinel_zero.default is None
1277 and the_sentinel_zero.server_default is None
1278 ):
1279 if the_sentinel_zero.nullable:
1280 raise exc.InvalidRequestError(
1281 f"Column {the_sentinel_zero} has been marked as a "
1282 "sentinel "
1283 "column with no default generation function; it "
1284 "at least needs to be marked nullable=False assuming "
1285 "user-populated sentinel values will be used."
1286 )
1287 default_characterization = (
1288 _SentinelDefaultCharacterization.NONE
1289 )
1290 elif the_sentinel_zero.default is not None:
1291 if the_sentinel_zero.default.is_sentinel:
1292 default_characterization = (
1293 _SentinelDefaultCharacterization.SENTINEL_DEFAULT
1294 )
1295 elif the_sentinel_zero.default._is_monotonic_fn:
1296 default_characterization = (
1297 _SentinelDefaultCharacterization.MONOTONIC_FUNCTION
1298 )
1299 elif default_is_sequence(the_sentinel_zero.default):
1300 if the_sentinel_zero.default._increment_is_negative:
1301 if sentinel_is_explicit:
1302 raise exc.InvalidRequestError(
1303 "Can't use SEQUENCE default with negative "
1304 "increment as an explicit sentinel column"
1305 )
1306 else:
1307 if sentinel_is_autoinc:
1308 autoinc_col = None
1309 sentinel_is_autoinc = False
1310 the_sentinel = None
1312 default_characterization = (
1313 _SentinelDefaultCharacterization.SEQUENCE
1314 )
1315 elif the_sentinel_zero.default.is_callable:
1316 default_characterization = (
1317 _SentinelDefaultCharacterization.CLIENTSIDE
1318 )
1319 elif the_sentinel_zero.server_default is not None:
1320 if sentinel_is_explicit:
1321 if not the_sentinel_zero.server_default._is_monotonic_fn:
1322 raise exc.InvalidRequestError(
1323 f"Column {the_sentinel[0]} can't be a sentinel "
1324 "column "
1325 "because it uses an explicit server side default "
1326 "that's not the Identity() default."
1327 )
1328 else:
1329 default_characterization = (
1330 _SentinelDefaultCharacterization.MONOTONIC_FUNCTION
1331 )
1332 else:
1333 default_characterization = (
1334 _SentinelDefaultCharacterization.SERVERSIDE
1335 )
1337 if the_sentinel is None and self.primary_key:
1338 assert autoinc_col is None
1340 # determine for non-autoincrement pk if all elements are
1341 # client side
1342 for _pkc in self.primary_key:
1343 if (
1344 _pkc.server_default is not None
1345 and not _pkc.server_default._is_monotonic_fn
1346 ):
1347 break
1349 if (
1350 _pkc.default
1351 and not _pkc.default.is_callable
1352 and not _pkc.default._is_monotonic_fn
1353 ):
1354 break
1355 else:
1356 the_sentinel = tuple(self.primary_key)
1357 default_characterization = (
1358 _SentinelDefaultCharacterization.CLIENTSIDE
1359 )
1361 return _SentinelColumnCharacterization(
1362 the_sentinel,
1363 sentinel_is_explicit,
1364 sentinel_is_autoinc,
1365 default_characterization,
1366 )
1368 @property
1369 def autoincrement_column(self) -> Optional[Column[int]]:
1370 """Returns the :class:`.Column` object which currently represents
1371 the "auto increment" column, if any, else returns None.
1373 This is based on the rules for :class:`.Column` as defined by the
1374 :paramref:`.Column.autoincrement` parameter, which generally means the
1375 column within a single integer column primary key constraint that is
1376 not constrained by a foreign key. If the table does not have such
1377 a primary key constraint, then there's no "autoincrement" column.
1378 A :class:`.Table` may have only one column defined as the
1379 "autoincrement" column.
1381 .. versionadded:: 2.0.4
1383 .. seealso::
1385 :paramref:`.Column.autoincrement`
1387 """
1388 return self._autoincrement_column
1390 @property
1391 def key(self) -> str:
1392 """Return the 'key' for this :class:`_schema.Table`.
1394 This value is used as the dictionary key within the
1395 :attr:`_schema.MetaData.tables` collection. It is typically the same
1396 as that of :attr:`_schema.Table.name` for a table with no
1397 :attr:`_schema.Table.schema`
1398 set; otherwise it is typically of the form
1399 ``schemaname.tablename``.
1401 """
1402 return _get_table_key(self.name, self.schema)
1404 def __repr__(self) -> str:
1405 return "Table(%s)" % ", ".join(
1406 [repr(self.name)]
1407 + [repr(self.metadata)]
1408 + [repr(x) for x in self.columns]
1409 + ["%s=%s" % (k, repr(getattr(self, k))) for k in ["schema"]]
1410 )
1412 def __str__(self) -> str:
1413 return _get_table_key(self.description, self.schema)
1415 def add_is_dependent_on(self, table: Table) -> None:
1416 """Add a 'dependency' for this Table.
1418 This is another Table object which must be created
1419 first before this one can, or dropped after this one.
1421 Usually, dependencies between tables are determined via
1422 ForeignKey objects. However, for other situations that
1423 create dependencies outside of foreign keys (rules, inheriting),
1424 this method can manually establish such a link.
1426 """
1427 self._extra_dependencies.add(table)
1429 def _insert_col_impl(
1430 self,
1431 column: ColumnClause[Any],
1432 *,
1433 index: Optional[int] = None,
1434 replace_existing: bool = False,
1435 ) -> None:
1436 try:
1437 column._set_parent_with_dispatch(
1438 self,
1439 allow_replacements=replace_existing,
1440 all_names={c.name: c for c in self.c},
1441 index=index,
1442 )
1443 except exc.DuplicateColumnError as de:
1444 raise exc.DuplicateColumnError(
1445 f"{de.args[0]} Specify replace_existing=True to "
1446 "Table.append_column() or Table.insert_column() to replace an "
1447 "existing column."
1448 ) from de
1450 def insert_column(
1451 self,
1452 column: ColumnClause[Any],
1453 index: int,
1454 *,
1455 replace_existing: bool = False,
1456 ) -> None:
1457 """Insert a :class:`_schema.Column` to this :class:`_schema.Table` at
1458 a specific position.
1460 Behavior is identical to :meth:`.Table.append_column` except that
1461 the index position can be controlled using the
1462 :paramref:`.Table.insert_column.index`
1463 parameter.
1465 :param replace_existing:
1466 see :paramref:`.Table.append_column.replace_existing`
1467 :param index: integer index to insert the new column.
1469 .. versionadded:: 2.1
1471 """
1472 self._insert_col_impl(
1473 column, index=index, replace_existing=replace_existing
1474 )
1476 def append_column(
1477 self, column: ColumnClause[Any], *, replace_existing: bool = False
1478 ) -> None:
1479 """Append a :class:`_schema.Column` to this :class:`_schema.Table`.
1481 The "key" of the newly added :class:`_schema.Column`, i.e. the
1482 value of its ``.key`` attribute, will then be available
1483 in the ``.c`` collection of this :class:`_schema.Table`, and the
1484 column definition will be included in any CREATE TABLE, SELECT,
1485 UPDATE, etc. statements generated from this :class:`_schema.Table`
1486 construct.
1488 Note that this does **not** change the definition of the table
1489 as it exists within any underlying database, assuming that
1490 table has already been created in the database. Relational
1491 databases support the addition of columns to existing tables
1492 using the SQL ALTER command, which would need to be
1493 emitted for an already-existing table that doesn't contain
1494 the newly added column.
1496 :param replace_existing: When ``True``, allows replacing existing
1497 columns. When ``False``, the default, an warning will be raised
1498 if a column with the same ``.key`` already exists. A future
1499 version of sqlalchemy will instead rise a warning.
1501 .. versionadded:: 1.4.0
1503 .. seealso::
1505 :meth:`.Table.insert_column`
1507 """
1508 self._insert_col_impl(column, replace_existing=replace_existing)
1510 def append_constraint(self, constraint: Union[Index, Constraint]) -> None:
1511 """Append a :class:`_schema.Constraint` to this
1512 :class:`_schema.Table`.
1514 This has the effect of the constraint being included in any
1515 future CREATE TABLE statement, assuming specific DDL creation
1516 events have not been associated with the given
1517 :class:`_schema.Constraint` object.
1519 Note that this does **not** produce the constraint within the
1520 relational database automatically, for a table that already exists
1521 in the database. To add a constraint to an
1522 existing relational database table, the SQL ALTER command must
1523 be used. SQLAlchemy also provides the
1524 :class:`.AddConstraint` construct which can produce this SQL when
1525 invoked as an executable clause.
1527 """
1529 constraint._set_parent_with_dispatch(self)
1531 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
1532 metadata = parent
1533 assert isinstance(metadata, MetaData)
1534 metadata._add_table(self.name, self.schema, self)
1535 self.metadata = metadata
1537 def create(
1538 self,
1539 bind: _CreateDropBind,
1540 checkfirst: Union[bool, CheckFirst] = CheckFirst.TYPES,
1541 ) -> None:
1542 """Issue a ``CREATE`` statement for this
1543 :class:`_schema.Table`, using the given
1544 :class:`.Connection` or :class:`.Engine`
1545 for connectivity.
1547 .. seealso::
1549 :meth:`_schema.MetaData.create_all`.
1551 """
1553 # the default is to only check for schema objects
1554 bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
1556 def drop(
1557 self,
1558 bind: _CreateDropBind,
1559 checkfirst: Union[bool, CheckFirst] = CheckFirst.NONE,
1560 ) -> None:
1561 """Issue a ``DROP`` statement for this
1562 :class:`_schema.Table`, using the given
1563 :class:`.Connection` or :class:`.Engine` for connectivity.
1565 .. seealso::
1567 :meth:`_schema.MetaData.drop_all`.
1569 """
1570 bind._run_ddl_visitor(ddl.SchemaDropper, self, checkfirst=checkfirst)
1572 @util.deprecated(
1573 "1.4",
1574 ":meth:`_schema.Table.tometadata` is renamed to "
1575 ":meth:`_schema.Table.to_metadata`",
1576 )
1577 def tometadata(
1578 self,
1579 metadata: MetaData,
1580 schema: Union[str, Literal[SchemaConst.RETAIN_SCHEMA]] = RETAIN_SCHEMA,
1581 referred_schema_fn: Optional[
1582 Callable[
1583 [Table, Optional[str], ForeignKeyConstraint, Optional[str]],
1584 Optional[str],
1585 ]
1586 ] = None,
1587 name: Optional[str] = None,
1588 ) -> Table[_ColCC_co]:
1589 """Return a copy of this :class:`_schema.Table`
1590 associated with a different
1591 :class:`_schema.MetaData`.
1593 See :meth:`_schema.Table.to_metadata` for a full description.
1595 """
1596 return self.to_metadata(
1597 metadata,
1598 schema=schema,
1599 referred_schema_fn=referred_schema_fn,
1600 name=name,
1601 )
1603 def to_metadata(
1604 self,
1605 metadata: MetaData,
1606 schema: Union[str, Literal[SchemaConst.RETAIN_SCHEMA]] = RETAIN_SCHEMA,
1607 referred_schema_fn: Optional[
1608 Callable[
1609 [Table, Optional[str], ForeignKeyConstraint, Optional[str]],
1610 Optional[str],
1611 ]
1612 ] = None,
1613 name: Optional[str] = None,
1614 ) -> Table[_ColCC_co]:
1615 """Return a copy of this :class:`_schema.Table` associated with a
1616 different :class:`_schema.MetaData`.
1618 E.g.::
1620 m1 = MetaData()
1622 user = Table("user", m1, Column("id", Integer, primary_key=True))
1624 m2 = MetaData()
1625 user_copy = user.to_metadata(m2)
1627 .. versionchanged:: 1.4 The :meth:`_schema.Table.to_metadata` function
1628 was renamed from :meth:`_schema.Table.tometadata`.
1631 :param metadata: Target :class:`_schema.MetaData` object,
1632 into which the
1633 new :class:`_schema.Table` object will be created.
1635 :param schema: optional string name indicating the target schema.
1636 Defaults to the special symbol :attr:`.RETAIN_SCHEMA` which indicates
1637 that no change to the schema name should be made in the new
1638 :class:`_schema.Table`. If set to a string name, the new
1639 :class:`_schema.Table`
1640 will have this new name as the ``.schema``. If set to ``None``, the
1641 schema will be set to that of the schema set on the target
1642 :class:`_schema.MetaData`, which is typically ``None`` as well,
1643 unless
1644 set explicitly::
1646 m2 = MetaData(schema="newschema")
1648 # user_copy_one will have "newschema" as the schema name
1649 user_copy_one = user.to_metadata(m2, schema=None)
1651 m3 = MetaData() # schema defaults to None
1653 # user_copy_two will have None as the schema name
1654 user_copy_two = user.to_metadata(m3, schema=None)
1656 :param referred_schema_fn: optional callable which can be supplied
1657 in order to provide for the schema name that should be assigned
1658 to the referenced table of a :class:`_schema.ForeignKeyConstraint`.
1659 The callable accepts this parent :class:`_schema.Table`, the
1660 target schema that we are changing to, the
1661 :class:`_schema.ForeignKeyConstraint` object, and the existing
1662 "target schema" of that constraint. The function should return the
1663 string schema name that should be applied. To reset the schema
1664 to "none", return the symbol :data:`.BLANK_SCHEMA`. To effect no
1665 change, return ``None`` or :data:`.RETAIN_SCHEMA`.
1667 .. versionchanged:: 1.4.33 The ``referred_schema_fn`` function
1668 may return the :data:`.BLANK_SCHEMA` or :data:`.RETAIN_SCHEMA`
1669 symbols.
1671 E.g.::
1673 def referred_schema_fn(table, to_schema, constraint, referred_schema):
1674 if referred_schema == "base_tables":
1675 return referred_schema
1676 else:
1677 return to_schema
1680 new_table = table.to_metadata(
1681 m2, schema="alt_schema", referred_schema_fn=referred_schema_fn
1682 )
1684 :param name: optional string name indicating the target table name.
1685 If not specified or None, the table name is retained. This allows
1686 a :class:`_schema.Table` to be copied to the same
1687 :class:`_schema.MetaData` target
1688 with a new name.
1690 """ # noqa: E501
1691 if name is None:
1692 name = self.name
1694 actual_schema: Optional[str]
1696 if schema is RETAIN_SCHEMA:
1697 actual_schema = self.schema
1698 elif schema is None:
1699 actual_schema = metadata.schema
1700 else:
1701 actual_schema = schema
1702 key = _get_table_key(name, actual_schema)
1703 if key in metadata.tables:
1704 util.warn(
1705 f"Table '{self.description}' already exists within the given "
1706 "MetaData - not copying."
1707 )
1708 return metadata.tables[key]
1710 args = []
1711 for col in self.columns:
1712 args.append(col._copy(schema=actual_schema, _to_metadata=metadata))
1714 table: Table[_ColCC_co] = Table( # type: ignore[assignment]
1715 name,
1716 metadata,
1717 schema=actual_schema,
1718 comment=self.comment,
1719 *args,
1720 **self.kwargs,
1721 )
1723 if self._creator_ddl is not None:
1724 table._creator_ddl = self._creator_ddl.to_metadata(metadata, table)
1725 if self._dropper_ddl is not None:
1726 table._dropper_ddl = self._dropper_ddl.to_metadata(metadata, table)
1728 for const in self.constraints:
1729 if isinstance(const, ForeignKeyConstraint):
1730 referred_schema = const._referred_schema
1731 if referred_schema_fn:
1732 fk_constraint_schema = referred_schema_fn(
1733 self, actual_schema, const, referred_schema
1734 )
1735 else:
1736 fk_constraint_schema = (
1737 actual_schema
1738 if referred_schema == self.schema
1739 else None
1740 )
1741 table.append_constraint(
1742 const._copy(
1743 schema=fk_constraint_schema, target_table=table
1744 )
1745 )
1746 elif not const._type_bound:
1747 # skip unique constraints that would be generated
1748 # by the 'unique' flag on Column
1749 if const._column_flag:
1750 continue
1752 table.append_constraint(
1753 const._copy(schema=actual_schema, target_table=table)
1754 )
1755 for index in self.indexes:
1756 # skip indexes that would be generated
1757 # by the 'index' flag on Column
1758 if index._column_flag:
1759 continue
1760 Index(
1761 index.name,
1762 unique=index.unique,
1763 *[
1764 _copy_expression(expr, self, table)
1765 for expr in index._table_bound_expressions
1766 ],
1767 _table=table,
1768 **index.kwargs,
1769 )
1770 return self._schema_item_copy(table)
1773class Column(DialectKWArgs, SchemaItem, ColumnClause[_T], Named[_T]):
1774 """Represents a column in a database table."""
1776 __visit_name__ = "column"
1778 inherit_cache = True
1779 key: str
1781 server_default: Optional[FetchedValue]
1783 def __init__(
1784 self,
1785 __name_pos: Optional[
1786 Union[str, _TypeEngineArgument[_T], SchemaEventTarget]
1787 ] = None,
1788 __type_pos: Optional[
1789 Union[_TypeEngineArgument[_T], SchemaEventTarget]
1790 ] = None,
1791 /,
1792 *args: SchemaEventTarget,
1793 name: Optional[str] = None,
1794 type_: Optional[_TypeEngineArgument[_T]] = None,
1795 autoincrement: _AutoIncrementType = "auto",
1796 default: Optional[Any] = _NoArg.NO_ARG,
1797 insert_default: Optional[Any] = _NoArg.NO_ARG,
1798 doc: Optional[str] = None,
1799 key: Optional[str] = None,
1800 index: Optional[bool] = None,
1801 unique: Optional[bool] = None,
1802 info: Optional[_InfoType] = None,
1803 nullable: Optional[
1804 Union[bool, Literal[SchemaConst.NULL_UNSPECIFIED]]
1805 ] = SchemaConst.NULL_UNSPECIFIED,
1806 onupdate: Optional[Any] = None,
1807 primary_key: bool = False,
1808 server_default: Optional[_ServerDefaultArgument] = None,
1809 server_onupdate: Optional[_ServerOnUpdateArgument] = None,
1810 quote: Optional[bool] = None,
1811 system: bool = False,
1812 comment: Optional[str] = None,
1813 insert_sentinel: bool = False,
1814 _omit_from_statements: bool = False,
1815 _proxies: Optional[Any] = None,
1816 **dialect_kwargs: Any,
1817 ):
1818 r"""
1819 Construct a new ``Column`` object.
1821 :param name: The name of this column as represented in the database.
1822 This argument may be the first positional argument, or specified
1823 via keyword.
1825 Names which contain no upper case characters
1826 will be treated as case insensitive names, and will not be quoted
1827 unless they are a reserved word. Names with any number of upper
1828 case characters will be quoted and sent exactly. Note that this
1829 behavior applies even for databases which standardize upper
1830 case names as case insensitive such as Oracle Database.
1832 The name field may be omitted at construction time and applied
1833 later, at any time before the Column is associated with a
1834 :class:`_schema.Table`. This is to support convenient
1835 usage within the :mod:`~sqlalchemy.ext.declarative` extension.
1837 :param type\_: The column's type, indicated using an instance which
1838 subclasses :class:`~sqlalchemy.types.TypeEngine`. If no arguments
1839 are required for the type, the class of the type can be sent
1840 as well, e.g.::
1842 # use a type with arguments
1843 Column("data", String(50))
1845 # use no arguments
1846 Column("level", Integer)
1848 The ``type`` argument may be the second positional argument
1849 or specified by keyword.
1851 If the ``type`` is ``None`` or is omitted, it will first default to
1852 the special type :class:`.NullType`. If and when this
1853 :class:`_schema.Column` is made to refer to another column using
1854 :class:`_schema.ForeignKey` and/or
1855 :class:`_schema.ForeignKeyConstraint`, the type
1856 of the remote-referenced column will be copied to this column as
1857 well, at the moment that the foreign key is resolved against that
1858 remote :class:`_schema.Column` object.
1860 :param \*args: Additional positional arguments include various
1861 :class:`.SchemaItem` derived constructs which will be applied
1862 as options to the column. These include instances of
1863 :class:`.Constraint`, :class:`_schema.ForeignKey`,
1864 :class:`.ColumnDefault`, :class:`.Sequence`, :class:`.Computed`
1865 :class:`.Identity`. In some cases an
1866 equivalent keyword argument is available such as ``server_default``,
1867 ``default`` and ``unique``.
1869 :param autoincrement: Set up "auto increment" semantics for an
1870 **integer primary key column with no foreign key dependencies**
1871 (see later in this docstring for a more specific definition).
1872 This may influence the :term:`DDL` that will be emitted for
1873 this column during a table create, as well as how the column
1874 will be considered when INSERT statements are compiled and
1875 executed.
1877 The default value is the string ``"auto"``,
1878 which indicates that a single-column (i.e. non-composite) primary key
1879 that is of an INTEGER type with no other client-side or server-side
1880 default constructs indicated should receive auto increment semantics
1881 automatically. Other values include ``True`` (force this column to
1882 have auto-increment semantics for a :term:`composite primary key` as
1883 well), ``False`` (this column should never have auto-increment
1884 semantics), and the string ``"ignore_fk"`` (special-case for foreign
1885 key columns, see below).
1887 The term "auto increment semantics" refers both to the kind of DDL
1888 that will be emitted for the column within a CREATE TABLE statement,
1889 when methods such as :meth:`.MetaData.create_all` and
1890 :meth:`.Table.create` are invoked, as well as how the column will be
1891 considered when an INSERT statement is compiled and emitted to the
1892 database:
1894 * **DDL rendering** (i.e. :meth:`.MetaData.create_all`,
1895 :meth:`.Table.create`): When used on a :class:`.Column` that has
1896 no other
1897 default-generating construct associated with it (such as a
1898 :class:`.Sequence` or :class:`.Identity` construct), the parameter
1899 will imply that database-specific keywords such as PostgreSQL
1900 ``SERIAL``, MySQL ``AUTO_INCREMENT``, or ``IDENTITY`` on SQL Server
1901 should also be rendered. Not every database backend has an
1902 "implied" default generator available; for example the Oracle Database
1903 backends always needs an explicit construct such as
1904 :class:`.Identity` to be included with a :class:`.Column` in order
1905 for the DDL rendered to include auto-generating constructs to also
1906 be produced in the database.
1908 * **INSERT semantics** (i.e. when a :func:`_sql.insert` construct is
1909 compiled into a SQL string and is then executed on a database using
1910 :meth:`_engine.Connection.execute` or equivalent): A single-row
1911 INSERT statement will be known to produce a new integer primary key
1912 value automatically for this column, which will be accessible
1913 after the statement is invoked via the
1914 :attr:`.CursorResult.inserted_primary_key` attribute upon the
1915 :class:`_result.Result` object. This also applies towards use of the
1916 ORM when ORM-mapped objects are persisted to the database,
1917 indicating that a new integer primary key will be available to
1918 become part of the :term:`identity key` for that object. This
1919 behavior takes place regardless of what DDL constructs are
1920 associated with the :class:`_schema.Column` and is independent
1921 of the "DDL Rendering" behavior discussed in the previous note
1922 above.
1924 The parameter may be set to ``True`` to indicate that a column which
1925 is part of a composite (i.e. multi-column) primary key should
1926 have autoincrement semantics, though note that only one column
1927 within a primary key may have this setting. It can also
1928 be set to ``True`` to indicate autoincrement semantics on a
1929 column that has a client-side or server-side default configured,
1930 however note that not all dialects can accommodate all styles
1931 of default as an "autoincrement". It can also be
1932 set to ``False`` on a single-column primary key that has a
1933 datatype of INTEGER in order to disable auto increment semantics
1934 for that column.
1936 The setting *only* has an effect for columns which are:
1938 * Integer derived (i.e. INT, SMALLINT, BIGINT).
1940 * Part of the primary key
1942 * Not referring to another column via :class:`_schema.ForeignKey`,
1943 unless
1944 the value is specified as ``'ignore_fk'``::
1946 # turn on autoincrement for this column despite
1947 # the ForeignKey()
1948 Column(
1949 "id",
1950 ForeignKey("other.id"),
1951 primary_key=True,
1952 autoincrement="ignore_fk",
1953 )
1955 It is typically not desirable to have "autoincrement" enabled on a
1956 column that refers to another via foreign key, as such a column is
1957 required to refer to a value that originates from elsewhere.
1959 The setting has these effects on columns that meet the
1960 above criteria:
1962 * DDL issued for the column, if the column does not already include
1963 a default generating construct supported by the backend such as
1964 :class:`.Identity`, will include database-specific
1965 keywords intended to signify this column as an
1966 "autoincrement" column for specific backends. Behavior for
1967 primary SQLAlchemy dialects includes:
1969 * AUTO INCREMENT on MySQL and MariaDB
1970 * SERIAL on PostgreSQL
1971 * IDENTITY on MS-SQL - this occurs even without the
1972 :class:`.Identity` construct as the
1973 :paramref:`.Column.autoincrement` parameter pre-dates this
1974 construct.
1975 * SQLite - SQLite integer primary key columns are implicitly
1976 "auto incrementing" and no additional keywords are rendered;
1977 to render the special SQLite keyword ``AUTOINCREMENT``
1978 is not included as this is unnecessary and not recommended
1979 by the database vendor. See the section
1980 :ref:`sqlite_autoincrement` for more background.
1981 * Oracle Database - The Oracle Database dialects have no default "autoincrement"
1982 feature available at this time, instead the :class:`.Identity`
1983 construct is recommended to achieve this (the :class:`.Sequence`
1984 construct may also be used).
1985 * Third-party dialects - consult those dialects' documentation
1986 for details on their specific behaviors.
1988 * When a single-row :func:`_sql.insert` construct is compiled and
1989 executed, which does not set the :meth:`_sql.Insert.inline`
1990 modifier, newly generated primary key values for this column
1991 will be automatically retrieved upon statement execution
1992 using a method specific to the database driver in use:
1994 * MySQL, SQLite - calling upon ``cursor.lastrowid()``
1995 (see
1996 `https://www.python.org/dev/peps/pep-0249/#lastrowid
1997 <https://www.python.org/dev/peps/pep-0249/#lastrowid>`_)
1998 * PostgreSQL, SQL Server, Oracle Database - use RETURNING or an equivalent
1999 construct when rendering an INSERT statement, and then retrieving
2000 the newly generated primary key values after execution
2001 * PostgreSQL, Oracle Database for :class:`_schema.Table` objects that
2002 set :paramref:`_schema.Table.implicit_returning` to False -
2003 for a :class:`.Sequence` only, the :class:`.Sequence` is invoked
2004 explicitly before the INSERT statement takes place so that the
2005 newly generated primary key value is available to the client
2006 * SQL Server for :class:`_schema.Table` objects that
2007 set :paramref:`_schema.Table.implicit_returning` to False -
2008 the ``SELECT scope_identity()`` construct is used after the
2009 INSERT statement is invoked to retrieve the newly generated
2010 primary key value.
2011 * Third-party dialects - consult those dialects' documentation
2012 for details on their specific behaviors.
2014 * For multiple-row :func:`_sql.insert` constructs invoked with
2015 a list of parameters (i.e. "executemany" semantics), primary-key
2016 retrieving behaviors are generally disabled, however there may
2017 be special APIs that may be used to retrieve lists of new
2018 primary key values for an "executemany", such as the psycopg2
2019 "fast insertmany" feature. Such features are very new and
2020 may not yet be well covered in documentation.
2022 :param default: A scalar, Python callable, or
2023 :class:`_expression.ColumnElement` expression representing the
2024 *default value* for this column, which will be invoked upon insert
2025 if this column is otherwise not specified in the VALUES clause of
2026 the insert. This is a shortcut to using :class:`.ColumnDefault` as
2027 a positional argument; see that class for full detail on the
2028 structure of the argument.
2030 Contrast this argument to
2031 :paramref:`_schema.Column.server_default`
2032 which creates a default generator on the database side.
2034 .. seealso::
2036 :ref:`metadata_defaults_toplevel`
2038 :param insert_default: An alias of :paramref:`.Column.default`
2039 for compatibility with :func:`_orm.mapped_column`.
2041 .. versionadded:: 2.0.31
2043 :param doc: optional String that can be used by the ORM or similar
2044 to document attributes on the Python side. This attribute does
2045 **not** render SQL comments; use the
2046 :paramref:`_schema.Column.comment`
2047 parameter for this purpose.
2049 :param key: An optional string identifier which will identify this
2050 ``Column`` object on the :class:`_schema.Table`.
2051 When a key is provided,
2052 this is the only identifier referencing the ``Column`` within the
2053 application, including ORM attribute mapping; the ``name`` field
2054 is used only when rendering SQL.
2056 :param index: When ``True``, indicates that a :class:`_schema.Index`
2057 construct will be automatically generated for this
2058 :class:`_schema.Column`, which will result in a "CREATE INDEX"
2059 statement being emitted for the :class:`_schema.Table` when the DDL
2060 create operation is invoked.
2062 Using this flag is equivalent to making use of the
2063 :class:`_schema.Index` construct explicitly at the level of the
2064 :class:`_schema.Table` construct itself::
2066 Table(
2067 "some_table",
2068 metadata,
2069 Column("x", Integer),
2070 Index("ix_some_table_x", "x"),
2071 )
2073 To add the :paramref:`_schema.Index.unique` flag to the
2074 :class:`_schema.Index`, set both the
2075 :paramref:`_schema.Column.unique` and
2076 :paramref:`_schema.Column.index` flags to True simultaneously,
2077 which will have the effect of rendering the "CREATE UNIQUE INDEX"
2078 DDL instruction instead of "CREATE INDEX".
2080 The name of the index is generated using the
2081 :ref:`default naming convention <constraint_default_naming_convention>`
2082 which for the :class:`_schema.Index` construct is of the form
2083 ``ix_<tablename>_<columnname>``.
2085 As this flag is intended only as a convenience for the common case
2086 of adding a single-column, default configured index to a table
2087 definition, explicit use of the :class:`_schema.Index` construct
2088 should be preferred for most use cases, including composite indexes
2089 that encompass more than one column, indexes with SQL expressions
2090 or ordering, backend-specific index configuration options, and
2091 indexes that use a specific name.
2093 .. note:: the :attr:`_schema.Column.index` attribute on
2094 :class:`_schema.Column`
2095 **does not indicate** if this column is indexed or not, only
2096 if this flag was explicitly set here. To view indexes on
2097 a column, view the :attr:`_schema.Table.indexes` collection
2098 or use :meth:`_reflection.Inspector.get_indexes`.
2100 .. seealso::
2102 :ref:`schema_indexes`
2104 :ref:`constraint_naming_conventions`
2106 :paramref:`_schema.Column.unique`
2108 :param info: Optional data dictionary which will be populated into the
2109 :attr:`.SchemaItem.info` attribute of this object.
2111 :param nullable: When set to ``False``, will cause the "NOT NULL"
2112 phrase to be added when generating DDL for the column. When
2113 ``True``, will normally generate nothing (in SQL this defaults to
2114 "NULL"), except in some very specific backend-specific edge cases
2115 where "NULL" may render explicitly.
2116 Defaults to ``True`` unless :paramref:`_schema.Column.primary_key`
2117 is also ``True`` or the column specifies a :class:`_sql.Identity`,
2118 in which case it defaults to ``False``.
2119 This parameter is only used when issuing CREATE TABLE statements.
2121 .. note::
2123 When the column specifies a :class:`_sql.Identity` this
2124 parameter is in general ignored by the DDL compiler. The
2125 PostgreSQL database allows nullable identity column by
2126 setting this parameter to ``True`` explicitly.
2128 :param onupdate: A scalar, Python callable, or
2129 :class:`~sqlalchemy.sql.expression.ClauseElement` representing a
2130 default value to be applied to the column within UPDATE
2131 statements, which will be invoked upon update if this column is not
2132 present in the SET clause of the update. This is a shortcut to
2133 using :class:`.ColumnDefault` as a positional argument with
2134 ``for_update=True``.
2136 .. seealso::
2138 :ref:`metadata_defaults` - complete discussion of onupdate
2140 :param primary_key: If ``True``, marks this column as a primary key
2141 column. Multiple columns can have this flag set to specify
2142 composite primary keys. As an alternative, the primary key of a
2143 :class:`_schema.Table` can be specified via an explicit
2144 :class:`.PrimaryKeyConstraint` object.
2146 :param server_default: A :class:`.FetchedValue` instance, str, Unicode
2147 or :func:`~sqlalchemy.sql.expression.text` construct representing
2148 the DDL DEFAULT value for the column.
2150 String types will be emitted as-is, surrounded by single quotes::
2152 Column("x", Text, server_default="val")
2154 will render:
2156 .. sourcecode:: sql
2158 x TEXT DEFAULT 'val'
2160 A :func:`~sqlalchemy.sql.expression.text` expression will be
2161 rendered as-is, without quotes::
2163 Column("y", DateTime, server_default=text("NOW()"))
2165 will render:
2167 .. sourcecode:: sql
2169 y DATETIME DEFAULT NOW()
2171 Strings and text() will be converted into a
2172 :class:`.DefaultClause` object upon initialization.
2174 This parameter can also accept complex combinations of contextually
2175 valid SQLAlchemy expressions or constructs::
2177 from sqlalchemy import create_engine
2178 from sqlalchemy import Table, Column, MetaData, ARRAY, Text
2179 from sqlalchemy.dialects.postgresql import array
2181 engine = create_engine(
2182 "postgresql+psycopg2://scott:tiger@localhost/mydatabase"
2183 )
2184 metadata_obj = MetaData()
2185 tbl = Table(
2186 "foo",
2187 metadata_obj,
2188 Column(
2189 "bar", ARRAY(Text), server_default=array(["biz", "bang", "bash"])
2190 ),
2191 )
2192 metadata_obj.create_all(engine)
2194 The above results in a table created with the following SQL:
2196 .. sourcecode:: sql
2198 CREATE TABLE foo (
2199 bar TEXT[] DEFAULT ARRAY['biz', 'bang', 'bash']
2200 )
2202 Use :class:`.FetchedValue` to indicate that an already-existing
2203 column will generate a default value on the database side which
2204 will be available to SQLAlchemy for post-fetch after inserts. This
2205 construct does not specify any DDL and the implementation is left
2206 to the database, such as via a trigger.
2208 .. seealso::
2210 :ref:`server_defaults` - complete discussion of server side
2211 defaults
2213 :param server_onupdate: A :class:`.FetchedValue` instance
2214 representing a database-side default generation function,
2215 such as a trigger. This
2216 indicates to SQLAlchemy that a newly generated value will be
2217 available after updates. This construct does not actually
2218 implement any kind of generation function within the database,
2219 which instead must be specified separately.
2222 .. warning:: This directive **does not** currently produce MySQL's
2223 "ON UPDATE CURRENT_TIMESTAMP()" clause. See
2224 :ref:`mysql_timestamp_onupdate` for background on how to
2225 produce this clause.
2227 .. seealso::
2229 :ref:`triggered_columns`
2231 :param quote: Force quoting of this column's name on or off,
2232 corresponding to ``True`` or ``False``. When left at its default
2233 of ``None``, the column identifier will be quoted according to
2234 whether the name is case sensitive (identifiers with at least one
2235 upper case character are treated as case sensitive), or if it's a
2236 reserved word. This flag is only needed to force quoting of a
2237 reserved word which is not known by the SQLAlchemy dialect.
2239 :param unique: When ``True``, and the :paramref:`_schema.Column.index`
2240 parameter is left at its default value of ``False``,
2241 indicates that a :class:`_schema.UniqueConstraint`
2242 construct will be automatically generated for this
2243 :class:`_schema.Column`,
2244 which will result in a "UNIQUE CONSTRAINT" clause referring
2245 to this column being included
2246 in the ``CREATE TABLE`` statement emitted, when the DDL create
2247 operation for the :class:`_schema.Table` object is invoked.
2249 When this flag is ``True`` while the
2250 :paramref:`_schema.Column.index` parameter is simultaneously
2251 set to ``True``, the effect instead is that a
2252 :class:`_schema.Index` construct which includes the
2253 :paramref:`_schema.Index.unique` parameter set to ``True``
2254 is generated. See the documentation for
2255 :paramref:`_schema.Column.index` for additional detail.
2257 Using this flag is equivalent to making use of the
2258 :class:`_schema.UniqueConstraint` construct explicitly at the
2259 level of the :class:`_schema.Table` construct itself::
2261 Table("some_table", metadata, Column("x", Integer), UniqueConstraint("x"))
2263 The :paramref:`_schema.UniqueConstraint.name` parameter
2264 of the unique constraint object is left at its default value
2265 of ``None``; in the absence of a :ref:`naming convention <constraint_naming_conventions>`
2266 for the enclosing :class:`_schema.MetaData`, the UNIQUE CONSTRAINT
2267 construct will be emitted as unnamed, which typically invokes
2268 a database-specific naming convention to take place.
2270 As this flag is intended only as a convenience for the common case
2271 of adding a single-column, default configured unique constraint to a table
2272 definition, explicit use of the :class:`_schema.UniqueConstraint` construct
2273 should be preferred for most use cases, including composite constraints
2274 that encompass more than one column, backend-specific index configuration options, and
2275 constraints that use a specific name.
2277 .. note:: the :attr:`_schema.Column.unique` attribute on
2278 :class:`_schema.Column`
2279 **does not indicate** if this column has a unique constraint or
2280 not, only if this flag was explicitly set here. To view
2281 indexes and unique constraints that may involve this column,
2282 view the
2283 :attr:`_schema.Table.indexes` and/or
2284 :attr:`_schema.Table.constraints` collections or use
2285 :meth:`_reflection.Inspector.get_indexes` and/or
2286 :meth:`_reflection.Inspector.get_unique_constraints`
2288 .. seealso::
2290 :ref:`schema_unique_constraint`
2292 :ref:`constraint_naming_conventions`
2294 :paramref:`_schema.Column.index`
2296 :param system: When ``True``, indicates this is a "system" column,
2297 that is a column which is automatically made available by the
2298 database, and should not be included in the columns list for a
2299 ``CREATE TABLE`` statement.
2301 For more elaborate scenarios where columns should be
2302 conditionally rendered differently on different backends,
2303 consider custom compilation rules for :class:`.CreateColumn`.
2305 :param comment: Optional string that will render an SQL comment on
2306 table creation.
2308 :param insert_sentinel: Marks this :class:`_schema.Column` as an
2309 :term:`insert sentinel` used for optimizing the performance of the
2310 :term:`insertmanyvalues` feature for tables that don't
2311 otherwise have qualifying primary key configurations.
2313 .. versionadded:: 2.0.10
2315 .. seealso::
2317 :func:`_schema.insert_sentinel` - all in one helper for declaring
2318 sentinel columns
2320 :ref:`engine_insertmanyvalues`
2322 :ref:`engine_insertmanyvalues_sentinel_columns`
2325 """ # noqa: E501, RST201, RST202
2327 l_args = [__name_pos, __type_pos] + list(args)
2328 del args
2330 if isinstance(l_args[0], str):
2331 if name is not None:
2332 raise exc.ArgumentError(
2333 "May not pass name positionally and as a keyword."
2334 )
2335 name = l_args.pop(0) # type: ignore[assignment]
2336 elif l_args[0] is None:
2337 l_args.pop(0)
2338 if l_args:
2339 coltype = l_args[0]
2341 if hasattr(coltype, "_sqla_type"):
2342 if type_ is not None:
2343 raise exc.ArgumentError(
2344 "May not pass type_ positionally and as a keyword."
2345 )
2346 type_ = l_args.pop(0) # type: ignore[assignment]
2347 elif l_args[0] is None:
2348 l_args.pop(0)
2350 if name is not None:
2351 name = quoted_name(name, quote)
2352 elif quote is not None:
2353 raise exc.ArgumentError(
2354 "Explicit 'name' is required when sending 'quote' argument"
2355 )
2357 # name = None is expected to be an interim state
2358 # note this use case is legacy now that ORM declarative has a
2359 # dedicated "column" construct local to the ORM
2360 super().__init__(name, type_) # type: ignore[arg-type]
2362 self.key = key if key is not None else name # type: ignore[assignment]
2363 self.primary_key = primary_key
2364 self._insert_sentinel = insert_sentinel
2365 self._omit_from_statements = _omit_from_statements
2366 self._user_defined_nullable = udn = nullable
2367 if udn is not NULL_UNSPECIFIED:
2368 self.nullable = udn
2369 else:
2370 self.nullable = not primary_key
2372 # these default to None because .index and .unique is *not*
2373 # an informational flag about Column - there can still be an
2374 # Index or UniqueConstraint referring to this Column.
2375 self.index = index
2376 self.unique = unique
2378 self.system = system
2379 self.doc = doc
2380 self.autoincrement: _AutoIncrementType = autoincrement
2381 self.constraints = set()
2382 self.foreign_keys = set()
2383 self.comment = comment
2384 self.computed = None
2385 self.identity = None
2387 # check if this Column is proxying another column
2389 if _proxies is not None:
2390 self._proxies = _proxies
2391 else:
2392 # otherwise, add DDL-related events
2393 self._set_type(self.type)
2395 if insert_default is not _NoArg.NO_ARG:
2396 if default is not _NoArg.NO_ARG:
2397 raise exc.ArgumentError(
2398 "The 'default' and 'insert_default' parameters "
2399 "of Column are mutually exclusive"
2400 )
2401 resolved_default = insert_default
2402 elif default is not _NoArg.NO_ARG:
2403 resolved_default = default
2404 else:
2405 resolved_default = None
2407 if resolved_default is not None:
2408 if not isinstance(resolved_default, (ColumnDefault, Sequence)):
2409 resolved_default = ColumnDefault(resolved_default)
2411 self.default = resolved_default
2412 l_args.append(resolved_default)
2413 else:
2414 self.default = None
2416 if onupdate is not None:
2417 if not isinstance(onupdate, (ColumnDefault, Sequence)):
2418 onupdate = ColumnDefault(onupdate, for_update=True)
2420 self.onupdate = onupdate
2421 l_args.append(onupdate)
2422 else:
2423 self.onupdate = None
2425 if server_default is not None:
2426 if isinstance(server_default, FetchedValue):
2427 server_default = server_default._as_for_update(False)
2428 l_args.append(server_default)
2429 else:
2430 server_default = DefaultClause(server_default)
2431 l_args.append(server_default)
2432 self.server_default = server_default
2434 if server_onupdate is not None:
2435 if isinstance(server_onupdate, FetchedValue):
2436 server_onupdate = server_onupdate._as_for_update(True)
2437 l_args.append(server_onupdate)
2438 else:
2439 server_onupdate = DefaultClause(
2440 server_onupdate, for_update=True
2441 )
2442 l_args.append(server_onupdate)
2443 self.server_onupdate = server_onupdate
2445 self._init_items(*cast(_typing_Sequence[SchemaItem], l_args))
2447 util.set_creation_order(self)
2449 if info is not None:
2450 self.info = info
2452 self._extra_kwargs(**dialect_kwargs)
2454 table: Table
2456 constraints: Set[Constraint]
2458 foreign_keys: Set[ForeignKey]
2459 """A collection of all :class:`_schema.ForeignKey` marker objects
2460 associated with this :class:`_schema.Column`.
2462 Each object is a member of a :class:`_schema.Table`-wide
2463 :class:`_schema.ForeignKeyConstraint`.
2465 .. seealso::
2467 :attr:`_schema.Table.foreign_keys`
2469 """
2471 index: Optional[bool]
2472 """The value of the :paramref:`_schema.Column.index` parameter.
2474 Does not indicate if this :class:`_schema.Column` is actually indexed
2475 or not; use :attr:`_schema.Table.indexes`.
2477 .. seealso::
2479 :attr:`_schema.Table.indexes`
2480 """
2482 unique: Optional[bool]
2483 """The value of the :paramref:`_schema.Column.unique` parameter.
2485 Does not indicate if this :class:`_schema.Column` is actually subject to
2486 a unique constraint or not; use :attr:`_schema.Table.indexes` and
2487 :attr:`_schema.Table.constraints`.
2489 .. seealso::
2491 :attr:`_schema.Table.indexes`
2493 :attr:`_schema.Table.constraints`.
2495 """
2497 computed: Optional[Computed]
2499 identity: Optional[Identity]
2501 def _set_type(self, type_: TypeEngine[Any]) -> None:
2502 assert self.type._isnull or type_ is self.type
2504 self.type = type_
2505 if isinstance(self.type, SchemaEventTarget):
2506 self.type._set_parent_with_dispatch(self)
2507 for impl in self.type._variant_mapping.values():
2508 if isinstance(impl, SchemaEventTarget):
2509 impl._set_parent_with_dispatch(self)
2511 @HasMemoized.memoized_attribute
2512 def _default_description_tuple(self) -> _DefaultDescriptionTuple:
2513 """used by default.py -> _process_execute_defaults()"""
2515 return _DefaultDescriptionTuple._from_column_default(self.default)
2517 @HasMemoized.memoized_attribute
2518 def _onupdate_description_tuple(self) -> _DefaultDescriptionTuple:
2519 """used by default.py -> _process_execute_defaults()"""
2520 return _DefaultDescriptionTuple._from_column_default(self.onupdate)
2522 @util.memoized_property
2523 def _gen_static_annotations_cache_key(self) -> bool:
2524 """special attribute used by cache key gen, if true, we will
2525 use a static cache key for the annotations dictionary, else we
2526 will generate a new cache key for annotations each time.
2528 Added for #8790
2530 """
2531 return self.table is not None and self.table._is_table
2533 def _extra_kwargs(self, **kwargs: Any) -> None:
2534 self._validate_dialect_kwargs(kwargs)
2536 def __str__(self) -> str:
2537 if self.name is None:
2538 return "(no name)"
2539 elif self.table is not None:
2540 if self.table.named_with_column:
2541 return self.table.description + "." + self.description
2542 else:
2543 return self.description
2544 else:
2545 return self.description
2547 def references(self, column: Column[Any]) -> bool:
2548 """Return True if this Column references the given column via foreign
2549 key."""
2551 for fk in self.foreign_keys:
2552 if fk.column.proxy_set.intersection(column.proxy_set):
2553 return True
2554 else:
2555 return False
2557 def append_foreign_key(self, fk: ForeignKey) -> None:
2558 fk._set_parent_with_dispatch(self)
2560 def __repr__(self) -> str:
2561 kwarg = []
2562 if self.key != self.name:
2563 kwarg.append("key")
2564 if self.primary_key:
2565 kwarg.append("primary_key")
2566 if not self.nullable:
2567 kwarg.append("nullable")
2568 if self.onupdate:
2569 kwarg.append("onupdate")
2570 if self.default:
2571 kwarg.append("default")
2572 if self.server_default:
2573 kwarg.append("server_default")
2574 if self.comment:
2575 kwarg.append("comment")
2576 return "Column(%s)" % ", ".join(
2577 [repr(self.name)]
2578 + [repr(self.type)]
2579 + [repr(x) for x in self.foreign_keys if x is not None]
2580 + [repr(x) for x in self.constraints]
2581 + [
2582 (
2583 self.table is not None
2584 and "table=<%s>" % self.table.description
2585 or "table=None"
2586 )
2587 ]
2588 + ["%s=%s" % (k, repr(getattr(self, k))) for k in kwarg]
2589 )
2591 def _set_parent( # type: ignore[override]
2592 self,
2593 parent: SchemaEventTarget,
2594 *,
2595 all_names: Dict[str, Column[Any]],
2596 allow_replacements: bool,
2597 index: Optional[int] = None,
2598 **kw: Any,
2599 ) -> None:
2600 table = parent
2601 assert isinstance(table, Table)
2602 if not self.name:
2603 raise exc.ArgumentError(
2604 "Column must be constructed with a non-blank name or "
2605 "assign a non-blank .name before adding to a Table."
2606 )
2608 self._reset_memoizations()
2610 if self.key is None:
2611 self.key = self.name
2613 existing = getattr(self, "table", None)
2614 if existing is not None and existing is not table:
2615 raise exc.ArgumentError(
2616 f"Column object '{self.key}' already "
2617 f"assigned to Table '{existing.description}'"
2618 )
2620 extra_remove = None
2621 existing_col = None
2622 conflicts_on = ""
2624 if self.key in table._columns:
2625 existing_col = table._columns[self.key]
2626 if self.key == self.name:
2627 conflicts_on = "name"
2628 else:
2629 conflicts_on = "key"
2630 elif self.name in all_names:
2631 existing_col = all_names[self.name]
2632 extra_remove = {existing_col}
2633 conflicts_on = "name"
2635 if existing_col is not None:
2636 if existing_col is not self:
2637 if not allow_replacements:
2638 raise exc.DuplicateColumnError(
2639 f"A column with {conflicts_on} " f"""'{
2640 self.key if conflicts_on == 'key' else self.name
2641 }' """ f"is already present in table '{table.name}'."
2642 )
2643 for fk in existing_col.foreign_keys:
2644 table.foreign_keys.remove(fk)
2645 if fk.constraint in table.constraints:
2646 # this might have been removed
2647 # already, if it's a composite constraint
2648 # and more than one col being replaced
2649 table.constraints.remove(fk.constraint)
2651 if extra_remove and existing_col is not None and self.key == self.name:
2652 util.warn(
2653 f'Column with user-specified key "{existing_col.key}" is '
2654 "being replaced with "
2655 f'plain named column "{self.name}", '
2656 f'key "{existing_col.key}" is being removed. If this is a '
2657 "reflection operation, specify autoload_replace=False to "
2658 "prevent this replacement."
2659 )
2660 table._columns.replace(self, extra_remove=extra_remove, index=index)
2661 all_names[self.name] = self
2662 self.table = table
2664 if self._insert_sentinel:
2665 if self.table._sentinel_column is not None:
2666 raise exc.ArgumentError(
2667 "a Table may have only one explicit sentinel column"
2668 )
2669 self.table._sentinel_column = self
2671 if self.primary_key:
2672 table.primary_key._replace(self)
2673 elif self.key in table.primary_key:
2674 raise exc.ArgumentError(
2675 f"Trying to redefine primary-key column '{self.key}' as a "
2676 f"non-primary-key column on table '{table.fullname}'"
2677 )
2679 if self.index:
2680 if isinstance(self.index, str):
2681 raise exc.ArgumentError(
2682 "The 'index' keyword argument on Column is boolean only. "
2683 "To create indexes with a specific name, create an "
2684 "explicit Index object external to the Table."
2685 )
2686 table.append_constraint(
2687 Index(
2688 None, self.key, unique=bool(self.unique), _column_flag=True
2689 )
2690 )
2692 elif self.unique:
2693 if isinstance(self.unique, str):
2694 raise exc.ArgumentError(
2695 "The 'unique' keyword argument on Column is boolean "
2696 "only. To create unique constraints or indexes with a "
2697 "specific name, append an explicit UniqueConstraint to "
2698 "the Table's list of elements, or create an explicit "
2699 "Index object external to the Table."
2700 )
2701 table.append_constraint(
2702 UniqueConstraint(self.key, _column_flag=True)
2703 )
2705 self._setup_on_memoized_fks(lambda fk: fk._set_remote_table(table))
2707 if self.identity and (
2708 isinstance(self.default, Sequence)
2709 or isinstance(self.onupdate, Sequence)
2710 ):
2711 raise exc.ArgumentError(
2712 "An column cannot specify both Identity and Sequence."
2713 )
2715 def _setup_on_memoized_fks(self, fn: Callable[..., Any]) -> None:
2716 fk_keys = [
2717 ((self.table.key, self.key), False),
2718 ((self.table.key, self.name), True),
2719 ]
2720 for fk_key, link_to_name in fk_keys:
2721 if fk_key in self.table.metadata._fk_memos:
2722 for fk in self.table.metadata._fk_memos[fk_key]:
2723 if fk.link_to_name is link_to_name:
2724 fn(fk)
2726 def _on_table_attach(self, fn: Callable[..., Any]) -> None:
2727 if self.table is not None:
2728 fn(self, self.table)
2729 else:
2730 event.listen(self, "after_parent_attach", fn)
2732 @util.deprecated(
2733 "1.4",
2734 "The :meth:`_schema.Column.copy` method is deprecated "
2735 "and will be removed in a future release.",
2736 )
2737 def copy(self, **kw: Any) -> Column[Any]:
2738 return self._copy(**kw)
2740 def _copy(self, **kw: Any) -> Column[Any]:
2741 """Create a copy of this ``Column``, uninitialized.
2743 This is used in :meth:`_schema.Table.to_metadata` and by the ORM.
2745 """
2747 # Constraint objects plus non-constraint-bound ForeignKey objects
2748 args: List[SchemaItem] = [
2749 c._copy(**kw) for c in self.constraints if not c._type_bound
2750 ] + [c._copy(**kw) for c in self.foreign_keys if not c.constraint]
2752 # ticket #5276
2753 column_kwargs = {}
2754 for dialect_name in self.dialect_options:
2755 dialect_options = self.dialect_options[dialect_name]._non_defaults
2756 for (
2757 dialect_option_key,
2758 dialect_option_value,
2759 ) in dialect_options.items():
2760 column_kwargs[dialect_name + "_" + dialect_option_key] = (
2761 dialect_option_value
2762 )
2764 default = self.default
2765 if default is not None:
2766 default = default._copy()
2767 onupdate = self.onupdate
2768 if onupdate is not None:
2769 onupdate = onupdate._copy()
2770 server_default = self.server_default
2771 server_onupdate = self.server_onupdate
2772 if isinstance(server_default, (Computed, Identity)):
2773 args.append(server_default._copy(**kw))
2774 server_default = server_onupdate = None
2775 else:
2776 if server_default is not None:
2777 server_default = server_default._copy()
2778 if server_onupdate is not None:
2779 server_onupdate = server_onupdate._copy()
2781 type_ = self.type
2782 if isinstance(type_, SchemaEventTarget):
2783 type_ = type_.copy(**kw)
2785 c = self._constructor(
2786 name=self.name,
2787 type_=type_,
2788 key=self.key,
2789 primary_key=self.primary_key,
2790 unique=self.unique,
2791 system=self.system,
2792 # quote=self.quote, # disabled 2013-08-27 (commit 031ef080)
2793 index=self.index,
2794 autoincrement=self.autoincrement,
2795 default=default,
2796 server_default=server_default,
2797 onupdate=onupdate,
2798 server_onupdate=server_onupdate,
2799 doc=self.doc,
2800 comment=self.comment,
2801 _omit_from_statements=self._omit_from_statements,
2802 insert_sentinel=self._insert_sentinel,
2803 *args,
2804 **column_kwargs,
2805 )
2807 # copy the state of "nullable" exactly, to accommodate for
2808 # ORM flipping the .nullable flag directly
2809 c.nullable = self.nullable
2810 c._user_defined_nullable = self._user_defined_nullable
2812 return self._schema_item_copy(c)
2814 def _merge(
2815 self, other: Column[Any], *, omit_defaults: bool = False
2816 ) -> None:
2817 """merge the elements of this column onto "other"
2819 this is used by ORM pep-593 merge and will likely need a lot
2820 of fixes.
2823 """
2825 if self.primary_key:
2826 other.primary_key = True
2828 if self.autoincrement != "auto" and other.autoincrement == "auto":
2829 other.autoincrement = self.autoincrement
2831 if self.system:
2832 other.system = self.system
2834 if self.info:
2835 other.info.update(self.info)
2837 type_ = self.type
2838 if not type_._isnull and other.type._isnull:
2839 if isinstance(type_, SchemaEventTarget):
2840 type_ = type_.copy()
2842 other.type = type_
2844 if isinstance(type_, SchemaEventTarget):
2845 type_._set_parent_with_dispatch(other)
2847 for impl in type_._variant_mapping.values():
2848 if isinstance(impl, SchemaEventTarget):
2849 impl._set_parent_with_dispatch(other)
2851 if (
2852 self._user_defined_nullable is not NULL_UNSPECIFIED
2853 and other._user_defined_nullable is NULL_UNSPECIFIED
2854 ):
2855 other.nullable = self.nullable
2856 other._user_defined_nullable = self._user_defined_nullable
2858 if (
2859 not omit_defaults
2860 and self.default is not None
2861 and other.default is None
2862 ):
2863 new_default = self.default._copy()
2864 new_default._set_parent(other)
2866 if self.server_default and other.server_default is None:
2867 new_server_default = self.server_default
2868 if isinstance(new_server_default, FetchedValue):
2869 new_server_default = new_server_default._copy()
2870 new_server_default._set_parent(other)
2871 else:
2872 other.server_default = new_server_default
2874 if self.server_onupdate and other.server_onupdate is None:
2875 new_server_onupdate = self.server_onupdate
2876 new_server_onupdate = new_server_onupdate._copy()
2877 new_server_onupdate._set_parent(other)
2879 if self.onupdate and other.onupdate is None:
2880 new_onupdate = self.onupdate._copy()
2881 new_onupdate._set_parent(other)
2883 if self.index in (True, False) and other.index is None:
2884 other.index = self.index
2886 if self.unique in (True, False) and other.unique is None:
2887 other.unique = self.unique
2889 if self.doc and other.doc is None:
2890 other.doc = self.doc
2892 if self.comment and other.comment is None:
2893 other.comment = self.comment
2895 for const in self.constraints:
2896 if not const._type_bound:
2897 new_const = const._copy()
2898 new_const._set_parent(other)
2900 for fk in self.foreign_keys:
2901 if not fk.constraint:
2902 new_fk = fk._copy()
2903 new_fk._set_parent(other)
2905 def _make_proxy(
2906 self,
2907 selectable: FromClause,
2908 primary_key: ColumnSet,
2909 foreign_keys: Set[KeyedColumnElement[Any]],
2910 name: Optional[str] = None,
2911 key: Optional[str] = None,
2912 name_is_truncatable: bool = False,
2913 compound_select_cols: Optional[
2914 _typing_Sequence[ColumnElement[Any]]
2915 ] = None,
2916 **kw: Any,
2917 ) -> Tuple[str, ColumnClause[_T]]:
2918 """Create a *proxy* for this column.
2920 This is a copy of this ``Column`` referenced by a different parent
2921 (such as an alias or select statement). The column should
2922 be used only in select scenarios, as its full DDL/default
2923 information is not transferred.
2925 """
2927 fk = [
2928 ForeignKey(
2929 col if col is not None else f.target_tokens,
2930 _unresolvable=col is None,
2931 _constraint=f.constraint,
2932 )
2933 for f, col in [
2934 (fk, fk._resolve_column(raiseerr=False))
2935 for fk in self.foreign_keys
2936 ]
2937 ]
2939 if name is None and self.name is None:
2940 raise exc.InvalidRequestError(
2941 "Cannot initialize a sub-selectable"
2942 " with this Column object until its 'name' has "
2943 "been assigned."
2944 )
2945 try:
2946 c = self._constructor(
2947 (
2948 coercions.expect(
2949 roles.TruncatedLabelRole, name if name else self.name
2950 )
2951 if name_is_truncatable
2952 else (name or self.name)
2953 ),
2954 self.type,
2955 # this may actually be ._proxy_key when the key is incoming
2956 key=key if key else name if name else self.key,
2957 primary_key=self.primary_key,
2958 nullable=self.nullable,
2959 _proxies=(
2960 list(compound_select_cols)
2961 if compound_select_cols
2962 else [self]
2963 ),
2964 *fk,
2965 )
2966 except TypeError as err:
2967 raise TypeError(
2968 "Could not create a copy of this %r object. "
2969 "Ensure the class includes a _constructor() "
2970 "attribute or method which accepts the "
2971 "standard Column constructor arguments, or "
2972 "references the Column class itself." % self.__class__
2973 ) from err
2975 c.table = selectable
2976 c._propagate_attrs = selectable._propagate_attrs
2977 if selectable._is_clone_of is not None:
2978 c._is_clone_of = selectable._is_clone_of.columns.get(c.key)
2980 if self.primary_key:
2981 primary_key.add(c)
2983 if fk:
2984 foreign_keys.update(fk) # type: ignore[arg-type]
2986 return c.key, c
2989def insert_sentinel(
2990 name: Optional[str] = None,
2991 type_: Optional[_TypeEngineArgument[_T]] = None,
2992 *,
2993 default: Optional[Any] = None,
2994 omit_from_statements: bool = True,
2995) -> Column[Any]:
2996 """Provides a surrogate :class:`_schema.Column` that will act as a
2997 dedicated insert :term:`sentinel` column, allowing efficient bulk
2998 inserts with deterministic RETURNING sorting for tables that
2999 don't otherwise have qualifying primary key configurations.
3001 Adding this column to a :class:`.Table` object requires that a
3002 corresponding database table actually has this column present, so if adding
3003 it to an existing model, existing database tables would need to be migrated
3004 (e.g. using ALTER TABLE or similar) to include this column.
3006 For background on how this object is used, see the section
3007 :ref:`engine_insertmanyvalues_sentinel_columns` as part of the
3008 section :ref:`engine_insertmanyvalues`.
3010 The :class:`_schema.Column` returned will be a nullable integer column by
3011 default and make use of a sentinel-specific default generator used only in
3012 "insertmanyvalues" operations.
3014 .. seealso::
3016 :func:`_orm.orm_insert_sentinel`
3018 :paramref:`_schema.Column.insert_sentinel`
3020 :ref:`engine_insertmanyvalues`
3022 :ref:`engine_insertmanyvalues_sentinel_columns`
3025 .. versionadded:: 2.0.10
3027 """
3028 return Column(
3029 name=name,
3030 type_=type_api.INTEGERTYPE if type_ is None else type_,
3031 default=(
3032 default if default is not None else _InsertSentinelColumnDefault()
3033 ),
3034 _omit_from_statements=omit_from_statements,
3035 insert_sentinel=True,
3036 )
3039class ForeignKeyTarget(NamedTuple):
3040 """Represents the target of a :class:`_schema.ForeignKey` as three
3041 individual name tokens.
3043 This is the return value of :attr:`_schema.ForeignKey.target_tokens`, and
3044 may also be passed directly to the :class:`_schema.ForeignKey` constructor
3045 as well as to the :paramref:`_schema.ForeignKeyConstraint.refcolumns`
3046 parameter, as either a three-token tuple ``(schema, table_name,
3047 column_name)`` or a two-token tuple ``(table_name, column_name)``.
3049 The token form is the only representation of a foreign key target that is
3050 unambiguous in all cases; the dotted string form, available at
3051 :attr:`_schema.ForeignKey.target_fullname`, cannot represent a target
3052 where the table or column name itself contains a dot.
3054 .. versionadded:: 2.1
3056 .. seealso::
3058 :attr:`_schema.ForeignKey.target_tokens`
3060 """
3062 schema: Optional[str]
3063 """The schema name of the target, or ``None`` for the default schema."""
3065 table_name: str
3066 """The name of the target table."""
3068 column_name: Optional[str]
3069 """The key of the target column.
3071 This is ``None`` for a :class:`_schema.ForeignKey` that was given a table
3072 name only, in which case the local column's key is used to locate the
3073 target column.
3075 Note that this is the ``key`` of the target column rather than its name,
3076 unless :paramref:`_schema.ForeignKey.link_to_name` is ``True``.
3078 """
3080 def _tokens_no_dots(self) -> _typing_Sequence[str] | None:
3081 """return a sequence of non-None tokens to create a dotted name.
3083 returns None if either of table_name or column_name already have an
3084 embedded dot, making dotted name string impossible.
3086 """
3087 tokens = []
3088 for i, token in enumerate(
3089 [self.schema, self.table_name, self.column_name]
3090 ):
3091 if token is not None:
3092 # dot in the table name or column name; a dotted name
3093 # would be ambiguous
3094 if i != 0 and "." in token:
3095 return None
3096 tokens.append(token)
3097 elif i == 1:
3098 # table name is None; not renderable
3099 return None
3100 elif i == 2 and self.schema:
3101 # column name is None and there's a schema; a dotted
3102 # name would be ambiguous
3103 return None
3105 return tokens
3107 def _as_string(self) -> str:
3108 """Render these tokens as a single dotted string if possible,
3109 else raise :class:`.InvalidRequestError` if no unambiguous string form
3110 exists and a string is required.
3112 """
3114 tokens_no_dots = self._tokens_no_dots()
3116 if tokens_no_dots is None:
3118 if not self.table_name:
3119 reason = (
3120 "the target has no table name; a ForeignKey to a Column "
3121 "which is not yet associated with a Table has no names "
3122 "to render until that Column is attached"
3123 )
3124 elif "." in self.table_name:
3125 reason = (
3126 f"the table name {self.table_name!r} contains a dot, "
3127 f"which can't be told apart from the separator between "
3128 f"a schema name and a table name"
3129 )
3130 elif self.column_name is not None and "." in self.column_name:
3131 reason = (
3132 f"the column name {self.column_name!r} contains a dot, "
3133 f"which can't be told apart from the separator between "
3134 f"a table name and a column name"
3135 )
3136 elif self.column_name is None:
3137 reason = (
3138 f"a schema name {self.schema!r} is present with no "
3139 f"column name, so the schema name can't be told apart "
3140 f"from a table name"
3141 )
3142 else:
3143 # this is currently unreachable based on the current behavior
3144 # of tokens_no_dots
3145 assert False
3147 raise exc.InvalidRequestError(
3148 f"Can't render a single string representation for foreign "
3149 f"key target {self._description()}; {reason}. Use "
3150 f"ForeignKey.target_tokens to receive the schema, table and "
3151 f"column names individually."
3152 )
3154 return ".".join(tokens_no_dots)
3156 def _description(self) -> str:
3157 """Render a description of these tokens which never raises.
3159 The dotted string form is preferred when it's available, falling
3160 back to naming the tokens individually when it isn't.
3162 """
3164 tokens_no_dots = self._tokens_no_dots()
3165 if tokens_no_dots:
3166 return repr(".".join(tokens_no_dots))
3167 else:
3168 return (
3169 f"(schema={self.schema!r}, "
3170 f"table_name={self.table_name!r}, "
3171 f"column_name={self.column_name!r})"
3172 )
3174 @classmethod
3175 def _from_string(cls, spec: str) -> ForeignKeyTarget:
3176 """Parse a dotted string colspec into its component tokens.
3178 A FK between column 'bar' and table 'foo' can be specified as 'foo',
3179 'foo.bar', 'dbo.foo.bar', 'otherdb.dbo.foo.bar'. Once we have the
3180 column name and the table name, treat everything else as the schema
3181 name. Some databases (e.g. Sybase) support inter-database foreign
3182 keys. See tickets #1341 and -- indirectly related -- #594.
3184 This assumes that '.' will never appear *within* the table or column
3185 name; a target which does contain such a dot has no string form and
3186 must be given as a :class:`.ForeignKeyTarget` instead.
3188 """
3189 m = spec.split(".")
3190 if len(m) == 1:
3191 return cls(None, m[0], None)
3193 colname = m.pop()
3194 tname = m.pop()
3195 return cls(".".join(m) if m else None, tname, colname)
3197 @classmethod
3198 def _from_argument(
3199 cls, argument: _typing_Sequence[Any]
3200 ) -> ForeignKeyTarget:
3201 """Coerce a two or three token sequence passed by the user."""
3203 if len(argument) == 3:
3204 schema, table_name, column_name = argument
3205 elif len(argument) == 2:
3206 schema = None
3207 table_name, column_name = argument
3208 else:
3209 raise exc.ArgumentError(
3210 f"ForeignKey target given as a tuple must have two tokens "
3211 f"(table_name, column_name) or three tokens "
3212 f"(schema, table_name, column_name); got {len(argument)}"
3213 )
3215 if not table_name:
3216 raise exc.ArgumentError(
3217 "ForeignKey target table_name must be a non-empty string"
3218 )
3220 return cls(schema, table_name, column_name)
3223class ForeignKey(DialectKWArgs, SchemaItem):
3224 """Defines a dependency between two columns.
3226 ``ForeignKey`` is specified as an argument to a :class:`_schema.Column`
3227 object,
3228 e.g.::
3230 t = Table(
3231 "remote_table",
3232 metadata,
3233 Column("remote_id", ForeignKey("main_table.id")),
3234 )
3236 Note that ``ForeignKey`` is only a marker object that defines
3237 a dependency between two columns. The actual constraint
3238 is in all cases represented by the :class:`_schema.ForeignKeyConstraint`
3239 object. This object will be generated automatically when
3240 a ``ForeignKey`` is associated with a :class:`_schema.Column` which
3241 in turn is associated with a :class:`_schema.Table`. Conversely,
3242 when :class:`_schema.ForeignKeyConstraint` is applied to a
3243 :class:`_schema.Table`,
3244 ``ForeignKey`` markers are automatically generated to be
3245 present on each associated :class:`_schema.Column`, which are also
3246 associated with the constraint object.
3248 Note that you cannot define a "composite" foreign key constraint,
3249 that is a constraint between a grouping of multiple parent/child
3250 columns, using ``ForeignKey`` objects. To define this grouping,
3251 the :class:`_schema.ForeignKeyConstraint` object must be used, and applied
3252 to the :class:`_schema.Table`. The associated ``ForeignKey`` objects
3253 are created automatically.
3255 The ``ForeignKey`` objects associated with an individual
3256 :class:`_schema.Column`
3257 object are available in the `foreign_keys` collection
3258 of that column.
3260 The target of a ``ForeignKey`` is described by three names -- a schema
3261 name, a table name and a column name -- which are available at
3262 :attr:`_schema.ForeignKey.target_tokens` as a
3263 :class:`_schema.ForeignKeyTarget` named tuple::
3265 >>> fk = ForeignKey("main_table.id")
3266 >>> fk.target_tokens
3267 ForeignKeyTarget(schema=None, table_name='main_table', column_name='id')
3269 The same three names may be given to ``ForeignKey`` in place of the
3270 dotted string, as either a two or three token tuple. This is the only
3271 way to name a target whose table or column name itself contains a dot,
3272 as the dotted string form has no way of telling such a dot apart from
3273 the separator between two names::
3275 ForeignKey(("my.tbl", "id"))
3276 ForeignKey(("my_schema", "my.tbl", "id"))
3278 .. versionadded:: 2.1 :attr:`_schema.ForeignKey.target_tokens`, and the
3279 tuple form of the target.
3281 Where the target was given as a :class:`_schema.Column` rather than by
3282 name, that column is available at
3283 :attr:`_schema.ForeignKey.target_column`; this is distinct from
3284 :attr:`_schema.ForeignKey.column`, which is the *resolved* target however
3285 it was specified. :attr:`_schema.ForeignKey.target_table_key` gives the
3286 key under which the referenced :class:`_schema.Table` is, or would be,
3287 registered in :attr:`_schema.MetaData.tables`.
3289 Further examples of foreign key configuration are in
3290 :ref:`metadata_foreignkeys`.
3292 """ # noqa: E501
3294 __visit_name__ = "foreign_key"
3296 parent: Column[Any]
3298 _table_column: Optional[Column[Any]]
3299 """Storage for :attr:`.ForeignKey.target_column`; read that instead.
3301 ``None`` when the target was given by name, in which case
3302 :attr:`.ForeignKey._given_tokens` holds those names.
3304 """
3306 _given_tokens: Optional[ForeignKeyTarget]
3307 """The names of the target, when the target was given by name.
3309 ``None`` when the target was given as a :class:`.Column`, in which case
3310 the names are derived from that column on demand -- read
3311 :attr:`.ForeignKey.target_tokens` rather than this.
3313 """
3315 def __init__(
3316 self,
3317 column: _DDLColumnReferenceArgument,
3318 _constraint: Optional[ForeignKeyConstraint] = None,
3319 use_alter: bool = False,
3320 name: _ConstraintNameArgument = None,
3321 onupdate: Optional[str] = None,
3322 ondelete: Optional[str] = None,
3323 deferrable: Optional[bool] = None,
3324 initially: Optional[str] = None,
3325 link_to_name: bool = False,
3326 match: Optional[str] = None,
3327 info: Optional[_InfoType] = None,
3328 comment: Optional[str] = None,
3329 _unresolvable: bool = False,
3330 **dialect_kw: Any,
3331 ):
3332 r"""
3333 Construct a column-level FOREIGN KEY.
3335 The :class:`_schema.ForeignKey` object when constructed generates a
3336 :class:`_schema.ForeignKeyConstraint`
3337 which is associated with the parent
3338 :class:`_schema.Table` object's collection of constraints.
3340 :param column: A single target column for the key relationship. A
3341 :class:`_schema.Column` object or a column name as a string:
3342 ``tablename.columnkey`` or ``schema.tablename.columnkey``.
3343 ``columnkey`` is the ``key`` which has been assigned to the column
3344 (defaults to the column name itself), unless ``link_to_name`` is
3345 ``True`` in which case the rendered name of the column is used.
3347 The target may also be given as a tuple of individual name
3348 tokens, either ``(table_name, column_name)`` or
3349 ``(schema, table_name, column_name)``. This is the only form
3350 which can refer to a name that itself contains a dot, as the
3351 dotted string form has no way of telling such a dot apart from
3352 the separator between two names::
3354 ForeignKey(("my.tbl", "z"))
3356 .. versionadded:: 2.1 The tuple form.
3358 :param name: Optional string. An in-database name for the key if
3359 `constraint` is not provided.
3361 :param onupdate: Optional string. If set, emit ON UPDATE <value> when
3362 issuing DDL for this constraint. Typical values include CASCADE,
3363 DELETE and RESTRICT.
3365 .. seealso::
3367 :ref:`on_update_on_delete`
3369 :param ondelete: Optional string. If set, emit ON DELETE <value> when
3370 issuing DDL for this constraint. Typical values include CASCADE,
3371 SET NULL and RESTRICT. Some dialects may allow for additional
3372 syntaxes.
3374 .. seealso::
3376 :ref:`on_update_on_delete`
3378 :param deferrable: Optional bool. If set, emit DEFERRABLE or NOT
3379 DEFERRABLE when issuing DDL for this constraint.
3381 :param initially: Optional string. If set, emit INITIALLY <value> when
3382 issuing DDL for this constraint.
3384 :param link_to_name: if True, the string name given in ``column`` is
3385 the rendered name of the referenced column, not its locally
3386 assigned ``key``.
3388 :param use_alter: passed to the underlying
3389 :class:`_schema.ForeignKeyConstraint`
3390 to indicate the constraint should
3391 be generated/dropped externally from the CREATE TABLE/ DROP TABLE
3392 statement. See :paramref:`_schema.ForeignKeyConstraint.use_alter`
3393 for further description.
3395 .. seealso::
3397 :paramref:`_schema.ForeignKeyConstraint.use_alter`
3399 :ref:`use_alter`
3401 :param match: Optional string. If set, emit MATCH <value> when issuing
3402 DDL for this constraint. Typical values include SIMPLE, PARTIAL
3403 and FULL.
3405 :param info: Optional data dictionary which will be populated into the
3406 :attr:`.SchemaItem.info` attribute of this object.
3408 :param comment: Optional string that will render an SQL comment on
3409 foreign key constraint creation.
3411 .. versionadded:: 2.0
3413 :param \**dialect_kw: Additional keyword arguments are dialect
3414 specific, and passed in the form ``<dialectname>_<argname>``. The
3415 arguments are ultimately handled by a corresponding
3416 :class:`_schema.ForeignKeyConstraint`.
3417 See the documentation regarding
3418 an individual dialect at :ref:`dialect_toplevel` for detail on
3419 documented arguments.
3421 """
3423 self._unresolvable = _unresolvable
3425 self._table_column, self._given_tokens = self._parse_colspec_argument(
3426 column
3427 )
3429 # the linked ForeignKeyConstraint.
3430 # ForeignKey will create this when parent Column
3431 # is attached to a Table, *or* ForeignKeyConstraint
3432 # object passes itself in when creating ForeignKey
3433 # markers.
3434 self.constraint = _constraint
3436 # .parent is not Optional under normal use
3437 self.parent = None # type: ignore[assignment]
3439 self.use_alter = use_alter
3440 self.name = name
3441 self.onupdate = onupdate
3442 self.ondelete = ondelete
3443 self.deferrable = deferrable
3444 self.initially = initially
3445 self.link_to_name = link_to_name
3446 self.match = match
3447 self.comment = comment
3448 if info:
3449 self.info = info
3450 self._unvalidated_dialect_kw = dialect_kw
3452 def _parse_colspec_argument(
3453 self,
3454 argument: _DDLColumnReferenceArgument,
3455 ) -> Tuple[Optional[Column[Any]], ForeignKeyTarget]:
3456 """Coerce the ``column`` argument into the target
3457 :class:`.ForeignKeyTarget`, along with the target :class:`.Column`
3458 itself if that's how the target was given.
3460 """
3461 if isinstance(argument, tuple):
3462 return None, ForeignKeyTarget._from_argument(argument)
3464 _colspec = coercions.expect(roles.DDLReferredColumnRole, argument)
3466 if isinstance(_colspec, str):
3467 return None, ForeignKeyTarget._from_string(_colspec)
3469 assert isinstance(_colspec, ColumnClause)
3471 table = _colspec.table
3472 if not isinstance(table, (type(None), TableClause)):
3473 # a Column of some other FromClause, e.g. a subquery or a join;
3474 # the target of a ForeignKey has to be a Table (or a TableClause,
3475 # or a Column not yet associated with either)
3476 raise exc.ArgumentError(
3477 f"ForeignKey target Column {_colspec!r} is associated with "
3478 f"{table!r}, which is not a Table; a foreign key may only "
3479 f"target a Column of a Table"
3480 )
3481 elif table is None:
3482 # a Column not yet associated with a Table; this is the
3483 # declarative mixin + declared_attr case, where the target column
3484 # is attached to its Table after this ForeignKey is constructed
3485 return _colspec, ForeignKeyTarget(None, _colspec.key, None)
3486 else:
3487 return _colspec, ForeignKeyTarget(
3488 table.schema, table.name, _colspec.key
3489 )
3491 def __repr__(self) -> str:
3492 tokens = self.target_tokens
3493 tokens_no_dots = self.target_tokens._tokens_no_dots()
3495 if tokens_no_dots:
3496 return f"ForeignKey({'.'.join(tokens_no_dots)!r})"
3497 else:
3498 # no string form for this target, so name the tokens; this still
3499 # round trips, as ForeignKeyTarget is accepted by the constructor
3500 return f"ForeignKey({tokens!r})"
3502 @util.deprecated(
3503 "1.4",
3504 "The :meth:`_schema.ForeignKey.copy` method is deprecated "
3505 "and will be removed in a future release.",
3506 )
3507 def copy(self, *, schema: Optional[str] = None, **kw: Any) -> ForeignKey:
3508 return self._copy(schema=schema, **kw)
3510 def _copy(self, *, schema: Optional[str] = None, **kw: Any) -> ForeignKey:
3511 """Produce a copy of this :class:`_schema.ForeignKey` object.
3513 The new :class:`_schema.ForeignKey` will not be bound
3514 to any :class:`_schema.Column`.
3516 This method is usually used by the internal
3517 copy procedures of :class:`_schema.Column`, :class:`_schema.Table`,
3518 and :class:`_schema.MetaData`.
3520 :param schema: The returned :class:`_schema.ForeignKey` will
3521 reference the original table and column name, qualified
3522 by the given string schema name.
3524 """
3525 fk = ForeignKey(
3526 self._copy_tokens(schema=schema),
3527 use_alter=self.use_alter,
3528 name=self.name,
3529 onupdate=self.onupdate,
3530 ondelete=self.ondelete,
3531 deferrable=self.deferrable,
3532 initially=self.initially,
3533 link_to_name=self.link_to_name,
3534 match=self.match,
3535 comment=self.comment,
3536 **self._unvalidated_dialect_kw,
3537 )
3538 return self._schema_item_copy(fk)
3540 @property
3541 def target_tokens(self) -> ForeignKeyTarget:
3542 """Return the target of this :class:`_schema.ForeignKey` as three
3543 individual name tokens.
3545 The return value is a :class:`_schema.ForeignKeyTarget` named tuple of
3546 ``(schema, table_name, column_name)``. This is the representation
3547 SQLAlchemy itself computes against; unlike
3548 :attr:`_schema.ForeignKey.target_fullname` it is available in all
3549 cases, including when one of the names contains a dot::
3551 >>> from sqlalchemy import Column, ForeignKey, Integer, MetaData, Table
3552 >>> m = MetaData()
3553 >>> r = Table("my.tbl", m, Column("z", Integer, primary_key=True))
3554 >>> t = Table("t", m, Column("a", Integer, ForeignKey(r.c.z)))
3555 >>> list(t.c.a.foreign_keys)[0].target_tokens
3556 ForeignKeyTarget(schema=None, table_name='my.tbl', column_name='z')
3558 The tokens describe the target as it was specified, and are available
3559 whether or not the target :class:`_schema.Column` has been resolved;
3560 for the resolved column, see :attr:`_schema.ForeignKey.column`.
3562 .. versionadded:: 2.1
3564 .. seealso::
3566 :class:`_schema.ForeignKeyTarget`
3568 """ # noqa: E501
3570 col = self._table_column
3571 if col is None:
3572 assert self._given_tokens is not None
3573 return self._given_tokens
3575 # derived on demand rather than captured up front: a target Column
3576 # may gain both its name and its Table *after* this ForeignKey is
3577 # constructed, which is what a declarative mixin using declared_attr
3578 # does (see test_fk_mixin_self_referential_declared_attr)
3579 table = col.table
3580 if table is None:
3581 return ForeignKeyTarget(None, col.key, None)
3583 return ForeignKeyTarget(table.schema, table.name, col.key)
3585 @property
3586 def target_column(self) -> Optional[Column[Any]]:
3587 """Return the target :class:`_schema.Column` of this
3588 :class:`_schema.ForeignKey`, if the target was given as one.
3590 Returns ``None`` when the target was instead given by name, as a
3591 string or as :class:`_schema.ForeignKeyTarget`; in that case only
3592 :attr:`_schema.ForeignKey.target_tokens` describes the target until
3593 it is resolved.
3595 This is distinct from :attr:`_schema.ForeignKey.column`, which is the
3596 *resolved* target however it was specified, and which raises if the
3597 target can't be resolved. Note also that a target given as a
3598 :class:`_schema.Column` need not be associated with a
3599 :class:`_schema.Table` yet.
3601 .. versionadded:: 2.1
3603 .. seealso::
3605 :attr:`_schema.ForeignKey.target_tokens`
3607 :attr:`_schema.ForeignKey.column`
3609 """
3610 return self._table_column
3612 @property
3613 def _column_tokens(self) -> ForeignKeyTarget:
3614 """legacy private name for :attr:`.ForeignKey.target_tokens`"""
3616 return self.target_tokens
3618 @property
3619 def _colspec(self) -> Union[str, Column[Any]]:
3620 """Legacy accessor for the target in its pre-2.1 form, either the
3621 target :class:`.Column` or a dotted string.
3623 Raises :class:`.InvalidRequestError` for a target which has no dotted
3624 string form. Kept for the benefit of third party code which reads it;
3625 :attr:`.ForeignKey.target_tokens` is what SQLAlchemy itself uses.
3627 """
3628 col = self.target_column
3629 return self.target_tokens._as_string() if col is None else col
3631 def _copy_tokens(
3632 self,
3633 schema: Optional[
3634 Union[
3635 str,
3636 Literal[SchemaConst.RETAIN_SCHEMA, SchemaConst.BLANK_SCHEMA],
3637 ]
3638 ] = None,
3639 table_name: Optional[str] = None,
3640 _is_copy: bool = False,
3641 ) -> ForeignKeyTarget:
3642 """Return the tokens for a copy of this :class:`_schema.ForeignKey`,
3643 optionally rewriting the schema and/or table name.
3645 """
3647 col = self.target_column
3649 if _is_copy and col is not None and col.table is None:
3650 raise exc.InvalidRequestError(
3651 f"Can't copy ForeignKey object which refers to "
3652 f"non-table bound Column {col!r}"
3653 )
3655 tokens = self.target_tokens
3657 if schema not in (None, RETAIN_SCHEMA):
3658 return ForeignKeyTarget(
3659 None if schema is BLANK_SCHEMA else schema,
3660 table_name if table_name is not None else tokens.table_name,
3661 tokens.column_name,
3662 )
3663 elif table_name:
3664 return tokens._replace(table_name=table_name)
3665 else:
3666 return tokens
3668 def _get_colspec(self) -> str:
3669 """legacy method for :attr:`.ForeignKey.target_fullname`.
3671 Retained as third party code makes use of it; new code should use
3672 :attr:`.ForeignKey.target_tokens`.
3674 """
3676 return self.target_fullname
3678 @property
3679 def _referred_schema(self) -> Optional[str]:
3680 return self.target_tokens.schema
3682 @property
3683 def target_table_key(self) -> Optional[str]:
3684 """Return the key under which the target :class:`_schema.Table` is,
3685 or would be, registered in :attr:`_schema.MetaData.tables`.
3687 This is derived from :attr:`_schema.ForeignKey.target_tokens`, and is
3688 available whether or not the target table exists yet, making it
3689 usable while a :class:`_schema.Table` is still being constructed.
3691 Returns ``None`` in the one case where no key can be named: the
3692 target was given as a :class:`_schema.Column` which is not yet
3693 associated with a :class:`_schema.Table`.
3695 .. versionadded:: 2.1
3697 .. seealso::
3699 :attr:`_schema.ForeignKey.target_tokens`
3701 """
3702 col = self.target_column
3703 if col is not None and col.table is None:
3704 # target Column not yet associated with a Table, so there is no
3705 # key to report; the tokens name the column, not a table
3706 return None
3708 schema, tname, colname = self.target_tokens
3709 return _get_table_key(tname, schema)
3711 @property
3712 def target_fullname(self) -> str:
3713 """Return the target of this :class:`_schema.ForeignKey` as a single
3714 dotted string, e.g. ``"schema.tablename.columnname"``.
3716 This is usually the equivalent of the string-based
3717 ``"tablename.colname"`` argument first passed to the object's
3718 constructor.
3720 .. versionchanged:: 2.1 This attribute raises
3721 :class:`.InvalidRequestError` when the target table or column name
3722 itself contains a dot, as a dotted string can't distinguish such a
3723 name from the separator between names. The dotted form is now a
3724 legacy convenience; :attr:`_schema.ForeignKey.target_tokens` is the
3725 representation that is available in all cases, and is what
3726 SQLAlchemy itself makes use of.
3728 .. seealso::
3730 :attr:`_schema.ForeignKey.target_tokens`
3732 """
3733 return self.target_tokens._as_string()
3735 def references(self, table: Table) -> bool:
3736 """Return True if the given :class:`_schema.Table`
3737 is referenced by this
3738 :class:`_schema.ForeignKey`."""
3740 return table.corresponding_column(self.column) is not None
3742 def get_referent(self, table: FromClause) -> Optional[Column[Any]]:
3743 """Return the :class:`_schema.Column` in the given
3744 :class:`_schema.Table` (or any :class:`.FromClause`)
3745 referenced by this :class:`_schema.ForeignKey`.
3747 Returns None if this :class:`_schema.ForeignKey`
3748 does not reference the given
3749 :class:`_schema.Table`.
3751 """
3752 # our column is a Column, and any subquery etc. proxying us
3753 # would be doing so via another Column, so that's what would
3754 # be returned here
3755 return table.columns.corresponding_column(self.column) # type: ignore[return-value] # noqa: E501
3757 def _resolve_col_tokens(self) -> Tuple[Table, str, Optional[str]]:
3758 if self.parent is None:
3759 raise exc.InvalidRequestError(
3760 "this ForeignKey object does not yet have a "
3761 "parent Column associated with it."
3762 )
3764 elif self.parent.table is None:
3765 raise exc.InvalidRequestError(
3766 "this ForeignKey's parent column is not yet associated "
3767 "with a Table."
3768 )
3770 parenttable = self.parent.table
3772 if self._unresolvable:
3773 schema, tname, colname = self.target_tokens
3774 tablekey = _get_table_key(tname, schema)
3775 return parenttable, tablekey, colname
3777 # assertion
3778 # basically Column._make_proxy() sends the actual
3779 # target Column to the ForeignKey object, so the
3780 # string resolution here is never called.
3781 for c in self.parent.base_columns:
3782 if isinstance(c, Column):
3783 assert c.table is parenttable
3784 break
3785 else:
3786 assert False
3787 ######################
3789 schema, tname, colname = self.target_tokens
3791 if schema is None and parenttable.metadata.schema is not None:
3792 schema = parenttable.metadata.schema
3794 tablekey = _get_table_key(tname, schema)
3795 return parenttable, tablekey, colname
3797 def _link_to_col_by_colstring(
3798 self, parenttable: Table, table: Table, colname: Optional[str]
3799 ) -> Column[Any]:
3800 _column = None
3801 if colname is None:
3802 # colname is None in the case that ForeignKey argument
3803 # was specified as table name only, in which case we
3804 # match the column name to the same column on the
3805 # parent.
3806 # this use case wasn't working in later 1.x series
3807 # as it had no test coverage; fixed in 2.0
3808 parent = self.parent
3809 assert parent is not None
3810 key = parent.key
3811 _column = table.c.get(key, None)
3812 elif self.link_to_name:
3813 key = colname
3814 for c in table.c:
3815 if c.name == colname:
3816 _column = c
3817 else:
3818 key = colname
3819 _column = table.c.get(colname, None)
3821 if _column is None:
3822 raise exc.NoReferencedColumnError(
3823 "Could not initialize target column "
3824 f"for ForeignKey {self.target_tokens._description()} "
3825 f"on table '{parenttable.name}': "
3826 f"table '{table.name}' has no column named '{key}'",
3827 table.name,
3828 key,
3829 )
3831 return _column
3833 def _set_target_column(self, column: Column[Any]) -> None:
3834 assert self.parent is not None
3836 # propagate TypeEngine to parent if it didn't have one
3837 if self.parent.type._isnull:
3838 self.parent.type = column.type
3840 # super-edgy case, if other FKs point to our column,
3841 # they'd get the type propagated out also.
3843 def set_type(fk: ForeignKey) -> None:
3844 if fk.parent.type._isnull:
3845 fk.parent.type = column.type
3847 self.parent._setup_on_memoized_fks(set_type)
3849 self.column = column # type: ignore[misc]
3851 @util.ro_memoized_property
3852 def column(self) -> Column[Any]:
3853 """Return the target :class:`_schema.Column` referenced by this
3854 :class:`_schema.ForeignKey`.
3856 If no target column has been established, an exception
3857 is raised.
3859 """
3860 return self._resolve_column()
3862 @overload
3863 def _resolve_column(
3864 self, *, raiseerr: Literal[True] = ...
3865 ) -> Column[Any]: ...
3867 @overload
3868 def _resolve_column(
3869 self, *, raiseerr: bool = ...
3870 ) -> Optional[Column[Any]]: ...
3872 def _resolve_column(
3873 self, *, raiseerr: bool = True
3874 ) -> Optional[Column[Any]]:
3875 target_column = self.target_column
3877 if target_column is None:
3878 parenttable, tablekey, colname = self._resolve_col_tokens()
3880 if self._unresolvable or tablekey not in parenttable.metadata:
3881 if not raiseerr:
3882 return None
3883 raise exc.NoReferencedTableError(
3884 f"Foreign key associated with column "
3885 f"'{self.parent}' could not find "
3886 f"table '{tablekey}' with which to generate a "
3887 f"foreign key to target column '{colname}'",
3888 tablekey,
3889 )
3890 elif parenttable.key not in parenttable.metadata:
3891 if not raiseerr:
3892 return None
3893 raise exc.InvalidRequestError(
3894 f"Table {parenttable} is no longer associated with its "
3895 "parent MetaData"
3896 )
3897 else:
3898 table = parenttable.metadata.tables[tablekey]
3899 return self._link_to_col_by_colstring(
3900 parenttable, table, colname
3901 )
3903 else:
3904 assert target_column is not None
3905 return target_column
3907 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
3908 assert isinstance(parent, Column)
3910 if self.parent is not None and self.parent is not parent:
3911 raise exc.InvalidRequestError(
3912 "This ForeignKey already has a parent !"
3913 )
3914 self.parent = parent
3915 self.parent.foreign_keys.add(self)
3916 self.parent._on_table_attach(self._set_table)
3918 def _set_remote_table(self, table: Table) -> None:
3919 parenttable, _, colname = self._resolve_col_tokens()
3920 _column = self._link_to_col_by_colstring(parenttable, table, colname)
3921 self._set_target_column(_column)
3922 assert self.constraint is not None
3923 self.constraint._validate_dest_table(table)
3925 def _remove_from_metadata(self, metadata: MetaData) -> None:
3926 parenttable, table_key, colname = self._resolve_col_tokens()
3927 fk_key = (table_key, colname)
3929 if self in metadata._fk_memos[fk_key]:
3930 # TODO: no test coverage for self not in memos
3931 metadata._fk_memos[fk_key].remove(self)
3933 def _set_table(self, column: Column[Any], table: Table) -> None:
3934 # standalone ForeignKey - create ForeignKeyConstraint
3935 # on the hosting Table when attached to the Table.
3936 assert isinstance(table, Table)
3937 if self.constraint is None:
3938 self.constraint = ForeignKeyConstraint(
3939 [],
3940 [],
3941 use_alter=self.use_alter,
3942 name=self.name,
3943 onupdate=self.onupdate,
3944 ondelete=self.ondelete,
3945 deferrable=self.deferrable,
3946 initially=self.initially,
3947 match=self.match,
3948 comment=self.comment,
3949 **self._unvalidated_dialect_kw,
3950 )
3951 self.constraint._append_element(column, self)
3952 self.constraint._set_parent_with_dispatch(table)
3953 table.foreign_keys.add(self)
3954 # set up remote ".column" attribute, or a note to pick it
3955 # up when the other Table/Column shows up
3957 target_column = self.target_column
3958 if target_column is None:
3959 parenttable, table_key, colname = self._resolve_col_tokens()
3960 fk_key = (table_key, colname)
3961 if table_key in parenttable.metadata.tables:
3962 table = parenttable.metadata.tables[table_key]
3963 try:
3964 _column = self._link_to_col_by_colstring(
3965 parenttable, table, colname
3966 )
3967 except exc.NoReferencedColumnError:
3968 # this is OK, we'll try later
3969 pass
3970 else:
3971 self._set_target_column(_column)
3973 parenttable.metadata._fk_memos[fk_key].append(self)
3974 else:
3975 self._set_target_column(target_column)
3978if TYPE_CHECKING:
3980 def default_is_sequence(
3981 obj: Optional[DefaultGenerator],
3982 ) -> TypeGuard[Sequence]: ...
3984 def default_is_clause_element(
3985 obj: Optional[DefaultGenerator],
3986 ) -> TypeGuard[ColumnElementColumnDefault]: ...
3988 def default_is_scalar(
3989 obj: Optional[DefaultGenerator],
3990 ) -> TypeGuard[ScalarElementColumnDefault]: ...
3992else:
3993 default_is_sequence = operator.attrgetter("is_sequence")
3995 default_is_clause_element = operator.attrgetter("is_clause_element")
3997 default_is_scalar = operator.attrgetter("is_scalar")
4000class DefaultGenerator(Executable, SchemaItem):
4001 """Base class for column *default* values.
4003 This object is only present on column.default or column.onupdate.
4004 It's not valid as a server default.
4006 """
4008 __visit_name__ = "default_generator"
4010 _is_default_generator = True
4011 is_sequence = False
4012 is_identity = False
4013 is_server_default = False
4014 is_clause_element = False
4015 is_callable = False
4016 is_scalar = False
4017 has_arg = False
4018 is_sentinel = False
4019 _is_monotonic_fn = False
4020 column: Optional[Column[Any]]
4022 def __init__(self, for_update: bool = False) -> None:
4023 self.for_update = for_update
4025 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4026 if TYPE_CHECKING:
4027 assert isinstance(parent, Column)
4028 self.column = parent
4029 if self.for_update:
4030 self.column.onupdate = self
4031 else:
4032 self.column.default = self
4034 def _copy(self) -> DefaultGenerator:
4035 raise NotImplementedError()
4037 def _execute_on_connection(
4038 self,
4039 connection: Connection,
4040 distilled_params: _CoreMultiExecuteParams,
4041 execution_options: CoreExecuteOptionsParameter,
4042 ) -> Any:
4043 util.warn_deprecated(
4044 "Using the .execute() method to invoke a "
4045 "DefaultGenerator object is deprecated; please use "
4046 "the .scalar() method.",
4047 "2.0",
4048 )
4049 return self._execute_on_scalar(
4050 connection, distilled_params, execution_options
4051 )
4053 def _execute_on_scalar(
4054 self,
4055 connection: Connection,
4056 distilled_params: _CoreMultiExecuteParams,
4057 execution_options: CoreExecuteOptionsParameter,
4058 ) -> Any:
4059 return connection._execute_default(
4060 self, distilled_params, execution_options
4061 )
4064class ColumnDefault(DefaultGenerator, ABC):
4065 """A plain default value on a column.
4067 This could correspond to a constant, a callable function,
4068 or a SQL clause.
4070 :class:`.ColumnDefault` is generated automatically
4071 whenever the ``default``, ``onupdate`` arguments of
4072 :class:`_schema.Column` are used. A :class:`.ColumnDefault`
4073 can be passed positionally as well.
4075 For example, the following::
4077 Column("foo", Integer, default=50)
4079 Is equivalent to::
4081 Column("foo", Integer, ColumnDefault(50))
4083 """
4085 arg: Any
4087 _is_monotonic_fn = False
4089 @overload
4090 def __new__(
4091 cls, arg: Callable[..., Any], for_update: bool = ...
4092 ) -> CallableColumnDefault: ...
4094 @overload
4095 def __new__(
4096 cls, arg: ColumnElement[Any], for_update: bool = ...
4097 ) -> ColumnElementColumnDefault: ...
4099 # if I return ScalarElementColumnDefault here, which is what's actually
4100 # returned, mypy complains that
4101 # overloads overlap w/ incompatible return types.
4102 @overload
4103 def __new__(cls, arg: object, for_update: bool = ...) -> ColumnDefault: ...
4105 def __new__(
4106 cls, arg: Any = None, for_update: bool = False
4107 ) -> ColumnDefault:
4108 """Construct a new :class:`.ColumnDefault`.
4111 :param arg: argument representing the default value.
4112 May be one of the following:
4114 * a plain non-callable Python value, such as a
4115 string, integer, boolean, or other simple type.
4116 The default value will be used as is each time.
4117 * a SQL expression, that is one which derives from
4118 :class:`_expression.ColumnElement`. The SQL expression will
4119 be rendered into the INSERT or UPDATE statement,
4120 or in the case of a primary key column when
4121 RETURNING is not used may be
4122 pre-executed before an INSERT within a SELECT.
4123 * A Python callable. The function will be invoked for each
4124 new row subject to an INSERT or UPDATE.
4125 The callable must accept exactly
4126 zero or one positional arguments. The one-argument form
4127 will receive an instance of the :class:`.ExecutionContext`,
4128 which provides contextual information as to the current
4129 :class:`_engine.Connection` in use as well as the current
4130 statement and parameters.
4132 """
4134 if isinstance(arg, FetchedValue):
4135 raise exc.ArgumentError(
4136 "ColumnDefault may not be a server-side default type."
4137 )
4138 elif callable(arg):
4139 cls = CallableColumnDefault
4140 elif isinstance(arg, ClauseElement):
4141 cls = ColumnElementColumnDefault
4142 elif arg is not None:
4143 cls = ScalarElementColumnDefault
4145 return object.__new__(cls)
4147 def __repr__(self) -> str:
4148 return f"{self.__class__.__name__}({self.arg!r})"
4151class ScalarElementColumnDefault(ColumnDefault):
4152 """default generator for a fixed scalar Python value
4154 .. versionadded:: 2.0
4156 """
4158 is_scalar = True
4159 has_arg = True
4161 def __init__(self, arg: Any, for_update: bool = False) -> None:
4162 self.for_update = for_update
4163 self.arg = arg
4165 def _copy(self) -> ScalarElementColumnDefault:
4166 return ScalarElementColumnDefault(
4167 arg=self.arg, for_update=self.for_update
4168 )
4171class _InsertSentinelColumnDefault(ColumnDefault):
4172 """Default generator that's specific to the use of a "sentinel" column
4173 when using the insertmanyvalues feature.
4175 This default is used as part of the :func:`_schema.insert_sentinel`
4176 construct.
4178 """
4180 is_sentinel = True
4181 for_update = False
4182 arg = None
4184 def __new__(cls) -> _InsertSentinelColumnDefault:
4185 return object.__new__(cls)
4187 def __init__(self) -> None:
4188 pass
4190 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4191 col = cast("Column[Any]", parent)
4192 if not col._insert_sentinel:
4193 raise exc.ArgumentError(
4194 "The _InsertSentinelColumnDefault may only be applied to a "
4195 "Column marked as insert_sentinel=True"
4196 )
4197 elif not col.nullable:
4198 raise exc.ArgumentError(
4199 "The _InsertSentinelColumnDefault may only be applied to a "
4200 "Column that is nullable"
4201 )
4203 super()._set_parent(parent, **kw)
4205 def _copy(self) -> _InsertSentinelColumnDefault:
4206 return _InsertSentinelColumnDefault()
4209_SQLExprDefault = Union["ColumnElement[Any]", "TextClause"]
4212class ColumnElementColumnDefault(ColumnDefault):
4213 """default generator for a SQL expression
4215 .. versionadded:: 2.0
4217 """
4219 is_clause_element = True
4220 has_arg = True
4221 arg: _SQLExprDefault
4223 def __init__(
4224 self,
4225 arg: _SQLExprDefault,
4226 for_update: bool = False,
4227 ) -> None:
4228 self.for_update = for_update
4229 self.arg = arg
4231 def _copy(self) -> ColumnElementColumnDefault:
4232 return ColumnElementColumnDefault(
4233 arg=self.arg, for_update=self.for_update
4234 )
4236 @util.memoized_property
4237 @util.preload_module("sqlalchemy.sql.functions")
4238 def _is_monotonic_fn(self) -> bool:
4239 functions = util.preloaded.sql_functions
4240 return (
4241 isinstance(self.arg, functions.FunctionElement)
4242 and self.arg.monotonic
4243 )
4245 @util.memoized_property
4246 @util.preload_module("sqlalchemy.sql.sqltypes")
4247 def _arg_is_typed(self) -> bool:
4248 sqltypes = util.preloaded.sql_sqltypes
4250 return not isinstance(self.arg.type, sqltypes.NullType)
4253class _CallableColumnDefaultProtocol(Protocol):
4254 def __call__(self, context: ExecutionContext) -> Any: ...
4257class CallableColumnDefault(ColumnDefault):
4258 """default generator for a callable Python function
4260 .. versionadded:: 2.0
4262 """
4264 is_callable = True
4265 arg: _CallableColumnDefaultProtocol
4266 has_arg = True
4268 def __init__(
4269 self,
4270 arg: Union[_CallableColumnDefaultProtocol, Callable[[], Any]],
4271 for_update: bool = False,
4272 ) -> None:
4273 self.for_update = for_update
4274 self.arg = self._maybe_wrap_callable(arg)
4276 def _copy(self) -> CallableColumnDefault:
4277 return CallableColumnDefault(arg=self.arg, for_update=self.for_update)
4279 def _maybe_wrap_callable(
4280 self, fn: Union[_CallableColumnDefaultProtocol, Callable[[], Any]]
4281 ) -> _CallableColumnDefaultProtocol:
4282 """Wrap callables that don't accept a context.
4284 This is to allow easy compatibility with default callables
4285 that aren't specific to accepting of a context.
4287 """
4289 try:
4290 argspec = util.get_callable_argspec(fn, no_self=True)
4291 except TypeError:
4292 return util.wrap_callable(lambda ctx: fn(), fn) # type: ignore[call-arg, no-any-return, no-untyped-call] # noqa: E501
4294 defaulted = argspec[3] is not None and len(argspec[3]) or 0
4295 positionals = len(argspec[0]) - defaulted
4297 if positionals == 0:
4298 return util.wrap_callable(lambda ctx: fn(), fn) # type: ignore[call-arg, no-any-return, no-untyped-call] # noqa: E501
4300 elif positionals == 1:
4301 return fn # type: ignore[return-value]
4302 else:
4303 raise exc.ArgumentError(
4304 "ColumnDefault Python function takes zero or one "
4305 "positional arguments"
4306 )
4309class IdentityOptions(DialectKWArgs):
4310 """Defines options for a named database sequence or an identity column.
4312 .. seealso::
4314 :class:`.Sequence`
4316 """
4318 def __init__(
4319 self,
4320 start: Optional[int] = None,
4321 increment: Optional[int] = None,
4322 minvalue: Optional[int] = None,
4323 maxvalue: Optional[int] = None,
4324 nominvalue: Optional[bool] = None,
4325 nomaxvalue: Optional[bool] = None,
4326 cycle: Optional[bool] = None,
4327 cache: Optional[int] = None,
4328 order: Optional[bool] = None,
4329 **dialect_kw: Any,
4330 ) -> None:
4331 """Construct a :class:`.IdentityOptions` object.
4333 See the :class:`.Sequence` documentation for a complete description
4334 of the parameters.
4336 :param start: the starting index of the sequence.
4337 :param increment: the increment value of the sequence.
4338 :param minvalue: the minimum value of the sequence.
4339 :param maxvalue: the maximum value of the sequence.
4340 :param nominvalue: no minimum value of the sequence.
4341 :param nomaxvalue: no maximum value of the sequence.
4342 :param cycle: allows the sequence to wrap around when the maxvalue
4343 or minvalue has been reached.
4344 :param cache: optional integer value; number of future values in the
4345 sequence which are calculated in advance.
4346 :param order: optional boolean value; if ``True``, renders the
4347 ORDER keyword.
4349 .. deprecated:: 2.1 Use ``oracle_order`` instead.
4351 """
4352 self.start = start
4353 self.increment = increment
4354 self.minvalue = minvalue
4355 self.maxvalue = maxvalue
4356 self.nominvalue = nominvalue
4357 self.nomaxvalue = nomaxvalue
4358 self.cycle = cycle
4359 self.cache = cache
4360 if order is not None:
4361 if "oracle_order" in dialect_kw:
4362 raise exc.ArgumentError(
4363 "Cannot specify both 'order' and 'oracle_order'. "
4364 "Please use only 'oracle_order'."
4365 )
4366 dialect_kw["oracle_order"] = order
4367 self._validate_dialect_kwargs(dialect_kw)
4369 @property
4370 def _increment_is_negative(self) -> bool:
4371 return self.increment is not None and self.increment < 0
4373 @property
4374 def order(self) -> Optional[bool]:
4375 """Alias of the ``dialect_kwargs`` ``'oracle_order'``.
4377 .. deprecated:: 2.1 The 'order' attribute is deprecated.
4378 """
4379 value: Optional[bool] = self.dialect_kwargs.get("oracle_order")
4380 return value
4382 def _as_dict(self) -> Dict[str, Any]:
4383 return {
4384 k: v
4385 for k, v in {
4386 "start": self.start,
4387 "increment": self.increment,
4388 "minvalue": self.minvalue,
4389 "maxvalue": self.maxvalue,
4390 "nominvalue": self.nominvalue,
4391 "nomaxvalue": self.nomaxvalue,
4392 "cycle": self.cycle,
4393 "cache": self.cache,
4394 }.items()
4395 if v != None
4396 }
4399class Sequence(HasSchemaAttr, IdentityOptions, DefaultGenerator):
4400 """Represents a named database sequence.
4402 The :class:`.Sequence` object represents the name and configurational
4403 parameters of a database sequence. It also represents
4404 a construct that can be "executed" by a SQLAlchemy :class:`_engine.Engine`
4405 or :class:`_engine.Connection`,
4406 rendering the appropriate "next value" function
4407 for the target database and returning a result.
4409 The :class:`.Sequence` is typically associated with a primary key column::
4411 some_table = Table(
4412 "some_table",
4413 metadata,
4414 Column(
4415 "id",
4416 Integer,
4417 Sequence("some_table_seq", start=1),
4418 primary_key=True,
4419 ),
4420 )
4422 When CREATE TABLE is emitted for the above :class:`_schema.Table`, if the
4423 target platform supports sequences, a CREATE SEQUENCE statement will
4424 be emitted as well. For platforms that don't support sequences,
4425 the :class:`.Sequence` construct is ignored.
4427 .. seealso::
4429 :ref:`defaults_sequences`
4431 :class:`.CreateSequence`
4433 :class:`.DropSequence`
4435 """
4437 __visit_name__ = "sequence"
4439 is_sequence = True
4441 column: Optional[Column[Any]]
4442 data_type: Optional[TypeEngine[int]]
4444 metadata: Optional[MetaData]
4446 @util.deprecated_params(
4447 order=(
4448 "2.1",
4449 "This parameter is supported only by Oracle Database, "
4450 "use ``oracle_order`` instead.",
4451 )
4452 )
4453 def __init__(
4454 self,
4455 name: str,
4456 start: Optional[int] = None,
4457 increment: Optional[int] = None,
4458 minvalue: Optional[int] = None,
4459 maxvalue: Optional[int] = None,
4460 nominvalue: Optional[bool] = None,
4461 nomaxvalue: Optional[bool] = None,
4462 cycle: Optional[bool] = None,
4463 schema: Optional[Union[str, Literal[SchemaConst.BLANK_SCHEMA]]] = None,
4464 cache: Optional[int] = None,
4465 order: Optional[bool] = None,
4466 data_type: Optional[_TypeEngineArgument[int]] = None,
4467 optional: bool = False,
4468 quote: Optional[bool] = None,
4469 metadata: Optional[MetaData] = None,
4470 quote_schema: Optional[bool] = None,
4471 for_update: bool = False,
4472 **dialect_kw: Any,
4473 ) -> None:
4474 """Construct a :class:`.Sequence` object.
4476 :param name: the name of the sequence.
4478 :param start: the starting index of the sequence. This value is
4479 used when the CREATE SEQUENCE command is emitted to the database
4480 as the value of the "START WITH" clause. If ``None``, the
4481 clause is omitted, which on most platforms indicates a starting
4482 value of 1.
4484 .. versionchanged:: 2.0 The :paramref:`.Sequence.start` parameter
4485 is required in order to have DDL emit "START WITH". This is a
4486 reversal of a change made in version 1.4 which would implicitly
4487 render "START WITH 1" if the :paramref:`.Sequence.start` were
4488 not included. See :ref:`change_7211` for more detail.
4490 :param increment: the increment value of the sequence. This
4491 value is used when the CREATE SEQUENCE command is emitted to
4492 the database as the value of the "INCREMENT BY" clause. If ``None``,
4493 the clause is omitted, which on most platforms indicates an
4494 increment of 1.
4495 :param minvalue: the minimum value of the sequence. This
4496 value is used when the CREATE SEQUENCE command is emitted to
4497 the database as the value of the "MINVALUE" clause. If ``None``,
4498 the clause is omitted, which on most platforms indicates a
4499 minvalue of 1 and -2^63-1 for ascending and descending sequences,
4500 respectively.
4502 :param maxvalue: the maximum value of the sequence. This
4503 value is used when the CREATE SEQUENCE command is emitted to
4504 the database as the value of the "MAXVALUE" clause. If ``None``,
4505 the clause is omitted, which on most platforms indicates a
4506 maxvalue of 2^63-1 and -1 for ascending and descending sequences,
4507 respectively.
4509 :param nominvalue: no minimum value of the sequence. This
4510 value is used when the CREATE SEQUENCE command is emitted to
4511 the database as the value of the "NO MINVALUE" clause. If ``None``,
4512 the clause is omitted, which on most platforms indicates a
4513 minvalue of 1 and -2^63-1 for ascending and descending sequences,
4514 respectively.
4516 :param nomaxvalue: no maximum value of the sequence. This
4517 value is used when the CREATE SEQUENCE command is emitted to
4518 the database as the value of the "NO MAXVALUE" clause. If ``None``,
4519 the clause is omitted, which on most platforms indicates a
4520 maxvalue of 2^63-1 and -1 for ascending and descending sequences,
4521 respectively.
4523 :param cycle: allows the sequence to wrap around when the maxvalue
4524 or minvalue has been reached by an ascending or descending sequence
4525 respectively. This value is used when the CREATE SEQUENCE command
4526 is emitted to the database as the "CYCLE" clause. If the limit is
4527 reached, the next number generated will be the minvalue or maxvalue,
4528 respectively. If cycle=False (the default) any calls to nextval
4529 after the sequence has reached its maximum value will return an
4530 error.
4532 :param schema: optional schema name for the sequence, if located
4533 in a schema other than the default. The rules for selecting the
4534 schema name when a :class:`_schema.MetaData`
4535 is also present are the same
4536 as that of :paramref:`_schema.Table.schema`.
4538 :param cache: optional integer value; number of future values in the
4539 sequence which are calculated in advance. Renders the CACHE keyword
4540 understood by Oracle Database and PostgreSQL.
4542 :param order: optional boolean value; if ``True``, renders the
4543 ORDER keyword, understood by Oracle Database, indicating the sequence
4544 is definitively ordered. May be necessary to provide deterministic
4545 ordering using Oracle RAC.
4547 :param data_type: The type to be returned by the sequence, for
4548 dialects that allow us to choose between INTEGER, BIGINT, etc.
4549 (e.g., mssql).
4551 .. versionadded:: 1.4.0
4553 :param optional: boolean value, when ``True``, indicates that this
4554 :class:`.Sequence` object only needs to be explicitly generated
4555 on backends that don't provide another way to generate primary
4556 key identifiers. Currently, it essentially means, "don't create
4557 this sequence on the PostgreSQL backend, where the SERIAL keyword
4558 creates a sequence for us automatically".
4559 :param quote: boolean value, when ``True`` or ``False``, explicitly
4560 forces quoting of the :paramref:`_schema.Sequence.name` on or off.
4561 When left at its default of ``None``, normal quoting rules based
4562 on casing and reserved words take place.
4563 :param quote_schema: Set the quoting preferences for the ``schema``
4564 name.
4566 :param metadata: optional :class:`_schema.MetaData` object which this
4567 :class:`.Sequence` will be associated with. A :class:`.Sequence`
4568 that is associated with a :class:`_schema.MetaData`
4569 gains the following
4570 capabilities:
4572 * The :class:`.Sequence` will inherit the
4573 :paramref:`_schema.MetaData.schema`
4574 parameter specified to the target :class:`_schema.MetaData`, which
4575 affects the production of CREATE / DROP DDL, if any.
4577 * The :meth:`.Sequence.create` and :meth:`.Sequence.drop` methods
4578 automatically use the engine bound to the :class:`_schema.MetaData`
4579 object, if any.
4581 * The :meth:`_schema.MetaData.create_all` and
4582 :meth:`_schema.MetaData.drop_all`
4583 methods will emit CREATE / DROP for this :class:`.Sequence`,
4584 even if the :class:`.Sequence` is not associated with any
4585 :class:`_schema.Table` / :class:`_schema.Column`
4586 that's a member of this
4587 :class:`_schema.MetaData`.
4589 The above behaviors can only occur if the :class:`.Sequence` is
4590 explicitly associated with the :class:`_schema.MetaData`
4591 via this parameter.
4593 .. seealso::
4595 :ref:`sequence_metadata` - full discussion of the
4596 :paramref:`.Sequence.metadata` parameter.
4598 :param for_update: Indicates this :class:`.Sequence`, when associated
4599 with a :class:`_schema.Column`,
4600 should be invoked for UPDATE statements
4601 on that column's table, rather than for INSERT statements, when
4602 no value is otherwise present for that column in the statement.
4604 """
4605 DefaultGenerator.__init__(self, for_update=for_update)
4606 IdentityOptions.__init__(
4607 self,
4608 start=start,
4609 increment=increment,
4610 minvalue=minvalue,
4611 maxvalue=maxvalue,
4612 nominvalue=nominvalue,
4613 nomaxvalue=nomaxvalue,
4614 cycle=cycle,
4615 cache=cache,
4616 order=order,
4617 **dialect_kw,
4618 )
4619 self.column = None
4620 self.name = quoted_name(name, quote)
4621 self.optional = optional
4622 if schema is BLANK_SCHEMA:
4623 self.schema = schema = None
4624 elif metadata is not None and schema is None and metadata.schema:
4625 self.schema = schema = metadata.schema
4626 else:
4627 self.schema = quoted_name.construct(schema, quote_schema)
4628 self._key = _get_table_key(name, schema)
4629 if data_type is not None:
4630 self.data_type = to_instance(data_type)
4631 else:
4632 self.data_type = None
4634 if metadata:
4635 self._set_metadata(metadata)
4636 else:
4637 self.metadata = None
4639 @util.preload_module("sqlalchemy.sql.functions")
4640 def next_value(self) -> Function[int]:
4641 """Return a :class:`.next_value` function element
4642 which will render the appropriate increment function
4643 for this :class:`.Sequence` within any SQL expression.
4645 """
4646 return util.preloaded.sql_functions.func.next_value(self)
4648 def _copy(self) -> Sequence:
4649 return Sequence(
4650 name=self.name,
4651 schema=self.schema,
4652 data_type=self.data_type,
4653 optional=self.optional,
4654 metadata=None,
4655 for_update=self.for_update,
4656 **self._as_dict(),
4657 **self.dialect_kwargs,
4658 )
4660 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4661 assert isinstance(parent, Column)
4662 super()._set_parent(parent, **kw)
4663 parent._on_table_attach(self._set_table)
4665 def _set_table(self, column: Column[Any], table: Table) -> None:
4666 self._set_metadata(table.metadata)
4668 def _set_metadata(self, metadata: MetaData) -> None:
4669 self.metadata = metadata
4670 self.metadata._register_object(self)
4671 metadata._sequences[self._key] = self
4673 def create(
4674 self,
4675 bind: _CreateDropBind,
4676 checkfirst: Union[bool, CheckFirst] = CheckFirst.SEQUENCES,
4677 ) -> None:
4678 """Creates this sequence in the database."""
4680 bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
4682 def drop(
4683 self,
4684 bind: _CreateDropBind,
4685 checkfirst: Union[bool, CheckFirst] = CheckFirst.SEQUENCES,
4686 ) -> None:
4687 """Drops this sequence from the database."""
4689 bind._run_ddl_visitor(ddl.SchemaDropper, self, checkfirst=checkfirst)
4691 def _not_a_column_expr(self) -> NoReturn:
4692 raise exc.InvalidRequestError(
4693 f"This {self.__class__.__name__} cannot be used directly "
4694 "as a column expression. Use func.next_value(sequence) "
4695 "to produce a 'next value' function that's usable "
4696 "as a column element."
4697 )
4700@inspection._self_inspects
4701class FetchedValue(SchemaEventTarget):
4702 """A marker for a transparent database-side default.
4704 Use :class:`.FetchedValue` when the database is configured
4705 to provide some automatic default for a column.
4707 E.g.::
4709 Column("foo", Integer, FetchedValue())
4711 Would indicate that some trigger or default generator
4712 will create a new value for the ``foo`` column during an
4713 INSERT.
4715 .. seealso::
4717 :ref:`triggered_columns`
4719 """
4721 is_server_default = True
4722 reflected = False
4723 has_argument = False
4724 is_clause_element = False
4725 is_identity = False
4726 _is_monotonic_fn = False
4728 column: Optional[Column[Any]]
4730 def __init__(self, for_update: bool = False) -> None:
4731 self.for_update = for_update
4733 def _as_for_update(self, for_update: bool) -> FetchedValue:
4734 if for_update == self.for_update:
4735 return self
4736 else:
4737 return self._clone(for_update)
4739 def _copy(self) -> Self:
4740 return self._clone(self.for_update)
4742 def _clone(self, for_update: bool) -> Self:
4743 n = self.__class__.__new__(self.__class__)
4744 n.__dict__.update(self.__dict__)
4745 n.__dict__.pop("column", None)
4746 n.for_update = for_update
4747 return n
4749 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4750 column = parent
4751 assert isinstance(column, Column)
4752 self.column = column
4753 if self.for_update:
4754 self.column.server_onupdate = self
4755 else:
4756 self.column.server_default = self
4758 def __repr__(self) -> str:
4759 return util.generic_repr(self)
4762class DefaultClause(FetchedValue):
4763 """A DDL-specified DEFAULT column value.
4765 :class:`.DefaultClause` is a :class:`.FetchedValue`
4766 that also generates a "DEFAULT" clause when
4767 "CREATE TABLE" is emitted.
4769 :class:`.DefaultClause` is generated automatically
4770 whenever the ``server_default``, ``server_onupdate`` arguments of
4771 :class:`_schema.Column` are used. A :class:`.DefaultClause`
4772 can be passed positionally as well.
4774 For example, the following::
4776 Column("foo", Integer, server_default="50")
4778 Is equivalent to::
4780 Column("foo", Integer, DefaultClause("50"))
4782 """
4784 has_argument = True
4786 def __init__(
4787 self,
4788 arg: Union[str, ClauseElement, TextClause],
4789 for_update: bool = False,
4790 _reflected: bool = False,
4791 ) -> None:
4792 util.assert_arg_type(arg, (str, ClauseElement, TextClause), "arg")
4793 super().__init__(for_update)
4794 self.arg = arg
4795 self.reflected = _reflected
4797 @util.memoized_property
4798 @util.preload_module("sqlalchemy.sql.functions")
4799 def _is_monotonic_fn(self) -> bool:
4800 functions = util.preloaded.sql_functions
4801 return (
4802 isinstance(self.arg, functions.FunctionElement)
4803 and self.arg.monotonic
4804 )
4806 def __repr__(self) -> str:
4807 return "DefaultClause(%r, for_update=%r)" % (self.arg, self.for_update)
4810class Constraint(DialectKWArgs, HasConditionalDDL, SchemaItem):
4811 """A table-level SQL constraint.
4813 :class:`_schema.Constraint` serves as the base class for the series of
4814 constraint objects that can be associated with :class:`_schema.Table`
4815 objects, including :class:`_schema.PrimaryKeyConstraint`,
4816 :class:`_schema.ForeignKeyConstraint`
4817 :class:`_schema.UniqueConstraint`, and
4818 :class:`_schema.CheckConstraint`.
4820 """
4822 __visit_name__ = "constraint"
4824 _creation_order: int
4825 _column_flag: bool
4827 def __init__(
4828 self,
4829 name: _ConstraintNameArgument = None,
4830 deferrable: Optional[bool] = None,
4831 initially: Optional[str] = None,
4832 info: Optional[_InfoType] = None,
4833 comment: Optional[str] = None,
4834 _create_rule: Optional[Any] = None,
4835 _type_bound: bool = False,
4836 **dialect_kw: Any,
4837 ) -> None:
4838 r"""Create a SQL constraint.
4840 :param name:
4841 Optional, the in-database name of this ``Constraint``.
4843 :param deferrable:
4844 Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
4845 issuing DDL for this constraint.
4847 :param initially:
4848 Optional string. If set, emit INITIALLY <value> when issuing DDL
4849 for this constraint.
4851 :param info: Optional data dictionary which will be populated into the
4852 :attr:`.SchemaItem.info` attribute of this object.
4854 :param comment: Optional string that will render an SQL comment on
4855 foreign key constraint creation.
4857 .. versionadded:: 2.0
4859 :param \**dialect_kw: Additional keyword arguments are dialect
4860 specific, and passed in the form ``<dialectname>_<argname>``. See
4861 the documentation regarding an individual dialect at
4862 :ref:`dialect_toplevel` for detail on documented arguments.
4864 :param _create_rule:
4865 used internally by some datatypes that also create constraints.
4867 :param _type_bound:
4868 used internally to indicate that this constraint is associated with
4869 a specific datatype.
4871 """
4873 self.name = name
4874 self.deferrable = deferrable
4875 self.initially = initially
4876 if info:
4877 self.info = info
4878 self._create_rule = _create_rule
4879 self._type_bound = _type_bound
4880 util.set_creation_order(self)
4881 self._validate_dialect_kwargs(dialect_kw)
4882 self.comment = comment
4884 def _should_create_for_compiler(
4885 self, compiler: DDLCompiler, **kw: Any
4886 ) -> bool:
4887 if self._create_rule is not None and not self._create_rule(compiler):
4888 return False
4889 elif self._ddl_if is not None:
4890 return self._ddl_if._should_execute(
4891 ddl.CreateConstraint(self), self, None, compiler=compiler, **kw
4892 )
4893 else:
4894 return True
4896 @property
4897 def table(self) -> Table:
4898 try:
4899 if isinstance(self.parent, Table):
4900 return self.parent
4901 except AttributeError:
4902 pass
4903 raise exc.InvalidRequestError(
4904 "This constraint is not bound to a table. Did you "
4905 "mean to call table.append_constraint(constraint) ?"
4906 )
4908 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4909 assert isinstance(parent, (Table, Column))
4910 self.parent = parent
4911 parent.constraints.add(self)
4913 @util.deprecated(
4914 "1.4",
4915 "The :meth:`_schema.Constraint.copy` method is deprecated "
4916 "and will be removed in a future release.",
4917 )
4918 def copy(self, **kw: Any) -> Self:
4919 return self._copy(**kw)
4921 def _copy(self, **kw: Any) -> Self:
4922 raise NotImplementedError()
4925class ColumnCollectionMixin:
4926 """A :class:`_expression.ColumnCollection` of :class:`_schema.Column`
4927 objects.
4929 This collection represents the columns which are referred to by
4930 this object.
4932 """
4934 _columns: WriteableColumnCollection[str, Column[Any]]
4936 _column_collection_class: ClassVar[
4937 Type[WriteableColumnCollection[Any, Any]]
4938 ] = DedupeColumnCollection
4940 _allow_multiple_tables = False
4942 _pending_colargs: List[Optional[Union[str, Column[Any]]]]
4944 if TYPE_CHECKING:
4946 def _set_parent_with_dispatch(
4947 self, parent: SchemaEventTarget, **kw: Any
4948 ) -> None: ...
4950 def __init__(
4951 self,
4952 *columns: _DDLColumnArgument,
4953 _autoattach: bool = True,
4954 _column_flag: bool = False,
4955 _gather_expressions: Optional[
4956 List[Union[str, ColumnElement[Any]]]
4957 ] = None,
4958 ) -> None:
4959 self._column_flag = _column_flag
4960 self._columns = self._column_collection_class()
4962 processed_expressions: Optional[
4963 List[Union[ColumnElement[Any], str]]
4964 ] = _gather_expressions
4966 if processed_expressions is not None:
4968 # this is expected to be an empty list
4969 assert not processed_expressions
4971 self._pending_colargs = []
4972 for (
4973 expr,
4974 _,
4975 _,
4976 add_element,
4977 ) in coercions.expect_col_expression_collection(
4978 roles.DDLConstraintColumnRole, columns
4979 ):
4980 self._pending_colargs.append(add_element)
4981 processed_expressions.append(expr)
4982 else:
4983 self._pending_colargs = [
4984 coercions.expect(roles.DDLConstraintColumnRole, column)
4985 for column in columns
4986 ]
4988 if _autoattach and self._pending_colargs:
4989 self._check_attach()
4991 def _check_attach(self, evt: bool = False) -> None:
4992 col_objs = [c for c in self._pending_colargs if isinstance(c, Column)]
4994 cols_w_table = [c for c in col_objs if isinstance(c.table, Table)]
4996 cols_wo_table = set(col_objs).difference(cols_w_table)
4997 if cols_wo_table:
4998 # feature #3341 - place event listeners for Column objects
4999 # such that when all those cols are attached, we autoattach.
5000 assert not evt, "Should not reach here on event call"
5002 # issue #3411 - don't do the per-column auto-attach if some of the
5003 # columns are specified as strings.
5004 has_string_cols = {
5005 c for c in self._pending_colargs if c is not None
5006 }.difference(col_objs)
5007 if not has_string_cols:
5009 def _col_attached(column: Column[Any], table: Table) -> None:
5010 # this isinstance() corresponds with the
5011 # isinstance() above; only want to count Table-bound
5012 # columns
5013 if isinstance(table, Table):
5014 cols_wo_table.discard(column)
5015 if not cols_wo_table:
5016 self._check_attach(evt=True)
5018 self._cols_wo_table = cols_wo_table
5019 for col in cols_wo_table:
5020 col._on_table_attach(_col_attached)
5021 return
5023 columns = cols_w_table
5025 tables = {c.table for c in columns}
5026 if len(tables) == 1:
5027 self._set_parent_with_dispatch(tables.pop())
5028 elif len(tables) > 1 and not self._allow_multiple_tables:
5029 table = columns[0].table
5030 others = [c for c in columns[1:] if c.table is not table]
5031 if others:
5032 # black could not format this inline
5033 other_str = ", ".join("'%s'" % c for c in others)
5034 raise exc.ArgumentError(
5035 f"Column(s) {other_str} "
5036 f"are not part of table '{table.description}'."
5037 )
5039 @util.ro_memoized_property
5040 def columns(self) -> ReadOnlyColumnCollection[str, Column[Any]]:
5041 return self._columns.as_readonly()
5043 @util.ro_memoized_property
5044 def c(self) -> ReadOnlyColumnCollection[str, Column[Any]]:
5045 return self._columns.as_readonly()
5047 def _col_expressions(
5048 self, parent: Union[Table, Column[Any]]
5049 ) -> List[Optional[Column[Any]]]:
5050 if isinstance(parent, Column):
5051 result: List[Optional[Column[Any]]] = [
5052 c for c in self._pending_colargs if isinstance(c, Column)
5053 ]
5054 assert len(result) == len(self._pending_colargs)
5055 return result
5056 else:
5057 try:
5058 return [
5059 parent.c[col] if isinstance(col, str) else col
5060 for col in self._pending_colargs
5061 ]
5062 except KeyError as ke:
5063 raise exc.ConstraintColumnNotFoundError(
5064 f"Can't create {self.__class__.__name__} "
5065 f"on table '{parent.description}': no column "
5066 f"named '{ke.args[0]}' is present."
5067 ) from ke
5069 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
5070 assert isinstance(parent, (Table, Column))
5072 for col in self._col_expressions(parent):
5073 if col is not None:
5074 self._columns.add(col)
5077class ColumnCollectionConstraint(ColumnCollectionMixin, Constraint):
5078 """A constraint that proxies a ColumnCollection."""
5080 def __init__(
5081 self,
5082 *columns: _DDLColumnArgument,
5083 name: _ConstraintNameArgument = None,
5084 deferrable: Optional[bool] = None,
5085 initially: Optional[str] = None,
5086 info: Optional[_InfoType] = None,
5087 _autoattach: bool = True,
5088 _column_flag: bool = False,
5089 _gather_expressions: Optional[List[_DDLColumnArgument]] = None,
5090 **dialect_kw: Any,
5091 ) -> None:
5092 r"""
5093 :param \*columns:
5094 A sequence of column names or Column objects.
5096 :param name:
5097 Optional, the in-database name of this constraint.
5099 :param deferrable:
5100 Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
5101 issuing DDL for this constraint.
5103 :param initially:
5104 Optional string. If set, emit INITIALLY <value> when issuing DDL
5105 for this constraint.
5107 :param \**dialect_kw: other keyword arguments including
5108 dialect-specific arguments are propagated to the :class:`.Constraint`
5109 superclass.
5111 """
5112 Constraint.__init__(
5113 self,
5114 name=name,
5115 deferrable=deferrable,
5116 initially=initially,
5117 info=info,
5118 **dialect_kw,
5119 )
5120 ColumnCollectionMixin.__init__(
5121 self, *columns, _autoattach=_autoattach, _column_flag=_column_flag
5122 )
5124 columns: ReadOnlyColumnCollection[str, Column[Any]]
5125 """A :class:`_expression.ColumnCollection` representing the set of columns
5126 for this constraint.
5128 """
5130 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
5131 assert isinstance(parent, (Column, Table))
5132 Constraint._set_parent(self, parent)
5133 ColumnCollectionMixin._set_parent(self, parent)
5135 def __contains__(self, x: Any) -> bool:
5136 return x in self._columns
5138 @util.deprecated(
5139 "1.4",
5140 "The :meth:`_schema.ColumnCollectionConstraint.copy` method "
5141 "is deprecated and will be removed in a future release.",
5142 )
5143 def copy(
5144 self,
5145 *,
5146 target_table: Optional[Table] = None,
5147 **kw: Any,
5148 ) -> ColumnCollectionConstraint:
5149 return self._copy(target_table=target_table, **kw)
5151 def _copy(
5152 self,
5153 *,
5154 target_table: Optional[Table] = None,
5155 **kw: Any,
5156 ) -> ColumnCollectionConstraint:
5157 # ticket #5276
5158 constraint_kwargs = {}
5159 for dialect_name in self.dialect_options:
5160 dialect_options = self.dialect_options[dialect_name]._non_defaults
5161 for (
5162 dialect_option_key,
5163 dialect_option_value,
5164 ) in dialect_options.items():
5165 constraint_kwargs[dialect_name + "_" + dialect_option_key] = (
5166 dialect_option_value
5167 )
5169 assert isinstance(self.parent, Table)
5170 c = self.__class__(
5171 name=self.name,
5172 deferrable=self.deferrable,
5173 initially=self.initially,
5174 *[
5175 _copy_expression(expr, self.parent, target_table)
5176 for expr in self._columns
5177 ],
5178 comment=self.comment,
5179 **constraint_kwargs,
5180 )
5181 return self._schema_item_copy(c)
5183 def contains_column(self, col: Column[Any]) -> bool:
5184 """Return True if this constraint contains the given column.
5186 Note that this object also contains an attribute ``.columns``
5187 which is a :class:`_expression.ColumnCollection` of
5188 :class:`_schema.Column` objects.
5190 """
5192 return self._columns.contains_column(col)
5194 def __iter__(self) -> Iterator[Column[Any]]:
5195 return iter(self._columns)
5197 def __len__(self) -> int:
5198 return len(self._columns)
5201class CheckConstraint(ColumnCollectionConstraint):
5202 """A table- or column-level CHECK constraint.
5204 Can be included in the definition of a Table or Column.
5205 """
5207 _allow_multiple_tables = True
5209 __visit_name__ = "table_or_column_check_constraint"
5211 @_document_text_coercion(
5212 "sqltext",
5213 ":class:`.CheckConstraint`",
5214 ":paramref:`.CheckConstraint.sqltext`",
5215 )
5216 def __init__(
5217 self,
5218 sqltext: _TextCoercedExpressionArgument[Any],
5219 name: _ConstraintNameArgument = None,
5220 deferrable: Optional[bool] = None,
5221 initially: Optional[str] = None,
5222 table: Optional[Table] = None,
5223 info: Optional[_InfoType] = None,
5224 _create_rule: Optional[Any] = None,
5225 _autoattach: bool = True,
5226 _type_bound: bool = False,
5227 **dialect_kw: Any,
5228 ) -> None:
5229 r"""Construct a CHECK constraint.
5231 :param sqltext:
5232 A string containing the constraint definition, which will be used
5233 verbatim, or a SQL expression construct. If given as a string,
5234 the object is converted to a :func:`_expression.text` object.
5235 If the textual
5236 string includes a colon character, escape this using a backslash::
5238 CheckConstraint(r"foo ~ E'a(?\:b|c)d")
5240 :param name:
5241 Optional, the in-database name of the constraint.
5243 :param deferrable:
5244 Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
5245 issuing DDL for this constraint.
5247 :param initially:
5248 Optional string. If set, emit INITIALLY <value> when issuing DDL
5249 for this constraint.
5251 :param info: Optional data dictionary which will be populated into the
5252 :attr:`.SchemaItem.info` attribute of this object.
5254 """
5256 self.sqltext = coercions.expect(roles.DDLExpressionRole, sqltext)
5257 columns: List[Column[Any]] = []
5258 visitors.traverse(self.sqltext, {}, {"column": columns.append})
5260 super().__init__(
5261 name=name,
5262 deferrable=deferrable,
5263 initially=initially,
5264 _create_rule=_create_rule,
5265 info=info,
5266 _type_bound=_type_bound,
5267 _autoattach=_autoattach,
5268 *columns,
5269 **dialect_kw,
5270 )
5271 if table is not None:
5272 self._set_parent_with_dispatch(table)
5274 @property
5275 def is_column_level(self) -> bool:
5276 return not isinstance(self.parent, Table)
5278 @util.deprecated(
5279 "1.4",
5280 "The :meth:`_schema.CheckConstraint.copy` method is deprecated "
5281 "and will be removed in a future release.",
5282 )
5283 def copy(
5284 self, *, target_table: Optional[Table] = None, **kw: Any
5285 ) -> CheckConstraint:
5286 return self._copy(target_table=target_table, **kw)
5288 def _copy(
5289 self, *, target_table: Optional[Table] = None, **kw: Any
5290 ) -> CheckConstraint:
5291 if target_table is not None:
5292 # note that target_table is None for the copy process of
5293 # a column-bound CheckConstraint, so this path is not reached
5294 # in that case.
5295 sqltext = _copy_expression(self.sqltext, self.table, target_table)
5296 else:
5297 sqltext = self.sqltext
5298 c = CheckConstraint(
5299 sqltext,
5300 name=self.name,
5301 initially=self.initially,
5302 deferrable=self.deferrable,
5303 _create_rule=self._create_rule,
5304 table=target_table,
5305 comment=self.comment,
5306 _autoattach=False,
5307 _type_bound=self._type_bound,
5308 )
5309 return self._schema_item_copy(c)
5312class ForeignKeyConstraint(ColumnCollectionConstraint):
5313 """A table-level FOREIGN KEY constraint.
5315 Defines a single column or composite FOREIGN KEY ... REFERENCES
5316 constraint. For a no-frills, single column foreign key, adding a
5317 :class:`_schema.ForeignKey` to the definition of a :class:`_schema.Column`
5318 is a
5319 shorthand equivalent for an unnamed, single column
5320 :class:`_schema.ForeignKeyConstraint`.
5322 Examples of foreign key configuration are in :ref:`metadata_foreignkeys`.
5324 """
5326 __visit_name__ = "foreign_key_constraint"
5328 # a FOREIGN KEY may name the same local column more than once, e.g.
5329 # FOREIGN KEY (a, a) REFERENCES r (b, c). the collection is therefore
5330 # positional and parallel to self.elements, not deduplicating.
5331 _column_collection_class = WriteableColumnCollection
5333 def __init__(
5334 self,
5335 columns: _typing_Sequence[_DDLColumnArgument],
5336 refcolumns: _typing_Sequence[_DDLColumnReferenceArgument],
5337 name: _ConstraintNameArgument = None,
5338 onupdate: Optional[str] = None,
5339 ondelete: Optional[str] = None,
5340 deferrable: Optional[bool] = None,
5341 initially: Optional[str] = None,
5342 use_alter: bool = False,
5343 link_to_name: bool = False,
5344 match: Optional[str] = None,
5345 table: Optional[Table] = None,
5346 info: Optional[_InfoType] = None,
5347 comment: Optional[str] = None,
5348 **dialect_kw: Any,
5349 ) -> None:
5350 r"""Construct a composite-capable FOREIGN KEY.
5352 :param columns: A sequence of local column names. The named columns
5353 must be defined and present in the parent Table. The names should
5354 match the ``key`` given to each column (defaults to the name) unless
5355 ``link_to_name`` is True. The same column may be named more than
5356 once, e.g. ``FOREIGN KEY (a, a) REFERENCES r (b, c)``, which
5357 constrains the referenced row so that its ``b`` and ``c`` values
5358 are equal.
5360 .. versionchanged:: 2.1 The same local column may be named more
5361 than once.
5363 :param refcolumns: A sequence of foreign column names or Column
5364 objects. The columns must all be located within the same Table.
5365 The number of entries must match that of
5366 :paramref:`_schema.ForeignKeyConstraint.columns`. Each entry
5367 accepts the same forms as :paramref:`_schema.ForeignKey.column`,
5368 including the ``(table_name, column_name)`` and
5369 ``(schema, table_name, column_name)`` tuple forms.
5371 .. versionchanged:: 2.1 Individual entries may be given as a
5372 tuple of name tokens.
5374 :param name: Optional, the in-database name of the key.
5376 :param onupdate: Optional string. If set, emit ON UPDATE <value> when
5377 issuing DDL for this constraint. Typical values include CASCADE,
5378 DELETE and RESTRICT.
5380 .. seealso::
5382 :ref:`on_update_on_delete`
5384 :param ondelete: Optional string. If set, emit ON DELETE <value> when
5385 issuing DDL for this constraint. Typical values include CASCADE,
5386 SET NULL and RESTRICT. Some dialects may allow for additional
5387 syntaxes.
5389 .. seealso::
5391 :ref:`on_update_on_delete`
5393 :param deferrable: Optional bool. If set, emit DEFERRABLE or NOT
5394 DEFERRABLE when issuing DDL for this constraint.
5396 :param initially: Optional string. If set, emit INITIALLY <value> when
5397 issuing DDL for this constraint.
5399 :param link_to_name: if True, the string name given in ``column`` is
5400 the rendered name of the referenced column, not its locally assigned
5401 ``key``.
5403 :param use_alter: If True, do not emit the DDL for this constraint as
5404 part of the CREATE TABLE definition. Instead, generate it via an
5405 ALTER TABLE statement issued after the full collection of tables
5406 have been created, and drop it via an ALTER TABLE statement before
5407 the full collection of tables are dropped.
5409 The use of :paramref:`_schema.ForeignKeyConstraint.use_alter` is
5410 particularly geared towards the case where two or more tables
5411 are established within a mutually-dependent foreign key constraint
5412 relationship; however, the :meth:`_schema.MetaData.create_all` and
5413 :meth:`_schema.MetaData.drop_all`
5414 methods will perform this resolution
5415 automatically, so the flag is normally not needed.
5417 .. seealso::
5419 :ref:`use_alter`
5421 :param match: Optional string. If set, emit MATCH <value> when issuing
5422 DDL for this constraint. Typical values include SIMPLE, PARTIAL
5423 and FULL.
5425 :param info: Optional data dictionary which will be populated into the
5426 :attr:`.SchemaItem.info` attribute of this object.
5428 :param comment: Optional string that will render an SQL comment on
5429 foreign key constraint creation.
5431 .. versionadded:: 2.0
5433 :param \**dialect_kw: Additional keyword arguments are dialect
5434 specific, and passed in the form ``<dialectname>_<argname>``. See
5435 the documentation regarding an individual dialect at
5436 :ref:`dialect_toplevel` for detail on documented arguments.
5438 """
5440 Constraint.__init__(
5441 self,
5442 name=name,
5443 deferrable=deferrable,
5444 initially=initially,
5445 info=info,
5446 comment=comment,
5447 **dialect_kw,
5448 )
5449 self.onupdate = onupdate
5450 self.ondelete = ondelete
5451 self.link_to_name = link_to_name
5452 self.use_alter = use_alter
5453 self.match = match
5455 if len(columns) != len(refcolumns):
5456 # e.g. FOREIGN KEY (a) REFERENCES r (b, c)
5457 # paraphrasing
5458 # https://www.postgresql.org/docs/current/static/ddl-constraints.html
5459 raise exc.ArgumentError(
5460 "ForeignKeyConstraint number "
5461 "of constrained columns must match the number of "
5462 "referenced columns."
5463 )
5465 # standalone ForeignKeyConstraint - create
5466 # associated ForeignKey objects which will be applied to hosted
5467 # Column objects (in col.foreign_keys), either now or when attached
5468 # to the Table for string-specified names
5469 self.elements = [
5470 ForeignKey(
5471 refcol,
5472 _constraint=self,
5473 name=self.name,
5474 onupdate=self.onupdate,
5475 ondelete=self.ondelete,
5476 use_alter=self.use_alter,
5477 link_to_name=self.link_to_name,
5478 match=self.match,
5479 deferrable=self.deferrable,
5480 initially=self.initially,
5481 **self.dialect_kwargs,
5482 )
5483 for refcol in refcolumns
5484 ]
5486 ColumnCollectionMixin.__init__(self, *columns)
5487 if table is not None:
5488 if hasattr(self, "parent"):
5489 assert table is self.parent
5490 self._set_parent_with_dispatch(table)
5492 def _append_element(self, column: Column[Any], fk: ForeignKey) -> None:
5493 self._columns.add(column)
5494 self.elements.append(fk)
5496 columns: ReadOnlyColumnCollection[str, Column[Any]]
5497 """A :class:`_expression.ColumnCollection` representing the local columns
5498 of this constraint, in the order given and parallel to
5499 :attr:`.ForeignKeyConstraint.elements`.
5501 Unlike the column collection of other constraint types, this collection
5502 does not deduplicate; a constraint which names the same column more than
5503 once, such as ``FOREIGN KEY (a, a) REFERENCES r (b, c)``, includes that
5504 column once per position.
5506 .. versionchanged:: 2.1 A :class:`_schema.ForeignKeyConstraint` may name
5507 the same local column more than once, and this collection retains the
5508 repeated entries.
5510 """
5512 elements: List[ForeignKey]
5513 """A sequence of :class:`_schema.ForeignKey` objects.
5515 Each :class:`_schema.ForeignKey`
5516 represents a single referring column/referred
5517 column pair.
5519 This collection is intended to be read-only.
5521 """
5523 @property
5524 def _referred_schema(self) -> Optional[str]:
5525 for elem in self.elements:
5526 return elem._referred_schema
5527 else:
5528 return None
5530 @property
5531 def referred_table(self) -> Table:
5532 """The :class:`_schema.Table` object to which this
5533 :class:`_schema.ForeignKeyConstraint` references.
5535 This is a dynamically calculated attribute which may not be available
5536 if the constraint and/or parent table is not yet associated with
5537 a metadata collection that contains the referred table.
5539 """
5540 return self.elements[0].column.table
5542 def _validate_dest_table(self, table: Table) -> None:
5543 table_keys = {elem.target_table_key for elem in self.elements}
5544 if None not in table_keys and len(table_keys) > 1:
5545 elem0, elem1 = sorted(cast("Set[str]", table_keys))[0:2]
5546 raise exc.ArgumentError(
5547 f"ForeignKeyConstraint on "
5548 f"{table.fullname}({self._col_description}) refers to "
5549 f"multiple remote tables: {elem0} and {elem1}"
5550 )
5552 @property
5553 def column_keys(self) -> _typing_Sequence[str]:
5554 """Return a list of string keys representing the local
5555 columns in this :class:`_schema.ForeignKeyConstraint`.
5557 This list is either the original string arguments sent
5558 to the constructor of the :class:`_schema.ForeignKeyConstraint`,
5559 or if the constraint has been initialized with :class:`_schema.Column`
5560 objects, is the string ``.key`` of each element.
5562 """
5563 if hasattr(self, "parent"):
5564 return self._columns.keys()
5565 else:
5566 return [
5567 col.key if isinstance(col, ColumnElement) else str(col)
5568 for col in self._pending_colargs
5569 ]
5571 @property
5572 def _col_description(self) -> str:
5573 return ", ".join(self.column_keys)
5575 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
5576 table = parent
5577 assert isinstance(table, Table)
5578 Constraint._set_parent(self, table)
5580 if self._pending_colargs:
5581 # this collection is positional and parallel to self.elements,
5582 # retaining duplicate entries for a constraint such as
5583 # FOREIGN KEY (a, a) REFERENCES r (b, c). _set_parent may run
5584 # more than once for the same table, e.g. for an inline-declared
5585 # constraint that also auto-attaches when its Column objects
5586 # are attached, so populate rather than accumulate.
5587 self._columns._populate_separate_keys(
5588 (col.key, col)
5589 for col in self._col_expressions(table)
5590 if col is not None
5591 )
5593 for col, fk in zip(self._columns, self.elements):
5594 if not hasattr(fk, "parent") or fk.parent is not col:
5595 fk._set_parent_with_dispatch(col)
5597 self._validate_dest_table(table)
5599 @util.deprecated(
5600 "1.4",
5601 "The :meth:`_schema.ForeignKeyConstraint.copy` method is deprecated "
5602 "and will be removed in a future release.",
5603 )
5604 def copy(
5605 self,
5606 *,
5607 schema: Optional[str] = None,
5608 target_table: Optional[Table] = None,
5609 **kw: Any,
5610 ) -> ForeignKeyConstraint:
5611 return self._copy(schema=schema, target_table=target_table, **kw)
5613 def _copy(
5614 self,
5615 *,
5616 schema: Optional[str] = None,
5617 target_table: Optional[Table] = None,
5618 **kw: Any,
5619 ) -> ForeignKeyConstraint:
5620 fkc = ForeignKeyConstraint(
5621 [x.parent.key for x in self.elements],
5622 [
5623 x._copy_tokens(
5624 schema=schema,
5625 table_name=(
5626 target_table.name
5627 if target_table is not None
5628 and x.target_table_key == x.parent.table.key
5629 else None
5630 ),
5631 _is_copy=True,
5632 )
5633 for x in self.elements
5634 ],
5635 name=self.name,
5636 onupdate=self.onupdate,
5637 ondelete=self.ondelete,
5638 use_alter=self.use_alter,
5639 deferrable=self.deferrable,
5640 initially=self.initially,
5641 link_to_name=self.link_to_name,
5642 match=self.match,
5643 comment=self.comment,
5644 )
5645 for self_fk, other_fk in zip(self.elements, fkc.elements):
5646 self_fk._schema_item_copy(other_fk)
5647 return self._schema_item_copy(fkc)
5650class PrimaryKeyConstraint(ColumnCollectionConstraint):
5651 """A table-level PRIMARY KEY constraint.
5653 The :class:`.PrimaryKeyConstraint` object is present automatically
5654 on any :class:`_schema.Table` object; it is assigned a set of
5655 :class:`_schema.Column` objects corresponding to those marked with
5656 the :paramref:`_schema.Column.primary_key` flag::
5658 >>> my_table = Table(
5659 ... "mytable",
5660 ... metadata,
5661 ... Column("id", Integer, primary_key=True),
5662 ... Column("version_id", Integer, primary_key=True),
5663 ... Column("data", String(50)),
5664 ... )
5665 >>> my_table.primary_key
5666 PrimaryKeyConstraint(
5667 Column('id', Integer(), table=<mytable>,
5668 primary_key=True, nullable=False),
5669 Column('version_id', Integer(), table=<mytable>,
5670 primary_key=True, nullable=False)
5671 )
5673 The primary key of a :class:`_schema.Table` can also be specified by using
5674 a :class:`.PrimaryKeyConstraint` object explicitly; in this mode of usage,
5675 the "name" of the constraint can also be specified, as well as other
5676 options which may be recognized by dialects::
5678 my_table = Table(
5679 "mytable",
5680 metadata,
5681 Column("id", Integer),
5682 Column("version_id", Integer),
5683 Column("data", String(50)),
5684 PrimaryKeyConstraint("id", "version_id", name="mytable_pk"),
5685 )
5687 The two styles of column-specification should generally not be mixed.
5688 An warning is emitted if the columns present in the
5689 :class:`.PrimaryKeyConstraint`
5690 don't match the columns that were marked as ``primary_key=True``, if both
5691 are present; in this case, the columns are taken strictly from the
5692 :class:`.PrimaryKeyConstraint` declaration, and those columns otherwise
5693 marked as ``primary_key=True`` are ignored. This behavior is intended to
5694 be backwards compatible with previous behavior.
5696 For the use case where specific options are to be specified on the
5697 :class:`.PrimaryKeyConstraint`, but the usual style of using
5698 ``primary_key=True`` flags is still desirable, an empty
5699 :class:`.PrimaryKeyConstraint` may be specified, which will take on the
5700 primary key column collection from the :class:`_schema.Table` based on the
5701 flags::
5703 my_table = Table(
5704 "mytable",
5705 metadata,
5706 Column("id", Integer, primary_key=True),
5707 Column("version_id", Integer, primary_key=True),
5708 Column("data", String(50)),
5709 PrimaryKeyConstraint(name="mytable_pk", mssql_clustered=True),
5710 )
5712 """
5714 __visit_name__ = "primary_key_constraint"
5716 _columns: DedupeColumnCollection[Column[Any]]
5718 def __init__(
5719 self,
5720 *columns: _DDLColumnArgument,
5721 name: Optional[str] = None,
5722 deferrable: Optional[bool] = None,
5723 initially: Optional[str] = None,
5724 info: Optional[_InfoType] = None,
5725 _implicit_generated: bool = False,
5726 **dialect_kw: Any,
5727 ) -> None:
5728 self._implicit_generated = _implicit_generated
5729 super().__init__(
5730 *columns,
5731 name=name,
5732 deferrable=deferrable,
5733 initially=initially,
5734 info=info,
5735 **dialect_kw,
5736 )
5738 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
5739 table = parent
5740 assert isinstance(table, Table)
5741 super()._set_parent(table)
5743 if table.primary_key is not self:
5744 table.constraints.discard(table.primary_key)
5745 table.primary_key = self # type: ignore[misc]
5746 table.constraints.add(self)
5748 table_pks = [c for c in table.c if c.primary_key]
5749 if (
5750 self._columns
5751 and table_pks
5752 and set(table_pks) != set(self._columns)
5753 ):
5754 # black could not format these inline
5755 table_pk_str = ", ".join("'%s'" % c.name for c in table_pks)
5756 col_str = ", ".join("'%s'" % c.name for c in self._columns)
5758 util.warn(
5759 f"Table '{table.name}' specifies columns "
5760 f"{table_pk_str} as "
5761 f"primary_key=True, "
5762 f"not matching locally specified columns {col_str}; "
5763 f"setting the "
5764 f"current primary key columns to "
5765 f"{col_str}. "
5766 f"This warning "
5767 f"may become an exception in a future release"
5768 )
5769 table_pks[:] = []
5771 for c in self._columns:
5772 c.primary_key = True
5773 if c._user_defined_nullable is NULL_UNSPECIFIED:
5774 c.nullable = False
5775 if table_pks:
5776 self._columns.extend(table_pks)
5778 def _reload(self, columns: Iterable[Column[Any]]) -> None:
5779 """repopulate this :class:`.PrimaryKeyConstraint` given
5780 a set of columns.
5782 Existing columns in the table that are marked as primary_key=True
5783 are maintained.
5785 Also fires a new event.
5787 This is basically like putting a whole new
5788 :class:`.PrimaryKeyConstraint` object on the parent
5789 :class:`_schema.Table` object without actually replacing the object.
5791 The ordering of the given list of columns is also maintained; these
5792 columns will be appended to the list of columns after any which
5793 are already present.
5795 """
5796 # set the primary key flag on new columns.
5797 # note any existing PK cols on the table also have their
5798 # flag still set.
5799 for col in columns:
5800 col.primary_key = True
5802 self._columns.extend(columns)
5804 PrimaryKeyConstraint._autoincrement_column._reset(self) # type: ignore[attr-defined] # noqa: E501
5805 self._set_parent_with_dispatch(self.table)
5807 def _replace(self, col: Column[Any]) -> None:
5808 PrimaryKeyConstraint._autoincrement_column._reset(self) # type: ignore[attr-defined] # noqa: E501
5809 self._columns.replace(col)
5811 self.dispatch._sa_event_column_added_to_pk_constraint(self, col)
5813 @property
5814 def columns_autoinc_first(self) -> List[Column[Any]]:
5815 autoinc = self._autoincrement_column
5817 if autoinc is not None:
5818 return [autoinc] + [c for c in self._columns if c is not autoinc]
5819 else:
5820 return list(self._columns)
5822 @util.ro_memoized_property
5823 def _autoincrement_column(self) -> Optional[Column[int]]:
5824 def _validate_autoinc(col: Column[Any], autoinc_true: bool) -> bool:
5825 if col.type._type_affinity is not None and issubclass(
5826 col.type._type_affinity, type_api.NUMERICTYPE._type_affinity
5827 ):
5828 scale = col.type.scale # type: ignore[attr-defined]
5829 if scale != 0 and autoinc_true:
5830 raise exc.ArgumentError(
5831 f"Column type {col.type} with non-zero scale "
5832 f"{scale} on column '{col}' is not "
5833 f"compatible with autoincrement=True"
5834 )
5835 elif not autoinc_true:
5836 return False
5837 elif col.type._type_affinity is None or not issubclass(
5838 col.type._type_affinity, type_api.INTEGERTYPE._type_affinity
5839 ):
5840 if autoinc_true:
5841 raise exc.ArgumentError(
5842 f"Column type {col.type} on column '{col}' is not "
5843 f"compatible with autoincrement=True"
5844 )
5845 else:
5846 return False
5847 elif (
5848 col.default is not None
5849 and not isinstance(col.default, Sequence)
5850 and not autoinc_true
5851 ):
5852 return False
5853 elif (
5854 col.server_default is not None
5855 and not isinstance(col.server_default, Identity)
5856 and not autoinc_true
5857 ):
5858 return False
5859 elif col.foreign_keys and col.autoincrement not in (
5860 True,
5861 "ignore_fk",
5862 ):
5863 return False
5864 return True
5866 if len(self._columns) == 1:
5867 col = list(self._columns)[0]
5869 if col.autoincrement is True:
5870 _validate_autoinc(col, True)
5871 return col
5872 elif col.autoincrement in (
5873 "auto",
5874 "ignore_fk",
5875 ) and _validate_autoinc(col, False):
5876 return col
5877 else:
5878 return None
5880 else:
5881 autoinc = None
5882 for col in self._columns:
5883 if col.autoincrement is True:
5884 _validate_autoinc(col, True)
5885 if autoinc is not None:
5886 raise exc.ArgumentError(
5887 f"Only one Column may be marked "
5888 f"autoincrement=True, found both "
5889 f"{col.name} and {autoinc.name}."
5890 )
5891 else:
5892 autoinc = col
5894 return autoinc
5897class UniqueConstraint(ColumnCollectionConstraint):
5898 """A table-level UNIQUE constraint.
5900 Defines a single column or composite UNIQUE constraint. For a no-frills,
5901 single column constraint, adding ``unique=True`` to the ``Column``
5902 definition is a shorthand equivalent for an unnamed, single column
5903 UniqueConstraint.
5904 """
5906 __visit_name__ = "unique_constraint"
5909class Index(
5910 DialectKWArgs, ColumnCollectionMixin, HasConditionalDDL, SchemaItem
5911):
5912 """A table-level INDEX.
5914 Defines a composite (one or more column) INDEX.
5916 E.g.::
5918 sometable = Table(
5919 "sometable",
5920 metadata,
5921 Column("name", String(50)),
5922 Column("address", String(100)),
5923 )
5925 Index("some_index", sometable.c.name)
5927 For a no-frills, single column index, adding
5928 :class:`_schema.Column` also supports ``index=True``::
5930 sometable = Table(
5931 "sometable", metadata, Column("name", String(50), index=True)
5932 )
5934 For a composite index, multiple columns can be specified::
5936 Index("some_index", sometable.c.name, sometable.c.address)
5938 Functional indexes are supported as well, typically by using the
5939 :data:`.func` construct in conjunction with table-bound
5940 :class:`_schema.Column` objects::
5942 Index("some_index", func.lower(sometable.c.name))
5944 An :class:`.Index` can also be manually associated with a
5945 :class:`_schema.Table`,
5946 either through inline declaration or using
5947 :meth:`_schema.Table.append_constraint`. When this approach is used,
5948 the names
5949 of the indexed columns can be specified as strings::
5951 Table(
5952 "sometable",
5953 metadata,
5954 Column("name", String(50)),
5955 Column("address", String(100)),
5956 Index("some_index", "name", "address"),
5957 )
5959 To support functional or expression-based indexes in this form, the
5960 :func:`_expression.text` construct may be used::
5962 from sqlalchemy import text
5964 Table(
5965 "sometable",
5966 metadata,
5967 Column("name", String(50)),
5968 Column("address", String(100)),
5969 Index("some_index", text("lower(name)")),
5970 )
5972 .. seealso::
5974 :ref:`schema_indexes` - General information on :class:`.Index`.
5976 :ref:`postgresql_indexes` - PostgreSQL-specific options available for
5977 the :class:`.Index` construct.
5979 :ref:`mysql_indexes` - MySQL-specific options available for the
5980 :class:`.Index` construct.
5982 :ref:`mssql_indexes` - MSSQL-specific options available for the
5983 :class:`.Index` construct.
5985 """
5987 __visit_name__ = "index"
5989 table: Optional[Table]
5990 expressions: _typing_Sequence[Union[str, ColumnElement[Any]]]
5991 _table_bound_expressions: _typing_Sequence[ColumnElement[Any]]
5993 def __init__(
5994 self,
5995 name: Optional[str],
5996 *expressions: _DDLColumnArgument,
5997 unique: bool = False,
5998 quote: Optional[bool] = None,
5999 info: Optional[_InfoType] = None,
6000 _table: Optional[Table] = None,
6001 _column_flag: bool = False,
6002 **dialect_kw: Any,
6003 ) -> None:
6004 r"""Construct an index object.
6006 :param name:
6007 The name of the index
6009 :param \*expressions:
6010 Column expressions to include in the index. The expressions
6011 are normally instances of :class:`_schema.Column`, but may also
6012 be arbitrary SQL expressions which ultimately refer to a
6013 :class:`_schema.Column`.
6015 :param unique=False:
6016 Keyword only argument; if True, create a unique index.
6018 :param quote=None:
6019 Keyword only argument; whether to apply quoting to the name of
6020 the index. Works in the same manner as that of
6021 :paramref:`_schema.Column.quote`.
6023 :param info=None: Optional data dictionary which will be populated
6024 into the :attr:`.SchemaItem.info` attribute of this object.
6026 :param \**dialect_kw: Additional keyword arguments not mentioned above
6027 are dialect specific, and passed in the form
6028 ``<dialectname>_<argname>``. See the documentation regarding an
6029 individual dialect at :ref:`dialect_toplevel` for detail on
6030 documented arguments.
6032 """
6033 self.table = table = None
6035 self.name = quoted_name.construct(name, quote)
6036 self.unique = unique
6037 if info is not None:
6038 self.info = info
6040 # TODO: consider "table" argument being public, but for
6041 # the purpose of the fix here, it starts as private.
6042 if _table is not None:
6043 table = _table
6045 self._validate_dialect_kwargs(dialect_kw)
6047 self.expressions = []
6048 # will call _set_parent() if table-bound column
6049 # objects are present
6050 ColumnCollectionMixin.__init__(
6051 self,
6052 *expressions,
6053 _column_flag=_column_flag,
6054 _gather_expressions=self.expressions,
6055 )
6056 if table is not None:
6057 self._set_parent(table)
6059 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
6060 table = parent
6061 assert isinstance(table, Table)
6062 ColumnCollectionMixin._set_parent(self, table)
6064 if self.table is not None and table is not self.table:
6065 raise exc.ArgumentError(
6066 f"Index '{self.name}' is against table "
6067 f"'{self.table.description}', and "
6068 f"cannot be associated with table '{table.description}'."
6069 )
6070 self.table = table
6071 table.indexes.add(self)
6073 expressions = self.expressions
6074 col_expressions = self._col_expressions(table)
6075 assert len(expressions) == len(col_expressions)
6077 exprs = []
6078 for expr, colexpr in zip(expressions, col_expressions):
6079 if isinstance(expr, ClauseElement):
6080 exprs.append(expr)
6081 elif colexpr is not None:
6082 exprs.append(colexpr)
6083 else:
6084 assert False
6085 self.expressions = self._table_bound_expressions = exprs
6087 def create(
6088 self,
6089 bind: _CreateDropBind,
6090 checkfirst: Union[bool, CheckFirst] = CheckFirst.NONE,
6091 ) -> None:
6092 """Issue a ``CREATE`` statement for this
6093 :class:`.Index`, using the given
6094 :class:`.Connection` or :class:`.Engine`` for connectivity.
6096 .. seealso::
6098 :meth:`_schema.MetaData.create_all`.
6100 """
6101 bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
6103 def drop(
6104 self,
6105 bind: _CreateDropBind,
6106 checkfirst: Union[bool, CheckFirst] = CheckFirst.NONE,
6107 ) -> None:
6108 """Issue a ``DROP`` statement for this
6109 :class:`.Index`, using the given
6110 :class:`.Connection` or :class:`.Engine` for connectivity.
6112 .. seealso::
6114 :meth:`_schema.MetaData.drop_all`.
6116 """
6117 bind._run_ddl_visitor(ddl.SchemaDropper, self, checkfirst=checkfirst)
6119 def __repr__(self) -> str:
6120 exprs: _typing_Sequence[Any] # noqa: F842
6122 return "Index(%s)" % (
6123 ", ".join(
6124 [repr(self.name)]
6125 + [repr(e) for e in self.expressions]
6126 + (self.unique and ["unique=True"] or [])
6127 )
6128 )
6131_NamingSchemaCallable = Union[
6132 Callable[[Constraint, Table], str],
6133 Callable[[Index, Table], str],
6134]
6135_NamingSchemaDirective = Union[str, _NamingSchemaCallable]
6138class _NamingSchemaTD(TypedDict, total=False):
6139 fk: _NamingSchemaDirective
6140 pk: _NamingSchemaDirective
6141 ix: _NamingSchemaDirective
6142 ck: _NamingSchemaDirective
6143 uq: _NamingSchemaDirective
6146_NamingSchemaParameter = Union[
6147 # it seems like the TypedDict here is useful for pylance typeahead,
6148 # and not much else
6149 _NamingSchemaTD,
6150 # there is no form that allows Union[Type[Any], str] to work in all
6151 # cases, including breaking out Mapping[] entries for each combination
6152 # even, therefore keys must be `Any` (see #10264)
6153 Mapping[Any, _NamingSchemaDirective],
6154]
6157DEFAULT_NAMING_CONVENTION: _NamingSchemaParameter = util.immutabledict(
6158 {"ix": "ix_%(column_0_label)s"}
6159)
6162class MetaData(HasSchemaAttr):
6163 """A collection of :class:`_schema.Table`
6164 objects and their associated schema
6165 constructs.
6167 Holds a collection of :class:`_schema.Table` objects as well as
6168 an optional binding to an :class:`_engine.Engine` or
6169 :class:`_engine.Connection`. If bound, the :class:`_schema.Table` objects
6170 in the collection and their columns may participate in implicit SQL
6171 execution.
6173 The :class:`_schema.Table` objects themselves are stored in the
6174 :attr:`_schema.MetaData.tables` dictionary.
6176 :class:`_schema.MetaData` is a thread-safe object for read operations.
6177 Construction of new tables within a single :class:`_schema.MetaData`
6178 object,
6179 either explicitly or via reflection, may not be completely thread-safe.
6181 .. seealso::
6183 :ref:`metadata_describing` - Introduction to database metadata
6185 """
6187 __visit_name__ = "metadata"
6189 def __init__(
6190 self,
6191 schema: Optional[str] = None,
6192 quote_schema: Optional[bool] = None,
6193 naming_convention: Optional[_NamingSchemaParameter] = None,
6194 info: Optional[_InfoType] = None,
6195 ) -> None:
6196 """Create a new MetaData object.
6198 :param schema:
6199 The default schema to use for the :class:`_schema.Table`,
6200 :class:`.Sequence`, and potentially other objects associated with
6201 this :class:`_schema.MetaData`. Defaults to ``None``.
6203 .. seealso::
6205 :ref:`schema_metadata_schema_name` - details on how the
6206 :paramref:`_schema.MetaData.schema` parameter is used.
6208 :paramref:`_schema.Table.schema`
6210 :paramref:`.Sequence.schema`
6212 :param quote_schema:
6213 Sets the ``quote_schema`` flag for those :class:`_schema.Table`,
6214 :class:`.Sequence`, and other objects which make usage of the
6215 local ``schema`` name.
6217 :param info: Optional data dictionary which will be populated into the
6218 :attr:`.SchemaItem.info` attribute of this object.
6220 :param naming_convention: a dictionary referring to values which
6221 will establish default naming conventions for :class:`.Constraint`
6222 and :class:`.Index` objects, for those objects which are not given
6223 a name explicitly.
6225 The keys of this dictionary may be:
6227 * a constraint or Index class, e.g. the :class:`.UniqueConstraint`,
6228 :class:`_schema.ForeignKeyConstraint` class, the :class:`.Index`
6229 class
6231 * a string mnemonic for one of the known constraint classes;
6232 ``"fk"``, ``"pk"``, ``"ix"``, ``"ck"``, ``"uq"`` for foreign key,
6233 primary key, index, check, and unique constraint, respectively.
6235 * the string name of a user-defined "token" that can be used
6236 to define new naming tokens.
6238 The values associated with each "constraint class" or "constraint
6239 mnemonic" key are string naming templates, such as
6240 ``"uq_%(table_name)s_%(column_0_name)s"``,
6241 which describe how the name should be composed. The values
6242 associated with user-defined "token" keys should be callables of the
6243 form ``fn(constraint, table)``, which accepts the constraint/index
6244 object and :class:`_schema.Table` as arguments, returning a string
6245 result.
6247 The built-in names are as follows, some of which may only be
6248 available for certain types of constraint:
6250 * ``%(table_name)s`` - the name of the :class:`_schema.Table`
6251 object
6252 associated with the constraint.
6254 * ``%(referred_table_name)s`` - the name of the
6255 :class:`_schema.Table`
6256 object associated with the referencing target of a
6257 :class:`_schema.ForeignKeyConstraint`.
6259 * ``%(column_0_name)s`` - the name of the :class:`_schema.Column`
6260 at
6261 index position "0" within the constraint.
6263 * ``%(column_0N_name)s`` - the name of all :class:`_schema.Column`
6264 objects in order within the constraint, joined without a
6265 separator.
6267 * ``%(column_0_N_name)s`` - the name of all
6268 :class:`_schema.Column`
6269 objects in order within the constraint, joined with an
6270 underscore as a separator.
6272 * ``%(column_0_label)s``, ``%(column_0N_label)s``,
6273 ``%(column_0_N_label)s`` - the label of either the zeroth
6274 :class:`_schema.Column` or all :class:`.Columns`, separated with
6275 or without an underscore
6277 * ``%(column_0_key)s``, ``%(column_0N_key)s``,
6278 ``%(column_0_N_key)s`` - the key of either the zeroth
6279 :class:`_schema.Column` or all :class:`.Columns`, separated with
6280 or without an underscore
6282 * ``%(referred_column_0_name)s``, ``%(referred_column_0N_name)s``
6283 ``%(referred_column_0_N_name)s``, ``%(referred_column_0_key)s``,
6284 ``%(referred_column_0N_key)s``, ... column tokens which
6285 render the names/keys/labels of columns that are referenced
6286 by a :class:`_schema.ForeignKeyConstraint`.
6288 * ``%(constraint_name)s`` - a special key that refers to the
6289 existing name given to the constraint. When this key is
6290 present, the :class:`.Constraint` object's existing name will be
6291 replaced with one that is composed from template string that
6292 uses this token. When this token is present, it is required that
6293 the :class:`.Constraint` is given an explicit name ahead of time.
6295 * user-defined: any additional token may be implemented by passing
6296 it along with a ``fn(constraint, table)`` callable to the
6297 naming_convention dictionary.
6299 .. seealso::
6301 :ref:`constraint_naming_conventions` - for detailed usage
6302 examples.
6304 """
6305 if schema is not None and not isinstance(schema, str):
6306 raise exc.ArgumentError(
6307 "expected schema argument to be a string, "
6308 f"got {type(schema)}."
6309 )
6310 self.tables = util.FacadeDict()
6311 self.schema = quoted_name.construct(schema, quote_schema)
6312 self.naming_convention = (
6313 naming_convention
6314 if naming_convention
6315 else DEFAULT_NAMING_CONVENTION
6316 )
6317 if info:
6318 self.info = info
6319 self._schemas: Set[str] = set()
6320 self._sequences: Dict[str, Sequence] = {}
6321 self._fk_memos: Dict[Tuple[str, Optional[str]], List[ForeignKey]] = (
6322 collections.defaultdict(list)
6323 )
6324 self._objects: Set[Union[HasSchemaAttr, SchemaType]] = set()
6326 tables: util.FacadeDict[str, Table]
6327 """A dictionary of :class:`_schema.Table`
6328 objects keyed to their name or "table key".
6330 The exact key is that determined by the :attr:`_schema.Table.key`
6331 attribute;
6332 for a table with no :attr:`_schema.Table.schema` attribute,
6333 this is the same
6334 as :attr:`_schema.Table.name`. For a table with a schema,
6335 it is typically of the
6336 form ``schemaname.tablename``.
6338 .. seealso::
6340 :attr:`_schema.MetaData.sorted_tables`
6342 """
6344 def __repr__(self) -> str:
6345 return "MetaData()"
6347 def __contains__(self, table_or_key: Union[str, Table]) -> bool:
6348 if not isinstance(table_or_key, str):
6349 table_or_key = table_or_key.key
6350 return table_or_key in self.tables
6352 def _add_table(
6353 self, name: str, schema: Optional[str], table: Table
6354 ) -> None:
6355 key = _get_table_key(name, schema)
6356 self.tables._insert_item(key, table)
6357 if schema:
6358 self._schemas.add(schema)
6360 def _remove_table(self, name: str, schema: Optional[str]) -> None:
6361 key = _get_table_key(name, schema)
6362 removed = dict.pop(self.tables, key, None)
6363 if removed is not None:
6364 for fk in removed.foreign_keys:
6365 fk._remove_from_metadata(self)
6366 if self._schemas:
6367 self._schemas = {
6368 t.schema for t in self.tables.values() if t.schema is not None
6369 }
6371 def __getstate__(self) -> Dict[str, Any]:
6372 return {
6373 "tables": self.tables,
6374 "schema": self.schema,
6375 "schemas": self._schemas,
6376 "sequences": self._sequences,
6377 "fk_memos": self._fk_memos,
6378 "naming_convention": self.naming_convention,
6379 "objects": self._objects,
6380 }
6382 def __setstate__(self, state: Dict[str, Any]) -> None:
6383 self.tables = state["tables"]
6384 self.schema = state["schema"]
6385 self.naming_convention = state["naming_convention"]
6386 self._sequences = state["sequences"]
6387 self._schemas = state["schemas"]
6388 self._fk_memos = state["fk_memos"]
6389 self._objects = state.get("objects", set())
6391 def clear(self) -> None:
6392 """Clear all objects from this MetaData."""
6394 dict.clear(self.tables)
6395 self._schemas.clear()
6396 self._fk_memos.clear()
6397 self._sequences.clear()
6398 self._objects.clear()
6400 def remove(self, table: Table) -> None:
6401 """Remove the given Table object from this MetaData."""
6403 self._remove_table(table.name, table.schema)
6405 @property
6406 def sorted_tables(self) -> List[Table]:
6407 """Returns a list of :class:`_schema.Table` objects sorted in order of
6408 foreign key dependency.
6410 The sorting will place :class:`_schema.Table`
6411 objects that have dependencies
6412 first, before the dependencies themselves, representing the
6413 order in which they can be created. To get the order in which
6414 the tables would be dropped, use the ``reversed()`` Python built-in.
6416 .. warning::
6418 The :attr:`.MetaData.sorted_tables` attribute cannot by itself
6419 accommodate automatic resolution of dependency cycles between
6420 tables, which are usually caused by mutually dependent foreign key
6421 constraints. When these cycles are detected, the foreign keys
6422 of these tables are omitted from consideration in the sort.
6423 A warning is emitted when this condition occurs, which will be an
6424 exception raise in a future release. Tables which are not part
6425 of the cycle will still be returned in dependency order.
6427 To resolve these cycles, the
6428 :paramref:`_schema.ForeignKeyConstraint.use_alter` parameter may be
6429 applied to those constraints which create a cycle. Alternatively,
6430 the :func:`_schema.sort_tables_and_constraints` function will
6431 automatically return foreign key constraints in a separate
6432 collection when cycles are detected so that they may be applied
6433 to a schema separately.
6435 .. seealso::
6437 :func:`_schema.sort_tables`
6439 :func:`_schema.sort_tables_and_constraints`
6441 :attr:`_schema.MetaData.tables`
6443 :meth:`_reflection.Inspector.get_table_names`
6445 :meth:`_reflection.Inspector.get_sorted_table_and_fkc_names`
6448 """
6449 return ddl.sort_tables(
6450 sorted(self.tables.values(), key=lambda t: t.key) # type: ignore[attr-defined] # noqa: E501
6451 )
6453 # overload needed to work around mypy this mypy
6454 # https://github.com/python/mypy/issues/17093
6455 @overload
6456 def reflect(
6457 self,
6458 bind: Engine,
6459 schema: Optional[str] = ...,
6460 views: bool = ...,
6461 only: Union[
6462 _typing_Sequence[str], Callable[[str, MetaData], bool], None
6463 ] = ...,
6464 extend_existing: bool = ...,
6465 autoload_replace: bool = ...,
6466 resolve_fks: bool = ...,
6467 **dialect_kwargs: Any,
6468 ) -> None: ...
6470 @overload
6471 def reflect(
6472 self,
6473 bind: Connection,
6474 schema: Optional[str] = ...,
6475 views: bool = ...,
6476 only: Union[
6477 _typing_Sequence[str], Callable[[str, MetaData], bool], None
6478 ] = ...,
6479 extend_existing: bool = ...,
6480 autoload_replace: bool = ...,
6481 resolve_fks: bool = ...,
6482 **dialect_kwargs: Any,
6483 ) -> None: ...
6485 @util.preload_module("sqlalchemy.engine.reflection")
6486 def reflect(
6487 self,
6488 bind: Union[Engine, Connection],
6489 schema: Optional[str] = None,
6490 views: bool = False,
6491 only: Union[
6492 _typing_Sequence[str], Callable[[str, MetaData], bool], None
6493 ] = None,
6494 extend_existing: bool = False,
6495 autoload_replace: bool = True,
6496 resolve_fks: bool = True,
6497 **dialect_kwargs: Any,
6498 ) -> None:
6499 r"""Load all available table definitions from the database.
6501 Automatically creates ``Table`` entries in this ``MetaData`` for any
6502 table available in the database but not yet present in the
6503 ``MetaData``. May be called multiple times to pick up tables recently
6504 added to the database, however no special action is taken if a table
6505 in this ``MetaData`` no longer exists in the database.
6507 :param bind:
6508 A :class:`.Connection` or :class:`.Engine` used to access the
6509 database.
6511 :param schema:
6512 Optional, query and reflect tables from an alternate schema.
6513 If None, the schema associated with this :class:`_schema.MetaData`
6514 is used, if any.
6516 :param views:
6517 If True, also reflect views (materialized and plain).
6519 :param only:
6520 Optional. Load only a sub-set of available named tables. May be
6521 specified as a sequence of names or a callable.
6523 If a sequence of names is provided, only those tables will be
6524 reflected. An error is raised if a table is requested but not
6525 available. Named tables already present in this ``MetaData`` are
6526 ignored.
6528 If a callable is provided, it will be used as a boolean predicate to
6529 filter the list of potential table names. The callable is called
6530 with a table name and this ``MetaData`` instance as positional
6531 arguments and should return a true value for any table to reflect.
6533 :param extend_existing: Passed along to each :class:`_schema.Table` as
6534 :paramref:`_schema.Table.extend_existing`.
6536 :param autoload_replace: Passed along to each :class:`_schema.Table`
6537 as
6538 :paramref:`_schema.Table.autoload_replace`.
6540 :param resolve_fks: if True, reflect :class:`_schema.Table`
6541 objects linked
6542 to :class:`_schema.ForeignKey` objects located in each
6543 :class:`_schema.Table`.
6544 For :meth:`_schema.MetaData.reflect`,
6545 this has the effect of reflecting
6546 related tables that might otherwise not be in the list of tables
6547 being reflected, for example if the referenced table is in a
6548 different schema or is omitted via the
6549 :paramref:`.MetaData.reflect.only` parameter. When False,
6550 :class:`_schema.ForeignKey` objects are not followed to the
6551 :class:`_schema.Table`
6552 in which they link, however if the related table is also part of the
6553 list of tables that would be reflected in any case, the
6554 :class:`_schema.ForeignKey` object will still resolve to its related
6555 :class:`_schema.Table` after the :meth:`_schema.MetaData.reflect`
6556 operation is
6557 complete. Defaults to True.
6559 .. seealso::
6561 :paramref:`_schema.Table.resolve_fks`
6563 :param \**dialect_kwargs: Additional keyword arguments not mentioned
6564 above are dialect specific, and passed in the form
6565 ``<dialectname>_<argname>``. See the documentation regarding an
6566 individual dialect at :ref:`dialect_toplevel` for detail on
6567 documented arguments.
6569 .. seealso::
6571 :ref:`metadata_reflection_toplevel`
6573 :meth:`_events.DDLEvents.column_reflect` - Event used to customize
6574 the reflected columns. Usually used to generalize the types using
6575 :meth:`_types.TypeEngine.as_generic`
6577 :ref:`metadata_reflection_dbagnostic_types` - describes how to
6578 reflect tables using general types.
6580 """
6582 with inspection.inspect(bind)._inspection_context() as insp:
6583 reflect_opts: Any = {
6584 "autoload_with": insp,
6585 "extend_existing": extend_existing,
6586 "autoload_replace": autoload_replace,
6587 "resolve_fks": resolve_fks,
6588 "_extend_on": set(),
6589 }
6591 reflect_opts.update(dialect_kwargs)
6593 if schema is None:
6594 schema = self.schema
6596 if schema is not None:
6597 reflect_opts["schema"] = schema
6599 kind = util.preloaded.engine_reflection.ObjectKind.TABLE
6600 available: util.OrderedSet[str] = util.OrderedSet(
6601 insp.get_table_names(schema, **dialect_kwargs)
6602 )
6603 if views:
6604 kind = util.preloaded.engine_reflection.ObjectKind.ANY
6605 available.update(insp.get_view_names(schema, **dialect_kwargs))
6606 try:
6607 available.update(
6608 insp.get_materialized_view_names(
6609 schema, **dialect_kwargs
6610 )
6611 )
6612 except NotImplementedError:
6613 pass
6615 if schema is not None:
6616 available_w_schema: util.OrderedSet[str] = util.OrderedSet(
6617 [f"{schema}.{name}" for name in available]
6618 )
6619 else:
6620 available_w_schema = available
6622 current = set(self.tables)
6624 if only is None:
6625 load = [
6626 name
6627 for name, schname in zip(available, available_w_schema)
6628 if extend_existing or schname not in current
6629 ]
6630 elif callable(only):
6631 load = [
6632 name
6633 for name, schname in zip(available, available_w_schema)
6634 if (extend_existing or schname not in current)
6635 and only(name, self)
6636 ]
6637 else:
6638 missing = [name for name in only if name not in available]
6639 if missing:
6640 s = schema and (" schema '%s'" % schema) or ""
6641 missing_str = ", ".join(missing)
6642 raise exc.InvalidRequestError(
6643 f"Could not reflect: requested table(s) not available "
6644 f"in {bind.engine!r}{s}: ({missing_str})"
6645 )
6646 load = [
6647 name
6648 for name in only
6649 if extend_existing or name not in current
6650 ]
6651 # pass the available tables so the inspector can
6652 # choose to ignore the filter_names
6653 _reflect_info = insp._get_reflection_info(
6654 schema=schema,
6655 filter_names=load,
6656 available=available,
6657 kind=kind,
6658 scope=util.preloaded.engine_reflection.ObjectScope.ANY,
6659 **dialect_kwargs,
6660 )
6661 reflect_opts["_reflect_info"] = _reflect_info
6663 for name in load:
6664 try:
6665 Table(name, self, **reflect_opts)
6666 except exc.UnreflectableTableError as uerr:
6667 util.warn(f"Skipping table {name}: {uerr}")
6669 def create_all(
6670 self,
6671 bind: _CreateDropBind,
6672 tables: Optional[_typing_Sequence[Table]] = None,
6673 checkfirst: Union[bool, CheckFirst] = CheckFirst.ALL,
6674 ) -> None:
6675 """Create all tables stored in this metadata.
6677 Conditional by default, will not attempt to recreate tables already
6678 present in the target database.
6680 :param bind:
6681 A :class:`.Connection` or :class:`.Engine` used to access the
6682 database.
6684 :param tables:
6685 Optional list of ``Table`` objects, which is a subset of the total
6686 tables in the ``MetaData`` (others are ignored).
6688 :param checkfirst: A boolean value or instance of :class:`.CheckFirst`.
6689 Indicates which objects should be checked for within a separate pass
6690 before creating schema objects.
6692 """
6693 bind._run_ddl_visitor(
6694 ddl.SchemaGenerator, self, checkfirst=checkfirst, tables=tables
6695 )
6697 def drop_all(
6698 self,
6699 bind: _CreateDropBind,
6700 tables: Optional[_typing_Sequence[Table]] = None,
6701 checkfirst: Union[bool, CheckFirst] = CheckFirst.ALL,
6702 ) -> None:
6703 """Drop all tables stored in this metadata.
6705 Conditional by default, will not attempt to drop tables not present in
6706 the target database.
6708 :param bind:
6709 A :class:`.Connection` or :class:`.Engine` used to access the
6710 database.
6712 :param tables:
6713 Optional list of ``Table`` objects, which is a subset of the
6714 total tables in the ``MetaData`` (others are ignored).
6716 :param checkfirst: A boolean value or instance of :class:`.CheckFirst`.
6717 Indicates which objects should be checked for within a separate pass
6718 before dropping schema objects.
6720 """
6721 bind._run_ddl_visitor(
6722 ddl.SchemaDropper, self, checkfirst=checkfirst, tables=tables
6723 )
6725 @property
6726 def schemas(self) -> _typing_Sequence[str]:
6727 """A sequence of schema names that are present in this MetaData."""
6728 schemas = self._schemas
6729 if self.schema:
6730 schemas = schemas | {self.schema}
6731 return tuple(schemas)
6733 def get_schema_objects(
6734 self,
6735 kind: Type[_T],
6736 *,
6737 schema: Union[str, None, Literal[_NoArg.NO_ARG]] = _NoArg.NO_ARG,
6738 ) -> _typing_Sequence[_T]:
6739 """Return a sequence of schema objects of the given kind.
6741 This method can be used to return :class:`_sqltypes.Enum`,
6742 :class:`.Sequence`, etc. objects registered in this
6743 :class:`_schema.MetaData`.
6745 :param kind: a type that indicates what object to return, such as
6746 :class:`Enum` or :class:`Sequence`.
6747 :param schema: Optional, a schema name to filter the objects by. If
6748 not provided the default schema of the metadata is used.
6750 """
6752 if schema is _NoArg.NO_ARG:
6753 schema = self.schema
6754 return tuple(
6755 obj
6756 for obj in self._objects
6757 if isinstance(obj, kind) and obj.schema == schema
6758 )
6760 def get_schema_object_by_name(
6761 self,
6762 kind: Type[_T],
6763 name: str,
6764 *,
6765 schema: Union[str, None, Literal[_NoArg.NO_ARG]] = _NoArg.NO_ARG,
6766 ) -> Optional[_T]:
6767 """Return a schema objects of the given kind and name if found.
6769 This method can be used to return :class:`_sqltypes.Enum`,
6770 :class:`.Sequence`, etc. objects registered in this
6771 :class:`_schema.MetaData`.
6773 :param kind: a type that indicates what object to return, such as
6774 :class:`Enum` or :class:`Sequence`.
6775 :param name: the name of the object to return.
6776 :param schema: Optional, a schema name to filter the objects by. If
6777 not provided the default schema of the metadata is used.
6779 """
6781 for obj in self.get_schema_objects(kind, schema=schema):
6782 if getattr(obj, "name", None) == name:
6783 return obj
6784 return None
6786 def _register_object(self, obj: Union[HasSchemaAttr, SchemaType]) -> None:
6787 self._objects.add(obj)
6790class Computed(FetchedValue, SchemaItem):
6791 """Defines a generated column, i.e. "GENERATED ALWAYS AS" syntax.
6793 The :class:`.Computed` construct is an inline construct added to the
6794 argument list of a :class:`_schema.Column` object::
6796 from sqlalchemy import Computed
6798 Table(
6799 "square",
6800 metadata_obj,
6801 Column("side", Float, nullable=False),
6802 Column("area", Float, Computed("side * side")),
6803 )
6805 See the linked documentation below for complete details.
6807 .. seealso::
6809 :ref:`computed_ddl`
6811 """
6813 __visit_name__ = "computed_column"
6815 column: Optional[Column[Any]]
6817 @_document_text_coercion(
6818 "sqltext", ":class:`.Computed`", ":paramref:`.Computed.sqltext`"
6819 )
6820 def __init__(
6821 self, sqltext: _DDLColumnArgument, persisted: Optional[bool] = None
6822 ) -> None:
6823 """Construct a GENERATED ALWAYS AS DDL construct to accompany a
6824 :class:`_schema.Column`.
6826 :param sqltext:
6827 A string containing the column generation expression, which will be
6828 used verbatim, or a SQL expression construct, such as a
6829 :func:`_expression.text`
6830 object. If given as a string, the object is converted to a
6831 :func:`_expression.text` object.
6833 :param persisted:
6834 Optional, controls how this column should be persisted by the
6835 database. Possible values are:
6837 * ``None``, the default, it will use the default persistence
6838 defined by the database.
6839 * ``True``, will render ``GENERATED ALWAYS AS ... STORED``, or the
6840 equivalent for the target database if supported.
6841 * ``False``, will render ``GENERATED ALWAYS AS ... VIRTUAL``, or
6842 the equivalent for the target database if supported.
6844 Specifying ``True`` or ``False`` may raise an error when the DDL
6845 is emitted to the target database if the database does not support
6846 that persistence option. Leaving this parameter at its default
6847 of ``None`` is guaranteed to succeed for all databases that support
6848 ``GENERATED ALWAYS AS``.
6850 """
6851 self.sqltext = coercions.expect(roles.DDLExpressionRole, sqltext)
6852 self.persisted = persisted
6853 self.column = None
6855 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
6856 assert isinstance(parent, Column)
6858 if not isinstance(
6859 parent.server_default, (type(None), Computed)
6860 ) or not isinstance(parent.server_onupdate, (type(None), Computed)):
6861 raise exc.ArgumentError(
6862 "A generated column cannot specify a server_default or a "
6863 "server_onupdate argument"
6864 )
6865 self.column = parent
6866 parent.computed = self
6867 self.column.server_onupdate = self
6868 self.column.server_default = self
6870 def _as_for_update(self, for_update: bool) -> FetchedValue:
6871 return self
6873 @util.deprecated(
6874 "1.4",
6875 "The :meth:`_schema.Computed.copy` method is deprecated "
6876 "and will be removed in a future release.",
6877 )
6878 def copy(
6879 self, *, target_table: Optional[Table] = None, **kw: Any
6880 ) -> Computed:
6881 return self._copy(target_table=target_table, **kw)
6883 def _copy(
6884 self, *, target_table: Optional[Table] = None, **kw: Any
6885 ) -> Computed:
6886 sqltext = _copy_expression(
6887 self.sqltext,
6888 self.column.table if self.column is not None else None,
6889 target_table,
6890 )
6891 g = Computed(sqltext, persisted=self.persisted)
6893 return self._schema_item_copy(g)
6896class Identity(IdentityOptions, FetchedValue, SchemaItem):
6897 """Defines an identity column, i.e. "GENERATED { ALWAYS | BY DEFAULT }
6898 AS IDENTITY" syntax.
6900 The :class:`.Identity` construct is an inline construct added to the
6901 argument list of a :class:`_schema.Column` object::
6903 from sqlalchemy import Identity
6905 Table(
6906 "foo",
6907 metadata_obj,
6908 Column("id", Integer, Identity()),
6909 Column("description", Text),
6910 )
6912 See the linked documentation below for complete details.
6914 .. versionadded:: 1.4
6916 .. seealso::
6918 :ref:`identity_ddl`
6920 """
6922 __visit_name__ = "identity_column"
6924 is_identity = True
6926 @util.deprecated_params(
6927 order=(
6928 "2.1",
6929 "This parameter is supported only by Oracle Database, "
6930 "use ``oracle_order`` instead.",
6931 ),
6932 on_null=(
6933 "2.1",
6934 "This parameter is supported only by Oracle Database, "
6935 "use ``oracle_on_null`` instead.",
6936 ),
6937 )
6938 def __init__(
6939 self,
6940 always: Optional[bool] = False,
6941 on_null: Optional[bool] = None,
6942 start: Optional[int] = None,
6943 increment: Optional[int] = None,
6944 minvalue: Optional[int] = None,
6945 maxvalue: Optional[int] = None,
6946 nominvalue: Optional[bool] = None,
6947 nomaxvalue: Optional[bool] = None,
6948 cycle: Optional[bool] = None,
6949 cache: Optional[int] = None,
6950 order: Optional[bool] = None,
6951 **dialect_kw: Any,
6952 ) -> None:
6953 """Construct a GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY DDL
6954 construct to accompany a :class:`_schema.Column`.
6956 See the :class:`.Sequence` documentation for a complete description
6957 of most parameters.
6959 .. note::
6960 MSSQL supports this construct as the preferred alternative to
6961 generate an IDENTITY on a column, but it uses non standard
6962 syntax that only support :paramref:`_schema.Identity.start`
6963 and :paramref:`_schema.Identity.increment`.
6964 All other parameters are ignored.
6966 :param always:
6967 A boolean, that indicates the type of identity column.
6968 If ``False`` is specified, the default, then the user-specified
6969 value takes precedence.
6970 If ``True`` is specified, a user-specified value is not accepted (
6971 on some backends, like PostgreSQL, OVERRIDING SYSTEM VALUE, or
6972 similar, may be specified in an INSERT to override the sequence
6973 value).
6974 Some backends also have a default value for this parameter,
6975 ``None`` can be used to omit rendering this part in the DDL. It
6976 will be treated as ``False`` if a backend does not have a default
6977 value.
6979 :param on_null:
6980 Set to ``True`` to specify ON NULL in conjunction with a
6981 ``always=False`` identity column. This option is only supported on
6982 some backends, like Oracle Database.
6984 :param start: the starting index of the sequence.
6985 :param increment: the increment value of the sequence.
6986 :param minvalue: the minimum value of the sequence.
6987 :param maxvalue: the maximum value of the sequence.
6988 :param nominvalue: no minimum value of the sequence.
6989 :param nomaxvalue: no maximum value of the sequence.
6990 :param cycle: allows the sequence to wrap around when the maxvalue
6991 or minvalue has been reached.
6992 :param cache: optional integer value; number of future values in the
6993 sequence which are calculated in advance.
6994 :param order: optional boolean value; if true, renders the
6995 ORDER keyword.
6997 """
6998 self.dialect_options
6999 if on_null is not None:
7000 if "oracle_on_null" in dialect_kw:
7001 raise exc.ArgumentError(
7002 "Cannot specify both 'on_null' and 'oracle_on_null'. "
7003 "Please use only 'oracle_on_null'."
7004 )
7005 dialect_kw["oracle_on_null"] = on_null
7007 IdentityOptions.__init__(
7008 self,
7009 start=start,
7010 increment=increment,
7011 minvalue=minvalue,
7012 maxvalue=maxvalue,
7013 nominvalue=nominvalue,
7014 nomaxvalue=nomaxvalue,
7015 cycle=cycle,
7016 cache=cache,
7017 order=order,
7018 **dialect_kw,
7019 )
7020 self.always = always
7021 self.column = None
7023 @property
7024 def on_null(self) -> Optional[bool]:
7025 """Alias of the ``dialect_kwargs`` ``'oracle_on_null'``.
7027 .. deprecated:: 2.1 The 'on_null' attribute is deprecated.
7028 """
7029 value: Optional[bool] = self.dialect_kwargs.get("oracle_on_null")
7030 return value
7032 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
7033 assert isinstance(parent, Column)
7034 if not isinstance(
7035 parent.server_default, (type(None), Identity)
7036 ) or not isinstance(parent.server_onupdate, type(None)):
7037 raise exc.ArgumentError(
7038 "A column with an Identity object cannot specify a "
7039 "server_default or a server_onupdate argument"
7040 )
7041 if parent.autoincrement is False:
7042 raise exc.ArgumentError(
7043 "A column with an Identity object cannot specify "
7044 "autoincrement=False"
7045 )
7046 self.column = parent
7048 parent.identity = self
7049 if parent._user_defined_nullable is NULL_UNSPECIFIED:
7050 parent.nullable = False
7052 parent.server_default = self
7054 def _as_for_update(self, for_update: bool) -> FetchedValue:
7055 return self
7057 @util.deprecated(
7058 "1.4",
7059 "The :meth:`_schema.Identity.copy` method is deprecated "
7060 "and will be removed in a future release.",
7061 )
7062 def copy(self, **kw: Any) -> Identity:
7063 return self._copy(**kw)
7065 def _copy(self, **kw: Any) -> Identity:
7066 i = Identity(**self._as_dict(), **self.dialect_kwargs)
7068 return self._schema_item_copy(i)
7070 def _as_dict(self) -> Dict[str, Any]:
7071 return {
7072 # always=None means something different than always=False
7073 "always": self.always,
7074 **super()._as_dict(),
7075 }