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-2024 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 Mapping
14from typing import Optional
15from typing import overload
16from typing import Sequence
17from typing import Tuple as typing_Tuple
18from typing import TYPE_CHECKING
19from typing import TypeVar
20from typing import Union
22from . import coercions
23from . import roles
24from .base import _NoArg
25from .coercions import _document_text_coercion
26from .elements import BindParameter
27from .elements import BooleanClauseList
28from .elements import Case
29from .elements import Cast
30from .elements import CollationClause
31from .elements import CollectionAggregate
32from .elements import ColumnClause
33from .elements import ColumnElement
34from .elements import Extract
35from .elements import False_
36from .elements import FunctionFilter
37from .elements import Label
38from .elements import Null
39from .elements import Over
40from .elements import TextClause
41from .elements import True_
42from .elements import TryCast
43from .elements import Tuple
44from .elements import TypeCoerce
45from .elements import UnaryExpression
46from .elements import WithinGroup
47from .functions import FunctionElement
48from ..util.typing import Literal
50if typing.TYPE_CHECKING:
51 from ._typing import _ByArgument
52 from ._typing import _ColumnExpressionArgument
53 from ._typing import _ColumnExpressionOrLiteralArgument
54 from ._typing import _ColumnExpressionOrStrLabelArgument
55 from ._typing import _TypeEngineArgument
56 from .elements import BinaryExpression
57 from .selectable import FromClause
58 from .type_api import TypeEngine
60_T = TypeVar("_T")
63def all_(expr: _ColumnExpressionArgument[_T]) -> CollectionAggregate[bool]:
64 """Produce an ALL expression.
66 For dialects such as that of PostgreSQL, this operator applies
67 to usage of the :class:`_types.ARRAY` datatype, for that of
68 MySQL, it may apply to a subquery. e.g.::
70 # renders on PostgreSQL:
71 # '5 = ALL (somearray)'
72 expr = 5 == all_(mytable.c.somearray)
74 # renders on MySQL:
75 # '5 = ALL (SELECT value FROM table)'
76 expr = 5 == all_(select(table.c.value))
78 Comparison to NULL may work using ``None``::
80 None == all_(mytable.c.somearray)
82 The any_() / all_() operators also feature a special "operand flipping"
83 behavior such that if any_() / all_() are used on the left side of a
84 comparison using a standalone operator such as ``==``, ``!=``, etc.
85 (not including operator methods such as
86 :meth:`_sql.ColumnOperators.is_`) the rendered expression is flipped::
88 # would render '5 = ALL (column)`
89 all_(mytable.c.column) == 5
91 Or with ``None``, which note will not perform
92 the usual step of rendering "IS" as is normally the case for NULL::
94 # would render 'NULL = ALL(somearray)'
95 all_(mytable.c.somearray) == None
97 .. versionchanged:: 1.4.26 repaired the use of any_() / all_()
98 comparing to NULL on the right side to be flipped to the left.
100 The column-level :meth:`_sql.ColumnElement.all_` method (not to be
101 confused with :class:`_types.ARRAY` level
102 :meth:`_types.ARRAY.Comparator.all`) is shorthand for
103 ``all_(col)``::
105 5 == mytable.c.somearray.all_()
107 .. seealso::
109 :meth:`_sql.ColumnOperators.all_`
111 :func:`_expression.any_`
113 """
114 return CollectionAggregate._create_all(expr)
117def and_( # type: ignore[empty-body]
118 initial_clause: Union[Literal[True], _ColumnExpressionArgument[bool]],
119 *clauses: _ColumnExpressionArgument[bool],
120) -> ColumnElement[bool]:
121 r"""Produce a conjunction of expressions joined by ``AND``.
123 E.g.::
125 from sqlalchemy import and_
127 stmt = select(users_table).where(
128 and_(
129 users_table.c.name == 'wendy',
130 users_table.c.enrolled == True
131 )
132 )
134 The :func:`.and_` conjunction is also available using the
135 Python ``&`` operator (though note that compound expressions
136 need to be parenthesized in order to function with Python
137 operator precedence behavior)::
139 stmt = select(users_table).where(
140 (users_table.c.name == 'wendy') &
141 (users_table.c.enrolled == True)
142 )
144 The :func:`.and_` operation is also implicit in some cases;
145 the :meth:`_expression.Select.where`
146 method for example can be invoked multiple
147 times against a statement, which will have the effect of each
148 clause being combined using :func:`.and_`::
150 stmt = select(users_table).\
151 where(users_table.c.name == 'wendy').\
152 where(users_table.c.enrolled == True)
154 The :func:`.and_` construct must be given at least one positional
155 argument in order to be valid; a :func:`.and_` construct with no
156 arguments is ambiguous. To produce an "empty" or dynamically
157 generated :func:`.and_` expression, from a given list of expressions,
158 a "default" element of :func:`_sql.true` (or just ``True``) should be
159 specified::
161 from sqlalchemy import true
162 criteria = and_(true(), *expressions)
164 The above expression will compile to SQL as the expression ``true``
165 or ``1 = 1``, depending on backend, if no other expressions are
166 present. If expressions are present, then the :func:`_sql.true` value is
167 ignored as it does not affect the outcome of an AND expression that
168 has other elements.
170 .. deprecated:: 1.4 The :func:`.and_` element now requires that at
171 least one argument is passed; creating the :func:`.and_` construct
172 with no arguments is deprecated, and will emit a deprecation warning
173 while continuing to produce a blank SQL string.
175 .. seealso::
177 :func:`.or_`
179 """
180 ...
183if not TYPE_CHECKING:
184 # handle deprecated case which allows zero-arguments
185 def and_(*clauses): # noqa: F811
186 r"""Produce a conjunction of expressions joined by ``AND``.
188 E.g.::
190 from sqlalchemy import and_
192 stmt = select(users_table).where(
193 and_(
194 users_table.c.name == 'wendy',
195 users_table.c.enrolled == True
196 )
197 )
199 The :func:`.and_` conjunction is also available using the
200 Python ``&`` operator (though note that compound expressions
201 need to be parenthesized in order to function with Python
202 operator precedence behavior)::
204 stmt = select(users_table).where(
205 (users_table.c.name == 'wendy') &
206 (users_table.c.enrolled == True)
207 )
209 The :func:`.and_` operation is also implicit in some cases;
210 the :meth:`_expression.Select.where`
211 method for example can be invoked multiple
212 times against a statement, which will have the effect of each
213 clause being combined using :func:`.and_`::
215 stmt = select(users_table).\
216 where(users_table.c.name == 'wendy').\
217 where(users_table.c.enrolled == True)
219 The :func:`.and_` construct must be given at least one positional
220 argument in order to be valid; a :func:`.and_` construct with no
221 arguments is ambiguous. To produce an "empty" or dynamically
222 generated :func:`.and_` expression, from a given list of expressions,
223 a "default" element of :func:`_sql.true` (or just ``True``) should be
224 specified::
226 from sqlalchemy import true
227 criteria = and_(true(), *expressions)
229 The above expression will compile to SQL as the expression ``true``
230 or ``1 = 1``, depending on backend, if no other expressions are
231 present. If expressions are present, then the :func:`_sql.true` value
232 is ignored as it does not affect the outcome of an AND expression that
233 has other elements.
235 .. deprecated:: 1.4 The :func:`.and_` element now requires that at
236 least one argument is passed; creating the :func:`.and_` construct
237 with no arguments is deprecated, and will emit a deprecation warning
238 while continuing to produce a blank SQL string.
240 .. seealso::
242 :func:`.or_`
244 """
245 return BooleanClauseList.and_(*clauses)
248def any_(expr: _ColumnExpressionArgument[_T]) -> CollectionAggregate[bool]:
249 """Produce an ANY expression.
251 For dialects such as that of PostgreSQL, this operator applies
252 to usage of the :class:`_types.ARRAY` datatype, for that of
253 MySQL, it may apply to a subquery. e.g.::
255 # renders on PostgreSQL:
256 # '5 = ANY (somearray)'
257 expr = 5 == any_(mytable.c.somearray)
259 # renders on MySQL:
260 # '5 = ANY (SELECT value FROM table)'
261 expr = 5 == any_(select(table.c.value))
263 Comparison to NULL may work using ``None`` or :func:`_sql.null`::
265 None == any_(mytable.c.somearray)
267 The any_() / all_() operators also feature a special "operand flipping"
268 behavior such that if any_() / all_() are used on the left side of a
269 comparison using a standalone operator such as ``==``, ``!=``, etc.
270 (not including operator methods such as
271 :meth:`_sql.ColumnOperators.is_`) the rendered expression is flipped::
273 # would render '5 = ANY (column)`
274 any_(mytable.c.column) == 5
276 Or with ``None``, which note will not perform
277 the usual step of rendering "IS" as is normally the case for NULL::
279 # would render 'NULL = ANY(somearray)'
280 any_(mytable.c.somearray) == None
282 .. versionchanged:: 1.4.26 repaired the use of any_() / all_()
283 comparing to NULL on the right side to be flipped to the left.
285 The column-level :meth:`_sql.ColumnElement.any_` method (not to be
286 confused with :class:`_types.ARRAY` level
287 :meth:`_types.ARRAY.Comparator.any`) is shorthand for
288 ``any_(col)``::
290 5 = mytable.c.somearray.any_()
292 .. seealso::
294 :meth:`_sql.ColumnOperators.any_`
296 :func:`_expression.all_`
298 """
299 return CollectionAggregate._create_any(expr)
302def asc(
303 column: _ColumnExpressionOrStrLabelArgument[_T],
304) -> UnaryExpression[_T]:
305 """Produce an ascending ``ORDER BY`` clause element.
307 e.g.::
309 from sqlalchemy import asc
310 stmt = select(users_table).order_by(asc(users_table.c.name))
312 will produce SQL as::
314 SELECT id, name FROM user ORDER BY name ASC
316 The :func:`.asc` function is a standalone version of the
317 :meth:`_expression.ColumnElement.asc`
318 method available on all SQL expressions,
319 e.g.::
322 stmt = select(users_table).order_by(users_table.c.name.asc())
324 :param column: A :class:`_expression.ColumnElement` (e.g.
325 scalar SQL expression)
326 with which to apply the :func:`.asc` operation.
328 .. seealso::
330 :func:`.desc`
332 :func:`.nulls_first`
334 :func:`.nulls_last`
336 :meth:`_expression.Select.order_by`
338 """
339 return UnaryExpression._create_asc(column)
342def collate(
343 expression: _ColumnExpressionArgument[str], collation: str
344) -> BinaryExpression[str]:
345 """Return the clause ``expression COLLATE collation``.
347 e.g.::
349 collate(mycolumn, 'utf8_bin')
351 produces::
353 mycolumn COLLATE utf8_bin
355 The collation expression is also quoted if it is a case sensitive
356 identifier, e.g. contains uppercase characters.
358 .. versionchanged:: 1.2 quoting is automatically applied to COLLATE
359 expressions if they are case sensitive.
361 """
362 return CollationClause._create_collation_expression(expression, collation)
365def between(
366 expr: _ColumnExpressionOrLiteralArgument[_T],
367 lower_bound: Any,
368 upper_bound: Any,
369 symmetric: bool = False,
370) -> BinaryExpression[bool]:
371 """Produce a ``BETWEEN`` predicate clause.
373 E.g.::
375 from sqlalchemy import between
376 stmt = select(users_table).where(between(users_table.c.id, 5, 7))
378 Would produce SQL resembling::
380 SELECT id, name FROM user WHERE id BETWEEN :id_1 AND :id_2
382 The :func:`.between` function is a standalone version of the
383 :meth:`_expression.ColumnElement.between` method available on all
384 SQL expressions, as in::
386 stmt = select(users_table).where(users_table.c.id.between(5, 7))
388 All arguments passed to :func:`.between`, including the left side
389 column expression, are coerced from Python scalar values if a
390 the value is not a :class:`_expression.ColumnElement` subclass.
391 For example,
392 three fixed values can be compared as in::
394 print(between(5, 3, 7))
396 Which would produce::
398 :param_1 BETWEEN :param_2 AND :param_3
400 :param expr: a column expression, typically a
401 :class:`_expression.ColumnElement`
402 instance or alternatively a Python scalar expression to be coerced
403 into a column expression, serving as the left side of the ``BETWEEN``
404 expression.
406 :param lower_bound: a column or Python scalar expression serving as the
407 lower bound of the right side of the ``BETWEEN`` expression.
409 :param upper_bound: a column or Python scalar expression serving as the
410 upper bound of the right side of the ``BETWEEN`` expression.
412 :param symmetric: if True, will render " BETWEEN SYMMETRIC ". Note
413 that not all databases support this syntax.
415 .. seealso::
417 :meth:`_expression.ColumnElement.between`
419 """
420 col_expr = coercions.expect(roles.ExpressionElementRole, expr)
421 return col_expr.between(lower_bound, upper_bound, symmetric=symmetric)
424def outparam(
425 key: str, type_: Optional[TypeEngine[_T]] = None
426) -> BindParameter[_T]:
427 """Create an 'OUT' parameter for usage in functions (stored procedures),
428 for databases which support them.
430 The ``outparam`` can be used like a regular function parameter.
431 The "output" value will be available from the
432 :class:`~sqlalchemy.engine.CursorResult` object via its ``out_parameters``
433 attribute, which returns a dictionary containing the values.
435 """
436 return BindParameter(key, None, type_=type_, unique=False, isoutparam=True)
439@overload
440def not_(clause: BinaryExpression[_T]) -> BinaryExpression[_T]: ...
443@overload
444def not_(clause: _ColumnExpressionArgument[_T]) -> ColumnElement[_T]: ...
447def not_(clause: _ColumnExpressionArgument[_T]) -> ColumnElement[_T]:
448 """Return a negation of the given clause, i.e. ``NOT(clause)``.
450 The ``~`` operator is also overloaded on all
451 :class:`_expression.ColumnElement` subclasses to produce the
452 same result.
454 """
456 return coercions.expect(roles.ExpressionElementRole, clause).__invert__()
459def bindparam(
460 key: Optional[str],
461 value: Any = _NoArg.NO_ARG,
462 type_: Optional[_TypeEngineArgument[_T]] = None,
463 unique: bool = False,
464 required: Union[bool, Literal[_NoArg.NO_ARG]] = _NoArg.NO_ARG,
465 quote: Optional[bool] = None,
466 callable_: Optional[Callable[[], Any]] = None,
467 expanding: bool = False,
468 isoutparam: bool = False,
469 literal_execute: bool = False,
470) -> BindParameter[_T]:
471 r"""Produce a "bound expression".
473 The return value is an instance of :class:`.BindParameter`; this
474 is a :class:`_expression.ColumnElement`
475 subclass which represents a so-called
476 "placeholder" value in a SQL expression, the value of which is
477 supplied at the point at which the statement in executed against a
478 database connection.
480 In SQLAlchemy, the :func:`.bindparam` construct has
481 the ability to carry along the actual value that will be ultimately
482 used at expression time. In this way, it serves not just as
483 a "placeholder" for eventual population, but also as a means of
484 representing so-called "unsafe" values which should not be rendered
485 directly in a SQL statement, but rather should be passed along
486 to the :term:`DBAPI` as values which need to be correctly escaped
487 and potentially handled for type-safety.
489 When using :func:`.bindparam` explicitly, the use case is typically
490 one of traditional deferment of parameters; the :func:`.bindparam`
491 construct accepts a name which can then be referred to at execution
492 time::
494 from sqlalchemy import bindparam
496 stmt = select(users_table).where(
497 users_table.c.name == bindparam("username")
498 )
500 The above statement, when rendered, will produce SQL similar to::
502 SELECT id, name FROM user WHERE name = :username
504 In order to populate the value of ``:username`` above, the value
505 would typically be applied at execution time to a method
506 like :meth:`_engine.Connection.execute`::
508 result = connection.execute(stmt, {"username": "wendy"})
510 Explicit use of :func:`.bindparam` is also common when producing
511 UPDATE or DELETE statements that are to be invoked multiple times,
512 where the WHERE criterion of the statement is to change on each
513 invocation, such as::
515 stmt = (
516 users_table.update()
517 .where(user_table.c.name == bindparam("username"))
518 .values(fullname=bindparam("fullname"))
519 )
521 connection.execute(
522 stmt,
523 [
524 {"username": "wendy", "fullname": "Wendy Smith"},
525 {"username": "jack", "fullname": "Jack Jones"},
526 ],
527 )
529 SQLAlchemy's Core expression system makes wide use of
530 :func:`.bindparam` in an implicit sense. It is typical that Python
531 literal values passed to virtually all SQL expression functions are
532 coerced into fixed :func:`.bindparam` constructs. For example, given
533 a comparison operation such as::
535 expr = users_table.c.name == 'Wendy'
537 The above expression will produce a :class:`.BinaryExpression`
538 construct, where the left side is the :class:`_schema.Column` object
539 representing the ``name`` column, and the right side is a
540 :class:`.BindParameter` representing the literal value::
542 print(repr(expr.right))
543 BindParameter('%(4327771088 name)s', 'Wendy', type_=String())
545 The expression above will render SQL such as::
547 user.name = :name_1
549 Where the ``:name_1`` parameter name is an anonymous name. The
550 actual string ``Wendy`` is not in the rendered string, but is carried
551 along where it is later used within statement execution. If we
552 invoke a statement like the following::
554 stmt = select(users_table).where(users_table.c.name == 'Wendy')
555 result = connection.execute(stmt)
557 We would see SQL logging output as::
559 SELECT "user".id, "user".name
560 FROM "user"
561 WHERE "user".name = %(name_1)s
562 {'name_1': 'Wendy'}
564 Above, we see that ``Wendy`` is passed as a parameter to the database,
565 while the placeholder ``:name_1`` is rendered in the appropriate form
566 for the target database, in this case the PostgreSQL database.
568 Similarly, :func:`.bindparam` is invoked automatically when working
569 with :term:`CRUD` statements as far as the "VALUES" portion is
570 concerned. The :func:`_expression.insert` construct produces an
571 ``INSERT`` expression which will, at statement execution time, generate
572 bound placeholders based on the arguments passed, as in::
574 stmt = users_table.insert()
575 result = connection.execute(stmt, {"name": "Wendy"})
577 The above will produce SQL output as::
579 INSERT INTO "user" (name) VALUES (%(name)s)
580 {'name': 'Wendy'}
582 The :class:`_expression.Insert` construct, at
583 compilation/execution time, rendered a single :func:`.bindparam`
584 mirroring the column name ``name`` as a result of the single ``name``
585 parameter we passed to the :meth:`_engine.Connection.execute` method.
587 :param key:
588 the key (e.g. the name) for this bind param.
589 Will be used in the generated
590 SQL statement for dialects that use named parameters. This
591 value may be modified when part of a compilation operation,
592 if other :class:`BindParameter` objects exist with the same
593 key, or if its length is too long and truncation is
594 required.
596 If omitted, an "anonymous" name is generated for the bound parameter;
597 when given a value to bind, the end result is equivalent to calling upon
598 the :func:`.literal` function with a value to bind, particularly
599 if the :paramref:`.bindparam.unique` parameter is also provided.
601 :param value:
602 Initial value for this bind param. Will be used at statement
603 execution time as the value for this parameter passed to the
604 DBAPI, if no other value is indicated to the statement execution
605 method for this particular parameter name. Defaults to ``None``.
607 :param callable\_:
608 A callable function that takes the place of "value". The function
609 will be called at statement execution time to determine the
610 ultimate value. Used for scenarios where the actual bind
611 value cannot be determined at the point at which the clause
612 construct is created, but embedded bind values are still desirable.
614 :param type\_:
615 A :class:`.TypeEngine` class or instance representing an optional
616 datatype for this :func:`.bindparam`. If not passed, a type
617 may be determined automatically for the bind, based on the given
618 value; for example, trivial Python types such as ``str``,
619 ``int``, ``bool``
620 may result in the :class:`.String`, :class:`.Integer` or
621 :class:`.Boolean` types being automatically selected.
623 The type of a :func:`.bindparam` is significant especially in that
624 the type will apply pre-processing to the value before it is
625 passed to the database. For example, a :func:`.bindparam` which
626 refers to a datetime value, and is specified as holding the
627 :class:`.DateTime` type, may apply conversion needed to the
628 value (such as stringification on SQLite) before passing the value
629 to the database.
631 :param unique:
632 if True, the key name of this :class:`.BindParameter` will be
633 modified if another :class:`.BindParameter` of the same name
634 already has been located within the containing
635 expression. This flag is used generally by the internals
636 when producing so-called "anonymous" bound expressions, it
637 isn't generally applicable to explicitly-named :func:`.bindparam`
638 constructs.
640 :param required:
641 If ``True``, a value is required at execution time. If not passed,
642 it defaults to ``True`` if neither :paramref:`.bindparam.value`
643 or :paramref:`.bindparam.callable` were passed. If either of these
644 parameters are present, then :paramref:`.bindparam.required`
645 defaults to ``False``.
647 :param quote:
648 True if this parameter name requires quoting and is not
649 currently known as a SQLAlchemy reserved word; this currently
650 only applies to the Oracle backend, where bound names must
651 sometimes be quoted.
653 :param isoutparam:
654 if True, the parameter should be treated like a stored procedure
655 "OUT" parameter. This applies to backends such as Oracle which
656 support OUT parameters.
658 :param expanding:
659 if True, this parameter will be treated as an "expanding" parameter
660 at execution time; the parameter value is expected to be a sequence,
661 rather than a scalar value, and the string SQL statement will
662 be transformed on a per-execution basis to accommodate the sequence
663 with a variable number of parameter slots passed to the DBAPI.
664 This is to allow statement caching to be used in conjunction with
665 an IN clause.
667 .. seealso::
669 :meth:`.ColumnOperators.in_`
671 :ref:`baked_in` - with baked queries
673 .. note:: The "expanding" feature does not support "executemany"-
674 style parameter sets.
676 .. versionadded:: 1.2
678 .. versionchanged:: 1.3 the "expanding" bound parameter feature now
679 supports empty lists.
681 :param literal_execute:
682 if True, the bound parameter will be rendered in the compile phase
683 with a special "POSTCOMPILE" token, and the SQLAlchemy compiler will
684 render the final value of the parameter into the SQL statement at
685 statement execution time, omitting the value from the parameter
686 dictionary / list passed to DBAPI ``cursor.execute()``. This
687 produces a similar effect as that of using the ``literal_binds``,
688 compilation flag, however takes place as the statement is sent to
689 the DBAPI ``cursor.execute()`` method, rather than when the statement
690 is compiled. The primary use of this
691 capability is for rendering LIMIT / OFFSET clauses for database
692 drivers that can't accommodate for bound parameters in these
693 contexts, while allowing SQL constructs to be cacheable at the
694 compilation level.
696 .. versionadded:: 1.4 Added "post compile" bound parameters
698 .. seealso::
700 :ref:`change_4808`.
702 .. seealso::
704 :ref:`tutorial_sending_parameters` - in the
705 :ref:`unified_tutorial`
708 """
709 return BindParameter(
710 key,
711 value,
712 type_,
713 unique,
714 required,
715 quote,
716 callable_,
717 expanding,
718 isoutparam,
719 literal_execute,
720 )
723def case(
724 *whens: Union[
725 typing_Tuple[_ColumnExpressionArgument[bool], Any], Mapping[Any, Any]
726 ],
727 value: Optional[Any] = None,
728 else_: Optional[Any] = None,
729) -> Case[Any]:
730 r"""Produce a ``CASE`` expression.
732 The ``CASE`` construct in SQL is a conditional object that
733 acts somewhat analogously to an "if/then" construct in other
734 languages. It returns an instance of :class:`.Case`.
736 :func:`.case` in its usual form is passed a series of "when"
737 constructs, that is, a list of conditions and results as tuples::
739 from sqlalchemy import case
741 stmt = select(users_table).\
742 where(
743 case(
744 (users_table.c.name == 'wendy', 'W'),
745 (users_table.c.name == 'jack', 'J'),
746 else_='E'
747 )
748 )
750 The above statement will produce SQL resembling::
752 SELECT id, name FROM user
753 WHERE CASE
754 WHEN (name = :name_1) THEN :param_1
755 WHEN (name = :name_2) THEN :param_2
756 ELSE :param_3
757 END
759 When simple equality expressions of several values against a single
760 parent column are needed, :func:`.case` also has a "shorthand" format
761 used via the
762 :paramref:`.case.value` parameter, which is passed a column
763 expression to be compared. In this form, the :paramref:`.case.whens`
764 parameter is passed as a dictionary containing expressions to be
765 compared against keyed to result expressions. The statement below is
766 equivalent to the preceding statement::
768 stmt = select(users_table).\
769 where(
770 case(
771 {"wendy": "W", "jack": "J"},
772 value=users_table.c.name,
773 else_='E'
774 )
775 )
777 The values which are accepted as result values in
778 :paramref:`.case.whens` as well as with :paramref:`.case.else_` are
779 coerced from Python literals into :func:`.bindparam` constructs.
780 SQL expressions, e.g. :class:`_expression.ColumnElement` constructs,
781 are accepted
782 as well. To coerce a literal string expression into a constant
783 expression rendered inline, use the :func:`_expression.literal_column`
784 construct,
785 as in::
787 from sqlalchemy import case, literal_column
789 case(
790 (
791 orderline.c.qty > 100,
792 literal_column("'greaterthan100'")
793 ),
794 (
795 orderline.c.qty > 10,
796 literal_column("'greaterthan10'")
797 ),
798 else_=literal_column("'lessthan10'")
799 )
801 The above will render the given constants without using bound
802 parameters for the result values (but still for the comparison
803 values), as in::
805 CASE
806 WHEN (orderline.qty > :qty_1) THEN 'greaterthan100'
807 WHEN (orderline.qty > :qty_2) THEN 'greaterthan10'
808 ELSE 'lessthan10'
809 END
811 :param \*whens: The criteria to be compared against,
812 :paramref:`.case.whens` accepts two different forms, based on
813 whether or not :paramref:`.case.value` is used.
815 .. versionchanged:: 1.4 the :func:`_sql.case`
816 function now accepts the series of WHEN conditions positionally
818 In the first form, it accepts multiple 2-tuples passed as positional
819 arguments; each 2-tuple consists of ``(<sql expression>, <value>)``,
820 where the SQL expression is a boolean expression and "value" is a
821 resulting value, e.g.::
823 case(
824 (users_table.c.name == 'wendy', 'W'),
825 (users_table.c.name == 'jack', 'J')
826 )
828 In the second form, it accepts a Python dictionary of comparison
829 values mapped to a resulting value; this form requires
830 :paramref:`.case.value` to be present, and values will be compared
831 using the ``==`` operator, e.g.::
833 case(
834 {"wendy": "W", "jack": "J"},
835 value=users_table.c.name
836 )
838 :param value: An optional SQL expression which will be used as a
839 fixed "comparison point" for candidate values within a dictionary
840 passed to :paramref:`.case.whens`.
842 :param else\_: An optional SQL expression which will be the evaluated
843 result of the ``CASE`` construct if all expressions within
844 :paramref:`.case.whens` evaluate to false. When omitted, most
845 databases will produce a result of NULL if none of the "when"
846 expressions evaluate to true.
849 """
850 return Case(*whens, value=value, else_=else_)
853def cast(
854 expression: _ColumnExpressionOrLiteralArgument[Any],
855 type_: _TypeEngineArgument[_T],
856) -> Cast[_T]:
857 r"""Produce a ``CAST`` expression.
859 :func:`.cast` returns an instance of :class:`.Cast`.
861 E.g.::
863 from sqlalchemy import cast, Numeric
865 stmt = select(cast(product_table.c.unit_price, Numeric(10, 4)))
867 The above statement will produce SQL resembling::
869 SELECT CAST(unit_price AS NUMERIC(10, 4)) FROM product
871 The :func:`.cast` function performs two distinct functions when
872 used. The first is that it renders the ``CAST`` expression within
873 the resulting SQL string. The second is that it associates the given
874 type (e.g. :class:`.TypeEngine` class or instance) with the column
875 expression on the Python side, which means the expression will take
876 on the expression operator behavior associated with that type,
877 as well as the bound-value handling and result-row-handling behavior
878 of the type.
880 An alternative to :func:`.cast` is the :func:`.type_coerce` function.
881 This function performs the second task of associating an expression
882 with a specific type, but does not render the ``CAST`` expression
883 in SQL.
885 :param expression: A SQL expression, such as a
886 :class:`_expression.ColumnElement`
887 expression or a Python string which will be coerced into a bound
888 literal value.
890 :param type\_: A :class:`.TypeEngine` class or instance indicating
891 the type to which the ``CAST`` should apply.
893 .. seealso::
895 :ref:`tutorial_casts`
897 :func:`.try_cast` - an alternative to CAST that results in
898 NULLs when the cast fails, instead of raising an error.
899 Only supported by some dialects.
901 :func:`.type_coerce` - an alternative to CAST that coerces the type
902 on the Python side only, which is often sufficient to generate the
903 correct SQL and data coercion.
906 """
907 return Cast(expression, type_)
910def try_cast(
911 expression: _ColumnExpressionOrLiteralArgument[Any],
912 type_: _TypeEngineArgument[_T],
913) -> TryCast[_T]:
914 """Produce a ``TRY_CAST`` expression for backends which support it;
915 this is a ``CAST`` which returns NULL for un-castable conversions.
917 In SQLAlchemy, this construct is supported **only** by the SQL Server
918 dialect, and will raise a :class:`.CompileError` if used on other
919 included backends. However, third party backends may also support
920 this construct.
922 .. tip:: As :func:`_sql.try_cast` originates from the SQL Server dialect,
923 it's importable both from ``sqlalchemy.`` as well as from
924 ``sqlalchemy.dialects.mssql``.
926 :func:`_sql.try_cast` returns an instance of :class:`.TryCast` and
927 generally behaves similarly to the :class:`.Cast` construct;
928 at the SQL level, the difference between ``CAST`` and ``TRY_CAST``
929 is that ``TRY_CAST`` returns NULL for an un-castable expression,
930 such as attempting to cast a string ``"hi"`` to an integer value.
932 E.g.::
934 from sqlalchemy import select, try_cast, Numeric
936 stmt = select(
937 try_cast(product_table.c.unit_price, Numeric(10, 4))
938 )
940 The above would render on Microsoft SQL Server as::
942 SELECT TRY_CAST (product_table.unit_price AS NUMERIC(10, 4))
943 FROM product_table
945 .. versionadded:: 2.0.14 :func:`.try_cast` has been
946 generalized from the SQL Server dialect into a general use
947 construct that may be supported by additional dialects.
949 """
950 return TryCast(expression, type_)
953def column(
954 text: str,
955 type_: Optional[_TypeEngineArgument[_T]] = None,
956 is_literal: bool = False,
957 _selectable: Optional[FromClause] = None,
958) -> ColumnClause[_T]:
959 """Produce a :class:`.ColumnClause` object.
961 The :class:`.ColumnClause` is a lightweight analogue to the
962 :class:`_schema.Column` class. The :func:`_expression.column`
963 function can
964 be invoked with just a name alone, as in::
966 from sqlalchemy import column
968 id, name = column("id"), column("name")
969 stmt = select(id, name).select_from("user")
971 The above statement would produce SQL like::
973 SELECT id, name FROM user
975 Once constructed, :func:`_expression.column`
976 may be used like any other SQL
977 expression element such as within :func:`_expression.select`
978 constructs::
980 from sqlalchemy.sql import column
982 id, name = column("id"), column("name")
983 stmt = select(id, name).select_from("user")
985 The text handled by :func:`_expression.column`
986 is assumed to be handled
987 like the name of a database column; if the string contains mixed case,
988 special characters, or matches a known reserved word on the target
989 backend, the column expression will render using the quoting
990 behavior determined by the backend. To produce a textual SQL
991 expression that is rendered exactly without any quoting,
992 use :func:`_expression.literal_column` instead,
993 or pass ``True`` as the
994 value of :paramref:`_expression.column.is_literal`. Additionally,
995 full SQL
996 statements are best handled using the :func:`_expression.text`
997 construct.
999 :func:`_expression.column` can be used in a table-like
1000 fashion by combining it with the :func:`.table` function
1001 (which is the lightweight analogue to :class:`_schema.Table`
1002 ) to produce
1003 a working table construct with minimal boilerplate::
1005 from sqlalchemy import table, column, select
1007 user = table("user",
1008 column("id"),
1009 column("name"),
1010 column("description"),
1011 )
1013 stmt = select(user.c.description).where(user.c.name == 'wendy')
1015 A :func:`_expression.column` / :func:`.table`
1016 construct like that illustrated
1017 above can be created in an
1018 ad-hoc fashion and is not associated with any
1019 :class:`_schema.MetaData`, DDL, or events, unlike its
1020 :class:`_schema.Table` counterpart.
1022 :param text: the text of the element.
1024 :param type: :class:`_types.TypeEngine` object which can associate
1025 this :class:`.ColumnClause` with a type.
1027 :param is_literal: if True, the :class:`.ColumnClause` is assumed to
1028 be an exact expression that will be delivered to the output with no
1029 quoting rules applied regardless of case sensitive settings. the
1030 :func:`_expression.literal_column()` function essentially invokes
1031 :func:`_expression.column` while passing ``is_literal=True``.
1033 .. seealso::
1035 :class:`_schema.Column`
1037 :func:`_expression.literal_column`
1039 :func:`.table`
1041 :func:`_expression.text`
1043 :ref:`tutorial_select_arbitrary_text`
1045 """
1046 return ColumnClause(text, type_, is_literal, _selectable)
1049def desc(
1050 column: _ColumnExpressionOrStrLabelArgument[_T],
1051) -> UnaryExpression[_T]:
1052 """Produce a descending ``ORDER BY`` clause element.
1054 e.g.::
1056 from sqlalchemy import desc
1058 stmt = select(users_table).order_by(desc(users_table.c.name))
1060 will produce SQL as::
1062 SELECT id, name FROM user ORDER BY name DESC
1064 The :func:`.desc` function is a standalone version of the
1065 :meth:`_expression.ColumnElement.desc`
1066 method available on all SQL expressions,
1067 e.g.::
1070 stmt = select(users_table).order_by(users_table.c.name.desc())
1072 :param column: A :class:`_expression.ColumnElement` (e.g.
1073 scalar SQL expression)
1074 with which to apply the :func:`.desc` operation.
1076 .. seealso::
1078 :func:`.asc`
1080 :func:`.nulls_first`
1082 :func:`.nulls_last`
1084 :meth:`_expression.Select.order_by`
1086 """
1087 return UnaryExpression._create_desc(column)
1090def distinct(expr: _ColumnExpressionArgument[_T]) -> UnaryExpression[_T]:
1091 """Produce an column-expression-level unary ``DISTINCT`` clause.
1093 This applies the ``DISTINCT`` keyword to an **individual column
1094 expression** (e.g. not the whole statement), and renders **specifically
1095 in that column position**; this is used for containment within
1096 an aggregate function, as in::
1098 from sqlalchemy import distinct, func
1099 stmt = select(users_table.c.id, func.count(distinct(users_table.c.name)))
1101 The above would produce an statement resembling::
1103 SELECT user.id, count(DISTINCT user.name) FROM user
1105 .. tip:: The :func:`_sql.distinct` function does **not** apply DISTINCT
1106 to the full SELECT statement, instead applying a DISTINCT modifier
1107 to **individual column expressions**. For general ``SELECT DISTINCT``
1108 support, use the
1109 :meth:`_sql.Select.distinct` method on :class:`_sql.Select`.
1111 The :func:`.distinct` function is also available as a column-level
1112 method, e.g. :meth:`_expression.ColumnElement.distinct`, as in::
1114 stmt = select(func.count(users_table.c.name.distinct()))
1116 The :func:`.distinct` operator is different from the
1117 :meth:`_expression.Select.distinct` method of
1118 :class:`_expression.Select`,
1119 which produces a ``SELECT`` statement
1120 with ``DISTINCT`` applied to the result set as a whole,
1121 e.g. a ``SELECT DISTINCT`` expression. See that method for further
1122 information.
1124 .. seealso::
1126 :meth:`_expression.ColumnElement.distinct`
1128 :meth:`_expression.Select.distinct`
1130 :data:`.func`
1132 """ # noqa: E501
1133 return UnaryExpression._create_distinct(expr)
1136def bitwise_not(expr: _ColumnExpressionArgument[_T]) -> UnaryExpression[_T]:
1137 """Produce a unary bitwise NOT clause, typically via the ``~`` operator.
1139 Not to be confused with boolean negation :func:`_sql.not_`.
1141 .. versionadded:: 2.0.2
1143 .. seealso::
1145 :ref:`operators_bitwise`
1148 """
1150 return UnaryExpression._create_bitwise_not(expr)
1153def extract(field: str, expr: _ColumnExpressionArgument[Any]) -> Extract:
1154 """Return a :class:`.Extract` construct.
1156 This is typically available as :func:`.extract`
1157 as well as ``func.extract`` from the
1158 :data:`.func` namespace.
1160 :param field: The field to extract.
1162 :param expr: A column or Python scalar expression serving as the
1163 right side of the ``EXTRACT`` expression.
1165 E.g.::
1167 from sqlalchemy import extract
1168 from sqlalchemy import table, column
1170 logged_table = table("user",
1171 column("id"),
1172 column("date_created"),
1173 )
1175 stmt = select(logged_table.c.id).where(
1176 extract("YEAR", logged_table.c.date_created) == 2021
1177 )
1179 In the above example, the statement is used to select ids from the
1180 database where the ``YEAR`` component matches a specific value.
1182 Similarly, one can also select an extracted component::
1184 stmt = select(
1185 extract("YEAR", logged_table.c.date_created)
1186 ).where(logged_table.c.id == 1)
1188 The implementation of ``EXTRACT`` may vary across database backends.
1189 Users are reminded to consult their database documentation.
1190 """
1191 return Extract(field, expr)
1194def false() -> False_:
1195 """Return a :class:`.False_` construct.
1197 E.g.:
1199 .. sourcecode:: pycon+sql
1201 >>> from sqlalchemy import false
1202 >>> print(select(t.c.x).where(false()))
1203 {printsql}SELECT x FROM t WHERE false
1205 A backend which does not support true/false constants will render as
1206 an expression against 1 or 0:
1208 .. sourcecode:: pycon+sql
1210 >>> print(select(t.c.x).where(false()))
1211 {printsql}SELECT x FROM t WHERE 0 = 1
1213 The :func:`.true` and :func:`.false` constants also feature
1214 "short circuit" operation within an :func:`.and_` or :func:`.or_`
1215 conjunction:
1217 .. sourcecode:: pycon+sql
1219 >>> print(select(t.c.x).where(or_(t.c.x > 5, true())))
1220 {printsql}SELECT x FROM t WHERE true{stop}
1222 >>> print(select(t.c.x).where(and_(t.c.x > 5, false())))
1223 {printsql}SELECT x FROM t WHERE false{stop}
1225 .. seealso::
1227 :func:`.true`
1229 """
1231 return False_._instance()
1234def funcfilter(
1235 func: FunctionElement[_T], *criterion: _ColumnExpressionArgument[bool]
1236) -> FunctionFilter[_T]:
1237 """Produce a :class:`.FunctionFilter` object against a function.
1239 Used against aggregate and window functions,
1240 for database backends that support the "FILTER" clause.
1242 E.g.::
1244 from sqlalchemy import funcfilter
1245 funcfilter(func.count(1), MyClass.name == 'some name')
1247 Would produce "COUNT(1) FILTER (WHERE myclass.name = 'some name')".
1249 This function is also available from the :data:`~.expression.func`
1250 construct itself via the :meth:`.FunctionElement.filter` method.
1252 .. seealso::
1254 :ref:`tutorial_functions_within_group` - in the
1255 :ref:`unified_tutorial`
1257 :meth:`.FunctionElement.filter`
1259 """
1260 return FunctionFilter(func, *criterion)
1263def label(
1264 name: str,
1265 element: _ColumnExpressionArgument[_T],
1266 type_: Optional[_TypeEngineArgument[_T]] = None,
1267) -> Label[_T]:
1268 """Return a :class:`Label` object for the
1269 given :class:`_expression.ColumnElement`.
1271 A label changes the name of an element in the columns clause of a
1272 ``SELECT`` statement, typically via the ``AS`` SQL keyword.
1274 This functionality is more conveniently available via the
1275 :meth:`_expression.ColumnElement.label` method on
1276 :class:`_expression.ColumnElement`.
1278 :param name: label name
1280 :param obj: a :class:`_expression.ColumnElement`.
1282 """
1283 return Label(name, element, type_)
1286def null() -> Null:
1287 """Return a constant :class:`.Null` construct."""
1289 return Null._instance()
1292def nulls_first(column: _ColumnExpressionArgument[_T]) -> UnaryExpression[_T]:
1293 """Produce the ``NULLS FIRST`` modifier for an ``ORDER BY`` expression.
1295 :func:`.nulls_first` is intended to modify the expression produced
1296 by :func:`.asc` or :func:`.desc`, and indicates how NULL values
1297 should be handled when they are encountered during ordering::
1300 from sqlalchemy import desc, nulls_first
1302 stmt = select(users_table).order_by(
1303 nulls_first(desc(users_table.c.name)))
1305 The SQL expression from the above would resemble::
1307 SELECT id, name FROM user ORDER BY name DESC NULLS FIRST
1309 Like :func:`.asc` and :func:`.desc`, :func:`.nulls_first` is typically
1310 invoked from the column expression itself using
1311 :meth:`_expression.ColumnElement.nulls_first`,
1312 rather than as its standalone
1313 function version, as in::
1315 stmt = select(users_table).order_by(
1316 users_table.c.name.desc().nulls_first())
1318 .. versionchanged:: 1.4 :func:`.nulls_first` is renamed from
1319 :func:`.nullsfirst` in previous releases.
1320 The previous name remains available for backwards compatibility.
1322 .. seealso::
1324 :func:`.asc`
1326 :func:`.desc`
1328 :func:`.nulls_last`
1330 :meth:`_expression.Select.order_by`
1332 """
1333 return UnaryExpression._create_nulls_first(column)
1336def nulls_last(column: _ColumnExpressionArgument[_T]) -> UnaryExpression[_T]:
1337 """Produce the ``NULLS LAST`` modifier for an ``ORDER BY`` expression.
1339 :func:`.nulls_last` is intended to modify the expression produced
1340 by :func:`.asc` or :func:`.desc`, and indicates how NULL values
1341 should be handled when they are encountered during ordering::
1344 from sqlalchemy import desc, nulls_last
1346 stmt = select(users_table).order_by(
1347 nulls_last(desc(users_table.c.name)))
1349 The SQL expression from the above would resemble::
1351 SELECT id, name FROM user ORDER BY name DESC NULLS LAST
1353 Like :func:`.asc` and :func:`.desc`, :func:`.nulls_last` is typically
1354 invoked from the column expression itself using
1355 :meth:`_expression.ColumnElement.nulls_last`,
1356 rather than as its standalone
1357 function version, as in::
1359 stmt = select(users_table).order_by(
1360 users_table.c.name.desc().nulls_last())
1362 .. versionchanged:: 1.4 :func:`.nulls_last` is renamed from
1363 :func:`.nullslast` in previous releases.
1364 The previous name remains available for backwards compatibility.
1366 .. seealso::
1368 :func:`.asc`
1370 :func:`.desc`
1372 :func:`.nulls_first`
1374 :meth:`_expression.Select.order_by`
1376 """
1377 return UnaryExpression._create_nulls_last(column)
1380def or_( # type: ignore[empty-body]
1381 initial_clause: Union[Literal[False], _ColumnExpressionArgument[bool]],
1382 *clauses: _ColumnExpressionArgument[bool],
1383) -> ColumnElement[bool]:
1384 """Produce a conjunction of expressions joined by ``OR``.
1386 E.g.::
1388 from sqlalchemy import or_
1390 stmt = select(users_table).where(
1391 or_(
1392 users_table.c.name == 'wendy',
1393 users_table.c.name == 'jack'
1394 )
1395 )
1397 The :func:`.or_` conjunction is also available using the
1398 Python ``|`` operator (though note that compound expressions
1399 need to be parenthesized in order to function with Python
1400 operator precedence behavior)::
1402 stmt = select(users_table).where(
1403 (users_table.c.name == 'wendy') |
1404 (users_table.c.name == 'jack')
1405 )
1407 The :func:`.or_` construct must be given at least one positional
1408 argument in order to be valid; a :func:`.or_` construct with no
1409 arguments is ambiguous. To produce an "empty" or dynamically
1410 generated :func:`.or_` expression, from a given list of expressions,
1411 a "default" element of :func:`_sql.false` (or just ``False``) should be
1412 specified::
1414 from sqlalchemy import false
1415 or_criteria = or_(false(), *expressions)
1417 The above expression will compile to SQL as the expression ``false``
1418 or ``0 = 1``, depending on backend, if no other expressions are
1419 present. If expressions are present, then the :func:`_sql.false` value is
1420 ignored as it does not affect the outcome of an OR expression which
1421 has other elements.
1423 .. deprecated:: 1.4 The :func:`.or_` element now requires that at
1424 least one argument is passed; creating the :func:`.or_` construct
1425 with no arguments is deprecated, and will emit a deprecation warning
1426 while continuing to produce a blank SQL string.
1428 .. seealso::
1430 :func:`.and_`
1432 """
1433 ...
1436if not TYPE_CHECKING:
1437 # handle deprecated case which allows zero-arguments
1438 def or_(*clauses): # noqa: F811
1439 """Produce a conjunction of expressions joined by ``OR``.
1441 E.g.::
1443 from sqlalchemy import or_
1445 stmt = select(users_table).where(
1446 or_(
1447 users_table.c.name == 'wendy',
1448 users_table.c.name == 'jack'
1449 )
1450 )
1452 The :func:`.or_` conjunction is also available using the
1453 Python ``|`` operator (though note that compound expressions
1454 need to be parenthesized in order to function with Python
1455 operator precedence behavior)::
1457 stmt = select(users_table).where(
1458 (users_table.c.name == 'wendy') |
1459 (users_table.c.name == 'jack')
1460 )
1462 The :func:`.or_` construct must be given at least one positional
1463 argument in order to be valid; a :func:`.or_` construct with no
1464 arguments is ambiguous. To produce an "empty" or dynamically
1465 generated :func:`.or_` expression, from a given list of expressions,
1466 a "default" element of :func:`_sql.false` (or just ``False``) should be
1467 specified::
1469 from sqlalchemy import false
1470 or_criteria = or_(false(), *expressions)
1472 The above expression will compile to SQL as the expression ``false``
1473 or ``0 = 1``, depending on backend, if no other expressions are
1474 present. If expressions are present, then the :func:`_sql.false` value
1475 is ignored as it does not affect the outcome of an OR expression which
1476 has other elements.
1478 .. deprecated:: 1.4 The :func:`.or_` element now requires that at
1479 least one argument is passed; creating the :func:`.or_` construct
1480 with no arguments is deprecated, and will emit a deprecation warning
1481 while continuing to produce a blank SQL string.
1483 .. seealso::
1485 :func:`.and_`
1487 """
1488 return BooleanClauseList.or_(*clauses)
1491def over(
1492 element: FunctionElement[_T],
1493 partition_by: Optional[_ByArgument] = None,
1494 order_by: Optional[_ByArgument] = None,
1495 range_: Optional[typing_Tuple[Optional[int], Optional[int]]] = None,
1496 rows: Optional[typing_Tuple[Optional[int], Optional[int]]] = None,
1497) -> Over[_T]:
1498 r"""Produce an :class:`.Over` object against a function.
1500 Used against aggregate or so-called "window" functions,
1501 for database backends that support window functions.
1503 :func:`_expression.over` is usually called using
1504 the :meth:`.FunctionElement.over` method, e.g.::
1506 func.row_number().over(order_by=mytable.c.some_column)
1508 Would produce::
1510 ROW_NUMBER() OVER(ORDER BY some_column)
1512 Ranges are also possible using the :paramref:`.expression.over.range_`
1513 and :paramref:`.expression.over.rows` parameters. These
1514 mutually-exclusive parameters each accept a 2-tuple, which contains
1515 a combination of integers and None::
1517 func.row_number().over(
1518 order_by=my_table.c.some_column, range_=(None, 0))
1520 The above would produce::
1522 ROW_NUMBER() OVER(ORDER BY some_column
1523 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
1525 A value of ``None`` indicates "unbounded", a
1526 value of zero indicates "current row", and negative / positive
1527 integers indicate "preceding" and "following":
1529 * RANGE BETWEEN 5 PRECEDING AND 10 FOLLOWING::
1531 func.row_number().over(order_by='x', range_=(-5, 10))
1533 * ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW::
1535 func.row_number().over(order_by='x', rows=(None, 0))
1537 * RANGE BETWEEN 2 PRECEDING AND UNBOUNDED FOLLOWING::
1539 func.row_number().over(order_by='x', range_=(-2, None))
1541 * RANGE BETWEEN 1 FOLLOWING AND 3 FOLLOWING::
1543 func.row_number().over(order_by='x', range_=(1, 3))
1545 :param element: a :class:`.FunctionElement`, :class:`.WithinGroup`,
1546 or other compatible construct.
1547 :param partition_by: a column element or string, or a list
1548 of such, that will be used as the PARTITION BY clause
1549 of the OVER construct.
1550 :param order_by: a column element or string, or a list
1551 of such, that will be used as the ORDER BY clause
1552 of the OVER construct.
1553 :param range\_: optional range clause for the window. This is a
1554 tuple value which can contain integer values or ``None``,
1555 and will render a RANGE BETWEEN PRECEDING / FOLLOWING clause.
1557 :param rows: optional rows clause for the window. This is a tuple
1558 value which can contain integer values or None, and will render
1559 a ROWS BETWEEN PRECEDING / FOLLOWING clause.
1561 This function is also available from the :data:`~.expression.func`
1562 construct itself via the :meth:`.FunctionElement.over` method.
1564 .. seealso::
1566 :ref:`tutorial_window_functions` - in the :ref:`unified_tutorial`
1568 :data:`.expression.func`
1570 :func:`_expression.within_group`
1572 """
1573 return Over(element, partition_by, order_by, range_, rows)
1576@_document_text_coercion("text", ":func:`.text`", ":paramref:`.text.text`")
1577def text(text: str) -> TextClause:
1578 r"""Construct a new :class:`_expression.TextClause` clause,
1579 representing
1580 a textual SQL string directly.
1582 E.g.::
1584 from sqlalchemy import text
1586 t = text("SELECT * FROM users")
1587 result = connection.execute(t)
1589 The advantages :func:`_expression.text`
1590 provides over a plain string are
1591 backend-neutral support for bind parameters, per-statement
1592 execution options, as well as
1593 bind parameter and result-column typing behavior, allowing
1594 SQLAlchemy type constructs to play a role when executing
1595 a statement that is specified literally. The construct can also
1596 be provided with a ``.c`` collection of column elements, allowing
1597 it to be embedded in other SQL expression constructs as a subquery.
1599 Bind parameters are specified by name, using the format ``:name``.
1600 E.g.::
1602 t = text("SELECT * FROM users WHERE id=:user_id")
1603 result = connection.execute(t, {"user_id": 12})
1605 For SQL statements where a colon is required verbatim, as within
1606 an inline string, use a backslash to escape::
1608 t = text(r"SELECT * FROM users WHERE name='\:username'")
1610 The :class:`_expression.TextClause`
1611 construct includes methods which can
1612 provide information about the bound parameters as well as the column
1613 values which would be returned from the textual statement, assuming
1614 it's an executable SELECT type of statement. The
1615 :meth:`_expression.TextClause.bindparams`
1616 method is used to provide bound
1617 parameter detail, and :meth:`_expression.TextClause.columns`
1618 method allows
1619 specification of return columns including names and types::
1621 t = text("SELECT * FROM users WHERE id=:user_id").\
1622 bindparams(user_id=7).\
1623 columns(id=Integer, name=String)
1625 for id, name in connection.execute(t):
1626 print(id, name)
1628 The :func:`_expression.text` construct is used in cases when
1629 a literal string SQL fragment is specified as part of a larger query,
1630 such as for the WHERE clause of a SELECT statement::
1632 s = select(users.c.id, users.c.name).where(text("id=:user_id"))
1633 result = connection.execute(s, {"user_id": 12})
1635 :func:`_expression.text` is also used for the construction
1636 of a full, standalone statement using plain text.
1637 As such, SQLAlchemy refers
1638 to it as an :class:`.Executable` object and may be used
1639 like any other statement passed to an ``.execute()`` method.
1641 :param text:
1642 the text of the SQL statement to be created. Use ``:<param>``
1643 to specify bind parameters; they will be compiled to their
1644 engine-specific format.
1646 .. seealso::
1648 :ref:`tutorial_select_arbitrary_text`
1650 """
1651 return TextClause(text)
1654def true() -> True_:
1655 """Return a constant :class:`.True_` construct.
1657 E.g.:
1659 .. sourcecode:: pycon+sql
1661 >>> from sqlalchemy import true
1662 >>> print(select(t.c.x).where(true()))
1663 {printsql}SELECT x FROM t WHERE true
1665 A backend which does not support true/false constants will render as
1666 an expression against 1 or 0:
1668 .. sourcecode:: pycon+sql
1670 >>> print(select(t.c.x).where(true()))
1671 {printsql}SELECT x FROM t WHERE 1 = 1
1673 The :func:`.true` and :func:`.false` constants also feature
1674 "short circuit" operation within an :func:`.and_` or :func:`.or_`
1675 conjunction:
1677 .. sourcecode:: pycon+sql
1679 >>> print(select(t.c.x).where(or_(t.c.x > 5, true())))
1680 {printsql}SELECT x FROM t WHERE true{stop}
1682 >>> print(select(t.c.x).where(and_(t.c.x > 5, false())))
1683 {printsql}SELECT x FROM t WHERE false{stop}
1685 .. seealso::
1687 :func:`.false`
1689 """
1691 return True_._instance()
1694def tuple_(
1695 *clauses: _ColumnExpressionArgument[Any],
1696 types: Optional[Sequence[_TypeEngineArgument[Any]]] = None,
1697) -> Tuple:
1698 """Return a :class:`.Tuple`.
1700 Main usage is to produce a composite IN construct using
1701 :meth:`.ColumnOperators.in_` ::
1703 from sqlalchemy import tuple_
1705 tuple_(table.c.col1, table.c.col2).in_(
1706 [(1, 2), (5, 12), (10, 19)]
1707 )
1709 .. versionchanged:: 1.3.6 Added support for SQLite IN tuples.
1711 .. warning::
1713 The composite IN construct is not supported by all backends, and is
1714 currently known to work on PostgreSQL, MySQL, and SQLite.
1715 Unsupported backends will raise a subclass of
1716 :class:`~sqlalchemy.exc.DBAPIError` when such an expression is
1717 invoked.
1719 """
1720 return Tuple(*clauses, types=types)
1723def type_coerce(
1724 expression: _ColumnExpressionOrLiteralArgument[Any],
1725 type_: _TypeEngineArgument[_T],
1726) -> TypeCoerce[_T]:
1727 r"""Associate a SQL expression with a particular type, without rendering
1728 ``CAST``.
1730 E.g.::
1732 from sqlalchemy import type_coerce
1734 stmt = select(type_coerce(log_table.date_string, StringDateTime()))
1736 The above construct will produce a :class:`.TypeCoerce` object, which
1737 does not modify the rendering in any way on the SQL side, with the
1738 possible exception of a generated label if used in a columns clause
1739 context:
1741 .. sourcecode:: sql
1743 SELECT date_string AS date_string FROM log
1745 When result rows are fetched, the ``StringDateTime`` type processor
1746 will be applied to result rows on behalf of the ``date_string`` column.
1748 .. note:: the :func:`.type_coerce` construct does not render any
1749 SQL syntax of its own, including that it does not imply
1750 parenthesization. Please use :meth:`.TypeCoerce.self_group`
1751 if explicit parenthesization is required.
1753 In order to provide a named label for the expression, use
1754 :meth:`_expression.ColumnElement.label`::
1756 stmt = select(
1757 type_coerce(log_table.date_string, StringDateTime()).label('date')
1758 )
1761 A type that features bound-value handling will also have that behavior
1762 take effect when literal values or :func:`.bindparam` constructs are
1763 passed to :func:`.type_coerce` as targets.
1764 For example, if a type implements the
1765 :meth:`.TypeEngine.bind_expression`
1766 method or :meth:`.TypeEngine.bind_processor` method or equivalent,
1767 these functions will take effect at statement compilation/execution
1768 time when a literal value is passed, as in::
1770 # bound-value handling of MyStringType will be applied to the
1771 # literal value "some string"
1772 stmt = select(type_coerce("some string", MyStringType))
1774 When using :func:`.type_coerce` with composed expressions, note that
1775 **parenthesis are not applied**. If :func:`.type_coerce` is being
1776 used in an operator context where the parenthesis normally present from
1777 CAST are necessary, use the :meth:`.TypeCoerce.self_group` method:
1779 .. sourcecode:: pycon+sql
1781 >>> some_integer = column("someint", Integer)
1782 >>> some_string = column("somestr", String)
1783 >>> expr = type_coerce(some_integer + 5, String) + some_string
1784 >>> print(expr)
1785 {printsql}someint + :someint_1 || somestr{stop}
1786 >>> expr = type_coerce(some_integer + 5, String).self_group() + some_string
1787 >>> print(expr)
1788 {printsql}(someint + :someint_1) || somestr{stop}
1790 :param expression: A SQL expression, such as a
1791 :class:`_expression.ColumnElement`
1792 expression or a Python string which will be coerced into a bound
1793 literal value.
1795 :param type\_: A :class:`.TypeEngine` class or instance indicating
1796 the type to which the expression is coerced.
1798 .. seealso::
1800 :ref:`tutorial_casts`
1802 :func:`.cast`
1804 """ # noqa
1805 return TypeCoerce(expression, type_)
1808def within_group(
1809 element: FunctionElement[_T], *order_by: _ColumnExpressionArgument[Any]
1810) -> WithinGroup[_T]:
1811 r"""Produce a :class:`.WithinGroup` object against a function.
1813 Used against so-called "ordered set aggregate" and "hypothetical
1814 set aggregate" functions, including :class:`.percentile_cont`,
1815 :class:`.rank`, :class:`.dense_rank`, etc.
1817 :func:`_expression.within_group` is usually called using
1818 the :meth:`.FunctionElement.within_group` method, e.g.::
1820 from sqlalchemy import within_group
1821 stmt = select(
1822 department.c.id,
1823 func.percentile_cont(0.5).within_group(
1824 department.c.salary.desc()
1825 )
1826 )
1828 The above statement would produce SQL similar to
1829 ``SELECT department.id, percentile_cont(0.5)
1830 WITHIN GROUP (ORDER BY department.salary DESC)``.
1832 :param element: a :class:`.FunctionElement` construct, typically
1833 generated by :data:`~.expression.func`.
1834 :param \*order_by: one or more column elements that will be used
1835 as the ORDER BY clause of the WITHIN GROUP construct.
1837 .. seealso::
1839 :ref:`tutorial_functions_within_group` - in the
1840 :ref:`unified_tutorial`
1842 :data:`.expression.func`
1844 :func:`_expression.over`
1846 """
1847 return WithinGroup(element, *order_by)