Skip to main content

Dynamic building of filtered database queries

Project description

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

Project details


Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Source Distribution

datasiphon-0.3.9.tar.gz (22.7 kB view details)

Uploaded Source

Built Distribution

datasiphon-0.3.9-py3-none-any.whl (14.6 kB view details)

Uploaded Python 3

File details

Details for the file datasiphon-0.3.9.tar.gz.

File metadata

  • Download URL: datasiphon-0.3.9.tar.gz
  • Upload date:
  • Size: 22.7 kB
  • Tags: Source
  • Uploaded using Trusted Publishing? No
  • Uploaded via: twine/6.0.1 CPython/3.13.1

File hashes

Hashes for datasiphon-0.3.9.tar.gz
Algorithm Hash digest
SHA256 dd2b8ae2962ce0ba262374ea62653d4455fb39b793c49336d591ea7ca41f76d5
MD5 d3530d1022a06386aec99a9f72e42650
BLAKE2b-256 90719570db17485b6633937ce27e6d0f5617b33801db36540d610c85ded9d882

See more details on using hashes here.

File details

Details for the file datasiphon-0.3.9-py3-none-any.whl.

File metadata

  • Download URL: datasiphon-0.3.9-py3-none-any.whl
  • Upload date:
  • Size: 14.6 kB
  • Tags: Python 3
  • Uploaded using Trusted Publishing? No
  • Uploaded via: twine/6.0.1 CPython/3.13.1

File hashes

Hashes for datasiphon-0.3.9-py3-none-any.whl
Algorithm Hash digest
SHA256 4f7c8452464dc039b4f7e12bbbb0cd131770179ba2edef844fc546d2a52860c4
MD5 d93ce9fb72b85c81fa3adc0ef11dc9a9
BLAKE2b-256 820699ce9a0fc54f4738b84fc639e2f6b270963ed7ca136166d1f4cc8736d117

See more details on using hashes here.

Supported by

AWS AWS Cloud computing and Security Sponsor Datadog Datadog Monitoring Fastly Fastly CDN Google Google Download Analytics Pingdom Pingdom Monitoring Sentry Sentry Error logging StatusPage StatusPage Status page