George V. Reilly wrote:
> We're using SQLAlchemy sharding to partition accounts across a couple
> of databases. We want to add more partitions, but first we need to
> eliminate some unnecessary cross-partition queries.
>
> class FindShardableId(sqlalchemy.sql.ClauseVisitor):
> def __init__(self, ids, key_fields, get_shard):
> self.ids = ids
> self.key_fields = key_fields
> self.get_shard = get_shard
>
> def _check_side(self, binary, side, other_side):
> has_constant = (isinstance(side, sqlalchemy.Column)
> and side.name in self.key_fields
> and binary.operator ==
> sqlalchemy.sql.operators.eq
> and getattr(other_side, "value", None))
> if has_constant:
> self.ids.append(self.get_shard(other_side.value))
> return has_constant
>
> def visit_binary(self, binary):
> if not self._check_side(binary, binary.left,
> binary.right):
> # Lazy load properties tend to be reversed, with the
> constant on the left
> self._check_side(binary, binary.right, binary.left)
>
> def query_chooser(query):
> ids = []
> if query._criterion is not None:
> FindShardableId(
> ids,
> set(["account_id", "account_guid"]),
> lambda account_id:
> shard_manager.shard_id_from_guid(account_id)
> ).traverse(query._criterion)
>
> if len(ids) == 0:
> logging.warn("\n\nExecuting query against all shards; "
> "this may not be optimal:\n
> \t{0}".format(str(query)))
> return shards.keys()
> else:
> return ids
>
> This works well most of the time, but we're finding that some queries
> do not have a "value". These are all of the form
>
> SELECT shopping_list_items.version AS shopping_list_items_version
> FROM shopping_list_items
> WHERE shopping_list_items.account_id = :param_1
> AND shopping_list_items.shopping_list_item_id = :param_2
>
> and :param1 is of the form _BindParamClause(u'%(63636624 param)s',
> None, type_=UUID())
>
> Typically, I'm seeing this come out of the innards of SQLAlchemy,
> as one of several queries triggered by, say, a session.merge().
The only Query() I can see getting generated that would have non-valued
bindparams would be during a _get(). The values should be present in
query._params. If you need more info I can dig in to recall how the keys
of that dictionary are formatted in this case.
>
> How do we work around this?
>
> Thanks!
> /George V. Reilly, Seattle
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to [email protected].
> To unsubscribe from this group, send email to
> [email protected].
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
