Python sqlalchemy.tuple_() Examples

def test_tuple_clauselist_in(self):
        self.assert_compile(
            tuple_(table1.c.myid, table1.c.name).in_(
                [tuple_(table2.c.otherid, table2.c.othername)]
            ),
            "(mytable.myid, mytable.name) IN "
            "((myothertable.otherid, myothertable.othername))",
        )

        self.assert_compile(
            tuple_(table1.c.myid, table1.c.name).in_(
                select([table2.c.otherid, table2.c.othername])
            ),
            "(mytable.myid, mytable.name) IN (SELECT "
            "myothertable.otherid, myothertable.othername FROM myothertable)",
        ) 

def batch_load_fn(self, keys: 'List[tuple]') -> Promise:
        """
        Load related objects.

        Args:
            keys: Primary key values of parent model.

        Returns:
            Lists of related orm objects.

        """
        if len(self.parent_model_pk_fields) == 1:
            left_hand_side = self.parent_model_pk_fields[0]
            right_hand_side = [k[0] for k in keys]
        else:
            left_hand_side = tuple_(*self.parent_model_pk_fields)
            right_hand_side = keys

        query: 'Query' = self._get_query().filter(
            left_hand_side.in_(right_hand_side)
        )

        objects: 'Dict[tuple, Any]' = {
            self.parent_model_object_to_key(parent_object): getattr(
                parent_object, self.model_relation_field
            )
            for parent_object in query
        }
        return Promise.resolve(
            [objects.get(object_id, []) for object_id in keys]
        ) 

def test_expanding_parameter(self):
        self.assert_compile(
            tuple_(table1.c.myid, table1.c.name).in_(
                bindparam("foo", expanding=True)
            ),
            "(mytable.myid, mytable.name) IN ([POSTCOMPILE_foo])",
        )

        dialect = default.DefaultDialect()
        dialect.tuple_in_values = True
        self.assert_compile(
            tuple_(table1.c.myid, table1.c.name).in_(
                bindparam("foo", expanding=True)
            ),
            "(mytable.myid, mytable.name) IN ([POSTCOMPILE_foo])",
            dialect=dialect,
        )

        self.assert_compile(
            table1.c.myid.in_(bindparam("foo", expanding=True)),
            "mytable.myid IN ([POSTCOMPILE_foo])",
        ) 

def filter_many_objects_by_list_of_primary_keys(Model: DeclarativeMeta, entity_dicts: Sequence[dict]) -> BinaryExpression:
    """ Build an expression to load many objects from the database by their primary keys

    This function uses SQL tuples to build an expression which looks like this:

        SELECT * FROM users WHERE (uid, login) IN ((1, 'vdmit11'), (2, 'kolypto'));

    Example:

        entity_dicts = [
            {'id': 1, ...},
            {'id': 2, ...},
            ...
        ]
        ssn.query(models.User).filter(
            filter_many_objects_by_list_of_primary_keys(models.User, entity_dicts)
        )

    Args:
        Model: the model to query
        entity_dicts: list of entity dicts to pluck the PK values from

    Returns:
        The condition for filter()

    Raises:
        KeyError: one of `entity_dicts` did not contain a full primary key set of fields
    """
    pk_columns, pk_names = model_primary_key_columns_and_names(Model)

    # Build the condition: (primary-key-tuple) IN (....)
    # It uses sql tuples and the IN operator: (pk_col_a, pk_col_b, ...) IN ((val1, val2, ...), (val3, val4, ...), ...)
    # Thanks @vdmit11 for this beautiful approach!
    return sql_tuple(*pk_columns).in_(
        # Every object is represented by its primary key tuple
        tuple(entity_dict[pk_field] for pk_field in pk_names)
        for entity_dict in entity_dicts
    ) 

def operate(self, op, other):
        if isinstance(other, sa.sql.selectable.Select):
            return op(sa.tuple_(*self.keys), other)
        if not isinstance(other, CompositeId):
            other = CompositeId(other)
        return sa.and_(
            op(key, other_key)
            for key, other_key in zip(self.keys, other.keys)
        ) 

