From 1a4e136bdcef84d24484f75d6291d2b8135b29b0 Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Thu, 4 Jul 2019 11:16:50 -0400 Subject: [PATCH] Add tutorial section for cast(), type_coerce() Change-Id: I49f635f0ad4d07abe8ef2681c9660ec7fcf5f99b (cherry picked from commit e03f7ab50e69ffce67585cdc7a0cb7a3cc5c0cc1) --- doc/build/core/tutorial.rst | 69 ++++++++++++++++++++++++++++++++++ lib/sqlalchemy/sql/elements.py | 26 ++++++++++++- 2 files changed, 93 insertions(+), 2 deletions(-) diff --git a/doc/build/core/tutorial.rst b/doc/build/core/tutorial.rst index a829c99c56..d2ab167e09 100644 --- a/doc/build/core/tutorial.rst +++ b/doc/build/core/tutorial.rst @@ -1326,6 +1326,8 @@ single named value is needed in the execute parameters: :func:`.bindparam` +.. _coretutorial_functions: + Functions --------- @@ -1474,6 +1476,73 @@ indicate "UNBOUNDED". See the examples at :func:`.over` for more detail. :meth:`.FunctionElement.over` +.. _coretutorial_casts: + +Data Casts and Type Coercion +----------------------------- + +In SQL, we often need to indicate the datatype of an element explicitly, or +we need to convert between one datatype and another within a SQL statement. +The CAST SQL function performs this. In SQLAlchemy, the :func:`.cast` function +renders the SQL CAST keyword. It accepts a column expression and a data type +object as arguments: + +.. sourcecode:: pycon+sql + + >>> from sqlalchemy import cast + >>> s = select([cast(users.c.id, String)]) + >>> conn.execute(s).fetchall() + {opensql}SELECT CAST(users.id AS VARCHAR) AS anon_1 + FROM users + () + {stop}[('1',), ('2',)] + +The :func:`.cast` function is used not just when converting between datatypes, +but also in cases where the database needs to +know that some particular value should be considered to be of a particular +datatype within an expression. + +The :func:`.cast` function also tells SQLAlchemy itself that an expression +should be treated as a particular type as well. The datatype of an expression +directly impacts the behavior of Python operators upon that object, such as how +the ``+`` operator may indicate integer addition or string concatenation, and +it also impacts how a literal Python value is transformed or handled before +being passed to the database as well as how result values of that expression +should be transformed or handled. + +Sometimes there is the need to have SQLAlchemy know the datatype of an +expression, for all the reasons mentioned above, but to not render the CAST +expression itself on the SQL side, where it may interfere with a SQL operation +that already works without it. For this fairly common use case there is +another function :func:`.type_coerce` which is closely related to +:func:`.cast`, in that it sets up a Python expression as having a specific SQL +database type, but does not render the ``CAST`` keyword or datatype on the +database side. :func:`.type_coerce` is particularly important when dealing +with the :class:`.types.JSON` datatype, which on a database like SQLite is +an "implied" datatype. Below, we use :func:`.type_coerce` to deliver a Python +structure as a JSON string into one of SQLite's JSON functions: + +.. sourcecode:: pycon+sql + + >>> import json + >>> from sqlalchemy import JSON + >>> from sqlalchemy import type_coerce + >>> s = select([ + ... type_coerce( + ... {'some_key': {'foo': 'bar'}}, JSON + ... )['some_key'] + ... ]) + >>> conn.execute(s).fetchall() + {opensql}SELECT JSON_QUOTE(JSON_EXTRACT(?, ?)) AS anon_1 + ('{"some_key": {"foo": "bar"}}', '$."some_key"') + {stop}[({'foo': 'bar'},)] + +Above, SQLite's ``JSON_QUOTE`` and ``JSON_EXTRACT`` SQL functions were invoked +because we used :func:`.type_coerce` to indicate that our Python dictionary +should be treated as :class:`.types.JSON`. The Python ``__getitem__`` +operator, ``['some_key']`` in this case, became available as a result and +allowed a ``JSON_EXTRACT`` path expression to be rendered. + Unions and Other Set Operations ------------------------------- diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py index d36be47626..0bfba1fb7f 100644 --- a/lib/sqlalchemy/sql/elements.py +++ b/lib/sqlalchemy/sql/elements.py @@ -853,6 +853,14 @@ class ColumnElement(operators.ColumnOperators, ClauseElement): This is a shortcut to the :func:`~.expression.cast` function. + .. seealso:: + + :ref:`coretutorial_casts` + + :func:`~.expression.cast` + + :func:`~.expression.type_coerce` + .. versionadded:: 1.0.7 """ @@ -2393,8 +2401,14 @@ class Cast(ColumnElement): .. seealso:: + :ref:`coretutorial_casts` + :func:`.cast` + :func:`.type_coerce` - an alternative to CAST that coerces the type + on the Python side only, which is often sufficient to generate the + correct SQL and data coercion. + """ __visit_name__ = "cast" @@ -2444,8 +2458,12 @@ class Cast(ColumnElement): .. seealso:: - :func:`.type_coerce` - Python-side type coercion without emitting - CAST. + :ref:`coretutorial_casts` + + :func:`.type_coerce` - an alternative to CAST that coerces the type + on the Python side only, which is often sufficient to generate the + correct SQL and data coercion. + """ self.type = type_api.to_instance(type_) @@ -2478,6 +2496,8 @@ class TypeCoerce(ColumnElement): :func:`.expression.type_coerce` + :func:`.cast` + """ __visit_name__ = "type_coerce" @@ -2540,6 +2560,8 @@ class TypeCoerce(ColumnElement): .. seealso:: + :ref:`coretutorial_casts` + :func:`.cast` """ -- 2.47.2