Coverage for /pythoncovmergedfiles/medio/medio/usr/local/lib/python3.8/site-packages/sqlalchemy/sql/dml.py: 47%

1# sql/dml.py 

2# Copyright (C) 2009-2022 the SQLAlchemy authors and contributors 

3# <see AUTHORS file> 

4# 

5# This module is part of SQLAlchemy and is released under 

6# the MIT License: https://www.opensource.org/licenses/mit-license.php 

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 @classmethod 

52 def get_entity_description(cls, statement): 

53 return {"name": statement.table.name, "table": statement.table} 

54 

55 @classmethod 

56 def get_returning_column_descriptions(cls, statement): 

57 return [ 

58 { 

59 "name": c.key, 

60 "type": c.type, 

61 "expr": c, 

62 } 

63 for c in statement._all_selected_columns 

64 ] 

65 

66 @property 

67 def dml_table(self): 

68 return self.statement.table 

69 

70 @classmethod 

71 def _get_crud_kv_pairs(cls, statement, kv_iterator): 

72 return [ 

73 ( 

74 coercions.expect(roles.DMLColumnRole, k), 

75 coercions.expect( 

76 roles.ExpressionElementRole, 

77 v, 

78 type_=NullType(), 

79 is_crud=True, 

80 ), 

81 ) 

82 for k, v in kv_iterator 

83 ] 

84 

85 def _make_extra_froms(self, statement): 

86 froms = [] 

87 

88 all_tables = list(sql_util.tables_from_leftmost(statement.table)) 

89 seen = {all_tables[0]} 

90 

91 for crit in statement._where_criteria: 

92 for item in _from_objects(crit): 

93 if not seen.intersection(item._cloned_set): 

94 froms.append(item) 

95 seen.update(item._cloned_set) 

96 

97 froms.extend(all_tables[1:]) 

98 return froms 

99 

100 def _process_multi_values(self, statement): 

101 if not statement._supports_multi_parameters: 

102 raise exc.InvalidRequestError( 

103 "%s construct does not support " 

104 "multiple parameter sets." % statement.__visit_name__.upper() 

105 ) 

106 

107 for parameters in statement._multi_values: 

108 multi_parameters = [ 

109 { 

110 c.key: value 

111 for c, value in zip(statement.table.c, parameter_set) 

112 } 

113 if isinstance(parameter_set, collections_abc.Sequence) 

114 else parameter_set 

115 for parameter_set in parameters 

116 ] 

117 

118 if self._no_parameters: 

119 self._no_parameters = False 

120 self._has_multi_parameters = True 

121 self._multi_parameters = multi_parameters 

122 self._dict_parameters = self._multi_parameters[0] 

123 elif not self._has_multi_parameters: 

124 self._cant_mix_formats_error() 

125 else: 

126 self._multi_parameters.extend(multi_parameters) 

127 

128 def _process_values(self, statement): 

129 if self._no_parameters: 

130 self._has_multi_parameters = False 

131 self._dict_parameters = statement._values 

132 self._no_parameters = False 

133 elif self._has_multi_parameters: 

134 self._cant_mix_formats_error() 

135 

136 def _process_ordered_values(self, statement): 

137 parameters = statement._ordered_values 

138 

139 if self._no_parameters: 

140 self._no_parameters = False 

141 self._dict_parameters = dict(parameters) 

142 self._ordered_values = parameters 

143 self._parameter_ordering = [key for key, value in parameters] 

144 elif self._has_multi_parameters: 

145 self._cant_mix_formats_error() 

146 else: 

147 raise exc.InvalidRequestError( 

148 "Can only invoke ordered_values() once, and not mixed " 

149 "with any other values() call" 

150 ) 

151 

152 def _process_select_values(self, statement): 

153 parameters = { 

154 coercions.expect(roles.DMLColumnRole, name, as_key=True): Null() 

155 for name in statement._select_names 

156 } 

157 

158 if self._no_parameters: 

159 self._no_parameters = False 

160 self._dict_parameters = parameters 

161 else: 

162 # this condition normally not reachable as the Insert 

163 # does not allow this construction to occur 

164 assert False, "This statement already has parameters" 

165 

166 def _cant_mix_formats_error(self): 

167 raise exc.InvalidRequestError( 

168 "Can't mix single and multiple VALUES " 

169 "formats in one INSERT statement; one style appends to a " 

170 "list while the other replaces values, so the intent is " 

171 "ambiguous." 

172 ) 

173 

174 

175@CompileState.plugin_for("default", "insert") 

176class InsertDMLState(DMLState): 

177 isinsert = True 

178 

179 include_table_with_column_exprs = False 

180 

181 def __init__(self, statement, compiler, **kw): 

182 self.statement = statement 

183 

184 self.isinsert = True 

185 if statement._select_names: 

186 self._process_select_values(statement) 