def get_select_prop(self, s, tail, params):
        pl = self.db.pagelinks
        page = self.db.page
        target_page = self.db.page.alias()
        nss = self.db.namespace_starname.alias()

        tail = tail.outerjoin(pl, page.c.page_id == pl.c.pl_from)
        tail = tail.outerjoin(target_page, (pl.c.pl_namespace == target_page.c.page_namespace) &
                                           (pl.c.pl_title == target_page.c.page_title))
        tail = tail.outerjoin(nss, pl.c.pl_namespace == nss.c.nss_id)

        s = s.column(pl.c.pl_namespace)
        s = s.column(pl.c.pl_title)
        s = s.column(nss.c.nss_name.label("target_nss_name"))

        # restrictions
        if "namespace" in params:
            namespace = params["namespace"]
            if not isinstance(namespace, set):
                namespace = {namespace}
            s = s.where(pl.c.pl_namespace.in_(namespace))
        if "titles" in params:
            titles = params["titles"]
            if not isinstance(titles, set):
                titles = {titles}
            pairs = set()
            for title in titles:
                title = self.db.Title(title)
                pairs.add( (title.namespacenumber, title.pagename) )
            s = s.where(sa.tuple_(pl.c.pl_namespace, pl.c.pl_title).in_(pairs))

        # order by
        if params["dir"] == "ascending":
            s = s.order_by(pl.c.pl_namespace.asc(), pl.c.pl_title.asc())
        else:
            s = s.order_by(pl.c.pl_namespace.desc(), pl.c.pl_title.desc())

        return s, tail 

def get_select_prop(self, s, tail, params):
        tl = self.db.templatelinks
        page = self.db.page
        target_page = self.db.page.alias()
        nss = self.db.namespace_starname.alias()

        tail = tail.outerjoin(tl, page.c.page_id == tl.c.tl_from)
        tail = tail.outerjoin(target_page, (tl.c.tl_namespace == target_page.c.page_namespace) &
                                           (tl.c.tl_title == target_page.c.page_title))
        tail = tail.outerjoin(nss, tl.c.tl_namespace == nss.c.nss_id)

        s = s.column(tl.c.tl_namespace)
        s = s.column(tl.c.tl_title)
        s = s.column(nss.c.nss_name.label("target_nss_name"))

        # restrictions
        if "namespace" in params:
            namespace = params["namespace"]
            if not isinstance(namespace, set):
                namespace = {namespace}
            s = s.where(tl.c.tl_namespace.in_(namespace))
        if "templates" in params:
            templates = params["templates"]
            if not isinstance(templates, set):
                templates = {templates}
            pairs = set()
            for template in templates:
                template = self.db.Title(template)
                pairs.add( (template.namespacenumber, template.pagename) )
            s = s.where(sa.tuple_(tl.c.tl_namespace, tl.c.tl_title).in_(pairs))

        # order by
        if params["dir"] == "ascending":
            s = s.order_by(tl.c.tl_namespace.asc(), tl.c.tl_title.asc())
        else:
            s = s.order_by(tl.c.tl_namespace.desc(), tl.c.tl_title.desc())

        return s, tail 

def _key_conditions(self, keys):
        vals = []
        for key in keys:
            row = self._key_orm.to_row(key)
            val = tuple(row[c] for c in self._key_cols)
            vals.append(val)
        return tuple_(*self._key_cols).in_(vals) 

def _key_conditions(self, keys):
        vals = []
        for key in keys:
            row = self._key_orm.to_row(key)
            val = tuple(row[c] for c in self._key_cols)
            vals.append(val)
        return tuple_(*self._key_cols).in_(vals) 

def test_with_tuples(self):
        a, b, c, d, b1, b1a, b1b, e, f = (
            column("a"),
            column("b"),
            column("c"),
            column("d"),
            column("b1"),
            column("b1a"),
            column("b1b"),
            column("e"),
            column("f"),
        )
        expr = tuple_(a, b, b1 == tuple_(b1a, b1b == d), c) > tuple_(
            func.go(e + f)
        )
        self._assert_traversal(
            expr,
            [
                (operators.gt, a, e),
                (operators.gt, a, f),
                (operators.gt, b, e),
                (operators.gt, b, f),
                (operators.eq, b1, b1a),
                (operators.eq, b1b, d),
                (operators.gt, c, e),
                (operators.gt, c, f),
            ],
        ) 

