Python sqlalchemy.TypeDecorator() Examples

def test_affinity_typedec(self):
        class MyType(TypeDecorator):
            impl = CHAR

            def load_dialect_impl(self, dialect):
                if dialect.name == "sqlite":
                    return dialect.type_descriptor(Integer())
                else:
                    return dialect.type_descriptor(CHAR(32))

        uo = ops.AlterColumnOp("sometable", "somecol")
        autogenerate.compare._compare_type(
            self.autogen_context,
            uo,
            None,
            "sometable",
            "somecol",
            Column("somecol", Integer, nullable=True),
            Column("somecol", MyType()),
        )
        assert not uo.has_changes() 

def test_typedec_to_nonstandard(self, impl_fixture):
        class PasswordType(TypeDecorator):
            impl = VARBINARY

            def copy(self, **kw):
                return PasswordType(self.impl.length)

            def load_dialect_impl(self, dialect):
                if dialect.name == "default":
                    impl = sqlite.NUMERIC(self.length)
                else:
                    impl = VARBINARY(self.length)
                return dialect.type_descriptor(impl)

        impl_fixture.compare_type(
            Column("x", sqlite.NUMERIC(50)), Column("x", PasswordType(50))
        ) 

def test_exception_wrapping_non_dbapi_statement(self):
        class MyType(TypeDecorator):
            impl = Integer

            def process_bind_param(self, value, dialect):
                raise SomeException("nope")

        def _go(conn):
            assert_raises_message(
                tsa.exc.StatementError,
                r"\(.*.SomeException\) " r"nope\n\[SQL\: u?SELECT 1 ",
                conn.execute,
                select([1]).where(column("foo") == literal("bar", MyType())),
            )

        _go(testing.db)
        with testing.db.connect() as conn:
            _go(conn) 

def test_dont_wrap_mixin(self):
        class MyException(Exception, tsa.exc.DontWrapMixin):
            pass

        class MyType(TypeDecorator):
            impl = Integer

            def process_bind_param(self, value, dialect):
                raise MyException("nope")

        def _go(conn):
            assert_raises_message(
                MyException,
                "nope",
                conn.execute,
                select([1]).where(column("foo") == literal("bar", MyType())),
            )

        _go(testing.db)
        conn = testing.db.connect()
        try:
            _go(conn)
        finally:
            conn.close() 

def _test_result_processor(self, cls, use_cache):
        class MyType(TypeDecorator):
            impl = String()

            def process_result_value(self, value, dialect):
                return "HI " + value

        with self._proxy_fixture(cls):
            with self.engine.connect() as conn:
                if use_cache:
                    cache = {}
                    conn = conn.execution_options(compiled_cache=cache)

                stmt = select([literal("THERE", type_=MyType())])
                for i in range(2):
                    r = conn.execute(stmt)
                    eq_(r.scalar(), "HI THERE") 

def test_user_defined_typedec_impl(self):
        class MyType(types.TypeDecorator):
            impl = Float

            def load_dialect_impl(self, dialect):
                if dialect.name == "sqlite":
                    return String(50)
                else:
                    return super(MyType, self).load_dialect_impl(dialect)

        sl = dialects.sqlite.dialect()
        pg = dialects.postgresql.dialect()
        t = MyType()
        self.assert_compile(t, "VARCHAR(50)", dialect=sl)
        self.assert_compile(t, "FLOAT", dialect=pg)
        eq_(
            t.dialect_impl(dialect=sl).impl.__class__,
            String().dialect_impl(dialect=sl).__class__,
        )
        eq_(
            t.dialect_impl(dialect=pg).impl.__class__,
            Float().dialect_impl(pg).__class__,
        ) 

def define_tables(cls, metadata):
        class MyString(TypeDecorator):
            impl = String

            def bind_expression(self, bindvalue):
                return func.lower(bindvalue)

            def column_expression(self, col):
                return func.upper(col)

        Table(
            "test_table",
            metadata,
            Column("x", String(50)),
            Column("y", MyString(50)),
        ) 

def _all_types(omit_special_types=False):
    seen = set()
    for typ in _types_for_mod(types):
        if omit_special_types and typ in (
            types.TypeDecorator,
            types.TypeEngine,
            types.Variant,
        ):
            continue

        if typ in seen:
            continue
        seen.add(typ)
        yield typ
    for dialect in _all_dialect_modules():
        for typ in _types_for_mod(dialect):
            if typ in seen:
                continue
            seen.add(typ)
            yield typ 

