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 ._typing import _ByArgument
57 from ._typing import _ColumnExpressionArgument
58 from ._typing import _ColumnExpressionOrLiteralArgument
59 from ._typing import _ColumnExpressionOrStrLabelArgument
60 from ._typing import _OnlyColumnArgument
61 from ._typing import _TypeEngineArgument
62 from .elements import _FrameIntTuple
63 from .elements import BinaryExpression
64 from .elements import FrameClause
65 from .selectable import FromClause
66 from .type_api import TypeEngine
67 from ..util.compat import Template
69_T = TypeVar("_T")
72def all_(expr: _ColumnExpressionArgument[_T]) -> CollectionAggregate[bool]:
73 """Produce an ALL expression.
75 For dialects such as that of PostgreSQL, this operator applies
76 to usage of the :class:`_types.ARRAY` datatype, for that of
77 MySQL, it may apply to a subquery. e.g.::
79 # renders on PostgreSQL:
80 # '5 = ALL (somearray)'
81 expr = 5 == all_(mytable.c.somearray)
83 # renders on MySQL:
84 # '5 = ALL (SELECT value FROM table)'
85 expr = 5 == all_(select(table.c.value))
87 Comparison to NULL may work using ``None``::
89 None == all_(mytable.c.somearray)
91 The any_() / all_() operators also feature a special "operand flipping"
92 behavior such that if any_() / all_() are used on the left side of a
93 comparison using a standalone operator such as ``==``, ``!=``, etc.
94 (not including operator methods such as
95 :meth:`_sql.ColumnOperators.is_`) the rendered expression is flipped::
97 # would render '5 = ALL (column)`
98 all_(mytable.c.column) == 5
100 Or with ``None``, which note will not perform
101 the usual step of rendering "IS" as is normally the case for NULL::
103 # would render 'NULL = ALL(somearray)'
104 all_(mytable.c.somearray) == None
106 The column-level :meth:`_sql.ColumnElement.all_` method (not to be
107 confused with the deprecated :class:`_types.ARRAY` level
108 :meth:`_types.ARRAY.Comparator.all`) is shorthand for
109 ``all_(col)``::
111 5 == mytable.c.somearray.all_()
113 .. seealso::
115 :meth:`_sql.ColumnOperators.all_`
117 :func:`_expression.any_`
119 """
120 if isinstance(expr, operators.ColumnOperators):
121 return expr.all_()
122 else:
123 return CollectionAggregate._create_all(expr)
126def and_( # type: ignore[empty-body]
127 initial_clause: Union[Literal[True], _ColumnExpressionArgument[bool]],
128 *clauses: _ColumnExpressionArgument[bool],
129) -> ColumnElement[bool]:
130 r"""Produce a conjunction of expressions joined by ``AND``.
132 E.g.::
134 from sqlalchemy import and_
136 stmt = select(users_table).where(
137 and_(users_table.c.name == "wendy", users_table.c.enrolled == True)
138 )
140 The :func:`.and_` conjunction is also available using the
141 Python ``&`` operator (though note that compound expressions
142 need to be parenthesized in order to function with Python
143 operator precedence behavior)::
145 stmt = select(users_table).where(
146 (users_table.c.name == "wendy") & (users_table.c.enrolled == True)
147 )
149 The :func:`.and_` operation is also implicit in some cases;
150 the :meth:`_expression.Select.where`
151 method for example can be invoked multiple
152 times against a statement, which will have the effect of each
153 clause being combined using :func:`.and_`::
155 stmt = (
156 select(users_table)
157 .where(users_table.c.name == "wendy")
158 .where(users_table.c.enrolled == True)
159 )
161 The :func:`.and_` construct must be given at least one positional
162 argument in order to be valid; a :func:`.and_` construct with no
163 arguments is ambiguous. To produce an "empty" or dynamically
164 generated :func:`.and_` expression, from a given list of expressions,
165 a "default" element of :func:`_sql.true` (or just ``True``) should be
166 specified::
168 from sqlalchemy import true
170 criteria = and_(true(), *expressions)
172 The above expression will compile to SQL as the expression ``true``
173 or ``1 = 1``, depending on backend, if no other expressions are
174 present. If expressions are present, then the :func:`_sql.true` value is
175 ignored as it does not affect the outcome of an AND expression that
176 has other elements.
178 .. deprecated:: 1.4 The :func:`.and_` element now requires that at
179 least one argument is passed; creating the :func:`.and_` construct
180 with no arguments is deprecated, and will emit a deprecation warning
181 while continuing to produce a blank SQL string.
183 .. seealso::
185 :func:`.or_`
187 """
188 ...
191if not TYPE_CHECKING:
192 # handle deprecated case which allows zero-arguments
193 def and_(*clauses): # noqa: F811
194 r"""Produce a conjunction of expressions joined by ``AND``.
196 E.g.::
198 from sqlalchemy import and_
200 stmt = select(users_table).where(
201 and_(users_table.c.name == "wendy", users_table.c.enrolled == True)
202 )
204 The :func:`.and_` conjunction is also available using the
205 Python ``&`` operator (though note that compound expressions
206 need to be parenthesized in order to function with Python
207 operator precedence behavior)::
209 stmt = select(users_table).where(
210 (users_table.c.name == "wendy") & (users_table.c.enrolled == True)
211 )
213 The :func:`.and_` operation is also implicit in some cases;
214 the :meth:`_expression.Select.where`
215 method for example can be invoked multiple
216 times against a statement, which will have the effect of each
217 clause being combined using :func:`.and_`::
219 stmt = (
220 select(users_table)
221 .where(users_table.c.name == "wendy")
222 .where(users_table.c.enrolled == True)
223 )
225 The :func:`.and_` construct must be given at least one positional
226 argument in order to be valid; a :func:`.and_` construct with no
227 arguments is ambiguous. To produce an "empty" or dynamically
228 generated :func:`.and_` expression, from a given list of expressions,
229 a "default" element of :func:`_sql.true` (or just ``True``) should be
230 specified::
232 from sqlalchemy import true
234 criteria = and_(true(), *expressions)
236 The above expression will compile to SQL as the expression ``true``
237 or ``1 = 1``, depending on backend, if no other expressions are
238 present. If expressions are present, then the :func:`_sql.true` value
239 is ignored as it does not affect the outcome of an AND expression that
240 has other elements.
242 .. deprecated:: 1.4 The :func:`.and_` element now requires that at
243 least one argument is passed; creating the :func:`.and_` construct
244 with no arguments is deprecated, and will emit a deprecation warning
245 while continuing to produce a blank SQL string.
247 .. seealso::
249 :func:`.or_`
251 """ # noqa: E501
252 return BooleanClauseList.and_(*clauses)
255def any_(expr: _ColumnExpressionArgument[_T]) -> CollectionAggregate[bool]:
256 """Produce an ANY expression.
258 For dialects such as that of PostgreSQL, this operator applies
259 to usage of the :class:`_types.ARRAY` datatype, for that of
260 MySQL, it may apply to a subquery. e.g.::
262 # renders on PostgreSQL:
263 # '5 = ANY (somearray)'
264 expr = 5 == any_(mytable.c.somearray)
266 # renders on MySQL:
267 # '5 = ANY (SELECT value FROM table)'
268 expr = 5 == any_(select(table.c.value))
270 Comparison to NULL may work using ``None`` or :func:`_sql.null`::
272 None == any_(mytable.c.somearray)
274 The any_() / all_() operators also feature a special "operand flipping"
275 behavior such that if any_() / all_() are used on the left side of a
276 comparison using a standalone operator such as ``==``, ``!=``, etc.
277 (not including operator methods such as
278 :meth:`_sql.ColumnOperators.is_`) the rendered expression is flipped::
280 # would render '5 = ANY (column)`
281 any_(mytable.c.column) == 5
283 Or with ``None``, which note will not perform
284 the usual step of rendering "IS" as is normally the case for NULL::
286 # would render 'NULL = ANY(somearray)'
287 any_(mytable.c.somearray) == None
289 The column-level :meth:`_sql.ColumnElement.any_` method (not to be
290 confused with the deprecated :class:`_types.ARRAY` level
291 :meth:`_types.ARRAY.Comparator.any`) is shorthand for
292 ``any_(col)``::
294 5 = mytable.c.somearray.any_()
296 .. seealso::
298 :meth:`_sql.ColumnOperators.any_`
300 :func:`_expression.all_`
302 """
303 if isinstance(expr, operators.ColumnOperators):
304 return expr.any_()
305 else:
306 return CollectionAggregate._create_any(expr)
309@overload
310def asc(
311 column: Union[str, "ColumnElement[_T]"],
312) -> UnaryExpression[_T]: ...
315@overload
316def asc(
317 column: _ColumnExpressionOrStrLabelArgument[_T],
318) -> Union[OrderByList, UnaryExpression[_T]]: ...
321def asc(
322 column: _ColumnExpressionOrStrLabelArgument[_T],
323) -> Union[OrderByList, UnaryExpression[_T]]:
324 """Produce an ascending ``ORDER BY`` clause element.
326 e.g.::
328 from sqlalchemy import asc
330 stmt = select(users_table).order_by(asc(users_table.c.name))
332 will produce SQL as:
334 .. sourcecode:: sql
336 SELECT id, name FROM user ORDER BY name ASC
338 The :func:`.asc` function is a standalone version of the
339 :meth:`_expression.ColumnElement.asc`
340 method available on all SQL expressions,
341 e.g.::
344 stmt = select(users_table).order_by(users_table.c.name.asc())
346 :param column: A :class:`_expression.ColumnElement` (e.g.
347 scalar SQL expression)
348 with which to apply the :func:`.asc` operation.
350 .. seealso::
352 :func:`.desc`
354 :func:`.nulls_first`
356 :func:`.nulls_last`
358 :meth:`_expression.Select.order_by`
360 """
362 if isinstance(column, operators.OrderingOperators):
363 return column.asc() # type: ignore[unused-ignore]
364 else:
365 return UnaryExpression._create_asc(column)
368def collate(
369 expression: _ColumnExpressionArgument[str], collation: str
370) -> BinaryExpression[str]:
371 """Return the clause ``expression COLLATE collation``.
373 e.g.::
375 collate(mycolumn, "utf8_bin")
377 produces:
379 .. sourcecode:: sql
381 mycolumn COLLATE utf8_bin
383 The collation expression is also quoted if it is a case sensitive
384 identifier, e.g. contains uppercase characters.
386 """
387 if isinstance(expression, operators.ColumnOperators):
388 return expression.collate(collation) # type: ignore
389 else:
390 return CollationClause._create_collation_expression(
391 expression, collation
392 )
395def between(
396 expr: _ColumnExpressionOrLiteralArgument[_T],
397 lower_bound: Any,
398 upper_bound: Any,
399 symmetric: bool = False,
400) -> BinaryExpression[bool]:
401 """Produce a ``BETWEEN`` predicate clause.
403 E.g.::
405 from sqlalchemy import between
407 stmt = select(users_table).where(between(users_table.c.id, 5, 7))
409 Would produce SQL resembling:
411 .. sourcecode:: sql
413 SELECT id, name FROM user WHERE id BETWEEN :id_1 AND :id_2
415 The :func:`.between` function is a standalone version of the
416 :meth:`_expression.ColumnElement.between` method available on all
417 SQL expressions, as in::
419 stmt = select(users_table).where(users_table.c.id.between(5, 7))
421 All arguments passed to :func:`.between`, including the left side
422 column expression, are coerced from Python scalar values if a
423 the value is not a :class:`_expression.ColumnElement` subclass.
424 For example,
425 three fixed values can be compared as in::
427 print(between(5, 3, 7))
429 Which would produce::
431 :param_1 BETWEEN :param_2 AND :param_3
433 :param expr: a column expression, typically a
434 :class:`_expression.ColumnElement`
435 instance or alternatively a Python scalar expression to be coerced
436 into a column expression, serving as the left side of the ``BETWEEN``
437 expression.
439 :param lower_bound: a column or Python scalar expression serving as the
440 lower bound of the right side of the ``BETWEEN`` expression.
442 :param upper_bound: a column or Python scalar expression serving as the
443 upper bound of the right side of the ``BETWEEN`` expression.
445 :param symmetric: if True, will render " BETWEEN SYMMETRIC ". Note
446 that not all databases support this syntax.
448 .. seealso::
450 :meth:`_expression.ColumnElement.between`
452 """
453 col_expr = coercions.expect(roles.ExpressionElementRole, expr)
454 return col_expr.between(lower_bound, upper_bound, symmetric=symmetric)
457def outparam(
458 key: str, type_: Optional[TypeEngine[_T]] = None
459) -> BindParameter[_T]:
460 """Create an 'OUT' parameter for usage in functions (stored procedures),
461 for databases which support them.
463 The ``outparam`` can be used like a regular function parameter.
464 The "output" value will be available from the
465 :class:`~sqlalchemy.engine.CursorResult` object via its ``out_parameters``
466 attribute, which returns a dictionary containing the values.
468 """
469 return BindParameter(key, None, type_=type_, unique=False, isoutparam=True)
472@overload
473def not_(clause: BinaryExpression[_T]) -> BinaryExpression[_T]: ...
476@overload
477def not_(clause: _ColumnExpressionArgument[_T]) -> ColumnElement[_T]: ...
480def not_(clause: _ColumnExpressionArgument[_T]) -> ColumnElement[_T]:
481 """Return a negation of the given clause, i.e. ``NOT(clause)``.
483 The ``~`` operator is also overloaded on all
484 :class:`_expression.ColumnElement` subclasses to produce the
485 same result.
487 """
489 return coercions.expect(roles.ExpressionElementRole, clause).__invert__()
492def from_dml_column(column: _OnlyColumnArgument[_T]) -> DMLTargetCopy[_T]:
493 r"""A placeholder that may be used in compiled INSERT or UPDATE expressions
494 to refer to the SQL expression or value being applied to another column.
496 Given a table such as::
498 t = Table(
499 "t",
500 MetaData(),
501 Column("x", Integer),
502 Column("y", Integer),
503 )
505 The :func:`_sql.from_dml_column` construct allows automatic copying
506 of an expression assigned to a different column to be reused::
508 >>> stmt = t.insert().values(x=func.foobar(3), y=from_dml_column(t.c.x) + 5)
509 >>> print(stmt)
510 INSERT INTO t (x, y) VALUES (foobar(:foobar_1), (foobar(:foobar_1) + :param_1))
512 The :func:`_sql.from_dml_column` construct is intended to be useful primarily
513 with event-based hooks such as those used by ORM hybrids.
515 .. seealso::
517 :ref:`hybrid_bulk_update`
519 .. versionadded:: 2.1
522 """ # noqa: E501
524 return DMLTargetCopy(column)
527def bindparam(
528 key: Optional[str],
529 value: Any = _NoArg.NO_ARG,
530 type_: Optional[_TypeEngineArgument[_T]] = None,
531 unique: bool = False,
532 required: Union[bool, Literal[_NoArg.NO_ARG]] = _NoArg.NO_ARG,
533 quote: Optional[bool] = None,
534 callable_: Optional[Callable[[], Any]] = None,
535 expanding: bool = False,
536 isoutparam: bool = False,
537 literal_execute: bool = False,
538) -> BindParameter[_T]:
539 r"""Produce a "bound expression".
541 The return value is an instance of :class:`.BindParameter`; this
542 is a :class:`_expression.ColumnElement`
543 subclass which represents a so-called
544 "placeholder" value in a SQL expression, the value of which is
545 supplied at the point at which the statement in executed against a
546 database connection.
548 In SQLAlchemy, the :func:`.bindparam` construct has
549 the ability to carry along the actual value that will be ultimately
550 used at expression time. In this way, it serves not just as
551 a "placeholder" for eventual population, but also as a means of
552 representing so-called "unsafe" values which should not be rendered
553 directly in a SQL statement, but rather should be passed along
554 to the :term:`DBAPI` as values which need to be correctly escaped
555 and potentially handled for type-safety.
557 When using :func:`.bindparam` explicitly, the use case is typically
558 one of traditional deferment of parameters; the :func:`.bindparam`
559 construct accepts a name which can then be referred to at execution
560 time::
562 from sqlalchemy import bindparam
564 stmt = select(users_table).where(
565 users_table.c.name == bindparam("username")
566 )
568 The above statement, when rendered, will produce SQL similar to:
570 .. sourcecode:: sql
572 SELECT id, name FROM user WHERE name = :username
574 In order to populate the value of ``:username`` above, the value
575 would typically be applied at execution time to a method
576 like :meth:`_engine.Connection.execute`::
578 result = connection.execute(stmt, {"username": "wendy"})
580 Explicit use of :func:`.bindparam` is also common when producing
581 UPDATE or DELETE statements that are to be invoked multiple times,
582 where the WHERE criterion of the statement is to change on each
583 invocation, such as::
585 stmt = (
586 users_table.update()
587 .where(user_table.c.name == bindparam("username"))
588 .values(fullname=bindparam("fullname"))
589 )
591 connection.execute(
592 stmt,
593 [
594 {"username": "wendy", "fullname": "Wendy Smith"},
595 {"username": "jack", "fullname": "Jack Jones"},
596 ],
597 )
599 SQLAlchemy's Core expression system makes wide use of
600 :func:`.bindparam` in an implicit sense. It is typical that Python
601 literal values passed to virtually all SQL expression functions are
602 coerced into fixed :func:`.bindparam` constructs. For example, given
603 a comparison operation such as::
605 expr = users_table.c.name == "Wendy"
607 The above expression will produce a :class:`.BinaryExpression`
608 construct, where the left side is the :class:`_schema.Column` object
609 representing the ``name`` column, and the right side is a
610 :class:`.BindParameter` representing the literal value::
612 print(repr(expr.right))
613 BindParameter("%(4327771088 name)s", "Wendy", type_=String())
615 The expression above will render SQL such as:
617 .. sourcecode:: sql
619 user.name = :name_1
621 Where the ``:name_1`` parameter name is an anonymous name. The
622 actual string ``Wendy`` is not in the rendered string, but is carried
623 along where it is later used within statement execution. If we
624 invoke a statement like the following::
626 stmt = select(users_table).where(users_table.c.name == "Wendy")
627 result = connection.execute(stmt)
629 We would see SQL logging output as:
631 .. sourcecode:: sql
633 SELECT "user".id, "user".name
634 FROM "user"
635 WHERE "user".name = %(name_1)s
636 {'name_1': 'Wendy'}
638 Above, we see that ``Wendy`` is passed as a parameter to the database,
639 while the placeholder ``:name_1`` is rendered in the appropriate form
640 for the target database, in this case the PostgreSQL database.
642 Similarly, :func:`.bindparam` is invoked automatically when working
643 with :term:`CRUD` statements as far as the "VALUES" portion is
644 concerned. The :func:`_expression.insert` construct produces an
645 ``INSERT`` expression which will, at statement execution time, generate
646 bound placeholders based on the arguments passed, as in::
648 stmt = users_table.insert()
649 result = connection.execute(stmt, {"name": "Wendy"})
651 The above will produce SQL output as:
653 .. sourcecode:: sql
655 INSERT INTO "user" (name) VALUES (%(name)s)
656 {'name': 'Wendy'}
658 The :class:`_expression.Insert` construct, at
659 compilation/execution time, rendered a single :func:`.bindparam`
660 mirroring the column name ``name`` as a result of the single ``name``
661 parameter we passed to the :meth:`_engine.Connection.execute` method.
663 :param key:
664 the key (e.g. the name) for this bind param.
665 Will be used in the generated
666 SQL statement for dialects that use named parameters. This
667 value may be modified when part of a compilation operation,
668 if other :class:`BindParameter` objects exist with the same
669 key, or if its length is too long and truncation is
670 required.
672 If omitted, an "anonymous" name is generated for the bound parameter;
673 when given a value to bind, the end result is equivalent to calling upon
674 the :func:`.literal` function with a value to bind, particularly
675 if the :paramref:`.bindparam.unique` parameter is also provided.
677 :param value:
678 Initial value for this bind param. Will be used at statement
679 execution time as the value for this parameter passed to the
680 DBAPI, if no other value is indicated to the statement execution
681 method for this particular parameter name. Defaults to ``None``.
683 :param callable\_:
684 A callable function that takes the place of "value". The function
685 will be called at statement execution time to determine the
686 ultimate value. Used for scenarios where the actual bind
687 value cannot be determined at the point at which the clause
688 construct is created, but embedded bind values are still desirable.
690 :param type\_:
691 A :class:`.TypeEngine` class or instance representing an optional
692 datatype for this :func:`.bindparam`. If not passed, a type
693 may be determined automatically for the bind, based on the given
694 value; for example, trivial Python types such as ``str``,
695 ``int``, ``bool``
696 may result in the :class:`.String`, :class:`.Integer` or
697 :class:`.Boolean` types being automatically selected.
699 The type of a :func:`.bindparam` is significant especially in that
700 the type will apply pre-processing to the value before it is
701 passed to the database. For example, a :func:`.bindparam` which
702 refers to a datetime value, and is specified as holding the
703 :class:`.DateTime` type, may apply conversion needed to the
704 value (such as stringification on SQLite) before passing the value
705 to the database.
707 :param unique:
708 if True, the key name of this :class:`.BindParameter` will be
709 modified if another :class:`.BindParameter` of the same name
710 already has been located within the containing
711 expression. This flag is used generally by the internals
712 when producing so-called "anonymous" bound expressions, it
713 isn't generally applicable to explicitly-named :func:`.bindparam`
714 constructs.
716 :param required:
717 If ``True``, a value is required at execution time. If not passed,
718 it defaults to ``True`` if neither :paramref:`.bindparam.value`
719 or :paramref:`.bindparam.callable` were passed. If either of these
720 parameters are present, then :paramref:`.bindparam.required`
721 defaults to ``False``.
723 :param quote:
724 True if this parameter name requires quoting and is not
725 currently known as a SQLAlchemy reserved word; this currently
726 only applies to the Oracle Database backends, where bound names must
727 sometimes be quoted.
729 :param isoutparam:
730 if True, the parameter should be treated like a stored procedure
731 "OUT" parameter. This applies to backends such as Oracle Database which
732 support OUT parameters.
734 :param expanding:
735 if True, this parameter will be treated as an "expanding" parameter
736 at execution time; the parameter value is expected to be a sequence,
737 rather than a scalar value, and the string SQL statement will
738 be transformed on a per-execution basis to accommodate the sequence
739 with a variable number of parameter slots passed to the DBAPI.
740 This is to allow statement caching to be used in conjunction with
741 an IN clause.
743 .. seealso::
745 :meth:`.ColumnOperators.in_`
747 :ref:`baked_in` - with baked queries
749 .. note:: The "expanding" feature does not support "executemany"-
750 style parameter sets.
752 :param literal_execute:
753 if True, the bound parameter will be rendered in the compile phase
754 with a special "POSTCOMPILE" token, and the SQLAlchemy compiler will
755 render the final value of the parameter into the SQL statement at
756 statement execution time, omitting the value from the parameter
757 dictionary / list passed to DBAPI ``cursor.execute()``. This
758 produces a similar effect as that of using the ``literal_binds``,
759 compilation flag, however takes place as the statement is sent to
760 the DBAPI ``cursor.execute()`` method, rather than when the statement
761 is compiled. The primary use of this
762 capability is for rendering LIMIT / OFFSET clauses for database
763 drivers that can't accommodate for bound parameters in these
764 contexts, while allowing SQL constructs to be cacheable at the
765 compilation level.
767 .. versionadded:: 1.4 Added "post compile" bound parameters
769 .. seealso::
771 :ref:`change_4808`.
773 .. seealso::
775 :ref:`tutorial_sending_parameters` - in the
776 :ref:`unified_tutorial`
779 """
780 return BindParameter(
781 key,
782 value,
783 type_,
784 unique,
785 required,
786 quote,
787 callable_,
788 expanding,
789 isoutparam,
790 literal_execute,
791 )
794def case(
795 *whens: Union[
796 typing_Tuple[_ColumnExpressionArgument[bool], Any], Mapping[Any, Any]
797 ],
798 value: Optional[Any] = None,
799 else_: Optional[Any] = None,
800) -> Case[Any]:
801 r"""Produce a ``CASE`` expression.
803 The ``CASE`` construct in SQL is a conditional object that
804 acts somewhat analogously to an "if/then" construct in other
805 languages. It returns an instance of :class:`.Case`.
807 :func:`.case` in its usual form is passed a series of "when"
808 constructs, that is, a list of conditions and results as tuples::
810 from sqlalchemy import case
812 stmt = select(users_table).where(
813 case(
814 (users_table.c.name == "wendy", "W"),
815 (users_table.c.name == "jack", "J"),
816 else_="E",
817 )
818 )
820 The above statement will produce SQL resembling:
822 .. sourcecode:: sql
824 SELECT id, name FROM user
825 WHERE CASE
826 WHEN (name = :name_1) THEN :param_1
827 WHEN (name = :name_2) THEN :param_2
828 ELSE :param_3
829 END
831 When simple equality expressions of several values against a single
832 parent column are needed, :func:`.case` also has a "shorthand" format
833 used via the
834 :paramref:`.case.value` parameter, which is passed a column
835 expression to be compared. In this form, the :paramref:`.case.whens`
836 parameter is passed as a dictionary containing expressions to be
837 compared against keyed to result expressions. The statement below is
838 equivalent to the preceding statement::
840 stmt = select(users_table).where(
841 case({"wendy": "W", "jack": "J"}, value=users_table.c.name, else_="E")
842 )
844 The values which are accepted as result values in
845 :paramref:`.case.whens` as well as with :paramref:`.case.else_` are
846 coerced from Python literals into :func:`.bindparam` constructs.
847 SQL expressions, e.g. :class:`_expression.ColumnElement` constructs,
848 are accepted
849 as well. To coerce a literal string expression into a constant
850 expression rendered inline, use the :func:`_expression.literal_column`
851 construct,
852 as in::
854 from sqlalchemy import case, literal_column
856 case(
857 (orderline.c.qty > 100, literal_column("'greaterthan100'")),
858 (orderline.c.qty > 10, literal_column("'greaterthan10'")),
859 else_=literal_column("'lessthan10'"),
860 )
862 The above will render the given constants without using bound
863 parameters for the result values (but still for the comparison
864 values), as in:
866 .. sourcecode:: sql
868 CASE
869 WHEN (orderline.qty > :qty_1) THEN 'greaterthan100'
870 WHEN (orderline.qty > :qty_2) THEN 'greaterthan10'
871 ELSE 'lessthan10'
872 END
874 :param \*whens: The criteria to be compared against,
875 :paramref:`.case.whens` accepts two different forms, based on
876 whether or not :paramref:`.case.value` is used.
878 .. versionchanged:: 1.4 the :func:`_sql.case`
879 function now accepts the series of WHEN conditions positionally
881 In the first form, it accepts multiple 2-tuples passed as positional
882 arguments; each 2-tuple consists of ``(<sql expression>, <value>)``,
883 where the SQL expression is a boolean expression and "value" is a
884 resulting value, e.g.::
886 case(
887 (users_table.c.name == "wendy", "W"),
888 (users_table.c.name == "jack", "J"),
889 )
891 In the second form, it accepts a Python dictionary of comparison
892 values mapped to a resulting value; this form requires
893 :paramref:`.case.value` to be present, and values will be compared
894 using the ``==`` operator, e.g.::
896 case({"wendy": "W", "jack": "J"}, value=users_table.c.name)
898 :param value: An optional SQL expression which will be used as a
899 fixed "comparison point" for candidate values within a dictionary
900 passed to :paramref:`.case.whens`.
902 :param else\_: An optional SQL expression which will be the evaluated
903 result of the ``CASE`` construct if all expressions within
904 :paramref:`.case.whens` evaluate to false. When omitted, most
905 databases will produce a result of NULL if none of the "when"
906 expressions evaluate to true.
909 """ # noqa: E501
910 return Case(*whens, value=value, else_=else_)
913def cast(
914 expression: _ColumnExpressionOrLiteralArgument[Any],
915 type_: _TypeEngineArgument[_T],
916) -> Cast[_T]:
917 r"""Produce a ``CAST`` expression.
919 :func:`.cast` returns an instance of :class:`.Cast`.
921 E.g.::
923 from sqlalchemy import cast, Numeric
925 stmt = select(cast(product_table.c.unit_price, Numeric(10, 4)))
927 The above statement will produce SQL resembling:
929 .. sourcecode:: sql
931 SELECT CAST(unit_price AS NUMERIC(10, 4)) FROM product
933 The :func:`.cast` function performs two distinct functions when
934 used. The first is that it renders the ``CAST`` expression within
935 the resulting SQL string. The second is that it associates the given
936 type (e.g. :class:`.TypeEngine` class or instance) with the column
937 expression on the Python side, which means the expression will take
938 on the expression operator behavior associated with that type,
939 as well as the bound-value handling and result-row-handling behavior
940 of the type.
942 An alternative to :func:`.cast` is the :func:`.type_coerce` function.
943 This function performs the second task of associating an expression
944 with a specific type, but does not render the ``CAST`` expression
945 in SQL.
947 :param expression: A SQL expression, such as a
948 :class:`_expression.ColumnElement`
949 expression or a Python string which will be coerced into a bound
950 literal value.
952 :param type\_: A :class:`.TypeEngine` class or instance indicating
953 the type to which the ``CAST`` should apply.
955 .. seealso::
957 :ref:`tutorial_casts`
959 :func:`.try_cast` - an alternative to CAST that results in
960 NULLs when the cast fails, instead of raising an error.
961 Only supported by some dialects.
963 :func:`.type_coerce` - an alternative to CAST that coerces the type
964 on the Python side only, which is often sufficient to generate the
965 correct SQL and data coercion.
968 """
969 return Cast(expression, type_)
972def try_cast(
973 expression: _ColumnExpressionOrLiteralArgument[Any],
974 type_: _TypeEngineArgument[_T],
975) -> TryCast[_T]:
976 """Produce a ``TRY_CAST`` expression for backends which support it;
977 this is a ``CAST`` which returns NULL for un-castable conversions.
979 In SQLAlchemy, this construct is supported **only** by the SQL Server
980 dialect, and will raise a :class:`.CompileError` if used on other
981 included backends. However, third party backends may also support
982 this construct.
984 .. tip:: As :func:`_sql.try_cast` originates from the SQL Server dialect,
985 it's importable both from ``sqlalchemy.`` as well as from
986 ``sqlalchemy.dialects.mssql``.
988 :func:`_sql.try_cast` returns an instance of :class:`.TryCast` and
989 generally behaves similarly to the :class:`.Cast` construct;
990 at the SQL level, the difference between ``CAST`` and ``TRY_CAST``
991 is that ``TRY_CAST`` returns NULL for an un-castable expression,
992 such as attempting to cast a string ``"hi"`` to an integer value.
994 E.g.::
996 from sqlalchemy import select, try_cast, Numeric
998 stmt = select(try_cast(product_table.c.unit_price, Numeric(10, 4)))
1000 The above would render on Microsoft SQL Server as:
1002 .. sourcecode:: sql
1004 SELECT TRY_CAST (product_table.unit_price AS NUMERIC(10, 4))
1005 FROM product_table
1007 .. versionadded:: 2.0.14 :func:`.try_cast` has been
1008 generalized from the SQL Server dialect into a general use
1009 construct that may be supported by additional dialects.
1011 """
1012 return TryCast(expression, type_)
1015def column(
1016 text: str,
1017 type_: Optional[_TypeEngineArgument[_T]] = None,
1018 is_literal: bool = False,
1019 _selectable: Optional[FromClause] = None,
1020) -> ColumnClause[_T]:
1021 """Produce a :class:`.ColumnClause` object.
1023 The :class:`.ColumnClause` is a lightweight analogue to the
1024 :class:`_schema.Column` class. The :func:`_expression.column`
1025 function can
1026 be invoked with just a name alone, as in::
1028 from sqlalchemy import column
1030 id, name = column("id"), column("name")
1031 stmt = select(id, name).select_from("user")
1033 The above statement would produce SQL like:
1035 .. sourcecode:: sql
1037 SELECT id, name FROM user
1039 Once constructed, :func:`_expression.column`
1040 may be used like any other SQL
1041 expression element such as within :func:`_expression.select`
1042 constructs::
1044 from sqlalchemy.sql import column
1046 id, name = column("id"), column("name")
1047 stmt = select(id, name).select_from("user")
1049 The text handled by :func:`_expression.column`
1050 is assumed to be handled
1051 like the name of a database column; if the string contains mixed case,
1052 special characters, or matches a known reserved word on the target
1053 backend, the column expression will render using the quoting
1054 behavior determined by the backend. To produce a textual SQL
1055 expression that is rendered exactly without any quoting,
1056 use :func:`_expression.literal_column` instead,
1057 or pass ``True`` as the
1058 value of :paramref:`_expression.column.is_literal`. Additionally,
1059 full SQL
1060 statements are best handled using the :func:`_expression.text`
1061 construct.
1063 :func:`_expression.column` can be used in a table-like
1064 fashion by combining it with the :func:`.table` function
1065 (which is the lightweight analogue to :class:`_schema.Table`
1066 ) to produce
1067 a working table construct with minimal boilerplate::
1069 from sqlalchemy import table, column, select
1071 user = table(
1072 "user",
1073 column("id"),
1074 column("name"),
1075 column("description"),
1076 )
1078 stmt = select(user.c.description).where(user.c.name == "wendy")
1080 A :func:`_expression.column` / :func:`.table`
1081 construct like that illustrated
1082 above can be created in an
1083 ad-hoc fashion and is not associated with any
1084 :class:`_schema.MetaData`, DDL, or events, unlike its
1085 :class:`_schema.Table` counterpart.
1087 :param text: the text of the element.
1089 :param type: :class:`_types.TypeEngine` object which can associate
1090 this :class:`.ColumnClause` with a type.
1092 :param is_literal: if True, the :class:`.ColumnClause` is assumed to
1093 be an exact expression that will be delivered to the output with no
1094 quoting rules applied regardless of case sensitive settings. the
1095 :func:`_expression.literal_column()` function essentially invokes
1096 :func:`_expression.column` while passing ``is_literal=True``.
1098 .. seealso::
1100 :class:`_schema.Column`
1102 :func:`_expression.literal_column`
1104 :func:`.table`
1106 :func:`_expression.text`
1108 :ref:`tutorial_select_arbitrary_text`
1110 """
1111 return ColumnClause(text, type_, is_literal, _selectable)
1114@overload
1115def desc(
1116 column: Union[str, "ColumnElement[_T]"],
1117) -> UnaryExpression[_T]: ...
1120@overload
1121def desc(
1122 column: _ColumnExpressionOrStrLabelArgument[_T],
1123) -> Union[OrderByList, UnaryExpression[_T]]: ...
1126def desc(
1127 column: _ColumnExpressionOrStrLabelArgument[_T],
1128) -> Union[OrderByList, UnaryExpression[_T]]:
1129 """Produce a descending ``ORDER BY`` clause element.
1131 e.g.::
1133 from sqlalchemy import desc
1135 stmt = select(users_table).order_by(desc(users_table.c.name))
1137 will produce SQL as:
1139 .. sourcecode:: sql
1141 SELECT id, name FROM user ORDER BY name DESC
1143 The :func:`.desc` function is a standalone version of the
1144 :meth:`_expression.ColumnElement.desc`
1145 method available on all SQL expressions,
1146 e.g.::
1149 stmt = select(users_table).order_by(users_table.c.name.desc())
1151 :param column: A :class:`_expression.ColumnElement` (e.g.
1152 scalar SQL expression)
1153 with which to apply the :func:`.desc` operation.
1155 .. seealso::
1157 :func:`.asc`
1159 :func:`.nulls_first`
1161 :func:`.nulls_last`
1163 :meth:`_expression.Select.order_by`
1165 """
1166 if isinstance(column, operators.OrderingOperators):
1167 return column.desc() # type: ignore[unused-ignore]
1168 else:
1169 return UnaryExpression._create_desc(column)
1172def distinct(expr: _ColumnExpressionArgument[_T]) -> UnaryExpression[_T]:
1173 """Produce an column-expression-level unary ``DISTINCT`` clause.
1175 This applies the ``DISTINCT`` keyword to an **individual column
1176 expression** (e.g. not the whole statement), and renders **specifically
1177 in that column position**; this is used for containment within
1178 an aggregate function, as in::
1180 from sqlalchemy import distinct, func
1182 stmt = select(users_table.c.id, func.count(distinct(users_table.c.name)))
1184 The above would produce an statement resembling:
1186 .. sourcecode:: sql
1188 SELECT user.id, count(DISTINCT user.name) FROM user
1190 .. tip:: The :func:`_sql.distinct` function does **not** apply DISTINCT
1191 to the full SELECT statement, instead applying a DISTINCT modifier
1192 to **individual column expressions**. For general ``SELECT DISTINCT``
1193 support, use the
1194 :meth:`_sql.Select.distinct` method on :class:`_sql.Select`.
1196 The :func:`.distinct` function is also available as a column-level
1197 method, e.g. :meth:`_expression.ColumnElement.distinct`, as in::
1199 stmt = select(func.count(users_table.c.name.distinct()))
1201 The :func:`.distinct` operator is different from the
1202 :meth:`_expression.Select.distinct` method of
1203 :class:`_expression.Select`,
1204 which produces a ``SELECT`` statement
1205 with ``DISTINCT`` applied to the result set as a whole,
1206 e.g. a ``SELECT DISTINCT`` expression. See that method for further
1207 information.
1209 .. seealso::
1211 :meth:`_expression.ColumnElement.distinct`
1213 :meth:`_expression.Select.distinct`
1215 :data:`.func`
1217 """ # noqa: E501
1218 if isinstance(expr, operators.ColumnOperators):
1219 return expr.distinct()
1220 else:
1221 return UnaryExpression._create_distinct(expr)
1224def bitwise_not(expr: _ColumnExpressionArgument[_T]) -> UnaryExpression[_T]:
1225 """Produce a unary bitwise NOT clause, typically via the ``~`` operator.
1227 Not to be confused with boolean negation :func:`_sql.not_`.
1229 .. versionadded:: 2.0.2
1231 .. seealso::
1233 :ref:`operators_bitwise`
1236 """
1237 if isinstance(expr, operators.ColumnOperators):
1238 return expr.bitwise_not()
1239 else:
1240 return UnaryExpression._create_bitwise_not(expr)
1243def extract(field: str, expr: _ColumnExpressionArgument[Any]) -> Extract:
1244 """Return a :class:`.Extract` construct.
1246 This is typically available as :func:`.extract`
1247 as well as ``func.extract`` from the
1248 :data:`.func` namespace.
1250 :param field: The field to extract.
1252 .. warning:: This field is used as a literal SQL string.
1253 **DO NOT PASS UNTRUSTED INPUT TO THIS STRING**.
1255 :param expr: A column or Python scalar expression serving as the
1256 right side of the ``EXTRACT`` expression.
1258 E.g.::
1260 from sqlalchemy import extract
1261 from sqlalchemy import table, column
1263 logged_table = table(
1264 "user",
1265 column("id"),
1266 column("date_created"),
1267 )
1269 stmt = select(logged_table.c.id).where(
1270 extract("YEAR", logged_table.c.date_created) == 2021
1271 )
1273 In the above example, the statement is used to select ids from the
1274 database where the ``YEAR`` component matches a specific value.
1276 Similarly, one can also select an extracted component::
1278 stmt = select(extract("YEAR", logged_table.c.date_created)).where(
1279 logged_table.c.id == 1
1280 )
1282 The implementation of ``EXTRACT`` may vary across database backends.
1283 Users are reminded to consult their database documentation.
1284 """
1285 return Extract(field, expr)
1288def false() -> False_:
1289 """Return a :class:`.False_` construct.
1291 E.g.:
1293 .. sourcecode:: pycon+sql
1295 >>> from sqlalchemy import false
1296 >>> print(select(t.c.x).where(false()))
1297 {printsql}SELECT x FROM t WHERE false
1299 A backend which does not support true/false constants will render as
1300 an expression against 1 or 0:
1302 .. sourcecode:: pycon+sql
1304 >>> print(select(t.c.x).where(false()))
1305 {printsql}SELECT x FROM t WHERE 0 = 1
1307 The :func:`.true` and :func:`.false` constants also feature
1308 "short circuit" operation within an :func:`.and_` or :func:`.or_`
1309 conjunction:
1311 .. sourcecode:: pycon+sql
1313 >>> print(select(t.c.x).where(or_(t.c.x > 5, true())))
1314 {printsql}SELECT x FROM t WHERE true{stop}
1316 >>> print(select(t.c.x).where(and_(t.c.x > 5, false())))
1317 {printsql}SELECT x FROM t WHERE false{stop}
1319 .. seealso::
1321 :func:`.true`
1323 """
1325 return False_._instance()
1328def funcfilter(
1329 func: FunctionElement[_T], *criterion: _ColumnExpressionArgument[bool]
1330) -> FunctionFilter[_T]:
1331 """Produce a :class:`.FunctionFilter` object against a function.
1333 Used against aggregate and window functions,
1334 for database backends that support the "FILTER" clause.
1336 E.g.::
1338 from sqlalchemy import funcfilter
1340 funcfilter(func.count(1), MyClass.name == "some name")
1342 Would produce "COUNT(1) FILTER (WHERE myclass.name = 'some name')".
1344 This function is also available from the :data:`~.expression.func`
1345 construct itself via the :meth:`.FunctionElement.filter` method.
1347 .. seealso::
1349 :ref:`tutorial_functions_within_group` - in the
1350 :ref:`unified_tutorial`
1352 :meth:`.FunctionElement.filter`
1354 """
1355 return FunctionFilter(func, *criterion)
1358def label(
1359 name: str,
1360 element: _ColumnExpressionArgument[_T],
1361 type_: Optional[_TypeEngineArgument[_T]] = None,
1362) -> Label[_T]:
1363 """Return a :class:`Label` object for the
1364 given :class:`_expression.ColumnElement`.
1366 A label changes the name of an element in the columns clause of a
1367 ``SELECT`` statement, typically via the ``AS`` SQL keyword.
1369 This functionality is more conveniently available via the
1370 :meth:`_expression.ColumnElement.label` method on
1371 :class:`_expression.ColumnElement`.
1373 :param name: label name
1375 :param obj: a :class:`_expression.ColumnElement`.
1377 """
1378 return Label(name, element, type_)
1381def null() -> Null:
1382 """Return a constant :class:`.Null` construct."""
1384 return Null._instance()
1387@overload
1388def nulls_first(
1389 column: "ColumnElement[_T]",
1390) -> UnaryExpression[_T]: ...
1393@overload
1394def nulls_first(
1395 column: _ColumnExpressionArgument[_T],
1396) -> Union[OrderByList, UnaryExpression[_T]]: ...
1399def nulls_first(
1400 column: _ColumnExpressionArgument[_T],
1401) -> Union[OrderByList, UnaryExpression[_T]]:
1402 """Produce the ``NULLS FIRST`` modifier for an ``ORDER BY`` expression.
1404 :func:`.nulls_first` is intended to modify the expression produced
1405 by :func:`.asc` or :func:`.desc`, and indicates how NULL values
1406 should be handled when they are encountered during ordering::
1409 from sqlalchemy import desc, nulls_first
1411 stmt = select(users_table).order_by(nulls_first(desc(users_table.c.name)))
1413 The SQL expression from the above would resemble:
1415 .. sourcecode:: sql
1417 SELECT id, name FROM user ORDER BY name DESC NULLS FIRST
1419 Like :func:`.asc` and :func:`.desc`, :func:`.nulls_first` is typically
1420 invoked from the column expression itself using
1421 :meth:`_expression.ColumnElement.nulls_first`,
1422 rather than as its standalone
1423 function version, as in::
1425 stmt = select(users_table).order_by(
1426 users_table.c.name.desc().nulls_first()
1427 )
1429 .. versionchanged:: 1.4 :func:`.nulls_first` is renamed from
1430 :func:`.nullsfirst` in previous releases.
1431 The previous name remains available for backwards compatibility.
1433 .. seealso::
1435 :func:`.asc`
1437 :func:`.desc`
1439 :func:`.nulls_last`
1441 :meth:`_expression.Select.order_by`
1443 """ # noqa: E501
1444 if isinstance(column, operators.OrderingOperators):
1445 return column.nulls_first()
1446 else:
1447 return UnaryExpression._create_nulls_first(column)
1450@overload
1451def nulls_last(
1452 column: "ColumnElement[_T]",
1453) -> UnaryExpression[_T]: ...
1456@overload
1457def nulls_last(
1458 column: _ColumnExpressionArgument[_T],
1459) -> Union[OrderByList, UnaryExpression[_T]]: ...
1462def nulls_last(
1463 column: _ColumnExpressionArgument[_T],
1464) -> Union[OrderByList, UnaryExpression[_T]]:
1465 """Produce the ``NULLS LAST`` modifier for an ``ORDER BY`` expression.
1467 :func:`.nulls_last` is intended to modify the expression produced
1468 by :func:`.asc` or :func:`.desc`, and indicates how NULL values
1469 should be handled when they are encountered during ordering::
1472 from sqlalchemy import desc, nulls_last
1474 stmt = select(users_table).order_by(nulls_last(desc(users_table.c.name)))
1476 The SQL expression from the above would resemble:
1478 .. sourcecode:: sql
1480 SELECT id, name FROM user ORDER BY name DESC NULLS LAST
1482 Like :func:`.asc` and :func:`.desc`, :func:`.nulls_last` is typically
1483 invoked from the column expression itself using
1484 :meth:`_expression.ColumnElement.nulls_last`,
1485 rather than as its standalone
1486 function version, as in::
1488 stmt = select(users_table).order_by(users_table.c.name.desc().nulls_last())
1490 .. versionchanged:: 1.4 :func:`.nulls_last` is renamed from
1491 :func:`.nullslast` in previous releases.
1492 The previous name remains available for backwards compatibility.
1494 .. seealso::
1496 :func:`.asc`
1498 :func:`.desc`
1500 :func:`.nulls_first`
1502 :meth:`_expression.Select.order_by`
1504 """ # noqa: E501
1505 if isinstance(column, operators.OrderingOperators):
1506 return column.nulls_last()
1507 else:
1508 return UnaryExpression._create_nulls_last(column)
1511def or_( # type: ignore[empty-body]
1512 initial_clause: Union[Literal[False], _ColumnExpressionArgument[bool]],
1513 *clauses: _ColumnExpressionArgument[bool],
1514) -> ColumnElement[bool]:
1515 """Produce a conjunction of expressions joined by ``OR``.
1517 E.g.::
1519 from sqlalchemy import or_
1521 stmt = select(users_table).where(
1522 or_(users_table.c.name == "wendy", users_table.c.name == "jack")
1523 )
1525 The :func:`.or_` conjunction is also available using the
1526 Python ``|`` operator (though note that compound expressions
1527 need to be parenthesized in order to function with Python
1528 operator precedence behavior)::
1530 stmt = select(users_table).where(
1531 (users_table.c.name == "wendy") | (users_table.c.name == "jack")
1532 )
1534 The :func:`.or_` construct must be given at least one positional
1535 argument in order to be valid; a :func:`.or_` construct with no
1536 arguments is ambiguous. To produce an "empty" or dynamically
1537 generated :func:`.or_` expression, from a given list of expressions,
1538 a "default" element of :func:`_sql.false` (or just ``False``) should be
1539 specified::
1541 from sqlalchemy import false
1543 or_criteria = or_(false(), *expressions)
1545 The above expression will compile to SQL as the expression ``false``
1546 or ``0 = 1``, depending on backend, if no other expressions are
1547 present. If expressions are present, then the :func:`_sql.false` value is
1548 ignored as it does not affect the outcome of an OR expression which
1549 has other elements.
1551 .. deprecated:: 1.4 The :func:`.or_` element now requires that at
1552 least one argument is passed; creating the :func:`.or_` construct
1553 with no arguments is deprecated, and will emit a deprecation warning
1554 while continuing to produce a blank SQL string.
1556 .. seealso::
1558 :func:`.and_`
1560 """
1561 ...
1564if not TYPE_CHECKING:
1565 # handle deprecated case which allows zero-arguments
1566 def or_(*clauses): # noqa: F811
1567 """Produce a conjunction of expressions joined by ``OR``.
1569 E.g.::
1571 from sqlalchemy import or_
1573 stmt = select(users_table).where(
1574 or_(users_table.c.name == "wendy", users_table.c.name == "jack")
1575 )
1577 The :func:`.or_` conjunction is also available using the
1578 Python ``|`` operator (though note that compound expressions
1579 need to be parenthesized in order to function with Python
1580 operator precedence behavior)::
1582 stmt = select(users_table).where(
1583 (users_table.c.name == "wendy") | (users_table.c.name == "jack")
1584 )
1586 The :func:`.or_` construct must be given at least one positional
1587 argument in order to be valid; a :func:`.or_` construct with no
1588 arguments is ambiguous. To produce an "empty" or dynamically
1589 generated :func:`.or_` expression, from a given list of expressions,
1590 a "default" element of :func:`_sql.false` (or just ``False``) should be
1591 specified::
1593 from sqlalchemy import false
1595 or_criteria = or_(false(), *expressions)
1597 The above expression will compile to SQL as the expression ``false``
1598 or ``0 = 1``, depending on backend, if no other expressions are
1599 present. If expressions are present, then the :func:`_sql.false` value
1600 is ignored as it does not affect the outcome of an OR expression which
1601 has other elements.
1603 .. deprecated:: 1.4 The :func:`.or_` element now requires that at
1604 least one argument is passed; creating the :func:`.or_` construct
1605 with no arguments is deprecated, and will emit a deprecation warning
1606 while continuing to produce a blank SQL string.
1608 .. seealso::
1610 :func:`.and_`
1612 """ # noqa: E501
1613 return BooleanClauseList.or_(*clauses)
1616def over(
1617 element: FunctionElement[_T],
1618 partition_by: _ByArgument | None = None,
1619 order_by: _ByArgument | None = None,
1620 range_: _FrameIntTuple | FrameClause | None = None,
1621 rows: _FrameIntTuple | FrameClause | None = None,
1622 groups: _FrameIntTuple | FrameClause | None = None,
1623 exclude: str | 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 :param exclude: optional string for the frame exclusion clause. This is a
1729 string value which can be one of ``CURRENT ROW``, ``GROUP``, ``TIES``, or
1730 ``NO OTHERS`` and will render an EXCLUDE clause within the window frame
1731 specification. Requires that one of :paramref:`_sql.over.rows`,
1732 :paramref:`_sql.over.range_`, or :paramref:`_sql.over.groups` is also
1733 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)