anodb 7.2

Convenient Wrapper around AioSQL and a Database Connection

AnoDB

Convenient Wrapper around aiosql and a Database Connection.

Description

This class creates a persistent database connection and imports SQL queries from a file as simple Python functions.

If the connection is broken, a new connection is attempted.

Compared to aiosql, the point is not to need to pass a connection as an argument on each call: The DB class embeds both connection and query methods.

For concurrent programming (threads, greenlets…), a relevant setup should also consider thread-locals and pooling issues at some higher level.

Example

Install the module with pip install anodb or whatever method you like. Once available:

import anodb
# parameters: driver, connection string, SQL file
db = anodb.DB("sqlite3", "test.db", "test.sql")

db.do_some_insert(key=1, val="hello")
db.do_some_update(key=1, val="world")
print("data", db.do_some_select(key=1))
db.commit()

db.close()

With file test.sql containing something like:

-- name: do_some_select
SELECT * FROM Stuff WHERE key = :key;

-- name: do_some_insert!
INSERT INTO Stuff(key, val) VALUES (:key, :val);

-- name: do_some_update!
UPDATE Stuff SET val = :val WHERE key = :key;

Documentation

The anodb module provides the DB class which embeds both a PEP 249 database connection (providing methods commit, rollback, cursor, close and its connect counterpart to re-connect) and SQL queries wrapped into dynamically generated functions by aiosql. Such functions may be loaded from a string (add_queries_from_str) or a path (add_queries_from_path).

The DB constructor parameters are:

• db the name of the database driver: sqlite3, psycopg, pymysql, see aiosql documentation for a list of supported drivers.
• conn an optional connection string used to initiate a connection with the driver. For instance, psycopg accepts a libpq connection string such as: "host=db1.my.org port=5432 dbname=acme user=calvin …".
• queries an optional path name from which to read query definitions.
• options an optional dictionary or string to pass additional connection parameters.
• auto_reconnect whether to attempt a reconnection if the connection is lost. Default is True.
• debug whether to generate debugging messages. Default is False.
• other named parameters are passed as additional connection parameters.

import anodb

db = anodb.DB("sqlite3", ":memory:", "acme-queries.sql")
db = anodb.DB("psycopg", "host=localhost dbname=acme", "acme-queries.sql")

Versions

Sources, documentation and issues are available on GitHub.

Latest version is 7.2 on 2022-12-11.

See all versions

TODO

• make it work with "with", i.e. provide relevant enter and exit
• sync drivers with aiosql?
• add something about caching?
• sync version numbering with aiosql?