From 9fc21c07b7cd3206a78fcc8665304fcbc2c5b27a Mon Sep 17 00:00:00 2001 From: Nick Crews Date: Mon, 8 May 2023 15:07:58 -0800 Subject: [PATCH] Move try_cast() from mssql to base Other backends besides mssql support an operation like try_cast. Move it to a common place so they can all implement it. Fixes: #9752 Closes: #9752 --- doc/build/changelog/unreleased_20/9752.rst | 14 ++++++++ lib/sqlalchemy/__init__.py | 2 ++ lib/sqlalchemy/dialects/mssql/base.py | 37 ++++---------------- lib/sqlalchemy/sql/_elements_constructors.py | 28 +++++++++++++++ lib/sqlalchemy/sql/elements.py | 19 ++++++++++ test/dialect/mssql/test_compiler.py | 14 +++++--- 6 files changed, 79 insertions(+), 35 deletions(-) create mode 100644 doc/build/changelog/unreleased_20/9752.rst diff --git a/doc/build/changelog/unreleased_20/9752.rst b/doc/build/changelog/unreleased_20/9752.rst new file mode 100644 index 0000000000..519c75f070 --- /dev/null +++ b/doc/build/changelog/unreleased_20/9752.rst @@ -0,0 +1,14 @@ +.. change:: + :tags: usecase, sql + :tickets: 9752 + + + Added new :func:`.try_cast` factory function and corresponding + :class:`.TryCast` SQL Element, which implements a cast where + un-castable values are returned as NULL, instead of raising an error. + + This is currently implemented as ``TRY_CAST`` in Microsoft SQL Server. + and could be implemented in other backends: + * ``SAFE_CAST`` in Google BigQuery, and + * ``TRY_CAST`` in DuckDB. + * ``TRY_CAST`` in Snowflake. diff --git a/lib/sqlalchemy/__init__.py b/lib/sqlalchemy/__init__.py index 0bf16401c5..da30e7edc2 100644 --- a/lib/sqlalchemy/__init__.py +++ b/lib/sqlalchemy/__init__.py @@ -99,6 +99,8 @@ from .sql.expression import Case as Case from .sql.expression import case as case from .sql.expression import Cast as Cast from .sql.expression import cast as cast +from .sql.expression import TryCast as TryCast +from .sql.expression import try_cast as try_cast from .sql.expression import ClauseElement as ClauseElement from .sql.expression import ClauseList as ClauseList from .sql.expression import collate as collate diff --git a/lib/sqlalchemy/dialects/mssql/base.py b/lib/sqlalchemy/dialects/mssql/base.py index b33ce4aec8..e37f92af33 100644 --- a/lib/sqlalchemy/dialects/mssql/base.py +++ b/lib/sqlalchemy/dialects/mssql/base.py @@ -941,6 +941,7 @@ from ...sql import sqltypes from ...sql import util as sql_util from ...sql._typing import is_sql_compiler from ...sql.compiler import InsertmanyvaluesSentinelOpts +from ...sql.elements import TryCast from ...types import BIGINT from ...types import BINARY from ...types import CHAR @@ -1604,40 +1605,14 @@ class SQL_VARIANT(sqltypes.TypeEngine): def try_cast(*arg, **kw): - """Create a TRY_CAST expression. - - :class:`.TryCast` is a subclass of SQLAlchemy's :class:`.Cast` - construct, and works in the same way, except that the SQL expression - rendered is "TRY_CAST" rather than "CAST":: - - from sqlalchemy import select - from sqlalchemy import Numeric - from sqlalchemy.dialects.mssql import try_cast - - stmt = select( - try_cast(product_table.c.unit_price, Numeric(10, 4)) - ) - - The above would render:: - - SELECT TRY_CAST (product_table.unit_price AS NUMERIC(10, 4)) - FROM product_table - - .. versionadded:: 1.3.7 - - """ + util.warn_deprecated( + "`sqlalchemy.dialects.mssql.base.try_cast` is deprecated. " + "Use directly from sqlalchemy instead, i.e. `sa.try_cast(...)`", + "2.1", + ) return TryCast(*arg, **kw) -class TryCast(sql.elements.Cast): - """Represent a SQL Server TRY_CAST expression.""" - - __visit_name__ = "try_cast" - - stringify_dialect = "mssql" - inherit_cache = True - - # old names. MSDateTime = _MSDateTime MSDate = _MSDate diff --git a/lib/sqlalchemy/sql/_elements_constructors.py b/lib/sqlalchemy/sql/_elements_constructors.py index 99a839cc90..9a52e59e4e 100644 --- a/lib/sqlalchemy/sql/_elements_constructors.py +++ b/lib/sqlalchemy/sql/_elements_constructors.py @@ -40,6 +40,7 @@ from .elements import Null from .elements import Over from .elements import TextClause from .elements import True_ +from .elements import TryCast from .elements import Tuple from .elements import TypeCoerce from .elements import UnaryExpression @@ -893,6 +894,9 @@ def cast( :ref:`tutorial_casts` + :func:`.try_cast` - an alternative to CAST that results in + NULLs when the cast fails, instead of raising an error. + :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. @@ -902,6 +906,30 @@ def cast( return Cast(expression, type_) +def try_cast(*arg, **kw): + """Create a TRY_CAST expression. + + :class:`.TryCast` is a subclass of SQLAlchemy's :class:`.Cast` + construct, and works in the same way, except that the SQL expression + rendered is "TRY_CAST" rather than "CAST":: + + from sqlalchemy import select, try_cast, Numeric + + stmt = select( + try_cast(product_table.c.unit_price, Numeric(10, 4)) + ) + + The above would render with mssql as:: + + SELECT TRY_CAST (product_table.unit_price AS NUMERIC(10, 4)) + FROM product_table + + .. versionadded:: 2.1.0 + + """ + return TryCast(*arg, **kw) + + def column( text: str, type_: Optional[_TypeEngineArgument[_T]] = None, diff --git a/lib/sqlalchemy/sql/elements.py b/lib/sqlalchemy/sql/elements.py index 2e32da7540..c70bac4772 100644 --- a/lib/sqlalchemy/sql/elements.py +++ b/lib/sqlalchemy/sql/elements.py @@ -3372,6 +3372,8 @@ class Cast(WrapsColumnExpression[_T]): :func:`.cast` + :func:`.try_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. @@ -3412,6 +3414,23 @@ class Cast(WrapsColumnExpression[_T]): return self.clause + +class TryCast(Cast): + """Represent a TRY_CAST expression. + + Details on :class:`.TryCast` usage is at :func:`.try_cast`. + + .. seealso:: + + :func:`.try_cast` + + :ref:`tutorial_casts` + """ + + __visit_name__ = "try_cast" + inherit_cache = True + + class TypeCoerce(WrapsColumnExpression[_T]): """Represent a Python-side type-coercion wrapper. diff --git a/test/dialect/mssql/test_compiler.py b/test/dialect/mssql/test_compiler.py index 0076c76fd9..3dbd436d3c 100644 --- a/test/dialect/mssql/test_compiler.py +++ b/test/dialect/mssql/test_compiler.py @@ -20,6 +20,7 @@ from sqlalchemy import String from sqlalchemy import Table from sqlalchemy import testing from sqlalchemy import text +from sqlalchemy import try_cast from sqlalchemy import union from sqlalchemy import UniqueConstraint from sqlalchemy import update @@ -1477,10 +1478,15 @@ class CompileTest(fixtures.TestBase, AssertsCompiledSQL): metadata = MetaData() t1 = Table("t1", metadata, Column("id", Integer, primary_key=True)) - self.assert_compile( - select(try_cast(t1.c.id, Integer)), - "SELECT TRY_CAST (t1.id AS INTEGER) AS id FROM t1", - ) + def call(func): + self.assert_compile( + select(func(t1.c.id, Integer)), + "SELECT TRY_CAST (t1.id AS INTEGER) AS id FROM t1", + ) + + with testing.expect_deprecated(".*try_cast.*"): + call(mssql_base.try_cast) + call(try_cast) @testing.combinations( ("no_persisted", "", "ignore"), -- 2.47.3