自动过滤 SQLAlchemy 查询的正确方法? [英] The right way to auto filter SQLAlchemy queries?
问题描述
我刚刚使用 sqlalchemy 从 CRM 应用程序中反省了一个非常讨厌的架构.所有表上都有一个已删除的列,我想自动过滤所有标记为已删除的实体和关系.这是我想出的:
I've just introspected a pretty nasty schema from a CRM app with sqlalchemy. All of the tables have a deleted column on them and I wanted to auto filter all those entities and relations flagged as deleted. Here's what I came up with:
class CustomizableQuery(Query):
"""An overridden sqlalchemy.orm.query.Query to filter entities
Filters itself by BinaryExpressions
found in :attr:`CONDITIONS`
"""
CONDITIONS = []
def __init__(self, mapper, session=None):
super(CustomizableQuery, self).__init__(mapper, session)
for cond in self.CONDITIONS:
self._add_criterion(cond)
def _add_criterion(self, criterion):
criterion = self._adapt_clause(criterion, False, True)
if self._criterion is not None:
self._criterion = self._criterion & criterion
else:
self._criterion = criterion
它是这样使用的:
class UndeletedContactQuery(CustomizableQuery):
CONDITIONS = [contacts.c.deleted != True]
def by_email(self, email_address):
return EmailInfo.query.by_module_and_address('Contacts', email_address).contact
def by_username(self, uname):
return self.filter_by(twod_username_c=uname).one()
class Contact(object):
query = session.query_property(UndeletedContactQuery)
Contact.query.by_email('someone@some.com')
EmailInfo 是映射到电子邮件与其相关的其他模块之间的连接表的类.
EmailInfo is the class that's mapped to the join table between emails and the other Modules that they're related to.
以下是映射器的示例:
contacts_map = mapper(Contact, join(contacts, contacts_cstm), {
'_emails': dynamic_loader(EmailInfo,
foreign_keys=[email_join.c.bean_id],
primaryjoin=contacts.c.id==email_join.c.bean_id,
query_class=EmailInfoQuery),
})
class EmailInfoQuery(CustomizableQuery):
CONDITIONS = [email_join.c.deleted != True]
# More methods here
<小时>
这给了我我想要的,因为我已经过滤掉了所有已删除的联系人.我也可以在我的映射器中将其用作 dynamic_loader 的 query_class 参数 - 但是...
This gives me what I want in that I've filtered out all deleted Contacts. I can also use this as the query_class argument to dynamic_loader in my mappers - However...
- 有没有更好的方法可以做到这一点,我不太喜欢在像 Query 这样的复杂类的内部进行探索.
- 有没有人用他们可以分享的不同方式解决了这个问题?
推荐答案
您可以映射到选择.像这样:
You can map to a select. Like this:
mapper(EmailInfo, select([email_join], email_join.c.deleted == False))
这篇关于自动过滤 SQLAlchemy 查询的正确方法?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!