diff options
Diffstat (limited to 'lib/sqlalchemy/sql')
| -rw-r--r-- | lib/sqlalchemy/sql/__init__.py | 209 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/_dml_constructors.py | 231 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/_elements_constructors.py | 1637 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/_selectable_constructors.py | 467 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/coercions.py | 124 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/default_comparator.py | 279 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/dml.py | 224 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/elements.py | 1978 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/expression.py | 394 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/functions.py | 2 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/operators.py | 351 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/roles.py | 8 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/schema.py | 112 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/selectable.py | 450 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/sqltypes.py | 60 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/type_api.py | 87 | ||||
| -rw-r--r-- | lib/sqlalchemy/sql/util.py | 32 |
17 files changed, 3889 insertions, 2756 deletions
diff --git a/lib/sqlalchemy/sql/__init__.py b/lib/sqlalchemy/sql/__init__.py index 9d66c8f61..b558f9bf5 100644 --- a/lib/sqlalchemy/sql/__init__.py +++ b/lib/sqlalchemy/sql/__init__.py @@ -5,121 +5,109 @@ # This module is part of SQLAlchemy and is released under # the MIT License: https://www.opensource.org/licenses/mit-license.php -from .base import Executable -from .compiler import COLLECT_CARTESIAN_PRODUCTS -from .compiler import FROM_LINTING -from .compiler import NO_LINTING -from .compiler import WARN_LINTING -from .expression import Alias -from .expression import alias -from .expression import all_ -from .expression import and_ -from .expression import any_ -from .expression import asc -from .expression import between -from .expression import bindparam -from .expression import case -from .expression import cast -from .expression import ClauseElement -from .expression import collate -from .expression import column -from .expression import ColumnCollection -from .expression import ColumnElement -from .expression import CompoundSelect -from .expression import cte -from .expression import Delete -from .expression import delete -from .expression import desc -from .expression import distinct -from .expression import except_ -from .expression import except_all -from .expression import exists -from .expression import extract -from .expression import false -from .expression import False_ -from .expression import FromClause -from .expression import func -from .expression import funcfilter -from .expression import Insert -from .expression import insert -from .expression import intersect -from .expression import intersect_all -from .expression import Join -from .expression import join -from .expression import label -from .expression import LABEL_STYLE_DEFAULT -from .expression import LABEL_STYLE_DISAMBIGUATE_ONLY -from .expression import LABEL_STYLE_NONE -from .expression import LABEL_STYLE_TABLENAME_PLUS_COL -from .expression import lambda_stmt -from .expression import LambdaElement -from .expression import lateral -from .expression import literal -from .expression import literal_column -from .expression import modifier -from .expression import not_ -from .expression import null -from .expression import nulls_first -from .expression import nulls_last -from .expression import nullsfirst -from .expression import nullslast -from .expression import or_ -from .expression import outerjoin -from .expression import outparam -from .expression import over -from .expression import quoted_name -from .expression import Select -from .expression import select -from .expression import Selectable -from .expression import StatementLambdaElement -from .expression import Subquery -from .expression import table -from .expression import TableClause -from .expression import TableSample -from .expression import tablesample -from .expression import text -from .expression import true -from .expression import True_ -from .expression import tuple_ -from .expression import type_coerce -from .expression import union -from .expression import union_all -from .expression import Update -from .expression import update -from .expression import Values -from .expression import values -from .expression import within_group -from .visitors import ClauseVisitor +from .base import Executable as Executable +from .compiler import COLLECT_CARTESIAN_PRODUCTS as COLLECT_CARTESIAN_PRODUCTS +from .compiler import FROM_LINTING as FROM_LINTING +from .compiler import NO_LINTING as NO_LINTING +from .compiler import WARN_LINTING as WARN_LINTING +from .expression import Alias as Alias +from .expression import alias as alias +from .expression import all_ as all_ +from .expression import and_ as and_ +from .expression import any_ as any_ +from .expression import asc as asc +from .expression import between as between +from .expression import bindparam as bindparam +from .expression import case as case +from .expression import cast as cast +from .expression import ClauseElement as ClauseElement +from .expression import collate as collate +from .expression import column as column +from .expression import ColumnCollection as ColumnCollection +from .expression import ColumnElement as ColumnElement +from .expression import CompoundSelect as CompoundSelect +from .expression import cte as cte +from .expression import Delete as Delete +from .expression import delete as delete +from .expression import desc as desc +from .expression import distinct as distinct +from .expression import except_ as except_ +from .expression import except_all as except_all +from .expression import exists as exists +from .expression import extract as extract +from .expression import false as false +from .expression import False_ as False_ +from .expression import FromClause as FromClause +from .expression import func as func +from .expression import funcfilter as funcfilter +from .expression import Insert as Insert +from .expression import insert as insert +from .expression import intersect as intersect +from .expression import intersect_all as intersect_all +from .expression import Join as Join +from .expression import join as join +from .expression import label as label +from .expression import LABEL_STYLE_DEFAULT as LABEL_STYLE_DEFAULT +from .expression import lambda_stmt as lambda_stmt +from .expression import LambdaElement as LambdaElement +from .expression import lateral as lateral +from .expression import literal as literal +from .expression import literal_column as literal_column +from .expression import modifier as modifier +from .expression import not_ as not_ +from .expression import null as null +from .expression import nulls_first as nulls_first +from .expression import nulls_last as nulls_last +from .expression import nullsfirst as nullsfirst +from .expression import nullslast as nullslast +from .expression import or_ as or_ +from .expression import outerjoin as outerjoin +from .expression import outparam as outparam +from .expression import over as over +from .expression import quoted_name as quoted_name +from .expression import Select as Select +from .expression import select as select +from .expression import Selectable as Selectable +from .expression import StatementLambdaElement as StatementLambdaElement +from .expression import Subquery as Subquery +from .expression import table as table +from .expression import TableClause as TableClause +from .expression import TableSample as TableSample +from .expression import tablesample as tablesample +from .expression import text as text +from .expression import true as true +from .expression import True_ as True_ +from .expression import tuple_ as tuple_ +from .expression import type_coerce as type_coerce +from .expression import union as union +from .expression import union_all as union_all +from .expression import Update as Update +from .expression import update as update +from .expression import Values as Values +from .expression import values as values +from .expression import within_group as within_group +from .visitors import ClauseVisitor as ClauseVisitor - -def __go(lcls): - global __all__ - from .. import util as _sa_util - - import inspect as _inspect - - __all__ = sorted( - name - for name, obj in lcls.items() - if not (name.startswith("_") or _inspect.ismodule(obj)) +if True: + # work around zimports bug + from .expression import ( + LABEL_STYLE_DISAMBIGUATE_ONLY as LABEL_STYLE_DISAMBIGUATE_ONLY, + ) + from .expression import LABEL_STYLE_NONE as LABEL_STYLE_NONE + from .expression import ( + LABEL_STYLE_TABLENAME_PLUS_COL as LABEL_STYLE_TABLENAME_PLUS_COL, ) - from .annotation import _prepare_annotations - from .annotation import Annotated - from .elements import AnnotatedColumnElement - from .elements import ClauseList - from .selectable import AnnotatedFromClause - # from .traversals import _preconfigure_traversals +def __go(lcls): + from .. import util as _sa_util from . import base from . import coercions from . import elements - from . import events from . import lambdas from . import selectable from . import schema - from . import sqltypes from . import traversals from . import type_api @@ -130,20 +118,19 @@ def __go(lcls): coercions.lambdas = lambdas coercions.schema = schema coercions.selectable = selectable - coercions.sqltypes = sqltypes coercions.traversals = traversals + from .annotation import _prepare_annotations + from .annotation import Annotated + from .elements import AnnotatedColumnElement + from .elements import ClauseList + from .selectable import AnnotatedFromClause + _prepare_annotations(ColumnElement, AnnotatedColumnElement) _prepare_annotations(FromClause, AnnotatedFromClause) _prepare_annotations(ClauseList, Annotated) - # this is expensive at import time; elements that are used can create - # their traversals on demand - # _preconfigure_traversals(ClauseElement) - _sa_util.preloaded.import_prefix("sqlalchemy.sql") - from . import naming - __go(locals()) diff --git a/lib/sqlalchemy/sql/_dml_constructors.py b/lib/sqlalchemy/sql/_dml_constructors.py new file mode 100644 index 000000000..e62edf5e6 --- /dev/null +++ b/lib/sqlalchemy/sql/_dml_constructors.py @@ -0,0 +1,231 @@ +# sql/_dml_constructors.py +# Copyright (C) 2005-2022 the SQLAlchemy authors and contributors +# <see AUTHORS file> +# +# This module is part of SQLAlchemy and is released under +# the MIT License: https://www.opensource.org/licenses/mit-license.php + +from .dml import Delete +from .dml import Insert +from .dml import Update + + +def insert(table): + """Construct an :class:`_expression.Insert` object. + + E.g.:: + + from sqlalchemy import insert + + stmt = ( + insert(user_table). + values(name='username', fullname='Full Username') + ) + + Similar functionality is available via the + :meth:`_expression.TableClause.insert` method on + :class:`_schema.Table`. + + .. seealso:: + + :ref:`coretutorial_insert_expressions` - in the + :ref:`1.x tutorial <sqlexpression_toplevel>` + + :ref:`tutorial_core_insert` - in the :ref:`unified_tutorial` + + + :param table: :class:`_expression.TableClause` + which is the subject of the + insert. + + :param values: collection of values to be inserted; see + :meth:`_expression.Insert.values` + for a description of allowed formats here. + Can be omitted entirely; a :class:`_expression.Insert` construct + will also dynamically render the VALUES clause at execution time + based on the parameters passed to :meth:`_engine.Connection.execute`. + + :param inline: if True, 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. + + If both :paramref:`_expression.Insert.values` and compile-time bind + parameters are present, the compile-time bind parameters override the + information specified within :paramref:`_expression.Insert.values` on a + per-key basis. + + The keys within :paramref:`_expression.Insert.values` can be either + :class:`~sqlalchemy.schema.Column` objects or their string + identifiers. 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 this + ``INSERT`` statement's table, the statement will be correlated + against the ``INSERT`` statement. + + .. seealso:: + + :ref:`coretutorial_insert_expressions` - SQL Expression Tutorial + + :ref:`inserts_and_updates` - SQL Expression Tutorial + + """ + return Insert(table) + + +def update(table): + r"""Construct an :class:`_expression.Update` object. + + E.g.:: + + from sqlalchemy import update + + stmt = ( + update(user_table). + where(user_table.c.id == 5). + values(name='user #5') + ) + + Similar functionality is available via the + :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. + + :param whereclause: Optional SQL expression describing the ``WHERE`` + condition of the ``UPDATE`` statement; is equivalent to using the + more modern :meth:`~Update.where()` method to specify the ``WHERE`` + clause. + + :param values: + Optional dictionary which specifies the ``SET`` conditions of the + ``UPDATE``. If left as ``None``, the ``SET`` + conditions are determined from those parameters passed to the + statement during the execution and/or compilation of the + statement. When compiled standalone without any parameters, + the ``SET`` clause generates for all columns. + + Modern applications may prefer to use the generative + :meth:`_expression.Update.values` method to set the values of the + UPDATE statement. + + :param inline: + if True, SQL defaults present on :class:`_schema.Column` objects via + the ``default`` keyword will be compiled 'inline' into the statement + and not pre-executed. This means that their values will not + be available in the dictionary returned from + :meth:`_engine.CursorResult.last_updated_params`. + + :param preserve_parameter_order: if True, the update statement is + expected to receive parameters **only** via the + :meth:`_expression.Update.values` method, + and they must be passed as a Python + ``list`` of 2-tuples. The rendered UPDATE statement will emit the SET + clause for each referenced column maintaining this order. + + .. versionadded:: 1.0.10 + + .. seealso:: + + :ref:`updates_order_parameters` - illustrates the + :meth:`_expression.Update.ordered_values` method. + + If both ``values`` and compile-time bind parameters are present, the + compile-time bind parameters override the information specified + within ``values`` on a per-key basis. + + The keys within ``values`` can be either :class:`_schema.Column` + objects or their string identifiers (specifically the "key" of the + :class:`_schema.Column`, normally but not necessarily equivalent to + its "name"). Normally, the + :class:`_schema.Column` objects used here are expected to be + part of the target :class:`_schema.Table` that is the table + to be updated. However when using MySQL, a multiple-table + UPDATE statement can refer to columns from any of + the 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 :class:`_schema.Column`, + a scalar-returning :func:`_expression.select` construct, + etc. + + When combining :func:`_expression.select` constructs within the + values clause of an :func:`_expression.update` + construct, the subquery represented + by the :func:`_expression.select` should be *correlated* to the + parent table, that is, providing criterion which links the table inside + the subquery to the outer table being updated:: + + users.update().values( + name=select(addresses.c.email_address).\ + where(addresses.c.user_id==users.c.id).\ + scalar_subquery() + ) + + .. seealso:: + + :ref:`inserts_and_updates` - SQL Expression + Language Tutorial + + + """ + return Update(table) + + +def delete(table): + r"""Construct :class:`_expression.Delete` object. + + E.g.:: + + from sqlalchemy import delete + + stmt = ( + delete(user_table). + where(user_table.c.id == 5) + ) + + Similar functionality is available via the + :meth:`_expression.TableClause.delete` 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: The table to delete rows from. + + :param whereclause: Optional SQL expression describing the ``WHERE`` + condition of the ``DELETE`` statement; is equivalent to using the + more modern :meth:`~Delete.where()` method to specify the ``WHERE`` + clause. + + .. seealso:: + + :ref:`deletes` - SQL Expression Tutorial + + """ + return Delete(table) diff --git a/lib/sqlalchemy/sql/_elements_constructors.py b/lib/sqlalchemy/sql/_elements_constructors.py new file mode 100644 index 000000000..a8c9372e0 --- /dev/null +++ b/lib/sqlalchemy/sql/_elements_constructors.py @@ -0,0 +1,1637 @@ +# sql/_elements_constructors.py +# Copyright (C) 2005-2022 the SQLAlchemy authors and contributors +# <see AUTHORS file> +# +# This module is part of SQLAlchemy and is released under +# the MIT License: https://www.opensource.org/licenses/mit-license.php + +import typing +from typing import Any +from typing import cast as _typing_cast +from typing import Optional +from typing import overload +from typing import Type +from typing import TypeVar +from typing import Union + +from . import coercions +from . import operators +from . import roles +from .base import NO_ARG +from .coercions import _document_text_coercion +from .elements import BindParameter +from .elements import BooleanClauseList +from .elements import Case +from .elements import Cast +from .elements import CollationClause +from .elements import CollectionAggregate +from .elements import ColumnClause +from .elements import ColumnElement +from .elements import Extract +from .elements import False_ +from .elements import FunctionFilter +from .elements import Label +from .elements import Null +from .elements import Over +from .elements import TextClause +from .elements import True_ +from .elements import Tuple +from .elements import TypeCoerce +from .elements import UnaryExpression +from .elements import WithinGroup + +if typing.TYPE_CHECKING: + from elements import BinaryExpression + + from . import sqltypes + from .functions import FunctionElement + from .selectable import FromClause + from .type_api import TypeEngine + +_T = TypeVar("_T") + + +def all_(expr): + """Produce an ALL expression. + + For dialects such as that of PostgreSQL, this operator applies + to usage of the :class:`_types.ARRAY` datatype, for that of + MySQL, it may apply to a subquery. e.g.:: + + # renders on PostgreSQL: + # '5 = ALL (somearray)' + expr = 5 == all_(mytable.c.somearray) + + # renders on MySQL: + # '5 = ALL (SELECT value FROM table)' + expr = 5 == all_(select(table.c.value)) + + Comparison to NULL may work using ``None``:: + + None == all_(mytable.c.somearray) + + The any_() / all_() operators also feature a special "operand flipping" + behavior such that if any_() / all_() are used on the left side of a + comparison using a standalone operator such as ``==``, ``!=``, etc. + (not including operator methods such as + :meth:`_sql.ColumnOperators.is_`) the rendered expression is flipped:: + + # would render '5 = ALL (column)` + all_(mytable.c.column) == 5 + + Or with ``None``, which note will not perform + the usual step of rendering "IS" as is normally the case for NULL:: + + # would render 'NULL = ALL(somearray)' + all_(mytable.c.somearray) == None + + .. versionchanged:: 1.4.26 repaired the use of any_() / all_() + comparing to NULL on the right side to be flipped to the left. + + The column-level :meth:`_sql.ColumnElement.all_` method (not to be + confused with :class:`_types.ARRAY` level + :meth:`_types.ARRAY.Comparator.all`) is shorthand for + ``all_(col)``:: + + 5 == mytable.c.somearray.all_() + + .. seealso:: + + :meth:`_sql.ColumnOperators.all_` + + :func:`_expression.any_` + + """ + return CollectionAggregate._create_all(expr) + + +def and_(*clauses): + r"""Produce a conjunction of expressions joined by ``AND``. + + E.g.:: + + from sqlalchemy import and_ + + stmt = select(users_table).where( + and_( + users_table.c.name == 'wendy', + users_table.c.enrolled == True + ) + ) + + The :func:`.and_` conjunction is also available using the + Python ``&`` operator (though note that compound expressions + need to be parenthesized in order to function with Python + operator precedence behavior):: + + stmt = select(users_table).where( + (users_table.c.name == 'wendy') & + (users_table.c.enrolled == True) + ) + + The :func:`.and_` operation is also implicit in some cases; + the :meth:`_expression.Select.where` + method for example can be invoked multiple + times against a statement, which will have the effect of each + clause being combined using :func:`.and_`:: + + stmt = select(users_table).\ + where(users_table.c.name == 'wendy').\ + where(users_table.c.enrolled == True) + + The :func:`.and_` construct must be given at least one positional + argument in order to be valid; a :func:`.and_` construct with no + arguments is ambiguous. To produce an "empty" or dynamically + generated :func:`.and_` expression, from a given list of expressions, + a "default" element of ``True`` should be specified:: + + criteria = and_(True, *expressions) + + The above expression will compile to SQL as the expression ``true`` + or ``1 = 1``, depending on backend, if no other expressions are + present. If expressions are present, then the ``True`` value is + ignored as it does not affect the outcome of an AND expression that + has other elements. + + .. deprecated:: 1.4 The :func:`.and_` element now requires that at + least one argument is passed; creating the :func:`.and_` construct + with no arguments is deprecated, and will emit a deprecation warning + while continuing to produce a blank SQL string. + + .. seealso:: + + :func:`.or_` + + """ + return BooleanClauseList.and_(*clauses) + + +def any_(expr): + """Produce an ANY expression. + + For dialects such as that of PostgreSQL, this operator applies + to usage of the :class:`_types.ARRAY` datatype, for that of + MySQL, it may apply to a subquery. e.g.:: + + # renders on PostgreSQL: + # '5 = ANY (somearray)' + expr = 5 == any_(mytable.c.somearray) + + # renders on MySQL: + # '5 = ANY (SELECT value FROM table)' + expr = 5 == any_(select(table.c.value)) + + Comparison to NULL may work using ``None`` or :func:`_sql.null`:: + + None == any_(mytable.c.somearray) + + The any_() / all_() operators also feature a special "operand flipping" + behavior such that if any_() / all_() are used on the left side of a + comparison using a standalone operator such as ``==``, ``!=``, etc. + (not including operator methods such as + :meth:`_sql.ColumnOperators.is_`) the rendered expression is flipped:: + + # would render '5 = ANY (column)` + any_(mytable.c.column) == 5 + + Or with ``None``, which note will not perform + the usual step of rendering "IS" as is normally the case for NULL:: + + # would render 'NULL = ANY(somearray)' + any_(mytable.c.somearray) == None + + .. versionchanged:: 1.4.26 repaired the use of any_() / all_() + comparing to NULL on the right side to be flipped to the left. + + The column-level :meth:`_sql.ColumnElement.any_` method (not to be + confused with :class:`_types.ARRAY` level + :meth:`_types.ARRAY.Comparator.any`) is shorthand for + ``any_(col)``:: + + 5 = mytable.c.somearray.any_() + + .. seealso:: + + :meth:`_sql.ColumnOperators.any_` + + :func:`_expression.all_` + + """ + return CollectionAggregate._create_any(expr) + + +def asc(column): + """Produce an ascending ``ORDER BY`` clause element. + + e.g.:: + + from sqlalchemy import asc + stmt = select(users_table).order_by(asc(users_table.c.name)) + + will produce SQL as:: + + SELECT id, name FROM user ORDER BY name ASC + + The :func:`.asc` function is a standalone version of the + :meth:`_expression.ColumnElement.asc` + method available on all SQL expressions, + e.g.:: + + + stmt = select(users_table).order_by(users_table.c.name.asc()) + + :param column: A :class:`_expression.ColumnElement` (e.g. + scalar SQL expression) + with which to apply the :func:`.asc` operation. + + .. seealso:: + + :func:`.desc` + + :func:`.nulls_first` + + :func:`.nulls_last` + + :meth:`_expression.Select.order_by` + + """ + return UnaryExpression._create_asc(column) + + +def collate(expression, collation): + """Return the clause ``expression COLLATE collation``. + + e.g.:: + + collate(mycolumn, 'utf8_bin') + + produces:: + + mycolumn COLLATE utf8_bin + + The collation expression is also quoted if it is a case sensitive + identifier, e.g. contains uppercase characters. + + .. versionchanged:: 1.2 quoting is automatically applied to COLLATE + expressions if they are case sensitive. + + """ + return CollationClause._create_collation_expression(expression, collation) + + +def between(expr, lower_bound, upper_bound, symmetric=False): + """Produce a ``BETWEEN`` predicate clause. + + E.g.:: + + from sqlalchemy import between + stmt = select(users_table).where(between(users_table.c.id, 5, 7)) + + Would produce SQL resembling:: + + SELECT id, name FROM user WHERE id BETWEEN :id_1 AND :id_2 + + The :func:`.between` function is a standalone version of the + :meth:`_expression.ColumnElement.between` method available on all + SQL expressions, as in:: + + stmt = select(users_table).where(users_table.c.id.between(5, 7)) + + All arguments passed to :func:`.between`, including the left side + column expression, are coerced from Python scalar values if a + the value is not a :class:`_expression.ColumnElement` subclass. + For example, + three fixed values can be compared as in:: + + print(between(5, 3, 7)) + + Which would produce:: + + :param_1 BETWEEN :param_2 AND :param_3 + + :param expr: a column expression, typically a + :class:`_expression.ColumnElement` + instance or alternatively a Python scalar expression to be coerced + into a column expression, serving as the left side of the ``BETWEEN`` + expression. + + :param lower_bound: a column or Python scalar expression serving as the + lower bound of the right side of the ``BETWEEN`` expression. + + :param upper_bound: a column or Python scalar expression serving as the + upper bound of the right side of the ``BETWEEN`` expression. + + :param symmetric: if True, will render " BETWEEN SYMMETRIC ". Note + that not all databases support this syntax. + + .. versionadded:: 0.9.5 + + .. seealso:: + + :meth:`_expression.ColumnElement.between` + + """ + expr = coercions.expect(roles.ExpressionElementRole, expr) + return expr.between(lower_bound, upper_bound, symmetric=symmetric) + + +def outparam(key, type_=None): + """Create an 'OUT' parameter for usage in functions (stored procedures), + for databases which support them. + + The ``outparam`` can be used like a regular function parameter. + The "output" value will be available from the + :class:`~sqlalchemy.engine.CursorResult` object via its ``out_parameters`` + attribute, which returns a dictionary containing the values. + + """ + return BindParameter(key, None, type_=type_, unique=False, isoutparam=True) + + +@overload +def not_(clause: "BinaryExpression[_T]") -> "BinaryExpression[_T]": + ... + + +@overload +def not_(clause: "ColumnElement[_T]") -> "UnaryExpression[_T]": + ... + + +def not_(clause: "ColumnElement[_T]") -> "ColumnElement[_T]": + """Return a negation of the given clause, i.e. ``NOT(clause)``. + + The ``~`` operator is also overloaded on all + :class:`_expression.ColumnElement` subclasses to produce the + same result. + + """ + + return operators.inv( + _typing_cast( + "ColumnElement[_T]", + coercions.expect(roles.ExpressionElementRole, clause), + ) + ) + + +def bindparam( + key, + value=NO_ARG, + type_: Optional[Union[Type["TypeEngine[_T]"], "TypeEngine[_T]"]] = None, + unique=False, + required=NO_ARG, + quote=None, + callable_=None, + expanding=False, + isoutparam=False, + literal_execute=False, + _compared_to_operator=None, + _compared_to_type=None, + _is_crud=False, +) -> "BindParameter[_T]": + r"""Produce a "bound expression". + + The return value is an instance of :class:`.BindParameter`; this + is a :class:`_expression.ColumnElement` + subclass which represents a so-called + "placeholder" value in a SQL expression, the value of which is + supplied at the point at which the statement in executed against a + database connection. + + In SQLAlchemy, the :func:`.bindparam` construct has + the ability to carry along the actual value that will be ultimately + used at expression time. In this way, it serves not just as + a "placeholder" for eventual population, but also as a means of + representing so-called "unsafe" values which should not be rendered + directly in a SQL statement, but rather should be passed along + to the :term:`DBAPI` as values which need to be correctly escaped + and potentially handled for type-safety. + + When using :func:`.bindparam` explicitly, the use case is typically + one of traditional deferment of parameters; the :func:`.bindparam` + construct accepts a name which can then be referred to at execution + time:: + + from sqlalchemy import bindparam + + stmt = select(users_table).\ + where(users_table.c.name == bindparam('username')) + + The above statement, when rendered, will produce SQL similar to:: + + SELECT id, name FROM user WHERE name = :username + + In order to populate the value of ``:username`` above, the value + would typically be applied at execution time to a method + like :meth:`_engine.Connection.execute`:: + + result = connection.execute(stmt, username='wendy') + + Explicit use of :func:`.bindparam` is also common when producing + UPDATE or DELETE statements that are to be invoked multiple times, + where the WHERE criterion of the statement is to change on each + invocation, such as:: + + stmt = (users_table.update(). + where(user_table.c.name == bindparam('username')). + values(fullname=bindparam('fullname')) + ) + + connection.execute( + stmt, [{"username": "wendy", "fullname": "Wendy Smith"}, + {"username": "jack", "fullname": "Jack Jones"}, + ] + ) + + SQLAlchemy's Core expression system makes wide use of + :func:`.bindparam` in an implicit sense. It is typical that Python + literal values passed to virtually all SQL expression functions are + coerced into fixed :func:`.bindparam` constructs. For example, given + a comparison operation such as:: + + expr = users_table.c.name == 'Wendy' + + The above expression will produce a :class:`.BinaryExpression` + construct, where the left side is the :class:`_schema.Column` object + representing the ``name`` column, and the right side is a + :class:`.BindParameter` representing the literal value:: + + print(repr(expr.right)) + BindParameter('%(4327771088 name)s', 'Wendy', type_=String()) + + The expression above will render SQL such as:: + + user.name = :name_1 + + Where the ``:name_1`` parameter name is an anonymous name. The + actual string ``Wendy`` is not in the rendered string, but is carried + along where it is later used within statement execution. If we + invoke a statement like the following:: + + stmt = select(users_table).where(users_table.c.name == 'Wendy') + result = connection.execute(stmt) + + We would see SQL logging output as:: + + SELECT "user".id, "user".name + FROM "user" + WHERE "user".name = %(name_1)s + {'name_1': 'Wendy'} + + Above, we see that ``Wendy`` is passed as a parameter to the database, + while the placeholder ``:name_1`` is rendered in the appropriate form + for the target database, in this case the PostgreSQL database. + + Similarly, :func:`.bindparam` is invoked automatically when working + with :term:`CRUD` statements as far as the "VALUES" portion is + concerned. The :func:`_expression.insert` construct produces an + ``INSERT`` expression which will, at statement execution time, generate + bound placeholders based on the arguments passed, as in:: + + stmt = users_table.insert() + result = connection.execute(stmt, name='Wendy') + + The above will produce SQL output as:: + + INSERT INTO "user" (name) VALUES (%(name)s) + {'name': 'Wendy'} + + The :class:`_expression.Insert` construct, at + compilation/execution time, rendered a single :func:`.bindparam` + mirroring the column name ``name`` as a result of the single ``name`` + parameter we passed to the :meth:`_engine.Connection.execute` method. + + :param key: + the key (e.g. the name) for this bind param. + Will be used in the generated + SQL statement for dialects that use named parameters. This + value may be modified when part of a compilation operation, + if other :class:`BindParameter` objects exist with the same + key, or if its length is too long and truncation is + required. + + :param value: + Initial value for this bind param. Will be used at statement + execution time as the value for this parameter passed to the + DBAPI, if no other value is indicated to the statement execution + method for this particular parameter name. Defaults to ``None``. + + :param callable\_: + A callable function that takes the place of "value". The function + will be called at statement execution time to determine the + ultimate value. Used for scenarios where the actual bind + value cannot be determined at the point at which the clause + construct is created, but embedded bind values are still desirable. + + :param type\_: + A :class:`.TypeEngine` class or instance representing an optional + datatype for this :func:`.bindparam`. If not passed, a type + may be determined automatically for the bind, based on the given + value; for example, trivial Python types such as ``str``, + ``int``, ``bool`` + may result in the :class:`.String`, :class:`.Integer` or + :class:`.Boolean` types being automatically selected. + + The type of a :func:`.bindparam` is significant especially in that + the type will apply pre-processing to the value before it is + passed to the database. For example, a :func:`.bindparam` which + refers to a datetime value, and is specified as holding the + :class:`.DateTime` type, may apply conversion needed to the + value (such as stringification on SQLite) before passing the value + to the database. + + :param unique: + if True, the key name of this :class:`.BindParameter` will be + modified if another :class:`.BindParameter` of the same name + already has been located within the containing + expression. This flag is used generally by the internals + when producing so-called "anonymous" bound expressions, it + isn't generally applicable to explicitly-named :func:`.bindparam` + constructs. + + :param required: + If ``True``, a value is required at execution time. If not passed, + it defaults to ``True`` if neither :paramref:`.bindparam.value` + or :paramref:`.bindparam.callable` were passed. If either of these + parameters are present, then :paramref:`.bindparam.required` + defaults to ``False``. + + :param quote: + True if this parameter name requires quoting and is not + currently known as a SQLAlchemy reserved word; this currently + only applies to the Oracle backend, where bound names must + sometimes be quoted. + + :param isoutparam: + if True, the parameter should be treated like a stored procedure + "OUT" parameter. This applies to backends such as Oracle which + support OUT parameters. + + :param expanding: + if True, this parameter will be treated as an "expanding" parameter + at execution time; the parameter value is expected to be a sequence, + rather than a scalar value, and the string SQL statement will + be transformed on a per-execution basis to accommodate the sequence + with a variable number of parameter slots passed to the DBAPI. + This is to allow statement caching to be used in conjunction with + an IN clause. + + .. seealso:: + + :meth:`.ColumnOperators.in_` + + :ref:`baked_in` - with baked queries + + .. note:: The "expanding" feature does not support "executemany"- + style parameter sets. + + .. versionadded:: 1.2 + + .. 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 + render the final value of the parameter into the SQL statement at + statement execution time, omitting the value from the parameter + dictionary / list passed to DBAPI ``cursor.execute()``. This + produces a similar effect as that of using the ``literal_binds``, + compilation flag, however takes place as the statement is sent to + the DBAPI ``cursor.execute()`` method, rather than when the statement + is compiled. The primary use of this + capability is for rendering LIMIT / OFFSET clauses for database + drivers that can't accommodate for bound parameters in these + contexts, while allowing SQL constructs to be cacheable at the + compilation level. + + .. versionadded:: 1.4 Added "post compile" bound parameters + + .. seealso:: + + :ref:`change_4808`. + + """ + return BindParameter( + key, + value, + type_, + unique, + required, + quote, + callable_, + expanding, + isoutparam, + literal_execute, + _compared_to_operator, + _compared_to_type, + _is_crud, + ) + + +def case(*whens, value=None, else_=None) -> "Case[Any]": + r"""Produce a ``CASE`` expression. + + The ``CASE`` construct in SQL is a conditional object that + acts somewhat analogously to an "if/then" construct in other + languages. It returns an instance of :class:`.Case`. + + :func:`.case` in its usual form is passed a series of "when" + constructs, that is, a list of conditions and results as tuples:: + + from sqlalchemy import case + + stmt = select(users_table).\ + where( + case( + (users_table.c.name == 'wendy', 'W'), + (users_table.c.name == 'jack', 'J'), + else_='E' + ) + ) + + The above statement will produce SQL resembling:: + + SELECT id, name FROM user + WHERE CASE + WHEN (name = :name_1) THEN :param_1 + WHEN (name = :name_2) THEN :param_2 + ELSE :param_3 + END + + When simple equality expressions of several values against a single + parent column are needed, :func:`.case` also has a "shorthand" format + used via the + :paramref:`.case.value` parameter, which is passed a column + expression to be compared. In this form, the :paramref:`.case.whens` + parameter is passed as a dictionary containing expressions to be + compared against keyed to result expressions. The statement below is + equivalent to the preceding statement:: + + stmt = select(users_table).\ + where( + case( + {"wendy": "W", "jack": "J"}, + value=users_table.c.name, + else_='E' + ) + ) + + The values which are accepted as result values in + :paramref:`.case.whens` as well as with :paramref:`.case.else_` are + coerced from Python literals into :func:`.bindparam` constructs. + SQL expressions, e.g. :class:`_expression.ColumnElement` constructs, + are accepted + as well. To coerce a literal string expression into a constant + expression rendered inline, use the :func:`_expression.literal_column` + construct, + as in:: + + from sqlalchemy import case, literal_column + + case( + ( + orderline.c.qty > 100, + literal_column("'greaterthan100'") + ), + ( + orderline.c.qty > 10, + literal_column("'greaterthan10'") + ), + else_=literal_column("'lessthan10'") + ) + + The above will render the given constants without using bound + parameters for the result values (but still for the comparison + values), as in:: + + CASE + WHEN (orderline.qty > :qty_1) THEN 'greaterthan100' + WHEN (orderline.qty > :qty_2) THEN 'greaterthan10' + ELSE 'lessthan10' + END + + :param \*whens: The criteria to be compared against, + :paramref:`.case.whens` accepts two different forms, based on + whether or not :paramref:`.case.value` is used. + + .. versionchanged:: 1.4 the :func:`_sql.case` + function now accepts the series of WHEN conditions positionally + + In the first form, it accepts a list of 2-tuples; each 2-tuple + consists of ``(<sql expression>, <value>)``, where the SQL + expression is a boolean expression and "value" is a resulting value, + e.g.:: + + case( + (users_table.c.name == 'wendy', 'W'), + (users_table.c.name == 'jack', 'J') + ) + + In the second form, it accepts a Python dictionary of comparison + values mapped to a resulting value; this form requires + :paramref:`.case.value` to be present, and values will be compared + using the ``==`` operator, e.g.:: + + case( + {"wendy": "W", "jack": "J"}, + value=users_table.c.name + ) + + :param value: An optional SQL expression which will be used as a + fixed "comparison point" for candidate values within a dictionary + passed to :paramref:`.case.whens`. + + :param else\_: An optional SQL expression which will be the evaluated + result of the ``CASE`` construct if all expressions within + :paramref:`.case.whens` evaluate to false. When omitted, most + databases will produce a result of NULL if none of the "when" + expressions evaluate to true. + + + """ + return Case(*whens, value=value, else_=else_) + + +def cast( + expression: ColumnElement, + type_: Union[Type["TypeEngine[_T]"], "TypeEngine[_T]"], +) -> "Cast[_T]": + r"""Produce a ``CAST`` expression. + + :func:`.cast` returns an instance of :class:`.Cast`. + + E.g.:: + + from sqlalchemy import cast, Numeric + + stmt = select(cast(product_table.c.unit_price, Numeric(10, 4))) + + The above statement will produce SQL resembling:: + + SELECT CAST(unit_price AS NUMERIC(10, 4)) FROM product + + The :func:`.cast` function performs two distinct functions when + used. The first is that it renders the ``CAST`` expression within + the resulting SQL string. The second is that it associates the given + type (e.g. :class:`.TypeEngine` class or instance) with the column + expression on the Python side, which means the expression will take + on the expression operator behavior associated with that type, + as well as the bound-value handling and result-row-handling behavior + of the type. + + .. versionchanged:: 0.9.0 :func:`.cast` now applies the given type + to the expression such that it takes effect on the bound-value, + e.g. the Python-to-database direction, in addition to the + result handling, e.g. database-to-Python, direction. + + An alternative to :func:`.cast` is the :func:`.type_coerce` function. + This function performs the second task of associating an expression + with a specific type, but does not render the ``CAST`` expression + in SQL. + + :param expression: A SQL expression, such as a + :class:`_expression.ColumnElement` + expression or a Python string which will be coerced into a bound + literal value. + + :param type\_: A :class:`.TypeEngine` class or instance indicating + the type to which the ``CAST`` should apply. + + .. seealso:: + + :ref:`coretutorial_casts` + + :func:`.type_coerce` - an alternative to CAST that coerces the type + on the Python side only, which is often sufficient to generate the + correct SQL and data coercion. + + + """ + return Cast(expression, type_) + + +def column( + text: str, + type_: Optional[Union[Type["TypeEngine[_T]"], "TypeEngine[_T]"]] = None, + is_literal: bool = False, + _selectable: Optional["FromClause"] = None, +) -> "ColumnClause[_T]": + """Produce a :class:`.ColumnClause` object. + + The :class:`.ColumnClause` is a lightweight analogue to the + :class:`_schema.Column` class. The :func:`_expression.column` + function can + be invoked with just a name alone, as in:: + + from sqlalchemy import column + + id, name = column("id"), column("name") + stmt = select(id, name).select_from("user") + + The above statement would produce SQL like:: + + SELECT id, name FROM user + + Once constructed, :func:`_expression.column` + may be used like any other SQL + expression element such as within :func:`_expression.select` + constructs:: + + from sqlalchemy.sql import column + + id, name = column("id"), column("name") + stmt = select(id, name).select_from("user") + + The text handled by :func:`_expression.column` + is assumed to be handled + like the name of a database column; if the string contains mixed case, + special characters, or matches a known reserved word on the target + backend, the column expression will render using the quoting + behavior determined by the backend. To produce a textual SQL + expression that is rendered exactly without any quoting, + use :func:`_expression.literal_column` instead, + or pass ``True`` as the + value of :paramref:`_expression.column.is_literal`. Additionally, + full SQL + statements are best handled using the :func:`_expression.text` + construct. + + :func:`_expression.column` can be used in a table-like + fashion by combining it with the :func:`.table` function + (which is the lightweight analogue to :class:`_schema.Table` + ) to produce + a working table construct with minimal boilerplate:: + + from sqlalchemy import table, column, select + + user = table("user", + column("id"), + column("name"), + column("description"), + ) + + stmt = select(user.c.description).where(user.c.name == 'wendy') + + A :func:`_expression.column` / :func:`.table` + construct like that illustrated + above can be created in an + ad-hoc fashion and is not associated with any + :class:`_schema.MetaData`, DDL, or events, unlike its + :class:`_schema.Table` counterpart. + + .. versionchanged:: 1.0.0 :func:`_expression.column` can now + be imported from the plain ``sqlalchemy`` namespace like any + other SQL element. + + :param text: the text of the element. + + :param type: :class:`_types.TypeEngine` object which can associate + this :class:`.ColumnClause` with a type. + + :param is_literal: if True, the :class:`.ColumnClause` is assumed to + be an exact expression that will be delivered to the output with no + quoting rules applied regardless of case sensitive settings. the + :func:`_expression.literal_column()` function essentially invokes + :func:`_expression.column` while passing ``is_literal=True``. + + .. seealso:: + + :class:`_schema.Column` + + :func:`_expression.literal_column` + + :func:`.table` + + :func:`_expression.text` + + :ref:`sqlexpression_literal_column` + + """ + self = ColumnClause.__new__(ColumnClause) + self.__init__(text, type_, is_literal, _selectable) + return self + + +def desc(column): + """Produce a descending ``ORDER BY`` clause element. + + e.g.:: + + from sqlalchemy import desc + + stmt = select(users_table).order_by(desc(users_table.c.name)) + + will produce SQL as:: + + SELECT id, name FROM user ORDER BY name DESC + + The :func:`.desc` function is a standalone version of the + :meth:`_expression.ColumnElement.desc` + method available on all SQL expressions, + e.g.:: + + + stmt = select(users_table).order_by(users_table.c.name.desc()) + + :param column: A :class:`_expression.ColumnElement` (e.g. + scalar SQL expression) + with which to apply the :func:`.desc` operation. + + .. seealso:: + + :func:`.asc` + + :func:`.nulls_first` + + :func:`.nulls_last` + + :meth:`_expression.Select.order_by` + + """ + return UnaryExpression._create_desc(column) + + +def distinct(expr): + """Produce an column-expression-level unary ``DISTINCT`` clause. + + This applies the ``DISTINCT`` keyword to an individual column + expression, and is typically contained within an aggregate function, + as in:: + + from sqlalchemy import distinct, func + stmt = select(func.count(distinct(users_table.c.name))) + + The above would produce an expression resembling:: + + SELECT COUNT(DISTINCT name) FROM user + + The :func:`.distinct` function is also available as a column-level + method, e.g. :meth:`_expression.ColumnElement.distinct`, as in:: + + stmt = select(func.count(users_table.c.name.distinct())) + + The :func:`.distinct` operator is different from the + :meth:`_expression.Select.distinct` method of + :class:`_expression.Select`, + which produces a ``SELECT`` statement + with ``DISTINCT`` applied to the result set as a whole, + e.g. a ``SELECT DISTINCT`` expression. See that method for further + information. + + .. seealso:: + + :meth:`_expression.ColumnElement.distinct` + + :meth:`_expression.Select.distinct` + + :data:`.func` + + """ + return UnaryExpression._create_distinct(expr) + + +def extract(field: str, expr: ColumnElement) -> "Extract[sqltypes.Integer]": + """Return a :class:`.Extract` construct. + + This is typically available as :func:`.extract` + as well as ``func.extract`` from the + :data:`.func` namespace. + + :param field: The field to extract. + + :param expr: A column or Python scalar expression serving as the + right side of the ``EXTRACT`` expression. + + E.g.:: + + from sqlalchemy import extract + from sqlalchemy import table, column + + logged_table = table("user", + column("id"), + column("date_created"), + ) + + stmt = select(logged_table.c.id).where( + extract("YEAR", logged_table.c.date_created) == 2021 + ) + + In the above example, the statement is used to select ids from the + database where the ``YEAR`` component matches a specific value. + + Similarly, one can also select an extracted component:: + + stmt = select( + extract("YEAR", logged_table.c.date_created) + ).where(logged_table.c.id == 1) + + The implementation of ``EXTRACT`` may vary across database backends. + Users are reminded to consult their database documentation. + """ + return Extract(field, expr) + + +def false(): + """Return a :class:`.False_` construct. + + E.g.:: + + >>> from sqlalchemy import false + >>> print(select(t.c.x).where(false())) + SELECT x FROM t WHERE false + + A backend which does not support true/false constants will render as + an expression against 1 or 0:: + + >>> print(select(t.c.x).where(false())) + SELECT x FROM t WHERE 0 = 1 + + The :func:`.true` and :func:`.false` constants also feature + "short circuit" operation within an :func:`.and_` or :func:`.or_` + conjunction:: + + >>> print(select(t.c.x).where(or_(t.c.x > 5, true()))) + SELECT x FROM t WHERE true + + >>> print(select(t.c.x).where(and_(t.c.x > 5, false()))) + SELECT x FROM t WHERE false + + .. versionchanged:: 0.9 :func:`.true` and :func:`.false` feature + better integrated behavior within conjunctions and on dialects + that don't support true/false constants. + + .. seealso:: + + :func:`.true` + + """ + + return False_._instance() + + +def funcfilter(func, *criterion) -> "FunctionFilter": + """Produce a :class:`.FunctionFilter` object against a function. + + Used against aggregate and window functions, + for database backends that support the "FILTER" clause. + + E.g.:: + + from sqlalchemy import funcfilter + funcfilter(func.count(1), MyClass.name == 'some name') + + Would produce "COUNT(1) FILTER (WHERE myclass.name = 'some name')". + + This function is also available from the :data:`~.expression.func` + construct itself via the :meth:`.FunctionElement.filter` method. + + .. versionadded:: 1.0.0 + + .. seealso:: + + :ref:`tutorial_functions_within_group` - in the + :ref:`unified_tutorial` + + :meth:`.FunctionElement.filter` + + """ + return FunctionFilter(func, *criterion) + + +def label( + name: str, + element: ColumnElement[_T], + type_: Optional[Union[Type["TypeEngine[_T]"], "TypeEngine[_T]"]] = None, +) -> "Label[_T]": + """Return a :class:`Label` object for the + given :class:`_expression.ColumnElement`. + + A label changes the name of an element in the columns clause of a + ``SELECT`` statement, typically via the ``AS`` SQL keyword. + + This functionality is more conveniently available via the + :meth:`_expression.ColumnElement.label` method on + :class:`_expression.ColumnElement`. + + :param name: label name + + :param obj: a :class:`_expression.ColumnElement`. + + """ + return Label(name, element, type_) + + +def null(): + """Return a constant :class:`.Null` construct.""" + + return Null._instance() + + +def nulls_first(column): + """Produce the ``NULLS FIRST`` modifier for an ``ORDER BY`` expression. + + :func:`.nulls_first` is intended to modify the expression produced + by :func:`.asc` or :func:`.desc`, and indicates how NULL values + should be handled when they are encountered during ordering:: + + + from sqlalchemy import desc, nulls_first + + stmt = select(users_table).order_by( + nulls_first(desc(users_table.c.name))) + + The SQL expression from the above would resemble:: + + SELECT id, name FROM user ORDER BY name DESC NULLS FIRST + + Like :func:`.asc` and :func:`.desc`, :func:`.nulls_first` is typically + invoked from the column expression itself using + :meth:`_expression.ColumnElement.nulls_first`, + rather than as its standalone + function version, as in:: + + stmt = select(users_table).order_by( + users_table.c.name.desc().nulls_first()) + + .. versionchanged:: 1.4 :func:`.nulls_first` is renamed from + :func:`.nullsfirst` in previous releases. + The previous name remains available for backwards compatibility. + + .. seealso:: + + :func:`.asc` + + :func:`.desc` + + :func:`.nulls_last` + + :meth:`_expression.Select.order_by` + + """ + return UnaryExpression._create_nulls_first(column) + + +def nulls_last(column): + """Produce the ``NULLS LAST`` modifier for an ``ORDER BY`` expression. + + :func:`.nulls_last` is intended to modify the expression produced + by :func:`.asc` or :func:`.desc`, and indicates how NULL values + should be handled when they are encountered during ordering:: + + + from sqlalchemy import desc, nulls_last + + stmt = select(users_table).order_by( + nulls_last(desc(users_table.c.name))) + + The SQL expression from the above would resemble:: + + SELECT id, name FROM user ORDER BY name DESC NULLS LAST + + Like :func:`.asc` and :func:`.desc`, :func:`.nulls_last` is typically + invoked from the column expression itself using + :meth:`_expression.ColumnElement.nulls_last`, + rather than as its standalone + function version, as in:: + + stmt = select(users_table).order_by( + users_table.c.name.desc().nulls_last()) + + .. versionchanged:: 1.4 :func:`.nulls_last` is renamed from + :func:`.nullslast` in previous releases. + The previous name remains available for backwards compatibility. + + .. seealso:: + + :func:`.asc` + + :func:`.desc` + + :func:`.nulls_first` + + :meth:`_expression.Select.order_by` + + """ + return UnaryExpression._create_nulls_last(column) + + +def or_(*clauses): + """Produce a conjunction of expressions joined by ``OR``. + + E.g.:: + + from sqlalchemy import or_ + + stmt = select(users_table).where( + or_( + users_table.c.name == 'wendy', + users_table.c.name == 'jack' + ) + ) + + The :func:`.or_` conjunction is also available using the + Python ``|`` operator (though note that compound expressions + need to be parenthesized in order to function with Python + operator precedence behavior):: + + stmt = select(users_table).where( + (users_table.c.name == 'wendy') | + (users_table.c.name == 'jack') + ) + + The :func:`.or_` construct must be given at least one positional + argument in order to be valid; a :func:`.or_` construct with no + arguments is ambiguous. To produce an "empty" or dynamically + generated :func:`.or_` expression, from a given list of expressions, + a "default" element of ``False`` should be specified:: + + or_criteria = or_(False, *expressions) + + The above expression will compile to SQL as the expression ``false`` + or ``0 = 1``, depending on backend, if no other expressions are + present. If expressions are present, then the ``False`` value is + ignored as it does not affect the outcome of an OR expression which + has other elements. + + .. deprecated:: 1.4 The :func:`.or_` element now requires that at + least one argument is passed; creating the :func:`.or_` construct + with no arguments is deprecated, and will emit a deprecation warning + while continuing to produce a blank SQL string. + + .. seealso:: + + :func:`.and_` + + """ + return BooleanClauseList.or_(*clauses) + + +def over( + element: "FunctionElement[_T]", + partition_by=None, + order_by=None, + range_=None, + rows=None, +) -> "Over[_T]": + r"""Produce an :class:`.Over` object against a function. + + Used against aggregate or so-called "window" functions, + for database backends that support window functions. + + :func:`_expression.over` is usually called using + the :meth:`.FunctionElement.over` method, e.g.:: + + func.row_number().over(order_by=mytable.c.some_column) + + Would produce:: + + ROW_NUMBER() OVER(ORDER BY some_column) + + Ranges are also possible using the :paramref:`.expression.over.range_` + and :paramref:`.expression.over.rows` parameters. These + mutually-exclusive parameters each accept a 2-tuple, which contains + a combination of integers and None:: + + func.row_number().over( + order_by=my_table.c.some_column, range_=(None, 0)) + + The above would produce:: + + ROW_NUMBER() OVER(ORDER BY some_column + RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) + + A value of ``None`` indicates "unbounded", a + value of zero indicates "current row", and negative / positive + integers indicate "preceding" and "following": + + * RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING:: + + func.row_number().over(order_by='x', range_=(-5, 10)) + + * ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW:: + + func.row_number().over(order_by='x', rows=(None, 0)) + + * RANGE BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING:: + + func.row_number().over(order_by='x', range_=(-2, None)) + + * RANGE BETWEEN 1 FOLLOWING AND 3 FOLLOWING:: + + func.row_number().over(order_by='x', range_=(1, 3)) + + .. versionadded:: 1.1 support for RANGE / ROWS within a window + + + :param element: a :class:`.FunctionElement`, :class:`.WithinGroup`, + or other compatible construct. + :param partition_by: a column element or string, or a list + of such, that will be used as the PARTITION BY clause + of the OVER construct. + :param order_by: a column element or string, or a list + of such, that will be used as the ORDER BY clause + of the OVER construct. + :param range\_: optional range clause for the window. This is a + tuple value which can contain integer values or ``None``, + and will render a RANGE BETWEEN PRECEDING / FOLLOWING clause. + + .. versionadded:: 1.1 + + :param rows: optional rows clause for the window. This is a tuple + value which can contain integer values or None, and will render + a ROWS BETWEEN PRECEDING / FOLLOWING clause. + + .. versionadded:: 1.1 + + This function is also available from the :data:`~.expression.func` + construct itself via the :meth:`.FunctionElement.over` method. + + .. seealso:: + + :ref:`tutorial_window_functions` - in the :ref:`unified_tutorial` + + :data:`.expression.func` + + :func:`_expression.within_group` + + """ + return Over(element, partition_by, order_by, range_, rows) + + +@_document_text_coercion("text", ":func:`.text`", ":paramref:`.text.text`") +def text(text): + r"""Construct a new :class:`_expression.TextClause` clause, + representing + a textual SQL string directly. + + E.g.:: + + from sqlalchemy import text + + t = text("SELECT * FROM users") + result = connection.execute(t) + + The advantages :func:`_expression.text` + provides over a plain string are + backend-neutral support for bind parameters, per-statement + execution options, as well as + bind parameter and result-column typing behavior, allowing + SQLAlchemy type constructs to play a role when executing + a statement that is specified literally. The construct can also + be provided with a ``.c`` collection of column elements, allowing + it to be embedded in other SQL expression constructs as a subquery. + + Bind parameters are specified by name, using the format ``:name``. + E.g.:: + + t = text("SELECT * FROM users WHERE id=:user_id") + result = connection.execute(t, user_id=12) + + For SQL statements where a colon is required verbatim, as within + an inline string, use a backslash to escape:: + + t = text("SELECT * FROM users WHERE name='\:username'") + + The :class:`_expression.TextClause` + construct includes methods which can + provide information about the bound parameters as well as the column + values which would be returned from the textual statement, assuming + it's an executable SELECT type of statement. The + :meth:`_expression.TextClause.bindparams` + method is used to provide bound + parameter detail, and :meth:`_expression.TextClause.columns` + method allows + specification of return columns including names and types:: + + t = text("SELECT * FROM users WHERE id=:user_id").\ + bindparams(user_id=7).\ + columns(id=Integer, name=String) + + for id, name in connection.execute(t): + print(id, name) + + The :func:`_expression.text` construct is used in cases when + a literal string SQL fragment is specified as part of a larger query, + such as for the WHERE clause of a SELECT statement:: + + s = select(users.c.id, users.c.name).where(text("id=:user_id")) + result = connection.execute(s, user_id=12) + + :func:`_expression.text` is also used for the construction + of a full, standalone statement using plain text. + As such, SQLAlchemy refers + to it as an :class:`.Executable` object and may be used + like any other statement passed to an ``.execute()`` method. + + :param text: + the text of the SQL statement to be created. Use ``:<param>`` + to specify bind parameters; they will be compiled to their + engine-specific format. + + .. seealso:: + + :ref:`sqlexpression_text` - in the Core tutorial + + + """ + return TextClause(text) + + +def true(): + """Return a constant :class:`.True_` construct. + + E.g.:: + + >>> from sqlalchemy import true + >>> print(select(t.c.x).where(true())) + SELECT x FROM t WHERE true + + A backend which does not support true/false constants will render as + an expression against 1 or 0:: + + >>> print(select(t.c.x).where(true())) + SELECT x FROM t WHERE 1 = 1 + + The :func:`.true` and :func:`.false` constants also feature + "short circuit" operation within an :func:`.and_` or :func:`.or_` + conjunction:: + + >>> print(select(t.c.x).where(or_(t.c.x > 5, true()))) + SELECT x FROM t WHERE true + + >>> print(select(t.c.x).where(and_(t.c.x > 5, false()))) + SELECT x FROM t WHERE false + + .. versionchanged:: 0.9 :func:`.true` and :func:`.false` feature + better integrated behavior within conjunctions and on dialects + that don't support true/false constants. + + .. seealso:: + + :func:`.false` + + """ + + return True_._instance() + + +def tuple_(*clauses: roles.ExpressionElementRole, types=None) -> "Tuple": + """Return a :class:`.Tuple`. + + Main usage is to produce a composite IN construct using + :meth:`.ColumnOperators.in_` :: + + from sqlalchemy import tuple_ + + tuple_(table.c.col1, table.c.col2).in_( + [(1, 2), (5, 12), (10, 19)] + ) + + .. versionchanged:: 1.3.6 Added support for SQLite IN tuples. + + .. warning:: + + The composite IN construct is not supported by all backends, and is + currently known to work on PostgreSQL, MySQL, and SQLite. + Unsupported backends will raise a subclass of + :class:`~sqlalchemy.exc.DBAPIError` when such an expression is + invoked. + + """ + return Tuple(*clauses, types=types) + + +def type_coerce( + expression: "ColumnElement", + type_: Union[Type["TypeEngine[_T]"], "TypeEngine[_T]"], +) -> "TypeCoerce[_T]": + r"""Associate a SQL expression with a particular type, without rendering + ``CAST``. + + E.g.:: + + from sqlalchemy import type_coerce + + stmt = select(type_coerce(log_table.date_string, StringDateTime())) + + The above construct will produce a :class:`.TypeCoerce` object, which + does not modify the rendering in any way on the SQL side, with the + possible exception of a generated label if used in a columns clause + context:: + + SELECT date_string AS date_string FROM log + + When result rows are fetched, the ``StringDateTime`` type processor + will be applied to result rows on behalf of the ``date_string`` column. + + .. note:: the :func:`.type_coerce` construct does not render any + SQL syntax of its own, including that it does not imply + parenthesization. Please use :meth:`.TypeCoerce.self_group` + if explicit parenthesization is required. + + In order to provide a named label for the expression, use + :meth:`_expression.ColumnElement.label`:: + + stmt = select( + type_coerce(log_table.date_string, StringDateTime()).label('date') + ) + + + A type that features bound-value handling will also have that behavior + take effect when literal values or :func:`.bindparam` constructs are + passed to :func:`.type_coerce` as targets. + For example, if a type implements the + :meth:`.TypeEngine.bind_expression` + method or :meth:`.TypeEngine.bind_processor` method or equivalent, + these functions will take effect at statement compilation/execution + time when a literal value is passed, as in:: + + # bound-value handling of MyStringType will be applied to the + # literal value "some string" + stmt = select(type_coerce("some string", MyStringType)) + + When using :func:`.type_coerce` with composed expressions, note that + **parenthesis are not applied**. If :func:`.type_coerce` is being + used in an operator context where the parenthesis normally present from + CAST are necessary, use the :meth:`.TypeCoerce.self_group` method:: + + >>> some_integer = column("someint", Integer) + >>> some_string = column("somestr", String) + >>> expr = type_coerce(some_integer + 5, String) + some_string + >>> print(expr) + someint + :someint_1 || somestr + >>> expr = type_coerce(some_integer + 5, String).self_group() + some_string + >>> print(expr) + (someint + :someint_1) || somestr + + :param expression: A SQL expression, such as a + :class:`_expression.ColumnElement` + expression or a Python string which will be coerced into a bound + literal value. + + :param type\_: A :class:`.TypeEngine` class or instance indicating + the type to which the expression is coerced. + + .. seealso:: + + :ref:`coretutorial_casts` + + :func:`.cast` + + """ # noqa + return TypeCoerce(expression, type_) + + +def within_group( + element: "FunctionElement[_T]", *order_by: roles.OrderByRole +) -> "WithinGroup[_T]": + r"""Produce a :class:`.WithinGroup` object against a function. + + Used against so-called "ordered set aggregate" and "hypothetical + set aggregate" functions, including :class:`.percentile_cont`, + :class:`.rank`, :class:`.dense_rank`, etc. + + :func:`_expression.within_group` is usually called using + the :meth:`.FunctionElement.within_group` method, e.g.:: + + from sqlalchemy import within_group + stmt = select( + department.c.id, + func.percentile_cont(0.5).within_group( + department.c.salary.desc() + ) + ) + + The above statement would produce SQL similar to + ``SELECT department.id, percentile_cont(0.5) + WITHIN GROUP (ORDER BY department.salary DESC)``. + + :param element: a :class:`.FunctionElement` construct, typically + generated by :data:`~.expression.func`. + :param \*order_by: one or more column elements that will be used + as the ORDER BY clause of the WITHIN GROUP construct. + + .. versionadded:: 1.1 + + .. seealso:: + + :ref:`tutorial_functions_within_group` - in the + :ref:`unified_tutorial` + + :data:`.expression.func` + + :func:`_expression.over` + + """ + return WithinGroup(element, *order_by) diff --git a/lib/sqlalchemy/sql/_selectable_constructors.py b/lib/sqlalchemy/sql/_selectable_constructors.py new file mode 100644 index 000000000..4b67c12f0 --- /dev/null +++ b/lib/sqlalchemy/sql/_selectable_constructors.py @@ -0,0 +1,467 @@ +# sql/_selectable_constructors.py +# Copyright (C) 2005-2022 the SQLAlchemy authors and contributors +# <see AUTHORS file> +# +# This module is part of SQLAlchemy and is released under +# the MIT License: https://www.opensource.org/licenses/mit-license.php + +from typing import Any +from typing import Type +from typing import Union + +from . import coercions +from . import roles +from .elements import ColumnClause +from .selectable import Alias +from .selectable import CompoundSelect +from .selectable import Exists +from .selectable import Join +from .selectable import Lateral +from .selectable import Select +from .selectable import TableClause +from .selectable import TableSample +from .selectable import Values + + +def alias(selectable, name=None, flat=False): + """Return an :class:`_expression.Alias` object. + + An :class:`_expression.Alias` represents any + :class:`_expression.FromClause` + with an alternate name assigned within SQL, typically using the ``AS`` + clause when generated, e.g. ``SELECT * FROM table AS aliasname``. + + Similar functionality is available via the + :meth:`_expression.FromClause.alias` + method available on all :class:`_expression.FromClause` subclasses. + In terms of + a SELECT object as generated from the :func:`_expression.select` + function, the :meth:`_expression.SelectBase.alias` method returns an + :class:`_expression.Alias` or similar object which represents a named, + parenthesized subquery. + + When an :class:`_expression.Alias` is created from a + :class:`_schema.Table` object, + this has the effect of the table being rendered + as ``tablename AS aliasname`` in a SELECT statement. + + For :func:`_expression.select` objects, the effect is that of + creating a named subquery, i.e. ``(select ...) AS aliasname``. + + The ``name`` parameter is optional, and provides the name + to use in the rendered SQL. If blank, an "anonymous" name + will be deterministically generated at compile time. + Deterministic means the name is guaranteed to be unique against + other constructs used in the same statement, and will also be the + same name for each successive compilation of the same statement + object. + + :param selectable: any :class:`_expression.FromClause` subclass, + such as a table, select statement, etc. + + :param name: string name to be assigned as the alias. + If ``None``, a name will be deterministically generated + at compile time. + + :param flat: Will be passed through to if the given selectable + is an instance of :class:`_expression.Join` - see + :meth:`_expression.Join.alias` + for details. + + """ + return Alias._factory(selectable, name=name, flat=flat) + + +def cte(selectable, name=None, recursive=False): + r"""Return a new :class:`_expression.CTE`, + or Common Table Expression instance. + + Please see :meth:`_expression.HasCTE.cte` for detail on CTE usage. + + """ + return coercions.expect(roles.HasCTERole, selectable).cte( + name=name, recursive=recursive + ) + + +def except_(*selects): + r"""Return an ``EXCEPT`` of multiple selectables. + + The returned object is an instance of + :class:`_expression.CompoundSelect`. + + :param \*selects: + a list of :class:`_expression.Select` instances. + + """ + return CompoundSelect._create_except(*selects) + + +def except_all(*selects): + r"""Return an ``EXCEPT ALL`` of multiple selectables. + + The returned object is an instance of + :class:`_expression.CompoundSelect`. + + :param \*selects: + a list of :class:`_expression.Select` instances. + + """ + return CompoundSelect._create_except_all(*selects) + + +def exists(__argument=None): + """Construct a new :class:`_expression.Exists` construct. + + The :func:`_sql.exists` can be invoked by itself to produce an + :class:`_sql.Exists` construct, which will accept simple WHERE + criteria:: + + exists_criteria = exists().where(table1.c.col1 == table2.c.col2) + + However, for greater flexibility in constructing the SELECT, an + existing :class:`_sql.Select` construct may be converted to an + :class:`_sql.Exists`, most conveniently by making use of the + :meth:`_sql.SelectBase.exists` method:: + + exists_criteria = ( + select(table2.c.col2). + where(table1.c.col1 == table2.c.col2). + exists() + ) + + The EXISTS criteria is then used inside of an enclosing SELECT:: + + stmt = select(table1.c.col1).where(exists_criteria) + + The above statement will then be of the form:: + + SELECT col1 FROM table1 WHERE EXISTS + (SELECT table2.col2 FROM table2 WHERE table2.col2 = table1.col1) + + .. seealso:: + + :ref:`tutorial_exists` - in the :term:`2.0 style` tutorial. + + :meth:`_sql.SelectBase.exists` - method to transform a ``SELECT`` to an + ``EXISTS`` clause. + + """ # noqa E501 + + return Exists(__argument) + + +def intersect(*selects): + r"""Return an ``INTERSECT`` of multiple selectables. + + The returned object is an instance of + :class:`_expression.CompoundSelect`. + + :param \*selects: + a list of :class:`_expression.Select` instances. + + """ + return CompoundSelect._create_intersect(*selects) + + +def intersect_all(*selects): + r"""Return an ``INTERSECT ALL`` of multiple selectables. + + The returned object is an instance of + :class:`_expression.CompoundSelect`. + + :param \*selects: + a list of :class:`_expression.Select` instances. + + + """ + return CompoundSelect._create_intersect_all(*selects) + + +def join(left, right, onclause=None, isouter=False, full=False): + """Produce a :class:`_expression.Join` object, given two + :class:`_expression.FromClause` + expressions. + + E.g.:: + + j = join(user_table, address_table, + user_table.c.id == address_table.c.user_id) + stmt = select(user_table).select_from(j) + + would emit SQL along the lines of:: + + SELECT user.id, user.name FROM user + JOIN address ON user.id = address.user_id + + Similar functionality is available given any + :class:`_expression.FromClause` object (e.g. such as a + :class:`_schema.Table`) using + the :meth:`_expression.FromClause.join` method. + + :param left: The left side of the join. + + :param right: the right side of the join; this is any + :class:`_expression.FromClause` object such as a + :class:`_schema.Table` object, and + may also be a selectable-compatible object such as an ORM-mapped + class. + + :param onclause: a SQL expression representing the ON clause of the + join. If left at ``None``, :meth:`_expression.FromClause.join` + will attempt to + join the two tables based on a foreign key relationship. + + :param isouter: if True, render a LEFT OUTER JOIN, instead of JOIN. + + :param full: if True, render a FULL OUTER JOIN, instead of JOIN. + + .. versionadded:: 1.1 + + .. seealso:: + + :meth:`_expression.FromClause.join` - method form, + based on a given left side. + + :class:`_expression.Join` - the type of object produced. + + """ + + return Join(left, right, onclause, isouter, full) + + +def lateral(selectable, name=None): + """Return a :class:`_expression.Lateral` object. + + :class:`_expression.Lateral` is an :class:`_expression.Alias` + subclass that represents + a subquery with the LATERAL keyword applied to it. + + The special behavior of a LATERAL subquery is that it appears in the + FROM clause of an enclosing SELECT, but may correlate to other + FROM clauses of that SELECT. It is a special case of subquery + only supported by a small number of backends, currently more recent + PostgreSQL versions. + + .. versionadded:: 1.1 + + .. seealso:: + + :ref:`lateral_selects` - overview of usage. + + """ + return Lateral._factory(selectable, name=name) + + +def outerjoin(left, right, onclause=None, full=False): + """Return an ``OUTER JOIN`` clause element. + + The returned object is an instance of :class:`_expression.Join`. + + Similar functionality is also available via the + :meth:`_expression.FromClause.outerjoin` method on any + :class:`_expression.FromClause`. + + :param left: The left side of the join. + + :param right: The right side of the join. + + :param onclause: Optional criterion for the ``ON`` clause, is + derived from foreign key relationships established between + left and right otherwise. + + To chain joins together, use the :meth:`_expression.FromClause.join` + or + :meth:`_expression.FromClause.outerjoin` methods on the resulting + :class:`_expression.Join` object. + + """ + return Join(left, right, onclause, isouter=True, full=full) + + +def select(*entities: Union[roles.ColumnsClauseRole, Type]) -> "Select": + r"""Construct a new :class:`_expression.Select`. + + + .. versionadded:: 1.4 - The :func:`_sql.select` function now accepts + column arguments positionally. The top-level :func:`_sql.select` + function will automatically use the 1.x or 2.x style API based on + the incoming arguments; using :func:`_future.select` from the + ``sqlalchemy.future`` module will enforce that only the 2.x style + constructor is used. + + Similar functionality is also available via the + :meth:`_expression.FromClause.select` method on any + :class:`_expression.FromClause`. + + .. seealso:: + + :ref:`coretutorial_selecting` - Core Tutorial description of + :func:`_expression.select`. + + :param \*entities: + Entities to SELECT from. For Core usage, this is typically a series + of :class:`_expression.ColumnElement` and / or + :class:`_expression.FromClause` + objects which will form the columns clause of the resulting + statement. For those objects that are instances of + :class:`_expression.FromClause` (typically :class:`_schema.Table` + or :class:`_expression.Alias` + objects), the :attr:`_expression.FromClause.c` + collection is extracted + to form a collection of :class:`_expression.ColumnElement` objects. + + This parameter will also accept :class:`_expression.TextClause` + constructs as + given, as well as ORM-mapped classes. + + """ + + return Select(*entities) + + +def table(name: str, *columns: ColumnClause, **kw: Any) -> "TableClause": + """Produce a new :class:`_expression.TableClause`. + + The object returned is an instance of + :class:`_expression.TableClause`, which + represents the "syntactical" portion of the schema-level + :class:`_schema.Table` object. + It may be used to construct lightweight table constructs. + + .. versionchanged:: 1.0.0 :func:`_expression.table` can now + be imported from the plain ``sqlalchemy`` namespace like any + other SQL element. + + + :param name: Name of the table. + + :param columns: A collection of :func:`_expression.column` constructs. + + :param schema: The schema name for this table. + + .. versionadded:: 1.3.18 :func:`_expression.table` can now + accept a ``schema`` argument. + """ + + return TableClause(name, *columns, **kw) + + +def tablesample(selectable, sampling, name=None, seed=None): + """Return a :class:`_expression.TableSample` object. + + :class:`_expression.TableSample` is an :class:`_expression.Alias` + subclass that represents + a table with the TABLESAMPLE clause applied to it. + :func:`_expression.tablesample` + is also available from the :class:`_expression.FromClause` + class via the + :meth:`_expression.FromClause.tablesample` method. + + The TABLESAMPLE clause allows selecting a randomly selected approximate + percentage of rows from a table. It supports multiple sampling methods, + most commonly BERNOULLI and SYSTEM. + + e.g.:: + + from sqlalchemy import func + + selectable = people.tablesample( + func.bernoulli(1), + name='alias', + seed=func.random()) + stmt = select(selectable.c.people_id) + + Assuming ``people`` with a column ``people_id``, the above + statement would render as:: + + SELECT alias.people_id FROM + people AS alias TABLESAMPLE bernoulli(:bernoulli_1) + REPEATABLE (random()) + + .. versionadded:: 1.1 + + :param sampling: a ``float`` percentage between 0 and 100 or + :class:`_functions.Function`. + + :param name: optional alias name + + :param seed: any real-valued SQL expression. When specified, the + REPEATABLE sub-clause is also rendered. + + """ + return TableSample._factory(selectable, sampling, name=name, seed=seed) + + +def union(*selects, **kwargs): + r"""Return a ``UNION`` of multiple selectables. + + The returned object is an instance of + :class:`_expression.CompoundSelect`. + + A similar :func:`union()` method is available on all + :class:`_expression.FromClause` subclasses. + + :param \*selects: + a list of :class:`_expression.Select` instances. + + :param \**kwargs: + available keyword arguments are the same as those of + :func:`select`. + + """ + return CompoundSelect._create_union(*selects, **kwargs) + + +def union_all(*selects): + r"""Return a ``UNION ALL`` of multiple selectables. + + The returned object is an instance of + :class:`_expression.CompoundSelect`. + + A similar :func:`union_all()` method is available on all + :class:`_expression.FromClause` subclasses. + + :param \*selects: + a list of :class:`_expression.Select` instances. + + """ + return CompoundSelect._create_union_all(*selects) + + +def values(*columns, name=None, literal_binds=False) -> "Values": + r"""Construct a :class:`_expression.Values` construct. + + The column expressions and the actual data for + :class:`_expression.Values` are given in two separate steps. The + constructor receives the column expressions typically as + :func:`_expression.column` constructs, + and the data is then passed via the + :meth:`_expression.Values.data` method as a list, + which can be called multiple + times to add more data, e.g.:: + + from sqlalchemy import column + from sqlalchemy import values + + value_expr = values( + column('id', Integer), + column('name', String), + name="my_values" + ).data( + [(1, 'name1'), (2, 'name2'), (3, 'name3')] + ) + + :param \*columns: column expressions, typically composed using + :func:`_expression.column` objects. + + :param name: the name for this VALUES construct. If omitted, the + VALUES construct will be unnamed in a SQL expression. Different + backends may have different requirements here. + + :param literal_binds: Defaults to False. Whether or not to render + the data values inline in the SQL output, rather than using bound + parameters. + + """ + return Values(*columns, literal_binds=literal_binds, name=name) diff --git a/lib/sqlalchemy/sql/coercions.py b/lib/sqlalchemy/sql/coercions.py index fe2b498c8..3bec73f7d 100644 --- a/lib/sqlalchemy/sql/coercions.py +++ b/lib/sqlalchemy/sql/coercions.py @@ -8,6 +8,14 @@ import collections.abc as collections_abc import numbers import re +import typing +from typing import Any +from typing import Callable +from typing import Optional +from typing import overload +from typing import Type +from typing import TypeVar +from typing import Union from . import operators from . import roles @@ -20,13 +28,24 @@ from .. import exc from .. import inspection from .. import util +if not typing.TYPE_CHECKING: + elements = None + lambdas = None + schema = None + selectable = None + traversals = None -elements = None -lambdas = None -schema = None -selectable = None -sqltypes = None -traversals = None +if typing.TYPE_CHECKING: + from . import elements + from . import lambdas + from . import schema + from . import selectable + from . import traversals + from .elements import ClauseElement + from .elements import ColumnElement + +_SR = TypeVar("_SR", bound=roles.SQLRole) +_StringOnlyR = TypeVar("_StringOnlyR", bound=roles.StringRole) def _is_literal(element): @@ -110,14 +129,93 @@ def _expression_collection_was_a_list(attrname, fnname, args): return args +@overload def expect( - role, - element, - apply_propagate_attrs=None, - argname=None, - post_inspect=False, - **kw, -): + role: Type[roles.InElementRole], + element: Any, + *, + apply_propagate_attrs: Optional["ClauseElement"] = None, + argname: Optional[str] = None, + post_inspect: bool = False, + **kw: Any, +) -> Union["elements.ColumnElement", "selectable.Select"]: + ... + + +@overload +def expect( + role: Type[roles.HasCTERole], + element: Any, + *, + apply_propagate_attrs: Optional["ClauseElement"] = None, + argname: Optional[str] = None, + post_inspect: bool = False, + **kw: Any, +) -> "selectable.HasCTE": + ... + + +@overload +def expect( + role: Type[roles.ExpressionElementRole], + element: Any, + *, + apply_propagate_attrs: Optional["ClauseElement"] = None, + argname: Optional[str] = None, + post_inspect: bool = False, + **kw: Any, +) -> "ColumnElement": + ... + + +@overload +def expect( + role: "Type[_StringOnlyR]", + element: Any, + *, + apply_propagate_attrs: Optional["ClauseElement"] = None, + argname: Optional[str] = None, + post_inspect: bool = False, + **kw: Any, +) -> str: + ... + + +@overload +def expect( + role: Type[_SR], + element: Any, + *, + apply_propagate_attrs: Optional["ClauseElement"] = None, + argname: Optional[str] = None, + post_inspect: bool = False, + **kw: Any, +) -> _SR: + ... + + +@overload +def expect( + role: Type[_SR], + element: Callable[..., Any], + *, + apply_propagate_attrs: Optional["ClauseElement"] = None, + argname: Optional[str] = None, + post_inspect: bool = False, + **kw: Any, +) -> "lambdas.LambdaElement": + ... + + +def expect( + role: Type[_SR], + element: Any, + *, + apply_propagate_attrs: Optional["ClauseElement"] = None, + argname: Optional[str] = None, + post_inspect: bool = False, + **kw: Any, +) -> Union[str, _SR, "lambdas.LambdaElement"]: if ( role.allows_lambda # note callable() will not invoke a __getattr__() method, whereas diff --git a/lib/sqlalchemy/sql/default_comparator.py b/lib/sqlalchemy/sql/default_comparator.py index 55a586285..1759e686e 100644 --- a/lib/sqlalchemy/sql/default_comparator.py +++ b/lib/sqlalchemy/sql/default_comparator.py @@ -8,6 +8,15 @@ """Default implementation of SQL comparison operations. """ +import typing +from typing import Any +from typing import Callable +from typing import Dict +from typing import NoReturn +from typing import Optional +from typing import Tuple +from typing import Type +from typing import Union from . import coercions from . import operators @@ -16,28 +25,38 @@ from . import type_api from .elements import and_ from .elements import BinaryExpression from .elements import ClauseList -from .elements import collate +from .elements import CollationClause from .elements import CollectionAggregate from .elements import False_ from .elements import Null from .elements import or_ from .elements import True_ from .elements import UnaryExpression +from .operators import OperatorType from .. import exc from .. import util +_T = typing.TypeVar("_T", bound=Any) + +if typing.TYPE_CHECKING: + from .elements import ColumnElement + from .sqltypes import TypeEngine + def _boolean_compare( - expr, - op, - obj, - negate=None, - reverse=False, + expr: "ColumnElement", + op: OperatorType, + obj: roles.BinaryElementRole, + *, + negate_op: Optional[OperatorType] = None, + reverse: bool = False, _python_is_types=(util.NoneType, bool), _any_all_expr=False, - result_type=None, - **kwargs, -): + result_type: Optional[ + Union[Type["TypeEngine[bool]"], "TypeEngine[bool]"] + ] = None, + **kwargs: Any, +) -> BinaryExpression[bool]: if result_type is None: result_type = type_api.BOOLEANTYPE @@ -54,7 +73,7 @@ def _boolean_compare( coercions.expect(roles.ConstExprRole, obj), op, type_=result_type, - negate=negate, + negate=negate_op, modifiers=kwargs, ) elif op in ( @@ -66,7 +85,7 @@ def _boolean_compare( coercions.expect(roles.ConstExprRole, obj), op, type_=result_type, - negate=negate, + negate=negate_op, modifiers=kwargs, ) elif _any_all_expr: @@ -104,11 +123,21 @@ def _boolean_compare( if reverse: return BinaryExpression( - obj, expr, op, type_=result_type, negate=negate, modifiers=kwargs + obj, + expr, + op, + type_=result_type, + negate=negate_op, + modifiers=kwargs, ) else: return BinaryExpression( - expr, obj, op, type_=result_type, negate=negate, modifiers=kwargs + expr, + obj, + op, + type_=result_type, + negate=negate_op, + modifiers=kwargs, ) @@ -124,15 +153,26 @@ def _custom_op_operate(expr, op, obj, reverse=False, result_type=None, **kw): ) -def _binary_operate(expr, op, obj, reverse=False, result_type=None, **kw): - obj = coercions.expect( +def _binary_operate( + expr: "ColumnElement", + op: OperatorType, + obj: roles.BinaryElementRole, + *, + reverse=False, + result_type: Optional[ + Union[Type["TypeEngine[_T]"], "TypeEngine[_T]"] + ] = None, + **kw: Any, +) -> BinaryExpression[_T]: + + coerced_obj = coercions.expect( roles.BinaryElementRole, obj, expr=expr, operator=op ) if reverse: - left, right = obj, expr + left, right = coerced_obj, expr else: - left, right = expr, obj + left, right = expr, coerced_obj if result_type is None: op, result_type = left.comparator._adapt_expression( @@ -142,7 +182,7 @@ def _binary_operate(expr, op, obj, reverse=False, result_type=None, **kw): return BinaryExpression(left, right, op, type_=result_type, modifiers=kw) -def _conjunction_operate(expr, op, other, **kw): +def _conjunction_operate(expr, op, other, **kw) -> "ColumnElement": if op is operators.and_: return and_(expr, other) elif op is operators.or_: @@ -151,11 +191,11 @@ def _conjunction_operate(expr, op, other, **kw): raise NotImplementedError() -def _scalar(expr, op, fn, **kw): +def _scalar(expr, op, fn, **kw) -> "ColumnElement": return fn(expr) -def _in_impl(expr, op, seq_or_selectable, negate_op, **kw): +def _in_impl(expr, op, seq_or_selectable, negate_op, **kw) -> "ColumnElement": seq_or_selectable = coercions.expect( roles.InElementRole, seq_or_selectable, expr=expr, operator=op ) @@ -163,11 +203,11 @@ def _in_impl(expr, op, seq_or_selectable, negate_op, **kw): op, negate_op = seq_or_selectable._annotations["in_ops"] return _boolean_compare( - expr, op, seq_or_selectable, negate=negate_op, **kw + expr, op, seq_or_selectable, negate_op=negate_op, **kw ) -def _getitem_impl(expr, op, other, **kw): +def _getitem_impl(expr, op, other, **kw) -> "ColumnElement": if isinstance(expr.type, type_api.INDEXABLE): other = coercions.expect( roles.BinaryElementRole, other, expr=expr, operator=op @@ -177,13 +217,13 @@ def _getitem_impl(expr, op, other, **kw): _unsupported_impl(expr, op, other, **kw) -def _unsupported_impl(expr, op, *arg, **kw): +def _unsupported_impl(expr, op, *arg, **kw) -> NoReturn: raise NotImplementedError( "Operator '%s' is not supported on " "this expression" % op.__name__ ) -def _inv_impl(expr, op, **kw): +def _inv_impl(expr, op, **kw) -> "ColumnElement": """See :meth:`.ColumnOperators.__inv__`.""" # undocumented element currently used by the ORM for @@ -194,12 +234,12 @@ def _inv_impl(expr, op, **kw): return expr._negate() -def _neg_impl(expr, op, **kw): +def _neg_impl(expr, op, **kw) -> "ColumnElement": """See :meth:`.ColumnOperators.__neg__`.""" return UnaryExpression(expr, operator=operators.neg, type_=expr.type) -def _match_impl(expr, op, other, **kw): +def _match_impl(expr, op, other, **kw) -> "ColumnElement": """See :meth:`.ColumnOperators.match`.""" return _boolean_compare( @@ -212,21 +252,21 @@ def _match_impl(expr, op, other, **kw): operator=operators.match_op, ), result_type=type_api.MATCHTYPE, - negate=operators.not_match_op + negate_op=operators.not_match_op if op is operators.match_op else operators.match_op, **kw, ) -def _distinct_impl(expr, op, **kw): +def _distinct_impl(expr, op, **kw) -> "ColumnElement": """See :meth:`.ColumnOperators.distinct`.""" return UnaryExpression( expr, operator=operators.distinct_op, type_=expr.type ) -def _between_impl(expr, op, cleft, cright, **kw): +def _between_impl(expr, op, cleft, cright, **kw) -> "ColumnElement": """See :meth:`.ColumnOperators.between`.""" return BinaryExpression( expr, @@ -255,11 +295,11 @@ def _between_impl(expr, op, cleft, cright, **kw): ) -def _collate_impl(expr, op, other, **kw): - return collate(expr, other) +def _collate_impl(expr, op, collation, **kw) -> "ColumnElement": + return CollationClause._create_collation_expression(expr, collation) -def _regexp_match_impl(expr, op, pattern, flags, **kw): +def _regexp_match_impl(expr, op, pattern, flags, **kw) -> "ColumnElement": if flags is not None: flags = coercions.expect( roles.BinaryElementRole, @@ -272,14 +312,16 @@ def _regexp_match_impl(expr, op, pattern, flags, **kw): op, pattern, flags=flags, - negate=operators.not_regexp_match_op + negate_op=operators.not_regexp_match_op if op is operators.regexp_match_op else operators.regexp_match_op, **kw, ) -def _regexp_replace_impl(expr, op, pattern, replacement, flags, **kw): +def _regexp_replace_impl( + expr, op, pattern, replacement, flags, **kw +) -> "ColumnElement": replacement = coercions.expect( roles.BinaryElementRole, replacement, @@ -299,59 +341,118 @@ def _regexp_replace_impl(expr, op, pattern, replacement, flags, **kw): # a mapping of operators with the method they use, along with -# their negated operator for comparison operators -operator_lookup = { - "and_": (_conjunction_operate,), - "or_": (_conjunction_operate,), - "inv": (_inv_impl,), - "add": (_binary_operate,), - "mul": (_binary_operate,), - "sub": (_binary_operate,), - "div": (_binary_operate,), - "mod": (_binary_operate,), - "truediv": (_binary_operate,), - "floordiv": (_binary_operate,), - "custom_op": (_custom_op_operate,), - "json_path_getitem_op": (_binary_operate,), - "json_getitem_op": (_binary_operate,), - "concat_op": (_binary_operate,), - "any_op": (_scalar, CollectionAggregate._create_any), - "all_op": (_scalar, CollectionAggregate._create_all), - "lt": (_boolean_compare, operators.ge), - "le": (_boolean_compare, operators.gt), - "ne": (_boolean_compare, operators.eq), - "gt": (_boolean_compare, operators.le), - "ge": (_boolean_compare, operators.lt), - "eq": (_boolean_compare, operators.ne), - "is_distinct_from": (_boolean_compare, operators.is_not_distinct_from), - "is_not_distinct_from": (_boolean_compare, operators.is_distinct_from), - "like_op": (_boolean_compare, operators.not_like_op), - "ilike_op": (_boolean_compare, operators.not_ilike_op), - "not_like_op": (_boolean_compare, operators.like_op), - "not_ilike_op": (_boolean_compare, operators.ilike_op), - "contains_op": (_boolean_compare, operators.not_contains_op), - "startswith_op": (_boolean_compare, operators.not_startswith_op), - "endswith_op": (_boolean_compare, operators.not_endswith_op), - "desc_op": (_scalar, UnaryExpression._create_desc), - "asc_op": (_scalar, UnaryExpression._create_asc), - "nulls_first_op": (_scalar, UnaryExpression._create_nulls_first), - "nulls_last_op": (_scalar, UnaryExpression._create_nulls_last), - "in_op": (_in_impl, operators.not_in_op), - "not_in_op": (_in_impl, operators.in_op), - "is_": (_boolean_compare, operators.is_), - "is_not": (_boolean_compare, operators.is_not), - "collate": (_collate_impl,), - "match_op": (_match_impl,), - "not_match_op": (_match_impl,), - "distinct_op": (_distinct_impl,), - "between_op": (_between_impl,), - "not_between_op": (_between_impl,), - "neg": (_neg_impl,), - "getitem": (_getitem_impl,), - "lshift": (_unsupported_impl,), - "rshift": (_unsupported_impl,), - "contains": (_unsupported_impl,), - "regexp_match_op": (_regexp_match_impl,), - "not_regexp_match_op": (_regexp_match_impl,), - "regexp_replace_op": (_regexp_replace_impl,), +# additional keyword arguments to be passed +operator_lookup: Dict[ + str, Tuple[Callable[..., "ColumnElement"], util.immutabledict] +] = { + "and_": (_conjunction_operate, util.EMPTY_DICT), + "or_": (_conjunction_operate, util.EMPTY_DICT), + "inv": (_inv_impl, util.EMPTY_DICT), + "add": (_binary_operate, util.EMPTY_DICT), + "mul": (_binary_operate, util.EMPTY_DICT), + "sub": (_binary_operate, util.EMPTY_DICT), + "div": (_binary_operate, util.EMPTY_DICT), + "mod": (_binary_operate, util.EMPTY_DICT), + "truediv": (_binary_operate, util.EMPTY_DICT), + "floordiv": (_binary_operate, util.EMPTY_DICT), + "custom_op": (_custom_op_operate, util.EMPTY_DICT), + "json_path_getitem_op": (_binary_operate, util.EMPTY_DICT), + "json_getitem_op": (_binary_operate, util.EMPTY_DICT), + "concat_op": (_binary_operate, util.EMPTY_DICT), + "any_op": ( + _scalar, + util.immutabledict({"fn": CollectionAggregate._create_any}), + ), + "all_op": ( + _scalar, + util.immutabledict({"fn": CollectionAggregate._create_all}), + ), + "lt": (_boolean_compare, util.immutabledict({"negate_op": operators.ge})), + "le": (_boolean_compare, util.immutabledict({"negate_op": operators.gt})), + "ne": (_boolean_compare, util.immutabledict({"negate_op": operators.eq})), + "gt": (_boolean_compare, util.immutabledict({"negate_op": operators.le})), + "ge": (_boolean_compare, util.immutabledict({"negate_op": operators.lt})), + "eq": (_boolean_compare, util.immutabledict({"negate_op": operators.ne})), + "is_distinct_from": ( + _boolean_compare, + util.immutabledict({"negate_op": operators.is_not_distinct_from}), + ), + "is_not_distinct_from": ( + _boolean_compare, + util.immutabledict({"negate_op": operators.is_distinct_from}), + ), + "like_op": ( + _boolean_compare, + util.immutabledict({"negate_op": operators.not_like_op}), + ), + "ilike_op": ( + _boolean_compare, + util.immutabledict({"negate_op": operators.not_ilike_op}), + ), + "not_like_op": ( + _boolean_compare, + util.immutabledict({"negate_op": operators.like_op}), + ), + "not_ilike_op": ( + _boolean_compare, + util.immutabledict({"negate_op": operators.ilike_op}), + ), + "contains_op": ( + _boolean_compare, + util.immutabledict({"negate_op": operators.not_contains_op}), + ), + "startswith_op": ( + _boolean_compare, + util.immutabledict({"negate_op": operators.not_startswith_op}), + ), + "endswith_op": ( + _boolean_compare, + util.immutabledict({"negate_op": operators.not_endswith_op}), + ), + "desc_op": ( + _scalar, + util.immutabledict({"fn": UnaryExpression._create_desc}), + ), + "asc_op": ( + _scalar, + util.immutabledict({"fn": UnaryExpression._create_asc}), + ), + "nulls_first_op": ( + _scalar, + util.immutabledict({"fn": UnaryExpression._create_nulls_first}), + ), + "nulls_last_op": ( + _scalar, + util.immutabledict({"fn": UnaryExpression._create_nulls_last}), + ), + "in_op": ( + _in_impl, + util.immutabledict({"negate_op": operators.not_in_op}), + ), + "not_in_op": ( + _in_impl, + util.immutabledict({"negate_op": operators.in_op}), + ), + "is_": ( + _boolean_compare, + util.immutabledict({"negate_op": operators.is_}), + ), + "is_not": ( + _boolean_compare, + util.immutabledict({"negate_op": operators.is_not}), + ), + "collate": (_collate_impl, util.EMPTY_DICT), + "match_op": (_match_impl, util.EMPTY_DICT), + "not_match_op": (_match_impl, util.EMPTY_DICT), + "distinct_op": (_distinct_impl, util.EMPTY_DICT), + "between_op": (_between_impl, util.EMPTY_DICT), + "not_between_op": (_between_impl, util.EMPTY_DICT), + "neg": (_neg_impl, util.EMPTY_DICT), + "getitem": (_getitem_impl, util.EMPTY_DICT), + "lshift": (_unsupported_impl, util.EMPTY_DICT), + "rshift": (_unsupported_impl, util.EMPTY_DICT), + "contains": (_unsupported_impl, util.EMPTY_DICT), + "regexp_match_op": (_regexp_match_impl, util.EMPTY_DICT), + "not_regexp_match_op": (_regexp_match_impl, util.EMPTY_DICT), + "regexp_replace_op": (_regexp_replace_impl, util.EMPTY_DICT), } diff --git a/lib/sqlalchemy/sql/dml.py b/lib/sqlalchemy/sql/dml.py index 5f2424466..33dca66cd 100644 --- a/lib/sqlalchemy/sql/dml.py +++ b/lib/sqlalchemy/sql/dml.py @@ -763,76 +763,7 @@ class Insert(ValuesBase): + HasCTE._has_ctes_traverse_internals ) - def __init__( - self, - table, - ): - """Construct an :class:`_expression.Insert` object. - - E.g.:: - - from sqlalchemy import insert - - stmt = ( - insert(user_table). - values(name='username', fullname='Full Username') - ) - - Similar functionality is available via the - :meth:`_expression.TableClause.insert` method on - :class:`_schema.Table`. - - .. seealso:: - - :ref:`coretutorial_insert_expressions` - in the - :ref:`1.x tutorial <sqlexpression_toplevel>` - - :ref:`tutorial_core_insert` - in the :ref:`unified_tutorial` - - - :param table: :class:`_expression.TableClause` - which is the subject of the - insert. - - :param values: collection of values to be inserted; see - :meth:`_expression.Insert.values` - for a description of allowed formats here. - Can be omitted entirely; a :class:`_expression.Insert` construct - will also dynamically render the VALUES clause at execution time - based on the parameters passed to :meth:`_engine.Connection.execute`. - - :param inline: if True, 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. - - If both :paramref:`_expression.Insert.values` and compile-time bind - parameters are present, the compile-time bind parameters override the - information specified within :paramref:`_expression.Insert.values` on a - per-key basis. - - The keys within :paramref:`_expression.Insert.values` can be either - :class:`~sqlalchemy.schema.Column` objects or their string - identifiers. 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 this - ``INSERT`` statement's table, the statement will be correlated - against the ``INSERT`` statement. - - .. seealso:: - - :ref:`coretutorial_insert_expressions` - SQL Expression Tutorial - - :ref:`inserts_and_updates` - SQL Expression Tutorial - - """ + def __init__(self, table): super(Insert, self).__init__(table) @_generative @@ -1045,118 +976,7 @@ class Update(DMLWhereBase, ValuesBase): + HasCTE._has_ctes_traverse_internals ) - def __init__( - self, - table, - ): - r"""Construct an :class:`_expression.Update` object. - - E.g.:: - - from sqlalchemy import update - - stmt = ( - update(user_table). - where(user_table.c.id == 5). - values(name='user #5') - ) - - Similar functionality is available via the - :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. - - :param whereclause: Optional SQL expression describing the ``WHERE`` - condition of the ``UPDATE`` statement; is equivalent to using the - more modern :meth:`~Update.where()` method to specify the ``WHERE`` - clause. - - :param values: - Optional dictionary which specifies the ``SET`` conditions of the - ``UPDATE``. If left as ``None``, the ``SET`` - conditions are determined from those parameters passed to the - statement during the execution and/or compilation of the - statement. When compiled standalone without any parameters, - the ``SET`` clause generates for all columns. - - Modern applications may prefer to use the generative - :meth:`_expression.Update.values` method to set the values of the - UPDATE statement. - - :param inline: - if True, SQL defaults present on :class:`_schema.Column` objects via - the ``default`` keyword will be compiled 'inline' into the statement - and not pre-executed. This means that their values will not - be available in the dictionary returned from - :meth:`_engine.CursorResult.last_updated_params`. - - :param preserve_parameter_order: if True, the update statement is - expected to receive parameters **only** via the - :meth:`_expression.Update.values` method, - and they must be passed as a Python - ``list`` of 2-tuples. The rendered UPDATE statement will emit the SET - clause for each referenced column maintaining this order. - - .. versionadded:: 1.0.10 - - .. seealso:: - - :ref:`updates_order_parameters` - illustrates the - :meth:`_expression.Update.ordered_values` method. - - If both ``values`` and compile-time bind parameters are present, the - compile-time bind parameters override the information specified - within ``values`` on a per-key basis. - - The keys within ``values`` can be either :class:`_schema.Column` - objects or their string identifiers (specifically the "key" of the - :class:`_schema.Column`, normally but not necessarily equivalent to - its "name"). Normally, the - :class:`_schema.Column` objects used here are expected to be - part of the target :class:`_schema.Table` that is the table - to be updated. However when using MySQL, a multiple-table - UPDATE statement can refer to columns from any of - the 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 :class:`_schema.Column`, - a scalar-returning :func:`_expression.select` construct, - etc. - - When combining :func:`_expression.select` constructs within the - values clause of an :func:`_expression.update` - construct, the subquery represented - by the :func:`_expression.select` should be *correlated* to the - parent table, that is, providing criterion which links the table inside - the subquery to the outer table being updated:: - - users.update().values( - name=select(addresses.c.email_address).\ - where(addresses.c.user_id==users.c.id).\ - scalar_subquery() - ) - - .. seealso:: - - :ref:`inserts_and_updates` - SQL Expression - Language Tutorial - - - """ + def __init__(self, table): super(Update, self).__init__(table) @_generative @@ -1244,45 +1064,7 @@ class Delete(DMLWhereBase, UpdateBase): + HasCTE._has_ctes_traverse_internals ) - def __init__( - self, - table, - ): - r"""Construct :class:`_expression.Delete` object. - - E.g.:: - - from sqlalchemy import delete - - stmt = ( - delete(user_table). - where(user_table.c.id == 5) - ) - - Similar functionality is available via the - :meth:`_expression.TableClause.delete` 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: The table to delete rows from. - - :param whereclause: Optional SQL expression describing the ``WHERE`` - condition of the ``DELETE`` statement; is equivalent to using the - more modern :meth:`~Delete.where()` method to specify the ``WHERE`` - clause. - - .. seealso:: - - :ref:`deletes` - SQL Expression Tutorial - - """ + def __init__(self, table): self.table = coercions.expect( roles.DMLTableRole, table, apply_propagate_attrs=self ) diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py index a025cce35..705a89889 100644 --- a/lib/sqlalchemy/sql/elements.py +++ b/lib/sqlalchemy/sql/elements.py @@ -14,6 +14,16 @@ import itertools import operator import re import typing +from typing import Any +from typing import Callable +from typing import Generic +from typing import Optional +from typing import overload +from typing import Sequence +from typing import Text as typing_Text +from typing import Type +from typing import TypeVar +from typing import Union from . import coercions from . import operators @@ -31,7 +41,7 @@ from .base import NO_ARG from .base import SingletonConstant from .cache_key import MemoizedHasCacheKey from .cache_key import NO_CACHE -from .coercions import _document_text_coercion +from .coercions import _document_text_coercion # noqa from .traversals import HasCopyInternals from .visitors import cloned_traverse from .visitors import InternalTraversal @@ -41,86 +51,20 @@ from .. import exc from .. import inspection from .. import util +if typing.TYPE_CHECKING: + from decimal import Decimal -def collate(expression, collation): - """Return the clause ``expression COLLATE collation``. + from .selectable import FromClause + from .selectable import Select + from .sqltypes import Boolean # noqa + from .type_api import TypeEngine - e.g.:: +_NUMERIC = Union[complex, "Decimal"] - collate(mycolumn, 'utf8_bin') - - produces:: - - mycolumn COLLATE utf8_bin - - The collation expression is also quoted if it is a case sensitive - identifier, e.g. contains uppercase characters. - - .. versionchanged:: 1.2 quoting is automatically applied to COLLATE - expressions if they are case sensitive. - - """ - - expr = coercions.expect(roles.ExpressionElementRole, expression) - return BinaryExpression( - expr, CollationClause(collation), operators.collate, type_=expr.type - ) - - -def between(expr, lower_bound, upper_bound, symmetric=False): - """Produce a ``BETWEEN`` predicate clause. - - E.g.:: - - from sqlalchemy import between - stmt = select(users_table).where(between(users_table.c.id, 5, 7)) - - Would produce SQL resembling:: - - SELECT id, name FROM user WHERE id BETWEEN :id_1 AND :id_2 - - The :func:`.between` function is a standalone version of the - :meth:`_expression.ColumnElement.between` method available on all - SQL expressions, as in:: - - stmt = select(users_table).where(users_table.c.id.between(5, 7)) - - All arguments passed to :func:`.between`, including the left side - column expression, are coerced from Python scalar values if a - the value is not a :class:`_expression.ColumnElement` subclass. - For example, - three fixed values can be compared as in:: - - print(between(5, 3, 7)) - - Which would produce:: - - :param_1 BETWEEN :param_2 AND :param_3 - - :param expr: a column expression, typically a - :class:`_expression.ColumnElement` - instance or alternatively a Python scalar expression to be coerced - into a column expression, serving as the left side of the ``BETWEEN`` - expression. - - :param lower_bound: a column or Python scalar expression serving as the - lower bound of the right side of the ``BETWEEN`` expression. - - :param upper_bound: a column or Python scalar expression serving as the - upper bound of the right side of the ``BETWEEN`` expression. - - :param symmetric: if True, will render " BETWEEN SYMMETRIC ". Note - that not all databases support this syntax. - - .. versionadded:: 0.9.5 - - .. seealso:: - - :meth:`_expression.ColumnElement.between` - - """ - expr = coercions.expect(roles.ExpressionElementRole, expr) - return expr.between(lower_bound, upper_bound, symmetric=symmetric) +_T = TypeVar("_T", bound="Any") +_OPT = TypeVar("_OPT", bound="Any") +_NT = TypeVar("_NT", bound="_NUMERIC") +_ST = TypeVar("_ST", bound="typing_Text") def literal(value, type_=None): @@ -145,28 +89,40 @@ def literal(value, type_=None): return coercions.expect(roles.LiteralValueRole, value, type_=type_) -def outparam(key, type_=None): - """Create an 'OUT' parameter for usage in functions (stored procedures), - for databases which support them. +def literal_column(text, type_=None): + r"""Produce a :class:`.ColumnClause` object that has the + :paramref:`_expression.column.is_literal` flag set to True. + + :func:`_expression.literal_column` is similar to + :func:`_expression.column`, except that + it is more often used as a "standalone" column expression that renders + exactly as stated; while :func:`_expression.column` + stores a string name that + will be assumed to be part of a table and may be quoted as such, + :func:`_expression.literal_column` can be that, + or any other arbitrary column-oriented + expression. + + :param text: the text of the expression; can be any SQL expression. + Quoting rules will not be applied. To specify a column-name expression + which should be subject to quoting rules, use the :func:`column` + function. - The ``outparam`` can be used like a regular function parameter. - The "output" value will be available from the - :class:`~sqlalchemy.engine.CursorResult` object via its ``out_parameters`` - attribute, which returns a dictionary containing the values. + :param type\_: an optional :class:`~sqlalchemy.types.TypeEngine` + object which will + provide result-set translation and additional expression semantics for + this column. If left as ``None`` the type will be :class:`.NullType`. - """ - return BindParameter(key, None, type_=type_, unique=False, isoutparam=True) + .. seealso:: + :func:`_expression.column` -def not_(clause): - """Return a negation of the given clause, i.e. ``NOT(clause)``. + :func:`_expression.text` - The ``~`` operator is also overloaded on all - :class:`_expression.ColumnElement` subclasses to produce the - same result. + :ref:`sqlexpression_literal_column` """ - return operators.inv(coercions.expect(roles.ExpressionElementRole, clause)) + return ColumnClause(text, type_=type_, is_literal=True) class CompilerElement(Traversible): @@ -258,6 +214,9 @@ class CompilerElement(Traversible): return str(self.compile()) +SelfClauseElement = TypeVar("SelfClauseElement", bound="ClauseElement") + + @inspection._self_inspects class ClauseElement( SupportsWrappingAnnotations, @@ -313,7 +272,7 @@ class ClauseElement( self._propagate_attrs = util.immutabledict(values) return self - def _clone(self, **kw): + def _clone(self: SelfClauseElement, **kw) -> SelfClauseElement: """Create a shallow copy of this ClauseElement. This method may be used by a generative API. Its also used as @@ -624,8 +583,9 @@ class ColumnElement( roles.DMLColumnRole, roles.DDLConstraintColumnRole, roles.DDLExpressionRole, - operators.ColumnOperators, + operators.ColumnOperators["ColumnElement"], ClauseElement, + Generic[_T], ): """Represent a column-oriented SQL expression suitable for usage in the "columns" clause, WHERE clause etc. of a statement. @@ -841,11 +801,11 @@ class ColumnElement( return super(ColumnElement, self)._negate() @util.memoized_property - def type(self): + def type(self) -> "TypeEngine[_T]": return type_api.NULLTYPE @HasMemoized.memoized_attribute - def comparator(self): + def comparator(self) -> "TypeEngine.Comparator[_T]": try: comparator_factory = self.type.comparator_factory except AttributeError as err: @@ -869,10 +829,347 @@ class ColumnElement( ) ) from err - def operate(self, op, *other, **kwargs): + # annotations for comparison methods + # these are from operators->Operators / ColumnOperators, + # redefined with the specific types returned by ColumnElement hierarchies + if typing.TYPE_CHECKING: + + def op( + self, + opstring: Any, + precedence: int = 0, + is_comparison: bool = False, + return_type: Optional[ + Union[Type["TypeEngine[_OPT]"], "TypeEngine[_OPT]"] + ] = None, + python_impl=None, + ) -> Callable[[Any], "BinaryExpression[_OPT]"]: + ... + + def bool_op( + self, opstring: Any, precedence: int = 0, python_impl=None + ) -> Callable[[Any], "BinaryExpression[bool]"]: + ... + + def __and__(self, other: Any) -> "BooleanClauseList": + ... + + def __or__(self, other: Any) -> "BooleanClauseList": + ... + + def __invert__(self) -> "UnaryExpression[_T]": + ... + + def __lt__(self, other: Any) -> "BinaryExpression[bool]": + ... + + def __le__(self, other: Any) -> "BinaryExpression[bool]": + ... + + def __eq__(self, other: Any) -> "BinaryExpression[bool]": + ... + + def __ne__(self, other: Any) -> "BinaryExpression[bool]": + ... + + def is_distinct_from(self, other: Any) -> "BinaryExpression[bool]": + ... + + def is_not_distinct_from(self, other: Any) -> "BinaryExpression[bool]": + ... + + def __gt__(self, other: Any) -> "BinaryExpression[bool]": + ... + + def __ge__(self, other: Any) -> "BinaryExpression[bool]": + ... + + def __neg__(self) -> "UnaryExpression[_T]": + ... + + def __contains__(self, other: Any) -> "BinaryExpression[bool]": + ... + + def __getitem__(self, index: Any) -> "ColumnElement": + ... + + @overload + def concat(self, other: Any) -> "BinaryExpression[_ST]": + ... + + @overload + def concat(self, other: Any) -> "BinaryExpression": + ... + + def concat(self, other: Any) -> "BinaryExpression": + ... + + def like(self, other: Any, escape=None) -> "BinaryExpression[bool]": + ... + + def ilike(self, other: Any, escape=None) -> "BinaryExpression[bool]": + ... + + def in_( + self, + other: Union[Sequence[Any], "BindParameter", "Select"], + ) -> "BinaryExpression[bool]": + ... + + def not_in( + self, + other: Union[Sequence[Any], "BindParameter", "Select"], + ) -> "BinaryExpression[bool]": + ... + + def not_like( + self, other: Any, escape=None + ) -> "BinaryExpression[bool]": + ... + + def not_ilike( + self, other: Any, escape=None + ) -> "BinaryExpression[bool]": + ... + + def is_(self, other: Any) -> "BinaryExpression[bool]": + ... + + def is_not(self, other: Any) -> "BinaryExpression[bool]": + ... + + def startswith( + self, other: Any, escape=None, autoescape=False + ) -> "BinaryExpression[bool]": + ... + + def endswith( + self, other: Any, escape=None, autoescape=False + ) -> "BinaryExpression[bool]": + ... + + def contains( + self, other: Any, escape=None, autoescape=False + ) -> "BinaryExpression[bool]": + ... + + def match(self, other: Any, **kwargs) -> "BinaryExpression[bool]": + ... + + def regexp_match( + self, pattern, flags=None + ) -> "BinaryExpression[bool]": + ... + + def regexp_replace( + self, pattern, replacement, flags=None + ) -> "BinaryExpression": + ... + + def desc(self) -> "UnaryExpression[_T]": + ... + + def asc(self) -> "UnaryExpression[_T]": + ... + + def nulls_first(self) -> "UnaryExpression[_T]": + ... + + def nulls_last(self) -> "UnaryExpression[_T]": + ... + + def collate(self, collation) -> "CollationClause": + ... + + def between( + self, cleft, cright, symmetric=False + ) -> "BinaryExpression[bool]": + ... + + def distinct(self: "ColumnElement[_T]") -> "UnaryExpression[_T]": + ... + + def any_(self) -> "CollectionAggregate": + ... + + def all_(self) -> "CollectionAggregate": + ... + + # numeric overloads. These need more tweaking + + @overload + def __add__( + self: "ColumnElement[_NT]", other: "Union[ColumnElement[_NT], _NT]" + ) -> "BinaryExpression[_NT]": + ... + + @overload + def __add__( + self: "ColumnElement[_NT]", other: Any + ) -> "BinaryExpression[_NUMERIC]": + ... + + @overload + def __add__( + self: "ColumnElement[_ST]", other: Any + ) -> "BinaryExpression[_ST]": + ... + + def __add__(self, other: Any) -> "BinaryExpression": + ... + + @overload + def __radd__(self, other: Any) -> "BinaryExpression[_NUMERIC]": + ... + + @overload + def __radd__(self, other: Any) -> "BinaryExpression": + ... + + def __radd__(self, other: Any) -> "BinaryExpression": + ... + + @overload + def __sub__( + self: "ColumnElement[_NT]", other: "Union[ColumnElement[_NT], _NT]" + ) -> "BinaryExpression[_NT]": + ... + + @overload + def __sub__(self, other: Any) -> "BinaryExpression": + ... + + def __sub__(self, other: Any) -> "BinaryExpression": + ... + + @overload + def __rsub__( + self: "ColumnElement[_NT]", other: Any + ) -> "BinaryExpression[_NUMERIC]": + ... + + @overload + def __rsub__(self, other: Any) -> "BinaryExpression": + ... + + def __rsub__(self, other: Any) -> "BinaryExpression": + ... + + @overload + def __mul__( + self: "ColumnElement[_NT]", other: Any + ) -> "BinaryExpression[_NUMERIC]": + ... + + @overload + def __mul__(self, other: Any) -> "BinaryExpression": + ... + + def __mul__(self, other: Any) -> "BinaryExpression": + ... + + @overload + def __rmul__( + self: "ColumnElement[_NT]", other: Any + ) -> "BinaryExpression[_NUMERIC]": + ... + + @overload + def __rmul__(self, other: Any) -> "BinaryExpression": + ... + + def __rmul__(self, other: Any) -> "BinaryExpression": + ... + + @overload + def __mod__( + self: "ColumnElement[_NT]", other: Any + ) -> "BinaryExpression[_NUMERIC]": + ... + + @overload + def __mod__(self, other: Any) -> "BinaryExpression": + ... + + def __mod__(self, other: Any) -> "BinaryExpression": + ... + + @overload + def __rmod__( + self: "ColumnElement[_NT]", other: Any + ) -> "BinaryExpression[_NUMERIC]": + ... + + @overload + def __rmod__(self, other: Any) -> "BinaryExpression": + ... + + def __rmod__(self, other: Any) -> "BinaryExpression": + ... + + @overload + def __truediv__( + self: "ColumnElement[_NT]", other: Any + ) -> "BinaryExpression[_NUMERIC]": + ... + + @overload + def __truediv__(self, other: Any) -> "BinaryExpression": + ... + + def __truediv__(self, other: Any) -> "BinaryExpression": + ... + + @overload + def __rtruediv__( + self: "ColumnElement[_NT]", other: Any + ) -> "BinaryExpression[_NUMERIC]": + ... + + @overload + def __rtruediv__(self, other: Any) -> "BinaryExpression": + ... + + def __rtruediv__(self, other: Any) -> "BinaryExpression": + ... + + @overload + def __floordiv__( + self: "ColumnElement[_NT]", other: Any + ) -> "BinaryExpression[_NUMERIC]": + ... + + @overload + def __floordiv__(self, other: Any) -> "BinaryExpression": + ... + + def __floordiv__(self, other: Any) -> "BinaryExpression": + ... + + @overload + def __rfloordiv__( + self: "ColumnElement[_NT]", other: Any + ) -> "BinaryExpression[_NUMERIC]": + ... + + @overload + def __rfloordiv__(self, other: Any) -> "BinaryExpression": + ... + + def __rfloordiv__(self, other: Any) -> "BinaryExpression": + ... + + def operate( + self, + op: operators.OperatorType, + *other: Any, + **kwargs, + ) -> "ColumnElement": return op(self.comparator, *other, **kwargs) - def reverse_operate(self, op, other, **kwargs): + def reverse_operate( + self, op: operators.OperatorType, other: Any, **kwargs + ) -> "ColumnElement": return op(other, self.comparator, **kwargs) def _bind_param(self, operator, obj, type_=None, expanding=False): @@ -975,7 +1272,12 @@ class ColumnElement( return None def _make_proxy( - self, selectable, name=None, key=None, name_is_truncatable=False, **kw + self, + selectable, + name: Optional[str] = None, + key=None, + name_is_truncatable=False, + **kw, ): """Create a new :class:`_expression.ColumnElement` representing this :class:`_expression.ColumnElement` as it appears in the select list of @@ -1031,7 +1333,7 @@ class ColumnElement( """ return Label(name, self, self.type) - def _anon_label(self, seed, add_hash=None): + def _anon_label(self, seed, add_hash=None) -> "_anonymous_label": while self._is_clone_of is not None: self = self._is_clone_of @@ -1066,7 +1368,7 @@ class ColumnElement( return _anonymous_label.safe_construct(hash_value, seed or "anon") @util.memoized_property - def _anon_name_label(self): + def _anon_name_label(self) -> "_anonymous_label": """Provides a constant 'anonymous label' for this ColumnElement. This is a label() expression which will be named at compile time. @@ -1214,7 +1516,10 @@ class WrapsColumnExpression: return self._dedupe_anon_tq_label_idx(idx) -class BindParameter(roles.InElementRole, ColumnElement): +SelfBindParameter = TypeVar("SelfBindParameter", bound="BindParameter") + + +class BindParameter(roles.InElementRole, ColumnElement[_T]): r"""Represent a "bound expression". :class:`.BindParameter` is invoked explicitly using the @@ -1267,238 +1572,6 @@ class BindParameter(roles.InElementRole, ColumnElement): _compared_to_type=None, _is_crud=False, ): - r"""Produce a "bound expression". - - The return value is an instance of :class:`.BindParameter`; this - is a :class:`_expression.ColumnElement` - subclass which represents a so-called - "placeholder" value in a SQL expression, the value of which is - supplied at the point at which the statement in executed against a - database connection. - - In SQLAlchemy, the :func:`.bindparam` construct has - the ability to carry along the actual value that will be ultimately - used at expression time. In this way, it serves not just as - a "placeholder" for eventual population, but also as a means of - representing so-called "unsafe" values which should not be rendered - directly in a SQL statement, but rather should be passed along - to the :term:`DBAPI` as values which need to be correctly escaped - and potentially handled for type-safety. - - When using :func:`.bindparam` explicitly, the use case is typically - one of traditional deferment of parameters; the :func:`.bindparam` - construct accepts a name which can then be referred to at execution - time:: - - from sqlalchemy import bindparam - - stmt = select(users_table).\ - where(users_table.c.name == bindparam('username')) - - The above statement, when rendered, will produce SQL similar to:: - - SELECT id, name FROM user WHERE name = :username - - In order to populate the value of ``:username`` above, the value - would typically be applied at execution time to a method - like :meth:`_engine.Connection.execute`:: - - result = connection.execute(stmt, username='wendy') - - Explicit use of :func:`.bindparam` is also common when producing - UPDATE or DELETE statements that are to be invoked multiple times, - where the WHERE criterion of the statement is to change on each - invocation, such as:: - - stmt = (users_table.update(). - where(user_table.c.name == bindparam('username')). - values(fullname=bindparam('fullname')) - ) - - connection.execute( - stmt, [{"username": "wendy", "fullname": "Wendy Smith"}, - {"username": "jack", "fullname": "Jack Jones"}, - ] - ) - - SQLAlchemy's Core expression system makes wide use of - :func:`.bindparam` in an implicit sense. It is typical that Python - literal values passed to virtually all SQL expression functions are - coerced into fixed :func:`.bindparam` constructs. For example, given - a comparison operation such as:: - - expr = users_table.c.name == 'Wendy' - - The above expression will produce a :class:`.BinaryExpression` - construct, where the left side is the :class:`_schema.Column` object - representing the ``name`` column, and the right side is a - :class:`.BindParameter` representing the literal value:: - - print(repr(expr.right)) - BindParameter('%(4327771088 name)s', 'Wendy', type_=String()) - - The expression above will render SQL such as:: - - user.name = :name_1 - - Where the ``:name_1`` parameter name is an anonymous name. The - actual string ``Wendy`` is not in the rendered string, but is carried - along where it is later used within statement execution. If we - invoke a statement like the following:: - - stmt = select(users_table).where(users_table.c.name == 'Wendy') - result = connection.execute(stmt) - - We would see SQL logging output as:: - - SELECT "user".id, "user".name - FROM "user" - WHERE "user".name = %(name_1)s - {'name_1': 'Wendy'} - - Above, we see that ``Wendy`` is passed as a parameter to the database, - while the placeholder ``:name_1`` is rendered in the appropriate form - for the target database, in this case the PostgreSQL database. - - Similarly, :func:`.bindparam` is invoked automatically when working - with :term:`CRUD` statements as far as the "VALUES" portion is - concerned. The :func:`_expression.insert` construct produces an - ``INSERT`` expression which will, at statement execution time, generate - bound placeholders based on the arguments passed, as in:: - - stmt = users_table.insert() - result = connection.execute(stmt, name='Wendy') - - The above will produce SQL output as:: - - INSERT INTO "user" (name) VALUES (%(name)s) - {'name': 'Wendy'} - - The :class:`_expression.Insert` construct, at - compilation/execution time, rendered a single :func:`.bindparam` - mirroring the column name ``name`` as a result of the single ``name`` - parameter we passed to the :meth:`_engine.Connection.execute` method. - - :param key: - the key (e.g. the name) for this bind param. - Will be used in the generated - SQL statement for dialects that use named parameters. This - value may be modified when part of a compilation operation, - if other :class:`BindParameter` objects exist with the same - key, or if its length is too long and truncation is - required. - - :param value: - Initial value for this bind param. Will be used at statement - execution time as the value for this parameter passed to the - DBAPI, if no other value is indicated to the statement execution - method for this particular parameter name. Defaults to ``None``. - - :param callable\_: - A callable function that takes the place of "value". The function - will be called at statement execution time to determine the - ultimate value. Used for scenarios where the actual bind - value cannot be determined at the point at which the clause - construct is created, but embedded bind values are still desirable. - - :param type\_: - A :class:`.TypeEngine` class or instance representing an optional - datatype for this :func:`.bindparam`. If not passed, a type - may be determined automatically for the bind, based on the given - value; for example, trivial Python types such as ``str``, - ``int``, ``bool`` - may result in the :class:`.String`, :class:`.Integer` or - :class:`.Boolean` types being automatically selected. - - The type of a :func:`.bindparam` is significant especially in that - the type will apply pre-processing to the value before it is - passed to the database. For example, a :func:`.bindparam` which - refers to a datetime value, and is specified as holding the - :class:`.DateTime` type, may apply conversion needed to the - value (such as stringification on SQLite) before passing the value - to the database. - - :param unique: - if True, the key name of this :class:`.BindParameter` will be - modified if another :class:`.BindParameter` of the same name - already has been located within the containing - expression. This flag is used generally by the internals - when producing so-called "anonymous" bound expressions, it - isn't generally applicable to explicitly-named :func:`.bindparam` - constructs. - - :param required: - If ``True``, a value is required at execution time. If not passed, - it defaults to ``True`` if neither :paramref:`.bindparam.value` - or :paramref:`.bindparam.callable` were passed. If either of these - parameters are present, then :paramref:`.bindparam.required` - defaults to ``False``. - - :param quote: - True if this parameter name requires quoting and is not - currently known as a SQLAlchemy reserved word; this currently - only applies to the Oracle backend, where bound names must - sometimes be quoted. - - :param isoutparam: - if True, the parameter should be treated like a stored procedure - "OUT" parameter. This applies to backends such as Oracle which - support OUT parameters. - - :param expanding: - if True, this parameter will be treated as an "expanding" parameter - at execution time; the parameter value is expected to be a sequence, - rather than a scalar value, and the string SQL statement will - be transformed on a per-execution basis to accommodate the sequence - with a variable number of parameter slots passed to the DBAPI. - This is to allow statement caching to be used in conjunction with - an IN clause. - - .. seealso:: - - :meth:`.ColumnOperators.in_` - - :ref:`baked_in` - with baked queries - - .. note:: The "expanding" feature does not support "executemany"- - style parameter sets. - - .. versionadded:: 1.2 - - .. 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 - render the final value of the parameter into the SQL statement at - statement execution time, omitting the value from the parameter - dictionary / list passed to DBAPI ``cursor.execute()``. This - produces a similar effect as that of using the ``literal_binds``, - compilation flag, however takes place as the statement is sent to - the DBAPI ``cursor.execute()`` method, rather than when the statement - is compiled. The primary use of this - capability is for rendering LIMIT / OFFSET clauses for database - drivers that can't accommodate for bound parameters in these - contexts, while allowing SQL constructs to be cacheable at the - compilation level. - - .. versionadded:: 1.4 Added "post compile" bound parameters - - .. seealso:: - - :ref:`change_4808`. - - """ if required is NO_ARG: required = value is NO_ARG and callable_ is None if value is NO_ARG: @@ -1641,7 +1714,9 @@ class BindParameter(roles.InElementRole, ColumnElement): c.type = type_ return c - def _clone(self, maintain_key=False, **kw): + def _clone( + self: SelfBindParameter, maintain_key=False, **kw + ) -> SelfBindParameter: c = ClauseElement._clone(self, **kw) if not maintain_key and self.unique: c.key = _anonymous_label.safe_construct( @@ -1799,85 +1874,6 @@ class TextClause( # to the list of bindparams self.text = self._bind_params_regex.sub(repl, text) - @classmethod - @_document_text_coercion("text", ":func:`.text`", ":paramref:`.text.text`") - def _create_text(cls, text): - r"""Construct a new :class:`_expression.TextClause` clause, - representing - a textual SQL string directly. - - E.g.:: - - from sqlalchemy import text - - t = text("SELECT * FROM users") - result = connection.execute(t) - - The advantages :func:`_expression.text` - provides over a plain string are - backend-neutral support for bind parameters, per-statement - execution options, as well as - bind parameter and result-column typing behavior, allowing - SQLAlchemy type constructs to play a role when executing - a statement that is specified literally. The construct can also - be provided with a ``.c`` collection of column elements, allowing - it to be embedded in other SQL expression constructs as a subquery. - - Bind parameters are specified by name, using the format ``:name``. - E.g.:: - - t = text("SELECT * FROM users WHERE id=:user_id") - result = connection.execute(t, user_id=12) - - For SQL statements where a colon is required verbatim, as within - an inline string, use a backslash to escape:: - - t = text("SELECT * FROM users WHERE name='\:username'") - - The :class:`_expression.TextClause` - construct includes methods which can - provide information about the bound parameters as well as the column - values which would be returned from the textual statement, assuming - it's an executable SELECT type of statement. The - :meth:`_expression.TextClause.bindparams` - method is used to provide bound - parameter detail, and :meth:`_expression.TextClause.columns` - method allows - specification of return columns including names and types:: - - t = text("SELECT * FROM users WHERE id=:user_id").\ - bindparams(user_id=7).\ - columns(id=Integer, name=String) - - for id, name in connection.execute(t): - print(id, name) - - The :func:`_expression.text` construct is used in cases when - a literal string SQL fragment is specified as part of a larger query, - such as for the WHERE clause of a SELECT statement:: - - s = select(users.c.id, users.c.name).where(text("id=:user_id")) - result = connection.execute(s, user_id=12) - - :func:`_expression.text` is also used for the construction - of a full, standalone statement using plain text. - As such, SQLAlchemy refers - to it as an :class:`.Executable` object and may be used - like any other statement passed to an ``.execute()`` method. - - :param text: - the text of the SQL statement to be created. Use ``:<param>`` - to specify bind parameters; they will be compiled to their - engine-specific format. - - .. seealso:: - - :ref:`sqlexpression_text` - in the Core tutorial - - - """ - return TextClause(text) - @_generative def bindparams( self: SelfTextClause, *binds, **names_to_values @@ -2204,40 +2200,6 @@ class False_(SingletonConstant, roles.ConstExprRole, ColumnElement): @classmethod def _instance(cls): - """Return a :class:`.False_` construct. - - E.g.:: - - >>> from sqlalchemy import false - >>> print(select(t.c.x).where(false())) - SELECT x FROM t WHERE false - - A backend which does not support true/false constants will render as - an expression against 1 or 0:: - - >>> print(select(t.c.x).where(false())) - SELECT x FROM t WHERE 0 = 1 - - The :func:`.true` and :func:`.false` constants also feature - "short circuit" operation within an :func:`.and_` or :func:`.or_` - conjunction:: - - >>> print(select(t.c.x).where(or_(t.c.x > 5, true()))) - SELECT x FROM t WHERE true - - >>> print(select(t.c.x).where(and_(t.c.x > 5, false()))) - SELECT x FROM t WHERE false - - .. versionchanged:: 0.9 :func:`.true` and :func:`.false` feature - better integrated behavior within conjunctions and on dialects - that don't support true/false constants. - - .. seealso:: - - :func:`.true` - - """ - return False_() @@ -2272,40 +2234,6 @@ class True_(SingletonConstant, roles.ConstExprRole, ColumnElement): @classmethod def _instance(cls): - """Return a constant :class:`.True_` construct. - - E.g.:: - - >>> from sqlalchemy import true - >>> print(select(t.c.x).where(true())) - SELECT x FROM t WHERE true - - A backend which does not support true/false constants will render as - an expression against 1 or 0:: - - >>> print(select(t.c.x).where(true())) - SELECT x FROM t WHERE 1 = 1 - - The :func:`.true` and :func:`.false` constants also feature - "short circuit" operation within an :func:`.and_` or :func:`.or_` - conjunction:: - - >>> print(select(t.c.x).where(or_(t.c.x > 5, true()))) - SELECT x FROM t WHERE true - - >>> print(select(t.c.x).where(and_(t.c.x > 5, false()))) - SELECT x FROM t WHERE false - - .. versionchanged:: 0.9 :func:`.true` and :func:`.false` feature - better integrated behavior within conjunctions and on dialects - that don't support true/false constants. - - .. seealso:: - - :func:`.false` - - """ - return True_() @@ -2334,15 +2262,23 @@ class ClauseList( ("operator", InternalTraversal.dp_operator), ] - def __init__(self, *clauses, **kwargs): - self.operator = kwargs.pop("operator", operators.comma_op) - self.group = kwargs.pop("group", True) - self.group_contents = kwargs.pop("group_contents", True) - if kwargs.pop("_flatten_sub_clauses", False): + def __init__( + self, + *clauses, + operator=operators.comma_op, + group=True, + group_contents=True, + _flatten_sub_clauses=False, + _literal_as_text_role: Type[roles.SQLRole] = roles.WhereHavingRole, + ): + self.operator = operator + self.group = group + self.group_contents = group_contents + if _flatten_sub_clauses: clauses = util.flatten_iterator(clauses) - self._text_converter_role = text_converter_role = kwargs.pop( - "_literal_as_text_role", roles.WhereHavingRole - ) + self._text_converter_role: Type[roles.SQLRole] = _literal_as_text_role + text_converter_role: Type[roles.SQLRole] = _literal_as_text_role + if self.group_contents: self.clauses = [ coercions.expect( @@ -2404,7 +2340,7 @@ class ClauseList( return self -class BooleanClauseList(ClauseList, ColumnElement): +class BooleanClauseList(ClauseList, ColumnElement[bool]): __visit_name__ = "clauselist" inherit_cache = True @@ -2531,60 +2467,7 @@ class BooleanClauseList(ClauseList, ColumnElement): def and_(cls, *clauses): r"""Produce a conjunction of expressions joined by ``AND``. - E.g.:: - - from sqlalchemy import and_ - - stmt = select(users_table).where( - and_( - users_table.c.name == 'wendy', - users_table.c.enrolled == True - ) - ) - - The :func:`.and_` conjunction is also available using the - Python ``&`` operator (though note that compound expressions - need to be parenthesized in order to function with Python - operator precedence behavior):: - - stmt = select(users_table).where( - (users_table.c.name == 'wendy') & - (users_table.c.enrolled == True) - ) - - The :func:`.and_` operation is also implicit in some cases; - the :meth:`_expression.Select.where` - method for example can be invoked multiple - times against a statement, which will have the effect of each - clause being combined using :func:`.and_`:: - - stmt = select(users_table).\ - where(users_table.c.name == 'wendy').\ - where(users_table.c.enrolled == True) - - The :func:`.and_` construct must be given at least one positional - argument in order to be valid; a :func:`.and_` construct with no - arguments is ambiguous. To produce an "empty" or dynamically - generated :func:`.and_` expression, from a given list of expressions, - a "default" element of ``True`` should be specified:: - - criteria = and_(True, *expressions) - - The above expression will compile to SQL as the expression ``true`` - or ``1 = 1``, depending on backend, if no other expressions are - present. If expressions are present, then the ``True`` value is - ignored as it does not affect the outcome of an AND expression that - has other elements. - - .. deprecated:: 1.4 The :func:`.and_` element now requires that at - least one argument is passed; creating the :func:`.and_` construct - with no arguments is deprecated, and will emit a deprecation warning - while continuing to produce a blank SQL string. - - .. seealso:: - - :func:`.or_` - + See :func:`_sql.and_` for full documentation. """ return cls._construct( operators.and_, True_._singleton, False_._singleton, *clauses @@ -2594,50 +2477,7 @@ class BooleanClauseList(ClauseList, ColumnElement): def or_(cls, *clauses): """Produce a conjunction of expressions joined by ``OR``. - E.g.:: - - from sqlalchemy import or_ - - stmt = select(users_table).where( - or_( - users_table.c.name == 'wendy', - users_table.c.name == 'jack' - ) - ) - - The :func:`.or_` conjunction is also available using the - Python ``|`` operator (though note that compound expressions - need to be parenthesized in order to function with Python - operator precedence behavior):: - - stmt = select(users_table).where( - (users_table.c.name == 'wendy') | - (users_table.c.name == 'jack') - ) - - The :func:`.or_` construct must be given at least one positional - argument in order to be valid; a :func:`.or_` construct with no - arguments is ambiguous. To produce an "empty" or dynamically - generated :func:`.or_` expression, from a given list of expressions, - a "default" element of ``False`` should be specified:: - - or_criteria = or_(False, *expressions) - - The above expression will compile to SQL as the expression ``false`` - or ``0 = 1``, depending on backend, if no other expressions are - present. If expressions are present, then the ``False`` value is - ignored as it does not affect the outcome of an OR expression which - has other elements. - - .. deprecated:: 1.4 The :func:`.or_` element now requires that at - least one argument is passed; creating the :func:`.or_` construct - with no arguments is deprecated, and will emit a deprecation warning - while continuing to produce a blank SQL string. - - .. seealso:: - - :func:`.and_` - + See :func:`_sql.or_` for full documentation. """ return cls._construct( operators.or_, False_._singleton, True_._singleton, *clauses @@ -2669,32 +2509,9 @@ class Tuple(ClauseList, ColumnElement): _traverse_internals = ClauseList._traverse_internals + [] @util.preload_module("sqlalchemy.sql.sqltypes") - def __init__(self, *clauses, **kw): - """Return a :class:`.Tuple`. - - Main usage is to produce a composite IN construct using - :meth:`.ColumnOperators.in_` :: - - from sqlalchemy import tuple_ - - tuple_(table.c.col1, table.c.col2).in_( - [(1, 2), (5, 12), (10, 19)] - ) - - .. versionchanged:: 1.3.6 Added support for SQLite IN tuples. - - .. warning:: - - The composite IN construct is not supported by all backends, and is - currently known to work on PostgreSQL, MySQL, and SQLite. - Unsupported backends will raise a subclass of - :class:`~sqlalchemy.exc.DBAPIError` when such an expression is - invoked. - - """ + def __init__(self, *clauses, types=None): sqltypes = util.preloaded.sql_sqltypes - types = kw.pop("types", None) if types is None: clauses = [ coercions.expect(roles.ExpressionElementRole, c) @@ -2716,7 +2533,7 @@ class Tuple(ClauseList, ColumnElement): ] self.type = sqltypes.TupleType(*[arg.type for arg in clauses]) - super(Tuple, self).__init__(*clauses, **kw) + super(Tuple, self).__init__(*clauses) @property def _select_iterable(self): @@ -2752,7 +2569,7 @@ class Tuple(ClauseList, ColumnElement): return self -class Case(ColumnElement): +class Case(ColumnElement[_T]): """Represent a ``CASE`` expression. :class:`.Case` is produced using the :func:`.case` factory function, @@ -2785,127 +2602,10 @@ class Case(ColumnElement): ("else_", InternalTraversal.dp_clauseelement), ] - def __init__(self, *whens, value=None, else_=None): - r"""Produce a ``CASE`` expression. - - The ``CASE`` construct in SQL is a conditional object that - acts somewhat analogously to an "if/then" construct in other - languages. It returns an instance of :class:`.Case`. - - :func:`.case` in its usual form is passed a series of "when" - constructs, that is, a list of conditions and results as tuples:: - - from sqlalchemy import case - - stmt = select(users_table).\ - where( - case( - (users_table.c.name == 'wendy', 'W'), - (users_table.c.name == 'jack', 'J'), - else_='E' - ) - ) - - The above statement will produce SQL resembling:: - - SELECT id, name FROM user - WHERE CASE - WHEN (name = :name_1) THEN :param_1 - WHEN (name = :name_2) THEN :param_2 - ELSE :param_3 - END - - When simple equality expressions of several values against a single - parent column are needed, :func:`.case` also has a "shorthand" format - used via the - :paramref:`.case.value` parameter, which is passed a column - expression to be compared. In this form, the :paramref:`.case.whens` - parameter is passed as a dictionary containing expressions to be - compared against keyed to result expressions. The statement below is - equivalent to the preceding statement:: - - stmt = select(users_table).\ - where( - case( - {"wendy": "W", "jack": "J"}, - value=users_table.c.name, - else_='E' - ) - ) - - The values which are accepted as result values in - :paramref:`.case.whens` as well as with :paramref:`.case.else_` are - coerced from Python literals into :func:`.bindparam` constructs. - SQL expressions, e.g. :class:`_expression.ColumnElement` constructs, - are accepted - as well. To coerce a literal string expression into a constant - expression rendered inline, use the :func:`_expression.literal_column` - construct, - as in:: - - from sqlalchemy import case, literal_column - - case( - ( - orderline.c.qty > 100, - literal_column("'greaterthan100'") - ), - ( - orderline.c.qty > 10, - literal_column("'greaterthan10'") - ), - else_=literal_column("'lessthan10'") - ) - - The above will render the given constants without using bound - parameters for the result values (but still for the comparison - values), as in:: - - CASE - WHEN (orderline.qty > :qty_1) THEN 'greaterthan100' - WHEN (orderline.qty > :qty_2) THEN 'greaterthan10' - ELSE 'lessthan10' - END - - :param \*whens: The criteria to be compared against, - :paramref:`.case.whens` accepts two different forms, based on - whether or not :paramref:`.case.value` is used. - - .. versionchanged:: 1.4 the :func:`_sql.case` - function now accepts the series of WHEN conditions positionally - - In the first form, it accepts a list of 2-tuples; each 2-tuple - consists of ``(<sql expression>, <value>)``, where the SQL - expression is a boolean expression and "value" is a resulting value, - e.g.:: - - case( - (users_table.c.name == 'wendy', 'W'), - (users_table.c.name == 'jack', 'J') - ) - - In the second form, it accepts a Python dictionary of comparison - values mapped to a resulting value; this form requires - :paramref:`.case.value` to be present, and values will be compared - using the ``==`` operator, e.g.:: - - case( - {"wendy": "W", "jack": "J"}, - value=users_table.c.name - ) - - :param value: An optional SQL expression which will be used as a - fixed "comparison point" for candidate values within a dictionary - passed to :paramref:`.case.whens`. - - :param else\_: An optional SQL expression which will be the evaluated - result of the ``CASE`` construct if all expressions within - :paramref:`.case.whens` evaluate to false. When omitted, most - databases will produce a result of NULL if none of the "when" - expressions evaluate to true. + # for case(), the type is derived from the whens. so for the moment + # users would have to cast() the case to get a specific type - - """ + def __init__(self, *whens, value=None, else_=None): whens = coercions._expression_collection_was_a_list( "whens", "case", whens @@ -2952,43 +2652,7 @@ class Case(ColumnElement): ) -def literal_column(text, type_=None): - r"""Produce a :class:`.ColumnClause` object that has the - :paramref:`_expression.column.is_literal` flag set to True. - - :func:`_expression.literal_column` is similar to - :func:`_expression.column`, except that - it is more often used as a "standalone" column expression that renders - exactly as stated; while :func:`_expression.column` - stores a string name that - will be assumed to be part of a table and may be quoted as such, - :func:`_expression.literal_column` can be that, - or any other arbitrary column-oriented - expression. - - :param text: the text of the expression; can be any SQL expression. - Quoting rules will not be applied. To specify a column-name expression - which should be subject to quoting rules, use the :func:`column` - function. - - :param type\_: an optional :class:`~sqlalchemy.types.TypeEngine` - object which will - provide result-set translation and additional expression semantics for - this column. If left as ``None`` the type will be :class:`.NullType`. - - .. seealso:: - - :func:`_expression.column` - - :func:`_expression.text` - - :ref:`sqlexpression_literal_column` - - """ - return ColumnClause(text, type_=type_, is_literal=True) - - -class Cast(WrapsColumnExpression, ColumnElement): +class Cast(WrapsColumnExpression, ColumnElement[_T]): """Represent a ``CAST`` expression. :class:`.Cast` is produced using the :func:`.cast` factory function, @@ -3020,57 +2684,6 @@ class Cast(WrapsColumnExpression, ColumnElement): ] def __init__(self, expression, type_): - r"""Produce a ``CAST`` expression. - - :func:`.cast` returns an instance of :class:`.Cast`. - - E.g.:: - - from sqlalchemy import cast, Numeric - - stmt = select(cast(product_table.c.unit_price, Numeric(10, 4))) - - The above statement will produce SQL resembling:: - - SELECT CAST(unit_price AS NUMERIC(10, 4)) FROM product - - The :func:`.cast` function performs two distinct functions when - used. The first is that it renders the ``CAST`` expression within - the resulting SQL string. The second is that it associates the given - type (e.g. :class:`.TypeEngine` class or instance) with the column - expression on the Python side, which means the expression will take - on the expression operator behavior associated with that type, - as well as the bound-value handling and result-row-handling behavior - of the type. - - .. versionchanged:: 0.9.0 :func:`.cast` now applies the given type - to the expression such that it takes effect on the bound-value, - e.g. the Python-to-database direction, in addition to the - result handling, e.g. database-to-Python, direction. - - An alternative to :func:`.cast` is the :func:`.type_coerce` function. - This function performs the second task of associating an expression - with a specific type, but does not render the ``CAST`` expression - in SQL. - - :param expression: A SQL expression, such as a - :class:`_expression.ColumnElement` - expression or a Python string which will be coerced into a bound - literal value. - - :param type\_: A :class:`.TypeEngine` class or instance indicating - the type to which the ``CAST`` should apply. - - .. seealso:: - - :ref:`coretutorial_casts` - - :func:`.type_coerce` - an alternative to CAST that coerces the type - on the Python side only, which is often sufficient to generate the - correct SQL and data coercion. - - - """ self.type = type_api.to_instance(type_) self.clause = coercions.expect( roles.ExpressionElementRole, @@ -3089,7 +2702,7 @@ class Cast(WrapsColumnExpression, ColumnElement): return self.clause -class TypeCoerce(WrapsColumnExpression, ColumnElement): +class TypeCoerce(WrapsColumnExpression, ColumnElement[_T]): """Represent a Python-side type-coercion wrapper. :class:`.TypeCoerce` supplies the :func:`_expression.type_coerce` @@ -3115,80 +2728,6 @@ class TypeCoerce(WrapsColumnExpression, ColumnElement): ] def __init__(self, expression, type_): - r"""Associate a SQL expression with a particular type, without rendering - ``CAST``. - - E.g.:: - - from sqlalchemy import type_coerce - - stmt = select(type_coerce(log_table.date_string, StringDateTime())) - - The above construct will produce a :class:`.TypeCoerce` object, which - does not modify the rendering in any way on the SQL side, with the - possible exception of a generated label if used in a columns clause - context:: - - SELECT date_string AS date_string FROM log - - When result rows are fetched, the ``StringDateTime`` type processor - will be applied to result rows on behalf of the ``date_string`` column. - - .. note:: the :func:`.type_coerce` construct does not render any - SQL syntax of its own, including that it does not imply - parenthesization. Please use :meth:`.TypeCoerce.self_group` - if explicit parenthesization is required. - - In order to provide a named label for the expression, use - :meth:`_expression.ColumnElement.label`:: - - stmt = select( - type_coerce(log_table.date_string, StringDateTime()).label('date') - ) - - - A type that features bound-value handling will also have that behavior - take effect when literal values or :func:`.bindparam` constructs are - passed to :func:`.type_coerce` as targets. - For example, if a type implements the - :meth:`.TypeEngine.bind_expression` - method or :meth:`.TypeEngine.bind_processor` method or equivalent, - these functions will take effect at statement compilation/execution - time when a literal value is passed, as in:: - - # bound-value handling of MyStringType will be applied to the - # literal value "some string" - stmt = select(type_coerce("some string", MyStringType)) - - When using :func:`.type_coerce` with composed expressions, note that - **parenthesis are not applied**. If :func:`.type_coerce` is being - used in an operator context where the parenthesis normally present from - CAST are necessary, use the :meth:`.TypeCoerce.self_group` method:: - - >>> some_integer = column("someint", Integer) - >>> some_string = column("somestr", String) - >>> expr = type_coerce(some_integer + 5, String) + some_string - >>> print(expr) - someint + :someint_1 || somestr - >>> expr = type_coerce(some_integer + 5, String).self_group() + some_string - >>> print(expr) - (someint + :someint_1) || somestr - - :param expression: A SQL expression, such as a - :class:`_expression.ColumnElement` - expression or a Python string which will be coerced into a bound - literal value. - - :param type\_: A :class:`.TypeEngine` class or instance indicating - the type to which the expression is coerced. - - .. seealso:: - - :ref:`coretutorial_casts` - - :func:`.cast` - - """ # noqa self.type = type_api.to_instance(type_) self.clause = coercions.expect( roles.ExpressionElementRole, @@ -3222,7 +2761,7 @@ class TypeCoerce(WrapsColumnExpression, ColumnElement): return self -class Extract(ColumnElement): +class Extract(ColumnElement[_T]): """Represent a SQL EXTRACT clause, ``extract(field FROM expr)``.""" __visit_name__ = "extract" @@ -3232,44 +2771,7 @@ class Extract(ColumnElement): ("field", InternalTraversal.dp_string), ] - def __init__(self, field, expr, **kwargs): - """Return a :class:`.Extract` construct. - - This is typically available as :func:`.extract` - as well as ``func.extract`` from the - :data:`.func` namespace. - - :param field: The field to extract. - - :param expr: A column or Python scalar expression serving as the - right side of the ``EXTRACT`` expression. - - E.g.:: - - from sqlalchemy import extract - from sqlalchemy import table, column - - logged_table = table("user", - column("id"), - column("date_created"), - ) - - stmt = select(logged_table.c.id).where( - extract("YEAR", logged_table.c.date_created) == 2021 - ) - - In the above example, the statement is used to select ids from the - database where the ``YEAR`` component matches a specific value. - - Similarly, one can also select an extracted component:: - - stmt = select( - extract("YEAR", logged_table.c.date_created) - ).where(logged_table.c.id == 1) - - The implementation of ``EXTRACT`` may vary across database backends. - Users are reminded to consult their database documentation. - """ + def __init__(self, field, expr): self.type = type_api.INTEGERTYPE self.field = field self.expr = coercions.expect(roles.ExpressionElementRole, expr) @@ -3314,10 +2816,10 @@ class _textual_label_reference(ColumnElement): @util.memoized_property def _text_clause(self): - return TextClause._create_text(self.element) + return TextClause(self.element) -class UnaryExpression(ColumnElement): +class UnaryExpression(ColumnElement[_T]): """Define a 'unary' expression. A unary expression has a single column expression @@ -3344,7 +2846,7 @@ class UnaryExpression(ColumnElement): element, operator=None, modifier=None, - type_=None, + type_: Union[Type["TypeEngine[_T]"], "TypeEngine[_T]"] = None, wraps_column_expression=False, ): self.operator = operator @@ -3353,51 +2855,11 @@ class UnaryExpression(ColumnElement): self.element = element.self_group( against=self.operator or self.modifier ) - self.type = type_api.to_instance(type_) + self.type: TypeEngine[_T] = type_api.to_instance(type_) self.wraps_column_expression = wraps_column_expression @classmethod def _create_nulls_first(cls, column): - """Produce the ``NULLS FIRST`` modifier for an ``ORDER BY`` expression. - - :func:`.nulls_first` is intended to modify the expression produced - by :func:`.asc` or :func:`.desc`, and indicates how NULL values - should be handled when they are encountered during ordering:: - - - from sqlalchemy import desc, nulls_first - - stmt = select(users_table).order_by( - nulls_first(desc(users_table.c.name))) - - The SQL expression from the above would resemble:: - - SELECT id, name FROM user ORDER BY name DESC NULLS FIRST - - Like :func:`.asc` and :func:`.desc`, :func:`.nulls_first` is typically - invoked from the column expression itself using - :meth:`_expression.ColumnElement.nulls_first`, - rather than as its standalone - function version, as in:: - - stmt = select(users_table).order_by( - users_table.c.name.desc().nulls_first()) - - .. versionchanged:: 1.4 :func:`.nulls_first` is renamed from - :func:`.nullsfirst` in previous releases. - The previous name remains available for backwards compatibility. - - .. seealso:: - - :func:`.asc` - - :func:`.desc` - - :func:`.nulls_last` - - :meth:`_expression.Select.order_by` - - """ return UnaryExpression( coercions.expect(roles.ByOfRole, column), modifier=operators.nulls_first_op, @@ -3406,46 +2868,6 @@ class UnaryExpression(ColumnElement): @classmethod def _create_nulls_last(cls, column): - """Produce the ``NULLS LAST`` modifier for an ``ORDER BY`` expression. - - :func:`.nulls_last` is intended to modify the expression produced - by :func:`.asc` or :func:`.desc`, and indicates how NULL values - should be handled when they are encountered during ordering:: - - - from sqlalchemy import desc, nulls_last - - stmt = select(users_table).order_by( - nulls_last(desc(users_table.c.name))) - - The SQL expression from the above would resemble:: - - SELECT id, name FROM user ORDER BY name DESC NULLS LAST - - Like :func:`.asc` and :func:`.desc`, :func:`.nulls_last` is typically - invoked from the column expression itself using - :meth:`_expression.ColumnElement.nulls_last`, - rather than as its standalone - function version, as in:: - - stmt = select(users_table).order_by( - users_table.c.name.desc().nulls_last()) - - .. versionchanged:: 1.4 :func:`.nulls_last` is renamed from - :func:`.nullslast` in previous releases. - The previous name remains available for backwards compatibility. - - .. seealso:: - - :func:`.asc` - - :func:`.desc` - - :func:`.nulls_first` - - :meth:`_expression.Select.order_by` - - """ return UnaryExpression( coercions.expect(roles.ByOfRole, column), modifier=operators.nulls_last_op, @@ -3454,41 +2876,6 @@ class UnaryExpression(ColumnElement): @classmethod def _create_desc(cls, column): - """Produce a descending ``ORDER BY`` clause element. - - e.g.:: - - from sqlalchemy import desc - - stmt = select(users_table).order_by(desc(users_table.c.name)) - - will produce SQL as:: - - SELECT id, name FROM user ORDER BY name DESC - - The :func:`.desc` function is a standalone version of the - :meth:`_expression.ColumnElement.desc` - method available on all SQL expressions, - e.g.:: - - - stmt = select(users_table).order_by(users_table.c.name.desc()) - - :param column: A :class:`_expression.ColumnElement` (e.g. - scalar SQL expression) - with which to apply the :func:`.desc` operation. - - .. seealso:: - - :func:`.asc` - - :func:`.nulls_first` - - :func:`.nulls_last` - - :meth:`_expression.Select.order_by` - - """ return UnaryExpression( coercions.expect(roles.ByOfRole, column), modifier=operators.desc_op, @@ -3497,40 +2884,6 @@ class UnaryExpression(ColumnElement): @classmethod def _create_asc(cls, column): - """Produce an ascending ``ORDER BY`` clause element. - - e.g.:: - - from sqlalchemy import asc - stmt = select(users_table).order_by(asc(users_table.c.name)) - - will produce SQL as:: - - SELECT id, name FROM user ORDER BY name ASC - - The :func:`.asc` function is a standalone version of the - :meth:`_expression.ColumnElement.asc` - method available on all SQL expressions, - e.g.:: - - - stmt = select(users_table).order_by(users_table.c.name.asc()) - - :param column: A :class:`_expression.ColumnElement` (e.g. - scalar SQL expression) - with which to apply the :func:`.asc` operation. - - .. seealso:: - - :func:`.desc` - - :func:`.nulls_first` - - :func:`.nulls_last` - - :meth:`_expression.Select.order_by` - - """ return UnaryExpression( coercions.expect(roles.ByOfRole, column), modifier=operators.asc_op, @@ -3539,41 +2892,6 @@ class UnaryExpression(ColumnElement): @classmethod def _create_distinct(cls, expr): - """Produce an column-expression-level unary ``DISTINCT`` clause. - - This applies the ``DISTINCT`` keyword to an individual column - expression, and is typically contained within an aggregate function, - as in:: - - from sqlalchemy import distinct, func - stmt = select(func.count(distinct(users_table.c.name))) - - The above would produce an expression resembling:: - - SELECT COUNT(DISTINCT name) FROM user - - The :func:`.distinct` function is also available as a column-level - method, e.g. :meth:`_expression.ColumnElement.distinct`, as in:: - - stmt = select(func.count(users_table.c.name.distinct())) - - The :func:`.distinct` operator is different from the - :meth:`_expression.Select.distinct` method of - :class:`_expression.Select`, - which produces a ``SELECT`` statement - with ``DISTINCT`` applied to the result set as a whole, - e.g. a ``SELECT DISTINCT`` expression. See that method for further - information. - - .. seealso:: - - :meth:`_expression.ColumnElement.distinct` - - :meth:`_expression.Select.distinct` - - :data:`.func` - - """ expr = coercions.expect(roles.ExpressionElementRole, expr) return UnaryExpression( expr, @@ -3625,57 +2943,6 @@ class CollectionAggregate(UnaryExpression): @classmethod def _create_any(cls, expr): - """Produce an ANY expression. - - For dialects such as that of PostgreSQL, this operator applies - to usage of the :class:`_types.ARRAY` datatype, for that of - MySQL, it may apply to a subquery. e.g.:: - - # renders on PostgreSQL: - # '5 = ANY (somearray)' - expr = 5 == any_(mytable.c.somearray) - - # renders on MySQL: - # '5 = ANY (SELECT value FROM table)' - expr = 5 == any_(select(table.c.value)) - - Comparison to NULL may work using ``None`` or :func:`_sql.null`:: - - None == any_(mytable.c.somearray) - - The any_() / all_() operators also feature a special "operand flipping" - behavior such that if any_() / all_() are used on the left side of a - comparison using a standalone operator such as ``==``, ``!=``, etc. - (not including operator methods such as - :meth:`_sql.ColumnOperators.is_`) the rendered expression is flipped:: - - # would render '5 = ANY (column)` - any_(mytable.c.column) == 5 - - Or with ``None``, which note will not perform - the usual step of rendering "IS" as is normally the case for NULL:: - - # would render 'NULL = ANY(somearray)' - any_(mytable.c.somearray) == None - - .. versionchanged:: 1.4.26 repaired the use of any_() / all_() - comparing to NULL on the right side to be flipped to the left. - - The column-level :meth:`_sql.ColumnElement.any_` method (not to be - confused with :class:`_types.ARRAY` level - :meth:`_types.ARRAY.Comparator.any`) is shorthand for - ``any_(col)``:: - - 5 = mytable.c.somearray.any_() - - .. seealso:: - - :meth:`_sql.ColumnOperators.any_` - - :func:`_expression.all_` - - """ - expr = coercions.expect(roles.ExpressionElementRole, expr) expr = expr.self_group() @@ -3688,56 +2955,6 @@ class CollectionAggregate(UnaryExpression): @classmethod def _create_all(cls, expr): - """Produce an ALL expression. - - For dialects such as that of PostgreSQL, this operator applies - to usage of the :class:`_types.ARRAY` datatype, for that of - MySQL, it may apply to a subquery. e.g.:: - - # renders on PostgreSQL: - # '5 = ALL (somearray)' - expr = 5 == all_(mytable.c.somearray) - - # renders on MySQL: - # '5 = ALL (SELECT value FROM table)' - expr = 5 == all_(select(table.c.value)) - - Comparison to NULL may work using ``None``:: - - None == all_(mytable.c.somearray) - - The any_() / all_() operators also feature a special "operand flipping" - behavior such that if any_() / all_() are used on the left side of a - comparison using a standalone operator such as ``==``, ``!=``, etc. - (not including operator methods such as - :meth:`_sql.ColumnOperators.is_`) the rendered expression is flipped:: - - # would render '5 = ALL (column)` - all_(mytable.c.column) == 5 - - Or with ``None``, which note will not perform - the usual step of rendering "IS" as is normally the case for NULL:: - - # would render 'NULL = ALL(somearray)' - all_(mytable.c.somearray) == None - - .. versionchanged:: 1.4.26 repaired the use of any_() / all_() - comparing to NULL on the right side to be flipped to the left. - - The column-level :meth:`_sql.ColumnElement.all_` method (not to be - confused with :class:`_types.ARRAY` level - :meth:`_types.ARRAY.Comparator.all`) is shorthand for - ``all_(col)``:: - - 5 == mytable.c.somearray.all_() - - .. seealso:: - - :meth:`_sql.ColumnOperators.all_` - - :func:`_expression.any_` - - """ expr = coercions.expect(roles.ExpressionElementRole, expr) expr = expr.self_group() return CollectionAggregate( @@ -3792,7 +3009,7 @@ class AsBoolean(WrapsColumnExpression, UnaryExpression): return AsBoolean(self.element, self.negate, self.operator) -class BinaryExpression(ColumnElement): +class BinaryExpression(ColumnElement[_T]): """Represent an expression that is ``LEFT <operator> RIGHT``. A :class:`.BinaryExpression` is generated automatically @@ -3827,7 +3044,15 @@ class BinaryExpression(ColumnElement): """ def __init__( - self, left, right, operator, type_=None, negate=None, modifiers=None + self, + left: ColumnElement, + right: Union[ColumnElement, ClauseList], + operator, + type_: Optional[ + Union[Type["TypeEngine[_T]"], "TypeEngine[_T]"] + ] = None, + negate=None, + modifiers=None, ): # allow compatibility with libraries that # refer to BinaryExpression directly and pass strings @@ -3838,7 +3063,7 @@ class BinaryExpression(ColumnElement): self.left = left.self_group(against=operator) self.right = right.self_group(against=operator) self.operator = operator - self.type = type_api.to_instance(type_) + self.type: TypeEngine[_T] = type_api.to_instance(type_) self.negate = negate self._is_implicitly_boolean = operators.is_boolean(operator) @@ -3855,6 +3080,13 @@ class BinaryExpression(ColumnElement): __nonzero__ = __bool__ + if typing.TYPE_CHECKING: + + def __invert__( + self: "BinaryExpression[_T]", + ) -> "BinaryExpression[_T]": + ... + @property def is_comparison(self): return operators.is_comparison(self.operator) @@ -3996,7 +3228,7 @@ RANGE_UNBOUNDED = util.symbol("RANGE_UNBOUNDED") RANGE_CURRENT = util.symbol("RANGE_CURRENT") -class Over(ColumnElement): +class Over(ColumnElement[_T]): """Represent an OVER clause. This is a special operator against a so-called @@ -4026,88 +3258,6 @@ class Over(ColumnElement): def __init__( self, element, partition_by=None, order_by=None, range_=None, rows=None ): - r"""Produce an :class:`.Over` object against a function. - - Used against aggregate or so-called "window" functions, - for database backends that support window functions. - - :func:`_expression.over` is usually called using - the :meth:`.FunctionElement.over` method, e.g.:: - - func.row_number().over(order_by=mytable.c.some_column) - - Would produce:: - - ROW_NUMBER() OVER(ORDER BY some_column) - - Ranges are also possible using the :paramref:`.expression.over.range_` - and :paramref:`.expression.over.rows` parameters. These - mutually-exclusive parameters each accept a 2-tuple, which contains - a combination of integers and None:: - - func.row_number().over( - order_by=my_table.c.some_column, range_=(None, 0)) - - The above would produce:: - - ROW_NUMBER() OVER(ORDER BY some_column - RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) - - A value of ``None`` indicates "unbounded", a - value of zero indicates "current row", and negative / positive - integers indicate "preceding" and "following": - - * RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING:: - - func.row_number().over(order_by='x', range_=(-5, 10)) - - * ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW:: - - func.row_number().over(order_by='x', rows=(None, 0)) - - * RANGE BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING:: - - func.row_number().over(order_by='x', range_=(-2, None)) - - * RANGE BETWEEN 1 FOLLOWING AND 3 FOLLOWING:: - - func.row_number().over(order_by='x', range_=(1, 3)) - - .. versionadded:: 1.1 support for RANGE / ROWS within a window - - - :param element: a :class:`.FunctionElement`, :class:`.WithinGroup`, - or other compatible construct. - :param partition_by: a column element or string, or a list - of such, that will be used as the PARTITION BY clause - of the OVER construct. - :param order_by: a column element or string, or a list - of such, that will be used as the ORDER BY clause - of the OVER construct. - :param range\_: optional range clause for the window. This is a - tuple value which can contain integer values or ``None``, - and will render a RANGE BETWEEN PRECEDING / FOLLOWING clause. - - .. versionadded:: 1.1 - - :param rows: optional rows clause for the window. This is a tuple - value which can contain integer values or None, and will render - a ROWS BETWEEN PRECEDING / FOLLOWING clause. - - .. versionadded:: 1.1 - - This function is also available from the :data:`~.expression.func` - construct itself via the :meth:`.FunctionElement.over` method. - - .. seealso:: - - :ref:`tutorial_window_functions` - in the :ref:`unified_tutorial` - - :data:`.expression.func` - - :func:`_expression.within_group` - - """ self.element = element if order_by is not None: self.order_by = ClauseList( @@ -4191,7 +3341,7 @@ class Over(ColumnElement): ) -class WithinGroup(ColumnElement): +class WithinGroup(ColumnElement[_T]): """Represent a WITHIN GROUP (ORDER BY) clause. This is a special operator against so-called @@ -4218,44 +3368,6 @@ class WithinGroup(ColumnElement): order_by = None def __init__(self, element, *order_by): - r"""Produce a :class:`.WithinGroup` object against a function. - - Used against so-called "ordered set aggregate" and "hypothetical - set aggregate" functions, including :class:`.percentile_cont`, - :class:`.rank`, :class:`.dense_rank`, etc. - - :func:`_expression.within_group` is usually called using - the :meth:`.FunctionElement.within_group` method, e.g.:: - - from sqlalchemy import within_group - stmt = select( - department.c.id, - func.percentile_cont(0.5).within_group( - department.c.salary.desc() - ) - ) - - The above statement would produce SQL similar to - ``SELECT department.id, percentile_cont(0.5) - WITHIN GROUP (ORDER BY department.salary DESC)``. - - :param element: a :class:`.FunctionElement` construct, typically - generated by :data:`~.expression.func`. - :param \*order_by: one or more column elements that will be used - as the ORDER BY clause of the WITHIN GROUP construct. - - .. versionadded:: 1.1 - - .. seealso:: - - :ref:`tutorial_functions_within_group` - in the - :ref:`unified_tutorial` - - :data:`.expression.func` - - :func:`_expression.over` - - """ self.element = element if order_by is not None: self.order_by = ClauseList( @@ -4332,31 +3444,6 @@ class FunctionFilter(ColumnElement): criterion = None def __init__(self, func, *criterion): - """Produce a :class:`.FunctionFilter` object against a function. - - Used against aggregate and window functions, - for database backends that support the "FILTER" clause. - - E.g.:: - - from sqlalchemy import funcfilter - funcfilter(func.count(1), MyClass.name == 'some name') - - Would produce "COUNT(1) FILTER (WHERE myclass.name = 'some name')". - - This function is also available from the :data:`~.expression.func` - construct itself via the :meth:`.FunctionElement.filter` method. - - .. versionadded:: 1.0.0 - - .. seealso:: - - :ref:`tutorial_functions_within_group` - in the - :ref:`unified_tutorial` - - :meth:`.FunctionElement.filter` - - """ self.func = func self.filter(*criterion) @@ -4431,7 +3518,7 @@ class FunctionFilter(ColumnElement): ) -class Label(roles.LabeledColumnExprRole, ColumnElement): +class Label(roles.LabeledColumnExprRole, ColumnElement[_T]): """Represents a column label (AS). Represent a label, as typically applied to any column-level @@ -4448,22 +3535,6 @@ class Label(roles.LabeledColumnExprRole, ColumnElement): ] def __init__(self, name, element, type_=None): - """Return a :class:`Label` object for the - given :class:`_expression.ColumnElement`. - - A label changes the name of an element in the columns clause of a - ``SELECT`` statement, typically via the ``AS`` SQL keyword. - - This functionality is more conveniently available via the - :meth:`_expression.ColumnElement.label` method on - :class:`_expression.ColumnElement`. - - :param name: label name - - :param obj: a :class:`_expression.ColumnElement`. - - """ - orig_element = element element = coercions.expect( roles.ExpressionElementRole, @@ -4583,7 +3654,7 @@ class Label(roles.LabeledColumnExprRole, ColumnElement): return self.key, e -class NamedColumn(ColumnElement): +class NamedColumn(ColumnElement[_T]): is_literal = False table = None @@ -4673,7 +3744,7 @@ class ColumnClause( roles.LabeledColumnExprRole, roles.StrAsPlainColumnRole, Immutable, - NamedColumn, + NamedColumn[_T], ): """Represents a column expression from any textual string. @@ -4728,101 +3799,18 @@ class ColumnClause( _is_multiparam_column = False - def __init__(self, text, type_=None, is_literal=False, _selectable=None): - """Produce a :class:`.ColumnClause` object. - - The :class:`.ColumnClause` is a lightweight analogue to the - :class:`_schema.Column` class. The :func:`_expression.column` - function can - be invoked with just a name alone, as in:: - - from sqlalchemy import column - - id, name = column("id"), column("name") - stmt = select(id, name).select_from("user") - - The above statement would produce SQL like:: - - SELECT id, name FROM user - - Once constructed, :func:`_expression.column` - may be used like any other SQL - expression element such as within :func:`_expression.select` - constructs:: - - from sqlalchemy.sql import column - - id, name = column("id"), column("name") - stmt = select(id, name).select_from("user") - - The text handled by :func:`_expression.column` - is assumed to be handled - like the name of a database column; if the string contains mixed case, - special characters, or matches a known reserved word on the target - backend, the column expression will render using the quoting - behavior determined by the backend. To produce a textual SQL - expression that is rendered exactly without any quoting, - use :func:`_expression.literal_column` instead, - or pass ``True`` as the - value of :paramref:`_expression.column.is_literal`. Additionally, - full SQL - statements are best handled using the :func:`_expression.text` - construct. - - :func:`_expression.column` can be used in a table-like - fashion by combining it with the :func:`.table` function - (which is the lightweight analogue to :class:`_schema.Table` - ) to produce - a working table construct with minimal boilerplate:: - - from sqlalchemy import table, column, select - - user = table("user", - column("id"), - column("name"), - column("description"), - ) - - stmt = select(user.c.description).where(user.c.name == 'wendy') - - A :func:`_expression.column` / :func:`.table` - construct like that illustrated - above can be created in an - ad-hoc fashion and is not associated with any - :class:`_schema.MetaData`, DDL, or events, unlike its - :class:`_schema.Table` counterpart. - - .. versionchanged:: 1.0.0 :func:`_expression.column` can now - be imported from the plain ``sqlalchemy`` namespace like any - other SQL element. - - :param text: the text of the element. - - :param type: :class:`_types.TypeEngine` object which can associate - this :class:`.ColumnClause` with a type. - - :param is_literal: if True, the :class:`.ColumnClause` is assumed to - be an exact expression that will be delivered to the output with no - quoting rules applied regardless of case sensitive settings. the - :func:`_expression.literal_column()` function essentially invokes - :func:`_expression.column` while passing ``is_literal=True``. - - .. seealso:: - - :class:`_schema.Column` - - :func:`_expression.literal_column` - - :func:`.table` - - :func:`_expression.text` - - :ref:`sqlexpression_literal_column` - - """ + def __init__( + self, + text: str, + type_: Optional[ + Union[Type["TypeEngine[_T]"], "TypeEngine[_T]"] + ] = None, + is_literal: bool = False, + _selectable: Optional["FromClause"] = None, + ): self.key = self.name = text self.table = _selectable - self.type = type_api.to_instance(type_) + self.type: TypeEngine[_T] = type_api.to_instance(type_) self.is_literal = is_literal def get_children(self, column_tables=False, **kw): @@ -5010,6 +3998,16 @@ class CollationClause(ColumnElement): _traverse_internals = [("collation", InternalTraversal.dp_string)] + @classmethod + def _create_collation_expression(cls, expression, collation): + expr = coercions.expect(roles.ExpressionElementRole, expression) + return BinaryExpression( + expr, + CollationClause(collation), + operators.collate, + type_=expression.type, + ) + def __init__(self, collation): self.collation = collation @@ -5280,7 +4278,7 @@ class _anonymous_label(_truncated_label): @classmethod def safe_construct( cls, seed, body, enclosing_label=None, sanitize_key=False - ): + ) -> "_anonymous_label": if sanitize_key: body = re.sub(r"[%\(\) \$]+", "_", body).strip("_") diff --git a/lib/sqlalchemy/sql/expression.py b/lib/sqlalchemy/sql/expression.py index 54f67b930..680eae754 100644 --- a/lib/sqlalchemy/sql/expression.py +++ b/lib/sqlalchemy/sql/expression.py @@ -10,263 +10,143 @@ """ -__all__ = [ - "Alias", - "AliasedReturnsRows", - "any_", - "all_", - "CacheKey", - "ClauseElement", - "ColumnCollection", - "ColumnElement", - "CompoundSelect", - "Delete", - "FromClause", - "Insert", - "Join", - "Lateral", - "LambdaElement", - "StatementLambdaElement", - "Select", - "Selectable", - "TableClause", - "TableValuedAlias", - "Update", - "Values", - "alias", - "and_", - "asc", - "between", - "bindparam", - "case", - "cast", - "column", - "custom_op", - "cte", - "delete", - "desc", - "distinct", - "except_", - "except_all", - "exists", - "extract", - "func", - "modifier", - "collate", - "insert", - "intersect", - "intersect_all", - "join", - "label", - "lateral", - "lambda_stmt", - "literal", - "literal_column", - "not_", - "null", - "nulls_first", - "nulls_last", - "or_", - "outparam", - "outerjoin", - "over", - "select", - "table", - "text", - "tuple_", - "type_coerce", - "quoted_name", - "union", - "union_all", - "update", - "quoted_name", - "within_group", - "Subquery", - "TableSample", - "tablesample", - "values", -] +from ._dml_constructors import delete as delete +from ._dml_constructors import insert as insert +from ._dml_constructors import update as update +from ._elements_constructors import all_ as all_ +from ._elements_constructors import and_ as and_ +from ._elements_constructors import any_ as any_ +from ._elements_constructors import asc as asc +from ._elements_constructors import between as between +from ._elements_constructors import bindparam as bindparam +from ._elements_constructors import case as case +from ._elements_constructors import cast as cast +from ._elements_constructors import collate as collate +from ._elements_constructors import column as column +from ._elements_constructors import desc as desc +from ._elements_constructors import distinct as distinct +from ._elements_constructors import extract as extract +from ._elements_constructors import false as false +from ._elements_constructors import funcfilter as funcfilter +from ._elements_constructors import label as label +from ._elements_constructors import not_ as not_ +from ._elements_constructors import null as null +from ._elements_constructors import nulls_first as nulls_first +from ._elements_constructors import nulls_last as nulls_last +from ._elements_constructors import or_ as or_ +from ._elements_constructors import outparam as outparam +from ._elements_constructors import over as over +from ._elements_constructors import text as text +from ._elements_constructors import true as true +from ._elements_constructors import tuple_ as tuple_ +from ._elements_constructors import type_coerce as type_coerce +from ._elements_constructors import typing as typing +from ._elements_constructors import within_group as within_group +from ._selectable_constructors import alias as alias +from ._selectable_constructors import cte as cte +from ._selectable_constructors import except_ as except_ +from ._selectable_constructors import except_all as except_all +from ._selectable_constructors import exists as exists +from ._selectable_constructors import intersect as intersect +from ._selectable_constructors import intersect_all as intersect_all +from ._selectable_constructors import join as join +from ._selectable_constructors import lateral as lateral +from ._selectable_constructors import outerjoin as outerjoin +from ._selectable_constructors import select as select +from ._selectable_constructors import table as table +from ._selectable_constructors import tablesample as tablesample +from ._selectable_constructors import union as union +from ._selectable_constructors import union_all as union_all +from ._selectable_constructors import values as values +from .base import _from_objects as _from_objects +from .base import _select_iterables as _select_iterables +from .base import ColumnCollection as ColumnCollection +from .base import Executable as Executable +from .cache_key import CacheKey as CacheKey +from .dml import Delete as Delete +from .dml import Insert as Insert +from .dml import Update as Update +from .dml import UpdateBase as UpdateBase +from .dml import ValuesBase as ValuesBase +from .elements import _truncated_label as _truncated_label +from .elements import BinaryExpression as BinaryExpression +from .elements import BindParameter as BindParameter +from .elements import BooleanClauseList as BooleanClauseList +from .elements import Case as Case +from .elements import Cast as Cast +from .elements import ClauseElement as ClauseElement +from .elements import ClauseList as ClauseList +from .elements import CollectionAggregate as CollectionAggregate +from .elements import ColumnClause as ColumnClause +from .elements import ColumnElement as ColumnElement +from .elements import Extract as Extract +from .elements import False_ as False_ +from .elements import FunctionFilter as FunctionFilter +from .elements import Grouping as Grouping +from .elements import Label as Label +from .elements import literal as literal +from .elements import literal_column as literal_column +from .elements import Null as Null +from .elements import Over as Over +from .elements import quoted_name as quoted_name +from .elements import ReleaseSavepointClause as ReleaseSavepointClause +from .elements import RollbackToSavepointClause as RollbackToSavepointClause +from .elements import SavepointClause as SavepointClause +from .elements import TextClause as TextClause +from .elements import True_ as True_ +from .elements import Tuple as Tuple +from .elements import TypeClause as TypeClause +from .elements import TypeCoerce as TypeCoerce +from .elements import UnaryExpression as UnaryExpression +from .elements import WithinGroup as WithinGroup +from .functions import func as func +from .functions import Function as Function +from .functions import FunctionElement as FunctionElement +from .functions import modifier as modifier +from .lambdas import lambda_stmt as lambda_stmt +from .lambdas import LambdaElement as LambdaElement +from .lambdas import StatementLambdaElement as StatementLambdaElement +from .operators import ColumnOperators as ColumnOperators +from .operators import custom_op as custom_op +from .operators import Operators as Operators +from .selectable import Alias as Alias +from .selectable import AliasedReturnsRows as AliasedReturnsRows +from .selectable import CompoundSelect as CompoundSelect +from .selectable import CTE as CTE +from .selectable import Exists as Exists +from .selectable import FromClause as FromClause +from .selectable import FromGrouping as FromGrouping +from .selectable import GenerativeSelect as GenerativeSelect +from .selectable import HasCTE as HasCTE +from .selectable import HasPrefixes as HasPrefixes +from .selectable import HasSuffixes as HasSuffixes +from .selectable import Join as Join +from .selectable import LABEL_STYLE_DEFAULT as LABEL_STYLE_DEFAULT +from .selectable import LABEL_STYLE_NONE as LABEL_STYLE_NONE +from .selectable import Lateral as Lateral +from .selectable import ReturnsRows as ReturnsRows +from .selectable import ScalarSelect as ScalarSelect +from .selectable import Select as Select +from .selectable import Selectable as Selectable +from .selectable import SelectBase as SelectBase +from .selectable import Subquery as Subquery +from .selectable import TableClause as TableClause +from .selectable import TableSample as TableSample +from .selectable import TableValuedAlias as TableValuedAlias +from .selectable import TextAsFrom as TextAsFrom +from .selectable import TextualSelect as TextualSelect +from .selectable import Values as Values +from .visitors import Visitable as Visitable -from typing import Callable +if True: + # work around zimports + from .selectable import ( + LABEL_STYLE_DISAMBIGUATE_ONLY as LABEL_STYLE_DISAMBIGUATE_ONLY, + ) + from .selectable import ( + LABEL_STYLE_TABLENAME_PLUS_COL as LABEL_STYLE_TABLENAME_PLUS_COL, + ) -from .base import _from_objects -from .base import _select_iterables -from .base import ColumnCollection -from .base import Executable -from .cache_key import CacheKey -from .dml import Delete -from .dml import Insert -from .dml import Update -from .dml import UpdateBase -from .dml import ValuesBase -from .elements import _truncated_label -from .elements import between -from .elements import BinaryExpression -from .elements import BindParameter -from .elements import BooleanClauseList -from .elements import Case -from .elements import Cast -from .elements import ClauseElement -from .elements import ClauseList -from .elements import collate -from .elements import CollectionAggregate -from .elements import ColumnClause -from .elements import ColumnElement -from .elements import Extract -from .elements import False_ -from .elements import FunctionFilter -from .elements import Grouping -from .elements import Label -from .elements import literal -from .elements import literal_column -from .elements import not_ -from .elements import Null -from .elements import outparam -from .elements import Over -from .elements import quoted_name -from .elements import ReleaseSavepointClause -from .elements import RollbackToSavepointClause -from .elements import SavepointClause -from .elements import TextClause -from .elements import True_ -from .elements import Tuple -from .elements import TypeClause -from .elements import TypeCoerce -from .elements import UnaryExpression -from .elements import WithinGroup -from .functions import func -from .functions import Function -from .functions import FunctionElement -from .functions import modifier -from .lambdas import lambda_stmt -from .lambdas import LambdaElement -from .lambdas import StatementLambdaElement -from .operators import ColumnOperators -from .operators import custom_op -from .operators import Operators -from .selectable import Alias -from .selectable import AliasedReturnsRows -from .selectable import CompoundSelect -from .selectable import CTE -from .selectable import Exists -from .selectable import FromClause -from .selectable import FromGrouping -from .selectable import GenerativeSelect -from .selectable import HasCTE -from .selectable import HasPrefixes -from .selectable import HasSuffixes -from .selectable import Join -from .selectable import LABEL_STYLE_DEFAULT -from .selectable import LABEL_STYLE_DISAMBIGUATE_ONLY -from .selectable import LABEL_STYLE_NONE -from .selectable import LABEL_STYLE_TABLENAME_PLUS_COL -from .selectable import Lateral -from .selectable import ReturnsRows -from .selectable import ScalarSelect -from .selectable import Select -from .selectable import Selectable -from .selectable import SelectBase -from .selectable import Subquery -from .selectable import TableClause -from .selectable import TableSample -from .selectable import TableValuedAlias -from .selectable import TextAsFrom -from .selectable import TextualSelect -from .selectable import Values -from .visitors import Visitable -from ..util.langhelpers import public_factory - -# TODO: proposal is to remove public_factory and replace with traditional -# functions exported here. - -all_ = public_factory(CollectionAggregate._create_all, ".sql.expression.all_") -any_ = public_factory(CollectionAggregate._create_any, ".sql.expression.any_") -and_ = public_factory(BooleanClauseList.and_, ".sql.expression.and_") -alias = public_factory(Alias._factory, ".sql.expression.alias") -tablesample = public_factory( - TableSample._factory, ".sql.expression.tablesample" -) -lateral = public_factory(Lateral._factory, ".sql.expression.lateral") -or_ = public_factory(BooleanClauseList.or_, ".sql.expression.or_") -bindparam = public_factory(BindParameter, ".sql.expression.bindparam") -select = public_factory(Select._create, ".sql.expression.select") -text = public_factory(TextClause._create_text, ".sql.expression.text") -table = public_factory(TableClause, ".sql.expression.table") -column = public_factory(ColumnClause, ".sql.expression.column") -over = public_factory(Over, ".sql.expression.over") -within_group = public_factory(WithinGroup, ".sql.expression.within_group") -label = public_factory(Label, ".sql.expression.label") -case = public_factory(Case, ".sql.expression.case") -cast = public_factory(Cast, ".sql.expression.cast") -cte = public_factory(CTE._factory, ".sql.expression.cte") -values = public_factory(Values, ".sql.expression.values") -extract = public_factory(Extract, ".sql.expression.extract") -tuple_ = public_factory(Tuple, ".sql.expression.tuple_") -except_ = public_factory( - CompoundSelect._create_except, ".sql.expression.except_" -) -except_all = public_factory( - CompoundSelect._create_except_all, ".sql.expression.except_all" -) -intersect = public_factory( - CompoundSelect._create_intersect, ".sql.expression.intersect" -) -intersect_all = public_factory( - CompoundSelect._create_intersect_all, ".sql.expression.intersect_all" -) -union = public_factory(CompoundSelect._create_union, ".sql.expression.union") -union_all = public_factory( - CompoundSelect._create_union_all, ".sql.expression.union_all" -) -exists = public_factory(Exists, ".sql.expression.exists") -nulls_first = public_factory( - UnaryExpression._create_nulls_first, ".sql.expression.nulls_first" -) -nullsfirst = nulls_first # deprecated 1.4; see #5435 -nulls_last = public_factory( - UnaryExpression._create_nulls_last, ".sql.expression.nulls_last" -) -nullslast = nulls_last # deprecated 1.4; see #5435 -asc = public_factory(UnaryExpression._create_asc, ".sql.expression.asc") -desc = public_factory(UnaryExpression._create_desc, ".sql.expression.desc") -distinct = public_factory( - UnaryExpression._create_distinct, ".sql.expression.distinct" -) -type_coerce = public_factory(TypeCoerce, ".sql.expression.type_coerce") -true = public_factory(True_._instance, ".sql.expression.true") -false = public_factory(False_._instance, ".sql.expression.false") -null = public_factory(Null._instance, ".sql.expression.null") -join = public_factory(Join._create_join, ".sql.expression.join") -outerjoin = public_factory(Join._create_outerjoin, ".sql.expression.outerjoin") -insert = public_factory(Insert, ".sql.expression.insert") -update = public_factory(Update, ".sql.expression.update") -delete = public_factory(Delete, ".sql.expression.delete") -funcfilter = public_factory(FunctionFilter, ".sql.expression.funcfilter") - - -# internal functions still being called from tests and the ORM, -# these might be better off in some other namespace - - -# old names for compatibility -_Executable = Executable -_BindParamClause = BindParameter -_Label = Label -_SelectBase = SelectBase -_BinaryExpression = BinaryExpression -_Cast = Cast -_Null = Null -_False = False_ -_True = True_ -_TextClause = TextClause -_UnaryExpression = UnaryExpression -_Case = Case -_Tuple = Tuple -_Over = Over -_TypeClause = TypeClause -_Extract = Extract -_Exists = Exists -_Grouping = Grouping -_FromGrouping = FromGrouping -_ScalarSelect = ScalarSelect +nullsfirst = nulls_first +nullslast = nulls_last diff --git a/lib/sqlalchemy/sql/functions.py b/lib/sqlalchemy/sql/functions.py index 3b6da7175..7a1e80889 100644 --- a/lib/sqlalchemy/sql/functions.py +++ b/lib/sqlalchemy/sql/functions.py @@ -567,7 +567,7 @@ class FunctionElement(Executable, ColumnElement, FromClause, Generative): s = select(function_element) """ - s = Select._create(self) + s = Select(self) if self._execution_options: s = s.execution_options(**self._execution_options) return s diff --git a/lib/sqlalchemy/sql/operators.py b/lib/sqlalchemy/sql/operators.py index d7a5d9348..cf61f2637 100644 --- a/lib/sqlalchemy/sql/operators.py +++ b/lib/sqlalchemy/sql/operators.py @@ -10,32 +10,84 @@ """Defines operators used in SQL expressions.""" -from operator import add -from operator import and_ -from operator import contains -from operator import eq -from operator import floordiv -from operator import ge -from operator import getitem -from operator import gt -from operator import inv -from operator import le -from operator import lshift -from operator import lt -from operator import mod -from operator import mul -from operator import ne -from operator import neg -from operator import or_ -from operator import rshift -from operator import sub -from operator import truediv +from operator import add as _uncast_add +from operator import and_ as _uncast_and_ +from operator import contains as _uncast_contains +from operator import eq as _uncast_eq +from operator import floordiv as _uncast_floordiv +from operator import ge as _uncast_ge +from operator import getitem as _uncast_getitem +from operator import gt as _uncast_gt +from operator import inv as _uncast_inv +from operator import le as _uncast_le +from operator import lshift as _uncast_lshift +from operator import lt as _uncast_lt +from operator import mod as _uncast_mod +from operator import mul as _uncast_mul +from operator import ne as _uncast_ne +from operator import neg as _uncast_neg +from operator import or_ as _uncast_or_ +from operator import rshift as _uncast_rshift +from operator import sub as _uncast_sub +from operator import truediv as _uncast_truediv +import typing +from typing import Any +from typing import Callable +from typing import cast +from typing import Generic +from typing import Optional +from typing import overload +from typing import Type +from typing import TypeVar +from typing import Union from .. import exc from .. import util - - -class Operators: +from ..util.typing import Protocol + +if typing.TYPE_CHECKING: + from .elements import BinaryExpression + from .elements import ColumnElement + from .type_api import TypeEngine + +_OP_RETURN = TypeVar("_OP_RETURN", bound=Any, covariant=True) +_T = TypeVar("_T", bound=Any) + + +class OperatorType(Protocol): + """describe an op() function.""" + + __name__: str + + def __call__( + self, left: "Operators[_OP_RETURN]", *other: Any, **kwargs: Any + ) -> "_OP_RETURN": + ... + + +add = cast(OperatorType, _uncast_add) +and_ = cast(OperatorType, _uncast_and_) +contains = cast(OperatorType, _uncast_contains) +eq = cast(OperatorType, _uncast_eq) +floordiv = cast(OperatorType, _uncast_floordiv) +ge = cast(OperatorType, _uncast_ge) +getitem = cast(OperatorType, _uncast_getitem) +gt = cast(OperatorType, _uncast_gt) +inv = cast(OperatorType, _uncast_inv) +le = cast(OperatorType, _uncast_le) +lshift = cast(OperatorType, _uncast_lshift) +lt = cast(OperatorType, _uncast_lt) +mod = cast(OperatorType, _uncast_mod) +mul = cast(OperatorType, _uncast_mul) +ne = cast(OperatorType, _uncast_ne) +neg = cast(OperatorType, _uncast_neg) +or_ = cast(OperatorType, _uncast_or_) +rshift = cast(OperatorType, _uncast_rshift) +sub = cast(OperatorType, _uncast_sub) +truediv = cast(OperatorType, _uncast_truediv) + + +class Operators(Generic[_OP_RETURN]): """Base of comparison and logical operators. Implements base methods @@ -52,7 +104,7 @@ class Operators: __slots__ = () - def __and__(self, other): + def __and__(self, other: Any) -> "Operators": """Implement the ``&`` operator. When used with SQL expressions, results in an @@ -76,7 +128,7 @@ class Operators: """ return self.operate(and_, other) - def __or__(self, other): + def __or__(self, other: Any) -> "Operators": """Implement the ``|`` operator. When used with SQL expressions, results in an @@ -100,7 +152,7 @@ class Operators: """ return self.operate(or_, other) - def __invert__(self): + def __invert__(self) -> "Operators": """Implement the ``~`` operator. When used with SQL expressions, results in a @@ -119,12 +171,14 @@ class Operators: def op( self, - opstring, - precedence=0, - is_comparison=False, - return_type=None, + opstring: Any, + precedence: int = 0, + is_comparison: bool = False, + return_type: Optional[ + Union[Type["TypeEngine[_T]"], "TypeEngine[_T]"] + ] = None, python_impl=None, - ): + ) -> Callable[[Any], _OP_RETURN]: """Produce a generic operator function. e.g.:: @@ -205,12 +259,14 @@ class Operators: python_impl=python_impl, ) - def against(other): + def against(other: Any) -> _OP_RETURN: return operator(self, other) return against - def bool_op(self, opstring, precedence=0, python_impl=None): + def bool_op( + self, opstring: Any, precedence: int = 0, python_impl=None + ) -> Callable[[Any], _OP_RETURN]: """Return a custom boolean operator. This method is shorthand for calling @@ -230,7 +286,9 @@ class Operators: python_impl=python_impl, ) - def operate(self, op, *other, **kwargs): + def operate( + self, op: OperatorType, *other: Any, **kwargs: Any + ) -> _OP_RETURN: r"""Operate on an argument. This is the lowest level of operation, raises @@ -258,7 +316,9 @@ class Operators: __sa_operate__ = operate - def reverse_operate(self, op, other, **kwargs): + def reverse_operate( + self, op: OperatorType, other: Any, **kwargs: Any + ) -> _OP_RETURN: """Reverse operate on an argument. Usage is the same as :meth:`operate`. @@ -267,7 +327,7 @@ class Operators: raise NotImplementedError(str(op)) -class custom_op: +class custom_op(OperatorType, Generic[_T]): """Represent a 'custom' operator. :class:`.custom_op` is normally instantiated when the @@ -307,12 +367,14 @@ class custom_op: def __init__( self, - opstring, - precedence=0, - is_comparison=False, - return_type=None, - natural_self_precedent=False, - eager_grouping=False, + opstring: str, + precedence: int = 0, + is_comparison: bool = False, + return_type: Optional[ + Union[Type["TypeEngine[_T]"], "TypeEngine[_T]"] + ] = None, + natural_self_precedent: bool = False, + eager_grouping: bool = False, python_impl=None, ): self.opstring = opstring @@ -325,13 +387,27 @@ class custom_op: ) self.python_impl = python_impl - def __eq__(self, other): + def __eq__(self, other: Any) -> bool: return isinstance(other, custom_op) and other.opstring == self.opstring - def __hash__(self): + def __hash__(self) -> int: return id(self) - def __call__(self, left, right, **kw): + @overload + def __call__( + self, left: "ColumnElement", right: Any, **kw + ) -> "BinaryExpression[_T]": + ... + + @overload + def __call__( + self, left: "Operators[_OP_RETURN]", right: Any, **kw + ) -> _OP_RETURN: + ... + + def __call__( + self, left: "Operators[_OP_RETURN]", right: Any, **kw + ) -> _OP_RETURN: if hasattr(left, "__sa_operate__"): return left.operate(self, right, **kw) elif self.python_impl: @@ -344,7 +420,7 @@ class custom_op: ) -class ColumnOperators(Operators): +class ColumnOperators(Operators[_OP_RETURN]): """Defines boolean, comparison, and other operators for :class:`_expression.ColumnElement` expressions. @@ -387,7 +463,19 @@ class ColumnOperators(Operators): timetuple = None """Hack, allows datetime objects to be compared on the LHS.""" - def __lt__(self, other): + if typing.TYPE_CHECKING: + + def operate( + self, op: OperatorType, *other: Any, **kwargs: Any + ) -> "ColumnOperators": + ... + + def reverse_operate( + self, op: OperatorType, other: Any, **kwargs: Any + ) -> "ColumnOperators": + ... + + def __lt__(self, other: Any) -> "ColumnOperators": """Implement the ``<`` operator. In a column context, produces the clause ``a < b``. @@ -395,7 +483,7 @@ class ColumnOperators(Operators): """ return self.operate(lt, other) - def __le__(self, other): + def __le__(self, other: Any) -> "ColumnOperators": """Implement the ``<=`` operator. In a column context, produces the clause ``a <= b``. @@ -403,9 +491,10 @@ class ColumnOperators(Operators): """ return self.operate(le, other) - __hash__ = Operators.__hash__ + # TODO: not sure why we have this + __hash__ = Operators.__hash__ # type: ignore - def __eq__(self, other): + def __eq__(self, other: Any) -> "ColumnOperators": """Implement the ``==`` operator. In a column context, produces the clause ``a = b``. @@ -414,7 +503,7 @@ class ColumnOperators(Operators): """ return self.operate(eq, other) - def __ne__(self, other): + def __ne__(self, other: Any) -> "ColumnOperators": """Implement the ``!=`` operator. In a column context, produces the clause ``a != b``. @@ -423,7 +512,7 @@ class ColumnOperators(Operators): """ return self.operate(ne, other) - def is_distinct_from(self, other): + def is_distinct_from(self, other: Any) -> "ColumnOperators": """Implement the ``IS DISTINCT FROM`` operator. Renders "a IS DISTINCT FROM b" on most platforms; @@ -434,7 +523,7 @@ class ColumnOperators(Operators): """ return self.operate(is_distinct_from, other) - def is_not_distinct_from(self, other): + def is_not_distinct_from(self, other: Any) -> "ColumnOperators": """Implement the ``IS NOT DISTINCT FROM`` operator. Renders "a IS NOT DISTINCT FROM b" on most platforms; @@ -452,7 +541,7 @@ class ColumnOperators(Operators): # deprecated 1.4; see #5435 isnot_distinct_from = is_not_distinct_from - def __gt__(self, other): + def __gt__(self, other: Any) -> "ColumnOperators": """Implement the ``>`` operator. In a column context, produces the clause ``a > b``. @@ -460,7 +549,7 @@ class ColumnOperators(Operators): """ return self.operate(gt, other) - def __ge__(self, other): + def __ge__(self, other: Any) -> "ColumnOperators": """Implement the ``>=`` operator. In a column context, produces the clause ``a >= b``. @@ -468,7 +557,7 @@ class ColumnOperators(Operators): """ return self.operate(ge, other) - def __neg__(self): + def __neg__(self) -> "ColumnOperators": """Implement the ``-`` operator. In a column context, produces the clause ``-a``. @@ -476,10 +565,10 @@ class ColumnOperators(Operators): """ return self.operate(neg) - def __contains__(self, other): + def __contains__(self, other: Any) -> "ColumnOperators": return self.operate(contains, other) - def __getitem__(self, index): + def __getitem__(self, index: Any) -> "ColumnOperators": """Implement the [] operator. This can be used by some database-specific types @@ -488,7 +577,7 @@ class ColumnOperators(Operators): """ return self.operate(getitem, index) - def __lshift__(self, other): + def __lshift__(self, other: Any) -> "ColumnOperators": """implement the << operator. Not used by SQLAlchemy core, this is provided @@ -497,7 +586,7 @@ class ColumnOperators(Operators): """ return self.operate(lshift, other) - def __rshift__(self, other): + def __rshift__(self, other: Any) -> "ColumnOperators": """implement the >> operator. Not used by SQLAlchemy core, this is provided @@ -506,7 +595,7 @@ class ColumnOperators(Operators): """ return self.operate(rshift, other) - def concat(self, other): + def concat(self, other: Any) -> "ColumnOperators": """Implement the 'concat' operator. In a column context, produces the clause ``a || b``, @@ -515,7 +604,7 @@ class ColumnOperators(Operators): """ return self.operate(concat_op, other) - def like(self, other, escape=None): + def like(self, other: Any, escape=None) -> "ColumnOperators": r"""Implement the ``like`` operator. In a column context, produces the expression:: @@ -540,7 +629,7 @@ class ColumnOperators(Operators): """ return self.operate(like_op, other, escape=escape) - def ilike(self, other, escape=None): + def ilike(self, other: Any, escape=None) -> "ColumnOperators": r"""Implement the ``ilike`` operator, e.g. case insensitive LIKE. In a column context, produces an expression either of the form:: @@ -569,7 +658,7 @@ class ColumnOperators(Operators): """ return self.operate(ilike_op, other, escape=escape) - def in_(self, other): + def in_(self, other: Any) -> "ColumnOperators": """Implement the ``in`` operator. In a column context, produces the clause ``column IN <other>``. @@ -658,7 +747,7 @@ class ColumnOperators(Operators): """ return self.operate(in_op, other) - def not_in(self, other): + def not_in(self, other: Any) -> "ColumnOperators": """implement the ``NOT IN`` operator. This is equivalent to using negation with @@ -689,7 +778,7 @@ class ColumnOperators(Operators): # deprecated 1.4; see #5429 notin_ = not_in - def not_like(self, other, escape=None): + def not_like(self, other: Any, escape=None) -> "ColumnOperators": """implement the ``NOT LIKE`` operator. This is equivalent to using negation with @@ -709,7 +798,7 @@ class ColumnOperators(Operators): # deprecated 1.4; see #5435 notlike = not_like - def not_ilike(self, other, escape=None): + def not_ilike(self, other: Any, escape=None) -> "ColumnOperators": """implement the ``NOT ILIKE`` operator. This is equivalent to using negation with @@ -729,7 +818,7 @@ class ColumnOperators(Operators): # deprecated 1.4; see #5435 notilike = not_ilike - def is_(self, other): + def is_(self, other: Any) -> "ColumnOperators": """Implement the ``IS`` operator. Normally, ``IS`` is generated automatically when comparing to a @@ -742,7 +831,7 @@ class ColumnOperators(Operators): """ return self.operate(is_, other) - def is_not(self, other): + def is_not(self, other: Any) -> "ColumnOperators": """Implement the ``IS NOT`` operator. Normally, ``IS NOT`` is generated automatically when comparing to a @@ -762,7 +851,9 @@ class ColumnOperators(Operators): # deprecated 1.4; see #5429 isnot = is_not - def startswith(self, other, **kwargs): + def startswith( + self, other: Any, escape=None, autoescape=False + ) -> "ColumnOperators": r"""Implement the ``startswith`` operator. Produces a LIKE expression that tests against a match for the start @@ -839,9 +930,13 @@ class ColumnOperators(Operators): :meth:`.ColumnOperators.like` """ - return self.operate(startswith_op, other, **kwargs) + return self.operate( + startswith_op, other, escape=escape, autoescape=autoescape + ) - def endswith(self, other, **kwargs): + def endswith( + self, other: Any, escape=None, autoescape=False + ) -> "ColumnOperators": r"""Implement the 'endswith' operator. Produces a LIKE expression that tests against a match for the end @@ -918,9 +1013,13 @@ class ColumnOperators(Operators): :meth:`.ColumnOperators.like` """ - return self.operate(endswith_op, other, **kwargs) + return self.operate( + endswith_op, other, escape=escape, autoescape=autoescape + ) - def contains(self, other, **kwargs): + def contains( + self, other: Any, escape=None, autoescape=False + ) -> "ColumnOperators": r"""Implement the 'contains' operator. Produces a LIKE expression that tests against a match for the middle @@ -998,9 +1097,11 @@ class ColumnOperators(Operators): """ - return self.operate(contains_op, other, **kwargs) + return self.operate( + contains_op, other, escape=escape, autoescape=autoescape + ) - def match(self, other, **kwargs): + def match(self, other: Any, **kwargs) -> "ColumnOperators": """Implements a database-specific 'match' operator. :meth:`_sql.ColumnOperators.match` attempts to resolve to @@ -1024,7 +1125,7 @@ class ColumnOperators(Operators): """ return self.operate(match_op, other, **kwargs) - def regexp_match(self, pattern, flags=None): + def regexp_match(self, pattern, flags=None) -> "ColumnOperators": """Implements a database-specific 'regexp match' operator. E.g.:: @@ -1072,7 +1173,9 @@ class ColumnOperators(Operators): """ return self.operate(regexp_match_op, pattern, flags=flags) - def regexp_replace(self, pattern, replacement, flags=None): + def regexp_replace( + self, pattern, replacement, flags=None + ) -> "ColumnOperators": """Implements a database-specific 'regexp replace' operator. E.g.:: @@ -1111,20 +1214,23 @@ class ColumnOperators(Operators): """ return self.operate( - regexp_replace_op, pattern, replacement=replacement, flags=flags + regexp_replace_op, + pattern, + replacement=replacement, + flags=flags, ) - def desc(self): + def desc(self) -> "ColumnOperators": """Produce a :func:`_expression.desc` clause against the parent object.""" return self.operate(desc_op) - def asc(self): + def asc(self) -> "ColumnOperators": """Produce a :func:`_expression.asc` clause against the parent object.""" return self.operate(asc_op) - def nulls_first(self): + def nulls_first(self) -> "ColumnOperators": """Produce a :func:`_expression.nulls_first` clause against the parent object. @@ -1137,7 +1243,7 @@ class ColumnOperators(Operators): # deprecated 1.4; see #5435 nullsfirst = nulls_first - def nulls_last(self): + def nulls_last(self) -> "ColumnOperators": """Produce a :func:`_expression.nulls_last` clause against the parent object. @@ -1150,7 +1256,7 @@ class ColumnOperators(Operators): # deprecated 1.4; see #5429 nullslast = nulls_last - def collate(self, collation): + def collate(self, collation) -> "ColumnOperators": """Produce a :func:`_expression.collate` clause against the parent object, given the collation string. @@ -1161,7 +1267,7 @@ class ColumnOperators(Operators): """ return self.operate(collate, collation) - def __radd__(self, other): + def __radd__(self, other: Any) -> "ColumnOperators": """Implement the ``+`` operator in reverse. See :meth:`.ColumnOperators.__add__`. @@ -1169,7 +1275,7 @@ class ColumnOperators(Operators): """ return self.reverse_operate(add, other) - def __rsub__(self, other): + def __rsub__(self, other: Any) -> "ColumnOperators": """Implement the ``-`` operator in reverse. See :meth:`.ColumnOperators.__sub__`. @@ -1177,7 +1283,7 @@ class ColumnOperators(Operators): """ return self.reverse_operate(sub, other) - def __rmul__(self, other): + def __rmul__(self, other: Any) -> "ColumnOperators": """Implement the ``*`` operator in reverse. See :meth:`.ColumnOperators.__mul__`. @@ -1185,7 +1291,7 @@ class ColumnOperators(Operators): """ return self.reverse_operate(mul, other) - def __rmod__(self, other): + def __rmod__(self, other: Any) -> "ColumnOperators": """Implement the ``%`` operator in reverse. See :meth:`.ColumnOperators.__mod__`. @@ -1193,21 +1299,21 @@ class ColumnOperators(Operators): """ return self.reverse_operate(mod, other) - def between(self, cleft, cright, symmetric=False): + def between(self, cleft, cright, symmetric=False) -> "ColumnOperators": """Produce a :func:`_expression.between` clause against the parent object, given the lower and upper range. """ return self.operate(between_op, cleft, cright, symmetric=symmetric) - def distinct(self): + def distinct(self) -> "ColumnOperators": """Produce a :func:`_expression.distinct` clause against the parent object. """ return self.operate(distinct_op) - def any_(self): + def any_(self) -> "ColumnOperators": """Produce an :func:`_expression.any_` clause against the parent object. @@ -1224,7 +1330,7 @@ class ColumnOperators(Operators): """ return self.operate(any_op) - def all_(self): + def all_(self) -> "ColumnOperators": """Produce an :func:`_expression.all_` clause against the parent object. @@ -1242,7 +1348,7 @@ class ColumnOperators(Operators): """ return self.operate(all_op) - def __add__(self, other): + def __add__(self, other: Any) -> "ColumnOperators": """Implement the ``+`` operator. In a column context, produces the clause ``a + b`` @@ -1254,7 +1360,7 @@ class ColumnOperators(Operators): """ return self.operate(add, other) - def __sub__(self, other): + def __sub__(self, other: Any) -> "ColumnOperators": """Implement the ``-`` operator. In a column context, produces the clause ``a - b``. @@ -1262,7 +1368,7 @@ class ColumnOperators(Operators): """ return self.operate(sub, other) - def __mul__(self, other): + def __mul__(self, other: Any) -> "ColumnOperators": """Implement the ``*`` operator. In a column context, produces the clause ``a * b``. @@ -1270,7 +1376,7 @@ class ColumnOperators(Operators): """ return self.operate(mul, other) - def __mod__(self, other): + def __mod__(self, other: Any) -> "ColumnOperators": """Implement the ``%`` operator. In a column context, produces the clause ``a % b``. @@ -1278,7 +1384,7 @@ class ColumnOperators(Operators): """ return self.operate(mod, other) - def __truediv__(self, other): + def __truediv__(self, other: Any) -> "ColumnOperators": """Implement the ``/`` operator. In a column context, produces the clause ``a / b``, and @@ -1291,7 +1397,7 @@ class ColumnOperators(Operators): """ return self.operate(truediv, other) - def __rtruediv__(self, other): + def __rtruediv__(self, other: Any) -> "ColumnOperators": """Implement the ``/`` operator in reverse. See :meth:`.ColumnOperators.__truediv__`. @@ -1299,7 +1405,7 @@ class ColumnOperators(Operators): """ return self.reverse_operate(truediv, other) - def __floordiv__(self, other): + def __floordiv__(self, other: Any) -> "ColumnOperators": """Implement the ``//`` operator. In a column context, produces the clause ``a / b``, @@ -1311,7 +1417,7 @@ class ColumnOperators(Operators): """ return self.operate(floordiv, other) - def __rfloordiv__(self, other): + def __rfloordiv__(self, other: Any) -> "ColumnOperators": """Implement the ``//`` operator in reverse. See :meth:`.ColumnOperators.__floordiv__`. @@ -1324,6 +1430,10 @@ _commutative = {eq, ne, add, mul} _comparison = {eq, ne, lt, gt, ge, le} +def _operator_fn(fn): + return cast(OperatorType, fn) + + def commutative_op(fn): _commutative.add(fn) return fn @@ -1351,6 +1461,7 @@ def exists(): raise NotImplementedError() +@_operator_fn def is_true(a): raise NotImplementedError() @@ -1359,6 +1470,7 @@ def is_true(a): istrue = is_true +@_operator_fn def is_false(a): raise NotImplementedError() @@ -1368,11 +1480,13 @@ isfalse = is_false @comparison_op +@_operator_fn def is_distinct_from(a, b): return a.is_distinct_from(b) @comparison_op +@_operator_fn def is_not_distinct_from(a, b): return a.is_not_distinct_from(b) @@ -1382,11 +1496,13 @@ isnot_distinct_from = is_not_distinct_from @comparison_op +@_operator_fn def is_(a, b): return a.is_(b) @comparison_op +@_operator_fn def is_not(a, b): return a.is_not(b) @@ -1395,20 +1511,24 @@ def is_not(a, b): isnot = is_not +@_operator_fn def collate(a, b): return a.collate(b) +@_operator_fn def op(a, opstring, b): return a.op(opstring)(b) @comparison_op +@_operator_fn def like_op(a, b, escape=None): return a.like(b, escape=escape) @comparison_op +@_operator_fn def not_like_op(a, b, escape=None): return a.notlike(b, escape=escape) @@ -1418,11 +1538,13 @@ notlike_op = not_like_op @comparison_op +@_operator_fn def ilike_op(a, b, escape=None): return a.ilike(b, escape=escape) @comparison_op +@_operator_fn def not_ilike_op(a, b, escape=None): return a.not_ilike(b, escape=escape) @@ -1432,11 +1554,13 @@ notilike_op = not_ilike_op @comparison_op +@_operator_fn def between_op(a, b, c, symmetric=False): return a.between(b, c, symmetric=symmetric) @comparison_op +@_operator_fn def not_between_op(a, b, c, symmetric=False): return ~a.between(b, c, symmetric=symmetric) @@ -1446,11 +1570,13 @@ notbetween_op = not_between_op @comparison_op +@_operator_fn def in_op(a, b): return a.in_(b) @comparison_op +@_operator_fn def not_in_op(a, b): return a.not_in(b) @@ -1459,19 +1585,22 @@ def not_in_op(a, b): notin_op = not_in_op +@_operator_fn def distinct_op(a): return a.distinct() +@_operator_fn def any_op(a): return a.any_() +@_operator_fn def all_op(a): return a.all_() -def _escaped_like_impl(fn, other, escape, autoescape): +def _escaped_like_impl(fn, other: Any, escape, autoescape): if autoescape: if autoescape is not True: util.warn( @@ -1492,11 +1621,13 @@ def _escaped_like_impl(fn, other, escape, autoescape): @comparison_op +@_operator_fn def startswith_op(a, b, escape=None, autoescape=False): return _escaped_like_impl(a.startswith, b, escape, autoescape) @comparison_op +@_operator_fn def not_startswith_op(a, b, escape=None, autoescape=False): return ~_escaped_like_impl(a.startswith, b, escape, autoescape) @@ -1506,11 +1637,13 @@ notstartswith_op = not_startswith_op @comparison_op +@_operator_fn def endswith_op(a, b, escape=None, autoescape=False): return _escaped_like_impl(a.endswith, b, escape, autoescape) @comparison_op +@_operator_fn def not_endswith_op(a, b, escape=None, autoescape=False): return ~_escaped_like_impl(a.endswith, b, escape, autoescape) @@ -1520,11 +1653,13 @@ notendswith_op = not_endswith_op @comparison_op +@_operator_fn def contains_op(a, b, escape=None, autoescape=False): return _escaped_like_impl(a.contains, b, escape, autoescape) @comparison_op +@_operator_fn def not_contains_op(a, b, escape=None, autoescape=False): return ~_escaped_like_impl(a.contains, b, escape, autoescape) @@ -1534,25 +1669,30 @@ notcontains_op = not_contains_op @comparison_op +@_operator_fn def match_op(a, b, **kw): return a.match(b, **kw) @comparison_op +@_operator_fn def regexp_match_op(a, b, flags=None): return a.regexp_match(b, flags=flags) @comparison_op +@_operator_fn def not_regexp_match_op(a, b, flags=None): return ~a.regexp_match(b, flags=flags) +@_operator_fn def regexp_replace_op(a, b, replacement, flags=None): return a.regexp_replace(b, replacement=replacement, flags=flags) @comparison_op +@_operator_fn def not_match_op(a, b, **kw): return ~a.match(b, **kw) @@ -1561,26 +1701,32 @@ def not_match_op(a, b, **kw): notmatch_op = not_match_op +@_operator_fn def comma_op(a, b): raise NotImplementedError() +@_operator_fn def filter_op(a, b): raise NotImplementedError() +@_operator_fn def concat_op(a, b): return a.concat(b) +@_operator_fn def desc_op(a): return a.desc() +@_operator_fn def asc_op(a): return a.asc() +@_operator_fn def nulls_first_op(a): return a.nulls_first() @@ -1589,6 +1735,7 @@ def nulls_first_op(a): nullsfirst_op = nulls_first_op +@_operator_fn def nulls_last_op(a): return a.nulls_last() @@ -1597,10 +1744,12 @@ def nulls_last_op(a): nullslast_op = nulls_last_op +@_operator_fn def json_getitem_op(a, b): raise NotImplementedError() +@_operator_fn def json_path_getitem_op(a, b): raise NotImplementedError() diff --git a/lib/sqlalchemy/sql/roles.py b/lib/sqlalchemy/sql/roles.py index 9172c2dc9..787a1c25e 100644 --- a/lib/sqlalchemy/sql/roles.py +++ b/lib/sqlalchemy/sql/roles.py @@ -71,7 +71,13 @@ class ColumnListRole(SQLRole): __slots__ = () -class TruncatedLabelRole(SQLRole): +class StringRole(SQLRole): + """mixin indicating a role that results in strings""" + + __slots__ = () + + +class TruncatedLabelRole(StringRole, SQLRole): __slots__ = () _role_name = "String SQL identifier" diff --git a/lib/sqlalchemy/sql/schema.py b/lib/sqlalchemy/sql/schema.py index 885cb2754..938d2c34a 100644 --- a/lib/sqlalchemy/sql/schema.py +++ b/lib/sqlalchemy/sql/schema.py @@ -29,6 +29,14 @@ as components in SQL expressions. """ import collections +import typing +from typing import Any +from typing import MutableMapping +from typing import Optional +from typing import overload +from typing import Type +from typing import TypeVar +from typing import Union from . import coercions from . import ddl @@ -52,7 +60,13 @@ from .. import event from .. import exc from .. import inspection from .. import util +from ..util.typing import Literal +if typing.TYPE_CHECKING: + from .type_api import TypeEngine + +_T = TypeVar("_T", bound="Any") +_ServerDefaultType = Union["FetchedValue", str, TextClause, ColumnElement] RETAIN_SCHEMA = util.symbol("retain_schema") @@ -1086,13 +1100,109 @@ class Table(DialectKWArgs, SchemaItem, TableClause): return self._schema_item_copy(table) -class Column(DialectKWArgs, SchemaItem, ColumnClause): +class Column(DialectKWArgs, SchemaItem, ColumnClause[_T]): """Represents a column in a database table.""" __visit_name__ = "column" inherit_cache = True + @overload + def __init__( + self: "Column[None]", + __name: str, + *args: SchemaEventTarget, + autoincrement: Union[bool, Literal["auto", "ignore_fk"]] = ..., + default: Optional[Any] = ..., + doc: Optional[str] = ..., + key: Optional[str] = ..., + index: Optional[bool] = ..., + info: MutableMapping[Any, Any] = ..., + nullable: bool = ..., + onupdate: Optional[Any] = ..., + primary_key: bool = ..., + server_default: Optional[_ServerDefaultType] = ..., + server_onupdate: Optional["FetchedValue"] = ..., + quote: Optional[bool] = ..., + unique: Optional[bool] = ..., + system: bool = ..., + comment: Optional[str] = ..., + **kwargs: Any, + ) -> None: + ... + + @overload + def __init__( + self: "Column[None]", + *args: SchemaEventTarget, + autoincrement: Union[bool, Literal["auto", "ignore_fk"]] = ..., + default: Optional[Any] = ..., + doc: Optional[str] = ..., + key: Optional[str] = ..., + index: Optional[bool] = ..., + info: MutableMapping[Any, Any] = ..., + nullable: bool = ..., + onupdate: Optional[Any] = ..., + primary_key: bool = ..., + server_default: Optional[_ServerDefaultType] = ..., + server_onupdate: Optional["FetchedValue"] = ..., + quote: Optional[bool] = ..., + unique: Optional[bool] = ..., + system: bool = ..., + comment: Optional[str] = ..., + **kwargs: Any, + ) -> None: + ... + + @overload + def __init__( + self, + __name: str, + __type: Union[Type["TypeEngine[_T]"], "TypeEngine[_T]"], + *args: SchemaEventTarget, + autoincrement: Union[bool, Literal["auto", "ignore_fk"]] = ..., + default: Optional[Any] = ..., + doc: Optional[str] = ..., + key: Optional[str] = ..., + index: Optional[bool] = ..., + info: MutableMapping[Any, Any] = ..., + nullable: bool = ..., + onupdate: Optional[Any] = ..., + primary_key: bool = ..., + server_default: Optional[_ServerDefaultType] = ..., + server_onupdate: Optional["FetchedValue"] = ..., + quote: Optional[bool] = ..., + unique: Optional[bool] = ..., + system: bool = ..., + comment: Optional[str] = ..., + **kwargs: Any, + ) -> None: + ... + + @overload + def __init__( + self, + __type: Union[Type["TypeEngine[_T]"], "TypeEngine[_T]"], + *args: SchemaEventTarget, + autoincrement: Union[bool, Literal["auto", "ignore_fk"]] = ..., + default: Optional[Any] = ..., + doc: Optional[str] = ..., + key: Optional[str] = ..., + index: Optional[bool] = ..., + info: MutableMapping[Any, Any] = ..., + nullable: bool = ..., + onupdate: Optional[Any] = ..., + primary_key: bool = ..., + server_default: Optional[_ServerDefaultType] = ..., + server_onupdate: Optional["FetchedValue"] = ..., + quote: Optional[bool] = ..., + unique: Optional[bool] = ..., + system: bool = ..., + comment: Optional[str] = ..., + **kwargs: Any, + ) -> None: + ... + def __init__(self, *args, **kwargs): r""" Construct a new ``Column`` object. diff --git a/lib/sqlalchemy/sql/selectable.py b/lib/sqlalchemy/sql/selectable.py index e674c4b74..fd1abd71b 100644 --- a/lib/sqlalchemy/sql/selectable.py +++ b/lib/sqlalchemy/sql/selectable.py @@ -48,7 +48,6 @@ from .base import Immutable from .base import prefix_anon_map from .coercions import _document_text_coercion from .elements import _anonymous_label -from .elements import and_ from .elements import BindParameter from .elements import BooleanClauseList from .elements import ClauseElement @@ -64,6 +63,9 @@ from .. import exc from .. import util +and_ = BooleanClauseList.and_ + + class _OffsetLimitParam(BindParameter): inherit_cache = True @@ -472,7 +474,7 @@ class FromClause(roles.AnonymizedFromClauseRole, Selectable): method which allows for arbitrary column lists. """ - return Select._create(self) + return Select(self) def join(self, right, onclause=None, isouter=False, full=False): """Return a :class:`_expression.Join` from this @@ -990,86 +992,6 @@ class Join(roles.DMLTableRole, FromClause): self.isouter = isouter self.full = full - @classmethod - def _create_outerjoin(cls, left, right, onclause=None, full=False): - """Return an ``OUTER JOIN`` clause element. - - The returned object is an instance of :class:`_expression.Join`. - - Similar functionality is also available via the - :meth:`_expression.FromClause.outerjoin` method on any - :class:`_expression.FromClause`. - - :param left: The left side of the join. - - :param right: The right side of the join. - - :param onclause: Optional criterion for the ``ON`` clause, is - derived from foreign key relationships established between - left and right otherwise. - - To chain joins together, use the :meth:`_expression.FromClause.join` - or - :meth:`_expression.FromClause.outerjoin` methods on the resulting - :class:`_expression.Join` object. - - """ - return cls(left, right, onclause, isouter=True, full=full) - - @classmethod - def _create_join( - cls, left, right, onclause=None, isouter=False, full=False - ): - """Produce a :class:`_expression.Join` object, given two - :class:`_expression.FromClause` - expressions. - - E.g.:: - - j = join(user_table, address_table, - user_table.c.id == address_table.c.user_id) - stmt = select(user_table).select_from(j) - - would emit SQL along the lines of:: - - SELECT user.id, user.name FROM user - JOIN address ON user.id = address.user_id - - Similar functionality is available given any - :class:`_expression.FromClause` object (e.g. such as a - :class:`_schema.Table`) using - the :meth:`_expression.FromClause.join` method. - - :param left: The left side of the join. - - :param right: the right side of the join; this is any - :class:`_expression.FromClause` object such as a - :class:`_schema.Table` object, and - may also be a selectable-compatible object such as an ORM-mapped - class. - - :param onclause: a SQL expression representing the ON clause of the - join. If left at ``None``, :meth:`_expression.FromClause.join` - will attempt to - join the two tables based on a foreign key relationship. - - :param isouter: if True, render a LEFT OUTER JOIN, instead of JOIN. - - :param full: if True, render a FULL OUTER JOIN, instead of JOIN. - - .. versionadded:: 1.1 - - .. seealso:: - - :meth:`_expression.FromClause.join` - method form, - based on a given left side. - - :class:`_expression.Join` - the type of object produced. - - """ - - return cls(left, right, onclause, isouter, full) - @property def description(self): return "Join object on %s(%d) and %s(%d)" % ( @@ -1161,24 +1083,7 @@ class Join(roles.DMLTableRole, FromClause): ): """Create a join condition between two tables or selectables. - e.g.:: - - join_condition(tablea, tableb) - - would produce an expression along the lines of:: - - tablea.c.id==tableb.c.tablea_id - - The join is determined based on the foreign key relationships - between the two selectables. If there are multiple ways - to join, or no way to join, an error is raised. - - :param a_subset: An optional expression that is a sub-component - of ``a``. An attempt will be made to join to just this sub-component - first before looking at the full ``a`` construct, and if found - will be successful even if there are other ways to join to ``a``. - This allows the "right side" of a join to be passed thereby - providing a "natural join". + See sqlalchemy.sql.util.join_condition() for full docs. """ constraints = cls._joincond_scan_left_right( @@ -1331,7 +1236,7 @@ class Join(roles.DMLTableRole, FromClause): FROM table_a JOIN table_b ON table_a.id = table_b.a_id """ - return Select._create(self.left, self.right).select_from(self) + return Select(self.left, self.right).select_from(self) @util.preload_module("sqlalchemy.sql.util") def _anonymous_fromclause(self, name=None, flat=False): @@ -1503,51 +1408,6 @@ class Alias(roles.DMLTableRole, AliasedReturnsRows): @classmethod def _factory(cls, selectable, name=None, flat=False): - """Return an :class:`_expression.Alias` object. - - An :class:`_expression.Alias` represents any - :class:`_expression.FromClause` - with an alternate name assigned within SQL, typically using the ``AS`` - clause when generated, e.g. ``SELECT * FROM table AS aliasname``. - - Similar functionality is available via the - :meth:`_expression.FromClause.alias` - method available on all :class:`_expression.FromClause` subclasses. - In terms of - a SELECT object as generated from the :func:`_expression.select` - function, the :meth:`_expression.SelectBase.alias` method returns an - :class:`_expression.Alias` or similar object which represents a named, - parenthesized subquery. - - When an :class:`_expression.Alias` is created from a - :class:`_schema.Table` object, - this has the effect of the table being rendered - as ``tablename AS aliasname`` in a SELECT statement. - - For :func:`_expression.select` objects, the effect is that of - creating a named subquery, i.e. ``(select ...) AS aliasname``. - - The ``name`` parameter is optional, and provides the name - to use in the rendered SQL. If blank, an "anonymous" name - will be deterministically generated at compile time. - Deterministic means the name is guaranteed to be unique against - other constructs used in the same statement, and will also be the - same name for each successive compilation of the same statement - object. - - :param selectable: any :class:`_expression.FromClause` subclass, - such as a table, select statement, etc. - - :param name: string name to be assigned as the alias. - If ``None``, a name will be deterministically generated - at compile time. - - :param flat: Will be passed through to if the given selectable - is an instance of :class:`_expression.Join` - see - :meth:`_expression.Join.alias` - for details. - - """ return coercions.expect( roles.FromClauseRole, selectable, allow_select=True ).alias(name=name, flat=flat) @@ -1724,25 +1584,6 @@ class Lateral(AliasedReturnsRows): @classmethod def _factory(cls, selectable, name=None): - """Return a :class:`_expression.Lateral` object. - - :class:`_expression.Lateral` is an :class:`_expression.Alias` - subclass that represents - a subquery with the LATERAL keyword applied to it. - - The special behavior of a LATERAL subquery is that it appears in the - FROM clause of an enclosing SELECT, but may correlate to other - FROM clauses of that SELECT. It is a special case of subquery - only supported by a small number of backends, currently more recent - PostgreSQL versions. - - .. versionadded:: 1.1 - - .. seealso:: - - :ref:`lateral_selects` - overview of usage. - - """ return coercions.expect( roles.FromClauseRole, selectable, explicit_subquery=True ).lateral(name=name) @@ -1773,48 +1614,6 @@ class TableSample(AliasedReturnsRows): @classmethod def _factory(cls, selectable, sampling, name=None, seed=None): - """Return a :class:`_expression.TableSample` object. - - :class:`_expression.TableSample` is an :class:`_expression.Alias` - subclass that represents - a table with the TABLESAMPLE clause applied to it. - :func:`_expression.tablesample` - is also available from the :class:`_expression.FromClause` - class via the - :meth:`_expression.FromClause.tablesample` method. - - The TABLESAMPLE clause allows selecting a randomly selected approximate - percentage of rows from a table. It supports multiple sampling methods, - most commonly BERNOULLI and SYSTEM. - - e.g.:: - - from sqlalchemy import func - - selectable = people.tablesample( - func.bernoulli(1), - name='alias', - seed=func.random()) - stmt = select(selectable.c.people_id) - - Assuming ``people`` with a column ``people_id``, the above - statement would render as:: - - SELECT alias.people_id FROM - people AS alias TABLESAMPLE bernoulli(:bernoulli_1) - REPEATABLE (random()) - - .. versionadded:: 1.1 - - :param sampling: a ``float`` percentage between 0 and 100 or - :class:`_functions.Function`. - - :param name: optional alias name - - :param seed: any real-valued SQL expression. When specified, the - REPEATABLE sub-clause is also rendered. - - """ return coercions.expect(roles.FromClauseRole, selectable).tablesample( sampling, name=name, seed=seed ) @@ -2493,28 +2292,6 @@ class TableClause(roles.DMLTableRole, Immutable, FromClause): """No PK or default support so no autoincrement column.""" def __init__(self, name, *columns, **kw): - """Produce a new :class:`_expression.TableClause`. - - The object returned is an instance of - :class:`_expression.TableClause`, which - represents the "syntactical" portion of the schema-level - :class:`_schema.Table` object. - It may be used to construct lightweight table constructs. - - .. versionchanged:: 1.0.0 :func:`_expression.table` can now - be imported from the plain ``sqlalchemy`` namespace like any - other SQL element. - - - :param name: Name of the table. - - :param columns: A collection of :func:`_expression.column` constructs. - - :param schema: The schema name for this table. - - .. versionadded:: 1.3.18 :func:`_expression.table` can now - accept a ``schema`` argument. - """ super(TableClause, self).__init__() self.name = name self._columns = DedupeColumnCollection() @@ -2697,41 +2474,6 @@ class Values(Generative, FromClause): ] def __init__(self, *columns, name=None, literal_binds=False): - r"""Construct a :class:`_expression.Values` construct. - - The column expressions and the actual data for - :class:`_expression.Values` are given in two separate steps. The - constructor receives the column expressions typically as - :func:`_expression.column` constructs, - and the data is then passed via the - :meth:`_expression.Values.data` method as a list, - which can be called multiple - times to add more data, e.g.:: - - from sqlalchemy import column - from sqlalchemy import values - - value_expr = values( - column('id', Integer), - column('name', String), - name="my_values" - ).data( - [(1, 'name1'), (2, 'name2'), (3, 'name3')] - ) - - :param \*columns: column expressions, typically composed using - :func:`_expression.column` objects. - - :param name: the name for this VALUES construct. If omitted, the - VALUES construct will be unnamed in a SQL expression. Different - backends may have different requirements here. - - :param literal_binds: Defaults to False. Whether or not to render - the data values inline in the SQL output, rather than using bound - parameters. - - """ - super(Values, self).__init__() self._column_args = columns self.name = name @@ -3708,91 +3450,26 @@ class CompoundSelect(HasCompileState, GenerativeSelect): @classmethod def _create_union(cls, *selects, **kwargs): - r"""Return a ``UNION`` of multiple selectables. - - The returned object is an instance of - :class:`_expression.CompoundSelect`. - - A similar :func:`union()` method is available on all - :class:`_expression.FromClause` subclasses. - - :param \*selects: - a list of :class:`_expression.Select` instances. - - :param \**kwargs: - available keyword arguments are the same as those of - :func:`select`. - - """ return CompoundSelect(CompoundSelect.UNION, *selects, **kwargs) @classmethod def _create_union_all(cls, *selects): - r"""Return a ``UNION ALL`` of multiple selectables. - - The returned object is an instance of - :class:`_expression.CompoundSelect`. - - A similar :func:`union_all()` method is available on all - :class:`_expression.FromClause` subclasses. - - :param \*selects: - a list of :class:`_expression.Select` instances. - - """ return CompoundSelect(CompoundSelect.UNION_ALL, *selects) @classmethod def _create_except(cls, *selects): - r"""Return an ``EXCEPT`` of multiple selectables. - - The returned object is an instance of - :class:`_expression.CompoundSelect`. - - :param \*selects: - a list of :class:`_expression.Select` instances. - - """ return CompoundSelect(CompoundSelect.EXCEPT, *selects) @classmethod def _create_except_all(cls, *selects): - r"""Return an ``EXCEPT ALL`` of multiple selectables. - - The returned object is an instance of - :class:`_expression.CompoundSelect`. - - :param \*selects: - a list of :class:`_expression.Select` instances. - - """ return CompoundSelect(CompoundSelect.EXCEPT_ALL, *selects) @classmethod def _create_intersect(cls, *selects): - r"""Return an ``INTERSECT`` of multiple selectables. - - The returned object is an instance of - :class:`_expression.CompoundSelect`. - - :param \*selects: - a list of :class:`_expression.Select` instances. - - """ return CompoundSelect(CompoundSelect.INTERSECT, *selects) @classmethod def _create_intersect_all(cls, *selects): - r"""Return an ``INTERSECT ALL`` of multiple selectables. - - The returned object is an instance of - :class:`_expression.CompoundSelect`. - - :param \*selects: - a list of :class:`_expression.Select` instances. - - - """ return CompoundSelect(CompoundSelect.INTERSECT_ALL, *selects) def _scalar_type(self): @@ -4413,47 +4090,26 @@ class Select( ] @classmethod - def _create( - cls, *entities: Union[roles.ColumnsClauseRole, Type] - ) -> "Select": - r"""Construct a new :class:`_expression.Select`. + def _create_raw_select(cls, **kw) -> "Select": + """Create a :class:`.Select` using raw ``__new__`` with no coercions. + Used internally to build up :class:`.Select` constructs with + pre-established state. - .. versionadded:: 1.4 - The :func:`_sql.select` function now accepts - column arguments positionally. The top-level :func:`_sql.select` - function will automatically use the 1.x or 2.x style API based on - the incoming arguments; using :func:`_future.select` from the - ``sqlalchemy.future`` module will enforce that only the 2.x style - constructor is used. + """ - Similar functionality is also available via the - :meth:`_expression.FromClause.select` method on any - :class:`_expression.FromClause`. + stmt = Select.__new__(Select) + stmt.__dict__.update(kw) + return stmt - .. seealso:: + def __init__(self, *entities: Union[roles.ColumnsClauseRole, Type]): + r"""Construct a new :class:`_expression.Select`. - :ref:`coretutorial_selecting` - Core Tutorial description of - :func:`_expression.select`. - - :param \*entities: - Entities to SELECT from. For Core usage, this is typically a series - of :class:`_expression.ColumnElement` and / or - :class:`_expression.FromClause` - objects which will form the columns clause of the resulting - statement. For those objects that are instances of - :class:`_expression.FromClause` (typically :class:`_schema.Table` - or :class:`_expression.Alias` - objects), the :attr:`_expression.FromClause.c` - collection is extracted - to form a collection of :class:`_expression.ColumnElement` objects. - - This parameter will also accept :class:`_expression.TextClause` - constructs as - given, as well as ORM-mapped classes. + The public constructor for :class:`_expression.Select` is the + :func:`_sql.select` function. """ - self = cls.__new__(cls) self._raw_columns = [ coercions.expect( roles.ColumnsClauseRole, ent, apply_propagate_attrs=self @@ -4463,24 +4119,6 @@ class Select( GenerativeSelect.__init__(self) - return self - - @classmethod - def _create_raw_select(cls, **kw) -> "Select": - """Create a :class:`.Select` using raw ``__new__`` with no coercions. - - Used internally to build up :class:`.Select` constructs with - pre-established state. - - """ - - stmt = Select.__new__(Select) - stmt.__dict__.update(kw) - return stmt - - def __init__(self): - raise NotImplementedError() - def _scalar_type(self): elem = self._raw_columns[0] cols = list(elem._select_iterable) @@ -5787,52 +5425,16 @@ class Exists(UnaryExpression): """ - _from_objects = [] + _from_objects = () inherit_cache = True - def __init__(self, *args, **kwargs): - """Construct a new :class:`_expression.Exists` construct. - - The :func:`_sql.exists` can be invoked by itself to produce an - :class:`_sql.Exists` construct, which will accept simple WHERE - criteria:: - - exists_criteria = exists().where(table1.c.col1 == table2.c.col2) - - However, for greater flexibility in constructing the SELECT, an - existing :class:`_sql.Select` construct may be converted to an - :class:`_sql.Exists`, most conveniently by making use of the - :meth:`_sql.SelectBase.exists` method:: - - exists_criteria = ( - select(table2.c.col2). - where(table1.c.col1 == table2.c.col2). - exists() - ) - - The EXISTS criteria is then used inside of an enclosing SELECT:: - - stmt = select(table1.c.col1).where(exists_criteria) - - The above statement will then be of the form:: - - SELECT col1 FROM table1 WHERE EXISTS - (SELECT table2.col2 FROM table2 WHERE table2.col2 = table1.col1) - - .. seealso:: - - :ref:`tutorial_exists` - in the :term:`2.0 style` tutorial. - - :meth:`_sql.SelectBase.exists` - method to transform a ``SELECT`` to an - ``EXISTS`` clause. - - """ # noqa E501 - if args and isinstance(args[0], (SelectBase, ScalarSelect)): - s = args[0] + def __init__(self, __argument=None): + if __argument is None: + s = Select(literal_column("*")).scalar_subquery() + elif isinstance(__argument, (SelectBase, ScalarSelect)): + s = __argument else: - if not args: - args = (literal_column("*"),) - s = Select._create(*args, **kwargs).scalar_subquery() + s = Select(__argument).scalar_subquery() UnaryExpression.__init__( self, @@ -5865,7 +5467,7 @@ class Exists(UnaryExpression): """ # noqa - return Select._create(self) + return Select(self) def correlate(self, *fromclause): """Apply correlation to the subquery noted by this :class:`_sql.Exists`. diff --git a/lib/sqlalchemy/sql/sqltypes.py b/lib/sqlalchemy/sql/sqltypes.py index 93ff53663..81434fbb9 100644 --- a/lib/sqlalchemy/sql/sqltypes.py +++ b/lib/sqlalchemy/sql/sqltypes.py @@ -8,12 +8,18 @@ """SQL specific types. """ - import collections.abc as collections_abc import datetime as dt import decimal +import enum import json import pickle +from typing import Any +from typing import Sequence +from typing import Text as typing_Text +from typing import Tuple +from typing import TypeVar +from typing import Union from . import coercions from . import elements @@ -43,6 +49,9 @@ from ..util import langhelpers from ..util import OrderedDict +_T = TypeVar("_T", bound="Any") + + class _LookupExpressionAdapter: """Mixin expression adaptations based on lookup tables. @@ -121,7 +130,7 @@ class Indexable: comparator_factory = Comparator -class String(Concatenable, TypeEngine): +class String(Concatenable, TypeEngine[typing_Text]): """The base for all string and character types. @@ -136,7 +145,10 @@ class String(Concatenable, TypeEngine): __visit_name__ = "string" def __init__( - self, + # note pylance appears to require the "self" type in a constructor + # for the _T type to be correctly recognized when we send the + # class as the argument, e.g. `column("somecol", String)` + self: "String", length=None, collation=None, ): @@ -289,7 +301,7 @@ class UnicodeText(Text): super(UnicodeText, self).__init__(length=length, **kwargs) -class Integer(_LookupExpressionAdapter, TypeEngine): +class Integer(_LookupExpressionAdapter, TypeEngine[int]): """A type for ``int`` integers.""" @@ -351,7 +363,9 @@ class BigInteger(Integer): __visit_name__ = "big_integer" -class Numeric(_LookupExpressionAdapter, TypeEngine): +class Numeric( + _LookupExpressionAdapter, TypeEngine[Union[decimal.Decimal, float]] +): """A type for fixed precision numbers, such as ``NUMERIC`` or ``DECIMAL``. @@ -396,7 +410,7 @@ class Numeric(_LookupExpressionAdapter, TypeEngine): _default_decimal_return_scale = 10 def __init__( - self, + self: "Numeric", precision=None, scale=None, decimal_return_scale=None, @@ -544,7 +558,10 @@ class Float(Numeric): scale = None def __init__( - self, precision=None, asdecimal=False, decimal_return_scale=None + self: "Float", + precision=None, + asdecimal=False, + decimal_return_scale=None, ): r""" Construct a Float. @@ -583,7 +600,7 @@ class Float(Numeric): return None -class DateTime(_LookupExpressionAdapter, TypeEngine): +class DateTime(_LookupExpressionAdapter, TypeEngine[dt.datetime]): """A type for ``datetime.datetime()`` objects. @@ -645,7 +662,7 @@ class DateTime(_LookupExpressionAdapter, TypeEngine): } -class Date(_LookupExpressionAdapter, TypeEngine): +class Date(_LookupExpressionAdapter, TypeEngine[dt.date]): """A type for ``datetime.date()`` objects.""" @@ -683,7 +700,7 @@ class Date(_LookupExpressionAdapter, TypeEngine): } -class Time(_LookupExpressionAdapter, TypeEngine): +class Time(_LookupExpressionAdapter, TypeEngine[dt.time]): """A type for ``datetime.time()`` objects.""" @@ -717,7 +734,7 @@ class Time(_LookupExpressionAdapter, TypeEngine): } -class _Binary(TypeEngine): +class _Binary(TypeEngine[bytes]): """Define base behavior for binary types.""" @@ -1019,7 +1036,7 @@ class SchemaType(SchemaEventTarget): return _we_are_the_impl(variant_mapping["_default"]) -class Enum(Emulated, String, SchemaType): +class Enum(Emulated, String, TypeEngine[Union[str, enum.Enum]], SchemaType): """Generic Enum Type. The :class:`.Enum` type provides a set of possible string values @@ -1496,7 +1513,7 @@ class Enum(Emulated, String, SchemaType): return super(Enum, self).python_type -class PickleType(TypeDecorator): +class PickleType(TypeDecorator[object]): """Holds Python objects, which are serialized using pickle. PickleType builds upon the Binary type to apply Python's @@ -1597,7 +1614,7 @@ class PickleType(TypeDecorator): return x == y -class Boolean(Emulated, TypeEngine, SchemaType): +class Boolean(Emulated, TypeEngine[bool], SchemaType): """A bool datatype. @@ -1621,7 +1638,10 @@ class Boolean(Emulated, TypeEngine, SchemaType): native = True def __init__( - self, create_constraint=False, name=None, _create_events=True + self: "Boolean", + create_constraint=False, + name=None, + _create_events=True, ): """Construct a Boolean. @@ -1723,7 +1743,7 @@ class Boolean(Emulated, TypeEngine, SchemaType): return processors.int_to_boolean -class _AbstractInterval(_LookupExpressionAdapter, TypeEngine): +class _AbstractInterval(_LookupExpressionAdapter, TypeEngine[dt.timedelta]): @util.memoized_property def _expression_adaptations(self): # Based on https://www.postgresql.org/docs/current/\ @@ -1841,7 +1861,7 @@ class Interval(Emulated, _AbstractInterval, TypeDecorator): return process -class JSON(Indexable, TypeEngine): +class JSON(Indexable, TypeEngine[Any]): """Represent a SQL JSON type. .. note:: :class:`_types.JSON` @@ -2399,7 +2419,9 @@ class JSON(Indexable, TypeEngine): return process -class ARRAY(SchemaEventTarget, Indexable, Concatenable, TypeEngine): +class ARRAY( + SchemaEventTarget, Indexable, Concatenable, TypeEngine[Sequence[Any]] +): """Represent a SQL Array type. .. note:: This type serves as the basis for all ARRAY operations. @@ -2700,7 +2722,7 @@ class ARRAY(SchemaEventTarget, Indexable, Concatenable, TypeEngine): self.item_type._set_parent_with_dispatch(parent) -class TupleType(TypeEngine): +class TupleType(TypeEngine[Tuple[Any]]): """represent the composite type of a Tuple.""" _is_tuple_type = True diff --git a/lib/sqlalchemy/sql/type_api.py b/lib/sqlalchemy/sql/type_api.py index 7981100a4..75eb1b8c5 100644 --- a/lib/sqlalchemy/sql/type_api.py +++ b/lib/sqlalchemy/sql/type_api.py @@ -10,30 +10,48 @@ """ import typing +from typing import Any +from typing import Generic +from typing import Tuple +from typing import Type +from typing import TypeVar +from typing import Union -from . import operators from .base import SchemaEventTarget from .cache_key import NO_CACHE +from .operators import ColumnOperators from .visitors import Traversible from .. import exc from .. import util # these are back-assigned by sqltypes. -BOOLEANTYPE = None -INTEGERTYPE = None -NULLTYPE = None -STRINGTYPE = None -MATCHTYPE = None -INDEXABLE = None -TABLEVALUE = None -_resolve_value_to_type = None - +if not typing.TYPE_CHECKING: + BOOLEANTYPE = None + INTEGERTYPE = None + NULLTYPE = None + STRINGTYPE = None + MATCHTYPE = None + INDEXABLE = None + TABLEVALUE = None + _resolve_value_to_type = None + +if typing.TYPE_CHECKING: + from .elements import ColumnElement + from .operators import OperatorType + from .sqltypes import _resolve_value_to_type + from .sqltypes import Boolean as BOOLEANTYPE # noqa + from .sqltypes import Indexable as INDEXABLE # noqa + from .sqltypes import MatchType as MATCHTYPE # noqa + from .sqltypes import NULLTYPE + +_T = TypeVar("_T", bound=Any) +_CT = TypeVar("_CT", bound=Any) # replace with pep-673 when applicable SelfTypeEngine = typing.TypeVar("SelfTypeEngine", bound="TypeEngine") -class TypeEngine(Traversible): +class TypeEngine(Traversible, Generic[_T]): """The ultimate base class for all SQL datatypes. Common subclasses of :class:`.TypeEngine` include @@ -55,6 +73,8 @@ class TypeEngine(Traversible): _is_array = False _is_type_decorator = False + _block_from_type_affinity = False + render_bind_cast = False """Render bind casts for :attr:`.BindTyping.RENDER_CASTS` mode. @@ -70,7 +90,10 @@ class TypeEngine(Traversible): """ - class Comparator(operators.ColumnOperators): + class Comparator( + ColumnOperators["ColumnElement"], + Generic[_CT], + ): """Base class for custom comparison operations defined at the type level. See :attr:`.TypeEngine.comparator_factory`. @@ -84,23 +107,33 @@ class TypeEngine(Traversible): def __clause_element__(self): return self.expr - def __init__(self, expr): + def __init__(self, expr: "ColumnElement[_CT]"): self.expr = expr - self.type = expr.type + self.type: TypeEngine[_CT] = expr.type @util.preload_module("sqlalchemy.sql.default_comparator") - def operate(self, op, *other, **kwargs): + def operate( + self, op: "OperatorType", *other, **kwargs + ) -> "ColumnElement": default_comparator = util.preloaded.sql_default_comparator - o = default_comparator.operator_lookup[op.__name__] - return o[0](self.expr, op, *(other + o[1:]), **kwargs) + op_fn, addtl_kw = default_comparator.operator_lookup[op.__name__] + if kwargs: + addtl_kw = addtl_kw.union(kwargs) + return op_fn(self.expr, op, *other, **addtl_kw) @util.preload_module("sqlalchemy.sql.default_comparator") - def reverse_operate(self, op, other, **kwargs): + def reverse_operate( + self, op: "OperatorType", other, **kwargs + ) -> "ColumnElement": default_comparator = util.preloaded.sql_default_comparator - o = default_comparator.operator_lookup[op.__name__] - return o[0](self.expr, op, other, reverse=True, *o[1:], **kwargs) - - def _adapt_expression(self, op, other_comparator): + op_fn, addtl_kw = default_comparator.operator_lookup[op.__name__] + if kwargs: + addtl_kw = addtl_kw.union(kwargs) + return op_fn(self.expr, op, other, reverse=True, **addtl_kw) + + def _adapt_expression( + self, op: "OperatorType", other_comparator + ) -> Tuple["OperatorType", "TypeEngine[_CT]"]: """evaluate the return type of <self> <op> <othertype>, and apply any adaptations to the given operator. @@ -611,7 +644,9 @@ class TypeEngine(Traversible): for t in self.__class__.__mro__: if t in (TypeEngine, UserDefinedType): return typ - elif issubclass(t, (TypeEngine, UserDefinedType)): + elif issubclass( + t, (TypeEngine, UserDefinedType) + ) and not t.__dict__.get("_block_from_type_affinity", False): typ = t else: return self.__class__ @@ -1202,7 +1237,7 @@ class NativeForEmulated: return cls(**kw) -class TypeDecorator(ExternalType, SchemaEventTarget, TypeEngine): +class TypeDecorator(ExternalType, SchemaEventTarget, TypeEngine[_T]): """Allows the creation of types which add additional functionality to an existing type. @@ -1882,7 +1917,9 @@ def _reconstitute_comparator(expression): return expression.comparator -def to_instance(typeobj, *arg, **kw): +def to_instance( + typeobj: Union[Type[TypeEngine[_T]], TypeEngine[_T], None], *arg, **kw +) -> TypeEngine[_T]: if typeobj is None: return NULLTYPE diff --git a/lib/sqlalchemy/sql/util.py b/lib/sqlalchemy/sql/util.py index 63067585e..fa3bae835 100644 --- a/lib/sqlalchemy/sql/util.py +++ b/lib/sqlalchemy/sql/util.py @@ -46,9 +46,35 @@ from .. import exc from .. import util -join_condition = util.langhelpers.public_factory( - Join._join_condition, ".sql.util.join_condition" -) +def join_condition(a, b, a_subset=None, consider_as_foreign_keys=None): + """Create a join condition between two tables or selectables. + + e.g.:: + + join_condition(tablea, tableb) + + would produce an expression along the lines of:: + + tablea.c.id==tableb.c.tablea_id + + The join is determined based on the foreign key relationships + between the two selectables. If there are multiple ways + to join, or no way to join, an error is raised. + + :param a_subset: An optional expression that is a sub-component + of ``a``. An attempt will be made to join to just this sub-component + first before looking at the full ``a`` construct, and if found + will be successful even if there are other ways to join to ``a``. + This allows the "right side" of a join to be passed thereby + providing a "natural join". + + """ + return Join._join_condition( + a, + b, + a_subset=a_subset, + consider_as_foreign_keys=consider_as_foreign_keys, + ) def find_join_source(clauses, join_to): |