def _adaptions():
        for typ in _all_types(omit_special_types=True):

            # up adapt from LowerCase to UPPERCASE,
            # as well as to all non-sqltypes
            up_adaptions = [typ] + typ.__subclasses__()
            yield "%s.%s" % (
                typ.__module__,
                typ.__name__,
            ), False, typ, up_adaptions
            for subcl in typ.__subclasses__():
                if (
                    subcl is not typ
                    and typ is not TypeDecorator
                    and "sqlalchemy" in subcl.__module__
                ):
                    yield "%s.%s" % (
                        subcl.__module__,
                        subcl.__name__,
                    ), True, subcl, [typ] 

def _type_decorator_both_fixture(self):
        class MyDialectString(String):
            def bind_expression(self, bindvalue):
                return func.inside_bind(bindvalue)

            def column_expression(self, col):
                return func.inside_colexpr(col)

        class MyString(TypeDecorator):
            impl = String

            # this works because when the compiler calls dialect_impl(),
            # a copy of MyString is created which has just this impl
            # as self.impl
            def load_dialect_impl(self, dialect):
                return MyDialectString()

            # user-defined methods need to invoke explicitly on the impl
            # for now...
            def bind_expression(self, bindvalue):
                return func.outside_bind(self.impl.bind_expression(bindvalue))

            def column_expression(self, col):
                return func.outside_colexpr(self.impl.column_expression(col))

        return self._test_table(MyString) 

def test_non_native_constraint_custom_type(self):
        class Foob(object):
            def __init__(self, name):
                self.name = name

        class MyEnum(TypeDecorator):
            def __init__(self, values):
                self.impl = Enum(
                    *[v.name for v in values],
                    name="myenum",
                    native_enum=False,
                    create_constraint=True
                )

            # future method
            def process_literal_param(self, value, dialect):
                return value.name

            def process_bind_param(self, value, dialect):
                return value.name

        m = MetaData()
        t1 = Table("t", m, Column("x", MyEnum([Foob("a"), Foob("b")])))
        const = [c for c in t1.constraints if isinstance(c, CheckConstraint)][
            0
        ]

        self.assert_compile(
            AddConstraint(const),
            "ALTER TABLE t ADD CONSTRAINT myenum CHECK (x IN ('a', 'b'))",
            dialect="default",
        ) 

def setup_class(cls):
        global binary_table, MyPickleType, metadata

        class MyPickleType(types.TypeDecorator):
            impl = PickleType

            def process_bind_param(self, value, dialect):
                if value:
                    value.stuff = "this is modified stuff"
                return value

            def process_result_value(self, value, dialect):
                if value:
                    value.stuff = "this is the right stuff"
                return value

        metadata = MetaData(testing.db)
        binary_table = Table(
            "binary_table",
            metadata,
            Column(
                "primary_id",
                Integer,
                primary_key=True,
                test_needs_autoincrement=True,
            ),
            Column("data", LargeBinary),
            Column("data_slice", LargeBinary(100)),
            Column("misc", String(30)),
            Column("pickled", PickleType),
            Column("mypickle", MyPickleType),
        )
        metadata.create_all() 

def test_typedec_is_adapt(self):
        class CoerceNothing(TypeDecorator):
            coerce_to_is_types = ()
            impl = Integer

        class CoerceBool(TypeDecorator):
            coerce_to_is_types = (bool,)
            impl = Boolean

        class CoerceNone(TypeDecorator):
            coerce_to_is_types = (type(None),)
            impl = Integer

        c1 = column("x", CoerceNothing())
        c2 = column("x", CoerceBool())
        c3 = column("x", CoerceNone())

        self.assert_compile(
            and_(c1 == None, c2 == None, c3 == None),  # noqa
            "x = :x_1 AND x = :x_2 AND x IS NULL",
        )
        self.assert_compile(
            and_(c1 == True, c2 == True, c3 == True),  # noqa
            "x = :x_1 AND x = true AND x = :x_2",
            dialect=default.DefaultDialect(supports_native_boolean=True),
        )
        self.assert_compile(
            and_(c1 == 3, c2 == 3, c3 == 3),
            "x = :x_1 AND x = :x_2 AND x = :x_3",
            dialect=default.DefaultDialect(supports_native_boolean=True),
        )
        self.assert_compile(
            and_(c1.is_(True), c2.is_(True), c3.is_(True)),
            "x IS :x_1 AND x IS true AND x IS :x_2",
            dialect=default.DefaultDialect(supports_native_boolean=True),
        ) 

