Insert, Updates, Deletes

INSERT, UPDATE and DELETE statements build on a hierarchy startingwith UpdateBase. The Insert and Updateconstructs build on the intermediary ValuesBase.

  • sqlalchemy.sql.expression.delete(table, whereclause=None, bind=None, returning=None, prefixes=None, **dialect_kw)
  • Construct Delete object.

Similar functionality is available via thedelete() method onTable.

  • Parameters

    • table – The table to delete rows from.

    • whereclause –

      • A ClauseElement describing the WHERE
      • condition of the DELETE statement. Note that thewhere() generative method may be used instead.

The WHERE clause can refer to multiple tables.For databases which support this, a DELETE..USING or similarclause will be generated. The statementwill fail on databases that don’t have support for multi-tabledelete statements. A SQL-standard method of referring toadditional tables in the WHERE clause is to use a correlatedsubquery:

  1. users.delete().where(
  2.         users.c.name==select([addresses.c.email_address]).                                where(addresses.c.user_id==users.c.id).                                as_scalar()
  3.         )

Changed in version 1.2.0: The WHERE clause of DELETE can refer to multiple tables.

See also

Deletes - SQL Expression Tutorial

  • sqlalchemy.sql.expression.insert(table, values=None, inline=False, bind=None, prefixes=None, returning=None, return_defaults=False, **dialect_kw)
  • Construct an Insert object.

Similar functionality is available via theinsert() method onTable.

  • Parameters

    • table – TableClause which is the subject of theinsert.

    • values – collection of values to be inserted; seeInsert.values() for a description of allowed formats here.Can be omitted entirely; a Insert construct will alsodynamically render the VALUES clause at execution time based onthe parameters passed to Connection.execute().

    • inline – if True, no attempt will be made to retrieve theSQL-generated default values to be provided within the statement;in particular,this allows SQL expressions to be rendered ‘inline’ within thestatement without the need to pre-execute them beforehand; forbackends that support “returning”, this turns off the “implicitreturning” feature for the statement.

If both values and compile-time bind parameters are present, thecompile-time bind parameters override the information specifiedwithin values on a per-key basis.

The keys within values can be eitherColumn objects or their stringidentifiers. Each key may reference one of:

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

  • a Column object;

  • a SELECT statement.

If a SELECT statement is specified which references thisINSERT statement’s table, the statement will be correlatedagainst the INSERT statement.

See also

Insert Expressions - SQL Expression Tutorial

Inserts, Updates and Deletes - SQL Expression Tutorial

  • sqlalchemy.sql.expression.update(table, whereclause=None, values=None, inline=False, bind=None, prefixes=None, returning=None, return_defaults=False, preserve_parameter_order=False, **dialect_kw)
  • Construct an Update object.

E.g.:

  1. from sqlalchemy import update
  2.  
  3. stmt = update(users).where(users.c.id==5).\
  4.         values(name='user #5')

Similar functionality is available via theupdate() method onTable:

  1. stmt = users.update().\
  2.             where(users.c.id==5).\
  3.             values(name='user #5')
  • Parameters

    • table – A Table object representing the databasetable to be updated.

    • whereclause –

Optional SQL expression describing the WHEREcondition of the UPDATE statement. Modern applicationsmay prefer to use the generative where()method to specify the WHERE clause.

The WHERE clause can refer to multiple tables.For databases which support this, an UPDATE FROM clause willbe generated, or on MySQL, a multi-table update. The statementwill fail on databases that don’t have support for multi-tableupdate statements. A SQL-standard method of referring toadditional tables in the WHERE clause is to use a correlatedsubquery:

  1. users.update().values(name='ed').where(
  2.         users.c.name==select([addresses.c.email_address]).\
  3.                     where(addresses.c.user_id==users.c.id).\
  4.                     as_scalar()
  5.         )
  1. -

values –

Optional dictionary which specifies the SET conditions of theUPDATE. If left as None, the SETconditions are determined from those parameters passed to thestatement during the execution and/or compilation of thestatement. When compiled standalone without any parameters,the SET clause generates for all columns.

Modern applications may prefer to use the generativeUpdate.values() method to set the values of theUPDATE statement.

  1. -

inline – if True, SQL defaults present on Column objects viathe default keyword will be compiled ‘inline’ into the statementand not pre-executed. This means that their values will notbe available in the dictionary returned fromResultProxy.last_updated_params().

  1. -

preserve_parameter_order –

if True, the update statement isexpected to receive parameters only via theUpdate.values() method, and they must be passed as a Pythonlist of 2-tuples. The rendered UPDATE statement will emit the SETclause for each referenced column maintaining this order.

New in version 1.0.10.

See also

Parameter-Ordered Updates - full example of thepreserve_parameter_order flag

If both values and compile-time bind parameters are present, thecompile-time bind parameters override the information specifiedwithin values on a per-key basis.

The keys within values can be either Columnobjects or their string identifiers (specifically the “key” of theColumn, normally but not necessarily equivalent toits “name”). Normally, theColumn objects used here are expected to bepart of the target Table that is the tableto be updated. However when using MySQL, a multiple-tableUPDATE statement can refer to columns from any ofthe tables referred to in the WHERE clause.

The values referred to in values are typically:

  • a literal data value (i.e. string, number, etc.)

  • a SQL expression, such as a related Column,a scalar-returning select() construct,etc.

When combining select() constructs within the valuesclause of an update() construct,the subquery represented by the select() should becorrelated to the parent table, that is, providing criterionwhich links the table inside the subquery to the outer tablebeing updated:

  1. users.update().values(
  2.         name=select([addresses.c.email_address]).\
  3.                 where(addresses.c.user_id==users.c.id).\
  4.                 as_scalar()
  5.     )

See also

Inserts, Updates and Deletes - SQL ExpressionLanguage Tutorial

Represent a DELETE construct.

The Delete object is created using the delete()function.

  • eq()

inherited from the eq() method of object

Return self==value.

  • init(table, whereclause=None, bind=None, returning=None, prefixes=None, **dialect_kw)
  • Construct a new Delete object.

This constructor is mirrored as a public API function; see delete() for a full usage and argument description.

  • le()

inherited from the le() method of object

Return self<=value.

  • lt()

inherited from the lt() method of object

Return self

  • class sqlalchemy.sql.expression.Insert(table, values=None, inline=False, bind=None, prefixes=None, returning=None, return_defaults=False, **dialect_kw)
  • Bases: sqlalchemy.sql.expression.ValuesBase

Represent an INSERT construct.

The Insert object is created using theinsert() function.

See also

Insert Expressions

  • eq()

inherited from the eq() method of object

Return self==value.

  • init(table, values=None, inline=False, bind=None, prefixes=None, returning=None, return_defaults=False, **dialect_kw)
  • Construct a new Insert object.

This constructor is mirrored as a public API function; see insert() for a full usage and argument description.

  • le()

inherited from the le() method of object

Return self<=value.

  • lt()

inherited from the lt() method of object

Return self

  • class sqlalchemy.sql.expression.Update(table, whereclause=None, values=None, inline=False, bind=None, prefixes=None, returning=None, return_defaults=False, preserve_parameter_order=False, **dialect_kw)
  • Bases: sqlalchemy.sql.expression.ValuesBase

Represent an Update construct.

The Update object is created using the update()function.

  • eq()

inherited from the eq() method of object

Return self==value.

  • init(table, whereclause=None, values=None, inline=False, bind=None, prefixes=None, returning=None, return_defaults=False, preserve_parameter_order=False, **dialect_kw)
  • Construct a new Update object.

This constructor is mirrored as a public API function; see update() for a full usage and argument description.

  • le()

inherited from the le() method of object

Return self<=value.

  • lt()

inherited from the lt() method of object

Return self

Form the base for INSERT, UPDATE, and DELETE statements.

  • eq()

inherited from the eq() method of object

Return self==value.

  • init()

inherited from the init() method of object

Initialize self. See help(type(self)) for accurate signature.

  • le()

inherited from the le() method of object

Return self<=value.

  • lt()

inherited from the lt() method of object

Return self

Supplies support for ValuesBase.values() toINSERT and UPDATE constructs.

  • returndefaults(*cols_)
  • Make use of a RETURNING clause for the purposeof fetching server-side expressions and defaults.

E.g.:

  1. stmt = table.insert().values(data='newdata').return_defaults()
  2.  
  3. result = connection.execute(stmt)
  4.  
  5. server_created_at = result.returned_defaults['created_at']

When used against a backend that supports RETURNING, all columnvalues generated by SQL expression or server-side-default will beadded to any existing RETURNING clause, provided thatUpdateBase.returning() is not used simultaneously. The columnvalues will then be available on the result using theResultProxy.returned_defaults accessor as a dictionary,referring to values keyed to the Column object as well asits .key.

This method differs from UpdateBase.returning() in these ways:

  1. -

ValuesBase.return_defaults() is only intended for use withan INSERT or an UPDATE statement that matches exactly one row.While the RETURNING construct in the general sense supportsmultiple rows for a multi-row UPDATE or DELETE statement, or forspecial cases of INSERT that return multiple rows (e.g. INSERT fromSELECT, multi-valued VALUES clause),ValuesBase.return_defaults() is intended only for an“ORM-style” single-row INSERT/UPDATE statement. The row returnedby the statement is also consumed implicitly whenValuesBase.return_defaults() is used. By contrast,UpdateBase.returning() leaves the RETURNING result-setintact with a collection of any number of rows.

  1. -

