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 .. versionchanged:: 1.2 quoting is automatically applied to COLLATE
362 expressions if they are case sensitive.
364 """
365 return CollationClause._create_collation_expression(expression, collation)
368def between(
369 expr: _ColumnExpressionOrLiteralArgument[_T],
370 lower_bound: Any,
371 upper_bound: Any,
372 symmetric: bool = False,
373) -> BinaryExpression[bool]:
374 """Produce a ``BETWEEN`` predicate clause.
376 E.g.::
378 from sqlalchemy import between
380 stmt = select(users_table).where(between(users_table.c.id, 5, 7))
382 Would produce SQL resembling:
384 .. sourcecode:: sql
386 SELECT id, name FROM user WHERE id BETWEEN :id_1 AND :id_2
388 The :func:`.between` function is a standalone version of the
389 :meth:`_expression.ColumnElement.between` method available on all
390 SQL expressions, as in::
392 stmt = select(users_table).where(users_table.c.id.between(5, 7))
394 All arguments passed to :func:`.between`, including the left side
395 column expression, are coerced from Python scalar values if a
396 the value is not a :class:`_expression.ColumnElement` subclass.
397 For example,
398 three fixed values can be compared as in::
400 print(between(5, 3, 7))
402 Which would produce::
404 :param_1 BETWEEN :param_2 AND :param_3
406 :param expr: a column expression, typically a
407 :class:`_expression.ColumnElement`
408 instance or alternatively a Python scalar expression to be coerced
409 into a column expression, serving as the left side of the ``BETWEEN``
410 expression.
412 :param lower_bound: a column or Python scalar expression serving as the
413 lower bound of the right side of the ``BETWEEN`` expression.
415 :param upper_bound: a column or Python scalar expression serving as the
416 upper bound of the right side of the ``BETWEEN`` expression.
418 :param symmetric: if True, will render " BETWEEN SYMMETRIC ". Note
419 that not all databases support this syntax.
421 .. seealso::
423 :meth:`_expression.ColumnElement.between`
425 """
426 col_expr = coercions.expect(roles.ExpressionElementRole, expr)
427 return col_expr.between(lower_bound, upper_bound, symmetric=symmetric)
430def outparam(
431 key: str, type_: Optional[TypeEngine[_T]] = None
432) -> BindParameter[_T]:
433 """Create an 'OUT' parameter for usage in functions (stored procedures),
434 for databases which support them.
436 The ``outparam`` can be used like a regular function parameter.
437 The "output" value will be available from the
438 :class:`~sqlalchemy.engine.CursorResult` object via its ``out_parameters``
439 attribute, which returns a dictionary containing the values.
441 """
442 return BindParameter(key, None, type_=type_, unique=False, isoutparam=True)
445@overload
446def not_(clause: BinaryExpression[_T]) -> BinaryExpression[_T]: ...
449@overload
450def not_(clause: _ColumnExpressionArgument[_T]) -> ColumnElement[_T]: ...
453def not_(clause: _ColumnExpressionArgument[_T]) -> ColumnElement[_T]:
454 """Return a negation of the given clause, i.e. ``NOT(clause)``.
456 The ``~`` operator is also overloaded on all
457 :class:`_expression.ColumnElement` subclasses to produce the
458 same result.
460 """
462 return coercions.expect(roles.ExpressionElementRole, clause).__invert__()
465def bindparam(
466 key: Optional[str],
467 value: Any = _NoArg.NO_ARG,
468 type_: Optional[_TypeEngineArgument[_T]] = None,
469 unique: bool = False,
470 required: Union[bool, Literal[_NoArg.NO_ARG]] = _NoArg.NO_ARG,
471 quote: Optional[bool] = None,
472 callable_: Optional[Callable[[], Any]] = None,
473 expanding: bool = False,
474 isoutparam: bool = False,
475 literal_execute: bool = False,
476) -> BindParameter[_T]:
477 r"""Produce a "bound expression".
479 The return value is an instance of :class:`.BindParameter`; this
480 is a :class:`_expression.ColumnElement`
481 subclass which represents a so-called
482 "placeholder" value in a SQL expression, the value of which is
483 supplied at the point at which the statement in executed against a
484 database connection.
486 In SQLAlchemy, the :func:`.bindparam` construct has
487 the ability to carry along the actual value that will be ultimately
488 used at expression time. In this way, it serves not just as
489 a "placeholder" for eventual population, but also as a means of
490 representing so-called "unsafe" values which should not be rendered
491 directly in a SQL statement, but rather should be passed along
492 to the :term:`DBAPI` as values which need to be correctly escaped
493 and potentially handled for type-safety.
495 When using :func:`.bindparam` explicitly, the use case is typically
496 one of traditional deferment of parameters; the :func:`.bindparam`
497 construct accepts a name which can then be referred to at execution
498 time::
500 from sqlalchemy import bindparam
502 stmt = select(users_table).where(
503 users_table.c.name == bindparam("username")
504 )
506 The above statement, when rendered, will produce SQL similar to:
508 .. sourcecode:: sql
510 SELECT id, name FROM user WHERE name = :username
512 In order to populate the value of ``:username`` above, the value
513 would typically be applied at execution time to a method
514 like :meth:`_engine.Connection.execute`::
516 result = connection.execute(stmt, {"username": "wendy"})
518 Explicit use of :func:`.bindparam` is also common when producing
519 UPDATE or DELETE statements that are to be invoked multiple times,
520 where the WHERE criterion of the statement is to change on each
521 invocation, such as::
523 stmt = (
524 users_table.update()
525 .where(user_table.c.name == bindparam("username"))
526 .values(fullname=bindparam("fullname"))
527 )
529 connection.execute(
530 stmt,
531 [
532 {"username": "wendy", "fullname": "Wendy Smith"},
533 {"username": "jack", "fullname": "Jack Jones"},
534 ],
535 )
537 SQLAlchemy's Core expression system makes wide use of
538 :func:`.bindparam` in an implicit sense. It is typical that Python
539 literal values passed to virtually all SQL expression functions are
540 coerced into fixed :func:`.bindparam` constructs. For example, given
541 a comparison operation such as::
543 expr = users_table.c.name == "Wendy"
545 The above expression will produce a :class:`.BinaryExpression`
546 construct, where the left side is the :class:`_schema.Column` object
547 representing the ``name`` column, and the right side is a
548 :class:`.BindParameter` representing the literal value::
550 print(repr(expr.right))
551 BindParameter("%(4327771088 name)s", "Wendy", type_=String())
553 The expression above will render SQL such as:
555 .. sourcecode:: sql
557 user.name = :name_1
559 Where the ``:name_1`` parameter name is an anonymous name. The
560 actual string ``Wendy`` is not in the rendered string, but is carried
561 along where it is later used within statement execution. If we
562 invoke a statement like the following::
564 stmt = select(users_table).where(users_table.c.name == "Wendy")
565 result = connection.execute(stmt)
567 We would see SQL logging output as:
569 .. sourcecode:: sql
571 SELECT "user".id, "user".name
572 FROM "user"
573 WHERE "user".name = %(name_1)s
574 {'name_1': 'Wendy'}
576 Above, we see that ``Wendy`` is passed as a parameter to the database,
577 while the placeholder ``:name_1`` is rendered in the appropriate form
578 for the target database, in this case the PostgreSQL database.
580 Similarly, :func:`.bindparam` is invoked automatically when working
581 with :term:`CRUD` statements as far as the "VALUES" portion is
582 concerned. The :func:`_expression.insert` construct produces an
583 ``INSERT`` expression which will, at statement execution time, generate
584 bound placeholders based on the arguments passed, as in::
586 stmt = users_table.insert()
587 result = connection.execute(stmt, {"name": "Wendy"})
589 The above will produce SQL output as:
591 .. sourcecode:: sql
593 INSERT INTO "user" (name) VALUES (%(name)s)
594 {'name': 'Wendy'}
596 The :class:`_expression.Insert` construct, at
597 compilation/execution time, rendered a single :func:`.bindparam`
598 mirroring the column name ``name`` as a result of the single ``name``
599 parameter we passed to the :meth:`_engine.Connection.execute` method.
601 :param key:
602 the key (e.g. the name) for this bind param.
603 Will be used in the generated
604 SQL statement for dialects that use named parameters. This
605 value may be modified when part of a compilation operation,
606 if other :class:`BindParameter` objects exist with the same
607 key, or if its length is too long and truncation is
608 required.
610 If omitted, an "anonymous" name is generated for the bound parameter;
611 when given a value to bind, the end result is equivalent to calling upon
612 the :func:`.literal` function with a value to bind, particularly
613 if the :paramref:`.bindparam.unique` parameter is also provided.
615 :param value:
616 Initial value for this bind param. Will be used at statement
617 execution time as the value for this parameter passed to the
618 DBAPI, if no other value is indicated to the statement execution
619 method for this particular parameter name. Defaults to ``None``.
621 :param callable\_:
622 A callable function that takes the place of "value". The function
623 will be called at statement execution time to determine the
624 ultimate value. Used for scenarios where the actual bind
625 value cannot be determined at the point at which the clause
626 construct is created, but embedded bind values are still desirable.
628 :param type\_:
629 A :class:`.TypeEngine` class or instance representing an optional
630 datatype for this :func:`.bindparam`. If not passed, a type
631 may be determined automatically for the bind, based on the given
632 value; for example, trivial Python types such as ``str``,
633 ``int``, ``bool``
634 may result in the :class:`.String`, :class:`.Integer` or
635 :class:`.Boolean` types being automatically selected.
637 The type of a :func:`.bindparam` is significant especially in that
638 the type will apply pre-processing to the value before it is
639 passed to the database. For example, a :func:`.bindparam` which
640 refers to a datetime value, and is specified as holding the
641 :class:`.DateTime` type, may apply conversion needed to the
642 value (such as stringification on SQLite) before passing the value
643 to the database.
645 :param unique:
646 if True, the key name of this :class:`.BindParameter` will be
647 modified if another :class:`.BindParameter` of the same name
648 already has been located within the containing
649 expression. This flag is used generally by the internals
650 when producing so-called "anonymous" bound expressions, it
651 isn't generally applicable to explicitly-named :func:`.bindparam`
652 constructs.
654 :param required:
655 If ``True``, a value is required at execution time. If not passed,
656 it defaults to ``True`` if neither :paramref:`.bindparam.value`
657 or :paramref:`.bindparam.callable` were passed. If either of these
658 parameters are present, then :paramref:`.bindparam.required`
659 defaults to ``False``.
661 :param quote:
662 True if this parameter name requires quoting and is not
663 currently known as a SQLAlchemy reserved word; this currently
664 only applies to the Oracle Database backends, where bound names must
665 sometimes be quoted.
667 :param isoutparam:
668 if True, the parameter should be treated like a stored procedure
669 "OUT" parameter. This applies to backends such as Oracle Database which
670 support OUT parameters.
672 :param expanding:
673 if True, this parameter will be treated as an "expanding" parameter
674 at execution time; the parameter value is expected to be a sequence,
675 rather than a scalar value, and the string SQL statement will
676 be transformed on a per-execution basis to accommodate the sequence
677 with a variable number of parameter slots passed to the DBAPI.
678 This is to allow statement caching to be used in conjunction with
679 an IN clause.
681 .. seealso::
683 :meth:`.ColumnOperators.in_`
685 :ref:`baked_in` - with baked queries
687 .. note:: The "expanding" feature does not support "executemany"-
688 style parameter sets.
690 .. versionadded:: 1.2
692 .. versionchanged:: 1.3 the "expanding" bound parameter feature now
693 supports empty lists.
695 :param literal_execute:
696 if True, the bound parameter will be rendered in the compile phase
697 with a special "POSTCOMPILE" token, and the SQLAlchemy compiler will
698 render the final value of the parameter into the SQL statement at
699 statement execution time, omitting the value from the parameter
700 dictionary / list passed to DBAPI ``cursor.execute()``. This
701 produces a similar effect as that of using the ``literal_binds``,
702 compilation flag, however takes place as the statement is sent to
703 the DBAPI ``cursor.execute()`` method, rather than when the statement
704 is compiled. The primary use of this
705 capability is for rendering LIMIT / OFFSET clauses for database
706 drivers that can't accommodate for bound parameters in these
707 contexts, while allowing SQL constructs to be cacheable at the
708 compilation level.
710 .. versionadded:: 1.4 Added "post compile" bound parameters
712 .. seealso::
714 :ref:`change_4808`.
716 .. seealso::
718 :ref:`tutorial_sending_parameters` - in the
719 :ref:`unified_tutorial`
722 """
723 return BindParameter(
724 key,
725 value,
726 type_,
727 unique,
728 required,
729 quote,
730 callable_,
731 expanding,
732 isoutparam,
733 literal_execute,
734 )
737def case(
738 *whens: Union[
739 typing_Tuple[_ColumnExpressionArgument[bool], Any], Mapping[Any, Any]
740 ],
741 value: Optional[Any] = None,
742 else_: Optional[Any] = None,
743) -> Case[Any]:
744 r"""Produce a ``CASE`` expression.
746 The ``CASE`` construct in SQL is a conditional object that
747 acts somewhat analogously to an "if/then" construct in other
748 languages. It returns an instance of :class:`.Case`.
750 :func:`.case` in its usual form is passed a series of "when"
751 constructs, that is, a list of conditions and results as tuples::
753 from sqlalchemy import case
755 stmt = select(users_table).where(
756 case(
757 (users_table.c.name == "wendy", "W"),
758 (users_table.c.name == "jack", "J"),
759 else_="E",
760 )
761 )
763 The above statement will produce SQL resembling:
765 .. sourcecode:: sql
767 SELECT id, name FROM user
768 WHERE CASE
769 WHEN (name = :name_1) THEN :param_1
770 WHEN (name = :name_2) THEN :param_2
771 ELSE :param_3
772 END
774 When simple equality expressions of several values against a single
775 parent column are needed, :func:`.case` also has a "shorthand" format
776 used via the
777 :paramref:`.case.value` parameter, which is passed a column
778 expression to be compared. In this form, the :paramref:`.case.whens`
779 parameter is passed as a dictionary containing expressions to be
780 compared against keyed to result expressions. The statement below is
781 equivalent to the preceding statement::
783 stmt = select(users_table).where(
784 case({"wendy": "W", "jack": "J"}, value=users_table.c.name, else_="E")
785 )
787 The values which are accepted as result values in
788 :paramref:`.case.whens` as well as with :paramref:`.case.else_` are
789 coerced from Python literals into :func:`.bindparam` constructs.
790 SQL expressions, e.g. :class:`_expression.ColumnElement` constructs,
791 are accepted
792 as well. To coerce a literal string expression into a constant
793 expression rendered inline, use the :func:`_expression.literal_column`
794 construct,
795 as in::
797 from sqlalchemy import case, literal_column
799 case(
800 (orderline.c.qty > 100, literal_column("'greaterthan100'")),
801 (orderline.c.qty > 10, literal_column("'greaterthan10'")),
802 else_=literal_column("'lessthan10'"),
803 )
805 The above will render the given constants without using bound
806 parameters for the result values (but still for the comparison
807 values), as in:
809 .. sourcecode:: sql
811 CASE
812 WHEN (orderline.qty > :qty_1) THEN 'greaterthan100'
813 WHEN (orderline.qty > :qty_2) THEN 'greaterthan10'
814 ELSE 'lessthan10'
815 END
817 :param \*whens: The criteria to be compared against,
818 :paramref:`.case.whens` accepts two different forms, based on
819 whether or not :paramref:`.case.value` is used.
821 .. versionchanged:: 1.4 the :func:`_sql.case`
822 function now accepts the series of WHEN conditions positionally
824 In the first form, it accepts multiple 2-tuples passed as positional
825 arguments; each 2-tuple consists of ``(<sql expression>, <value>)``,
826 where the SQL expression is a boolean expression and "value" is a
827 resulting value, e.g.::
829 case(
830 (users_table.c.name == "wendy", "W"),
831 (users_table.c.name == "jack", "J"),
832 )
834 In the second form, it accepts a Python dictionary of comparison
835 values mapped to a resulting value; this form requires
836 :paramref:`.case.value` to be present, and values will be compared
837 using the ``==`` operator, e.g.::
839 case({"wendy": "W", "jack": "J"}, value=users_table.c.name)
841 :param value: An optional SQL expression which will be used as a
842 fixed "comparison point" for candidate values within a dictionary
843 passed to :paramref:`.case.whens`.
845 :param else\_: An optional SQL expression which will be the evaluated
846 result of the ``CASE`` construct if all expressions within
847 :paramref:`.case.whens` evaluate to false. When omitted, most
848 databases will produce a result of NULL if none of the "when"
849 expressions evaluate to true.
852 """ # noqa: E501
853 return Case(*whens, value=value, else_=else_)
856def cast(
857 expression: _ColumnExpressionOrLiteralArgument[Any],
858 type_: _TypeEngineArgument[_T],
859) -> Cast[_T]:
860 r"""Produce a ``CAST`` expression.
862 :func:`.cast` returns an instance of :class:`.Cast`.
864 E.g.::
866 from sqlalchemy import cast, Numeric
868 stmt = select(cast(product_table.c.unit_price, Numeric(10, 4)))
870 The above statement will produce SQL resembling:
872 .. sourcecode:: sql
874 SELECT CAST(unit_price AS NUMERIC(10, 4)) FROM product
876 The :func:`.cast` function performs two distinct functions when
877 used. The first is that it renders the ``CAST`` expression within
878 the resulting SQL string. The second is that it associates the given
879 type (e.g. :class:`.TypeEngine` class or instance) with the column
880 expression on the Python side, which means the expression will take
881 on the expression operator behavior associated with that type,
882 as well as the bound-value handling and result-row-handling behavior
883 of the type.
885 An alternative to :func:`.cast` is the :func:`.type_coerce` function.
886 This function performs the second task of associating an expression
887 with a specific type, but does not render the ``CAST`` expression
888 in SQL.
890 :param expression: A SQL expression, such as a
891 :class:`_expression.ColumnElement`
892 expression or a Python string which will be coerced into a bound
893 literal value.
895 :param type\_: A :class:`.TypeEngine` class or instance indicating
896 the type to which the ``CAST`` should apply.
898 .. seealso::
900 :ref:`tutorial_casts`
902 :func:`.try_cast` - an alternative to CAST that results in
903 NULLs when the cast fails, instead of raising an error.
904 Only supported by some dialects.
906 :func:`.type_coerce` - an alternative to CAST that coerces the type
907 on the Python side only, which is often sufficient to generate the
908 correct SQL and data coercion.
911 """
912 return Cast(expression, type_)
915def try_cast(
916 expression: _ColumnExpressionOrLiteralArgument[Any],
917 type_: _TypeEngineArgument[_T],
918) -> TryCast[_T]:
919 """Produce a ``TRY_CAST`` expression for backends which support it;
920 this is a ``CAST`` which returns NULL for un-castable conversions.
922 In SQLAlchemy, this construct is supported **only** by the SQL Server
923 dialect, and will raise a :class:`.CompileError` if used on other
924 included backends. However, third party backends may also support
925 this construct.
927 .. tip:: As :func:`_sql.try_cast` originates from the SQL Server dialect,
928 it's importable both from ``sqlalchemy.`` as well as from
929 ``sqlalchemy.dialects.mssql``.
931 :func:`_sql.try_cast` returns an instance of :class:`.TryCast` and
932 generally behaves similarly to the :class:`.Cast` construct;
933 at the SQL level, the difference between ``CAST`` and ``TRY_CAST``
934 is that ``TRY_CAST`` returns NULL for an un-castable expression,
935 such as attempting to cast a string ``"hi"`` to an integer value.
937 E.g.::
939 from sqlalchemy import select, try_cast, Numeric
941 stmt = select(try_cast(product_table.c.unit_price, Numeric(10, 4)))
943 The above would render on Microsoft SQL Server as:
945 .. sourcecode:: sql
947 SELECT TRY_CAST (product_table.unit_price AS NUMERIC(10, 4))
948 FROM product_table
950 .. versionadded:: 2.0.14 :func:`.try_cast` has been
951 generalized from the SQL Server dialect into a general use
952 construct that may be supported by additional dialects.
954 """
955 return TryCast(expression, type_)
958def column(
959 text: str,
960 type_: Optional[_TypeEngineArgument[_T]] = None,
961 is_literal: bool = False,
962 _selectable: Optional[FromClause] = None,
963) -> ColumnClause[_T]:
964 """Produce a :class:`.ColumnClause` object.
966 The :class:`.ColumnClause` is a lightweight analogue to the
967 :class:`_schema.Column` class. The :func:`_expression.column`
968 function can
969 be invoked with just a name alone, as in::
971 from sqlalchemy import column
973 id, name = column("id"), column("name")
974 stmt = select(id, name).select_from("user")
976 The above statement would produce SQL like:
978 .. sourcecode:: sql
980 SELECT id, name FROM user
982 Once constructed, :func:`_expression.column`
983 may be used like any other SQL
984 expression element such as within :func:`_expression.select`
985 constructs::
987 from sqlalchemy.sql import column
989 id, name = column("id"), column("name")
990 stmt = select(id, name).select_from("user")
992 The text handled by :func:`_expression.column`
993 is assumed to be handled
994 like the name of a database column; if the string contains mixed case,
995 special characters, or matches a known reserved word on the target
996 backend, the column expression will render using the quoting
997 behavior determined by the backend. To produce a textual SQL
998 expression that is rendered exactly without any quoting,
999 use :func:`_expression.literal_column` instead,
1000 or pass ``True`` as the
1001 value of :paramref:`_expression.column.is_literal`. Additionally,
1002 full SQL
1003 statements are best handled using the :func:`_expression.text`
1004 construct.
1006 :func:`_expression.column` can be used in a table-like
1007 fashion by combining it with the :func:`.table` function
1008 (which is the lightweight analogue to :class:`_schema.Table`
1009 ) to produce
1010 a working table construct with minimal boilerplate::
1012 from sqlalchemy import table, column, select
1014 user = table(
1015 "user",
1016 column("id"),
1017 column("name"),
1018 column("description"),
1019 )
1021 stmt = select(user.c.description).where(user.c.name == "wendy")
1023 A :func:`_expression.column` / :func:`.table`
1024 construct like that illustrated
1025 above can be created in an
1026 ad-hoc fashion and is not associated with any
1027 :class:`_schema.MetaData`, DDL, or events, unlike its
1028 :class:`_schema.Table` counterpart.
1030 :param text: the text of the element.
1032 :param type: :class:`_types.TypeEngine` object which can associate
1033 this :class:`.ColumnClause` with a type.
1035 :param is_literal: if True, the :class:`.ColumnClause` is assumed to
1036 be an exact expression that will be delivered to the output with no
1037 quoting rules applied regardless of case sensitive settings. the
1038 :func:`_expression.literal_column()` function essentially invokes
1039 :func:`_expression.column` while passing ``is_literal=True``.
1041 .. seealso::
1043 :class:`_schema.Column`
1045 :func:`_expression.literal_column`
1047 :func:`.table`
1049 :func:`_expression.text`
1051 :ref:`tutorial_select_arbitrary_text`
1053 """
1054 return ColumnClause(text, type_, is_literal, _selectable)
1057def desc(
1058 column: _ColumnExpressionOrStrLabelArgument[_T],
1059) -> UnaryExpression[_T]:
1060 """Produce a descending ``ORDER BY`` clause element.
1062 e.g.::
1064 from sqlalchemy import desc
1066 stmt = select(users_table).order_by(desc(users_table.c.name))
1068 will produce SQL as:
1070 .. sourcecode:: sql
1072 SELECT id, name FROM user ORDER BY name DESC
1074 The :func:`.desc` function is a standalone version of the
1075 :meth:`_expression.ColumnElement.desc`
1076 method available on all SQL expressions,
1077 e.g.::
1080 stmt = select(users_table).order_by(users_table.c.name.desc())
1082 :param column: A :class:`_expression.ColumnElement` (e.g.
1083 scalar SQL expression)
1084 with which to apply the :func:`.desc` operation.
1086 .. seealso::
1088 :func:`.asc`
1090 :func:`.nulls_first`
1092 :func:`.nulls_last`
1094 :meth:`_expression.Select.order_by`
1096 """
1097 return UnaryExpression._create_desc(column)
1100def distinct(expr: _ColumnExpressionArgument[_T]) -> UnaryExpression[_T]:
1101 """Produce an column-expression-level unary ``DISTINCT`` clause.
1103 This applies the ``DISTINCT`` keyword to an **individual column
1104 expression** (e.g. not the whole statement), and renders **specifically
1105 in that column position**; this is used for containment within
1106 an aggregate function, as in::
1108 from sqlalchemy import distinct, func
1110 stmt = select(users_table.c.id, func.count(distinct(users_table.c.name)))
1112 The above would produce an statement resembling:
1114 .. sourcecode:: sql
1116 SELECT user.id, count(DISTINCT user.name) FROM user
1118 .. tip:: The :func:`_sql.distinct` function does **not** apply DISTINCT
1119 to the full SELECT statement, instead applying a DISTINCT modifier
1120 to **individual column expressions**. For general ``SELECT DISTINCT``
1121 support, use the
1122 :meth:`_sql.Select.distinct` method on :class:`_sql.Select`.
1124 The :func:`.distinct` function is also available as a column-level
1125 method, e.g. :meth:`_expression.ColumnElement.distinct`, as in::
1127 stmt = select(func.count(users_table.c.name.distinct()))
1129 The :func:`.distinct` operator is different from the
1130 :meth:`_expression.Select.distinct` method of
1131 :class:`_expression.Select`,
1132 which produces a ``SELECT`` statement
1133 with ``DISTINCT`` applied to the result set as a whole,
1134 e.g. a ``SELECT DISTINCT`` expression. See that method for further
1135 information.
1137 .. seealso::
1139 :meth:`_expression.ColumnElement.distinct`
1141 :meth:`_expression.Select.distinct`
1143 :data:`.func`
1145 """ # noqa: E501
1146 return UnaryExpression._create_distinct(expr)
1149def bitwise_not(expr: _ColumnExpressionArgument[_T]) -> UnaryExpression[_T]:
1150 """Produce a unary bitwise NOT clause, typically via the ``~`` operator.
1152 Not to be confused with boolean negation :func:`_sql.not_`.
1154 .. versionadded:: 2.0.2
1156 .. seealso::
1158 :ref:`operators_bitwise`
1161 """
1163 return UnaryExpression._create_bitwise_not(expr)
1166def extract(field: str, expr: _ColumnExpressionArgument[Any]) -> Extract:
1167 """Return a :class:`.Extract` construct.
1169 This is typically available as :func:`.extract`
1170 as well as ``func.extract`` from the
1171 :data:`.func` namespace.
1173 :param field: The field to extract.
1175 .. warning:: This field is used as a literal SQL string.
1176 **DO NOT PASS UNTRUSTED INPUT TO THIS STRING**.
1178 :param expr: A column or Python scalar expression serving as the
1179 right side of the ``EXTRACT`` expression.
1181 E.g.::
1183 from sqlalchemy import extract
1184 from sqlalchemy import table, column
1186 logged_table = table(
1187 "user",
1188 column("id"),
1189 column("date_created"),
1190 )
1192 stmt = select(logged_table.c.id).where(
1193 extract("YEAR", logged_table.c.date_created) == 2021
1194 )
1196 In the above example, the statement is used to select ids from the
1197 database where the ``YEAR`` component matches a specific value.
1199 Similarly, one can also select an extracted component::
1201 stmt = select(extract("YEAR", logged_table.c.date_created)).where(
1202 logged_table.c.id == 1
1203 )
1205 The implementation of ``EXTRACT`` may vary across database backends.
1206 Users are reminded to consult their database documentation.
1207 """
1208 return Extract(field, expr)
1211def false() -> False_:
1212 """Return a :class:`.False_` construct.
1214 E.g.:
1216 .. sourcecode:: pycon+sql
1218 >>> from sqlalchemy import false
1219 >>> print(select(t.c.x).where(false()))
1220 {printsql}SELECT x FROM t WHERE false
1222 A backend which does not support true/false constants will render as
1223 an expression against 1 or 0:
1225 .. sourcecode:: pycon+sql
1227 >>> print(select(t.c.x).where(false()))
1228 {printsql}SELECT x FROM t WHERE 0 = 1
1230 The :func:`.true` and :func:`.false` constants also feature
1231 "short circuit" operation within an :func:`.and_` or :func:`.or_`
1232 conjunction:
1234 .. sourcecode:: pycon+sql
1236 >>> print(select(t.c.x).where(or_(t.c.x > 5, true())))
1237 {printsql}SELECT x FROM t WHERE true{stop}
1239 >>> print(select(t.c.x).where(and_(t.c.x > 5, false())))
1240 {printsql}SELECT x FROM t WHERE false{stop}
1242 .. seealso::
1244 :func:`.true`
1246 """
1248 return False_._instance()
1251def funcfilter(
1252 func: FunctionElement[_T], *criterion: _ColumnExpressionArgument[bool]
1253) -> FunctionFilter[_T]:
1254 """Produce a :class:`.FunctionFilter` object against a function.
1256 Used against aggregate and window functions,
1257 for database backends that support the "FILTER" clause.
1259 E.g.::
1261 from sqlalchemy import funcfilter
1263 funcfilter(func.count(1), MyClass.name == "some name")
1265 Would produce "COUNT(1) FILTER (WHERE myclass.name = 'some name')".
1267 This function is also available from the :data:`~.expression.func`
1268 construct itself via the :meth:`.FunctionElement.filter` method.
1270 .. seealso::
1272 :ref:`tutorial_functions_within_group` - in the
1273 :ref:`unified_tutorial`
1275 :meth:`.FunctionElement.filter`
1277 """
1278 return FunctionFilter(func, *criterion)
1281def label(
1282 name: str,
1283 element: _ColumnExpressionArgument[_T],
1284 type_: Optional[_TypeEngineArgument[_T]] = None,
1285) -> Label[_T]:
1286 """Return a :class:`Label` object for the
1287 given :class:`_expression.ColumnElement`.
1289 A label changes the name of an element in the columns clause of a
1290 ``SELECT`` statement, typically via the ``AS`` SQL keyword.
1292 This functionality is more conveniently available via the
1293 :meth:`_expression.ColumnElement.label` method on
1294 :class:`_expression.ColumnElement`.
1296 :param name: label name
1298 :param obj: a :class:`_expression.ColumnElement`.
1300 """
1301 return Label(name, element, type_)
1304def null() -> Null:
1305 """Return a constant :class:`.Null` construct."""
1307 return Null._instance()
1310def nulls_first(column: _ColumnExpressionArgument[_T]) -> UnaryExpression[_T]:
1311 """Produce the ``NULLS FIRST`` modifier for an ``ORDER BY`` expression.
1313 :func:`.nulls_first` is intended to modify the expression produced
1314 by :func:`.asc` or :func:`.desc`, and indicates how NULL values
1315 should be handled when they are encountered during ordering::
1318 from sqlalchemy import desc, nulls_first
1320 stmt = select(users_table).order_by(nulls_first(desc(users_table.c.name)))
1322 The SQL expression from the above would resemble:
1324 .. sourcecode:: sql
1326 SELECT id, name FROM user ORDER BY name DESC NULLS FIRST
1328 Like :func:`.asc` and :func:`.desc`, :func:`.nulls_first` is typically
1329 invoked from the column expression itself using
1330 :meth:`_expression.ColumnElement.nulls_first`,
1331 rather than as its standalone
1332 function version, as in::
1334 stmt = select(users_table).order_by(
1335 users_table.c.name.desc().nulls_first()
1336 )
1338 .. versionchanged:: 1.4 :func:`.nulls_first` is renamed from
1339 :func:`.nullsfirst` in previous releases.
1340 The previous name remains available for backwards compatibility.
1342 .. seealso::
1344 :func:`.asc`
1346 :func:`.desc`
1348 :func:`.nulls_last`
1350 :meth:`_expression.Select.order_by`
1352 """ # noqa: E501
1353 return UnaryExpression._create_nulls_first(column)
1356def nulls_last(column: _ColumnExpressionArgument[_T]) -> UnaryExpression[_T]:
1357 """Produce the ``NULLS LAST`` modifier for an ``ORDER BY`` expression.
1359 :func:`.nulls_last` is intended to modify the expression produced
1360 by :func:`.asc` or :func:`.desc`, and indicates how NULL values
1361 should be handled when they are encountered during ordering::
1364 from sqlalchemy import desc, nulls_last
1366 stmt = select(users_table).order_by(nulls_last(desc(users_table.c.name)))
1368 The SQL expression from the above would resemble:
1370 .. sourcecode:: sql
1372 SELECT id, name FROM user ORDER BY name DESC NULLS LAST
1374 Like :func:`.asc` and :func:`.desc`, :func:`.nulls_last` is typically
1375 invoked from the column expression itself using
1376 :meth:`_expression.ColumnElement.nulls_last`,
1377 rather than as its standalone
1378 function version, as in::
1380 stmt = select(users_table).order_by(users_table.c.name.desc().nulls_last())
1382 .. versionchanged:: 1.4 :func:`.nulls_last` is renamed from
1383 :func:`.nullslast` in previous releases.
1384 The previous name remains available for backwards compatibility.
1386 .. seealso::
1388 :func:`.asc`
1390 :func:`.desc`
1392 :func:`.nulls_first`
1394 :meth:`_expression.Select.order_by`
1396 """ # noqa: E501
1397 return UnaryExpression._create_nulls_last(column)
1400def or_( # type: ignore[empty-body]
1401 initial_clause: Union[Literal[False], _ColumnExpressionArgument[bool]],
1402 *clauses: _ColumnExpressionArgument[bool],
1403) -> ColumnElement[bool]:
1404 """Produce a conjunction of expressions joined by ``OR``.
1406 E.g.::
1408 from sqlalchemy import or_
1410 stmt = select(users_table).where(
1411 or_(users_table.c.name == "wendy", users_table.c.name == "jack")
1412 )
1414 The :func:`.or_` conjunction is also available using the
1415 Python ``|`` operator (though note that compound expressions
1416 need to be parenthesized in order to function with Python
1417 operator precedence behavior)::
1419 stmt = select(users_table).where(
1420 (users_table.c.name == "wendy") | (users_table.c.name == "jack")
1421 )
1423 The :func:`.or_` construct must be given at least one positional
1424 argument in order to be valid; a :func:`.or_` construct with no
1425 arguments is ambiguous. To produce an "empty" or dynamically
1426 generated :func:`.or_` expression, from a given list of expressions,
1427 a "default" element of :func:`_sql.false` (or just ``False``) should be
1428 specified::
1430 from sqlalchemy import false
1432 or_criteria = or_(false(), *expressions)
1434 The above expression will compile to SQL as the expression ``false``
1435 or ``0 = 1``, depending on backend, if no other expressions are
1436 present. If expressions are present, then the :func:`_sql.false` value is
1437 ignored as it does not affect the outcome of an OR expression which
1438 has other elements.
1440 .. deprecated:: 1.4 The :func:`.or_` element now requires that at
1441 least one argument is passed; creating the :func:`.or_` construct
1442 with no arguments is deprecated, and will emit a deprecation warning
1443 while continuing to produce a blank SQL string.
1445 .. seealso::
1447 :func:`.and_`
1449 """
1450 ...
1453if not TYPE_CHECKING:
1454 # handle deprecated case which allows zero-arguments
1455 def or_(*clauses): # noqa: F811
1456 """Produce a conjunction of expressions joined by ``OR``.
1458 E.g.::
1460 from sqlalchemy import or_
1462 stmt = select(users_table).where(
1463 or_(users_table.c.name == "wendy", users_table.c.name == "jack")
1464 )
1466 The :func:`.or_` conjunction is also available using the
1467 Python ``|`` operator (though note that compound expressions
1468 need to be parenthesized in order to function with Python
1469 operator precedence behavior)::
1471 stmt = select(users_table).where(
1472 (users_table.c.name == "wendy") | (users_table.c.name == "jack")
1473 )
1475 The :func:`.or_` construct must be given at least one positional
1476 argument in order to be valid; a :func:`.or_` construct with no
1477 arguments is ambiguous. To produce an "empty" or dynamically
1478 generated :func:`.or_` expression, from a given list of expressions,
1479 a "default" element of :func:`_sql.false` (or just ``False``) should be
1480 specified::
1482 from sqlalchemy import false
1484 or_criteria = or_(false(), *expressions)
1486 The above expression will compile to SQL as the expression ``false``
1487 or ``0 = 1``, depending on backend, if no other expressions are
1488 present. If expressions are present, then the :func:`_sql.false` value
1489 is ignored as it does not affect the outcome of an OR expression which
1490 has other elements.
1492 .. deprecated:: 1.4 The :func:`.or_` element now requires that at
1493 least one argument is passed; creating the :func:`.or_` construct
1494 with no arguments is deprecated, and will emit a deprecation warning
1495 while continuing to produce a blank SQL string.
1497 .. seealso::
1499 :func:`.and_`
1501 """ # noqa: E501
1502 return BooleanClauseList.or_(*clauses)
1505def over(
1506 element: FunctionElement[_T],
1507 partition_by: Optional[_ByArgument] = None,
1508 order_by: Optional[_ByArgument] = None,
1509 range_: Optional[typing_Tuple[Optional[int], Optional[int]]] = None,
1510 rows: Optional[typing_Tuple[Optional[int], Optional[int]]] = None,
1511 groups: Optional[typing_Tuple[Optional[int], Optional[int]]] = None,
1512) -> Over[_T]:
1513 r"""Produce an :class:`.Over` object against a function.
1515 Used against aggregate or so-called "window" functions,
1516 for database backends that support window functions.
1518 :func:`_expression.over` is usually called using
1519 the :meth:`.FunctionElement.over` method, e.g.::
1521 func.row_number().over(order_by=mytable.c.some_column)
1523 Would produce:
1525 .. sourcecode:: sql
1527 ROW_NUMBER() OVER(ORDER BY some_column)
1529 Ranges are also possible using the :paramref:`.expression.over.range_`,
1530 :paramref:`.expression.over.rows`, and :paramref:`.expression.over.groups`
1531 parameters. These
1532 mutually-exclusive parameters each accept a 2-tuple, which contains
1533 a combination of integers and None::
1535 func.row_number().over(order_by=my_table.c.some_column, range_=(None, 0))
1537 The above would produce:
1539 .. sourcecode:: sql
1541 ROW_NUMBER() OVER(ORDER BY some_column
1542 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
1544 A value of ``None`` indicates "unbounded", a
1545 value of zero indicates "current row", and negative / positive
1546 integers indicate "preceding" and "following":
1548 * RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING::
1550 func.row_number().over(order_by="x", range_=(-5, 10))
1552 * ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW::
1554 func.row_number().over(order_by="x", rows=(None, 0))
1556 * RANGE BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING::
1558 func.row_number().over(order_by="x", range_=(-2, None))
1560 * RANGE BETWEEN 1 FOLLOWING AND 3 FOLLOWING::
1562 func.row_number().over(order_by="x", range_=(1, 3))
1564 * GROUPS BETWEEN 1 FOLLOWING AND 3 FOLLOWING::
1566 func.row_number().over(order_by="x", groups=(1, 3))
1568 :param element: a :class:`.FunctionElement`, :class:`.WithinGroup`,
1569 or other compatible construct.
1570 :param partition_by: a column element or string, or a list
1571 of such, that will be used as the PARTITION BY clause
1572 of the OVER construct.
1573 :param order_by: a column element or string, or a list
1574 of such, that will be used as the ORDER BY clause
1575 of the OVER construct.
1576 :param range\_: optional range clause for the window. This is a
1577 tuple value which can contain integer values or ``None``,
1578 and will render a RANGE BETWEEN PRECEDING / FOLLOWING clause.
1579 :param rows: optional rows clause for the window. This is a tuple
1580 value which can contain integer values or None, and will render
1581 a ROWS BETWEEN PRECEDING / FOLLOWING clause.
1582 :param groups: optional groups clause for the window. This is a
1583 tuple value which can contain integer values or ``None``,
1584 and will render a GROUPS BETWEEN PRECEDING / FOLLOWING clause.
1586 .. versionadded:: 2.0.40
1588 This function is also available from the :data:`~.expression.func`
1589 construct itself via the :meth:`.FunctionElement.over` method.
1591 .. seealso::
1593 :ref:`tutorial_window_functions` - in the :ref:`unified_tutorial`
1595 :data:`.expression.func`
1597 :func:`_expression.within_group`
1599 """ # noqa: E501
1600 return Over(element, partition_by, order_by, range_, rows, groups)
1603@_document_text_coercion("text", ":func:`.text`", ":paramref:`.text.text`")
1604def text(text: str) -> TextClause:
1605 r"""Construct a new :class:`_expression.TextClause` clause,
1606 representing
1607 a textual SQL string directly.
1609 E.g.::
1611 from sqlalchemy import text
1613 t = text("SELECT * FROM users")
1614 result = connection.execute(t)
1616 The advantages :func:`_expression.text`
1617 provides over a plain string are
1618 backend-neutral support for bind parameters, per-statement
1619 execution options, as well as
1620 bind parameter and result-column typing behavior, allowing
1621 SQLAlchemy type constructs to play a role when executing
1622 a statement that is specified literally. The construct can also
1623 be provided with a ``.c`` collection of column elements, allowing
1624 it to be embedded in other SQL expression constructs as a subquery.
1626 Bind parameters are specified by name, using the format ``:name``.
1627 E.g.::
1629 t = text("SELECT * FROM users WHERE id=:user_id")
1630 result = connection.execute(t, {"user_id": 12})
1632 For SQL statements where a colon is required verbatim, as within
1633 an inline string, use a backslash to escape::
1635 t = text(r"SELECT * FROM users WHERE name='\:username'")
1637 The :class:`_expression.TextClause`
1638 construct includes methods which can
1639 provide information about the bound parameters as well as the column
1640 values which would be returned from the textual statement, assuming
1641 it's an executable SELECT type of statement. The
1642 :meth:`_expression.TextClause.bindparams`
1643 method is used to provide bound
1644 parameter detail, and :meth:`_expression.TextClause.columns`
1645 method allows
1646 specification of return columns including names and types::
1648 t = (
1649 text("SELECT * FROM users WHERE id=:user_id")
1650 .bindparams(user_id=7)
1651 .columns(id=Integer, name=String)
1652 )
1654 for id, name in connection.execute(t):
1655 print(id, name)
1657 The :func:`_expression.text` construct is used in cases when
1658 a literal string SQL fragment is specified as part of a larger query,
1659 such as for the WHERE clause of a SELECT statement::
1661 s = select(users.c.id, users.c.name).where(text("id=:user_id"))
1662 result = connection.execute(s, {"user_id": 12})
1664 :func:`_expression.text` is also used for the construction
1665 of a full, standalone statement using plain text.
1666 As such, SQLAlchemy refers
1667 to it as an :class:`.Executable` object and may be used
1668 like any other statement passed to an ``.execute()`` method.
1670 :param text:
1671 the text of the SQL statement to be created. Use ``:<param>``
1672 to specify bind parameters; they will be compiled to their
1673 engine-specific format.
1675 .. seealso::
1677 :ref:`tutorial_select_arbitrary_text`
1679 """
1680 return TextClause(text)
1683def true() -> True_:
1684 """Return a constant :class:`.True_` construct.
1686 E.g.:
1688 .. sourcecode:: pycon+sql
1690 >>> from sqlalchemy import true
1691 >>> print(select(t.c.x).where(true()))
1692 {printsql}SELECT x FROM t WHERE true
1694 A backend which does not support true/false constants will render as
1695 an expression against 1 or 0:
1697 .. sourcecode:: pycon+sql
1699 >>> print(select(t.c.x).where(true()))
1700 {printsql}SELECT x FROM t WHERE 1 = 1
1702 The :func:`.true` and :func:`.false` constants also feature
1703 "short circuit" operation within an :func:`.and_` or :func:`.or_`
1704 conjunction:
1706 .. sourcecode:: pycon+sql
1708 >>> print(select(t.c.x).where(or_(t.c.x > 5, true())))
1709 {printsql}SELECT x FROM t WHERE true{stop}
1711 >>> print(select(t.c.x).where(and_(t.c.x > 5, false())))
1712 {printsql}SELECT x FROM t WHERE false{stop}
1714 .. seealso::
1716 :func:`.false`
1718 """
1720 return True_._instance()
1723def tuple_(
1724 *clauses: _ColumnExpressionArgument[Any],
1725 types: Optional[Sequence[_TypeEngineArgument[Any]]] = None,
1726) -> Tuple:
1727 """Return a :class:`.Tuple`.
1729 Main usage is to produce a composite IN construct using
1730 :meth:`.ColumnOperators.in_` ::
1732 from sqlalchemy import tuple_
1734 tuple_(table.c.col1, table.c.col2).in_([(1, 2), (5, 12), (10, 19)])
1736 .. versionchanged:: 1.3.6 Added support for SQLite IN tuples.
1738 .. warning::
1740 The composite IN construct is not supported by all backends, and is
1741 currently known to work on PostgreSQL, MySQL, and SQLite.
1742 Unsupported backends will raise a subclass of
1743 :class:`~sqlalchemy.exc.DBAPIError` when such an expression is
1744 invoked.
1746 """
1747 return Tuple(*clauses, types=types)
1750def type_coerce(
1751 expression: _ColumnExpressionOrLiteralArgument[Any],
1752 type_: _TypeEngineArgument[_T],
1753) -> TypeCoerce[_T]:
1754 r"""Associate a SQL expression with a particular type, without rendering
1755 ``CAST``.
1757 E.g.::
1759 from sqlalchemy import type_coerce
1761 stmt = select(type_coerce(log_table.date_string, StringDateTime()))
1763 The above construct will produce a :class:`.TypeCoerce` object, which
1764 does not modify the rendering in any way on the SQL side, with the
1765 possible exception of a generated label if used in a columns clause
1766 context:
1768 .. sourcecode:: sql
1770 SELECT date_string AS date_string FROM log
1772 When result rows are fetched, the ``StringDateTime`` type processor
1773 will be applied to result rows on behalf of the ``date_string`` column.
1775 .. note:: the :func:`.type_coerce` construct does not render any
1776 SQL syntax of its own, including that it does not imply
1777 parenthesization. Please use :meth:`.TypeCoerce.self_group`
1778 if explicit parenthesization is required.
1780 In order to provide a named label for the expression, use
1781 :meth:`_expression.ColumnElement.label`::
1783 stmt = select(
1784 type_coerce(log_table.date_string, StringDateTime()).label("date")
1785 )
1787 A type that features bound-value handling will also have that behavior
1788 take effect when literal values or :func:`.bindparam` constructs are
1789 passed to :func:`.type_coerce` as targets.
1790 For example, if a type implements the
1791 :meth:`.TypeEngine.bind_expression`
1792 method or :meth:`.TypeEngine.bind_processor` method or equivalent,
1793 these functions will take effect at statement compilation/execution
1794 time when a literal value is passed, as in::
1796 # bound-value handling of MyStringType will be applied to the
1797 # literal value "some string"
1798 stmt = select(type_coerce("some string", MyStringType))
1800 When using :func:`.type_coerce` with composed expressions, note that
1801 **parenthesis are not applied**. If :func:`.type_coerce` is being
1802 used in an operator context where the parenthesis normally present from
1803 CAST are necessary, use the :meth:`.TypeCoerce.self_group` method:
1805 .. sourcecode:: pycon+sql
1807 >>> some_integer = column("someint", Integer)
1808 >>> some_string = column("somestr", String)
1809 >>> expr = type_coerce(some_integer + 5, String) + some_string
1810 >>> print(expr)
1811 {printsql}someint + :someint_1 || somestr{stop}
1812 >>> expr = type_coerce(some_integer + 5, String).self_group() + some_string
1813 >>> print(expr)
1814 {printsql}(someint + :someint_1) || somestr{stop}
1816 :param expression: A SQL expression, such as a
1817 :class:`_expression.ColumnElement`
1818 expression or a Python string which will be coerced into a bound
1819 literal value.
1821 :param type\_: A :class:`.TypeEngine` class or instance indicating
1822 the type to which the expression is coerced.
1824 .. seealso::
1826 :ref:`tutorial_casts`
1828 :func:`.cast`
1830 """ # noqa
1831 return TypeCoerce(expression, type_)
1834def within_group(
1835 element: FunctionElement[_T], *order_by: _ColumnExpressionArgument[Any]
1836) -> WithinGroup[_T]:
1837 r"""Produce a :class:`.WithinGroup` object against a function.
1839 Used against so-called "ordered set aggregate" and "hypothetical
1840 set aggregate" functions, including :class:`.percentile_cont`,
1841 :class:`.rank`, :class:`.dense_rank`, etc.
1843 :func:`_expression.within_group` is usually called using
1844 the :meth:`.FunctionElement.within_group` method, e.g.::
1846 from sqlalchemy import within_group
1848 stmt = select(
1849 department.c.id,
1850 func.percentile_cont(0.5).within_group(department.c.salary.desc()),
1851 )
1853 The above statement would produce SQL similar to
1854 ``SELECT department.id, percentile_cont(0.5)
1855 WITHIN GROUP (ORDER BY department.salary DESC)``.
1857 :param element: a :class:`.FunctionElement` construct, typically
1858 generated by :data:`~.expression.func`.
1859 :param \*order_by: one or more column elements that will be used
1860 as the ORDER BY clause of the WITHIN GROUP construct.
1862 .. seealso::
1864 :ref:`tutorial_functions_within_group` - in the
1865 :ref:`unified_tutorial`
1867 :data:`.expression.func`
1869 :func:`_expression.over`
1871 """
1872 return WithinGroup(element, *order_by)