187 if statement._values is not None: 

188 self._process_values(statement) 

189 if statement._multi_values: 

190 self._process_multi_values(statement) 

191 

192 @util.memoized_property 

193 def _insert_col_keys(self): 

194 # this is also done in crud.py -> _key_getters_for_crud_column 

195 return [ 

196 coercions.expect_as_key(roles.DMLColumnRole, col) 

197 for col in self._dict_parameters 

198 ] 

199 

200 

201@CompileState.plugin_for("default", "update") 

202class UpdateDMLState(DMLState): 

203 isupdate = True 

204 

205 include_table_with_column_exprs = False 

206 

207 def __init__(self, statement, compiler, **kw): 

208 self.statement = statement 

209 self.isupdate = True 

210 self._preserve_parameter_order = statement._preserve_parameter_order 

211 if statement._ordered_values is not None: 

212 self._process_ordered_values(statement) 

213 elif statement._values is not None: 

214 self._process_values(statement) 

215 elif statement._multi_values: 

216 self._process_multi_values(statement) 

217 self._extra_froms = ef = self._make_extra_froms(statement) 

218 self.is_multitable = mt = ef and self._dict_parameters 

219 self.include_table_with_column_exprs = ( 

220 mt and compiler.render_table_with_column_in_update_from 

221 ) 

222 

223 

224@CompileState.plugin_for("default", "delete") 

225class DeleteDMLState(DMLState): 

226 isdelete = True 

227 

228 def __init__(self, statement, compiler, **kw): 

229 self.statement = statement 

230 

231 self.isdelete = True 

232 self._extra_froms = self._make_extra_froms(statement) 

233 

234 

235class UpdateBase( 

236 roles.DMLRole, 

237 HasCTE, 

238 HasCompileState, 

239 DialectKWArgs, 

240 HasPrefixes, 

241 ReturnsRows, 

242 Executable, 

243 ClauseElement, 

244): 

245 """Form the base for ``INSERT``, ``UPDATE``, and ``DELETE`` statements.""" 

246 

247 __visit_name__ = "update_base" 

248 

249 _execution_options = Executable._execution_options.union( 

250 {"autocommit": True} 

251 ) 

252 _hints = util.immutabledict() 

253 named_with_column = False 

254 

255 _return_defaults = False 

256 _return_defaults_columns = None 

257 _returning = () 

258 

259 is_dml = True 

260 

261 @classmethod 

262 def _constructor_20_deprecations(cls, fn_name, clsname, names): 

263 

264 param_to_method_lookup = dict( 

265 whereclause=( 

266 "The :paramref:`%(func)s.whereclause` parameter " 

267 "will be removed " 

268 "in SQLAlchemy 2.0. Please refer to the " 

269 ":meth:`%(classname)s.where` method." 

270 ), 

271 values=( 

272 "The :paramref:`%(func)s.values` parameter will be removed " 

273 "in SQLAlchemy 2.0. Please refer to the " 

274 ":meth:`%(classname)s.values` method." 

275 ), 

276 bind=( 

277 "The :paramref:`%(func)s.bind` parameter will be removed in " 

278 "SQLAlchemy 2.0. Please use explicit connection execution." 

279 ), 

280 inline=( 

281 "The :paramref:`%(func)s.inline` parameter will be " 

282 "removed in " 

283 "SQLAlchemy 2.0. Please use the " 

284 ":meth:`%(classname)s.inline` method." 

285 ), 

286 prefixes=( 

287 "The :paramref:`%(func)s.prefixes parameter will be " 

288 "removed in " 

289 "SQLAlchemy 2.0. Please use the " 

290 ":meth:`%(classname)s.prefix_with` " 

291 "method." 

292 ), 

293 return_defaults=( 

294 "The :paramref:`%(func)s.return_defaults` parameter will be " 

295 "removed in SQLAlchemy 2.0. Please use the " 

296 ":meth:`%(classname)s.return_defaults` method." 

297 ), 

298 returning=( 

299 "The :paramref:`%(func)s.returning` parameter will be " 

300 "removed in SQLAlchemy 2.0. Please use the " 

301 ":meth:`%(classname)s.returning`` method." 

302 ), 

303 preserve_parameter_order=( 

304 "The :paramref:`%(func)s.preserve_parameter_order` parameter " 

305 "will be removed in SQLAlchemy 2.0. Use the " 

306 ":meth:`%(classname)s.ordered_values` method with a list " 

307 "of tuples. " 

308 ), 

309 ) 

310 

311 return util.deprecated_params( 

312 **{ 

313 name: ( 

314 "2.0", 

315 param_to_method_lookup[name] 

316 % { 

317 "func": "_expression.%s" % fn_name, 

318 "classname": "_expression.%s" % clsname, 

319 }, 

320 ) 

321 for name in names 

322 } 

323 ) 

