Coverage for /pythoncovmergedfiles/medio/medio/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/schema.py: 40%
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-2024 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 .coercions import _document_text_coercion
75from .elements import ClauseElement
76from .elements import ColumnClause
77from .elements import ColumnElement
78from .elements import quoted_name
79from .elements import TextClause
80from .selectable import TableClause
81from .type_api import to_instance
82from .visitors import ExternallyTraversible
83from .. import event
84from .. import exc
85from .. import inspection
86from .. import util
87from ..util import HasMemoized
88from ..util.typing import Literal
89from ..util.typing import Self
90from ..util.typing import TypeGuard
92if typing.TYPE_CHECKING:
93 from ._typing import _AutoIncrementType
94 from ._typing import _DDLColumnArgument
95 from ._typing import _DDLColumnReferenceArgument
96 from ._typing import _InfoType
97 from ._typing import _TextCoercedExpressionArgument
98 from ._typing import _TypeEngineArgument
99 from .base import ReadOnlyColumnCollection
100 from .compiler import DDLCompiler
101 from .elements import BindParameter
102 from .functions import Function
103 from .type_api import TypeEngine
104 from .visitors import anon_map
105 from ..engine import Connection
106 from ..engine import Engine
107 from ..engine.interfaces import _CoreMultiExecuteParams
108 from ..engine.interfaces import CoreExecuteOptionsParameter
109 from ..engine.interfaces import ExecutionContext
110 from ..engine.mock import MockConnection
111 from ..engine.reflection import _ReflectionInfo
112 from ..sql.selectable import FromClause
114_T = TypeVar("_T", bound="Any")
115_SI = TypeVar("_SI", bound="SchemaItem")
116_TAB = TypeVar("_TAB", bound="Table")
119_CreateDropBind = Union["Engine", "Connection", "MockConnection"]
121_ConstraintNameArgument = Optional[Union[str, _NoneName]]
123_ServerDefaultArgument = Union[
124 "FetchedValue", str, TextClause, ColumnElement[Any]
125]
127_ServerOnUpdateArgument = _ServerDefaultArgument
130class SchemaConst(Enum):
131 RETAIN_SCHEMA = 1
132 """Symbol indicating that a :class:`_schema.Table`, :class:`.Sequence`
133 or in some cases a :class:`_schema.ForeignKey` object, in situations
134 where the object is being copied for a :meth:`.Table.to_metadata`
135 operation, should retain the schema name that it already has.
137 """
139 BLANK_SCHEMA = 2
140 """Symbol indicating that a :class:`_schema.Table` or :class:`.Sequence`
141 should have 'None' for its schema, even if the parent
142 :class:`_schema.MetaData` has specified a schema.
144 .. seealso::
146 :paramref:`_schema.MetaData.schema`
148 :paramref:`_schema.Table.schema`
150 :paramref:`.Sequence.schema`
152 """
154 NULL_UNSPECIFIED = 3
155 """Symbol indicating the "nullable" keyword was not passed to a Column.
157 This is used to distinguish between the use case of passing
158 ``nullable=None`` to a :class:`.Column`, which has special meaning
159 on some backends such as SQL Server.
161 """
164RETAIN_SCHEMA: Final[Literal[SchemaConst.RETAIN_SCHEMA]] = (
165 SchemaConst.RETAIN_SCHEMA
166)
167BLANK_SCHEMA: Final[Literal[SchemaConst.BLANK_SCHEMA]] = (
168 SchemaConst.BLANK_SCHEMA
169)
170NULL_UNSPECIFIED: Final[Literal[SchemaConst.NULL_UNSPECIFIED]] = (
171 SchemaConst.NULL_UNSPECIFIED
172)
175def _get_table_key(name: str, schema: Optional[str]) -> str:
176 if schema is None:
177 return name
178 else:
179 return schema + "." + name
182# this should really be in sql/util.py but we'd have to
183# break an import cycle
184def _copy_expression(
185 expression: ColumnElement[Any],
186 source_table: Optional[Table],
187 target_table: Optional[Table],
188) -> ColumnElement[Any]:
189 if source_table is None or target_table is None:
190 return expression
192 fixed_source_table = source_table
193 fixed_target_table = target_table
195 def replace(
196 element: ExternallyTraversible, **kw: Any
197 ) -> Optional[ExternallyTraversible]:
198 if (
199 isinstance(element, Column)
200 and element.table is fixed_source_table
201 and element.key in fixed_source_table.c
202 ):
203 return fixed_target_table.c[element.key]
204 else:
205 return None
207 return cast(
208 ColumnElement[Any],
209 visitors.replacement_traverse(expression, {}, replace),
210 )
213@inspection._self_inspects
214class SchemaItem(SchemaEventTarget, visitors.Visitable):
215 """Base class for items that define a database schema."""
217 __visit_name__ = "schema_item"
219 create_drop_stringify_dialect = "default"
221 def _init_items(self, *args: SchemaItem, **kw: Any) -> None:
222 """Initialize the list of child items for this SchemaItem."""
223 for item in args:
224 if item is not None:
225 try:
226 spwd = item._set_parent_with_dispatch
227 except AttributeError as err:
228 raise exc.ArgumentError(
229 "'SchemaItem' object, such as a 'Column' or a "
230 f"'Constraint' expected, got {item!r}"
231 ) from err
232 else:
233 spwd(self, **kw)
235 def __repr__(self) -> str:
236 return util.generic_repr(self, omit_kwarg=["info"])
238 @util.memoized_property
239 def info(self) -> _InfoType:
240 """Info dictionary associated with the object, allowing user-defined
241 data to be associated with this :class:`.SchemaItem`.
243 The dictionary is automatically generated when first accessed.
244 It can also be specified in the constructor of some objects,
245 such as :class:`_schema.Table` and :class:`_schema.Column`.
247 """
248 return {}
250 def _schema_item_copy(self, schema_item: _SI) -> _SI:
251 if "info" in self.__dict__:
252 schema_item.info = self.info.copy()
253 schema_item.dispatch._update(self.dispatch)
254 return schema_item
256 _use_schema_map = True
259class HasConditionalDDL:
260 """define a class that includes the :meth:`.HasConditionalDDL.ddl_if`
261 method, allowing for conditional rendering of DDL.
263 Currently applies to constraints and indexes.
265 .. versionadded:: 2.0
268 """
270 _ddl_if: Optional[ddl.DDLIf] = None
272 def ddl_if(
273 self,
274 dialect: Optional[str] = None,
275 callable_: Optional[ddl.DDLIfCallable] = None,
276 state: Optional[Any] = None,
277 ) -> Self:
278 r"""apply a conditional DDL rule to this schema item.
280 These rules work in a similar manner to the
281 :meth:`.ExecutableDDLElement.execute_if` callable, with the added
282 feature that the criteria may be checked within the DDL compilation
283 phase for a construct such as :class:`.CreateTable`.
284 :meth:`.HasConditionalDDL.ddl_if` currently applies towards the
285 :class:`.Index` construct as well as all :class:`.Constraint`
286 constructs.
288 :param dialect: string name of a dialect, or a tuple of string names
289 to indicate multiple dialect types.
291 :param callable\_: a callable that is constructed using the same form
292 as that described in
293 :paramref:`.ExecutableDDLElement.execute_if.callable_`.
295 :param state: any arbitrary object that will be passed to the
296 callable, if present.
298 .. versionadded:: 2.0
300 .. seealso::
302 :ref:`schema_ddl_ddl_if` - background and usage examples
305 """
306 self._ddl_if = ddl.DDLIf(dialect, callable_, state)
307 return self
310class HasSchemaAttr(SchemaItem):
311 """schema item that includes a top-level schema name"""
313 schema: Optional[str]
316class Table(
317 DialectKWArgs, HasSchemaAttr, TableClause, inspection.Inspectable["Table"]
318):
319 r"""Represent a table in a database.
321 e.g.::
323 mytable = Table(
324 "mytable", metadata,
325 Column('mytable_id', Integer, primary_key=True),
326 Column('value', String(50))
327 )
329 The :class:`_schema.Table`
330 object constructs a unique instance of itself based
331 on its name and optional schema name within the given
332 :class:`_schema.MetaData` object. Calling the :class:`_schema.Table`
333 constructor with the same name and same :class:`_schema.MetaData` argument
334 a second time will return the *same* :class:`_schema.Table`
335 object - in this way
336 the :class:`_schema.Table` constructor acts as a registry function.
338 .. seealso::
340 :ref:`metadata_describing` - Introduction to database metadata
342 """
344 __visit_name__ = "table"
346 if TYPE_CHECKING:
348 @util.ro_non_memoized_property
349 def primary_key(self) -> PrimaryKeyConstraint: ...
351 @util.ro_non_memoized_property
352 def foreign_keys(self) -> Set[ForeignKey]: ...
354 _columns: DedupeColumnCollection[Column[Any]]
356 _sentinel_column: Optional[Column[Any]]
358 constraints: Set[Constraint]
359 """A collection of all :class:`_schema.Constraint` objects associated with
360 this :class:`_schema.Table`.
362 Includes :class:`_schema.PrimaryKeyConstraint`,
363 :class:`_schema.ForeignKeyConstraint`, :class:`_schema.UniqueConstraint`,
364 :class:`_schema.CheckConstraint`. A separate collection
365 :attr:`_schema.Table.foreign_key_constraints` refers to the collection
366 of all :class:`_schema.ForeignKeyConstraint` objects, and the
367 :attr:`_schema.Table.primary_key` attribute refers to the single
368 :class:`_schema.PrimaryKeyConstraint` associated with the
369 :class:`_schema.Table`.
371 .. seealso::
373 :attr:`_schema.Table.constraints`
375 :attr:`_schema.Table.primary_key`
377 :attr:`_schema.Table.foreign_key_constraints`
379 :attr:`_schema.Table.indexes`
381 :class:`_reflection.Inspector`
384 """
386 indexes: Set[Index]
387 """A collection of all :class:`_schema.Index` objects associated with this
388 :class:`_schema.Table`.
390 .. seealso::
392 :meth:`_reflection.Inspector.get_indexes`
394 """
396 if TYPE_CHECKING:
398 @util.ro_non_memoized_property
399 def columns(self) -> ReadOnlyColumnCollection[str, Column[Any]]: ...
401 @util.ro_non_memoized_property
402 def exported_columns(
403 self,
404 ) -> ReadOnlyColumnCollection[str, Column[Any]]: ...
406 @util.ro_non_memoized_property
407 def c(self) -> ReadOnlyColumnCollection[str, Column[Any]]: ...
409 def _gen_cache_key(
410 self, anon_map: anon_map, bindparams: List[BindParameter[Any]]
411 ) -> Tuple[Any, ...]:
412 if self._annotations:
413 return (self,) + self._annotations_cache_key
414 else:
415 return (self,)
417 if not typing.TYPE_CHECKING:
418 # typing tools seem to be inconsistent in how they handle
419 # __new__, so suggest this pattern for classes that use
420 # __new__. apply typing to the __init__ method normally
421 @util.deprecated_params(
422 mustexist=(
423 "1.4",
424 "Deprecated alias of :paramref:`_schema.Table.must_exist`",
425 ),
426 )
427 def __new__(cls, *args: Any, **kw: Any) -> Any:
428 return cls._new(*args, **kw)
430 @classmethod
431 def _new(cls, *args: Any, **kw: Any) -> Any:
432 if not args and not kw:
433 # python3k pickle seems to call this
434 return object.__new__(cls)
436 try:
437 name, metadata, args = args[0], args[1], args[2:]
438 except IndexError:
439 raise TypeError(
440 "Table() takes at least two positional-only "
441 "arguments 'name' and 'metadata'"
442 )
444 schema = kw.get("schema", None)
445 if schema is None:
446 schema = metadata.schema
447 elif schema is BLANK_SCHEMA:
448 schema = None
449 keep_existing = kw.get("keep_existing", False)
450 extend_existing = kw.get("extend_existing", False)
452 if keep_existing and extend_existing:
453 msg = "keep_existing and extend_existing are mutually exclusive."
454 raise exc.ArgumentError(msg)
456 must_exist = kw.pop("must_exist", kw.pop("mustexist", False))
457 key = _get_table_key(name, schema)
458 if key in metadata.tables:
459 if not keep_existing and not extend_existing and bool(args):
460 raise exc.InvalidRequestError(
461 f"Table '{key}' is already defined for this MetaData "
462 "instance. Specify 'extend_existing=True' "
463 "to redefine "
464 "options and columns on an "
465 "existing Table object."
466 )
467 table = metadata.tables[key]
468 if extend_existing:
469 table._init_existing(*args, **kw)
470 return table
471 else:
472 if must_exist:
473 raise exc.InvalidRequestError(f"Table '{key}' not defined")
474 table = object.__new__(cls)
475 table.dispatch.before_parent_attach(table, metadata)
476 metadata._add_table(name, schema, table)
477 try:
478 table.__init__(name, metadata, *args, _no_init=False, **kw)
479 table.dispatch.after_parent_attach(table, metadata)
480 return table
481 except Exception:
482 with util.safe_reraise():
483 metadata._remove_table(name, schema)
485 def __init__(
486 self,
487 name: str,
488 metadata: MetaData,
489 *args: SchemaItem,
490 schema: Optional[Union[str, Literal[SchemaConst.BLANK_SCHEMA]]] = None,
491 quote: Optional[bool] = None,
492 quote_schema: Optional[bool] = None,
493 autoload_with: Optional[Union[Engine, Connection]] = None,
494 autoload_replace: bool = True,
495 keep_existing: bool = False,
496 extend_existing: bool = False,
497 resolve_fks: bool = True,
498 include_columns: Optional[Collection[str]] = None,
499 implicit_returning: bool = True,
500 comment: Optional[str] = None,
501 info: Optional[Dict[Any, Any]] = None,
502 listeners: Optional[
503 _typing_Sequence[Tuple[str, Callable[..., Any]]]
504 ] = None,
505 prefixes: Optional[_typing_Sequence[str]] = None,
506 # used internally in the metadata.reflect() process
507 _extend_on: Optional[Set[Table]] = None,
508 # used by __new__ to bypass __init__
509 _no_init: bool = True,
510 # dialect-specific keyword args
511 **kw: Any,
512 ) -> None:
513 r"""Constructor for :class:`_schema.Table`.
516 :param name: The name of this table as represented in the database.
518 The table name, along with the value of the ``schema`` parameter,
519 forms a key which uniquely identifies this :class:`_schema.Table`
520 within
521 the owning :class:`_schema.MetaData` collection.
522 Additional calls to :class:`_schema.Table` with the same name,
523 metadata,
524 and schema name will return the same :class:`_schema.Table` object.
526 Names which contain no upper case characters
527 will be treated as case insensitive names, and will not be quoted
528 unless they are a reserved word or contain special characters.
529 A name with any number of upper case characters is considered
530 to be case sensitive, and will be sent as quoted.
532 To enable unconditional quoting for the table name, specify the flag
533 ``quote=True`` to the constructor, or use the :class:`.quoted_name`
534 construct to specify the name.
536 :param metadata: a :class:`_schema.MetaData`
537 object which will contain this
538 table. The metadata is used as a point of association of this table
539 with other tables which are referenced via foreign key. It also
540 may be used to associate this table with a particular
541 :class:`.Connection` or :class:`.Engine`.
543 :param \*args: Additional positional arguments are used primarily
544 to add the list of :class:`_schema.Column`
545 objects contained within this
546 table. Similar to the style of a CREATE TABLE statement, other
547 :class:`.SchemaItem` constructs may be added here, including
548 :class:`.PrimaryKeyConstraint`, and
549 :class:`_schema.ForeignKeyConstraint`.
551 :param autoload_replace: Defaults to ``True``; when using
552 :paramref:`_schema.Table.autoload_with`
553 in conjunction with :paramref:`_schema.Table.extend_existing`,
554 indicates
555 that :class:`_schema.Column` objects present in the already-existing
556 :class:`_schema.Table`
557 object should be replaced with columns of the same
558 name retrieved from the autoload process. When ``False``, columns
559 already present under existing names will be omitted from the
560 reflection process.
562 Note that this setting does not impact :class:`_schema.Column` objects
563 specified programmatically within the call to :class:`_schema.Table`
564 that
565 also is autoloading; those :class:`_schema.Column` objects will always
566 replace existing columns of the same name when
567 :paramref:`_schema.Table.extend_existing` is ``True``.
569 .. seealso::
571 :paramref:`_schema.Table.autoload_with`
573 :paramref:`_schema.Table.extend_existing`
575 :param autoload_with: An :class:`_engine.Engine` or
576 :class:`_engine.Connection` object,
577 or a :class:`_reflection.Inspector` object as returned by
578 :func:`_sa.inspect`
579 against one, with which this :class:`_schema.Table`
580 object will be reflected.
581 When set to a non-None value, the autoload process will take place
582 for this table against the given engine or connection.
584 .. seealso::
586 :ref:`metadata_reflection_toplevel`
588 :meth:`_events.DDLEvents.column_reflect`
590 :ref:`metadata_reflection_dbagnostic_types`
592 :param extend_existing: When ``True``, indicates that if this
593 :class:`_schema.Table` is already present in the given
594 :class:`_schema.MetaData`,
595 apply further arguments within the constructor to the existing
596 :class:`_schema.Table`.
598 If :paramref:`_schema.Table.extend_existing` or
599 :paramref:`_schema.Table.keep_existing` are not set,
600 and the given name
601 of the new :class:`_schema.Table` refers to a :class:`_schema.Table`
602 that is
603 already present in the target :class:`_schema.MetaData` collection,
604 and
605 this :class:`_schema.Table`
606 specifies additional columns or other constructs
607 or flags that modify the table's state, an
608 error is raised. The purpose of these two mutually-exclusive flags
609 is to specify what action should be taken when a
610 :class:`_schema.Table`
611 is specified that matches an existing :class:`_schema.Table`,
612 yet specifies
613 additional constructs.
615 :paramref:`_schema.Table.extend_existing`
616 will also work in conjunction
617 with :paramref:`_schema.Table.autoload_with` to run a new reflection
618 operation against the database, even if a :class:`_schema.Table`
619 of the same name is already present in the target
620 :class:`_schema.MetaData`; newly reflected :class:`_schema.Column`
621 objects
622 and other options will be added into the state of the
623 :class:`_schema.Table`, potentially overwriting existing columns
624 and options of the same name.
626 As is always the case with :paramref:`_schema.Table.autoload_with`,
627 :class:`_schema.Column` objects can be specified in the same
628 :class:`_schema.Table`
629 constructor, which will take precedence. Below, the existing
630 table ``mytable`` will be augmented with :class:`_schema.Column`
631 objects
632 both reflected from the database, as well as the given
633 :class:`_schema.Column`
634 named "y"::
636 Table("mytable", metadata,
637 Column('y', Integer),
638 extend_existing=True,
639 autoload_with=engine
640 )
642 .. seealso::
644 :paramref:`_schema.Table.autoload_with`
646 :paramref:`_schema.Table.autoload_replace`
648 :paramref:`_schema.Table.keep_existing`
651 :param implicit_returning: True by default - indicates that
652 RETURNING can be used, typically by the ORM, in order to fetch
653 server-generated values such as primary key values and
654 server side defaults, on those backends which support RETURNING.
656 In modern SQLAlchemy there is generally no reason to alter this
657 setting, except for some backend specific cases
658 (see :ref:`mssql_triggers` in the SQL Server dialect documentation
659 for one such example).
661 :param include_columns: A list of strings indicating a subset of
662 columns to be loaded via the ``autoload`` operation; table columns who
663 aren't present in this list will not be represented on the resulting
664 ``Table`` object. Defaults to ``None`` which indicates all columns
665 should be reflected.
667 :param resolve_fks: Whether or not to reflect :class:`_schema.Table`
668 objects
669 related to this one via :class:`_schema.ForeignKey` objects, when
670 :paramref:`_schema.Table.autoload_with` is
671 specified. Defaults to True. Set to False to disable reflection of
672 related tables as :class:`_schema.ForeignKey`
673 objects are encountered; may be
674 used either to save on SQL calls or to avoid issues with related tables
675 that can't be accessed. Note that if a related table is already present
676 in the :class:`_schema.MetaData` collection, or becomes present later,
677 a
678 :class:`_schema.ForeignKey` object associated with this
679 :class:`_schema.Table` will
680 resolve to that table normally.
682 .. versionadded:: 1.3
684 .. seealso::
686 :paramref:`.MetaData.reflect.resolve_fks`
689 :param info: Optional data dictionary which will be populated into the
690 :attr:`.SchemaItem.info` attribute of this object.
692 :param keep_existing: When ``True``, indicates that if this Table
693 is already present in the given :class:`_schema.MetaData`, ignore
694 further arguments within the constructor to the existing
695 :class:`_schema.Table`, and return the :class:`_schema.Table`
696 object as
697 originally created. This is to allow a function that wishes
698 to define a new :class:`_schema.Table` on first call, but on
699 subsequent calls will return the same :class:`_schema.Table`,
700 without any of the declarations (particularly constraints)
701 being applied a second time.
703 If :paramref:`_schema.Table.extend_existing` or
704 :paramref:`_schema.Table.keep_existing` are not set,
705 and the given name
706 of the new :class:`_schema.Table` refers to a :class:`_schema.Table`
707 that is
708 already present in the target :class:`_schema.MetaData` collection,
709 and
710 this :class:`_schema.Table`
711 specifies additional columns or other constructs
712 or flags that modify the table's state, an
713 error is raised. The purpose of these two mutually-exclusive flags
714 is to specify what action should be taken when a
715 :class:`_schema.Table`
716 is specified that matches an existing :class:`_schema.Table`,
717 yet specifies
718 additional constructs.
720 .. seealso::
722 :paramref:`_schema.Table.extend_existing`
724 :param listeners: A list of tuples of the form ``(<eventname>, <fn>)``
725 which will be passed to :func:`.event.listen` upon construction.
726 This alternate hook to :func:`.event.listen` allows the establishment
727 of a listener function specific to this :class:`_schema.Table` before
728 the "autoload" process begins. Historically this has been intended
729 for use with the :meth:`.DDLEvents.column_reflect` event, however
730 note that this event hook may now be associated with the
731 :class:`_schema.MetaData` object directly::
733 def listen_for_reflect(table, column_info):
734 "handle the column reflection event"
735 # ...
737 t = Table(
738 'sometable',
739 autoload_with=engine,
740 listeners=[
741 ('column_reflect', listen_for_reflect)
742 ])
744 .. seealso::
746 :meth:`_events.DDLEvents.column_reflect`
748 :param must_exist: When ``True``, indicates that this Table must already
749 be present in the given :class:`_schema.MetaData` collection, else
750 an exception is raised.
752 :param prefixes:
753 A list of strings to insert after CREATE in the CREATE TABLE
754 statement. They will be separated by spaces.
756 :param quote: Force quoting of this table's name on or off, corresponding
757 to ``True`` or ``False``. When left at its default of ``None``,
758 the column identifier will be quoted according to whether the name is
759 case sensitive (identifiers with at least one upper case character are
760 treated as case sensitive), or if it's a reserved word. This flag
761 is only needed to force quoting of a reserved word which is not known
762 by the SQLAlchemy dialect.
764 .. note:: setting this flag to ``False`` will not provide
765 case-insensitive behavior for table reflection; table reflection
766 will always search for a mixed-case name in a case sensitive
767 fashion. Case insensitive names are specified in SQLAlchemy only
768 by stating the name with all lower case characters.
770 :param quote_schema: same as 'quote' but applies to the schema identifier.
772 :param schema: The schema name for this table, which is required if
773 the table resides in a schema other than the default selected schema
774 for the engine's database connection. Defaults to ``None``.
776 If the owning :class:`_schema.MetaData` of this :class:`_schema.Table`
777 specifies its
778 own :paramref:`_schema.MetaData.schema` parameter,
779 then that schema name will
780 be applied to this :class:`_schema.Table`
781 if the schema parameter here is set
782 to ``None``. To set a blank schema name on a :class:`_schema.Table`
783 that
784 would otherwise use the schema set on the owning
785 :class:`_schema.MetaData`,
786 specify the special symbol :attr:`.BLANK_SCHEMA`.
788 The quoting rules for the schema name are the same as those for the
789 ``name`` parameter, in that quoting is applied for reserved words or
790 case-sensitive names; to enable unconditional quoting for the schema
791 name, specify the flag ``quote_schema=True`` to the constructor, or use
792 the :class:`.quoted_name` construct to specify the name.
794 :param comment: Optional string that will render an SQL comment on table
795 creation.
797 .. versionadded:: 1.2 Added the :paramref:`_schema.Table.comment`
798 parameter
799 to :class:`_schema.Table`.
801 :param \**kw: Additional keyword arguments not mentioned above are
802 dialect specific, and passed in the form ``<dialectname>_<argname>``.
803 See the documentation regarding an individual dialect at
804 :ref:`dialect_toplevel` for detail on documented arguments.
806 """ # noqa: E501
807 if _no_init:
808 # don't run __init__ from __new__ by default;
809 # __new__ has a specific place that __init__ is called
810 return
812 super().__init__(quoted_name(name, quote))
813 self.metadata = metadata
815 if schema is None:
816 self.schema = metadata.schema
817 elif schema is BLANK_SCHEMA:
818 self.schema = None
819 else:
820 quote_schema = quote_schema
821 assert isinstance(schema, str)
822 self.schema = quoted_name(schema, quote_schema)
824 self._sentinel_column = None
826 self.indexes = set()
827 self.constraints = set()
828 PrimaryKeyConstraint(
829 _implicit_generated=True
830 )._set_parent_with_dispatch(self)
831 self.foreign_keys = set() # type: ignore
832 self._extra_dependencies: Set[Table] = set()
833 if self.schema is not None:
834 self.fullname = "%s.%s" % (self.schema, self.name)
835 else:
836 self.fullname = self.name
838 self.implicit_returning = implicit_returning
839 _reflect_info = kw.pop("_reflect_info", None)
841 self.comment = comment
843 if info is not None:
844 self.info = info
846 if listeners is not None:
847 for evt, fn in listeners:
848 event.listen(self, evt, fn)
850 self._prefixes = prefixes if prefixes else []
852 self._extra_kwargs(**kw)
854 # load column definitions from the database if 'autoload' is defined
855 # we do it after the table is in the singleton dictionary to support
856 # circular foreign keys
857 if autoload_with is not None:
858 self._autoload(
859 metadata,
860 autoload_with,
861 include_columns,
862 _extend_on=_extend_on,
863 _reflect_info=_reflect_info,
864 resolve_fks=resolve_fks,
865 )
867 # initialize all the column, etc. objects. done after reflection to
868 # allow user-overrides
870 self._init_items(
871 *args,
872 allow_replacements=extend_existing
873 or keep_existing
874 or autoload_with,
875 all_names={},
876 )
878 def _autoload(
879 self,
880 metadata: MetaData,
881 autoload_with: Union[Engine, Connection],
882 include_columns: Optional[Collection[str]],
883 exclude_columns: Collection[str] = (),
884 resolve_fks: bool = True,
885 _extend_on: Optional[Set[Table]] = None,
886 _reflect_info: _ReflectionInfo | None = None,
887 ) -> None:
888 insp = inspection.inspect(autoload_with)
889 with insp._inspection_context() as conn_insp:
890 conn_insp.reflect_table(
891 self,
892 include_columns,
893 exclude_columns,
894 resolve_fks,
895 _extend_on=_extend_on,
896 _reflect_info=_reflect_info,
897 )
899 @property
900 def _sorted_constraints(self) -> List[Constraint]:
901 """Return the set of constraints as a list, sorted by creation
902 order.
904 """
906 return sorted(self.constraints, key=lambda c: c._creation_order)
908 @property
909 def foreign_key_constraints(self) -> Set[ForeignKeyConstraint]:
910 """:class:`_schema.ForeignKeyConstraint` objects referred to by this
911 :class:`_schema.Table`.
913 This list is produced from the collection of
914 :class:`_schema.ForeignKey`
915 objects currently associated.
918 .. seealso::
920 :attr:`_schema.Table.constraints`
922 :attr:`_schema.Table.foreign_keys`
924 :attr:`_schema.Table.indexes`
926 """
927 return {
928 fkc.constraint
929 for fkc in self.foreign_keys
930 if fkc.constraint is not None
931 }
933 def _init_existing(self, *args: Any, **kwargs: Any) -> None:
934 autoload_with = kwargs.pop("autoload_with", None)
935 autoload = kwargs.pop("autoload", autoload_with is not None)
936 autoload_replace = kwargs.pop("autoload_replace", True)
937 schema = kwargs.pop("schema", None)
938 _extend_on = kwargs.pop("_extend_on", None)
939 _reflect_info = kwargs.pop("_reflect_info", None)
941 # these arguments are only used with _init()
942 extend_existing = kwargs.pop("extend_existing", False)
943 keep_existing = kwargs.pop("keep_existing", False)
945 assert extend_existing
946 assert not keep_existing
948 if schema and schema != self.schema:
949 raise exc.ArgumentError(
950 f"Can't change schema of existing table "
951 f"from '{self.schema}' to '{schema}'",
952 )
954 include_columns = kwargs.pop("include_columns", None)
955 if include_columns is not None:
956 for c in self.c:
957 if c.name not in include_columns:
958 self._columns.remove(c)
960 resolve_fks = kwargs.pop("resolve_fks", True)
962 for key in ("quote", "quote_schema"):
963 if key in kwargs:
964 raise exc.ArgumentError(
965 "Can't redefine 'quote' or 'quote_schema' arguments"
966 )
968 # update `self` with these kwargs, if provided
969 self.comment = kwargs.pop("comment", self.comment)
970 self.implicit_returning = kwargs.pop(
971 "implicit_returning", self.implicit_returning
972 )
973 self.info = kwargs.pop("info", self.info)
975 exclude_columns: _typing_Sequence[str]
977 if autoload:
978 if not autoload_replace:
979 # don't replace columns already present.
980 # we'd like to do this for constraints also however we don't
981 # have simple de-duping for unnamed constraints.
982 exclude_columns = [c.name for c in self.c]
983 else:
984 exclude_columns = ()
985 self._autoload(
986 self.metadata,
987 autoload_with,
988 include_columns,
989 exclude_columns,
990 resolve_fks,
991 _extend_on=_extend_on,
992 _reflect_info=_reflect_info,
993 )
995 all_names = {c.name: c for c in self.c}
996 self._extra_kwargs(**kwargs)
997 self._init_items(*args, allow_replacements=True, all_names=all_names)
999 def _extra_kwargs(self, **kwargs: Any) -> None:
1000 self._validate_dialect_kwargs(kwargs)
1002 def _init_collections(self) -> None:
1003 pass
1005 def _reset_exported(self) -> None:
1006 pass
1008 @util.ro_non_memoized_property
1009 def _autoincrement_column(self) -> Optional[Column[int]]:
1010 return self.primary_key._autoincrement_column
1012 @util.ro_memoized_property
1013 def _sentinel_column_characteristics(
1014 self,
1015 ) -> _SentinelColumnCharacterization:
1016 """determine a candidate column (or columns, in case of a client
1017 generated composite primary key) which can be used as an
1018 "insert sentinel" for an INSERT statement.
1020 The returned structure, :class:`_SentinelColumnCharacterization`,
1021 includes all the details needed by :class:`.Dialect` and
1022 :class:`.SQLCompiler` to determine if these column(s) can be used
1023 as an INSERT..RETURNING sentinel for a particular database
1024 dialect.
1026 .. versionadded:: 2.0.10
1028 """
1030 sentinel_is_explicit = False
1031 sentinel_is_autoinc = False
1032 the_sentinel: Optional[_typing_Sequence[Column[Any]]] = None
1034 # see if a column was explicitly marked "insert_sentinel=True".
1035 explicit_sentinel_col = self._sentinel_column
1037 if explicit_sentinel_col is not None:
1038 the_sentinel = (explicit_sentinel_col,)
1039 sentinel_is_explicit = True
1041 autoinc_col = self._autoincrement_column
1042 if sentinel_is_explicit and explicit_sentinel_col is autoinc_col:
1043 assert autoinc_col is not None
1044 sentinel_is_autoinc = True
1045 elif explicit_sentinel_col is None and autoinc_col is not None:
1046 the_sentinel = (autoinc_col,)
1047 sentinel_is_autoinc = True
1049 default_characterization = _SentinelDefaultCharacterization.UNKNOWN
1051 if the_sentinel:
1052 the_sentinel_zero = the_sentinel[0]
1053 if the_sentinel_zero.identity:
1054 if the_sentinel_zero.identity._increment_is_negative:
1055 if sentinel_is_explicit:
1056 raise exc.InvalidRequestError(
1057 "Can't use IDENTITY default with negative "
1058 "increment as an explicit sentinel column"
1059 )
1060 else:
1061 if sentinel_is_autoinc:
1062 autoinc_col = None
1063 sentinel_is_autoinc = False
1064 the_sentinel = None
1065 else:
1066 default_characterization = (
1067 _SentinelDefaultCharacterization.IDENTITY
1068 )
1069 elif (
1070 the_sentinel_zero.default is None
1071 and the_sentinel_zero.server_default is None
1072 ):
1073 if the_sentinel_zero.nullable:
1074 raise exc.InvalidRequestError(
1075 f"Column {the_sentinel_zero} has been marked as a "
1076 "sentinel "
1077 "column with no default generation function; it "
1078 "at least needs to be marked nullable=False assuming "
1079 "user-populated sentinel values will be used."
1080 )
1081 default_characterization = (
1082 _SentinelDefaultCharacterization.NONE
1083 )
1084 elif the_sentinel_zero.default is not None:
1085 if the_sentinel_zero.default.is_sentinel:
1086 default_characterization = (
1087 _SentinelDefaultCharacterization.SENTINEL_DEFAULT
1088 )
1089 elif default_is_sequence(the_sentinel_zero.default):
1090 if the_sentinel_zero.default._increment_is_negative:
1091 if sentinel_is_explicit:
1092 raise exc.InvalidRequestError(
1093 "Can't use SEQUENCE default with negative "
1094 "increment as an explicit sentinel column"
1095 )
1096 else:
1097 if sentinel_is_autoinc:
1098 autoinc_col = None
1099 sentinel_is_autoinc = False
1100 the_sentinel = None
1102 default_characterization = (
1103 _SentinelDefaultCharacterization.SEQUENCE
1104 )
1105 elif the_sentinel_zero.default.is_callable:
1106 default_characterization = (
1107 _SentinelDefaultCharacterization.CLIENTSIDE
1108 )
1109 elif the_sentinel_zero.server_default is not None:
1110 if sentinel_is_explicit:
1111 raise exc.InvalidRequestError(
1112 f"Column {the_sentinel[0]} can't be a sentinel column "
1113 "because it uses an explicit server side default "
1114 "that's not the Identity() default."
1115 )
1117 default_characterization = (
1118 _SentinelDefaultCharacterization.SERVERSIDE
1119 )
1121 if the_sentinel is None and self.primary_key:
1122 assert autoinc_col is None
1124 # determine for non-autoincrement pk if all elements are
1125 # client side
1126 for _pkc in self.primary_key:
1127 if _pkc.server_default is not None or (
1128 _pkc.default and not _pkc.default.is_callable
1129 ):
1130 break
1131 else:
1132 the_sentinel = tuple(self.primary_key)
1133 default_characterization = (
1134 _SentinelDefaultCharacterization.CLIENTSIDE
1135 )
1137 return _SentinelColumnCharacterization(
1138 the_sentinel,
1139 sentinel_is_explicit,
1140 sentinel_is_autoinc,
1141 default_characterization,
1142 )
1144 @property
1145 def autoincrement_column(self) -> Optional[Column[int]]:
1146 """Returns the :class:`.Column` object which currently represents
1147 the "auto increment" column, if any, else returns None.
1149 This is based on the rules for :class:`.Column` as defined by the
1150 :paramref:`.Column.autoincrement` parameter, which generally means the
1151 column within a single integer column primary key constraint that is
1152 not constrained by a foreign key. If the table does not have such
1153 a primary key constraint, then there's no "autoincrement" column.
1154 A :class:`.Table` may have only one column defined as the
1155 "autoincrement" column.
1157 .. versionadded:: 2.0.4
1159 .. seealso::
1161 :paramref:`.Column.autoincrement`
1163 """
1164 return self._autoincrement_column
1166 @property
1167 def key(self) -> str:
1168 """Return the 'key' for this :class:`_schema.Table`.
1170 This value is used as the dictionary key within the
1171 :attr:`_schema.MetaData.tables` collection. It is typically the same
1172 as that of :attr:`_schema.Table.name` for a table with no
1173 :attr:`_schema.Table.schema`
1174 set; otherwise it is typically of the form
1175 ``schemaname.tablename``.
1177 """
1178 return _get_table_key(self.name, self.schema)
1180 def __repr__(self) -> str:
1181 return "Table(%s)" % ", ".join(
1182 [repr(self.name)]
1183 + [repr(self.metadata)]
1184 + [repr(x) for x in self.columns]
1185 + ["%s=%s" % (k, repr(getattr(self, k))) for k in ["schema"]]
1186 )
1188 def __str__(self) -> str:
1189 return _get_table_key(self.description, self.schema)
1191 def add_is_dependent_on(self, table: Table) -> None:
1192 """Add a 'dependency' for this Table.
1194 This is another Table object which must be created
1195 first before this one can, or dropped after this one.
1197 Usually, dependencies between tables are determined via
1198 ForeignKey objects. However, for other situations that
1199 create dependencies outside of foreign keys (rules, inheriting),
1200 this method can manually establish such a link.
1202 """
1203 self._extra_dependencies.add(table)
1205 def append_column(
1206 self, column: ColumnClause[Any], replace_existing: bool = False
1207 ) -> None:
1208 """Append a :class:`_schema.Column` to this :class:`_schema.Table`.
1210 The "key" of the newly added :class:`_schema.Column`, i.e. the
1211 value of its ``.key`` attribute, will then be available
1212 in the ``.c`` collection of this :class:`_schema.Table`, and the
1213 column definition will be included in any CREATE TABLE, SELECT,
1214 UPDATE, etc. statements generated from this :class:`_schema.Table`
1215 construct.
1217 Note that this does **not** change the definition of the table
1218 as it exists within any underlying database, assuming that
1219 table has already been created in the database. Relational
1220 databases support the addition of columns to existing tables
1221 using the SQL ALTER command, which would need to be
1222 emitted for an already-existing table that doesn't contain
1223 the newly added column.
1225 :param replace_existing: When ``True``, allows replacing existing
1226 columns. When ``False``, the default, an warning will be raised
1227 if a column with the same ``.key`` already exists. A future
1228 version of sqlalchemy will instead rise a warning.
1230 .. versionadded:: 1.4.0
1231 """
1233 try:
1234 column._set_parent_with_dispatch(
1235 self,
1236 allow_replacements=replace_existing,
1237 all_names={c.name: c for c in self.c},
1238 )
1239 except exc.DuplicateColumnError as de:
1240 raise exc.DuplicateColumnError(
1241 f"{de.args[0]} Specify replace_existing=True to "
1242 "Table.append_column() to replace an "
1243 "existing column."
1244 ) from de
1246 def append_constraint(self, constraint: Union[Index, Constraint]) -> None:
1247 """Append a :class:`_schema.Constraint` to this
1248 :class:`_schema.Table`.
1250 This has the effect of the constraint being included in any
1251 future CREATE TABLE statement, assuming specific DDL creation
1252 events have not been associated with the given
1253 :class:`_schema.Constraint` object.
1255 Note that this does **not** produce the constraint within the
1256 relational database automatically, for a table that already exists
1257 in the database. To add a constraint to an
1258 existing relational database table, the SQL ALTER command must
1259 be used. SQLAlchemy also provides the
1260 :class:`.AddConstraint` construct which can produce this SQL when
1261 invoked as an executable clause.
1263 """
1265 constraint._set_parent_with_dispatch(self)
1267 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
1268 metadata = parent
1269 assert isinstance(metadata, MetaData)
1270 metadata._add_table(self.name, self.schema, self)
1271 self.metadata = metadata
1273 def create(self, bind: _CreateDropBind, checkfirst: bool = False) -> None:
1274 """Issue a ``CREATE`` statement for this
1275 :class:`_schema.Table`, using the given
1276 :class:`.Connection` or :class:`.Engine`
1277 for connectivity.
1279 .. seealso::
1281 :meth:`_schema.MetaData.create_all`.
1283 """
1285 bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
1287 def drop(self, bind: _CreateDropBind, checkfirst: bool = False) -> None:
1288 """Issue a ``DROP`` statement for this
1289 :class:`_schema.Table`, using the given
1290 :class:`.Connection` or :class:`.Engine` for connectivity.
1292 .. seealso::
1294 :meth:`_schema.MetaData.drop_all`.
1296 """
1297 bind._run_ddl_visitor(ddl.SchemaDropper, self, checkfirst=checkfirst)
1299 @util.deprecated(
1300 "1.4",
1301 ":meth:`_schema.Table.tometadata` is renamed to "
1302 ":meth:`_schema.Table.to_metadata`",
1303 )
1304 def tometadata(
1305 self,
1306 metadata: MetaData,
1307 schema: Union[str, Literal[SchemaConst.RETAIN_SCHEMA]] = RETAIN_SCHEMA,
1308 referred_schema_fn: Optional[
1309 Callable[
1310 [Table, Optional[str], ForeignKeyConstraint, Optional[str]],
1311 Optional[str],
1312 ]
1313 ] = None,
1314 name: Optional[str] = None,
1315 ) -> Table:
1316 """Return a copy of this :class:`_schema.Table`
1317 associated with a different
1318 :class:`_schema.MetaData`.
1320 See :meth:`_schema.Table.to_metadata` for a full description.
1322 """
1323 return self.to_metadata(
1324 metadata,
1325 schema=schema,
1326 referred_schema_fn=referred_schema_fn,
1327 name=name,
1328 )
1330 def to_metadata(
1331 self,
1332 metadata: MetaData,
1333 schema: Union[str, Literal[SchemaConst.RETAIN_SCHEMA]] = RETAIN_SCHEMA,
1334 referred_schema_fn: Optional[
1335 Callable[
1336 [Table, Optional[str], ForeignKeyConstraint, Optional[str]],
1337 Optional[str],
1338 ]
1339 ] = None,
1340 name: Optional[str] = None,
1341 ) -> Table:
1342 """Return a copy of this :class:`_schema.Table` associated with a
1343 different :class:`_schema.MetaData`.
1345 E.g.::
1347 m1 = MetaData()
1349 user = Table('user', m1, Column('id', Integer, primary_key=True))
1351 m2 = MetaData()
1352 user_copy = user.to_metadata(m2)
1354 .. versionchanged:: 1.4 The :meth:`_schema.Table.to_metadata` function
1355 was renamed from :meth:`_schema.Table.tometadata`.
1358 :param metadata: Target :class:`_schema.MetaData` object,
1359 into which the
1360 new :class:`_schema.Table` object will be created.
1362 :param schema: optional string name indicating the target schema.
1363 Defaults to the special symbol :attr:`.RETAIN_SCHEMA` which indicates
1364 that no change to the schema name should be made in the new
1365 :class:`_schema.Table`. If set to a string name, the new
1366 :class:`_schema.Table`
1367 will have this new name as the ``.schema``. If set to ``None``, the
1368 schema will be set to that of the schema set on the target
1369 :class:`_schema.MetaData`, which is typically ``None`` as well,
1370 unless
1371 set explicitly::
1373 m2 = MetaData(schema='newschema')
1375 # user_copy_one will have "newschema" as the schema name
1376 user_copy_one = user.to_metadata(m2, schema=None)
1378 m3 = MetaData() # schema defaults to None
1380 # user_copy_two will have None as the schema name
1381 user_copy_two = user.to_metadata(m3, schema=None)
1383 :param referred_schema_fn: optional callable which can be supplied
1384 in order to provide for the schema name that should be assigned
1385 to the referenced table of a :class:`_schema.ForeignKeyConstraint`.
1386 The callable accepts this parent :class:`_schema.Table`, the
1387 target schema that we are changing to, the
1388 :class:`_schema.ForeignKeyConstraint` object, and the existing
1389 "target schema" of that constraint. The function should return the
1390 string schema name that should be applied. To reset the schema
1391 to "none", return the symbol :data:`.BLANK_SCHEMA`. To effect no
1392 change, return ``None`` or :data:`.RETAIN_SCHEMA`.
1394 .. versionchanged:: 1.4.33 The ``referred_schema_fn`` function
1395 may return the :data:`.BLANK_SCHEMA` or :data:`.RETAIN_SCHEMA`
1396 symbols.
1398 E.g.::
1400 def referred_schema_fn(table, to_schema,
1401 constraint, referred_schema):
1402 if referred_schema == 'base_tables':
1403 return referred_schema
1404 else:
1405 return to_schema
1407 new_table = table.to_metadata(m2, schema="alt_schema",
1408 referred_schema_fn=referred_schema_fn)
1410 :param name: optional string name indicating the target table name.
1411 If not specified or None, the table name is retained. This allows
1412 a :class:`_schema.Table` to be copied to the same
1413 :class:`_schema.MetaData` target
1414 with a new name.
1416 """
1417 if name is None:
1418 name = self.name
1420 actual_schema: Optional[str]
1422 if schema is RETAIN_SCHEMA:
1423 actual_schema = self.schema
1424 elif schema is None:
1425 actual_schema = metadata.schema
1426 else:
1427 actual_schema = schema
1428 key = _get_table_key(name, actual_schema)
1429 if key in metadata.tables:
1430 util.warn(
1431 f"Table '{self.description}' already exists within the given "
1432 "MetaData - not copying."
1433 )
1434 return metadata.tables[key]
1436 args = []
1437 for col in self.columns:
1438 args.append(col._copy(schema=actual_schema, _to_metadata=metadata))
1439 table = Table(
1440 name,
1441 metadata,
1442 schema=actual_schema,
1443 comment=self.comment,
1444 *args,
1445 **self.kwargs,
1446 )
1447 for const in self.constraints:
1448 if isinstance(const, ForeignKeyConstraint):
1449 referred_schema = const._referred_schema
1450 if referred_schema_fn:
1451 fk_constraint_schema = referred_schema_fn(
1452 self, actual_schema, const, referred_schema
1453 )
1454 else:
1455 fk_constraint_schema = (
1456 actual_schema
1457 if referred_schema == self.schema
1458 else None
1459 )
1460 table.append_constraint(
1461 const._copy(
1462 schema=fk_constraint_schema, target_table=table
1463 )
1464 )
1465 elif not const._type_bound:
1466 # skip unique constraints that would be generated
1467 # by the 'unique' flag on Column
1468 if const._column_flag:
1469 continue
1471 table.append_constraint(
1472 const._copy(schema=actual_schema, target_table=table)
1473 )
1474 for index in self.indexes:
1475 # skip indexes that would be generated
1476 # by the 'index' flag on Column
1477 if index._column_flag:
1478 continue
1479 Index(
1480 index.name,
1481 unique=index.unique,
1482 *[
1483 _copy_expression(expr, self, table)
1484 for expr in index._table_bound_expressions
1485 ],
1486 _table=table,
1487 **index.kwargs,
1488 )
1489 return self._schema_item_copy(table)
1492class Column(DialectKWArgs, SchemaItem, ColumnClause[_T]):
1493 """Represents a column in a database table."""
1495 __visit_name__ = "column"
1497 inherit_cache = True
1498 key: str
1500 server_default: Optional[FetchedValue]
1502 def __init__(
1503 self,
1504 __name_pos: Optional[
1505 Union[str, _TypeEngineArgument[_T], SchemaEventTarget]
1506 ] = None,
1507 __type_pos: Optional[
1508 Union[_TypeEngineArgument[_T], SchemaEventTarget]
1509 ] = None,
1510 /,
1511 *args: SchemaEventTarget,
1512 name: Optional[str] = None,
1513 type_: Optional[_TypeEngineArgument[_T]] = None,
1514 autoincrement: _AutoIncrementType = "auto",
1515 default: Optional[Any] = _NoArg.NO_ARG,
1516 insert_default: Optional[Any] = _NoArg.NO_ARG,
1517 doc: Optional[str] = None,
1518 key: Optional[str] = None,
1519 index: Optional[bool] = None,
1520 unique: Optional[bool] = None,
1521 info: Optional[_InfoType] = None,
1522 nullable: Optional[
1523 Union[bool, Literal[SchemaConst.NULL_UNSPECIFIED]]
1524 ] = SchemaConst.NULL_UNSPECIFIED,
1525 onupdate: Optional[Any] = None,
1526 primary_key: bool = False,
1527 server_default: Optional[_ServerDefaultArgument] = None,
1528 server_onupdate: Optional[_ServerOnUpdateArgument] = None,
1529 quote: Optional[bool] = None,
1530 system: bool = False,
1531 comment: Optional[str] = None,
1532 insert_sentinel: bool = False,
1533 _omit_from_statements: bool = False,
1534 _proxies: Optional[Any] = None,
1535 **dialect_kwargs: Any,
1536 ):
1537 r"""
1538 Construct a new ``Column`` object.
1540 :param name: The name of this column as represented in the database.
1541 This argument may be the first positional argument, or specified
1542 via keyword.
1544 Names which contain no upper case characters
1545 will be treated as case insensitive names, and will not be quoted
1546 unless they are a reserved word. Names with any number of upper
1547 case characters will be quoted and sent exactly. Note that this
1548 behavior applies even for databases which standardize upper
1549 case names as case insensitive such as Oracle.
1551 The name field may be omitted at construction time and applied
1552 later, at any time before the Column is associated with a
1553 :class:`_schema.Table`. This is to support convenient
1554 usage within the :mod:`~sqlalchemy.ext.declarative` extension.
1556 :param type\_: The column's type, indicated using an instance which
1557 subclasses :class:`~sqlalchemy.types.TypeEngine`. If no arguments
1558 are required for the type, the class of the type can be sent
1559 as well, e.g.::
1561 # use a type with arguments
1562 Column('data', String(50))
1564 # use no arguments
1565 Column('level', Integer)
1567 The ``type`` argument may be the second positional argument
1568 or specified by keyword.
1570 If the ``type`` is ``None`` or is omitted, it will first default to
1571 the special type :class:`.NullType`. If and when this
1572 :class:`_schema.Column` is made to refer to another column using
1573 :class:`_schema.ForeignKey` and/or
1574 :class:`_schema.ForeignKeyConstraint`, the type
1575 of the remote-referenced column will be copied to this column as
1576 well, at the moment that the foreign key is resolved against that
1577 remote :class:`_schema.Column` object.
1579 :param \*args: Additional positional arguments include various
1580 :class:`.SchemaItem` derived constructs which will be applied
1581 as options to the column. These include instances of
1582 :class:`.Constraint`, :class:`_schema.ForeignKey`,
1583 :class:`.ColumnDefault`, :class:`.Sequence`, :class:`.Computed`
1584 :class:`.Identity`. In some cases an
1585 equivalent keyword argument is available such as ``server_default``,
1586 ``default`` and ``unique``.
1588 :param autoincrement: Set up "auto increment" semantics for an
1589 **integer primary key column with no foreign key dependencies**
1590 (see later in this docstring for a more specific definition).
1591 This may influence the :term:`DDL` that will be emitted for
1592 this column during a table create, as well as how the column
1593 will be considered when INSERT statements are compiled and
1594 executed.
1596 The default value is the string ``"auto"``,
1597 which indicates that a single-column (i.e. non-composite) primary key
1598 that is of an INTEGER type with no other client-side or server-side
1599 default constructs indicated should receive auto increment semantics
1600 automatically. Other values include ``True`` (force this column to
1601 have auto-increment semantics for a :term:`composite primary key` as
1602 well), ``False`` (this column should never have auto-increment
1603 semantics), and the string ``"ignore_fk"`` (special-case for foreign
1604 key columns, see below).
1606 The term "auto increment semantics" refers both to the kind of DDL
1607 that will be emitted for the column within a CREATE TABLE statement,
1608 when methods such as :meth:`.MetaData.create_all` and
1609 :meth:`.Table.create` are invoked, as well as how the column will be
1610 considered when an INSERT statement is compiled and emitted to the
1611 database:
1613 * **DDL rendering** (i.e. :meth:`.MetaData.create_all`,
1614 :meth:`.Table.create`): When used on a :class:`.Column` that has
1615 no other
1616 default-generating construct associated with it (such as a
1617 :class:`.Sequence` or :class:`.Identity` construct), the parameter
1618 will imply that database-specific keywords such as PostgreSQL
1619 ``SERIAL``, MySQL ``AUTO_INCREMENT``, or ``IDENTITY`` on SQL Server
1620 should also be rendered. Not every database backend has an
1621 "implied" default generator available; for example the Oracle
1622 backend always needs an explicit construct such as
1623 :class:`.Identity` to be included with a :class:`.Column` in order
1624 for the DDL rendered to include auto-generating constructs to also
1625 be produced in the database.
1627 * **INSERT semantics** (i.e. when a :func:`_sql.insert` construct is
1628 compiled into a SQL string and is then executed on a database using
1629 :meth:`_engine.Connection.execute` or equivalent): A single-row
1630 INSERT statement will be known to produce a new integer primary key
1631 value automatically for this column, which will be accessible
1632 after the statement is invoked via the
1633 :attr:`.CursorResult.inserted_primary_key` attribute upon the
1634 :class:`_result.Result` object. This also applies towards use of the
1635 ORM when ORM-mapped objects are persisted to the database,
1636 indicating that a new integer primary key will be available to
1637 become part of the :term:`identity key` for that object. This
1638 behavior takes place regardless of what DDL constructs are
1639 associated with the :class:`_schema.Column` and is independent
1640 of the "DDL Rendering" behavior discussed in the previous note
1641 above.
1643 The parameter may be set to ``True`` to indicate that a column which
1644 is part of a composite (i.e. multi-column) primary key should
1645 have autoincrement semantics, though note that only one column
1646 within a primary key may have this setting. It can also
1647 be set to ``True`` to indicate autoincrement semantics on a
1648 column that has a client-side or server-side default configured,
1649 however note that not all dialects can accommodate all styles
1650 of default as an "autoincrement". It can also be
1651 set to ``False`` on a single-column primary key that has a
1652 datatype of INTEGER in order to disable auto increment semantics
1653 for that column.
1655 The setting *only* has an effect for columns which are:
1657 * Integer derived (i.e. INT, SMALLINT, BIGINT).
1659 * Part of the primary key
1661 * Not referring to another column via :class:`_schema.ForeignKey`,
1662 unless
1663 the value is specified as ``'ignore_fk'``::
1665 # turn on autoincrement for this column despite
1666 # the ForeignKey()
1667 Column('id', ForeignKey('other.id'),
1668 primary_key=True, autoincrement='ignore_fk')
1670 It is typically not desirable to have "autoincrement" enabled on a
1671 column that refers to another via foreign key, as such a column is
1672 required to refer to a value that originates from elsewhere.
1674 The setting has these effects on columns that meet the
1675 above criteria:
1677 * DDL issued for the column, if the column does not already include
1678 a default generating construct supported by the backend such as
1679 :class:`.Identity`, will include database-specific
1680 keywords intended to signify this column as an
1681 "autoincrement" column for specific backends. Behavior for
1682 primary SQLAlchemy dialects includes:
1684 * AUTO INCREMENT on MySQL and MariaDB
1685 * SERIAL on PostgreSQL
1686 * IDENTITY on MS-SQL - this occurs even without the
1687 :class:`.Identity` construct as the
1688 :paramref:`.Column.autoincrement` parameter pre-dates this
1689 construct.
1690 * SQLite - SQLite integer primary key columns are implicitly
1691 "auto incrementing" and no additional keywords are rendered;
1692 to render the special SQLite keyword ``AUTOINCREMENT``
1693 is not included as this is unnecessary and not recommended
1694 by the database vendor. See the section
1695 :ref:`sqlite_autoincrement` for more background.
1696 * Oracle - The Oracle dialect has no default "autoincrement"
1697 feature available at this time, instead the :class:`.Identity`
1698 construct is recommended to achieve this (the :class:`.Sequence`
1699 construct may also be used).
1700 * Third-party dialects - consult those dialects' documentation
1701 for details on their specific behaviors.
1703 * When a single-row :func:`_sql.insert` construct is compiled and
1704 executed, which does not set the :meth:`_sql.Insert.inline`
1705 modifier, newly generated primary key values for this column
1706 will be automatically retrieved upon statement execution
1707 using a method specific to the database driver in use:
1709 * MySQL, SQLite - calling upon ``cursor.lastrowid()``
1710 (see
1711 `https://www.python.org/dev/peps/pep-0249/#lastrowid
1712 <https://www.python.org/dev/peps/pep-0249/#lastrowid>`_)
1713 * PostgreSQL, SQL Server, Oracle - use RETURNING or an equivalent
1714 construct when rendering an INSERT statement, and then retrieving
1715 the newly generated primary key values after execution
1716 * PostgreSQL, Oracle for :class:`_schema.Table` objects that
1717 set :paramref:`_schema.Table.implicit_returning` to False -
1718 for a :class:`.Sequence` only, the :class:`.Sequence` is invoked
1719 explicitly before the INSERT statement takes place so that the
1720 newly generated primary key value is available to the client
1721 * SQL Server for :class:`_schema.Table` objects that
1722 set :paramref:`_schema.Table.implicit_returning` to False -
1723 the ``SELECT scope_identity()`` construct is used after the
1724 INSERT statement is invoked to retrieve the newly generated
1725 primary key value.
1726 * Third-party dialects - consult those dialects' documentation
1727 for details on their specific behaviors.
1729 * For multiple-row :func:`_sql.insert` constructs invoked with
1730 a list of parameters (i.e. "executemany" semantics), primary-key
1731 retrieving behaviors are generally disabled, however there may
1732 be special APIs that may be used to retrieve lists of new
1733 primary key values for an "executemany", such as the psycopg2
1734 "fast insertmany" feature. Such features are very new and
1735 may not yet be well covered in documentation.
1737 :param default: A scalar, Python callable, or
1738 :class:`_expression.ColumnElement` expression representing the
1739 *default value* for this column, which will be invoked upon insert
1740 if this column is otherwise not specified in the VALUES clause of
1741 the insert. This is a shortcut to using :class:`.ColumnDefault` as
1742 a positional argument; see that class for full detail on the
1743 structure of the argument.
1745 Contrast this argument to
1746 :paramref:`_schema.Column.server_default`
1747 which creates a default generator on the database side.
1749 .. seealso::
1751 :ref:`metadata_defaults_toplevel`
1753 :param insert_default: An alias of :paramref:`.Column.default`
1754 for compatibility with :func:`_orm.mapped_column`.
1756 .. versionadded: 2.0.31
1758 :param doc: optional String that can be used by the ORM or similar
1759 to document attributes on the Python side. This attribute does
1760 **not** render SQL comments; use the
1761 :paramref:`_schema.Column.comment`
1762 parameter for this purpose.
1764 :param key: An optional string identifier which will identify this
1765 ``Column`` object on the :class:`_schema.Table`.
1766 When a key is provided,
1767 this is the only identifier referencing the ``Column`` within the
1768 application, including ORM attribute mapping; the ``name`` field
1769 is used only when rendering SQL.
1771 :param index: When ``True``, indicates that a :class:`_schema.Index`
1772 construct will be automatically generated for this
1773 :class:`_schema.Column`, which will result in a "CREATE INDEX"
1774 statement being emitted for the :class:`_schema.Table` when the DDL
1775 create operation is invoked.
1777 Using this flag is equivalent to making use of the
1778 :class:`_schema.Index` construct explicitly at the level of the
1779 :class:`_schema.Table` construct itself::
1781 Table(
1782 "some_table",
1783 metadata,
1784 Column("x", Integer),
1785 Index("ix_some_table_x", "x")
1786 )
1788 To add the :paramref:`_schema.Index.unique` flag to the
1789 :class:`_schema.Index`, set both the
1790 :paramref:`_schema.Column.unique` and
1791 :paramref:`_schema.Column.index` flags to True simultaneously,
1792 which will have the effect of rendering the "CREATE UNIQUE INDEX"
1793 DDL instruction instead of "CREATE INDEX".
1795 The name of the index is generated using the
1796 :ref:`default naming convention <constraint_default_naming_convention>`
1797 which for the :class:`_schema.Index` construct is of the form
1798 ``ix_<tablename>_<columnname>``.
1800 As this flag is intended only as a convenience for the common case
1801 of adding a single-column, default configured index to a table
1802 definition, explicit use of the :class:`_schema.Index` construct
1803 should be preferred for most use cases, including composite indexes
1804 that encompass more than one column, indexes with SQL expressions
1805 or ordering, backend-specific index configuration options, and
1806 indexes that use a specific name.
1808 .. note:: the :attr:`_schema.Column.index` attribute on
1809 :class:`_schema.Column`
1810 **does not indicate** if this column is indexed or not, only
1811 if this flag was explicitly set here. To view indexes on
1812 a column, view the :attr:`_schema.Table.indexes` collection
1813 or use :meth:`_reflection.Inspector.get_indexes`.
1815 .. seealso::
1817 :ref:`schema_indexes`
1819 :ref:`constraint_naming_conventions`
1821 :paramref:`_schema.Column.unique`
1823 :param info: Optional data dictionary which will be populated into the
1824 :attr:`.SchemaItem.info` attribute of this object.
1826 :param nullable: When set to ``False``, will cause the "NOT NULL"
1827 phrase to be added when generating DDL for the column. When
1828 ``True``, will normally generate nothing (in SQL this defaults to
1829 "NULL"), except in some very specific backend-specific edge cases
1830 where "NULL" may render explicitly.
1831 Defaults to ``True`` unless :paramref:`_schema.Column.primary_key`
1832 is also ``True`` or the column specifies a :class:`_sql.Identity`,
1833 in which case it defaults to ``False``.
1834 This parameter is only used when issuing CREATE TABLE statements.
1836 .. note::
1838 When the column specifies a :class:`_sql.Identity` this
1839 parameter is in general ignored by the DDL compiler. The
1840 PostgreSQL database allows nullable identity column by
1841 setting this parameter to ``True`` explicitly.
1843 :param onupdate: A scalar, Python callable, or
1844 :class:`~sqlalchemy.sql.expression.ClauseElement` representing a
1845 default value to be applied to the column within UPDATE
1846 statements, which will be invoked upon update if this column is not
1847 present in the SET clause of the update. This is a shortcut to
1848 using :class:`.ColumnDefault` as a positional argument with
1849 ``for_update=True``.
1851 .. seealso::
1853 :ref:`metadata_defaults` - complete discussion of onupdate
1855 :param primary_key: If ``True``, marks this column as a primary key
1856 column. Multiple columns can have this flag set to specify
1857 composite primary keys. As an alternative, the primary key of a
1858 :class:`_schema.Table` can be specified via an explicit
1859 :class:`.PrimaryKeyConstraint` object.
1861 :param server_default: A :class:`.FetchedValue` instance, str, Unicode
1862 or :func:`~sqlalchemy.sql.expression.text` construct representing
1863 the DDL DEFAULT value for the column.
1865 String types will be emitted as-is, surrounded by single quotes::
1867 Column('x', Text, server_default="val")
1869 x TEXT DEFAULT 'val'
1871 A :func:`~sqlalchemy.sql.expression.text` expression will be
1872 rendered as-is, without quotes::
1874 Column('y', DateTime, server_default=text('NOW()'))
1876 y DATETIME DEFAULT NOW()
1878 Strings and text() will be converted into a
1879 :class:`.DefaultClause` object upon initialization.
1881 This parameter can also accept complex combinations of contextually
1882 valid SQLAlchemy expressions or constructs::
1884 from sqlalchemy import create_engine
1885 from sqlalchemy import Table, Column, MetaData, ARRAY, Text
1886 from sqlalchemy.dialects.postgresql import array
1888 engine = create_engine(
1889 'postgresql+psycopg2://scott:tiger@localhost/mydatabase'
1890 )
1891 metadata_obj = MetaData()
1892 tbl = Table(
1893 "foo",
1894 metadata_obj,
1895 Column("bar",
1896 ARRAY(Text),
1897 server_default=array(["biz", "bang", "bash"])
1898 )
1899 )
1900 metadata_obj.create_all(engine)
1902 The above results in a table created with the following SQL::
1904 CREATE TABLE foo (
1905 bar TEXT[] DEFAULT ARRAY['biz', 'bang', 'bash']
1906 )
1908 Use :class:`.FetchedValue` to indicate that an already-existing
1909 column will generate a default value on the database side which
1910 will be available to SQLAlchemy for post-fetch after inserts. This
1911 construct does not specify any DDL and the implementation is left
1912 to the database, such as via a trigger.
1914 .. seealso::
1916 :ref:`server_defaults` - complete discussion of server side
1917 defaults
1919 :param server_onupdate: A :class:`.FetchedValue` instance
1920 representing a database-side default generation function,
1921 such as a trigger. This
1922 indicates to SQLAlchemy that a newly generated value will be
1923 available after updates. This construct does not actually
1924 implement any kind of generation function within the database,
1925 which instead must be specified separately.
1928 .. warning:: This directive **does not** currently produce MySQL's
1929 "ON UPDATE CURRENT_TIMESTAMP()" clause. See
1930 :ref:`mysql_timestamp_onupdate` for background on how to
1931 produce this clause.
1933 .. seealso::
1935 :ref:`triggered_columns`
1937 :param quote: Force quoting of this column's name on or off,
1938 corresponding to ``True`` or ``False``. When left at its default
1939 of ``None``, the column identifier will be quoted according to
1940 whether the name is case sensitive (identifiers with at least one
1941 upper case character are treated as case sensitive), or if it's a
1942 reserved word. This flag is only needed to force quoting of a
1943 reserved word which is not known by the SQLAlchemy dialect.
1945 :param unique: When ``True``, and the :paramref:`_schema.Column.index`
1946 parameter is left at its default value of ``False``,
1947 indicates that a :class:`_schema.UniqueConstraint`
1948 construct will be automatically generated for this
1949 :class:`_schema.Column`,
1950 which will result in a "UNIQUE CONSTRAINT" clause referring
1951 to this column being included
1952 in the ``CREATE TABLE`` statement emitted, when the DDL create
1953 operation for the :class:`_schema.Table` object is invoked.
1955 When this flag is ``True`` while the
1956 :paramref:`_schema.Column.index` parameter is simultaneously
1957 set to ``True``, the effect instead is that a
1958 :class:`_schema.Index` construct which includes the
1959 :paramref:`_schema.Index.unique` parameter set to ``True``
1960 is generated. See the documentation for
1961 :paramref:`_schema.Column.index` for additional detail.
1963 Using this flag is equivalent to making use of the
1964 :class:`_schema.UniqueConstraint` construct explicitly at the
1965 level of the :class:`_schema.Table` construct itself::
1967 Table(
1968 "some_table",
1969 metadata,
1970 Column("x", Integer),
1971 UniqueConstraint("x")
1972 )
1974 The :paramref:`_schema.UniqueConstraint.name` parameter
1975 of the unique constraint object is left at its default value
1976 of ``None``; in the absence of a :ref:`naming convention <constraint_naming_conventions>`
1977 for the enclosing :class:`_schema.MetaData`, the UNIQUE CONSTRAINT
1978 construct will be emitted as unnamed, which typically invokes
1979 a database-specific naming convention to take place.
1981 As this flag is intended only as a convenience for the common case
1982 of adding a single-column, default configured unique constraint to a table
1983 definition, explicit use of the :class:`_schema.UniqueConstraint` construct
1984 should be preferred for most use cases, including composite constraints
1985 that encompass more than one column, backend-specific index configuration options, and
1986 constraints that use a specific name.
1988 .. note:: the :attr:`_schema.Column.unique` attribute on
1989 :class:`_schema.Column`
1990 **does not indicate** if this column has a unique constraint or
1991 not, only if this flag was explicitly set here. To view
1992 indexes and unique constraints that may involve this column,
1993 view the
1994 :attr:`_schema.Table.indexes` and/or
1995 :attr:`_schema.Table.constraints` collections or use
1996 :meth:`_reflection.Inspector.get_indexes` and/or
1997 :meth:`_reflection.Inspector.get_unique_constraints`
1999 .. seealso::
2001 :ref:`schema_unique_constraint`
2003 :ref:`constraint_naming_conventions`
2005 :paramref:`_schema.Column.index`
2007 :param system: When ``True``, indicates this is a "system" column,
2008 that is a column which is automatically made available by the
2009 database, and should not be included in the columns list for a
2010 ``CREATE TABLE`` statement.
2012 For more elaborate scenarios where columns should be
2013 conditionally rendered differently on different backends,
2014 consider custom compilation rules for :class:`.CreateColumn`.
2016 :param comment: Optional string that will render an SQL comment on
2017 table creation.
2019 .. versionadded:: 1.2 Added the
2020 :paramref:`_schema.Column.comment`
2021 parameter to :class:`_schema.Column`.
2023 :param insert_sentinel: Marks this :class:`_schema.Column` as an
2024 :term:`insert sentinel` used for optimizing the performance of the
2025 :term:`insertmanyvalues` feature for tables that don't
2026 otherwise have qualifying primary key configurations.
2028 .. versionadded:: 2.0.10
2030 .. seealso::
2032 :func:`_schema.insert_sentinel` - all in one helper for declaring
2033 sentinel columns
2035 :ref:`engine_insertmanyvalues`
2037 :ref:`engine_insertmanyvalues_sentinel_columns`
2040 """ # noqa: E501, RST201, RST202
2042 l_args = [__name_pos, __type_pos] + list(args)
2043 del args
2045 if l_args:
2046 if isinstance(l_args[0], str):
2047 if name is not None:
2048 raise exc.ArgumentError(
2049 "May not pass name positionally and as a keyword."
2050 )
2051 name = l_args.pop(0) # type: ignore
2052 elif l_args[0] is None:
2053 l_args.pop(0)
2054 if l_args:
2055 coltype = l_args[0]
2057 if hasattr(coltype, "_sqla_type"):
2058 if type_ is not None:
2059 raise exc.ArgumentError(
2060 "May not pass type_ positionally and as a keyword."
2061 )
2062 type_ = l_args.pop(0) # type: ignore
2063 elif l_args[0] is None:
2064 l_args.pop(0)
2066 if name is not None:
2067 name = quoted_name(name, quote)
2068 elif quote is not None:
2069 raise exc.ArgumentError(
2070 "Explicit 'name' is required when sending 'quote' argument"
2071 )
2073 # name = None is expected to be an interim state
2074 # note this use case is legacy now that ORM declarative has a
2075 # dedicated "column" construct local to the ORM
2076 super().__init__(name, type_) # type: ignore
2078 self.key = key if key is not None else name # type: ignore
2079 self.primary_key = primary_key
2080 self._insert_sentinel = insert_sentinel
2081 self._omit_from_statements = _omit_from_statements
2082 self._user_defined_nullable = udn = nullable
2083 if udn is not NULL_UNSPECIFIED:
2084 self.nullable = udn
2085 else:
2086 self.nullable = not primary_key
2088 # these default to None because .index and .unique is *not*
2089 # an informational flag about Column - there can still be an
2090 # Index or UniqueConstraint referring to this Column.
2091 self.index = index
2092 self.unique = unique
2094 self.system = system
2095 self.doc = doc
2096 self.autoincrement: _AutoIncrementType = autoincrement
2097 self.constraints = set()
2098 self.foreign_keys = set()
2099 self.comment = comment
2100 self.computed = None
2101 self.identity = None
2103 # check if this Column is proxying another column
2105 if _proxies is not None:
2106 self._proxies = _proxies
2107 else:
2108 # otherwise, add DDL-related events
2109 self._set_type(self.type)
2111 if insert_default is not _NoArg.NO_ARG:
2112 resolved_default = insert_default
2113 elif default is not _NoArg.NO_ARG:
2114 resolved_default = default
2115 else:
2116 resolved_default = None
2118 if resolved_default is not None:
2119 if not isinstance(resolved_default, (ColumnDefault, Sequence)):
2120 resolved_default = ColumnDefault(resolved_default)
2122 self.default = resolved_default
2123 l_args.append(resolved_default)
2124 else:
2125 self.default = None
2127 if onupdate is not None:
2128 if not isinstance(onupdate, (ColumnDefault, Sequence)):
2129 onupdate = ColumnDefault(onupdate, for_update=True)
2131 self.onupdate = onupdate
2132 l_args.append(onupdate)
2133 else:
2134 self.onupdate = None
2136 if server_default is not None:
2137 if isinstance(server_default, FetchedValue):
2138 server_default = server_default._as_for_update(False)
2139 l_args.append(server_default)
2140 else:
2141 server_default = DefaultClause(server_default)
2142 l_args.append(server_default)
2143 self.server_default = server_default
2145 if server_onupdate is not None:
2146 if isinstance(server_onupdate, FetchedValue):
2147 server_onupdate = server_onupdate._as_for_update(True)
2148 l_args.append(server_onupdate)
2149 else:
2150 server_onupdate = DefaultClause(
2151 server_onupdate, for_update=True
2152 )
2153 l_args.append(server_onupdate)
2154 self.server_onupdate = server_onupdate
2156 self._init_items(*cast(_typing_Sequence[SchemaItem], l_args))
2158 util.set_creation_order(self)
2160 if info is not None:
2161 self.info = info
2163 self._extra_kwargs(**dialect_kwargs)
2165 table: Table
2167 constraints: Set[Constraint]
2169 foreign_keys: Set[ForeignKey]
2170 """A collection of all :class:`_schema.ForeignKey` marker objects
2171 associated with this :class:`_schema.Column`.
2173 Each object is a member of a :class:`_schema.Table`-wide
2174 :class:`_schema.ForeignKeyConstraint`.
2176 .. seealso::
2178 :attr:`_schema.Table.foreign_keys`
2180 """
2182 index: Optional[bool]
2183 """The value of the :paramref:`_schema.Column.index` parameter.
2185 Does not indicate if this :class:`_schema.Column` is actually indexed
2186 or not; use :attr:`_schema.Table.indexes`.
2188 .. seealso::
2190 :attr:`_schema.Table.indexes`
2191 """
2193 unique: Optional[bool]
2194 """The value of the :paramref:`_schema.Column.unique` parameter.
2196 Does not indicate if this :class:`_schema.Column` is actually subject to
2197 a unique constraint or not; use :attr:`_schema.Table.indexes` and
2198 :attr:`_schema.Table.constraints`.
2200 .. seealso::
2202 :attr:`_schema.Table.indexes`
2204 :attr:`_schema.Table.constraints`.
2206 """
2208 computed: Optional[Computed]
2210 identity: Optional[Identity]
2212 def _set_type(self, type_: TypeEngine[Any]) -> None:
2213 assert self.type._isnull or type_ is self.type
2215 self.type = type_
2216 if isinstance(self.type, SchemaEventTarget):
2217 self.type._set_parent_with_dispatch(self)
2218 for impl in self.type._variant_mapping.values():
2219 if isinstance(impl, SchemaEventTarget):
2220 impl._set_parent_with_dispatch(self)
2222 @HasMemoized.memoized_attribute
2223 def _default_description_tuple(self) -> _DefaultDescriptionTuple:
2224 """used by default.py -> _process_execute_defaults()"""
2226 return _DefaultDescriptionTuple._from_column_default(self.default)
2228 @HasMemoized.memoized_attribute
2229 def _onupdate_description_tuple(self) -> _DefaultDescriptionTuple:
2230 """used by default.py -> _process_execute_defaults()"""
2231 return _DefaultDescriptionTuple._from_column_default(self.onupdate)
2233 @util.memoized_property
2234 def _gen_static_annotations_cache_key(self) -> bool: # type: ignore
2235 """special attribute used by cache key gen, if true, we will
2236 use a static cache key for the annotations dictionary, else we
2237 will generate a new cache key for annotations each time.
2239 Added for #8790
2241 """
2242 return self.table is not None and self.table._is_table
2244 def _extra_kwargs(self, **kwargs: Any) -> None:
2245 self._validate_dialect_kwargs(kwargs)
2247 def __str__(self) -> str:
2248 if self.name is None:
2249 return "(no name)"
2250 elif self.table is not None:
2251 if self.table.named_with_column:
2252 return self.table.description + "." + self.description
2253 else:
2254 return self.description
2255 else:
2256 return self.description
2258 def references(self, column: Column[Any]) -> bool:
2259 """Return True if this Column references the given column via foreign
2260 key."""
2262 for fk in self.foreign_keys:
2263 if fk.column.proxy_set.intersection(column.proxy_set):
2264 return True
2265 else:
2266 return False
2268 def append_foreign_key(self, fk: ForeignKey) -> None:
2269 fk._set_parent_with_dispatch(self)
2271 def __repr__(self) -> str:
2272 kwarg = []
2273 if self.key != self.name:
2274 kwarg.append("key")
2275 if self.primary_key:
2276 kwarg.append("primary_key")
2277 if not self.nullable:
2278 kwarg.append("nullable")
2279 if self.onupdate:
2280 kwarg.append("onupdate")
2281 if self.default:
2282 kwarg.append("default")
2283 if self.server_default:
2284 kwarg.append("server_default")
2285 if self.comment:
2286 kwarg.append("comment")
2287 return "Column(%s)" % ", ".join(
2288 [repr(self.name)]
2289 + [repr(self.type)]
2290 + [repr(x) for x in self.foreign_keys if x is not None]
2291 + [repr(x) for x in self.constraints]
2292 + [
2293 (
2294 self.table is not None
2295 and "table=<%s>" % self.table.description
2296 or "table=None"
2297 )
2298 ]
2299 + ["%s=%s" % (k, repr(getattr(self, k))) for k in kwarg]
2300 )
2302 def _set_parent( # type: ignore[override]
2303 self,
2304 parent: SchemaEventTarget,
2305 *,
2306 all_names: Dict[str, Column[Any]],
2307 allow_replacements: bool,
2308 **kw: Any,
2309 ) -> None:
2310 table = parent
2311 assert isinstance(table, Table)
2312 if not self.name:
2313 raise exc.ArgumentError(
2314 "Column must be constructed with a non-blank name or "
2315 "assign a non-blank .name before adding to a Table."
2316 )
2318 self._reset_memoizations()
2320 if self.key is None:
2321 self.key = self.name
2323 existing = getattr(self, "table", None)
2324 if existing is not None and existing is not table:
2325 raise exc.ArgumentError(
2326 f"Column object '{self.key}' already "
2327 f"assigned to Table '{existing.description}'"
2328 )
2330 extra_remove = None
2331 existing_col = None
2332 conflicts_on = ""
2334 if self.key in table._columns:
2335 existing_col = table._columns[self.key]
2336 if self.key == self.name:
2337 conflicts_on = "name"
2338 else:
2339 conflicts_on = "key"
2340 elif self.name in all_names:
2341 existing_col = all_names[self.name]
2342 extra_remove = {existing_col}
2343 conflicts_on = "name"
2345 if existing_col is not None:
2346 if existing_col is not self:
2347 if not allow_replacements:
2348 raise exc.DuplicateColumnError(
2349 f"A column with {conflicts_on} "
2350 f"""'{
2351 self.key if conflicts_on == 'key' else self.name
2352 }' """
2353 f"is already present in table '{table.name}'."
2354 )
2355 for fk in existing_col.foreign_keys:
2356 table.foreign_keys.remove(fk)
2357 if fk.constraint in table.constraints:
2358 # this might have been removed
2359 # already, if it's a composite constraint
2360 # and more than one col being replaced
2361 table.constraints.remove(fk.constraint)
2363 if extra_remove and existing_col is not None and self.key == self.name:
2364 util.warn(
2365 f'Column with user-specified key "{existing_col.key}" is '
2366 "being replaced with "
2367 f'plain named column "{self.name}", '
2368 f'key "{existing_col.key}" is being removed. If this is a '
2369 "reflection operation, specify autoload_replace=False to "
2370 "prevent this replacement."
2371 )
2372 table._columns.replace(self, extra_remove=extra_remove)
2373 all_names[self.name] = self
2374 self.table = table
2376 if self._insert_sentinel:
2377 if self.table._sentinel_column is not None:
2378 raise exc.ArgumentError(
2379 "a Table may have only one explicit sentinel column"
2380 )
2381 self.table._sentinel_column = self
2383 if self.primary_key:
2384 table.primary_key._replace(self)
2385 elif self.key in table.primary_key:
2386 raise exc.ArgumentError(
2387 f"Trying to redefine primary-key column '{self.key}' as a "
2388 f"non-primary-key column on table '{table.fullname}'"
2389 )
2391 if self.index:
2392 if isinstance(self.index, str):
2393 raise exc.ArgumentError(
2394 "The 'index' keyword argument on Column is boolean only. "
2395 "To create indexes with a specific name, create an "
2396 "explicit Index object external to the Table."
2397 )
2398 table.append_constraint(
2399 Index(
2400 None, self.key, unique=bool(self.unique), _column_flag=True
2401 )
2402 )
2404 elif self.unique:
2405 if isinstance(self.unique, str):
2406 raise exc.ArgumentError(
2407 "The 'unique' keyword argument on Column is boolean "
2408 "only. To create unique constraints or indexes with a "
2409 "specific name, append an explicit UniqueConstraint to "
2410 "the Table's list of elements, or create an explicit "
2411 "Index object external to the Table."
2412 )
2413 table.append_constraint(
2414 UniqueConstraint(self.key, _column_flag=True)
2415 )
2417 self._setup_on_memoized_fks(lambda fk: fk._set_remote_table(table))
2419 if self.identity and (
2420 isinstance(self.default, Sequence)
2421 or isinstance(self.onupdate, Sequence)
2422 ):
2423 raise exc.ArgumentError(
2424 "An column cannot specify both Identity and Sequence."
2425 )
2427 def _setup_on_memoized_fks(self, fn: Callable[..., Any]) -> None:
2428 fk_keys = [
2429 ((self.table.key, self.key), False),
2430 ((self.table.key, self.name), True),
2431 ]
2432 for fk_key, link_to_name in fk_keys:
2433 if fk_key in self.table.metadata._fk_memos:
2434 for fk in self.table.metadata._fk_memos[fk_key]:
2435 if fk.link_to_name is link_to_name:
2436 fn(fk)
2438 def _on_table_attach(self, fn: Callable[..., Any]) -> None:
2439 if self.table is not None:
2440 fn(self, self.table)
2441 else:
2442 event.listen(self, "after_parent_attach", fn)
2444 @util.deprecated(
2445 "1.4",
2446 "The :meth:`_schema.Column.copy` method is deprecated "
2447 "and will be removed in a future release.",
2448 )
2449 def copy(self, **kw: Any) -> Column[Any]:
2450 return self._copy(**kw)
2452 def _copy(self, **kw: Any) -> Column[Any]:
2453 """Create a copy of this ``Column``, uninitialized.
2455 This is used in :meth:`_schema.Table.to_metadata` and by the ORM.
2457 """
2459 # Constraint objects plus non-constraint-bound ForeignKey objects
2460 args: List[SchemaItem] = [
2461 c._copy(**kw) for c in self.constraints if not c._type_bound
2462 ] + [c._copy(**kw) for c in self.foreign_keys if not c.constraint]
2464 # ticket #5276
2465 column_kwargs = {}
2466 for dialect_name in self.dialect_options:
2467 dialect_options = self.dialect_options[dialect_name]._non_defaults
2468 for (
2469 dialect_option_key,
2470 dialect_option_value,
2471 ) in dialect_options.items():
2472 column_kwargs[dialect_name + "_" + dialect_option_key] = (
2473 dialect_option_value
2474 )
2476 server_default = self.server_default
2477 server_onupdate = self.server_onupdate
2478 if isinstance(server_default, (Computed, Identity)):
2479 # TODO: likely should be copied in all cases
2480 # TODO: if a Sequence, we would need to transfer the Sequence
2481 # .metadata as well
2482 args.append(server_default._copy(**kw))
2483 server_default = server_onupdate = None
2485 type_ = self.type
2486 if isinstance(type_, SchemaEventTarget):
2487 type_ = type_.copy(**kw)
2489 # TODO: DefaultGenerator is not copied here! it's just used again
2490 # with _set_parent() pointing to the old column. see the new
2491 # use of _copy() in the new _merge() method
2493 c = self._constructor(
2494 name=self.name,
2495 type_=type_,
2496 key=self.key,
2497 primary_key=self.primary_key,
2498 unique=self.unique,
2499 system=self.system,
2500 # quote=self.quote, # disabled 2013-08-27 (commit 031ef080)
2501 index=self.index,
2502 autoincrement=self.autoincrement,
2503 default=self.default,
2504 server_default=server_default,
2505 onupdate=self.onupdate,
2506 server_onupdate=server_onupdate,
2507 doc=self.doc,
2508 comment=self.comment,
2509 _omit_from_statements=self._omit_from_statements,
2510 insert_sentinel=self._insert_sentinel,
2511 *args,
2512 **column_kwargs,
2513 )
2515 # copy the state of "nullable" exactly, to accommodate for
2516 # ORM flipping the .nullable flag directly
2517 c.nullable = self.nullable
2518 c._user_defined_nullable = self._user_defined_nullable
2520 return self._schema_item_copy(c)
2522 def _merge(self, other: Column[Any]) -> None:
2523 """merge the elements of another column into this one.
2525 this is used by ORM pep-593 merge and will likely need a lot
2526 of fixes.
2529 """
2531 if self.primary_key:
2532 other.primary_key = True
2534 if self.autoincrement != "auto" and other.autoincrement == "auto":
2535 other.autoincrement = self.autoincrement
2537 if self.system:
2538 other.system = self.system
2540 if self.info:
2541 other.info.update(self.info)
2543 type_ = self.type
2544 if not type_._isnull and other.type._isnull:
2545 if isinstance(type_, SchemaEventTarget):
2546 type_ = type_.copy()
2548 other.type = type_
2550 if isinstance(type_, SchemaEventTarget):
2551 type_._set_parent_with_dispatch(other)
2553 for impl in type_._variant_mapping.values():
2554 if isinstance(impl, SchemaEventTarget):
2555 impl._set_parent_with_dispatch(other)
2557 if (
2558 self._user_defined_nullable is not NULL_UNSPECIFIED
2559 and other._user_defined_nullable is NULL_UNSPECIFIED
2560 ):
2561 other.nullable = self.nullable
2562 other._user_defined_nullable = self._user_defined_nullable
2564 if self.default is not None and other.default is None:
2565 new_default = self.default._copy()
2566 new_default._set_parent(other)
2568 if self.server_default and other.server_default is None:
2569 new_server_default = self.server_default
2570 if isinstance(new_server_default, FetchedValue):
2571 new_server_default = new_server_default._copy()
2572 new_server_default._set_parent(other)
2573 else:
2574 other.server_default = new_server_default
2576 if self.server_onupdate and other.server_onupdate is None:
2577 new_server_onupdate = self.server_onupdate
2578 new_server_onupdate = new_server_onupdate._copy()
2579 new_server_onupdate._set_parent(other)
2581 if self.onupdate and other.onupdate is None:
2582 new_onupdate = self.onupdate._copy()
2583 new_onupdate._set_parent(other)
2585 if self.index in (True, False) and other.index is None:
2586 other.index = self.index
2588 if self.unique in (True, False) and other.unique is None:
2589 other.unique = self.unique
2591 if self.doc and other.doc is None:
2592 other.doc = self.doc
2594 if self.comment and other.comment is None:
2595 other.comment = self.comment
2597 for const in self.constraints:
2598 if not const._type_bound:
2599 new_const = const._copy()
2600 new_const._set_parent(other)
2602 for fk in self.foreign_keys:
2603 if not fk.constraint:
2604 new_fk = fk._copy()
2605 new_fk._set_parent(other)
2607 def _make_proxy(
2608 self,
2609 selectable: FromClause,
2610 name: Optional[str] = None,
2611 key: Optional[str] = None,
2612 name_is_truncatable: bool = False,
2613 compound_select_cols: Optional[
2614 _typing_Sequence[ColumnElement[Any]]
2615 ] = None,
2616 **kw: Any,
2617 ) -> Tuple[str, ColumnClause[_T]]:
2618 """Create a *proxy* for this column.
2620 This is a copy of this ``Column`` referenced by a different parent
2621 (such as an alias or select statement). The column should
2622 be used only in select scenarios, as its full DDL/default
2623 information is not transferred.
2625 """
2627 fk = [
2628 ForeignKey(
2629 col if col is not None else f._colspec,
2630 _unresolvable=col is None,
2631 _constraint=f.constraint,
2632 )
2633 for f, col in [
2634 (fk, fk._resolve_column(raiseerr=False))
2635 for fk in self.foreign_keys
2636 ]
2637 ]
2639 if name is None and self.name is None:
2640 raise exc.InvalidRequestError(
2641 "Cannot initialize a sub-selectable"
2642 " with this Column object until its 'name' has "
2643 "been assigned."
2644 )
2645 try:
2646 c = self._constructor(
2647 (
2648 coercions.expect(
2649 roles.TruncatedLabelRole, name if name else self.name
2650 )
2651 if name_is_truncatable
2652 else (name or self.name)
2653 ),
2654 self.type,
2655 # this may actually be ._proxy_key when the key is incoming
2656 key=key if key else name if name else self.key,
2657 primary_key=self.primary_key,
2658 nullable=self.nullable,
2659 _proxies=(
2660 list(compound_select_cols)
2661 if compound_select_cols
2662 else [self]
2663 ),
2664 *fk,
2665 )
2666 except TypeError as err:
2667 raise TypeError(
2668 "Could not create a copy of this %r object. "
2669 "Ensure the class includes a _constructor() "
2670 "attribute or method which accepts the "
2671 "standard Column constructor arguments, or "
2672 "references the Column class itself." % self.__class__
2673 ) from err
2675 c.table = selectable
2676 c._propagate_attrs = selectable._propagate_attrs
2677 if selectable._is_clone_of is not None:
2678 c._is_clone_of = selectable._is_clone_of.columns.get(c.key)
2679 if self.primary_key:
2680 selectable.primary_key.add(c) # type: ignore
2681 if fk:
2682 selectable.foreign_keys.update(fk) # type: ignore
2683 return c.key, c
2686def insert_sentinel(
2687 name: Optional[str] = None,
2688 type_: Optional[_TypeEngineArgument[_T]] = None,
2689 *,
2690 default: Optional[Any] = None,
2691 omit_from_statements: bool = True,
2692) -> Column[Any]:
2693 """Provides a surrogate :class:`_schema.Column` that will act as a
2694 dedicated insert :term:`sentinel` column, allowing efficient bulk
2695 inserts with deterministic RETURNING sorting for tables that
2696 don't otherwise have qualifying primary key configurations.
2698 Adding this column to a :class:`.Table` object requires that a
2699 corresponding database table actually has this column present, so if adding
2700 it to an existing model, existing database tables would need to be migrated
2701 (e.g. using ALTER TABLE or similar) to include this column.
2703 For background on how this object is used, see the section
2704 :ref:`engine_insertmanyvalues_sentinel_columns` as part of the
2705 section :ref:`engine_insertmanyvalues`.
2707 The :class:`_schema.Column` returned will be a nullable integer column by
2708 default and make use of a sentinel-specific default generator used only in
2709 "insertmanyvalues" operations.
2711 .. seealso::
2713 :func:`_orm.orm_insert_sentinel`
2715 :paramref:`_schema.Column.insert_sentinel`
2717 :ref:`engine_insertmanyvalues`
2719 :ref:`engine_insertmanyvalues_sentinel_columns`
2722 .. versionadded:: 2.0.10
2724 """
2725 return Column(
2726 name=name,
2727 type_=type_api.INTEGERTYPE if type_ is None else type_,
2728 default=(
2729 default if default is not None else _InsertSentinelColumnDefault()
2730 ),
2731 _omit_from_statements=omit_from_statements,
2732 insert_sentinel=True,
2733 )
2736class ForeignKey(DialectKWArgs, SchemaItem):
2737 """Defines a dependency between two columns.
2739 ``ForeignKey`` is specified as an argument to a :class:`_schema.Column`
2740 object,
2741 e.g.::
2743 t = Table("remote_table", metadata,
2744 Column("remote_id", ForeignKey("main_table.id"))
2745 )
2747 Note that ``ForeignKey`` is only a marker object that defines
2748 a dependency between two columns. The actual constraint
2749 is in all cases represented by the :class:`_schema.ForeignKeyConstraint`
2750 object. This object will be generated automatically when
2751 a ``ForeignKey`` is associated with a :class:`_schema.Column` which
2752 in turn is associated with a :class:`_schema.Table`. Conversely,
2753 when :class:`_schema.ForeignKeyConstraint` is applied to a
2754 :class:`_schema.Table`,
2755 ``ForeignKey`` markers are automatically generated to be
2756 present on each associated :class:`_schema.Column`, which are also
2757 associated with the constraint object.
2759 Note that you cannot define a "composite" foreign key constraint,
2760 that is a constraint between a grouping of multiple parent/child
2761 columns, using ``ForeignKey`` objects. To define this grouping,
2762 the :class:`_schema.ForeignKeyConstraint` object must be used, and applied
2763 to the :class:`_schema.Table`. The associated ``ForeignKey`` objects
2764 are created automatically.
2766 The ``ForeignKey`` objects associated with an individual
2767 :class:`_schema.Column`
2768 object are available in the `foreign_keys` collection
2769 of that column.
2771 Further examples of foreign key configuration are in
2772 :ref:`metadata_foreignkeys`.
2774 """
2776 __visit_name__ = "foreign_key"
2778 parent: Column[Any]
2780 _table_column: Optional[Column[Any]]
2782 _colspec: Union[str, Column[Any]]
2784 def __init__(
2785 self,
2786 column: _DDLColumnReferenceArgument,
2787 _constraint: Optional[ForeignKeyConstraint] = None,
2788 use_alter: bool = False,
2789 name: _ConstraintNameArgument = None,
2790 onupdate: Optional[str] = None,
2791 ondelete: Optional[str] = None,
2792 deferrable: Optional[bool] = None,
2793 initially: Optional[str] = None,
2794 link_to_name: bool = False,
2795 match: Optional[str] = None,
2796 info: Optional[_InfoType] = None,
2797 comment: Optional[str] = None,
2798 _unresolvable: bool = False,
2799 **dialect_kw: Any,
2800 ):
2801 r"""
2802 Construct a column-level FOREIGN KEY.
2804 The :class:`_schema.ForeignKey` object when constructed generates a
2805 :class:`_schema.ForeignKeyConstraint`
2806 which is associated with the parent
2807 :class:`_schema.Table` object's collection of constraints.
2809 :param column: A single target column for the key relationship. A
2810 :class:`_schema.Column` object or a column name as a string:
2811 ``tablename.columnkey`` or ``schema.tablename.columnkey``.
2812 ``columnkey`` is the ``key`` which has been assigned to the column
2813 (defaults to the column name itself), unless ``link_to_name`` is
2814 ``True`` in which case the rendered name of the column is used.
2816 :param name: Optional string. An in-database name for the key if
2817 `constraint` is not provided.
2819 :param onupdate: Optional string. If set, emit ON UPDATE <value> when
2820 issuing DDL for this constraint. Typical values include CASCADE,
2821 DELETE and RESTRICT.
2823 :param ondelete: Optional string. If set, emit ON DELETE <value> when
2824 issuing DDL for this constraint. Typical values include CASCADE,
2825 DELETE and RESTRICT.
2827 :param deferrable: Optional bool. If set, emit DEFERRABLE or NOT
2828 DEFERRABLE when issuing DDL for this constraint.
2830 :param initially: Optional string. If set, emit INITIALLY <value> when
2831 issuing DDL for this constraint.
2833 :param link_to_name: if True, the string name given in ``column`` is
2834 the rendered name of the referenced column, not its locally
2835 assigned ``key``.
2837 :param use_alter: passed to the underlying
2838 :class:`_schema.ForeignKeyConstraint`
2839 to indicate the constraint should
2840 be generated/dropped externally from the CREATE TABLE/ DROP TABLE
2841 statement. See :paramref:`_schema.ForeignKeyConstraint.use_alter`
2842 for further description.
2844 .. seealso::
2846 :paramref:`_schema.ForeignKeyConstraint.use_alter`
2848 :ref:`use_alter`
2850 :param match: Optional string. If set, emit MATCH <value> when issuing
2851 DDL for this constraint. Typical values include SIMPLE, PARTIAL
2852 and FULL.
2854 :param info: Optional data dictionary which will be populated into the
2855 :attr:`.SchemaItem.info` attribute of this object.
2857 :param comment: Optional string that will render an SQL comment on
2858 foreign key constraint creation.
2860 .. versionadded:: 2.0
2862 :param \**dialect_kw: Additional keyword arguments are dialect
2863 specific, and passed in the form ``<dialectname>_<argname>``. The
2864 arguments are ultimately handled by a corresponding
2865 :class:`_schema.ForeignKeyConstraint`.
2866 See the documentation regarding
2867 an individual dialect at :ref:`dialect_toplevel` for detail on
2868 documented arguments.
2870 """
2872 self._unresolvable = _unresolvable
2874 self._colspec, self._table_column = self._parse_colspec_argument(
2875 column
2876 )
2878 # the linked ForeignKeyConstraint.
2879 # ForeignKey will create this when parent Column
2880 # is attached to a Table, *or* ForeignKeyConstraint
2881 # object passes itself in when creating ForeignKey
2882 # markers.
2883 self.constraint = _constraint
2885 # .parent is not Optional under normal use
2886 self.parent = None # type: ignore
2888 self.use_alter = use_alter
2889 self.name = name
2890 self.onupdate = onupdate
2891 self.ondelete = ondelete
2892 self.deferrable = deferrable
2893 self.initially = initially
2894 self.link_to_name = link_to_name
2895 self.match = match
2896 self.comment = comment
2897 if info:
2898 self.info = info
2899 self._unvalidated_dialect_kw = dialect_kw
2901 def _resolve_colspec_argument(
2902 self,
2903 ) -> Tuple[
2904 Union[str, Column[Any]],
2905 Optional[Column[Any]],
2906 ]:
2907 argument = self._colspec
2909 return self._parse_colspec_argument(argument)
2911 def _parse_colspec_argument(
2912 self,
2913 argument: _DDLColumnArgument,
2914 ) -> Tuple[
2915 Union[str, Column[Any]],
2916 Optional[Column[Any]],
2917 ]:
2918 _colspec = coercions.expect(roles.DDLReferredColumnRole, argument)
2920 if isinstance(_colspec, str):
2921 _table_column = None
2922 else:
2923 assert isinstance(_colspec, ColumnClause)
2924 _table_column = _colspec
2926 if not isinstance(_table_column.table, (type(None), TableClause)):
2927 raise exc.ArgumentError(
2928 "ForeignKey received Column not bound "
2929 "to a Table, got: %r" % _table_column.table
2930 )
2932 return _colspec, _table_column
2934 def __repr__(self) -> str:
2935 return "ForeignKey(%r)" % self._get_colspec()
2937 @util.deprecated(
2938 "1.4",
2939 "The :meth:`_schema.ForeignKey.copy` method is deprecated "
2940 "and will be removed in a future release.",
2941 )
2942 def copy(self, *, schema: Optional[str] = None, **kw: Any) -> ForeignKey:
2943 return self._copy(schema=schema, **kw)
2945 def _copy(self, *, schema: Optional[str] = None, **kw: Any) -> ForeignKey:
2946 """Produce a copy of this :class:`_schema.ForeignKey` object.
2948 The new :class:`_schema.ForeignKey` will not be bound
2949 to any :class:`_schema.Column`.
2951 This method is usually used by the internal
2952 copy procedures of :class:`_schema.Column`, :class:`_schema.Table`,
2953 and :class:`_schema.MetaData`.
2955 :param schema: The returned :class:`_schema.ForeignKey` will
2956 reference the original table and column name, qualified
2957 by the given string schema name.
2959 """
2960 fk = ForeignKey(
2961 self._get_colspec(schema=schema),
2962 use_alter=self.use_alter,
2963 name=self.name,
2964 onupdate=self.onupdate,
2965 ondelete=self.ondelete,
2966 deferrable=self.deferrable,
2967 initially=self.initially,
2968 link_to_name=self.link_to_name,
2969 match=self.match,
2970 comment=self.comment,
2971 **self._unvalidated_dialect_kw,
2972 )
2973 return self._schema_item_copy(fk)
2975 def _get_colspec(
2976 self,
2977 schema: Optional[
2978 Union[
2979 str,
2980 Literal[SchemaConst.RETAIN_SCHEMA, SchemaConst.BLANK_SCHEMA],
2981 ]
2982 ] = None,
2983 table_name: Optional[str] = None,
2984 _is_copy: bool = False,
2985 ) -> str:
2986 """Return a string based 'column specification' for this
2987 :class:`_schema.ForeignKey`.
2989 This is usually the equivalent of the string-based "tablename.colname"
2990 argument first passed to the object's constructor.
2992 """
2994 _colspec, effective_table_column = self._resolve_colspec_argument()
2996 if schema not in (None, RETAIN_SCHEMA):
2997 _schema, tname, colname = self._column_tokens
2998 if table_name is not None:
2999 tname = table_name
3000 if schema is BLANK_SCHEMA:
3001 return "%s.%s" % (tname, colname)
3002 else:
3003 return "%s.%s.%s" % (schema, tname, colname)
3004 elif table_name:
3005 schema, tname, colname = self._column_tokens
3006 if schema:
3007 return "%s.%s.%s" % (schema, table_name, colname)
3008 else:
3009 return "%s.%s" % (table_name, colname)
3010 elif effective_table_column is not None:
3011 if effective_table_column.table is None:
3012 if _is_copy:
3013 raise exc.InvalidRequestError(
3014 f"Can't copy ForeignKey object which refers to "
3015 f"non-table bound Column {effective_table_column!r}"
3016 )
3017 else:
3018 return effective_table_column.key
3019 return "%s.%s" % (
3020 effective_table_column.table.fullname,
3021 effective_table_column.key,
3022 )
3023 else:
3024 assert isinstance(_colspec, str)
3025 return _colspec
3027 @property
3028 def _referred_schema(self) -> Optional[str]:
3029 return self._column_tokens[0]
3031 def _table_key_within_construction(self) -> Any:
3032 """get the table key but only safely"""
3034 if self._table_column is not None:
3035 if self._table_column.table is None:
3036 return None
3037 else:
3038 return self._table_column.table.key
3039 else:
3040 schema, tname, colname = self._column_tokens
3041 return _get_table_key(tname, schema)
3043 target_fullname = property(_get_colspec)
3045 def references(self, table: Table) -> bool:
3046 """Return True if the given :class:`_schema.Table`
3047 is referenced by this
3048 :class:`_schema.ForeignKey`."""
3050 return table.corresponding_column(self.column) is not None
3052 def get_referent(self, table: FromClause) -> Optional[Column[Any]]:
3053 """Return the :class:`_schema.Column` in the given
3054 :class:`_schema.Table` (or any :class:`.FromClause`)
3055 referenced by this :class:`_schema.ForeignKey`.
3057 Returns None if this :class:`_schema.ForeignKey`
3058 does not reference the given
3059 :class:`_schema.Table`.
3061 """
3062 # our column is a Column, and any subquery etc. proxying us
3063 # would be doing so via another Column, so that's what would
3064 # be returned here
3065 return table.columns.corresponding_column(self.column) # type: ignore
3067 @util.memoized_property
3068 def _column_tokens(self) -> Tuple[Optional[str], str, Optional[str]]:
3069 """parse a string-based _colspec into its component parts."""
3071 m = self._get_colspec().split(".")
3072 if len(m) == 1:
3073 tname = m.pop()
3074 colname = None
3075 else:
3076 colname = m.pop()
3077 tname = m.pop()
3079 # A FK between column 'bar' and table 'foo' can be
3080 # specified as 'foo', 'foo.bar', 'dbo.foo.bar',
3081 # 'otherdb.dbo.foo.bar'. Once we have the column name and
3082 # the table name, treat everything else as the schema
3083 # name. Some databases (e.g. Sybase) support
3084 # inter-database foreign keys. See tickets#1341 and --
3085 # indirectly related -- Ticket #594. This assumes that '.'
3086 # will never appear *within* any component of the FK.
3088 if len(m) > 0:
3089 schema = ".".join(m)
3090 else:
3091 schema = None
3092 return schema, tname, colname
3094 def _resolve_col_tokens(self) -> Tuple[Table, str, Optional[str]]:
3095 if self.parent is None:
3096 raise exc.InvalidRequestError(
3097 "this ForeignKey object does not yet have a "
3098 "parent Column associated with it."
3099 )
3101 elif self.parent.table is None:
3102 raise exc.InvalidRequestError(
3103 "this ForeignKey's parent column is not yet associated "
3104 "with a Table."
3105 )
3107 parenttable = self.parent.table
3109 if self._unresolvable:
3110 schema, tname, colname = self._column_tokens
3111 tablekey = _get_table_key(tname, schema)
3112 return parenttable, tablekey, colname
3114 # assertion
3115 # basically Column._make_proxy() sends the actual
3116 # target Column to the ForeignKey object, so the
3117 # string resolution here is never called.
3118 for c in self.parent.base_columns:
3119 if isinstance(c, Column):
3120 assert c.table is parenttable
3121 break
3122 else:
3123 assert False
3124 ######################
3126 schema, tname, colname = self._column_tokens
3128 if schema is None and parenttable.metadata.schema is not None:
3129 schema = parenttable.metadata.schema
3131 tablekey = _get_table_key(tname, schema)
3132 return parenttable, tablekey, colname
3134 def _link_to_col_by_colstring(
3135 self, parenttable: Table, table: Table, colname: Optional[str]
3136 ) -> Column[Any]:
3137 _column = None
3138 if colname is None:
3139 # colname is None in the case that ForeignKey argument
3140 # was specified as table name only, in which case we
3141 # match the column name to the same column on the
3142 # parent.
3143 # this use case wasn't working in later 1.x series
3144 # as it had no test coverage; fixed in 2.0
3145 parent = self.parent
3146 assert parent is not None
3147 key = parent.key
3148 _column = table.c.get(key, None)
3149 elif self.link_to_name:
3150 key = colname
3151 for c in table.c:
3152 if c.name == colname:
3153 _column = c
3154 else:
3155 key = colname
3156 _column = table.c.get(colname, None)
3158 if _column is None:
3159 raise exc.NoReferencedColumnError(
3160 "Could not initialize target column "
3161 f"for ForeignKey '{self._get_colspec()}' "
3162 f"on table '{parenttable.name}': "
3163 f"table '{table.name}' has no column named '{key}'",
3164 table.name,
3165 key,
3166 )
3168 return _column
3170 def _set_target_column(self, column: Column[Any]) -> None:
3171 assert self.parent is not None
3173 # propagate TypeEngine to parent if it didn't have one
3174 if self.parent.type._isnull:
3175 self.parent.type = column.type
3177 # super-edgy case, if other FKs point to our column,
3178 # they'd get the type propagated out also.
3180 def set_type(fk: ForeignKey) -> None:
3181 if fk.parent.type._isnull:
3182 fk.parent.type = column.type
3184 self.parent._setup_on_memoized_fks(set_type)
3186 self.column = column # type: ignore
3188 @util.ro_memoized_property
3189 def column(self) -> Column[Any]:
3190 """Return the target :class:`_schema.Column` referenced by this
3191 :class:`_schema.ForeignKey`.
3193 If no target column has been established, an exception
3194 is raised.
3196 """
3197 return self._resolve_column()
3199 @overload
3200 def _resolve_column(
3201 self, *, raiseerr: Literal[True] = ...
3202 ) -> Column[Any]: ...
3204 @overload
3205 def _resolve_column(
3206 self, *, raiseerr: bool = ...
3207 ) -> Optional[Column[Any]]: ...
3209 def _resolve_column(
3210 self, *, raiseerr: bool = True
3211 ) -> Optional[Column[Any]]:
3212 _column: Column[Any]
3214 _colspec, effective_table_column = self._resolve_colspec_argument()
3216 if isinstance(_colspec, str):
3217 parenttable, tablekey, colname = self._resolve_col_tokens()
3219 if self._unresolvable or tablekey not in parenttable.metadata:
3220 if not raiseerr:
3221 return None
3222 raise exc.NoReferencedTableError(
3223 f"Foreign key associated with column "
3224 f"'{self.parent}' could not find "
3225 f"table '{tablekey}' with which to generate a "
3226 f"foreign key to target column '{colname}'",
3227 tablekey,
3228 )
3229 elif parenttable.key not in parenttable.metadata:
3230 if not raiseerr:
3231 return None
3232 raise exc.InvalidRequestError(
3233 f"Table {parenttable} is no longer associated with its "
3234 "parent MetaData"
3235 )
3236 else:
3237 table = parenttable.metadata.tables[tablekey]
3238 return self._link_to_col_by_colstring(
3239 parenttable, table, colname
3240 )
3242 elif hasattr(_colspec, "__clause_element__"):
3243 _column = _colspec.__clause_element__()
3244 return _column
3245 else:
3246 assert isinstance(_colspec, Column)
3247 _column = _colspec
3248 return _column
3250 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
3251 assert isinstance(parent, Column)
3253 if self.parent is not None and self.parent is not parent:
3254 raise exc.InvalidRequestError(
3255 "This ForeignKey already has a parent !"
3256 )
3257 self.parent = parent
3258 self.parent.foreign_keys.add(self)
3259 self.parent._on_table_attach(self._set_table)
3261 def _set_remote_table(self, table: Table) -> None:
3262 parenttable, _, colname = self._resolve_col_tokens()
3263 _column = self._link_to_col_by_colstring(parenttable, table, colname)
3264 self._set_target_column(_column)
3265 assert self.constraint is not None
3266 self.constraint._validate_dest_table(table)
3268 def _remove_from_metadata(self, metadata: MetaData) -> None:
3269 parenttable, table_key, colname = self._resolve_col_tokens()
3270 fk_key = (table_key, colname)
3272 if self in metadata._fk_memos[fk_key]:
3273 # TODO: no test coverage for self not in memos
3274 metadata._fk_memos[fk_key].remove(self)
3276 def _set_table(self, column: Column[Any], table: Table) -> None:
3277 # standalone ForeignKey - create ForeignKeyConstraint
3278 # on the hosting Table when attached to the Table.
3279 assert isinstance(table, Table)
3280 if self.constraint is None:
3281 self.constraint = ForeignKeyConstraint(
3282 [],
3283 [],
3284 use_alter=self.use_alter,
3285 name=self.name,
3286 onupdate=self.onupdate,
3287 ondelete=self.ondelete,
3288 deferrable=self.deferrable,
3289 initially=self.initially,
3290 match=self.match,
3291 comment=self.comment,
3292 **self._unvalidated_dialect_kw,
3293 )
3294 self.constraint._append_element(column, self)
3295 self.constraint._set_parent_with_dispatch(table)
3296 table.foreign_keys.add(self)
3297 # set up remote ".column" attribute, or a note to pick it
3298 # up when the other Table/Column shows up
3300 _colspec, _ = self._resolve_colspec_argument()
3301 if isinstance(_colspec, str):
3302 parenttable, table_key, colname = self._resolve_col_tokens()
3303 fk_key = (table_key, colname)
3304 if table_key in parenttable.metadata.tables:
3305 table = parenttable.metadata.tables[table_key]
3306 try:
3307 _column = self._link_to_col_by_colstring(
3308 parenttable, table, colname
3309 )
3310 except exc.NoReferencedColumnError:
3311 # this is OK, we'll try later
3312 pass
3313 else:
3314 self._set_target_column(_column)
3316 parenttable.metadata._fk_memos[fk_key].append(self)
3317 elif hasattr(_colspec, "__clause_element__"):
3318 _column = _colspec.__clause_element__()
3319 self._set_target_column(_column)
3320 else:
3321 self._set_target_column(_colspec)
3324if TYPE_CHECKING:
3326 def default_is_sequence(
3327 obj: Optional[DefaultGenerator],
3328 ) -> TypeGuard[Sequence]: ...
3330 def default_is_clause_element(
3331 obj: Optional[DefaultGenerator],
3332 ) -> TypeGuard[ColumnElementColumnDefault]: ...
3334 def default_is_scalar(
3335 obj: Optional[DefaultGenerator],
3336 ) -> TypeGuard[ScalarElementColumnDefault]: ...
3338else:
3339 default_is_sequence = operator.attrgetter("is_sequence")
3341 default_is_clause_element = operator.attrgetter("is_clause_element")
3343 default_is_scalar = operator.attrgetter("is_scalar")
3346class DefaultGenerator(Executable, SchemaItem):
3347 """Base class for column *default* values.
3349 This object is only present on column.default or column.onupdate.
3350 It's not valid as a server default.
3352 """
3354 __visit_name__ = "default_generator"
3356 _is_default_generator = True
3357 is_sequence = False
3358 is_identity = False
3359 is_server_default = False
3360 is_clause_element = False
3361 is_callable = False
3362 is_scalar = False
3363 has_arg = False
3364 is_sentinel = False
3365 column: Optional[Column[Any]]
3367 def __init__(self, for_update: bool = False) -> None:
3368 self.for_update = for_update
3370 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
3371 if TYPE_CHECKING:
3372 assert isinstance(parent, Column)
3373 self.column = parent
3374 if self.for_update:
3375 self.column.onupdate = self
3376 else:
3377 self.column.default = self
3379 def _copy(self) -> DefaultGenerator:
3380 raise NotImplementedError()
3382 def _execute_on_connection(
3383 self,
3384 connection: Connection,
3385 distilled_params: _CoreMultiExecuteParams,
3386 execution_options: CoreExecuteOptionsParameter,
3387 ) -> Any:
3388 util.warn_deprecated(
3389 "Using the .execute() method to invoke a "
3390 "DefaultGenerator object is deprecated; please use "
3391 "the .scalar() method.",
3392 "2.0",
3393 )
3394 return self._execute_on_scalar(
3395 connection, distilled_params, execution_options
3396 )
3398 def _execute_on_scalar(
3399 self,
3400 connection: Connection,
3401 distilled_params: _CoreMultiExecuteParams,
3402 execution_options: CoreExecuteOptionsParameter,
3403 ) -> Any:
3404 return connection._execute_default(
3405 self, distilled_params, execution_options
3406 )
3409class ColumnDefault(DefaultGenerator, ABC):
3410 """A plain default value on a column.
3412 This could correspond to a constant, a callable function,
3413 or a SQL clause.
3415 :class:`.ColumnDefault` is generated automatically
3416 whenever the ``default``, ``onupdate`` arguments of
3417 :class:`_schema.Column` are used. A :class:`.ColumnDefault`
3418 can be passed positionally as well.
3420 For example, the following::
3422 Column('foo', Integer, default=50)
3424 Is equivalent to::
3426 Column('foo', Integer, ColumnDefault(50))
3429 """
3431 arg: Any
3433 @overload
3434 def __new__(
3435 cls, arg: Callable[..., Any], for_update: bool = ...
3436 ) -> CallableColumnDefault: ...
3438 @overload
3439 def __new__(
3440 cls, arg: ColumnElement[Any], for_update: bool = ...
3441 ) -> ColumnElementColumnDefault: ...
3443 # if I return ScalarElementColumnDefault here, which is what's actually
3444 # returned, mypy complains that
3445 # overloads overlap w/ incompatible return types.
3446 @overload
3447 def __new__(cls, arg: object, for_update: bool = ...) -> ColumnDefault: ...
3449 def __new__(
3450 cls, arg: Any = None, for_update: bool = False
3451 ) -> ColumnDefault:
3452 """Construct a new :class:`.ColumnDefault`.
3455 :param arg: argument representing the default value.
3456 May be one of the following:
3458 * a plain non-callable Python value, such as a
3459 string, integer, boolean, or other simple type.
3460 The default value will be used as is each time.
3461 * a SQL expression, that is one which derives from
3462 :class:`_expression.ColumnElement`. The SQL expression will
3463 be rendered into the INSERT or UPDATE statement,
3464 or in the case of a primary key column when
3465 RETURNING is not used may be
3466 pre-executed before an INSERT within a SELECT.
3467 * A Python callable. The function will be invoked for each
3468 new row subject to an INSERT or UPDATE.
3469 The callable must accept exactly
3470 zero or one positional arguments. The one-argument form
3471 will receive an instance of the :class:`.ExecutionContext`,
3472 which provides contextual information as to the current
3473 :class:`_engine.Connection` in use as well as the current
3474 statement and parameters.
3476 """
3478 if isinstance(arg, FetchedValue):
3479 raise exc.ArgumentError(
3480 "ColumnDefault may not be a server-side default type."
3481 )
3482 elif callable(arg):
3483 cls = CallableColumnDefault
3484 elif isinstance(arg, ClauseElement):
3485 cls = ColumnElementColumnDefault
3486 elif arg is not None:
3487 cls = ScalarElementColumnDefault
3489 return object.__new__(cls)
3491 def __repr__(self) -> str:
3492 return f"{self.__class__.__name__}({self.arg!r})"
3495class ScalarElementColumnDefault(ColumnDefault):
3496 """default generator for a fixed scalar Python value
3498 .. versionadded: 2.0
3500 """
3502 is_scalar = True
3503 has_arg = True
3505 def __init__(self, arg: Any, for_update: bool = False) -> None:
3506 self.for_update = for_update
3507 self.arg = arg
3509 def _copy(self) -> ScalarElementColumnDefault:
3510 return ScalarElementColumnDefault(
3511 arg=self.arg, for_update=self.for_update
3512 )
3515class _InsertSentinelColumnDefault(ColumnDefault):
3516 """Default generator that's specific to the use of a "sentinel" column
3517 when using the insertmanyvalues feature.
3519 This default is used as part of the :func:`_schema.insert_sentinel`
3520 construct.
3522 """
3524 is_sentinel = True
3525 for_update = False
3526 arg = None
3528 def __new__(cls) -> _InsertSentinelColumnDefault:
3529 return object.__new__(cls)
3531 def __init__(self) -> None:
3532 pass
3534 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
3535 col = cast("Column[Any]", parent)
3536 if not col._insert_sentinel:
3537 raise exc.ArgumentError(
3538 "The _InsertSentinelColumnDefault may only be applied to a "
3539 "Column marked as insert_sentinel=True"
3540 )
3541 elif not col.nullable:
3542 raise exc.ArgumentError(
3543 "The _InsertSentinelColumnDefault may only be applied to a "
3544 "Column that is nullable"
3545 )
3547 super()._set_parent(parent, **kw)
3549 def _copy(self) -> _InsertSentinelColumnDefault:
3550 return _InsertSentinelColumnDefault()
3553_SQLExprDefault = Union["ColumnElement[Any]", "TextClause"]
3556class ColumnElementColumnDefault(ColumnDefault):
3557 """default generator for a SQL expression
3559 .. versionadded:: 2.0
3561 """
3563 is_clause_element = True
3564 has_arg = True
3565 arg: _SQLExprDefault
3567 def __init__(
3568 self,
3569 arg: _SQLExprDefault,
3570 for_update: bool = False,
3571 ) -> None:
3572 self.for_update = for_update
3573 self.arg = arg
3575 def _copy(self) -> ColumnElementColumnDefault:
3576 return ColumnElementColumnDefault(
3577 arg=self.arg, for_update=self.for_update
3578 )
3580 @util.memoized_property
3581 @util.preload_module("sqlalchemy.sql.sqltypes")
3582 def _arg_is_typed(self) -> bool:
3583 sqltypes = util.preloaded.sql_sqltypes
3585 return not isinstance(self.arg.type, sqltypes.NullType)
3588class _CallableColumnDefaultProtocol(Protocol):
3589 def __call__(self, context: ExecutionContext) -> Any: ...
3592class CallableColumnDefault(ColumnDefault):
3593 """default generator for a callable Python function
3595 .. versionadded:: 2.0
3597 """
3599 is_callable = True
3600 arg: _CallableColumnDefaultProtocol
3601 has_arg = True
3603 def __init__(
3604 self,
3605 arg: Union[_CallableColumnDefaultProtocol, Callable[[], Any]],
3606 for_update: bool = False,
3607 ) -> None:
3608 self.for_update = for_update
3609 self.arg = self._maybe_wrap_callable(arg)
3611 def _copy(self) -> CallableColumnDefault:
3612 return CallableColumnDefault(arg=self.arg, for_update=self.for_update)
3614 def _maybe_wrap_callable(
3615 self, fn: Union[_CallableColumnDefaultProtocol, Callable[[], Any]]
3616 ) -> _CallableColumnDefaultProtocol:
3617 """Wrap callables that don't accept a context.
3619 This is to allow easy compatibility with default callables
3620 that aren't specific to accepting of a context.
3622 """
3624 try:
3625 argspec = util.get_callable_argspec(fn, no_self=True)
3626 except TypeError:
3627 return util.wrap_callable(lambda ctx: fn(), fn) # type: ignore
3629 defaulted = argspec[3] is not None and len(argspec[3]) or 0
3630 positionals = len(argspec[0]) - defaulted
3632 if positionals == 0:
3633 return util.wrap_callable(lambda ctx: fn(), fn) # type: ignore
3635 elif positionals == 1:
3636 return fn # type: ignore
3637 else:
3638 raise exc.ArgumentError(
3639 "ColumnDefault Python function takes zero or one "
3640 "positional arguments"
3641 )
3644class IdentityOptions(DialectKWArgs):
3645 """Defines options for a named database sequence or an identity column.
3647 .. versionadded:: 1.3.18
3649 .. seealso::
3651 :class:`.Sequence`
3653 """
3655 def __init__(
3656 self,
3657 start: Optional[int] = None,
3658 increment: Optional[int] = None,
3659 minvalue: Optional[int] = None,
3660 maxvalue: Optional[int] = None,
3661 nominvalue: Optional[bool] = None,
3662 nomaxvalue: Optional[bool] = None,
3663 cycle: Optional[bool] = None,
3664 cache: Optional[int] = None,
3665 order: Optional[bool] = None,
3666 **dialect_kw: Any,
3667 ) -> None:
3668 """Construct a :class:`.IdentityOptions` object.
3670 See the :class:`.Sequence` documentation for a complete description
3671 of the parameters.
3673 :param start: the starting index of the sequence.
3674 :param increment: the increment value of the sequence.
3675 :param minvalue: the minimum value of the sequence.
3676 :param maxvalue: the maximum value of the sequence.
3677 :param nominvalue: no minimum value of the sequence.
3678 :param nomaxvalue: no maximum value of the sequence.
3679 :param cycle: allows the sequence to wrap around when the maxvalue
3680 or minvalue has been reached.
3681 :param cache: optional integer value; number of future values in the
3682 sequence which are calculated in advance.
3683 :param order: optional boolean value; if ``True``, renders the
3684 ORDER keyword.
3686 .. deprecated:: 2.1 Use ``oracle_order`` instead.
3688 """
3689 self.start = start
3690 self.increment = increment
3691 self.minvalue = minvalue
3692 self.maxvalue = maxvalue
3693 self.nominvalue = nominvalue
3694 self.nomaxvalue = nomaxvalue
3695 self.cycle = cycle
3696 self.cache = cache
3697 if order is not None:
3698 if "oracle_order" in dialect_kw:
3699 raise exc.ArgumentError(
3700 "Cannot specify both 'order' and 'oracle_order'. "
3701 "Plese use only 'oracle_order'."
3702 )
3703 dialect_kw["oracle_order"] = order
3704 self._validate_dialect_kwargs(dialect_kw)
3706 @property
3707 def _increment_is_negative(self) -> bool:
3708 return self.increment is not None and self.increment < 0
3710 @property
3711 def order(self) -> Optional[bool]:
3712 """Alias of the ``dialect_kwargs`` ``'oracle_order'``.
3714 .. deprecated:: 2.1 The 'order' attribute is deprecated.
3715 """
3716 value: Optional[bool] = self.dialect_kwargs.get("oracle_order")
3717 return value
3719 def _as_dict(self) -> Dict[str, Any]:
3720 return {
3721 k: v
3722 for k, v in {
3723 "start": self.start,
3724 "increment": self.increment,
3725 "minvalue": self.minvalue,
3726 "maxvalue": self.maxvalue,
3727 "nominvalue": self.nominvalue,
3728 "nomaxvalue": self.nomaxvalue,
3729 "cycle": self.cycle,
3730 "cache": self.cache,
3731 }.items()
3732 if v != None
3733 }
3736class Sequence(HasSchemaAttr, IdentityOptions, DefaultGenerator):
3737 """Represents a named database sequence.
3739 The :class:`.Sequence` object represents the name and configurational
3740 parameters of a database sequence. It also represents
3741 a construct that can be "executed" by a SQLAlchemy :class:`_engine.Engine`
3742 or :class:`_engine.Connection`,
3743 rendering the appropriate "next value" function
3744 for the target database and returning a result.
3746 The :class:`.Sequence` is typically associated with a primary key column::
3748 some_table = Table(
3749 'some_table', metadata,
3750 Column('id', Integer, Sequence('some_table_seq', start=1),
3751 primary_key=True)
3752 )
3754 When CREATE TABLE is emitted for the above :class:`_schema.Table`, if the
3755 target platform supports sequences, a CREATE SEQUENCE statement will
3756 be emitted as well. For platforms that don't support sequences,
3757 the :class:`.Sequence` construct is ignored.
3759 .. seealso::
3761 :ref:`defaults_sequences`
3763 :class:`.CreateSequence`
3765 :class:`.DropSequence`
3767 """
3769 __visit_name__ = "sequence"
3771 is_sequence = True
3773 column: Optional[Column[Any]]
3774 data_type: Optional[TypeEngine[int]]
3776 @util.deprecated_params(
3777 order=(
3778 "2.1",
3779 "This parameter is supported only by Oracle, "
3780 "use ``oracle_order`` instead.",
3781 )
3782 )
3783 def __init__(
3784 self,
3785 name: str,
3786 start: Optional[int] = None,
3787 increment: Optional[int] = None,
3788 minvalue: Optional[int] = None,
3789 maxvalue: Optional[int] = None,
3790 nominvalue: Optional[bool] = None,
3791 nomaxvalue: Optional[bool] = None,
3792 cycle: Optional[bool] = None,
3793 schema: Optional[Union[str, Literal[SchemaConst.BLANK_SCHEMA]]] = None,
3794 cache: Optional[int] = None,
3795 order: Optional[bool] = None,
3796 data_type: Optional[_TypeEngineArgument[int]] = None,
3797 optional: bool = False,
3798 quote: Optional[bool] = None,
3799 metadata: Optional[MetaData] = None,
3800 quote_schema: Optional[bool] = None,
3801 for_update: bool = False,
3802 **dialect_kw: Any,
3803 ) -> None:
3804 """Construct a :class:`.Sequence` object.
3806 :param name: the name of the sequence.
3808 :param start: the starting index of the sequence. This value is
3809 used when the CREATE SEQUENCE command is emitted to the database
3810 as the value of the "START WITH" clause. If ``None``, the
3811 clause is omitted, which on most platforms indicates a starting
3812 value of 1.
3814 .. versionchanged:: 2.0 The :paramref:`.Sequence.start` parameter
3815 is required in order to have DDL emit "START WITH". This is a
3816 reversal of a change made in version 1.4 which would implicitly
3817 render "START WITH 1" if the :paramref:`.Sequence.start` were
3818 not included. See :ref:`change_7211` for more detail.
3820 :param increment: the increment value of the sequence. This
3821 value is used when the CREATE SEQUENCE command is emitted to
3822 the database as the value of the "INCREMENT BY" clause. If ``None``,
3823 the clause is omitted, which on most platforms indicates an
3824 increment of 1.
3825 :param minvalue: the minimum value of the sequence. This
3826 value is used when the CREATE SEQUENCE command is emitted to
3827 the database as the value of the "MINVALUE" clause. If ``None``,
3828 the clause is omitted, which on most platforms indicates a
3829 minvalue of 1 and -2^63-1 for ascending and descending sequences,
3830 respectively.
3832 :param maxvalue: the maximum value of the sequence. This
3833 value is used when the CREATE SEQUENCE command is emitted to
3834 the database as the value of the "MAXVALUE" clause. If ``None``,
3835 the clause is omitted, which on most platforms indicates a
3836 maxvalue of 2^63-1 and -1 for ascending and descending sequences,
3837 respectively.
3839 :param nominvalue: no minimum value of the sequence. This
3840 value is used when the CREATE SEQUENCE command is emitted to
3841 the database as the value of the "NO MINVALUE" clause. If ``None``,
3842 the clause is omitted, which on most platforms indicates a
3843 minvalue of 1 and -2^63-1 for ascending and descending sequences,
3844 respectively.
3846 :param nomaxvalue: no maximum value of the sequence. This
3847 value is used when the CREATE SEQUENCE command is emitted to
3848 the database as the value of the "NO MAXVALUE" clause. If ``None``,
3849 the clause is omitted, which on most platforms indicates a
3850 maxvalue of 2^63-1 and -1 for ascending and descending sequences,
3851 respectively.
3853 :param cycle: allows the sequence to wrap around when the maxvalue
3854 or minvalue has been reached by an ascending or descending sequence
3855 respectively. This value is used when the CREATE SEQUENCE command
3856 is emitted to the database as the "CYCLE" clause. If the limit is
3857 reached, the next number generated will be the minvalue or maxvalue,
3858 respectively. If cycle=False (the default) any calls to nextval
3859 after the sequence has reached its maximum value will return an
3860 error.
3862 :param schema: optional schema name for the sequence, if located
3863 in a schema other than the default. The rules for selecting the
3864 schema name when a :class:`_schema.MetaData`
3865 is also present are the same
3866 as that of :paramref:`_schema.Table.schema`.
3868 :param cache: optional integer value; number of future values in the
3869 sequence which are calculated in advance. Renders the CACHE keyword
3870 understood by Oracle and PostgreSQL.
3872 :param order: optional boolean value; if ``True``, renders the
3873 ORDER keyword, understood by Oracle, indicating the sequence is
3874 definitively ordered. May be necessary to provide deterministic
3875 ordering using Oracle RAC.
3877 :param data_type: The type to be returned by the sequence, for
3878 dialects that allow us to choose between INTEGER, BIGINT, etc.
3879 (e.g., mssql).
3881 .. versionadded:: 1.4.0
3883 :param optional: boolean value, when ``True``, indicates that this
3884 :class:`.Sequence` object only needs to be explicitly generated
3885 on backends that don't provide another way to generate primary
3886 key identifiers. Currently, it essentially means, "don't create
3887 this sequence on the PostgreSQL backend, where the SERIAL keyword
3888 creates a sequence for us automatically".
3889 :param quote: boolean value, when ``True`` or ``False``, explicitly
3890 forces quoting of the :paramref:`_schema.Sequence.name` on or off.
3891 When left at its default of ``None``, normal quoting rules based
3892 on casing and reserved words take place.
3893 :param quote_schema: Set the quoting preferences for the ``schema``
3894 name.
3896 :param metadata: optional :class:`_schema.MetaData` object which this
3897 :class:`.Sequence` will be associated with. A :class:`.Sequence`
3898 that is associated with a :class:`_schema.MetaData`
3899 gains the following
3900 capabilities:
3902 * The :class:`.Sequence` will inherit the
3903 :paramref:`_schema.MetaData.schema`
3904 parameter specified to the target :class:`_schema.MetaData`, which
3905 affects the production of CREATE / DROP DDL, if any.
3907 * The :meth:`.Sequence.create` and :meth:`.Sequence.drop` methods
3908 automatically use the engine bound to the :class:`_schema.MetaData`
3909 object, if any.
3911 * The :meth:`_schema.MetaData.create_all` and
3912 :meth:`_schema.MetaData.drop_all`
3913 methods will emit CREATE / DROP for this :class:`.Sequence`,
3914 even if the :class:`.Sequence` is not associated with any
3915 :class:`_schema.Table` / :class:`_schema.Column`
3916 that's a member of this
3917 :class:`_schema.MetaData`.
3919 The above behaviors can only occur if the :class:`.Sequence` is
3920 explicitly associated with the :class:`_schema.MetaData`
3921 via this parameter.
3923 .. seealso::
3925 :ref:`sequence_metadata` - full discussion of the
3926 :paramref:`.Sequence.metadata` parameter.
3928 :param for_update: Indicates this :class:`.Sequence`, when associated
3929 with a :class:`_schema.Column`,
3930 should be invoked for UPDATE statements
3931 on that column's table, rather than for INSERT statements, when
3932 no value is otherwise present for that column in the statement.
3934 """
3935 DefaultGenerator.__init__(self, for_update=for_update)
3936 IdentityOptions.__init__(
3937 self,
3938 start=start,
3939 increment=increment,
3940 minvalue=minvalue,
3941 maxvalue=maxvalue,
3942 nominvalue=nominvalue,
3943 nomaxvalue=nomaxvalue,
3944 cycle=cycle,
3945 cache=cache,
3946 order=order,
3947 **dialect_kw,
3948 )
3949 self.column = None
3950 self.name = quoted_name(name, quote)
3951 self.optional = optional
3952 if schema is BLANK_SCHEMA:
3953 self.schema = schema = None
3954 elif metadata is not None and schema is None and metadata.schema:
3955 self.schema = schema = metadata.schema
3956 else:
3957 self.schema = quoted_name.construct(schema, quote_schema)
3958 self.metadata = metadata
3959 self._key = _get_table_key(name, schema)
3960 if metadata:
3961 self._set_metadata(metadata)
3962 if data_type is not None:
3963 self.data_type = to_instance(data_type)
3964 else:
3965 self.data_type = None
3967 @util.preload_module("sqlalchemy.sql.functions")
3968 def next_value(self) -> Function[int]:
3969 """Return a :class:`.next_value` function element
3970 which will render the appropriate increment function
3971 for this :class:`.Sequence` within any SQL expression.
3973 """
3974 return util.preloaded.sql_functions.func.next_value(self)
3976 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
3977 column = parent
3978 assert isinstance(column, Column)
3979 super()._set_parent(column)
3980 column._on_table_attach(self._set_table)
3982 def _copy(self) -> Sequence:
3983 return Sequence(
3984 name=self.name,
3985 schema=self.schema,
3986 data_type=self.data_type,
3987 optional=self.optional,
3988 metadata=self.metadata,
3989 for_update=self.for_update,
3990 **self._as_dict(),
3991 **self.dialect_kwargs,
3992 )
3994 def _set_table(self, column: Column[Any], table: Table) -> None:
3995 self._set_metadata(table.metadata)
3997 def _set_metadata(self, metadata: MetaData) -> None:
3998 self.metadata = metadata
3999 self.metadata._sequences[self._key] = self
4001 def create(self, bind: _CreateDropBind, checkfirst: bool = True) -> None:
4002 """Creates this sequence in the database."""
4004 bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
4006 def drop(self, bind: _CreateDropBind, checkfirst: bool = True) -> None:
4007 """Drops this sequence from the database."""
4009 bind._run_ddl_visitor(ddl.SchemaDropper, self, checkfirst=checkfirst)
4011 def _not_a_column_expr(self) -> NoReturn:
4012 raise exc.InvalidRequestError(
4013 f"This {self.__class__.__name__} cannot be used directly "
4014 "as a column expression. Use func.next_value(sequence) "
4015 "to produce a 'next value' function that's usable "
4016 "as a column element."
4017 )
4020@inspection._self_inspects
4021class FetchedValue(SchemaEventTarget):
4022 """A marker for a transparent database-side default.
4024 Use :class:`.FetchedValue` when the database is configured
4025 to provide some automatic default for a column.
4027 E.g.::
4029 Column('foo', Integer, FetchedValue())
4031 Would indicate that some trigger or default generator
4032 will create a new value for the ``foo`` column during an
4033 INSERT.
4035 .. seealso::
4037 :ref:`triggered_columns`
4039 """
4041 is_server_default = True
4042 reflected = False
4043 has_argument = False
4044 is_clause_element = False
4045 is_identity = False
4047 column: Optional[Column[Any]]
4049 def __init__(self, for_update: bool = False) -> None:
4050 self.for_update = for_update
4052 def _as_for_update(self, for_update: bool) -> FetchedValue:
4053 if for_update == self.for_update:
4054 return self
4055 else:
4056 return self._clone(for_update)
4058 def _copy(self) -> FetchedValue:
4059 return FetchedValue(self.for_update)
4061 def _clone(self, for_update: bool) -> Self:
4062 n = self.__class__.__new__(self.__class__)
4063 n.__dict__.update(self.__dict__)
4064 n.__dict__.pop("column", None)
4065 n.for_update = for_update
4066 return n
4068 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4069 column = parent
4070 assert isinstance(column, Column)
4071 self.column = column
4072 if self.for_update:
4073 self.column.server_onupdate = self
4074 else:
4075 self.column.server_default = self
4077 def __repr__(self) -> str:
4078 return util.generic_repr(self)
4081class DefaultClause(FetchedValue):
4082 """A DDL-specified DEFAULT column value.
4084 :class:`.DefaultClause` is a :class:`.FetchedValue`
4085 that also generates a "DEFAULT" clause when
4086 "CREATE TABLE" is emitted.
4088 :class:`.DefaultClause` is generated automatically
4089 whenever the ``server_default``, ``server_onupdate`` arguments of
4090 :class:`_schema.Column` are used. A :class:`.DefaultClause`
4091 can be passed positionally as well.
4093 For example, the following::
4095 Column('foo', Integer, server_default="50")
4097 Is equivalent to::
4099 Column('foo', Integer, DefaultClause("50"))
4101 """
4103 has_argument = True
4105 def __init__(
4106 self,
4107 arg: Union[str, ClauseElement, TextClause],
4108 for_update: bool = False,
4109 _reflected: bool = False,
4110 ) -> None:
4111 util.assert_arg_type(arg, (str, ClauseElement, TextClause), "arg")
4112 super().__init__(for_update)
4113 self.arg = arg
4114 self.reflected = _reflected
4116 def _copy(self) -> DefaultClause:
4117 return DefaultClause(
4118 arg=self.arg, for_update=self.for_update, _reflected=self.reflected
4119 )
4121 def __repr__(self) -> str:
4122 return "DefaultClause(%r, for_update=%r)" % (self.arg, self.for_update)
4125class Constraint(DialectKWArgs, HasConditionalDDL, SchemaItem):
4126 """A table-level SQL constraint.
4128 :class:`_schema.Constraint` serves as the base class for the series of
4129 constraint objects that can be associated with :class:`_schema.Table`
4130 objects, including :class:`_schema.PrimaryKeyConstraint`,
4131 :class:`_schema.ForeignKeyConstraint`
4132 :class:`_schema.UniqueConstraint`, and
4133 :class:`_schema.CheckConstraint`.
4135 """
4137 __visit_name__ = "constraint"
4139 _creation_order: int
4140 _column_flag: bool
4142 def __init__(
4143 self,
4144 name: _ConstraintNameArgument = None,
4145 deferrable: Optional[bool] = None,
4146 initially: Optional[str] = None,
4147 info: Optional[_InfoType] = None,
4148 comment: Optional[str] = None,
4149 _create_rule: Optional[Any] = None,
4150 _type_bound: bool = False,
4151 **dialect_kw: Any,
4152 ) -> None:
4153 r"""Create a SQL constraint.
4155 :param name:
4156 Optional, the in-database name of this ``Constraint``.
4158 :param deferrable:
4159 Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
4160 issuing DDL for this constraint.
4162 :param initially:
4163 Optional string. If set, emit INITIALLY <value> when issuing DDL
4164 for this constraint.
4166 :param info: Optional data dictionary which will be populated into the
4167 :attr:`.SchemaItem.info` attribute of this object.
4169 :param comment: Optional string that will render an SQL comment on
4170 foreign key constraint creation.
4172 .. versionadded:: 2.0
4174 :param \**dialect_kw: Additional keyword arguments are dialect
4175 specific, and passed in the form ``<dialectname>_<argname>``. See
4176 the documentation regarding an individual dialect at
4177 :ref:`dialect_toplevel` for detail on documented arguments.
4179 :param _create_rule:
4180 used internally by some datatypes that also create constraints.
4182 :param _type_bound:
4183 used internally to indicate that this constraint is associated with
4184 a specific datatype.
4186 """
4188 self.name = name
4189 self.deferrable = deferrable
4190 self.initially = initially
4191 if info:
4192 self.info = info
4193 self._create_rule = _create_rule
4194 self._type_bound = _type_bound
4195 util.set_creation_order(self)
4196 self._validate_dialect_kwargs(dialect_kw)
4197 self.comment = comment
4199 def _should_create_for_compiler(
4200 self, compiler: DDLCompiler, **kw: Any
4201 ) -> bool:
4202 if self._create_rule is not None and not self._create_rule(compiler):
4203 return False
4204 elif self._ddl_if is not None:
4205 return self._ddl_if._should_execute(
4206 ddl.CreateConstraint(self), self, None, compiler=compiler, **kw
4207 )
4208 else:
4209 return True
4211 @property
4212 def table(self) -> Table:
4213 try:
4214 if isinstance(self.parent, Table):
4215 return self.parent
4216 except AttributeError:
4217 pass
4218 raise exc.InvalidRequestError(
4219 "This constraint is not bound to a table. Did you "
4220 "mean to call table.append_constraint(constraint) ?"
4221 )
4223 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4224 assert isinstance(parent, (Table, Column))
4225 self.parent = parent
4226 parent.constraints.add(self)
4228 @util.deprecated(
4229 "1.4",
4230 "The :meth:`_schema.Constraint.copy` method is deprecated "
4231 "and will be removed in a future release.",
4232 )
4233 def copy(self, **kw: Any) -> Self:
4234 return self._copy(**kw)
4236 def _copy(self, **kw: Any) -> Self:
4237 raise NotImplementedError()
4240class ColumnCollectionMixin:
4241 """A :class:`_expression.ColumnCollection` of :class:`_schema.Column`
4242 objects.
4244 This collection represents the columns which are referred to by
4245 this object.
4247 """
4249 _columns: DedupeColumnCollection[Column[Any]]
4251 _allow_multiple_tables = False
4253 _pending_colargs: List[Optional[Union[str, Column[Any]]]]
4255 if TYPE_CHECKING:
4257 def _set_parent_with_dispatch(
4258 self, parent: SchemaEventTarget, **kw: Any
4259 ) -> None: ...
4261 def __init__(
4262 self,
4263 *columns: _DDLColumnArgument,
4264 _autoattach: bool = True,
4265 _column_flag: bool = False,
4266 _gather_expressions: Optional[
4267 List[Union[str, ColumnElement[Any]]]
4268 ] = None,
4269 ) -> None:
4270 self._column_flag = _column_flag
4271 self._columns = DedupeColumnCollection()
4273 processed_expressions: Optional[
4274 List[Union[ColumnElement[Any], str]]
4275 ] = _gather_expressions
4277 if processed_expressions is not None:
4278 self._pending_colargs = []
4279 for (
4280 expr,
4281 _,
4282 _,
4283 add_element,
4284 ) in coercions.expect_col_expression_collection(
4285 roles.DDLConstraintColumnRole, columns
4286 ):
4287 self._pending_colargs.append(add_element)
4288 processed_expressions.append(expr)
4289 else:
4290 self._pending_colargs = [
4291 coercions.expect(roles.DDLConstraintColumnRole, column)
4292 for column in columns
4293 ]
4295 if _autoattach and self._pending_colargs:
4296 self._check_attach()
4298 def _check_attach(self, evt: bool = False) -> None:
4299 col_objs = [c for c in self._pending_colargs if isinstance(c, Column)]
4301 cols_w_table = [c for c in col_objs if isinstance(c.table, Table)]
4303 cols_wo_table = set(col_objs).difference(cols_w_table)
4304 if cols_wo_table:
4305 # feature #3341 - place event listeners for Column objects
4306 # such that when all those cols are attached, we autoattach.
4307 assert not evt, "Should not reach here on event call"
4309 # issue #3411 - don't do the per-column auto-attach if some of the
4310 # columns are specified as strings.
4311 has_string_cols = {
4312 c for c in self._pending_colargs if c is not None
4313 }.difference(col_objs)
4314 if not has_string_cols:
4316 def _col_attached(column: Column[Any], table: Table) -> None:
4317 # this isinstance() corresponds with the
4318 # isinstance() above; only want to count Table-bound
4319 # columns
4320 if isinstance(table, Table):
4321 cols_wo_table.discard(column)
4322 if not cols_wo_table:
4323 self._check_attach(evt=True)
4325 self._cols_wo_table = cols_wo_table
4326 for col in cols_wo_table:
4327 col._on_table_attach(_col_attached)
4328 return
4330 columns = cols_w_table
4332 tables = {c.table for c in columns}
4333 if len(tables) == 1:
4334 self._set_parent_with_dispatch(tables.pop())
4335 elif len(tables) > 1 and not self._allow_multiple_tables:
4336 table = columns[0].table
4337 others = [c for c in columns[1:] if c.table is not table]
4338 if others:
4339 # black could not format this inline
4340 other_str = ", ".join("'%s'" % c for c in others)
4341 raise exc.ArgumentError(
4342 f"Column(s) {other_str} "
4343 f"are not part of table '{table.description}'."
4344 )
4346 @util.ro_memoized_property
4347 def columns(self) -> ReadOnlyColumnCollection[str, Column[Any]]:
4348 return self._columns.as_readonly()
4350 @util.ro_memoized_property
4351 def c(self) -> ReadOnlyColumnCollection[str, Column[Any]]:
4352 return self._columns.as_readonly()
4354 def _col_expressions(
4355 self, parent: Union[Table, Column[Any]]
4356 ) -> List[Optional[Column[Any]]]:
4357 if isinstance(parent, Column):
4358 result: List[Optional[Column[Any]]] = [
4359 c for c in self._pending_colargs if isinstance(c, Column)
4360 ]
4361 assert len(result) == len(self._pending_colargs)
4362 return result
4363 else:
4364 try:
4365 return [
4366 parent.c[col] if isinstance(col, str) else col
4367 for col in self._pending_colargs
4368 ]
4369 except KeyError as ke:
4370 raise exc.ConstraintColumnNotFoundError(
4371 f"Can't create {self.__class__.__name__} "
4372 f"on table '{parent.description}': no column "
4373 f"named '{ke.args[0]}' is present."
4374 ) from ke
4376 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4377 assert isinstance(parent, (Table, Column))
4379 for col in self._col_expressions(parent):
4380 if col is not None:
4381 self._columns.add(col)
4384class ColumnCollectionConstraint(ColumnCollectionMixin, Constraint):
4385 """A constraint that proxies a ColumnCollection."""
4387 def __init__(
4388 self,
4389 *columns: _DDLColumnArgument,
4390 name: _ConstraintNameArgument = None,
4391 deferrable: Optional[bool] = None,
4392 initially: Optional[str] = None,
4393 info: Optional[_InfoType] = None,
4394 _autoattach: bool = True,
4395 _column_flag: bool = False,
4396 _gather_expressions: Optional[List[_DDLColumnArgument]] = None,
4397 **dialect_kw: Any,
4398 ) -> None:
4399 r"""
4400 :param \*columns:
4401 A sequence of column names or Column objects.
4403 :param name:
4404 Optional, the in-database name of this constraint.
4406 :param deferrable:
4407 Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
4408 issuing DDL for this constraint.
4410 :param initially:
4411 Optional string. If set, emit INITIALLY <value> when issuing DDL
4412 for this constraint.
4414 :param \**dialect_kw: other keyword arguments including
4415 dialect-specific arguments are propagated to the :class:`.Constraint`
4416 superclass.
4418 """
4419 Constraint.__init__(
4420 self,
4421 name=name,
4422 deferrable=deferrable,
4423 initially=initially,
4424 info=info,
4425 **dialect_kw,
4426 )
4427 ColumnCollectionMixin.__init__(
4428 self, *columns, _autoattach=_autoattach, _column_flag=_column_flag
4429 )
4431 columns: ReadOnlyColumnCollection[str, Column[Any]]
4432 """A :class:`_expression.ColumnCollection` representing the set of columns
4433 for this constraint.
4435 """
4437 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4438 assert isinstance(parent, (Column, Table))
4439 Constraint._set_parent(self, parent)
4440 ColumnCollectionMixin._set_parent(self, parent)
4442 def __contains__(self, x: Any) -> bool:
4443 return x in self._columns
4445 @util.deprecated(
4446 "1.4",
4447 "The :meth:`_schema.ColumnCollectionConstraint.copy` method "
4448 "is deprecated and will be removed in a future release.",
4449 )
4450 def copy(
4451 self,
4452 *,
4453 target_table: Optional[Table] = None,
4454 **kw: Any,
4455 ) -> ColumnCollectionConstraint:
4456 return self._copy(target_table=target_table, **kw)
4458 def _copy(
4459 self,
4460 *,
4461 target_table: Optional[Table] = None,
4462 **kw: Any,
4463 ) -> ColumnCollectionConstraint:
4464 # ticket #5276
4465 constraint_kwargs = {}
4466 for dialect_name in self.dialect_options:
4467 dialect_options = self.dialect_options[dialect_name]._non_defaults
4468 for (
4469 dialect_option_key,
4470 dialect_option_value,
4471 ) in dialect_options.items():
4472 constraint_kwargs[dialect_name + "_" + dialect_option_key] = (
4473 dialect_option_value
4474 )
4476 assert isinstance(self.parent, Table)
4477 c = self.__class__(
4478 name=self.name,
4479 deferrable=self.deferrable,
4480 initially=self.initially,
4481 *[
4482 _copy_expression(expr, self.parent, target_table)
4483 for expr in self._columns
4484 ],
4485 comment=self.comment,
4486 **constraint_kwargs,
4487 )
4488 return self._schema_item_copy(c)
4490 def contains_column(self, col: Column[Any]) -> bool:
4491 """Return True if this constraint contains the given column.
4493 Note that this object also contains an attribute ``.columns``
4494 which is a :class:`_expression.ColumnCollection` of
4495 :class:`_schema.Column` objects.
4497 """
4499 return self._columns.contains_column(col)
4501 def __iter__(self) -> Iterator[Column[Any]]:
4502 return iter(self._columns)
4504 def __len__(self) -> int:
4505 return len(self._columns)
4508class CheckConstraint(ColumnCollectionConstraint):
4509 """A table- or column-level CHECK constraint.
4511 Can be included in the definition of a Table or Column.
4512 """
4514 _allow_multiple_tables = True
4516 __visit_name__ = "table_or_column_check_constraint"
4518 @_document_text_coercion(
4519 "sqltext",
4520 ":class:`.CheckConstraint`",
4521 ":paramref:`.CheckConstraint.sqltext`",
4522 )
4523 def __init__(
4524 self,
4525 sqltext: _TextCoercedExpressionArgument[Any],
4526 name: _ConstraintNameArgument = None,
4527 deferrable: Optional[bool] = None,
4528 initially: Optional[str] = None,
4529 table: Optional[Table] = None,
4530 info: Optional[_InfoType] = None,
4531 _create_rule: Optional[Any] = None,
4532 _autoattach: bool = True,
4533 _type_bound: bool = False,
4534 **dialect_kw: Any,
4535 ) -> None:
4536 r"""Construct a CHECK constraint.
4538 :param sqltext:
4539 A string containing the constraint definition, which will be used
4540 verbatim, or a SQL expression construct. If given as a string,
4541 the object is converted to a :func:`_expression.text` object.
4542 If the textual
4543 string includes a colon character, escape this using a backslash::
4545 CheckConstraint(r"foo ~ E'a(?\:b|c)d")
4547 :param name:
4548 Optional, the in-database name of the constraint.
4550 :param deferrable:
4551 Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
4552 issuing DDL for this constraint.
4554 :param initially:
4555 Optional string. If set, emit INITIALLY <value> when issuing DDL
4556 for this constraint.
4558 :param info: Optional data dictionary which will be populated into the
4559 :attr:`.SchemaItem.info` attribute of this object.
4561 """
4563 self.sqltext = coercions.expect(roles.DDLExpressionRole, sqltext)
4564 columns: List[Column[Any]] = []
4565 visitors.traverse(self.sqltext, {}, {"column": columns.append})
4567 super().__init__(
4568 name=name,
4569 deferrable=deferrable,
4570 initially=initially,
4571 _create_rule=_create_rule,
4572 info=info,
4573 _type_bound=_type_bound,
4574 _autoattach=_autoattach,
4575 *columns,
4576 **dialect_kw,
4577 )
4578 if table is not None:
4579 self._set_parent_with_dispatch(table)
4581 @property
4582 def is_column_level(self) -> bool:
4583 return not isinstance(self.parent, Table)
4585 @util.deprecated(
4586 "1.4",
4587 "The :meth:`_schema.CheckConstraint.copy` method is deprecated "
4588 "and will be removed in a future release.",
4589 )
4590 def copy(
4591 self, *, target_table: Optional[Table] = None, **kw: Any
4592 ) -> CheckConstraint:
4593 return self._copy(target_table=target_table, **kw)
4595 def _copy(
4596 self, *, target_table: Optional[Table] = None, **kw: Any
4597 ) -> CheckConstraint:
4598 if target_table is not None:
4599 # note that target_table is None for the copy process of
4600 # a column-bound CheckConstraint, so this path is not reached
4601 # in that case.
4602 sqltext = _copy_expression(self.sqltext, self.table, target_table)
4603 else:
4604 sqltext = self.sqltext
4605 c = CheckConstraint(
4606 sqltext,
4607 name=self.name,
4608 initially=self.initially,
4609 deferrable=self.deferrable,
4610 _create_rule=self._create_rule,
4611 table=target_table,
4612 comment=self.comment,
4613 _autoattach=False,
4614 _type_bound=self._type_bound,
4615 )
4616 return self._schema_item_copy(c)
4619class ForeignKeyConstraint(ColumnCollectionConstraint):
4620 """A table-level FOREIGN KEY constraint.
4622 Defines a single column or composite FOREIGN KEY ... REFERENCES
4623 constraint. For a no-frills, single column foreign key, adding a
4624 :class:`_schema.ForeignKey` to the definition of a :class:`_schema.Column`
4625 is a
4626 shorthand equivalent for an unnamed, single column
4627 :class:`_schema.ForeignKeyConstraint`.
4629 Examples of foreign key configuration are in :ref:`metadata_foreignkeys`.
4631 """
4633 __visit_name__ = "foreign_key_constraint"
4635 def __init__(
4636 self,
4637 columns: _typing_Sequence[_DDLColumnArgument],
4638 refcolumns: _typing_Sequence[_DDLColumnReferenceArgument],
4639 name: _ConstraintNameArgument = None,
4640 onupdate: Optional[str] = None,
4641 ondelete: Optional[str] = None,
4642 deferrable: Optional[bool] = None,
4643 initially: Optional[str] = None,
4644 use_alter: bool = False,
4645 link_to_name: bool = False,
4646 match: Optional[str] = None,
4647 table: Optional[Table] = None,
4648 info: Optional[_InfoType] = None,
4649 comment: Optional[str] = None,
4650 **dialect_kw: Any,
4651 ) -> None:
4652 r"""Construct a composite-capable FOREIGN KEY.
4654 :param columns: A sequence of local column names. The named columns
4655 must be defined and present in the parent Table. The names should
4656 match the ``key`` given to each column (defaults to the name) unless
4657 ``link_to_name`` is True.
4659 :param refcolumns: A sequence of foreign column names or Column
4660 objects. The columns must all be located within the same Table.
4662 :param name: Optional, the in-database name of the key.
4664 :param onupdate: Optional string. If set, emit ON UPDATE <value> when
4665 issuing DDL for this constraint. Typical values include CASCADE,
4666 DELETE and RESTRICT.
4668 :param ondelete: Optional string. If set, emit ON DELETE <value> when
4669 issuing DDL for this constraint. Typical values include CASCADE,
4670 DELETE and RESTRICT.
4672 :param deferrable: Optional bool. If set, emit DEFERRABLE or NOT
4673 DEFERRABLE when issuing DDL for this constraint.
4675 :param initially: Optional string. If set, emit INITIALLY <value> when
4676 issuing DDL for this constraint.
4678 :param link_to_name: if True, the string name given in ``column`` is
4679 the rendered name of the referenced column, not its locally assigned
4680 ``key``.
4682 :param use_alter: If True, do not emit the DDL for this constraint as
4683 part of the CREATE TABLE definition. Instead, generate it via an
4684 ALTER TABLE statement issued after the full collection of tables
4685 have been created, and drop it via an ALTER TABLE statement before
4686 the full collection of tables are dropped.
4688 The use of :paramref:`_schema.ForeignKeyConstraint.use_alter` is
4689 particularly geared towards the case where two or more tables
4690 are established within a mutually-dependent foreign key constraint
4691 relationship; however, the :meth:`_schema.MetaData.create_all` and
4692 :meth:`_schema.MetaData.drop_all`
4693 methods will perform this resolution
4694 automatically, so the flag is normally not needed.
4696 .. seealso::
4698 :ref:`use_alter`
4700 :param match: Optional string. If set, emit MATCH <value> when issuing
4701 DDL for this constraint. Typical values include SIMPLE, PARTIAL
4702 and FULL.
4704 :param info: Optional data dictionary which will be populated into the
4705 :attr:`.SchemaItem.info` attribute of this object.
4707 :param comment: Optional string that will render an SQL comment on
4708 foreign key constraint creation.
4710 .. versionadded:: 2.0
4712 :param \**dialect_kw: Additional keyword arguments are dialect
4713 specific, and passed in the form ``<dialectname>_<argname>``. See
4714 the documentation regarding an individual dialect at
4715 :ref:`dialect_toplevel` for detail on documented arguments.
4717 """
4719 Constraint.__init__(
4720 self,
4721 name=name,
4722 deferrable=deferrable,
4723 initially=initially,
4724 info=info,
4725 comment=comment,
4726 **dialect_kw,
4727 )
4728 self.onupdate = onupdate
4729 self.ondelete = ondelete
4730 self.link_to_name = link_to_name
4731 self.use_alter = use_alter
4732 self.match = match
4734 if len(set(columns)) != len(refcolumns):
4735 if len(set(columns)) != len(columns):
4736 # e.g. FOREIGN KEY (a, a) REFERENCES r (b, c)
4737 raise exc.ArgumentError(
4738 "ForeignKeyConstraint with duplicate source column "
4739 "references are not supported."
4740 )
4741 else:
4742 # e.g. FOREIGN KEY (a) REFERENCES r (b, c)
4743 # paraphrasing
4744 # https://www.postgresql.org/docs/current/static/ddl-constraints.html
4745 raise exc.ArgumentError(
4746 "ForeignKeyConstraint number "
4747 "of constrained columns must match the number of "
4748 "referenced columns."
4749 )
4751 # standalone ForeignKeyConstraint - create
4752 # associated ForeignKey objects which will be applied to hosted
4753 # Column objects (in col.foreign_keys), either now or when attached
4754 # to the Table for string-specified names
4755 self.elements = [
4756 ForeignKey(
4757 refcol,
4758 _constraint=self,
4759 name=self.name,
4760 onupdate=self.onupdate,
4761 ondelete=self.ondelete,
4762 use_alter=self.use_alter,
4763 link_to_name=self.link_to_name,
4764 match=self.match,
4765 deferrable=self.deferrable,
4766 initially=self.initially,
4767 **self.dialect_kwargs,
4768 )
4769 for refcol in refcolumns
4770 ]
4772 ColumnCollectionMixin.__init__(self, *columns)
4773 if table is not None:
4774 if hasattr(self, "parent"):
4775 assert table is self.parent
4776 self._set_parent_with_dispatch(table)
4778 def _append_element(self, column: Column[Any], fk: ForeignKey) -> None:
4779 self._columns.add(column)
4780 self.elements.append(fk)
4782 columns: ReadOnlyColumnCollection[str, Column[Any]]
4783 """A :class:`_expression.ColumnCollection` representing the set of columns
4784 for this constraint.
4786 """
4788 elements: List[ForeignKey]
4789 """A sequence of :class:`_schema.ForeignKey` objects.
4791 Each :class:`_schema.ForeignKey`
4792 represents a single referring column/referred
4793 column pair.
4795 This collection is intended to be read-only.
4797 """
4799 @property
4800 def _elements(self) -> util.OrderedDict[str, ForeignKey]:
4801 # legacy - provide a dictionary view of (column_key, fk)
4802 return util.OrderedDict(zip(self.column_keys, self.elements))
4804 @property
4805 def _referred_schema(self) -> Optional[str]:
4806 for elem in self.elements:
4807 return elem._referred_schema
4808 else:
4809 return None
4811 @property
4812 def referred_table(self) -> Table:
4813 """The :class:`_schema.Table` object to which this
4814 :class:`_schema.ForeignKeyConstraint` references.
4816 This is a dynamically calculated attribute which may not be available
4817 if the constraint and/or parent table is not yet associated with
4818 a metadata collection that contains the referred table.
4820 """
4821 return self.elements[0].column.table
4823 def _validate_dest_table(self, table: Table) -> None:
4824 table_keys = {
4825 elem._table_key_within_construction() for elem in self.elements
4826 }
4827 if None not in table_keys and len(table_keys) > 1:
4828 elem0, elem1 = sorted(table_keys)[0:2]
4829 raise exc.ArgumentError(
4830 f"ForeignKeyConstraint on "
4831 f"{table.fullname}({self._col_description}) refers to "
4832 f"multiple remote tables: {elem0} and {elem1}"
4833 )
4835 @property
4836 def column_keys(self) -> _typing_Sequence[str]:
4837 """Return a list of string keys representing the local
4838 columns in this :class:`_schema.ForeignKeyConstraint`.
4840 This list is either the original string arguments sent
4841 to the constructor of the :class:`_schema.ForeignKeyConstraint`,
4842 or if the constraint has been initialized with :class:`_schema.Column`
4843 objects, is the string ``.key`` of each element.
4845 """
4846 if hasattr(self, "parent"):
4847 return self._columns.keys()
4848 else:
4849 return [
4850 col.key if isinstance(col, ColumnElement) else str(col)
4851 for col in self._pending_colargs
4852 ]
4854 @property
4855 def _col_description(self) -> str:
4856 return ", ".join(self.column_keys)
4858 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4859 table = parent
4860 assert isinstance(table, Table)
4861 Constraint._set_parent(self, table)
4863 ColumnCollectionConstraint._set_parent(self, table)
4865 for col, fk in zip(self._columns, self.elements):
4866 if not hasattr(fk, "parent") or fk.parent is not col:
4867 fk._set_parent_with_dispatch(col)
4869 self._validate_dest_table(table)
4871 @util.deprecated(
4872 "1.4",
4873 "The :meth:`_schema.ForeignKeyConstraint.copy` method is deprecated "
4874 "and will be removed in a future release.",
4875 )
4876 def copy(
4877 self,
4878 *,
4879 schema: Optional[str] = None,
4880 target_table: Optional[Table] = None,
4881 **kw: Any,
4882 ) -> ForeignKeyConstraint:
4883 return self._copy(schema=schema, target_table=target_table, **kw)
4885 def _copy(
4886 self,
4887 *,
4888 schema: Optional[str] = None,
4889 target_table: Optional[Table] = None,
4890 **kw: Any,
4891 ) -> ForeignKeyConstraint:
4892 fkc = ForeignKeyConstraint(
4893 [x.parent.key for x in self.elements],
4894 [
4895 x._get_colspec(
4896 schema=schema,
4897 table_name=(
4898 target_table.name
4899 if target_table is not None
4900 and x._table_key_within_construction()
4901 == x.parent.table.key
4902 else None
4903 ),
4904 _is_copy=True,
4905 )
4906 for x in self.elements
4907 ],
4908 name=self.name,
4909 onupdate=self.onupdate,
4910 ondelete=self.ondelete,
4911 use_alter=self.use_alter,
4912 deferrable=self.deferrable,
4913 initially=self.initially,
4914 link_to_name=self.link_to_name,
4915 match=self.match,
4916 comment=self.comment,
4917 )
4918 for self_fk, other_fk in zip(self.elements, fkc.elements):
4919 self_fk._schema_item_copy(other_fk)
4920 return self._schema_item_copy(fkc)
4923class PrimaryKeyConstraint(ColumnCollectionConstraint):
4924 """A table-level PRIMARY KEY constraint.
4926 The :class:`.PrimaryKeyConstraint` object is present automatically
4927 on any :class:`_schema.Table` object; it is assigned a set of
4928 :class:`_schema.Column` objects corresponding to those marked with
4929 the :paramref:`_schema.Column.primary_key` flag::
4931 >>> my_table = Table('mytable', metadata,
4932 ... Column('id', Integer, primary_key=True),
4933 ... Column('version_id', Integer, primary_key=True),
4934 ... Column('data', String(50))
4935 ... )
4936 >>> my_table.primary_key
4937 PrimaryKeyConstraint(
4938 Column('id', Integer(), table=<mytable>,
4939 primary_key=True, nullable=False),
4940 Column('version_id', Integer(), table=<mytable>,
4941 primary_key=True, nullable=False)
4942 )
4944 The primary key of a :class:`_schema.Table` can also be specified by using
4945 a :class:`.PrimaryKeyConstraint` object explicitly; in this mode of usage,
4946 the "name" of the constraint can also be specified, as well as other
4947 options which may be recognized by dialects::
4949 my_table = Table('mytable', metadata,
4950 Column('id', Integer),
4951 Column('version_id', Integer),
4952 Column('data', String(50)),
4953 PrimaryKeyConstraint('id', 'version_id',
4954 name='mytable_pk')
4955 )
4957 The two styles of column-specification should generally not be mixed.
4958 An warning is emitted if the columns present in the
4959 :class:`.PrimaryKeyConstraint`
4960 don't match the columns that were marked as ``primary_key=True``, if both
4961 are present; in this case, the columns are taken strictly from the
4962 :class:`.PrimaryKeyConstraint` declaration, and those columns otherwise
4963 marked as ``primary_key=True`` are ignored. This behavior is intended to
4964 be backwards compatible with previous behavior.
4966 For the use case where specific options are to be specified on the
4967 :class:`.PrimaryKeyConstraint`, but the usual style of using
4968 ``primary_key=True`` flags is still desirable, an empty
4969 :class:`.PrimaryKeyConstraint` may be specified, which will take on the
4970 primary key column collection from the :class:`_schema.Table` based on the
4971 flags::
4973 my_table = Table('mytable', metadata,
4974 Column('id', Integer, primary_key=True),
4975 Column('version_id', Integer, primary_key=True),
4976 Column('data', String(50)),
4977 PrimaryKeyConstraint(name='mytable_pk',
4978 mssql_clustered=True)
4979 )
4981 """
4983 __visit_name__ = "primary_key_constraint"
4985 def __init__(
4986 self,
4987 *columns: _DDLColumnArgument,
4988 name: Optional[str] = None,
4989 deferrable: Optional[bool] = None,
4990 initially: Optional[str] = None,
4991 info: Optional[_InfoType] = None,
4992 _implicit_generated: bool = False,
4993 **dialect_kw: Any,
4994 ) -> None:
4995 self._implicit_generated = _implicit_generated
4996 super().__init__(
4997 *columns,
4998 name=name,
4999 deferrable=deferrable,
5000 initially=initially,
5001 info=info,
5002 **dialect_kw,
5003 )
5005 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
5006 table = parent
5007 assert isinstance(table, Table)
5008 super()._set_parent(table)
5010 if table.primary_key is not self:
5011 table.constraints.discard(table.primary_key)
5012 table.primary_key = self # type: ignore
5013 table.constraints.add(self)
5015 table_pks = [c for c in table.c if c.primary_key]
5016 if (
5017 self._columns
5018 and table_pks
5019 and set(table_pks) != set(self._columns)
5020 ):
5021 # black could not format these inline
5022 table_pk_str = ", ".join("'%s'" % c.name for c in table_pks)
5023 col_str = ", ".join("'%s'" % c.name for c in self._columns)
5025 util.warn(
5026 f"Table '{table.name}' specifies columns "
5027 f"{table_pk_str} as "
5028 f"primary_key=True, "
5029 f"not matching locally specified columns {col_str}; "
5030 f"setting the "
5031 f"current primary key columns to "
5032 f"{col_str}. "
5033 f"This warning "
5034 f"may become an exception in a future release"
5035 )
5036 table_pks[:] = []
5038 for c in self._columns:
5039 c.primary_key = True
5040 if c._user_defined_nullable is NULL_UNSPECIFIED:
5041 c.nullable = False
5042 if table_pks:
5043 self._columns.extend(table_pks)
5045 def _reload(self, columns: Iterable[Column[Any]]) -> None:
5046 """repopulate this :class:`.PrimaryKeyConstraint` given
5047 a set of columns.
5049 Existing columns in the table that are marked as primary_key=True
5050 are maintained.
5052 Also fires a new event.
5054 This is basically like putting a whole new
5055 :class:`.PrimaryKeyConstraint` object on the parent
5056 :class:`_schema.Table` object without actually replacing the object.
5058 The ordering of the given list of columns is also maintained; these
5059 columns will be appended to the list of columns after any which
5060 are already present.
5062 """
5063 # set the primary key flag on new columns.
5064 # note any existing PK cols on the table also have their
5065 # flag still set.
5066 for col in columns:
5067 col.primary_key = True
5069 self._columns.extend(columns)
5071 PrimaryKeyConstraint._autoincrement_column._reset(self) # type: ignore
5072 self._set_parent_with_dispatch(self.table)
5074 def _replace(self, col: Column[Any]) -> None:
5075 PrimaryKeyConstraint._autoincrement_column._reset(self) # type: ignore
5076 self._columns.replace(col)
5078 self.dispatch._sa_event_column_added_to_pk_constraint(self, col)
5080 @property
5081 def columns_autoinc_first(self) -> List[Column[Any]]:
5082 autoinc = self._autoincrement_column
5084 if autoinc is not None:
5085 return [autoinc] + [c for c in self._columns if c is not autoinc]
5086 else:
5087 return list(self._columns)
5089 @util.ro_memoized_property
5090 def _autoincrement_column(self) -> Optional[Column[int]]:
5091 def _validate_autoinc(col: Column[Any], autoinc_true: bool) -> bool:
5092 if col.type._type_affinity is None or not issubclass(
5093 col.type._type_affinity,
5094 (
5095 type_api.INTEGERTYPE._type_affinity,
5096 type_api.NUMERICTYPE._type_affinity,
5097 ),
5098 ):
5099 if autoinc_true:
5100 raise exc.ArgumentError(
5101 f"Column type {col.type} on column '{col}' is not "
5102 f"compatible with autoincrement=True"
5103 )
5104 else:
5105 return False
5106 elif (
5107 not isinstance(col.default, (type(None), Sequence))
5108 and not autoinc_true
5109 ):
5110 return False
5111 elif (
5112 col.server_default is not None
5113 and not isinstance(col.server_default, Identity)
5114 and not autoinc_true
5115 ):
5116 return False
5117 elif col.foreign_keys and col.autoincrement not in (
5118 True,
5119 "ignore_fk",
5120 ):
5121 return False
5122 return True
5124 if len(self._columns) == 1:
5125 col = list(self._columns)[0]
5127 if col.autoincrement is True:
5128 _validate_autoinc(col, True)
5129 return col
5130 elif col.autoincrement in (
5131 "auto",
5132 "ignore_fk",
5133 ) and _validate_autoinc(col, False):
5134 return col
5135 else:
5136 return None
5138 else:
5139 autoinc = None
5140 for col in self._columns:
5141 if col.autoincrement is True:
5142 _validate_autoinc(col, True)
5143 if autoinc is not None:
5144 raise exc.ArgumentError(
5145 f"Only one Column may be marked "
5146 f"autoincrement=True, found both "
5147 f"{col.name} and {autoinc.name}."
5148 )
5149 else:
5150 autoinc = col
5152 return autoinc
5155class UniqueConstraint(ColumnCollectionConstraint):
5156 """A table-level UNIQUE constraint.
5158 Defines a single column or composite UNIQUE constraint. For a no-frills,
5159 single column constraint, adding ``unique=True`` to the ``Column``
5160 definition is a shorthand equivalent for an unnamed, single column
5161 UniqueConstraint.
5162 """
5164 __visit_name__ = "unique_constraint"
5167class Index(
5168 DialectKWArgs, ColumnCollectionMixin, HasConditionalDDL, SchemaItem
5169):
5170 """A table-level INDEX.
5172 Defines a composite (one or more column) INDEX.
5174 E.g.::
5176 sometable = Table("sometable", metadata,
5177 Column("name", String(50)),
5178 Column("address", String(100))
5179 )
5181 Index("some_index", sometable.c.name)
5183 For a no-frills, single column index, adding
5184 :class:`_schema.Column` also supports ``index=True``::
5186 sometable = Table("sometable", metadata,
5187 Column("name", String(50), index=True)
5188 )
5190 For a composite index, multiple columns can be specified::
5192 Index("some_index", sometable.c.name, sometable.c.address)
5194 Functional indexes are supported as well, typically by using the
5195 :data:`.func` construct in conjunction with table-bound
5196 :class:`_schema.Column` objects::
5198 Index("some_index", func.lower(sometable.c.name))
5200 An :class:`.Index` can also be manually associated with a
5201 :class:`_schema.Table`,
5202 either through inline declaration or using
5203 :meth:`_schema.Table.append_constraint`. When this approach is used,
5204 the names
5205 of the indexed columns can be specified as strings::
5207 Table("sometable", metadata,
5208 Column("name", String(50)),
5209 Column("address", String(100)),
5210 Index("some_index", "name", "address")
5211 )
5213 To support functional or expression-based indexes in this form, the
5214 :func:`_expression.text` construct may be used::
5216 from sqlalchemy import text
5218 Table("sometable", metadata,
5219 Column("name", String(50)),
5220 Column("address", String(100)),
5221 Index("some_index", text("lower(name)"))
5222 )
5224 .. seealso::
5226 :ref:`schema_indexes` - General information on :class:`.Index`.
5228 :ref:`postgresql_indexes` - PostgreSQL-specific options available for
5229 the :class:`.Index` construct.
5231 :ref:`mysql_indexes` - MySQL-specific options available for the
5232 :class:`.Index` construct.
5234 :ref:`mssql_indexes` - MSSQL-specific options available for the
5235 :class:`.Index` construct.
5237 """
5239 __visit_name__ = "index"
5241 table: Optional[Table]
5242 expressions: _typing_Sequence[Union[str, ColumnElement[Any]]]
5243 _table_bound_expressions: _typing_Sequence[ColumnElement[Any]]
5245 def __init__(
5246 self,
5247 name: Optional[str],
5248 *expressions: _DDLColumnArgument,
5249 unique: bool = False,
5250 quote: Optional[bool] = None,
5251 info: Optional[_InfoType] = None,
5252 _table: Optional[Table] = None,
5253 _column_flag: bool = False,
5254 **dialect_kw: Any,
5255 ) -> None:
5256 r"""Construct an index object.
5258 :param name:
5259 The name of the index
5261 :param \*expressions:
5262 Column expressions to include in the index. The expressions
5263 are normally instances of :class:`_schema.Column`, but may also
5264 be arbitrary SQL expressions which ultimately refer to a
5265 :class:`_schema.Column`.
5267 :param unique=False:
5268 Keyword only argument; if True, create a unique index.
5270 :param quote=None:
5271 Keyword only argument; whether to apply quoting to the name of
5272 the index. Works in the same manner as that of
5273 :paramref:`_schema.Column.quote`.
5275 :param info=None: Optional data dictionary which will be populated
5276 into the :attr:`.SchemaItem.info` attribute of this object.
5278 :param \**dialect_kw: Additional keyword arguments not mentioned above
5279 are dialect specific, and passed in the form
5280 ``<dialectname>_<argname>``. See the documentation regarding an
5281 individual dialect at :ref:`dialect_toplevel` for detail on
5282 documented arguments.
5284 """
5285 self.table = table = None
5287 self.name = quoted_name.construct(name, quote)
5288 self.unique = unique
5289 if info is not None:
5290 self.info = info
5292 # TODO: consider "table" argument being public, but for
5293 # the purpose of the fix here, it starts as private.
5294 if _table is not None:
5295 table = _table
5297 self._validate_dialect_kwargs(dialect_kw)
5299 self.expressions = []
5300 # will call _set_parent() if table-bound column
5301 # objects are present
5302 ColumnCollectionMixin.__init__(
5303 self,
5304 *expressions,
5305 _column_flag=_column_flag,
5306 _gather_expressions=self.expressions,
5307 )
5308 if table is not None:
5309 self._set_parent(table)
5311 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
5312 table = parent
5313 assert isinstance(table, Table)
5314 ColumnCollectionMixin._set_parent(self, table)
5316 if self.table is not None and table is not self.table:
5317 raise exc.ArgumentError(
5318 f"Index '{self.name}' is against table "
5319 f"'{self.table.description}', and "
5320 f"cannot be associated with table '{table.description}'."
5321 )
5322 self.table = table
5323 table.indexes.add(self)
5325 expressions = self.expressions
5326 col_expressions = self._col_expressions(table)
5327 assert len(expressions) == len(col_expressions)
5329 exprs = []
5330 for expr, colexpr in zip(expressions, col_expressions):
5331 if isinstance(expr, ClauseElement):
5332 exprs.append(expr)
5333 elif colexpr is not None:
5334 exprs.append(colexpr)
5335 else:
5336 assert False
5337 self.expressions = self._table_bound_expressions = exprs
5339 def create(self, bind: _CreateDropBind, checkfirst: bool = False) -> None:
5340 """Issue a ``CREATE`` statement for this
5341 :class:`.Index`, using the given
5342 :class:`.Connection` or :class:`.Engine`` for connectivity.
5344 .. seealso::
5346 :meth:`_schema.MetaData.create_all`.
5348 """
5349 bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
5351 def drop(self, bind: _CreateDropBind, checkfirst: bool = False) -> None:
5352 """Issue a ``DROP`` statement for this
5353 :class:`.Index`, using the given
5354 :class:`.Connection` or :class:`.Engine` for connectivity.
5356 .. seealso::
5358 :meth:`_schema.MetaData.drop_all`.
5360 """
5361 bind._run_ddl_visitor(ddl.SchemaDropper, self, checkfirst=checkfirst)
5363 def __repr__(self) -> str:
5364 exprs: _typing_Sequence[Any] # noqa: F842
5366 return "Index(%s)" % (
5367 ", ".join(
5368 [repr(self.name)]
5369 + [repr(e) for e in self.expressions]
5370 + (self.unique and ["unique=True"] or [])
5371 )
5372 )
5375_NamingSchemaCallable = Callable[[Constraint, Table], str]
5376_NamingSchemaDirective = Union[str, _NamingSchemaCallable]
5379class _NamingSchemaTD(TypedDict, total=False):
5380 fk: _NamingSchemaDirective
5381 pk: _NamingSchemaDirective
5382 ix: _NamingSchemaDirective
5383 ck: _NamingSchemaDirective
5384 uq: _NamingSchemaDirective
5387_NamingSchemaParameter = Union[
5388 # it seems like the TypedDict here is useful for pylance typeahead,
5389 # and not much else
5390 _NamingSchemaTD,
5391 # there is no form that allows Union[Type[Any], str] to work in all
5392 # cases, including breaking out Mapping[] entries for each combination
5393 # even, therefore keys must be `Any` (see #10264)
5394 Mapping[Any, _NamingSchemaDirective],
5395]
5398DEFAULT_NAMING_CONVENTION: _NamingSchemaParameter = util.immutabledict(
5399 {"ix": "ix_%(column_0_label)s"}
5400)
5403class MetaData(HasSchemaAttr):
5404 """A collection of :class:`_schema.Table`
5405 objects and their associated schema
5406 constructs.
5408 Holds a collection of :class:`_schema.Table` objects as well as
5409 an optional binding to an :class:`_engine.Engine` or
5410 :class:`_engine.Connection`. If bound, the :class:`_schema.Table` objects
5411 in the collection and their columns may participate in implicit SQL
5412 execution.
5414 The :class:`_schema.Table` objects themselves are stored in the
5415 :attr:`_schema.MetaData.tables` dictionary.
5417 :class:`_schema.MetaData` is a thread-safe object for read operations.
5418 Construction of new tables within a single :class:`_schema.MetaData`
5419 object,
5420 either explicitly or via reflection, may not be completely thread-safe.
5422 .. seealso::
5424 :ref:`metadata_describing` - Introduction to database metadata
5426 """
5428 __visit_name__ = "metadata"
5430 def __init__(
5431 self,
5432 schema: Optional[str] = None,
5433 quote_schema: Optional[bool] = None,
5434 naming_convention: Optional[_NamingSchemaParameter] = None,
5435 info: Optional[_InfoType] = None,
5436 ) -> None:
5437 """Create a new MetaData object.
5439 :param schema:
5440 The default schema to use for the :class:`_schema.Table`,
5441 :class:`.Sequence`, and potentially other objects associated with
5442 this :class:`_schema.MetaData`. Defaults to ``None``.
5444 .. seealso::
5446 :ref:`schema_metadata_schema_name` - details on how the
5447 :paramref:`_schema.MetaData.schema` parameter is used.
5449 :paramref:`_schema.Table.schema`
5451 :paramref:`.Sequence.schema`
5453 :param quote_schema:
5454 Sets the ``quote_schema`` flag for those :class:`_schema.Table`,
5455 :class:`.Sequence`, and other objects which make usage of the
5456 local ``schema`` name.
5458 :param info: Optional data dictionary which will be populated into the
5459 :attr:`.SchemaItem.info` attribute of this object.
5461 :param naming_convention: a dictionary referring to values which
5462 will establish default naming conventions for :class:`.Constraint`
5463 and :class:`.Index` objects, for those objects which are not given
5464 a name explicitly.
5466 The keys of this dictionary may be:
5468 * a constraint or Index class, e.g. the :class:`.UniqueConstraint`,
5469 :class:`_schema.ForeignKeyConstraint` class, the :class:`.Index`
5470 class
5472 * a string mnemonic for one of the known constraint classes;
5473 ``"fk"``, ``"pk"``, ``"ix"``, ``"ck"``, ``"uq"`` for foreign key,
5474 primary key, index, check, and unique constraint, respectively.
5476 * the string name of a user-defined "token" that can be used
5477 to define new naming tokens.
5479 The values associated with each "constraint class" or "constraint
5480 mnemonic" key are string naming templates, such as
5481 ``"uq_%(table_name)s_%(column_0_name)s"``,
5482 which describe how the name should be composed. The values
5483 associated with user-defined "token" keys should be callables of the
5484 form ``fn(constraint, table)``, which accepts the constraint/index
5485 object and :class:`_schema.Table` as arguments, returning a string
5486 result.
5488 The built-in names are as follows, some of which may only be
5489 available for certain types of constraint:
5491 * ``%(table_name)s`` - the name of the :class:`_schema.Table`
5492 object
5493 associated with the constraint.
5495 * ``%(referred_table_name)s`` - the name of the
5496 :class:`_schema.Table`
5497 object associated with the referencing target of a
5498 :class:`_schema.ForeignKeyConstraint`.
5500 * ``%(column_0_name)s`` - the name of the :class:`_schema.Column`
5501 at
5502 index position "0" within the constraint.
5504 * ``%(column_0N_name)s`` - the name of all :class:`_schema.Column`
5505 objects in order within the constraint, joined without a
5506 separator.
5508 * ``%(column_0_N_name)s`` - the name of all
5509 :class:`_schema.Column`
5510 objects in order within the constraint, joined with an
5511 underscore as a separator.
5513 * ``%(column_0_label)s``, ``%(column_0N_label)s``,
5514 ``%(column_0_N_label)s`` - the label of either the zeroth
5515 :class:`_schema.Column` or all :class:`.Columns`, separated with
5516 or without an underscore
5518 * ``%(column_0_key)s``, ``%(column_0N_key)s``,
5519 ``%(column_0_N_key)s`` - the key of either the zeroth
5520 :class:`_schema.Column` or all :class:`.Columns`, separated with
5521 or without an underscore
5523 * ``%(referred_column_0_name)s``, ``%(referred_column_0N_name)s``
5524 ``%(referred_column_0_N_name)s``, ``%(referred_column_0_key)s``,
5525 ``%(referred_column_0N_key)s``, ... column tokens which
5526 render the names/keys/labels of columns that are referenced
5527 by a :class:`_schema.ForeignKeyConstraint`.
5529 * ``%(constraint_name)s`` - a special key that refers to the
5530 existing name given to the constraint. When this key is
5531 present, the :class:`.Constraint` object's existing name will be
5532 replaced with one that is composed from template string that
5533 uses this token. When this token is present, it is required that
5534 the :class:`.Constraint` is given an explicit name ahead of time.
5536 * user-defined: any additional token may be implemented by passing
5537 it along with a ``fn(constraint, table)`` callable to the
5538 naming_convention dictionary.
5540 .. versionadded:: 1.3.0 - added new ``%(column_0N_name)s``,
5541 ``%(column_0_N_name)s``, and related tokens that produce
5542 concatenations of names, keys, or labels for all columns referred
5543 to by a given constraint.
5545 .. seealso::
5547 :ref:`constraint_naming_conventions` - for detailed usage
5548 examples.
5550 """
5551 if schema is not None and not isinstance(schema, str):
5552 raise exc.ArgumentError(
5553 "expected schema argument to be a string, "
5554 f"got {type(schema)}."
5555 )
5556 self.tables = util.FacadeDict()
5557 self.schema = quoted_name.construct(schema, quote_schema)
5558 self.naming_convention = (
5559 naming_convention
5560 if naming_convention
5561 else DEFAULT_NAMING_CONVENTION
5562 )
5563 if info:
5564 self.info = info
5565 self._schemas: Set[str] = set()
5566 self._sequences: Dict[str, Sequence] = {}
5567 self._fk_memos: Dict[Tuple[str, Optional[str]], List[ForeignKey]] = (
5568 collections.defaultdict(list)
5569 )
5571 tables: util.FacadeDict[str, Table]
5572 """A dictionary of :class:`_schema.Table`
5573 objects keyed to their name or "table key".
5575 The exact key is that determined by the :attr:`_schema.Table.key`
5576 attribute;
5577 for a table with no :attr:`_schema.Table.schema` attribute,
5578 this is the same
5579 as :attr:`_schema.Table.name`. For a table with a schema,
5580 it is typically of the
5581 form ``schemaname.tablename``.
5583 .. seealso::
5585 :attr:`_schema.MetaData.sorted_tables`
5587 """
5589 def __repr__(self) -> str:
5590 return "MetaData()"
5592 def __contains__(self, table_or_key: Union[str, Table]) -> bool:
5593 if not isinstance(table_or_key, str):
5594 table_or_key = table_or_key.key
5595 return table_or_key in self.tables
5597 def _add_table(
5598 self, name: str, schema: Optional[str], table: Table
5599 ) -> None:
5600 key = _get_table_key(name, schema)
5601 self.tables._insert_item(key, table)
5602 if schema:
5603 self._schemas.add(schema)
5605 def _remove_table(self, name: str, schema: Optional[str]) -> None:
5606 key = _get_table_key(name, schema)
5607 removed = dict.pop(self.tables, key, None)
5608 if removed is not None:
5609 for fk in removed.foreign_keys:
5610 fk._remove_from_metadata(self)
5611 if self._schemas:
5612 self._schemas = {
5613 t.schema for t in self.tables.values() if t.schema is not None
5614 }
5616 def __getstate__(self) -> Dict[str, Any]:
5617 return {
5618 "tables": self.tables,
5619 "schema": self.schema,
5620 "schemas": self._schemas,
5621 "sequences": self._sequences,
5622 "fk_memos": self._fk_memos,
5623 "naming_convention": self.naming_convention,
5624 }
5626 def __setstate__(self, state: Dict[str, Any]) -> None:
5627 self.tables = state["tables"]
5628 self.schema = state["schema"]
5629 self.naming_convention = state["naming_convention"]
5630 self._sequences = state["sequences"]
5631 self._schemas = state["schemas"]
5632 self._fk_memos = state["fk_memos"]
5634 def clear(self) -> None:
5635 """Clear all Table objects from this MetaData."""
5637 dict.clear(self.tables) # type: ignore
5638 self._schemas.clear()
5639 self._fk_memos.clear()
5641 def remove(self, table: Table) -> None:
5642 """Remove the given Table object from this MetaData."""
5644 self._remove_table(table.name, table.schema)
5646 @property
5647 def sorted_tables(self) -> List[Table]:
5648 """Returns a list of :class:`_schema.Table` objects sorted in order of
5649 foreign key dependency.
5651 The sorting will place :class:`_schema.Table`
5652 objects that have dependencies
5653 first, before the dependencies themselves, representing the
5654 order in which they can be created. To get the order in which
5655 the tables would be dropped, use the ``reversed()`` Python built-in.
5657 .. warning::
5659 The :attr:`.MetaData.sorted_tables` attribute cannot by itself
5660 accommodate automatic resolution of dependency cycles between
5661 tables, which are usually caused by mutually dependent foreign key
5662 constraints. When these cycles are detected, the foreign keys
5663 of these tables are omitted from consideration in the sort.
5664 A warning is emitted when this condition occurs, which will be an
5665 exception raise in a future release. Tables which are not part
5666 of the cycle will still be returned in dependency order.
5668 To resolve these cycles, the
5669 :paramref:`_schema.ForeignKeyConstraint.use_alter` parameter may be
5670 applied to those constraints which create a cycle. Alternatively,
5671 the :func:`_schema.sort_tables_and_constraints` function will
5672 automatically return foreign key constraints in a separate
5673 collection when cycles are detected so that they may be applied
5674 to a schema separately.
5676 .. versionchanged:: 1.3.17 - a warning is emitted when
5677 :attr:`.MetaData.sorted_tables` cannot perform a proper sort
5678 due to cyclical dependencies. This will be an exception in a
5679 future release. Additionally, the sort will continue to return
5680 other tables not involved in the cycle in dependency order which
5681 was not the case previously.
5683 .. seealso::
5685 :func:`_schema.sort_tables`
5687 :func:`_schema.sort_tables_and_constraints`
5689 :attr:`_schema.MetaData.tables`
5691 :meth:`_reflection.Inspector.get_table_names`
5693 :meth:`_reflection.Inspector.get_sorted_table_and_fkc_names`
5696 """
5697 return ddl.sort_tables(
5698 sorted(self.tables.values(), key=lambda t: t.key) # type: ignore
5699 )
5701 # overload needed to work around mypy this mypy
5702 # https://github.com/python/mypy/issues/17093
5703 @overload
5704 def reflect(
5705 self,
5706 bind: Engine,
5707 schema: Optional[str] = ...,
5708 views: bool = ...,
5709 only: Union[
5710 _typing_Sequence[str], Callable[[str, MetaData], bool], None
5711 ] = ...,
5712 extend_existing: bool = ...,
5713 autoload_replace: bool = ...,
5714 resolve_fks: bool = ...,
5715 **dialect_kwargs: Any,
5716 ) -> None: ...
5718 @overload
5719 def reflect(
5720 self,
5721 bind: Connection,
5722 schema: Optional[str] = ...,
5723 views: bool = ...,
5724 only: Union[
5725 _typing_Sequence[str], Callable[[str, MetaData], bool], None
5726 ] = ...,
5727 extend_existing: bool = ...,
5728 autoload_replace: bool = ...,
5729 resolve_fks: bool = ...,
5730 **dialect_kwargs: Any,
5731 ) -> None: ...
5733 @util.preload_module("sqlalchemy.engine.reflection")
5734 def reflect(
5735 self,
5736 bind: Union[Engine, Connection],
5737 schema: Optional[str] = None,
5738 views: bool = False,
5739 only: Union[
5740 _typing_Sequence[str], Callable[[str, MetaData], bool], None
5741 ] = None,
5742 extend_existing: bool = False,
5743 autoload_replace: bool = True,
5744 resolve_fks: bool = True,
5745 **dialect_kwargs: Any,
5746 ) -> None:
5747 r"""Load all available table definitions from the database.
5749 Automatically creates ``Table`` entries in this ``MetaData`` for any
5750 table available in the database but not yet present in the
5751 ``MetaData``. May be called multiple times to pick up tables recently
5752 added to the database, however no special action is taken if a table
5753 in this ``MetaData`` no longer exists in the database.
5755 :param bind:
5756 A :class:`.Connection` or :class:`.Engine` used to access the
5757 database.
5759 :param schema:
5760 Optional, query and reflect tables from an alternate schema.
5761 If None, the schema associated with this :class:`_schema.MetaData`
5762 is used, if any.
5764 :param views:
5765 If True, also reflect views (materialized and plain).
5767 :param only:
5768 Optional. Load only a sub-set of available named tables. May be
5769 specified as a sequence of names or a callable.
5771 If a sequence of names is provided, only those tables will be
5772 reflected. An error is raised if a table is requested but not
5773 available. Named tables already present in this ``MetaData`` are
5774 ignored.
5776 If a callable is provided, it will be used as a boolean predicate to
5777 filter the list of potential table names. The callable is called
5778 with a table name and this ``MetaData`` instance as positional
5779 arguments and should return a true value for any table to reflect.
5781 :param extend_existing: Passed along to each :class:`_schema.Table` as
5782 :paramref:`_schema.Table.extend_existing`.
5784 :param autoload_replace: Passed along to each :class:`_schema.Table`
5785 as
5786 :paramref:`_schema.Table.autoload_replace`.
5788 :param resolve_fks: if True, reflect :class:`_schema.Table`
5789 objects linked
5790 to :class:`_schema.ForeignKey` objects located in each
5791 :class:`_schema.Table`.
5792 For :meth:`_schema.MetaData.reflect`,
5793 this has the effect of reflecting
5794 related tables that might otherwise not be in the list of tables
5795 being reflected, for example if the referenced table is in a
5796 different schema or is omitted via the
5797 :paramref:`.MetaData.reflect.only` parameter. When False,
5798 :class:`_schema.ForeignKey` objects are not followed to the
5799 :class:`_schema.Table`
5800 in which they link, however if the related table is also part of the
5801 list of tables that would be reflected in any case, the
5802 :class:`_schema.ForeignKey` object will still resolve to its related
5803 :class:`_schema.Table` after the :meth:`_schema.MetaData.reflect`
5804 operation is
5805 complete. Defaults to True.
5807 .. versionadded:: 1.3.0
5809 .. seealso::
5811 :paramref:`_schema.Table.resolve_fks`
5813 :param \**dialect_kwargs: Additional keyword arguments not mentioned
5814 above are dialect specific, and passed in the form
5815 ``<dialectname>_<argname>``. See the documentation regarding an
5816 individual dialect at :ref:`dialect_toplevel` for detail on
5817 documented arguments.
5819 .. seealso::
5821 :ref:`metadata_reflection_toplevel`
5823 :meth:`_events.DDLEvents.column_reflect` - Event used to customize
5824 the reflected columns. Usually used to generalize the types using
5825 :meth:`_types.TypeEngine.as_generic`
5827 :ref:`metadata_reflection_dbagnostic_types` - describes how to
5828 reflect tables using general types.
5830 """
5832 with inspection.inspect(bind)._inspection_context() as insp:
5833 reflect_opts: Any = {
5834 "autoload_with": insp,
5835 "extend_existing": extend_existing,
5836 "autoload_replace": autoload_replace,
5837 "resolve_fks": resolve_fks,
5838 "_extend_on": set(),
5839 }
5841 reflect_opts.update(dialect_kwargs)
5843 if schema is None:
5844 schema = self.schema
5846 if schema is not None:
5847 reflect_opts["schema"] = schema
5849 kind = util.preloaded.engine_reflection.ObjectKind.TABLE
5850 available: util.OrderedSet[str] = util.OrderedSet(
5851 insp.get_table_names(schema)
5852 )
5853 if views:
5854 kind = util.preloaded.engine_reflection.ObjectKind.ANY
5855 available.update(insp.get_view_names(schema))
5856 try:
5857 available.update(insp.get_materialized_view_names(schema))
5858 except NotImplementedError:
5859 pass
5861 if schema is not None:
5862 available_w_schema: util.OrderedSet[str] = util.OrderedSet(
5863 [f"{schema}.{name}" for name in available]
5864 )
5865 else:
5866 available_w_schema = available
5868 current = set(self.tables)
5870 if only is None:
5871 load = [
5872 name
5873 for name, schname in zip(available, available_w_schema)
5874 if extend_existing or schname not in current
5875 ]
5876 elif callable(only):
5877 load = [
5878 name
5879 for name, schname in zip(available, available_w_schema)
5880 if (extend_existing or schname not in current)
5881 and only(name, self)
5882 ]
5883 else:
5884 missing = [name for name in only if name not in available]
5885 if missing:
5886 s = schema and (" schema '%s'" % schema) or ""
5887 missing_str = ", ".join(missing)
5888 raise exc.InvalidRequestError(
5889 f"Could not reflect: requested table(s) not available "
5890 f"in {bind.engine!r}{s}: ({missing_str})"
5891 )
5892 load = [
5893 name
5894 for name in only
5895 if extend_existing or name not in current
5896 ]
5897 # pass the available tables so the inspector can
5898 # choose to ignore the filter_names
5899 _reflect_info = insp._get_reflection_info(
5900 schema=schema,
5901 filter_names=load,
5902 available=available,
5903 kind=kind,
5904 scope=util.preloaded.engine_reflection.ObjectScope.ANY,
5905 **dialect_kwargs,
5906 )
5907 reflect_opts["_reflect_info"] = _reflect_info
5909 for name in load:
5910 try:
5911 Table(name, self, **reflect_opts)
5912 except exc.UnreflectableTableError as uerr:
5913 util.warn(f"Skipping table {name}: {uerr}")
5915 def create_all(
5916 self,
5917 bind: _CreateDropBind,
5918 tables: Optional[_typing_Sequence[Table]] = None,
5919 checkfirst: bool = True,
5920 ) -> None:
5921 """Create all tables stored in this metadata.
5923 Conditional by default, will not attempt to recreate tables already
5924 present in the target database.
5926 :param bind:
5927 A :class:`.Connection` or :class:`.Engine` used to access the
5928 database.
5930 :param tables:
5931 Optional list of ``Table`` objects, which is a subset of the total
5932 tables in the ``MetaData`` (others are ignored).
5934 :param checkfirst:
5935 Defaults to True, don't issue CREATEs for tables already present
5936 in the target database.
5938 """
5939 bind._run_ddl_visitor(
5940 ddl.SchemaGenerator, self, checkfirst=checkfirst, tables=tables
5941 )
5943 def drop_all(
5944 self,
5945 bind: _CreateDropBind,
5946 tables: Optional[_typing_Sequence[Table]] = None,
5947 checkfirst: bool = True,
5948 ) -> None:
5949 """Drop all tables stored in this metadata.
5951 Conditional by default, will not attempt to drop tables not present in
5952 the target database.
5954 :param bind:
5955 A :class:`.Connection` or :class:`.Engine` used to access the
5956 database.
5958 :param tables:
5959 Optional list of ``Table`` objects, which is a subset of the
5960 total tables in the ``MetaData`` (others are ignored).
5962 :param checkfirst:
5963 Defaults to True, only issue DROPs for tables confirmed to be
5964 present in the target database.
5966 """
5967 bind._run_ddl_visitor(
5968 ddl.SchemaDropper, self, checkfirst=checkfirst, tables=tables
5969 )
5972class Computed(FetchedValue, SchemaItem):
5973 """Defines a generated column, i.e. "GENERATED ALWAYS AS" syntax.
5975 The :class:`.Computed` construct is an inline construct added to the
5976 argument list of a :class:`_schema.Column` object::
5978 from sqlalchemy import Computed
5980 Table('square', metadata_obj,
5981 Column('side', Float, nullable=False),
5982 Column('area', Float, Computed('side * side'))
5983 )
5985 See the linked documentation below for complete details.
5987 .. versionadded:: 1.3.11
5989 .. seealso::
5991 :ref:`computed_ddl`
5993 """
5995 __visit_name__ = "computed_column"
5997 column: Optional[Column[Any]]
5999 @_document_text_coercion(
6000 "sqltext", ":class:`.Computed`", ":paramref:`.Computed.sqltext`"
6001 )
6002 def __init__(
6003 self, sqltext: _DDLColumnArgument, persisted: Optional[bool] = None
6004 ) -> None:
6005 """Construct a GENERATED ALWAYS AS DDL construct to accompany a
6006 :class:`_schema.Column`.
6008 :param sqltext:
6009 A string containing the column generation expression, which will be
6010 used verbatim, or a SQL expression construct, such as a
6011 :func:`_expression.text`
6012 object. If given as a string, the object is converted to a
6013 :func:`_expression.text` object.
6015 :param persisted:
6016 Optional, controls how this column should be persisted by the
6017 database. Possible values are:
6019 * ``None``, the default, it will use the default persistence
6020 defined by the database.
6021 * ``True``, will render ``GENERATED ALWAYS AS ... STORED``, or the
6022 equivalent for the target database if supported.
6023 * ``False``, will render ``GENERATED ALWAYS AS ... VIRTUAL``, or
6024 the equivalent for the target database if supported.
6026 Specifying ``True`` or ``False`` may raise an error when the DDL
6027 is emitted to the target database if the database does not support
6028 that persistence option. Leaving this parameter at its default
6029 of ``None`` is guaranteed to succeed for all databases that support
6030 ``GENERATED ALWAYS AS``.
6032 """
6033 self.sqltext = coercions.expect(roles.DDLExpressionRole, sqltext)
6034 self.persisted = persisted
6035 self.column = None
6037 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
6038 assert isinstance(parent, Column)
6040 if not isinstance(
6041 parent.server_default, (type(None), Computed)
6042 ) or not isinstance(parent.server_onupdate, (type(None), Computed)):
6043 raise exc.ArgumentError(
6044 "A generated column cannot specify a server_default or a "
6045 "server_onupdate argument"
6046 )
6047 self.column = parent
6048 parent.computed = self
6049 self.column.server_onupdate = self
6050 self.column.server_default = self
6052 def _as_for_update(self, for_update: bool) -> FetchedValue:
6053 return self
6055 @util.deprecated(
6056 "1.4",
6057 "The :meth:`_schema.Computed.copy` method is deprecated "
6058 "and will be removed in a future release.",
6059 )
6060 def copy(
6061 self, *, target_table: Optional[Table] = None, **kw: Any
6062 ) -> Computed:
6063 return self._copy(target_table=target_table, **kw)
6065 def _copy(
6066 self, *, target_table: Optional[Table] = None, **kw: Any
6067 ) -> Computed:
6068 sqltext = _copy_expression(
6069 self.sqltext,
6070 self.column.table if self.column is not None else None,
6071 target_table,
6072 )
6073 g = Computed(sqltext, persisted=self.persisted)
6075 return self._schema_item_copy(g)
6078class Identity(IdentityOptions, FetchedValue, SchemaItem):
6079 """Defines an identity column, i.e. "GENERATED { ALWAYS | BY DEFAULT }
6080 AS IDENTITY" syntax.
6082 The :class:`.Identity` construct is an inline construct added to the
6083 argument list of a :class:`_schema.Column` object::
6085 from sqlalchemy import Identity
6087 Table('foo', metadata_obj,
6088 Column('id', Integer, Identity())
6089 Column('description', Text),
6090 )
6092 See the linked documentation below for complete details.
6094 .. versionadded:: 1.4
6096 .. seealso::
6098 :ref:`identity_ddl`
6100 """
6102 __visit_name__ = "identity_column"
6104 is_identity = True
6106 @util.deprecated_params(
6107 order=(
6108 "2.1",
6109 "This parameter is supported only by Oracle, "
6110 "use ``oracle_order`` instead.",
6111 ),
6112 on_null=(
6113 "2.1",
6114 "This parameter is supported only by Oracle, "
6115 "use ``oracle_on_null`` instead.",
6116 ),
6117 )
6118 def __init__(
6119 self,
6120 always: Optional[bool] = False,
6121 on_null: Optional[bool] = None,
6122 start: Optional[int] = None,
6123 increment: Optional[int] = None,
6124 minvalue: Optional[int] = None,
6125 maxvalue: Optional[int] = None,
6126 nominvalue: Optional[bool] = None,
6127 nomaxvalue: Optional[bool] = None,
6128 cycle: Optional[bool] = None,
6129 cache: Optional[int] = None,
6130 order: Optional[bool] = None,
6131 **dialect_kw: Any,
6132 ) -> None:
6133 """Construct a GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY DDL
6134 construct to accompany a :class:`_schema.Column`.
6136 See the :class:`.Sequence` documentation for a complete description
6137 of most parameters.
6139 .. note::
6140 MSSQL supports this construct as the preferred alternative to
6141 generate an IDENTITY on a column, but it uses non standard
6142 syntax that only support :paramref:`_schema.Identity.start`
6143 and :paramref:`_schema.Identity.increment`.
6144 All other parameters are ignored.
6146 :param always:
6147 A boolean, that indicates the type of identity column.
6148 If ``False`` is specified, the default, then the user-specified
6149 value takes precedence.
6150 If ``True`` is specified, a user-specified value is not accepted (
6151 on some backends, like PostgreSQL, OVERRIDING SYSTEM VALUE, or
6152 similar, may be specified in an INSERT to override the sequence
6153 value).
6154 Some backends also have a default value for this parameter,
6155 ``None`` can be used to omit rendering this part in the DDL. It
6156 will be treated as ``False`` if a backend does not have a default
6157 value.
6159 :param on_null:
6160 Set to ``True`` to specify ON NULL in conjunction with a
6161 ``always=False`` identity column. This option is only supported on
6162 some backends, like Oracle.
6164 :param start: the starting index of the sequence.
6165 :param increment: the increment value of the sequence.
6166 :param minvalue: the minimum value of the sequence.
6167 :param maxvalue: the maximum value of the sequence.
6168 :param nominvalue: no minimum value of the sequence.
6169 :param nomaxvalue: no maximum value of the sequence.
6170 :param cycle: allows the sequence to wrap around when the maxvalue
6171 or minvalue has been reached.
6172 :param cache: optional integer value; number of future values in the
6173 sequence which are calculated in advance.
6174 :param order: optional boolean value; if true, renders the
6175 ORDER keyword.
6177 """
6178 self.dialect_options
6179 if on_null is not None:
6180 if "oracle_on_null" in dialect_kw:
6181 raise exc.ArgumentError(
6182 "Cannot specify both 'on_null' and 'oracle_on_null'. "
6183 "Plese use only 'oracle_on_null'."
6184 )
6185 dialect_kw["oracle_on_null"] = on_null
6187 IdentityOptions.__init__(
6188 self,
6189 start=start,
6190 increment=increment,
6191 minvalue=minvalue,
6192 maxvalue=maxvalue,
6193 nominvalue=nominvalue,
6194 nomaxvalue=nomaxvalue,
6195 cycle=cycle,
6196 cache=cache,
6197 order=order,
6198 **dialect_kw,
6199 )
6200 self.always = always
6201 self.column = None
6203 @property
6204 def on_null(self) -> Optional[bool]:
6205 """Alias of the ``dialect_kwargs`` ``'oracle_on_null'``.
6207 .. deprecated:: 2.1 The 'on_null' attribute is deprecated.
6208 """
6209 value: Optional[bool] = self.dialect_kwargs.get("oracle_on_null")
6210 return value
6212 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
6213 assert isinstance(parent, Column)
6214 if not isinstance(
6215 parent.server_default, (type(None), Identity)
6216 ) or not isinstance(parent.server_onupdate, type(None)):
6217 raise exc.ArgumentError(
6218 "A column with an Identity object cannot specify a "
6219 "server_default or a server_onupdate argument"
6220 )
6221 if parent.autoincrement is False:
6222 raise exc.ArgumentError(
6223 "A column with an Identity object cannot specify "
6224 "autoincrement=False"
6225 )
6226 self.column = parent
6228 parent.identity = self
6229 if parent._user_defined_nullable is NULL_UNSPECIFIED:
6230 parent.nullable = False
6232 parent.server_default = self
6234 def _as_for_update(self, for_update: bool) -> FetchedValue:
6235 return self
6237 @util.deprecated(
6238 "1.4",
6239 "The :meth:`_schema.Identity.copy` method is deprecated "
6240 "and will be removed in a future release.",
6241 )
6242 def copy(self, **kw: Any) -> Identity:
6243 return self._copy(**kw)
6245 def _copy(self, **kw: Any) -> Identity:
6246 i = Identity(**self._as_dict(), **self.dialect_kwargs)
6248 return self._schema_item_copy(i)
6250 def _as_dict(self) -> Dict[str, Any]:
6251 return {
6252 # always=None means something different than always=False
6253 "always": self.always,
6254 **super()._as_dict(),
6255 }