Shortcuts on this page
r m x toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1# sql/dml.py
2# Copyright (C) 2009-2021 the SQLAlchemy authors and contributors
3# <see AUTHORS file>
4#
5# This module is part of SQLAlchemy and is released under
6# the MIT License: http://www.opensource.org/licenses/mit-license.php
7"""
8Provide :class:`_expression.Insert`, :class:`_expression.Update` and
9:class:`_expression.Delete`.
11"""
12from . import util as sql_util
13from .base import _from_objects
14from .base import _generative
15from .base import DialectKWArgs
16from .base import Executable
17from .elements import _clone
18from .elements import _column_as_key
19from .elements import _literal_as_text
20from .elements import and_
21from .elements import ClauseElement
22from .elements import Null
23from .selectable import _interpret_as_from
24from .selectable import _interpret_as_select
25from .selectable import HasCTE
26from .selectable import HasPrefixes
27from .. import exc
28from .. import util
31class UpdateBase(
32 HasCTE, DialectKWArgs, HasPrefixes, Executable, ClauseElement
33):
34 """Form the base for ``INSERT``, ``UPDATE``, and ``DELETE`` statements."""
36 __visit_name__ = "update_base"
38 _execution_options = Executable._execution_options.union(
39 {"autocommit": True}
40 )
41 _hints = util.immutabledict()
42 _parameter_ordering = None
43 _prefixes = ()
44 named_with_column = False
45 _return_defaults = None
47 def _process_colparams(self, parameters):
48 def process_single(p):
49 if isinstance(p, (list, tuple)):
50 return dict((c.key, pval) for c, pval in zip(self.table.c, p))
51 else:
52 return p
54 if self._preserve_parameter_order and parameters is not None:
55 if not isinstance(parameters, list) or (
56 parameters and not isinstance(parameters[0], tuple)
57 ):
58 raise ValueError(
59 "When preserve_parameter_order is True, "
60 "values() only accepts a list of 2-tuples"
61 )
62 self._parameter_ordering = [key for key, value in parameters]
64 return dict(parameters), False
66 if (
67 isinstance(parameters, (list, tuple))
68 and parameters
69 and isinstance(parameters[0], (list, tuple, dict))
70 ):
72 if not self._supports_multi_parameters:
73 raise exc.InvalidRequestError(
74 "This construct does not support "
75 "multiple parameter sets."
76 )
78 return [process_single(p) for p in parameters], True
79 else:
80 return process_single(parameters), False
82 def params(self, *arg, **kw):
83 """Set the parameters for the statement.
85 This method raises ``NotImplementedError`` on the base class,
86 and is overridden by :class:`.ValuesBase` to provide the
87 SET/VALUES clause of UPDATE and INSERT.
89 """
90 raise NotImplementedError(
91 "params() is not supported for INSERT/UPDATE/DELETE statements."
92 " To set the values for an INSERT or UPDATE statement, use"
93 " stmt.values(**parameters)."
94 )
96 def bind(self):
97 """Return a 'bind' linked to this :class:`.UpdateBase`
98 or a :class:`_schema.Table` associated with it.
100 """
101 return self._bind or self.table.bind
103 def _set_bind(self, bind):
104 self._bind = bind
106 bind = property(bind, _set_bind)
108 @_generative
109 def returning(self, *cols):
110 r"""Add a :term:`RETURNING` or equivalent clause to this statement.
112 e.g.::
114 stmt = table.update().\
115 where(table.c.data == 'value').\
116 values(status='X').\
117 returning(table.c.server_flag,
118 table.c.updated_timestamp)
120 for server_flag, updated_timestamp in connection.execute(stmt):
121 print(server_flag, updated_timestamp)
123 The given collection of column expressions should be derived from the
124 table that is the target of the INSERT, UPDATE, or DELETE. While
125 :class:`_schema.Column` objects are typical, the elements can also be
126 expressions::
128 stmt = table.insert().returning(
129 (table.c.first_name + " " + table.c.last_name).
130 label('fullname'))
132 Upon compilation, a RETURNING clause, or database equivalent,
133 will be rendered within the statement. For INSERT and UPDATE,
134 the values are the newly inserted/updated values. For DELETE,
135 the values are those of the rows which were deleted.
137 Upon execution, the values of the columns to be returned are made
138 available via the result set and can be iterated using
139 :meth:`_engine.ResultProxy.fetchone` and similar.
140 For DBAPIs which do not
141 natively support returning values (i.e. cx_oracle), SQLAlchemy will
142 approximate this behavior at the result level so that a reasonable
143 amount of behavioral neutrality is provided.
145 Note that not all databases/DBAPIs
146 support RETURNING. For those backends with no support,
147 an exception is raised upon compilation and/or execution.
148 For those who do support it, the functionality across backends
149 varies greatly, including restrictions on executemany()
150 and other statements which return multiple rows. Please
151 read the documentation notes for the database in use in
152 order to determine the availability of RETURNING.
154 .. seealso::
156 :meth:`.ValuesBase.return_defaults` - an alternative method tailored
157 towards efficient fetching of server-side defaults and triggers
158 for single-row INSERTs or UPDATEs.
161 """
162 if self._return_defaults:
163 raise exc.InvalidRequestError(
164 "return_defaults() is already configured on this statement"
165 )
166 if self._returning:
167 util.warn(
168 "The returning() method does not currently support multiple "
169 "additive calls. The existing RETURNING clause being "
170 "replaced by new columns."
171 )
172 self._returning = cols
174 @_generative
175 def with_hint(self, text, selectable=None, dialect_name="*"):
176 """Add a table hint for a single table to this
177 INSERT/UPDATE/DELETE statement.
179 .. note::
181 :meth:`.UpdateBase.with_hint` currently applies only to
182 Microsoft SQL Server. For MySQL INSERT/UPDATE/DELETE hints, use
183 :meth:`.UpdateBase.prefix_with`.
185 The text of the hint is rendered in the appropriate
186 location for the database backend in use, relative
187 to the :class:`_schema.Table` that is the subject of this
188 statement, or optionally to that of the given
189 :class:`_schema.Table` passed as the ``selectable`` argument.
191 The ``dialect_name`` option will limit the rendering of a particular
192 hint to a particular backend. Such as, to add a hint
193 that only takes effect for SQL Server::
195 mytable.insert().with_hint("WITH (PAGLOCK)", dialect_name="mssql")
197 :param text: Text of the hint.
198 :param selectable: optional :class:`_schema.Table` that specifies
199 an element of the FROM clause within an UPDATE or DELETE
200 to be the subject of the hint - applies only to certain backends.
201 :param dialect_name: defaults to ``*``, if specified as the name
202 of a particular dialect, will apply these hints only when
203 that dialect is in use.
205 """
206 if selectable is None:
207 selectable = self.table
209 self._hints = self._hints.union({(selectable, dialect_name): text})
212class ValuesBase(UpdateBase):
213 """Supplies support for :meth:`.ValuesBase.values` to
214 INSERT and UPDATE constructs."""
216 __visit_name__ = "values_base"
218 _supports_multi_parameters = False
219 _has_multi_parameters = False
220 _preserve_parameter_order = False
221 select = None
222 _post_values_clause = None
224 def __init__(self, table, values, prefixes):
225 self.table = _interpret_as_from(table)
226 self.parameters, self._has_multi_parameters = self._process_colparams(
227 values
228 )
229 if prefixes:
230 self._setup_prefixes(prefixes)
232 @_generative
233 def values(self, *args, **kwargs):
234 r"""Specify a fixed VALUES clause for an INSERT statement, or the SET
235 clause for an UPDATE.
237 Note that the :class:`_expression.Insert` and
238 :class:`_expression.Update` constructs support
239 per-execution time formatting of the VALUES and/or SET clauses,
240 based on the arguments passed to :meth:`_engine.Connection.execute`.
241 However, the :meth:`.ValuesBase.values` method can be used to "fix" a
242 particular set of parameters into the statement.
244 Multiple calls to :meth:`.ValuesBase.values` will produce a new
245 construct, each one with the parameter list modified to include
246 the new parameters sent. In the typical case of a single
247 dictionary of parameters, the newly passed keys will replace
248 the same keys in the previous construct. In the case of a list-based
249 "multiple values" construct, each new list of values is extended
250 onto the existing list of values.
252 :param \**kwargs: key value pairs representing the string key
253 of a :class:`_schema.Column`
254 mapped to the value to be rendered into the
255 VALUES or SET clause::
257 users.insert().values(name="some name")
259 users.update().where(users.c.id==5).values(name="some name")
261 :param \*args: As an alternative to passing key/value parameters,
262 a dictionary, tuple, or list of dictionaries or tuples can be passed
263 as a single positional argument in order to form the VALUES or
264 SET clause of the statement. The forms that are accepted vary
265 based on whether this is an :class:`_expression.Insert` or an
266 :class:`_expression.Update` construct.
268 For either an :class:`_expression.Insert` or
269 :class:`_expression.Update`
270 construct, a single dictionary can be passed, which works the same as
271 that of the kwargs form::
273 users.insert().values({"name": "some name"})
275 users.update().values({"name": "some new name"})
277 Also for either form but more typically for the
278 :class:`_expression.Insert` construct, a tuple that contains an
279 entry for every column in the table is also accepted::
281 users.insert().values((5, "some name"))
283 The :class:`_expression.Insert` construct also supports being
284 passed a list of dictionaries or full-table-tuples, which on the
285 server will render the less common SQL syntax of "multiple values" -
286 this syntax is supported on backends such as SQLite, PostgreSQL,
287 MySQL, but not necessarily others::
289 users.insert().values([
290 {"name": "some name"},
291 {"name": "some other name"},
292 {"name": "yet another name"},
293 ])
295 The above form would render a multiple VALUES statement similar to::
297 INSERT INTO users (name) VALUES
298 (:name_1),
299 (:name_2),
300 (:name_3)
302 It is essential to note that **passing multiple values is
303 NOT the same as using traditional executemany() form**. The above
304 syntax is a **special** syntax not typically used. To emit an
305 INSERT statement against multiple rows, the normal method is
306 to pass a multiple values list to the
307 :meth:`_engine.Connection.execute`
308 method, which is supported by all database backends and is generally
309 more efficient for a very large number of parameters.
311 .. seealso::
313 :ref:`execute_multiple` - an introduction to
314 the traditional Core method of multiple parameter set
315 invocation for INSERTs and other statements.
317 .. versionchanged:: 1.0.0 an INSERT that uses a multiple-VALUES
318 clause, even a list of length one,
319 implies that the :paramref:`_expression.Insert.inline`
320 flag is set to
321 True, indicating that the statement will not attempt to fetch
322 the "last inserted primary key" or other defaults. The
323 statement deals with an arbitrary number of rows, so the
324 :attr:`_engine.ResultProxy.inserted_primary_key`
325 accessor does not
326 apply.
328 .. versionchanged:: 1.0.0 A multiple-VALUES INSERT now supports
329 columns with Python side default values and callables in the
330 same way as that of an "executemany" style of invocation; the
331 callable is invoked for each row. See :ref:`bug_3288`
332 for other details.
334 The :class:`_expression.Update`
335 construct supports a special form which is a
336 list of 2-tuples, which when provided must be passed in conjunction
337 with the
338 :paramref:`_expression.update.preserve_parameter_order`
339 parameter.
340 This form causes the UPDATE statement to render the SET clauses
341 using the order of parameters given to
342 :meth:`_expression.Update.values`, rather
343 than the ordering of columns given in the :class:`_schema.Table`.
345 .. versionadded:: 1.0.10 - added support for parameter-ordered
346 UPDATE statements via the
347 :paramref:`_expression.update.preserve_parameter_order`
348 flag.
350 .. seealso::
352 :ref:`updates_order_parameters` - full example of the
353 :paramref:`_expression.update.preserve_parameter_order`
354 flag
356 .. seealso::
358 :ref:`inserts_and_updates` - SQL Expression
359 Language Tutorial
361 :func:`_expression.insert` - produce an ``INSERT`` statement
363 :func:`_expression.update` - produce an ``UPDATE`` statement
365 """
366 if self.select is not None:
367 raise exc.InvalidRequestError(
368 "This construct already inserts from a SELECT"
369 )
370 if self._has_multi_parameters and kwargs:
371 raise exc.InvalidRequestError(
372 "This construct already has multiple parameter sets."
373 )
375 if args:
376 if len(args) > 1:
377 raise exc.ArgumentError(
378 "Only a single dictionary/tuple or list of "
379 "dictionaries/tuples is accepted positionally."
380 )
381 v = args[0]
382 else:
383 v = {}
385 if self.parameters is None:
386 (
387 self.parameters,
388 self._has_multi_parameters,
389 ) = self._process_colparams(v)
390 else:
391 if self._has_multi_parameters:
392 self.parameters = list(self.parameters)
393 p, self._has_multi_parameters = self._process_colparams(v)
394 if not self._has_multi_parameters:
395 raise exc.ArgumentError(
396 "Can't mix single-values and multiple values "
397 "formats in one statement"
398 )
400 self.parameters.extend(p)
401 else:
402 self.parameters = self.parameters.copy()
403 p, self._has_multi_parameters = self._process_colparams(v)
404 if self._has_multi_parameters:
405 raise exc.ArgumentError(
406 "Can't mix single-values and multiple values "
407 "formats in one statement"
408 )
409 self.parameters.update(p)
411 if kwargs:
412 if self._has_multi_parameters:
413 raise exc.ArgumentError(
414 "Can't pass kwargs and multiple parameter sets "
415 "simultaneously"
416 )
417 else:
418 self.parameters.update(kwargs)
420 @_generative
421 def return_defaults(self, *cols):
422 """Make use of a :term:`RETURNING` clause for the purpose
423 of fetching server-side expressions and defaults.
425 E.g.::
427 stmt = table.insert().values(data='newdata').return_defaults()
429 result = connection.execute(stmt)
431 server_created_at = result.returned_defaults['created_at']
433 When used against a backend that supports RETURNING, all column
434 values generated by SQL expression or server-side-default will be
435 added to any existing RETURNING clause, provided that
436 :meth:`.UpdateBase.returning` is not used simultaneously. The column
437 values will then be available on the result using the
438 :attr:`_engine.ResultProxy.returned_defaults` accessor as
439 a dictionary,
440 referring to values keyed to the :class:`_schema.Column`
441 object as well as
442 its ``.key``.
444 This method differs from :meth:`.UpdateBase.returning` in these ways:
446 1. :meth:`.ValuesBase.return_defaults` is only intended for use with
447 an INSERT or an UPDATE statement that matches exactly one row.
448 While the RETURNING construct in the general sense supports
449 multiple rows for a multi-row UPDATE or DELETE statement, or for
450 special cases of INSERT that return multiple rows (e.g. INSERT from
451 SELECT, multi-valued VALUES clause),
452 :meth:`.ValuesBase.return_defaults` is intended only for an
453 "ORM-style" single-row INSERT/UPDATE statement. The row returned
454 by the statement is also consumed implicitly when
455 :meth:`.ValuesBase.return_defaults` is used. By contrast,
456 :meth:`.UpdateBase.returning` leaves the RETURNING result-set
457 intact with a collection of any number of rows.
459 2. It is compatible with the existing logic to fetch auto-generated
460 primary key values, also known as "implicit returning". Backends
461 that support RETURNING will automatically make use of RETURNING in
462 order to fetch the value of newly generated primary keys; while the
463 :meth:`.UpdateBase.returning` method circumvents this behavior,
464 :meth:`.ValuesBase.return_defaults` leaves it intact.
466 3. It can be called against any backend. Backends that don't support
467 RETURNING will skip the usage of the feature, rather than raising
468 an exception. The return value of
469 :attr:`_engine.ResultProxy.returned_defaults` will be ``None``
471 :meth:`.ValuesBase.return_defaults` is used by the ORM to provide
472 an efficient implementation for the ``eager_defaults`` feature of
473 :func:`.mapper`.
475 :param cols: optional list of column key names or
476 :class:`_schema.Column`
477 objects. If omitted, all column expressions evaluated on the server
478 are added to the returning list.
480 .. versionadded:: 0.9.0
482 .. seealso::
484 :meth:`.UpdateBase.returning`
486 :attr:`_engine.ResultProxy.returned_defaults`
488 """
489 if self._returning:
490 raise exc.InvalidRequestError(
491 "RETURNING is already configured on this statement"
492 )
493 self._return_defaults = cols or True
496class Insert(ValuesBase):
497 """Represent an INSERT construct.
499 The :class:`_expression.Insert` object is created using the
500 :func:`_expression.insert()` function.
502 .. seealso::
504 :ref:`coretutorial_insert_expressions`
506 """
508 __visit_name__ = "insert"
510 _supports_multi_parameters = True
512 def __init__(
513 self,
514 table,
515 values=None,
516 inline=False,
517 bind=None,
518 prefixes=None,
519 returning=None,
520 return_defaults=False,
521 **dialect_kw
522 ):
523 """Construct an :class:`_expression.Insert` object.
525 Similar functionality is available via the
526 :meth:`_expression.TableClause.insert` method on
527 :class:`_schema.Table`.
529 :param table: :class:`_expression.TableClause`
530 which is the subject of the
531 insert.
533 :param values: collection of values to be inserted; see
534 :meth:`_expression.Insert.values`
535 for a description of allowed formats here.
536 Can be omitted entirely; a :class:`_expression.Insert` construct
537 will also dynamically render the VALUES clause at execution time
538 based on the parameters passed to :meth:`_engine.Connection.execute`.
540 :param inline: if True, no attempt will be made to retrieve the
541 SQL-generated default values to be provided within the statement;
542 in particular,
543 this allows SQL expressions to be rendered 'inline' within the
544 statement without the need to pre-execute them beforehand; for
545 backends that support "returning", this turns off the "implicit
546 returning" feature for the statement.
548 If both `values` and compile-time bind parameters are present, the
549 compile-time bind parameters override the information specified
550 within `values` on a per-key basis.
552 The keys within `values` can be either
553 :class:`~sqlalchemy.schema.Column` objects or their string
554 identifiers. Each key may reference one of:
556 * a literal data value (i.e. string, number, etc.);
557 * a Column object;
558 * a SELECT statement.
560 If a ``SELECT`` statement is specified which references this
561 ``INSERT`` statement's table, the statement will be correlated
562 against the ``INSERT`` statement.
564 .. seealso::
566 :ref:`coretutorial_insert_expressions` - SQL Expression Tutorial
568 :ref:`inserts_and_updates` - SQL Expression Tutorial
570 """
571 ValuesBase.__init__(self, table, values, prefixes)
572 self._bind = bind
573 self.select = self.select_names = None
574 self.include_insert_from_select_defaults = False
575 self.inline = inline
576 self._returning = returning
577 self._validate_dialect_kwargs(dialect_kw)
578 self._return_defaults = return_defaults
580 def get_children(self, **kwargs):
581 if self.select is not None:
582 return (self.select,)
583 else:
584 return ()
586 @_generative
587 def from_select(self, names, select, include_defaults=True):
588 """Return a new :class:`_expression.Insert` construct which represents
589 an ``INSERT...FROM SELECT`` statement.
591 e.g.::
593 sel = select([table1.c.a, table1.c.b]).where(table1.c.c > 5)
594 ins = table2.insert().from_select(['a', 'b'], sel)
596 :param names: a sequence of string column names or
597 :class:`_schema.Column`
598 objects representing the target columns.
599 :param select: a :func:`_expression.select` construct,
600 :class:`_expression.FromClause`
601 or other construct which resolves into a
602 :class:`_expression.FromClause`,
603 such as an ORM :class:`_query.Query` object, etc. The order of
604 columns returned from this FROM clause should correspond to the
605 order of columns sent as the ``names`` parameter; while this
606 is not checked before passing along to the database, the database
607 would normally raise an exception if these column lists don't
608 correspond.
609 :param include_defaults: if True, non-server default values and
610 SQL expressions as specified on :class:`_schema.Column` objects
611 (as documented in :ref:`metadata_defaults_toplevel`) not
612 otherwise specified in the list of names will be rendered
613 into the INSERT and SELECT statements, so that these values are also
614 included in the data to be inserted.
616 .. note:: A Python-side default that uses a Python callable function
617 will only be invoked **once** for the whole statement, and **not
618 per row**.
620 .. versionadded:: 1.0.0 - :meth:`_expression.Insert.from_select`
621 now renders
622 Python-side and SQL expression column defaults into the
623 SELECT statement for columns otherwise not included in the
624 list of column names.
626 .. versionchanged:: 1.0.0 an INSERT that uses FROM SELECT
627 implies that the :paramref:`_expression.insert.inline`
628 flag is set to
629 True, indicating that the statement will not attempt to fetch
630 the "last inserted primary key" or other defaults. The statement
631 deals with an arbitrary number of rows, so the
632 :attr:`_engine.ResultProxy.inserted_primary_key`
633 accessor does not apply.
635 """
636 if self.parameters:
637 raise exc.InvalidRequestError(
638 "This construct already inserts value expressions"
639 )
641 self.parameters, self._has_multi_parameters = self._process_colparams(
642 {_column_as_key(n): Null() for n in names}
643 )
645 self.select_names = names
646 self.inline = True
647 self.include_insert_from_select_defaults = include_defaults
648 self.select = _interpret_as_select(select)
650 def _copy_internals(self, clone=_clone, **kw):
651 # TODO: coverage
652 self.parameters = self.parameters.copy()
653 if self.select is not None:
654 self.select = _clone(self.select)
657class Update(ValuesBase):
658 """Represent an Update construct.
660 The :class:`_expression.Update`
661 object is created using the :func:`update()`
662 function.
664 """
666 __visit_name__ = "update"
668 def __init__(
669 self,
670 table,
671 whereclause=None,
672 values=None,
673 inline=False,
674 bind=None,
675 prefixes=None,
676 returning=None,
677 return_defaults=False,
678 preserve_parameter_order=False,
679 **dialect_kw
680 ):
681 r"""Construct an :class:`_expression.Update` object.
683 E.g.::
685 from sqlalchemy import update
687 stmt = update(users).where(users.c.id==5).\
688 values(name='user #5')
690 Similar functionality is available via the
691 :meth:`_expression.TableClause.update` method on
692 :class:`_schema.Table`::
694 stmt = users.update().\
695 where(users.c.id==5).\
696 values(name='user #5')
698 :param table: A :class:`_schema.Table`
699 object representing the database
700 table to be updated.
702 :param whereclause: Optional SQL expression describing the ``WHERE``
703 condition of the ``UPDATE`` statement; is equivalent to using the
704 more modern :meth:`~Update.where()` method to specify the ``WHERE``
705 clause.
707 :param values:
708 Optional dictionary which specifies the ``SET`` conditions of the
709 ``UPDATE``. If left as ``None``, the ``SET``
710 conditions are determined from those parameters passed to the
711 statement during the execution and/or compilation of the
712 statement. When compiled standalone without any parameters,
713 the ``SET`` clause generates for all columns.
715 Modern applications may prefer to use the generative
716 :meth:`_expression.Update.values` method to set the values of the
717 UPDATE statement.
719 :param inline:
720 if True, SQL defaults present on :class:`_schema.Column` objects via
721 the ``default`` keyword will be compiled 'inline' into the statement
722 and not pre-executed. This means that their values will not
723 be available in the dictionary returned from
724 :meth:`_engine.ResultProxy.last_updated_params`.
726 :param preserve_parameter_order: if True, the update statement is
727 expected to receive parameters **only** via the
728 :meth:`_expression.Update.values` method,
729 and they must be passed as a Python
730 ``list`` of 2-tuples. The rendered UPDATE statement will emit the SET
731 clause for each referenced column maintaining this order.
733 .. versionadded:: 1.0.10
735 .. seealso::
737 :ref:`updates_order_parameters` - full example of the
738 :paramref:`_expression.update.preserve_parameter_order` flag
740 If both ``values`` and compile-time bind parameters are present, the
741 compile-time bind parameters override the information specified
742 within ``values`` on a per-key basis.
744 The keys within ``values`` can be either :class:`_schema.Column`
745 objects or their string identifiers (specifically the "key" of the
746 :class:`_schema.Column`, normally but not necessarily equivalent to
747 its "name"). Normally, the
748 :class:`_schema.Column` objects used here are expected to be
749 part of the target :class:`_schema.Table` that is the table
750 to be updated. However when using MySQL, a multiple-table
751 UPDATE statement can refer to columns from any of
752 the tables referred to in the WHERE clause.
754 The values referred to in ``values`` are typically:
756 * a literal data value (i.e. string, number, etc.)
757 * a SQL expression, such as a related :class:`_schema.Column`,
758 a scalar-returning :func:`_expression.select` construct,
759 etc.
761 When combining :func:`_expression.select` constructs within the values
762 clause of an :func:`_expression.update` construct,
763 the subquery represented by the :func:`_expression.select` should be
764 *correlated* to the parent table, that is, providing criterion
765 which links the table inside the subquery to the outer table
766 being updated::
768 users.update().values(
769 name=select([addresses.c.email_address]).\
770 where(addresses.c.user_id==users.c.id).\
771 as_scalar()
772 )
774 .. seealso::
776 :ref:`inserts_and_updates` - SQL Expression
777 Language Tutorial
780 """
781 self._preserve_parameter_order = preserve_parameter_order
782 ValuesBase.__init__(self, table, values, prefixes)
783 self._bind = bind
784 self._returning = returning
785 if whereclause is not None:
786 self._whereclause = _literal_as_text(whereclause)
787 else:
788 self._whereclause = None
789 self.inline = inline
790 self._validate_dialect_kwargs(dialect_kw)
791 self._return_defaults = return_defaults
793 def get_children(self, **kwargs):
794 if self._whereclause is not None:
795 return (self._whereclause,)
796 else:
797 return ()
799 def _copy_internals(self, clone=_clone, **kw):
800 # TODO: coverage
801 self._whereclause = clone(self._whereclause, **kw)
802 self.parameters = self.parameters.copy()
804 @_generative
805 def where(self, whereclause):
806 """Return a new update() construct with the given expression added to
807 its WHERE clause, joined to the existing clause via AND, if any.
809 Both :meth:`_dml.Update.where` and :meth:`_dml.Delete.where`
810 support multiple-table forms, including database-specific
811 ``UPDATE...FROM`` as well as ``DELETE..USING``. For backends that
812 don't have multiple-table support, a backend agnostic approach
813 to using multiple tables is to make use of correlated subqueries.
814 See the linked tutorial sections below for examples.
816 .. seealso::
818 :ref:`tutorial_1x_correlated_updates`
820 :ref:`multi_table_updates`
822 :ref:`multi_table_deletes`
825 """
826 if self._whereclause is not None:
827 self._whereclause = and_(
828 self._whereclause, _literal_as_text(whereclause)
829 )
830 else:
831 self._whereclause = _literal_as_text(whereclause)
833 @property
834 def _extra_froms(self):
835 froms = []
837 all_tables = list(sql_util.tables_from_leftmost(self.table))
838 seen = {all_tables[0]}
840 if self._whereclause is not None:
841 for item in _from_objects(self._whereclause):
842 if not seen.intersection(item._cloned_set):
843 froms.append(item)
844 seen.update(item._cloned_set)
846 froms.extend(all_tables[1:])
847 return froms
850class Delete(UpdateBase):
851 """Represent a DELETE construct.
853 The :class:`_expression.Delete`
854 object is created using the :func:`delete()`
855 function.
857 """
859 __visit_name__ = "delete"
861 def __init__(
862 self,
863 table,
864 whereclause=None,
865 bind=None,
866 returning=None,
867 prefixes=None,
868 **dialect_kw
869 ):
870 r"""Construct :class:`_expression.Delete` object.
872 Similar functionality is available via the
873 :meth:`_expression.TableClause.delete` method on
874 :class:`_schema.Table`.
876 :param table: The table to delete rows from.
878 :param whereclause: Optional SQL expression describing the ``WHERE``
879 condition of the ``DELETE`` statement; is equivalent to using the
880 more modern :meth:`~Delete.where()` method to specify the ``WHERE``
881 clause.
883 .. seealso::
885 :ref:`deletes` - SQL Expression Tutorial
887 """
888 self._bind = bind
889 self.table = _interpret_as_from(table)
890 self._returning = returning
892 if prefixes:
893 self._setup_prefixes(prefixes)
895 if whereclause is not None:
896 self._whereclause = _literal_as_text(whereclause)
897 else:
898 self._whereclause = None
900 self._validate_dialect_kwargs(dialect_kw)
902 def get_children(self, **kwargs):
903 if self._whereclause is not None:
904 return (self._whereclause,)
905 else:
906 return ()
908 @_generative
909 def where(self, whereclause):
910 """Add the given WHERE clause to a newly returned delete construct.
912 Both :meth:`_dml.Update.where` and :meth:`_dml.Delete.where`
913 support multiple-table forms, including database-specific
914 ``UPDATE...FROM`` as well as ``DELETE..USING``. For backends that
915 don't have multiple-table support, a backend agnostic approach
916 to using multiple tables is to make use of correlated subqueries.
917 See the linked tutorial sections below for examples.
919 .. seealso::
921 :ref:`tutorial_1x_correlated_updates`
923 :ref:`multi_table_updates`
925 :ref:`multi_table_deletes`
928 """
930 if self._whereclause is not None:
931 self._whereclause = and_(
932 self._whereclause, _literal_as_text(whereclause)
933 )
934 else:
935 self._whereclause = _literal_as_text(whereclause)
937 @property
938 def _extra_froms(self):
939 froms = []
940 seen = {self.table}
942 if self._whereclause is not None:
943 for item in _from_objects(self._whereclause):
944 if not seen.intersection(item._cloned_set):
945 froms.append(item)
946 seen.update(item._cloned_set)
948 return froms
950 def _copy_internals(self, clone=_clone, **kw):
951 # TODO: coverage
952 self._whereclause = clone(self._whereclause, **kw)