324 

325 def _generate_fromclause_column_proxies(self, fromclause): 

326 fromclause._columns._populate_separate_keys( 

327 col._make_proxy(fromclause) for col in self._returning 

328 ) 

329 

330 def params(self, *arg, **kw): 

331 """Set the parameters for the statement. 

332 

333 This method raises ``NotImplementedError`` on the base class, 

334 and is overridden by :class:`.ValuesBase` to provide the 

335 SET/VALUES clause of UPDATE and INSERT. 

336 

337 """ 

338 raise NotImplementedError( 

339 "params() is not supported for INSERT/UPDATE/DELETE statements." 

340 " To set the values for an INSERT or UPDATE statement, use" 

341 " stmt.values(**parameters)." 

342 ) 

343 

344 @_generative 

345 def with_dialect_options(self, **opt): 

346 """Add dialect options to this INSERT/UPDATE/DELETE object. 

347 

348 e.g.:: 

349 

350 upd = table.update().dialect_options(mysql_limit=10) 

351 

352 .. versionadded: 1.4 - this method supersedes the dialect options 

353 associated with the constructor. 

354 

355 

356 """ 

357 self._validate_dialect_kwargs(opt) 

358 

359 def _validate_dialect_kwargs_deprecated(self, dialect_kw): 

360 util.warn_deprecated_20( 

361 "Passing dialect keyword arguments directly to the " 

362 "%s constructor is deprecated and will be removed in SQLAlchemy " 

363 "2.0. Please use the ``with_dialect_options()`` method." 

364 % (self.__class__.__name__) 

365 ) 

366 self._validate_dialect_kwargs(dialect_kw) 

367 

368 def bind(self): 

369 """Return a 'bind' linked to this :class:`.UpdateBase` 

370 or a :class:`_schema.Table` associated with it. 

371 

372 """ 

373 return self._bind or self.table.bind 

374 

375 def _set_bind(self, bind): 

376 self._bind = bind 

377 

378 bind = property(bind, _set_bind) 

379 

380 @_generative 

381 def returning(self, *cols): 

382 r"""Add a :term:`RETURNING` or equivalent clause to this statement. 

383 

384 e.g.: 

385 

386 .. sourcecode:: pycon+sql 

387 

388 >>> stmt = ( 

389 ... table.update() 

390 ... .where(table.c.data == "value") 

391 ... .values(status="X") 

392 ... .returning(table.c.server_flag, table.c.updated_timestamp) 

393 ... ) 

394 >>> print(stmt) 

395 UPDATE some_table SET status=:status 

396 WHERE some_table.data = :data_1 

397 RETURNING some_table.server_flag, some_table.updated_timestamp 

398 

399 The method may be invoked multiple times to add new entries to the 

400 list of expressions to be returned. 

401 

402 .. versionadded:: 1.4.0b2 The method may be invoked multiple times to 

403 add new entries to the list of expressions to be returned. 

404 

405 The given collection of column expressions should be derived from the 

406 table that is the target of the INSERT, UPDATE, or DELETE. While 

407 :class:`_schema.Column` objects are typical, the elements can also be 

408 expressions: 

409 

410 .. sourcecode:: pycon+sql 

411 

412 >>> stmt = table.insert().returning( 

413 ... (table.c.first_name + " " + table.c.last_name).label("fullname") 

414 ... ) 

415 >>> print(stmt) 

416 INSERT INTO some_table (first_name, last_name) 

417 VALUES (:first_name, :last_name) 

418 RETURNING some_table.first_name || :first_name_1 || some_table.last_name AS fullname 

419 

420 Upon compilation, a RETURNING clause, or database equivalent, 

421 will be rendered within the statement. For INSERT and UPDATE, 

422 the values are the newly inserted/updated values. For DELETE, 

423 the values are those of the rows which were deleted. 

424 

425 Upon execution, the values of the columns to be returned are made 

426 available via the result set and can be iterated using 

427 :meth:`_engine.CursorResult.fetchone` and similar. 

428 For DBAPIs which do not 

429 natively support returning values (i.e. cx_oracle), SQLAlchemy will 

430 approximate this behavior at the result level so that a reasonable 

431 amount of behavioral neutrality is provided. 

432 

433 Note that not all databases/DBAPIs 

434 support RETURNING. For those backends with no support, 

435 an exception is raised upon compilation and/or execution. 

436 For those who do support it, the functionality across backends 

437 varies greatly, including restrictions on executemany() 

438 and other statements which return multiple rows. Please 

439 read the documentation notes for the database in use in 

440 order to determine the availability of RETURNING. 

441 

442 .. seealso:: 

443 

444 :meth:`.ValuesBase.return_defaults` - an alternative method tailored 

445 towards efficient fetching of server-side defaults and triggers 

446 for single-row INSERTs or UPDATEs. 

447 

448 :ref:`tutorial_insert_returning` - in the :ref:`unified_tutorial` 

449 

450 """ # noqa: E501 

