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 BinaryExpression
62 from .selectable import FromClause
63 from .type_api import TypeEngine
65_T = TypeVar("_T")
68def all_(expr: _ColumnExpressionArgument[_T]) -> CollectionAggregate[bool]:
69 """Produce an ALL expression.
71 For dialects such as that of PostgreSQL, this operator applies
72 to usage of the :class:`_types.ARRAY` datatype, for that of
73 MySQL, it may apply to a subquery. e.g.::
75 # renders on PostgreSQL:
76 # '5 = ALL (somearray)'
77 expr = 5 == all_(mytable.c.somearray)
79 # renders on MySQL:
80 # '5 = ALL (SELECT value FROM table)'
81 expr = 5 == all_(select(table.c.value))
83 Comparison to NULL may work using ``None``::
85 None == all_(mytable.c.somearray)
87 The any_() / all_() operators also feature a special "operand flipping"
88 behavior such that if any_() / all_() are used on the left side of a
89 comparison using a standalone operator such as ``==``, ``!=``, etc.
90 (not including operator methods such as
91 :meth:`_sql.ColumnOperators.is_`) the rendered expression is flipped::
93 # would render '5 = ALL (column)`
94 all_(mytable.c.column) == 5
96 Or with ``None``, which note will not perform
97 the usual step of rendering "IS" as is normally the case for NULL::
99 # would render 'NULL = ALL(somearray)'
100 all_(mytable.c.somearray) == None
102 The column-level :meth:`_sql.ColumnElement.all_` method (not to be
103 confused with the deprecated :class:`_types.ARRAY` level
104 :meth:`_types.ARRAY.Comparator.all`) is shorthand for
105 ``all_(col)``::
107 5 == mytable.c.somearray.all_()
109 .. seealso::
111 :meth:`_sql.ColumnOperators.all_`
113 :func:`_expression.any_`
115 """
116 if isinstance(expr, operators.ColumnOperators):
117 return expr.all_()
118 else:
119 return CollectionAggregate._create_all(expr)
122def and_( # type: ignore[empty-body]
123 initial_clause: Union[Literal[True], _ColumnExpressionArgument[bool]],
124 *clauses: _ColumnExpressionArgument[bool],
125) -> ColumnElement[bool]:
126 r"""Produce a conjunction of expressions joined by ``AND``.
128 E.g.::
130 from sqlalchemy import and_
132 stmt = select(users_table).where(
133 and_(users_table.c.name == "wendy", users_table.c.enrolled == True)
134 )
136 The :func:`.and_` conjunction is also available using the
137 Python ``&`` operator (though note that compound expressions
138 need to be parenthesized in order to function with Python
139 operator precedence behavior)::
141 stmt = select(users_table).where(
142 (users_table.c.name == "wendy") & (users_table.c.enrolled == True)
143 )
145 The :func:`.and_` operation is also implicit in some cases;
146 the :meth:`_expression.Select.where`
147 method for example can be invoked multiple
148 times against a statement, which will have the effect of each
149 clause being combined using :func:`.and_`::
151 stmt = (
152 select(users_table)
153 .where(users_table.c.name == "wendy")
154 .where(users_table.c.enrolled == True)
155 )
157 The :func:`.and_` construct must be given at least one positional
158 argument in order to be valid; a :func:`.and_` construct with no
159 arguments is ambiguous. To produce an "empty" or dynamically
160 generated :func:`.and_` expression, from a given list of expressions,
161 a "default" element of :func:`_sql.true` (or just ``True``) should be
162 specified::
164 from sqlalchemy import true
166 criteria = and_(true(), *expressions)
168 The above expression will compile to SQL as the expression ``true``
169 or ``1 = 1``, depending on backend, if no other expressions are
170 present. If expressions are present, then the :func:`_sql.true` value is
171 ignored as it does not affect the outcome of an AND expression that
172 has other elements.
174 .. deprecated:: 1.4 The :func:`.and_` element now requires that at
175 least one argument is passed; creating the :func:`.and_` construct
176 with no arguments is deprecated, and will emit a deprecation warning
177 while continuing to produce a blank SQL string.
179 .. seealso::
181 :func:`.or_`
183 """
184 ...
187if not TYPE_CHECKING:
188 # handle deprecated case which allows zero-arguments
189 def and_(*clauses): # noqa: F811
190 r"""Produce a conjunction of expressions joined by ``AND``.
192 E.g.::
194 from sqlalchemy import and_
196 stmt = select(users_table).where(
197 and_(users_table.c.name == "wendy", users_table.c.enrolled == True)
198 )
200 The :func:`.and_` conjunction is also available using the
201 Python ``&`` operator (though note that compound expressions
202 need to be parenthesized in order to function with Python
203 operator precedence behavior)::
205 stmt = select(users_table).where(
206 (users_table.c.name == "wendy") & (users_table.c.enrolled == True)
207 )
209 The :func:`.and_` operation is also implicit in some cases;
210 the :meth:`_expression.Select.where`
211 method for example can be invoked multiple
212 times against a statement, which will have the effect of each
213 clause being combined using :func:`.and_`::
215 stmt = (
216 select(users_table)
217 .where(users_table.c.name == "wendy")
218 .where(users_table.c.enrolled == True)
219 )
221 The :func:`.and_` construct must be given at least one positional
222 argument in order to be valid; a :func:`.and_` construct with no
223 arguments is ambiguous. To produce an "empty" or dynamically
224 generated :func:`.and_` expression, from a given list of expressions,
225 a "default" element of :func:`_sql.true` (or just ``True``) should be
226 specified::
228 from sqlalchemy import true
230 criteria = and_(true(), *expressions)
232 The above expression will compile to SQL as the expression ``true``
233 or ``1 = 1``, depending on backend, if no other expressions are
234 present. If expressions are present, then the :func:`_sql.true` value
235 is ignored as it does not affect the outcome of an AND expression that
236 has other elements.
238 .. deprecated:: 1.4 The :func:`.and_` element now requires that at
239 least one argument is passed; creating the :func:`.and_` construct
240 with no arguments is deprecated, and will emit a deprecation warning
241 while continuing to produce a blank SQL string.
243 .. seealso::
245 :func:`.or_`
247 """ # noqa: E501
248 return BooleanClauseList.and_(*clauses)
251def any_(expr: _ColumnExpressionArgument[_T]) -> CollectionAggregate[bool]:
252 """Produce an ANY expression.
254 For dialects such as that of PostgreSQL, this operator applies
255 to usage of the :class:`_types.ARRAY` datatype, for that of
256 MySQL, it may apply to a subquery. e.g.::
258 # renders on PostgreSQL:
259 # '5 = ANY (somearray)'
260 expr = 5 == any_(mytable.c.somearray)
262 # renders on MySQL:
263 # '5 = ANY (SELECT value FROM table)'
264 expr = 5 == any_(select(table.c.value))
266 Comparison to NULL may work using ``None`` or :func:`_sql.null`::
268 None == any_(mytable.c.somearray)
270 The any_() / all_() operators also feature a special "operand flipping"
271 behavior such that if any_() / all_() are used on the left side of a
272 comparison using a standalone operator such as ``==``, ``!=``, etc.
273 (not including operator methods such as
274 :meth:`_sql.ColumnOperators.is_`) the rendered expression is flipped::
276 # would render '5 = ANY (column)`
277 any_(mytable.c.column) == 5
279 Or with ``None``, which note will not perform
280 the usual step of rendering "IS" as is normally the case for NULL::
282 # would render 'NULL = ANY(somearray)'
283 any_(mytable.c.somearray) == None
285 The column-level :meth:`_sql.ColumnElement.any_` method (not to be
286 confused with the deprecated :class:`_types.ARRAY` level
287 :meth:`_types.ARRAY.Comparator.any`) is shorthand for
288 ``any_(col)``::
290 5 = mytable.c.somearray.any_()
292 .. seealso::
294 :meth:`_sql.ColumnOperators.any_`
296 :func:`_expression.all_`
298 """
299 if isinstance(expr, operators.ColumnOperators):
300 return expr.any_()
301 else:
302 return CollectionAggregate._create_any(expr)
305@overload
306def asc(
307 column: Union[str, "ColumnElement[_T]"],
308) -> UnaryExpression[_T]: ...
311@overload
312def asc(
313 column: _ColumnExpressionOrStrLabelArgument[_T],
314) -> Union[OrderByList, UnaryExpression[_T]]: ...
317def asc(
318 column: _ColumnExpressionOrStrLabelArgument[_T],
319) -> Union[OrderByList, UnaryExpression[_T]]:
320 """Produce an ascending ``ORDER BY`` clause element.
322 e.g.::
324 from sqlalchemy import asc
326 stmt = select(users_table).order_by(asc(users_table.c.name))
328 will produce SQL as:
330 .. sourcecode:: sql
332 SELECT id, name FROM user ORDER BY name ASC
334 The :func:`.asc` function is a standalone version of the
335 :meth:`_expression.ColumnElement.asc`
336 method available on all SQL expressions,
337 e.g.::
340 stmt = select(users_table).order_by(users_table.c.name.asc())
342 :param column: A :class:`_expression.ColumnElement` (e.g.
343 scalar SQL expression)
344 with which to apply the :func:`.asc` operation.
346 .. seealso::
348 :func:`.desc`
350 :func:`.nulls_first`
352 :func:`.nulls_last`
354 :meth:`_expression.Select.order_by`
356 """
358 if isinstance(column, operators.OrderingOperators):
359 return column.asc() # type: ignore[unused-ignore]
360 else:
361 return UnaryExpression._create_asc(column)
364def collate(
365 expression: _ColumnExpressionArgument[str], collation: str
366) -> BinaryExpression[str]:
367 """Return the clause ``expression COLLATE collation``.
369 e.g.::
371 collate(mycolumn, "utf8_bin")
373 produces:
375 .. sourcecode:: sql
377 mycolumn COLLATE utf8_bin
379 The collation expression is also quoted if it is a case sensitive
380 identifier, e.g. contains uppercase characters.
382 """
383 if isinstance(expression, operators.ColumnOperators):
384 return expression.collate(collation) # type: ignore
385 else:
386 return CollationClause._create_collation_expression(
387 expression, collation
388 )
391def between(
392 expr: _ColumnExpressionOrLiteralArgument[_T],
393 lower_bound: Any,
394 upper_bound: Any,
395 symmetric: bool = False,
396) -> BinaryExpression[bool]:
397 """Produce a ``BETWEEN`` predicate clause.
399 E.g.::
401 from sqlalchemy import between
403 stmt = select(users_table).where(between(users_table.c.id, 5, 7))
405 Would produce SQL resembling:
407 .. sourcecode:: sql
409 SELECT id, name FROM user WHERE id BETWEEN :id_1 AND :id_2
411 The :func:`.between` function is a standalone version of the
412 :meth:`_expression.ColumnElement.between` method available on all
413 SQL expressions, as in::
415 stmt = select(users_table).where(users_table.c.id.between(5, 7))
417 All arguments passed to :func:`.between`, including the left side
418 column expression, are coerced from Python scalar values if a
419 the value is not a :class:`_expression.ColumnElement` subclass.
420 For example,
421 three fixed values can be compared as in::
423 print(between(5, 3, 7))
425 Which would produce::
427 :param_1 BETWEEN :param_2 AND :param_3
429 :param expr: a column expression, typically a
430 :class:`_expression.ColumnElement`
431 instance or alternatively a Python scalar expression to be coerced
432 into a column expression, serving as the left side of the ``BETWEEN``
433 expression.
435 :param lower_bound: a column or Python scalar expression serving as the
436 lower bound of the right side of the ``BETWEEN`` expression.
438 :param upper_bound: a column or Python scalar expression serving as the
439 upper bound of the right side of the ``BETWEEN`` expression.
441 :param symmetric: if True, will render " BETWEEN SYMMETRIC ". Note
442 that not all databases support this syntax.
444 .. seealso::
446 :meth:`_expression.ColumnElement.between`
448 """
449 col_expr = coercions.expect(roles.ExpressionElementRole, expr)
450 return col_expr.between(lower_bound, upper_bound, symmetric=symmetric)
453def outparam(
454 key: str, type_: Optional[TypeEngine[_T]] = None
455) -> BindParameter[_T]:
456 """Create an 'OUT' parameter for usage in functions (stored procedures),
457 for databases which support them.
459 The ``outparam`` can be used like a regular function parameter.
460 The "output" value will be available from the
461 :class:`~sqlalchemy.engine.CursorResult` object via its ``out_parameters``
462 attribute, which returns a dictionary containing the values.
464 """
465 return BindParameter(key, None, type_=type_, unique=False, isoutparam=True)
468@overload
469def not_(clause: BinaryExpression[_T]) -> BinaryExpression[_T]: ...
472@overload
473def not_(clause: _ColumnExpressionArgument[_T]) -> ColumnElement[_T]: ...
476def not_(clause: _ColumnExpressionArgument[_T]) -> ColumnElement[_T]:
477 """Return a negation of the given clause, i.e. ``NOT(clause)``.
479 The ``~`` operator is also overloaded on all
480 :class:`_expression.ColumnElement` subclasses to produce the
481 same result.
483 """
485 return coercions.expect(roles.ExpressionElementRole, clause).__invert__()
488def from_dml_column(column: _DMLOnlyColumnArgument[_T]) -> DMLTargetCopy[_T]:
489 r"""A placeholder that may be used in compiled INSERT or UPDATE expressions
490 to refer to the SQL expression or value being applied to another column.
492 Given a table such as::
494 t = Table(
495 "t",
496 MetaData(),
497 Column("x", Integer),
498 Column("y", Integer),
499 )
501 The :func:`_sql.from_dml_column` construct allows automatic copying
502 of an expression assigned to a different column to be re-used::
504 >>> stmt = t.insert().values(x=func.foobar(3), y=from_dml_column(t.c.x) + 5)
505 >>> print(stmt)
506 INSERT INTO t (x, y) VALUES (foobar(:foobar_1), (foobar(:foobar_1) + :param_1))
508 The :func:`_sql.from_dml_column` construct is intended to be useful primarily
509 with event-based hooks such as those used by ORM hybrids.
511 .. seealso::
513 :ref:`hybrid_bulk_update`
515 .. versionadded:: 2.1
518 """ # noqa: E501
520 return DMLTargetCopy(column)
523def bindparam(
524 key: Optional[str],
525 value: Any = _NoArg.NO_ARG,
526 type_: Optional[_TypeEngineArgument[_T]] = None,
527 unique: bool = False,
528 required: Union[bool, Literal[_NoArg.NO_ARG]] = _NoArg.NO_ARG,
529 quote: Optional[bool] = None,
530 callable_: Optional[Callable[[], Any]] = None,
531 expanding: bool = False,
532 isoutparam: bool = False,
533 literal_execute: bool = False,
534) -> BindParameter[_T]:
535 r"""Produce a "bound expression".
537 The return value is an instance of :class:`.BindParameter`; this
538 is a :class:`_expression.ColumnElement`
539 subclass which represents a so-called
540 "placeholder" value in a SQL expression, the value of which is
541 supplied at the point at which the statement in executed against a
542 database connection.
544 In SQLAlchemy, the :func:`.bindparam` construct has
545 the ability to carry along the actual value that will be ultimately
546 used at expression time. In this way, it serves not just as
547 a "placeholder" for eventual population, but also as a means of
548 representing so-called "unsafe" values which should not be rendered
549 directly in a SQL statement, but rather should be passed along
550 to the :term:`DBAPI` as values which need to be correctly escaped
551 and potentially handled for type-safety.
553 When using :func:`.bindparam` explicitly, the use case is typically
554 one of traditional deferment of parameters; the :func:`.bindparam`
555 construct accepts a name which can then be referred to at execution
556 time::
558 from sqlalchemy import bindparam
560 stmt = select(users_table).where(
561 users_table.c.name == bindparam("username")
562 )
564 The above statement, when rendered, will produce SQL similar to:
566 .. sourcecode:: sql
568 SELECT id, name FROM user WHERE name = :username
570 In order to populate the value of ``:username`` above, the value
571 would typically be applied at execution time to a method
572 like :meth:`_engine.Connection.execute`::
574 result = connection.execute(stmt, {"username": "wendy"})
576 Explicit use of :func:`.bindparam` is also common when producing
577 UPDATE or DELETE statements that are to be invoked multiple times,
578 where the WHERE criterion of the statement is to change on each
579 invocation, such as::
581 stmt = (
582 users_table.update()
583 .where(user_table.c.name == bindparam("username"))
584 .values(fullname=bindparam("fullname"))
585 )
587 connection.execute(
588 stmt,
589 [
590 {"username": "wendy", "fullname": "Wendy Smith"},
591 {"username": "jack", "fullname": "Jack Jones"},
592 ],
593 )
595 SQLAlchemy's Core expression system makes wide use of
596 :func:`.bindparam` in an implicit sense. It is typical that Python
597 literal values passed to virtually all SQL expression functions are
598 coerced into fixed :func:`.bindparam` constructs. For example, given
599 a comparison operation such as::
601 expr = users_table.c.name == "Wendy"
603 The above expression will produce a :class:`.BinaryExpression`
604 construct, where the left side is the :class:`_schema.Column` object
605 representing the ``name`` column, and the right side is a
606 :class:`.BindParameter` representing the literal value::
608 print(repr(expr.right))
609 BindParameter("%(4327771088 name)s", "Wendy", type_=String())
611 The expression above will render SQL such as:
613 .. sourcecode:: sql
615 user.name = :name_1
617 Where the ``:name_1`` parameter name is an anonymous name. The
618 actual string ``Wendy`` is not in the rendered string, but is carried
619 along where it is later used within statement execution. If we
620 invoke a statement like the following::
622 stmt = select(users_table).where(users_table.c.name == "Wendy")
623 result = connection.execute(stmt)
625 We would see SQL logging output as:
627 .. sourcecode:: sql
629 SELECT "user".id, "user".name
630 FROM "user"
631 WHERE "user".name = %(name_1)s
632 {'name_1': 'Wendy'}
634 Above, we see that ``Wendy`` is passed as a parameter to the database,
635 while the placeholder ``:name_1`` is rendered in the appropriate form
636 for the target database, in this case the PostgreSQL database.
638 Similarly, :func:`.bindparam` is invoked automatically when working
639 with :term:`CRUD` statements as far as the "VALUES" portion is
640 concerned. The :func:`_expression.insert` construct produces an
641 ``INSERT`` expression which will, at statement execution time, generate
642 bound placeholders based on the arguments passed, as in::
644 stmt = users_table.insert()
645 result = connection.execute(stmt, {"name": "Wendy"})
647 The above will produce SQL output as:
649 .. sourcecode:: sql
651 INSERT INTO "user" (name) VALUES (%(name)s)
652 {'name': 'Wendy'}
654 The :class:`_expression.Insert` construct, at
655 compilation/execution time, rendered a single :func:`.bindparam`
656 mirroring the column name ``name`` as a result of the single ``name``
657 parameter we passed to the :meth:`_engine.Connection.execute` method.
659 :param key:
660 the key (e.g. the name) for this bind param.
661 Will be used in the generated
662 SQL statement for dialects that use named parameters. This
663 value may be modified when part of a compilation operation,
664 if other :class:`BindParameter` objects exist with the same
665 key, or if its length is too long and truncation is
666 required.
668 If omitted, an "anonymous" name is generated for the bound parameter;
669 when given a value to bind, the end result is equivalent to calling upon
670 the :func:`.literal` function with a value to bind, particularly
671 if the :paramref:`.bindparam.unique` parameter is also provided.
673 :param value:
674 Initial value for this bind param. Will be used at statement
675 execution time as the value for this parameter passed to the
676 DBAPI, if no other value is indicated to the statement execution
677 method for this particular parameter name. Defaults to ``None``.
679 :param callable\_:
680 A callable function that takes the place of "value". The function
681 will be called at statement execution time to determine the
682 ultimate value. Used for scenarios where the actual bind
683 value cannot be determined at the point at which the clause
684 construct is created, but embedded bind values are still desirable.
686 :param type\_:
687 A :class:`.TypeEngine` class or instance representing an optional
688 datatype for this :func:`.bindparam`. If not passed, a type
689 may be determined automatically for the bind, based on the given
690 value; for example, trivial Python types such as ``str``,
691 ``int``, ``bool``
692 may result in the :class:`.String`, :class:`.Integer` or
693 :class:`.Boolean` types being automatically selected.
695 The type of a :func:`.bindparam` is significant especially in that
696 the type will apply pre-processing to the value before it is
697 passed to the database. For example, a :func:`.bindparam` which
698 refers to a datetime value, and is specified as holding the
699 :class:`.DateTime` type, may apply conversion needed to the
700 value (such as stringification on SQLite) before passing the value
701 to the database.
703 :param unique:
704 if True, the key name of this :class:`.BindParameter` will be
705 modified if another :class:`.BindParameter` of the same name
706 already has been located within the containing
707 expression. This flag is used generally by the internals
708 when producing so-called "anonymous" bound expressions, it
709 isn't generally applicable to explicitly-named :func:`.bindparam`
710 constructs.
712 :param required:
713 If ``True``, a value is required at execution time. If not passed,
714 it defaults to ``True`` if neither :paramref:`.bindparam.value`
715 or :paramref:`.bindparam.callable` were passed. If either of these
716 parameters are present, then :paramref:`.bindparam.required`
717 defaults to ``False``.
719 :param quote:
720 True if this parameter name requires quoting and is not
721 currently known as a SQLAlchemy reserved word; this currently
722 only applies to the Oracle Database backends, where bound names must
723 sometimes be quoted.
725 :param isoutparam:
726 if True, the parameter should be treated like a stored procedure
727 "OUT" parameter. This applies to backends such as Oracle Database which
728 support OUT parameters.
730 :param expanding:
731 if True, this parameter will be treated as an "expanding" parameter
732 at execution time; the parameter value is expected to be a sequence,
733 rather than a scalar value, and the string SQL statement will
734 be transformed on a per-execution basis to accommodate the sequence
735 with a variable number of parameter slots passed to the DBAPI.
736 This is to allow statement caching to be used in conjunction with
737 an IN clause.
739 .. seealso::
741 :meth:`.ColumnOperators.in_`
743 :ref:`baked_in` - with baked queries
745 .. note:: The "expanding" feature does not support "executemany"-
746 style parameter sets.
748 :param literal_execute:
749 if True, the bound parameter will be rendered in the compile phase
750 with a special "POSTCOMPILE" token, and the SQLAlchemy compiler will
751 render the final value of the parameter into the SQL statement at
752 statement execution time, omitting the value from the parameter
753 dictionary / list passed to DBAPI ``cursor.execute()``. This
754 produces a similar effect as that of using the ``literal_binds``,
755 compilation flag, however takes place as the statement is sent to
756 the DBAPI ``cursor.execute()`` method, rather than when the statement
757 is compiled. The primary use of this
758 capability is for rendering LIMIT / OFFSET clauses for database
759 drivers that can't accommodate for bound parameters in these
760 contexts, while allowing SQL constructs to be cacheable at the
761 compilation level.
763 .. versionadded:: 1.4 Added "post compile" bound parameters
765 .. seealso::
767 :ref:`change_4808`.
769 .. seealso::
771 :ref:`tutorial_sending_parameters` - in the
772 :ref:`unified_tutorial`
775 """
776 return BindParameter(
777 key,
778 value,
779 type_,
780 unique,
781 required,
782 quote,
783 callable_,
784 expanding,
785 isoutparam,
786 literal_execute,
787 )
790def case(
791 *whens: Union[
792 typing_Tuple[_ColumnExpressionArgument[bool], Any], Mapping[Any, Any]
793 ],
794 value: Optional[Any] = None,
795 else_: Optional[Any] = None,
796) -> Case[Any]:
797 r"""Produce a ``CASE`` expression.
799 The ``CASE`` construct in SQL is a conditional object that
800 acts somewhat analogously to an "if/then" construct in other
801 languages. It returns an instance of :class:`.Case`.
803 :func:`.case` in its usual form is passed a series of "when"
804 constructs, that is, a list of conditions and results as tuples::
806 from sqlalchemy import case
808 stmt = select(users_table).where(
809 case(
810 (users_table.c.name == "wendy", "W"),
811 (users_table.c.name == "jack", "J"),
812 else_="E",
813 )
814 )
816 The above statement will produce SQL resembling:
818 .. sourcecode:: sql
820 SELECT id, name FROM user
821 WHERE CASE
822 WHEN (name = :name_1) THEN :param_1
823 WHEN (name = :name_2) THEN :param_2
824 ELSE :param_3
825 END
827 When simple equality expressions of several values against a single
828 parent column are needed, :func:`.case` also has a "shorthand" format
829 used via the
830 :paramref:`.case.value` parameter, which is passed a column
831 expression to be compared. In this form, the :paramref:`.case.whens`
832 parameter is passed as a dictionary containing expressions to be
833 compared against keyed to result expressions. The statement below is
834 equivalent to the preceding statement::
836 stmt = select(users_table).where(
837 case({"wendy": "W", "jack": "J"}, value=users_table.c.name, else_="E")
838 )
840 The values which are accepted as result values in
841 :paramref:`.case.whens` as well as with :paramref:`.case.else_` are
842 coerced from Python literals into :func:`.bindparam` constructs.
843 SQL expressions, e.g. :class:`_expression.ColumnElement` constructs,
844 are accepted
845 as well. To coerce a literal string expression into a constant
846 expression rendered inline, use the :func:`_expression.literal_column`
847 construct,
848 as in::
850 from sqlalchemy import case, literal_column
852 case(
853 (orderline.c.qty > 100, literal_column("'greaterthan100'")),
854 (orderline.c.qty > 10, literal_column("'greaterthan10'")),
855 else_=literal_column("'lessthan10'"),
856 )
858 The above will render the given constants without using bound
859 parameters for the result values (but still for the comparison
860 values), as in:
862 .. sourcecode:: sql
864 CASE
865 WHEN (orderline.qty > :qty_1) THEN 'greaterthan100'
866 WHEN (orderline.qty > :qty_2) THEN 'greaterthan10'
867 ELSE 'lessthan10'
868 END
870 :param \*whens: The criteria to be compared against,
871 :paramref:`.case.whens` accepts two different forms, based on
872 whether or not :paramref:`.case.value` is used.
874 .. versionchanged:: 1.4 the :func:`_sql.case`
875 function now accepts the series of WHEN conditions positionally
877 In the first form, it accepts multiple 2-tuples passed as positional
878 arguments; each 2-tuple consists of ``(<sql expression>, <value>)``,
879 where the SQL expression is a boolean expression and "value" is a
880 resulting value, e.g.::
882 case(
883 (users_table.c.name == "wendy", "W"),
884 (users_table.c.name == "jack", "J"),
885 )
887 In the second form, it accepts a Python dictionary of comparison
888 values mapped to a resulting value; this form requires
889 :paramref:`.case.value` to be present, and values will be compared
890 using the ``==`` operator, e.g.::
892 case({"wendy": "W", "jack": "J"}, value=users_table.c.name)
894 :param value: An optional SQL expression which will be used as a
895 fixed "comparison point" for candidate values within a dictionary
896 passed to :paramref:`.case.whens`.
898 :param else\_: An optional SQL expression which will be the evaluated
899 result of the ``CASE`` construct if all expressions within
900 :paramref:`.case.whens` evaluate to false. When omitted, most
901 databases will produce a result of NULL if none of the "when"
902 expressions evaluate to true.
905 """ # noqa: E501
906 return Case(*whens, value=value, else_=else_)
909def cast(
910 expression: _ColumnExpressionOrLiteralArgument[Any],
911 type_: _TypeEngineArgument[_T],
912) -> Cast[_T]:
913 r"""Produce a ``CAST`` expression.
915 :func:`.cast` returns an instance of :class:`.Cast`.
917 E.g.::
919 from sqlalchemy import cast, Numeric
921 stmt = select(cast(product_table.c.unit_price, Numeric(10, 4)))
923 The above statement will produce SQL resembling:
925 .. sourcecode:: sql
927 SELECT CAST(unit_price AS NUMERIC(10, 4)) FROM product
929 The :func:`.cast` function performs two distinct functions when
930 used. The first is that it renders the ``CAST`` expression within
931 the resulting SQL string. The second is that it associates the given
932 type (e.g. :class:`.TypeEngine` class or instance) with the column
933 expression on the Python side, which means the expression will take
934 on the expression operator behavior associated with that type,
935 as well as the bound-value handling and result-row-handling behavior
936 of the type.
938 An alternative to :func:`.cast` is the :func:`.type_coerce` function.
939 This function performs the second task of associating an expression
940 with a specific type, but does not render the ``CAST`` expression
941 in SQL.
943 :param expression: A SQL expression, such as a
944 :class:`_expression.ColumnElement`
945 expression or a Python string which will be coerced into a bound
946 literal value.
948 :param type\_: A :class:`.TypeEngine` class or instance indicating
949 the type to which the ``CAST`` should apply.
951 .. seealso::
953 :ref:`tutorial_casts`
955 :func:`.try_cast` - an alternative to CAST that results in
956 NULLs when the cast fails, instead of raising an error.
957 Only supported by some dialects.
959 :func:`.type_coerce` - an alternative to CAST that coerces the type
960 on the Python side only, which is often sufficient to generate the
961 correct SQL and data coercion.
964 """
965 return Cast(expression, type_)
968def try_cast(
969 expression: _ColumnExpressionOrLiteralArgument[Any],
970 type_: _TypeEngineArgument[_T],
971) -> TryCast[_T]:
972 """Produce a ``TRY_CAST`` expression for backends which support it;
973 this is a ``CAST`` which returns NULL for un-castable conversions.
975 In SQLAlchemy, this construct is supported **only** by the SQL Server
976 dialect, and will raise a :class:`.CompileError` if used on other
977 included backends. However, third party backends may also support
978 this construct.
980 .. tip:: As :func:`_sql.try_cast` originates from the SQL Server dialect,
981 it's importable both from ``sqlalchemy.`` as well as from
982 ``sqlalchemy.dialects.mssql``.
984 :func:`_sql.try_cast` returns an instance of :class:`.TryCast` and
985 generally behaves similarly to the :class:`.Cast` construct;
986 at the SQL level, the difference between ``CAST`` and ``TRY_CAST``
987 is that ``TRY_CAST`` returns NULL for an un-castable expression,
988 such as attempting to cast a string ``"hi"`` to an integer value.
990 E.g.::
992 from sqlalchemy import select, try_cast, Numeric
994 stmt = select(try_cast(product_table.c.unit_price, Numeric(10, 4)))
996 The above would render on Microsoft SQL Server as:
998 .. sourcecode:: sql
1000 SELECT TRY_CAST (product_table.unit_price AS NUMERIC(10, 4))
1001 FROM product_table
1003 .. versionadded:: 2.0.14 :func:`.try_cast` has been
1004 generalized from the SQL Server dialect into a general use
1005 construct that may be supported by additional dialects.
1007 """
1008 return TryCast(expression, type_)
1011def column(
1012 text: str,
1013 type_: Optional[_TypeEngineArgument[_T]] = None,
1014 is_literal: bool = False,
1015 _selectable: Optional[FromClause] = None,
1016) -> ColumnClause[_T]:
1017 """Produce a :class:`.ColumnClause` object.
1019 The :class:`.ColumnClause` is a lightweight analogue to the
1020 :class:`_schema.Column` class. The :func:`_expression.column`
1021 function can
1022 be invoked with just a name alone, as in::
1024 from sqlalchemy import column
1026 id, name = column("id"), column("name")
1027 stmt = select(id, name).select_from("user")
1029 The above statement would produce SQL like:
1031 .. sourcecode:: sql
1033 SELECT id, name FROM user
1035 Once constructed, :func:`_expression.column`
1036 may be used like any other SQL
1037 expression element such as within :func:`_expression.select`
1038 constructs::
1040 from sqlalchemy.sql import column
1042 id, name = column("id"), column("name")
1043 stmt = select(id, name).select_from("user")
1045 The text handled by :func:`_expression.column`
1046 is assumed to be handled
1047 like the name of a database column; if the string contains mixed case,
1048 special characters, or matches a known reserved word on the target
1049 backend, the column expression will render using the quoting
1050 behavior determined by the backend. To produce a textual SQL
1051 expression that is rendered exactly without any quoting,
1052 use :func:`_expression.literal_column` instead,
1053 or pass ``True`` as the
1054 value of :paramref:`_expression.column.is_literal`. Additionally,
1055 full SQL
1056 statements are best handled using the :func:`_expression.text`
1057 construct.
1059 :func:`_expression.column` can be used in a table-like
1060 fashion by combining it with the :func:`.table` function
1061 (which is the lightweight analogue to :class:`_schema.Table`
1062 ) to produce
1063 a working table construct with minimal boilerplate::
1065 from sqlalchemy import table, column, select
1067 user = table(
1068 "user",
1069 column("id"),
1070 column("name"),
1071 column("description"),
1072 )
1074 stmt = select(user.c.description).where(user.c.name == "wendy")
1076 A :func:`_expression.column` / :func:`.table`
1077 construct like that illustrated
1078 above can be created in an
1079 ad-hoc fashion and is not associated with any
1080 :class:`_schema.MetaData`, DDL, or events, unlike its
1081 :class:`_schema.Table` counterpart.
1083 :param text: the text of the element.
1085 :param type: :class:`_types.TypeEngine` object which can associate
1086 this :class:`.ColumnClause` with a type.
1088 :param is_literal: if True, the :class:`.ColumnClause` is assumed to
1089 be an exact expression that will be delivered to the output with no
1090 quoting rules applied regardless of case sensitive settings. the
1091 :func:`_expression.literal_column()` function essentially invokes
1092 :func:`_expression.column` while passing ``is_literal=True``.
1094 .. seealso::
1096 :class:`_schema.Column`
1098 :func:`_expression.literal_column`
1100 :func:`.table`
1102 :func:`_expression.text`
1104 :ref:`tutorial_select_arbitrary_text`
1106 """
1107 return ColumnClause(text, type_, is_literal, _selectable)
1110@overload
1111def desc(
1112 column: Union[str, "ColumnElement[_T]"],
1113) -> UnaryExpression[_T]: ...
1116@overload
1117def desc(
1118 column: _ColumnExpressionOrStrLabelArgument[_T],
1119) -> Union[OrderByList, UnaryExpression[_T]]: ...
1122def desc(
1123 column: _ColumnExpressionOrStrLabelArgument[_T],
1124) -> Union[OrderByList, UnaryExpression[_T]]:
1125 """Produce a descending ``ORDER BY`` clause element.
1127 e.g.::
1129 from sqlalchemy import desc
1131 stmt = select(users_table).order_by(desc(users_table.c.name))
1133 will produce SQL as:
1135 .. sourcecode:: sql
1137 SELECT id, name FROM user ORDER BY name DESC
1139 The :func:`.desc` function is a standalone version of the
1140 :meth:`_expression.ColumnElement.desc`
1141 method available on all SQL expressions,
1142 e.g.::
1145 stmt = select(users_table).order_by(users_table.c.name.desc())
1147 :param column: A :class:`_expression.ColumnElement` (e.g.
1148 scalar SQL expression)
1149 with which to apply the :func:`.desc` operation.
1151 .. seealso::
1153 :func:`.asc`
1155 :func:`.nulls_first`
1157 :func:`.nulls_last`
1159 :meth:`_expression.Select.order_by`
1161 """
1162 if isinstance(column, operators.OrderingOperators):
1163 return column.desc() # type: ignore[unused-ignore]
1164 else:
1165 return UnaryExpression._create_desc(column)
1168def distinct(expr: _ColumnExpressionArgument[_T]) -> UnaryExpression[_T]:
1169 """Produce an column-expression-level unary ``DISTINCT`` clause.
1171 This applies the ``DISTINCT`` keyword to an **individual column
1172 expression** (e.g. not the whole statement), and renders **specifically
1173 in that column position**; this is used for containment within
1174 an aggregate function, as in::
1176 from sqlalchemy import distinct, func
1178 stmt = select(users_table.c.id, func.count(distinct(users_table.c.name)))
1180 The above would produce an statement resembling:
1182 .. sourcecode:: sql
1184 SELECT user.id, count(DISTINCT user.name) FROM user
1186 .. tip:: The :func:`_sql.distinct` function does **not** apply DISTINCT
1187 to the full SELECT statement, instead applying a DISTINCT modifier
1188 to **individual column expressions**. For general ``SELECT DISTINCT``
1189 support, use the
1190 :meth:`_sql.Select.distinct` method on :class:`_sql.Select`.
1192 The :func:`.distinct` function is also available as a column-level
1193 method, e.g. :meth:`_expression.ColumnElement.distinct`, as in::
1195 stmt = select(func.count(users_table.c.name.distinct()))
1197 The :func:`.distinct` operator is different from the
1198 :meth:`_expression.Select.distinct` method of
1199 :class:`_expression.Select`,
1200 which produces a ``SELECT`` statement
1201 with ``DISTINCT`` applied to the result set as a whole,
1202 e.g. a ``SELECT DISTINCT`` expression. See that method for further
1203 information.
1205 .. seealso::
1207 :meth:`_expression.ColumnElement.distinct`
1209 :meth:`_expression.Select.distinct`
1211 :data:`.func`
1213 """ # noqa: E501
1214 if isinstance(expr, operators.ColumnOperators):
1215 return expr.distinct()
1216 else:
1217 return UnaryExpression._create_distinct(expr)
1220def bitwise_not(expr: _ColumnExpressionArgument[_T]) -> UnaryExpression[_T]:
1221 """Produce a unary bitwise NOT clause, typically via the ``~`` operator.
1223 Not to be confused with boolean negation :func:`_sql.not_`.
1225 .. versionadded:: 2.0.2
1227 .. seealso::
1229 :ref:`operators_bitwise`
1232 """
1233 if isinstance(expr, operators.ColumnOperators):
1234 return expr.bitwise_not()
1235 else:
1236 return UnaryExpression._create_bitwise_not(expr)
1239def extract(field: str, expr: _ColumnExpressionArgument[Any]) -> Extract:
1240 """Return a :class:`.Extract` construct.
1242 This is typically available as :func:`.extract`
1243 as well as ``func.extract`` from the
1244 :data:`.func` namespace.
1246 :param field: The field to extract.
1248 .. warning:: This field is used as a literal SQL string.
1249 **DO NOT PASS UNTRUSTED INPUT TO THIS STRING**.
1251 :param expr: A column or Python scalar expression serving as the
1252 right side of the ``EXTRACT`` expression.
1254 E.g.::
1256 from sqlalchemy import extract
1257 from sqlalchemy import table, column
1259 logged_table = table(
1260 "user",
1261 column("id"),
1262 column("date_created"),
1263 )
1265 stmt = select(logged_table.c.id).where(
1266 extract("YEAR", logged_table.c.date_created) == 2021
1267 )
1269 In the above example, the statement is used to select ids from the
1270 database where the ``YEAR`` component matches a specific value.
1272 Similarly, one can also select an extracted component::
1274 stmt = select(extract("YEAR", logged_table.c.date_created)).where(
1275 logged_table.c.id == 1
1276 )
1278 The implementation of ``EXTRACT`` may vary across database backends.
1279 Users are reminded to consult their database documentation.
1280 """
1281 return Extract(field, expr)
1284def false() -> False_:
1285 """Return a :class:`.False_` construct.
1287 E.g.:
1289 .. sourcecode:: pycon+sql
1291 >>> from sqlalchemy import false
1292 >>> print(select(t.c.x).where(false()))
1293 {printsql}SELECT x FROM t WHERE false
1295 A backend which does not support true/false constants will render as
1296 an expression against 1 or 0:
1298 .. sourcecode:: pycon+sql
1300 >>> print(select(t.c.x).where(false()))
1301 {printsql}SELECT x FROM t WHERE 0 = 1
1303 The :func:`.true` and :func:`.false` constants also feature
1304 "short circuit" operation within an :func:`.and_` or :func:`.or_`
1305 conjunction:
1307 .. sourcecode:: pycon+sql
1309 >>> print(select(t.c.x).where(or_(t.c.x > 5, true())))
1310 {printsql}SELECT x FROM t WHERE true{stop}
1312 >>> print(select(t.c.x).where(and_(t.c.x > 5, false())))
1313 {printsql}SELECT x FROM t WHERE false{stop}
1315 .. seealso::
1317 :func:`.true`
1319 """
1321 return False_._instance()
1324def funcfilter(
1325 func: FunctionElement[_T], *criterion: _ColumnExpressionArgument[bool]
1326) -> FunctionFilter[_T]:
1327 """Produce a :class:`.FunctionFilter` object against a function.
1329 Used against aggregate and window functions,
1330 for database backends that support the "FILTER" clause.
1332 E.g.::
1334 from sqlalchemy import funcfilter
1336 funcfilter(func.count(1), MyClass.name == "some name")
1338 Would produce "COUNT(1) FILTER (WHERE myclass.name = 'some name')".
1340 This function is also available from the :data:`~.expression.func`
1341 construct itself via the :meth:`.FunctionElement.filter` method.
1343 .. seealso::
1345 :ref:`tutorial_functions_within_group` - in the
1346 :ref:`unified_tutorial`
1348 :meth:`.FunctionElement.filter`
1350 """
1351 return FunctionFilter(func, *criterion)
1354def label(
1355 name: str,
1356 element: _ColumnExpressionArgument[_T],
1357 type_: Optional[_TypeEngineArgument[_T]] = None,
1358) -> Label[_T]:
1359 """Return a :class:`Label` object for the
1360 given :class:`_expression.ColumnElement`.
1362 A label changes the name of an element in the columns clause of a
1363 ``SELECT`` statement, typically via the ``AS`` SQL keyword.
1365 This functionality is more conveniently available via the
1366 :meth:`_expression.ColumnElement.label` method on
1367 :class:`_expression.ColumnElement`.
1369 :param name: label name
1371 :param obj: a :class:`_expression.ColumnElement`.
1373 """
1374 return Label(name, element, type_)
1377def null() -> Null:
1378 """Return a constant :class:`.Null` construct."""
1380 return Null._instance()
1383@overload
1384def nulls_first(
1385 column: "ColumnElement[_T]",
1386) -> UnaryExpression[_T]: ...
1389@overload
1390def nulls_first(
1391 column: _ColumnExpressionArgument[_T],
1392) -> Union[OrderByList, UnaryExpression[_T]]: ...
1395def nulls_first(
1396 column: _ColumnExpressionArgument[_T],
1397) -> Union[OrderByList, UnaryExpression[_T]]:
1398 """Produce the ``NULLS FIRST`` modifier for an ``ORDER BY`` expression.
1400 :func:`.nulls_first` is intended to modify the expression produced
1401 by :func:`.asc` or :func:`.desc`, and indicates how NULL values
1402 should be handled when they are encountered during ordering::
1405 from sqlalchemy import desc, nulls_first
1407 stmt = select(users_table).order_by(nulls_first(desc(users_table.c.name)))
1409 The SQL expression from the above would resemble:
1411 .. sourcecode:: sql
1413 SELECT id, name FROM user ORDER BY name DESC NULLS FIRST
1415 Like :func:`.asc` and :func:`.desc`, :func:`.nulls_first` is typically
1416 invoked from the column expression itself using
1417 :meth:`_expression.ColumnElement.nulls_first`,
1418 rather than as its standalone
1419 function version, as in::
1421 stmt = select(users_table).order_by(
1422 users_table.c.name.desc().nulls_first()
1423 )
1425 .. versionchanged:: 1.4 :func:`.nulls_first` is renamed from
1426 :func:`.nullsfirst` in previous releases.
1427 The previous name remains available for backwards compatibility.
1429 .. seealso::
1431 :func:`.asc`
1433 :func:`.desc`
1435 :func:`.nulls_last`
1437 :meth:`_expression.Select.order_by`
1439 """ # noqa: E501
1440 if isinstance(column, operators.OrderingOperators):
1441 return column.nulls_first()
1442 else:
1443 return UnaryExpression._create_nulls_first(column)
1446@overload
1447def nulls_last(
1448 column: "ColumnElement[_T]",
1449) -> UnaryExpression[_T]: ...
1452@overload
1453def nulls_last(
1454 column: _ColumnExpressionArgument[_T],
1455) -> Union[OrderByList, UnaryExpression[_T]]: ...
1458def nulls_last(
1459 column: _ColumnExpressionArgument[_T],
1460) -> Union[OrderByList, UnaryExpression[_T]]:
1461 """Produce the ``NULLS LAST`` modifier for an ``ORDER BY`` expression.
1463 :func:`.nulls_last` is intended to modify the expression produced
1464 by :func:`.asc` or :func:`.desc`, and indicates how NULL values
1465 should be handled when they are encountered during ordering::
1468 from sqlalchemy import desc, nulls_last
1470 stmt = select(users_table).order_by(nulls_last(desc(users_table.c.name)))
1472 The SQL expression from the above would resemble:
1474 .. sourcecode:: sql
1476 SELECT id, name FROM user ORDER BY name DESC NULLS LAST
1478 Like :func:`.asc` and :func:`.desc`, :func:`.nulls_last` is typically
1479 invoked from the column expression itself using
1480 :meth:`_expression.ColumnElement.nulls_last`,
1481 rather than as its standalone
1482 function version, as in::
1484 stmt = select(users_table).order_by(users_table.c.name.desc().nulls_last())
1486 .. versionchanged:: 1.4 :func:`.nulls_last` is renamed from
1487 :func:`.nullslast` in previous releases.
1488 The previous name remains available for backwards compatibility.
1490 .. seealso::
1492 :func:`.asc`
1494 :func:`.desc`
1496 :func:`.nulls_first`
1498 :meth:`_expression.Select.order_by`
1500 """ # noqa: E501
1501 if isinstance(column, operators.OrderingOperators):
1502 return column.nulls_last()
1503 else:
1504 return UnaryExpression._create_nulls_last(column)
1507def or_( # type: ignore[empty-body]
1508 initial_clause: Union[Literal[False], _ColumnExpressionArgument[bool]],
1509 *clauses: _ColumnExpressionArgument[bool],
1510) -> ColumnElement[bool]:
1511 """Produce a conjunction of expressions joined by ``OR``.
1513 E.g.::
1515 from sqlalchemy import or_
1517 stmt = select(users_table).where(
1518 or_(users_table.c.name == "wendy", users_table.c.name == "jack")
1519 )
1521 The :func:`.or_` conjunction is also available using the
1522 Python ``|`` operator (though note that compound expressions
1523 need to be parenthesized in order to function with Python
1524 operator precedence behavior)::
1526 stmt = select(users_table).where(
1527 (users_table.c.name == "wendy") | (users_table.c.name == "jack")
1528 )
1530 The :func:`.or_` construct must be given at least one positional
1531 argument in order to be valid; a :func:`.or_` construct with no
1532 arguments is ambiguous. To produce an "empty" or dynamically
1533 generated :func:`.or_` expression, from a given list of expressions,
1534 a "default" element of :func:`_sql.false` (or just ``False``) should be
1535 specified::
1537 from sqlalchemy import false
1539 or_criteria = or_(false(), *expressions)
1541 The above expression will compile to SQL as the expression ``false``
1542 or ``0 = 1``, depending on backend, if no other expressions are
1543 present. If expressions are present, then the :func:`_sql.false` value is
1544 ignored as it does not affect the outcome of an OR expression which
1545 has other elements.
1547 .. deprecated:: 1.4 The :func:`.or_` element now requires that at
1548 least one argument is passed; creating the :func:`.or_` construct
1549 with no arguments is deprecated, and will emit a deprecation warning
1550 while continuing to produce a blank SQL string.
1552 .. seealso::
1554 :func:`.and_`
1556 """
1557 ...
1560if not TYPE_CHECKING:
1561 # handle deprecated case which allows zero-arguments
1562 def or_(*clauses): # noqa: F811
1563 """Produce a conjunction of expressions joined by ``OR``.
1565 E.g.::
1567 from sqlalchemy import or_
1569 stmt = select(users_table).where(
1570 or_(users_table.c.name == "wendy", users_table.c.name == "jack")
1571 )
1573 The :func:`.or_` conjunction is also available using the
1574 Python ``|`` operator (though note that compound expressions
1575 need to be parenthesized in order to function with Python
1576 operator precedence behavior)::
1578 stmt = select(users_table).where(
1579 (users_table.c.name == "wendy") | (users_table.c.name == "jack")
1580 )
1582 The :func:`.or_` construct must be given at least one positional
1583 argument in order to be valid; a :func:`.or_` construct with no
1584 arguments is ambiguous. To produce an "empty" or dynamically
1585 generated :func:`.or_` expression, from a given list of expressions,
1586 a "default" element of :func:`_sql.false` (or just ``False``) should be
1587 specified::
1589 from sqlalchemy import false
1591 or_criteria = or_(false(), *expressions)
1593 The above expression will compile to SQL as the expression ``false``
1594 or ``0 = 1``, depending on backend, if no other expressions are
1595 present. If expressions are present, then the :func:`_sql.false` value
1596 is ignored as it does not affect the outcome of an OR expression which
1597 has other elements.
1599 .. deprecated:: 1.4 The :func:`.or_` element now requires that at
1600 least one argument is passed; creating the :func:`.or_` construct
1601 with no arguments is deprecated, and will emit a deprecation warning
1602 while continuing to produce a blank SQL string.
1604 .. seealso::
1606 :func:`.and_`
1608 """ # noqa: E501
1609 return BooleanClauseList.or_(*clauses)
1612def over(
1613 element: FunctionElement[_T],
1614 partition_by: Optional[_ByArgument] = None,
1615 order_by: Optional[_ByArgument] = None,
1616 range_: Optional[typing_Tuple[Optional[int], Optional[int]]] = None,
1617 rows: Optional[typing_Tuple[Optional[int], Optional[int]]] = None,
1618 groups: Optional[typing_Tuple[Optional[int], Optional[int]]] = None,
1619) -> Over[_T]:
1620 r"""Produce an :class:`.Over` object against a function.
1622 Used against aggregate or so-called "window" functions,
1623 for database backends that support window functions.
1625 :func:`_expression.over` is usually called using
1626 the :meth:`.FunctionElement.over` method, e.g.::
1628 func.row_number().over(order_by=mytable.c.some_column)
1630 Would produce:
1632 .. sourcecode:: sql
1634 ROW_NUMBER() OVER(ORDER BY some_column)
1636 Ranges are also possible using the :paramref:`.expression.over.range_`,
1637 :paramref:`.expression.over.rows`, and :paramref:`.expression.over.groups`
1638 parameters. These
1639 mutually-exclusive parameters each accept a 2-tuple, which contains
1640 a combination of integers and None::
1642 func.row_number().over(order_by=my_table.c.some_column, range_=(None, 0))
1644 The above would produce:
1646 .. sourcecode:: sql
1648 ROW_NUMBER() OVER(ORDER BY some_column
1649 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
1651 A value of ``None`` indicates "unbounded", a
1652 value of zero indicates "current row", and negative / positive
1653 integers indicate "preceding" and "following":
1655 * RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING::
1657 func.row_number().over(order_by="x", range_=(-5, 10))
1659 * ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW::
1661 func.row_number().over(order_by="x", rows=(None, 0))
1663 * RANGE BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING::
1665 func.row_number().over(order_by="x", range_=(-2, None))
1667 * RANGE BETWEEN 1 FOLLOWING AND 3 FOLLOWING::
1669 func.row_number().over(order_by="x", range_=(1, 3))
1671 * GROUPS BETWEEN 1 FOLLOWING AND 3 FOLLOWING::
1673 func.row_number().over(order_by="x", groups=(1, 3))
1675 :param element: a :class:`.FunctionElement`, :class:`.WithinGroup`,
1676 or other compatible construct.
1677 :param partition_by: a column element or string, or a list
1678 of such, that will be used as the PARTITION BY clause
1679 of the OVER construct.
1680 :param order_by: a column element or string, or a list
1681 of such, that will be used as the ORDER BY clause
1682 of the OVER construct.
1683 :param range\_: optional range clause for the window. This is a
1684 tuple value which can contain integer values or ``None``,
1685 and will render a RANGE BETWEEN PRECEDING / FOLLOWING clause.
1686 :param rows: optional rows clause for the window. This is a tuple
1687 value which can contain integer values or None, and will render
1688 a ROWS BETWEEN PRECEDING / FOLLOWING clause.
1689 :param groups: optional groups clause for the window. This is a
1690 tuple value which can contain integer values or ``None``,
1691 and will render a GROUPS BETWEEN PRECEDING / FOLLOWING clause.
1693 .. versionadded:: 2.0.40
1695 This function is also available from the :data:`~.expression.func`
1696 construct itself via the :meth:`.FunctionElement.over` method.
1698 .. seealso::
1700 :ref:`tutorial_window_functions` - in the :ref:`unified_tutorial`
1702 :data:`.expression.func`
1704 :func:`_expression.within_group`
1706 """ # noqa: E501
1707 return Over(element, partition_by, order_by, range_, rows, groups)
1710@_document_text_coercion("text", ":func:`.text`", ":paramref:`.text.text`")
1711def text(text: str) -> TextClause:
1712 r"""Construct a new :class:`_expression.TextClause` clause,
1713 representing
1714 a textual SQL string directly.
1716 E.g.::
1718 from sqlalchemy import text
1720 t = text("SELECT * FROM users")
1721 result = connection.execute(t)
1723 The advantages :func:`_expression.text`
1724 provides over a plain string are
1725 backend-neutral support for bind parameters, per-statement
1726 execution options, as well as
1727 bind parameter and result-column typing behavior, allowing
1728 SQLAlchemy type constructs to play a role when executing
1729 a statement that is specified literally. The construct can also
1730 be provided with a ``.c`` collection of column elements, allowing
1731 it to be embedded in other SQL expression constructs as a subquery.
1733 Bind parameters are specified by name, using the format ``:name``.
1734 E.g.::
1736 t = text("SELECT * FROM users WHERE id=:user_id")
1737 result = connection.execute(t, {"user_id": 12})
1739 For SQL statements where a colon is required verbatim, as within
1740 an inline string, use a backslash to escape::
1742 t = text(r"SELECT * FROM users WHERE name='\:username'")
1744 The :class:`_expression.TextClause`
1745 construct includes methods which can
1746 provide information about the bound parameters as well as the column
1747 values which would be returned from the textual statement, assuming
1748 it's an executable SELECT type of statement. The
1749 :meth:`_expression.TextClause.bindparams`
1750 method is used to provide bound
1751 parameter detail, and :meth:`_expression.TextClause.columns`
1752 method allows
1753 specification of return columns including names and types::
1755 t = (
1756 text("SELECT * FROM users WHERE id=:user_id")
1757 .bindparams(user_id=7)
1758 .columns(id=Integer, name=String)
1759 )
1761 for id, name in connection.execute(t):
1762 print(id, name)
1764 The :func:`_expression.text` construct is used in cases when
1765 a literal string SQL fragment is specified as part of a larger query,
1766 such as for the WHERE clause of a SELECT statement::
1768 s = select(users.c.id, users.c.name).where(text("id=:user_id"))
1769 result = connection.execute(s, {"user_id": 12})
1771 :func:`_expression.text` is also used for the construction
1772 of a full, standalone statement using plain text.
1773 As such, SQLAlchemy refers
1774 to it as an :class:`.Executable` object and may be used
1775 like any other statement passed to an ``.execute()`` method.
1777 :param text:
1778 the text of the SQL statement to be created. Use ``:<param>``
1779 to specify bind parameters; they will be compiled to their
1780 engine-specific format.
1782 .. seealso::
1784 :ref:`tutorial_select_arbitrary_text`
1786 """
1787 return TextClause(text)
1790def true() -> True_:
1791 """Return a constant :class:`.True_` construct.
1793 E.g.:
1795 .. sourcecode:: pycon+sql
1797 >>> from sqlalchemy import true
1798 >>> print(select(t.c.x).where(true()))
1799 {printsql}SELECT x FROM t WHERE true
1801 A backend which does not support true/false constants will render as
1802 an expression against 1 or 0:
1804 .. sourcecode:: pycon+sql
1806 >>> print(select(t.c.x).where(true()))
1807 {printsql}SELECT x FROM t WHERE 1 = 1
1809 The :func:`.true` and :func:`.false` constants also feature
1810 "short circuit" operation within an :func:`.and_` or :func:`.or_`
1811 conjunction:
1813 .. sourcecode:: pycon+sql
1815 >>> print(select(t.c.x).where(or_(t.c.x > 5, true())))
1816 {printsql}SELECT x FROM t WHERE true{stop}
1818 >>> print(select(t.c.x).where(and_(t.c.x > 5, false())))
1819 {printsql}SELECT x FROM t WHERE false{stop}
1821 .. seealso::
1823 :func:`.false`
1825 """
1827 return True_._instance()
1830def tuple_(
1831 *clauses: _ColumnExpressionOrLiteralArgument[Any],
1832 types: Optional[Sequence[_TypeEngineArgument[Any]]] = None,
1833) -> Tuple:
1834 """Return a :class:`.Tuple`.
1836 Main usage is to produce a composite IN construct using
1837 :meth:`.ColumnOperators.in_` ::
1839 from sqlalchemy import tuple_
1841 tuple_(table.c.col1, table.c.col2).in_([(1, 2), (5, 12), (10, 19)])
1843 .. warning::
1845 The composite IN construct is not supported by all backends, and is
1846 currently known to work on PostgreSQL, MySQL, and SQLite.
1847 Unsupported backends will raise a subclass of
1848 :class:`~sqlalchemy.exc.DBAPIError` when such an expression is
1849 invoked.
1851 """
1852 return Tuple(*clauses, types=types)
1855def type_coerce(
1856 expression: _ColumnExpressionOrLiteralArgument[Any],
1857 type_: _TypeEngineArgument[_T],
1858) -> TypeCoerce[_T]:
1859 r"""Associate a SQL expression with a particular type, without rendering
1860 ``CAST``.
1862 E.g.::
1864 from sqlalchemy import type_coerce
1866 stmt = select(type_coerce(log_table.date_string, StringDateTime()))
1868 The above construct will produce a :class:`.TypeCoerce` object, which
1869 does not modify the rendering in any way on the SQL side, with the
1870 possible exception of a generated label if used in a columns clause
1871 context:
1873 .. sourcecode:: sql
1875 SELECT date_string AS date_string FROM log
1877 When result rows are fetched, the ``StringDateTime`` type processor
1878 will be applied to result rows on behalf of the ``date_string`` column.
1880 .. note:: the :func:`.type_coerce` construct does not render any
1881 SQL syntax of its own, including that it does not imply
1882 parenthesization. Please use :meth:`.TypeCoerce.self_group`
1883 if explicit parenthesization is required.
1885 In order to provide a named label for the expression, use
1886 :meth:`_expression.ColumnElement.label`::
1888 stmt = select(
1889 type_coerce(log_table.date_string, StringDateTime()).label("date")
1890 )
1892 A type that features bound-value handling will also have that behavior
1893 take effect when literal values or :func:`.bindparam` constructs are
1894 passed to :func:`.type_coerce` as targets.
1895 For example, if a type implements the
1896 :meth:`.TypeEngine.bind_expression`
1897 method or :meth:`.TypeEngine.bind_processor` method or equivalent,
1898 these functions will take effect at statement compilation/execution
1899 time when a literal value is passed, as in::
1901 # bound-value handling of MyStringType will be applied to the
1902 # literal value "some string"
1903 stmt = select(type_coerce("some string", MyStringType))
1905 When using :func:`.type_coerce` with composed expressions, note that
1906 **parenthesis are not applied**. If :func:`.type_coerce` is being
1907 used in an operator context where the parenthesis normally present from
1908 CAST are necessary, use the :meth:`.TypeCoerce.self_group` method:
1910 .. sourcecode:: pycon+sql
1912 >>> some_integer = column("someint", Integer)
1913 >>> some_string = column("somestr", String)
1914 >>> expr = type_coerce(some_integer + 5, String) + some_string
1915 >>> print(expr)
1916 {printsql}someint + :someint_1 || somestr{stop}
1917 >>> expr = type_coerce(some_integer + 5, String).self_group() + some_string
1918 >>> print(expr)
1919 {printsql}(someint + :someint_1) || somestr{stop}
1921 :param expression: A SQL expression, such as a
1922 :class:`_expression.ColumnElement`
1923 expression or a Python string which will be coerced into a bound
1924 literal value.
1926 :param type\_: A :class:`.TypeEngine` class or instance indicating
1927 the type to which the expression is coerced.
1929 .. seealso::
1931 :ref:`tutorial_casts`
1933 :func:`.cast`
1935 """ # noqa
1936 return TypeCoerce(expression, type_)
1939def within_group(
1940 element: FunctionElement[_T], *order_by: _ColumnExpressionArgument[Any]
1941) -> WithinGroup[_T]:
1942 r"""Produce a :class:`.WithinGroup` object against a function.
1944 Used against so-called "ordered set aggregate" and "hypothetical
1945 set aggregate" functions, including :class:`.percentile_cont`,
1946 :class:`.rank`, :class:`.dense_rank`, etc. This feature is typically
1947 used by Oracle Database, Microsoft SQL Server.
1949 For generalized ORDER BY of aggregate functions on all included
1950 backends, including PostgreSQL, MySQL/MariaDB, SQLite as well as Oracle
1951 and SQL Server, the :func:`_sql.aggregate_order_by` provides a more
1952 general approach that compiles to "WITHIN GROUP" only on those backends
1953 which require it.
1955 :func:`_expression.within_group` is usually called using
1956 the :meth:`.FunctionElement.within_group` method, e.g.::
1958 stmt = select(
1959 func.percentile_cont(0.5).within_group(department.c.salary.desc()),
1960 )
1962 The above statement would produce SQL similar to
1963 ``SELECT percentile_cont(0.5)
1964 WITHIN GROUP (ORDER BY department.salary DESC)``.
1966 :param element: a :class:`.FunctionElement` construct, typically
1967 generated by :data:`~.expression.func`.
1968 :param \*order_by: one or more column elements that will be used
1969 as the ORDER BY clause of the WITHIN GROUP construct.
1971 .. seealso::
1973 :ref:`tutorial_functions_within_group` - in the
1974 :ref:`unified_tutorial`
1976 :func:`_sql.aggregate_order_by` - helper for PostgreSQL, MySQL,
1977 SQLite aggregate functions
1979 :data:`.expression.func`
1981 :func:`_expression.over`
1983 """
1984 return WithinGroup(element, *order_by)
1987def aggregate_order_by(
1988 element: FunctionElement[_T], *order_by: _ColumnExpressionArgument[Any]
1989) -> AggregateOrderBy[_T]:
1990 r"""Produce a :class:`.AggregateOrderBy` object against a function.
1992 Used for aggregating functions such as :class:`_functions.array_agg`,
1993 ``group_concat``, ``json_agg`` on backends that support ordering via an
1994 embedded ``ORDER BY`` parameter, e.g. PostgreSQL, MySQL/MariaDB, SQLite.
1995 When used on backends like Oracle and SQL Server, SQL compilation uses that
1996 of :class:`.WithinGroup`. On PostgreSQL, compilation is fixed at embedded
1997 ``ORDER BY``; for set aggregation functions where PostgreSQL requires the
1998 use of ``WITHIN GROUP``, :func:`_expression.within_group` should be used
1999 explicitly.
2001 :func:`_expression.aggregate_order_by` is usually called using
2002 the :meth:`.FunctionElement.aggregate_order_by` method, e.g.::
2004 stmt = select(
2005 func.array_agg(department.c.code).aggregate_order_by(
2006 department.c.code.desc()
2007 ),
2008 )
2010 which would produce an expression resembling:
2012 .. sourcecode:: sql
2014 SELECT array_agg(department.code ORDER BY department.code DESC)
2015 AS array_agg_1 FROM department
2017 The ORDER BY argument may also be multiple terms.
2019 When using the backend-agnostic :class:`_functions.aggregate_strings`
2020 string aggregation function, use the
2021 :paramref:`_functions.aggregate_strings.order_by` parameter to indicate a
2022 dialect-agnostic ORDER BY expression.
2024 .. versionadded:: 2.0.44 Generalized the PostgreSQL-specific
2025 :func:`_postgresql.aggregate_order_by` function to a method on
2026 :class:`.Function` that is backend agnostic.
2028 .. seealso::
2030 :class:`_functions.aggregate_strings` - backend-agnostic string
2031 concatenation function which also supports ORDER BY
2033 """ # noqa: E501
2034 return AggregateOrderBy(element, *order_by)