def test_non_native_constraint_custom_type(self):
        class Foob(object):
            def __init__(self, value):
                self.value = value

        class MyBool(TypeDecorator):
            impl = Boolean(create_constraint=True)

            # future method
            def process_literal_param(self, value, dialect):
                return value.value

            def process_bind_param(self, value, dialect):
                return value.value

        m = MetaData()
        t1 = Table("t", m, Column("x", MyBool()))
        const = [c for c in t1.constraints if isinstance(c, CheckConstraint)][
            0
        ]

        self.assert_compile(
            AddConstraint(const),
            "ALTER TABLE t ADD CHECK (x IN (0, 1))",
            dialect="sqlite",
        ) 

def _type_decorator_outside_fixture(self):
        class MyString(TypeDecorator):
            impl = String

            def bind_expression(self, bindvalue):
                return func.outside_bind(bindvalue)

            def column_expression(self, col):
                return func.outside_colexpr(col)

        return self._test_table(MyString) 

def _type_decorator_inside_fixture(self):
        class MyInsideString(String):
            def bind_expression(self, bindvalue):
                return func.inside_bind(bindvalue)

            def column_expression(self, col):
                return func.inside_colexpr(col)

        class MyString(TypeDecorator):
            impl = MyInsideString

        return self._test_table(MyString) 

def define_tables(cls, metadata):
        from sqlalchemy import TypeDecorator

        class NoBool(object):
            def __nonzero__(self):
                raise NotImplementedError("not supported")

        class MyWidget(object):
            def __init__(self, text):
                self.text = text

            def __eq__(self, other):
                return NoBool()

        cls.MyWidget = MyWidget

        class MyType(TypeDecorator):
            impl = String(50)

            def process_bind_param(self, value, dialect):
                if value is not None:
                    value = value.text
                return value

            def process_result_value(self, value, dialect):
                if value is not None:
                    value = MyWidget(value)
                return value

        Table(
            "test",
            metadata,
            Column(
                "id", Integer, primary_key=True, test_needs_autoincrement=True
            ),
            Column("value", MyType),
            Column("unrelated", String(50)),
        ) 

def test_fetch_unordered_result_map(self, connection):
        users = self.tables.users

        connection.execute(users.insert(), user_id=7, user_name="ed")

        class Goofy1(TypeDecorator):
            impl = String

            def process_result_value(self, value, dialect):
                return value + "a"

        class Goofy2(TypeDecorator):
            impl = String

            def process_result_value(self, value, dialect):
                return value + "b"

        class Goofy3(TypeDecorator):
            impl = String

            def process_result_value(self, value, dialect):
                return value + "c"

        t = text(
            "select user_name as a, user_name as b, "
            "user_name as c from users"
        ).columns(a=Goofy1(), b=Goofy2(), c=Goofy3())
        eq_(connection.execute(t).fetchall(), [("eda", "edb", "edc")]) 

def define_tables(cls, metadata):
        class SpecialType(TypeDecorator):
            impl = Date

            def process_bind_param(self, value, dialect):
                assert isinstance(value, datetime.date)
                return value

        Table(
            "version_table",
            metadata,
            Column("id", SpecialType, primary_key=True),
            Column("version_id", Integer, nullable=False),
            Column("value", String(40), nullable=False),
        ) 

def _get_column_type(col: MapperProperty) -> TypeEngine:
    """ Get column's SQL type """
    if isinstance(col.type, TypeDecorator):
        # Type decorators wrap other types, so we have to handle them carefully
        return col.type.impl
    else:
        return col.type 

def define_tables(cls, metadata):
        class MyType(types.TypeDecorator):
            impl = String(50)

            def process_bind_param(self, value, dialect):
                return "BIND_IN" + str(value)

            def process_result_value(self, value, dialect):
                return value + "BIND_OUT"

        cls.MyType = MyType

        Table("t", metadata, Column("data", String(50))) 

def test_type_decorator_repr(self):
        class MyType(TypeDecorator):
            impl = VARCHAR

        eq_(repr(MyType(45)), "MyType(length=45)") 

def test_typedecorator_impl(self):
        for impl_, exp, kw in [
            (Float, "FLOAT", {}),
            (Float, "FLOAT(2)", {"precision": 2}),
            (Float(2), "FLOAT(2)", {"precision": 4}),
            (Numeric(19, 2), "NUMERIC(19, 2)", {}),
        ]:
            for dialect_ in (
                dialects.postgresql,
                dialects.mssql,
                dialects.mysql,
            ):
                dialect_ = dialect_.dialect()

                raw_impl = types.to_instance(impl_, **kw)

                class MyType(types.TypeDecorator):
                    impl = impl_

                dec_type = MyType(**kw)

                eq_(dec_type.impl.__class__, raw_impl.__class__)

                raw_dialect_impl = raw_impl.dialect_impl(dialect_)
                dec_dialect_impl = dec_type.dialect_impl(dialect_)
                eq_(dec_dialect_impl.__class__, MyType)
                eq_(
                    raw_dialect_impl.__class__, dec_dialect_impl.impl.__class__
                )

                self.assert_compile(MyType(**kw), exp, dialect=dialect_) 