451 if self._return_defaults: 

452 raise exc.InvalidRequestError( 

453 "return_defaults() is already configured on this statement" 

454 ) 

455 self._returning += tuple( 

456 coercions.expect(roles.ColumnsClauseRole, c) for c in cols 

457 ) 

458 

459 @property 

460 def _all_selected_columns(self): 

461 return self._returning 

462 

463 @property 

464 def exported_columns(self): 

465 """Return the RETURNING columns as a column collection for this 

466 statement. 

467 

468 .. versionadded:: 1.4 

469 

470 """ 

471 # TODO: no coverage here 

472 return ColumnCollection( 

473 (c.key, c) for c in self._all_selected_columns 

474 ).as_immutable() 

475 

476 @_generative 

477 def with_hint(self, text, selectable=None, dialect_name="*"): 

478 """Add a table hint for a single table to this 

479 INSERT/UPDATE/DELETE statement. 

480 

481 .. note:: 

482 

483 :meth:`.UpdateBase.with_hint` currently applies only to 

484 Microsoft SQL Server. For MySQL INSERT/UPDATE/DELETE hints, use 

485 :meth:`.UpdateBase.prefix_with`. 

486 

487 The text of the hint is rendered in the appropriate 

488 location for the database backend in use, relative 

489 to the :class:`_schema.Table` that is the subject of this 

490 statement, or optionally to that of the given 

491 :class:`_schema.Table` passed as the ``selectable`` argument. 

492 

493 The ``dialect_name`` option will limit the rendering of a particular 

494 hint to a particular backend. Such as, to add a hint 

495 that only takes effect for SQL Server:: 

496 

497 mytable.insert().with_hint("WITH (PAGLOCK)", dialect_name="mssql") 

498 

499 :param text: Text of the hint. 

500 :param selectable: optional :class:`_schema.Table` that specifies 

501 an element of the FROM clause within an UPDATE or DELETE 

502 to be the subject of the hint - applies only to certain backends. 

503 :param dialect_name: defaults to ``*``, if specified as the name 

504 of a particular dialect, will apply these hints only when 

505 that dialect is in use. 

506 """ 

507 if selectable is None: 

508 selectable = self.table 

509 

510 self._hints = self._hints.union({(selectable, dialect_name): text}) 

511 

512 @property 

513 def entity_description(self): 

514 """Return a :term:`plugin-enabled` description of the table and/or 

515 entity which this DML construct is operating against. 

516 

517 This attribute is generally useful when using the ORM, as an 

518 extended structure which includes information about mapped 

519 entities is returned. The section :ref:`queryguide_inspection` 

520 contains more background. 

521 

522 For a Core statement, the structure returned by this accessor 

523 is derived from the :attr:`.UpdateBase.table` attribute, and 

524 refers to the :class:`.Table` being inserted, updated, or deleted:: 

525 

526 >>> stmt = insert(user_table) 

527 >>> stmt.entity_description 

528 { 

529 "name": "user_table", 

530 "table": Table("user_table", ...) 

531 } 

532 

533 .. versionadded:: 1.4.33 

534 

535 .. seealso:: 

536 

537 :attr:`.UpdateBase.returning_column_descriptions` 

538 

539 :attr:`.Select.column_descriptions` - entity information for 

540 a :func:`.select` construct 

541 

542 :ref:`queryguide_inspection` - ORM background 

543 

544 """ 

545 meth = DMLState.get_plugin_class(self).get_entity_description 

546 return meth(self) 

547 

548 @property 

549 def returning_column_descriptions(self): 

550 """Return a :term:`plugin-enabled` description of the columns 

551 which this DML construct is RETURNING against, in other words 

552 the expressions established as part of :meth:`.UpdateBase.returning`. 

553 

554 This attribute is generally useful when using the ORM, as an 

555 extended structure which includes information about mapped 

556 entities is returned. The section :ref:`queryguide_inspection` 

557 contains more background. 

558 

559 For a Core statement, the structure returned by this accessor is 

560 derived from the same objects that are returned by the 

561 :attr:`.UpdateBase.exported_columns` accessor:: 

562 

563 >>> stmt = insert(user_table).returning(user_table.c.id, user_table.c.name) 

564 >>> stmt.entity_description 

565 [ 

566 { 

567 "name": "id", 

568 "type": Integer, 

569 "expr": Column("id", Integer(), table=<user>, ...) 

570 }, 

571 { 

572 "name": "name", 

573 "type": String(), 

574 "expr": Column("name", String(), table=<user>, ...) 

575 }, 

576 ] 

577 

578 .. versionadded:: 1.4.33 

579 

580 .. seealso:: 

581 

582 :attr:`.UpdateBase.entity_description` 

583 

584 :attr:`.Select.column_descriptions` - entity information for 

585 a :func:`.select` construct 

586 

587 :ref:`queryguide_inspection` - ORM background 

588 

589 """ # noqa: E501 

