Name | datasiphon JSON |
Version |
0.2.2
JSON |
| download |
home_page | |
Summary | Dynamic building of filtered database queries |
upload_time | 2024-03-04 08:48:46 |
maintainer | |
docs_url | None |
author | |
requires_python | >=3.11 |
license | BSD-3-Clause |
keywords |
database
sql
filtering
query
|
VCS |
|
bugtrack_url |
|
requirements |
No requirements were recorded.
|
Travis-CI |
No Travis.
|
coveralls test coverage |
No coveralls.
|
# 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 siphon import sql
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()
# apply filter using build function
query = sql.SQL.build(query, filter_)
# execute query
result = engine.execute(query)
...
```
### 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), optional, optimally parsed using `qstion` package -> similiar to npm's `qs` package
- restriction model (child of `siphon.sql.RestrictionModel` class), optional, to restrict the filter to certain fields
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.SQL.build(query, filter_)
```
- `filter_` is validated before building the query, against columns used in select statement and restriction model (if provided)
- allowed format represents nestings containing one of :
1. junctions (AND, OR) -> for combining multiple conditions with desired logical operators (allowed exclusively per nest level)
```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"
}
}
}
# Example - incorrect - multiple junctions in same nest level
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, filter also can contain only fields in the restriction model, and operators used have to be in restriction model's field (RestrictionModel class has pretty strict validation for initialization and annotation and won't allow any faulty initialization or usage)
```python
# Example of correct restriction model usage
class UserRestrictionModel(sql.RestrictionModel):
# must always be annotated as `list[str]` otherwise will raise an error
name = list[str] = []
# must be always subset of all existing operators for that query builder
age = list[str] = ['eq']
# Example incorrect - using operator not in restriction model
class UserRestrictionModel(sql.RestrictionModel):
# does not have correct annotation
name = list[int] = []
# does not have correct operators
age = list[str] = ['eql']
```
- 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
Raw data
{
"_id": null,
"home_page": "",
"name": "datasiphon",
"maintainer": "",
"docs_url": null,
"requires_python": ">=3.11",
"maintainer_email": "",
"keywords": "database,sql,filtering,query",
"author": "",
"author_email": "Marek Nemeth <99m.nemeth@gmail.com>",
"download_url": "https://files.pythonhosted.org/packages/13/04/ed09330e8faf9e71d10a5f827412ccc387659aac564780f7831627f22d6b/datasiphon-0.2.2.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 siphon import sql\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# apply filter using build function\nquery = sql.SQL.build(query, filter_)\n# execute query\nresult = engine.execute(query)\n...\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), optional, optimally parsed using `qstion` package -> similiar to npm's `qs` package\n - restriction model (child of `siphon.sql.RestrictionModel` class), optional, to restrict the filter to certain fields\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.SQL.build(query, filter_)\n```\n- `filter_` is validated before building the query, against columns used in select statement and restriction model (if provided)\n - allowed format represents nestings containing one of :\n 1. junctions (AND, OR) -> for combining multiple conditions with desired logical operators (allowed exclusively per nest level)\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 # Example - incorrect - multiple junctions in same nest level\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, filter also can contain only fields in the restriction model, and operators used have to be in restriction model's field (RestrictionModel class has pretty strict validation for initialization and annotation and won't allow any faulty initialization or usage)\n ```python\n # Example of correct restriction model usage\n class UserRestrictionModel(sql.RestrictionModel):\n # must always be annotated as `list[str]` otherwise will raise an error\n name = list[str] = []\n # must be always subset of all existing operators for that query builder\n age = list[str] = ['eq']\n \n # Example incorrect - using operator not in restriction model\n class UserRestrictionModel(sql.RestrictionModel):\n # does not have correct annotation\n name = list[int] = []\n # does not have correct operators\n age = list[str] = ['eql']\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",
"bugtrack_url": null,
"license": "BSD-3-Clause",
"summary": "Dynamic building of filtered database queries",
"version": "0.2.2",
"project_urls": {
"Bug Tracker": "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"
},
"split_keywords": [
"database",
"sql",
"filtering",
"query"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "63883faf86b89dcf97ae5292c3b6b5c79a42d7e66d6b9921acf6c52a575aacb9",
"md5": "b55577055115bb4e60badc2c33704db2",
"sha256": "bddbc178c5ae11c102936f6c153f79ee1e00da105a95da99d2f2a51401ff208d"
},
"downloads": -1,
"filename": "datasiphon-0.2.2-py3-none-any.whl",
"has_sig": false,
"md5_digest": "b55577055115bb4e60badc2c33704db2",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": ">=3.11",
"size": 9154,
"upload_time": "2024-03-04T08:48:44",
"upload_time_iso_8601": "2024-03-04T08:48:44.751255Z",
"url": "https://files.pythonhosted.org/packages/63/88/3faf86b89dcf97ae5292c3b6b5c79a42d7e66d6b9921acf6c52a575aacb9/datasiphon-0.2.2-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
},
{
"comment_text": "",
"digests": {
"blake2b_256": "1304ed09330e8faf9e71d10a5f827412ccc387659aac564780f7831627f22d6b",
"md5": "d868eb3296e89c86d50c0c863985a462",
"sha256": "5b08fcb82f4059eea210d960ec0f1c554eae4311900b7549f6b6bedb54b6f6b0"
},
"downloads": -1,
"filename": "datasiphon-0.2.2.tar.gz",
"has_sig": false,
"md5_digest": "d868eb3296e89c86d50c0c863985a462",
"packagetype": "sdist",
"python_version": "source",
"requires_python": ">=3.11",
"size": 10540,
"upload_time": "2024-03-04T08:48:46",
"upload_time_iso_8601": "2024-03-04T08:48:46.062218Z",
"url": "https://files.pythonhosted.org/packages/13/04/ed09330e8faf9e71d10a5f827412ccc387659aac564780f7831627f22d6b/datasiphon-0.2.2.tar.gz",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2024-03-04 08:48:46",
"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"
}