scrippy-db

Generic database client for the Scrippy framework.

Requirements

• Python >= 3.11
• The following modules are installed automatically:
  • psycopg2-binary
  • mysql-connector-python

Installation

With pip

pip install scrippy-db

Manual installation

git clone https://codeberg.org/scrippy/scrippy-db.git
cd scrippy-db
python -m pip install -r requirements.txt
make install

Usage

The scrippy_db.db module provides the Database class, intended to be used as a context manager (with statement).

Connection

The Database constructor accepts the following parameters:

• db_type: The database type, one of pgsql, mysql or sqlite3 (required). Any other value raises a ScrippyDbError.
• database: The database name, or the database file path for sqlite3.
• username, password, host, port: Connection parameters (ignored by sqlite3).
• service: A PostgreSQL service name (pgsql only). When provided, all other connection parameters are ignored.

A new connection is opened for each query(), multi_query() or transaction() call and closed right after.

Connection chain

DbFromConnectionChain creates a Database from a single string following the format <DB_TYPE>:<USERNAME>/<PASSWORD>@<HOST>:<PORT>//<DATABASE>:

from scrippy_db.db import DbFromConnectionChain

with DbFromConnectionChain("pgsql:luiggi.vercotti/D3ADP4ARR0T@db.flying.circus:5432//monty_python") as db:
  ...

The password may contain any character. The username may not contain /, the host may not contain @ nor / and the port must be numeric. An invalid string raises a ScrippyDbError.

Simple query

Database.query() executes a single query and accepts the following parameters:

• query: The query (required). See query formatting.
• params: The query parameters, as a mapping (or a sequence, depending on the placeholder style). Optional.
• commit: Boolean. When True, the query is committed. Without it, any change is discarded. Default to False.
• verbose: Boolean. When True, the query, its parameters and the returned rows are sent to the logger. Default to False.
• exit_on_error: Boolean. When True, an error raises a ScrippyDbError after rollback. When False, the error is logged, the query is rollbacked and None is returned. Default to True.

It returns the rows as a list of tuples. The list is empty when the query returns no rows (INSERT, UPDATE, SELECT with no match...).

Multiple queries

Database.multi_query() executes the same query once per set of parameters. It is intended to insert or update data, not to retrieve it, and returns None. It accepts the following parameters:

• query: The query (required).
• paramlist: A list of parameter mappings, one per execution (required).
• commit: Boolean. When True, each execution is committed right after it. Default to False.
• verbose: Boolean. Same as for query(). Default to False.
• exit_on_error: Boolean. When True, an error raises a ScrippyDbError after rollback of the current execution. Default to False.

On error, the remaining executions are skipped. With commit=True, the executions preceding the error remain committed.

Transactions

Database.transaction() executes several queries as a single transaction: they are all committed if none fails, otherwise they are all rollbacked. It is intended to insert or update data, not to retrieve it, and returns None. It accepts the following parameters:

• queries: A list of dictionaries, each one with a query item (required) and a params item (optional mapping).
• verbose: Boolean. Same as for query(). Default to False.
• exit_on_error: Boolean. When True, an error raises a ScrippyDbError after rollback. Default to False.

Query formatting

To prevent SQL injection, queries must use placeholders and never embed the parameter values themselves. The placeholder format depends on the database type.

pgsql and mysql

Placeholders are written %(name)s. Parameters are provided as a dictionary whose keys match the placeholder names:

query = "INSERT INTO people VALUES(%(firstname)s, %(lastname)s, %(age)s)"
params = {"firstname": "Luiggi", "lastname": "Vercotti", "age": 42}

sqlite3

Placeholders are written :name. Parameters are provided as a dictionary whose keys match the placeholder names:

query = "INSERT INTO people VALUES(:firstname, :lastname, :age)"
params = {"firstname": "Luiggi", "lastname": "Vercotti", "age": 42}

Examples

from scrippy_db import ScrippyDbError, logger
from scrippy_db.db import Database

connection = {"db_type": "pgsql",
              "username": "luiggi.vercotti",
              "password": "D3ADP4ARR0T",
              "host": "db.flying.circus",
              "port": 5432,
              "database": "monty_python"}

# Simple query
with Database(**connection) as db:
  query = "INSERT INTO people VALUES(%(firstname)s, %(lastname)s, %(age)s)"
  params = {"firstname": "Harry", "lastname": "Fink", "age": 42}
  db.query(query=query, params=params, commit=True)

with Database(**connection) as db:
  query = "SELECT firstname, lastname, age FROM people WHERE age = %(age)s"
  rows = db.query(query=query, params={"age": 42})
  for firstname, lastname, age in rows:
    logger.info(f"{firstname} {lastname} is {age} years old")

# Multiple queries
with Database(**connection) as db:
  query = "INSERT INTO people VALUES(%(firstname)s, %(lastname)s, %(age)s)"
  paramlist = [{"firstname": "Harry", "lastname": "Fink", "age": 42},
               {"firstname": "Eric", "lastname": "Parline", "age": 50},
               {"firstname": None, "lastname": "Gumby", "age": 36}]
  db.multi_query(query=query, paramlist=paramlist, commit=True, exit_on_error=True)

# Transaction
with Database(**connection) as db:
  queries = [{"query": "INSERT INTO people VALUES(%(firstname)s, %(lastname)s, %(age)s)",
              "params": {"firstname": "Harry", "lastname": "Fink", "age": 42}},
             {"query": "DELETE FROM people WHERE age > %(age)s",
              "params": {"age": 30}}]
  db.transaction(queries=queries, exit_on_error=True)

# Error handling
with Database(**connection) as db:
  try:
    db.query(query="SELECT * FROM unknown_table")
  except ScrippyDbError as err:
    logger.error(err)