590 meth = DMLState.get_plugin_class( 

591 self 

592 ).get_returning_column_descriptions 

593 return meth(self) 

594 

595 

596class ValuesBase(UpdateBase): 

597 """Supplies support for :meth:`.ValuesBase.values` to 

598 INSERT and UPDATE constructs.""" 

599 

600 __visit_name__ = "values_base" 

601 

602 _supports_multi_parameters = False 

603 _preserve_parameter_order = False 

604 select = None 

605 _post_values_clause = None 

606 

607 _values = None 

608 _multi_values = () 

609 _ordered_values = None 

610 _select_names = None 

611 

612 _returning = () 

613 

614 def __init__(self, table, values, prefixes): 

615 self.table = coercions.expect( 

616 roles.DMLTableRole, table, apply_propagate_attrs=self 

617 ) 

618 if values is not None: 

619 self.values.non_generative(self, values) 

620 if prefixes: 

621 self._setup_prefixes(prefixes) 

622 

623 @_generative 

624 @_exclusive_against( 

625 "_select_names", 

626 "_ordered_values", 

627 msgs={ 

628 "_select_names": "This construct already inserts from a SELECT", 

629 "_ordered_values": "This statement already has ordered " 

630 "values present", 

631 }, 

632 ) 

633 def values(self, *args, **kwargs): 

634 r"""Specify a fixed VALUES clause for an INSERT statement, or the SET 

635 clause for an UPDATE. 

636 

637 Note that the :class:`_expression.Insert` and 

638 :class:`_expression.Update` 

639 constructs support 

640 per-execution time formatting of the VALUES and/or SET clauses, 

641 based on the arguments passed to :meth:`_engine.Connection.execute`. 

642 However, the :meth:`.ValuesBase.values` method can be used to "fix" a 

643 particular set of parameters into the statement. 

644 

645 Multiple calls to :meth:`.ValuesBase.values` will produce a new 

646 construct, each one with the parameter list modified to include 

647 the new parameters sent. In the typical case of a single 

648 dictionary of parameters, the newly passed keys will replace 

649 the same keys in the previous construct. In the case of a list-based 

650 "multiple values" construct, each new list of values is extended 

651 onto the existing list of values. 

652 

653 :param \**kwargs: key value pairs representing the string key 

654 of a :class:`_schema.Column` 

655 mapped to the value to be rendered into the 

656 VALUES or SET clause:: 

657 

658 users.insert().values(name="some name") 

659 

660 users.update().where(users.c.id==5).values(name="some name") 

661 

662 :param \*args: As an alternative to passing key/value parameters, 

663 a dictionary, tuple, or list of dictionaries or tuples can be passed 

664 as a single positional argument in order to form the VALUES or 

665 SET clause of the statement. The forms that are accepted vary 

666 based on whether this is an :class:`_expression.Insert` or an 

667 :class:`_expression.Update` construct. 

668 

669 For either an :class:`_expression.Insert` or 

670 :class:`_expression.Update` 

671 construct, a single dictionary can be passed, which works the same as 

672 that of the kwargs form:: 

673 

674 users.insert().values({"name": "some name"}) 

675 

676 users.update().values({"name": "some new name"}) 

677 

678 Also for either form but more typically for the 

679 :class:`_expression.Insert` construct, a tuple that contains an 

680 entry for every column in the table is also accepted:: 

681 

682 users.insert().values((5, "some name")) 

683 

684 The :class:`_expression.Insert` construct also supports being 

685 passed a list of dictionaries or full-table-tuples, which on the 

686 server will render the less common SQL syntax of "multiple values" - 

687 this syntax is supported on backends such as SQLite, PostgreSQL, 

688 MySQL, but not necessarily others:: 

689 

690 users.insert().values([ 

691 {"name": "some name"}, 

692 {"name": "some other name"}, 

693 {"name": "yet another name"}, 

694 ]) 

695 

696 The above form would render a multiple VALUES statement similar to:: 

697 

698 INSERT INTO users (name) VALUES 

699 (:name_1), 

700 (:name_2), 

701 (:name_3) 

702 

703 It is essential to note that **passing multiple values is 

704 NOT the same as using traditional executemany() form**. The above 

705 syntax is a **special** syntax not typically used. To emit an 

706 INSERT statement against multiple rows, the normal method is 

707 to pass a multiple values list to the 

708 :meth:`_engine.Connection.execute` 

709 method, which is supported by all database backends and is generally 

710 more efficient for a very large number of parameters. 

711 

712 .. seealso:: 

713 

714 :ref:`tutorial_multiple_parameters` - an introduction to 

715 the traditional Core method of multiple parameter set 

716 invocation for INSERTs and other statements. 

717 

718 .. versionchanged:: 1.0.0 an INSERT that uses a multiple-VALUES 

719 clause, even a list of length one, 

720 implies that the :paramref:`_expression.Insert.inline` 

721 flag is set to 

722 True, indicating that the statement will not attempt to fetch 

723 the "last inserted primary key" or other defaults. The 

724 statement deals with an arbitrary number of rows, so the 

725 :attr:`_engine.CursorResult.inserted_primary_key` 

726 accessor does not 

727 apply. 

728 

729 .. versionchanged:: 1.0.0 A multiple-VALUES INSERT now supports 

730 columns with Python side default values and callables in the 

731 same way as that of an "executemany" style of invocation; the 

732 callable is invoked for each row. See :ref:`bug_3288` 

733 for other details. 

734 

735 The UPDATE construct also supports rendering the SET parameters 

736 in a specific order. For this feature refer to the 

737 :meth:`_expression.Update.ordered_values` method. 

738 

739 .. seealso:: 

740 

741 :meth:`_expression.Update.ordered_values` 

742 

743 

744 """ 

