Coverage for /home/martinb/.local/share/virtualenvs/camcops/lib/python3.6/site-packages/sqlalchemy/sql/schema.py : 56%

Hot-keys on this page
r m x p 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-2020 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 def replace(col):
84 if (
85 isinstance(col, Column)
86 and col.table is source_table
87 and col.key in source_table.c
88 ):
89 return target_table.c[col.key]
90 else:
91 return None
93 return visitors.replacement_traverse(expression, {}, replace)
96@inspection._self_inspects
97class SchemaItem(SchemaEventTarget, visitors.Visitable):
98 """Base class for items that define a database schema."""
100 __visit_name__ = "schema_item"
102 def _init_items(self, *args):
103 """Initialize the list of child items for this SchemaItem."""
105 for item in args:
106 if item is not None:
107 try:
108 spwd = item._set_parent_with_dispatch
109 except AttributeError as err:
110 util.raise_(
111 exc.ArgumentError(
112 "'SchemaItem' object, such as a 'Column' or a "
113 "'Constraint' expected, got %r" % item
114 ),
115 replace_context=err,
116 )
117 else:
118 spwd(self)
120 def get_children(self, **kwargs):
121 """used to allow SchemaVisitor access"""
122 return []
124 def __repr__(self):
125 return util.generic_repr(self, omit_kwarg=["info"])
127 @property
128 @util.deprecated(
129 "0.9",
130 "The :attr:`.SchemaItem.quote` attribute is deprecated and will be "
131 "removed in a future release. Use the :attr:`.quoted_name.quote` "
132 "attribute on the ``name`` field of the target schema item to retrieve"
133 "quoted status.",
134 )
135 def quote(self):
136 """Return the value of the ``quote`` flag passed
137 to this schema object, for those schema items which
138 have a ``name`` field.
140 """
142 return self.name.quote
144 @util.memoized_property
145 def info(self):
146 """Info dictionary associated with the object, allowing user-defined
147 data to be associated with this :class:`.SchemaItem`.
149 The dictionary is automatically generated when first accessed.
150 It can also be specified in the constructor of some objects,
151 such as :class:`_schema.Table` and :class:`_schema.Column`.
153 """
154 return {}
156 def _schema_item_copy(self, schema_item):
157 if "info" in self.__dict__:
158 schema_item.info = self.info.copy()
159 schema_item.dispatch._update(self.dispatch)
160 return schema_item
162 def _translate_schema(self, effective_schema, map_):
163 return map_.get(effective_schema, effective_schema)
166class Table(DialectKWArgs, SchemaItem, TableClause):
167 r"""Represent a table in a database.
169 e.g.::
171 mytable = Table("mytable", metadata,
172 Column('mytable_id', Integer, primary_key=True),
173 Column('value', String(50))
174 )
176 The :class:`_schema.Table`
177 object constructs a unique instance of itself based
178 on its name and optional schema name within the given
179 :class:`_schema.MetaData` object. Calling the :class:`_schema.Table`
180 constructor with the same name and same :class:`_schema.MetaData` argument
181 a second time will return the *same* :class:`_schema.Table`
182 object - in this way
183 the :class:`_schema.Table` constructor acts as a registry function.
185 .. seealso::
187 :ref:`metadata_describing` - Introduction to database metadata
189 Constructor arguments are as follows:
191 :param name: The name of this table as represented in the database.
193 The table name, along with the value of the ``schema`` parameter,
194 forms a key which uniquely identifies this :class:`_schema.Table`
195 within
196 the owning :class:`_schema.MetaData` collection.
197 Additional calls to :class:`_schema.Table` with the same name,
198 metadata,
199 and schema name will return the same :class:`_schema.Table` object.
201 Names which contain no upper case characters
202 will be treated as case insensitive names, and will not be quoted
203 unless they are a reserved word or contain special characters.
204 A name with any number of upper case characters is considered
205 to be case sensitive, and will be sent as quoted.
207 To enable unconditional quoting for the table name, specify the flag
208 ``quote=True`` to the constructor, or use the :class:`.quoted_name`
209 construct to specify the name.
211 :param metadata: a :class:`_schema.MetaData`
212 object which will contain this
213 table. The metadata is used as a point of association of this table
214 with other tables which are referenced via foreign key. It also
215 may be used to associate this table with a particular
216 :class:`.Connectable`.
218 :param \*args: Additional positional arguments are used primarily
219 to add the list of :class:`_schema.Column`
220 objects contained within this
221 table. Similar to the style of a CREATE TABLE statement, other
222 :class:`.SchemaItem` constructs may be added here, including
223 :class:`.PrimaryKeyConstraint`, and
224 :class:`_schema.ForeignKeyConstraint`.
226 :param autoload: Defaults to False, unless
227 :paramref:`_schema.Table.autoload_with`
228 is set in which case it defaults to True; :class:`_schema.Column`
229 objects
230 for this table should be reflected from the database, possibly
231 augmenting or replacing existing :class:`_schema.Column`
232 objects that were
233 explicitly specified.
235 .. versionchanged:: 1.0.0 setting the
236 :paramref:`_schema.Table.autoload_with`
237 parameter implies that :paramref:`_schema.Table.autoload`
238 will default
239 to True.
241 .. seealso::
243 :ref:`metadata_reflection_toplevel`
245 :param autoload_replace: Defaults to ``True``; when using
246 :paramref:`_schema.Table.autoload`
247 in conjunction with :paramref:`_schema.Table.extend_existing`,
248 indicates
249 that :class:`_schema.Column` objects present in the already-existing
250 :class:`_schema.Table`
251 object should be replaced with columns of the same
252 name retrieved from the autoload process. When ``False``, columns
253 already present under existing names will be omitted from the
254 reflection process.
256 Note that this setting does not impact :class:`_schema.Column` objects
257 specified programmatically within the call to :class:`_schema.Table`
258 that
259 also is autoloading; those :class:`_schema.Column` objects will always
260 replace existing columns of the same name when
261 :paramref:`_schema.Table.extend_existing` is ``True``.
263 .. seealso::
265 :paramref:`_schema.Table.autoload`
267 :paramref:`_schema.Table.extend_existing`
269 :param autoload_with: An :class:`_engine.Engine` or
270 :class:`_engine.Connection` object
271 with which this :class:`_schema.Table` object will be reflected; when
272 set to a non-None value, it implies that
273 :paramref:`_schema.Table.autoload`
274 is ``True``. If left unset, but :paramref:`_schema.Table.autoload`
275 is
276 explicitly set to ``True``, an autoload operation will attempt to
277 proceed by locating an :class:`_engine.Engine` or
278 :class:`_engine.Connection` bound
279 to the underlying :class:`_schema.MetaData` object.
281 .. seealso::
283 :paramref:`_schema.Table.autoload`
285 :param extend_existing: When ``True``, indicates that if this
286 :class:`_schema.Table` is already present in the given
287 :class:`_schema.MetaData`,
288 apply further arguments within the constructor to the existing
289 :class:`_schema.Table`.
291 If :paramref:`_schema.Table.extend_existing` or
292 :paramref:`_schema.Table.keep_existing` are not set,
293 and the given name
294 of the new :class:`_schema.Table` refers to a :class:`_schema.Table`
295 that is
296 already present in the target :class:`_schema.MetaData` collection,
297 and
298 this :class:`_schema.Table`
299 specifies additional columns or other constructs
300 or flags that modify the table's state, an
301 error is raised. The purpose of these two mutually-exclusive flags
302 is to specify what action should be taken when a
303 :class:`_schema.Table`
304 is specified that matches an existing :class:`_schema.Table`,
305 yet specifies
306 additional constructs.
308 :paramref:`_schema.Table.extend_existing`
309 will also work in conjunction
310 with :paramref:`_schema.Table.autoload` to run a new reflection
311 operation against the database, even if a :class:`_schema.Table`
312 of the same name is already present in the target
313 :class:`_schema.MetaData`; newly reflected :class:`_schema.Column`
314 objects
315 and other options will be added into the state of the
316 :class:`_schema.Table`, potentially overwriting existing columns
317 and options of the same name.
319 As is always the case with :paramref:`_schema.Table.autoload`,
320 :class:`_schema.Column` objects can be specified in the same
321 :class:`_schema.Table`
322 constructor, which will take precedence. Below, the existing
323 table ``mytable`` will be augmented with :class:`_schema.Column`
324 objects
325 both reflected from the database, as well as the given
326 :class:`_schema.Column`
327 named "y"::
329 Table("mytable", metadata,
330 Column('y', Integer),
331 extend_existing=True,
332 autoload=True,
333 autoload_with=engine
334 )
336 .. seealso::
338 :paramref:`_schema.Table.autoload`
340 :paramref:`_schema.Table.autoload_replace`
342 :paramref:`_schema.Table.keep_existing`
345 :param implicit_returning: True by default - indicates that
346 RETURNING can be used by default to fetch newly inserted primary key
347 values, for backends which support this. Note that
348 create_engine() also provides an implicit_returning flag.
350 :param include_columns: A list of strings indicating a subset of
351 columns to be loaded via the ``autoload`` operation; table columns who
352 aren't present in this list will not be represented on the resulting
353 ``Table`` object. Defaults to ``None`` which indicates all columns
354 should be reflected.
356 :param resolve_fks: Whether or not to reflect :class:`_schema.Table`
357 objects
358 related to this one via :class:`_schema.ForeignKey` objects, when
359 :paramref:`_schema.Table.autoload` or
360 :paramref:`_schema.Table.autoload_with` is
361 specified. Defaults to True. Set to False to disable reflection of
362 related tables as :class:`_schema.ForeignKey`
363 objects are encountered; may be
364 used either to save on SQL calls or to avoid issues with related tables
365 that can't be accessed. Note that if a related table is already present
366 in the :class:`_schema.MetaData` collection, or becomes present later,
367 a
368 :class:`_schema.ForeignKey` object associated with this
369 :class:`_schema.Table` will
370 resolve to that table normally.
372 .. versionadded:: 1.3
374 .. seealso::
376 :paramref:`.MetaData.reflect.resolve_fks`
379 :param info: Optional data dictionary which will be populated into the
380 :attr:`.SchemaItem.info` attribute of this object.
382 :param keep_existing: When ``True``, indicates that if this Table
383 is already present in the given :class:`_schema.MetaData`, ignore
384 further arguments within the constructor to the existing
385 :class:`_schema.Table`, and return the :class:`_schema.Table`
386 object as
387 originally created. This is to allow a function that wishes
388 to define a new :class:`_schema.Table` on first call, but on
389 subsequent calls will return the same :class:`_schema.Table`,
390 without any of the declarations (particularly constraints)
391 being applied a second time.
393 If :paramref:`_schema.Table.extend_existing` or
394 :paramref:`_schema.Table.keep_existing` are not set,
395 and the given name
396 of the new :class:`_schema.Table` refers to a :class:`_schema.Table`
397 that is
398 already present in the target :class:`_schema.MetaData` collection,
399 and
400 this :class:`_schema.Table`
401 specifies additional columns or other constructs
402 or flags that modify the table's state, an
403 error is raised. The purpose of these two mutually-exclusive flags
404 is to specify what action should be taken when a
405 :class:`_schema.Table`
406 is specified that matches an existing :class:`_schema.Table`,
407 yet specifies
408 additional constructs.
410 .. seealso::
412 :paramref:`_schema.Table.extend_existing`
414 :param listeners: A list of tuples of the form ``(<eventname>, <fn>)``
415 which will be passed to :func:`.event.listen` upon construction.
416 This alternate hook to :func:`.event.listen` allows the establishment
417 of a listener function specific to this :class:`_schema.Table` before
418 the "autoload" process begins. Particularly useful for
419 the :meth:`.DDLEvents.column_reflect` event::
421 def listen_for_reflect(table, column_info):
422 "handle the column reflection event"
423 # ...
425 t = Table(
426 'sometable',
427 autoload=True,
428 listeners=[
429 ('column_reflect', listen_for_reflect)
430 ])
432 :param mustexist: When ``True``, indicates that this Table must already
433 be present in the given :class:`_schema.MetaData` collection, else
434 an exception is raised.
436 :param prefixes:
437 A list of strings to insert after CREATE in the CREATE TABLE
438 statement. They will be separated by spaces.
440 :param quote: Force quoting of this table's name on or off, corresponding
441 to ``True`` or ``False``. When left at its default of ``None``,
442 the column identifier will be quoted according to whether the name is
443 case sensitive (identifiers with at least one upper case character are
444 treated as case sensitive), or if it's a reserved word. This flag
445 is only needed to force quoting of a reserved word which is not known
446 by the SQLAlchemy dialect.
448 :param quote_schema: same as 'quote' but applies to the schema identifier.
450 :param schema: The schema name for this table, which is required if
451 the table resides in a schema other than the default selected schema
452 for the engine's database connection. Defaults to ``None``.
454 If the owning :class:`_schema.MetaData` of this :class:`_schema.Table`
455 specifies its
456 own :paramref:`_schema.MetaData.schema` parameter,
457 then that schema name will
458 be applied to this :class:`_schema.Table`
459 if the schema parameter here is set
460 to ``None``. To set a blank schema name on a :class:`_schema.Table`
461 that
462 would otherwise use the schema set on the owning
463 :class:`_schema.MetaData`,
464 specify the special symbol :attr:`.BLANK_SCHEMA`.
466 .. versionadded:: 1.0.14 Added the :attr:`.BLANK_SCHEMA` symbol to
467 allow a :class:`_schema.Table`
468 to have a blank schema name even when the
469 parent :class:`_schema.MetaData` specifies
470 :paramref:`_schema.MetaData.schema`.
472 The quoting rules for the schema name are the same as those for the
473 ``name`` parameter, in that quoting is applied for reserved words or
474 case-sensitive names; to enable unconditional quoting for the schema
475 name, specify the flag ``quote_schema=True`` to the constructor, or use
476 the :class:`.quoted_name` construct to specify the name.
478 :param useexisting: the same as :paramref:`_schema.Table.extend_existing`.
480 :param comment: Optional string that will render an SQL comment on table
481 creation.
483 .. versionadded:: 1.2 Added the :paramref:`_schema.Table.comment`
484 parameter
485 to :class:`_schema.Table`.
487 :param \**kw: Additional keyword arguments not mentioned above are
488 dialect specific, and passed in the form ``<dialectname>_<argname>``.
489 See the documentation regarding an individual dialect at
490 :ref:`dialect_toplevel` for detail on documented arguments.
492 """
494 __visit_name__ = "table"
496 @util.deprecated_params(
497 useexisting=(
498 "0.7",
499 "The :paramref:`_schema.Table.useexisting` "
500 "parameter is deprecated and "
501 "will be removed in a future release. Please use "
502 ":paramref:`_schema.Table.extend_existing`.",
503 )
504 )
505 def __new__(cls, *args, **kw):
506 if not args:
507 # python3k pickle seems to call this
508 return object.__new__(cls)
510 try:
511 name, metadata, args = args[0], args[1], args[2:]
512 except IndexError:
513 raise TypeError("Table() takes at least two arguments")
515 schema = kw.get("schema", None)
516 if schema is None:
517 schema = metadata.schema
518 elif schema is BLANK_SCHEMA:
519 schema = None
520 keep_existing = kw.pop("keep_existing", False)
521 extend_existing = kw.pop("extend_existing", False)
522 if "useexisting" in kw:
523 if extend_existing:
524 msg = "useexisting is synonymous with extend_existing."
525 raise exc.ArgumentError(msg)
526 extend_existing = kw.pop("useexisting", False)
528 if keep_existing and extend_existing:
529 msg = "keep_existing and extend_existing are mutually exclusive."
530 raise exc.ArgumentError(msg)
532 mustexist = kw.pop("mustexist", False)
533 key = _get_table_key(name, schema)
534 if key in metadata.tables:
535 if not keep_existing and not extend_existing and bool(args):
536 raise exc.InvalidRequestError(
537 "Table '%s' is already defined for this MetaData "
538 "instance. Specify 'extend_existing=True' "
539 "to redefine "
540 "options and columns on an "
541 "existing Table object." % key
542 )
543 table = metadata.tables[key]
544 if extend_existing:
545 table._init_existing(*args, **kw)
546 return table
547 else:
548 if mustexist:
549 raise exc.InvalidRequestError("Table '%s' not defined" % (key))
550 table = object.__new__(cls)
551 table.dispatch.before_parent_attach(table, metadata)
552 metadata._add_table(name, schema, table)
553 try:
554 table._init(name, metadata, *args, **kw)
555 table.dispatch.after_parent_attach(table, metadata)
556 return table
557 except:
558 with util.safe_reraise():
559 metadata._remove_table(name, schema)
561 @property
562 @util.deprecated(
563 "0.9",
564 "The :meth:`.SchemaItem.quote` method is deprecated and will be "
565 "removed in a future release. Use the :attr:`.quoted_name.quote` "
566 "attribute on the ``schema`` field of the target schema item to "
567 "retrieve quoted status.",
568 )
569 def quote_schema(self):
570 """Return the value of the ``quote_schema`` flag passed
571 to this :class:`_schema.Table`.
572 """
574 return self.schema.quote
576 def __init__(self, *args, **kw):
577 """Constructor for :class:`_schema.Table`.
579 This method is a no-op. See the top-level
580 documentation for :class:`_schema.Table`
581 for constructor arguments.
583 """
584 # __init__ is overridden to prevent __new__ from
585 # calling the superclass constructor.
587 def _init(self, name, metadata, *args, **kwargs):
588 super(Table, self).__init__(
589 quoted_name(name, kwargs.pop("quote", None))
590 )
591 self.metadata = metadata
593 self.schema = kwargs.pop("schema", None)
594 if self.schema is None:
595 self.schema = metadata.schema
596 elif self.schema is BLANK_SCHEMA:
597 self.schema = None
598 else:
599 quote_schema = kwargs.pop("quote_schema", None)
600 self.schema = quoted_name(self.schema, quote_schema)
602 self.indexes = set()
603 self.constraints = set()
604 self._columns = ColumnCollection()
605 PrimaryKeyConstraint(
606 _implicit_generated=True
607 )._set_parent_with_dispatch(self)
608 self.foreign_keys = set()
609 self._extra_dependencies = set()
610 if self.schema is not None:
611 self.fullname = "%s.%s" % (self.schema, self.name)
612 else:
613 self.fullname = self.name
615 autoload_with = kwargs.pop("autoload_with", None)
616 autoload = kwargs.pop("autoload", autoload_with is not None)
617 # this argument is only used with _init_existing()
618 kwargs.pop("autoload_replace", True)
619 _extend_on = kwargs.pop("_extend_on", None)
621 resolve_fks = kwargs.pop("resolve_fks", True)
622 include_columns = kwargs.pop("include_columns", None)
624 self.implicit_returning = kwargs.pop("implicit_returning", True)
626 self.comment = kwargs.pop("comment", None)
628 if "info" in kwargs:
629 self.info = kwargs.pop("info")
630 if "listeners" in kwargs:
631 listeners = kwargs.pop("listeners")
632 for evt, fn in listeners:
633 event.listen(self, evt, fn)
635 self._prefixes = kwargs.pop("prefixes", [])
637 self._extra_kwargs(**kwargs)
639 # load column definitions from the database if 'autoload' is defined
640 # we do it after the table is in the singleton dictionary to support
641 # circular foreign keys
642 if autoload:
643 self._autoload(
644 metadata,
645 autoload_with,
646 include_columns,
647 _extend_on=_extend_on,
648 resolve_fks=resolve_fks,
649 )
651 # initialize all the column, etc. objects. done after reflection to
652 # allow user-overrides
653 self._init_items(*args)
655 def _autoload(
656 self,
657 metadata,
658 autoload_with,
659 include_columns,
660 exclude_columns=(),
661 resolve_fks=True,
662 _extend_on=None,
663 ):
665 if autoload_with:
666 autoload_with.run_callable(
667 autoload_with.dialect.reflecttable,
668 self,
669 include_columns,
670 exclude_columns,
671 resolve_fks,
672 _extend_on=_extend_on,
673 )
674 else:
675 bind = _bind_or_error(
676 metadata,
677 msg="No engine is bound to this Table's MetaData. "
678 "Pass an engine to the Table via "
679 "autoload_with=<someengine>, "
680 "or associate the MetaData with an engine via "
681 "metadata.bind=<someengine>",
682 )
683 bind.run_callable(
684 bind.dialect.reflecttable,
685 self,
686 include_columns,
687 exclude_columns,
688 resolve_fks,
689 _extend_on=_extend_on,
690 )
692 @property
693 def _sorted_constraints(self):
694 """Return the set of constraints as a list, sorted by creation
695 order.
697 """
698 return sorted(self.constraints, key=lambda c: c._creation_order)
700 @property
701 def foreign_key_constraints(self):
702 """:class:`_schema.ForeignKeyConstraint` objects referred to by this
703 :class:`_schema.Table`.
705 This list is produced from the collection of
706 :class:`_schema.ForeignKey`
707 objects currently associated.
709 .. versionadded:: 1.0.0
711 """
712 return set(fkc.constraint for fkc in self.foreign_keys)
714 def _init_existing(self, *args, **kwargs):
715 autoload_with = kwargs.pop("autoload_with", None)
716 autoload = kwargs.pop("autoload", autoload_with is not None)
717 autoload_replace = kwargs.pop("autoload_replace", True)
718 schema = kwargs.pop("schema", None)
719 _extend_on = kwargs.pop("_extend_on", None)
721 if schema and schema != self.schema:
722 raise exc.ArgumentError(
723 "Can't change schema of existing table from '%s' to '%s'",
724 (self.schema, schema),
725 )
727 include_columns = kwargs.pop("include_columns", None)
729 resolve_fks = kwargs.pop("resolve_fks", True)
731 if include_columns is not None:
732 for c in self.c:
733 if c.name not in include_columns:
734 self._columns.remove(c)
736 for key in ("quote", "quote_schema"):
737 if key in kwargs:
738 raise exc.ArgumentError(
739 "Can't redefine 'quote' or 'quote_schema' arguments"
740 )
742 if "comment" in kwargs:
743 self.comment = kwargs.pop("comment", None)
745 if "info" in kwargs:
746 self.info = kwargs.pop("info")
748 if autoload:
749 if not autoload_replace:
750 # don't replace columns already present.
751 # we'd like to do this for constraints also however we don't
752 # have simple de-duping for unnamed constraints.
753 exclude_columns = [c.name for c in self.c]
754 else:
755 exclude_columns = ()
756 self._autoload(
757 self.metadata,
758 autoload_with,
759 include_columns,
760 exclude_columns,
761 resolve_fks,
762 _extend_on=_extend_on,
763 )
765 self._extra_kwargs(**kwargs)
766 self._init_items(*args)
768 def _extra_kwargs(self, **kwargs):
769 self._validate_dialect_kwargs(kwargs)
771 def _init_collections(self):
772 pass
774 def _reset_exported(self):
775 pass
777 @property
778 def _autoincrement_column(self):
779 return self.primary_key._autoincrement_column
781 @property
782 def key(self):
783 """Return the 'key' for this :class:`_schema.Table`.
785 This value is used as the dictionary key within the
786 :attr:`_schema.MetaData.tables` collection. It is typically the same
787 as that of :attr:`_schema.Table.name` for a table with no
788 :attr:`_schema.Table.schema`
789 set; otherwise it is typically of the form
790 ``schemaname.tablename``.
792 """
793 return _get_table_key(self.name, self.schema)
795 def __repr__(self):
796 return "Table(%s)" % ", ".join(
797 [repr(self.name)]
798 + [repr(self.metadata)]
799 + [repr(x) for x in self.columns]
800 + ["%s=%s" % (k, repr(getattr(self, k))) for k in ["schema"]]
801 )
803 def __str__(self):
804 return _get_table_key(self.description, self.schema)
806 @property
807 def bind(self):
808 """Return the connectable associated with this Table."""
810 return self.metadata and self.metadata.bind or None
812 def add_is_dependent_on(self, table):
813 """Add a 'dependency' for this Table.
815 This is another Table object which must be created
816 first before this one can, or dropped after this one.
818 Usually, dependencies between tables are determined via
819 ForeignKey objects. However, for other situations that
820 create dependencies outside of foreign keys (rules, inheriting),
821 this method can manually establish such a link.
823 """
824 self._extra_dependencies.add(table)
826 def append_column(self, column):
827 """Append a :class:`_schema.Column` to this :class:`_schema.Table`.
829 The "key" of the newly added :class:`_schema.Column`, i.e. the
830 value of its ``.key`` attribute, will then be available
831 in the ``.c`` collection of this :class:`_schema.Table`, and the
832 column definition will be included in any CREATE TABLE, SELECT,
833 UPDATE, etc. statements generated from this :class:`_schema.Table`
834 construct.
836 Note that this does **not** change the definition of the table
837 as it exists within any underlying database, assuming that
838 table has already been created in the database. Relational
839 databases support the addition of columns to existing tables
840 using the SQL ALTER command, which would need to be
841 emitted for an already-existing table that doesn't contain
842 the newly added column.
844 """
846 column._set_parent_with_dispatch(self)
848 def append_constraint(self, constraint):
849 """Append a :class:`_schema.Constraint` to this
850 :class:`_schema.Table`.
852 This has the effect of the constraint being included in any
853 future CREATE TABLE statement, assuming specific DDL creation
854 events have not been associated with the given
855 :class:`_schema.Constraint` object.
857 Note that this does **not** produce the constraint within the
858 relational database automatically, for a table that already exists
859 in the database. To add a constraint to an
860 existing relational database table, the SQL ALTER command must
861 be used. SQLAlchemy also provides the
862 :class:`.AddConstraint` construct which can produce this SQL when
863 invoked as an executable clause.
865 """
867 constraint._set_parent_with_dispatch(self)
869 @util.deprecated(
870 "0.7",
871 "the :meth:`_schema.Table.append_ddl_listener` "
872 "method is deprecated and "
873 "will be removed in a future release. Please refer to "
874 ":class:`.DDLEvents`.",
875 )
876 def append_ddl_listener(self, event_name, listener):
877 """Append a DDL event listener to this ``Table``.
879 """
881 def adapt_listener(target, connection, **kw):
882 listener(event_name, target, connection)
884 event.listen(self, "" + event_name.replace("-", "_"), adapt_listener)
886 def _set_parent(self, metadata):
887 metadata._add_table(self.name, self.schema, self)
888 self.metadata = metadata
890 def get_children(
891 self, column_collections=True, schema_visitor=False, **kw
892 ):
893 if not schema_visitor:
894 return TableClause.get_children(
895 self, column_collections=column_collections, **kw
896 )
897 else:
898 if column_collections:
899 return list(self.columns)
900 else:
901 return []
903 def exists(self, bind=None):
904 """Return True if this table exists."""
906 if bind is None:
907 bind = _bind_or_error(self)
909 return bind.run_callable(
910 bind.dialect.has_table, self.name, schema=self.schema
911 )
913 def create(self, bind=None, checkfirst=False):
914 """Issue a ``CREATE`` statement for this
915 :class:`_schema.Table`, using the given :class:`.Connectable`
916 for connectivity.
918 .. seealso::
920 :meth:`_schema.MetaData.create_all`.
922 """
924 if bind is None:
925 bind = _bind_or_error(self)
926 bind._run_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
928 def drop(self, bind=None, checkfirst=False):
929 """Issue a ``DROP`` statement for this
930 :class:`_schema.Table`, using the given :class:`.Connectable`
931 for connectivity.
933 .. seealso::
935 :meth:`_schema.MetaData.drop_all`.
937 """
938 if bind is None:
939 bind = _bind_or_error(self)
940 bind._run_visitor(ddl.SchemaDropper, self, checkfirst=checkfirst)
942 def tometadata(
943 self,
944 metadata,
945 schema=RETAIN_SCHEMA,
946 referred_schema_fn=None,
947 name=None,
948 ):
949 """Return a copy of this :class:`_schema.Table`
950 associated with a different
951 :class:`_schema.MetaData`.
953 E.g.::
955 m1 = MetaData()
957 user = Table('user', m1, Column('id', Integer, primary_key=True))
959 m2 = MetaData()
960 user_copy = user.tometadata(m2)
962 :param metadata: Target :class:`_schema.MetaData` object,
963 into which the
964 new :class:`_schema.Table` object will be created.
966 :param schema: optional string name indicating the target schema.
967 Defaults to the special symbol :attr:`.RETAIN_SCHEMA` which indicates
968 that no change to the schema name should be made in the new
969 :class:`_schema.Table`. If set to a string name, the new
970 :class:`_schema.Table`
971 will have this new name as the ``.schema``. If set to ``None``, the
972 schema will be set to that of the schema set on the target
973 :class:`_schema.MetaData`, which is typically ``None`` as well,
974 unless
975 set explicitly::
977 m2 = MetaData(schema='newschema')
979 # user_copy_one will have "newschema" as the schema name
980 user_copy_one = user.tometadata(m2, schema=None)
982 m3 = MetaData() # schema defaults to None
984 # user_copy_two will have None as the schema name
985 user_copy_two = user.tometadata(m3, schema=None)
987 :param referred_schema_fn: optional callable which can be supplied
988 in order to provide for the schema name that should be assigned
989 to the referenced table of a :class:`_schema.ForeignKeyConstraint`.
990 The callable accepts this parent :class:`_schema.Table`, the
991 target schema that we are changing to, the
992 :class:`_schema.ForeignKeyConstraint` object, and the existing
993 "target schema" of that constraint. The function should return the
994 string schema name that should be applied.
995 E.g.::
997 def referred_schema_fn(table, to_schema,
998 constraint, referred_schema):
999 if referred_schema == 'base_tables':
1000 return referred_schema
1001 else:
1002 return to_schema
1004 new_table = table.tometadata(m2, schema="alt_schema",
1005 referred_schema_fn=referred_schema_fn)
1007 .. versionadded:: 0.9.2
1009 :param name: optional string name indicating the target table name.
1010 If not specified or None, the table name is retained. This allows
1011 a :class:`_schema.Table` to be copied to the same
1012 :class:`_schema.MetaData` target
1013 with a new name.
1015 .. versionadded:: 1.0.0
1017 """
1018 if name is None:
1019 name = self.name
1020 if schema is RETAIN_SCHEMA:
1021 schema = self.schema
1022 elif schema is None:
1023 schema = metadata.schema
1024 key = _get_table_key(name, schema)
1025 if key in metadata.tables:
1026 util.warn(
1027 "Table '%s' already exists within the given "
1028 "MetaData - not copying." % self.description
1029 )
1030 return metadata.tables[key]
1032 args = []
1033 for c in self.columns:
1034 args.append(c.copy(schema=schema))
1035 table = Table(
1036 name,
1037 metadata,
1038 schema=schema,
1039 comment=self.comment,
1040 *args,
1041 **self.kwargs
1042 )
1043 for c in self.constraints:
1044 if isinstance(c, ForeignKeyConstraint):
1045 referred_schema = c._referred_schema
1046 if referred_schema_fn:
1047 fk_constraint_schema = referred_schema_fn(
1048 self, schema, c, referred_schema
1049 )
1050 else:
1051 fk_constraint_schema = (
1052 schema if referred_schema == self.schema else None
1053 )
1054 table.append_constraint(
1055 c.copy(schema=fk_constraint_schema, target_table=table)
1056 )
1057 elif not c._type_bound:
1058 # skip unique constraints that would be generated
1059 # by the 'unique' flag on Column
1060 if c._column_flag:
1061 continue
1063 table.append_constraint(
1064 c.copy(schema=schema, target_table=table)
1065 )
1066 for index in self.indexes:
1067 # skip indexes that would be generated
1068 # by the 'index' flag on Column
1069 if index._column_flag:
1070 continue
1071 Index(
1072 index.name,
1073 unique=index.unique,
1074 *[
1075 _copy_expression(expr, self, table)
1076 for expr in index.expressions
1077 ],
1078 _table=table,
1079 **index.kwargs
1080 )
1081 return self._schema_item_copy(table)
1084class Column(DialectKWArgs, SchemaItem, ColumnClause):
1085 """Represents a column in a database table."""
1087 __visit_name__ = "column"
1089 def __init__(self, *args, **kwargs):
1090 r"""
1091 Construct a new ``Column`` object.
1093 :param name: The name of this column as represented in the database.
1094 This argument may be the first positional argument, or specified
1095 via keyword.
1097 Names which contain no upper case characters
1098 will be treated as case insensitive names, and will not be quoted
1099 unless they are a reserved word. Names with any number of upper
1100 case characters will be quoted and sent exactly. Note that this
1101 behavior applies even for databases which standardize upper
1102 case names as case insensitive such as Oracle.
1104 The name field may be omitted at construction time and applied
1105 later, at any time before the Column is associated with a
1106 :class:`_schema.Table`. This is to support convenient
1107 usage within the :mod:`~sqlalchemy.ext.declarative` extension.
1109 :param type\_: The column's type, indicated using an instance which
1110 subclasses :class:`~sqlalchemy.types.TypeEngine`. If no arguments
1111 are required for the type, the class of the type can be sent
1112 as well, e.g.::
1114 # use a type with arguments
1115 Column('data', String(50))
1117 # use no arguments
1118 Column('level', Integer)
1120 The ``type`` argument may be the second positional argument
1121 or specified by keyword.
1123 If the ``type`` is ``None`` or is omitted, it will first default to
1124 the special type :class:`.NullType`. If and when this
1125 :class:`_schema.Column` is made to refer to another column using
1126 :class:`_schema.ForeignKey` and/or
1127 :class:`_schema.ForeignKeyConstraint`, the type
1128 of the remote-referenced column will be copied to this column as
1129 well, at the moment that the foreign key is resolved against that
1130 remote :class:`_schema.Column` object.
1132 .. versionchanged:: 0.9.0
1133 Support for propagation of type to a :class:`_schema.Column`
1134 from its
1135 :class:`_schema.ForeignKey` object has been improved and should be
1136 more reliable and timely.
1138 :param \*args: Additional positional arguments include various
1139 :class:`.SchemaItem` derived constructs which will be applied
1140 as options to the column. These include instances of
1141 :class:`.Constraint`, :class:`_schema.ForeignKey`,
1142 :class:`.ColumnDefault`,
1143 :class:`.Sequence`, :class:`.Computed`. In some cases an
1144 equivalent keyword argument is available such as ``server_default``,
1145 ``default`` and ``unique``.
1147 :param autoincrement: Set up "auto increment" semantics for an integer
1148 primary key column. The default value is the string ``"auto"``
1149 which indicates that a single-column primary key that is of
1150 an INTEGER type with no stated client-side or python-side defaults
1151 should receive auto increment semantics automatically;
1152 all other varieties of primary key columns will not. This
1153 includes that :term:`DDL` such as PostgreSQL SERIAL or MySQL
1154 AUTO_INCREMENT will be emitted for this column during a table
1155 create, as well as that the column is assumed to generate new
1156 integer primary key values when an INSERT statement invokes which
1157 will be retrieved by the dialect.
1159 The flag may be set to ``True`` to indicate that a column which
1160 is part of a composite (e.g. multi-column) primary key should
1161 have autoincrement semantics, though note that only one column
1162 within a primary key may have this setting. It can also
1163 be set to ``True`` to indicate autoincrement semantics on a
1164 column that has a client-side or server-side default configured,
1165 however note that not all dialects can accommodate all styles
1166 of default as an "autoincrement". It can also be
1167 set to ``False`` on a single-column primary key that has a
1168 datatype of INTEGER in order to disable auto increment semantics
1169 for that column.
1171 .. versionchanged:: 1.1 The autoincrement flag now defaults to
1172 ``"auto"`` which indicates autoincrement semantics by default
1173 for single-column integer primary keys only; for composite
1174 (multi-column) primary keys, autoincrement is never implicitly
1175 enabled; as always, ``autoincrement=True`` will allow for
1176 at most one of those columns to be an "autoincrement" column.
1177 ``autoincrement=True`` may also be set on a
1178 :class:`_schema.Column`
1179 that has an explicit client-side or server-side default,
1180 subject to limitations of the backend database and dialect.
1183 The setting *only* has an effect for columns which are:
1185 * Integer derived (i.e. INT, SMALLINT, BIGINT).
1187 * Part of the primary key
1189 * Not referring to another column via :class:`_schema.ForeignKey`,
1190 unless
1191 the value is specified as ``'ignore_fk'``::
1193 # turn on autoincrement for this column despite
1194 # the ForeignKey()
1195 Column('id', ForeignKey('other.id'),
1196 primary_key=True, autoincrement='ignore_fk')
1198 It is typically not desirable to have "autoincrement" enabled on a
1199 column that refers to another via foreign key, as such a column is
1200 required to refer to a value that originates from elsewhere.
1202 The setting has these two effects on columns that meet the
1203 above criteria:
1205 * DDL issued for the column will include database-specific
1206 keywords intended to signify this column as an
1207 "autoincrement" column, such as AUTO INCREMENT on MySQL,
1208 SERIAL on PostgreSQL, and IDENTITY on MS-SQL. It does
1209 *not* issue AUTOINCREMENT for SQLite since this is a
1210 special SQLite flag that is not required for autoincrementing
1211 behavior.
1213 .. seealso::
1215 :ref:`sqlite_autoincrement`
1217 * The column will be considered to be available using an
1218 "autoincrement" method specific to the backend database, such
1219 as calling upon ``cursor.lastrowid``, using RETURNING in an
1220 INSERT statement to get at a sequence-generated value, or using
1221 special functions such as "SELECT scope_identity()".
1222 These methods are highly specific to the DBAPIs and databases in
1223 use and vary greatly, so care should be taken when associating
1224 ``autoincrement=True`` with a custom default generation function.
1227 :param default: A scalar, Python callable, or
1228 :class:`_expression.ColumnElement` expression representing the
1229 *default value* for this column, which will be invoked upon insert
1230 if this column is otherwise not specified in the VALUES clause of
1231 the insert. This is a shortcut to using :class:`.ColumnDefault` as
1232 a positional argument; see that class for full detail on the
1233 structure of the argument.
1235 Contrast this argument to
1236 :paramref:`_schema.Column.server_default`
1237 which creates a default generator on the database side.
1239 .. seealso::
1241 :ref:`metadata_defaults_toplevel`
1243 :param doc: optional String that can be used by the ORM or similar
1244 to document attributes on the Python side. This attribute does
1245 **not** render SQL comments; use the
1246 :paramref:`_schema.Column.comment`
1247 parameter for this purpose.
1249 :param key: An optional string identifier which will identify this
1250 ``Column`` object on the :class:`_schema.Table`.
1251 When a key is provided,
1252 this is the only identifier referencing the ``Column`` within the
1253 application, including ORM attribute mapping; the ``name`` field
1254 is used only when rendering SQL.
1256 :param index: When ``True``, indicates that the column is indexed.
1257 This is a shortcut for using a :class:`.Index` construct on the
1258 table. To specify indexes with explicit names or indexes that
1259 contain multiple columns, use the :class:`.Index` construct
1260 instead.
1262 :param info: Optional data dictionary which will be populated into the
1263 :attr:`.SchemaItem.info` attribute of this object.
1265 :param nullable: When set to ``False``, will cause the "NOT NULL"
1266 phrase to be added when generating DDL for the column. When
1267 ``True``, will normally generate nothing (in SQL this defaults to
1268 "NULL"), except in some very specific backend-specific edge cases
1269 where "NULL" may render explicitly. Defaults to ``True`` unless
1270 :paramref:`_schema.Column.primary_key` is also ``True``,
1271 in which case it
1272 defaults to ``False``. This parameter is only used when issuing
1273 CREATE TABLE statements.
1275 :param onupdate: A scalar, Python callable, or
1276 :class:`~sqlalchemy.sql.expression.ClauseElement` representing a
1277 default value to be applied to the column within UPDATE
1278 statements, which will be invoked upon update if this column is not
1279 present in the SET clause of the update. This is a shortcut to
1280 using :class:`.ColumnDefault` as a positional argument with
1281 ``for_update=True``.
1283 .. seealso::
1285 :ref:`metadata_defaults` - complete discussion of onupdate
1287 :param primary_key: If ``True``, marks this column as a primary key
1288 column. Multiple columns can have this flag set to specify
1289 composite primary keys. As an alternative, the primary key of a
1290 :class:`_schema.Table` can be specified via an explicit
1291 :class:`.PrimaryKeyConstraint` object.
1293 :param server_default: A :class:`.FetchedValue` instance, str, Unicode
1294 or :func:`~sqlalchemy.sql.expression.text` construct representing
1295 the DDL DEFAULT value for the column.
1297 String types will be emitted as-is, surrounded by single quotes::
1299 Column('x', Text, server_default="val")
1301 x TEXT DEFAULT 'val'
1303 A :func:`~sqlalchemy.sql.expression.text` expression will be
1304 rendered as-is, without quotes::
1306 Column('y', DateTime, server_default=text('NOW()'))
1308 y DATETIME DEFAULT NOW()
1310 Strings and text() will be converted into a
1311 :class:`.DefaultClause` object upon initialization.
1313 Use :class:`.FetchedValue` to indicate that an already-existing
1314 column will generate a default value on the database side which
1315 will be available to SQLAlchemy for post-fetch after inserts. This
1316 construct does not specify any DDL and the implementation is left
1317 to the database, such as via a trigger.
1319 .. seealso::
1321 :ref:`server_defaults` - complete discussion of server side
1322 defaults
1324 :param server_onupdate: A :class:`.FetchedValue` instance
1325 representing a database-side default generation function,
1326 such as a trigger. This
1327 indicates to SQLAlchemy that a newly generated value will be
1328 available after updates. This construct does not actually
1329 implement any kind of generation function within the database,
1330 which instead must be specified separately.
1332 .. seealso::
1334 :ref:`triggered_columns`
1336 :param quote: Force quoting of this column's name on or off,
1337 corresponding to ``True`` or ``False``. When left at its default
1338 of ``None``, the column identifier will be quoted according to
1339 whether the name is case sensitive (identifiers with at least one
1340 upper case character are treated as case sensitive), or if it's a
1341 reserved word. This flag is only needed to force quoting of a
1342 reserved word which is not known by the SQLAlchemy dialect.
1344 :param unique: When ``True``, indicates that this column contains a
1345 unique constraint, or if ``index`` is ``True`` as well, indicates
1346 that the :class:`.Index` should be created with the unique flag.
1347 To specify multiple columns in the constraint/index or to specify
1348 an explicit name, use the :class:`.UniqueConstraint` or
1349 :class:`.Index` constructs explicitly.
1351 :param system: When ``True``, indicates this is a "system" column,
1352 that is a column which is automatically made available by the
1353 database, and should not be included in the columns list for a
1354 ``CREATE TABLE`` statement.
1356 For more elaborate scenarios where columns should be
1357 conditionally rendered differently on different backends,
1358 consider custom compilation rules for :class:`.CreateColumn`.
1360 :param comment: Optional string that will render an SQL comment on
1361 table creation.
1363 .. versionadded:: 1.2 Added the
1364 :paramref:`_schema.Column.comment`
1365 parameter to :class:`_schema.Column`.
1368 """
1370 name = kwargs.pop("name", None)
1371 type_ = kwargs.pop("type_", None)
1372 args = list(args)
1373 if args:
1374 if isinstance(args[0], util.string_types):
1375 if name is not None:
1376 raise exc.ArgumentError(
1377 "May not pass name positionally and as a keyword."
1378 )
1379 name = args.pop(0)
1380 if args:
1381 coltype = args[0]
1383 if hasattr(coltype, "_sqla_type"):
1384 if type_ is not None:
1385 raise exc.ArgumentError(
1386 "May not pass type_ positionally and as a keyword."
1387 )
1388 type_ = args.pop(0)
1390 if name is not None:
1391 name = quoted_name(name, kwargs.pop("quote", None))
1392 elif "quote" in kwargs:
1393 raise exc.ArgumentError(
1394 "Explicit 'name' is required when " "sending 'quote' argument"
1395 )
1397 super(Column, self).__init__(name, type_)
1398 self.key = kwargs.pop("key", name)
1399 self.primary_key = kwargs.pop("primary_key", False)
1400 self.nullable = kwargs.pop("nullable", not self.primary_key)
1401 self.default = kwargs.pop("default", None)
1402 self.server_default = kwargs.pop("server_default", None)
1403 self.server_onupdate = kwargs.pop("server_onupdate", None)
1405 # these default to None because .index and .unique is *not*
1406 # an informational flag about Column - there can still be an
1407 # Index or UniqueConstraint referring to this Column.
1408 self.index = kwargs.pop("index", None)
1409 self.unique = kwargs.pop("unique", None)
1411 self.system = kwargs.pop("system", False)
1412 self.doc = kwargs.pop("doc", None)
1413 self.onupdate = kwargs.pop("onupdate", None)
1414 self.autoincrement = kwargs.pop("autoincrement", "auto")
1415 self.constraints = set()
1416 self.foreign_keys = set()
1417 self.comment = kwargs.pop("comment", None)
1418 self.computed = None
1420 # check if this Column is proxying another column
1421 if "_proxies" in kwargs:
1422 self._proxies = kwargs.pop("_proxies")
1423 # otherwise, add DDL-related events
1424 elif isinstance(self.type, SchemaEventTarget):
1425 self.type._set_parent_with_dispatch(self)
1427 if self.default is not None:
1428 if isinstance(self.default, (ColumnDefault, Sequence)):
1429 args.append(self.default)
1430 else:
1431 if getattr(self.type, "_warn_on_bytestring", False):
1432 if isinstance(self.default, util.binary_type):
1433 util.warn(
1434 "Unicode column '%s' has non-unicode "
1435 "default value %r specified."
1436 % (self.key, self.default)
1437 )
1438 args.append(ColumnDefault(self.default))
1440 if self.server_default is not None:
1441 if isinstance(self.server_default, FetchedValue):
1442 args.append(self.server_default._as_for_update(False))
1443 else:
1444 args.append(DefaultClause(self.server_default))
1446 if self.onupdate is not None:
1447 if isinstance(self.onupdate, (ColumnDefault, Sequence)):
1448 args.append(self.onupdate)
1449 else:
1450 args.append(ColumnDefault(self.onupdate, for_update=True))
1452 if self.server_onupdate is not None:
1453 if isinstance(self.server_onupdate, FetchedValue):
1454 args.append(self.server_onupdate._as_for_update(True))
1455 else:
1456 args.append(
1457 DefaultClause(self.server_onupdate, for_update=True)
1458 )
1459 self._init_items(*args)
1461 util.set_creation_order(self)
1463 if "info" in kwargs:
1464 self.info = kwargs.pop("info")
1466 self._extra_kwargs(**kwargs)
1468 def _extra_kwargs(self, **kwargs):
1469 self._validate_dialect_kwargs(kwargs)
1471 # @property
1472 # def quote(self):
1473 # return getattr(self.name, "quote", None)
1475 def __str__(self):
1476 if self.name is None:
1477 return "(no name)"
1478 elif self.table is not None:
1479 if self.table.named_with_column:
1480 return self.table.description + "." + self.description
1481 else:
1482 return self.description
1483 else:
1484 return self.description
1486 def references(self, column):
1487 """Return True if this Column references the given column via foreign
1488 key."""
1490 for fk in self.foreign_keys:
1491 if fk.column.proxy_set.intersection(column.proxy_set):
1492 return True
1493 else:
1494 return False
1496 def append_foreign_key(self, fk):
1497 fk._set_parent_with_dispatch(self)
1499 def __repr__(self):
1500 kwarg = []
1501 if self.key != self.name:
1502 kwarg.append("key")
1503 if self.primary_key:
1504 kwarg.append("primary_key")
1505 if not self.nullable:
1506 kwarg.append("nullable")
1507 if self.onupdate:
1508 kwarg.append("onupdate")
1509 if self.default:
1510 kwarg.append("default")
1511 if self.server_default:
1512 kwarg.append("server_default")
1513 if self.comment:
1514 kwarg.append("comment")
1515 return "Column(%s)" % ", ".join(
1516 [repr(self.name)]
1517 + [repr(self.type)]
1518 + [repr(x) for x in self.foreign_keys if x is not None]
1519 + [repr(x) for x in self.constraints]
1520 + [
1521 (
1522 self.table is not None
1523 and "table=<%s>" % self.table.description
1524 or "table=None"
1525 )
1526 ]
1527 + ["%s=%s" % (k, repr(getattr(self, k))) for k in kwarg]
1528 )
1530 def _set_parent(self, table):
1531 if not self.name:
1532 raise exc.ArgumentError(
1533 "Column must be constructed with a non-blank name or "
1534 "assign a non-blank .name before adding to a Table."
1535 )
1536 if self.key is None:
1537 self.key = self.name
1539 existing = getattr(self, "table", None)
1540 if existing is not None and existing is not table:
1541 raise exc.ArgumentError(
1542 "Column object '%s' already assigned to Table '%s'"
1543 % (self.key, existing.description)
1544 )
1546 if self.key in table._columns:
1547 col = table._columns.get(self.key)
1548 if col is not self:
1549 for fk in col.foreign_keys:
1550 table.foreign_keys.remove(fk)
1551 if fk.constraint in table.constraints:
1552 # this might have been removed
1553 # already, if it's a composite constraint
1554 # and more than one col being replaced
1555 table.constraints.remove(fk.constraint)
1557 table._columns.replace(self)
1559 if self.primary_key:
1560 table.primary_key._replace(self)
1561 elif self.key in table.primary_key:
1562 raise exc.ArgumentError(
1563 "Trying to redefine primary-key column '%s' as a "
1564 "non-primary-key column on table '%s'"
1565 % (self.key, table.fullname)
1566 )
1568 self.table = table
1570 if self.index:
1571 if isinstance(self.index, util.string_types):
1572 raise exc.ArgumentError(
1573 "The 'index' keyword argument on Column is boolean only. "
1574 "To create indexes with a specific name, create an "
1575 "explicit Index object external to the Table."
1576 )
1577 table.append_constraint(
1578 Index(
1579 None, self.key, unique=bool(self.unique), _column_flag=True
1580 )
1581 )
1583 elif self.unique:
1584 if isinstance(self.unique, util.string_types):
1585 raise exc.ArgumentError(
1586 "The 'unique' keyword argument on Column is boolean "
1587 "only. To create unique constraints or indexes with a "
1588 "specific name, append an explicit UniqueConstraint to "
1589 "the Table's list of elements, or create an explicit "
1590 "Index object external to the Table."
1591 )
1592 table.append_constraint(
1593 UniqueConstraint(self.key, _column_flag=True)
1594 )
1596 self._setup_on_memoized_fks(lambda fk: fk._set_remote_table(table))
1598 def _setup_on_memoized_fks(self, fn):
1599 fk_keys = [
1600 ((self.table.key, self.key), False),
1601 ((self.table.key, self.name), True),
1602 ]
1603 for fk_key, link_to_name in fk_keys:
1604 if fk_key in self.table.metadata._fk_memos:
1605 for fk in self.table.metadata._fk_memos[fk_key]:
1606 if fk.link_to_name is link_to_name:
1607 fn(fk)
1609 def _on_table_attach(self, fn):
1610 if self.table is not None:
1611 fn(self, self.table)
1612 else:
1613 event.listen(self, "after_parent_attach", fn)
1615 def copy(self, **kw):
1616 """Create a copy of this ``Column``, uninitialized.
1618 This is used in ``Table.tometadata``.
1620 """
1622 # Constraint objects plus non-constraint-bound ForeignKey objects
1623 args = [
1624 c.copy(**kw) for c in self.constraints if not c._type_bound
1625 ] + [c.copy(**kw) for c in self.foreign_keys if not c.constraint]
1627 # ticket #5276
1628 column_kwargs = {}
1629 for dialect_name in self.dialect_options:
1630 dialect_options = self.dialect_options[dialect_name]._non_defaults
1631 for (
1632 dialect_option_key,
1633 dialect_option_value,
1634 ) in dialect_options.items():
1635 column_kwargs[
1636 dialect_name + "_" + dialect_option_key
1637 ] = dialect_option_value
1639 server_default = self.server_default
1640 server_onupdate = self.server_onupdate
1641 if isinstance(server_default, Computed):
1642 server_default = server_onupdate = None
1643 args.append(self.server_default.copy(**kw))
1645 type_ = self.type
1646 if isinstance(type_, SchemaEventTarget):
1647 type_ = type_.copy(**kw)
1649 c = self._constructor(
1650 name=self.name,
1651 type_=type_,
1652 key=self.key,
1653 primary_key=self.primary_key,
1654 nullable=self.nullable,
1655 unique=self.unique,
1656 system=self.system,
1657 # quote=self.quote, # disabled 2013-08-27 (commit 031ef080)
1658 index=self.index,
1659 autoincrement=self.autoincrement,
1660 default=self.default,
1661 server_default=server_default,
1662 onupdate=self.onupdate,
1663 server_onupdate=server_onupdate,
1664 doc=self.doc,
1665 comment=self.comment,
1666 *args,
1667 **column_kwargs
1668 )
1669 return self._schema_item_copy(c)
1671 def _make_proxy(
1672 self, selectable, name=None, key=None, name_is_truncatable=False, **kw
1673 ):
1674 """Create a *proxy* for this column.
1676 This is a copy of this ``Column`` referenced by a different parent
1677 (such as an alias or select statement). The column should
1678 be used only in select scenarios, as its full DDL/default
1679 information is not transferred.
1681 """
1682 fk = [
1683 ForeignKey(f.column, _constraint=f.constraint)
1684 for f in self.foreign_keys
1685 ]
1686 if name is None and self.name is None:
1687 raise exc.InvalidRequestError(
1688 "Cannot initialize a sub-selectable"
1689 " with this Column object until its 'name' has "
1690 "been assigned."
1691 )
1692 try:
1693 c = self._constructor(
1694 _as_truncated(name or self.name)
1695 if name_is_truncatable
1696 else (name or self.name),
1697 self.type,
1698 key=key if key else name if name else self.key,
1699 primary_key=self.primary_key,
1700 nullable=self.nullable,
1701 _proxies=[self],
1702 *fk
1703 )
1704 except TypeError as err:
1705 util.raise_(
1706 TypeError(
1707 "Could not create a copy of this %r object. "
1708 "Ensure the class includes a _constructor() "
1709 "attribute or method which accepts the "
1710 "standard Column constructor arguments, or "
1711 "references the Column class itself." % self.__class__
1712 ),
1713 from_=err,
1714 )
1716 c.table = selectable
1717 selectable._columns.add(c)
1718 if selectable._is_clone_of is not None:
1719 c._is_clone_of = selectable._is_clone_of.columns.get(c.key)
1720 if self.primary_key:
1721 selectable.primary_key.add(c)
1722 c.dispatch.after_parent_attach(c, selectable)
1723 return c
1725 def get_children(self, schema_visitor=False, **kwargs):
1726 if schema_visitor:
1727 return (
1728 [x for x in (self.default, self.onupdate) if x is not None]
1729 + list(self.foreign_keys)
1730 + list(self.constraints)
1731 )
1732 else:
1733 return ColumnClause.get_children(self, **kwargs)
1736class ForeignKey(DialectKWArgs, SchemaItem):
1737 """Defines a dependency between two columns.
1739 ``ForeignKey`` is specified as an argument to a :class:`_schema.Column`
1740 object,
1741 e.g.::
1743 t = Table("remote_table", metadata,
1744 Column("remote_id", ForeignKey("main_table.id"))
1745 )
1747 Note that ``ForeignKey`` is only a marker object that defines
1748 a dependency between two columns. The actual constraint
1749 is in all cases represented by the :class:`_schema.ForeignKeyConstraint`
1750 object. This object will be generated automatically when
1751 a ``ForeignKey`` is associated with a :class:`_schema.Column` which
1752 in turn is associated with a :class:`_schema.Table`. Conversely,
1753 when :class:`_schema.ForeignKeyConstraint` is applied to a
1754 :class:`_schema.Table`,
1755 ``ForeignKey`` markers are automatically generated to be
1756 present on each associated :class:`_schema.Column`, which are also
1757 associated with the constraint object.
1759 Note that you cannot define a "composite" foreign key constraint,
1760 that is a constraint between a grouping of multiple parent/child
1761 columns, using ``ForeignKey`` objects. To define this grouping,
1762 the :class:`_schema.ForeignKeyConstraint` object must be used, and applied
1763 to the :class:`_schema.Table`. The associated ``ForeignKey`` objects
1764 are created automatically.
1766 The ``ForeignKey`` objects associated with an individual
1767 :class:`_schema.Column`
1768 object are available in the `foreign_keys` collection
1769 of that column.
1771 Further examples of foreign key configuration are in
1772 :ref:`metadata_foreignkeys`.
1774 """
1776 __visit_name__ = "foreign_key"
1778 def __init__(
1779 self,
1780 column,
1781 _constraint=None,
1782 use_alter=False,
1783 name=None,
1784 onupdate=None,
1785 ondelete=None,
1786 deferrable=None,
1787 initially=None,
1788 link_to_name=False,
1789 match=None,
1790 info=None,
1791 **dialect_kw
1792 ):
1793 r"""
1794 Construct a column-level FOREIGN KEY.
1796 The :class:`_schema.ForeignKey` object when constructed generates a
1797 :class:`_schema.ForeignKeyConstraint`
1798 which is associated with the parent
1799 :class:`_schema.Table` object's collection of constraints.
1801 :param column: A single target column for the key relationship. A
1802 :class:`_schema.Column` object or a column name as a string:
1803 ``tablename.columnkey`` or ``schema.tablename.columnkey``.
1804 ``columnkey`` is the ``key`` which has been assigned to the column
1805 (defaults to the column name itself), unless ``link_to_name`` is
1806 ``True`` in which case the rendered name of the column is used.
1808 :param name: Optional string. An in-database name for the key if
1809 `constraint` is not provided.
1811 :param onupdate: Optional string. If set, emit ON UPDATE <value> when
1812 issuing DDL for this constraint. Typical values include CASCADE,
1813 DELETE and RESTRICT.
1815 :param ondelete: Optional string. If set, emit ON DELETE <value> when
1816 issuing DDL for this constraint. Typical values include CASCADE,
1817 DELETE and RESTRICT.
1819 :param deferrable: Optional bool. If set, emit DEFERRABLE or NOT
1820 DEFERRABLE when issuing DDL for this constraint.
1822 :param initially: Optional string. If set, emit INITIALLY <value> when
1823 issuing DDL for this constraint.
1825 :param link_to_name: if True, the string name given in ``column`` is
1826 the rendered name of the referenced column, not its locally
1827 assigned ``key``.
1829 :param use_alter: passed to the underlying
1830 :class:`_schema.ForeignKeyConstraint`
1831 to indicate the constraint should
1832 be generated/dropped externally from the CREATE TABLE/ DROP TABLE
1833 statement. See :paramref:`_schema.ForeignKeyConstraint.use_alter`
1834 for further description.
1836 .. seealso::
1838 :paramref:`_schema.ForeignKeyConstraint.use_alter`
1840 :ref:`use_alter`
1842 :param match: Optional string. If set, emit MATCH <value> when issuing
1843 DDL for this constraint. Typical values include SIMPLE, PARTIAL
1844 and FULL.
1846 :param info: Optional data dictionary which will be populated into the
1847 :attr:`.SchemaItem.info` attribute of this object.
1849 .. versionadded:: 1.0.0
1851 :param \**dialect_kw: Additional keyword arguments are dialect
1852 specific, and passed in the form ``<dialectname>_<argname>``. The
1853 arguments are ultimately handled by a corresponding
1854 :class:`_schema.ForeignKeyConstraint`.
1855 See the documentation regarding
1856 an individual dialect at :ref:`dialect_toplevel` for detail on
1857 documented arguments.
1859 .. versionadded:: 0.9.2
1861 """
1863 self._colspec = column
1864 if isinstance(self._colspec, util.string_types):
1865 self._table_column = None
1866 else:
1867 if hasattr(self._colspec, "__clause_element__"):
1868 self._table_column = self._colspec.__clause_element__()
1869 else:
1870 self._table_column = self._colspec
1872 if not isinstance(self._table_column, ColumnClause):
1873 raise exc.ArgumentError(
1874 "String, Column, or Column-bound argument "
1875 "expected, got %r" % self._table_column
1876 )
1877 elif not isinstance(
1878 self._table_column.table, (util.NoneType, TableClause)
1879 ):
1880 raise exc.ArgumentError(
1881 "ForeignKey received Column not bound "
1882 "to a Table, got: %r" % self._table_column.table
1883 )
1885 # the linked ForeignKeyConstraint.
1886 # ForeignKey will create this when parent Column
1887 # is attached to a Table, *or* ForeignKeyConstraint
1888 # object passes itself in when creating ForeignKey
1889 # markers.
1890 self.constraint = _constraint
1891 self.parent = None
1892 self.use_alter = use_alter
1893 self.name = name
1894 self.onupdate = onupdate
1895 self.ondelete = ondelete
1896 self.deferrable = deferrable
1897 self.initially = initially
1898 self.link_to_name = link_to_name
1899 self.match = match
1900 if info:
1901 self.info = info
1902 self._unvalidated_dialect_kw = dialect_kw
1904 def __repr__(self):
1905 return "ForeignKey(%r)" % self._get_colspec()
1907 def copy(self, schema=None):
1908 """Produce a copy of this :class:`_schema.ForeignKey` object.
1910 The new :class:`_schema.ForeignKey` will not be bound
1911 to any :class:`_schema.Column`.
1913 This method is usually used by the internal
1914 copy procedures of :class:`_schema.Column`, :class:`_schema.Table`,
1915 and :class:`_schema.MetaData`.
1917 :param schema: The returned :class:`_schema.ForeignKey` will
1918 reference the original table and column name, qualified
1919 by the given string schema name.
1921 """
1923 fk = ForeignKey(
1924 self._get_colspec(schema=schema),
1925 use_alter=self.use_alter,
1926 name=self.name,
1927 onupdate=self.onupdate,
1928 ondelete=self.ondelete,
1929 deferrable=self.deferrable,
1930 initially=self.initially,
1931 link_to_name=self.link_to_name,
1932 match=self.match,
1933 **self._unvalidated_dialect_kw
1934 )
1935 return self._schema_item_copy(fk)
1937 def _get_colspec(self, schema=None, table_name=None):
1938 """Return a string based 'column specification' for this
1939 :class:`_schema.ForeignKey`.
1941 This is usually the equivalent of the string-based "tablename.colname"
1942 argument first passed to the object's constructor.
1944 """
1945 if schema:
1946 _schema, tname, colname = self._column_tokens
1947 if table_name is not None:
1948 tname = table_name
1949 return "%s.%s.%s" % (schema, tname, colname)
1950 elif table_name:
1951 schema, tname, colname = self._column_tokens
1952 if schema:
1953 return "%s.%s.%s" % (schema, table_name, colname)
1954 else:
1955 return "%s.%s" % (table_name, colname)
1956 elif self._table_column is not None:
1957 return "%s.%s" % (
1958 self._table_column.table.fullname,
1959 self._table_column.key,
1960 )
1961 else:
1962 return self._colspec
1964 @property
1965 def _referred_schema(self):
1966 return self._column_tokens[0]
1968 def _table_key(self):
1969 if self._table_column is not None:
1970 if self._table_column.table is None:
1971 return None
1972 else:
1973 return self._table_column.table.key
1974 else:
1975 schema, tname, colname = self._column_tokens
1976 return _get_table_key(tname, schema)
1978 target_fullname = property(_get_colspec)
1980 def references(self, table):
1981 """Return True if the given :class:`_schema.Table`
1982 is referenced by this
1983 :class:`_schema.ForeignKey`."""
1985 return table.corresponding_column(self.column) is not None
1987 def get_referent(self, table):
1988 """Return the :class:`_schema.Column` in the given
1989 :class:`_schema.Table`
1990 referenced by this :class:`_schema.ForeignKey`.
1992 Returns None if this :class:`_schema.ForeignKey`
1993 does not reference the given
1994 :class:`_schema.Table`.
1996 """
1998 return table.corresponding_column(self.column)
2000 @util.memoized_property
2001 def _column_tokens(self):
2002 """parse a string-based _colspec into its component parts."""
2004 m = self._get_colspec().split(".")
2005 if m is None:
2006 raise exc.ArgumentError(
2007 "Invalid foreign key column specification: %s" % self._colspec
2008 )
2009 if len(m) == 1:
2010 tname = m.pop()
2011 colname = None
2012 else:
2013 colname = m.pop()
2014 tname = m.pop()
2016 # A FK between column 'bar' and table 'foo' can be
2017 # specified as 'foo', 'foo.bar', 'dbo.foo.bar',
2018 # 'otherdb.dbo.foo.bar'. Once we have the column name and
2019 # the table name, treat everything else as the schema
2020 # name. Some databases (e.g. Sybase) support
2021 # inter-database foreign keys. See tickets#1341 and --
2022 # indirectly related -- Ticket #594. This assumes that '.'
2023 # will never appear *within* any component of the FK.
2025 if len(m) > 0:
2026 schema = ".".join(m)
2027 else:
2028 schema = None
2029 return schema, tname, colname
2031 def _resolve_col_tokens(self):
2032 if self.parent is None:
2033 raise exc.InvalidRequestError(
2034 "this ForeignKey object does not yet have a "
2035 "parent Column associated with it."
2036 )
2038 elif self.parent.table is None:
2039 raise exc.InvalidRequestError(
2040 "this ForeignKey's parent column is not yet associated "
2041 "with a Table."
2042 )
2044 parenttable = self.parent.table
2046 # assertion
2047 # basically Column._make_proxy() sends the actual
2048 # target Column to the ForeignKey object, so the
2049 # string resolution here is never called.
2050 for c in self.parent.base_columns:
2051 if isinstance(c, Column):
2052 assert c.table is parenttable
2053 break
2054 else:
2055 assert False
2056 ######################
2058 schema, tname, colname = self._column_tokens
2060 if schema is None and parenttable.metadata.schema is not None:
2061 schema = parenttable.metadata.schema
2063 tablekey = _get_table_key(tname, schema)
2064 return parenttable, tablekey, colname
2066 def _link_to_col_by_colstring(self, parenttable, table, colname):
2067 if not hasattr(self.constraint, "_referred_table"):
2068 self.constraint._referred_table = table
2069 else:
2070 assert self.constraint._referred_table is table
2072 _column = None
2073 if colname is None:
2074 # colname is None in the case that ForeignKey argument
2075 # was specified as table name only, in which case we
2076 # match the column name to the same column on the
2077 # parent.
2078 key = self.parent
2079 _column = table.c.get(self.parent.key, None)
2080 elif self.link_to_name:
2081 key = colname
2082 for c in table.c:
2083 if c.name == colname:
2084 _column = c
2085 else:
2086 key = colname
2087 _column = table.c.get(colname, None)
2089 if _column is None:
2090 raise exc.NoReferencedColumnError(
2091 "Could not initialize target column "
2092 "for ForeignKey '%s' on table '%s': "
2093 "table '%s' has no column named '%s'"
2094 % (self._colspec, parenttable.name, table.name, key),
2095 table.name,
2096 key,
2097 )
2099 self._set_target_column(_column)
2101 def _set_target_column(self, column):
2102 # propagate TypeEngine to parent if it didn't have one
2103 if self.parent.type._isnull:
2104 self.parent.type = column.type
2106 # super-edgy case, if other FKs point to our column,
2107 # they'd get the type propagated out also.
2108 if isinstance(self.parent.table, Table):
2110 def set_type(fk):
2111 if fk.parent.type._isnull:
2112 fk.parent.type = column.type
2114 self.parent._setup_on_memoized_fks(set_type)
2116 self.column = column
2118 @util.memoized_property
2119 def column(self):
2120 """Return the target :class:`_schema.Column` referenced by this
2121 :class:`_schema.ForeignKey`.
2123 If no target column has been established, an exception
2124 is raised.
2126 .. versionchanged:: 0.9.0
2127 Foreign key target column resolution now occurs as soon as both
2128 the ForeignKey object and the remote Column to which it refers
2129 are both associated with the same MetaData object.
2131 """
2133 if isinstance(self._colspec, util.string_types):
2135 parenttable, tablekey, colname = self._resolve_col_tokens()
2137 if tablekey not in parenttable.metadata:
2138 raise exc.NoReferencedTableError(
2139 "Foreign key associated with column '%s' could not find "
2140 "table '%s' with which to generate a "
2141 "foreign key to target column '%s'"
2142 % (self.parent, tablekey, colname),
2143 tablekey,
2144 )
2145 elif parenttable.key not in parenttable.metadata:
2146 raise exc.InvalidRequestError(
2147 "Table %s is no longer associated with its "
2148 "parent MetaData" % parenttable
2149 )
2150 else:
2151 raise exc.NoReferencedColumnError(
2152 "Could not initialize target column for "
2153 "ForeignKey '%s' on table '%s': "
2154 "table '%s' has no column named '%s'"
2155 % (self._colspec, parenttable.name, tablekey, colname),
2156 tablekey,
2157 colname,
2158 )
2159 elif hasattr(self._colspec, "__clause_element__"):
2160 _column = self._colspec.__clause_element__()
2161 return _column
2162 else:
2163 _column = self._colspec
2164 return _column
2166 def _set_parent(self, column):
2167 if self.parent is not None and self.parent is not column:
2168 raise exc.InvalidRequestError(
2169 "This ForeignKey already has a parent !"
2170 )
2171 self.parent = column
2172 self.parent.foreign_keys.add(self)
2173 self.parent._on_table_attach(self._set_table)
2175 def _set_remote_table(self, table):
2176 parenttable, tablekey, colname = self._resolve_col_tokens()
2177 self._link_to_col_by_colstring(parenttable, table, colname)
2178 self.constraint._validate_dest_table(table)
2180 def _remove_from_metadata(self, metadata):
2181 parenttable, table_key, colname = self._resolve_col_tokens()
2182 fk_key = (table_key, colname)
2184 if self in metadata._fk_memos[fk_key]:
2185 # TODO: no test coverage for self not in memos
2186 metadata._fk_memos[fk_key].remove(self)
2188 def _set_table(self, column, table):
2189 # standalone ForeignKey - create ForeignKeyConstraint
2190 # on the hosting Table when attached to the Table.
2191 if self.constraint is None and isinstance(table, Table):
2192 self.constraint = ForeignKeyConstraint(
2193 [],
2194 [],
2195 use_alter=self.use_alter,
2196 name=self.name,
2197 onupdate=self.onupdate,
2198 ondelete=self.ondelete,
2199 deferrable=self.deferrable,
2200 initially=self.initially,
2201 match=self.match,
2202 **self._unvalidated_dialect_kw
2203 )
2204 self.constraint._append_element(column, self)
2205 self.constraint._set_parent_with_dispatch(table)
2206 table.foreign_keys.add(self)
2208 # set up remote ".column" attribute, or a note to pick it
2209 # up when the other Table/Column shows up
2210 if isinstance(self._colspec, util.string_types):
2211 parenttable, table_key, colname = self._resolve_col_tokens()
2212 fk_key = (table_key, colname)
2213 if table_key in parenttable.metadata.tables:
2214 table = parenttable.metadata.tables[table_key]
2215 try:
2216 self._link_to_col_by_colstring(parenttable, table, colname)
2217 except exc.NoReferencedColumnError:
2218 # this is OK, we'll try later
2219 pass
2220 parenttable.metadata._fk_memos[fk_key].append(self)
2221 elif hasattr(self._colspec, "__clause_element__"):
2222 _column = self._colspec.__clause_element__()
2223 self._set_target_column(_column)
2224 else:
2225 _column = self._colspec
2226 self._set_target_column(_column)
2229class _NotAColumnExpr(object):
2230 def _not_a_column_expr(self):
2231 raise exc.InvalidRequestError(
2232 "This %s cannot be used directly "
2233 "as a column expression." % self.__class__.__name__
2234 )
2236 __clause_element__ = self_group = lambda self: self._not_a_column_expr()
2237 _from_objects = property(lambda self: self._not_a_column_expr())
2240class DefaultGenerator(_NotAColumnExpr, SchemaItem):
2241 """Base class for column *default* values."""
2243 __visit_name__ = "default_generator"
2245 is_sequence = False
2246 is_server_default = False
2247 column = None
2249 def __init__(self, for_update=False):
2250 self.for_update = for_update
2252 def _set_parent(self, column):
2253 self.column = column
2254 if self.for_update:
2255 self.column.onupdate = self
2256 else:
2257 self.column.default = self
2259 def execute(self, bind=None, **kwargs):
2260 if bind is None:
2261 bind = _bind_or_error(self)
2262 return bind.execute(self, **kwargs)
2264 def _execute_on_connection(self, connection, multiparams, params):
2265 return connection._execute_default(self, multiparams, params)
2267 @property
2268 def bind(self):
2269 """Return the connectable associated with this default."""
2270 if getattr(self, "column", None) is not None:
2271 return self.column.table.bind
2272 else:
2273 return None
2276class ColumnDefault(DefaultGenerator):
2277 """A plain default value on a column.
2279 This could correspond to a constant, a callable function,
2280 or a SQL clause.
2282 :class:`.ColumnDefault` is generated automatically
2283 whenever the ``default``, ``onupdate`` arguments of
2284 :class:`_schema.Column` are used. A :class:`.ColumnDefault`
2285 can be passed positionally as well.
2287 For example, the following::
2289 Column('foo', Integer, default=50)
2291 Is equivalent to::
2293 Column('foo', Integer, ColumnDefault(50))
2296 """
2298 def __init__(self, arg, **kwargs):
2299 """"Construct a new :class:`.ColumnDefault`.
2302 :param arg: argument representing the default value.
2303 May be one of the following:
2305 * a plain non-callable Python value, such as a
2306 string, integer, boolean, or other simple type.
2307 The default value will be used as is each time.
2308 * a SQL expression, that is one which derives from
2309 :class:`_expression.ColumnElement`. The SQL expression will
2310 be rendered into the INSERT or UPDATE statement,
2311 or in the case of a primary key column when
2312 RETURNING is not used may be
2313 pre-executed before an INSERT within a SELECT.
2314 * A Python callable. The function will be invoked for each
2315 new row subject to an INSERT or UPDATE.
2316 The callable must accept exactly
2317 zero or one positional arguments. The one-argument form
2318 will receive an instance of the :class:`.ExecutionContext`,
2319 which provides contextual information as to the current
2320 :class:`_engine.Connection` in use as well as the current
2321 statement and parameters.
2323 """
2324 super(ColumnDefault, self).__init__(**kwargs)
2325 if isinstance(arg, FetchedValue):
2326 raise exc.ArgumentError(
2327 "ColumnDefault may not be a server-side default type."
2328 )
2329 if util.callable(arg):
2330 arg = self._maybe_wrap_callable(arg)
2331 self.arg = arg
2333 @util.memoized_property
2334 def is_callable(self):
2335 return util.callable(self.arg)
2337 @util.memoized_property
2338 def is_clause_element(self):
2339 return isinstance(self.arg, ClauseElement)
2341 @util.memoized_property
2342 def is_scalar(self):
2343 return (
2344 not self.is_callable
2345 and not self.is_clause_element
2346 and not self.is_sequence
2347 )
2349 @util.memoized_property
2350 @util.dependencies("sqlalchemy.sql.sqltypes")
2351 def _arg_is_typed(self, sqltypes):
2352 if self.is_clause_element:
2353 return not isinstance(self.arg.type, sqltypes.NullType)
2354 else:
2355 return False
2357 def _maybe_wrap_callable(self, fn):
2358 """Wrap callables that don't accept a context.
2360 This is to allow easy compatibility with default callables
2361 that aren't specific to accepting of a context.
2363 """
2364 try:
2365 argspec = util.get_callable_argspec(fn, no_self=True)
2366 except TypeError:
2367 return util.wrap_callable(lambda ctx: fn(), fn)
2369 defaulted = argspec[3] is not None and len(argspec[3]) or 0
2370 positionals = len(argspec[0]) - defaulted
2372 if positionals == 0:
2373 return util.wrap_callable(lambda ctx: fn(), fn)
2375 elif positionals == 1:
2376 return fn
2377 else:
2378 raise exc.ArgumentError(
2379 "ColumnDefault Python function takes zero or one "
2380 "positional arguments"
2381 )
2383 def _visit_name(self):
2384 if self.for_update:
2385 return "column_onupdate"
2386 else:
2387 return "column_default"
2389 __visit_name__ = property(_visit_name)
2391 def __repr__(self):
2392 return "ColumnDefault(%r)" % (self.arg,)
2395class IdentityOptions(object):
2396 """Defines options for a named database sequence or an identity column.
2398 .. versionadded:: 1.3.18
2400 .. seealso::
2402 :class:`.Sequence`
2404 """
2406 def __init__(
2407 self,
2408 start=None,
2409 increment=None,
2410 minvalue=None,
2411 maxvalue=None,
2412 nominvalue=None,
2413 nomaxvalue=None,
2414 cycle=None,
2415 cache=None,
2416 order=None,
2417 ):
2418 """Construct a :class:`.IdentityOptions` object.
2420 See the :class:`.Sequence` documentation for a complete description
2421 of the parameters
2423 :param start: the starting index of the sequence.
2424 :param increment: the increment value of the sequence.
2425 :param minvalue: the minimum value of the sequence.
2426 :param maxvalue: the maximum value of the sequence.
2427 :param nominvalue: no minimum value of the sequence.
2428 :param nomaxvalue: no maximum value of the sequence.
2429 :param cycle: allows the sequence to wrap around when the maxvalue
2430 or minvalue has been reached.
2431 :param cache: optional integer value; number of future values in the
2432 sequence which are calculated in advance.
2433 :param order: optional boolean value; if true, renders the
2434 ORDER keyword.
2435 name.
2436 """
2437 self.start = start
2438 self.increment = increment
2439 self.minvalue = minvalue
2440 self.maxvalue = maxvalue
2441 self.nominvalue = nominvalue
2442 self.nomaxvalue = nomaxvalue
2443 self.cycle = cycle
2444 self.cache = cache
2445 self.order = order
2448class Sequence(IdentityOptions, DefaultGenerator):
2449 """Represents a named database sequence.
2451 The :class:`.Sequence` object represents the name and configurational
2452 parameters of a database sequence. It also represents
2453 a construct that can be "executed" by a SQLAlchemy :class:`_engine.Engine`
2454 or :class:`_engine.Connection`,
2455 rendering the appropriate "next value" function
2456 for the target database and returning a result.
2458 The :class:`.Sequence` is typically associated with a primary key column::
2460 some_table = Table(
2461 'some_table', metadata,
2462 Column('id', Integer, Sequence('some_table_seq'),
2463 primary_key=True)
2464 )
2466 When CREATE TABLE is emitted for the above :class:`_schema.Table`, if the
2467 target platform supports sequences, a CREATE SEQUENCE statement will
2468 be emitted as well. For platforms that don't support sequences,
2469 the :class:`.Sequence` construct is ignored.
2471 .. seealso::
2473 :class:`.CreateSequence`
2475 :class:`.DropSequence`
2477 """
2479 __visit_name__ = "sequence"
2481 is_sequence = True
2483 def __init__(
2484 self,
2485 name,
2486 start=None,
2487 increment=None,
2488 minvalue=None,
2489 maxvalue=None,
2490 nominvalue=None,
2491 nomaxvalue=None,
2492 cycle=None,
2493 schema=None,
2494 cache=None,
2495 order=None,
2496 optional=False,
2497 quote=None,
2498 metadata=None,
2499 quote_schema=None,
2500 for_update=False,
2501 ):
2502 """Construct a :class:`.Sequence` object.
2504 :param name: the name of the sequence.
2505 :param start: the starting index of the sequence. This value is
2506 used when the CREATE SEQUENCE command is emitted to the database
2507 as the value of the "START WITH" clause. If ``None``, the
2508 clause is omitted, which on most platforms indicates a starting
2509 value of 1.
2510 :param increment: the increment value of the sequence. This
2511 value is used when the CREATE SEQUENCE command is emitted to
2512 the database as the value of the "INCREMENT BY" clause. If ``None``,
2513 the clause is omitted, which on most platforms indicates an
2514 increment of 1.
2515 :param minvalue: the minimum value of the sequence. This
2516 value is used when the CREATE SEQUENCE command is emitted to
2517 the database as the value of the "MINVALUE" clause. If ``None``,
2518 the clause is omitted, which on most platforms indicates a
2519 minvalue of 1 and -2^63-1 for ascending and descending sequences,
2520 respectively.
2522 .. versionadded:: 1.0.7
2524 :param maxvalue: the maximum value of the sequence. This
2525 value is used when the CREATE SEQUENCE command is emitted to
2526 the database as the value of the "MAXVALUE" clause. If ``None``,
2527 the clause is omitted, which on most platforms indicates a
2528 maxvalue of 2^63-1 and -1 for ascending and descending sequences,
2529 respectively.
2531 .. versionadded:: 1.0.7
2533 :param nominvalue: no minimum value of the sequence. This
2534 value is used when the CREATE SEQUENCE command is emitted to
2535 the database as the value of the "NO MINVALUE" clause. If ``None``,
2536 the clause is omitted, which on most platforms indicates a
2537 minvalue of 1 and -2^63-1 for ascending and descending sequences,
2538 respectively.
2540 .. versionadded:: 1.0.7
2542 :param nomaxvalue: no maximum value of the sequence. This
2543 value is used when the CREATE SEQUENCE command is emitted to
2544 the database as the value of the "NO MAXVALUE" clause. If ``None``,
2545 the clause is omitted, which on most platforms indicates a
2546 maxvalue of 2^63-1 and -1 for ascending and descending sequences,
2547 respectively.
2549 .. versionadded:: 1.0.7
2551 :param cycle: allows the sequence to wrap around when the maxvalue
2552 or minvalue has been reached by an ascending or descending sequence
2553 respectively. This value is used when the CREATE SEQUENCE command
2554 is emitted to the database as the "CYCLE" clause. If the limit is
2555 reached, the next number generated will be the minvalue or maxvalue,
2556 respectively. If cycle=False (the default) any calls to nextval
2557 after the sequence has reached its maximum value will return an
2558 error.
2560 .. versionadded:: 1.0.7
2562 :param schema: optional schema name for the sequence, if located
2563 in a schema other than the default. The rules for selecting the
2564 schema name when a :class:`_schema.MetaData`
2565 is also present are the same
2566 as that of :paramref:`_schema.Table.schema`.
2568 :param cache: optional integer value; number of future values in the
2569 sequence which are calculated in advance. Renders the CACHE keyword
2570 understood by Oracle and PostgreSQL.
2572 .. versionadded:: 1.1.12
2574 :param order: optional boolean value; if true, renders the
2575 ORDER keyword, understood by Oracle, indicating the sequence is
2576 definitively ordered. May be necessary to provide deterministic
2577 ordering using Oracle RAC.
2579 .. versionadded:: 1.1.12
2581 :param optional: boolean value, when ``True``, indicates that this
2582 :class:`.Sequence` object only needs to be explicitly generated
2583 on backends that don't provide another way to generate primary
2584 key identifiers. Currently, it essentially means, "don't create
2585 this sequence on the PostgreSQL backend, where the SERIAL keyword
2586 creates a sequence for us automatically".
2587 :param quote: boolean value, when ``True`` or ``False``, explicitly
2588 forces quoting of the :paramref:`_schema.Sequence.name` on or off.
2589 When left at its default of ``None``, normal quoting rules based
2590 on casing and reserved words take place.
2591 :param quote_schema: Set the quoting preferences for the ``schema``
2592 name.
2594 :param metadata: optional :class:`_schema.MetaData` object which this
2595 :class:`.Sequence` will be associated with. A :class:`.Sequence`
2596 that is associated with a :class:`_schema.MetaData`
2597 gains the following
2598 capabilities:
2600 * The :class:`.Sequence` will inherit the
2601 :paramref:`_schema.MetaData.schema`
2602 parameter specified to the target :class:`_schema.MetaData`, which
2603 affects the production of CREATE / DROP DDL, if any.
2605 * The :meth:`.Sequence.create` and :meth:`.Sequence.drop` methods
2606 automatically use the engine bound to the :class:`_schema.MetaData`
2607 object, if any.
2609 * The :meth:`_schema.MetaData.create_all` and
2610 :meth:`_schema.MetaData.drop_all`
2611 methods will emit CREATE / DROP for this :class:`.Sequence`,
2612 even if the :class:`.Sequence` is not associated with any
2613 :class:`_schema.Table` / :class:`_schema.Column`
2614 that's a member of this
2615 :class:`_schema.MetaData`.
2617 The above behaviors can only occur if the :class:`.Sequence` is
2618 explicitly associated with the :class:`_schema.MetaData`
2619 via this parameter.
2621 .. seealso::
2623 :ref:`sequence_metadata` - full discussion of the
2624 :paramref:`.Sequence.metadata` parameter.
2626 :param for_update: Indicates this :class:`.Sequence`, when associated
2627 with a :class:`_schema.Column`,
2628 should be invoked for UPDATE statements
2629 on that column's table, rather than for INSERT statements, when
2630 no value is otherwise present for that column in the statement.
2632 """
2633 DefaultGenerator.__init__(self, for_update=for_update)
2634 IdentityOptions.__init__(
2635 self,
2636 start=start,
2637 increment=increment,
2638 minvalue=minvalue,
2639 maxvalue=maxvalue,
2640 nominvalue=nominvalue,
2641 nomaxvalue=nomaxvalue,
2642 cycle=cycle,
2643 cache=cache,
2644 order=order,
2645 )
2646 self.name = quoted_name(name, quote)
2647 self.optional = optional
2648 if schema is BLANK_SCHEMA:
2649 self.schema = schema = None
2650 elif metadata is not None and schema is None and metadata.schema:
2651 self.schema = schema = metadata.schema
2652 else:
2653 self.schema = quoted_name(schema, quote_schema)
2654 self.metadata = metadata
2655 self._key = _get_table_key(name, schema)
2656 if metadata:
2657 self._set_metadata(metadata)
2659 @util.memoized_property
2660 def is_callable(self):
2661 return False
2663 @util.memoized_property
2664 def is_clause_element(self):
2665 return False
2667 @util.dependencies("sqlalchemy.sql.functions.func")
2668 def next_value(self, func):
2669 """Return a :class:`.next_value` function element
2670 which will render the appropriate increment function
2671 for this :class:`.Sequence` within any SQL expression.
2673 """
2674 return func.next_value(self, bind=self.bind)
2676 def _set_parent(self, column):
2677 super(Sequence, self)._set_parent(column)
2678 column._on_table_attach(self._set_table)
2680 def _set_table(self, column, table):
2681 self._set_metadata(table.metadata)
2683 def _set_metadata(self, metadata):
2684 self.metadata = metadata
2685 self.metadata._sequences[self._key] = self
2687 @property
2688 def bind(self):
2689 if self.metadata:
2690 return self.metadata.bind
2691 else:
2692 return None
2694 def create(self, bind=None, checkfirst=True):
2695 """Creates this sequence in the database."""
2697 if bind is None:
2698 bind = _bind_or_error(self)
2699 bind._run_visitor(ddl.SchemaGenerator, self, checkfirst=checkfirst)
2701 def drop(self, bind=None, checkfirst=True):
2702 """Drops this sequence from the database."""
2704 if bind is None:
2705 bind = _bind_or_error(self)
2706 bind._run_visitor(ddl.SchemaDropper, self, checkfirst=checkfirst)
2708 def _not_a_column_expr(self):
2709 raise exc.InvalidRequestError(
2710 "This %s cannot be used directly "
2711 "as a column expression. Use func.next_value(sequence) "
2712 "to produce a 'next value' function that's usable "
2713 "as a column element." % self.__class__.__name__
2714 )
2717@inspection._self_inspects
2718class FetchedValue(_NotAColumnExpr, SchemaEventTarget):
2719 """A marker for a transparent database-side default.
2721 Use :class:`.FetchedValue` when the database is configured
2722 to provide some automatic default for a column.
2724 E.g.::
2726 Column('foo', Integer, FetchedValue())
2728 Would indicate that some trigger or default generator
2729 will create a new value for the ``foo`` column during an
2730 INSERT.
2732 .. seealso::
2734 :ref:`triggered_columns`
2736 """
2738 is_server_default = True
2739 reflected = False
2740 has_argument = False
2742 def __init__(self, for_update=False):
2743 self.for_update = for_update
2745 def _as_for_update(self, for_update):
2746 if for_update == self.for_update:
2747 return self
2748 else:
2749 return self._clone(for_update)
2751 def _clone(self, for_update):
2752 n = self.__class__.__new__(self.__class__)
2753 n.__dict__.update(self.__dict__)
2754 n.__dict__.pop("column", None)
2755 n.for_update = for_update
2756 return n
2758 def _set_parent(self, column):
2759 self.column = column
2760 if self.for_update:
2761 self.column.server_onupdate = self
2762 else:
2763 self.column.server_default = self
2765 def __repr__(self):
2766 return util.generic_repr(self)
2769class DefaultClause(FetchedValue):
2770 """A DDL-specified DEFAULT column value.
2772 :class:`.DefaultClause` is a :class:`.FetchedValue`
2773 that also generates a "DEFAULT" clause when
2774 "CREATE TABLE" is emitted.
2776 :class:`.DefaultClause` is generated automatically
2777 whenever the ``server_default``, ``server_onupdate`` arguments of
2778 :class:`_schema.Column` are used. A :class:`.DefaultClause`
2779 can be passed positionally as well.
2781 For example, the following::
2783 Column('foo', Integer, server_default="50")
2785 Is equivalent to::
2787 Column('foo', Integer, DefaultClause("50"))
2789 """
2791 has_argument = True
2793 def __init__(self, arg, for_update=False, _reflected=False):
2794 util.assert_arg_type(
2795 arg, (util.string_types[0], ClauseElement, TextClause), "arg"
2796 )
2797 super(DefaultClause, self).__init__(for_update)
2798 self.arg = arg
2799 self.reflected = _reflected
2801 def __repr__(self):
2802 return "DefaultClause(%r, for_update=%r)" % (self.arg, self.for_update)
2805@util.deprecated_cls(
2806 "0.6",
2807 ":class:`.PassiveDefault` is deprecated and will be removed in a "
2808 "future release. Please refer to :class:`.DefaultClause`.",
2809)
2810class PassiveDefault(DefaultClause):
2811 """A DDL-specified DEFAULT column value.
2812 """
2814 def __init__(self, *arg, **kw):
2815 DefaultClause.__init__(self, *arg, **kw)
2818class Constraint(DialectKWArgs, SchemaItem):
2819 """A table-level SQL constraint."""
2821 __visit_name__ = "constraint"
2823 def __init__(
2824 self,
2825 name=None,
2826 deferrable=None,
2827 initially=None,
2828 _create_rule=None,
2829 info=None,
2830 _type_bound=False,
2831 **dialect_kw
2832 ):
2833 r"""Create a SQL constraint.
2835 :param name:
2836 Optional, the in-database name of this ``Constraint``.
2838 :param deferrable:
2839 Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
2840 issuing DDL for this constraint.
2842 :param initially:
2843 Optional string. If set, emit INITIALLY <value> when issuing DDL
2844 for this constraint.
2846 :param info: Optional data dictionary which will be populated into the
2847 :attr:`.SchemaItem.info` attribute of this object.
2849 .. versionadded:: 1.0.0
2851 :param _create_rule:
2852 a callable which is passed the DDLCompiler object during
2853 compilation. Returns True or False to signal inline generation of
2854 this Constraint.
2856 The AddConstraint and DropConstraint DDL constructs provide
2857 DDLElement's more comprehensive "conditional DDL" approach that is
2858 passed a database connection when DDL is being issued. _create_rule
2859 is instead called during any CREATE TABLE compilation, where there
2860 may not be any transaction/connection in progress. However, it
2861 allows conditional compilation of the constraint even for backends
2862 which do not support addition of constraints through ALTER TABLE,
2863 which currently includes SQLite.
2865 _create_rule is used by some types to create constraints.
2866 Currently, its call signature is subject to change at any time.
2868 :param \**dialect_kw: Additional keyword arguments are dialect
2869 specific, and passed in the form ``<dialectname>_<argname>``. See
2870 the documentation regarding an individual dialect at
2871 :ref:`dialect_toplevel` for detail on documented arguments.
2873 """
2875 self.name = name
2876 self.deferrable = deferrable
2877 self.initially = initially
2878 if info:
2879 self.info = info
2880 self._create_rule = _create_rule
2881 self._type_bound = _type_bound
2882 util.set_creation_order(self)
2883 self._validate_dialect_kwargs(dialect_kw)
2885 @property
2886 def table(self):
2887 try:
2888 if isinstance(self.parent, Table):
2889 return self.parent
2890 except AttributeError:
2891 pass
2892 raise exc.InvalidRequestError(
2893 "This constraint is not bound to a table. Did you "
2894 "mean to call table.append_constraint(constraint) ?"
2895 )
2897 def _set_parent(self, parent):
2898 self.parent = parent
2899 parent.constraints.add(self)
2901 def copy(self, **kw):
2902 raise NotImplementedError()
2905def _to_schema_column(element):
2906 if hasattr(element, "__clause_element__"):
2907 element = element.__clause_element__()
2908 if not isinstance(element, Column):
2909 raise exc.ArgumentError("schema.Column object expected")
2910 return element
2913def _to_schema_column_or_string(element):
2914 if element is None:
2915 return element
2916 elif hasattr(element, "__clause_element__"):
2917 element = element.__clause_element__()
2918 if not isinstance(element, util.string_types + (ColumnElement,)):
2919 msg = "Element %r is not a string name or column element"
2920 raise exc.ArgumentError(msg % element)
2921 return element
2924class ColumnCollectionMixin(object):
2926 columns = None
2927 """A :class:`_expression.ColumnCollection` of :class:`_schema.Column`
2928 objects.
2930 This collection represents the columns which are referred to by
2931 this object.
2933 """
2935 _allow_multiple_tables = False
2937 def __init__(self, *columns, **kw):
2938 _autoattach = kw.pop("_autoattach", True)
2939 self._column_flag = kw.pop("_column_flag", False)
2940 self.columns = ColumnCollection()
2941 self._pending_colargs = [
2942 _to_schema_column_or_string(c) for c in columns
2943 ]
2944 if _autoattach and self._pending_colargs:
2945 self._check_attach()
2947 @classmethod
2948 def _extract_col_expression_collection(cls, expressions):
2949 for expr in expressions:
2950 strname = None
2951 column = None
2952 if hasattr(expr, "__clause_element__"):
2953 expr = expr.__clause_element__()
2955 if not isinstance(expr, (ColumnElement, TextClause)):
2956 # this assumes a string
2957 strname = expr
2958 else:
2959 cols = []
2960 visitors.traverse(expr, {}, {"column": cols.append})
2961 if cols:
2962 column = cols[0]
2963 add_element = column if column is not None else strname
2964 yield expr, column, strname, add_element
2966 def _check_attach(self, evt=False):
2967 col_objs = [c for c in self._pending_colargs if isinstance(c, Column)]
2969 cols_w_table = [c for c in col_objs if isinstance(c.table, Table)]
2971 cols_wo_table = set(col_objs).difference(cols_w_table)
2972 if cols_wo_table:
2973 # feature #3341 - place event listeners for Column objects
2974 # such that when all those cols are attached, we autoattach.
2975 assert not evt, "Should not reach here on event call"
2977 # issue #3411 - don't do the per-column auto-attach if some of the
2978 # columns are specified as strings.
2979 has_string_cols = set(
2980 c for c in self._pending_colargs if c is not None
2981 ).difference(col_objs)
2982 if not has_string_cols:
2984 def _col_attached(column, table):
2985 # this isinstance() corresponds with the
2986 # isinstance() above; only want to count Table-bound
2987 # columns
2988 if isinstance(table, Table):
2989 cols_wo_table.discard(column)
2990 if not cols_wo_table:
2991 self._check_attach(evt=True)
2993 self._cols_wo_table = cols_wo_table
2994 for col in cols_wo_table:
2995 col._on_table_attach(_col_attached)
2996 return
2998 columns = cols_w_table
3000 tables = {c.table for c in columns}
3001 if len(tables) == 1:
3002 self._set_parent_with_dispatch(tables.pop())
3003 elif len(tables) > 1 and not self._allow_multiple_tables:
3004 table = columns[0].table
3005 others = [c for c in columns[1:] if c.table is not table]
3006 if others:
3007 raise exc.ArgumentError(
3008 "Column(s) %s are not part of table '%s'."
3009 % (
3010 ", ".join("'%s'" % c for c in others),
3011 table.description,
3012 )
3013 )
3015 def _col_expressions(self, table):
3016 return [
3017 table.c[col] if isinstance(col, util.string_types) else col
3018 for col in self._pending_colargs
3019 ]
3021 def _set_parent(self, table):
3022 for col in self._col_expressions(table):
3023 if col is not None:
3024 self.columns.add(col)
3027class ColumnCollectionConstraint(ColumnCollectionMixin, Constraint):
3028 """A constraint that proxies a ColumnCollection."""
3030 def __init__(self, *columns, **kw):
3031 r"""
3032 :param \*columns:
3033 A sequence of column names or Column objects.
3035 :param name:
3036 Optional, the in-database name of this constraint.
3038 :param deferrable:
3039 Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
3040 issuing DDL for this constraint.
3042 :param initially:
3043 Optional string. If set, emit INITIALLY <value> when issuing DDL
3044 for this constraint.
3046 :param \**kw: other keyword arguments including dialect-specific
3047 arguments are propagated to the :class:`.Constraint` superclass.
3049 """
3050 _autoattach = kw.pop("_autoattach", True)
3051 _column_flag = kw.pop("_column_flag", False)
3052 Constraint.__init__(self, **kw)
3053 ColumnCollectionMixin.__init__(
3054 self, *columns, _autoattach=_autoattach, _column_flag=_column_flag
3055 )
3057 columns = None
3058 """A :class:`_expression.ColumnCollection` representing the set of columns
3059 for this constraint.
3061 """
3063 def _set_parent(self, table):
3064 Constraint._set_parent(self, table)
3065 ColumnCollectionMixin._set_parent(self, table)
3067 def __contains__(self, x):
3068 return x in self.columns
3070 def copy(self, **kw):
3071 # ticket #5276
3072 constraint_kwargs = {}
3073 for dialect_name in self.dialect_options:
3074 dialect_options = self.dialect_options[dialect_name]._non_defaults
3075 for (
3076 dialect_option_key,
3077 dialect_option_value,
3078 ) in dialect_options.items():
3079 constraint_kwargs[
3080 dialect_name + "_" + dialect_option_key
3081 ] = dialect_option_value
3083 c = self.__class__(
3084 name=self.name,
3085 deferrable=self.deferrable,
3086 initially=self.initially,
3087 *self.columns.keys(),
3088 **constraint_kwargs
3089 )
3090 return self._schema_item_copy(c)
3092 def contains_column(self, col):
3093 """Return True if this constraint contains the given column.
3095 Note that this object also contains an attribute ``.columns``
3096 which is a :class:`_expression.ColumnCollection` of
3097 :class:`_schema.Column` objects.
3099 """
3101 return self.columns.contains_column(col)
3103 def __iter__(self):
3104 # inlining of
3105 # return iter(self.columns)
3106 # ColumnCollection->OrderedProperties->OrderedDict
3107 ordered_dict = self.columns._data
3108 return (ordered_dict[key] for key in ordered_dict._list)
3110 def __len__(self):
3111 return len(self.columns._data)
3114class CheckConstraint(ColumnCollectionConstraint):
3115 """A table- or column-level CHECK constraint.
3117 Can be included in the definition of a Table or Column.
3118 """
3120 _allow_multiple_tables = True
3122 @_document_text_coercion(
3123 "sqltext",
3124 ":class:`.CheckConstraint`",
3125 ":paramref:`.CheckConstraint.sqltext`",
3126 )
3127 def __init__(
3128 self,
3129 sqltext,
3130 name=None,
3131 deferrable=None,
3132 initially=None,
3133 table=None,
3134 info=None,
3135 _create_rule=None,
3136 _autoattach=True,
3137 _type_bound=False,
3138 **kw
3139 ):
3140 r"""Construct a CHECK constraint.
3142 :param sqltext:
3143 A string containing the constraint definition, which will be used
3144 verbatim, or a SQL expression construct. If given as a string,
3145 the object is converted to a :func:`_expression.text` object.
3146 If the textual
3147 string includes a colon character, escape this using a backslash::
3149 CheckConstraint(r"foo ~ E'a(?\:b|c)d")
3151 :param name:
3152 Optional, the in-database name of the constraint.
3154 :param deferrable:
3155 Optional bool. If set, emit DEFERRABLE or NOT DEFERRABLE when
3156 issuing DDL for this constraint.
3158 :param initially:
3159 Optional string. If set, emit INITIALLY <value> when issuing DDL
3160 for this constraint.
3162 :param info: Optional data dictionary which will be populated into the
3163 :attr:`.SchemaItem.info` attribute of this object.
3165 .. versionadded:: 1.0.0
3167 """
3169 self.sqltext = _literal_as_text(sqltext, allow_coercion_to_text=True)
3171 columns = []
3172 visitors.traverse(self.sqltext, {}, {"column": columns.append})
3174 super(CheckConstraint, self).__init__(
3175 name=name,
3176 deferrable=deferrable,
3177 initially=initially,
3178 _create_rule=_create_rule,
3179 info=info,
3180 _type_bound=_type_bound,
3181 _autoattach=_autoattach,
3182 *columns,
3183 **kw
3184 )
3185 if table is not None:
3186 self._set_parent_with_dispatch(table)
3188 def __visit_name__(self):
3189 if isinstance(self.parent, Table):
3190 return "check_constraint"
3191 else:
3192 return "column_check_constraint"
3194 __visit_name__ = property(__visit_name__)
3196 def copy(self, target_table=None, **kw):
3197 if target_table is not None:
3198 sqltext = _copy_expression(self.sqltext, self.table, target_table)
3199 else:
3200 sqltext = self.sqltext
3201 c = CheckConstraint(
3202 sqltext,
3203 name=self.name,
3204 initially=self.initially,
3205 deferrable=self.deferrable,
3206 _create_rule=self._create_rule,
3207 table=target_table,
3208 _autoattach=False,
3209 _type_bound=self._type_bound,
3210 )
3211 return self._schema_item_copy(c)
3214class ForeignKeyConstraint(ColumnCollectionConstraint):
3215 """A table-level FOREIGN KEY constraint.
3217 Defines a single column or composite FOREIGN KEY ... REFERENCES
3218 constraint. For a no-frills, single column foreign key, adding a
3219 :class:`_schema.ForeignKey` to the definition of a :class:`_schema.Column`
3220 is a
3221 shorthand equivalent for an unnamed, single column
3222 :class:`_schema.ForeignKeyConstraint`.
3224 Examples of foreign key configuration are in :ref:`metadata_foreignkeys`.
3226 """
3228 __visit_name__ = "foreign_key_constraint"
3230 def __init__(
3231 self,
3232 columns,
3233 refcolumns,
3234 name=None,
3235 onupdate=None,
3236 ondelete=None,
3237 deferrable=None,
3238 initially=None,
3239 use_alter=False,
3240 link_to_name=False,
3241 match=None,
3242 table=None,
3243 info=None,
3244 **dialect_kw
3245 ):
3246 r"""Construct a composite-capable FOREIGN KEY.
3248 :param columns: A sequence of local column names. The named columns
3249 must be defined and present in the parent Table. The names should
3250 match the ``key`` given to each column (defaults to the name) unless
3251 ``link_to_name`` is True.
3253 :param refcolumns: A sequence of foreign column names or Column
3254 objects. The columns must all be located within the same Table.
3256 :param name: Optional, the in-database name of the key.
3258 :param onupdate: Optional string. If set, emit ON UPDATE <value> when
3259 issuing DDL for this constraint. Typical values include CASCADE,
3260 DELETE and RESTRICT.
3262 :param ondelete: Optional string. If set, emit ON DELETE <value> when
3263 issuing DDL for this constraint. Typical values include CASCADE,
3264 DELETE and RESTRICT.
3266 :param deferrable: Optional bool. If set, emit DEFERRABLE or NOT
3267 DEFERRABLE when issuing DDL for this constraint.
3269 :param initially: Optional string. If set, emit INITIALLY <value> when
3270 issuing DDL for this constraint.
3272 :param link_to_name: if True, the string name given in ``column`` is
3273 the rendered name of the referenced column, not its locally assigned
3274 ``key``.
3276 :param use_alter: If True, do not emit the DDL for this constraint as
3277 part of the CREATE TABLE definition. Instead, generate it via an
3278 ALTER TABLE statement issued after the full collection of tables
3279 have been created, and drop it via an ALTER TABLE statement before
3280 the full collection of tables are dropped.
3282 The use of :paramref:`_schema.ForeignKeyConstraint.use_alter` is
3283 particularly geared towards the case where two or more tables
3284 are established within a mutually-dependent foreign key constraint
3285 relationship; however, the :meth:`_schema.MetaData.create_all` and
3286 :meth:`_schema.MetaData.drop_all`
3287 methods will perform this resolution
3288 automatically, so the flag is normally not needed.
3290 .. versionchanged:: 1.0.0 Automatic resolution of foreign key
3291 cycles has been added, removing the need to use the
3292 :paramref:`_schema.ForeignKeyConstraint.use_alter` in typical use
3293 cases.
3295 .. seealso::
3297 :ref:`use_alter`
3299 :param match: Optional string. If set, emit MATCH <value> when issuing
3300 DDL for this constraint. Typical values include SIMPLE, PARTIAL
3301 and FULL.
3303 :param info: Optional data dictionary which will be populated into the
3304 :attr:`.SchemaItem.info` attribute of this object.
3306 .. versionadded:: 1.0.0
3308 :param \**dialect_kw: Additional keyword arguments are dialect
3309 specific, and passed in the form ``<dialectname>_<argname>``. See
3310 the documentation regarding an individual dialect at
3311 :ref:`dialect_toplevel` for detail on documented arguments.
3313 .. versionadded:: 0.9.2
3315 """
3317 Constraint.__init__(
3318 self,
3319 name=name,
3320 deferrable=deferrable,
3321 initially=initially,
3322 info=info,
3323 **dialect_kw
3324 )
3325 self.onupdate = onupdate
3326 self.ondelete = ondelete
3327 self.link_to_name = link_to_name
3328 self.use_alter = use_alter
3329 self.match = match
3331 if len(set(columns)) != len(refcolumns):
3332 if len(set(columns)) != len(columns):
3333 # e.g. FOREIGN KEY (a, a) REFERENCES r (b, c)
3334 raise exc.ArgumentError(
3335 "ForeignKeyConstraint with duplicate source column "
3336 "references are not supported."
3337 )
3338 else:
3339 # e.g. FOREIGN KEY (a) REFERENCES r (b, c)
3340 # paraphrasing https://www.postgresql.org/docs/9.2/static/\
3341 # ddl-constraints.html
3342 raise exc.ArgumentError(
3343 "ForeignKeyConstraint number "
3344 "of constrained columns must match the number of "
3345 "referenced columns."
3346 )
3348 # standalone ForeignKeyConstraint - create
3349 # associated ForeignKey objects which will be applied to hosted
3350 # Column objects (in col.foreign_keys), either now or when attached
3351 # to the Table for string-specified names
3352 self.elements = [
3353 ForeignKey(
3354 refcol,
3355 _constraint=self,
3356 name=self.name,
3357 onupdate=self.onupdate,
3358 ondelete=self.ondelete,
3359 use_alter=self.use_alter,
3360 link_to_name=self.link_to_name,
3361 match=self.match,
3362 deferrable=self.deferrable,
3363 initially=self.initially,
3364 **self.dialect_kwargs
3365 )
3366 for refcol in refcolumns
3367 ]
3369 ColumnCollectionMixin.__init__(self, *columns)
3370 if table is not None:
3371 if hasattr(self, "parent"):
3372 assert table is self.parent
3373 self._set_parent_with_dispatch(table)
3375 def _append_element(self, column, fk):
3376 self.columns.add(column)
3377 self.elements.append(fk)
3379 columns = None
3380 """A :class:`_expression.ColumnCollection` representing the set of columns
3381 for this constraint.
3383 """
3385 elements = None
3386 """A sequence of :class:`_schema.ForeignKey` objects.
3388 Each :class:`_schema.ForeignKey`
3389 represents a single referring column/referred
3390 column pair.
3392 This collection is intended to be read-only.
3394 """
3396 @property
3397 def _elements(self):
3398 # legacy - provide a dictionary view of (column_key, fk)
3399 return util.OrderedDict(zip(self.column_keys, self.elements))
3401 @property
3402 def _referred_schema(self):
3403 for elem in self.elements:
3404 return elem._referred_schema
3405 else:
3406 return None
3408 @property
3409 def referred_table(self):
3410 """The :class:`_schema.Table` object to which this
3411 :class:`_schema.ForeignKeyConstraint` references.
3413 This is a dynamically calculated attribute which may not be available
3414 if the constraint and/or parent table is not yet associated with
3415 a metadata collection that contains the referred table.
3417 .. versionadded:: 1.0.0
3419 """
3420 return self.elements[0].column.table
3422 def _validate_dest_table(self, table):
3423 table_keys = set([elem._table_key() for elem in self.elements])
3424 if None not in table_keys and len(table_keys) > 1:
3425 elem0, elem1 = sorted(table_keys)[0:2]
3426 raise exc.ArgumentError(
3427 "ForeignKeyConstraint on %s(%s) refers to "
3428 "multiple remote tables: %s and %s"
3429 % (table.fullname, self._col_description, elem0, elem1)
3430 )
3432 @property
3433 def column_keys(self):
3434 """Return a list of string keys representing the local
3435 columns in this :class:`_schema.ForeignKeyConstraint`.
3437 This list is either the original string arguments sent
3438 to the constructor of the :class:`_schema.ForeignKeyConstraint`,
3439 or if the constraint has been initialized with :class:`_schema.Column`
3440 objects, is the string .key of each element.
3442 .. versionadded:: 1.0.0
3444 """
3445 if hasattr(self, "parent"):
3446 return self.columns.keys()
3447 else:
3448 return [
3449 col.key if isinstance(col, ColumnElement) else str(col)
3450 for col in self._pending_colargs
3451 ]
3453 @property
3454 def _col_description(self):
3455 return ", ".join(self.column_keys)
3457 def _set_parent(self, table):
3458 Constraint._set_parent(self, table)
3460 try:
3461 ColumnCollectionConstraint._set_parent(self, table)
3462 except KeyError as ke:
3463 util.raise_(
3464 exc.ArgumentError(
3465 "Can't create ForeignKeyConstraint "
3466 "on table '%s': no column "
3467 "named '%s' is present." % (table.description, ke.args[0])
3468 ),
3469 from_=ke,
3470 )
3472 for col, fk in zip(self.columns, self.elements):
3473 if not hasattr(fk, "parent") or fk.parent is not col:
3474 fk._set_parent_with_dispatch(col)
3476 self._validate_dest_table(table)
3478 def copy(self, schema=None, target_table=None, **kw):
3479 fkc = ForeignKeyConstraint(
3480 [x.parent.key for x in self.elements],
3481 [
3482 x._get_colspec(
3483 schema=schema,
3484 table_name=target_table.name
3485 if target_table is not None
3486 and x._table_key() == x.parent.table.key
3487 else None,
3488 )
3489 for x in self.elements
3490 ],
3491 name=self.name,
3492 onupdate=self.onupdate,
3493 ondelete=self.ondelete,
3494 use_alter=self.use_alter,
3495 deferrable=self.deferrable,
3496 initially=self.initially,
3497 link_to_name=self.link_to_name,
3498 match=self.match,
3499 )
3500 for self_fk, other_fk in zip(self.elements, fkc.elements):
3501 self_fk._schema_item_copy(other_fk)
3502 return self._schema_item_copy(fkc)
3505class PrimaryKeyConstraint(ColumnCollectionConstraint):
3506 """A table-level PRIMARY KEY constraint.
3508 The :class:`.PrimaryKeyConstraint` object is present automatically
3509 on any :class:`_schema.Table` object; it is assigned a set of
3510 :class:`_schema.Column` objects corresponding to those marked with
3511 the :paramref:`_schema.Column.primary_key` flag::
3513 >>> my_table = Table('mytable', metadata,
3514 ... Column('id', Integer, primary_key=True),
3515 ... Column('version_id', Integer, primary_key=True),
3516 ... Column('data', String(50))
3517 ... )
3518 >>> my_table.primary_key
3519 PrimaryKeyConstraint(
3520 Column('id', Integer(), table=<mytable>,
3521 primary_key=True, nullable=False),
3522 Column('version_id', Integer(), table=<mytable>,
3523 primary_key=True, nullable=False)
3524 )
3526 The primary key of a :class:`_schema.Table` can also be specified by using
3527 a :class:`.PrimaryKeyConstraint` object explicitly; in this mode of usage,
3528 the "name" of the constraint can also be specified, as well as other
3529 options which may be recognized by dialects::
3531 my_table = Table('mytable', metadata,
3532 Column('id', Integer),
3533 Column('version_id', Integer),
3534 Column('data', String(50)),
3535 PrimaryKeyConstraint('id', 'version_id',
3536 name='mytable_pk')
3537 )
3539 The two styles of column-specification should generally not be mixed.
3540 An warning is emitted if the columns present in the
3541 :class:`.PrimaryKeyConstraint`
3542 don't match the columns that were marked as ``primary_key=True``, if both
3543 are present; in this case, the columns are taken strictly from the
3544 :class:`.PrimaryKeyConstraint` declaration, and those columns otherwise
3545 marked as ``primary_key=True`` are ignored. This behavior is intended to
3546 be backwards compatible with previous behavior.
3548 .. versionchanged:: 0.9.2 Using a mixture of columns within a
3549 :class:`.PrimaryKeyConstraint` in addition to columns marked as
3550 ``primary_key=True`` now emits a warning if the lists don't match.
3551 The ultimate behavior of ignoring those columns marked with the flag
3552 only is currently maintained for backwards compatibility; this warning
3553 may raise an exception in a future release.
3555 For the use case where specific options are to be specified on the
3556 :class:`.PrimaryKeyConstraint`, but the usual style of using
3557 ``primary_key=True`` flags is still desirable, an empty
3558 :class:`.PrimaryKeyConstraint` may be specified, which will take on the
3559 primary key column collection from the :class:`_schema.Table` based on the
3560 flags::
3562 my_table = Table('mytable', metadata,
3563 Column('id', Integer, primary_key=True),
3564 Column('version_id', Integer, primary_key=True),
3565 Column('data', String(50)),
3566 PrimaryKeyConstraint(name='mytable_pk',
3567 mssql_clustered=True)
3568 )
3570 .. versionadded:: 0.9.2 an empty :class:`.PrimaryKeyConstraint` may now
3571 be specified for the purposes of establishing keyword arguments with
3572 the constraint, independently of the specification of "primary key"
3573 columns within the :class:`_schema.Table` itself; columns marked as
3574 ``primary_key=True`` will be gathered into the empty constraint's
3575 column collection.
3577 """
3579 __visit_name__ = "primary_key_constraint"
3581 def __init__(self, *columns, **kw):
3582 self._implicit_generated = kw.pop("_implicit_generated", False)
3583 super(PrimaryKeyConstraint, self).__init__(*columns, **kw)
3585 def _set_parent(self, table):
3586 super(PrimaryKeyConstraint, self)._set_parent(table)
3588 if table.primary_key is not self:
3589 table.constraints.discard(table.primary_key)
3590 table.primary_key = self
3591 table.constraints.add(self)
3593 table_pks = [c for c in table.c if c.primary_key]
3594 if (
3595 self.columns
3596 and table_pks
3597 and set(table_pks) != set(self.columns.values())
3598 ):
3599 util.warn(
3600 "Table '%s' specifies columns %s as primary_key=True, "
3601 "not matching locally specified columns %s; setting the "
3602 "current primary key columns to %s. This warning "
3603 "may become an exception in a future release"
3604 % (
3605 table.name,
3606 ", ".join("'%s'" % c.name for c in table_pks),
3607 ", ".join("'%s'" % c.name for c in self.columns),
3608 ", ".join("'%s'" % c.name for c in self.columns),
3609 )
3610 )
3611 table_pks[:] = []
3613 for c in self.columns:
3614 c.primary_key = True
3615 c.nullable = False
3616 self.columns.extend(table_pks)
3618 def _reload(self, columns):
3619 """repopulate this :class:`.PrimaryKeyConstraint` given
3620 a set of columns.
3622 Existing columns in the table that are marked as primary_key=True
3623 are maintained.
3625 Also fires a new event.
3627 This is basically like putting a whole new
3628 :class:`.PrimaryKeyConstraint` object on the parent
3629 :class:`_schema.Table` object without actually replacing the object.
3631 The ordering of the given list of columns is also maintained; these
3632 columns will be appended to the list of columns after any which
3633 are already present.
3635 """
3637 # set the primary key flag on new columns.
3638 # note any existing PK cols on the table also have their
3639 # flag still set.
3640 for col in columns:
3641 col.primary_key = True
3643 self.columns.extend(columns)
3645 PrimaryKeyConstraint._autoincrement_column._reset(self)
3646 self._set_parent_with_dispatch(self.table)
3648 def _replace(self, col):
3649 PrimaryKeyConstraint._autoincrement_column._reset(self)
3650 self.columns.replace(col)
3652 @property
3653 def columns_autoinc_first(self):
3654 autoinc = self._autoincrement_column
3656 if autoinc is not None:
3657 return [autoinc] + [c for c in self.columns if c is not autoinc]
3658 else:
3659 return list(self.columns)
3661 @util.memoized_property
3662 def _autoincrement_column(self):
3663 def _validate_autoinc(col, autoinc_true):
3664 if col.type._type_affinity is None or not issubclass(
3665 col.type._type_affinity, type_api.INTEGERTYPE._type_affinity
3666 ):
3667 if autoinc_true:
3668 raise exc.ArgumentError(
3669 "Column type %s on column '%s' is not "
3670 "compatible with autoincrement=True" % (col.type, col)
3671 )
3672 else:
3673 return False
3674 elif (
3675 not isinstance(col.default, (type(None), Sequence))
3676 and not autoinc_true
3677 ):
3678 return False
3679 elif col.server_default is not None and not autoinc_true:
3680 return False
3681 elif col.foreign_keys and col.autoincrement not in (
3682 True,
3683 "ignore_fk",
3684 ):
3685 return False
3686 return True
3688 if len(self.columns) == 1:
3689 col = list(self.columns)[0]
3691 if col.autoincrement is True:
3692 _validate_autoinc(col, True)
3693 return col
3694 elif col.autoincrement in (
3695 "auto",
3696 "ignore_fk",
3697 ) and _validate_autoinc(col, False):
3698 return col
3700 else:
3701 autoinc = None
3702 for col in self.columns:
3703 if col.autoincrement is True:
3704 _validate_autoinc(col, True)
3705 if autoinc is not None:
3706 raise exc.ArgumentError(
3707 "Only one Column may be marked "
3708 "autoincrement=True, found both %s and %s."
3709 % (col.name, autoinc.name)
3710 )
3711 else:
3712 autoinc = col
3714 return autoinc
3717class UniqueConstraint(ColumnCollectionConstraint):
3718 """A table-level UNIQUE constraint.
3720 Defines a single column or composite UNIQUE constraint. For a no-frills,
3721 single column constraint, adding ``unique=True`` to the ``Column``
3722 definition is a shorthand equivalent for an unnamed, single column
3723 UniqueConstraint.
3724 """
3726 __visit_name__ = "unique_constraint"
3729class Index(DialectKWArgs, ColumnCollectionMixin, SchemaItem):
3730 """A table-level INDEX.
3732 Defines a composite (one or more column) INDEX.
3734 E.g.::
3736 sometable = Table("sometable", metadata,
3737 Column("name", String(50)),
3738 Column("address", String(100))
3739 )
3741 Index("some_index", sometable.c.name)
3743 For a no-frills, single column index, adding
3744 :class:`_schema.Column` also supports ``index=True``::
3746 sometable = Table("sometable", metadata,
3747 Column("name", String(50), index=True)
3748 )
3750 For a composite index, multiple columns can be specified::
3752 Index("some_index", sometable.c.name, sometable.c.address)
3754 Functional indexes are supported as well, typically by using the
3755 :data:`.func` construct in conjunction with table-bound
3756 :class:`_schema.Column` objects::
3758 Index("some_index", func.lower(sometable.c.name))
3760 An :class:`.Index` can also be manually associated with a
3761 :class:`_schema.Table`,
3762 either through inline declaration or using
3763 :meth:`_schema.Table.append_constraint`. When this approach is used,
3764 the names
3765 of the indexed columns can be specified as strings::
3767 Table("sometable", metadata,
3768 Column("name", String(50)),
3769 Column("address", String(100)),
3770 Index("some_index", "name", "address")
3771 )
3773 To support functional or expression-based indexes in this form, the
3774 :func:`_expression.text` construct may be used::
3776 from sqlalchemy import text
3778 Table("sometable", metadata,
3779 Column("name", String(50)),
3780 Column("address", String(100)),
3781 Index("some_index", text("lower(name)"))
3782 )
3784 .. versionadded:: 0.9.5 the :func:`_expression.text`
3785 construct may be used to
3786 specify :class:`.Index` expressions, provided the :class:`.Index`
3787 is explicitly associated with the :class:`_schema.Table`.
3790 .. seealso::
3792 :ref:`schema_indexes` - General information on :class:`.Index`.
3794 :ref:`postgresql_indexes` - PostgreSQL-specific options available for
3795 the :class:`.Index` construct.
3797 :ref:`mysql_indexes` - MySQL-specific options available for the
3798 :class:`.Index` construct.
3800 :ref:`mssql_indexes` - MSSQL-specific options available for the
3801 :class:`.Index` construct.
3803 """
3805 __visit_name__ = "index"
3807 def __init__(self, name, *expressions, **kw):
3808 r"""Construct an index object.
3810 :param name:
3811 The name of the index
3813 :param \*expressions:
3814 Column expressions to include in the index. The expressions
3815 are normally instances of :class:`_schema.Column`, but may also
3816 be arbitrary SQL expressions which ultimately refer to a
3817 :class:`_schema.Column`.
3819 :param unique=False:
3820 Keyword only argument; if True, create a unique index.
3822 :param quote=None:
3823 Keyword only argument; whether to apply quoting to the name of
3824 the index. Works in the same manner as that of
3825 :paramref:`_schema.Column.quote`.
3827 :param info=None: Optional data dictionary which will be populated
3828 into the :attr:`.SchemaItem.info` attribute of this object.
3830 .. versionadded:: 1.0.0
3832 :param \**kw: Additional keyword arguments not mentioned above are
3833 dialect specific, and passed in the form
3834 ``<dialectname>_<argname>``. See the documentation regarding an
3835 individual dialect at :ref:`dialect_toplevel` for detail on
3836 documented arguments.
3838 """
3839 self.table = table = None
3841 columns = []
3842 processed_expressions = []
3843 for (
3844 expr,
3845 column,
3846 strname,
3847 add_element,
3848 ) in self._extract_col_expression_collection(expressions):
3849 columns.append(add_element)
3850 processed_expressions.append(expr)
3852 self.expressions = processed_expressions
3853 self.name = quoted_name(name, kw.pop("quote", None))
3854 self.unique = kw.pop("unique", False)
3855 _column_flag = kw.pop("_column_flag", False)
3856 if "info" in kw:
3857 self.info = kw.pop("info")
3859 # TODO: consider "table" argument being public, but for
3860 # the purpose of the fix here, it starts as private.
3861 if "_table" in kw:
3862 table = kw.pop("_table")
3864 self._validate_dialect_kwargs(kw)
3866 # will call _set_parent() if table-bound column
3867 # objects are present
3868 ColumnCollectionMixin.__init__(
3869 self, *columns, _column_flag=_column_flag
3870 )
3872 if table is not None:
3873 self._set_parent(table)
3875 def _set_parent(self, table):
3876 ColumnCollectionMixin._set_parent(self, table)
3878 if self.table is not None and table is not self.table:
3879 raise exc.ArgumentError(
3880 "Index '%s' is against table '%s', and "
3881 "cannot be associated with table '%s'."
3882 % (self.name, self.table.description, table.description)
3883 )
3884 self.table = table
3885 table.indexes.add(self)
3887 expressions = self.expressions
3888 col_expressions = self._col_expressions(table)
3889 assert len(expressions) == len(col_expressions)
3890 self.expressions = [
3891 expr if isinstance(expr, ClauseElement) else colexpr
3892 for expr, colexpr in zip(expressions, col_expressions)
3893 ]
3895 @property
3896 def bind(self):
3897 """Return the connectable associated with this Index."""
3899 return self.table.bind
3901 def create(self, bind=None):
3902 """Issue a ``CREATE`` statement for this
3903 :class:`.Index`, using the given :class:`.Connectable`
3904 for connectivity.
3906 .. seealso::
3908 :meth:`_schema.MetaData.create_all`.
3910 """
3911 if bind is None:
3912 bind = _bind_or_error(self)
3913 bind._run_visitor(ddl.SchemaGenerator, self)
3914 return self
3916 def drop(self, bind=None):
3917 """Issue a ``DROP`` statement for this
3918 :class:`.Index`, using the given :class:`.Connectable`
3919 for connectivity.
3921 .. seealso::
3923 :meth:`_schema.MetaData.drop_all`.
3925 """
3926 if bind is None:
3927 bind = _bind_or_error(self)
3928 bind._run_visitor(ddl.SchemaDropper, self)
3930 def __repr__(self):
3931 return "Index(%s)" % (
3932 ", ".join(
3933 [repr(self.name)]
3934 + [repr(e) for e in self.expressions]
3935 + (self.unique and ["unique=True"] or [])
3936 )
3937 )
3940DEFAULT_NAMING_CONVENTION = util.immutabledict({"ix": "ix_%(column_0_label)s"})
3943class MetaData(SchemaItem):
3944 """A collection of :class:`_schema.Table`
3945 objects and their associated schema
3946 constructs.
3948 Holds a collection of :class:`_schema.Table` objects as well as
3949 an optional binding to an :class:`_engine.Engine` or
3950 :class:`_engine.Connection`. If bound, the :class:`_schema.Table` objects
3951 in the collection and their columns may participate in implicit SQL
3952 execution.
3954 The :class:`_schema.Table` objects themselves are stored in the
3955 :attr:`_schema.MetaData.tables` dictionary.
3957 :class:`_schema.MetaData` is a thread-safe object for read operations.
3958 Construction of new tables within a single :class:`_schema.MetaData`
3959 object,
3960 either explicitly or via reflection, may not be completely thread-safe.
3962 .. seealso::
3964 :ref:`metadata_describing` - Introduction to database metadata
3966 """
3968 __visit_name__ = "metadata"
3970 @util.deprecated_params(
3971 reflect=(
3972 "0.8",
3973 "The :paramref:`_schema.MetaData.reflect` "
3974 "flag is deprecated and will "
3975 "be removed in a future release. Please use the "
3976 ":meth:`_schema.MetaData.reflect` method.",
3977 )
3978 )
3979 def __init__(
3980 self,
3981 bind=None,
3982 reflect=False,
3983 schema=None,
3984 quote_schema=None,
3985 naming_convention=None,
3986 info=None,
3987 ):
3988 """Create a new MetaData object.
3990 :param bind:
3991 An Engine or Connection to bind to. May also be a string or URL
3992 instance, these are passed to create_engine() and this MetaData will
3993 be bound to the resulting engine.
3995 :param reflect:
3996 Optional, automatically load all tables from the bound database.
3997 Defaults to False. ``bind`` is required when this option is set.
3999 :param schema:
4000 The default schema to use for the :class:`_schema.Table`,
4001 :class:`.Sequence`, and potentially other objects associated with
4002 this :class:`_schema.MetaData`. Defaults to ``None``.
4004 When this value is set, any :class:`_schema.Table` or
4005 :class:`.Sequence`
4006 which specifies ``None`` for the schema parameter will instead
4007 have this schema name defined. To build a :class:`_schema.Table`
4008 or :class:`.Sequence` that still has ``None`` for the schema
4009 even when this parameter is present, use the :attr:`.BLANK_SCHEMA`
4010 symbol.
4012 .. note::
4014 As referred above, the :paramref:`_schema.MetaData.schema`
4015 parameter
4016 only refers to the **default value** that will be applied to
4017 the :paramref:`_schema.Table.schema` parameter of an incoming
4018 :class:`_schema.Table` object. It does not refer to how the
4019 :class:`_schema.Table` is catalogued within the
4020 :class:`_schema.MetaData`,
4021 which remains consistent vs. a :class:`_schema.MetaData`
4022 collection
4023 that does not define this parameter. The
4024 :class:`_schema.Table`
4025 within the :class:`_schema.MetaData`
4026 will still be keyed based on its
4027 schema-qualified name, e.g.
4028 ``my_metadata.tables["some_schema.my_table"]``.
4030 The current behavior of the :class:`_schema.ForeignKey`
4031 object is to
4032 circumvent this restriction, where it can locate a table given
4033 the table name alone, where the schema will be assumed to be
4034 present from this value as specified on the owning
4035 :class:`_schema.MetaData` collection. However,
4036 this implies that a
4037 table qualified with BLANK_SCHEMA cannot currently be referred
4038 to by string name from :class:`_schema.ForeignKey`.
4039 Other parts of
4040 SQLAlchemy such as Declarative may not have similar behaviors
4041 built in, however may do so in a future release, along with a
4042 consistent method of referring to a table in BLANK_SCHEMA.
4045 .. seealso::
4047 :paramref:`_schema.Table.schema`
4049 :paramref:`.Sequence.schema`
4051 :param quote_schema:
4052 Sets the ``quote_schema`` flag for those :class:`_schema.Table`,
4053 :class:`.Sequence`, and other objects which make usage of the
4054 local ``schema`` name.
4056 :param info: Optional data dictionary which will be populated into the
4057 :attr:`.SchemaItem.info` attribute of this object.
4059 .. versionadded:: 1.0.0
4061 :param naming_convention: a dictionary referring to values which
4062 will establish default naming conventions for :class:`.Constraint`
4063 and :class:`.Index` objects, for those objects which are not given
4064 a name explicitly.
4066 The keys of this dictionary may be:
4068 * a constraint or Index class, e.g. the :class:`.UniqueConstraint`,
4069 :class:`_schema.ForeignKeyConstraint` class, the :class:`.Index`
4070 class
4072 * a string mnemonic for one of the known constraint classes;
4073 ``"fk"``, ``"pk"``, ``"ix"``, ``"ck"``, ``"uq"`` for foreign key,
4074 primary key, index, check, and unique constraint, respectively.
4076 * the string name of a user-defined "token" that can be used
4077 to define new naming tokens.
4079 The values associated with each "constraint class" or "constraint
4080 mnemonic" key are string naming templates, such as
4081 ``"uq_%(table_name)s_%(column_0_name)s"``,
4082 which describe how the name should be composed. The values
4083 associated with user-defined "token" keys should be callables of the
4084 form ``fn(constraint, table)``, which accepts the constraint/index
4085 object and :class:`_schema.Table` as arguments, returning a string
4086 result.
4088 The built-in names are as follows, some of which may only be
4089 available for certain types of constraint:
4091 * ``%(table_name)s`` - the name of the :class:`_schema.Table`
4092 object
4093 associated with the constraint.
4095 * ``%(referred_table_name)s`` - the name of the
4096 :class:`_schema.Table`
4097 object associated with the referencing target of a
4098 :class:`_schema.ForeignKeyConstraint`.
4100 * ``%(column_0_name)s`` - the name of the :class:`_schema.Column`
4101 at
4102 index position "0" within the constraint.
4104 * ``%(column_0N_name)s`` - the name of all :class:`_schema.Column`
4105 objects in order within the constraint, joined without a
4106 separator.
4108 * ``%(column_0_N_name)s`` - the name of all
4109 :class:`_schema.Column`
4110 objects in order within the constraint, joined with an
4111 underscore as a separator.
4113 * ``%(column_0_label)s``, ``%(column_0N_label)s``,
4114 ``%(column_0_N_label)s`` - the label of either the zeroth
4115 :class:`_schema.Column` or all :class:`.Columns`, separated with
4116 or without an underscore
4118 * ``%(column_0_key)s``, ``%(column_0N_key)s``,
4119 ``%(column_0_N_key)s`` - the key of either the zeroth
4120 :class:`_schema.Column` or all :class:`.Columns`, separated with
4121 or without an underscore
4123 * ``%(referred_column_0_name)s``, ``%(referred_column_0N_name)s``
4124 ``%(referred_column_0_N_name)s``, ``%(referred_column_0_key)s``,
4125 ``%(referred_column_0N_key)s``, ... column tokens which
4126 render the names/keys/labels of columns that are referenced
4127 by a :class:`_schema.ForeignKeyConstraint`.
4129 * ``%(constraint_name)s`` - a special key that refers to the
4130 existing name given to the constraint. When this key is
4131 present, the :class:`.Constraint` object's existing name will be
4132 replaced with one that is composed from template string that
4133 uses this token. When this token is present, it is required that
4134 the :class:`.Constraint` is given an explicit name ahead of time.
4136 * user-defined: any additional token may be implemented by passing
4137 it along with a ``fn(constraint, table)`` callable to the
4138 naming_convention dictionary.
4140 .. versionadded:: 1.3.0 - added new ``%(column_0N_name)s``,
4141 ``%(column_0_N_name)s``, and related tokens that produce
4142 concatenations of names, keys, or labels for all columns referred
4143 to by a given constraint.
4145 .. seealso::
4147 :ref:`constraint_naming_conventions` - for detailed usage
4148 examples.
4150 """
4151 self.tables = util.immutabledict()
4152 self.schema = quoted_name(schema, quote_schema)
4153 self.naming_convention = (
4154 naming_convention
4155 if naming_convention
4156 else DEFAULT_NAMING_CONVENTION
4157 )
4158 if info:
4159 self.info = info
4160 self._schemas = set()
4161 self._sequences = {}
4162 self._fk_memos = collections.defaultdict(list)
4164 self.bind = bind
4165 if reflect:
4166 if not bind:
4167 raise exc.ArgumentError(
4168 "A bind must be supplied in conjunction "
4169 "with reflect=True"
4170 )
4171 self.reflect()
4173 tables = None
4174 """A dictionary of :class:`_schema.Table`
4175 objects keyed to their name or "table key".
4177 The exact key is that determined by the :attr:`_schema.Table.key`
4178 attribute;
4179 for a table with no :attr:`_schema.Table.schema` attribute,
4180 this is the same
4181 as :attr:`_schema.Table.name`. For a table with a schema,
4182 it is typically of the
4183 form ``schemaname.tablename``.
4185 .. seealso::
4187 :attr:`_schema.MetaData.sorted_tables`
4189 """
4191 def __repr__(self):
4192 return "MetaData(bind=%r)" % self.bind
4194 def __contains__(self, table_or_key):
4195 if not isinstance(table_or_key, util.string_types):
4196 table_or_key = table_or_key.key
4197 return table_or_key in self.tables
4199 def _add_table(self, name, schema, table):
4200 key = _get_table_key(name, schema)
4201 dict.__setitem__(self.tables, key, table)
4202 if schema:
4203 self._schemas.add(schema)
4205 def _remove_table(self, name, schema):
4206 key = _get_table_key(name, schema)
4207 removed = dict.pop(self.tables, key, None)
4208 if removed is not None:
4209 for fk in removed.foreign_keys:
4210 fk._remove_from_metadata(self)
4211 if self._schemas:
4212 self._schemas = set(
4213 [
4214 t.schema
4215 for t in self.tables.values()
4216 if t.schema is not None
4217 ]
4218 )
4220 def __getstate__(self):
4221 return {
4222 "tables": self.tables,
4223 "schema": self.schema,
4224 "schemas": self._schemas,
4225 "sequences": self._sequences,
4226 "fk_memos": self._fk_memos,
4227 "naming_convention": self.naming_convention,
4228 }
4230 def __setstate__(self, state):
4231 self.tables = state["tables"]
4232 self.schema = state["schema"]
4233 self.naming_convention = state["naming_convention"]
4234 self._bind = None
4235 self._sequences = state["sequences"]
4236 self._schemas = state["schemas"]
4237 self._fk_memos = state["fk_memos"]
4239 def is_bound(self):
4240 """True if this MetaData is bound to an Engine or Connection."""
4242 return self._bind is not None
4244 def bind(self):
4245 """An :class:`_engine.Engine` or :class:`_engine.Connection`
4246 to which this
4247 :class:`_schema.MetaData` is bound.
4249 Typically, a :class:`_engine.Engine` is assigned to this attribute
4250 so that "implicit execution" may be used, or alternatively
4251 as a means of providing engine binding information to an
4252 ORM :class:`.Session` object::
4254 engine = create_engine("someurl://")
4255 metadata.bind = engine
4257 .. seealso::
4259 :ref:`dbengine_implicit` - background on "bound metadata"
4261 """
4262 return self._bind
4264 @util.dependencies("sqlalchemy.engine.url")
4265 def _bind_to(self, url, bind):
4266 """Bind this MetaData to an Engine, Connection, string or URL."""
4268 if isinstance(bind, util.string_types + (url.URL,)):
4269 self._bind = sqlalchemy.create_engine(bind)
4270 else:
4271 self._bind = bind
4273 bind = property(bind, _bind_to)
4275 def clear(self):
4276 """Clear all Table objects from this MetaData."""
4278 dict.clear(self.tables)
4279 self._schemas.clear()
4280 self._fk_memos.clear()
4282 def remove(self, table):
4283 """Remove the given Table object from this MetaData."""
4285 self._remove_table(table.name, table.schema)
4287 @property
4288 def sorted_tables(self):
4289 """Returns a list of :class:`_schema.Table` objects sorted in order of
4290 foreign key dependency.
4292 The sorting will place :class:`_schema.Table`
4293 objects that have dependencies
4294 first, before the dependencies themselves, representing the
4295 order in which they can be created. To get the order in which
4296 the tables would be dropped, use the ``reversed()`` Python built-in.
4298 .. warning::
4300 The :attr:`.MetaData.sorted_tables` attribute cannot by itself
4301 accommodate automatic resolution of dependency cycles between
4302 tables, which are usually caused by mutually dependent foreign key
4303 constraints. When these cycles are detected, the foreign keys
4304 of these tables are omitted from consideration in the sort.
4305 A warning is emitted when this condition occurs, which will be an
4306 exception raise in a future release. Tables which are not part
4307 of the cycle will still be returned in dependency order.
4309 To resolve these cycles, the
4310 :paramref:`_schema.ForeignKeyConstraint.use_alter` parameter may be
4311 applied to those constraints which create a cycle. Alternatively,
4312 the :func:`_schema.sort_tables_and_constraints` function will
4313 automatically return foreign key constraints in a separate
4314 collection when cycles are detected so that they may be applied
4315 to a schema separately.
4317 .. versionchanged:: 1.3.17 - a warning is emitted when
4318 :attr:`.MetaData.sorted_tables` cannot perform a proper sort
4319 due to cyclical dependencies. This will be an exception in a
4320 future release. Additionally, the sort will continue to return
4321 other tables not involved in the cycle in dependency order which
4322 was not the case previously.
4324 .. seealso::
4326 :func:`_schema.sort_tables`
4328 :func:`_schema.sort_tables_and_constraints`
4330 :attr:`_schema.MetaData.tables`
4332 :meth:`_reflection.Inspector.get_table_names`
4334 :meth:`_reflection.Inspector.get_sorted_table_and_fkc_names`
4337 """
4338 return ddl.sort_tables(
4339 sorted(self.tables.values(), key=lambda t: t.key)
4340 )
4342 def reflect(
4343 self,
4344 bind=None,
4345 schema=None,
4346 views=False,
4347 only=None,
4348 extend_existing=False,
4349 autoload_replace=True,
4350 resolve_fks=True,
4351 **dialect_kwargs
4352 ):
4353 r"""Load all available table definitions from the database.
4355 Automatically creates ``Table`` entries in this ``MetaData`` for any
4356 table available in the database but not yet present in the
4357 ``MetaData``. May be called multiple times to pick up tables recently
4358 added to the database, however no special action is taken if a table
4359 in this ``MetaData`` no longer exists in the database.
4361 :param bind:
4362 A :class:`.Connectable` used to access the database; if None, uses
4363 the existing bind on this ``MetaData``, if any.
4365 :param schema:
4366 Optional, query and reflect tables from an alternate schema.
4367 If None, the schema associated with this :class:`_schema.MetaData`
4368 is used, if any.
4370 :param views:
4371 If True, also reflect views.
4373 :param only:
4374 Optional. Load only a sub-set of available named tables. May be
4375 specified as a sequence of names or a callable.
4377 If a sequence of names is provided, only those tables will be
4378 reflected. An error is raised if a table is requested but not
4379 available. Named tables already present in this ``MetaData`` are
4380 ignored.
4382 If a callable is provided, it will be used as a boolean predicate to
4383 filter the list of potential table names. The callable is called
4384 with a table name and this ``MetaData`` instance as positional
4385 arguments and should return a true value for any table to reflect.
4387 :param extend_existing: Passed along to each :class:`_schema.Table` as
4388 :paramref:`_schema.Table.extend_existing`.
4390 .. versionadded:: 0.9.1
4392 :param autoload_replace: Passed along to each :class:`_schema.Table`
4393 as
4394 :paramref:`_schema.Table.autoload_replace`.
4396 .. versionadded:: 0.9.1
4398 :param resolve_fks: if True, reflect :class:`_schema.Table`
4399 objects linked
4400 to :class:`_schema.ForeignKey` objects located in each
4401 :class:`_schema.Table`.
4402 For :meth:`_schema.MetaData.reflect`,
4403 this has the effect of reflecting
4404 related tables that might otherwise not be in the list of tables
4405 being reflected, for example if the referenced table is in a
4406 different schema or is omitted via the
4407 :paramref:`.MetaData.reflect.only` parameter. When False,
4408 :class:`_schema.ForeignKey` objects are not followed to the
4409 :class:`_schema.Table`
4410 in which they link, however if the related table is also part of the
4411 list of tables that would be reflected in any case, the
4412 :class:`_schema.ForeignKey` object will still resolve to its related
4413 :class:`_schema.Table` after the :meth:`_schema.MetaData.reflect`
4414 operation is
4415 complete. Defaults to True.
4417 .. versionadded:: 1.3.0
4419 .. seealso::
4421 :paramref:`_schema.Table.resolve_fks`
4423 :param \**dialect_kwargs: Additional keyword arguments not mentioned
4424 above are dialect specific, and passed in the form
4425 ``<dialectname>_<argname>``. See the documentation regarding an
4426 individual dialect at :ref:`dialect_toplevel` for detail on
4427 documented arguments.
4429 .. versionadded:: 0.9.2 - Added
4430 :paramref:`.MetaData.reflect.**dialect_kwargs` to support
4431 dialect-level reflection options for all :class:`_schema.Table`
4432 objects reflected.
4434 """
4435 if bind is None:
4436 bind = _bind_or_error(self)
4438 with bind.connect() as conn:
4440 reflect_opts = {
4441 "autoload": True,
4442 "autoload_with": conn,
4443 "extend_existing": extend_existing,
4444 "autoload_replace": autoload_replace,
4445 "resolve_fks": resolve_fks,
4446 "_extend_on": set(),
4447 }
4449 reflect_opts.update(dialect_kwargs)
4451 if schema is None:
4452 schema = self.schema
4454 if schema is not None:
4455 reflect_opts["schema"] = schema
4457 available = util.OrderedSet(
4458 bind.engine.table_names(schema, connection=conn)
4459 )
4460 if views:
4461 available.update(bind.dialect.get_view_names(conn, schema))
4463 if schema is not None:
4464 available_w_schema = util.OrderedSet(
4465 ["%s.%s" % (schema, name) for name in available]
4466 )
4467 else:
4468 available_w_schema = available
4470 current = set(self.tables)
4472 if only is None:
4473 load = [
4474 name
4475 for name, schname in zip(available, available_w_schema)
4476 if extend_existing or schname not in current
4477 ]
4478 elif util.callable(only):
4479 load = [
4480 name
4481 for name, schname in zip(available, available_w_schema)
4482 if (extend_existing or schname not in current)
4483 and only(name, self)
4484 ]
4485 else:
4486 missing = [name for name in only if name not in available]
4487 if missing:
4488 s = schema and (" schema '%s'" % schema) or ""
4489 raise exc.InvalidRequestError(
4490 "Could not reflect: requested table(s) not available "
4491 "in %r%s: (%s)" % (bind.engine, s, ", ".join(missing))
4492 )
4493 load = [
4494 name
4495 for name in only
4496 if extend_existing or name not in current
4497 ]
4499 for name in load:
4500 try:
4501 Table(name, self, **reflect_opts)
4502 except exc.UnreflectableTableError as uerr:
4503 util.warn("Skipping table %s: %s" % (name, uerr))
4505 @util.deprecated(
4506 "0.7",
4507 "the :meth:`_schema.MetaData.append_ddl_listener` "
4508 "method is deprecated and "
4509 "will be removed in a future release. Please refer to "
4510 ":class:`.DDLEvents`.",
4511 )
4512 def append_ddl_listener(self, event_name, listener):
4513 """Append a DDL event listener to this ``MetaData``.
4516 """
4518 def adapt_listener(target, connection, **kw):
4519 tables = kw["tables"]
4520 listener(event, target, connection, tables=tables)
4522 event.listen(self, "" + event_name.replace("-", "_"), adapt_listener)
4524 def create_all(self, bind=None, tables=None, checkfirst=True):
4525 """Create all tables stored in this metadata.
4527 Conditional by default, will not attempt to recreate tables already
4528 present in the target database.
4530 :param bind:
4531 A :class:`.Connectable` used to access the
4532 database; if None, uses the existing bind on this ``MetaData``, if
4533 any.
4535 :param tables:
4536 Optional list of ``Table`` objects, which is a subset of the total
4537 tables in the ``MetaData`` (others are ignored).
4539 :param checkfirst:
4540 Defaults to True, don't issue CREATEs for tables already present
4541 in the target database.
4543 """
4544 if bind is None:
4545 bind = _bind_or_error(self)
4546 bind._run_visitor(
4547 ddl.SchemaGenerator, self, checkfirst=checkfirst, tables=tables
4548 )
4550 def drop_all(self, bind=None, tables=None, checkfirst=True):
4551 """Drop all tables stored in this metadata.
4553 Conditional by default, will not attempt to drop tables not present in
4554 the target database.
4556 :param bind:
4557 A :class:`.Connectable` used to access the
4558 database; if None, uses the existing bind on this ``MetaData``, if
4559 any.
4561 :param tables:
4562 Optional list of ``Table`` objects, which is a subset of the
4563 total tables in the ``MetaData`` (others are ignored).
4565 :param checkfirst:
4566 Defaults to True, only issue DROPs for tables confirmed to be
4567 present in the target database.
4569 """
4570 if bind is None:
4571 bind = _bind_or_error(self)
4572 bind._run_visitor(
4573 ddl.SchemaDropper, self, checkfirst=checkfirst, tables=tables
4574 )
4577class ThreadLocalMetaData(MetaData):
4578 """A MetaData variant that presents a different ``bind`` in every thread.
4580 Makes the ``bind`` property of the MetaData a thread-local value, allowing
4581 this collection of tables to be bound to different ``Engine``
4582 implementations or connections in each thread.
4584 The ThreadLocalMetaData starts off bound to None in each thread. Binds
4585 must be made explicitly by assigning to the ``bind`` property or using
4586 ``connect()``. You can also re-bind dynamically multiple times per
4587 thread, just like a regular ``MetaData``.
4589 """
4591 __visit_name__ = "metadata"
4593 def __init__(self):
4594 """Construct a ThreadLocalMetaData."""
4596 self.context = util.threading.local()
4597 self.__engines = {}
4598 super(ThreadLocalMetaData, self).__init__()
4600 def bind(self):
4601 """The bound Engine or Connection for this thread.
4603 This property may be assigned an Engine or Connection, or assigned a
4604 string or URL to automatically create a basic Engine for this bind
4605 with ``create_engine()``."""
4607 return getattr(self.context, "_engine", None)
4609 @util.dependencies("sqlalchemy.engine.url")
4610 def _bind_to(self, url, bind):
4611 """Bind to a Connectable in the caller's thread."""
4613 if isinstance(bind, util.string_types + (url.URL,)):
4614 try:
4615 self.context._engine = self.__engines[bind]
4616 except KeyError:
4617 e = sqlalchemy.create_engine(bind)
4618 self.__engines[bind] = e
4619 self.context._engine = e
4620 else:
4621 # TODO: this is squirrely. we shouldn't have to hold onto engines
4622 # in a case like this
4623 if bind not in self.__engines:
4624 self.__engines[bind] = bind
4625 self.context._engine = bind
4627 bind = property(bind, _bind_to)
4629 def is_bound(self):
4630 """True if there is a bind for this thread."""
4631 return (
4632 hasattr(self.context, "_engine")
4633 and self.context._engine is not None
4634 )
4636 def dispose(self):
4637 """Dispose all bound engines, in all thread contexts."""
4639 for e in self.__engines.values():
4640 if hasattr(e, "dispose"):
4641 e.dispose()
4644class _SchemaTranslateMap(object):
4645 """Provide translation of schema names based on a mapping.
4647 Also provides helpers for producing cache keys and optimized
4648 access when no mapping is present.
4650 Used by the :paramref:`.Connection.execution_options.schema_translate_map`
4651 feature.
4653 .. versionadded:: 1.1
4656 """
4658 __slots__ = "map_", "__call__", "hash_key", "is_default"
4660 _default_schema_getter = operator.attrgetter("schema")
4662 def __init__(self, map_):
4663 self.map_ = map_
4664 if map_ is not None:
4666 def schema_for_object(obj):
4667 effective_schema = self._default_schema_getter(obj)
4668 effective_schema = obj._translate_schema(
4669 effective_schema, map_
4670 )
4671 return effective_schema
4673 self.__call__ = schema_for_object
4674 self.hash_key = ";".join(
4675 "%s=%s" % (k, map_[k]) for k in sorted(map_, key=str)
4676 )
4677 self.is_default = False
4678 else:
4679 self.hash_key = 0
4680 self.__call__ = self._default_schema_getter
4681 self.is_default = True
4683 @classmethod
4684 def _schema_getter(cls, map_):
4685 if map_ is None:
4686 return _default_schema_map
4687 elif isinstance(map_, _SchemaTranslateMap):
4688 return map_
4689 else:
4690 return _SchemaTranslateMap(map_)
4693_default_schema_map = _SchemaTranslateMap(None)
4694_schema_getter = _SchemaTranslateMap._schema_getter
4697class Computed(FetchedValue, SchemaItem):
4698 """Defines a generated column, i.e. "GENERATED ALWAYS AS" syntax.
4700 The :class:`.Computed` construct is an inline construct added to the
4701 argument list of a :class:`_schema.Column` object::
4703 from sqlalchemy import Computed
4705 Table('square', meta,
4706 Column('side', Float, nullable=False),
4707 Column('area', Float, Computed('side * side'))
4708 )
4710 See the linked documentation below for complete details.
4712 .. versionadded:: 1.3.11
4714 .. seealso::
4716 :ref:`computed_ddl`
4718 """
4720 __visit_name__ = "computed_column"
4722 @_document_text_coercion(
4723 "sqltext", ":class:`.Computed`", ":paramref:`.Computed.sqltext`"
4724 )
4725 def __init__(self, sqltext, persisted=None):
4726 """Construct a GENERATED ALWAYS AS DDL construct to accompany a
4727 :class:`_schema.Column`.
4729 :param sqltext:
4730 A string containing the column generation expression, which will be
4731 used verbatim, or a SQL expression construct, such as a
4732 :func:`_expression.text`
4733 object. If given as a string, the object is converted to a
4734 :func:`_expression.text` object.
4736 :param persisted:
4737 Optional, controls how this column should be persisted by the
4738 database. Possible values are:
4740 * None, the default, it will use the default persistence defined
4741 by the database.
4742 * True, will render ``GENERATED ALWAYS AS ... STORED``, or the
4743 equivalent for the target database if supported
4744 * False, will render ``GENERATED ALWAYS AS ... VIRTUAL``, or the
4745 equivalent for the target database if supported.
4747 Specifying ``True`` or ``False`` may raise an error when the DDL
4748 is emitted to the target database if the databse does not support
4749 that persistence option. Leaving this parameter at its default
4750 of ``None`` is guaranteed to succeed for all databases that support
4751 ``GENERATED ALWAYS AS``.
4753 """
4754 self.sqltext = _literal_as_text(sqltext, allow_coercion_to_text=True)
4755 self.persisted = persisted
4756 self.column = None
4758 def _set_parent(self, parent):
4759 if not isinstance(
4760 parent.server_default, (type(None), Computed)
4761 ) or not isinstance(parent.server_onupdate, (type(None), Computed)):
4762 raise exc.ArgumentError(
4763 "A generated column cannot specify a server_default or a "
4764 "server_onupdate argument"
4765 )
4766 self.column = parent
4767 parent.computed = self
4768 self.column.server_onupdate = self
4769 self.column.server_default = self
4771 def _as_for_update(self, for_update):
4772 return self
4774 def copy(self, target_table=None, **kw):
4775 if target_table is not None:
4776 sqltext = _copy_expression(self.sqltext, self.table, target_table)
4777 else:
4778 sqltext = self.sqltext
4779 g = Computed(sqltext, persisted=self.persisted)
4781 return self._schema_item_copy(g)