From 79c25ede440e80e15c4c789234157d0e9497349c Mon Sep 17 00:00:00 2001 From: Mike Bayer Date: Sat, 23 Oct 2010 16:40:39 -0400 Subject: [PATCH] - Added type_coerce(expr, type_) expression element. Treats the given expression as the given type when evaluating expressions and processing result rows, but does not affect the generation of SQL, other than an anonymous label. --- CHANGES | 6 ++++ doc/build/core/expression_api.rst | 2 ++ lib/sqlalchemy/__init__.py | 1 + lib/sqlalchemy/sql/__init__.py | 1 + lib/sqlalchemy/sql/compiler.py | 8 +++-- lib/sqlalchemy/sql/expression.py | 52 +++++++++++++++++++++++++-- test/sql/test_types.py | 58 ++++++++++++++++++++++++++++++- 7 files changed, 123 insertions(+), 5 deletions(-) diff --git a/CHANGES b/CHANGES index d1ea9cb920..6df2e8006e 100644 --- a/CHANGES +++ b/CHANGES @@ -160,6 +160,12 @@ CHANGES "self.impl". This to support compilation correctly. Behavior can be user-overridden in exactly the same way as before to the same effect. + + - Added type_coerce(expr, type_) expression element. + Treats the given expression as the given type when evaluating + expressions and processing result rows, but does not + affect the generation of SQL, other than an anonymous + label. - Table.tometadata() now copies Index objects associated with the Table as well. diff --git a/doc/build/core/expression_api.rst b/doc/build/core/expression_api.rst index c39701a596..1b4a35f84d 100644 --- a/doc/build/core/expression_api.rst +++ b/doc/build/core/expression_api.rst @@ -115,6 +115,8 @@ The expression package uses functions to construct SQL expressions. The return .. autofunction:: tuple_ +.. autofunction:: type_coerce + .. autofunction:: union .. autofunction:: union_all diff --git a/lib/sqlalchemy/__init__.py b/lib/sqlalchemy/__init__.py index cb4e8e10b1..65ecc9a889 100644 --- a/lib/sqlalchemy/__init__.py +++ b/lib/sqlalchemy/__init__.py @@ -43,6 +43,7 @@ from sqlalchemy.sql import ( subquery, text, tuple_, + type_coerce, union, union_all, update, diff --git a/lib/sqlalchemy/sql/__init__.py b/lib/sqlalchemy/sql/__init__.py index aa18eac171..2bb5f6ab49 100644 --- a/lib/sqlalchemy/sql/__init__.py +++ b/lib/sqlalchemy/sql/__init__.py @@ -47,6 +47,7 @@ from sqlalchemy.sql.expression import ( table, text, tuple_, + type_coerce, union, union_all, update, diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py index e47db7e283..d3b8bf023a 100644 --- a/lib/sqlalchemy/sql/compiler.py +++ b/lib/sqlalchemy/sql/compiler.py @@ -153,6 +153,10 @@ class _CompileLabel(visitors.Visitable): def __init__(self, col, name): self.element = col self.name = name + + @property + def type(self): + return self.element.type @property def quote(self): @@ -317,7 +321,7 @@ class SQLCompiler(engine.Compiled): if result_map is not None: result_map[labelname.lower()] = \ - (label.name, (label, label.element, labelname), label.element.type) + (label.name, (label, label.element, labelname), label.type) return self.process(label.element, within_columns_clause=True, @@ -329,7 +333,7 @@ class SQLCompiler(engine.Compiled): return self.process(label.element, within_columns_clause=False, **kw) - + def visit_column(self, column, result_map=None, **kwargs): name = column.name if name is None: diff --git a/lib/sqlalchemy/sql/expression.py b/lib/sqlalchemy/sql/expression.py index 219e3bf14b..625893a68a 100644 --- a/lib/sqlalchemy/sql/expression.py +++ b/lib/sqlalchemy/sql/expression.py @@ -45,8 +45,8 @@ __all__ = [ 'except_', 'except_all', 'exists', 'extract', 'func', 'modifier', 'collate', 'insert', 'intersect', 'intersect_all', 'join', 'label', 'literal', 'literal_column', 'not_', 'null', 'or_', 'outparam', - 'outerjoin', 'select', 'subquery', 'table', 'text', 'tuple_', 'union', - 'union_all', 'update', ] + 'outerjoin', 'select', 'subquery', 'table', 'text', 'tuple_', 'type_coerce', + 'union', 'union_all', 'update', ] PARSE_AUTOCOMMIT = util._symbol('PARSE_AUTOCOMMIT') @@ -666,6 +666,54 @@ def tuple_(*expr): """ return _Tuple(*expr) + +def type_coerce(expr, type_): + """Coerce the given expression into the given type, on the Python side only. + + :func:`.type_coerce` is roughly similar to :func:.`cast`, except no + "CAST" expression is rendered - the given type is only applied towards + expression typing and against received result values. + + e.g.:: + + from sqlalchemy.types import TypeDecorator + import uuid + + class AsGuid(TypeDecorator): + impl = String + + def process_bind_param(self, value, dialect): + if value is not None: + return str(value) + else: + return None + + def process_result_value(self, value, dialect): + if value is not None: + return uuid.UUID(value) + else: + return None + + conn.execute( + select([type_coerce(mytable.c.ident, AsGuid)]).\\ + where( + type_coerce(mytable.c.ident, AsGuid) == + uuid.uuid3(uuid.NAMESPACE_URL, 'bar') + ) + ) + + """ + if hasattr(expr, '__clause_expr__'): + return type_coerce(expr.__clause_expr__()) + + elif not isinstance(expr, Visitable): + if expr is None: + return null() + else: + return literal(expr, type_=type_) + else: + return _Label(None, expr, type_=type_) + def label(name, obj): """Return a :class:`_Label` object for the diff --git a/test/sql/test_types.py b/test/sql/test_types.py index 437d69fffa..9938438913 100644 --- a/test/sql/test_types.py +++ b/test/sql/test_types.py @@ -201,7 +201,63 @@ class UserDefinedTest(TestBase, AssertsCompiledSQL): t.dialect_impl(dialect=pg).impl.__class__, Float().dialect_impl(pg).__class__ ) - + + @testing.provide_metadata + def test_type_coerce(self): + """test ad-hoc usage of custom types with type_coerce().""" + + class MyType(types.TypeDecorator): + impl = String + + def process_bind_param(self, value, dialect): + return value[0:-8] + + def process_result_value(self, value, dialect): + return value + "BIND_OUT" + + t = Table('t', metadata, Column('data', String(50))) + metadata.create_all() + + t.insert().values(data=type_coerce('d1BIND_OUT',MyType)).execute() + + eq_( + select([type_coerce(t.c.data, MyType)]).execute().fetchall(), + [('d1BIND_OUT', )] + ) + + eq_( + select([t.c.data, type_coerce(t.c.data, MyType)]).execute().fetchall(), + [('d1', 'd1BIND_OUT')] + ) + + eq_( + select([t.c.data, type_coerce(t.c.data, MyType)]).\ + where(type_coerce(t.c.data, MyType) == 'd1BIND_OUT').\ + execute().fetchall(), + [('d1', 'd1BIND_OUT')] + ) + + eq_( + select([t.c.data, type_coerce(t.c.data, MyType)]).\ + where(t.c.data == type_coerce('d1BIND_OUT', MyType)).\ + execute().fetchall(), + [('d1', 'd1BIND_OUT')] + ) + + eq_( + select([t.c.data, type_coerce(t.c.data, MyType)]).\ + where(t.c.data == type_coerce(None, MyType)).\ + execute().fetchall(), + [] + ) + + eq_( + select([t.c.data, type_coerce(t.c.data, MyType)]).\ + where(type_coerce(t.c.data, MyType) == None).\ + execute().fetchall(), + [] + ) + @classmethod def setup_class(cls): global users, metadata -- 2.47.2