# 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/43/e0/b4d36bbfeb81b01aeab2bb88b99c6d37c289fcfb4cfa43c9fc022d7d1fcf/datasiphon-0.3.8.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.8",
"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": "5ffc5080b0a33a4e7903fddf0bf50bdfc4c94d302d25db0a5f914c152a52b082",
"md5": "c205e0162711918d84a9f5fd9756a386",
"sha256": "d647ef625c63377f766d134e90d325afb9384cff37a40bb8603eac0073d50920"
},
"downloads": -1,
"filename": "datasiphon-0.3.8-py3-none-any.whl",
"has_sig": false,
"md5_digest": "c205e0162711918d84a9f5fd9756a386",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": "<4,>=3.10",
"size": 14498,
"upload_time": "2024-10-02T14:02:56",
"upload_time_iso_8601": "2024-10-02T14:02:56.054619Z",
"url": "https://files.pythonhosted.org/packages/5f/fc/5080b0a33a4e7903fddf0bf50bdfc4c94d302d25db0a5f914c152a52b082/datasiphon-0.3.8-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "43e0b4d36bbfeb81b01aeab2bb88b99c6d37c289fcfb4cfa43c9fc022d7d1fcf",
"md5": "d6e28639b02d5d57196650994bcc9837",
"sha256": "5b417489968cb84529c8f33d16d701566fb2d86b1649597698897302e56d13d8"
},
"downloads": -1,
"filename": "datasiphon-0.3.8.tar.gz",
"has_sig": false,
"md5_digest": "d6e28639b02d5d57196650994bcc9837",
"packagetype": "sdist",
"python_version": "source",
"requires_python": "<4,>=3.10",
"size": 22168,
"upload_time": "2024-10-02T14:02:57",
"upload_time_iso_8601": "2024-10-02T14:02:57.466750Z",
"url": "https://files.pythonhosted.org/packages/43/e0/b4d36bbfeb81b01aeab2bb88b99c6d37c289fcfb4cfa43c9fc022d7d1fcf/datasiphon-0.3.8.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2024-10-02 14:02:57",
"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
