Lightweight library for providing filtering mechanism for your APIs using SQLAlchemy

Overview

example workflow example workflow codecov

sqlalchemy-filters-plus is a light-weight extendable library for filtering queries with sqlalchemy.

Install

pip install sqlalchemy-fitlers-plus

Usage

This library provides an easy way to filter your SQLAlchemy queries, which can for example be used by your users as a filtering mechanism for your exposed models via an API.

Let's define an example of models that will be used as a base query.

from sqlalchemy import Column, Date, Integer, String, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import relationship, backref

Base = declarative_base()


class User(Base):
    id = Column(Integer, primary_key=True)
    email = Column(String)
    age = Column(Integer)
    birth_date = Column(Date, nullable=False)


class Article(Base):
    id = Column(Integer, primary_key=True)
    title = Column(String)
    user_id = Column(Integer, ForeignKey(User.id), nullable=False)
    user = relationship(
        User,
        uselist=False,
        lazy="select",
        backref=backref("articles", uselist=True, lazy="select"),
    )

Define your first filter

Let's then define our first Filter class for the Article model

from sqlalchemy_filters import Filter, StringField
from sqlalchemy_filters.operators import ContainsOperator


class ArticleFilter(Filter):
    title = StringField(lookup_operator=ContainsOperator)
    email = StringField(field_name="user.email")

    class Meta:
        model = Article
        session = my_sqlalchemy_session

The example above defines a new filter class attached to the Article model, we declared two fields to filter with, title with the lookup_operator ContainsOperator and an email field which points to the user's email, hence the field_name="user.email" without any lookup_operator (default value is EqualsOperator) that will be used to filter with on the database level. We will see other operators that can also be used.

To apply the filter class, we instantiate it and pass it the data(as a dictionary) to filter with.

my_filter = ArticleFilter(data={"email": "[email protected]", "title": "python"})
query = my_filter.apply()  # query is a SQLAlchemy Query object

Please read the full documentation here https://sqlalchemy-filters-plus.readthedocs.io/

You might also like...
flask-apispec MIT flask-apispec (🥉24 · ⭐ 520) - Build and document REST APIs with Flask and apispec. MIT

flask-apispec flask-apispec is a lightweight tool for building REST APIs in Flask. flask-apispec uses webargs for request parsing, marshmallow for res

Flask + marshmallow for beautiful APIs

Flask-Marshmallow Flask + marshmallow for beautiful APIs Flask-Marshmallow is a thin integration layer for Flask (a Python web framework) and marshmal

A template for Flask APIs.
A template for Flask APIs.

FlaskAPITempate A template for a Flask API. Why tho? I just wanted an easy way to create a Flask API. How to setup First, use the template. You can do

Seamlessly serve your static assets of your Flask app from Amazon S3

flask-s3 Seamlessly serve the static assets of your Flask app from Amazon S3. Maintainers Flask-S3 is maintained by @e-dard, @eriktaubeneck and @SunDw

Flask-Bcrypt is a Flask extension that provides bcrypt hashing utilities for your application.

Flask-Bcrypt Flask-Bcrypt is a Flask extension that provides bcrypt hashing utilities for your application. Due to the recent increased prevelance of

A basic JSON-RPC implementation for your Flask-powered sites
A basic JSON-RPC implementation for your Flask-powered sites

Flask JSON-RPC A basic JSON-RPC implementation for your Flask-powered sites. Some reasons you might want to use: Simple, powerful, flexible and python

:rocket: Generate a Postman collection from your Flask application
:rocket: Generate a Postman collection from your Flask application

flask2postman A tool that creates a Postman collection from a Flask application. Install $ pip install flask2postman Example Let's say that you have a

Adds GraphQL support to your Flask application.

Flask-GraphQL Adds GraphQL support to your Flask application. Usage Just use the GraphQLView view from flask_graphql from flask import Flask from flas

A basic JSON-RPC implementation for your Flask-powered sites
A basic JSON-RPC implementation for your Flask-powered sites

Flask JSON-RPC A basic JSON-RPC implementation for your Flask-powered sites. Some reasons you might want to use: Simple, powerful, flexible and python

