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-2026 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 When using a Python sequence with PostgreSQL, wrap it with
79 :func:`_sql.literal` to produce a SQL expression. Whether the sequence
80 can be adapted to an array parameter is DBAPI-specific::
82 expr = 5 == all_(literal([1, 2, 3]))
84 Comparison to NULL may work using ``None``::
86 None == all_(mytable.c.somearray)
88 The any_() / all_() operators also feature a special "operand flipping"
89 behavior such that if any_() / all_() are used on the left side of a
90 comparison using a standalone operator such as ``==``, ``!=``, etc.
91 (not including operator methods such as
92 :meth:`_sql.ColumnOperators.is_`) the rendered expression is flipped::
94 # would render '5 = ALL (column)`
95 all_(mytable.c.column) == 5
97 Or with ``None``, which note will not perform
98 the usual step of rendering "IS" as is normally the case for NULL::
100 # would render 'NULL = ALL(somearray)'
101 all_(mytable.c.somearray) == None
103 .. versionchanged:: 1.4.26 repaired the use of any_() / all_()
104 comparing to NULL on the right side to be flipped to the left.
106 The column-level :meth:`_sql.ColumnElement.all_` method (not to be
107 confused with :class:`_types.ARRAY` level
108 :meth:`_types.ARRAY.Comparator.all`) is shorthand for
109 ``all_(col)``::
111 5 == mytable.c.somearray.all_()
113 .. seealso::
115 :meth:`_sql.ColumnOperators.all_`
117 :func:`_expression.any_`
119 """
120 return CollectionAggregate._create_all(expr)
123def and_( # type: ignore[empty-body]
124 initial_clause: Union[Literal[True], _ColumnExpressionArgument[bool]],
125 *clauses: _ColumnExpressionArgument[bool],
126) -> ColumnElement[bool]:
127 r"""Produce a conjunction of expressions joined by ``AND``.
129 E.g.::
131 from sqlalchemy import and_
133 stmt = select(users_table).where(
134 and_(users_table.c.name == "wendy", users_table.c.enrolled == True)
135 )
137 The :func:`.and_` conjunction is also available using the
138 Python ``&`` operator (though note that compound expressions
139 need to be parenthesized in order to function with Python
140 operator precedence behavior)::
142 stmt = select(users_table).where(
143 (users_table.c.name == "wendy") & (users_table.c.enrolled == True)
144 )
146 The :func:`.and_` operation is also implicit in some cases;
147 the :meth:`_expression.Select.where`
148 method for example can be invoked multiple
149 times against a statement, which will have the effect of each
150 clause being combined using :func:`.and_`::
152 stmt = (
153 select(users_table)
154 .where(users_table.c.name == "wendy")
155 .where(users_table.c.enrolled == True)
156 )
158 The :func:`.and_` construct must be given at least one positional
159 argument in order to be valid; a :func:`.and_` construct with no
160 arguments is ambiguous. To produce an "empty" or dynamically
161 generated :func:`.and_` expression, from a given list of expressions,
162 a "default" element of :func:`_sql.true` (or just ``True``) should be
163 specified::
165 from sqlalchemy import true
167 criteria = and_(true(), *expressions)
169 The above expression will compile to SQL as the expression ``true``
170 or ``1 = 1``, depending on backend, if no other expressions are
171 present. If expressions are present, then the :func:`_sql.true` value is
172 ignored as it does not affect the outcome of an AND expression that
173 has other elements.
175 .. deprecated:: 1.4 The :func:`.and_` element now requires that at
176 least one argument is passed; creating the :func:`.and_` construct
177 with no arguments is deprecated, and will emit a deprecation warning
178 while continuing to produce a blank SQL string.
180 .. seealso::
182 :func:`.or_`
184 """
185 ...
188if not TYPE_CHECKING:
189 # handle deprecated case which allows zero-arguments
190 def and_(*clauses): # noqa: F811
191 r"""Produce a conjunction of expressions joined by ``AND``.
193 E.g.::
195 from sqlalchemy import and_
197 stmt = select(users_table).where(
198 and_(users_table.c.name == "wendy", users_table.c.enrolled == True)
199 )
201 The :func:`.and_` conjunction is also available using the
202 Python ``&`` operator (though note that compound expressions
203 need to be parenthesized in order to function with Python
204 operator precedence behavior)::
206 stmt = select(users_table).where(
207 (users_table.c.name == "wendy") & (users_table.c.enrolled == True)
208 )
210 The :func:`.and_` operation is also implicit in some cases;
211 the :meth:`_expression.Select.where`
212 method for example can be invoked multiple
213 times against a statement, which will have the effect of each
214 clause being combined using :func:`.and_`::
216 stmt = (
217 select(users_table)
218 .where(users_table.c.name == "wendy")
219 .where(users_table.c.enrolled == True)
220 )
222 The :func:`.and_` construct must be given at least one positional
223 argument in order to be valid; a :func:`.and_` construct with no
224 arguments is ambiguous. To produce an "empty" or dynamically
225 generated :func:`.and_` expression, from a given list of expressions,
226 a "default" element of :func:`_sql.true` (or just ``True``) should be
227 specified::
229 from sqlalchemy import true
231 criteria = and_(true(), *expressions)
233 The above expression will compile to SQL as the expression ``true``
234 or ``1 = 1``, depending on backend, if no other expressions are
235 present. If expressions are present, then the :func:`_sql.true` value
236 is ignored as it does not affect the outcome of an AND expression that
237 has other elements.
239 .. deprecated:: 1.4 The :func:`.and_` element now requires that at
240 least one argument is passed; creating the :func:`.and_` construct
241 with no arguments is deprecated, and will emit a deprecation warning
242 while continuing to produce a blank SQL string.
244 .. seealso::
246 :func:`.or_`
248 """ # noqa: E501
249 return BooleanClauseList.and_(*clauses)
252def any_(expr: _ColumnExpressionArgument[_T]) -> CollectionAggregate[bool]:
253 """Produce an ANY expression.
255 For dialects such as that of PostgreSQL, this operator applies
256 to usage of the :class:`_types.ARRAY` datatype, for that of
257 MySQL, it may apply to a subquery. e.g.::
259 # renders on PostgreSQL:
260 # '5 = ANY (somearray)'
261 expr = 5 == any_(mytable.c.somearray)
263 # renders on MySQL:
264 # '5 = ANY (SELECT value FROM table)'
265 expr = 5 == any_(select(table.c.value))
267 When using a Python sequence with PostgreSQL, wrap it with
268 :func:`_sql.literal` to produce a SQL expression. Whether the sequence
269 can be adapted to an array parameter is DBAPI-specific::
271 expr = 5 == any_(literal([1, 2, 3]))
273 Comparison to NULL may work using ``None`` or :func:`_sql.null`::
275 None == any_(mytable.c.somearray)
277 The any_() / all_() operators also feature a special "operand flipping"
278 behavior such that if any_() / all_() are used on the left side of a
279 comparison using a standalone operator such as ``==``, ``!=``, etc.
280 (not including operator methods such as
281 :meth:`_sql.ColumnOperators.is_`) the rendered expression is flipped::
283 # would render '5 = ANY (column)`
284 any_(mytable.c.column) == 5
286 Or with ``None``, which note will not perform
287 the usual step of rendering "IS" as is normally the case for NULL::
289 # would render 'NULL = ANY(somearray)'
290 any_(mytable.c.somearray) == None
292 .. versionchanged:: 1.4.26 repaired the use of any_() / all_()
293 comparing to NULL on the right side to be flipped to the left.
295 The column-level :meth:`_sql.ColumnElement.any_` method (not to be
296 confused with :class:`_types.ARRAY` level
297 :meth:`_types.ARRAY.Comparator.any`) is shorthand for
298 ``any_(col)``::
300 5 = mytable.c.somearray.any_()
302 .. seealso::
304 :meth:`_sql.ColumnOperators.any_`
306 :func:`_expression.all_`
308 """
309 return CollectionAggregate._create_any(expr)
312def asc(
313 column: _ColumnExpressionOrStrLabelArgument[_T],
314) -> UnaryExpression[_T]:
315 """Produce an ascending ``ORDER BY`` clause element.
317 e.g.::
319 from sqlalchemy import asc
321 stmt = select(users_table).order_by(asc(users_table.c.name))
323 will produce SQL as:
325 .. sourcecode:: sql
327 SELECT id, name FROM user ORDER BY name ASC
329 The :func:`.asc` function is a standalone version of the
330 :meth:`_expression.ColumnElement.asc`
331 method available on all SQL expressions,
332 e.g.::
335 stmt = select(users_table).order_by(users_table.c.name.asc())
337 :param column: A :class:`_expression.ColumnElement` (e.g.
338 scalar SQL expression)
339 with which to apply the :func:`.asc` operation.
341 .. seealso::
343 :func:`.desc`
345 :func:`.nulls_first`
347 :func:`.nulls_last`
349 :meth:`_expression.Select.order_by`
351 """
352 return UnaryExpression._create_asc(column)
355def collate(
356 expression: _ColumnExpressionArgument[str], collation: str
357) -> BinaryExpression[str]:
358 """Return the clause ``expression COLLATE collation``.
360 e.g.::
362 collate(mycolumn, "utf8_bin")
364 produces:
366 .. sourcecode:: sql
368 mycolumn COLLATE utf8_bin
370 The collation expression is also quoted if it is a case sensitive
371 identifier, e.g. contains uppercase characters.
373 .. versionchanged:: 1.2 quoting is automatically applied to COLLATE
374 expressions if they are case sensitive.
376 """
377 return CollationClause._create_collation_expression(expression, collation)
380def between(
381 expr: _ColumnExpressionOrLiteralArgument[_T],
382 lower_bound: Any,
383 upper_bound: Any,
384 symmetric: bool = False,
385) -> BinaryExpression[bool]:
386 """Produce a ``BETWEEN`` predicate clause.
388 E.g.::
390 from sqlalchemy import between
392 stmt = select(users_table).where(between(users_table.c.id, 5, 7))
394 Would produce SQL resembling:
396 .. sourcecode:: sql
398 SELECT id, name FROM user WHERE id BETWEEN :id_1 AND :id_2
400 The :func:`.between` function is a standalone version of the
401 :meth:`_expression.ColumnElement.between` method available on all
402 SQL expressions, as in::
404 stmt = select(users_table).where(users_table.c.id.between(5, 7))
406 All arguments passed to :func:`.between`, including the left side
407 column expression, are coerced from Python scalar values if a
408 the value is not a :class:`_expression.ColumnElement` subclass.
409 For example,
410 three fixed values can be compared as in::
412 print(between(5, 3, 7))
414 Which would produce::
416 :param_1 BETWEEN :param_2 AND :param_3
418 :param expr: a column expression, typically a
419 :class:`_expression.ColumnElement`
420 instance or alternatively a Python scalar expression to be coerced
421 into a column expression, serving as the left side of the ``BETWEEN``
422 expression.
424 :param lower_bound: a column or Python scalar expression serving as the
425 lower bound of the right side of the ``BETWEEN`` expression.
427 :param upper_bound: a column or Python scalar expression serving as the
428 upper bound of the right side of the ``BETWEEN`` expression.
430 :param symmetric: if True, will render " BETWEEN SYMMETRIC ". Note
431 that not all databases support this syntax.
433 .. seealso::
435 :meth:`_expression.ColumnElement.between`
437 """
438 col_expr = coercions.expect(roles.ExpressionElementRole, expr)
439 return col_expr.between(lower_bound, upper_bound, symmetric=symmetric)
442def outparam(
443 key: str, type_: Optional[TypeEngine[_T]] = None
444) -> BindParameter[_T]:
445 """Create an 'OUT' parameter for usage in functions (stored procedures),
446 for databases which support them.
448 The ``outparam`` can be used like a regular function parameter.
449 The "output" value will be available from the
450 :class:`~sqlalchemy.engine.CursorResult` object via its ``out_parameters``
451 attribute, which returns a dictionary containing the values.
453 """
454 return BindParameter(key, None, type_=type_, unique=False, isoutparam=True)
457@overload
458def not_(clause: BinaryExpression[_T]) -> BinaryExpression[_T]: ...
461@overload
462def not_(clause: _ColumnExpressionArgument[_T]) -> ColumnElement[_T]: ...
465def not_(clause: _ColumnExpressionArgument[_T]) -> ColumnElement[_T]:
466 """Return a negation of the given clause, i.e. ``NOT(clause)``.
468 The ``~`` operator is also overloaded on all
469 :class:`_expression.ColumnElement` subclasses to produce the
470 same result.
472 """
474 return coercions.expect(roles.ExpressionElementRole, clause).__invert__()
477def bindparam(
478 key: Optional[str],
479 value: Any = _NoArg.NO_ARG,
480 type_: Optional[_TypeEngineArgument[_T]] = None,
481 unique: bool = False,
482 required: Union[bool, Literal[_NoArg.NO_ARG]] = _NoArg.NO_ARG,
483 quote: Optional[bool] = None,
484 callable_: Optional[Callable[[], Any]] = None,
485 expanding: bool = False,
486 isoutparam: bool = False,
487 literal_execute: bool = False,
488) -> BindParameter[_T]:
489 r"""Produce a "bound expression".
491 The return value is an instance of :class:`.BindParameter`; this
492 is a :class:`_expression.ColumnElement`
493 subclass which represents a so-called
494 "placeholder" value in a SQL expression, the value of which is
495 supplied at the point at which the statement in executed against a
496 database connection.
498 In SQLAlchemy, the :func:`.bindparam` construct has
499 the ability to carry along the actual value that will be ultimately
500 used at expression time. In this way, it serves not just as
501 a "placeholder" for eventual population, but also as a means of
502 representing so-called "unsafe" values which should not be rendered
503 directly in a SQL statement, but rather should be passed along
504 to the :term:`DBAPI` as values which need to be correctly escaped
505 and potentially handled for type-safety.
507 When using :func:`.bindparam` explicitly, the use case is typically
508 one of traditional deferment of parameters; the :func:`.bindparam`
509 construct accepts a name which can then be referred to at execution
510 time::
512 from sqlalchemy import bindparam
514 stmt = select(users_table).where(
515 users_table.c.name == bindparam("username")
516 )
518 The above statement, when rendered, will produce SQL similar to:
520 .. sourcecode:: sql
522 SELECT id, name FROM user WHERE name = :username
524 In order to populate the value of ``:username`` above, the value
525 would typically be applied at execution time to a method
526 like :meth:`_engine.Connection.execute`::
528 result = connection.execute(stmt, {"username": "wendy"})
530 Explicit use of :func:`.bindparam` is also common when producing
531 UPDATE or DELETE statements that are to be invoked multiple times,
532 where the WHERE criterion of the statement is to change on each
533 invocation, such as::
535 stmt = (
536 users_table.update()
537 .where(user_table.c.name == bindparam("username"))
538 .values(fullname=bindparam("fullname"))
539 )
541 connection.execute(
542 stmt,
543 [
544 {"username": "wendy", "fullname": "Wendy Smith"},
545 {"username": "jack", "fullname": "Jack Jones"},
546 ],
547 )
549 SQLAlchemy's Core expression system makes wide use of
550 :func:`.bindparam` in an implicit sense. It is typical that Python
551 literal values passed to virtually all SQL expression functions are
552 coerced into fixed :func:`.bindparam` constructs. For example, given
553 a comparison operation such as::
555 expr = users_table.c.name == "Wendy"
557 The above expression will produce a :class:`.BinaryExpression`
558 construct, where the left side is the :class:`_schema.Column` object
559 representing the ``name`` column, and the right side is a
560 :class:`.BindParameter` representing the literal value::
562 print(repr(expr.right))
563 BindParameter("%(4327771088 name)s", "Wendy", type_=String())
565 The expression above will render SQL such as:
567 .. sourcecode:: sql
569 user.name = :name_1
571 Where the ``:name_1`` parameter name is an anonymous name. The
572 actual string ``Wendy`` is not in the rendered string, but is carried
573 along where it is later used within statement execution. If we
574 invoke a statement like the following::
576 stmt = select(users_table).where(users_table.c.name == "Wendy")
577 result = connection.execute(stmt)
579 We would see SQL logging output as:
581 .. sourcecode:: sql
583 SELECT "user".id, "user".name
584 FROM "user"
585 WHERE "user".name = %(name_1)s
586 {'name_1': 'Wendy'}
588 Above, we see that ``Wendy`` is passed as a parameter to the database,
589 while the placeholder ``:name_1`` is rendered in the appropriate form
590 for the target database, in this case the PostgreSQL database.
592 Similarly, :func:`.bindparam` is invoked automatically when working
593 with :term:`CRUD` statements as far as the "VALUES" portion is
594 concerned. The :func:`_expression.insert` construct produces an
595 ``INSERT`` expression which will, at statement execution time, generate
596 bound placeholders based on the arguments passed, as in::
598 stmt = users_table.insert()
599 result = connection.execute(stmt, {"name": "Wendy"})
601 The above will produce SQL output as:
603 .. sourcecode:: sql
605 INSERT INTO "user" (name) VALUES (%(name)s)
606 {'name': 'Wendy'}
608 The :class:`_expression.Insert` construct, at
609 compilation/execution time, rendered a single :func:`.bindparam`
610 mirroring the column name ``name`` as a result of the single ``name``
611 parameter we passed to the :meth:`_engine.Connection.execute` method.
613 :param key:
614 the key (e.g. the name) for this bind param.
615 Will be used in the generated
616 SQL statement for dialects that use named parameters. This
617 value may be modified when part of a compilation operation,
618 if other :class:`BindParameter` objects exist with the same
619 key, or if its length is too long and truncation is
620 required.
622 If omitted, an "anonymous" name is generated for the bound parameter;
623 when given a value to bind, the end result is equivalent to calling upon
624 the :func:`.literal` function with a value to bind, particularly
625 if the :paramref:`.bindparam.unique` parameter is also provided.
627 :param value:
628 Initial value for this bind param. Will be used at statement
629 execution time as the value for this parameter passed to the
630 DBAPI, if no other value is indicated to the statement execution
631 method for this particular parameter name. Defaults to ``None``.
633 :param callable\_:
634 A callable function that takes the place of "value". The function
635 will be called at statement execution time to determine the
636 ultimate value. Used for scenarios where the actual bind
637 value cannot be determined at the point at which the clause
638 construct is created, but embedded bind values are still desirable.
640 :param type\_:
641 A :class:`.TypeEngine` class or instance representing an optional
642 datatype for this :func:`.bindparam`. If not passed, a type
643 may be determined automatically for the bind, based on the given
644 value; for example, trivial Python types such as ``str``,
645 ``int``, ``bool``
646 may result in the :class:`.String`, :class:`.Integer` or
647 :class:`.Boolean` types being automatically selected.
649 The type of a :func:`.bindparam` is significant especially in that
650 the type will apply pre-processing to the value before it is
651 passed to the database. For example, a :func:`.bindparam` which
652 refers to a datetime value, and is specified as holding the
653 :class:`.DateTime` type, may apply conversion needed to the
654 value (such as stringification on SQLite) before passing the value
655 to the database.
657 :param unique:
658 if True, the key name of this :class:`.BindParameter` will be
659 modified if another :class:`.BindParameter` of the same name
660 already has been located within the containing
661 expression. This flag is used generally by the internals
662 when producing so-called "anonymous" bound expressions, it
663 isn't generally applicable to explicitly-named :func:`.bindparam`
664 constructs.
666 :param required:
667 If ``True``, a value is required at execution time. If not passed,
668 it defaults to ``True`` if neither :paramref:`.bindparam.value`
669 or :paramref:`.bindparam.callable` were passed. If either of these
670 parameters are present, then :paramref:`.bindparam.required`
671 defaults to ``False``.
673 :param quote:
674 True if this parameter name requires quoting and is not
675 currently known as a SQLAlchemy reserved word; this currently
676 only applies to the Oracle Database backends, where bound names must
677 sometimes be quoted.
679 :param isoutparam:
680 if True, the parameter should be treated like a stored procedure
681 "OUT" parameter. This applies to backends such as Oracle Database which
682 support OUT parameters.
684 :param expanding:
685 if True, this parameter will be treated as an "expanding" parameter
686 at execution time; the parameter value is expected to be a sequence,
687 rather than a scalar value, and the string SQL statement will
688 be transformed on a per-execution basis to accommodate the sequence
689 with a variable number of parameter slots passed to the DBAPI.
690 This is to allow statement caching to be used in conjunction with
691 an IN clause.
693 .. seealso::
695 :meth:`.ColumnOperators.in_`
697 :ref:`baked_in` - with baked queries
699 .. note:: The "expanding" feature does not support "executemany"-
700 style parameter sets.
702 .. versionadded:: 1.2
704 .. versionchanged:: 1.3 the "expanding" bound parameter feature now
705 supports empty lists.
707 :param literal_execute:
708 if True, the bound parameter will be rendered in the compile phase
709 with a special "POSTCOMPILE" token, and the SQLAlchemy compiler will
710 render the final value of the parameter into the SQL statement at
711 statement execution time, omitting the value from the parameter
712 dictionary / list passed to DBAPI ``cursor.execute()``. This
713 produces a similar effect as that of using the ``literal_binds``,
714 compilation flag, however takes place as the statement is sent to
715 the DBAPI ``cursor.execute()`` method, rather than when the statement
716 is compiled. The primary use of this
717 capability is for rendering LIMIT / OFFSET clauses for database
718 drivers that can't accommodate for bound parameters in these
719 contexts, while allowing SQL constructs to be cacheable at the
720 compilation level.
722 .. versionadded:: 1.4 Added "post compile" bound parameters
724 .. seealso::
726 :ref:`change_4808`.
728 .. seealso::
730 :ref:`tutorial_sending_parameters` - in the
731 :ref:`unified_tutorial`
734 """
735 return BindParameter(
736 key,
737 value,
738 type_,
739 unique,
740 required,
741 quote,
742 callable_,
743 expanding,
744 isoutparam,
745 literal_execute,
746 )
749def case(
750 *whens: Union[
751 typing_Tuple[_ColumnExpressionArgument[bool], Any], Mapping[Any, Any]
752 ],
753 value: Optional[Any] = None,
754 else_: Optional[Any] = None,
755) -> Case[Any]:
756 r"""Produce a ``CASE`` expression.
758 The ``CASE`` construct in SQL is a conditional object that
759 acts somewhat analogously to an "if/then" construct in other
760 languages. It returns an instance of :class:`.Case`.
762 :func:`.case` in its usual form is passed a series of "when"
763 constructs, that is, a list of conditions and results as tuples::
765 from sqlalchemy import case
767 stmt = select(users_table).where(
768 case(
769 (users_table.c.name == "wendy", "W"),
770 (users_table.c.name == "jack", "J"),
771 else_="E",
772 )
773 )
775 The above statement will produce SQL resembling:
777 .. sourcecode:: sql
779 SELECT id, name FROM user
780 WHERE CASE
781 WHEN (name = :name_1) THEN :param_1
782 WHEN (name = :name_2) THEN :param_2
783 ELSE :param_3
784 END
786 When simple equality expressions of several values against a single
787 parent column are needed, :func:`.case` also has a "shorthand" format
788 used via the
789 :paramref:`.case.value` parameter, which is passed a column
790 expression to be compared. In this form, the :paramref:`.case.whens`
791 parameter is passed as a dictionary containing expressions to be
792 compared against keyed to result expressions. The statement below is
793 equivalent to the preceding statement::
795 stmt = select(users_table).where(
796 case({"wendy": "W", "jack": "J"}, value=users_table.c.name, else_="E")
797 )
799 The values which are accepted as result values in
800 :paramref:`.case.whens` as well as with :paramref:`.case.else_` are
801 coerced from Python literals into :func:`.bindparam` constructs.
802 SQL expressions, e.g. :class:`_expression.ColumnElement` constructs,
803 are accepted
804 as well. To coerce a literal string expression into a constant
805 expression rendered inline, use the :func:`_expression.literal_column`
806 construct,
807 as in::
809 from sqlalchemy import case, literal_column
811 case(
812 (orderline.c.qty > 100, literal_column("'greaterthan100'")),
813 (orderline.c.qty > 10, literal_column("'greaterthan10'")),
814 else_=literal_column("'lessthan10'"),
815 )
817 The above will render the given constants without using bound
818 parameters for the result values (but still for the comparison
819 values), as in:
821 .. sourcecode:: sql
823 CASE
824 WHEN (orderline.qty > :qty_1) THEN 'greaterthan100'
825 WHEN (orderline.qty > :qty_2) THEN 'greaterthan10'
826 ELSE 'lessthan10'
827 END
829 :param \*whens: The criteria to be compared against,
830 :paramref:`.case.whens` accepts two different forms, based on
831 whether or not :paramref:`.case.value` is used.
833 .. versionchanged:: 1.4 the :func:`_sql.case`
834 function now accepts the series of WHEN conditions positionally
836 In the first form, it accepts multiple 2-tuples passed as positional
837 arguments; each 2-tuple consists of ``(<sql expression>, <value>)``,
838 where the SQL expression is a boolean expression and "value" is a
839 resulting value, e.g.::
841 case(
842 (users_table.c.name == "wendy", "W"),
843 (users_table.c.name == "jack", "J"),
844 )
846 In the second form, it accepts a Python dictionary of comparison
847 values mapped to a resulting value; this form requires
848 :paramref:`.case.value` to be present, and values will be compared
849 using the ``==`` operator, e.g.::
851 case({"wendy": "W", "jack": "J"}, value=users_table.c.name)
853 :param value: An optional SQL expression which will be used as a
854 fixed "comparison point" for candidate values within a dictionary
855 passed to :paramref:`.case.whens`.
857 :param else\_: An optional SQL expression which will be the evaluated
858 result of the ``CASE`` construct if all expressions within
859 :paramref:`.case.whens` evaluate to false. When omitted, most
860 databases will produce a result of NULL if none of the "when"
861 expressions evaluate to true.
864 """ # noqa: E501
865 return Case(*whens, value=value, else_=else_)
868def cast(
869 expression: _ColumnExpressionOrLiteralArgument[Any],
870 type_: _TypeEngineArgument[_T],
871) -> Cast[_T]:
872 r"""Produce a ``CAST`` expression.
874 :func:`.cast` returns an instance of :class:`.Cast`.
876 E.g.::
878 from sqlalchemy import cast, Numeric
880 stmt = select(cast(product_table.c.unit_price, Numeric(10, 4)))
882 The above statement will produce SQL resembling:
884 .. sourcecode:: sql
886 SELECT CAST(unit_price AS NUMERIC(10, 4)) FROM product
888 The :func:`.cast` function performs two distinct functions when
889 used. The first is that it renders the ``CAST`` expression within
890 the resulting SQL string. The second is that it associates the given
891 type (e.g. :class:`.TypeEngine` class or instance) with the column
892 expression on the Python side, which means the expression will take
893 on the expression operator behavior associated with that type,
894 as well as the bound-value handling and result-row-handling behavior
895 of the type.
897 An alternative to :func:`.cast` is the :func:`.type_coerce` function.
898 This function performs the second task of associating an expression
899 with a specific type, but does not render the ``CAST`` expression
900 in SQL.
902 :param expression: A SQL expression, such as a
903 :class:`_expression.ColumnElement`
904 expression or a Python string which will be coerced into a bound
905 literal value.
907 :param type\_: A :class:`.TypeEngine` class or instance indicating
908 the type to which the ``CAST`` should apply.
910 .. seealso::
912 :ref:`tutorial_casts`
914 :func:`.try_cast` - an alternative to CAST that results in
915 NULLs when the cast fails, instead of raising an error.
916 Only supported by some dialects.
918 :func:`.type_coerce` - an alternative to CAST that coerces the type
919 on the Python side only, which is often sufficient to generate the
920 correct SQL and data coercion.
923 """
924 return Cast(expression, type_)
927def try_cast(
928 expression: _ColumnExpressionOrLiteralArgument[Any],
929 type_: _TypeEngineArgument[_T],
930) -> TryCast[_T]:
931 """Produce a ``TRY_CAST`` expression for backends which support it;
932 this is a ``CAST`` which returns NULL for un-castable conversions.
934 In SQLAlchemy, this construct is supported **only** by the SQL Server
935 dialect, and will raise a :class:`.CompileError` if used on other
936 included backends. However, third party backends may also support
937 this construct.
939 .. tip:: As :func:`_sql.try_cast` originates from the SQL Server dialect,
940 it's importable both from ``sqlalchemy.`` as well as from
941 ``sqlalchemy.dialects.mssql``.
943 :func:`_sql.try_cast` returns an instance of :class:`.TryCast` and
944 generally behaves similarly to the :class:`.Cast` construct;
945 at the SQL level, the difference between ``CAST`` and ``TRY_CAST``
946 is that ``TRY_CAST`` returns NULL for an un-castable expression,
947 such as attempting to cast a string ``"hi"`` to an integer value.
949 E.g.::
951 from sqlalchemy import select, try_cast, Numeric
953 stmt = select(try_cast(product_table.c.unit_price, Numeric(10, 4)))
955 The above would render on Microsoft SQL Server as:
957 .. sourcecode:: sql
959 SELECT TRY_CAST (product_table.unit_price AS NUMERIC(10, 4))
960 FROM product_table
962 .. versionadded:: 2.0.14 :func:`.try_cast` has been
963 generalized from the SQL Server dialect into a general use
964 construct that may be supported by additional dialects.
966 """
967 return TryCast(expression, type_)
970def column(
971 text: str,
972 type_: Optional[_TypeEngineArgument[_T]] = None,
973 is_literal: bool = False,
974 _selectable: Optional[FromClause] = None,
975) -> ColumnClause[_T]:
976 """Produce a :class:`.ColumnClause` object.
978 The :class:`.ColumnClause` is a lightweight analogue to the
979 :class:`_schema.Column` class. The :func:`_expression.column`
980 function can
981 be invoked with just a name alone, as in::
983 from sqlalchemy import column
985 id, name = column("id"), column("name")
986 stmt = select(id, name).select_from("user")
988 The above statement would produce SQL like:
990 .. sourcecode:: sql
992 SELECT id, name FROM user
994 Once constructed, :func:`_expression.column`
995 may be used like any other SQL
996 expression element such as within :func:`_expression.select`
997 constructs::
999 from sqlalchemy.sql import column
1001 id, name = column("id"), column("name")
1002 stmt = select(id, name).select_from("user")
1004 The text handled by :func:`_expression.column`
1005 is assumed to be handled
1006 like the name of a database column; if the string contains mixed case,
1007 special characters, or matches a known reserved word on the target
1008 backend, the column expression will render using the quoting
1009 behavior determined by the backend. To produce a textual SQL
1010 expression that is rendered exactly without any quoting,
1011 use :func:`_expression.literal_column` instead,
1012 or pass ``True`` as the
1013 value of :paramref:`_expression.column.is_literal`. Additionally,
1014 full SQL
1015 statements are best handled using the :func:`_expression.text`
1016 construct.
1018 :func:`_expression.column` can be used in a table-like
1019 fashion by combining it with the :func:`.table` function
1020 (which is the lightweight analogue to :class:`_schema.Table`
1021 ) to produce
1022 a working table construct with minimal boilerplate::
1024 from sqlalchemy import table, column, select
1026 user = table(
1027 "user",
1028 column("id"),
1029 column("name"),
1030 column("description"),
1031 )
1033 stmt = select(user.c.description).where(user.c.name == "wendy")
1035 A :func:`_expression.column` / :func:`.table`
1036 construct like that illustrated
1037 above can be created in an
1038 ad-hoc fashion and is not associated with any
1039 :class:`_schema.MetaData`, DDL, or events, unlike its
1040 :class:`_schema.Table` counterpart.
1042 :param text: the text of the element.
1044 :param type: :class:`_types.TypeEngine` object which can associate
1045 this :class:`.ColumnClause` with a type.
1047 :param is_literal: if True, the :class:`.ColumnClause` is assumed to
1048 be an exact expression that will be delivered to the output with no
1049 quoting rules applied regardless of case sensitive settings. the
1050 :func:`_expression.literal_column()` function essentially invokes
1051 :func:`_expression.column` while passing ``is_literal=True``.
1053 .. seealso::
1055 :class:`_schema.Column`
1057 :func:`_expression.literal_column`
1059 :func:`.table`
1061 :func:`_expression.text`
1063 :ref:`tutorial_select_arbitrary_text`
1065 """
1066 return ColumnClause(text, type_, is_literal, _selectable)
1069def desc(
1070 column: _ColumnExpressionOrStrLabelArgument[_T],
1071) -> UnaryExpression[_T]:
1072 """Produce a descending ``ORDER BY`` clause element.
1074 e.g.::
1076 from sqlalchemy import desc
1078 stmt = select(users_table).order_by(desc(users_table.c.name))
1080 will produce SQL as:
1082 .. sourcecode:: sql
1084 SELECT id, name FROM user ORDER BY name DESC
1086 The :func:`.desc` function is a standalone version of the
1087 :meth:`_expression.ColumnElement.desc`
1088 method available on all SQL expressions,
1089 e.g.::
1092 stmt = select(users_table).order_by(users_table.c.name.desc())
1094 :param column: A :class:`_expression.ColumnElement` (e.g.
1095 scalar SQL expression)
1096 with which to apply the :func:`.desc` operation.
1098 .. seealso::
1100 :func:`.asc`
1102 :func:`.nulls_first`
1104 :func:`.nulls_last`
1106 :meth:`_expression.Select.order_by`
1108 """
1109 return UnaryExpression._create_desc(column)
1112def distinct(expr: _ColumnExpressionArgument[_T]) -> UnaryExpression[_T]:
1113 """Produce an column-expression-level unary ``DISTINCT`` clause.
1115 This applies the ``DISTINCT`` keyword to an **individual column
1116 expression** (e.g. not the whole statement), and renders **specifically
1117 in that column position**; this is used for containment within
1118 an aggregate function, as in::
1120 from sqlalchemy import distinct, func
1122 stmt = select(users_table.c.id, func.count(distinct(users_table.c.name)))
1124 The above would produce an statement resembling:
1126 .. sourcecode:: sql
1128 SELECT user.id, count(DISTINCT user.name) FROM user
1130 .. tip:: The :func:`_sql.distinct` function does **not** apply DISTINCT
1131 to the full SELECT statement, instead applying a DISTINCT modifier
1132 to **individual column expressions**. For general ``SELECT DISTINCT``
1133 support, use the
1134 :meth:`_sql.Select.distinct` method on :class:`_sql.Select`.
1136 The :func:`.distinct` function is also available as a column-level
1137 method, e.g. :meth:`_expression.ColumnElement.distinct`, as in::
1139 stmt = select(func.count(users_table.c.name.distinct()))
1141 The :func:`.distinct` operator is different from the
1142 :meth:`_expression.Select.distinct` method of
1143 :class:`_expression.Select`,
1144 which produces a ``SELECT`` statement
1145 with ``DISTINCT`` applied to the result set as a whole,
1146 e.g. a ``SELECT DISTINCT`` expression. See that method for further
1147 information.
1149 .. seealso::
1151 :meth:`_expression.ColumnElement.distinct`
1153 :meth:`_expression.Select.distinct`
1155 :data:`.func`
1157 """ # noqa: E501
1158 return UnaryExpression._create_distinct(expr)
1161def bitwise_not(expr: _ColumnExpressionArgument[_T]) -> UnaryExpression[_T]:
1162 """Produce a unary bitwise NOT clause, typically via the ``~`` operator.
1164 Not to be confused with boolean negation :func:`_sql.not_`.
1166 .. versionadded:: 2.0.2
1168 .. seealso::
1170 :ref:`operators_bitwise`
1173 """
1175 return UnaryExpression._create_bitwise_not(expr)
1178def extract(field: str, expr: _ColumnExpressionArgument[Any]) -> Extract:
1179 """Return a :class:`.Extract` construct.
1181 This is typically available as :func:`.extract`
1182 as well as ``func.extract`` from the
1183 :data:`.func` namespace.
1185 :param field: The field to extract.
1187 .. warning:: This field is used as a literal SQL string.
1188 **DO NOT PASS UNTRUSTED INPUT TO THIS STRING**.
1190 :param expr: A column or Python scalar expression serving as the
1191 right side of the ``EXTRACT`` expression.
1193 E.g.::
1195 from sqlalchemy import extract
1196 from sqlalchemy import table, column
1198 logged_table = table(
1199 "user",
1200 column("id"),
1201 column("date_created"),
1202 )
1204 stmt = select(logged_table.c.id).where(
1205 extract("YEAR", logged_table.c.date_created) == 2021
1206 )
1208 In the above example, the statement is used to select ids from the
1209 database where the ``YEAR`` component matches a specific value.
1211 Similarly, one can also select an extracted component::
1213 stmt = select(extract("YEAR", logged_table.c.date_created)).where(
1214 logged_table.c.id == 1
1215 )
1217 The implementation of ``EXTRACT`` may vary across database backends.
1218 Users are reminded to consult their database documentation.
1219 """
1220 return Extract(field, expr)
1223def false() -> False_:
1224 """Return a :class:`.False_` construct.
1226 E.g.:
1228 .. sourcecode:: pycon+sql
1230 >>> from sqlalchemy import false
1231 >>> print(select(t.c.x).where(false()))
1232 {printsql}SELECT x FROM t WHERE false
1234 A backend which does not support true/false constants will render as
1235 an expression against 1 or 0:
1237 .. sourcecode:: pycon+sql
1239 >>> print(select(t.c.x).where(false()))
1240 {printsql}SELECT x FROM t WHERE 0 = 1
1242 The :func:`.true` and :func:`.false` constants also feature
1243 "short circuit" operation within an :func:`.and_` or :func:`.or_`
1244 conjunction:
1246 .. sourcecode:: pycon+sql
1248 >>> print(select(t.c.x).where(or_(t.c.x > 5, true())))
1249 {printsql}SELECT x FROM t WHERE true{stop}
1251 >>> print(select(t.c.x).where(and_(t.c.x > 5, false())))
1252 {printsql}SELECT x FROM t WHERE false{stop}
1254 .. seealso::
1256 :func:`.true`
1258 """
1260 return False_._instance()
1263def funcfilter(
1264 func: FunctionElement[_T], *criterion: _ColumnExpressionArgument[bool]
1265) -> FunctionFilter[_T]:
1266 """Produce a :class:`.FunctionFilter` object against a function.
1268 Used against aggregate and window functions,
1269 for database backends that support the "FILTER" clause.
1271 E.g.::
1273 from sqlalchemy import funcfilter
1275 funcfilter(func.count(1), MyClass.name == "some name")
1277 Would produce "COUNT(1) FILTER (WHERE myclass.name = 'some name')".
1279 This function is also available from the :data:`~.expression.func`
1280 construct itself via the :meth:`.FunctionElement.filter` method.
1282 .. seealso::
1284 :ref:`tutorial_functions_within_group` - in the
1285 :ref:`unified_tutorial`
1287 :meth:`.FunctionElement.filter`
1289 """
1290 return FunctionFilter(func, *criterion)
1293def label(
1294 name: str,
1295 element: _ColumnExpressionArgument[_T],
1296 type_: Optional[_TypeEngineArgument[_T]] = None,
1297) -> Label[_T]:
1298 """Return a :class:`Label` object for the
1299 given :class:`_expression.ColumnElement`.
1301 A label changes the name of an element in the columns clause of a
1302 ``SELECT`` statement, typically via the ``AS`` SQL keyword.
1304 This functionality is more conveniently available via the
1305 :meth:`_expression.ColumnElement.label` method on
1306 :class:`_expression.ColumnElement`.
1308 :param name: label name
1310 :param obj: a :class:`_expression.ColumnElement`.
1312 """
1313 return Label(name, element, type_)
1316def null() -> Null:
1317 """Return a constant :class:`.Null` construct."""
1319 return Null._instance()
1322def nulls_first(column: _ColumnExpressionArgument[_T]) -> UnaryExpression[_T]:
1323 """Produce the ``NULLS FIRST`` modifier for an ``ORDER BY`` expression.
1325 :func:`.nulls_first` is intended to modify the expression produced
1326 by :func:`.asc` or :func:`.desc`, and indicates how NULL values
1327 should be handled when they are encountered during ordering::
1330 from sqlalchemy import desc, nulls_first
1332 stmt = select(users_table).order_by(nulls_first(desc(users_table.c.name)))
1334 The SQL expression from the above would resemble:
1336 .. sourcecode:: sql
1338 SELECT id, name FROM user ORDER BY name DESC NULLS FIRST
1340 Like :func:`.asc` and :func:`.desc`, :func:`.nulls_first` is typically
1341 invoked from the column expression itself using
1342 :meth:`_expression.ColumnElement.nulls_first`,
1343 rather than as its standalone
1344 function version, as in::
1346 stmt = select(users_table).order_by(
1347 users_table.c.name.desc().nulls_first()
1348 )
1350 .. versionchanged:: 1.4 :func:`.nulls_first` is renamed from
1351 :func:`.nullsfirst` in previous releases.
1352 The previous name remains available for backwards compatibility.
1354 .. seealso::
1356 :func:`.asc`
1358 :func:`.desc`
1360 :func:`.nulls_last`
1362 :meth:`_expression.Select.order_by`
1364 """ # noqa: E501
1365 return UnaryExpression._create_nulls_first(column)
1368def nulls_last(column: _ColumnExpressionArgument[_T]) -> UnaryExpression[_T]:
1369 """Produce the ``NULLS LAST`` modifier for an ``ORDER BY`` expression.
1371 :func:`.nulls_last` is intended to modify the expression produced
1372 by :func:`.asc` or :func:`.desc`, and indicates how NULL values
1373 should be handled when they are encountered during ordering::
1376 from sqlalchemy import desc, nulls_last
1378 stmt = select(users_table).order_by(nulls_last(desc(users_table.c.name)))
1380 The SQL expression from the above would resemble:
1382 .. sourcecode:: sql
1384 SELECT id, name FROM user ORDER BY name DESC NULLS LAST
1386 Like :func:`.asc` and :func:`.desc`, :func:`.nulls_last` is typically
1387 invoked from the column expression itself using
1388 :meth:`_expression.ColumnElement.nulls_last`,
1389 rather than as its standalone
1390 function version, as in::
1392 stmt = select(users_table).order_by(users_table.c.name.desc().nulls_last())
1394 .. versionchanged:: 1.4 :func:`.nulls_last` is renamed from
1395 :func:`.nullslast` in previous releases.
1396 The previous name remains available for backwards compatibility.
1398 .. seealso::
1400 :func:`.asc`
1402 :func:`.desc`
1404 :func:`.nulls_first`
1406 :meth:`_expression.Select.order_by`
1408 """ # noqa: E501
1409 return UnaryExpression._create_nulls_last(column)
1412def or_( # type: ignore[empty-body]
1413 initial_clause: Union[Literal[False], _ColumnExpressionArgument[bool]],
1414 *clauses: _ColumnExpressionArgument[bool],
1415) -> ColumnElement[bool]:
1416 """Produce a conjunction of expressions joined by ``OR``.
1418 E.g.::
1420 from sqlalchemy import or_
1422 stmt = select(users_table).where(
1423 or_(users_table.c.name == "wendy", users_table.c.name == "jack")
1424 )
1426 The :func:`.or_` conjunction is also available using the
1427 Python ``|`` operator (though note that compound expressions
1428 need to be parenthesized in order to function with Python
1429 operator precedence behavior)::
1431 stmt = select(users_table).where(
1432 (users_table.c.name == "wendy") | (users_table.c.name == "jack")
1433 )
1435 The :func:`.or_` construct must be given at least one positional
1436 argument in order to be valid; a :func:`.or_` construct with no
1437 arguments is ambiguous. To produce an "empty" or dynamically
1438 generated :func:`.or_` expression, from a given list of expressions,
1439 a "default" element of :func:`_sql.false` (or just ``False``) should be
1440 specified::
1442 from sqlalchemy import false
1444 or_criteria = or_(false(), *expressions)
1446 The above expression will compile to SQL as the expression ``false``
1447 or ``0 = 1``, depending on backend, if no other expressions are
1448 present. If expressions are present, then the :func:`_sql.false` value is
1449 ignored as it does not affect the outcome of an OR expression which
1450 has other elements.
1452 .. deprecated:: 1.4 The :func:`.or_` element now requires that at
1453 least one argument is passed; creating the :func:`.or_` construct
1454 with no arguments is deprecated, and will emit a deprecation warning
1455 while continuing to produce a blank SQL string.
1457 .. seealso::
1459 :func:`.and_`
1461 """
1462 ...
1465if not TYPE_CHECKING:
1466 # handle deprecated case which allows zero-arguments
1467 def or_(*clauses): # noqa: F811
1468 """Produce a conjunction of expressions joined by ``OR``.
1470 E.g.::
1472 from sqlalchemy import or_
1474 stmt = select(users_table).where(
1475 or_(users_table.c.name == "wendy", users_table.c.name == "jack")
1476 )
1478 The :func:`.or_` conjunction is also available using the
1479 Python ``|`` operator (though note that compound expressions
1480 need to be parenthesized in order to function with Python
1481 operator precedence behavior)::
1483 stmt = select(users_table).where(
1484 (users_table.c.name == "wendy") | (users_table.c.name == "jack")
1485 )
1487 The :func:`.or_` construct must be given at least one positional
1488 argument in order to be valid; a :func:`.or_` construct with no
1489 arguments is ambiguous. To produce an "empty" or dynamically
1490 generated :func:`.or_` expression, from a given list of expressions,
1491 a "default" element of :func:`_sql.false` (or just ``False``) should be
1492 specified::
1494 from sqlalchemy import false
1496 or_criteria = or_(false(), *expressions)
1498 The above expression will compile to SQL as the expression ``false``
1499 or ``0 = 1``, depending on backend, if no other expressions are
1500 present. If expressions are present, then the :func:`_sql.false` value
1501 is ignored as it does not affect the outcome of an OR expression which
1502 has other elements.
1504 .. deprecated:: 1.4 The :func:`.or_` element now requires that at
1505 least one argument is passed; creating the :func:`.or_` construct
1506 with no arguments is deprecated, and will emit a deprecation warning
1507 while continuing to produce a blank SQL string.
1509 .. seealso::
1511 :func:`.and_`
1513 """ # noqa: E501
1514 return BooleanClauseList.or_(*clauses)
1517def over(
1518 element: FunctionElement[_T],
1519 partition_by: Optional[_ByArgument] = None,
1520 order_by: Optional[_ByArgument] = None,
1521 range_: Optional[typing_Tuple[Optional[int], Optional[int]]] = None,
1522 rows: Optional[typing_Tuple[Optional[int], Optional[int]]] = None,
1523 groups: Optional[typing_Tuple[Optional[int], Optional[int]]] = None,
1524) -> Over[_T]:
1525 r"""Produce an :class:`.Over` object against a function.
1527 Used against aggregate or so-called "window" functions,
1528 for database backends that support window functions.
1530 :func:`_expression.over` is usually called using
1531 the :meth:`.FunctionElement.over` method, e.g.::
1533 func.row_number().over(order_by=mytable.c.some_column)
1535 Would produce:
1537 .. sourcecode:: sql
1539 ROW_NUMBER() OVER(ORDER BY some_column)
1541 Ranges are also possible using the :paramref:`.expression.over.range_`,
1542 :paramref:`.expression.over.rows`, and :paramref:`.expression.over.groups`
1543 parameters. These
1544 mutually-exclusive parameters each accept a 2-tuple, which contains
1545 a combination of integers and None::
1547 func.row_number().over(order_by=my_table.c.some_column, range_=(None, 0))
1549 The above would produce:
1551 .. sourcecode:: sql
1553 ROW_NUMBER() OVER(ORDER BY some_column
1554 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
1556 A value of ``None`` indicates "unbounded", a
1557 value of zero indicates "current row", and negative / positive
1558 integers indicate "preceding" and "following":
1560 * RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING::
1562 func.row_number().over(order_by="x", range_=(-5, 10))
1564 * ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW::
1566 func.row_number().over(order_by="x", rows=(None, 0))
1568 * RANGE BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING::
1570 func.row_number().over(order_by="x", range_=(-2, None))
1572 * RANGE BETWEEN 1 FOLLOWING AND 3 FOLLOWING::
1574 func.row_number().over(order_by="x", range_=(1, 3))
1576 * GROUPS BETWEEN 1 FOLLOWING AND 3 FOLLOWING::
1578 func.row_number().over(order_by="x", groups=(1, 3))
1580 :param element: a :class:`.FunctionElement`, :class:`.WithinGroup`,
1581 or other compatible construct.
1582 :param partition_by: a column element or string, or a list
1583 of such, that will be used as the PARTITION BY clause
1584 of the OVER construct.
1585 :param order_by: a column element or string, or a list
1586 of such, that will be used as the ORDER BY clause
1587 of the OVER construct.
1588 :param range\_: optional range clause for the window. This is a
1589 tuple value which can contain integer values or ``None``,
1590 and will render a RANGE BETWEEN PRECEDING / FOLLOWING clause.
1591 :param rows: optional rows clause for the window. This is a tuple
1592 value which can contain integer values or None, and will render
1593 a ROWS BETWEEN PRECEDING / FOLLOWING clause.
1594 :param groups: optional groups clause for the window. This is a
1595 tuple value which can contain integer values or ``None``,
1596 and will render a GROUPS BETWEEN PRECEDING / FOLLOWING clause.
1598 .. versionadded:: 2.0.40
1600 This function is also available from the :data:`~.expression.func`
1601 construct itself via the :meth:`.FunctionElement.over` method.
1603 .. seealso::
1605 :ref:`tutorial_window_functions` - in the :ref:`unified_tutorial`
1607 :data:`.expression.func`
1609 :func:`_expression.within_group`
1611 """ # noqa: E501
1612 return Over(element, partition_by, order_by, range_, rows, groups)
1615@_document_text_coercion("text", ":func:`.text`", ":paramref:`.text.text`")
1616def text(text: str) -> TextClause:
1617 r"""Construct a new :class:`_expression.TextClause` clause,
1618 representing
1619 a textual SQL string directly.
1621 E.g.::
1623 from sqlalchemy import text
1625 t = text("SELECT * FROM users")
1626 result = connection.execute(t)
1628 The advantages :func:`_expression.text`
1629 provides over a plain string are
1630 backend-neutral support for bind parameters, per-statement
1631 execution options, as well as
1632 bind parameter and result-column typing behavior, allowing
1633 SQLAlchemy type constructs to play a role when executing
1634 a statement that is specified literally. The construct can also
1635 be provided with a ``.c`` collection of column elements, allowing
1636 it to be embedded in other SQL expression constructs as a subquery.
1638 Bind parameters are specified by name, using the format ``:name``.
1639 E.g.::
1641 t = text("SELECT * FROM users WHERE id=:user_id")
1642 result = connection.execute(t, {"user_id": 12})
1644 For SQL statements where a colon is required verbatim, as within
1645 an inline string, use a backslash to escape::
1647 t = text(r"SELECT * FROM users WHERE name='\:username'")
1649 The :class:`_expression.TextClause`
1650 construct includes methods which can
1651 provide information about the bound parameters as well as the column
1652 values which would be returned from the textual statement, assuming
1653 it's an executable SELECT type of statement. The
1654 :meth:`_expression.TextClause.bindparams`
1655 method is used to provide bound
1656 parameter detail, and :meth:`_expression.TextClause.columns`
1657 method allows
1658 specification of return columns including names and types::
1660 t = (
1661 text("SELECT * FROM users WHERE id=:user_id")
1662 .bindparams(user_id=7)
1663 .columns(id=Integer, name=String)
1664 )
1666 for id, name in connection.execute(t):
1667 print(id, name)
1669 The :func:`_expression.text` construct is used in cases when
1670 a literal string SQL fragment is specified as part of a larger query,
1671 such as for the WHERE clause of a SELECT statement::
1673 s = select(users.c.id, users.c.name).where(text("id=:user_id"))
1674 result = connection.execute(s, {"user_id": 12})
1676 :func:`_expression.text` is also used for the construction
1677 of a full, standalone statement using plain text.
1678 As such, SQLAlchemy refers
1679 to it as an :class:`.Executable` object and may be used
1680 like any other statement passed to an ``.execute()`` method.
1682 :param text:
1683 the text of the SQL statement to be created. Use ``:<param>``
1684 to specify bind parameters; they will be compiled to their
1685 engine-specific format.
1687 .. seealso::
1689 :ref:`tutorial_select_arbitrary_text`
1691 """
1692 return TextClause(text)
1695def true() -> True_:
1696 """Return a constant :class:`.True_` construct.
1698 E.g.:
1700 .. sourcecode:: pycon+sql
1702 >>> from sqlalchemy import true
1703 >>> print(select(t.c.x).where(true()))
1704 {printsql}SELECT x FROM t WHERE true
1706 A backend which does not support true/false constants will render as
1707 an expression against 1 or 0:
1709 .. sourcecode:: pycon+sql
1711 >>> print(select(t.c.x).where(true()))
1712 {printsql}SELECT x FROM t WHERE 1 = 1
1714 The :func:`.true` and :func:`.false` constants also feature
1715 "short circuit" operation within an :func:`.and_` or :func:`.or_`
1716 conjunction:
1718 .. sourcecode:: pycon+sql
1720 >>> print(select(t.c.x).where(or_(t.c.x > 5, true())))
1721 {printsql}SELECT x FROM t WHERE true{stop}
1723 >>> print(select(t.c.x).where(and_(t.c.x > 5, false())))
1724 {printsql}SELECT x FROM t WHERE false{stop}
1726 .. seealso::
1728 :func:`.false`
1730 """
1732 return True_._instance()
1735def tuple_(
1736 *clauses: _ColumnExpressionArgument[Any],
1737 types: Optional[Sequence[_TypeEngineArgument[Any]]] = None,
1738) -> Tuple:
1739 """Return a :class:`.Tuple`.
1741 Main usage is to produce a composite IN construct using
1742 :meth:`.ColumnOperators.in_` ::
1744 from sqlalchemy import tuple_
1746 tuple_(table.c.col1, table.c.col2).in_([(1, 2), (5, 12), (10, 19)])
1748 .. versionchanged:: 1.3.6 Added support for SQLite IN tuples.
1750 .. warning::
1752 The composite IN construct is not supported by all backends, and is
1753 currently known to work on PostgreSQL, MySQL, and SQLite.
1754 Unsupported backends will raise a subclass of
1755 :class:`~sqlalchemy.exc.DBAPIError` when such an expression is
1756 invoked.
1758 """
1759 return Tuple(*clauses, types=types)
1762def type_coerce(
1763 expression: _ColumnExpressionOrLiteralArgument[Any],
1764 type_: _TypeEngineArgument[_T],
1765) -> TypeCoerce[_T]:
1766 r"""Associate a SQL expression with a particular type, without rendering
1767 ``CAST``.
1769 E.g.::
1771 from sqlalchemy import type_coerce
1773 stmt = select(type_coerce(log_table.date_string, StringDateTime()))
1775 The above construct will produce a :class:`.TypeCoerce` object, which
1776 does not modify the rendering in any way on the SQL side, with the
1777 possible exception of a generated label if used in a columns clause
1778 context:
1780 .. sourcecode:: sql
1782 SELECT date_string AS date_string FROM log
1784 When result rows are fetched, the ``StringDateTime`` type processor
1785 will be applied to result rows on behalf of the ``date_string`` column.
1787 .. note:: the :func:`.type_coerce` construct does not render any
1788 SQL syntax of its own, including that it does not imply
1789 parenthesization. Please use :meth:`.TypeCoerce.self_group`
1790 if explicit parenthesization is required.
1792 In order to provide a named label for the expression, use
1793 :meth:`_expression.ColumnElement.label`::
1795 stmt = select(
1796 type_coerce(log_table.date_string, StringDateTime()).label("date")
1797 )
1799 A type that features bound-value handling will also have that behavior
1800 take effect when literal values or :func:`.bindparam` constructs are
1801 passed to :func:`.type_coerce` as targets.
1802 For example, if a type implements the
1803 :meth:`.TypeEngine.bind_expression`
1804 method or :meth:`.TypeEngine.bind_processor` method or equivalent,
1805 these functions will take effect at statement compilation/execution
1806 time when a literal value is passed, as in::
1808 # bound-value handling of MyStringType will be applied to the
1809 # literal value "some string"
1810 stmt = select(type_coerce("some string", MyStringType))
1812 When using :func:`.type_coerce` with composed expressions, note that
1813 **parenthesis are not applied**. If :func:`.type_coerce` is being
1814 used in an operator context where the parenthesis normally present from
1815 CAST are necessary, use the :meth:`.TypeCoerce.self_group` method:
1817 .. sourcecode:: pycon+sql
1819 >>> some_integer = column("someint", Integer)
1820 >>> some_string = column("somestr", String)
1821 >>> expr = type_coerce(some_integer + 5, String) + some_string
1822 >>> print(expr)
1823 {printsql}someint + :someint_1 || somestr{stop}
1824 >>> expr = type_coerce(some_integer + 5, String).self_group() + some_string
1825 >>> print(expr)
1826 {printsql}(someint + :someint_1) || somestr{stop}
1828 :param expression: A SQL expression, such as a
1829 :class:`_expression.ColumnElement`
1830 expression or a Python string which will be coerced into a bound
1831 literal value.
1833 :param type\_: A :class:`.TypeEngine` class or instance indicating
1834 the type to which the expression is coerced.
1836 .. seealso::
1838 :ref:`tutorial_casts`
1840 :func:`.cast`
1842 """ # noqa
1843 return TypeCoerce(expression, type_)
1846def within_group(
1847 element: FunctionElement[_T], *order_by: _ColumnExpressionArgument[Any]
1848) -> WithinGroup[_T]:
1849 r"""Produce a :class:`.WithinGroup` object against a function.
1851 Used against so-called "ordered set aggregate" and "hypothetical
1852 set aggregate" functions, including :class:`.percentile_cont`,
1853 :class:`.rank`, :class:`.dense_rank`, etc.
1855 :func:`_expression.within_group` is usually called using
1856 the :meth:`.FunctionElement.within_group` method, e.g.::
1858 from sqlalchemy import within_group
1860 stmt = select(
1861 department.c.id,
1862 func.percentile_cont(0.5).within_group(department.c.salary.desc()),
1863 )
1865 The above statement would produce SQL similar to
1866 ``SELECT department.id, percentile_cont(0.5)
1867 WITHIN GROUP (ORDER BY department.salary DESC)``.
1869 :param element: a :class:`.FunctionElement` construct, typically
1870 generated by :data:`~.expression.func`.
1871 :param \*order_by: one or more column elements that will be used
1872 as the ORDER BY clause of the WITHIN GROUP construct.
1874 .. seealso::
1876 :ref:`tutorial_functions_within_group` - in the
1877 :ref:`unified_tutorial`
1879 :data:`.expression.func`
1881 :func:`_expression.over`
1883 """
1884 return WithinGroup(element, *order_by)