Python sqlalchemy.type_coerce() Examples

def list_dids_by_meta(scope, select, session=None):
    """
    Add or update the given metadata to the given did

    :param scope: the scope of the did
    :param name: the name of the did
    :param meta: the metadata to be added or updated
    """
    # Currently for sqlite only add, get and delete is implemented.
    if session.bind.dialect.name == 'sqlite':
        raise NotImplementedError
    if session.bind.dialect.name == 'oracle':
        oracle_version = int(session.connection().connection.version.split('.')[0])
        if oracle_version < 12:
            raise NotImplementedError

    query = session.query(models.DidMeta)
    if scope is not None:
        query = query.filter(models.DidMeta.scope == scope)

    for k, v in iteritems(select):
        if session.bind.dialect.name == 'oracle':
            query = query.filter(text("json_exists(meta,'$.%s?(@==''%s'')')" % (k, v)))
        else:
            query = query.filter(cast(models.DidMeta.meta[k], String) == type_coerce(v, JSON))
    dids = list()
    for row in query.yield_per(10):
        dids.append({'scope': row.scope, 'name': row.name})
    return dids 

def _set_table(self, schema, column, table):
        SchemaType._set_table(self, column, table)

        if not self.create_constraint:
            return

        variant_mapping = self._variant_mapping_for_set_table(column)

        e = schema.CheckConstraint(
            type_coerce(column, self).in_(self.enums),
            name=_defer_name(self.name),
            _create_rule=util.portable_instancemethod(
                self._should_create_constraint,
                {"variant_mapping": variant_mapping}),
            _type_bound=True
        )
        assert e.table is table 

def _set_table(self, column, table):
        schema = util.preloaded.sql_schema
        if not self.create_constraint:
            return

        variant_mapping = self._variant_mapping_for_set_table(column)

        e = schema.CheckConstraint(
            type_coerce(column, self).in_([0, 1]),
            name=_defer_name(self.name),
            _create_rule=util.portable_instancemethod(
                self._should_create_constraint,
                {"variant_mapping": variant_mapping},
            ),
            _type_bound=True,
        )
        assert e.table is table 

def _set_table(self, schema, column, table):
        SchemaType._set_table(self, column, table)

        if not self.create_constraint:
            return

        variant_mapping = self._variant_mapping_for_set_table(column)

        e = schema.CheckConstraint(
            type_coerce(column, self).in_(self.enums),
            name=_defer_name(self.name),
            _create_rule=util.portable_instancemethod(
                self._should_create_constraint,
                {"variant_mapping": variant_mapping}),
            _type_bound=True
        )
        assert e.table is table 

def _set_table(self, schema, column, table):
        SchemaType._set_table(self, column, table)

        if not self.create_constraint:
            return

        variant_mapping = self._variant_mapping_for_set_table(column)

        e = schema.CheckConstraint(
            type_coerce(column, self).in_(self.enums),
            name=_defer_name(self.name),
            _create_rule=util.portable_instancemethod(
                self._should_create_constraint,
                {"variant_mapping": variant_mapping}),
            _type_bound=True
        )
        assert e.table is table 

def _set_table(self, column, table):
        schema = util.preloaded.sql_schema
        SchemaType._set_table(self, column, table)

        if not self.create_constraint:
            return

        variant_mapping = self._variant_mapping_for_set_table(column)

        e = schema.CheckConstraint(
            type_coerce(column, self).in_(self.enums),
            name=_defer_name(self.name),
            _create_rule=util.portable_instancemethod(
                self._should_create_constraint,
                {"variant_mapping": variant_mapping},
            ),
            _type_bound=True,
        )
        assert e.table is table 

def test_unhashable_type(self):
        from sqlalchemy.types import TypeDecorator, Integer
        from sqlalchemy.sql import type_coerce

        class MyType(TypeDecorator):
            impl = Integer
            hashable = False

            def process_result_value(self, value, dialect):
                return [value]

        User, users = self.classes.User, self.tables.users

        mapper(User, users)

        s = Session()
        q = s.query(User, type_coerce(users.c.id, MyType).label("foo")).filter(
            User.id == 7
        )
        row = q.first()
        eq_(row, (User(id=7), [7])) 

def _set_table(self, schema, column, table):
        if self.native_enum:
            SchemaType._set_table(self, column, table)

        if not self.create_constraint:
            return

        variant_mapping = self._variant_mapping_for_set_table(column)

        e = schema.CheckConstraint(
            type_coerce(column, self).in_(self.enums),
            name=_defer_name(self.name),
            _create_rule=util.portable_instancemethod(
                self._should_create_constraint,
                {"variant_mapping": variant_mapping}),
            _type_bound=True
        )
        assert e.table is table 

def setup_mappers(cls):
        mapper(
            cls.classes.Person,
            cls.tables.person,
            properties=dict(
                pets=relationship(
                    cls.classes.Pet,
                    primaryjoin=(
                        orm.foreign(cls.tables.pets.c.person_id)
                        == sa.cast(
                            sa.type_coerce(cls.tables.person.c.id, Integer),
                            Integer,
                        )
                    ),
                )
            ),
        )

        mapper(cls.classes.Pet, cls.tables.pets) 

def test_label_plus_element(self):
        t = Table("t", MetaData(), Column("a", Integer))
        l1 = t.c.a.label("bar")
        tc = type_coerce(t.c.a + "str", String)
        stmt = select([t.c.a, l1, tc])
        comp = stmt.compile()
        tc_anon_label = comp._create_result_map()["anon_1"][1][0]
        eq_(
            comp._create_result_map(),
            {
                "a": ("a", (t.c.a, "a", "a", "t_a"), t.c.a.type),
                "bar": ("bar", (l1, "bar"), l1.type),
                "anon_1": (
                    tc.anon_label,
                    (tc_anon_label, "anon_1", tc),
                    tc.type,
                ),
            },
        ) 

