Shortcuts on this page
r m x toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1# sql/_elements_constructors.py
2# Copyright (C) 2005-2025 the SQLAlchemy authors and contributors
3# <see AUTHORS file>
4#
5# This module is part of SQLAlchemy and is released under
6# the MIT License: https://www.opensource.org/licenses/mit-license.php
8from __future__ import annotations
10import typing
11from typing import Any
12from typing import Callable
13from typing import Literal
14from typing import Mapping
15from typing import Optional
16from typing import overload
17from typing import Sequence
18from typing import Tuple as typing_Tuple
19from typing import TYPE_CHECKING
20from typing import TypeVar
21from typing import Union
23from . import coercions
24from . import operators
25from . import roles
26from .base import _NoArg
27from .coercions import _document_text_coercion
28from .elements import AggregateOrderBy
29from .elements import BindParameter
30from .elements import BooleanClauseList
31from .elements import Case
32from .elements import Cast
33from .elements import CollationClause
34from .elements import CollectionAggregate
35from .elements import ColumnClause
36from .elements import ColumnElement
37from .elements import DMLTargetCopy
38from .elements import Extract
39from .elements import False_
40from .elements import FunctionFilter
41from .elements import Label
42from .elements import Null
43from .elements import OrderByList
44from .elements import Over
45from .elements import TextClause
46from .elements import True_
47from .elements import TryCast
48from .elements import Tuple
49from .elements import TypeCoerce
50from .elements import UnaryExpression
51from .elements import WithinGroup
52from .functions import FunctionElement
54if typing.TYPE_CHECKING:
55 from ._typing import _ByArgument
56 from ._typing import _ColumnExpressionArgument
57 from ._typing import _ColumnExpressionOrLiteralArgument
58 from ._typing import _ColumnExpressionOrStrLabelArgument
59 from ._typing import _DMLOnlyColumnArgument
60 from ._typing import _TypeEngineArgument
61 from .elements import _FrameIntTuple
62 from .elements import BinaryExpression
63 from .elements import FrameClause
64 from .selectable import FromClause
65 from .type_api import TypeEngine
67_T = TypeVar("_T")
70def all_(expr: _ColumnExpressionArgument[_T]) -> CollectionAggregate[bool]:
71 """Produce an ALL expression.
73 For dialects such as that of PostgreSQL, this operator applies
74 to usage of the :class:`_types.ARRAY` datatype, for that of
75 MySQL, it may apply to a subquery. e.g.::
77 # renders on PostgreSQL:
78 # '5 = ALL (somearray)'
79 expr = 5 == all_(mytable.c.somearray)
81 # renders on MySQL:
82 # '5 = ALL (SELECT value FROM table)'
83 expr = 5 == all_(select(table.c.value))
85 Comparison to NULL may work using ``None``::
87 None == all_(mytable.c.somearray)
89 The any_() / all_() operators also feature a special "operand flipping"
90 behavior such that if any_() / all_() are used on the left side of a
91 comparison using a standalone operator such as ``==``, ``!=``, etc.
92 (not including operator methods such as
93 :meth:`_sql.ColumnOperators.is_`) the rendered expression is flipped::
95 # would render '5 = ALL (column)`
96 all_(mytable.c.column) == 5
98 Or with ``None``, which note will not perform
99 the usual step of rendering "IS" as is normally the case for NULL::
101 # would render 'NULL = ALL(somearray)'
102 all_(mytable.c.somearray) == None
104 The column-level :meth:`_sql.ColumnElement.all_` method (not to be
105 confused with the deprecated :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 The column-level :meth:`_sql.ColumnElement.any_` method (not to be
288 confused with the deprecated :class:`_types.ARRAY` level
289 :meth:`_types.ARRAY.Comparator.any`) is shorthand for
290 ``any_(col)``::
292 5 = mytable.c.somearray.any_()
294 .. seealso::
296 :meth:`_sql.ColumnOperators.any_`
298 :func:`_expression.all_`
300 """
301 if isinstance(expr, operators.ColumnOperators):
302 return expr.any_()
303 else:
304 return CollectionAggregate._create_any(expr)
307@overload
308def asc(
309 column: Union[str, "ColumnElement[_T]"],
310) -> UnaryExpression[_T]: ...
313@overload
314def asc(
315 column: _ColumnExpressionOrStrLabelArgument[_T],
316) -> Union[OrderByList, UnaryExpression[_T]]: ...
319def asc(
320 column: _ColumnExpressionOrStrLabelArgument[_T],
321) -> Union[OrderByList, UnaryExpression[_T]]:
322 """Produce an ascending ``ORDER BY`` clause element.
324 e.g.::
326 from sqlalchemy import asc
328 stmt = select(users_table).order_by(asc(users_table.c.name))
330 will produce SQL as:
332 .. sourcecode:: sql
334 SELECT id, name FROM user ORDER BY name ASC
336 The :func:`.asc` function is a standalone version of the
337 :meth:`_expression.ColumnElement.asc`
338 method available on all SQL expressions,
339 e.g.::
342 stmt = select(users_table).order_by(users_table.c.name.asc())
344 :param column: A :class:`_expression.ColumnElement` (e.g.
345 scalar SQL expression)
346 with which to apply the :func:`.asc` operation.
348 .. seealso::
350 :func:`.desc`
352 :func:`.nulls_first`
354 :func:`.nulls_last`
356 :meth:`_expression.Select.order_by`
358 """
360 if isinstance(column, operators.OrderingOperators):
361 return column.asc() # type: ignore[unused-ignore]
362 else:
363 return UnaryExpression._create_asc(column)
366def collate(
367 expression: _ColumnExpressionArgument[str], collation: str
368) -> BinaryExpression[str]:
369 """Return the clause ``expression COLLATE collation``.
371 e.g.::
373 collate(mycolumn, "utf8_bin")
375 produces:
377 .. sourcecode:: sql
379 mycolumn COLLATE utf8_bin
381 The collation expression is also quoted if it is a case sensitive
382 identifier, e.g. contains uppercase characters.
384 """
385 if isinstance(expression, operators.ColumnOperators):
386 return expression.collate(collation) # type: ignore
387 else:
388 return CollationClause._create_collation_expression(
389 expression, collation
390 )
393def between(
394 expr: _ColumnExpressionOrLiteralArgument[_T],
395 lower_bound: Any,
396 upper_bound: Any,
397 symmetric: bool = False,
398) -> BinaryExpression[bool]:
399 """Produce a ``BETWEEN`` predicate clause.
401 E.g.::
403 from sqlalchemy import between
405 stmt = select(users_table).where(between(users_table.c.id, 5, 7))
407 Would produce SQL resembling:
409 .. sourcecode:: sql
411 SELECT id, name FROM user WHERE id BETWEEN :id_1 AND :id_2
413 The :func:`.between` function is a standalone version of the
414 :meth:`_expression.ColumnElement.between` method available on all
415 SQL expressions, as in::
417 stmt = select(users_table).where(users_table.c.id.between(5, 7))
419 All arguments passed to :func:`.between`, including the left side
420 column expression, are coerced from Python scalar values if a
421 the value is not a :class:`_expression.ColumnElement` subclass.
422 For example,
423 three fixed values can be compared as in::
425 print(between(5, 3, 7))
427 Which would produce::
429 :param_1 BETWEEN :param_2 AND :param_3
431 :param expr: a column expression, typically a
432 :class:`_expression.ColumnElement`
433 instance or alternatively a Python scalar expression to be coerced
434 into a column expression, serving as the left side of the ``BETWEEN``
435 expression.
437 :param lower_bound: a column or Python scalar expression serving as the
438 lower bound of the right side of the ``BETWEEN`` expression.
440 :param upper_bound: a column or Python scalar expression serving as the
441 upper bound of the right side of the ``BETWEEN`` expression.
443 :param symmetric: if True, will render " BETWEEN SYMMETRIC ". Note
444 that not all databases support this syntax.
446 .. seealso::
448 :meth:`_expression.ColumnElement.between`
450 """
451 col_expr = coercions.expect(roles.ExpressionElementRole, expr)
452 return col_expr.between(lower_bound, upper_bound, symmetric=symmetric)
455def outparam(
456 key: str, type_: Optional[TypeEngine[_T]] = None
457) -> BindParameter[_T]:
458 """Create an 'OUT' parameter for usage in functions (stored procedures),
459 for databases which support them.
461 The ``outparam`` can be used like a regular function parameter.
462 The "output" value will be available from the
463 :class:`~sqlalchemy.engine.CursorResult` object via its ``out_parameters``
464 attribute, which returns a dictionary containing the values.
466 """
467 return BindParameter(key, None, type_=type_, unique=False, isoutparam=True)
470@overload
471def not_(clause: BinaryExpression[_T]) -> BinaryExpression[_T]: ...
474@overload
475def not_(clause: _ColumnExpressionArgument[_T]) -> ColumnElement[_T]: ...
478def not_(clause: _ColumnExpressionArgument[_T]) -> ColumnElement[_T]:
479 """Return a negation of the given clause, i.e. ``NOT(clause)``.
481 The ``~`` operator is also overloaded on all
482 :class:`_expression.ColumnElement` subclasses to produce the
483 same result.
485 """
487 return coercions.expect(roles.ExpressionElementRole, clause).__invert__()
490def from_dml_column(column: _DMLOnlyColumnArgument[_T]) -> DMLTargetCopy[_T]:
491 r"""A placeholder that may be used in compiled INSERT or UPDATE expressions
492 to refer to the SQL expression or value being applied to another column.
494 Given a table such as::
496 t = Table(
497 "t",
498 MetaData(),
499 Column("x", Integer),
500 Column("y", Integer),
501 )
503 The :func:`_sql.from_dml_column` construct allows automatic copying
504 of an expression assigned to a different column to be re-used::
506 >>> stmt = t.insert().values(x=func.foobar(3), y=from_dml_column(t.c.x) + 5)
507 >>> print(stmt)
508 INSERT INTO t (x, y) VALUES (foobar(:foobar_1), (foobar(:foobar_1) + :param_1))
510 The :func:`_sql.from_dml_column` construct is intended to be useful primarily
511 with event-based hooks such as those used by ORM hybrids.
513 .. seealso::
515 :ref:`hybrid_bulk_update`
517 .. versionadded:: 2.1
520 """ # noqa: E501
522 return DMLTargetCopy(column)
525def bindparam(
526 key: Optional[str],
527 value: Any = _NoArg.NO_ARG,
528 type_: Optional[_TypeEngineArgument[_T]] = None,
529 unique: bool = False,
530 required: Union[bool, Literal[_NoArg.NO_ARG]] = _NoArg.NO_ARG,
531 quote: Optional[bool] = None,
532 callable_: Optional[Callable[[], Any]] = None,
533 expanding: bool = False,
534 isoutparam: bool = False,
535 literal_execute: bool = False,
536) -> BindParameter[_T]:
537 r"""Produce a "bound expression".
539 The return value is an instance of :class:`.BindParameter`; this
540 is a :class:`_expression.ColumnElement`
541 subclass which represents a so-called
542 "placeholder" value in a SQL expression, the value of which is
543 supplied at the point at which the statement in executed against a
544 database connection.
546 In SQLAlchemy, the :func:`.bindparam` construct has
547 the ability to carry along the actual value that will be ultimately
548 used at expression time. In this way, it serves not just as
549 a "placeholder" for eventual population, but also as a means of
550 representing so-called "unsafe" values which should not be rendered
551 directly in a SQL statement, but rather should be passed along
552 to the :term:`DBAPI` as values which need to be correctly escaped
553 and potentially handled for type-safety.
555 When using :func:`.bindparam` explicitly, the use case is typically
556 one of traditional deferment of parameters; the :func:`.bindparam`
557 construct accepts a name which can then be referred to at execution
558 time::
560 from sqlalchemy import bindparam
562 stmt = select(users_table).where(
563 users_table.c.name == bindparam("username")
564 )
566 The above statement, when rendered, will produce SQL similar to:
568 .. sourcecode:: sql
570 SELECT id, name FROM user WHERE name = :username
572 In order to populate the value of ``:username`` above, the value
573 would typically be applied at execution time to a method
574 like :meth:`_engine.Connection.execute`::
576 result = connection.execute(stmt, {"username": "wendy"})
578 Explicit use of :func:`.bindparam` is also common when producing
579 UPDATE or DELETE statements that are to be invoked multiple times,
580 where the WHERE criterion of the statement is to change on each
581 invocation, such as::
583 stmt = (
584 users_table.update()
585 .where(user_table.c.name == bindparam("username"))
586 .values(fullname=bindparam("fullname"))
587 )
589 connection.execute(
590 stmt,
591 [
592 {"username": "wendy", "fullname": "Wendy Smith"},
593 {"username": "jack", "fullname": "Jack Jones"},
594 ],
595 )
597 SQLAlchemy's Core expression system makes wide use of
598 :func:`.bindparam` in an implicit sense. It is typical that Python
599 literal values passed to virtually all SQL expression functions are
600 coerced into fixed :func:`.bindparam` constructs. For example, given
601 a comparison operation such as::
603 expr = users_table.c.name == "Wendy"
605 The above expression will produce a :class:`.BinaryExpression`
606 construct, where the left side is the :class:`_schema.Column` object
607 representing the ``name`` column, and the right side is a
608 :class:`.BindParameter` representing the literal value::
610 print(repr(expr.right))
611 BindParameter("%(4327771088 name)s", "Wendy", type_=String())
613 The expression above will render SQL such as:
615 .. sourcecode:: sql
617 user.name = :name_1
619 Where the ``:name_1`` parameter name is an anonymous name. The
620 actual string ``Wendy`` is not in the rendered string, but is carried
621 along where it is later used within statement execution. If we
622 invoke a statement like the following::
624 stmt = select(users_table).where(users_table.c.name == "Wendy")
625 result = connection.execute(stmt)
627 We would see SQL logging output as:
629 .. sourcecode:: sql
631 SELECT "user".id, "user".name
632 FROM "user"
633 WHERE "user".name = %(name_1)s
634 {'name_1': 'Wendy'}
636 Above, we see that ``Wendy`` is passed as a parameter to the database,
637 while the placeholder ``:name_1`` is rendered in the appropriate form
638 for the target database, in this case the PostgreSQL database.
640 Similarly, :func:`.bindparam` is invoked automatically when working
641 with :term:`CRUD` statements as far as the "VALUES" portion is
642 concerned. The :func:`_expression.insert` construct produces an
643 ``INSERT`` expression which will, at statement execution time, generate
644 bound placeholders based on the arguments passed, as in::
646 stmt = users_table.insert()
647 result = connection.execute(stmt, {"name": "Wendy"})
649 The above will produce SQL output as:
651 .. sourcecode:: sql
653 INSERT INTO "user" (name) VALUES (%(name)s)
654 {'name': 'Wendy'}
656 The :class:`_expression.Insert` construct, at
657 compilation/execution time, rendered a single :func:`.bindparam`
658 mirroring the column name ``name`` as a result of the single ``name``
659 parameter we passed to the :meth:`_engine.Connection.execute` method.
661 :param key:
662 the key (e.g. the name) for this bind param.
663 Will be used in the generated
664 SQL statement for dialects that use named parameters. This
665 value may be modified when part of a compilation operation,
666 if other :class:`BindParameter` objects exist with the same
667 key, or if its length is too long and truncation is
668 required.
670 If omitted, an "anonymous" name is generated for the bound parameter;
671 when given a value to bind, the end result is equivalent to calling upon
672 the :func:`.literal` function with a value to bind, particularly
673 if the :paramref:`.bindparam.unique` parameter is also provided.
675 :param value:
676 Initial value for this bind param. Will be used at statement
677 execution time as the value for this parameter passed to the
678 DBAPI, if no other value is indicated to the statement execution
679 method for this particular parameter name. Defaults to ``None``.
681 :param callable\_:
682 A callable function that takes the place of "value". The function
683 will be called at statement execution time to determine the
684 ultimate value. Used for scenarios where the actual bind
685 value cannot be determined at the point at which the clause
686 construct is created, but embedded bind values are still desirable.
688 :param type\_:
689 A :class:`.TypeEngine` class or instance representing an optional
690 datatype for this :func:`.bindparam`. If not passed, a type
691 may be determined automatically for the bind, based on the given
692 value; for example, trivial Python types such as ``str``,
693 ``int``, ``bool``
694 may result in the :class:`.String`, :class:`.Integer` or
695 :class:`.Boolean` types being automatically selected.
697 The type of a :func:`.bindparam` is significant especially in that
698 the type will apply pre-processing to the value before it is
699 passed to the database. For example, a :func:`.bindparam` which
700 refers to a datetime value, and is specified as holding the
701 :class:`.DateTime` type, may apply conversion needed to the
702 value (such as stringification on SQLite) before passing the value
703 to the database.
705 :param unique:
706 if True, the key name of this :class:`.BindParameter` will be
707 modified if another :class:`.BindParameter` of the same name
708 already has been located within the containing
709 expression. This flag is used generally by the internals
710 when producing so-called "anonymous" bound expressions, it
711 isn't generally applicable to explicitly-named :func:`.bindparam`
712 constructs.
714 :param required:
715 If ``True``, a value is required at execution time. If not passed,
716 it defaults to ``True`` if neither :paramref:`.bindparam.value`
717 or :paramref:`.bindparam.callable` were passed. If either of these
718 parameters are present, then :paramref:`.bindparam.required`
719 defaults to ``False``.
721 :param quote:
722 True if this parameter name requires quoting and is not
723 currently known as a SQLAlchemy reserved word; this currently
724 only applies to the Oracle Database backends, where bound names must
725 sometimes be quoted.
727 :param isoutparam:
728 if True, the parameter should be treated like a stored procedure
729 "OUT" parameter. This applies to backends such as Oracle Database which
730 support OUT parameters.
732 :param expanding:
733 if True, this parameter will be treated as an "expanding" parameter
734 at execution time; the parameter value is expected to be a sequence,
735 rather than a scalar value, and the string SQL statement will
736 be transformed on a per-execution basis to accommodate the sequence
737 with a variable number of parameter slots passed to the DBAPI.
738 This is to allow statement caching to be used in conjunction with
739 an IN clause.
741 .. seealso::
743 :meth:`.ColumnOperators.in_`
745 :ref:`baked_in` - with baked queries
747 .. note:: The "expanding" feature does not support "executemany"-
748 style parameter sets.
750 :param literal_execute:
751 if True, the bound parameter will be rendered in the compile phase
752 with a special "POSTCOMPILE" token, and the SQLAlchemy compiler will
753 render the final value of the parameter into the SQL statement at
754 statement execution time, omitting the value from the parameter
755 dictionary / list passed to DBAPI ``cursor.execute()``. This
756 produces a similar effect as that of using the ``literal_binds``,
757 compilation flag, however takes place as the statement is sent to
758 the DBAPI ``cursor.execute()`` method, rather than when the statement
759 is compiled. The primary use of this
760 capability is for rendering LIMIT / OFFSET clauses for database
761 drivers that can't accommodate for bound parameters in these
762 contexts, while allowing SQL constructs to be cacheable at the
763 compilation level.
765 .. versionadded:: 1.4 Added "post compile" bound parameters
767 .. seealso::
769 :ref:`change_4808`.
771 .. seealso::
773 :ref:`tutorial_sending_parameters` - in the
774 :ref:`unified_tutorial`
777 """
778 return BindParameter(
779 key,
780 value,
781 type_,
782 unique,
783 required,
784 quote,
785 callable_,
786 expanding,
787 isoutparam,
788 literal_execute,
789 )
792def case(
793 *whens: Union[
794 typing_Tuple[_ColumnExpressionArgument[bool], Any], Mapping[Any, Any]
795 ],
796 value: Optional[Any] = None,
797 else_: Optional[Any] = None,
798) -> Case[Any]:
799 r"""Produce a ``CASE`` expression.
801 The ``CASE`` construct in SQL is a conditional object that
802 acts somewhat analogously to an "if/then" construct in other
803 languages. It returns an instance of :class:`.Case`.
805 :func:`.case` in its usual form is passed a series of "when"
806 constructs, that is, a list of conditions and results as tuples::
808 from sqlalchemy import case
810 stmt = select(users_table).where(
811 case(
812 (users_table.c.name == "wendy", "W"),
813 (users_table.c.name == "jack", "J"),
814 else_="E",
815 )
816 )
818 The above statement will produce SQL resembling:
820 .. sourcecode:: sql
822 SELECT id, name FROM user
823 WHERE CASE
824 WHEN (name = :name_1) THEN :param_1
825 WHEN (name = :name_2) THEN :param_2
826 ELSE :param_3
827 END
829 When simple equality expressions of several values against a single
830 parent column are needed, :func:`.case` also has a "shorthand" format
831 used via the
832 :paramref:`.case.value` parameter, which is passed a column
833 expression to be compared. In this form, the :paramref:`.case.whens`
834 parameter is passed as a dictionary containing expressions to be
835 compared against keyed to result expressions. The statement below is
836 equivalent to the preceding statement::
838 stmt = select(users_table).where(
839 case({"wendy": "W", "jack": "J"}, value=users_table.c.name, else_="E")
840 )
842 The values which are accepted as result values in
843 :paramref:`.case.whens` as well as with :paramref:`.case.else_` are
844 coerced from Python literals into :func:`.bindparam` constructs.
845 SQL expressions, e.g. :class:`_expression.ColumnElement` constructs,
846 are accepted
847 as well. To coerce a literal string expression into a constant
848 expression rendered inline, use the :func:`_expression.literal_column`
849 construct,
850 as in::
852 from sqlalchemy import case, literal_column
854 case(
855 (orderline.c.qty > 100, literal_column("'greaterthan100'")),
856 (orderline.c.qty > 10, literal_column("'greaterthan10'")),
857 else_=literal_column("'lessthan10'"),
858 )
860 The above will render the given constants without using bound
861 parameters for the result values (but still for the comparison
862 values), as in:
864 .. sourcecode:: sql
866 CASE
867 WHEN (orderline.qty > :qty_1) THEN 'greaterthan100'
868 WHEN (orderline.qty > :qty_2) THEN 'greaterthan10'
869 ELSE 'lessthan10'
870 END
872 :param \*whens: The criteria to be compared against,
873 :paramref:`.case.whens` accepts two different forms, based on
874 whether or not :paramref:`.case.value` is used.
876 .. versionchanged:: 1.4 the :func:`_sql.case`
877 function now accepts the series of WHEN conditions positionally
879 In the first form, it accepts multiple 2-tuples passed as positional
880 arguments; each 2-tuple consists of ``(<sql expression>, <value>)``,
881 where the SQL expression is a boolean expression and "value" is a
882 resulting value, e.g.::
884 case(
885 (users_table.c.name == "wendy", "W"),
886 (users_table.c.name == "jack", "J"),
887 )
889 In the second form, it accepts a Python dictionary of comparison
890 values mapped to a resulting value; this form requires
891 :paramref:`.case.value` to be present, and values will be compared
892 using the ``==`` operator, e.g.::
894 case({"wendy": "W", "jack": "J"}, value=users_table.c.name)
896 :param value: An optional SQL expression which will be used as a
897 fixed "comparison point" for candidate values within a dictionary
898 passed to :paramref:`.case.whens`.
900 :param else\_: An optional SQL expression which will be the evaluated
901 result of the ``CASE`` construct if all expressions within
902 :paramref:`.case.whens` evaluate to false. When omitted, most
903 databases will produce a result of NULL if none of the "when"
904 expressions evaluate to true.
907 """ # noqa: E501
908 return Case(*whens, value=value, else_=else_)
911def cast(
912 expression: _ColumnExpressionOrLiteralArgument[Any],
913 type_: _TypeEngineArgument[_T],
914) -> Cast[_T]:
915 r"""Produce a ``CAST`` expression.
917 :func:`.cast` returns an instance of :class:`.Cast`.
919 E.g.::
921 from sqlalchemy import cast, Numeric
923 stmt = select(cast(product_table.c.unit_price, Numeric(10, 4)))
925 The above statement will produce SQL resembling:
927 .. sourcecode:: sql
929 SELECT CAST(unit_price AS NUMERIC(10, 4)) FROM product
931 The :func:`.cast` function performs two distinct functions when
932 used. The first is that it renders the ``CAST`` expression within
933 the resulting SQL string. The second is that it associates the given
934 type (e.g. :class:`.TypeEngine` class or instance) with the column
935 expression on the Python side, which means the expression will take
936 on the expression operator behavior associated with that type,
937 as well as the bound-value handling and result-row-handling behavior
938 of the type.
940 An alternative to :func:`.cast` is the :func:`.type_coerce` function.
941 This function performs the second task of associating an expression
942 with a specific type, but does not render the ``CAST`` expression
943 in SQL.
945 :param expression: A SQL expression, such as a
946 :class:`_expression.ColumnElement`
947 expression or a Python string which will be coerced into a bound
948 literal value.
950 :param type\_: A :class:`.TypeEngine` class or instance indicating
951 the type to which the ``CAST`` should apply.
953 .. seealso::
955 :ref:`tutorial_casts`
957 :func:`.try_cast` - an alternative to CAST that results in
958 NULLs when the cast fails, instead of raising an error.
959 Only supported by some dialects.
961 :func:`.type_coerce` - an alternative to CAST that coerces the type
962 on the Python side only, which is often sufficient to generate the
963 correct SQL and data coercion.
966 """
967 return Cast(expression, type_)
970def try_cast(
971 expression: _ColumnExpressionOrLiteralArgument[Any],
972 type_: _TypeEngineArgument[_T],
973) -> TryCast[_T]:
974 """Produce a ``TRY_CAST`` expression for backends which support it;
975 this is a ``CAST`` which returns NULL for un-castable conversions.
977 In SQLAlchemy, this construct is supported **only** by the SQL Server
978 dialect, and will raise a :class:`.CompileError` if used on other
979 included backends. However, third party backends may also support
980 this construct.
982 .. tip:: As :func:`_sql.try_cast` originates from the SQL Server dialect,
983 it's importable both from ``sqlalchemy.`` as well as from
984 ``sqlalchemy.dialects.mssql``.
986 :func:`_sql.try_cast` returns an instance of :class:`.TryCast` and
987 generally behaves similarly to the :class:`.Cast` construct;
988 at the SQL level, the difference between ``CAST`` and ``TRY_CAST``
989 is that ``TRY_CAST`` returns NULL for an un-castable expression,
990 such as attempting to cast a string ``"hi"`` to an integer value.
992 E.g.::
994 from sqlalchemy import select, try_cast, Numeric
996 stmt = select(try_cast(product_table.c.unit_price, Numeric(10, 4)))
998 The above would render on Microsoft SQL Server as:
1000 .. sourcecode:: sql
1002 SELECT TRY_CAST (product_table.unit_price AS NUMERIC(10, 4))
1003 FROM product_table
1005 .. versionadded:: 2.0.14 :func:`.try_cast` has been
1006 generalized from the SQL Server dialect into a general use
1007 construct that may be supported by additional dialects.
1009 """
1010 return TryCast(expression, type_)
1013def column(
1014 text: str,
1015 type_: Optional[_TypeEngineArgument[_T]] = None,
1016 is_literal: bool = False,
1017 _selectable: Optional[FromClause] = None,
1018) -> ColumnClause[_T]:
1019 """Produce a :class:`.ColumnClause` object.
1021 The :class:`.ColumnClause` is a lightweight analogue to the
1022 :class:`_schema.Column` class. The :func:`_expression.column`
1023 function can
1024 be invoked with just a name alone, as in::
1026 from sqlalchemy import column
1028 id, name = column("id"), column("name")
1029 stmt = select(id, name).select_from("user")
1031 The above statement would produce SQL like:
1033 .. sourcecode:: sql
1035 SELECT id, name FROM user
1037 Once constructed, :func:`_expression.column`
1038 may be used like any other SQL
1039 expression element such as within :func:`_expression.select`
1040 constructs::
1042 from sqlalchemy.sql import column
1044 id, name = column("id"), column("name")
1045 stmt = select(id, name).select_from("user")
1047 The text handled by :func:`_expression.column`
1048 is assumed to be handled
1049 like the name of a database column; if the string contains mixed case,
1050 special characters, or matches a known reserved word on the target
1051 backend, the column expression will render using the quoting
1052 behavior determined by the backend. To produce a textual SQL
1053 expression that is rendered exactly without any quoting,
1054 use :func:`_expression.literal_column` instead,
1055 or pass ``True`` as the
1056 value of :paramref:`_expression.column.is_literal`. Additionally,
1057 full SQL
1058 statements are best handled using the :func:`_expression.text`
1059 construct.
1061 :func:`_expression.column` can be used in a table-like
1062 fashion by combining it with the :func:`.table` function
1063 (which is the lightweight analogue to :class:`_schema.Table`
1064 ) to produce
1065 a working table construct with minimal boilerplate::
1067 from sqlalchemy import table, column, select
1069 user = table(
1070 "user",
1071 column("id"),
1072 column("name"),
1073 column("description"),
1074 )
1076 stmt = select(user.c.description).where(user.c.name == "wendy")
1078 A :func:`_expression.column` / :func:`.table`
1079 construct like that illustrated
1080 above can be created in an
1081 ad-hoc fashion and is not associated with any
1082 :class:`_schema.MetaData`, DDL, or events, unlike its
1083 :class:`_schema.Table` counterpart.
1085 :param text: the text of the element.
1087 :param type: :class:`_types.TypeEngine` object which can associate
1088 this :class:`.ColumnClause` with a type.
1090 :param is_literal: if True, the :class:`.ColumnClause` is assumed to
1091 be an exact expression that will be delivered to the output with no
1092 quoting rules applied regardless of case sensitive settings. the
1093 :func:`_expression.literal_column()` function essentially invokes
1094 :func:`_expression.column` while passing ``is_literal=True``.
1096 .. seealso::
1098 :class:`_schema.Column`
1100 :func:`_expression.literal_column`
1102 :func:`.table`
1104 :func:`_expression.text`
1106 :ref:`tutorial_select_arbitrary_text`
1108 """
1109 return ColumnClause(text, type_, is_literal, _selectable)
1112@overload
1113def desc(
1114 column: Union[str, "ColumnElement[_T]"],
1115) -> UnaryExpression[_T]: ...
1118@overload
1119def desc(
1120 column: _ColumnExpressionOrStrLabelArgument[_T],
1121) -> Union[OrderByList, UnaryExpression[_T]]: ...
1124def desc(
1125 column: _ColumnExpressionOrStrLabelArgument[_T],
1126) -> Union[OrderByList, UnaryExpression[_T]]:
1127 """Produce a descending ``ORDER BY`` clause element.
1129 e.g.::
1131 from sqlalchemy import desc
1133 stmt = select(users_table).order_by(desc(users_table.c.name))
1135 will produce SQL as:
1137 .. sourcecode:: sql
1139 SELECT id, name FROM user ORDER BY name DESC
1141 The :func:`.desc` function is a standalone version of the
1142 :meth:`_expression.ColumnElement.desc`
1143 method available on all SQL expressions,
1144 e.g.::
1147 stmt = select(users_table).order_by(users_table.c.name.desc())
1149 :param column: A :class:`_expression.ColumnElement` (e.g.
1150 scalar SQL expression)
1151 with which to apply the :func:`.desc` operation.
1153 .. seealso::
1155 :func:`.asc`
1157 :func:`.nulls_first`
1159 :func:`.nulls_last`
1161 :meth:`_expression.Select.order_by`
1163 """
1164 if isinstance(column, operators.OrderingOperators):
1165 return column.desc() # type: ignore[unused-ignore]
1166 else:
1167 return UnaryExpression._create_desc(column)
1170def distinct(expr: _ColumnExpressionArgument[_T]) -> UnaryExpression[_T]:
1171 """Produce an column-expression-level unary ``DISTINCT`` clause.
1173 This applies the ``DISTINCT`` keyword to an **individual column
1174 expression** (e.g. not the whole statement), and renders **specifically
1175 in that column position**; this is used for containment within
1176 an aggregate function, as in::
1178 from sqlalchemy import distinct, func
1180 stmt = select(users_table.c.id, func.count(distinct(users_table.c.name)))
1182 The above would produce an statement resembling:
1184 .. sourcecode:: sql
1186 SELECT user.id, count(DISTINCT user.name) FROM user
1188 .. tip:: The :func:`_sql.distinct` function does **not** apply DISTINCT
1189 to the full SELECT statement, instead applying a DISTINCT modifier
1190 to **individual column expressions**. For general ``SELECT DISTINCT``
1191 support, use the
1192 :meth:`_sql.Select.distinct` method on :class:`_sql.Select`.
1194 The :func:`.distinct` function is also available as a column-level
1195 method, e.g. :meth:`_expression.ColumnElement.distinct`, as in::
1197 stmt = select(func.count(users_table.c.name.distinct()))
1199 The :func:`.distinct` operator is different from the
1200 :meth:`_expression.Select.distinct` method of
1201 :class:`_expression.Select`,
1202 which produces a ``SELECT`` statement
1203 with ``DISTINCT`` applied to the result set as a whole,
1204 e.g. a ``SELECT DISTINCT`` expression. See that method for further
1205 information.
1207 .. seealso::
1209 :meth:`_expression.ColumnElement.distinct`
1211 :meth:`_expression.Select.distinct`
1213 :data:`.func`
1215 """ # noqa: E501
1216 if isinstance(expr, operators.ColumnOperators):
1217 return expr.distinct()
1218 else:
1219 return UnaryExpression._create_distinct(expr)
1222def bitwise_not(expr: _ColumnExpressionArgument[_T]) -> UnaryExpression[_T]:
1223 """Produce a unary bitwise NOT clause, typically via the ``~`` operator.
1225 Not to be confused with boolean negation :func:`_sql.not_`.
1227 .. versionadded:: 2.0.2
1229 .. seealso::
1231 :ref:`operators_bitwise`
1234 """
1235 if isinstance(expr, operators.ColumnOperators):
1236 return expr.bitwise_not()
1237 else:
1238 return UnaryExpression._create_bitwise_not(expr)
1241def extract(field: str, expr: _ColumnExpressionArgument[Any]) -> Extract:
1242 """Return a :class:`.Extract` construct.
1244 This is typically available as :func:`.extract`
1245 as well as ``func.extract`` from the
1246 :data:`.func` namespace.
1248 :param field: The field to extract.
1250 .. warning:: This field is used as a literal SQL string.
1251 **DO NOT PASS UNTRUSTED INPUT TO THIS STRING**.
1253 :param expr: A column or Python scalar expression serving as the
1254 right side of the ``EXTRACT`` expression.
1256 E.g.::
1258 from sqlalchemy import extract
1259 from sqlalchemy import table, column
1261 logged_table = table(
1262 "user",
1263 column("id"),
1264 column("date_created"),
1265 )
1267 stmt = select(logged_table.c.id).where(
1268 extract("YEAR", logged_table.c.date_created) == 2021
1269 )
1271 In the above example, the statement is used to select ids from the
1272 database where the ``YEAR`` component matches a specific value.
1274 Similarly, one can also select an extracted component::
1276 stmt = select(extract("YEAR", logged_table.c.date_created)).where(
1277 logged_table.c.id == 1
1278 )
1280 The implementation of ``EXTRACT`` may vary across database backends.
1281 Users are reminded to consult their database documentation.
1282 """
1283 return Extract(field, expr)
1286def false() -> False_:
1287 """Return a :class:`.False_` construct.
1289 E.g.:
1291 .. sourcecode:: pycon+sql
1293 >>> from sqlalchemy import false
1294 >>> print(select(t.c.x).where(false()))
1295 {printsql}SELECT x FROM t WHERE false
1297 A backend which does not support true/false constants will render as
1298 an expression against 1 or 0:
1300 .. sourcecode:: pycon+sql
1302 >>> print(select(t.c.x).where(false()))
1303 {printsql}SELECT x FROM t WHERE 0 = 1
1305 The :func:`.true` and :func:`.false` constants also feature
1306 "short circuit" operation within an :func:`.and_` or :func:`.or_`
1307 conjunction:
1309 .. sourcecode:: pycon+sql
1311 >>> print(select(t.c.x).where(or_(t.c.x > 5, true())))
1312 {printsql}SELECT x FROM t WHERE true{stop}
1314 >>> print(select(t.c.x).where(and_(t.c.x > 5, false())))
1315 {printsql}SELECT x FROM t WHERE false{stop}
1317 .. seealso::
1319 :func:`.true`
1321 """
1323 return False_._instance()
1326def funcfilter(
1327 func: FunctionElement[_T], *criterion: _ColumnExpressionArgument[bool]
1328) -> FunctionFilter[_T]:
1329 """Produce a :class:`.FunctionFilter` object against a function.
1331 Used against aggregate and window functions,
1332 for database backends that support the "FILTER" clause.
1334 E.g.::
1336 from sqlalchemy import funcfilter
1338 funcfilter(func.count(1), MyClass.name == "some name")
1340 Would produce "COUNT(1) FILTER (WHERE myclass.name = 'some name')".
1342 This function is also available from the :data:`~.expression.func`
1343 construct itself via the :meth:`.FunctionElement.filter` method.
1345 .. seealso::
1347 :ref:`tutorial_functions_within_group` - in the
1348 :ref:`unified_tutorial`
1350 :meth:`.FunctionElement.filter`
1352 """
1353 return FunctionFilter(func, *criterion)
1356def label(
1357 name: str,
1358 element: _ColumnExpressionArgument[_T],
1359 type_: Optional[_TypeEngineArgument[_T]] = None,
1360) -> Label[_T]:
1361 """Return a :class:`Label` object for the
1362 given :class:`_expression.ColumnElement`.
1364 A label changes the name of an element in the columns clause of a
1365 ``SELECT`` statement, typically via the ``AS`` SQL keyword.
1367 This functionality is more conveniently available via the
1368 :meth:`_expression.ColumnElement.label` method on
1369 :class:`_expression.ColumnElement`.
1371 :param name: label name
1373 :param obj: a :class:`_expression.ColumnElement`.
1375 """
1376 return Label(name, element, type_)
1379def null() -> Null:
1380 """Return a constant :class:`.Null` construct."""
1382 return Null._instance()
1385@overload
1386def nulls_first(
1387 column: "ColumnElement[_T]",
1388) -> UnaryExpression[_T]: ...
1391@overload
1392def nulls_first(
1393 column: _ColumnExpressionArgument[_T],
1394) -> Union[OrderByList, UnaryExpression[_T]]: ...
1397def nulls_first(
1398 column: _ColumnExpressionArgument[_T],
1399) -> Union[OrderByList, UnaryExpression[_T]]:
1400 """Produce the ``NULLS FIRST`` modifier for an ``ORDER BY`` expression.
1402 :func:`.nulls_first` is intended to modify the expression produced
1403 by :func:`.asc` or :func:`.desc`, and indicates how NULL values
1404 should be handled when they are encountered during ordering::
1407 from sqlalchemy import desc, nulls_first
1409 stmt = select(users_table).order_by(nulls_first(desc(users_table.c.name)))
1411 The SQL expression from the above would resemble:
1413 .. sourcecode:: sql
1415 SELECT id, name FROM user ORDER BY name DESC NULLS FIRST
1417 Like :func:`.asc` and :func:`.desc`, :func:`.nulls_first` is typically
1418 invoked from the column expression itself using
1419 :meth:`_expression.ColumnElement.nulls_first`,
1420 rather than as its standalone
1421 function version, as in::
1423 stmt = select(users_table).order_by(
1424 users_table.c.name.desc().nulls_first()
1425 )
1427 .. versionchanged:: 1.4 :func:`.nulls_first` is renamed from
1428 :func:`.nullsfirst` in previous releases.
1429 The previous name remains available for backwards compatibility.
1431 .. seealso::
1433 :func:`.asc`
1435 :func:`.desc`
1437 :func:`.nulls_last`
1439 :meth:`_expression.Select.order_by`
1441 """ # noqa: E501
1442 if isinstance(column, operators.OrderingOperators):
1443 return column.nulls_first()
1444 else:
1445 return UnaryExpression._create_nulls_first(column)
1448@overload
1449def nulls_last(
1450 column: "ColumnElement[_T]",
1451) -> UnaryExpression[_T]: ...
1454@overload
1455def nulls_last(
1456 column: _ColumnExpressionArgument[_T],
1457) -> Union[OrderByList, UnaryExpression[_T]]: ...
1460def nulls_last(
1461 column: _ColumnExpressionArgument[_T],
1462) -> Union[OrderByList, UnaryExpression[_T]]:
1463 """Produce the ``NULLS LAST`` modifier for an ``ORDER BY`` expression.
1465 :func:`.nulls_last` is intended to modify the expression produced
1466 by :func:`.asc` or :func:`.desc`, and indicates how NULL values
1467 should be handled when they are encountered during ordering::
1470 from sqlalchemy import desc, nulls_last
1472 stmt = select(users_table).order_by(nulls_last(desc(users_table.c.name)))
1474 The SQL expression from the above would resemble:
1476 .. sourcecode:: sql
1478 SELECT id, name FROM user ORDER BY name DESC NULLS LAST
1480 Like :func:`.asc` and :func:`.desc`, :func:`.nulls_last` is typically
1481 invoked from the column expression itself using
1482 :meth:`_expression.ColumnElement.nulls_last`,
1483 rather than as its standalone
1484 function version, as in::
1486 stmt = select(users_table).order_by(users_table.c.name.desc().nulls_last())
1488 .. versionchanged:: 1.4 :func:`.nulls_last` is renamed from
1489 :func:`.nullslast` in previous releases.
1490 The previous name remains available for backwards compatibility.
1492 .. seealso::
1494 :func:`.asc`
1496 :func:`.desc`
1498 :func:`.nulls_first`
1500 :meth:`_expression.Select.order_by`
1502 """ # noqa: E501
1503 if isinstance(column, operators.OrderingOperators):
1504 return column.nulls_last()
1505 else:
1506 return UnaryExpression._create_nulls_last(column)
1509def or_( # type: ignore[empty-body]
1510 initial_clause: Union[Literal[False], _ColumnExpressionArgument[bool]],
1511 *clauses: _ColumnExpressionArgument[bool],
1512) -> ColumnElement[bool]:
1513 """Produce a conjunction of expressions joined by ``OR``.
1515 E.g.::
1517 from sqlalchemy import or_
1519 stmt = select(users_table).where(
1520 or_(users_table.c.name == "wendy", users_table.c.name == "jack")
1521 )
1523 The :func:`.or_` conjunction is also available using the
1524 Python ``|`` operator (though note that compound expressions
1525 need to be parenthesized in order to function with Python
1526 operator precedence behavior)::
1528 stmt = select(users_table).where(
1529 (users_table.c.name == "wendy") | (users_table.c.name == "jack")
1530 )
1532 The :func:`.or_` construct must be given at least one positional
1533 argument in order to be valid; a :func:`.or_` construct with no
1534 arguments is ambiguous. To produce an "empty" or dynamically
1535 generated :func:`.or_` expression, from a given list of expressions,
1536 a "default" element of :func:`_sql.false` (or just ``False``) should be
1537 specified::
1539 from sqlalchemy import false
1541 or_criteria = or_(false(), *expressions)
1543 The above expression will compile to SQL as the expression ``false``
1544 or ``0 = 1``, depending on backend, if no other expressions are
1545 present. If expressions are present, then the :func:`_sql.false` value is
1546 ignored as it does not affect the outcome of an OR expression which
1547 has other elements.
1549 .. deprecated:: 1.4 The :func:`.or_` element now requires that at
1550 least one argument is passed; creating the :func:`.or_` construct
1551 with no arguments is deprecated, and will emit a deprecation warning
1552 while continuing to produce a blank SQL string.
1554 .. seealso::
1556 :func:`.and_`
1558 """
1559 ...
1562if not TYPE_CHECKING:
1563 # handle deprecated case which allows zero-arguments
1564 def or_(*clauses): # noqa: F811
1565 """Produce a conjunction of expressions joined by ``OR``.
1567 E.g.::
1569 from sqlalchemy import or_
1571 stmt = select(users_table).where(
1572 or_(users_table.c.name == "wendy", users_table.c.name == "jack")
1573 )
1575 The :func:`.or_` conjunction is also available using the
1576 Python ``|`` operator (though note that compound expressions
1577 need to be parenthesized in order to function with Python
1578 operator precedence behavior)::
1580 stmt = select(users_table).where(
1581 (users_table.c.name == "wendy") | (users_table.c.name == "jack")
1582 )
1584 The :func:`.or_` construct must be given at least one positional
1585 argument in order to be valid; a :func:`.or_` construct with no
1586 arguments is ambiguous. To produce an "empty" or dynamically
1587 generated :func:`.or_` expression, from a given list of expressions,
1588 a "default" element of :func:`_sql.false` (or just ``False``) should be
1589 specified::
1591 from sqlalchemy import false
1593 or_criteria = or_(false(), *expressions)
1595 The above expression will compile to SQL as the expression ``false``
1596 or ``0 = 1``, depending on backend, if no other expressions are
1597 present. If expressions are present, then the :func:`_sql.false` value
1598 is ignored as it does not affect the outcome of an OR expression which
1599 has other elements.
1601 .. deprecated:: 1.4 The :func:`.or_` element now requires that at
1602 least one argument is passed; creating the :func:`.or_` construct
1603 with no arguments is deprecated, and will emit a deprecation warning
1604 while continuing to produce a blank SQL string.
1606 .. seealso::
1608 :func:`.and_`
1610 """ # noqa: E501
1611 return BooleanClauseList.or_(*clauses)
1614def over(
1615 element: FunctionElement[_T],
1616 partition_by: _ByArgument | None = None,
1617 order_by: _ByArgument | None = None,
1618 range_: _FrameIntTuple | FrameClause | None = None,
1619 rows: _FrameIntTuple | FrameClause | None = None,
1620 groups: _FrameIntTuple | FrameClause | None = None,
1621) -> Over[_T]:
1622 r"""Produce an :class:`.Over` object against a function.
1624 Used against aggregate or so-called "window" functions,
1625 for database backends that support window functions.
1627 :func:`_expression.over` is usually called using
1628 the :meth:`.FunctionElement.over` method, e.g.::
1630 func.row_number().over(order_by=mytable.c.some_column)
1632 Would produce:
1634 .. sourcecode:: sql
1636 ROW_NUMBER() OVER(ORDER BY some_column)
1638 Ranges are also possible using the :paramref:`.expression.over.range_`,
1639 :paramref:`.expression.over.rows`, and :paramref:`.expression.over.groups`
1640 parameters. These
1641 mutually-exclusive parameters each accept a 2-tuple, which contains
1642 a combination of integers and None::
1644 func.row_number().over(order_by=my_table.c.some_column, range_=(None, 0))
1646 The above would produce:
1648 .. sourcecode:: sql
1650 ROW_NUMBER() OVER(ORDER BY some_column
1651 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
1653 A value of ``None`` indicates "unbounded", a
1654 value of zero indicates "current row", and negative / positive
1655 integers indicate "preceding" and "following":
1657 * RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING::
1659 func.row_number().over(order_by="x", range_=(-5, 10))
1661 * ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW::
1663 func.row_number().over(order_by="x", rows=(None, 0))
1665 * RANGE BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING::
1667 func.row_number().over(order_by="x", range_=(-2, None))
1669 * RANGE BETWEEN 1 FOLLOWING AND 3 FOLLOWING::
1671 func.row_number().over(order_by="x", range_=(1, 3))
1673 * GROUPS BETWEEN 1 FOLLOWING AND 3 FOLLOWING::
1675 func.row_number().over(order_by="x", groups=(1, 3))
1677 Depending on the type of the order column, the 'RANGE' value may not be
1678 an integer. In this case use a :class:`_expression.FrameClause` directly
1679 to specify the frame boundaries. E.g.::
1681 from datetime import timedelta
1682 from sqlalchemy import FrameClause, FrameClauseType
1684 func.sum(my_table.c.amount).over(
1685 order_by=my_table.c.date,
1686 range_=FrameClause(
1687 start=timedelta(days=7),
1688 end=None,
1689 start_frame_type=FrameClauseType.PRECEDING,
1690 end_frame_type=FrameClauseType.UNBOUNDED,
1691 ),
1692 )
1694 .. versionchanged:: 2.1 Added support for range types that are not
1695 integer-based, via the :class:`_expression.FrameClause` construct.
1697 :param element: a :class:`.FunctionElement`, :class:`.WithinGroup`,
1698 or other compatible construct.
1699 :param partition_by: a column element or string, or a list
1700 of such, that will be used as the PARTITION BY clause
1701 of the OVER construct.
1702 :param order_by: a column element or string, or a list
1703 of such, that will be used as the ORDER BY clause
1704 of the OVER construct.
1705 :param range\_: optional range clause for the window. This is a
1706 two-tuple value which can contain integer values or ``None``,
1707 and will render a RANGE BETWEEN PRECEDING / FOLLOWING clause.
1708 Can also be a :class:`_expression.FrameClause` instance to
1709 specify non-integer values.
1711 .. versionchanged:: 2.1 Added support for range types that are not
1712 integer-based, via the :class:`_expression.FrameClause` construct.
1714 :param rows: optional rows clause for the window. This is a two-tuple
1715 value which can contain integer values or None, and will render
1716 a ROWS BETWEEN PRECEDING / FOLLOWING clause. Can also be a
1717 :class:`_expression.FrameClause` instance.
1718 :param groups: optional groups clause for the window. This is a
1719 two-tuple value which can contain integer values or ``None``,
1720 and will render a GROUPS BETWEEN PRECEDING / FOLLOWING clause.
1721 Can also be a :class:`_expression.FrameClause` instance.
1723 .. versionadded:: 2.0.40
1725 This function is also available from the :data:`~.expression.func`
1726 construct itself via the :meth:`.FunctionElement.over` method.
1728 .. seealso::
1730 :ref:`tutorial_window_functions` - in the :ref:`unified_tutorial`
1732 :data:`.expression.func`
1734 :func:`_expression.within_group`
1736 """ # noqa: E501
1737 return Over(element, partition_by, order_by, range_, rows, groups)
1740@_document_text_coercion("text", ":func:`.text`", ":paramref:`.text.text`")
1741def text(text: str) -> TextClause:
1742 r"""Construct a new :class:`_expression.TextClause` clause,
1743 representing
1744 a textual SQL string directly.
1746 E.g.::
1748 from sqlalchemy import text
1750 t = text("SELECT * FROM users")
1751 result = connection.execute(t)
1753 The advantages :func:`_expression.text`
1754 provides over a plain string are
1755 backend-neutral support for bind parameters, per-statement
1756 execution options, as well as
1757 bind parameter and result-column typing behavior, allowing
1758 SQLAlchemy type constructs to play a role when executing
1759 a statement that is specified literally. The construct can also
1760 be provided with a ``.c`` collection of column elements, allowing
1761 it to be embedded in other SQL expression constructs as a subquery.
1763 Bind parameters are specified by name, using the format ``:name``.
1764 E.g.::
1766 t = text("SELECT * FROM users WHERE id=:user_id")
1767 result = connection.execute(t, {"user_id": 12})
1769 For SQL statements where a colon is required verbatim, as within
1770 an inline string, use a backslash to escape::
1772 t = text(r"SELECT * FROM users WHERE name='\:username'")
1774 The :class:`_expression.TextClause`
1775 construct includes methods which can
1776 provide information about the bound parameters as well as the column
1777 values which would be returned from the textual statement, assuming
1778 it's an executable SELECT type of statement. The
1779 :meth:`_expression.TextClause.bindparams`
1780 method is used to provide bound
1781 parameter detail, and :meth:`_expression.TextClause.columns`
1782 method allows
1783 specification of return columns including names and types::
1785 t = (
1786 text("SELECT * FROM users WHERE id=:user_id")
1787 .bindparams(user_id=7)
1788 .columns(id=Integer, name=String)
1789 )
1791 for id, name in connection.execute(t):
1792 print(id, name)
1794 The :func:`_expression.text` construct is used in cases when
1795 a literal string SQL fragment is specified as part of a larger query,
1796 such as for the WHERE clause of a SELECT statement::
1798 s = select(users.c.id, users.c.name).where(text("id=:user_id"))
1799 result = connection.execute(s, {"user_id": 12})
1801 :func:`_expression.text` is also used for the construction
1802 of a full, standalone statement using plain text.
1803 As such, SQLAlchemy refers
1804 to it as an :class:`.Executable` object and may be used
1805 like any other statement passed to an ``.execute()`` method.
1807 :param text:
1808 the text of the SQL statement to be created. Use ``:<param>``
1809 to specify bind parameters; they will be compiled to their
1810 engine-specific format.
1812 .. seealso::
1814 :ref:`tutorial_select_arbitrary_text`
1816 """
1817 return TextClause(text)
1820def true() -> True_:
1821 """Return a constant :class:`.True_` construct.
1823 E.g.:
1825 .. sourcecode:: pycon+sql
1827 >>> from sqlalchemy import true
1828 >>> print(select(t.c.x).where(true()))
1829 {printsql}SELECT x FROM t WHERE true
1831 A backend which does not support true/false constants will render as
1832 an expression against 1 or 0:
1834 .. sourcecode:: pycon+sql
1836 >>> print(select(t.c.x).where(true()))
1837 {printsql}SELECT x FROM t WHERE 1 = 1
1839 The :func:`.true` and :func:`.false` constants also feature
1840 "short circuit" operation within an :func:`.and_` or :func:`.or_`
1841 conjunction:
1843 .. sourcecode:: pycon+sql
1845 >>> print(select(t.c.x).where(or_(t.c.x > 5, true())))
1846 {printsql}SELECT x FROM t WHERE true{stop}
1848 >>> print(select(t.c.x).where(and_(t.c.x > 5, false())))
1849 {printsql}SELECT x FROM t WHERE false{stop}
1851 .. seealso::
1853 :func:`.false`
1855 """
1857 return True_._instance()
1860def tuple_(
1861 *clauses: _ColumnExpressionOrLiteralArgument[Any],
1862 types: Optional[Sequence[_TypeEngineArgument[Any]]] = None,
1863) -> Tuple:
1864 """Return a :class:`.Tuple`.
1866 Main usage is to produce a composite IN construct using
1867 :meth:`.ColumnOperators.in_` ::
1869 from sqlalchemy import tuple_
1871 tuple_(table.c.col1, table.c.col2).in_([(1, 2), (5, 12), (10, 19)])
1873 .. warning::
1875 The composite IN construct is not supported by all backends, and is
1876 currently known to work on PostgreSQL, MySQL, and SQLite.
1877 Unsupported backends will raise a subclass of
1878 :class:`~sqlalchemy.exc.DBAPIError` when such an expression is
1879 invoked.
1881 """
1882 return Tuple(*clauses, types=types)
1885def type_coerce(
1886 expression: _ColumnExpressionOrLiteralArgument[Any],
1887 type_: _TypeEngineArgument[_T],
1888) -> TypeCoerce[_T]:
1889 r"""Associate a SQL expression with a particular type, without rendering
1890 ``CAST``.
1892 E.g.::
1894 from sqlalchemy import type_coerce
1896 stmt = select(type_coerce(log_table.date_string, StringDateTime()))
1898 The above construct will produce a :class:`.TypeCoerce` object, which
1899 does not modify the rendering in any way on the SQL side, with the
1900 possible exception of a generated label if used in a columns clause
1901 context:
1903 .. sourcecode:: sql
1905 SELECT date_string AS date_string FROM log
1907 When result rows are fetched, the ``StringDateTime`` type processor
1908 will be applied to result rows on behalf of the ``date_string`` column.
1910 .. note:: the :func:`.type_coerce` construct does not render any
1911 SQL syntax of its own, including that it does not imply
1912 parenthesization. Please use :meth:`.TypeCoerce.self_group`
1913 if explicit parenthesization is required.
1915 In order to provide a named label for the expression, use
1916 :meth:`_expression.ColumnElement.label`::
1918 stmt = select(
1919 type_coerce(log_table.date_string, StringDateTime()).label("date")
1920 )
1922 A type that features bound-value handling will also have that behavior
1923 take effect when literal values or :func:`.bindparam` constructs are
1924 passed to :func:`.type_coerce` as targets.
1925 For example, if a type implements the
1926 :meth:`.TypeEngine.bind_expression`
1927 method or :meth:`.TypeEngine.bind_processor` method or equivalent,
1928 these functions will take effect at statement compilation/execution
1929 time when a literal value is passed, as in::
1931 # bound-value handling of MyStringType will be applied to the
1932 # literal value "some string"
1933 stmt = select(type_coerce("some string", MyStringType))
1935 When using :func:`.type_coerce` with composed expressions, note that
1936 **parenthesis are not applied**. If :func:`.type_coerce` is being
1937 used in an operator context where the parenthesis normally present from
1938 CAST are necessary, use the :meth:`.TypeCoerce.self_group` method:
1940 .. sourcecode:: pycon+sql
1942 >>> some_integer = column("someint", Integer)
1943 >>> some_string = column("somestr", String)
1944 >>> expr = type_coerce(some_integer + 5, String) + some_string
1945 >>> print(expr)
1946 {printsql}someint + :someint_1 || somestr{stop}
1947 >>> expr = type_coerce(some_integer + 5, String).self_group() + some_string
1948 >>> print(expr)
1949 {printsql}(someint + :someint_1) || somestr{stop}
1951 :param expression: A SQL expression, such as a
1952 :class:`_expression.ColumnElement`
1953 expression or a Python string which will be coerced into a bound
1954 literal value.
1956 :param type\_: A :class:`.TypeEngine` class or instance indicating
1957 the type to which the expression is coerced.
1959 .. seealso::
1961 :ref:`tutorial_casts`
1963 :func:`.cast`
1965 """ # noqa
1966 return TypeCoerce(expression, type_)
1969def within_group(
1970 element: FunctionElement[_T], *order_by: _ColumnExpressionArgument[Any]
1971) -> WithinGroup[_T]:
1972 r"""Produce a :class:`.WithinGroup` object against a function.
1974 Used against so-called "ordered set aggregate" and "hypothetical
1975 set aggregate" functions, including :class:`.percentile_cont`,
1976 :class:`.rank`, :class:`.dense_rank`, etc. This feature is typically
1977 used by Oracle Database, Microsoft SQL Server.
1979 For generalized ORDER BY of aggregate functions on all included
1980 backends, including PostgreSQL, MySQL/MariaDB, SQLite as well as Oracle
1981 and SQL Server, the :func:`_sql.aggregate_order_by` provides a more
1982 general approach that compiles to "WITHIN GROUP" only on those backends
1983 which require it.
1985 :func:`_expression.within_group` is usually called using
1986 the :meth:`.FunctionElement.within_group` method, e.g.::
1988 stmt = select(
1989 func.percentile_cont(0.5).within_group(department.c.salary.desc()),
1990 )
1992 The above statement would produce SQL similar to
1993 ``SELECT percentile_cont(0.5)
1994 WITHIN GROUP (ORDER BY department.salary DESC)``.
1996 :param element: a :class:`.FunctionElement` construct, typically
1997 generated by :data:`~.expression.func`.
1998 :param \*order_by: one or more column elements that will be used
1999 as the ORDER BY clause of the WITHIN GROUP construct.
2001 .. seealso::
2003 :ref:`tutorial_functions_within_group` - in the
2004 :ref:`unified_tutorial`
2006 :func:`_sql.aggregate_order_by` - helper for PostgreSQL, MySQL,
2007 SQLite aggregate functions
2009 :data:`.expression.func`
2011 :func:`_expression.over`
2013 """
2014 return WithinGroup(element, *order_by)
2017def aggregate_order_by(
2018 element: FunctionElement[_T], *order_by: _ColumnExpressionArgument[Any]
2019) -> AggregateOrderBy[_T]:
2020 r"""Produce a :class:`.AggregateOrderBy` object against a function.
2022 Used for aggregating functions such as :class:`_functions.array_agg`,
2023 ``group_concat``, ``json_agg`` on backends that support ordering via an
2024 embedded ``ORDER BY`` parameter, e.g. PostgreSQL, MySQL/MariaDB, SQLite.
2025 When used on backends like Oracle and SQL Server, SQL compilation uses that
2026 of :class:`.WithinGroup`. On PostgreSQL, compilation is fixed at embedded
2027 ``ORDER BY``; for set aggregation functions where PostgreSQL requires the
2028 use of ``WITHIN GROUP``, :func:`_expression.within_group` should be used
2029 explicitly.
2031 :func:`_expression.aggregate_order_by` is usually called using
2032 the :meth:`.FunctionElement.aggregate_order_by` method, e.g.::
2034 stmt = select(
2035 func.array_agg(department.c.code).aggregate_order_by(
2036 department.c.code.desc()
2037 ),
2038 )
2040 which would produce an expression resembling:
2042 .. sourcecode:: sql
2044 SELECT array_agg(department.code ORDER BY department.code DESC)
2045 AS array_agg_1 FROM department
2047 The ORDER BY argument may also be multiple terms.
2049 When using the backend-agnostic :class:`_functions.aggregate_strings`
2050 string aggregation function, use the
2051 :paramref:`_functions.aggregate_strings.order_by` parameter to indicate a
2052 dialect-agnostic ORDER BY expression.
2054 .. versionadded:: 2.1 Generalized the PostgreSQL-specific
2055 :func:`_postgresql.aggregate_order_by` function to a method on
2056 :class:`.Function` that is backend agnostic.
2058 .. seealso::
2060 :class:`_functions.aggregate_strings` - backend-agnostic string
2061 concatenation function which also supports ORDER BY
2063 """ # noqa: E501
2064 return AggregateOrderBy(element, *order_by)