| Name | zensols.db JSON |
| Version |
1.3.1
JSON |
| download |
| home_page | https://github.com/plandes/dbutil |
| Summary | A library of database convenience utilities, typically for creation of temporary files for processing large data. |
| upload_time | 2024-03-14 18:25:02 |
| maintainer | |
| docs_url | None |
| author | Paul Landes |
| requires_python | |
| license | |
| keywords |
tooling
|
| VCS |
 |
| bugtrack_url |
|
| requirements |
No requirements were recorded.
|
| Travis-CI |
No Travis.
|
| coveralls test coverage |
No coveralls.
|
# Database convenience utilities.
[![PyPI][pypi-badge]][pypi-link]
[![Python 3.10][python310-badge]][python310-link]
[![Python 3.11][python311-badge]][python311-link]
[![Build Status][build-badge]][build-link]
A library of database convenience utilities, typically for creation of
temporary files for processing large data.
Features:
* DB-API Interface allows combined SQL rapid prototyping with backing
programmatic usage.
* Java Beans like persistence.
* Integration with [zensols.util stash].
* [SQLite] integration.
* [PostgreSQL] integration with the [dbutilpg] library.
* [Pandas] data frame creation, which is agnostic of database provider.
## Documentation
See the [full documentation](https://plandes.github.io/dbutil/index.html).
The [API reference](https://plandes.github.io/dbutil/api.html) is also
available.
## Obtaining
The easist way to install the command line program is via the `pip` installer:
```bash
pip3 install zensols.db
```
Binaries are also available on [pypi].
## Usage
A simple example is detailed below, and also found in the [repo](example).
### SQL binding file
First, create the SQL file, which is used to create and access the database.
Here we can replace `name, age` with `${cols}` and call it `person.sql`:
```sql
-- meta=init_sections=create_tables,create_idx
-- name=create_idx
create index person_name on person(name);
-- name=create_tables
create table person (name text, age int);
-- name=insert_person
insert into person (${cols}) values (?, ?);
-- name=select_people; note that the order is needed for the unit tests only
select ${cols}, rowid as id
from person
order by name;
-- name=select_people_by_id
select ${cols}, rowid as id from person where id = ?;
-- name=update_person
update person set name = ?, age = ? where rowid = ?;
-- name=delete_person
delete from person where rowid = ?;
```
### Persister
Next, create the application context with a persister that is the SQL to client
binding and call it `app.conf`:
```ini
# command line interaction
[cli]
class_name = zensols.cli.ActionCliManager
apps = list: app
# the connection manager, which is the DB binding and in our case SQLite
[sqlite_conn_manager]
class_name = zensols.db.SqliteConnectionManager
db_file = path: person.db
# the persister binds the API to the SQL
[person_persister]
class_name = zensols.db.DataClassDbPersister
bean_class = class: app.Person
sql_file = person.sql
conn_manager = instance: sqlite_conn_manager
insert_name = insert_person
select_name = select_people
select_by_id = select_people_by_id
update_name = update_person
delete_name = delete_person
# the application class invoked by the CLI
[app]
class_name = app.Application
persister = instance: person_persister
```
### Application
Define the *bean*, which provides the metadata for the `${cols}` in
`person.sql` and can (but not must) be used with the API to CRUD rows:
```python
from dataclasses import dataclass, field
from zensols.db import BeanDbPersister
@dataclass
class Person(object):
name: str = field()
age: int = field()
id: int = field(default=None)
@dataclass
class Application(object):
"""A people database"""
persister: BeanDbPersister
def demo(self):
# create a row using an instance of a dataclass and return the unique
# ID of the inserted row
paul_id: int = self.persister.insert(Person('Paul', 31))
# we can also insert by columns in the order given in the dataclass
jane_id: int = self.persister.insert_row('Jane', 32)
# print everyone in the database
print(self.persister.get())
# delete a row
self.persister.delete(paul_id)
print(self.persister.get())
# update jane's age
self.persister.update_row(jane_id, 'jane', 36)
# get the updated row we just set
jane = self.persister.get_by_id(jane_id)
print(f'jane: {jane}')
# clean up, which for SQLite deletes the file
self.persister.conn_manager.drop()
```
Create the entry point used on the command line and call it `run.py`:
```python
from zensols.cli import CliHarness
CliHarness(app_config_resource='app.conf').run()
```
### Run
```bash
$ ./run.py -h
Usage: run.py [options]:
A people database.
Options:
-h, --help show this help message and exit
--version show the program version and exit
$ ./run.py
(Person(name='Jane', age=32, id=2), Person(name='Paul', age=31, id=1))
(Person(name='Jane', age=32, id=2),)
jane: Person(name='jane', age=36, id=2)
```
See the [use cases](test/python/test_sqlite.py) for more detailed examples of
how to use the API.
## Changelog
An extensive changelog is available [here](CHANGELOG.md).
## Community
Please star this repository and let me know how and where you use this API.
Contributions as pull requests, feedback and any input is welcome.
## License
[MIT License](LICENSE.md)
Copyright (c) 2020 - 2023 Paul Landes
<!-- links -->
[pypi]: https://pypi.org/project/zensols.db/
[pypi-link]: https://pypi.python.org/pypi/zensols.db
[pypi-badge]: https://img.shields.io/pypi/v/zensols.db.svg
[python310-badge]: https://img.shields.io/badge/python-3.10-blue.svg
[python310-link]: https://www.python.org/downloads/release/python-3100
[python311-badge]: https://img.shields.io/badge/python-3.11-blue.svg
[python311-link]: https://www.python.org/downloads/release/python-3110
[build-badge]: https://github.com/plandes/dbutil/workflows/CI/badge.svg
[build-link]: https://github.com/plandes/dbutil/actions
[zensols.util stash]: https://github.com/plandes/util/blob/master/src/python/zensols/util/persist.py
[SQLite]: https://www.sqlite.org/index.html
[PostgreSQL]: https://www.postgresql.org
[dbutilpg]: https://github.com/plandes/dbutilpg
[Pandas]: https://pandas.pydata.org
Raw data
{
"_id": null,
"home_page": "https://github.com/plandes/dbutil",
"name": "zensols.db",
"maintainer": "",
"docs_url": null,
"requires_python": "",
"maintainer_email": "",
"keywords": "tooling",
"author": "Paul Landes",
"author_email": "landes@mailc.net",
"download_url": "https://github.com/plandes/dbutil/releases/download/v1.3.1/zensols.db-1.3.1-py3-none-any.whl",
"platform": null,
"description": "# Database convenience utilities.\n\n[![PyPI][pypi-badge]][pypi-link]\n[![Python 3.10][python310-badge]][python310-link]\n[![Python 3.11][python311-badge]][python311-link]\n[![Build Status][build-badge]][build-link]\n\nA library of database convenience utilities, typically for creation of\ntemporary files for processing large data.\n\nFeatures:\n* DB-API Interface allows combined SQL rapid prototyping with backing\nprogrammatic usage.\n* Java Beans like persistence.\n* Integration with [zensols.util stash].\n* [SQLite] integration.\n* [PostgreSQL] integration with the [dbutilpg] library.\n* [Pandas] data frame creation, which is agnostic of database provider.\n\n\n## Documentation\n\nSee the [full documentation](https://plandes.github.io/dbutil/index.html).\nThe [API reference](https://plandes.github.io/dbutil/api.html) is also\navailable.\n\n\n## Obtaining\n\nThe easist way to install the command line program is via the `pip` installer:\n```bash\npip3 install zensols.db\n```\n\nBinaries are also available on [pypi].\n\n\n## Usage\n\nA simple example is detailed below, and also found in the [repo](example).\n\n\n### SQL binding file\n\nFirst, create the SQL file, which is used to create and access the database.\nHere we can replace `name, age` with `${cols}` and call it `person.sql`:\n\n```sql\n-- meta=init_sections=create_tables,create_idx\n\n-- name=create_idx\ncreate index person_name on person(name);\n\n-- name=create_tables\ncreate table person (name text, age int);\n\n-- name=insert_person\ninsert into person (${cols}) values (?, ?);\n\n-- name=select_people; note that the order is needed for the unit tests only\nselect ${cols}, rowid as id\n from person\n order by name;\n\n-- name=select_people_by_id\nselect ${cols}, rowid as id from person where id = ?;\n\n-- name=update_person\nupdate person set name = ?, age = ? where rowid = ?;\n\n-- name=delete_person\ndelete from person where rowid = ?;\n```\n\n### Persister\n\nNext, create the application context with a persister that is the SQL to client\nbinding and call it `app.conf`:\n\n```ini\n# command line interaction\n[cli]\nclass_name = zensols.cli.ActionCliManager\napps = list: app\n\n# the connection manager, which is the DB binding and in our case SQLite\n[sqlite_conn_manager]\nclass_name = zensols.db.SqliteConnectionManager\ndb_file = path: person.db\n\n# the persister binds the API to the SQL\n[person_persister]\nclass_name = zensols.db.DataClassDbPersister\nbean_class = class: app.Person\nsql_file = person.sql\nconn_manager = instance: sqlite_conn_manager\ninsert_name = insert_person\nselect_name = select_people\nselect_by_id = select_people_by_id\nupdate_name = update_person\ndelete_name = delete_person\n\n# the application class invoked by the CLI\n[app]\nclass_name = app.Application\npersister = instance: person_persister\n```\n\n\n### Application\n\nDefine the *bean*, which provides the metadata for the `${cols}` in\n`person.sql` and can (but not must) be used with the API to CRUD rows:\n\n```python\nfrom dataclasses import dataclass, field\nfrom zensols.db import BeanDbPersister\n\n\n@dataclass\nclass Person(object):\n name: str = field()\n age: int = field()\n id: int = field(default=None)\n\n\n@dataclass\nclass Application(object):\n \"\"\"A people database\"\"\"\n\n persister: BeanDbPersister\n\n def demo(self):\n # create a row using an instance of a dataclass and return the unique\n # ID of the inserted row\n paul_id: int = self.persister.insert(Person('Paul', 31))\n\n # we can also insert by columns in the order given in the dataclass\n jane_id: int = self.persister.insert_row('Jane', 32)\n\n # print everyone in the database\n print(self.persister.get())\n\n # delete a row\n self.persister.delete(paul_id)\n print(self.persister.get())\n\n # update jane's age\n self.persister.update_row(jane_id, 'jane', 36)\n\n # get the updated row we just set\n jane = self.persister.get_by_id(jane_id)\n print(f'jane: {jane}')\n\n # clean up, which for SQLite deletes the file\n self.persister.conn_manager.drop()\n```\n\nCreate the entry point used on the command line and call it `run.py`:\n\n```python\nfrom zensols.cli import CliHarness\n\nCliHarness(app_config_resource='app.conf').run()\n```\n\n\n### Run\n\n```bash\n$ ./run.py -h\nUsage: run.py [options]:\n\nA people database.\n\nOptions:\n -h, --help show this help message and exit\n --version show the program version and exit\n\n$ ./run.py\n(Person(name='Jane', age=32, id=2), Person(name='Paul', age=31, id=1))\n(Person(name='Jane', age=32, id=2),)\njane: Person(name='jane', age=36, id=2)\n```\n\nSee the [use cases](test/python/test_sqlite.py) for more detailed examples of\nhow to use the API.\n\n\n## Changelog\n\nAn extensive changelog is available [here](CHANGELOG.md).\n\n\n## Community\n\nPlease star this repository and let me know how and where you use this API.\nContributions as pull requests, feedback and any input is welcome.\n\n\n## License\n\n[MIT License](LICENSE.md)\n\nCopyright (c) 2020 - 2023 Paul Landes\n\n\n<!-- links -->\n[pypi]: https://pypi.org/project/zensols.db/\n[pypi-link]: https://pypi.python.org/pypi/zensols.db\n[pypi-badge]: https://img.shields.io/pypi/v/zensols.db.svg\n[python310-badge]: https://img.shields.io/badge/python-3.10-blue.svg\n[python310-link]: https://www.python.org/downloads/release/python-3100\n[python311-badge]: https://img.shields.io/badge/python-3.11-blue.svg\n[python311-link]: https://www.python.org/downloads/release/python-3110\n[build-badge]: https://github.com/plandes/dbutil/workflows/CI/badge.svg\n[build-link]: https://github.com/plandes/dbutil/actions\n\n[zensols.util stash]: https://github.com/plandes/util/blob/master/src/python/zensols/util/persist.py\n[SQLite]: https://www.sqlite.org/index.html\n\n[PostgreSQL]: https://www.postgresql.org\n[dbutilpg]: https://github.com/plandes/dbutilpg\n[Pandas]: https://pandas.pydata.org\n",
"bugtrack_url": null,
"license": "",
"summary": "A library of database convenience utilities, typically for creation of temporary files for processing large data.",
"version": "1.3.1",
"project_urls": {
"Download": "https://github.com/plandes/dbutil/releases/download/v1.3.1/zensols.db-1.3.1-py3-none-any.whl",
"Homepage": "https://github.com/plandes/dbutil"
},
"split_keywords": [
"tooling"
],
"urls": [
{
"comment_text": "",
"digests": {
"blake2b_256": "c0835184a0b57cae724680089046aa7bc9ff77f9e1d16be1a1012568bcd97fbd",
"md5": "c5155e2be1eb4282ced0f37ff234363f",
"sha256": "41cfaf5cde65089db3e228c80218a434e5a03c627175be9f849711559a6a5280"
},
"downloads": -1,
"filename": "zensols.db-1.3.1-py3-none-any.whl",
"has_sig": false,
"md5_digest": "c5155e2be1eb4282ced0f37ff234363f",
"packagetype": "bdist_wheel",
"python_version": "py3",
"requires_python": null,
"size": 16256,
"upload_time": "2024-03-14T18:25:02",
"upload_time_iso_8601": "2024-03-14T18:25:02.569706Z",
"url": "https://files.pythonhosted.org/packages/c0/83/5184a0b57cae724680089046aa7bc9ff77f9e1d16be1a1012568bcd97fbd/zensols.db-1.3.1-py3-none-any.whl",
"yanked": false,
"yanked_reason": null
}
],
"upload_time": "2024-03-14 18:25:02",
"github": true,
"gitlab": false,
"bitbucket": false,
"codeberg": false,
"github_user": "plandes",
"github_project": "dbutil",
"travis_ci": false,
"coveralls": false,
"github_actions": true,
"lcname": "zensols.db"
}
