diff options
Diffstat (limited to 'lib/sqlalchemy/sql')
| -rw-r--r-- | lib/sqlalchemy/sql/_dml_constructors.py | 16 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/_elements_constructors.py | 24 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/_selectable_constructors.py | 5 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/compiler.py | 15 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/crud.py | 66 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/dml.py | 46 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/elements.py | 6 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/functions.py | 4 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/operators.py | 4 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/schema.py | 11 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/selectable.py | 34 |
11 files changed, 135 insertions, 96 deletions
diff --git a/lib/sqlalchemy/sql/_dml_constructors.py b/lib/sqlalchemy/sql/_dml_constructors.py index 926e5257b..293d225f9 100644 --- a/lib/sqlalchemy/sql/_dml_constructors.py +++ b/lib/sqlalchemy/sql/_dml_constructors.py @@ -35,9 +35,6 @@ def insert(table: _DMLTableArgument) -> Insert: .. seealso:: - :ref:`coretutorial_insert_expressions` - in the - :ref:`1.x tutorial <sqlexpression_toplevel>` - :ref:`tutorial_core_insert` - in the :ref:`unified_tutorial` @@ -79,9 +76,7 @@ def insert(table: _DMLTableArgument) -> Insert: .. seealso:: - :ref:`coretutorial_insert_expressions` - SQL Expression Tutorial - - :ref:`inserts_and_updates` - SQL Expression Tutorial + :ref:`tutorial_core_insert` - in the :ref:`unified_tutorial` """ return Insert(table) @@ -104,15 +99,6 @@ def update(table: _DMLTableArgument) -> Update: :meth:`_expression.TableClause.update` method on :class:`_schema.Table`. - .. seealso:: - - :ref:`inserts_and_updates` - in the - :ref:`1.x tutorial <sqlexpression_toplevel>` - - :ref:`tutorial_core_update_delete` - in the :ref:`unified_tutorial` - - - :param table: A :class:`_schema.Table` object representing the database table to be updated. diff --git a/lib/sqlalchemy/sql/_elements_constructors.py b/lib/sqlalchemy/sql/_elements_constructors.py index f6dd92865..8b8f6b010 100644 --- a/lib/sqlalchemy/sql/_elements_constructors.py +++ b/lib/sqlalchemy/sql/_elements_constructors.py @@ -605,15 +605,6 @@ def bindparam( .. versionchanged:: 1.3 the "expanding" bound parameter feature now supports empty lists. - - .. seealso:: - - :ref:`coretutorial_bind_param` - - :ref:`coretutorial_insert_expressions` - - :func:`.outparam` - :param literal_execute: if True, the bound parameter will be rendered in the compile phase with a special "POSTCOMPILE" token, and the SQLAlchemy compiler will @@ -635,6 +626,12 @@ def bindparam( :ref:`change_4808`. + .. seealso:: + + :ref:`tutorial_sending_parameters` - in the + :ref:`unified_tutorial` + + """ return BindParameter( key, @@ -827,7 +824,7 @@ def cast( .. seealso:: - :ref:`coretutorial_casts` + :ref:`tutorial_casts` :func:`.type_coerce` - an alternative to CAST that coerces the type on the Python side only, which is often sufficient to generate the @@ -932,7 +929,7 @@ def column( :func:`_expression.text` - :ref:`sqlexpression_literal_column` + :ref:`tutorial_select_arbitrary_text` """ return ColumnClause(text, type_, is_literal, _selectable) @@ -1468,8 +1465,7 @@ def text(text: str) -> TextClause: .. seealso:: - :ref:`sqlexpression_text` - in the Core tutorial - + :ref:`tutorial_select_arbitrary_text` """ return TextClause(text) @@ -1615,7 +1611,7 @@ def type_coerce( .. seealso:: - :ref:`coretutorial_casts` + :ref:`tutorial_casts` :func:`.cast` diff --git a/lib/sqlalchemy/sql/_selectable_constructors.py b/lib/sqlalchemy/sql/_selectable_constructors.py index ea824d622..b661d6f47 100644 --- a/lib/sqlalchemy/sql/_selectable_constructors.py +++ b/lib/sqlalchemy/sql/_selectable_constructors.py @@ -290,7 +290,7 @@ def lateral( .. seealso:: - :ref:`lateral_selects` - overview of usage. + :ref:`tutorial_lateral_correlation` - overview of usage. """ return Lateral._factory(selectable, name=name) @@ -466,8 +466,7 @@ def select(*entities: _ColumnsClauseArgument[Any], **__kw: Any) -> Select[Any]: .. seealso:: - :ref:`coretutorial_selecting` - Core Tutorial description of - :func:`_expression.select`. + :ref:`tutorial_selecting_data` - in the :ref:`unified_tutorial` :param \*entities: Entities to SELECT from. For Core usage, this is typically a series diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py index 3685751b0..78c6af38b 100644 --- a/lib/sqlalchemy/sql/compiler.py +++ b/lib/sqlalchemy/sql/compiler.py @@ -3482,7 +3482,7 @@ class SQLCompiler(Compiled): ) def _label_returning_column( - self, stmt, column, populate_result_map, column_clause_args=None + self, stmt, column, populate_result_map, column_clause_args=None, **kw ): """Render a column with necessary labels inside of a RETURNING clause. @@ -3499,6 +3499,7 @@ class SQLCompiler(Compiled): populate_result_map, False, {} if column_clause_args is None else column_clause_args, + **kw, ) def _label_select_column( @@ -3514,6 +3515,7 @@ class SQLCompiler(Compiled): within_columns_clause=True, column_is_repeated=False, need_column_expressions=False, + include_table=True, ): """produce labeled columns present in a select().""" impl = column.type.dialect_impl(self.dialect) @@ -3661,6 +3663,7 @@ class SQLCompiler(Compiled): column_clause_args.update( within_columns_clause=within_columns_clause, add_to_result_map=add_to_result_map, + include_table=include_table, ) return result_expr._compiler_dispatch(self, **column_clause_args) @@ -4218,10 +4221,12 @@ class SQLCompiler(Compiled): populate_result_map: bool, **kw: Any, ) -> str: - raise exc.CompileError( - "RETURNING is not supported by this " - "dialect's statement compiler." - ) + columns = [ + self._label_returning_column(stmt, c, populate_result_map, **kw) + for c in base._select_iterables(returning_cols) + ] + + return "RETURNING " + ", ".join(columns) def limit_clause(self, select, **kw): text = "" diff --git a/lib/sqlalchemy/sql/crud.py b/lib/sqlalchemy/sql/crud.py index 913e4d433..81151a26b 100644 --- a/lib/sqlalchemy/sql/crud.py +++ b/lib/sqlalchemy/sql/crud.py @@ -568,6 +568,7 @@ def _scan_cols( _col_bind_name, implicit_returning, implicit_return_defaults, + postfetch_lastrowid, values, autoincrement_col, insert_null_pk_still_autoincrements, @@ -649,6 +650,7 @@ def _append_param_parameter( _col_bind_name, implicit_returning, implicit_return_defaults, + postfetch_lastrowid, values, autoincrement_col, insert_null_pk_still_autoincrements, @@ -668,11 +670,12 @@ def _append_param_parameter( and c is autoincrement_col ): # support use case for #7998, fetch autoincrement cols - # even if value was given - if implicit_returning: - compiler.implicit_returning.append(c) - elif compiler.dialect.postfetch_lastrowid: + # even if value was given. + + if postfetch_lastrowid: compiler.postfetch_lastrowid = True + elif implicit_returning: + compiler.implicit_returning.append(c) value = _create_bind_param( compiler, @@ -1281,7 +1284,12 @@ def _get_stmt_parameter_tuples_params( def _get_returning_modifiers(compiler, stmt, compile_state, toplevel): + """determines RETURNING strategy, if any, for the statement. + + This is where it's determined what we need to fetch from the + INSERT or UPDATE statement after it's invoked. + """ need_pks = ( toplevel and compile_state.isinsert @@ -1296,19 +1304,58 @@ def _get_returning_modifiers(compiler, stmt, compile_state, toplevel): and not stmt._returning and not compile_state._has_multi_parameters ) + + # check if we have access to simple cursor.lastrowid. we can use that + # after the INSERT if that's all we need. + postfetch_lastrowid = ( + need_pks + and compiler.dialect.postfetch_lastrowid + and stmt.table._autoincrement_column is not None + ) + + # see if we want to add RETURNING to an INSERT in order to get + # primary key columns back. This would be instead of postfetch_lastrowid + # if that's set. implicit_returning = ( + # statement itself can veto it need_pks - and compiler.dialect.implicit_returning - and stmt.table.implicit_returning + # the dialect can veto it if it just doesnt support RETURNING + # with INSERT + and compiler.dialect.insert_returning + # user-defined implicit_returning on Table can veto it + and compile_state._primary_table.implicit_returning + # the compile_state can veto it (SQlite uses this to disable + # RETURNING for an ON CONFLICT insert, as SQLite does not return + # for rows that were updated, which is wrong) + and compile_state._supports_implicit_returning + and ( + # since we support MariaDB and SQLite which also support lastrowid, + # decide if we should use lastrowid or RETURNING. for insert + # that didnt call return_defaults() and has just one set of + # parameters, we can use lastrowid. this is more "traditional" + # and a lot of weird use cases are supported by it. + # SQLite lastrowid times 3x faster than returning, + # Mariadb lastrowid 2x faster than returning + ( + not postfetch_lastrowid + or compiler.dialect.favor_returning_over_lastrowid + ) + or compile_state._has_multi_parameters + or stmt._return_defaults + ) ) + if implicit_returning: + postfetch_lastrowid = False + if compile_state.isinsert: implicit_return_defaults = implicit_returning and stmt._return_defaults elif compile_state.isupdate: implicit_return_defaults = ( - compiler.dialect.implicit_returning - and stmt.table.implicit_returning - and stmt._return_defaults + stmt._return_defaults + and compile_state._primary_table.implicit_returning + and compile_state._supports_implicit_returning + and compiler.dialect.update_returning ) else: # this line is unused, currently we are always @@ -1321,7 +1368,6 @@ def _get_returning_modifiers(compiler, stmt, compile_state, toplevel): else: implicit_return_defaults = set(stmt._return_defaults_columns) - postfetch_lastrowid = need_pks and compiler.dialect.postfetch_lastrowid return ( need_pks, implicit_returning, diff --git a/lib/sqlalchemy/sql/dml.py b/lib/sqlalchemy/sql/dml.py index e63a34454..2ed3be9cb 100644 --- a/lib/sqlalchemy/sql/dml.py +++ b/lib/sqlalchemy/sql/dml.py @@ -119,6 +119,8 @@ class DMLState(CompileState): _ordered_values: Optional[List[Tuple[_DMLColumnElement, Any]]] = None _parameter_ordering: Optional[List[_DMLColumnElement]] = None _has_multi_parameters = False + _primary_table: FromClause + _supports_implicit_returning = True isupdate = False isdelete = False @@ -182,11 +184,14 @@ class DMLState(CompileState): for k, v in kv_iterator ] - def _make_extra_froms(self, statement: DMLWhereBase) -> List[FromClause]: + def _make_extra_froms( + self, statement: DMLWhereBase + ) -> Tuple[FromClause, List[FromClause]]: froms: List[FromClause] = [] all_tables = list(sql_util.tables_from_leftmost(statement.table)) - seen = {all_tables[0]} + primary_table = all_tables[0] + seen = {primary_table} for crit in statement._where_criteria: for item in _from_objects(crit): @@ -195,7 +200,7 @@ class DMLState(CompileState): seen.update(item._cloned_set) froms.extend(all_tables[1:]) - return froms + return primary_table, froms def _process_multi_values(self, statement: ValuesBase) -> None: if not statement._supports_multi_parameters: @@ -286,8 +291,18 @@ class InsertDMLState(DMLState): include_table_with_column_exprs = False - def __init__(self, statement: Insert, compiler: SQLCompiler, **kw: Any): + def __init__( + self, + statement: Insert, + compiler: SQLCompiler, + disable_implicit_returning: bool = False, + **kw: Any, + ): self.statement = statement + self._primary_table = statement.table + + if disable_implicit_returning: + self._supports_implicit_returning = False self.isinsert = True if statement._select_names: @@ -306,6 +321,7 @@ class UpdateDMLState(DMLState): def __init__(self, statement: Update, compiler: SQLCompiler, **kw: Any): self.statement = statement + self.isupdate = True if statement._ordered_values is not None: self._process_ordered_values(statement) @@ -313,7 +329,9 @@ class UpdateDMLState(DMLState): self._process_values(statement) elif statement._multi_values: self._process_multi_values(statement) - self._extra_froms = ef = self._make_extra_froms(statement) + t, ef = self._make_extra_froms(statement) + self._primary_table = t + self._extra_froms = ef self.is_multitable = mt = ef @@ -330,7 +348,9 @@ class DeleteDMLState(DMLState): self.statement = statement self.isdelete = True - self._extra_froms = self._make_extra_froms(statement) + t, ef = self._make_extra_froms(statement) + self._primary_table = t + self._extra_froms = ef SelfUpdateBase = typing.TypeVar("SelfUpdateBase", bound="UpdateBase") @@ -780,7 +800,7 @@ class ValuesBase(UpdateBase): .. seealso:: - :ref:`execute_multiple` - an introduction to + :ref:`tutorial_multiple_parameters` - an introduction to the traditional Core method of multiple parameter set invocation for INSERTs and other statements. @@ -1236,16 +1256,6 @@ class DMLWhereBase: .. seealso:: - **1.x Tutorial Examples** - - :ref:`tutorial_1x_correlated_updates` - - :ref:`multi_table_updates` - - :ref:`multi_table_deletes` - - **2.0 Tutorial Examples** - :ref:`tutorial_correlated_updates` :ref:`tutorial_update_from` @@ -1361,7 +1371,7 @@ class Update(DMLWhereBase, ValuesBase): .. seealso:: - :ref:`updates_order_parameters` - full example of the + :ref:`tutorial_parameter_ordered_updates` - full example of the :meth:`_expression.Update.ordered_values` method. .. versionchanged:: 1.4 The :meth:`_expression.Update.ordered_values` diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py index 6032253c2..625e1d94b 100644 --- a/lib/sqlalchemy/sql/elements.py +++ b/lib/sqlalchemy/sql/elements.py @@ -190,7 +190,7 @@ def literal_column( :func:`_expression.text` - :ref:`sqlexpression_literal_column` + :ref:`tutorial_select_arbitrary_text` """ return ColumnClause(text, type_=type_, is_literal=True) @@ -1568,7 +1568,7 @@ class ColumnElement( .. seealso:: - :ref:`coretutorial_casts` + :ref:`tutorial_casts` :func:`_expression.cast` @@ -3198,7 +3198,7 @@ class Cast(WrapsColumnExpression[_T]): .. seealso:: - :ref:`coretutorial_casts` + :ref:`tutorial_casts` :func:`.cast` diff --git a/lib/sqlalchemy/sql/functions.py b/lib/sqlalchemy/sql/functions.py index 0cba1a1a8..befd262ec 100644 --- a/lib/sqlalchemy/sql/functions.py +++ b/lib/sqlalchemy/sql/functions.py @@ -102,7 +102,7 @@ class FunctionElement(Executable, ColumnElement[_T], FromClause, Generative): .. seealso:: - :ref:`coretutorial_functions` - in the Core tutorial + :ref:`tutorial_functions` - in the :ref:`unified_tutorial` :class:`.Function` - named SQL function. @@ -821,7 +821,7 @@ class _FunctionGenerator: .. seealso:: - :ref:`coretutorial_functions` - in the Core Tutorial + :ref:`tutorial_functions` - in the :ref:`unified_tutorial` :class:`.Function` diff --git a/lib/sqlalchemy/sql/operators.py b/lib/sqlalchemy/sql/operators.py index 593454432..2b888769a 100644 --- a/lib/sqlalchemy/sql/operators.py +++ b/lib/sqlalchemy/sql/operators.py @@ -320,8 +320,8 @@ class Operators: side:: class MyComparator(ColumnOperators): - def operate(self, op, other): - return op(func.lower(self), func.lower(other)) + def operate(self, op, other, **kwargs): + return op(func.lower(self), func.lower(other), **kwargs) :param op: Operator callable. :param \*other: the 'other' side of the operation. Will diff --git a/lib/sqlalchemy/sql/schema.py b/lib/sqlalchemy/sql/schema.py index 598bacc59..447e102ed 100644 --- a/lib/sqlalchemy/sql/schema.py +++ b/lib/sqlalchemy/sql/schema.py @@ -639,10 +639,13 @@ class Table( :param implicit_returning: True by default - indicates that - RETURNING can be used by default to fetch newly inserted primary key - values, for backends which support this. Note that - :func:`_sa.create_engine` also provides an ``implicit_returning`` - flag. + RETURNING can be used, typically by the ORM, in order to fetch + server-generated values such as primary key values and + server side defaults, on those backends which support RETURNING. + + In modern SQLAlchemy there is generally no reason to alter this + setting, except in the case of some backends such as SQL Server + when INSERT triggers are used for that table. :param include_columns: A list of strings indicating a subset of columns to be loaded via the ``autoload`` operation; table columns who diff --git a/lib/sqlalchemy/sql/selectable.py b/lib/sqlalchemy/sql/selectable.py index 53dcf51c7..ce561697b 100644 --- a/lib/sqlalchemy/sql/selectable.py +++ b/lib/sqlalchemy/sql/selectable.py @@ -105,6 +105,7 @@ and_ = BooleanClauseList.and_ _T = TypeVar("_T", bound=Any) if TYPE_CHECKING: + import sqlalchemy from ._typing import _ColumnExpressionArgument from ._typing import _FromClauseArgument from ._typing import _JoinTargetArgument @@ -129,7 +130,6 @@ if TYPE_CHECKING: from .cache_key import _CacheKeyTraversalType from .compiler import SQLCompiler from .dml import Delete - from .dml import Insert from .dml import Update from .elements import KeyedColumnElement from .elements import Label @@ -292,7 +292,7 @@ class Selectable(ReturnsRows): .. seealso:: - :ref:`lateral_selects` - overview of usage. + :ref:`tutorial_lateral_correlation` - overview of usage. """ return Lateral._construct(self, name) @@ -751,7 +751,7 @@ class FromClause(roles.AnonymizedFromClauseRole, Selectable): .. seealso:: - :ref:`core_tutorial_aliases` + :ref:`tutorial_using_aliases` :func:`_expression.alias` @@ -1635,6 +1635,10 @@ class AliasedReturnsRows(NoInit, NamedFromClause): return name + @util.ro_non_memoized_property + def implicit_returning(self): + return self.element.implicit_returning # type: ignore + @property def original(self): """Legacy for dialects that are referring to Alias.original.""" @@ -1889,7 +1893,7 @@ class Lateral(FromClauseAlias, LateralFromClause): .. seealso:: - :ref:`lateral_selects` - overview of usage. + :ref:`tutorial_lateral_correlation` - overview of usage. """ @@ -2059,7 +2063,7 @@ class CTE( .. seealso:: - :ref:`core_tutorial_aliases` + :ref:`tutorial_using_aliases` :func:`_expression.alias` @@ -2992,7 +2996,7 @@ class TableClause(roles.DMLTableRole, Immutable, NamedFromClause): c.table = self @util.preload_module("sqlalchemy.sql.dml") - def insert(self) -> Insert: + def insert(self) -> sqlalchemy.sql.expression.Insert: """Generate an :func:`_expression.insert` construct against this :class:`_expression.TableClause`. @@ -3176,7 +3180,7 @@ class Values(Generative, LateralFromClause): .. seealso:: - :ref:`core_tutorial_aliases` + :ref:`tutorial_using_aliases` :func:`_expression.alias` @@ -3458,8 +3462,6 @@ class SelectBase( :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial - :ref:`scalar_selects` - in the 1.x tutorial - """ if self._label_style is not LABEL_STYLE_NONE: self = self.set_label_style(LABEL_STYLE_NONE) @@ -3487,7 +3489,7 @@ class SelectBase( .. seealso:: - :ref:`lateral_selects` - overview of usage. + :ref:`tutorial_lateral_correlation` - overview of usage. """ return Lateral._factory(self, name) @@ -4966,8 +4968,6 @@ class Select( :func:`_sql.select` - :ref:`coretutorial_selecting` - in the 1.x tutorial - :ref:`tutorial_selecting_data` - in the 2.0 tutorial """ @@ -6018,7 +6018,7 @@ class Select( :meth:`_expression.Select.correlate_except` - :ref:`correlated_subqueries` + :ref:`tutorial_scalar_subquery` """ @@ -6068,7 +6068,7 @@ class Select( :meth:`_expression.Select.correlate` - :ref:`correlated_subqueries` + :ref:`tutorial_scalar_subquery` """ @@ -6341,8 +6341,6 @@ class ScalarSelect( :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial - :ref:`scalar_selects` - in the 1.x tutorial - """ _traverse_internals: _TraverseInternalsType = [ @@ -6440,8 +6438,6 @@ class ScalarSelect( :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial - :ref:`correlated_subqueries` - in the 1.x tutorial - """ self.element = cast("Select[Any]", self.element).correlate( @@ -6479,8 +6475,6 @@ class ScalarSelect( :ref:`tutorial_scalar_subquery` - in the 2.0 tutorial - :ref:`correlated_subqueries` - in the 1.x tutorial - """ |