Skip to main content

A library of database convenience utilities, typically for creation of temporary files for processing large data.

Project description

Database convenience utilities.

PyPI Python 3.9 Python 3.10 Build Status

A library of database convenience utilities, typically for creation of temporary files for processing large data.


  • 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.


See the full documentation. The API reference is also available.


The easist way to install the command line program is via the pip installer:

pip3 install zensols.db

Binaries are also available on pypi.


A simple example is detailed below, and also found in the repo.

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:

-- 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 = ?;


Next, create the application context with a persister that is the SQL to client binding and call it app.conf:

# command line interaction
class_name = zensols.cli.ActionCliManager
apps = list: app

# the connection manager, which is the DB binding and in our case SQLite
class_name = zensols.db.SqliteConnectionManager
db_file = path: person.db

# the persister binds the API to the SQL
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
class_name = app.Application
persister = instance: person_persister


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:

from dataclasses import dataclass, field
from zensols.db import BeanDbPersister

class Person(object):
    name: str = field()
    age: int = field()
    id: int = field(default=None)

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

        # delete a row

        # 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

Create the entry point used on the command line and call it

from zensols.cli import CliHarness



$ ./ -h
Usage: [options]:

A people database.

  -h, --help      show this help message and exit
  --version       show the program version and exit

$ ./
(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 for more detailed examples of how to use the API.


An extensive changelog is available here.


MIT License

Copyright (c) 2020 - 2021 Paul Landes

Project details

Download files

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

Source Distributions

No source distribution files available for this release. See tutorial on generating distribution archives.

Built Distributions

zensols.db-1.0.0-py3.10.egg (29.8 kB view hashes)

Uploaded 1 0 0

zensols.db-1.0.0-py3-none-any.whl (13.3 kB view hashes)

Uploaded py3

Supported by

AWS AWS Cloud computing Datadog Datadog Monitoring Fastly Fastly CDN Google Google Object Storage and Download Analytics Microsoft Microsoft PSF Sponsor Pingdom Pingdom Monitoring Sentry Sentry Error logging StatusPage StatusPage Status page