Comments
  • ArgumentError on pre-joined query

    ArgumentError on pre-joined query

    I'm not totally sure what's causing this so apologies for the somewhat vague issue. Hopefully you'll have a better idea!

    I have a query with a couple of joins on it already and when I attempt to filter it, I'm getting the following exception:

      File "/usr/local/lib/python3.9/site-packages/sqlalchemy_filters/filters.py", line 563, in apply
        query = self._apply_join(query)
      File "/usr/local/lib/python3.9/site-packages/sqlalchemy_filters/filters.py", line 364, in _apply_join
        if is_already_joined(query, join[0]):
      File "/usr/local/lib/python3.9/site-packages/sqlalchemy_filters/utils.py", line 37, in is_already_joined
        return join_table in [_[0] for _ in query._legacy_setup_joins]
      File "/usr/local/lib/python3.9/site-packages/sqlalchemy/sql/operators.py", line 366, in __eq__
        return self.operate(eq, other)
      File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/attributes.py", line 317, in operate
        return op(self.comparator, *other, **kwargs)
      File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/relationships.py", line 1302, in __eq__
        self.property._optimized_compare(
      File "/usr/local/lib/python3.9/site-packages/sqlalchemy/orm/relationships.py", line 1690, in _optimized_compare
        raise sa_exc.ArgumentError(
    sqlalchemy.exc.ArgumentError: Mapped instance expected for relationship comparison to object.   Classes, queries and other SQL elements are not accepted in this context; for comparison with a subquery, use DataSource.owner.has(**criteria).
    
    opened by TWeatherston 3
  • Using filter on query with a join creates a duplicate join

    Using filter on query with a join creates a duplicate join

    I have these two related models eg:

    class Organisation(Base):
        __tablename__ = "organisations"
        id = Column(INTEGER, primary_key=True)
        name = Column(VARCHAR(64))
    
    
    class User(Base):
        __tablename__ = "users"
        id = Column(INTEGER, primary_key=True)
        name = Column(VARCHAR(64))
        email = Column(VARCHAR(64))
        organisation_id = Column(Integer, ForeignKey("organisations.id"))
        organisation = relationship("Organisation")
    

    And then a filter for filtering users:

    class UserFilter(Filter):
        name = Field()
        email = Field(lookup_operator=ContainsOperator)
        organisation = Field(field_name="organisation.name")
    
        class Meta:
            model = User
    

    If I then create a query with a join and attempt to apply the filter to the query:

    query = db_session.query(User).join(Organisation)
    my_filter = UserFilter(data={"organisation": "test_org"}, query=query)
    query = my_filter.apply()
    print(str(query))
    

    It creates this second joining of the organisations table:

    SELECT 
        users.id AS users_id, 
        users.name AS users_name, 
        users.email AS users_email, 
        users.organisation_id AS users_organisation_id
    FROM users 
        JOIN organisations ON organisations.id = users.organisation_id
        JOIN organisations ON organisations.id = users.organisation_id
    WHERE organisations.name = ?
    
    opened by TWeatherston 1
  • Ordering by UnaryExpression raises TypeError

    Ordering by UnaryExpression raises TypeError

    Hey,

    Really love the package, great work! :+1:

    I've just been having some difficulty using the order_by with a UnaryExpression. Example from the docs:

    MyFilter(data={"order_by": User.first_name.asc()})
    

    This results in:

    TypeError: Boolean value of this clause is not defined

    It seems that this is the offending line. Would it be possible to get this changed to something like this?

    order_by = self.data.get("order_by", self._order_by)
    

    Here's a minimal working example that I was using to test this:

    from sqlalchemy import Column, VARCHAR, INTEGER, create_engine
    from sqlalchemy.orm import declarative_base, Session
    from sqlalchemy_filters import Filter, Field
    
    
    Base = declarative_base()
    
    
    class User(Base):
        __tablename__ = "users"
        id = Column(INTEGER, primary_key=True)
        name = Column(VARCHAR(64))
        email = Column(VARCHAR(64))
    
    
    class UserFilter(Filter):
        name = Field()
    
        class Meta:
            model = User
    
    
    engine = create_engine("sqlite://")
    with Session(engine) as session:
        query = session.query(User)
        my_filter = UserFilter(data={"order_by": User.name.desc()}, query=query)
        query = my_filter.apply()
    

    Thanks!

    opened by TWeatherston 1
  • Doesn't seem to apply the specific filter

    Doesn't seem to apply the specific filter

    Hi, Using your library, and this seemed to be not filtering with address_id and business_id base_filter = ScreeningHistoryFilter(data={"address_id":f"{address_id}", "business_id":f"{business_id}", "page":pagination_params.page_index, "page_size": pagination_params.page_size, "order_by": sort_params.sort_fields}, session= self.db)

    Below is the filter class: `class ScreeningHistoryFilter(Filter): address_id: Field() business_id: Field() # start_range: DateTimeField(field_name ='create_tz', lookup_operator = GTEOperator) # end_range: DateTimeField(field_name ='create_tz', lookup_operator = LTEOperator)

    class Meta:
        model = V_Address_Screening_History`
    
    opened by jaballe 4
Releases(1.1.5)
Owner
Karami El Mehdi
Karami El Mehdi
Full-Stack application that visualizes amusement park safety.

Amusement Park Ride Safety Analysis Project Proposal We have chosen to look into amusement park data to explore ride safety relationships visually, in

Michael Absher 0 Jul 11, 2021
Flask starter template for better structuring.

Flask Starter app Flask starter template for better structuring. use the starter plate step 1 : cloning this repo through git clone the repo git clone

Tirtharaj Sinha 1 Jul 26, 2022
A Flask extension that enables or disables features based on configuration.

Flask FeatureFlags This is a Flask extension that adds feature flagging to your applications. This lets you turn parts of your site on or off based on

Rachel Greenfield 131 Sep 26, 2022
Are-You-OK is a Flask-based, responsive Web App to monitor whether the Internet Service you care about is still working.

Are-You-OK Are-You-OK is a Flask-based, responsive Web App to monitor whether the Internet Service you care about is still working. Demo-Preview Get S

Tim Qiu 1 Oct 28, 2021
flask extension for integration with the awesome pydantic package

Flask-Pydantic Flask extension for integration of the awesome pydantic package with Flask. Installation python3 -m pip install Flask-Pydantic Basics v

249 Jan 06, 2023
Browsable web APIs for Flask.

Flask API Browsable web APIs for Flask. Status: This project is in maintenance mode. The original author (Tom Christie) has shifted his focus to API S

Flask API 1.3k Jan 05, 2023
É uma API feita em Python e Flask que pesquisa informações em uma tabela .xlsx e retorna o resultado.

API de rastreamento de pacotes É uma API feita em Python e Flask que pesquisa informações de rastreamento de pacotes em uma tabela .xlsx e retorna o r

Marcos Beraldo Barros 4 Jun 27, 2021
A Flask wrapper of Starknet state. Similar in purpose to Ganache.

Introduction A Flask wrapper of Starknet state. Similar in purpose to Ganache. Aims to mimic Starknet's Alpha testnet, but with simplified functionali

Shard Labs 159 Jan 04, 2023
A Flask application for Subdomain Enumeration

subdomainer-flask A Flask application for Subdomain Enumeration steps to be done git clone https://github.com/gokulapap/subdomainer-flask pip3 install

GOKUL A.P 7 Sep 22, 2022
A clean and simple blog system based on Flask and MongoDB

CleanBlog A clean and simple blog system based on Flask and MongoDB You can access CleanBlog This is the source code of Flask Tutorial Pro,you can buy

shin 107 Oct 06, 2022
docker-compose uWSGI nginx flask

docker-compose uWSGI nginx flask Note that this was tested on CentOS 7 Usage sudo yum install docker

Abdolkarim Saeedi 3 Sep 11, 2022
Search users in Github. Created with Flask, PipEnv, Heroku and free time.

Search in Github Here search for users in Github and other stuff! This app is working with, Data Github API BackEnd Flask Language Python Package mana

AmirHossein Mohammadi 12 Jan 16, 2022
Rate Limiting extension for Flask

Flask-Limiter Flask-Limiter provides rate limiting features to flask routes. It has support for a configurable backend for storage with current implem

Ali-Akber Saifee 922 Jan 08, 2023
Geometry Dash Song Bypass with Python Flask Server

Geometry Dash Song Bypass with Python Flask Server

pixelsuft‮ 1 Nov 16, 2021
Pf-flask-rest-com - Flask REST API Common Implementation by Problem Fighter Library

In the name of God, the Most Gracious, the Most Merciful. PF-Flask-Rest-Com Docu

Problem Fighter 3 Jan 15, 2022
A web application that consists of a collection of board games

PyBoardGame About This website contains a collection of board games for users to enjoy, as well as various guides for the games. The web app is built

Larry Shi 0 Aug 11, 2021
Cross Origin Resource Sharing ( CORS ) support for Flask

Flask-CORS A Flask extension for handling Cross Origin Resource Sharing (CORS), making cross-origin AJAX possible. This package has a simple philosoph

Cory Dolphin 803 Jan 01, 2023
A simple way to demo Flask apps from your machine.

flask-ngrok A simple way to demo Flask apps from your machine. Makes your Flask apps running on localhost available over the internet via the excellen

117 Dec 27, 2022
A boilerplate Flask API for a Fullstack Project :rocket:

Flask Boilerplate to quickly get started with production grade flask application with some additional packages and configuration prebuilt.

Yasser Tahiri 32 Dec 24, 2022
Flask-template - A simple template for make an flask api

flask-template By GaGoU :3 a simple template for make an flask api notes: you ca

GaGoU 2 Feb 17, 2022