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 _CovariantTypeEngineArgument
61 from ._typing import _OnlyColumnArgument
62 from ._typing import _TypeEngineArgument
63 from .elements import _FrameIntTuple
64 from .elements import BinaryExpression
65 from .elements import FrameClause
66 from .selectable import FromClause
67 from .type_api import TypeEngine
68 from ..util.compat import Template
70_T = TypeVar("_T")
73def all_(expr: _ColumnExpressionArgument[_T]) -> CollectionAggregate[bool]:
74 """Produce an ALL expression.
76 For dialects such as that of PostgreSQL, this operator applies
77 to usage of the :class:`_types.ARRAY` datatype, for that of
78 MySQL, it may apply to a subquery. e.g.::
80 # renders on PostgreSQL:
81 # '5 = ALL (somearray)'
82 expr = 5 == all_(mytable.c.somearray)
84 # renders on MySQL:
85 # '5 = ALL (SELECT value FROM table)'
86 expr = 5 == all_(select(table.c.value))
88 When using a Python sequence with PostgreSQL, wrap it with
89 :func:`_sql.literal` to produce a SQL expression. Whether the sequence
90 can be adapted to an array parameter is DBAPI-specific::
92 expr = 5 == all_(literal([1, 2, 3]))
94 Comparison to NULL may work using ``None``::
96 None == all_(mytable.c.somearray)
98 The any_() / all_() operators also feature a special "operand flipping"
99 behavior such that if any_() / all_() are used on the left side of a
100 comparison using a standalone operator such as ``==``, ``!=``, etc.
101 (not including operator methods such as
102 :meth:`_sql.ColumnOperators.is_`) the rendered expression is flipped::
104 # would render '5 = ALL (column)`
105 all_(mytable.c.column) == 5
107 Or with ``None``, which note will not perform
108 the usual step of rendering "IS" as is normally the case for NULL::
110 # would render 'NULL = ALL(somearray)'
111 all_(mytable.c.somearray) == None
113 The column-level :meth:`_sql.ColumnElement.all_` method (not to be
114 confused with the deprecated :class:`_types.ARRAY` level
115 :meth:`_types.ARRAY.Comparator.all`) is shorthand for
116 ``all_(col)``::
118 5 == mytable.c.somearray.all_()
120 .. seealso::
122 :meth:`_sql.ColumnOperators.all_`
124 :func:`_expression.any_`
126 """
127 if isinstance(expr, operators.ColumnOperators):
128 return expr.all_()
129 else:
130 return CollectionAggregate._create_all(expr)
133def and_( # type: ignore[empty-body]
134 initial_clause: Union[Literal[True], _ColumnExpressionArgument[bool]],
135 *clauses: _ColumnExpressionArgument[bool],
136) -> ColumnElement[bool]:
137 r"""Produce a conjunction of expressions joined by ``AND``.
139 E.g.::
141 from sqlalchemy import and_
143 stmt = select(users_table).where(
144 and_(users_table.c.name == "wendy", users_table.c.enrolled == True)
145 )
147 The :func:`.and_` conjunction is also available using the
148 Python ``&`` operator (though note that compound expressions
149 need to be parenthesized in order to function with Python
150 operator precedence behavior)::
152 stmt = select(users_table).where(
153 (users_table.c.name == "wendy") & (users_table.c.enrolled == True)
154 )
156 The :func:`.and_` operation is also implicit in some cases;
157 the :meth:`_expression.Select.where`
158 method for example can be invoked multiple
159 times against a statement, which will have the effect of each
160 clause being combined using :func:`.and_`::
162 stmt = (
163 select(users_table)
164 .where(users_table.c.name == "wendy")
165 .where(users_table.c.enrolled == True)
166 )
168 The :func:`.and_` construct must be given at least one positional
169 argument in order to be valid; a :func:`.and_` construct with no
170 arguments is ambiguous. To produce an "empty" or dynamically
171 generated :func:`.and_` expression, from a given list of expressions,
172 a "default" element of :func:`_sql.true` (or just ``True``) should be
173 specified::
175 from sqlalchemy import true
177 criteria = and_(true(), *expressions)
179 The above expression will compile to SQL as the expression ``true``
180 or ``1 = 1``, depending on backend, if no other expressions are
181 present. If expressions are present, then the :func:`_sql.true` value is
182 ignored as it does not affect the outcome of an AND expression that
183 has other elements.
185 .. deprecated:: 1.4 The :func:`.and_` element now requires that at
186 least one argument is passed; creating the :func:`.and_` construct
187 with no arguments is deprecated, and will emit a deprecation warning
188 while continuing to produce a blank SQL string.
190 .. seealso::
192 :func:`.or_`
194 """
195 ...
198if not TYPE_CHECKING:
199 # handle deprecated case which allows zero-arguments
200 def and_(*clauses): # noqa: F811
201 r"""Produce a conjunction of expressions joined by ``AND``.
203 E.g.::
205 from sqlalchemy import and_
207 stmt = select(users_table).where(
208 and_(users_table.c.name == "wendy", users_table.c.enrolled == True)
209 )
211 The :func:`.and_` conjunction is also available using the
212 Python ``&`` operator (though note that compound expressions
213 need to be parenthesized in order to function with Python
214 operator precedence behavior)::
216 stmt = select(users_table).where(
217 (users_table.c.name == "wendy") & (users_table.c.enrolled == True)
218 )
220 The :func:`.and_` operation is also implicit in some cases;
221 the :meth:`_expression.Select.where`
222 method for example can be invoked multiple
223 times against a statement, which will have the effect of each
224 clause being combined using :func:`.and_`::
226 stmt = (
227 select(users_table)
228 .where(users_table.c.name == "wendy")
229 .where(users_table.c.enrolled == True)
230 )
232 The :func:`.and_` construct must be given at least one positional
233 argument in order to be valid; a :func:`.and_` construct with no
234 arguments is ambiguous. To produce an "empty" or dynamically
235 generated :func:`.and_` expression, from a given list of expressions,
236 a "default" element of :func:`_sql.true` (or just ``True``) should be
237 specified::
239 from sqlalchemy import true
241 criteria = and_(true(), *expressions)
243 The above expression will compile to SQL as the expression ``true``
244 or ``1 = 1``, depending on backend, if no other expressions are
245 present. If expressions are present, then the :func:`_sql.true` value
246 is ignored as it does not affect the outcome of an AND expression that
247 has other elements.
249 .. deprecated:: 1.4 The :func:`.and_` element now requires that at
250 least one argument is passed; creating the :func:`.and_` construct
251 with no arguments is deprecated, and will emit a deprecation warning
252 while continuing to produce a blank SQL string.
254 .. seealso::
256 :func:`.or_`
258 """ # noqa: E501
259 return BooleanClauseList.and_(*clauses)
262def any_(expr: _ColumnExpressionArgument[_T]) -> CollectionAggregate[bool]:
263 """Produce an ANY expression.
265 For dialects such as that of PostgreSQL, this operator applies
266 to usage of the :class:`_types.ARRAY` datatype, for that of
267 MySQL, it may apply to a subquery. e.g.::
269 # renders on PostgreSQL:
270 # '5 = ANY (somearray)'
271 expr = 5 == any_(mytable.c.somearray)
273 # renders on MySQL:
274 # '5 = ANY (SELECT value FROM table)'
275 expr = 5 == any_(select(table.c.value))
277 When using a Python sequence with PostgreSQL, wrap it with
278 :func:`_sql.literal` to produce a SQL expression. Whether the sequence
279 can be adapted to an array parameter is DBAPI-specific::
281 expr = 5 == any_(literal([1, 2, 3]))
283 Comparison to NULL may work using ``None`` or :func:`_sql.null`::
285 None == any_(mytable.c.somearray)
287 The any_() / all_() operators also feature a special "operand flipping"
288 behavior such that if any_() / all_() are used on the left side of a
289 comparison using a standalone operator such as ``==``, ``!=``, etc.
290 (not including operator methods such as
291 :meth:`_sql.ColumnOperators.is_`) the rendered expression is flipped::
293 # would render '5 = ANY (column)`
294 any_(mytable.c.column) == 5
296 Or with ``None``, which note will not perform
297 the usual step of rendering "IS" as is normally the case for NULL::
299 # would render 'NULL = ANY(somearray)'
300 any_(mytable.c.somearray) == None
302 The column-level :meth:`_sql.ColumnElement.any_` method (not to be
303 confused with the deprecated :class:`_types.ARRAY` level
304 :meth:`_types.ARRAY.Comparator.any`) is shorthand for
305 ``any_(col)``::
307 5 = mytable.c.somearray.any_()
309 .. seealso::
311 :meth:`_sql.ColumnOperators.any_`
313 :func:`_expression.all_`
315 """
316 if isinstance(expr, operators.ColumnOperators):
317 return expr.any_()
318 else:
319 return CollectionAggregate._create_any(expr)
322@overload
323def asc(
324 column: Union[str, "ColumnElement[_T]"],
325) -> UnaryExpression[_T]: ...
328@overload
329def asc(
330 column: _ColumnExpressionOrStrLabelArgument[_T],
331) -> Union[OrderByList, UnaryExpression[_T]]: ...
334def asc(
335 column: _ColumnExpressionOrStrLabelArgument[_T],
336) -> Union[OrderByList, UnaryExpression[_T]]:
337 """Produce an ascending ``ORDER BY`` clause element.
339 e.g.::
341 from sqlalchemy import asc
343 stmt = select(users_table).order_by(asc(users_table.c.name))
345 will produce SQL as:
347 .. sourcecode:: sql
349 SELECT id, name FROM user ORDER BY name ASC
351 The :func:`.asc` function is a standalone version of the
352 :meth:`_expression.ColumnElement.asc`
353 method available on all SQL expressions,
354 e.g.::
357 stmt = select(users_table).order_by(users_table.c.name.asc())
359 :param column: A :class:`_expression.ColumnElement` (e.g.
360 scalar SQL expression)
361 with which to apply the :func:`.asc` operation.
363 .. seealso::
365 :func:`.desc`
367 :func:`.nulls_first`
369 :func:`.nulls_last`
371 :meth:`_expression.Select.order_by`
373 """
375 if isinstance(column, operators.OrderingOperators):
376 return column.asc() # type: ignore[unused-ignore]
377 else:
378 return UnaryExpression._create_asc(column)
381def collate(
382 expression: _ColumnExpressionArgument[str],
383 collation: str,
384 collation_schema: Optional[str] = None,
385) -> BinaryExpression[str]:
386 """Return the clause ``expression COLLATE collation``.
388 e.g.::
390 collate(mycolumn, "utf8_bin")
392 produces:
394 .. sourcecode:: sql
396 mycolumn COLLATE utf8_bin
398 The collation expression is also quoted if it is a case sensitive
399 identifier, e.g. contains uppercase characters.
401 :param expression: the column expression to apply a collation to.
403 :param collation: the name of the collation.
405 :param collation_schema: optional, the name of the schema in which the
406 collation is defined, for use with database backends that support
407 schema-qualified collations, currently PostgreSQL.
409 .. versionadded:: 2.1
411 """
412 if isinstance(expression, operators.ColumnOperators):
413 return expression.collate( # type: ignore[return-value]
414 collation, collation_schema=collation_schema
415 )
416 else:
417 return CollationClause._create_collation_expression(
418 expression, collation, collation_schema
419 )
422def between(
423 expr: _ColumnExpressionOrLiteralArgument[_T],
424 lower_bound: Any,
425 upper_bound: Any,
426 symmetric: bool = False,
427) -> BinaryExpression[bool]:
428 """Produce a ``BETWEEN`` predicate clause.
430 E.g.::
432 from sqlalchemy import between
434 stmt = select(users_table).where(between(users_table.c.id, 5, 7))
436 Would produce SQL resembling:
438 .. sourcecode:: sql
440 SELECT id, name FROM user WHERE id BETWEEN :id_1 AND :id_2
442 The :func:`.between` function is a standalone version of the
443 :meth:`_expression.ColumnElement.between` method available on all
444 SQL expressions, as in::
446 stmt = select(users_table).where(users_table.c.id.between(5, 7))
448 All arguments passed to :func:`.between`, including the left side
449 column expression, are coerced from Python scalar values if a
450 the value is not a :class:`_expression.ColumnElement` subclass.
451 For example,
452 three fixed values can be compared as in::
454 print(between(5, 3, 7))
456 Which would produce::
458 :param_1 BETWEEN :param_2 AND :param_3
460 :param expr: a column expression, typically a
461 :class:`_expression.ColumnElement`
462 instance or alternatively a Python scalar expression to be coerced
463 into a column expression, serving as the left side of the ``BETWEEN``
464 expression.
466 :param lower_bound: a column or Python scalar expression serving as the
467 lower bound of the right side of the ``BETWEEN`` expression.
469 :param upper_bound: a column or Python scalar expression serving as the
470 upper bound of the right side of the ``BETWEEN`` expression.
472 :param symmetric: if True, will render " BETWEEN SYMMETRIC ". Note
473 that not all databases support this syntax.
475 .. seealso::
477 :meth:`_expression.ColumnElement.between`
479 """
480 col_expr = coercions.expect(roles.ExpressionElementRole, expr)
481 return col_expr.between(lower_bound, upper_bound, symmetric=symmetric)
484def outparam(
485 key: str, type_: Optional[TypeEngine[_T]] = None
486) -> BindParameter[_T]:
487 """Create an 'OUT' parameter for usage in functions (stored procedures),
488 for databases which support them.
490 The ``outparam`` can be used like a regular function parameter.
491 The "output" value will be available from the
492 :class:`~sqlalchemy.engine.CursorResult` object via its ``out_parameters``
493 attribute, which returns a dictionary containing the values.
495 """
496 return BindParameter(key, None, type_=type_, unique=False, isoutparam=True)
499@overload
500def not_(clause: BinaryExpression[_T]) -> BinaryExpression[_T]: ...
503@overload
504def not_(clause: _ColumnExpressionArgument[_T]) -> ColumnElement[_T]: ...
507def not_(clause: _ColumnExpressionArgument[_T]) -> ColumnElement[_T]:
508 """Return a negation of the given clause, i.e. ``NOT(clause)``.
510 The ``~`` operator is also overloaded on all
511 :class:`_expression.ColumnElement` subclasses to produce the
512 same result.
514 """
516 return coercions.expect(roles.ExpressionElementRole, clause).__invert__()
519def from_dml_column(column: _OnlyColumnArgument[_T]) -> DMLTargetCopy[_T]:
520 r"""A placeholder that may be used in compiled INSERT or UPDATE expressions
521 to refer to the SQL expression or value being applied to another column.
523 Given a table such as::
525 t = Table(
526 "t",
527 MetaData(),
528 Column("x", Integer),
529 Column("y", Integer),
530 )
532 The :func:`_sql.from_dml_column` construct allows automatic copying
533 of an expression assigned to a different column to be reused::
535 >>> stmt = t.insert().values(x=func.foobar(3), y=from_dml_column(t.c.x) + 5)
536 >>> print(stmt)
537 INSERT INTO t (x, y) VALUES (foobar(:foobar_1), (foobar(:foobar_1) + :param_1))
539 The :func:`_sql.from_dml_column` construct is intended to be useful primarily
540 with event-based hooks such as those used by ORM hybrids.
542 .. seealso::
544 :ref:`hybrid_bulk_update`
546 .. versionadded:: 2.1
549 """ # noqa: E501
551 return DMLTargetCopy(column)
554def bindparam(
555 key: Optional[str],
556 value: Any = _NoArg.NO_ARG,
557 type_: Optional[_CovariantTypeEngineArgument[_T]] = None,
558 unique: bool = False,
559 required: Union[bool, Literal[_NoArg.NO_ARG]] = _NoArg.NO_ARG,
560 quote: Optional[bool] = None,
561 callable_: Optional[Callable[[], Any]] = None,
562 expanding: bool = False,
563 isoutparam: bool = False,
564 literal_execute: bool = False,
565) -> BindParameter[_T]:
566 r"""Produce a "bound expression".
568 The return value is an instance of :class:`.BindParameter`; this
569 is a :class:`_expression.ColumnElement`
570 subclass which represents a so-called
571 "placeholder" value in a SQL expression, the value of which is
572 supplied at the point at which the statement in executed against a
573 database connection.
575 In SQLAlchemy, the :func:`.bindparam` construct has
576 the ability to carry along the actual value that will be ultimately
577 used at expression time. In this way, it serves not just as
578 a "placeholder" for eventual population, but also as a means of
579 representing so-called "unsafe" values which should not be rendered
580 directly in a SQL statement, but rather should be passed along
581 to the :term:`DBAPI` as values which need to be correctly escaped
582 and potentially handled for type-safety.
584 When using :func:`.bindparam` explicitly, the use case is typically
585 one of traditional deferment of parameters; the :func:`.bindparam`
586 construct accepts a name which can then be referred to at execution
587 time::
589 from sqlalchemy import bindparam
591 stmt = select(users_table).where(
592 users_table.c.name == bindparam("username")
593 )
595 The above statement, when rendered, will produce SQL similar to:
597 .. sourcecode:: sql
599 SELECT id, name FROM user WHERE name = :username
601 In order to populate the value of ``:username`` above, the value
602 would typically be applied at execution time to a method
603 like :meth:`_engine.Connection.execute`::
605 result = connection.execute(stmt, {"username": "wendy"})
607 Explicit use of :func:`.bindparam` is also common when producing
608 UPDATE or DELETE statements that are to be invoked multiple times,
609 where the WHERE criterion of the statement is to change on each
610 invocation, such as::
612 stmt = (
613 users_table.update()
614 .where(user_table.c.name == bindparam("username"))
615 .values(fullname=bindparam("fullname"))
616 )
618 connection.execute(
619 stmt,
620 [
621 {"username": "wendy", "fullname": "Wendy Smith"},
622 {"username": "jack", "fullname": "Jack Jones"},
623 ],
624 )
626 SQLAlchemy's Core expression system makes wide use of
627 :func:`.bindparam` in an implicit sense. It is typical that Python
628 literal values passed to virtually all SQL expression functions are
629 coerced into fixed :func:`.bindparam` constructs. For example, given
630 a comparison operation such as::
632 expr = users_table.c.name == "Wendy"
634 The above expression will produce a :class:`.BinaryExpression`
635 construct, where the left side is the :class:`_schema.Column` object
636 representing the ``name`` column, and the right side is a
637 :class:`.BindParameter` representing the literal value::
639 print(repr(expr.right))
640 BindParameter("%(4327771088 name)s", "Wendy", type_=String())
642 The expression above will render SQL such as:
644 .. sourcecode:: sql
646 user.name = :name_1
648 Where the ``:name_1`` parameter name is an anonymous name. The
649 actual string ``Wendy`` is not in the rendered string, but is carried
650 along where it is later used within statement execution. If we
651 invoke a statement like the following::
653 stmt = select(users_table).where(users_table.c.name == "Wendy")
654 result = connection.execute(stmt)
656 We would see SQL logging output as:
658 .. sourcecode:: sql
660 SELECT "user".id, "user".name
661 FROM "user"
662 WHERE "user".name = %(name_1)s
663 {'name_1': 'Wendy'}
665 Above, we see that ``Wendy`` is passed as a parameter to the database,
666 while the placeholder ``:name_1`` is rendered in the appropriate form
667 for the target database, in this case the PostgreSQL database.
669 Similarly, :func:`.bindparam` is invoked automatically when working
670 with :term:`CRUD` statements as far as the "VALUES" portion is
671 concerned. The :func:`_expression.insert` construct produces an
672 ``INSERT`` expression which will, at statement execution time, generate
673 bound placeholders based on the arguments passed, as in::
675 stmt = users_table.insert()
676 result = connection.execute(stmt, {"name": "Wendy"})
678 The above will produce SQL output as:
680 .. sourcecode:: sql
682 INSERT INTO "user" (name) VALUES (%(name)s)
683 {'name': 'Wendy'}
685 The :class:`_expression.Insert` construct, at
686 compilation/execution time, rendered a single :func:`.bindparam`
687 mirroring the column name ``name`` as a result of the single ``name``
688 parameter we passed to the :meth:`_engine.Connection.execute` method.
690 :param key:
691 the key (e.g. the name) for this bind param.
692 Will be used in the generated
693 SQL statement for dialects that use named parameters. This
694 value may be modified when part of a compilation operation,
695 if other :class:`BindParameter` objects exist with the same
696 key, or if its length is too long and truncation is
697 required.
699 If omitted, an "anonymous" name is generated for the bound parameter;
700 when given a value to bind, the end result is equivalent to calling upon
701 the :func:`.literal` function with a value to bind, particularly
702 if the :paramref:`.bindparam.unique` parameter is also provided.
704 :param value:
705 Initial value for this bind param. Will be used at statement
706 execution time as the value for this parameter passed to the
707 DBAPI, if no other value is indicated to the statement execution
708 method for this particular parameter name. Defaults to ``None``.
710 :param callable\_:
711 A callable function that takes the place of "value". The function
712 will be called at statement execution time to determine the
713 ultimate value. Used for scenarios where the actual bind
714 value cannot be determined at the point at which the clause
715 construct is created, but embedded bind values are still desirable.
717 :param type\_:
718 A :class:`.TypeEngine` class or instance representing an optional
719 datatype for this :func:`.bindparam`. If not passed, a type
720 may be determined automatically for the bind, based on the given
721 value; for example, trivial Python types such as ``str``,
722 ``int``, ``bool``
723 may result in the :class:`.String`, :class:`.Integer` or
724 :class:`.Boolean` types being automatically selected.
726 The type of a :func:`.bindparam` is significant especially in that
727 the type will apply pre-processing to the value before it is
728 passed to the database. For example, a :func:`.bindparam` which
729 refers to a datetime value, and is specified as holding the
730 :class:`.DateTime` type, may apply conversion needed to the
731 value (such as stringification on SQLite) before passing the value
732 to the database.
734 :param unique:
735 if True, the key name of this :class:`.BindParameter` will be
736 modified if another :class:`.BindParameter` of the same name
737 already has been located within the containing
738 expression. This flag is used generally by the internals
739 when producing so-called "anonymous" bound expressions, it
740 isn't generally applicable to explicitly-named :func:`.bindparam`
741 constructs.
743 :param required:
744 If ``True``, a value is required at execution time. If not passed,
745 it defaults to ``True`` if neither :paramref:`.bindparam.value`
746 or :paramref:`.bindparam.callable` were passed. If either of these
747 parameters are present, then :paramref:`.bindparam.required`
748 defaults to ``False``.
750 :param quote:
751 True if this parameter name requires quoting and is not
752 currently known as a SQLAlchemy reserved word; this currently
753 only applies to the Oracle Database backends, where bound names must
754 sometimes be quoted.
756 :param isoutparam:
757 if True, the parameter should be treated like a stored procedure
758 "OUT" parameter. This applies to backends such as Oracle Database which
759 support OUT parameters.
761 :param expanding:
762 if True, this parameter will be treated as an "expanding" parameter
763 at execution time; the parameter value is expected to be a sequence,
764 rather than a scalar value, and the string SQL statement will
765 be transformed on a per-execution basis to accommodate the sequence
766 with a variable number of parameter slots passed to the DBAPI.
767 This is to allow statement caching to be used in conjunction with
768 an IN clause.
770 .. seealso::
772 :meth:`.ColumnOperators.in_`
774 :ref:`baked_in` - with baked queries
776 .. note:: The "expanding" feature does not support "executemany"-
777 style parameter sets.
779 :param literal_execute:
780 if True, the bound parameter will be rendered in the compile phase
781 with a special "POSTCOMPILE" token, and the SQLAlchemy compiler will
782 render the final value of the parameter into the SQL statement at
783 statement execution time, omitting the value from the parameter
784 dictionary / list passed to DBAPI ``cursor.execute()``. This
785 produces a similar effect as that of using the ``literal_binds``,
786 compilation flag, however takes place as the statement is sent to
787 the DBAPI ``cursor.execute()`` method, rather than when the statement
788 is compiled. The primary use of this
789 capability is for rendering LIMIT / OFFSET clauses for database
790 drivers that can't accommodate for bound parameters in these
791 contexts, while allowing SQL constructs to be cacheable at the
792 compilation level.
794 .. versionadded:: 1.4 Added "post compile" bound parameters
796 .. seealso::
798 :ref:`change_4808`.
800 .. seealso::
802 :ref:`tutorial_sending_parameters` - in the
803 :ref:`unified_tutorial`
806 """
807 return BindParameter(
808 key,
809 value,
810 type_,
811 unique,
812 required,
813 quote,
814 callable_,
815 expanding,
816 isoutparam,
817 literal_execute,
818 )
821def case(
822 *whens: Union[
823 typing_Tuple[_ColumnExpressionArgument[bool], Any], Mapping[Any, Any]
824 ],
825 value: Optional[Any] = None,
826 else_: Optional[Any] = None,
827) -> Case[Any]:
828 r"""Produce a ``CASE`` expression.
830 The ``CASE`` construct in SQL is a conditional object that
831 acts somewhat analogously to an "if/then" construct in other
832 languages. It returns an instance of :class:`.Case`.
834 :func:`.case` in its usual form is passed a series of "when"
835 constructs, that is, a list of conditions and results as tuples::
837 from sqlalchemy import case
839 stmt = select(users_table).where(
840 case(
841 (users_table.c.name == "wendy", "W"),
842 (users_table.c.name == "jack", "J"),
843 else_="E",
844 )
845 )
847 The above statement will produce SQL resembling:
849 .. sourcecode:: sql
851 SELECT id, name FROM user
852 WHERE CASE
853 WHEN (name = :name_1) THEN :param_1
854 WHEN (name = :name_2) THEN :param_2
855 ELSE :param_3
856 END
858 When simple equality expressions of several values against a single
859 parent column are needed, :func:`.case` also has a "shorthand" format
860 used via the
861 :paramref:`.case.value` parameter, which is passed a column
862 expression to be compared. In this form, the :paramref:`.case.whens`
863 parameter is passed as a dictionary containing expressions to be
864 compared against keyed to result expressions. The statement below is
865 equivalent to the preceding statement::
867 stmt = select(users_table).where(
868 case({"wendy": "W", "jack": "J"}, value=users_table.c.name, else_="E")
869 )
871 The values which are accepted as result values in
872 :paramref:`.case.whens` as well as with :paramref:`.case.else_` are
873 coerced from Python literals into :func:`.bindparam` constructs.
874 SQL expressions, e.g. :class:`_expression.ColumnElement` constructs,
875 are accepted
876 as well. To coerce a literal string expression into a constant
877 expression rendered inline, use the :func:`_expression.literal_column`
878 construct,
879 as in::
881 from sqlalchemy import case, literal_column
883 case(
884 (orderline.c.qty > 100, literal_column("'greaterthan100'")),
885 (orderline.c.qty > 10, literal_column("'greaterthan10'")),
886 else_=literal_column("'lessthan10'"),
887 )
889 The above will render the given constants without using bound
890 parameters for the result values (but still for the comparison
891 values), as in:
893 .. sourcecode:: sql
895 CASE
896 WHEN (orderline.qty > :qty_1) THEN 'greaterthan100'
897 WHEN (orderline.qty > :qty_2) THEN 'greaterthan10'
898 ELSE 'lessthan10'
899 END
901 :param \*whens: The criteria to be compared against,
902 :paramref:`.case.whens` accepts two different forms, based on
903 whether or not :paramref:`.case.value` is used.
905 .. versionchanged:: 1.4 the :func:`_sql.case`
906 function now accepts the series of WHEN conditions positionally
908 In the first form, it accepts multiple 2-tuples passed as positional
909 arguments; each 2-tuple consists of ``(<sql expression>, <value>)``,
910 where the SQL expression is a boolean expression and "value" is a
911 resulting value, e.g.::
913 case(
914 (users_table.c.name == "wendy", "W"),
915 (users_table.c.name == "jack", "J"),
916 )
918 In the second form, it accepts a Python dictionary of comparison
919 values mapped to a resulting value; this form requires
920 :paramref:`.case.value` to be present, and values will be compared
921 using the ``==`` operator, e.g.::
923 case({"wendy": "W", "jack": "J"}, value=users_table.c.name)
925 :param value: An optional SQL expression which will be used as a
926 fixed "comparison point" for candidate values within a dictionary
927 passed to :paramref:`.case.whens`.
929 :param else\_: An optional SQL expression which will be the evaluated
930 result of the ``CASE`` construct if all expressions within
931 :paramref:`.case.whens` evaluate to false. When omitted, most
932 databases will produce a result of NULL if none of the "when"
933 expressions evaluate to true.
936 """ # noqa: E501
937 return Case(*whens, value=value, else_=else_)
940def cast(
941 expression: _ColumnExpressionOrLiteralArgument[Any],
942 type_: _CovariantTypeEngineArgument[_T],
943) -> Cast[_T]:
944 r"""Produce a ``CAST`` expression.
946 :func:`.cast` returns an instance of :class:`.Cast`.
948 E.g.::
950 from sqlalchemy import cast, Numeric
952 stmt = select(cast(product_table.c.unit_price, Numeric(10, 4)))
954 The above statement will produce SQL resembling:
956 .. sourcecode:: sql
958 SELECT CAST(unit_price AS NUMERIC(10, 4)) FROM product
960 The :func:`.cast` function performs two distinct functions when
961 used. The first is that it renders the ``CAST`` expression within
962 the resulting SQL string. The second is that it associates the given
963 type (e.g. :class:`.TypeEngine` class or instance) with the column
964 expression on the Python side, which means the expression will take
965 on the expression operator behavior associated with that type,
966 as well as the bound-value handling and result-row-handling behavior
967 of the type.
969 An alternative to :func:`.cast` is the :func:`.type_coerce` function.
970 This function performs the second task of associating an expression
971 with a specific type, but does not render the ``CAST`` expression
972 in SQL.
974 :param expression: A SQL expression, such as a
975 :class:`_expression.ColumnElement`
976 expression or a Python string which will be coerced into a bound
977 literal value.
979 :param type\_: A :class:`.TypeEngine` class or instance indicating
980 the type to which the ``CAST`` should apply.
982 .. seealso::
984 :ref:`tutorial_casts`
986 :func:`.try_cast` - an alternative to CAST that results in
987 NULLs when the cast fails, instead of raising an error.
988 Only supported by some dialects.
990 :func:`.type_coerce` - an alternative to CAST that coerces the type
991 on the Python side only, which is often sufficient to generate the
992 correct SQL and data coercion.
995 """
996 return Cast(expression, type_)
999def try_cast(
1000 expression: _ColumnExpressionOrLiteralArgument[Any],
1001 type_: _CovariantTypeEngineArgument[_T],
1002) -> TryCast[_T]:
1003 """Produce a ``TRY_CAST`` expression for backends which support it;
1004 this is a ``CAST`` which returns NULL for un-castable conversions.
1006 In SQLAlchemy, this construct is supported **only** by the SQL Server
1007 dialect, and will raise a :class:`.CompileError` if used on other
1008 included backends. However, third party backends may also support
1009 this construct.
1011 .. tip:: As :func:`_sql.try_cast` originates from the SQL Server dialect,
1012 it's importable both from ``sqlalchemy.`` as well as from
1013 ``sqlalchemy.dialects.mssql``.
1015 :func:`_sql.try_cast` returns an instance of :class:`.TryCast` and
1016 generally behaves similarly to the :class:`.Cast` construct;
1017 at the SQL level, the difference between ``CAST`` and ``TRY_CAST``
1018 is that ``TRY_CAST`` returns NULL for an un-castable expression,
1019 such as attempting to cast a string ``"hi"`` to an integer value.
1021 E.g.::
1023 from sqlalchemy import select, try_cast, Numeric
1025 stmt = select(try_cast(product_table.c.unit_price, Numeric(10, 4)))
1027 The above would render on Microsoft SQL Server as:
1029 .. sourcecode:: sql
1031 SELECT TRY_CAST (product_table.unit_price AS NUMERIC(10, 4))
1032 FROM product_table
1034 .. versionadded:: 2.0.14 :func:`.try_cast` has been
1035 generalized from the SQL Server dialect into a general use
1036 construct that may be supported by additional dialects.
1038 """
1039 return TryCast(expression, type_)
1042def column(
1043 text: str,
1044 type_: Optional[_CovariantTypeEngineArgument[_T]] = None,
1045 is_literal: bool = False,
1046 _selectable: Optional[FromClause] = None,
1047) -> ColumnClause[_T]:
1048 """Produce a :class:`.ColumnClause` object.
1050 The :class:`.ColumnClause` is a lightweight analogue to the
1051 :class:`_schema.Column` class. The :func:`_expression.column`
1052 function can
1053 be invoked with just a name alone, as in::
1055 from sqlalchemy import column
1057 id, name = column("id"), column("name")
1058 stmt = select(id, name).select_from("user")
1060 The above statement would produce SQL like:
1062 .. sourcecode:: sql
1064 SELECT id, name FROM user
1066 Once constructed, :func:`_expression.column`
1067 may be used like any other SQL
1068 expression element such as within :func:`_expression.select`
1069 constructs::
1071 from sqlalchemy.sql import column
1073 id, name = column("id"), column("name")
1074 stmt = select(id, name).select_from("user")
1076 The text handled by :func:`_expression.column`
1077 is assumed to be handled
1078 like the name of a database column; if the string contains mixed case,
1079 special characters, or matches a known reserved word on the target
1080 backend, the column expression will render using the quoting
1081 behavior determined by the backend. To produce a textual SQL
1082 expression that is rendered exactly without any quoting,
1083 use :func:`_expression.literal_column` instead,
1084 or pass ``True`` as the
1085 value of :paramref:`_expression.column.is_literal`. Additionally,
1086 full SQL
1087 statements are best handled using the :func:`_expression.text`
1088 construct.
1090 :func:`_expression.column` can be used in a table-like
1091 fashion by combining it with the :func:`.table` function
1092 (which is the lightweight analogue to :class:`_schema.Table`
1093 ) to produce
1094 a working table construct with minimal boilerplate::
1096 from sqlalchemy import table, column, select
1098 user = table(
1099 "user",
1100 column("id"),
1101 column("name"),
1102 column("description"),
1103 )
1105 stmt = select(user.c.description).where(user.c.name == "wendy")
1107 A :func:`_expression.column` / :func:`.table`
1108 construct like that illustrated
1109 above can be created in an
1110 ad-hoc fashion and is not associated with any
1111 :class:`_schema.MetaData`, DDL, or events, unlike its
1112 :class:`_schema.Table` counterpart.
1114 :param text: the text of the element.
1116 :param type: :class:`_types.TypeEngine` object which can associate
1117 this :class:`.ColumnClause` with a type.
1119 :param is_literal: if True, the :class:`.ColumnClause` is assumed to
1120 be an exact expression that will be delivered to the output with no
1121 quoting rules applied regardless of case sensitive settings. the
1122 :func:`_expression.literal_column()` function essentially invokes
1123 :func:`_expression.column` while passing ``is_literal=True``.
1125 .. seealso::
1127 :class:`_schema.Column`
1129 :func:`_expression.literal_column`
1131 :func:`.table`
1133 :func:`_expression.text`
1135 :ref:`tutorial_select_arbitrary_text`
1137 """
1138 return ColumnClause(text, type_, is_literal, _selectable)
1141@overload
1142def desc(
1143 column: Union[str, "ColumnElement[_T]"],
1144) -> UnaryExpression[_T]: ...
1147@overload
1148def desc(
1149 column: _ColumnExpressionOrStrLabelArgument[_T],
1150) -> Union[OrderByList, UnaryExpression[_T]]: ...
1153def desc(
1154 column: _ColumnExpressionOrStrLabelArgument[_T],
1155) -> Union[OrderByList, UnaryExpression[_T]]:
1156 """Produce a descending ``ORDER BY`` clause element.
1158 e.g.::
1160 from sqlalchemy import desc
1162 stmt = select(users_table).order_by(desc(users_table.c.name))
1164 will produce SQL as:
1166 .. sourcecode:: sql
1168 SELECT id, name FROM user ORDER BY name DESC
1170 The :func:`.desc` function is a standalone version of the
1171 :meth:`_expression.ColumnElement.desc`
1172 method available on all SQL expressions,
1173 e.g.::
1176 stmt = select(users_table).order_by(users_table.c.name.desc())
1178 :param column: A :class:`_expression.ColumnElement` (e.g.
1179 scalar SQL expression)
1180 with which to apply the :func:`.desc` operation.
1182 .. seealso::
1184 :func:`.asc`
1186 :func:`.nulls_first`
1188 :func:`.nulls_last`
1190 :meth:`_expression.Select.order_by`
1192 """
1193 if isinstance(column, operators.OrderingOperators):
1194 return column.desc() # type: ignore[unused-ignore]
1195 else:
1196 return UnaryExpression._create_desc(column)
1199def distinct(expr: _ColumnExpressionArgument[_T]) -> UnaryExpression[_T]:
1200 """Produce an column-expression-level unary ``DISTINCT`` clause.
1202 This applies the ``DISTINCT`` keyword to an **individual column
1203 expression** (e.g. not the whole statement), and renders **specifically
1204 in that column position**; this is used for containment within
1205 an aggregate function, as in::
1207 from sqlalchemy import distinct, func
1209 stmt = select(users_table.c.id, func.count(distinct(users_table.c.name)))
1211 The above would produce an statement resembling:
1213 .. sourcecode:: sql
1215 SELECT user.id, count(DISTINCT user.name) FROM user
1217 .. tip:: The :func:`_sql.distinct` function does **not** apply DISTINCT
1218 to the full SELECT statement, instead applying a DISTINCT modifier
1219 to **individual column expressions**. For general ``SELECT DISTINCT``
1220 support, use the
1221 :meth:`_sql.Select.distinct` method on :class:`_sql.Select`.
1223 The :func:`.distinct` function is also available as a column-level
1224 method, e.g. :meth:`_expression.ColumnElement.distinct`, as in::
1226 stmt = select(func.count(users_table.c.name.distinct()))
1228 The :func:`.distinct` operator is different from the
1229 :meth:`_expression.Select.distinct` method of
1230 :class:`_expression.Select`,
1231 which produces a ``SELECT`` statement
1232 with ``DISTINCT`` applied to the result set as a whole,
1233 e.g. a ``SELECT DISTINCT`` expression. See that method for further
1234 information.
1236 .. seealso::
1238 :meth:`_expression.ColumnElement.distinct`
1240 :meth:`_expression.Select.distinct`
1242 :data:`.func`
1244 """ # noqa: E501
1245 if isinstance(expr, operators.ColumnOperators):
1246 return expr.distinct()
1247 else:
1248 return UnaryExpression._create_distinct(expr)
1251def bitwise_not(expr: _ColumnExpressionArgument[_T]) -> UnaryExpression[_T]:
1252 """Produce a unary bitwise NOT clause, typically via the ``~`` operator.
1254 Not to be confused with boolean negation :func:`_sql.not_`.
1256 .. versionadded:: 2.0.2
1258 .. seealso::
1260 :ref:`operators_bitwise`
1263 """
1264 if isinstance(expr, operators.ColumnOperators):
1265 return expr.bitwise_not()
1266 else:
1267 return UnaryExpression._create_bitwise_not(expr)
1270def extract(field: str, expr: _ColumnExpressionArgument[Any]) -> Extract:
1271 """Return a :class:`.Extract` construct.
1273 This is typically available as :func:`.extract`
1274 as well as ``func.extract`` from the
1275 :data:`.func` namespace.
1277 :param field: The field to extract.
1279 .. warning:: This field is used as a literal SQL string.
1280 **DO NOT PASS UNTRUSTED INPUT TO THIS STRING**.
1282 :param expr: A column or Python scalar expression serving as the
1283 right side of the ``EXTRACT`` expression.
1285 E.g.::
1287 from sqlalchemy import extract
1288 from sqlalchemy import table, column
1290 logged_table = table(
1291 "user",
1292 column("id"),
1293 column("date_created"),
1294 )
1296 stmt = select(logged_table.c.id).where(
1297 extract("YEAR", logged_table.c.date_created) == 2021
1298 )
1300 In the above example, the statement is used to select ids from the
1301 database where the ``YEAR`` component matches a specific value.
1303 Similarly, one can also select an extracted component::
1305 stmt = select(extract("YEAR", logged_table.c.date_created)).where(
1306 logged_table.c.id == 1
1307 )
1309 The implementation of ``EXTRACT`` may vary across database backends.
1310 Users are reminded to consult their database documentation.
1311 """
1312 return Extract(field, expr)
1315def false() -> False_:
1316 """Return a :class:`.False_` construct.
1318 E.g.:
1320 .. sourcecode:: pycon+sql
1322 >>> from sqlalchemy import false
1323 >>> print(select(t.c.x).where(false()))
1324 {printsql}SELECT x FROM t WHERE false
1326 A backend which does not support true/false constants will render as
1327 an expression against 1 or 0:
1329 .. sourcecode:: pycon+sql
1331 >>> print(select(t.c.x).where(false()))
1332 {printsql}SELECT x FROM t WHERE 0 = 1
1334 The :func:`.true` and :func:`.false` constants also feature
1335 "short circuit" operation within an :func:`.and_` or :func:`.or_`
1336 conjunction:
1338 .. sourcecode:: pycon+sql
1340 >>> print(select(t.c.x).where(or_(t.c.x > 5, true())))
1341 {printsql}SELECT x FROM t WHERE true{stop}
1343 >>> print(select(t.c.x).where(and_(t.c.x > 5, false())))
1344 {printsql}SELECT x FROM t WHERE false{stop}
1346 .. seealso::
1348 :func:`.true`
1350 """
1352 return False_._instance()
1355def funcfilter(
1356 func: FunctionElement[_T], *criterion: _ColumnExpressionArgument[bool]
1357) -> FunctionFilter[_T]:
1358 """Produce a :class:`.FunctionFilter` object against a function.
1360 Used against aggregate and window functions,
1361 for database backends that support the "FILTER" clause.
1363 E.g.::
1365 from sqlalchemy import funcfilter
1367 funcfilter(func.count(1), MyClass.name == "some name")
1369 Would produce "COUNT(1) FILTER (WHERE myclass.name = 'some name')".
1371 This function is also available from the :data:`~.expression.func`
1372 construct itself via the :meth:`.FunctionElement.filter` method.
1374 .. seealso::
1376 :ref:`tutorial_functions_within_group` - in the
1377 :ref:`unified_tutorial`
1379 :meth:`.FunctionElement.filter`
1381 """
1382 return FunctionFilter(func, *criterion)
1385def label(
1386 name: str,
1387 element: _ColumnExpressionArgument[_T],
1388 type_: Optional[_CovariantTypeEngineArgument[_T]] = None,
1389) -> Label[_T]:
1390 """Return a :class:`Label` object for the
1391 given :class:`_expression.ColumnElement`.
1393 A label changes the name of an element in the columns clause of a
1394 ``SELECT`` statement, typically via the ``AS`` SQL keyword.
1396 This functionality is more conveniently available via the
1397 :meth:`_expression.ColumnElement.label` method on
1398 :class:`_expression.ColumnElement`.
1400 :param name: label name
1402 :param obj: a :class:`_expression.ColumnElement`.
1404 """
1405 return Label(name, element, type_)
1408def null() -> Null:
1409 """Return a constant :class:`.Null` construct."""
1411 return Null._instance()
1414@overload
1415def nulls_first(
1416 column: "ColumnElement[_T]",
1417) -> UnaryExpression[_T]: ...
1420@overload
1421def nulls_first(
1422 column: _ColumnExpressionArgument[_T],
1423) -> Union[OrderByList, UnaryExpression[_T]]: ...
1426def nulls_first(
1427 column: _ColumnExpressionArgument[_T],
1428) -> Union[OrderByList, UnaryExpression[_T]]:
1429 """Produce the ``NULLS FIRST`` modifier for an ``ORDER BY`` expression.
1431 :func:`.nulls_first` is intended to modify the expression produced
1432 by :func:`.asc` or :func:`.desc`, and indicates how NULL values
1433 should be handled when they are encountered during ordering::
1436 from sqlalchemy import desc, nulls_first
1438 stmt = select(users_table).order_by(nulls_first(desc(users_table.c.name)))
1440 The SQL expression from the above would resemble:
1442 .. sourcecode:: sql
1444 SELECT id, name FROM user ORDER BY name DESC NULLS FIRST
1446 Like :func:`.asc` and :func:`.desc`, :func:`.nulls_first` is typically
1447 invoked from the column expression itself using
1448 :meth:`_expression.ColumnElement.nulls_first`,
1449 rather than as its standalone
1450 function version, as in::
1452 stmt = select(users_table).order_by(
1453 users_table.c.name.desc().nulls_first()
1454 )
1456 .. versionchanged:: 1.4 :func:`.nulls_first` is renamed from
1457 :func:`.nullsfirst` in previous releases.
1458 The previous name remains available for backwards compatibility.
1460 .. seealso::
1462 :func:`.asc`
1464 :func:`.desc`
1466 :func:`.nulls_last`
1468 :meth:`_expression.Select.order_by`
1470 """ # noqa: E501
1471 if isinstance(column, operators.OrderingOperators):
1472 return column.nulls_first()
1473 else:
1474 return UnaryExpression._create_nulls_first(column)
1477@overload
1478def nulls_last(
1479 column: "ColumnElement[_T]",
1480) -> UnaryExpression[_T]: ...
1483@overload
1484def nulls_last(
1485 column: _ColumnExpressionArgument[_T],
1486) -> Union[OrderByList, UnaryExpression[_T]]: ...
1489def nulls_last(
1490 column: _ColumnExpressionArgument[_T],
1491) -> Union[OrderByList, UnaryExpression[_T]]:
1492 """Produce the ``NULLS LAST`` modifier for an ``ORDER BY`` expression.
1494 :func:`.nulls_last` is intended to modify the expression produced
1495 by :func:`.asc` or :func:`.desc`, and indicates how NULL values
1496 should be handled when they are encountered during ordering::
1499 from sqlalchemy import desc, nulls_last
1501 stmt = select(users_table).order_by(nulls_last(desc(users_table.c.name)))
1503 The SQL expression from the above would resemble:
1505 .. sourcecode:: sql
1507 SELECT id, name FROM user ORDER BY name DESC NULLS LAST
1509 Like :func:`.asc` and :func:`.desc`, :func:`.nulls_last` is typically
1510 invoked from the column expression itself using
1511 :meth:`_expression.ColumnElement.nulls_last`,
1512 rather than as its standalone
1513 function version, as in::
1515 stmt = select(users_table).order_by(users_table.c.name.desc().nulls_last())
1517 .. versionchanged:: 1.4 :func:`.nulls_last` is renamed from
1518 :func:`.nullslast` in previous releases.
1519 The previous name remains available for backwards compatibility.
1521 .. seealso::
1523 :func:`.asc`
1525 :func:`.desc`
1527 :func:`.nulls_first`
1529 :meth:`_expression.Select.order_by`
1531 """ # noqa: E501
1532 if isinstance(column, operators.OrderingOperators):
1533 return column.nulls_last()
1534 else:
1535 return UnaryExpression._create_nulls_last(column)
1538def or_( # type: ignore[empty-body]
1539 initial_clause: Union[Literal[False], _ColumnExpressionArgument[bool]],
1540 *clauses: _ColumnExpressionArgument[bool],
1541) -> ColumnElement[bool]:
1542 """Produce a conjunction of expressions joined by ``OR``.
1544 E.g.::
1546 from sqlalchemy import or_
1548 stmt = select(users_table).where(
1549 or_(users_table.c.name == "wendy", users_table.c.name == "jack")
1550 )
1552 The :func:`.or_` conjunction is also available using the
1553 Python ``|`` operator (though note that compound expressions
1554 need to be parenthesized in order to function with Python
1555 operator precedence behavior)::
1557 stmt = select(users_table).where(
1558 (users_table.c.name == "wendy") | (users_table.c.name == "jack")
1559 )
1561 The :func:`.or_` construct must be given at least one positional
1562 argument in order to be valid; a :func:`.or_` construct with no
1563 arguments is ambiguous. To produce an "empty" or dynamically
1564 generated :func:`.or_` expression, from a given list of expressions,
1565 a "default" element of :func:`_sql.false` (or just ``False``) should be
1566 specified::
1568 from sqlalchemy import false
1570 or_criteria = or_(false(), *expressions)
1572 The above expression will compile to SQL as the expression ``false``
1573 or ``0 = 1``, depending on backend, if no other expressions are
1574 present. If expressions are present, then the :func:`_sql.false` value is
1575 ignored as it does not affect the outcome of an OR expression which
1576 has other elements.
1578 .. deprecated:: 1.4 The :func:`.or_` element now requires that at
1579 least one argument is passed; creating the :func:`.or_` construct
1580 with no arguments is deprecated, and will emit a deprecation warning
1581 while continuing to produce a blank SQL string.
1583 .. seealso::
1585 :func:`.and_`
1587 """
1588 ...
1591if not TYPE_CHECKING:
1592 # handle deprecated case which allows zero-arguments
1593 def or_(*clauses): # noqa: F811
1594 """Produce a conjunction of expressions joined by ``OR``.
1596 E.g.::
1598 from sqlalchemy import or_
1600 stmt = select(users_table).where(
1601 or_(users_table.c.name == "wendy", users_table.c.name == "jack")
1602 )
1604 The :func:`.or_` conjunction is also available using the
1605 Python ``|`` operator (though note that compound expressions
1606 need to be parenthesized in order to function with Python
1607 operator precedence behavior)::
1609 stmt = select(users_table).where(
1610 (users_table.c.name == "wendy") | (users_table.c.name == "jack")
1611 )
1613 The :func:`.or_` construct must be given at least one positional
1614 argument in order to be valid; a :func:`.or_` construct with no
1615 arguments is ambiguous. To produce an "empty" or dynamically
1616 generated :func:`.or_` expression, from a given list of expressions,
1617 a "default" element of :func:`_sql.false` (or just ``False``) should be
1618 specified::
1620 from sqlalchemy import false
1622 or_criteria = or_(false(), *expressions)
1624 The above expression will compile to SQL as the expression ``false``
1625 or ``0 = 1``, depending on backend, if no other expressions are
1626 present. If expressions are present, then the :func:`_sql.false` value
1627 is ignored as it does not affect the outcome of an OR expression which
1628 has other elements.
1630 .. deprecated:: 1.4 The :func:`.or_` element now requires that at
1631 least one argument is passed; creating the :func:`.or_` construct
1632 with no arguments is deprecated, and will emit a deprecation warning
1633 while continuing to produce a blank SQL string.
1635 .. seealso::
1637 :func:`.and_`
1639 """ # noqa: E501
1640 return BooleanClauseList.or_(*clauses)
1643def over(
1644 element: FunctionElement[_T],
1645 partition_by: _ByArgument | None = None,
1646 order_by: _ByArgument | None = None,
1647 range_: _FrameIntTuple | FrameClause | None = None,
1648 rows: _FrameIntTuple | FrameClause | None = None,
1649 groups: _FrameIntTuple | FrameClause | None = None,
1650 exclude: str | None = None,
1651) -> Over[_T]:
1652 r"""Produce an :class:`.Over` object against a function.
1654 Used against aggregate or so-called "window" functions,
1655 for database backends that support window functions.
1657 :func:`_expression.over` is usually called using
1658 the :meth:`.FunctionElement.over` method, e.g.::
1660 func.row_number().over(order_by=mytable.c.some_column)
1662 Would produce:
1664 .. sourcecode:: sql
1666 ROW_NUMBER() OVER(ORDER BY some_column)
1668 Ranges are also possible using the :paramref:`.expression.over.range_`,
1669 :paramref:`.expression.over.rows`, and :paramref:`.expression.over.groups`
1670 parameters. These
1671 mutually-exclusive parameters each accept a 2-tuple, which contains
1672 a combination of integers and None::
1674 func.row_number().over(order_by=my_table.c.some_column, range_=(None, 0))
1676 The above would produce:
1678 .. sourcecode:: sql
1680 ROW_NUMBER() OVER(ORDER BY some_column
1681 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
1683 A value of ``None`` indicates "unbounded", a
1684 value of zero indicates "current row", and negative / positive
1685 integers indicate "preceding" and "following":
1687 * RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING::
1689 func.row_number().over(order_by="x", range_=(-5, 10))
1691 * ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW::
1693 func.row_number().over(order_by="x", rows=(None, 0))
1695 * RANGE BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING::
1697 func.row_number().over(order_by="x", range_=(-2, None))
1699 * RANGE BETWEEN 1 FOLLOWING AND 3 FOLLOWING::
1701 func.row_number().over(order_by="x", range_=(1, 3))
1703 * GROUPS BETWEEN 1 FOLLOWING AND 3 FOLLOWING::
1705 func.row_number().over(order_by="x", groups=(1, 3))
1707 Depending on the type of the order column, the 'RANGE' value may not be
1708 an integer. In this case use a :class:`_expression.FrameClause` directly
1709 to specify the frame boundaries. E.g.::
1711 from datetime import timedelta
1712 from sqlalchemy import FrameClause, FrameClauseType
1714 func.sum(my_table.c.amount).over(
1715 order_by=my_table.c.date,
1716 range_=FrameClause(
1717 start=timedelta(days=7),
1718 end=None,
1719 start_frame_type=FrameClauseType.PRECEDING,
1720 end_frame_type=FrameClauseType.UNBOUNDED,
1721 ),
1722 )
1724 .. versionchanged:: 2.1 Added support for range types that are not
1725 integer-based, via the :class:`_expression.FrameClause` construct.
1727 :param element: a :class:`.FunctionElement`, :class:`.WithinGroup`,
1728 or other compatible construct.
1729 :param partition_by: a column element or string, or a list
1730 of such, that will be used as the PARTITION BY clause
1731 of the OVER construct.
1732 :param order_by: a column element or string, or a list
1733 of such, that will be used as the ORDER BY clause
1734 of the OVER construct.
1735 :param range\_: optional range clause for the window. This is a
1736 two-tuple value which can contain integer values or ``None``,
1737 and will render a RANGE BETWEEN PRECEDING / FOLLOWING clause.
1738 Can also be a :class:`_expression.FrameClause` instance to
1739 specify non-integer values.
1741 .. versionchanged:: 2.1 Added support for range types that are not
1742 integer-based, via the :class:`_expression.FrameClause` construct.
1744 :param rows: optional rows clause for the window. This is a two-tuple
1745 value which can contain integer values or None, and will render
1746 a ROWS BETWEEN PRECEDING / FOLLOWING clause. Can also be a
1747 :class:`_expression.FrameClause` instance.
1748 :param groups: optional groups clause for the window. This is a
1749 two-tuple value which can contain integer values or ``None``,
1750 and will render a GROUPS BETWEEN PRECEDING / FOLLOWING clause.
1751 Can also be a :class:`_expression.FrameClause` instance.
1753 .. versionadded:: 2.0.40
1755 :param exclude: optional string for the frame exclusion clause. This is a
1756 string value which can be one of ``CURRENT ROW``, ``GROUP``, ``TIES``, or
1757 ``NO OTHERS`` and will render an EXCLUDE clause within the window frame
1758 specification. Requires that one of :paramref:`_sql.over.rows`,
1759 :paramref:`_sql.over.range_`, or :paramref:`_sql.over.groups` is also
1760 specified.
1762 .. versionadded:: 2.1
1764 This function is also available from the :data:`~.expression.func`
1765 construct itself via the :meth:`.FunctionElement.over` method.
1767 .. seealso::
1769 :ref:`tutorial_window_functions` - in the :ref:`unified_tutorial`
1771 :data:`.expression.func`
1773 :func:`_expression.within_group`
1775 """ # noqa: E501
1776 return Over(
1777 element,
1778 partition_by,
1779 order_by,
1780 range_,
1781 rows,
1782 groups,
1783 exclude,
1784 )
1787@_document_text_coercion("text", ":func:`.text`", ":paramref:`.text.text`")
1788def text(text: str) -> TextClause:
1789 r"""Construct a new :class:`_expression.TextClause` clause,
1790 representing
1791 a textual SQL string directly.
1793 E.g.::
1795 from sqlalchemy import text
1797 t = text("SELECT * FROM users")
1798 result = connection.execute(t)
1800 The advantages :func:`_expression.text`
1801 provides over a plain string are
1802 backend-neutral support for bind parameters, per-statement
1803 execution options, as well as
1804 bind parameter and result-column typing behavior, allowing
1805 SQLAlchemy type constructs to play a role when executing
1806 a statement that is specified literally. The construct can also
1807 be provided with a ``.c`` collection of column elements, allowing
1808 it to be embedded in other SQL expression constructs as a subquery.
1810 Bind parameters are specified by name, using the format ``:name``.
1811 E.g.::
1813 t = text("SELECT * FROM users WHERE id=:user_id")
1814 result = connection.execute(t, {"user_id": 12})
1816 For SQL statements where a colon is required verbatim, as within
1817 an inline string, use a backslash to escape::
1819 t = text(r"SELECT * FROM users WHERE name='\:username'")
1821 .. note:: A name that is immediately followed by another colon,
1822 such as ``:this:that`` or ``:value::int``, is currently not
1823 interpreted as a bound parameter and is rendered as literal text;
1824 whitespace may be used to separate the name from the colon, e.g.
1825 ``:value ::int``. This behavior may change in a future release.
1827 The :class:`_expression.TextClause`
1828 construct includes methods which can
1829 provide information about the bound parameters as well as the column
1830 values which would be returned from the textual statement, assuming
1831 it's an executable SELECT type of statement. The
1832 :meth:`_expression.TextClause.bindparams`
1833 method is used to provide bound
1834 parameter detail, and :meth:`_expression.TextClause.columns`
1835 method allows
1836 specification of return columns including names and types::
1838 t = (
1839 text("SELECT * FROM users WHERE id=:user_id")
1840 .bindparams(user_id=7)
1841 .columns(id=Integer, name=String)
1842 )
1844 for id, name in connection.execute(t):
1845 print(id, name)
1847 The :func:`_expression.text` construct is used in cases when
1848 a literal string SQL fragment is specified as part of a larger query,
1849 such as for the WHERE clause of a SELECT statement::
1851 s = select(users.c.id, users.c.name).where(text("id=:user_id"))
1852 result = connection.execute(s, {"user_id": 12})
1854 :func:`_expression.text` is also used for the construction
1855 of a full, standalone statement using plain text.
1856 As such, SQLAlchemy refers
1857 to it as an :class:`.Executable` object and may be used
1858 like any other statement passed to an ``.execute()`` method.
1860 :param text:
1861 the text of the SQL statement to be created. Use ``:<param>``
1862 to specify bind parameters; they will be compiled to their
1863 engine-specific format.
1865 .. seealso::
1867 :ref:`tutorial_select_arbitrary_text`
1869 """
1870 return TextClause(text)
1873def tstring(template: Template) -> TString:
1874 r"""Construct a new :class:`_expression.TString` clause,
1875 representing a SQL template string using Python 3.14+ t-strings.
1877 .. versionadded:: 2.1
1879 E.g.::
1881 from sqlalchemy import tstring
1883 a = 5
1884 b = 10
1885 stmt = tstring(t"select {a}, {b}")
1886 result = connection.execute(stmt)
1888 The :func:`_expression.tstring` function accepts a Python 3.14+
1889 template string (t-string) and processes it to create a SQL statement.
1890 Unlike :func:`_expression.text`, which requires manual bind parameter
1891 specification, :func:`_expression.tstring` automatically handles
1892 interpolation of Python values and SQLAlchemy expressions.
1894 **Interpolation Behavior**:
1896 - **SQL content** expressed in the plain string portions of the template
1897 are rendered directly as SQL
1898 - **SQLAlchemy expressions** (columns, functions, etc.) are embedded
1899 as clause elements
1900 - **Plain Python values** are automatically wrapped in
1901 :func:`_expression.literal`
1903 For example::
1905 from sqlalchemy import tstring, select, literal, JSON, table, column
1907 # Python values become bound parameters
1908 user_id = 42
1909 stmt = tstring(t"SELECT * FROM users WHERE id = {user_id}")
1910 # renders: SELECT * FROM users WHERE id = :param_1
1912 # SQLAlchemy expressions are embedded
1913 stmt = tstring(t"SELECT {column('q')} FROM {table('t')}")
1914 # renders: SELECT q FROM t
1916 # Apply explicit SQL types to bound values using literal()
1917 some_json = {"foo": "bar"}
1918 stmt = tstring(t"SELECT {literal(some_json, JSON)}")
1920 **Literal SQL Text**:
1922 The plain string portions of the template are rendered exactly as
1923 given. Unlike :func:`_expression.text`, they are not scanned for
1924 bound parameter names in the ``:name`` format, and there are no escaping
1925 rules such as ``\:`` for colons; a colon in the template, such as within
1926 a quoted SQL string, is rendered unchanged. A named bound parameter
1927 whose value is passed at execution time may be embedded explicitly
1928 using :func:`_expression.bindparam`::
1930 from sqlalchemy import tstring, bindparam, Integer
1932 user_id = bindparam("user_id", type_=Integer)
1933 stmt = tstring(t"SELECT * FROM users WHERE id = {user_id}")
1934 result = connection.execute(stmt, {"user_id": 42})
1936 .. versionchanged:: 2.1.2 The plain string portions of a
1937 :func:`_expression.tstring` are no longer scanned for bound parameter
1938 names or ``\:`` escapes, as was the case in 2.1.0 and 2.1.1.
1940 **Column Specification**:
1942 Like :func:`_expression.text`, the :func:`_expression.tstring` construct
1943 supports the :meth:`_expression.TString.columns` method to specify
1944 return columns and their types::
1946 from sqlalchemy import tstring, column, Integer, String
1948 stmt = tstring(t"SELECT id, name FROM users").columns(
1949 column("id", Integer), column("name", String)
1950 )
1952 for id, name in connection.execute(stmt):
1953 print(id, name)
1955 :param template:
1956 a Python 3.14+ template string (t-string) containing SQL fragments
1957 and Python expressions to be interpolated.
1959 .. seealso::
1961 :ref:`tutorial_select_arbitrary_text` - in the :ref:`unified_tutorial`
1963 :class:`_expression.TString`
1965 :func:`_expression.text`
1967 `PEP 750 <https://peps.python.org/pep-0750/>`_ - Template Strings
1969 """
1970 return TString(template)
1973def true() -> True_:
1974 """Return a constant :class:`.True_` construct.
1976 E.g.:
1978 .. sourcecode:: pycon+sql
1980 >>> from sqlalchemy import true
1981 >>> print(select(t.c.x).where(true()))
1982 {printsql}SELECT x FROM t WHERE true
1984 A backend which does not support true/false constants will render as
1985 an expression against 1 or 0:
1987 .. sourcecode:: pycon+sql
1989 >>> print(select(t.c.x).where(true()))
1990 {printsql}SELECT x FROM t WHERE 1 = 1
1992 The :func:`.true` and :func:`.false` constants also feature
1993 "short circuit" operation within an :func:`.and_` or :func:`.or_`
1994 conjunction:
1996 .. sourcecode:: pycon+sql
1998 >>> print(select(t.c.x).where(or_(t.c.x > 5, true())))
1999 {printsql}SELECT x FROM t WHERE true{stop}
2001 >>> print(select(t.c.x).where(and_(t.c.x > 5, false())))
2002 {printsql}SELECT x FROM t WHERE false{stop}
2004 .. seealso::
2006 :func:`.false`
2008 """
2010 return True_._instance()
2013def tuple_(
2014 *clauses: _ColumnExpressionOrLiteralArgument[Any],
2015 types: Optional[Sequence[_TypeEngineArgument[Any]]] = None,
2016) -> Tuple:
2017 """Return a :class:`.Tuple`.
2019 Main usage is to produce a composite IN construct using
2020 :meth:`.ColumnOperators.in_` ::
2022 from sqlalchemy import tuple_
2024 tuple_(table.c.col1, table.c.col2).in_([(1, 2), (5, 12), (10, 19)])
2026 .. warning::
2028 The composite IN construct is not supported by all backends, and is
2029 currently known to work on PostgreSQL, MySQL, and SQLite.
2030 Unsupported backends will raise a subclass of
2031 :class:`~sqlalchemy.exc.DBAPIError` when such an expression is
2032 invoked.
2034 """
2035 return Tuple(*clauses, types=types)
2038def type_coerce(
2039 expression: _ColumnExpressionOrLiteralArgument[Any],
2040 type_: _CovariantTypeEngineArgument[_T],
2041) -> TypeCoerce[_T]:
2042 r"""Associate a SQL expression with a particular type, without rendering
2043 ``CAST``.
2045 E.g.::
2047 from sqlalchemy import type_coerce
2049 stmt = select(type_coerce(log_table.date_string, StringDateTime()))
2051 The above construct will produce a :class:`.TypeCoerce` object, which
2052 does not modify the rendering in any way on the SQL side, with the
2053 possible exception of a generated label if used in a columns clause
2054 context:
2056 .. sourcecode:: sql
2058 SELECT date_string AS date_string FROM log
2060 When result rows are fetched, the ``StringDateTime`` type processor
2061 will be applied to result rows on behalf of the ``date_string`` column.
2063 .. note:: the :func:`.type_coerce` construct does not render any
2064 SQL syntax of its own, including that it does not imply
2065 parenthesization. Please use :meth:`.TypeCoerce.self_group`
2066 if explicit parenthesization is required.
2068 In order to provide a named label for the expression, use
2069 :meth:`_expression.ColumnElement.label`::
2071 stmt = select(
2072 type_coerce(log_table.date_string, StringDateTime()).label("date")
2073 )
2075 A type that features bound-value handling will also have that behavior
2076 take effect when literal values or :func:`.bindparam` constructs are
2077 passed to :func:`.type_coerce` as targets.
2078 For example, if a type implements the
2079 :meth:`.TypeEngine.bind_expression`
2080 method or :meth:`.TypeEngine.bind_processor` method or equivalent,
2081 these functions will take effect at statement compilation/execution
2082 time when a literal value is passed, as in::
2084 # bound-value handling of MyStringType will be applied to the
2085 # literal value "some string"
2086 stmt = select(type_coerce("some string", MyStringType))
2088 When using :func:`.type_coerce` with composed expressions, note that
2089 **parenthesis are not applied**. If :func:`.type_coerce` is being
2090 used in an operator context where the parenthesis normally present from
2091 CAST are necessary, use the :meth:`.TypeCoerce.self_group` method:
2093 .. sourcecode:: pycon+sql
2095 >>> some_integer = column("someint", Integer)
2096 >>> some_string = column("somestr", String)
2097 >>> expr = type_coerce(some_integer + 5, String) + some_string
2098 >>> print(expr)
2099 {printsql}someint + :someint_1 || somestr{stop}
2100 >>> expr = type_coerce(some_integer + 5, String).self_group() + some_string
2101 >>> print(expr)
2102 {printsql}(someint + :someint_1) || somestr{stop}
2104 :param expression: A SQL expression, such as a
2105 :class:`_expression.ColumnElement`
2106 expression or a Python string which will be coerced into a bound
2107 literal value.
2109 :param type\_: A :class:`.TypeEngine` class or instance indicating
2110 the type to which the expression is coerced.
2112 .. seealso::
2114 :ref:`tutorial_casts`
2116 :func:`.cast`
2118 """ # noqa
2119 return TypeCoerce(expression, type_)
2122def within_group(
2123 element: FunctionElement[_T], *order_by: _ColumnExpressionArgument[Any]
2124) -> WithinGroup[_T]:
2125 r"""Produce a :class:`.WithinGroup` object against a function.
2127 Used against so-called "ordered set aggregate" and "hypothetical
2128 set aggregate" functions, including :class:`.percentile_cont`,
2129 :class:`.rank`, :class:`.dense_rank`, etc. This feature is typically
2130 used by Oracle Database, Microsoft SQL Server.
2132 For generalized ORDER BY of aggregate functions on all included
2133 backends, including PostgreSQL, MySQL/MariaDB, SQLite as well as Oracle
2134 and SQL Server, the :func:`_sql.aggregate_order_by` provides a more
2135 general approach that compiles to "WITHIN GROUP" only on those backends
2136 which require it.
2138 :func:`_expression.within_group` is usually called using
2139 the :meth:`.FunctionElement.within_group` method, e.g.::
2141 stmt = select(
2142 func.percentile_cont(0.5).within_group(department.c.salary.desc()),
2143 )
2145 The above statement would produce SQL similar to
2146 ``SELECT percentile_cont(0.5)
2147 WITHIN GROUP (ORDER BY department.salary DESC)``.
2149 :param element: a :class:`.FunctionElement` construct, typically
2150 generated by :data:`~.expression.func`.
2151 :param \*order_by: one or more column elements that will be used
2152 as the ORDER BY clause of the WITHIN GROUP construct.
2154 .. seealso::
2156 :ref:`tutorial_functions_within_group` - in the
2157 :ref:`unified_tutorial`
2159 :func:`_sql.aggregate_order_by` - helper for PostgreSQL, MySQL,
2160 SQLite aggregate functions
2162 :data:`.expression.func`
2164 :func:`_expression.over`
2166 """
2167 return WithinGroup(element, *order_by)
2170def aggregate_order_by(
2171 element: FunctionElement[_T], *order_by: _ColumnExpressionArgument[Any]
2172) -> AggregateOrderBy[_T]:
2173 r"""Produce a :class:`.AggregateOrderBy` object against a function.
2175 Used for aggregating functions such as :class:`_functions.array_agg`,
2176 ``group_concat``, ``json_agg`` on backends that support ordering via an
2177 embedded ``ORDER BY`` parameter, e.g. PostgreSQL, MySQL/MariaDB, SQLite.
2178 When used on backends like Oracle and SQL Server, SQL compilation uses that
2179 of :class:`.WithinGroup`. On PostgreSQL, compilation is fixed at embedded
2180 ``ORDER BY``; for set aggregation functions where PostgreSQL requires the
2181 use of ``WITHIN GROUP``, :func:`_expression.within_group` should be used
2182 explicitly.
2184 :func:`_expression.aggregate_order_by` is usually called using
2185 the :meth:`.FunctionElement.aggregate_order_by` method, e.g.::
2187 stmt = select(
2188 func.array_agg(department.c.code).aggregate_order_by(
2189 department.c.code.desc()
2190 ),
2191 )
2193 which would produce an expression resembling:
2195 .. sourcecode:: sql
2197 SELECT array_agg(department.code ORDER BY department.code DESC)
2198 AS array_agg_1 FROM department
2200 The ORDER BY argument may also be multiple terms.
2202 When using the backend-agnostic :class:`_functions.aggregate_strings`
2203 string aggregation function, use the
2204 :paramref:`_functions.aggregate_strings.order_by` parameter to indicate a
2205 dialect-agnostic ORDER BY expression.
2207 .. versionadded:: 2.1 Generalized the PostgreSQL-specific
2208 :func:`_postgresql.aggregate_order_by` function to a method on
2209 :class:`.Function` that is backend agnostic.
2211 .. seealso::
2213 :class:`_functions.aggregate_strings` - backend-agnostic string
2214 concatenation function which also supports ORDER BY
2216 """ # noqa: E501
2217 return AggregateOrderBy(element, *order_by)