sqlalchemyfilter是一个帮助程序库,用于对sqlalchemy查询执行过滤
sqlalchemy-filter的Python项目详细描述
使用
sqlalchemy-filter
可用于生成类似于django-filter
的接口
图书馆。例如,如果您有一个Post模型,您可以创建一个
用以下代码筛选它:
fromsqlalchemy_filterimportFilter,fieldsfromappimportmodelsclassPostFilter(Filter):from_date=fields.DateField(field_name="pub_date",lookup_type=">=")to_date=fields.DateField(field_name="pub_date",lookup_type="<=")is_published=fields.BooleanField()title=fields.Field(lookup_type="==")title_like=fields.Field(lookup_type="like")title_ilike=fields.Field(lookup_type="ilike")data=fields.JsonField(lookup_type="#>>",lookup_path="{foo,0}",not_equal=True)category=fields.Field(relation_model="Category",field_name="name",lookup_type="in")order=fields.OrderField()classMeta:model=models.Post
在你看来,你可以:
^{pr2}$上面的代码将执行如下查询:
SELECTpost.idASpost_id,post.titleASpost_title,post.pub_dateASpost_pub_date,post.is_publishedASpost_is_published,post.category_idASpost_category_idFROMpostJOINcategoryONcategory.id=post.category_idWHEREcategory.nameIN('Category 1')ORDERBYpost.titleASC,post.idDESC
注意事项:
您应该自己验证过滤器参数,并将已经验证的参数传递给filter_query func,
此外,您还应该像上面的示例Post.query.join(Category)
手动创建所需的连接
字段类的可能查找类型:
['==', '<', '>', '<=', '>=', '!=', 'in', 'not_in', 'like', 'ilike', 'notlike', 'notilike']
DateField和DateTimeField类的可能查找类型:
['==', '<', '>', '<=', '>=', '!=']
JsonField类的可能查找类型:
['->>', '#>>']
JsonField用法示例:
fromappimportmodels,dbpost=models.Post(data={"title":"Title 1","is_published":True,"tags":[{"name":"IT"},{"name":"Biology"}]})db.session.add(post)db.session.commit()
fromsqlalchemy_filterimportfields,FilterfromappimportmodelsclassPostFilter(Filter):not_title=fields.JsonField(field_name='data',lookup_type='->>',lookup_path='title',not_equal=True)is_published=fields.JsonField(field_name='data',lookup_type='->>',lookup_path='is_published')tag=fields.JsonField(field_name='data',lookup_type='#>>',lookup_path='{tags, 0, name}')classMeta:model=models.Post
找到帖子标题!=标题1
PostFilter().filter_query(models.Post.query,{"not_title":"Title 1"}).all()
SELECT*FROMpostWHERE(post.data->>"title")!="Title 1"
查找发布位置==True的帖子
PostFilter().filter_query(models.Post.query,{"is_published":"true"}).all()
SELECT*FROMpostWHERE(post.data->>"is_published")="true"
查找第一个标记名==IT的帖子
PostFilter().filter_query(models.Post.query,{"tag":'IT'}).all()
SELECT*FROMpostWHERE(post.data#>>"{tags, 0, name}")="IT"
与烧瓶一起使用
以下示例包含与烧瓶的集成:
fromflask.viewsimportMethodViewfromsqlalchemy_filter.mixinsimportFilterSetMixinfromapp.filtersimportPostFilterclassPostAPI(MethodView,FilterSetMixin):filter_class=PostFilterdefget(self,*args,**kwargs):base_query=Post.queryfilter_params={...}filtered_query=self.filter_query(base_query,filter_params)return{"posts":filtered_query.all()}
- 项目
标签: