class Insert(ValuesBase): (source)
Known subclasses: sqlalchemy.dialects.mysql.dml.Insert
, sqlalchemy.dialects.postgresql.dml.Insert
, sqlalchemy.dialects.sqlite.dml.Insert
, sqlalchemy.sql.dml.ReturningInsert
Represent an INSERT construct. The :class:`_expression.Insert` object is created using the :func:`_expression.insert()` function.
Method | __init__ |
Undocumented |
Method | from |
Return a new :class:`_expression.Insert` construct which represents an ``INSERT...FROM SELECT`` statement. |
Method | inline |
Make this :class:`_expression.Insert` construct "inline" . |
Method | returning |
Add a :term:`RETURNING` or equivalent clause to this statement. |
Class Variable | __visit |
Undocumented |
Class Variable | is |
Undocumented |
Class Variable | table |
Undocumented |
Instance Variable | include |
Undocumented |
Instance Variable | select |
SELECT statement for INSERT .. FROM SELECT |
Class Variable | _supports |
Undocumented |
Class Variable | _traverse |
Undocumented |
Instance Variable | _inline |
Undocumented |
Instance Variable | _select |
Undocumented |
Inherited from ValuesBase
:
Method | values |
Specify a fixed VALUES clause for an INSERT statement, or the SET clause for an UPDATE. |
Class Variable | _multi |
Undocumented |
Class Variable | _ordered |
Undocumented |
Class Variable | _post |
used by extensions to Insert etc. to add additional syntacitcal constructs, e.g. ON CONFLICT etc. |
Instance Variable | _values |
Undocumented |
Inherited from UpdateBase
(via ValuesBase
):
Method | corresponding |
Undocumented |
Method | params |
Set the parameters for the statement. |
Method | return |
Make use of a :term:`RETURNING` clause for the purpose of fetching server-side expressions and defaults, for supporting backends only. |
Method | with |
Add dialect options to this INSERT/UPDATE/DELETE object. |
Method | with |
Add a table hint for a single table to this INSERT/UPDATE/DELETE statement. |
Class Variable | is |
Undocumented |
Class Variable | named |
Undocumented |
Property | entity |
Return a :term:`plugin-enabled` description of the table and/or entity which this DML construct is operating against. |
Property | exported |
Return the RETURNING columns as a column collection for this statement. |
Property | returning |
Return a :term:`plugin-enabled` description of the columns which this DML construct is RETURNING against, in other words the expressions established as part of :meth:`.UpdateBase.returning`. |
Method | _generate |
Populate columns into an :class:`.AliasedReturnsRows` object. |
Class Variable | _returning |
Undocumented |
Instance Variable | _hints |
Undocumented |
Instance Variable | _return |
Undocumented |
Instance Variable | _return |
Undocumented |
Instance Variable | _supplemental |
Undocumented |
Property | _all |
A sequence of column expression objects that represents the "selected" columns of this :class:`_expression.ReturnsRows`. |
Inherited from DMLRole
(via ValuesBase
, UpdateBase
):
Class Variable | __slots__ |
Undocumented |
Inherited from HasCTE
(via ValuesBase
, UpdateBase
, DMLRole
):
Method | add |
Add one or more :class:`_sql.CTE` constructs to this statement. |
Method | cte |
Return a new :class:`_expression.CTE`, or Common Table Expression instance. |
Class Variable | _has |
Undocumented |
Class Variable | _independent |
Undocumented |
Class Variable | _independent |
Undocumented |
Inherited from SelectsRows
(via ValuesBase
, UpdateBase
, DMLRole
, HasCTE
, HasCTERole
):
Method | _generate |
Generate column names as rendered in a SELECT statement by the compiler. |
Inherited from HasCompileState
(via ValuesBase
, UpdateBase
, DMLRole
, HasCTE
, HasCTERole
, SelectsRows
):
Class Variable | _compile |
Undocumented |
Inherited from DialectKWArgs
(via ValuesBase
, UpdateBase
, DMLRole
, HasCTE
, HasCTERole
, SelectsRows
, HasCompileState
):
Class Method | argument |
Add a new kind of dialect-specific keyword argument for this class. |
Property | dialect |
A collection of keyword arguments specified as dialect-specific options to this construct. |
Property | dialect |
A collection of keyword arguments specified as dialect-specific options to this construct. |
Property | kwargs |
A synonym for :attr:`.DialectKWArgs.dialect_kwargs`. |
Method | _kw |
Undocumented |
Method | _validate |
Undocumented |
Class Variable | _dialect |
Undocumented |
Class Variable | _kw |
Undocumented |
Inherited from HasPrefixes
(via ValuesBase
, UpdateBase
, DMLRole
, HasCTE
, HasCTERole
, SelectsRows
, HasCompileState
, DialectKWArgs
):
Method | prefix |
Add one or more expressions following the statement keyword, i.e. SELECT, INSERT, UPDATE, or DELETE. Generative. |
Class Variable | _has |
Undocumented |
Instance Variable | _prefixes |
Undocumented |
Inherited from Generative
(via ValuesBase
, UpdateBase
, DMLRole
, HasCTE
, HasCTERole
, SelectsRows
, HasCompileState
, DialectKWArgs
, HasPrefixes
):
Method | _generate |
Undocumented |
Inherited from Executable
(via ValuesBase
, UpdateBase
, DMLRole
, HasCTE
, HasCTERole
, SelectsRows
, HasCompileState
, DialectKWArgs
, HasPrefixes
, Generative
, ExecutableReturnsRows
):
Method | execution |
Set non-SQL options for the statement which take effect during execution. |
Method | get |
Get the non-SQL options which will take effect during execution. |
Method | options |
Apply options to this statement. |
Class Variable | is |
Undocumented |
Class Variable | is |
Undocumented |
Class Variable | is |
Undocumented |
Class Variable | is |
Undocumented |
Class Variable | supports |
Undocumented |
Method | _add |
Add a context option to this statement. |
Method | _compile |
Undocumented |
Method | _execute |
Undocumented |
Method | _execute |
Undocumented |
Method | _set |
Assign the compile options to a new value. |
Method | _update |
update the _compile_options with new keys. |
Class Variable | _executable |
Undocumented |
Class Variable | _is |
Undocumented |
Class Variable | _with |
Undocumented |
Class Variable | _with |
Undocumented |
Instance Variable | _compile |
Undocumented |
Instance Variable | _execution |
Undocumented |
Property | _effective |
Undocumented |
Inherited from StatementRole
(via ValuesBase
, UpdateBase
, DMLRole
, HasCTE
, HasCTERole
, SelectsRows
, HasCompileState
, DialectKWArgs
, HasPrefixes
, Generative
, ExecutableReturnsRows
, Executable
):
Class Variable | _role |
Undocumented |
Property | _propagate |
Undocumented |
Inherited from ReturnsRows
(via ValuesBase
, UpdateBase
, DMLRole
, HasCTE
, HasCTERole
, SelectsRows
, HasCompileState
, DialectKWArgs
, HasPrefixes
, Generative
, ExecutableReturnsRows
, Executable
, StatementRole
):
Method | is |
Return ``True`` if this :class:`.ReturnsRows` is 'derived' from the given :class:`.FromClause`. |
Property | selectable |
Undocumented |
Method | _refresh |
reset internal collections for an incoming column being added. |
Class Variable | _is |
Undocumented |
Class Variable | _is |
Undocumented |
Class Variable | _is |
Undocumented |
Class Variable | _is |
Undocumented |
Class Variable | _is |
Undocumented |
Inherited from SQLRole
(via ValuesBase
, UpdateBase
, DMLRole
, HasCTE
, HasCTERole
, SelectsRows
, HasCompileState
, DialectKWArgs
, HasPrefixes
, Generative
, ExecutableReturnsRows
, Executable
, StatementRole
, ReturnsRows
, ReturnsRowsRole
):
Class Variable | allows |
Undocumented |
Class Variable | uses |
Undocumented |
Inherited from DQLDMLClauseElement
(via ValuesBase
, UpdateBase
, DMLRole
, HasCTE
, HasCTERole
, SelectsRows
, HasCompileState
, DialectKWArgs
, HasPrefixes
, Generative
, ExecutableReturnsRows
, Executable
, StatementRole
, ReturnsRows
, ReturnsRowsRole
, SQLRole
):
Method | compile |
Compile this SQL expression. |
Method | _compiler |
Return a compiler appropriate for this ClauseElement, given a Dialect. |
Inherited from ClauseElement
(via ValuesBase
, UpdateBase
, DMLRole
, HasCTE
, HasCTERole
, SelectsRows
, HasCompileState
, DialectKWArgs
, HasPrefixes
, Generative
, ExecutableReturnsRows
, Executable
, StatementRole
, ReturnsRows
, ReturnsRowsRole
, SQLRole
, DQLDMLClauseElement
):
Method | __bool__ |
Undocumented |
Method | __getstate__ |
Undocumented |
Method | __invert__ |
Undocumented |
Method | __repr__ |
Undocumented |
Method | compare |
Compare this :class:`_expression.ClauseElement` to the given :class:`_expression.ClauseElement`. |
Method | get |
Return immediate child :class:`.visitors.HasTraverseInternals` elements of this :class:`.visitors.HasTraverseInternals`. |
Method | self |
Apply a 'grouping' to this :class:`_expression.ClauseElement`. |
Method | unique |
Return a copy with :func:`_expression.bindparam` elements replaced. |
Class Variable | is |
Undocumented |
Class Variable | is |
Undocumented |
Class Variable | negation |
Undocumented |
Property | description |
Undocumented |
Property | entity |
Undocumented |
Method | _clone |
Create a shallow copy of this ClauseElement. |
Method | _cloned |
Return the set consisting all cloned ancestors of this ClauseElement. |
Method | _negate |
Undocumented |
Method | _negate |
a hook to allow the right side of a binary expression to respond to a negation of the binary expression. |
Method | _replace |
Undocumented |
Method | _set |
Undocumented |
Method | _ungroup |
Return this :class:`_expression.ClauseElement` without any groupings. |
Method | _with |
in the context of binary expression, convert the type of this object to the one given. |
Class Variable | _cache |
Undocumented |
Class Variable | _gen |
Undocumented |
Class Variable | _is |
Undocumented |
Class Variable | _is |
Undocumented |
Class Variable | _is |
Undocumented |
Class Variable | _is |
Undocumented |
Class Variable | _is |
Undocumented |
Class Variable | _is |
Undocumented |
Class Variable | _is |
Undocumented |
Class Variable | _is |
Undocumented |
Class Variable | _is |
Undocumented |
Class Variable | _is |
Undocumented |
Class Variable | _is |
Undocumented |
Class Variable | _is |
Undocumented |
Class Variable | _is |
Undocumented |
Class Variable | _is |
Undocumented |
Property | _constructor |
return the 'constructor' for this ClauseElement. |
Property | _from |
Undocumented |
Property | _order |
Undocumented |
Inherited from SupportsWrappingAnnotations
(via ValuesBase
, UpdateBase
, DMLRole
, HasCTE
, HasCTERole
, SelectsRows
, HasCompileState
, DialectKWArgs
, HasPrefixes
, Generative
, ExecutableReturnsRows
, Executable
, StatementRole
, ReturnsRows
, ReturnsRowsRole
, SQLRole
, DQLDMLClauseElement
, ClauseElement
):
Method | _annotate |
return a copy of this ClauseElement with annotations updated by the given dictionary. |
Method | _deannotate |
return a copy of this :class:`_expression.ClauseElement` with annotations removed. |
Method | _with |
return a copy of this ClauseElement with annotations replaced by the given dictionary. |
Inherited from SupportsAnnotations
(via ValuesBase
, UpdateBase
, DMLRole
, HasCTE
, HasCTERole
, SelectsRows
, HasCompileState
, DialectKWArgs
, HasPrefixes
, Generative
, ExecutableReturnsRows
, Executable
, StatementRole
, ReturnsRows
, ReturnsRowsRole
, SQLRole
, DQLDMLClauseElement
, ClauseElement
, SupportsWrappingAnnotations
):
Class Variable | proxy |
Undocumented |
Method | _gen |
Undocumented |
Property | _annotations |
Undocumented |
Inherited from MemoizedHasCacheKey
(via ValuesBase
, UpdateBase
, DMLRole
, HasCTE
, HasCTERole
, SelectsRows
, HasCompileState
, DialectKWArgs
, HasPrefixes
, Generative
, ExecutableReturnsRows
, Executable
, StatementRole
, ReturnsRows
, ReturnsRowsRole
, SQLRole
, DQLDMLClauseElement
, ClauseElement
, SupportsWrappingAnnotations
, SupportsAnnotations
):
Method | _generate |
return a cache key. |
Inherited from HasCacheKey
(via ValuesBase
, UpdateBase
, DMLRole
, HasCTE
, HasCTERole
, SelectsRows
, HasCompileState
, DialectKWArgs
, HasPrefixes
, Generative
, ExecutableReturnsRows
, Executable
, StatementRole
, ReturnsRows
, ReturnsRowsRole
, SQLRole
, DQLDMLClauseElement
, ClauseElement
, SupportsWrappingAnnotations
, SupportsAnnotations
, MemoizedHasCacheKey
):
Class Variable | inherit |
Indicate if this :class:`.HasCacheKey` instance should make use of the cache key generation scheme used by its immediate superclass. |
Class Method | _generate |
generate cache key dispatcher for a new class. |
Class Method | _generate |
Undocumented |
Method | _gen |
return an optional cache key. |
Class Variable | _generated |
Undocumented |
Class Variable | _hierarchy |
private attribute which may be set to False to prevent the inherit_cache warning from being emitted for a hierarchy of subclasses. |
Class Variable | _is |
Undocumented |
Inherited from HasCopyInternals
(via ValuesBase
, UpdateBase
, DMLRole
, HasCTE
, HasCTERole
, SelectsRows
, HasCompileState
, DialectKWArgs
, HasPrefixes
, Generative
, ExecutableReturnsRows
, Executable
, StatementRole
, ReturnsRows
, ReturnsRowsRole
, SQLRole
, DQLDMLClauseElement
, ClauseElement
, SupportsWrappingAnnotations
, SupportsAnnotations
, MemoizedHasCacheKey
, HasCacheKey
):
Method | _copy |
Reassign internal elements to be clones of themselves. |
Inherited from CompilerElement
(via ValuesBase
, UpdateBase
, DMLRole
, HasCTE
, HasCTERole
, SelectsRows
, HasCompileState
, DialectKWArgs
, HasPrefixes
, Generative
, ExecutableReturnsRows
, Executable
, StatementRole
, ReturnsRows
, ReturnsRowsRole
, SQLRole
, DQLDMLClauseElement
, ClauseElement
, SupportsWrappingAnnotations
, SupportsAnnotations
, MemoizedHasCacheKey
, HasCacheKey
, HasCopyInternals
, ExternallyTraversible
, HasTraverseInternals
):
Method | __str__ |
Undocumented |
Class Variable | stringify |
Undocumented |
Inherited from Visitable
(via ValuesBase
, UpdateBase
, DMLRole
, HasCTE
, HasCTERole
, SelectsRows
, HasCompileState
, DialectKWArgs
, HasPrefixes
, Generative
, ExecutableReturnsRows
, Executable
, StatementRole
, ReturnsRows
, ReturnsRowsRole
, SQLRole
, DQLDMLClauseElement
, ClauseElement
, SupportsWrappingAnnotations
, SupportsAnnotations
, MemoizedHasCacheKey
, HasCacheKey
, HasCopyInternals
, ExternallyTraversible
, HasTraverseInternals
, CompilerElement
):
Method | __class |
Undocumented |
Method | __init |
Undocumented |
Class Method | _generate |
Undocumented |
Method | _compiler |
Undocumented |
Class Variable | _original |
Undocumented |
def from_select(self, names:
List[ str]
, select: Selectable
, include_defaults: bool
= True) -> Self
:
(source)
¶
Return a new :class:`_expression.Insert` construct which represents an ``INSERT...FROM SELECT`` statement. e.g.:: sel = select(table1.c.a, table1.c.b).where(table1.c.c > 5) ins = table2.insert().from_select(['a', 'b'], sel) :param names: a sequence of string column names or :class:`_schema.Column` objects representing the target columns. :param select: a :func:`_expression.select` construct, :class:`_expression.FromClause` or other construct which resolves into a :class:`_expression.FromClause`, such as an ORM :class:`_query.Query` object, etc. The order of columns returned from this FROM clause should correspond to the order of columns sent as the ``names`` parameter; while this is not checked before passing along to the database, the database would normally raise an exception if these column lists don't correspond. :param include_defaults: if True, non-server default values and SQL expressions as specified on :class:`_schema.Column` objects (as documented in :ref:`metadata_defaults_toplevel`) not otherwise specified in the list of names will be rendered into the INSERT and SELECT statements, so that these values are also included in the data to be inserted. .. note:: A Python-side default that uses a Python callable function will only be invoked **once** for the whole statement, and **not per row**. .. versionadded:: 1.0.0 - :meth:`_expression.Insert.from_select` now renders Python-side and SQL expression column defaults into the SELECT statement for columns otherwise not included in the list of column names. .. versionchanged:: 1.0.0 an INSERT that uses FROM SELECT implies that the :paramref:`_expression.insert.inline` flag is set to True, indicating that the statement will not attempt to fetch the "last inserted primary key" or other defaults. The statement deals with an arbitrary number of rows, so the :attr:`_engine.CursorResult.inserted_primary_key` accessor does not apply.
Make this :class:`_expression.Insert` construct "inline" . When set, no attempt will be made to retrieve the SQL-generated default values to be provided within the statement; in particular, this allows SQL expressions to be rendered 'inline' within the statement without the need to pre-execute them beforehand; for backends that support "returning", this turns off the "implicit returning" feature for the statement. .. versionchanged:: 1.4 the :paramref:`_expression.Insert.inline` parameter is now superseded by the :meth:`_expression.Insert.inline` method.
@overload
@overload
_TCCA[ _T0]
, __ent1: _TCCA[ _T1]
, __ent2: _TCCA[ _T2]
) -> ReturningInsert[ Tuple[ _T0, _T1, _T2]]
:_TCCA[ _T0]
, __ent1: _TCCA[ _T1]
, __ent2: _TCCA[ _T2]
, __ent3: _TCCA[ _T3]
) -> ReturningInsert[ Tuple[ _T0, _T1, _T2, _T3]]
:_TCCA[ _T0]
, __ent1: _TCCA[ _T1]
, __ent2: _TCCA[ _T2]
, __ent3: _TCCA[ _T3]
, __ent4: _TCCA[ _T4]
) -> ReturningInsert[ Tuple[ _T0, _T1, _T2, _T3, _T4]]
:_TCCA[ _T0]
, __ent1: _TCCA[ _T1]
, __ent2: _TCCA[ _T2]
, __ent3: _TCCA[ _T3]
, __ent4: _TCCA[ _T4]
, __ent5: _TCCA[ _T5]
) -> ReturningInsert[ Tuple[ _T0, _T1, _T2, _T3, _T4, _T5]]
:_TCCA[ _T0]
, __ent1: _TCCA[ _T1]
, __ent2: _TCCA[ _T2]
, __ent3: _TCCA[ _T3]
, __ent4: _TCCA[ _T4]
, __ent5: _TCCA[ _T5]
, __ent6: _TCCA[ _T6]
) -> ReturningInsert[ Tuple[ _T0, _T1, _T2, _T3, _T4, _T5, _T6]]
:_TCCA[ _T0]
, __ent1: _TCCA[ _T1]
, __ent2: _TCCA[ _T2]
, __ent3: _TCCA[ _T3]
, __ent4: _TCCA[ _T4]
, __ent5: _TCCA[ _T5]
, __ent6: _TCCA[ _T6]
, __ent7: _TCCA[ _T7]
) -> ReturningInsert[ Tuple[ _T0, _T1, _T2, _T3, _T4, _T5, _T6, _T7]]
:(source) ¶
sqlalchemy.sql.dml.UpdateBase.returning
Add a :term:`RETURNING` or equivalent clause to this statement. e.g.: .. sourcecode:: pycon+sql >>> stmt = ( ... table.update() ... .where(table.c.data == "value") ... .values(status="X") ... .returning(table.c.server_flag, table.c.updated_timestamp) ... ) >>> print(stmt) {printsql}UPDATE some_table SET status=:status WHERE some_table.data = :data_1 RETURNING some_table.server_flag, some_table.updated_timestamp The method may be invoked multiple times to add new entries to the list of expressions to be returned. .. versionadded:: 1.4.0b2 The method may be invoked multiple times to add new entries to the list of expressions to be returned. The given collection of column expressions should be derived from the table that is the target of the INSERT, UPDATE, or DELETE. While :class:`_schema.Column` objects are typical, the elements can also be expressions: .. sourcecode:: pycon+sql >>> stmt = table.insert().returning( ... (table.c.first_name + " " + table.c.last_name).label("fullname") ... ) >>> print(stmt) {printsql}INSERT INTO some_table (first_name, last_name) VALUES (:first_name, :last_name) RETURNING some_table.first_name || :first_name_1 || some_table.last_name AS fullname Upon compilation, a RETURNING clause, or database equivalent, will be rendered within the statement. For INSERT and UPDATE, the values are the newly inserted/updated values. For DELETE, the values are those of the rows which were deleted. Upon execution, the values of the columns to be returned are made available via the result set and can be iterated using :meth:`_engine.CursorResult.fetchone` and similar. For DBAPIs which do not natively support returning values (i.e. cx_oracle), SQLAlchemy will approximate this behavior at the result level so that a reasonable amount of behavioral neutrality is provided. Note that not all databases/DBAPIs support RETURNING. For those backends with no support, an exception is raised upon compilation and/or execution. For those who do support it, the functionality across backends varies greatly, including restrictions on executemany() and other statements which return multiple rows. Please read the documentation notes for the database in use in order to determine the availability of RETURNING. .. seealso:: :meth:`.UpdateBase.return_defaults` - an alternative method tailored towards efficient fetching of server-side defaults and triggers for single-row INSERTs or UPDATEs. :ref:`tutorial_insert_returning` - in the :ref:`unified_tutorial`