745 if args: 

746 # positional case. this is currently expensive. we don't 

747 # yet have positional-only args so we have to check the length. 

748 # then we need to check multiparams vs. single dictionary. 

749 # since the parameter format is needed in order to determine 

750 # a cache key, we need to determine this up front. 

751 arg = args[0] 

752 

753 if kwargs: 

754 raise exc.ArgumentError( 

755 "Can't pass positional and kwargs to values() " 

756 "simultaneously" 

757 ) 

758 elif len(args) > 1: 

759 raise exc.ArgumentError( 

760 "Only a single dictionary/tuple or list of " 

761 "dictionaries/tuples is accepted positionally." 

762 ) 

763 

764 elif not self._preserve_parameter_order and isinstance( 

765 arg, collections_abc.Sequence 

766 ): 

767 

768 if arg and isinstance(arg[0], (list, dict, tuple)): 

769 self._multi_values += (arg,) 

770 return 

771 

772 # tuple values 

773 arg = {c.key: value for c, value in zip(self.table.c, arg)} 

774 elif self._preserve_parameter_order and not isinstance( 

775 arg, collections_abc.Sequence 

776 ): 

777 raise ValueError( 

778 "When preserve_parameter_order is True, " 

779 "values() only accepts a list of 2-tuples" 

780 ) 

781 

782 else: 

783 # kwarg path. this is the most common path for non-multi-params 

784 # so this is fairly quick. 

785 arg = kwargs 

786 if args: 

787 raise exc.ArgumentError( 

788 "Only a single dictionary/tuple or list of " 

789 "dictionaries/tuples is accepted positionally." 

790 ) 

791 

792 # for top level values(), convert literals to anonymous bound 

793 # parameters at statement construction time, so that these values can 

794 # participate in the cache key process like any other ClauseElement. 

795 # crud.py now intercepts bound parameters with unique=True from here 

796 # and ensures they get the "crud"-style name when rendered. 

797 

798 kv_generator = DMLState.get_plugin_class(self)._get_crud_kv_pairs 

799 

800 if self._preserve_parameter_order: 

801 self._ordered_values = kv_generator(self, arg) 

802 else: 

803 arg = {k: v for k, v in kv_generator(self, arg.items())} 

804 if self._values: 

805 self._values = self._values.union(arg) 

806 else: 

807 self._values = util.immutabledict(arg) 

808 

809 @_generative 

810 @_exclusive_against( 

811 "_returning", 

812 msgs={ 

813 "_returning": "RETURNING is already configured on this statement" 

814 }, 

815 defaults={"_returning": _returning}, 

816 ) 

817 def return_defaults(self, *cols): 

