codekingpro/portable-devtools
115k
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
7
8from __future__ import annotations
9
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
21
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
49
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
59
60_T = TypeVar("_T")
61
62
63def all_(expr: _ColumnExpressionArgument[_T]) -> CollectionAggregate[bool]:
64 """Produce an ALL expression.
65
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.::
69
70 # renders on PostgreSQL:
71 # '5 = ALL (somearray)'
72 expr = 5 == all_(mytable.c.somearray)
73
74 # renders on MySQL:
75 # '5 = ALL (SELECT value FROM table)'
76 expr = 5 == all_(select(table.c.value))
77
78 Comparison to NULL may work using ``None``::
79
80 None == all_(mytable.c.somearray)
81
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::
87
88 # would render '5 = ALL (column)`
89 all_(mytable.c.column) == 5
90
91 Or with ``None``, which note will not perform
92 the usual step of rendering "IS" as is normally the case for NULL::
93
94 # would render 'NULL = ALL(somearray)'
95 all_(mytable.c.somearray) == None
96
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.
99
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)``::
104
105 5 == mytable.c.somearray.all_()
106
107 .. seealso::
108
109 :meth:`_sql.ColumnOperators.all_`
110
111 :func:`_expression.any_`
112
113 """
114 return CollectionAggregate._create_all(expr)
115
116
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``.
122
123 E.g.::
124
125 from sqlalchemy import and_
126
127 stmt = select(users_table).where(
128 and_(users_table.c.name == "wendy", users_table.c.enrolled == True)
129 )
130
131 The :func:`.and_` conjunction is also available using the
132 Python ``&`` operator (though note that compound expressions
133 need to be parenthesized in order to function with Python
134 operator precedence behavior)::
135
136 stmt = select(users_table).where(
137 (users_table.c.name == "wendy") & (users_table.c.enrolled == True)
138 )
139
140 The :func:`.and_` operation is also implicit in some cases;
141 the :meth:`_expression.Select.where`
142 method for example can be invoked multiple
143 times against a statement, which will have the effect of each
144 clause being combined using :func:`.and_`::
145
146 stmt = (
147 select(users_table)
148 .where(users_table.c.name == "wendy")
149 .where(users_table.c.enrolled == True)
150 )
151
152 The :func:`.and_` construct must be given at least one positional
153 argument in order to be valid; a :func:`.and_` construct with no
154 arguments is ambiguous. To produce an "empty" or dynamically
155 generated :func:`.and_` expression, from a given list of expressions,
156 a "default" element of :func:`_sql.true` (or just ``True``) should be
157 specified::
158
159 from sqlalchemy import true
160
161 criteria = and_(true(), *expressions)
162
163 The above expression will compile to SQL as the expression ``true``
164 or ``1 = 1``, depending on backend, if no other expressions are
165 present. If expressions are present, then the :func:`_sql.true` value is
166 ignored as it does not affect the outcome of an AND expression that
167 has other elements.
168
169 .. deprecated:: 1.4 The :func:`.and_` element now requires that at
170 least one argument is passed; creating the :func:`.and_` construct
171 with no arguments is deprecated, and will emit a deprecation warning
172 while continuing to produce a blank SQL string.
173
174 .. seealso::
175
176 :func:`.or_`
177
178 """
179 ...
180
181
182if not TYPE_CHECKING:
183 # handle deprecated case which allows zero-arguments
184 def and_(*clauses): # noqa: F811
185 r"""Produce a conjunction of expressions joined by ``AND``.
186
187 E.g.::
188
189 from sqlalchemy import and_
190
191 stmt = select(users_table).where(
192 and_(users_table.c.name == "wendy", users_table.c.enrolled == True)
193 )
194
195 The :func:`.and_` conjunction is also available using the
196 Python ``&`` operator (though note that compound expressions
197 need to be parenthesized in order to function with Python
198 operator precedence behavior)::
199
200 stmt = select(users_table).where(
201 (users_table.c.name == "wendy") & (users_table.c.enrolled == True)
202 )
203
204 The :func:`.and_` operation is also implicit in some cases;
205 the :meth:`_expression.Select.where`
206 method for example can be invoked multiple
207 times against a statement, which will have the effect of each
208 clause being combined using :func:`.and_`::
209
210 stmt = (
211 select(users_table)
212 .where(users_table.c.name == "wendy")
213 .where(users_table.c.enrolled == True)
214 )
215
216 The :func:`.and_` construct must be given at least one positional
217 argument in order to be valid; a :func:`.and_` construct with no
218 arguments is ambiguous. To produce an "empty" or dynamically
219 generated :func:`.and_` expression, from a given list of expressions,
220 a "default" element of :func:`_sql.true` (or just ``True``) should be
221 specified::
222
223 from sqlalchemy import true
224
225 criteria = and_(true(), *expressions)
226
227 The above expression will compile to SQL as the expression ``true``
228 or ``1 = 1``, depending on backend, if no other expressions are
229 present. If expressions are present, then the :func:`_sql.true` value
230 is ignored as it does not affect the outcome of an AND expression that
231 has other elements.
232
233 .. deprecated:: 1.4 The :func:`.and_` element now requires that at
234 least one argument is passed; creating the :func:`.and_` construct
235 with no arguments is deprecated, and will emit a deprecation warning
236 while continuing to produce a blank SQL string.
237
238 .. seealso::
239
240 :func:`.or_`
241
242 """ # noqa: E501
243 return BooleanClauseList.and_(*clauses)
244
245
246def any_(expr: _ColumnExpressionArgument[_T]) -> CollectionAggregate[bool]:
247 """Produce an ANY expression.
248
249 For dialects such as that of PostgreSQL, this operator applies
250 to usage of the :class:`_types.ARRAY` datatype, for that of
251 MySQL, it may apply to a subquery. e.g.::
252
253 # renders on PostgreSQL:
254 # '5 = ANY (somearray)'
255 expr = 5 == any_(mytable.c.somearray)
256
257 # renders on MySQL:
258 # '5 = ANY (SELECT value FROM table)'
259 expr = 5 == any_(select(table.c.value))
260
261 Comparison to NULL may work using ``None`` or :func:`_sql.null`::
262
263 None == any_(mytable.c.somearray)
264
265 The any_() / all_() operators also feature a special "operand flipping"
266 behavior such that if any_() / all_() are used on the left side of a
267 comparison using a standalone operator such as ``==``, ``!=``, etc.
268 (not including operator methods such as
269 :meth:`_sql.ColumnOperators.is_`) the rendered expression is flipped::
270
271 # would render '5 = ANY (column)`
272 any_(mytable.c.column) == 5
273
274 Or with ``None``, which note will not perform
275 the usual step of rendering "IS" as is normally the case for NULL::
276
277 # would render 'NULL = ANY(somearray)'
278 any_(mytable.c.somearray) == None
279
280 .. versionchanged:: 1.4.26 repaired the use of any_() / all_()
281 comparing to NULL on the right side to be flipped to the left.
282
283 The column-level :meth:`_sql.ColumnElement.any_` method (not to be
284 confused with :class:`_types.ARRAY` level
285 :meth:`_types.ARRAY.Comparator.any`) is shorthand for
286 ``any_(col)``::
287
288 5 = mytable.c.somearray.any_()
289
290 .. seealso::
291
292 :meth:`_sql.ColumnOperators.any_`
293
294 :func:`_expression.all_`
295
296 """
297 return CollectionAggregate._create_any(expr)
298
299
300def asc(
301 column: _ColumnExpressionOrStrLabelArgument[_T],
302) -> UnaryExpression[_T]:
303 """Produce an ascending ``ORDER BY`` clause element.
304
305 e.g.::
306
307 from sqlalchemy import asc
308
309 stmt = select(users_table).order_by(asc(users_table.c.name))
310
311 will produce SQL as:
312
313 .. sourcecode:: sql
314
315 SELECT id, name FROM user ORDER BY name ASC
316
317 The :func:`.asc` function is a standalone version of the
318 :meth:`_expression.ColumnElement.asc`
319 method available on all SQL expressions,
320 e.g.::
321
322
323 stmt = select(users_table).order_by(users_table.c.name.asc())
324
325 :param column: A :class:`_expression.ColumnElement` (e.g.
326 scalar SQL expression)
327 with which to apply the :func:`.asc` operation.
328
329 .. seealso::
330
331 :func:`.desc`
332
333 :func:`.nulls_first`
334
335 :func:`.nulls_last`
336
337 :meth:`_expression.Select.order_by`
338
339 """
340 return UnaryExpression._create_asc(column)
341
342
343def collate(
344 expression: _ColumnExpressionArgument[str], collation: str
345) -> BinaryExpression[str]:
346 """Return the clause ``expression COLLATE collation``.
347
348 e.g.::
349
350 collate(mycolumn, "utf8_bin")
351
352 produces:
353
354 .. sourcecode:: sql
355
356 mycolumn COLLATE utf8_bin
357
358 The collation expression is also quoted if it is a case sensitive
359 identifier, e.g. contains uppercase characters.
360
361 .. versionchanged:: 1.2 quoting is automatically applied to COLLATE
362 expressions if they are case sensitive.
363
364 """
365 return CollationClause._create_collation_expression(expression, collation)
366
367
368def between(
369 expr: _ColumnExpressionOrLiteralArgument[_T],
370 lower_bound: Any,
371 upper_bound: Any,
372 symmetric: bool = False,
373) -> BinaryExpression[bool]:
374 """Produce a ``BETWEEN`` predicate clause.
375
376 E.g.::
377
378 from sqlalchemy import between
379
380 stmt = select(users_table).where(between(users_table.c.id, 5, 7))
381
382 Would produce SQL resembling:
383
384 .. sourcecode:: sql
385
386 SELECT id, name FROM user WHERE id BETWEEN :id_1 AND :id_2
387
388 The :func:`.between` function is a standalone version of the
389 :meth:`_expression.ColumnElement.between` method available on all
390 SQL expressions, as in::
391
392 stmt = select(users_table).where(users_table.c.id.between(5, 7))
393
394 All arguments passed to :func:`.between`, including the left side
395 column expression, are coerced from Python scalar values if a
396 the value is not a :class:`_expression.ColumnElement` subclass.
397 For example,
398 three fixed values can be compared as in::
399
400 print(between(5, 3, 7))
401
402 Which would produce::
403
404 :param_1 BETWEEN :param_2 AND :param_3
405
406 :param expr: a column expression, typically a
407 :class:`_expression.ColumnElement`
408 instance or alternatively a Python scalar expression to be coerced
409 into a column expression, serving as the left side of the ``BETWEEN``
410 expression.
411
412 :param lower_bound: a column or Python scalar expression serving as the
413 lower bound of the right side of the ``BETWEEN`` expression.
414
415 :param upper_bound: a column or Python scalar expression serving as the
416 upper bound of the right side of the ``BETWEEN`` expression.
417
418 :param symmetric: if True, will render " BETWEEN SYMMETRIC ". Note
419 that not all databases support this syntax.
420
421 .. seealso::
422
423 :meth:`_expression.ColumnElement.between`
424
425 """
426 col_expr = coercions.expect(roles.ExpressionElementRole, expr)
427 return col_expr.between(lower_bound, upper_bound, symmetric=symmetric)
428
429
430def outparam(
431 key: str, type_: Optional[TypeEngine[_T]] = None
432) -> BindParameter[_T]:
433 """Create an 'OUT' parameter for usage in functions (stored procedures),
434 for databases which support them.
435
436 The ``outparam`` can be used like a regular function parameter.
437 The "output" value will be available from the
438 :class:`~sqlalchemy.engine.CursorResult` object via its ``out_parameters``
439 attribute, which returns a dictionary containing the values.
440
441 """
442 return BindParameter(key, None, type_=type_, unique=False, isoutparam=True)
443
444
445@overload
446def not_(clause: BinaryExpression[_T]) -> BinaryExpression[_T]: ...
447
448
449@overload
450def not_(clause: _ColumnExpressionArgument[_T]) -> ColumnElement[_T]: ...
451
452
453def not_(clause: _ColumnExpressionArgument[_T]) -> ColumnElement[_T]:
454 """Return a negation of the given clause, i.e. ``NOT(clause)``.
455
456 The ``~`` operator is also overloaded on all
457 :class:`_expression.ColumnElement` subclasses to produce the
458 same result.
459
460 """
461
462 return coercions.expect(roles.ExpressionElementRole, clause).__invert__()
463
464
465def bindparam(
466 key: Optional[str],
467 value: Any = _NoArg.NO_ARG,
468 type_: Optional[_TypeEngineArgument[_T]] = None,
469 unique: bool = False,
470 required: Union[bool, Literal[_NoArg.NO_ARG]] = _NoArg.NO_ARG,
471 quote: Optional[bool] = None,
472 callable_: Optional[Callable[[], Any]] = None,
473 expanding: bool = False,
474 isoutparam: bool = False,
475 literal_execute: bool = False,
476) -> BindParameter[_T]:
477 r"""Produce a "bound expression".
478
479 The return value is an instance of :class:`.BindParameter`; this
480 is a :class:`_expression.ColumnElement`
481 subclass which represents a so-called
482 "placeholder" value in a SQL expression, the value of which is
483 supplied at the point at which the statement in executed against a
484 database connection.
485
486 In SQLAlchemy, the :func:`.bindparam` construct has
487 the ability to carry along the actual value that will be ultimately
488 used at expression time. In this way, it serves not just as
489 a "placeholder" for eventual population, but also as a means of
490 representing so-called "unsafe" values which should not be rendered
491 directly in a SQL statement, but rather should be passed along
492 to the :term:`DBAPI` as values which need to be correctly escaped
493 and potentially handled for type-safety.
494
495 When using :func:`.bindparam` explicitly, the use case is typically
496 one of traditional deferment of parameters; the :func:`.bindparam`
497 construct accepts a name which can then be referred to at execution
498 time::
499
500 from sqlalchemy import bindparam
501
502 stmt = select(users_table).where(
503 users_table.c.name == bindparam("username")
504 )
505
506 The above statement, when rendered, will produce SQL similar to:
507
508 .. sourcecode:: sql
509
510 SELECT id, name FROM user WHERE name = :username
511
512 In order to populate the value of ``:username`` above, the value
513 would typically be applied at execution time to a method
514 like :meth:`_engine.Connection.execute`::
515
516 result = connection.execute(stmt, {"username": "wendy"})
517
518 Explicit use of :func:`.bindparam` is also common when producing
519 UPDATE or DELETE statements that are to be invoked multiple times,
520 where the WHERE criterion of the statement is to change on each
521 invocation, such as::
522
523 stmt = (
524 users_table.update()
525 .where(user_table.c.name == bindparam("username"))
526 .values(fullname=bindparam("fullname"))
527 )
528
529 connection.execute(
530 stmt,
531 [
532 {"username": "wendy", "fullname": "Wendy Smith"},
533 {"username": "jack", "fullname": "Jack Jones"},
534 ],
535 )
536
537 SQLAlchemy's Core expression system makes wide use of
538 :func:`.bindparam` in an implicit sense. It is typical that Python
539 literal values passed to virtually all SQL expression functions are
540 coerced into fixed :func:`.bindparam` constructs. For example, given
541 a comparison operation such as::
542
543 expr = users_table.c.name == "Wendy"
544
545 The above expression will produce a :class:`.BinaryExpression`
546 construct, where the left side is the :class:`_schema.Column` object
547 representing the ``name`` column, and the right side is a
548 :class:`.BindParameter` representing the literal value::
549
550 print(repr(expr.right))
551 BindParameter("%(4327771088 name)s", "Wendy", type_=String())
552
553 The expression above will render SQL such as:
554
555 .. sourcecode:: sql
556
557 user.name = :name_1
558
559 Where the ``:name_1`` parameter name is an anonymous name. The
560 actual string ``Wendy`` is not in the rendered string, but is carried
561 along where it is later used within statement execution. If we
562 invoke a statement like the following::
563
564 stmt = select(users_table).where(users_table.c.name == "Wendy")
565 result = connection.execute(stmt)
566
567 We would see SQL logging output as:
568
569 .. sourcecode:: sql
570
571 SELECT "user".id, "user".name
572 FROM "user"
573 WHERE "user".name = %(name_1)s
574 {'name_1': 'Wendy'}
575
576 Above, we see that ``Wendy`` is passed as a parameter to the database,
577 while the placeholder ``:name_1`` is rendered in the appropriate form
578 for the target database, in this case the PostgreSQL database.
579
580 Similarly, :func:`.bindparam` is invoked automatically when working
581 with :term:`CRUD` statements as far as the "VALUES" portion is
582 concerned. The :func:`_expression.insert` construct produces an
583 ``INSERT`` expression which will, at statement execution time, generate
584 bound placeholders based on the arguments passed, as in::
585
586 stmt = users_table.insert()
587 result = connection.execute(stmt, {"name": "Wendy"})
588
589 The above will produce SQL output as:
590
591 .. sourcecode:: sql
592
593 INSERT INTO "user" (name) VALUES (%(name)s)
594 {'name': 'Wendy'}
595
596 The :class:`_expression.Insert` construct, at
597 compilation/execution time, rendered a single :func:`.bindparam`
598 mirroring the column name ``name`` as a result of the single ``name``
599 parameter we passed to the :meth:`_engine.Connection.execute` method.
600
601 :param key:
602 the key (e.g. the name) for this bind param.
603 Will be used in the generated
604 SQL statement for dialects that use named parameters. This
605 value may be modified when part of a compilation operation,
606 if other :class:`BindParameter` objects exist with the same
607 key, or if its length is too long and truncation is
608 required.
609
610 If omitted, an "anonymous" name is generated for the bound parameter;
611 when given a value to bind, the end result is equivalent to calling upon
612 the :func:`.literal` function with a value to bind, particularly
613 if the :paramref:`.bindparam.unique` parameter is also provided.
614
615 :param value:
616 Initial value for this bind param. Will be used at statement
617 execution time as the value for this parameter passed to the
618 DBAPI, if no other value is indicated to the statement execution
619 method for this particular parameter name. Defaults to ``None``.
620
621 :param callable\_:
622 A callable function that takes the place of "value". The function
623 will be called at statement execution time to determine the
624 ultimate value. Used for scenarios where the actual bind
625 value cannot be determined at the point at which the clause
626 construct is created, but embedded bind values are still desirable.
627
628 :param type\_:
629 A :class:`.TypeEngine` class or instance representing an optional
630 datatype for this :func:`.bindparam`. If not passed, a type
631 may be determined automatically for the bind, based on the given
632 value; for example, trivial Python types such as ``str``,
633 ``int``, ``bool``
634 may result in the :class:`.String`, :class:`.Integer` or
635 :class:`.Boolean` types being automatically selected.
636
637 The type of a :func:`.bindparam` is significant especially in that
638 the type will apply pre-processing to the value before it is
639 passed to the database. For example, a :func:`.bindparam` which
640 refers to a datetime value, and is specified as holding the
641 :class:`.DateTime` type, may apply conversion needed to the
642 value (such as stringification on SQLite) before passing the value
643 to the database.
644
645 :param unique:
646 if True, the key name of this :class:`.BindParameter` will be
647 modified if another :class:`.BindParameter` of the same name
648 already has been located within the containing
649 expression. This flag is used generally by the internals
650 when producing so-called "anonymous" bound expressions, it
651 isn't generally applicable to explicitly-named :func:`.bindparam`
652 constructs.
653
654 :param required:
655 If ``True``, a value is required at execution time. If not passed,
656 it defaults to ``True`` if neither :paramref:`.bindparam.value`
657 or :paramref:`.bindparam.callable` were passed. If either of these
658 parameters are present, then :paramref:`.bindparam.required`
659 defaults to ``False``.
660
661 :param quote:
662 True if this parameter name requires quoting and is not
663 currently known as a SQLAlchemy reserved word; this currently
664 only applies to the Oracle Database backends, where bound names must
665 sometimes be quoted.
666
667 :param isoutparam:
668 if True, the parameter should be treated like a stored procedure
669 "OUT" parameter. This applies to backends such as Oracle Database which
670 support OUT parameters.
671
672 :param expanding:
673 if True, this parameter will be treated as an "expanding" parameter
674 at execution time; the parameter value is expected to be a sequence,
675 rather than a scalar value, and the string SQL statement will
676 be transformed on a per-execution basis to accommodate the sequence
677 with a variable number of parameter slots passed to the DBAPI.
678 This is to allow statement caching to be used in conjunction with
679 an IN clause.
680
681 .. seealso::
682
683 :meth:`.ColumnOperators.in_`
684
685 :ref:`baked_in` - with baked queries
686
687 .. note:: The "expanding" feature does not support "executemany"-
688 style parameter sets.
689
690 .. versionadded:: 1.2
691
692 .. versionchanged:: 1.3 the "expanding" bound parameter feature now
693 supports empty lists.
694
695 :param literal_execute:
696 if True, the bound parameter will be rendered in the compile phase
697 with a special "POSTCOMPILE" token, and the SQLAlchemy compiler will
698 render the final value of the parameter into the SQL statement at
699 statement execution time, omitting the value from the parameter
700 dictionary / list passed to DBAPI ``cursor.execute()``. This
701 produces a similar effect as that of using the ``literal_binds``,
702 compilation flag, however takes place as the statement is sent to
703 the DBAPI ``cursor.execute()`` method, rather than when the statement
704 is compiled. The primary use of this
705 capability is for rendering LIMIT / OFFSET clauses for database
706 drivers that can't accommodate for bound parameters in these
707 contexts, while allowing SQL constructs to be cacheable at the
708 compilation level.
709
710 .. versionadded:: 1.4 Added "post compile" bound parameters
711
712 .. seealso::
713
714 :ref:`change_4808`.
715
716 .. seealso::
717
718 :ref:`tutorial_sending_parameters` - in the
719 :ref:`unified_tutorial`
720
721
722 """
723 return BindParameter(
724 key,
725 value,
726 type_,
727 unique,
728 required,
729 quote,
730 callable_,
731 expanding,
732 isoutparam,
733 literal_execute,
734 )
735
736
737def case(
738 *whens: Union[
739 typing_Tuple[_ColumnExpressionArgument[bool], Any], Mapping[Any, Any]
740 ],
741 value: Optional[Any] = None,
742 else_: Optional[Any] = None,
743) -> Case[Any]:
744 r"""Produce a ``CASE`` expression.
745
746 The ``CASE`` construct in SQL is a conditional object that
747 acts somewhat analogously to an "if/then" construct in other
748 languages. It returns an instance of :class:`.Case`.
749
750 :func:`.case` in its usual form is passed a series of "when"
751 constructs, that is, a list of conditions and results as tuples::
752
753 from sqlalchemy import case
754
755 stmt = select(users_table).where(
756 case(
757 (users_table.c.name == "wendy", "W"),
758 (users_table.c.name == "jack", "J"),
759 else_="E",
760 )
761 )
762
763 The above statement will produce SQL resembling:
764
765 .. sourcecode:: sql
766
767 SELECT id, name FROM user
768 WHERE CASE
769 WHEN (name = :name_1) THEN :param_1
770 WHEN (name = :name_2) THEN :param_2
771 ELSE :param_3
772 END
773
774 When simple equality expressions of several values against a single
775 parent column are needed, :func:`.case` also has a "shorthand" format
776 used via the
777 :paramref:`.case.value` parameter, which is passed a column
778 expression to be compared. In this form, the :paramref:`.case.whens`
779 parameter is passed as a dictionary containing expressions to be
780 compared against keyed to result expressions. The statement below is
781 equivalent to the preceding statement::
782
783 stmt = select(users_table).where(
784 case({"wendy": "W", "jack": "J"}, value=users_table.c.name, else_="E")
785 )
786
787 The values which are accepted as result values in
788 :paramref:`.case.whens` as well as with :paramref:`.case.else_` are
789 coerced from Python literals into :func:`.bindparam` constructs.
790 SQL expressions, e.g. :class:`_expression.ColumnElement` constructs,
791 are accepted
792 as well. To coerce a literal string expression into a constant
793 expression rendered inline, use the :func:`_expression.literal_column`
794 construct,
795 as in::
796
797 from sqlalchemy import case, literal_column
798
799 case(
800 (orderline.c.qty > 100, literal_column("'greaterthan100'")),
801 (orderline.c.qty > 10, literal_column("'greaterthan10'")),
802 else_=literal_column("'lessthan10'"),
803 )
804
805 The above will render the given constants without using bound
806 parameters for the result values (but still for the comparison
807 values), as in:
808
809 .. sourcecode:: sql
810
811 CASE
812 WHEN (orderline.qty > :qty_1) THEN 'greaterthan100'
813 WHEN (orderline.qty > :qty_2) THEN 'greaterthan10'
814 ELSE 'lessthan10'
815 END
816
817 :param \*whens: The criteria to be compared against,
818 :paramref:`.case.whens` accepts two different forms, based on
819 whether or not :paramref:`.case.value` is used.
820
821 .. versionchanged:: 1.4 the :func:`_sql.case`
822 function now accepts the series of WHEN conditions positionally
823
824 In the first form, it accepts multiple 2-tuples passed as positional
825 arguments; each 2-tuple consists of ``(<sql expression>, <value>)``,
826 where the SQL expression is a boolean expression and "value" is a
827 resulting value, e.g.::
828
829 case(
830 (users_table.c.name == "wendy", "W"),
831 (users_table.c.name == "jack", "J"),
832 )
833
834 In the second form, it accepts a Python dictionary of comparison
835 values mapped to a resulting value; this form requires
836 :paramref:`.case.value` to be present, and values will be compared
837 using the ``==`` operator, e.g.::
838
839 case({"wendy": "W", "jack": "J"}, value=users_table.c.name)
840
841 :param value: An optional SQL expression which will be used as a
842 fixed "comparison point" for candidate values within a dictionary
843 passed to :paramref:`.case.whens`.
844
845 :param else\_: An optional SQL expression which will be the evaluated
846 result of the ``CASE`` construct if all expressions within
847 :paramref:`.case.whens` evaluate to false. When omitted, most
848 databases will produce a result of NULL if none of the "when"
849 expressions evaluate to true.
850
851
852 """ # noqa: E501
853 return Case(*whens, value=value, else_=else_)
854
855
856def cast(
857 expression: _ColumnExpressionOrLiteralArgument[Any],
858 type_: _TypeEngineArgument[_T],
859) -> Cast[_T]:
860 r"""Produce a ``CAST`` expression.
861
862 :func:`.cast` returns an instance of :class:`.Cast`.
863
864 E.g.::
865
866 from sqlalchemy import cast, Numeric
867
868 stmt = select(cast(product_table.c.unit_price, Numeric(10, 4)))
869
870 The above statement will produce SQL resembling:
871
872 .. sourcecode:: sql
873
874 SELECT CAST(unit_price AS NUMERIC(10, 4)) FROM product
875
876 The :func:`.cast` function performs two distinct functions when
877 used. The first is that it renders the ``CAST`` expression within
878 the resulting SQL string. The second is that it associates the given
879 type (e.g. :class:`.TypeEngine` class or instance) with the column
880 expression on the Python side, which means the expression will take
881 on the expression operator behavior associated with that type,
882 as well as the bound-value handling and result-row-handling behavior
883 of the type.
884
885 An alternative to :func:`.cast` is the :func:`.type_coerce` function.
886 This function performs the second task of associating an expression
887 with a specific type, but does not render the ``CAST`` expression
888 in SQL.
889
890 :param expression: A SQL expression, such as a
891 :class:`_expression.ColumnElement`
892 expression or a Python string which will be coerced into a bound
893 literal value.
894
895 :param type\_: A :class:`.TypeEngine` class or instance indicating
896 the type to which the ``CAST`` should apply.
897
898 .. seealso::
899
900 :ref:`tutorial_casts`
901
902 :func:`.try_cast` - an alternative to CAST that results in
903 NULLs when the cast fails, instead of raising an error.
904 Only supported by some dialects.
905
906 :func:`.type_coerce` - an alternative to CAST that coerces the type
907 on the Python side only, which is often sufficient to generate the
908 correct SQL and data coercion.
909
910
911 """
912 return Cast(expression, type_)
913
914
915def try_cast(
916 expression: _ColumnExpressionOrLiteralArgument[Any],
917 type_: _TypeEngineArgument[_T],
918) -> TryCast[_T]:
919 """Produce a ``TRY_CAST`` expression for backends which support it;
920 this is a ``CAST`` which returns NULL for un-castable conversions.
921
922 In SQLAlchemy, this construct is supported **only** by the SQL Server
923 dialect, and will raise a :class:`.CompileError` if used on other
924 included backends. However, third party backends may also support
925 this construct.
926
927 .. tip:: As :func:`_sql.try_cast` originates from the SQL Server dialect,
928 it's importable both from ``sqlalchemy.`` as well as from
929 ``sqlalchemy.dialects.mssql``.
930
931 :func:`_sql.try_cast` returns an instance of :class:`.TryCast` and
932 generally behaves similarly to the :class:`.Cast` construct;
933 at the SQL level, the difference between ``CAST`` and ``TRY_CAST``
934 is that ``TRY_CAST`` returns NULL for an un-castable expression,
935 such as attempting to cast a string ``"hi"`` to an integer value.
936
937 E.g.::
938
939 from sqlalchemy import select, try_cast, Numeric
940
941 stmt = select(try_cast(product_table.c.unit_price, Numeric(10, 4)))
942
943 The above would render on Microsoft SQL Server as:
944
945 .. sourcecode:: sql
946
947 SELECT TRY_CAST (product_table.unit_price AS NUMERIC(10, 4))
948 FROM product_table
949
950 .. versionadded:: 2.0.14 :func:`.try_cast` has been
951 generalized from the SQL Server dialect into a general use
952 construct that may be supported by additional dialects.
953
954 """
955 return TryCast(expression, type_)
956
957
958def column(
959 text: str,
960 type_: Optional[_TypeEngineArgument[_T]] = None,
961 is_literal: bool = False,
962 _selectable: Optional[FromClause] = None,
963) -> ColumnClause[_T]:
964 """Produce a :class:`.ColumnClause` object.
965
966 The :class:`.ColumnClause` is a lightweight analogue to the
967 :class:`_schema.Column` class. The :func:`_expression.column`
968 function can
969 be invoked with just a name alone, as in::
970
971 from sqlalchemy import column
972
973 id, name = column("id"), column("name")
974 stmt = select(id, name).select_from("user")
975
976 The above statement would produce SQL like:
977
978 .. sourcecode:: sql
979
980 SELECT id, name FROM user
981
982 Once constructed, :func:`_expression.column`
983 may be used like any other SQL
984 expression element such as within :func:`_expression.select`
985 constructs::
986
987 from sqlalchemy.sql import column
988
989 id, name = column("id"), column("name")
990 stmt = select(id, name).select_from("user")
991
992 The text handled by :func:`_expression.column`
993 is assumed to be handled
994 like the name of a database column; if the string contains mixed case,
995 special characters, or matches a known reserved word on the target
996 backend, the column expression will render using the quoting
997 behavior determined by the backend. To produce a textual SQL
998 expression that is rendered exactly without any quoting,
999 use :func:`_expression.literal_column` instead,
1000 or pass ``True`` as the
1001 value of :paramref:`_expression.column.is_literal`. Additionally,
1002 full SQL
1003 statements are best handled using the :func:`_expression.text`
1004 construct.
1005
1006 :func:`_expression.column` can be used in a table-like
1007 fashion by combining it with the :func:`.table` function
1008 (which is the lightweight analogue to :class:`_schema.Table`
1009 ) to produce
1010 a working table construct with minimal boilerplate::
1011
1012 from sqlalchemy import table, column, select
1013
1014 user = table(
1015 "user",
1016 column("id"),
1017 column("name"),
1018 column("description"),
1019 )
1020
1021 stmt = select(user.c.description).where(user.c.name == "wendy")
1022
1023 A :func:`_expression.column` / :func:`.table`
1024 construct like that illustrated
1025 above can be created in an
1026 ad-hoc fashion and is not associated with any
1027 :class:`_schema.MetaData`, DDL, or events, unlike its
1028 :class:`_schema.Table` counterpart.
1029
1030 :param text: the text of the element.
1031
1032 :param type: :class:`_types.TypeEngine` object which can associate
1033 this :class:`.ColumnClause` with a type.
1034
1035 :param is_literal: if True, the :class:`.ColumnClause` is assumed to
1036 be an exact expression that will be delivered to the output with no
1037 quoting rules applied regardless of case sensitive settings. the
1038 :func:`_expression.literal_column()` function essentially invokes
1039 :func:`_expression.column` while passing ``is_literal=True``.
1040
1041 .. seealso::
1042
1043 :class:`_schema.Column`
1044
1045 :func:`_expression.literal_column`
1046
1047 :func:`.table`
1048
1049 :func:`_expression.text`
1050
1051 :ref:`tutorial_select_arbitrary_text`
1052
1053 """
1054 return ColumnClause(text, type_, is_literal, _selectable)
1055
1056
1057def desc(
1058 column: _ColumnExpressionOrStrLabelArgument[_T],
1059) -> UnaryExpression[_T]:
1060 """Produce a descending ``ORDER BY`` clause element.
1061
1062 e.g.::
1063
1064 from sqlalchemy import desc
1065
1066 stmt = select(users_table).order_by(desc(users_table.c.name))
1067
1068 will produce SQL as:
1069
1070 .. sourcecode:: sql
1071
1072 SELECT id, name FROM user ORDER BY name DESC
1073
1074 The :func:`.desc` function is a standalone version of the
1075 :meth:`_expression.ColumnElement.desc`
1076 method available on all SQL expressions,
1077 e.g.::
1078
1079
1080 stmt = select(users_table).order_by(users_table.c.name.desc())
1081
1082 :param column: A :class:`_expression.ColumnElement` (e.g.
1083 scalar SQL expression)
1084 with which to apply the :func:`.desc` operation.
1085
1086 .. seealso::
1087
1088 :func:`.asc`
1089
1090 :func:`.nulls_first`
1091
1092 :func:`.nulls_last`
1093
1094 :meth:`_expression.Select.order_by`
1095
1096 """
1097 return UnaryExpression._create_desc(column)
1098
1099
1100def distinct(expr: _ColumnExpressionArgument[_T]) -> UnaryExpression[_T]:
1101 """Produce an column-expression-level unary ``DISTINCT`` clause.
1102
1103 This applies the ``DISTINCT`` keyword to an **individual column
1104 expression** (e.g. not the whole statement), and renders **specifically
1105 in that column position**; this is used for containment within
1106 an aggregate function, as in::
1107
1108 from sqlalchemy import distinct, func
1109
1110 stmt = select(users_table.c.id, func.count(distinct(users_table.c.name)))
1111
1112 The above would produce an statement resembling:
1113
1114 .. sourcecode:: sql
1115
1116 SELECT user.id, count(DISTINCT user.name) FROM user
1117
1118 .. tip:: The :func:`_sql.distinct` function does **not** apply DISTINCT
1119 to the full SELECT statement, instead applying a DISTINCT modifier
1120 to **individual column expressions**. For general ``SELECT DISTINCT``
1121 support, use the
1122 :meth:`_sql.Select.distinct` method on :class:`_sql.Select`.
1123
1124 The :func:`.distinct` function is also available as a column-level
1125 method, e.g. :meth:`_expression.ColumnElement.distinct`, as in::
1126
1127 stmt = select(func.count(users_table.c.name.distinct()))
1128
1129 The :func:`.distinct` operator is different from the
1130 :meth:`_expression.Select.distinct` method of
1131 :class:`_expression.Select`,
1132 which produces a ``SELECT`` statement
1133 with ``DISTINCT`` applied to the result set as a whole,
1134 e.g. a ``SELECT DISTINCT`` expression. See that method for further
1135 information.
1136
1137 .. seealso::
1138
1139 :meth:`_expression.ColumnElement.distinct`
1140
1141 :meth:`_expression.Select.distinct`
1142
1143 :data:`.func`
1144
1145 """ # noqa: E501
1146 return UnaryExpression._create_distinct(expr)
1147
1148
1149def bitwise_not(expr: _ColumnExpressionArgument[_T]) -> UnaryExpression[_T]:
1150 """Produce a unary bitwise NOT clause, typically via the ``~`` operator.
1151
1152 Not to be confused with boolean negation :func:`_sql.not_`.
1153
1154 .. versionadded:: 2.0.2
1155
1156 .. seealso::
1157
1158 :ref:`operators_bitwise`
1159
1160
1161 """
1162
1163 return UnaryExpression._create_bitwise_not(expr)
1164
1165
1166def extract(field: str, expr: _ColumnExpressionArgument[Any]) -> Extract:
1167 """Return a :class:`.Extract` construct.
1168
1169 This is typically available as :func:`.extract`
1170 as well as ``func.extract`` from the
1171 :data:`.func` namespace.
1172
1173 :param field: The field to extract.
1174
1175 .. warning:: This field is used as a literal SQL string.
1176 **DO NOT PASS UNTRUSTED INPUT TO THIS STRING**.
1177
1178 :param expr: A column or Python scalar expression serving as the
1179 right side of the ``EXTRACT`` expression.
1180
1181 E.g.::
1182
1183 from sqlalchemy import extract
1184 from sqlalchemy import table, column
1185
1186 logged_table = table(
1187 "user",
1188 column("id"),
1189 column("date_created"),
1190 )
1191
1192 stmt = select(logged_table.c.id).where(
1193 extract("YEAR", logged_table.c.date_created) == 2021
1194 )
1195
1196 In the above example, the statement is used to select ids from the
1197 database where the ``YEAR`` component matches a specific value.
1198
1199 Similarly, one can also select an extracted component::
1200