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: https://www.opensource.org/licenses/mit-license.php 7""" 8Provide :class:`_expression.Insert`, :class:`_expression.Update` and 9:class:`_expression.Delete`. 10 11""" 12from sqlalchemy.types import NullType 13from . import coercions 14from . import roles 15from . import util as sql_util 16from .base import _entity_namespace_key 17from .base import _exclusive_against 18from .base import _from_objects 19from .base import _generative 20from .base import ColumnCollection 21from .base import CompileState 22from .base import DialectKWArgs 23from .base import Executable 24from .base import HasCompileState 25from .elements import BooleanClauseList 26from .elements import ClauseElement 27from .elements import Null 28from .selectable import HasCTE 29from .selectable import HasPrefixes 30from .selectable import ReturnsRows 31from .visitors import InternalTraversal 32from .. import exc 33from .. import util 34from ..util import collections_abc 35 36 37class DMLState(CompileState): 38 _no_parameters = True 39 _dict_parameters = None 40 _multi_parameters = None 41 _ordered_values = None 42 _parameter_ordering = None 43 _has_multi_parameters = False 44 isupdate = False 45 isdelete = False 46 isinsert = False 47 48 def __init__(self, statement, compiler, **kw): 49 raise NotImplementedError() 50 51 @property 52 def dml_table(self): 53 return self.statement.table 54 55 @classmethod 56 def _get_crud_kv_pairs(cls, statement, kv_iterator): 57 return [ 58 ( 59 coercions.expect(roles.DMLColumnRole, k), 60 coercions.expect( 61 roles.ExpressionElementRole, 62 v, 63 type_=NullType(), 64 is_crud=True, 65 ), 66 ) 67 for k, v in kv_iterator 68 ] 69 70 def _make_extra_froms(self, statement): 71 froms = [] 72 73 all_tables = list(sql_util.tables_from_leftmost(statement.table)) 74 seen = {all_tables[0]} 75 76 for crit in statement._where_criteria: 77 for item in _from_objects(crit): 78 if not seen.intersection(item._cloned_set): 79 froms.append(item) 80 seen.update(item._cloned_set) 81 82 froms.extend(all_tables[1:]) 83 return froms 84 85 def _process_multi_values(self, statement): 86 if not statement._supports_multi_parameters: 87 raise exc.InvalidRequestError( 88 "%s construct does not support " 89 "multiple parameter sets." % statement.__visit_name__.upper() 90 ) 91 92 for parameters in statement._multi_values: 93 multi_parameters = [ 94 { 95 c.key: value 96 for c, value in zip(statement.table.c, parameter_set) 97 } 98 if isinstance(parameter_set, collections_abc.Sequence) 99 else parameter_set 100 for parameter_set in parameters 101 ] 102 103 if self._no_parameters: 104 self._no_parameters = False 105 self._has_multi_parameters = True 106 self._multi_parameters = multi_parameters 107 self._dict_parameters = self._multi_parameters[0] 108 elif not self._has_multi_parameters: 109 self._cant_mix_formats_error() 110 else: 111 self._multi_parameters.extend(multi_parameters) 112 113 def _process_values(self, statement): 114 if self._no_parameters: 115 self._has_multi_parameters = False 116 self._dict_parameters = statement._values 117 self._no_parameters = False 118 elif self._has_multi_parameters: 119 self._cant_mix_formats_error() 120 121 def _process_ordered_values(self, statement): 122 parameters = statement._ordered_values 123 124 if self._no_parameters: 125 self._no_parameters = False 126 self._dict_parameters = dict(parameters) 127 self._ordered_values = parameters 128 self._parameter_ordering = [key for key, value in parameters] 129 elif self._has_multi_parameters: 130 self._cant_mix_formats_error() 131 else: 132 raise exc.InvalidRequestError( 133 "Can only invoke ordered_values() once, and not mixed " 134 "with any other values() call" 135 ) 136 137 def _process_select_values(self, statement): 138 parameters = { 139 coercions.expect(roles.DMLColumnRole, name, as_key=True): Null() 140 for name in statement._select_names 141 } 142 143 if self._no_parameters: 144 self._no_parameters = False 145 self._dict_parameters = parameters 146 else: 147 # this condition normally not reachable as the Insert 148 # does not allow this construction to occur 149 assert False, "This statement already has parameters" 150 151 def _cant_mix_formats_error(self): 152 raise exc.InvalidRequestError( 153 "Can't mix single and multiple VALUES " 154 "formats in one INSERT statement; one style appends to a " 155 "list while the other replaces values, so the intent is " 156 "ambiguous." 157 ) 158 159 160@CompileState.plugin_for("default", "insert") 161class InsertDMLState(DMLState): 162 isinsert = True 163 164 include_table_with_column_exprs = False 165 166 def __init__(self, statement, compiler, **kw): 167 self.statement = statement 168 169 self.isinsert = True 170 if statement._select_names: 171 self._process_select_values(statement) 172 if statement._values is not None: 173 self._process_values(statement) 174 if statement._multi_values: 175 self._process_multi_values(statement) 176 177 178@CompileState.plugin_for("default", "update") 179class UpdateDMLState(DMLState): 180 isupdate = True 181 182 include_table_with_column_exprs = False 183 184 def __init__(self, statement, compiler, **kw): 185 self.statement = statement 186 self.isupdate = True 187 self._preserve_parameter_order = statement._preserve_parameter_order 188 if statement._ordered_values is not None: 189 self._process_ordered_values(statement) 190 elif statement._values is not None: 191 self._process_values(statement) 192 elif statement._multi_values: 193 self._process_multi_values(statement) 194 self._extra_froms = ef = self._make_extra_froms(statement) 195 self.is_multitable = mt = ef and self._dict_parameters 196 self.include_table_with_column_exprs = ( 197 mt and compiler.render_table_with_column_in_update_from 198 ) 199 200 201@CompileState.plugin_for("default", "delete") 202class DeleteDMLState(DMLState): 203 isdelete = True 204 205 def __init__(self, statement, compiler, **kw): 206 self.statement = statement 207 208 self.isdelete = True 209 self._extra_froms = self._make_extra_froms(statement) 210 211 212class UpdateBase( 213 roles.DMLRole, 214 HasCTE, 215 HasCompileState, 216 DialectKWArgs, 217 HasPrefixes, 218 ReturnsRows, 219 Executable, 220 ClauseElement, 221): 222 """Form the base for ``INSERT``, ``UPDATE``, and ``DELETE`` statements.""" 223 224 __visit_name__ = "update_base" 225 226 _execution_options = Executable._execution_options.union( 227 {"autocommit": True} 228 ) 229 _hints = util.immutabledict() 230 named_with_column = False 231 232 _return_defaults = False 233 _return_defaults_columns = None 234 _returning = () 235 236 is_dml = True 237 238 @classmethod 239 def _constructor_20_deprecations(cls, fn_name, clsname, names): 240 241 param_to_method_lookup = dict( 242 whereclause=( 243 "The :paramref:`%(func)s.whereclause` parameter " 244 "will be removed " 245 "in SQLAlchemy 2.0. Please refer to the " 246 ":meth:`%(classname)s.where` method." 247 ), 248 values=( 249 "The :paramref:`%(func)s.values` parameter will be removed " 250 "in SQLAlchemy 2.0. Please refer to the " 251 ":meth:`%(classname)s.values` method." 252 ), 253 bind=( 254 "The :paramref:`%(func)s.bind` parameter will be removed in " 255 "SQLAlchemy 2.0. Please use explicit connection execution." 256 ), 257 inline=( 258 "The :paramref:`%(func)s.inline` parameter will be " 259 "removed in " 260 "SQLAlchemy 2.0. Please use the " 261 ":meth:`%(classname)s.inline` method." 262 ), 263 prefixes=( 264 "The :paramref:`%(func)s.prefixes parameter will be " 265 "removed in " 266 "SQLAlchemy 2.0. Please use the " 267 ":meth:`%(classname)s.prefix_with` " 268 "method." 269 ), 270 return_defaults=( 271 "The :paramref:`%(func)s.return_defaults` parameter will be " 272 "removed in SQLAlchemy 2.0. Please use the " 273 ":meth:`%(classname)s.return_defaults` method." 274 ), 275 returning=( 276 "The :paramref:`%(func)s.returning` parameter will be " 277 "removed in SQLAlchemy 2.0. Please use the " 278 ":meth:`%(classname)s.returning`` method." 279 ), 280 preserve_parameter_order=( 281 "The :paramref:`%(func)s.preserve_parameter_order` parameter " 282 "will be removed in SQLAlchemy 2.0. Use the " 283 ":meth:`%(classname)s.ordered_values` method with a list " 284 "of tuples. " 285 ), 286 ) 287 288 return util.deprecated_params( 289 **{ 290 name: ( 291 "2.0", 292 param_to_method_lookup[name] 293 % { 294 "func": "_expression.%s" % fn_name, 295 "classname": "_expression.%s" % clsname, 296 }, 297 ) 298 for name in names 299 } 300 ) 301 302 def _generate_fromclause_column_proxies(self, fromclause): 303 fromclause._columns._populate_separate_keys( 304 col._make_proxy(fromclause) for col in self._returning 305 ) 306 307 def params(self, *arg, **kw): 308 """Set the parameters for the statement. 309 310 This method raises ``NotImplementedError`` on the base class, 311 and is overridden by :class:`.ValuesBase` to provide the 312 SET/VALUES clause of UPDATE and INSERT. 313 314 """ 315 raise NotImplementedError( 316 "params() is not supported for INSERT/UPDATE/DELETE statements." 317 " To set the values for an INSERT or UPDATE statement, use" 318 " stmt.values(**parameters)." 319 ) 320 321 @_generative 322 def with_dialect_options(self, **opt): 323 """Add dialect options to this INSERT/UPDATE/DELETE object. 324 325 e.g.:: 326 327 upd = table.update().dialect_options(mysql_limit=10) 328 329 .. versionadded: 1.4 - this method supersedes the dialect options 330 associated with the constructor. 331 332 333 """ 334 self._validate_dialect_kwargs(opt) 335 336 def _validate_dialect_kwargs_deprecated(self, dialect_kw): 337 util.warn_deprecated_20( 338 "Passing dialect keyword arguments directly to the " 339 "%s constructor is deprecated and will be removed in SQLAlchemy " 340 "2.0. Please use the ``with_dialect_options()`` method." 341 % (self.__class__.__name__) 342 ) 343 self._validate_dialect_kwargs(dialect_kw) 344 345 def bind(self): 346 """Return a 'bind' linked to this :class:`.UpdateBase` 347 or a :class:`_schema.Table` associated with it. 348 349 """ 350 return self._bind or self.table.bind 351 352 def _set_bind(self, bind): 353 self._bind = bind 354 355 bind = property(bind, _set_bind) 356 357 @_generative 358 def returning(self, *cols): 359 r"""Add a :term:`RETURNING` or equivalent clause to this statement. 360 361 e.g.: 362 363 .. sourcecode:: pycon+sql 364 365 >>> stmt = ( 366 ... table.update() 367 ... .where(table.c.data == "value") 368 ... .values(status="X") 369 ... .returning(table.c.server_flag, table.c.updated_timestamp) 370 ... ) 371 >>> print(stmt) 372 UPDATE some_table SET status=:status 373 WHERE some_table.data = :data_1 374 RETURNING some_table.server_flag, some_table.updated_timestamp 375 376 The method may be invoked multiple times to add new entries to the 377 list of expressions to be returned. 378 379 .. versionadded:: 1.4.0b2 The method may be invoked multiple times to 380 add new entries to the list of expressions to be returned. 381 382 The given collection of column expressions should be derived from the 383 table that is the target of the INSERT, UPDATE, or DELETE. While 384 :class:`_schema.Column` objects are typical, the elements can also be 385 expressions: 386 387 .. sourcecode:: pycon+sql 388 389 >>> stmt = table.insert().returning( 390 ... (table.c.first_name + " " + table.c.last_name).label("fullname") 391 ... ) 392 >>> print(stmt) 393 INSERT INTO some_table (first_name, last_name) 394 VALUES (:first_name, :last_name) 395 RETURNING some_table.first_name || :first_name_1 || some_table.last_name AS fullname 396 397 Upon compilation, a RETURNING clause, or database equivalent, 398 will be rendered within the statement. For INSERT and UPDATE, 399 the values are the newly inserted/updated values. For DELETE, 400 the values are those of the rows which were deleted. 401 402 Upon execution, the values of the columns to be returned are made 403 available via the result set and can be iterated using 404 :meth:`_engine.CursorResult.fetchone` and similar. 405 For DBAPIs which do not 406 natively support returning values (i.e. cx_oracle), SQLAlchemy will 407 approximate this behavior at the result level so that a reasonable 408 amount of behavioral neutrality is provided. 409 410 Note that not all databases/DBAPIs 411 support RETURNING. For those backends with no support, 412 an exception is raised upon compilation and/or execution. 413 For those who do support it, the functionality across backends 414 varies greatly, including restrictions on executemany() 415 and other statements which return multiple rows. Please 416 read the documentation notes for the database in use in 417 order to determine the availability of RETURNING. 418 419 .. seealso:: 420 421 :meth:`.ValuesBase.return_defaults` - an alternative method tailored 422 towards efficient fetching of server-side defaults and triggers 423 for single-row INSERTs or UPDATEs. 424 425 :ref:`tutorial_insert_returning` - in the :ref:`unified_tutorial` 426 427 """ # noqa E501 428 if self._return_defaults: 429 raise exc.InvalidRequestError( 430 "return_defaults() is already configured on this statement" 431 ) 432 self._returning += tuple( 433 coercions.expect(roles.ColumnsClauseRole, c) for c in cols 434 ) 435 436 @property 437 def _all_selected_columns(self): 438 return self._returning 439 440 @property 441 def exported_columns(self): 442 """Return the RETURNING columns as a column collection for this 443 statement. 444 445 .. versionadded:: 1.4 446 447 """ 448 # TODO: no coverage here 449 return ColumnCollection( 450 (c.key, c) for c in self._all_selected_columns 451 ).as_immutable() 452 453 @_generative 454 def with_hint(self, text, selectable=None, dialect_name="*"): 455 """Add a table hint for a single table to this 456 INSERT/UPDATE/DELETE statement. 457 458 .. note:: 459 460 :meth:`.UpdateBase.with_hint` currently applies only to 461 Microsoft SQL Server. For MySQL INSERT/UPDATE/DELETE hints, use 462 :meth:`.UpdateBase.prefix_with`. 463 464 The text of the hint is rendered in the appropriate 465 location for the database backend in use, relative 466 to the :class:`_schema.Table` that is the subject of this 467 statement, or optionally to that of the given 468 :class:`_schema.Table` passed as the ``selectable`` argument. 469 470 The ``dialect_name`` option will limit the rendering of a particular 471 hint to a particular backend. Such as, to add a hint 472 that only takes effect for SQL Server:: 473 474 mytable.insert().with_hint("WITH (PAGLOCK)", dialect_name="mssql") 475 476 :param text: Text of the hint. 477 :param selectable: optional :class:`_schema.Table` that specifies 478 an element of the FROM clause within an UPDATE or DELETE 479 to be the subject of the hint - applies only to certain backends. 480 :param dialect_name: defaults to ``*``, if specified as the name 481 of a particular dialect, will apply these hints only when 482 that dialect is in use. 483 """ 484 if selectable is None: 485 selectable = self.table 486 487 self._hints = self._hints.union({(selectable, dialect_name): text}) 488 489 490class ValuesBase(UpdateBase): 491 """Supplies support for :meth:`.ValuesBase.values` to 492 INSERT and UPDATE constructs.""" 493 494 __visit_name__ = "values_base" 495 496 _supports_multi_parameters = False 497 _preserve_parameter_order = False 498 select = None 499 _post_values_clause = None 500 501 _values = None 502 _multi_values = () 503 _ordered_values = None 504 _select_names = None 505 506 _returning = () 507 508 def __init__(self, table, values, prefixes): 509 self.table = coercions.expect( 510 roles.DMLTableRole, table, apply_propagate_attrs=self 511 ) 512 if values is not None: 513 self.values.non_generative(self, values) 514 if prefixes: 515 self._setup_prefixes(prefixes) 516 517 @_generative 518 @_exclusive_against( 519 "_select_names", 520 "_ordered_values", 521 msgs={ 522 "_select_names": "This construct already inserts from a SELECT", 523 "_ordered_values": "This statement already has ordered " 524 "values present", 525 }, 526 ) 527 def values(self, *args, **kwargs): 528 r"""Specify a fixed VALUES clause for an INSERT statement, or the SET 529 clause for an UPDATE. 530 531 Note that the :class:`_expression.Insert` and 532 :class:`_expression.Update` 533 constructs support 534 per-execution time formatting of the VALUES and/or SET clauses, 535 based on the arguments passed to :meth:`_engine.Connection.execute`. 536 However, the :meth:`.ValuesBase.values` method can be used to "fix" a 537 particular set of parameters into the statement. 538 539 Multiple calls to :meth:`.ValuesBase.values` will produce a new 540 construct, each one with the parameter list modified to include 541 the new parameters sent. In the typical case of a single 542 dictionary of parameters, the newly passed keys will replace 543 the same keys in the previous construct. In the case of a list-based 544 "multiple values" construct, each new list of values is extended 545 onto the existing list of values. 546 547 :param \**kwargs: key value pairs representing the string key 548 of a :class:`_schema.Column` 549 mapped to the value to be rendered into the 550 VALUES or SET clause:: 551 552 users.insert().values(name="some name") 553 554 users.update().where(users.c.id==5).values(name="some name") 555 556 :param \*args: As an alternative to passing key/value parameters, 557 a dictionary, tuple, or list of dictionaries or tuples can be passed 558 as a single positional argument in order to form the VALUES or 559 SET clause of the statement. The forms that are accepted vary 560 based on whether this is an :class:`_expression.Insert` or an 561 :class:`_expression.Update` construct. 562 563 For either an :class:`_expression.Insert` or 564 :class:`_expression.Update` 565 construct, a single dictionary can be passed, which works the same as 566 that of the kwargs form:: 567 568 users.insert().values({"name": "some name"}) 569 570 users.update().values({"name": "some new name"}) 571 572 Also for either form but more typically for the 573 :class:`_expression.Insert` construct, a tuple that contains an 574 entry for every column in the table is also accepted:: 575 576 users.insert().values((5, "some name")) 577 578 The :class:`_expression.Insert` construct also supports being 579 passed a list of dictionaries or full-table-tuples, which on the 580 server will render the less common SQL syntax of "multiple values" - 581 this syntax is supported on backends such as SQLite, PostgreSQL, 582 MySQL, but not necessarily others:: 583 584 users.insert().values([ 585 {"name": "some name"}, 586 {"name": "some other name"}, 587 {"name": "yet another name"}, 588 ]) 589 590 The above form would render a multiple VALUES statement similar to:: 591 592 INSERT INTO users (name) VALUES 593 (:name_1), 594 (:name_2), 595 (:name_3) 596 597 It is essential to note that **passing multiple values is 598 NOT the same as using traditional executemany() form**. The above 599 syntax is a **special** syntax not typically used. To emit an 600 INSERT statement against multiple rows, the normal method is 601 to pass a multiple values list to the 602 :meth:`_engine.Connection.execute` 603 method, which is supported by all database backends and is generally 604 more efficient for a very large number of parameters. 605 606 .. seealso:: 607 608 :ref:`execute_multiple` - an introduction to 609 the traditional Core method of multiple parameter set 610 invocation for INSERTs and other statements. 611 612 .. versionchanged:: 1.0.0 an INSERT that uses a multiple-VALUES 613 clause, even a list of length one, 614 implies that the :paramref:`_expression.Insert.inline` 615 flag is set to 616 True, indicating that the statement will not attempt to fetch 617 the "last inserted primary key" or other defaults. The 618 statement deals with an arbitrary number of rows, so the 619 :attr:`_engine.CursorResult.inserted_primary_key` 620 accessor does not 621 apply. 622 623 .. versionchanged:: 1.0.0 A multiple-VALUES INSERT now supports 624 columns with Python side default values and callables in the 625 same way as that of an "executemany" style of invocation; the 626 callable is invoked for each row. See :ref:`bug_3288` 627 for other details. 628 629 The UPDATE construct also supports rendering the SET parameters 630 in a specific order. For this feature refer to the 631 :meth:`_expression.Update.ordered_values` method. 632 633 .. seealso:: 634 635 :meth:`_expression.Update.ordered_values` 636 637 638 """ 639 if args: 640 # positional case. this is currently expensive. we don't 641 # yet have positional-only args so we have to check the length. 642 # then we need to check multiparams vs. single dictionary. 643 # since the parameter format is needed in order to determine 644 # a cache key, we need to determine this up front. 645 arg = args[0] 646 647 if kwargs: 648 raise exc.ArgumentError( 649 "Can't pass positional and kwargs to values() " 650 "simultaneously" 651 ) 652 elif len(args) > 1: 653 raise exc.ArgumentError( 654 "Only a single dictionary/tuple or list of " 655 "dictionaries/tuples is accepted positionally." 656 ) 657 658 elif not self._preserve_parameter_order and isinstance( 659 arg, collections_abc.Sequence 660 ): 661 662 if arg and isinstance(arg[0], (list, dict, tuple)): 663 self._multi_values += (arg,) 664 return 665 666 # tuple values 667 arg = {c.key: value for c, value in zip(self.table.c, arg)} 668 elif self._preserve_parameter_order and not isinstance( 669 arg, collections_abc.Sequence 670 ): 671 raise ValueError( 672 "When preserve_parameter_order is True, " 673 "values() only accepts a list of 2-tuples" 674 ) 675 676 else: 677 # kwarg path. this is the most common path for non-multi-params 678 # so this is fairly quick. 679 arg = kwargs 680 if args: 681 raise exc.ArgumentError( 682 "Only a single dictionary/tuple or list of " 683 "dictionaries/tuples is accepted positionally." 684 ) 685 686 # for top level values(), convert literals to anonymous bound 687 # parameters at statement construction time, so that these values can 688 # participate in the cache key process like any other ClauseElement. 689 # crud.py now intercepts bound parameters with unique=True from here 690 # and ensures they get the "crud"-style name when rendered. 691 692 kv_generator = DMLState.get_plugin_class(self)._get_crud_kv_pairs 693 694 if self._preserve_parameter_order: 695 self._ordered_values = kv_generator(self, arg) 696 else: 697 arg = {k: v for k, v in kv_generator(self, arg.items())} 698 if self._values: 699 self._values = self._values.union(arg) 700 else: 701 self._values = util.immutabledict(arg) 702 703 @_generative 704 @_exclusive_against( 705 "_returning", 706 msgs={ 707 "_returning": "RETURNING is already configured on this statement" 708 }, 709 defaults={"_returning": _returning}, 710 ) 711 def return_defaults(self, *cols): 712 """Make use of a :term:`RETURNING` clause for the purpose 713 of fetching server-side expressions and defaults. 714 715 E.g.:: 716 717 stmt = table.insert().values(data='newdata').return_defaults() 718 719 result = connection.execute(stmt) 720 721 server_created_at = result.returned_defaults['created_at'] 722 723 When used against a backend that supports RETURNING, all column 724 values generated by SQL expression or server-side-default will be 725 added to any existing RETURNING clause, provided that 726 :meth:`.UpdateBase.returning` is not used simultaneously. The column 727 values will then be available on the result using the 728 :attr:`_engine.CursorResult.returned_defaults` accessor as 729 a dictionary, 730 referring to values keyed to the :class:`_schema.Column` 731 object as well as 732 its ``.key``. 733 734 This method differs from :meth:`.UpdateBase.returning` in these ways: 735 736 1. :meth:`.ValuesBase.return_defaults` is only intended for use with an 737 INSERT or an UPDATE statement that matches exactly one row per 738 parameter set. While the RETURNING construct in the general sense 739 supports multiple rows for a multi-row UPDATE or DELETE statement, 740 or for special cases of INSERT that return multiple rows (e.g. 741 INSERT from SELECT, multi-valued VALUES clause), 742 :meth:`.ValuesBase.return_defaults` is intended only for an 743 "ORM-style" single-row INSERT/UPDATE statement. The row 744 returned by the statement is also consumed implicitly when 745 :meth:`.ValuesBase.return_defaults` is used. By contrast, 746 :meth:`.UpdateBase.returning` leaves the RETURNING result-set intact 747 with a collection of any number of rows. 748 749 2. It is compatible with the existing logic to fetch auto-generated 750 primary key values, also known as "implicit returning". Backends 751 that support RETURNING will automatically make use of RETURNING in 752 order to fetch the value of newly generated primary keys; while the 753 :meth:`.UpdateBase.returning` method circumvents this behavior, 754 :meth:`.ValuesBase.return_defaults` leaves it intact. 755 756 3. It can be called against any backend. Backends that don't support 757 RETURNING will skip the usage of the feature, rather than raising 758 an exception. The return value of 759 :attr:`_engine.CursorResult.returned_defaults` will be ``None`` 760 761 4. An INSERT statement invoked with executemany() is supported if the 762 backend database driver supports the 763 ``insert_executemany_returning`` feature, currently this includes 764 PostgreSQL with psycopg2. When executemany is used, the 765 :attr:`_engine.CursorResult.returned_defaults_rows` and 766 :attr:`_engine.CursorResult.inserted_primary_key_rows` accessors 767 will return the inserted defaults and primary keys. 768 769 .. versionadded:: 1.4 770 771 :meth:`.ValuesBase.return_defaults` is used by the ORM to provide 772 an efficient implementation for the ``eager_defaults`` feature of 773 :func:`.mapper`. 774 775 :param cols: optional list of column key names or 776 :class:`_schema.Column` 777 objects. If omitted, all column expressions evaluated on the server 778 are added to the returning list. 779 780 .. versionadded:: 0.9.0 781 782 .. seealso:: 783 784 :meth:`.UpdateBase.returning` 785 786 :attr:`_engine.CursorResult.returned_defaults` 787 788 :attr:`_engine.CursorResult.returned_defaults_rows` 789 790 :attr:`_engine.CursorResult.inserted_primary_key` 791 792 :attr:`_engine.CursorResult.inserted_primary_key_rows` 793 794 """ 795 self._return_defaults = True 796 self._return_defaults_columns = cols 797 798 799class Insert(ValuesBase): 800 """Represent an INSERT construct. 801 802 The :class:`_expression.Insert` object is created using the 803 :func:`_expression.insert()` function. 804 805 """ 806 807 __visit_name__ = "insert" 808 809 _supports_multi_parameters = True 810 811 select = None 812 include_insert_from_select_defaults = False 813 814 is_insert = True 815 816 _traverse_internals = ( 817 [ 818 ("table", InternalTraversal.dp_clauseelement), 819 ("_inline", InternalTraversal.dp_boolean), 820 ("_select_names", InternalTraversal.dp_string_list), 821 ("_values", InternalTraversal.dp_dml_values), 822 ("_multi_values", InternalTraversal.dp_dml_multi_values), 823 ("select", InternalTraversal.dp_clauseelement), 824 ("_post_values_clause", InternalTraversal.dp_clauseelement), 825 ("_returning", InternalTraversal.dp_clauseelement_list), 826 ("_hints", InternalTraversal.dp_table_hint_list), 827 ("_return_defaults", InternalTraversal.dp_boolean), 828 ( 829 "_return_defaults_columns", 830 InternalTraversal.dp_clauseelement_list, 831 ), 832 ] 833 + HasPrefixes._has_prefixes_traverse_internals 834 + DialectKWArgs._dialect_kwargs_traverse_internals 835 + Executable._executable_traverse_internals 836 + HasCTE._has_ctes_traverse_internals 837 ) 838 839 @ValuesBase._constructor_20_deprecations( 840 "insert", 841 "Insert", 842 [ 843 "values", 844 "inline", 845 "bind", 846 "prefixes", 847 "returning", 848 "return_defaults", 849 ], 850 ) 851 def __init__( 852 self, 853 table, 854 values=None, 855 inline=False, 856 bind=None, 857 prefixes=None, 858 returning=None, 859 return_defaults=False, 860 **dialect_kw 861 ): 862 """Construct an :class:`_expression.Insert` object. 863 864 E.g.:: 865 866 from sqlalchemy import insert 867 868 stmt = ( 869 insert(user_table). 870 values(name='username', fullname='Full Username') 871 ) 872 873 Similar functionality is available via the 874 :meth:`_expression.TableClause.insert` method on 875 :class:`_schema.Table`. 876 877 .. seealso:: 878 879 :ref:`coretutorial_insert_expressions` - in the 880 :ref:`1.x tutorial <sqlexpression_toplevel>` 881 882 :ref:`tutorial_core_insert` - in the :ref:`unified_tutorial` 883 884 885 :param table: :class:`_expression.TableClause` 886 which is the subject of the 887 insert. 888 889 :param values: collection of values to be inserted; see 890 :meth:`_expression.Insert.values` 891 for a description of allowed formats here. 892 Can be omitted entirely; a :class:`_expression.Insert` construct 893 will also dynamically render the VALUES clause at execution time 894 based on the parameters passed to :meth:`_engine.Connection.execute`. 895 896 :param inline: if True, no attempt will be made to retrieve the 897 SQL-generated default values to be provided within the statement; 898 in particular, 899 this allows SQL expressions to be rendered 'inline' within the 900 statement without the need to pre-execute them beforehand; for 901 backends that support "returning", this turns off the "implicit 902 returning" feature for the statement. 903 904 If both :paramref:`_expression.Insert.values` and compile-time bind 905 parameters are present, the compile-time bind parameters override the 906 information specified within :paramref:`_expression.Insert.values` on a 907 per-key basis. 908 909 The keys within :paramref:`_expression.Insert.values` can be either 910 :class:`~sqlalchemy.schema.Column` objects or their string 911 identifiers. Each key may reference one of: 912 913 * a literal data value (i.e. string, number, etc.); 914 * a Column object; 915 * a SELECT statement. 916 917 If a ``SELECT`` statement is specified which references this 918 ``INSERT`` statement's table, the statement will be correlated 919 against the ``INSERT`` statement. 920 921 .. seealso:: 922 923 :ref:`coretutorial_insert_expressions` - SQL Expression Tutorial 924 925 :ref:`inserts_and_updates` - SQL Expression Tutorial 926 927 """ 928 super(Insert, self).__init__(table, values, prefixes) 929 self._bind = bind 930 self._inline = inline 931 if returning: 932 self._returning = returning 933 if dialect_kw: 934 self._validate_dialect_kwargs_deprecated(dialect_kw) 935 936 if return_defaults: 937 self._return_defaults = True 938 if not isinstance(return_defaults, bool): 939 self._return_defaults_columns = return_defaults 940 941 @_generative 942 def inline(self): 943 """Make this :class:`_expression.Insert` construct "inline" . 944 945 When set, no attempt will be made to retrieve the 946 SQL-generated default values to be provided within the statement; 947 in particular, 948 this allows SQL expressions to be rendered 'inline' within the 949 statement without the need to pre-execute them beforehand; for 950 backends that support "returning", this turns off the "implicit 951 returning" feature for the statement. 952 953 954 .. versionchanged:: 1.4 the :paramref:`_expression.Insert.inline` 955 parameter 956 is now superseded by the :meth:`_expression.Insert.inline` method. 957 958 """ 959 self._inline = True 960 961 @_generative 962 def from_select(self, names, select, include_defaults=True): 963 """Return a new :class:`_expression.Insert` construct which represents 964 an ``INSERT...FROM SELECT`` statement. 965 966 e.g.:: 967 968 sel = select(table1.c.a, table1.c.b).where(table1.c.c > 5) 969 ins = table2.insert().from_select(['a', 'b'], sel) 970 971 :param names: a sequence of string column names or 972 :class:`_schema.Column` 973 objects representing the target columns. 974 :param select: a :func:`_expression.select` construct, 975 :class:`_expression.FromClause` 976 or other construct which resolves into a 977 :class:`_expression.FromClause`, 978 such as an ORM :class:`_query.Query` object, etc. The order of 979 columns returned from this FROM clause should correspond to the 980 order of columns sent as the ``names`` parameter; while this 981 is not checked before passing along to the database, the database 982 would normally raise an exception if these column lists don't 983 correspond. 984 :param include_defaults: if True, non-server default values and 985 SQL expressions as specified on :class:`_schema.Column` objects 986 (as documented in :ref:`metadata_defaults_toplevel`) not 987 otherwise specified in the list of names will be rendered 988 into the INSERT and SELECT statements, so that these values are also 989 included in the data to be inserted. 990 991 .. note:: A Python-side default that uses a Python callable function 992 will only be invoked **once** for the whole statement, and **not 993 per row**. 994 995 .. versionadded:: 1.0.0 - :meth:`_expression.Insert.from_select` 996 now renders 997 Python-side and SQL expression column defaults into the 998 SELECT statement for columns otherwise not included in the 999 list of column names. 1000 1001 .. versionchanged:: 1.0.0 an INSERT that uses FROM SELECT 1002 implies that the :paramref:`_expression.insert.inline` 1003 flag is set to 1004 True, indicating that the statement will not attempt to fetch 1005 the "last inserted primary key" or other defaults. The statement 1006 deals with an arbitrary number of rows, so the 1007 :attr:`_engine.CursorResult.inserted_primary_key` 1008 accessor does not apply. 1009 1010 """ 1011 1012 if self._values: 1013 raise exc.InvalidRequestError( 1014 "This construct already inserts value expressions" 1015 ) 1016 1017 self._select_names = names 1018 self._inline = True 1019 self.include_insert_from_select_defaults = include_defaults 1020 self.select = coercions.expect(roles.DMLSelectRole, select) 1021 1022 1023class DMLWhereBase(object): 1024 _where_criteria = () 1025 1026 @_generative 1027 def where(self, *whereclause): 1028 """Return a new construct with the given expression(s) added to 1029 its WHERE clause, joined to the existing clause via AND, if any. 1030 1031 Both :meth:`_dml.Update.where` and :meth:`_dml.Delete.where` 1032 support multiple-table forms, including database-specific 1033 ``UPDATE...FROM`` as well as ``DELETE..USING``. For backends that 1034 don't have multiple-table support, a backend agnostic approach 1035 to using multiple tables is to make use of correlated subqueries. 1036 See the linked tutorial sections below for examples. 1037 1038 .. seealso:: 1039 1040 **1.x Tutorial Examples** 1041 1042 :ref:`tutorial_1x_correlated_updates` 1043 1044 :ref:`multi_table_updates` 1045 1046 :ref:`multi_table_deletes` 1047 1048 **2.0 Tutorial Examples** 1049 1050 :ref:`tutorial_correlated_updates` 1051 1052 :ref:`tutorial_update_from` 1053 1054 :ref:`tutorial_multi_table_deletes` 1055 1056 """ 1057 1058 for criterion in whereclause: 1059 where_criteria = coercions.expect(roles.WhereHavingRole, criterion) 1060 self._where_criteria += (where_criteria,) 1061 1062 def filter(self, *criteria): 1063 """A synonym for the :meth:`_dml.DMLWhereBase.where` method. 1064 1065 .. versionadded:: 1.4 1066 1067 """ 1068 1069 return self.where(*criteria) 1070 1071 def _filter_by_zero(self): 1072 return self.table 1073 1074 def filter_by(self, **kwargs): 1075 r"""apply the given filtering criterion as a WHERE clause 1076 to this select. 1077 1078 """ 1079 from_entity = self._filter_by_zero() 1080 1081 clauses = [ 1082 _entity_namespace_key(from_entity, key) == value 1083 for key, value in kwargs.items() 1084 ] 1085 return self.filter(*clauses) 1086 1087 @property 1088 def whereclause(self): 1089 """Return the completed WHERE clause for this :class:`.DMLWhereBase` 1090 statement. 1091 1092 This assembles the current collection of WHERE criteria 1093 into a single :class:`_expression.BooleanClauseList` construct. 1094 1095 1096 .. versionadded:: 1.4 1097 1098 """ 1099 1100 return BooleanClauseList._construct_for_whereclause( 1101 self._where_criteria 1102 ) 1103 1104 1105class Update(DMLWhereBase, ValuesBase): 1106 """Represent an Update construct. 1107 1108 The :class:`_expression.Update` object is created using the 1109 :func:`_expression.update()` function. 1110 1111 """ 1112 1113 __visit_name__ = "update" 1114 1115 is_update = True 1116 1117 _traverse_internals = ( 1118 [ 1119 ("table", InternalTraversal.dp_clauseelement), 1120 ("_where_criteria", InternalTraversal.dp_clauseelement_list), 1121 ("_inline", InternalTraversal.dp_boolean), 1122 ("_ordered_values", InternalTraversal.dp_dml_ordered_values), 1123 ("_values", InternalTraversal.dp_dml_values), 1124 ("_returning", InternalTraversal.dp_clauseelement_list), 1125 ("_hints", InternalTraversal.dp_table_hint_list), 1126 ("_return_defaults", InternalTraversal.dp_boolean), 1127 ( 1128 "_return_defaults_columns", 1129 InternalTraversal.dp_clauseelement_list, 1130 ), 1131 ] 1132 + HasPrefixes._has_prefixes_traverse_internals 1133 + DialectKWArgs._dialect_kwargs_traverse_internals 1134 + Executable._executable_traverse_internals 1135 + HasCTE._has_ctes_traverse_internals 1136 ) 1137 1138 @ValuesBase._constructor_20_deprecations( 1139 "update", 1140 "Update", 1141 [ 1142 "whereclause", 1143 "values", 1144 "inline", 1145 "bind", 1146 "prefixes", 1147 "returning", 1148 "return_defaults", 1149 "preserve_parameter_order", 1150 ], 1151 ) 1152 def __init__( 1153 self, 1154 table, 1155 whereclause=None, 1156 values=None, 1157 inline=False, 1158 bind=None, 1159 prefixes=None, 1160 returning=None, 1161 return_defaults=False, 1162 preserve_parameter_order=False, 1163 **dialect_kw 1164 ): 1165 r"""Construct an :class:`_expression.Update` object. 1166 1167 E.g.:: 1168 1169 from sqlalchemy import update 1170 1171 stmt = ( 1172 update(user_table). 1173 where(user_table.c.id == 5). 1174 values(name='user #5') 1175 ) 1176 1177 Similar functionality is available via the 1178 :meth:`_expression.TableClause.update` method on 1179 :class:`_schema.Table`. 1180 1181 .. seealso:: 1182 1183 :ref:`inserts_and_updates` - in the 1184 :ref:`1.x tutorial <sqlexpression_toplevel>` 1185 1186 :ref:`tutorial_core_update_delete` - in the :ref:`unified_tutorial` 1187 1188 1189 1190 :param table: A :class:`_schema.Table` 1191 object representing the database 1192 table to be updated. 1193 1194 :param whereclause: Optional SQL expression describing the ``WHERE`` 1195 condition of the ``UPDATE`` statement; is equivalent to using the 1196 more modern :meth:`~Update.where()` method to specify the ``WHERE`` 1197 clause. 1198 1199 :param values: 1200 Optional dictionary which specifies the ``SET`` conditions of the 1201 ``UPDATE``. If left as ``None``, the ``SET`` 1202 conditions are determined from those parameters passed to the 1203 statement during the execution and/or compilation of the 1204 statement. When compiled standalone without any parameters, 1205 the ``SET`` clause generates for all columns. 1206 1207 Modern applications may prefer to use the generative 1208 :meth:`_expression.Update.values` method to set the values of the 1209 UPDATE statement. 1210 1211 :param inline: 1212 if True, SQL defaults present on :class:`_schema.Column` objects via 1213 the ``default`` keyword will be compiled 'inline' into the statement 1214 and not pre-executed. This means that their values will not 1215 be available in the dictionary returned from 1216 :meth:`_engine.CursorResult.last_updated_params`. 1217 1218 :param preserve_parameter_order: if True, the update statement is 1219 expected to receive parameters **only** via the 1220 :meth:`_expression.Update.values` method, 1221 and they must be passed as a Python 1222 ``list`` of 2-tuples. The rendered UPDATE statement will emit the SET 1223 clause for each referenced column maintaining this order. 1224 1225 .. versionadded:: 1.0.10 1226 1227 .. seealso:: 1228 1229 :ref:`updates_order_parameters` - illustrates the 1230 :meth:`_expression.Update.ordered_values` method. 1231 1232 If both ``values`` and compile-time bind parameters are present, the 1233 compile-time bind parameters override the information specified 1234 within ``values`` on a per-key basis. 1235 1236 The keys within ``values`` can be either :class:`_schema.Column` 1237 objects or their string identifiers (specifically the "key" of the 1238 :class:`_schema.Column`, normally but not necessarily equivalent to 1239 its "name"). Normally, the 1240 :class:`_schema.Column` objects used here are expected to be 1241 part of the target :class:`_schema.Table` that is the table 1242 to be updated. However when using MySQL, a multiple-table 1243 UPDATE statement can refer to columns from any of 1244 the tables referred to in the WHERE clause. 1245 1246 The values referred to in ``values`` are typically: 1247 1248 * a literal data value (i.e. string, number, etc.) 1249 * a SQL expression, such as a related :class:`_schema.Column`, 1250 a scalar-returning :func:`_expression.select` construct, 1251 etc. 1252 1253 When combining :func:`_expression.select` constructs within the 1254 values clause of an :func:`_expression.update` 1255 construct, the subquery represented 1256 by the :func:`_expression.select` should be *correlated* to the 1257 parent table, that is, providing criterion which links the table inside 1258 the subquery to the outer table being updated:: 1259 1260 users.update().values( 1261 name=select(addresses.c.email_address).\ 1262 where(addresses.c.user_id==users.c.id).\ 1263 scalar_subquery() 1264 ) 1265 1266 .. seealso:: 1267 1268 :ref:`inserts_and_updates` - SQL Expression 1269 Language Tutorial 1270 1271 1272 """ 1273 self._preserve_parameter_order = preserve_parameter_order 1274 super(Update, self).__init__(table, values, prefixes) 1275 self._bind = bind 1276 if returning: 1277 self._returning = returning 1278 if whereclause is not None: 1279 self._where_criteria += ( 1280 coercions.expect(roles.WhereHavingRole, whereclause), 1281 ) 1282 self._inline = inline 1283 if dialect_kw: 1284 self._validate_dialect_kwargs_deprecated(dialect_kw) 1285 self._return_defaults = return_defaults 1286 1287 @_generative 1288 def ordered_values(self, *args): 1289 """Specify the VALUES clause of this UPDATE statement with an explicit 1290 parameter ordering that will be maintained in the SET clause of the 1291 resulting UPDATE statement. 1292 1293 E.g.:: 1294 1295 stmt = table.update().ordered_values( 1296 ("name", "ed"), ("ident": "foo") 1297 ) 1298 1299 .. seealso:: 1300 1301 :ref:`updates_order_parameters` - full example of the 1302 :meth:`_expression.Update.ordered_values` method. 1303 1304 .. versionchanged:: 1.4 The :meth:`_expression.Update.ordered_values` 1305 method 1306 supersedes the 1307 :paramref:`_expression.update.preserve_parameter_order` 1308 parameter, which will be removed in SQLAlchemy 2.0. 1309 1310 """ 1311 if self._values: 1312 raise exc.ArgumentError( 1313 "This statement already has values present" 1314 ) 1315 elif self._ordered_values: 1316 raise exc.ArgumentError( 1317 "This statement already has ordered values present" 1318 ) 1319 1320 kv_generator = DMLState.get_plugin_class(self)._get_crud_kv_pairs 1321 self._ordered_values = kv_generator(self, args) 1322 1323 @_generative 1324 def inline(self): 1325 """Make this :class:`_expression.Update` construct "inline" . 1326 1327 When set, SQL defaults present on :class:`_schema.Column` 1328 objects via the 1329 ``default`` keyword will be compiled 'inline' into the statement and 1330 not pre-executed. This means that their values will not be available 1331 in the dictionary returned from 1332 :meth:`_engine.CursorResult.last_updated_params`. 1333 1334 .. versionchanged:: 1.4 the :paramref:`_expression.update.inline` 1335 parameter 1336 is now superseded by the :meth:`_expression.Update.inline` method. 1337 1338 """ 1339 self._inline = True 1340 1341 1342class Delete(DMLWhereBase, UpdateBase): 1343 """Represent a DELETE construct. 1344 1345 The :class:`_expression.Delete` object is created using the 1346 :func:`_expression.delete()` function. 1347 1348 """ 1349 1350 __visit_name__ = "delete" 1351 1352 is_delete = True 1353 1354 _traverse_internals = ( 1355 [ 1356 ("table", InternalTraversal.dp_clauseelement), 1357 ("_where_criteria", InternalTraversal.dp_clauseelement_list), 1358 ("_returning", InternalTraversal.dp_clauseelement_list), 1359 ("_hints", InternalTraversal.dp_table_hint_list), 1360 ] 1361 + HasPrefixes._has_prefixes_traverse_internals 1362 + DialectKWArgs._dialect_kwargs_traverse_internals 1363 + Executable._executable_traverse_internals 1364 + HasCTE._has_ctes_traverse_internals 1365 ) 1366 1367 @ValuesBase._constructor_20_deprecations( 1368 "delete", 1369 "Delete", 1370 ["whereclause", "values", "bind", "prefixes", "returning"], 1371 ) 1372 def __init__( 1373 self, 1374 table, 1375 whereclause=None, 1376 bind=None, 1377 returning=None, 1378 prefixes=None, 1379 **dialect_kw 1380 ): 1381 r"""Construct :class:`_expression.Delete` object. 1382 1383 E.g.:: 1384 1385 from sqlalchemy import delete 1386 1387 stmt = ( 1388 delete(user_table). 1389 where(user_table.c.id == 5) 1390 ) 1391 1392 Similar functionality is available via the 1393 :meth:`_expression.TableClause.delete` method on 1394 :class:`_schema.Table`. 1395 1396 .. seealso:: 1397 1398 :ref:`inserts_and_updates` - in the 1399 :ref:`1.x tutorial <sqlexpression_toplevel>` 1400 1401 :ref:`tutorial_core_update_delete` - in the :ref:`unified_tutorial` 1402 1403 1404 :param table: The table to delete rows from. 1405 1406 :param whereclause: Optional SQL expression describing the ``WHERE`` 1407 condition of the ``DELETE`` statement; is equivalent to using the 1408 more modern :meth:`~Delete.where()` method to specify the ``WHERE`` 1409 clause. 1410 1411 .. seealso:: 1412 1413 :ref:`deletes` - SQL Expression Tutorial 1414 1415 """ 1416 self._bind = bind 1417 self.table = coercions.expect( 1418 roles.DMLTableRole, table, apply_propagate_attrs=self 1419 ) 1420 if returning: 1421 self._returning = returning 1422 1423 if prefixes: 1424 self._setup_prefixes(prefixes) 1425 1426 if whereclause is not None: 1427 self._where_criteria += ( 1428 coercions.expect(roles.WhereHavingRole, whereclause), 1429 ) 1430 1431 if dialect_kw: 1432 self._validate_dialect_kwargs_deprecated(dialect_kw) 1433