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 Literal
14from typing import Mapping
15from typing import Optional
16from typing import overload
17from typing import Sequence
18from typing import Tuple as typing_Tuple
19from typing import TYPE_CHECKING
20from typing import TypeVar
21from typing import Union
23from . import coercions
24from . import operators
25from . import roles
26from .base import _NoArg
27from .coercions import _document_text_coercion
28from .elements import AggregateOrderBy
29from .elements import BindParameter
30from .elements import BooleanClauseList
31from .elements import Case
32from .elements import Cast
33from .elements import CollationClause
34from .elements import CollectionAggregate
35from .elements import ColumnClause
36from .elements import ColumnElement
37from .elements import DMLTargetCopy
38from .elements import Extract
39from .elements import False_
40from .elements import FunctionFilter
41from .elements import Label
42from .elements import Null
43from .elements import OrderByList
44from .elements import Over
45from .elements import TextClause
46from .elements import True_
47from .elements import TryCast
48from .elements import TString
49from .elements import Tuple
50from .elements import TypeCoerce
51from .elements import UnaryExpression
52from .elements import WithinGroup
53from .functions import FunctionElement
55if typing.TYPE_CHECKING:
56 from string.templatelib import Template
58 from ._typing import _ByArgument
59 from ._typing import _ColumnExpressionArgument
60 from ._typing import _ColumnExpressionOrLiteralArgument
61 from ._typing import _ColumnExpressionOrStrLabelArgument
62 from ._typing import _OnlyColumnArgument
63 from ._typing import _TypeEngineArgument
64 from .elements import _FrameIntTuple
65 from .elements import BinaryExpression
66 from .elements import FrameClause
67 from .selectable import FromClause
68 from .type_api import TypeEngine
70_T = TypeVar("_T")
73def all_(expr: _ColumnExpressionArgument[_T]) -> CollectionAggregate[bool]:
74 """Produce an ALL expression.
76 For dialects such as that of PostgreSQL, this operator applies
77 to usage of the :class:`_types.ARRAY` datatype, for that of
78 MySQL, it may apply to a subquery. e.g.::
80 # renders on PostgreSQL:
81 # '5 = ALL (somearray)'
82 expr = 5 == all_(mytable.c.somearray)
84 # renders on MySQL:
85 # '5 = ALL (SELECT value FROM table)'
86 expr = 5 == all_(select(table.c.value))
88 Comparison to NULL may work using ``None``::
90 None == all_(mytable.c.somearray)
92 The any_() / all_() operators also feature a special "operand flipping"
93 behavior such that if any_() / all_() are used on the left side of a
94 comparison using a standalone operator such as ``==``, ``!=``, etc.
95 (not including operator methods such as
96 :meth:`_sql.ColumnOperators.is_`) the rendered expression is flipped::
98 # would render '5 = ALL (column)`
99 all_(mytable.c.column) == 5
101 Or with ``None``, which note will not perform
102 the usual step of rendering "IS" as is normally the case for NULL::
104 # would render 'NULL = ALL(somearray)'
105 all_(mytable.c.somearray) == None
107 The column-level :meth:`_sql.ColumnElement.all_` method (not to be
108 confused with the deprecated :class:`_types.ARRAY` level
109 :meth:`_types.ARRAY.Comparator.all`) is shorthand for
110 ``all_(col)``::
112 5 == mytable.c.somearray.all_()
114 .. seealso::
116 :meth:`_sql.ColumnOperators.all_`
118 :func:`_expression.any_`
120 """
121 if isinstance(expr, operators.ColumnOperators):
122 return expr.all_()
123 else:
124 return CollectionAggregate._create_all(expr)
127def and_( # type: ignore[empty-body]
128 initial_clause: Union[Literal[True], _ColumnExpressionArgument[bool]],
129 *clauses: _ColumnExpressionArgument[bool],
130) -> ColumnElement[bool]:
131 r"""Produce a conjunction of expressions joined by ``AND``.
133 E.g.::
135 from sqlalchemy import and_
137 stmt = select(users_table).where(
138 and_(users_table.c.name == "wendy", users_table.c.enrolled == True)
139 )
141 The :func:`.and_` conjunction is also available using the
142 Python ``&`` operator (though note that compound expressions
143 need to be parenthesized in order to function with Python
144 operator precedence behavior)::
146 stmt = select(users_table).where(
147 (users_table.c.name == "wendy") & (users_table.c.enrolled == True)
148 )
150 The :func:`.and_` operation is also implicit in some cases;
151 the :meth:`_expression.Select.where`
152 method for example can be invoked multiple
153 times against a statement, which will have the effect of each
154 clause being combined using :func:`.and_`::
156 stmt = (
157 select(users_table)
158 .where(users_table.c.name == "wendy")
159 .where(users_table.c.enrolled == True)
160 )
162 The :func:`.and_` construct must be given at least one positional
163 argument in order to be valid; a :func:`.and_` construct with no
164 arguments is ambiguous. To produce an "empty" or dynamically
165 generated :func:`.and_` expression, from a given list of expressions,
166 a "default" element of :func:`_sql.true` (or just ``True``) should be
167 specified::
169 from sqlalchemy import true
171 criteria = and_(true(), *expressions)
173 The above expression will compile to SQL as the expression ``true``
174 or ``1 = 1``, depending on backend, if no other expressions are
175 present. If expressions are present, then the :func:`_sql.true` value is
176 ignored as it does not affect the outcome of an AND expression that
177 has other elements.
179 .. deprecated:: 1.4 The :func:`.and_` element now requires that at
180 least one argument is passed; creating the :func:`.and_` construct
181 with no arguments is deprecated, and will emit a deprecation warning
182 while continuing to produce a blank SQL string.
184 .. seealso::
186 :func:`.or_`
188 """
189 ...
192if not TYPE_CHECKING:
193 # handle deprecated case which allows zero-arguments
194 def and_(*clauses): # noqa: F811
195 r"""Produce a conjunction of expressions joined by ``AND``.
197 E.g.::
199 from sqlalchemy import and_
201 stmt = select(users_table).where(
202 and_(users_table.c.name == "wendy", users_table.c.enrolled == True)
203 )
205 The :func:`.and_` conjunction is also available using the
206 Python ``&`` operator (though note that compound expressions
207 need to be parenthesized in order to function with Python
208 operator precedence behavior)::
210 stmt = select(users_table).where(
211 (users_table.c.name == "wendy") & (users_table.c.enrolled == True)
212 )
214 The :func:`.and_` operation is also implicit in some cases;
215 the :meth:`_expression.Select.where`
216 method for example can be invoked multiple
217 times against a statement, which will have the effect of each
218 clause being combined using :func:`.and_`::
220 stmt = (
221 select(users_table)
222 .where(users_table.c.name == "wendy")
223 .where(users_table.c.enrolled == True)
224 )
226 The :func:`.and_` construct must be given at least one positional
227 argument in order to be valid; a :func:`.and_` construct with no
228 arguments is ambiguous. To produce an "empty" or dynamically
229 generated :func:`.and_` expression, from a given list of expressions,
230 a "default" element of :func:`_sql.true` (or just ``True``) should be
231 specified::
233 from sqlalchemy import true
235 criteria = and_(true(), *expressions)
237 The above expression will compile to SQL as the expression ``true``
238 or ``1 = 1``, depending on backend, if no other expressions are
239 present. If expressions are present, then the :func:`_sql.true` value
240 is ignored as it does not affect the outcome of an AND expression that
241 has other elements.
243 .. deprecated:: 1.4 The :func:`.and_` element now requires that at
244 least one argument is passed; creating the :func:`.and_` construct
245 with no arguments is deprecated, and will emit a deprecation warning
246 while continuing to produce a blank SQL string.
248 .. seealso::
250 :func:`.or_`
252 """ # noqa: E501
253 return BooleanClauseList.and_(*clauses)
256def any_(expr: _ColumnExpressionArgument[_T]) -> CollectionAggregate[bool]:
257 """Produce an ANY expression.
259 For dialects such as that of PostgreSQL, this operator applies
260 to usage of the :class:`_types.ARRAY` datatype, for that of
261 MySQL, it may apply to a subquery. e.g.::
263 # renders on PostgreSQL:
264 # '5 = ANY (somearray)'
265 expr = 5 == any_(mytable.c.somearray)
267 # renders on MySQL:
268 # '5 = ANY (SELECT value FROM table)'
269 expr = 5 == any_(select(table.c.value))
271 Comparison to NULL may work using ``None`` or :func:`_sql.null`::
273 None == any_(mytable.c.somearray)
275 The any_() / all_() operators also feature a special "operand flipping"
276 behavior such that if any_() / all_() are used on the left side of a
277 comparison using a standalone operator such as ``==``, ``!=``, etc.
278 (not including operator methods such as
279 :meth:`_sql.ColumnOperators.is_`) the rendered expression is flipped::
281 # would render '5 = ANY (column)`
282 any_(mytable.c.column) == 5
284 Or with ``None``, which note will not perform
285 the usual step of rendering "IS" as is normally the case for NULL::
287 # would render 'NULL = ANY(somearray)'
288 any_(mytable.c.somearray) == None
290 The column-level :meth:`_sql.ColumnElement.any_` method (not to be
291 confused with the deprecated :class:`_types.ARRAY` level
292 :meth:`_types.ARRAY.Comparator.any`) is shorthand for
293 ``any_(col)``::
295 5 = mytable.c.somearray.any_()
297 .. seealso::
299 :meth:`_sql.ColumnOperators.any_`
301 :func:`_expression.all_`
303 """
304 if isinstance(expr, operators.ColumnOperators):
305 return expr.any_()
306 else:
307 return CollectionAggregate._create_any(expr)
310@overload
311def asc(
312 column: Union[str, "ColumnElement[_T]"],
313) -> UnaryExpression[_T]: ...
316@overload
317def asc(
318 column: _ColumnExpressionOrStrLabelArgument[_T],
319) -> Union[OrderByList, UnaryExpression[_T]]: ...
322def asc(
323 column: _ColumnExpressionOrStrLabelArgument[_T],
324) -> Union[OrderByList, UnaryExpression[_T]]:
325 """Produce an ascending ``ORDER BY`` clause element.
327 e.g.::
329 from sqlalchemy import asc
331 stmt = select(users_table).order_by(asc(users_table.c.name))
333 will produce SQL as:
335 .. sourcecode:: sql
337 SELECT id, name FROM user ORDER BY name ASC
339 The :func:`.asc` function is a standalone version of the
340 :meth:`_expression.ColumnElement.asc`
341 method available on all SQL expressions,
342 e.g.::
345 stmt = select(users_table).order_by(users_table.c.name.asc())
347 :param column: A :class:`_expression.ColumnElement` (e.g.
348 scalar SQL expression)
349 with which to apply the :func:`.asc` operation.
351 .. seealso::
353 :func:`.desc`
355 :func:`.nulls_first`
357 :func:`.nulls_last`
359 :meth:`_expression.Select.order_by`
361 """
363 if isinstance(column, operators.OrderingOperators):
364 return column.asc() # type: ignore[unused-ignore]
365 else:
366 return UnaryExpression._create_asc(column)
369def collate(
370 expression: _ColumnExpressionArgument[str], collation: str
371) -> BinaryExpression[str]:
372 """Return the clause ``expression COLLATE collation``.
374 e.g.::
376 collate(mycolumn, "utf8_bin")
378 produces:
380 .. sourcecode:: sql
382 mycolumn COLLATE utf8_bin
384 The collation expression is also quoted if it is a case sensitive
385 identifier, e.g. contains uppercase characters.
387 """
388 if isinstance(expression, operators.ColumnOperators):
389 return expression.collate(collation) # type: ignore
390 else:
391 return CollationClause._create_collation_expression(
392 expression, collation
393 )
396def between(
397 expr: _ColumnExpressionOrLiteralArgument[_T],
398 lower_bound: Any,
399 upper_bound: Any,
400 symmetric: bool = False,
401) -> BinaryExpression[bool]:
402 """Produce a ``BETWEEN`` predicate clause.
404 E.g.::
406 from sqlalchemy import between
408 stmt = select(users_table).where(between(users_table.c.id, 5, 7))
410 Would produce SQL resembling:
412 .. sourcecode:: sql
414 SELECT id, name FROM user WHERE id BETWEEN :id_1 AND :id_2
416 The :func:`.between` function is a standalone version of the
417 :meth:`_expression.ColumnElement.between` method available on all
418 SQL expressions, as in::
420 stmt = select(users_table).where(users_table.c.id.between(5, 7))
422 All arguments passed to :func:`.between`, including the left side
423 column expression, are coerced from Python scalar values if a
424 the value is not a :class:`_expression.ColumnElement` subclass.
425 For example,
426 three fixed values can be compared as in::
428 print(between(5, 3, 7))
430 Which would produce::
432 :param_1 BETWEEN :param_2 AND :param_3
434 :param expr: a column expression, typically a
435 :class:`_expression.ColumnElement`
436 instance or alternatively a Python scalar expression to be coerced
437 into a column expression, serving as the left side of the ``BETWEEN``
438 expression.
440 :param lower_bound: a column or Python scalar expression serving as the
441 lower bound of the right side of the ``BETWEEN`` expression.
443 :param upper_bound: a column or Python scalar expression serving as the
444 upper bound of the right side of the ``BETWEEN`` expression.
446 :param symmetric: if True, will render " BETWEEN SYMMETRIC ". Note
447 that not all databases support this syntax.
449 .. seealso::
451 :meth:`_expression.ColumnElement.between`
453 """
454 col_expr = coercions.expect(roles.ExpressionElementRole, expr)
455 return col_expr.between(lower_bound, upper_bound, symmetric=symmetric)
458def outparam(
459 key: str, type_: Optional[TypeEngine[_T]] = None
460) -> BindParameter[_T]:
461 """Create an 'OUT' parameter for usage in functions (stored procedures),
462 for databases which support them.
464 The ``outparam`` can be used like a regular function parameter.
465 The "output" value will be available from the
466 :class:`~sqlalchemy.engine.CursorResult` object via its ``out_parameters``
467 attribute, which returns a dictionary containing the values.
469 """
470 return BindParameter(key, None, type_=type_, unique=False, isoutparam=True)
473@overload
474def not_(clause: BinaryExpression[_T]) -> BinaryExpression[_T]: ...
477@overload
478def not_(clause: _ColumnExpressionArgument[_T]) -> ColumnElement[_T]: ...
481def not_(clause: _ColumnExpressionArgument[_T]) -> ColumnElement[_T]:
482 """Return a negation of the given clause, i.e. ``NOT(clause)``.
484 The ``~`` operator is also overloaded on all
485 :class:`_expression.ColumnElement` subclasses to produce the
486 same result.
488 """
490 return coercions.expect(roles.ExpressionElementRole, clause).__invert__()
493def from_dml_column(column: _OnlyColumnArgument[_T]) -> DMLTargetCopy[_T]:
494 r"""A placeholder that may be used in compiled INSERT or UPDATE expressions
495 to refer to the SQL expression or value being applied to another column.
497 Given a table such as::
499 t = Table(
500 "t",
501 MetaData(),
502 Column("x", Integer),
503 Column("y", Integer),
504 )
506 The :func:`_sql.from_dml_column` construct allows automatic copying
507 of an expression assigned to a different column to be re-used::
509 >>> stmt = t.insert().values(x=func.foobar(3), y=from_dml_column(t.c.x) + 5)
510 >>> print(stmt)
511 INSERT INTO t (x, y) VALUES (foobar(:foobar_1), (foobar(:foobar_1) + :param_1))
513 The :func:`_sql.from_dml_column` construct is intended to be useful primarily
514 with event-based hooks such as those used by ORM hybrids.
516 .. seealso::
518 :ref:`hybrid_bulk_update`
520 .. versionadded:: 2.1
523 """ # noqa: E501
525 return DMLTargetCopy(column)
528def bindparam(
529 key: Optional[str],
530 value: Any = _NoArg.NO_ARG,
531 type_: Optional[_TypeEngineArgument[_T]] = None,
532 unique: bool = False,
533 required: Union[bool, Literal[_NoArg.NO_ARG]] = _NoArg.NO_ARG,
534 quote: Optional[bool] = None,
535 callable_: Optional[Callable[[], Any]] = None,
536 expanding: bool = False,
537 isoutparam: bool = False,
538 literal_execute: bool = False,
539) -> BindParameter[_T]:
540 r"""Produce a "bound expression".
542 The return value is an instance of :class:`.BindParameter`; this
543 is a :class:`_expression.ColumnElement`
544 subclass which represents a so-called
545 "placeholder" value in a SQL expression, the value of which is
546 supplied at the point at which the statement in executed against a
547 database connection.
549 In SQLAlchemy, the :func:`.bindparam` construct has
550 the ability to carry along the actual value that will be ultimately
551 used at expression time. In this way, it serves not just as
552 a "placeholder" for eventual population, but also as a means of
553 representing so-called "unsafe" values which should not be rendered
554 directly in a SQL statement, but rather should be passed along
555 to the :term:`DBAPI` as values which need to be correctly escaped
556 and potentially handled for type-safety.
558 When using :func:`.bindparam` explicitly, the use case is typically
559 one of traditional deferment of parameters; the :func:`.bindparam`
560 construct accepts a name which can then be referred to at execution
561 time::
563 from sqlalchemy import bindparam
565 stmt = select(users_table).where(
566 users_table.c.name == bindparam("username")
567 )
569 The above statement, when rendered, will produce SQL similar to:
571 .. sourcecode:: sql
573 SELECT id, name FROM user WHERE name = :username
575 In order to populate the value of ``:username`` above, the value
576 would typically be applied at execution time to a method
577 like :meth:`_engine.Connection.execute`::
579 result = connection.execute(stmt, {"username": "wendy"})
581 Explicit use of :func:`.bindparam` is also common when producing
582 UPDATE or DELETE statements that are to be invoked multiple times,
583 where the WHERE criterion of the statement is to change on each
584 invocation, such as::
586 stmt = (
587 users_table.update()
588 .where(user_table.c.name == bindparam("username"))
589 .values(fullname=bindparam("fullname"))
590 )
592 connection.execute(
593 stmt,
594 [
595 {"username": "wendy", "fullname": "Wendy Smith"},
596 {"username": "jack", "fullname": "Jack Jones"},
597 ],
598 )
600 SQLAlchemy's Core expression system makes wide use of
601 :func:`.bindparam` in an implicit sense. It is typical that Python
602 literal values passed to virtually all SQL expression functions are
603 coerced into fixed :func:`.bindparam` constructs. For example, given
604 a comparison operation such as::
606 expr = users_table.c.name == "Wendy"
608 The above expression will produce a :class:`.BinaryExpression`
609 construct, where the left side is the :class:`_schema.Column` object
610 representing the ``name`` column, and the right side is a
611 :class:`.BindParameter` representing the literal value::
613 print(repr(expr.right))
614 BindParameter("%(4327771088 name)s", "Wendy", type_=String())
616 The expression above will render SQL such as:
618 .. sourcecode:: sql
620 user.name = :name_1
622 Where the ``:name_1`` parameter name is an anonymous name. The
623 actual string ``Wendy`` is not in the rendered string, but is carried
624 along where it is later used within statement execution. If we
625 invoke a statement like the following::
627 stmt = select(users_table).where(users_table.c.name == "Wendy")
628 result = connection.execute(stmt)
630 We would see SQL logging output as:
632 .. sourcecode:: sql
634 SELECT "user".id, "user".name
635 FROM "user"
636 WHERE "user".name = %(name_1)s
637 {'name_1': 'Wendy'}
639 Above, we see that ``Wendy`` is passed as a parameter to the database,
640 while the placeholder ``:name_1`` is rendered in the appropriate form
641 for the target database, in this case the PostgreSQL database.
643 Similarly, :func:`.bindparam` is invoked automatically when working
644 with :term:`CRUD` statements as far as the "VALUES" portion is
645 concerned. The :func:`_expression.insert` construct produces an
646 ``INSERT`` expression which will, at statement execution time, generate
647 bound placeholders based on the arguments passed, as in::
649 stmt = users_table.insert()
650 result = connection.execute(stmt, {"name": "Wendy"})
652 The above will produce SQL output as:
654 .. sourcecode:: sql
656 INSERT INTO "user" (name) VALUES (%(name)s)
657 {'name': 'Wendy'}
659 The :class:`_expression.Insert` construct, at
660 compilation/execution time, rendered a single :func:`.bindparam`
661 mirroring the column name ``name`` as a result of the single ``name``
662 parameter we passed to the :meth:`_engine.Connection.execute` method.
664 :param key:
665 the key (e.g. the name) for this bind param.
666 Will be used in the generated
667 SQL statement for dialects that use named parameters. This
668 value may be modified when part of a compilation operation,
669 if other :class:`BindParameter` objects exist with the same
670 key, or if its length is too long and truncation is
671 required.
673 If omitted, an "anonymous" name is generated for the bound parameter;
674 when given a value to bind, the end result is equivalent to calling upon
675 the :func:`.literal` function with a value to bind, particularly
676 if the :paramref:`.bindparam.unique` parameter is also provided.
678 :param value:
679 Initial value for this bind param. Will be used at statement
680 execution time as the value for this parameter passed to the
681 DBAPI, if no other value is indicated to the statement execution
682 method for this particular parameter name. Defaults to ``None``.
684 :param callable\_:
685 A callable function that takes the place of "value". The function
686 will be called at statement execution time to determine the
687 ultimate value. Used for scenarios where the actual bind
688 value cannot be determined at the point at which the clause
689 construct is created, but embedded bind values are still desirable.
691 :param type\_:
692 A :class:`.TypeEngine` class or instance representing an optional
693 datatype for this :func:`.bindparam`. If not passed, a type
694 may be determined automatically for the bind, based on the given
695 value; for example, trivial Python types such as ``str``,
696 ``int``, ``bool``
697 may result in the :class:`.String`, :class:`.Integer` or
698 :class:`.Boolean` types being automatically selected.
700 The type of a :func:`.bindparam` is significant especially in that
701 the type will apply pre-processing to the value before it is
702 passed to the database. For example, a :func:`.bindparam` which
703 refers to a datetime value, and is specified as holding the
704 :class:`.DateTime` type, may apply conversion needed to the
705 value (such as stringification on SQLite) before passing the value
706 to the database.
708 :param unique:
709 if True, the key name of this :class:`.BindParameter` will be
710 modified if another :class:`.BindParameter` of the same name
711 already has been located within the containing
712 expression. This flag is used generally by the internals
713 when producing so-called "anonymous" bound expressions, it
714 isn't generally applicable to explicitly-named :func:`.bindparam`
715 constructs.
717 :param required:
718 If ``True``, a value is required at execution time. If not passed,
719 it defaults to ``True`` if neither :paramref:`.bindparam.value`
720 or :paramref:`.bindparam.callable` were passed. If either of these
721 parameters are present, then :paramref:`.bindparam.required`
722 defaults to ``False``.
724 :param quote:
725 True if this parameter name requires quoting and is not
726 currently known as a SQLAlchemy reserved word; this currently
727 only applies to the Oracle Database backends, where bound names must
728 sometimes be quoted.
730 :param isoutparam:
731 if True, the parameter should be treated like a stored procedure
732 "OUT" parameter. This applies to backends such as Oracle Database which
733 support OUT parameters.
735 :param expanding:
736 if True, this parameter will be treated as an "expanding" parameter
737 at execution time; the parameter value is expected to be a sequence,
738 rather than a scalar value, and the string SQL statement will
739 be transformed on a per-execution basis to accommodate the sequence
740 with a variable number of parameter slots passed to the DBAPI.
741 This is to allow statement caching to be used in conjunction with
742 an IN clause.
744 .. seealso::
746 :meth:`.ColumnOperators.in_`
748 :ref:`baked_in` - with baked queries
750 .. note:: The "expanding" feature does not support "executemany"-
751 style parameter sets.
753 :param literal_execute:
754 if True, the bound parameter will be rendered in the compile phase
755 with a special "POSTCOMPILE" token, and the SQLAlchemy compiler will
756 render the final value of the parameter into the SQL statement at
757 statement execution time, omitting the value from the parameter
758 dictionary / list passed to DBAPI ``cursor.execute()``. This
759 produces a similar effect as that of using the ``literal_binds``,
760 compilation flag, however takes place as the statement is sent to
761 the DBAPI ``cursor.execute()`` method, rather than when the statement
762 is compiled. The primary use of this
763 capability is for rendering LIMIT / OFFSET clauses for database
764 drivers that can't accommodate for bound parameters in these
765 contexts, while allowing SQL constructs to be cacheable at the
766 compilation level.
768 .. versionadded:: 1.4 Added "post compile" bound parameters
770 .. seealso::
772 :ref:`change_4808`.
774 .. seealso::
776 :ref:`tutorial_sending_parameters` - in the
777 :ref:`unified_tutorial`
780 """
781 return BindParameter(
782 key,
783 value,
784 type_,
785 unique,
786 required,
787 quote,
788 callable_,
789 expanding,
790 isoutparam,
791 literal_execute,
792 )
795def case(
796 *whens: Union[
797 typing_Tuple[_ColumnExpressionArgument[bool], Any], Mapping[Any, Any]
798 ],
799 value: Optional[Any] = None,
800 else_: Optional[Any] = None,
801) -> Case[Any]:
802 r"""Produce a ``CASE`` expression.
804 The ``CASE`` construct in SQL is a conditional object that
805 acts somewhat analogously to an "if/then" construct in other
806 languages. It returns an instance of :class:`.Case`.
808 :func:`.case` in its usual form is passed a series of "when"
809 constructs, that is, a list of conditions and results as tuples::
811 from sqlalchemy import case
813 stmt = select(users_table).where(
814 case(
815 (users_table.c.name == "wendy", "W"),
816 (users_table.c.name == "jack", "J"),
817 else_="E",
818 )
819 )
821 The above statement will produce SQL resembling:
823 .. sourcecode:: sql
825 SELECT id, name FROM user
826 WHERE CASE
827 WHEN (name = :name_1) THEN :param_1
828 WHEN (name = :name_2) THEN :param_2
829 ELSE :param_3
830 END
832 When simple equality expressions of several values against a single
833 parent column are needed, :func:`.case` also has a "shorthand" format
834 used via the
835 :paramref:`.case.value` parameter, which is passed a column
836 expression to be compared. In this form, the :paramref:`.case.whens`
837 parameter is passed as a dictionary containing expressions to be
838 compared against keyed to result expressions. The statement below is
839 equivalent to the preceding statement::
841 stmt = select(users_table).where(
842 case({"wendy": "W", "jack": "J"}, value=users_table.c.name, else_="E")
843 )
845 The values which are accepted as result values in
846 :paramref:`.case.whens` as well as with :paramref:`.case.else_` are
847 coerced from Python literals into :func:`.bindparam` constructs.
848 SQL expressions, e.g. :class:`_expression.ColumnElement` constructs,
849 are accepted
850 as well. To coerce a literal string expression into a constant
851 expression rendered inline, use the :func:`_expression.literal_column`
852 construct,
853 as in::
855 from sqlalchemy import case, literal_column
857 case(
858 (orderline.c.qty > 100, literal_column("'greaterthan100'")),
859 (orderline.c.qty > 10, literal_column("'greaterthan10'")),
860 else_=literal_column("'lessthan10'"),
861 )
863 The above will render the given constants without using bound
864 parameters for the result values (but still for the comparison
865 values), as in:
867 .. sourcecode:: sql
869 CASE
870 WHEN (orderline.qty > :qty_1) THEN 'greaterthan100'
871 WHEN (orderline.qty > :qty_2) THEN 'greaterthan10'
872 ELSE 'lessthan10'
873 END
875 :param \*whens: The criteria to be compared against,
876 :paramref:`.case.whens` accepts two different forms, based on
877 whether or not :paramref:`.case.value` is used.
879 .. versionchanged:: 1.4 the :func:`_sql.case`
880 function now accepts the series of WHEN conditions positionally
882 In the first form, it accepts multiple 2-tuples passed as positional
883 arguments; each 2-tuple consists of ``(<sql expression>, <value>)``,
884 where the SQL expression is a boolean expression and "value" is a
885 resulting value, e.g.::
887 case(
888 (users_table.c.name == "wendy", "W"),
889 (users_table.c.name == "jack", "J"),
890 )
892 In the second form, it accepts a Python dictionary of comparison
893 values mapped to a resulting value; this form requires
894 :paramref:`.case.value` to be present, and values will be compared
895 using the ``==`` operator, e.g.::
897 case({"wendy": "W", "jack": "J"}, value=users_table.c.name)
899 :param value: An optional SQL expression which will be used as a
900 fixed "comparison point" for candidate values within a dictionary
901 passed to :paramref:`.case.whens`.
903 :param else\_: An optional SQL expression which will be the evaluated
904 result of the ``CASE`` construct if all expressions within
905 :paramref:`.case.whens` evaluate to false. When omitted, most
906 databases will produce a result of NULL if none of the "when"
907 expressions evaluate to true.
910 """ # noqa: E501
911 return Case(*whens, value=value, else_=else_)
914def cast(
915 expression: _ColumnExpressionOrLiteralArgument[Any],
916 type_: _TypeEngineArgument[_T],
917) -> Cast[_T]:
918 r"""Produce a ``CAST`` expression.
920 :func:`.cast` returns an instance of :class:`.Cast`.
922 E.g.::
924 from sqlalchemy import cast, Numeric
926 stmt = select(cast(product_table.c.unit_price, Numeric(10, 4)))
928 The above statement will produce SQL resembling:
930 .. sourcecode:: sql
932 SELECT CAST(unit_price AS NUMERIC(10, 4)) FROM product
934 The :func:`.cast` function performs two distinct functions when
935 used. The first is that it renders the ``CAST`` expression within
936 the resulting SQL string. The second is that it associates the given
937 type (e.g. :class:`.TypeEngine` class or instance) with the column
938 expression on the Python side, which means the expression will take
939 on the expression operator behavior associated with that type,
940 as well as the bound-value handling and result-row-handling behavior
941 of the type.
943 An alternative to :func:`.cast` is the :func:`.type_coerce` function.
944 This function performs the second task of associating an expression
945 with a specific type, but does not render the ``CAST`` expression
946 in SQL.
948 :param expression: A SQL expression, such as a
949 :class:`_expression.ColumnElement`
950 expression or a Python string which will be coerced into a bound
951 literal value.
953 :param type\_: A :class:`.TypeEngine` class or instance indicating
954 the type to which the ``CAST`` should apply.
956 .. seealso::
958 :ref:`tutorial_casts`
960 :func:`.try_cast` - an alternative to CAST that results in
961 NULLs when the cast fails, instead of raising an error.
962 Only supported by some dialects.
964 :func:`.type_coerce` - an alternative to CAST that coerces the type
965 on the Python side only, which is often sufficient to generate the
966 correct SQL and data coercion.
969 """
970 return Cast(expression, type_)
973def try_cast(
974 expression: _ColumnExpressionOrLiteralArgument[Any],
975 type_: _TypeEngineArgument[_T],
976) -> TryCast[_T]:
977 """Produce a ``TRY_CAST`` expression for backends which support it;
978 this is a ``CAST`` which returns NULL for un-castable conversions.
980 In SQLAlchemy, this construct is supported **only** by the SQL Server
981 dialect, and will raise a :class:`.CompileError` if used on other
982 included backends. However, third party backends may also support
983 this construct.
985 .. tip:: As :func:`_sql.try_cast` originates from the SQL Server dialect,
986 it's importable both from ``sqlalchemy.`` as well as from
987 ``sqlalchemy.dialects.mssql``.
989 :func:`_sql.try_cast` returns an instance of :class:`.TryCast` and
990 generally behaves similarly to the :class:`.Cast` construct;
991 at the SQL level, the difference between ``CAST`` and ``TRY_CAST``
992 is that ``TRY_CAST`` returns NULL for an un-castable expression,
993 such as attempting to cast a string ``"hi"`` to an integer value.
995 E.g.::
997 from sqlalchemy import select, try_cast, Numeric
999 stmt = select(try_cast(product_table.c.unit_price, Numeric(10, 4)))
1001 The above would render on Microsoft SQL Server as:
1003 .. sourcecode:: sql
1005 SELECT TRY_CAST (product_table.unit_price AS NUMERIC(10, 4))
1006 FROM product_table
1008 .. versionadded:: 2.0.14 :func:`.try_cast` has been
1009 generalized from the SQL Server dialect into a general use
1010 construct that may be supported by additional dialects.
1012 """
1013 return TryCast(expression, type_)
1016def column(
1017 text: str,
1018 type_: Optional[_TypeEngineArgument[_T]] = None,
1019 is_literal: bool = False,
1020 _selectable: Optional[FromClause] = None,
1021) -> ColumnClause[_T]:
1022 """Produce a :class:`.ColumnClause` object.
1024 The :class:`.ColumnClause` is a lightweight analogue to the
1025 :class:`_schema.Column` class. The :func:`_expression.column`
1026 function can
1027 be invoked with just a name alone, as in::
1029 from sqlalchemy import column
1031 id, name = column("id"), column("name")
1032 stmt = select(id, name).select_from("user")
1034 The above statement would produce SQL like:
1036 .. sourcecode:: sql
1038 SELECT id, name FROM user
1040 Once constructed, :func:`_expression.column`
1041 may be used like any other SQL
1042 expression element such as within :func:`_expression.select`
1043 constructs::
1045 from sqlalchemy.sql import column
1047 id, name = column("id"), column("name")
1048 stmt = select(id, name).select_from("user")
1050 The text handled by :func:`_expression.column`
1051 is assumed to be handled
1052 like the name of a database column; if the string contains mixed case,
1053 special characters, or matches a known reserved word on the target
1054 backend, the column expression will render using the quoting
1055 behavior determined by the backend. To produce a textual SQL
1056 expression that is rendered exactly without any quoting,
1057 use :func:`_expression.literal_column` instead,
1058 or pass ``True`` as the
1059 value of :paramref:`_expression.column.is_literal`. Additionally,
1060 full SQL
1061 statements are best handled using the :func:`_expression.text`
1062 construct.
1064 :func:`_expression.column` can be used in a table-like
1065 fashion by combining it with the :func:`.table` function
1066 (which is the lightweight analogue to :class:`_schema.Table`
1067 ) to produce
1068 a working table construct with minimal boilerplate::
1070 from sqlalchemy import table, column, select
1072 user = table(
1073 "user",
1074 column("id"),
1075 column("name"),
1076 column("description"),
1077 )
1079 stmt = select(user.c.description).where(user.c.name == "wendy")
1081 A :func:`_expression.column` / :func:`.table`
1082 construct like that illustrated
1083 above can be created in an
1084 ad-hoc fashion and is not associated with any
1085 :class:`_schema.MetaData`, DDL, or events, unlike its
1086 :class:`_schema.Table` counterpart.
1088 :param text: the text of the element.
1090 :param type: :class:`_types.TypeEngine` object which can associate
1091 this :class:`.ColumnClause` with a type.
1093 :param is_literal: if True, the :class:`.ColumnClause` is assumed to
1094 be an exact expression that will be delivered to the output with no
1095 quoting rules applied regardless of case sensitive settings. the
1096 :func:`_expression.literal_column()` function essentially invokes
1097 :func:`_expression.column` while passing ``is_literal=True``.
1099 .. seealso::
1101 :class:`_schema.Column`
1103 :func:`_expression.literal_column`
1105 :func:`.table`
1107 :func:`_expression.text`
1109 :ref:`tutorial_select_arbitrary_text`
1111 """
1112 return ColumnClause(text, type_, is_literal, _selectable)
1115@overload
1116def desc(
1117 column: Union[str, "ColumnElement[_T]"],
1118) -> UnaryExpression[_T]: ...
1121@overload
1122def desc(
1123 column: _ColumnExpressionOrStrLabelArgument[_T],
1124) -> Union[OrderByList, UnaryExpression[_T]]: ...
1127def desc(
1128 column: _ColumnExpressionOrStrLabelArgument[_T],
1129) -> Union[OrderByList, UnaryExpression[_T]]:
1130 """Produce a descending ``ORDER BY`` clause element.
1132 e.g.::
1134 from sqlalchemy import desc
1136 stmt = select(users_table).order_by(desc(users_table.c.name))
1138 will produce SQL as:
1140 .. sourcecode:: sql
1142 SELECT id, name FROM user ORDER BY name DESC
1144 The :func:`.desc` function is a standalone version of the
1145 :meth:`_expression.ColumnElement.desc`
1146 method available on all SQL expressions,
1147 e.g.::
1150 stmt = select(users_table).order_by(users_table.c.name.desc())
1152 :param column: A :class:`_expression.ColumnElement` (e.g.
1153 scalar SQL expression)
1154 with which to apply the :func:`.desc` operation.
1156 .. seealso::
1158 :func:`.asc`
1160 :func:`.nulls_first`
1162 :func:`.nulls_last`
1164 :meth:`_expression.Select.order_by`
1166 """
1167 if isinstance(column, operators.OrderingOperators):
1168 return column.desc() # type: ignore[unused-ignore]
1169 else:
1170 return UnaryExpression._create_desc(column)
1173def distinct(expr: _ColumnExpressionArgument[_T]) -> UnaryExpression[_T]:
1174 """Produce an column-expression-level unary ``DISTINCT`` clause.
1176 This applies the ``DISTINCT`` keyword to an **individual column
1177 expression** (e.g. not the whole statement), and renders **specifically
1178 in that column position**; this is used for containment within
1179 an aggregate function, as in::
1181 from sqlalchemy import distinct, func
1183 stmt = select(users_table.c.id, func.count(distinct(users_table.c.name)))
1185 The above would produce an statement resembling:
1187 .. sourcecode:: sql
1189 SELECT user.id, count(DISTINCT user.name) FROM user
1191 .. tip:: The :func:`_sql.distinct` function does **not** apply DISTINCT
1192 to the full SELECT statement, instead applying a DISTINCT modifier
1193 to **individual column expressions**. For general ``SELECT DISTINCT``
1194 support, use the
1195 :meth:`_sql.Select.distinct` method on :class:`_sql.Select`.
1197 The :func:`.distinct` function is also available as a column-level
1198 method, e.g. :meth:`_expression.ColumnElement.distinct`, as in::
1200 stmt = select(func.count(users_table.c.name.distinct()))
1202 The :func:`.distinct` operator is different from the
1203 :meth:`_expression.Select.distinct` method of
1204 :class:`_expression.Select`,
1205 which produces a ``SELECT`` statement
1206 with ``DISTINCT`` applied to the result set as a whole,
1207 e.g. a ``SELECT DISTINCT`` expression. See that method for further
1208 information.
1210 .. seealso::
1212 :meth:`_expression.ColumnElement.distinct`
1214 :meth:`_expression.Select.distinct`
1216 :data:`.func`
1218 """ # noqa: E501
1219 if isinstance(expr, operators.ColumnOperators):
1220 return expr.distinct()
1221 else:
1222 return UnaryExpression._create_distinct(expr)
1225def bitwise_not(expr: _ColumnExpressionArgument[_T]) -> UnaryExpression[_T]:
1226 """Produce a unary bitwise NOT clause, typically via the ``~`` operator.
1228 Not to be confused with boolean negation :func:`_sql.not_`.
1230 .. versionadded:: 2.0.2
1232 .. seealso::
1234 :ref:`operators_bitwise`
1237 """
1238 if isinstance(expr, operators.ColumnOperators):
1239 return expr.bitwise_not()
1240 else:
1241 return UnaryExpression._create_bitwise_not(expr)
1244def extract(field: str, expr: _ColumnExpressionArgument[Any]) -> Extract:
1245 """Return a :class:`.Extract` construct.
1247 This is typically available as :func:`.extract`
1248 as well as ``func.extract`` from the
1249 :data:`.func` namespace.
1251 :param field: The field to extract.
1253 .. warning:: This field is used as a literal SQL string.
1254 **DO NOT PASS UNTRUSTED INPUT TO THIS STRING**.
1256 :param expr: A column or Python scalar expression serving as the
1257 right side of the ``EXTRACT`` expression.
1259 E.g.::
1261 from sqlalchemy import extract
1262 from sqlalchemy import table, column
1264 logged_table = table(
1265 "user",
1266 column("id"),
1267 column("date_created"),
1268 )
1270 stmt = select(logged_table.c.id).where(
1271 extract("YEAR", logged_table.c.date_created) == 2021
1272 )
1274 In the above example, the statement is used to select ids from the
1275 database where the ``YEAR`` component matches a specific value.
1277 Similarly, one can also select an extracted component::
1279 stmt = select(extract("YEAR", logged_table.c.date_created)).where(
1280 logged_table.c.id == 1
1281 )
1283 The implementation of ``EXTRACT`` may vary across database backends.
1284 Users are reminded to consult their database documentation.
1285 """
1286 return Extract(field, expr)
1289def false() -> False_:
1290 """Return a :class:`.False_` construct.
1292 E.g.:
1294 .. sourcecode:: pycon+sql
1296 >>> from sqlalchemy import false
1297 >>> print(select(t.c.x).where(false()))
1298 {printsql}SELECT x FROM t WHERE false
1300 A backend which does not support true/false constants will render as
1301 an expression against 1 or 0:
1303 .. sourcecode:: pycon+sql
1305 >>> print(select(t.c.x).where(false()))
1306 {printsql}SELECT x FROM t WHERE 0 = 1
1308 The :func:`.true` and :func:`.false` constants also feature
1309 "short circuit" operation within an :func:`.and_` or :func:`.or_`
1310 conjunction:
1312 .. sourcecode:: pycon+sql
1314 >>> print(select(t.c.x).where(or_(t.c.x > 5, true())))
1315 {printsql}SELECT x FROM t WHERE true{stop}
1317 >>> print(select(t.c.x).where(and_(t.c.x > 5, false())))
1318 {printsql}SELECT x FROM t WHERE false{stop}
1320 .. seealso::
1322 :func:`.true`
1324 """
1326 return False_._instance()
1329def funcfilter(
1330 func: FunctionElement[_T], *criterion: _ColumnExpressionArgument[bool]
1331) -> FunctionFilter[_T]:
1332 """Produce a :class:`.FunctionFilter` object against a function.
1334 Used against aggregate and window functions,
1335 for database backends that support the "FILTER" clause.
1337 E.g.::
1339 from sqlalchemy import funcfilter
1341 funcfilter(func.count(1), MyClass.name == "some name")
1343 Would produce "COUNT(1) FILTER (WHERE myclass.name = 'some name')".
1345 This function is also available from the :data:`~.expression.func`
1346 construct itself via the :meth:`.FunctionElement.filter` method.
1348 .. seealso::
1350 :ref:`tutorial_functions_within_group` - in the
1351 :ref:`unified_tutorial`
1353 :meth:`.FunctionElement.filter`
1355 """
1356 return FunctionFilter(func, *criterion)
1359def label(
1360 name: str,
1361 element: _ColumnExpressionArgument[_T],
1362 type_: Optional[_TypeEngineArgument[_T]] = None,
1363) -> Label[_T]:
1364 """Return a :class:`Label` object for the
1365 given :class:`_expression.ColumnElement`.
1367 A label changes the name of an element in the columns clause of a
1368 ``SELECT`` statement, typically via the ``AS`` SQL keyword.
1370 This functionality is more conveniently available via the
1371 :meth:`_expression.ColumnElement.label` method on
1372 :class:`_expression.ColumnElement`.
1374 :param name: label name
1376 :param obj: a :class:`_expression.ColumnElement`.
1378 """
1379 return Label(name, element, type_)
1382def null() -> Null:
1383 """Return a constant :class:`.Null` construct."""
1385 return Null._instance()
1388@overload
1389def nulls_first(
1390 column: "ColumnElement[_T]",
1391) -> UnaryExpression[_T]: ...
1394@overload
1395def nulls_first(
1396 column: _ColumnExpressionArgument[_T],
1397) -> Union[OrderByList, UnaryExpression[_T]]: ...
1400def nulls_first(
1401 column: _ColumnExpressionArgument[_T],
1402) -> Union[OrderByList, UnaryExpression[_T]]:
1403 """Produce the ``NULLS FIRST`` modifier for an ``ORDER BY`` expression.
1405 :func:`.nulls_first` is intended to modify the expression produced
1406 by :func:`.asc` or :func:`.desc`, and indicates how NULL values
1407 should be handled when they are encountered during ordering::
1410 from sqlalchemy import desc, nulls_first
1412 stmt = select(users_table).order_by(nulls_first(desc(users_table.c.name)))
1414 The SQL expression from the above would resemble:
1416 .. sourcecode:: sql
1418 SELECT id, name FROM user ORDER BY name DESC NULLS FIRST
1420 Like :func:`.asc` and :func:`.desc`, :func:`.nulls_first` is typically
1421 invoked from the column expression itself using
1422 :meth:`_expression.ColumnElement.nulls_first`,
1423 rather than as its standalone
1424 function version, as in::
1426 stmt = select(users_table).order_by(
1427 users_table.c.name.desc().nulls_first()
1428 )
1430 .. versionchanged:: 1.4 :func:`.nulls_first` is renamed from
1431 :func:`.nullsfirst` in previous releases.
1432 The previous name remains available for backwards compatibility.
1434 .. seealso::
1436 :func:`.asc`
1438 :func:`.desc`
1440 :func:`.nulls_last`
1442 :meth:`_expression.Select.order_by`
1444 """ # noqa: E501
1445 if isinstance(column, operators.OrderingOperators):
1446 return column.nulls_first()
1447 else:
1448 return UnaryExpression._create_nulls_first(column)
1451@overload
1452def nulls_last(
1453 column: "ColumnElement[_T]",
1454) -> UnaryExpression[_T]: ...
1457@overload
1458def nulls_last(
1459 column: _ColumnExpressionArgument[_T],
1460) -> Union[OrderByList, UnaryExpression[_T]]: ...
1463def nulls_last(
1464 column: _ColumnExpressionArgument[_T],
1465) -> Union[OrderByList, UnaryExpression[_T]]:
1466 """Produce the ``NULLS LAST`` modifier for an ``ORDER BY`` expression.
1468 :func:`.nulls_last` is intended to modify the expression produced
1469 by :func:`.asc` or :func:`.desc`, and indicates how NULL values
1470 should be handled when they are encountered during ordering::
1473 from sqlalchemy import desc, nulls_last
1475 stmt = select(users_table).order_by(nulls_last(desc(users_table.c.name)))
1477 The SQL expression from the above would resemble:
1479 .. sourcecode:: sql
1481 SELECT id, name FROM user ORDER BY name DESC NULLS LAST
1483 Like :func:`.asc` and :func:`.desc`, :func:`.nulls_last` is typically
1484 invoked from the column expression itself using
1485 :meth:`_expression.ColumnElement.nulls_last`,
1486 rather than as its standalone
1487 function version, as in::
1489 stmt = select(users_table).order_by(users_table.c.name.desc().nulls_last())
1491 .. versionchanged:: 1.4 :func:`.nulls_last` is renamed from
1492 :func:`.nullslast` in previous releases.
1493 The previous name remains available for backwards compatibility.
1495 .. seealso::
1497 :func:`.asc`
1499 :func:`.desc`
1501 :func:`.nulls_first`
1503 :meth:`_expression.Select.order_by`
1505 """ # noqa: E501
1506 if isinstance(column, operators.OrderingOperators):
1507 return column.nulls_last()
1508 else:
1509 return UnaryExpression._create_nulls_last(column)
1512def or_( # type: ignore[empty-body]
1513 initial_clause: Union[Literal[False], _ColumnExpressionArgument[bool]],
1514 *clauses: _ColumnExpressionArgument[bool],
1515) -> ColumnElement[bool]:
1516 """Produce a conjunction of expressions joined by ``OR``.
1518 E.g.::
1520 from sqlalchemy import or_
1522 stmt = select(users_table).where(
1523 or_(users_table.c.name == "wendy", users_table.c.name == "jack")
1524 )
1526 The :func:`.or_` conjunction is also available using the
1527 Python ``|`` operator (though note that compound expressions
1528 need to be parenthesized in order to function with Python
1529 operator precedence behavior)::
1531 stmt = select(users_table).where(
1532 (users_table.c.name == "wendy") | (users_table.c.name == "jack")
1533 )
1535 The :func:`.or_` construct must be given at least one positional
1536 argument in order to be valid; a :func:`.or_` construct with no
1537 arguments is ambiguous. To produce an "empty" or dynamically
1538 generated :func:`.or_` expression, from a given list of expressions,
1539 a "default" element of :func:`_sql.false` (or just ``False``) should be
1540 specified::
1542 from sqlalchemy import false
1544 or_criteria = or_(false(), *expressions)
1546 The above expression will compile to SQL as the expression ``false``
1547 or ``0 = 1``, depending on backend, if no other expressions are
1548 present. If expressions are present, then the :func:`_sql.false` value is
1549 ignored as it does not affect the outcome of an OR expression which
1550 has other elements.
1552 .. deprecated:: 1.4 The :func:`.or_` element now requires that at
1553 least one argument is passed; creating the :func:`.or_` construct
1554 with no arguments is deprecated, and will emit a deprecation warning
1555 while continuing to produce a blank SQL string.
1557 .. seealso::
1559 :func:`.and_`
1561 """
1562 ...
1565if not TYPE_CHECKING:
1566 # handle deprecated case which allows zero-arguments
1567 def or_(*clauses): # noqa: F811
1568 """Produce a conjunction of expressions joined by ``OR``.
1570 E.g.::
1572 from sqlalchemy import or_
1574 stmt = select(users_table).where(
1575 or_(users_table.c.name == "wendy", users_table.c.name == "jack")
1576 )
1578 The :func:`.or_` conjunction is also available using the
1579 Python ``|`` operator (though note that compound expressions
1580 need to be parenthesized in order to function with Python
1581 operator precedence behavior)::
1583 stmt = select(users_table).where(
1584 (users_table.c.name == "wendy") | (users_table.c.name == "jack")
1585 )
1587 The :func:`.or_` construct must be given at least one positional
1588 argument in order to be valid; a :func:`.or_` construct with no
1589 arguments is ambiguous. To produce an "empty" or dynamically
1590 generated :func:`.or_` expression, from a given list of expressions,
1591 a "default" element of :func:`_sql.false` (or just ``False``) should be
1592 specified::
1594 from sqlalchemy import false
1596 or_criteria = or_(false(), *expressions)
1598 The above expression will compile to SQL as the expression ``false``
1599 or ``0 = 1``, depending on backend, if no other expressions are
1600 present. If expressions are present, then the :func:`_sql.false` value
1601 is ignored as it does not affect the outcome of an OR expression which
1602 has other elements.
1604 .. deprecated:: 1.4 The :func:`.or_` element now requires that at
1605 least one argument is passed; creating the :func:`.or_` construct
1606 with no arguments is deprecated, and will emit a deprecation warning
1607 while continuing to produce a blank SQL string.
1609 .. seealso::
1611 :func:`.and_`
1613 """ # noqa: E501
1614 return BooleanClauseList.or_(*clauses)
1617def over(
1618 element: FunctionElement[_T],
1619 partition_by: _ByArgument | None = None,
1620 order_by: _ByArgument | None = None,
1621 range_: _FrameIntTuple | FrameClause | None = None,
1622 rows: _FrameIntTuple | FrameClause | None = None,
1623 groups: _FrameIntTuple | FrameClause | None = None,
1624) -> Over[_T]:
1625 r"""Produce an :class:`.Over` object against a function.
1627 Used against aggregate or so-called "window" functions,
1628 for database backends that support window functions.
1630 :func:`_expression.over` is usually called using
1631 the :meth:`.FunctionElement.over` method, e.g.::
1633 func.row_number().over(order_by=mytable.c.some_column)
1635 Would produce:
1637 .. sourcecode:: sql
1639 ROW_NUMBER() OVER(ORDER BY some_column)
1641 Ranges are also possible using the :paramref:`.expression.over.range_`,
1642 :paramref:`.expression.over.rows`, and :paramref:`.expression.over.groups`
1643 parameters. These
1644 mutually-exclusive parameters each accept a 2-tuple, which contains
1645 a combination of integers and None::
1647 func.row_number().over(order_by=my_table.c.some_column, range_=(None, 0))
1649 The above would produce:
1651 .. sourcecode:: sql
1653 ROW_NUMBER() OVER(ORDER BY some_column
1654 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
1656 A value of ``None`` indicates "unbounded", a
1657 value of zero indicates "current row", and negative / positive
1658 integers indicate "preceding" and "following":
1660 * RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING::
1662 func.row_number().over(order_by="x", range_=(-5, 10))
1664 * ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW::
1666 func.row_number().over(order_by="x", rows=(None, 0))
1668 * RANGE BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING::
1670 func.row_number().over(order_by="x", range_=(-2, None))
1672 * RANGE BETWEEN 1 FOLLOWING AND 3 FOLLOWING::
1674 func.row_number().over(order_by="x", range_=(1, 3))
1676 * GROUPS BETWEEN 1 FOLLOWING AND 3 FOLLOWING::
1678 func.row_number().over(order_by="x", groups=(1, 3))
1680 Depending on the type of the order column, the 'RANGE' value may not be
1681 an integer. In this case use a :class:`_expression.FrameClause` directly
1682 to specify the frame boundaries. E.g.::
1684 from datetime import timedelta
1685 from sqlalchemy import FrameClause, FrameClauseType
1687 func.sum(my_table.c.amount).over(
1688 order_by=my_table.c.date,
1689 range_=FrameClause(
1690 start=timedelta(days=7),
1691 end=None,
1692 start_frame_type=FrameClauseType.PRECEDING,
1693 end_frame_type=FrameClauseType.UNBOUNDED,
1694 ),
1695 )
1697 .. versionchanged:: 2.1 Added support for range types that are not
1698 integer-based, via the :class:`_expression.FrameClause` construct.
1700 :param element: a :class:`.FunctionElement`, :class:`.WithinGroup`,
1701 or other compatible construct.
1702 :param partition_by: a column element or string, or a list
1703 of such, that will be used as the PARTITION BY clause
1704 of the OVER construct.
1705 :param order_by: a column element or string, or a list
1706 of such, that will be used as the ORDER BY clause
1707 of the OVER construct.
1708 :param range\_: optional range clause for the window. This is a
1709 two-tuple value which can contain integer values or ``None``,
1710 and will render a RANGE BETWEEN PRECEDING / FOLLOWING clause.
1711 Can also be a :class:`_expression.FrameClause` instance to
1712 specify non-integer values.
1714 .. versionchanged:: 2.1 Added support for range types that are not
1715 integer-based, via the :class:`_expression.FrameClause` construct.
1717 :param rows: optional rows clause for the window. This is a two-tuple
1718 value which can contain integer values or None, and will render
1719 a ROWS BETWEEN PRECEDING / FOLLOWING clause. Can also be a
1720 :class:`_expression.FrameClause` instance.
1721 :param groups: optional groups clause for the window. This is a
1722 two-tuple value which can contain integer values or ``None``,
1723 and will render a GROUPS BETWEEN PRECEDING / FOLLOWING clause.
1724 Can also be a :class:`_expression.FrameClause` instance.
1726 .. versionadded:: 2.0.40
1728 This function is also available from the :data:`~.expression.func`
1729 construct itself via the :meth:`.FunctionElement.over` method.
1731 .. seealso::
1733 :ref:`tutorial_window_functions` - in the :ref:`unified_tutorial`
1735 :data:`.expression.func`
1737 :func:`_expression.within_group`
1739 """ # noqa: E501
1740 return Over(element, partition_by, order_by, range_, rows, groups)
1743@_document_text_coercion("text", ":func:`.text`", ":paramref:`.text.text`")
1744def text(text: str) -> TextClause:
1745 r"""Construct a new :class:`_expression.TextClause` clause,
1746 representing
1747 a textual SQL string directly.
1749 E.g.::
1751 from sqlalchemy import text
1753 t = text("SELECT * FROM users")
1754 result = connection.execute(t)
1756 The advantages :func:`_expression.text`
1757 provides over a plain string are
1758 backend-neutral support for bind parameters, per-statement
1759 execution options, as well as
1760 bind parameter and result-column typing behavior, allowing
1761 SQLAlchemy type constructs to play a role when executing
1762 a statement that is specified literally. The construct can also
1763 be provided with a ``.c`` collection of column elements, allowing
1764 it to be embedded in other SQL expression constructs as a subquery.
1766 Bind parameters are specified by name, using the format ``:name``.
1767 E.g.::
1769 t = text("SELECT * FROM users WHERE id=:user_id")
1770 result = connection.execute(t, {"user_id": 12})
1772 For SQL statements where a colon is required verbatim, as within
1773 an inline string, use a backslash to escape::
1775 t = text(r"SELECT * FROM users WHERE name='\:username'")
1777 The :class:`_expression.TextClause`
1778 construct includes methods which can
1779 provide information about the bound parameters as well as the column
1780 values which would be returned from the textual statement, assuming
1781 it's an executable SELECT type of statement. The
1782 :meth:`_expression.TextClause.bindparams`
1783 method is used to provide bound
1784 parameter detail, and :meth:`_expression.TextClause.columns`
1785 method allows
1786 specification of return columns including names and types::
1788 t = (
1789 text("SELECT * FROM users WHERE id=:user_id")
1790 .bindparams(user_id=7)
1791 .columns(id=Integer, name=String)
1792 )
1794 for id, name in connection.execute(t):
1795 print(id, name)
1797 The :func:`_expression.text` construct is used in cases when
1798 a literal string SQL fragment is specified as part of a larger query,
1799 such as for the WHERE clause of a SELECT statement::
1801 s = select(users.c.id, users.c.name).where(text("id=:user_id"))
1802 result = connection.execute(s, {"user_id": 12})
1804 :func:`_expression.text` is also used for the construction
1805 of a full, standalone statement using plain text.
1806 As such, SQLAlchemy refers
1807 to it as an :class:`.Executable` object and may be used
1808 like any other statement passed to an ``.execute()`` method.
1810 :param text:
1811 the text of the SQL statement to be created. Use ``:<param>``
1812 to specify bind parameters; they will be compiled to their
1813 engine-specific format.
1815 .. seealso::
1817 :ref:`tutorial_select_arbitrary_text`
1819 """
1820 return TextClause(text)
1823def tstring(template: Template) -> TString:
1824 r"""Construct a new :class:`_expression.TString` clause,
1825 representing a SQL template string using Python 3.14+ t-strings.
1827 .. versionadded:: 2.1
1829 E.g.::
1831 from sqlalchemy import tstring
1833 a = 5
1834 b = 10
1835 stmt = tstring(t"select {a}, {b}")
1836 result = connection.execute(stmt)
1838 The :func:`_expression.tstring` function accepts a Python 3.14+
1839 template string (t-string) and processes it to create a SQL statement.
1840 Unlike :func:`_expression.text`, which requires manual bind parameter
1841 specification, :func:`_expression.tstring` automatically handles
1842 interpolation of Python values and SQLAlchemy expressions.
1844 **Interpolation Behavior**:
1846 - **SQL content** expressed in the plain string portions of the template
1847 are rendered directly as SQL
1848 - **SQLAlchemy expressions** (columns, functions, etc.) are embedded
1849 as clause elements
1850 - **Plain Python values** are automatically wrapped in
1851 :func:`_expression.literal`
1853 For example::
1855 from sqlalchemy import tstring, select, literal, JSON, table, column
1857 # Python values become bound parameters
1858 user_id = 42
1859 stmt = tstring(t"SELECT * FROM users WHERE id = {user_id}")
1860 # renders: SELECT * FROM users WHERE id = :param_1
1862 # SQLAlchemy expressions are embedded
1863 stmt = tstring(t"SELECT {column('q')} FROM {table('t')}")
1864 # renders: SELECT q FROM t
1866 # Apply explicit SQL types to bound values using literal()
1867 some_json = {"foo": "bar"}
1868 stmt = tstring(t"SELECT {literal(some_json, JSON)}")
1870 **Column Specification**:
1872 Like :func:`_expression.text`, the :func:`_expression.tstring` construct
1873 supports the :meth:`_expression.TString.columns` method to specify
1874 return columns and their types::
1876 from sqlalchemy import tstring, column, Integer, String
1878 stmt = tstring(t"SELECT id, name FROM users").columns(
1879 column("id", Integer), column("name", String)
1880 )
1882 for id, name in connection.execute(stmt):
1883 print(id, name)
1885 :param template:
1886 a Python 3.14+ template string (t-string) containing SQL fragments
1887 and Python expressions to be interpolated.
1889 .. seealso::
1891 :ref:`tutorial_select_arbitrary_text` - in the :ref:`unified_tutorial`
1893 :class:`_expression.TString`
1895 :func:`_expression.text`
1897 `PEP 750 <https://peps.python.org/pep-0750/>`_ - Template Strings
1899 """
1900 return TString(template)
1903def true() -> True_:
1904 """Return a constant :class:`.True_` construct.
1906 E.g.:
1908 .. sourcecode:: pycon+sql
1910 >>> from sqlalchemy import true
1911 >>> print(select(t.c.x).where(true()))
1912 {printsql}SELECT x FROM t WHERE true
1914 A backend which does not support true/false constants will render as
1915 an expression against 1 or 0:
1917 .. sourcecode:: pycon+sql
1919 >>> print(select(t.c.x).where(true()))
1920 {printsql}SELECT x FROM t WHERE 1 = 1
1922 The :func:`.true` and :func:`.false` constants also feature
1923 "short circuit" operation within an :func:`.and_` or :func:`.or_`
1924 conjunction:
1926 .. sourcecode:: pycon+sql
1928 >>> print(select(t.c.x).where(or_(t.c.x > 5, true())))
1929 {printsql}SELECT x FROM t WHERE true{stop}
1931 >>> print(select(t.c.x).where(and_(t.c.x > 5, false())))
1932 {printsql}SELECT x FROM t WHERE false{stop}
1934 .. seealso::
1936 :func:`.false`
1938 """
1940 return True_._instance()
1943def tuple_(
1944 *clauses: _ColumnExpressionOrLiteralArgument[Any],
1945 types: Optional[Sequence[_TypeEngineArgument[Any]]] = None,
1946) -> Tuple:
1947 """Return a :class:`.Tuple`.
1949 Main usage is to produce a composite IN construct using
1950 :meth:`.ColumnOperators.in_` ::
1952 from sqlalchemy import tuple_
1954 tuple_(table.c.col1, table.c.col2).in_([(1, 2), (5, 12), (10, 19)])
1956 .. warning::
1958 The composite IN construct is not supported by all backends, and is
1959 currently known to work on PostgreSQL, MySQL, and SQLite.
1960 Unsupported backends will raise a subclass of
1961 :class:`~sqlalchemy.exc.DBAPIError` when such an expression is
1962 invoked.
1964 """
1965 return Tuple(*clauses, types=types)
1968def type_coerce(
1969 expression: _ColumnExpressionOrLiteralArgument[Any],
1970 type_: _TypeEngineArgument[_T],
1971) -> TypeCoerce[_T]:
1972 r"""Associate a SQL expression with a particular type, without rendering
1973 ``CAST``.
1975 E.g.::
1977 from sqlalchemy import type_coerce
1979 stmt = select(type_coerce(log_table.date_string, StringDateTime()))
1981 The above construct will produce a :class:`.TypeCoerce` object, which
1982 does not modify the rendering in any way on the SQL side, with the
1983 possible exception of a generated label if used in a columns clause
1984 context:
1986 .. sourcecode:: sql
1988 SELECT date_string AS date_string FROM log
1990 When result rows are fetched, the ``StringDateTime`` type processor
1991 will be applied to result rows on behalf of the ``date_string`` column.
1993 .. note:: the :func:`.type_coerce` construct does not render any
1994 SQL syntax of its own, including that it does not imply
1995 parenthesization. Please use :meth:`.TypeCoerce.self_group`
1996 if explicit parenthesization is required.
1998 In order to provide a named label for the expression, use
1999 :meth:`_expression.ColumnElement.label`::
2001 stmt = select(
2002 type_coerce(log_table.date_string, StringDateTime()).label("date")
2003 )
2005 A type that features bound-value handling will also have that behavior
2006 take effect when literal values or :func:`.bindparam` constructs are
2007 passed to :func:`.type_coerce` as targets.
2008 For example, if a type implements the
2009 :meth:`.TypeEngine.bind_expression`
2010 method or :meth:`.TypeEngine.bind_processor` method or equivalent,
2011 these functions will take effect at statement compilation/execution
2012 time when a literal value is passed, as in::
2014 # bound-value handling of MyStringType will be applied to the
2015 # literal value "some string"
2016 stmt = select(type_coerce("some string", MyStringType))
2018 When using :func:`.type_coerce` with composed expressions, note that
2019 **parenthesis are not applied**. If :func:`.type_coerce` is being
2020 used in an operator context where the parenthesis normally present from
2021 CAST are necessary, use the :meth:`.TypeCoerce.self_group` method:
2023 .. sourcecode:: pycon+sql
2025 >>> some_integer = column("someint", Integer)
2026 >>> some_string = column("somestr", String)
2027 >>> expr = type_coerce(some_integer + 5, String) + some_string
2028 >>> print(expr)
2029 {printsql}someint + :someint_1 || somestr{stop}
2030 >>> expr = type_coerce(some_integer + 5, String).self_group() + some_string
2031 >>> print(expr)
2032 {printsql}(someint + :someint_1) || somestr{stop}
2034 :param expression: A SQL expression, such as a
2035 :class:`_expression.ColumnElement`
2036 expression or a Python string which will be coerced into a bound
2037 literal value.
2039 :param type\_: A :class:`.TypeEngine` class or instance indicating
2040 the type to which the expression is coerced.
2042 .. seealso::
2044 :ref:`tutorial_casts`
2046 :func:`.cast`
2048 """ # noqa
2049 return TypeCoerce(expression, type_)
2052def within_group(
2053 element: FunctionElement[_T], *order_by: _ColumnExpressionArgument[Any]
2054) -> WithinGroup[_T]:
2055 r"""Produce a :class:`.WithinGroup` object against a function.
2057 Used against so-called "ordered set aggregate" and "hypothetical
2058 set aggregate" functions, including :class:`.percentile_cont`,
2059 :class:`.rank`, :class:`.dense_rank`, etc. This feature is typically
2060 used by Oracle Database, Microsoft SQL Server.
2062 For generalized ORDER BY of aggregate functions on all included
2063 backends, including PostgreSQL, MySQL/MariaDB, SQLite as well as Oracle
2064 and SQL Server, the :func:`_sql.aggregate_order_by` provides a more
2065 general approach that compiles to "WITHIN GROUP" only on those backends
2066 which require it.
2068 :func:`_expression.within_group` is usually called using
2069 the :meth:`.FunctionElement.within_group` method, e.g.::
2071 stmt = select(
2072 func.percentile_cont(0.5).within_group(department.c.salary.desc()),
2073 )
2075 The above statement would produce SQL similar to
2076 ``SELECT percentile_cont(0.5)
2077 WITHIN GROUP (ORDER BY department.salary DESC)``.
2079 :param element: a :class:`.FunctionElement` construct, typically
2080 generated by :data:`~.expression.func`.
2081 :param \*order_by: one or more column elements that will be used
2082 as the ORDER BY clause of the WITHIN GROUP construct.
2084 .. seealso::
2086 :ref:`tutorial_functions_within_group` - in the
2087 :ref:`unified_tutorial`
2089 :func:`_sql.aggregate_order_by` - helper for PostgreSQL, MySQL,
2090 SQLite aggregate functions
2092 :data:`.expression.func`
2094 :func:`_expression.over`
2096 """
2097 return WithinGroup(element, *order_by)
2100def aggregate_order_by(
2101 element: FunctionElement[_T], *order_by: _ColumnExpressionArgument[Any]
2102) -> AggregateOrderBy[_T]:
2103 r"""Produce a :class:`.AggregateOrderBy` object against a function.
2105 Used for aggregating functions such as :class:`_functions.array_agg`,
2106 ``group_concat``, ``json_agg`` on backends that support ordering via an
2107 embedded ``ORDER BY`` parameter, e.g. PostgreSQL, MySQL/MariaDB, SQLite.
2108 When used on backends like Oracle and SQL Server, SQL compilation uses that
2109 of :class:`.WithinGroup`. On PostgreSQL, compilation is fixed at embedded
2110 ``ORDER BY``; for set aggregation functions where PostgreSQL requires the
2111 use of ``WITHIN GROUP``, :func:`_expression.within_group` should be used
2112 explicitly.
2114 :func:`_expression.aggregate_order_by` is usually called using
2115 the :meth:`.FunctionElement.aggregate_order_by` method, e.g.::
2117 stmt = select(
2118 func.array_agg(department.c.code).aggregate_order_by(
2119 department.c.code.desc()
2120 ),
2121 )
2123 which would produce an expression resembling:
2125 .. sourcecode:: sql
2127 SELECT array_agg(department.code ORDER BY department.code DESC)
2128 AS array_agg_1 FROM department
2130 The ORDER BY argument may also be multiple terms.
2132 When using the backend-agnostic :class:`_functions.aggregate_strings`
2133 string aggregation function, use the
2134 :paramref:`_functions.aggregate_strings.order_by` parameter to indicate a
2135 dialect-agnostic ORDER BY expression.
2137 .. versionadded:: 2.1 Generalized the PostgreSQL-specific
2138 :func:`_postgresql.aggregate_order_by` function to a method on
2139 :class:`.Function` that is backend agnostic.
2141 .. seealso::
2143 :class:`_functions.aggregate_strings` - backend-agnostic string
2144 concatenation function which also supports ORDER BY
2146 """ # noqa: E501
2147 return AggregateOrderBy(element, *order_by)