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-2025 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"""
31from __future__ import annotations
33from abc import ABC
34import collections
35from enum import Enum
36import operator
37import typing
38from typing import Any
39from typing import Callable
40from typing import cast
41from typing import Collection
42from typing import Dict
43from typing import Final
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 Protocol
52from typing import Sequence as _typing_Sequence
53from typing import Set
54from typing import Tuple
55from typing import TYPE_CHECKING
56from typing import TypedDict
57from typing import TypeVar
58from typing import Union
60from . import coercions
61from . import ddl
62from . import roles
63from . import type_api
64from . import visitors
65from .base import _DefaultDescriptionTuple
66from .base import _NoArg
67from .base import _NoneName
68from .base import _SentinelColumnCharacterization
69from .base import _SentinelDefaultCharacterization
70from .base import DedupeColumnCollection
71from .base import DialectKWArgs
72from .base import Executable
73from .base import SchemaEventTarget as SchemaEventTarget
74from .base import SchemaVisitable as SchemaVisitable
75from .coercions import _document_text_coercion
76from .elements import ClauseElement
77from .elements import ColumnClause
78from .elements import ColumnElement
79from .elements import quoted_name
80from .elements import TextClause
81from .selectable import TableClause
82from .type_api import to_instance
83from .visitors import ExternallyTraversible
84from .. import event
85from .. import exc
86from .. import inspection
87from .. import util
88from ..util import HasMemoized
89from ..util.typing import Literal
90from ..util.typing import Self
91from ..util.typing import TypeGuard
93if typing.TYPE_CHECKING:
94 from ._typing import _AutoIncrementType
95 from ._typing import _CreateDropBind
96 from ._typing import _DDLColumnArgument
97 from ._typing import _DDLColumnReferenceArgument
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]] # type: ignore[assignment]
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 .. seealso::
688 :paramref:`.MetaData.reflect.resolve_fks`
691 :param info: Optional data dictionary which will be populated into the
692 :attr:`.SchemaItem.info` attribute of this object.
694 :param keep_existing: When ``True``, indicates that if this Table
695 is already present in the given :class:`_schema.MetaData`, ignore
696 further arguments within the constructor to the existing
697 :class:`_schema.Table`, and return the :class:`_schema.Table`
698 object as
699 originally created. This is to allow a function that wishes
700 to define a new :class:`_schema.Table` on first call, but on
701 subsequent calls will return the same :class:`_schema.Table`,
702 without any of the declarations (particularly constraints)
703 being applied a second time.
705 If :paramref:`_schema.Table.extend_existing` or
706 :paramref:`_schema.Table.keep_existing` are not set,
707 and the given name
708 of the new :class:`_schema.Table` refers to a :class:`_schema.Table`
709 that is
710 already present in the target :class:`_schema.MetaData` collection,
711 and
712 this :class:`_schema.Table`
713 specifies additional columns or other constructs
714 or flags that modify the table's state, an
715 error is raised. The purpose of these two mutually-exclusive flags
716 is to specify what action should be taken when a
717 :class:`_schema.Table`
718 is specified that matches an existing :class:`_schema.Table`,
719 yet specifies
720 additional constructs.
722 .. seealso::
724 :paramref:`_schema.Table.extend_existing`
726 :param listeners: A list of tuples of the form ``(<eventname>, <fn>)``
727 which will be passed to :func:`.event.listen` upon construction.
728 This alternate hook to :func:`.event.listen` allows the establishment
729 of a listener function specific to this :class:`_schema.Table` before
730 the "autoload" process begins. Historically this has been intended
731 for use with the :meth:`.DDLEvents.column_reflect` event, however
732 note that this event hook may now be associated with the
733 :class:`_schema.MetaData` object directly::
735 def listen_for_reflect(table, column_info):
736 "handle the column reflection event"
737 # ...
740 t = Table(
741 "sometable",
742 autoload_with=engine,
743 listeners=[("column_reflect", listen_for_reflect)],
744 )
746 .. seealso::
748 :meth:`_events.DDLEvents.column_reflect`
750 :param must_exist: When ``True``, indicates that this Table must already
751 be present in the given :class:`_schema.MetaData` collection, else
752 an exception is raised.
754 :param prefixes:
755 A list of strings to insert after CREATE in the CREATE TABLE
756 statement. They will be separated by spaces.
758 :param quote: Force quoting of this table's name on or off, corresponding
759 to ``True`` or ``False``. When left at its default of ``None``,
760 the column identifier will be quoted according to whether the name is
761 case sensitive (identifiers with at least one upper case character are
762 treated as case sensitive), or if it's a reserved word. This flag
763 is only needed to force quoting of a reserved word which is not known
764 by the SQLAlchemy dialect.
766 .. note:: setting this flag to ``False`` will not provide
767 case-insensitive behavior for table reflection; table reflection
768 will always search for a mixed-case name in a case sensitive
769 fashion. Case insensitive names are specified in SQLAlchemy only
770 by stating the name with all lower case characters.
772 :param quote_schema: same as 'quote' but applies to the schema identifier.
774 :param schema: The schema name for this table, which is required if
775 the table resides in a schema other than the default selected schema
776 for the engine's database connection. Defaults to ``None``.
778 If the owning :class:`_schema.MetaData` of this :class:`_schema.Table`
779 specifies its
780 own :paramref:`_schema.MetaData.schema` parameter,
781 then that schema name will
782 be applied to this :class:`_schema.Table`
783 if the schema parameter here is set
784 to ``None``. To set a blank schema name on a :class:`_schema.Table`
785 that
786 would otherwise use the schema set on the owning
787 :class:`_schema.MetaData`,
788 specify the special symbol :attr:`.BLANK_SCHEMA`.
790 The quoting rules for the schema name are the same as those for the
791 ``name`` parameter, in that quoting is applied for reserved words or
792 case-sensitive names; to enable unconditional quoting for the schema
793 name, specify the flag ``quote_schema=True`` to the constructor, or use
794 the :class:`.quoted_name` construct to specify the name.
796 :param comment: Optional string that will render an SQL comment on table
797 creation.
799 :param \**kw: Additional keyword arguments not mentioned above are
800 dialect specific, and passed in the form ``<dialectname>_<argname>``.
801 See the documentation regarding an individual dialect at
802 :ref:`dialect_toplevel` for detail on documented arguments.
804 """ # noqa: E501
805 if _no_init:
806 # don't run __init__ from __new__ by default;
807 # __new__ has a specific place that __init__ is called
808 return
810 super().__init__(quoted_name(name, quote))
811 self.metadata = metadata
813 if schema is None:
814 self.schema = metadata.schema
815 elif schema is BLANK_SCHEMA:
816 self.schema = None
817 else:
818 quote_schema = quote_schema
819 assert isinstance(schema, str)
820 self.schema = quoted_name(schema, quote_schema)
822 self._sentinel_column = None
824 self.indexes = set()
825 self.constraints = set()
826 PrimaryKeyConstraint(
827 _implicit_generated=True
828 )._set_parent_with_dispatch(self)
829 self.foreign_keys = set() # type: ignore
830 self._extra_dependencies: Set[Table] = set()
831 if self.schema is not None:
832 self.fullname = "%s.%s" % (self.schema, self.name)
833 else:
834 self.fullname = self.name
836 self.implicit_returning = implicit_returning
837 _reflect_info = kw.pop("_reflect_info", None)
839 self.comment = comment
841 if info is not None:
842 self.info = info
844 if listeners is not None:
845 for evt, fn in listeners:
846 event.listen(self, evt, fn)
848 self._prefixes = prefixes if prefixes else []
850 self._extra_kwargs(**kw)
852 # load column definitions from the database if 'autoload' is defined
853 # we do it after the table is in the singleton dictionary to support
854 # circular foreign keys
855 if autoload_with is not None:
856 self._autoload(
857 metadata,
858 autoload_with,
859 include_columns,
860 _extend_on=_extend_on,
861 _reflect_info=_reflect_info,
862 resolve_fks=resolve_fks,
863 )
865 # initialize all the column, etc. objects. done after reflection to
866 # allow user-overrides
868 self._init_items(
869 *args,
870 allow_replacements=extend_existing
871 or keep_existing
872 or autoload_with,
873 all_names={},
874 )
876 def _autoload(
877 self,
878 metadata: MetaData,
879 autoload_with: Union[Engine, Connection],
880 include_columns: Optional[Collection[str]],
881 exclude_columns: Collection[str] = (),
882 resolve_fks: bool = True,
883 _extend_on: Optional[Set[Table]] = None,
884 _reflect_info: _ReflectionInfo | None = None,
885 ) -> None:
886 insp = inspection.inspect(autoload_with)
887 with insp._inspection_context() as conn_insp:
888 conn_insp.reflect_table(
889 self,
890 include_columns,
891 exclude_columns,
892 resolve_fks,
893 _extend_on=_extend_on,
894 _reflect_info=_reflect_info,
895 )
897 @property
898 def _sorted_constraints(self) -> List[Constraint]:
899 """Return the set of constraints as a list, sorted by creation
900 order.
902 """
904 return sorted(self.constraints, key=lambda c: c._creation_order)
906 @property
907 def foreign_key_constraints(self) -> Set[ForeignKeyConstraint]:
908 """:class:`_schema.ForeignKeyConstraint` objects referred to by this
909 :class:`_schema.Table`.
911 This list is produced from the collection of
912 :class:`_schema.ForeignKey`
913 objects currently associated.
916 .. seealso::
918 :attr:`_schema.Table.constraints`
920 :attr:`_schema.Table.foreign_keys`
922 :attr:`_schema.Table.indexes`
924 """
925 return {
926 fkc.constraint
927 for fkc in self.foreign_keys
928 if fkc.constraint is not None
929 }
931 def _init_existing(self, *args: Any, **kwargs: Any) -> None:
932 autoload_with = kwargs.pop("autoload_with", None)
933 autoload = kwargs.pop("autoload", autoload_with is not None)
934 autoload_replace = kwargs.pop("autoload_replace", True)
935 schema = kwargs.pop("schema", None)
936 _extend_on = kwargs.pop("_extend_on", None)
937 _reflect_info = kwargs.pop("_reflect_info", None)
939 # these arguments are only used with _init()
940 extend_existing = kwargs.pop("extend_existing", False)
941 keep_existing = kwargs.pop("keep_existing", False)
943 assert extend_existing
944 assert not keep_existing
946 if schema and schema != self.schema:
947 raise exc.ArgumentError(
948 f"Can't change schema of existing table "
949 f"from '{self.schema}' to '{schema}'",
950 )
952 include_columns = kwargs.pop("include_columns", None)
953 if include_columns is not None:
954 for c in self.c:
955 if c.name not in include_columns:
956 self._columns.remove(c)
958 resolve_fks = kwargs.pop("resolve_fks", True)
960 for key in ("quote", "quote_schema"):
961 if key in kwargs:
962 raise exc.ArgumentError(
963 "Can't redefine 'quote' or 'quote_schema' arguments"
964 )
966 # update `self` with these kwargs, if provided
967 self.comment = kwargs.pop("comment", self.comment)
968 self.implicit_returning = kwargs.pop(
969 "implicit_returning", self.implicit_returning
970 )
971 self.info = kwargs.pop("info", self.info)
973 exclude_columns: _typing_Sequence[str]
975 if autoload:
976 if not autoload_replace:
977 # don't replace columns already present.
978 # we'd like to do this for constraints also however we don't
979 # have simple de-duping for unnamed constraints.
980 exclude_columns = [c.name for c in self.c]
981 else:
982 exclude_columns = ()
983 self._autoload(
984 self.metadata,
985 autoload_with,
986 include_columns,
987 exclude_columns,
988 resolve_fks,
989 _extend_on=_extend_on,
990 _reflect_info=_reflect_info,
991 )
993 all_names = {c.name: c for c in self.c}
994 self._extra_kwargs(**kwargs)
995 self._init_items(*args, allow_replacements=True, all_names=all_names)
997 def _extra_kwargs(self, **kwargs: Any) -> None:
998 self._validate_dialect_kwargs(kwargs)
1000 def _init_collections(self) -> None:
1001 pass
1003 def _reset_exported(self) -> None:
1004 pass
1006 @util.ro_non_memoized_property
1007 def _autoincrement_column(self) -> Optional[Column[int]]:
1008 return self.primary_key._autoincrement_column
1010 @util.ro_memoized_property
1011 def _sentinel_column_characteristics(
1012 self,
1013 ) -> _SentinelColumnCharacterization:
1014 """determine a candidate column (or columns, in case of a client
1015 generated composite primary key) which can be used as an
1016 "insert sentinel" for an INSERT statement.
1018 The returned structure, :class:`_SentinelColumnCharacterization`,
1019 includes all the details needed by :class:`.Dialect` and
1020 :class:`.SQLCompiler` to determine if these column(s) can be used
1021 as an INSERT..RETURNING sentinel for a particular database
1022 dialect.
1024 .. versionadded:: 2.0.10
1026 """
1028 sentinel_is_explicit = False
1029 sentinel_is_autoinc = False
1030 the_sentinel: Optional[_typing_Sequence[Column[Any]]] = None
1032 # see if a column was explicitly marked "insert_sentinel=True".
1033 explicit_sentinel_col = self._sentinel_column
1035 if explicit_sentinel_col is not None:
1036 the_sentinel = (explicit_sentinel_col,)
1037 sentinel_is_explicit = True
1039 autoinc_col = self._autoincrement_column
1040 if sentinel_is_explicit and explicit_sentinel_col is autoinc_col:
1041 assert autoinc_col is not None
1042 sentinel_is_autoinc = True
1043 elif explicit_sentinel_col is None and autoinc_col is not None:
1044 the_sentinel = (autoinc_col,)
1045 sentinel_is_autoinc = True
1047 default_characterization = _SentinelDefaultCharacterization.UNKNOWN
1049 if the_sentinel:
1050 the_sentinel_zero = the_sentinel[0]
1051 if the_sentinel_zero.identity:
1052 if the_sentinel_zero.identity._increment_is_negative:
1053 if sentinel_is_explicit:
1054 raise exc.InvalidRequestError(
1055 "Can't use IDENTITY default with negative "
1056 "increment as an explicit sentinel column"
1057 )
1058 else:
1059 if sentinel_is_autoinc:
1060 autoinc_col = None
1061 sentinel_is_autoinc = False
1062 the_sentinel = None
1063 else:
1064 default_characterization = (
1065 _SentinelDefaultCharacterization.IDENTITY
1066 )
1067 elif (
1068 the_sentinel_zero.default is None
1069 and the_sentinel_zero.server_default is None
1070 ):
1071 if the_sentinel_zero.nullable:
1072 raise exc.InvalidRequestError(
1073 f"Column {the_sentinel_zero} has been marked as a "
1074 "sentinel "
1075 "column with no default generation function; it "
1076 "at least needs to be marked nullable=False assuming "
1077 "user-populated sentinel values will be used."
1078 )
1079 default_characterization = (
1080 _SentinelDefaultCharacterization.NONE
1081 )
1082 elif the_sentinel_zero.default is not None:
1083 if the_sentinel_zero.default.is_sentinel:
1084 default_characterization = (
1085 _SentinelDefaultCharacterization.SENTINEL_DEFAULT
1086 )
1087 elif default_is_sequence(the_sentinel_zero.default):
1088 if the_sentinel_zero.default._increment_is_negative:
1089 if sentinel_is_explicit:
1090 raise exc.InvalidRequestError(
1091 "Can't use SEQUENCE default with negative "
1092 "increment as an explicit sentinel column"
1093 )
1094 else:
1095 if sentinel_is_autoinc:
1096 autoinc_col = None
1097 sentinel_is_autoinc = False
1098 the_sentinel = None
1100 default_characterization = (
1101 _SentinelDefaultCharacterization.SEQUENCE
1102 )
1103 elif the_sentinel_zero.default.is_callable:
1104 default_characterization = (
1105 _SentinelDefaultCharacterization.CLIENTSIDE
1106 )
1107 elif the_sentinel_zero.server_default is not None:
1108 if sentinel_is_explicit:
1109 raise exc.InvalidRequestError(
1110 f"Column {the_sentinel[0]} can't be a sentinel column "
1111 "because it uses an explicit server side default "
1112 "that's not the Identity() default."
1113 )
1115 default_characterization = (
1116 _SentinelDefaultCharacterization.SERVERSIDE
1117 )
1119 if the_sentinel is None and self.primary_key:
1120 assert autoinc_col is None
1122 # determine for non-autoincrement pk if all elements are
1123 # client side
1124 for _pkc in self.primary_key:
1125 if _pkc.server_default is not None or (
1126 _pkc.default and not _pkc.default.is_callable
1127 ):
1128 break
1129 else:
1130 the_sentinel = tuple(self.primary_key)
1131 default_characterization = (
1132 _SentinelDefaultCharacterization.CLIENTSIDE
1133 )
1135 return _SentinelColumnCharacterization(
1136 the_sentinel,
1137 sentinel_is_explicit,
1138 sentinel_is_autoinc,
1139 default_characterization,
1140 )
1142 @property
1143 def autoincrement_column(self) -> Optional[Column[int]]:
1144 """Returns the :class:`.Column` object which currently represents
1145 the "auto increment" column, if any, else returns None.
1147 This is based on the rules for :class:`.Column` as defined by the
1148 :paramref:`.Column.autoincrement` parameter, which generally means the
1149 column within a single integer column primary key constraint that is
1150 not constrained by a foreign key. If the table does not have such
1151 a primary key constraint, then there's no "autoincrement" column.
1152 A :class:`.Table` may have only one column defined as the
1153 "autoincrement" column.
1155 .. versionadded:: 2.0.4
1157 .. seealso::
1159 :paramref:`.Column.autoincrement`
1161 """
1162 return self._autoincrement_column
1164 @property
1165 def key(self) -> str:
1166 """Return the 'key' for this :class:`_schema.Table`.
1168 This value is used as the dictionary key within the
1169 :attr:`_schema.MetaData.tables` collection. It is typically the same
1170 as that of :attr:`_schema.Table.name` for a table with no
1171 :attr:`_schema.Table.schema`
1172 set; otherwise it is typically of the form
1173 ``schemaname.tablename``.
1175 """
1176 return _get_table_key(self.name, self.schema)
1178 def __repr__(self) -> str:
1179 return "Table(%s)" % ", ".join(
1180 [repr(self.name)]
1181 + [repr(self.metadata)]
1182 + [repr(x) for x in self.columns]
1183 + ["%s=%s" % (k, repr(getattr(self, k))) for k in ["schema"]]
1184 )
1186 def __str__(self) -> str:
1187 return _get_table_key(self.description, self.schema)
1189 def add_is_dependent_on(self, table: Table) -> None:
1190 """Add a 'dependency' for this Table.
1192 This is another Table object which must be created
1193 first before this one can, or dropped after this one.
1195 Usually, dependencies between tables are determined via
1196 ForeignKey objects. However, for other situations that
1197 create dependencies outside of foreign keys (rules, inheriting),
1198 this method can manually establish such a link.
1200 """
1201 self._extra_dependencies.add(table)
1203 def _insert_col_impl(
1204 self,
1205 column: ColumnClause[Any],
1206 *,
1207 index: Optional[int] = None,
1208 replace_existing: bool = False,
1209 ) -> None:
1210 try:
1211 column._set_parent_with_dispatch(
1212 self,
1213 allow_replacements=replace_existing,
1214 all_names={c.name: c for c in self.c},
1215 index=index,
1216 )
1217 except exc.DuplicateColumnError as de:
1218 raise exc.DuplicateColumnError(
1219 f"{de.args[0]} Specify replace_existing=True to "
1220 "Table.append_column() or Table.insert_column() to replace an "
1221 "existing column."
1222 ) from de
1224 def insert_column(
1225 self,
1226 column: ColumnClause[Any],
1227 index: int,
1228 *,
1229 replace_existing: bool = False,
1230 ) -> None:
1231 """Insert a :class:`_schema.Column` to this :class:`_schema.Table` at
1232 a specific position.
1234 Behavior is identical to :meth:`.Table.append_column` except that
1235 the index position can be controlled using the
1236 :paramref:`.Table.insert_column.index`
1237 parameter.
1239 :param replace_existing:
1240 see :paramref:`.Table.append_column.replace_existing`
1241 :param index: integer index to insert the new column.
1243 .. versionadded:: 2.1
1245 """
1246 self._insert_col_impl(
1247 column, index=index, replace_existing=replace_existing
1248 )
1250 def append_column(
1251 self, column: ColumnClause[Any], *, replace_existing: bool = False
1252 ) -> None:
1253 """Append a :class:`_schema.Column` to this :class:`_schema.Table`.
1255 The "key" of the newly added :class:`_schema.Column`, i.e. the
1256 value of its ``.key`` attribute, will then be available
1257 in the ``.c`` collection of this :class:`_schema.Table`, and the
1258 column definition will be included in any CREATE TABLE, SELECT,
1259 UPDATE, etc. statements generated from this :class:`_schema.Table`
1260 construct.
1262 Note that this does **not** change the definition of the table
1263 as it exists within any underlying database, assuming that
1264 table has already been created in the database. Relational
1265 databases support the addition of columns to existing tables
1266 using the SQL ALTER command, which would need to be
1267 emitted for an already-existing table that doesn't contain
1268 the newly added column.
1270 :param replace_existing: When ``True``, allows replacing existing
1271 columns. When ``False``, the default, an warning will be raised
1272 if a column with the same ``.key`` already exists. A future
1273 version of sqlalchemy will instead rise a warning.
1275 .. versionadded:: 1.4.0
1277 .. seealso::
1279 :meth:`.Table.insert_column`
1281 """
1282 self._insert_col_impl(column, replace_existing=replace_existing)
1284 def append_constraint(self, constraint: Union[Index, Constraint]) -> None:
1285 """Append a :class:`_schema.Constraint` to this
1286 :class:`_schema.Table`.
1288 This has the effect of the constraint being included in any
1289 future CREATE TABLE statement, assuming specific DDL creation
1290 events have not been associated with the given
1291 :class:`_schema.Constraint` object.
1293 Note that this does **not** produce the constraint within the
1294 relational database automatically, for a table that already exists
1295 in the database. To add a constraint to an
1296 existing relational database table, the SQL ALTER command must
1297 be used. SQLAlchemy also provides the
1298 :class:`.AddConstraint` construct which can produce this SQL when
1299 invoked as an executable clause.
1301 """
1303 constraint._set_parent_with_dispatch(self)
1305 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
1306 metadata = parent
1307 assert isinstance(metadata, MetaData)
1308 metadata._add_table(self.name, self.schema, self)
1309 self.metadata = metadata
1311 def create(self, bind: _CreateDropBind, checkfirst: bool = False) -> None:
1312 """Issue a ``CREATE`` statement for this
1313 :class:`_schema.Table`, using the given
1314 :class:`.Connection` or :class:`.Engine`
1315 for connectivity.
1317 .. seealso::
1319 :meth:`_schema.MetaData.create_all`.
1321 """
1323 bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
1325 def drop(self, bind: _CreateDropBind, checkfirst: bool = False) -> None:
1326 """Issue a ``DROP`` statement for this
1327 :class:`_schema.Table`, using the given
1328 :class:`.Connection` or :class:`.Engine` for connectivity.
1330 .. seealso::
1332 :meth:`_schema.MetaData.drop_all`.
1334 """
1335 bind._run_ddl_visitor(ddl.SchemaDropper, self, checkfirst=checkfirst)
1337 @util.deprecated(
1338 "1.4",
1339 ":meth:`_schema.Table.tometadata` is renamed to "
1340 ":meth:`_schema.Table.to_metadata`",
1341 )
1342 def tometadata(
1343 self,
1344 metadata: MetaData,
1345 schema: Union[str, Literal[SchemaConst.RETAIN_SCHEMA]] = RETAIN_SCHEMA,
1346 referred_schema_fn: Optional[
1347 Callable[
1348 [Table, Optional[str], ForeignKeyConstraint, Optional[str]],
1349 Optional[str],
1350 ]
1351 ] = None,
1352 name: Optional[str] = None,
1353 ) -> Table:
1354 """Return a copy of this :class:`_schema.Table`
1355 associated with a different
1356 :class:`_schema.MetaData`.
1358 See :meth:`_schema.Table.to_metadata` for a full description.
1360 """
1361 return self.to_metadata(
1362 metadata,
1363 schema=schema,
1364 referred_schema_fn=referred_schema_fn,
1365 name=name,
1366 )
1368 def to_metadata(
1369 self,
1370 metadata: MetaData,
1371 schema: Union[str, Literal[SchemaConst.RETAIN_SCHEMA]] = RETAIN_SCHEMA,
1372 referred_schema_fn: Optional[
1373 Callable[
1374 [Table, Optional[str], ForeignKeyConstraint, Optional[str]],
1375 Optional[str],
1376 ]
1377 ] = None,
1378 name: Optional[str] = None,
1379 ) -> Table:
1380 """Return a copy of this :class:`_schema.Table` associated with a
1381 different :class:`_schema.MetaData`.
1383 E.g.::
1385 m1 = MetaData()
1387 user = Table("user", m1, Column("id", Integer, primary_key=True))
1389 m2 = MetaData()
1390 user_copy = user.to_metadata(m2)
1392 .. versionchanged:: 1.4 The :meth:`_schema.Table.to_metadata` function
1393 was renamed from :meth:`_schema.Table.tometadata`.
1396 :param metadata: Target :class:`_schema.MetaData` object,
1397 into which the
1398 new :class:`_schema.Table` object will be created.
1400 :param schema: optional string name indicating the target schema.
1401 Defaults to the special symbol :attr:`.RETAIN_SCHEMA` which indicates
1402 that no change to the schema name should be made in the new
1403 :class:`_schema.Table`. If set to a string name, the new
1404 :class:`_schema.Table`
1405 will have this new name as the ``.schema``. If set to ``None``, the
1406 schema will be set to that of the schema set on the target
1407 :class:`_schema.MetaData`, which is typically ``None`` as well,
1408 unless
1409 set explicitly::
1411 m2 = MetaData(schema="newschema")
1413 # user_copy_one will have "newschema" as the schema name
1414 user_copy_one = user.to_metadata(m2, schema=None)
1416 m3 = MetaData() # schema defaults to None
1418 # user_copy_two will have None as the schema name
1419 user_copy_two = user.to_metadata(m3, schema=None)
1421 :param referred_schema_fn: optional callable which can be supplied
1422 in order to provide for the schema name that should be assigned
1423 to the referenced table of a :class:`_schema.ForeignKeyConstraint`.
1424 The callable accepts this parent :class:`_schema.Table`, the
1425 target schema that we are changing to, the
1426 :class:`_schema.ForeignKeyConstraint` object, and the existing
1427 "target schema" of that constraint. The function should return the
1428 string schema name that should be applied. To reset the schema
1429 to "none", return the symbol :data:`.BLANK_SCHEMA`. To effect no
1430 change, return ``None`` or :data:`.RETAIN_SCHEMA`.
1432 .. versionchanged:: 1.4.33 The ``referred_schema_fn`` function
1433 may return the :data:`.BLANK_SCHEMA` or :data:`.RETAIN_SCHEMA`
1434 symbols.
1436 E.g.::
1438 def referred_schema_fn(table, to_schema, constraint, referred_schema):
1439 if referred_schema == "base_tables":
1440 return referred_schema
1441 else:
1442 return to_schema
1445 new_table = table.to_metadata(
1446 m2, schema="alt_schema", referred_schema_fn=referred_schema_fn
1447 )
1449 :param name: optional string name indicating the target table name.
1450 If not specified or None, the table name is retained. This allows
1451 a :class:`_schema.Table` to be copied to the same
1452 :class:`_schema.MetaData` target
1453 with a new name.
1455 """ # noqa: E501
1456 if name is None:
1457 name = self.name
1459 actual_schema: Optional[str]
1461 if schema is RETAIN_SCHEMA:
1462 actual_schema = self.schema
1463 elif schema is None:
1464 actual_schema = metadata.schema
1465 else:
1466 actual_schema = schema
1467 key = _get_table_key(name, actual_schema)
1468 if key in metadata.tables:
1469 util.warn(
1470 f"Table '{self.description}' already exists within the given "
1471 "MetaData - not copying."
1472 )
1473 return metadata.tables[key]
1475 args = []
1476 for col in self.columns:
1477 args.append(col._copy(schema=actual_schema, _to_metadata=metadata))
1478 table = Table(
1479 name,
1480 metadata,
1481 schema=actual_schema,
1482 comment=self.comment,
1483 *args,
1484 **self.kwargs,
1485 )
1486 for const in self.constraints:
1487 if isinstance(const, ForeignKeyConstraint):
1488 referred_schema = const._referred_schema
1489 if referred_schema_fn:
1490 fk_constraint_schema = referred_schema_fn(
1491 self, actual_schema, const, referred_schema
1492 )
1493 else:
1494 fk_constraint_schema = (
1495 actual_schema
1496 if referred_schema == self.schema
1497 else None
1498 )
1499 table.append_constraint(
1500 const._copy(
1501 schema=fk_constraint_schema, target_table=table
1502 )
1503 )
1504 elif not const._type_bound:
1505 # skip unique constraints that would be generated
1506 # by the 'unique' flag on Column
1507 if const._column_flag:
1508 continue
1510 table.append_constraint(
1511 const._copy(schema=actual_schema, target_table=table)
1512 )
1513 for index in self.indexes:
1514 # skip indexes that would be generated
1515 # by the 'index' flag on Column
1516 if index._column_flag:
1517 continue
1518 Index(
1519 index.name,
1520 unique=index.unique,
1521 *[
1522 _copy_expression(expr, self, table)
1523 for expr in index._table_bound_expressions
1524 ],
1525 _table=table,
1526 **index.kwargs,
1527 )
1528 return self._schema_item_copy(table)
1531class Column(DialectKWArgs, SchemaItem, ColumnClause[_T]):
1532 """Represents a column in a database table."""
1534 __visit_name__ = "column"
1536 inherit_cache = True
1537 key: str
1539 server_default: Optional[FetchedValue]
1541 def __init__(
1542 self,
1543 __name_pos: Optional[
1544 Union[str, _TypeEngineArgument[_T], SchemaEventTarget]
1545 ] = None,
1546 __type_pos: Optional[
1547 Union[_TypeEngineArgument[_T], SchemaEventTarget]
1548 ] = None,
1549 /,
1550 *args: SchemaEventTarget,
1551 name: Optional[str] = None,
1552 type_: Optional[_TypeEngineArgument[_T]] = None,
1553 autoincrement: _AutoIncrementType = "auto",
1554 default: Optional[Any] = _NoArg.NO_ARG,
1555 insert_default: Optional[Any] = _NoArg.NO_ARG,
1556 doc: Optional[str] = None,
1557 key: Optional[str] = None,
1558 index: Optional[bool] = None,
1559 unique: Optional[bool] = None,
1560 info: Optional[_InfoType] = None,
1561 nullable: Optional[
1562 Union[bool, Literal[SchemaConst.NULL_UNSPECIFIED]]
1563 ] = SchemaConst.NULL_UNSPECIFIED,
1564 onupdate: Optional[Any] = None,
1565 primary_key: bool = False,
1566 server_default: Optional[_ServerDefaultArgument] = None,
1567 server_onupdate: Optional[_ServerOnUpdateArgument] = None,
1568 quote: Optional[bool] = None,
1569 system: bool = False,
1570 comment: Optional[str] = None,
1571 insert_sentinel: bool = False,
1572 _omit_from_statements: bool = False,
1573 _proxies: Optional[Any] = None,
1574 **dialect_kwargs: Any,
1575 ):
1576 r"""
1577 Construct a new ``Column`` object.
1579 :param name: The name of this column as represented in the database.
1580 This argument may be the first positional argument, or specified
1581 via keyword.
1583 Names which contain no upper case characters
1584 will be treated as case insensitive names, and will not be quoted
1585 unless they are a reserved word. Names with any number of upper
1586 case characters will be quoted and sent exactly. Note that this
1587 behavior applies even for databases which standardize upper
1588 case names as case insensitive such as Oracle Database.
1590 The name field may be omitted at construction time and applied
1591 later, at any time before the Column is associated with a
1592 :class:`_schema.Table`. This is to support convenient
1593 usage within the :mod:`~sqlalchemy.ext.declarative` extension.
1595 :param type\_: The column's type, indicated using an instance which
1596 subclasses :class:`~sqlalchemy.types.TypeEngine`. If no arguments
1597 are required for the type, the class of the type can be sent
1598 as well, e.g.::
1600 # use a type with arguments
1601 Column("data", String(50))
1603 # use no arguments
1604 Column("level", Integer)
1606 The ``type`` argument may be the second positional argument
1607 or specified by keyword.
1609 If the ``type`` is ``None`` or is omitted, it will first default to
1610 the special type :class:`.NullType`. If and when this
1611 :class:`_schema.Column` is made to refer to another column using
1612 :class:`_schema.ForeignKey` and/or
1613 :class:`_schema.ForeignKeyConstraint`, the type
1614 of the remote-referenced column will be copied to this column as
1615 well, at the moment that the foreign key is resolved against that
1616 remote :class:`_schema.Column` object.
1618 :param \*args: Additional positional arguments include various
1619 :class:`.SchemaItem` derived constructs which will be applied
1620 as options to the column. These include instances of
1621 :class:`.Constraint`, :class:`_schema.ForeignKey`,
1622 :class:`.ColumnDefault`, :class:`.Sequence`, :class:`.Computed`
1623 :class:`.Identity`. In some cases an
1624 equivalent keyword argument is available such as ``server_default``,
1625 ``default`` and ``unique``.
1627 :param autoincrement: Set up "auto increment" semantics for an
1628 **integer primary key column with no foreign key dependencies**
1629 (see later in this docstring for a more specific definition).
1630 This may influence the :term:`DDL` that will be emitted for
1631 this column during a table create, as well as how the column
1632 will be considered when INSERT statements are compiled and
1633 executed.
1635 The default value is the string ``"auto"``,
1636 which indicates that a single-column (i.e. non-composite) primary key
1637 that is of an INTEGER type with no other client-side or server-side
1638 default constructs indicated should receive auto increment semantics
1639 automatically. Other values include ``True`` (force this column to
1640 have auto-increment semantics for a :term:`composite primary key` as
1641 well), ``False`` (this column should never have auto-increment
1642 semantics), and the string ``"ignore_fk"`` (special-case for foreign
1643 key columns, see below).
1645 The term "auto increment semantics" refers both to the kind of DDL
1646 that will be emitted for the column within a CREATE TABLE statement,
1647 when methods such as :meth:`.MetaData.create_all` and
1648 :meth:`.Table.create` are invoked, as well as how the column will be
1649 considered when an INSERT statement is compiled and emitted to the
1650 database:
1652 * **DDL rendering** (i.e. :meth:`.MetaData.create_all`,
1653 :meth:`.Table.create`): When used on a :class:`.Column` that has
1654 no other
1655 default-generating construct associated with it (such as a
1656 :class:`.Sequence` or :class:`.Identity` construct), the parameter
1657 will imply that database-specific keywords such as PostgreSQL
1658 ``SERIAL``, MySQL ``AUTO_INCREMENT``, or ``IDENTITY`` on SQL Server
1659 should also be rendered. Not every database backend has an
1660 "implied" default generator available; for example the Oracle Database
1661 backends alway needs an explicit construct such as
1662 :class:`.Identity` to be included with a :class:`.Column` in order
1663 for the DDL rendered to include auto-generating constructs to also
1664 be produced in the database.
1666 * **INSERT semantics** (i.e. when a :func:`_sql.insert` construct is
1667 compiled into a SQL string and is then executed on a database using
1668 :meth:`_engine.Connection.execute` or equivalent): A single-row
1669 INSERT statement will be known to produce a new integer primary key
1670 value automatically for this column, which will be accessible
1671 after the statement is invoked via the
1672 :attr:`.CursorResult.inserted_primary_key` attribute upon the
1673 :class:`_result.Result` object. This also applies towards use of the
1674 ORM when ORM-mapped objects are persisted to the database,
1675 indicating that a new integer primary key will be available to
1676 become part of the :term:`identity key` for that object. This
1677 behavior takes place regardless of what DDL constructs are
1678 associated with the :class:`_schema.Column` and is independent
1679 of the "DDL Rendering" behavior discussed in the previous note
1680 above.
1682 The parameter may be set to ``True`` to indicate that a column which
1683 is part of a composite (i.e. multi-column) primary key should
1684 have autoincrement semantics, though note that only one column
1685 within a primary key may have this setting. It can also
1686 be set to ``True`` to indicate autoincrement semantics on a
1687 column that has a client-side or server-side default configured,
1688 however note that not all dialects can accommodate all styles
1689 of default as an "autoincrement". It can also be
1690 set to ``False`` on a single-column primary key that has a
1691 datatype of INTEGER in order to disable auto increment semantics
1692 for that column.
1694 The setting *only* has an effect for columns which are:
1696 * Integer derived (i.e. INT, SMALLINT, BIGINT).
1698 * Part of the primary key
1700 * Not referring to another column via :class:`_schema.ForeignKey`,
1701 unless
1702 the value is specified as ``'ignore_fk'``::
1704 # turn on autoincrement for this column despite
1705 # the ForeignKey()
1706 Column(
1707 "id",
1708 ForeignKey("other.id"),
1709 primary_key=True,
1710 autoincrement="ignore_fk",
1711 )
1713 It is typically not desirable to have "autoincrement" enabled on a
1714 column that refers to another via foreign key, as such a column is
1715 required to refer to a value that originates from elsewhere.
1717 The setting has these effects on columns that meet the
1718 above criteria:
1720 * DDL issued for the column, if the column does not already include
1721 a default generating construct supported by the backend such as
1722 :class:`.Identity`, will include database-specific
1723 keywords intended to signify this column as an
1724 "autoincrement" column for specific backends. Behavior for
1725 primary SQLAlchemy dialects includes:
1727 * AUTO INCREMENT on MySQL and MariaDB
1728 * SERIAL on PostgreSQL
1729 * IDENTITY on MS-SQL - this occurs even without the
1730 :class:`.Identity` construct as the
1731 :paramref:`.Column.autoincrement` parameter pre-dates this
1732 construct.
1733 * SQLite - SQLite integer primary key columns are implicitly
1734 "auto incrementing" and no additional keywords are rendered;
1735 to render the special SQLite keyword ``AUTOINCREMENT``
1736 is not included as this is unnecessary and not recommended
1737 by the database vendor. See the section
1738 :ref:`sqlite_autoincrement` for more background.
1739 * Oracle Database - The Oracle Database dialects have no default "autoincrement"
1740 feature available at this time, instead the :class:`.Identity`
1741 construct is recommended to achieve this (the :class:`.Sequence`
1742 construct may also be used).
1743 * Third-party dialects - consult those dialects' documentation
1744 for details on their specific behaviors.
1746 * When a single-row :func:`_sql.insert` construct is compiled and
1747 executed, which does not set the :meth:`_sql.Insert.inline`
1748 modifier, newly generated primary key values for this column
1749 will be automatically retrieved upon statement execution
1750 using a method specific to the database driver in use:
1752 * MySQL, SQLite - calling upon ``cursor.lastrowid()``
1753 (see
1754 `https://www.python.org/dev/peps/pep-0249/#lastrowid
1755 <https://www.python.org/dev/peps/pep-0249/#lastrowid>`_)
1756 * PostgreSQL, SQL Server, Oracle Database - use RETURNING or an equivalent
1757 construct when rendering an INSERT statement, and then retrieving
1758 the newly generated primary key values after execution
1759 * PostgreSQL, Oracle Database for :class:`_schema.Table` objects that
1760 set :paramref:`_schema.Table.implicit_returning` to False -
1761 for a :class:`.Sequence` only, the :class:`.Sequence` is invoked
1762 explicitly before the INSERT statement takes place so that the
1763 newly generated primary key value is available to the client
1764 * SQL Server for :class:`_schema.Table` objects that
1765 set :paramref:`_schema.Table.implicit_returning` to False -
1766 the ``SELECT scope_identity()`` construct is used after the
1767 INSERT statement is invoked to retrieve the newly generated
1768 primary key value.
1769 * Third-party dialects - consult those dialects' documentation
1770 for details on their specific behaviors.
1772 * For multiple-row :func:`_sql.insert` constructs invoked with
1773 a list of parameters (i.e. "executemany" semantics), primary-key
1774 retrieving behaviors are generally disabled, however there may
1775 be special APIs that may be used to retrieve lists of new
1776 primary key values for an "executemany", such as the psycopg2
1777 "fast insertmany" feature. Such features are very new and
1778 may not yet be well covered in documentation.
1780 :param default: A scalar, Python callable, or
1781 :class:`_expression.ColumnElement` expression representing the
1782 *default value* for this column, which will be invoked upon insert
1783 if this column is otherwise not specified in the VALUES clause of
1784 the insert. This is a shortcut to using :class:`.ColumnDefault` as
1785 a positional argument; see that class for full detail on the
1786 structure of the argument.
1788 Contrast this argument to
1789 :paramref:`_schema.Column.server_default`
1790 which creates a default generator on the database side.
1792 .. seealso::
1794 :ref:`metadata_defaults_toplevel`
1796 :param insert_default: An alias of :paramref:`.Column.default`
1797 for compatibility with :func:`_orm.mapped_column`.
1799 .. versionadded:: 2.0.31
1801 :param doc: optional String that can be used by the ORM or similar
1802 to document attributes on the Python side. This attribute does
1803 **not** render SQL comments; use the
1804 :paramref:`_schema.Column.comment`
1805 parameter for this purpose.
1807 :param key: An optional string identifier which will identify this
1808 ``Column`` object on the :class:`_schema.Table`.
1809 When a key is provided,
1810 this is the only identifier referencing the ``Column`` within the
1811 application, including ORM attribute mapping; the ``name`` field
1812 is used only when rendering SQL.
1814 :param index: When ``True``, indicates that a :class:`_schema.Index`
1815 construct will be automatically generated for this
1816 :class:`_schema.Column`, which will result in a "CREATE INDEX"
1817 statement being emitted for the :class:`_schema.Table` when the DDL
1818 create operation is invoked.
1820 Using this flag is equivalent to making use of the
1821 :class:`_schema.Index` construct explicitly at the level of the
1822 :class:`_schema.Table` construct itself::
1824 Table(
1825 "some_table",
1826 metadata,
1827 Column("x", Integer),
1828 Index("ix_some_table_x", "x"),
1829 )
1831 To add the :paramref:`_schema.Index.unique` flag to the
1832 :class:`_schema.Index`, set both the
1833 :paramref:`_schema.Column.unique` and
1834 :paramref:`_schema.Column.index` flags to True simultaneously,
1835 which will have the effect of rendering the "CREATE UNIQUE INDEX"
1836 DDL instruction instead of "CREATE INDEX".
1838 The name of the index is generated using the
1839 :ref:`default naming convention <constraint_default_naming_convention>`
1840 which for the :class:`_schema.Index` construct is of the form
1841 ``ix_<tablename>_<columnname>``.
1843 As this flag is intended only as a convenience for the common case
1844 of adding a single-column, default configured index to a table
1845 definition, explicit use of the :class:`_schema.Index` construct
1846 should be preferred for most use cases, including composite indexes
1847 that encompass more than one column, indexes with SQL expressions
1848 or ordering, backend-specific index configuration options, and
1849 indexes that use a specific name.
1851 .. note:: the :attr:`_schema.Column.index` attribute on
1852 :class:`_schema.Column`
1853 **does not indicate** if this column is indexed or not, only
1854 if this flag was explicitly set here. To view indexes on
1855 a column, view the :attr:`_schema.Table.indexes` collection
1856 or use :meth:`_reflection.Inspector.get_indexes`.
1858 .. seealso::
1860 :ref:`schema_indexes`
1862 :ref:`constraint_naming_conventions`
1864 :paramref:`_schema.Column.unique`
1866 :param info: Optional data dictionary which will be populated into the
1867 :attr:`.SchemaItem.info` attribute of this object.
1869 :param nullable: When set to ``False``, will cause the "NOT NULL"
1870 phrase to be added when generating DDL for the column. When
1871 ``True``, will normally generate nothing (in SQL this defaults to
1872 "NULL"), except in some very specific backend-specific edge cases
1873 where "NULL" may render explicitly.
1874 Defaults to ``True`` unless :paramref:`_schema.Column.primary_key`
1875 is also ``True`` or the column specifies a :class:`_sql.Identity`,
1876 in which case it defaults to ``False``.
1877 This parameter is only used when issuing CREATE TABLE statements.
1879 .. note::
1881 When the column specifies a :class:`_sql.Identity` this
1882 parameter is in general ignored by the DDL compiler. The
1883 PostgreSQL database allows nullable identity column by
1884 setting this parameter to ``True`` explicitly.
1886 :param onupdate: A scalar, Python callable, or
1887 :class:`~sqlalchemy.sql.expression.ClauseElement` representing a
1888 default value to be applied to the column within UPDATE
1889 statements, which will be invoked upon update if this column is not
1890 present in the SET clause of the update. This is a shortcut to
1891 using :class:`.ColumnDefault` as a positional argument with
1892 ``for_update=True``.
1894 .. seealso::
1896 :ref:`metadata_defaults` - complete discussion of onupdate
1898 :param primary_key: If ``True``, marks this column as a primary key
1899 column. Multiple columns can have this flag set to specify
1900 composite primary keys. As an alternative, the primary key of a
1901 :class:`_schema.Table` can be specified via an explicit
1902 :class:`.PrimaryKeyConstraint` object.
1904 :param server_default: A :class:`.FetchedValue` instance, str, Unicode
1905 or :func:`~sqlalchemy.sql.expression.text` construct representing
1906 the DDL DEFAULT value for the column.
1908 String types will be emitted as-is, surrounded by single quotes::
1910 Column("x", Text, server_default="val")
1912 will render:
1914 .. sourcecode:: sql
1916 x TEXT DEFAULT 'val'
1918 A :func:`~sqlalchemy.sql.expression.text` expression will be
1919 rendered as-is, without quotes::
1921 Column("y", DateTime, server_default=text("NOW()"))
1923 will render:
1925 .. sourcecode:: sql
1927 y DATETIME DEFAULT NOW()
1929 Strings and text() will be converted into a
1930 :class:`.DefaultClause` object upon initialization.
1932 This parameter can also accept complex combinations of contextually
1933 valid SQLAlchemy expressions or constructs::
1935 from sqlalchemy import create_engine
1936 from sqlalchemy import Table, Column, MetaData, ARRAY, Text
1937 from sqlalchemy.dialects.postgresql import array
1939 engine = create_engine(
1940 "postgresql+psycopg2://scott:tiger@localhost/mydatabase"
1941 )
1942 metadata_obj = MetaData()
1943 tbl = Table(
1944 "foo",
1945 metadata_obj,
1946 Column(
1947 "bar", ARRAY(Text), server_default=array(["biz", "bang", "bash"])
1948 ),
1949 )
1950 metadata_obj.create_all(engine)
1952 The above results in a table created with the following SQL:
1954 .. sourcecode:: sql
1956 CREATE TABLE foo (
1957 bar TEXT[] DEFAULT ARRAY['biz', 'bang', 'bash']
1958 )
1960 Use :class:`.FetchedValue` to indicate that an already-existing
1961 column will generate a default value on the database side which
1962 will be available to SQLAlchemy for post-fetch after inserts. This
1963 construct does not specify any DDL and the implementation is left
1964 to the database, such as via a trigger.
1966 .. seealso::
1968 :ref:`server_defaults` - complete discussion of server side
1969 defaults
1971 :param server_onupdate: A :class:`.FetchedValue` instance
1972 representing a database-side default generation function,
1973 such as a trigger. This
1974 indicates to SQLAlchemy that a newly generated value will be
1975 available after updates. This construct does not actually
1976 implement any kind of generation function within the database,
1977 which instead must be specified separately.
1980 .. warning:: This directive **does not** currently produce MySQL's
1981 "ON UPDATE CURRENT_TIMESTAMP()" clause. See
1982 :ref:`mysql_timestamp_onupdate` for background on how to
1983 produce this clause.
1985 .. seealso::
1987 :ref:`triggered_columns`
1989 :param quote: Force quoting of this column's name on or off,
1990 corresponding to ``True`` or ``False``. When left at its default
1991 of ``None``, the column identifier will be quoted according to
1992 whether the name is case sensitive (identifiers with at least one
1993 upper case character are treated as case sensitive), or if it's a
1994 reserved word. This flag is only needed to force quoting of a
1995 reserved word which is not known by the SQLAlchemy dialect.
1997 :param unique: When ``True``, and the :paramref:`_schema.Column.index`
1998 parameter is left at its default value of ``False``,
1999 indicates that a :class:`_schema.UniqueConstraint`
2000 construct will be automatically generated for this
2001 :class:`_schema.Column`,
2002 which will result in a "UNIQUE CONSTRAINT" clause referring
2003 to this column being included
2004 in the ``CREATE TABLE`` statement emitted, when the DDL create
2005 operation for the :class:`_schema.Table` object is invoked.
2007 When this flag is ``True`` while the
2008 :paramref:`_schema.Column.index` parameter is simultaneously
2009 set to ``True``, the effect instead is that a
2010 :class:`_schema.Index` construct which includes the
2011 :paramref:`_schema.Index.unique` parameter set to ``True``
2012 is generated. See the documentation for
2013 :paramref:`_schema.Column.index` for additional detail.
2015 Using this flag is equivalent to making use of the
2016 :class:`_schema.UniqueConstraint` construct explicitly at the
2017 level of the :class:`_schema.Table` construct itself::
2019 Table("some_table", metadata, Column("x", Integer), UniqueConstraint("x"))
2021 The :paramref:`_schema.UniqueConstraint.name` parameter
2022 of the unique constraint object is left at its default value
2023 of ``None``; in the absence of a :ref:`naming convention <constraint_naming_conventions>`
2024 for the enclosing :class:`_schema.MetaData`, the UNIQUE CONSTRAINT
2025 construct will be emitted as unnamed, which typically invokes
2026 a database-specific naming convention to take place.
2028 As this flag is intended only as a convenience for the common case
2029 of adding a single-column, default configured unique constraint to a table
2030 definition, explicit use of the :class:`_schema.UniqueConstraint` construct
2031 should be preferred for most use cases, including composite constraints
2032 that encompass more than one column, backend-specific index configuration options, and
2033 constraints that use a specific name.
2035 .. note:: the :attr:`_schema.Column.unique` attribute on
2036 :class:`_schema.Column`
2037 **does not indicate** if this column has a unique constraint or
2038 not, only if this flag was explicitly set here. To view
2039 indexes and unique constraints that may involve this column,
2040 view the
2041 :attr:`_schema.Table.indexes` and/or
2042 :attr:`_schema.Table.constraints` collections or use
2043 :meth:`_reflection.Inspector.get_indexes` and/or
2044 :meth:`_reflection.Inspector.get_unique_constraints`
2046 .. seealso::
2048 :ref:`schema_unique_constraint`
2050 :ref:`constraint_naming_conventions`
2052 :paramref:`_schema.Column.index`
2054 :param system: When ``True``, indicates this is a "system" column,
2055 that is a column which is automatically made available by the
2056 database, and should not be included in the columns list for a
2057 ``CREATE TABLE`` statement.
2059 For more elaborate scenarios where columns should be
2060 conditionally rendered differently on different backends,
2061 consider custom compilation rules for :class:`.CreateColumn`.
2063 :param comment: Optional string that will render an SQL comment on
2064 table creation.
2066 :param insert_sentinel: Marks this :class:`_schema.Column` as an
2067 :term:`insert sentinel` used for optimizing the performance of the
2068 :term:`insertmanyvalues` feature for tables that don't
2069 otherwise have qualifying primary key configurations.
2071 .. versionadded:: 2.0.10
2073 .. seealso::
2075 :func:`_schema.insert_sentinel` - all in one helper for declaring
2076 sentinel columns
2078 :ref:`engine_insertmanyvalues`
2080 :ref:`engine_insertmanyvalues_sentinel_columns`
2083 """ # noqa: E501, RST201, RST202
2085 l_args = [__name_pos, __type_pos] + list(args)
2086 del args
2088 if l_args:
2089 if isinstance(l_args[0], str):
2090 if name is not None:
2091 raise exc.ArgumentError(
2092 "May not pass name positionally and as a keyword."
2093 )
2094 name = l_args.pop(0) # type: ignore
2095 elif l_args[0] is None:
2096 l_args.pop(0)
2097 if l_args:
2098 coltype = l_args[0]
2100 if hasattr(coltype, "_sqla_type"):
2101 if type_ is not None:
2102 raise exc.ArgumentError(
2103 "May not pass type_ positionally and as a keyword."
2104 )
2105 type_ = l_args.pop(0) # type: ignore
2106 elif l_args[0] is None:
2107 l_args.pop(0)
2109 if name is not None:
2110 name = quoted_name(name, quote)
2111 elif quote is not None:
2112 raise exc.ArgumentError(
2113 "Explicit 'name' is required when sending 'quote' argument"
2114 )
2116 # name = None is expected to be an interim state
2117 # note this use case is legacy now that ORM declarative has a
2118 # dedicated "column" construct local to the ORM
2119 super().__init__(name, type_) # type: ignore
2121 self.key = key if key is not None else name # type: ignore
2122 self.primary_key = primary_key
2123 self._insert_sentinel = insert_sentinel
2124 self._omit_from_statements = _omit_from_statements
2125 self._user_defined_nullable = udn = nullable
2126 if udn is not NULL_UNSPECIFIED:
2127 self.nullable = udn
2128 else:
2129 self.nullable = not primary_key
2131 # these default to None because .index and .unique is *not*
2132 # an informational flag about Column - there can still be an
2133 # Index or UniqueConstraint referring to this Column.
2134 self.index = index
2135 self.unique = unique
2137 self.system = system
2138 self.doc = doc
2139 self.autoincrement: _AutoIncrementType = autoincrement
2140 self.constraints = set()
2141 self.foreign_keys = set()
2142 self.comment = comment
2143 self.computed = None
2144 self.identity = None
2146 # check if this Column is proxying another column
2148 if _proxies is not None:
2149 self._proxies = _proxies
2150 else:
2151 # otherwise, add DDL-related events
2152 self._set_type(self.type)
2154 if insert_default is not _NoArg.NO_ARG:
2155 if default is not _NoArg.NO_ARG:
2156 raise exc.ArgumentError(
2157 "The 'default' and 'insert_default' parameters "
2158 "of Column are mutually exclusive"
2159 )
2160 resolved_default = insert_default
2161 elif default is not _NoArg.NO_ARG:
2162 resolved_default = default
2163 else:
2164 resolved_default = None
2166 if resolved_default is not None:
2167 if not isinstance(resolved_default, (ColumnDefault, Sequence)):
2168 resolved_default = ColumnDefault(resolved_default)
2170 self.default = resolved_default
2171 l_args.append(resolved_default)
2172 else:
2173 self.default = None
2175 if onupdate is not None:
2176 if not isinstance(onupdate, (ColumnDefault, Sequence)):
2177 onupdate = ColumnDefault(onupdate, for_update=True)
2179 self.onupdate = onupdate
2180 l_args.append(onupdate)
2181 else:
2182 self.onupdate = None
2184 if server_default is not None:
2185 if isinstance(server_default, FetchedValue):
2186 server_default = server_default._as_for_update(False)
2187 l_args.append(server_default)
2188 else:
2189 server_default = DefaultClause(server_default)
2190 l_args.append(server_default)
2191 self.server_default = server_default
2193 if server_onupdate is not None:
2194 if isinstance(server_onupdate, FetchedValue):
2195 server_onupdate = server_onupdate._as_for_update(True)
2196 l_args.append(server_onupdate)
2197 else:
2198 server_onupdate = DefaultClause(
2199 server_onupdate, for_update=True
2200 )
2201 l_args.append(server_onupdate)
2202 self.server_onupdate = server_onupdate
2204 self._init_items(*cast(_typing_Sequence[SchemaItem], l_args))
2206 util.set_creation_order(self)
2208 if info is not None:
2209 self.info = info
2211 self._extra_kwargs(**dialect_kwargs)
2213 table: Table
2215 constraints: Set[Constraint]
2217 foreign_keys: Set[ForeignKey]
2218 """A collection of all :class:`_schema.ForeignKey` marker objects
2219 associated with this :class:`_schema.Column`.
2221 Each object is a member of a :class:`_schema.Table`-wide
2222 :class:`_schema.ForeignKeyConstraint`.
2224 .. seealso::
2226 :attr:`_schema.Table.foreign_keys`
2228 """
2230 index: Optional[bool]
2231 """The value of the :paramref:`_schema.Column.index` parameter.
2233 Does not indicate if this :class:`_schema.Column` is actually indexed
2234 or not; use :attr:`_schema.Table.indexes`.
2236 .. seealso::
2238 :attr:`_schema.Table.indexes`
2239 """
2241 unique: Optional[bool]
2242 """The value of the :paramref:`_schema.Column.unique` parameter.
2244 Does not indicate if this :class:`_schema.Column` is actually subject to
2245 a unique constraint or not; use :attr:`_schema.Table.indexes` and
2246 :attr:`_schema.Table.constraints`.
2248 .. seealso::
2250 :attr:`_schema.Table.indexes`
2252 :attr:`_schema.Table.constraints`.
2254 """
2256 computed: Optional[Computed]
2258 identity: Optional[Identity]
2260 def _set_type(self, type_: TypeEngine[Any]) -> None:
2261 assert self.type._isnull or type_ is self.type
2263 self.type = type_
2264 if isinstance(self.type, SchemaEventTarget):
2265 self.type._set_parent_with_dispatch(self)
2266 for impl in self.type._variant_mapping.values():
2267 if isinstance(impl, SchemaEventTarget):
2268 impl._set_parent_with_dispatch(self)
2270 @HasMemoized.memoized_attribute
2271 def _default_description_tuple(self) -> _DefaultDescriptionTuple:
2272 """used by default.py -> _process_execute_defaults()"""
2274 return _DefaultDescriptionTuple._from_column_default(self.default)
2276 @HasMemoized.memoized_attribute
2277 def _onupdate_description_tuple(self) -> _DefaultDescriptionTuple:
2278 """used by default.py -> _process_execute_defaults()"""
2279 return _DefaultDescriptionTuple._from_column_default(self.onupdate)
2281 @util.memoized_property
2282 def _gen_static_annotations_cache_key(self) -> bool:
2283 """special attribute used by cache key gen, if true, we will
2284 use a static cache key for the annotations dictionary, else we
2285 will generate a new cache key for annotations each time.
2287 Added for #8790
2289 """
2290 return self.table is not None and self.table._is_table
2292 def _extra_kwargs(self, **kwargs: Any) -> None:
2293 self._validate_dialect_kwargs(kwargs)
2295 def __str__(self) -> str:
2296 if self.name is None:
2297 return "(no name)"
2298 elif self.table is not None:
2299 if self.table.named_with_column:
2300 return self.table.description + "." + self.description
2301 else:
2302 return self.description
2303 else:
2304 return self.description
2306 def references(self, column: Column[Any]) -> bool:
2307 """Return True if this Column references the given column via foreign
2308 key."""
2310 for fk in self.foreign_keys:
2311 if fk.column.proxy_set.intersection(column.proxy_set):
2312 return True
2313 else:
2314 return False
2316 def append_foreign_key(self, fk: ForeignKey) -> None:
2317 fk._set_parent_with_dispatch(self)
2319 def __repr__(self) -> str:
2320 kwarg = []
2321 if self.key != self.name:
2322 kwarg.append("key")
2323 if self.primary_key:
2324 kwarg.append("primary_key")
2325 if not self.nullable:
2326 kwarg.append("nullable")
2327 if self.onupdate:
2328 kwarg.append("onupdate")
2329 if self.default:
2330 kwarg.append("default")
2331 if self.server_default:
2332 kwarg.append("server_default")
2333 if self.comment:
2334 kwarg.append("comment")
2335 return "Column(%s)" % ", ".join(
2336 [repr(self.name)]
2337 + [repr(self.type)]
2338 + [repr(x) for x in self.foreign_keys if x is not None]
2339 + [repr(x) for x in self.constraints]
2340 + [
2341 (
2342 self.table is not None
2343 and "table=<%s>" % self.table.description
2344 or "table=None"
2345 )
2346 ]
2347 + ["%s=%s" % (k, repr(getattr(self, k))) for k in kwarg]
2348 )
2350 def _set_parent( # type: ignore[override]
2351 self,
2352 parent: SchemaEventTarget,
2353 *,
2354 all_names: Dict[str, Column[Any]],
2355 allow_replacements: bool,
2356 index: Optional[int] = None,
2357 **kw: Any,
2358 ) -> None:
2359 table = parent
2360 assert isinstance(table, Table)
2361 if not self.name:
2362 raise exc.ArgumentError(
2363 "Column must be constructed with a non-blank name or "
2364 "assign a non-blank .name before adding to a Table."
2365 )
2367 self._reset_memoizations()
2369 if self.key is None:
2370 self.key = self.name
2372 existing = getattr(self, "table", None)
2373 if existing is not None and existing is not table:
2374 raise exc.ArgumentError(
2375 f"Column object '{self.key}' already "
2376 f"assigned to Table '{existing.description}'"
2377 )
2379 extra_remove = None
2380 existing_col = None
2381 conflicts_on = ""
2383 if self.key in table._columns:
2384 existing_col = table._columns[self.key]
2385 if self.key == self.name:
2386 conflicts_on = "name"
2387 else:
2388 conflicts_on = "key"
2389 elif self.name in all_names:
2390 existing_col = all_names[self.name]
2391 extra_remove = {existing_col}
2392 conflicts_on = "name"
2394 if existing_col is not None:
2395 if existing_col is not self:
2396 if not allow_replacements:
2397 raise exc.DuplicateColumnError(
2398 f"A column with {conflicts_on} "
2399 f"""'{
2400 self.key if conflicts_on == 'key' else self.name
2401 }' """
2402 f"is already present in table '{table.name}'."
2403 )
2404 for fk in existing_col.foreign_keys:
2405 table.foreign_keys.remove(fk)
2406 if fk.constraint in table.constraints:
2407 # this might have been removed
2408 # already, if it's a composite constraint
2409 # and more than one col being replaced
2410 table.constraints.remove(fk.constraint)
2412 if extra_remove and existing_col is not None and self.key == self.name:
2413 util.warn(
2414 f'Column with user-specified key "{existing_col.key}" is '
2415 "being replaced with "
2416 f'plain named column "{self.name}", '
2417 f'key "{existing_col.key}" is being removed. If this is a '
2418 "reflection operation, specify autoload_replace=False to "
2419 "prevent this replacement."
2420 )
2421 table._columns.replace(self, extra_remove=extra_remove, index=index)
2422 all_names[self.name] = self
2423 self.table = table
2425 if self._insert_sentinel:
2426 if self.table._sentinel_column is not None:
2427 raise exc.ArgumentError(
2428 "a Table may have only one explicit sentinel column"
2429 )
2430 self.table._sentinel_column = self
2432 if self.primary_key:
2433 table.primary_key._replace(self)
2434 elif self.key in table.primary_key:
2435 raise exc.ArgumentError(
2436 f"Trying to redefine primary-key column '{self.key}' as a "
2437 f"non-primary-key column on table '{table.fullname}'"
2438 )
2440 if self.index:
2441 if isinstance(self.index, str):
2442 raise exc.ArgumentError(
2443 "The 'index' keyword argument on Column is boolean only. "
2444 "To create indexes with a specific name, create an "
2445 "explicit Index object external to the Table."
2446 )
2447 table.append_constraint(
2448 Index(
2449 None, self.key, unique=bool(self.unique), _column_flag=True
2450 )
2451 )
2453 elif self.unique:
2454 if isinstance(self.unique, str):
2455 raise exc.ArgumentError(
2456 "The 'unique' keyword argument on Column is boolean "
2457 "only. To create unique constraints or indexes with a "
2458 "specific name, append an explicit UniqueConstraint to "
2459 "the Table's list of elements, or create an explicit "
2460 "Index object external to the Table."
2461 )
2462 table.append_constraint(
2463 UniqueConstraint(self.key, _column_flag=True)
2464 )
2466 self._setup_on_memoized_fks(lambda fk: fk._set_remote_table(table))
2468 if self.identity and (
2469 isinstance(self.default, Sequence)
2470 or isinstance(self.onupdate, Sequence)
2471 ):
2472 raise exc.ArgumentError(
2473 "An column cannot specify both Identity and Sequence."
2474 )
2476 def _setup_on_memoized_fks(self, fn: Callable[..., Any]) -> None:
2477 fk_keys = [
2478 ((self.table.key, self.key), False),
2479 ((self.table.key, self.name), True),
2480 ]
2481 for fk_key, link_to_name in fk_keys:
2482 if fk_key in self.table.metadata._fk_memos:
2483 for fk in self.table.metadata._fk_memos[fk_key]:
2484 if fk.link_to_name is link_to_name:
2485 fn(fk)
2487 def _on_table_attach(self, fn: Callable[..., Any]) -> None:
2488 if self.table is not None:
2489 fn(self, self.table)
2490 else:
2491 event.listen(self, "after_parent_attach", fn)
2493 @util.deprecated(
2494 "1.4",
2495 "The :meth:`_schema.Column.copy` method is deprecated "
2496 "and will be removed in a future release.",
2497 )
2498 def copy(self, **kw: Any) -> Column[Any]:
2499 return self._copy(**kw)
2501 def _copy(self, **kw: Any) -> Column[Any]:
2502 """Create a copy of this ``Column``, uninitialized.
2504 This is used in :meth:`_schema.Table.to_metadata` and by the ORM.
2506 """
2508 # Constraint objects plus non-constraint-bound ForeignKey objects
2509 args: List[SchemaItem] = [
2510 c._copy(**kw) for c in self.constraints if not c._type_bound
2511 ] + [c._copy(**kw) for c in self.foreign_keys if not c.constraint]
2513 # ticket #5276
2514 column_kwargs = {}
2515 for dialect_name in self.dialect_options:
2516 dialect_options = self.dialect_options[dialect_name]._non_defaults
2517 for (
2518 dialect_option_key,
2519 dialect_option_value,
2520 ) in dialect_options.items():
2521 column_kwargs[dialect_name + "_" + dialect_option_key] = (
2522 dialect_option_value
2523 )
2525 server_default = self.server_default
2526 server_onupdate = self.server_onupdate
2527 if isinstance(server_default, (Computed, Identity)):
2528 # TODO: likely should be copied in all cases
2529 # TODO: if a Sequence, we would need to transfer the Sequence
2530 # .metadata as well
2531 args.append(server_default._copy(**kw))
2532 server_default = server_onupdate = None
2534 type_ = self.type
2535 if isinstance(type_, SchemaEventTarget):
2536 type_ = type_.copy(**kw)
2538 # TODO: DefaultGenerator is not copied here! it's just used again
2539 # with _set_parent() pointing to the old column. see the new
2540 # use of _copy() in the new _merge() method
2542 c = self._constructor(
2543 name=self.name,
2544 type_=type_,
2545 key=self.key,
2546 primary_key=self.primary_key,
2547 unique=self.unique,
2548 system=self.system,
2549 # quote=self.quote, # disabled 2013-08-27 (commit 031ef080)
2550 index=self.index,
2551 autoincrement=self.autoincrement,
2552 default=self.default,
2553 server_default=server_default,
2554 onupdate=self.onupdate,
2555 server_onupdate=server_onupdate,
2556 doc=self.doc,
2557 comment=self.comment,
2558 _omit_from_statements=self._omit_from_statements,
2559 insert_sentinel=self._insert_sentinel,
2560 *args,
2561 **column_kwargs,
2562 )
2564 # copy the state of "nullable" exactly, to accommodate for
2565 # ORM flipping the .nullable flag directly
2566 c.nullable = self.nullable
2567 c._user_defined_nullable = self._user_defined_nullable
2569 return self._schema_item_copy(c)
2571 def _merge(
2572 self, other: Column[Any], *, omit_defaults: bool = False
2573 ) -> None:
2574 """merge the elements of this column onto "other"
2576 this is used by ORM pep-593 merge and will likely need a lot
2577 of fixes.
2580 """
2582 if self.primary_key:
2583 other.primary_key = True
2585 if self.autoincrement != "auto" and other.autoincrement == "auto":
2586 other.autoincrement = self.autoincrement
2588 if self.system:
2589 other.system = self.system
2591 if self.info:
2592 other.info.update(self.info)
2594 type_ = self.type
2595 if not type_._isnull and other.type._isnull:
2596 if isinstance(type_, SchemaEventTarget):
2597 type_ = type_.copy()
2599 other.type = type_
2601 if isinstance(type_, SchemaEventTarget):
2602 type_._set_parent_with_dispatch(other)
2604 for impl in type_._variant_mapping.values():
2605 if isinstance(impl, SchemaEventTarget):
2606 impl._set_parent_with_dispatch(other)
2608 if (
2609 self._user_defined_nullable is not NULL_UNSPECIFIED
2610 and other._user_defined_nullable is NULL_UNSPECIFIED
2611 ):
2612 other.nullable = self.nullable
2613 other._user_defined_nullable = self._user_defined_nullable
2615 if (
2616 not omit_defaults
2617 and self.default is not None
2618 and other.default is None
2619 ):
2620 new_default = self.default._copy()
2621 new_default._set_parent(other)
2623 if self.server_default and other.server_default is None:
2624 new_server_default = self.server_default
2625 if isinstance(new_server_default, FetchedValue):
2626 new_server_default = new_server_default._copy()
2627 new_server_default._set_parent(other)
2628 else:
2629 other.server_default = new_server_default
2631 if self.server_onupdate and other.server_onupdate is None:
2632 new_server_onupdate = self.server_onupdate
2633 new_server_onupdate = new_server_onupdate._copy()
2634 new_server_onupdate._set_parent(other)
2636 if self.onupdate and other.onupdate is None:
2637 new_onupdate = self.onupdate._copy()
2638 new_onupdate._set_parent(other)
2640 if self.index in (True, False) and other.index is None:
2641 other.index = self.index
2643 if self.unique in (True, False) and other.unique is None:
2644 other.unique = self.unique
2646 if self.doc and other.doc is None:
2647 other.doc = self.doc
2649 if self.comment and other.comment is None:
2650 other.comment = self.comment
2652 for const in self.constraints:
2653 if not const._type_bound:
2654 new_const = const._copy()
2655 new_const._set_parent(other)
2657 for fk in self.foreign_keys:
2658 if not fk.constraint:
2659 new_fk = fk._copy()
2660 new_fk._set_parent(other)
2662 def _make_proxy(
2663 self,
2664 selectable: FromClause,
2665 primary_key: ColumnSet,
2666 foreign_keys: Set[KeyedColumnElement[Any]],
2667 name: Optional[str] = None,
2668 key: Optional[str] = None,
2669 name_is_truncatable: bool = False,
2670 compound_select_cols: Optional[
2671 _typing_Sequence[ColumnElement[Any]]
2672 ] = None,
2673 **kw: Any,
2674 ) -> Tuple[str, ColumnClause[_T]]:
2675 """Create a *proxy* for this column.
2677 This is a copy of this ``Column`` referenced by a different parent
2678 (such as an alias or select statement). The column should
2679 be used only in select scenarios, as its full DDL/default
2680 information is not transferred.
2682 """
2684 fk = [
2685 ForeignKey(
2686 col if col is not None else f._colspec,
2687 _unresolvable=col is None,
2688 _constraint=f.constraint,
2689 )
2690 for f, col in [
2691 (fk, fk._resolve_column(raiseerr=False))
2692 for fk in self.foreign_keys
2693 ]
2694 ]
2696 if name is None and self.name is None:
2697 raise exc.InvalidRequestError(
2698 "Cannot initialize a sub-selectable"
2699 " with this Column object until its 'name' has "
2700 "been assigned."
2701 )
2702 try:
2703 c = self._constructor(
2704 (
2705 coercions.expect(
2706 roles.TruncatedLabelRole, name if name else self.name
2707 )
2708 if name_is_truncatable
2709 else (name or self.name)
2710 ),
2711 self.type,
2712 # this may actually be ._proxy_key when the key is incoming
2713 key=key if key else name if name else self.key,
2714 primary_key=self.primary_key,
2715 nullable=self.nullable,
2716 _proxies=(
2717 list(compound_select_cols)
2718 if compound_select_cols
2719 else [self]
2720 ),
2721 *fk,
2722 )
2723 except TypeError as err:
2724 raise TypeError(
2725 "Could not create a copy of this %r object. "
2726 "Ensure the class includes a _constructor() "
2727 "attribute or method which accepts the "
2728 "standard Column constructor arguments, or "
2729 "references the Column class itself." % self.__class__
2730 ) from err
2732 c.table = selectable
2733 c._propagate_attrs = selectable._propagate_attrs
2734 if selectable._is_clone_of is not None:
2735 c._is_clone_of = selectable._is_clone_of.columns.get(c.key)
2737 if self.primary_key:
2738 primary_key.add(c)
2740 if fk:
2741 foreign_keys.update(fk) # type: ignore
2743 return c.key, c
2746def insert_sentinel(
2747 name: Optional[str] = None,
2748 type_: Optional[_TypeEngineArgument[_T]] = None,
2749 *,
2750 default: Optional[Any] = None,
2751 omit_from_statements: bool = True,
2752) -> Column[Any]:
2753 """Provides a surrogate :class:`_schema.Column` that will act as a
2754 dedicated insert :term:`sentinel` column, allowing efficient bulk
2755 inserts with deterministic RETURNING sorting for tables that
2756 don't otherwise have qualifying primary key configurations.
2758 Adding this column to a :class:`.Table` object requires that a
2759 corresponding database table actually has this column present, so if adding
2760 it to an existing model, existing database tables would need to be migrated
2761 (e.g. using ALTER TABLE or similar) to include this column.
2763 For background on how this object is used, see the section
2764 :ref:`engine_insertmanyvalues_sentinel_columns` as part of the
2765 section :ref:`engine_insertmanyvalues`.
2767 The :class:`_schema.Column` returned will be a nullable integer column by
2768 default and make use of a sentinel-specific default generator used only in
2769 "insertmanyvalues" operations.
2771 .. seealso::
2773 :func:`_orm.orm_insert_sentinel`
2775 :paramref:`_schema.Column.insert_sentinel`
2777 :ref:`engine_insertmanyvalues`
2779 :ref:`engine_insertmanyvalues_sentinel_columns`
2782 .. versionadded:: 2.0.10
2784 """
2785 return Column(
2786 name=name,
2787 type_=type_api.INTEGERTYPE if type_ is None else type_,
2788 default=(
2789 default if default is not None else _InsertSentinelColumnDefault()
2790 ),
2791 _omit_from_statements=omit_from_statements,
2792 insert_sentinel=True,
2793 )
2796class ForeignKey(DialectKWArgs, SchemaItem):
2797 """Defines a dependency between two columns.
2799 ``ForeignKey`` is specified as an argument to a :class:`_schema.Column`
2800 object,
2801 e.g.::
2803 t = Table(
2804 "remote_table",
2805 metadata,
2806 Column("remote_id", ForeignKey("main_table.id")),
2807 )
2809 Note that ``ForeignKey`` is only a marker object that defines
2810 a dependency between two columns. The actual constraint
2811 is in all cases represented by the :class:`_schema.ForeignKeyConstraint`
2812 object. This object will be generated automatically when
2813 a ``ForeignKey`` is associated with a :class:`_schema.Column` which
2814 in turn is associated with a :class:`_schema.Table`. Conversely,
2815 when :class:`_schema.ForeignKeyConstraint` is applied to a
2816 :class:`_schema.Table`,
2817 ``ForeignKey`` markers are automatically generated to be
2818 present on each associated :class:`_schema.Column`, which are also
2819 associated with the constraint object.
2821 Note that you cannot define a "composite" foreign key constraint,
2822 that is a constraint between a grouping of multiple parent/child
2823 columns, using ``ForeignKey`` objects. To define this grouping,
2824 the :class:`_schema.ForeignKeyConstraint` object must be used, and applied
2825 to the :class:`_schema.Table`. The associated ``ForeignKey`` objects
2826 are created automatically.
2828 The ``ForeignKey`` objects associated with an individual
2829 :class:`_schema.Column`
2830 object are available in the `foreign_keys` collection
2831 of that column.
2833 Further examples of foreign key configuration are in
2834 :ref:`metadata_foreignkeys`.
2836 """
2838 __visit_name__ = "foreign_key"
2840 parent: Column[Any]
2842 _table_column: Optional[Column[Any]]
2844 _colspec: Union[str, Column[Any]]
2846 def __init__(
2847 self,
2848 column: _DDLColumnReferenceArgument,
2849 _constraint: Optional[ForeignKeyConstraint] = None,
2850 use_alter: bool = False,
2851 name: _ConstraintNameArgument = None,
2852 onupdate: Optional[str] = None,
2853 ondelete: Optional[str] = None,
2854 deferrable: Optional[bool] = None,
2855 initially: Optional[str] = None,
2856 link_to_name: bool = False,
2857 match: Optional[str] = None,
2858 info: Optional[_InfoType] = None,
2859 comment: Optional[str] = None,
2860 _unresolvable: bool = False,
2861 **dialect_kw: Any,
2862 ):
2863 r"""
2864 Construct a column-level FOREIGN KEY.
2866 The :class:`_schema.ForeignKey` object when constructed generates a
2867 :class:`_schema.ForeignKeyConstraint`
2868 which is associated with the parent
2869 :class:`_schema.Table` object's collection of constraints.
2871 :param column: A single target column for the key relationship. A
2872 :class:`_schema.Column` object or a column name as a string:
2873 ``tablename.columnkey`` or ``schema.tablename.columnkey``.
2874 ``columnkey`` is the ``key`` which has been assigned to the column
2875 (defaults to the column name itself), unless ``link_to_name`` is
2876 ``True`` in which case the rendered name of the column is used.
2878 :param name: Optional string. An in-database name for the key if
2879 `constraint` is not provided.
2881 :param onupdate: Optional string. If set, emit ON UPDATE <value> when
2882 issuing DDL for this constraint. Typical values include CASCADE,
2883 DELETE and RESTRICT.
2885 .. seealso::
2887 :ref:`on_update_on_delete`
2889 :param ondelete: Optional string. If set, emit ON DELETE <value> when
2890 issuing DDL for this constraint. Typical values include CASCADE,
2891 SET NULL and RESTRICT. Some dialects may allow for additional
2892 syntaxes.
2894 .. seealso::
2896 :ref:`on_update_on_delete`
2898 :param deferrable: Optional bool. If set, emit DEFERRABLE or NOT
2899 DEFERRABLE when issuing DDL for this constraint.
2901 :param initially: Optional string. If set, emit INITIALLY <value> when
2902 issuing DDL for this constraint.
2904 :param link_to_name: if True, the string name given in ``column`` is
2905 the rendered name of the referenced column, not its locally
2906 assigned ``key``.
2908 :param use_alter: passed to the underlying
2909 :class:`_schema.ForeignKeyConstraint`
2910 to indicate the constraint should
2911 be generated/dropped externally from the CREATE TABLE/ DROP TABLE
2912 statement. See :paramref:`_schema.ForeignKeyConstraint.use_alter`
2913 for further description.
2915 .. seealso::
2917 :paramref:`_schema.ForeignKeyConstraint.use_alter`
2919 :ref:`use_alter`
2921 :param match: Optional string. If set, emit MATCH <value> when issuing
2922 DDL for this constraint. Typical values include SIMPLE, PARTIAL
2923 and FULL.
2925 :param info: Optional data dictionary which will be populated into the
2926 :attr:`.SchemaItem.info` attribute of this object.
2928 :param comment: Optional string that will render an SQL comment on
2929 foreign key constraint creation.
2931 .. versionadded:: 2.0
2933 :param \**dialect_kw: Additional keyword arguments are dialect
2934 specific, and passed in the form ``<dialectname>_<argname>``. The
2935 arguments are ultimately handled by a corresponding
2936 :class:`_schema.ForeignKeyConstraint`.
2937 See the documentation regarding
2938 an individual dialect at :ref:`dialect_toplevel` for detail on
2939 documented arguments.
2941 """
2943 self._unresolvable = _unresolvable
2945 self._colspec, self._table_column = self._parse_colspec_argument(
2946 column
2947 )
2949 # the linked ForeignKeyConstraint.
2950 # ForeignKey will create this when parent Column
2951 # is attached to a Table, *or* ForeignKeyConstraint
2952 # object passes itself in when creating ForeignKey
2953 # markers.
2954 self.constraint = _constraint
2956 # .parent is not Optional under normal use
2957 self.parent = None # type: ignore
2959 self.use_alter = use_alter
2960 self.name = name
2961 self.onupdate = onupdate
2962 self.ondelete = ondelete
2963 self.deferrable = deferrable
2964 self.initially = initially
2965 self.link_to_name = link_to_name
2966 self.match = match
2967 self.comment = comment
2968 if info:
2969 self.info = info
2970 self._unvalidated_dialect_kw = dialect_kw
2972 def _resolve_colspec_argument(
2973 self,
2974 ) -> Tuple[
2975 Union[str, Column[Any]],
2976 Optional[Column[Any]],
2977 ]:
2978 argument = self._colspec
2980 return self._parse_colspec_argument(argument)
2982 def _parse_colspec_argument(
2983 self,
2984 argument: _DDLColumnArgument,
2985 ) -> Tuple[
2986 Union[str, Column[Any]],
2987 Optional[Column[Any]],
2988 ]:
2989 _colspec = coercions.expect(roles.DDLReferredColumnRole, argument)
2991 if isinstance(_colspec, str):
2992 _table_column = None
2993 else:
2994 assert isinstance(_colspec, ColumnClause)
2995 _table_column = _colspec
2997 if not isinstance(_table_column.table, (type(None), TableClause)):
2998 raise exc.ArgumentError(
2999 "ForeignKey received Column not bound "
3000 "to a Table, got: %r" % _table_column.table
3001 )
3003 return _colspec, _table_column
3005 def __repr__(self) -> str:
3006 return "ForeignKey(%r)" % self._get_colspec()
3008 @util.deprecated(
3009 "1.4",
3010 "The :meth:`_schema.ForeignKey.copy` method is deprecated "
3011 "and will be removed in a future release.",
3012 )
3013 def copy(self, *, schema: Optional[str] = None, **kw: Any) -> ForeignKey:
3014 return self._copy(schema=schema, **kw)
3016 def _copy(self, *, schema: Optional[str] = None, **kw: Any) -> ForeignKey:
3017 """Produce a copy of this :class:`_schema.ForeignKey` object.
3019 The new :class:`_schema.ForeignKey` will not be bound
3020 to any :class:`_schema.Column`.
3022 This method is usually used by the internal
3023 copy procedures of :class:`_schema.Column`, :class:`_schema.Table`,
3024 and :class:`_schema.MetaData`.
3026 :param schema: The returned :class:`_schema.ForeignKey` will
3027 reference the original table and column name, qualified
3028 by the given string schema name.
3030 """
3031 fk = ForeignKey(
3032 self._get_colspec(schema=schema),
3033 use_alter=self.use_alter,
3034 name=self.name,
3035 onupdate=self.onupdate,
3036 ondelete=self.ondelete,
3037 deferrable=self.deferrable,
3038 initially=self.initially,
3039 link_to_name=self.link_to_name,
3040 match=self.match,
3041 comment=self.comment,
3042 **self._unvalidated_dialect_kw,
3043 )
3044 return self._schema_item_copy(fk)
3046 def _get_colspec(
3047 self,
3048 schema: Optional[
3049 Union[
3050 str,
3051 Literal[SchemaConst.RETAIN_SCHEMA, SchemaConst.BLANK_SCHEMA],
3052 ]
3053 ] = None,
3054 table_name: Optional[str] = None,
3055 _is_copy: bool = False,
3056 ) -> str:
3057 """Return a string based 'column specification' for this
3058 :class:`_schema.ForeignKey`.
3060 This is usually the equivalent of the string-based "tablename.colname"
3061 argument first passed to the object's constructor.
3063 """
3065 _colspec, effective_table_column = self._resolve_colspec_argument()
3067 if schema not in (None, RETAIN_SCHEMA):
3068 _schema, tname, colname = self._column_tokens
3069 if table_name is not None:
3070 tname = table_name
3071 if schema is BLANK_SCHEMA:
3072 return "%s.%s" % (tname, colname)
3073 else:
3074 return "%s.%s.%s" % (schema, tname, colname)
3075 elif table_name:
3076 schema, tname, colname = self._column_tokens
3077 if schema:
3078 return "%s.%s.%s" % (schema, table_name, colname)
3079 else:
3080 return "%s.%s" % (table_name, colname)
3081 elif effective_table_column is not None:
3082 if effective_table_column.table is None:
3083 if _is_copy:
3084 raise exc.InvalidRequestError(
3085 f"Can't copy ForeignKey object which refers to "
3086 f"non-table bound Column {effective_table_column!r}"
3087 )
3088 else:
3089 return effective_table_column.key
3090 return "%s.%s" % (
3091 effective_table_column.table.fullname,
3092 effective_table_column.key,
3093 )
3094 else:
3095 assert isinstance(_colspec, str)
3096 return _colspec
3098 @property
3099 def _referred_schema(self) -> Optional[str]:
3100 return self._column_tokens[0]
3102 def _table_key_within_construction(self) -> Any:
3103 """get the table key but only safely"""
3105 if self._table_column is not None:
3106 if self._table_column.table is None:
3107 return None
3108 else:
3109 return self._table_column.table.key
3110 else:
3111 schema, tname, colname = self._column_tokens
3112 return _get_table_key(tname, schema)
3114 target_fullname = property(_get_colspec)
3116 def references(self, table: Table) -> bool:
3117 """Return True if the given :class:`_schema.Table`
3118 is referenced by this
3119 :class:`_schema.ForeignKey`."""
3121 return table.corresponding_column(self.column) is not None
3123 def get_referent(self, table: FromClause) -> Optional[Column[Any]]:
3124 """Return the :class:`_schema.Column` in the given
3125 :class:`_schema.Table` (or any :class:`.FromClause`)
3126 referenced by this :class:`_schema.ForeignKey`.
3128 Returns None if this :class:`_schema.ForeignKey`
3129 does not reference the given
3130 :class:`_schema.Table`.
3132 """
3133 # our column is a Column, and any subquery etc. proxying us
3134 # would be doing so via another Column, so that's what would
3135 # be returned here
3136 return table.columns.corresponding_column(self.column) # type: ignore
3138 @util.memoized_property
3139 def _column_tokens(self) -> Tuple[Optional[str], str, Optional[str]]:
3140 """parse a string-based _colspec into its component parts."""
3142 m = self._get_colspec().split(".")
3143 if len(m) == 1:
3144 tname = m.pop()
3145 colname = None
3146 else:
3147 colname = m.pop()
3148 tname = m.pop()
3150 # A FK between column 'bar' and table 'foo' can be
3151 # specified as 'foo', 'foo.bar', 'dbo.foo.bar',
3152 # 'otherdb.dbo.foo.bar'. Once we have the column name and
3153 # the table name, treat everything else as the schema
3154 # name. Some databases (e.g. Sybase) support
3155 # inter-database foreign keys. See tickets#1341 and --
3156 # indirectly related -- Ticket #594. This assumes that '.'
3157 # will never appear *within* any component of the FK.
3159 if len(m) > 0:
3160 schema = ".".join(m)
3161 else:
3162 schema = None
3163 return schema, tname, colname
3165 def _resolve_col_tokens(self) -> Tuple[Table, str, Optional[str]]:
3166 if self.parent is None:
3167 raise exc.InvalidRequestError(
3168 "this ForeignKey object does not yet have a "
3169 "parent Column associated with it."
3170 )
3172 elif self.parent.table is None:
3173 raise exc.InvalidRequestError(
3174 "this ForeignKey's parent column is not yet associated "
3175 "with a Table."
3176 )
3178 parenttable = self.parent.table
3180 if self._unresolvable:
3181 schema, tname, colname = self._column_tokens
3182 tablekey = _get_table_key(tname, schema)
3183 return parenttable, tablekey, colname
3185 # assertion
3186 # basically Column._make_proxy() sends the actual
3187 # target Column to the ForeignKey object, so the
3188 # string resolution here is never called.
3189 for c in self.parent.base_columns:
3190 if isinstance(c, Column):
3191 assert c.table is parenttable
3192 break
3193 else:
3194 assert False
3195 ######################
3197 schema, tname, colname = self._column_tokens
3199 if schema is None and parenttable.metadata.schema is not None:
3200 schema = parenttable.metadata.schema
3202 tablekey = _get_table_key(tname, schema)
3203 return parenttable, tablekey, colname
3205 def _link_to_col_by_colstring(
3206 self, parenttable: Table, table: Table, colname: Optional[str]
3207 ) -> Column[Any]:
3208 _column = None
3209 if colname is None:
3210 # colname is None in the case that ForeignKey argument
3211 # was specified as table name only, in which case we
3212 # match the column name to the same column on the
3213 # parent.
3214 # this use case wasn't working in later 1.x series
3215 # as it had no test coverage; fixed in 2.0
3216 parent = self.parent
3217 assert parent is not None
3218 key = parent.key
3219 _column = table.c.get(key, None)
3220 elif self.link_to_name:
3221 key = colname
3222 for c in table.c:
3223 if c.name == colname:
3224 _column = c
3225 else:
3226 key = colname
3227 _column = table.c.get(colname, None)
3229 if _column is None:
3230 raise exc.NoReferencedColumnError(
3231 "Could not initialize target column "
3232 f"for ForeignKey '{self._get_colspec()}' "
3233 f"on table '{parenttable.name}': "
3234 f"table '{table.name}' has no column named '{key}'",
3235 table.name,
3236 key,
3237 )
3239 return _column
3241 def _set_target_column(self, column: Column[Any]) -> None:
3242 assert self.parent is not None
3244 # propagate TypeEngine to parent if it didn't have one
3245 if self.parent.type._isnull:
3246 self.parent.type = column.type
3248 # super-edgy case, if other FKs point to our column,
3249 # they'd get the type propagated out also.
3251 def set_type(fk: ForeignKey) -> None:
3252 if fk.parent.type._isnull:
3253 fk.parent.type = column.type
3255 self.parent._setup_on_memoized_fks(set_type)
3257 self.column = column # type: ignore
3259 @util.ro_memoized_property
3260 def column(self) -> Column[Any]:
3261 """Return the target :class:`_schema.Column` referenced by this
3262 :class:`_schema.ForeignKey`.
3264 If no target column has been established, an exception
3265 is raised.
3267 """
3268 return self._resolve_column()
3270 @overload
3271 def _resolve_column(
3272 self, *, raiseerr: Literal[True] = ...
3273 ) -> Column[Any]: ...
3275 @overload
3276 def _resolve_column(
3277 self, *, raiseerr: bool = ...
3278 ) -> Optional[Column[Any]]: ...
3280 def _resolve_column(
3281 self, *, raiseerr: bool = True
3282 ) -> Optional[Column[Any]]:
3283 _column: Column[Any]
3285 _colspec, effective_table_column = self._resolve_colspec_argument()
3287 if isinstance(_colspec, str):
3288 parenttable, tablekey, colname = self._resolve_col_tokens()
3290 if self._unresolvable or tablekey not in parenttable.metadata:
3291 if not raiseerr:
3292 return None
3293 raise exc.NoReferencedTableError(
3294 f"Foreign key associated with column "
3295 f"'{self.parent}' could not find "
3296 f"table '{tablekey}' with which to generate a "
3297 f"foreign key to target column '{colname}'",
3298 tablekey,
3299 )
3300 elif parenttable.key not in parenttable.metadata:
3301 if not raiseerr:
3302 return None
3303 raise exc.InvalidRequestError(
3304 f"Table {parenttable} is no longer associated with its "
3305 "parent MetaData"
3306 )
3307 else:
3308 table = parenttable.metadata.tables[tablekey]
3309 return self._link_to_col_by_colstring(
3310 parenttable, table, colname
3311 )
3313 elif hasattr(_colspec, "__clause_element__"):
3314 _column = _colspec.__clause_element__()
3315 return _column
3316 else:
3317 assert isinstance(_colspec, Column)
3318 _column = _colspec
3319 return _column
3321 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
3322 assert isinstance(parent, Column)
3324 if self.parent is not None and self.parent is not parent:
3325 raise exc.InvalidRequestError(
3326 "This ForeignKey already has a parent !"
3327 )
3328 self.parent = parent
3329 self.parent.foreign_keys.add(self)
3330 self.parent._on_table_attach(self._set_table)
3332 def _set_remote_table(self, table: Table) -> None:
3333 parenttable, _, colname = self._resolve_col_tokens()
3334 _column = self._link_to_col_by_colstring(parenttable, table, colname)
3335 self._set_target_column(_column)
3336 assert self.constraint is not None
3337 self.constraint._validate_dest_table(table)
3339 def _remove_from_metadata(self, metadata: MetaData) -> None:
3340 parenttable, table_key, colname = self._resolve_col_tokens()
3341 fk_key = (table_key, colname)
3343 if self in metadata._fk_memos[fk_key]:
3344 # TODO: no test coverage for self not in memos
3345 metadata._fk_memos[fk_key].remove(self)
3347 def _set_table(self, column: Column[Any], table: Table) -> None:
3348 # standalone ForeignKey - create ForeignKeyConstraint
3349 # on the hosting Table when attached to the Table.
3350 assert isinstance(table, Table)
3351 if self.constraint is None:
3352 self.constraint = ForeignKeyConstraint(
3353 [],
3354 [],
3355 use_alter=self.use_alter,
3356 name=self.name,
3357 onupdate=self.onupdate,
3358 ondelete=self.ondelete,
3359 deferrable=self.deferrable,
3360 initially=self.initially,
3361 match=self.match,
3362 comment=self.comment,
3363 **self._unvalidated_dialect_kw,
3364 )
3365 self.constraint._append_element(column, self)
3366 self.constraint._set_parent_with_dispatch(table)
3367 table.foreign_keys.add(self)
3368 # set up remote ".column" attribute, or a note to pick it
3369 # up when the other Table/Column shows up
3371 _colspec, _ = self._resolve_colspec_argument()
3372 if isinstance(_colspec, str):
3373 parenttable, table_key, colname = self._resolve_col_tokens()
3374 fk_key = (table_key, colname)
3375 if table_key in parenttable.metadata.tables:
3376 table = parenttable.metadata.tables[table_key]
3377 try:
3378 _column = self._link_to_col_by_colstring(
3379 parenttable, table, colname
3380 )
3381 except exc.NoReferencedColumnError:
3382 # this is OK, we'll try later
3383 pass
3384 else:
3385 self._set_target_column(_column)
3387 parenttable.metadata._fk_memos[fk_key].append(self)
3388 elif hasattr(_colspec, "__clause_element__"):
3389 _column = _colspec.__clause_element__()
3390 self._set_target_column(_column)
3391 else:
3392 self._set_target_column(_colspec)
3395if TYPE_CHECKING:
3397 def default_is_sequence(
3398 obj: Optional[DefaultGenerator],
3399 ) -> TypeGuard[Sequence]: ...
3401 def default_is_clause_element(
3402 obj: Optional[DefaultGenerator],
3403 ) -> TypeGuard[ColumnElementColumnDefault]: ...
3405 def default_is_scalar(
3406 obj: Optional[DefaultGenerator],
3407 ) -> TypeGuard[ScalarElementColumnDefault]: ...
3409else:
3410 default_is_sequence = operator.attrgetter("is_sequence")
3412 default_is_clause_element = operator.attrgetter("is_clause_element")
3414 default_is_scalar = operator.attrgetter("is_scalar")
3417class DefaultGenerator(Executable, SchemaItem):
3418 """Base class for column *default* values.
3420 This object is only present on column.default or column.onupdate.
3421 It's not valid as a server default.
3423 """
3425 __visit_name__ = "default_generator"
3427 _is_default_generator = True
3428 is_sequence = False
3429 is_identity = False
3430 is_server_default = False
3431 is_clause_element = False
3432 is_callable = False
3433 is_scalar = False
3434 has_arg = False
3435 is_sentinel = False
3436 column: Optional[Column[Any]]
3438 def __init__(self, for_update: bool = False) -> None:
3439 self.for_update = for_update
3441 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
3442 if TYPE_CHECKING:
3443 assert isinstance(parent, Column)
3444 self.column = parent
3445 if self.for_update:
3446 self.column.onupdate = self
3447 else:
3448 self.column.default = self
3450 def _copy(self) -> DefaultGenerator:
3451 raise NotImplementedError()
3453 def _execute_on_connection(
3454 self,
3455 connection: Connection,
3456 distilled_params: _CoreMultiExecuteParams,
3457 execution_options: CoreExecuteOptionsParameter,
3458 ) -> Any:
3459 util.warn_deprecated(
3460 "Using the .execute() method to invoke a "
3461 "DefaultGenerator object is deprecated; please use "
3462 "the .scalar() method.",
3463 "2.0",
3464 )
3465 return self._execute_on_scalar(
3466 connection, distilled_params, execution_options
3467 )
3469 def _execute_on_scalar(
3470 self,
3471 connection: Connection,
3472 distilled_params: _CoreMultiExecuteParams,
3473 execution_options: CoreExecuteOptionsParameter,
3474 ) -> Any:
3475 return connection._execute_default(
3476 self, distilled_params, execution_options
3477 )
3480class ColumnDefault(DefaultGenerator, ABC):
3481 """A plain default value on a column.
3483 This could correspond to a constant, a callable function,
3484 or a SQL clause.
3486 :class:`.ColumnDefault` is generated automatically
3487 whenever the ``default``, ``onupdate`` arguments of
3488 :class:`_schema.Column` are used. A :class:`.ColumnDefault`
3489 can be passed positionally as well.
3491 For example, the following::
3493 Column("foo", Integer, default=50)
3495 Is equivalent to::
3497 Column("foo", Integer, ColumnDefault(50))
3499 """
3501 arg: Any
3503 @overload
3504 def __new__(
3505 cls, arg: Callable[..., Any], for_update: bool = ...
3506 ) -> CallableColumnDefault: ...
3508 @overload
3509 def __new__(
3510 cls, arg: ColumnElement[Any], for_update: bool = ...
3511 ) -> ColumnElementColumnDefault: ...
3513 # if I return ScalarElementColumnDefault here, which is what's actually
3514 # returned, mypy complains that
3515 # overloads overlap w/ incompatible return types.
3516 @overload
3517 def __new__(cls, arg: object, for_update: bool = ...) -> ColumnDefault: ...
3519 def __new__(
3520 cls, arg: Any = None, for_update: bool = False
3521 ) -> ColumnDefault:
3522 """Construct a new :class:`.ColumnDefault`.
3525 :param arg: argument representing the default value.
3526 May be one of the following:
3528 * a plain non-callable Python value, such as a
3529 string, integer, boolean, or other simple type.
3530 The default value will be used as is each time.
3531 * a SQL expression, that is one which derives from
3532 :class:`_expression.ColumnElement`. The SQL expression will
3533 be rendered into the INSERT or UPDATE statement,
3534 or in the case of a primary key column when
3535 RETURNING is not used may be
3536 pre-executed before an INSERT within a SELECT.
3537 * A Python callable. The function will be invoked for each
3538 new row subject to an INSERT or UPDATE.
3539 The callable must accept exactly
3540 zero or one positional arguments. The one-argument form
3541 will receive an instance of the :class:`.ExecutionContext`,
3542 which provides contextual information as to the current
3543 :class:`_engine.Connection` in use as well as the current
3544 statement and parameters.
3546 """
3548 if isinstance(arg, FetchedValue):
3549 raise exc.ArgumentError(
3550 "ColumnDefault may not be a server-side default type."
3551 )
3552 elif callable(arg):
3553 cls = CallableColumnDefault
3554 elif isinstance(arg, ClauseElement):
3555 cls = ColumnElementColumnDefault
3556 elif arg is not None:
3557 cls = ScalarElementColumnDefault
3559 return object.__new__(cls)
3561 def __repr__(self) -> str:
3562 return f"{self.__class__.__name__}({self.arg!r})"
3565class ScalarElementColumnDefault(ColumnDefault):
3566 """default generator for a fixed scalar Python value
3568 .. versionadded:: 2.0
3570 """
3572 is_scalar = True
3573 has_arg = True
3575 def __init__(self, arg: Any, for_update: bool = False) -> None:
3576 self.for_update = for_update
3577 self.arg = arg
3579 def _copy(self) -> ScalarElementColumnDefault:
3580 return ScalarElementColumnDefault(
3581 arg=self.arg, for_update=self.for_update
3582 )
3585class _InsertSentinelColumnDefault(ColumnDefault):
3586 """Default generator that's specific to the use of a "sentinel" column
3587 when using the insertmanyvalues feature.
3589 This default is used as part of the :func:`_schema.insert_sentinel`
3590 construct.
3592 """
3594 is_sentinel = True
3595 for_update = False
3596 arg = None
3598 def __new__(cls) -> _InsertSentinelColumnDefault:
3599 return object.__new__(cls)
3601 def __init__(self) -> None:
3602 pass
3604 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
3605 col = cast("Column[Any]", parent)
3606 if not col._insert_sentinel:
3607 raise exc.ArgumentError(
3608 "The _InsertSentinelColumnDefault may only be applied to a "
3609 "Column marked as insert_sentinel=True"
3610 )
3611 elif not col.nullable:
3612 raise exc.ArgumentError(
3613 "The _InsertSentinelColumnDefault may only be applied to a "
3614 "Column that is nullable"
3615 )
3617 super()._set_parent(parent, **kw)
3619 def _copy(self) -> _InsertSentinelColumnDefault:
3620 return _InsertSentinelColumnDefault()
3623_SQLExprDefault = Union["ColumnElement[Any]", "TextClause"]
3626class ColumnElementColumnDefault(ColumnDefault):
3627 """default generator for a SQL expression
3629 .. versionadded:: 2.0
3631 """
3633 is_clause_element = True
3634 has_arg = True
3635 arg: _SQLExprDefault
3637 def __init__(
3638 self,
3639 arg: _SQLExprDefault,
3640 for_update: bool = False,
3641 ) -> None:
3642 self.for_update = for_update
3643 self.arg = arg
3645 def _copy(self) -> ColumnElementColumnDefault:
3646 return ColumnElementColumnDefault(
3647 arg=self.arg, for_update=self.for_update
3648 )
3650 @util.memoized_property
3651 @util.preload_module("sqlalchemy.sql.sqltypes")
3652 def _arg_is_typed(self) -> bool:
3653 sqltypes = util.preloaded.sql_sqltypes
3655 return not isinstance(self.arg.type, sqltypes.NullType)
3658class _CallableColumnDefaultProtocol(Protocol):
3659 def __call__(self, context: ExecutionContext) -> Any: ...
3662class CallableColumnDefault(ColumnDefault):
3663 """default generator for a callable Python function
3665 .. versionadded:: 2.0
3667 """
3669 is_callable = True
3670 arg: _CallableColumnDefaultProtocol
3671 has_arg = True
3673 def __init__(
3674 self,
3675 arg: Union[_CallableColumnDefaultProtocol, Callable[[], Any]],
3676 for_update: bool = False,
3677 ) -> None:
3678 self.for_update = for_update
3679 self.arg = self._maybe_wrap_callable(arg)
3681 def _copy(self) -> CallableColumnDefault:
3682 return CallableColumnDefault(arg=self.arg, for_update=self.for_update)
3684 def _maybe_wrap_callable(
3685 self, fn: Union[_CallableColumnDefaultProtocol, Callable[[], Any]]
3686 ) -> _CallableColumnDefaultProtocol:
3687 """Wrap callables that don't accept a context.
3689 This is to allow easy compatibility with default callables
3690 that aren't specific to accepting of a context.
3692 """
3694 try:
3695 argspec = util.get_callable_argspec(fn, no_self=True)
3696 except TypeError:
3697 return util.wrap_callable(lambda ctx: fn(), fn) # type: ignore
3699 defaulted = argspec[3] is not None and len(argspec[3]) or 0
3700 positionals = len(argspec[0]) - defaulted
3702 if positionals == 0:
3703 return util.wrap_callable(lambda ctx: fn(), fn) # type: ignore
3705 elif positionals == 1:
3706 return fn # type: ignore
3707 else:
3708 raise exc.ArgumentError(
3709 "ColumnDefault Python function takes zero or one "
3710 "positional arguments"
3711 )
3714class IdentityOptions(DialectKWArgs):
3715 """Defines options for a named database sequence or an identity column.
3717 .. seealso::
3719 :class:`.Sequence`
3721 """
3723 def __init__(
3724 self,
3725 start: Optional[int] = None,
3726 increment: Optional[int] = None,
3727 minvalue: Optional[int] = None,
3728 maxvalue: Optional[int] = None,
3729 nominvalue: Optional[bool] = None,
3730 nomaxvalue: Optional[bool] = None,
3731 cycle: Optional[bool] = None,
3732 cache: Optional[int] = None,
3733 order: Optional[bool] = None,
3734 **dialect_kw: Any,
3735 ) -> None:
3736 """Construct a :class:`.IdentityOptions` object.
3738 See the :class:`.Sequence` documentation for a complete description
3739 of the parameters.
3741 :param start: the starting index of the sequence.
3742 :param increment: the increment value of the sequence.
3743 :param minvalue: the minimum value of the sequence.
3744 :param maxvalue: the maximum value of the sequence.
3745 :param nominvalue: no minimum value of the sequence.
3746 :param nomaxvalue: no maximum value of the sequence.
3747 :param cycle: allows the sequence to wrap around when the maxvalue
3748 or minvalue has been reached.
3749 :param cache: optional integer value; number of future values in the
3750 sequence which are calculated in advance.
3751 :param order: optional boolean value; if ``True``, renders the
3752 ORDER keyword.
3754 .. deprecated:: 2.1 Use ``oracle_order`` instead.
3756 """
3757 self.start = start
3758 self.increment = increment
3759 self.minvalue = minvalue
3760 self.maxvalue = maxvalue
3761 self.nominvalue = nominvalue
3762 self.nomaxvalue = nomaxvalue
3763 self.cycle = cycle
3764 self.cache = cache
3765 if order is not None:
3766 if "oracle_order" in dialect_kw:
3767 raise exc.ArgumentError(
3768 "Cannot specify both 'order' and 'oracle_order'. "
3769 "Plese use only 'oracle_order'."
3770 )
3771 dialect_kw["oracle_order"] = order
3772 self._validate_dialect_kwargs(dialect_kw)
3774 @property
3775 def _increment_is_negative(self) -> bool:
3776 return self.increment is not None and self.increment < 0
3778 @property
3779 def order(self) -> Optional[bool]:
3780 """Alias of the ``dialect_kwargs`` ``'oracle_order'``.
3782 .. deprecated:: 2.1 The 'order' attribute is deprecated.
3783 """
3784 value: Optional[bool] = self.dialect_kwargs.get("oracle_order")
3785 return value
3787 def _as_dict(self) -> Dict[str, Any]:
3788 return {
3789 k: v
3790 for k, v in {
3791 "start": self.start,
3792 "increment": self.increment,
3793 "minvalue": self.minvalue,
3794 "maxvalue": self.maxvalue,
3795 "nominvalue": self.nominvalue,
3796 "nomaxvalue": self.nomaxvalue,
3797 "cycle": self.cycle,
3798 "cache": self.cache,
3799 }.items()
3800 if v != None
3801 }
3804class Sequence(HasSchemaAttr, IdentityOptions, DefaultGenerator):
3805 """Represents a named database sequence.
3807 The :class:`.Sequence` object represents the name and configurational
3808 parameters of a database sequence. It also represents
3809 a construct that can be "executed" by a SQLAlchemy :class:`_engine.Engine`
3810 or :class:`_engine.Connection`,
3811 rendering the appropriate "next value" function
3812 for the target database and returning a result.
3814 The :class:`.Sequence` is typically associated with a primary key column::
3816 some_table = Table(
3817 "some_table",
3818 metadata,
3819 Column(
3820 "id",
3821 Integer,
3822 Sequence("some_table_seq", start=1),
3823 primary_key=True,
3824 ),
3825 )
3827 When CREATE TABLE is emitted for the above :class:`_schema.Table`, if the
3828 target platform supports sequences, a CREATE SEQUENCE statement will
3829 be emitted as well. For platforms that don't support sequences,
3830 the :class:`.Sequence` construct is ignored.
3832 .. seealso::
3834 :ref:`defaults_sequences`
3836 :class:`.CreateSequence`
3838 :class:`.DropSequence`
3840 """
3842 __visit_name__ = "sequence"
3844 is_sequence = True
3846 column: Optional[Column[Any]]
3847 data_type: Optional[TypeEngine[int]]
3849 @util.deprecated_params(
3850 order=(
3851 "2.1",
3852 "This parameter is supported only by Oracle Database, "
3853 "use ``oracle_order`` instead.",
3854 )
3855 )
3856 def __init__(
3857 self,
3858 name: str,
3859 start: Optional[int] = None,
3860 increment: Optional[int] = None,
3861 minvalue: Optional[int] = None,
3862 maxvalue: Optional[int] = None,
3863 nominvalue: Optional[bool] = None,
3864 nomaxvalue: Optional[bool] = None,
3865 cycle: Optional[bool] = None,
3866 schema: Optional[Union[str, Literal[SchemaConst.BLANK_SCHEMA]]] = None,
3867 cache: Optional[int] = None,
3868 order: Optional[bool] = None,
3869 data_type: Optional[_TypeEngineArgument[int]] = None,
3870 optional: bool = False,
3871 quote: Optional[bool] = None,
3872 metadata: Optional[MetaData] = None,
3873 quote_schema: Optional[bool] = None,
3874 for_update: bool = False,
3875 **dialect_kw: Any,
3876 ) -> None:
3877 """Construct a :class:`.Sequence` object.
3879 :param name: the name of the sequence.
3881 :param start: the starting index of the sequence. This value is
3882 used when the CREATE SEQUENCE command is emitted to the database
3883 as the value of the "START WITH" clause. If ``None``, the
3884 clause is omitted, which on most platforms indicates a starting
3885 value of 1.
3887 .. versionchanged:: 2.0 The :paramref:`.Sequence.start` parameter
3888 is required in order to have DDL emit "START WITH". This is a
3889 reversal of a change made in version 1.4 which would implicitly
3890 render "START WITH 1" if the :paramref:`.Sequence.start` were
3891 not included. See :ref:`change_7211` for more detail.
3893 :param increment: the increment value of the sequence. This
3894 value is used when the CREATE SEQUENCE command is emitted to
3895 the database as the value of the "INCREMENT BY" clause. If ``None``,
3896 the clause is omitted, which on most platforms indicates an
3897 increment of 1.
3898 :param minvalue: the minimum value of the sequence. This
3899 value is used when the CREATE SEQUENCE command is emitted to
3900 the database as the value of the "MINVALUE" clause. If ``None``,
3901 the clause is omitted, which on most platforms indicates a
3902 minvalue of 1 and -2^63-1 for ascending and descending sequences,
3903 respectively.
3905 :param maxvalue: the maximum value of the sequence. This
3906 value is used when the CREATE SEQUENCE command is emitted to
3907 the database as the value of the "MAXVALUE" clause. If ``None``,
3908 the clause is omitted, which on most platforms indicates a
3909 maxvalue of 2^63-1 and -1 for ascending and descending sequences,
3910 respectively.
3912 :param nominvalue: no minimum value of the sequence. This
3913 value is used when the CREATE SEQUENCE command is emitted to
3914 the database as the value of the "NO MINVALUE" clause. If ``None``,
3915 the clause is omitted, which on most platforms indicates a
3916 minvalue of 1 and -2^63-1 for ascending and descending sequences,
3917 respectively.
3919 :param nomaxvalue: no maximum value of the sequence. This
3920 value is used when the CREATE SEQUENCE command is emitted to
3921 the database as the value of the "NO MAXVALUE" clause. If ``None``,
3922 the clause is omitted, which on most platforms indicates a
3923 maxvalue of 2^63-1 and -1 for ascending and descending sequences,
3924 respectively.
3926 :param cycle: allows the sequence to wrap around when the maxvalue
3927 or minvalue has been reached by an ascending or descending sequence
3928 respectively. This value is used when the CREATE SEQUENCE command
3929 is emitted to the database as the "CYCLE" clause. If the limit is
3930 reached, the next number generated will be the minvalue or maxvalue,
3931 respectively. If cycle=False (the default) any calls to nextval
3932 after the sequence has reached its maximum value will return an
3933 error.
3935 :param schema: optional schema name for the sequence, if located
3936 in a schema other than the default. The rules for selecting the
3937 schema name when a :class:`_schema.MetaData`
3938 is also present are the same
3939 as that of :paramref:`_schema.Table.schema`.
3941 :param cache: optional integer value; number of future values in the
3942 sequence which are calculated in advance. Renders the CACHE keyword
3943 understood by Oracle Database and PostgreSQL.
3945 :param order: optional boolean value; if ``True``, renders the
3946 ORDER keyword, understood by Oracle Database, indicating the sequence
3947 is definitively ordered. May be necessary to provide deterministic
3948 ordering using Oracle RAC.
3950 :param data_type: The type to be returned by the sequence, for
3951 dialects that allow us to choose between INTEGER, BIGINT, etc.
3952 (e.g., mssql).
3954 .. versionadded:: 1.4.0
3956 :param optional: boolean value, when ``True``, indicates that this
3957 :class:`.Sequence` object only needs to be explicitly generated
3958 on backends that don't provide another way to generate primary
3959 key identifiers. Currently, it essentially means, "don't create
3960 this sequence on the PostgreSQL backend, where the SERIAL keyword
3961 creates a sequence for us automatically".
3962 :param quote: boolean value, when ``True`` or ``False``, explicitly
3963 forces quoting of the :paramref:`_schema.Sequence.name` on or off.
3964 When left at its default of ``None``, normal quoting rules based
3965 on casing and reserved words take place.
3966 :param quote_schema: Set the quoting preferences for the ``schema``
3967 name.
3969 :param metadata: optional :class:`_schema.MetaData` object which this
3970 :class:`.Sequence` will be associated with. A :class:`.Sequence`
3971 that is associated with a :class:`_schema.MetaData`
3972 gains the following
3973 capabilities:
3975 * The :class:`.Sequence` will inherit the
3976 :paramref:`_schema.MetaData.schema`
3977 parameter specified to the target :class:`_schema.MetaData`, which
3978 affects the production of CREATE / DROP DDL, if any.
3980 * The :meth:`.Sequence.create` and :meth:`.Sequence.drop` methods
3981 automatically use the engine bound to the :class:`_schema.MetaData`
3982 object, if any.
3984 * The :meth:`_schema.MetaData.create_all` and
3985 :meth:`_schema.MetaData.drop_all`
3986 methods will emit CREATE / DROP for this :class:`.Sequence`,
3987 even if the :class:`.Sequence` is not associated with any
3988 :class:`_schema.Table` / :class:`_schema.Column`
3989 that's a member of this
3990 :class:`_schema.MetaData`.
3992 The above behaviors can only occur if the :class:`.Sequence` is
3993 explicitly associated with the :class:`_schema.MetaData`
3994 via this parameter.
3996 .. seealso::
3998 :ref:`sequence_metadata` - full discussion of the
3999 :paramref:`.Sequence.metadata` parameter.
4001 :param for_update: Indicates this :class:`.Sequence`, when associated
4002 with a :class:`_schema.Column`,
4003 should be invoked for UPDATE statements
4004 on that column's table, rather than for INSERT statements, when
4005 no value is otherwise present for that column in the statement.
4007 """
4008 DefaultGenerator.__init__(self, for_update=for_update)
4009 IdentityOptions.__init__(
4010 self,
4011 start=start,
4012 increment=increment,
4013 minvalue=minvalue,
4014 maxvalue=maxvalue,
4015 nominvalue=nominvalue,
4016 nomaxvalue=nomaxvalue,
4017 cycle=cycle,
4018 cache=cache,
4019 order=order,
4020 **dialect_kw,
4021 )
4022 self.column = None
4023 self.name = quoted_name(name, quote)
4024 self.optional = optional
4025 if schema is BLANK_SCHEMA:
4026 self.schema = schema = None
4027 elif metadata is not None and schema is None and metadata.schema:
4028 self.schema = schema = metadata.schema
4029 else:
4030 self.schema = quoted_name.construct(schema, quote_schema)
4031 self.metadata = metadata
4032 self._key = _get_table_key(name, schema)
4033 if metadata:
4034 self._set_metadata(metadata)
4035 if data_type is not None:
4036 self.data_type = to_instance(data_type)
4037 else:
4038 self.data_type = None
4040 @util.preload_module("sqlalchemy.sql.functions")
4041 def next_value(self) -> Function[int]:
4042 """Return a :class:`.next_value` function element
4043 which will render the appropriate increment function
4044 for this :class:`.Sequence` within any SQL expression.
4046 """
4047 return util.preloaded.sql_functions.func.next_value(self)
4049 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4050 column = parent
4051 assert isinstance(column, Column)
4052 super()._set_parent(column)
4053 column._on_table_attach(self._set_table)
4055 def _copy(self) -> Sequence:
4056 return Sequence(
4057 name=self.name,
4058 schema=self.schema,
4059 data_type=self.data_type,
4060 optional=self.optional,
4061 metadata=self.metadata,
4062 for_update=self.for_update,
4063 **self._as_dict(),
4064 **self.dialect_kwargs,
4065 )
4067 def _set_table(self, column: Column[Any], table: Table) -> None:
4068 self._set_metadata(table.metadata)
4070 def _set_metadata(self, metadata: MetaData) -> None:
4071 self.metadata = metadata
4072 self.metadata._sequences[self._key] = self
4074 def create(self, bind: _CreateDropBind, checkfirst: bool = True) -> None:
4075 """Creates this sequence in the database."""
4077 bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
4079 def drop(self, bind: _CreateDropBind, checkfirst: bool = True) -> None:
4080 """Drops this sequence from the database."""
4082 bind._run_ddl_visitor(ddl.SchemaDropper, self, checkfirst=checkfirst)
4084 def _not_a_column_expr(self) -> NoReturn:
4085 raise exc.InvalidRequestError(
4086 f"This {self.__class__.__name__} cannot be used directly "
4087 "as a column expression. Use func.next_value(sequence) "
4088 "to produce a 'next value' function that's usable "
4089 "as a column element."
4090 )
4093@inspection._self_inspects
4094class FetchedValue(SchemaEventTarget):
4095 """A marker for a transparent database-side default.
4097 Use :class:`.FetchedValue` when the database is configured
4098 to provide some automatic default for a column.
4100 E.g.::
4102 Column("foo", Integer, FetchedValue())
4104 Would indicate that some trigger or default generator
4105 will create a new value for the ``foo`` column during an
4106 INSERT.
4108 .. seealso::
4110 :ref:`triggered_columns`
4112 """
4114 is_server_default = True
4115 reflected = False
4116 has_argument = False
4117 is_clause_element = False
4118 is_identity = False
4120 column: Optional[Column[Any]]
4122 def __init__(self, for_update: bool = False) -> None:
4123 self.for_update = for_update
4125 def _as_for_update(self, for_update: bool) -> FetchedValue:
4126 if for_update == self.for_update:
4127 return self
4128 else:
4129 return self._clone(for_update)
4131 def _copy(self) -> FetchedValue:
4132 return FetchedValue(self.for_update)
4134 def _clone(self, for_update: bool) -> Self:
4135 n = self.__class__.__new__(self.__class__)
4136 n.__dict__.update(self.__dict__)
4137 n.__dict__.pop("column", None)
4138 n.for_update = for_update
4139 return n
4141 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4142 column = parent
4143 assert isinstance(column, Column)
4144 self.column = column
4145 if self.for_update:
4146 self.column.server_onupdate = self
4147 else:
4148 self.column.server_default = self
4150 def __repr__(self) -> str:
4151 return util.generic_repr(self)
4154class DefaultClause(FetchedValue):
4155 """A DDL-specified DEFAULT column value.
4157 :class:`.DefaultClause` is a :class:`.FetchedValue`
4158 that also generates a "DEFAULT" clause when
4159 "CREATE TABLE" is emitted.
4161 :class:`.DefaultClause` is generated automatically
4162 whenever the ``server_default``, ``server_onupdate`` arguments of
4163 :class:`_schema.Column` are used. A :class:`.DefaultClause`
4164 can be passed positionally as well.
4166 For example, the following::
4168 Column("foo", Integer, server_default="50")
4170 Is equivalent to::
4172 Column("foo", Integer, DefaultClause("50"))
4174 """
4176 has_argument = True
4178 def __init__(
4179 self,
4180 arg: Union[str, ClauseElement, TextClause],
4181 for_update: bool = False,
4182 _reflected: bool = False,
4183 ) -> None:
4184 util.assert_arg_type(arg, (str, ClauseElement, TextClause), "arg")
4185 super().__init__(for_update)
4186 self.arg = arg
4187 self.reflected = _reflected
4189 def _copy(self) -> DefaultClause:
4190 return DefaultClause(
4191 arg=self.arg, for_update=self.for_update, _reflected=self.reflected
4192 )
4194 def __repr__(self) -> str:
4195 return "DefaultClause(%r, for_update=%r)" % (self.arg, self.for_update)
4198class Constraint(DialectKWArgs, HasConditionalDDL, SchemaItem):
4199 """A table-level SQL constraint.
4201 :class:`_schema.Constraint` serves as the base class for the series of
4202 constraint objects that can be associated with :class:`_schema.Table`
4203 objects, including :class:`_schema.PrimaryKeyConstraint`,
4204 :class:`_schema.ForeignKeyConstraint`
4205 :class:`_schema.UniqueConstraint`, and
4206 :class:`_schema.CheckConstraint`.
4208 """
4210 __visit_name__ = "constraint"
4212 _creation_order: int
4213 _column_flag: bool
4215 def __init__(
4216 self,
4217 name: _ConstraintNameArgument = None,
4218 deferrable: Optional[bool] = None,
4219 initially: Optional[str] = None,
4220 info: Optional[_InfoType] = None,
4221 comment: Optional[str] = None,
4222 _create_rule: Optional[Any] = None,
4223 _type_bound: bool = False,
4224 **dialect_kw: Any,
4225 ) -> None:
4226 r"""Create a SQL constraint.
4228 :param name:
4229 Optional, the in-database name of this ``Constraint``.
4231 :param deferrable:
4232 Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
4233 issuing DDL for this constraint.
4235 :param initially:
4236 Optional string. If set, emit INITIALLY <value> when issuing DDL
4237 for this constraint.
4239 :param info: Optional data dictionary which will be populated into the
4240 :attr:`.SchemaItem.info` attribute of this object.
4242 :param comment: Optional string that will render an SQL comment on
4243 foreign key constraint creation.
4245 .. versionadded:: 2.0
4247 :param \**dialect_kw: Additional keyword arguments are dialect
4248 specific, and passed in the form ``<dialectname>_<argname>``. See
4249 the documentation regarding an individual dialect at
4250 :ref:`dialect_toplevel` for detail on documented arguments.
4252 :param _create_rule:
4253 used internally by some datatypes that also create constraints.
4255 :param _type_bound:
4256 used internally to indicate that this constraint is associated with
4257 a specific datatype.
4259 """
4261 self.name = name
4262 self.deferrable = deferrable
4263 self.initially = initially
4264 if info:
4265 self.info = info
4266 self._create_rule = _create_rule
4267 self._type_bound = _type_bound
4268 util.set_creation_order(self)
4269 self._validate_dialect_kwargs(dialect_kw)
4270 self.comment = comment
4272 def _should_create_for_compiler(
4273 self, compiler: DDLCompiler, **kw: Any
4274 ) -> bool:
4275 if self._create_rule is not None and not self._create_rule(compiler):
4276 return False
4277 elif self._ddl_if is not None:
4278 return self._ddl_if._should_execute(
4279 ddl.CreateConstraint(self), self, None, compiler=compiler, **kw
4280 )
4281 else:
4282 return True
4284 @property
4285 def table(self) -> Table:
4286 try:
4287 if isinstance(self.parent, Table):
4288 return self.parent
4289 except AttributeError:
4290 pass
4291 raise exc.InvalidRequestError(
4292 "This constraint is not bound to a table. Did you "
4293 "mean to call table.append_constraint(constraint) ?"
4294 )
4296 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4297 assert isinstance(parent, (Table, Column))
4298 self.parent = parent
4299 parent.constraints.add(self)
4301 @util.deprecated(
4302 "1.4",
4303 "The :meth:`_schema.Constraint.copy` method is deprecated "
4304 "and will be removed in a future release.",
4305 )
4306 def copy(self, **kw: Any) -> Self:
4307 return self._copy(**kw)
4309 def _copy(self, **kw: Any) -> Self:
4310 raise NotImplementedError()
4313class ColumnCollectionMixin:
4314 """A :class:`_expression.ColumnCollection` of :class:`_schema.Column`
4315 objects.
4317 This collection represents the columns which are referred to by
4318 this object.
4320 """
4322 _columns: DedupeColumnCollection[Column[Any]]
4324 _allow_multiple_tables = False
4326 _pending_colargs: List[Optional[Union[str, Column[Any]]]]
4328 if TYPE_CHECKING:
4330 def _set_parent_with_dispatch(
4331 self, parent: SchemaEventTarget, **kw: Any
4332 ) -> None: ...
4334 def __init__(
4335 self,
4336 *columns: _DDLColumnArgument,
4337 _autoattach: bool = True,
4338 _column_flag: bool = False,
4339 _gather_expressions: Optional[
4340 List[Union[str, ColumnElement[Any]]]
4341 ] = None,
4342 ) -> None:
4343 self._column_flag = _column_flag
4344 self._columns = DedupeColumnCollection()
4346 processed_expressions: Optional[
4347 List[Union[ColumnElement[Any], str]]
4348 ] = _gather_expressions
4350 if processed_expressions is not None:
4352 # this is expected to be an empty list
4353 assert not processed_expressions
4355 self._pending_colargs = []
4356 for (
4357 expr,
4358 _,
4359 _,
4360 add_element,
4361 ) in coercions.expect_col_expression_collection(
4362 roles.DDLConstraintColumnRole, columns
4363 ):
4364 self._pending_colargs.append(add_element)
4365 processed_expressions.append(expr)
4366 else:
4367 self._pending_colargs = [
4368 coercions.expect(roles.DDLConstraintColumnRole, column)
4369 for column in columns
4370 ]
4372 if _autoattach and self._pending_colargs:
4373 self._check_attach()
4375 def _check_attach(self, evt: bool = False) -> None:
4376 col_objs = [c for c in self._pending_colargs if isinstance(c, Column)]
4378 cols_w_table = [c for c in col_objs if isinstance(c.table, Table)]
4380 cols_wo_table = set(col_objs).difference(cols_w_table)
4381 if cols_wo_table:
4382 # feature #3341 - place event listeners for Column objects
4383 # such that when all those cols are attached, we autoattach.
4384 assert not evt, "Should not reach here on event call"
4386 # issue #3411 - don't do the per-column auto-attach if some of the
4387 # columns are specified as strings.
4388 has_string_cols = {
4389 c for c in self._pending_colargs if c is not None
4390 }.difference(col_objs)
4391 if not has_string_cols:
4393 def _col_attached(column: Column[Any], table: Table) -> None:
4394 # this isinstance() corresponds with the
4395 # isinstance() above; only want to count Table-bound
4396 # columns
4397 if isinstance(table, Table):
4398 cols_wo_table.discard(column)
4399 if not cols_wo_table:
4400 self._check_attach(evt=True)
4402 self._cols_wo_table = cols_wo_table
4403 for col in cols_wo_table:
4404 col._on_table_attach(_col_attached)
4405 return
4407 columns = cols_w_table
4409 tables = {c.table for c in columns}
4410 if len(tables) == 1:
4411 self._set_parent_with_dispatch(tables.pop())
4412 elif len(tables) > 1 and not self._allow_multiple_tables:
4413 table = columns[0].table
4414 others = [c for c in columns[1:] if c.table is not table]
4415 if others:
4416 # black could not format this inline
4417 other_str = ", ".join("'%s'" % c for c in others)
4418 raise exc.ArgumentError(
4419 f"Column(s) {other_str} "
4420 f"are not part of table '{table.description}'."
4421 )
4423 @util.ro_memoized_property
4424 def columns(self) -> ReadOnlyColumnCollection[str, Column[Any]]:
4425 return self._columns.as_readonly()
4427 @util.ro_memoized_property
4428 def c(self) -> ReadOnlyColumnCollection[str, Column[Any]]:
4429 return self._columns.as_readonly()
4431 def _col_expressions(
4432 self, parent: Union[Table, Column[Any]]
4433 ) -> List[Optional[Column[Any]]]:
4434 if isinstance(parent, Column):
4435 result: List[Optional[Column[Any]]] = [
4436 c for c in self._pending_colargs if isinstance(c, Column)
4437 ]
4438 assert len(result) == len(self._pending_colargs)
4439 return result
4440 else:
4441 try:
4442 return [
4443 parent.c[col] if isinstance(col, str) else col
4444 for col in self._pending_colargs
4445 ]
4446 except KeyError as ke:
4447 raise exc.ConstraintColumnNotFoundError(
4448 f"Can't create {self.__class__.__name__} "
4449 f"on table '{parent.description}': no column "
4450 f"named '{ke.args[0]}' is present."
4451 ) from ke
4453 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4454 assert isinstance(parent, (Table, Column))
4456 for col in self._col_expressions(parent):
4457 if col is not None:
4458 self._columns.add(col)
4461class ColumnCollectionConstraint(ColumnCollectionMixin, Constraint):
4462 """A constraint that proxies a ColumnCollection."""
4464 def __init__(
4465 self,
4466 *columns: _DDLColumnArgument,
4467 name: _ConstraintNameArgument = None,
4468 deferrable: Optional[bool] = None,
4469 initially: Optional[str] = None,
4470 info: Optional[_InfoType] = None,
4471 _autoattach: bool = True,
4472 _column_flag: bool = False,
4473 _gather_expressions: Optional[List[_DDLColumnArgument]] = None,
4474 **dialect_kw: Any,
4475 ) -> None:
4476 r"""
4477 :param \*columns:
4478 A sequence of column names or Column objects.
4480 :param name:
4481 Optional, the in-database name of this constraint.
4483 :param deferrable:
4484 Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
4485 issuing DDL for this constraint.
4487 :param initially:
4488 Optional string. If set, emit INITIALLY <value> when issuing DDL
4489 for this constraint.
4491 :param \**dialect_kw: other keyword arguments including
4492 dialect-specific arguments are propagated to the :class:`.Constraint`
4493 superclass.
4495 """
4496 Constraint.__init__(
4497 self,
4498 name=name,
4499 deferrable=deferrable,
4500 initially=initially,
4501 info=info,
4502 **dialect_kw,
4503 )
4504 ColumnCollectionMixin.__init__(
4505 self, *columns, _autoattach=_autoattach, _column_flag=_column_flag
4506 )
4508 columns: ReadOnlyColumnCollection[str, Column[Any]]
4509 """A :class:`_expression.ColumnCollection` representing the set of columns
4510 for this constraint.
4512 """
4514 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4515 assert isinstance(parent, (Column, Table))
4516 Constraint._set_parent(self, parent)
4517 ColumnCollectionMixin._set_parent(self, parent)
4519 def __contains__(self, x: Any) -> bool:
4520 return x in self._columns
4522 @util.deprecated(
4523 "1.4",
4524 "The :meth:`_schema.ColumnCollectionConstraint.copy` method "
4525 "is deprecated and will be removed in a future release.",
4526 )
4527 def copy(
4528 self,
4529 *,
4530 target_table: Optional[Table] = None,
4531 **kw: Any,
4532 ) -> ColumnCollectionConstraint:
4533 return self._copy(target_table=target_table, **kw)
4535 def _copy(
4536 self,
4537 *,
4538 target_table: Optional[Table] = None,
4539 **kw: Any,
4540 ) -> ColumnCollectionConstraint:
4541 # ticket #5276
4542 constraint_kwargs = {}
4543 for dialect_name in self.dialect_options:
4544 dialect_options = self.dialect_options[dialect_name]._non_defaults
4545 for (
4546 dialect_option_key,
4547 dialect_option_value,
4548 ) in dialect_options.items():
4549 constraint_kwargs[dialect_name + "_" + dialect_option_key] = (
4550 dialect_option_value
4551 )
4553 assert isinstance(self.parent, Table)
4554 c = self.__class__(
4555 name=self.name,
4556 deferrable=self.deferrable,
4557 initially=self.initially,
4558 *[
4559 _copy_expression(expr, self.parent, target_table)
4560 for expr in self._columns
4561 ],
4562 comment=self.comment,
4563 **constraint_kwargs,
4564 )
4565 return self._schema_item_copy(c)
4567 def contains_column(self, col: Column[Any]) -> bool:
4568 """Return True if this constraint contains the given column.
4570 Note that this object also contains an attribute ``.columns``
4571 which is a :class:`_expression.ColumnCollection` of
4572 :class:`_schema.Column` objects.
4574 """
4576 return self._columns.contains_column(col)
4578 def __iter__(self) -> Iterator[Column[Any]]:
4579 return iter(self._columns)
4581 def __len__(self) -> int:
4582 return len(self._columns)
4585class CheckConstraint(ColumnCollectionConstraint):
4586 """A table- or column-level CHECK constraint.
4588 Can be included in the definition of a Table or Column.
4589 """
4591 _allow_multiple_tables = True
4593 __visit_name__ = "table_or_column_check_constraint"
4595 @_document_text_coercion(
4596 "sqltext",
4597 ":class:`.CheckConstraint`",
4598 ":paramref:`.CheckConstraint.sqltext`",
4599 )
4600 def __init__(
4601 self,
4602 sqltext: _TextCoercedExpressionArgument[Any],
4603 name: _ConstraintNameArgument = None,
4604 deferrable: Optional[bool] = None,
4605 initially: Optional[str] = None,
4606 table: Optional[Table] = None,
4607 info: Optional[_InfoType] = None,
4608 _create_rule: Optional[Any] = None,
4609 _autoattach: bool = True,
4610 _type_bound: bool = False,
4611 **dialect_kw: Any,
4612 ) -> None:
4613 r"""Construct a CHECK constraint.
4615 :param sqltext:
4616 A string containing the constraint definition, which will be used
4617 verbatim, or a SQL expression construct. If given as a string,
4618 the object is converted to a :func:`_expression.text` object.
4619 If the textual
4620 string includes a colon character, escape this using a backslash::
4622 CheckConstraint(r"foo ~ E'a(?\:b|c)d")
4624 :param name:
4625 Optional, the in-database name of the constraint.
4627 :param deferrable:
4628 Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
4629 issuing DDL for this constraint.
4631 :param initially:
4632 Optional string. If set, emit INITIALLY <value> when issuing DDL
4633 for this constraint.
4635 :param info: Optional data dictionary which will be populated into the
4636 :attr:`.SchemaItem.info` attribute of this object.
4638 """
4640 self.sqltext = coercions.expect(roles.DDLExpressionRole, sqltext)
4641 columns: List[Column[Any]] = []
4642 visitors.traverse(self.sqltext, {}, {"column": columns.append})
4644 super().__init__(
4645 name=name,
4646 deferrable=deferrable,
4647 initially=initially,
4648 _create_rule=_create_rule,
4649 info=info,
4650 _type_bound=_type_bound,
4651 _autoattach=_autoattach,
4652 *columns,
4653 **dialect_kw,
4654 )
4655 if table is not None:
4656 self._set_parent_with_dispatch(table)
4658 @property
4659 def is_column_level(self) -> bool:
4660 return not isinstance(self.parent, Table)
4662 @util.deprecated(
4663 "1.4",
4664 "The :meth:`_schema.CheckConstraint.copy` method is deprecated "
4665 "and will be removed in a future release.",
4666 )
4667 def copy(
4668 self, *, target_table: Optional[Table] = None, **kw: Any
4669 ) -> CheckConstraint:
4670 return self._copy(target_table=target_table, **kw)
4672 def _copy(
4673 self, *, target_table: Optional[Table] = None, **kw: Any
4674 ) -> CheckConstraint:
4675 if target_table is not None:
4676 # note that target_table is None for the copy process of
4677 # a column-bound CheckConstraint, so this path is not reached
4678 # in that case.
4679 sqltext = _copy_expression(self.sqltext, self.table, target_table)
4680 else:
4681 sqltext = self.sqltext
4682 c = CheckConstraint(
4683 sqltext,
4684 name=self.name,
4685 initially=self.initially,
4686 deferrable=self.deferrable,
4687 _create_rule=self._create_rule,
4688 table=target_table,
4689 comment=self.comment,
4690 _autoattach=False,
4691 _type_bound=self._type_bound,
4692 )
4693 return self._schema_item_copy(c)
4696class ForeignKeyConstraint(ColumnCollectionConstraint):
4697 """A table-level FOREIGN KEY constraint.
4699 Defines a single column or composite FOREIGN KEY ... REFERENCES
4700 constraint. For a no-frills, single column foreign key, adding a
4701 :class:`_schema.ForeignKey` to the definition of a :class:`_schema.Column`
4702 is a
4703 shorthand equivalent for an unnamed, single column
4704 :class:`_schema.ForeignKeyConstraint`.
4706 Examples of foreign key configuration are in :ref:`metadata_foreignkeys`.
4708 """
4710 __visit_name__ = "foreign_key_constraint"
4712 def __init__(
4713 self,
4714 columns: _typing_Sequence[_DDLColumnArgument],
4715 refcolumns: _typing_Sequence[_DDLColumnReferenceArgument],
4716 name: _ConstraintNameArgument = None,
4717 onupdate: Optional[str] = None,
4718 ondelete: Optional[str] = None,
4719 deferrable: Optional[bool] = None,
4720 initially: Optional[str] = None,
4721 use_alter: bool = False,
4722 link_to_name: bool = False,
4723 match: Optional[str] = None,
4724 table: Optional[Table] = None,
4725 info: Optional[_InfoType] = None,
4726 comment: Optional[str] = None,
4727 **dialect_kw: Any,
4728 ) -> None:
4729 r"""Construct a composite-capable FOREIGN KEY.
4731 :param columns: A sequence of local column names. The named columns
4732 must be defined and present in the parent Table. The names should
4733 match the ``key`` given to each column (defaults to the name) unless
4734 ``link_to_name`` is True.
4736 :param refcolumns: A sequence of foreign column names or Column
4737 objects. The columns must all be located within the same Table.
4739 :param name: Optional, the in-database name of the key.
4741 :param onupdate: Optional string. If set, emit ON UPDATE <value> when
4742 issuing DDL for this constraint. Typical values include CASCADE,
4743 DELETE and RESTRICT.
4745 .. seealso::
4747 :ref:`on_update_on_delete`
4749 :param ondelete: Optional string. If set, emit ON DELETE <value> when
4750 issuing DDL for this constraint. Typical values include CASCADE,
4751 SET NULL and RESTRICT. Some dialects may allow for additional
4752 syntaxes.
4754 .. seealso::
4756 :ref:`on_update_on_delete`
4758 :param deferrable: Optional bool. If set, emit DEFERRABLE or NOT
4759 DEFERRABLE when issuing DDL for this constraint.
4761 :param initially: Optional string. If set, emit INITIALLY <value> when
4762 issuing DDL for this constraint.
4764 :param link_to_name: if True, the string name given in ``column`` is
4765 the rendered name of the referenced column, not its locally assigned
4766 ``key``.
4768 :param use_alter: If True, do not emit the DDL for this constraint as
4769 part of the CREATE TABLE definition. Instead, generate it via an
4770 ALTER TABLE statement issued after the full collection of tables
4771 have been created, and drop it via an ALTER TABLE statement before
4772 the full collection of tables are dropped.
4774 The use of :paramref:`_schema.ForeignKeyConstraint.use_alter` is
4775 particularly geared towards the case where two or more tables
4776 are established within a mutually-dependent foreign key constraint
4777 relationship; however, the :meth:`_schema.MetaData.create_all` and
4778 :meth:`_schema.MetaData.drop_all`
4779 methods will perform this resolution
4780 automatically, so the flag is normally not needed.
4782 .. seealso::
4784 :ref:`use_alter`
4786 :param match: Optional string. If set, emit MATCH <value> when issuing
4787 DDL for this constraint. Typical values include SIMPLE, PARTIAL
4788 and FULL.
4790 :param info: Optional data dictionary which will be populated into the
4791 :attr:`.SchemaItem.info` attribute of this object.
4793 :param comment: Optional string that will render an SQL comment on
4794 foreign key constraint creation.
4796 .. versionadded:: 2.0
4798 :param \**dialect_kw: Additional keyword arguments are dialect
4799 specific, and passed in the form ``<dialectname>_<argname>``. See
4800 the documentation regarding an individual dialect at
4801 :ref:`dialect_toplevel` for detail on documented arguments.
4803 """
4805 Constraint.__init__(
4806 self,
4807 name=name,
4808 deferrable=deferrable,
4809 initially=initially,
4810 info=info,
4811 comment=comment,
4812 **dialect_kw,
4813 )
4814 self.onupdate = onupdate
4815 self.ondelete = ondelete
4816 self.link_to_name = link_to_name
4817 self.use_alter = use_alter
4818 self.match = match
4820 if len(set(columns)) != len(refcolumns):
4821 if len(set(columns)) != len(columns):
4822 # e.g. FOREIGN KEY (a, a) REFERENCES r (b, c)
4823 raise exc.ArgumentError(
4824 "ForeignKeyConstraint with duplicate source column "
4825 "references are not supported."
4826 )
4827 else:
4828 # e.g. FOREIGN KEY (a) REFERENCES r (b, c)
4829 # paraphrasing
4830 # https://www.postgresql.org/docs/current/static/ddl-constraints.html
4831 raise exc.ArgumentError(
4832 "ForeignKeyConstraint number "
4833 "of constrained columns must match the number of "
4834 "referenced columns."
4835 )
4837 # standalone ForeignKeyConstraint - create
4838 # associated ForeignKey objects which will be applied to hosted
4839 # Column objects (in col.foreign_keys), either now or when attached
4840 # to the Table for string-specified names
4841 self.elements = [
4842 ForeignKey(
4843 refcol,
4844 _constraint=self,
4845 name=self.name,
4846 onupdate=self.onupdate,
4847 ondelete=self.ondelete,
4848 use_alter=self.use_alter,
4849 link_to_name=self.link_to_name,
4850 match=self.match,
4851 deferrable=self.deferrable,
4852 initially=self.initially,
4853 **self.dialect_kwargs,
4854 )
4855 for refcol in refcolumns
4856 ]
4858 ColumnCollectionMixin.__init__(self, *columns)
4859 if table is not None:
4860 if hasattr(self, "parent"):
4861 assert table is self.parent
4862 self._set_parent_with_dispatch(table)
4864 def _append_element(self, column: Column[Any], fk: ForeignKey) -> None:
4865 self._columns.add(column)
4866 self.elements.append(fk)
4868 columns: ReadOnlyColumnCollection[str, Column[Any]]
4869 """A :class:`_expression.ColumnCollection` representing the set of columns
4870 for this constraint.
4872 """
4874 elements: List[ForeignKey]
4875 """A sequence of :class:`_schema.ForeignKey` objects.
4877 Each :class:`_schema.ForeignKey`
4878 represents a single referring column/referred
4879 column pair.
4881 This collection is intended to be read-only.
4883 """
4885 @property
4886 def _elements(self) -> util.OrderedDict[str, ForeignKey]:
4887 # legacy - provide a dictionary view of (column_key, fk)
4888 return util.OrderedDict(zip(self.column_keys, self.elements))
4890 @property
4891 def _referred_schema(self) -> Optional[str]:
4892 for elem in self.elements:
4893 return elem._referred_schema
4894 else:
4895 return None
4897 @property
4898 def referred_table(self) -> Table:
4899 """The :class:`_schema.Table` object to which this
4900 :class:`_schema.ForeignKeyConstraint` references.
4902 This is a dynamically calculated attribute which may not be available
4903 if the constraint and/or parent table is not yet associated with
4904 a metadata collection that contains the referred table.
4906 """
4907 return self.elements[0].column.table
4909 def _validate_dest_table(self, table: Table) -> None:
4910 table_keys = {
4911 elem._table_key_within_construction() for elem in self.elements
4912 }
4913 if None not in table_keys and len(table_keys) > 1:
4914 elem0, elem1 = sorted(table_keys)[0:2]
4915 raise exc.ArgumentError(
4916 f"ForeignKeyConstraint on "
4917 f"{table.fullname}({self._col_description}) refers to "
4918 f"multiple remote tables: {elem0} and {elem1}"
4919 )
4921 @property
4922 def column_keys(self) -> _typing_Sequence[str]:
4923 """Return a list of string keys representing the local
4924 columns in this :class:`_schema.ForeignKeyConstraint`.
4926 This list is either the original string arguments sent
4927 to the constructor of the :class:`_schema.ForeignKeyConstraint`,
4928 or if the constraint has been initialized with :class:`_schema.Column`
4929 objects, is the string ``.key`` of each element.
4931 """
4932 if hasattr(self, "parent"):
4933 return self._columns.keys()
4934 else:
4935 return [
4936 col.key if isinstance(col, ColumnElement) else str(col)
4937 for col in self._pending_colargs
4938 ]
4940 @property
4941 def _col_description(self) -> str:
4942 return ", ".join(self.column_keys)
4944 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4945 table = parent
4946 assert isinstance(table, Table)
4947 Constraint._set_parent(self, table)
4949 ColumnCollectionConstraint._set_parent(self, table)
4951 for col, fk in zip(self._columns, self.elements):
4952 if not hasattr(fk, "parent") or fk.parent is not col:
4953 fk._set_parent_with_dispatch(col)
4955 self._validate_dest_table(table)
4957 @util.deprecated(
4958 "1.4",
4959 "The :meth:`_schema.ForeignKeyConstraint.copy` method is deprecated "
4960 "and will be removed in a future release.",
4961 )
4962 def copy(
4963 self,
4964 *,
4965 schema: Optional[str] = None,
4966 target_table: Optional[Table] = None,
4967 **kw: Any,
4968 ) -> ForeignKeyConstraint:
4969 return self._copy(schema=schema, target_table=target_table, **kw)
4971 def _copy(
4972 self,
4973 *,
4974 schema: Optional[str] = None,
4975 target_table: Optional[Table] = None,
4976 **kw: Any,
4977 ) -> ForeignKeyConstraint:
4978 fkc = ForeignKeyConstraint(
4979 [x.parent.key for x in self.elements],
4980 [
4981 x._get_colspec(
4982 schema=schema,
4983 table_name=(
4984 target_table.name
4985 if target_table is not None
4986 and x._table_key_within_construction()
4987 == x.parent.table.key
4988 else None
4989 ),
4990 _is_copy=True,
4991 )
4992 for x in self.elements
4993 ],
4994 name=self.name,
4995 onupdate=self.onupdate,
4996 ondelete=self.ondelete,
4997 use_alter=self.use_alter,
4998 deferrable=self.deferrable,
4999 initially=self.initially,
5000 link_to_name=self.link_to_name,
5001 match=self.match,
5002 comment=self.comment,
5003 )
5004 for self_fk, other_fk in zip(self.elements, fkc.elements):
5005 self_fk._schema_item_copy(other_fk)
5006 return self._schema_item_copy(fkc)
5009class PrimaryKeyConstraint(ColumnCollectionConstraint):
5010 """A table-level PRIMARY KEY constraint.
5012 The :class:`.PrimaryKeyConstraint` object is present automatically
5013 on any :class:`_schema.Table` object; it is assigned a set of
5014 :class:`_schema.Column` objects corresponding to those marked with
5015 the :paramref:`_schema.Column.primary_key` flag::
5017 >>> my_table = Table(
5018 ... "mytable",
5019 ... metadata,
5020 ... Column("id", Integer, primary_key=True),
5021 ... Column("version_id", Integer, primary_key=True),
5022 ... Column("data", String(50)),
5023 ... )
5024 >>> my_table.primary_key
5025 PrimaryKeyConstraint(
5026 Column('id', Integer(), table=<mytable>,
5027 primary_key=True, nullable=False),
5028 Column('version_id', Integer(), table=<mytable>,
5029 primary_key=True, nullable=False)
5030 )
5032 The primary key of a :class:`_schema.Table` can also be specified by using
5033 a :class:`.PrimaryKeyConstraint` object explicitly; in this mode of usage,
5034 the "name" of the constraint can also be specified, as well as other
5035 options which may be recognized by dialects::
5037 my_table = Table(
5038 "mytable",
5039 metadata,
5040 Column("id", Integer),
5041 Column("version_id", Integer),
5042 Column("data", String(50)),
5043 PrimaryKeyConstraint("id", "version_id", name="mytable_pk"),
5044 )
5046 The two styles of column-specification should generally not be mixed.
5047 An warning is emitted if the columns present in the
5048 :class:`.PrimaryKeyConstraint`
5049 don't match the columns that were marked as ``primary_key=True``, if both
5050 are present; in this case, the columns are taken strictly from the
5051 :class:`.PrimaryKeyConstraint` declaration, and those columns otherwise
5052 marked as ``primary_key=True`` are ignored. This behavior is intended to
5053 be backwards compatible with previous behavior.
5055 For the use case where specific options are to be specified on the
5056 :class:`.PrimaryKeyConstraint`, but the usual style of using
5057 ``primary_key=True`` flags is still desirable, an empty
5058 :class:`.PrimaryKeyConstraint` may be specified, which will take on the
5059 primary key column collection from the :class:`_schema.Table` based on the
5060 flags::
5062 my_table = Table(
5063 "mytable",
5064 metadata,
5065 Column("id", Integer, primary_key=True),
5066 Column("version_id", Integer, primary_key=True),
5067 Column("data", String(50)),
5068 PrimaryKeyConstraint(name="mytable_pk", mssql_clustered=True),
5069 )
5071 """
5073 __visit_name__ = "primary_key_constraint"
5075 def __init__(
5076 self,
5077 *columns: _DDLColumnArgument,
5078 name: Optional[str] = None,
5079 deferrable: Optional[bool] = None,
5080 initially: Optional[str] = None,
5081 info: Optional[_InfoType] = None,
5082 _implicit_generated: bool = False,
5083 **dialect_kw: Any,
5084 ) -> None:
5085 self._implicit_generated = _implicit_generated
5086 super().__init__(
5087 *columns,
5088 name=name,
5089 deferrable=deferrable,
5090 initially=initially,
5091 info=info,
5092 **dialect_kw,
5093 )
5095 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
5096 table = parent
5097 assert isinstance(table, Table)
5098 super()._set_parent(table)
5100 if table.primary_key is not self:
5101 table.constraints.discard(table.primary_key)
5102 table.primary_key = self # type: ignore
5103 table.constraints.add(self)
5105 table_pks = [c for c in table.c if c.primary_key]
5106 if (
5107 self._columns
5108 and table_pks
5109 and set(table_pks) != set(self._columns)
5110 ):
5111 # black could not format these inline
5112 table_pk_str = ", ".join("'%s'" % c.name for c in table_pks)
5113 col_str = ", ".join("'%s'" % c.name for c in self._columns)
5115 util.warn(
5116 f"Table '{table.name}' specifies columns "
5117 f"{table_pk_str} as "
5118 f"primary_key=True, "
5119 f"not matching locally specified columns {col_str}; "
5120 f"setting the "
5121 f"current primary key columns to "
5122 f"{col_str}. "
5123 f"This warning "
5124 f"may become an exception in a future release"
5125 )
5126 table_pks[:] = []
5128 for c in self._columns:
5129 c.primary_key = True
5130 if c._user_defined_nullable is NULL_UNSPECIFIED:
5131 c.nullable = False
5132 if table_pks:
5133 self._columns.extend(table_pks)
5135 def _reload(self, columns: Iterable[Column[Any]]) -> None:
5136 """repopulate this :class:`.PrimaryKeyConstraint` given
5137 a set of columns.
5139 Existing columns in the table that are marked as primary_key=True
5140 are maintained.
5142 Also fires a new event.
5144 This is basically like putting a whole new
5145 :class:`.PrimaryKeyConstraint` object on the parent
5146 :class:`_schema.Table` object without actually replacing the object.
5148 The ordering of the given list of columns is also maintained; these
5149 columns will be appended to the list of columns after any which
5150 are already present.
5152 """
5153 # set the primary key flag on new columns.
5154 # note any existing PK cols on the table also have their
5155 # flag still set.
5156 for col in columns:
5157 col.primary_key = True
5159 self._columns.extend(columns)
5161 PrimaryKeyConstraint._autoincrement_column._reset(self) # type: ignore
5162 self._set_parent_with_dispatch(self.table)
5164 def _replace(self, col: Column[Any]) -> None:
5165 PrimaryKeyConstraint._autoincrement_column._reset(self) # type: ignore
5166 self._columns.replace(col)
5168 self.dispatch._sa_event_column_added_to_pk_constraint(self, col)
5170 @property
5171 def columns_autoinc_first(self) -> List[Column[Any]]:
5172 autoinc = self._autoincrement_column
5174 if autoinc is not None:
5175 return [autoinc] + [c for c in self._columns if c is not autoinc]
5176 else:
5177 return list(self._columns)
5179 @util.ro_memoized_property
5180 def _autoincrement_column(self) -> Optional[Column[int]]:
5181 def _validate_autoinc(col: Column[Any], autoinc_true: bool) -> bool:
5182 if col.type._type_affinity is not None and issubclass(
5183 col.type._type_affinity, type_api.NUMERICTYPE._type_affinity
5184 ):
5185 scale = col.type.scale # type: ignore[attr-defined]
5186 if scale != 0 and autoinc_true:
5187 raise exc.ArgumentError(
5188 f"Column type {col.type} with non-zero scale "
5189 f"{scale} on column '{col}' is not "
5190 f"compatible with autoincrement=True"
5191 )
5192 elif not autoinc_true:
5193 return False
5194 elif col.type._type_affinity is None or not issubclass(
5195 col.type._type_affinity, type_api.INTEGERTYPE._type_affinity
5196 ):
5197 if autoinc_true:
5198 raise exc.ArgumentError(
5199 f"Column type {col.type} on column '{col}' is not "
5200 f"compatible with autoincrement=True"
5201 )
5202 else:
5203 return False
5204 elif (
5205 col.default is not None
5206 and not isinstance(col.default, Sequence)
5207 and not autoinc_true
5208 ):
5209 return False
5210 elif (
5211 col.server_default is not None
5212 and not isinstance(col.server_default, Identity)
5213 and not autoinc_true
5214 ):
5215 return False
5216 elif col.foreign_keys and col.autoincrement not in (
5217 True,
5218 "ignore_fk",
5219 ):
5220 return False
5221 return True
5223 if len(self._columns) == 1:
5224 col = list(self._columns)[0]
5226 if col.autoincrement is True:
5227 _validate_autoinc(col, True)
5228 return col
5229 elif col.autoincrement in (
5230 "auto",
5231 "ignore_fk",
5232 ) and _validate_autoinc(col, False):
5233 return col
5234 else:
5235 return None
5237 else:
5238 autoinc = None
5239 for col in self._columns:
5240 if col.autoincrement is True:
5241 _validate_autoinc(col, True)
5242 if autoinc is not None:
5243 raise exc.ArgumentError(
5244 f"Only one Column may be marked "
5245 f"autoincrement=True, found both "
5246 f"{col.name} and {autoinc.name}."
5247 )
5248 else:
5249 autoinc = col
5251 return autoinc
5254class UniqueConstraint(ColumnCollectionConstraint):
5255 """A table-level UNIQUE constraint.
5257 Defines a single column or composite UNIQUE constraint. For a no-frills,
5258 single column constraint, adding ``unique=True`` to the ``Column``
5259 definition is a shorthand equivalent for an unnamed, single column
5260 UniqueConstraint.
5261 """
5263 __visit_name__ = "unique_constraint"
5266class Index(
5267 DialectKWArgs, ColumnCollectionMixin, HasConditionalDDL, SchemaItem
5268):
5269 """A table-level INDEX.
5271 Defines a composite (one or more column) INDEX.
5273 E.g.::
5275 sometable = Table(
5276 "sometable",
5277 metadata,
5278 Column("name", String(50)),
5279 Column("address", String(100)),
5280 )
5282 Index("some_index", sometable.c.name)
5284 For a no-frills, single column index, adding
5285 :class:`_schema.Column` also supports ``index=True``::
5287 sometable = Table(
5288 "sometable", metadata, Column("name", String(50), index=True)
5289 )
5291 For a composite index, multiple columns can be specified::
5293 Index("some_index", sometable.c.name, sometable.c.address)
5295 Functional indexes are supported as well, typically by using the
5296 :data:`.func` construct in conjunction with table-bound
5297 :class:`_schema.Column` objects::
5299 Index("some_index", func.lower(sometable.c.name))
5301 An :class:`.Index` can also be manually associated with a
5302 :class:`_schema.Table`,
5303 either through inline declaration or using
5304 :meth:`_schema.Table.append_constraint`. When this approach is used,
5305 the names
5306 of the indexed columns can be specified as strings::
5308 Table(
5309 "sometable",
5310 metadata,
5311 Column("name", String(50)),
5312 Column("address", String(100)),
5313 Index("some_index", "name", "address"),
5314 )
5316 To support functional or expression-based indexes in this form, the
5317 :func:`_expression.text` construct may be used::
5319 from sqlalchemy import text
5321 Table(
5322 "sometable",
5323 metadata,
5324 Column("name", String(50)),
5325 Column("address", String(100)),
5326 Index("some_index", text("lower(name)")),
5327 )
5329 .. seealso::
5331 :ref:`schema_indexes` - General information on :class:`.Index`.
5333 :ref:`postgresql_indexes` - PostgreSQL-specific options available for
5334 the :class:`.Index` construct.
5336 :ref:`mysql_indexes` - MySQL-specific options available for the
5337 :class:`.Index` construct.
5339 :ref:`mssql_indexes` - MSSQL-specific options available for the
5340 :class:`.Index` construct.
5342 """
5344 __visit_name__ = "index"
5346 table: Optional[Table]
5347 expressions: _typing_Sequence[Union[str, ColumnElement[Any]]]
5348 _table_bound_expressions: _typing_Sequence[ColumnElement[Any]]
5350 def __init__(
5351 self,
5352 name: Optional[str],
5353 *expressions: _DDLColumnArgument,
5354 unique: bool = False,
5355 quote: Optional[bool] = None,
5356 info: Optional[_InfoType] = None,
5357 _table: Optional[Table] = None,
5358 _column_flag: bool = False,
5359 **dialect_kw: Any,
5360 ) -> None:
5361 r"""Construct an index object.
5363 :param name:
5364 The name of the index
5366 :param \*expressions:
5367 Column expressions to include in the index. The expressions
5368 are normally instances of :class:`_schema.Column`, but may also
5369 be arbitrary SQL expressions which ultimately refer to a
5370 :class:`_schema.Column`.
5372 :param unique=False:
5373 Keyword only argument; if True, create a unique index.
5375 :param quote=None:
5376 Keyword only argument; whether to apply quoting to the name of
5377 the index. Works in the same manner as that of
5378 :paramref:`_schema.Column.quote`.
5380 :param info=None: Optional data dictionary which will be populated
5381 into the :attr:`.SchemaItem.info` attribute of this object.
5383 :param \**dialect_kw: Additional keyword arguments not mentioned above
5384 are dialect specific, and passed in the form
5385 ``<dialectname>_<argname>``. See the documentation regarding an
5386 individual dialect at :ref:`dialect_toplevel` for detail on
5387 documented arguments.
5389 """
5390 self.table = table = None
5392 self.name = quoted_name.construct(name, quote)
5393 self.unique = unique
5394 if info is not None:
5395 self.info = info
5397 # TODO: consider "table" argument being public, but for
5398 # the purpose of the fix here, it starts as private.
5399 if _table is not None:
5400 table = _table
5402 self._validate_dialect_kwargs(dialect_kw)
5404 self.expressions = []
5405 # will call _set_parent() if table-bound column
5406 # objects are present
5407 ColumnCollectionMixin.__init__(
5408 self,
5409 *expressions,
5410 _column_flag=_column_flag,
5411 _gather_expressions=self.expressions,
5412 )
5413 if table is not None:
5414 self._set_parent(table)
5416 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
5417 table = parent
5418 assert isinstance(table, Table)
5419 ColumnCollectionMixin._set_parent(self, table)
5421 if self.table is not None and table is not self.table:
5422 raise exc.ArgumentError(
5423 f"Index '{self.name}' is against table "
5424 f"'{self.table.description}', and "
5425 f"cannot be associated with table '{table.description}'."
5426 )
5427 self.table = table
5428 table.indexes.add(self)
5430 expressions = self.expressions
5431 col_expressions = self._col_expressions(table)
5432 assert len(expressions) == len(col_expressions)
5434 exprs = []
5435 for expr, colexpr in zip(expressions, col_expressions):
5436 if isinstance(expr, ClauseElement):
5437 exprs.append(expr)
5438 elif colexpr is not None:
5439 exprs.append(colexpr)
5440 else:
5441 assert False
5442 self.expressions = self._table_bound_expressions = exprs
5444 def create(self, bind: _CreateDropBind, checkfirst: bool = False) -> None:
5445 """Issue a ``CREATE`` statement for this
5446 :class:`.Index`, using the given
5447 :class:`.Connection` or :class:`.Engine`` for connectivity.
5449 .. seealso::
5451 :meth:`_schema.MetaData.create_all`.
5453 """
5454 bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
5456 def drop(self, bind: _CreateDropBind, checkfirst: bool = False) -> None:
5457 """Issue a ``DROP`` statement for this
5458 :class:`.Index`, using the given
5459 :class:`.Connection` or :class:`.Engine` for connectivity.
5461 .. seealso::
5463 :meth:`_schema.MetaData.drop_all`.
5465 """
5466 bind._run_ddl_visitor(ddl.SchemaDropper, self, checkfirst=checkfirst)
5468 def __repr__(self) -> str:
5469 exprs: _typing_Sequence[Any] # noqa: F842
5471 return "Index(%s)" % (
5472 ", ".join(
5473 [repr(self.name)]
5474 + [repr(e) for e in self.expressions]
5475 + (self.unique and ["unique=True"] or [])
5476 )
5477 )
5480_NamingSchemaCallable = Callable[[Constraint, Table], str]
5481_NamingSchemaDirective = Union[str, _NamingSchemaCallable]
5484class _NamingSchemaTD(TypedDict, total=False):
5485 fk: _NamingSchemaDirective
5486 pk: _NamingSchemaDirective
5487 ix: _NamingSchemaDirective
5488 ck: _NamingSchemaDirective
5489 uq: _NamingSchemaDirective
5492_NamingSchemaParameter = Union[
5493 # it seems like the TypedDict here is useful for pylance typeahead,
5494 # and not much else
5495 _NamingSchemaTD,
5496 # there is no form that allows Union[Type[Any], str] to work in all
5497 # cases, including breaking out Mapping[] entries for each combination
5498 # even, therefore keys must be `Any` (see #10264)
5499 Mapping[Any, _NamingSchemaDirective],
5500]
5503DEFAULT_NAMING_CONVENTION: _NamingSchemaParameter = util.immutabledict(
5504 {"ix": "ix_%(column_0_label)s"}
5505)
5508class MetaData(HasSchemaAttr):
5509 """A collection of :class:`_schema.Table`
5510 objects and their associated schema
5511 constructs.
5513 Holds a collection of :class:`_schema.Table` objects as well as
5514 an optional binding to an :class:`_engine.Engine` or
5515 :class:`_engine.Connection`. If bound, the :class:`_schema.Table` objects
5516 in the collection and their columns may participate in implicit SQL
5517 execution.
5519 The :class:`_schema.Table` objects themselves are stored in the
5520 :attr:`_schema.MetaData.tables` dictionary.
5522 :class:`_schema.MetaData` is a thread-safe object for read operations.
5523 Construction of new tables within a single :class:`_schema.MetaData`
5524 object,
5525 either explicitly or via reflection, may not be completely thread-safe.
5527 .. seealso::
5529 :ref:`metadata_describing` - Introduction to database metadata
5531 """
5533 __visit_name__ = "metadata"
5535 def __init__(
5536 self,
5537 schema: Optional[str] = None,
5538 quote_schema: Optional[bool] = None,
5539 naming_convention: Optional[_NamingSchemaParameter] = None,
5540 info: Optional[_InfoType] = None,
5541 ) -> None:
5542 """Create a new MetaData object.
5544 :param schema:
5545 The default schema to use for the :class:`_schema.Table`,
5546 :class:`.Sequence`, and potentially other objects associated with
5547 this :class:`_schema.MetaData`. Defaults to ``None``.
5549 .. seealso::
5551 :ref:`schema_metadata_schema_name` - details on how the
5552 :paramref:`_schema.MetaData.schema` parameter is used.
5554 :paramref:`_schema.Table.schema`
5556 :paramref:`.Sequence.schema`
5558 :param quote_schema:
5559 Sets the ``quote_schema`` flag for those :class:`_schema.Table`,
5560 :class:`.Sequence`, and other objects which make usage of the
5561 local ``schema`` name.
5563 :param info: Optional data dictionary which will be populated into the
5564 :attr:`.SchemaItem.info` attribute of this object.
5566 :param naming_convention: a dictionary referring to values which
5567 will establish default naming conventions for :class:`.Constraint`
5568 and :class:`.Index` objects, for those objects which are not given
5569 a name explicitly.
5571 The keys of this dictionary may be:
5573 * a constraint or Index class, e.g. the :class:`.UniqueConstraint`,
5574 :class:`_schema.ForeignKeyConstraint` class, the :class:`.Index`
5575 class
5577 * a string mnemonic for one of the known constraint classes;
5578 ``"fk"``, ``"pk"``, ``"ix"``, ``"ck"``, ``"uq"`` for foreign key,
5579 primary key, index, check, and unique constraint, respectively.
5581 * the string name of a user-defined "token" that can be used
5582 to define new naming tokens.
5584 The values associated with each "constraint class" or "constraint
5585 mnemonic" key are string naming templates, such as
5586 ``"uq_%(table_name)s_%(column_0_name)s"``,
5587 which describe how the name should be composed. The values
5588 associated with user-defined "token" keys should be callables of the
5589 form ``fn(constraint, table)``, which accepts the constraint/index
5590 object and :class:`_schema.Table` as arguments, returning a string
5591 result.
5593 The built-in names are as follows, some of which may only be
5594 available for certain types of constraint:
5596 * ``%(table_name)s`` - the name of the :class:`_schema.Table`
5597 object
5598 associated with the constraint.
5600 * ``%(referred_table_name)s`` - the name of the
5601 :class:`_schema.Table`
5602 object associated with the referencing target of a
5603 :class:`_schema.ForeignKeyConstraint`.
5605 * ``%(column_0_name)s`` - the name of the :class:`_schema.Column`
5606 at
5607 index position "0" within the constraint.
5609 * ``%(column_0N_name)s`` - the name of all :class:`_schema.Column`
5610 objects in order within the constraint, joined without a
5611 separator.
5613 * ``%(column_0_N_name)s`` - the name of all
5614 :class:`_schema.Column`
5615 objects in order within the constraint, joined with an
5616 underscore as a separator.
5618 * ``%(column_0_label)s``, ``%(column_0N_label)s``,
5619 ``%(column_0_N_label)s`` - the label of either the zeroth
5620 :class:`_schema.Column` or all :class:`.Columns`, separated with
5621 or without an underscore
5623 * ``%(column_0_key)s``, ``%(column_0N_key)s``,
5624 ``%(column_0_N_key)s`` - the key of either the zeroth
5625 :class:`_schema.Column` or all :class:`.Columns`, separated with
5626 or without an underscore
5628 * ``%(referred_column_0_name)s``, ``%(referred_column_0N_name)s``
5629 ``%(referred_column_0_N_name)s``, ``%(referred_column_0_key)s``,
5630 ``%(referred_column_0N_key)s``, ... column tokens which
5631 render the names/keys/labels of columns that are referenced
5632 by a :class:`_schema.ForeignKeyConstraint`.
5634 * ``%(constraint_name)s`` - a special key that refers to the
5635 existing name given to the constraint. When this key is
5636 present, the :class:`.Constraint` object's existing name will be
5637 replaced with one that is composed from template string that
5638 uses this token. When this token is present, it is required that
5639 the :class:`.Constraint` is given an explicit name ahead of time.
5641 * user-defined: any additional token may be implemented by passing
5642 it along with a ``fn(constraint, table)`` callable to the
5643 naming_convention dictionary.
5645 .. seealso::
5647 :ref:`constraint_naming_conventions` - for detailed usage
5648 examples.
5650 """
5651 if schema is not None and not isinstance(schema, str):
5652 raise exc.ArgumentError(
5653 "expected schema argument to be a string, "
5654 f"got {type(schema)}."
5655 )
5656 self.tables = util.FacadeDict()
5657 self.schema = quoted_name.construct(schema, quote_schema)
5658 self.naming_convention = (
5659 naming_convention
5660 if naming_convention
5661 else DEFAULT_NAMING_CONVENTION
5662 )
5663 if info:
5664 self.info = info
5665 self._schemas: Set[str] = set()
5666 self._sequences: Dict[str, Sequence] = {}
5667 self._fk_memos: Dict[Tuple[str, Optional[str]], List[ForeignKey]] = (
5668 collections.defaultdict(list)
5669 )
5671 tables: util.FacadeDict[str, Table]
5672 """A dictionary of :class:`_schema.Table`
5673 objects keyed to their name or "table key".
5675 The exact key is that determined by the :attr:`_schema.Table.key`
5676 attribute;
5677 for a table with no :attr:`_schema.Table.schema` attribute,
5678 this is the same
5679 as :attr:`_schema.Table.name`. For a table with a schema,
5680 it is typically of the
5681 form ``schemaname.tablename``.
5683 .. seealso::
5685 :attr:`_schema.MetaData.sorted_tables`
5687 """
5689 def __repr__(self) -> str:
5690 return "MetaData()"
5692 def __contains__(self, table_or_key: Union[str, Table]) -> bool:
5693 if not isinstance(table_or_key, str):
5694 table_or_key = table_or_key.key
5695 return table_or_key in self.tables
5697 def _add_table(
5698 self, name: str, schema: Optional[str], table: Table
5699 ) -> None:
5700 key = _get_table_key(name, schema)
5701 self.tables._insert_item(key, table)
5702 if schema:
5703 self._schemas.add(schema)
5705 def _remove_table(self, name: str, schema: Optional[str]) -> None:
5706 key = _get_table_key(name, schema)
5707 removed = dict.pop(self.tables, key, None)
5708 if removed is not None:
5709 for fk in removed.foreign_keys:
5710 fk._remove_from_metadata(self)
5711 if self._schemas:
5712 self._schemas = {
5713 t.schema for t in self.tables.values() if t.schema is not None
5714 }
5716 def __getstate__(self) -> Dict[str, Any]:
5717 return {
5718 "tables": self.tables,
5719 "schema": self.schema,
5720 "schemas": self._schemas,
5721 "sequences": self._sequences,
5722 "fk_memos": self._fk_memos,
5723 "naming_convention": self.naming_convention,
5724 }
5726 def __setstate__(self, state: Dict[str, Any]) -> None:
5727 self.tables = state["tables"]
5728 self.schema = state["schema"]
5729 self.naming_convention = state["naming_convention"]
5730 self._sequences = state["sequences"]
5731 self._schemas = state["schemas"]
5732 self._fk_memos = state["fk_memos"]
5734 def clear(self) -> None:
5735 """Clear all Table objects from this MetaData."""
5737 dict.clear(self.tables) # type: ignore
5738 self._schemas.clear()
5739 self._fk_memos.clear()
5741 def remove(self, table: Table) -> None:
5742 """Remove the given Table object from this MetaData."""
5744 self._remove_table(table.name, table.schema)
5746 @property
5747 def sorted_tables(self) -> List[Table]:
5748 """Returns a list of :class:`_schema.Table` objects sorted in order of
5749 foreign key dependency.
5751 The sorting will place :class:`_schema.Table`
5752 objects that have dependencies
5753 first, before the dependencies themselves, representing the
5754 order in which they can be created. To get the order in which
5755 the tables would be dropped, use the ``reversed()`` Python built-in.
5757 .. warning::
5759 The :attr:`.MetaData.sorted_tables` attribute cannot by itself
5760 accommodate automatic resolution of dependency cycles between
5761 tables, which are usually caused by mutually dependent foreign key
5762 constraints. When these cycles are detected, the foreign keys
5763 of these tables are omitted from consideration in the sort.
5764 A warning is emitted when this condition occurs, which will be an
5765 exception raise in a future release. Tables which are not part
5766 of the cycle will still be returned in dependency order.
5768 To resolve these cycles, the
5769 :paramref:`_schema.ForeignKeyConstraint.use_alter` parameter may be
5770 applied to those constraints which create a cycle. Alternatively,
5771 the :func:`_schema.sort_tables_and_constraints` function will
5772 automatically return foreign key constraints in a separate
5773 collection when cycles are detected so that they may be applied
5774 to a schema separately.
5776 .. seealso::
5778 :func:`_schema.sort_tables`
5780 :func:`_schema.sort_tables_and_constraints`
5782 :attr:`_schema.MetaData.tables`
5784 :meth:`_reflection.Inspector.get_table_names`
5786 :meth:`_reflection.Inspector.get_sorted_table_and_fkc_names`
5789 """
5790 return ddl.sort_tables(
5791 sorted(self.tables.values(), key=lambda t: t.key) # type: ignore
5792 )
5794 # overload needed to work around mypy this mypy
5795 # https://github.com/python/mypy/issues/17093
5796 @overload
5797 def reflect(
5798 self,
5799 bind: Engine,
5800 schema: Optional[str] = ...,
5801 views: bool = ...,
5802 only: Union[
5803 _typing_Sequence[str], Callable[[str, MetaData], bool], None
5804 ] = ...,
5805 extend_existing: bool = ...,
5806 autoload_replace: bool = ...,
5807 resolve_fks: bool = ...,
5808 **dialect_kwargs: Any,
5809 ) -> None: ...
5811 @overload
5812 def reflect(
5813 self,
5814 bind: Connection,
5815 schema: Optional[str] = ...,
5816 views: bool = ...,
5817 only: Union[
5818 _typing_Sequence[str], Callable[[str, MetaData], bool], None
5819 ] = ...,
5820 extend_existing: bool = ...,
5821 autoload_replace: bool = ...,
5822 resolve_fks: bool = ...,
5823 **dialect_kwargs: Any,
5824 ) -> None: ...
5826 @util.preload_module("sqlalchemy.engine.reflection")
5827 def reflect(
5828 self,
5829 bind: Union[Engine, Connection],
5830 schema: Optional[str] = None,
5831 views: bool = False,
5832 only: Union[
5833 _typing_Sequence[str], Callable[[str, MetaData], bool], None
5834 ] = None,
5835 extend_existing: bool = False,
5836 autoload_replace: bool = True,
5837 resolve_fks: bool = True,
5838 **dialect_kwargs: Any,
5839 ) -> None:
5840 r"""Load all available table definitions from the database.
5842 Automatically creates ``Table`` entries in this ``MetaData`` for any
5843 table available in the database but not yet present in the
5844 ``MetaData``. May be called multiple times to pick up tables recently
5845 added to the database, however no special action is taken if a table
5846 in this ``MetaData`` no longer exists in the database.
5848 :param bind:
5849 A :class:`.Connection` or :class:`.Engine` used to access the
5850 database.
5852 :param schema:
5853 Optional, query and reflect tables from an alternate schema.
5854 If None, the schema associated with this :class:`_schema.MetaData`
5855 is used, if any.
5857 :param views:
5858 If True, also reflect views (materialized and plain).
5860 :param only:
5861 Optional. Load only a sub-set of available named tables. May be
5862 specified as a sequence of names or a callable.
5864 If a sequence of names is provided, only those tables will be
5865 reflected. An error is raised if a table is requested but not
5866 available. Named tables already present in this ``MetaData`` are
5867 ignored.
5869 If a callable is provided, it will be used as a boolean predicate to
5870 filter the list of potential table names. The callable is called
5871 with a table name and this ``MetaData`` instance as positional
5872 arguments and should return a true value for any table to reflect.
5874 :param extend_existing: Passed along to each :class:`_schema.Table` as
5875 :paramref:`_schema.Table.extend_existing`.
5877 :param autoload_replace: Passed along to each :class:`_schema.Table`
5878 as
5879 :paramref:`_schema.Table.autoload_replace`.
5881 :param resolve_fks: if True, reflect :class:`_schema.Table`
5882 objects linked
5883 to :class:`_schema.ForeignKey` objects located in each
5884 :class:`_schema.Table`.
5885 For :meth:`_schema.MetaData.reflect`,
5886 this has the effect of reflecting
5887 related tables that might otherwise not be in the list of tables
5888 being reflected, for example if the referenced table is in a
5889 different schema or is omitted via the
5890 :paramref:`.MetaData.reflect.only` parameter. When False,
5891 :class:`_schema.ForeignKey` objects are not followed to the
5892 :class:`_schema.Table`
5893 in which they link, however if the related table is also part of the
5894 list of tables that would be reflected in any case, the
5895 :class:`_schema.ForeignKey` object will still resolve to its related
5896 :class:`_schema.Table` after the :meth:`_schema.MetaData.reflect`
5897 operation is
5898 complete. Defaults to True.
5900 .. seealso::
5902 :paramref:`_schema.Table.resolve_fks`
5904 :param \**dialect_kwargs: Additional keyword arguments not mentioned
5905 above are dialect specific, and passed in the form
5906 ``<dialectname>_<argname>``. See the documentation regarding an
5907 individual dialect at :ref:`dialect_toplevel` for detail on
5908 documented arguments.
5910 .. seealso::
5912 :ref:`metadata_reflection_toplevel`
5914 :meth:`_events.DDLEvents.column_reflect` - Event used to customize
5915 the reflected columns. Usually used to generalize the types using
5916 :meth:`_types.TypeEngine.as_generic`
5918 :ref:`metadata_reflection_dbagnostic_types` - describes how to
5919 reflect tables using general types.
5921 """
5923 with inspection.inspect(bind)._inspection_context() as insp:
5924 reflect_opts: Any = {
5925 "autoload_with": insp,
5926 "extend_existing": extend_existing,
5927 "autoload_replace": autoload_replace,
5928 "resolve_fks": resolve_fks,
5929 "_extend_on": set(),
5930 }
5932 reflect_opts.update(dialect_kwargs)
5934 if schema is None:
5935 schema = self.schema
5937 if schema is not None:
5938 reflect_opts["schema"] = schema
5940 kind = util.preloaded.engine_reflection.ObjectKind.TABLE
5941 available: util.OrderedSet[str] = util.OrderedSet(
5942 insp.get_table_names(schema)
5943 )
5944 if views:
5945 kind = util.preloaded.engine_reflection.ObjectKind.ANY
5946 available.update(insp.get_view_names(schema))
5947 try:
5948 available.update(insp.get_materialized_view_names(schema))
5949 except NotImplementedError:
5950 pass
5952 if schema is not None:
5953 available_w_schema: util.OrderedSet[str] = util.OrderedSet(
5954 [f"{schema}.{name}" for name in available]
5955 )
5956 else:
5957 available_w_schema = available
5959 current = set(self.tables)
5961 if only is None:
5962 load = [
5963 name
5964 for name, schname in zip(available, available_w_schema)
5965 if extend_existing or schname not in current
5966 ]
5967 elif callable(only):
5968 load = [
5969 name
5970 for name, schname in zip(available, available_w_schema)
5971 if (extend_existing or schname not in current)
5972 and only(name, self)
5973 ]
5974 else:
5975 missing = [name for name in only if name not in available]
5976 if missing:
5977 s = schema and (" schema '%s'" % schema) or ""
5978 missing_str = ", ".join(missing)
5979 raise exc.InvalidRequestError(
5980 f"Could not reflect: requested table(s) not available "
5981 f"in {bind.engine!r}{s}: ({missing_str})"
5982 )
5983 load = [
5984 name
5985 for name in only
5986 if extend_existing or name not in current
5987 ]
5988 # pass the available tables so the inspector can
5989 # choose to ignore the filter_names
5990 _reflect_info = insp._get_reflection_info(
5991 schema=schema,
5992 filter_names=load,
5993 available=available,
5994 kind=kind,
5995 scope=util.preloaded.engine_reflection.ObjectScope.ANY,
5996 **dialect_kwargs,
5997 )
5998 reflect_opts["_reflect_info"] = _reflect_info
6000 for name in load:
6001 try:
6002 Table(name, self, **reflect_opts)
6003 except exc.UnreflectableTableError as uerr:
6004 util.warn(f"Skipping table {name}: {uerr}")
6006 def create_all(
6007 self,
6008 bind: _CreateDropBind,
6009 tables: Optional[_typing_Sequence[Table]] = None,
6010 checkfirst: bool = True,
6011 ) -> None:
6012 """Create all tables stored in this metadata.
6014 Conditional by default, will not attempt to recreate tables already
6015 present in the target database.
6017 :param bind:
6018 A :class:`.Connection` or :class:`.Engine` used to access the
6019 database.
6021 :param tables:
6022 Optional list of ``Table`` objects, which is a subset of the total
6023 tables in the ``MetaData`` (others are ignored).
6025 :param checkfirst:
6026 Defaults to True, don't issue CREATEs for tables already present
6027 in the target database.
6029 """
6030 bind._run_ddl_visitor(
6031 ddl.SchemaGenerator, self, checkfirst=checkfirst, tables=tables
6032 )
6034 def drop_all(
6035 self,
6036 bind: _CreateDropBind,
6037 tables: Optional[_typing_Sequence[Table]] = None,
6038 checkfirst: bool = True,
6039 ) -> None:
6040 """Drop all tables stored in this metadata.
6042 Conditional by default, will not attempt to drop tables not present in
6043 the target database.
6045 :param bind:
6046 A :class:`.Connection` or :class:`.Engine` used to access the
6047 database.
6049 :param tables:
6050 Optional list of ``Table`` objects, which is a subset of the
6051 total tables in the ``MetaData`` (others are ignored).
6053 :param checkfirst:
6054 Defaults to True, only issue DROPs for tables confirmed to be
6055 present in the target database.
6057 """
6058 bind._run_ddl_visitor(
6059 ddl.SchemaDropper, self, checkfirst=checkfirst, tables=tables
6060 )
6063class Computed(FetchedValue, SchemaItem):
6064 """Defines a generated column, i.e. "GENERATED ALWAYS AS" syntax.
6066 The :class:`.Computed` construct is an inline construct added to the
6067 argument list of a :class:`_schema.Column` object::
6069 from sqlalchemy import Computed
6071 Table(
6072 "square",
6073 metadata_obj,
6074 Column("side", Float, nullable=False),
6075 Column("area", Float, Computed("side * side")),
6076 )
6078 See the linked documentation below for complete details.
6080 .. seealso::
6082 :ref:`computed_ddl`
6084 """
6086 __visit_name__ = "computed_column"
6088 column: Optional[Column[Any]]
6090 @_document_text_coercion(
6091 "sqltext", ":class:`.Computed`", ":paramref:`.Computed.sqltext`"
6092 )
6093 def __init__(
6094 self, sqltext: _DDLColumnArgument, persisted: Optional[bool] = None
6095 ) -> None:
6096 """Construct a GENERATED ALWAYS AS DDL construct to accompany a
6097 :class:`_schema.Column`.
6099 :param sqltext:
6100 A string containing the column generation expression, which will be
6101 used verbatim, or a SQL expression construct, such as a
6102 :func:`_expression.text`
6103 object. If given as a string, the object is converted to a
6104 :func:`_expression.text` object.
6106 :param persisted:
6107 Optional, controls how this column should be persisted by the
6108 database. Possible values are:
6110 * ``None``, the default, it will use the default persistence
6111 defined by the database.
6112 * ``True``, will render ``GENERATED ALWAYS AS ... STORED``, or the
6113 equivalent for the target database if supported.
6114 * ``False``, will render ``GENERATED ALWAYS AS ... VIRTUAL``, or
6115 the equivalent for the target database if supported.
6117 Specifying ``True`` or ``False`` may raise an error when the DDL
6118 is emitted to the target database if the database does not support
6119 that persistence option. Leaving this parameter at its default
6120 of ``None`` is guaranteed to succeed for all databases that support
6121 ``GENERATED ALWAYS AS``.
6123 """
6124 self.sqltext = coercions.expect(roles.DDLExpressionRole, sqltext)
6125 self.persisted = persisted
6126 self.column = None
6128 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
6129 assert isinstance(parent, Column)
6131 if not isinstance(
6132 parent.server_default, (type(None), Computed)
6133 ) or not isinstance(parent.server_onupdate, (type(None), Computed)):
6134 raise exc.ArgumentError(
6135 "A generated column cannot specify a server_default or a "
6136 "server_onupdate argument"
6137 )
6138 self.column = parent
6139 parent.computed = self
6140 self.column.server_onupdate = self
6141 self.column.server_default = self
6143 def _as_for_update(self, for_update: bool) -> FetchedValue:
6144 return self
6146 @util.deprecated(
6147 "1.4",
6148 "The :meth:`_schema.Computed.copy` method is deprecated "
6149 "and will be removed in a future release.",
6150 )
6151 def copy(
6152 self, *, target_table: Optional[Table] = None, **kw: Any
6153 ) -> Computed:
6154 return self._copy(target_table=target_table, **kw)
6156 def _copy(
6157 self, *, target_table: Optional[Table] = None, **kw: Any
6158 ) -> Computed:
6159 sqltext = _copy_expression(
6160 self.sqltext,
6161 self.column.table if self.column is not None else None,
6162 target_table,
6163 )
6164 g = Computed(sqltext, persisted=self.persisted)
6166 return self._schema_item_copy(g)
6169class Identity(IdentityOptions, FetchedValue, SchemaItem):
6170 """Defines an identity column, i.e. "GENERATED { ALWAYS | BY DEFAULT }
6171 AS IDENTITY" syntax.
6173 The :class:`.Identity` construct is an inline construct added to the
6174 argument list of a :class:`_schema.Column` object::
6176 from sqlalchemy import Identity
6178 Table(
6179 "foo",
6180 metadata_obj,
6181 Column("id", Integer, Identity()),
6182 Column("description", Text),
6183 )
6185 See the linked documentation below for complete details.
6187 .. versionadded:: 1.4
6189 .. seealso::
6191 :ref:`identity_ddl`
6193 """
6195 __visit_name__ = "identity_column"
6197 is_identity = True
6199 @util.deprecated_params(
6200 order=(
6201 "2.1",
6202 "This parameter is supported only by Oracle Database, "
6203 "use ``oracle_order`` instead.",
6204 ),
6205 on_null=(
6206 "2.1",
6207 "This parameter is supported only by Oracle Database, "
6208 "use ``oracle_on_null`` instead.",
6209 ),
6210 )
6211 def __init__(
6212 self,
6213 always: Optional[bool] = False,
6214 on_null: Optional[bool] = None,
6215 start: Optional[int] = None,
6216 increment: Optional[int] = None,
6217 minvalue: Optional[int] = None,
6218 maxvalue: Optional[int] = None,
6219 nominvalue: Optional[bool] = None,
6220 nomaxvalue: Optional[bool] = None,
6221 cycle: Optional[bool] = None,
6222 cache: Optional[int] = None,
6223 order: Optional[bool] = None,
6224 **dialect_kw: Any,
6225 ) -> None:
6226 """Construct a GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY DDL
6227 construct to accompany a :class:`_schema.Column`.
6229 See the :class:`.Sequence` documentation for a complete description
6230 of most parameters.
6232 .. note::
6233 MSSQL supports this construct as the preferred alternative to
6234 generate an IDENTITY on a column, but it uses non standard
6235 syntax that only support :paramref:`_schema.Identity.start`
6236 and :paramref:`_schema.Identity.increment`.
6237 All other parameters are ignored.
6239 :param always:
6240 A boolean, that indicates the type of identity column.
6241 If ``False`` is specified, the default, then the user-specified
6242 value takes precedence.
6243 If ``True`` is specified, a user-specified value is not accepted (
6244 on some backends, like PostgreSQL, OVERRIDING SYSTEM VALUE, or
6245 similar, may be specified in an INSERT to override the sequence
6246 value).
6247 Some backends also have a default value for this parameter,
6248 ``None`` can be used to omit rendering this part in the DDL. It
6249 will be treated as ``False`` if a backend does not have a default
6250 value.
6252 :param on_null:
6253 Set to ``True`` to specify ON NULL in conjunction with a
6254 ``always=False`` identity column. This option is only supported on
6255 some backends, like Oracle Database.
6257 :param start: the starting index of the sequence.
6258 :param increment: the increment value of the sequence.
6259 :param minvalue: the minimum value of the sequence.
6260 :param maxvalue: the maximum value of the sequence.
6261 :param nominvalue: no minimum value of the sequence.
6262 :param nomaxvalue: no maximum value of the sequence.
6263 :param cycle: allows the sequence to wrap around when the maxvalue
6264 or minvalue has been reached.
6265 :param cache: optional integer value; number of future values in the
6266 sequence which are calculated in advance.
6267 :param order: optional boolean value; if true, renders the
6268 ORDER keyword.
6270 """
6271 self.dialect_options
6272 if on_null is not None:
6273 if "oracle_on_null" in dialect_kw:
6274 raise exc.ArgumentError(
6275 "Cannot specify both 'on_null' and 'oracle_on_null'. "
6276 "Plese use only 'oracle_on_null'."
6277 )
6278 dialect_kw["oracle_on_null"] = on_null
6280 IdentityOptions.__init__(
6281 self,
6282 start=start,
6283 increment=increment,
6284 minvalue=minvalue,
6285 maxvalue=maxvalue,
6286 nominvalue=nominvalue,
6287 nomaxvalue=nomaxvalue,
6288 cycle=cycle,
6289 cache=cache,
6290 order=order,
6291 **dialect_kw,
6292 )
6293 self.always = always
6294 self.column = None
6296 @property
6297 def on_null(self) -> Optional[bool]:
6298 """Alias of the ``dialect_kwargs`` ``'oracle_on_null'``.
6300 .. deprecated:: 2.1 The 'on_null' attribute is deprecated.
6301 """
6302 value: Optional[bool] = self.dialect_kwargs.get("oracle_on_null")
6303 return value
6305 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
6306 assert isinstance(parent, Column)
6307 if not isinstance(
6308 parent.server_default, (type(None), Identity)
6309 ) or not isinstance(parent.server_onupdate, type(None)):
6310 raise exc.ArgumentError(
6311 "A column with an Identity object cannot specify a "
6312 "server_default or a server_onupdate argument"
6313 )
6314 if parent.autoincrement is False:
6315 raise exc.ArgumentError(
6316 "A column with an Identity object cannot specify "
6317 "autoincrement=False"
6318 )
6319 self.column = parent
6321 parent.identity = self
6322 if parent._user_defined_nullable is NULL_UNSPECIFIED:
6323 parent.nullable = False
6325 parent.server_default = self
6327 def _as_for_update(self, for_update: bool) -> FetchedValue:
6328 return self
6330 @util.deprecated(
6331 "1.4",
6332 "The :meth:`_schema.Identity.copy` method is deprecated "
6333 "and will be removed in a future release.",
6334 )
6335 def copy(self, **kw: Any) -> Identity:
6336 return self._copy(**kw)
6338 def _copy(self, **kw: Any) -> Identity:
6339 i = Identity(**self._as_dict(), **self.dialect_kwargs)
6341 return self._schema_item_copy(i)
6343 def _as_dict(self) -> Dict[str, Any]:
6344 return {
6345 # always=None means something different than always=False
6346 "always": self.always,
6347 **super()._as_dict(),
6348 }