def test_typedecorator_literal_render_fallback_bound(self):
        # fall back to process_bind_param for literal
        # value rendering.
        class MyType(types.TypeDecorator):
            impl = String

            def process_bind_param(self, value, dialect):
                return "HI->%s<-THERE" % value

        self.assert_compile(
            select([literal("test", MyType)]),
            "SELECT 'HI->test<-THERE' AS anon_1",
            dialect="default",
            literal_binds=True,
        ) 

def test_typedecorator_literal_render(self):
        class MyType(types.TypeDecorator):
            impl = String

            def process_literal_param(self, value, dialect):
                return "HI->%s<-THERE" % value

        self.assert_compile(
            select([literal("test", MyType)]),
            "SELECT 'HI->test<-THERE' AS anon_1",
            dialect="default",
            literal_binds=True,
        ) 

def test_type_coerce_preserve_subq(self):
        class MyType(TypeDecorator):
            impl = Integer

        stmt = select([type_coerce(column("x"), MyType).label("foo")])
        subq = stmt.subquery()
        stmt2 = subq.select()
        subq2 = stmt2.subquery()
        assert isinstance(stmt._raw_columns[0].type, MyType)
        assert isinstance(subq.c.foo.type, MyType)
        assert isinstance(stmt2.selected_columns.foo.type, MyType)
        assert isinstance(subq2.c.foo.type, MyType) 

def define_tables(cls, metadata):
        class ProcValue(TypeDecorator):
            impl = cls.ARRAY(Integer, dimensions=2)

            def process_bind_param(self, value, dialect):
                if value is None:
                    return None
                return [[x + 5 for x in v] for v in value]

            def process_result_value(self, value, dialect):
                if value is None:
                    return None
                return [[x - 7 for x in v] for v in value]

        Table(
            "arrtable",
            metadata,
            Column("id", Integer, primary_key=True),
            Column("intarr", cls.ARRAY(Integer)),
            Column("strarr", cls.ARRAY(Unicode())),
            Column("dimarr", ProcValue),
        )

        Table(
            "dim_arrtable",
            metadata,
            Column("id", Integer, primary_key=True),
            Column("intarr", cls.ARRAY(Integer, dimensions=1)),
            Column("strarr", cls.ARRAY(Unicode(), dimensions=1)),
            Column("dimarr", ProcValue),
        ) 

def test_limit_preserves_typing_information(self):
        class MyType(TypeDecorator):
            impl = Integer

        stmt = select([type_coerce(column("x"), MyType).label("foo")]).limit(1)
        dialect = oracle.dialect()
        compiled = stmt.compile(dialect=dialect)
        assert isinstance(compiled._create_result_map()["foo"][-1], MyType) 

def test_exception_event_ad_hoc_context(self):
        """test that handle_error is called with a context in
        cases where _handle_dbapi_error() is normally called without
        any context.

        """

        engine = engines.testing_engine()

        listener = Mock(return_value=None)
        event.listen(engine, "handle_error", listener)

        nope = SomeException("nope")

        class MyType(TypeDecorator):
            impl = Integer

            def process_bind_param(self, value, dialect):
                raise nope

        with engine.connect() as conn:
            assert_raises_message(
                tsa.exc.StatementError,
                r"\(.*.SomeException\) " r"nope\n\[SQL\: u?SELECT 1 ",
                conn.execute,
                select([1]).where(column("foo") == literal("bar", MyType())),
            )

        ctx = listener.mock_calls[0][1][0]
        assert ctx.statement.startswith("SELECT 1 ")
        is_(ctx.is_disconnect, False)
        is_(ctx.original_exception, nope) 

def test_custom_type_compare(self):
        class MyType(TypeDecorator):
            impl = Integer

            def compare_against_backend(self, dialect, conn_type):
                return isinstance(conn_type, Integer)

        ac = ops.AlterColumnOp("sometable", "somecol")
        autogenerate.compare._compare_type(
            self.autogen_context,
            ac,
            None,
            "sometable",
            "somecol",
            Column("somecol", INTEGER()),
            Column("somecol", MyType()),
        )

        assert not ac.has_changes()

        ac = ops.AlterColumnOp("sometable", "somecol")
        autogenerate.compare._compare_type(
            self.autogen_context,
            ac,
            None,
            "sometable",
            "somecol",
            Column("somecol", String()),
            Column("somecol", MyType()),
        )
        diff = ac.to_diff_tuple()
        eq_(diff[0][0:4], ("modify_type", None, "sometable", "somecol"))