It is compatible with the existing logic to fetch auto-generatedprimary key values, also known as “implicit returning”. Backendsthat support RETURNING will automatically make use of RETURNING inorder to fetch the value of newly generated primary keys; while theUpdateBase.returning() method circumvents this behavior,ValuesBase.return_defaults() leaves it intact.

  1. -

It can be called against any backend. Backends that don’t supportRETURNING will skip the usage of the feature, rather than raisingan exception. The return value ofResultProxy.returned_defaults will be None

ValuesBase.return_defaults() is used by the ORM to providean efficient implementation for the eager_defaults feature ofmapper().

  1. - Parameters
  2. -

cols – optional list of column key names or Columnobjects. If omitted, all column expressions evaluated on the serverare added to the returning list.

New in version 0.9.0.

See also

UpdateBase.returning()

ResultProxy.returned_defaults

  • values(*args, **kwargs)
  • specify a fixed VALUES clause for an INSERT statement, or the SETclause for an UPDATE.

Note that the Insert and Update constructs supportper-execution time formatting of the VALUES and/or SET clauses,based on the arguments passed to Connection.execute().However, the ValuesBase.values() method can be used to “fix” aparticular set of parameters into the statement.

Multiple calls to ValuesBase.values() will produce a newconstruct, each one with the parameter list modified to includethe new parameters sent. In the typical case of a singledictionary of parameters, the newly passed keys will replacethe same keys in the previous construct. In the case of a list-based“multiple values” construct, each new list of values is extendedonto the existing list of values.

  1. - Parameters
  2. - 
  3.   -

**kwargs –

key value pairs representing the string keyof a Column mapped to the value to be rendered into theVALUES or SET clause:

  1. users.insert().values(name="some name")
  2.  
  3. users.update().where(users.c.id==5).values(name="some name")
  1.   -

*args –

As an alternative to passing key/value parameters,a dictionary, tuple, or list of dictionaries or tuples can be passedas a single positional argument in order to form the VALUES orSET clause of the statement. The forms that are accepted varybased on whether this is an Insert or an Updateconstruct.

For either an Insert or Update construct, asingle dictionary can be passed, which works the same as that ofthe kwargs form:

  1. users.insert().values({"name": "some name"})
  2.  
  3. users.update().values({"name": "some new name"})

Also for either form but more typically for the Insertconstruct, a tuple that contains an entry for every column in thetable is also accepted:

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

The Insert construct also supports being passed a listof dictionaries or full-table-tuples, which on the server willrender the less common SQL syntax of “multiple values” - thissyntax is supported on backends such as SQLite, PostgreSQL, MySQL,but not necessarily others:

  1. users.insert().values([
  2.                     {"name": "some name"},
  3.                     {"name": "some other name"},
  4.                     {"name": "yet another name"},
  5.                 ])

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

  1. INSERT INTO users (name) VALUES
  2.                 (:name_1),
  3.                 (:name_2),
  4.                 (:name_3)

It is essential to note that passing multiple values isNOT the same as using traditional executemany() form. The abovesyntax is a special syntax not typically used. To emit anINSERT statement against multiple rows, the normal method isto pass a multiple values list to the Connection.execute()method, which is supported by all database backends and is generallymore efficient for a very large number of parameters.

See also

Executing Multiple Statements - an introduction tothe traditional Core method of multiple parameter setinvocation for INSERTs and other statements.

Changed in version 1.0.0: an INSERT that uses a multiple-VALUESclause, even a list of length one,implies that the Insert.inline flag is set toTrue, indicating that the statement will not attempt to fetchthe “last inserted primary key” or other defaults. Thestatement deals with an arbitrary number of rows, so theResultProxy.inserted_primary_key accessor does notapply.

Changed in version 1.0.0: A multiple-VALUES INSERT now supportscolumns with Python side default values and callables in thesame way as that of an “executemany” style of invocation; thecallable is invoked for each row. See Python-side defaults invoked for each row individually when using a multivalued insertfor other details.

The Update construct supports a special form which is alist of 2-tuples, which when provided must be passed in conjunctionwith thepreserve_parameter_orderparameter.This form causes the UPDATE statement to render the SET clausesusing the order of parameters given to Update.values(), ratherthan the ordering of columns given in the Table.

New in version 1.0.10: - added support for parameter-orderedUPDATE statements via thepreserve_parameter_orderflag.

See also

Parameter-Ordered Updates - full example of thepreserve_parameter_orderflag

See also

Inserts, Updates and Deletes - SQL ExpressionLanguage Tutorial

insert() - produce an INSERT statement

update() - produce an UPDATE statement