Shortcuts on this page
r m x toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1# sql/schema.py
2# Copyright (C) 2005-2021 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: http://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
34import operator
36import sqlalchemy
37from . import ddl
38from . import type_api
39from . import visitors
40from .base import _bind_or_error
41from .base import ColumnCollection
42from .base import DialectKWArgs
43from .base import SchemaEventTarget
44from .elements import _as_truncated
45from .elements import _document_text_coercion
46from .elements import _literal_as_text
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 .. import event
54from .. import exc
55from .. import inspection
56from .. import util
59RETAIN_SCHEMA = util.symbol("retain_schema")
61BLANK_SCHEMA = util.symbol(
62 "blank_schema",
63 """Symbol indicating that a :class:`_schema.Table` or :class:`.Sequence`
64 should have 'None' for its schema, even if the parent
65 :class:`_schema.MetaData` has specified a schema.
67 .. versionadded:: 1.0.14
69 """,
70)
73def _get_table_key(name, schema):
74 if schema is None:
75 return name
76 else:
77 return schema + "." + name
80# this should really be in sql/util.py but we'd have to
81# break an import cycle
82def _copy_expression(expression, source_table, target_table):
83 if source_table is None or target_table is None:
84 return expression
86 def replace(col):
87 if (
88 isinstance(col, Column)
89 and col.table is source_table
90 and col.key in source_table.c
91 ):
92 return target_table.c[col.key]
93 else:
94 return None
96 return visitors.replacement_traverse(expression, {}, replace)
99@inspection._self_inspects
100class SchemaItem(SchemaEventTarget, visitors.Visitable):
101 """Base class for items that define a database schema."""
103 __visit_name__ = "schema_item"
105 def _init_items(self, *args):
106 """Initialize the list of child items for this SchemaItem."""
108 for item in args:
109 if item is not None:
110 try:
111 spwd = item._set_parent_with_dispatch
112 except AttributeError as err:
113 util.raise_(
114 exc.ArgumentError(
115 "'SchemaItem' object, such as a 'Column' or a "
116 "'Constraint' expected, got %r" % item
117 ),
118 replace_context=err,
119 )
120 else:
121 spwd(self)
123 def get_children(self, **kwargs):
124 """used to allow SchemaVisitor access"""
125 return []
127 def __repr__(self):
128 return util.generic_repr(self, omit_kwarg=["info"])
130 @property
131 @util.deprecated(
132 "0.9",
133 "The :attr:`.SchemaItem.quote` attribute is deprecated and will be "
134 "removed in a future release. Use the :attr:`.quoted_name.quote` "
135 "attribute on the ``name`` field of the target schema item to retrieve"
136 "quoted status.",
137 )
138 def quote(self):
139 """Return the value of the ``quote`` flag passed
140 to this schema object, for those schema items which
141 have a ``name`` field.
143 """
145 return self.name.quote
147 @util.memoized_property
148 def info(self):
149 """Info dictionary associated with the object, allowing user-defined
150 data to be associated with this :class:`.SchemaItem`.
152 The dictionary is automatically generated when first accessed.
153 It can also be specified in the constructor of some objects,
154 such as :class:`_schema.Table` and :class:`_schema.Column`.
156 """
157 return {}
159 def _schema_item_copy(self, schema_item):
160 if "info" in self.__dict__:
161 schema_item.info = self.info.copy()
162 schema_item.dispatch._update(self.dispatch)
163 return schema_item
165 def _translate_schema(self, effective_schema, map_):
166 return map_.get(effective_schema, effective_schema)
169class Table(DialectKWArgs, SchemaItem, TableClause):
170 r"""Represent a table in a database.
172 e.g.::
174 mytable = Table("mytable", metadata,
175 Column('mytable_id', Integer, primary_key=True),
176 Column('value', String(50))
177 )
179 The :class:`_schema.Table`
180 object constructs a unique instance of itself based
181 on its name and optional schema name within the given
182 :class:`_schema.MetaData` object. Calling the :class:`_schema.Table`
183 constructor with the same name and same :class:`_schema.MetaData` argument
184 a second time will return the *same* :class:`_schema.Table`
185 object - in this way
186 the :class:`_schema.Table` constructor acts as a registry function.
188 .. seealso::
190 :ref:`metadata_describing` - Introduction to database metadata
192 Constructor arguments are as follows:
194 :param name: The name of this table as represented in the database.
196 The table name, along with the value of the ``schema`` parameter,
197 forms a key which uniquely identifies this :class:`_schema.Table`
198 within
199 the owning :class:`_schema.MetaData` collection.
200 Additional calls to :class:`_schema.Table` with the same name,
201 metadata,
202 and schema name will return the same :class:`_schema.Table` object.
204 Names which contain no upper case characters
205 will be treated as case insensitive names, and will not be quoted
206 unless they are a reserved word or contain special characters.
207 A name with any number of upper case characters is considered
208 to be case sensitive, and will be sent as quoted.
210 To enable unconditional quoting for the table name, specify the flag
211 ``quote=True`` to the constructor, or use the :class:`.quoted_name`
212 construct to specify the name.
214 :param metadata: a :class:`_schema.MetaData`
215 object which will contain this
216 table. The metadata is used as a point of association of this table
217 with other tables which are referenced via foreign key. It also
218 may be used to associate this table with a particular
219 :class:`.Connectable`.
221 :param \*args: Additional positional arguments are used primarily
222 to add the list of :class:`_schema.Column`
223 objects contained within this
224 table. Similar to the style of a CREATE TABLE statement, other
225 :class:`.SchemaItem` constructs may be added here, including
226 :class:`.PrimaryKeyConstraint`, and
227 :class:`_schema.ForeignKeyConstraint`.
229 :param autoload: Defaults to False, unless
230 :paramref:`_schema.Table.autoload_with`
231 is set in which case it defaults to True; :class:`_schema.Column`
232 objects
233 for this table should be reflected from the database, possibly
234 augmenting or replacing existing :class:`_schema.Column`
235 objects that were
236 explicitly specified.
238 .. versionchanged:: 1.0.0 setting the
239 :paramref:`_schema.Table.autoload_with`
240 parameter implies that :paramref:`_schema.Table.autoload`
241 will default
242 to True.
244 .. seealso::
246 :ref:`metadata_reflection_toplevel`
248 :param autoload_replace: Defaults to ``True``; when using
249 :paramref:`_schema.Table.autoload`
250 in conjunction with :paramref:`_schema.Table.extend_existing`,
251 indicates
252 that :class:`_schema.Column` objects present in the already-existing
253 :class:`_schema.Table`
254 object should be replaced with columns of the same
255 name retrieved from the autoload process. When ``False``, columns
256 already present under existing names will be omitted from the
257 reflection process.
259 Note that this setting does not impact :class:`_schema.Column` objects
260 specified programmatically within the call to :class:`_schema.Table`
261 that
262 also is autoloading; those :class:`_schema.Column` objects will always
263 replace existing columns of the same name when
264 :paramref:`_schema.Table.extend_existing` is ``True``.
266 .. seealso::
268 :paramref:`_schema.Table.autoload`
270 :paramref:`_schema.Table.extend_existing`
272 :param autoload_with: An :class:`_engine.Engine` or
273 :class:`_engine.Connection` object
274 with which this :class:`_schema.Table` object will be reflected; when
275 set to a non-None value, it implies that
276 :paramref:`_schema.Table.autoload`
277 is ``True``. If left unset, but :paramref:`_schema.Table.autoload`
278 is
279 explicitly set to ``True``, an autoload operation will attempt to
280 proceed by locating an :class:`_engine.Engine` or
281 :class:`_engine.Connection` bound
282 to the underlying :class:`_schema.MetaData` object.
284 .. seealso::
286 :paramref:`_schema.Table.autoload`
288 :param extend_existing: When ``True``, indicates that if this
289 :class:`_schema.Table` is already present in the given
290 :class:`_schema.MetaData`,
291 apply further arguments within the constructor to the existing
292 :class:`_schema.Table`.
294 If :paramref:`_schema.Table.extend_existing` or
295 :paramref:`_schema.Table.keep_existing` are not set,
296 and the given name
297 of the new :class:`_schema.Table` refers to a :class:`_schema.Table`
298 that is
299 already present in the target :class:`_schema.MetaData` collection,
300 and
301 this :class:`_schema.Table`
302 specifies additional columns or other constructs
303 or flags that modify the table's state, an
304 error is raised. The purpose of these two mutually-exclusive flags
305 is to specify what action should be taken when a
306 :class:`_schema.Table`
307 is specified that matches an existing :class:`_schema.Table`,
308 yet specifies
309 additional constructs.
311 :paramref:`_schema.Table.extend_existing`
312 will also work in conjunction
313 with :paramref:`_schema.Table.autoload` to run a new reflection
314 operation against the database, even if a :class:`_schema.Table`
315 of the same name is already present in the target
316 :class:`_schema.MetaData`; newly reflected :class:`_schema.Column`
317 objects
318 and other options will be added into the state of the
319 :class:`_schema.Table`, potentially overwriting existing columns
320 and options of the same name.
322 As is always the case with :paramref:`_schema.Table.autoload`,
323 :class:`_schema.Column` objects can be specified in the same
324 :class:`_schema.Table`
325 constructor, which will take precedence. Below, the existing
326 table ``mytable`` will be augmented with :class:`_schema.Column`
327 objects
328 both reflected from the database, as well as the given
329 :class:`_schema.Column`
330 named "y"::
332 Table("mytable", metadata,
333 Column('y', Integer),
334 extend_existing=True,
335 autoload=True,
336 autoload_with=engine
337 )
339 .. seealso::
341 :paramref:`_schema.Table.autoload`
343 :paramref:`_schema.Table.autoload_replace`
345 :paramref:`_schema.Table.keep_existing`
348 :param implicit_returning: True by default - indicates that
349 RETURNING can be used by default to fetch newly inserted primary key
350 values, for backends which support this. Note that
351 :func:`_sa.create_engine` also provides an ``implicit_returning``
352 flag.
354 :param include_columns: A list of strings indicating a subset of
355 columns to be loaded via the ``autoload`` operation; table columns who
356 aren't present in this list will not be represented on the resulting
357 ``Table`` object. Defaults to ``None`` which indicates all columns
358 should be reflected.
360 :param resolve_fks: Whether or not to reflect :class:`_schema.Table`
361 objects
362 related to this one via :class:`_schema.ForeignKey` objects, when
363 :paramref:`_schema.Table.autoload` or
364 :paramref:`_schema.Table.autoload_with` is
365 specified. Defaults to True. Set to False to disable reflection of
366 related tables as :class:`_schema.ForeignKey`
367 objects are encountered; may be
368 used either to save on SQL calls or to avoid issues with related tables
369 that can't be accessed. Note that if a related table is already present
370 in the :class:`_schema.MetaData` collection, or becomes present later,
371 a
372 :class:`_schema.ForeignKey` object associated with this
373 :class:`_schema.Table` will
374 resolve to that table normally.
376 .. versionadded:: 1.3
378 .. seealso::
380 :paramref:`.MetaData.reflect.resolve_fks`
383 :param info: Optional data dictionary which will be populated into the
384 :attr:`.SchemaItem.info` attribute of this object.
386 :param keep_existing: When ``True``, indicates that if this Table
387 is already present in the given :class:`_schema.MetaData`, ignore
388 further arguments within the constructor to the existing
389 :class:`_schema.Table`, and return the :class:`_schema.Table`
390 object as
391 originally created. This is to allow a function that wishes
392 to define a new :class:`_schema.Table` on first call, but on
393 subsequent calls will return the same :class:`_schema.Table`,
394 without any of the declarations (particularly constraints)
395 being applied a second time.
397 If :paramref:`_schema.Table.extend_existing` or
398 :paramref:`_schema.Table.keep_existing` are not set,
399 and the given name
400 of the new :class:`_schema.Table` refers to a :class:`_schema.Table`
401 that is
402 already present in the target :class:`_schema.MetaData` collection,
403 and
404 this :class:`_schema.Table`
405 specifies additional columns or other constructs
406 or flags that modify the table's state, an
407 error is raised. The purpose of these two mutually-exclusive flags
408 is to specify what action should be taken when a
409 :class:`_schema.Table`
410 is specified that matches an existing :class:`_schema.Table`,
411 yet specifies
412 additional constructs.
414 .. seealso::
416 :paramref:`_schema.Table.extend_existing`
418 :param listeners: A list of tuples of the form ``(<eventname>, <fn>)``
419 which will be passed to :func:`.event.listen` upon construction.
420 This alternate hook to :func:`.event.listen` allows the establishment
421 of a listener function specific to this :class:`_schema.Table` before
422 the "autoload" process begins. Particularly useful for
423 the :meth:`.DDLEvents.column_reflect` event::
425 def listen_for_reflect(table, column_info):
426 "handle the column reflection event"
427 # ...
429 t = Table(
430 'sometable',
431 autoload=True,
432 listeners=[
433 ('column_reflect', listen_for_reflect)
434 ])
436 :param mustexist: When ``True``, indicates that this Table must already
437 be present in the given :class:`_schema.MetaData` collection, else
438 an exception is raised.
440 :param prefixes:
441 A list of strings to insert after CREATE in the CREATE TABLE
442 statement. They will be separated by spaces.
444 :param quote: Force quoting of this table's name on or off, corresponding
445 to ``True`` or ``False``. When left at its default of ``None``,
446 the column identifier will be quoted according to whether the name is
447 case sensitive (identifiers with at least one upper case character are
448 treated as case sensitive), or if it's a reserved word. This flag
449 is only needed to force quoting of a reserved word which is not known
450 by the SQLAlchemy dialect.
452 :param quote_schema: same as 'quote' but applies to the schema identifier.
454 :param schema: The schema name for this table, which is required if
455 the table resides in a schema other than the default selected schema
456 for the engine's database connection. Defaults to ``None``.
458 If the owning :class:`_schema.MetaData` of this :class:`_schema.Table`
459 specifies its
460 own :paramref:`_schema.MetaData.schema` parameter,
461 then that schema name will
462 be applied to this :class:`_schema.Table`
463 if the schema parameter here is set
464 to ``None``. To set a blank schema name on a :class:`_schema.Table`
465 that
466 would otherwise use the schema set on the owning
467 :class:`_schema.MetaData`,
468 specify the special symbol :attr:`.BLANK_SCHEMA`.
470 .. versionadded:: 1.0.14 Added the :attr:`.BLANK_SCHEMA` symbol to
471 allow a :class:`_schema.Table`
472 to have a blank schema name even when the
473 parent :class:`_schema.MetaData` specifies
474 :paramref:`_schema.MetaData.schema`.
476 The quoting rules for the schema name are the same as those for the
477 ``name`` parameter, in that quoting is applied for reserved words or
478 case-sensitive names; to enable unconditional quoting for the schema
479 name, specify the flag ``quote_schema=True`` to the constructor, or use
480 the :class:`.quoted_name` construct to specify the name.
482 :param useexisting: the same as :paramref:`_schema.Table.extend_existing`.
484 :param comment: Optional string that will render an SQL comment on table
485 creation.
487 .. versionadded:: 1.2 Added the :paramref:`_schema.Table.comment`
488 parameter
489 to :class:`_schema.Table`.
491 :param \**kw: Additional keyword arguments not mentioned above are
492 dialect specific, and passed in the form ``<dialectname>_<argname>``.
493 See the documentation regarding an individual dialect at
494 :ref:`dialect_toplevel` for detail on documented arguments.
496 """
498 __visit_name__ = "table"
500 @util.deprecated_params(
501 useexisting=(
502 "0.7",
503 "The :paramref:`_schema.Table.useexisting` "
504 "parameter is deprecated and "
505 "will be removed in a future release. Please use "
506 ":paramref:`_schema.Table.extend_existing`.",
507 )
508 )
509 def __new__(cls, *args, **kw):
510 if not args and not kw:
511 # python3k pickle seems to call this
512 return object.__new__(cls)
514 try:
515 name, metadata, args = args[0], args[1], args[2:]
516 except IndexError:
517 raise TypeError(
518 "Table() takes at least two positional-only "
519 "arguments 'name' and 'metadata'"
520 )
522 schema = kw.get("schema", None)
523 if schema is None:
524 schema = metadata.schema
525 elif schema is BLANK_SCHEMA:
526 schema = None
527 keep_existing = kw.pop("keep_existing", False)
528 extend_existing = kw.pop("extend_existing", False)
529 if "useexisting" in kw:
530 if extend_existing:
531 msg = "useexisting is synonymous with extend_existing."
532 raise exc.ArgumentError(msg)
533 extend_existing = kw.pop("useexisting", False)
535 if keep_existing and extend_existing:
536 msg = "keep_existing and extend_existing are mutually exclusive."
537 raise exc.ArgumentError(msg)
539 mustexist = kw.pop("mustexist", False)
540 key = _get_table_key(name, schema)
541 if key in metadata.tables:
542 if not keep_existing and not extend_existing and bool(args):
543 raise exc.InvalidRequestError(
544 "Table '%s' is already defined for this MetaData "
545 "instance. Specify 'extend_existing=True' "
546 "to redefine "
547 "options and columns on an "
548 "existing Table object." % key
549 )
550 table = metadata.tables[key]
551 if extend_existing:
552 table._init_existing(*args, **kw)
553 return table
554 else:
555 if mustexist:
556 raise exc.InvalidRequestError("Table '%s' not defined" % (key))
557 table = object.__new__(cls)
558 table.dispatch.before_parent_attach(table, metadata)
559 metadata._add_table(name, schema, table)
560 try:
561 table._init(name, metadata, *args, **kw)
562 table.dispatch.after_parent_attach(table, metadata)
563 return table
564 except:
565 with util.safe_reraise():
566 metadata._remove_table(name, schema)
568 @property
569 @util.deprecated(
570 "0.9",
571 "The :meth:`.SchemaItem.quote` method is deprecated and will be "
572 "removed in a future release. Use the :attr:`.quoted_name.quote` "
573 "attribute on the ``schema`` field of the target schema item to "
574 "retrieve quoted status.",
575 )
576 def quote_schema(self):
577 """Return the value of the ``quote_schema`` flag passed
578 to this :class:`_schema.Table`.
579 """
581 return self.schema.quote
583 def __init__(self, *args, **kw):
584 """Constructor for :class:`_schema.Table`.
586 This method is a no-op. See the top-level
587 documentation for :class:`_schema.Table`
588 for constructor arguments.
590 """
591 # __init__ is overridden to prevent __new__ from
592 # calling the superclass constructor.
594 def _init(self, name, metadata, *args, **kwargs):
595 super(Table, self).__init__(
596 quoted_name(name, kwargs.pop("quote", None))
597 )
598 self.metadata = metadata
600 self.schema = kwargs.pop("schema", None)
601 if self.schema is None:
602 self.schema = metadata.schema
603 elif self.schema is BLANK_SCHEMA:
604 self.schema = None
605 else:
606 quote_schema = kwargs.pop("quote_schema", None)
607 self.schema = quoted_name(self.schema, quote_schema)
609 self.indexes = set()
610 self.constraints = set()
611 self._columns = ColumnCollection()
612 PrimaryKeyConstraint(
613 _implicit_generated=True
614 )._set_parent_with_dispatch(self)
615 self.foreign_keys = set()
616 self._extra_dependencies = set()
617 if self.schema is not None:
618 self.fullname = "%s.%s" % (self.schema, self.name)
619 else:
620 self.fullname = self.name
622 autoload_with = kwargs.pop("autoload_with", None)
623 autoload = kwargs.pop("autoload", autoload_with is not None)
624 # this argument is only used with _init_existing()
625 kwargs.pop("autoload_replace", True)
626 _extend_on = kwargs.pop("_extend_on", None)
628 resolve_fks = kwargs.pop("resolve_fks", True)
629 include_columns = kwargs.pop("include_columns", None)
631 self.implicit_returning = kwargs.pop("implicit_returning", True)
633 self.comment = kwargs.pop("comment", None)
635 if "info" in kwargs:
636 self.info = kwargs.pop("info")
637 if "listeners" in kwargs:
638 listeners = kwargs.pop("listeners")
639 for evt, fn in listeners:
640 event.listen(self, evt, fn)
642 self._prefixes = kwargs.pop("prefixes", [])
644 self._extra_kwargs(**kwargs)
646 # load column definitions from the database if 'autoload' is defined
647 # we do it after the table is in the singleton dictionary to support
648 # circular foreign keys
649 if autoload:
650 self._autoload(
651 metadata,
652 autoload_with,
653 include_columns,
654 _extend_on=_extend_on,
655 resolve_fks=resolve_fks,
656 )
658 # initialize all the column, etc. objects. done after reflection to
659 # allow user-overrides
660 self._init_items(*args)
662 def _autoload(
663 self,
664 metadata,
665 autoload_with,
666 include_columns,
667 exclude_columns=(),
668 resolve_fks=True,
669 _extend_on=None,
670 ):
672 if autoload_with:
673 autoload_with.run_callable(
674 autoload_with.dialect.reflecttable,
675 self,
676 include_columns,
677 exclude_columns,
678 resolve_fks,
679 _extend_on=_extend_on,
680 )
681 else:
682 bind = _bind_or_error(
683 metadata,
684 msg="No engine is bound to this Table's MetaData. "
685 "Pass an engine to the Table via "
686 "autoload_with=<someengine>, "
687 "or associate the MetaData with an engine via "
688 "metadata.bind=<someengine>",
689 )
690 bind.run_callable(
691 bind.dialect.reflecttable,
692 self,
693 include_columns,
694 exclude_columns,
695 resolve_fks,
696 _extend_on=_extend_on,
697 )
699 @property
700 def _sorted_constraints(self):
701 """Return the set of constraints as a list, sorted by creation
702 order.
704 """
705 return sorted(self.constraints, key=lambda c: c._creation_order)
707 @property
708 def foreign_key_constraints(self):
709 """:class:`_schema.ForeignKeyConstraint` objects referred to by this
710 :class:`_schema.Table`.
712 This list is produced from the collection of
713 :class:`_schema.ForeignKey`
714 objects currently associated.
716 .. versionadded:: 1.0.0
718 """
719 return set(fkc.constraint for fkc in self.foreign_keys)
721 def _init_existing(self, *args, **kwargs):
722 autoload_with = kwargs.pop("autoload_with", None)
723 autoload = kwargs.pop("autoload", autoload_with is not None)
724 autoload_replace = kwargs.pop("autoload_replace", True)
725 schema = kwargs.pop("schema", None)
726 _extend_on = kwargs.pop("_extend_on", None)
728 if schema and schema != self.schema:
729 raise exc.ArgumentError(
730 "Can't change schema of existing table from '%s' to '%s'",
731 (self.schema, schema),
732 )
734 include_columns = kwargs.pop("include_columns", None)
736 resolve_fks = kwargs.pop("resolve_fks", True)
738 if include_columns is not None:
739 for c in self.c:
740 if c.name not in include_columns:
741 self._columns.remove(c)
743 for key in ("quote", "quote_schema"):
744 if key in kwargs:
745 raise exc.ArgumentError(
746 "Can't redefine 'quote' or 'quote_schema' arguments"
747 )
749 if "comment" in kwargs:
750 self.comment = kwargs.pop("comment", None)
752 if "info" in kwargs:
753 self.info = kwargs.pop("info")
755 if autoload:
756 if not autoload_replace:
757 # don't replace columns already present.
758 # we'd like to do this for constraints also however we don't
759 # have simple de-duping for unnamed constraints.
760 exclude_columns = [c.name for c in self.c]
761 else:
762 exclude_columns = ()
763 self._autoload(
764 self.metadata,
765 autoload_with,
766 include_columns,
767 exclude_columns,
768 resolve_fks,
769 _extend_on=_extend_on,
770 )
772 self._extra_kwargs(**kwargs)
773 self._init_items(*args)
775 def _extra_kwargs(self, **kwargs):
776 self._validate_dialect_kwargs(kwargs)
778 def _init_collections(self):
779 pass
781 def _reset_exported(self):
782 pass
784 @property
785 def _autoincrement_column(self):
786 return self.primary_key._autoincrement_column
788 @property
789 def key(self):
790 """Return the 'key' for this :class:`_schema.Table`.
792 This value is used as the dictionary key within the
793 :attr:`_schema.MetaData.tables` collection. It is typically the same
794 as that of :attr:`_schema.Table.name` for a table with no
795 :attr:`_schema.Table.schema`
796 set; otherwise it is typically of the form
797 ``schemaname.tablename``.
799 """
800 return _get_table_key(self.name, self.schema)
802 def __repr__(self):
803 return "Table(%s)" % ", ".join(
804 [repr(self.name)]
805 + [repr(self.metadata)]
806 + [repr(x) for x in self.columns]
807 + ["%s=%s" % (k, repr(getattr(self, k))) for k in ["schema"]]
808 )
810 def __str__(self):
811 return _get_table_key(self.description, self.schema)
813 @property
814 def bind(self):
815 """Return the connectable associated with this Table."""
817 return self.metadata and self.metadata.bind or None
819 def add_is_dependent_on(self, table):
820 """Add a 'dependency' for this Table.
822 This is another Table object which must be created
823 first before this one can, or dropped after this one.
825 Usually, dependencies between tables are determined via
826 ForeignKey objects. However, for other situations that
827 create dependencies outside of foreign keys (rules, inheriting),
828 this method can manually establish such a link.
830 """
831 self._extra_dependencies.add(table)
833 def append_column(self, column):
834 """Append a :class:`_schema.Column` to this :class:`_schema.Table`.
836 The "key" of the newly added :class:`_schema.Column`, i.e. the
837 value of its ``.key`` attribute, will then be available
838 in the ``.c`` collection of this :class:`_schema.Table`, and the
839 column definition will be included in any CREATE TABLE, SELECT,
840 UPDATE, etc. statements generated from this :class:`_schema.Table`
841 construct.
843 Note that this does **not** change the definition of the table
844 as it exists within any underlying database, assuming that
845 table has already been created in the database. Relational
846 databases support the addition of columns to existing tables
847 using the SQL ALTER command, which would need to be
848 emitted for an already-existing table that doesn't contain
849 the newly added column.
851 """
853 column._set_parent_with_dispatch(self)
855 def append_constraint(self, constraint):
856 """Append a :class:`_schema.Constraint` to this
857 :class:`_schema.Table`.
859 This has the effect of the constraint being included in any
860 future CREATE TABLE statement, assuming specific DDL creation
861 events have not been associated with the given
862 :class:`_schema.Constraint` object.
864 Note that this does **not** produce the constraint within the
865 relational database automatically, for a table that already exists
866 in the database. To add a constraint to an
867 existing relational database table, the SQL ALTER command must
868 be used. SQLAlchemy also provides the
869 :class:`.AddConstraint` construct which can produce this SQL when
870 invoked as an executable clause.
872 """
874 constraint._set_parent_with_dispatch(self)
876 @util.deprecated(
877 "0.7",
878 "the :meth:`_schema.Table.append_ddl_listener` "
879 "method is deprecated and "
880 "will be removed in a future release. Please refer to "
881 ":class:`.DDLEvents`.",
882 )
883 def append_ddl_listener(self, event_name, listener):
884 """Append a DDL event listener to this ``Table``."""
886 def adapt_listener(target, connection, **kw):
887 listener(event_name, target, connection)
889 event.listen(self, "" + event_name.replace("-", "_"), adapt_listener)
891 def _set_parent(self, metadata):
892 metadata._add_table(self.name, self.schema, self)
893 self.metadata = metadata
895 def get_children(
896 self, column_collections=True, schema_visitor=False, **kw
897 ):
898 if not schema_visitor:
899 return TableClause.get_children(
900 self, column_collections=column_collections, **kw
901 )
902 else:
903 if column_collections:
904 return list(self.columns)
905 else:
906 return []
908 def exists(self, bind=None):
909 """Return True if this table exists."""
911 if bind is None:
912 bind = _bind_or_error(self)
914 return bind.run_callable(
915 bind.dialect.has_table, self.name, schema=self.schema
916 )
918 def create(self, bind=None, checkfirst=False):
919 """Issue a ``CREATE`` statement for this
920 :class:`_schema.Table`, using the given :class:`.Connectable`
921 for connectivity.
923 .. seealso::
925 :meth:`_schema.MetaData.create_all`.
927 """
929 if bind is None:
930 bind = _bind_or_error(self)
931 bind._run_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
933 def drop(self, bind=None, checkfirst=False):
934 """Issue a ``DROP`` statement for this
935 :class:`_schema.Table`, using the given :class:`.Connectable`
936 for connectivity.
938 .. seealso::
940 :meth:`_schema.MetaData.drop_all`.
942 """
943 if bind is None:
944 bind = _bind_or_error(self)
945 bind._run_visitor(ddl.SchemaDropper, self, checkfirst=checkfirst)
947 def tometadata(
948 self,
949 metadata,
950 schema=RETAIN_SCHEMA,
951 referred_schema_fn=None,
952 name=None,
953 ):
954 """Return a copy of this :class:`_schema.Table`
955 associated with a different
956 :class:`_schema.MetaData`.
958 E.g.::
960 m1 = MetaData()
962 user = Table('user', m1, Column('id', Integer, primary_key=True))
964 m2 = MetaData()
965 user_copy = user.tometadata(m2)
967 :param metadata: Target :class:`_schema.MetaData` object,
968 into which the
969 new :class:`_schema.Table` object will be created.
971 :param schema: optional string name indicating the target schema.
972 Defaults to the special symbol :attr:`.RETAIN_SCHEMA` which indicates
973 that no change to the schema name should be made in the new
974 :class:`_schema.Table`. If set to a string name, the new
975 :class:`_schema.Table`
976 will have this new name as the ``.schema``. If set to ``None``, the
977 schema will be set to that of the schema set on the target
978 :class:`_schema.MetaData`, which is typically ``None`` as well,
979 unless
980 set explicitly::
982 m2 = MetaData(schema='newschema')
984 # user_copy_one will have "newschema" as the schema name
985 user_copy_one = user.tometadata(m2, schema=None)
987 m3 = MetaData() # schema defaults to None
989 # user_copy_two will have None as the schema name
990 user_copy_two = user.tometadata(m3, schema=None)
992 :param referred_schema_fn: optional callable which can be supplied
993 in order to provide for the schema name that should be assigned
994 to the referenced table of a :class:`_schema.ForeignKeyConstraint`.
995 The callable accepts this parent :class:`_schema.Table`, the
996 target schema that we are changing to, the
997 :class:`_schema.ForeignKeyConstraint` object, and the existing
998 "target schema" of that constraint. The function should return the
999 string schema name that should be applied.
1000 E.g.::
1002 def referred_schema_fn(table, to_schema,
1003 constraint, referred_schema):
1004 if referred_schema == 'base_tables':
1005 return referred_schema
1006 else:
1007 return to_schema
1009 new_table = table.tometadata(m2, schema="alt_schema",
1010 referred_schema_fn=referred_schema_fn)
1012 .. versionadded:: 0.9.2
1014 :param name: optional string name indicating the target table name.
1015 If not specified or None, the table name is retained. This allows
1016 a :class:`_schema.Table` to be copied to the same
1017 :class:`_schema.MetaData` target
1018 with a new name.
1020 .. versionadded:: 1.0.0
1022 """
1023 if name is None:
1024 name = self.name
1025 if schema is RETAIN_SCHEMA:
1026 schema = self.schema
1027 elif schema is None:
1028 schema = metadata.schema
1029 key = _get_table_key(name, schema)
1030 if key in metadata.tables:
1031 util.warn(
1032 "Table '%s' already exists within the given "
1033 "MetaData - not copying." % self.description
1034 )
1035 return metadata.tables[key]
1037 args = []
1038 for c in self.columns:
1039 args.append(c.copy(schema=schema))
1040 table = Table(
1041 name,
1042 metadata,
1043 schema=schema,
1044 comment=self.comment,
1045 *args,
1046 **self.kwargs
1047 )
1048 for c in self.constraints:
1049 if isinstance(c, ForeignKeyConstraint):
1050 referred_schema = c._referred_schema
1051 if referred_schema_fn:
1052 fk_constraint_schema = referred_schema_fn(
1053 self, schema, c, referred_schema
1054 )
1055 else:
1056 fk_constraint_schema = (
1057 schema if referred_schema == self.schema else None
1058 )
1059 table.append_constraint(
1060 c.copy(schema=fk_constraint_schema, target_table=table)
1061 )
1062 elif not c._type_bound:
1063 # skip unique constraints that would be generated
1064 # by the 'unique' flag on Column
1065 if c._column_flag:
1066 continue
1068 table.append_constraint(
1069 c.copy(schema=schema, target_table=table)
1070 )
1071 for index in self.indexes:
1072 # skip indexes that would be generated
1073 # by the 'index' flag on Column
1074 if index._column_flag:
1075 continue
1076 Index(
1077 index.name,
1078 unique=index.unique,
1079 *[
1080 _copy_expression(expr, self, table)
1081 for expr in index.expressions
1082 ],
1083 _table=table,
1084 **index.kwargs
1085 )
1086 return self._schema_item_copy(table)
1089class Column(DialectKWArgs, SchemaItem, ColumnClause):
1090 """Represents a column in a database table."""
1092 __visit_name__ = "column"
1094 def __init__(self, *args, **kwargs):
1095 r"""
1096 Construct a new ``Column`` object.
1098 :param name: The name of this column as represented in the database.
1099 This argument may be the first positional argument, or specified
1100 via keyword.
1102 Names which contain no upper case characters
1103 will be treated as case insensitive names, and will not be quoted
1104 unless they are a reserved word. Names with any number of upper
1105 case characters will be quoted and sent exactly. Note that this
1106 behavior applies even for databases which standardize upper
1107 case names as case insensitive such as Oracle.
1109 The name field may be omitted at construction time and applied
1110 later, at any time before the Column is associated with a
1111 :class:`_schema.Table`. This is to support convenient
1112 usage within the :mod:`~sqlalchemy.ext.declarative` extension.
1114 :param type\_: The column's type, indicated using an instance which
1115 subclasses :class:`~sqlalchemy.types.TypeEngine`. If no arguments
1116 are required for the type, the class of the type can be sent
1117 as well, e.g.::
1119 # use a type with arguments
1120 Column('data', String(50))
1122 # use no arguments
1123 Column('level', Integer)
1125 The ``type`` argument may be the second positional argument
1126 or specified by keyword.
1128 If the ``type`` is ``None`` or is omitted, it will first default to
1129 the special type :class:`.NullType`. If and when this
1130 :class:`_schema.Column` is made to refer to another column using
1131 :class:`_schema.ForeignKey` and/or
1132 :class:`_schema.ForeignKeyConstraint`, the type
1133 of the remote-referenced column will be copied to this column as
1134 well, at the moment that the foreign key is resolved against that
1135 remote :class:`_schema.Column` object.
1137 .. versionchanged:: 0.9.0
1138 Support for propagation of type to a :class:`_schema.Column`
1139 from its
1140 :class:`_schema.ForeignKey` object has been improved and should be
1141 more reliable and timely.
1143 :param \*args: Additional positional arguments include various
1144 :class:`.SchemaItem` derived constructs which will be applied
1145 as options to the column. These include instances of
1146 :class:`.Constraint`, :class:`_schema.ForeignKey`,
1147 :class:`.ColumnDefault`,
1148 :class:`.Sequence`, :class:`.Computed`. In some cases an
1149 equivalent keyword argument is available such as ``server_default``,
1150 ``default`` and ``unique``.
1152 :param autoincrement: Set up "auto increment" semantics for an integer
1153 primary key column. The default value is the string ``"auto"``
1154 which indicates that a single-column primary key that is of
1155 an INTEGER type with no stated client-side or python-side defaults
1156 should receive auto increment semantics automatically;
1157 all other varieties of primary key columns will not. This
1158 includes that :term:`DDL` such as PostgreSQL SERIAL or MySQL
1159 AUTO_INCREMENT will be emitted for this column during a table
1160 create, as well as that the column is assumed to generate new
1161 integer primary key values when an INSERT statement invokes which
1162 will be retrieved by the dialect.
1164 The flag may be set to ``True`` to indicate that a column which
1165 is part of a composite (e.g. multi-column) primary key should
1166 have autoincrement semantics, though note that only one column
1167 within a primary key may have this setting. It can also
1168 be set to ``True`` to indicate autoincrement semantics on a
1169 column that has a client-side or server-side default configured,
1170 however note that not all dialects can accommodate all styles
1171 of default as an "autoincrement". It can also be
1172 set to ``False`` on a single-column primary key that has a
1173 datatype of INTEGER in order to disable auto increment semantics
1174 for that column.
1176 .. versionchanged:: 1.1 The autoincrement flag now defaults to
1177 ``"auto"`` which indicates autoincrement semantics by default
1178 for single-column integer primary keys only; for composite
1179 (multi-column) primary keys, autoincrement is never implicitly
1180 enabled; as always, ``autoincrement=True`` will allow for
1181 at most one of those columns to be an "autoincrement" column.
1182 ``autoincrement=True`` may also be set on a
1183 :class:`_schema.Column`
1184 that has an explicit client-side or server-side default,
1185 subject to limitations of the backend database and dialect.
1188 The setting *only* has an effect for columns which are:
1190 * Integer derived (i.e. INT, SMALLINT, BIGINT).
1192 * Part of the primary key
1194 * Not referring to another column via :class:`_schema.ForeignKey`,
1195 unless
1196 the value is specified as ``'ignore_fk'``::
1198 # turn on autoincrement for this column despite
1199 # the ForeignKey()
1200 Column('id', ForeignKey('other.id'),
1201 primary_key=True, autoincrement='ignore_fk')
1203 It is typically not desirable to have "autoincrement" enabled on a
1204 column that refers to another via foreign key, as such a column is
1205 required to refer to a value that originates from elsewhere.
1207 The setting has these two effects on columns that meet the
1208 above criteria:
1210 * DDL issued for the column will include database-specific
1211 keywords intended to signify this column as an
1212 "autoincrement" column, such as AUTO INCREMENT on MySQL,
1213 SERIAL on PostgreSQL, and IDENTITY on MS-SQL. It does
1214 *not* issue AUTOINCREMENT for SQLite since this is a
1215 special SQLite flag that is not required for autoincrementing
1216 behavior.
1218 .. seealso::
1220 :ref:`sqlite_autoincrement`
1222 * The column will be considered to be available using an
1223 "autoincrement" method specific to the backend database, such
1224 as calling upon ``cursor.lastrowid``, using RETURNING in an
1225 INSERT statement to get at a sequence-generated value, or using
1226 special functions such as "SELECT scope_identity()".
1227 These methods are highly specific to the DBAPIs and databases in
1228 use and vary greatly, so care should be taken when associating
1229 ``autoincrement=True`` with a custom default generation function.
1232 :param default: A scalar, Python callable, or
1233 :class:`_expression.ColumnElement` expression representing the
1234 *default value* for this column, which will be invoked upon insert
1235 if this column is otherwise not specified in the VALUES clause of
1236 the insert. This is a shortcut to using :class:`.ColumnDefault` as
1237 a positional argument; see that class for full detail on the
1238 structure of the argument.
1240 Contrast this argument to
1241 :paramref:`_schema.Column.server_default`
1242 which creates a default generator on the database side.
1244 .. seealso::
1246 :ref:`metadata_defaults_toplevel`
1248 :param doc: optional String that can be used by the ORM or similar
1249 to document attributes on the Python side. This attribute does
1250 **not** render SQL comments; use the
1251 :paramref:`_schema.Column.comment`
1252 parameter for this purpose.
1254 :param key: An optional string identifier which will identify this
1255 ``Column`` object on the :class:`_schema.Table`.
1256 When a key is provided,
1257 this is the only identifier referencing the ``Column`` within the
1258 application, including ORM attribute mapping; the ``name`` field
1259 is used only when rendering SQL.
1261 :param index: When ``True``, indicates that a :class:`_schema.Index`
1262 construct will be automatically generated for this
1263 :class:`_schema.Column`, which will result in a "CREATE INDEX"
1264 statement being emitted for the :class:`_schema.Table` when the DDL
1265 create operation is invoked.
1267 Using this flag is equivalent to making use of the
1268 :class:`_schema.Index` construct explicitly at the level of the
1269 :class:`_schema.Table` construct itself::
1271 Table(
1272 "some_table",
1273 metadata,
1274 Column("x", Integer),
1275 Index("ix_some_table_x", "x")
1276 )
1278 To add the :paramref:`_schema.Index.unique` flag to the
1279 :class:`_schema.Index`, set both the
1280 :paramref:`_schema.Column.unique` and
1281 :paramref:`_schema.Column.index` flags to True simultaneously,
1282 which will have the effect of rendering the "CREATE UNIQUE INDEX"
1283 DDL instruction instead of "CREATE INDEX".
1285 The name of the index is generated using the
1286 :ref:`default naming convention <constraint_default_naming_convention>`
1287 which for the :class:`_schema.Index` construct is of the form
1288 ``ix_<tablename>_<columnname>``.
1290 As this flag is intended only as a convenience for the common case
1291 of adding a single-column, default configured index to a table
1292 definition, explicit use of the :class:`_schema.Index` construct
1293 should be preferred for most use cases, including composite indexes
1294 that encompass more than one column, indexes with SQL expressions
1295 or ordering, backend-specific index configuration options, and
1296 indexes that use a specific name.
1298 .. note:: the :attr:`_schema.Column.index` attribute on
1299 :class:`_schema.Column`
1300 **does not indicate** if this column is indexed or not, only
1301 if this flag was explicitly set here. To view indexes on
1302 a column, view the :attr:`_schema.Table.indexes` collection
1303 or use :meth:`_reflection.Inspector.get_indexes`.
1305 .. seealso::
1307 :ref:`schema_indexes`
1309 :ref:`constraint_naming_conventions`
1311 :paramref:`_schema.Column.unique`
1313 :param info: Optional data dictionary which will be populated into the
1314 :attr:`.SchemaItem.info` attribute of this object.
1316 :param nullable: When set to ``False``, will cause the "NOT NULL"
1317 phrase to be added when generating DDL for the column. When
1318 ``True``, will normally generate nothing (in SQL this defaults to
1319 "NULL"), except in some very specific backend-specific edge cases
1320 where "NULL" may render explicitly. Defaults to ``True`` unless
1321 :paramref:`_schema.Column.primary_key` is also ``True``,
1322 in which case it
1323 defaults to ``False``. This parameter is only used when issuing
1324 CREATE TABLE statements.
1326 :param onupdate: A scalar, Python callable, or
1327 :class:`~sqlalchemy.sql.expression.ClauseElement` representing a
1328 default value to be applied to the column within UPDATE
1329 statements, which will be invoked upon update if this column is not
1330 present in the SET clause of the update. This is a shortcut to
1331 using :class:`.ColumnDefault` as a positional argument with
1332 ``for_update=True``.
1334 .. seealso::
1336 :ref:`metadata_defaults` - complete discussion of onupdate
1338 :param primary_key: If ``True``, marks this column as a primary key
1339 column. Multiple columns can have this flag set to specify
1340 composite primary keys. As an alternative, the primary key of a
1341 :class:`_schema.Table` can be specified via an explicit
1342 :class:`.PrimaryKeyConstraint` object.
1344 :param server_default: A :class:`.FetchedValue` instance, str, Unicode
1345 or :func:`~sqlalchemy.sql.expression.text` construct representing
1346 the DDL DEFAULT value for the column.
1348 String types will be emitted as-is, surrounded by single quotes::
1350 Column('x', Text, server_default="val")
1352 x TEXT DEFAULT 'val'
1354 A :func:`~sqlalchemy.sql.expression.text` expression will be
1355 rendered as-is, without quotes::
1357 Column('y', DateTime, server_default=text('NOW()'))
1359 y DATETIME DEFAULT NOW()
1361 Strings and text() will be converted into a
1362 :class:`.DefaultClause` object upon initialization.
1364 Use :class:`.FetchedValue` to indicate that an already-existing
1365 column will generate a default value on the database side which
1366 will be available to SQLAlchemy for post-fetch after inserts. This
1367 construct does not specify any DDL and the implementation is left
1368 to the database, such as via a trigger.
1370 .. seealso::
1372 :ref:`server_defaults` - complete discussion of server side
1373 defaults
1375 :param server_onupdate: A :class:`.FetchedValue` instance
1376 representing a database-side default generation function,
1377 such as a trigger. This
1378 indicates to SQLAlchemy that a newly generated value will be
1379 available after updates. This construct does not actually
1380 implement any kind of generation function within the database,
1381 which instead must be specified separately.
1384 .. warning:: This directive **does not** currently produce MySQL's
1385 "ON UPDATE CURRENT_TIMESTAMP()" clause. See
1386 :ref:`mysql_timestamp_onupdate` for background on how to
1387 produce this clause.
1389 .. seealso::
1391 :ref:`triggered_columns`
1393 :param quote: Force quoting of this column's name on or off,
1394 corresponding to ``True`` or ``False``. When left at its default
1395 of ``None``, the column identifier will be quoted according to
1396 whether the name is case sensitive (identifiers with at least one
1397 upper case character are treated as case sensitive), or if it's a
1398 reserved word. This flag is only needed to force quoting of a
1399 reserved word which is not known by the SQLAlchemy dialect.
1401 :param unique: When ``True``, and the :paramref:`_schema.Column.index`
1402 parameter is left at its default value of ``False``,
1403 indicates that a :class:`_schema.UniqueConstraint`
1404 construct will be automatically generated for this
1405 :class:`_schema.Column`,
1406 which will result in a "UNIQUE CONSTRAINT" clause referring
1407 to this column being included
1408 in the ``CREATE TABLE`` statement emitted, when the DDL create
1409 operation for the :class:`_schema.Table` object is invoked.
1411 When this flag is ``True`` while the
1412 :paramref:`_schema.Column.index` parameter is simultaneously
1413 set to ``True``, the effect instead is that a
1414 :class:`_schema.Index` construct which includes the
1415 :paramref:`_schema.Index.unique` parameter set to ``True``
1416 is generated. See the documentation for
1417 :paramref:`_schema.Column.index` for additional detail.
1419 Using this flag is equivalent to making use of the
1420 :class:`_schema.UniqueConstraint` construct explicitly at the
1421 level of the :class:`_schema.Table` construct itself::
1423 Table(
1424 "some_table",
1425 metadata,
1426 Column("x", Integer),
1427 UniqueConstraint("x")
1428 )
1430 The :paramref:`_schema.UniqueConstraint.name` parameter
1431 of the unique constraint object is left at its default value
1432 of ``None``; in the absence of a :ref:`naming convention <constraint_naming_conventions>`
1433 for the enclosing :class:`_schema.MetaData`, the UNIQUE CONSTRAINT
1434 construct will be emitted as unnamed, which typically invokes
1435 a database-specific naming convention to take place.
1437 As this flag is intended only as a convenience for the common case
1438 of adding a single-column, default configured unique constraint to a table
1439 definition, explicit use of the :class:`_schema.UniqueConstraint` construct
1440 should be preferred for most use cases, including composite constraints
1441 that encompass more than one column, backend-specific index configuration options, and
1442 constraints that use a specific name.
1444 .. note:: the :attr:`_schema.Column.unique` attribute on
1445 :class:`_schema.Column`
1446 **does not indicate** if this column has a unique constraint or
1447 not, only if this flag was explicitly set here. To view
1448 indexes and unique constraints that may involve this column,
1449 view the
1450 :attr:`_schema.Table.indexes` and/or
1451 :attr:`_schema.Table.constraints` collections or use
1452 :meth:`_reflection.Inspector.get_indexes` and/or
1453 :meth:`_reflection.Inspector.get_unique_constraints`
1455 .. seealso::
1457 :ref:`schema_unique_constraint`
1459 :ref:`constraint_naming_conventions`
1461 :paramref:`_schema.Column.index`
1463 :param system: When ``True``, indicates this is a "system" column,
1464 that is a column which is automatically made available by the
1465 database, and should not be included in the columns list for a
1466 ``CREATE TABLE`` statement.
1468 For more elaborate scenarios where columns should be
1469 conditionally rendered differently on different backends,
1470 consider custom compilation rules for :class:`.CreateColumn`.
1472 :param comment: Optional string that will render an SQL comment on
1473 table creation.
1475 .. versionadded:: 1.2 Added the
1476 :paramref:`_schema.Column.comment`
1477 parameter to :class:`_schema.Column`.
1480 """ # noqa E501
1482 name = kwargs.pop("name", None)
1483 type_ = kwargs.pop("type_", None)
1484 args = list(args)
1485 if args:
1486 if isinstance(args[0], util.string_types):
1487 if name is not None:
1488 raise exc.ArgumentError(
1489 "May not pass name positionally and as a keyword."
1490 )
1491 name = args.pop(0)
1492 if args:
1493 coltype = args[0]
1495 if hasattr(coltype, "_sqla_type"):
1496 if type_ is not None:
1497 raise exc.ArgumentError(
1498 "May not pass type_ positionally and as a keyword."
1499 )
1500 type_ = args.pop(0)
1502 if name is not None:
1503 name = quoted_name(name, kwargs.pop("quote", None))
1504 elif "quote" in kwargs:
1505 raise exc.ArgumentError(
1506 "Explicit 'name' is required when " "sending 'quote' argument"
1507 )
1509 super(Column, self).__init__(name, type_)
1510 self.key = kwargs.pop("key", name)
1511 self.primary_key = kwargs.pop("primary_key", False)
1512 self.nullable = kwargs.pop("nullable", not self.primary_key)
1513 self.default = kwargs.pop("default", None)
1514 self.server_default = kwargs.pop("server_default", None)
1515 self.server_onupdate = kwargs.pop("server_onupdate", None)
1517 # these default to None because .index and .unique is *not*
1518 # an informational flag about Column - there can still be an
1519 # Index or UniqueConstraint referring to this Column.
1520 self.index = kwargs.pop("index", None)
1521 self.unique = kwargs.pop("unique", None)
1523 self.system = kwargs.pop("system", False)
1524 self.doc = kwargs.pop("doc", None)
1525 self.onupdate = kwargs.pop("onupdate", None)
1526 self.autoincrement = kwargs.pop("autoincrement", "auto")
1527 self.constraints = set()
1528 self.foreign_keys = set()
1529 self.comment = kwargs.pop("comment", None)
1530 self.computed = None
1532 # check if this Column is proxying another column
1533 if "_proxies" in kwargs:
1534 self._proxies = kwargs.pop("_proxies")
1535 # otherwise, add DDL-related events
1536 elif isinstance(self.type, SchemaEventTarget):
1537 self.type._set_parent_with_dispatch(self)
1539 if self.default is not None:
1540 if isinstance(self.default, (ColumnDefault, Sequence)):
1541 args.append(self.default)
1542 else:
1543 if getattr(self.type, "_warn_on_bytestring", False):
1544 if isinstance(self.default, util.binary_type):
1545 util.warn(
1546 "Unicode column '%s' has non-unicode "
1547 "default value %r specified."
1548 % (self.key, self.default)
1549 )
1550 args.append(ColumnDefault(self.default))
1552 if self.server_default is not None:
1553 if isinstance(self.server_default, FetchedValue):
1554 args.append(self.server_default._as_for_update(False))
1555 else:
1556 args.append(DefaultClause(self.server_default))
1558 if self.onupdate is not None:
1559 if isinstance(self.onupdate, (ColumnDefault, Sequence)):
1560 args.append(self.onupdate)
1561 else:
1562 args.append(ColumnDefault(self.onupdate, for_update=True))
1564 if self.server_onupdate is not None:
1565 if isinstance(self.server_onupdate, FetchedValue):
1566 args.append(self.server_onupdate._as_for_update(True))
1567 else:
1568 args.append(
1569 DefaultClause(self.server_onupdate, for_update=True)
1570 )
1571 self._init_items(*args)
1573 util.set_creation_order(self)
1575 if "info" in kwargs:
1576 self.info = kwargs.pop("info")
1578 self._extra_kwargs(**kwargs)
1580 def _extra_kwargs(self, **kwargs):
1581 self._validate_dialect_kwargs(kwargs)
1583 # @property
1584 # def quote(self):
1585 # return getattr(self.name, "quote", None)
1587 def __str__(self):
1588 if self.name is None:
1589 return "(no name)"
1590 elif self.table is not None:
1591 if self.table.named_with_column:
1592 return self.table.description + "." + self.description
1593 else:
1594 return self.description
1595 else:
1596 return self.description
1598 def references(self, column):
1599 """Return True if this Column references the given column via foreign
1600 key."""
1602 for fk in self.foreign_keys:
1603 if fk.column.proxy_set.intersection(column.proxy_set):
1604 return True
1605 else:
1606 return False
1608 def append_foreign_key(self, fk):
1609 fk._set_parent_with_dispatch(self)
1611 def __repr__(self):
1612 kwarg = []
1613 if self.key != self.name:
1614 kwarg.append("key")
1615 if self.primary_key:
1616 kwarg.append("primary_key")
1617 if not self.nullable:
1618 kwarg.append("nullable")
1619 if self.onupdate:
1620 kwarg.append("onupdate")
1621 if self.default:
1622 kwarg.append("default")
1623 if self.server_default:
1624 kwarg.append("server_default")
1625 if self.comment:
1626 kwarg.append("comment")
1627 return "Column(%s)" % ", ".join(
1628 [repr(self.name)]
1629 + [repr(self.type)]
1630 + [repr(x) for x in self.foreign_keys if x is not None]
1631 + [repr(x) for x in self.constraints]
1632 + [
1633 (
1634 self.table is not None
1635 and "table=<%s>" % self.table.description
1636 or "table=None"
1637 )
1638 ]
1639 + ["%s=%s" % (k, repr(getattr(self, k))) for k in kwarg]
1640 )
1642 def _set_parent(self, table):
1643 if not self.name:
1644 raise exc.ArgumentError(
1645 "Column must be constructed with a non-blank name or "
1646 "assign a non-blank .name before adding to a Table."
1647 )
1648 if self.key is None:
1649 self.key = self.name
1651 existing = getattr(self, "table", None)
1652 if existing is not None and existing is not table:
1653 raise exc.ArgumentError(
1654 "Column object '%s' already assigned to Table '%s'"
1655 % (self.key, existing.description)
1656 )
1658 if self.key in table._columns:
1659 col = table._columns.get(self.key)
1660 if col is not self:
1661 for fk in col.foreign_keys:
1662 table.foreign_keys.remove(fk)
1663 if fk.constraint in table.constraints:
1664 # this might have been removed
1665 # already, if it's a composite constraint
1666 # and more than one col being replaced
1667 table.constraints.remove(fk.constraint)
1669 table._columns.replace(self)
1671 self.table = table
1673 if self.primary_key:
1674 table.primary_key._replace(self)
1675 elif self.key in table.primary_key:
1676 raise exc.ArgumentError(
1677 "Trying to redefine primary-key column '%s' as a "
1678 "non-primary-key column on table '%s'"
1679 % (self.key, table.fullname)
1680 )
1682 if self.index:
1683 if isinstance(self.index, util.string_types):
1684 raise exc.ArgumentError(
1685 "The 'index' keyword argument on Column is boolean only. "
1686 "To create indexes with a specific name, create an "
1687 "explicit Index object external to the Table."
1688 )
1689 table.append_constraint(
1690 Index(
1691 None, self.key, unique=bool(self.unique), _column_flag=True
1692 )
1693 )
1695 elif self.unique:
1696 if isinstance(self.unique, util.string_types):
1697 raise exc.ArgumentError(
1698 "The 'unique' keyword argument on Column is boolean "
1699 "only. To create unique constraints or indexes with a "
1700 "specific name, append an explicit UniqueConstraint to "
1701 "the Table's list of elements, or create an explicit "
1702 "Index object external to the Table."
1703 )
1704 table.append_constraint(
1705 UniqueConstraint(self.key, _column_flag=True)
1706 )
1708 self._setup_on_memoized_fks(lambda fk: fk._set_remote_table(table))
1710 def _setup_on_memoized_fks(self, fn):
1711 fk_keys = [
1712 ((self.table.key, self.key), False),
1713 ((self.table.key, self.name), True),
1714 ]
1715 for fk_key, link_to_name in fk_keys:
1716 if fk_key in self.table.metadata._fk_memos:
1717 for fk in self.table.metadata._fk_memos[fk_key]:
1718 if fk.link_to_name is link_to_name:
1719 fn(fk)
1721 def _on_table_attach(self, fn):
1722 if self.table is not None:
1723 fn(self, self.table)
1724 else:
1725 event.listen(self, "after_parent_attach", fn)
1727 def copy(self, **kw):
1728 """Create a copy of this ``Column``, uninitialized.
1730 This is used in :meth:`_schema.Table.tometadata`.
1732 """
1734 # Constraint objects plus non-constraint-bound ForeignKey objects
1735 args = [
1736 c.copy(**kw) for c in self.constraints if not c._type_bound
1737 ] + [c.copy(**kw) for c in self.foreign_keys if not c.constraint]
1739 # ticket #5276
1740 column_kwargs = {}
1741 for dialect_name in self.dialect_options:
1742 dialect_options = self.dialect_options[dialect_name]._non_defaults
1743 for (
1744 dialect_option_key,
1745 dialect_option_value,
1746 ) in dialect_options.items():
1747 column_kwargs[
1748 dialect_name + "_" + dialect_option_key
1749 ] = dialect_option_value
1751 server_default = self.server_default
1752 server_onupdate = self.server_onupdate
1753 if isinstance(server_default, Computed):
1754 server_default = server_onupdate = None
1755 args.append(self.server_default.copy(**kw))
1757 type_ = self.type
1758 if isinstance(type_, SchemaEventTarget):
1759 type_ = type_.copy(**kw)
1761 c = self._constructor(
1762 name=self.name,
1763 type_=type_,
1764 key=self.key,
1765 primary_key=self.primary_key,
1766 nullable=self.nullable,
1767 unique=self.unique,
1768 system=self.system,
1769 # quote=self.quote, # disabled 2013-08-27 (commit 031ef080)
1770 index=self.index,
1771 autoincrement=self.autoincrement,
1772 default=self.default,
1773 server_default=server_default,
1774 onupdate=self.onupdate,
1775 server_onupdate=server_onupdate,
1776 doc=self.doc,
1777 comment=self.comment,
1778 *args,
1779 **column_kwargs
1780 )
1781 return self._schema_item_copy(c)
1783 def _make_proxy(
1784 self, selectable, name=None, key=None, name_is_truncatable=False, **kw
1785 ):
1786 """Create a *proxy* for this column.
1788 This is a copy of this ``Column`` referenced by a different parent
1789 (such as an alias or select statement). The column should
1790 be used only in select scenarios, as its full DDL/default
1791 information is not transferred.
1793 """
1794 fk = [
1795 ForeignKey(f.column, _constraint=f.constraint)
1796 for f in self.foreign_keys
1797 ]
1798 if name is None and self.name is None:
1799 raise exc.InvalidRequestError(
1800 "Cannot initialize a sub-selectable"
1801 " with this Column object until its 'name' has "
1802 "been assigned."
1803 )
1804 try:
1805 c = self._constructor(
1806 _as_truncated(name or self.name)
1807 if name_is_truncatable
1808 else (name or self.name),
1809 self.type,
1810 key=key if key else name if name else self.key,
1811 primary_key=self.primary_key,
1812 nullable=self.nullable,
1813 _proxies=[self],
1814 *fk
1815 )
1816 except TypeError as err:
1817 util.raise_(
1818 TypeError(
1819 "Could not create a copy of this %r object. "
1820 "Ensure the class includes a _constructor() "
1821 "attribute or method which accepts the "
1822 "standard Column constructor arguments, or "
1823 "references the Column class itself." % self.__class__
1824 ),
1825 from_=err,
1826 )
1828 c.table = selectable
1829 selectable._columns.add(c)
1830 if selectable._is_clone_of is not None:
1831 c._is_clone_of = selectable._is_clone_of.columns.get(c.key)
1832 if self.primary_key:
1833 selectable.primary_key.add(c)
1834 c.dispatch.after_parent_attach(c, selectable)
1835 return c
1837 def get_children(self, schema_visitor=False, **kwargs):
1838 if schema_visitor:
1839 return (
1840 [x for x in (self.default, self.onupdate) if x is not None]
1841 + list(self.foreign_keys)
1842 + list(self.constraints)
1843 )
1844 else:
1845 return ColumnClause.get_children(self, **kwargs)
1848class ForeignKey(DialectKWArgs, SchemaItem):
1849 """Defines a dependency between two columns.
1851 ``ForeignKey`` is specified as an argument to a :class:`_schema.Column`
1852 object,
1853 e.g.::
1855 t = Table("remote_table", metadata,
1856 Column("remote_id", ForeignKey("main_table.id"))
1857 )
1859 Note that ``ForeignKey`` is only a marker object that defines
1860 a dependency between two columns. The actual constraint
1861 is in all cases represented by the :class:`_schema.ForeignKeyConstraint`
1862 object. This object will be generated automatically when
1863 a ``ForeignKey`` is associated with a :class:`_schema.Column` which
1864 in turn is associated with a :class:`_schema.Table`. Conversely,
1865 when :class:`_schema.ForeignKeyConstraint` is applied to a
1866 :class:`_schema.Table`,
1867 ``ForeignKey`` markers are automatically generated to be
1868 present on each associated :class:`_schema.Column`, which are also
1869 associated with the constraint object.
1871 Note that you cannot define a "composite" foreign key constraint,
1872 that is a constraint between a grouping of multiple parent/child
1873 columns, using ``ForeignKey`` objects. To define this grouping,
1874 the :class:`_schema.ForeignKeyConstraint` object must be used, and applied
1875 to the :class:`_schema.Table`. The associated ``ForeignKey`` objects
1876 are created automatically.
1878 The ``ForeignKey`` objects associated with an individual
1879 :class:`_schema.Column`
1880 object are available in the `foreign_keys` collection
1881 of that column.
1883 Further examples of foreign key configuration are in
1884 :ref:`metadata_foreignkeys`.
1886 """
1888 __visit_name__ = "foreign_key"
1890 def __init__(
1891 self,
1892 column,
1893 _constraint=None,
1894 use_alter=False,
1895 name=None,
1896 onupdate=None,
1897 ondelete=None,
1898 deferrable=None,
1899 initially=None,
1900 link_to_name=False,
1901 match=None,
1902 info=None,
1903 **dialect_kw
1904 ):
1905 r"""
1906 Construct a column-level FOREIGN KEY.
1908 The :class:`_schema.ForeignKey` object when constructed generates a
1909 :class:`_schema.ForeignKeyConstraint`
1910 which is associated with the parent
1911 :class:`_schema.Table` object's collection of constraints.
1913 :param column: A single target column for the key relationship. A
1914 :class:`_schema.Column` object or a column name as a string:
1915 ``tablename.columnkey`` or ``schema.tablename.columnkey``.
1916 ``columnkey`` is the ``key`` which has been assigned to the column
1917 (defaults to the column name itself), unless ``link_to_name`` is
1918 ``True`` in which case the rendered name of the column is used.
1920 :param name: Optional string. An in-database name for the key if
1921 `constraint` is not provided.
1923 :param onupdate: Optional string. If set, emit ON UPDATE <value> when
1924 issuing DDL for this constraint. Typical values include CASCADE,
1925 DELETE and RESTRICT.
1927 :param ondelete: Optional string. If set, emit ON DELETE <value> when
1928 issuing DDL for this constraint. Typical values include CASCADE,
1929 DELETE and RESTRICT.
1931 :param deferrable: Optional bool. If set, emit DEFERRABLE or NOT
1932 DEFERRABLE when issuing DDL for this constraint.
1934 :param initially: Optional string. If set, emit INITIALLY <value> when
1935 issuing DDL for this constraint.
1937 :param link_to_name: if True, the string name given in ``column`` is
1938 the rendered name of the referenced column, not its locally
1939 assigned ``key``.
1941 :param use_alter: passed to the underlying
1942 :class:`_schema.ForeignKeyConstraint`
1943 to indicate the constraint should
1944 be generated/dropped externally from the CREATE TABLE/ DROP TABLE
1945 statement. See :paramref:`_schema.ForeignKeyConstraint.use_alter`
1946 for further description.
1948 .. seealso::
1950 :paramref:`_schema.ForeignKeyConstraint.use_alter`
1952 :ref:`use_alter`
1954 :param match: Optional string. If set, emit MATCH <value> when issuing
1955 DDL for this constraint. Typical values include SIMPLE, PARTIAL
1956 and FULL.
1958 :param info: Optional data dictionary which will be populated into the
1959 :attr:`.SchemaItem.info` attribute of this object.
1961 .. versionadded:: 1.0.0
1963 :param \**dialect_kw: Additional keyword arguments are dialect
1964 specific, and passed in the form ``<dialectname>_<argname>``. The
1965 arguments are ultimately handled by a corresponding
1966 :class:`_schema.ForeignKeyConstraint`.
1967 See the documentation regarding
1968 an individual dialect at :ref:`dialect_toplevel` for detail on
1969 documented arguments.
1971 .. versionadded:: 0.9.2
1973 """
1975 self._colspec = column
1976 if isinstance(self._colspec, util.string_types):
1977 self._table_column = None
1978 else:
1979 if hasattr(self._colspec, "__clause_element__"):
1980 self._table_column = self._colspec.__clause_element__()
1981 else:
1982 self._table_column = self._colspec
1984 if not isinstance(self._table_column, ColumnClause):
1985 raise exc.ArgumentError(
1986 "String, Column, or Column-bound argument "
1987 "expected, got %r" % self._table_column
1988 )
1989 elif not isinstance(
1990 self._table_column.table, (util.NoneType, TableClause)
1991 ):
1992 raise exc.ArgumentError(
1993 "ForeignKey received Column not bound "
1994 "to a Table, got: %r" % self._table_column.table
1995 )
1997 # the linked ForeignKeyConstraint.
1998 # ForeignKey will create this when parent Column
1999 # is attached to a Table, *or* ForeignKeyConstraint
2000 # object passes itself in when creating ForeignKey
2001 # markers.
2002 self.constraint = _constraint
2003 self.parent = None
2004 self.use_alter = use_alter
2005 self.name = name
2006 self.onupdate = onupdate
2007 self.ondelete = ondelete
2008 self.deferrable = deferrable
2009 self.initially = initially
2010 self.link_to_name = link_to_name
2011 self.match = match
2012 if info:
2013 self.info = info
2014 self._unvalidated_dialect_kw = dialect_kw
2016 def __repr__(self):
2017 return "ForeignKey(%r)" % self._get_colspec()
2019 def copy(self, schema=None):
2020 """Produce a copy of this :class:`_schema.ForeignKey` object.
2022 The new :class:`_schema.ForeignKey` will not be bound
2023 to any :class:`_schema.Column`.
2025 This method is usually used by the internal
2026 copy procedures of :class:`_schema.Column`, :class:`_schema.Table`,
2027 and :class:`_schema.MetaData`.
2029 :param schema: The returned :class:`_schema.ForeignKey` will
2030 reference the original table and column name, qualified
2031 by the given string schema name.
2033 """
2035 fk = ForeignKey(
2036 self._get_colspec(schema=schema),
2037 use_alter=self.use_alter,
2038 name=self.name,
2039 onupdate=self.onupdate,
2040 ondelete=self.ondelete,
2041 deferrable=self.deferrable,
2042 initially=self.initially,
2043 link_to_name=self.link_to_name,
2044 match=self.match,
2045 **self._unvalidated_dialect_kw
2046 )
2047 return self._schema_item_copy(fk)
2049 def _get_colspec(self, schema=None, table_name=None):
2050 """Return a string based 'column specification' for this
2051 :class:`_schema.ForeignKey`.
2053 This is usually the equivalent of the string-based "tablename.colname"
2054 argument first passed to the object's constructor.
2056 """
2057 if schema:
2058 _schema, tname, colname = self._column_tokens
2059 if table_name is not None:
2060 tname = table_name
2061 return "%s.%s.%s" % (schema, tname, colname)
2062 elif table_name:
2063 schema, tname, colname = self._column_tokens
2064 if schema:
2065 return "%s.%s.%s" % (schema, table_name, colname)
2066 else:
2067 return "%s.%s" % (table_name, colname)
2068 elif self._table_column is not None:
2069 return "%s.%s" % (
2070 self._table_column.table.fullname,
2071 self._table_column.key,
2072 )
2073 else:
2074 return self._colspec
2076 @property
2077 def _referred_schema(self):
2078 return self._column_tokens[0]
2080 def _table_key(self):
2081 if self._table_column is not None:
2082 if self._table_column.table is None:
2083 return None
2084 else:
2085 return self._table_column.table.key
2086 else:
2087 schema, tname, colname = self._column_tokens
2088 return _get_table_key(tname, schema)
2090 target_fullname = property(_get_colspec)
2092 def references(self, table):
2093 """Return True if the given :class:`_schema.Table`
2094 is referenced by this
2095 :class:`_schema.ForeignKey`."""
2097 return table.corresponding_column(self.column) is not None
2099 def get_referent(self, table):
2100 """Return the :class:`_schema.Column` in the given
2101 :class:`_schema.Table`
2102 referenced by this :class:`_schema.ForeignKey`.
2104 Returns None if this :class:`_schema.ForeignKey`
2105 does not reference the given
2106 :class:`_schema.Table`.
2108 """
2110 return table.corresponding_column(self.column)
2112 @util.memoized_property
2113 def _column_tokens(self):
2114 """parse a string-based _colspec into its component parts."""
2116 m = self._get_colspec().split(".")
2117 if m is None:
2118 raise exc.ArgumentError(
2119 "Invalid foreign key column specification: %s" % self._colspec
2120 )
2121 if len(m) == 1:
2122 tname = m.pop()
2123 colname = None
2124 else:
2125 colname = m.pop()
2126 tname = m.pop()
2128 # A FK between column 'bar' and table 'foo' can be
2129 # specified as 'foo', 'foo.bar', 'dbo.foo.bar',
2130 # 'otherdb.dbo.foo.bar'. Once we have the column name and
2131 # the table name, treat everything else as the schema
2132 # name. Some databases (e.g. Sybase) support
2133 # inter-database foreign keys. See tickets#1341 and --
2134 # indirectly related -- Ticket #594. This assumes that '.'
2135 # will never appear *within* any component of the FK.
2137 if len(m) > 0:
2138 schema = ".".join(m)
2139 else:
2140 schema = None
2141 return schema, tname, colname
2143 def _resolve_col_tokens(self):
2144 if self.parent is None:
2145 raise exc.InvalidRequestError(
2146 "this ForeignKey object does not yet have a "
2147 "parent Column associated with it."
2148 )
2150 elif self.parent.table is None:
2151 raise exc.InvalidRequestError(
2152 "this ForeignKey's parent column is not yet associated "
2153 "with a Table."
2154 )
2156 parenttable = self.parent.table
2158 # assertion
2159 # basically Column._make_proxy() sends the actual
2160 # target Column to the ForeignKey object, so the
2161 # string resolution here is never called.
2162 for c in self.parent.base_columns:
2163 if isinstance(c, Column):
2164 assert c.table is parenttable
2165 break
2166 else:
2167 assert False
2168 ######################
2170 schema, tname, colname = self._column_tokens
2172 if schema is None and parenttable.metadata.schema is not None:
2173 schema = parenttable.metadata.schema
2175 tablekey = _get_table_key(tname, schema)
2176 return parenttable, tablekey, colname
2178 def _link_to_col_by_colstring(self, parenttable, table, colname):
2179 if not hasattr(self.constraint, "_referred_table"):
2180 self.constraint._referred_table = table
2181 else:
2182 assert self.constraint._referred_table is table
2184 _column = None
2185 if colname is None:
2186 # colname is None in the case that ForeignKey argument
2187 # was specified as table name only, in which case we
2188 # match the column name to the same column on the
2189 # parent.
2190 key = self.parent
2191 _column = table.c.get(self.parent.key, None)
2192 elif self.link_to_name:
2193 key = colname
2194 for c in table.c:
2195 if c.name == colname:
2196 _column = c
2197 else:
2198 key = colname
2199 _column = table.c.get(colname, None)
2201 if _column is None:
2202 raise exc.NoReferencedColumnError(
2203 "Could not initialize target column "
2204 "for ForeignKey '%s' on table '%s': "
2205 "table '%s' has no column named '%s'"
2206 % (self._colspec, parenttable.name, table.name, key),
2207 table.name,
2208 key,
2209 )
2211 self._set_target_column(_column)
2213 def _set_target_column(self, column):
2214 # propagate TypeEngine to parent if it didn't have one
2215 if self.parent.type._isnull:
2216 self.parent.type = column.type
2218 # super-edgy case, if other FKs point to our column,
2219 # they'd get the type propagated out also.
2220 if isinstance(self.parent.table, Table):
2222 def set_type(fk):
2223 if fk.parent.type._isnull:
2224 fk.parent.type = column.type
2226 self.parent._setup_on_memoized_fks(set_type)
2228 self.column = column
2230 @util.memoized_property
2231 def column(self):
2232 """Return the target :class:`_schema.Column` referenced by this
2233 :class:`_schema.ForeignKey`.
2235 If no target column has been established, an exception
2236 is raised.
2238 .. versionchanged:: 0.9.0
2239 Foreign key target column resolution now occurs as soon as both
2240 the ForeignKey object and the remote Column to which it refers
2241 are both associated with the same MetaData object.
2243 """
2245 if isinstance(self._colspec, util.string_types):
2247 parenttable, tablekey, colname = self._resolve_col_tokens()
2249 if tablekey not in parenttable.metadata:
2250 raise exc.NoReferencedTableError(
2251 "Foreign key associated with column '%s' could not find "
2252 "table '%s' with which to generate a "
2253 "foreign key to target column '%s'"
2254 % (self.parent, tablekey, colname),
2255 tablekey,
2256 )
2257 elif parenttable.key not in parenttable.metadata:
2258 raise exc.InvalidRequestError(
2259 "Table %s is no longer associated with its "
2260 "parent MetaData" % parenttable
2261 )
2262 else:
2263 raise exc.NoReferencedColumnError(
2264 "Could not initialize target column for "
2265 "ForeignKey '%s' on table '%s': "
2266 "table '%s' has no column named '%s'"
2267 % (self._colspec, parenttable.name, tablekey, colname),
2268 tablekey,
2269 colname,
2270 )
2271 elif hasattr(self._colspec, "__clause_element__"):
2272 _column = self._colspec.__clause_element__()
2273 return _column
2274 else:
2275 _column = self._colspec
2276 return _column
2278 def _set_parent(self, column):
2279 if self.parent is not None and self.parent is not column:
2280 raise exc.InvalidRequestError(
2281 "This ForeignKey already has a parent !"
2282 )
2283 self.parent = column
2284 self.parent.foreign_keys.add(self)
2285 self.parent._on_table_attach(self._set_table)
2287 def _set_remote_table(self, table):
2288 parenttable, tablekey, colname = self._resolve_col_tokens()
2289 self._link_to_col_by_colstring(parenttable, table, colname)
2290 self.constraint._validate_dest_table(table)
2292 def _remove_from_metadata(self, metadata):
2293 parenttable, table_key, colname = self._resolve_col_tokens()
2294 fk_key = (table_key, colname)
2296 if self in metadata._fk_memos[fk_key]:
2297 # TODO: no test coverage for self not in memos
2298 metadata._fk_memos[fk_key].remove(self)
2300 def _set_table(self, column, table):
2301 # standalone ForeignKey - create ForeignKeyConstraint
2302 # on the hosting Table when attached to the Table.
2303 if self.constraint is None and isinstance(table, Table):
2304 self.constraint = ForeignKeyConstraint(
2305 [],
2306 [],
2307 use_alter=self.use_alter,
2308 name=self.name,
2309 onupdate=self.onupdate,
2310 ondelete=self.ondelete,
2311 deferrable=self.deferrable,
2312 initially=self.initially,
2313 match=self.match,
2314 **self._unvalidated_dialect_kw
2315 )
2316 self.constraint._append_element(column, self)
2317 self.constraint._set_parent_with_dispatch(table)
2318 table.foreign_keys.add(self)
2320 # set up remote ".column" attribute, or a note to pick it
2321 # up when the other Table/Column shows up
2322 if isinstance(self._colspec, util.string_types):
2323 parenttable, table_key, colname = self._resolve_col_tokens()
2324 fk_key = (table_key, colname)
2325 if table_key in parenttable.metadata.tables:
2326 table = parenttable.metadata.tables[table_key]
2327 try:
2328 self._link_to_col_by_colstring(parenttable, table, colname)
2329 except exc.NoReferencedColumnError:
2330 # this is OK, we'll try later
2331 pass
2332 parenttable.metadata._fk_memos[fk_key].append(self)
2333 elif hasattr(self._colspec, "__clause_element__"):
2334 _column = self._colspec.__clause_element__()
2335 self._set_target_column(_column)
2336 else:
2337 _column = self._colspec
2338 self._set_target_column(_column)
2341class _NotAColumnExpr(object):
2342 def _not_a_column_expr(self):
2343 raise exc.InvalidRequestError(
2344 "This %s cannot be used directly "
2345 "as a column expression." % self.__class__.__name__
2346 )
2348 __clause_element__ = self_group = lambda self: self._not_a_column_expr()
2349 _from_objects = property(lambda self: self._not_a_column_expr())
2352class DefaultGenerator(_NotAColumnExpr, SchemaItem):
2353 """Base class for column *default* values."""
2355 __visit_name__ = "default_generator"
2357 is_sequence = False
2358 is_server_default = False
2359 column = None
2361 def __init__(self, for_update=False):
2362 self.for_update = for_update
2364 def _set_parent(self, column):
2365 self.column = column
2366 if self.for_update:
2367 self.column.onupdate = self
2368 else:
2369 self.column.default = self
2371 def execute(self, bind=None, **kwargs):
2372 if bind is None:
2373 bind = _bind_or_error(self)
2374 return bind.execute(self, **kwargs)
2376 def _execute_on_connection(self, connection, multiparams, params):
2377 return connection._execute_default(self, multiparams, params)
2379 @property
2380 def bind(self):
2381 """Return the connectable associated with this default."""
2382 if getattr(self, "column", None) is not None:
2383 return self.column.table.bind
2384 else:
2385 return None
2388class ColumnDefault(DefaultGenerator):
2389 """A plain default value on a column.
2391 This could correspond to a constant, a callable function,
2392 or a SQL clause.
2394 :class:`.ColumnDefault` is generated automatically
2395 whenever the ``default``, ``onupdate`` arguments of
2396 :class:`_schema.Column` are used. A :class:`.ColumnDefault`
2397 can be passed positionally as well.
2399 For example, the following::
2401 Column('foo', Integer, default=50)
2403 Is equivalent to::
2405 Column('foo', Integer, ColumnDefault(50))
2408 """
2410 def __init__(self, arg, **kwargs):
2411 """Construct a new :class:`.ColumnDefault`.
2414 :param arg: argument representing the default value.
2415 May be one of the following:
2417 * a plain non-callable Python value, such as a
2418 string, integer, boolean, or other simple type.
2419 The default value will be used as is each time.
2420 * a SQL expression, that is one which derives from
2421 :class:`_expression.ColumnElement`. The SQL expression will
2422 be rendered into the INSERT or UPDATE statement,
2423 or in the case of a primary key column when
2424 RETURNING is not used may be
2425 pre-executed before an INSERT within a SELECT.
2426 * A Python callable. The function will be invoked for each
2427 new row subject to an INSERT or UPDATE.
2428 The callable must accept exactly
2429 zero or one positional arguments. The one-argument form
2430 will receive an instance of the :class:`.ExecutionContext`,
2431 which provides contextual information as to the current
2432 :class:`_engine.Connection` in use as well as the current
2433 statement and parameters.
2435 """
2436 super(ColumnDefault, self).__init__(**kwargs)
2437 if isinstance(arg, FetchedValue):
2438 raise exc.ArgumentError(
2439 "ColumnDefault may not be a server-side default type."
2440 )
2441 if util.callable(arg):
2442 arg = self._maybe_wrap_callable(arg)
2443 self.arg = arg
2445 @util.memoized_property
2446 def is_callable(self):
2447 return util.callable(self.arg)
2449 @util.memoized_property
2450 def is_clause_element(self):
2451 return isinstance(self.arg, ClauseElement)
2453 @util.memoized_property
2454 def is_scalar(self):
2455 return (
2456 not self.is_callable
2457 and not self.is_clause_element
2458 and not self.is_sequence
2459 )
2461 @util.memoized_property
2462 @util.dependencies("sqlalchemy.sql.sqltypes")
2463 def _arg_is_typed(self, sqltypes):
2464 if self.is_clause_element:
2465 return not isinstance(self.arg.type, sqltypes.NullType)
2466 else:
2467 return False
2469 def _maybe_wrap_callable(self, fn):
2470 """Wrap callables that don't accept a context.
2472 This is to allow easy compatibility with default callables
2473 that aren't specific to accepting of a context.
2475 """
2476 try:
2477 argspec = util.get_callable_argspec(fn, no_self=True)
2478 except TypeError:
2479 return util.wrap_callable(lambda ctx: fn(), fn)
2481 defaulted = argspec[3] is not None and len(argspec[3]) or 0
2482 positionals = len(argspec[0]) - defaulted
2484 if positionals == 0:
2485 return util.wrap_callable(lambda ctx: fn(), fn)
2487 elif positionals == 1:
2488 return fn
2489 else:
2490 raise exc.ArgumentError(
2491 "ColumnDefault Python function takes zero or one "
2492 "positional arguments"
2493 )
2495 def _visit_name(self):
2496 if self.for_update:
2497 return "column_onupdate"
2498 else:
2499 return "column_default"
2501 __visit_name__ = property(_visit_name)
2503 def __repr__(self):
2504 return "ColumnDefault(%r)" % (self.arg,)
2507class IdentityOptions(object):
2508 """Defines options for a named database sequence or an identity column.
2510 .. versionadded:: 1.3.18
2512 .. seealso::
2514 :class:`.Sequence`
2516 """
2518 def __init__(
2519 self,
2520 start=None,
2521 increment=None,
2522 minvalue=None,
2523 maxvalue=None,
2524 nominvalue=None,
2525 nomaxvalue=None,
2526 cycle=None,
2527 cache=None,
2528 order=None,
2529 ):
2530 """Construct a :class:`.IdentityOptions` object.
2532 See the :class:`.Sequence` documentation for a complete description
2533 of the parameters
2535 :param start: the starting index of the sequence.
2536 :param increment: the increment value of the sequence.
2537 :param minvalue: the minimum value of the sequence.
2538 :param maxvalue: the maximum value of the sequence.
2539 :param nominvalue: no minimum value of the sequence.
2540 :param nomaxvalue: no maximum value of the sequence.
2541 :param cycle: allows the sequence to wrap around when the maxvalue
2542 or minvalue has been reached.
2543 :param cache: optional integer value; number of future values in the
2544 sequence which are calculated in advance.
2545 :param order: optional boolean value; if ``True``, renders the
2546 ORDER keyword.
2547 name.
2548 """
2549 self.start = start
2550 self.increment = increment
2551 self.minvalue = minvalue
2552 self.maxvalue = maxvalue
2553 self.nominvalue = nominvalue
2554 self.nomaxvalue = nomaxvalue
2555 self.cycle = cycle
2556 self.cache = cache
2557 self.order = order
2560class Sequence(IdentityOptions, DefaultGenerator):
2561 """Represents a named database sequence.
2563 The :class:`.Sequence` object represents the name and configurational
2564 parameters of a database sequence. It also represents
2565 a construct that can be "executed" by a SQLAlchemy :class:`_engine.Engine`
2566 or :class:`_engine.Connection`,
2567 rendering the appropriate "next value" function
2568 for the target database and returning a result.
2570 The :class:`.Sequence` is typically associated with a primary key column::
2572 some_table = Table(
2573 'some_table', metadata,
2574 Column('id', Integer, Sequence('some_table_seq'),
2575 primary_key=True)
2576 )
2578 When CREATE TABLE is emitted for the above :class:`_schema.Table`, if the
2579 target platform supports sequences, a CREATE SEQUENCE statement will
2580 be emitted as well. For platforms that don't support sequences,
2581 the :class:`.Sequence` construct is ignored.
2583 .. seealso::
2585 :class:`.CreateSequence`
2587 :class:`.DropSequence`
2589 """
2591 __visit_name__ = "sequence"
2593 is_sequence = True
2595 def __init__(
2596 self,
2597 name,
2598 start=None,
2599 increment=None,
2600 minvalue=None,
2601 maxvalue=None,
2602 nominvalue=None,
2603 nomaxvalue=None,
2604 cycle=None,
2605 schema=None,
2606 cache=None,
2607 order=None,
2608 optional=False,
2609 quote=None,
2610 metadata=None,
2611 quote_schema=None,
2612 for_update=False,
2613 ):
2614 """Construct a :class:`.Sequence` object.
2616 :param name: the name of the sequence.
2617 :param start: the starting index of the sequence. This value is
2618 used when the CREATE SEQUENCE command is emitted to the database
2619 as the value of the "START WITH" clause. If ``None``, the
2620 clause is omitted, which on most platforms indicates a starting
2621 value of 1.
2622 :param increment: the increment value of the sequence. This
2623 value is used when the CREATE SEQUENCE command is emitted to
2624 the database as the value of the "INCREMENT BY" clause. If ``None``,
2625 the clause is omitted, which on most platforms indicates an
2626 increment of 1.
2627 :param minvalue: the minimum value of the sequence. This
2628 value is used when the CREATE SEQUENCE command is emitted to
2629 the database as the value of the "MINVALUE" clause. If ``None``,
2630 the clause is omitted, which on most platforms indicates a
2631 minvalue of 1 and -2^63-1 for ascending and descending sequences,
2632 respectively.
2634 .. versionadded:: 1.0.7
2636 :param maxvalue: the maximum value of the sequence. This
2637 value is used when the CREATE SEQUENCE command is emitted to
2638 the database as the value of the "MAXVALUE" clause. If ``None``,
2639 the clause is omitted, which on most platforms indicates a
2640 maxvalue of 2^63-1 and -1 for ascending and descending sequences,
2641 respectively.
2643 .. versionadded:: 1.0.7
2645 :param nominvalue: no minimum value of the sequence. This
2646 value is used when the CREATE SEQUENCE command is emitted to
2647 the database as the value of the "NO MINVALUE" clause. If ``None``,
2648 the clause is omitted, which on most platforms indicates a
2649 minvalue of 1 and -2^63-1 for ascending and descending sequences,
2650 respectively.
2652 .. versionadded:: 1.0.7
2654 :param nomaxvalue: no maximum value of the sequence. This
2655 value is used when the CREATE SEQUENCE command is emitted to
2656 the database as the value of the "NO MAXVALUE" clause. If ``None``,
2657 the clause is omitted, which on most platforms indicates a
2658 maxvalue of 2^63-1 and -1 for ascending and descending sequences,
2659 respectively.
2661 .. versionadded:: 1.0.7
2663 :param cycle: allows the sequence to wrap around when the maxvalue
2664 or minvalue has been reached by an ascending or descending sequence
2665 respectively. This value is used when the CREATE SEQUENCE command
2666 is emitted to the database as the "CYCLE" clause. If the limit is
2667 reached, the next number generated will be the minvalue or maxvalue,
2668 respectively. If cycle=False (the default) any calls to nextval
2669 after the sequence has reached its maximum value will return an
2670 error.
2672 .. versionadded:: 1.0.7
2674 :param schema: optional schema name for the sequence, if located
2675 in a schema other than the default. The rules for selecting the
2676 schema name when a :class:`_schema.MetaData`
2677 is also present are the same
2678 as that of :paramref:`_schema.Table.schema`.
2680 :param cache: optional integer value; number of future values in the
2681 sequence which are calculated in advance. Renders the CACHE keyword
2682 understood by Oracle and PostgreSQL.
2684 .. versionadded:: 1.1.12
2686 :param order: optional boolean value; if ``True``, renders the
2687 ORDER keyword, understood by Oracle, indicating the sequence is
2688 definitively ordered. May be necessary to provide deterministic
2689 ordering using Oracle RAC.
2691 .. versionadded:: 1.1.12
2693 :param optional: boolean value, when ``True``, indicates that this
2694 :class:`.Sequence` object only needs to be explicitly generated
2695 on backends that don't provide another way to generate primary
2696 key identifiers. Currently, it essentially means, "don't create
2697 this sequence on the PostgreSQL backend, where the SERIAL keyword
2698 creates a sequence for us automatically".
2699 :param quote: boolean value, when ``True`` or ``False``, explicitly
2700 forces quoting of the :paramref:`_schema.Sequence.name` on or off.
2701 When left at its default of ``None``, normal quoting rules based
2702 on casing and reserved words take place.
2703 :param quote_schema: Set the quoting preferences for the ``schema``
2704 name.
2706 :param metadata: optional :class:`_schema.MetaData` object which this
2707 :class:`.Sequence` will be associated with. A :class:`.Sequence`
2708 that is associated with a :class:`_schema.MetaData`
2709 gains the following
2710 capabilities:
2712 * The :class:`.Sequence` will inherit the
2713 :paramref:`_schema.MetaData.schema`
2714 parameter specified to the target :class:`_schema.MetaData`, which
2715 affects the production of CREATE / DROP DDL, if any.
2717 * The :meth:`.Sequence.create` and :meth:`.Sequence.drop` methods
2718 automatically use the engine bound to the :class:`_schema.MetaData`
2719 object, if any.
2721 * The :meth:`_schema.MetaData.create_all` and
2722 :meth:`_schema.MetaData.drop_all`
2723 methods will emit CREATE / DROP for this :class:`.Sequence`,
2724 even if the :class:`.Sequence` is not associated with any
2725 :class:`_schema.Table` / :class:`_schema.Column`
2726 that's a member of this
2727 :class:`_schema.MetaData`.
2729 The above behaviors can only occur if the :class:`.Sequence` is
2730 explicitly associated with the :class:`_schema.MetaData`
2731 via this parameter.
2733 .. seealso::
2735 :ref:`sequence_metadata` - full discussion of the
2736 :paramref:`.Sequence.metadata` parameter.
2738 :param for_update: Indicates this :class:`.Sequence`, when associated
2739 with a :class:`_schema.Column`,
2740 should be invoked for UPDATE statements
2741 on that column's table, rather than for INSERT statements, when
2742 no value is otherwise present for that column in the statement.
2744 """
2745 DefaultGenerator.__init__(self, for_update=for_update)
2746 IdentityOptions.__init__(
2747 self,
2748 start=start,
2749 increment=increment,
2750 minvalue=minvalue,
2751 maxvalue=maxvalue,
2752 nominvalue=nominvalue,
2753 nomaxvalue=nomaxvalue,
2754 cycle=cycle,
2755 cache=cache,
2756 order=order,
2757 )
2758 self.name = quoted_name(name, quote)
2759 self.optional = optional
2760 if schema is BLANK_SCHEMA:
2761 self.schema = schema = None
2762 elif metadata is not None and schema is None and metadata.schema:
2763 self.schema = schema = metadata.schema
2764 else:
2765 self.schema = quoted_name(schema, quote_schema)
2766 self.metadata = metadata
2767 self._key = _get_table_key(name, schema)
2768 if metadata:
2769 self._set_metadata(metadata)
2771 @util.memoized_property
2772 def is_callable(self):
2773 return False
2775 @util.memoized_property
2776 def is_clause_element(self):
2777 return False
2779 @util.dependencies("sqlalchemy.sql.functions.func")
2780 def next_value(self, func):
2781 """Return a :class:`.next_value` function element
2782 which will render the appropriate increment function
2783 for this :class:`.Sequence` within any SQL expression.
2785 """
2786 return func.next_value(self, bind=self.bind)
2788 def _set_parent(self, column):
2789 super(Sequence, self)._set_parent(column)
2790 column._on_table_attach(self._set_table)
2792 def _set_table(self, column, table):
2793 self._set_metadata(table.metadata)
2795 def _set_metadata(self, metadata):
2796 self.metadata = metadata
2797 self.metadata._sequences[self._key] = self
2799 @property
2800 def bind(self):
2801 if self.metadata:
2802 return self.metadata.bind
2803 else:
2804 return None
2806 def create(self, bind=None, checkfirst=True):
2807 """Creates this sequence in the database."""
2809 if bind is None:
2810 bind = _bind_or_error(self)
2811 bind._run_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
2813 def drop(self, bind=None, checkfirst=True):
2814 """Drops this sequence from the database."""
2816 if bind is None:
2817 bind = _bind_or_error(self)
2818 bind._run_visitor(ddl.SchemaDropper, self, checkfirst=checkfirst)
2820 def _not_a_column_expr(self):
2821 raise exc.InvalidRequestError(
2822 "This %s cannot be used directly "
2823 "as a column expression. Use func.next_value(sequence) "
2824 "to produce a 'next value' function that's usable "
2825 "as a column element." % self.__class__.__name__
2826 )
2829@inspection._self_inspects
2830class FetchedValue(_NotAColumnExpr, SchemaEventTarget):
2831 """A marker for a transparent database-side default.
2833 Use :class:`.FetchedValue` when the database is configured
2834 to provide some automatic default for a column.
2836 E.g.::
2838 Column('foo', Integer, FetchedValue())
2840 Would indicate that some trigger or default generator
2841 will create a new value for the ``foo`` column during an
2842 INSERT.
2844 .. seealso::
2846 :ref:`triggered_columns`
2848 """
2850 is_server_default = True
2851 reflected = False
2852 has_argument = False
2854 def __init__(self, for_update=False):
2855 self.for_update = for_update
2857 def _as_for_update(self, for_update):
2858 if for_update == self.for_update:
2859 return self
2860 else:
2861 return self._clone(for_update)
2863 def _clone(self, for_update):
2864 n = self.__class__.__new__(self.__class__)
2865 n.__dict__.update(self.__dict__)
2866 n.__dict__.pop("column", None)
2867 n.for_update = for_update
2868 return n
2870 def _set_parent(self, column):
2871 self.column = column
2872 if self.for_update:
2873 self.column.server_onupdate = self
2874 else:
2875 self.column.server_default = self
2877 def __repr__(self):
2878 return util.generic_repr(self)
2881class DefaultClause(FetchedValue):
2882 """A DDL-specified DEFAULT column value.
2884 :class:`.DefaultClause` is a :class:`.FetchedValue`
2885 that also generates a "DEFAULT" clause when
2886 "CREATE TABLE" is emitted.
2888 :class:`.DefaultClause` is generated automatically
2889 whenever the ``server_default``, ``server_onupdate`` arguments of
2890 :class:`_schema.Column` are used. A :class:`.DefaultClause`
2891 can be passed positionally as well.
2893 For example, the following::
2895 Column('foo', Integer, server_default="50")
2897 Is equivalent to::
2899 Column('foo', Integer, DefaultClause("50"))
2901 """
2903 has_argument = True
2905 def __init__(self, arg, for_update=False, _reflected=False):
2906 util.assert_arg_type(
2907 arg, (util.string_types[0], ClauseElement, TextClause), "arg"
2908 )
2909 super(DefaultClause, self).__init__(for_update)
2910 self.arg = arg
2911 self.reflected = _reflected
2913 def __repr__(self):
2914 return "DefaultClause(%r, for_update=%r)" % (self.arg, self.for_update)
2917@util.deprecated_cls(
2918 "0.6",
2919 ":class:`.PassiveDefault` is deprecated and will be removed in a "
2920 "future release. Please refer to :class:`.DefaultClause`.",
2921)
2922class PassiveDefault(DefaultClause):
2923 """A DDL-specified DEFAULT column value."""
2925 def __init__(self, *arg, **kw):
2926 DefaultClause.__init__(self, *arg, **kw)
2929class Constraint(DialectKWArgs, SchemaItem):
2930 """A table-level SQL constraint."""
2932 __visit_name__ = "constraint"
2934 def __init__(
2935 self,
2936 name=None,
2937 deferrable=None,
2938 initially=None,
2939 _create_rule=None,
2940 info=None,
2941 _type_bound=False,
2942 **dialect_kw
2943 ):
2944 r"""Create a SQL constraint.
2946 :param name:
2947 Optional, the in-database name of this ``Constraint``.
2949 :param deferrable:
2950 Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
2951 issuing DDL for this constraint.
2953 :param initially:
2954 Optional string. If set, emit INITIALLY <value> when issuing DDL
2955 for this constraint.
2957 :param info: Optional data dictionary which will be populated into the
2958 :attr:`.SchemaItem.info` attribute of this object.
2960 .. versionadded:: 1.0.0
2962 :param _create_rule:
2963 a callable which is passed the DDLCompiler object during
2964 compilation. Returns True or False to signal inline generation of
2965 this Constraint.
2967 The AddConstraint and DropConstraint DDL constructs provide
2968 DDLElement's more comprehensive "conditional DDL" approach that is
2969 passed a database connection when DDL is being issued. _create_rule
2970 is instead called during any CREATE TABLE compilation, where there
2971 may not be any transaction/connection in progress. However, it
2972 allows conditional compilation of the constraint even for backends
2973 which do not support addition of constraints through ALTER TABLE,
2974 which currently includes SQLite.
2976 _create_rule is used by some types to create constraints.
2977 Currently, its call signature is subject to change at any time.
2979 :param \**dialect_kw: Additional keyword arguments are dialect
2980 specific, and passed in the form ``<dialectname>_<argname>``. See
2981 the documentation regarding an individual dialect at
2982 :ref:`dialect_toplevel` for detail on documented arguments.
2984 """
2986 self.name = name
2987 self.deferrable = deferrable
2988 self.initially = initially
2989 if info:
2990 self.info = info
2991 self._create_rule = _create_rule
2992 self._type_bound = _type_bound
2993 util.set_creation_order(self)
2994 self._validate_dialect_kwargs(dialect_kw)
2996 @property
2997 def table(self):
2998 try:
2999 if isinstance(self.parent, Table):
3000 return self.parent
3001 except AttributeError:
3002 pass
3003 raise exc.InvalidRequestError(
3004 "This constraint is not bound to a table. Did you "
3005 "mean to call table.append_constraint(constraint) ?"
3006 )
3008 def _set_parent(self, parent):
3009 self.parent = parent
3010 parent.constraints.add(self)
3012 def copy(self, **kw):
3013 raise NotImplementedError()
3016def _to_schema_column(element):
3017 if hasattr(element, "__clause_element__"):
3018 element = element.__clause_element__()
3019 if not isinstance(element, Column):
3020 raise exc.ArgumentError("schema.Column object expected")
3021 return element
3024def _to_schema_column_or_string(element):
3025 if element is None:
3026 return element
3027 elif hasattr(element, "__clause_element__"):
3028 element = element.__clause_element__()
3029 if not isinstance(element, util.string_types + (ColumnElement,)):
3030 msg = "Element %r is not a string name or column element"
3031 raise exc.ArgumentError(msg % element)
3032 return element
3035class ColumnCollectionMixin(object):
3037 columns = None
3038 """A :class:`_expression.ColumnCollection` of :class:`_schema.Column`
3039 objects.
3041 This collection represents the columns which are referred to by
3042 this object.
3044 """
3046 _allow_multiple_tables = False
3048 def __init__(self, *columns, **kw):
3049 _autoattach = kw.pop("_autoattach", True)
3050 self._column_flag = kw.pop("_column_flag", False)
3051 self.columns = ColumnCollection()
3052 self._pending_colargs = [
3053 _to_schema_column_or_string(c) for c in columns
3054 ]
3055 if _autoattach and self._pending_colargs:
3056 self._check_attach()
3058 @classmethod
3059 def _extract_col_expression_collection(cls, expressions):
3060 for expr in expressions:
3061 strname = None
3062 column = None
3063 if hasattr(expr, "__clause_element__"):
3064 expr = expr.__clause_element__()
3066 if not isinstance(expr, (ColumnElement, TextClause)):
3067 # this assumes a string
3068 strname = expr
3069 else:
3070 cols = []
3071 visitors.traverse(expr, {}, {"column": cols.append})
3072 if cols:
3073 column = cols[0]
3074 add_element = column if column is not None else strname
3075 yield expr, column, strname, add_element
3077 def _check_attach(self, evt=False):
3078 col_objs = [c for c in self._pending_colargs if isinstance(c, Column)]
3080 cols_w_table = [c for c in col_objs if isinstance(c.table, Table)]
3082 cols_wo_table = set(col_objs).difference(cols_w_table)
3083 if cols_wo_table:
3084 # feature #3341 - place event listeners for Column objects
3085 # such that when all those cols are attached, we autoattach.
3086 assert not evt, "Should not reach here on event call"
3088 # issue #3411 - don't do the per-column auto-attach if some of the
3089 # columns are specified as strings.
3090 has_string_cols = set(
3091 c for c in self._pending_colargs if c is not None
3092 ).difference(col_objs)
3093 if not has_string_cols:
3095 def _col_attached(column, table):
3096 # this isinstance() corresponds with the
3097 # isinstance() above; only want to count Table-bound
3098 # columns
3099 if isinstance(table, Table):
3100 cols_wo_table.discard(column)
3101 if not cols_wo_table:
3102 self._check_attach(evt=True)
3104 self._cols_wo_table = cols_wo_table
3105 for col in cols_wo_table:
3106 col._on_table_attach(_col_attached)
3107 return
3109 columns = cols_w_table
3111 tables = {c.table for c in columns}
3112 if len(tables) == 1:
3113 self._set_parent_with_dispatch(tables.pop())
3114 elif len(tables) > 1 and not self._allow_multiple_tables:
3115 table = columns[0].table
3116 others = [c for c in columns[1:] if c.table is not table]
3117 if others:
3118 raise exc.ArgumentError(
3119 "Column(s) %s are not part of table '%s'."
3120 % (
3121 ", ".join("'%s'" % c for c in others),
3122 table.description,
3123 )
3124 )
3126 def _col_expressions(self, table):
3127 return [
3128 table.c[col] if isinstance(col, util.string_types) else col
3129 for col in self._pending_colargs
3130 ]
3132 def _set_parent(self, table):
3133 for col in self._col_expressions(table):
3134 if col is not None:
3135 self.columns.add(col)
3138class ColumnCollectionConstraint(ColumnCollectionMixin, Constraint):
3139 """A constraint that proxies a ColumnCollection."""
3141 def __init__(self, *columns, **kw):
3142 r"""
3143 :param \*columns:
3144 A sequence of column names or Column objects.
3146 :param name:
3147 Optional, the in-database name of this constraint.
3149 :param deferrable:
3150 Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
3151 issuing DDL for this constraint.
3153 :param initially:
3154 Optional string. If set, emit INITIALLY <value> when issuing DDL
3155 for this constraint.
3157 :param \**kw: other keyword arguments including dialect-specific
3158 arguments are propagated to the :class:`.Constraint` superclass.
3160 """
3161 _autoattach = kw.pop("_autoattach", True)
3162 _column_flag = kw.pop("_column_flag", False)
3163 Constraint.__init__(self, **kw)
3164 ColumnCollectionMixin.__init__(
3165 self, *columns, _autoattach=_autoattach, _column_flag=_column_flag
3166 )
3168 columns = None
3169 """A :class:`_expression.ColumnCollection` representing the set of columns
3170 for this constraint.
3172 """
3174 def _set_parent(self, table):
3175 Constraint._set_parent(self, table)
3176 ColumnCollectionMixin._set_parent(self, table)
3178 def __contains__(self, x):
3179 return x in self.columns
3181 def copy(self, target_table=None, **kw):
3182 # ticket #5276
3183 constraint_kwargs = {}
3184 for dialect_name in self.dialect_options:
3185 dialect_options = self.dialect_options[dialect_name]._non_defaults
3186 for (
3187 dialect_option_key,
3188 dialect_option_value,
3189 ) in dialect_options.items():
3190 constraint_kwargs[
3191 dialect_name + "_" + dialect_option_key
3192 ] = dialect_option_value
3194 c = self.__class__(
3195 name=self.name,
3196 deferrable=self.deferrable,
3197 initially=self.initially,
3198 *[
3199 _copy_expression(expr, self.parent, target_table)
3200 for expr in self.columns
3201 ],
3202 **constraint_kwargs
3203 )
3204 return self._schema_item_copy(c)
3206 def contains_column(self, col):
3207 """Return True if this constraint contains the given column.
3209 Note that this object also contains an attribute ``.columns``
3210 which is a :class:`_expression.ColumnCollection` of
3211 :class:`_schema.Column` objects.
3213 """
3215 return self.columns.contains_column(col)
3217 def __iter__(self):
3218 # inlining of
3219 # return iter(self.columns)
3220 # ColumnCollection->OrderedProperties->OrderedDict
3221 ordered_dict = self.columns._data
3222 return (ordered_dict[key] for key in ordered_dict._list)
3224 def __len__(self):
3225 return len(self.columns._data)
3228class CheckConstraint(ColumnCollectionConstraint):
3229 """A table- or column-level CHECK constraint.
3231 Can be included in the definition of a Table or Column.
3232 """
3234 _allow_multiple_tables = True
3236 @_document_text_coercion(
3237 "sqltext",
3238 ":class:`.CheckConstraint`",
3239 ":paramref:`.CheckConstraint.sqltext`",
3240 )
3241 def __init__(
3242 self,
3243 sqltext,
3244 name=None,
3245 deferrable=None,
3246 initially=None,
3247 table=None,
3248 info=None,
3249 _create_rule=None,
3250 _autoattach=True,
3251 _type_bound=False,
3252 **kw
3253 ):
3254 r"""Construct a CHECK constraint.
3256 :param sqltext:
3257 A string containing the constraint definition, which will be used
3258 verbatim, or a SQL expression construct. If given as a string,
3259 the object is converted to a :func:`_expression.text` object.
3260 If the textual
3261 string includes a colon character, escape this using a backslash::
3263 CheckConstraint(r"foo ~ E'a(?\:b|c)d")
3265 :param name:
3266 Optional, the in-database name of the constraint.
3268 :param deferrable:
3269 Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
3270 issuing DDL for this constraint.
3272 :param initially:
3273 Optional string. If set, emit INITIALLY <value> when issuing DDL
3274 for this constraint.
3276 :param info: Optional data dictionary which will be populated into the
3277 :attr:`.SchemaItem.info` attribute of this object.
3279 .. versionadded:: 1.0.0
3281 """
3283 self.sqltext = _literal_as_text(sqltext, allow_coercion_to_text=True)
3285 columns = []
3286 visitors.traverse(self.sqltext, {}, {"column": columns.append})
3288 super(CheckConstraint, self).__init__(
3289 name=name,
3290 deferrable=deferrable,
3291 initially=initially,
3292 _create_rule=_create_rule,
3293 info=info,
3294 _type_bound=_type_bound,
3295 _autoattach=_autoattach,
3296 *columns,
3297 **kw
3298 )
3299 if table is not None:
3300 self._set_parent_with_dispatch(table)
3302 def __visit_name__(self):
3303 if isinstance(self.parent, Table):
3304 return "check_constraint"
3305 else:
3306 return "column_check_constraint"
3308 __visit_name__ = property(__visit_name__)
3310 def copy(self, target_table=None, **kw):
3311 if target_table is not None:
3312 # note that target_table is None for the copy process of
3313 # a column-bound CheckConstraint, so this path is not reached
3314 # in that case.
3315 sqltext = _copy_expression(self.sqltext, self.table, target_table)
3316 else:
3317 sqltext = self.sqltext
3318 c = CheckConstraint(
3319 sqltext,
3320 name=self.name,
3321 initially=self.initially,
3322 deferrable=self.deferrable,
3323 _create_rule=self._create_rule,
3324 table=target_table,
3325 _autoattach=False,
3326 _type_bound=self._type_bound,
3327 )
3328 return self._schema_item_copy(c)
3331class ForeignKeyConstraint(ColumnCollectionConstraint):
3332 """A table-level FOREIGN KEY constraint.
3334 Defines a single column or composite FOREIGN KEY ... REFERENCES
3335 constraint. For a no-frills, single column foreign key, adding a
3336 :class:`_schema.ForeignKey` to the definition of a :class:`_schema.Column`
3337 is a
3338 shorthand equivalent for an unnamed, single column
3339 :class:`_schema.ForeignKeyConstraint`.
3341 Examples of foreign key configuration are in :ref:`metadata_foreignkeys`.
3343 """
3345 __visit_name__ = "foreign_key_constraint"
3347 def __init__(
3348 self,
3349 columns,
3350 refcolumns,
3351 name=None,
3352 onupdate=None,
3353 ondelete=None,
3354 deferrable=None,
3355 initially=None,
3356 use_alter=False,
3357 link_to_name=False,
3358 match=None,
3359 table=None,
3360 info=None,
3361 **dialect_kw
3362 ):
3363 r"""Construct a composite-capable FOREIGN KEY.
3365 :param columns: A sequence of local column names. The named columns
3366 must be defined and present in the parent Table. The names should
3367 match the ``key`` given to each column (defaults to the name) unless
3368 ``link_to_name`` is True.
3370 :param refcolumns: A sequence of foreign column names or Column
3371 objects. The columns must all be located within the same Table.
3373 :param name: Optional, the in-database name of the key.
3375 :param onupdate: Optional string. If set, emit ON UPDATE <value> when
3376 issuing DDL for this constraint. Typical values include CASCADE,
3377 DELETE and RESTRICT.
3379 :param ondelete: Optional string. If set, emit ON DELETE <value> when
3380 issuing DDL for this constraint. Typical values include CASCADE,
3381 DELETE and RESTRICT.
3383 :param deferrable: Optional bool. If set, emit DEFERRABLE or NOT
3384 DEFERRABLE when issuing DDL for this constraint.
3386 :param initially: Optional string. If set, emit INITIALLY <value> when
3387 issuing DDL for this constraint.
3389 :param link_to_name: if True, the string name given in ``column`` is
3390 the rendered name of the referenced column, not its locally assigned
3391 ``key``.
3393 :param use_alter: If True, do not emit the DDL for this constraint as
3394 part of the CREATE TABLE definition. Instead, generate it via an
3395 ALTER TABLE statement issued after the full collection of tables
3396 have been created, and drop it via an ALTER TABLE statement before
3397 the full collection of tables are dropped.
3399 The use of :paramref:`_schema.ForeignKeyConstraint.use_alter` is
3400 particularly geared towards the case where two or more tables
3401 are established within a mutually-dependent foreign key constraint
3402 relationship; however, the :meth:`_schema.MetaData.create_all` and
3403 :meth:`_schema.MetaData.drop_all`
3404 methods will perform this resolution
3405 automatically, so the flag is normally not needed.
3407 .. versionchanged:: 1.0.0 Automatic resolution of foreign key
3408 cycles has been added, removing the need to use the
3409 :paramref:`_schema.ForeignKeyConstraint.use_alter` in typical use
3410 cases.
3412 .. seealso::
3414 :ref:`use_alter`
3416 :param match: Optional string. If set, emit MATCH <value> when issuing
3417 DDL for this constraint. Typical values include SIMPLE, PARTIAL
3418 and FULL.
3420 :param info: Optional data dictionary which will be populated into the
3421 :attr:`.SchemaItem.info` attribute of this object.
3423 .. versionadded:: 1.0.0
3425 :param \**dialect_kw: Additional keyword arguments are dialect
3426 specific, and passed in the form ``<dialectname>_<argname>``. See
3427 the documentation regarding an individual dialect at
3428 :ref:`dialect_toplevel` for detail on documented arguments.
3430 .. versionadded:: 0.9.2
3432 """
3434 Constraint.__init__(
3435 self,
3436 name=name,
3437 deferrable=deferrable,
3438 initially=initially,
3439 info=info,
3440 **dialect_kw
3441 )
3442 self.onupdate = onupdate
3443 self.ondelete = ondelete
3444 self.link_to_name = link_to_name
3445 self.use_alter = use_alter
3446 self.match = match
3448 if len(set(columns)) != len(refcolumns):
3449 if len(set(columns)) != len(columns):
3450 # e.g. FOREIGN KEY (a, a) REFERENCES r (b, c)
3451 raise exc.ArgumentError(
3452 "ForeignKeyConstraint with duplicate source column "
3453 "references are not supported."
3454 )
3455 else:
3456 # e.g. FOREIGN KEY (a) REFERENCES r (b, c)
3457 # paraphrasing https://www.postgresql.org/docs/9.2/static/\
3458 # ddl-constraints.html
3459 raise exc.ArgumentError(
3460 "ForeignKeyConstraint number "
3461 "of constrained columns must match the number of "
3462 "referenced columns."
3463 )
3465 # standalone ForeignKeyConstraint - create
3466 # associated ForeignKey objects which will be applied to hosted
3467 # Column objects (in col.foreign_keys), either now or when attached
3468 # to the Table for string-specified names
3469 self.elements = [
3470 ForeignKey(
3471 refcol,
3472 _constraint=self,
3473 name=self.name,
3474 onupdate=self.onupdate,
3475 ondelete=self.ondelete,
3476 use_alter=self.use_alter,
3477 link_to_name=self.link_to_name,
3478 match=self.match,
3479 deferrable=self.deferrable,
3480 initially=self.initially,
3481 **self.dialect_kwargs
3482 )
3483 for refcol in refcolumns
3484 ]
3486 ColumnCollectionMixin.__init__(self, *columns)
3487 if table is not None:
3488 if hasattr(self, "parent"):
3489 assert table is self.parent
3490 self._set_parent_with_dispatch(table)
3492 def _append_element(self, column, fk):
3493 self.columns.add(column)
3494 self.elements.append(fk)
3496 columns = None
3497 """A :class:`_expression.ColumnCollection` representing the set of columns
3498 for this constraint.
3500 """
3502 elements = None
3503 """A sequence of :class:`_schema.ForeignKey` objects.
3505 Each :class:`_schema.ForeignKey`
3506 represents a single referring column/referred
3507 column pair.
3509 This collection is intended to be read-only.
3511 """
3513 @property
3514 def _elements(self):
3515 # legacy - provide a dictionary view of (column_key, fk)
3516 return util.OrderedDict(zip(self.column_keys, self.elements))
3518 @property
3519 def _referred_schema(self):
3520 for elem in self.elements:
3521 return elem._referred_schema
3522 else:
3523 return None
3525 @property
3526 def referred_table(self):
3527 """The :class:`_schema.Table` object to which this
3528 :class:`_schema.ForeignKeyConstraint` references.
3530 This is a dynamically calculated attribute which may not be available
3531 if the constraint and/or parent table is not yet associated with
3532 a metadata collection that contains the referred table.
3534 .. versionadded:: 1.0.0
3536 """
3537 return self.elements[0].column.table
3539 def _validate_dest_table(self, table):
3540 table_keys = set([elem._table_key() for elem in self.elements])
3541 if None not in table_keys and len(table_keys) > 1:
3542 elem0, elem1 = sorted(table_keys)[0:2]
3543 raise exc.ArgumentError(
3544 "ForeignKeyConstraint on %s(%s) refers to "
3545 "multiple remote tables: %s and %s"
3546 % (table.fullname, self._col_description, elem0, elem1)
3547 )
3549 @property
3550 def column_keys(self):
3551 """Return a list of string keys representing the local
3552 columns in this :class:`_schema.ForeignKeyConstraint`.
3554 This list is either the original string arguments sent
3555 to the constructor of the :class:`_schema.ForeignKeyConstraint`,
3556 or if the constraint has been initialized with :class:`_schema.Column`
3557 objects, is the string ``.key`` of each element.
3559 .. versionadded:: 1.0.0
3561 """
3562 if hasattr(self, "parent"):
3563 return self.columns.keys()
3564 else:
3565 return [
3566 col.key if isinstance(col, ColumnElement) else str(col)
3567 for col in self._pending_colargs
3568 ]
3570 @property
3571 def _col_description(self):
3572 return ", ".join(self.column_keys)
3574 def _set_parent(self, table):
3575 Constraint._set_parent(self, table)
3577 try:
3578 ColumnCollectionConstraint._set_parent(self, table)
3579 except KeyError as ke:
3580 util.raise_(
3581 exc.ArgumentError(
3582 "Can't create ForeignKeyConstraint "
3583 "on table '%s': no column "
3584 "named '%s' is present." % (table.description, ke.args[0])
3585 ),
3586 from_=ke,
3587 )
3589 for col, fk in zip(self.columns, self.elements):
3590 if not hasattr(fk, "parent") or fk.parent is not col:
3591 fk._set_parent_with_dispatch(col)
3593 self._validate_dest_table(table)
3595 def copy(self, schema=None, target_table=None, **kw):
3596 fkc = ForeignKeyConstraint(
3597 [x.parent.key for x in self.elements],
3598 [
3599 x._get_colspec(
3600 schema=schema,
3601 table_name=target_table.name
3602 if target_table is not None
3603 and x._table_key() == x.parent.table.key
3604 else None,
3605 )
3606 for x in self.elements
3607 ],
3608 name=self.name,
3609 onupdate=self.onupdate,
3610 ondelete=self.ondelete,
3611 use_alter=self.use_alter,
3612 deferrable=self.deferrable,
3613 initially=self.initially,
3614 link_to_name=self.link_to_name,
3615 match=self.match,
3616 )
3617 for self_fk, other_fk in zip(self.elements, fkc.elements):
3618 self_fk._schema_item_copy(other_fk)
3619 return self._schema_item_copy(fkc)
3622class PrimaryKeyConstraint(ColumnCollectionConstraint):
3623 """A table-level PRIMARY KEY constraint.
3625 The :class:`.PrimaryKeyConstraint` object is present automatically
3626 on any :class:`_schema.Table` object; it is assigned a set of
3627 :class:`_schema.Column` objects corresponding to those marked with
3628 the :paramref:`_schema.Column.primary_key` flag::
3630 >>> my_table = Table('mytable', metadata,
3631 ... Column('id', Integer, primary_key=True),
3632 ... Column('version_id', Integer, primary_key=True),
3633 ... Column('data', String(50))
3634 ... )
3635 >>> my_table.primary_key
3636 PrimaryKeyConstraint(
3637 Column('id', Integer(), table=<mytable>,
3638 primary_key=True, nullable=False),
3639 Column('version_id', Integer(), table=<mytable>,
3640 primary_key=True, nullable=False)
3641 )
3643 The primary key of a :class:`_schema.Table` can also be specified by using
3644 a :class:`.PrimaryKeyConstraint` object explicitly; in this mode of usage,
3645 the "name" of the constraint can also be specified, as well as other
3646 options which may be recognized by dialects::
3648 my_table = Table('mytable', metadata,
3649 Column('id', Integer),
3650 Column('version_id', Integer),
3651 Column('data', String(50)),
3652 PrimaryKeyConstraint('id', 'version_id',
3653 name='mytable_pk')
3654 )
3656 The two styles of column-specification should generally not be mixed.
3657 An warning is emitted if the columns present in the
3658 :class:`.PrimaryKeyConstraint`
3659 don't match the columns that were marked as ``primary_key=True``, if both
3660 are present; in this case, the columns are taken strictly from the
3661 :class:`.PrimaryKeyConstraint` declaration, and those columns otherwise
3662 marked as ``primary_key=True`` are ignored. This behavior is intended to
3663 be backwards compatible with previous behavior.
3665 .. versionchanged:: 0.9.2 Using a mixture of columns within a
3666 :class:`.PrimaryKeyConstraint` in addition to columns marked as
3667 ``primary_key=True`` now emits a warning if the lists don't match.
3668 The ultimate behavior of ignoring those columns marked with the flag
3669 only is currently maintained for backwards compatibility; this warning
3670 may raise an exception in a future release.
3672 For the use case where specific options are to be specified on the
3673 :class:`.PrimaryKeyConstraint`, but the usual style of using
3674 ``primary_key=True`` flags is still desirable, an empty
3675 :class:`.PrimaryKeyConstraint` may be specified, which will take on the
3676 primary key column collection from the :class:`_schema.Table` based on the
3677 flags::
3679 my_table = Table('mytable', metadata,
3680 Column('id', Integer, primary_key=True),
3681 Column('version_id', Integer, primary_key=True),
3682 Column('data', String(50)),
3683 PrimaryKeyConstraint(name='mytable_pk',
3684 mssql_clustered=True)
3685 )
3687 .. versionadded:: 0.9.2 an empty :class:`.PrimaryKeyConstraint` may now
3688 be specified for the purposes of establishing keyword arguments with
3689 the constraint, independently of the specification of "primary key"
3690 columns within the :class:`_schema.Table` itself; columns marked as
3691 ``primary_key=True`` will be gathered into the empty constraint's
3692 column collection.
3694 """
3696 __visit_name__ = "primary_key_constraint"
3698 def __init__(self, *columns, **kw):
3699 self._implicit_generated = kw.pop("_implicit_generated", False)
3700 super(PrimaryKeyConstraint, self).__init__(*columns, **kw)
3702 def _set_parent(self, table):
3703 super(PrimaryKeyConstraint, self)._set_parent(table)
3705 if table.primary_key is not self:
3706 table.constraints.discard(table.primary_key)
3707 table.primary_key = self
3708 table.constraints.add(self)
3710 table_pks = [c for c in table.c if c.primary_key]
3711 if (
3712 self.columns
3713 and table_pks
3714 and set(table_pks) != set(self.columns.values())
3715 ):
3716 util.warn(
3717 "Table '%s' specifies columns %s as primary_key=True, "
3718 "not matching locally specified columns %s; setting the "
3719 "current primary key columns to %s. This warning "
3720 "may become an exception in a future release"
3721 % (
3722 table.name,
3723 ", ".join("'%s'" % c.name for c in table_pks),
3724 ", ".join("'%s'" % c.name for c in self.columns),
3725 ", ".join("'%s'" % c.name for c in self.columns),
3726 )
3727 )
3728 table_pks[:] = []
3730 for c in self.columns:
3731 c.primary_key = True
3732 c.nullable = False
3733 self.columns.extend(table_pks)
3735 def _reload(self, columns):
3736 """repopulate this :class:`.PrimaryKeyConstraint` given
3737 a set of columns.
3739 Existing columns in the table that are marked as primary_key=True
3740 are maintained.
3742 Also fires a new event.
3744 This is basically like putting a whole new
3745 :class:`.PrimaryKeyConstraint` object on the parent
3746 :class:`_schema.Table` object without actually replacing the object.
3748 The ordering of the given list of columns is also maintained; these
3749 columns will be appended to the list of columns after any which
3750 are already present.
3752 """
3753 # set the primary key flag on new columns.
3754 # note any existing PK cols on the table also have their
3755 # flag still set.
3756 for col in columns:
3757 col.primary_key = True
3759 self.columns.extend(columns)
3761 PrimaryKeyConstraint._autoincrement_column._reset(self)
3762 self._set_parent_with_dispatch(self.table)
3764 def _replace(self, col):
3765 PrimaryKeyConstraint._autoincrement_column._reset(self)
3766 self.columns.replace(col)
3768 self.dispatch._sa_event_column_added_to_pk_constraint(self, col)
3770 @property
3771 def columns_autoinc_first(self):
3772 autoinc = self._autoincrement_column
3774 if autoinc is not None:
3775 return [autoinc] + [c for c in self.columns if c is not autoinc]
3776 else:
3777 return list(self.columns)
3779 @util.memoized_property
3780 def _autoincrement_column(self):
3781 def _validate_autoinc(col, autoinc_true):
3782 if col.type._type_affinity is None or not issubclass(
3783 col.type._type_affinity, type_api.INTEGERTYPE._type_affinity
3784 ):
3785 if autoinc_true:
3786 raise exc.ArgumentError(
3787 "Column type %s on column '%s' is not "
3788 "compatible with autoincrement=True" % (col.type, col)
3789 )
3790 else:
3791 return False
3792 elif (
3793 not isinstance(col.default, (type(None), Sequence))
3794 and not autoinc_true
3795 ):
3796 return False
3797 elif col.server_default is not None and not autoinc_true:
3798 return False
3799 elif col.foreign_keys and col.autoincrement not in (
3800 True,
3801 "ignore_fk",
3802 ):
3803 return False
3804 return True
3806 if len(self.columns) == 1:
3807 col = list(self.columns)[0]
3809 if col.autoincrement is True:
3810 _validate_autoinc(col, True)
3811 return col
3812 elif (
3813 col.autoincrement
3814 in (
3815 "auto",
3816 "ignore_fk",
3817 )
3818 and _validate_autoinc(col, False)
3819 ):
3820 return col
3822 else:
3823 autoinc = None
3824 for col in self.columns:
3825 if col.autoincrement is True:
3826 _validate_autoinc(col, True)
3827 if autoinc is not None:
3828 raise exc.ArgumentError(
3829 "Only one Column may be marked "
3830 "autoincrement=True, found both %s and %s."
3831 % (col.name, autoinc.name)
3832 )
3833 else:
3834 autoinc = col
3836 return autoinc
3839class UniqueConstraint(ColumnCollectionConstraint):
3840 """A table-level UNIQUE constraint.
3842 Defines a single column or composite UNIQUE constraint. For a no-frills,
3843 single column constraint, adding ``unique=True`` to the ``Column``
3844 definition is a shorthand equivalent for an unnamed, single column
3845 UniqueConstraint.
3846 """
3848 __visit_name__ = "unique_constraint"
3851class Index(DialectKWArgs, ColumnCollectionMixin, SchemaItem):
3852 """A table-level INDEX.
3854 Defines a composite (one or more column) INDEX.
3856 E.g.::
3858 sometable = Table("sometable", metadata,
3859 Column("name", String(50)),
3860 Column("address", String(100))
3861 )
3863 Index("some_index", sometable.c.name)
3865 For a no-frills, single column index, adding
3866 :class:`_schema.Column` also supports ``index=True``::
3868 sometable = Table("sometable", metadata,
3869 Column("name", String(50), index=True)
3870 )
3872 For a composite index, multiple columns can be specified::
3874 Index("some_index", sometable.c.name, sometable.c.address)
3876 Functional indexes are supported as well, typically by using the
3877 :data:`.func` construct in conjunction with table-bound
3878 :class:`_schema.Column` objects::
3880 Index("some_index", func.lower(sometable.c.name))
3882 An :class:`.Index` can also be manually associated with a
3883 :class:`_schema.Table`,
3884 either through inline declaration or using
3885 :meth:`_schema.Table.append_constraint`. When this approach is used,
3886 the names
3887 of the indexed columns can be specified as strings::
3889 Table("sometable", metadata,
3890 Column("name", String(50)),
3891 Column("address", String(100)),
3892 Index("some_index", "name", "address")
3893 )
3895 To support functional or expression-based indexes in this form, the
3896 :func:`_expression.text` construct may be used::
3898 from sqlalchemy import text
3900 Table("sometable", metadata,
3901 Column("name", String(50)),
3902 Column("address", String(100)),
3903 Index("some_index", text("lower(name)"))
3904 )
3906 .. versionadded:: 0.9.5 the :func:`_expression.text`
3907 construct may be used to
3908 specify :class:`.Index` expressions, provided the :class:`.Index`
3909 is explicitly associated with the :class:`_schema.Table`.
3912 .. seealso::
3914 :ref:`schema_indexes` - General information on :class:`.Index`.
3916 :ref:`postgresql_indexes` - PostgreSQL-specific options available for
3917 the :class:`.Index` construct.
3919 :ref:`mysql_indexes` - MySQL-specific options available for the
3920 :class:`.Index` construct.
3922 :ref:`mssql_indexes` - MSSQL-specific options available for the
3923 :class:`.Index` construct.
3925 """
3927 __visit_name__ = "index"
3929 def __init__(self, name, *expressions, **kw):
3930 r"""Construct an index object.
3932 :param name:
3933 The name of the index
3935 :param \*expressions:
3936 Column expressions to include in the index. The expressions
3937 are normally instances of :class:`_schema.Column`, but may also
3938 be arbitrary SQL expressions which ultimately refer to a
3939 :class:`_schema.Column`.
3941 :param unique=False:
3942 Keyword only argument; if True, create a unique index.
3944 :param quote=None:
3945 Keyword only argument; whether to apply quoting to the name of
3946 the index. Works in the same manner as that of
3947 :paramref:`_schema.Column.quote`.
3949 :param info=None: Optional data dictionary which will be populated
3950 into the :attr:`.SchemaItem.info` attribute of this object.
3952 .. versionadded:: 1.0.0
3954 :param \**kw: Additional keyword arguments not mentioned above are
3955 dialect specific, and passed in the form
3956 ``<dialectname>_<argname>``. See the documentation regarding an
3957 individual dialect at :ref:`dialect_toplevel` for detail on
3958 documented arguments.
3960 """
3961 self.table = table = None
3963 columns = []
3964 processed_expressions = []
3965 for (
3966 expr,
3967 column,
3968 strname,
3969 add_element,
3970 ) in self._extract_col_expression_collection(expressions):
3971 columns.append(add_element)
3972 processed_expressions.append(expr)
3974 self.expressions = processed_expressions
3975 self.name = quoted_name(name, kw.pop("quote", None))
3976 self.unique = kw.pop("unique", False)
3977 _column_flag = kw.pop("_column_flag", False)
3978 if "info" in kw:
3979 self.info = kw.pop("info")
3981 # TODO: consider "table" argument being public, but for
3982 # the purpose of the fix here, it starts as private.
3983 if "_table" in kw:
3984 table = kw.pop("_table")
3986 self._validate_dialect_kwargs(kw)
3988 # will call _set_parent() if table-bound column
3989 # objects are present
3990 ColumnCollectionMixin.__init__(
3991 self, *columns, _column_flag=_column_flag
3992 )
3994 if table is not None:
3995 self._set_parent(table)
3997 def _set_parent(self, table):
3998 ColumnCollectionMixin._set_parent(self, table)
4000 if self.table is not None and table is not self.table:
4001 raise exc.ArgumentError(
4002 "Index '%s' is against table '%s', and "
4003 "cannot be associated with table '%s'."
4004 % (self.name, self.table.description, table.description)
4005 )
4006 self.table = table
4007 table.indexes.add(self)
4009 expressions = self.expressions
4010 col_expressions = self._col_expressions(table)
4011 assert len(expressions) == len(col_expressions)
4012 self.expressions = [
4013 expr if isinstance(expr, ClauseElement) else colexpr
4014 for expr, colexpr in zip(expressions, col_expressions)
4015 ]
4017 @property
4018 def bind(self):
4019 """Return the connectable associated with this Index."""
4021 return self.table.bind
4023 def create(self, bind=None):
4024 """Issue a ``CREATE`` statement for this
4025 :class:`.Index`, using the given :class:`.Connectable`
4026 for connectivity.
4028 .. seealso::
4030 :meth:`_schema.MetaData.create_all`.
4032 """
4033 if bind is None:
4034 bind = _bind_or_error(self)
4035 bind._run_visitor(ddl.SchemaGenerator, self)
4036 return self
4038 def drop(self, bind=None):
4039 """Issue a ``DROP`` statement for this
4040 :class:`.Index`, using the given :class:`.Connectable`
4041 for connectivity.
4043 .. seealso::
4045 :meth:`_schema.MetaData.drop_all`.
4047 """
4048 if bind is None:
4049 bind = _bind_or_error(self)
4050 bind._run_visitor(ddl.SchemaDropper, self)
4052 def __repr__(self):
4053 return "Index(%s)" % (
4054 ", ".join(
4055 [repr(self.name)]
4056 + [repr(e) for e in self.expressions]
4057 + (self.unique and ["unique=True"] or [])
4058 )
4059 )
4062DEFAULT_NAMING_CONVENTION = util.immutabledict({"ix": "ix_%(column_0_label)s"})
4065class MetaData(SchemaItem):
4066 """A collection of :class:`_schema.Table`
4067 objects and their associated schema
4068 constructs.
4070 Holds a collection of :class:`_schema.Table` objects as well as
4071 an optional binding to an :class:`_engine.Engine` or
4072 :class:`_engine.Connection`. If bound, the :class:`_schema.Table` objects
4073 in the collection and their columns may participate in implicit SQL
4074 execution.
4076 The :class:`_schema.Table` objects themselves are stored in the
4077 :attr:`_schema.MetaData.tables` dictionary.
4079 :class:`_schema.MetaData` is a thread-safe object for read operations.
4080 Construction of new tables within a single :class:`_schema.MetaData`
4081 object,
4082 either explicitly or via reflection, may not be completely thread-safe.
4084 .. seealso::
4086 :ref:`metadata_describing` - Introduction to database metadata
4088 """
4090 __visit_name__ = "metadata"
4092 @util.deprecated_params(
4093 reflect=(
4094 "0.8",
4095 "The :paramref:`_schema.MetaData.reflect` "
4096 "flag is deprecated and will "
4097 "be removed in a future release. Please use the "
4098 ":meth:`_schema.MetaData.reflect` method.",
4099 )
4100 )
4101 def __init__(
4102 self,
4103 bind=None,
4104 reflect=False,
4105 schema=None,
4106 quote_schema=None,
4107 naming_convention=None,
4108 info=None,
4109 ):
4110 """Create a new MetaData object.
4112 :param bind:
4113 An Engine or Connection to bind to. May also be a string or URL
4114 instance, these are passed to :func:`_sa.create_engine` and
4115 this :class:`_schema.MetaData` will
4116 be bound to the resulting engine.
4118 :param reflect:
4119 Optional, automatically load all tables from the bound database.
4120 Defaults to False. :paramref:`_schema.MetaData.bind` is required
4121 when this option is set.
4123 :param schema:
4124 The default schema to use for the :class:`_schema.Table`,
4125 :class:`.Sequence`, and potentially other objects associated with
4126 this :class:`_schema.MetaData`. Defaults to ``None``.
4128 When this value is set, any :class:`_schema.Table` or
4129 :class:`.Sequence`
4130 which specifies ``None`` for the schema parameter will instead
4131 have this schema name defined. To build a :class:`_schema.Table`
4132 or :class:`.Sequence` that still has ``None`` for the schema
4133 even when this parameter is present, use the :attr:`.BLANK_SCHEMA`
4134 symbol.
4136 .. note::
4138 As referred above, the :paramref:`_schema.MetaData.schema`
4139 parameter
4140 only refers to the **default value** that will be applied to
4141 the :paramref:`_schema.Table.schema` parameter of an incoming
4142 :class:`_schema.Table` object. It does not refer to how the
4143 :class:`_schema.Table` is catalogued within the
4144 :class:`_schema.MetaData`,
4145 which remains consistent vs. a :class:`_schema.MetaData`
4146 collection
4147 that does not define this parameter. The
4148 :class:`_schema.Table`
4149 within the :class:`_schema.MetaData`
4150 will still be keyed based on its
4151 schema-qualified name, e.g.
4152 ``my_metadata.tables["some_schema.my_table"]``.
4154 The current behavior of the :class:`_schema.ForeignKey`
4155 object is to
4156 circumvent this restriction, where it can locate a table given
4157 the table name alone, where the schema will be assumed to be
4158 present from this value as specified on the owning
4159 :class:`_schema.MetaData` collection. However,
4160 this implies that a
4161 table qualified with BLANK_SCHEMA cannot currently be referred
4162 to by string name from :class:`_schema.ForeignKey`.
4163 Other parts of
4164 SQLAlchemy such as Declarative may not have similar behaviors
4165 built in, however may do so in a future release, along with a
4166 consistent method of referring to a table in BLANK_SCHEMA.
4169 .. seealso::
4171 :paramref:`_schema.Table.schema`
4173 :paramref:`.Sequence.schema`
4175 :param quote_schema:
4176 Sets the ``quote_schema`` flag for those :class:`_schema.Table`,
4177 :class:`.Sequence`, and other objects which make usage of the
4178 local ``schema`` name.
4180 :param info: Optional data dictionary which will be populated into the
4181 :attr:`.SchemaItem.info` attribute of this object.
4183 .. versionadded:: 1.0.0
4185 :param naming_convention: a dictionary referring to values which
4186 will establish default naming conventions for :class:`.Constraint`
4187 and :class:`.Index` objects, for those objects which are not given
4188 a name explicitly.
4190 The keys of this dictionary may be:
4192 * a constraint or Index class, e.g. the :class:`.UniqueConstraint`,
4193 :class:`_schema.ForeignKeyConstraint` class, the :class:`.Index`
4194 class
4196 * a string mnemonic for one of the known constraint classes;
4197 ``"fk"``, ``"pk"``, ``"ix"``, ``"ck"``, ``"uq"`` for foreign key,
4198 primary key, index, check, and unique constraint, respectively.
4200 * the string name of a user-defined "token" that can be used
4201 to define new naming tokens.
4203 The values associated with each "constraint class" or "constraint
4204 mnemonic" key are string naming templates, such as
4205 ``"uq_%(table_name)s_%(column_0_name)s"``,
4206 which describe how the name should be composed. The values
4207 associated with user-defined "token" keys should be callables of the
4208 form ``fn(constraint, table)``, which accepts the constraint/index
4209 object and :class:`_schema.Table` as arguments, returning a string
4210 result.
4212 The built-in names are as follows, some of which may only be
4213 available for certain types of constraint:
4215 * ``%(table_name)s`` - the name of the :class:`_schema.Table`
4216 object
4217 associated with the constraint.
4219 * ``%(referred_table_name)s`` - the name of the
4220 :class:`_schema.Table`
4221 object associated with the referencing target of a
4222 :class:`_schema.ForeignKeyConstraint`.
4224 * ``%(column_0_name)s`` - the name of the :class:`_schema.Column`
4225 at
4226 index position "0" within the constraint.
4228 * ``%(column_0N_name)s`` - the name of all :class:`_schema.Column`
4229 objects in order within the constraint, joined without a
4230 separator.
4232 * ``%(column_0_N_name)s`` - the name of all
4233 :class:`_schema.Column`
4234 objects in order within the constraint, joined with an
4235 underscore as a separator.
4237 * ``%(column_0_label)s``, ``%(column_0N_label)s``,
4238 ``%(column_0_N_label)s`` - the label of either the zeroth
4239 :class:`_schema.Column` or all :class:`.Columns`, separated with
4240 or without an underscore
4242 * ``%(column_0_key)s``, ``%(column_0N_key)s``,
4243 ``%(column_0_N_key)s`` - the key of either the zeroth
4244 :class:`_schema.Column` or all :class:`.Columns`, separated with
4245 or without an underscore
4247 * ``%(referred_column_0_name)s``, ``%(referred_column_0N_name)s``
4248 ``%(referred_column_0_N_name)s``, ``%(referred_column_0_key)s``,
4249 ``%(referred_column_0N_key)s``, ... column tokens which
4250 render the names/keys/labels of columns that are referenced
4251 by a :class:`_schema.ForeignKeyConstraint`.
4253 * ``%(constraint_name)s`` - a special key that refers to the
4254 existing name given to the constraint. When this key is
4255 present, the :class:`.Constraint` object's existing name will be
4256 replaced with one that is composed from template string that
4257 uses this token. When this token is present, it is required that
4258 the :class:`.Constraint` is given an explicit name ahead of time.
4260 * user-defined: any additional token may be implemented by passing
4261 it along with a ``fn(constraint, table)`` callable to the
4262 naming_convention dictionary.
4264 .. versionadded:: 1.3.0 - added new ``%(column_0N_name)s``,
4265 ``%(column_0_N_name)s``, and related tokens that produce
4266 concatenations of names, keys, or labels for all columns referred
4267 to by a given constraint.
4269 .. seealso::
4271 :ref:`constraint_naming_conventions` - for detailed usage
4272 examples.
4274 """
4275 self.tables = util.immutabledict()
4276 self.schema = quoted_name(schema, quote_schema)
4277 self.naming_convention = (
4278 naming_convention
4279 if naming_convention
4280 else DEFAULT_NAMING_CONVENTION
4281 )
4282 if info:
4283 self.info = info
4284 self._schemas = set()
4285 self._sequences = {}
4286 self._fk_memos = collections.defaultdict(list)
4288 self.bind = bind
4289 if reflect:
4290 if not bind:
4291 raise exc.ArgumentError(
4292 "A bind must be supplied in conjunction "
4293 "with reflect=True"
4294 )
4295 self.reflect()
4297 tables = None
4298 """A dictionary of :class:`_schema.Table`
4299 objects keyed to their name or "table key".
4301 The exact key is that determined by the :attr:`_schema.Table.key`
4302 attribute;
4303 for a table with no :attr:`_schema.Table.schema` attribute,
4304 this is the same
4305 as :attr:`_schema.Table.name`. For a table with a schema,
4306 it is typically of the
4307 form ``schemaname.tablename``.
4309 .. seealso::
4311 :attr:`_schema.MetaData.sorted_tables`
4313 """
4315 def __repr__(self):
4316 return "MetaData(bind=%r)" % self.bind
4318 def __contains__(self, table_or_key):
4319 if not isinstance(table_or_key, util.string_types):
4320 table_or_key = table_or_key.key
4321 return table_or_key in self.tables
4323 def _add_table(self, name, schema, table):
4324 key = _get_table_key(name, schema)
4325 dict.__setitem__(self.tables, key, table)
4326 if schema:
4327 self._schemas.add(schema)
4329 def _remove_table(self, name, schema):
4330 key = _get_table_key(name, schema)
4331 removed = dict.pop(self.tables, key, None)
4332 if removed is not None:
4333 for fk in removed.foreign_keys:
4334 fk._remove_from_metadata(self)
4335 if self._schemas:
4336 self._schemas = set(
4337 [
4338 t.schema
4339 for t in self.tables.values()
4340 if t.schema is not None
4341 ]
4342 )
4344 def __getstate__(self):
4345 return {
4346 "tables": self.tables,
4347 "schema": self.schema,
4348 "schemas": self._schemas,
4349 "sequences": self._sequences,
4350 "fk_memos": self._fk_memos,
4351 "naming_convention": self.naming_convention,
4352 }
4354 def __setstate__(self, state):
4355 self.tables = state["tables"]
4356 self.schema = state["schema"]
4357 self.naming_convention = state["naming_convention"]
4358 self._bind = None
4359 self._sequences = state["sequences"]
4360 self._schemas = state["schemas"]
4361 self._fk_memos = state["fk_memos"]
4363 def is_bound(self):
4364 """True if this MetaData is bound to an Engine or Connection."""
4366 return self._bind is not None
4368 def bind(self):
4369 """An :class:`_engine.Engine` or :class:`_engine.Connection`
4370 to which this
4371 :class:`_schema.MetaData` is bound.
4373 Typically, a :class:`_engine.Engine` is assigned to this attribute
4374 so that "implicit execution" may be used, or alternatively
4375 as a means of providing engine binding information to an
4376 ORM :class:`.Session` object::
4378 engine = create_engine("someurl://")
4379 metadata.bind = engine
4381 .. seealso::
4383 :ref:`dbengine_implicit` - background on "bound metadata"
4385 """
4386 return self._bind
4388 @util.dependencies("sqlalchemy.engine.url")
4389 def _bind_to(self, url, bind):
4390 """Bind this MetaData to an Engine, Connection, string or URL."""
4392 if isinstance(bind, util.string_types + (url.URL,)):
4393 self._bind = sqlalchemy.create_engine(bind)
4394 else:
4395 self._bind = bind
4397 bind = property(bind, _bind_to)
4399 def clear(self):
4400 """Clear all Table objects from this MetaData."""
4402 dict.clear(self.tables)
4403 self._schemas.clear()
4404 self._fk_memos.clear()
4406 def remove(self, table):
4407 """Remove the given Table object from this MetaData."""
4409 self._remove_table(table.name, table.schema)
4411 @property
4412 def sorted_tables(self):
4413 """Returns a list of :class:`_schema.Table` objects sorted in order of
4414 foreign key dependency.
4416 The sorting will place :class:`_schema.Table`
4417 objects that have dependencies
4418 first, before the dependencies themselves, representing the
4419 order in which they can be created. To get the order in which
4420 the tables would be dropped, use the ``reversed()`` Python built-in.
4422 .. warning::
4424 The :attr:`.MetaData.sorted_tables` attribute cannot by itself
4425 accommodate automatic resolution of dependency cycles between
4426 tables, which are usually caused by mutually dependent foreign key
4427 constraints. When these cycles are detected, the foreign keys
4428 of these tables are omitted from consideration in the sort.
4429 A warning is emitted when this condition occurs, which will be an
4430 exception raise in a future release. Tables which are not part
4431 of the cycle will still be returned in dependency order.
4433 To resolve these cycles, the
4434 :paramref:`_schema.ForeignKeyConstraint.use_alter` parameter may be
4435 applied to those constraints which create a cycle. Alternatively,
4436 the :func:`_schema.sort_tables_and_constraints` function will
4437 automatically return foreign key constraints in a separate
4438 collection when cycles are detected so that they may be applied
4439 to a schema separately.
4441 .. versionchanged:: 1.3.17 - a warning is emitted when
4442 :attr:`.MetaData.sorted_tables` cannot perform a proper sort
4443 due to cyclical dependencies. This will be an exception in a
4444 future release. Additionally, the sort will continue to return
4445 other tables not involved in the cycle in dependency order which
4446 was not the case previously.
4448 .. seealso::
4450 :func:`_schema.sort_tables`
4452 :func:`_schema.sort_tables_and_constraints`
4454 :attr:`_schema.MetaData.tables`
4456 :meth:`_reflection.Inspector.get_table_names`
4458 :meth:`_reflection.Inspector.get_sorted_table_and_fkc_names`
4461 """
4462 return ddl.sort_tables(
4463 sorted(self.tables.values(), key=lambda t: t.key)
4464 )
4466 def reflect(
4467 self,
4468 bind=None,
4469 schema=None,
4470 views=False,
4471 only=None,
4472 extend_existing=False,
4473 autoload_replace=True,
4474 resolve_fks=True,
4475 **dialect_kwargs
4476 ):
4477 r"""Load all available table definitions from the database.
4479 Automatically creates ``Table`` entries in this ``MetaData`` for any
4480 table available in the database but not yet present in the
4481 ``MetaData``. May be called multiple times to pick up tables recently
4482 added to the database, however no special action is taken if a table
4483 in this ``MetaData`` no longer exists in the database.
4485 :param bind:
4486 A :class:`.Connectable` used to access the database; if None, uses
4487 the existing bind on this ``MetaData``, if any.
4489 :param schema:
4490 Optional, query and reflect tables from an alternate schema.
4491 If None, the schema associated with this :class:`_schema.MetaData`
4492 is used, if any.
4494 :param views:
4495 If True, also reflect views.
4497 :param only:
4498 Optional. Load only a sub-set of available named tables. May be
4499 specified as a sequence of names or a callable.
4501 If a sequence of names is provided, only those tables will be
4502 reflected. An error is raised if a table is requested but not
4503 available. Named tables already present in this ``MetaData`` are
4504 ignored.
4506 If a callable is provided, it will be used as a boolean predicate to
4507 filter the list of potential table names. The callable is called
4508 with a table name and this ``MetaData`` instance as positional
4509 arguments and should return a true value for any table to reflect.
4511 :param extend_existing: Passed along to each :class:`_schema.Table` as
4512 :paramref:`_schema.Table.extend_existing`.
4514 .. versionadded:: 0.9.1
4516 :param autoload_replace: Passed along to each :class:`_schema.Table`
4517 as
4518 :paramref:`_schema.Table.autoload_replace`.
4520 .. versionadded:: 0.9.1
4522 :param resolve_fks: if True, reflect :class:`_schema.Table`
4523 objects linked
4524 to :class:`_schema.ForeignKey` objects located in each
4525 :class:`_schema.Table`.
4526 For :meth:`_schema.MetaData.reflect`,
4527 this has the effect of reflecting
4528 related tables that might otherwise not be in the list of tables
4529 being reflected, for example if the referenced table is in a
4530 different schema or is omitted via the
4531 :paramref:`.MetaData.reflect.only` parameter. When False,
4532 :class:`_schema.ForeignKey` objects are not followed to the
4533 :class:`_schema.Table`
4534 in which they link, however if the related table is also part of the
4535 list of tables that would be reflected in any case, the
4536 :class:`_schema.ForeignKey` object will still resolve to its related
4537 :class:`_schema.Table` after the :meth:`_schema.MetaData.reflect`
4538 operation is
4539 complete. Defaults to True.
4541 .. versionadded:: 1.3.0
4543 .. seealso::
4545 :paramref:`_schema.Table.resolve_fks`
4547 :param \**dialect_kwargs: Additional keyword arguments not mentioned
4548 above are dialect specific, and passed in the form
4549 ``<dialectname>_<argname>``. See the documentation regarding an
4550 individual dialect at :ref:`dialect_toplevel` for detail on
4551 documented arguments.
4553 .. versionadded:: 0.9.2 - Added
4554 :paramref:`.MetaData.reflect.**dialect_kwargs` to support
4555 dialect-level reflection options for all :class:`_schema.Table`
4556 objects reflected.
4558 """
4559 if bind is None:
4560 bind = _bind_or_error(self)
4562 with bind.connect() as conn:
4564 reflect_opts = {
4565 "autoload": True,
4566 "autoload_with": conn,
4567 "extend_existing": extend_existing,
4568 "autoload_replace": autoload_replace,
4569 "resolve_fks": resolve_fks,
4570 "_extend_on": set(),
4571 }
4573 reflect_opts.update(dialect_kwargs)
4575 if schema is None:
4576 schema = self.schema
4578 if schema is not None:
4579 reflect_opts["schema"] = schema
4581 available = util.OrderedSet(
4582 bind.engine.table_names(schema, connection=conn)
4583 )
4584 if views:
4585 available.update(bind.dialect.get_view_names(conn, schema))
4587 if schema is not None:
4588 available_w_schema = util.OrderedSet(
4589 ["%s.%s" % (schema, name) for name in available]
4590 )
4591 else:
4592 available_w_schema = available
4594 current = set(self.tables)
4596 if only is None:
4597 load = [
4598 name
4599 for name, schname in zip(available, available_w_schema)
4600 if extend_existing or schname not in current
4601 ]
4602 elif util.callable(only):
4603 load = [
4604 name
4605 for name, schname in zip(available, available_w_schema)
4606 if (extend_existing or schname not in current)
4607 and only(name, self)
4608 ]
4609 else:
4610 missing = [name for name in only if name not in available]
4611 if missing:
4612 s = schema and (" schema '%s'" % schema) or ""
4613 raise exc.InvalidRequestError(
4614 "Could not reflect: requested table(s) not available "
4615 "in %r%s: (%s)" % (bind.engine, s, ", ".join(missing))
4616 )
4617 load = [
4618 name
4619 for name in only
4620 if extend_existing or name not in current
4621 ]
4623 for name in load:
4624 try:
4625 Table(name, self, **reflect_opts)
4626 except exc.UnreflectableTableError as uerr:
4627 util.warn("Skipping table %s: %s" % (name, uerr))
4629 @util.deprecated(
4630 "0.7",
4631 "the :meth:`_schema.MetaData.append_ddl_listener` "
4632 "method is deprecated and "
4633 "will be removed in a future release. Please refer to "
4634 ":class:`.DDLEvents`.",
4635 )
4636 def append_ddl_listener(self, event_name, listener):
4637 """Append a DDL event listener to this ``MetaData``."""
4639 def adapt_listener(target, connection, **kw):
4640 tables = kw["tables"]
4641 listener(event, target, connection, tables=tables)
4643 event.listen(self, "" + event_name.replace("-", "_"), adapt_listener)
4645 def create_all(self, bind=None, tables=None, checkfirst=True):
4646 """Create all tables stored in this metadata.
4648 Conditional by default, will not attempt to recreate tables already
4649 present in the target database.
4651 :param bind:
4652 A :class:`.Connectable` used to access the
4653 database; if None, uses the existing bind on this ``MetaData``, if
4654 any.
4656 :param tables:
4657 Optional list of ``Table`` objects, which is a subset of the total
4658 tables in the ``MetaData`` (others are ignored).
4660 :param checkfirst:
4661 Defaults to True, don't issue CREATEs for tables already present
4662 in the target database.
4664 """
4665 if bind is None:
4666 bind = _bind_or_error(self)
4667 bind._run_visitor(
4668 ddl.SchemaGenerator, self, checkfirst=checkfirst, tables=tables
4669 )
4671 def drop_all(self, bind=None, tables=None, checkfirst=True):
4672 """Drop all tables stored in this metadata.
4674 Conditional by default, will not attempt to drop tables not present in
4675 the target database.
4677 :param bind:
4678 A :class:`.Connectable` used to access the
4679 database; if None, uses the existing bind on this ``MetaData``, if
4680 any.
4682 :param tables:
4683 Optional list of ``Table`` objects, which is a subset of the
4684 total tables in the ``MetaData`` (others are ignored).
4686 :param checkfirst:
4687 Defaults to True, only issue DROPs for tables confirmed to be
4688 present in the target database.
4690 """
4691 if bind is None:
4692 bind = _bind_or_error(self)
4693 bind._run_visitor(
4694 ddl.SchemaDropper, self, checkfirst=checkfirst, tables=tables
4695 )
4698class ThreadLocalMetaData(MetaData):
4699 """A MetaData variant that presents a different ``bind`` in every thread.
4701 Makes the ``bind`` property of the MetaData a thread-local value, allowing
4702 this collection of tables to be bound to different ``Engine``
4703 implementations or connections in each thread.
4705 The ThreadLocalMetaData starts off bound to None in each thread. Binds
4706 must be made explicitly by assigning to the ``bind`` property or using
4707 ``connect()``. You can also re-bind dynamically multiple times per
4708 thread, just like a regular ``MetaData``.
4710 """
4712 __visit_name__ = "metadata"
4714 def __init__(self):
4715 """Construct a ThreadLocalMetaData."""
4717 self.context = util.threading.local()
4718 self.__engines = {}
4719 super(ThreadLocalMetaData, self).__init__()
4721 def bind(self):
4722 """The bound Engine or Connection for this thread.
4724 This property may be assigned an Engine or Connection, or assigned a
4725 string or URL to automatically create a basic Engine for this bind
4726 with ``create_engine()``."""
4728 return getattr(self.context, "_engine", None)
4730 @util.dependencies("sqlalchemy.engine.url")
4731 def _bind_to(self, url, bind):
4732 """Bind to a Connectable in the caller's thread."""
4734 if isinstance(bind, util.string_types + (url.URL,)):
4735 try:
4736 self.context._engine = self.__engines[bind]
4737 except KeyError:
4738 e = sqlalchemy.create_engine(bind)
4739 self.__engines[bind] = e
4740 self.context._engine = e
4741 else:
4742 # TODO: this is squirrely. we shouldn't have to hold onto engines
4743 # in a case like this
4744 if bind not in self.__engines:
4745 self.__engines[bind] = bind
4746 self.context._engine = bind
4748 bind = property(bind, _bind_to)
4750 def is_bound(self):
4751 """True if there is a bind for this thread."""
4752 return (
4753 hasattr(self.context, "_engine")
4754 and self.context._engine is not None
4755 )
4757 def dispose(self):
4758 """Dispose all bound engines, in all thread contexts."""
4760 for e in self.__engines.values():
4761 if hasattr(e, "dispose"):
4762 e.dispose()
4765class _SchemaTranslateMap(object):
4766 """Provide translation of schema names based on a mapping.
4768 Also provides helpers for producing cache keys and optimized
4769 access when no mapping is present.
4771 Used by the :paramref:`.Connection.execution_options.schema_translate_map`
4772 feature.
4774 .. versionadded:: 1.1
4777 """
4779 __slots__ = "map_", "__call__", "hash_key", "is_default"
4781 _default_schema_getter = operator.attrgetter("schema")
4783 def __init__(self, map_):
4784 self.map_ = map_
4785 if map_ is not None:
4787 def schema_for_object(obj):
4788 effective_schema = self._default_schema_getter(obj)
4789 effective_schema = obj._translate_schema(
4790 effective_schema, map_
4791 )
4792 return effective_schema
4794 self.__call__ = schema_for_object
4795 self.hash_key = ";".join(
4796 "%s=%s" % (k, map_[k]) for k in sorted(map_, key=str)
4797 )
4798 self.is_default = False
4799 else:
4800 self.hash_key = 0
4801 self.__call__ = self._default_schema_getter
4802 self.is_default = True
4804 @classmethod
4805 def _schema_getter(cls, map_):
4806 if map_ is None:
4807 return _default_schema_map
4808 elif isinstance(map_, _SchemaTranslateMap):
4809 return map_
4810 else:
4811 return _SchemaTranslateMap(map_)
4814_default_schema_map = _SchemaTranslateMap(None)
4815_schema_getter = _SchemaTranslateMap._schema_getter
4818class Computed(FetchedValue, SchemaItem):
4819 """Defines a generated column, i.e. "GENERATED ALWAYS AS" syntax.
4821 The :class:`.Computed` construct is an inline construct added to the
4822 argument list of a :class:`_schema.Column` object::
4824 from sqlalchemy import Computed
4826 Table('square', meta,
4827 Column('side', Float, nullable=False),
4828 Column('area', Float, Computed('side * side'))
4829 )
4831 See the linked documentation below for complete details.
4833 .. versionadded:: 1.3.11
4835 .. seealso::
4837 :ref:`computed_ddl`
4839 """
4841 __visit_name__ = "computed_column"
4843 @_document_text_coercion(
4844 "sqltext", ":class:`.Computed`", ":paramref:`.Computed.sqltext`"
4845 )
4846 def __init__(self, sqltext, persisted=None):
4847 """Construct a GENERATED ALWAYS AS DDL construct to accompany a
4848 :class:`_schema.Column`.
4850 :param sqltext:
4851 A string containing the column generation expression, which will be
4852 used verbatim, or a SQL expression construct, such as a
4853 :func:`_expression.text`
4854 object. If given as a string, the object is converted to a
4855 :func:`_expression.text` object.
4857 :param persisted:
4858 Optional, controls how this column should be persisted by the
4859 database. Possible values are:
4861 * ``None``, the default, it will use the default persistence
4862 defined by the database.
4863 * ``True``, will render ``GENERATED ALWAYS AS ... STORED``, or the
4864 equivalent for the target database if supported.
4865 * ``False``, will render ``GENERATED ALWAYS AS ... VIRTUAL``, or
4866 the equivalent for the target database if supported.
4868 Specifying ``True`` or ``False`` may raise an error when the DDL
4869 is emitted to the target database if the database does not support
4870 that persistence option. Leaving this parameter at its default
4871 of ``None`` is guaranteed to succeed for all databases that support
4872 ``GENERATED ALWAYS AS``.
4874 """
4875 self.sqltext = _literal_as_text(sqltext, allow_coercion_to_text=True)
4876 self.persisted = persisted
4877 self.column = None
4879 def _set_parent(self, parent):
4880 if not isinstance(
4881 parent.server_default, (type(None), Computed)
4882 ) or not isinstance(parent.server_onupdate, (type(None), Computed)):
4883 raise exc.ArgumentError(
4884 "A generated column cannot specify a server_default or a "
4885 "server_onupdate argument"
4886 )
4887 self.column = parent
4888 parent.computed = self
4889 self.column.server_onupdate = self
4890 self.column.server_default = self
4892 def _as_for_update(self, for_update):
4893 return self
4895 def copy(self, target_table=None, **kw):
4896 sqltext = _copy_expression(
4897 self.sqltext,
4898 self.column.table if self.column is not None else None,
4899 target_table,
4900 )
4901 g = Computed(sqltext, persisted=self.persisted)
4903 return self._schema_item_copy(g)