Shortcuts on this page
r m x toggle line displays
j k next/prev highlighted chunk
0 (zero) top of page
1 (one) first highlighted chunk
1# sql/_elements_constructors.py
2# Copyright (C) 2005-2026 the SQLAlchemy authors and contributors
3# <see AUTHORS file>
4#
5# This module is part of SQLAlchemy and is released under
6# the MIT License: https://www.opensource.org/licenses/mit-license.php
8from __future__ import annotations
10import typing
11from typing import Any
12from typing import Callable
13from typing import Literal
14from typing import Mapping
15from typing import Optional
16from typing import overload
17from typing import Sequence
18from typing import Tuple as typing_Tuple
19from typing import TYPE_CHECKING
20from typing import TypeVar
21from typing import Union
23from . import coercions
24from . import operators
25from . import roles
26from .base import _NoArg
27from .coercions import _document_text_coercion
28from .elements import AggregateOrderBy
29from .elements import BindParameter
30from .elements import BooleanClauseList
31from .elements import Case
32from .elements import Cast
33from .elements import CollationClause
34from .elements import CollectionAggregate
35from .elements import ColumnClause
36from .elements import ColumnElement
37from .elements import DMLTargetCopy
38from .elements import Extract
39from .elements import False_
40from .elements import FunctionFilter
41from .elements import Label
42from .elements import Null
43from .elements import OrderByList
44from .elements import Over
45from .elements import TextClause
46from .elements import True_
47from .elements import TryCast
48from .elements import TString
49from .elements import Tuple
50from .elements import TypeCoerce
51from .elements import UnaryExpression
52from .elements import WithinGroup
53from .functions import FunctionElement
55if typing.TYPE_CHECKING:
56 from ._typing import _ByArgument
57 from ._typing import _ColumnExpressionArgument
58 from ._typing import _ColumnExpressionOrLiteralArgument
59 from ._typing import _ColumnExpressionOrStrLabelArgument
60 from ._typing import _OnlyColumnArgument
61 from ._typing import _TypeEngineArgument
62 from .elements import _FrameIntTuple
63 from .elements import BinaryExpression
64 from .elements import FrameClause
65 from .selectable import FromClause
66 from .type_api import TypeEngine
67 from ..util.compat import Template
69_T = TypeVar("_T")
72def all_(expr: _ColumnExpressionArgument[_T]) -> CollectionAggregate[bool]:
73 """Produce an ALL expression.
75 For dialects such as that of PostgreSQL, this operator applies
76 to usage of the :class:`_types.ARRAY` datatype, for that of
77 MySQL, it may apply to a subquery. e.g.::
79 # renders on PostgreSQL:
80 # '5 = ALL (somearray)'
81 expr = 5 == all_(mytable.c.somearray)
83 # renders on MySQL:
84 # '5 = ALL (SELECT value FROM table)'
85 expr = 5 == all_(select(table.c.value))
87 When using a Python sequence with PostgreSQL, wrap it with
88 :func:`_sql.literal` to produce a SQL expression. Whether the sequence
89 can be adapted to an array parameter is DBAPI-specific::
91 expr = 5 == all_(literal([1, 2, 3]))
93 Comparison to NULL may work using ``None``::
95 None == all_(mytable.c.somearray)
97 The any_() / all_() operators also feature a special "operand flipping"
98 behavior such that if any_() / all_() are used on the left side of a
99 comparison using a standalone operator such as ``==``, ``!=``, etc.
100 (not including operator methods such as
101 :meth:`_sql.ColumnOperators.is_`) the rendered expression is flipped::
103 # would render '5 = ALL (column)`
104 all_(mytable.c.column) == 5
106 Or with ``None``, which note will not perform
107 the usual step of rendering "IS" as is normally the case for NULL::
109 # would render 'NULL = ALL(somearray)'
110 all_(mytable.c.somearray) == None
112 The column-level :meth:`_sql.ColumnElement.all_` method (not to be
113 confused with the deprecated :class:`_types.ARRAY` level
114 :meth:`_types.ARRAY.Comparator.all`) is shorthand for
115 ``all_(col)``::
117 5 == mytable.c.somearray.all_()
119 .. seealso::
121 :meth:`_sql.ColumnOperators.all_`
123 :func:`_expression.any_`
125 """
126 if isinstance(expr, operators.ColumnOperators):
127 return expr.all_()
128 else:
129 return CollectionAggregate._create_all(expr)
132def and_( # type: ignore[empty-body]
133 initial_clause: Union[Literal[True], _ColumnExpressionArgument[bool]],
134 *clauses: _ColumnExpressionArgument[bool],
135) -> ColumnElement[bool]:
136 r"""Produce a conjunction of expressions joined by ``AND``.
138 E.g.::
140 from sqlalchemy import and_
142 stmt = select(users_table).where(
143 and_(users_table.c.name == "wendy", users_table.c.enrolled == True)
144 )
146 The :func:`.and_` conjunction is also available using the
147 Python ``&`` operator (though note that compound expressions
148 need to be parenthesized in order to function with Python
149 operator precedence behavior)::
151 stmt = select(users_table).where(
152 (users_table.c.name == "wendy") & (users_table.c.enrolled == True)
153 )
155 The :func:`.and_` operation is also implicit in some cases;
156 the :meth:`_expression.Select.where`
157 method for example can be invoked multiple
158 times against a statement, which will have the effect of each
159 clause being combined using :func:`.and_`::
161 stmt = (
162 select(users_table)
163 .where(users_table.c.name == "wendy")
164 .where(users_table.c.enrolled == True)
165 )
167 The :func:`.and_` construct must be given at least one positional
168 argument in order to be valid; a :func:`.and_` construct with no
169 arguments is ambiguous. To produce an "empty" or dynamically
170 generated :func:`.and_` expression, from a given list of expressions,
171 a "default" element of :func:`_sql.true` (or just ``True``) should be
172 specified::
174 from sqlalchemy import true
176 criteria = and_(true(), *expressions)
178 The above expression will compile to SQL as the expression ``true``
179 or ``1 = 1``, depending on backend, if no other expressions are
180 present. If expressions are present, then the :func:`_sql.true` value is
181 ignored as it does not affect the outcome of an AND expression that
182 has other elements.
184 .. deprecated:: 1.4 The :func:`.and_` element now requires that at
185 least one argument is passed; creating the :func:`.and_` construct
186 with no arguments is deprecated, and will emit a deprecation warning
187 while continuing to produce a blank SQL string.
189 .. seealso::
191 :func:`.or_`
193 """
194 ...
197if not TYPE_CHECKING:
198 # handle deprecated case which allows zero-arguments
199 def and_(*clauses): # noqa: F811
200 r"""Produce a conjunction of expressions joined by ``AND``.
202 E.g.::
204 from sqlalchemy import and_
206 stmt = select(users_table).where(
207 and_(users_table.c.name == "wendy", users_table.c.enrolled == True)
208 )
210 The :func:`.and_` conjunction is also available using the
211 Python ``&`` operator (though note that compound expressions
212 need to be parenthesized in order to function with Python
213 operator precedence behavior)::
215 stmt = select(users_table).where(
216 (users_table.c.name == "wendy") & (users_table.c.enrolled == True)
217 )
219 The :func:`.and_` operation is also implicit in some cases;
220 the :meth:`_expression.Select.where`
221 method for example can be invoked multiple
222 times against a statement, which will have the effect of each
223 clause being combined using :func:`.and_`::
225 stmt = (
226 select(users_table)
227 .where(users_table.c.name == "wendy")
228 .where(users_table.c.enrolled == True)
229 )
231 The :func:`.and_` construct must be given at least one positional
232 argument in order to be valid; a :func:`.and_` construct with no
233 arguments is ambiguous. To produce an "empty" or dynamically
234 generated :func:`.and_` expression, from a given list of expressions,
235 a "default" element of :func:`_sql.true` (or just ``True``) should be
236 specified::
238 from sqlalchemy import true
240 criteria = and_(true(), *expressions)
242 The above expression will compile to SQL as the expression ``true``
243 or ``1 = 1``, depending on backend, if no other expressions are
244 present. If expressions are present, then the :func:`_sql.true` value
245 is ignored as it does not affect the outcome of an AND expression that
246 has other elements.
248 .. deprecated:: 1.4 The :func:`.and_` element now requires that at
249 least one argument is passed; creating the :func:`.and_` construct
250 with no arguments is deprecated, and will emit a deprecation warning
251 while continuing to produce a blank SQL string.
253 .. seealso::
255 :func:`.or_`
257 """ # noqa: E501
258 return BooleanClauseList.and_(*clauses)
261def any_(expr: _ColumnExpressionArgument[_T]) -> CollectionAggregate[bool]:
262 """Produce an ANY expression.
264 For dialects such as that of PostgreSQL, this operator applies
265 to usage of the :class:`_types.ARRAY` datatype, for that of
266 MySQL, it may apply to a subquery. e.g.::
268 # renders on PostgreSQL:
269 # '5 = ANY (somearray)'
270 expr = 5 == any_(mytable.c.somearray)
272 # renders on MySQL:
273 # '5 = ANY (SELECT value FROM table)'
274 expr = 5 == any_(select(table.c.value))
276 When using a Python sequence with PostgreSQL, wrap it with
277 :func:`_sql.literal` to produce a SQL expression. Whether the sequence
278 can be adapted to an array parameter is DBAPI-specific::
280 expr = 5 == any_(literal([1, 2, 3]))
282 Comparison to NULL may work using ``None`` or :func:`_sql.null`::
284 None == any_(mytable.c.somearray)
286 The any_() / all_() operators also feature a special "operand flipping"
287 behavior such that if any_() / all_() are used on the left side of a
288 comparison using a standalone operator such as ``==``, ``!=``, etc.
289 (not including operator methods such as
290 :meth:`_sql.ColumnOperators.is_`) the rendered expression is flipped::
292 # would render '5 = ANY (column)`
293 any_(mytable.c.column) == 5
295 Or with ``None``, which note will not perform
296 the usual step of rendering "IS" as is normally the case for NULL::
298 # would render 'NULL = ANY(somearray)'
299 any_(mytable.c.somearray) == None
301 The column-level :meth:`_sql.ColumnElement.any_` method (not to be
302 confused with the deprecated :class:`_types.ARRAY` level
303 :meth:`_types.ARRAY.Comparator.any`) is shorthand for
304 ``any_(col)``::
306 5 = mytable.c.somearray.any_()
308 .. seealso::
310 :meth:`_sql.ColumnOperators.any_`
312 :func:`_expression.all_`
314 """
315 if isinstance(expr, operators.ColumnOperators):
316 return expr.any_()
317 else:
318 return CollectionAggregate._create_any(expr)
321@overload
322def asc(
323 column: Union[str, "ColumnElement[_T]"],
324) -> UnaryExpression[_T]: ...
327@overload
328def asc(
329 column: _ColumnExpressionOrStrLabelArgument[_T],
330) -> Union[OrderByList, UnaryExpression[_T]]: ...
333def asc(
334 column: _ColumnExpressionOrStrLabelArgument[_T],
335) -> Union[OrderByList, UnaryExpression[_T]]:
336 """Produce an ascending ``ORDER BY`` clause element.
338 e.g.::
340 from sqlalchemy import asc
342 stmt = select(users_table).order_by(asc(users_table.c.name))
344 will produce SQL as:
346 .. sourcecode:: sql
348 SELECT id, name FROM user ORDER BY name ASC
350 The :func:`.asc` function is a standalone version of the
351 :meth:`_expression.ColumnElement.asc`
352 method available on all SQL expressions,
353 e.g.::
356 stmt = select(users_table).order_by(users_table.c.name.asc())
358 :param column: A :class:`_expression.ColumnElement` (e.g.
359 scalar SQL expression)
360 with which to apply the :func:`.asc` operation.
362 .. seealso::
364 :func:`.desc`
366 :func:`.nulls_first`
368 :func:`.nulls_last`
370 :meth:`_expression.Select.order_by`
372 """
374 if isinstance(column, operators.OrderingOperators):
375 return column.asc() # type: ignore[unused-ignore]
376 else:
377 return UnaryExpression._create_asc(column)
380def collate(
381 expression: _ColumnExpressionArgument[str],
382 collation: str,
383 collation_schema: Optional[str] = None,
384) -> BinaryExpression[str]:
385 """Return the clause ``expression COLLATE collation``.
387 e.g.::
389 collate(mycolumn, "utf8_bin")
391 produces:
393 .. sourcecode:: sql
395 mycolumn COLLATE utf8_bin
397 The collation expression is also quoted if it is a case sensitive
398 identifier, e.g. contains uppercase characters.
400 :param expression: the column expression to apply a collation to.
402 :param collation: the name of the collation.
404 :param collation_schema: optional, the name of the schema in which the
405 collation is defined, for use with database backends that support
406 schema-qualified collations, currently PostgreSQL.
408 .. versionadded:: 2.1
410 """
411 if isinstance(expression, operators.ColumnOperators):
412 return expression.collate( # type: ignore[return-value]
413 collation, collation_schema=collation_schema
414 )
415 else:
416 return CollationClause._create_collation_expression(
417 expression, collation, collation_schema
418 )
421def between(
422 expr: _ColumnExpressionOrLiteralArgument[_T],
423 lower_bound: Any,
424 upper_bound: Any,
425 symmetric: bool = False,
426) -> BinaryExpression[bool]:
427 """Produce a ``BETWEEN`` predicate clause.
429 E.g.::
431 from sqlalchemy import between
433 stmt = select(users_table).where(between(users_table.c.id, 5, 7))
435 Would produce SQL resembling:
437 .. sourcecode:: sql
439 SELECT id, name FROM user WHERE id BETWEEN :id_1 AND :id_2
441 The :func:`.between` function is a standalone version of the
442 :meth:`_expression.ColumnElement.between` method available on all
443 SQL expressions, as in::
445 stmt = select(users_table).where(users_table.c.id.between(5, 7))
447 All arguments passed to :func:`.between`, including the left side
448 column expression, are coerced from Python scalar values if a
449 the value is not a :class:`_expression.ColumnElement` subclass.
450 For example,
451 three fixed values can be compared as in::
453 print(between(5, 3, 7))
455 Which would produce::
457 :param_1 BETWEEN :param_2 AND :param_3
459 :param expr: a column expression, typically a
460 :class:`_expression.ColumnElement`
461 instance or alternatively a Python scalar expression to be coerced
462 into a column expression, serving as the left side of the ``BETWEEN``
463 expression.
465 :param lower_bound: a column or Python scalar expression serving as the
466 lower bound of the right side of the ``BETWEEN`` expression.
468 :param upper_bound: a column or Python scalar expression serving as the
469 upper bound of the right side of the ``BETWEEN`` expression.
471 :param symmetric: if True, will render " BETWEEN SYMMETRIC ". Note
472 that not all databases support this syntax.
474 .. seealso::
476 :meth:`_expression.ColumnElement.between`
478 """
479 col_expr = coercions.expect(roles.ExpressionElementRole, expr)
480 return col_expr.between(lower_bound, upper_bound, symmetric=symmetric)
483def outparam(
484 key: str, type_: Optional[TypeEngine[_T]] = None
485) -> BindParameter[_T]:
486 """Create an 'OUT' parameter for usage in functions (stored procedures),
487 for databases which support them.
489 The ``outparam`` can be used like a regular function parameter.
490 The "output" value will be available from the
491 :class:`~sqlalchemy.engine.CursorResult` object via its ``out_parameters``
492 attribute, which returns a dictionary containing the values.
494 """
495 return BindParameter(key, None, type_=type_, unique=False, isoutparam=True)
498@overload
499def not_(clause: BinaryExpression[_T]) -> BinaryExpression[_T]: ...
502@overload
503def not_(clause: _ColumnExpressionArgument[_T]) -> ColumnElement[_T]: ...
506def not_(clause: _ColumnExpressionArgument[_T]) -> ColumnElement[_T]:
507 """Return a negation of the given clause, i.e. ``NOT(clause)``.
509 The ``~`` operator is also overloaded on all
510 :class:`_expression.ColumnElement` subclasses to produce the
511 same result.
513 """
515 return coercions.expect(roles.ExpressionElementRole, clause).__invert__()
518def from_dml_column(column: _OnlyColumnArgument[_T]) -> DMLTargetCopy[_T]:
519 r"""A placeholder that may be used in compiled INSERT or UPDATE expressions
520 to refer to the SQL expression or value being applied to another column.
522 Given a table such as::
524 t = Table(
525 "t",
526 MetaData(),
527 Column("x", Integer),
528 Column("y", Integer),
529 )
531 The :func:`_sql.from_dml_column` construct allows automatic copying
532 of an expression assigned to a different column to be reused::
534 >>> stmt = t.insert().values(x=func.foobar(3), y=from_dml_column(t.c.x) + 5)
535 >>> print(stmt)
536 INSERT INTO t (x, y) VALUES (foobar(:foobar_1), (foobar(:foobar_1) + :param_1))
538 The :func:`_sql.from_dml_column` construct is intended to be useful primarily
539 with event-based hooks such as those used by ORM hybrids.
541 .. seealso::
543 :ref:`hybrid_bulk_update`
545 .. versionadded:: 2.1
548 """ # noqa: E501
550 return DMLTargetCopy(column)
553def bindparam(
554 key: Optional[str],
555 value: Any = _NoArg.NO_ARG,
556 type_: Optional[_TypeEngineArgument[_T]] = None,
557 unique: bool = False,
558 required: Union[bool, Literal[_NoArg.NO_ARG]] = _NoArg.NO_ARG,
559 quote: Optional[bool] = None,
560 callable_: Optional[Callable[[], Any]] = None,
561 expanding: bool = False,
562 isoutparam: bool = False,
563 literal_execute: bool = False,
564) -> BindParameter[_T]:
565 r"""Produce a "bound expression".
567 The return value is an instance of :class:`.BindParameter`; this
568 is a :class:`_expression.ColumnElement`
569 subclass which represents a so-called
570 "placeholder" value in a SQL expression, the value of which is
571 supplied at the point at which the statement in executed against a
572 database connection.
574 In SQLAlchemy, the :func:`.bindparam` construct has
575 the ability to carry along the actual value that will be ultimately
576 used at expression time. In this way, it serves not just as
577 a "placeholder" for eventual population, but also as a means of
578 representing so-called "unsafe" values which should not be rendered
579 directly in a SQL statement, but rather should be passed along
580 to the :term:`DBAPI` as values which need to be correctly escaped
581 and potentially handled for type-safety.
583 When using :func:`.bindparam` explicitly, the use case is typically
584 one of traditional deferment of parameters; the :func:`.bindparam`
585 construct accepts a name which can then be referred to at execution
586 time::
588 from sqlalchemy import bindparam
590 stmt = select(users_table).where(
591 users_table.c.name == bindparam("username")
592 )
594 The above statement, when rendered, will produce SQL similar to:
596 .. sourcecode:: sql
598 SELECT id, name FROM user WHERE name = :username
600 In order to populate the value of ``:username`` above, the value
601 would typically be applied at execution time to a method
602 like :meth:`_engine.Connection.execute`::
604 result = connection.execute(stmt, {"username": "wendy"})
606 Explicit use of :func:`.bindparam` is also common when producing
607 UPDATE or DELETE statements that are to be invoked multiple times,
608 where the WHERE criterion of the statement is to change on each
609 invocation, such as::
611 stmt = (
612 users_table.update()
613 .where(user_table.c.name == bindparam("username"))
614 .values(fullname=bindparam("fullname"))
615 )
617 connection.execute(
618 stmt,
619 [
620 {"username": "wendy", "fullname": "Wendy Smith"},
621 {"username": "jack", "fullname": "Jack Jones"},
622 ],
623 )
625 SQLAlchemy's Core expression system makes wide use of
626 :func:`.bindparam` in an implicit sense. It is typical that Python
627 literal values passed to virtually all SQL expression functions are
628 coerced into fixed :func:`.bindparam` constructs. For example, given
629 a comparison operation such as::
631 expr = users_table.c.name == "Wendy"
633 The above expression will produce a :class:`.BinaryExpression`
634 construct, where the left side is the :class:`_schema.Column` object
635 representing the ``name`` column, and the right side is a
636 :class:`.BindParameter` representing the literal value::
638 print(repr(expr.right))
639 BindParameter("%(4327771088 name)s", "Wendy", type_=String())
641 The expression above will render SQL such as:
643 .. sourcecode:: sql
645 user.name = :name_1
647 Where the ``:name_1`` parameter name is an anonymous name. The
648 actual string ``Wendy`` is not in the rendered string, but is carried
649 along where it is later used within statement execution. If we
650 invoke a statement like the following::
652 stmt = select(users_table).where(users_table.c.name == "Wendy")
653 result = connection.execute(stmt)
655 We would see SQL logging output as:
657 .. sourcecode:: sql
659 SELECT "user".id, "user".name
660 FROM "user"
661 WHERE "user".name = %(name_1)s
662 {'name_1': 'Wendy'}
664 Above, we see that ``Wendy`` is passed as a parameter to the database,
665 while the placeholder ``:name_1`` is rendered in the appropriate form
666 for the target database, in this case the PostgreSQL database.
668 Similarly, :func:`.bindparam` is invoked automatically when working
669 with :term:`CRUD` statements as far as the "VALUES" portion is
670 concerned. The :func:`_expression.insert` construct produces an
671 ``INSERT`` expression which will, at statement execution time, generate
672 bound placeholders based on the arguments passed, as in::
674 stmt = users_table.insert()
675 result = connection.execute(stmt, {"name": "Wendy"})
677 The above will produce SQL output as:
679 .. sourcecode:: sql
681 INSERT INTO "user" (name) VALUES (%(name)s)
682 {'name': 'Wendy'}
684 The :class:`_expression.Insert` construct, at
685 compilation/execution time, rendered a single :func:`.bindparam`
686 mirroring the column name ``name`` as a result of the single ``name``
687 parameter we passed to the :meth:`_engine.Connection.execute` method.
689 :param key:
690 the key (e.g. the name) for this bind param.
691 Will be used in the generated
692 SQL statement for dialects that use named parameters. This
693 value may be modified when part of a compilation operation,
694 if other :class:`BindParameter` objects exist with the same
695 key, or if its length is too long and truncation is
696 required.
698 If omitted, an "anonymous" name is generated for the bound parameter;
699 when given a value to bind, the end result is equivalent to calling upon
700 the :func:`.literal` function with a value to bind, particularly
701 if the :paramref:`.bindparam.unique` parameter is also provided.
703 :param value:
704 Initial value for this bind param. Will be used at statement
705 execution time as the value for this parameter passed to the
706 DBAPI, if no other value is indicated to the statement execution
707 method for this particular parameter name. Defaults to ``None``.
709 :param callable\_:
710 A callable function that takes the place of "value". The function
711 will be called at statement execution time to determine the
712 ultimate value. Used for scenarios where the actual bind
713 value cannot be determined at the point at which the clause
714 construct is created, but embedded bind values are still desirable.
716 :param type\_:
717 A :class:`.TypeEngine` class or instance representing an optional
718 datatype for this :func:`.bindparam`. If not passed, a type
719 may be determined automatically for the bind, based on the given
720 value; for example, trivial Python types such as ``str``,
721 ``int``, ``bool``
722 may result in the :class:`.String`, :class:`.Integer` or
723 :class:`.Boolean` types being automatically selected.
725 The type of a :func:`.bindparam` is significant especially in that
726 the type will apply pre-processing to the value before it is
727 passed to the database. For example, a :func:`.bindparam` which
728 refers to a datetime value, and is specified as holding the
729 :class:`.DateTime` type, may apply conversion needed to the
730 value (such as stringification on SQLite) before passing the value
731 to the database.
733 :param unique:
734 if True, the key name of this :class:`.BindParameter` will be
735 modified if another :class:`.BindParameter` of the same name
736 already has been located within the containing
737 expression. This flag is used generally by the internals
738 when producing so-called "anonymous" bound expressions, it
739 isn't generally applicable to explicitly-named :func:`.bindparam`
740 constructs.
742 :param required:
743 If ``True``, a value is required at execution time. If not passed,
744 it defaults to ``True`` if neither :paramref:`.bindparam.value`
745 or :paramref:`.bindparam.callable` were passed. If either of these
746 parameters are present, then :paramref:`.bindparam.required`
747 defaults to ``False``.
749 :param quote:
750 True if this parameter name requires quoting and is not
751 currently known as a SQLAlchemy reserved word; this currently
752 only applies to the Oracle Database backends, where bound names must
753 sometimes be quoted.
755 :param isoutparam:
756 if True, the parameter should be treated like a stored procedure
757 "OUT" parameter. This applies to backends such as Oracle Database which
758 support OUT parameters.
760 :param expanding:
761 if True, this parameter will be treated as an "expanding" parameter
762 at execution time; the parameter value is expected to be a sequence,
763 rather than a scalar value, and the string SQL statement will
764 be transformed on a per-execution basis to accommodate the sequence
765 with a variable number of parameter slots passed to the DBAPI.
766 This is to allow statement caching to be used in conjunction with
767 an IN clause.
769 .. seealso::
771 :meth:`.ColumnOperators.in_`
773 :ref:`baked_in` - with baked queries
775 .. note:: The "expanding" feature does not support "executemany"-
776 style parameter sets.
778 :param literal_execute:
779 if True, the bound parameter will be rendered in the compile phase
780 with a special "POSTCOMPILE" token, and the SQLAlchemy compiler will
781 render the final value of the parameter into the SQL statement at
782 statement execution time, omitting the value from the parameter
783 dictionary / list passed to DBAPI ``cursor.execute()``. This
784 produces a similar effect as that of using the ``literal_binds``,
785 compilation flag, however takes place as the statement is sent to
786 the DBAPI ``cursor.execute()`` method, rather than when the statement
787 is compiled. The primary use of this
788 capability is for rendering LIMIT / OFFSET clauses for database
789 drivers that can't accommodate for bound parameters in these
790 contexts, while allowing SQL constructs to be cacheable at the
791 compilation level.
793 .. versionadded:: 1.4 Added "post compile" bound parameters
795 .. seealso::
797 :ref:`change_4808`.
799 .. seealso::
801 :ref:`tutorial_sending_parameters` - in the
802 :ref:`unified_tutorial`
805 """
806 return BindParameter(
807 key,
808 value,
809 type_,
810 unique,
811 required,
812 quote,
813 callable_,
814 expanding,
815 isoutparam,
816 literal_execute,
817 )
820def case(
821 *whens: Union[
822 typing_Tuple[_ColumnExpressionArgument[bool], Any], Mapping[Any, Any]
823 ],
824 value: Optional[Any] = None,
825 else_: Optional[Any] = None,
826) -> Case[Any]:
827 r"""Produce a ``CASE`` expression.
829 The ``CASE`` construct in SQL is a conditional object that
830 acts somewhat analogously to an "if/then" construct in other
831 languages. It returns an instance of :class:`.Case`.
833 :func:`.case` in its usual form is passed a series of "when"
834 constructs, that is, a list of conditions and results as tuples::
836 from sqlalchemy import case
838 stmt = select(users_table).where(
839 case(
840 (users_table.c.name == "wendy", "W"),
841 (users_table.c.name == "jack", "J"),
842 else_="E",
843 )
844 )
846 The above statement will produce SQL resembling:
848 .. sourcecode:: sql
850 SELECT id, name FROM user
851 WHERE CASE
852 WHEN (name = :name_1) THEN :param_1
853 WHEN (name = :name_2) THEN :param_2
854 ELSE :param_3
855 END
857 When simple equality expressions of several values against a single
858 parent column are needed, :func:`.case` also has a "shorthand" format
859 used via the
860 :paramref:`.case.value` parameter, which is passed a column
861 expression to be compared. In this form, the :paramref:`.case.whens`
862 parameter is passed as a dictionary containing expressions to be
863 compared against keyed to result expressions. The statement below is
864 equivalent to the preceding statement::
866 stmt = select(users_table).where(
867 case({"wendy": "W", "jack": "J"}, value=users_table.c.name, else_="E")
868 )
870 The values which are accepted as result values in
871 :paramref:`.case.whens` as well as with :paramref:`.case.else_` are
872 coerced from Python literals into :func:`.bindparam` constructs.
873 SQL expressions, e.g. :class:`_expression.ColumnElement` constructs,
874 are accepted
875 as well. To coerce a literal string expression into a constant
876 expression rendered inline, use the :func:`_expression.literal_column`
877 construct,
878 as in::
880 from sqlalchemy import case, literal_column
882 case(
883 (orderline.c.qty > 100, literal_column("'greaterthan100'")),
884 (orderline.c.qty > 10, literal_column("'greaterthan10'")),
885 else_=literal_column("'lessthan10'"),
886 )
888 The above will render the given constants without using bound
889 parameters for the result values (but still for the comparison
890 values), as in:
892 .. sourcecode:: sql
894 CASE
895 WHEN (orderline.qty > :qty_1) THEN 'greaterthan100'
896 WHEN (orderline.qty > :qty_2) THEN 'greaterthan10'
897 ELSE 'lessthan10'
898 END
900 :param \*whens: The criteria to be compared against,
901 :paramref:`.case.whens` accepts two different forms, based on
902 whether or not :paramref:`.case.value` is used.
904 .. versionchanged:: 1.4 the :func:`_sql.case`
905 function now accepts the series of WHEN conditions positionally
907 In the first form, it accepts multiple 2-tuples passed as positional
908 arguments; each 2-tuple consists of ``(<sql expression>, <value>)``,
909 where the SQL expression is a boolean expression and "value" is a
910 resulting value, e.g.::
912 case(
913 (users_table.c.name == "wendy", "W"),
914 (users_table.c.name == "jack", "J"),
915 )
917 In the second form, it accepts a Python dictionary of comparison
918 values mapped to a resulting value; this form requires
919 :paramref:`.case.value` to be present, and values will be compared
920 using the ``==`` operator, e.g.::
922 case({"wendy": "W", "jack": "J"}, value=users_table.c.name)
924 :param value: An optional SQL expression which will be used as a
925 fixed "comparison point" for candidate values within a dictionary
926 passed to :paramref:`.case.whens`.
928 :param else\_: An optional SQL expression which will be the evaluated
929 result of the ``CASE`` construct if all expressions within
930 :paramref:`.case.whens` evaluate to false. When omitted, most
931 databases will produce a result of NULL if none of the "when"
932 expressions evaluate to true.
935 """ # noqa: E501
936 return Case(*whens, value=value, else_=else_)
939def cast(
940 expression: _ColumnExpressionOrLiteralArgument[Any],
941 type_: _TypeEngineArgument[_T],
942) -> Cast[_T]:
943 r"""Produce a ``CAST`` expression.
945 :func:`.cast` returns an instance of :class:`.Cast`.
947 E.g.::
949 from sqlalchemy import cast, Numeric
951 stmt = select(cast(product_table.c.unit_price, Numeric(10, 4)))
953 The above statement will produce SQL resembling:
955 .. sourcecode:: sql
957 SELECT CAST(unit_price AS NUMERIC(10, 4)) FROM product
959 The :func:`.cast` function performs two distinct functions when
960 used. The first is that it renders the ``CAST`` expression within
961 the resulting SQL string. The second is that it associates the given
962 type (e.g. :class:`.TypeEngine` class or instance) with the column
963 expression on the Python side, which means the expression will take
964 on the expression operator behavior associated with that type,
965 as well as the bound-value handling and result-row-handling behavior
966 of the type.
968 An alternative to :func:`.cast` is the :func:`.type_coerce` function.
969 This function performs the second task of associating an expression
970 with a specific type, but does not render the ``CAST`` expression
971 in SQL.
973 :param expression: A SQL expression, such as a
974 :class:`_expression.ColumnElement`
975 expression or a Python string which will be coerced into a bound
976 literal value.
978 :param type\_: A :class:`.TypeEngine` class or instance indicating
979 the type to which the ``CAST`` should apply.
981 .. seealso::
983 :ref:`tutorial_casts`
985 :func:`.try_cast` - an alternative to CAST that results in
986 NULLs when the cast fails, instead of raising an error.
987 Only supported by some dialects.
989 :func:`.type_coerce` - an alternative to CAST that coerces the type
990 on the Python side only, which is often sufficient to generate the
991 correct SQL and data coercion.
994 """
995 return Cast(expression, type_)
998def try_cast(
999 expression: _ColumnExpressionOrLiteralArgument[Any],
1000 type_: _TypeEngineArgument[_T],
1001) -> TryCast[_T]:
1002 """Produce a ``TRY_CAST`` expression for backends which support it;
1003 this is a ``CAST`` which returns NULL for un-castable conversions.
1005 In SQLAlchemy, this construct is supported **only** by the SQL Server
1006 dialect, and will raise a :class:`.CompileError` if used on other
1007 included backends. However, third party backends may also support
1008 this construct.
1010 .. tip:: As :func:`_sql.try_cast` originates from the SQL Server dialect,
1011 it's importable both from ``sqlalchemy.`` as well as from
1012 ``sqlalchemy.dialects.mssql``.
1014 :func:`_sql.try_cast` returns an instance of :class:`.TryCast` and
1015 generally behaves similarly to the :class:`.Cast` construct;
1016 at the SQL level, the difference between ``CAST`` and ``TRY_CAST``
1017 is that ``TRY_CAST`` returns NULL for an un-castable expression,
1018 such as attempting to cast a string ``"hi"`` to an integer value.
1020 E.g.::
1022 from sqlalchemy import select, try_cast, Numeric
1024 stmt = select(try_cast(product_table.c.unit_price, Numeric(10, 4)))
1026 The above would render on Microsoft SQL Server as:
1028 .. sourcecode:: sql
1030 SELECT TRY_CAST (product_table.unit_price AS NUMERIC(10, 4))
1031 FROM product_table
1033 .. versionadded:: 2.0.14 :func:`.try_cast` has been
1034 generalized from the SQL Server dialect into a general use
1035 construct that may be supported by additional dialects.
1037 """
1038 return TryCast(expression, type_)
1041def column(
1042 text: str,
1043 type_: Optional[_TypeEngineArgument[_T]] = None,
1044 is_literal: bool = False,
1045 _selectable: Optional[FromClause] = None,
1046) -> ColumnClause[_T]:
1047 """Produce a :class:`.ColumnClause` object.
1049 The :class:`.ColumnClause` is a lightweight analogue to the
1050 :class:`_schema.Column` class. The :func:`_expression.column`
1051 function can
1052 be invoked with just a name alone, as in::
1054 from sqlalchemy import column
1056 id, name = column("id"), column("name")
1057 stmt = select(id, name).select_from("user")
1059 The above statement would produce SQL like:
1061 .. sourcecode:: sql
1063 SELECT id, name FROM user
1065 Once constructed, :func:`_expression.column`
1066 may be used like any other SQL
1067 expression element such as within :func:`_expression.select`
1068 constructs::
1070 from sqlalchemy.sql import column
1072 id, name = column("id"), column("name")
1073 stmt = select(id, name).select_from("user")
1075 The text handled by :func:`_expression.column`
1076 is assumed to be handled
1077 like the name of a database column; if the string contains mixed case,
1078 special characters, or matches a known reserved word on the target
1079 backend, the column expression will render using the quoting
1080 behavior determined by the backend. To produce a textual SQL
1081 expression that is rendered exactly without any quoting,
1082 use :func:`_expression.literal_column` instead,
1083 or pass ``True`` as the
1084 value of :paramref:`_expression.column.is_literal`. Additionally,
1085 full SQL
1086 statements are best handled using the :func:`_expression.text`
1087 construct.
1089 :func:`_expression.column` can be used in a table-like
1090 fashion by combining it with the :func:`.table` function
1091 (which is the lightweight analogue to :class:`_schema.Table`
1092 ) to produce
1093 a working table construct with minimal boilerplate::
1095 from sqlalchemy import table, column, select
1097 user = table(
1098 "user",
1099 column("id"),
1100 column("name"),
1101 column("description"),
1102 )
1104 stmt = select(user.c.description).where(user.c.name == "wendy")
1106 A :func:`_expression.column` / :func:`.table`
1107 construct like that illustrated
1108 above can be created in an
1109 ad-hoc fashion and is not associated with any
1110 :class:`_schema.MetaData`, DDL, or events, unlike its
1111 :class:`_schema.Table` counterpart.
1113 :param text: the text of the element.
1115 :param type: :class:`_types.TypeEngine` object which can associate
1116 this :class:`.ColumnClause` with a type.
1118 :param is_literal: if True, the :class:`.ColumnClause` is assumed to
1119 be an exact expression that will be delivered to the output with no
1120 quoting rules applied regardless of case sensitive settings. the
1121 :func:`_expression.literal_column()` function essentially invokes
1122 :func:`_expression.column` while passing ``is_literal=True``.
1124 .. seealso::
1126 :class:`_schema.Column`
1128 :func:`_expression.literal_column`
1130 :func:`.table`
1132 :func:`_expression.text`
1134 :ref:`tutorial_select_arbitrary_text`
1136 """
1137 return ColumnClause(text, type_, is_literal, _selectable)
1140@overload
1141def desc(
1142 column: Union[str, "ColumnElement[_T]"],
1143) -> UnaryExpression[_T]: ...
1146@overload
1147def desc(
1148 column: _ColumnExpressionOrStrLabelArgument[_T],
1149) -> Union[OrderByList, UnaryExpression[_T]]: ...
1152def desc(
1153 column: _ColumnExpressionOrStrLabelArgument[_T],
1154) -> Union[OrderByList, UnaryExpression[_T]]:
1155 """Produce a descending ``ORDER BY`` clause element.
1157 e.g.::
1159 from sqlalchemy import desc
1161 stmt = select(users_table).order_by(desc(users_table.c.name))
1163 will produce SQL as:
1165 .. sourcecode:: sql
1167 SELECT id, name FROM user ORDER BY name DESC
1169 The :func:`.desc` function is a standalone version of the
1170 :meth:`_expression.ColumnElement.desc`
1171 method available on all SQL expressions,
1172 e.g.::
1175 stmt = select(users_table).order_by(users_table.c.name.desc())
1177 :param column: A :class:`_expression.ColumnElement` (e.g.
1178 scalar SQL expression)
1179 with which to apply the :func:`.desc` operation.
1181 .. seealso::
1183 :func:`.asc`
1185 :func:`.nulls_first`
1187 :func:`.nulls_last`
1189 :meth:`_expression.Select.order_by`
1191 """
1192 if isinstance(column, operators.OrderingOperators):
1193 return column.desc() # type: ignore[unused-ignore]
1194 else:
1195 return UnaryExpression._create_desc(column)
1198def distinct(expr: _ColumnExpressionArgument[_T]) -> UnaryExpression[_T]:
1199 """Produce an column-expression-level unary ``DISTINCT`` clause.
1201 This applies the ``DISTINCT`` keyword to an **individual column
1202 expression** (e.g. not the whole statement), and renders **specifically
1203 in that column position**; this is used for containment within
1204 an aggregate function, as in::
1206 from sqlalchemy import distinct, func
1208 stmt = select(users_table.c.id, func.count(distinct(users_table.c.name)))
1210 The above would produce an statement resembling:
1212 .. sourcecode:: sql
1214 SELECT user.id, count(DISTINCT user.name) FROM user
1216 .. tip:: The :func:`_sql.distinct` function does **not** apply DISTINCT
1217 to the full SELECT statement, instead applying a DISTINCT modifier
1218 to **individual column expressions**. For general ``SELECT DISTINCT``
1219 support, use the
1220 :meth:`_sql.Select.distinct` method on :class:`_sql.Select`.
1222 The :func:`.distinct` function is also available as a column-level
1223 method, e.g. :meth:`_expression.ColumnElement.distinct`, as in::
1225 stmt = select(func.count(users_table.c.name.distinct()))
1227 The :func:`.distinct` operator is different from the
1228 :meth:`_expression.Select.distinct` method of
1229 :class:`_expression.Select`,
1230 which produces a ``SELECT`` statement
1231 with ``DISTINCT`` applied to the result set as a whole,
1232 e.g. a ``SELECT DISTINCT`` expression. See that method for further
1233 information.
1235 .. seealso::
1237 :meth:`_expression.ColumnElement.distinct`
1239 :meth:`_expression.Select.distinct`
1241 :data:`.func`
1243 """ # noqa: E501
1244 if isinstance(expr, operators.ColumnOperators):
1245 return expr.distinct()
1246 else:
1247 return UnaryExpression._create_distinct(expr)
1250def bitwise_not(expr: _ColumnExpressionArgument[_T]) -> UnaryExpression[_T]:
1251 """Produce a unary bitwise NOT clause, typically via the ``~`` operator.
1253 Not to be confused with boolean negation :func:`_sql.not_`.
1255 .. versionadded:: 2.0.2
1257 .. seealso::
1259 :ref:`operators_bitwise`
1262 """
1263 if isinstance(expr, operators.ColumnOperators):
1264 return expr.bitwise_not()
1265 else:
1266 return UnaryExpression._create_bitwise_not(expr)
1269def extract(field: str, expr: _ColumnExpressionArgument[Any]) -> Extract:
1270 """Return a :class:`.Extract` construct.
1272 This is typically available as :func:`.extract`
1273 as well as ``func.extract`` from the
1274 :data:`.func` namespace.
1276 :param field: The field to extract.
1278 .. warning:: This field is used as a literal SQL string.
1279 **DO NOT PASS UNTRUSTED INPUT TO THIS STRING**.
1281 :param expr: A column or Python scalar expression serving as the
1282 right side of the ``EXTRACT`` expression.
1284 E.g.::
1286 from sqlalchemy import extract
1287 from sqlalchemy import table, column
1289 logged_table = table(
1290 "user",
1291 column("id"),
1292 column("date_created"),
1293 )
1295 stmt = select(logged_table.c.id).where(
1296 extract("YEAR", logged_table.c.date_created) == 2021
1297 )
1299 In the above example, the statement is used to select ids from the
1300 database where the ``YEAR`` component matches a specific value.
1302 Similarly, one can also select an extracted component::
1304 stmt = select(extract("YEAR", logged_table.c.date_created)).where(
1305 logged_table.c.id == 1
1306 )
1308 The implementation of ``EXTRACT`` may vary across database backends.
1309 Users are reminded to consult their database documentation.
1310 """
1311 return Extract(field, expr)
1314def false() -> False_:
1315 """Return a :class:`.False_` construct.
1317 E.g.:
1319 .. sourcecode:: pycon+sql
1321 >>> from sqlalchemy import false
1322 >>> print(select(t.c.x).where(false()))
1323 {printsql}SELECT x FROM t WHERE false
1325 A backend which does not support true/false constants will render as
1326 an expression against 1 or 0:
1328 .. sourcecode:: pycon+sql
1330 >>> print(select(t.c.x).where(false()))
1331 {printsql}SELECT x FROM t WHERE 0 = 1
1333 The :func:`.true` and :func:`.false` constants also feature
1334 "short circuit" operation within an :func:`.and_` or :func:`.or_`
1335 conjunction:
1337 .. sourcecode:: pycon+sql
1339 >>> print(select(t.c.x).where(or_(t.c.x > 5, true())))
1340 {printsql}SELECT x FROM t WHERE true{stop}
1342 >>> print(select(t.c.x).where(and_(t.c.x > 5, false())))
1343 {printsql}SELECT x FROM t WHERE false{stop}
1345 .. seealso::
1347 :func:`.true`
1349 """
1351 return False_._instance()
1354def funcfilter(
1355 func: FunctionElement[_T], *criterion: _ColumnExpressionArgument[bool]
1356) -> FunctionFilter[_T]:
1357 """Produce a :class:`.FunctionFilter` object against a function.
1359 Used against aggregate and window functions,
1360 for database backends that support the "FILTER" clause.
1362 E.g.::
1364 from sqlalchemy import funcfilter
1366 funcfilter(func.count(1), MyClass.name == "some name")
1368 Would produce "COUNT(1) FILTER (WHERE myclass.name = 'some name')".
1370 This function is also available from the :data:`~.expression.func`
1371 construct itself via the :meth:`.FunctionElement.filter` method.
1373 .. seealso::
1375 :ref:`tutorial_functions_within_group` - in the
1376 :ref:`unified_tutorial`
1378 :meth:`.FunctionElement.filter`
1380 """
1381 return FunctionFilter(func, *criterion)
1384def label(
1385 name: str,
1386 element: _ColumnExpressionArgument[_T],
1387 type_: Optional[_TypeEngineArgument[_T]] = None,
1388) -> Label[_T]:
1389 """Return a :class:`Label` object for the
1390 given :class:`_expression.ColumnElement`.
1392 A label changes the name of an element in the columns clause of a
1393 ``SELECT`` statement, typically via the ``AS`` SQL keyword.
1395 This functionality is more conveniently available via the
1396 :meth:`_expression.ColumnElement.label` method on
1397 :class:`_expression.ColumnElement`.
1399 :param name: label name
1401 :param obj: a :class:`_expression.ColumnElement`.
1403 """
1404 return Label(name, element, type_)
1407def null() -> Null:
1408 """Return a constant :class:`.Null` construct."""
1410 return Null._instance()
1413@overload
1414def nulls_first(
1415 column: "ColumnElement[_T]",
1416) -> UnaryExpression[_T]: ...
1419@overload
1420def nulls_first(
1421 column: _ColumnExpressionArgument[_T],
1422) -> Union[OrderByList, UnaryExpression[_T]]: ...
1425def nulls_first(
1426 column: _ColumnExpressionArgument[_T],
1427) -> Union[OrderByList, UnaryExpression[_T]]:
1428 """Produce the ``NULLS FIRST`` modifier for an ``ORDER BY`` expression.
1430 :func:`.nulls_first` is intended to modify the expression produced
1431 by :func:`.asc` or :func:`.desc`, and indicates how NULL values
1432 should be handled when they are encountered during ordering::
1435 from sqlalchemy import desc, nulls_first
1437 stmt = select(users_table).order_by(nulls_first(desc(users_table.c.name)))
1439 The SQL expression from the above would resemble:
1441 .. sourcecode:: sql
1443 SELECT id, name FROM user ORDER BY name DESC NULLS FIRST
1445 Like :func:`.asc` and :func:`.desc`, :func:`.nulls_first` is typically
1446 invoked from the column expression itself using
1447 :meth:`_expression.ColumnElement.nulls_first`,
1448 rather than as its standalone
1449 function version, as in::
1451 stmt = select(users_table).order_by(
1452 users_table.c.name.desc().nulls_first()
1453 )
1455 .. versionchanged:: 1.4 :func:`.nulls_first` is renamed from
1456 :func:`.nullsfirst` in previous releases.
1457 The previous name remains available for backwards compatibility.
1459 .. seealso::
1461 :func:`.asc`
1463 :func:`.desc`
1465 :func:`.nulls_last`
1467 :meth:`_expression.Select.order_by`
1469 """ # noqa: E501
1470 if isinstance(column, operators.OrderingOperators):
1471 return column.nulls_first()
1472 else:
1473 return UnaryExpression._create_nulls_first(column)
1476@overload
1477def nulls_last(
1478 column: "ColumnElement[_T]",
1479) -> UnaryExpression[_T]: ...
1482@overload
1483def nulls_last(
1484 column: _ColumnExpressionArgument[_T],
1485) -> Union[OrderByList, UnaryExpression[_T]]: ...
1488def nulls_last(
1489 column: _ColumnExpressionArgument[_T],
1490) -> Union[OrderByList, UnaryExpression[_T]]:
1491 """Produce the ``NULLS LAST`` modifier for an ``ORDER BY`` expression.
1493 :func:`.nulls_last` is intended to modify the expression produced
1494 by :func:`.asc` or :func:`.desc`, and indicates how NULL values
1495 should be handled when they are encountered during ordering::
1498 from sqlalchemy import desc, nulls_last
1500 stmt = select(users_table).order_by(nulls_last(desc(users_table.c.name)))
1502 The SQL expression from the above would resemble:
1504 .. sourcecode:: sql
1506 SELECT id, name FROM user ORDER BY name DESC NULLS LAST
1508 Like :func:`.asc` and :func:`.desc`, :func:`.nulls_last` is typically
1509 invoked from the column expression itself using
1510 :meth:`_expression.ColumnElement.nulls_last`,
1511 rather than as its standalone
1512 function version, as in::
1514 stmt = select(users_table).order_by(users_table.c.name.desc().nulls_last())
1516 .. versionchanged:: 1.4 :func:`.nulls_last` is renamed from
1517 :func:`.nullslast` in previous releases.
1518 The previous name remains available for backwards compatibility.
1520 .. seealso::
1522 :func:`.asc`
1524 :func:`.desc`
1526 :func:`.nulls_first`
1528 :meth:`_expression.Select.order_by`
1530 """ # noqa: E501
1531 if isinstance(column, operators.OrderingOperators):
1532 return column.nulls_last()
1533 else:
1534 return UnaryExpression._create_nulls_last(column)
1537def or_( # type: ignore[empty-body]
1538 initial_clause: Union[Literal[False], _ColumnExpressionArgument[bool]],
1539 *clauses: _ColumnExpressionArgument[bool],
1540) -> ColumnElement[bool]:
1541 """Produce a conjunction of expressions joined by ``OR``.
1543 E.g.::
1545 from sqlalchemy import or_
1547 stmt = select(users_table).where(
1548 or_(users_table.c.name == "wendy", users_table.c.name == "jack")
1549 )
1551 The :func:`.or_` conjunction is also available using the
1552 Python ``|`` operator (though note that compound expressions
1553 need to be parenthesized in order to function with Python
1554 operator precedence behavior)::
1556 stmt = select(users_table).where(
1557 (users_table.c.name == "wendy") | (users_table.c.name == "jack")
1558 )
1560 The :func:`.or_` construct must be given at least one positional
1561 argument in order to be valid; a :func:`.or_` construct with no
1562 arguments is ambiguous. To produce an "empty" or dynamically
1563 generated :func:`.or_` expression, from a given list of expressions,
1564 a "default" element of :func:`_sql.false` (or just ``False``) should be
1565 specified::
1567 from sqlalchemy import false
1569 or_criteria = or_(false(), *expressions)
1571 The above expression will compile to SQL as the expression ``false``
1572 or ``0 = 1``, depending on backend, if no other expressions are
1573 present. If expressions are present, then the :func:`_sql.false` value is
1574 ignored as it does not affect the outcome of an OR expression which
1575 has other elements.
1577 .. deprecated:: 1.4 The :func:`.or_` element now requires that at
1578 least one argument is passed; creating the :func:`.or_` construct
1579 with no arguments is deprecated, and will emit a deprecation warning
1580 while continuing to produce a blank SQL string.
1582 .. seealso::
1584 :func:`.and_`
1586 """
1587 ...
1590if not TYPE_CHECKING:
1591 # handle deprecated case which allows zero-arguments
1592 def or_(*clauses): # noqa: F811
1593 """Produce a conjunction of expressions joined by ``OR``.
1595 E.g.::
1597 from sqlalchemy import or_
1599 stmt = select(users_table).where(
1600 or_(users_table.c.name == "wendy", users_table.c.name == "jack")
1601 )
1603 The :func:`.or_` conjunction is also available using the
1604 Python ``|`` operator (though note that compound expressions
1605 need to be parenthesized in order to function with Python
1606 operator precedence behavior)::
1608 stmt = select(users_table).where(
1609 (users_table.c.name == "wendy") | (users_table.c.name == "jack")
1610 )
1612 The :func:`.or_` construct must be given at least one positional
1613 argument in order to be valid; a :func:`.or_` construct with no
1614 arguments is ambiguous. To produce an "empty" or dynamically
1615 generated :func:`.or_` expression, from a given list of expressions,
1616 a "default" element of :func:`_sql.false` (or just ``False``) should be
1617 specified::
1619 from sqlalchemy import false
1621 or_criteria = or_(false(), *expressions)
1623 The above expression will compile to SQL as the expression ``false``
1624 or ``0 = 1``, depending on backend, if no other expressions are
1625 present. If expressions are present, then the :func:`_sql.false` value
1626 is ignored as it does not affect the outcome of an OR expression which
1627 has other elements.
1629 .. deprecated:: 1.4 The :func:`.or_` element now requires that at
1630 least one argument is passed; creating the :func:`.or_` construct
1631 with no arguments is deprecated, and will emit a deprecation warning
1632 while continuing to produce a blank SQL string.
1634 .. seealso::
1636 :func:`.and_`
1638 """ # noqa: E501
1639 return BooleanClauseList.or_(*clauses)
1642def over(
1643 element: FunctionElement[_T],
1644 partition_by: _ByArgument | None = None,
1645 order_by: _ByArgument | None = None,
1646 range_: _FrameIntTuple | FrameClause | None = None,
1647 rows: _FrameIntTuple | FrameClause | None = None,
1648 groups: _FrameIntTuple | FrameClause | None = None,
1649 exclude: str | None = None,
1650) -> Over[_T]:
1651 r"""Produce an :class:`.Over` object against a function.
1653 Used against aggregate or so-called "window" functions,
1654 for database backends that support window functions.
1656 :func:`_expression.over` is usually called using
1657 the :meth:`.FunctionElement.over` method, e.g.::
1659 func.row_number().over(order_by=mytable.c.some_column)
1661 Would produce:
1663 .. sourcecode:: sql
1665 ROW_NUMBER() OVER(ORDER BY some_column)
1667 Ranges are also possible using the :paramref:`.expression.over.range_`,
1668 :paramref:`.expression.over.rows`, and :paramref:`.expression.over.groups`
1669 parameters. These
1670 mutually-exclusive parameters each accept a 2-tuple, which contains
1671 a combination of integers and None::
1673 func.row_number().over(order_by=my_table.c.some_column, range_=(None, 0))
1675 The above would produce:
1677 .. sourcecode:: sql
1679 ROW_NUMBER() OVER(ORDER BY some_column
1680 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
1682 A value of ``None`` indicates "unbounded", a
1683 value of zero indicates "current row", and negative / positive
1684 integers indicate "preceding" and "following":
1686 * RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING::
1688 func.row_number().over(order_by="x", range_=(-5, 10))
1690 * ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW::
1692 func.row_number().over(order_by="x", rows=(None, 0))
1694 * RANGE BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING::
1696 func.row_number().over(order_by="x", range_=(-2, None))
1698 * RANGE BETWEEN 1 FOLLOWING AND 3 FOLLOWING::
1700 func.row_number().over(order_by="x", range_=(1, 3))
1702 * GROUPS BETWEEN 1 FOLLOWING AND 3 FOLLOWING::
1704 func.row_number().over(order_by="x", groups=(1, 3))
1706 Depending on the type of the order column, the 'RANGE' value may not be
1707 an integer. In this case use a :class:`_expression.FrameClause` directly
1708 to specify the frame boundaries. E.g.::
1710 from datetime import timedelta
1711 from sqlalchemy import FrameClause, FrameClauseType
1713 func.sum(my_table.c.amount).over(
1714 order_by=my_table.c.date,
1715 range_=FrameClause(
1716 start=timedelta(days=7),
1717 end=None,
1718 start_frame_type=FrameClauseType.PRECEDING,
1719 end_frame_type=FrameClauseType.UNBOUNDED,
1720 ),
1721 )
1723 .. versionchanged:: 2.1 Added support for range types that are not
1724 integer-based, via the :class:`_expression.FrameClause` construct.
1726 :param element: a :class:`.FunctionElement`, :class:`.WithinGroup`,
1727 or other compatible construct.
1728 :param partition_by: a column element or string, or a list
1729 of such, that will be used as the PARTITION BY clause
1730 of the OVER construct.
1731 :param order_by: a column element or string, or a list
1732 of such, that will be used as the ORDER BY clause
1733 of the OVER construct.
1734 :param range\_: optional range clause for the window. This is a
1735 two-tuple value which can contain integer values or ``None``,
1736 and will render a RANGE BETWEEN PRECEDING / FOLLOWING clause.
1737 Can also be a :class:`_expression.FrameClause` instance to
1738 specify non-integer values.
1740 .. versionchanged:: 2.1 Added support for range types that are not
1741 integer-based, via the :class:`_expression.FrameClause` construct.
1743 :param rows: optional rows clause for the window. This is a two-tuple
1744 value which can contain integer values or None, and will render
1745 a ROWS BETWEEN PRECEDING / FOLLOWING clause. Can also be a
1746 :class:`_expression.FrameClause` instance.
1747 :param groups: optional groups clause for the window. This is a
1748 two-tuple value which can contain integer values or ``None``,
1749 and will render a GROUPS BETWEEN PRECEDING / FOLLOWING clause.
1750 Can also be a :class:`_expression.FrameClause` instance.
1752 .. versionadded:: 2.0.40
1754 :param exclude: optional string for the frame exclusion clause. This is a
1755 string value which can be one of ``CURRENT ROW``, ``GROUP``, ``TIES``, or
1756 ``NO OTHERS`` and will render an EXCLUDE clause within the window frame
1757 specification. Requires that one of :paramref:`_sql.over.rows`,
1758 :paramref:`_sql.over.range_`, or :paramref:`_sql.over.groups` is also
1759 specified.
1761 .. versionadded:: 2.1
1763 This function is also available from the :data:`~.expression.func`
1764 construct itself via the :meth:`.FunctionElement.over` method.
1766 .. seealso::
1768 :ref:`tutorial_window_functions` - in the :ref:`unified_tutorial`
1770 :data:`.expression.func`
1772 :func:`_expression.within_group`
1774 """ # noqa: E501
1775 return Over(
1776 element,
1777 partition_by,
1778 order_by,
1779 range_,
1780 rows,
1781 groups,
1782 exclude,
1783 )
1786@_document_text_coercion("text", ":func:`.text`", ":paramref:`.text.text`")
1787def text(text: str) -> TextClause:
1788 r"""Construct a new :class:`_expression.TextClause` clause,
1789 representing
1790 a textual SQL string directly.
1792 E.g.::
1794 from sqlalchemy import text
1796 t = text("SELECT * FROM users")
1797 result = connection.execute(t)
1799 The advantages :func:`_expression.text`
1800 provides over a plain string are
1801 backend-neutral support for bind parameters, per-statement
1802 execution options, as well as
1803 bind parameter and result-column typing behavior, allowing
1804 SQLAlchemy type constructs to play a role when executing
1805 a statement that is specified literally. The construct can also
1806 be provided with a ``.c`` collection of column elements, allowing
1807 it to be embedded in other SQL expression constructs as a subquery.
1809 Bind parameters are specified by name, using the format ``:name``.
1810 E.g.::
1812 t = text("SELECT * FROM users WHERE id=:user_id")
1813 result = connection.execute(t, {"user_id": 12})
1815 For SQL statements where a colon is required verbatim, as within
1816 an inline string, use a backslash to escape::
1818 t = text(r"SELECT * FROM users WHERE name='\:username'")
1820 The :class:`_expression.TextClause`
1821 construct includes methods which can
1822 provide information about the bound parameters as well as the column
1823 values which would be returned from the textual statement, assuming
1824 it's an executable SELECT type of statement. The
1825 :meth:`_expression.TextClause.bindparams`
1826 method is used to provide bound
1827 parameter detail, and :meth:`_expression.TextClause.columns`
1828 method allows
1829 specification of return columns including names and types::
1831 t = (
1832 text("SELECT * FROM users WHERE id=:user_id")
1833 .bindparams(user_id=7)
1834 .columns(id=Integer, name=String)
1835 )
1837 for id, name in connection.execute(t):
1838 print(id, name)
1840 The :func:`_expression.text` construct is used in cases when
1841 a literal string SQL fragment is specified as part of a larger query,
1842 such as for the WHERE clause of a SELECT statement::
1844 s = select(users.c.id, users.c.name).where(text("id=:user_id"))
1845 result = connection.execute(s, {"user_id": 12})
1847 :func:`_expression.text` is also used for the construction
1848 of a full, standalone statement using plain text.
1849 As such, SQLAlchemy refers
1850 to it as an :class:`.Executable` object and may be used
1851 like any other statement passed to an ``.execute()`` method.
1853 :param text:
1854 the text of the SQL statement to be created. Use ``:<param>``
1855 to specify bind parameters; they will be compiled to their
1856 engine-specific format.
1858 .. seealso::
1860 :ref:`tutorial_select_arbitrary_text`
1862 """
1863 return TextClause(text)
1866def tstring(template: Template) -> TString:
1867 r"""Construct a new :class:`_expression.TString` clause,
1868 representing a SQL template string using Python 3.14+ t-strings.
1870 .. versionadded:: 2.1
1872 E.g.::
1874 from sqlalchemy import tstring
1876 a = 5
1877 b = 10
1878 stmt = tstring(t"select {a}, {b}")
1879 result = connection.execute(stmt)
1881 The :func:`_expression.tstring` function accepts a Python 3.14+
1882 template string (t-string) and processes it to create a SQL statement.
1883 Unlike :func:`_expression.text`, which requires manual bind parameter
1884 specification, :func:`_expression.tstring` automatically handles
1885 interpolation of Python values and SQLAlchemy expressions.
1887 **Interpolation Behavior**:
1889 - **SQL content** expressed in the plain string portions of the template
1890 are rendered directly as SQL
1891 - **SQLAlchemy expressions** (columns, functions, etc.) are embedded
1892 as clause elements
1893 - **Plain Python values** are automatically wrapped in
1894 :func:`_expression.literal`
1896 For example::
1898 from sqlalchemy import tstring, select, literal, JSON, table, column
1900 # Python values become bound parameters
1901 user_id = 42
1902 stmt = tstring(t"SELECT * FROM users WHERE id = {user_id}")
1903 # renders: SELECT * FROM users WHERE id = :param_1
1905 # SQLAlchemy expressions are embedded
1906 stmt = tstring(t"SELECT {column('q')} FROM {table('t')}")
1907 # renders: SELECT q FROM t
1909 # Apply explicit SQL types to bound values using literal()
1910 some_json = {"foo": "bar"}
1911 stmt = tstring(t"SELECT {literal(some_json, JSON)}")
1913 **Column Specification**:
1915 Like :func:`_expression.text`, the :func:`_expression.tstring` construct
1916 supports the :meth:`_expression.TString.columns` method to specify
1917 return columns and their types::
1919 from sqlalchemy import tstring, column, Integer, String
1921 stmt = tstring(t"SELECT id, name FROM users").columns(
1922 column("id", Integer), column("name", String)
1923 )
1925 for id, name in connection.execute(stmt):
1926 print(id, name)
1928 :param template:
1929 a Python 3.14+ template string (t-string) containing SQL fragments
1930 and Python expressions to be interpolated.
1932 .. seealso::
1934 :ref:`tutorial_select_arbitrary_text` - in the :ref:`unified_tutorial`
1936 :class:`_expression.TString`
1938 :func:`_expression.text`
1940 `PEP 750 <https://peps.python.org/pep-0750/>`_ - Template Strings
1942 """
1943 return TString(template)
1946def true() -> True_:
1947 """Return a constant :class:`.True_` construct.
1949 E.g.:
1951 .. sourcecode:: pycon+sql
1953 >>> from sqlalchemy import true
1954 >>> print(select(t.c.x).where(true()))
1955 {printsql}SELECT x FROM t WHERE true
1957 A backend which does not support true/false constants will render as
1958 an expression against 1 or 0:
1960 .. sourcecode:: pycon+sql
1962 >>> print(select(t.c.x).where(true()))
1963 {printsql}SELECT x FROM t WHERE 1 = 1
1965 The :func:`.true` and :func:`.false` constants also feature
1966 "short circuit" operation within an :func:`.and_` or :func:`.or_`
1967 conjunction:
1969 .. sourcecode:: pycon+sql
1971 >>> print(select(t.c.x).where(or_(t.c.x > 5, true())))
1972 {printsql}SELECT x FROM t WHERE true{stop}
1974 >>> print(select(t.c.x).where(and_(t.c.x > 5, false())))
1975 {printsql}SELECT x FROM t WHERE false{stop}
1977 .. seealso::
1979 :func:`.false`
1981 """
1983 return True_._instance()
1986def tuple_(
1987 *clauses: _ColumnExpressionOrLiteralArgument[Any],
1988 types: Optional[Sequence[_TypeEngineArgument[Any]]] = None,
1989) -> Tuple:
1990 """Return a :class:`.Tuple`.
1992 Main usage is to produce a composite IN construct using
1993 :meth:`.ColumnOperators.in_` ::
1995 from sqlalchemy import tuple_
1997 tuple_(table.c.col1, table.c.col2).in_([(1, 2), (5, 12), (10, 19)])
1999 .. warning::
2001 The composite IN construct is not supported by all backends, and is
2002 currently known to work on PostgreSQL, MySQL, and SQLite.
2003 Unsupported backends will raise a subclass of
2004 :class:`~sqlalchemy.exc.DBAPIError` when such an expression is
2005 invoked.
2007 """
2008 return Tuple(*clauses, types=types)
2011def type_coerce(
2012 expression: _ColumnExpressionOrLiteralArgument[Any],
2013 type_: _TypeEngineArgument[_T],
2014) -> TypeCoerce[_T]:
2015 r"""Associate a SQL expression with a particular type, without rendering
2016 ``CAST``.
2018 E.g.::
2020 from sqlalchemy import type_coerce
2022 stmt = select(type_coerce(log_table.date_string, StringDateTime()))
2024 The above construct will produce a :class:`.TypeCoerce` object, which
2025 does not modify the rendering in any way on the SQL side, with the
2026 possible exception of a generated label if used in a columns clause
2027 context:
2029 .. sourcecode:: sql
2031 SELECT date_string AS date_string FROM log
2033 When result rows are fetched, the ``StringDateTime`` type processor
2034 will be applied to result rows on behalf of the ``date_string`` column.
2036 .. note:: the :func:`.type_coerce` construct does not render any
2037 SQL syntax of its own, including that it does not imply
2038 parenthesization. Please use :meth:`.TypeCoerce.self_group`
2039 if explicit parenthesization is required.
2041 In order to provide a named label for the expression, use
2042 :meth:`_expression.ColumnElement.label`::
2044 stmt = select(
2045 type_coerce(log_table.date_string, StringDateTime()).label("date")
2046 )
2048 A type that features bound-value handling will also have that behavior
2049 take effect when literal values or :func:`.bindparam` constructs are
2050 passed to :func:`.type_coerce` as targets.
2051 For example, if a type implements the
2052 :meth:`.TypeEngine.bind_expression`
2053 method or :meth:`.TypeEngine.bind_processor` method or equivalent,
2054 these functions will take effect at statement compilation/execution
2055 time when a literal value is passed, as in::
2057 # bound-value handling of MyStringType will be applied to the
2058 # literal value "some string"
2059 stmt = select(type_coerce("some string", MyStringType))
2061 When using :func:`.type_coerce` with composed expressions, note that
2062 **parenthesis are not applied**. If :func:`.type_coerce` is being
2063 used in an operator context where the parenthesis normally present from
2064 CAST are necessary, use the :meth:`.TypeCoerce.self_group` method:
2066 .. sourcecode:: pycon+sql
2068 >>> some_integer = column("someint", Integer)
2069 >>> some_string = column("somestr", String)
2070 >>> expr = type_coerce(some_integer + 5, String) + some_string
2071 >>> print(expr)
2072 {printsql}someint + :someint_1 || somestr{stop}
2073 >>> expr = type_coerce(some_integer + 5, String).self_group() + some_string
2074 >>> print(expr)
2075 {printsql}(someint + :someint_1) || somestr{stop}
2077 :param expression: A SQL expression, such as a
2078 :class:`_expression.ColumnElement`
2079 expression or a Python string which will be coerced into a bound
2080 literal value.
2082 :param type\_: A :class:`.TypeEngine` class or instance indicating
2083 the type to which the expression is coerced.
2085 .. seealso::
2087 :ref:`tutorial_casts`
2089 :func:`.cast`
2091 """ # noqa
2092 return TypeCoerce(expression, type_)
2095def within_group(
2096 element: FunctionElement[_T], *order_by: _ColumnExpressionArgument[Any]
2097) -> WithinGroup[_T]:
2098 r"""Produce a :class:`.WithinGroup` object against a function.
2100 Used against so-called "ordered set aggregate" and "hypothetical
2101 set aggregate" functions, including :class:`.percentile_cont`,
2102 :class:`.rank`, :class:`.dense_rank`, etc. This feature is typically
2103 used by Oracle Database, Microsoft SQL Server.
2105 For generalized ORDER BY of aggregate functions on all included
2106 backends, including PostgreSQL, MySQL/MariaDB, SQLite as well as Oracle
2107 and SQL Server, the :func:`_sql.aggregate_order_by` provides a more
2108 general approach that compiles to "WITHIN GROUP" only on those backends
2109 which require it.
2111 :func:`_expression.within_group` is usually called using
2112 the :meth:`.FunctionElement.within_group` method, e.g.::
2114 stmt = select(
2115 func.percentile_cont(0.5).within_group(department.c.salary.desc()),
2116 )
2118 The above statement would produce SQL similar to
2119 ``SELECT percentile_cont(0.5)
2120 WITHIN GROUP (ORDER BY department.salary DESC)``.
2122 :param element: a :class:`.FunctionElement` construct, typically
2123 generated by :data:`~.expression.func`.
2124 :param \*order_by: one or more column elements that will be used
2125 as the ORDER BY clause of the WITHIN GROUP construct.
2127 .. seealso::
2129 :ref:`tutorial_functions_within_group` - in the
2130 :ref:`unified_tutorial`
2132 :func:`_sql.aggregate_order_by` - helper for PostgreSQL, MySQL,
2133 SQLite aggregate functions
2135 :data:`.expression.func`
2137 :func:`_expression.over`
2139 """
2140 return WithinGroup(element, *order_by)
2143def aggregate_order_by(
2144 element: FunctionElement[_T], *order_by: _ColumnExpressionArgument[Any]
2145) -> AggregateOrderBy[_T]:
2146 r"""Produce a :class:`.AggregateOrderBy` object against a function.
2148 Used for aggregating functions such as :class:`_functions.array_agg`,
2149 ``group_concat``, ``json_agg`` on backends that support ordering via an
2150 embedded ``ORDER BY`` parameter, e.g. PostgreSQL, MySQL/MariaDB, SQLite.
2151 When used on backends like Oracle and SQL Server, SQL compilation uses that
2152 of :class:`.WithinGroup`. On PostgreSQL, compilation is fixed at embedded
2153 ``ORDER BY``; for set aggregation functions where PostgreSQL requires the
2154 use of ``WITHIN GROUP``, :func:`_expression.within_group` should be used
2155 explicitly.
2157 :func:`_expression.aggregate_order_by` is usually called using
2158 the :meth:`.FunctionElement.aggregate_order_by` method, e.g.::
2160 stmt = select(
2161 func.array_agg(department.c.code).aggregate_order_by(
2162 department.c.code.desc()
2163 ),
2164 )
2166 which would produce an expression resembling:
2168 .. sourcecode:: sql
2170 SELECT array_agg(department.code ORDER BY department.code DESC)
2171 AS array_agg_1 FROM department
2173 The ORDER BY argument may also be multiple terms.
2175 When using the backend-agnostic :class:`_functions.aggregate_strings`
2176 string aggregation function, use the
2177 :paramref:`_functions.aggregate_strings.order_by` parameter to indicate a
2178 dialect-agnostic ORDER BY expression.
2180 .. versionadded:: 2.1 Generalized the PostgreSQL-specific
2181 :func:`_postgresql.aggregate_order_by` function to a method on
2182 :class:`.Function` that is backend agnostic.
2184 .. seealso::
2186 :class:`_functions.aggregate_strings` - backend-agnostic string
2187 concatenation function which also supports ORDER BY
2189 """ # noqa: E501
2190 return AggregateOrderBy(element, *order_by)