Shortcuts on this page
r m x toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1# sql/_elements_constructors.py
2# Copyright (C) 2005-2025 the SQLAlchemy authors and contributors
3# <see AUTHORS file>
4#
5# This module is part of SQLAlchemy and is released under
6# the MIT License: https://www.opensource.org/licenses/mit-license.php
8from __future__ import annotations
10import typing
11from typing import Any
12from typing import Callable
13from typing import Mapping
14from typing import Optional
15from typing import overload
16from typing import Sequence
17from typing import Tuple as typing_Tuple
18from typing import TYPE_CHECKING
19from typing import TypeVar
20from typing import Union
22from . import coercions
23from . import roles
24from .base import _NoArg
25from .coercions import _document_text_coercion
26from .elements import BindParameter
27from .elements import BooleanClauseList
28from .elements import Case
29from .elements import Cast
30from .elements import CollationClause
31from .elements import CollectionAggregate
32from .elements import ColumnClause
33from .elements import ColumnElement
34from .elements import DMLTargetCopy
35from .elements import Extract
36from .elements import False_
37from .elements import FunctionFilter
38from .elements import Label
39from .elements import Null
40from .elements import Over
41from .elements import TextClause
42from .elements import True_
43from .elements import TryCast
44from .elements import Tuple
45from .elements import TypeCoerce
46from .elements import UnaryExpression
47from .elements import WithinGroup
48from .functions import FunctionElement
49from ..util.typing import Literal
51if typing.TYPE_CHECKING:
52 from ._typing import _ByArgument
53 from ._typing import _ColumnExpressionArgument
54 from ._typing import _ColumnExpressionOrLiteralArgument
55 from ._typing import _ColumnExpressionOrStrLabelArgument
56 from ._typing import _DMLOnlyColumnArgument
57 from ._typing import _TypeEngineArgument
58 from .elements import BinaryExpression
59 from .selectable import FromClause
60 from .type_api import TypeEngine
62_T = TypeVar("_T")
65def all_(expr: _ColumnExpressionArgument[_T]) -> CollectionAggregate[bool]:
66 """Produce an ALL expression.
68 For dialects such as that of PostgreSQL, this operator applies
69 to usage of the :class:`_types.ARRAY` datatype, for that of
70 MySQL, it may apply to a subquery. e.g.::
72 # renders on PostgreSQL:
73 # '5 = ALL (somearray)'
74 expr = 5 == all_(mytable.c.somearray)
76 # renders on MySQL:
77 # '5 = ALL (SELECT value FROM table)'
78 expr = 5 == all_(select(table.c.value))
80 Comparison to NULL may work using ``None``::
82 None == all_(mytable.c.somearray)
84 The any_() / all_() operators also feature a special "operand flipping"
85 behavior such that if any_() / all_() are used on the left side of a
86 comparison using a standalone operator such as ``==``, ``!=``, etc.
87 (not including operator methods such as
88 :meth:`_sql.ColumnOperators.is_`) the rendered expression is flipped::
90 # would render '5 = ALL (column)`
91 all_(mytable.c.column) == 5
93 Or with ``None``, which note will not perform
94 the usual step of rendering "IS" as is normally the case for NULL::
96 # would render 'NULL = ALL(somearray)'
97 all_(mytable.c.somearray) == None
99 .. versionchanged:: 1.4.26 repaired the use of any_() / all_()
100 comparing to NULL on the right side to be flipped to the left.
102 The column-level :meth:`_sql.ColumnElement.all_` method (not to be
103 confused with :class:`_types.ARRAY` level
104 :meth:`_types.ARRAY.Comparator.all`) is shorthand for
105 ``all_(col)``::
107 5 == mytable.c.somearray.all_()
109 .. seealso::
111 :meth:`_sql.ColumnOperators.all_`
113 :func:`_expression.any_`
115 """
116 return CollectionAggregate._create_all(expr)
119def and_( # type: ignore[empty-body]
120 initial_clause: Union[Literal[True], _ColumnExpressionArgument[bool]],
121 *clauses: _ColumnExpressionArgument[bool],
122) -> ColumnElement[bool]:
123 r"""Produce a conjunction of expressions joined by ``AND``.
125 E.g.::
127 from sqlalchemy import and_
129 stmt = select(users_table).where(
130 and_(users_table.c.name == "wendy", users_table.c.enrolled == True)
131 )
133 The :func:`.and_` conjunction is also available using the
134 Python ``&`` operator (though note that compound expressions
135 need to be parenthesized in order to function with Python
136 operator precedence behavior)::
138 stmt = select(users_table).where(
139 (users_table.c.name == "wendy") & (users_table.c.enrolled == True)
140 )
142 The :func:`.and_` operation is also implicit in some cases;
143 the :meth:`_expression.Select.where`
144 method for example can be invoked multiple
145 times against a statement, which will have the effect of each
146 clause being combined using :func:`.and_`::
148 stmt = (
149 select(users_table)
150 .where(users_table.c.name == "wendy")
151 .where(users_table.c.enrolled == True)
152 )
154 The :func:`.and_` construct must be given at least one positional
155 argument in order to be valid; a :func:`.and_` construct with no
156 arguments is ambiguous. To produce an "empty" or dynamically
157 generated :func:`.and_` expression, from a given list of expressions,
158 a "default" element of :func:`_sql.true` (or just ``True``) should be
159 specified::
161 from sqlalchemy import true
163 criteria = and_(true(), *expressions)
165 The above expression will compile to SQL as the expression ``true``
166 or ``1 = 1``, depending on backend, if no other expressions are
167 present. If expressions are present, then the :func:`_sql.true` value is
168 ignored as it does not affect the outcome of an AND expression that
169 has other elements.
171 .. deprecated:: 1.4 The :func:`.and_` element now requires that at
172 least one argument is passed; creating the :func:`.and_` construct
173 with no arguments is deprecated, and will emit a deprecation warning
174 while continuing to produce a blank SQL string.
176 .. seealso::
178 :func:`.or_`
180 """
181 ...
184if not TYPE_CHECKING:
185 # handle deprecated case which allows zero-arguments
186 def and_(*clauses): # noqa: F811
187 r"""Produce a conjunction of expressions joined by ``AND``.
189 E.g.::
191 from sqlalchemy import and_
193 stmt = select(users_table).where(
194 and_(users_table.c.name == "wendy", users_table.c.enrolled == True)
195 )
197 The :func:`.and_` conjunction is also available using the
198 Python ``&`` operator (though note that compound expressions
199 need to be parenthesized in order to function with Python
200 operator precedence behavior)::
202 stmt = select(users_table).where(
203 (users_table.c.name == "wendy") & (users_table.c.enrolled == True)
204 )
206 The :func:`.and_` operation is also implicit in some cases;
207 the :meth:`_expression.Select.where`
208 method for example can be invoked multiple
209 times against a statement, which will have the effect of each
210 clause being combined using :func:`.and_`::
212 stmt = (
213 select(users_table)
214 .where(users_table.c.name == "wendy")
215 .where(users_table.c.enrolled == True)
216 )
218 The :func:`.and_` construct must be given at least one positional
219 argument in order to be valid; a :func:`.and_` construct with no
220 arguments is ambiguous. To produce an "empty" or dynamically
221 generated :func:`.and_` expression, from a given list of expressions,
222 a "default" element of :func:`_sql.true` (or just ``True``) should be
223 specified::
225 from sqlalchemy import true
227 criteria = and_(true(), *expressions)
229 The above expression will compile to SQL as the expression ``true``
230 or ``1 = 1``, depending on backend, if no other expressions are
231 present. If expressions are present, then the :func:`_sql.true` value
232 is ignored as it does not affect the outcome of an AND expression that
233 has other elements.
235 .. deprecated:: 1.4 The :func:`.and_` element now requires that at
236 least one argument is passed; creating the :func:`.and_` construct
237 with no arguments is deprecated, and will emit a deprecation warning
238 while continuing to produce a blank SQL string.
240 .. seealso::
242 :func:`.or_`
244 """ # noqa: E501
245 return BooleanClauseList.and_(*clauses)
248def any_(expr: _ColumnExpressionArgument[_T]) -> CollectionAggregate[bool]:
249 """Produce an ANY expression.
251 For dialects such as that of PostgreSQL, this operator applies
252 to usage of the :class:`_types.ARRAY` datatype, for that of
253 MySQL, it may apply to a subquery. e.g.::
255 # renders on PostgreSQL:
256 # '5 = ANY (somearray)'
257 expr = 5 == any_(mytable.c.somearray)
259 # renders on MySQL:
260 # '5 = ANY (SELECT value FROM table)'
261 expr = 5 == any_(select(table.c.value))
263 Comparison to NULL may work using ``None`` or :func:`_sql.null`::
265 None == any_(mytable.c.somearray)
267 The any_() / all_() operators also feature a special "operand flipping"
268 behavior such that if any_() / all_() are used on the left side of a
269 comparison using a standalone operator such as ``==``, ``!=``, etc.
270 (not including operator methods such as
271 :meth:`_sql.ColumnOperators.is_`) the rendered expression is flipped::
273 # would render '5 = ANY (column)`
274 any_(mytable.c.column) == 5
276 Or with ``None``, which note will not perform
277 the usual step of rendering "IS" as is normally the case for NULL::
279 # would render 'NULL = ANY(somearray)'
280 any_(mytable.c.somearray) == None
282 .. versionchanged:: 1.4.26 repaired the use of any_() / all_()
283 comparing to NULL on the right side to be flipped to the left.
285 The column-level :meth:`_sql.ColumnElement.any_` method (not to be
286 confused with :class:`_types.ARRAY` level
287 :meth:`_types.ARRAY.Comparator.any`) is shorthand for
288 ``any_(col)``::
290 5 = mytable.c.somearray.any_()
292 .. seealso::
294 :meth:`_sql.ColumnOperators.any_`
296 :func:`_expression.all_`
298 """
299 return CollectionAggregate._create_any(expr)
302def asc(
303 column: _ColumnExpressionOrStrLabelArgument[_T],
304) -> UnaryExpression[_T]:
305 """Produce an ascending ``ORDER BY`` clause element.
307 e.g.::
309 from sqlalchemy import asc
311 stmt = select(users_table).order_by(asc(users_table.c.name))
313 will produce SQL as:
315 .. sourcecode:: sql
317 SELECT id, name FROM user ORDER BY name ASC
319 The :func:`.asc` function is a standalone version of the
320 :meth:`_expression.ColumnElement.asc`
321 method available on all SQL expressions,
322 e.g.::
325 stmt = select(users_table).order_by(users_table.c.name.asc())
327 :param column: A :class:`_expression.ColumnElement` (e.g.
328 scalar SQL expression)
329 with which to apply the :func:`.asc` operation.
331 .. seealso::
333 :func:`.desc`
335 :func:`.nulls_first`
337 :func:`.nulls_last`
339 :meth:`_expression.Select.order_by`
341 """
342 return UnaryExpression._create_asc(column)
345def collate(
346 expression: _ColumnExpressionArgument[str], collation: str
347) -> BinaryExpression[str]:
348 """Return the clause ``expression COLLATE collation``.
350 e.g.::
352 collate(mycolumn, "utf8_bin")
354 produces:
356 .. sourcecode:: sql
358 mycolumn COLLATE utf8_bin
360 The collation expression is also quoted if it is a case sensitive
361 identifier, e.g. contains uppercase characters.
363 """
364 return CollationClause._create_collation_expression(expression, collation)
367def between(
368 expr: _ColumnExpressionOrLiteralArgument[_T],
369 lower_bound: Any,
370 upper_bound: Any,
371 symmetric: bool = False,
372) -> BinaryExpression[bool]:
373 """Produce a ``BETWEEN`` predicate clause.
375 E.g.::
377 from sqlalchemy import between
379 stmt = select(users_table).where(between(users_table.c.id, 5, 7))
381 Would produce SQL resembling:
383 .. sourcecode:: sql
385 SELECT id, name FROM user WHERE id BETWEEN :id_1 AND :id_2
387 The :func:`.between` function is a standalone version of the
388 :meth:`_expression.ColumnElement.between` method available on all
389 SQL expressions, as in::
391 stmt = select(users_table).where(users_table.c.id.between(5, 7))
393 All arguments passed to :func:`.between`, including the left side
394 column expression, are coerced from Python scalar values if a
395 the value is not a :class:`_expression.ColumnElement` subclass.
396 For example,
397 three fixed values can be compared as in::
399 print(between(5, 3, 7))
401 Which would produce::
403 :param_1 BETWEEN :param_2 AND :param_3
405 :param expr: a column expression, typically a
406 :class:`_expression.ColumnElement`
407 instance or alternatively a Python scalar expression to be coerced
408 into a column expression, serving as the left side of the ``BETWEEN``
409 expression.
411 :param lower_bound: a column or Python scalar expression serving as the
412 lower bound of the right side of the ``BETWEEN`` expression.
414 :param upper_bound: a column or Python scalar expression serving as the
415 upper bound of the right side of the ``BETWEEN`` expression.
417 :param symmetric: if True, will render " BETWEEN SYMMETRIC ". Note
418 that not all databases support this syntax.
420 .. seealso::
422 :meth:`_expression.ColumnElement.between`
424 """
425 col_expr = coercions.expect(roles.ExpressionElementRole, expr)
426 return col_expr.between(lower_bound, upper_bound, symmetric=symmetric)
429def outparam(
430 key: str, type_: Optional[TypeEngine[_T]] = None
431) -> BindParameter[_T]:
432 """Create an 'OUT' parameter for usage in functions (stored procedures),
433 for databases which support them.
435 The ``outparam`` can be used like a regular function parameter.
436 The "output" value will be available from the
437 :class:`~sqlalchemy.engine.CursorResult` object via its ``out_parameters``
438 attribute, which returns a dictionary containing the values.
440 """
441 return BindParameter(key, None, type_=type_, unique=False, isoutparam=True)
444@overload
445def not_(clause: BinaryExpression[_T]) -> BinaryExpression[_T]: ...
448@overload
449def not_(clause: _ColumnExpressionArgument[_T]) -> ColumnElement[_T]: ...
452def not_(clause: _ColumnExpressionArgument[_T]) -> ColumnElement[_T]:
453 """Return a negation of the given clause, i.e. ``NOT(clause)``.
455 The ``~`` operator is also overloaded on all
456 :class:`_expression.ColumnElement` subclasses to produce the
457 same result.
459 """
461 return coercions.expect(roles.ExpressionElementRole, clause).__invert__()
464def from_dml_column(column: _DMLOnlyColumnArgument[_T]) -> DMLTargetCopy[_T]:
465 r"""A placeholder that may be used in compiled INSERT or UPDATE expressions
466 to refer to the SQL expression or value being applied to another column.
468 Given a table such as::
470 t = Table(
471 "t",
472 MetaData(),
473 Column("x", Integer),
474 Column("y", Integer),
475 )
477 The :func:`_sql.from_dml_column` construct allows automatic copying
478 of an expression assigned to a different column to be re-used::
480 >>> stmt = t.insert().values(x=func.foobar(3), y=from_dml_column(t.c.x) + 5)
481 >>> print(stmt)
482 INSERT INTO t (x, y) VALUES (foobar(:foobar_1), (foobar(:foobar_1) + :param_1))
484 The :func:`_sql.from_dml_column` construct is intended to be useful primarily
485 with event-based hooks such as those used by ORM hybrids.
487 .. seealso::
489 :ref:`hybrid_bulk_update`
491 .. versionadded:: 2.1
494 """ # noqa: E501
496 return DMLTargetCopy(column)
499def bindparam(
500 key: Optional[str],
501 value: Any = _NoArg.NO_ARG,
502 type_: Optional[_TypeEngineArgument[_T]] = None,
503 unique: bool = False,
504 required: Union[bool, Literal[_NoArg.NO_ARG]] = _NoArg.NO_ARG,
505 quote: Optional[bool] = None,
506 callable_: Optional[Callable[[], Any]] = None,
507 expanding: bool = False,
508 isoutparam: bool = False,
509 literal_execute: bool = False,
510) -> BindParameter[_T]:
511 r"""Produce a "bound expression".
513 The return value is an instance of :class:`.BindParameter`; this
514 is a :class:`_expression.ColumnElement`
515 subclass which represents a so-called
516 "placeholder" value in a SQL expression, the value of which is
517 supplied at the point at which the statement in executed against a
518 database connection.
520 In SQLAlchemy, the :func:`.bindparam` construct has
521 the ability to carry along the actual value that will be ultimately
522 used at expression time. In this way, it serves not just as
523 a "placeholder" for eventual population, but also as a means of
524 representing so-called "unsafe" values which should not be rendered
525 directly in a SQL statement, but rather should be passed along
526 to the :term:`DBAPI` as values which need to be correctly escaped
527 and potentially handled for type-safety.
529 When using :func:`.bindparam` explicitly, the use case is typically
530 one of traditional deferment of parameters; the :func:`.bindparam`
531 construct accepts a name which can then be referred to at execution
532 time::
534 from sqlalchemy import bindparam
536 stmt = select(users_table).where(
537 users_table.c.name == bindparam("username")
538 )
540 The above statement, when rendered, will produce SQL similar to:
542 .. sourcecode:: sql
544 SELECT id, name FROM user WHERE name = :username
546 In order to populate the value of ``:username`` above, the value
547 would typically be applied at execution time to a method
548 like :meth:`_engine.Connection.execute`::
550 result = connection.execute(stmt, {"username": "wendy"})
552 Explicit use of :func:`.bindparam` is also common when producing
553 UPDATE or DELETE statements that are to be invoked multiple times,
554 where the WHERE criterion of the statement is to change on each
555 invocation, such as::
557 stmt = (
558 users_table.update()
559 .where(user_table.c.name == bindparam("username"))
560 .values(fullname=bindparam("fullname"))
561 )
563 connection.execute(
564 stmt,
565 [
566 {"username": "wendy", "fullname": "Wendy Smith"},
567 {"username": "jack", "fullname": "Jack Jones"},
568 ],
569 )
571 SQLAlchemy's Core expression system makes wide use of
572 :func:`.bindparam` in an implicit sense. It is typical that Python
573 literal values passed to virtually all SQL expression functions are
574 coerced into fixed :func:`.bindparam` constructs. For example, given
575 a comparison operation such as::
577 expr = users_table.c.name == "Wendy"
579 The above expression will produce a :class:`.BinaryExpression`
580 construct, where the left side is the :class:`_schema.Column` object
581 representing the ``name`` column, and the right side is a
582 :class:`.BindParameter` representing the literal value::
584 print(repr(expr.right))
585 BindParameter("%(4327771088 name)s", "Wendy", type_=String())
587 The expression above will render SQL such as:
589 .. sourcecode:: sql
591 user.name = :name_1
593 Where the ``:name_1`` parameter name is an anonymous name. The
594 actual string ``Wendy`` is not in the rendered string, but is carried
595 along where it is later used within statement execution. If we
596 invoke a statement like the following::
598 stmt = select(users_table).where(users_table.c.name == "Wendy")
599 result = connection.execute(stmt)
601 We would see SQL logging output as:
603 .. sourcecode:: sql
605 SELECT "user".id, "user".name
606 FROM "user"
607 WHERE "user".name = %(name_1)s
608 {'name_1': 'Wendy'}
610 Above, we see that ``Wendy`` is passed as a parameter to the database,
611 while the placeholder ``:name_1`` is rendered in the appropriate form
612 for the target database, in this case the PostgreSQL database.
614 Similarly, :func:`.bindparam` is invoked automatically when working
615 with :term:`CRUD` statements as far as the "VALUES" portion is
616 concerned. The :func:`_expression.insert` construct produces an
617 ``INSERT`` expression which will, at statement execution time, generate
618 bound placeholders based on the arguments passed, as in::
620 stmt = users_table.insert()
621 result = connection.execute(stmt, {"name": "Wendy"})
623 The above will produce SQL output as:
625 .. sourcecode:: sql
627 INSERT INTO "user" (name) VALUES (%(name)s)
628 {'name': 'Wendy'}
630 The :class:`_expression.Insert` construct, at
631 compilation/execution time, rendered a single :func:`.bindparam`
632 mirroring the column name ``name`` as a result of the single ``name``
633 parameter we passed to the :meth:`_engine.Connection.execute` method.
635 :param key:
636 the key (e.g. the name) for this bind param.
637 Will be used in the generated
638 SQL statement for dialects that use named parameters. This
639 value may be modified when part of a compilation operation,
640 if other :class:`BindParameter` objects exist with the same
641 key, or if its length is too long and truncation is
642 required.
644 If omitted, an "anonymous" name is generated for the bound parameter;
645 when given a value to bind, the end result is equivalent to calling upon
646 the :func:`.literal` function with a value to bind, particularly
647 if the :paramref:`.bindparam.unique` parameter is also provided.
649 :param value:
650 Initial value for this bind param. Will be used at statement
651 execution time as the value for this parameter passed to the
652 DBAPI, if no other value is indicated to the statement execution
653 method for this particular parameter name. Defaults to ``None``.
655 :param callable\_:
656 A callable function that takes the place of "value". The function
657 will be called at statement execution time to determine the
658 ultimate value. Used for scenarios where the actual bind
659 value cannot be determined at the point at which the clause
660 construct is created, but embedded bind values are still desirable.
662 :param type\_:
663 A :class:`.TypeEngine` class or instance representing an optional
664 datatype for this :func:`.bindparam`. If not passed, a type
665 may be determined automatically for the bind, based on the given
666 value; for example, trivial Python types such as ``str``,
667 ``int``, ``bool``
668 may result in the :class:`.String`, :class:`.Integer` or
669 :class:`.Boolean` types being automatically selected.
671 The type of a :func:`.bindparam` is significant especially in that
672 the type will apply pre-processing to the value before it is
673 passed to the database. For example, a :func:`.bindparam` which
674 refers to a datetime value, and is specified as holding the
675 :class:`.DateTime` type, may apply conversion needed to the
676 value (such as stringification on SQLite) before passing the value
677 to the database.
679 :param unique:
680 if True, the key name of this :class:`.BindParameter` will be
681 modified if another :class:`.BindParameter` of the same name
682 already has been located within the containing
683 expression. This flag is used generally by the internals
684 when producing so-called "anonymous" bound expressions, it
685 isn't generally applicable to explicitly-named :func:`.bindparam`
686 constructs.
688 :param required:
689 If ``True``, a value is required at execution time. If not passed,
690 it defaults to ``True`` if neither :paramref:`.bindparam.value`
691 or :paramref:`.bindparam.callable` were passed. If either of these
692 parameters are present, then :paramref:`.bindparam.required`
693 defaults to ``False``.
695 :param quote:
696 True if this parameter name requires quoting and is not
697 currently known as a SQLAlchemy reserved word; this currently
698 only applies to the Oracle Database backends, where bound names must
699 sometimes be quoted.
701 :param isoutparam:
702 if True, the parameter should be treated like a stored procedure
703 "OUT" parameter. This applies to backends such as Oracle Database which
704 support OUT parameters.
706 :param expanding:
707 if True, this parameter will be treated as an "expanding" parameter
708 at execution time; the parameter value is expected to be a sequence,
709 rather than a scalar value, and the string SQL statement will
710 be transformed on a per-execution basis to accommodate the sequence
711 with a variable number of parameter slots passed to the DBAPI.
712 This is to allow statement caching to be used in conjunction with
713 an IN clause.
715 .. seealso::
717 :meth:`.ColumnOperators.in_`
719 :ref:`baked_in` - with baked queries
721 .. note:: The "expanding" feature does not support "executemany"-
722 style parameter sets.
724 :param literal_execute:
725 if True, the bound parameter will be rendered in the compile phase
726 with a special "POSTCOMPILE" token, and the SQLAlchemy compiler will
727 render the final value of the parameter into the SQL statement at
728 statement execution time, omitting the value from the parameter
729 dictionary / list passed to DBAPI ``cursor.execute()``. This
730 produces a similar effect as that of using the ``literal_binds``,
731 compilation flag, however takes place as the statement is sent to
732 the DBAPI ``cursor.execute()`` method, rather than when the statement
733 is compiled. The primary use of this
734 capability is for rendering LIMIT / OFFSET clauses for database
735 drivers that can't accommodate for bound parameters in these
736 contexts, while allowing SQL constructs to be cacheable at the
737 compilation level.
739 .. versionadded:: 1.4 Added "post compile" bound parameters
741 .. seealso::
743 :ref:`change_4808`.
745 .. seealso::
747 :ref:`tutorial_sending_parameters` - in the
748 :ref:`unified_tutorial`
751 """
752 return BindParameter(
753 key,
754 value,
755 type_,
756 unique,
757 required,
758 quote,
759 callable_,
760 expanding,
761 isoutparam,
762 literal_execute,
763 )
766def case(
767 *whens: Union[
768 typing_Tuple[_ColumnExpressionArgument[bool], Any], Mapping[Any, Any]
769 ],
770 value: Optional[Any] = None,
771 else_: Optional[Any] = None,
772) -> Case[Any]:
773 r"""Produce a ``CASE`` expression.
775 The ``CASE`` construct in SQL is a conditional object that
776 acts somewhat analogously to an "if/then" construct in other
777 languages. It returns an instance of :class:`.Case`.
779 :func:`.case` in its usual form is passed a series of "when"
780 constructs, that is, a list of conditions and results as tuples::
782 from sqlalchemy import case
784 stmt = select(users_table).where(
785 case(
786 (users_table.c.name == "wendy", "W"),
787 (users_table.c.name == "jack", "J"),
788 else_="E",
789 )
790 )
792 The above statement will produce SQL resembling:
794 .. sourcecode:: sql
796 SELECT id, name FROM user
797 WHERE CASE
798 WHEN (name = :name_1) THEN :param_1
799 WHEN (name = :name_2) THEN :param_2
800 ELSE :param_3
801 END
803 When simple equality expressions of several values against a single
804 parent column are needed, :func:`.case` also has a "shorthand" format
805 used via the
806 :paramref:`.case.value` parameter, which is passed a column
807 expression to be compared. In this form, the :paramref:`.case.whens`
808 parameter is passed as a dictionary containing expressions to be
809 compared against keyed to result expressions. The statement below is
810 equivalent to the preceding statement::
812 stmt = select(users_table).where(
813 case({"wendy": "W", "jack": "J"}, value=users_table.c.name, else_="E")
814 )
816 The values which are accepted as result values in
817 :paramref:`.case.whens` as well as with :paramref:`.case.else_` are
818 coerced from Python literals into :func:`.bindparam` constructs.
819 SQL expressions, e.g. :class:`_expression.ColumnElement` constructs,
820 are accepted
821 as well. To coerce a literal string expression into a constant
822 expression rendered inline, use the :func:`_expression.literal_column`
823 construct,
824 as in::
826 from sqlalchemy import case, literal_column
828 case(
829 (orderline.c.qty > 100, literal_column("'greaterthan100'")),
830 (orderline.c.qty > 10, literal_column("'greaterthan10'")),
831 else_=literal_column("'lessthan10'"),
832 )
834 The above will render the given constants without using bound
835 parameters for the result values (but still for the comparison
836 values), as in:
838 .. sourcecode:: sql
840 CASE
841 WHEN (orderline.qty > :qty_1) THEN 'greaterthan100'
842 WHEN (orderline.qty > :qty_2) THEN 'greaterthan10'
843 ELSE 'lessthan10'
844 END
846 :param \*whens: The criteria to be compared against,
847 :paramref:`.case.whens` accepts two different forms, based on
848 whether or not :paramref:`.case.value` is used.
850 .. versionchanged:: 1.4 the :func:`_sql.case`
851 function now accepts the series of WHEN conditions positionally
853 In the first form, it accepts multiple 2-tuples passed as positional
854 arguments; each 2-tuple consists of ``(<sql expression>, <value>)``,
855 where the SQL expression is a boolean expression and "value" is a
856 resulting value, e.g.::
858 case(
859 (users_table.c.name == "wendy", "W"),
860 (users_table.c.name == "jack", "J"),
861 )
863 In the second form, it accepts a Python dictionary of comparison
864 values mapped to a resulting value; this form requires
865 :paramref:`.case.value` to be present, and values will be compared
866 using the ``==`` operator, e.g.::
868 case({"wendy": "W", "jack": "J"}, value=users_table.c.name)
870 :param value: An optional SQL expression which will be used as a
871 fixed "comparison point" for candidate values within a dictionary
872 passed to :paramref:`.case.whens`.
874 :param else\_: An optional SQL expression which will be the evaluated
875 result of the ``CASE`` construct if all expressions within
876 :paramref:`.case.whens` evaluate to false. When omitted, most
877 databases will produce a result of NULL if none of the "when"
878 expressions evaluate to true.
881 """ # noqa: E501
882 return Case(*whens, value=value, else_=else_)
885def cast(
886 expression: _ColumnExpressionOrLiteralArgument[Any],
887 type_: _TypeEngineArgument[_T],
888) -> Cast[_T]:
889 r"""Produce a ``CAST`` expression.
891 :func:`.cast` returns an instance of :class:`.Cast`.
893 E.g.::
895 from sqlalchemy import cast, Numeric
897 stmt = select(cast(product_table.c.unit_price, Numeric(10, 4)))
899 The above statement will produce SQL resembling:
901 .. sourcecode:: sql
903 SELECT CAST(unit_price AS NUMERIC(10, 4)) FROM product
905 The :func:`.cast` function performs two distinct functions when
906 used. The first is that it renders the ``CAST`` expression within
907 the resulting SQL string. The second is that it associates the given
908 type (e.g. :class:`.TypeEngine` class or instance) with the column
909 expression on the Python side, which means the expression will take
910 on the expression operator behavior associated with that type,
911 as well as the bound-value handling and result-row-handling behavior
912 of the type.
914 An alternative to :func:`.cast` is the :func:`.type_coerce` function.
915 This function performs the second task of associating an expression
916 with a specific type, but does not render the ``CAST`` expression
917 in SQL.
919 :param expression: A SQL expression, such as a
920 :class:`_expression.ColumnElement`
921 expression or a Python string which will be coerced into a bound
922 literal value.
924 :param type\_: A :class:`.TypeEngine` class or instance indicating
925 the type to which the ``CAST`` should apply.
927 .. seealso::
929 :ref:`tutorial_casts`
931 :func:`.try_cast` - an alternative to CAST that results in
932 NULLs when the cast fails, instead of raising an error.
933 Only supported by some dialects.
935 :func:`.type_coerce` - an alternative to CAST that coerces the type
936 on the Python side only, which is often sufficient to generate the
937 correct SQL and data coercion.
940 """
941 return Cast(expression, type_)
944def try_cast(
945 expression: _ColumnExpressionOrLiteralArgument[Any],
946 type_: _TypeEngineArgument[_T],
947) -> TryCast[_T]:
948 """Produce a ``TRY_CAST`` expression for backends which support it;
949 this is a ``CAST`` which returns NULL for un-castable conversions.
951 In SQLAlchemy, this construct is supported **only** by the SQL Server
952 dialect, and will raise a :class:`.CompileError` if used on other
953 included backends. However, third party backends may also support
954 this construct.
956 .. tip:: As :func:`_sql.try_cast` originates from the SQL Server dialect,
957 it's importable both from ``sqlalchemy.`` as well as from
958 ``sqlalchemy.dialects.mssql``.
960 :func:`_sql.try_cast` returns an instance of :class:`.TryCast` and
961 generally behaves similarly to the :class:`.Cast` construct;
962 at the SQL level, the difference between ``CAST`` and ``TRY_CAST``
963 is that ``TRY_CAST`` returns NULL for an un-castable expression,
964 such as attempting to cast a string ``"hi"`` to an integer value.
966 E.g.::
968 from sqlalchemy import select, try_cast, Numeric
970 stmt = select(try_cast(product_table.c.unit_price, Numeric(10, 4)))
972 The above would render on Microsoft SQL Server as:
974 .. sourcecode:: sql
976 SELECT TRY_CAST (product_table.unit_price AS NUMERIC(10, 4))
977 FROM product_table
979 .. versionadded:: 2.0.14 :func:`.try_cast` has been
980 generalized from the SQL Server dialect into a general use
981 construct that may be supported by additional dialects.
983 """
984 return TryCast(expression, type_)
987def column(
988 text: str,
989 type_: Optional[_TypeEngineArgument[_T]] = None,
990 is_literal: bool = False,
991 _selectable: Optional[FromClause] = None,
992) -> ColumnClause[_T]:
993 """Produce a :class:`.ColumnClause` object.
995 The :class:`.ColumnClause` is a lightweight analogue to the
996 :class:`_schema.Column` class. The :func:`_expression.column`
997 function can
998 be invoked with just a name alone, as in::
1000 from sqlalchemy import column
1002 id, name = column("id"), column("name")
1003 stmt = select(id, name).select_from("user")
1005 The above statement would produce SQL like:
1007 .. sourcecode:: sql
1009 SELECT id, name FROM user
1011 Once constructed, :func:`_expression.column`
1012 may be used like any other SQL
1013 expression element such as within :func:`_expression.select`
1014 constructs::
1016 from sqlalchemy.sql import column
1018 id, name = column("id"), column("name")
1019 stmt = select(id, name).select_from("user")
1021 The text handled by :func:`_expression.column`
1022 is assumed to be handled
1023 like the name of a database column; if the string contains mixed case,
1024 special characters, or matches a known reserved word on the target
1025 backend, the column expression will render using the quoting
1026 behavior determined by the backend. To produce a textual SQL
1027 expression that is rendered exactly without any quoting,
1028 use :func:`_expression.literal_column` instead,
1029 or pass ``True`` as the
1030 value of :paramref:`_expression.column.is_literal`. Additionally,
1031 full SQL
1032 statements are best handled using the :func:`_expression.text`
1033 construct.
1035 :func:`_expression.column` can be used in a table-like
1036 fashion by combining it with the :func:`.table` function
1037 (which is the lightweight analogue to :class:`_schema.Table`
1038 ) to produce
1039 a working table construct with minimal boilerplate::
1041 from sqlalchemy import table, column, select
1043 user = table(
1044 "user",
1045 column("id"),
1046 column("name"),
1047 column("description"),
1048 )
1050 stmt = select(user.c.description).where(user.c.name == "wendy")
1052 A :func:`_expression.column` / :func:`.table`
1053 construct like that illustrated
1054 above can be created in an
1055 ad-hoc fashion and is not associated with any
1056 :class:`_schema.MetaData`, DDL, or events, unlike its
1057 :class:`_schema.Table` counterpart.
1059 :param text: the text of the element.
1061 :param type: :class:`_types.TypeEngine` object which can associate
1062 this :class:`.ColumnClause` with a type.
1064 :param is_literal: if True, the :class:`.ColumnClause` is assumed to
1065 be an exact expression that will be delivered to the output with no
1066 quoting rules applied regardless of case sensitive settings. the
1067 :func:`_expression.literal_column()` function essentially invokes
1068 :func:`_expression.column` while passing ``is_literal=True``.
1070 .. seealso::
1072 :class:`_schema.Column`
1074 :func:`_expression.literal_column`
1076 :func:`.table`
1078 :func:`_expression.text`
1080 :ref:`tutorial_select_arbitrary_text`
1082 """
1083 return ColumnClause(text, type_, is_literal, _selectable)
1086def desc(
1087 column: _ColumnExpressionOrStrLabelArgument[_T],
1088) -> UnaryExpression[_T]:
1089 """Produce a descending ``ORDER BY`` clause element.
1091 e.g.::
1093 from sqlalchemy import desc
1095 stmt = select(users_table).order_by(desc(users_table.c.name))
1097 will produce SQL as:
1099 .. sourcecode:: sql
1101 SELECT id, name FROM user ORDER BY name DESC
1103 The :func:`.desc` function is a standalone version of the
1104 :meth:`_expression.ColumnElement.desc`
1105 method available on all SQL expressions,
1106 e.g.::
1109 stmt = select(users_table).order_by(users_table.c.name.desc())
1111 :param column: A :class:`_expression.ColumnElement` (e.g.
1112 scalar SQL expression)
1113 with which to apply the :func:`.desc` operation.
1115 .. seealso::
1117 :func:`.asc`
1119 :func:`.nulls_first`
1121 :func:`.nulls_last`
1123 :meth:`_expression.Select.order_by`
1125 """
1126 return UnaryExpression._create_desc(column)
1129def distinct(expr: _ColumnExpressionArgument[_T]) -> UnaryExpression[_T]:
1130 """Produce an column-expression-level unary ``DISTINCT`` clause.
1132 This applies the ``DISTINCT`` keyword to an **individual column
1133 expression** (e.g. not the whole statement), and renders **specifically
1134 in that column position**; this is used for containment within
1135 an aggregate function, as in::
1137 from sqlalchemy import distinct, func
1139 stmt = select(users_table.c.id, func.count(distinct(users_table.c.name)))
1141 The above would produce an statement resembling:
1143 .. sourcecode:: sql
1145 SELECT user.id, count(DISTINCT user.name) FROM user
1147 .. tip:: The :func:`_sql.distinct` function does **not** apply DISTINCT
1148 to the full SELECT statement, instead applying a DISTINCT modifier
1149 to **individual column expressions**. For general ``SELECT DISTINCT``
1150 support, use the
1151 :meth:`_sql.Select.distinct` method on :class:`_sql.Select`.
1153 The :func:`.distinct` function is also available as a column-level
1154 method, e.g. :meth:`_expression.ColumnElement.distinct`, as in::
1156 stmt = select(func.count(users_table.c.name.distinct()))
1158 The :func:`.distinct` operator is different from the
1159 :meth:`_expression.Select.distinct` method of
1160 :class:`_expression.Select`,
1161 which produces a ``SELECT`` statement
1162 with ``DISTINCT`` applied to the result set as a whole,
1163 e.g. a ``SELECT DISTINCT`` expression. See that method for further
1164 information.
1166 .. seealso::
1168 :meth:`_expression.ColumnElement.distinct`
1170 :meth:`_expression.Select.distinct`
1172 :data:`.func`
1174 """ # noqa: E501
1175 return UnaryExpression._create_distinct(expr)
1178def bitwise_not(expr: _ColumnExpressionArgument[_T]) -> UnaryExpression[_T]:
1179 """Produce a unary bitwise NOT clause, typically via the ``~`` operator.
1181 Not to be confused with boolean negation :func:`_sql.not_`.
1183 .. versionadded:: 2.0.2
1185 .. seealso::
1187 :ref:`operators_bitwise`
1190 """
1192 return UnaryExpression._create_bitwise_not(expr)
1195def extract(field: str, expr: _ColumnExpressionArgument[Any]) -> Extract:
1196 """Return a :class:`.Extract` construct.
1198 This is typically available as :func:`.extract`
1199 as well as ``func.extract`` from the
1200 :data:`.func` namespace.
1202 :param field: The field to extract.
1204 .. warning:: This field is used as a literal SQL string.
1205 **DO NOT PASS UNTRUSTED INPUT TO THIS STRING**.
1207 :param expr: A column or Python scalar expression serving as the
1208 right side of the ``EXTRACT`` expression.
1210 E.g.::
1212 from sqlalchemy import extract
1213 from sqlalchemy import table, column
1215 logged_table = table(
1216 "user",
1217 column("id"),
1218 column("date_created"),
1219 )
1221 stmt = select(logged_table.c.id).where(
1222 extract("YEAR", logged_table.c.date_created) == 2021
1223 )
1225 In the above example, the statement is used to select ids from the
1226 database where the ``YEAR`` component matches a specific value.
1228 Similarly, one can also select an extracted component::
1230 stmt = select(extract("YEAR", logged_table.c.date_created)).where(
1231 logged_table.c.id == 1
1232 )
1234 The implementation of ``EXTRACT`` may vary across database backends.
1235 Users are reminded to consult their database documentation.
1236 """
1237 return Extract(field, expr)
1240def false() -> False_:
1241 """Return a :class:`.False_` construct.
1243 E.g.:
1245 .. sourcecode:: pycon+sql
1247 >>> from sqlalchemy import false
1248 >>> print(select(t.c.x).where(false()))
1249 {printsql}SELECT x FROM t WHERE false
1251 A backend which does not support true/false constants will render as
1252 an expression against 1 or 0:
1254 .. sourcecode:: pycon+sql
1256 >>> print(select(t.c.x).where(false()))
1257 {printsql}SELECT x FROM t WHERE 0 = 1
1259 The :func:`.true` and :func:`.false` constants also feature
1260 "short circuit" operation within an :func:`.and_` or :func:`.or_`
1261 conjunction:
1263 .. sourcecode:: pycon+sql
1265 >>> print(select(t.c.x).where(or_(t.c.x > 5, true())))
1266 {printsql}SELECT x FROM t WHERE true{stop}
1268 >>> print(select(t.c.x).where(and_(t.c.x > 5, false())))
1269 {printsql}SELECT x FROM t WHERE false{stop}
1271 .. seealso::
1273 :func:`.true`
1275 """
1277 return False_._instance()
1280def funcfilter(
1281 func: FunctionElement[_T], *criterion: _ColumnExpressionArgument[bool]
1282) -> FunctionFilter[_T]:
1283 """Produce a :class:`.FunctionFilter` object against a function.
1285 Used against aggregate and window functions,
1286 for database backends that support the "FILTER" clause.
1288 E.g.::
1290 from sqlalchemy import funcfilter
1292 funcfilter(func.count(1), MyClass.name == "some name")
1294 Would produce "COUNT(1) FILTER (WHERE myclass.name = 'some name')".
1296 This function is also available from the :data:`~.expression.func`
1297 construct itself via the :meth:`.FunctionElement.filter` method.
1299 .. seealso::
1301 :ref:`tutorial_functions_within_group` - in the
1302 :ref:`unified_tutorial`
1304 :meth:`.FunctionElement.filter`
1306 """
1307 return FunctionFilter(func, *criterion)
1310def label(
1311 name: str,
1312 element: _ColumnExpressionArgument[_T],
1313 type_: Optional[_TypeEngineArgument[_T]] = None,
1314) -> Label[_T]:
1315 """Return a :class:`Label` object for the
1316 given :class:`_expression.ColumnElement`.
1318 A label changes the name of an element in the columns clause of a
1319 ``SELECT`` statement, typically via the ``AS`` SQL keyword.
1321 This functionality is more conveniently available via the
1322 :meth:`_expression.ColumnElement.label` method on
1323 :class:`_expression.ColumnElement`.
1325 :param name: label name
1327 :param obj: a :class:`_expression.ColumnElement`.
1329 """
1330 return Label(name, element, type_)
1333def null() -> Null:
1334 """Return a constant :class:`.Null` construct."""
1336 return Null._instance()
1339def nulls_first(column: _ColumnExpressionArgument[_T]) -> UnaryExpression[_T]:
1340 """Produce the ``NULLS FIRST`` modifier for an ``ORDER BY`` expression.
1342 :func:`.nulls_first` is intended to modify the expression produced
1343 by :func:`.asc` or :func:`.desc`, and indicates how NULL values
1344 should be handled when they are encountered during ordering::
1347 from sqlalchemy import desc, nulls_first
1349 stmt = select(users_table).order_by(nulls_first(desc(users_table.c.name)))
1351 The SQL expression from the above would resemble:
1353 .. sourcecode:: sql
1355 SELECT id, name FROM user ORDER BY name DESC NULLS FIRST
1357 Like :func:`.asc` and :func:`.desc`, :func:`.nulls_first` is typically
1358 invoked from the column expression itself using
1359 :meth:`_expression.ColumnElement.nulls_first`,
1360 rather than as its standalone
1361 function version, as in::
1363 stmt = select(users_table).order_by(
1364 users_table.c.name.desc().nulls_first()
1365 )
1367 .. versionchanged:: 1.4 :func:`.nulls_first` is renamed from
1368 :func:`.nullsfirst` in previous releases.
1369 The previous name remains available for backwards compatibility.
1371 .. seealso::
1373 :func:`.asc`
1375 :func:`.desc`
1377 :func:`.nulls_last`
1379 :meth:`_expression.Select.order_by`
1381 """ # noqa: E501
1382 return UnaryExpression._create_nulls_first(column)
1385def nulls_last(column: _ColumnExpressionArgument[_T]) -> UnaryExpression[_T]:
1386 """Produce the ``NULLS LAST`` modifier for an ``ORDER BY`` expression.
1388 :func:`.nulls_last` is intended to modify the expression produced
1389 by :func:`.asc` or :func:`.desc`, and indicates how NULL values
1390 should be handled when they are encountered during ordering::
1393 from sqlalchemy import desc, nulls_last
1395 stmt = select(users_table).order_by(nulls_last(desc(users_table.c.name)))
1397 The SQL expression from the above would resemble:
1399 .. sourcecode:: sql
1401 SELECT id, name FROM user ORDER BY name DESC NULLS LAST
1403 Like :func:`.asc` and :func:`.desc`, :func:`.nulls_last` is typically
1404 invoked from the column expression itself using
1405 :meth:`_expression.ColumnElement.nulls_last`,
1406 rather than as its standalone
1407 function version, as in::
1409 stmt = select(users_table).order_by(users_table.c.name.desc().nulls_last())
1411 .. versionchanged:: 1.4 :func:`.nulls_last` is renamed from
1412 :func:`.nullslast` in previous releases.
1413 The previous name remains available for backwards compatibility.
1415 .. seealso::
1417 :func:`.asc`
1419 :func:`.desc`
1421 :func:`.nulls_first`
1423 :meth:`_expression.Select.order_by`
1425 """ # noqa: E501
1426 return UnaryExpression._create_nulls_last(column)
1429def or_( # type: ignore[empty-body]
1430 initial_clause: Union[Literal[False], _ColumnExpressionArgument[bool]],
1431 *clauses: _ColumnExpressionArgument[bool],
1432) -> ColumnElement[bool]:
1433 """Produce a conjunction of expressions joined by ``OR``.
1435 E.g.::
1437 from sqlalchemy import or_
1439 stmt = select(users_table).where(
1440 or_(users_table.c.name == "wendy", users_table.c.name == "jack")
1441 )
1443 The :func:`.or_` conjunction is also available using the
1444 Python ``|`` operator (though note that compound expressions
1445 need to be parenthesized in order to function with Python
1446 operator precedence behavior)::
1448 stmt = select(users_table).where(
1449 (users_table.c.name == "wendy") | (users_table.c.name == "jack")
1450 )
1452 The :func:`.or_` construct must be given at least one positional
1453 argument in order to be valid; a :func:`.or_` construct with no
1454 arguments is ambiguous. To produce an "empty" or dynamically
1455 generated :func:`.or_` expression, from a given list of expressions,
1456 a "default" element of :func:`_sql.false` (or just ``False``) should be
1457 specified::
1459 from sqlalchemy import false
1461 or_criteria = or_(false(), *expressions)
1463 The above expression will compile to SQL as the expression ``false``
1464 or ``0 = 1``, depending on backend, if no other expressions are
1465 present. If expressions are present, then the :func:`_sql.false` value is
1466 ignored as it does not affect the outcome of an OR expression which
1467 has other elements.
1469 .. deprecated:: 1.4 The :func:`.or_` element now requires that at
1470 least one argument is passed; creating the :func:`.or_` construct
1471 with no arguments is deprecated, and will emit a deprecation warning
1472 while continuing to produce a blank SQL string.
1474 .. seealso::
1476 :func:`.and_`
1478 """
1479 ...
1482if not TYPE_CHECKING:
1483 # handle deprecated case which allows zero-arguments
1484 def or_(*clauses): # noqa: F811
1485 """Produce a conjunction of expressions joined by ``OR``.
1487 E.g.::
1489 from sqlalchemy import or_
1491 stmt = select(users_table).where(
1492 or_(users_table.c.name == "wendy", users_table.c.name == "jack")
1493 )
1495 The :func:`.or_` conjunction is also available using the
1496 Python ``|`` operator (though note that compound expressions
1497 need to be parenthesized in order to function with Python
1498 operator precedence behavior)::
1500 stmt = select(users_table).where(
1501 (users_table.c.name == "wendy") | (users_table.c.name == "jack")
1502 )
1504 The :func:`.or_` construct must be given at least one positional
1505 argument in order to be valid; a :func:`.or_` construct with no
1506 arguments is ambiguous. To produce an "empty" or dynamically
1507 generated :func:`.or_` expression, from a given list of expressions,
1508 a "default" element of :func:`_sql.false` (or just ``False``) should be
1509 specified::
1511 from sqlalchemy import false
1513 or_criteria = or_(false(), *expressions)
1515 The above expression will compile to SQL as the expression ``false``
1516 or ``0 = 1``, depending on backend, if no other expressions are
1517 present. If expressions are present, then the :func:`_sql.false` value
1518 is ignored as it does not affect the outcome of an OR expression which
1519 has other elements.
1521 .. deprecated:: 1.4 The :func:`.or_` element now requires that at
1522 least one argument is passed; creating the :func:`.or_` construct
1523 with no arguments is deprecated, and will emit a deprecation warning
1524 while continuing to produce a blank SQL string.
1526 .. seealso::
1528 :func:`.and_`
1530 """ # noqa: E501
1531 return BooleanClauseList.or_(*clauses)
1534def over(
1535 element: FunctionElement[_T],
1536 partition_by: Optional[_ByArgument] = None,
1537 order_by: Optional[_ByArgument] = None,
1538 range_: Optional[typing_Tuple[Optional[int], Optional[int]]] = None,
1539 rows: Optional[typing_Tuple[Optional[int], Optional[int]]] = None,
1540 groups: Optional[typing_Tuple[Optional[int], Optional[int]]] = None,
1541) -> Over[_T]:
1542 r"""Produce an :class:`.Over` object against a function.
1544 Used against aggregate or so-called "window" functions,
1545 for database backends that support window functions.
1547 :func:`_expression.over` is usually called using
1548 the :meth:`.FunctionElement.over` method, e.g.::
1550 func.row_number().over(order_by=mytable.c.some_column)
1552 Would produce:
1554 .. sourcecode:: sql
1556 ROW_NUMBER() OVER(ORDER BY some_column)
1558 Ranges are also possible using the :paramref:`.expression.over.range_`,
1559 :paramref:`.expression.over.rows`, and :paramref:`.expression.over.groups`
1560 parameters. These
1561 mutually-exclusive parameters each accept a 2-tuple, which contains
1562 a combination of integers and None::
1564 func.row_number().over(order_by=my_table.c.some_column, range_=(None, 0))
1566 The above would produce:
1568 .. sourcecode:: sql
1570 ROW_NUMBER() OVER(ORDER BY some_column
1571 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
1573 A value of ``None`` indicates "unbounded", a
1574 value of zero indicates "current row", and negative / positive
1575 integers indicate "preceding" and "following":
1577 * RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING::
1579 func.row_number().over(order_by="x", range_=(-5, 10))
1581 * ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW::
1583 func.row_number().over(order_by="x", rows=(None, 0))
1585 * RANGE BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING::
1587 func.row_number().over(order_by="x", range_=(-2, None))
1589 * RANGE BETWEEN 1 FOLLOWING AND 3 FOLLOWING::
1591 func.row_number().over(order_by="x", range_=(1, 3))
1593 * GROUPS BETWEEN 1 FOLLOWING AND 3 FOLLOWING::
1595 func.row_number().over(order_by="x", groups=(1, 3))
1597 :param element: a :class:`.FunctionElement`, :class:`.WithinGroup`,
1598 or other compatible construct.
1599 :param partition_by: a column element or string, or a list
1600 of such, that will be used as the PARTITION BY clause
1601 of the OVER construct.
1602 :param order_by: a column element or string, or a list
1603 of such, that will be used as the ORDER BY clause
1604 of the OVER construct.
1605 :param range\_: optional range clause for the window. This is a
1606 tuple value which can contain integer values or ``None``,
1607 and will render a RANGE BETWEEN PRECEDING / FOLLOWING clause.
1608 :param rows: optional rows clause for the window. This is a tuple
1609 value which can contain integer values or None, and will render
1610 a ROWS BETWEEN PRECEDING / FOLLOWING clause.
1611 :param groups: optional groups clause for the window. This is a
1612 tuple value which can contain integer values or ``None``,
1613 and will render a GROUPS BETWEEN PRECEDING / FOLLOWING clause.
1615 .. versionadded:: 2.0.40
1617 This function is also available from the :data:`~.expression.func`
1618 construct itself via the :meth:`.FunctionElement.over` method.
1620 .. seealso::
1622 :ref:`tutorial_window_functions` - in the :ref:`unified_tutorial`
1624 :data:`.expression.func`
1626 :func:`_expression.within_group`
1628 """ # noqa: E501
1629 return Over(element, partition_by, order_by, range_, rows, groups)
1632@_document_text_coercion("text", ":func:`.text`", ":paramref:`.text.text`")
1633def text(text: str) -> TextClause:
1634 r"""Construct a new :class:`_expression.TextClause` clause,
1635 representing
1636 a textual SQL string directly.
1638 E.g.::
1640 from sqlalchemy import text
1642 t = text("SELECT * FROM users")
1643 result = connection.execute(t)
1645 The advantages :func:`_expression.text`
1646 provides over a plain string are
1647 backend-neutral support for bind parameters, per-statement
1648 execution options, as well as
1649 bind parameter and result-column typing behavior, allowing
1650 SQLAlchemy type constructs to play a role when executing
1651 a statement that is specified literally. The construct can also
1652 be provided with a ``.c`` collection of column elements, allowing
1653 it to be embedded in other SQL expression constructs as a subquery.
1655 Bind parameters are specified by name, using the format ``:name``.
1656 E.g.::
1658 t = text("SELECT * FROM users WHERE id=:user_id")
1659 result = connection.execute(t, {"user_id": 12})
1661 For SQL statements where a colon is required verbatim, as within
1662 an inline string, use a backslash to escape::
1664 t = text(r"SELECT * FROM users WHERE name='\:username'")
1666 The :class:`_expression.TextClause`
1667 construct includes methods which can
1668 provide information about the bound parameters as well as the column
1669 values which would be returned from the textual statement, assuming
1670 it's an executable SELECT type of statement. The
1671 :meth:`_expression.TextClause.bindparams`
1672 method is used to provide bound
1673 parameter detail, and :meth:`_expression.TextClause.columns`
1674 method allows
1675 specification of return columns including names and types::
1677 t = (
1678 text("SELECT * FROM users WHERE id=:user_id")
1679 .bindparams(user_id=7)
1680 .columns(id=Integer, name=String)
1681 )
1683 for id, name in connection.execute(t):
1684 print(id, name)
1686 The :func:`_expression.text` construct is used in cases when
1687 a literal string SQL fragment is specified as part of a larger query,
1688 such as for the WHERE clause of a SELECT statement::
1690 s = select(users.c.id, users.c.name).where(text("id=:user_id"))
1691 result = connection.execute(s, {"user_id": 12})
1693 :func:`_expression.text` is also used for the construction
1694 of a full, standalone statement using plain text.
1695 As such, SQLAlchemy refers
1696 to it as an :class:`.Executable` object and may be used
1697 like any other statement passed to an ``.execute()`` method.
1699 :param text:
1700 the text of the SQL statement to be created. Use ``:<param>``
1701 to specify bind parameters; they will be compiled to their
1702 engine-specific format.
1704 .. seealso::
1706 :ref:`tutorial_select_arbitrary_text`
1708 """
1709 return TextClause(text)
1712def true() -> True_:
1713 """Return a constant :class:`.True_` construct.
1715 E.g.:
1717 .. sourcecode:: pycon+sql
1719 >>> from sqlalchemy import true
1720 >>> print(select(t.c.x).where(true()))
1721 {printsql}SELECT x FROM t WHERE true
1723 A backend which does not support true/false constants will render as
1724 an expression against 1 or 0:
1726 .. sourcecode:: pycon+sql
1728 >>> print(select(t.c.x).where(true()))
1729 {printsql}SELECT x FROM t WHERE 1 = 1
1731 The :func:`.true` and :func:`.false` constants also feature
1732 "short circuit" operation within an :func:`.and_` or :func:`.or_`
1733 conjunction:
1735 .. sourcecode:: pycon+sql
1737 >>> print(select(t.c.x).where(or_(t.c.x > 5, true())))
1738 {printsql}SELECT x FROM t WHERE true{stop}
1740 >>> print(select(t.c.x).where(and_(t.c.x > 5, false())))
1741 {printsql}SELECT x FROM t WHERE false{stop}
1743 .. seealso::
1745 :func:`.false`
1747 """
1749 return True_._instance()
1752def tuple_(
1753 *clauses: _ColumnExpressionOrLiteralArgument[Any],
1754 types: Optional[Sequence[_TypeEngineArgument[Any]]] = None,
1755) -> Tuple:
1756 """Return a :class:`.Tuple`.
1758 Main usage is to produce a composite IN construct using
1759 :meth:`.ColumnOperators.in_` ::
1761 from sqlalchemy import tuple_
1763 tuple_(table.c.col1, table.c.col2).in_([(1, 2), (5, 12), (10, 19)])
1765 .. warning::
1767 The composite IN construct is not supported by all backends, and is
1768 currently known to work on PostgreSQL, MySQL, and SQLite.
1769 Unsupported backends will raise a subclass of
1770 :class:`~sqlalchemy.exc.DBAPIError` when such an expression is
1771 invoked.
1773 """
1774 return Tuple(*clauses, types=types)
1777def type_coerce(
1778 expression: _ColumnExpressionOrLiteralArgument[Any],
1779 type_: _TypeEngineArgument[_T],
1780) -> TypeCoerce[_T]:
1781 r"""Associate a SQL expression with a particular type, without rendering
1782 ``CAST``.
1784 E.g.::
1786 from sqlalchemy import type_coerce
1788 stmt = select(type_coerce(log_table.date_string, StringDateTime()))
1790 The above construct will produce a :class:`.TypeCoerce` object, which
1791 does not modify the rendering in any way on the SQL side, with the
1792 possible exception of a generated label if used in a columns clause
1793 context:
1795 .. sourcecode:: sql
1797 SELECT date_string AS date_string FROM log
1799 When result rows are fetched, the ``StringDateTime`` type processor
1800 will be applied to result rows on behalf of the ``date_string`` column.
1802 .. note:: the :func:`.type_coerce` construct does not render any
1803 SQL syntax of its own, including that it does not imply
1804 parenthesization. Please use :meth:`.TypeCoerce.self_group`
1805 if explicit parenthesization is required.
1807 In order to provide a named label for the expression, use
1808 :meth:`_expression.ColumnElement.label`::
1810 stmt = select(
1811 type_coerce(log_table.date_string, StringDateTime()).label("date")
1812 )
1814 A type that features bound-value handling will also have that behavior
1815 take effect when literal values or :func:`.bindparam` constructs are
1816 passed to :func:`.type_coerce` as targets.
1817 For example, if a type implements the
1818 :meth:`.TypeEngine.bind_expression`
1819 method or :meth:`.TypeEngine.bind_processor` method or equivalent,
1820 these functions will take effect at statement compilation/execution
1821 time when a literal value is passed, as in::
1823 # bound-value handling of MyStringType will be applied to the
1824 # literal value "some string"
1825 stmt = select(type_coerce("some string", MyStringType))
1827 When using :func:`.type_coerce` with composed expressions, note that
1828 **parenthesis are not applied**. If :func:`.type_coerce` is being
1829 used in an operator context where the parenthesis normally present from
1830 CAST are necessary, use the :meth:`.TypeCoerce.self_group` method:
1832 .. sourcecode:: pycon+sql
1834 >>> some_integer = column("someint", Integer)
1835 >>> some_string = column("somestr", String)
1836 >>> expr = type_coerce(some_integer + 5, String) + some_string
1837 >>> print(expr)
1838 {printsql}someint + :someint_1 || somestr{stop}
1839 >>> expr = type_coerce(some_integer + 5, String).self_group() + some_string
1840 >>> print(expr)
1841 {printsql}(someint + :someint_1) || somestr{stop}
1843 :param expression: A SQL expression, such as a
1844 :class:`_expression.ColumnElement`
1845 expression or a Python string which will be coerced into a bound
1846 literal value.
1848 :param type\_: A :class:`.TypeEngine` class or instance indicating
1849 the type to which the expression is coerced.
1851 .. seealso::
1853 :ref:`tutorial_casts`
1855 :func:`.cast`
1857 """ # noqa
1858 return TypeCoerce(expression, type_)
1861def within_group(
1862 element: FunctionElement[_T], *order_by: _ColumnExpressionArgument[Any]
1863) -> WithinGroup[_T]:
1864 r"""Produce a :class:`.WithinGroup` object against a function.
1866 Used against so-called "ordered set aggregate" and "hypothetical
1867 set aggregate" functions, including :class:`.percentile_cont`,
1868 :class:`.rank`, :class:`.dense_rank`, etc.
1870 :func:`_expression.within_group` is usually called using
1871 the :meth:`.FunctionElement.within_group` method, e.g.::
1873 from sqlalchemy import within_group
1875 stmt = select(
1876 department.c.id,
1877 func.percentile_cont(0.5).within_group(department.c.salary.desc()),
1878 )
1880 The above statement would produce SQL similar to
1881 ``SELECT department.id, percentile_cont(0.5)
1882 WITHIN GROUP (ORDER BY department.salary DESC)``.
1884 :param element: a :class:`.FunctionElement` construct, typically
1885 generated by :data:`~.expression.func`.
1886 :param \*order_by: one or more column elements that will be used
1887 as the ORDER BY clause of the WITHIN GROUP construct.
1889 .. seealso::
1891 :ref:`tutorial_functions_within_group` - in the
1892 :ref:`unified_tutorial`
1894 :data:`.expression.func`
1896 :func:`_expression.over`
1898 """
1899 return WithinGroup(element, *order_by)