Metadata-Version: 2.4
Name: scrippy-db
Version: 3.0.0
Summary: Generic database client for the Scrippy framework
Home-page: https://codeberg.org/scrippy/scrippy-db
Author: Michael Costa, Florent Chevalier
Author-email: michael.costa@mcos.nc, florent.chevalier.nc@gmail.com
License: MIT
Classifier: Programming Language :: Python :: 3
Classifier: Operating System :: OS Independent
Classifier: Topic :: Software Development :: Libraries :: Application Frameworks
Classifier: Intended Audience :: Developers
Classifier: Intended Audience :: System Administrators
Classifier: Environment :: Console
Classifier: Development Status :: 5 - Production/Stable
Requires-Python: >=3.11
Description-Content-Type: text/markdown
License-File: LICENSE
Requires-Dist: psycopg2-binary>=2.9.10
Requires-Dist: mysql-connector-python>=26.7.0
Provides-Extra: dev
Requires-Dist: twine; extra == "dev"
Requires-Dist: build; extra == "dev"
Requires-Dist: wheel>=0.37.0; extra == "dev"
Requires-Dist: flake8>=4.0.1; extra == "dev"
Requires-Dist: flake8-breakpoint>=1.1.0; extra == "dev"
Requires-Dist: flake8-builtins>=1.5.3; extra == "dev"
Requires-Dist: flake8-print>=4.0.0; extra == "dev"
Requires-Dist: flake8-return>=1.1.3; extra == "dev"
Requires-Dist: pep8-naming>=0.8.2; extra == "dev"
Requires-Dist: pytest>=6.2.2; extra == "dev"
Requires-Dist: pytest-cov>=3.0.0; extra == "dev"
Requires-Dist: bump2version>=1.0.1; extra == "dev"
Requires-Dist: setuptools>=60.9.2; extra == "dev"
Requires-Dist: pypirc-chappers>=1.0.8; extra == "dev"
Requires-Dist: pylint>=2.12.2; extra == "dev"
Dynamic: license-file


![Build Status](https://drone-ext.mcos.nc/api/badges/scrippy/scrippy-db/status.svg) ![License](https://img.shields.io/static/v1?label=license&color=orange&message=MIT) ![Language](https://img.shields.io/static/v1?label=language&color=informational&message=Python)

![Scrippy, my scrangourou friend](./scrippy-db.png "Scrippy, my scrangourou friend")

# `scrippy-db`

Generic database client for the [`Scrippy`](https://codeberg.org/scrippy) framework.

## Requirements

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

## Installation

### With `pip`

```bash
pip install scrippy-db
```

### Manual installation

```bash
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](https://www.postgresql.org/docs/current/libpq-pgservice.html) 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>`:

```python
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](#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:

```python
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:

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

### Examples

```python
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)
```