def test_type_coerce_auto_label(self):
        table1 = self.table1

        self.assert_compile(
            select(
                [
                    type_coerce(table1.c.name, Integer),
                    type_coerce(table1.c.name, String),
                    table1.c.name,
                ]
            ),
            # ideally type_coerce wouldn't label at all...
            "SELECT some_table.name AS name, "
            "some_table.name AS name, "
            "some_table.name FROM some_table",
        ) 

def _test_bind(self, coerce_fn, conn):
        MyType = self.MyType

        t = self.tables.t
        conn.execute(t.insert().values(data=coerce_fn("d1", MyType)))

        stmt = select(
            [
                bindparam(None, "x", String(50), unique=True),
                coerce_fn(
                    bindparam(None, "x", String(50), unique=True), MyType
                ),
            ]
        )

        eq_(
            conn.execute(stmt).fetchall(),
            [("x", "BIND_INxBIND_OUT")]
            if coerce_fn is type_coerce
            else [("x", "xBIND_OUT")],
        ) 

def cast(self, type_):
        """Produce a type cast, i.e. ``CAST(<expression> AS <type>)``.

        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

        """
        return Cast(self, type_) 

def _test_replace_col_w_bind(self, coerce_fn, conn):
        MyType = self.MyType

        t = self.tables.t
        conn.execute(t.insert().values(data=coerce_fn("d1", MyType)))

        stmt = select([t.c.data, coerce_fn(t.c.data, MyType)])

        def col_to_bind(col):
            if col is t.c.data:
                return bindparam(None, "x", type_=col.type, unique=True)
            return None

        # ensure we evaluate the expression so that we can see
        # the clone resets this info
        stmt.compile()

        new_stmt = visitors.replacement_traverse(stmt, {}, col_to_bind)

        # original statement
        eq_(
            conn.execute(stmt).fetchall(),
            [("BIND_INd1", "BIND_INd1BIND_OUT")],
        )

        # replaced with binds; CAST can't affect the bound parameter
        # on the way in here
        eq_(
            conn.execute(new_stmt).fetchall(),
            [("x", "BIND_INxBIND_OUT")]
            if coerce_fn is type_coerce
            else [("x", "xBIND_OUT")],
        ) 

def test_vs_non_coerced_where_type_coerce(self, connection):
        self._test_vs_non_coerced_where(type_coerce, connection) 

def test_type_coerce_replace_col_w_bind(self, connection):
        self._test_replace_col_w_bind(type_coerce, connection) 

def test_resolve_clause_element_type_coerce(self, connection):
        self._test_resolve_clause_element(type_coerce, connection) 

def test_vs_non_coerced_type_coerce(self, connection):
        self._test_vs_non_coerced(type_coerce, connection) 

def test_type_bind(self, connection):
        self._test_bind(type_coerce, connection) 

def test_type_coerce(self):
        User = self.classes.User
        session = Session()
        with self._assert_bind_args(session, expect_mapped_bind=True):
            session.query(type_coerce(User.name, String())).all() 

def _set_table(self, schema, column, table):
        if not self.create_constraint:
            return

        variant_mapping = self._variant_mapping_for_set_table(column)

        e = schema.CheckConstraint(
            type_coerce(column, self).in_([0, 1]),
            name=_defer_name(self.name),
            _create_rule=util.portable_instancemethod(
                self._should_create_constraint,
                {"variant_mapping": variant_mapping}),
            _type_bound=True
        )
        assert e.table is table 

def _set_table(self, schema, column, table):
        if not self.create_constraint:
            return

        variant_mapping = self._variant_mapping_for_set_table(column)

        e = schema.CheckConstraint(
            type_coerce(column, self).in_([0, 1]),
            name=_defer_name(self.name),
            _create_rule=util.portable_instancemethod(
                self._should_create_constraint,
                {"variant_mapping": variant_mapping}),
            _type_bound=True
        )
        assert e.table is table 

def test_coerce_from_nulltype_type_coerce(self, connection):
        self._test_coerce_from_nulltype(type_coerce, connection) 

def test_insert_round_trip_type_coerce(self, connection):
        self._test_insert_round_trip(type_coerce, connection) 

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 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 __getitem__(self, index):
            super_ = super(ARRAY_D.Comparator, self).__getitem__(index)
            if not isinstance(index, slice) and self.type.dimensions > 1:
                super_ = type_coerce(
                    super_,
                    ARRAY_D(
                        self.type.item_type,
                        dimensions=self.type.dimensions - 1,
                        zero_indexes=self.type.zero_indexes)
                )
            return super_ 

def _set_table(self, schema, column, table):
        if not self.create_constraint:
            return

        variant_mapping = self._variant_mapping_for_set_table(column)

        e = schema.CheckConstraint(
            type_coerce(column, self).in_([0, 1]),
            name=_defer_name(self.name),
            _create_rule=util.portable_instancemethod(
                self._should_create_constraint,
                {"variant_mapping": variant_mapping}),
            _type_bound=True
        )
        assert e.table is table 

def test_vs_non_coerced_alias_type_coerce(self, connection):
        self._test_vs_non_coerced_alias(type_coerce, connection) 

def _set_table(self, schema, column, table):
        if not self.create_constraint:
            return

        variant_mapping = self._variant_mapping_for_set_table(column)

        e = schema.CheckConstraint(
            type_coerce(column, self).in_([0, 1]),
            name=_defer_name(self.name),
            _create_rule=util.portable_instancemethod(
                self._should_create_constraint,
                {"variant_mapping": variant_mapping}),
            _type_bound=True
        )
        assert e.table is table