# 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](https://pip.pypa.io/en/stable/) to install datasiphon.
```bash
pip install datasiphon
```
## Usage
```python
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
```python
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
```python
# 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)
```python
# 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)
```python
# 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)
```python
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
```python
# 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
Raw data
{
"_id": null,
"home_page": "https://github.com/Nemulo/libs-datasiphon",
"name": "datasiphon",
"maintainer": null,
"docs_url": null,
"requires_python": "<4,>=3.10",
"maintainer_email": null,
"keywords": "database, sql, filtering, query",
"author": "Marek Nemeth",
"author_email": "99m.nemeth@gmail.com",
"download_url": "https://files.pythonhosted.org/packages/90/71/9570db17485b6633937ce27e6d0f5617b33801db36540d610c85ded9d882/datasiphon-0.3.9.tar.gz",
"platform": null,
"description": "# Datasiphon\n\nPackage for applying dictionary filter to some form of query on database to retrieve filtered data or acquire filtered query\n\n## Installation\n\nUse the package manager [pip](https://pip.pypa.io/en/stable/) to install datasiphon.\n\n```bash\npip install datasiphon\n```\n\n## Usage\n\n```python\nfrom datasiphon import SqlQueryBuilder\nimport sqlalchemy as sa\n# Create a filter\nfilter_ = {\n \"name\": {\"eq\": \"John\"},\n}\n\ntable = sa.Table(\"users\", sa.MetaData(), autoload=True, autoload_with=engine)\n# Build a query\nquery = table.select()\n\n# set up builder with table base\nbuilder = SqlQueryBuilder({\"users\": table})\n\n# build a query with filter\nnew_query = builder.build(query, filter_)\n```\n\n### Supported Database types\n## SQL package (No ORM)\n- implemented using `sqlalchemy` package, expected to work with `Table` and `Select` objects\n#### Building query\n1. Prerequisite\n - base `SELECT` query (`Select` object) from actual `Table` objects (not `text` objects)\n - filter (dictionary/`QsRoot` from `qstion` package), optional, optimally parsed using `qstion` package -> similiar to npm's `qs` package\n - restrictions (optional) - objects that restrict specific columns and operators that can be used in filter\n2. Usage\n```python\nfrom siphon import sql\n\n# Create a filter with strict form\nfilter_ = {\n \"name\": {\"eq\": \"John\"},\n}\n\n# build a query with filter\nnew_query = sql.SqlQueryBuilder({\"users\": table}).build(query, filter_)\n```\n- `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)\n - allowed format represents nestings containing one of :\n 1. junctions (AND, OR) -> for combining multiple conditions with desired logical operators\n ```python\n # Example correct - joining or with different fields\n filter_ = {\n \"or\":\n {\n \"name\": {\"eq\": \"John\"},\n \"age\": {\"gt\": 20}\n }\n }\n \n # example correct - joining or with same field, different operators\n filter_ = {\n \"name\": {\n \"or\": {\n \"eq\": \"John\",\n \"ne\": \"John\"\n }\n }\n }\n filter_ = {\n \"or\":\n {\n \"name\": {\"eq\": \"John\"},\n \"age\": {\"gt\": 20}\n },\n \"and\":\n {\n \"name\": {\"eq\": \"John\"},\n \"age\": {\"gt\": 20}\n }\n }\n ```\n 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)\n ```python\n # Example correct - applying eq operator on field name\n filter_ = {\n \"name\": {\"eq\": \"John\"}\n }\n\n # Example - incorrect - applying eq operator before field name\n filter_ = {\n \"eq\": {\n \"name\": \"John\"\n }\n }\n ```\n 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)\n ```python\n # Example correct - applying eq operator on field name\n filter_ = {\n \"name\": {\"eq\": \"John\"}\n }\n \n # Example - incorrect - applying eq operator before field name\n filter_ = {\n \"eq\": {\n \"name\": \"John\"\n }\n }\n ```\n - if using restriction model - builder will raise error when trying to apply operator that is restricted for given field (column)\n ```python\n from siphon import ColumnFilterRestriction, AnyValue\n from siphon.sql_filter import SQLEq, SQLNe\n # Example of correct restriction model usage\n # This restriction will forbid applying eq operator on field `name` - AnyValue signifies that any value is forbidden\n restriction = ColumnFilterRestriction(\n \"name\", SQLEq.generate_restriction(AnyValue)\n )\n # Example of specific value restriction\n # This restriction will forbid applying eq operator on field `name` with value \"John\"\n restriction = ColumnFilterRestriction(\n \"name\", SQLEq.generate_restriction(\"John\")\n )\n # Alternate approach to generate restriction\n restriction = ColumnFilterRestriction.from_dict(\n \"name\", {\"eq\": AnyValue}\n )\n restriction = ColumnFilterRestriction.from_dict(\n \"name\", {\"eq\": \"John\"}\n )\n\n # Applying restriction to builder\n builder = SqlQueryBuilder({\"users\": table})\n # Restrictions are optional positional argument\n builder.build(query, filter_, restriction)\n \n # different restriction for different column\n age_restriction = ColumnFilterRestriction(\n \"age\", SQLNe.generate_restriction(20)\n )\n builder.build(query, filter_, restriction, age_restriction)\n \n ```\n - using multiple condition without specifying junctions will result in an `AND` junction between them\n ```python\n # Example correct - applying eq operator on field name\n filter_ = {\n \"name\": {\"eq\": \"John\"},\n \"age\": {\"gt\": 20}\n }\n # will be treated as\n filter_ = {\n \"and\": {\n \"name\": {\"eq\": \"John\"},\n \"age\": {\"gt\": 20}\n }\n }\n\n filter_ = {\n \"name\": {\n \"eq\": \"John\",\n \"ne\": \"John\"\n }\n }\n # will be treated as\n filter_ = {\n \"and\": {\n \"name\": {\n \"eq\": \"John\",\n \"ne\": \"John\"\n }\n }\n }\n ```\n\n- generating query: recursively collecting items from filter, and applying filtering directly to exported columns of given query\n#### Manipulating `FilterExpression` object\n- `FilterExpression` object is a tree-like structure representing filter dictionary in a way that can be easily manipulated\n- Expressions can be added via `add_expression` method\n- Expressions can be replaced via `replace_expression` method\n- Expressions can be removed via `remove_expression` method\n- Expressions can be retrieved via `find_expression` method\n\n#### Reconstructing filter from `FilterExpression` and `SqlKeywordFilter` objects\n\n- 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\n- this objects can be manipulated directly to adjust filter or to be used in different context\n",
"bugtrack_url": null,
"license": null,
"summary": "Dynamic building of filtered database queries",
"version": "0.3.9",
"project_urls": {
"Bug Reports": "https://github.com/Nemulo/libs-datasiphon/issues",
"Documentation": "https://github.com/Nemulo/libs-datasiphon/blob/main/README.md",
"Homepage": "https://github.com/Nemulo/libs-datasiphon",
"Source": "https://github.com/Nemulo/libs-datasiphon"
},
"split_keywords": [
"database",
" sql",
" filtering",
" query"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "820699ce9a0fc54f4738b84fc639e2f6b270963ed7ca136166d1f4cc8736d117",
"md5": "d93ce9fb72b85c81fa3adc0ef11dc9a9",
"sha256": "4f7c8452464dc039b4f7e12bbbb0cd131770179ba2edef844fc546d2a52860c4"
},
"downloads": -1,
"filename": "datasiphon-0.3.9-py3-none-any.whl",
"has_sig": false,
"md5_digest": "d93ce9fb72b85c81fa3adc0ef11dc9a9",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": "<4,>=3.10",
"size": 14566,
"upload_time": "2025-01-20T11:04:21",
"upload_time_iso_8601": "2025-01-20T11:04:21.882631Z",
"url": "https://files.pythonhosted.org/packages/82/06/99ce9a0fc54f4738b84fc639e2f6b270963ed7ca136166d1f4cc8736d117/datasiphon-0.3.9-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "90719570db17485b6633937ce27e6d0f5617b33801db36540d610c85ded9d882",
"md5": "d3530d1022a06386aec99a9f72e42650",
"sha256": "dd2b8ae2962ce0ba262374ea62653d4455fb39b793c49336d591ea7ca41f76d5"
},
"downloads": -1,
"filename": "datasiphon-0.3.9.tar.gz",
"has_sig": false,
"md5_digest": "d3530d1022a06386aec99a9f72e42650",
"packagetype": "sdist",
"python_version": "source",
"requires_python": "<4,>=3.10",
"size": 22674,
"upload_time": "2025-01-20T11:04:24",
"upload_time_iso_8601": "2025-01-20T11:04:24.381406Z",
"url": "https://files.pythonhosted.org/packages/90/71/9570db17485b6633937ce27e6d0f5617b33801db36540d610c85ded9d882/datasiphon-0.3.9.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2025-01-20 11:04:24",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "Nemulo",
"github_project": "libs-datasiphon",
"travis_ci": false,
"coveralls": false,
"github_actions": false,
"lcname": "datasiphon"
}
JSON
