Shortcuts on this page
r m x toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1# sql/schema.py
2# Copyright (C) 2005-2026 the SQLAlchemy authors and contributors
3# <see AUTHORS file>
4#
5# This module is part of SQLAlchemy and is released under
6# the MIT License: https://www.opensource.org/licenses/mit-license.php
8"""The schema module provides the building blocks for database metadata.
10Each element within this module describes a database entity which can be
11created and dropped, or is otherwise part of such an entity. Examples include
12tables, columns, sequences, and indexes.
14All entities are subclasses of :class:`~sqlalchemy.schema.SchemaItem`, and as
15defined in this module they are intended to be agnostic of any vendor-specific
16constructs.
18A collection of entities are grouped into a unit called
19:class:`~sqlalchemy.schema.MetaData`. MetaData serves as a logical grouping of
20schema elements, and can also be associated with an actual database connection
21such that operations involving the contained elements can contact the database
22as needed.
24Two of the elements here also build upon their "syntactic" counterparts, which
25are defined in :class:`~sqlalchemy.sql.expression.`, specifically
26:class:`~sqlalchemy.schema.Table` and :class:`~sqlalchemy.schema.Column`.
27Since these objects are part of the SQL expression language, they are usable
28as components in SQL expressions.
30"""
32from __future__ import annotations
34from abc import ABC
35import collections
36from enum import Enum
37import operator
38import typing
39from typing import Any
40from typing import Callable
41from typing import cast
42from typing import Collection
43from typing import Dict
44from typing import Iterable
45from typing import Iterator
46from typing import List
47from typing import Mapping
48from typing import NoReturn
49from typing import Optional
50from typing import overload
51from typing import Sequence as _typing_Sequence
52from typing import Set
53from typing import Tuple
54from typing import TYPE_CHECKING
55from typing import TypeVar
56from typing import Union
58from . import coercions
59from . import ddl
60from . import roles
61from . import type_api
62from . import visitors
63from .base import _DefaultDescriptionTuple
64from .base import _NoArg
65from .base import _NoneName
66from .base import _SentinelColumnCharacterization
67from .base import _SentinelDefaultCharacterization
68from .base import DedupeColumnCollection
69from .base import DialectKWArgs
70from .base import Executable
71from .base import SchemaEventTarget as SchemaEventTarget
72from .base import SchemaVisitable as SchemaVisitable
73from .coercions import _document_text_coercion
74from .elements import ClauseElement
75from .elements import ColumnClause
76from .elements import ColumnElement
77from .elements import quoted_name
78from .elements import TextClause
79from .selectable import TableClause
80from .type_api import to_instance
81from .visitors import ExternallyTraversible
82from .. import event
83from .. import exc
84from .. import inspection
85from .. import util
86from ..util import HasMemoized
87from ..util.typing import Final
88from ..util.typing import Literal
89from ..util.typing import Protocol
90from ..util.typing import Self
91from ..util.typing import TypedDict
92from ..util.typing import TypeGuard
94if typing.TYPE_CHECKING:
95 from ._typing import _AutoIncrementType
96 from ._typing import _CreateDropBind
97 from ._typing import _DDLColumnArgument
98 from ._typing import _InfoType
99 from ._typing import _TextCoercedExpressionArgument
100 from ._typing import _TypeEngineArgument
101 from .base import ColumnSet
102 from .base import ReadOnlyColumnCollection
103 from .compiler import DDLCompiler
104 from .elements import BindParameter
105 from .elements import KeyedColumnElement
106 from .functions import Function
107 from .type_api import TypeEngine
108 from .visitors import anon_map
109 from ..engine import Connection
110 from ..engine import Engine
111 from ..engine.interfaces import _CoreMultiExecuteParams
112 from ..engine.interfaces import CoreExecuteOptionsParameter
113 from ..engine.interfaces import ExecutionContext
114 from ..engine.reflection import _ReflectionInfo
115 from ..sql.selectable import FromClause
117_T = TypeVar("_T", bound="Any")
118_SI = TypeVar("_SI", bound="SchemaItem")
119_TAB = TypeVar("_TAB", bound="Table")
122_ConstraintNameArgument = Optional[Union[str, _NoneName]]
124_ServerDefaultArgument = Union[
125 "FetchedValue", str, TextClause, ColumnElement[Any]
126]
128_ServerOnUpdateArgument = _ServerDefaultArgument
131class SchemaConst(Enum):
132 RETAIN_SCHEMA = 1
133 """Symbol indicating that a :class:`_schema.Table`, :class:`.Sequence`
134 or in some cases a :class:`_schema.ForeignKey` object, in situations
135 where the object is being copied for a :meth:`.Table.to_metadata`
136 operation, should retain the schema name that it already has.
138 """
140 BLANK_SCHEMA = 2
141 """Symbol indicating that a :class:`_schema.Table` or :class:`.Sequence`
142 should have 'None' for its schema, even if the parent
143 :class:`_schema.MetaData` has specified a schema.
145 .. seealso::
147 :paramref:`_schema.MetaData.schema`
149 :paramref:`_schema.Table.schema`
151 :paramref:`.Sequence.schema`
153 """
155 NULL_UNSPECIFIED = 3
156 """Symbol indicating the "nullable" keyword was not passed to a Column.
158 This is used to distinguish between the use case of passing
159 ``nullable=None`` to a :class:`.Column`, which has special meaning
160 on some backends such as SQL Server.
162 """
165RETAIN_SCHEMA: Final[Literal[SchemaConst.RETAIN_SCHEMA]] = (
166 SchemaConst.RETAIN_SCHEMA
167)
168BLANK_SCHEMA: Final[Literal[SchemaConst.BLANK_SCHEMA]] = (
169 SchemaConst.BLANK_SCHEMA
170)
171NULL_UNSPECIFIED: Final[Literal[SchemaConst.NULL_UNSPECIFIED]] = (
172 SchemaConst.NULL_UNSPECIFIED
173)
176def _get_table_key(name: str, schema: Optional[str]) -> str:
177 if schema is None:
178 return name
179 else:
180 return schema + "." + name
183# this should really be in sql/util.py but we'd have to
184# break an import cycle
185def _copy_expression(
186 expression: ColumnElement[Any],
187 source_table: Optional[Table],
188 target_table: Optional[Table],
189) -> ColumnElement[Any]:
190 if source_table is None or target_table is None:
191 return expression
193 fixed_source_table = source_table
194 fixed_target_table = target_table
196 def replace(
197 element: ExternallyTraversible, **kw: Any
198 ) -> Optional[ExternallyTraversible]:
199 if (
200 isinstance(element, Column)
201 and element.table is fixed_source_table
202 and element.key in fixed_source_table.c
203 ):
204 return fixed_target_table.c[element.key]
205 else:
206 return None
208 return cast(
209 ColumnElement[Any],
210 visitors.replacement_traverse(expression, {}, replace),
211 )
214@inspection._self_inspects
215class SchemaItem(SchemaVisitable):
216 """Base class for items that define a database schema."""
218 __visit_name__ = "schema_item"
220 create_drop_stringify_dialect = "default"
222 def _init_items(self, *args: SchemaItem, **kw: Any) -> None:
223 """Initialize the list of child items for this SchemaItem."""
224 for item in args:
225 if item is not None:
226 try:
227 spwd = item._set_parent_with_dispatch
228 except AttributeError as err:
229 raise exc.ArgumentError(
230 "'SchemaItem' object, such as a 'Column' or a "
231 f"'Constraint' expected, got {item!r}"
232 ) from err
233 else:
234 spwd(self, **kw)
236 def __repr__(self) -> str:
237 return util.generic_repr(self, omit_kwarg=["info"])
239 @util.memoized_property
240 def info(self) -> _InfoType:
241 """Info dictionary associated with the object, allowing user-defined
242 data to be associated with this :class:`.SchemaItem`.
244 The dictionary is automatically generated when first accessed.
245 It can also be specified in the constructor of some objects,
246 such as :class:`_schema.Table` and :class:`_schema.Column`.
248 """
249 return {}
251 def _schema_item_copy(self, schema_item: _SI) -> _SI:
252 if "info" in self.__dict__:
253 schema_item.info = self.info.copy()
254 schema_item.dispatch._update(self.dispatch)
255 return schema_item
257 _use_schema_map = True
260class HasConditionalDDL:
261 """define a class that includes the :meth:`.HasConditionalDDL.ddl_if`
262 method, allowing for conditional rendering of DDL.
264 Currently applies to constraints and indexes.
266 .. versionadded:: 2.0
269 """
271 _ddl_if: Optional[ddl.DDLIf] = None
273 def ddl_if(
274 self,
275 dialect: Optional[str] = None,
276 callable_: Optional[ddl.DDLIfCallable] = None,
277 state: Optional[Any] = None,
278 ) -> Self:
279 r"""apply a conditional DDL rule to this schema item.
281 These rules work in a similar manner to the
282 :meth:`.ExecutableDDLElement.execute_if` callable, with the added
283 feature that the criteria may be checked within the DDL compilation
284 phase for a construct such as :class:`.CreateTable`.
285 :meth:`.HasConditionalDDL.ddl_if` currently applies towards the
286 :class:`.Index` construct as well as all :class:`.Constraint`
287 constructs.
289 :param dialect: string name of a dialect, or a tuple of string names
290 to indicate multiple dialect types.
292 :param callable\_: a callable that is constructed using the same form
293 as that described in
294 :paramref:`.ExecutableDDLElement.execute_if.callable_`.
296 :param state: any arbitrary object that will be passed to the
297 callable, if present.
299 .. versionadded:: 2.0
301 .. seealso::
303 :ref:`schema_ddl_ddl_if` - background and usage examples
306 """
307 self._ddl_if = ddl.DDLIf(dialect, callable_, state)
308 return self
311class HasSchemaAttr(SchemaItem):
312 """schema item that includes a top-level schema name"""
314 schema: Optional[str]
317class Table(
318 DialectKWArgs, HasSchemaAttr, TableClause, inspection.Inspectable["Table"]
319):
320 r"""Represent a table in a database.
322 e.g.::
324 mytable = Table(
325 "mytable",
326 metadata,
327 Column("mytable_id", Integer, primary_key=True),
328 Column("value", String(50)),
329 )
331 The :class:`_schema.Table`
332 object constructs a unique instance of itself based
333 on its name and optional schema name within the given
334 :class:`_schema.MetaData` object. Calling the :class:`_schema.Table`
335 constructor with the same name and same :class:`_schema.MetaData` argument
336 a second time will return the *same* :class:`_schema.Table`
337 object - in this way
338 the :class:`_schema.Table` constructor acts as a registry function.
340 .. seealso::
342 :ref:`metadata_describing` - Introduction to database metadata
344 """
346 __visit_name__ = "table"
348 if TYPE_CHECKING:
350 @util.ro_non_memoized_property
351 def primary_key(self) -> PrimaryKeyConstraint: ...
353 @util.ro_non_memoized_property
354 def foreign_keys(self) -> Set[ForeignKey]: ...
356 _columns: DedupeColumnCollection[Column[Any]]
358 _sentinel_column: Optional[Column[Any]]
360 constraints: Set[Constraint]
361 """A collection of all :class:`_schema.Constraint` objects associated with
362 this :class:`_schema.Table`.
364 Includes :class:`_schema.PrimaryKeyConstraint`,
365 :class:`_schema.ForeignKeyConstraint`, :class:`_schema.UniqueConstraint`,
366 :class:`_schema.CheckConstraint`. A separate collection
367 :attr:`_schema.Table.foreign_key_constraints` refers to the collection
368 of all :class:`_schema.ForeignKeyConstraint` objects, and the
369 :attr:`_schema.Table.primary_key` attribute refers to the single
370 :class:`_schema.PrimaryKeyConstraint` associated with the
371 :class:`_schema.Table`.
373 .. seealso::
375 :attr:`_schema.Table.constraints`
377 :attr:`_schema.Table.primary_key`
379 :attr:`_schema.Table.foreign_key_constraints`
381 :attr:`_schema.Table.indexes`
383 :class:`_reflection.Inspector`
386 """
388 indexes: Set[Index]
389 """A collection of all :class:`_schema.Index` objects associated with this
390 :class:`_schema.Table`.
392 .. seealso::
394 :meth:`_reflection.Inspector.get_indexes`
396 """
398 if TYPE_CHECKING:
400 @util.ro_non_memoized_property
401 def columns(self) -> ReadOnlyColumnCollection[str, Column[Any]]: ...
403 @util.ro_non_memoized_property
404 def exported_columns(
405 self,
406 ) -> ReadOnlyColumnCollection[str, Column[Any]]: ...
408 @util.ro_non_memoized_property
409 def c(self) -> ReadOnlyColumnCollection[str, Column[Any]]: ...
411 def _gen_cache_key(
412 self, anon_map: anon_map, bindparams: List[BindParameter[Any]]
413 ) -> Tuple[Any, ...]:
414 if self._annotations:
415 return (self,) + self._annotations_cache_key
416 else:
417 return (self,)
419 if not typing.TYPE_CHECKING:
420 # typing tools seem to be inconsistent in how they handle
421 # __new__, so suggest this pattern for classes that use
422 # __new__. apply typing to the __init__ method normally
423 @util.deprecated_params(
424 mustexist=(
425 "1.4",
426 "Deprecated alias of :paramref:`_schema.Table.must_exist`",
427 ),
428 )
429 def __new__(cls, *args: Any, **kw: Any) -> Any:
430 return cls._new(*args, **kw)
432 @classmethod
433 def _new(cls, *args: Any, **kw: Any) -> Any:
434 if not args and not kw:
435 # python3k pickle seems to call this
436 return object.__new__(cls)
438 try:
439 name, metadata, args = args[0], args[1], args[2:]
440 except IndexError:
441 raise TypeError(
442 "Table() takes at least two positional-only "
443 "arguments 'name' and 'metadata'"
444 )
446 schema = kw.get("schema", None)
447 if schema is None:
448 schema = metadata.schema
449 elif schema is BLANK_SCHEMA:
450 schema = None
451 keep_existing = kw.get("keep_existing", False)
452 extend_existing = kw.get("extend_existing", False)
454 if keep_existing and extend_existing:
455 msg = "keep_existing and extend_existing are mutually exclusive."
456 raise exc.ArgumentError(msg)
458 must_exist = kw.pop("must_exist", kw.pop("mustexist", False))
459 key = _get_table_key(name, schema)
460 if key in metadata.tables:
461 if not keep_existing and not extend_existing and bool(args):
462 raise exc.InvalidRequestError(
463 f"Table '{key}' is already defined for this MetaData "
464 "instance. Specify 'extend_existing=True' "
465 "to redefine "
466 "options and columns on an "
467 "existing Table object."
468 )
469 table = metadata.tables[key]
470 if extend_existing:
471 table._init_existing(*args, **kw)
472 return table
473 else:
474 if must_exist:
475 raise exc.InvalidRequestError(f"Table '{key}' not defined")
476 table = object.__new__(cls)
477 table.dispatch.before_parent_attach(table, metadata)
478 metadata._add_table(name, schema, table)
479 try:
480 table.__init__(name, metadata, *args, _no_init=False, **kw) # type: ignore[misc] # noqa: E501
481 table.dispatch.after_parent_attach(table, metadata)
482 return table
483 except Exception:
484 with util.safe_reraise():
485 metadata._remove_table(name, schema)
487 def __init__(
488 self,
489 name: str,
490 metadata: MetaData,
491 *args: SchemaItem,
492 schema: Optional[Union[str, Literal[SchemaConst.BLANK_SCHEMA]]] = None,
493 quote: Optional[bool] = None,
494 quote_schema: Optional[bool] = None,
495 autoload_with: Optional[Union[Engine, Connection]] = None,
496 autoload_replace: bool = True,
497 keep_existing: bool = False,
498 extend_existing: bool = False,
499 resolve_fks: bool = True,
500 include_columns: Optional[Collection[str]] = None,
501 implicit_returning: bool = True,
502 comment: Optional[str] = None,
503 info: Optional[Dict[Any, Any]] = None,
504 listeners: Optional[
505 _typing_Sequence[Tuple[str, Callable[..., Any]]]
506 ] = None,
507 prefixes: Optional[_typing_Sequence[str]] = None,
508 # used internally in the metadata.reflect() process
509 _extend_on: Optional[Set[Table]] = None,
510 # used by __new__ to bypass __init__
511 _no_init: bool = True,
512 # dialect-specific keyword args
513 **kw: Any,
514 ) -> None:
515 r"""Constructor for :class:`_schema.Table`.
518 :param name: The name of this table as represented in the database.
520 The table name, along with the value of the ``schema`` parameter,
521 forms a key which uniquely identifies this :class:`_schema.Table`
522 within
523 the owning :class:`_schema.MetaData` collection.
524 Additional calls to :class:`_schema.Table` with the same name,
525 metadata,
526 and schema name will return the same :class:`_schema.Table` object.
528 Names which contain no upper case characters
529 will be treated as case insensitive names, and will not be quoted
530 unless they are a reserved word or contain special characters.
531 A name with any number of upper case characters is considered
532 to be case sensitive, and will be sent as quoted.
534 To enable unconditional quoting for the table name, specify the flag
535 ``quote=True`` to the constructor, or use the :class:`.quoted_name`
536 construct to specify the name.
538 :param metadata: a :class:`_schema.MetaData`
539 object which will contain this
540 table. The metadata is used as a point of association of this table
541 with other tables which are referenced via foreign key. It also
542 may be used to associate this table with a particular
543 :class:`.Connection` or :class:`.Engine`.
545 :param \*args: Additional positional arguments are used primarily
546 to add the list of :class:`_schema.Column`
547 objects contained within this
548 table. Similar to the style of a CREATE TABLE statement, other
549 :class:`.SchemaItem` constructs may be added here, including
550 :class:`.PrimaryKeyConstraint`, and
551 :class:`_schema.ForeignKeyConstraint`.
553 :param autoload_replace: Defaults to ``True``; when using
554 :paramref:`_schema.Table.autoload_with`
555 in conjunction with :paramref:`_schema.Table.extend_existing`,
556 indicates
557 that :class:`_schema.Column` objects present in the already-existing
558 :class:`_schema.Table`
559 object should be replaced with columns of the same
560 name retrieved from the autoload process. When ``False``, columns
561 already present under existing names will be omitted from the
562 reflection process.
564 Note that this setting does not impact :class:`_schema.Column` objects
565 specified programmatically within the call to :class:`_schema.Table`
566 that
567 also is autoloading; those :class:`_schema.Column` objects will always
568 replace existing columns of the same name when
569 :paramref:`_schema.Table.extend_existing` is ``True``.
571 .. seealso::
573 :paramref:`_schema.Table.autoload_with`
575 :paramref:`_schema.Table.extend_existing`
577 :param autoload_with: An :class:`_engine.Engine` or
578 :class:`_engine.Connection` object,
579 or a :class:`_reflection.Inspector` object as returned by
580 :func:`_sa.inspect`
581 against one, with which this :class:`_schema.Table`
582 object will be reflected.
583 When set to a non-None value, the autoload process will take place
584 for this table against the given engine or connection.
586 .. seealso::
588 :ref:`metadata_reflection_toplevel`
590 :meth:`_events.DDLEvents.column_reflect`
592 :ref:`metadata_reflection_dbagnostic_types`
594 :param extend_existing: When ``True``, indicates that if this
595 :class:`_schema.Table` is already present in the given
596 :class:`_schema.MetaData`,
597 apply further arguments within the constructor to the existing
598 :class:`_schema.Table`.
600 If :paramref:`_schema.Table.extend_existing` or
601 :paramref:`_schema.Table.keep_existing` are not set,
602 and the given name
603 of the new :class:`_schema.Table` refers to a :class:`_schema.Table`
604 that is
605 already present in the target :class:`_schema.MetaData` collection,
606 and
607 this :class:`_schema.Table`
608 specifies additional columns or other constructs
609 or flags that modify the table's state, an
610 error is raised. The purpose of these two mutually-exclusive flags
611 is to specify what action should be taken when a
612 :class:`_schema.Table`
613 is specified that matches an existing :class:`_schema.Table`,
614 yet specifies
615 additional constructs.
617 :paramref:`_schema.Table.extend_existing`
618 will also work in conjunction
619 with :paramref:`_schema.Table.autoload_with` to run a new reflection
620 operation against the database, even if a :class:`_schema.Table`
621 of the same name is already present in the target
622 :class:`_schema.MetaData`; newly reflected :class:`_schema.Column`
623 objects
624 and other options will be added into the state of the
625 :class:`_schema.Table`, potentially overwriting existing columns
626 and options of the same name.
628 As is always the case with :paramref:`_schema.Table.autoload_with`,
629 :class:`_schema.Column` objects can be specified in the same
630 :class:`_schema.Table`
631 constructor, which will take precedence. Below, the existing
632 table ``mytable`` will be augmented with :class:`_schema.Column`
633 objects
634 both reflected from the database, as well as the given
635 :class:`_schema.Column`
636 named "y"::
638 Table(
639 "mytable",
640 metadata,
641 Column("y", Integer),
642 extend_existing=True,
643 autoload_with=engine,
644 )
646 .. seealso::
648 :paramref:`_schema.Table.autoload_with`
650 :paramref:`_schema.Table.autoload_replace`
652 :paramref:`_schema.Table.keep_existing`
655 :param implicit_returning: True by default - indicates that
656 RETURNING can be used, typically by the ORM, in order to fetch
657 server-generated values such as primary key values and
658 server side defaults, on those backends which support RETURNING.
660 In modern SQLAlchemy there is generally no reason to alter this
661 setting, except for some backend specific cases
662 (see :ref:`mssql_triggers` in the SQL Server dialect documentation
663 for one such example).
665 :param include_columns: A list of strings indicating a subset of
666 columns to be loaded via the ``autoload`` operation; table columns who
667 aren't present in this list will not be represented on the resulting
668 ``Table`` object. Defaults to ``None`` which indicates all columns
669 should be reflected.
671 :param resolve_fks: Whether or not to reflect :class:`_schema.Table`
672 objects
673 related to this one via :class:`_schema.ForeignKey` objects, when
674 :paramref:`_schema.Table.autoload_with` is
675 specified. Defaults to True. Set to False to disable reflection of
676 related tables as :class:`_schema.ForeignKey`
677 objects are encountered; may be
678 used either to save on SQL calls or to avoid issues with related tables
679 that can't be accessed. Note that if a related table is already present
680 in the :class:`_schema.MetaData` collection, or becomes present later,
681 a
682 :class:`_schema.ForeignKey` object associated with this
683 :class:`_schema.Table` will
684 resolve to that table normally.
686 .. versionadded:: 1.3
688 .. seealso::
690 :paramref:`.MetaData.reflect.resolve_fks`
693 :param info: Optional data dictionary which will be populated into the
694 :attr:`.SchemaItem.info` attribute of this object.
696 :param keep_existing: When ``True``, indicates that if this Table
697 is already present in the given :class:`_schema.MetaData`, ignore
698 further arguments within the constructor to the existing
699 :class:`_schema.Table`, and return the :class:`_schema.Table`
700 object as
701 originally created. This is to allow a function that wishes
702 to define a new :class:`_schema.Table` on first call, but on
703 subsequent calls will return the same :class:`_schema.Table`,
704 without any of the declarations (particularly constraints)
705 being applied a second time.
707 If :paramref:`_schema.Table.extend_existing` or
708 :paramref:`_schema.Table.keep_existing` are not set,
709 and the given name
710 of the new :class:`_schema.Table` refers to a :class:`_schema.Table`
711 that is
712 already present in the target :class:`_schema.MetaData` collection,
713 and
714 this :class:`_schema.Table`
715 specifies additional columns or other constructs
716 or flags that modify the table's state, an
717 error is raised. The purpose of these two mutually-exclusive flags
718 is to specify what action should be taken when a
719 :class:`_schema.Table`
720 is specified that matches an existing :class:`_schema.Table`,
721 yet specifies
722 additional constructs.
724 .. seealso::
726 :paramref:`_schema.Table.extend_existing`
728 :param listeners: A list of tuples of the form ``(<eventname>, <fn>)``
729 which will be passed to :func:`.event.listen` upon construction.
730 This alternate hook to :func:`.event.listen` allows the establishment
731 of a listener function specific to this :class:`_schema.Table` before
732 the "autoload" process begins. Historically this has been intended
733 for use with the :meth:`.DDLEvents.column_reflect` event, however
734 note that this event hook may now be associated with the
735 :class:`_schema.MetaData` object directly::
737 def listen_for_reflect(table, column_info):
738 "handle the column reflection event"
739 # ...
742 t = Table(
743 "sometable",
744 autoload_with=engine,
745 listeners=[("column_reflect", listen_for_reflect)],
746 )
748 .. seealso::
750 :meth:`_events.DDLEvents.column_reflect`
752 :param must_exist: When ``True``, indicates that this Table must already
753 be present in the given :class:`_schema.MetaData` collection, else
754 an exception is raised.
756 :param prefixes:
757 A list of strings to insert after CREATE in the CREATE TABLE
758 statement. They will be separated by spaces.
760 :param quote: Force quoting of this table's name on or off, corresponding
761 to ``True`` or ``False``. When left at its default of ``None``,
762 the column identifier will be quoted according to whether the name is
763 case sensitive (identifiers with at least one upper case character are
764 treated as case sensitive), or if it's a reserved word. This flag
765 is only needed to force quoting of a reserved word which is not known
766 by the SQLAlchemy dialect.
768 .. note:: setting this flag to ``False`` will not provide
769 case-insensitive behavior for table reflection; table reflection
770 will always search for a mixed-case name in a case sensitive
771 fashion. Case insensitive names are specified in SQLAlchemy only
772 by stating the name with all lower case characters.
774 :param quote_schema: same as 'quote' but applies to the schema identifier.
776 :param schema: The schema name for this table, which is required if
777 the table resides in a schema other than the default selected schema
778 for the engine's database connection. Defaults to ``None``.
780 If the owning :class:`_schema.MetaData` of this :class:`_schema.Table`
781 specifies its
782 own :paramref:`_schema.MetaData.schema` parameter,
783 then that schema name will
784 be applied to this :class:`_schema.Table`
785 if the schema parameter here is set
786 to ``None``. To set a blank schema name on a :class:`_schema.Table`
787 that
788 would otherwise use the schema set on the owning
789 :class:`_schema.MetaData`,
790 specify the special symbol :attr:`.BLANK_SCHEMA`.
792 The quoting rules for the schema name are the same as those for the
793 ``name`` parameter, in that quoting is applied for reserved words or
794 case-sensitive names; to enable unconditional quoting for the schema
795 name, specify the flag ``quote_schema=True`` to the constructor, or use
796 the :class:`.quoted_name` construct to specify the name.
798 :param comment: Optional string that will render an SQL comment on table
799 creation.
801 .. versionadded:: 1.2 Added the :paramref:`_schema.Table.comment`
802 parameter
803 to :class:`_schema.Table`.
805 :param \**kw: Additional keyword arguments not mentioned above are
806 dialect specific, and passed in the form ``<dialectname>_<argname>``.
807 See the documentation regarding an individual dialect at
808 :ref:`dialect_toplevel` for detail on documented arguments.
810 """ # noqa: E501
811 if _no_init:
812 # don't run __init__ from __new__ by default;
813 # __new__ has a specific place that __init__ is called
814 return
816 super().__init__(quoted_name(name, quote))
817 self.metadata = metadata
819 if schema is None:
820 self.schema = metadata.schema
821 elif schema is BLANK_SCHEMA:
822 self.schema = None
823 else:
824 quote_schema = quote_schema
825 assert isinstance(schema, str)
826 self.schema = quoted_name(schema, quote_schema)
828 self._sentinel_column = None
830 self.indexes = set()
831 self.constraints = set()
832 PrimaryKeyConstraint(
833 _implicit_generated=True
834 )._set_parent_with_dispatch(self)
835 self.foreign_keys = set() # type: ignore
836 self._extra_dependencies: Set[Table] = set()
837 if self.schema is not None:
838 self.fullname = "%s.%s" % (self.schema, self.name)
839 else:
840 self.fullname = self.name
842 self.implicit_returning = implicit_returning
843 _reflect_info = kw.pop("_reflect_info", None)
845 self.comment = comment
847 if info is not None:
848 self.info = info
850 if listeners is not None:
851 for evt, fn in listeners:
852 event.listen(self, evt, fn)
854 self._prefixes = prefixes if prefixes else []
856 self._extra_kwargs(**kw)
858 # load column definitions from the database if 'autoload' is defined
859 # we do it after the table is in the singleton dictionary to support
860 # circular foreign keys
861 if autoload_with is not None:
862 self._autoload(
863 metadata,
864 autoload_with,
865 include_columns,
866 _extend_on=_extend_on,
867 _reflect_info=_reflect_info,
868 resolve_fks=resolve_fks,
869 )
871 # initialize all the column, etc. objects. done after reflection to
872 # allow user-overrides
874 self._init_items(
875 *args,
876 allow_replacements=extend_existing
877 or keep_existing
878 or autoload_with,
879 all_names={},
880 )
882 def _autoload(
883 self,
884 metadata: MetaData,
885 autoload_with: Union[Engine, Connection],
886 include_columns: Optional[Collection[str]],
887 exclude_columns: Collection[str] = (),
888 resolve_fks: bool = True,
889 _extend_on: Optional[Set[Table]] = None,
890 _reflect_info: _ReflectionInfo | None = None,
891 ) -> None:
892 insp = inspection.inspect(autoload_with)
893 with insp._inspection_context() as conn_insp:
894 conn_insp.reflect_table(
895 self,
896 include_columns,
897 exclude_columns,
898 resolve_fks,
899 _extend_on=_extend_on,
900 _reflect_info=_reflect_info,
901 )
903 @property
904 def _sorted_constraints(self) -> List[Constraint]:
905 """Return the set of constraints as a list, sorted by creation
906 order.
908 """
910 return sorted(self.constraints, key=lambda c: c._creation_order)
912 @property
913 def foreign_key_constraints(self) -> Set[ForeignKeyConstraint]:
914 """:class:`_schema.ForeignKeyConstraint` objects referred to by this
915 :class:`_schema.Table`.
917 This list is produced from the collection of
918 :class:`_schema.ForeignKey`
919 objects currently associated.
922 .. seealso::
924 :attr:`_schema.Table.constraints`
926 :attr:`_schema.Table.foreign_keys`
928 :attr:`_schema.Table.indexes`
930 """
931 return {
932 fkc.constraint
933 for fkc in self.foreign_keys
934 if fkc.constraint is not None
935 }
937 def _init_existing(self, *args: Any, **kwargs: Any) -> None:
938 autoload_with = kwargs.pop("autoload_with", None)
939 autoload = kwargs.pop("autoload", autoload_with is not None)
940 autoload_replace = kwargs.pop("autoload_replace", True)
941 schema = kwargs.pop("schema", None)
942 _extend_on = kwargs.pop("_extend_on", None)
943 _reflect_info = kwargs.pop("_reflect_info", None)
945 # these arguments are only used with _init()
946 extend_existing = kwargs.pop("extend_existing", False)
947 keep_existing = kwargs.pop("keep_existing", False)
949 assert extend_existing
950 assert not keep_existing
952 if schema and schema != self.schema:
953 raise exc.ArgumentError(
954 f"Can't change schema of existing table "
955 f"from '{self.schema}' to '{schema}'",
956 )
958 include_columns = kwargs.pop("include_columns", None)
959 if include_columns is not None:
960 for c in self.c:
961 if c.name not in include_columns:
962 self._columns.remove(c)
964 resolve_fks = kwargs.pop("resolve_fks", True)
966 for key in ("quote", "quote_schema"):
967 if key in kwargs:
968 raise exc.ArgumentError(
969 "Can't redefine 'quote' or 'quote_schema' arguments"
970 )
972 # update `self` with these kwargs, if provided
973 self.comment = kwargs.pop("comment", self.comment)
974 self.implicit_returning = kwargs.pop(
975 "implicit_returning", self.implicit_returning
976 )
977 self.info = kwargs.pop("info", self.info)
979 exclude_columns: _typing_Sequence[str]
981 if autoload:
982 if not autoload_replace:
983 # don't replace columns already present.
984 # we'd like to do this for constraints also however we don't
985 # have simple de-duping for unnamed constraints.
986 exclude_columns = [c.name for c in self.c]
987 else:
988 exclude_columns = ()
989 self._autoload(
990 self.metadata,
991 autoload_with,
992 include_columns,
993 exclude_columns,
994 resolve_fks,
995 _extend_on=_extend_on,
996 _reflect_info=_reflect_info,
997 )
999 all_names = {c.name: c for c in self.c}
1000 self._extra_kwargs(**kwargs)
1001 self._init_items(*args, allow_replacements=True, all_names=all_names)
1003 def _extra_kwargs(self, **kwargs: Any) -> None:
1004 self._validate_dialect_kwargs(kwargs)
1006 def _init_collections(self) -> None:
1007 pass
1009 def _reset_exported(self) -> None:
1010 pass
1012 @util.ro_non_memoized_property
1013 def _autoincrement_column(self) -> Optional[Column[int]]:
1014 return self.primary_key._autoincrement_column
1016 @util.ro_memoized_property
1017 def _sentinel_column_characteristics(
1018 self,
1019 ) -> _SentinelColumnCharacterization:
1020 """determine a candidate column (or columns, in case of a client
1021 generated composite primary key) which can be used as an
1022 "insert sentinel" for an INSERT statement.
1024 The returned structure, :class:`_SentinelColumnCharacterization`,
1025 includes all the details needed by :class:`.Dialect` and
1026 :class:`.SQLCompiler` to determine if these column(s) can be used
1027 as an INSERT..RETURNING sentinel for a particular database
1028 dialect.
1030 .. versionadded:: 2.0.10
1032 """
1034 sentinel_is_explicit = False
1035 sentinel_is_autoinc = False
1036 the_sentinel: Optional[_typing_Sequence[Column[Any]]] = None
1038 # see if a column was explicitly marked "insert_sentinel=True".
1039 explicit_sentinel_col = self._sentinel_column
1041 if explicit_sentinel_col is not None:
1042 the_sentinel = (explicit_sentinel_col,)
1043 sentinel_is_explicit = True
1045 autoinc_col = self._autoincrement_column
1046 if sentinel_is_explicit and explicit_sentinel_col is autoinc_col:
1047 assert autoinc_col is not None
1048 sentinel_is_autoinc = True
1049 elif explicit_sentinel_col is None and autoinc_col is not None:
1050 the_sentinel = (autoinc_col,)
1051 sentinel_is_autoinc = True
1053 default_characterization = _SentinelDefaultCharacterization.UNKNOWN
1055 if the_sentinel:
1056 the_sentinel_zero = the_sentinel[0]
1057 if the_sentinel_zero.identity:
1058 if the_sentinel_zero.identity._increment_is_negative:
1059 if sentinel_is_explicit:
1060 raise exc.InvalidRequestError(
1061 "Can't use IDENTITY default with negative "
1062 "increment as an explicit sentinel column"
1063 )
1064 else:
1065 if sentinel_is_autoinc:
1066 autoinc_col = None
1067 sentinel_is_autoinc = False
1068 the_sentinel = None
1069 else:
1070 default_characterization = (
1071 _SentinelDefaultCharacterization.IDENTITY
1072 )
1073 elif (
1074 the_sentinel_zero.default is None
1075 and the_sentinel_zero.server_default is None
1076 ):
1077 if the_sentinel_zero.nullable:
1078 raise exc.InvalidRequestError(
1079 f"Column {the_sentinel_zero} has been marked as a "
1080 "sentinel "
1081 "column with no default generation function; it "
1082 "at least needs to be marked nullable=False assuming "
1083 "user-populated sentinel values will be used."
1084 )
1085 default_characterization = (
1086 _SentinelDefaultCharacterization.NONE
1087 )
1088 elif the_sentinel_zero.default is not None:
1089 if the_sentinel_zero.default.is_sentinel:
1090 default_characterization = (
1091 _SentinelDefaultCharacterization.SENTINEL_DEFAULT
1092 )
1093 elif default_is_sequence(the_sentinel_zero.default):
1094 if the_sentinel_zero.default._increment_is_negative:
1095 if sentinel_is_explicit:
1096 raise exc.InvalidRequestError(
1097 "Can't use SEQUENCE default with negative "
1098 "increment as an explicit sentinel column"
1099 )
1100 else:
1101 if sentinel_is_autoinc:
1102 autoinc_col = None
1103 sentinel_is_autoinc = False
1104 the_sentinel = None
1106 default_characterization = (
1107 _SentinelDefaultCharacterization.SEQUENCE
1108 )
1109 elif the_sentinel_zero.default.is_callable:
1110 default_characterization = (
1111 _SentinelDefaultCharacterization.CLIENTSIDE
1112 )
1113 elif the_sentinel_zero.server_default is not None:
1114 if sentinel_is_explicit:
1115 raise exc.InvalidRequestError(
1116 f"Column {the_sentinel[0]} can't be a sentinel column "
1117 "because it uses an explicit server side default "
1118 "that's not the Identity() default."
1119 )
1121 default_characterization = (
1122 _SentinelDefaultCharacterization.SERVERSIDE
1123 )
1125 if the_sentinel is None and self.primary_key:
1126 assert autoinc_col is None
1128 # determine for non-autoincrement pk if all elements are
1129 # client side
1130 for _pkc in self.primary_key:
1131 if _pkc.server_default is not None or (
1132 _pkc.default and not _pkc.default.is_callable
1133 ):
1134 break
1135 else:
1136 the_sentinel = tuple(self.primary_key)
1137 default_characterization = (
1138 _SentinelDefaultCharacterization.CLIENTSIDE
1139 )
1141 return _SentinelColumnCharacterization(
1142 the_sentinel,
1143 sentinel_is_explicit,
1144 sentinel_is_autoinc,
1145 default_characterization,
1146 )
1148 @property
1149 def autoincrement_column(self) -> Optional[Column[int]]:
1150 """Returns the :class:`.Column` object which currently represents
1151 the "auto increment" column, if any, else returns None.
1153 This is based on the rules for :class:`.Column` as defined by the
1154 :paramref:`.Column.autoincrement` parameter, which generally means the
1155 column within a single integer column primary key constraint that is
1156 not constrained by a foreign key. If the table does not have such
1157 a primary key constraint, then there's no "autoincrement" column.
1158 A :class:`.Table` may have only one column defined as the
1159 "autoincrement" column.
1161 .. versionadded:: 2.0.4
1163 .. seealso::
1165 :paramref:`.Column.autoincrement`
1167 """
1168 return self._autoincrement_column
1170 @property
1171 def key(self) -> str:
1172 """Return the 'key' for this :class:`_schema.Table`.
1174 This value is used as the dictionary key within the
1175 :attr:`_schema.MetaData.tables` collection. It is typically the same
1176 as that of :attr:`_schema.Table.name` for a table with no
1177 :attr:`_schema.Table.schema`
1178 set; otherwise it is typically of the form
1179 ``schemaname.tablename``.
1181 """
1182 return _get_table_key(self.name, self.schema)
1184 def __repr__(self) -> str:
1185 return "Table(%s)" % ", ".join(
1186 [repr(self.name)]
1187 + [repr(self.metadata)]
1188 + [repr(x) for x in self.columns]
1189 + ["%s=%s" % (k, repr(getattr(self, k))) for k in ["schema"]]
1190 )
1192 def __str__(self) -> str:
1193 return _get_table_key(self.description, self.schema)
1195 def add_is_dependent_on(self, table: Table) -> None:
1196 """Add a 'dependency' for this Table.
1198 This is another Table object which must be created
1199 first before this one can, or dropped after this one.
1201 Usually, dependencies between tables are determined via
1202 ForeignKey objects. However, for other situations that
1203 create dependencies outside of foreign keys (rules, inheriting),
1204 this method can manually establish such a link.
1206 """
1207 self._extra_dependencies.add(table)
1209 def append_column(
1210 self, column: ColumnClause[Any], replace_existing: bool = False
1211 ) -> None:
1212 """Append a :class:`_schema.Column` to this :class:`_schema.Table`.
1214 The "key" of the newly added :class:`_schema.Column`, i.e. the
1215 value of its ``.key`` attribute, will then be available
1216 in the ``.c`` collection of this :class:`_schema.Table`, and the
1217 column definition will be included in any CREATE TABLE, SELECT,
1218 UPDATE, etc. statements generated from this :class:`_schema.Table`
1219 construct.
1221 Note that this does **not** change the definition of the table
1222 as it exists within any underlying database, assuming that
1223 table has already been created in the database. Relational
1224 databases support the addition of columns to existing tables
1225 using the SQL ALTER command, which would need to be
1226 emitted for an already-existing table that doesn't contain
1227 the newly added column.
1229 :param replace_existing: When ``True``, allows replacing existing
1230 columns. When ``False``, the default, an warning will be raised
1231 if a column with the same ``.key`` already exists. A future
1232 version of sqlalchemy will instead rise a warning.
1234 .. versionadded:: 1.4.0
1235 """
1237 try:
1238 column._set_parent_with_dispatch(
1239 self,
1240 allow_replacements=replace_existing,
1241 all_names={c.name: c for c in self.c},
1242 )
1243 except exc.DuplicateColumnError as de:
1244 raise exc.DuplicateColumnError(
1245 f"{de.args[0]} Specify replace_existing=True to "
1246 "Table.append_column() to replace an "
1247 "existing column."
1248 ) from de
1250 def append_constraint(self, constraint: Union[Index, Constraint]) -> None:
1251 """Append a :class:`_schema.Constraint` to this
1252 :class:`_schema.Table`.
1254 This has the effect of the constraint being included in any
1255 future CREATE TABLE statement, assuming specific DDL creation
1256 events have not been associated with the given
1257 :class:`_schema.Constraint` object.
1259 Note that this does **not** produce the constraint within the
1260 relational database automatically, for a table that already exists
1261 in the database. To add a constraint to an
1262 existing relational database table, the SQL ALTER command must
1263 be used. SQLAlchemy also provides the
1264 :class:`.AddConstraint` construct which can produce this SQL when
1265 invoked as an executable clause.
1267 """
1269 constraint._set_parent_with_dispatch(self)
1271 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
1272 metadata = parent
1273 assert isinstance(metadata, MetaData)
1274 metadata._add_table(self.name, self.schema, self)
1275 self.metadata = metadata
1277 def create(self, bind: _CreateDropBind, checkfirst: bool = False) -> None:
1278 """Issue a ``CREATE`` statement for this
1279 :class:`_schema.Table`, using the given
1280 :class:`.Connection` or :class:`.Engine`
1281 for connectivity.
1283 .. seealso::
1285 :meth:`_schema.MetaData.create_all`.
1287 """
1289 bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
1291 def drop(self, bind: _CreateDropBind, checkfirst: bool = False) -> None:
1292 """Issue a ``DROP`` statement for this
1293 :class:`_schema.Table`, using the given
1294 :class:`.Connection` or :class:`.Engine` for connectivity.
1296 .. seealso::
1298 :meth:`_schema.MetaData.drop_all`.
1300 """
1301 bind._run_ddl_visitor(ddl.SchemaDropper, self, checkfirst=checkfirst)
1303 @util.deprecated(
1304 "1.4",
1305 ":meth:`_schema.Table.tometadata` is renamed to "
1306 ":meth:`_schema.Table.to_metadata`",
1307 )
1308 def tometadata(
1309 self,
1310 metadata: MetaData,
1311 schema: Union[str, Literal[SchemaConst.RETAIN_SCHEMA]] = RETAIN_SCHEMA,
1312 referred_schema_fn: Optional[
1313 Callable[
1314 [Table, Optional[str], ForeignKeyConstraint, Optional[str]],
1315 Optional[str],
1316 ]
1317 ] = None,
1318 name: Optional[str] = None,
1319 ) -> Table:
1320 """Return a copy of this :class:`_schema.Table`
1321 associated with a different
1322 :class:`_schema.MetaData`.
1324 See :meth:`_schema.Table.to_metadata` for a full description.
1326 """
1327 return self.to_metadata(
1328 metadata,
1329 schema=schema,
1330 referred_schema_fn=referred_schema_fn,
1331 name=name,
1332 )
1334 def to_metadata(
1335 self,
1336 metadata: MetaData,
1337 schema: Union[str, Literal[SchemaConst.RETAIN_SCHEMA]] = RETAIN_SCHEMA,
1338 referred_schema_fn: Optional[
1339 Callable[
1340 [Table, Optional[str], ForeignKeyConstraint, Optional[str]],
1341 Optional[str],
1342 ]
1343 ] = None,
1344 name: Optional[str] = None,
1345 ) -> Table:
1346 """Return a copy of this :class:`_schema.Table` associated with a
1347 different :class:`_schema.MetaData`.
1349 E.g.::
1351 m1 = MetaData()
1353 user = Table("user", m1, Column("id", Integer, primary_key=True))
1355 m2 = MetaData()
1356 user_copy = user.to_metadata(m2)
1358 .. versionchanged:: 1.4 The :meth:`_schema.Table.to_metadata` function
1359 was renamed from :meth:`_schema.Table.tometadata`.
1362 :param metadata: Target :class:`_schema.MetaData` object,
1363 into which the
1364 new :class:`_schema.Table` object will be created.
1366 :param schema: optional string name indicating the target schema.
1367 Defaults to the special symbol :attr:`.RETAIN_SCHEMA` which indicates
1368 that no change to the schema name should be made in the new
1369 :class:`_schema.Table`. If set to a string name, the new
1370 :class:`_schema.Table`
1371 will have this new name as the ``.schema``. If set to ``None``, the
1372 schema will be set to that of the schema set on the target
1373 :class:`_schema.MetaData`, which is typically ``None`` as well,
1374 unless
1375 set explicitly::
1377 m2 = MetaData(schema="newschema")
1379 # user_copy_one will have "newschema" as the schema name
1380 user_copy_one = user.to_metadata(m2, schema=None)
1382 m3 = MetaData() # schema defaults to None
1384 # user_copy_two will have None as the schema name
1385 user_copy_two = user.to_metadata(m3, schema=None)
1387 :param referred_schema_fn: optional callable which can be supplied
1388 in order to provide for the schema name that should be assigned
1389 to the referenced table of a :class:`_schema.ForeignKeyConstraint`.
1390 The callable accepts this parent :class:`_schema.Table`, the
1391 target schema that we are changing to, the
1392 :class:`_schema.ForeignKeyConstraint` object, and the existing
1393 "target schema" of that constraint. The function should return the
1394 string schema name that should be applied. To reset the schema
1395 to "none", return the symbol :data:`.BLANK_SCHEMA`. To effect no
1396 change, return ``None`` or :data:`.RETAIN_SCHEMA`.
1398 .. versionchanged:: 1.4.33 The ``referred_schema_fn`` function
1399 may return the :data:`.BLANK_SCHEMA` or :data:`.RETAIN_SCHEMA`
1400 symbols.
1402 E.g.::
1404 def referred_schema_fn(table, to_schema, constraint, referred_schema):
1405 if referred_schema == "base_tables":
1406 return referred_schema
1407 else:
1408 return to_schema
1411 new_table = table.to_metadata(
1412 m2, schema="alt_schema", referred_schema_fn=referred_schema_fn
1413 )
1415 :param name: optional string name indicating the target table name.
1416 If not specified or None, the table name is retained. This allows
1417 a :class:`_schema.Table` to be copied to the same
1418 :class:`_schema.MetaData` target
1419 with a new name.
1421 """ # noqa: E501
1422 if name is None:
1423 name = self.name
1425 actual_schema: Optional[str]
1427 if schema is RETAIN_SCHEMA:
1428 actual_schema = self.schema
1429 elif schema is None:
1430 actual_schema = metadata.schema
1431 else:
1432 actual_schema = schema
1433 key = _get_table_key(name, actual_schema)
1434 if key in metadata.tables:
1435 util.warn(
1436 f"Table '{self.description}' already exists within the given "
1437 "MetaData - not copying."
1438 )
1439 return metadata.tables[key]
1441 args = []
1442 for col in self.columns:
1443 args.append(col._copy(schema=actual_schema, _to_metadata=metadata))
1444 table = Table(
1445 name,
1446 metadata,
1447 schema=actual_schema,
1448 comment=self.comment,
1449 *args,
1450 **self.kwargs,
1451 )
1452 for const in self.constraints:
1453 if isinstance(const, ForeignKeyConstraint):
1454 referred_schema = const._referred_schema
1455 if referred_schema_fn:
1456 fk_constraint_schema = referred_schema_fn(
1457 self, actual_schema, const, referred_schema
1458 )
1459 else:
1460 fk_constraint_schema = (
1461 actual_schema
1462 if referred_schema == self.schema
1463 else None
1464 )
1465 table.append_constraint(
1466 const._copy(
1467 schema=fk_constraint_schema, target_table=table
1468 )
1469 )
1470 elif not const._type_bound:
1471 # skip unique constraints that would be generated
1472 # by the 'unique' flag on Column
1473 if const._column_flag:
1474 continue
1476 table.append_constraint(
1477 const._copy(schema=actual_schema, target_table=table)
1478 )
1479 for index in self.indexes:
1480 # skip indexes that would be generated
1481 # by the 'index' flag on Column
1482 if index._column_flag:
1483 continue
1484 Index(
1485 index.name,
1486 unique=index.unique,
1487 *[
1488 _copy_expression(expr, self, table)
1489 for expr in index._table_bound_expressions
1490 ],
1491 _table=table,
1492 **index.kwargs,
1493 )
1494 return self._schema_item_copy(table)
1497class Column(DialectKWArgs, SchemaItem, ColumnClause[_T]):
1498 """Represents a column in a database table."""
1500 __visit_name__ = "column"
1502 inherit_cache = True
1503 key: str
1505 server_default: Optional[FetchedValue]
1507 def __init__(
1508 self,
1509 __name_pos: Optional[
1510 Union[str, _TypeEngineArgument[_T], SchemaEventTarget]
1511 ] = None,
1512 __type_pos: Optional[
1513 Union[_TypeEngineArgument[_T], SchemaEventTarget]
1514 ] = None,
1515 *args: SchemaEventTarget,
1516 name: Optional[str] = None,
1517 type_: Optional[_TypeEngineArgument[_T]] = None,
1518 autoincrement: _AutoIncrementType = "auto",
1519 default: Optional[Any] = _NoArg.NO_ARG,
1520 insert_default: Optional[Any] = _NoArg.NO_ARG,
1521 doc: Optional[str] = None,
1522 key: Optional[str] = None,
1523 index: Optional[bool] = None,
1524 unique: Optional[bool] = None,
1525 info: Optional[_InfoType] = None,
1526 nullable: Optional[
1527 Union[bool, Literal[SchemaConst.NULL_UNSPECIFIED]]
1528 ] = SchemaConst.NULL_UNSPECIFIED,
1529 onupdate: Optional[Any] = None,
1530 primary_key: bool = False,
1531 server_default: Optional[_ServerDefaultArgument] = None,
1532 server_onupdate: Optional[_ServerOnUpdateArgument] = None,
1533 quote: Optional[bool] = None,
1534 system: bool = False,
1535 comment: Optional[str] = None,
1536 insert_sentinel: bool = False,
1537 _omit_from_statements: bool = False,
1538 _proxies: Optional[Any] = None,
1539 **dialect_kwargs: Any,
1540 ):
1541 r"""
1542 Construct a new ``Column`` object.
1544 :param name: The name of this column as represented in the database.
1545 This argument may be the first positional argument, or specified
1546 via keyword.
1548 Names which contain no upper case characters
1549 will be treated as case insensitive names, and will not be quoted
1550 unless they are a reserved word. Names with any number of upper
1551 case characters will be quoted and sent exactly. Note that this
1552 behavior applies even for databases which standardize upper
1553 case names as case insensitive such as Oracle Database.
1555 The name field may be omitted at construction time and applied
1556 later, at any time before the Column is associated with a
1557 :class:`_schema.Table`. This is to support convenient
1558 usage within the :mod:`~sqlalchemy.ext.declarative` extension.
1560 :param type\_: The column's type, indicated using an instance which
1561 subclasses :class:`~sqlalchemy.types.TypeEngine`. If no arguments
1562 are required for the type, the class of the type can be sent
1563 as well, e.g.::
1565 # use a type with arguments
1566 Column("data", String(50))
1568 # use no arguments
1569 Column("level", Integer)
1571 The ``type`` argument may be the second positional argument
1572 or specified by keyword.
1574 If the ``type`` is ``None`` or is omitted, it will first default to
1575 the special type :class:`.NullType`. If and when this
1576 :class:`_schema.Column` is made to refer to another column using
1577 :class:`_schema.ForeignKey` and/or
1578 :class:`_schema.ForeignKeyConstraint`, the type
1579 of the remote-referenced column will be copied to this column as
1580 well, at the moment that the foreign key is resolved against that
1581 remote :class:`_schema.Column` object.
1583 :param \*args: Additional positional arguments include various
1584 :class:`.SchemaItem` derived constructs which will be applied
1585 as options to the column. These include instances of
1586 :class:`.Constraint`, :class:`_schema.ForeignKey`,
1587 :class:`.ColumnDefault`, :class:`.Sequence`, :class:`.Computed`
1588 :class:`.Identity`. In some cases an
1589 equivalent keyword argument is available such as ``server_default``,
1590 ``default`` and ``unique``.
1592 :param autoincrement: Set up "auto increment" semantics for an
1593 **integer primary key column with no foreign key dependencies**
1594 (see later in this docstring for a more specific definition).
1595 This may influence the :term:`DDL` that will be emitted for
1596 this column during a table create, as well as how the column
1597 will be considered when INSERT statements are compiled and
1598 executed.
1600 The default value is the string ``"auto"``,
1601 which indicates that a single-column (i.e. non-composite) primary key
1602 that is of an INTEGER type with no other client-side or server-side
1603 default constructs indicated should receive auto increment semantics
1604 automatically. Other values include ``True`` (force this column to
1605 have auto-increment semantics for a :term:`composite primary key` as
1606 well), ``False`` (this column should never have auto-increment
1607 semantics), and the string ``"ignore_fk"`` (special-case for foreign
1608 key columns, see below).
1610 The term "auto increment semantics" refers both to the kind of DDL
1611 that will be emitted for the column within a CREATE TABLE statement,
1612 when methods such as :meth:`.MetaData.create_all` and
1613 :meth:`.Table.create` are invoked, as well as how the column will be
1614 considered when an INSERT statement is compiled and emitted to the
1615 database:
1617 * **DDL rendering** (i.e. :meth:`.MetaData.create_all`,
1618 :meth:`.Table.create`): When used on a :class:`.Column` that has
1619 no other
1620 default-generating construct associated with it (such as a
1621 :class:`.Sequence` or :class:`.Identity` construct), the parameter
1622 will imply that database-specific keywords such as PostgreSQL
1623 ``SERIAL``, MySQL ``AUTO_INCREMENT``, or ``IDENTITY`` on SQL Server
1624 should also be rendered. Not every database backend has an
1625 "implied" default generator available; for example the Oracle Database
1626 backends always needs an explicit construct such as
1627 :class:`.Identity` to be included with a :class:`.Column` in order
1628 for the DDL rendered to include auto-generating constructs to also
1629 be produced in the database.
1631 * **INSERT semantics** (i.e. when a :func:`_sql.insert` construct is
1632 compiled into a SQL string and is then executed on a database using
1633 :meth:`_engine.Connection.execute` or equivalent): A single-row
1634 INSERT statement will be known to produce a new integer primary key
1635 value automatically for this column, which will be accessible
1636 after the statement is invoked via the
1637 :attr:`.CursorResult.inserted_primary_key` attribute upon the
1638 :class:`_result.Result` object. This also applies towards use of the
1639 ORM when ORM-mapped objects are persisted to the database,
1640 indicating that a new integer primary key will be available to
1641 become part of the :term:`identity key` for that object. This
1642 behavior takes place regardless of what DDL constructs are
1643 associated with the :class:`_schema.Column` and is independent
1644 of the "DDL Rendering" behavior discussed in the previous note
1645 above.
1647 The parameter may be set to ``True`` to indicate that a column which
1648 is part of a composite (i.e. multi-column) primary key should
1649 have autoincrement semantics, though note that only one column
1650 within a primary key may have this setting. It can also
1651 be set to ``True`` to indicate autoincrement semantics on a
1652 column that has a client-side or server-side default configured,
1653 however note that not all dialects can accommodate all styles
1654 of default as an "autoincrement". It can also be
1655 set to ``False`` on a single-column primary key that has a
1656 datatype of INTEGER in order to disable auto increment semantics
1657 for that column.
1659 The setting *only* has an effect for columns which are:
1661 * Integer derived (i.e. INT, SMALLINT, BIGINT).
1663 * Part of the primary key
1665 * Not referring to another column via :class:`_schema.ForeignKey`,
1666 unless
1667 the value is specified as ``'ignore_fk'``::
1669 # turn on autoincrement for this column despite
1670 # the ForeignKey()
1671 Column(
1672 "id",
1673 ForeignKey("other.id"),
1674 primary_key=True,
1675 autoincrement="ignore_fk",
1676 )
1678 It is typically not desirable to have "autoincrement" enabled on a
1679 column that refers to another via foreign key, as such a column is
1680 required to refer to a value that originates from elsewhere.
1682 The setting has these effects on columns that meet the
1683 above criteria:
1685 * DDL issued for the column, if the column does not already include
1686 a default generating construct supported by the backend such as
1687 :class:`.Identity`, will include database-specific
1688 keywords intended to signify this column as an
1689 "autoincrement" column for specific backends. Behavior for
1690 primary SQLAlchemy dialects includes:
1692 * AUTO INCREMENT on MySQL and MariaDB
1693 * SERIAL on PostgreSQL
1694 * IDENTITY on MS-SQL - this occurs even without the
1695 :class:`.Identity` construct as the
1696 :paramref:`.Column.autoincrement` parameter pre-dates this
1697 construct.
1698 * SQLite - SQLite integer primary key columns are implicitly
1699 "auto incrementing" and no additional keywords are rendered;
1700 to render the special SQLite keyword ``AUTOINCREMENT``
1701 is not included as this is unnecessary and not recommended
1702 by the database vendor. See the section
1703 :ref:`sqlite_autoincrement` for more background.
1704 * Oracle Database - The Oracle Database dialects have no default "autoincrement"
1705 feature available at this time, instead the :class:`.Identity`
1706 construct is recommended to achieve this (the :class:`.Sequence`
1707 construct may also be used).
1708 * Third-party dialects - consult those dialects' documentation
1709 for details on their specific behaviors.
1711 * When a single-row :func:`_sql.insert` construct is compiled and
1712 executed, which does not set the :meth:`_sql.Insert.inline`
1713 modifier, newly generated primary key values for this column
1714 will be automatically retrieved upon statement execution
1715 using a method specific to the database driver in use:
1717 * MySQL, SQLite - calling upon ``cursor.lastrowid()``
1718 (see
1719 `https://www.python.org/dev/peps/pep-0249/#lastrowid
1720 <https://www.python.org/dev/peps/pep-0249/#lastrowid>`_)
1721 * PostgreSQL, SQL Server, Oracle Database - use RETURNING or an equivalent
1722 construct when rendering an INSERT statement, and then retrieving
1723 the newly generated primary key values after execution
1724 * PostgreSQL, Oracle Database for :class:`_schema.Table` objects that
1725 set :paramref:`_schema.Table.implicit_returning` to False -
1726 for a :class:`.Sequence` only, the :class:`.Sequence` is invoked
1727 explicitly before the INSERT statement takes place so that the
1728 newly generated primary key value is available to the client
1729 * SQL Server for :class:`_schema.Table` objects that
1730 set :paramref:`_schema.Table.implicit_returning` to False -
1731 the ``SELECT scope_identity()`` construct is used after the
1732 INSERT statement is invoked to retrieve the newly generated
1733 primary key value.
1734 * Third-party dialects - consult those dialects' documentation
1735 for details on their specific behaviors.
1737 * For multiple-row :func:`_sql.insert` constructs invoked with
1738 a list of parameters (i.e. "executemany" semantics), primary-key
1739 retrieving behaviors are generally disabled, however there may
1740 be special APIs that may be used to retrieve lists of new
1741 primary key values for an "executemany", such as the psycopg2
1742 "fast insertmany" feature. Such features are very new and
1743 may not yet be well covered in documentation.
1745 :param default: A scalar, Python callable, or
1746 :class:`_expression.ColumnElement` expression representing the
1747 *default value* for this column, which will be invoked upon insert
1748 if this column is otherwise not specified in the VALUES clause of
1749 the insert. This is a shortcut to using :class:`.ColumnDefault` as
1750 a positional argument; see that class for full detail on the
1751 structure of the argument.
1753 Contrast this argument to
1754 :paramref:`_schema.Column.server_default`
1755 which creates a default generator on the database side.
1757 .. seealso::
1759 :ref:`metadata_defaults_toplevel`
1761 :param insert_default: An alias of :paramref:`.Column.default`
1762 for compatibility with :func:`_orm.mapped_column`.
1764 .. versionadded: 2.0.31
1766 :param doc: optional String that can be used by the ORM or similar
1767 to document attributes on the Python side. This attribute does
1768 **not** render SQL comments; use the
1769 :paramref:`_schema.Column.comment`
1770 parameter for this purpose.
1772 :param key: An optional string identifier which will identify this
1773 ``Column`` object on the :class:`_schema.Table`.
1774 When a key is provided,
1775 this is the only identifier referencing the ``Column`` within the
1776 application, including ORM attribute mapping; the ``name`` field
1777 is used only when rendering SQL.
1779 :param index: When ``True``, indicates that a :class:`_schema.Index`
1780 construct will be automatically generated for this
1781 :class:`_schema.Column`, which will result in a "CREATE INDEX"
1782 statement being emitted for the :class:`_schema.Table` when the DDL
1783 create operation is invoked.
1785 Using this flag is equivalent to making use of the
1786 :class:`_schema.Index` construct explicitly at the level of the
1787 :class:`_schema.Table` construct itself::
1789 Table(
1790 "some_table",
1791 metadata,
1792 Column("x", Integer),
1793 Index("ix_some_table_x", "x"),
1794 )
1796 To add the :paramref:`_schema.Index.unique` flag to the
1797 :class:`_schema.Index`, set both the
1798 :paramref:`_schema.Column.unique` and
1799 :paramref:`_schema.Column.index` flags to True simultaneously,
1800 which will have the effect of rendering the "CREATE UNIQUE INDEX"
1801 DDL instruction instead of "CREATE INDEX".
1803 The name of the index is generated using the
1804 :ref:`default naming convention <constraint_default_naming_convention>`
1805 which for the :class:`_schema.Index` construct is of the form
1806 ``ix_<tablename>_<columnname>``.
1808 As this flag is intended only as a convenience for the common case
1809 of adding a single-column, default configured index to a table
1810 definition, explicit use of the :class:`_schema.Index` construct
1811 should be preferred for most use cases, including composite indexes
1812 that encompass more than one column, indexes with SQL expressions
1813 or ordering, backend-specific index configuration options, and
1814 indexes that use a specific name.
1816 .. note:: the :attr:`_schema.Column.index` attribute on
1817 :class:`_schema.Column`
1818 **does not indicate** if this column is indexed or not, only
1819 if this flag was explicitly set here. To view indexes on
1820 a column, view the :attr:`_schema.Table.indexes` collection
1821 or use :meth:`_reflection.Inspector.get_indexes`.
1823 .. seealso::
1825 :ref:`schema_indexes`
1827 :ref:`constraint_naming_conventions`
1829 :paramref:`_schema.Column.unique`
1831 :param info: Optional data dictionary which will be populated into the
1832 :attr:`.SchemaItem.info` attribute of this object.
1834 :param nullable: When set to ``False``, will cause the "NOT NULL"
1835 phrase to be added when generating DDL for the column. When
1836 ``True``, will normally generate nothing (in SQL this defaults to
1837 "NULL"), except in some very specific backend-specific edge cases
1838 where "NULL" may render explicitly.
1839 Defaults to ``True`` unless :paramref:`_schema.Column.primary_key`
1840 is also ``True`` or the column specifies a :class:`_sql.Identity`,
1841 in which case it defaults to ``False``.
1842 This parameter is only used when issuing CREATE TABLE statements.
1844 .. note::
1846 When the column specifies a :class:`_sql.Identity` this
1847 parameter is in general ignored by the DDL compiler. The
1848 PostgreSQL database allows nullable identity column by
1849 setting this parameter to ``True`` explicitly.
1851 :param onupdate: A scalar, Python callable, or
1852 :class:`~sqlalchemy.sql.expression.ClauseElement` representing a
1853 default value to be applied to the column within UPDATE
1854 statements, which will be invoked upon update if this column is not
1855 present in the SET clause of the update. This is a shortcut to
1856 using :class:`.ColumnDefault` as a positional argument with
1857 ``for_update=True``.
1859 .. seealso::
1861 :ref:`metadata_defaults` - complete discussion of onupdate
1863 :param primary_key: If ``True``, marks this column as a primary key
1864 column. Multiple columns can have this flag set to specify
1865 composite primary keys. As an alternative, the primary key of a
1866 :class:`_schema.Table` can be specified via an explicit
1867 :class:`.PrimaryKeyConstraint` object.
1869 :param server_default: A :class:`.FetchedValue` instance, str, Unicode
1870 or :func:`~sqlalchemy.sql.expression.text` construct representing
1871 the DDL DEFAULT value for the column.
1873 String types will be emitted as-is, surrounded by single quotes::
1875 Column("x", Text, server_default="val")
1877 will render:
1879 .. sourcecode:: sql
1881 x TEXT DEFAULT 'val'
1883 A :func:`~sqlalchemy.sql.expression.text` expression will be
1884 rendered as-is, without quotes::
1886 Column("y", DateTime, server_default=text("NOW()"))
1888 will render:
1890 .. sourcecode:: sql
1892 y DATETIME DEFAULT NOW()
1894 Strings and text() will be converted into a
1895 :class:`.DefaultClause` object upon initialization.
1897 This parameter can also accept complex combinations of contextually
1898 valid SQLAlchemy expressions or constructs::
1900 from sqlalchemy import create_engine
1901 from sqlalchemy import Table, Column, MetaData, ARRAY, Text
1902 from sqlalchemy.dialects.postgresql import array
1904 engine = create_engine(
1905 "postgresql+psycopg2://scott:tiger@localhost/mydatabase"
1906 )
1907 metadata_obj = MetaData()
1908 tbl = Table(
1909 "foo",
1910 metadata_obj,
1911 Column(
1912 "bar", ARRAY(Text), server_default=array(["biz", "bang", "bash"])
1913 ),
1914 )
1915 metadata_obj.create_all(engine)
1917 The above results in a table created with the following SQL:
1919 .. sourcecode:: sql
1921 CREATE TABLE foo (
1922 bar TEXT[] DEFAULT ARRAY['biz', 'bang', 'bash']
1923 )
1925 Use :class:`.FetchedValue` to indicate that an already-existing
1926 column will generate a default value on the database side which
1927 will be available to SQLAlchemy for post-fetch after inserts. This
1928 construct does not specify any DDL and the implementation is left
1929 to the database, such as via a trigger.
1931 .. seealso::
1933 :ref:`server_defaults` - complete discussion of server side
1934 defaults
1936 :param server_onupdate: A :class:`.FetchedValue` instance
1937 representing a database-side default generation function,
1938 such as a trigger. This
1939 indicates to SQLAlchemy that a newly generated value will be
1940 available after updates. This construct does not actually
1941 implement any kind of generation function within the database,
1942 which instead must be specified separately.
1945 .. warning:: This directive **does not** currently produce MySQL's
1946 "ON UPDATE CURRENT_TIMESTAMP()" clause. See
1947 :ref:`mysql_timestamp_onupdate` for background on how to
1948 produce this clause.
1950 .. seealso::
1952 :ref:`triggered_columns`
1954 :param quote: Force quoting of this column's name on or off,
1955 corresponding to ``True`` or ``False``. When left at its default
1956 of ``None``, the column identifier will be quoted according to
1957 whether the name is case sensitive (identifiers with at least one
1958 upper case character are treated as case sensitive), or if it's a
1959 reserved word. This flag is only needed to force quoting of a
1960 reserved word which is not known by the SQLAlchemy dialect.
1962 :param unique: When ``True``, and the :paramref:`_schema.Column.index`
1963 parameter is left at its default value of ``False``,
1964 indicates that a :class:`_schema.UniqueConstraint`
1965 construct will be automatically generated for this
1966 :class:`_schema.Column`,
1967 which will result in a "UNIQUE CONSTRAINT" clause referring
1968 to this column being included
1969 in the ``CREATE TABLE`` statement emitted, when the DDL create
1970 operation for the :class:`_schema.Table` object is invoked.
1972 When this flag is ``True`` while the
1973 :paramref:`_schema.Column.index` parameter is simultaneously
1974 set to ``True``, the effect instead is that a
1975 :class:`_schema.Index` construct which includes the
1976 :paramref:`_schema.Index.unique` parameter set to ``True``
1977 is generated. See the documentation for
1978 :paramref:`_schema.Column.index` for additional detail.
1980 Using this flag is equivalent to making use of the
1981 :class:`_schema.UniqueConstraint` construct explicitly at the
1982 level of the :class:`_schema.Table` construct itself::
1984 Table("some_table", metadata, Column("x", Integer), UniqueConstraint("x"))
1986 The :paramref:`_schema.UniqueConstraint.name` parameter
1987 of the unique constraint object is left at its default value
1988 of ``None``; in the absence of a :ref:`naming convention <constraint_naming_conventions>`
1989 for the enclosing :class:`_schema.MetaData`, the UNIQUE CONSTRAINT
1990 construct will be emitted as unnamed, which typically invokes
1991 a database-specific naming convention to take place.
1993 As this flag is intended only as a convenience for the common case
1994 of adding a single-column, default configured unique constraint to a table
1995 definition, explicit use of the :class:`_schema.UniqueConstraint` construct
1996 should be preferred for most use cases, including composite constraints
1997 that encompass more than one column, backend-specific index configuration options, and
1998 constraints that use a specific name.
2000 .. note:: the :attr:`_schema.Column.unique` attribute on
2001 :class:`_schema.Column`
2002 **does not indicate** if this column has a unique constraint or
2003 not, only if this flag was explicitly set here. To view
2004 indexes and unique constraints that may involve this column,
2005 view the
2006 :attr:`_schema.Table.indexes` and/or
2007 :attr:`_schema.Table.constraints` collections or use
2008 :meth:`_reflection.Inspector.get_indexes` and/or
2009 :meth:`_reflection.Inspector.get_unique_constraints`
2011 .. seealso::
2013 :ref:`schema_unique_constraint`
2015 :ref:`constraint_naming_conventions`
2017 :paramref:`_schema.Column.index`
2019 :param system: When ``True``, indicates this is a "system" column,
2020 that is a column which is automatically made available by the
2021 database, and should not be included in the columns list for a
2022 ``CREATE TABLE`` statement.
2024 For more elaborate scenarios where columns should be
2025 conditionally rendered differently on different backends,
2026 consider custom compilation rules for :class:`.CreateColumn`.
2028 :param comment: Optional string that will render an SQL comment on
2029 table creation.
2031 .. versionadded:: 1.2 Added the
2032 :paramref:`_schema.Column.comment`
2033 parameter to :class:`_schema.Column`.
2035 :param insert_sentinel: Marks this :class:`_schema.Column` as an
2036 :term:`insert sentinel` used for optimizing the performance of the
2037 :term:`insertmanyvalues` feature for tables that don't
2038 otherwise have qualifying primary key configurations.
2040 .. versionadded:: 2.0.10
2042 .. seealso::
2044 :func:`_schema.insert_sentinel` - all in one helper for declaring
2045 sentinel columns
2047 :ref:`engine_insertmanyvalues`
2049 :ref:`engine_insertmanyvalues_sentinel_columns`
2052 """ # noqa: E501, RST201, RST202
2054 l_args = [__name_pos, __type_pos] + list(args)
2055 del args
2057 if l_args:
2058 if isinstance(l_args[0], str):
2059 if name is not None:
2060 raise exc.ArgumentError(
2061 "May not pass name positionally and as a keyword."
2062 )
2063 name = l_args.pop(0) # type: ignore
2064 elif l_args[0] is None:
2065 l_args.pop(0)
2066 if l_args:
2067 coltype = l_args[0]
2069 if hasattr(coltype, "_sqla_type"):
2070 if type_ is not None:
2071 raise exc.ArgumentError(
2072 "May not pass type_ positionally and as a keyword."
2073 )
2074 type_ = l_args.pop(0) # type: ignore
2075 elif l_args[0] is None:
2076 l_args.pop(0)
2078 if name is not None:
2079 name = quoted_name(name, quote)
2080 elif quote is not None:
2081 raise exc.ArgumentError(
2082 "Explicit 'name' is required when sending 'quote' argument"
2083 )
2085 # name = None is expected to be an interim state
2086 # note this use case is legacy now that ORM declarative has a
2087 # dedicated "column" construct local to the ORM
2088 super().__init__(name, type_) # type: ignore
2090 self.key = key if key is not None else name # type: ignore
2091 self.primary_key = primary_key
2092 self._insert_sentinel = insert_sentinel
2093 self._omit_from_statements = _omit_from_statements
2094 self._user_defined_nullable = udn = nullable
2095 if udn is not NULL_UNSPECIFIED:
2096 self.nullable = udn
2097 else:
2098 self.nullable = not primary_key
2100 # these default to None because .index and .unique is *not*
2101 # an informational flag about Column - there can still be an
2102 # Index or UniqueConstraint referring to this Column.
2103 self.index = index
2104 self.unique = unique
2106 self.system = system
2107 self.doc = doc
2108 self.autoincrement: _AutoIncrementType = autoincrement
2109 self.constraints = set()
2110 self.foreign_keys = set()
2111 self.comment = comment
2112 self.computed = None
2113 self.identity = None
2115 # check if this Column is proxying another column
2117 if _proxies is not None:
2118 self._proxies = _proxies
2119 else:
2120 # otherwise, add DDL-related events
2121 self._set_type(self.type)
2123 if insert_default is not _NoArg.NO_ARG:
2124 resolved_default = insert_default
2125 elif default is not _NoArg.NO_ARG:
2126 resolved_default = default
2127 else:
2128 resolved_default = None
2130 if resolved_default is not None:
2131 if not isinstance(resolved_default, (ColumnDefault, Sequence)):
2132 resolved_default = ColumnDefault(resolved_default)
2134 self.default = resolved_default
2135 l_args.append(resolved_default)
2136 else:
2137 self.default = None
2139 if onupdate is not None:
2140 if not isinstance(onupdate, (ColumnDefault, Sequence)):
2141 onupdate = ColumnDefault(onupdate, for_update=True)
2143 self.onupdate = onupdate
2144 l_args.append(onupdate)
2145 else:
2146 self.onupdate = None
2148 if server_default is not None:
2149 if isinstance(server_default, FetchedValue):
2150 server_default = server_default._as_for_update(False)
2151 l_args.append(server_default)
2152 else:
2153 server_default = DefaultClause(server_default)
2154 l_args.append(server_default)
2155 self.server_default = server_default
2157 if server_onupdate is not None:
2158 if isinstance(server_onupdate, FetchedValue):
2159 server_onupdate = server_onupdate._as_for_update(True)
2160 l_args.append(server_onupdate)
2161 else:
2162 server_onupdate = DefaultClause(
2163 server_onupdate, for_update=True
2164 )
2165 l_args.append(server_onupdate)
2166 self.server_onupdate = server_onupdate
2168 self._init_items(*cast(_typing_Sequence[SchemaItem], l_args))
2170 util.set_creation_order(self)
2172 if info is not None:
2173 self.info = info
2175 self._extra_kwargs(**dialect_kwargs)
2177 table: Table
2179 constraints: Set[Constraint]
2181 foreign_keys: Set[ForeignKey]
2182 """A collection of all :class:`_schema.ForeignKey` marker objects
2183 associated with this :class:`_schema.Column`.
2185 Each object is a member of a :class:`_schema.Table`-wide
2186 :class:`_schema.ForeignKeyConstraint`.
2188 .. seealso::
2190 :attr:`_schema.Table.foreign_keys`
2192 """
2194 index: Optional[bool]
2195 """The value of the :paramref:`_schema.Column.index` parameter.
2197 Does not indicate if this :class:`_schema.Column` is actually indexed
2198 or not; use :attr:`_schema.Table.indexes`.
2200 .. seealso::
2202 :attr:`_schema.Table.indexes`
2203 """
2205 unique: Optional[bool]
2206 """The value of the :paramref:`_schema.Column.unique` parameter.
2208 Does not indicate if this :class:`_schema.Column` is actually subject to
2209 a unique constraint or not; use :attr:`_schema.Table.indexes` and
2210 :attr:`_schema.Table.constraints`.
2212 .. seealso::
2214 :attr:`_schema.Table.indexes`
2216 :attr:`_schema.Table.constraints`.
2218 """
2220 computed: Optional[Computed]
2222 identity: Optional[Identity]
2224 def _set_type(self, type_: TypeEngine[Any]) -> None:
2225 assert self.type._isnull or type_ is self.type
2227 self.type = type_
2228 if isinstance(self.type, SchemaEventTarget):
2229 self.type._set_parent_with_dispatch(self)
2230 for impl in self.type._variant_mapping.values():
2231 if isinstance(impl, SchemaEventTarget):
2232 impl._set_parent_with_dispatch(self)
2234 @HasMemoized.memoized_attribute
2235 def _default_description_tuple(self) -> _DefaultDescriptionTuple:
2236 """used by default.py -> _process_execute_defaults()"""
2238 return _DefaultDescriptionTuple._from_column_default(self.default)
2240 @HasMemoized.memoized_attribute
2241 def _onupdate_description_tuple(self) -> _DefaultDescriptionTuple:
2242 """used by default.py -> _process_execute_defaults()"""
2243 return _DefaultDescriptionTuple._from_column_default(self.onupdate)
2245 @util.memoized_property
2246 def _gen_static_annotations_cache_key(self) -> bool:
2247 """special attribute used by cache key gen, if true, we will
2248 use a static cache key for the annotations dictionary, else we
2249 will generate a new cache key for annotations each time.
2251 Added for #8790
2253 """
2254 return self.table is not None and self.table._is_table
2256 def _extra_kwargs(self, **kwargs: Any) -> None:
2257 self._validate_dialect_kwargs(kwargs)
2259 def __str__(self) -> str:
2260 if self.name is None:
2261 return "(no name)"
2262 elif self.table is not None:
2263 if self.table.named_with_column:
2264 return self.table.description + "." + self.description
2265 else:
2266 return self.description
2267 else:
2268 return self.description
2270 def references(self, column: Column[Any]) -> bool:
2271 """Return True if this Column references the given column via foreign
2272 key."""
2274 for fk in self.foreign_keys:
2275 if fk.column.proxy_set.intersection(column.proxy_set):
2276 return True
2277 else:
2278 return False
2280 def append_foreign_key(self, fk: ForeignKey) -> None:
2281 fk._set_parent_with_dispatch(self)
2283 def __repr__(self) -> str:
2284 kwarg = []
2285 if self.key != self.name:
2286 kwarg.append("key")
2287 if self.primary_key:
2288 kwarg.append("primary_key")
2289 if not self.nullable:
2290 kwarg.append("nullable")
2291 if self.onupdate:
2292 kwarg.append("onupdate")
2293 if self.default:
2294 kwarg.append("default")
2295 if self.server_default:
2296 kwarg.append("server_default")
2297 if self.comment:
2298 kwarg.append("comment")
2299 return "Column(%s)" % ", ".join(
2300 [repr(self.name)]
2301 + [repr(self.type)]
2302 + [repr(x) for x in self.foreign_keys if x is not None]
2303 + [repr(x) for x in self.constraints]
2304 + [
2305 (
2306 self.table is not None
2307 and "table=<%s>" % self.table.description
2308 or "table=None"
2309 )
2310 ]
2311 + ["%s=%s" % (k, repr(getattr(self, k))) for k in kwarg]
2312 )
2314 def _set_parent( # type: ignore[override]
2315 self,
2316 parent: SchemaEventTarget,
2317 *,
2318 all_names: Dict[str, Column[Any]],
2319 allow_replacements: bool,
2320 **kw: Any,
2321 ) -> None:
2322 table = parent
2323 assert isinstance(table, Table)
2324 if not self.name:
2325 raise exc.ArgumentError(
2326 "Column must be constructed with a non-blank name or "
2327 "assign a non-blank .name before adding to a Table."
2328 )
2330 self._reset_memoizations()
2332 if self.key is None:
2333 self.key = self.name
2335 existing = getattr(self, "table", None)
2336 if existing is not None and existing is not table:
2337 raise exc.ArgumentError(
2338 f"Column object '{self.key}' already "
2339 f"assigned to Table '{existing.description}'"
2340 )
2342 extra_remove = None
2343 existing_col = None
2344 conflicts_on = ""
2346 if self.key in table._columns:
2347 existing_col = table._columns[self.key]
2348 if self.key == self.name:
2349 conflicts_on = "name"
2350 else:
2351 conflicts_on = "key"
2352 elif self.name in all_names:
2353 existing_col = all_names[self.name]
2354 extra_remove = {existing_col}
2355 conflicts_on = "name"
2357 if existing_col is not None:
2358 if existing_col is not self:
2359 if not allow_replacements:
2360 raise exc.DuplicateColumnError(
2361 f"A column with {conflicts_on} " f"""'{
2362 self.key if conflicts_on == 'key' else self.name
2363 }' """ f"is already present in table '{table.name}'."
2364 )
2365 for fk in existing_col.foreign_keys:
2366 table.foreign_keys.remove(fk)
2367 if fk.constraint in table.constraints:
2368 # this might have been removed
2369 # already, if it's a composite constraint
2370 # and more than one col being replaced
2371 table.constraints.remove(fk.constraint)
2373 if extra_remove and existing_col is not None and self.key == self.name:
2374 util.warn(
2375 f'Column with user-specified key "{existing_col.key}" is '
2376 "being replaced with "
2377 f'plain named column "{self.name}", '
2378 f'key "{existing_col.key}" is being removed. If this is a '
2379 "reflection operation, specify autoload_replace=False to "
2380 "prevent this replacement."
2381 )
2382 table._columns.replace(self, extra_remove=extra_remove)
2383 all_names[self.name] = self
2384 self.table = table
2386 if self._insert_sentinel:
2387 if self.table._sentinel_column is not None:
2388 raise exc.ArgumentError(
2389 "a Table may have only one explicit sentinel column"
2390 )
2391 self.table._sentinel_column = self
2393 if self.primary_key:
2394 table.primary_key._replace(self)
2395 elif self.key in table.primary_key:
2396 raise exc.ArgumentError(
2397 f"Trying to redefine primary-key column '{self.key}' as a "
2398 f"non-primary-key column on table '{table.fullname}'"
2399 )
2401 if self.index:
2402 if isinstance(self.index, str):
2403 raise exc.ArgumentError(
2404 "The 'index' keyword argument on Column is boolean only. "
2405 "To create indexes with a specific name, create an "
2406 "explicit Index object external to the Table."
2407 )
2408 table.append_constraint(
2409 Index(
2410 None, self.key, unique=bool(self.unique), _column_flag=True
2411 )
2412 )
2414 elif self.unique:
2415 if isinstance(self.unique, str):
2416 raise exc.ArgumentError(
2417 "The 'unique' keyword argument on Column is boolean "
2418 "only. To create unique constraints or indexes with a "
2419 "specific name, append an explicit UniqueConstraint to "
2420 "the Table's list of elements, or create an explicit "
2421 "Index object external to the Table."
2422 )
2423 table.append_constraint(
2424 UniqueConstraint(self.key, _column_flag=True)
2425 )
2427 self._setup_on_memoized_fks(lambda fk: fk._set_remote_table(table))
2429 if self.identity and (
2430 isinstance(self.default, Sequence)
2431 or isinstance(self.onupdate, Sequence)
2432 ):
2433 raise exc.ArgumentError(
2434 "An column cannot specify both Identity and Sequence."
2435 )
2437 def _setup_on_memoized_fks(self, fn: Callable[..., Any]) -> None:
2438 fk_keys = [
2439 ((self.table.key, self.key), False),
2440 ((self.table.key, self.name), True),
2441 ]
2442 for fk_key, link_to_name in fk_keys:
2443 if fk_key in self.table.metadata._fk_memos:
2444 for fk in self.table.metadata._fk_memos[fk_key]:
2445 if fk.link_to_name is link_to_name:
2446 fn(fk)
2448 def _on_table_attach(self, fn: Callable[..., Any]) -> None:
2449 if self.table is not None:
2450 fn(self, self.table)
2451 else:
2452 event.listen(self, "after_parent_attach", fn)
2454 @util.deprecated(
2455 "1.4",
2456 "The :meth:`_schema.Column.copy` method is deprecated "
2457 "and will be removed in a future release.",
2458 )
2459 def copy(self, **kw: Any) -> Column[Any]:
2460 return self._copy(**kw)
2462 def _copy(self, **kw: Any) -> Column[Any]:
2463 """Create a copy of this ``Column``, uninitialized.
2465 This is used in :meth:`_schema.Table.to_metadata` and by the ORM.
2467 """
2469 # Constraint objects plus non-constraint-bound ForeignKey objects
2470 args: List[SchemaItem] = [
2471 c._copy(**kw) for c in self.constraints if not c._type_bound
2472 ] + [c._copy(**kw) for c in self.foreign_keys if not c.constraint]
2474 # ticket #5276
2475 column_kwargs = {}
2476 for dialect_name in self.dialect_options:
2477 dialect_options = self.dialect_options[dialect_name]._non_defaults
2478 for (
2479 dialect_option_key,
2480 dialect_option_value,
2481 ) in dialect_options.items():
2482 column_kwargs[dialect_name + "_" + dialect_option_key] = (
2483 dialect_option_value
2484 )
2486 server_default = self.server_default
2487 server_onupdate = self.server_onupdate
2488 if isinstance(server_default, (Computed, Identity)):
2489 # TODO: likely should be copied in all cases
2490 # TODO: if a Sequence, we would need to transfer the Sequence
2491 # .metadata as well
2492 args.append(server_default._copy(**kw))
2493 server_default = server_onupdate = None
2495 type_ = self.type
2496 if isinstance(type_, SchemaEventTarget):
2497 type_ = type_.copy(**kw)
2499 # TODO: DefaultGenerator is not copied here! it's just used again
2500 # with _set_parent() pointing to the old column. see the new
2501 # use of _copy() in the new _merge() method
2503 c = self._constructor(
2504 name=self.name,
2505 type_=type_,
2506 key=self.key,
2507 primary_key=self.primary_key,
2508 unique=self.unique,
2509 system=self.system,
2510 # quote=self.quote, # disabled 2013-08-27 (commit 031ef080)
2511 index=self.index,
2512 autoincrement=self.autoincrement,
2513 default=self.default,
2514 server_default=server_default,
2515 onupdate=self.onupdate,
2516 server_onupdate=server_onupdate,
2517 doc=self.doc,
2518 comment=self.comment,
2519 _omit_from_statements=self._omit_from_statements,
2520 insert_sentinel=self._insert_sentinel,
2521 *args,
2522 **column_kwargs,
2523 )
2525 # copy the state of "nullable" exactly, to accommodate for
2526 # ORM flipping the .nullable flag directly
2527 c.nullable = self.nullable
2528 c._user_defined_nullable = self._user_defined_nullable
2530 return self._schema_item_copy(c)
2532 def _merge(self, other: Column[Any]) -> None:
2533 """merge the elements of another column into this one.
2535 this is used by ORM pep-593 merge and will likely need a lot
2536 of fixes.
2539 """
2541 if self.primary_key:
2542 other.primary_key = True
2544 if self.autoincrement != "auto" and other.autoincrement == "auto":
2545 other.autoincrement = self.autoincrement
2547 if self.system:
2548 other.system = self.system
2550 if self.info:
2551 other.info.update(self.info)
2553 type_ = self.type
2554 if not type_._isnull and other.type._isnull:
2555 if isinstance(type_, SchemaEventTarget):
2556 type_ = type_.copy()
2558 other.type = type_
2560 if isinstance(type_, SchemaEventTarget):
2561 type_._set_parent_with_dispatch(other)
2563 for impl in type_._variant_mapping.values():
2564 if isinstance(impl, SchemaEventTarget):
2565 impl._set_parent_with_dispatch(other)
2567 if (
2568 self._user_defined_nullable is not NULL_UNSPECIFIED
2569 and other._user_defined_nullable is NULL_UNSPECIFIED
2570 ):
2571 other.nullable = self.nullable
2572 other._user_defined_nullable = self._user_defined_nullable
2574 if self.default is not None and other.default is None:
2575 new_default = self.default._copy()
2576 new_default._set_parent(other)
2578 if self.server_default and other.server_default is None:
2579 new_server_default = self.server_default
2580 if isinstance(new_server_default, FetchedValue):
2581 new_server_default = new_server_default._copy()
2582 new_server_default._set_parent(other)
2583 else:
2584 other.server_default = new_server_default
2586 if self.server_onupdate and other.server_onupdate is None:
2587 new_server_onupdate = self.server_onupdate
2588 new_server_onupdate = new_server_onupdate._copy()
2589 new_server_onupdate._set_parent(other)
2591 if self.onupdate and other.onupdate is None:
2592 new_onupdate = self.onupdate._copy()
2593 new_onupdate._set_parent(other)
2595 if self.index in (True, False) and other.index is None:
2596 other.index = self.index
2598 if self.unique in (True, False) and other.unique is None:
2599 other.unique = self.unique
2601 if self.doc and other.doc is None:
2602 other.doc = self.doc
2604 if self.comment and other.comment is None:
2605 other.comment = self.comment
2607 for const in self.constraints:
2608 if not const._type_bound:
2609 new_const = const._copy()
2610 new_const._set_parent(other)
2612 for fk in self.foreign_keys:
2613 if not fk.constraint:
2614 new_fk = fk._copy()
2615 new_fk._set_parent(other)
2617 def _make_proxy(
2618 self,
2619 selectable: FromClause,
2620 primary_key: ColumnSet,
2621 foreign_keys: Set[KeyedColumnElement[Any]],
2622 name: Optional[str] = None,
2623 key: Optional[str] = None,
2624 name_is_truncatable: bool = False,
2625 compound_select_cols: Optional[
2626 _typing_Sequence[ColumnElement[Any]]
2627 ] = None,
2628 **kw: Any,
2629 ) -> Tuple[str, ColumnClause[_T]]:
2630 """Create a *proxy* for this column.
2632 This is a copy of this ``Column`` referenced by a different parent
2633 (such as an alias or select statement). The column should
2634 be used only in select scenarios, as its full DDL/default
2635 information is not transferred.
2637 """
2639 fk = [
2640 ForeignKey(
2641 col if col is not None else f._colspec,
2642 _unresolvable=col is None,
2643 _constraint=f.constraint,
2644 )
2645 for f, col in [
2646 (fk, fk._resolve_column(raiseerr=False))
2647 for fk in self.foreign_keys
2648 ]
2649 ]
2651 if name is None and self.name is None:
2652 raise exc.InvalidRequestError(
2653 "Cannot initialize a sub-selectable"
2654 " with this Column object until its 'name' has "
2655 "been assigned."
2656 )
2657 try:
2658 c = self._constructor(
2659 (
2660 coercions.expect(
2661 roles.TruncatedLabelRole, name if name else self.name
2662 )
2663 if name_is_truncatable
2664 else (name or self.name)
2665 ),
2666 self.type,
2667 # this may actually be ._proxy_key when the key is incoming
2668 key=key if key else name if name else self.key,
2669 primary_key=self.primary_key,
2670 nullable=self.nullable,
2671 _proxies=(
2672 list(compound_select_cols)
2673 if compound_select_cols
2674 else [self]
2675 ),
2676 *fk,
2677 )
2678 except TypeError as err:
2679 raise TypeError(
2680 "Could not create a copy of this %r object. "
2681 "Ensure the class includes a _constructor() "
2682 "attribute or method which accepts the "
2683 "standard Column constructor arguments, or "
2684 "references the Column class itself." % self.__class__
2685 ) from err
2687 c.table = selectable
2688 c._propagate_attrs = selectable._propagate_attrs
2689 if selectable._is_clone_of is not None:
2690 c._is_clone_of = selectable._is_clone_of.columns.get(c.key)
2692 if self.primary_key:
2693 primary_key.add(c)
2695 if fk:
2696 foreign_keys.update(fk) # type: ignore
2698 return c.key, c
2701def insert_sentinel(
2702 name: Optional[str] = None,
2703 type_: Optional[_TypeEngineArgument[_T]] = None,
2704 *,
2705 default: Optional[Any] = None,
2706 omit_from_statements: bool = True,
2707) -> Column[Any]:
2708 """Provides a surrogate :class:`_schema.Column` that will act as a
2709 dedicated insert :term:`sentinel` column, allowing efficient bulk
2710 inserts with deterministic RETURNING sorting for tables that
2711 don't otherwise have qualifying primary key configurations.
2713 Adding this column to a :class:`.Table` object requires that a
2714 corresponding database table actually has this column present, so if adding
2715 it to an existing model, existing database tables would need to be migrated
2716 (e.g. using ALTER TABLE or similar) to include this column.
2718 For background on how this object is used, see the section
2719 :ref:`engine_insertmanyvalues_sentinel_columns` as part of the
2720 section :ref:`engine_insertmanyvalues`.
2722 The :class:`_schema.Column` returned will be a nullable integer column by
2723 default and make use of a sentinel-specific default generator used only in
2724 "insertmanyvalues" operations.
2726 .. seealso::
2728 :func:`_orm.orm_insert_sentinel`
2730 :paramref:`_schema.Column.insert_sentinel`
2732 :ref:`engine_insertmanyvalues`
2734 :ref:`engine_insertmanyvalues_sentinel_columns`
2737 .. versionadded:: 2.0.10
2739 """
2740 return Column(
2741 name=name,
2742 type_=type_api.INTEGERTYPE if type_ is None else type_,
2743 default=(
2744 default if default is not None else _InsertSentinelColumnDefault()
2745 ),
2746 _omit_from_statements=omit_from_statements,
2747 insert_sentinel=True,
2748 )
2751class ForeignKey(DialectKWArgs, SchemaItem):
2752 """Defines a dependency between two columns.
2754 ``ForeignKey`` is specified as an argument to a :class:`_schema.Column`
2755 object,
2756 e.g.::
2758 t = Table(
2759 "remote_table",
2760 metadata,
2761 Column("remote_id", ForeignKey("main_table.id")),
2762 )
2764 Note that ``ForeignKey`` is only a marker object that defines
2765 a dependency between two columns. The actual constraint
2766 is in all cases represented by the :class:`_schema.ForeignKeyConstraint`
2767 object. This object will be generated automatically when
2768 a ``ForeignKey`` is associated with a :class:`_schema.Column` which
2769 in turn is associated with a :class:`_schema.Table`. Conversely,
2770 when :class:`_schema.ForeignKeyConstraint` is applied to a
2771 :class:`_schema.Table`,
2772 ``ForeignKey`` markers are automatically generated to be
2773 present on each associated :class:`_schema.Column`, which are also
2774 associated with the constraint object.
2776 Note that you cannot define a "composite" foreign key constraint,
2777 that is a constraint between a grouping of multiple parent/child
2778 columns, using ``ForeignKey`` objects. To define this grouping,
2779 the :class:`_schema.ForeignKeyConstraint` object must be used, and applied
2780 to the :class:`_schema.Table`. The associated ``ForeignKey`` objects
2781 are created automatically.
2783 The ``ForeignKey`` objects associated with an individual
2784 :class:`_schema.Column`
2785 object are available in the `foreign_keys` collection
2786 of that column.
2788 Further examples of foreign key configuration are in
2789 :ref:`metadata_foreignkeys`.
2791 """
2793 __visit_name__ = "foreign_key"
2795 parent: Column[Any]
2797 _table_column: Optional[Column[Any]]
2799 def __init__(
2800 self,
2801 column: _DDLColumnArgument,
2802 _constraint: Optional[ForeignKeyConstraint] = None,
2803 use_alter: bool = False,
2804 name: _ConstraintNameArgument = None,
2805 onupdate: Optional[str] = None,
2806 ondelete: Optional[str] = None,
2807 deferrable: Optional[bool] = None,
2808 initially: Optional[str] = None,
2809 link_to_name: bool = False,
2810 match: Optional[str] = None,
2811 info: Optional[_InfoType] = None,
2812 comment: Optional[str] = None,
2813 _unresolvable: bool = False,
2814 **dialect_kw: Any,
2815 ):
2816 r"""
2817 Construct a column-level FOREIGN KEY.
2819 The :class:`_schema.ForeignKey` object when constructed generates a
2820 :class:`_schema.ForeignKeyConstraint`
2821 which is associated with the parent
2822 :class:`_schema.Table` object's collection of constraints.
2824 :param column: A single target column for the key relationship. A
2825 :class:`_schema.Column` object or a column name as a string:
2826 ``tablename.columnkey`` or ``schema.tablename.columnkey``.
2827 ``columnkey`` is the ``key`` which has been assigned to the column
2828 (defaults to the column name itself), unless ``link_to_name`` is
2829 ``True`` in which case the rendered name of the column is used.
2831 :param name: Optional string. An in-database name for the key if
2832 `constraint` is not provided.
2834 :param onupdate: Optional string. If set, emit ON UPDATE <value> when
2835 issuing DDL for this constraint. Typical values include CASCADE,
2836 DELETE and RESTRICT.
2838 .. seealso::
2840 :ref:`on_update_on_delete`
2842 :param ondelete: Optional string. If set, emit ON DELETE <value> when
2843 issuing DDL for this constraint. Typical values include CASCADE,
2844 SET NULL and RESTRICT. Some dialects may allow for additional
2845 syntaxes.
2847 .. seealso::
2849 :ref:`on_update_on_delete`
2851 :param deferrable: Optional bool. If set, emit DEFERRABLE or NOT
2852 DEFERRABLE when issuing DDL for this constraint.
2854 :param initially: Optional string. If set, emit INITIALLY <value> when
2855 issuing DDL for this constraint.
2857 :param link_to_name: if True, the string name given in ``column`` is
2858 the rendered name of the referenced column, not its locally
2859 assigned ``key``.
2861 :param use_alter: passed to the underlying
2862 :class:`_schema.ForeignKeyConstraint`
2863 to indicate the constraint should
2864 be generated/dropped externally from the CREATE TABLE/ DROP TABLE
2865 statement. See :paramref:`_schema.ForeignKeyConstraint.use_alter`
2866 for further description.
2868 .. seealso::
2870 :paramref:`_schema.ForeignKeyConstraint.use_alter`
2872 :ref:`use_alter`
2874 :param match: Optional string. If set, emit MATCH <value> when issuing
2875 DDL for this constraint. Typical values include SIMPLE, PARTIAL
2876 and FULL.
2878 :param info: Optional data dictionary which will be populated into the
2879 :attr:`.SchemaItem.info` attribute of this object.
2881 :param comment: Optional string that will render an SQL comment on
2882 foreign key constraint creation.
2884 .. versionadded:: 2.0
2886 :param \**dialect_kw: Additional keyword arguments are dialect
2887 specific, and passed in the form ``<dialectname>_<argname>``. The
2888 arguments are ultimately handled by a corresponding
2889 :class:`_schema.ForeignKeyConstraint`.
2890 See the documentation regarding
2891 an individual dialect at :ref:`dialect_toplevel` for detail on
2892 documented arguments.
2894 """
2896 self._colspec = coercions.expect(roles.DDLReferredColumnRole, column)
2897 self._unresolvable = _unresolvable
2899 if isinstance(self._colspec, str):
2900 self._table_column = None
2901 else:
2902 self._table_column = self._colspec
2904 if not isinstance(
2905 self._table_column.table, (type(None), TableClause)
2906 ):
2907 raise exc.ArgumentError(
2908 "ForeignKey received Column not bound "
2909 "to a Table, got: %r" % self._table_column.table
2910 )
2912 # the linked ForeignKeyConstraint.
2913 # ForeignKey will create this when parent Column
2914 # is attached to a Table, *or* ForeignKeyConstraint
2915 # object passes itself in when creating ForeignKey
2916 # markers.
2917 self.constraint = _constraint
2919 # .parent is not Optional under normal use
2920 self.parent = None # type: ignore
2922 self.use_alter = use_alter
2923 self.name = name
2924 self.onupdate = onupdate
2925 self.ondelete = ondelete
2926 self.deferrable = deferrable
2927 self.initially = initially
2928 self.link_to_name = link_to_name
2929 self.match = match
2930 self.comment = comment
2931 if info:
2932 self.info = info
2933 self._unvalidated_dialect_kw = dialect_kw
2935 def __repr__(self) -> str:
2936 return "ForeignKey(%r)" % self._get_colspec()
2938 @util.deprecated(
2939 "1.4",
2940 "The :meth:`_schema.ForeignKey.copy` method is deprecated "
2941 "and will be removed in a future release.",
2942 )
2943 def copy(self, *, schema: Optional[str] = None, **kw: Any) -> ForeignKey:
2944 return self._copy(schema=schema, **kw)
2946 def _copy(self, *, schema: Optional[str] = None, **kw: Any) -> ForeignKey:
2947 """Produce a copy of this :class:`_schema.ForeignKey` object.
2949 The new :class:`_schema.ForeignKey` will not be bound
2950 to any :class:`_schema.Column`.
2952 This method is usually used by the internal
2953 copy procedures of :class:`_schema.Column`, :class:`_schema.Table`,
2954 and :class:`_schema.MetaData`.
2956 :param schema: The returned :class:`_schema.ForeignKey` will
2957 reference the original table and column name, qualified
2958 by the given string schema name.
2960 """
2961 fk = ForeignKey(
2962 self._get_colspec(schema=schema),
2963 use_alter=self.use_alter,
2964 name=self.name,
2965 onupdate=self.onupdate,
2966 ondelete=self.ondelete,
2967 deferrable=self.deferrable,
2968 initially=self.initially,
2969 link_to_name=self.link_to_name,
2970 match=self.match,
2971 comment=self.comment,
2972 **self._unvalidated_dialect_kw,
2973 )
2974 return self._schema_item_copy(fk)
2976 def _get_colspec(
2977 self,
2978 schema: Optional[
2979 Union[
2980 str,
2981 Literal[SchemaConst.RETAIN_SCHEMA, SchemaConst.BLANK_SCHEMA],
2982 ]
2983 ] = None,
2984 table_name: Optional[str] = None,
2985 _is_copy: bool = False,
2986 ) -> str:
2987 """Return a string based 'column specification' for this
2988 :class:`_schema.ForeignKey`.
2990 This is usually the equivalent of the string-based "tablename.colname"
2991 argument first passed to the object's constructor.
2993 """
2994 if schema not in (None, RETAIN_SCHEMA):
2995 _schema, tname, colname = self._column_tokens
2996 if table_name is not None:
2997 tname = table_name
2998 if schema is BLANK_SCHEMA:
2999 return "%s.%s" % (tname, colname)
3000 else:
3001 return "%s.%s.%s" % (schema, tname, colname)
3002 elif table_name:
3003 schema, tname, colname = self._column_tokens
3004 if schema:
3005 return "%s.%s.%s" % (schema, table_name, colname)
3006 else:
3007 return "%s.%s" % (table_name, colname)
3008 elif self._table_column is not None:
3009 if self._table_column.table is None:
3010 if _is_copy:
3011 raise exc.InvalidRequestError(
3012 f"Can't copy ForeignKey object which refers to "
3013 f"non-table bound Column {self._table_column!r}"
3014 )
3015 else:
3016 return self._table_column.key
3017 return "%s.%s" % (
3018 self._table_column.table.fullname,
3019 self._table_column.key,
3020 )
3021 else:
3022 assert isinstance(self._colspec, str)
3023 return self._colspec
3025 @property
3026 def _referred_schema(self) -> Optional[str]:
3027 return self._column_tokens[0]
3029 def _table_key(self) -> Any:
3030 if self._table_column is not None:
3031 if self._table_column.table is None:
3032 return None
3033 else:
3034 return self._table_column.table.key
3035 else:
3036 schema, tname, colname = self._column_tokens
3037 return _get_table_key(tname, schema)
3039 target_fullname = property(_get_colspec)
3041 def references(self, table: Table) -> bool:
3042 """Return True if the given :class:`_schema.Table`
3043 is referenced by this
3044 :class:`_schema.ForeignKey`."""
3046 return table.corresponding_column(self.column) is not None
3048 def get_referent(self, table: FromClause) -> Optional[Column[Any]]:
3049 """Return the :class:`_schema.Column` in the given
3050 :class:`_schema.Table` (or any :class:`.FromClause`)
3051 referenced by this :class:`_schema.ForeignKey`.
3053 Returns None if this :class:`_schema.ForeignKey`
3054 does not reference the given
3055 :class:`_schema.Table`.
3057 """
3058 # our column is a Column, and any subquery etc. proxying us
3059 # would be doing so via another Column, so that's what would
3060 # be returned here
3061 return table.columns.corresponding_column(self.column) # type: ignore
3063 @util.memoized_property
3064 def _column_tokens(self) -> Tuple[Optional[str], str, Optional[str]]:
3065 """parse a string-based _colspec into its component parts."""
3067 m = self._get_colspec().split(".")
3068 if m is None:
3069 raise exc.ArgumentError(
3070 f"Invalid foreign key column specification: {self._colspec}"
3071 )
3072 if len(m) == 1:
3073 tname = m.pop()
3074 colname = None
3075 else:
3076 colname = m.pop()
3077 tname = m.pop()
3079 # A FK between column 'bar' and table 'foo' can be
3080 # specified as 'foo', 'foo.bar', 'dbo.foo.bar',
3081 # 'otherdb.dbo.foo.bar'. Once we have the column name and
3082 # the table name, treat everything else as the schema
3083 # name. Some databases (e.g. Sybase) support
3084 # inter-database foreign keys. See tickets#1341 and --
3085 # indirectly related -- Ticket #594. This assumes that '.'
3086 # will never appear *within* any component of the FK.
3088 if len(m) > 0:
3089 schema = ".".join(m)
3090 else:
3091 schema = None
3092 return schema, tname, colname
3094 def _resolve_col_tokens(self) -> Tuple[Table, str, Optional[str]]:
3095 if self.parent is None:
3096 raise exc.InvalidRequestError(
3097 "this ForeignKey object does not yet have a "
3098 "parent Column associated with it."
3099 )
3101 elif self.parent.table is None:
3102 raise exc.InvalidRequestError(
3103 "this ForeignKey's parent column is not yet associated "
3104 "with a Table."
3105 )
3107 parenttable = self.parent.table
3109 if self._unresolvable:
3110 schema, tname, colname = self._column_tokens
3111 tablekey = _get_table_key(tname, schema)
3112 return parenttable, tablekey, colname
3114 # assertion
3115 # basically Column._make_proxy() sends the actual
3116 # target Column to the ForeignKey object, so the
3117 # string resolution here is never called.
3118 for c in self.parent.base_columns:
3119 if isinstance(c, Column):
3120 assert c.table is parenttable
3121 break
3122 else:
3123 assert False
3124 ######################
3126 schema, tname, colname = self._column_tokens
3128 if schema is None and parenttable.metadata.schema is not None:
3129 schema = parenttable.metadata.schema
3131 tablekey = _get_table_key(tname, schema)
3132 return parenttable, tablekey, colname
3134 def _link_to_col_by_colstring(
3135 self, parenttable: Table, table: Table, colname: Optional[str]
3136 ) -> Column[Any]:
3137 _column = None
3138 if colname is None:
3139 # colname is None in the case that ForeignKey argument
3140 # was specified as table name only, in which case we
3141 # match the column name to the same column on the
3142 # parent.
3143 # this use case wasn't working in later 1.x series
3144 # as it had no test coverage; fixed in 2.0
3145 parent = self.parent
3146 assert parent is not None
3147 key = parent.key
3148 _column = table.c.get(key, None)
3149 elif self.link_to_name:
3150 key = colname
3151 for c in table.c:
3152 if c.name == colname:
3153 _column = c
3154 else:
3155 key = colname
3156 _column = table.c.get(colname, None)
3158 if _column is None:
3159 raise exc.NoReferencedColumnError(
3160 "Could not initialize target column "
3161 f"for ForeignKey '{self._colspec}' "
3162 f"on table '{parenttable.name}': "
3163 f"table '{table.name}' has no column named '{key}'",
3164 table.name,
3165 key,
3166 )
3168 return _column
3170 def _set_target_column(self, column: Column[Any]) -> None:
3171 assert self.parent is not None
3173 # propagate TypeEngine to parent if it didn't have one
3174 if self.parent.type._isnull:
3175 self.parent.type = column.type
3177 # super-edgy case, if other FKs point to our column,
3178 # they'd get the type propagated out also.
3180 def set_type(fk: ForeignKey) -> None:
3181 if fk.parent.type._isnull:
3182 fk.parent.type = column.type
3184 self.parent._setup_on_memoized_fks(set_type)
3186 self.column = column # type: ignore
3188 @util.ro_memoized_property
3189 def column(self) -> Column[Any]:
3190 """Return the target :class:`_schema.Column` referenced by this
3191 :class:`_schema.ForeignKey`.
3193 If no target column has been established, an exception
3194 is raised.
3196 """
3198 return self._resolve_column()
3200 @overload
3201 def _resolve_column(
3202 self, *, raiseerr: Literal[True] = ...
3203 ) -> Column[Any]: ...
3205 @overload
3206 def _resolve_column(
3207 self, *, raiseerr: bool = ...
3208 ) -> Optional[Column[Any]]: ...
3210 def _resolve_column(
3211 self, *, raiseerr: bool = True
3212 ) -> Optional[Column[Any]]:
3213 _column: Column[Any]
3215 if isinstance(self._colspec, str):
3216 parenttable, tablekey, colname = self._resolve_col_tokens()
3218 if self._unresolvable or tablekey not in parenttable.metadata:
3219 if not raiseerr:
3220 return None
3221 raise exc.NoReferencedTableError(
3222 f"Foreign key associated with column "
3223 f"'{self.parent}' could not find "
3224 f"table '{tablekey}' with which to generate a "
3225 f"foreign key to target column '{colname}'",
3226 tablekey,
3227 )
3228 elif parenttable.key not in parenttable.metadata:
3229 if not raiseerr:
3230 return None
3231 raise exc.InvalidRequestError(
3232 f"Table {parenttable} is no longer associated with its "
3233 "parent MetaData"
3234 )
3235 else:
3236 table = parenttable.metadata.tables[tablekey]
3237 return self._link_to_col_by_colstring(
3238 parenttable, table, colname
3239 )
3241 elif hasattr(self._colspec, "__clause_element__"):
3242 _column = self._colspec.__clause_element__()
3243 return _column
3244 else:
3245 _column = self._colspec
3246 return _column
3248 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
3249 assert isinstance(parent, Column)
3251 if self.parent is not None and self.parent is not parent:
3252 raise exc.InvalidRequestError(
3253 "This ForeignKey already has a parent !"
3254 )
3255 self.parent = parent
3256 self.parent.foreign_keys.add(self)
3257 self.parent._on_table_attach(self._set_table)
3259 def _set_remote_table(self, table: Table) -> None:
3260 parenttable, _, colname = self._resolve_col_tokens()
3261 _column = self._link_to_col_by_colstring(parenttable, table, colname)
3262 self._set_target_column(_column)
3263 assert self.constraint is not None
3264 self.constraint._validate_dest_table(table)
3266 def _remove_from_metadata(self, metadata: MetaData) -> None:
3267 parenttable, table_key, colname = self._resolve_col_tokens()
3268 fk_key = (table_key, colname)
3270 if self in metadata._fk_memos[fk_key]:
3271 # TODO: no test coverage for self not in memos
3272 metadata._fk_memos[fk_key].remove(self)
3274 def _set_table(self, column: Column[Any], table: Table) -> None:
3275 # standalone ForeignKey - create ForeignKeyConstraint
3276 # on the hosting Table when attached to the Table.
3277 assert isinstance(table, Table)
3278 if self.constraint is None:
3279 self.constraint = ForeignKeyConstraint(
3280 [],
3281 [],
3282 use_alter=self.use_alter,
3283 name=self.name,
3284 onupdate=self.onupdate,
3285 ondelete=self.ondelete,
3286 deferrable=self.deferrable,
3287 initially=self.initially,
3288 match=self.match,
3289 comment=self.comment,
3290 **self._unvalidated_dialect_kw,
3291 )
3292 self.constraint._append_element(column, self)
3293 self.constraint._set_parent_with_dispatch(table)
3294 table.foreign_keys.add(self)
3295 # set up remote ".column" attribute, or a note to pick it
3296 # up when the other Table/Column shows up
3297 if isinstance(self._colspec, str):
3298 parenttable, table_key, colname = self._resolve_col_tokens()
3299 fk_key = (table_key, colname)
3300 if table_key in parenttable.metadata.tables:
3301 table = parenttable.metadata.tables[table_key]
3302 try:
3303 _column = self._link_to_col_by_colstring(
3304 parenttable, table, colname
3305 )
3306 except exc.NoReferencedColumnError:
3307 # this is OK, we'll try later
3308 pass
3309 else:
3310 self._set_target_column(_column)
3312 parenttable.metadata._fk_memos[fk_key].append(self)
3313 elif hasattr(self._colspec, "__clause_element__"):
3314 _column = self._colspec.__clause_element__()
3315 self._set_target_column(_column)
3316 else:
3317 _column = self._colspec
3318 self._set_target_column(_column)
3321if TYPE_CHECKING:
3323 def default_is_sequence(
3324 obj: Optional[DefaultGenerator],
3325 ) -> TypeGuard[Sequence]: ...
3327 def default_is_clause_element(
3328 obj: Optional[DefaultGenerator],
3329 ) -> TypeGuard[ColumnElementColumnDefault]: ...
3331 def default_is_scalar(
3332 obj: Optional[DefaultGenerator],
3333 ) -> TypeGuard[ScalarElementColumnDefault]: ...
3335else:
3336 default_is_sequence = operator.attrgetter("is_sequence")
3338 default_is_clause_element = operator.attrgetter("is_clause_element")
3340 default_is_scalar = operator.attrgetter("is_scalar")
3343class DefaultGenerator(Executable, SchemaItem):
3344 """Base class for column *default* values.
3346 This object is only present on column.default or column.onupdate.
3347 It's not valid as a server default.
3349 """
3351 __visit_name__ = "default_generator"
3353 _is_default_generator = True
3354 is_sequence = False
3355 is_identity = False
3356 is_server_default = False
3357 is_clause_element = False
3358 is_callable = False
3359 is_scalar = False
3360 has_arg = False
3361 is_sentinel = False
3362 column: Optional[Column[Any]]
3364 def __init__(self, for_update: bool = False) -> None:
3365 self.for_update = for_update
3367 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
3368 if TYPE_CHECKING:
3369 assert isinstance(parent, Column)
3370 self.column = parent
3371 if self.for_update:
3372 self.column.onupdate = self
3373 else:
3374 self.column.default = self
3376 def _copy(self) -> DefaultGenerator:
3377 raise NotImplementedError()
3379 def _execute_on_connection(
3380 self,
3381 connection: Connection,
3382 distilled_params: _CoreMultiExecuteParams,
3383 execution_options: CoreExecuteOptionsParameter,
3384 ) -> Any:
3385 util.warn_deprecated(
3386 "Using the .execute() method to invoke a "
3387 "DefaultGenerator object is deprecated; please use "
3388 "the .scalar() method.",
3389 "2.0",
3390 )
3391 return self._execute_on_scalar(
3392 connection, distilled_params, execution_options
3393 )
3395 def _execute_on_scalar(
3396 self,
3397 connection: Connection,
3398 distilled_params: _CoreMultiExecuteParams,
3399 execution_options: CoreExecuteOptionsParameter,
3400 ) -> Any:
3401 return connection._execute_default(
3402 self, distilled_params, execution_options
3403 )
3406class ColumnDefault(DefaultGenerator, ABC):
3407 """A plain default value on a column.
3409 This could correspond to a constant, a callable function,
3410 or a SQL clause.
3412 :class:`.ColumnDefault` is generated automatically
3413 whenever the ``default``, ``onupdate`` arguments of
3414 :class:`_schema.Column` are used. A :class:`.ColumnDefault`
3415 can be passed positionally as well.
3417 For example, the following::
3419 Column("foo", Integer, default=50)
3421 Is equivalent to::
3423 Column("foo", Integer, ColumnDefault(50))
3425 """
3427 arg: Any
3429 @overload
3430 def __new__(
3431 cls, arg: Callable[..., Any], for_update: bool = ...
3432 ) -> CallableColumnDefault: ...
3434 @overload
3435 def __new__(
3436 cls, arg: ColumnElement[Any], for_update: bool = ...
3437 ) -> ColumnElementColumnDefault: ...
3439 # if I return ScalarElementColumnDefault here, which is what's actually
3440 # returned, mypy complains that
3441 # overloads overlap w/ incompatible return types.
3442 @overload
3443 def __new__(cls, arg: object, for_update: bool = ...) -> ColumnDefault: ...
3445 def __new__(
3446 cls, arg: Any = None, for_update: bool = False
3447 ) -> ColumnDefault:
3448 """Construct a new :class:`.ColumnDefault`.
3451 :param arg: argument representing the default value.
3452 May be one of the following:
3454 * a plain non-callable Python value, such as a
3455 string, integer, boolean, or other simple type.
3456 The default value will be used as is each time.
3457 * a SQL expression, that is one which derives from
3458 :class:`_expression.ColumnElement`. The SQL expression will
3459 be rendered into the INSERT or UPDATE statement,
3460 or in the case of a primary key column when
3461 RETURNING is not used may be
3462 pre-executed before an INSERT within a SELECT.
3463 * A Python callable. The function will be invoked for each
3464 new row subject to an INSERT or UPDATE.
3465 The callable must accept exactly
3466 zero or one positional arguments. The one-argument form
3467 will receive an instance of the :class:`.ExecutionContext`,
3468 which provides contextual information as to the current
3469 :class:`_engine.Connection` in use as well as the current
3470 statement and parameters.
3472 """
3474 if isinstance(arg, FetchedValue):
3475 raise exc.ArgumentError(
3476 "ColumnDefault may not be a server-side default type."
3477 )
3478 elif callable(arg):
3479 cls = CallableColumnDefault
3480 elif isinstance(arg, ClauseElement):
3481 cls = ColumnElementColumnDefault
3482 elif arg is not None:
3483 cls = ScalarElementColumnDefault
3485 return object.__new__(cls)
3487 def __repr__(self) -> str:
3488 return f"{self.__class__.__name__}({self.arg!r})"
3491class ScalarElementColumnDefault(ColumnDefault):
3492 """default generator for a fixed scalar Python value
3494 .. versionadded: 2.0
3496 """
3498 is_scalar = True
3499 has_arg = True
3501 def __init__(self, arg: Any, for_update: bool = False) -> None:
3502 self.for_update = for_update
3503 self.arg = arg
3505 def _copy(self) -> ScalarElementColumnDefault:
3506 return ScalarElementColumnDefault(
3507 arg=self.arg, for_update=self.for_update
3508 )
3511class _InsertSentinelColumnDefault(ColumnDefault):
3512 """Default generator that's specific to the use of a "sentinel" column
3513 when using the insertmanyvalues feature.
3515 This default is used as part of the :func:`_schema.insert_sentinel`
3516 construct.
3518 """
3520 is_sentinel = True
3521 for_update = False
3522 arg = None
3524 def __new__(cls) -> _InsertSentinelColumnDefault:
3525 return object.__new__(cls)
3527 def __init__(self) -> None:
3528 pass
3530 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
3531 col = cast("Column[Any]", parent)
3532 if not col._insert_sentinel:
3533 raise exc.ArgumentError(
3534 "The _InsertSentinelColumnDefault may only be applied to a "
3535 "Column marked as insert_sentinel=True"
3536 )
3537 elif not col.nullable:
3538 raise exc.ArgumentError(
3539 "The _InsertSentinelColumnDefault may only be applied to a "
3540 "Column that is nullable"
3541 )
3543 super()._set_parent(parent, **kw)
3545 def _copy(self) -> _InsertSentinelColumnDefault:
3546 return _InsertSentinelColumnDefault()
3549_SQLExprDefault = Union["ColumnElement[Any]", "TextClause"]
3552class ColumnElementColumnDefault(ColumnDefault):
3553 """default generator for a SQL expression
3555 .. versionadded:: 2.0
3557 """
3559 is_clause_element = True
3560 has_arg = True
3561 arg: _SQLExprDefault
3563 def __init__(
3564 self,
3565 arg: _SQLExprDefault,
3566 for_update: bool = False,
3567 ) -> None:
3568 self.for_update = for_update
3569 self.arg = arg
3571 def _copy(self) -> ColumnElementColumnDefault:
3572 return ColumnElementColumnDefault(
3573 arg=self.arg, for_update=self.for_update
3574 )
3576 @util.memoized_property
3577 @util.preload_module("sqlalchemy.sql.sqltypes")
3578 def _arg_is_typed(self) -> bool:
3579 sqltypes = util.preloaded.sql_sqltypes
3581 return not isinstance(self.arg.type, sqltypes.NullType)
3584class _CallableColumnDefaultProtocol(Protocol):
3585 def __call__(self, context: ExecutionContext) -> Any: ...
3588class CallableColumnDefault(ColumnDefault):
3589 """default generator for a callable Python function
3591 .. versionadded:: 2.0
3593 """
3595 is_callable = True
3596 arg: _CallableColumnDefaultProtocol
3597 has_arg = True
3599 def __init__(
3600 self,
3601 arg: Union[_CallableColumnDefaultProtocol, Callable[[], Any]],
3602 for_update: bool = False,
3603 ) -> None:
3604 self.for_update = for_update
3605 self.arg = self._maybe_wrap_callable(arg)
3607 def _copy(self) -> CallableColumnDefault:
3608 return CallableColumnDefault(arg=self.arg, for_update=self.for_update)
3610 def _maybe_wrap_callable(
3611 self, fn: Union[_CallableColumnDefaultProtocol, Callable[[], Any]]
3612 ) -> _CallableColumnDefaultProtocol:
3613 """Wrap callables that don't accept a context.
3615 This is to allow easy compatibility with default callables
3616 that aren't specific to accepting of a context.
3618 """
3620 try:
3621 argspec = util.get_callable_argspec(fn, no_self=True)
3622 except TypeError:
3623 return util.wrap_callable(lambda ctx: fn(), fn) # type: ignore
3625 defaulted = argspec[3] is not None and len(argspec[3]) or 0
3626 positionals = len(argspec[0]) - defaulted
3628 if positionals == 0:
3629 return util.wrap_callable(lambda ctx: fn(), fn) # type: ignore
3631 elif positionals == 1:
3632 return fn # type: ignore
3633 else:
3634 raise exc.ArgumentError(
3635 "ColumnDefault Python function takes zero or one "
3636 "positional arguments"
3637 )
3640class IdentityOptions:
3641 """Defines options for a named database sequence or an identity column.
3643 .. versionadded:: 1.3.18
3645 .. seealso::
3647 :class:`.Sequence`
3649 """
3651 def __init__(
3652 self,
3653 start: Optional[int] = None,
3654 increment: Optional[int] = None,
3655 minvalue: Optional[int] = None,
3656 maxvalue: Optional[int] = None,
3657 nominvalue: Optional[bool] = None,
3658 nomaxvalue: Optional[bool] = None,
3659 cycle: Optional[bool] = None,
3660 cache: Optional[int] = None,
3661 order: Optional[bool] = None,
3662 ) -> None:
3663 """Construct a :class:`.IdentityOptions` object.
3665 See the :class:`.Sequence` documentation for a complete description
3666 of the parameters.
3668 :param start: the starting index of the sequence.
3669 :param increment: the increment value of the sequence.
3670 :param minvalue: the minimum value of the sequence.
3671 :param maxvalue: the maximum value of the sequence.
3672 :param nominvalue: no minimum value of the sequence.
3673 :param nomaxvalue: no maximum value of the sequence.
3674 :param cycle: allows the sequence to wrap around when the maxvalue
3675 or minvalue has been reached.
3676 :param cache: optional integer value; number of future values in the
3677 sequence which are calculated in advance.
3678 :param order: optional boolean value; if ``True``, renders the
3679 ORDER keyword.
3681 """
3682 self.start = start
3683 self.increment = increment
3684 self.minvalue = minvalue
3685 self.maxvalue = maxvalue
3686 self.nominvalue = nominvalue
3687 self.nomaxvalue = nomaxvalue
3688 self.cycle = cycle
3689 self.cache = cache
3690 self.order = order
3692 @property
3693 def _increment_is_negative(self) -> bool:
3694 return self.increment is not None and self.increment < 0
3697class Sequence(HasSchemaAttr, IdentityOptions, DefaultGenerator):
3698 """Represents a named database sequence.
3700 The :class:`.Sequence` object represents the name and configurational
3701 parameters of a database sequence. It also represents
3702 a construct that can be "executed" by a SQLAlchemy :class:`_engine.Engine`
3703 or :class:`_engine.Connection`,
3704 rendering the appropriate "next value" function
3705 for the target database and returning a result.
3707 The :class:`.Sequence` is typically associated with a primary key column::
3709 some_table = Table(
3710 "some_table",
3711 metadata,
3712 Column(
3713 "id",
3714 Integer,
3715 Sequence("some_table_seq", start=1),
3716 primary_key=True,
3717 ),
3718 )
3720 When CREATE TABLE is emitted for the above :class:`_schema.Table`, if the
3721 target platform supports sequences, a CREATE SEQUENCE statement will
3722 be emitted as well. For platforms that don't support sequences,
3723 the :class:`.Sequence` construct is ignored.
3725 .. seealso::
3727 :ref:`defaults_sequences`
3729 :class:`.CreateSequence`
3731 :class:`.DropSequence`
3733 """
3735 __visit_name__ = "sequence"
3737 is_sequence = True
3739 column: Optional[Column[Any]]
3740 data_type: Optional[TypeEngine[int]]
3742 def __init__(
3743 self,
3744 name: str,
3745 start: Optional[int] = None,
3746 increment: Optional[int] = None,
3747 minvalue: Optional[int] = None,
3748 maxvalue: Optional[int] = None,
3749 nominvalue: Optional[bool] = None,
3750 nomaxvalue: Optional[bool] = None,
3751 cycle: Optional[bool] = None,
3752 schema: Optional[Union[str, Literal[SchemaConst.BLANK_SCHEMA]]] = None,
3753 cache: Optional[int] = None,
3754 order: Optional[bool] = None,
3755 data_type: Optional[_TypeEngineArgument[int]] = None,
3756 optional: bool = False,
3757 quote: Optional[bool] = None,
3758 metadata: Optional[MetaData] = None,
3759 quote_schema: Optional[bool] = None,
3760 for_update: bool = False,
3761 ) -> None:
3762 """Construct a :class:`.Sequence` object.
3764 :param name: the name of the sequence.
3766 :param start: the starting index of the sequence. This value is
3767 used when the CREATE SEQUENCE command is emitted to the database
3768 as the value of the "START WITH" clause. If ``None``, the
3769 clause is omitted, which on most platforms indicates a starting
3770 value of 1.
3772 .. versionchanged:: 2.0 The :paramref:`.Sequence.start` parameter
3773 is required in order to have DDL emit "START WITH". This is a
3774 reversal of a change made in version 1.4 which would implicitly
3775 render "START WITH 1" if the :paramref:`.Sequence.start` were
3776 not included. See :ref:`change_7211` for more detail.
3778 :param increment: the increment value of the sequence. This
3779 value is used when the CREATE SEQUENCE command is emitted to
3780 the database as the value of the "INCREMENT BY" clause. If ``None``,
3781 the clause is omitted, which on most platforms indicates an
3782 increment of 1.
3783 :param minvalue: the minimum value of the sequence. This
3784 value is used when the CREATE SEQUENCE command is emitted to
3785 the database as the value of the "MINVALUE" clause. If ``None``,
3786 the clause is omitted, which on most platforms indicates a
3787 minvalue of 1 and -2^63-1 for ascending and descending sequences,
3788 respectively.
3790 :param maxvalue: the maximum value of the sequence. This
3791 value is used when the CREATE SEQUENCE command is emitted to
3792 the database as the value of the "MAXVALUE" clause. If ``None``,
3793 the clause is omitted, which on most platforms indicates a
3794 maxvalue of 2^63-1 and -1 for ascending and descending sequences,
3795 respectively.
3797 :param nominvalue: no minimum value of the sequence. This
3798 value is used when the CREATE SEQUENCE command is emitted to
3799 the database as the value of the "NO MINVALUE" clause. If ``None``,
3800 the clause is omitted, which on most platforms indicates a
3801 minvalue of 1 and -2^63-1 for ascending and descending sequences,
3802 respectively.
3804 :param nomaxvalue: no maximum value of the sequence. This
3805 value is used when the CREATE SEQUENCE command is emitted to
3806 the database as the value of the "NO MAXVALUE" clause. If ``None``,
3807 the clause is omitted, which on most platforms indicates a
3808 maxvalue of 2^63-1 and -1 for ascending and descending sequences,
3809 respectively.
3811 :param cycle: allows the sequence to wrap around when the maxvalue
3812 or minvalue has been reached by an ascending or descending sequence
3813 respectively. This value is used when the CREATE SEQUENCE command
3814 is emitted to the database as the "CYCLE" clause. If the limit is
3815 reached, the next number generated will be the minvalue or maxvalue,
3816 respectively. If cycle=False (the default) any calls to nextval
3817 after the sequence has reached its maximum value will return an
3818 error.
3820 :param schema: optional schema name for the sequence, if located
3821 in a schema other than the default. The rules for selecting the
3822 schema name when a :class:`_schema.MetaData`
3823 is also present are the same
3824 as that of :paramref:`_schema.Table.schema`.
3826 :param cache: optional integer value; number of future values in the
3827 sequence which are calculated in advance. Renders the CACHE keyword
3828 understood by Oracle Database and PostgreSQL.
3830 :param order: optional boolean value; if ``True``, renders the
3831 ORDER keyword, understood by Oracle Database, indicating the sequence
3832 is definitively ordered. May be necessary to provide deterministic
3833 ordering using Oracle RAC.
3835 :param data_type: The type to be returned by the sequence, for
3836 dialects that allow us to choose between INTEGER, BIGINT, etc.
3837 (e.g., mssql).
3839 .. versionadded:: 1.4.0
3841 :param optional: boolean value, when ``True``, indicates that this
3842 :class:`.Sequence` object only needs to be explicitly generated
3843 on backends that don't provide another way to generate primary
3844 key identifiers. Currently, it essentially means, "don't create
3845 this sequence on the PostgreSQL backend, where the SERIAL keyword
3846 creates a sequence for us automatically".
3847 :param quote: boolean value, when ``True`` or ``False``, explicitly
3848 forces quoting of the :paramref:`_schema.Sequence.name` on or off.
3849 When left at its default of ``None``, normal quoting rules based
3850 on casing and reserved words take place.
3851 :param quote_schema: Set the quoting preferences for the ``schema``
3852 name.
3854 :param metadata: optional :class:`_schema.MetaData` object which this
3855 :class:`.Sequence` will be associated with. A :class:`.Sequence`
3856 that is associated with a :class:`_schema.MetaData`
3857 gains the following
3858 capabilities:
3860 * The :class:`.Sequence` will inherit the
3861 :paramref:`_schema.MetaData.schema`
3862 parameter specified to the target :class:`_schema.MetaData`, which
3863 affects the production of CREATE / DROP DDL, if any.
3865 * The :meth:`.Sequence.create` and :meth:`.Sequence.drop` methods
3866 automatically use the engine bound to the :class:`_schema.MetaData`
3867 object, if any.
3869 * The :meth:`_schema.MetaData.create_all` and
3870 :meth:`_schema.MetaData.drop_all`
3871 methods will emit CREATE / DROP for this :class:`.Sequence`,
3872 even if the :class:`.Sequence` is not associated with any
3873 :class:`_schema.Table` / :class:`_schema.Column`
3874 that's a member of this
3875 :class:`_schema.MetaData`.
3877 The above behaviors can only occur if the :class:`.Sequence` is
3878 explicitly associated with the :class:`_schema.MetaData`
3879 via this parameter.
3881 .. seealso::
3883 :ref:`sequence_metadata` - full discussion of the
3884 :paramref:`.Sequence.metadata` parameter.
3886 :param for_update: Indicates this :class:`.Sequence`, when associated
3887 with a :class:`_schema.Column`,
3888 should be invoked for UPDATE statements
3889 on that column's table, rather than for INSERT statements, when
3890 no value is otherwise present for that column in the statement.
3892 """
3893 DefaultGenerator.__init__(self, for_update=for_update)
3894 IdentityOptions.__init__(
3895 self,
3896 start=start,
3897 increment=increment,
3898 minvalue=minvalue,
3899 maxvalue=maxvalue,
3900 nominvalue=nominvalue,
3901 nomaxvalue=nomaxvalue,
3902 cycle=cycle,
3903 cache=cache,
3904 order=order,
3905 )
3906 self.column = None
3907 self.name = quoted_name(name, quote)
3908 self.optional = optional
3909 if schema is BLANK_SCHEMA:
3910 self.schema = schema = None
3911 elif metadata is not None and schema is None and metadata.schema:
3912 self.schema = schema = metadata.schema
3913 else:
3914 self.schema = quoted_name.construct(schema, quote_schema)
3915 self.metadata = metadata
3916 self._key = _get_table_key(name, schema)
3917 if metadata:
3918 self._set_metadata(metadata)
3919 if data_type is not None:
3920 self.data_type = to_instance(data_type)
3921 else:
3922 self.data_type = None
3924 @util.preload_module("sqlalchemy.sql.functions")
3925 def next_value(self) -> Function[int]:
3926 """Return a :class:`.next_value` function element
3927 which will render the appropriate increment function
3928 for this :class:`.Sequence` within any SQL expression.
3930 """
3931 return util.preloaded.sql_functions.func.next_value(self)
3933 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
3934 column = parent
3935 assert isinstance(column, Column)
3936 super()._set_parent(column)
3937 column._on_table_attach(self._set_table)
3939 def _copy(self) -> Sequence:
3940 return Sequence(
3941 name=self.name,
3942 start=self.start,
3943 increment=self.increment,
3944 minvalue=self.minvalue,
3945 maxvalue=self.maxvalue,
3946 nominvalue=self.nominvalue,
3947 nomaxvalue=self.nomaxvalue,
3948 cycle=self.cycle,
3949 schema=self.schema,
3950 cache=self.cache,
3951 order=self.order,
3952 data_type=self.data_type,
3953 optional=self.optional,
3954 metadata=self.metadata,
3955 for_update=self.for_update,
3956 )
3958 def _set_table(self, column: Column[Any], table: Table) -> None:
3959 self._set_metadata(table.metadata)
3961 def _set_metadata(self, metadata: MetaData) -> None:
3962 self.metadata = metadata
3963 self.metadata._sequences[self._key] = self
3965 def create(self, bind: _CreateDropBind, checkfirst: bool = True) -> None:
3966 """Creates this sequence in the database."""
3968 bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
3970 def drop(self, bind: _CreateDropBind, checkfirst: bool = True) -> None:
3971 """Drops this sequence from the database."""
3973 bind._run_ddl_visitor(ddl.SchemaDropper, self, checkfirst=checkfirst)
3975 def _not_a_column_expr(self) -> NoReturn:
3976 raise exc.InvalidRequestError(
3977 f"This {self.__class__.__name__} cannot be used directly "
3978 "as a column expression. Use func.next_value(sequence) "
3979 "to produce a 'next value' function that's usable "
3980 "as a column element."
3981 )
3984@inspection._self_inspects
3985class FetchedValue(SchemaEventTarget):
3986 """A marker for a transparent database-side default.
3988 Use :class:`.FetchedValue` when the database is configured
3989 to provide some automatic default for a column.
3991 E.g.::
3993 Column("foo", Integer, FetchedValue())
3995 Would indicate that some trigger or default generator
3996 will create a new value for the ``foo`` column during an
3997 INSERT.
3999 .. seealso::
4001 :ref:`triggered_columns`
4003 """
4005 is_server_default = True
4006 reflected = False
4007 has_argument = False
4008 is_clause_element = False
4009 is_identity = False
4011 column: Optional[Column[Any]]
4013 def __init__(self, for_update: bool = False) -> None:
4014 self.for_update = for_update
4016 def _as_for_update(self, for_update: bool) -> FetchedValue:
4017 if for_update == self.for_update:
4018 return self
4019 else:
4020 return self._clone(for_update)
4022 def _copy(self) -> FetchedValue:
4023 return FetchedValue(self.for_update)
4025 def _clone(self, for_update: bool) -> Self:
4026 n = self.__class__.__new__(self.__class__)
4027 n.__dict__.update(self.__dict__)
4028 n.__dict__.pop("column", None)
4029 n.for_update = for_update
4030 return n
4032 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4033 column = parent
4034 assert isinstance(column, Column)
4035 self.column = column
4036 if self.for_update:
4037 self.column.server_onupdate = self
4038 else:
4039 self.column.server_default = self
4041 def __repr__(self) -> str:
4042 return util.generic_repr(self)
4045class DefaultClause(FetchedValue):
4046 """A DDL-specified DEFAULT column value.
4048 :class:`.DefaultClause` is a :class:`.FetchedValue`
4049 that also generates a "DEFAULT" clause when
4050 "CREATE TABLE" is emitted.
4052 :class:`.DefaultClause` is generated automatically
4053 whenever the ``server_default``, ``server_onupdate`` arguments of
4054 :class:`_schema.Column` are used. A :class:`.DefaultClause`
4055 can be passed positionally as well.
4057 For example, the following::
4059 Column("foo", Integer, server_default="50")
4061 Is equivalent to::
4063 Column("foo", Integer, DefaultClause("50"))
4065 """
4067 has_argument = True
4069 def __init__(
4070 self,
4071 arg: Union[str, ClauseElement, TextClause],
4072 for_update: bool = False,
4073 _reflected: bool = False,
4074 ) -> None:
4075 util.assert_arg_type(arg, (str, ClauseElement, TextClause), "arg")
4076 super().__init__(for_update)
4077 self.arg = arg
4078 self.reflected = _reflected
4080 def _copy(self) -> DefaultClause:
4081 return DefaultClause(
4082 arg=self.arg, for_update=self.for_update, _reflected=self.reflected
4083 )
4085 def __repr__(self) -> str:
4086 return "DefaultClause(%r, for_update=%r)" % (self.arg, self.for_update)
4089class Constraint(DialectKWArgs, HasConditionalDDL, SchemaItem):
4090 """A table-level SQL constraint.
4092 :class:`_schema.Constraint` serves as the base class for the series of
4093 constraint objects that can be associated with :class:`_schema.Table`
4094 objects, including :class:`_schema.PrimaryKeyConstraint`,
4095 :class:`_schema.ForeignKeyConstraint`
4096 :class:`_schema.UniqueConstraint`, and
4097 :class:`_schema.CheckConstraint`.
4099 """
4101 __visit_name__ = "constraint"
4103 _creation_order: int
4104 _column_flag: bool
4106 def __init__(
4107 self,
4108 name: _ConstraintNameArgument = None,
4109 deferrable: Optional[bool] = None,
4110 initially: Optional[str] = None,
4111 info: Optional[_InfoType] = None,
4112 comment: Optional[str] = None,
4113 _create_rule: Optional[Any] = None,
4114 _type_bound: bool = False,
4115 **dialect_kw: Any,
4116 ) -> None:
4117 r"""Create a SQL constraint.
4119 :param name:
4120 Optional, the in-database name of this ``Constraint``.
4122 :param deferrable:
4123 Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
4124 issuing DDL for this constraint.
4126 :param initially:
4127 Optional string. If set, emit INITIALLY <value> when issuing DDL
4128 for this constraint.
4130 :param info: Optional data dictionary which will be populated into the
4131 :attr:`.SchemaItem.info` attribute of this object.
4133 :param comment: Optional string that will render an SQL comment on
4134 foreign key constraint creation.
4136 .. versionadded:: 2.0
4138 :param \**dialect_kw: Additional keyword arguments are dialect
4139 specific, and passed in the form ``<dialectname>_<argname>``. See
4140 the documentation regarding an individual dialect at
4141 :ref:`dialect_toplevel` for detail on documented arguments.
4143 :param _create_rule:
4144 used internally by some datatypes that also create constraints.
4146 :param _type_bound:
4147 used internally to indicate that this constraint is associated with
4148 a specific datatype.
4150 """
4152 self.name = name
4153 self.deferrable = deferrable
4154 self.initially = initially
4155 if info:
4156 self.info = info
4157 self._create_rule = _create_rule
4158 self._type_bound = _type_bound
4159 util.set_creation_order(self)
4160 self._validate_dialect_kwargs(dialect_kw)
4161 self.comment = comment
4163 def _should_create_for_compiler(
4164 self, compiler: DDLCompiler, **kw: Any
4165 ) -> bool:
4166 if self._create_rule is not None and not self._create_rule(compiler):
4167 return False
4168 elif self._ddl_if is not None:
4169 return self._ddl_if._should_execute(
4170 ddl.CreateConstraint(self), self, None, compiler=compiler, **kw
4171 )
4172 else:
4173 return True
4175 @property
4176 def table(self) -> Table:
4177 try:
4178 if isinstance(self.parent, Table):
4179 return self.parent
4180 except AttributeError:
4181 pass
4182 raise exc.InvalidRequestError(
4183 "This constraint is not bound to a table. Did you "
4184 "mean to call table.append_constraint(constraint) ?"
4185 )
4187 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4188 assert isinstance(parent, (Table, Column))
4189 self.parent = parent
4190 parent.constraints.add(self)
4192 @util.deprecated(
4193 "1.4",
4194 "The :meth:`_schema.Constraint.copy` method is deprecated "
4195 "and will be removed in a future release.",
4196 )
4197 def copy(self, **kw: Any) -> Self:
4198 return self._copy(**kw)
4200 def _copy(self, **kw: Any) -> Self:
4201 raise NotImplementedError()
4204class ColumnCollectionMixin:
4205 """A :class:`_expression.ColumnCollection` of :class:`_schema.Column`
4206 objects.
4208 This collection represents the columns which are referred to by
4209 this object.
4211 """
4213 _columns: DedupeColumnCollection[Column[Any]]
4215 _allow_multiple_tables = False
4217 _pending_colargs: List[Optional[Union[str, Column[Any]]]]
4219 if TYPE_CHECKING:
4221 def _set_parent_with_dispatch(
4222 self, parent: SchemaEventTarget, **kw: Any
4223 ) -> None: ...
4225 def __init__(
4226 self,
4227 *columns: _DDLColumnArgument,
4228 _autoattach: bool = True,
4229 _column_flag: bool = False,
4230 _gather_expressions: Optional[
4231 List[Union[str, ColumnElement[Any]]]
4232 ] = None,
4233 ) -> None:
4234 self._column_flag = _column_flag
4235 self._columns = DedupeColumnCollection()
4237 processed_expressions: Optional[
4238 List[Union[ColumnElement[Any], str]]
4239 ] = _gather_expressions
4241 if processed_expressions is not None:
4243 # this is expected to be an empty list
4244 assert not processed_expressions
4246 self._pending_colargs = []
4247 for (
4248 expr,
4249 _,
4250 _,
4251 add_element,
4252 ) in coercions.expect_col_expression_collection(
4253 roles.DDLConstraintColumnRole, columns
4254 ):
4255 self._pending_colargs.append(add_element)
4256 processed_expressions.append(expr)
4257 else:
4258 self._pending_colargs = [
4259 coercions.expect(roles.DDLConstraintColumnRole, column)
4260 for column in columns
4261 ]
4263 if _autoattach and self._pending_colargs:
4264 self._check_attach()
4266 def _check_attach(self, evt: bool = False) -> None:
4267 col_objs = [c for c in self._pending_colargs if isinstance(c, Column)]
4269 cols_w_table = [c for c in col_objs if isinstance(c.table, Table)]
4271 cols_wo_table = set(col_objs).difference(cols_w_table)
4272 if cols_wo_table:
4273 # feature #3341 - place event listeners for Column objects
4274 # such that when all those cols are attached, we autoattach.
4275 assert not evt, "Should not reach here on event call"
4277 # issue #3411 - don't do the per-column auto-attach if some of the
4278 # columns are specified as strings.
4279 has_string_cols = {
4280 c for c in self._pending_colargs if c is not None
4281 }.difference(col_objs)
4282 if not has_string_cols:
4284 def _col_attached(column: Column[Any], table: Table) -> None:
4285 # this isinstance() corresponds with the
4286 # isinstance() above; only want to count Table-bound
4287 # columns
4288 if isinstance(table, Table):
4289 cols_wo_table.discard(column)
4290 if not cols_wo_table:
4291 self._check_attach(evt=True)
4293 self._cols_wo_table = cols_wo_table
4294 for col in cols_wo_table:
4295 col._on_table_attach(_col_attached)
4296 return
4298 columns = cols_w_table
4300 tables = {c.table for c in columns}
4301 if len(tables) == 1:
4302 self._set_parent_with_dispatch(tables.pop())
4303 elif len(tables) > 1 and not self._allow_multiple_tables:
4304 table = columns[0].table
4305 others = [c for c in columns[1:] if c.table is not table]
4306 if others:
4307 # black could not format this inline
4308 other_str = ", ".join("'%s'" % c for c in others)
4309 raise exc.ArgumentError(
4310 f"Column(s) {other_str} "
4311 f"are not part of table '{table.description}'."
4312 )
4314 @util.ro_memoized_property
4315 def columns(self) -> ReadOnlyColumnCollection[str, Column[Any]]:
4316 return self._columns.as_readonly()
4318 @util.ro_memoized_property
4319 def c(self) -> ReadOnlyColumnCollection[str, Column[Any]]:
4320 return self._columns.as_readonly()
4322 def _col_expressions(
4323 self, parent: Union[Table, Column[Any]]
4324 ) -> List[Optional[Column[Any]]]:
4325 if isinstance(parent, Column):
4326 result: List[Optional[Column[Any]]] = [
4327 c for c in self._pending_colargs if isinstance(c, Column)
4328 ]
4329 assert len(result) == len(self._pending_colargs)
4330 return result
4331 else:
4332 try:
4333 return [
4334 parent.c[col] if isinstance(col, str) else col
4335 for col in self._pending_colargs
4336 ]
4337 except KeyError as ke:
4338 raise exc.ConstraintColumnNotFoundError(
4339 f"Can't create {self.__class__.__name__} "
4340 f"on table '{parent.description}': no column "
4341 f"named '{ke.args[0]}' is present."
4342 ) from ke
4344 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4345 assert isinstance(parent, (Table, Column))
4347 for col in self._col_expressions(parent):
4348 if col is not None:
4349 self._columns.add(col)
4352class ColumnCollectionConstraint(ColumnCollectionMixin, Constraint):
4353 """A constraint that proxies a ColumnCollection."""
4355 def __init__(
4356 self,
4357 *columns: _DDLColumnArgument,
4358 name: _ConstraintNameArgument = None,
4359 deferrable: Optional[bool] = None,
4360 initially: Optional[str] = None,
4361 info: Optional[_InfoType] = None,
4362 _autoattach: bool = True,
4363 _column_flag: bool = False,
4364 _gather_expressions: Optional[List[_DDLColumnArgument]] = None,
4365 **dialect_kw: Any,
4366 ) -> None:
4367 r"""
4368 :param \*columns:
4369 A sequence of column names or Column objects.
4371 :param name:
4372 Optional, the in-database name of this constraint.
4374 :param deferrable:
4375 Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
4376 issuing DDL for this constraint.
4378 :param initially:
4379 Optional string. If set, emit INITIALLY <value> when issuing DDL
4380 for this constraint.
4382 :param \**dialect_kw: other keyword arguments including
4383 dialect-specific arguments are propagated to the :class:`.Constraint`
4384 superclass.
4386 """
4387 Constraint.__init__(
4388 self,
4389 name=name,
4390 deferrable=deferrable,
4391 initially=initially,
4392 info=info,
4393 **dialect_kw,
4394 )
4395 ColumnCollectionMixin.__init__(
4396 self, *columns, _autoattach=_autoattach, _column_flag=_column_flag
4397 )
4399 columns: ReadOnlyColumnCollection[str, Column[Any]]
4400 """A :class:`_expression.ColumnCollection` representing the set of columns
4401 for this constraint.
4403 """
4405 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4406 assert isinstance(parent, (Column, Table))
4407 Constraint._set_parent(self, parent)
4408 ColumnCollectionMixin._set_parent(self, parent)
4410 def __contains__(self, x: Any) -> bool:
4411 return x in self._columns
4413 @util.deprecated(
4414 "1.4",
4415 "The :meth:`_schema.ColumnCollectionConstraint.copy` method "
4416 "is deprecated and will be removed in a future release.",
4417 )
4418 def copy(
4419 self,
4420 *,
4421 target_table: Optional[Table] = None,
4422 **kw: Any,
4423 ) -> ColumnCollectionConstraint:
4424 return self._copy(target_table=target_table, **kw)
4426 def _copy(
4427 self,
4428 *,
4429 target_table: Optional[Table] = None,
4430 **kw: Any,
4431 ) -> ColumnCollectionConstraint:
4432 # ticket #5276
4433 constraint_kwargs = {}
4434 for dialect_name in self.dialect_options:
4435 dialect_options = self.dialect_options[dialect_name]._non_defaults
4436 for (
4437 dialect_option_key,
4438 dialect_option_value,
4439 ) in dialect_options.items():
4440 constraint_kwargs[dialect_name + "_" + dialect_option_key] = (
4441 dialect_option_value
4442 )
4444 assert isinstance(self.parent, Table)
4445 c = self.__class__(
4446 name=self.name,
4447 deferrable=self.deferrable,
4448 initially=self.initially,
4449 *[
4450 _copy_expression(expr, self.parent, target_table)
4451 for expr in self._columns
4452 ],
4453 comment=self.comment,
4454 **constraint_kwargs,
4455 )
4456 return self._schema_item_copy(c)
4458 def contains_column(self, col: Column[Any]) -> bool:
4459 """Return True if this constraint contains the given column.
4461 Note that this object also contains an attribute ``.columns``
4462 which is a :class:`_expression.ColumnCollection` of
4463 :class:`_schema.Column` objects.
4465 """
4467 return self._columns.contains_column(col)
4469 def __iter__(self) -> Iterator[Column[Any]]:
4470 return iter(self._columns)
4472 def __len__(self) -> int:
4473 return len(self._columns)
4476class CheckConstraint(ColumnCollectionConstraint):
4477 """A table- or column-level CHECK constraint.
4479 Can be included in the definition of a Table or Column.
4480 """
4482 _allow_multiple_tables = True
4484 __visit_name__ = "table_or_column_check_constraint"
4486 @_document_text_coercion(
4487 "sqltext",
4488 ":class:`.CheckConstraint`",
4489 ":paramref:`.CheckConstraint.sqltext`",
4490 )
4491 def __init__(
4492 self,
4493 sqltext: _TextCoercedExpressionArgument[Any],
4494 name: _ConstraintNameArgument = None,
4495 deferrable: Optional[bool] = None,
4496 initially: Optional[str] = None,
4497 table: Optional[Table] = None,
4498 info: Optional[_InfoType] = None,
4499 _create_rule: Optional[Any] = None,
4500 _autoattach: bool = True,
4501 _type_bound: bool = False,
4502 **dialect_kw: Any,
4503 ) -> None:
4504 r"""Construct a CHECK constraint.
4506 :param sqltext:
4507 A string containing the constraint definition, which will be used
4508 verbatim, or a SQL expression construct. If given as a string,
4509 the object is converted to a :func:`_expression.text` object.
4510 If the textual
4511 string includes a colon character, escape this using a backslash::
4513 CheckConstraint(r"foo ~ E'a(?\:b|c)d")
4515 :param name:
4516 Optional, the in-database name of the constraint.
4518 :param deferrable:
4519 Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
4520 issuing DDL for this constraint.
4522 :param initially:
4523 Optional string. If set, emit INITIALLY <value> when issuing DDL
4524 for this constraint.
4526 :param info: Optional data dictionary which will be populated into the
4527 :attr:`.SchemaItem.info` attribute of this object.
4529 """
4531 self.sqltext = coercions.expect(roles.DDLExpressionRole, sqltext)
4532 columns: List[Column[Any]] = []
4533 visitors.traverse(self.sqltext, {}, {"column": columns.append})
4535 super().__init__(
4536 name=name,
4537 deferrable=deferrable,
4538 initially=initially,
4539 _create_rule=_create_rule,
4540 info=info,
4541 _type_bound=_type_bound,
4542 _autoattach=_autoattach,
4543 *columns,
4544 **dialect_kw,
4545 )
4546 if table is not None:
4547 self._set_parent_with_dispatch(table)
4549 @property
4550 def is_column_level(self) -> bool:
4551 return not isinstance(self.parent, Table)
4553 @util.deprecated(
4554 "1.4",
4555 "The :meth:`_schema.CheckConstraint.copy` method is deprecated "
4556 "and will be removed in a future release.",
4557 )
4558 def copy(
4559 self, *, target_table: Optional[Table] = None, **kw: Any
4560 ) -> CheckConstraint:
4561 return self._copy(target_table=target_table, **kw)
4563 def _copy(
4564 self, *, target_table: Optional[Table] = None, **kw: Any
4565 ) -> CheckConstraint:
4566 if target_table is not None:
4567 # note that target_table is None for the copy process of
4568 # a column-bound CheckConstraint, so this path is not reached
4569 # in that case.
4570 sqltext = _copy_expression(self.sqltext, self.table, target_table)
4571 else:
4572 sqltext = self.sqltext
4573 c = CheckConstraint(
4574 sqltext,
4575 name=self.name,
4576 initially=self.initially,
4577 deferrable=self.deferrable,
4578 _create_rule=self._create_rule,
4579 table=target_table,
4580 comment=self.comment,
4581 _autoattach=False,
4582 _type_bound=self._type_bound,
4583 )
4584 return self._schema_item_copy(c)
4587class ForeignKeyConstraint(ColumnCollectionConstraint):
4588 """A table-level FOREIGN KEY constraint.
4590 Defines a single column or composite FOREIGN KEY ... REFERENCES
4591 constraint. For a no-frills, single column foreign key, adding a
4592 :class:`_schema.ForeignKey` to the definition of a :class:`_schema.Column`
4593 is a
4594 shorthand equivalent for an unnamed, single column
4595 :class:`_schema.ForeignKeyConstraint`.
4597 Examples of foreign key configuration are in :ref:`metadata_foreignkeys`.
4599 """
4601 __visit_name__ = "foreign_key_constraint"
4603 def __init__(
4604 self,
4605 columns: _typing_Sequence[_DDLColumnArgument],
4606 refcolumns: _typing_Sequence[_DDLColumnArgument],
4607 name: _ConstraintNameArgument = None,
4608 onupdate: Optional[str] = None,
4609 ondelete: Optional[str] = None,
4610 deferrable: Optional[bool] = None,
4611 initially: Optional[str] = None,
4612 use_alter: bool = False,
4613 link_to_name: bool = False,
4614 match: Optional[str] = None,
4615 table: Optional[Table] = None,
4616 info: Optional[_InfoType] = None,
4617 comment: Optional[str] = None,
4618 **dialect_kw: Any,
4619 ) -> None:
4620 r"""Construct a composite-capable FOREIGN KEY.
4622 :param columns: A sequence of local column names. The named columns
4623 must be defined and present in the parent Table. The names should
4624 match the ``key`` given to each column (defaults to the name) unless
4625 ``link_to_name`` is True.
4627 :param refcolumns: A sequence of foreign column names or Column
4628 objects. The columns must all be located within the same Table.
4630 :param name: Optional, the in-database name of the key.
4632 :param onupdate: Optional string. If set, emit ON UPDATE <value> when
4633 issuing DDL for this constraint. Typical values include CASCADE,
4634 DELETE and RESTRICT.
4636 .. seealso::
4638 :ref:`on_update_on_delete`
4640 :param ondelete: Optional string. If set, emit ON DELETE <value> when
4641 issuing DDL for this constraint. Typical values include CASCADE,
4642 SET NULL and RESTRICT. Some dialects may allow for additional
4643 syntaxes.
4645 .. seealso::
4647 :ref:`on_update_on_delete`
4649 :param deferrable: Optional bool. If set, emit DEFERRABLE or NOT
4650 DEFERRABLE when issuing DDL for this constraint.
4652 :param initially: Optional string. If set, emit INITIALLY <value> when
4653 issuing DDL for this constraint.
4655 :param link_to_name: if True, the string name given in ``column`` is
4656 the rendered name of the referenced column, not its locally assigned
4657 ``key``.
4659 :param use_alter: If True, do not emit the DDL for this constraint as
4660 part of the CREATE TABLE definition. Instead, generate it via an
4661 ALTER TABLE statement issued after the full collection of tables
4662 have been created, and drop it via an ALTER TABLE statement before
4663 the full collection of tables are dropped.
4665 The use of :paramref:`_schema.ForeignKeyConstraint.use_alter` is
4666 particularly geared towards the case where two or more tables
4667 are established within a mutually-dependent foreign key constraint
4668 relationship; however, the :meth:`_schema.MetaData.create_all` and
4669 :meth:`_schema.MetaData.drop_all`
4670 methods will perform this resolution
4671 automatically, so the flag is normally not needed.
4673 .. seealso::
4675 :ref:`use_alter`
4677 :param match: Optional string. If set, emit MATCH <value> when issuing
4678 DDL for this constraint. Typical values include SIMPLE, PARTIAL
4679 and FULL.
4681 :param info: Optional data dictionary which will be populated into the
4682 :attr:`.SchemaItem.info` attribute of this object.
4684 :param comment: Optional string that will render an SQL comment on
4685 foreign key constraint creation.
4687 .. versionadded:: 2.0
4689 :param \**dialect_kw: Additional keyword arguments are dialect
4690 specific, and passed in the form ``<dialectname>_<argname>``. See
4691 the documentation regarding an individual dialect at
4692 :ref:`dialect_toplevel` for detail on documented arguments.
4694 """
4696 Constraint.__init__(
4697 self,
4698 name=name,
4699 deferrable=deferrable,
4700 initially=initially,
4701 info=info,
4702 comment=comment,
4703 **dialect_kw,
4704 )
4705 self.onupdate = onupdate
4706 self.ondelete = ondelete
4707 self.link_to_name = link_to_name
4708 self.use_alter = use_alter
4709 self.match = match
4711 if len(set(columns)) != len(refcolumns):
4712 if len(set(columns)) != len(columns):
4713 # e.g. FOREIGN KEY (a, a) REFERENCES r (b, c)
4714 raise exc.ArgumentError(
4715 "ForeignKeyConstraint with duplicate source column "
4716 "references are not supported."
4717 )
4718 else:
4719 # e.g. FOREIGN KEY (a) REFERENCES r (b, c)
4720 # paraphrasing
4721 # https://www.postgresql.org/docs/current/static/ddl-constraints.html
4722 raise exc.ArgumentError(
4723 "ForeignKeyConstraint number "
4724 "of constrained columns must match the number of "
4725 "referenced columns."
4726 )
4728 # standalone ForeignKeyConstraint - create
4729 # associated ForeignKey objects which will be applied to hosted
4730 # Column objects (in col.foreign_keys), either now or when attached
4731 # to the Table for string-specified names
4732 self.elements = [
4733 ForeignKey(
4734 refcol,
4735 _constraint=self,
4736 name=self.name,
4737 onupdate=self.onupdate,
4738 ondelete=self.ondelete,
4739 use_alter=self.use_alter,
4740 link_to_name=self.link_to_name,
4741 match=self.match,
4742 deferrable=self.deferrable,
4743 initially=self.initially,
4744 **self.dialect_kwargs,
4745 )
4746 for refcol in refcolumns
4747 ]
4749 ColumnCollectionMixin.__init__(self, *columns)
4750 if table is not None:
4751 if hasattr(self, "parent"):
4752 assert table is self.parent
4753 self._set_parent_with_dispatch(table)
4755 def _append_element(self, column: Column[Any], fk: ForeignKey) -> None:
4756 self._columns.add(column)
4757 self.elements.append(fk)
4759 columns: ReadOnlyColumnCollection[str, Column[Any]]
4760 """A :class:`_expression.ColumnCollection` representing the set of columns
4761 for this constraint.
4763 """
4765 elements: List[ForeignKey]
4766 """A sequence of :class:`_schema.ForeignKey` objects.
4768 Each :class:`_schema.ForeignKey`
4769 represents a single referring column/referred
4770 column pair.
4772 This collection is intended to be read-only.
4774 """
4776 @property
4777 def _elements(self) -> util.OrderedDict[str, ForeignKey]:
4778 # legacy - provide a dictionary view of (column_key, fk)
4779 return util.OrderedDict(zip(self.column_keys, self.elements))
4781 @property
4782 def _referred_schema(self) -> Optional[str]:
4783 for elem in self.elements:
4784 return elem._referred_schema
4785 else:
4786 return None
4788 @property
4789 def referred_table(self) -> Table:
4790 """The :class:`_schema.Table` object to which this
4791 :class:`_schema.ForeignKeyConstraint` references.
4793 This is a dynamically calculated attribute which may not be available
4794 if the constraint and/or parent table is not yet associated with
4795 a metadata collection that contains the referred table.
4797 """
4798 return self.elements[0].column.table
4800 def _validate_dest_table(self, table: Table) -> None:
4801 table_keys = {elem._table_key() for elem in self.elements}
4802 if None not in table_keys and len(table_keys) > 1:
4803 elem0, elem1 = sorted(table_keys)[0:2]
4804 raise exc.ArgumentError(
4805 f"ForeignKeyConstraint on "
4806 f"{table.fullname}({self._col_description}) refers to "
4807 f"multiple remote tables: {elem0} and {elem1}"
4808 )
4810 @property
4811 def column_keys(self) -> _typing_Sequence[str]:
4812 """Return a list of string keys representing the local
4813 columns in this :class:`_schema.ForeignKeyConstraint`.
4815 This list is either the original string arguments sent
4816 to the constructor of the :class:`_schema.ForeignKeyConstraint`,
4817 or if the constraint has been initialized with :class:`_schema.Column`
4818 objects, is the string ``.key`` of each element.
4820 """
4821 if hasattr(self, "parent"):
4822 return self._columns.keys()
4823 else:
4824 return [
4825 col.key if isinstance(col, ColumnElement) else str(col)
4826 for col in self._pending_colargs
4827 ]
4829 @property
4830 def _col_description(self) -> str:
4831 return ", ".join(self.column_keys)
4833 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4834 table = parent
4835 assert isinstance(table, Table)
4836 Constraint._set_parent(self, table)
4838 ColumnCollectionConstraint._set_parent(self, table)
4840 for col, fk in zip(self._columns, self.elements):
4841 if not hasattr(fk, "parent") or fk.parent is not col:
4842 fk._set_parent_with_dispatch(col)
4844 self._validate_dest_table(table)
4846 @util.deprecated(
4847 "1.4",
4848 "The :meth:`_schema.ForeignKeyConstraint.copy` method is deprecated "
4849 "and will be removed in a future release.",
4850 )
4851 def copy(
4852 self,
4853 *,
4854 schema: Optional[str] = None,
4855 target_table: Optional[Table] = None,
4856 **kw: Any,
4857 ) -> ForeignKeyConstraint:
4858 return self._copy(schema=schema, target_table=target_table, **kw)
4860 def _copy(
4861 self,
4862 *,
4863 schema: Optional[str] = None,
4864 target_table: Optional[Table] = None,
4865 **kw: Any,
4866 ) -> ForeignKeyConstraint:
4867 fkc = ForeignKeyConstraint(
4868 [x.parent.key for x in self.elements],
4869 [
4870 x._get_colspec(
4871 schema=schema,
4872 table_name=(
4873 target_table.name
4874 if target_table is not None
4875 and x._table_key() == x.parent.table.key
4876 else None
4877 ),
4878 _is_copy=True,
4879 )
4880 for x in self.elements
4881 ],
4882 name=self.name,
4883 onupdate=self.onupdate,
4884 ondelete=self.ondelete,
4885 use_alter=self.use_alter,
4886 deferrable=self.deferrable,
4887 initially=self.initially,
4888 link_to_name=self.link_to_name,
4889 match=self.match,
4890 comment=self.comment,
4891 )
4892 for self_fk, other_fk in zip(self.elements, fkc.elements):
4893 self_fk._schema_item_copy(other_fk)
4894 return self._schema_item_copy(fkc)
4897class PrimaryKeyConstraint(ColumnCollectionConstraint):
4898 """A table-level PRIMARY KEY constraint.
4900 The :class:`.PrimaryKeyConstraint` object is present automatically
4901 on any :class:`_schema.Table` object; it is assigned a set of
4902 :class:`_schema.Column` objects corresponding to those marked with
4903 the :paramref:`_schema.Column.primary_key` flag::
4905 >>> my_table = Table(
4906 ... "mytable",
4907 ... metadata,
4908 ... Column("id", Integer, primary_key=True),
4909 ... Column("version_id", Integer, primary_key=True),
4910 ... Column("data", String(50)),
4911 ... )
4912 >>> my_table.primary_key
4913 PrimaryKeyConstraint(
4914 Column('id', Integer(), table=<mytable>,
4915 primary_key=True, nullable=False),
4916 Column('version_id', Integer(), table=<mytable>,
4917 primary_key=True, nullable=False)
4918 )
4920 The primary key of a :class:`_schema.Table` can also be specified by using
4921 a :class:`.PrimaryKeyConstraint` object explicitly; in this mode of usage,
4922 the "name" of the constraint can also be specified, as well as other
4923 options which may be recognized by dialects::
4925 my_table = Table(
4926 "mytable",
4927 metadata,
4928 Column("id", Integer),
4929 Column("version_id", Integer),
4930 Column("data", String(50)),
4931 PrimaryKeyConstraint("id", "version_id", name="mytable_pk"),
4932 )
4934 The two styles of column-specification should generally not be mixed.
4935 An warning is emitted if the columns present in the
4936 :class:`.PrimaryKeyConstraint`
4937 don't match the columns that were marked as ``primary_key=True``, if both
4938 are present; in this case, the columns are taken strictly from the
4939 :class:`.PrimaryKeyConstraint` declaration, and those columns otherwise
4940 marked as ``primary_key=True`` are ignored. This behavior is intended to
4941 be backwards compatible with previous behavior.
4943 For the use case where specific options are to be specified on the
4944 :class:`.PrimaryKeyConstraint`, but the usual style of using
4945 ``primary_key=True`` flags is still desirable, an empty
4946 :class:`.PrimaryKeyConstraint` may be specified, which will take on the
4947 primary key column collection from the :class:`_schema.Table` based on the
4948 flags::
4950 my_table = Table(
4951 "mytable",
4952 metadata,
4953 Column("id", Integer, primary_key=True),
4954 Column("version_id", Integer, primary_key=True),
4955 Column("data", String(50)),
4956 PrimaryKeyConstraint(name="mytable_pk", mssql_clustered=True),
4957 )
4959 """
4961 __visit_name__ = "primary_key_constraint"
4963 def __init__(
4964 self,
4965 *columns: _DDLColumnArgument,
4966 name: Optional[str] = None,
4967 deferrable: Optional[bool] = None,
4968 initially: Optional[str] = None,
4969 info: Optional[_InfoType] = None,
4970 _implicit_generated: bool = False,
4971 **dialect_kw: Any,
4972 ) -> None:
4973 self._implicit_generated = _implicit_generated
4974 super().__init__(
4975 *columns,
4976 name=name,
4977 deferrable=deferrable,
4978 initially=initially,
4979 info=info,
4980 **dialect_kw,
4981 )
4983 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4984 table = parent
4985 assert isinstance(table, Table)
4986 super()._set_parent(table)
4988 if table.primary_key is not self:
4989 table.constraints.discard(table.primary_key)
4990 table.primary_key = self # type: ignore
4991 table.constraints.add(self)
4993 table_pks = [c for c in table.c if c.primary_key]
4994 if (
4995 self._columns
4996 and table_pks
4997 and set(table_pks) != set(self._columns)
4998 ):
4999 # black could not format these inline
5000 table_pk_str = ", ".join("'%s'" % c.name for c in table_pks)
5001 col_str = ", ".join("'%s'" % c.name for c in self._columns)
5003 util.warn(
5004 f"Table '{table.name}' specifies columns "
5005 f"{table_pk_str} as "
5006 f"primary_key=True, "
5007 f"not matching locally specified columns {col_str}; "
5008 f"setting the "
5009 f"current primary key columns to "
5010 f"{col_str}. "
5011 f"This warning "
5012 f"may become an exception in a future release"
5013 )
5014 table_pks[:] = []
5016 for c in self._columns:
5017 c.primary_key = True
5018 if c._user_defined_nullable is NULL_UNSPECIFIED:
5019 c.nullable = False
5020 if table_pks:
5021 self._columns.extend(table_pks)
5023 def _reload(self, columns: Iterable[Column[Any]]) -> None:
5024 """repopulate this :class:`.PrimaryKeyConstraint` given
5025 a set of columns.
5027 Existing columns in the table that are marked as primary_key=True
5028 are maintained.
5030 Also fires a new event.
5032 This is basically like putting a whole new
5033 :class:`.PrimaryKeyConstraint` object on the parent
5034 :class:`_schema.Table` object without actually replacing the object.
5036 The ordering of the given list of columns is also maintained; these
5037 columns will be appended to the list of columns after any which
5038 are already present.
5040 """
5041 # set the primary key flag on new columns.
5042 # note any existing PK cols on the table also have their
5043 # flag still set.
5044 for col in columns:
5045 col.primary_key = True
5047 self._columns.extend(columns)
5049 PrimaryKeyConstraint._autoincrement_column._reset(self) # type: ignore
5050 self._set_parent_with_dispatch(self.table)
5052 def _replace(self, col: Column[Any]) -> None:
5053 PrimaryKeyConstraint._autoincrement_column._reset(self) # type: ignore
5054 self._columns.replace(col)
5056 self.dispatch._sa_event_column_added_to_pk_constraint(self, col)
5058 @property
5059 def columns_autoinc_first(self) -> List[Column[Any]]:
5060 autoinc = self._autoincrement_column
5062 if autoinc is not None:
5063 return [autoinc] + [c for c in self._columns if c is not autoinc]
5064 else:
5065 return list(self._columns)
5067 @util.ro_memoized_property
5068 def _autoincrement_column(self) -> Optional[Column[int]]:
5069 def _validate_autoinc(col: Column[Any], autoinc_true: bool) -> bool:
5070 if col.type._type_affinity is None or not issubclass(
5071 col.type._type_affinity,
5072 (
5073 type_api.INTEGERTYPE._type_affinity,
5074 type_api.NUMERICTYPE._type_affinity,
5075 ),
5076 ):
5077 if autoinc_true:
5078 raise exc.ArgumentError(
5079 f"Column type {col.type} on column '{col}' is not "
5080 f"compatible with autoincrement=True"
5081 )
5082 else:
5083 return False
5084 elif (
5085 not isinstance(col.default, (type(None), Sequence))
5086 and not autoinc_true
5087 ):
5088 return False
5089 elif (
5090 col.server_default is not None
5091 and not isinstance(col.server_default, Identity)
5092 and not autoinc_true
5093 ):
5094 return False
5095 elif col.foreign_keys and col.autoincrement not in (
5096 True,
5097 "ignore_fk",
5098 ):
5099 return False
5100 return True
5102 if len(self._columns) == 1:
5103 col = list(self._columns)[0]
5105 if col.autoincrement is True:
5106 _validate_autoinc(col, True)
5107 return col
5108 elif col.autoincrement in (
5109 "auto",
5110 "ignore_fk",
5111 ) and _validate_autoinc(col, False):
5112 return col
5113 else:
5114 return None
5116 else:
5117 autoinc = None
5118 for col in self._columns:
5119 if col.autoincrement is True:
5120 _validate_autoinc(col, True)
5121 if autoinc is not None:
5122 raise exc.ArgumentError(
5123 f"Only one Column may be marked "
5124 f"autoincrement=True, found both "
5125 f"{col.name} and {autoinc.name}."
5126 )
5127 else:
5128 autoinc = col
5130 return autoinc
5133class UniqueConstraint(ColumnCollectionConstraint):
5134 """A table-level UNIQUE constraint.
5136 Defines a single column or composite UNIQUE constraint. For a no-frills,
5137 single column constraint, adding ``unique=True`` to the ``Column``
5138 definition is a shorthand equivalent for an unnamed, single column
5139 UniqueConstraint.
5140 """
5142 __visit_name__ = "unique_constraint"
5145class Index(
5146 DialectKWArgs, ColumnCollectionMixin, HasConditionalDDL, SchemaItem
5147):
5148 """A table-level INDEX.
5150 Defines a composite (one or more column) INDEX.
5152 E.g.::
5154 sometable = Table(
5155 "sometable",
5156 metadata,
5157 Column("name", String(50)),
5158 Column("address", String(100)),
5159 )
5161 Index("some_index", sometable.c.name)
5163 For a no-frills, single column index, adding
5164 :class:`_schema.Column` also supports ``index=True``::
5166 sometable = Table(
5167 "sometable", metadata, Column("name", String(50), index=True)
5168 )
5170 For a composite index, multiple columns can be specified::
5172 Index("some_index", sometable.c.name, sometable.c.address)
5174 Functional indexes are supported as well, typically by using the
5175 :data:`.func` construct in conjunction with table-bound
5176 :class:`_schema.Column` objects::
5178 Index("some_index", func.lower(sometable.c.name))
5180 An :class:`.Index` can also be manually associated with a
5181 :class:`_schema.Table`,
5182 either through inline declaration or using
5183 :meth:`_schema.Table.append_constraint`. When this approach is used,
5184 the names
5185 of the indexed columns can be specified as strings::
5187 Table(
5188 "sometable",
5189 metadata,
5190 Column("name", String(50)),
5191 Column("address", String(100)),
5192 Index("some_index", "name", "address"),
5193 )
5195 To support functional or expression-based indexes in this form, the
5196 :func:`_expression.text` construct may be used::
5198 from sqlalchemy import text
5200 Table(
5201 "sometable",
5202 metadata,
5203 Column("name", String(50)),
5204 Column("address", String(100)),
5205 Index("some_index", text("lower(name)")),
5206 )
5208 .. seealso::
5210 :ref:`schema_indexes` - General information on :class:`.Index`.
5212 :ref:`postgresql_indexes` - PostgreSQL-specific options available for
5213 the :class:`.Index` construct.
5215 :ref:`mysql_indexes` - MySQL-specific options available for the
5216 :class:`.Index` construct.
5218 :ref:`mssql_indexes` - MSSQL-specific options available for the
5219 :class:`.Index` construct.
5221 """
5223 __visit_name__ = "index"
5225 table: Optional[Table]
5226 expressions: _typing_Sequence[Union[str, ColumnElement[Any]]]
5227 _table_bound_expressions: _typing_Sequence[ColumnElement[Any]]
5229 def __init__(
5230 self,
5231 name: Optional[str],
5232 *expressions: _DDLColumnArgument,
5233 unique: bool = False,
5234 quote: Optional[bool] = None,
5235 info: Optional[_InfoType] = None,
5236 _table: Optional[Table] = None,
5237 _column_flag: bool = False,
5238 **dialect_kw: Any,
5239 ) -> None:
5240 r"""Construct an index object.
5242 :param name:
5243 The name of the index
5245 :param \*expressions:
5246 Column expressions to include in the index. The expressions
5247 are normally instances of :class:`_schema.Column`, but may also
5248 be arbitrary SQL expressions which ultimately refer to a
5249 :class:`_schema.Column`.
5251 :param unique=False:
5252 Keyword only argument; if True, create a unique index.
5254 :param quote=None:
5255 Keyword only argument; whether to apply quoting to the name of
5256 the index. Works in the same manner as that of
5257 :paramref:`_schema.Column.quote`.
5259 :param info=None: Optional data dictionary which will be populated
5260 into the :attr:`.SchemaItem.info` attribute of this object.
5262 :param \**dialect_kw: Additional keyword arguments not mentioned above
5263 are dialect specific, and passed in the form
5264 ``<dialectname>_<argname>``. See the documentation regarding an
5265 individual dialect at :ref:`dialect_toplevel` for detail on
5266 documented arguments.
5268 """
5269 self.table = table = None
5271 self.name = quoted_name.construct(name, quote)
5272 self.unique = unique
5273 if info is not None:
5274 self.info = info
5276 # TODO: consider "table" argument being public, but for
5277 # the purpose of the fix here, it starts as private.
5278 if _table is not None:
5279 table = _table
5281 self._validate_dialect_kwargs(dialect_kw)
5283 self.expressions = []
5284 # will call _set_parent() if table-bound column
5285 # objects are present
5286 ColumnCollectionMixin.__init__(
5287 self,
5288 *expressions,
5289 _column_flag=_column_flag,
5290 _gather_expressions=self.expressions,
5291 )
5292 if table is not None:
5293 self._set_parent(table)
5295 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
5296 table = parent
5297 assert isinstance(table, Table)
5298 ColumnCollectionMixin._set_parent(self, table)
5300 if self.table is not None and table is not self.table:
5301 raise exc.ArgumentError(
5302 f"Index '{self.name}' is against table "
5303 f"'{self.table.description}', and "
5304 f"cannot be associated with table '{table.description}'."
5305 )
5306 self.table = table
5307 table.indexes.add(self)
5309 expressions = self.expressions
5310 col_expressions = self._col_expressions(table)
5311 assert len(expressions) == len(col_expressions)
5313 exprs = []
5314 for expr, colexpr in zip(expressions, col_expressions):
5315 if isinstance(expr, ClauseElement):
5316 exprs.append(expr)
5317 elif colexpr is not None:
5318 exprs.append(colexpr)
5319 else:
5320 assert False
5321 self.expressions = self._table_bound_expressions = exprs
5323 def create(self, bind: _CreateDropBind, checkfirst: bool = False) -> None:
5324 """Issue a ``CREATE`` statement for this
5325 :class:`.Index`, using the given
5326 :class:`.Connection` or :class:`.Engine`` for connectivity.
5328 .. seealso::
5330 :meth:`_schema.MetaData.create_all`.
5332 """
5333 bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
5335 def drop(self, bind: _CreateDropBind, checkfirst: bool = False) -> None:
5336 """Issue a ``DROP`` statement for this
5337 :class:`.Index`, using the given
5338 :class:`.Connection` or :class:`.Engine` for connectivity.
5340 .. seealso::
5342 :meth:`_schema.MetaData.drop_all`.
5344 """
5345 bind._run_ddl_visitor(ddl.SchemaDropper, self, checkfirst=checkfirst)
5347 def __repr__(self) -> str:
5348 exprs: _typing_Sequence[Any] # noqa: F842
5350 return "Index(%s)" % (
5351 ", ".join(
5352 [repr(self.name)]
5353 + [repr(e) for e in self.expressions]
5354 + (self.unique and ["unique=True"] or [])
5355 )
5356 )
5359_NamingSchemaCallable = Union[
5360 Callable[[Constraint, Table], str],
5361 Callable[[Index, Table], str],
5362]
5363_NamingSchemaDirective = Union[str, _NamingSchemaCallable]
5366class _NamingSchemaTD(TypedDict, total=False):
5367 fk: _NamingSchemaDirective
5368 pk: _NamingSchemaDirective
5369 ix: _NamingSchemaDirective
5370 ck: _NamingSchemaDirective
5371 uq: _NamingSchemaDirective
5374_NamingSchemaParameter = Union[
5375 # it seems like the TypedDict here is useful for pylance typeahead,
5376 # and not much else
5377 _NamingSchemaTD,
5378 # there is no form that allows Union[Type[Any], str] to work in all
5379 # cases, including breaking out Mapping[] entries for each combination
5380 # even, therefore keys must be `Any` (see #10264)
5381 Mapping[Any, _NamingSchemaDirective],
5382]
5385DEFAULT_NAMING_CONVENTION: _NamingSchemaParameter = util.immutabledict(
5386 {"ix": "ix_%(column_0_label)s"}
5387)
5390class MetaData(HasSchemaAttr):
5391 """A collection of :class:`_schema.Table`
5392 objects and their associated schema
5393 constructs.
5395 Holds a collection of :class:`_schema.Table` objects as well as
5396 an optional binding to an :class:`_engine.Engine` or
5397 :class:`_engine.Connection`. If bound, the :class:`_schema.Table` objects
5398 in the collection and their columns may participate in implicit SQL
5399 execution.
5401 The :class:`_schema.Table` objects themselves are stored in the
5402 :attr:`_schema.MetaData.tables` dictionary.
5404 :class:`_schema.MetaData` is a thread-safe object for read operations.
5405 Construction of new tables within a single :class:`_schema.MetaData`
5406 object,
5407 either explicitly or via reflection, may not be completely thread-safe.
5409 .. seealso::
5411 :ref:`metadata_describing` - Introduction to database metadata
5413 """
5415 __visit_name__ = "metadata"
5417 def __init__(
5418 self,
5419 schema: Optional[str] = None,
5420 quote_schema: Optional[bool] = None,
5421 naming_convention: Optional[_NamingSchemaParameter] = None,
5422 info: Optional[_InfoType] = None,
5423 ) -> None:
5424 """Create a new MetaData object.
5426 :param schema:
5427 The default schema to use for the :class:`_schema.Table`,
5428 :class:`.Sequence`, and potentially other objects associated with
5429 this :class:`_schema.MetaData`. Defaults to ``None``.
5431 .. seealso::
5433 :ref:`schema_metadata_schema_name` - details on how the
5434 :paramref:`_schema.MetaData.schema` parameter is used.
5436 :paramref:`_schema.Table.schema`
5438 :paramref:`.Sequence.schema`
5440 :param quote_schema:
5441 Sets the ``quote_schema`` flag for those :class:`_schema.Table`,
5442 :class:`.Sequence`, and other objects which make usage of the
5443 local ``schema`` name.
5445 :param info: Optional data dictionary which will be populated into the
5446 :attr:`.SchemaItem.info` attribute of this object.
5448 :param naming_convention: a dictionary referring to values which
5449 will establish default naming conventions for :class:`.Constraint`
5450 and :class:`.Index` objects, for those objects which are not given
5451 a name explicitly.
5453 The keys of this dictionary may be:
5455 * a constraint or Index class, e.g. the :class:`.UniqueConstraint`,
5456 :class:`_schema.ForeignKeyConstraint` class, the :class:`.Index`
5457 class
5459 * a string mnemonic for one of the known constraint classes;
5460 ``"fk"``, ``"pk"``, ``"ix"``, ``"ck"``, ``"uq"`` for foreign key,
5461 primary key, index, check, and unique constraint, respectively.
5463 * the string name of a user-defined "token" that can be used
5464 to define new naming tokens.
5466 The values associated with each "constraint class" or "constraint
5467 mnemonic" key are string naming templates, such as
5468 ``"uq_%(table_name)s_%(column_0_name)s"``,
5469 which describe how the name should be composed. The values
5470 associated with user-defined "token" keys should be callables of the
5471 form ``fn(constraint, table)``, which accepts the constraint/index
5472 object and :class:`_schema.Table` as arguments, returning a string
5473 result.
5475 The built-in names are as follows, some of which may only be
5476 available for certain types of constraint:
5478 * ``%(table_name)s`` - the name of the :class:`_schema.Table`
5479 object
5480 associated with the constraint.
5482 * ``%(referred_table_name)s`` - the name of the
5483 :class:`_schema.Table`
5484 object associated with the referencing target of a
5485 :class:`_schema.ForeignKeyConstraint`.
5487 * ``%(column_0_name)s`` - the name of the :class:`_schema.Column`
5488 at
5489 index position "0" within the constraint.
5491 * ``%(column_0N_name)s`` - the name of all :class:`_schema.Column`
5492 objects in order within the constraint, joined without a
5493 separator.
5495 * ``%(column_0_N_name)s`` - the name of all
5496 :class:`_schema.Column`
5497 objects in order within the constraint, joined with an
5498 underscore as a separator.
5500 * ``%(column_0_label)s``, ``%(column_0N_label)s``,
5501 ``%(column_0_N_label)s`` - the label of either the zeroth
5502 :class:`_schema.Column` or all :class:`.Columns`, separated with
5503 or without an underscore
5505 * ``%(column_0_key)s``, ``%(column_0N_key)s``,
5506 ``%(column_0_N_key)s`` - the key of either the zeroth
5507 :class:`_schema.Column` or all :class:`.Columns`, separated with
5508 or without an underscore
5510 * ``%(referred_column_0_name)s``, ``%(referred_column_0N_name)s``
5511 ``%(referred_column_0_N_name)s``, ``%(referred_column_0_key)s``,
5512 ``%(referred_column_0N_key)s``, ... column tokens which
5513 render the names/keys/labels of columns that are referenced
5514 by a :class:`_schema.ForeignKeyConstraint`.
5516 * ``%(constraint_name)s`` - a special key that refers to the
5517 existing name given to the constraint. When this key is
5518 present, the :class:`.Constraint` object's existing name will be
5519 replaced with one that is composed from template string that
5520 uses this token. When this token is present, it is required that
5521 the :class:`.Constraint` is given an explicit name ahead of time.
5523 * user-defined: any additional token may be implemented by passing
5524 it along with a ``fn(constraint, table)`` callable to the
5525 naming_convention dictionary.
5527 .. versionadded:: 1.3.0 - added new ``%(column_0N_name)s``,
5528 ``%(column_0_N_name)s``, and related tokens that produce
5529 concatenations of names, keys, or labels for all columns referred
5530 to by a given constraint.
5532 .. seealso::
5534 :ref:`constraint_naming_conventions` - for detailed usage
5535 examples.
5537 """
5538 if schema is not None and not isinstance(schema, str):
5539 raise exc.ArgumentError(
5540 "expected schema argument to be a string, "
5541 f"got {type(schema)}."
5542 )
5543 self.tables = util.FacadeDict()
5544 self.schema = quoted_name.construct(schema, quote_schema)
5545 self.naming_convention = (
5546 naming_convention
5547 if naming_convention
5548 else DEFAULT_NAMING_CONVENTION
5549 )
5550 if info:
5551 self.info = info
5552 self._schemas: Set[str] = set()
5553 self._sequences: Dict[str, Sequence] = {}
5554 self._fk_memos: Dict[Tuple[str, Optional[str]], List[ForeignKey]] = (
5555 collections.defaultdict(list)
5556 )
5558 tables: util.FacadeDict[str, Table]
5559 """A dictionary of :class:`_schema.Table`
5560 objects keyed to their name or "table key".
5562 The exact key is that determined by the :attr:`_schema.Table.key`
5563 attribute;
5564 for a table with no :attr:`_schema.Table.schema` attribute,
5565 this is the same
5566 as :attr:`_schema.Table.name`. For a table with a schema,
5567 it is typically of the
5568 form ``schemaname.tablename``.
5570 .. seealso::
5572 :attr:`_schema.MetaData.sorted_tables`
5574 """
5576 def __repr__(self) -> str:
5577 return "MetaData()"
5579 def __contains__(self, table_or_key: Union[str, Table]) -> bool:
5580 if not isinstance(table_or_key, str):
5581 table_or_key = table_or_key.key
5582 return table_or_key in self.tables
5584 def _add_table(
5585 self, name: str, schema: Optional[str], table: Table
5586 ) -> None:
5587 key = _get_table_key(name, schema)
5588 self.tables._insert_item(key, table)
5589 if schema:
5590 self._schemas.add(schema)
5592 def _remove_table(self, name: str, schema: Optional[str]) -> None:
5593 key = _get_table_key(name, schema)
5594 removed = dict.pop(self.tables, key, None)
5595 if removed is not None:
5596 for fk in removed.foreign_keys:
5597 fk._remove_from_metadata(self)
5598 if self._schemas:
5599 self._schemas = {
5600 t.schema for t in self.tables.values() if t.schema is not None
5601 }
5603 def __getstate__(self) -> Dict[str, Any]:
5604 return {
5605 "tables": self.tables,
5606 "schema": self.schema,
5607 "schemas": self._schemas,
5608 "sequences": self._sequences,
5609 "fk_memos": self._fk_memos,
5610 "naming_convention": self.naming_convention,
5611 }
5613 def __setstate__(self, state: Dict[str, Any]) -> None:
5614 self.tables = state["tables"]
5615 self.schema = state["schema"]
5616 self.naming_convention = state["naming_convention"]
5617 self._sequences = state["sequences"]
5618 self._schemas = state["schemas"]
5619 self._fk_memos = state["fk_memos"]
5621 def clear(self) -> None:
5622 """Clear all Table objects from this MetaData."""
5624 dict.clear(self.tables)
5625 self._schemas.clear()
5626 self._fk_memos.clear()
5628 def remove(self, table: Table) -> None:
5629 """Remove the given Table object from this MetaData."""
5631 self._remove_table(table.name, table.schema)
5633 @property
5634 def sorted_tables(self) -> List[Table]:
5635 """Returns a list of :class:`_schema.Table` objects sorted in order of
5636 foreign key dependency.
5638 The sorting will place :class:`_schema.Table`
5639 objects that have dependencies
5640 first, before the dependencies themselves, representing the
5641 order in which they can be created. To get the order in which
5642 the tables would be dropped, use the ``reversed()`` Python built-in.
5644 .. warning::
5646 The :attr:`.MetaData.sorted_tables` attribute cannot by itself
5647 accommodate automatic resolution of dependency cycles between
5648 tables, which are usually caused by mutually dependent foreign key
5649 constraints. When these cycles are detected, the foreign keys
5650 of these tables are omitted from consideration in the sort.
5651 A warning is emitted when this condition occurs, which will be an
5652 exception raise in a future release. Tables which are not part
5653 of the cycle will still be returned in dependency order.
5655 To resolve these cycles, the
5656 :paramref:`_schema.ForeignKeyConstraint.use_alter` parameter may be
5657 applied to those constraints which create a cycle. Alternatively,
5658 the :func:`_schema.sort_tables_and_constraints` function will
5659 automatically return foreign key constraints in a separate
5660 collection when cycles are detected so that they may be applied
5661 to a schema separately.
5663 .. versionchanged:: 1.3.17 - a warning is emitted when
5664 :attr:`.MetaData.sorted_tables` cannot perform a proper sort
5665 due to cyclical dependencies. This will be an exception in a
5666 future release. Additionally, the sort will continue to return
5667 other tables not involved in the cycle in dependency order which
5668 was not the case previously.
5670 .. seealso::
5672 :func:`_schema.sort_tables`
5674 :func:`_schema.sort_tables_and_constraints`
5676 :attr:`_schema.MetaData.tables`
5678 :meth:`_reflection.Inspector.get_table_names`
5680 :meth:`_reflection.Inspector.get_sorted_table_and_fkc_names`
5683 """
5684 return ddl.sort_tables(
5685 sorted(self.tables.values(), key=lambda t: t.key) # type: ignore
5686 )
5688 # overload needed to work around mypy this mypy
5689 # https://github.com/python/mypy/issues/17093
5690 @overload
5691 def reflect(
5692 self,
5693 bind: Engine,
5694 schema: Optional[str] = ...,
5695 views: bool = ...,
5696 only: Union[
5697 _typing_Sequence[str], Callable[[str, MetaData], bool], None
5698 ] = ...,
5699 extend_existing: bool = ...,
5700 autoload_replace: bool = ...,
5701 resolve_fks: bool = ...,
5702 **dialect_kwargs: Any,
5703 ) -> None: ...
5705 @overload
5706 def reflect(
5707 self,
5708 bind: Connection,
5709 schema: Optional[str] = ...,
5710 views: bool = ...,
5711 only: Union[
5712 _typing_Sequence[str], Callable[[str, MetaData], bool], None
5713 ] = ...,
5714 extend_existing: bool = ...,
5715 autoload_replace: bool = ...,
5716 resolve_fks: bool = ...,
5717 **dialect_kwargs: Any,
5718 ) -> None: ...
5720 @util.preload_module("sqlalchemy.engine.reflection")
5721 def reflect(
5722 self,
5723 bind: Union[Engine, Connection],
5724 schema: Optional[str] = None,
5725 views: bool = False,
5726 only: Union[
5727 _typing_Sequence[str], Callable[[str, MetaData], bool], None
5728 ] = None,
5729 extend_existing: bool = False,
5730 autoload_replace: bool = True,
5731 resolve_fks: bool = True,
5732 **dialect_kwargs: Any,
5733 ) -> None:
5734 r"""Load all available table definitions from the database.
5736 Automatically creates ``Table`` entries in this ``MetaData`` for any
5737 table available in the database but not yet present in the
5738 ``MetaData``. May be called multiple times to pick up tables recently
5739 added to the database, however no special action is taken if a table
5740 in this ``MetaData`` no longer exists in the database.
5742 :param bind:
5743 A :class:`.Connection` or :class:`.Engine` used to access the
5744 database.
5746 :param schema:
5747 Optional, query and reflect tables from an alternate schema.
5748 If None, the schema associated with this :class:`_schema.MetaData`
5749 is used, if any.
5751 :param views:
5752 If True, also reflect views (materialized and plain).
5754 :param only:
5755 Optional. Load only a sub-set of available named tables. May be
5756 specified as a sequence of names or a callable.
5758 If a sequence of names is provided, only those tables will be
5759 reflected. An error is raised if a table is requested but not
5760 available. Named tables already present in this ``MetaData`` are
5761 ignored.
5763 If a callable is provided, it will be used as a boolean predicate to
5764 filter the list of potential table names. The callable is called
5765 with a table name and this ``MetaData`` instance as positional
5766 arguments and should return a true value for any table to reflect.
5768 :param extend_existing: Passed along to each :class:`_schema.Table` as
5769 :paramref:`_schema.Table.extend_existing`.
5771 :param autoload_replace: Passed along to each :class:`_schema.Table`
5772 as
5773 :paramref:`_schema.Table.autoload_replace`.
5775 :param resolve_fks: if True, reflect :class:`_schema.Table`
5776 objects linked
5777 to :class:`_schema.ForeignKey` objects located in each
5778 :class:`_schema.Table`.
5779 For :meth:`_schema.MetaData.reflect`,
5780 this has the effect of reflecting
5781 related tables that might otherwise not be in the list of tables
5782 being reflected, for example if the referenced table is in a
5783 different schema or is omitted via the
5784 :paramref:`.MetaData.reflect.only` parameter. When False,
5785 :class:`_schema.ForeignKey` objects are not followed to the
5786 :class:`_schema.Table`
5787 in which they link, however if the related table is also part of the
5788 list of tables that would be reflected in any case, the
5789 :class:`_schema.ForeignKey` object will still resolve to its related
5790 :class:`_schema.Table` after the :meth:`_schema.MetaData.reflect`
5791 operation is
5792 complete. Defaults to True.
5794 .. versionadded:: 1.3.0
5796 .. seealso::
5798 :paramref:`_schema.Table.resolve_fks`
5800 :param \**dialect_kwargs: Additional keyword arguments not mentioned
5801 above are dialect specific, and passed in the form
5802 ``<dialectname>_<argname>``. See the documentation regarding an
5803 individual dialect at :ref:`dialect_toplevel` for detail on
5804 documented arguments.
5806 .. seealso::
5808 :ref:`metadata_reflection_toplevel`
5810 :meth:`_events.DDLEvents.column_reflect` - Event used to customize
5811 the reflected columns. Usually used to generalize the types using
5812 :meth:`_types.TypeEngine.as_generic`
5814 :ref:`metadata_reflection_dbagnostic_types` - describes how to
5815 reflect tables using general types.
5817 """
5819 with inspection.inspect(bind)._inspection_context() as insp:
5820 reflect_opts: Any = {
5821 "autoload_with": insp,
5822 "extend_existing": extend_existing,
5823 "autoload_replace": autoload_replace,
5824 "resolve_fks": resolve_fks,
5825 "_extend_on": set(),
5826 }
5828 reflect_opts.update(dialect_kwargs)
5830 if schema is None:
5831 schema = self.schema
5833 if schema is not None:
5834 reflect_opts["schema"] = schema
5836 kind = util.preloaded.engine_reflection.ObjectKind.TABLE
5837 available: util.OrderedSet[str] = util.OrderedSet(
5838 insp.get_table_names(schema, **dialect_kwargs)
5839 )
5840 if views:
5841 kind = util.preloaded.engine_reflection.ObjectKind.ANY
5842 available.update(insp.get_view_names(schema, **dialect_kwargs))
5843 try:
5844 available.update(
5845 insp.get_materialized_view_names(
5846 schema, **dialect_kwargs
5847 )
5848 )
5849 except NotImplementedError:
5850 pass
5852 if schema is not None:
5853 available_w_schema: util.OrderedSet[str] = util.OrderedSet(
5854 [f"{schema}.{name}" for name in available]
5855 )
5856 else:
5857 available_w_schema = available
5859 current = set(self.tables)
5861 if only is None:
5862 load = [
5863 name
5864 for name, schname in zip(available, available_w_schema)
5865 if extend_existing or schname not in current
5866 ]
5867 elif callable(only):
5868 load = [
5869 name
5870 for name, schname in zip(available, available_w_schema)
5871 if (extend_existing or schname not in current)
5872 and only(name, self)
5873 ]
5874 else:
5875 missing = [name for name in only if name not in available]
5876 if missing:
5877 s = schema and (" schema '%s'" % schema) or ""
5878 missing_str = ", ".join(missing)
5879 raise exc.InvalidRequestError(
5880 f"Could not reflect: requested table(s) not available "
5881 f"in {bind.engine!r}{s}: ({missing_str})"
5882 )
5883 load = [
5884 name
5885 for name in only
5886 if extend_existing or name not in current
5887 ]
5888 # pass the available tables so the inspector can
5889 # choose to ignore the filter_names
5890 _reflect_info = insp._get_reflection_info(
5891 schema=schema,
5892 filter_names=load,
5893 available=available,
5894 kind=kind,
5895 scope=util.preloaded.engine_reflection.ObjectScope.ANY,
5896 **dialect_kwargs,
5897 )
5898 reflect_opts["_reflect_info"] = _reflect_info
5900 for name in load:
5901 try:
5902 Table(name, self, **reflect_opts)
5903 except exc.UnreflectableTableError as uerr:
5904 util.warn(f"Skipping table {name}: {uerr}")
5906 def create_all(
5907 self,
5908 bind: _CreateDropBind,
5909 tables: Optional[_typing_Sequence[Table]] = None,
5910 checkfirst: bool = True,
5911 ) -> None:
5912 """Create all tables stored in this metadata.
5914 Conditional by default, will not attempt to recreate tables already
5915 present in the target database.
5917 :param bind:
5918 A :class:`.Connection` or :class:`.Engine` used to access the
5919 database.
5921 :param tables:
5922 Optional list of ``Table`` objects, which is a subset of the total
5923 tables in the ``MetaData`` (others are ignored).
5925 :param checkfirst:
5926 Defaults to True, don't issue CREATEs for tables already present
5927 in the target database.
5929 """
5930 bind._run_ddl_visitor(
5931 ddl.SchemaGenerator, self, checkfirst=checkfirst, tables=tables
5932 )
5934 def drop_all(
5935 self,
5936 bind: _CreateDropBind,
5937 tables: Optional[_typing_Sequence[Table]] = None,
5938 checkfirst: bool = True,
5939 ) -> None:
5940 """Drop all tables stored in this metadata.
5942 Conditional by default, will not attempt to drop tables not present in
5943 the target database.
5945 :param bind:
5946 A :class:`.Connection` or :class:`.Engine` used to access the
5947 database.
5949 :param tables:
5950 Optional list of ``Table`` objects, which is a subset of the
5951 total tables in the ``MetaData`` (others are ignored).
5953 :param checkfirst:
5954 Defaults to True, only issue DROPs for tables confirmed to be
5955 present in the target database.
5957 """
5958 bind._run_ddl_visitor(
5959 ddl.SchemaDropper, self, checkfirst=checkfirst, tables=tables
5960 )
5963class Computed(FetchedValue, SchemaItem):
5964 """Defines a generated column, i.e. "GENERATED ALWAYS AS" syntax.
5966 The :class:`.Computed` construct is an inline construct added to the
5967 argument list of a :class:`_schema.Column` object::
5969 from sqlalchemy import Computed
5971 Table(
5972 "square",
5973 metadata_obj,
5974 Column("side", Float, nullable=False),
5975 Column("area", Float, Computed("side * side")),
5976 )
5978 See the linked documentation below for complete details.
5980 .. versionadded:: 1.3.11
5982 .. seealso::
5984 :ref:`computed_ddl`
5986 """
5988 __visit_name__ = "computed_column"
5990 column: Optional[Column[Any]]
5992 @_document_text_coercion(
5993 "sqltext", ":class:`.Computed`", ":paramref:`.Computed.sqltext`"
5994 )
5995 def __init__(
5996 self, sqltext: _DDLColumnArgument, persisted: Optional[bool] = None
5997 ) -> None:
5998 """Construct a GENERATED ALWAYS AS DDL construct to accompany a
5999 :class:`_schema.Column`.
6001 :param sqltext:
6002 A string containing the column generation expression, which will be
6003 used verbatim, or a SQL expression construct, such as a
6004 :func:`_expression.text`
6005 object. If given as a string, the object is converted to a
6006 :func:`_expression.text` object.
6008 :param persisted:
6009 Optional, controls how this column should be persisted by the
6010 database. Possible values are:
6012 * ``None``, the default, it will use the default persistence
6013 defined by the database.
6014 * ``True``, will render ``GENERATED ALWAYS AS ... STORED``, or the
6015 equivalent for the target database if supported.
6016 * ``False``, will render ``GENERATED ALWAYS AS ... VIRTUAL``, or
6017 the equivalent for the target database if supported.
6019 Specifying ``True`` or ``False`` may raise an error when the DDL
6020 is emitted to the target database if the database does not support
6021 that persistence option. Leaving this parameter at its default
6022 of ``None`` is guaranteed to succeed for all databases that support
6023 ``GENERATED ALWAYS AS``.
6025 """
6026 self.sqltext = coercions.expect(roles.DDLExpressionRole, sqltext)
6027 self.persisted = persisted
6028 self.column = None
6030 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
6031 assert isinstance(parent, Column)
6033 if not isinstance(
6034 parent.server_default, (type(None), Computed)
6035 ) or not isinstance(parent.server_onupdate, (type(None), Computed)):
6036 raise exc.ArgumentError(
6037 "A generated column cannot specify a server_default or a "
6038 "server_onupdate argument"
6039 )
6040 self.column = parent
6041 parent.computed = self
6042 self.column.server_onupdate = self
6043 self.column.server_default = self
6045 def _as_for_update(self, for_update: bool) -> FetchedValue:
6046 return self
6048 @util.deprecated(
6049 "1.4",
6050 "The :meth:`_schema.Computed.copy` method is deprecated "
6051 "and will be removed in a future release.",
6052 )
6053 def copy(
6054 self, *, target_table: Optional[Table] = None, **kw: Any
6055 ) -> Computed:
6056 return self._copy(target_table=target_table, **kw)
6058 def _copy(
6059 self, *, target_table: Optional[Table] = None, **kw: Any
6060 ) -> Computed:
6061 sqltext = _copy_expression(
6062 self.sqltext,
6063 self.column.table if self.column is not None else None,
6064 target_table,
6065 )
6066 g = Computed(sqltext, persisted=self.persisted)
6068 return self._schema_item_copy(g)
6071class Identity(IdentityOptions, FetchedValue, SchemaItem):
6072 """Defines an identity column, i.e. "GENERATED { ALWAYS | BY DEFAULT }
6073 AS IDENTITY" syntax.
6075 The :class:`.Identity` construct is an inline construct added to the
6076 argument list of a :class:`_schema.Column` object::
6078 from sqlalchemy import Identity
6080 Table(
6081 "foo",
6082 metadata_obj,
6083 Column("id", Integer, Identity()),
6084 Column("description", Text),
6085 )
6087 See the linked documentation below for complete details.
6089 .. versionadded:: 1.4
6091 .. seealso::
6093 :ref:`identity_ddl`
6095 """
6097 __visit_name__ = "identity_column"
6099 is_identity = True
6101 def __init__(
6102 self,
6103 always: bool = False,
6104 on_null: Optional[bool] = None,
6105 start: Optional[int] = None,
6106 increment: Optional[int] = None,
6107 minvalue: Optional[int] = None,
6108 maxvalue: Optional[int] = None,
6109 nominvalue: Optional[bool] = None,
6110 nomaxvalue: Optional[bool] = None,
6111 cycle: Optional[bool] = None,
6112 cache: Optional[int] = None,
6113 order: Optional[bool] = None,
6114 ) -> None:
6115 """Construct a GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY DDL
6116 construct to accompany a :class:`_schema.Column`.
6118 See the :class:`.Sequence` documentation for a complete description
6119 of most parameters.
6121 .. note::
6122 MSSQL supports this construct as the preferred alternative to
6123 generate an IDENTITY on a column, but it uses non standard
6124 syntax that only support :paramref:`_schema.Identity.start`
6125 and :paramref:`_schema.Identity.increment`.
6126 All other parameters are ignored.
6128 :param always:
6129 A boolean, that indicates the type of identity column.
6130 If ``False`` is specified, the default, then the user-specified
6131 value takes precedence.
6132 If ``True`` is specified, a user-specified value is not accepted (
6133 on some backends, like PostgreSQL, OVERRIDING SYSTEM VALUE, or
6134 similar, may be specified in an INSERT to override the sequence
6135 value).
6136 Some backends also have a default value for this parameter,
6137 ``None`` can be used to omit rendering this part in the DDL. It
6138 will be treated as ``False`` if a backend does not have a default
6139 value.
6141 :param on_null:
6142 Set to ``True`` to specify ON NULL in conjunction with a
6143 ``always=False`` identity column. This option is only supported on
6144 some backends, like Oracle Database.
6146 :param start: the starting index of the sequence.
6147 :param increment: the increment value of the sequence.
6148 :param minvalue: the minimum value of the sequence.
6149 :param maxvalue: the maximum value of the sequence.
6150 :param nominvalue: no minimum value of the sequence.
6151 :param nomaxvalue: no maximum value of the sequence.
6152 :param cycle: allows the sequence to wrap around when the maxvalue
6153 or minvalue has been reached.
6154 :param cache: optional integer value; number of future values in the
6155 sequence which are calculated in advance.
6156 :param order: optional boolean value; if true, renders the
6157 ORDER keyword.
6159 """
6160 IdentityOptions.__init__(
6161 self,
6162 start=start,
6163 increment=increment,
6164 minvalue=minvalue,
6165 maxvalue=maxvalue,
6166 nominvalue=nominvalue,
6167 nomaxvalue=nomaxvalue,
6168 cycle=cycle,
6169 cache=cache,
6170 order=order,
6171 )
6172 self.always = always
6173 self.on_null = on_null
6174 self.column = None
6176 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
6177 assert isinstance(parent, Column)
6178 if not isinstance(
6179 parent.server_default, (type(None), Identity)
6180 ) or not isinstance(parent.server_onupdate, type(None)):
6181 raise exc.ArgumentError(
6182 "A column with an Identity object cannot specify a "
6183 "server_default or a server_onupdate argument"
6184 )
6185 if parent.autoincrement is False:
6186 raise exc.ArgumentError(
6187 "A column with an Identity object cannot specify "
6188 "autoincrement=False"
6189 )
6190 self.column = parent
6192 parent.identity = self
6193 if parent._user_defined_nullable is NULL_UNSPECIFIED:
6194 parent.nullable = False
6196 parent.server_default = self
6198 def _as_for_update(self, for_update: bool) -> FetchedValue:
6199 return self
6201 @util.deprecated(
6202 "1.4",
6203 "The :meth:`_schema.Identity.copy` method is deprecated "
6204 "and will be removed in a future release.",
6205 )
6206 def copy(self, **kw: Any) -> Identity:
6207 return self._copy(**kw)
6209 def _copy(self, **kw: Any) -> Identity:
6210 i = Identity(
6211 always=self.always,
6212 on_null=self.on_null,
6213 start=self.start,
6214 increment=self.increment,
6215 minvalue=self.minvalue,
6216 maxvalue=self.maxvalue,
6217 nominvalue=self.nominvalue,
6218 nomaxvalue=self.nomaxvalue,
6219 cycle=self.cycle,
6220 cache=self.cache,
6221 order=self.order,
6222 )
6224 return self._schema_item_copy(i)