datasiphon 0.3.6

Dynamic building of filtered database queries

Datasiphon

Package for applying dictionary filter to some form of query on database to retrieve filtered data or acquire filtered query

Installation

Use the package manager pip to install datasiphon.

pip install datasiphon

Usage

from datasiphon import SqlQueryBuilder
import sqlalchemy as sa
# Create a filter
filter_ = {
    "name": {"eq": "John"},
}

table = sa.Table("users", sa.MetaData(), autoload=True, autoload_with=engine)
# Build a query
query = table.select()

# set up builder with table base
builder = SqlQueryBuilder({"users": table})

# build a query with filter
new_query = builder.build(query, filter_)

Supported Database types

SQL package (No ORM)

• implemented using sqlalchemy package, expected to work with Table and Select objects

Building query

1. Prerequisite
   • base SELECT query (Select object) from actual Table objects (not text objects)
   • filter (dictionary/QsRoot from qstion package), optional, optimally parsed using qstion package -> similiar to npm's qs package
   • restrictions (optional) - objects that restrict specific columns and operators that can be used in filter
2. Usage

from siphon import sql

# Create a filter with strict form
filter_ = {
    "name": {"eq": "John"},
}

# build a query with filter
new_query = sql.SqlQueryBuilder({"users": table}).build(query, filter_)

• filter_ is validated before building the query, expecting specific format representing valid structure of applicable filter for given backend (currently only SQL backend is supported)
• allowed format represents nestings containing one of :

1. junctions (AND, OR) -> for combining multiple conditions with desired logical operators

       # Example correct - joining or with different fields
       filter_ = {
           "or":
           {
               "name": {"eq": "John"},
               "age": {"gt": 20}
           }
       }
       
       # example correct - joining or with same field, different operators
       filter_ = {
           "name": {
               "or": {
                   "eq": "John",
                   "ne": "John"
               }
           }
       }
       filter_ = {
           "or":
           {
               "name": {"eq": "John"},
               "age": {"gt": 20}
           },
           "and":
           {
               "name": {"eq": "John"},
               "age": {"gt": 20}
           }
       }
   

2. operators (eq, ne...) -> for applying conditions on fields -> must always follow a field name (not directly but always has to be nested deeper than field name)

   # Example correct - applying eq operator on field name
   filter_ = {
       "name": {"eq": "John"}
       }
   
   # Example - incorrect - applying eq operator before field name
   filter_ = {
       "eq": {
           "name": "John"
       }
   }
   

3. field name -> for applying conditions on fields -> must always contain an operator (not directly but always has to be nested deeper than field name)

   # Example correct - applying eq operator on field name
   filter_ = {
       "name": {"eq": "John"}
       }
   
   # Example - incorrect - applying eq operator before field name
   filter_ = {
       "eq": {
           "name": "John"
       }
   }
   

• if using restriction model - builder will raise error when trying to apply operator that is restricted for given field (column)

  from siphon import ColumnFilterRestriction, AnyValue
  from siphon.sql_filter import SQLEq, SQLNe
  # Example of correct restriction model usage
  # This restriction will forbid applying eq operator on field `name` - AnyValue signifies that any value is forbidden
  restriction = ColumnFilterRestriction(
      "name", SQLEq.generate_restriction(AnyValue)
  )
  # Example of specific value restriction
  # This restriction will forbid applying eq operator on field `name` with value "John"
  restriction = ColumnFilterRestriction(
      "name", SQLEq.generate_restriction("John")
  )
  # Alternate approach to generate restriction
  restriction = ColumnFilterRestriction.from_dict(
      "name", {"eq": AnyValue}
  )
  restriction = ColumnFilterRestriction.from_dict(
      "name", {"eq": "John"}
  )
  
  # Applying restriction to builder
  builder = SqlQueryBuilder({"users": table})
  # Restrictions are optional positional argument
  builder.build(query, filter_, restriction)
  
  # different restriction for different column
  age_restriction = ColumnFilterRestriction(
      "age", SQLNe.generate_restriction(20)
  )
  builder.build(query, filter_, restriction, age_restriction)
      
  

• using multiple condition without specifying junctions will result in an AND junction between them

  # Example correct - applying eq operator on field name
  filter_ = {
      "name": {"eq": "John"},
      "age": {"gt": 20}
      }
  # will be treated as
  filter_ = {
      "and": {
          "name": {"eq": "John"},
          "age": {"gt": 20}
      }
  }
  
  filter_ = {
      "name": {
          "eq": "John",
          "ne": "John"
          }
  }
  # will be treated as
  filter_ = {
      "and": {
          "name": {
              "eq": "John",
              "ne": "John"
          }
      }
  }
  

• generating query: recursively collecting items from filter, and applying filtering directly to exported columns of given query

Manipulating FilterExpression object

• FilterExpression object is a tree-like structure representing filter dictionary in a way that can be easily manipulated
• Expressions can be added via add_expression method
• Expressions can be replaced via replace_expression method
• Expressions can be removed via remove_expression method
• Expressions can be retrieved via find_expression method

Reconstructing filter from FilterExpression and SqlKeywordFilter objects

• since FilterExpression object is a tree-like structure builded originally from filter dictionary, it can be easily reconstructed along with SqlKeywordFilter object to represent the same filter as original dictionary
• this objects can be manipulated directly to adjust filter or to be used in different context