Shortcuts on this page
r m x toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1# sql/_elements_constructors.py
2# Copyright (C) 2005-2025 the SQLAlchemy authors and contributors
3# <see AUTHORS file>
4#
5# This module is part of SQLAlchemy and is released under
6# the MIT License: https://www.opensource.org/licenses/mit-license.php
8from __future__ import annotations
10import typing
11from typing import Any
12from typing import Callable
13from typing import Literal
14from typing import Mapping
15from typing import Optional
16from typing import overload
17from typing import Sequence
18from typing import Tuple as typing_Tuple
19from typing import TYPE_CHECKING
20from typing import TypeVar
21from typing import Union
23from . import coercions
24from . import operators
25from . import roles
26from .base import _NoArg
27from .coercions import _document_text_coercion
28from .elements import BindParameter
29from .elements import BooleanClauseList
30from .elements import Case
31from .elements import Cast
32from .elements import CollationClause
33from .elements import CollectionAggregate
34from .elements import ColumnClause
35from .elements import ColumnElement
36from .elements import DMLTargetCopy
37from .elements import Extract
38from .elements import False_
39from .elements import FunctionFilter
40from .elements import Label
41from .elements import Null
42from .elements import OrderByList
43from .elements import Over
44from .elements import TextClause
45from .elements import True_
46from .elements import TryCast
47from .elements import Tuple
48from .elements import TypeCoerce
49from .elements import UnaryExpression
50from .elements import WithinGroup
51from .functions import FunctionElement
53if typing.TYPE_CHECKING:
54 from ._typing import _ByArgument
55 from ._typing import _ColumnExpressionArgument
56 from ._typing import _ColumnExpressionOrLiteralArgument
57 from ._typing import _ColumnExpressionOrStrLabelArgument
58 from ._typing import _DMLOnlyColumnArgument
59 from ._typing import _TypeEngineArgument
60 from .elements import BinaryExpression
61 from .selectable import FromClause
62 from .type_api import TypeEngine
64_T = TypeVar("_T")
67def all_(expr: _ColumnExpressionArgument[_T]) -> CollectionAggregate[bool]:
68 """Produce an ALL expression.
70 For dialects such as that of PostgreSQL, this operator applies
71 to usage of the :class:`_types.ARRAY` datatype, for that of
72 MySQL, it may apply to a subquery. e.g.::
74 # renders on PostgreSQL:
75 # '5 = ALL (somearray)'
76 expr = 5 == all_(mytable.c.somearray)
78 # renders on MySQL:
79 # '5 = ALL (SELECT value FROM table)'
80 expr = 5 == all_(select(table.c.value))
82 Comparison to NULL may work using ``None``::
84 None == all_(mytable.c.somearray)
86 The any_() / all_() operators also feature a special "operand flipping"
87 behavior such that if any_() / all_() are used on the left side of a
88 comparison using a standalone operator such as ``==``, ``!=``, etc.
89 (not including operator methods such as
90 :meth:`_sql.ColumnOperators.is_`) the rendered expression is flipped::
92 # would render '5 = ALL (column)`
93 all_(mytable.c.column) == 5
95 Or with ``None``, which note will not perform
96 the usual step of rendering "IS" as is normally the case for NULL::
98 # would render 'NULL = ALL(somearray)'
99 all_(mytable.c.somearray) == None
101 .. versionchanged:: 1.4.26 repaired the use of any_() / all_()
102 comparing to NULL on the right side to be flipped to the left.
104 The column-level :meth:`_sql.ColumnElement.all_` method (not to be
105 confused with :class:`_types.ARRAY` level
106 :meth:`_types.ARRAY.Comparator.all`) is shorthand for
107 ``all_(col)``::
109 5 == mytable.c.somearray.all_()
111 .. seealso::
113 :meth:`_sql.ColumnOperators.all_`
115 :func:`_expression.any_`
117 """
118 if isinstance(expr, operators.ColumnOperators):
119 return expr.all_()
120 else:
121 return CollectionAggregate._create_all(expr)
124def and_( # type: ignore[empty-body]
125 initial_clause: Union[Literal[True], _ColumnExpressionArgument[bool]],
126 *clauses: _ColumnExpressionArgument[bool],
127) -> ColumnElement[bool]:
128 r"""Produce a conjunction of expressions joined by ``AND``.
130 E.g.::
132 from sqlalchemy import and_
134 stmt = select(users_table).where(
135 and_(users_table.c.name == "wendy", users_table.c.enrolled == True)
136 )
138 The :func:`.and_` conjunction is also available using the
139 Python ``&`` operator (though note that compound expressions
140 need to be parenthesized in order to function with Python
141 operator precedence behavior)::
143 stmt = select(users_table).where(
144 (users_table.c.name == "wendy") & (users_table.c.enrolled == True)
145 )
147 The :func:`.and_` operation is also implicit in some cases;
148 the :meth:`_expression.Select.where`
149 method for example can be invoked multiple
150 times against a statement, which will have the effect of each
151 clause being combined using :func:`.and_`::
153 stmt = (
154 select(users_table)
155 .where(users_table.c.name == "wendy")
156 .where(users_table.c.enrolled == True)
157 )
159 The :func:`.and_` construct must be given at least one positional
160 argument in order to be valid; a :func:`.and_` construct with no
161 arguments is ambiguous. To produce an "empty" or dynamically
162 generated :func:`.and_` expression, from a given list of expressions,
163 a "default" element of :func:`_sql.true` (or just ``True``) should be
164 specified::
166 from sqlalchemy import true
168 criteria = and_(true(), *expressions)
170 The above expression will compile to SQL as the expression ``true``
171 or ``1 = 1``, depending on backend, if no other expressions are
172 present. If expressions are present, then the :func:`_sql.true` value is
173 ignored as it does not affect the outcome of an AND expression that
174 has other elements.
176 .. deprecated:: 1.4 The :func:`.and_` element now requires that at
177 least one argument is passed; creating the :func:`.and_` construct
178 with no arguments is deprecated, and will emit a deprecation warning
179 while continuing to produce a blank SQL string.
181 .. seealso::
183 :func:`.or_`
185 """
186 ...
189if not TYPE_CHECKING:
190 # handle deprecated case which allows zero-arguments
191 def and_(*clauses): # noqa: F811
192 r"""Produce a conjunction of expressions joined by ``AND``.
194 E.g.::
196 from sqlalchemy import and_
198 stmt = select(users_table).where(
199 and_(users_table.c.name == "wendy", users_table.c.enrolled == True)
200 )
202 The :func:`.and_` conjunction is also available using the
203 Python ``&`` operator (though note that compound expressions
204 need to be parenthesized in order to function with Python
205 operator precedence behavior)::
207 stmt = select(users_table).where(
208 (users_table.c.name == "wendy") & (users_table.c.enrolled == True)
209 )
211 The :func:`.and_` operation is also implicit in some cases;
212 the :meth:`_expression.Select.where`
213 method for example can be invoked multiple
214 times against a statement, which will have the effect of each
215 clause being combined using :func:`.and_`::
217 stmt = (
218 select(users_table)
219 .where(users_table.c.name == "wendy")
220 .where(users_table.c.enrolled == True)
221 )
223 The :func:`.and_` construct must be given at least one positional
224 argument in order to be valid; a :func:`.and_` construct with no
225 arguments is ambiguous. To produce an "empty" or dynamically
226 generated :func:`.and_` expression, from a given list of expressions,
227 a "default" element of :func:`_sql.true` (or just ``True``) should be
228 specified::
230 from sqlalchemy import true
232 criteria = and_(true(), *expressions)
234 The above expression will compile to SQL as the expression ``true``
235 or ``1 = 1``, depending on backend, if no other expressions are
236 present. If expressions are present, then the :func:`_sql.true` value
237 is ignored as it does not affect the outcome of an AND expression that
238 has other elements.
240 .. deprecated:: 1.4 The :func:`.and_` element now requires that at
241 least one argument is passed; creating the :func:`.and_` construct
242 with no arguments is deprecated, and will emit a deprecation warning
243 while continuing to produce a blank SQL string.
245 .. seealso::
247 :func:`.or_`
249 """ # noqa: E501
250 return BooleanClauseList.and_(*clauses)
253def any_(expr: _ColumnExpressionArgument[_T]) -> CollectionAggregate[bool]:
254 """Produce an ANY expression.
256 For dialects such as that of PostgreSQL, this operator applies
257 to usage of the :class:`_types.ARRAY` datatype, for that of
258 MySQL, it may apply to a subquery. e.g.::
260 # renders on PostgreSQL:
261 # '5 = ANY (somearray)'
262 expr = 5 == any_(mytable.c.somearray)
264 # renders on MySQL:
265 # '5 = ANY (SELECT value FROM table)'
266 expr = 5 == any_(select(table.c.value))
268 Comparison to NULL may work using ``None`` or :func:`_sql.null`::
270 None == any_(mytable.c.somearray)
272 The any_() / all_() operators also feature a special "operand flipping"
273 behavior such that if any_() / all_() are used on the left side of a
274 comparison using a standalone operator such as ``==``, ``!=``, etc.
275 (not including operator methods such as
276 :meth:`_sql.ColumnOperators.is_`) the rendered expression is flipped::
278 # would render '5 = ANY (column)`
279 any_(mytable.c.column) == 5
281 Or with ``None``, which note will not perform
282 the usual step of rendering "IS" as is normally the case for NULL::
284 # would render 'NULL = ANY(somearray)'
285 any_(mytable.c.somearray) == None
287 .. versionchanged:: 1.4.26 repaired the use of any_() / all_()
288 comparing to NULL on the right side to be flipped to the left.
290 The column-level :meth:`_sql.ColumnElement.any_` method (not to be
291 confused with :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: _DMLOnlyColumnArgument[_T]) -> DMLTargetCopy[_T]:
494 r"""A placeholder that may be used in compiled INSERT or UPDATE expressions
495 to refer to the SQL expression or value being applied to another column.
497 Given a table such as::
499 t = Table(
500 "t",
501 MetaData(),
502 Column("x", Integer),
503 Column("y", Integer),
504 )
506 The :func:`_sql.from_dml_column` construct allows automatic copying
507 of an expression assigned to a different column to be re-used::
509 >>> stmt = t.insert().values(x=func.foobar(3), y=from_dml_column(t.c.x) + 5)
510 >>> print(stmt)
511 INSERT INTO t (x, y) VALUES (foobar(:foobar_1), (foobar(:foobar_1) + :param_1))
513 The :func:`_sql.from_dml_column` construct is intended to be useful primarily
514 with event-based hooks such as those used by ORM hybrids.
516 .. seealso::
518 :ref:`hybrid_bulk_update`
520 .. versionadded:: 2.1
523 """ # noqa: E501
525 return DMLTargetCopy(column)
528def bindparam(
529 key: Optional[str],
530 value: Any = _NoArg.NO_ARG,
531 type_: Optional[_TypeEngineArgument[_T]] = None,
532 unique: bool = False,
533 required: Union[bool, Literal[_NoArg.NO_ARG]] = _NoArg.NO_ARG,
534 quote: Optional[bool] = None,
535 callable_: Optional[Callable[[], Any]] = None,
536 expanding: bool = False,
537 isoutparam: bool = False,
538 literal_execute: bool = False,
539) -> BindParameter[_T]:
540 r"""Produce a "bound expression".
542 The return value is an instance of :class:`.BindParameter`; this
543 is a :class:`_expression.ColumnElement`
544 subclass which represents a so-called
545 "placeholder" value in a SQL expression, the value of which is
546 supplied at the point at which the statement in executed against a
547 database connection.
549 In SQLAlchemy, the :func:`.bindparam` construct has
550 the ability to carry along the actual value that will be ultimately
551 used at expression time. In this way, it serves not just as
552 a "placeholder" for eventual population, but also as a means of
553 representing so-called "unsafe" values which should not be rendered
554 directly in a SQL statement, but rather should be passed along
555 to the :term:`DBAPI` as values which need to be correctly escaped
556 and potentially handled for type-safety.
558 When using :func:`.bindparam` explicitly, the use case is typically
559 one of traditional deferment of parameters; the :func:`.bindparam`
560 construct accepts a name which can then be referred to at execution
561 time::
563 from sqlalchemy import bindparam
565 stmt = select(users_table).where(
566 users_table.c.name == bindparam("username")
567 )
569 The above statement, when rendered, will produce SQL similar to:
571 .. sourcecode:: sql
573 SELECT id, name FROM user WHERE name = :username
575 In order to populate the value of ``:username`` above, the value
576 would typically be applied at execution time to a method
577 like :meth:`_engine.Connection.execute`::
579 result = connection.execute(stmt, {"username": "wendy"})
581 Explicit use of :func:`.bindparam` is also common when producing
582 UPDATE or DELETE statements that are to be invoked multiple times,
583 where the WHERE criterion of the statement is to change on each
584 invocation, such as::
586 stmt = (
587 users_table.update()
588 .where(user_table.c.name == bindparam("username"))
589 .values(fullname=bindparam("fullname"))
590 )
592 connection.execute(
593 stmt,
594 [
595 {"username": "wendy", "fullname": "Wendy Smith"},
596 {"username": "jack", "fullname": "Jack Jones"},
597 ],
598 )
600 SQLAlchemy's Core expression system makes wide use of
601 :func:`.bindparam` in an implicit sense. It is typical that Python
602 literal values passed to virtually all SQL expression functions are
603 coerced into fixed :func:`.bindparam` constructs. For example, given
604 a comparison operation such as::
606 expr = users_table.c.name == "Wendy"
608 The above expression will produce a :class:`.BinaryExpression`
609 construct, where the left side is the :class:`_schema.Column` object
610 representing the ``name`` column, and the right side is a
611 :class:`.BindParameter` representing the literal value::
613 print(repr(expr.right))
614 BindParameter("%(4327771088 name)s", "Wendy", type_=String())
616 The expression above will render SQL such as:
618 .. sourcecode:: sql
620 user.name = :name_1
622 Where the ``:name_1`` parameter name is an anonymous name. The
623 actual string ``Wendy`` is not in the rendered string, but is carried
624 along where it is later used within statement execution. If we
625 invoke a statement like the following::
627 stmt = select(users_table).where(users_table.c.name == "Wendy")
628 result = connection.execute(stmt)
630 We would see SQL logging output as:
632 .. sourcecode:: sql
634 SELECT "user".id, "user".name
635 FROM "user"
636 WHERE "user".name = %(name_1)s
637 {'name_1': 'Wendy'}
639 Above, we see that ``Wendy`` is passed as a parameter to the database,
640 while the placeholder ``:name_1`` is rendered in the appropriate form
641 for the target database, in this case the PostgreSQL database.
643 Similarly, :func:`.bindparam` is invoked automatically when working
644 with :term:`CRUD` statements as far as the "VALUES" portion is
645 concerned. The :func:`_expression.insert` construct produces an
646 ``INSERT`` expression which will, at statement execution time, generate
647 bound placeholders based on the arguments passed, as in::
649 stmt = users_table.insert()
650 result = connection.execute(stmt, {"name": "Wendy"})
652 The above will produce SQL output as:
654 .. sourcecode:: sql
656 INSERT INTO "user" (name) VALUES (%(name)s)
657 {'name': 'Wendy'}
659 The :class:`_expression.Insert` construct, at
660 compilation/execution time, rendered a single :func:`.bindparam`
661 mirroring the column name ``name`` as a result of the single ``name``
662 parameter we passed to the :meth:`_engine.Connection.execute` method.
664 :param key:
665 the key (e.g. the name) for this bind param.
666 Will be used in the generated
667 SQL statement for dialects that use named parameters. This
668 value may be modified when part of a compilation operation,
669 if other :class:`BindParameter` objects exist with the same
670 key, or if its length is too long and truncation is
671 required.
673 If omitted, an "anonymous" name is generated for the bound parameter;
674 when given a value to bind, the end result is equivalent to calling upon
675 the :func:`.literal` function with a value to bind, particularly
676 if the :paramref:`.bindparam.unique` parameter is also provided.
678 :param value:
679 Initial value for this bind param. Will be used at statement
680 execution time as the value for this parameter passed to the
681 DBAPI, if no other value is indicated to the statement execution
682 method for this particular parameter name. Defaults to ``None``.
684 :param callable\_:
685 A callable function that takes the place of "value". The function
686 will be called at statement execution time to determine the
687 ultimate value. Used for scenarios where the actual bind
688 value cannot be determined at the point at which the clause
689 construct is created, but embedded bind values are still desirable.
691 :param type\_:
692 A :class:`.TypeEngine` class or instance representing an optional
693 datatype for this :func:`.bindparam`. If not passed, a type
694 may be determined automatically for the bind, based on the given
695 value; for example, trivial Python types such as ``str``,
696 ``int``, ``bool``
697 may result in the :class:`.String`, :class:`.Integer` or
698 :class:`.Boolean` types being automatically selected.
700 The type of a :func:`.bindparam` is significant especially in that
701 the type will apply pre-processing to the value before it is
702 passed to the database. For example, a :func:`.bindparam` which
703 refers to a datetime value, and is specified as holding the
704 :class:`.DateTime` type, may apply conversion needed to the
705 value (such as stringification on SQLite) before passing the value
706 to the database.
708 :param unique:
709 if True, the key name of this :class:`.BindParameter` will be
710 modified if another :class:`.BindParameter` of the same name
711 already has been located within the containing
712 expression. This flag is used generally by the internals
713 when producing so-called "anonymous" bound expressions, it
714 isn't generally applicable to explicitly-named :func:`.bindparam`
715 constructs.
717 :param required:
718 If ``True``, a value is required at execution time. If not passed,
719 it defaults to ``True`` if neither :paramref:`.bindparam.value`
720 or :paramref:`.bindparam.callable` were passed. If either of these
721 parameters are present, then :paramref:`.bindparam.required`
722 defaults to ``False``.
724 :param quote:
725 True if this parameter name requires quoting and is not
726 currently known as a SQLAlchemy reserved word; this currently
727 only applies to the Oracle Database backends, where bound names must
728 sometimes be quoted.
730 :param isoutparam:
731 if True, the parameter should be treated like a stored procedure
732 "OUT" parameter. This applies to backends such as Oracle Database which
733 support OUT parameters.
735 :param expanding:
736 if True, this parameter will be treated as an "expanding" parameter
737 at execution time; the parameter value is expected to be a sequence,
738 rather than a scalar value, and the string SQL statement will
739 be transformed on a per-execution basis to accommodate the sequence
740 with a variable number of parameter slots passed to the DBAPI.
741 This is to allow statement caching to be used in conjunction with
742 an IN clause.
744 .. seealso::
746 :meth:`.ColumnOperators.in_`
748 :ref:`baked_in` - with baked queries
750 .. note:: The "expanding" feature does not support "executemany"-
751 style parameter sets.
753 :param literal_execute:
754 if True, the bound parameter will be rendered in the compile phase
755 with a special "POSTCOMPILE" token, and the SQLAlchemy compiler will
756 render the final value of the parameter into the SQL statement at
757 statement execution time, omitting the value from the parameter
758 dictionary / list passed to DBAPI ``cursor.execute()``. This
759 produces a similar effect as that of using the ``literal_binds``,
760 compilation flag, however takes place as the statement is sent to
761 the DBAPI ``cursor.execute()`` method, rather than when the statement
762 is compiled. The primary use of this
763 capability is for rendering LIMIT / OFFSET clauses for database
764 drivers that can't accommodate for bound parameters in these
765 contexts, while allowing SQL constructs to be cacheable at the
766 compilation level.
768 .. versionadded:: 1.4 Added "post compile" bound parameters
770 .. seealso::
772 :ref:`change_4808`.
774 .. seealso::
776 :ref:`tutorial_sending_parameters` - in the
777 :ref:`unified_tutorial`
780 """
781 return BindParameter(
782 key,
783 value,
784 type_,
785 unique,
786 required,
787 quote,
788 callable_,
789 expanding,
790 isoutparam,
791 literal_execute,
792 )
795def case(
796 *whens: Union[
797 typing_Tuple[_ColumnExpressionArgument[bool], Any], Mapping[Any, Any]
798 ],
799 value: Optional[Any] = None,
800 else_: Optional[Any] = None,
801) -> Case[Any]:
802 r"""Produce a ``CASE`` expression.
804 The ``CASE`` construct in SQL is a conditional object that
805 acts somewhat analogously to an "if/then" construct in other
806 languages. It returns an instance of :class:`.Case`.
808 :func:`.case` in its usual form is passed a series of "when"
809 constructs, that is, a list of conditions and results as tuples::
811 from sqlalchemy import case
813 stmt = select(users_table).where(
814 case(
815 (users_table.c.name == "wendy", "W"),
816 (users_table.c.name == "jack", "J"),
817 else_="E",
818 )
819 )
821 The above statement will produce SQL resembling:
823 .. sourcecode:: sql
825 SELECT id, name FROM user
826 WHERE CASE
827 WHEN (name = :name_1) THEN :param_1
828 WHEN (name = :name_2) THEN :param_2
829 ELSE :param_3
830 END
832 When simple equality expressions of several values against a single
833 parent column are needed, :func:`.case` also has a "shorthand" format
834 used via the
835 :paramref:`.case.value` parameter, which is passed a column
836 expression to be compared. In this form, the :paramref:`.case.whens`
837 parameter is passed as a dictionary containing expressions to be
838 compared against keyed to result expressions. The statement below is
839 equivalent to the preceding statement::
841 stmt = select(users_table).where(
842 case({"wendy": "W", "jack": "J"}, value=users_table.c.name, else_="E")
843 )
845 The values which are accepted as result values in
846 :paramref:`.case.whens` as well as with :paramref:`.case.else_` are
847 coerced from Python literals into :func:`.bindparam` constructs.
848 SQL expressions, e.g. :class:`_expression.ColumnElement` constructs,
849 are accepted
850 as well. To coerce a literal string expression into a constant
851 expression rendered inline, use the :func:`_expression.literal_column`
852 construct,
853 as in::
855 from sqlalchemy import case, literal_column
857 case(
858 (orderline.c.qty > 100, literal_column("'greaterthan100'")),
859 (orderline.c.qty > 10, literal_column("'greaterthan10'")),
860 else_=literal_column("'lessthan10'"),
861 )
863 The above will render the given constants without using bound
864 parameters for the result values (but still for the comparison
865 values), as in:
867 .. sourcecode:: sql
869 CASE
870 WHEN (orderline.qty > :qty_1) THEN 'greaterthan100'
871 WHEN (orderline.qty > :qty_2) THEN 'greaterthan10'
872 ELSE 'lessthan10'
873 END
875 :param \*whens: The criteria to be compared against,
876 :paramref:`.case.whens` accepts two different forms, based on
877 whether or not :paramref:`.case.value` is used.
879 .. versionchanged:: 1.4 the :func:`_sql.case`
880 function now accepts the series of WHEN conditions positionally
882 In the first form, it accepts multiple 2-tuples passed as positional
883 arguments; each 2-tuple consists of ``(<sql expression>, <value>)``,
884 where the SQL expression is a boolean expression and "value" is a
885 resulting value, e.g.::
887 case(
888 (users_table.c.name == "wendy", "W"),
889 (users_table.c.name == "jack", "J"),
890 )
892 In the second form, it accepts a Python dictionary of comparison
893 values mapped to a resulting value; this form requires
894 :paramref:`.case.value` to be present, and values will be compared
895 using the ``==`` operator, e.g.::
897 case({"wendy": "W", "jack": "J"}, value=users_table.c.name)
899 :param value: An optional SQL expression which will be used as a
900 fixed "comparison point" for candidate values within a dictionary
901 passed to :paramref:`.case.whens`.
903 :param else\_: An optional SQL expression which will be the evaluated
904 result of the ``CASE`` construct if all expressions within
905 :paramref:`.case.whens` evaluate to false. When omitted, most
906 databases will produce a result of NULL if none of the "when"
907 expressions evaluate to true.
910 """ # noqa: E501
911 return Case(*whens, value=value, else_=else_)
914def cast(
915 expression: _ColumnExpressionOrLiteralArgument[Any],
916 type_: _TypeEngineArgument[_T],
917) -> Cast[_T]:
918 r"""Produce a ``CAST`` expression.
920 :func:`.cast` returns an instance of :class:`.Cast`.
922 E.g.::
924 from sqlalchemy import cast, Numeric
926 stmt = select(cast(product_table.c.unit_price, Numeric(10, 4)))
928 The above statement will produce SQL resembling:
930 .. sourcecode:: sql
932 SELECT CAST(unit_price AS NUMERIC(10, 4)) FROM product
934 The :func:`.cast` function performs two distinct functions when
935 used. The first is that it renders the ``CAST`` expression within
936 the resulting SQL string. The second is that it associates the given
937 type (e.g. :class:`.TypeEngine` class or instance) with the column
938 expression on the Python side, which means the expression will take
939 on the expression operator behavior associated with that type,
940 as well as the bound-value handling and result-row-handling behavior
941 of the type.
943 An alternative to :func:`.cast` is the :func:`.type_coerce` function.
944 This function performs the second task of associating an expression
945 with a specific type, but does not render the ``CAST`` expression
946 in SQL.
948 :param expression: A SQL expression, such as a
949 :class:`_expression.ColumnElement`
950 expression or a Python string which will be coerced into a bound
951 literal value.
953 :param type\_: A :class:`.TypeEngine` class or instance indicating
954 the type to which the ``CAST`` should apply.
956 .. seealso::
958 :ref:`tutorial_casts`
960 :func:`.try_cast` - an alternative to CAST that results in
961 NULLs when the cast fails, instead of raising an error.
962 Only supported by some dialects.
964 :func:`.type_coerce` - an alternative to CAST that coerces the type
965 on the Python side only, which is often sufficient to generate the
966 correct SQL and data coercion.
969 """
970 return Cast(expression, type_)
973def try_cast(
974 expression: _ColumnExpressionOrLiteralArgument[Any],
975 type_: _TypeEngineArgument[_T],
976) -> TryCast[_T]:
977 """Produce a ``TRY_CAST`` expression for backends which support it;
978 this is a ``CAST`` which returns NULL for un-castable conversions.
980 In SQLAlchemy, this construct is supported **only** by the SQL Server
981 dialect, and will raise a :class:`.CompileError` if used on other
982 included backends. However, third party backends may also support
983 this construct.
985 .. tip:: As :func:`_sql.try_cast` originates from the SQL Server dialect,
986 it's importable both from ``sqlalchemy.`` as well as from
987 ``sqlalchemy.dialects.mssql``.
989 :func:`_sql.try_cast` returns an instance of :class:`.TryCast` and
990 generally behaves similarly to the :class:`.Cast` construct;
991 at the SQL level, the difference between ``CAST`` and ``TRY_CAST``
992 is that ``TRY_CAST`` returns NULL for an un-castable expression,
993 such as attempting to cast a string ``"hi"`` to an integer value.
995 E.g.::
997 from sqlalchemy import select, try_cast, Numeric
999 stmt = select(try_cast(product_table.c.unit_price, Numeric(10, 4)))
1001 The above would render on Microsoft SQL Server as:
1003 .. sourcecode:: sql
1005 SELECT TRY_CAST (product_table.unit_price AS NUMERIC(10, 4))
1006 FROM product_table
1008 .. versionadded:: 2.0.14 :func:`.try_cast` has been
1009 generalized from the SQL Server dialect into a general use
1010 construct that may be supported by additional dialects.
1012 """
1013 return TryCast(expression, type_)
1016def column(
1017 text: str,
1018 type_: Optional[_TypeEngineArgument[_T]] = None,
1019 is_literal: bool = False,
1020 _selectable: Optional[FromClause] = None,
1021) -> ColumnClause[_T]:
1022 """Produce a :class:`.ColumnClause` object.
1024 The :class:`.ColumnClause` is a lightweight analogue to the
1025 :class:`_schema.Column` class. The :func:`_expression.column`
1026 function can
1027 be invoked with just a name alone, as in::
1029 from sqlalchemy import column
1031 id, name = column("id"), column("name")
1032 stmt = select(id, name).select_from("user")
1034 The above statement would produce SQL like:
1036 .. sourcecode:: sql
1038 SELECT id, name FROM user
1040 Once constructed, :func:`_expression.column`
1041 may be used like any other SQL
1042 expression element such as within :func:`_expression.select`
1043 constructs::
1045 from sqlalchemy.sql import column
1047 id, name = column("id"), column("name")
1048 stmt = select(id, name).select_from("user")
1050 The text handled by :func:`_expression.column`
1051 is assumed to be handled
1052 like the name of a database column; if the string contains mixed case,
1053 special characters, or matches a known reserved word on the target
1054 backend, the column expression will render using the quoting
1055 behavior determined by the backend. To produce a textual SQL
1056 expression that is rendered exactly without any quoting,
1057 use :func:`_expression.literal_column` instead,
1058 or pass ``True`` as the
1059 value of :paramref:`_expression.column.is_literal`. Additionally,
1060 full SQL
1061 statements are best handled using the :func:`_expression.text`
1062 construct.
1064 :func:`_expression.column` can be used in a table-like
1065 fashion by combining it with the :func:`.table` function
1066 (which is the lightweight analogue to :class:`_schema.Table`
1067 ) to produce
1068 a working table construct with minimal boilerplate::
1070 from sqlalchemy import table, column, select
1072 user = table(
1073 "user",
1074 column("id"),
1075 column("name"),
1076 column("description"),
1077 )
1079 stmt = select(user.c.description).where(user.c.name == "wendy")
1081 A :func:`_expression.column` / :func:`.table`
1082 construct like that illustrated
1083 above can be created in an
1084 ad-hoc fashion and is not associated with any
1085 :class:`_schema.MetaData`, DDL, or events, unlike its
1086 :class:`_schema.Table` counterpart.
1088 :param text: the text of the element.
1090 :param type: :class:`_types.TypeEngine` object which can associate
1091 this :class:`.ColumnClause` with a type.
1093 :param is_literal: if True, the :class:`.ColumnClause` is assumed to
1094 be an exact expression that will be delivered to the output with no
1095 quoting rules applied regardless of case sensitive settings. the
1096 :func:`_expression.literal_column()` function essentially invokes
1097 :func:`_expression.column` while passing ``is_literal=True``.
1099 .. seealso::
1101 :class:`_schema.Column`
1103 :func:`_expression.literal_column`
1105 :func:`.table`
1107 :func:`_expression.text`
1109 :ref:`tutorial_select_arbitrary_text`
1111 """
1112 return ColumnClause(text, type_, is_literal, _selectable)
1115@overload
1116def desc(
1117 column: Union[str, "ColumnElement[_T]"],
1118) -> UnaryExpression[_T]: ...
1121@overload
1122def desc(
1123 column: _ColumnExpressionOrStrLabelArgument[_T],
1124) -> Union[OrderByList, UnaryExpression[_T]]: ...
1127def desc(
1128 column: _ColumnExpressionOrStrLabelArgument[_T],
1129) -> Union[OrderByList, UnaryExpression[_T]]:
1130 """Produce a descending ``ORDER BY`` clause element.
1132 e.g.::
1134 from sqlalchemy import desc
1136 stmt = select(users_table).order_by(desc(users_table.c.name))
1138 will produce SQL as:
1140 .. sourcecode:: sql
1142 SELECT id, name FROM user ORDER BY name DESC
1144 The :func:`.desc` function is a standalone version of the
1145 :meth:`_expression.ColumnElement.desc`
1146 method available on all SQL expressions,
1147 e.g.::
1150 stmt = select(users_table).order_by(users_table.c.name.desc())
1152 :param column: A :class:`_expression.ColumnElement` (e.g.
1153 scalar SQL expression)
1154 with which to apply the :func:`.desc` operation.
1156 .. seealso::
1158 :func:`.asc`
1160 :func:`.nulls_first`
1162 :func:`.nulls_last`
1164 :meth:`_expression.Select.order_by`
1166 """
1167 if isinstance(column, operators.OrderingOperators):
1168 return column.desc() # type: ignore[unused-ignore]
1169 else:
1170 return UnaryExpression._create_desc(column)
1173def distinct(expr: _ColumnExpressionArgument[_T]) -> UnaryExpression[_T]:
1174 """Produce an column-expression-level unary ``DISTINCT`` clause.
1176 This applies the ``DISTINCT`` keyword to an **individual column
1177 expression** (e.g. not the whole statement), and renders **specifically
1178 in that column position**; this is used for containment within
1179 an aggregate function, as in::
1181 from sqlalchemy import distinct, func
1183 stmt = select(users_table.c.id, func.count(distinct(users_table.c.name)))
1185 The above would produce an statement resembling:
1187 .. sourcecode:: sql
1189 SELECT user.id, count(DISTINCT user.name) FROM user
1191 .. tip:: The :func:`_sql.distinct` function does **not** apply DISTINCT
1192 to the full SELECT statement, instead applying a DISTINCT modifier
1193 to **individual column expressions**. For general ``SELECT DISTINCT``
1194 support, use the
1195 :meth:`_sql.Select.distinct` method on :class:`_sql.Select`.
1197 The :func:`.distinct` function is also available as a column-level
1198 method, e.g. :meth:`_expression.ColumnElement.distinct`, as in::
1200 stmt = select(func.count(users_table.c.name.distinct()))
1202 The :func:`.distinct` operator is different from the
1203 :meth:`_expression.Select.distinct` method of
1204 :class:`_expression.Select`,
1205 which produces a ``SELECT`` statement
1206 with ``DISTINCT`` applied to the result set as a whole,
1207 e.g. a ``SELECT DISTINCT`` expression. See that method for further
1208 information.
1210 .. seealso::
1212 :meth:`_expression.ColumnElement.distinct`
1214 :meth:`_expression.Select.distinct`
1216 :data:`.func`
1218 """ # noqa: E501
1219 if isinstance(expr, operators.ColumnOperators):
1220 return expr.distinct()
1221 else:
1222 return UnaryExpression._create_distinct(expr)
1225def bitwise_not(expr: _ColumnExpressionArgument[_T]) -> UnaryExpression[_T]:
1226 """Produce a unary bitwise NOT clause, typically via the ``~`` operator.
1228 Not to be confused with boolean negation :func:`_sql.not_`.
1230 .. versionadded:: 2.0.2
1232 .. seealso::
1234 :ref:`operators_bitwise`
1237 """
1238 if isinstance(expr, operators.ColumnOperators):
1239 return expr.bitwise_not()
1240 else:
1241 return UnaryExpression._create_bitwise_not(expr)
1244def extract(field: str, expr: _ColumnExpressionArgument[Any]) -> Extract:
1245 """Return a :class:`.Extract` construct.
1247 This is typically available as :func:`.extract`
1248 as well as ``func.extract`` from the
1249 :data:`.func` namespace.
1251 :param field: The field to extract.
1253 .. warning:: This field is used as a literal SQL string.
1254 **DO NOT PASS UNTRUSTED INPUT TO THIS STRING**.
1256 :param expr: A column or Python scalar expression serving as the
1257 right side of the ``EXTRACT`` expression.
1259 E.g.::
1261 from sqlalchemy import extract
1262 from sqlalchemy import table, column
1264 logged_table = table(
1265 "user",
1266 column("id"),
1267 column("date_created"),
1268 )
1270 stmt = select(logged_table.c.id).where(
1271 extract("YEAR", logged_table.c.date_created) == 2021
1272 )
1274 In the above example, the statement is used to select ids from the
1275 database where the ``YEAR`` component matches a specific value.
1277 Similarly, one can also select an extracted component::
1279 stmt = select(extract("YEAR", logged_table.c.date_created)).where(
1280 logged_table.c.id == 1
1281 )
1283 The implementation of ``EXTRACT`` may vary across database backends.
1284 Users are reminded to consult their database documentation.
1285 """
1286 return Extract(field, expr)
1289def false() -> False_:
1290 """Return a :class:`.False_` construct.
1292 E.g.:
1294 .. sourcecode:: pycon+sql
1296 >>> from sqlalchemy import false
1297 >>> print(select(t.c.x).where(false()))
1298 {printsql}SELECT x FROM t WHERE false
1300 A backend which does not support true/false constants will render as
1301 an expression against 1 or 0:
1303 .. sourcecode:: pycon+sql
1305 >>> print(select(t.c.x).where(false()))
1306 {printsql}SELECT x FROM t WHERE 0 = 1
1308 The :func:`.true` and :func:`.false` constants also feature
1309 "short circuit" operation within an :func:`.and_` or :func:`.or_`
1310 conjunction:
1312 .. sourcecode:: pycon+sql
1314 >>> print(select(t.c.x).where(or_(t.c.x > 5, true())))
1315 {printsql}SELECT x FROM t WHERE true{stop}
1317 >>> print(select(t.c.x).where(and_(t.c.x > 5, false())))
1318 {printsql}SELECT x FROM t WHERE false{stop}
1320 .. seealso::
1322 :func:`.true`
1324 """
1326 return False_._instance()
1329def funcfilter(
1330 func: FunctionElement[_T], *criterion: _ColumnExpressionArgument[bool]
1331) -> FunctionFilter[_T]:
1332 """Produce a :class:`.FunctionFilter` object against a function.
1334 Used against aggregate and window functions,
1335 for database backends that support the "FILTER" clause.
1337 E.g.::
1339 from sqlalchemy import funcfilter
1341 funcfilter(func.count(1), MyClass.name == "some name")
1343 Would produce "COUNT(1) FILTER (WHERE myclass.name = 'some name')".
1345 This function is also available from the :data:`~.expression.func`
1346 construct itself via the :meth:`.FunctionElement.filter` method.
1348 .. seealso::
1350 :ref:`tutorial_functions_within_group` - in the
1351 :ref:`unified_tutorial`
1353 :meth:`.FunctionElement.filter`
1355 """
1356 return FunctionFilter(func, *criterion)
1359def label(
1360 name: str,
1361 element: _ColumnExpressionArgument[_T],
1362 type_: Optional[_TypeEngineArgument[_T]] = None,
1363) -> Label[_T]:
1364 """Return a :class:`Label` object for the
1365 given :class:`_expression.ColumnElement`.
1367 A label changes the name of an element in the columns clause of a
1368 ``SELECT`` statement, typically via the ``AS`` SQL keyword.
1370 This functionality is more conveniently available via the
1371 :meth:`_expression.ColumnElement.label` method on
1372 :class:`_expression.ColumnElement`.
1374 :param name: label name
1376 :param obj: a :class:`_expression.ColumnElement`.
1378 """
1379 return Label(name, element, type_)
1382def null() -> Null:
1383 """Return a constant :class:`.Null` construct."""
1385 return Null._instance()
1388@overload
1389def nulls_first(
1390 column: "ColumnElement[_T]",
1391) -> UnaryExpression[_T]: ...
1394@overload
1395def nulls_first(
1396 column: _ColumnExpressionArgument[_T],
1397) -> Union[OrderByList, UnaryExpression[_T]]: ...
1400def nulls_first(
1401 column: _ColumnExpressionArgument[_T],
1402) -> Union[OrderByList, UnaryExpression[_T]]:
1403 """Produce the ``NULLS FIRST`` modifier for an ``ORDER BY`` expression.
1405 :func:`.nulls_first` is intended to modify the expression produced
1406 by :func:`.asc` or :func:`.desc`, and indicates how NULL values
1407 should be handled when they are encountered during ordering::
1410 from sqlalchemy import desc, nulls_first
1412 stmt = select(users_table).order_by(nulls_first(desc(users_table.c.name)))
1414 The SQL expression from the above would resemble:
1416 .. sourcecode:: sql
1418 SELECT id, name FROM user ORDER BY name DESC NULLS FIRST
1420 Like :func:`.asc` and :func:`.desc`, :func:`.nulls_first` is typically
1421 invoked from the column expression itself using
1422 :meth:`_expression.ColumnElement.nulls_first`,
1423 rather than as its standalone
1424 function version, as in::
1426 stmt = select(users_table).order_by(
1427 users_table.c.name.desc().nulls_first()
1428 )
1430 .. versionchanged:: 1.4 :func:`.nulls_first` is renamed from
1431 :func:`.nullsfirst` in previous releases.
1432 The previous name remains available for backwards compatibility.
1434 .. seealso::
1436 :func:`.asc`
1438 :func:`.desc`
1440 :func:`.nulls_last`
1442 :meth:`_expression.Select.order_by`
1444 """ # noqa: E501
1445 if isinstance(column, operators.OrderingOperators):
1446 return column.nulls_first()
1447 else:
1448 return UnaryExpression._create_nulls_first(column)
1451@overload
1452def nulls_last(
1453 column: "ColumnElement[_T]",
1454) -> UnaryExpression[_T]: ...
1457@overload
1458def nulls_last(
1459 column: _ColumnExpressionArgument[_T],
1460) -> Union[OrderByList, UnaryExpression[_T]]: ...
1463def nulls_last(
1464 column: _ColumnExpressionArgument[_T],
1465) -> Union[OrderByList, UnaryExpression[_T]]:
1466 """Produce the ``NULLS LAST`` modifier for an ``ORDER BY`` expression.
1468 :func:`.nulls_last` is intended to modify the expression produced
1469 by :func:`.asc` or :func:`.desc`, and indicates how NULL values
1470 should be handled when they are encountered during ordering::
1473 from sqlalchemy import desc, nulls_last
1475 stmt = select(users_table).order_by(nulls_last(desc(users_table.c.name)))
1477 The SQL expression from the above would resemble:
1479 .. sourcecode:: sql
1481 SELECT id, name FROM user ORDER BY name DESC NULLS LAST
1483 Like :func:`.asc` and :func:`.desc`, :func:`.nulls_last` is typically
1484 invoked from the column expression itself using
1485 :meth:`_expression.ColumnElement.nulls_last`,
1486 rather than as its standalone
1487 function version, as in::
1489 stmt = select(users_table).order_by(users_table.c.name.desc().nulls_last())
1491 .. versionchanged:: 1.4 :func:`.nulls_last` is renamed from
1492 :func:`.nullslast` in previous releases.
1493 The previous name remains available for backwards compatibility.
1495 .. seealso::
1497 :func:`.asc`
1499 :func:`.desc`
1501 :func:`.nulls_first`
1503 :meth:`_expression.Select.order_by`
1505 """ # noqa: E501
1506 if isinstance(column, operators.OrderingOperators):
1507 return column.nulls_last()
1508 else:
1509 return UnaryExpression._create_nulls_last(column)
1512def or_( # type: ignore[empty-body]
1513 initial_clause: Union[Literal[False], _ColumnExpressionArgument[bool]],
1514 *clauses: _ColumnExpressionArgument[bool],
1515) -> ColumnElement[bool]:
1516 """Produce a conjunction of expressions joined by ``OR``.
1518 E.g.::
1520 from sqlalchemy import or_
1522 stmt = select(users_table).where(
1523 or_(users_table.c.name == "wendy", users_table.c.name == "jack")
1524 )
1526 The :func:`.or_` conjunction is also available using the
1527 Python ``|`` operator (though note that compound expressions
1528 need to be parenthesized in order to function with Python
1529 operator precedence behavior)::
1531 stmt = select(users_table).where(
1532 (users_table.c.name == "wendy") | (users_table.c.name == "jack")
1533 )
1535 The :func:`.or_` construct must be given at least one positional
1536 argument in order to be valid; a :func:`.or_` construct with no
1537 arguments is ambiguous. To produce an "empty" or dynamically
1538 generated :func:`.or_` expression, from a given list of expressions,
1539 a "default" element of :func:`_sql.false` (or just ``False``) should be
1540 specified::
1542 from sqlalchemy import false
1544 or_criteria = or_(false(), *expressions)
1546 The above expression will compile to SQL as the expression ``false``
1547 or ``0 = 1``, depending on backend, if no other expressions are
1548 present. If expressions are present, then the :func:`_sql.false` value is
1549 ignored as it does not affect the outcome of an OR expression which
1550 has other elements.
1552 .. deprecated:: 1.4 The :func:`.or_` element now requires that at
1553 least one argument is passed; creating the :func:`.or_` construct
1554 with no arguments is deprecated, and will emit a deprecation warning
1555 while continuing to produce a blank SQL string.
1557 .. seealso::
1559 :func:`.and_`
1561 """
1562 ...
1565if not TYPE_CHECKING:
1566 # handle deprecated case which allows zero-arguments
1567 def or_(*clauses): # noqa: F811
1568 """Produce a conjunction of expressions joined by ``OR``.
1570 E.g.::
1572 from sqlalchemy import or_
1574 stmt = select(users_table).where(
1575 or_(users_table.c.name == "wendy", users_table.c.name == "jack")
1576 )
1578 The :func:`.or_` conjunction is also available using the
1579 Python ``|`` operator (though note that compound expressions
1580 need to be parenthesized in order to function with Python
1581 operator precedence behavior)::
1583 stmt = select(users_table).where(
1584 (users_table.c.name == "wendy") | (users_table.c.name == "jack")
1585 )
1587 The :func:`.or_` construct must be given at least one positional
1588 argument in order to be valid; a :func:`.or_` construct with no
1589 arguments is ambiguous. To produce an "empty" or dynamically
1590 generated :func:`.or_` expression, from a given list of expressions,
1591 a "default" element of :func:`_sql.false` (or just ``False``) should be
1592 specified::
1594 from sqlalchemy import false
1596 or_criteria = or_(false(), *expressions)
1598 The above expression will compile to SQL as the expression ``false``
1599 or ``0 = 1``, depending on backend, if no other expressions are
1600 present. If expressions are present, then the :func:`_sql.false` value
1601 is ignored as it does not affect the outcome of an OR expression which
1602 has other elements.
1604 .. deprecated:: 1.4 The :func:`.or_` element now requires that at
1605 least one argument is passed; creating the :func:`.or_` construct
1606 with no arguments is deprecated, and will emit a deprecation warning
1607 while continuing to produce a blank SQL string.
1609 .. seealso::
1611 :func:`.and_`
1613 """ # noqa: E501
1614 return BooleanClauseList.or_(*clauses)
1617def over(
1618 element: FunctionElement[_T],
1619 partition_by: Optional[_ByArgument] = None,
1620 order_by: Optional[_ByArgument] = None,
1621 range_: Optional[typing_Tuple[Optional[int], Optional[int]]] = None,
1622 rows: Optional[typing_Tuple[Optional[int], Optional[int]]] = None,
1623 groups: Optional[typing_Tuple[Optional[int], Optional[int]]] = 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 :param element: a :class:`.FunctionElement`, :class:`.WithinGroup`,
1681 or other compatible construct.
1682 :param partition_by: a column element or string, or a list
1683 of such, that will be used as the PARTITION BY clause
1684 of the OVER construct.
1685 :param order_by: a column element or string, or a list
1686 of such, that will be used as the ORDER BY clause
1687 of the OVER construct.
1688 :param range\_: optional range clause for the window. This is a
1689 tuple value which can contain integer values or ``None``,
1690 and will render a RANGE BETWEEN PRECEDING / FOLLOWING clause.
1691 :param rows: optional rows clause for the window. This is a tuple
1692 value which can contain integer values or None, and will render
1693 a ROWS BETWEEN PRECEDING / FOLLOWING clause.
1694 :param groups: optional groups clause for the window. This is a
1695 tuple value which can contain integer values or ``None``,
1696 and will render a GROUPS BETWEEN PRECEDING / FOLLOWING clause.
1698 .. versionadded:: 2.0.40
1700 This function is also available from the :data:`~.expression.func`
1701 construct itself via the :meth:`.FunctionElement.over` method.
1703 .. seealso::
1705 :ref:`tutorial_window_functions` - in the :ref:`unified_tutorial`
1707 :data:`.expression.func`
1709 :func:`_expression.within_group`
1711 """ # noqa: E501
1712 return Over(element, partition_by, order_by, range_, rows, groups)
1715@_document_text_coercion("text", ":func:`.text`", ":paramref:`.text.text`")
1716def text(text: str) -> TextClause:
1717 r"""Construct a new :class:`_expression.TextClause` clause,
1718 representing
1719 a textual SQL string directly.
1721 E.g.::
1723 from sqlalchemy import text
1725 t = text("SELECT * FROM users")
1726 result = connection.execute(t)
1728 The advantages :func:`_expression.text`
1729 provides over a plain string are
1730 backend-neutral support for bind parameters, per-statement
1731 execution options, as well as
1732 bind parameter and result-column typing behavior, allowing
1733 SQLAlchemy type constructs to play a role when executing
1734 a statement that is specified literally. The construct can also
1735 be provided with a ``.c`` collection of column elements, allowing
1736 it to be embedded in other SQL expression constructs as a subquery.
1738 Bind parameters are specified by name, using the format ``:name``.
1739 E.g.::
1741 t = text("SELECT * FROM users WHERE id=:user_id")
1742 result = connection.execute(t, {"user_id": 12})
1744 For SQL statements where a colon is required verbatim, as within
1745 an inline string, use a backslash to escape::
1747 t = text(r"SELECT * FROM users WHERE name='\:username'")
1749 The :class:`_expression.TextClause`
1750 construct includes methods which can
1751 provide information about the bound parameters as well as the column
1752 values which would be returned from the textual statement, assuming
1753 it's an executable SELECT type of statement. The
1754 :meth:`_expression.TextClause.bindparams`
1755 method is used to provide bound
1756 parameter detail, and :meth:`_expression.TextClause.columns`
1757 method allows
1758 specification of return columns including names and types::
1760 t = (
1761 text("SELECT * FROM users WHERE id=:user_id")
1762 .bindparams(user_id=7)
1763 .columns(id=Integer, name=String)
1764 )
1766 for id, name in connection.execute(t):
1767 print(id, name)
1769 The :func:`_expression.text` construct is used in cases when
1770 a literal string SQL fragment is specified as part of a larger query,
1771 such as for the WHERE clause of a SELECT statement::
1773 s = select(users.c.id, users.c.name).where(text("id=:user_id"))
1774 result = connection.execute(s, {"user_id": 12})
1776 :func:`_expression.text` is also used for the construction
1777 of a full, standalone statement using plain text.
1778 As such, SQLAlchemy refers
1779 to it as an :class:`.Executable` object and may be used
1780 like any other statement passed to an ``.execute()`` method.
1782 :param text:
1783 the text of the SQL statement to be created. Use ``:<param>``
1784 to specify bind parameters; they will be compiled to their
1785 engine-specific format.
1787 .. seealso::
1789 :ref:`tutorial_select_arbitrary_text`
1791 """
1792 return TextClause(text)
1795def true() -> True_:
1796 """Return a constant :class:`.True_` construct.
1798 E.g.:
1800 .. sourcecode:: pycon+sql
1802 >>> from sqlalchemy import true
1803 >>> print(select(t.c.x).where(true()))
1804 {printsql}SELECT x FROM t WHERE true
1806 A backend which does not support true/false constants will render as
1807 an expression against 1 or 0:
1809 .. sourcecode:: pycon+sql
1811 >>> print(select(t.c.x).where(true()))
1812 {printsql}SELECT x FROM t WHERE 1 = 1
1814 The :func:`.true` and :func:`.false` constants also feature
1815 "short circuit" operation within an :func:`.and_` or :func:`.or_`
1816 conjunction:
1818 .. sourcecode:: pycon+sql
1820 >>> print(select(t.c.x).where(or_(t.c.x > 5, true())))
1821 {printsql}SELECT x FROM t WHERE true{stop}
1823 >>> print(select(t.c.x).where(and_(t.c.x > 5, false())))
1824 {printsql}SELECT x FROM t WHERE false{stop}
1826 .. seealso::
1828 :func:`.false`
1830 """
1832 return True_._instance()
1835def tuple_(
1836 *clauses: _ColumnExpressionOrLiteralArgument[Any],
1837 types: Optional[Sequence[_TypeEngineArgument[Any]]] = None,
1838) -> Tuple:
1839 """Return a :class:`.Tuple`.
1841 Main usage is to produce a composite IN construct using
1842 :meth:`.ColumnOperators.in_` ::
1844 from sqlalchemy import tuple_
1846 tuple_(table.c.col1, table.c.col2).in_([(1, 2), (5, 12), (10, 19)])
1848 .. warning::
1850 The composite IN construct is not supported by all backends, and is
1851 currently known to work on PostgreSQL, MySQL, and SQLite.
1852 Unsupported backends will raise a subclass of
1853 :class:`~sqlalchemy.exc.DBAPIError` when such an expression is
1854 invoked.
1856 """
1857 return Tuple(*clauses, types=types)
1860def type_coerce(
1861 expression: _ColumnExpressionOrLiteralArgument[Any],
1862 type_: _TypeEngineArgument[_T],
1863) -> TypeCoerce[_T]:
1864 r"""Associate a SQL expression with a particular type, without rendering
1865 ``CAST``.
1867 E.g.::
1869 from sqlalchemy import type_coerce
1871 stmt = select(type_coerce(log_table.date_string, StringDateTime()))
1873 The above construct will produce a :class:`.TypeCoerce` object, which
1874 does not modify the rendering in any way on the SQL side, with the
1875 possible exception of a generated label if used in a columns clause
1876 context:
1878 .. sourcecode:: sql
1880 SELECT date_string AS date_string FROM log
1882 When result rows are fetched, the ``StringDateTime`` type processor
1883 will be applied to result rows on behalf of the ``date_string`` column.
1885 .. note:: the :func:`.type_coerce` construct does not render any
1886 SQL syntax of its own, including that it does not imply
1887 parenthesization. Please use :meth:`.TypeCoerce.self_group`
1888 if explicit parenthesization is required.
1890 In order to provide a named label for the expression, use
1891 :meth:`_expression.ColumnElement.label`::
1893 stmt = select(
1894 type_coerce(log_table.date_string, StringDateTime()).label("date")
1895 )
1897 A type that features bound-value handling will also have that behavior
1898 take effect when literal values or :func:`.bindparam` constructs are
1899 passed to :func:`.type_coerce` as targets.
1900 For example, if a type implements the
1901 :meth:`.TypeEngine.bind_expression`
1902 method or :meth:`.TypeEngine.bind_processor` method or equivalent,
1903 these functions will take effect at statement compilation/execution
1904 time when a literal value is passed, as in::
1906 # bound-value handling of MyStringType will be applied to the
1907 # literal value "some string"
1908 stmt = select(type_coerce("some string", MyStringType))
1910 When using :func:`.type_coerce` with composed expressions, note that
1911 **parenthesis are not applied**. If :func:`.type_coerce` is being
1912 used in an operator context where the parenthesis normally present from
1913 CAST are necessary, use the :meth:`.TypeCoerce.self_group` method:
1915 .. sourcecode:: pycon+sql
1917 >>> some_integer = column("someint", Integer)
1918 >>> some_string = column("somestr", String)
1919 >>> expr = type_coerce(some_integer + 5, String) + some_string
1920 >>> print(expr)
1921 {printsql}someint + :someint_1 || somestr{stop}
1922 >>> expr = type_coerce(some_integer + 5, String).self_group() + some_string
1923 >>> print(expr)
1924 {printsql}(someint + :someint_1) || somestr{stop}
1926 :param expression: A SQL expression, such as a
1927 :class:`_expression.ColumnElement`
1928 expression or a Python string which will be coerced into a bound
1929 literal value.
1931 :param type\_: A :class:`.TypeEngine` class or instance indicating
1932 the type to which the expression is coerced.
1934 .. seealso::
1936 :ref:`tutorial_casts`
1938 :func:`.cast`
1940 """ # noqa
1941 return TypeCoerce(expression, type_)
1944def within_group(
1945 element: FunctionElement[_T], *order_by: _ColumnExpressionArgument[Any]
1946) -> WithinGroup[_T]:
1947 r"""Produce a :class:`.WithinGroup` object against a function.
1949 Used against so-called "ordered set aggregate" and "hypothetical
1950 set aggregate" functions, including :class:`.percentile_cont`,
1951 :class:`.rank`, :class:`.dense_rank`, etc.
1953 :func:`_expression.within_group` is usually called using
1954 the :meth:`.FunctionElement.within_group` method, e.g.::
1956 from sqlalchemy import within_group
1958 stmt = select(
1959 department.c.id,
1960 func.percentile_cont(0.5).within_group(department.c.salary.desc()),
1961 )
1963 The above statement would produce SQL similar to
1964 ``SELECT department.id, percentile_cont(0.5)
1965 WITHIN GROUP (ORDER BY department.salary DESC)``.
1967 :param element: a :class:`.FunctionElement` construct, typically
1968 generated by :data:`~.expression.func`.
1969 :param \*order_by: one or more column elements that will be used
1970 as the ORDER BY clause of the WITHIN GROUP construct.
1972 .. seealso::
1974 :ref:`tutorial_functions_within_group` - in the
1975 :ref:`unified_tutorial`
1977 :data:`.expression.func`
1979 :func:`_expression.over`
1981 """
1982 return WithinGroup(element, *order_by)