def test_tuple_expanding_in_no_values(self):
        expr = tuple_(table1.c.myid, table1.c.name).in_(
            [(1, "foo"), (5, "bar")]
        )
        self.assert_compile(
            expr,
            "(mytable.myid, mytable.name) IN " "([POSTCOMPILE_param_1])",
            checkparams={"param_1": [(1, "foo"), (5, "bar")]},
            check_post_param={"param_1": [(1, "foo"), (5, "bar")]},
            check_literal_execute={},
        )

        compiled = expr.compile()
        (
            to_update,
            replacement_expr,
        ) = compiled._literal_execute_expanding_parameter(
            "param_1", expr.right, [(1, "foo"), (5, "bar")]
        )
        eq_(
            to_update,
            [
                ("param_1_1_1", 1),
                ("param_1_1_2", "foo"),
                ("param_1_2_1", 5),
                ("param_1_2_2", "bar"),
            ],
        )
        eq_(
            replacement_expr,
            "(:param_1_1_1, :param_1_1_2), (:param_1_2_1, :param_1_2_2)",
        ) 

def test_tuple_expanding_in_values(self):
        expr = tuple_(table1.c.myid, table1.c.name).in_(
            [(1, "foo"), (5, "bar")]
        )
        dialect = default.DefaultDialect()
        dialect.tuple_in_values = True
        self.assert_compile(
            tuple_(table1.c.myid, table1.c.name).in_([(1, "foo"), (5, "bar")]),
            "(mytable.myid, mytable.name) IN " "([POSTCOMPILE_param_1])",
            dialect=dialect,
            checkparams={"param_1": [(1, "foo"), (5, "bar")]},
            check_post_param={"param_1": [(1, "foo"), (5, "bar")]},
            check_literal_execute={},
        )

        compiled = expr.compile(dialect=dialect)
        (
            to_update,
            replacement_expr,
        ) = compiled._literal_execute_expanding_parameter(
            "param_1", expr.right, [(1, "foo"), (5, "bar")]
        )
        eq_(
            to_update,
            [
                ("param_1_1_1", 1),
                ("param_1_1_2", "foo"),
                ("param_1_2_1", 5),
                ("param_1_2_2", "bar"),
            ],
        )
        eq_(
            replacement_expr,
            "VALUES (:param_1_1_1, :param_1_1_2), "
            "(:param_1_2_1, :param_1_2_2)",
        ) 

def load_many_instance_dicts(query: Query, pk_columns: Sequence[Column], entity_dicts: Sequence[EntityDictWrapper]) -> Sequence[EntityDictWrapper]:
    """ Given a list of wrapped entity dicts submitted by the client, load some of them from the database

    As the client submits a list of entity dicts, some of them may contain the primary key.
    This function loads them from the database with one query and returns a list of  EntityDictWrapper objects.

    Note that there will be three kinds of EntityDictWrapper objects: is_new, is_found, is_not_found:

    1. New: entity dicts without a primary key
    2. Found: entity dicts with a primary key that were also found in the database
    3. Not found: entity dicts with a primary key that were not found in the database

    NOTE: no errors are raised for instances that were not found by their primary key!

    Args:
        query: The query to load the instances with
        pk_columns: The list of primary key columns for the target model.
            Use model_primary_key_columns_and_names()
        entity_dicts: The list of entity dicts submitted by the user
    """
    # Load all instances by their primary keys at once
    # It uses sql tuples and the IN operator: (pk_col_a, pk_col_b, ...) IN ((val1, val2, ...), (val3, val4, ...), ...)
    # Thanks @vdmit11 for this beautiful approach!
    instances = query.filter(sql_tuple(*pk_columns).in_(
        # Search by PK tuples
        entity_dict.primary_key_tuple
        for entity_dict in entity_dicts
        if entity_dict.has_primary_key
    ))

    # Prepare a PK lookup object: we want to look up entity dicts by primary key tuples
    entity_dict_lookup_by_pk: Mapping[Tuple, EntityDictWrapper] = {
        entity_dict.primary_key_tuple: entity_dict
        for entity_dict in entity_dicts
        if entity_dict.has_primary_key
    }

    # Match instances with entity dicts
    for instance in instances:
        # Lookup an entity dict by its primary key tuple
        # We safely expect it to be there because objects were loaded by those primary keys in the first place :)
        entity_dict = entity_dict_lookup_by_pk[inspect(instance).identity]
        # Associate the instance with it
        entity_dict.loaded_instance = instance

    # Done
    return entity_dicts