Coverage for /pythoncovmergedfiles/medio/medio/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/schema.py: 55%
1296 statements
« prev ^ index » next coverage.py v7.0.1, created at 2022-12-25 06:11 +0000
1# sql/schema.py
2# Copyright (C) 2005-2022 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 absolute_import
33import collections
35import sqlalchemy
36from . import coercions
37from . import ddl
38from . import roles
39from . import type_api
40from . import visitors
41from .base import _bind_or_error
42from .base import DedupeColumnCollection
43from .base import DialectKWArgs
44from .base import Executable
45from .base import SchemaEventTarget
46from .coercions import _document_text_coercion
47from .elements import ClauseElement
48from .elements import ColumnClause
49from .elements import ColumnElement
50from .elements import quoted_name
51from .elements import TextClause
52from .selectable import TableClause
53from .type_api import to_instance
54from .visitors import InternalTraversal
55from .. import event
56from .. import exc
57from .. import inspection
58from .. import util
61RETAIN_SCHEMA = util.symbol(
62 "retain_schema"
63 """Symbol indicating that a :class:`_schema.Table`, :class:`.Sequence`
64 or in some cases a :class:`_schema.ForeignKey` object, in situations
65 where the object is being copied for a :meth:`.Table.to_metadata`
66 operation, should retain the schema name that it already has.
68 """
69)
71BLANK_SCHEMA = util.symbol(
72 "blank_schema",
73 """Symbol indicating that a :class:`_schema.Table`, :class:`.Sequence`
74 or in some cases a :class:`_schema.ForeignKey` object
75 should have 'None' for its schema, even if the parent
76 :class:`_schema.MetaData` has specified a schema.
78 .. versionadded:: 1.0.14
80 """,
81)
83NULL_UNSPECIFIED = util.symbol(
84 "NULL_UNSPECIFIED",
85 """Symbol indicating the "nullable" keyword was not passed to a Column.
87 Normally we would expect None to be acceptable for this but some backends
88 such as that of SQL Server place special signficance on a "nullability"
89 value of None.
91 """,
92)
95def _get_table_key(name, schema):
96 if schema is None:
97 return name
98 else:
99 return schema + "." + name
102# this should really be in sql/util.py but we'd have to
103# break an import cycle
104def _copy_expression(expression, source_table, target_table):
105 if source_table is None or target_table is None:
106 return expression
108 def replace(col):
109 if (
110 isinstance(col, Column)
111 and col.table is source_table
112 and col.key in source_table.c
113 ):
114 return target_table.c[col.key]
115 else:
116 return None
118 return visitors.replacement_traverse(expression, {}, replace)
121@inspection._self_inspects
122class SchemaItem(SchemaEventTarget, visitors.Visitable):
123 """Base class for items that define a database schema."""
125 __visit_name__ = "schema_item"
127 create_drop_stringify_dialect = "default"
129 def _init_items(self, *args, **kw):
130 """Initialize the list of child items for this SchemaItem."""
131 for item in args:
132 if item is not None:
133 try:
134 spwd = item._set_parent_with_dispatch
135 except AttributeError as err:
136 util.raise_(
137 exc.ArgumentError(
138 "'SchemaItem' object, such as a 'Column' or a "
139 "'Constraint' expected, got %r" % item
140 ),
141 replace_context=err,
142 )
143 else:
144 spwd(self, **kw)
146 def __repr__(self):
147 return util.generic_repr(self, omit_kwarg=["info"])
149 @util.memoized_property
150 def info(self):
151 """Info dictionary associated with the object, allowing user-defined
152 data to be associated with this :class:`.SchemaItem`.
154 The dictionary is automatically generated when first accessed.
155 It can also be specified in the constructor of some objects,
156 such as :class:`_schema.Table` and :class:`_schema.Column`.
158 """
159 return {}
161 def _schema_item_copy(self, schema_item):
162 if "info" in self.__dict__:
163 schema_item.info = self.info.copy()
164 schema_item.dispatch._update(self.dispatch)
165 return schema_item
167 _use_schema_map = True
170class Table(DialectKWArgs, SchemaItem, TableClause):
171 r"""Represent a table in a database.
173 e.g.::
175 mytable = Table(
176 "mytable", metadata,
177 Column('mytable_id', Integer, primary_key=True),
178 Column('value', String(50))
179 )
181 The :class:`_schema.Table`
182 object constructs a unique instance of itself based
183 on its name and optional schema name within the given
184 :class:`_schema.MetaData` object. Calling the :class:`_schema.Table`
185 constructor with the same name and same :class:`_schema.MetaData` argument
186 a second time will return the *same* :class:`_schema.Table`
187 object - in this way
188 the :class:`_schema.Table` constructor acts as a registry function.
190 .. seealso::
192 :ref:`metadata_describing` - Introduction to database metadata
194 Constructor arguments are as follows:
196 :param name: The name of this table as represented in the database.
198 The table name, along with the value of the ``schema`` parameter,
199 forms a key which uniquely identifies this :class:`_schema.Table`
200 within
201 the owning :class:`_schema.MetaData` collection.
202 Additional calls to :class:`_schema.Table` with the same name,
203 metadata,
204 and schema name will return the same :class:`_schema.Table` object.
206 Names which contain no upper case characters
207 will be treated as case insensitive names, and will not be quoted
208 unless they are a reserved word or contain special characters.
209 A name with any number of upper case characters is considered
210 to be case sensitive, and will be sent as quoted.
212 To enable unconditional quoting for the table name, specify the flag
213 ``quote=True`` to the constructor, or use the :class:`.quoted_name`
214 construct to specify the name.
216 :param metadata: a :class:`_schema.MetaData`
217 object which will contain this
218 table. The metadata is used as a point of association of this table
219 with other tables which are referenced via foreign key. It also
220 may be used to associate this table with a particular
221 :class:`.Connectable`.
223 :param \*args: Additional positional arguments are used primarily
224 to add the list of :class:`_schema.Column`
225 objects contained within this
226 table. Similar to the style of a CREATE TABLE statement, other
227 :class:`.SchemaItem` constructs may be added here, including
228 :class:`.PrimaryKeyConstraint`, and
229 :class:`_schema.ForeignKeyConstraint`.
231 :param autoload: Defaults to ``False``, unless
232 :paramref:`_schema.Table.autoload_with`
233 is set in which case it defaults to ``True``;
234 :class:`_schema.Column` objects
235 for this table should be reflected from the database, possibly
236 augmenting objects that were explicitly specified.
237 :class:`_schema.Column` and other objects explicitly set on the
238 table will replace corresponding reflected objects.
240 .. deprecated:: 1.4
242 The autoload parameter is deprecated and will be removed in
243 version 2.0. Please use the
244 :paramref:`_schema.Table.autoload_with` parameter, passing an
245 engine or connection.
247 .. seealso::
249 :ref:`metadata_reflection_toplevel`
251 :param autoload_replace: Defaults to ``True``; when using
252 :paramref:`_schema.Table.autoload`
253 in conjunction with :paramref:`_schema.Table.extend_existing`,
254 indicates
255 that :class:`_schema.Column` objects present in the already-existing
256 :class:`_schema.Table`
257 object should be replaced with columns of the same
258 name retrieved from the autoload process. When ``False``, columns
259 already present under existing names will be omitted from the
260 reflection process.
262 Note that this setting does not impact :class:`_schema.Column` objects
263 specified programmatically within the call to :class:`_schema.Table`
264 that
265 also is autoloading; those :class:`_schema.Column` objects will always
266 replace existing columns of the same name when
267 :paramref:`_schema.Table.extend_existing` is ``True``.
269 .. seealso::
271 :paramref:`_schema.Table.autoload`
273 :paramref:`_schema.Table.extend_existing`
275 :param autoload_with: An :class:`_engine.Engine` or
276 :class:`_engine.Connection` object,
277 or a :class:`_reflection.Inspector` object as returned by
278 :func:`_sa.inspect`
279 against one, with which this :class:`_schema.Table`
280 object will be reflected.
281 When set to a non-None value, the autoload process will take place
282 for this table against the given engine or connection.
284 :param extend_existing: When ``True``, indicates that if this
285 :class:`_schema.Table` is already present in the given
286 :class:`_schema.MetaData`,
287 apply further arguments within the constructor to the existing
288 :class:`_schema.Table`.
290 If :paramref:`_schema.Table.extend_existing` or
291 :paramref:`_schema.Table.keep_existing` are not set,
292 and the given name
293 of the new :class:`_schema.Table` refers to a :class:`_schema.Table`
294 that is
295 already present in the target :class:`_schema.MetaData` collection,
296 and
297 this :class:`_schema.Table`
298 specifies additional columns or other constructs
299 or flags that modify the table's state, an
300 error is raised. The purpose of these two mutually-exclusive flags
301 is to specify what action should be taken when a
302 :class:`_schema.Table`
303 is specified that matches an existing :class:`_schema.Table`,
304 yet specifies
305 additional constructs.
307 :paramref:`_schema.Table.extend_existing`
308 will also work in conjunction
309 with :paramref:`_schema.Table.autoload` to run a new reflection
310 operation against the database, even if a :class:`_schema.Table`
311 of the same name is already present in the target
312 :class:`_schema.MetaData`; newly reflected :class:`_schema.Column`
313 objects
314 and other options will be added into the state of the
315 :class:`_schema.Table`, potentially overwriting existing columns
316 and options of the same name.
318 As is always the case with :paramref:`_schema.Table.autoload`,
319 :class:`_schema.Column` objects can be specified in the same
320 :class:`_schema.Table`
321 constructor, which will take precedence. Below, the existing
322 table ``mytable`` will be augmented with :class:`_schema.Column`
323 objects
324 both reflected from the database, as well as the given
325 :class:`_schema.Column`
326 named "y"::
328 Table("mytable", metadata,
329 Column('y', Integer),
330 extend_existing=True,
331 autoload_with=engine
332 )
334 .. seealso::
336 :paramref:`_schema.Table.autoload`
338 :paramref:`_schema.Table.autoload_replace`
340 :paramref:`_schema.Table.keep_existing`
343 :param implicit_returning: True by default - indicates that
344 RETURNING can be used by default to fetch newly inserted primary key
345 values, for backends which support this. Note that
346 :func:`_sa.create_engine` also provides an ``implicit_returning``
347 flag.
349 :param include_columns: A list of strings indicating a subset of
350 columns to be loaded via the ``autoload`` operation; table columns who
351 aren't present in this list will not be represented on the resulting
352 ``Table`` object. Defaults to ``None`` which indicates all columns
353 should be reflected.
355 :param resolve_fks: Whether or not to reflect :class:`_schema.Table`
356 objects
357 related to this one via :class:`_schema.ForeignKey` objects, when
358 :paramref:`_schema.Table.autoload` or
359 :paramref:`_schema.Table.autoload_with` is
360 specified. Defaults to True. Set to False to disable reflection of
361 related tables as :class:`_schema.ForeignKey`
362 objects are encountered; may be
363 used either to save on SQL calls or to avoid issues with related tables
364 that can't be accessed. Note that if a related table is already present
365 in the :class:`_schema.MetaData` collection, or becomes present later,
366 a
367 :class:`_schema.ForeignKey` object associated with this
368 :class:`_schema.Table` will
369 resolve to that table normally.
371 .. versionadded:: 1.3
373 .. seealso::
375 :paramref:`.MetaData.reflect.resolve_fks`
378 :param info: Optional data dictionary which will be populated into the
379 :attr:`.SchemaItem.info` attribute of this object.
381 :param keep_existing: When ``True``, indicates that if this Table
382 is already present in the given :class:`_schema.MetaData`, ignore
383 further arguments within the constructor to the existing
384 :class:`_schema.Table`, and return the :class:`_schema.Table`
385 object as
386 originally created. This is to allow a function that wishes
387 to define a new :class:`_schema.Table` on first call, but on
388 subsequent calls will return the same :class:`_schema.Table`,
389 without any of the declarations (particularly constraints)
390 being applied a second time.
392 If :paramref:`_schema.Table.extend_existing` or
393 :paramref:`_schema.Table.keep_existing` are not set,
394 and the given name
395 of the new :class:`_schema.Table` refers to a :class:`_schema.Table`
396 that is
397 already present in the target :class:`_schema.MetaData` collection,
398 and
399 this :class:`_schema.Table`
400 specifies additional columns or other constructs
401 or flags that modify the table's state, an
402 error is raised. The purpose of these two mutually-exclusive flags
403 is to specify what action should be taken when a
404 :class:`_schema.Table`
405 is specified that matches an existing :class:`_schema.Table`,
406 yet specifies
407 additional constructs.
409 .. seealso::
411 :paramref:`_schema.Table.extend_existing`
413 :param listeners: A list of tuples of the form ``(<eventname>, <fn>)``
414 which will be passed to :func:`.event.listen` upon construction.
415 This alternate hook to :func:`.event.listen` allows the establishment
416 of a listener function specific to this :class:`_schema.Table` before
417 the "autoload" process begins. Historically this has been intended
418 for use with the :meth:`.DDLEvents.column_reflect` event, however
419 note that this event hook may now be associated with the
420 :class:`_schema.MetaData` object directly::
422 def listen_for_reflect(table, column_info):
423 "handle the column reflection event"
424 # ...
426 t = Table(
427 'sometable',
428 autoload_with=engine,
429 listeners=[
430 ('column_reflect', listen_for_reflect)
431 ])
433 .. seealso::
435 :meth:`_events.DDLEvents.column_reflect`
437 :param must_exist: When ``True``, indicates that this Table must already
438 be present in the given :class:`_schema.MetaData` collection, else
439 an exception is raised.
441 :param prefixes:
442 A list of strings to insert after CREATE in the CREATE TABLE
443 statement. They will be separated by spaces.
445 :param quote: Force quoting of this table's name on or off, corresponding
446 to ``True`` or ``False``. When left at its default of ``None``,
447 the column identifier will be quoted according to whether the name is
448 case sensitive (identifiers with at least one upper case character are
449 treated as case sensitive), or if it's a reserved word. This flag
450 is only needed to force quoting of a reserved word which is not known
451 by the SQLAlchemy dialect.
453 .. note:: setting this flag to ``False`` will not provide
454 case-insensitive behavior for table reflection; table reflection
455 will always search for a mixed-case name in a case sensitive
456 fashion. Case insensitive names are specified in SQLAlchemy only
457 by stating the name with all lower case characters.
459 :param quote_schema: same as 'quote' but applies to the schema identifier.
461 :param schema: The schema name for this table, which is required if
462 the table resides in a schema other than the default selected schema
463 for the engine's database connection. Defaults to ``None``.
465 If the owning :class:`_schema.MetaData` of this :class:`_schema.Table`
466 specifies its
467 own :paramref:`_schema.MetaData.schema` parameter,
468 then that schema name will
469 be applied to this :class:`_schema.Table`
470 if the schema parameter here is set
471 to ``None``. To set a blank schema name on a :class:`_schema.Table`
472 that
473 would otherwise use the schema set on the owning
474 :class:`_schema.MetaData`,
475 specify the special symbol :attr:`.BLANK_SCHEMA`.
477 .. versionadded:: 1.0.14 Added the :attr:`.BLANK_SCHEMA` symbol to
478 allow a :class:`_schema.Table`
479 to have a blank schema name even when the
480 parent :class:`_schema.MetaData` specifies
481 :paramref:`_schema.MetaData.schema`.
483 The quoting rules for the schema name are the same as those for the
484 ``name`` parameter, in that quoting is applied for reserved words or
485 case-sensitive names; to enable unconditional quoting for the schema
486 name, specify the flag ``quote_schema=True`` to the constructor, or use
487 the :class:`.quoted_name` construct to specify the name.
489 :param comment: Optional string that will render an SQL comment on table
490 creation.
492 .. versionadded:: 1.2 Added the :paramref:`_schema.Table.comment`
493 parameter
494 to :class:`_schema.Table`.
496 :param \**kw: Additional keyword arguments not mentioned above are
497 dialect specific, and passed in the form ``<dialectname>_<argname>``.
498 See the documentation regarding an individual dialect at
499 :ref:`dialect_toplevel` for detail on documented arguments.
501 """
503 __visit_name__ = "table"
505 constraints = None
506 """A collection of all :class:`_schema.Constraint` objects associated with
507 this :class:`_schema.Table`.
509 Includes :class:`_schema.PrimaryKeyConstraint`,
510 :class:`_schema.ForeignKeyConstraint`, :class:`_schema.UniqueConstraint`,
511 :class:`_schema.CheckConstraint`. A separate collection
512 :attr:`_schema.Table.foreign_key_constraints` refers to the collection
513 of all :class:`_schema.ForeignKeyConstraint` objects, and the
514 :attr:`_schema.Table.primary_key` attribute refers to the single
515 :class:`_schema.PrimaryKeyConstraint` associated with the
516 :class:`_schema.Table`.
518 .. seealso::
520 :attr:`_schema.Table.constraints`
522 :attr:`_schema.Table.primary_key`
524 :attr:`_schema.Table.foreign_key_constraints`
526 :attr:`_schema.Table.indexes`
528 :class:`_reflection.Inspector`
531 """
533 indexes = None
534 """A collection of all :class:`_schema.Index` objects associated with this
535 :class:`_schema.Table`.
537 .. seealso::
539 :meth:`_reflection.Inspector.get_indexes`
541 """
543 _traverse_internals = TableClause._traverse_internals + [
544 ("schema", InternalTraversal.dp_string)
545 ]
547 _is_table = True
549 def _gen_cache_key(self, anon_map, bindparams):
550 if self._annotations:
551 return (self,) + self._annotations_cache_key
552 else:
553 return (self,)
555 @util.deprecated_params(
556 mustexist=(
557 "1.4",
558 "Deprecated alias of :paramref:`_schema.Table.must_exist`",
559 ),
560 autoload=(
561 "2.0",
562 "The autoload parameter is deprecated and will be removed in "
563 "version 2.0. Please use the "
564 "autoload_with parameter, passing an engine or connection.",
565 ),
566 )
567 def __new__(cls, *args, **kw):
568 if not args and not kw:
569 # python3k pickle seems to call this
570 return object.__new__(cls)
572 try:
573 name, metadata, args = args[0], args[1], args[2:]
574 except IndexError:
575 raise TypeError(
576 "Table() takes at least two positional-only "
577 "arguments 'name' and 'metadata'"
578 )
580 schema = kw.get("schema", None)
581 if schema is None:
582 schema = metadata.schema
583 elif schema is BLANK_SCHEMA:
584 schema = None
585 keep_existing = kw.get("keep_existing", False)
586 extend_existing = kw.get("extend_existing", False)
588 if keep_existing and extend_existing:
589 msg = "keep_existing and extend_existing are mutually exclusive."
590 raise exc.ArgumentError(msg)
592 must_exist = kw.pop("must_exist", kw.pop("mustexist", False))
593 key = _get_table_key(name, schema)
594 if key in metadata.tables:
595 if not keep_existing and not extend_existing and bool(args):
596 raise exc.InvalidRequestError(
597 "Table '%s' is already defined for this MetaData "
598 "instance. Specify 'extend_existing=True' "
599 "to redefine "
600 "options and columns on an "
601 "existing Table object." % key
602 )
603 table = metadata.tables[key]
604 if extend_existing:
605 table._init_existing(*args, **kw)
606 return table
607 else:
608 if must_exist:
609 raise exc.InvalidRequestError("Table '%s' not defined" % (key))
610 table = object.__new__(cls)
611 table.dispatch.before_parent_attach(table, metadata)
612 metadata._add_table(name, schema, table)
613 try:
614 table._init(name, metadata, *args, **kw)
615 table.dispatch.after_parent_attach(table, metadata)
616 return table
617 except Exception:
618 with util.safe_reraise():
619 metadata._remove_table(name, schema)
621 def __init__(self, *args, **kw):
622 """Constructor for :class:`_schema.Table`.
624 This method is a no-op. See the top-level
625 documentation for :class:`_schema.Table`
626 for constructor arguments.
628 """
629 # __init__ is overridden to prevent __new__ from
630 # calling the superclass constructor.
632 def _init(self, name, metadata, *args, **kwargs):
633 super(Table, self).__init__(
634 quoted_name(name, kwargs.pop("quote", None))
635 )
636 self.metadata = metadata
638 self.schema = kwargs.pop("schema", None)
639 if self.schema is None:
640 self.schema = metadata.schema
641 elif self.schema is BLANK_SCHEMA:
642 self.schema = None
643 else:
644 quote_schema = kwargs.pop("quote_schema", None)
645 self.schema = quoted_name(self.schema, quote_schema)
647 self.indexes = set()
648 self.constraints = set()
649 PrimaryKeyConstraint(
650 _implicit_generated=True
651 )._set_parent_with_dispatch(self)
652 self.foreign_keys = set()
653 self._extra_dependencies = set()
654 if self.schema is not None:
655 self.fullname = "%s.%s" % (self.schema, self.name)
656 else:
657 self.fullname = self.name
659 autoload_with = kwargs.pop("autoload_with", None)
660 autoload = kwargs.pop("autoload", autoload_with is not None)
661 # this argument is only used with _init_existing()
662 kwargs.pop("autoload_replace", True)
663 keep_existing = kwargs.pop("keep_existing", False)
664 extend_existing = kwargs.pop("extend_existing", False)
665 _extend_on = kwargs.pop("_extend_on", None)
667 resolve_fks = kwargs.pop("resolve_fks", True)
668 include_columns = kwargs.pop("include_columns", None)
670 self.implicit_returning = kwargs.pop("implicit_returning", True)
672 self.comment = kwargs.pop("comment", None)
674 if "info" in kwargs:
675 self.info = kwargs.pop("info")
676 if "listeners" in kwargs:
677 listeners = kwargs.pop("listeners")
678 for evt, fn in listeners:
679 event.listen(self, evt, fn)
681 self._prefixes = kwargs.pop("prefixes", None) or []
683 self._extra_kwargs(**kwargs)
685 # load column definitions from the database if 'autoload' is defined
686 # we do it after the table is in the singleton dictionary to support
687 # circular foreign keys
688 if autoload:
689 self._autoload(
690 metadata,
691 autoload_with,
692 include_columns,
693 _extend_on=_extend_on,
694 resolve_fks=resolve_fks,
695 )
697 # initialize all the column, etc. objects. done after reflection to
698 # allow user-overrides
700 self._init_items(
701 *args,
702 allow_replacements=extend_existing or keep_existing or autoload
703 )
705 def _autoload(
706 self,
707 metadata,
708 autoload_with,
709 include_columns,
710 exclude_columns=(),
711 resolve_fks=True,
712 _extend_on=None,
713 ):
714 if autoload_with is None:
715 autoload_with = _bind_or_error(
716 metadata,
717 msg="No engine is bound to this Table's MetaData. "
718 "Pass an engine to the Table via "
719 "autoload_with=<someengine_or_connection>",
720 )
722 insp = inspection.inspect(autoload_with)
723 with insp._inspection_context() as conn_insp:
724 conn_insp.reflect_table(
725 self,
726 include_columns,
727 exclude_columns,
728 resolve_fks,
729 _extend_on=_extend_on,
730 )
732 @property
733 def _sorted_constraints(self):
734 """Return the set of constraints as a list, sorted by creation
735 order.
737 """
738 return sorted(self.constraints, key=lambda c: c._creation_order)
740 @property
741 def foreign_key_constraints(self):
742 """:class:`_schema.ForeignKeyConstraint` objects referred to by this
743 :class:`_schema.Table`.
745 This list is produced from the collection of
746 :class:`_schema.ForeignKey`
747 objects currently associated.
750 .. seealso::
752 :attr:`_schema.Table.constraints`
754 :attr:`_schema.Table.foreign_keys`
756 :attr:`_schema.Table.indexes`
758 """
759 return set(fkc.constraint for fkc in self.foreign_keys)
761 def _init_existing(self, *args, **kwargs):
762 autoload_with = kwargs.pop("autoload_with", None)
763 autoload = kwargs.pop("autoload", autoload_with is not None)
764 autoload_replace = kwargs.pop("autoload_replace", True)
765 schema = kwargs.pop("schema", None)
766 _extend_on = kwargs.pop("_extend_on", None)
767 # these arguments are only used with _init()
768 kwargs.pop("extend_existing", False)
769 kwargs.pop("keep_existing", False)
771 if schema and schema != self.schema:
772 raise exc.ArgumentError(
773 "Can't change schema of existing table from '%s' to '%s'",
774 (self.schema, schema),
775 )
777 include_columns = kwargs.pop("include_columns", None)
778 if include_columns is not None:
779 for c in self.c:
780 if c.name not in include_columns:
781 self._columns.remove(c)
783 resolve_fks = kwargs.pop("resolve_fks", True)
785 for key in ("quote", "quote_schema"):
786 if key in kwargs:
787 raise exc.ArgumentError(
788 "Can't redefine 'quote' or 'quote_schema' arguments"
789 )
791 # update `self` with these kwargs, if provided
792 self.comment = kwargs.pop("comment", self.comment)
793 self.implicit_returning = kwargs.pop(
794 "implicit_returning", self.implicit_returning
795 )
796 self.info = kwargs.pop("info", self.info)
798 if autoload:
799 if not autoload_replace:
800 # don't replace columns already present.
801 # we'd like to do this for constraints also however we don't
802 # have simple de-duping for unnamed constraints.
803 exclude_columns = [c.name for c in self.c]
804 else:
805 exclude_columns = ()
806 self._autoload(
807 self.metadata,
808 autoload_with,
809 include_columns,
810 exclude_columns,
811 resolve_fks,
812 _extend_on=_extend_on,
813 )
815 self._extra_kwargs(**kwargs)
816 self._init_items(*args)
818 def _extra_kwargs(self, **kwargs):
819 self._validate_dialect_kwargs(kwargs)
821 def _init_collections(self):
822 pass
824 def _reset_exported(self):
825 pass
827 @property
828 def _autoincrement_column(self):
829 return self.primary_key._autoincrement_column
831 @property
832 def key(self):
833 """Return the 'key' for this :class:`_schema.Table`.
835 This value is used as the dictionary key within the
836 :attr:`_schema.MetaData.tables` collection. It is typically the same
837 as that of :attr:`_schema.Table.name` for a table with no
838 :attr:`_schema.Table.schema`
839 set; otherwise it is typically of the form
840 ``schemaname.tablename``.
842 """
843 return _get_table_key(self.name, self.schema)
845 def __repr__(self):
846 return "Table(%s)" % ", ".join(
847 [repr(self.name)]
848 + [repr(self.metadata)]
849 + [repr(x) for x in self.columns]
850 + ["%s=%s" % (k, repr(getattr(self, k))) for k in ["schema"]]
851 )
853 def __str__(self):
854 return _get_table_key(self.description, self.schema)
856 @property
857 def bind(self):
858 """Return the connectable associated with this Table."""
860 return self.metadata and self.metadata.bind or None
862 def add_is_dependent_on(self, table):
863 """Add a 'dependency' for this Table.
865 This is another Table object which must be created
866 first before this one can, or dropped after this one.
868 Usually, dependencies between tables are determined via
869 ForeignKey objects. However, for other situations that
870 create dependencies outside of foreign keys (rules, inheriting),
871 this method can manually establish such a link.
873 """
874 self._extra_dependencies.add(table)
876 def append_column(self, column, replace_existing=False):
877 """Append a :class:`_schema.Column` to this :class:`_schema.Table`.
879 The "key" of the newly added :class:`_schema.Column`, i.e. the
880 value of its ``.key`` attribute, will then be available
881 in the ``.c`` collection of this :class:`_schema.Table`, and the
882 column definition will be included in any CREATE TABLE, SELECT,
883 UPDATE, etc. statements generated from this :class:`_schema.Table`
884 construct.
886 Note that this does **not** change the definition of the table
887 as it exists within any underlying database, assuming that
888 table has already been created in the database. Relational
889 databases support the addition of columns to existing tables
890 using the SQL ALTER command, which would need to be
891 emitted for an already-existing table that doesn't contain
892 the newly added column.
894 :param replace_existing: When ``True``, allows replacing existing
895 columns. When ``False``, the default, an warning will be raised
896 if a column with the same ``.key`` already exists. A future
897 version of sqlalchemy will instead rise a warning.
899 .. versionadded:: 1.4.0
900 """
902 column._set_parent_with_dispatch(
903 self, allow_replacements=replace_existing
904 )
906 def append_constraint(self, constraint):
907 """Append a :class:`_schema.Constraint` to this
908 :class:`_schema.Table`.
910 This has the effect of the constraint being included in any
911 future CREATE TABLE statement, assuming specific DDL creation
912 events have not been associated with the given
913 :class:`_schema.Constraint` object.
915 Note that this does **not** produce the constraint within the
916 relational database automatically, for a table that already exists
917 in the database. To add a constraint to an
918 existing relational database table, the SQL ALTER command must
919 be used. SQLAlchemy also provides the
920 :class:`.AddConstraint` construct which can produce this SQL when
921 invoked as an executable clause.
923 """
925 constraint._set_parent_with_dispatch(self)
927 def _set_parent(self, metadata, **kw):
928 metadata._add_table(self.name, self.schema, self)
929 self.metadata = metadata
931 @util.deprecated(
932 "1.4",
933 "The :meth:`_schema.Table.exists` method is deprecated and will be "
934 "removed in a future release. Please refer to "
935 ":meth:`_reflection.Inspector.has_table`.",
936 )
937 def exists(self, bind=None):
938 """Return True if this table exists."""
940 if bind is None:
941 bind = _bind_or_error(self)
943 insp = inspection.inspect(bind)
944 return insp.has_table(self.name, schema=self.schema)
946 def create(self, bind=None, checkfirst=False):
947 """Issue a ``CREATE`` statement for this
948 :class:`_schema.Table`, using the given :class:`.Connectable`
949 for connectivity.
951 .. note:: the "bind" argument will be required in
952 SQLAlchemy 2.0.
954 .. seealso::
956 :meth:`_schema.MetaData.create_all`.
958 """
960 if bind is None:
961 bind = _bind_or_error(self)
962 bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
964 def drop(self, bind=None, checkfirst=False):
965 """Issue a ``DROP`` statement for this
966 :class:`_schema.Table`, using the given :class:`.Connectable`
967 for connectivity.
969 .. note:: the "bind" argument will be required in
970 SQLAlchemy 2.0.
972 .. seealso::
974 :meth:`_schema.MetaData.drop_all`.
976 """
977 if bind is None:
978 bind = _bind_or_error(self)
979 bind._run_ddl_visitor(ddl.SchemaDropper, self, checkfirst=checkfirst)
981 @util.deprecated(
982 "1.4",
983 ":meth:`_schema.Table.tometadata` is renamed to "
984 ":meth:`_schema.Table.to_metadata`",
985 )
986 def tometadata(
987 self,
988 metadata,
989 schema=RETAIN_SCHEMA,
990 referred_schema_fn=None,
991 name=None,
992 ):
993 """Return a copy of this :class:`_schema.Table`
994 associated with a different
995 :class:`_schema.MetaData`.
997 See :meth:`_schema.Table.to_metadata` for a full description.
999 """
1000 return self.to_metadata(
1001 metadata,
1002 schema=schema,
1003 referred_schema_fn=referred_schema_fn,
1004 name=name,
1005 )
1007 def to_metadata(
1008 self,
1009 metadata,
1010 schema=RETAIN_SCHEMA,
1011 referred_schema_fn=None,
1012 name=None,
1013 ):
1014 """Return a copy of this :class:`_schema.Table` associated with a
1015 different :class:`_schema.MetaData`.
1017 E.g.::
1019 m1 = MetaData()
1021 user = Table('user', m1, Column('id', Integer, primary_key=True))
1023 m2 = MetaData()
1024 user_copy = user.to_metadata(m2)
1026 .. versionchanged:: 1.4 The :meth:`_schema.Table.to_metadata` function
1027 was renamed from :meth:`_schema.Table.tometadata`.
1030 :param metadata: Target :class:`_schema.MetaData` object,
1031 into which the
1032 new :class:`_schema.Table` object will be created.
1034 :param schema: optional string name indicating the target schema.
1035 Defaults to the special symbol :attr:`.RETAIN_SCHEMA` which indicates
1036 that no change to the schema name should be made in the new
1037 :class:`_schema.Table`. If set to a string name, the new
1038 :class:`_schema.Table`
1039 will have this new name as the ``.schema``. If set to ``None``, the
1040 schema will be set to that of the schema set on the target
1041 :class:`_schema.MetaData`, which is typically ``None`` as well,
1042 unless
1043 set explicitly::
1045 m2 = MetaData(schema='newschema')
1047 # user_copy_one will have "newschema" as the schema name
1048 user_copy_one = user.to_metadata(m2, schema=None)
1050 m3 = MetaData() # schema defaults to None
1052 # user_copy_two will have None as the schema name
1053 user_copy_two = user.to_metadata(m3, schema=None)
1055 :param referred_schema_fn: optional callable which can be supplied
1056 in order to provide for the schema name that should be assigned
1057 to the referenced table of a :class:`_schema.ForeignKeyConstraint`.
1058 The callable accepts this parent :class:`_schema.Table`, the
1059 target schema that we are changing to, the
1060 :class:`_schema.ForeignKeyConstraint` object, and the existing
1061 "target schema" of that constraint. The function should return the
1062 string schema name that should be applied. To reset the schema
1063 to "none", return the symbol :data:`.BLANK_SCHEMA`. To effect no
1064 change, return ``None`` or :data:`.RETAIN_SCHEMA`.
1066 .. versionchanged:: 1.4.33 The ``referred_schema_fn`` function
1067 may return the :data:`.BLANK_SCHEMA` or :data:`.RETAIN_SCHEMA`
1068 symbols.
1070 E.g.::
1072 def referred_schema_fn(table, to_schema,
1073 constraint, referred_schema):
1074 if referred_schema == 'base_tables':
1075 return referred_schema
1076 else:
1077 return to_schema
1079 new_table = table.to_metadata(m2, schema="alt_schema",
1080 referred_schema_fn=referred_schema_fn)
1082 .. versionadded:: 0.9.2
1084 :param name: optional string name indicating the target table name.
1085 If not specified or None, the table name is retained. This allows
1086 a :class:`_schema.Table` to be copied to the same
1087 :class:`_schema.MetaData` target
1088 with a new name.
1090 .. versionadded:: 1.0.0
1092 """
1093 if name is None:
1094 name = self.name
1095 if schema is RETAIN_SCHEMA:
1096 schema = self.schema
1097 elif schema is None:
1098 schema = metadata.schema
1099 key = _get_table_key(name, schema)
1100 if key in metadata.tables:
1101 util.warn(
1102 "Table '%s' already exists within the given "
1103 "MetaData - not copying." % self.description
1104 )
1105 return metadata.tables[key]
1107 args = []
1108 for c in self.columns:
1109 args.append(c._copy(schema=schema))
1110 table = Table(
1111 name,
1112 metadata,
1113 schema=schema,
1114 comment=self.comment,
1115 *args,
1116 **self.kwargs
1117 )
1118 for c in self.constraints:
1119 if isinstance(c, ForeignKeyConstraint):
1120 referred_schema = c._referred_schema
1121 if referred_schema_fn:
1122 fk_constraint_schema = referred_schema_fn(
1123 self, schema, c, referred_schema
1124 )
1125 else:
1126 fk_constraint_schema = (
1127 schema if referred_schema == self.schema else None
1128 )
1129 table.append_constraint(
1130 c._copy(schema=fk_constraint_schema, target_table=table)
1131 )
1132 elif not c._type_bound:
1133 # skip unique constraints that would be generated
1134 # by the 'unique' flag on Column
1135 if c._column_flag:
1136 continue
1138 table.append_constraint(
1139 c._copy(schema=schema, target_table=table)
1140 )
1141 for index in self.indexes:
1142 # skip indexes that would be generated
1143 # by the 'index' flag on Column
1144 if index._column_flag:
1145 continue
1146 Index(
1147 index.name,
1148 unique=index.unique,
1149 *[
1150 _copy_expression(expr, self, table)
1151 for expr in index.expressions
1152 ],
1153 _table=table,
1154 **index.kwargs
1155 )
1156 return self._schema_item_copy(table)
1159class Column(DialectKWArgs, SchemaItem, ColumnClause):
1160 """Represents a column in a database table."""
1162 __visit_name__ = "column"
1164 inherit_cache = True
1166 def __init__(self, *args, **kwargs):
1167 r"""
1168 Construct a new ``Column`` object.
1170 :param name: The name of this column as represented in the database.
1171 This argument may be the first positional argument, or specified
1172 via keyword.
1174 Names which contain no upper case characters
1175 will be treated as case insensitive names, and will not be quoted
1176 unless they are a reserved word. Names with any number of upper
1177 case characters will be quoted and sent exactly. Note that this
1178 behavior applies even for databases which standardize upper
1179 case names as case insensitive such as Oracle.
1181 The name field may be omitted at construction time and applied
1182 later, at any time before the Column is associated with a
1183 :class:`_schema.Table`. This is to support convenient
1184 usage within the :mod:`~sqlalchemy.ext.declarative` extension.
1186 :param type\_: The column's type, indicated using an instance which
1187 subclasses :class:`~sqlalchemy.types.TypeEngine`. If no arguments
1188 are required for the type, the class of the type can be sent
1189 as well, e.g.::
1191 # use a type with arguments
1192 Column('data', String(50))
1194 # use no arguments
1195 Column('level', Integer)
1197 The ``type`` argument may be the second positional argument
1198 or specified by keyword.
1200 If the ``type`` is ``None`` or is omitted, it will first default to
1201 the special type :class:`.NullType`. If and when this
1202 :class:`_schema.Column` is made to refer to another column using
1203 :class:`_schema.ForeignKey` and/or
1204 :class:`_schema.ForeignKeyConstraint`, the type
1205 of the remote-referenced column will be copied to this column as
1206 well, at the moment that the foreign key is resolved against that
1207 remote :class:`_schema.Column` object.
1209 .. versionchanged:: 0.9.0
1210 Support for propagation of type to a :class:`_schema.Column`
1211 from its
1212 :class:`_schema.ForeignKey` object has been improved and should be
1213 more reliable and timely.
1215 :param \*args: Additional positional arguments include various
1216 :class:`.SchemaItem` derived constructs which will be applied
1217 as options to the column. These include instances of
1218 :class:`.Constraint`, :class:`_schema.ForeignKey`,
1219 :class:`.ColumnDefault`, :class:`.Sequence`, :class:`.Computed`
1220 :class:`.Identity`. In some cases an
1221 equivalent keyword argument is available such as ``server_default``,
1222 ``default`` and ``unique``.
1224 :param autoincrement: Set up "auto increment" semantics for an
1225 **integer primary key column with no foreign key dependencies**
1226 (see later in this docstring for a more specific definition).
1227 This may influence the :term:`DDL` that will be emitted for
1228 this column during a table create, as well as how the column
1229 will be considered when INSERT statements are compiled and
1230 executed.
1232 The default value is the string ``"auto"``,
1233 which indicates that a single-column (i.e. non-composite) primary key
1234 that is of an INTEGER type with no other client-side or server-side
1235 default constructs indicated should receive auto increment semantics
1236 automatically. Other values include ``True`` (force this column to
1237 have auto-increment semantics for a :term:`composite primary key` as
1238 well), ``False`` (this column should never have auto-increment
1239 semantics), and the string ``"ignore_fk"`` (special-case for foreign
1240 key columns, see below).
1242 The term "auto increment semantics" refers both to the kind of DDL
1243 that will be emitted for the column within a CREATE TABLE statement,
1244 when methods such as :meth:`.MetaData.create_all` and
1245 :meth:`.Table.create` are invoked, as well as how the column will be
1246 considered when an INSERT statement is compiled and emitted to the
1247 database:
1249 * **DDL rendering** (i.e. :meth:`.MetaData.create_all`,
1250 :meth:`.Table.create`): When used on a :class:`.Column` that has
1251 no other
1252 default-generating construct associated with it (such as a
1253 :class:`.Sequence` or :class:`.Identity` construct), the parameter
1254 will imply that database-specific keywords such as PostgreSQL
1255 ``SERIAL``, MySQL ``AUTO_INCREMENT``, or ``IDENTITY`` on SQL Server
1256 should also be rendered. Not every database backend has an
1257 "implied" default generator available; for example the Oracle
1258 backend always needs an explicit construct such as
1259 :class:`.Identity` to be included with a :class:`.Column` in order
1260 for the DDL rendered to include auto-generating constructs to also
1261 be produced in the database.
1263 * **INSERT semantics** (i.e. when a :func:`_sql.insert` construct is
1264 compiled into a SQL string and is then executed on a database using
1265 :meth:`_engine.Connection.execute` or equivalent): A single-row
1266 INSERT statement will be known to produce a new integer primary key
1267 value automatically for this column, which will be accessible
1268 after the statement is invoked via the
1269 :attr:`.CursorResult.inserted_primary_key` attribute upon the
1270 :class:`_result.Result` object. This also applies towards use of the
1271 ORM when ORM-mapped objects are persisted to the database,
1272 indicating that a new integer primary key will be available to
1273 become part of the :term:`identity key` for that object. This
1274 behavior takes place regardless of what DDL constructs are
1275 associated with the :class:`_schema.Column` and is independent
1276 of the "DDL Rendering" behavior discussed in the previous note
1277 above.
1279 The parameter may be set to ``True`` to indicate that a column which
1280 is part of a composite (i.e. multi-column) primary key should
1281 have autoincrement semantics, though note that only one column
1282 within a primary key may have this setting. It can also
1283 be set to ``True`` to indicate autoincrement semantics on a
1284 column that has a client-side or server-side default configured,
1285 however note that not all dialects can accommodate all styles
1286 of default as an "autoincrement". It can also be
1287 set to ``False`` on a single-column primary key that has a
1288 datatype of INTEGER in order to disable auto increment semantics
1289 for that column.
1291 .. versionchanged:: 1.1 The autoincrement flag now defaults to
1292 ``"auto"`` which indicates autoincrement semantics by default
1293 for single-column integer primary keys only; for composite
1294 (multi-column) primary keys, autoincrement is never implicitly
1295 enabled; as always, ``autoincrement=True`` will allow for
1296 at most one of those columns to be an "autoincrement" column.
1297 ``autoincrement=True`` may also be set on a
1298 :class:`_schema.Column`
1299 that has an explicit client-side or server-side default,
1300 subject to limitations of the backend database and dialect.
1302 The setting *only* has an effect for columns which are:
1304 * Integer derived (i.e. INT, SMALLINT, BIGINT).
1306 * Part of the primary key
1308 * Not referring to another column via :class:`_schema.ForeignKey`,
1309 unless
1310 the value is specified as ``'ignore_fk'``::
1312 # turn on autoincrement for this column despite
1313 # the ForeignKey()
1314 Column('id', ForeignKey('other.id'),
1315 primary_key=True, autoincrement='ignore_fk')
1317 It is typically not desirable to have "autoincrement" enabled on a
1318 column that refers to another via foreign key, as such a column is
1319 required to refer to a value that originates from elsewhere.
1321 The setting has these effects on columns that meet the
1322 above criteria:
1324 * DDL issued for the column, if the column does not already include
1325 a default generating construct supported by the backend such as
1326 :class:`.Identity`, will include database-specific
1327 keywords intended to signify this column as an
1328 "autoincrement" column for specific backends. Behavior for
1329 primary SQLAlchemy dialects includes:
1331 * AUTO INCREMENT on MySQL and MariaDB
1332 * SERIAL on PostgreSQL
1333 * IDENTITY on MS-SQL - this occurs even without the
1334 :class:`.Identity` construct as the
1335 :paramref:`.Column.autoincrement` parameter pre-dates this
1336 construct.
1337 * SQLite - SQLite integer primary key columns are implicitly
1338 "auto incrementing" and no additional keywords are rendered;
1339 to render the special SQLite keyword ``AUTOINCREMENT``
1340 is not included as this is unnecessary and not recommended
1341 by the database vendor. See the section
1342 :ref:`sqlite_autoincrement` for more background.
1343 * Oracle - The Oracle dialect has no default "autoincrement"
1344 feature available at this time, instead the :class:`.Identity`
1345 construct is recommended to achieve this (the :class:`.Sequence`
1346 construct may also be used).
1347 * Third-party dialects - consult those dialects' documentation
1348 for details on their specific behaviors.
1350 * When a single-row :func:`_sql.insert` construct is compiled and
1351 executed, which does not set the :meth:`_sql.Insert.inline`
1352 modifier, newly generated primary key values for this column
1353 will be automatically retrieved upon statement execution
1354 using a method specific to the database driver in use:
1356 * MySQL, SQLite - calling upon ``cursor.lastrowid()``
1357 (see
1358 `https://www.python.org/dev/peps/pep-0249/#lastrowid
1359 <https://www.python.org/dev/peps/pep-0249/#lastrowid>`_)
1360 * PostgreSQL, SQL Server, Oracle - use RETURNING or an equivalent
1361 construct when rendering an INSERT statement, and then retrieving
1362 the newly generated primary key values after execution
1363 * PostgreSQL, Oracle for :class:`_schema.Table` objects that
1364 set :paramref:`_schema.Table.implicit_returning` to False -
1365 for a :class:`.Sequence` only, the :class:`.Sequence` is invoked
1366 explicitly before the INSERT statement takes place so that the
1367 newly generated primary key value is available to the client
1368 * SQL Server for :class:`_schema.Table` objects that
1369 set :paramref:`_schema.Table.implicit_returning` to False -
1370 the ``SELECT scope_identity()`` construct is used after the
1371 INSERT statement is invoked to retrieve the newly generated
1372 primary key value.
1373 * Third-party dialects - consult those dialects' documentation
1374 for details on their specific behaviors.
1376 * For multiple-row :func:`_sql.insert` constructs invoked with
1377 a list of parameters (i.e. "executemany" semantics), primary-key
1378 retrieving behaviors are generally disabled, however there may
1379 be special APIs that may be used to retrieve lists of new
1380 primary key values for an "executemany", such as the psycopg2
1381 "fast insertmany" feature. Such features are very new and
1382 may not yet be well covered in documentation.
1384 :param default: A scalar, Python callable, or
1385 :class:`_expression.ColumnElement` expression representing the
1386 *default value* for this column, which will be invoked upon insert
1387 if this column is otherwise not specified in the VALUES clause of
1388 the insert. This is a shortcut to using :class:`.ColumnDefault` as
1389 a positional argument; see that class for full detail on the
1390 structure of the argument.
1392 Contrast this argument to
1393 :paramref:`_schema.Column.server_default`
1394 which creates a default generator on the database side.
1396 .. seealso::
1398 :ref:`metadata_defaults_toplevel`
1400 :param doc: optional String that can be used by the ORM or similar
1401 to document attributes on the Python side. This attribute does
1402 **not** render SQL comments; use the
1403 :paramref:`_schema.Column.comment`
1404 parameter for this purpose.
1406 :param key: An optional string identifier which will identify this
1407 ``Column`` object on the :class:`_schema.Table`.
1408 When a key is provided,
1409 this is the only identifier referencing the ``Column`` within the
1410 application, including ORM attribute mapping; the ``name`` field
1411 is used only when rendering SQL.
1413 :param index: When ``True``, indicates that a :class:`_schema.Index`
1414 construct will be automatically generated for this
1415 :class:`_schema.Column`, which will result in a "CREATE INDEX"
1416 statement being emitted for the :class:`_schema.Table` when the DDL
1417 create operation is invoked.
1419 Using this flag is equivalent to making use of the
1420 :class:`_schema.Index` construct explicitly at the level of the
1421 :class:`_schema.Table` construct itself::
1423 Table(
1424 "some_table",
1425 metadata,
1426 Column("x", Integer),
1427 Index("ix_some_table_x", "x")
1428 )
1430 To add the :paramref:`_schema.Index.unique` flag to the
1431 :class:`_schema.Index`, set both the
1432 :paramref:`_schema.Column.unique` and
1433 :paramref:`_schema.Column.index` flags to True simultaneously,
1434 which will have the effect of rendering the "CREATE UNIQUE INDEX"
1435 DDL instruction instead of "CREATE INDEX".
1437 The name of the index is generated using the
1438 :ref:`default naming convention <constraint_default_naming_convention>`
1439 which for the :class:`_schema.Index` construct is of the form
1440 ``ix_<tablename>_<columnname>``.
1442 As this flag is intended only as a convenience for the common case
1443 of adding a single-column, default configured index to a table
1444 definition, explicit use of the :class:`_schema.Index` construct
1445 should be preferred for most use cases, including composite indexes
1446 that encompass more than one column, indexes with SQL expressions
1447 or ordering, backend-specific index configuration options, and
1448 indexes that use a specific name.
1450 .. note:: the :attr:`_schema.Column.index` attribute on
1451 :class:`_schema.Column`
1452 **does not indicate** if this column is indexed or not, only
1453 if this flag was explicitly set here. To view indexes on
1454 a column, view the :attr:`_schema.Table.indexes` collection
1455 or use :meth:`_reflection.Inspector.get_indexes`.
1457 .. seealso::
1459 :ref:`schema_indexes`
1461 :ref:`constraint_naming_conventions`
1463 :paramref:`_schema.Column.unique`
1465 :param info: Optional data dictionary which will be populated into the
1466 :attr:`.SchemaItem.info` attribute of this object.
1468 :param nullable: When set to ``False``, will cause the "NOT NULL"
1469 phrase to be added when generating DDL for the column. When
1470 ``True``, will normally generate nothing (in SQL this defaults to
1471 "NULL"), except in some very specific backend-specific edge cases
1472 where "NULL" may render explicitly.
1473 Defaults to ``True`` unless :paramref:`_schema.Column.primary_key`
1474 is also ``True`` or the column specifies a :class:`_sql.Identity`,
1475 in which case it defaults to ``False``.
1476 This parameter is only used when issuing CREATE TABLE statements.
1478 .. note::
1480 When the column specifies a :class:`_sql.Identity` this
1481 parameter is in general ignored by the DDL compiler. The
1482 PostgreSQL database allows nullable identity column by
1483 setting this parameter to ``True`` explicitly.
1485 :param onupdate: A scalar, Python callable, or
1486 :class:`~sqlalchemy.sql.expression.ClauseElement` representing a
1487 default value to be applied to the column within UPDATE
1488 statements, which will be invoked upon update if this column is not
1489 present in the SET clause of the update. This is a shortcut to
1490 using :class:`.ColumnDefault` as a positional argument with
1491 ``for_update=True``.
1493 .. seealso::
1495 :ref:`metadata_defaults` - complete discussion of onupdate
1497 :param primary_key: If ``True``, marks this column as a primary key
1498 column. Multiple columns can have this flag set to specify
1499 composite primary keys. As an alternative, the primary key of a
1500 :class:`_schema.Table` can be specified via an explicit
1501 :class:`.PrimaryKeyConstraint` object.
1503 :param server_default: A :class:`.FetchedValue` instance, str, Unicode
1504 or :func:`~sqlalchemy.sql.expression.text` construct representing
1505 the DDL DEFAULT value for the column.
1507 String types will be emitted as-is, surrounded by single quotes::
1509 Column('x', Text, server_default="val")
1511 x TEXT DEFAULT 'val'
1513 A :func:`~sqlalchemy.sql.expression.text` expression will be
1514 rendered as-is, without quotes::
1516 Column('y', DateTime, server_default=text('NOW()'))
1518 y DATETIME DEFAULT NOW()
1520 Strings and text() will be converted into a
1521 :class:`.DefaultClause` object upon initialization.
1523 This parameter can also accept complex combinations of contextually
1524 valid SQLAlchemy expressions or constructs::
1526 from sqlalchemy import create_engine
1527 from sqlalchemy import Table, Column, MetaData, ARRAY, Text
1528 from sqlalchemy.dialects.postgresql import array
1530 engine = create_engine(
1531 'postgresql://scott:tiger@localhost/mydatabase'
1532 )
1533 metadata_obj = MetaData()
1534 tbl = Table(
1535 "foo",
1536 metadata_obj,
1537 Column("bar",
1538 ARRAY(Text),
1539 server_default=array(["biz", "bang", "bash"])
1540 )
1541 )
1542 metadata_obj.create_all(engine)
1544 The above results in a table created with the following SQL::
1546 CREATE TABLE foo (
1547 bar TEXT[] DEFAULT ARRAY['biz', 'bang', 'bash']
1548 )
1550 Use :class:`.FetchedValue` to indicate that an already-existing
1551 column will generate a default value on the database side which
1552 will be available to SQLAlchemy for post-fetch after inserts. This
1553 construct does not specify any DDL and the implementation is left
1554 to the database, such as via a trigger.
1556 .. seealso::
1558 :ref:`server_defaults` - complete discussion of server side
1559 defaults
1561 :param server_onupdate: A :class:`.FetchedValue` instance
1562 representing a database-side default generation function,
1563 such as a trigger. This
1564 indicates to SQLAlchemy that a newly generated value will be
1565 available after updates. This construct does not actually
1566 implement any kind of generation function within the database,
1567 which instead must be specified separately.
1570 .. warning:: This directive **does not** currently produce MySQL's
1571 "ON UPDATE CURRENT_TIMESTAMP()" clause. See
1572 :ref:`mysql_timestamp_onupdate` for background on how to
1573 produce this clause.
1575 .. seealso::
1577 :ref:`triggered_columns`
1579 :param quote: Force quoting of this column's name on or off,
1580 corresponding to ``True`` or ``False``. When left at its default
1581 of ``None``, the column identifier will be quoted according to
1582 whether the name is case sensitive (identifiers with at least one
1583 upper case character are treated as case sensitive), or if it's a
1584 reserved word. This flag is only needed to force quoting of a
1585 reserved word which is not known by the SQLAlchemy dialect.
1587 :param unique: When ``True``, and the :paramref:`_schema.Column.index`
1588 parameter is left at its default value of ``False``,
1589 indicates that a :class:`_schema.UniqueConstraint`
1590 construct will be automatically generated for this
1591 :class:`_schema.Column`,
1592 which will result in a "UNIQUE CONSTRAINT" clause referring
1593 to this column being included
1594 in the ``CREATE TABLE`` statement emitted, when the DDL create
1595 operation for the :class:`_schema.Table` object is invoked.
1597 When this flag is ``True`` while the
1598 :paramref:`_schema.Column.index` parameter is simultaneously
1599 set to ``True``, the effect instead is that a
1600 :class:`_schema.Index` construct which includes the
1601 :paramref:`_schema.Index.unique` parameter set to ``True``
1602 is generated. See the documentation for
1603 :paramref:`_schema.Column.index` for additional detail.
1605 Using this flag is equivalent to making use of the
1606 :class:`_schema.UniqueConstraint` construct explicitly at the
1607 level of the :class:`_schema.Table` construct itself::
1609 Table(
1610 "some_table",
1611 metadata,
1612 Column("x", Integer),
1613 UniqueConstraint("x")
1614 )
1616 The :paramref:`_schema.UniqueConstraint.name` parameter
1617 of the unique constraint object is left at its default value
1618 of ``None``; in the absence of a :ref:`naming convention <constraint_naming_conventions>`
1619 for the enclosing :class:`_schema.MetaData`, the UNIQUE CONSTRAINT
1620 construct will be emitted as unnamed, which typically invokes
1621 a database-specific naming convention to take place.
1623 As this flag is intended only as a convenience for the common case
1624 of adding a single-column, default configured unique constraint to a table
1625 definition, explicit use of the :class:`_schema.UniqueConstraint` construct
1626 should be preferred for most use cases, including composite constraints
1627 that encompass more than one column, backend-specific index configuration options, and
1628 constraints that use a specific name.
1630 .. note:: the :attr:`_schema.Column.unique` attribute on
1631 :class:`_schema.Column`
1632 **does not indicate** if this column has a unique constraint or
1633 not, only if this flag was explicitly set here. To view
1634 indexes and unique constraints that may involve this column,
1635 view the
1636 :attr:`_schema.Table.indexes` and/or
1637 :attr:`_schema.Table.constraints` collections or use
1638 :meth:`_reflection.Inspector.get_indexes` and/or
1639 :meth:`_reflection.Inspector.get_unique_constraints`
1641 .. seealso::
1643 :ref:`schema_unique_constraint`
1645 :ref:`constraint_naming_conventions`
1647 :paramref:`_schema.Column.index`
1649 :param system: When ``True``, indicates this is a "system" column,
1650 that is a column which is automatically made available by the
1651 database, and should not be included in the columns list for a
1652 ``CREATE TABLE`` statement.
1654 For more elaborate scenarios where columns should be
1655 conditionally rendered differently on different backends,
1656 consider custom compilation rules for :class:`.CreateColumn`.
1658 :param comment: Optional string that will render an SQL comment on
1659 table creation.
1661 .. versionadded:: 1.2 Added the
1662 :paramref:`_schema.Column.comment`
1663 parameter to :class:`_schema.Column`.
1666 """ # noqa: E501, RST201, RST202
1668 name = kwargs.pop("name", None)
1669 type_ = kwargs.pop("type_", None)
1670 args = list(args)
1671 if args:
1672 if isinstance(args[0], util.string_types):
1673 if name is not None:
1674 raise exc.ArgumentError(
1675 "May not pass name positionally and as a keyword."
1676 )
1677 name = args.pop(0)
1678 if args:
1679 coltype = args[0]
1681 if hasattr(coltype, "_sqla_type"):
1682 if type_ is not None:
1683 raise exc.ArgumentError(
1684 "May not pass type_ positionally and as a keyword."
1685 )
1686 type_ = args.pop(0)
1688 if name is not None:
1689 name = quoted_name(name, kwargs.pop("quote", None))
1690 elif "quote" in kwargs:
1691 raise exc.ArgumentError(
1692 "Explicit 'name' is required when " "sending 'quote' argument"
1693 )
1695 super(Column, self).__init__(name, type_)
1696 self.key = kwargs.pop("key", name)
1697 self.primary_key = primary_key = kwargs.pop("primary_key", False)
1699 self._user_defined_nullable = udn = kwargs.pop(
1700 "nullable", NULL_UNSPECIFIED
1701 )
1703 if udn is not NULL_UNSPECIFIED:
1704 self.nullable = udn
1705 else:
1706 self.nullable = not primary_key
1708 self.default = kwargs.pop("default", None)
1709 self.server_default = kwargs.pop("server_default", None)
1710 self.server_onupdate = kwargs.pop("server_onupdate", None)
1712 # these default to None because .index and .unique is *not*
1713 # an informational flag about Column - there can still be an
1714 # Index or UniqueConstraint referring to this Column.
1715 self.index = kwargs.pop("index", None)
1716 self.unique = kwargs.pop("unique", None)
1718 self.system = kwargs.pop("system", False)
1719 self.doc = kwargs.pop("doc", None)
1720 self.onupdate = kwargs.pop("onupdate", None)
1721 self.autoincrement = kwargs.pop("autoincrement", "auto")
1722 self.constraints = set()
1723 self.foreign_keys = set()
1724 self.comment = kwargs.pop("comment", None)
1725 self.computed = None
1726 self.identity = None
1728 # check if this Column is proxying another column
1729 if "_proxies" in kwargs:
1730 self._proxies = kwargs.pop("_proxies")
1731 # otherwise, add DDL-related events
1732 elif isinstance(self.type, SchemaEventTarget):
1733 self.type._set_parent_with_dispatch(self)
1735 if self.default is not None:
1736 if isinstance(self.default, (ColumnDefault, Sequence)):
1737 args.append(self.default)
1738 else:
1739 if getattr(self.type, "_warn_on_bytestring", False):
1740 if isinstance(self.default, util.binary_type):
1741 util.warn(
1742 "Unicode column '%s' has non-unicode "
1743 "default value %r specified."
1744 % (self.key, self.default)
1745 )
1746 args.append(ColumnDefault(self.default))
1748 if self.server_default is not None:
1749 if isinstance(self.server_default, FetchedValue):
1750 args.append(self.server_default._as_for_update(False))
1751 else:
1752 args.append(DefaultClause(self.server_default))
1754 if self.onupdate is not None:
1755 if isinstance(self.onupdate, (ColumnDefault, Sequence)):
1756 args.append(self.onupdate)
1757 else:
1758 args.append(ColumnDefault(self.onupdate, for_update=True))
1760 if self.server_onupdate is not None:
1761 if isinstance(self.server_onupdate, FetchedValue):
1762 args.append(self.server_onupdate._as_for_update(True))
1763 else:
1764 args.append(
1765 DefaultClause(self.server_onupdate, for_update=True)
1766 )
1767 self._init_items(*args)
1769 util.set_creation_order(self)
1771 if "info" in kwargs:
1772 self.info = kwargs.pop("info")
1774 self._extra_kwargs(**kwargs)
1776 foreign_keys = None
1777 """A collection of all :class:`_schema.ForeignKey` marker objects
1778 associated with this :class:`_schema.Column`.
1780 Each object is a member of a :class:`_schema.Table`-wide
1781 :class:`_schema.ForeignKeyConstraint`.
1783 .. seealso::
1785 :attr:`_schema.Table.foreign_keys`
1787 """
1789 index = None
1790 """The value of the :paramref:`_schema.Column.index` parameter.
1792 Does not indicate if this :class:`_schema.Column` is actually indexed
1793 or not; use :attr:`_schema.Table.indexes`.
1795 .. seealso::
1797 :attr:`_schema.Table.indexes`
1798 """
1800 unique = None
1801 """The value of the :paramref:`_schema.Column.unique` parameter.
1803 Does not indicate if this :class:`_schema.Column` is actually subject to
1804 a unique constraint or not; use :attr:`_schema.Table.indexes` and
1805 :attr:`_schema.Table.constraints`.
1807 .. seealso::
1809 :attr:`_schema.Table.indexes`
1811 :attr:`_schema.Table.constraints`.
1813 """
1815 @util.memoized_property
1816 def _gen_static_annotations_cache_key(self):
1817 """special attribute used by cache key gen, if true, we will
1818 use a static cache key for the annotations dictionary, else we
1819 will generate a new cache key for annotations each time.
1821 Added for #8790
1823 """
1824 return self.table is not None and self.table._is_table
1826 def _extra_kwargs(self, **kwargs):
1827 self._validate_dialect_kwargs(kwargs)
1829 def __str__(self):
1830 if self.name is None:
1831 return "(no name)"
1832 elif self.table is not None:
1833 if self.table.named_with_column:
1834 return self.table.description + "." + self.description
1835 else:
1836 return self.description
1837 else:
1838 return self.description
1840 def references(self, column):
1841 """Return True if this Column references the given column via foreign
1842 key."""
1844 for fk in self.foreign_keys:
1845 if fk.column.proxy_set.intersection(column.proxy_set):
1846 return True
1847 else:
1848 return False
1850 def append_foreign_key(self, fk):
1851 fk._set_parent_with_dispatch(self)
1853 def __repr__(self):
1854 kwarg = []
1855 if self.key != self.name:
1856 kwarg.append("key")
1857 if self.primary_key:
1858 kwarg.append("primary_key")
1859 if not self.nullable:
1860 kwarg.append("nullable")
1861 if self.onupdate:
1862 kwarg.append("onupdate")
1863 if self.default:
1864 kwarg.append("default")
1865 if self.server_default:
1866 kwarg.append("server_default")
1867 if self.comment:
1868 kwarg.append("comment")
1869 return "Column(%s)" % ", ".join(
1870 [repr(self.name)]
1871 + [repr(self.type)]
1872 + [repr(x) for x in self.foreign_keys if x is not None]
1873 + [repr(x) for x in self.constraints]
1874 + [
1875 (
1876 self.table is not None
1877 and "table=<%s>" % self.table.description
1878 or "table=None"
1879 )
1880 ]
1881 + ["%s=%s" % (k, repr(getattr(self, k))) for k in kwarg]
1882 )
1884 def _set_parent(self, table, allow_replacements=True):
1885 if not self.name:
1886 raise exc.ArgumentError(
1887 "Column must be constructed with a non-blank name or "
1888 "assign a non-blank .name before adding to a Table."
1889 )
1891 self._reset_memoizations()
1893 if self.key is None:
1894 self.key = self.name
1896 existing = getattr(self, "table", None)
1897 if existing is not None and existing is not table:
1898 raise exc.ArgumentError(
1899 "Column object '%s' already assigned to Table '%s'"
1900 % (self.key, existing.description)
1901 )
1903 if self.key in table._columns:
1904 col = table._columns.get(self.key)
1905 if col is not self:
1906 if not allow_replacements:
1907 util.warn_deprecated(
1908 "A column with name '%s' is already present "
1909 "in table '%s'. Please use method "
1910 ":meth:`_schema.Table.append_column` with the "
1911 "parameter ``replace_existing=True`` to replace an "
1912 "existing column." % (self.key, table.name),
1913 "1.4",
1914 )
1915 for fk in col.foreign_keys:
1916 table.foreign_keys.remove(fk)
1917 if fk.constraint in table.constraints:
1918 # this might have been removed
1919 # already, if it's a composite constraint
1920 # and more than one col being replaced
1921 table.constraints.remove(fk.constraint)
1923 table._columns.replace(self)
1925 self.table = table
1927 if self.primary_key:
1928 table.primary_key._replace(self)
1929 elif self.key in table.primary_key:
1930 raise exc.ArgumentError(
1931 "Trying to redefine primary-key column '%s' as a "
1932 "non-primary-key column on table '%s'"
1933 % (self.key, table.fullname)
1934 )
1936 if self.index:
1937 if isinstance(self.index, util.string_types):
1938 raise exc.ArgumentError(
1939 "The 'index' keyword argument on Column is boolean only. "
1940 "To create indexes with a specific name, create an "
1941 "explicit Index object external to the Table."
1942 )
1943 table.append_constraint(
1944 Index(
1945 None, self.key, unique=bool(self.unique), _column_flag=True
1946 )
1947 )
1949 elif self.unique:
1950 if isinstance(self.unique, util.string_types):
1951 raise exc.ArgumentError(
1952 "The 'unique' keyword argument on Column is boolean "
1953 "only. To create unique constraints or indexes with a "
1954 "specific name, append an explicit UniqueConstraint to "
1955 "the Table's list of elements, or create an explicit "
1956 "Index object external to the Table."
1957 )
1958 table.append_constraint(
1959 UniqueConstraint(self.key, _column_flag=True)
1960 )
1962 self._setup_on_memoized_fks(lambda fk: fk._set_remote_table(table))
1964 if self.identity and (
1965 isinstance(self.default, Sequence)
1966 or isinstance(self.onupdate, Sequence)
1967 ):
1968 raise exc.ArgumentError(
1969 "An column cannot specify both Identity and Sequence."
1970 )
1972 def _setup_on_memoized_fks(self, fn):
1973 fk_keys = [
1974 ((self.table.key, self.key), False),
1975 ((self.table.key, self.name), True),
1976 ]
1977 for fk_key, link_to_name in fk_keys:
1978 if fk_key in self.table.metadata._fk_memos:
1979 for fk in self.table.metadata._fk_memos[fk_key]:
1980 if fk.link_to_name is link_to_name:
1981 fn(fk)
1983 def _on_table_attach(self, fn):
1984 if self.table is not None:
1985 fn(self, self.table)
1986 else:
1987 event.listen(self, "after_parent_attach", fn)
1989 @util.deprecated(
1990 "1.4",
1991 "The :meth:`_schema.Column.copy` method is deprecated "
1992 "and will be removed in a future release.",
1993 )
1994 def copy(self, **kw):
1995 return self._copy(**kw)
1997 def _copy(self, **kw):
1998 """Create a copy of this ``Column``, uninitialized.
2000 This is used in :meth:`_schema.Table.to_metadata`.
2002 """
2004 # Constraint objects plus non-constraint-bound ForeignKey objects
2005 args = [
2006 c._copy(**kw) for c in self.constraints if not c._type_bound
2007 ] + [c._copy(**kw) for c in self.foreign_keys if not c.constraint]
2009 # ticket #5276
2010 column_kwargs = {}
2011 for dialect_name in self.dialect_options:
2012 dialect_options = self.dialect_options[dialect_name]._non_defaults
2013 for (
2014 dialect_option_key,
2015 dialect_option_value,
2016 ) in dialect_options.items():
2017 column_kwargs[
2018 dialect_name + "_" + dialect_option_key
2019 ] = dialect_option_value
2021 server_default = self.server_default
2022 server_onupdate = self.server_onupdate
2023 if isinstance(server_default, (Computed, Identity)):
2024 server_default = server_onupdate = None
2025 args.append(self.server_default._copy(**kw))
2027 type_ = self.type
2028 if isinstance(type_, SchemaEventTarget):
2029 type_ = type_.copy(**kw)
2031 if self._user_defined_nullable is not NULL_UNSPECIFIED:
2032 column_kwargs["nullable"] = self._user_defined_nullable
2034 c = self._constructor(
2035 name=self.name,
2036 type_=type_,
2037 key=self.key,
2038 primary_key=self.primary_key,
2039 unique=self.unique,
2040 system=self.system,
2041 # quote=self.quote, # disabled 2013-08-27 (commit 031ef080)
2042 index=self.index,
2043 autoincrement=self.autoincrement,
2044 default=self.default,
2045 server_default=server_default,
2046 onupdate=self.onupdate,
2047 server_onupdate=server_onupdate,
2048 doc=self.doc,
2049 comment=self.comment,
2050 *args,
2051 **column_kwargs
2052 )
2053 return self._schema_item_copy(c)
2055 def _make_proxy(
2056 self, selectable, name=None, key=None, name_is_truncatable=False, **kw
2057 ):
2058 """Create a *proxy* for this column.
2060 This is a copy of this ``Column`` referenced by a different parent
2061 (such as an alias or select statement). The column should
2062 be used only in select scenarios, as its full DDL/default
2063 information is not transferred.
2065 """
2067 fk = [
2068 ForeignKey(
2069 col if col is not None else f._colspec,
2070 _unresolvable=col is None,
2071 _constraint=f.constraint,
2072 )
2073 for f, col in [
2074 (fk, fk._resolve_column(raiseerr=False))
2075 for fk in self.foreign_keys
2076 ]
2077 ]
2079 if name is None and self.name is None:
2080 raise exc.InvalidRequestError(
2081 "Cannot initialize a sub-selectable"
2082 " with this Column object until its 'name' has "
2083 "been assigned."
2084 )
2085 try:
2086 c = self._constructor(
2087 coercions.expect(
2088 roles.TruncatedLabelRole, name if name else self.name
2089 )
2090 if name_is_truncatable
2091 else (name or self.name),
2092 self.type,
2093 # this may actually be ._proxy_key when the key is incoming
2094 key=key if key else name if name else self.key,
2095 primary_key=self.primary_key,
2096 nullable=self.nullable,
2097 _proxies=[self],
2098 *fk
2099 )
2100 except TypeError as err:
2101 util.raise_(
2102 TypeError(
2103 "Could not create a copy of this %r object. "
2104 "Ensure the class includes a _constructor() "
2105 "attribute or method which accepts the "
2106 "standard Column constructor arguments, or "
2107 "references the Column class itself." % self.__class__
2108 ),
2109 from_=err,
2110 )
2112 c.table = selectable
2113 c._propagate_attrs = selectable._propagate_attrs
2114 if selectable._is_clone_of is not None:
2115 c._is_clone_of = selectable._is_clone_of.columns.get(c.key)
2116 if self.primary_key:
2117 selectable.primary_key.add(c)
2118 if fk:
2119 selectable.foreign_keys.update(fk)
2120 return c.key, c
2123class ForeignKey(DialectKWArgs, SchemaItem):
2124 """Defines a dependency between two columns.
2126 ``ForeignKey`` is specified as an argument to a :class:`_schema.Column`
2127 object,
2128 e.g.::
2130 t = Table("remote_table", metadata,
2131 Column("remote_id", ForeignKey("main_table.id"))
2132 )
2134 Note that ``ForeignKey`` is only a marker object that defines
2135 a dependency between two columns. The actual constraint
2136 is in all cases represented by the :class:`_schema.ForeignKeyConstraint`
2137 object. This object will be generated automatically when
2138 a ``ForeignKey`` is associated with a :class:`_schema.Column` which
2139 in turn is associated with a :class:`_schema.Table`. Conversely,
2140 when :class:`_schema.ForeignKeyConstraint` is applied to a
2141 :class:`_schema.Table`,
2142 ``ForeignKey`` markers are automatically generated to be
2143 present on each associated :class:`_schema.Column`, which are also
2144 associated with the constraint object.
2146 Note that you cannot define a "composite" foreign key constraint,
2147 that is a constraint between a grouping of multiple parent/child
2148 columns, using ``ForeignKey`` objects. To define this grouping,
2149 the :class:`_schema.ForeignKeyConstraint` object must be used, and applied
2150 to the :class:`_schema.Table`. The associated ``ForeignKey`` objects
2151 are created automatically.
2153 The ``ForeignKey`` objects associated with an individual
2154 :class:`_schema.Column`
2155 object are available in the `foreign_keys` collection
2156 of that column.
2158 Further examples of foreign key configuration are in
2159 :ref:`metadata_foreignkeys`.
2161 """
2163 __visit_name__ = "foreign_key"
2165 def __init__(
2166 self,
2167 column,
2168 _constraint=None,
2169 use_alter=False,
2170 name=None,
2171 onupdate=None,
2172 ondelete=None,
2173 deferrable=None,
2174 initially=None,
2175 link_to_name=False,
2176 match=None,
2177 info=None,
2178 _unresolvable=False,
2179 **dialect_kw
2180 ):
2181 r"""
2182 Construct a column-level FOREIGN KEY.
2184 The :class:`_schema.ForeignKey` object when constructed generates a
2185 :class:`_schema.ForeignKeyConstraint`
2186 which is associated with the parent
2187 :class:`_schema.Table` object's collection of constraints.
2189 :param column: A single target column for the key relationship. A
2190 :class:`_schema.Column` object or a column name as a string:
2191 ``tablename.columnkey`` or ``schema.tablename.columnkey``.
2192 ``columnkey`` is the ``key`` which has been assigned to the column
2193 (defaults to the column name itself), unless ``link_to_name`` is
2194 ``True`` in which case the rendered name of the column is used.
2196 :param name: Optional string. An in-database name for the key if
2197 `constraint` is not provided.
2199 :param onupdate: Optional string. If set, emit ON UPDATE <value> when
2200 issuing DDL for this constraint. Typical values include CASCADE,
2201 DELETE and RESTRICT.
2203 :param ondelete: Optional string. If set, emit ON DELETE <value> when
2204 issuing DDL for this constraint. Typical values include CASCADE,
2205 DELETE and RESTRICT.
2207 :param deferrable: Optional bool. If set, emit DEFERRABLE or NOT
2208 DEFERRABLE when issuing DDL for this constraint.
2210 :param initially: Optional string. If set, emit INITIALLY <value> when
2211 issuing DDL for this constraint.
2213 :param link_to_name: if True, the string name given in ``column`` is
2214 the rendered name of the referenced column, not its locally
2215 assigned ``key``.
2217 :param use_alter: passed to the underlying
2218 :class:`_schema.ForeignKeyConstraint`
2219 to indicate the constraint should
2220 be generated/dropped externally from the CREATE TABLE/ DROP TABLE
2221 statement. See :paramref:`_schema.ForeignKeyConstraint.use_alter`
2222 for further description.
2224 .. seealso::
2226 :paramref:`_schema.ForeignKeyConstraint.use_alter`
2228 :ref:`use_alter`
2230 :param match: Optional string. If set, emit MATCH <value> when issuing
2231 DDL for this constraint. Typical values include SIMPLE, PARTIAL
2232 and FULL.
2234 :param info: Optional data dictionary which will be populated into the
2235 :attr:`.SchemaItem.info` attribute of this object.
2237 .. versionadded:: 1.0.0
2239 :param \**dialect_kw: Additional keyword arguments are dialect
2240 specific, and passed in the form ``<dialectname>_<argname>``. The
2241 arguments are ultimately handled by a corresponding
2242 :class:`_schema.ForeignKeyConstraint`.
2243 See the documentation regarding
2244 an individual dialect at :ref:`dialect_toplevel` for detail on
2245 documented arguments.
2247 .. versionadded:: 0.9.2
2249 """
2251 self._colspec = coercions.expect(roles.DDLReferredColumnRole, column)
2252 self._unresolvable = _unresolvable
2254 if isinstance(self._colspec, util.string_types):
2255 self._table_column = None
2256 else:
2257 self._table_column = self._colspec
2259 if not isinstance(
2260 self._table_column.table, (util.NoneType, TableClause)
2261 ):
2262 raise exc.ArgumentError(
2263 "ForeignKey received Column not bound "
2264 "to a Table, got: %r" % self._table_column.table
2265 )
2267 # the linked ForeignKeyConstraint.
2268 # ForeignKey will create this when parent Column
2269 # is attached to a Table, *or* ForeignKeyConstraint
2270 # object passes itself in when creating ForeignKey
2271 # markers.
2272 self.constraint = _constraint
2273 self.parent = None
2274 self.use_alter = use_alter
2275 self.name = name
2276 self.onupdate = onupdate
2277 self.ondelete = ondelete
2278 self.deferrable = deferrable
2279 self.initially = initially
2280 self.link_to_name = link_to_name
2281 self.match = match
2282 if info:
2283 self.info = info
2284 self._unvalidated_dialect_kw = dialect_kw
2286 def __repr__(self):
2287 return "ForeignKey(%r)" % self._get_colspec()
2289 @util.deprecated(
2290 "1.4",
2291 "The :meth:`_schema.ForeignKey.copy` method is deprecated "
2292 "and will be removed in a future release.",
2293 )
2294 def copy(self, schema=None, **kw):
2295 return self._copy(schema=schema, **kw)
2297 def _copy(self, schema=None, **kw):
2298 """Produce a copy of this :class:`_schema.ForeignKey` object.
2300 The new :class:`_schema.ForeignKey` will not be bound
2301 to any :class:`_schema.Column`.
2303 This method is usually used by the internal
2304 copy procedures of :class:`_schema.Column`, :class:`_schema.Table`,
2305 and :class:`_schema.MetaData`.
2307 :param schema: The returned :class:`_schema.ForeignKey` will
2308 reference the original table and column name, qualified
2309 by the given string schema name.
2311 """
2313 fk = ForeignKey(
2314 self._get_colspec(schema=schema),
2315 use_alter=self.use_alter,
2316 name=self.name,
2317 onupdate=self.onupdate,
2318 ondelete=self.ondelete,
2319 deferrable=self.deferrable,
2320 initially=self.initially,
2321 link_to_name=self.link_to_name,
2322 match=self.match,
2323 **self._unvalidated_dialect_kw
2324 )
2325 return self._schema_item_copy(fk)
2327 def _get_colspec(self, schema=None, table_name=None):
2328 """Return a string based 'column specification' for this
2329 :class:`_schema.ForeignKey`.
2331 This is usually the equivalent of the string-based "tablename.colname"
2332 argument first passed to the object's constructor.
2334 """
2335 if schema not in (None, RETAIN_SCHEMA):
2336 _schema, tname, colname = self._column_tokens
2337 if table_name is not None:
2338 tname = table_name
2339 if schema is BLANK_SCHEMA:
2340 return "%s.%s" % (tname, colname)
2341 else:
2342 return "%s.%s.%s" % (schema, tname, colname)
2343 elif table_name:
2344 schema, tname, colname = self._column_tokens
2345 if schema:
2346 return "%s.%s.%s" % (schema, table_name, colname)
2347 else:
2348 return "%s.%s" % (table_name, colname)
2349 elif self._table_column is not None:
2350 return "%s.%s" % (
2351 self._table_column.table.fullname,
2352 self._table_column.key,
2353 )
2354 else:
2355 return self._colspec
2357 @property
2358 def _referred_schema(self):
2359 return self._column_tokens[0]
2361 def _table_key(self):
2362 if self._table_column is not None:
2363 if self._table_column.table is None:
2364 return None
2365 else:
2366 return self._table_column.table.key
2367 else:
2368 schema, tname, colname = self._column_tokens
2369 return _get_table_key(tname, schema)
2371 target_fullname = property(_get_colspec)
2373 def references(self, table):
2374 """Return True if the given :class:`_schema.Table`
2375 is referenced by this
2376 :class:`_schema.ForeignKey`."""
2378 return table.corresponding_column(self.column) is not None
2380 def get_referent(self, table):
2381 """Return the :class:`_schema.Column` in the given
2382 :class:`_schema.Table`
2383 referenced by this :class:`_schema.ForeignKey`.
2385 Returns None if this :class:`_schema.ForeignKey`
2386 does not reference the given
2387 :class:`_schema.Table`.
2389 """
2391 return table.corresponding_column(self.column)
2393 @util.memoized_property
2394 def _column_tokens(self):
2395 """parse a string-based _colspec into its component parts."""
2397 m = self._get_colspec().split(".")
2398 if m is None:
2399 raise exc.ArgumentError(
2400 "Invalid foreign key column specification: %s" % self._colspec
2401 )
2402 if len(m) == 1:
2403 tname = m.pop()
2404 colname = None
2405 else:
2406 colname = m.pop()
2407 tname = m.pop()
2409 # A FK between column 'bar' and table 'foo' can be
2410 # specified as 'foo', 'foo.bar', 'dbo.foo.bar',
2411 # 'otherdb.dbo.foo.bar'. Once we have the column name and
2412 # the table name, treat everything else as the schema
2413 # name. Some databases (e.g. Sybase) support
2414 # inter-database foreign keys. See tickets#1341 and --
2415 # indirectly related -- Ticket #594. This assumes that '.'
2416 # will never appear *within* any component of the FK.
2418 if len(m) > 0:
2419 schema = ".".join(m)
2420 else:
2421 schema = None
2422 return schema, tname, colname
2424 def _resolve_col_tokens(self):
2425 if self.parent is None:
2426 raise exc.InvalidRequestError(
2427 "this ForeignKey object does not yet have a "
2428 "parent Column associated with it."
2429 )
2431 elif self.parent.table is None:
2432 raise exc.InvalidRequestError(
2433 "this ForeignKey's parent column is not yet associated "
2434 "with a Table."
2435 )
2437 parenttable = self.parent.table
2439 if self._unresolvable:
2440 schema, tname, colname = self._column_tokens
2441 tablekey = _get_table_key(tname, schema)
2442 return parenttable, tablekey, colname
2444 # assertion
2445 # basically Column._make_proxy() sends the actual
2446 # target Column to the ForeignKey object, so the
2447 # string resolution here is never called.
2448 for c in self.parent.base_columns:
2449 if isinstance(c, Column):
2450 assert c.table is parenttable
2451 break
2452 else:
2453 assert False
2454 ######################
2456 schema, tname, colname = self._column_tokens
2458 if schema is None and parenttable.metadata.schema is not None:
2459 schema = parenttable.metadata.schema
2461 tablekey = _get_table_key(tname, schema)
2462 return parenttable, tablekey, colname
2464 def _link_to_col_by_colstring(self, parenttable, table, colname):
2466 _column = None
2467 if colname is None:
2468 # colname is None in the case that ForeignKey argument
2469 # was specified as table name only, in which case we
2470 # match the column name to the same column on the
2471 # parent.
2472 # this use case wasn't working in later 1.x series
2473 # as it had no test coverage; fixed in 2.0
2474 parent = self.parent
2475 assert parent is not None
2476 key = parent.key
2477 _column = table.c.get(key, None)
2478 elif self.link_to_name:
2479 key = colname
2480 for c in table.c:
2481 if c.name == colname:
2482 _column = c
2483 else:
2484 key = colname
2485 _column = table.c.get(colname, None)
2487 if _column is None:
2488 raise exc.NoReferencedColumnError(
2489 "Could not initialize target column "
2490 "for ForeignKey '%s' on table '%s': "
2491 "table '%s' has no column named '%s'"
2492 % (self._colspec, parenttable.name, table.name, key),
2493 table.name,
2494 key,
2495 )
2497 return _column
2499 def _set_target_column(self, column):
2500 assert self.parent is not None
2502 # propagate TypeEngine to parent if it didn't have one
2503 if self.parent.type._isnull:
2504 self.parent.type = column.type
2506 # super-edgy case, if other FKs point to our column,
2507 # they'd get the type propagated out also.
2509 def set_type(fk):
2510 if fk.parent.type._isnull:
2511 fk.parent.type = column.type
2513 self.parent._setup_on_memoized_fks(set_type)
2515 self.column = column
2517 @util.memoized_property
2518 def column(self):
2519 """Return the target :class:`_schema.Column` referenced by this
2520 :class:`_schema.ForeignKey`.
2522 If no target column has been established, an exception
2523 is raised.
2525 .. versionchanged:: 0.9.0
2526 Foreign key target column resolution now occurs as soon as both
2527 the ForeignKey object and the remote Column to which it refers
2528 are both associated with the same MetaData object.
2530 """
2532 return self._resolve_column()
2534 def _resolve_column(self, raiseerr=True):
2536 if isinstance(self._colspec, util.string_types):
2538 parenttable, tablekey, colname = self._resolve_col_tokens()
2540 if self._unresolvable or tablekey not in parenttable.metadata:
2541 if not raiseerr:
2542 return None
2543 raise exc.NoReferencedTableError(
2544 "Foreign key associated with column '%s' could not find "
2545 "table '%s' with which to generate a "
2546 "foreign key to target column '%s'"
2547 % (self.parent, tablekey, colname),
2548 tablekey,
2549 )
2550 elif parenttable.key not in parenttable.metadata:
2551 if not raiseerr:
2552 return None
2553 raise exc.InvalidRequestError(
2554 "Table %s is no longer associated with its "
2555 "parent MetaData" % parenttable
2556 )
2557 else:
2558 table = parenttable.metadata.tables[tablekey]
2559 return self._link_to_col_by_colstring(
2560 parenttable, table, colname
2561 )
2563 elif hasattr(self._colspec, "__clause_element__"):
2564 _column = self._colspec.__clause_element__()
2565 return _column
2566 else:
2567 _column = self._colspec
2568 return _column
2570 def _set_parent(self, column, **kw):
2571 if self.parent is not None and self.parent is not column:
2572 raise exc.InvalidRequestError(
2573 "This ForeignKey already has a parent !"
2574 )
2575 self.parent = column
2576 self.parent.foreign_keys.add(self)
2577 self.parent._on_table_attach(self._set_table)
2579 def _set_remote_table(self, table):
2580 parenttable, tablekey, colname = self._resolve_col_tokens()
2581 self._link_to_col_by_colstring(parenttable, table, colname)
2583 _column = self._link_to_col_by_colstring(parenttable, table, colname)
2584 self._set_target_column(_column)
2585 assert self.constraint is not None
2587 self.constraint._validate_dest_table(table)
2589 def _remove_from_metadata(self, metadata):
2590 parenttable, table_key, colname = self._resolve_col_tokens()
2591 fk_key = (table_key, colname)
2593 if self in metadata._fk_memos[fk_key]:
2594 # TODO: no test coverage for self not in memos
2595 metadata._fk_memos[fk_key].remove(self)
2597 def _set_table(self, column, table):
2598 # standalone ForeignKey - create ForeignKeyConstraint
2599 # on the hosting Table when attached to the Table.
2600 assert isinstance(table, Table)
2601 if self.constraint is None:
2602 self.constraint = ForeignKeyConstraint(
2603 [],
2604 [],
2605 use_alter=self.use_alter,
2606 name=self.name,
2607 onupdate=self.onupdate,
2608 ondelete=self.ondelete,
2609 deferrable=self.deferrable,
2610 initially=self.initially,
2611 match=self.match,
2612 **self._unvalidated_dialect_kw
2613 )
2614 self.constraint._append_element(column, self)
2615 self.constraint._set_parent_with_dispatch(table)
2616 table.foreign_keys.add(self)
2617 # set up remote ".column" attribute, or a note to pick it
2618 # up when the other Table/Column shows up
2619 if isinstance(self._colspec, util.string_types):
2620 parenttable, table_key, colname = self._resolve_col_tokens()
2621 fk_key = (table_key, colname)
2622 if table_key in parenttable.metadata.tables:
2623 table = parenttable.metadata.tables[table_key]
2624 try:
2625 _column = self._link_to_col_by_colstring(
2626 parenttable, table, colname
2627 )
2628 except exc.NoReferencedColumnError:
2629 # this is OK, we'll try later
2630 pass
2631 else:
2632 self._set_target_column(_column)
2633 parenttable.metadata._fk_memos[fk_key].append(self)
2634 elif hasattr(self._colspec, "__clause_element__"):
2635 _column = self._colspec.__clause_element__()
2636 self._set_target_column(_column)
2637 else:
2638 _column = self._colspec
2639 self._set_target_column(_column)
2642class DefaultGenerator(Executable, SchemaItem):
2643 """Base class for column *default* values."""
2645 __visit_name__ = "default_generator"
2647 is_sequence = False
2648 is_server_default = False
2649 column = None
2651 def __init__(self, for_update=False):
2652 self.for_update = for_update
2654 def _set_parent(self, column, **kw):
2655 self.column = column
2656 if self.for_update:
2657 self.column.onupdate = self
2658 else:
2659 self.column.default = self
2661 @util.deprecated_20(
2662 ":meth:`.DefaultGenerator.execute`",
2663 alternative="All statement execution in SQLAlchemy 2.0 is performed "
2664 "by the :meth:`_engine.Connection.execute` method of "
2665 ":class:`_engine.Connection`, "
2666 "or in the ORM by the :meth:`.Session.execute` method of "
2667 ":class:`.Session`.",
2668 )
2669 def execute(self, bind=None):
2670 if bind is None:
2671 bind = _bind_or_error(self)
2672 return bind._execute_default(self, (), util.EMPTY_DICT)
2674 def _execute_on_connection(
2675 self, connection, multiparams, params, execution_options
2676 ):
2677 return connection._execute_default(
2678 self, multiparams, params, execution_options
2679 )
2681 @property
2682 def bind(self):
2683 """Return the connectable associated with this default."""
2684 if getattr(self, "column", None) is not None:
2685 return self.column.table.bind
2686 else:
2687 return None
2690class ColumnDefault(DefaultGenerator):
2691 """A plain default value on a column.
2693 This could correspond to a constant, a callable function,
2694 or a SQL clause.
2696 :class:`.ColumnDefault` is generated automatically
2697 whenever the ``default``, ``onupdate`` arguments of
2698 :class:`_schema.Column` are used. A :class:`.ColumnDefault`
2699 can be passed positionally as well.
2701 For example, the following::
2703 Column('foo', Integer, default=50)
2705 Is equivalent to::
2707 Column('foo', Integer, ColumnDefault(50))
2710 """
2712 def __init__(self, arg, **kwargs):
2713 """Construct a new :class:`.ColumnDefault`.
2716 :param arg: argument representing the default value.
2717 May be one of the following:
2719 * a plain non-callable Python value, such as a
2720 string, integer, boolean, or other simple type.
2721 The default value will be used as is each time.
2722 * a SQL expression, that is one which derives from
2723 :class:`_expression.ColumnElement`. The SQL expression will
2724 be rendered into the INSERT or UPDATE statement,
2725 or in the case of a primary key column when
2726 RETURNING is not used may be
2727 pre-executed before an INSERT within a SELECT.
2728 * A Python callable. The function will be invoked for each
2729 new row subject to an INSERT or UPDATE.
2730 The callable must accept exactly
2731 zero or one positional arguments. The one-argument form
2732 will receive an instance of the :class:`.ExecutionContext`,
2733 which provides contextual information as to the current
2734 :class:`_engine.Connection` in use as well as the current
2735 statement and parameters.
2737 """
2738 super(ColumnDefault, self).__init__(**kwargs)
2739 if isinstance(arg, FetchedValue):
2740 raise exc.ArgumentError(
2741 "ColumnDefault may not be a server-side default type."
2742 )
2743 if callable(arg):
2744 arg = self._maybe_wrap_callable(arg)
2745 self.arg = arg
2747 @util.memoized_property
2748 def is_callable(self):
2749 return callable(self.arg)
2751 @util.memoized_property
2752 def is_clause_element(self):
2753 return isinstance(self.arg, ClauseElement)
2755 @util.memoized_property
2756 def is_scalar(self):
2757 return (
2758 not self.is_callable
2759 and not self.is_clause_element
2760 and not self.is_sequence
2761 )
2763 @util.memoized_property
2764 @util.preload_module("sqlalchemy.sql.sqltypes")
2765 def _arg_is_typed(self):
2766 sqltypes = util.preloaded.sql_sqltypes
2768 if self.is_clause_element:
2769 return not isinstance(self.arg.type, sqltypes.NullType)
2770 else:
2771 return False
2773 def _maybe_wrap_callable(self, fn):
2774 """Wrap callables that don't accept a context.
2776 This is to allow easy compatibility with default callables
2777 that aren't specific to accepting of a context.
2779 """
2780 try:
2781 argspec = util.get_callable_argspec(fn, no_self=True)
2782 except TypeError:
2783 return util.wrap_callable(lambda ctx: fn(), fn)
2785 defaulted = argspec[3] is not None and len(argspec[3]) or 0
2786 positionals = len(argspec[0]) - defaulted
2788 if positionals == 0:
2789 return util.wrap_callable(lambda ctx: fn(), fn)
2791 elif positionals == 1:
2792 return fn
2793 else:
2794 raise exc.ArgumentError(
2795 "ColumnDefault Python function takes zero or one "
2796 "positional arguments"
2797 )
2799 def __repr__(self):
2800 return "ColumnDefault(%r)" % (self.arg,)
2803class IdentityOptions(object):
2804 """Defines options for a named database sequence or an identity column.
2806 .. versionadded:: 1.3.18
2808 .. seealso::
2810 :class:`.Sequence`
2812 """
2814 def __init__(
2815 self,
2816 start=None,
2817 increment=None,
2818 minvalue=None,
2819 maxvalue=None,
2820 nominvalue=None,
2821 nomaxvalue=None,
2822 cycle=None,
2823 cache=None,
2824 order=None,
2825 ):
2826 """Construct a :class:`.IdentityOptions` object.
2828 See the :class:`.Sequence` documentation for a complete description
2829 of the parameters.
2831 :param start: the starting index of the sequence.
2832 :param increment: the increment value of the sequence.
2833 :param minvalue: the minimum value of the sequence.
2834 :param maxvalue: the maximum value of the sequence.
2835 :param nominvalue: no minimum value of the sequence.
2836 :param nomaxvalue: no maximum value of the sequence.
2837 :param cycle: allows the sequence to wrap around when the maxvalue
2838 or minvalue has been reached.
2839 :param cache: optional integer value; number of future values in the
2840 sequence which are calculated in advance.
2841 :param order: optional boolean value; if ``True``, renders the
2842 ORDER keyword.
2844 """
2845 self.start = start
2846 self.increment = increment
2847 self.minvalue = minvalue
2848 self.maxvalue = maxvalue
2849 self.nominvalue = nominvalue
2850 self.nomaxvalue = nomaxvalue
2851 self.cycle = cycle
2852 self.cache = cache
2853 self.order = order
2856class Sequence(IdentityOptions, DefaultGenerator):
2857 """Represents a named database sequence.
2859 The :class:`.Sequence` object represents the name and configurational
2860 parameters of a database sequence. It also represents
2861 a construct that can be "executed" by a SQLAlchemy :class:`_engine.Engine`
2862 or :class:`_engine.Connection`,
2863 rendering the appropriate "next value" function
2864 for the target database and returning a result.
2866 The :class:`.Sequence` is typically associated with a primary key column::
2868 some_table = Table(
2869 'some_table', metadata,
2870 Column('id', Integer, Sequence('some_table_seq'),
2871 primary_key=True)
2872 )
2874 When CREATE TABLE is emitted for the above :class:`_schema.Table`, if the
2875 target platform supports sequences, a CREATE SEQUENCE statement will
2876 be emitted as well. For platforms that don't support sequences,
2877 the :class:`.Sequence` construct is ignored.
2879 .. seealso::
2881 :ref:`defaults_sequences`
2883 :class:`.CreateSequence`
2885 :class:`.DropSequence`
2887 """
2889 __visit_name__ = "sequence"
2891 is_sequence = True
2893 def __init__(
2894 self,
2895 name,
2896 start=None,
2897 increment=None,
2898 minvalue=None,
2899 maxvalue=None,
2900 nominvalue=None,
2901 nomaxvalue=None,
2902 cycle=None,
2903 schema=None,
2904 cache=None,
2905 order=None,
2906 data_type=None,
2907 optional=False,
2908 quote=None,
2909 metadata=None,
2910 quote_schema=None,
2911 for_update=False,
2912 ):
2913 """Construct a :class:`.Sequence` object.
2915 :param name: the name of the sequence.
2917 :param start: the starting index of the sequence. This value is
2918 used when the CREATE SEQUENCE command is emitted to the database
2919 as the value of the "START WITH" clause. If ``None``, the
2920 clause is omitted, which on most platforms indicates a starting
2921 value of 1.
2922 :param increment: the increment value of the sequence. This
2923 value is used when the CREATE SEQUENCE command is emitted to
2924 the database as the value of the "INCREMENT BY" clause. If ``None``,
2925 the clause is omitted, which on most platforms indicates an
2926 increment of 1.
2927 :param minvalue: the minimum value of the sequence. This
2928 value is used when the CREATE SEQUENCE command is emitted to
2929 the database as the value of the "MINVALUE" clause. If ``None``,
2930 the clause is omitted, which on most platforms indicates a
2931 minvalue of 1 and -2^63-1 for ascending and descending sequences,
2932 respectively.
2934 .. versionadded:: 1.0.7
2936 :param maxvalue: the maximum value of the sequence. This
2937 value is used when the CREATE SEQUENCE command is emitted to
2938 the database as the value of the "MAXVALUE" clause. If ``None``,
2939 the clause is omitted, which on most platforms indicates a
2940 maxvalue of 2^63-1 and -1 for ascending and descending sequences,
2941 respectively.
2943 .. versionadded:: 1.0.7
2945 :param nominvalue: no minimum value of the sequence. This
2946 value is used when the CREATE SEQUENCE command is emitted to
2947 the database as the value of the "NO MINVALUE" clause. If ``None``,
2948 the clause is omitted, which on most platforms indicates a
2949 minvalue of 1 and -2^63-1 for ascending and descending sequences,
2950 respectively.
2952 .. versionadded:: 1.0.7
2954 :param nomaxvalue: no maximum value of the sequence. This
2955 value is used when the CREATE SEQUENCE command is emitted to
2956 the database as the value of the "NO MAXVALUE" clause. If ``None``,
2957 the clause is omitted, which on most platforms indicates a
2958 maxvalue of 2^63-1 and -1 for ascending and descending sequences,
2959 respectively.
2961 .. versionadded:: 1.0.7
2963 :param cycle: allows the sequence to wrap around when the maxvalue
2964 or minvalue has been reached by an ascending or descending sequence
2965 respectively. This value is used when the CREATE SEQUENCE command
2966 is emitted to the database as the "CYCLE" clause. If the limit is
2967 reached, the next number generated will be the minvalue or maxvalue,
2968 respectively. If cycle=False (the default) any calls to nextval
2969 after the sequence has reached its maximum value will return an
2970 error.
2972 .. versionadded:: 1.0.7
2974 :param schema: optional schema name for the sequence, if located
2975 in a schema other than the default. The rules for selecting the
2976 schema name when a :class:`_schema.MetaData`
2977 is also present are the same
2978 as that of :paramref:`_schema.Table.schema`.
2980 :param cache: optional integer value; number of future values in the
2981 sequence which are calculated in advance. Renders the CACHE keyword
2982 understood by Oracle and PostgreSQL.
2984 .. versionadded:: 1.1.12
2986 :param order: optional boolean value; if ``True``, renders the
2987 ORDER keyword, understood by Oracle, indicating the sequence is
2988 definitively ordered. May be necessary to provide deterministic
2989 ordering using Oracle RAC.
2991 .. versionadded:: 1.1.12
2993 :param data_type: The type to be returned by the sequence, for
2994 dialects that allow us to choose between INTEGER, BIGINT, etc.
2995 (e.g., mssql).
2997 .. versionadded:: 1.4.0
2999 :param optional: boolean value, when ``True``, indicates that this
3000 :class:`.Sequence` object only needs to be explicitly generated
3001 on backends that don't provide another way to generate primary
3002 key identifiers. Currently, it essentially means, "don't create
3003 this sequence on the PostgreSQL backend, where the SERIAL keyword
3004 creates a sequence for us automatically".
3005 :param quote: boolean value, when ``True`` or ``False``, explicitly
3006 forces quoting of the :paramref:`_schema.Sequence.name` on or off.
3007 When left at its default of ``None``, normal quoting rules based
3008 on casing and reserved words take place.
3009 :param quote_schema: Set the quoting preferences for the ``schema``
3010 name.
3012 :param metadata: optional :class:`_schema.MetaData` object which this
3013 :class:`.Sequence` will be associated with. A :class:`.Sequence`
3014 that is associated with a :class:`_schema.MetaData`
3015 gains the following
3016 capabilities:
3018 * The :class:`.Sequence` will inherit the
3019 :paramref:`_schema.MetaData.schema`
3020 parameter specified to the target :class:`_schema.MetaData`, which
3021 affects the production of CREATE / DROP DDL, if any.
3023 * The :meth:`.Sequence.create` and :meth:`.Sequence.drop` methods
3024 automatically use the engine bound to the :class:`_schema.MetaData`
3025 object, if any.
3027 * The :meth:`_schema.MetaData.create_all` and
3028 :meth:`_schema.MetaData.drop_all`
3029 methods will emit CREATE / DROP for this :class:`.Sequence`,
3030 even if the :class:`.Sequence` is not associated with any
3031 :class:`_schema.Table` / :class:`_schema.Column`
3032 that's a member of this
3033 :class:`_schema.MetaData`.
3035 The above behaviors can only occur if the :class:`.Sequence` is
3036 explicitly associated with the :class:`_schema.MetaData`
3037 via this parameter.
3039 .. seealso::
3041 :ref:`sequence_metadata` - full discussion of the
3042 :paramref:`.Sequence.metadata` parameter.
3044 :param for_update: Indicates this :class:`.Sequence`, when associated
3045 with a :class:`_schema.Column`,
3046 should be invoked for UPDATE statements
3047 on that column's table, rather than for INSERT statements, when
3048 no value is otherwise present for that column in the statement.
3050 """
3051 DefaultGenerator.__init__(self, for_update=for_update)
3052 IdentityOptions.__init__(
3053 self,
3054 start=start,
3055 increment=increment,
3056 minvalue=minvalue,
3057 maxvalue=maxvalue,
3058 nominvalue=nominvalue,
3059 nomaxvalue=nomaxvalue,
3060 cycle=cycle,
3061 cache=cache,
3062 order=order,
3063 )
3064 self.name = quoted_name(name, quote)
3065 self.optional = optional
3066 if schema is BLANK_SCHEMA:
3067 self.schema = schema = None
3068 elif metadata is not None and schema is None and metadata.schema:
3069 self.schema = schema = metadata.schema
3070 else:
3071 self.schema = quoted_name(schema, quote_schema)
3072 self.metadata = metadata
3073 self._key = _get_table_key(name, schema)
3074 if metadata:
3075 self._set_metadata(metadata)
3076 if data_type is not None:
3077 self.data_type = to_instance(data_type)
3078 else:
3079 self.data_type = None
3081 @util.memoized_property
3082 def is_callable(self):
3083 return False
3085 @util.memoized_property
3086 def is_clause_element(self):
3087 return False
3089 @util.preload_module("sqlalchemy.sql.functions")
3090 def next_value(self):
3091 """Return a :class:`.next_value` function element
3092 which will render the appropriate increment function
3093 for this :class:`.Sequence` within any SQL expression.
3095 """
3096 if self.bind:
3097 return util.preloaded.sql_functions.func.next_value(
3098 self, bind=self.bind
3099 )
3100 else:
3101 return util.preloaded.sql_functions.func.next_value(self)
3103 def _set_parent(self, column, **kw):
3104 super(Sequence, self)._set_parent(column)
3105 column._on_table_attach(self._set_table)
3107 def _set_table(self, column, table):
3108 self._set_metadata(table.metadata)
3110 def _set_metadata(self, metadata):
3111 self.metadata = metadata
3112 self.metadata._sequences[self._key] = self
3114 @property
3115 def bind(self):
3116 if self.metadata:
3117 return self.metadata.bind
3118 else:
3119 return None
3121 def create(self, bind=None, checkfirst=True):
3122 """Creates this sequence in the database.
3124 .. note:: the "bind" argument will be required in
3125 SQLAlchemy 2.0.
3127 """
3129 if bind is None:
3130 bind = _bind_or_error(self)
3131 bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
3133 def drop(self, bind=None, checkfirst=True):
3134 """Drops this sequence from the database.
3136 .. note:: the "bind" argument will be required in
3137 SQLAlchemy 2.0.
3139 """
3141 if bind is None:
3142 bind = _bind_or_error(self)
3143 bind._run_ddl_visitor(ddl.SchemaDropper, self, checkfirst=checkfirst)
3145 def _not_a_column_expr(self):
3146 raise exc.InvalidRequestError(
3147 "This %s cannot be used directly "
3148 "as a column expression. Use func.next_value(sequence) "
3149 "to produce a 'next value' function that's usable "
3150 "as a column element." % self.__class__.__name__
3151 )
3154@inspection._self_inspects
3155class FetchedValue(SchemaEventTarget):
3156 """A marker for a transparent database-side default.
3158 Use :class:`.FetchedValue` when the database is configured
3159 to provide some automatic default for a column.
3161 E.g.::
3163 Column('foo', Integer, FetchedValue())
3165 Would indicate that some trigger or default generator
3166 will create a new value for the ``foo`` column during an
3167 INSERT.
3169 .. seealso::
3171 :ref:`triggered_columns`
3173 """
3175 is_server_default = True
3176 reflected = False
3177 has_argument = False
3178 is_clause_element = False
3180 def __init__(self, for_update=False):
3181 self.for_update = for_update
3183 def _as_for_update(self, for_update):
3184 if for_update == self.for_update:
3185 return self
3186 else:
3187 return self._clone(for_update)
3189 def _clone(self, for_update):
3190 n = self.__class__.__new__(self.__class__)
3191 n.__dict__.update(self.__dict__)
3192 n.__dict__.pop("column", None)
3193 n.for_update = for_update
3194 return n
3196 def _set_parent(self, column, **kw):
3197 self.column = column
3198 if self.for_update:
3199 self.column.server_onupdate = self
3200 else:
3201 self.column.server_default = self
3203 def __repr__(self):
3204 return util.generic_repr(self)
3207class DefaultClause(FetchedValue):
3208 """A DDL-specified DEFAULT column value.
3210 :class:`.DefaultClause` is a :class:`.FetchedValue`
3211 that also generates a "DEFAULT" clause when
3212 "CREATE TABLE" is emitted.
3214 :class:`.DefaultClause` is generated automatically
3215 whenever the ``server_default``, ``server_onupdate`` arguments of
3216 :class:`_schema.Column` are used. A :class:`.DefaultClause`
3217 can be passed positionally as well.
3219 For example, the following::
3221 Column('foo', Integer, server_default="50")
3223 Is equivalent to::
3225 Column('foo', Integer, DefaultClause("50"))
3227 """
3229 has_argument = True
3231 def __init__(self, arg, for_update=False, _reflected=False):
3232 util.assert_arg_type(
3233 arg, (util.string_types[0], ClauseElement, TextClause), "arg"
3234 )
3235 super(DefaultClause, self).__init__(for_update)
3236 self.arg = arg
3237 self.reflected = _reflected
3239 def __repr__(self):
3240 return "DefaultClause(%r, for_update=%r)" % (self.arg, self.for_update)
3243class Constraint(DialectKWArgs, SchemaItem):
3244 """A table-level SQL constraint.
3246 :class:`_schema.Constraint` serves as the base class for the series of
3247 constraint objects that can be associated with :class:`_schema.Table`
3248 objects, including :class:`_schema.PrimaryKeyConstraint`,
3249 :class:`_schema.ForeignKeyConstraint`
3250 :class:`_schema.UniqueConstraint`, and
3251 :class:`_schema.CheckConstraint`.
3253 """
3255 __visit_name__ = "constraint"
3257 def __init__(
3258 self,
3259 name=None,
3260 deferrable=None,
3261 initially=None,
3262 _create_rule=None,
3263 info=None,
3264 _type_bound=False,
3265 **dialect_kw
3266 ):
3267 r"""Create a SQL constraint.
3269 :param name:
3270 Optional, the in-database name of this ``Constraint``.
3272 :param deferrable:
3273 Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
3274 issuing DDL for this constraint.
3276 :param initially:
3277 Optional string. If set, emit INITIALLY <value> when issuing DDL
3278 for this constraint.
3280 :param info: Optional data dictionary which will be populated into the
3281 :attr:`.SchemaItem.info` attribute of this object.
3283 .. versionadded:: 1.0.0
3285 :param \**dialect_kw: Additional keyword arguments are dialect
3286 specific, and passed in the form ``<dialectname>_<argname>``. See
3287 the documentation regarding an individual dialect at
3288 :ref:`dialect_toplevel` for detail on documented arguments.
3290 :param _create_rule:
3291 used internally by some datatypes that also create constraints.
3293 :param _type_bound:
3294 used internally to indicate that this constraint is associated with
3295 a specific datatype.
3297 """
3299 self.name = name
3300 self.deferrable = deferrable
3301 self.initially = initially
3302 if info:
3303 self.info = info
3304 self._create_rule = _create_rule
3305 self._type_bound = _type_bound
3306 util.set_creation_order(self)
3307 self._validate_dialect_kwargs(dialect_kw)
3309 @property
3310 def table(self):
3311 try:
3312 if isinstance(self.parent, Table):
3313 return self.parent
3314 except AttributeError:
3315 pass
3316 raise exc.InvalidRequestError(
3317 "This constraint is not bound to a table. Did you "
3318 "mean to call table.append_constraint(constraint) ?"
3319 )
3321 def _set_parent(self, parent, **kw):
3322 self.parent = parent
3323 parent.constraints.add(self)
3325 @util.deprecated(
3326 "1.4",
3327 "The :meth:`_schema.Constraint.copy` method is deprecated "
3328 "and will be removed in a future release.",
3329 )
3330 def copy(self, **kw):
3331 return self._copy(**kw)
3333 def _copy(self, **kw):
3334 raise NotImplementedError()
3337class ColumnCollectionMixin(object):
3339 columns = None
3340 """A :class:`_expression.ColumnCollection` of :class:`_schema.Column`
3341 objects.
3343 This collection represents the columns which are referred to by
3344 this object.
3346 """
3348 _allow_multiple_tables = False
3350 def __init__(self, *columns, **kw):
3351 _autoattach = kw.pop("_autoattach", True)
3352 self._column_flag = kw.pop("_column_flag", False)
3353 self.columns = DedupeColumnCollection()
3355 processed_expressions = kw.pop("_gather_expressions", None)
3356 if processed_expressions is not None:
3357 self._pending_colargs = []
3358 for (
3359 expr,
3360 column,
3361 strname,
3362 add_element,
3363 ) in coercions.expect_col_expression_collection(
3364 roles.DDLConstraintColumnRole, columns
3365 ):
3366 self._pending_colargs.append(add_element)
3367 processed_expressions.append(expr)
3368 else:
3369 self._pending_colargs = [
3370 coercions.expect(roles.DDLConstraintColumnRole, column)
3371 for column in columns
3372 ]
3374 if _autoattach and self._pending_colargs:
3375 self._check_attach()
3377 def _check_attach(self, evt=False):
3378 col_objs = [c for c in self._pending_colargs if isinstance(c, Column)]
3380 cols_w_table = [c for c in col_objs if isinstance(c.table, Table)]
3382 cols_wo_table = set(col_objs).difference(cols_w_table)
3383 if cols_wo_table:
3384 # feature #3341 - place event listeners for Column objects
3385 # such that when all those cols are attached, we autoattach.
3386 assert not evt, "Should not reach here on event call"
3388 # issue #3411 - don't do the per-column auto-attach if some of the
3389 # columns are specified as strings.
3390 has_string_cols = set(
3391 c for c in self._pending_colargs if c is not None
3392 ).difference(col_objs)
3393 if not has_string_cols:
3395 def _col_attached(column, table):
3396 # this isinstance() corresponds with the
3397 # isinstance() above; only want to count Table-bound
3398 # columns
3399 if isinstance(table, Table):
3400 cols_wo_table.discard(column)
3401 if not cols_wo_table:
3402 self._check_attach(evt=True)
3404 self._cols_wo_table = cols_wo_table
3405 for col in cols_wo_table:
3406 col._on_table_attach(_col_attached)
3407 return
3409 columns = cols_w_table
3411 tables = {c.table for c in columns}
3412 if len(tables) == 1:
3413 self._set_parent_with_dispatch(tables.pop())
3414 elif len(tables) > 1 and not self._allow_multiple_tables:
3415 table = columns[0].table
3416 others = [c for c in columns[1:] if c.table is not table]
3417 if others:
3418 raise exc.ArgumentError(
3419 "Column(s) %s are not part of table '%s'."
3420 % (
3421 ", ".join("'%s'" % c for c in others),
3422 table.description,
3423 )
3424 )
3426 def _col_expressions(self, table):
3427 return [
3428 table.c[col] if isinstance(col, util.string_types) else col
3429 for col in self._pending_colargs
3430 ]
3432 def _set_parent(self, table, **kw):
3433 for col in self._col_expressions(table):
3434 if col is not None:
3435 self.columns.add(col)
3438class ColumnCollectionConstraint(ColumnCollectionMixin, Constraint):
3439 """A constraint that proxies a ColumnCollection."""
3441 def __init__(self, *columns, **kw):
3442 r"""
3443 :param \*columns:
3444 A sequence of column names or Column objects.
3446 :param name:
3447 Optional, the in-database name of this constraint.
3449 :param deferrable:
3450 Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
3451 issuing DDL for this constraint.
3453 :param initially:
3454 Optional string. If set, emit INITIALLY <value> when issuing DDL
3455 for this constraint.
3457 :param \**kw: other keyword arguments including dialect-specific
3458 arguments are propagated to the :class:`.Constraint` superclass.
3460 """
3461 _autoattach = kw.pop("_autoattach", True)
3462 _column_flag = kw.pop("_column_flag", False)
3463 Constraint.__init__(self, **kw)
3464 ColumnCollectionMixin.__init__(
3465 self, *columns, _autoattach=_autoattach, _column_flag=_column_flag
3466 )
3468 columns = None
3469 """A :class:`_expression.ColumnCollection` representing the set of columns
3470 for this constraint.
3472 """
3474 def _set_parent(self, table, **kw):
3475 Constraint._set_parent(self, table)
3476 ColumnCollectionMixin._set_parent(self, table)
3478 def __contains__(self, x):
3479 return x in self.columns
3481 @util.deprecated(
3482 "1.4",
3483 "The :meth:`_schema.ColumnCollectionConstraint.copy` method "
3484 "is deprecated and will be removed in a future release.",
3485 )
3486 def copy(self, target_table=None, **kw):
3487 return self._copy(target_table=target_table, **kw)
3489 def _copy(self, target_table=None, **kw):
3490 # ticket #5276
3491 constraint_kwargs = {}
3492 for dialect_name in self.dialect_options:
3493 dialect_options = self.dialect_options[dialect_name]._non_defaults
3494 for (
3495 dialect_option_key,
3496 dialect_option_value,
3497 ) in dialect_options.items():
3498 constraint_kwargs[
3499 dialect_name + "_" + dialect_option_key
3500 ] = dialect_option_value
3502 c = self.__class__(
3503 name=self.name,
3504 deferrable=self.deferrable,
3505 initially=self.initially,
3506 *[
3507 _copy_expression(expr, self.parent, target_table)
3508 for expr in self.columns
3509 ],
3510 **constraint_kwargs
3511 )
3512 return self._schema_item_copy(c)
3514 def contains_column(self, col):
3515 """Return True if this constraint contains the given column.
3517 Note that this object also contains an attribute ``.columns``
3518 which is a :class:`_expression.ColumnCollection` of
3519 :class:`_schema.Column` objects.
3521 """
3523 return self.columns.contains_column(col)
3525 def __iter__(self):
3526 return iter(self.columns)
3528 def __len__(self):
3529 return len(self.columns)
3532class CheckConstraint(ColumnCollectionConstraint):
3533 """A table- or column-level CHECK constraint.
3535 Can be included in the definition of a Table or Column.
3536 """
3538 _allow_multiple_tables = True
3540 __visit_name__ = "table_or_column_check_constraint"
3542 @_document_text_coercion(
3543 "sqltext",
3544 ":class:`.CheckConstraint`",
3545 ":paramref:`.CheckConstraint.sqltext`",
3546 )
3547 def __init__(
3548 self,
3549 sqltext,
3550 name=None,
3551 deferrable=None,
3552 initially=None,
3553 table=None,
3554 info=None,
3555 _create_rule=None,
3556 _autoattach=True,
3557 _type_bound=False,
3558 **kw
3559 ):
3560 r"""Construct a CHECK constraint.
3562 :param sqltext:
3563 A string containing the constraint definition, which will be used
3564 verbatim, or a SQL expression construct. If given as a string,
3565 the object is converted to a :func:`_expression.text` object.
3566 If the textual
3567 string includes a colon character, escape this using a backslash::
3569 CheckConstraint(r"foo ~ E'a(?\:b|c)d")
3571 :param name:
3572 Optional, the in-database name of the constraint.
3574 :param deferrable:
3575 Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
3576 issuing DDL for this constraint.
3578 :param initially:
3579 Optional string. If set, emit INITIALLY <value> when issuing DDL
3580 for this constraint.
3582 :param info: Optional data dictionary which will be populated into the
3583 :attr:`.SchemaItem.info` attribute of this object.
3585 .. versionadded:: 1.0.0
3587 """
3589 self.sqltext = coercions.expect(roles.DDLExpressionRole, sqltext)
3590 columns = []
3591 visitors.traverse(self.sqltext, {}, {"column": columns.append})
3593 super(CheckConstraint, self).__init__(
3594 name=name,
3595 deferrable=deferrable,
3596 initially=initially,
3597 _create_rule=_create_rule,
3598 info=info,
3599 _type_bound=_type_bound,
3600 _autoattach=_autoattach,
3601 *columns,
3602 **kw
3603 )
3604 if table is not None:
3605 self._set_parent_with_dispatch(table)
3607 @property
3608 def is_column_level(self):
3609 return not isinstance(self.parent, Table)
3611 @util.deprecated(
3612 "1.4",
3613 "The :meth:`_schema.CheckConstraint.copy` method is deprecated "
3614 "and will be removed in a future release.",
3615 )
3616 def copy(self, target_table=None, **kw):
3617 return self._copy(target_table=target_table, **kw)
3619 def _copy(self, target_table=None, **kw):
3620 if target_table is not None:
3621 # note that target_table is None for the copy process of
3622 # a column-bound CheckConstraint, so this path is not reached
3623 # in that case.
3624 sqltext = _copy_expression(self.sqltext, self.table, target_table)
3625 else:
3626 sqltext = self.sqltext
3627 c = CheckConstraint(
3628 sqltext,
3629 name=self.name,
3630 initially=self.initially,
3631 deferrable=self.deferrable,
3632 _create_rule=self._create_rule,
3633 table=target_table,
3634 _autoattach=False,
3635 _type_bound=self._type_bound,
3636 )
3637 return self._schema_item_copy(c)
3640class ForeignKeyConstraint(ColumnCollectionConstraint):
3641 """A table-level FOREIGN KEY constraint.
3643 Defines a single column or composite FOREIGN KEY ... REFERENCES
3644 constraint. For a no-frills, single column foreign key, adding a
3645 :class:`_schema.ForeignKey` to the definition of a :class:`_schema.Column`
3646 is a
3647 shorthand equivalent for an unnamed, single column
3648 :class:`_schema.ForeignKeyConstraint`.
3650 Examples of foreign key configuration are in :ref:`metadata_foreignkeys`.
3652 """
3654 __visit_name__ = "foreign_key_constraint"
3656 def __init__(
3657 self,
3658 columns,
3659 refcolumns,
3660 name=None,
3661 onupdate=None,
3662 ondelete=None,
3663 deferrable=None,
3664 initially=None,
3665 use_alter=False,
3666 link_to_name=False,
3667 match=None,
3668 table=None,
3669 info=None,
3670 **dialect_kw
3671 ):
3672 r"""Construct a composite-capable FOREIGN KEY.
3674 :param columns: A sequence of local column names. The named columns
3675 must be defined and present in the parent Table. The names should
3676 match the ``key`` given to each column (defaults to the name) unless
3677 ``link_to_name`` is True.
3679 :param refcolumns: A sequence of foreign column names or Column
3680 objects. The columns must all be located within the same Table.
3682 :param name: Optional, the in-database name of the key.
3684 :param onupdate: Optional string. If set, emit ON UPDATE <value> when
3685 issuing DDL for this constraint. Typical values include CASCADE,
3686 DELETE and RESTRICT.
3688 :param ondelete: Optional string. If set, emit ON DELETE <value> when
3689 issuing DDL for this constraint. Typical values include CASCADE,
3690 DELETE and RESTRICT.
3692 :param deferrable: Optional bool. If set, emit DEFERRABLE or NOT
3693 DEFERRABLE when issuing DDL for this constraint.
3695 :param initially: Optional string. If set, emit INITIALLY <value> when
3696 issuing DDL for this constraint.
3698 :param link_to_name: if True, the string name given in ``column`` is
3699 the rendered name of the referenced column, not its locally assigned
3700 ``key``.
3702 :param use_alter: If True, do not emit the DDL for this constraint as
3703 part of the CREATE TABLE definition. Instead, generate it via an
3704 ALTER TABLE statement issued after the full collection of tables
3705 have been created, and drop it via an ALTER TABLE statement before
3706 the full collection of tables are dropped.
3708 The use of :paramref:`_schema.ForeignKeyConstraint.use_alter` is
3709 particularly geared towards the case where two or more tables
3710 are established within a mutually-dependent foreign key constraint
3711 relationship; however, the :meth:`_schema.MetaData.create_all` and
3712 :meth:`_schema.MetaData.drop_all`
3713 methods will perform this resolution
3714 automatically, so the flag is normally not needed.
3716 .. versionchanged:: 1.0.0 Automatic resolution of foreign key
3717 cycles has been added, removing the need to use the
3718 :paramref:`_schema.ForeignKeyConstraint.use_alter` in typical use
3719 cases.
3721 .. seealso::
3723 :ref:`use_alter`
3725 :param match: Optional string. If set, emit MATCH <value> when issuing
3726 DDL for this constraint. Typical values include SIMPLE, PARTIAL
3727 and FULL.
3729 :param info: Optional data dictionary which will be populated into the
3730 :attr:`.SchemaItem.info` attribute of this object.
3732 .. versionadded:: 1.0.0
3734 :param \**dialect_kw: Additional keyword arguments are dialect
3735 specific, and passed in the form ``<dialectname>_<argname>``. See
3736 the documentation regarding an individual dialect at
3737 :ref:`dialect_toplevel` for detail on documented arguments.
3739 .. versionadded:: 0.9.2
3741 """
3743 Constraint.__init__(
3744 self,
3745 name=name,
3746 deferrable=deferrable,
3747 initially=initially,
3748 info=info,
3749 **dialect_kw
3750 )
3751 self.onupdate = onupdate
3752 self.ondelete = ondelete
3753 self.link_to_name = link_to_name
3754 self.use_alter = use_alter
3755 self.match = match
3757 if len(set(columns)) != len(refcolumns):
3758 if len(set(columns)) != len(columns):
3759 # e.g. FOREIGN KEY (a, a) REFERENCES r (b, c)
3760 raise exc.ArgumentError(
3761 "ForeignKeyConstraint with duplicate source column "
3762 "references are not supported."
3763 )
3764 else:
3765 # e.g. FOREIGN KEY (a) REFERENCES r (b, c)
3766 # paraphrasing
3767 # https://www.postgresql.org/docs/current/static/ddl-constraints.html
3768 raise exc.ArgumentError(
3769 "ForeignKeyConstraint number "
3770 "of constrained columns must match the number of "
3771 "referenced columns."
3772 )
3774 # standalone ForeignKeyConstraint - create
3775 # associated ForeignKey objects which will be applied to hosted
3776 # Column objects (in col.foreign_keys), either now or when attached
3777 # to the Table for string-specified names
3778 self.elements = [
3779 ForeignKey(
3780 refcol,
3781 _constraint=self,
3782 name=self.name,
3783 onupdate=self.onupdate,
3784 ondelete=self.ondelete,
3785 use_alter=self.use_alter,
3786 link_to_name=self.link_to_name,
3787 match=self.match,
3788 deferrable=self.deferrable,
3789 initially=self.initially,
3790 **self.dialect_kwargs
3791 )
3792 for refcol in refcolumns
3793 ]
3795 ColumnCollectionMixin.__init__(self, *columns)
3796 if table is not None:
3797 if hasattr(self, "parent"):
3798 assert table is self.parent
3799 self._set_parent_with_dispatch(table)
3801 def _append_element(self, column, fk):
3802 self.columns.add(column)
3803 self.elements.append(fk)
3805 columns = None
3806 """A :class:`_expression.ColumnCollection` representing the set of columns
3807 for this constraint.
3809 """
3811 elements = None
3812 """A sequence of :class:`_schema.ForeignKey` objects.
3814 Each :class:`_schema.ForeignKey`
3815 represents a single referring column/referred
3816 column pair.
3818 This collection is intended to be read-only.
3820 """
3822 @property
3823 def _elements(self):
3824 # legacy - provide a dictionary view of (column_key, fk)
3825 return util.OrderedDict(zip(self.column_keys, self.elements))
3827 @property
3828 def _referred_schema(self):
3829 for elem in self.elements:
3830 return elem._referred_schema
3831 else:
3832 return None
3834 @property
3835 def referred_table(self):
3836 """The :class:`_schema.Table` object to which this
3837 :class:`_schema.ForeignKeyConstraint` references.
3839 This is a dynamically calculated attribute which may not be available
3840 if the constraint and/or parent table is not yet associated with
3841 a metadata collection that contains the referred table.
3843 .. versionadded:: 1.0.0
3845 """
3846 return self.elements[0].column.table
3848 def _validate_dest_table(self, table):
3849 table_keys = set([elem._table_key() for elem in self.elements])
3850 if None not in table_keys and len(table_keys) > 1:
3851 elem0, elem1 = sorted(table_keys)[0:2]
3852 raise exc.ArgumentError(
3853 "ForeignKeyConstraint on %s(%s) refers to "
3854 "multiple remote tables: %s and %s"
3855 % (table.fullname, self._col_description, elem0, elem1)
3856 )
3858 @property
3859 def column_keys(self):
3860 """Return a list of string keys representing the local
3861 columns in this :class:`_schema.ForeignKeyConstraint`.
3863 This list is either the original string arguments sent
3864 to the constructor of the :class:`_schema.ForeignKeyConstraint`,
3865 or if the constraint has been initialized with :class:`_schema.Column`
3866 objects, is the string ``.key`` of each element.
3868 .. versionadded:: 1.0.0
3870 """
3871 if hasattr(self, "parent"):
3872 return self.columns.keys()
3873 else:
3874 return [
3875 col.key if isinstance(col, ColumnElement) else str(col)
3876 for col in self._pending_colargs
3877 ]
3879 @property
3880 def _col_description(self):
3881 return ", ".join(self.column_keys)
3883 def _set_parent(self, table, **kw):
3884 Constraint._set_parent(self, table)
3886 try:
3887 ColumnCollectionConstraint._set_parent(self, table)
3888 except KeyError as ke:
3889 util.raise_(
3890 exc.ArgumentError(
3891 "Can't create ForeignKeyConstraint "
3892 "on table '%s': no column "
3893 "named '%s' is present." % (table.description, ke.args[0])
3894 ),
3895 from_=ke,
3896 )
3898 for col, fk in zip(self.columns, self.elements):
3899 if not hasattr(fk, "parent") or fk.parent is not col:
3900 fk._set_parent_with_dispatch(col)
3902 self._validate_dest_table(table)
3904 @util.deprecated(
3905 "1.4",
3906 "The :meth:`_schema.ForeignKeyConstraint.copy` method is deprecated "
3907 "and will be removed in a future release.",
3908 )
3909 def copy(self, schema=None, target_table=None, **kw):
3910 return self._copy(schema=schema, target_table=target_table, **kw)
3912 def _copy(self, schema=None, target_table=None, **kw):
3913 fkc = ForeignKeyConstraint(
3914 [x.parent.key for x in self.elements],
3915 [
3916 x._get_colspec(
3917 schema=schema,
3918 table_name=target_table.name
3919 if target_table is not None
3920 and x._table_key() == x.parent.table.key
3921 else None,
3922 )
3923 for x in self.elements
3924 ],
3925 name=self.name,
3926 onupdate=self.onupdate,
3927 ondelete=self.ondelete,
3928 use_alter=self.use_alter,
3929 deferrable=self.deferrable,
3930 initially=self.initially,
3931 link_to_name=self.link_to_name,
3932 match=self.match,
3933 )
3934 for self_fk, other_fk in zip(self.elements, fkc.elements):
3935 self_fk._schema_item_copy(other_fk)
3936 return self._schema_item_copy(fkc)
3939class PrimaryKeyConstraint(ColumnCollectionConstraint):
3940 """A table-level PRIMARY KEY constraint.
3942 The :class:`.PrimaryKeyConstraint` object is present automatically
3943 on any :class:`_schema.Table` object; it is assigned a set of
3944 :class:`_schema.Column` objects corresponding to those marked with
3945 the :paramref:`_schema.Column.primary_key` flag::
3947 >>> my_table = Table('mytable', metadata,
3948 ... Column('id', Integer, primary_key=True),
3949 ... Column('version_id', Integer, primary_key=True),
3950 ... Column('data', String(50))
3951 ... )
3952 >>> my_table.primary_key
3953 PrimaryKeyConstraint(
3954 Column('id', Integer(), table=<mytable>,
3955 primary_key=True, nullable=False),
3956 Column('version_id', Integer(), table=<mytable>,
3957 primary_key=True, nullable=False)
3958 )
3960 The primary key of a :class:`_schema.Table` can also be specified by using
3961 a :class:`.PrimaryKeyConstraint` object explicitly; in this mode of usage,
3962 the "name" of the constraint can also be specified, as well as other
3963 options which may be recognized by dialects::
3965 my_table = Table('mytable', metadata,
3966 Column('id', Integer),
3967 Column('version_id', Integer),
3968 Column('data', String(50)),
3969 PrimaryKeyConstraint('id', 'version_id',
3970 name='mytable_pk')
3971 )
3973 The two styles of column-specification should generally not be mixed.
3974 An warning is emitted if the columns present in the
3975 :class:`.PrimaryKeyConstraint`
3976 don't match the columns that were marked as ``primary_key=True``, if both
3977 are present; in this case, the columns are taken strictly from the
3978 :class:`.PrimaryKeyConstraint` declaration, and those columns otherwise
3979 marked as ``primary_key=True`` are ignored. This behavior is intended to
3980 be backwards compatible with previous behavior.
3982 .. versionchanged:: 0.9.2 Using a mixture of columns within a
3983 :class:`.PrimaryKeyConstraint` in addition to columns marked as
3984 ``primary_key=True`` now emits a warning if the lists don't match.
3985 The ultimate behavior of ignoring those columns marked with the flag
3986 only is currently maintained for backwards compatibility; this warning
3987 may raise an exception in a future release.
3989 For the use case where specific options are to be specified on the
3990 :class:`.PrimaryKeyConstraint`, but the usual style of using
3991 ``primary_key=True`` flags is still desirable, an empty
3992 :class:`.PrimaryKeyConstraint` may be specified, which will take on the
3993 primary key column collection from the :class:`_schema.Table` based on the
3994 flags::
3996 my_table = Table('mytable', metadata,
3997 Column('id', Integer, primary_key=True),
3998 Column('version_id', Integer, primary_key=True),
3999 Column('data', String(50)),
4000 PrimaryKeyConstraint(name='mytable_pk',
4001 mssql_clustered=True)
4002 )
4004 .. versionadded:: 0.9.2 an empty :class:`.PrimaryKeyConstraint` may now
4005 be specified for the purposes of establishing keyword arguments with
4006 the constraint, independently of the specification of "primary key"
4007 columns within the :class:`_schema.Table` itself; columns marked as
4008 ``primary_key=True`` will be gathered into the empty constraint's
4009 column collection.
4011 """
4013 __visit_name__ = "primary_key_constraint"
4015 def __init__(self, *columns, **kw):
4016 self._implicit_generated = kw.pop("_implicit_generated", False)
4017 super(PrimaryKeyConstraint, self).__init__(*columns, **kw)
4019 def _set_parent(self, table, **kw):
4020 super(PrimaryKeyConstraint, self)._set_parent(table)
4022 if table.primary_key is not self:
4023 table.constraints.discard(table.primary_key)
4024 table.primary_key = self
4025 table.constraints.add(self)
4027 table_pks = [c for c in table.c if c.primary_key]
4028 if self.columns and table_pks and set(table_pks) != set(self.columns):
4029 util.warn(
4030 "Table '%s' specifies columns %s as primary_key=True, "
4031 "not matching locally specified columns %s; setting the "
4032 "current primary key columns to %s. This warning "
4033 "may become an exception in a future release"
4034 % (
4035 table.name,
4036 ", ".join("'%s'" % c.name for c in table_pks),
4037 ", ".join("'%s'" % c.name for c in self.columns),
4038 ", ".join("'%s'" % c.name for c in self.columns),
4039 )
4040 )
4041 table_pks[:] = []
4043 for c in self.columns:
4044 c.primary_key = True
4045 if c._user_defined_nullable is NULL_UNSPECIFIED:
4046 c.nullable = False
4047 if table_pks:
4048 self.columns.extend(table_pks)
4050 def _reload(self, columns):
4051 """repopulate this :class:`.PrimaryKeyConstraint` given
4052 a set of columns.
4054 Existing columns in the table that are marked as primary_key=True
4055 are maintained.
4057 Also fires a new event.
4059 This is basically like putting a whole new
4060 :class:`.PrimaryKeyConstraint` object on the parent
4061 :class:`_schema.Table` object without actually replacing the object.
4063 The ordering of the given list of columns is also maintained; these
4064 columns will be appended to the list of columns after any which
4065 are already present.
4067 """
4068 # set the primary key flag on new columns.
4069 # note any existing PK cols on the table also have their
4070 # flag still set.
4071 for col in columns:
4072 col.primary_key = True
4074 self.columns.extend(columns)
4076 PrimaryKeyConstraint._autoincrement_column._reset(self)
4077 self._set_parent_with_dispatch(self.table)
4079 def _replace(self, col):
4080 PrimaryKeyConstraint._autoincrement_column._reset(self)
4081 self.columns.replace(col)
4083 self.dispatch._sa_event_column_added_to_pk_constraint(self, col)
4085 @property
4086 def columns_autoinc_first(self):
4087 autoinc = self._autoincrement_column
4089 if autoinc is not None:
4090 return [autoinc] + [c for c in self.columns if c is not autoinc]
4091 else:
4092 return list(self.columns)
4094 @util.memoized_property
4095 def _autoincrement_column(self):
4096 def _validate_autoinc(col, autoinc_true):
4097 if col.type._type_affinity is None or not issubclass(
4098 col.type._type_affinity,
4099 (
4100 type_api.INTEGERTYPE._type_affinity,
4101 type_api.NUMERICTYPE._type_affinity,
4102 ),
4103 ):
4104 if autoinc_true:
4105 raise exc.ArgumentError(
4106 "Column type %s on column '%s' is not "
4107 "compatible with autoincrement=True" % (col.type, col)
4108 )
4109 else:
4110 return False
4111 elif (
4112 not isinstance(col.default, (type(None), Sequence))
4113 and not autoinc_true
4114 ):
4115 return False
4116 elif (
4117 col.server_default is not None
4118 and not isinstance(col.server_default, Identity)
4119 and not autoinc_true
4120 ):
4121 return False
4122 elif col.foreign_keys and col.autoincrement not in (
4123 True,
4124 "ignore_fk",
4125 ):
4126 return False
4127 return True
4129 if len(self.columns) == 1:
4130 col = list(self.columns)[0]
4132 if col.autoincrement is True:
4133 _validate_autoinc(col, True)
4134 return col
4135 elif (
4136 col.autoincrement
4137 in (
4138 "auto",
4139 "ignore_fk",
4140 )
4141 and _validate_autoinc(col, False)
4142 ):
4143 return col
4145 else:
4146 autoinc = None
4147 for col in self.columns:
4148 if col.autoincrement is True:
4149 _validate_autoinc(col, True)
4150 if autoinc is not None:
4151 raise exc.ArgumentError(
4152 "Only one Column may be marked "
4153 "autoincrement=True, found both %s and %s."
4154 % (col.name, autoinc.name)
4155 )
4156 else:
4157 autoinc = col
4159 return autoinc
4162class UniqueConstraint(ColumnCollectionConstraint):
4163 """A table-level UNIQUE constraint.
4165 Defines a single column or composite UNIQUE constraint. For a no-frills,
4166 single column constraint, adding ``unique=True`` to the ``Column``
4167 definition is a shorthand equivalent for an unnamed, single column
4168 UniqueConstraint.
4169 """
4171 __visit_name__ = "unique_constraint"
4174class Index(DialectKWArgs, ColumnCollectionMixin, SchemaItem):
4175 """A table-level INDEX.
4177 Defines a composite (one or more column) INDEX.
4179 E.g.::
4181 sometable = Table("sometable", metadata,
4182 Column("name", String(50)),
4183 Column("address", String(100))
4184 )
4186 Index("some_index", sometable.c.name)
4188 For a no-frills, single column index, adding
4189 :class:`_schema.Column` also supports ``index=True``::
4191 sometable = Table("sometable", metadata,
4192 Column("name", String(50), index=True)
4193 )
4195 For a composite index, multiple columns can be specified::
4197 Index("some_index", sometable.c.name, sometable.c.address)
4199 Functional indexes are supported as well, typically by using the
4200 :data:`.func` construct in conjunction with table-bound
4201 :class:`_schema.Column` objects::
4203 Index("some_index", func.lower(sometable.c.name))
4205 An :class:`.Index` can also be manually associated with a
4206 :class:`_schema.Table`,
4207 either through inline declaration or using
4208 :meth:`_schema.Table.append_constraint`. When this approach is used,
4209 the names
4210 of the indexed columns can be specified as strings::
4212 Table("sometable", metadata,
4213 Column("name", String(50)),
4214 Column("address", String(100)),
4215 Index("some_index", "name", "address")
4216 )
4218 To support functional or expression-based indexes in this form, the
4219 :func:`_expression.text` construct may be used::
4221 from sqlalchemy import text
4223 Table("sometable", metadata,
4224 Column("name", String(50)),
4225 Column("address", String(100)),
4226 Index("some_index", text("lower(name)"))
4227 )
4229 .. versionadded:: 0.9.5 the :func:`_expression.text`
4230 construct may be used to
4231 specify :class:`.Index` expressions, provided the :class:`.Index`
4232 is explicitly associated with the :class:`_schema.Table`.
4235 .. seealso::
4237 :ref:`schema_indexes` - General information on :class:`.Index`.
4239 :ref:`postgresql_indexes` - PostgreSQL-specific options available for
4240 the :class:`.Index` construct.
4242 :ref:`mysql_indexes` - MySQL-specific options available for the
4243 :class:`.Index` construct.
4245 :ref:`mssql_indexes` - MSSQL-specific options available for the
4246 :class:`.Index` construct.
4248 """
4250 __visit_name__ = "index"
4252 def __init__(self, name, *expressions, **kw):
4253 r"""Construct an index object.
4255 :param name:
4256 The name of the index
4258 :param \*expressions:
4259 Column expressions to include in the index. The expressions
4260 are normally instances of :class:`_schema.Column`, but may also
4261 be arbitrary SQL expressions which ultimately refer to a
4262 :class:`_schema.Column`.
4264 :param unique=False:
4265 Keyword only argument; if True, create a unique index.
4267 :param quote=None:
4268 Keyword only argument; whether to apply quoting to the name of
4269 the index. Works in the same manner as that of
4270 :paramref:`_schema.Column.quote`.
4272 :param info=None: Optional data dictionary which will be populated
4273 into the :attr:`.SchemaItem.info` attribute of this object.
4275 .. versionadded:: 1.0.0
4277 :param \**kw: Additional keyword arguments not mentioned above are
4278 dialect specific, and passed in the form
4279 ``<dialectname>_<argname>``. See the documentation regarding an
4280 individual dialect at :ref:`dialect_toplevel` for detail on
4281 documented arguments.
4283 """
4284 self.table = table = None
4286 self.name = quoted_name(name, kw.pop("quote", None))
4287 self.unique = kw.pop("unique", False)
4288 _column_flag = kw.pop("_column_flag", False)
4289 if "info" in kw:
4290 self.info = kw.pop("info")
4292 # TODO: consider "table" argument being public, but for
4293 # the purpose of the fix here, it starts as private.
4294 if "_table" in kw:
4295 table = kw.pop("_table")
4297 self._validate_dialect_kwargs(kw)
4299 self.expressions = []
4300 # will call _set_parent() if table-bound column
4301 # objects are present
4302 ColumnCollectionMixin.__init__(
4303 self,
4304 *expressions,
4305 _column_flag=_column_flag,
4306 _gather_expressions=self.expressions
4307 )
4309 if table is not None:
4310 self._set_parent(table)
4312 def _set_parent(self, table, **kw):
4313 ColumnCollectionMixin._set_parent(self, table)
4315 if self.table is not None and table is not self.table:
4316 raise exc.ArgumentError(
4317 "Index '%s' is against table '%s', and "
4318 "cannot be associated with table '%s'."
4319 % (self.name, self.table.description, table.description)
4320 )
4321 self.table = table
4322 table.indexes.add(self)
4324 expressions = self.expressions
4325 col_expressions = self._col_expressions(table)
4326 assert len(expressions) == len(col_expressions)
4327 self.expressions = [
4328 expr if isinstance(expr, ClauseElement) else colexpr
4329 for expr, colexpr in zip(expressions, col_expressions)
4330 ]
4332 @property
4333 def bind(self):
4334 """Return the connectable associated with this Index."""
4336 return self.table.bind
4338 def create(self, bind=None, checkfirst=False):
4339 """Issue a ``CREATE`` statement for this
4340 :class:`.Index`, using the given :class:`.Connectable`
4341 for connectivity.
4343 .. note:: the "bind" argument will be required in
4344 SQLAlchemy 2.0.
4346 .. seealso::
4348 :meth:`_schema.MetaData.create_all`.
4350 """
4351 if bind is None:
4352 bind = _bind_or_error(self)
4353 bind._run_ddl_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
4354 return self
4356 def drop(self, bind=None, checkfirst=False):
4357 """Issue a ``DROP`` statement for this
4358 :class:`.Index`, using the given :class:`.Connectable`
4359 for connectivity.
4361 .. note:: the "bind" argument will be required in
4362 SQLAlchemy 2.0.
4364 .. seealso::
4366 :meth:`_schema.MetaData.drop_all`.
4368 """
4369 if bind is None:
4370 bind = _bind_or_error(self)
4371 bind._run_ddl_visitor(ddl.SchemaDropper, self, checkfirst=checkfirst)
4373 def __repr__(self):
4374 return "Index(%s)" % (
4375 ", ".join(
4376 [repr(self.name)]
4377 + [repr(e) for e in self.expressions]
4378 + (self.unique and ["unique=True"] or [])
4379 )
4380 )
4383DEFAULT_NAMING_CONVENTION = util.immutabledict({"ix": "ix_%(column_0_label)s"})
4386class MetaData(SchemaItem):
4387 """A collection of :class:`_schema.Table`
4388 objects and their associated schema
4389 constructs.
4391 Holds a collection of :class:`_schema.Table` objects as well as
4392 an optional binding to an :class:`_engine.Engine` or
4393 :class:`_engine.Connection`. If bound, the :class:`_schema.Table` objects
4394 in the collection and their columns may participate in implicit SQL
4395 execution.
4397 The :class:`_schema.Table` objects themselves are stored in the
4398 :attr:`_schema.MetaData.tables` dictionary.
4400 :class:`_schema.MetaData` is a thread-safe object for read operations.
4401 Construction of new tables within a single :class:`_schema.MetaData`
4402 object,
4403 either explicitly or via reflection, may not be completely thread-safe.
4405 .. seealso::
4407 :ref:`metadata_describing` - Introduction to database metadata
4409 """
4411 __visit_name__ = "metadata"
4413 @util.deprecated_params(
4414 bind=(
4415 "2.0",
4416 "The :paramref:`_schema.MetaData.bind` argument is deprecated and "
4417 "will be removed in SQLAlchemy 2.0.",
4418 ),
4419 )
4420 def __init__(
4421 self,
4422 bind=None,
4423 schema=None,
4424 quote_schema=None,
4425 naming_convention=None,
4426 info=None,
4427 ):
4428 """Create a new MetaData object.
4430 :param bind:
4431 An Engine or Connection to bind to. May also be a string or URL
4432 instance, these are passed to :func:`_sa.create_engine` and
4433 this :class:`_schema.MetaData` will
4434 be bound to the resulting engine.
4436 :param schema:
4437 The default schema to use for the :class:`_schema.Table`,
4438 :class:`.Sequence`, and potentially other objects associated with
4439 this :class:`_schema.MetaData`. Defaults to ``None``.
4441 .. seealso::
4443 :ref:`schema_metadata_schema_name` - details on how the
4444 :paramref:`_schema.MetaData.schema` parameter is used.
4446 :paramref:`_schema.Table.schema`
4448 :paramref:`.Sequence.schema`
4450 :param quote_schema:
4451 Sets the ``quote_schema`` flag for those :class:`_schema.Table`,
4452 :class:`.Sequence`, and other objects which make usage of the
4453 local ``schema`` name.
4455 :param info: Optional data dictionary which will be populated into the
4456 :attr:`.SchemaItem.info` attribute of this object.
4458 .. versionadded:: 1.0.0
4460 :param naming_convention: a dictionary referring to values which
4461 will establish default naming conventions for :class:`.Constraint`
4462 and :class:`.Index` objects, for those objects which are not given
4463 a name explicitly.
4465 The keys of this dictionary may be:
4467 * a constraint or Index class, e.g. the :class:`.UniqueConstraint`,
4468 :class:`_schema.ForeignKeyConstraint` class, the :class:`.Index`
4469 class
4471 * a string mnemonic for one of the known constraint classes;
4472 ``"fk"``, ``"pk"``, ``"ix"``, ``"ck"``, ``"uq"`` for foreign key,
4473 primary key, index, check, and unique constraint, respectively.
4475 * the string name of a user-defined "token" that can be used
4476 to define new naming tokens.
4478 The values associated with each "constraint class" or "constraint
4479 mnemonic" key are string naming templates, such as
4480 ``"uq_%(table_name)s_%(column_0_name)s"``,
4481 which describe how the name should be composed. The values
4482 associated with user-defined "token" keys should be callables of the
4483 form ``fn(constraint, table)``, which accepts the constraint/index
4484 object and :class:`_schema.Table` as arguments, returning a string
4485 result.
4487 The built-in names are as follows, some of which may only be
4488 available for certain types of constraint:
4490 * ``%(table_name)s`` - the name of the :class:`_schema.Table`
4491 object
4492 associated with the constraint.
4494 * ``%(referred_table_name)s`` - the name of the
4495 :class:`_schema.Table`
4496 object associated with the referencing target of a
4497 :class:`_schema.ForeignKeyConstraint`.
4499 * ``%(column_0_name)s`` - the name of the :class:`_schema.Column`
4500 at
4501 index position "0" within the constraint.
4503 * ``%(column_0N_name)s`` - the name of all :class:`_schema.Column`
4504 objects in order within the constraint, joined without a
4505 separator.
4507 * ``%(column_0_N_name)s`` - the name of all
4508 :class:`_schema.Column`
4509 objects in order within the constraint, joined with an
4510 underscore as a separator.
4512 * ``%(column_0_label)s``, ``%(column_0N_label)s``,
4513 ``%(column_0_N_label)s`` - the label of either the zeroth
4514 :class:`_schema.Column` or all :class:`.Columns`, separated with
4515 or without an underscore
4517 * ``%(column_0_key)s``, ``%(column_0N_key)s``,
4518 ``%(column_0_N_key)s`` - the key of either the zeroth
4519 :class:`_schema.Column` or all :class:`.Columns`, separated with
4520 or without an underscore
4522 * ``%(referred_column_0_name)s``, ``%(referred_column_0N_name)s``
4523 ``%(referred_column_0_N_name)s``, ``%(referred_column_0_key)s``,
4524 ``%(referred_column_0N_key)s``, ... column tokens which
4525 render the names/keys/labels of columns that are referenced
4526 by a :class:`_schema.ForeignKeyConstraint`.
4528 * ``%(constraint_name)s`` - a special key that refers to the
4529 existing name given to the constraint. When this key is
4530 present, the :class:`.Constraint` object's existing name will be
4531 replaced with one that is composed from template string that
4532 uses this token. When this token is present, it is required that
4533 the :class:`.Constraint` is given an explicit name ahead of time.
4535 * user-defined: any additional token may be implemented by passing
4536 it along with a ``fn(constraint, table)`` callable to the
4537 naming_convention dictionary.
4539 .. versionadded:: 1.3.0 - added new ``%(column_0N_name)s``,
4540 ``%(column_0_N_name)s``, and related tokens that produce
4541 concatenations of names, keys, or labels for all columns referred
4542 to by a given constraint.
4544 .. seealso::
4546 :ref:`constraint_naming_conventions` - for detailed usage
4547 examples.
4549 """
4550 self.tables = util.FacadeDict()
4551 self.schema = quoted_name(schema, quote_schema)
4552 self.naming_convention = (
4553 naming_convention
4554 if naming_convention
4555 else DEFAULT_NAMING_CONVENTION
4556 )
4557 if info:
4558 self.info = info
4559 self._schemas = set()
4560 self._sequences = {}
4561 self._fk_memos = collections.defaultdict(list)
4563 self.bind = bind
4565 tables = None
4566 """A dictionary of :class:`_schema.Table`
4567 objects keyed to their name or "table key".
4569 The exact key is that determined by the :attr:`_schema.Table.key`
4570 attribute;
4571 for a table with no :attr:`_schema.Table.schema` attribute,
4572 this is the same
4573 as :attr:`_schema.Table.name`. For a table with a schema,
4574 it is typically of the
4575 form ``schemaname.tablename``.
4577 .. seealso::
4579 :attr:`_schema.MetaData.sorted_tables`
4581 """
4583 def __repr__(self):
4584 if self.bind:
4585 return "MetaData(bind=%r)" % self.bind
4586 else:
4587 return "MetaData()"
4589 def __contains__(self, table_or_key):
4590 if not isinstance(table_or_key, util.string_types):
4591 table_or_key = table_or_key.key
4592 return table_or_key in self.tables
4594 def _add_table(self, name, schema, table):
4595 key = _get_table_key(name, schema)
4596 self.tables._insert_item(key, table)
4597 if schema:
4598 self._schemas.add(schema)
4600 def _remove_table(self, name, schema):
4601 key = _get_table_key(name, schema)
4602 removed = dict.pop(self.tables, key, None)
4603 if removed is not None:
4604 for fk in removed.foreign_keys:
4605 fk._remove_from_metadata(self)
4606 if self._schemas:
4607 self._schemas = set(
4608 [
4609 t.schema
4610 for t in self.tables.values()
4611 if t.schema is not None
4612 ]
4613 )
4615 def __getstate__(self):
4616 return {
4617 "tables": self.tables,
4618 "schema": self.schema,
4619 "schemas": self._schemas,
4620 "sequences": self._sequences,
4621 "fk_memos": self._fk_memos,
4622 "naming_convention": self.naming_convention,
4623 }
4625 def __setstate__(self, state):
4626 self.tables = state["tables"]
4627 self.schema = state["schema"]
4628 self.naming_convention = state["naming_convention"]
4629 self._bind = None
4630 self._sequences = state["sequences"]
4631 self._schemas = state["schemas"]
4632 self._fk_memos = state["fk_memos"]
4634 def is_bound(self):
4635 """True if this MetaData is bound to an Engine or Connection."""
4637 return self._bind is not None
4639 def bind(self):
4640 """An :class:`_engine.Engine` or :class:`_engine.Connection`
4641 to which this
4642 :class:`_schema.MetaData` is bound.
4644 Typically, a :class:`_engine.Engine` is assigned to this attribute
4645 so that "implicit execution" may be used, or alternatively
4646 as a means of providing engine binding information to an
4647 ORM :class:`.Session` object::
4649 engine = create_engine("someurl://")
4650 metadata.bind = engine
4652 .. deprecated :: 1.4
4654 The metadata.bind attribute, as part of the deprecated system
4655 of "implicit execution", is itself deprecated and will be
4656 removed in SQLAlchemy 2.0.
4658 .. seealso::
4660 :ref:`dbengine_implicit` - background on "bound metadata"
4662 """
4663 return self._bind
4665 @util.preload_module("sqlalchemy.engine.url")
4666 def _bind_to(self, bind):
4667 """Bind this MetaData to an Engine, Connection, string or URL."""
4668 url = util.preloaded.engine_url
4669 if isinstance(bind, util.string_types + (url.URL,)):
4670 self._bind = sqlalchemy.create_engine(bind)
4671 else:
4672 self._bind = bind
4674 bind = property(bind, _bind_to)
4676 def clear(self):
4677 """Clear all Table objects from this MetaData."""
4679 dict.clear(self.tables)
4680 self._schemas.clear()
4681 self._fk_memos.clear()
4683 def remove(self, table):
4684 """Remove the given Table object from this MetaData."""
4686 self._remove_table(table.name, table.schema)
4688 @property
4689 def sorted_tables(self):
4690 """Returns a list of :class:`_schema.Table` objects sorted in order of
4691 foreign key dependency.
4693 The sorting will place :class:`_schema.Table`
4694 objects that have dependencies
4695 first, before the dependencies themselves, representing the
4696 order in which they can be created. To get the order in which
4697 the tables would be dropped, use the ``reversed()`` Python built-in.
4699 .. warning::
4701 The :attr:`.MetaData.sorted_tables` attribute cannot by itself
4702 accommodate automatic resolution of dependency cycles between
4703 tables, which are usually caused by mutually dependent foreign key
4704 constraints. When these cycles are detected, the foreign keys
4705 of these tables are omitted from consideration in the sort.
4706 A warning is emitted when this condition occurs, which will be an
4707 exception raise in a future release. Tables which are not part
4708 of the cycle will still be returned in dependency order.
4710 To resolve these cycles, the
4711 :paramref:`_schema.ForeignKeyConstraint.use_alter` parameter may be
4712 applied to those constraints which create a cycle. Alternatively,
4713 the :func:`_schema.sort_tables_and_constraints` function will
4714 automatically return foreign key constraints in a separate
4715 collection when cycles are detected so that they may be applied
4716 to a schema separately.
4718 .. versionchanged:: 1.3.17 - a warning is emitted when
4719 :attr:`.MetaData.sorted_tables` cannot perform a proper sort
4720 due to cyclical dependencies. This will be an exception in a
4721 future release. Additionally, the sort will continue to return
4722 other tables not involved in the cycle in dependency order which
4723 was not the case previously.
4725 .. seealso::
4727 :func:`_schema.sort_tables`
4729 :func:`_schema.sort_tables_and_constraints`
4731 :attr:`_schema.MetaData.tables`
4733 :meth:`_reflection.Inspector.get_table_names`
4735 :meth:`_reflection.Inspector.get_sorted_table_and_fkc_names`
4738 """
4739 return ddl.sort_tables(
4740 sorted(self.tables.values(), key=lambda t: t.key)
4741 )
4743 def reflect(
4744 self,
4745 bind=None,
4746 schema=None,
4747 views=False,
4748 only=None,
4749 extend_existing=False,
4750 autoload_replace=True,
4751 resolve_fks=True,
4752 **dialect_kwargs
4753 ):
4754 r"""Load all available table definitions from the database.
4756 Automatically creates ``Table`` entries in this ``MetaData`` for any
4757 table available in the database but not yet present in the
4758 ``MetaData``. May be called multiple times to pick up tables recently
4759 added to the database, however no special action is taken if a table
4760 in this ``MetaData`` no longer exists in the database.
4762 :param bind:
4763 A :class:`.Connectable` used to access the database; if None, uses
4764 the existing bind on this ``MetaData``, if any.
4766 .. note:: the "bind" argument will be required in
4767 SQLAlchemy 2.0.
4769 :param schema:
4770 Optional, query and reflect tables from an alternate schema.
4771 If None, the schema associated with this :class:`_schema.MetaData`
4772 is used, if any.
4774 :param views:
4775 If True, also reflect views.
4777 :param only:
4778 Optional. Load only a sub-set of available named tables. May be
4779 specified as a sequence of names or a callable.
4781 If a sequence of names is provided, only those tables will be
4782 reflected. An error is raised if a table is requested but not
4783 available. Named tables already present in this ``MetaData`` are
4784 ignored.
4786 If a callable is provided, it will be used as a boolean predicate to
4787 filter the list of potential table names. The callable is called
4788 with a table name and this ``MetaData`` instance as positional
4789 arguments and should return a true value for any table to reflect.
4791 :param extend_existing: Passed along to each :class:`_schema.Table` as
4792 :paramref:`_schema.Table.extend_existing`.
4794 .. versionadded:: 0.9.1
4796 :param autoload_replace: Passed along to each :class:`_schema.Table`
4797 as
4798 :paramref:`_schema.Table.autoload_replace`.
4800 .. versionadded:: 0.9.1
4802 :param resolve_fks: if True, reflect :class:`_schema.Table`
4803 objects linked
4804 to :class:`_schema.ForeignKey` objects located in each
4805 :class:`_schema.Table`.
4806 For :meth:`_schema.MetaData.reflect`,
4807 this has the effect of reflecting
4808 related tables that might otherwise not be in the list of tables
4809 being reflected, for example if the referenced table is in a
4810 different schema or is omitted via the
4811 :paramref:`.MetaData.reflect.only` parameter. When False,
4812 :class:`_schema.ForeignKey` objects are not followed to the
4813 :class:`_schema.Table`
4814 in which they link, however if the related table is also part of the
4815 list of tables that would be reflected in any case, the
4816 :class:`_schema.ForeignKey` object will still resolve to its related
4817 :class:`_schema.Table` after the :meth:`_schema.MetaData.reflect`
4818 operation is
4819 complete. Defaults to True.
4821 .. versionadded:: 1.3.0
4823 .. seealso::
4825 :paramref:`_schema.Table.resolve_fks`
4827 :param \**dialect_kwargs: Additional keyword arguments not mentioned
4828 above are dialect specific, and passed in the form
4829 ``<dialectname>_<argname>``. See the documentation regarding an
4830 individual dialect at :ref:`dialect_toplevel` for detail on
4831 documented arguments.
4833 .. versionadded:: 0.9.2 - Added
4834 :paramref:`.MetaData.reflect.**dialect_kwargs` to support
4835 dialect-level reflection options for all :class:`_schema.Table`
4836 objects reflected.
4838 """
4839 if bind is None:
4840 bind = _bind_or_error(self)
4842 with inspection.inspect(bind)._inspection_context() as insp:
4843 reflect_opts = {
4844 "autoload_with": insp,
4845 "extend_existing": extend_existing,
4846 "autoload_replace": autoload_replace,
4847 "resolve_fks": resolve_fks,
4848 "_extend_on": set(),
4849 }
4851 reflect_opts.update(dialect_kwargs)
4853 if schema is None:
4854 schema = self.schema
4856 if schema is not None:
4857 reflect_opts["schema"] = schema
4859 available = util.OrderedSet(insp.get_table_names(schema))
4860 if views:
4861 available.update(insp.get_view_names(schema))
4863 if schema is not None:
4864 available_w_schema = util.OrderedSet(
4865 ["%s.%s" % (schema, name) for name in available]
4866 )
4867 else:
4868 available_w_schema = available
4870 current = set(self.tables)
4872 if only is None:
4873 load = [
4874 name
4875 for name, schname in zip(available, available_w_schema)
4876 if extend_existing or schname not in current
4877 ]
4878 elif callable(only):
4879 load = [
4880 name
4881 for name, schname in zip(available, available_w_schema)
4882 if (extend_existing or schname not in current)
4883 and only(name, self)
4884 ]
4885 else:
4886 missing = [name for name in only if name not in available]
4887 if missing:
4888 s = schema and (" schema '%s'" % schema) or ""
4889 raise exc.InvalidRequestError(
4890 "Could not reflect: requested table(s) not available "
4891 "in %r%s: (%s)" % (bind.engine, s, ", ".join(missing))
4892 )
4893 load = [
4894 name
4895 for name in only
4896 if extend_existing or name not in current
4897 ]
4899 for name in load:
4900 try:
4901 Table(name, self, **reflect_opts)
4902 except exc.UnreflectableTableError as uerr:
4903 util.warn("Skipping table %s: %s" % (name, uerr))
4905 def create_all(self, bind=None, tables=None, checkfirst=True):
4906 """Create all tables stored in this metadata.
4908 Conditional by default, will not attempt to recreate tables already
4909 present in the target database.
4911 :param bind:
4912 A :class:`.Connectable` used to access the
4913 database; if None, uses the existing bind on this ``MetaData``, if
4914 any.
4916 .. note:: the "bind" argument will be required in
4917 SQLAlchemy 2.0.
4919 :param tables:
4920 Optional list of ``Table`` objects, which is a subset of the total
4921 tables in the ``MetaData`` (others are ignored).
4923 :param checkfirst:
4924 Defaults to True, don't issue CREATEs for tables already present
4925 in the target database.
4927 """
4928 if bind is None:
4929 bind = _bind_or_error(self)
4930 bind._run_ddl_visitor(
4931 ddl.SchemaGenerator, self, checkfirst=checkfirst, tables=tables
4932 )
4934 def drop_all(self, bind=None, tables=None, checkfirst=True):
4935 """Drop all tables stored in this metadata.
4937 Conditional by default, will not attempt to drop tables not present in
4938 the target database.
4940 :param bind:
4941 A :class:`.Connectable` used to access the
4942 database; if None, uses the existing bind on this ``MetaData``, if
4943 any.
4945 .. note:: the "bind" argument will be required in
4946 SQLAlchemy 2.0.
4948 :param tables:
4949 Optional list of ``Table`` objects, which is a subset of the
4950 total tables in the ``MetaData`` (others are ignored).
4952 :param checkfirst:
4953 Defaults to True, only issue DROPs for tables confirmed to be
4954 present in the target database.
4956 """
4957 if bind is None:
4958 bind = _bind_or_error(self)
4959 bind._run_ddl_visitor(
4960 ddl.SchemaDropper, self, checkfirst=checkfirst, tables=tables
4961 )
4964@util.deprecated_cls(
4965 "1.4",
4966 ":class:`.ThreadLocalMetaData` is deprecated and will be removed "
4967 "in a future release.",
4968 constructor="__init__",
4969)
4970class ThreadLocalMetaData(MetaData):
4971 """A MetaData variant that presents a different ``bind`` in every thread.
4973 Makes the ``bind`` property of the MetaData a thread-local value, allowing
4974 this collection of tables to be bound to different ``Engine``
4975 implementations or connections in each thread.
4977 The ThreadLocalMetaData starts off bound to None in each thread. Binds
4978 must be made explicitly by assigning to the ``bind`` property or using
4979 ``connect()``. You can also re-bind dynamically multiple times per
4980 thread, just like a regular ``MetaData``.
4982 """
4984 __visit_name__ = "metadata"
4986 def __init__(self):
4987 """Construct a ThreadLocalMetaData."""
4989 self.context = util.threading.local()
4990 self.__engines = {}
4991 super(ThreadLocalMetaData, self).__init__()
4993 def bind(self):
4994 """The bound Engine or Connection for this thread.
4996 This property may be assigned an Engine or Connection, or assigned a
4997 string or URL to automatically create a basic Engine for this bind
4998 with ``create_engine()``."""
5000 return getattr(self.context, "_engine", None)
5002 @util.preload_module("sqlalchemy.engine.url")
5003 def _bind_to(self, bind):
5004 """Bind to a Connectable in the caller's thread."""
5005 url = util.preloaded.engine_url
5006 if isinstance(bind, util.string_types + (url.URL,)):
5007 try:
5008 self.context._engine = self.__engines[bind]
5009 except KeyError:
5010 e = sqlalchemy.create_engine(bind)
5011 self.__engines[bind] = e
5012 self.context._engine = e
5013 else:
5014 # TODO: this is squirrely. we shouldn't have to hold onto engines
5015 # in a case like this
5016 if bind not in self.__engines:
5017 self.__engines[bind] = bind
5018 self.context._engine = bind
5020 bind = property(bind, _bind_to)
5022 def is_bound(self):
5023 """True if there is a bind for this thread."""
5024 return (
5025 hasattr(self.context, "_engine")
5026 and self.context._engine is not None
5027 )
5029 def dispose(self):
5030 """Dispose all bound engines, in all thread contexts."""
5032 for e in self.__engines.values():
5033 if hasattr(e, "dispose"):
5034 e.dispose()
5037class Computed(FetchedValue, SchemaItem):
5038 """Defines a generated column, i.e. "GENERATED ALWAYS AS" syntax.
5040 The :class:`.Computed` construct is an inline construct added to the
5041 argument list of a :class:`_schema.Column` object::
5043 from sqlalchemy import Computed
5045 Table('square', metadata_obj,
5046 Column('side', Float, nullable=False),
5047 Column('area', Float, Computed('side * side'))
5048 )
5050 See the linked documentation below for complete details.
5052 .. versionadded:: 1.3.11
5054 .. seealso::
5056 :ref:`computed_ddl`
5058 """
5060 __visit_name__ = "computed_column"
5062 @_document_text_coercion(
5063 "sqltext", ":class:`.Computed`", ":paramref:`.Computed.sqltext`"
5064 )
5065 def __init__(self, sqltext, persisted=None):
5066 """Construct a GENERATED ALWAYS AS DDL construct to accompany a
5067 :class:`_schema.Column`.
5069 :param sqltext:
5070 A string containing the column generation expression, which will be
5071 used verbatim, or a SQL expression construct, such as a
5072 :func:`_expression.text`
5073 object. If given as a string, the object is converted to a
5074 :func:`_expression.text` object.
5076 :param persisted:
5077 Optional, controls how this column should be persisted by the
5078 database. Possible values are:
5080 * ``None``, the default, it will use the default persistence
5081 defined by the database.
5082 * ``True``, will render ``GENERATED ALWAYS AS ... STORED``, or the
5083 equivalent for the target database if supported.
5084 * ``False``, will render ``GENERATED ALWAYS AS ... VIRTUAL``, or
5085 the equivalent for the target database if supported.
5087 Specifying ``True`` or ``False`` may raise an error when the DDL
5088 is emitted to the target database if the database does not support
5089 that persistence option. Leaving this parameter at its default
5090 of ``None`` is guaranteed to succeed for all databases that support
5091 ``GENERATED ALWAYS AS``.
5093 """
5094 self.sqltext = coercions.expect(roles.DDLExpressionRole, sqltext)
5095 self.persisted = persisted
5096 self.column = None
5098 def _set_parent(self, parent, **kw):
5099 if not isinstance(
5100 parent.server_default, (type(None), Computed)
5101 ) or not isinstance(parent.server_onupdate, (type(None), Computed)):
5102 raise exc.ArgumentError(
5103 "A generated column cannot specify a server_default or a "
5104 "server_onupdate argument"
5105 )
5106 self.column = parent
5107 parent.computed = self
5108 self.column.server_onupdate = self
5109 self.column.server_default = self
5111 def _as_for_update(self, for_update):
5112 return self
5114 @util.deprecated(
5115 "1.4",
5116 "The :meth:`_schema.Computed.copy` method is deprecated "
5117 "and will be removed in a future release.",
5118 )
5119 def copy(self, target_table=None, **kw):
5120 return self._copy(target_table, **kw)
5122 def _copy(self, target_table=None, **kw):
5123 sqltext = _copy_expression(
5124 self.sqltext,
5125 self.column.table if self.column is not None else None,
5126 target_table,
5127 )
5128 g = Computed(sqltext, persisted=self.persisted)
5130 return self._schema_item_copy(g)
5133class Identity(IdentityOptions, FetchedValue, SchemaItem):
5134 """Defines an identity column, i.e. "GENERATED { ALWAYS | BY DEFAULT }
5135 AS IDENTITY" syntax.
5137 The :class:`.Identity` construct is an inline construct added to the
5138 argument list of a :class:`_schema.Column` object::
5140 from sqlalchemy import Identity
5142 Table('foo', metadata_obj,
5143 Column('id', Integer, Identity())
5144 Column('description', Text),
5145 )
5147 See the linked documentation below for complete details.
5149 .. versionadded:: 1.4
5151 .. seealso::
5153 :ref:`identity_ddl`
5155 """
5157 __visit_name__ = "identity_column"
5159 def __init__(
5160 self,
5161 always=False,
5162 on_null=None,
5163 start=None,
5164 increment=None,
5165 minvalue=None,
5166 maxvalue=None,
5167 nominvalue=None,
5168 nomaxvalue=None,
5169 cycle=None,
5170 cache=None,
5171 order=None,
5172 ):
5173 """Construct a GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY DDL
5174 construct to accompany a :class:`_schema.Column`.
5176 See the :class:`.Sequence` documentation for a complete description
5177 of most parameters.
5179 .. note::
5180 MSSQL supports this construct as the preferred alternative to
5181 generate an IDENTITY on a column, but it uses non standard
5182 syntax that only support :paramref:`_schema.Identity.start`
5183 and :paramref:`_schema.Identity.increment`.
5184 All other parameters are ignored.
5186 :param always:
5187 A boolean, that indicates the type of identity column.
5188 If ``False`` is specified, the default, then the user-specified
5189 value takes precedence.
5190 If ``True`` is specified, a user-specified value is not accepted (
5191 on some backends, like PostgreSQL, OVERRIDING SYSTEM VALUE, or
5192 similar, may be specified in an INSERT to override the sequence
5193 value).
5194 Some backends also have a default value for this parameter,
5195 ``None`` can be used to omit rendering this part in the DDL. It
5196 will be treated as ``False`` if a backend does not have a default
5197 value.
5199 :param on_null:
5200 Set to ``True`` to specify ON NULL in conjunction with a
5201 ``always=False`` identity column. This option is only supported on
5202 some backends, like Oracle.
5204 :param start: the starting index of the sequence.
5205 :param increment: the increment value of the sequence.
5206 :param minvalue: the minimum value of the sequence.
5207 :param maxvalue: the maximum value of the sequence.
5208 :param nominvalue: no minimum value of the sequence.
5209 :param nomaxvalue: no maximum value of the sequence.
5210 :param cycle: allows the sequence to wrap around when the maxvalue
5211 or minvalue has been reached.
5212 :param cache: optional integer value; number of future values in the
5213 sequence which are calculated in advance.
5214 :param order: optional boolean value; if true, renders the
5215 ORDER keyword.
5217 """
5218 IdentityOptions.__init__(
5219 self,
5220 start=start,
5221 increment=increment,
5222 minvalue=minvalue,
5223 maxvalue=maxvalue,
5224 nominvalue=nominvalue,
5225 nomaxvalue=nomaxvalue,
5226 cycle=cycle,
5227 cache=cache,
5228 order=order,
5229 )
5230 self.always = always
5231 self.on_null = on_null
5232 self.column = None
5234 def _set_parent(self, parent, **kw):
5235 if not isinstance(
5236 parent.server_default, (type(None), Identity)
5237 ) or not isinstance(parent.server_onupdate, type(None)):
5238 raise exc.ArgumentError(
5239 "A column with an Identity object cannot specify a "
5240 "server_default or a server_onupdate argument"
5241 )
5242 if parent.autoincrement is False:
5243 raise exc.ArgumentError(
5244 "A column with an Identity object cannot specify "
5245 "autoincrement=False"
5246 )
5247 self.column = parent
5249 parent.identity = self
5250 if parent._user_defined_nullable is NULL_UNSPECIFIED:
5251 parent.nullable = False
5253 parent.server_default = self
5255 def _as_for_update(self, for_update):
5256 return self
5258 @util.deprecated(
5259 "1.4",
5260 "The :meth:`_schema.Identity.copy` method is deprecated "
5261 "and will be removed in a future release.",
5262 )
5263 def copy(self, **kw):
5264 return self._copy(**kw)
5266 def _copy(self, **kw):
5267 i = Identity(
5268 always=self.always,
5269 on_null=self.on_null,
5270 start=self.start,
5271 increment=self.increment,
5272 minvalue=self.minvalue,
5273 maxvalue=self.maxvalue,
5274 nominvalue=self.nominvalue,
5275 nomaxvalue=self.nomaxvalue,
5276 cycle=self.cycle,
5277 cache=self.cache,
5278 order=self.order,
5279 )
5281 return self._schema_item_copy(i)