818 """Make use of a :term:`RETURNING` clause for the purpose 

819 of fetching server-side expressions and defaults. 

820 

821 E.g.:: 

822 

823 stmt = table.insert().values(data='newdata').return_defaults() 

824 

825 result = connection.execute(stmt) 

826 

827 server_created_at = result.returned_defaults['created_at'] 

828 

829 When used against a backend that supports RETURNING, all column 

830 values generated by SQL expression or server-side-default will be 

831 added to any existing RETURNING clause, provided that 

832 :meth:`.UpdateBase.returning` is not used simultaneously. The column 

833 values will then be available on the result using the 

834 :attr:`_engine.CursorResult.returned_defaults` accessor as 

835 a dictionary, 

836 referring to values keyed to the :class:`_schema.Column` 

837 object as well as 

838 its ``.key``. 

839 

840 This method differs from :meth:`.UpdateBase.returning` in these ways: 

841 

842 1. :meth:`.ValuesBase.return_defaults` is only intended for use with an 

843 INSERT or an UPDATE statement that matches exactly one row per 

844 parameter set. While the RETURNING construct in the general sense 

845 supports multiple rows for a multi-row UPDATE or DELETE statement, 

846 or for special cases of INSERT that return multiple rows (e.g. 

847 INSERT from SELECT, multi-valued VALUES clause), 

848 :meth:`.ValuesBase.return_defaults` is intended only for an 

849 "ORM-style" single-row INSERT/UPDATE statement. The row 

850 returned by the statement is also consumed implicitly when 

851 :meth:`.ValuesBase.return_defaults` is used. By contrast, 

852 :meth:`.UpdateBase.returning` leaves the RETURNING result-set intact 

853 with a collection of any number of rows. 

854 

855 2. It is compatible with the existing logic to fetch auto-generated 

856 primary key values, also known as "implicit returning". Backends 

857 that support RETURNING will automatically make use of RETURNING in 

858 order to fetch the value of newly generated primary keys; while the 

859 :meth:`.UpdateBase.returning` method circumvents this behavior, 

860 :meth:`.ValuesBase.return_defaults` leaves it intact. 

861 

862 3. It can be called against any backend. Backends that don't support 

863 RETURNING will skip the usage of the feature, rather than raising 

864 an exception. The return value of 

865 :attr:`_engine.CursorResult.returned_defaults` will be ``None`` 

866 

867 4. An INSERT statement invoked with executemany() is supported if the 

868 backend database driver supports the 

869 ``insert_executemany_returning`` feature, currently this includes 

870 PostgreSQL with psycopg2. When executemany is used, the 

871 :attr:`_engine.CursorResult.returned_defaults_rows` and 

872 :attr:`_engine.CursorResult.inserted_primary_key_rows` accessors 

873 will return the inserted defaults and primary keys. 

874 

875 .. versionadded:: 1.4 

876 

877 :meth:`.ValuesBase.return_defaults` is used by the ORM to provide 

878 an efficient implementation for the ``eager_defaults`` feature of 

879 :func:`.mapper`. 

880 

881 :param cols: optional list of column key names or 

882 :class:`_schema.Column` 

883 objects. If omitted, all column expressions evaluated on the server 

884 are added to the returning list. 

885 

886 .. versionadded:: 0.9.0 

887 

888 .. seealso:: 

889 

890 :meth:`.UpdateBase.returning` 

891 

892 :attr:`_engine.CursorResult.returned_defaults` 

893 

894 :attr:`_engine.CursorResult.returned_defaults_rows` 

895 

896 :attr:`_engine.CursorResult.inserted_primary_key` 

897 

898 :attr:`_engine.CursorResult.inserted_primary_key_rows` 

899 

900 """ 

901 self._return_defaults = True 

902 self._return_defaults_columns = cols 

903 

904 

905class Insert(ValuesBase): 

906 """Represent an INSERT construct. 

907 

908 The :class:`_expression.Insert` object is created using the 

909 :func:`_expression.insert()` function. 

910 

911 """ 

912 

913 __visit_name__ = "insert" 

914 

915 _supports_multi_parameters = True 

916 

917 select = None 

918 include_insert_from_select_defaults = False 

919 

920 is_insert = True 

921 

922 _traverse_internals = ( 

923 [ 

924 ("table", InternalTraversal.dp_clauseelement), 

925 ("_inline", InternalTraversal.dp_boolean), 

926 ("_select_names", InternalTraversal.dp_string_list), 

927 ("_values", InternalTraversal.dp_dml_values), 

928 ("_multi_values", InternalTraversal.dp_dml_multi_values), 

929 ("select", InternalTraversal.dp_clauseelement), 

930 ("_post_values_clause", InternalTraversal.dp_clauseelement), 

931 ("_returning", InternalTraversal.dp_clauseelement_list), 

932 ("_hints", InternalTraversal.dp_table_hint_list), 

933 ("_return_defaults", InternalTraversal.dp_boolean), 

934 ( 

935 "_return_defaults_columns", 

936 InternalTraversal.dp_clauseelement_list, 

937 ), 

938 ] 

939 + HasPrefixes._has_prefixes_traverse_internals 

940 + DialectKWArgs._dialect_kwargs_traverse_internals 

941 + Executable._executable_traverse_internals 

942 + HasCTE._has_ctes_traverse_internals 

943 ) 

944 

945 @ValuesBase._constructor_20_deprecations( 

946 "insert", 

947 "Insert", 

948 [ 

949 "values", 

950 "inline", 

951 "bind", 

952 "prefixes", 

953 "returning", 

954 "return_defaults", 

955 ], 

956 ) 

957 def __init__( 

958 self, 

959 table, 

960 values=None, 

961 inline=False, 

962 bind=None, 

963 prefixes=None, 

964 returning=None, 

965 return_defaults=False, 

966 **dialect_kw 

967 ): 

