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 Extract
35from .elements import False_
36from .elements import FunctionFilter
37from .elements import Label
38from .elements import Null
39from .elements import Over
40from .elements import TextClause
41from .elements import True_
42from .elements import TryCast
43from .elements import Tuple
44from .elements import TypeCoerce
45from .elements import UnaryExpression
46from .elements import WithinGroup
47from .functions import FunctionElement
48from ..util.typing import Literal
50if typing.TYPE_CHECKING:
51 from ._typing import _ByArgument
52 from ._typing import _ColumnExpressionArgument
53 from ._typing import _ColumnExpressionOrLiteralArgument
54 from ._typing import _ColumnExpressionOrStrLabelArgument
55 from ._typing import _TypeEngineArgument
56 from .elements import BinaryExpression
57 from .selectable import FromClause
58 from .type_api import TypeEngine
60_T = TypeVar("_T")
63def all_(expr: _ColumnExpressionArgument[_T]) -> CollectionAggregate[bool]:
64 """Produce an ALL expression.
66 For dialects such as that of PostgreSQL, this operator applies
67 to usage of the :class:`_types.ARRAY` datatype, for that of
68 MySQL, it may apply to a subquery. e.g.::
70 # renders on PostgreSQL:
71 # '5 = ALL (somearray)'
72 expr = 5 == all_(mytable.c.somearray)
74 # renders on MySQL:
75 # '5 = ALL (SELECT value FROM table)'
76 expr = 5 == all_(select(table.c.value))
78 Comparison to NULL may work using ``None``::
80 None == all_(mytable.c.somearray)
82 The any_() / all_() operators also feature a special "operand flipping"
83 behavior such that if any_() / all_() are used on the left side of a
84 comparison using a standalone operator such as ``==``, ``!=``, etc.
85 (not including operator methods such as
86 :meth:`_sql.ColumnOperators.is_`) the rendered expression is flipped::
88 # would render '5 = ALL (column)`
89 all_(mytable.c.column) == 5
91 Or with ``None``, which note will not perform
92 the usual step of rendering "IS" as is normally the case for NULL::
94 # would render 'NULL = ALL(somearray)'
95 all_(mytable.c.somearray) == None
97 .. versionchanged:: 1.4.26 repaired the use of any_() / all_()
98 comparing to NULL on the right side to be flipped to the left.
100 The column-level :meth:`_sql.ColumnElement.all_` method (not to be
101 confused with :class:`_types.ARRAY` level
102 :meth:`_types.ARRAY.Comparator.all`) is shorthand for
103 ``all_(col)``::
105 5 == mytable.c.somearray.all_()
107 .. seealso::
109 :meth:`_sql.ColumnOperators.all_`
111 :func:`_expression.any_`
113 """
114 return CollectionAggregate._create_all(expr)
117def and_( # type: ignore[empty-body]
118 initial_clause: Union[Literal[True], _ColumnExpressionArgument[bool]],
119 *clauses: _ColumnExpressionArgument[bool],
120) -> ColumnElement[bool]:
121 r"""Produce a conjunction of expressions joined by ``AND``.
123 E.g.::
125 from sqlalchemy import and_
127 stmt = select(users_table).where(
128 and_(users_table.c.name == "wendy", users_table.c.enrolled == True)
129 )
131 The :func:`.and_` conjunction is also available using the
132 Python ``&`` operator (though note that compound expressions
133 need to be parenthesized in order to function with Python
134 operator precedence behavior)::
136 stmt = select(users_table).where(
137 (users_table.c.name == "wendy") & (users_table.c.enrolled == True)
138 )
140 The :func:`.and_` operation is also implicit in some cases;
141 the :meth:`_expression.Select.where`
142 method for example can be invoked multiple
143 times against a statement, which will have the effect of each
144 clause being combined using :func:`.and_`::
146 stmt = (
147 select(users_table)
148 .where(users_table.c.name == "wendy")
149 .where(users_table.c.enrolled == True)
150 )
152 The :func:`.and_` construct must be given at least one positional
153 argument in order to be valid; a :func:`.and_` construct with no
154 arguments is ambiguous. To produce an "empty" or dynamically
155 generated :func:`.and_` expression, from a given list of expressions,
156 a "default" element of :func:`_sql.true` (or just ``True``) should be
157 specified::
159 from sqlalchemy import true
161 criteria = and_(true(), *expressions)
163 The above expression will compile to SQL as the expression ``true``
164 or ``1 = 1``, depending on backend, if no other expressions are
165 present. If expressions are present, then the :func:`_sql.true` value is
166 ignored as it does not affect the outcome of an AND expression that
167 has other elements.
169 .. deprecated:: 1.4 The :func:`.and_` element now requires that at
170 least one argument is passed; creating the :func:`.and_` construct
171 with no arguments is deprecated, and will emit a deprecation warning
172 while continuing to produce a blank SQL string.
174 .. seealso::
176 :func:`.or_`
178 """
179 ...
182if not TYPE_CHECKING:
183 # handle deprecated case which allows zero-arguments
184 def and_(*clauses): # noqa: F811
185 r"""Produce a conjunction of expressions joined by ``AND``.
187 E.g.::
189 from sqlalchemy import and_
191 stmt = select(users_table).where(
192 and_(users_table.c.name == "wendy", users_table.c.enrolled == True)
193 )
195 The :func:`.and_` conjunction is also available using the
196 Python ``&`` operator (though note that compound expressions
197 need to be parenthesized in order to function with Python
198 operator precedence behavior)::
200 stmt = select(users_table).where(
201 (users_table.c.name == "wendy") & (users_table.c.enrolled == True)
202 )
204 The :func:`.and_` operation is also implicit in some cases;
205 the :meth:`_expression.Select.where`
206 method for example can be invoked multiple
207 times against a statement, which will have the effect of each
208 clause being combined using :func:`.and_`::
210 stmt = (
211 select(users_table)
212 .where(users_table.c.name == "wendy")
213 .where(users_table.c.enrolled == True)
214 )
216 The :func:`.and_` construct must be given at least one positional
217 argument in order to be valid; a :func:`.and_` construct with no
218 arguments is ambiguous. To produce an "empty" or dynamically
219 generated :func:`.and_` expression, from a given list of expressions,
220 a "default" element of :func:`_sql.true` (or just ``True``) should be
221 specified::
223 from sqlalchemy import true
225 criteria = and_(true(), *expressions)
227 The above expression will compile to SQL as the expression ``true``
228 or ``1 = 1``, depending on backend, if no other expressions are
229 present. If expressions are present, then the :func:`_sql.true` value
230 is ignored as it does not affect the outcome of an AND expression that
231 has other elements.
233 .. deprecated:: 1.4 The :func:`.and_` element now requires that at
234 least one argument is passed; creating the :func:`.and_` construct
235 with no arguments is deprecated, and will emit a deprecation warning
236 while continuing to produce a blank SQL string.
238 .. seealso::
240 :func:`.or_`
242 """ # noqa: E501
243 return BooleanClauseList.and_(*clauses)
246def any_(expr: _ColumnExpressionArgument[_T]) -> CollectionAggregate[bool]:
247 """Produce an ANY expression.
249 For dialects such as that of PostgreSQL, this operator applies
250 to usage of the :class:`_types.ARRAY` datatype, for that of
251 MySQL, it may apply to a subquery. e.g.::
253 # renders on PostgreSQL:
254 # '5 = ANY (somearray)'
255 expr = 5 == any_(mytable.c.somearray)
257 # renders on MySQL:
258 # '5 = ANY (SELECT value FROM table)'
259 expr = 5 == any_(select(table.c.value))
261 Comparison to NULL may work using ``None`` or :func:`_sql.null`::
263 None == any_(mytable.c.somearray)
265 The any_() / all_() operators also feature a special "operand flipping"
266 behavior such that if any_() / all_() are used on the left side of a
267 comparison using a standalone operator such as ``==``, ``!=``, etc.
268 (not including operator methods such as
269 :meth:`_sql.ColumnOperators.is_`) the rendered expression is flipped::
271 # would render '5 = ANY (column)`
272 any_(mytable.c.column) == 5
274 Or with ``None``, which note will not perform
275 the usual step of rendering "IS" as is normally the case for NULL::
277 # would render 'NULL = ANY(somearray)'
278 any_(mytable.c.somearray) == None
280 .. versionchanged:: 1.4.26 repaired the use of any_() / all_()
281 comparing to NULL on the right side to be flipped to the left.
283 The column-level :meth:`_sql.ColumnElement.any_` method (not to be
284 confused with :class:`_types.ARRAY` level
285 :meth:`_types.ARRAY.Comparator.any`) is shorthand for
286 ``any_(col)``::
288 5 = mytable.c.somearray.any_()
290 .. seealso::
292 :meth:`_sql.ColumnOperators.any_`
294 :func:`_expression.all_`
296 """
297 return CollectionAggregate._create_any(expr)
300def asc(
301 column: _ColumnExpressionOrStrLabelArgument[_T],
302) -> UnaryExpression[_T]:
303 """Produce an ascending ``ORDER BY`` clause element.
305 e.g.::
307 from sqlalchemy import asc
309 stmt = select(users_table).order_by(asc(users_table.c.name))
311 will produce SQL as:
313 .. sourcecode:: sql
315 SELECT id, name FROM user ORDER BY name ASC
317 The :func:`.asc` function is a standalone version of the
318 :meth:`_expression.ColumnElement.asc`
319 method available on all SQL expressions,
320 e.g.::
323 stmt = select(users_table).order_by(users_table.c.name.asc())
325 :param column: A :class:`_expression.ColumnElement` (e.g.
326 scalar SQL expression)
327 with which to apply the :func:`.asc` operation.
329 .. seealso::
331 :func:`.desc`
333 :func:`.nulls_first`
335 :func:`.nulls_last`
337 :meth:`_expression.Select.order_by`
339 """
340 return UnaryExpression._create_asc(column)
343def collate(
344 expression: _ColumnExpressionArgument[str], collation: str
345) -> BinaryExpression[str]:
346 """Return the clause ``expression COLLATE collation``.
348 e.g.::
350 collate(mycolumn, "utf8_bin")
352 produces:
354 .. sourcecode:: sql
356 mycolumn COLLATE utf8_bin
358 The collation expression is also quoted if it is a case sensitive
359 identifier, e.g. contains uppercase characters.
361 """
362 return CollationClause._create_collation_expression(expression, collation)
365def between(
366 expr: _ColumnExpressionOrLiteralArgument[_T],
367 lower_bound: Any,
368 upper_bound: Any,
369 symmetric: bool = False,
370) -> BinaryExpression[bool]:
371 """Produce a ``BETWEEN`` predicate clause.
373 E.g.::
375 from sqlalchemy import between
377 stmt = select(users_table).where(between(users_table.c.id, 5, 7))
379 Would produce SQL resembling:
381 .. sourcecode:: sql
383 SELECT id, name FROM user WHERE id BETWEEN :id_1 AND :id_2
385 The :func:`.between` function is a standalone version of the
386 :meth:`_expression.ColumnElement.between` method available on all
387 SQL expressions, as in::
389 stmt = select(users_table).where(users_table.c.id.between(5, 7))
391 All arguments passed to :func:`.between`, including the left side
392 column expression, are coerced from Python scalar values if a
393 the value is not a :class:`_expression.ColumnElement` subclass.
394 For example,
395 three fixed values can be compared as in::
397 print(between(5, 3, 7))
399 Which would produce::
401 :param_1 BETWEEN :param_2 AND :param_3
403 :param expr: a column expression, typically a
404 :class:`_expression.ColumnElement`
405 instance or alternatively a Python scalar expression to be coerced
406 into a column expression, serving as the left side of the ``BETWEEN``
407 expression.
409 :param lower_bound: a column or Python scalar expression serving as the
410 lower bound of the right side of the ``BETWEEN`` expression.
412 :param upper_bound: a column or Python scalar expression serving as the
413 upper bound of the right side of the ``BETWEEN`` expression.
415 :param symmetric: if True, will render " BETWEEN SYMMETRIC ". Note
416 that not all databases support this syntax.
418 .. seealso::
420 :meth:`_expression.ColumnElement.between`
422 """
423 col_expr = coercions.expect(roles.ExpressionElementRole, expr)
424 return col_expr.between(lower_bound, upper_bound, symmetric=symmetric)
427def outparam(
428 key: str, type_: Optional[TypeEngine[_T]] = None
429) -> BindParameter[_T]:
430 """Create an 'OUT' parameter for usage in functions (stored procedures),
431 for databases which support them.
433 The ``outparam`` can be used like a regular function parameter.
434 The "output" value will be available from the
435 :class:`~sqlalchemy.engine.CursorResult` object via its ``out_parameters``
436 attribute, which returns a dictionary containing the values.
438 """
439 return BindParameter(key, None, type_=type_, unique=False, isoutparam=True)
442@overload
443def not_(clause: BinaryExpression[_T]) -> BinaryExpression[_T]: ...
446@overload
447def not_(clause: _ColumnExpressionArgument[_T]) -> ColumnElement[_T]: ...
450def not_(clause: _ColumnExpressionArgument[_T]) -> ColumnElement[_T]:
451 """Return a negation of the given clause, i.e. ``NOT(clause)``.
453 The ``~`` operator is also overloaded on all
454 :class:`_expression.ColumnElement` subclasses to produce the
455 same result.
457 """
459 return coercions.expect(roles.ExpressionElementRole, clause).__invert__()
462def bindparam(
463 key: Optional[str],
464 value: Any = _NoArg.NO_ARG,
465 type_: Optional[_TypeEngineArgument[_T]] = None,
466 unique: bool = False,
467 required: Union[bool, Literal[_NoArg.NO_ARG]] = _NoArg.NO_ARG,
468 quote: Optional[bool] = None,
469 callable_: Optional[Callable[[], Any]] = None,
470 expanding: bool = False,
471 isoutparam: bool = False,
472 literal_execute: bool = False,
473) -> BindParameter[_T]:
474 r"""Produce a "bound expression".
476 The return value is an instance of :class:`.BindParameter`; this
477 is a :class:`_expression.ColumnElement`
478 subclass which represents a so-called
479 "placeholder" value in a SQL expression, the value of which is
480 supplied at the point at which the statement in executed against a
481 database connection.
483 In SQLAlchemy, the :func:`.bindparam` construct has
484 the ability to carry along the actual value that will be ultimately
485 used at expression time. In this way, it serves not just as
486 a "placeholder" for eventual population, but also as a means of
487 representing so-called "unsafe" values which should not be rendered
488 directly in a SQL statement, but rather should be passed along
489 to the :term:`DBAPI` as values which need to be correctly escaped
490 and potentially handled for type-safety.
492 When using :func:`.bindparam` explicitly, the use case is typically
493 one of traditional deferment of parameters; the :func:`.bindparam`
494 construct accepts a name which can then be referred to at execution
495 time::
497 from sqlalchemy import bindparam
499 stmt = select(users_table).where(
500 users_table.c.name == bindparam("username")
501 )
503 The above statement, when rendered, will produce SQL similar to:
505 .. sourcecode:: sql
507 SELECT id, name FROM user WHERE name = :username
509 In order to populate the value of ``:username`` above, the value
510 would typically be applied at execution time to a method
511 like :meth:`_engine.Connection.execute`::
513 result = connection.execute(stmt, {"username": "wendy"})
515 Explicit use of :func:`.bindparam` is also common when producing
516 UPDATE or DELETE statements that are to be invoked multiple times,
517 where the WHERE criterion of the statement is to change on each
518 invocation, such as::
520 stmt = (
521 users_table.update()
522 .where(user_table.c.name == bindparam("username"))
523 .values(fullname=bindparam("fullname"))
524 )
526 connection.execute(
527 stmt,
528 [
529 {"username": "wendy", "fullname": "Wendy Smith"},
530 {"username": "jack", "fullname": "Jack Jones"},
531 ],
532 )
534 SQLAlchemy's Core expression system makes wide use of
535 :func:`.bindparam` in an implicit sense. It is typical that Python
536 literal values passed to virtually all SQL expression functions are
537 coerced into fixed :func:`.bindparam` constructs. For example, given
538 a comparison operation such as::
540 expr = users_table.c.name == "Wendy"
542 The above expression will produce a :class:`.BinaryExpression`
543 construct, where the left side is the :class:`_schema.Column` object
544 representing the ``name`` column, and the right side is a
545 :class:`.BindParameter` representing the literal value::
547 print(repr(expr.right))
548 BindParameter("%(4327771088 name)s", "Wendy", type_=String())
550 The expression above will render SQL such as:
552 .. sourcecode:: sql
554 user.name = :name_1
556 Where the ``:name_1`` parameter name is an anonymous name. The
557 actual string ``Wendy`` is not in the rendered string, but is carried
558 along where it is later used within statement execution. If we
559 invoke a statement like the following::
561 stmt = select(users_table).where(users_table.c.name == "Wendy")
562 result = connection.execute(stmt)
564 We would see SQL logging output as:
566 .. sourcecode:: sql
568 SELECT "user".id, "user".name
569 FROM "user"
570 WHERE "user".name = %(name_1)s
571 {'name_1': 'Wendy'}
573 Above, we see that ``Wendy`` is passed as a parameter to the database,
574 while the placeholder ``:name_1`` is rendered in the appropriate form
575 for the target database, in this case the PostgreSQL database.
577 Similarly, :func:`.bindparam` is invoked automatically when working
578 with :term:`CRUD` statements as far as the "VALUES" portion is
579 concerned. The :func:`_expression.insert` construct produces an
580 ``INSERT`` expression which will, at statement execution time, generate
581 bound placeholders based on the arguments passed, as in::
583 stmt = users_table.insert()
584 result = connection.execute(stmt, {"name": "Wendy"})
586 The above will produce SQL output as:
588 .. sourcecode:: sql
590 INSERT INTO "user" (name) VALUES (%(name)s)
591 {'name': 'Wendy'}
593 The :class:`_expression.Insert` construct, at
594 compilation/execution time, rendered a single :func:`.bindparam`
595 mirroring the column name ``name`` as a result of the single ``name``
596 parameter we passed to the :meth:`_engine.Connection.execute` method.
598 :param key:
599 the key (e.g. the name) for this bind param.
600 Will be used in the generated
601 SQL statement for dialects that use named parameters. This
602 value may be modified when part of a compilation operation,
603 if other :class:`BindParameter` objects exist with the same
604 key, or if its length is too long and truncation is
605 required.
607 If omitted, an "anonymous" name is generated for the bound parameter;
608 when given a value to bind, the end result is equivalent to calling upon
609 the :func:`.literal` function with a value to bind, particularly
610 if the :paramref:`.bindparam.unique` parameter is also provided.
612 :param value:
613 Initial value for this bind param. Will be used at statement
614 execution time as the value for this parameter passed to the
615 DBAPI, if no other value is indicated to the statement execution
616 method for this particular parameter name. Defaults to ``None``.
618 :param callable\_:
619 A callable function that takes the place of "value". The function
620 will be called at statement execution time to determine the
621 ultimate value. Used for scenarios where the actual bind
622 value cannot be determined at the point at which the clause
623 construct is created, but embedded bind values are still desirable.
625 :param type\_:
626 A :class:`.TypeEngine` class or instance representing an optional
627 datatype for this :func:`.bindparam`. If not passed, a type
628 may be determined automatically for the bind, based on the given
629 value; for example, trivial Python types such as ``str``,
630 ``int``, ``bool``
631 may result in the :class:`.String`, :class:`.Integer` or
632 :class:`.Boolean` types being automatically selected.
634 The type of a :func:`.bindparam` is significant especially in that
635 the type will apply pre-processing to the value before it is
636 passed to the database. For example, a :func:`.bindparam` which
637 refers to a datetime value, and is specified as holding the
638 :class:`.DateTime` type, may apply conversion needed to the
639 value (such as stringification on SQLite) before passing the value
640 to the database.
642 :param unique:
643 if True, the key name of this :class:`.BindParameter` will be
644 modified if another :class:`.BindParameter` of the same name
645 already has been located within the containing
646 expression. This flag is used generally by the internals
647 when producing so-called "anonymous" bound expressions, it
648 isn't generally applicable to explicitly-named :func:`.bindparam`
649 constructs.
651 :param required:
652 If ``True``, a value is required at execution time. If not passed,
653 it defaults to ``True`` if neither :paramref:`.bindparam.value`
654 or :paramref:`.bindparam.callable` were passed. If either of these
655 parameters are present, then :paramref:`.bindparam.required`
656 defaults to ``False``.
658 :param quote:
659 True if this parameter name requires quoting and is not
660 currently known as a SQLAlchemy reserved word; this currently
661 only applies to the Oracle Database backends, where bound names must
662 sometimes be quoted.
664 :param isoutparam:
665 if True, the parameter should be treated like a stored procedure
666 "OUT" parameter. This applies to backends such as Oracle Database which
667 support OUT parameters.
669 :param expanding:
670 if True, this parameter will be treated as an "expanding" parameter
671 at execution time; the parameter value is expected to be a sequence,
672 rather than a scalar value, and the string SQL statement will
673 be transformed on a per-execution basis to accommodate the sequence
674 with a variable number of parameter slots passed to the DBAPI.
675 This is to allow statement caching to be used in conjunction with
676 an IN clause.
678 .. seealso::
680 :meth:`.ColumnOperators.in_`
682 :ref:`baked_in` - with baked queries
684 .. note:: The "expanding" feature does not support "executemany"-
685 style parameter sets.
687 :param literal_execute:
688 if True, the bound parameter will be rendered in the compile phase
689 with a special "POSTCOMPILE" token, and the SQLAlchemy compiler will
690 render the final value of the parameter into the SQL statement at
691 statement execution time, omitting the value from the parameter
692 dictionary / list passed to DBAPI ``cursor.execute()``. This
693 produces a similar effect as that of using the ``literal_binds``,
694 compilation flag, however takes place as the statement is sent to
695 the DBAPI ``cursor.execute()`` method, rather than when the statement
696 is compiled. The primary use of this
697 capability is for rendering LIMIT / OFFSET clauses for database
698 drivers that can't accommodate for bound parameters in these
699 contexts, while allowing SQL constructs to be cacheable at the
700 compilation level.
702 .. versionadded:: 1.4 Added "post compile" bound parameters
704 .. seealso::
706 :ref:`change_4808`.
708 .. seealso::
710 :ref:`tutorial_sending_parameters` - in the
711 :ref:`unified_tutorial`
714 """
715 return BindParameter(
716 key,
717 value,
718 type_,
719 unique,
720 required,
721 quote,
722 callable_,
723 expanding,
724 isoutparam,
725 literal_execute,
726 )
729def case(
730 *whens: Union[
731 typing_Tuple[_ColumnExpressionArgument[bool], Any], Mapping[Any, Any]
732 ],
733 value: Optional[Any] = None,
734 else_: Optional[Any] = None,
735) -> Case[Any]:
736 r"""Produce a ``CASE`` expression.
738 The ``CASE`` construct in SQL is a conditional object that
739 acts somewhat analogously to an "if/then" construct in other
740 languages. It returns an instance of :class:`.Case`.
742 :func:`.case` in its usual form is passed a series of "when"
743 constructs, that is, a list of conditions and results as tuples::
745 from sqlalchemy import case
747 stmt = select(users_table).where(
748 case(
749 (users_table.c.name == "wendy", "W"),
750 (users_table.c.name == "jack", "J"),
751 else_="E",
752 )
753 )
755 The above statement will produce SQL resembling:
757 .. sourcecode:: sql
759 SELECT id, name FROM user
760 WHERE CASE
761 WHEN (name = :name_1) THEN :param_1
762 WHEN (name = :name_2) THEN :param_2
763 ELSE :param_3
764 END
766 When simple equality expressions of several values against a single
767 parent column are needed, :func:`.case` also has a "shorthand" format
768 used via the
769 :paramref:`.case.value` parameter, which is passed a column
770 expression to be compared. In this form, the :paramref:`.case.whens`
771 parameter is passed as a dictionary containing expressions to be
772 compared against keyed to result expressions. The statement below is
773 equivalent to the preceding statement::
775 stmt = select(users_table).where(
776 case({"wendy": "W", "jack": "J"}, value=users_table.c.name, else_="E")
777 )
779 The values which are accepted as result values in
780 :paramref:`.case.whens` as well as with :paramref:`.case.else_` are
781 coerced from Python literals into :func:`.bindparam` constructs.
782 SQL expressions, e.g. :class:`_expression.ColumnElement` constructs,
783 are accepted
784 as well. To coerce a literal string expression into a constant
785 expression rendered inline, use the :func:`_expression.literal_column`
786 construct,
787 as in::
789 from sqlalchemy import case, literal_column
791 case(
792 (orderline.c.qty > 100, literal_column("'greaterthan100'")),
793 (orderline.c.qty > 10, literal_column("'greaterthan10'")),
794 else_=literal_column("'lessthan10'"),
795 )
797 The above will render the given constants without using bound
798 parameters for the result values (but still for the comparison
799 values), as in:
801 .. sourcecode:: sql
803 CASE
804 WHEN (orderline.qty > :qty_1) THEN 'greaterthan100'
805 WHEN (orderline.qty > :qty_2) THEN 'greaterthan10'
806 ELSE 'lessthan10'
807 END
809 :param \*whens: The criteria to be compared against,
810 :paramref:`.case.whens` accepts two different forms, based on
811 whether or not :paramref:`.case.value` is used.
813 .. versionchanged:: 1.4 the :func:`_sql.case`
814 function now accepts the series of WHEN conditions positionally
816 In the first form, it accepts multiple 2-tuples passed as positional
817 arguments; each 2-tuple consists of ``(<sql expression>, <value>)``,
818 where the SQL expression is a boolean expression and "value" is a
819 resulting value, e.g.::
821 case(
822 (users_table.c.name == "wendy", "W"),
823 (users_table.c.name == "jack", "J"),
824 )
826 In the second form, it accepts a Python dictionary of comparison
827 values mapped to a resulting value; this form requires
828 :paramref:`.case.value` to be present, and values will be compared
829 using the ``==`` operator, e.g.::
831 case({"wendy": "W", "jack": "J"}, value=users_table.c.name)
833 :param value: An optional SQL expression which will be used as a
834 fixed "comparison point" for candidate values within a dictionary
835 passed to :paramref:`.case.whens`.
837 :param else\_: An optional SQL expression which will be the evaluated
838 result of the ``CASE`` construct if all expressions within
839 :paramref:`.case.whens` evaluate to false. When omitted, most
840 databases will produce a result of NULL if none of the "when"
841 expressions evaluate to true.
844 """ # noqa: E501
845 return Case(*whens, value=value, else_=else_)
848def cast(
849 expression: _ColumnExpressionOrLiteralArgument[Any],
850 type_: _TypeEngineArgument[_T],
851) -> Cast[_T]:
852 r"""Produce a ``CAST`` expression.
854 :func:`.cast` returns an instance of :class:`.Cast`.
856 E.g.::
858 from sqlalchemy import cast, Numeric
860 stmt = select(cast(product_table.c.unit_price, Numeric(10, 4)))
862 The above statement will produce SQL resembling:
864 .. sourcecode:: sql
866 SELECT CAST(unit_price AS NUMERIC(10, 4)) FROM product
868 The :func:`.cast` function performs two distinct functions when
869 used. The first is that it renders the ``CAST`` expression within
870 the resulting SQL string. The second is that it associates the given
871 type (e.g. :class:`.TypeEngine` class or instance) with the column
872 expression on the Python side, which means the expression will take
873 on the expression operator behavior associated with that type,
874 as well as the bound-value handling and result-row-handling behavior
875 of the type.
877 An alternative to :func:`.cast` is the :func:`.type_coerce` function.
878 This function performs the second task of associating an expression
879 with a specific type, but does not render the ``CAST`` expression
880 in SQL.
882 :param expression: A SQL expression, such as a
883 :class:`_expression.ColumnElement`
884 expression or a Python string which will be coerced into a bound
885 literal value.
887 :param type\_: A :class:`.TypeEngine` class or instance indicating
888 the type to which the ``CAST`` should apply.
890 .. seealso::
892 :ref:`tutorial_casts`
894 :func:`.try_cast` - an alternative to CAST that results in
895 NULLs when the cast fails, instead of raising an error.
896 Only supported by some dialects.
898 :func:`.type_coerce` - an alternative to CAST that coerces the type
899 on the Python side only, which is often sufficient to generate the
900 correct SQL and data coercion.
903 """
904 return Cast(expression, type_)
907def try_cast(
908 expression: _ColumnExpressionOrLiteralArgument[Any],
909 type_: _TypeEngineArgument[_T],
910) -> TryCast[_T]:
911 """Produce a ``TRY_CAST`` expression for backends which support it;
912 this is a ``CAST`` which returns NULL for un-castable conversions.
914 In SQLAlchemy, this construct is supported **only** by the SQL Server
915 dialect, and will raise a :class:`.CompileError` if used on other
916 included backends. However, third party backends may also support
917 this construct.
919 .. tip:: As :func:`_sql.try_cast` originates from the SQL Server dialect,
920 it's importable both from ``sqlalchemy.`` as well as from
921 ``sqlalchemy.dialects.mssql``.
923 :func:`_sql.try_cast` returns an instance of :class:`.TryCast` and
924 generally behaves similarly to the :class:`.Cast` construct;
925 at the SQL level, the difference between ``CAST`` and ``TRY_CAST``
926 is that ``TRY_CAST`` returns NULL for an un-castable expression,
927 such as attempting to cast a string ``"hi"`` to an integer value.
929 E.g.::
931 from sqlalchemy import select, try_cast, Numeric
933 stmt = select(try_cast(product_table.c.unit_price, Numeric(10, 4)))
935 The above would render on Microsoft SQL Server as:
937 .. sourcecode:: sql
939 SELECT TRY_CAST (product_table.unit_price AS NUMERIC(10, 4))
940 FROM product_table
942 .. versionadded:: 2.0.14 :func:`.try_cast` has been
943 generalized from the SQL Server dialect into a general use
944 construct that may be supported by additional dialects.
946 """
947 return TryCast(expression, type_)
950def column(
951 text: str,
952 type_: Optional[_TypeEngineArgument[_T]] = None,
953 is_literal: bool = False,
954 _selectable: Optional[FromClause] = None,
955) -> ColumnClause[_T]:
956 """Produce a :class:`.ColumnClause` object.
958 The :class:`.ColumnClause` is a lightweight analogue to the
959 :class:`_schema.Column` class. The :func:`_expression.column`
960 function can
961 be invoked with just a name alone, as in::
963 from sqlalchemy import column
965 id, name = column("id"), column("name")
966 stmt = select(id, name).select_from("user")
968 The above statement would produce SQL like:
970 .. sourcecode:: sql
972 SELECT id, name FROM user
974 Once constructed, :func:`_expression.column`
975 may be used like any other SQL
976 expression element such as within :func:`_expression.select`
977 constructs::
979 from sqlalchemy.sql import column
981 id, name = column("id"), column("name")
982 stmt = select(id, name).select_from("user")
984 The text handled by :func:`_expression.column`
985 is assumed to be handled
986 like the name of a database column; if the string contains mixed case,
987 special characters, or matches a known reserved word on the target
988 backend, the column expression will render using the quoting
989 behavior determined by the backend. To produce a textual SQL
990 expression that is rendered exactly without any quoting,
991 use :func:`_expression.literal_column` instead,
992 or pass ``True`` as the
993 value of :paramref:`_expression.column.is_literal`. Additionally,
994 full SQL
995 statements are best handled using the :func:`_expression.text`
996 construct.
998 :func:`_expression.column` can be used in a table-like
999 fashion by combining it with the :func:`.table` function
1000 (which is the lightweight analogue to :class:`_schema.Table`
1001 ) to produce
1002 a working table construct with minimal boilerplate::
1004 from sqlalchemy import table, column, select
1006 user = table(
1007 "user",
1008 column("id"),
1009 column("name"),
1010 column("description"),
1011 )
1013 stmt = select(user.c.description).where(user.c.name == "wendy")
1015 A :func:`_expression.column` / :func:`.table`
1016 construct like that illustrated
1017 above can be created in an
1018 ad-hoc fashion and is not associated with any
1019 :class:`_schema.MetaData`, DDL, or events, unlike its
1020 :class:`_schema.Table` counterpart.
1022 :param text: the text of the element.
1024 :param type: :class:`_types.TypeEngine` object which can associate
1025 this :class:`.ColumnClause` with a type.
1027 :param is_literal: if True, the :class:`.ColumnClause` is assumed to
1028 be an exact expression that will be delivered to the output with no
1029 quoting rules applied regardless of case sensitive settings. the
1030 :func:`_expression.literal_column()` function essentially invokes
1031 :func:`_expression.column` while passing ``is_literal=True``.
1033 .. seealso::
1035 :class:`_schema.Column`
1037 :func:`_expression.literal_column`
1039 :func:`.table`
1041 :func:`_expression.text`
1043 :ref:`tutorial_select_arbitrary_text`
1045 """
1046 return ColumnClause(text, type_, is_literal, _selectable)
1049def desc(
1050 column: _ColumnExpressionOrStrLabelArgument[_T],
1051) -> UnaryExpression[_T]:
1052 """Produce a descending ``ORDER BY`` clause element.
1054 e.g.::
1056 from sqlalchemy import desc
1058 stmt = select(users_table).order_by(desc(users_table.c.name))
1060 will produce SQL as:
1062 .. sourcecode:: sql
1064 SELECT id, name FROM user ORDER BY name DESC
1066 The :func:`.desc` function is a standalone version of the
1067 :meth:`_expression.ColumnElement.desc`
1068 method available on all SQL expressions,
1069 e.g.::
1072 stmt = select(users_table).order_by(users_table.c.name.desc())
1074 :param column: A :class:`_expression.ColumnElement` (e.g.
1075 scalar SQL expression)
1076 with which to apply the :func:`.desc` operation.
1078 .. seealso::
1080 :func:`.asc`
1082 :func:`.nulls_first`
1084 :func:`.nulls_last`
1086 :meth:`_expression.Select.order_by`
1088 """
1089 return UnaryExpression._create_desc(column)
1092def distinct(expr: _ColumnExpressionArgument[_T]) -> UnaryExpression[_T]:
1093 """Produce an column-expression-level unary ``DISTINCT`` clause.
1095 This applies the ``DISTINCT`` keyword to an **individual column
1096 expression** (e.g. not the whole statement), and renders **specifically
1097 in that column position**; this is used for containment within
1098 an aggregate function, as in::
1100 from sqlalchemy import distinct, func
1102 stmt = select(users_table.c.id, func.count(distinct(users_table.c.name)))
1104 The above would produce an statement resembling:
1106 .. sourcecode:: sql
1108 SELECT user.id, count(DISTINCT user.name) FROM user
1110 .. tip:: The :func:`_sql.distinct` function does **not** apply DISTINCT
1111 to the full SELECT statement, instead applying a DISTINCT modifier
1112 to **individual column expressions**. For general ``SELECT DISTINCT``
1113 support, use the
1114 :meth:`_sql.Select.distinct` method on :class:`_sql.Select`.
1116 The :func:`.distinct` function is also available as a column-level
1117 method, e.g. :meth:`_expression.ColumnElement.distinct`, as in::
1119 stmt = select(func.count(users_table.c.name.distinct()))
1121 The :func:`.distinct` operator is different from the
1122 :meth:`_expression.Select.distinct` method of
1123 :class:`_expression.Select`,
1124 which produces a ``SELECT`` statement
1125 with ``DISTINCT`` applied to the result set as a whole,
1126 e.g. a ``SELECT DISTINCT`` expression. See that method for further
1127 information.
1129 .. seealso::
1131 :meth:`_expression.ColumnElement.distinct`
1133 :meth:`_expression.Select.distinct`
1135 :data:`.func`
1137 """ # noqa: E501
1138 return UnaryExpression._create_distinct(expr)
1141def bitwise_not(expr: _ColumnExpressionArgument[_T]) -> UnaryExpression[_T]:
1142 """Produce a unary bitwise NOT clause, typically via the ``~`` operator.
1144 Not to be confused with boolean negation :func:`_sql.not_`.
1146 .. versionadded:: 2.0.2
1148 .. seealso::
1150 :ref:`operators_bitwise`
1153 """
1155 return UnaryExpression._create_bitwise_not(expr)
1158def extract(field: str, expr: _ColumnExpressionArgument[Any]) -> Extract:
1159 """Return a :class:`.Extract` construct.
1161 This is typically available as :func:`.extract`
1162 as well as ``func.extract`` from the
1163 :data:`.func` namespace.
1165 :param field: The field to extract.
1167 .. warning:: This field is used as a literal SQL string.
1168 **DO NOT PASS UNTRUSTED INPUT TO THIS STRING**.
1170 :param expr: A column or Python scalar expression serving as the
1171 right side of the ``EXTRACT`` expression.
1173 E.g.::
1175 from sqlalchemy import extract
1176 from sqlalchemy import table, column
1178 logged_table = table(
1179 "user",
1180 column("id"),
1181 column("date_created"),
1182 )
1184 stmt = select(logged_table.c.id).where(
1185 extract("YEAR", logged_table.c.date_created) == 2021
1186 )
1188 In the above example, the statement is used to select ids from the
1189 database where the ``YEAR`` component matches a specific value.
1191 Similarly, one can also select an extracted component::
1193 stmt = select(extract("YEAR", logged_table.c.date_created)).where(
1194 logged_table.c.id == 1
1195 )
1197 The implementation of ``EXTRACT`` may vary across database backends.
1198 Users are reminded to consult their database documentation.
1199 """
1200 return Extract(field, expr)
1203def false() -> False_:
1204 """Return a :class:`.False_` construct.
1206 E.g.:
1208 .. sourcecode:: pycon+sql
1210 >>> from sqlalchemy import false
1211 >>> print(select(t.c.x).where(false()))
1212 {printsql}SELECT x FROM t WHERE false
1214 A backend which does not support true/false constants will render as
1215 an expression against 1 or 0:
1217 .. sourcecode:: pycon+sql
1219 >>> print(select(t.c.x).where(false()))
1220 {printsql}SELECT x FROM t WHERE 0 = 1
1222 The :func:`.true` and :func:`.false` constants also feature
1223 "short circuit" operation within an :func:`.and_` or :func:`.or_`
1224 conjunction:
1226 .. sourcecode:: pycon+sql
1228 >>> print(select(t.c.x).where(or_(t.c.x > 5, true())))
1229 {printsql}SELECT x FROM t WHERE true{stop}
1231 >>> print(select(t.c.x).where(and_(t.c.x > 5, false())))
1232 {printsql}SELECT x FROM t WHERE false{stop}
1234 .. seealso::
1236 :func:`.true`
1238 """
1240 return False_._instance()
1243def funcfilter(
1244 func: FunctionElement[_T], *criterion: _ColumnExpressionArgument[bool]
1245) -> FunctionFilter[_T]:
1246 """Produce a :class:`.FunctionFilter` object against a function.
1248 Used against aggregate and window functions,
1249 for database backends that support the "FILTER" clause.
1251 E.g.::
1253 from sqlalchemy import funcfilter
1255 funcfilter(func.count(1), MyClass.name == "some name")
1257 Would produce "COUNT(1) FILTER (WHERE myclass.name = 'some name')".
1259 This function is also available from the :data:`~.expression.func`
1260 construct itself via the :meth:`.FunctionElement.filter` method.
1262 .. seealso::
1264 :ref:`tutorial_functions_within_group` - in the
1265 :ref:`unified_tutorial`
1267 :meth:`.FunctionElement.filter`
1269 """
1270 return FunctionFilter(func, *criterion)
1273def label(
1274 name: str,
1275 element: _ColumnExpressionArgument[_T],
1276 type_: Optional[_TypeEngineArgument[_T]] = None,
1277) -> Label[_T]:
1278 """Return a :class:`Label` object for the
1279 given :class:`_expression.ColumnElement`.
1281 A label changes the name of an element in the columns clause of a
1282 ``SELECT`` statement, typically via the ``AS`` SQL keyword.
1284 This functionality is more conveniently available via the
1285 :meth:`_expression.ColumnElement.label` method on
1286 :class:`_expression.ColumnElement`.
1288 :param name: label name
1290 :param obj: a :class:`_expression.ColumnElement`.
1292 """
1293 return Label(name, element, type_)
1296def null() -> Null:
1297 """Return a constant :class:`.Null` construct."""
1299 return Null._instance()
1302def nulls_first(column: _ColumnExpressionArgument[_T]) -> UnaryExpression[_T]:
1303 """Produce the ``NULLS FIRST`` modifier for an ``ORDER BY`` expression.
1305 :func:`.nulls_first` is intended to modify the expression produced
1306 by :func:`.asc` or :func:`.desc`, and indicates how NULL values
1307 should be handled when they are encountered during ordering::
1310 from sqlalchemy import desc, nulls_first
1312 stmt = select(users_table).order_by(nulls_first(desc(users_table.c.name)))
1314 The SQL expression from the above would resemble:
1316 .. sourcecode:: sql
1318 SELECT id, name FROM user ORDER BY name DESC NULLS FIRST
1320 Like :func:`.asc` and :func:`.desc`, :func:`.nulls_first` is typically
1321 invoked from the column expression itself using
1322 :meth:`_expression.ColumnElement.nulls_first`,
1323 rather than as its standalone
1324 function version, as in::
1326 stmt = select(users_table).order_by(
1327 users_table.c.name.desc().nulls_first()
1328 )
1330 .. versionchanged:: 1.4 :func:`.nulls_first` is renamed from
1331 :func:`.nullsfirst` in previous releases.
1332 The previous name remains available for backwards compatibility.
1334 .. seealso::
1336 :func:`.asc`
1338 :func:`.desc`
1340 :func:`.nulls_last`
1342 :meth:`_expression.Select.order_by`
1344 """ # noqa: E501
1345 return UnaryExpression._create_nulls_first(column)
1348def nulls_last(column: _ColumnExpressionArgument[_T]) -> UnaryExpression[_T]:
1349 """Produce the ``NULLS LAST`` modifier for an ``ORDER BY`` expression.
1351 :func:`.nulls_last` is intended to modify the expression produced
1352 by :func:`.asc` or :func:`.desc`, and indicates how NULL values
1353 should be handled when they are encountered during ordering::
1356 from sqlalchemy import desc, nulls_last
1358 stmt = select(users_table).order_by(nulls_last(desc(users_table.c.name)))
1360 The SQL expression from the above would resemble:
1362 .. sourcecode:: sql
1364 SELECT id, name FROM user ORDER BY name DESC NULLS LAST
1366 Like :func:`.asc` and :func:`.desc`, :func:`.nulls_last` is typically
1367 invoked from the column expression itself using
1368 :meth:`_expression.ColumnElement.nulls_last`,
1369 rather than as its standalone
1370 function version, as in::
1372 stmt = select(users_table).order_by(users_table.c.name.desc().nulls_last())
1374 .. versionchanged:: 1.4 :func:`.nulls_last` is renamed from
1375 :func:`.nullslast` in previous releases.
1376 The previous name remains available for backwards compatibility.
1378 .. seealso::
1380 :func:`.asc`
1382 :func:`.desc`
1384 :func:`.nulls_first`
1386 :meth:`_expression.Select.order_by`
1388 """ # noqa: E501
1389 return UnaryExpression._create_nulls_last(column)
1392def or_( # type: ignore[empty-body]
1393 initial_clause: Union[Literal[False], _ColumnExpressionArgument[bool]],
1394 *clauses: _ColumnExpressionArgument[bool],
1395) -> ColumnElement[bool]:
1396 """Produce a conjunction of expressions joined by ``OR``.
1398 E.g.::
1400 from sqlalchemy import or_
1402 stmt = select(users_table).where(
1403 or_(users_table.c.name == "wendy", users_table.c.name == "jack")
1404 )
1406 The :func:`.or_` conjunction is also available using the
1407 Python ``|`` operator (though note that compound expressions
1408 need to be parenthesized in order to function with Python
1409 operator precedence behavior)::
1411 stmt = select(users_table).where(
1412 (users_table.c.name == "wendy") | (users_table.c.name == "jack")
1413 )
1415 The :func:`.or_` construct must be given at least one positional
1416 argument in order to be valid; a :func:`.or_` construct with no
1417 arguments is ambiguous. To produce an "empty" or dynamically
1418 generated :func:`.or_` expression, from a given list of expressions,
1419 a "default" element of :func:`_sql.false` (or just ``False``) should be
1420 specified::
1422 from sqlalchemy import false
1424 or_criteria = or_(false(), *expressions)
1426 The above expression will compile to SQL as the expression ``false``
1427 or ``0 = 1``, depending on backend, if no other expressions are
1428 present. If expressions are present, then the :func:`_sql.false` value is
1429 ignored as it does not affect the outcome of an OR expression which
1430 has other elements.
1432 .. deprecated:: 1.4 The :func:`.or_` element now requires that at
1433 least one argument is passed; creating the :func:`.or_` construct
1434 with no arguments is deprecated, and will emit a deprecation warning
1435 while continuing to produce a blank SQL string.
1437 .. seealso::
1439 :func:`.and_`
1441 """
1442 ...
1445if not TYPE_CHECKING:
1446 # handle deprecated case which allows zero-arguments
1447 def or_(*clauses): # noqa: F811
1448 """Produce a conjunction of expressions joined by ``OR``.
1450 E.g.::
1452 from sqlalchemy import or_
1454 stmt = select(users_table).where(
1455 or_(users_table.c.name == "wendy", users_table.c.name == "jack")
1456 )
1458 The :func:`.or_` conjunction is also available using the
1459 Python ``|`` operator (though note that compound expressions
1460 need to be parenthesized in order to function with Python
1461 operator precedence behavior)::
1463 stmt = select(users_table).where(
1464 (users_table.c.name == "wendy") | (users_table.c.name == "jack")
1465 )
1467 The :func:`.or_` construct must be given at least one positional
1468 argument in order to be valid; a :func:`.or_` construct with no
1469 arguments is ambiguous. To produce an "empty" or dynamically
1470 generated :func:`.or_` expression, from a given list of expressions,
1471 a "default" element of :func:`_sql.false` (or just ``False``) should be
1472 specified::
1474 from sqlalchemy import false
1476 or_criteria = or_(false(), *expressions)
1478 The above expression will compile to SQL as the expression ``false``
1479 or ``0 = 1``, depending on backend, if no other expressions are
1480 present. If expressions are present, then the :func:`_sql.false` value
1481 is ignored as it does not affect the outcome of an OR expression which
1482 has other elements.
1484 .. deprecated:: 1.4 The :func:`.or_` element now requires that at
1485 least one argument is passed; creating the :func:`.or_` construct
1486 with no arguments is deprecated, and will emit a deprecation warning
1487 while continuing to produce a blank SQL string.
1489 .. seealso::
1491 :func:`.and_`
1493 """ # noqa: E501
1494 return BooleanClauseList.or_(*clauses)
1497def over(
1498 element: FunctionElement[_T],
1499 partition_by: Optional[_ByArgument] = None,
1500 order_by: Optional[_ByArgument] = None,
1501 range_: Optional[typing_Tuple[Optional[int], Optional[int]]] = None,
1502 rows: Optional[typing_Tuple[Optional[int], Optional[int]]] = None,
1503 groups: Optional[typing_Tuple[Optional[int], Optional[int]]] = None,
1504) -> Over[_T]:
1505 r"""Produce an :class:`.Over` object against a function.
1507 Used against aggregate or so-called "window" functions,
1508 for database backends that support window functions.
1510 :func:`_expression.over` is usually called using
1511 the :meth:`.FunctionElement.over` method, e.g.::
1513 func.row_number().over(order_by=mytable.c.some_column)
1515 Would produce:
1517 .. sourcecode:: sql
1519 ROW_NUMBER() OVER(ORDER BY some_column)
1521 Ranges are also possible using the :paramref:`.expression.over.range_`,
1522 :paramref:`.expression.over.rows`, and :paramref:`.expression.over.groups`
1523 parameters. These
1524 mutually-exclusive parameters each accept a 2-tuple, which contains
1525 a combination of integers and None::
1527 func.row_number().over(order_by=my_table.c.some_column, range_=(None, 0))
1529 The above would produce:
1531 .. sourcecode:: sql
1533 ROW_NUMBER() OVER(ORDER BY some_column
1534 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
1536 A value of ``None`` indicates "unbounded", a
1537 value of zero indicates "current row", and negative / positive
1538 integers indicate "preceding" and "following":
1540 * RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING::
1542 func.row_number().over(order_by="x", range_=(-5, 10))
1544 * ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW::
1546 func.row_number().over(order_by="x", rows=(None, 0))
1548 * RANGE BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING::
1550 func.row_number().over(order_by="x", range_=(-2, None))
1552 * RANGE BETWEEN 1 FOLLOWING AND 3 FOLLOWING::
1554 func.row_number().over(order_by="x", range_=(1, 3))
1556 * GROUPS BETWEEN 1 FOLLOWING AND 3 FOLLOWING::
1558 func.row_number().over(order_by="x", groups=(1, 3))
1560 :param element: a :class:`.FunctionElement`, :class:`.WithinGroup`,
1561 or other compatible construct.
1562 :param partition_by: a column element or string, or a list
1563 of such, that will be used as the PARTITION BY clause
1564 of the OVER construct.
1565 :param order_by: a column element or string, or a list
1566 of such, that will be used as the ORDER BY clause
1567 of the OVER construct.
1568 :param range\_: optional range clause for the window. This is a
1569 tuple value which can contain integer values or ``None``,
1570 and will render a RANGE BETWEEN PRECEDING / FOLLOWING clause.
1571 :param rows: optional rows clause for the window. This is a tuple
1572 value which can contain integer values or None, and will render
1573 a ROWS BETWEEN PRECEDING / FOLLOWING clause.
1574 :param groups: optional groups clause for the window. This is a
1575 tuple value which can contain integer values or ``None``,
1576 and will render a GROUPS BETWEEN PRECEDING / FOLLOWING clause.
1578 .. versionadded:: 2.0.40
1580 This function is also available from the :data:`~.expression.func`
1581 construct itself via the :meth:`.FunctionElement.over` method.
1583 .. seealso::
1585 :ref:`tutorial_window_functions` - in the :ref:`unified_tutorial`
1587 :data:`.expression.func`
1589 :func:`_expression.within_group`
1591 """ # noqa: E501
1592 return Over(element, partition_by, order_by, range_, rows, groups)
1595@_document_text_coercion("text", ":func:`.text`", ":paramref:`.text.text`")
1596def text(text: str) -> TextClause:
1597 r"""Construct a new :class:`_expression.TextClause` clause,
1598 representing
1599 a textual SQL string directly.
1601 E.g.::
1603 from sqlalchemy import text
1605 t = text("SELECT * FROM users")
1606 result = connection.execute(t)
1608 The advantages :func:`_expression.text`
1609 provides over a plain string are
1610 backend-neutral support for bind parameters, per-statement
1611 execution options, as well as
1612 bind parameter and result-column typing behavior, allowing
1613 SQLAlchemy type constructs to play a role when executing
1614 a statement that is specified literally. The construct can also
1615 be provided with a ``.c`` collection of column elements, allowing
1616 it to be embedded in other SQL expression constructs as a subquery.
1618 Bind parameters are specified by name, using the format ``:name``.
1619 E.g.::
1621 t = text("SELECT * FROM users WHERE id=:user_id")
1622 result = connection.execute(t, {"user_id": 12})
1624 For SQL statements where a colon is required verbatim, as within
1625 an inline string, use a backslash to escape::
1627 t = text(r"SELECT * FROM users WHERE name='\:username'")
1629 The :class:`_expression.TextClause`
1630 construct includes methods which can
1631 provide information about the bound parameters as well as the column
1632 values which would be returned from the textual statement, assuming
1633 it's an executable SELECT type of statement. The
1634 :meth:`_expression.TextClause.bindparams`
1635 method is used to provide bound
1636 parameter detail, and :meth:`_expression.TextClause.columns`
1637 method allows
1638 specification of return columns including names and types::
1640 t = (
1641 text("SELECT * FROM users WHERE id=:user_id")
1642 .bindparams(user_id=7)
1643 .columns(id=Integer, name=String)
1644 )
1646 for id, name in connection.execute(t):
1647 print(id, name)
1649 The :func:`_expression.text` construct is used in cases when
1650 a literal string SQL fragment is specified as part of a larger query,
1651 such as for the WHERE clause of a SELECT statement::
1653 s = select(users.c.id, users.c.name).where(text("id=:user_id"))
1654 result = connection.execute(s, {"user_id": 12})
1656 :func:`_expression.text` is also used for the construction
1657 of a full, standalone statement using plain text.
1658 As such, SQLAlchemy refers
1659 to it as an :class:`.Executable` object and may be used
1660 like any other statement passed to an ``.execute()`` method.
1662 :param text:
1663 the text of the SQL statement to be created. Use ``:<param>``
1664 to specify bind parameters; they will be compiled to their
1665 engine-specific format.
1667 .. seealso::
1669 :ref:`tutorial_select_arbitrary_text`
1671 """
1672 return TextClause(text)
1675def true() -> True_:
1676 """Return a constant :class:`.True_` construct.
1678 E.g.:
1680 .. sourcecode:: pycon+sql
1682 >>> from sqlalchemy import true
1683 >>> print(select(t.c.x).where(true()))
1684 {printsql}SELECT x FROM t WHERE true
1686 A backend which does not support true/false constants will render as
1687 an expression against 1 or 0:
1689 .. sourcecode:: pycon+sql
1691 >>> print(select(t.c.x).where(true()))
1692 {printsql}SELECT x FROM t WHERE 1 = 1
1694 The :func:`.true` and :func:`.false` constants also feature
1695 "short circuit" operation within an :func:`.and_` or :func:`.or_`
1696 conjunction:
1698 .. sourcecode:: pycon+sql
1700 >>> print(select(t.c.x).where(or_(t.c.x > 5, true())))
1701 {printsql}SELECT x FROM t WHERE true{stop}
1703 >>> print(select(t.c.x).where(and_(t.c.x > 5, false())))
1704 {printsql}SELECT x FROM t WHERE false{stop}
1706 .. seealso::
1708 :func:`.false`
1710 """
1712 return True_._instance()
1715def tuple_(
1716 *clauses: _ColumnExpressionArgument[Any],
1717 types: Optional[Sequence[_TypeEngineArgument[Any]]] = None,
1718) -> Tuple:
1719 """Return a :class:`.Tuple`.
1721 Main usage is to produce a composite IN construct using
1722 :meth:`.ColumnOperators.in_` ::
1724 from sqlalchemy import tuple_
1726 tuple_(table.c.col1, table.c.col2).in_([(1, 2), (5, 12), (10, 19)])
1728 .. warning::
1730 The composite IN construct is not supported by all backends, and is
1731 currently known to work on PostgreSQL, MySQL, and SQLite.
1732 Unsupported backends will raise a subclass of
1733 :class:`~sqlalchemy.exc.DBAPIError` when such an expression is
1734 invoked.
1736 """
1737 return Tuple(*clauses, types=types)
1740def type_coerce(
1741 expression: _ColumnExpressionOrLiteralArgument[Any],
1742 type_: _TypeEngineArgument[_T],
1743) -> TypeCoerce[_T]:
1744 r"""Associate a SQL expression with a particular type, without rendering
1745 ``CAST``.
1747 E.g.::
1749 from sqlalchemy import type_coerce
1751 stmt = select(type_coerce(log_table.date_string, StringDateTime()))
1753 The above construct will produce a :class:`.TypeCoerce` object, which
1754 does not modify the rendering in any way on the SQL side, with the
1755 possible exception of a generated label if used in a columns clause
1756 context:
1758 .. sourcecode:: sql
1760 SELECT date_string AS date_string FROM log
1762 When result rows are fetched, the ``StringDateTime`` type processor
1763 will be applied to result rows on behalf of the ``date_string`` column.
1765 .. note:: the :func:`.type_coerce` construct does not render any
1766 SQL syntax of its own, including that it does not imply
1767 parenthesization. Please use :meth:`.TypeCoerce.self_group`
1768 if explicit parenthesization is required.
1770 In order to provide a named label for the expression, use
1771 :meth:`_expression.ColumnElement.label`::
1773 stmt = select(
1774 type_coerce(log_table.date_string, StringDateTime()).label("date")
1775 )
1777 A type that features bound-value handling will also have that behavior
1778 take effect when literal values or :func:`.bindparam` constructs are
1779 passed to :func:`.type_coerce` as targets.
1780 For example, if a type implements the
1781 :meth:`.TypeEngine.bind_expression`
1782 method or :meth:`.TypeEngine.bind_processor` method or equivalent,
1783 these functions will take effect at statement compilation/execution
1784 time when a literal value is passed, as in::
1786 # bound-value handling of MyStringType will be applied to the
1787 # literal value "some string"
1788 stmt = select(type_coerce("some string", MyStringType))
1790 When using :func:`.type_coerce` with composed expressions, note that
1791 **parenthesis are not applied**. If :func:`.type_coerce` is being
1792 used in an operator context where the parenthesis normally present from
1793 CAST are necessary, use the :meth:`.TypeCoerce.self_group` method:
1795 .. sourcecode:: pycon+sql
1797 >>> some_integer = column("someint", Integer)
1798 >>> some_string = column("somestr", String)
1799 >>> expr = type_coerce(some_integer + 5, String) + some_string
1800 >>> print(expr)
1801 {printsql}someint + :someint_1 || somestr{stop}
1802 >>> expr = type_coerce(some_integer + 5, String).self_group() + some_string
1803 >>> print(expr)
1804 {printsql}(someint + :someint_1) || somestr{stop}
1806 :param expression: A SQL expression, such as a
1807 :class:`_expression.ColumnElement`
1808 expression or a Python string which will be coerced into a bound
1809 literal value.
1811 :param type\_: A :class:`.TypeEngine` class or instance indicating
1812 the type to which the expression is coerced.
1814 .. seealso::
1816 :ref:`tutorial_casts`
1818 :func:`.cast`
1820 """ # noqa
1821 return TypeCoerce(expression, type_)
1824def within_group(
1825 element: FunctionElement[_T], *order_by: _ColumnExpressionArgument[Any]
1826) -> WithinGroup[_T]:
1827 r"""Produce a :class:`.WithinGroup` object against a function.
1829 Used against so-called "ordered set aggregate" and "hypothetical
1830 set aggregate" functions, including :class:`.percentile_cont`,
1831 :class:`.rank`, :class:`.dense_rank`, etc.
1833 :func:`_expression.within_group` is usually called using
1834 the :meth:`.FunctionElement.within_group` method, e.g.::
1836 from sqlalchemy import within_group
1838 stmt = select(
1839 department.c.id,
1840 func.percentile_cont(0.5).within_group(department.c.salary.desc()),
1841 )
1843 The above statement would produce SQL similar to
1844 ``SELECT department.id, percentile_cont(0.5)
1845 WITHIN GROUP (ORDER BY department.salary DESC)``.
1847 :param element: a :class:`.FunctionElement` construct, typically
1848 generated by :data:`~.expression.func`.
1849 :param \*order_by: one or more column elements that will be used
1850 as the ORDER BY clause of the WITHIN GROUP construct.
1852 .. seealso::
1854 :ref:`tutorial_functions_within_group` - in the
1855 :ref:`unified_tutorial`
1857 :data:`.expression.func`
1859 :func:`_expression.over`
1861 """
1862 return WithinGroup(element, *order_by)