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 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 reused::
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 exclude: str | None = None,
1625) -> Over[_T]:
1626 r"""Produce an :class:`.Over` object against a function.
1628 Used against aggregate or so-called "window" functions,
1629 for database backends that support window functions.
1631 :func:`_expression.over` is usually called using
1632 the :meth:`.FunctionElement.over` method, e.g.::
1634 func.row_number().over(order_by=mytable.c.some_column)
1636 Would produce:
1638 .. sourcecode:: sql
1640 ROW_NUMBER() OVER(ORDER BY some_column)
1642 Ranges are also possible using the :paramref:`.expression.over.range_`,
1643 :paramref:`.expression.over.rows`, and :paramref:`.expression.over.groups`
1644 parameters. These
1645 mutually-exclusive parameters each accept a 2-tuple, which contains
1646 a combination of integers and None::
1648 func.row_number().over(order_by=my_table.c.some_column, range_=(None, 0))
1650 The above would produce:
1652 .. sourcecode:: sql
1654 ROW_NUMBER() OVER(ORDER BY some_column
1655 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
1657 A value of ``None`` indicates "unbounded", a
1658 value of zero indicates "current row", and negative / positive
1659 integers indicate "preceding" and "following":
1661 * RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING::
1663 func.row_number().over(order_by="x", range_=(-5, 10))
1665 * ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW::
1667 func.row_number().over(order_by="x", rows=(None, 0))
1669 * RANGE BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING::
1671 func.row_number().over(order_by="x", range_=(-2, None))
1673 * RANGE BETWEEN 1 FOLLOWING AND 3 FOLLOWING::
1675 func.row_number().over(order_by="x", range_=(1, 3))
1677 * GROUPS BETWEEN 1 FOLLOWING AND 3 FOLLOWING::
1679 func.row_number().over(order_by="x", groups=(1, 3))
1681 Depending on the type of the order column, the 'RANGE' value may not be
1682 an integer. In this case use a :class:`_expression.FrameClause` directly
1683 to specify the frame boundaries. E.g.::
1685 from datetime import timedelta
1686 from sqlalchemy import FrameClause, FrameClauseType
1688 func.sum(my_table.c.amount).over(
1689 order_by=my_table.c.date,
1690 range_=FrameClause(
1691 start=timedelta(days=7),
1692 end=None,
1693 start_frame_type=FrameClauseType.PRECEDING,
1694 end_frame_type=FrameClauseType.UNBOUNDED,
1695 ),
1696 )
1698 .. versionchanged:: 2.1 Added support for range types that are not
1699 integer-based, via the :class:`_expression.FrameClause` construct.
1701 :param element: a :class:`.FunctionElement`, :class:`.WithinGroup`,
1702 or other compatible construct.
1703 :param partition_by: a column element or string, or a list
1704 of such, that will be used as the PARTITION BY clause
1705 of the OVER construct.
1706 :param order_by: a column element or string, or a list
1707 of such, that will be used as the ORDER BY clause
1708 of the OVER construct.
1709 :param range\_: optional range clause for the window. This is a
1710 two-tuple value which can contain integer values or ``None``,
1711 and will render a RANGE BETWEEN PRECEDING / FOLLOWING clause.
1712 Can also be a :class:`_expression.FrameClause` instance to
1713 specify non-integer values.
1715 .. versionchanged:: 2.1 Added support for range types that are not
1716 integer-based, via the :class:`_expression.FrameClause` construct.
1718 :param rows: optional rows clause for the window. This is a two-tuple
1719 value which can contain integer values or None, and will render
1720 a ROWS BETWEEN PRECEDING / FOLLOWING clause. Can also be a
1721 :class:`_expression.FrameClause` instance.
1722 :param groups: optional groups clause for the window. This is a
1723 two-tuple value which can contain integer values or ``None``,
1724 and will render a GROUPS BETWEEN PRECEDING / FOLLOWING clause.
1725 Can also be a :class:`_expression.FrameClause` instance.
1727 .. versionadded:: 2.0.40
1729 :param exclude: optional string for the frame exclusion clause.
1730 This is a string value which can be one of ``CURRENT ROW``,
1731 ``GROUP``, ``TIES``, or ``NO OTHERS`` and will render an
1732 EXCLUDE clause within the window frame specification. Requires
1733 that one of ``rows``, ``range_``, or ``groups`` is also specified.
1735 .. versionadded:: 2.1
1737 This function is also available from the :data:`~.expression.func`
1738 construct itself via the :meth:`.FunctionElement.over` method.
1740 .. seealso::
1742 :ref:`tutorial_window_functions` - in the :ref:`unified_tutorial`
1744 :data:`.expression.func`
1746 :func:`_expression.within_group`
1748 """ # noqa: E501
1749 return Over(
1750 element,
1751 partition_by,
1752 order_by,
1753 range_,
1754 rows,
1755 groups,
1756 exclude,
1757 )
1760@_document_text_coercion("text", ":func:`.text`", ":paramref:`.text.text`")
1761def text(text: str) -> TextClause:
1762 r"""Construct a new :class:`_expression.TextClause` clause,
1763 representing
1764 a textual SQL string directly.
1766 E.g.::
1768 from sqlalchemy import text
1770 t = text("SELECT * FROM users")
1771 result = connection.execute(t)
1773 The advantages :func:`_expression.text`
1774 provides over a plain string are
1775 backend-neutral support for bind parameters, per-statement
1776 execution options, as well as
1777 bind parameter and result-column typing behavior, allowing
1778 SQLAlchemy type constructs to play a role when executing
1779 a statement that is specified literally. The construct can also
1780 be provided with a ``.c`` collection of column elements, allowing
1781 it to be embedded in other SQL expression constructs as a subquery.
1783 Bind parameters are specified by name, using the format ``:name``.
1784 E.g.::
1786 t = text("SELECT * FROM users WHERE id=:user_id")
1787 result = connection.execute(t, {"user_id": 12})
1789 For SQL statements where a colon is required verbatim, as within
1790 an inline string, use a backslash to escape::
1792 t = text(r"SELECT * FROM users WHERE name='\:username'")
1794 The :class:`_expression.TextClause`
1795 construct includes methods which can
1796 provide information about the bound parameters as well as the column
1797 values which would be returned from the textual statement, assuming
1798 it's an executable SELECT type of statement. The
1799 :meth:`_expression.TextClause.bindparams`
1800 method is used to provide bound
1801 parameter detail, and :meth:`_expression.TextClause.columns`
1802 method allows
1803 specification of return columns including names and types::
1805 t = (
1806 text("SELECT * FROM users WHERE id=:user_id")
1807 .bindparams(user_id=7)
1808 .columns(id=Integer, name=String)
1809 )
1811 for id, name in connection.execute(t):
1812 print(id, name)
1814 The :func:`_expression.text` construct is used in cases when
1815 a literal string SQL fragment is specified as part of a larger query,
1816 such as for the WHERE clause of a SELECT statement::
1818 s = select(users.c.id, users.c.name).where(text("id=:user_id"))
1819 result = connection.execute(s, {"user_id": 12})
1821 :func:`_expression.text` is also used for the construction
1822 of a full, standalone statement using plain text.
1823 As such, SQLAlchemy refers
1824 to it as an :class:`.Executable` object and may be used
1825 like any other statement passed to an ``.execute()`` method.
1827 :param text:
1828 the text of the SQL statement to be created. Use ``:<param>``
1829 to specify bind parameters; they will be compiled to their
1830 engine-specific format.
1832 .. seealso::
1834 :ref:`tutorial_select_arbitrary_text`
1836 """
1837 return TextClause(text)
1840def tstring(template: Template) -> TString:
1841 r"""Construct a new :class:`_expression.TString` clause,
1842 representing a SQL template string using Python 3.14+ t-strings.
1844 .. versionadded:: 2.1
1846 E.g.::
1848 from sqlalchemy import tstring
1850 a = 5
1851 b = 10
1852 stmt = tstring(t"select {a}, {b}")
1853 result = connection.execute(stmt)
1855 The :func:`_expression.tstring` function accepts a Python 3.14+
1856 template string (t-string) and processes it to create a SQL statement.
1857 Unlike :func:`_expression.text`, which requires manual bind parameter
1858 specification, :func:`_expression.tstring` automatically handles
1859 interpolation of Python values and SQLAlchemy expressions.
1861 **Interpolation Behavior**:
1863 - **SQL content** expressed in the plain string portions of the template
1864 are rendered directly as SQL
1865 - **SQLAlchemy expressions** (columns, functions, etc.) are embedded
1866 as clause elements
1867 - **Plain Python values** are automatically wrapped in
1868 :func:`_expression.literal`
1870 For example::
1872 from sqlalchemy import tstring, select, literal, JSON, table, column
1874 # Python values become bound parameters
1875 user_id = 42
1876 stmt = tstring(t"SELECT * FROM users WHERE id = {user_id}")
1877 # renders: SELECT * FROM users WHERE id = :param_1
1879 # SQLAlchemy expressions are embedded
1880 stmt = tstring(t"SELECT {column('q')} FROM {table('t')}")
1881 # renders: SELECT q FROM t
1883 # Apply explicit SQL types to bound values using literal()
1884 some_json = {"foo": "bar"}
1885 stmt = tstring(t"SELECT {literal(some_json, JSON)}")
1887 **Column Specification**:
1889 Like :func:`_expression.text`, the :func:`_expression.tstring` construct
1890 supports the :meth:`_expression.TString.columns` method to specify
1891 return columns and their types::
1893 from sqlalchemy import tstring, column, Integer, String
1895 stmt = tstring(t"SELECT id, name FROM users").columns(
1896 column("id", Integer), column("name", String)
1897 )
1899 for id, name in connection.execute(stmt):
1900 print(id, name)
1902 :param template:
1903 a Python 3.14+ template string (t-string) containing SQL fragments
1904 and Python expressions to be interpolated.
1906 .. seealso::
1908 :ref:`tutorial_select_arbitrary_text` - in the :ref:`unified_tutorial`
1910 :class:`_expression.TString`
1912 :func:`_expression.text`
1914 `PEP 750 <https://peps.python.org/pep-0750/>`_ - Template Strings
1916 """
1917 return TString(template)
1920def true() -> True_:
1921 """Return a constant :class:`.True_` construct.
1923 E.g.:
1925 .. sourcecode:: pycon+sql
1927 >>> from sqlalchemy import true
1928 >>> print(select(t.c.x).where(true()))
1929 {printsql}SELECT x FROM t WHERE true
1931 A backend which does not support true/false constants will render as
1932 an expression against 1 or 0:
1934 .. sourcecode:: pycon+sql
1936 >>> print(select(t.c.x).where(true()))
1937 {printsql}SELECT x FROM t WHERE 1 = 1
1939 The :func:`.true` and :func:`.false` constants also feature
1940 "short circuit" operation within an :func:`.and_` or :func:`.or_`
1941 conjunction:
1943 .. sourcecode:: pycon+sql
1945 >>> print(select(t.c.x).where(or_(t.c.x > 5, true())))
1946 {printsql}SELECT x FROM t WHERE true{stop}
1948 >>> print(select(t.c.x).where(and_(t.c.x > 5, false())))
1949 {printsql}SELECT x FROM t WHERE false{stop}
1951 .. seealso::
1953 :func:`.false`
1955 """
1957 return True_._instance()
1960def tuple_(
1961 *clauses: _ColumnExpressionOrLiteralArgument[Any],
1962 types: Optional[Sequence[_TypeEngineArgument[Any]]] = None,
1963) -> Tuple:
1964 """Return a :class:`.Tuple`.
1966 Main usage is to produce a composite IN construct using
1967 :meth:`.ColumnOperators.in_` ::
1969 from sqlalchemy import tuple_
1971 tuple_(table.c.col1, table.c.col2).in_([(1, 2), (5, 12), (10, 19)])
1973 .. warning::
1975 The composite IN construct is not supported by all backends, and is
1976 currently known to work on PostgreSQL, MySQL, and SQLite.
1977 Unsupported backends will raise a subclass of
1978 :class:`~sqlalchemy.exc.DBAPIError` when such an expression is
1979 invoked.
1981 """
1982 return Tuple(*clauses, types=types)
1985def type_coerce(
1986 expression: _ColumnExpressionOrLiteralArgument[Any],
1987 type_: _TypeEngineArgument[_T],
1988) -> TypeCoerce[_T]:
1989 r"""Associate a SQL expression with a particular type, without rendering
1990 ``CAST``.
1992 E.g.::
1994 from sqlalchemy import type_coerce
1996 stmt = select(type_coerce(log_table.date_string, StringDateTime()))
1998 The above construct will produce a :class:`.TypeCoerce` object, which
1999 does not modify the rendering in any way on the SQL side, with the
2000 possible exception of a generated label if used in a columns clause
2001 context:
2003 .. sourcecode:: sql
2005 SELECT date_string AS date_string FROM log
2007 When result rows are fetched, the ``StringDateTime`` type processor
2008 will be applied to result rows on behalf of the ``date_string`` column.
2010 .. note:: the :func:`.type_coerce` construct does not render any
2011 SQL syntax of its own, including that it does not imply
2012 parenthesization. Please use :meth:`.TypeCoerce.self_group`
2013 if explicit parenthesization is required.
2015 In order to provide a named label for the expression, use
2016 :meth:`_expression.ColumnElement.label`::
2018 stmt = select(
2019 type_coerce(log_table.date_string, StringDateTime()).label("date")
2020 )
2022 A type that features bound-value handling will also have that behavior
2023 take effect when literal values or :func:`.bindparam` constructs are
2024 passed to :func:`.type_coerce` as targets.
2025 For example, if a type implements the
2026 :meth:`.TypeEngine.bind_expression`
2027 method or :meth:`.TypeEngine.bind_processor` method or equivalent,
2028 these functions will take effect at statement compilation/execution
2029 time when a literal value is passed, as in::
2031 # bound-value handling of MyStringType will be applied to the
2032 # literal value "some string"
2033 stmt = select(type_coerce("some string", MyStringType))
2035 When using :func:`.type_coerce` with composed expressions, note that
2036 **parenthesis are not applied**. If :func:`.type_coerce` is being
2037 used in an operator context where the parenthesis normally present from
2038 CAST are necessary, use the :meth:`.TypeCoerce.self_group` method:
2040 .. sourcecode:: pycon+sql
2042 >>> some_integer = column("someint", Integer)
2043 >>> some_string = column("somestr", String)
2044 >>> expr = type_coerce(some_integer + 5, String) + some_string
2045 >>> print(expr)
2046 {printsql}someint + :someint_1 || somestr{stop}
2047 >>> expr = type_coerce(some_integer + 5, String).self_group() + some_string
2048 >>> print(expr)
2049 {printsql}(someint + :someint_1) || somestr{stop}
2051 :param expression: A SQL expression, such as a
2052 :class:`_expression.ColumnElement`
2053 expression or a Python string which will be coerced into a bound
2054 literal value.
2056 :param type\_: A :class:`.TypeEngine` class or instance indicating
2057 the type to which the expression is coerced.
2059 .. seealso::
2061 :ref:`tutorial_casts`
2063 :func:`.cast`
2065 """ # noqa
2066 return TypeCoerce(expression, type_)
2069def within_group(
2070 element: FunctionElement[_T], *order_by: _ColumnExpressionArgument[Any]
2071) -> WithinGroup[_T]:
2072 r"""Produce a :class:`.WithinGroup` object against a function.
2074 Used against so-called "ordered set aggregate" and "hypothetical
2075 set aggregate" functions, including :class:`.percentile_cont`,
2076 :class:`.rank`, :class:`.dense_rank`, etc. This feature is typically
2077 used by Oracle Database, Microsoft SQL Server.
2079 For generalized ORDER BY of aggregate functions on all included
2080 backends, including PostgreSQL, MySQL/MariaDB, SQLite as well as Oracle
2081 and SQL Server, the :func:`_sql.aggregate_order_by` provides a more
2082 general approach that compiles to "WITHIN GROUP" only on those backends
2083 which require it.
2085 :func:`_expression.within_group` is usually called using
2086 the :meth:`.FunctionElement.within_group` method, e.g.::
2088 stmt = select(
2089 func.percentile_cont(0.5).within_group(department.c.salary.desc()),
2090 )
2092 The above statement would produce SQL similar to
2093 ``SELECT percentile_cont(0.5)
2094 WITHIN GROUP (ORDER BY department.salary DESC)``.
2096 :param element: a :class:`.FunctionElement` construct, typically
2097 generated by :data:`~.expression.func`.
2098 :param \*order_by: one or more column elements that will be used
2099 as the ORDER BY clause of the WITHIN GROUP construct.
2101 .. seealso::
2103 :ref:`tutorial_functions_within_group` - in the
2104 :ref:`unified_tutorial`
2106 :func:`_sql.aggregate_order_by` - helper for PostgreSQL, MySQL,
2107 SQLite aggregate functions
2109 :data:`.expression.func`
2111 :func:`_expression.over`
2113 """
2114 return WithinGroup(element, *order_by)
2117def aggregate_order_by(
2118 element: FunctionElement[_T], *order_by: _ColumnExpressionArgument[Any]
2119) -> AggregateOrderBy[_T]:
2120 r"""Produce a :class:`.AggregateOrderBy` object against a function.
2122 Used for aggregating functions such as :class:`_functions.array_agg`,
2123 ``group_concat``, ``json_agg`` on backends that support ordering via an
2124 embedded ``ORDER BY`` parameter, e.g. PostgreSQL, MySQL/MariaDB, SQLite.
2125 When used on backends like Oracle and SQL Server, SQL compilation uses that
2126 of :class:`.WithinGroup`. On PostgreSQL, compilation is fixed at embedded
2127 ``ORDER BY``; for set aggregation functions where PostgreSQL requires the
2128 use of ``WITHIN GROUP``, :func:`_expression.within_group` should be used
2129 explicitly.
2131 :func:`_expression.aggregate_order_by` is usually called using
2132 the :meth:`.FunctionElement.aggregate_order_by` method, e.g.::
2134 stmt = select(
2135 func.array_agg(department.c.code).aggregate_order_by(
2136 department.c.code.desc()
2137 ),
2138 )
2140 which would produce an expression resembling:
2142 .. sourcecode:: sql
2144 SELECT array_agg(department.code ORDER BY department.code DESC)
2145 AS array_agg_1 FROM department
2147 The ORDER BY argument may also be multiple terms.
2149 When using the backend-agnostic :class:`_functions.aggregate_strings`
2150 string aggregation function, use the
2151 :paramref:`_functions.aggregate_strings.order_by` parameter to indicate a
2152 dialect-agnostic ORDER BY expression.
2154 .. versionadded:: 2.1 Generalized the PostgreSQL-specific
2155 :func:`_postgresql.aggregate_order_by` function to a method on
2156 :class:`.Function` that is backend agnostic.
2158 .. seealso::
2160 :class:`_functions.aggregate_strings` - backend-agnostic string
2161 concatenation function which also supports ORDER BY
2163 """ # noqa: E501
2164 return AggregateOrderBy(element, *order_by)