968 """Construct an :class:`_expression.Insert` object. 

969 

970 E.g.:: 

971 

972 from sqlalchemy import insert 

973 

974 stmt = ( 

975 insert(user_table). 

976 values(name='username', fullname='Full Username') 

977 ) 

978 

979 Similar functionality is available via the 

980 :meth:`_expression.TableClause.insert` method on 

981 :class:`_schema.Table`. 

982 

983 .. seealso:: 

984 

985 :ref:`tutorial_core_insert` - in the :ref:`unified_tutorial` 

986 

987 

988 :param table: :class:`_expression.TableClause` 

989 which is the subject of the 

990 insert. 

991 

992 :param values: collection of values to be inserted; see 

993 :meth:`_expression.Insert.values` 

994 for a description of allowed formats here. 

995 Can be omitted entirely; a :class:`_expression.Insert` construct 

996 will also dynamically render the VALUES clause at execution time 

997 based on the parameters passed to :meth:`_engine.Connection.execute`. 

998 

999 :param inline: if True, no attempt will be made to retrieve the 

1000 SQL-generated default values to be provided within the statement; 

1001 in particular, 

1002 this allows SQL expressions to be rendered 'inline' within the 

1003 statement without the need to pre-execute them beforehand; for 

1004 backends that support "returning", this turns off the "implicit 

1005 returning" feature for the statement. 

1006 

1007 If both :paramref:`_expression.Insert.values` and compile-time bind 

1008 parameters are present, the compile-time bind parameters override the 

1009 information specified within :paramref:`_expression.Insert.values` on a 

1010 per-key basis. 

1011 

1012 The keys within :paramref:`_expression.Insert.values` can be either 

1013 :class:`~sqlalchemy.schema.Column` objects or their string 

1014 identifiers. Each key may reference one of: 

1015 

1016 * a literal data value (i.e. string, number, etc.); 

1017 * a Column object; 

1018 * a SELECT statement. 

1019 

1020 If a ``SELECT`` statement is specified which references this 

1021 ``INSERT`` statement's table, the statement will be correlated 

1022 against the ``INSERT`` statement. 

1023 

1024 .. seealso:: 

1025 

1026 :ref:`tutorial_core_insert` - in the :ref:`unified_tutorial` 

1027 

1028 """ 

1029 super(Insert, self).__init__(table, values, prefixes) 

1030 self._bind = bind 

1031 self._inline = inline 

1032 if returning: 

1033 self._returning = returning 

1034 if dialect_kw: 

1035 self._validate_dialect_kwargs_deprecated(dialect_kw) 

1036 

1037 if return_defaults: 

1038 self._return_defaults = True 

1039 if not isinstance(return_defaults, bool): 

1040 self._return_defaults_columns = return_defaults 

1041 

1042 @_generative 

1043 def inline(self): 

1044 """Make this :class:`_expression.Insert` construct "inline" . 

1045 

1046 When set, no attempt will be made to retrieve the 

1047 SQL-generated default values to be provided within the statement; 

1048 in particular, 

1049 this allows SQL expressions to be rendered 'inline' within the 

1050 statement without the need to pre-execute them beforehand; for 

1051 backends that support "returning", this turns off the "implicit 

1052 returning" feature for the statement. 

1053 

1054 

1055 .. versionchanged:: 1.4 the :paramref:`_expression.Insert.inline` 

1056 parameter 

1057 is now superseded by the :meth:`_expression.Insert.inline` method. 

1058 

1059 """ 

1060 self._inline = True 

1061 

1062 @_generative 

1063 def from_select(self, names, select, include_defaults=True): 

1064 """Return a new :class:`_expression.Insert` construct which represents 

1065 an ``INSERT...FROM SELECT`` statement. 

1066 

1067 e.g.:: 

1068 

1069 sel = select(table1.c.a, table1.c.b).where(table1.c.c > 5) 

1070 ins = table2.insert().from_select(['a', 'b'], sel) 

1071 

1072 :param names: a sequence of string column names or 

1073 :class:`_schema.Column` 

1074 objects representing the target columns. 

1075 :param select: a :func:`_expression.select` construct, 

1076 :class:`_expression.FromClause` 

1077 or other construct which resolves into a 

1078 :class:`_expression.FromClause`, 

1079 such as an ORM :class:`_query.Query` object, etc. The order of 

1080 columns returned from this FROM clause should correspond to the 

1081 order of columns sent as the ``names`` parameter; while this 

1082 is not checked before passing along to the database, the database 

1083 would normally raise an exception if these column lists don't 

1084 correspond. 

1085 :param include_defaults: if True, non-server default values and 

1086 SQL expressions as specified on :class:`_schema.Column` objects 

1087 (as documented in :ref:`metadata_defaults_toplevel`) not 

1088 otherwise specified in the list of names will be rendered