Coverage for /pythoncovmergedfiles/medio/medio/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/schema.py: 41%
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 .visitors import InternalTraversal
84from .. import event
85from .. import exc
86from .. import inspection
87from .. import util
88from ..util import HasMemoized
89from ..util.typing import Literal
90from ..util.typing import Self
91from ..util.typing import TypeGuard
93if typing.TYPE_CHECKING:
94 from ._typing import _AutoIncrementType
95 from ._typing import _DDLColumnArgument
96 from ._typing import _DDLColumnReferenceArgument
97 from ._typing import _InfoType
98 from ._typing import _TextCoercedExpressionArgument
99 from ._typing import _TypeEngineArgument
100 from .base import ReadOnlyColumnCollection
101 from .compiler import DDLCompiler
102 from .elements import BindParameter
103 from .functions import Function
104 from .type_api import TypeEngine
105 from .visitors import _TraverseInternalsType
106 from .visitors import anon_map
107 from ..engine import Connection
108 from ..engine import Engine
109 from ..engine.interfaces import _CoreMultiExecuteParams
110 from ..engine.interfaces import CoreExecuteOptionsParameter
111 from ..engine.interfaces import ExecutionContext
112 from ..engine.mock import MockConnection
113 from ..engine.reflection import _ReflectionInfo
114 from ..sql.selectable import FromClause
116_T = TypeVar("_T", bound="Any")
117_SI = TypeVar("_SI", bound="SchemaItem")
118_TAB = TypeVar("_TAB", bound="Table")
121_CreateDropBind = Union["Engine", "Connection", "MockConnection"]
123_ConstraintNameArgument = Optional[Union[str, _NoneName]]
125_ServerDefaultArgument = Union[
126 "FetchedValue", str, TextClause, ColumnElement[Any]
127]
129_ServerOnUpdateArgument = _ServerDefaultArgument
132class SchemaConst(Enum):
133 RETAIN_SCHEMA = 1
134 """Symbol indicating that a :class:`_schema.Table`, :class:`.Sequence`
135 or in some cases a :class:`_schema.ForeignKey` object, in situations
136 where the object is being copied for a :meth:`.Table.to_metadata`
137 operation, should retain the schema name that it already has.
139 """
141 BLANK_SCHEMA = 2
142 """Symbol indicating that a :class:`_schema.Table` or :class:`.Sequence`
143 should have 'None' for its schema, even if the parent
144 :class:`_schema.MetaData` has specified a schema.
146 .. seealso::
148 :paramref:`_schema.MetaData.schema`
150 :paramref:`_schema.Table.schema`
152 :paramref:`.Sequence.schema`
154 """
156 NULL_UNSPECIFIED = 3
157 """Symbol indicating the "nullable" keyword was not passed to a Column.
159 This is used to distinguish between the use case of passing
160 ``nullable=None`` to a :class:`.Column`, which has special meaning
161 on some backends such as SQL Server.
163 """
166RETAIN_SCHEMA: Final[Literal[SchemaConst.RETAIN_SCHEMA]] = (
167 SchemaConst.RETAIN_SCHEMA
168)
169BLANK_SCHEMA: Final[Literal[SchemaConst.BLANK_SCHEMA]] = (
170 SchemaConst.BLANK_SCHEMA
171)
172NULL_UNSPECIFIED: Final[Literal[SchemaConst.NULL_UNSPECIFIED]] = (
173 SchemaConst.NULL_UNSPECIFIED
174)
177def _get_table_key(name: str, schema: Optional[str]) -> str:
178 if schema is None:
179 return name
180 else:
181 return schema + "." + name
184# this should really be in sql/util.py but we'd have to
185# break an import cycle
186def _copy_expression(
187 expression: ColumnElement[Any],
188 source_table: Optional[Table],
189 target_table: Optional[Table],
190) -> ColumnElement[Any]:
191 if source_table is None or target_table is None:
192 return expression
194 fixed_source_table = source_table
195 fixed_target_table = target_table
197 def replace(
198 element: ExternallyTraversible, **kw: Any
199 ) -> Optional[ExternallyTraversible]:
200 if (
201 isinstance(element, Column)
202 and element.table is fixed_source_table
203 and element.key in fixed_source_table.c
204 ):
205 return fixed_target_table.c[element.key]
206 else:
207 return None
209 return cast(
210 ColumnElement[Any],
211 visitors.replacement_traverse(expression, {}, replace),
212 )
215@inspection._self_inspects
216class SchemaItem(SchemaEventTarget, visitors.Visitable):
217 """Base class for items that define a database schema."""
219 __visit_name__ = "schema_item"
221 create_drop_stringify_dialect = "default"
223 def _init_items(self, *args: SchemaItem, **kw: Any) -> None:
224 """Initialize the list of child items for this SchemaItem."""
225 for item in args:
226 if item is not None:
227 try:
228 spwd = item._set_parent_with_dispatch
229 except AttributeError as err:
230 raise exc.ArgumentError(
231 "'SchemaItem' object, such as a 'Column' or a "
232 f"'Constraint' expected, got {item!r}"
233 ) from err
234 else:
235 spwd(self, **kw)
237 def __repr__(self) -> str:
238 return util.generic_repr(self, omit_kwarg=["info"])
240 @util.memoized_property
241 def info(self) -> _InfoType:
242 """Info dictionary associated with the object, allowing user-defined
243 data to be associated with this :class:`.SchemaItem`.
245 The dictionary is automatically generated when first accessed.
246 It can also be specified in the constructor of some objects,
247 such as :class:`_schema.Table` and :class:`_schema.Column`.
249 """
250 return {}
252 def _schema_item_copy(self, schema_item: _SI) -> _SI:
253 if "info" in self.__dict__:
254 schema_item.info = self.info.copy()
255 schema_item.dispatch._update(self.dispatch)
256 return schema_item
258 _use_schema_map = True
261class HasConditionalDDL:
262 """define a class that includes the :meth:`.HasConditionalDDL.ddl_if`
263 method, allowing for conditional rendering of DDL.
265 Currently applies to constraints and indexes.
267 .. versionadded:: 2.0
270 """
272 _ddl_if: Optional[ddl.DDLIf] = None
274 def ddl_if(
275 self,
276 dialect: Optional[str] = None,
277 callable_: Optional[ddl.DDLIfCallable] = None,
278 state: Optional[Any] = None,
279 ) -> Self:
280 r"""apply a conditional DDL rule to this schema item.
282 These rules work in a similar manner to the
283 :meth:`.ExecutableDDLElement.execute_if` callable, with the added
284 feature that the criteria may be checked within the DDL compilation
285 phase for a construct such as :class:`.CreateTable`.
286 :meth:`.HasConditionalDDL.ddl_if` currently applies towards the
287 :class:`.Index` construct as well as all :class:`.Constraint`
288 constructs.
290 :param dialect: string name of a dialect, or a tuple of string names
291 to indicate multiple dialect types.
293 :param callable\_: a callable that is constructed using the same form
294 as that described in
295 :paramref:`.ExecutableDDLElement.execute_if.callable_`.
297 :param state: any arbitrary object that will be passed to the
298 callable, if present.
300 .. versionadded:: 2.0
302 .. seealso::
304 :ref:`schema_ddl_ddl_if` - background and usage examples
307 """
308 self._ddl_if = ddl.DDLIf(dialect, callable_, state)
309 return self
312class HasSchemaAttr(SchemaItem):
313 """schema item that includes a top-level schema name"""
315 schema: Optional[str]
318class Table(
319 DialectKWArgs, HasSchemaAttr, TableClause, inspection.Inspectable["Table"]
320):
321 r"""Represent a table in a database.
323 e.g.::
325 mytable = Table(
326 "mytable", metadata,
327 Column('mytable_id', Integer, primary_key=True),
328 Column('value', String(50))
329 )
331 The :class:`_schema.Table`
332 object constructs a unique instance of itself based
333 on its name and optional schema name within the given
334 :class:`_schema.MetaData` object. Calling the :class:`_schema.Table`
335 constructor with the same name and same :class:`_schema.MetaData` argument
336 a second time will return the *same* :class:`_schema.Table`
337 object - in this way
338 the :class:`_schema.Table` constructor acts as a registry function.
340 .. seealso::
342 :ref:`metadata_describing` - Introduction to database metadata
344 """
346 __visit_name__ = "table"
348 if TYPE_CHECKING:
350 @util.ro_non_memoized_property
351 def primary_key(self) -> PrimaryKeyConstraint: ...
353 @util.ro_non_memoized_property
354 def foreign_keys(self) -> Set[ForeignKey]: ...
356 _columns: DedupeColumnCollection[Column[Any]]
358 _sentinel_column: Optional[Column[Any]]
360 constraints: Set[Constraint]
361 """A collection of all :class:`_schema.Constraint` objects associated with
362 this :class:`_schema.Table`.
364 Includes :class:`_schema.PrimaryKeyConstraint`,
365 :class:`_schema.ForeignKeyConstraint`, :class:`_schema.UniqueConstraint`,
366 :class:`_schema.CheckConstraint`. A separate collection
367 :attr:`_schema.Table.foreign_key_constraints` refers to the collection
368 of all :class:`_schema.ForeignKeyConstraint` objects, and the
369 :attr:`_schema.Table.primary_key` attribute refers to the single
370 :class:`_schema.PrimaryKeyConstraint` associated with the
371 :class:`_schema.Table`.
373 .. seealso::
375 :attr:`_schema.Table.constraints`
377 :attr:`_schema.Table.primary_key`
379 :attr:`_schema.Table.foreign_key_constraints`
381 :attr:`_schema.Table.indexes`
383 :class:`_reflection.Inspector`
386 """
388 indexes: Set[Index]
389 """A collection of all :class:`_schema.Index` objects associated with this
390 :class:`_schema.Table`.
392 .. seealso::
394 :meth:`_reflection.Inspector.get_indexes`
396 """
398 _traverse_internals: _TraverseInternalsType = (
399 TableClause._traverse_internals
400 + [("schema", InternalTraversal.dp_string)]
401 )
403 if TYPE_CHECKING:
405 @util.ro_non_memoized_property
406 def columns(self) -> ReadOnlyColumnCollection[str, Column[Any]]: ...
408 @util.ro_non_memoized_property
409 def exported_columns(
410 self,
411 ) -> ReadOnlyColumnCollection[str, Column[Any]]: ...
413 @util.ro_non_memoized_property
414 def c(self) -> ReadOnlyColumnCollection[str, Column[Any]]: ...
416 def _gen_cache_key(
417 self, anon_map: anon_map, bindparams: List[BindParameter[Any]]
418 ) -> Tuple[Any, ...]:
419 if self._annotations:
420 return (self,) + self._annotations_cache_key
421 else:
422 return (self,)
424 if not typing.TYPE_CHECKING:
425 # typing tools seem to be inconsistent in how they handle
426 # __new__, so suggest this pattern for classes that use
427 # __new__. apply typing to the __init__ method normally
428 @util.deprecated_params(
429 mustexist=(
430 "1.4",
431 "Deprecated alias of :paramref:`_schema.Table.must_exist`",
432 ),
433 )
434 def __new__(cls, *args: Any, **kw: Any) -> Any:
435 return cls._new(*args, **kw)
437 @classmethod
438 def _new(cls, *args: Any, **kw: Any) -> Any:
439 if not args and not kw:
440 # python3k pickle seems to call this
441 return object.__new__(cls)
443 try:
444 name, metadata, args = args[0], args[1], args[2:]
445 except IndexError:
446 raise TypeError(
447 "Table() takes at least two positional-only "
448 "arguments 'name' and 'metadata'"
449 )
451 schema = kw.get("schema", None)
452 if schema is None:
453 schema = metadata.schema
454 elif schema is BLANK_SCHEMA:
455 schema = None
456 keep_existing = kw.get("keep_existing", False)
457 extend_existing = kw.get("extend_existing", False)
459 if keep_existing and extend_existing:
460 msg = "keep_existing and extend_existing are mutually exclusive."
461 raise exc.ArgumentError(msg)
463 must_exist = kw.pop("must_exist", kw.pop("mustexist", False))
464 key = _get_table_key(name, schema)
465 if key in metadata.tables:
466 if not keep_existing and not extend_existing and bool(args):
467 raise exc.InvalidRequestError(
468 f"Table '{key}' is already defined for this MetaData "
469 "instance. Specify 'extend_existing=True' "
470 "to redefine "
471 "options and columns on an "
472 "existing Table object."
473 )
474 table = metadata.tables[key]
475 if extend_existing:
476 table._init_existing(*args, **kw)
477 return table
478 else:
479 if must_exist:
480 raise exc.InvalidRequestError(f"Table '{key}' not defined")
481 table = object.__new__(cls)
482 table.dispatch.before_parent_attach(table, metadata)
483 metadata._add_table(name, schema, table)
484 try:
485 table.__init__(name, metadata, *args, _no_init=False, **kw)
486 table.dispatch.after_parent_attach(table, metadata)
487 return table
488 except Exception:
489 with util.safe_reraise():
490 metadata._remove_table(name, schema)
492 def __init__(
493 self,
494 name: str,
495 metadata: MetaData,
496 *args: SchemaItem,
497 schema: Optional[Union[str, Literal[SchemaConst.BLANK_SCHEMA]]] = None,
498 quote: Optional[bool] = None,
499 quote_schema: Optional[bool] = None,
500 autoload_with: Optional[Union[Engine, Connection]] = None,
501 autoload_replace: bool = True,
502 keep_existing: bool = False,
503 extend_existing: bool = False,
504 resolve_fks: bool = True,
505 include_columns: Optional[Collection[str]] = None,
506 implicit_returning: bool = True,
507 comment: Optional[str] = None,
508 info: Optional[Dict[Any, Any]] = None,
509 listeners: Optional[
510 _typing_Sequence[Tuple[str, Callable[..., Any]]]
511 ] = None,
512 prefixes: Optional[_typing_Sequence[str]] = None,
513 # used internally in the metadata.reflect() process
514 _extend_on: Optional[Set[Table]] = None,
515 # used by __new__ to bypass __init__
516 _no_init: bool = True,
517 # dialect-specific keyword args
518 **kw: Any,
519 ) -> None:
520 r"""Constructor for :class:`_schema.Table`.
523 :param name: The name of this table as represented in the database.
525 The table name, along with the value of the ``schema`` parameter,
526 forms a key which uniquely identifies this :class:`_schema.Table`
527 within
528 the owning :class:`_schema.MetaData` collection.
529 Additional calls to :class:`_schema.Table` with the same name,
530 metadata,
531 and schema name will return the same :class:`_schema.Table` object.
533 Names which contain no upper case characters
534 will be treated as case insensitive names, and will not be quoted
535 unless they are a reserved word or contain special characters.
536 A name with any number of upper case characters is considered
537 to be case sensitive, and will be sent as quoted.
539 To enable unconditional quoting for the table name, specify the flag
540 ``quote=True`` to the constructor, or use the :class:`.quoted_name`
541 construct to specify the name.
543 :param metadata: a :class:`_schema.MetaData`
544 object which will contain this
545 table. The metadata is used as a point of association of this table
546 with other tables which are referenced via foreign key. It also
547 may be used to associate this table with a particular
548 :class:`.Connection` or :class:`.Engine`.
550 :param \*args: Additional positional arguments are used primarily
551 to add the list of :class:`_schema.Column`
552 objects contained within this
553 table. Similar to the style of a CREATE TABLE statement, other
554 :class:`.SchemaItem` constructs may be added here, including
555 :class:`.PrimaryKeyConstraint`, and
556 :class:`_schema.ForeignKeyConstraint`.
558 :param autoload_replace: Defaults to ``True``; when using
559 :paramref:`_schema.Table.autoload_with`
560 in conjunction with :paramref:`_schema.Table.extend_existing`,
561 indicates
562 that :class:`_schema.Column` objects present in the already-existing
563 :class:`_schema.Table`
564 object should be replaced with columns of the same
565 name retrieved from the autoload process. When ``False``, columns
566 already present under existing names will be omitted from the
567 reflection process.
569 Note that this setting does not impact :class:`_schema.Column` objects
570 specified programmatically within the call to :class:`_schema.Table`
571 that
572 also is autoloading; those :class:`_schema.Column` objects will always
573 replace existing columns of the same name when
574 :paramref:`_schema.Table.extend_existing` is ``True``.
576 .. seealso::
578 :paramref:`_schema.Table.autoload_with`
580 :paramref:`_schema.Table.extend_existing`
582 :param autoload_with: An :class:`_engine.Engine` or
583 :class:`_engine.Connection` object,
584 or a :class:`_reflection.Inspector` object as returned by
585 :func:`_sa.inspect`
586 against one, with which this :class:`_schema.Table`
587 object will be reflected.
588 When set to a non-None value, the autoload process will take place
589 for this table against the given engine or connection.
591 .. seealso::
593 :ref:`metadata_reflection_toplevel`
595 :meth:`_events.DDLEvents.column_reflect`
597 :ref:`metadata_reflection_dbagnostic_types`
599 :param extend_existing: When ``True``, indicates that if this
600 :class:`_schema.Table` is already present in the given
601 :class:`_schema.MetaData`,
602 apply further arguments within the constructor to the existing
603 :class:`_schema.Table`.
605 If :paramref:`_schema.Table.extend_existing` or
606 :paramref:`_schema.Table.keep_existing` are not set,
607 and the given name
608 of the new :class:`_schema.Table` refers to a :class:`_schema.Table`
609 that is
610 already present in the target :class:`_schema.MetaData` collection,
611 and
612 this :class:`_schema.Table`
613 specifies additional columns or other constructs
614 or flags that modify the table's state, an
615 error is raised. The purpose of these two mutually-exclusive flags
616 is to specify what action should be taken when a
617 :class:`_schema.Table`
618 is specified that matches an existing :class:`_schema.Table`,
619 yet specifies
620 additional constructs.
622 :paramref:`_schema.Table.extend_existing`
623 will also work in conjunction
624 with :paramref:`_schema.Table.autoload_with` to run a new reflection
625 operation against the database, even if a :class:`_schema.Table`
626 of the same name is already present in the target
627 :class:`_schema.MetaData`; newly reflected :class:`_schema.Column`
628 objects
629 and other options will be added into the state of the
630 :class:`_schema.Table`, potentially overwriting existing columns
631 and options of the same name.
633 As is always the case with :paramref:`_schema.Table.autoload_with`,
634 :class:`_schema.Column` objects can be specified in the same
635 :class:`_schema.Table`
636 constructor, which will take precedence. Below, the existing
637 table ``mytable`` will be augmented with :class:`_schema.Column`
638 objects
639 both reflected from the database, as well as the given
640 :class:`_schema.Column`
641 named "y"::
643 Table("mytable", metadata,
644 Column('y', Integer),
645 extend_existing=True,
646 autoload_with=engine
647 )
649 .. seealso::
651 :paramref:`_schema.Table.autoload_with`
653 :paramref:`_schema.Table.autoload_replace`
655 :paramref:`_schema.Table.keep_existing`
658 :param implicit_returning: True by default - indicates that
659 RETURNING can be used, typically by the ORM, in order to fetch
660 server-generated values such as primary key values and
661 server side defaults, on those backends which support RETURNING.
663 In modern SQLAlchemy there is generally no reason to alter this
664 setting, except for some backend specific cases
665 (see :ref:`mssql_triggers` in the SQL Server dialect documentation
666 for one such example).
668 :param include_columns: A list of strings indicating a subset of
669 columns to be loaded via the ``autoload`` operation; table columns who
670 aren't present in this list will not be represented on the resulting
671 ``Table`` object. Defaults to ``None`` which indicates all columns
672 should be reflected.
674 :param resolve_fks: Whether or not to reflect :class:`_schema.Table`
675 objects
676 related to this one via :class:`_schema.ForeignKey` objects, when
677 :paramref:`_schema.Table.autoload_with` is
678 specified. Defaults to True. Set to False to disable reflection of
679 related tables as :class:`_schema.ForeignKey`
680 objects are encountered; may be
681 used either to save on SQL calls or to avoid issues with related tables
682 that can't be accessed. Note that if a related table is already present
683 in the :class:`_schema.MetaData` collection, or becomes present later,
684 a
685 :class:`_schema.ForeignKey` object associated with this
686 :class:`_schema.Table` will
687 resolve to that table normally.
689 .. versionadded:: 1.3
691 .. seealso::
693 :paramref:`.MetaData.reflect.resolve_fks`
696 :param info: Optional data dictionary which will be populated into the
697 :attr:`.SchemaItem.info` attribute of this object.
699 :param keep_existing: When ``True``, indicates that if this Table
700 is already present in the given :class:`_schema.MetaData`, ignore
701 further arguments within the constructor to the existing
702 :class:`_schema.Table`, and return the :class:`_schema.Table`
703 object as
704 originally created. This is to allow a function that wishes
705 to define a new :class:`_schema.Table` on first call, but on
706 subsequent calls will return the same :class:`_schema.Table`,
707 without any of the declarations (particularly constraints)
708 being applied a second time.
710 If :paramref:`_schema.Table.extend_existing` or
711 :paramref:`_schema.Table.keep_existing` are not set,
712 and the given name
713 of the new :class:`_schema.Table` refers to a :class:`_schema.Table`
714 that is
715 already present in the target :class:`_schema.MetaData` collection,
716 and
717 this :class:`_schema.Table`
718 specifies additional columns or other constructs
719 or flags that modify the table's state, an
720 error is raised. The purpose of these two mutually-exclusive flags
721 is to specify what action should be taken when a
722 :class:`_schema.Table`
723 is specified that matches an existing :class:`_schema.Table`,
724 yet specifies
725 additional constructs.
727 .. seealso::
729 :paramref:`_schema.Table.extend_existing`
731 :param listeners: A list of tuples of the form ``(<eventname>, <fn>)``
732 which will be passed to :func:`.event.listen` upon construction.
733 This alternate hook to :func:`.event.listen` allows the establishment
734 of a listener function specific to this :class:`_schema.Table` before
735 the "autoload" process begins. Historically this has been intended
736 for use with the :meth:`.DDLEvents.column_reflect` event, however
737 note that this event hook may now be associated with the
738 :class:`_schema.MetaData` object directly::
740 def listen_for_reflect(table, column_info):
741 "handle the column reflection event"
742 # ...
744 t = Table(
745 'sometable',
746 autoload_with=engine,
747 listeners=[
748 ('column_reflect', listen_for_reflect)
749 ])
751 .. seealso::
753 :meth:`_events.DDLEvents.column_reflect`
755 :param must_exist: When ``True``, indicates that this Table must already
756 be present in the given :class:`_schema.MetaData` collection, else
757 an exception is raised.
759 :param prefixes:
760 A list of strings to insert after CREATE in the CREATE TABLE
761 statement. They will be separated by spaces.
763 :param quote: Force quoting of this table's name on or off, corresponding
764 to ``True`` or ``False``. When left at its default of ``None``,
765 the column identifier will be quoted according to whether the name is
766 case sensitive (identifiers with at least one upper case character are
767 treated as case sensitive), or if it's a reserved word. This flag
768 is only needed to force quoting of a reserved word which is not known
769 by the SQLAlchemy dialect.
771 .. note:: setting this flag to ``False`` will not provide
772 case-insensitive behavior for table reflection; table reflection
773 will always search for a mixed-case name in a case sensitive
774 fashion. Case insensitive names are specified in SQLAlchemy only
775 by stating the name with all lower case characters.
777 :param quote_schema: same as 'quote' but applies to the schema identifier.
779 :param schema: The schema name for this table, which is required if
780 the table resides in a schema other than the default selected schema
781 for the engine's database connection. Defaults to ``None``.
783 If the owning :class:`_schema.MetaData` of this :class:`_schema.Table`
784 specifies its
785 own :paramref:`_schema.MetaData.schema` parameter,
786 then that schema name will
787 be applied to this :class:`_schema.Table`
788 if the schema parameter here is set
789 to ``None``. To set a blank schema name on a :class:`_schema.Table`
790 that
791 would otherwise use the schema set on the owning
792 :class:`_schema.MetaData`,
793 specify the special symbol :attr:`.BLANK_SCHEMA`.
795 The quoting rules for the schema name are the same as those for the
796 ``name`` parameter, in that quoting is applied for reserved words or
797 case-sensitive names; to enable unconditional quoting for the schema
798 name, specify the flag ``quote_schema=True`` to the constructor, or use
799 the :class:`.quoted_name` construct to specify the name.
801 :param comment: Optional string that will render an SQL comment on table
802 creation.
804 .. versionadded:: 1.2 Added the :paramref:`_schema.Table.comment`
805 parameter
806 to :class:`_schema.Table`.
808 :param \**kw: Additional keyword arguments not mentioned above are
809 dialect specific, and passed in the form ``<dialectname>_<argname>``.
810 See the documentation regarding an individual dialect at
811 :ref:`dialect_toplevel` for detail on documented arguments.
813 """ # noqa: E501
814 if _no_init:
815 # don't run __init__ from __new__ by default;
816 # __new__ has a specific place that __init__ is called
817 return
819 super().__init__(quoted_name(name, quote))
820 self.metadata = metadata
822 if schema is None:
823 self.schema = metadata.schema
824 elif schema is BLANK_SCHEMA:
825 self.schema = None
826 else:
827 quote_schema = quote_schema
828 assert isinstance(schema, str)
829 self.schema = quoted_name(schema, quote_schema)
831 self._sentinel_column = None
833 self.indexes = set()
834 self.constraints = set()
835 PrimaryKeyConstraint(
836 _implicit_generated=True
837 )._set_parent_with_dispatch(self)
838 self.foreign_keys = set() # type: ignore
839 self._extra_dependencies: Set[Table] = set()
840 if self.schema is not None:
841 self.fullname = "%s.%s" % (self.schema, self.name)
842 else:
843 self.fullname = self.name
845 self.implicit_returning = implicit_returning
846 _reflect_info = kw.pop("_reflect_info", None)
848 self.comment = comment
850 if info is not None:
851 self.info = info
853 if listeners is not None:
854 for evt, fn in listeners:
855 event.listen(self, evt, fn)
857 self._prefixes = prefixes if prefixes else []
859 self._extra_kwargs(**kw)
861 # load column definitions from the database if 'autoload' is defined
862 # we do it after the table is in the singleton dictionary to support
863 # circular foreign keys
864 if autoload_with is not None:
865 self._autoload(
866 metadata,
867 autoload_with,
868 include_columns,
869 _extend_on=_extend_on,
870 _reflect_info=_reflect_info,
871 resolve_fks=resolve_fks,
872 )
874 # initialize all the column, etc. objects. done after reflection to
875 # allow user-overrides
877 self._init_items(
878 *args,
879 allow_replacements=extend_existing
880 or keep_existing
881 or autoload_with,
882 all_names={},
883 )
885 def _autoload(
886 self,
887 metadata: MetaData,
888 autoload_with: Union[Engine, Connection],
889 include_columns: Optional[Collection[str]],
890 exclude_columns: Collection[str] = (),
891 resolve_fks: bool = True,
892 _extend_on: Optional[Set[Table]] = None,
893 _reflect_info: _ReflectionInfo | None = None,
894 ) -> None:
895 insp = inspection.inspect(autoload_with)
896 with insp._inspection_context() as conn_insp:
897 conn_insp.reflect_table(
898 self,
899 include_columns,
900 exclude_columns,
901 resolve_fks,
902 _extend_on=_extend_on,
903 _reflect_info=_reflect_info,
904 )
906 @property
907 def _sorted_constraints(self) -> List[Constraint]:
908 """Return the set of constraints as a list, sorted by creation
909 order.
911 """
913 return sorted(self.constraints, key=lambda c: c._creation_order)
915 @property
916 def foreign_key_constraints(self) -> Set[ForeignKeyConstraint]:
917 """:class:`_schema.ForeignKeyConstraint` objects referred to by this
918 :class:`_schema.Table`.
920 This list is produced from the collection of
921 :class:`_schema.ForeignKey`
922 objects currently associated.
925 .. seealso::
927 :attr:`_schema.Table.constraints`
929 :attr:`_schema.Table.foreign_keys`
931 :attr:`_schema.Table.indexes`
933 """
934 return {
935 fkc.constraint
936 for fkc in self.foreign_keys
937 if fkc.constraint is not None
938 }
940 def _init_existing(self, *args: Any, **kwargs: Any) -> None:
941 autoload_with = kwargs.pop("autoload_with", None)
942 autoload = kwargs.pop("autoload", autoload_with is not None)
943 autoload_replace = kwargs.pop("autoload_replace", True)
944 schema = kwargs.pop("schema", None)
945 _extend_on = kwargs.pop("_extend_on", None)
946 _reflect_info = kwargs.pop("_reflect_info", None)
948 # these arguments are only used with _init()
949 extend_existing = kwargs.pop("extend_existing", False)
950 keep_existing = kwargs.pop("keep_existing", False)
952 assert extend_existing
953 assert not keep_existing
955 if schema and schema != self.schema:
956 raise exc.ArgumentError(
957 f"Can't change schema of existing table "
958 f"from '{self.schema}' to '{schema}'",
959 )
961 include_columns = kwargs.pop("include_columns", None)
962 if include_columns is not None:
963 for c in self.c:
964 if c.name not in include_columns:
965 self._columns.remove(c)
967 resolve_fks = kwargs.pop("resolve_fks", True)
969 for key in ("quote", "quote_schema"):
970 if key in kwargs:
971 raise exc.ArgumentError(
972 "Can't redefine 'quote' or 'quote_schema' arguments"
973 )
975 # update `self` with these kwargs, if provided
976 self.comment = kwargs.pop("comment", self.comment)
977 self.implicit_returning = kwargs.pop(
978 "implicit_returning", self.implicit_returning
979 )
980 self.info = kwargs.pop("info", self.info)
982 exclude_columns: _typing_Sequence[str]
984 if autoload:
985 if not autoload_replace:
986 # don't replace columns already present.
987 # we'd like to do this for constraints also however we don't
988 # have simple de-duping for unnamed constraints.
989 exclude_columns = [c.name for c in self.c]
990 else:
991 exclude_columns = ()
992 self._autoload(
993 self.metadata,
994 autoload_with,
995 include_columns,
996 exclude_columns,
997 resolve_fks,
998 _extend_on=_extend_on,
999 _reflect_info=_reflect_info,
1000 )
1002 all_names = {c.name: c for c in self.c}
1003 self._extra_kwargs(**kwargs)
1004 self._init_items(*args, allow_replacements=True, all_names=all_names)
1006 def _extra_kwargs(self, **kwargs: Any) -> None:
1007 self._validate_dialect_kwargs(kwargs)
1009 def _init_collections(self) -> None:
1010 pass
1012 def _reset_exported(self) -> None:
1013 pass
1015 @util.ro_non_memoized_property
1016 def _autoincrement_column(self) -> Optional[Column[int]]:
1017 return self.primary_key._autoincrement_column
1019 @util.ro_memoized_property
1020 def _sentinel_column_characteristics(
1021 self,
1022 ) -> _SentinelColumnCharacterization:
1023 """determine a candidate column (or columns, in case of a client
1024 generated composite primary key) which can be used as an
1025 "insert sentinel" for an INSERT statement.
1027 The returned structure, :class:`_SentinelColumnCharacterization`,
1028 includes all the details needed by :class:`.Dialect` and
1029 :class:`.SQLCompiler` to determine if these column(s) can be used
1030 as an INSERT..RETURNING sentinel for a particular database
1031 dialect.
1033 .. versionadded:: 2.0.10
1035 """
1037 sentinel_is_explicit = False
1038 sentinel_is_autoinc = False
1039 the_sentinel: Optional[_typing_Sequence[Column[Any]]] = None
1041 # see if a column was explicitly marked "insert_sentinel=True".
1042 explicit_sentinel_col = self._sentinel_column
1044 if explicit_sentinel_col is not None:
1045 the_sentinel = (explicit_sentinel_col,)
1046 sentinel_is_explicit = True
1048 autoinc_col = self._autoincrement_column
1049 if sentinel_is_explicit and explicit_sentinel_col is autoinc_col:
1050 assert autoinc_col is not None
1051 sentinel_is_autoinc = True
1052 elif explicit_sentinel_col is None and autoinc_col is not None:
1053 the_sentinel = (autoinc_col,)
1054 sentinel_is_autoinc = True
1056 default_characterization = _SentinelDefaultCharacterization.UNKNOWN
1058 if the_sentinel:
1059 the_sentinel_zero = the_sentinel[0]
1060 if the_sentinel_zero.identity:
1061 if the_sentinel_zero.identity._increment_is_negative:
1062 if sentinel_is_explicit:
1063 raise exc.InvalidRequestError(
1064 "Can't use IDENTITY default with negative "
1065 "increment as an explicit sentinel column"
1066 )
1067 else:
1068 if sentinel_is_autoinc:
1069 autoinc_col = None
1070 sentinel_is_autoinc = False
1071 the_sentinel = None
1072 else:
1073 default_characterization = (
1074 _SentinelDefaultCharacterization.IDENTITY
1075 )
1076 elif (
1077 the_sentinel_zero.default is None
1078 and the_sentinel_zero.server_default is None
1079 ):
1080 if the_sentinel_zero.nullable:
1081 raise exc.InvalidRequestError(
1082 f"Column {the_sentinel_zero} has been marked as a "
1083 "sentinel "
1084 "column with no default generation function; it "
1085 "at least needs to be marked nullable=False assuming "
1086 "user-populated sentinel values will be used."
1087 )
1088 default_characterization = (
1089 _SentinelDefaultCharacterization.NONE
1090 )
1091 elif the_sentinel_zero.default is not None:
1092 if the_sentinel_zero.default.is_sentinel:
1093 default_characterization = (
1094 _SentinelDefaultCharacterization.SENTINEL_DEFAULT
1095 )
1096 elif default_is_sequence(the_sentinel_zero.default):
1097 if the_sentinel_zero.default._increment_is_negative:
1098 if sentinel_is_explicit:
1099 raise exc.InvalidRequestError(
1100 "Can't use SEQUENCE default with negative "
1101 "increment as an explicit sentinel column"
1102 )
1103 else:
1104 if sentinel_is_autoinc:
1105 autoinc_col = None
1106 sentinel_is_autoinc = False
1107 the_sentinel = None
1109 default_characterization = (
1110 _SentinelDefaultCharacterization.SEQUENCE
1111 )
1112 elif the_sentinel_zero.default.is_callable:
1113 default_characterization = (
1114 _SentinelDefaultCharacterization.CLIENTSIDE
1115 )
1116 elif the_sentinel_zero.server_default is not None:
1117 if sentinel_is_explicit:
1118 raise exc.InvalidRequestError(
1119 f"Column {the_sentinel[0]} can't be a sentinel column "
1120 "because it uses an explicit server side default "
1121 "that's not the Identity() default."
1122 )
1124 default_characterization = (
1125 _SentinelDefaultCharacterization.SERVERSIDE
1126 )
1128 if the_sentinel is None and self.primary_key:
1129 assert autoinc_col is None
1131 # determine for non-autoincrement pk if all elements are
1132 # client side
1133 for _pkc in self.primary_key:
1134 if _pkc.server_default is not None or (
1135 _pkc.default and not _pkc.default.is_callable
1136 ):
1137 break
1138 else:
1139 the_sentinel = tuple(self.primary_key)
1140 default_characterization = (
1141 _SentinelDefaultCharacterization.CLIENTSIDE
1142 )
1144 return _SentinelColumnCharacterization(
1145 the_sentinel,
1146 sentinel_is_explicit,
1147 sentinel_is_autoinc,
1148 default_characterization,
1149 )
1151 @property
1152 def autoincrement_column(self) -> Optional[Column[int]]:
1153 """Returns the :class:`.Column` object which currently represents
1154 the "auto increment" column, if any, else returns None.
1156 This is based on the rules for :class:`.Column` as defined by the
1157 :paramref:`.Column.autoincrement` parameter, which generally means the
1158 column within a single integer column primary key constraint that is
1159 not constrained by a foreign key. If the table does not have such
1160 a primary key constraint, then there's no "autoincrement" column.
1161 A :class:`.Table` may have only one column defined as the
1162 "autoincrement" column.
1164 .. versionadded:: 2.0.4
1166 .. seealso::
1168 :paramref:`.Column.autoincrement`
1170 """
1171 return self._autoincrement_column
1173 @property
1174 def key(self) -> str:
1175 """Return the 'key' for this :class:`_schema.Table`.
1177 This value is used as the dictionary key within the
1178 :attr:`_schema.MetaData.tables` collection. It is typically the same
1179 as that of :attr:`_schema.Table.name` for a table with no
1180 :attr:`_schema.Table.schema`
1181 set; otherwise it is typically of the form
1182 ``schemaname.tablename``.
1184 """
1185 return _get_table_key(self.name, self.schema)
1187 def __repr__(self) -> str:
1188 return "Table(%s)" % ", ".join(
1189 [repr(self.name)]
1190 + [repr(self.metadata)]
1191 + [repr(x) for x in self.columns]
1192 + ["%s=%s" % (k, repr(getattr(self, k))) for k in ["schema"]]
1193 )
1195 def __str__(self) -> str:
1196 return _get_table_key(self.description, self.schema)
1198 def add_is_dependent_on(self, table: Table) -> None:
1199 """Add a 'dependency' for this Table.
1201 This is another Table object which must be created
1202 first before this one can, or dropped after this one.
1204 Usually, dependencies between tables are determined via
1205 ForeignKey objects. However, for other situations that
1206 create dependencies outside of foreign keys (rules, inheriting),
1207 this method can manually establish such a link.
1209 """
1210 self._extra_dependencies.add(table)
1212 def append_column(
1213 self, column: ColumnClause[Any], replace_existing: bool = False
1214 ) -> None:
1215 """Append a :class:`_schema.Column` to this :class:`_schema.Table`.
1217 The "key" of the newly added :class:`_schema.Column`, i.e. the
1218 value of its ``.key`` attribute, will then be available
1219 in the ``.c`` collection of this :class:`_schema.Table`, and the
1220 column definition will be included in any CREATE TABLE, SELECT,
1221 UPDATE, etc. statements generated from this :class:`_schema.Table`
1222 construct.
1224 Note that this does **not** change the definition of the table
1225 as it exists within any underlying database, assuming that
1226 table has already been created in the database. Relational
1227 databases support the addition of columns to existing tables
1228 using the SQL ALTER command, which would need to be
1229 emitted for an already-existing table that doesn't contain
1230 the newly added column.
1232 :param replace_existing: When ``True``, allows replacing existing
1233 columns. When ``False``, the default, an warning will be raised
1234 if a column with the same ``.key`` already exists. A future
1235 version of sqlalchemy will instead rise a warning.
1237 .. versionadded:: 1.4.0
1238 """
1240 try:
1241 column._set_parent_with_dispatch(
1242 self,
1243 allow_replacements=replace_existing,
1244 all_names={c.name: c for c in self.c},
1245 )
1246 except exc.DuplicateColumnError as de:
1247 raise exc.DuplicateColumnError(
1248 f"{de.args[0]} Specify replace_existing=True to "
1249 "Table.append_column() to replace an "
1250 "existing column."
1251 ) from de
1253 def append_constraint(self, constraint: Union[Index, Constraint]) -> None:
1254 """Append a :class:`_schema.Constraint` to this
1255 :class:`_schema.Table`.
1257 This has the effect of the constraint being included in any
1258 future CREATE TABLE statement, assuming specific DDL creation
1259 events have not been associated with the given
1260 :class:`_schema.Constraint` object.
1262 Note that this does **not** produce the constraint within the
1263 relational database automatically, for a table that already exists
1264 in the database. To add a constraint to an
1265 existing relational database table, the SQL ALTER command must
1266 be used. SQLAlchemy also provides the
1267 :class:`.AddConstraint` construct which can produce this SQL when
1268 invoked as an executable clause.
1270 """
1272 constraint._set_parent_with_dispatch(self)
1274 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
1275 metadata = parent
1276 assert isinstance(metadata, MetaData)
1277 metadata._add_table(self.name, self.schema, self)
1278 self.metadata = metadata
1280 def create(self, bind: _CreateDropBind, checkfirst: bool = False) -> None:
1281 """Issue a ``CREATE`` statement for this
1282 :class:`_schema.Table`, using the given
1283 :class:`.Connection` or :class:`.Engine`
1284 for connectivity.
1286 .. seealso::
1288 :meth:`_schema.MetaData.create_all`.
1290 """
1292 bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
1294 def drop(self, bind: _CreateDropBind, checkfirst: bool = False) -> None:
1295 """Issue a ``DROP`` statement for this
1296 :class:`_schema.Table`, using the given
1297 :class:`.Connection` or :class:`.Engine` for connectivity.
1299 .. seealso::
1301 :meth:`_schema.MetaData.drop_all`.
1303 """
1304 bind._run_ddl_visitor(ddl.SchemaDropper, self, checkfirst=checkfirst)
1306 @util.deprecated(
1307 "1.4",
1308 ":meth:`_schema.Table.tometadata` is renamed to "
1309 ":meth:`_schema.Table.to_metadata`",
1310 )
1311 def tometadata(
1312 self,
1313 metadata: MetaData,
1314 schema: Union[str, Literal[SchemaConst.RETAIN_SCHEMA]] = RETAIN_SCHEMA,
1315 referred_schema_fn: Optional[
1316 Callable[
1317 [Table, Optional[str], ForeignKeyConstraint, Optional[str]],
1318 Optional[str],
1319 ]
1320 ] = None,
1321 name: Optional[str] = None,
1322 ) -> Table:
1323 """Return a copy of this :class:`_schema.Table`
1324 associated with a different
1325 :class:`_schema.MetaData`.
1327 See :meth:`_schema.Table.to_metadata` for a full description.
1329 """
1330 return self.to_metadata(
1331 metadata,
1332 schema=schema,
1333 referred_schema_fn=referred_schema_fn,
1334 name=name,
1335 )
1337 def to_metadata(
1338 self,
1339 metadata: MetaData,
1340 schema: Union[str, Literal[SchemaConst.RETAIN_SCHEMA]] = RETAIN_SCHEMA,
1341 referred_schema_fn: Optional[
1342 Callable[
1343 [Table, Optional[str], ForeignKeyConstraint, Optional[str]],
1344 Optional[str],
1345 ]
1346 ] = None,
1347 name: Optional[str] = None,
1348 ) -> Table:
1349 """Return a copy of this :class:`_schema.Table` associated with a
1350 different :class:`_schema.MetaData`.
1352 E.g.::
1354 m1 = MetaData()
1356 user = Table('user', m1, Column('id', Integer, primary_key=True))
1358 m2 = MetaData()
1359 user_copy = user.to_metadata(m2)
1361 .. versionchanged:: 1.4 The :meth:`_schema.Table.to_metadata` function
1362 was renamed from :meth:`_schema.Table.tometadata`.
1365 :param metadata: Target :class:`_schema.MetaData` object,
1366 into which the
1367 new :class:`_schema.Table` object will be created.
1369 :param schema: optional string name indicating the target schema.
1370 Defaults to the special symbol :attr:`.RETAIN_SCHEMA` which indicates
1371 that no change to the schema name should be made in the new
1372 :class:`_schema.Table`. If set to a string name, the new
1373 :class:`_schema.Table`
1374 will have this new name as the ``.schema``. If set to ``None``, the
1375 schema will be set to that of the schema set on the target
1376 :class:`_schema.MetaData`, which is typically ``None`` as well,
1377 unless
1378 set explicitly::
1380 m2 = MetaData(schema='newschema')
1382 # user_copy_one will have "newschema" as the schema name
1383 user_copy_one = user.to_metadata(m2, schema=None)
1385 m3 = MetaData() # schema defaults to None
1387 # user_copy_two will have None as the schema name
1388 user_copy_two = user.to_metadata(m3, schema=None)
1390 :param referred_schema_fn: optional callable which can be supplied
1391 in order to provide for the schema name that should be assigned
1392 to the referenced table of a :class:`_schema.ForeignKeyConstraint`.
1393 The callable accepts this parent :class:`_schema.Table`, the
1394 target schema that we are changing to, the
1395 :class:`_schema.ForeignKeyConstraint` object, and the existing
1396 "target schema" of that constraint. The function should return the
1397 string schema name that should be applied. To reset the schema
1398 to "none", return the symbol :data:`.BLANK_SCHEMA`. To effect no
1399 change, return ``None`` or :data:`.RETAIN_SCHEMA`.
1401 .. versionchanged:: 1.4.33 The ``referred_schema_fn`` function
1402 may return the :data:`.BLANK_SCHEMA` or :data:`.RETAIN_SCHEMA`
1403 symbols.
1405 E.g.::
1407 def referred_schema_fn(table, to_schema,
1408 constraint, referred_schema):
1409 if referred_schema == 'base_tables':
1410 return referred_schema
1411 else:
1412 return to_schema
1414 new_table = table.to_metadata(m2, schema="alt_schema",
1415 referred_schema_fn=referred_schema_fn)
1417 :param name: optional string name indicating the target table name.
1418 If not specified or None, the table name is retained. This allows
1419 a :class:`_schema.Table` to be copied to the same
1420 :class:`_schema.MetaData` target
1421 with a new name.
1423 """
1424 if name is None:
1425 name = self.name
1427 actual_schema: Optional[str]
1429 if schema is RETAIN_SCHEMA:
1430 actual_schema = self.schema
1431 elif schema is None:
1432 actual_schema = metadata.schema
1433 else:
1434 actual_schema = schema
1435 key = _get_table_key(name, actual_schema)
1436 if key in metadata.tables:
1437 util.warn(
1438 f"Table '{self.description}' already exists within the given "
1439 "MetaData - not copying."
1440 )
1441 return metadata.tables[key]
1443 args = []
1444 for col in self.columns:
1445 args.append(col._copy(schema=actual_schema))
1446 table = Table(
1447 name,
1448 metadata,
1449 schema=actual_schema,
1450 comment=self.comment,
1451 *args,
1452 **self.kwargs,
1453 )
1454 for const in self.constraints:
1455 if isinstance(const, ForeignKeyConstraint):
1456 referred_schema = const._referred_schema
1457 if referred_schema_fn:
1458 fk_constraint_schema = referred_schema_fn(
1459 self, actual_schema, const, referred_schema
1460 )
1461 else:
1462 fk_constraint_schema = (
1463 actual_schema
1464 if referred_schema == self.schema
1465 else None
1466 )
1467 table.append_constraint(
1468 const._copy(
1469 schema=fk_constraint_schema, target_table=table
1470 )
1471 )
1472 elif not const._type_bound:
1473 # skip unique constraints that would be generated
1474 # by the 'unique' flag on Column
1475 if const._column_flag:
1476 continue
1478 table.append_constraint(
1479 const._copy(schema=actual_schema, target_table=table)
1480 )
1481 for index in self.indexes:
1482 # skip indexes that would be generated
1483 # by the 'index' flag on Column
1484 if index._column_flag:
1485 continue
1486 Index(
1487 index.name,
1488 unique=index.unique,
1489 *[
1490 _copy_expression(expr, self, table)
1491 for expr in index._table_bound_expressions
1492 ],
1493 _table=table,
1494 **index.kwargs,
1495 )
1496 return self._schema_item_copy(table)
1499class Column(DialectKWArgs, SchemaItem, ColumnClause[_T]):
1500 """Represents a column in a database table."""
1502 __visit_name__ = "column"
1504 inherit_cache = True
1505 key: str
1507 server_default: Optional[FetchedValue]
1509 def __init__(
1510 self,
1511 __name_pos: Optional[
1512 Union[str, _TypeEngineArgument[_T], SchemaEventTarget]
1513 ] = None,
1514 __type_pos: Optional[
1515 Union[_TypeEngineArgument[_T], SchemaEventTarget]
1516 ] = None,
1517 /,
1518 *args: SchemaEventTarget,
1519 name: Optional[str] = None,
1520 type_: Optional[_TypeEngineArgument[_T]] = None,
1521 autoincrement: _AutoIncrementType = "auto",
1522 default: Optional[Any] = _NoArg.NO_ARG,
1523 insert_default: Optional[Any] = _NoArg.NO_ARG,
1524 doc: Optional[str] = None,
1525 key: Optional[str] = None,
1526 index: Optional[bool] = None,
1527 unique: Optional[bool] = None,
1528 info: Optional[_InfoType] = None,
1529 nullable: Optional[
1530 Union[bool, Literal[SchemaConst.NULL_UNSPECIFIED]]
1531 ] = SchemaConst.NULL_UNSPECIFIED,
1532 onupdate: Optional[Any] = None,
1533 primary_key: bool = False,
1534 server_default: Optional[_ServerDefaultArgument] = None,
1535 server_onupdate: Optional[_ServerOnUpdateArgument] = None,
1536 quote: Optional[bool] = None,
1537 system: bool = False,
1538 comment: Optional[str] = None,
1539 insert_sentinel: bool = False,
1540 _omit_from_statements: bool = False,
1541 _proxies: Optional[Any] = None,
1542 **dialect_kwargs: Any,
1543 ):
1544 r"""
1545 Construct a new ``Column`` object.
1547 :param name: The name of this column as represented in the database.
1548 This argument may be the first positional argument, or specified
1549 via keyword.
1551 Names which contain no upper case characters
1552 will be treated as case insensitive names, and will not be quoted
1553 unless they are a reserved word. Names with any number of upper
1554 case characters will be quoted and sent exactly. Note that this
1555 behavior applies even for databases which standardize upper
1556 case names as case insensitive such as Oracle.
1558 The name field may be omitted at construction time and applied
1559 later, at any time before the Column is associated with a
1560 :class:`_schema.Table`. This is to support convenient
1561 usage within the :mod:`~sqlalchemy.ext.declarative` extension.
1563 :param type\_: The column's type, indicated using an instance which
1564 subclasses :class:`~sqlalchemy.types.TypeEngine`. If no arguments
1565 are required for the type, the class of the type can be sent
1566 as well, e.g.::
1568 # use a type with arguments
1569 Column('data', String(50))
1571 # use no arguments
1572 Column('level', Integer)
1574 The ``type`` argument may be the second positional argument
1575 or specified by keyword.
1577 If the ``type`` is ``None`` or is omitted, it will first default to
1578 the special type :class:`.NullType`. If and when this
1579 :class:`_schema.Column` is made to refer to another column using
1580 :class:`_schema.ForeignKey` and/or
1581 :class:`_schema.ForeignKeyConstraint`, the type
1582 of the remote-referenced column will be copied to this column as
1583 well, at the moment that the foreign key is resolved against that
1584 remote :class:`_schema.Column` object.
1586 :param \*args: Additional positional arguments include various
1587 :class:`.SchemaItem` derived constructs which will be applied
1588 as options to the column. These include instances of
1589 :class:`.Constraint`, :class:`_schema.ForeignKey`,
1590 :class:`.ColumnDefault`, :class:`.Sequence`, :class:`.Computed`
1591 :class:`.Identity`. In some cases an
1592 equivalent keyword argument is available such as ``server_default``,
1593 ``default`` and ``unique``.
1595 :param autoincrement: Set up "auto increment" semantics for an
1596 **integer primary key column with no foreign key dependencies**
1597 (see later in this docstring for a more specific definition).
1598 This may influence the :term:`DDL` that will be emitted for
1599 this column during a table create, as well as how the column
1600 will be considered when INSERT statements are compiled and
1601 executed.
1603 The default value is the string ``"auto"``,
1604 which indicates that a single-column (i.e. non-composite) primary key
1605 that is of an INTEGER type with no other client-side or server-side
1606 default constructs indicated should receive auto increment semantics
1607 automatically. Other values include ``True`` (force this column to
1608 have auto-increment semantics for a :term:`composite primary key` as
1609 well), ``False`` (this column should never have auto-increment
1610 semantics), and the string ``"ignore_fk"`` (special-case for foreign
1611 key columns, see below).
1613 The term "auto increment semantics" refers both to the kind of DDL
1614 that will be emitted for the column within a CREATE TABLE statement,
1615 when methods such as :meth:`.MetaData.create_all` and
1616 :meth:`.Table.create` are invoked, as well as how the column will be
1617 considered when an INSERT statement is compiled and emitted to the
1618 database:
1620 * **DDL rendering** (i.e. :meth:`.MetaData.create_all`,
1621 :meth:`.Table.create`): When used on a :class:`.Column` that has
1622 no other
1623 default-generating construct associated with it (such as a
1624 :class:`.Sequence` or :class:`.Identity` construct), the parameter
1625 will imply that database-specific keywords such as PostgreSQL
1626 ``SERIAL``, MySQL ``AUTO_INCREMENT``, or ``IDENTITY`` on SQL Server
1627 should also be rendered. Not every database backend has an
1628 "implied" default generator available; for example the Oracle
1629 backend always needs an explicit construct such as
1630 :class:`.Identity` to be included with a :class:`.Column` in order
1631 for the DDL rendered to include auto-generating constructs to also
1632 be produced in the database.
1634 * **INSERT semantics** (i.e. when a :func:`_sql.insert` construct is
1635 compiled into a SQL string and is then executed on a database using
1636 :meth:`_engine.Connection.execute` or equivalent): A single-row
1637 INSERT statement will be known to produce a new integer primary key
1638 value automatically for this column, which will be accessible
1639 after the statement is invoked via the
1640 :attr:`.CursorResult.inserted_primary_key` attribute upon the
1641 :class:`_result.Result` object. This also applies towards use of the
1642 ORM when ORM-mapped objects are persisted to the database,
1643 indicating that a new integer primary key will be available to
1644 become part of the :term:`identity key` for that object. This
1645 behavior takes place regardless of what DDL constructs are
1646 associated with the :class:`_schema.Column` and is independent
1647 of the "DDL Rendering" behavior discussed in the previous note
1648 above.
1650 The parameter may be set to ``True`` to indicate that a column which
1651 is part of a composite (i.e. multi-column) primary key should
1652 have autoincrement semantics, though note that only one column
1653 within a primary key may have this setting. It can also
1654 be set to ``True`` to indicate autoincrement semantics on a
1655 column that has a client-side or server-side default configured,
1656 however note that not all dialects can accommodate all styles
1657 of default as an "autoincrement". It can also be
1658 set to ``False`` on a single-column primary key that has a
1659 datatype of INTEGER in order to disable auto increment semantics
1660 for that column.
1662 The setting *only* has an effect for columns which are:
1664 * Integer derived (i.e. INT, SMALLINT, BIGINT).
1666 * Part of the primary key
1668 * Not referring to another column via :class:`_schema.ForeignKey`,
1669 unless
1670 the value is specified as ``'ignore_fk'``::
1672 # turn on autoincrement for this column despite
1673 # the ForeignKey()
1674 Column('id', ForeignKey('other.id'),
1675 primary_key=True, autoincrement='ignore_fk')
1677 It is typically not desirable to have "autoincrement" enabled on a
1678 column that refers to another via foreign key, as such a column is
1679 required to refer to a value that originates from elsewhere.
1681 The setting has these effects on columns that meet the
1682 above criteria:
1684 * DDL issued for the column, if the column does not already include
1685 a default generating construct supported by the backend such as
1686 :class:`.Identity`, will include database-specific
1687 keywords intended to signify this column as an
1688 "autoincrement" column for specific backends. Behavior for
1689 primary SQLAlchemy dialects includes:
1691 * AUTO INCREMENT on MySQL and MariaDB
1692 * SERIAL on PostgreSQL
1693 * IDENTITY on MS-SQL - this occurs even without the
1694 :class:`.Identity` construct as the
1695 :paramref:`.Column.autoincrement` parameter pre-dates this
1696 construct.
1697 * SQLite - SQLite integer primary key columns are implicitly
1698 "auto incrementing" and no additional keywords are rendered;
1699 to render the special SQLite keyword ``AUTOINCREMENT``
1700 is not included as this is unnecessary and not recommended
1701 by the database vendor. See the section
1702 :ref:`sqlite_autoincrement` for more background.
1703 * Oracle - The Oracle dialect has no default "autoincrement"
1704 feature available at this time, instead the :class:`.Identity`
1705 construct is recommended to achieve this (the :class:`.Sequence`
1706 construct may also be used).
1707 * Third-party dialects - consult those dialects' documentation
1708 for details on their specific behaviors.
1710 * When a single-row :func:`_sql.insert` construct is compiled and
1711 executed, which does not set the :meth:`_sql.Insert.inline`
1712 modifier, newly generated primary key values for this column
1713 will be automatically retrieved upon statement execution
1714 using a method specific to the database driver in use:
1716 * MySQL, SQLite - calling upon ``cursor.lastrowid()``
1717 (see
1718 `https://www.python.org/dev/peps/pep-0249/#lastrowid
1719 <https://www.python.org/dev/peps/pep-0249/#lastrowid>`_)
1720 * PostgreSQL, SQL Server, Oracle - use RETURNING or an equivalent
1721 construct when rendering an INSERT statement, and then retrieving
1722 the newly generated primary key values after execution
1723 * PostgreSQL, Oracle for :class:`_schema.Table` objects that
1724 set :paramref:`_schema.Table.implicit_returning` to False -
1725 for a :class:`.Sequence` only, the :class:`.Sequence` is invoked
1726 explicitly before the INSERT statement takes place so that the
1727 newly generated primary key value is available to the client
1728 * SQL Server for :class:`_schema.Table` objects that
1729 set :paramref:`_schema.Table.implicit_returning` to False -
1730 the ``SELECT scope_identity()`` construct is used after the
1731 INSERT statement is invoked to retrieve the newly generated
1732 primary key value.
1733 * Third-party dialects - consult those dialects' documentation
1734 for details on their specific behaviors.
1736 * For multiple-row :func:`_sql.insert` constructs invoked with
1737 a list of parameters (i.e. "executemany" semantics), primary-key
1738 retrieving behaviors are generally disabled, however there may
1739 be special APIs that may be used to retrieve lists of new
1740 primary key values for an "executemany", such as the psycopg2
1741 "fast insertmany" feature. Such features are very new and
1742 may not yet be well covered in documentation.
1744 :param default: A scalar, Python callable, or
1745 :class:`_expression.ColumnElement` expression representing the
1746 *default value* for this column, which will be invoked upon insert
1747 if this column is otherwise not specified in the VALUES clause of
1748 the insert. This is a shortcut to using :class:`.ColumnDefault` as
1749 a positional argument; see that class for full detail on the
1750 structure of the argument.
1752 Contrast this argument to
1753 :paramref:`_schema.Column.server_default`
1754 which creates a default generator on the database side.
1756 .. seealso::
1758 :ref:`metadata_defaults_toplevel`
1760 :param insert_default: An alias of :paramref:`.Column.default`
1761 for compatibility with :func:`_orm.mapped_column`.
1763 .. versionadded: 2.0.31
1765 :param doc: optional String that can be used by the ORM or similar
1766 to document attributes on the Python side. This attribute does
1767 **not** render SQL comments; use the
1768 :paramref:`_schema.Column.comment`
1769 parameter for this purpose.
1771 :param key: An optional string identifier which will identify this
1772 ``Column`` object on the :class:`_schema.Table`.
1773 When a key is provided,
1774 this is the only identifier referencing the ``Column`` within the
1775 application, including ORM attribute mapping; the ``name`` field
1776 is used only when rendering SQL.
1778 :param index: When ``True``, indicates that a :class:`_schema.Index`
1779 construct will be automatically generated for this
1780 :class:`_schema.Column`, which will result in a "CREATE INDEX"
1781 statement being emitted for the :class:`_schema.Table` when the DDL
1782 create operation is invoked.
1784 Using this flag is equivalent to making use of the
1785 :class:`_schema.Index` construct explicitly at the level of the
1786 :class:`_schema.Table` construct itself::
1788 Table(
1789 "some_table",
1790 metadata,
1791 Column("x", Integer),
1792 Index("ix_some_table_x", "x")
1793 )
1795 To add the :paramref:`_schema.Index.unique` flag to the
1796 :class:`_schema.Index`, set both the
1797 :paramref:`_schema.Column.unique` and
1798 :paramref:`_schema.Column.index` flags to True simultaneously,
1799 which will have the effect of rendering the "CREATE UNIQUE INDEX"
1800 DDL instruction instead of "CREATE INDEX".
1802 The name of the index is generated using the
1803 :ref:`default naming convention <constraint_default_naming_convention>`
1804 which for the :class:`_schema.Index` construct is of the form
1805 ``ix_<tablename>_<columnname>``.
1807 As this flag is intended only as a convenience for the common case
1808 of adding a single-column, default configured index to a table
1809 definition, explicit use of the :class:`_schema.Index` construct
1810 should be preferred for most use cases, including composite indexes
1811 that encompass more than one column, indexes with SQL expressions
1812 or ordering, backend-specific index configuration options, and
1813 indexes that use a specific name.
1815 .. note:: the :attr:`_schema.Column.index` attribute on
1816 :class:`_schema.Column`
1817 **does not indicate** if this column is indexed or not, only
1818 if this flag was explicitly set here. To view indexes on
1819 a column, view the :attr:`_schema.Table.indexes` collection
1820 or use :meth:`_reflection.Inspector.get_indexes`.
1822 .. seealso::
1824 :ref:`schema_indexes`
1826 :ref:`constraint_naming_conventions`
1828 :paramref:`_schema.Column.unique`
1830 :param info: Optional data dictionary which will be populated into the
1831 :attr:`.SchemaItem.info` attribute of this object.
1833 :param nullable: When set to ``False``, will cause the "NOT NULL"
1834 phrase to be added when generating DDL for the column. When
1835 ``True``, will normally generate nothing (in SQL this defaults to
1836 "NULL"), except in some very specific backend-specific edge cases
1837 where "NULL" may render explicitly.
1838 Defaults to ``True`` unless :paramref:`_schema.Column.primary_key`
1839 is also ``True`` or the column specifies a :class:`_sql.Identity`,
1840 in which case it defaults to ``False``.
1841 This parameter is only used when issuing CREATE TABLE statements.
1843 .. note::
1845 When the column specifies a :class:`_sql.Identity` this
1846 parameter is in general ignored by the DDL compiler. The
1847 PostgreSQL database allows nullable identity column by
1848 setting this parameter to ``True`` explicitly.
1850 :param onupdate: A scalar, Python callable, or
1851 :class:`~sqlalchemy.sql.expression.ClauseElement` representing a
1852 default value to be applied to the column within UPDATE
1853 statements, which will be invoked upon update if this column is not
1854 present in the SET clause of the update. This is a shortcut to
1855 using :class:`.ColumnDefault` as a positional argument with
1856 ``for_update=True``.
1858 .. seealso::
1860 :ref:`metadata_defaults` - complete discussion of onupdate
1862 :param primary_key: If ``True``, marks this column as a primary key
1863 column. Multiple columns can have this flag set to specify
1864 composite primary keys. As an alternative, the primary key of a
1865 :class:`_schema.Table` can be specified via an explicit
1866 :class:`.PrimaryKeyConstraint` object.
1868 :param server_default: A :class:`.FetchedValue` instance, str, Unicode
1869 or :func:`~sqlalchemy.sql.expression.text` construct representing
1870 the DDL DEFAULT value for the column.
1872 String types will be emitted as-is, surrounded by single quotes::
1874 Column('x', Text, server_default="val")
1876 x TEXT DEFAULT 'val'
1878 A :func:`~sqlalchemy.sql.expression.text` expression will be
1879 rendered as-is, without quotes::
1881 Column('y', DateTime, server_default=text('NOW()'))
1883 y DATETIME DEFAULT NOW()
1885 Strings and text() will be converted into a
1886 :class:`.DefaultClause` object upon initialization.
1888 This parameter can also accept complex combinations of contextually
1889 valid SQLAlchemy expressions or constructs::
1891 from sqlalchemy import create_engine
1892 from sqlalchemy import Table, Column, MetaData, ARRAY, Text
1893 from sqlalchemy.dialects.postgresql import array
1895 engine = create_engine(
1896 'postgresql+psycopg2://scott:tiger@localhost/mydatabase'
1897 )
1898 metadata_obj = MetaData()
1899 tbl = Table(
1900 "foo",
1901 metadata_obj,
1902 Column("bar",
1903 ARRAY(Text),
1904 server_default=array(["biz", "bang", "bash"])
1905 )
1906 )
1907 metadata_obj.create_all(engine)
1909 The above results in a table created with the following SQL::
1911 CREATE TABLE foo (
1912 bar TEXT[] DEFAULT ARRAY['biz', 'bang', 'bash']
1913 )
1915 Use :class:`.FetchedValue` to indicate that an already-existing
1916 column will generate a default value on the database side which
1917 will be available to SQLAlchemy for post-fetch after inserts. This
1918 construct does not specify any DDL and the implementation is left
1919 to the database, such as via a trigger.
1921 .. seealso::
1923 :ref:`server_defaults` - complete discussion of server side
1924 defaults
1926 :param server_onupdate: A :class:`.FetchedValue` instance
1927 representing a database-side default generation function,
1928 such as a trigger. This
1929 indicates to SQLAlchemy that a newly generated value will be
1930 available after updates. This construct does not actually
1931 implement any kind of generation function within the database,
1932 which instead must be specified separately.
1935 .. warning:: This directive **does not** currently produce MySQL's
1936 "ON UPDATE CURRENT_TIMESTAMP()" clause. See
1937 :ref:`mysql_timestamp_onupdate` for background on how to
1938 produce this clause.
1940 .. seealso::
1942 :ref:`triggered_columns`
1944 :param quote: Force quoting of this column's name on or off,
1945 corresponding to ``True`` or ``False``. When left at its default
1946 of ``None``, the column identifier will be quoted according to
1947 whether the name is case sensitive (identifiers with at least one
1948 upper case character are treated as case sensitive), or if it's a
1949 reserved word. This flag is only needed to force quoting of a
1950 reserved word which is not known by the SQLAlchemy dialect.
1952 :param unique: When ``True``, and the :paramref:`_schema.Column.index`
1953 parameter is left at its default value of ``False``,
1954 indicates that a :class:`_schema.UniqueConstraint`
1955 construct will be automatically generated for this
1956 :class:`_schema.Column`,
1957 which will result in a "UNIQUE CONSTRAINT" clause referring
1958 to this column being included
1959 in the ``CREATE TABLE`` statement emitted, when the DDL create
1960 operation for the :class:`_schema.Table` object is invoked.
1962 When this flag is ``True`` while the
1963 :paramref:`_schema.Column.index` parameter is simultaneously
1964 set to ``True``, the effect instead is that a
1965 :class:`_schema.Index` construct which includes the
1966 :paramref:`_schema.Index.unique` parameter set to ``True``
1967 is generated. See the documentation for
1968 :paramref:`_schema.Column.index` for additional detail.
1970 Using this flag is equivalent to making use of the
1971 :class:`_schema.UniqueConstraint` construct explicitly at the
1972 level of the :class:`_schema.Table` construct itself::
1974 Table(
1975 "some_table",
1976 metadata,
1977 Column("x", Integer),
1978 UniqueConstraint("x")
1979 )
1981 The :paramref:`_schema.UniqueConstraint.name` parameter
1982 of the unique constraint object is left at its default value
1983 of ``None``; in the absence of a :ref:`naming convention <constraint_naming_conventions>`
1984 for the enclosing :class:`_schema.MetaData`, the UNIQUE CONSTRAINT
1985 construct will be emitted as unnamed, which typically invokes
1986 a database-specific naming convention to take place.
1988 As this flag is intended only as a convenience for the common case
1989 of adding a single-column, default configured unique constraint to a table
1990 definition, explicit use of the :class:`_schema.UniqueConstraint` construct
1991 should be preferred for most use cases, including composite constraints
1992 that encompass more than one column, backend-specific index configuration options, and
1993 constraints that use a specific name.
1995 .. note:: the :attr:`_schema.Column.unique` attribute on
1996 :class:`_schema.Column`
1997 **does not indicate** if this column has a unique constraint or
1998 not, only if this flag was explicitly set here. To view
1999 indexes and unique constraints that may involve this column,
2000 view the
2001 :attr:`_schema.Table.indexes` and/or
2002 :attr:`_schema.Table.constraints` collections or use
2003 :meth:`_reflection.Inspector.get_indexes` and/or
2004 :meth:`_reflection.Inspector.get_unique_constraints`
2006 .. seealso::
2008 :ref:`schema_unique_constraint`
2010 :ref:`constraint_naming_conventions`
2012 :paramref:`_schema.Column.index`
2014 :param system: When ``True``, indicates this is a "system" column,
2015 that is a column which is automatically made available by the
2016 database, and should not be included in the columns list for a
2017 ``CREATE TABLE`` statement.
2019 For more elaborate scenarios where columns should be
2020 conditionally rendered differently on different backends,
2021 consider custom compilation rules for :class:`.CreateColumn`.
2023 :param comment: Optional string that will render an SQL comment on
2024 table creation.
2026 .. versionadded:: 1.2 Added the
2027 :paramref:`_schema.Column.comment`
2028 parameter to :class:`_schema.Column`.
2030 :param insert_sentinel: Marks this :class:`_schema.Column` as an
2031 :term:`insert sentinel` used for optimizing the performance of the
2032 :term:`insertmanyvalues` feature for tables that don't
2033 otherwise have qualifying primary key configurations.
2035 .. versionadded:: 2.0.10
2037 .. seealso::
2039 :func:`_schema.insert_sentinel` - all in one helper for declaring
2040 sentinel columns
2042 :ref:`engine_insertmanyvalues`
2044 :ref:`engine_insertmanyvalues_sentinel_columns`
2047 """ # noqa: E501, RST201, RST202
2049 l_args = [__name_pos, __type_pos] + list(args)
2050 del args
2052 if l_args:
2053 if isinstance(l_args[0], str):
2054 if name is not None:
2055 raise exc.ArgumentError(
2056 "May not pass name positionally and as a keyword."
2057 )
2058 name = l_args.pop(0) # type: ignore
2059 elif l_args[0] is None:
2060 l_args.pop(0)
2061 if l_args:
2062 coltype = l_args[0]
2064 if hasattr(coltype, "_sqla_type"):
2065 if type_ is not None:
2066 raise exc.ArgumentError(
2067 "May not pass type_ positionally and as a keyword."
2068 )
2069 type_ = l_args.pop(0) # type: ignore
2070 elif l_args[0] is None:
2071 l_args.pop(0)
2073 if name is not None:
2074 name = quoted_name(name, quote)
2075 elif quote is not None:
2076 raise exc.ArgumentError(
2077 "Explicit 'name' is required when sending 'quote' argument"
2078 )
2080 # name = None is expected to be an interim state
2081 # note this use case is legacy now that ORM declarative has a
2082 # dedicated "column" construct local to the ORM
2083 super().__init__(name, type_) # type: ignore
2085 self.key = key if key is not None else name # type: ignore
2086 self.primary_key = primary_key
2087 self._insert_sentinel = insert_sentinel
2088 self._omit_from_statements = _omit_from_statements
2089 self._user_defined_nullable = udn = nullable
2090 if udn is not NULL_UNSPECIFIED:
2091 self.nullable = udn
2092 else:
2093 self.nullable = not primary_key
2095 # these default to None because .index and .unique is *not*
2096 # an informational flag about Column - there can still be an
2097 # Index or UniqueConstraint referring to this Column.
2098 self.index = index
2099 self.unique = unique
2101 self.system = system
2102 self.doc = doc
2103 self.autoincrement: _AutoIncrementType = autoincrement
2104 self.constraints = set()
2105 self.foreign_keys = set()
2106 self.comment = comment
2107 self.computed = None
2108 self.identity = None
2110 # check if this Column is proxying another column
2112 if _proxies is not None:
2113 self._proxies = _proxies
2114 else:
2115 # otherwise, add DDL-related events
2116 self._set_type(self.type)
2118 if insert_default is not _NoArg.NO_ARG:
2119 resolved_default = insert_default
2120 elif default is not _NoArg.NO_ARG:
2121 resolved_default = default
2122 else:
2123 resolved_default = None
2125 if resolved_default is not None:
2126 if not isinstance(resolved_default, (ColumnDefault, Sequence)):
2127 resolved_default = ColumnDefault(resolved_default)
2129 self.default = resolved_default
2130 l_args.append(resolved_default)
2131 else:
2132 self.default = None
2134 if onupdate is not None:
2135 if not isinstance(onupdate, (ColumnDefault, Sequence)):
2136 onupdate = ColumnDefault(onupdate, for_update=True)
2138 self.onupdate = onupdate
2139 l_args.append(onupdate)
2140 else:
2141 self.onupdate = None
2143 if server_default is not None:
2144 if isinstance(server_default, FetchedValue):
2145 server_default = server_default._as_for_update(False)
2146 l_args.append(server_default)
2147 else:
2148 server_default = DefaultClause(server_default)
2149 l_args.append(server_default)
2150 self.server_default = server_default
2152 if server_onupdate is not None:
2153 if isinstance(server_onupdate, FetchedValue):
2154 server_onupdate = server_onupdate._as_for_update(True)
2155 l_args.append(server_onupdate)
2156 else:
2157 server_onupdate = DefaultClause(
2158 server_onupdate, for_update=True
2159 )
2160 l_args.append(server_onupdate)
2161 self.server_onupdate = server_onupdate
2163 self._init_items(*cast(_typing_Sequence[SchemaItem], l_args))
2165 util.set_creation_order(self)
2167 if info is not None:
2168 self.info = info
2170 self._extra_kwargs(**dialect_kwargs)
2172 table: Table
2174 constraints: Set[Constraint]
2176 foreign_keys: Set[ForeignKey]
2177 """A collection of all :class:`_schema.ForeignKey` marker objects
2178 associated with this :class:`_schema.Column`.
2180 Each object is a member of a :class:`_schema.Table`-wide
2181 :class:`_schema.ForeignKeyConstraint`.
2183 .. seealso::
2185 :attr:`_schema.Table.foreign_keys`
2187 """
2189 index: Optional[bool]
2190 """The value of the :paramref:`_schema.Column.index` parameter.
2192 Does not indicate if this :class:`_schema.Column` is actually indexed
2193 or not; use :attr:`_schema.Table.indexes`.
2195 .. seealso::
2197 :attr:`_schema.Table.indexes`
2198 """
2200 unique: Optional[bool]
2201 """The value of the :paramref:`_schema.Column.unique` parameter.
2203 Does not indicate if this :class:`_schema.Column` is actually subject to
2204 a unique constraint or not; use :attr:`_schema.Table.indexes` and
2205 :attr:`_schema.Table.constraints`.
2207 .. seealso::
2209 :attr:`_schema.Table.indexes`
2211 :attr:`_schema.Table.constraints`.
2213 """
2215 computed: Optional[Computed]
2217 identity: Optional[Identity]
2219 def _set_type(self, type_: TypeEngine[Any]) -> None:
2220 assert self.type._isnull or type_ is self.type
2222 self.type = type_
2223 if isinstance(self.type, SchemaEventTarget):
2224 self.type._set_parent_with_dispatch(self)
2225 for impl in self.type._variant_mapping.values():
2226 if isinstance(impl, SchemaEventTarget):
2227 impl._set_parent_with_dispatch(self)
2229 @HasMemoized.memoized_attribute
2230 def _default_description_tuple(self) -> _DefaultDescriptionTuple:
2231 """used by default.py -> _process_execute_defaults()"""
2233 return _DefaultDescriptionTuple._from_column_default(self.default)
2235 @HasMemoized.memoized_attribute
2236 def _onupdate_description_tuple(self) -> _DefaultDescriptionTuple:
2237 """used by default.py -> _process_execute_defaults()"""
2238 return _DefaultDescriptionTuple._from_column_default(self.onupdate)
2240 @util.memoized_property
2241 def _gen_static_annotations_cache_key(self) -> bool: # type: ignore
2242 """special attribute used by cache key gen, if true, we will
2243 use a static cache key for the annotations dictionary, else we
2244 will generate a new cache key for annotations each time.
2246 Added for #8790
2248 """
2249 return self.table is not None and self.table._is_table
2251 def _extra_kwargs(self, **kwargs: Any) -> None:
2252 self._validate_dialect_kwargs(kwargs)
2254 def __str__(self) -> str:
2255 if self.name is None:
2256 return "(no name)"
2257 elif self.table is not None:
2258 if self.table.named_with_column:
2259 return self.table.description + "." + self.description
2260 else:
2261 return self.description
2262 else:
2263 return self.description
2265 def references(self, column: Column[Any]) -> bool:
2266 """Return True if this Column references the given column via foreign
2267 key."""
2269 for fk in self.foreign_keys:
2270 if fk.column.proxy_set.intersection(column.proxy_set):
2271 return True
2272 else:
2273 return False
2275 def append_foreign_key(self, fk: ForeignKey) -> None:
2276 fk._set_parent_with_dispatch(self)
2278 def __repr__(self) -> str:
2279 kwarg = []
2280 if self.key != self.name:
2281 kwarg.append("key")
2282 if self.primary_key:
2283 kwarg.append("primary_key")
2284 if not self.nullable:
2285 kwarg.append("nullable")
2286 if self.onupdate:
2287 kwarg.append("onupdate")
2288 if self.default:
2289 kwarg.append("default")
2290 if self.server_default:
2291 kwarg.append("server_default")
2292 if self.comment:
2293 kwarg.append("comment")
2294 return "Column(%s)" % ", ".join(
2295 [repr(self.name)]
2296 + [repr(self.type)]
2297 + [repr(x) for x in self.foreign_keys if x is not None]
2298 + [repr(x) for x in self.constraints]
2299 + [
2300 (
2301 self.table is not None
2302 and "table=<%s>" % self.table.description
2303 or "table=None"
2304 )
2305 ]
2306 + ["%s=%s" % (k, repr(getattr(self, k))) for k in kwarg]
2307 )
2309 def _set_parent( # type: ignore[override]
2310 self,
2311 parent: SchemaEventTarget,
2312 *,
2313 all_names: Dict[str, Column[Any]],
2314 allow_replacements: bool,
2315 **kw: Any,
2316 ) -> None:
2317 table = parent
2318 assert isinstance(table, Table)
2319 if not self.name:
2320 raise exc.ArgumentError(
2321 "Column must be constructed with a non-blank name or "
2322 "assign a non-blank .name before adding to a Table."
2323 )
2325 self._reset_memoizations()
2327 if self.key is None:
2328 self.key = self.name
2330 existing = getattr(self, "table", None)
2331 if existing is not None and existing is not table:
2332 raise exc.ArgumentError(
2333 f"Column object '{self.key}' already "
2334 f"assigned to Table '{existing.description}'"
2335 )
2337 extra_remove = None
2338 existing_col = None
2339 conflicts_on = ""
2341 if self.key in table._columns:
2342 existing_col = table._columns[self.key]
2343 if self.key == self.name:
2344 conflicts_on = "name"
2345 else:
2346 conflicts_on = "key"
2347 elif self.name in all_names:
2348 existing_col = all_names[self.name]
2349 extra_remove = {existing_col}
2350 conflicts_on = "name"
2352 if existing_col is not None:
2353 if existing_col is not self:
2354 if not allow_replacements:
2355 raise exc.DuplicateColumnError(
2356 f"A column with {conflicts_on} "
2357 f"""'{
2358 self.key if conflicts_on == 'key' else self.name
2359 }' """
2360 f"is already present in table '{table.name}'."
2361 )
2362 for fk in existing_col.foreign_keys:
2363 table.foreign_keys.remove(fk)
2364 if fk.constraint in table.constraints:
2365 # this might have been removed
2366 # already, if it's a composite constraint
2367 # and more than one col being replaced
2368 table.constraints.remove(fk.constraint)
2370 if extra_remove and existing_col is not None and self.key == self.name:
2371 util.warn(
2372 f'Column with user-specified key "{existing_col.key}" is '
2373 "being replaced with "
2374 f'plain named column "{self.name}", '
2375 f'key "{existing_col.key}" is being removed. If this is a '
2376 "reflection operation, specify autoload_replace=False to "
2377 "prevent this replacement."
2378 )
2379 table._columns.replace(self, extra_remove=extra_remove)
2380 all_names[self.name] = self
2381 self.table = table
2383 if self._insert_sentinel:
2384 if self.table._sentinel_column is not None:
2385 raise exc.ArgumentError(
2386 "a Table may have only one explicit sentinel column"
2387 )
2388 self.table._sentinel_column = self
2390 if self.primary_key:
2391 table.primary_key._replace(self)
2392 elif self.key in table.primary_key:
2393 raise exc.ArgumentError(
2394 f"Trying to redefine primary-key column '{self.key}' as a "
2395 f"non-primary-key column on table '{table.fullname}'"
2396 )
2398 if self.index:
2399 if isinstance(self.index, str):
2400 raise exc.ArgumentError(
2401 "The 'index' keyword argument on Column is boolean only. "
2402 "To create indexes with a specific name, create an "
2403 "explicit Index object external to the Table."
2404 )
2405 table.append_constraint(
2406 Index(
2407 None, self.key, unique=bool(self.unique), _column_flag=True
2408 )
2409 )
2411 elif self.unique:
2412 if isinstance(self.unique, str):
2413 raise exc.ArgumentError(
2414 "The 'unique' keyword argument on Column is boolean "
2415 "only. To create unique constraints or indexes with a "
2416 "specific name, append an explicit UniqueConstraint to "
2417 "the Table's list of elements, or create an explicit "
2418 "Index object external to the Table."
2419 )
2420 table.append_constraint(
2421 UniqueConstraint(self.key, _column_flag=True)
2422 )
2424 self._setup_on_memoized_fks(lambda fk: fk._set_remote_table(table))
2426 if self.identity and (
2427 isinstance(self.default, Sequence)
2428 or isinstance(self.onupdate, Sequence)
2429 ):
2430 raise exc.ArgumentError(
2431 "An column cannot specify both Identity and Sequence."
2432 )
2434 def _setup_on_memoized_fks(self, fn: Callable[..., Any]) -> None:
2435 fk_keys = [
2436 ((self.table.key, self.key), False),
2437 ((self.table.key, self.name), True),
2438 ]
2439 for fk_key, link_to_name in fk_keys:
2440 if fk_key in self.table.metadata._fk_memos:
2441 for fk in self.table.metadata._fk_memos[fk_key]:
2442 if fk.link_to_name is link_to_name:
2443 fn(fk)
2445 def _on_table_attach(self, fn: Callable[..., Any]) -> None:
2446 if self.table is not None:
2447 fn(self, self.table)
2448 else:
2449 event.listen(self, "after_parent_attach", fn)
2451 @util.deprecated(
2452 "1.4",
2453 "The :meth:`_schema.Column.copy` method is deprecated "
2454 "and will be removed in a future release.",
2455 )
2456 def copy(self, **kw: Any) -> Column[Any]:
2457 return self._copy(**kw)
2459 def _copy(self, **kw: Any) -> Column[Any]:
2460 """Create a copy of this ``Column``, uninitialized.
2462 This is used in :meth:`_schema.Table.to_metadata` and by the ORM.
2464 """
2466 # Constraint objects plus non-constraint-bound ForeignKey objects
2467 args: List[SchemaItem] = [
2468 c._copy(**kw) for c in self.constraints if not c._type_bound
2469 ] + [c._copy(**kw) for c in self.foreign_keys if not c.constraint]
2471 # ticket #5276
2472 column_kwargs = {}
2473 for dialect_name in self.dialect_options:
2474 dialect_options = self.dialect_options[dialect_name]._non_defaults
2475 for (
2476 dialect_option_key,
2477 dialect_option_value,
2478 ) in dialect_options.items():
2479 column_kwargs[dialect_name + "_" + dialect_option_key] = (
2480 dialect_option_value
2481 )
2483 server_default = self.server_default
2484 server_onupdate = self.server_onupdate
2485 if isinstance(server_default, (Computed, Identity)):
2486 # TODO: likely should be copied in all cases
2487 args.append(server_default._copy(**kw))
2488 server_default = server_onupdate = None
2490 type_ = self.type
2491 if isinstance(type_, SchemaEventTarget):
2492 type_ = type_.copy(**kw)
2494 # TODO: DefaultGenerator is not copied here! it's just used again
2495 # with _set_parent() pointing to the old column. see the new
2496 # use of _copy() in the new _merge() method
2498 c = self._constructor(
2499 name=self.name,
2500 type_=type_,
2501 key=self.key,
2502 primary_key=self.primary_key,
2503 unique=self.unique,
2504 system=self.system,
2505 # quote=self.quote, # disabled 2013-08-27 (commit 031ef080)
2506 index=self.index,
2507 autoincrement=self.autoincrement,
2508 default=self.default,
2509 server_default=server_default,
2510 onupdate=self.onupdate,
2511 server_onupdate=server_onupdate,
2512 doc=self.doc,
2513 comment=self.comment,
2514 _omit_from_statements=self._omit_from_statements,
2515 insert_sentinel=self._insert_sentinel,
2516 *args,
2517 **column_kwargs,
2518 )
2520 # copy the state of "nullable" exactly, to accommodate for
2521 # ORM flipping the .nullable flag directly
2522 c.nullable = self.nullable
2523 c._user_defined_nullable = self._user_defined_nullable
2525 return self._schema_item_copy(c)
2527 def _merge(self, other: Column[Any]) -> None:
2528 """merge the elements of another column into this one.
2530 this is used by ORM pep-593 merge and will likely need a lot
2531 of fixes.
2534 """
2536 if self.primary_key:
2537 other.primary_key = True
2539 if self.autoincrement != "auto" and other.autoincrement == "auto":
2540 other.autoincrement = self.autoincrement
2542 if self.system:
2543 other.system = self.system
2545 if self.info:
2546 other.info.update(self.info)
2548 type_ = self.type
2549 if not type_._isnull and other.type._isnull:
2550 if isinstance(type_, SchemaEventTarget):
2551 type_ = type_.copy()
2553 other.type = type_
2555 if isinstance(type_, SchemaEventTarget):
2556 type_._set_parent_with_dispatch(other)
2558 for impl in type_._variant_mapping.values():
2559 if isinstance(impl, SchemaEventTarget):
2560 impl._set_parent_with_dispatch(other)
2562 if (
2563 self._user_defined_nullable is not NULL_UNSPECIFIED
2564 and other._user_defined_nullable is NULL_UNSPECIFIED
2565 ):
2566 other.nullable = self.nullable
2567 other._user_defined_nullable = self._user_defined_nullable
2569 if self.default is not None and other.default is None:
2570 new_default = self.default._copy()
2571 new_default._set_parent(other)
2573 if self.server_default and other.server_default is None:
2574 new_server_default = self.server_default
2575 if isinstance(new_server_default, FetchedValue):
2576 new_server_default = new_server_default._copy()
2577 new_server_default._set_parent(other)
2578 else:
2579 other.server_default = new_server_default
2581 if self.server_onupdate and other.server_onupdate is None:
2582 new_server_onupdate = self.server_onupdate
2583 new_server_onupdate = new_server_onupdate._copy()
2584 new_server_onupdate._set_parent(other)
2586 if self.onupdate and other.onupdate is None:
2587 new_onupdate = self.onupdate._copy()
2588 new_onupdate._set_parent(other)
2590 if self.index in (True, False) and other.index is None:
2591 other.index = self.index
2593 if self.unique in (True, False) and other.unique is None:
2594 other.unique = self.unique
2596 if self.doc and other.doc is None:
2597 other.doc = self.doc
2599 if self.comment and other.comment is None:
2600 other.comment = self.comment
2602 for const in self.constraints:
2603 if not const._type_bound:
2604 new_const = const._copy()
2605 new_const._set_parent(other)
2607 for fk in self.foreign_keys:
2608 if not fk.constraint:
2609 new_fk = fk._copy()
2610 new_fk._set_parent(other)
2612 def _make_proxy(
2613 self,
2614 selectable: FromClause,
2615 name: Optional[str] = None,
2616 key: Optional[str] = None,
2617 name_is_truncatable: bool = False,
2618 compound_select_cols: Optional[
2619 _typing_Sequence[ColumnElement[Any]]
2620 ] = None,
2621 **kw: Any,
2622 ) -> Tuple[str, ColumnClause[_T]]:
2623 """Create a *proxy* for this column.
2625 This is a copy of this ``Column`` referenced by a different parent
2626 (such as an alias or select statement). The column should
2627 be used only in select scenarios, as its full DDL/default
2628 information is not transferred.
2630 """
2632 fk = [
2633 ForeignKey(
2634 col if col is not None else f._colspec,
2635 _unresolvable=col is None,
2636 _constraint=f.constraint,
2637 )
2638 for f, col in [
2639 (fk, fk._resolve_column(raiseerr=False))
2640 for fk in self.foreign_keys
2641 ]
2642 ]
2644 if name is None and self.name is None:
2645 raise exc.InvalidRequestError(
2646 "Cannot initialize a sub-selectable"
2647 " with this Column object until its 'name' has "
2648 "been assigned."
2649 )
2650 try:
2651 c = self._constructor(
2652 (
2653 coercions.expect(
2654 roles.TruncatedLabelRole, name if name else self.name
2655 )
2656 if name_is_truncatable
2657 else (name or self.name)
2658 ),
2659 self.type,
2660 # this may actually be ._proxy_key when the key is incoming
2661 key=key if key else name if name else self.key,
2662 primary_key=self.primary_key,
2663 nullable=self.nullable,
2664 _proxies=(
2665 list(compound_select_cols)
2666 if compound_select_cols
2667 else [self]
2668 ),
2669 *fk,
2670 )
2671 except TypeError as err:
2672 raise TypeError(
2673 "Could not create a copy of this %r object. "
2674 "Ensure the class includes a _constructor() "
2675 "attribute or method which accepts the "
2676 "standard Column constructor arguments, or "
2677 "references the Column class itself." % self.__class__
2678 ) from err
2680 c.table = selectable
2681 c._propagate_attrs = selectable._propagate_attrs
2682 if selectable._is_clone_of is not None:
2683 c._is_clone_of = selectable._is_clone_of.columns.get(c.key)
2684 if self.primary_key:
2685 selectable.primary_key.add(c) # type: ignore
2686 if fk:
2687 selectable.foreign_keys.update(fk) # type: ignore
2688 return c.key, c
2691def insert_sentinel(
2692 name: Optional[str] = None,
2693 type_: Optional[_TypeEngineArgument[_T]] = None,
2694 *,
2695 default: Optional[Any] = None,
2696 omit_from_statements: bool = True,
2697) -> Column[Any]:
2698 """Provides a surrogate :class:`_schema.Column` that will act as a
2699 dedicated insert :term:`sentinel` column, allowing efficient bulk
2700 inserts with deterministic RETURNING sorting for tables that
2701 don't otherwise have qualifying primary key configurations.
2703 Adding this column to a :class:`.Table` object requires that a
2704 corresponding database table actually has this column present, so if adding
2705 it to an existing model, existing database tables would need to be migrated
2706 (e.g. using ALTER TABLE or similar) to include this column.
2708 For background on how this object is used, see the section
2709 :ref:`engine_insertmanyvalues_sentinel_columns` as part of the
2710 section :ref:`engine_insertmanyvalues`.
2712 The :class:`_schema.Column` returned will be a nullable integer column by
2713 default and make use of a sentinel-specific default generator used only in
2714 "insertmanyvalues" operations.
2716 .. seealso::
2718 :func:`_orm.orm_insert_sentinel`
2720 :paramref:`_schema.Column.insert_sentinel`
2722 :ref:`engine_insertmanyvalues`
2724 :ref:`engine_insertmanyvalues_sentinel_columns`
2727 .. versionadded:: 2.0.10
2729 """
2730 return Column(
2731 name=name,
2732 type_=type_api.INTEGERTYPE if type_ is None else type_,
2733 default=(
2734 default if default is not None else _InsertSentinelColumnDefault()
2735 ),
2736 _omit_from_statements=omit_from_statements,
2737 insert_sentinel=True,
2738 )
2741class ForeignKey(DialectKWArgs, SchemaItem):
2742 """Defines a dependency between two columns.
2744 ``ForeignKey`` is specified as an argument to a :class:`_schema.Column`
2745 object,
2746 e.g.::
2748 t = Table("remote_table", metadata,
2749 Column("remote_id", ForeignKey("main_table.id"))
2750 )
2752 Note that ``ForeignKey`` is only a marker object that defines
2753 a dependency between two columns. The actual constraint
2754 is in all cases represented by the :class:`_schema.ForeignKeyConstraint`
2755 object. This object will be generated automatically when
2756 a ``ForeignKey`` is associated with a :class:`_schema.Column` which
2757 in turn is associated with a :class:`_schema.Table`. Conversely,
2758 when :class:`_schema.ForeignKeyConstraint` is applied to a
2759 :class:`_schema.Table`,
2760 ``ForeignKey`` markers are automatically generated to be
2761 present on each associated :class:`_schema.Column`, which are also
2762 associated with the constraint object.
2764 Note that you cannot define a "composite" foreign key constraint,
2765 that is a constraint between a grouping of multiple parent/child
2766 columns, using ``ForeignKey`` objects. To define this grouping,
2767 the :class:`_schema.ForeignKeyConstraint` object must be used, and applied
2768 to the :class:`_schema.Table`. The associated ``ForeignKey`` objects
2769 are created automatically.
2771 The ``ForeignKey`` objects associated with an individual
2772 :class:`_schema.Column`
2773 object are available in the `foreign_keys` collection
2774 of that column.
2776 Further examples of foreign key configuration are in
2777 :ref:`metadata_foreignkeys`.
2779 """
2781 __visit_name__ = "foreign_key"
2783 parent: Column[Any]
2785 _table_column: Optional[Column[Any]]
2787 _colspec: Union[str, Column[Any]]
2789 def __init__(
2790 self,
2791 column: _DDLColumnReferenceArgument,
2792 _constraint: Optional[ForeignKeyConstraint] = None,
2793 use_alter: bool = False,
2794 name: _ConstraintNameArgument = None,
2795 onupdate: Optional[str] = None,
2796 ondelete: Optional[str] = None,
2797 deferrable: Optional[bool] = None,
2798 initially: Optional[str] = None,
2799 link_to_name: bool = False,
2800 match: Optional[str] = None,
2801 info: Optional[_InfoType] = None,
2802 comment: Optional[str] = None,
2803 _unresolvable: bool = False,
2804 **dialect_kw: Any,
2805 ):
2806 r"""
2807 Construct a column-level FOREIGN KEY.
2809 The :class:`_schema.ForeignKey` object when constructed generates a
2810 :class:`_schema.ForeignKeyConstraint`
2811 which is associated with the parent
2812 :class:`_schema.Table` object's collection of constraints.
2814 :param column: A single target column for the key relationship. A
2815 :class:`_schema.Column` object or a column name as a string:
2816 ``tablename.columnkey`` or ``schema.tablename.columnkey``.
2817 ``columnkey`` is the ``key`` which has been assigned to the column
2818 (defaults to the column name itself), unless ``link_to_name`` is
2819 ``True`` in which case the rendered name of the column is used.
2821 :param name: Optional string. An in-database name for the key if
2822 `constraint` is not provided.
2824 :param onupdate: Optional string. If set, emit ON UPDATE <value> when
2825 issuing DDL for this constraint. Typical values include CASCADE,
2826 DELETE and RESTRICT.
2828 :param ondelete: Optional string. If set, emit ON DELETE <value> when
2829 issuing DDL for this constraint. Typical values include CASCADE,
2830 DELETE and RESTRICT.
2832 :param deferrable: Optional bool. If set, emit DEFERRABLE or NOT
2833 DEFERRABLE when issuing DDL for this constraint.
2835 :param initially: Optional string. If set, emit INITIALLY <value> when
2836 issuing DDL for this constraint.
2838 :param link_to_name: if True, the string name given in ``column`` is
2839 the rendered name of the referenced column, not its locally
2840 assigned ``key``.
2842 :param use_alter: passed to the underlying
2843 :class:`_schema.ForeignKeyConstraint`
2844 to indicate the constraint should
2845 be generated/dropped externally from the CREATE TABLE/ DROP TABLE
2846 statement. See :paramref:`_schema.ForeignKeyConstraint.use_alter`
2847 for further description.
2849 .. seealso::
2851 :paramref:`_schema.ForeignKeyConstraint.use_alter`
2853 :ref:`use_alter`
2855 :param match: Optional string. If set, emit MATCH <value> when issuing
2856 DDL for this constraint. Typical values include SIMPLE, PARTIAL
2857 and FULL.
2859 :param info: Optional data dictionary which will be populated into the
2860 :attr:`.SchemaItem.info` attribute of this object.
2862 :param comment: Optional string that will render an SQL comment on
2863 foreign key constraint creation.
2865 .. versionadded:: 2.0
2867 :param \**dialect_kw: Additional keyword arguments are dialect
2868 specific, and passed in the form ``<dialectname>_<argname>``. The
2869 arguments are ultimately handled by a corresponding
2870 :class:`_schema.ForeignKeyConstraint`.
2871 See the documentation regarding
2872 an individual dialect at :ref:`dialect_toplevel` for detail on
2873 documented arguments.
2875 """
2877 self._unresolvable = _unresolvable
2879 self._colspec, self._table_column = self._parse_colspec_argument(
2880 column
2881 )
2883 # the linked ForeignKeyConstraint.
2884 # ForeignKey will create this when parent Column
2885 # is attached to a Table, *or* ForeignKeyConstraint
2886 # object passes itself in when creating ForeignKey
2887 # markers.
2888 self.constraint = _constraint
2890 # .parent is not Optional under normal use
2891 self.parent = None # type: ignore
2893 self.use_alter = use_alter
2894 self.name = name
2895 self.onupdate = onupdate
2896 self.ondelete = ondelete
2897 self.deferrable = deferrable
2898 self.initially = initially
2899 self.link_to_name = link_to_name
2900 self.match = match
2901 self.comment = comment
2902 if info:
2903 self.info = info
2904 self._unvalidated_dialect_kw = dialect_kw
2906 def _resolve_colspec_argument(
2907 self,
2908 ) -> Tuple[
2909 Union[str, Column[Any]],
2910 Optional[Column[Any]],
2911 ]:
2912 argument = self._colspec
2914 return self._parse_colspec_argument(argument)
2916 def _parse_colspec_argument(
2917 self,
2918 argument: _DDLColumnArgument,
2919 ) -> Tuple[
2920 Union[str, Column[Any]],
2921 Optional[Column[Any]],
2922 ]:
2923 _colspec = coercions.expect(roles.DDLReferredColumnRole, argument)
2925 if isinstance(_colspec, str):
2926 _table_column = None
2927 else:
2928 assert isinstance(_colspec, ColumnClause)
2929 _table_column = _colspec
2931 if not isinstance(_table_column.table, (type(None), TableClause)):
2932 raise exc.ArgumentError(
2933 "ForeignKey received Column not bound "
2934 "to a Table, got: %r" % _table_column.table
2935 )
2937 return _colspec, _table_column
2939 def __repr__(self) -> str:
2940 return "ForeignKey(%r)" % self._get_colspec()
2942 @util.deprecated(
2943 "1.4",
2944 "The :meth:`_schema.ForeignKey.copy` method is deprecated "
2945 "and will be removed in a future release.",
2946 )
2947 def copy(self, *, schema: Optional[str] = None, **kw: Any) -> ForeignKey:
2948 return self._copy(schema=schema, **kw)
2950 def _copy(self, *, schema: Optional[str] = None, **kw: Any) -> ForeignKey:
2951 """Produce a copy of this :class:`_schema.ForeignKey` object.
2953 The new :class:`_schema.ForeignKey` will not be bound
2954 to any :class:`_schema.Column`.
2956 This method is usually used by the internal
2957 copy procedures of :class:`_schema.Column`, :class:`_schema.Table`,
2958 and :class:`_schema.MetaData`.
2960 :param schema: The returned :class:`_schema.ForeignKey` will
2961 reference the original table and column name, qualified
2962 by the given string schema name.
2964 """
2965 fk = ForeignKey(
2966 self._get_colspec(schema=schema),
2967 use_alter=self.use_alter,
2968 name=self.name,
2969 onupdate=self.onupdate,
2970 ondelete=self.ondelete,
2971 deferrable=self.deferrable,
2972 initially=self.initially,
2973 link_to_name=self.link_to_name,
2974 match=self.match,
2975 comment=self.comment,
2976 **self._unvalidated_dialect_kw,
2977 )
2978 return self._schema_item_copy(fk)
2980 def _get_colspec(
2981 self,
2982 schema: Optional[
2983 Union[
2984 str,
2985 Literal[SchemaConst.RETAIN_SCHEMA, SchemaConst.BLANK_SCHEMA],
2986 ]
2987 ] = None,
2988 table_name: Optional[str] = None,
2989 _is_copy: bool = False,
2990 ) -> str:
2991 """Return a string based 'column specification' for this
2992 :class:`_schema.ForeignKey`.
2994 This is usually the equivalent of the string-based "tablename.colname"
2995 argument first passed to the object's constructor.
2997 """
2999 _colspec, effective_table_column = self._resolve_colspec_argument()
3001 if schema not in (None, RETAIN_SCHEMA):
3002 _schema, tname, colname = self._column_tokens
3003 if table_name is not None:
3004 tname = table_name
3005 if schema is BLANK_SCHEMA:
3006 return "%s.%s" % (tname, colname)
3007 else:
3008 return "%s.%s.%s" % (schema, tname, colname)
3009 elif table_name:
3010 schema, tname, colname = self._column_tokens
3011 if schema:
3012 return "%s.%s.%s" % (schema, table_name, colname)
3013 else:
3014 return "%s.%s" % (table_name, colname)
3015 elif effective_table_column is not None:
3016 if effective_table_column.table is None:
3017 if _is_copy:
3018 raise exc.InvalidRequestError(
3019 f"Can't copy ForeignKey object which refers to "
3020 f"non-table bound Column {effective_table_column!r}"
3021 )
3022 else:
3023 return effective_table_column.key
3024 return "%s.%s" % (
3025 effective_table_column.table.fullname,
3026 effective_table_column.key,
3027 )
3028 else:
3029 assert isinstance(_colspec, str)
3030 return _colspec
3032 @property
3033 def _referred_schema(self) -> Optional[str]:
3034 return self._column_tokens[0]
3036 def _table_key_within_construction(self) -> Any:
3037 """get the table key but only safely"""
3039 if self._table_column is not None:
3040 if self._table_column.table is None:
3041 return None
3042 else:
3043 return self._table_column.table.key
3044 else:
3045 schema, tname, colname = self._column_tokens
3046 return _get_table_key(tname, schema)
3048 target_fullname = property(_get_colspec)
3050 def references(self, table: Table) -> bool:
3051 """Return True if the given :class:`_schema.Table`
3052 is referenced by this
3053 :class:`_schema.ForeignKey`."""
3055 return table.corresponding_column(self.column) is not None
3057 def get_referent(self, table: FromClause) -> Optional[Column[Any]]:
3058 """Return the :class:`_schema.Column` in the given
3059 :class:`_schema.Table` (or any :class:`.FromClause`)
3060 referenced by this :class:`_schema.ForeignKey`.
3062 Returns None if this :class:`_schema.ForeignKey`
3063 does not reference the given
3064 :class:`_schema.Table`.
3066 """
3067 # our column is a Column, and any subquery etc. proxying us
3068 # would be doing so via another Column, so that's what would
3069 # be returned here
3070 return table.columns.corresponding_column(self.column) # type: ignore
3072 @util.memoized_property
3073 def _column_tokens(self) -> Tuple[Optional[str], str, Optional[str]]:
3074 """parse a string-based _colspec into its component parts."""
3076 m = self._get_colspec().split(".")
3077 if len(m) == 1:
3078 tname = m.pop()
3079 colname = None
3080 else:
3081 colname = m.pop()
3082 tname = m.pop()
3084 # A FK between column 'bar' and table 'foo' can be
3085 # specified as 'foo', 'foo.bar', 'dbo.foo.bar',
3086 # 'otherdb.dbo.foo.bar'. Once we have the column name and
3087 # the table name, treat everything else as the schema
3088 # name. Some databases (e.g. Sybase) support
3089 # inter-database foreign keys. See tickets#1341 and --
3090 # indirectly related -- Ticket #594. This assumes that '.'
3091 # will never appear *within* any component of the FK.
3093 if len(m) > 0:
3094 schema = ".".join(m)
3095 else:
3096 schema = None
3097 return schema, tname, colname
3099 def _resolve_col_tokens(self) -> Tuple[Table, str, Optional[str]]:
3100 if self.parent is None:
3101 raise exc.InvalidRequestError(
3102 "this ForeignKey object does not yet have a "
3103 "parent Column associated with it."
3104 )
3106 elif self.parent.table is None:
3107 raise exc.InvalidRequestError(
3108 "this ForeignKey's parent column is not yet associated "
3109 "with a Table."
3110 )
3112 parenttable = self.parent.table
3114 if self._unresolvable:
3115 schema, tname, colname = self._column_tokens
3116 tablekey = _get_table_key(tname, schema)
3117 return parenttable, tablekey, colname
3119 # assertion
3120 # basically Column._make_proxy() sends the actual
3121 # target Column to the ForeignKey object, so the
3122 # string resolution here is never called.
3123 for c in self.parent.base_columns:
3124 if isinstance(c, Column):
3125 assert c.table is parenttable
3126 break
3127 else:
3128 assert False
3129 ######################
3131 schema, tname, colname = self._column_tokens
3133 if schema is None and parenttable.metadata.schema is not None:
3134 schema = parenttable.metadata.schema
3136 tablekey = _get_table_key(tname, schema)
3137 return parenttable, tablekey, colname
3139 def _link_to_col_by_colstring(
3140 self, parenttable: Table, table: Table, colname: Optional[str]
3141 ) -> Column[Any]:
3142 _column = None
3143 if colname is None:
3144 # colname is None in the case that ForeignKey argument
3145 # was specified as table name only, in which case we
3146 # match the column name to the same column on the
3147 # parent.
3148 # this use case wasn't working in later 1.x series
3149 # as it had no test coverage; fixed in 2.0
3150 parent = self.parent
3151 assert parent is not None
3152 key = parent.key
3153 _column = table.c.get(key, None)
3154 elif self.link_to_name:
3155 key = colname
3156 for c in table.c:
3157 if c.name == colname:
3158 _column = c
3159 else:
3160 key = colname
3161 _column = table.c.get(colname, None)
3163 if _column is None:
3164 raise exc.NoReferencedColumnError(
3165 "Could not initialize target column "
3166 f"for ForeignKey '{self._get_colspec()}' "
3167 f"on table '{parenttable.name}': "
3168 f"table '{table.name}' has no column named '{key}'",
3169 table.name,
3170 key,
3171 )
3173 return _column
3175 def _set_target_column(self, column: Column[Any]) -> None:
3176 assert self.parent is not None
3178 # propagate TypeEngine to parent if it didn't have one
3179 if self.parent.type._isnull:
3180 self.parent.type = column.type
3182 # super-edgy case, if other FKs point to our column,
3183 # they'd get the type propagated out also.
3185 def set_type(fk: ForeignKey) -> None:
3186 if fk.parent.type._isnull:
3187 fk.parent.type = column.type
3189 self.parent._setup_on_memoized_fks(set_type)
3191 self.column = column # type: ignore
3193 @util.ro_memoized_property
3194 def column(self) -> Column[Any]:
3195 """Return the target :class:`_schema.Column` referenced by this
3196 :class:`_schema.ForeignKey`.
3198 If no target column has been established, an exception
3199 is raised.
3201 """
3202 return self._resolve_column()
3204 @overload
3205 def _resolve_column(
3206 self, *, raiseerr: Literal[True] = ...
3207 ) -> Column[Any]: ...
3209 @overload
3210 def _resolve_column(
3211 self, *, raiseerr: bool = ...
3212 ) -> Optional[Column[Any]]: ...
3214 def _resolve_column(
3215 self, *, raiseerr: bool = True
3216 ) -> Optional[Column[Any]]:
3217 _column: Column[Any]
3219 _colspec, effective_table_column = self._resolve_colspec_argument()
3221 if isinstance(_colspec, str):
3222 parenttable, tablekey, colname = self._resolve_col_tokens()
3224 if self._unresolvable or tablekey not in parenttable.metadata:
3225 if not raiseerr:
3226 return None
3227 raise exc.NoReferencedTableError(
3228 f"Foreign key associated with column "
3229 f"'{self.parent}' could not find "
3230 f"table '{tablekey}' with which to generate a "
3231 f"foreign key to target column '{colname}'",
3232 tablekey,
3233 )
3234 elif parenttable.key not in parenttable.metadata:
3235 if not raiseerr:
3236 return None
3237 raise exc.InvalidRequestError(
3238 f"Table {parenttable} is no longer associated with its "
3239 "parent MetaData"
3240 )
3241 else:
3242 table = parenttable.metadata.tables[tablekey]
3243 return self._link_to_col_by_colstring(
3244 parenttable, table, colname
3245 )
3247 elif hasattr(_colspec, "__clause_element__"):
3248 _column = _colspec.__clause_element__()
3249 return _column
3250 else:
3251 assert isinstance(_colspec, Column)
3252 _column = _colspec
3253 return _column
3255 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
3256 assert isinstance(parent, Column)
3258 if self.parent is not None and self.parent is not parent:
3259 raise exc.InvalidRequestError(
3260 "This ForeignKey already has a parent !"
3261 )
3262 self.parent = parent
3263 self.parent.foreign_keys.add(self)
3264 self.parent._on_table_attach(self._set_table)
3266 def _set_remote_table(self, table: Table) -> None:
3267 parenttable, _, colname = self._resolve_col_tokens()
3268 _column = self._link_to_col_by_colstring(parenttable, table, colname)
3269 self._set_target_column(_column)
3270 assert self.constraint is not None
3271 self.constraint._validate_dest_table(table)
3273 def _remove_from_metadata(self, metadata: MetaData) -> None:
3274 parenttable, table_key, colname = self._resolve_col_tokens()
3275 fk_key = (table_key, colname)
3277 if self in metadata._fk_memos[fk_key]:
3278 # TODO: no test coverage for self not in memos
3279 metadata._fk_memos[fk_key].remove(self)
3281 def _set_table(self, column: Column[Any], table: Table) -> None:
3282 # standalone ForeignKey - create ForeignKeyConstraint
3283 # on the hosting Table when attached to the Table.
3284 assert isinstance(table, Table)
3285 if self.constraint is None:
3286 self.constraint = ForeignKeyConstraint(
3287 [],
3288 [],
3289 use_alter=self.use_alter,
3290 name=self.name,
3291 onupdate=self.onupdate,
3292 ondelete=self.ondelete,
3293 deferrable=self.deferrable,
3294 initially=self.initially,
3295 match=self.match,
3296 comment=self.comment,
3297 **self._unvalidated_dialect_kw,
3298 )
3299 self.constraint._append_element(column, self)
3300 self.constraint._set_parent_with_dispatch(table)
3301 table.foreign_keys.add(self)
3302 # set up remote ".column" attribute, or a note to pick it
3303 # up when the other Table/Column shows up
3305 _colspec, _ = self._resolve_colspec_argument()
3306 if isinstance(_colspec, str):
3307 parenttable, table_key, colname = self._resolve_col_tokens()
3308 fk_key = (table_key, colname)
3309 if table_key in parenttable.metadata.tables:
3310 table = parenttable.metadata.tables[table_key]
3311 try:
3312 _column = self._link_to_col_by_colstring(
3313 parenttable, table, colname
3314 )
3315 except exc.NoReferencedColumnError:
3316 # this is OK, we'll try later
3317 pass
3318 else:
3319 self._set_target_column(_column)
3321 parenttable.metadata._fk_memos[fk_key].append(self)
3322 elif hasattr(_colspec, "__clause_element__"):
3323 _column = _colspec.__clause_element__()
3324 self._set_target_column(_column)
3325 else:
3326 self._set_target_column(_colspec)
3329if TYPE_CHECKING:
3331 def default_is_sequence(
3332 obj: Optional[DefaultGenerator],
3333 ) -> TypeGuard[Sequence]: ...
3335 def default_is_clause_element(
3336 obj: Optional[DefaultGenerator],
3337 ) -> TypeGuard[ColumnElementColumnDefault]: ...
3339 def default_is_scalar(
3340 obj: Optional[DefaultGenerator],
3341 ) -> TypeGuard[ScalarElementColumnDefault]: ...
3343else:
3344 default_is_sequence = operator.attrgetter("is_sequence")
3346 default_is_clause_element = operator.attrgetter("is_clause_element")
3348 default_is_scalar = operator.attrgetter("is_scalar")
3351class DefaultGenerator(Executable, SchemaItem):
3352 """Base class for column *default* values.
3354 This object is only present on column.default or column.onupdate.
3355 It's not valid as a server default.
3357 """
3359 __visit_name__ = "default_generator"
3361 _is_default_generator = True
3362 is_sequence = False
3363 is_identity = False
3364 is_server_default = False
3365 is_clause_element = False
3366 is_callable = False
3367 is_scalar = False
3368 has_arg = False
3369 is_sentinel = False
3370 column: Optional[Column[Any]]
3372 def __init__(self, for_update: bool = False) -> None:
3373 self.for_update = for_update
3375 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
3376 if TYPE_CHECKING:
3377 assert isinstance(parent, Column)
3378 self.column = parent
3379 if self.for_update:
3380 self.column.onupdate = self
3381 else:
3382 self.column.default = self
3384 def _copy(self) -> DefaultGenerator:
3385 raise NotImplementedError()
3387 def _execute_on_connection(
3388 self,
3389 connection: Connection,
3390 distilled_params: _CoreMultiExecuteParams,
3391 execution_options: CoreExecuteOptionsParameter,
3392 ) -> Any:
3393 util.warn_deprecated(
3394 "Using the .execute() method to invoke a "
3395 "DefaultGenerator object is deprecated; please use "
3396 "the .scalar() method.",
3397 "2.0",
3398 )
3399 return self._execute_on_scalar(
3400 connection, distilled_params, execution_options
3401 )
3403 def _execute_on_scalar(
3404 self,
3405 connection: Connection,
3406 distilled_params: _CoreMultiExecuteParams,
3407 execution_options: CoreExecuteOptionsParameter,
3408 ) -> Any:
3409 return connection._execute_default(
3410 self, distilled_params, execution_options
3411 )
3414class ColumnDefault(DefaultGenerator, ABC):
3415 """A plain default value on a column.
3417 This could correspond to a constant, a callable function,
3418 or a SQL clause.
3420 :class:`.ColumnDefault` is generated automatically
3421 whenever the ``default``, ``onupdate`` arguments of
3422 :class:`_schema.Column` are used. A :class:`.ColumnDefault`
3423 can be passed positionally as well.
3425 For example, the following::
3427 Column('foo', Integer, default=50)
3429 Is equivalent to::
3431 Column('foo', Integer, ColumnDefault(50))
3434 """
3436 arg: Any
3438 @overload
3439 def __new__(
3440 cls, arg: Callable[..., Any], for_update: bool = ...
3441 ) -> CallableColumnDefault: ...
3443 @overload
3444 def __new__(
3445 cls, arg: ColumnElement[Any], for_update: bool = ...
3446 ) -> ColumnElementColumnDefault: ...
3448 # if I return ScalarElementColumnDefault here, which is what's actually
3449 # returned, mypy complains that
3450 # overloads overlap w/ incompatible return types.
3451 @overload
3452 def __new__(cls, arg: object, for_update: bool = ...) -> ColumnDefault: ...
3454 def __new__(
3455 cls, arg: Any = None, for_update: bool = False
3456 ) -> ColumnDefault:
3457 """Construct a new :class:`.ColumnDefault`.
3460 :param arg: argument representing the default value.
3461 May be one of the following:
3463 * a plain non-callable Python value, such as a
3464 string, integer, boolean, or other simple type.
3465 The default value will be used as is each time.
3466 * a SQL expression, that is one which derives from
3467 :class:`_expression.ColumnElement`. The SQL expression will
3468 be rendered into the INSERT or UPDATE statement,
3469 or in the case of a primary key column when
3470 RETURNING is not used may be
3471 pre-executed before an INSERT within a SELECT.
3472 * A Python callable. The function will be invoked for each
3473 new row subject to an INSERT or UPDATE.
3474 The callable must accept exactly
3475 zero or one positional arguments. The one-argument form
3476 will receive an instance of the :class:`.ExecutionContext`,
3477 which provides contextual information as to the current
3478 :class:`_engine.Connection` in use as well as the current
3479 statement and parameters.
3481 """
3483 if isinstance(arg, FetchedValue):
3484 raise exc.ArgumentError(
3485 "ColumnDefault may not be a server-side default type."
3486 )
3487 elif callable(arg):
3488 cls = CallableColumnDefault
3489 elif isinstance(arg, ClauseElement):
3490 cls = ColumnElementColumnDefault
3491 elif arg is not None:
3492 cls = ScalarElementColumnDefault
3494 return object.__new__(cls)
3496 def __repr__(self) -> str:
3497 return f"{self.__class__.__name__}({self.arg!r})"
3500class ScalarElementColumnDefault(ColumnDefault):
3501 """default generator for a fixed scalar Python value
3503 .. versionadded: 2.0
3505 """
3507 is_scalar = True
3508 has_arg = True
3510 def __init__(self, arg: Any, for_update: bool = False) -> None:
3511 self.for_update = for_update
3512 self.arg = arg
3514 def _copy(self) -> ScalarElementColumnDefault:
3515 return ScalarElementColumnDefault(
3516 arg=self.arg, for_update=self.for_update
3517 )
3520class _InsertSentinelColumnDefault(ColumnDefault):
3521 """Default generator that's specific to the use of a "sentinel" column
3522 when using the insertmanyvalues feature.
3524 This default is used as part of the :func:`_schema.insert_sentinel`
3525 construct.
3527 """
3529 is_sentinel = True
3530 for_update = False
3531 arg = None
3533 def __new__(cls) -> _InsertSentinelColumnDefault:
3534 return object.__new__(cls)
3536 def __init__(self) -> None:
3537 pass
3539 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
3540 col = cast("Column[Any]", parent)
3541 if not col._insert_sentinel:
3542 raise exc.ArgumentError(
3543 "The _InsertSentinelColumnDefault may only be applied to a "
3544 "Column marked as insert_sentinel=True"
3545 )
3546 elif not col.nullable:
3547 raise exc.ArgumentError(
3548 "The _InsertSentinelColumnDefault may only be applied to a "
3549 "Column that is nullable"
3550 )
3552 super()._set_parent(parent, **kw)
3554 def _copy(self) -> _InsertSentinelColumnDefault:
3555 return _InsertSentinelColumnDefault()
3558_SQLExprDefault = Union["ColumnElement[Any]", "TextClause"]
3561class ColumnElementColumnDefault(ColumnDefault):
3562 """default generator for a SQL expression
3564 .. versionadded:: 2.0
3566 """
3568 is_clause_element = True
3569 has_arg = True
3570 arg: _SQLExprDefault
3572 def __init__(
3573 self,
3574 arg: _SQLExprDefault,
3575 for_update: bool = False,
3576 ) -> None:
3577 self.for_update = for_update
3578 self.arg = arg
3580 def _copy(self) -> ColumnElementColumnDefault:
3581 return ColumnElementColumnDefault(
3582 arg=self.arg, for_update=self.for_update
3583 )
3585 @util.memoized_property
3586 @util.preload_module("sqlalchemy.sql.sqltypes")
3587 def _arg_is_typed(self) -> bool:
3588 sqltypes = util.preloaded.sql_sqltypes
3590 return not isinstance(self.arg.type, sqltypes.NullType)
3593class _CallableColumnDefaultProtocol(Protocol):
3594 def __call__(self, context: ExecutionContext) -> Any: ...
3597class CallableColumnDefault(ColumnDefault):
3598 """default generator for a callable Python function
3600 .. versionadded:: 2.0
3602 """
3604 is_callable = True
3605 arg: _CallableColumnDefaultProtocol
3606 has_arg = True
3608 def __init__(
3609 self,
3610 arg: Union[_CallableColumnDefaultProtocol, Callable[[], Any]],
3611 for_update: bool = False,
3612 ) -> None:
3613 self.for_update = for_update
3614 self.arg = self._maybe_wrap_callable(arg)
3616 def _copy(self) -> CallableColumnDefault:
3617 return CallableColumnDefault(arg=self.arg, for_update=self.for_update)
3619 def _maybe_wrap_callable(
3620 self, fn: Union[_CallableColumnDefaultProtocol, Callable[[], Any]]
3621 ) -> _CallableColumnDefaultProtocol:
3622 """Wrap callables that don't accept a context.
3624 This is to allow easy compatibility with default callables
3625 that aren't specific to accepting of a context.
3627 """
3629 try:
3630 argspec = util.get_callable_argspec(fn, no_self=True)
3631 except TypeError:
3632 return util.wrap_callable(lambda ctx: fn(), fn) # type: ignore
3634 defaulted = argspec[3] is not None and len(argspec[3]) or 0
3635 positionals = len(argspec[0]) - defaulted
3637 if positionals == 0:
3638 return util.wrap_callable(lambda ctx: fn(), fn) # type: ignore
3640 elif positionals == 1:
3641 return fn # type: ignore
3642 else:
3643 raise exc.ArgumentError(
3644 "ColumnDefault Python function takes zero or one "
3645 "positional arguments"
3646 )
3649class IdentityOptions(DialectKWArgs):
3650 """Defines options for a named database sequence or an identity column.
3652 .. versionadded:: 1.3.18
3654 .. seealso::
3656 :class:`.Sequence`
3658 """
3660 def __init__(
3661 self,
3662 start: Optional[int] = None,
3663 increment: Optional[int] = None,
3664 minvalue: Optional[int] = None,
3665 maxvalue: Optional[int] = None,
3666 nominvalue: Optional[bool] = None,
3667 nomaxvalue: Optional[bool] = None,
3668 cycle: Optional[bool] = None,
3669 cache: Optional[int] = None,
3670 order: Optional[bool] = None,
3671 **dialect_kw: Any,
3672 ) -> None:
3673 """Construct a :class:`.IdentityOptions` object.
3675 See the :class:`.Sequence` documentation for a complete description
3676 of the parameters.
3678 :param start: the starting index of the sequence.
3679 :param increment: the increment value of the sequence.
3680 :param minvalue: the minimum value of the sequence.
3681 :param maxvalue: the maximum value of the sequence.
3682 :param nominvalue: no minimum value of the sequence.
3683 :param nomaxvalue: no maximum value of the sequence.
3684 :param cycle: allows the sequence to wrap around when the maxvalue
3685 or minvalue has been reached.
3686 :param cache: optional integer value; number of future values in the
3687 sequence which are calculated in advance.
3688 :param order: optional boolean value; if ``True``, renders the
3689 ORDER keyword.
3691 .. deprecated:: 2.1 Use ``oracle_order`` instead.
3693 """
3694 self.start = start
3695 self.increment = increment
3696 self.minvalue = minvalue
3697 self.maxvalue = maxvalue
3698 self.nominvalue = nominvalue
3699 self.nomaxvalue = nomaxvalue
3700 self.cycle = cycle
3701 self.cache = cache
3702 if order is not None:
3703 if "oracle_order" in dialect_kw:
3704 raise exc.ArgumentError(
3705 "Cannot specify both 'order' and 'oracle_order'. "
3706 "Plese use only 'oracle_order'."
3707 )
3708 dialect_kw["oracle_order"] = order
3709 self._validate_dialect_kwargs(dialect_kw)
3711 @property
3712 def _increment_is_negative(self) -> bool:
3713 return self.increment is not None and self.increment < 0
3715 @property
3716 def order(self) -> Optional[bool]:
3717 """Alias of the ``dialect_kwargs`` ``'oracle_order'``.
3719 .. deprecated:: 2.1 The 'order' attribute is deprecated.
3720 """
3721 value: Optional[bool] = self.dialect_kwargs.get("oracle_order")
3722 return value
3724 def _as_dict(self) -> Dict[str, Any]:
3725 return {
3726 k: v
3727 for k, v in {
3728 "start": self.start,
3729 "increment": self.increment,
3730 "minvalue": self.minvalue,
3731 "maxvalue": self.maxvalue,
3732 "nominvalue": self.nominvalue,
3733 "nomaxvalue": self.nomaxvalue,
3734 "cycle": self.cycle,
3735 "cache": self.cache,
3736 }.items()
3737 if v != None
3738 }
3741class Sequence(HasSchemaAttr, IdentityOptions, DefaultGenerator):
3742 """Represents a named database sequence.
3744 The :class:`.Sequence` object represents the name and configurational
3745 parameters of a database sequence. It also represents
3746 a construct that can be "executed" by a SQLAlchemy :class:`_engine.Engine`
3747 or :class:`_engine.Connection`,
3748 rendering the appropriate "next value" function
3749 for the target database and returning a result.
3751 The :class:`.Sequence` is typically associated with a primary key column::
3753 some_table = Table(
3754 'some_table', metadata,
3755 Column('id', Integer, Sequence('some_table_seq', start=1),
3756 primary_key=True)
3757 )
3759 When CREATE TABLE is emitted for the above :class:`_schema.Table`, if the
3760 target platform supports sequences, a CREATE SEQUENCE statement will
3761 be emitted as well. For platforms that don't support sequences,
3762 the :class:`.Sequence` construct is ignored.
3764 .. seealso::
3766 :ref:`defaults_sequences`
3768 :class:`.CreateSequence`
3770 :class:`.DropSequence`
3772 """
3774 __visit_name__ = "sequence"
3776 is_sequence = True
3778 column: Optional[Column[Any]]
3779 data_type: Optional[TypeEngine[int]]
3781 @util.deprecated_params(
3782 order=(
3783 "2.1",
3784 "This parameter is supported only by Oracle, "
3785 "use ``oracle_order`` instead.",
3786 )
3787 )
3788 def __init__(
3789 self,
3790 name: str,
3791 start: Optional[int] = None,
3792 increment: Optional[int] = None,
3793 minvalue: Optional[int] = None,
3794 maxvalue: Optional[int] = None,
3795 nominvalue: Optional[bool] = None,
3796 nomaxvalue: Optional[bool] = None,
3797 cycle: Optional[bool] = None,
3798 schema: Optional[Union[str, Literal[SchemaConst.BLANK_SCHEMA]]] = None,
3799 cache: Optional[int] = None,
3800 order: Optional[bool] = None,
3801 data_type: Optional[_TypeEngineArgument[int]] = None,
3802 optional: bool = False,
3803 quote: Optional[bool] = None,
3804 metadata: Optional[MetaData] = None,
3805 quote_schema: Optional[bool] = None,
3806 for_update: bool = False,
3807 **dialect_kw: Any,
3808 ) -> None:
3809 """Construct a :class:`.Sequence` object.
3811 :param name: the name of the sequence.
3813 :param start: the starting index of the sequence. This value is
3814 used when the CREATE SEQUENCE command is emitted to the database
3815 as the value of the "START WITH" clause. If ``None``, the
3816 clause is omitted, which on most platforms indicates a starting
3817 value of 1.
3819 .. versionchanged:: 2.0 The :paramref:`.Sequence.start` parameter
3820 is required in order to have DDL emit "START WITH". This is a
3821 reversal of a change made in version 1.4 which would implicitly
3822 render "START WITH 1" if the :paramref:`.Sequence.start` were
3823 not included. See :ref:`change_7211` for more detail.
3825 :param increment: the increment 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 "INCREMENT BY" clause. If ``None``,
3828 the clause is omitted, which on most platforms indicates an
3829 increment of 1.
3830 :param minvalue: the minimum value of the sequence. This
3831 value is used when the CREATE SEQUENCE command is emitted to
3832 the database as the value of the "MINVALUE" clause. If ``None``,
3833 the clause is omitted, which on most platforms indicates a
3834 minvalue of 1 and -2^63-1 for ascending and descending sequences,
3835 respectively.
3837 :param maxvalue: the maximum value of the sequence. This
3838 value is used when the CREATE SEQUENCE command is emitted to
3839 the database as the value of the "MAXVALUE" clause. If ``None``,
3840 the clause is omitted, which on most platforms indicates a
3841 maxvalue of 2^63-1 and -1 for ascending and descending sequences,
3842 respectively.
3844 :param nominvalue: no minimum value of the sequence. This
3845 value is used when the CREATE SEQUENCE command is emitted to
3846 the database as the value of the "NO MINVALUE" clause. If ``None``,
3847 the clause is omitted, which on most platforms indicates a
3848 minvalue of 1 and -2^63-1 for ascending and descending sequences,
3849 respectively.
3851 :param nomaxvalue: no maximum value of the sequence. This
3852 value is used when the CREATE SEQUENCE command is emitted to
3853 the database as the value of the "NO MAXVALUE" clause. If ``None``,
3854 the clause is omitted, which on most platforms indicates a
3855 maxvalue of 2^63-1 and -1 for ascending and descending sequences,
3856 respectively.
3858 :param cycle: allows the sequence to wrap around when the maxvalue
3859 or minvalue has been reached by an ascending or descending sequence
3860 respectively. This value is used when the CREATE SEQUENCE command
3861 is emitted to the database as the "CYCLE" clause. If the limit is
3862 reached, the next number generated will be the minvalue or maxvalue,
3863 respectively. If cycle=False (the default) any calls to nextval
3864 after the sequence has reached its maximum value will return an
3865 error.
3867 :param schema: optional schema name for the sequence, if located
3868 in a schema other than the default. The rules for selecting the
3869 schema name when a :class:`_schema.MetaData`
3870 is also present are the same
3871 as that of :paramref:`_schema.Table.schema`.
3873 :param cache: optional integer value; number of future values in the
3874 sequence which are calculated in advance. Renders the CACHE keyword
3875 understood by Oracle and PostgreSQL.
3877 :param order: optional boolean value; if ``True``, renders the
3878 ORDER keyword, understood by Oracle, indicating the sequence is
3879 definitively ordered. May be necessary to provide deterministic
3880 ordering using Oracle RAC.
3882 :param data_type: The type to be returned by the sequence, for
3883 dialects that allow us to choose between INTEGER, BIGINT, etc.
3884 (e.g., mssql).
3886 .. versionadded:: 1.4.0
3888 :param optional: boolean value, when ``True``, indicates that this
3889 :class:`.Sequence` object only needs to be explicitly generated
3890 on backends that don't provide another way to generate primary
3891 key identifiers. Currently, it essentially means, "don't create
3892 this sequence on the PostgreSQL backend, where the SERIAL keyword
3893 creates a sequence for us automatically".
3894 :param quote: boolean value, when ``True`` or ``False``, explicitly
3895 forces quoting of the :paramref:`_schema.Sequence.name` on or off.
3896 When left at its default of ``None``, normal quoting rules based
3897 on casing and reserved words take place.
3898 :param quote_schema: Set the quoting preferences for the ``schema``
3899 name.
3901 :param metadata: optional :class:`_schema.MetaData` object which this
3902 :class:`.Sequence` will be associated with. A :class:`.Sequence`
3903 that is associated with a :class:`_schema.MetaData`
3904 gains the following
3905 capabilities:
3907 * The :class:`.Sequence` will inherit the
3908 :paramref:`_schema.MetaData.schema`
3909 parameter specified to the target :class:`_schema.MetaData`, which
3910 affects the production of CREATE / DROP DDL, if any.
3912 * The :meth:`.Sequence.create` and :meth:`.Sequence.drop` methods
3913 automatically use the engine bound to the :class:`_schema.MetaData`
3914 object, if any.
3916 * The :meth:`_schema.MetaData.create_all` and
3917 :meth:`_schema.MetaData.drop_all`
3918 methods will emit CREATE / DROP for this :class:`.Sequence`,
3919 even if the :class:`.Sequence` is not associated with any
3920 :class:`_schema.Table` / :class:`_schema.Column`
3921 that's a member of this
3922 :class:`_schema.MetaData`.
3924 The above behaviors can only occur if the :class:`.Sequence` is
3925 explicitly associated with the :class:`_schema.MetaData`
3926 via this parameter.
3928 .. seealso::
3930 :ref:`sequence_metadata` - full discussion of the
3931 :paramref:`.Sequence.metadata` parameter.
3933 :param for_update: Indicates this :class:`.Sequence`, when associated
3934 with a :class:`_schema.Column`,
3935 should be invoked for UPDATE statements
3936 on that column's table, rather than for INSERT statements, when
3937 no value is otherwise present for that column in the statement.
3939 """
3940 DefaultGenerator.__init__(self, for_update=for_update)
3941 IdentityOptions.__init__(
3942 self,
3943 start=start,
3944 increment=increment,
3945 minvalue=minvalue,
3946 maxvalue=maxvalue,
3947 nominvalue=nominvalue,
3948 nomaxvalue=nomaxvalue,
3949 cycle=cycle,
3950 cache=cache,
3951 order=order,
3952 **dialect_kw,
3953 )
3954 self.column = None
3955 self.name = quoted_name(name, quote)
3956 self.optional = optional
3957 if schema is BLANK_SCHEMA:
3958 self.schema = schema = None
3959 elif metadata is not None and schema is None and metadata.schema:
3960 self.schema = schema = metadata.schema
3961 else:
3962 self.schema = quoted_name.construct(schema, quote_schema)
3963 self.metadata = metadata
3964 self._key = _get_table_key(name, schema)
3965 if metadata:
3966 self._set_metadata(metadata)
3967 if data_type is not None:
3968 self.data_type = to_instance(data_type)
3969 else:
3970 self.data_type = None
3972 @util.preload_module("sqlalchemy.sql.functions")
3973 def next_value(self) -> Function[int]:
3974 """Return a :class:`.next_value` function element
3975 which will render the appropriate increment function
3976 for this :class:`.Sequence` within any SQL expression.
3978 """
3979 return util.preloaded.sql_functions.func.next_value(self)
3981 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
3982 column = parent
3983 assert isinstance(column, Column)
3984 super()._set_parent(column)
3985 column._on_table_attach(self._set_table)
3987 def _copy(self) -> Sequence:
3988 return Sequence(
3989 name=self.name,
3990 schema=self.schema,
3991 data_type=self.data_type,
3992 optional=self.optional,
3993 metadata=self.metadata,
3994 for_update=self.for_update,
3995 **self._as_dict(),
3996 **self.dialect_kwargs,
3997 )
3999 def _set_table(self, column: Column[Any], table: Table) -> None:
4000 self._set_metadata(table.metadata)
4002 def _set_metadata(self, metadata: MetaData) -> None:
4003 self.metadata = metadata
4004 self.metadata._sequences[self._key] = self
4006 def create(self, bind: _CreateDropBind, checkfirst: bool = True) -> None:
4007 """Creates this sequence in the database."""
4009 bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
4011 def drop(self, bind: _CreateDropBind, checkfirst: bool = True) -> None:
4012 """Drops this sequence from the database."""
4014 bind._run_ddl_visitor(ddl.SchemaDropper, self, checkfirst=checkfirst)
4016 def _not_a_column_expr(self) -> NoReturn:
4017 raise exc.InvalidRequestError(
4018 f"This {self.__class__.__name__} cannot be used directly "
4019 "as a column expression. Use func.next_value(sequence) "
4020 "to produce a 'next value' function that's usable "
4021 "as a column element."
4022 )
4025@inspection._self_inspects
4026class FetchedValue(SchemaEventTarget):
4027 """A marker for a transparent database-side default.
4029 Use :class:`.FetchedValue` when the database is configured
4030 to provide some automatic default for a column.
4032 E.g.::
4034 Column('foo', Integer, FetchedValue())
4036 Would indicate that some trigger or default generator
4037 will create a new value for the ``foo`` column during an
4038 INSERT.
4040 .. seealso::
4042 :ref:`triggered_columns`
4044 """
4046 is_server_default = True
4047 reflected = False
4048 has_argument = False
4049 is_clause_element = False
4050 is_identity = False
4052 column: Optional[Column[Any]]
4054 def __init__(self, for_update: bool = False) -> None:
4055 self.for_update = for_update
4057 def _as_for_update(self, for_update: bool) -> FetchedValue:
4058 if for_update == self.for_update:
4059 return self
4060 else:
4061 return self._clone(for_update)
4063 def _copy(self) -> FetchedValue:
4064 return FetchedValue(self.for_update)
4066 def _clone(self, for_update: bool) -> Self:
4067 n = self.__class__.__new__(self.__class__)
4068 n.__dict__.update(self.__dict__)
4069 n.__dict__.pop("column", None)
4070 n.for_update = for_update
4071 return n
4073 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4074 column = parent
4075 assert isinstance(column, Column)
4076 self.column = column
4077 if self.for_update:
4078 self.column.server_onupdate = self
4079 else:
4080 self.column.server_default = self
4082 def __repr__(self) -> str:
4083 return util.generic_repr(self)
4086class DefaultClause(FetchedValue):
4087 """A DDL-specified DEFAULT column value.
4089 :class:`.DefaultClause` is a :class:`.FetchedValue`
4090 that also generates a "DEFAULT" clause when
4091 "CREATE TABLE" is emitted.
4093 :class:`.DefaultClause` is generated automatically
4094 whenever the ``server_default``, ``server_onupdate`` arguments of
4095 :class:`_schema.Column` are used. A :class:`.DefaultClause`
4096 can be passed positionally as well.
4098 For example, the following::
4100 Column('foo', Integer, server_default="50")
4102 Is equivalent to::
4104 Column('foo', Integer, DefaultClause("50"))
4106 """
4108 has_argument = True
4110 def __init__(
4111 self,
4112 arg: Union[str, ClauseElement, TextClause],
4113 for_update: bool = False,
4114 _reflected: bool = False,
4115 ) -> None:
4116 util.assert_arg_type(arg, (str, ClauseElement, TextClause), "arg")
4117 super().__init__(for_update)
4118 self.arg = arg
4119 self.reflected = _reflected
4121 def _copy(self) -> DefaultClause:
4122 return DefaultClause(
4123 arg=self.arg, for_update=self.for_update, _reflected=self.reflected
4124 )
4126 def __repr__(self) -> str:
4127 return "DefaultClause(%r, for_update=%r)" % (self.arg, self.for_update)
4130class Constraint(DialectKWArgs, HasConditionalDDL, SchemaItem):
4131 """A table-level SQL constraint.
4133 :class:`_schema.Constraint` serves as the base class for the series of
4134 constraint objects that can be associated with :class:`_schema.Table`
4135 objects, including :class:`_schema.PrimaryKeyConstraint`,
4136 :class:`_schema.ForeignKeyConstraint`
4137 :class:`_schema.UniqueConstraint`, and
4138 :class:`_schema.CheckConstraint`.
4140 """
4142 __visit_name__ = "constraint"
4144 _creation_order: int
4145 _column_flag: bool
4147 def __init__(
4148 self,
4149 name: _ConstraintNameArgument = None,
4150 deferrable: Optional[bool] = None,
4151 initially: Optional[str] = None,
4152 info: Optional[_InfoType] = None,
4153 comment: Optional[str] = None,
4154 _create_rule: Optional[Any] = None,
4155 _type_bound: bool = False,
4156 **dialect_kw: Any,
4157 ) -> None:
4158 r"""Create a SQL constraint.
4160 :param name:
4161 Optional, the in-database name of this ``Constraint``.
4163 :param deferrable:
4164 Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
4165 issuing DDL for this constraint.
4167 :param initially:
4168 Optional string. If set, emit INITIALLY <value> when issuing DDL
4169 for this constraint.
4171 :param info: Optional data dictionary which will be populated into the
4172 :attr:`.SchemaItem.info` attribute of this object.
4174 :param comment: Optional string that will render an SQL comment on
4175 foreign key constraint creation.
4177 .. versionadded:: 2.0
4179 :param \**dialect_kw: Additional keyword arguments are dialect
4180 specific, and passed in the form ``<dialectname>_<argname>``. See
4181 the documentation regarding an individual dialect at
4182 :ref:`dialect_toplevel` for detail on documented arguments.
4184 :param _create_rule:
4185 used internally by some datatypes that also create constraints.
4187 :param _type_bound:
4188 used internally to indicate that this constraint is associated with
4189 a specific datatype.
4191 """
4193 self.name = name
4194 self.deferrable = deferrable
4195 self.initially = initially
4196 if info:
4197 self.info = info
4198 self._create_rule = _create_rule
4199 self._type_bound = _type_bound
4200 util.set_creation_order(self)
4201 self._validate_dialect_kwargs(dialect_kw)
4202 self.comment = comment
4204 def _should_create_for_compiler(
4205 self, compiler: DDLCompiler, **kw: Any
4206 ) -> bool:
4207 if self._create_rule is not None and not self._create_rule(compiler):
4208 return False
4209 elif self._ddl_if is not None:
4210 return self._ddl_if._should_execute(
4211 ddl.CreateConstraint(self), self, None, compiler=compiler, **kw
4212 )
4213 else:
4214 return True
4216 @property
4217 def table(self) -> Table:
4218 try:
4219 if isinstance(self.parent, Table):
4220 return self.parent
4221 except AttributeError:
4222 pass
4223 raise exc.InvalidRequestError(
4224 "This constraint is not bound to a table. Did you "
4225 "mean to call table.append_constraint(constraint) ?"
4226 )
4228 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4229 assert isinstance(parent, (Table, Column))
4230 self.parent = parent
4231 parent.constraints.add(self)
4233 @util.deprecated(
4234 "1.4",
4235 "The :meth:`_schema.Constraint.copy` method is deprecated "
4236 "and will be removed in a future release.",
4237 )
4238 def copy(self, **kw: Any) -> Self:
4239 return self._copy(**kw)
4241 def _copy(self, **kw: Any) -> Self:
4242 raise NotImplementedError()
4245class ColumnCollectionMixin:
4246 """A :class:`_expression.ColumnCollection` of :class:`_schema.Column`
4247 objects.
4249 This collection represents the columns which are referred to by
4250 this object.
4252 """
4254 _columns: DedupeColumnCollection[Column[Any]]
4256 _allow_multiple_tables = False
4258 _pending_colargs: List[Optional[Union[str, Column[Any]]]]
4260 if TYPE_CHECKING:
4262 def _set_parent_with_dispatch(
4263 self, parent: SchemaEventTarget, **kw: Any
4264 ) -> None: ...
4266 def __init__(
4267 self,
4268 *columns: _DDLColumnArgument,
4269 _autoattach: bool = True,
4270 _column_flag: bool = False,
4271 _gather_expressions: Optional[
4272 List[Union[str, ColumnElement[Any]]]
4273 ] = None,
4274 ) -> None:
4275 self._column_flag = _column_flag
4276 self._columns = DedupeColumnCollection()
4278 processed_expressions: Optional[
4279 List[Union[ColumnElement[Any], str]]
4280 ] = _gather_expressions
4282 if processed_expressions is not None:
4283 self._pending_colargs = []
4284 for (
4285 expr,
4286 _,
4287 _,
4288 add_element,
4289 ) in coercions.expect_col_expression_collection(
4290 roles.DDLConstraintColumnRole, columns
4291 ):
4292 self._pending_colargs.append(add_element)
4293 processed_expressions.append(expr)
4294 else:
4295 self._pending_colargs = [
4296 coercions.expect(roles.DDLConstraintColumnRole, column)
4297 for column in columns
4298 ]
4300 if _autoattach and self._pending_colargs:
4301 self._check_attach()
4303 def _check_attach(self, evt: bool = False) -> None:
4304 col_objs = [c for c in self._pending_colargs if isinstance(c, Column)]
4306 cols_w_table = [c for c in col_objs if isinstance(c.table, Table)]
4308 cols_wo_table = set(col_objs).difference(cols_w_table)
4309 if cols_wo_table:
4310 # feature #3341 - place event listeners for Column objects
4311 # such that when all those cols are attached, we autoattach.
4312 assert not evt, "Should not reach here on event call"
4314 # issue #3411 - don't do the per-column auto-attach if some of the
4315 # columns are specified as strings.
4316 has_string_cols = {
4317 c for c in self._pending_colargs if c is not None
4318 }.difference(col_objs)
4319 if not has_string_cols:
4321 def _col_attached(column: Column[Any], table: Table) -> None:
4322 # this isinstance() corresponds with the
4323 # isinstance() above; only want to count Table-bound
4324 # columns
4325 if isinstance(table, Table):
4326 cols_wo_table.discard(column)
4327 if not cols_wo_table:
4328 self._check_attach(evt=True)
4330 self._cols_wo_table = cols_wo_table
4331 for col in cols_wo_table:
4332 col._on_table_attach(_col_attached)
4333 return
4335 columns = cols_w_table
4337 tables = {c.table for c in columns}
4338 if len(tables) == 1:
4339 self._set_parent_with_dispatch(tables.pop())
4340 elif len(tables) > 1 and not self._allow_multiple_tables:
4341 table = columns[0].table
4342 others = [c for c in columns[1:] if c.table is not table]
4343 if others:
4344 # black could not format this inline
4345 other_str = ", ".join("'%s'" % c for c in others)
4346 raise exc.ArgumentError(
4347 f"Column(s) {other_str} "
4348 f"are not part of table '{table.description}'."
4349 )
4351 @util.ro_memoized_property
4352 def columns(self) -> ReadOnlyColumnCollection[str, Column[Any]]:
4353 return self._columns.as_readonly()
4355 @util.ro_memoized_property
4356 def c(self) -> ReadOnlyColumnCollection[str, Column[Any]]:
4357 return self._columns.as_readonly()
4359 def _col_expressions(
4360 self, parent: Union[Table, Column[Any]]
4361 ) -> List[Optional[Column[Any]]]:
4362 if isinstance(parent, Column):
4363 result: List[Optional[Column[Any]]] = [
4364 c for c in self._pending_colargs if isinstance(c, Column)
4365 ]
4366 assert len(result) == len(self._pending_colargs)
4367 return result
4368 else:
4369 try:
4370 return [
4371 parent.c[col] if isinstance(col, str) else col
4372 for col in self._pending_colargs
4373 ]
4374 except KeyError as ke:
4375 raise exc.ConstraintColumnNotFoundError(
4376 f"Can't create {self.__class__.__name__} "
4377 f"on table '{parent.description}': no column "
4378 f"named '{ke.args[0]}' is present."
4379 ) from ke
4381 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4382 assert isinstance(parent, (Table, Column))
4384 for col in self._col_expressions(parent):
4385 if col is not None:
4386 self._columns.add(col)
4389class ColumnCollectionConstraint(ColumnCollectionMixin, Constraint):
4390 """A constraint that proxies a ColumnCollection."""
4392 def __init__(
4393 self,
4394 *columns: _DDLColumnArgument,
4395 name: _ConstraintNameArgument = None,
4396 deferrable: Optional[bool] = None,
4397 initially: Optional[str] = None,
4398 info: Optional[_InfoType] = None,
4399 _autoattach: bool = True,
4400 _column_flag: bool = False,
4401 _gather_expressions: Optional[List[_DDLColumnArgument]] = None,
4402 **dialect_kw: Any,
4403 ) -> None:
4404 r"""
4405 :param \*columns:
4406 A sequence of column names or Column objects.
4408 :param name:
4409 Optional, the in-database name of this constraint.
4411 :param deferrable:
4412 Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
4413 issuing DDL for this constraint.
4415 :param initially:
4416 Optional string. If set, emit INITIALLY <value> when issuing DDL
4417 for this constraint.
4419 :param \**dialect_kw: other keyword arguments including
4420 dialect-specific arguments are propagated to the :class:`.Constraint`
4421 superclass.
4423 """
4424 Constraint.__init__(
4425 self,
4426 name=name,
4427 deferrable=deferrable,
4428 initially=initially,
4429 info=info,
4430 **dialect_kw,
4431 )
4432 ColumnCollectionMixin.__init__(
4433 self, *columns, _autoattach=_autoattach, _column_flag=_column_flag
4434 )
4436 columns: ReadOnlyColumnCollection[str, Column[Any]]
4437 """A :class:`_expression.ColumnCollection` representing the set of columns
4438 for this constraint.
4440 """
4442 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4443 assert isinstance(parent, (Column, Table))
4444 Constraint._set_parent(self, parent)
4445 ColumnCollectionMixin._set_parent(self, parent)
4447 def __contains__(self, x: Any) -> bool:
4448 return x in self._columns
4450 @util.deprecated(
4451 "1.4",
4452 "The :meth:`_schema.ColumnCollectionConstraint.copy` method "
4453 "is deprecated and will be removed in a future release.",
4454 )
4455 def copy(
4456 self,
4457 *,
4458 target_table: Optional[Table] = None,
4459 **kw: Any,
4460 ) -> ColumnCollectionConstraint:
4461 return self._copy(target_table=target_table, **kw)
4463 def _copy(
4464 self,
4465 *,
4466 target_table: Optional[Table] = None,
4467 **kw: Any,
4468 ) -> ColumnCollectionConstraint:
4469 # ticket #5276
4470 constraint_kwargs = {}
4471 for dialect_name in self.dialect_options:
4472 dialect_options = self.dialect_options[dialect_name]._non_defaults
4473 for (
4474 dialect_option_key,
4475 dialect_option_value,
4476 ) in dialect_options.items():
4477 constraint_kwargs[dialect_name + "_" + dialect_option_key] = (
4478 dialect_option_value
4479 )
4481 assert isinstance(self.parent, Table)
4482 c = self.__class__(
4483 name=self.name,
4484 deferrable=self.deferrable,
4485 initially=self.initially,
4486 *[
4487 _copy_expression(expr, self.parent, target_table)
4488 for expr in self._columns
4489 ],
4490 comment=self.comment,
4491 **constraint_kwargs,
4492 )
4493 return self._schema_item_copy(c)
4495 def contains_column(self, col: Column[Any]) -> bool:
4496 """Return True if this constraint contains the given column.
4498 Note that this object also contains an attribute ``.columns``
4499 which is a :class:`_expression.ColumnCollection` of
4500 :class:`_schema.Column` objects.
4502 """
4504 return self._columns.contains_column(col)
4506 def __iter__(self) -> Iterator[Column[Any]]:
4507 return iter(self._columns)
4509 def __len__(self) -> int:
4510 return len(self._columns)
4513class CheckConstraint(ColumnCollectionConstraint):
4514 """A table- or column-level CHECK constraint.
4516 Can be included in the definition of a Table or Column.
4517 """
4519 _allow_multiple_tables = True
4521 __visit_name__ = "table_or_column_check_constraint"
4523 @_document_text_coercion(
4524 "sqltext",
4525 ":class:`.CheckConstraint`",
4526 ":paramref:`.CheckConstraint.sqltext`",
4527 )
4528 def __init__(
4529 self,
4530 sqltext: _TextCoercedExpressionArgument[Any],
4531 name: _ConstraintNameArgument = None,
4532 deferrable: Optional[bool] = None,
4533 initially: Optional[str] = None,
4534 table: Optional[Table] = None,
4535 info: Optional[_InfoType] = None,
4536 _create_rule: Optional[Any] = None,
4537 _autoattach: bool = True,
4538 _type_bound: bool = False,
4539 **dialect_kw: Any,
4540 ) -> None:
4541 r"""Construct a CHECK constraint.
4543 :param sqltext:
4544 A string containing the constraint definition, which will be used
4545 verbatim, or a SQL expression construct. If given as a string,
4546 the object is converted to a :func:`_expression.text` object.
4547 If the textual
4548 string includes a colon character, escape this using a backslash::
4550 CheckConstraint(r"foo ~ E'a(?\:b|c)d")
4552 :param name:
4553 Optional, the in-database name of the constraint.
4555 :param deferrable:
4556 Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
4557 issuing DDL for this constraint.
4559 :param initially:
4560 Optional string. If set, emit INITIALLY <value> when issuing DDL
4561 for this constraint.
4563 :param info: Optional data dictionary which will be populated into the
4564 :attr:`.SchemaItem.info` attribute of this object.
4566 """
4568 self.sqltext = coercions.expect(roles.DDLExpressionRole, sqltext)
4569 columns: List[Column[Any]] = []
4570 visitors.traverse(self.sqltext, {}, {"column": columns.append})
4572 super().__init__(
4573 name=name,
4574 deferrable=deferrable,
4575 initially=initially,
4576 _create_rule=_create_rule,
4577 info=info,
4578 _type_bound=_type_bound,
4579 _autoattach=_autoattach,
4580 *columns,
4581 **dialect_kw,
4582 )
4583 if table is not None:
4584 self._set_parent_with_dispatch(table)
4586 @property
4587 def is_column_level(self) -> bool:
4588 return not isinstance(self.parent, Table)
4590 @util.deprecated(
4591 "1.4",
4592 "The :meth:`_schema.CheckConstraint.copy` method is deprecated "
4593 "and will be removed in a future release.",
4594 )
4595 def copy(
4596 self, *, target_table: Optional[Table] = None, **kw: Any
4597 ) -> CheckConstraint:
4598 return self._copy(target_table=target_table, **kw)
4600 def _copy(
4601 self, *, target_table: Optional[Table] = None, **kw: Any
4602 ) -> CheckConstraint:
4603 if target_table is not None:
4604 # note that target_table is None for the copy process of
4605 # a column-bound CheckConstraint, so this path is not reached
4606 # in that case.
4607 sqltext = _copy_expression(self.sqltext, self.table, target_table)
4608 else:
4609 sqltext = self.sqltext
4610 c = CheckConstraint(
4611 sqltext,
4612 name=self.name,
4613 initially=self.initially,
4614 deferrable=self.deferrable,
4615 _create_rule=self._create_rule,
4616 table=target_table,
4617 comment=self.comment,
4618 _autoattach=False,
4619 _type_bound=self._type_bound,
4620 )
4621 return self._schema_item_copy(c)
4624class ForeignKeyConstraint(ColumnCollectionConstraint):
4625 """A table-level FOREIGN KEY constraint.
4627 Defines a single column or composite FOREIGN KEY ... REFERENCES
4628 constraint. For a no-frills, single column foreign key, adding a
4629 :class:`_schema.ForeignKey` to the definition of a :class:`_schema.Column`
4630 is a
4631 shorthand equivalent for an unnamed, single column
4632 :class:`_schema.ForeignKeyConstraint`.
4634 Examples of foreign key configuration are in :ref:`metadata_foreignkeys`.
4636 """
4638 __visit_name__ = "foreign_key_constraint"
4640 def __init__(
4641 self,
4642 columns: _typing_Sequence[_DDLColumnArgument],
4643 refcolumns: _typing_Sequence[_DDLColumnReferenceArgument],
4644 name: _ConstraintNameArgument = None,
4645 onupdate: Optional[str] = None,
4646 ondelete: Optional[str] = None,
4647 deferrable: Optional[bool] = None,
4648 initially: Optional[str] = None,
4649 use_alter: bool = False,
4650 link_to_name: bool = False,
4651 match: Optional[str] = None,
4652 table: Optional[Table] = None,
4653 info: Optional[_InfoType] = None,
4654 comment: Optional[str] = None,
4655 **dialect_kw: Any,
4656 ) -> None:
4657 r"""Construct a composite-capable FOREIGN KEY.
4659 :param columns: A sequence of local column names. The named columns
4660 must be defined and present in the parent Table. The names should
4661 match the ``key`` given to each column (defaults to the name) unless
4662 ``link_to_name`` is True.
4664 :param refcolumns: A sequence of foreign column names or Column
4665 objects. The columns must all be located within the same Table.
4667 :param name: Optional, the in-database name of the key.
4669 :param onupdate: Optional string. If set, emit ON UPDATE <value> when
4670 issuing DDL for this constraint. Typical values include CASCADE,
4671 DELETE and RESTRICT.
4673 :param ondelete: Optional string. If set, emit ON DELETE <value> when
4674 issuing DDL for this constraint. Typical values include CASCADE,
4675 DELETE and RESTRICT.
4677 :param deferrable: Optional bool. If set, emit DEFERRABLE or NOT
4678 DEFERRABLE when issuing DDL for this constraint.
4680 :param initially: Optional string. If set, emit INITIALLY <value> when
4681 issuing DDL for this constraint.
4683 :param link_to_name: if True, the string name given in ``column`` is
4684 the rendered name of the referenced column, not its locally assigned
4685 ``key``.
4687 :param use_alter: If True, do not emit the DDL for this constraint as
4688 part of the CREATE TABLE definition. Instead, generate it via an
4689 ALTER TABLE statement issued after the full collection of tables
4690 have been created, and drop it via an ALTER TABLE statement before
4691 the full collection of tables are dropped.
4693 The use of :paramref:`_schema.ForeignKeyConstraint.use_alter` is
4694 particularly geared towards the case where two or more tables
4695 are established within a mutually-dependent foreign key constraint
4696 relationship; however, the :meth:`_schema.MetaData.create_all` and
4697 :meth:`_schema.MetaData.drop_all`
4698 methods will perform this resolution
4699 automatically, so the flag is normally not needed.
4701 .. seealso::
4703 :ref:`use_alter`
4705 :param match: Optional string. If set, emit MATCH <value> when issuing
4706 DDL for this constraint. Typical values include SIMPLE, PARTIAL
4707 and FULL.
4709 :param info: Optional data dictionary which will be populated into the
4710 :attr:`.SchemaItem.info` attribute of this object.
4712 :param comment: Optional string that will render an SQL comment on
4713 foreign key constraint creation.
4715 .. versionadded:: 2.0
4717 :param \**dialect_kw: Additional keyword arguments are dialect
4718 specific, and passed in the form ``<dialectname>_<argname>``. See
4719 the documentation regarding an individual dialect at
4720 :ref:`dialect_toplevel` for detail on documented arguments.
4722 """
4724 Constraint.__init__(
4725 self,
4726 name=name,
4727 deferrable=deferrable,
4728 initially=initially,
4729 info=info,
4730 comment=comment,
4731 **dialect_kw,
4732 )
4733 self.onupdate = onupdate
4734 self.ondelete = ondelete
4735 self.link_to_name = link_to_name
4736 self.use_alter = use_alter
4737 self.match = match
4739 if len(set(columns)) != len(refcolumns):
4740 if len(set(columns)) != len(columns):
4741 # e.g. FOREIGN KEY (a, a) REFERENCES r (b, c)
4742 raise exc.ArgumentError(
4743 "ForeignKeyConstraint with duplicate source column "
4744 "references are not supported."
4745 )
4746 else:
4747 # e.g. FOREIGN KEY (a) REFERENCES r (b, c)
4748 # paraphrasing
4749 # https://www.postgresql.org/docs/current/static/ddl-constraints.html
4750 raise exc.ArgumentError(
4751 "ForeignKeyConstraint number "
4752 "of constrained columns must match the number of "
4753 "referenced columns."
4754 )
4756 # standalone ForeignKeyConstraint - create
4757 # associated ForeignKey objects which will be applied to hosted
4758 # Column objects (in col.foreign_keys), either now or when attached
4759 # to the Table for string-specified names
4760 self.elements = [
4761 ForeignKey(
4762 refcol,
4763 _constraint=self,
4764 name=self.name,
4765 onupdate=self.onupdate,
4766 ondelete=self.ondelete,
4767 use_alter=self.use_alter,
4768 link_to_name=self.link_to_name,
4769 match=self.match,
4770 deferrable=self.deferrable,
4771 initially=self.initially,
4772 **self.dialect_kwargs,
4773 )
4774 for refcol in refcolumns
4775 ]
4777 ColumnCollectionMixin.__init__(self, *columns)
4778 if table is not None:
4779 if hasattr(self, "parent"):
4780 assert table is self.parent
4781 self._set_parent_with_dispatch(table)
4783 def _append_element(self, column: Column[Any], fk: ForeignKey) -> None:
4784 self._columns.add(column)
4785 self.elements.append(fk)
4787 columns: ReadOnlyColumnCollection[str, Column[Any]]
4788 """A :class:`_expression.ColumnCollection` representing the set of columns
4789 for this constraint.
4791 """
4793 elements: List[ForeignKey]
4794 """A sequence of :class:`_schema.ForeignKey` objects.
4796 Each :class:`_schema.ForeignKey`
4797 represents a single referring column/referred
4798 column pair.
4800 This collection is intended to be read-only.
4802 """
4804 @property
4805 def _elements(self) -> util.OrderedDict[str, ForeignKey]:
4806 # legacy - provide a dictionary view of (column_key, fk)
4807 return util.OrderedDict(zip(self.column_keys, self.elements))
4809 @property
4810 def _referred_schema(self) -> Optional[str]:
4811 for elem in self.elements:
4812 return elem._referred_schema
4813 else:
4814 return None
4816 @property
4817 def referred_table(self) -> Table:
4818 """The :class:`_schema.Table` object to which this
4819 :class:`_schema.ForeignKeyConstraint` references.
4821 This is a dynamically calculated attribute which may not be available
4822 if the constraint and/or parent table is not yet associated with
4823 a metadata collection that contains the referred table.
4825 """
4826 return self.elements[0].column.table
4828 def _validate_dest_table(self, table: Table) -> None:
4829 table_keys = {
4830 elem._table_key_within_construction() for elem in self.elements
4831 }
4832 if None not in table_keys and len(table_keys) > 1:
4833 elem0, elem1 = sorted(table_keys)[0:2]
4834 raise exc.ArgumentError(
4835 f"ForeignKeyConstraint on "
4836 f"{table.fullname}({self._col_description}) refers to "
4837 f"multiple remote tables: {elem0} and {elem1}"
4838 )
4840 @property
4841 def column_keys(self) -> _typing_Sequence[str]:
4842 """Return a list of string keys representing the local
4843 columns in this :class:`_schema.ForeignKeyConstraint`.
4845 This list is either the original string arguments sent
4846 to the constructor of the :class:`_schema.ForeignKeyConstraint`,
4847 or if the constraint has been initialized with :class:`_schema.Column`
4848 objects, is the string ``.key`` of each element.
4850 """
4851 if hasattr(self, "parent"):
4852 return self._columns.keys()
4853 else:
4854 return [
4855 col.key if isinstance(col, ColumnElement) else str(col)
4856 for col in self._pending_colargs
4857 ]
4859 @property
4860 def _col_description(self) -> str:
4861 return ", ".join(self.column_keys)
4863 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
4864 table = parent
4865 assert isinstance(table, Table)
4866 Constraint._set_parent(self, table)
4868 ColumnCollectionConstraint._set_parent(self, table)
4870 for col, fk in zip(self._columns, self.elements):
4871 if not hasattr(fk, "parent") or fk.parent is not col:
4872 fk._set_parent_with_dispatch(col)
4874 self._validate_dest_table(table)
4876 @util.deprecated(
4877 "1.4",
4878 "The :meth:`_schema.ForeignKeyConstraint.copy` method is deprecated "
4879 "and will be removed in a future release.",
4880 )
4881 def copy(
4882 self,
4883 *,
4884 schema: Optional[str] = None,
4885 target_table: Optional[Table] = None,
4886 **kw: Any,
4887 ) -> ForeignKeyConstraint:
4888 return self._copy(schema=schema, target_table=target_table, **kw)
4890 def _copy(
4891 self,
4892 *,
4893 schema: Optional[str] = None,
4894 target_table: Optional[Table] = None,
4895 **kw: Any,
4896 ) -> ForeignKeyConstraint:
4897 fkc = ForeignKeyConstraint(
4898 [x.parent.key for x in self.elements],
4899 [
4900 x._get_colspec(
4901 schema=schema,
4902 table_name=(
4903 target_table.name
4904 if target_table is not None
4905 and x._table_key_within_construction()
4906 == x.parent.table.key
4907 else None
4908 ),
4909 _is_copy=True,
4910 )
4911 for x in self.elements
4912 ],
4913 name=self.name,
4914 onupdate=self.onupdate,
4915 ondelete=self.ondelete,
4916 use_alter=self.use_alter,
4917 deferrable=self.deferrable,
4918 initially=self.initially,
4919 link_to_name=self.link_to_name,
4920 match=self.match,
4921 comment=self.comment,
4922 )
4923 for self_fk, other_fk in zip(self.elements, fkc.elements):
4924 self_fk._schema_item_copy(other_fk)
4925 return self._schema_item_copy(fkc)
4928class PrimaryKeyConstraint(ColumnCollectionConstraint):
4929 """A table-level PRIMARY KEY constraint.
4931 The :class:`.PrimaryKeyConstraint` object is present automatically
4932 on any :class:`_schema.Table` object; it is assigned a set of
4933 :class:`_schema.Column` objects corresponding to those marked with
4934 the :paramref:`_schema.Column.primary_key` flag::
4936 >>> my_table = Table('mytable', metadata,
4937 ... Column('id', Integer, primary_key=True),
4938 ... Column('version_id', Integer, primary_key=True),
4939 ... Column('data', String(50))
4940 ... )
4941 >>> my_table.primary_key
4942 PrimaryKeyConstraint(
4943 Column('id', Integer(), table=<mytable>,
4944 primary_key=True, nullable=False),
4945 Column('version_id', Integer(), table=<mytable>,
4946 primary_key=True, nullable=False)
4947 )
4949 The primary key of a :class:`_schema.Table` can also be specified by using
4950 a :class:`.PrimaryKeyConstraint` object explicitly; in this mode of usage,
4951 the "name" of the constraint can also be specified, as well as other
4952 options which may be recognized by dialects::
4954 my_table = Table('mytable', metadata,
4955 Column('id', Integer),
4956 Column('version_id', Integer),
4957 Column('data', String(50)),
4958 PrimaryKeyConstraint('id', 'version_id',
4959 name='mytable_pk')
4960 )
4962 The two styles of column-specification should generally not be mixed.
4963 An warning is emitted if the columns present in the
4964 :class:`.PrimaryKeyConstraint`
4965 don't match the columns that were marked as ``primary_key=True``, if both
4966 are present; in this case, the columns are taken strictly from the
4967 :class:`.PrimaryKeyConstraint` declaration, and those columns otherwise
4968 marked as ``primary_key=True`` are ignored. This behavior is intended to
4969 be backwards compatible with previous behavior.
4971 For the use case where specific options are to be specified on the
4972 :class:`.PrimaryKeyConstraint`, but the usual style of using
4973 ``primary_key=True`` flags is still desirable, an empty
4974 :class:`.PrimaryKeyConstraint` may be specified, which will take on the
4975 primary key column collection from the :class:`_schema.Table` based on the
4976 flags::
4978 my_table = Table('mytable', metadata,
4979 Column('id', Integer, primary_key=True),
4980 Column('version_id', Integer, primary_key=True),
4981 Column('data', String(50)),
4982 PrimaryKeyConstraint(name='mytable_pk',
4983 mssql_clustered=True)
4984 )
4986 """
4988 __visit_name__ = "primary_key_constraint"
4990 def __init__(
4991 self,
4992 *columns: _DDLColumnArgument,
4993 name: Optional[str] = None,
4994 deferrable: Optional[bool] = None,
4995 initially: Optional[str] = None,
4996 info: Optional[_InfoType] = None,
4997 _implicit_generated: bool = False,
4998 **dialect_kw: Any,
4999 ) -> None:
5000 self._implicit_generated = _implicit_generated
5001 super().__init__(
5002 *columns,
5003 name=name,
5004 deferrable=deferrable,
5005 initially=initially,
5006 info=info,
5007 **dialect_kw,
5008 )
5010 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
5011 table = parent
5012 assert isinstance(table, Table)
5013 super()._set_parent(table)
5015 if table.primary_key is not self:
5016 table.constraints.discard(table.primary_key)
5017 table.primary_key = self # type: ignore
5018 table.constraints.add(self)
5020 table_pks = [c for c in table.c if c.primary_key]
5021 if (
5022 self._columns
5023 and table_pks
5024 and set(table_pks) != set(self._columns)
5025 ):
5026 # black could not format these inline
5027 table_pk_str = ", ".join("'%s'" % c.name for c in table_pks)
5028 col_str = ", ".join("'%s'" % c.name for c in self._columns)
5030 util.warn(
5031 f"Table '{table.name}' specifies columns "
5032 f"{table_pk_str} as "
5033 f"primary_key=True, "
5034 f"not matching locally specified columns {col_str}; "
5035 f"setting the "
5036 f"current primary key columns to "
5037 f"{col_str}. "
5038 f"This warning "
5039 f"may become an exception in a future release"
5040 )
5041 table_pks[:] = []
5043 for c in self._columns:
5044 c.primary_key = True
5045 if c._user_defined_nullable is NULL_UNSPECIFIED:
5046 c.nullable = False
5047 if table_pks:
5048 self._columns.extend(table_pks)
5050 def _reload(self, columns: Iterable[Column[Any]]) -> None:
5051 """repopulate this :class:`.PrimaryKeyConstraint` given
5052 a set of columns.
5054 Existing columns in the table that are marked as primary_key=True
5055 are maintained.
5057 Also fires a new event.
5059 This is basically like putting a whole new
5060 :class:`.PrimaryKeyConstraint` object on the parent
5061 :class:`_schema.Table` object without actually replacing the object.
5063 The ordering of the given list of columns is also maintained; these
5064 columns will be appended to the list of columns after any which
5065 are already present.
5067 """
5068 # set the primary key flag on new columns.
5069 # note any existing PK cols on the table also have their
5070 # flag still set.
5071 for col in columns:
5072 col.primary_key = True
5074 self._columns.extend(columns)
5076 PrimaryKeyConstraint._autoincrement_column._reset(self) # type: ignore
5077 self._set_parent_with_dispatch(self.table)
5079 def _replace(self, col: Column[Any]) -> None:
5080 PrimaryKeyConstraint._autoincrement_column._reset(self) # type: ignore
5081 self._columns.replace(col)
5083 self.dispatch._sa_event_column_added_to_pk_constraint(self, col)
5085 @property
5086 def columns_autoinc_first(self) -> List[Column[Any]]:
5087 autoinc = self._autoincrement_column
5089 if autoinc is not None:
5090 return [autoinc] + [c for c in self._columns if c is not autoinc]
5091 else:
5092 return list(self._columns)
5094 @util.ro_memoized_property
5095 def _autoincrement_column(self) -> Optional[Column[int]]:
5096 def _validate_autoinc(col: Column[Any], autoinc_true: bool) -> bool:
5097 if col.type._type_affinity is None or not issubclass(
5098 col.type._type_affinity,
5099 (
5100 type_api.INTEGERTYPE._type_affinity,
5101 type_api.NUMERICTYPE._type_affinity,
5102 ),
5103 ):
5104 if autoinc_true:
5105 raise exc.ArgumentError(
5106 f"Column type {col.type} on column '{col}' is not "
5107 f"compatible with autoincrement=True"
5108 )
5109 else:
5110 return False
5111 elif (
5112 not isinstance(col.default, (type(None), Sequence))
5113 and not autoinc_true
5114 ):
5115 return False
5116 elif (
5117 col.server_default is not None
5118 and not isinstance(col.server_default, Identity)
5119 and not autoinc_true
5120 ):
5121 return False
5122 elif col.foreign_keys and col.autoincrement not in (
5123 True,
5124 "ignore_fk",
5125 ):
5126 return False
5127 return True
5129 if len(self._columns) == 1:
5130 col = list(self._columns)[0]
5132 if col.autoincrement is True:
5133 _validate_autoinc(col, True)
5134 return col
5135 elif col.autoincrement in (
5136 "auto",
5137 "ignore_fk",
5138 ) and _validate_autoinc(col, False):
5139 return col
5140 else:
5141 return None
5143 else:
5144 autoinc = None
5145 for col in self._columns:
5146 if col.autoincrement is True:
5147 _validate_autoinc(col, True)
5148 if autoinc is not None:
5149 raise exc.ArgumentError(
5150 f"Only one Column may be marked "
5151 f"autoincrement=True, found both "
5152 f"{col.name} and {autoinc.name}."
5153 )
5154 else:
5155 autoinc = col
5157 return autoinc
5160class UniqueConstraint(ColumnCollectionConstraint):
5161 """A table-level UNIQUE constraint.
5163 Defines a single column or composite UNIQUE constraint. For a no-frills,
5164 single column constraint, adding ``unique=True`` to the ``Column``
5165 definition is a shorthand equivalent for an unnamed, single column
5166 UniqueConstraint.
5167 """
5169 __visit_name__ = "unique_constraint"
5172class Index(
5173 DialectKWArgs, ColumnCollectionMixin, HasConditionalDDL, SchemaItem
5174):
5175 """A table-level INDEX.
5177 Defines a composite (one or more column) INDEX.
5179 E.g.::
5181 sometable = Table("sometable", metadata,
5182 Column("name", String(50)),
5183 Column("address", String(100))
5184 )
5186 Index("some_index", sometable.c.name)
5188 For a no-frills, single column index, adding
5189 :class:`_schema.Column` also supports ``index=True``::
5191 sometable = Table("sometable", metadata,
5192 Column("name", String(50), index=True)
5193 )
5195 For a composite index, multiple columns can be specified::
5197 Index("some_index", sometable.c.name, sometable.c.address)
5199 Functional indexes are supported as well, typically by using the
5200 :data:`.func` construct in conjunction with table-bound
5201 :class:`_schema.Column` objects::
5203 Index("some_index", func.lower(sometable.c.name))
5205 An :class:`.Index` can also be manually associated with a
5206 :class:`_schema.Table`,
5207 either through inline declaration or using
5208 :meth:`_schema.Table.append_constraint`. When this approach is used,
5209 the names
5210 of the indexed columns can be specified as strings::
5212 Table("sometable", metadata,
5213 Column("name", String(50)),
5214 Column("address", String(100)),
5215 Index("some_index", "name", "address")
5216 )
5218 To support functional or expression-based indexes in this form, the
5219 :func:`_expression.text` construct may be used::
5221 from sqlalchemy import text
5223 Table("sometable", metadata,
5224 Column("name", String(50)),
5225 Column("address", String(100)),
5226 Index("some_index", text("lower(name)"))
5227 )
5229 .. seealso::
5231 :ref:`schema_indexes` - General information on :class:`.Index`.
5233 :ref:`postgresql_indexes` - PostgreSQL-specific options available for
5234 the :class:`.Index` construct.
5236 :ref:`mysql_indexes` - MySQL-specific options available for the
5237 :class:`.Index` construct.
5239 :ref:`mssql_indexes` - MSSQL-specific options available for the
5240 :class:`.Index` construct.
5242 """
5244 __visit_name__ = "index"
5246 table: Optional[Table]
5247 expressions: _typing_Sequence[Union[str, ColumnElement[Any]]]
5248 _table_bound_expressions: _typing_Sequence[ColumnElement[Any]]
5250 def __init__(
5251 self,
5252 name: Optional[str],
5253 *expressions: _DDLColumnArgument,
5254 unique: bool = False,
5255 quote: Optional[bool] = None,
5256 info: Optional[_InfoType] = None,
5257 _table: Optional[Table] = None,
5258 _column_flag: bool = False,
5259 **dialect_kw: Any,
5260 ) -> None:
5261 r"""Construct an index object.
5263 :param name:
5264 The name of the index
5266 :param \*expressions:
5267 Column expressions to include in the index. The expressions
5268 are normally instances of :class:`_schema.Column`, but may also
5269 be arbitrary SQL expressions which ultimately refer to a
5270 :class:`_schema.Column`.
5272 :param unique=False:
5273 Keyword only argument; if True, create a unique index.
5275 :param quote=None:
5276 Keyword only argument; whether to apply quoting to the name of
5277 the index. Works in the same manner as that of
5278 :paramref:`_schema.Column.quote`.
5280 :param info=None: Optional data dictionary which will be populated
5281 into the :attr:`.SchemaItem.info` attribute of this object.
5283 :param \**dialect_kw: Additional keyword arguments not mentioned above
5284 are dialect specific, and passed in the form
5285 ``<dialectname>_<argname>``. See the documentation regarding an
5286 individual dialect at :ref:`dialect_toplevel` for detail on
5287 documented arguments.
5289 """
5290 self.table = table = None
5292 self.name = quoted_name.construct(name, quote)
5293 self.unique = unique
5294 if info is not None:
5295 self.info = info
5297 # TODO: consider "table" argument being public, but for
5298 # the purpose of the fix here, it starts as private.
5299 if _table is not None:
5300 table = _table
5302 self._validate_dialect_kwargs(dialect_kw)
5304 self.expressions = []
5305 # will call _set_parent() if table-bound column
5306 # objects are present
5307 ColumnCollectionMixin.__init__(
5308 self,
5309 *expressions,
5310 _column_flag=_column_flag,
5311 _gather_expressions=self.expressions,
5312 )
5313 if table is not None:
5314 self._set_parent(table)
5316 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
5317 table = parent
5318 assert isinstance(table, Table)
5319 ColumnCollectionMixin._set_parent(self, table)
5321 if self.table is not None and table is not self.table:
5322 raise exc.ArgumentError(
5323 f"Index '{self.name}' is against table "
5324 f"'{self.table.description}', and "
5325 f"cannot be associated with table '{table.description}'."
5326 )
5327 self.table = table
5328 table.indexes.add(self)
5330 expressions = self.expressions
5331 col_expressions = self._col_expressions(table)
5332 assert len(expressions) == len(col_expressions)
5334 exprs = []
5335 for expr, colexpr in zip(expressions, col_expressions):
5336 if isinstance(expr, ClauseElement):
5337 exprs.append(expr)
5338 elif colexpr is not None:
5339 exprs.append(colexpr)
5340 else:
5341 assert False
5342 self.expressions = self._table_bound_expressions = exprs
5344 def create(self, bind: _CreateDropBind, checkfirst: bool = False) -> None:
5345 """Issue a ``CREATE`` statement for this
5346 :class:`.Index`, using the given
5347 :class:`.Connection` or :class:`.Engine`` for connectivity.
5349 .. seealso::
5351 :meth:`_schema.MetaData.create_all`.
5353 """
5354 bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
5356 def drop(self, bind: _CreateDropBind, checkfirst: bool = False) -> None:
5357 """Issue a ``DROP`` statement for this
5358 :class:`.Index`, using the given
5359 :class:`.Connection` or :class:`.Engine` for connectivity.
5361 .. seealso::
5363 :meth:`_schema.MetaData.drop_all`.
5365 """
5366 bind._run_ddl_visitor(ddl.SchemaDropper, self, checkfirst=checkfirst)
5368 def __repr__(self) -> str:
5369 exprs: _typing_Sequence[Any] # noqa: F842
5371 return "Index(%s)" % (
5372 ", ".join(
5373 [repr(self.name)]
5374 + [repr(e) for e in self.expressions]
5375 + (self.unique and ["unique=True"] or [])
5376 )
5377 )
5380_NamingSchemaCallable = Callable[[Constraint, Table], str]
5381_NamingSchemaDirective = Union[str, _NamingSchemaCallable]
5384class _NamingSchemaTD(TypedDict, total=False):
5385 fk: _NamingSchemaDirective
5386 pk: _NamingSchemaDirective
5387 ix: _NamingSchemaDirective
5388 ck: _NamingSchemaDirective
5389 uq: _NamingSchemaDirective
5392_NamingSchemaParameter = Union[
5393 # it seems like the TypedDict here is useful for pylance typeahead,
5394 # and not much else
5395 _NamingSchemaTD,
5396 # there is no form that allows Union[Type[Any], str] to work in all
5397 # cases, including breaking out Mapping[] entries for each combination
5398 # even, therefore keys must be `Any` (see #10264)
5399 Mapping[Any, _NamingSchemaDirective],
5400]
5403DEFAULT_NAMING_CONVENTION: _NamingSchemaParameter = util.immutabledict(
5404 {"ix": "ix_%(column_0_label)s"}
5405)
5408class MetaData(HasSchemaAttr):
5409 """A collection of :class:`_schema.Table`
5410 objects and their associated schema
5411 constructs.
5413 Holds a collection of :class:`_schema.Table` objects as well as
5414 an optional binding to an :class:`_engine.Engine` or
5415 :class:`_engine.Connection`. If bound, the :class:`_schema.Table` objects
5416 in the collection and their columns may participate in implicit SQL
5417 execution.
5419 The :class:`_schema.Table` objects themselves are stored in the
5420 :attr:`_schema.MetaData.tables` dictionary.
5422 :class:`_schema.MetaData` is a thread-safe object for read operations.
5423 Construction of new tables within a single :class:`_schema.MetaData`
5424 object,
5425 either explicitly or via reflection, may not be completely thread-safe.
5427 .. seealso::
5429 :ref:`metadata_describing` - Introduction to database metadata
5431 """
5433 __visit_name__ = "metadata"
5435 def __init__(
5436 self,
5437 schema: Optional[str] = None,
5438 quote_schema: Optional[bool] = None,
5439 naming_convention: Optional[_NamingSchemaParameter] = None,
5440 info: Optional[_InfoType] = None,
5441 ) -> None:
5442 """Create a new MetaData object.
5444 :param schema:
5445 The default schema to use for the :class:`_schema.Table`,
5446 :class:`.Sequence`, and potentially other objects associated with
5447 this :class:`_schema.MetaData`. Defaults to ``None``.
5449 .. seealso::
5451 :ref:`schema_metadata_schema_name` - details on how the
5452 :paramref:`_schema.MetaData.schema` parameter is used.
5454 :paramref:`_schema.Table.schema`
5456 :paramref:`.Sequence.schema`
5458 :param quote_schema:
5459 Sets the ``quote_schema`` flag for those :class:`_schema.Table`,
5460 :class:`.Sequence`, and other objects which make usage of the
5461 local ``schema`` name.
5463 :param info: Optional data dictionary which will be populated into the
5464 :attr:`.SchemaItem.info` attribute of this object.
5466 :param naming_convention: a dictionary referring to values which
5467 will establish default naming conventions for :class:`.Constraint`
5468 and :class:`.Index` objects, for those objects which are not given
5469 a name explicitly.
5471 The keys of this dictionary may be:
5473 * a constraint or Index class, e.g. the :class:`.UniqueConstraint`,
5474 :class:`_schema.ForeignKeyConstraint` class, the :class:`.Index`
5475 class
5477 * a string mnemonic for one of the known constraint classes;
5478 ``"fk"``, ``"pk"``, ``"ix"``, ``"ck"``, ``"uq"`` for foreign key,
5479 primary key, index, check, and unique constraint, respectively.
5481 * the string name of a user-defined "token" that can be used
5482 to define new naming tokens.
5484 The values associated with each "constraint class" or "constraint
5485 mnemonic" key are string naming templates, such as
5486 ``"uq_%(table_name)s_%(column_0_name)s"``,
5487 which describe how the name should be composed. The values
5488 associated with user-defined "token" keys should be callables of the
5489 form ``fn(constraint, table)``, which accepts the constraint/index
5490 object and :class:`_schema.Table` as arguments, returning a string
5491 result.
5493 The built-in names are as follows, some of which may only be
5494 available for certain types of constraint:
5496 * ``%(table_name)s`` - the name of the :class:`_schema.Table`
5497 object
5498 associated with the constraint.
5500 * ``%(referred_table_name)s`` - the name of the
5501 :class:`_schema.Table`
5502 object associated with the referencing target of a
5503 :class:`_schema.ForeignKeyConstraint`.
5505 * ``%(column_0_name)s`` - the name of the :class:`_schema.Column`
5506 at
5507 index position "0" within the constraint.
5509 * ``%(column_0N_name)s`` - the name of all :class:`_schema.Column`
5510 objects in order within the constraint, joined without a
5511 separator.
5513 * ``%(column_0_N_name)s`` - the name of all
5514 :class:`_schema.Column`
5515 objects in order within the constraint, joined with an
5516 underscore as a separator.
5518 * ``%(column_0_label)s``, ``%(column_0N_label)s``,
5519 ``%(column_0_N_label)s`` - the label of either the zeroth
5520 :class:`_schema.Column` or all :class:`.Columns`, separated with
5521 or without an underscore
5523 * ``%(column_0_key)s``, ``%(column_0N_key)s``,
5524 ``%(column_0_N_key)s`` - the key of either the zeroth
5525 :class:`_schema.Column` or all :class:`.Columns`, separated with
5526 or without an underscore
5528 * ``%(referred_column_0_name)s``, ``%(referred_column_0N_name)s``
5529 ``%(referred_column_0_N_name)s``, ``%(referred_column_0_key)s``,
5530 ``%(referred_column_0N_key)s``, ... column tokens which
5531 render the names/keys/labels of columns that are referenced
5532 by a :class:`_schema.ForeignKeyConstraint`.
5534 * ``%(constraint_name)s`` - a special key that refers to the
5535 existing name given to the constraint. When this key is
5536 present, the :class:`.Constraint` object's existing name will be
5537 replaced with one that is composed from template string that
5538 uses this token. When this token is present, it is required that
5539 the :class:`.Constraint` is given an explicit name ahead of time.
5541 * user-defined: any additional token may be implemented by passing
5542 it along with a ``fn(constraint, table)`` callable to the
5543 naming_convention dictionary.
5545 .. versionadded:: 1.3.0 - added new ``%(column_0N_name)s``,
5546 ``%(column_0_N_name)s``, and related tokens that produce
5547 concatenations of names, keys, or labels for all columns referred
5548 to by a given constraint.
5550 .. seealso::
5552 :ref:`constraint_naming_conventions` - for detailed usage
5553 examples.
5555 """
5556 if schema is not None and not isinstance(schema, str):
5557 raise exc.ArgumentError(
5558 "expected schema argument to be a string, "
5559 f"got {type(schema)}."
5560 )
5561 self.tables = util.FacadeDict()
5562 self.schema = quoted_name.construct(schema, quote_schema)
5563 self.naming_convention = (
5564 naming_convention
5565 if naming_convention
5566 else DEFAULT_NAMING_CONVENTION
5567 )
5568 if info:
5569 self.info = info
5570 self._schemas: Set[str] = set()
5571 self._sequences: Dict[str, Sequence] = {}
5572 self._fk_memos: Dict[Tuple[str, Optional[str]], List[ForeignKey]] = (
5573 collections.defaultdict(list)
5574 )
5576 tables: util.FacadeDict[str, Table]
5577 """A dictionary of :class:`_schema.Table`
5578 objects keyed to their name or "table key".
5580 The exact key is that determined by the :attr:`_schema.Table.key`
5581 attribute;
5582 for a table with no :attr:`_schema.Table.schema` attribute,
5583 this is the same
5584 as :attr:`_schema.Table.name`. For a table with a schema,
5585 it is typically of the
5586 form ``schemaname.tablename``.
5588 .. seealso::
5590 :attr:`_schema.MetaData.sorted_tables`
5592 """
5594 def __repr__(self) -> str:
5595 return "MetaData()"
5597 def __contains__(self, table_or_key: Union[str, Table]) -> bool:
5598 if not isinstance(table_or_key, str):
5599 table_or_key = table_or_key.key
5600 return table_or_key in self.tables
5602 def _add_table(
5603 self, name: str, schema: Optional[str], table: Table
5604 ) -> None:
5605 key = _get_table_key(name, schema)
5606 self.tables._insert_item(key, table)
5607 if schema:
5608 self._schemas.add(schema)
5610 def _remove_table(self, name: str, schema: Optional[str]) -> None:
5611 key = _get_table_key(name, schema)
5612 removed = dict.pop(self.tables, key, None)
5613 if removed is not None:
5614 for fk in removed.foreign_keys:
5615 fk._remove_from_metadata(self)
5616 if self._schemas:
5617 self._schemas = {
5618 t.schema for t in self.tables.values() if t.schema is not None
5619 }
5621 def __getstate__(self) -> Dict[str, Any]:
5622 return {
5623 "tables": self.tables,
5624 "schema": self.schema,
5625 "schemas": self._schemas,
5626 "sequences": self._sequences,
5627 "fk_memos": self._fk_memos,
5628 "naming_convention": self.naming_convention,
5629 }
5631 def __setstate__(self, state: Dict[str, Any]) -> None:
5632 self.tables = state["tables"]
5633 self.schema = state["schema"]
5634 self.naming_convention = state["naming_convention"]
5635 self._sequences = state["sequences"]
5636 self._schemas = state["schemas"]
5637 self._fk_memos = state["fk_memos"]
5639 def clear(self) -> None:
5640 """Clear all Table objects from this MetaData."""
5642 dict.clear(self.tables) # type: ignore
5643 self._schemas.clear()
5644 self._fk_memos.clear()
5646 def remove(self, table: Table) -> None:
5647 """Remove the given Table object from this MetaData."""
5649 self._remove_table(table.name, table.schema)
5651 @property
5652 def sorted_tables(self) -> List[Table]:
5653 """Returns a list of :class:`_schema.Table` objects sorted in order of
5654 foreign key dependency.
5656 The sorting will place :class:`_schema.Table`
5657 objects that have dependencies
5658 first, before the dependencies themselves, representing the
5659 order in which they can be created. To get the order in which
5660 the tables would be dropped, use the ``reversed()`` Python built-in.
5662 .. warning::
5664 The :attr:`.MetaData.sorted_tables` attribute cannot by itself
5665 accommodate automatic resolution of dependency cycles between
5666 tables, which are usually caused by mutually dependent foreign key
5667 constraints. When these cycles are detected, the foreign keys
5668 of these tables are omitted from consideration in the sort.
5669 A warning is emitted when this condition occurs, which will be an
5670 exception raise in a future release. Tables which are not part
5671 of the cycle will still be returned in dependency order.
5673 To resolve these cycles, the
5674 :paramref:`_schema.ForeignKeyConstraint.use_alter` parameter may be
5675 applied to those constraints which create a cycle. Alternatively,
5676 the :func:`_schema.sort_tables_and_constraints` function will
5677 automatically return foreign key constraints in a separate
5678 collection when cycles are detected so that they may be applied
5679 to a schema separately.
5681 .. versionchanged:: 1.3.17 - a warning is emitted when
5682 :attr:`.MetaData.sorted_tables` cannot perform a proper sort
5683 due to cyclical dependencies. This will be an exception in a
5684 future release. Additionally, the sort will continue to return
5685 other tables not involved in the cycle in dependency order which
5686 was not the case previously.
5688 .. seealso::
5690 :func:`_schema.sort_tables`
5692 :func:`_schema.sort_tables_and_constraints`
5694 :attr:`_schema.MetaData.tables`
5696 :meth:`_reflection.Inspector.get_table_names`
5698 :meth:`_reflection.Inspector.get_sorted_table_and_fkc_names`
5701 """
5702 return ddl.sort_tables(
5703 sorted(self.tables.values(), key=lambda t: t.key) # type: ignore
5704 )
5706 # overload needed to work around mypy this mypy
5707 # https://github.com/python/mypy/issues/17093
5708 @overload
5709 def reflect(
5710 self,
5711 bind: Engine,
5712 schema: Optional[str] = ...,
5713 views: bool = ...,
5714 only: Union[
5715 _typing_Sequence[str], Callable[[str, MetaData], bool], None
5716 ] = ...,
5717 extend_existing: bool = ...,
5718 autoload_replace: bool = ...,
5719 resolve_fks: bool = ...,
5720 **dialect_kwargs: Any,
5721 ) -> None: ...
5723 @overload
5724 def reflect(
5725 self,
5726 bind: Connection,
5727 schema: Optional[str] = ...,
5728 views: bool = ...,
5729 only: Union[
5730 _typing_Sequence[str], Callable[[str, MetaData], bool], None
5731 ] = ...,
5732 extend_existing: bool = ...,
5733 autoload_replace: bool = ...,
5734 resolve_fks: bool = ...,
5735 **dialect_kwargs: Any,
5736 ) -> None: ...
5738 @util.preload_module("sqlalchemy.engine.reflection")
5739 def reflect(
5740 self,
5741 bind: Union[Engine, Connection],
5742 schema: Optional[str] = None,
5743 views: bool = False,
5744 only: Union[
5745 _typing_Sequence[str], Callable[[str, MetaData], bool], None
5746 ] = None,
5747 extend_existing: bool = False,
5748 autoload_replace: bool = True,
5749 resolve_fks: bool = True,
5750 **dialect_kwargs: Any,
5751 ) -> None:
5752 r"""Load all available table definitions from the database.
5754 Automatically creates ``Table`` entries in this ``MetaData`` for any
5755 table available in the database but not yet present in the
5756 ``MetaData``. May be called multiple times to pick up tables recently
5757 added to the database, however no special action is taken if a table
5758 in this ``MetaData`` no longer exists in the database.
5760 :param bind:
5761 A :class:`.Connection` or :class:`.Engine` used to access the
5762 database.
5764 :param schema:
5765 Optional, query and reflect tables from an alternate schema.
5766 If None, the schema associated with this :class:`_schema.MetaData`
5767 is used, if any.
5769 :param views:
5770 If True, also reflect views (materialized and plain).
5772 :param only:
5773 Optional. Load only a sub-set of available named tables. May be
5774 specified as a sequence of names or a callable.
5776 If a sequence of names is provided, only those tables will be
5777 reflected. An error is raised if a table is requested but not
5778 available. Named tables already present in this ``MetaData`` are
5779 ignored.
5781 If a callable is provided, it will be used as a boolean predicate to
5782 filter the list of potential table names. The callable is called
5783 with a table name and this ``MetaData`` instance as positional
5784 arguments and should return a true value for any table to reflect.
5786 :param extend_existing: Passed along to each :class:`_schema.Table` as
5787 :paramref:`_schema.Table.extend_existing`.
5789 :param autoload_replace: Passed along to each :class:`_schema.Table`
5790 as
5791 :paramref:`_schema.Table.autoload_replace`.
5793 :param resolve_fks: if True, reflect :class:`_schema.Table`
5794 objects linked
5795 to :class:`_schema.ForeignKey` objects located in each
5796 :class:`_schema.Table`.
5797 For :meth:`_schema.MetaData.reflect`,
5798 this has the effect of reflecting
5799 related tables that might otherwise not be in the list of tables
5800 being reflected, for example if the referenced table is in a
5801 different schema or is omitted via the
5802 :paramref:`.MetaData.reflect.only` parameter. When False,
5803 :class:`_schema.ForeignKey` objects are not followed to the
5804 :class:`_schema.Table`
5805 in which they link, however if the related table is also part of the
5806 list of tables that would be reflected in any case, the
5807 :class:`_schema.ForeignKey` object will still resolve to its related
5808 :class:`_schema.Table` after the :meth:`_schema.MetaData.reflect`
5809 operation is
5810 complete. Defaults to True.
5812 .. versionadded:: 1.3.0
5814 .. seealso::
5816 :paramref:`_schema.Table.resolve_fks`
5818 :param \**dialect_kwargs: Additional keyword arguments not mentioned
5819 above are dialect specific, and passed in the form
5820 ``<dialectname>_<argname>``. See the documentation regarding an
5821 individual dialect at :ref:`dialect_toplevel` for detail on
5822 documented arguments.
5824 .. seealso::
5826 :ref:`metadata_reflection_toplevel`
5828 :meth:`_events.DDLEvents.column_reflect` - Event used to customize
5829 the reflected columns. Usually used to generalize the types using
5830 :meth:`_types.TypeEngine.as_generic`
5832 :ref:`metadata_reflection_dbagnostic_types` - describes how to
5833 reflect tables using general types.
5835 """
5837 with inspection.inspect(bind)._inspection_context() as insp:
5838 reflect_opts: Any = {
5839 "autoload_with": insp,
5840 "extend_existing": extend_existing,
5841 "autoload_replace": autoload_replace,
5842 "resolve_fks": resolve_fks,
5843 "_extend_on": set(),
5844 }
5846 reflect_opts.update(dialect_kwargs)
5848 if schema is None:
5849 schema = self.schema
5851 if schema is not None:
5852 reflect_opts["schema"] = schema
5854 kind = util.preloaded.engine_reflection.ObjectKind.TABLE
5855 available: util.OrderedSet[str] = util.OrderedSet(
5856 insp.get_table_names(schema)
5857 )
5858 if views:
5859 kind = util.preloaded.engine_reflection.ObjectKind.ANY
5860 available.update(insp.get_view_names(schema))
5861 try:
5862 available.update(insp.get_materialized_view_names(schema))
5863 except NotImplementedError:
5864 pass
5866 if schema is not None:
5867 available_w_schema: util.OrderedSet[str] = util.OrderedSet(
5868 [f"{schema}.{name}" for name in available]
5869 )
5870 else:
5871 available_w_schema = available
5873 current = set(self.tables)
5875 if only is None:
5876 load = [
5877 name
5878 for name, schname in zip(available, available_w_schema)
5879 if extend_existing or schname not in current
5880 ]
5881 elif callable(only):
5882 load = [
5883 name
5884 for name, schname in zip(available, available_w_schema)
5885 if (extend_existing or schname not in current)
5886 and only(name, self)
5887 ]
5888 else:
5889 missing = [name for name in only if name not in available]
5890 if missing:
5891 s = schema and (" schema '%s'" % schema) or ""
5892 missing_str = ", ".join(missing)
5893 raise exc.InvalidRequestError(
5894 f"Could not reflect: requested table(s) not available "
5895 f"in {bind.engine!r}{s}: ({missing_str})"
5896 )
5897 load = [
5898 name
5899 for name in only
5900 if extend_existing or name not in current
5901 ]
5902 # pass the available tables so the inspector can
5903 # choose to ignore the filter_names
5904 _reflect_info = insp._get_reflection_info(
5905 schema=schema,
5906 filter_names=load,
5907 available=available,
5908 kind=kind,
5909 scope=util.preloaded.engine_reflection.ObjectScope.ANY,
5910 **dialect_kwargs,
5911 )
5912 reflect_opts["_reflect_info"] = _reflect_info
5914 for name in load:
5915 try:
5916 Table(name, self, **reflect_opts)
5917 except exc.UnreflectableTableError as uerr:
5918 util.warn(f"Skipping table {name}: {uerr}")
5920 def create_all(
5921 self,
5922 bind: _CreateDropBind,
5923 tables: Optional[_typing_Sequence[Table]] = None,
5924 checkfirst: bool = True,
5925 ) -> None:
5926 """Create all tables stored in this metadata.
5928 Conditional by default, will not attempt to recreate tables already
5929 present in the target database.
5931 :param bind:
5932 A :class:`.Connection` or :class:`.Engine` used to access the
5933 database.
5935 :param tables:
5936 Optional list of ``Table`` objects, which is a subset of the total
5937 tables in the ``MetaData`` (others are ignored).
5939 :param checkfirst:
5940 Defaults to True, don't issue CREATEs for tables already present
5941 in the target database.
5943 """
5944 bind._run_ddl_visitor(
5945 ddl.SchemaGenerator, self, checkfirst=checkfirst, tables=tables
5946 )
5948 def drop_all(
5949 self,
5950 bind: _CreateDropBind,
5951 tables: Optional[_typing_Sequence[Table]] = None,
5952 checkfirst: bool = True,
5953 ) -> None:
5954 """Drop all tables stored in this metadata.
5956 Conditional by default, will not attempt to drop tables not present in
5957 the target database.
5959 :param bind:
5960 A :class:`.Connection` or :class:`.Engine` used to access the
5961 database.
5963 :param tables:
5964 Optional list of ``Table`` objects, which is a subset of the
5965 total tables in the ``MetaData`` (others are ignored).
5967 :param checkfirst:
5968 Defaults to True, only issue DROPs for tables confirmed to be
5969 present in the target database.
5971 """
5972 bind._run_ddl_visitor(
5973 ddl.SchemaDropper, self, checkfirst=checkfirst, tables=tables
5974 )
5977class Computed(FetchedValue, SchemaItem):
5978 """Defines a generated column, i.e. "GENERATED ALWAYS AS" syntax.
5980 The :class:`.Computed` construct is an inline construct added to the
5981 argument list of a :class:`_schema.Column` object::
5983 from sqlalchemy import Computed
5985 Table('square', metadata_obj,
5986 Column('side', Float, nullable=False),
5987 Column('area', Float, Computed('side * side'))
5988 )
5990 See the linked documentation below for complete details.
5992 .. versionadded:: 1.3.11
5994 .. seealso::
5996 :ref:`computed_ddl`
5998 """
6000 __visit_name__ = "computed_column"
6002 column: Optional[Column[Any]]
6004 @_document_text_coercion(
6005 "sqltext", ":class:`.Computed`", ":paramref:`.Computed.sqltext`"
6006 )
6007 def __init__(
6008 self, sqltext: _DDLColumnArgument, persisted: Optional[bool] = None
6009 ) -> None:
6010 """Construct a GENERATED ALWAYS AS DDL construct to accompany a
6011 :class:`_schema.Column`.
6013 :param sqltext:
6014 A string containing the column generation expression, which will be
6015 used verbatim, or a SQL expression construct, such as a
6016 :func:`_expression.text`
6017 object. If given as a string, the object is converted to a
6018 :func:`_expression.text` object.
6020 :param persisted:
6021 Optional, controls how this column should be persisted by the
6022 database. Possible values are:
6024 * ``None``, the default, it will use the default persistence
6025 defined by the database.
6026 * ``True``, will render ``GENERATED ALWAYS AS ... STORED``, or the
6027 equivalent for the target database if supported.
6028 * ``False``, will render ``GENERATED ALWAYS AS ... VIRTUAL``, or
6029 the equivalent for the target database if supported.
6031 Specifying ``True`` or ``False`` may raise an error when the DDL
6032 is emitted to the target database if the database does not support
6033 that persistence option. Leaving this parameter at its default
6034 of ``None`` is guaranteed to succeed for all databases that support
6035 ``GENERATED ALWAYS AS``.
6037 """
6038 self.sqltext = coercions.expect(roles.DDLExpressionRole, sqltext)
6039 self.persisted = persisted
6040 self.column = None
6042 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
6043 assert isinstance(parent, Column)
6045 if not isinstance(
6046 parent.server_default, (type(None), Computed)
6047 ) or not isinstance(parent.server_onupdate, (type(None), Computed)):
6048 raise exc.ArgumentError(
6049 "A generated column cannot specify a server_default or a "
6050 "server_onupdate argument"
6051 )
6052 self.column = parent
6053 parent.computed = self
6054 self.column.server_onupdate = self
6055 self.column.server_default = self
6057 def _as_for_update(self, for_update: bool) -> FetchedValue:
6058 return self
6060 @util.deprecated(
6061 "1.4",
6062 "The :meth:`_schema.Computed.copy` method is deprecated "
6063 "and will be removed in a future release.",
6064 )
6065 def copy(
6066 self, *, target_table: Optional[Table] = None, **kw: Any
6067 ) -> Computed:
6068 return self._copy(target_table=target_table, **kw)
6070 def _copy(
6071 self, *, target_table: Optional[Table] = None, **kw: Any
6072 ) -> Computed:
6073 sqltext = _copy_expression(
6074 self.sqltext,
6075 self.column.table if self.column is not None else None,
6076 target_table,
6077 )
6078 g = Computed(sqltext, persisted=self.persisted)
6080 return self._schema_item_copy(g)
6083class Identity(IdentityOptions, FetchedValue, SchemaItem):
6084 """Defines an identity column, i.e. "GENERATED { ALWAYS | BY DEFAULT }
6085 AS IDENTITY" syntax.
6087 The :class:`.Identity` construct is an inline construct added to the
6088 argument list of a :class:`_schema.Column` object::
6090 from sqlalchemy import Identity
6092 Table('foo', metadata_obj,
6093 Column('id', Integer, Identity())
6094 Column('description', Text),
6095 )
6097 See the linked documentation below for complete details.
6099 .. versionadded:: 1.4
6101 .. seealso::
6103 :ref:`identity_ddl`
6105 """
6107 __visit_name__ = "identity_column"
6109 is_identity = True
6111 @util.deprecated_params(
6112 order=(
6113 "2.1",
6114 "This parameter is supported only by Oracle, "
6115 "use ``oracle_order`` instead.",
6116 ),
6117 on_null=(
6118 "2.1",
6119 "This parameter is supported only by Oracle, "
6120 "use ``oracle_on_null`` instead.",
6121 ),
6122 )
6123 def __init__(
6124 self,
6125 always: Optional[bool] = False,
6126 on_null: Optional[bool] = None,
6127 start: Optional[int] = None,
6128 increment: Optional[int] = None,
6129 minvalue: Optional[int] = None,
6130 maxvalue: Optional[int] = None,
6131 nominvalue: Optional[bool] = None,
6132 nomaxvalue: Optional[bool] = None,
6133 cycle: Optional[bool] = None,
6134 cache: Optional[int] = None,
6135 order: Optional[bool] = None,
6136 **dialect_kw: Any,
6137 ) -> None:
6138 """Construct a GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY DDL
6139 construct to accompany a :class:`_schema.Column`.
6141 See the :class:`.Sequence` documentation for a complete description
6142 of most parameters.
6144 .. note::
6145 MSSQL supports this construct as the preferred alternative to
6146 generate an IDENTITY on a column, but it uses non standard
6147 syntax that only support :paramref:`_schema.Identity.start`
6148 and :paramref:`_schema.Identity.increment`.
6149 All other parameters are ignored.
6151 :param always:
6152 A boolean, that indicates the type of identity column.
6153 If ``False`` is specified, the default, then the user-specified
6154 value takes precedence.
6155 If ``True`` is specified, a user-specified value is not accepted (
6156 on some backends, like PostgreSQL, OVERRIDING SYSTEM VALUE, or
6157 similar, may be specified in an INSERT to override the sequence
6158 value).
6159 Some backends also have a default value for this parameter,
6160 ``None`` can be used to omit rendering this part in the DDL. It
6161 will be treated as ``False`` if a backend does not have a default
6162 value.
6164 :param on_null:
6165 Set to ``True`` to specify ON NULL in conjunction with a
6166 ``always=False`` identity column. This option is only supported on
6167 some backends, like Oracle.
6169 :param start: the starting index of the sequence.
6170 :param increment: the increment value of the sequence.
6171 :param minvalue: the minimum value of the sequence.
6172 :param maxvalue: the maximum value of the sequence.
6173 :param nominvalue: no minimum value of the sequence.
6174 :param nomaxvalue: no maximum value of the sequence.
6175 :param cycle: allows the sequence to wrap around when the maxvalue
6176 or minvalue has been reached.
6177 :param cache: optional integer value; number of future values in the
6178 sequence which are calculated in advance.
6179 :param order: optional boolean value; if true, renders the
6180 ORDER keyword.
6182 """
6183 self.dialect_options
6184 if on_null is not None:
6185 if "oracle_on_null" in dialect_kw:
6186 raise exc.ArgumentError(
6187 "Cannot specify both 'on_null' and 'oracle_on_null'. "
6188 "Plese use only 'oracle_on_null'."
6189 )
6190 dialect_kw["oracle_on_null"] = on_null
6192 IdentityOptions.__init__(
6193 self,
6194 start=start,
6195 increment=increment,
6196 minvalue=minvalue,
6197 maxvalue=maxvalue,
6198 nominvalue=nominvalue,
6199 nomaxvalue=nomaxvalue,
6200 cycle=cycle,
6201 cache=cache,
6202 order=order,
6203 **dialect_kw,
6204 )
6205 self.always = always
6206 self.column = None
6208 @property
6209 def on_null(self) -> Optional[bool]:
6210 """Alias of the ``dialect_kwargs`` ``'oracle_on_null'``.
6212 .. deprecated:: 2.1 The 'on_null' attribute is deprecated.
6213 """
6214 value: Optional[bool] = self.dialect_kwargs.get("oracle_on_null")
6215 return value
6217 def _set_parent(self, parent: SchemaEventTarget, **kw: Any) -> None:
6218 assert isinstance(parent, Column)
6219 if not isinstance(
6220 parent.server_default, (type(None), Identity)
6221 ) or not isinstance(parent.server_onupdate, type(None)):
6222 raise exc.ArgumentError(
6223 "A column with an Identity object cannot specify a "
6224 "server_default or a server_onupdate argument"
6225 )
6226 if parent.autoincrement is False:
6227 raise exc.ArgumentError(
6228 "A column with an Identity object cannot specify "
6229 "autoincrement=False"
6230 )
6231 self.column = parent
6233 parent.identity = self
6234 if parent._user_defined_nullable is NULL_UNSPECIFIED:
6235 parent.nullable = False
6237 parent.server_default = self
6239 def _as_for_update(self, for_update: bool) -> FetchedValue:
6240 return self
6242 @util.deprecated(
6243 "1.4",
6244 "The :meth:`_schema.Identity.copy` method is deprecated "
6245 "and will be removed in a future release.",
6246 )
6247 def copy(self, **kw: Any) -> Identity:
6248 return self._copy(**kw)
6250 def _copy(self, **kw: Any) -> Identity:
6251 i = Identity(**self._as_dict(), **self.dialect_kwargs)
6253 return self._schema_item_copy(i)
6255 def _as_dict(self) -> Dict[str, Any]:
6256 return {
6257 # always=None means something different than always=False
6258 "always": self.always,
6259 **super()._as_dict(),
6260 }