classic-db-tools 0.1.0
Classic DB tools
Requires Python: >=3.10
Keywords: Jinja2, SQL, Python, Template

# Classic DB Tools

Идея библиотеки заключается в попытке работать с SQL-запросами как с шаблонами Jinja.
Вдохновлено [embrace](https://pypi.org/project/embrace/) и 
[jinjasql](https://pypi.org/project/jinjasql/), оттуда же бралась часть кода.

## Установка:
```shell
pip install classic-db-tools
```

## Quickstart:

```python
from classic.db_tools import Engine, ConnectionPool
import psycopg

pool = ConnectionPool(psycopg.connect)
engine = Engine('path/to/sql/templates/dir', pool)

# При входе движок займет соединение в пуле,
# на выходе, по дефолту, закоммитит
with engine:
    # Применим схему:
    engine.query_from('tasks/ddl.sql').execute()

    # Сохранение данных
    engine.query_from('tasks/save.sql').executemany([
        {'title': 'Some Task', 'body': 'Do something'},
        {'title': 'Another Task', 'body': 'Do anything'},
    ])

    # Получение данных
    task = engine.query_from('tasks/get_by_id.sql').one(id=1)
    # (1, 'Some Task', 'Do something')
```

В директории sql рядом с .py файлом надо разместить 3 файла
(можно найти в директории test/example):

`sql/tasks/ddl.sql`:
```sql
CREATE TABLE tasks (
    id serial PRIMARY KEY,
    title text,
    body text
);
```

`sql/tasks/get_by_id.sql`:
```sql
SELECT id, title, body FROM tasks WHERE id = %(id)s;
```

`sql/tasks/save.sql`:
```sql
INSERT INTO tasks (title, body) VALUES (%(title)s, %(body)s);
```


## Управление подключениями и транзакциями
Библиотека рассчитана на 2 способа управления подключениями, внешний, 
по отношению к библиотеке, и внутренний.

Внешний - это когда библиотека не управляет подключениями напрямую,
а управление происходит снаружи, вручную, или во внешнем фреймворке.
В этом случае следует создавать курсор вручную, и передавать в методы Engine,
в свойство _cursor:
```python
cursor = some_connection.cursor()
engine.query('SELECT 1').scalar(_cursor=cursor)
```

Название свойства, начинающееся с _, может немного смутить, но, на самом деле,
_ не означает приватность, он добавлен для того,
чтобы уменьшить вероятность пересечения с названием свойства из запроса.

Второй способ, "Внутренний" - это когда объект Engine управляет подключениями
самостоятельно. Engine является менеджером контекста. При входе контекст Engine
возьмет подключение из пула, запомнит его для текущего потока (thread-local),
при выходе, по умолчанию, произведет .commit у подключения, затем вернет его в 
пул. Либо, в случае ошибок внутри контекста, произведет .rollback при выходе.
Пример:
```python
with engine:
    engine.query('SELECT 1').scalar()
```

Поведение при ошибках в контексте можно изменить,
задав параметр конструктора Engine commit_on_exit=False:
```python
engine = Engine('./queries', some_pool, commit_on_exit=False)
```


## Запросы
Объект Engine дает 2 способа задать запрос - из файла, и напрямую.
Также запросы, могут быть статическими или динамическими.


#### Запросы в файлах (.query_from)
Для работы с запросами в файлах существует метод .query_from:
```python
query = engine.query_from('path/to/some/file.sql')
query.execute()
```

Метод .query_from ищет файл относительно каждого пути,
переданного в параметр template_paths конструктора Engine, в том же порядке,
в каком пути были переданы, до первого раза, когда файл будет найден.

Также каждый взятый зарос заносится в кеш. Это значит, что загрузка запроса
из файла будет происходить лишь единожды.

Также .query_from является ленивым, то есть реальная загрузка запроса
из файла произойдет уже при исполнении запроса.


### Запросы, формируемые в коде (.query)
В случае, когда запрос формируется динамически в python-коде, можно использовать
метод .query.

```python
query = engine.query('SELECT 1')
query.execute()
```

### Статические запросы
Статические запросы передаются в драйвер при исполнении как есть,
без каких-либо преобразований.

Метод .query_from считает статическими запросами файлы, названия которых
заканчиваются на `.sql`.

Чтобы создать статический запрос через метод .query, нужно передать параметр
static=True:
```python
query = engine.query('SELECT {{ value }}', static=True)
query.execute(value=1)
```

По умолчанию Engine считает, что запросы, передаваемые в .query - динамические.
Можно изменить это поведение, установив нужное значение
в параметр str_templates_static_by_default конструктора Engine.

Статические запросы очень легковесны, потому рекомендуется по умолчанию 
выражать запросы статично везде, где это возможно.


### Динамические запросы
Динамические запросы, в отличие от статических, являются шаблонами Jinja.
При исполнении шаблон запроса будет собран, результат передан драйверу.

Метод .query_from считает динамическими запросами файлы, названия которых
заканчиваются на `.sql.tmpl`.

Чтобы создать статический запрос через метод .query, нужно передать параметр
static=False:
```python
query = engine.query('SELECT {{ value }}', static=False)
query.execute(value=1)
```

По умолчанию Engine считает, что запросы, передаваемые в .query - динамические.
Можно изменить это поведение, установив нужное значение
в параметр str_templates_static_by_default конструктора Engine.


#### Сборка шаблонов
В целом, при сборке доступны все возможности Jinja, но есть и особенность.

Каждый placeholder Jinja оборачивается в фильтр bind.
Следовательно, эти 2 примера кода эквивалентны:
```jinja
SELECT {{ value }}
```
и
```jinja
SELECT {{ value|bind }}
```

Фильтр bind вставляет вместо реального значения плейсхолдер, подходящий для
драйвера, с которым идет работа.
Шаблон из предыдущего примера, в случае применения psycopg,
будет скомпилирован в:
```sql
SELECT %(value)s
```

Таким образом, устраняется поле для SQL-инъекций, но, в то же время, 
утяжеляется сборка запроса.

Если необходимо отрендерить значение 'как есть', 
например, если оно было получено из безопасного источника,
можно применить фильтр safe:
```jinja
SELECT {{ value|sqlsafe }}
```

Также, для случая, когда необходимо передавать названия объектов БД
(схем, таблиц, столбцов и прочих), есть фильтр `identifier`:
```jinja
SELECT * FROM {{ table|identifier }}
```

В разных БД идентификаторы выделяются разными знаками препинания.
К примеру, Postgres использует двойные кавычки:
```sql
SELECT * FROM "public"."some_table"
```
MS SQL Server использует `:
```sql
SELECT * FROM `public`.`some_table`
```

Изменить это можно в параметре identifier_quote_char 
конструктора Engine. По умолчанию используются двойные кавычки.


## Выдача значений
Объект Query предоставляет несколько способов вернуть результаты запроса.
По дефолту запрос возвращает наружу то, что возвращает драйвер.
ДЛя изменения типа можно обратиться к документации драйвера,
а можно использовать маппинг. Подробно маппинг описывается ниже.

Метод .all() вернет список всех значений результатов. Реализация использует
метод .fetchall() у курсора. Например:
```python
for row in engine.query(
    'SELECT * FROM some_table'
).all():
    print(row)
```

Метод .iter вернет итератор по батчам. Реализация вызывает .fetchmany() 
у курсора, буферизует результат, и выдает наружу записи по одной. Размер батча
можно задать параметром _batch:
```python
for row in engine.query(
        'SELECT * FROM some_table'
).iter(batch=100):
    print(row)
```

Метод .one() вернет одно (первое) значение или None. Реализация использует
метод .fetchone() у курсора. Например:
```python
row = engine.query(
    'SELECT * FROM some_table ' 
    'WHERE id = %(id)s').one(id=id)
print(row)
```

Метод .scalar() вернет одно (первое) значение или None. Реализация использует
метод .fetchone() у курсора. Например:
```python
name = engine.query(
    'SELECT name FROM some_table '
    'WHERE id = %(id)s'
).scalar(id=id)
print(name)
```

Также есть метод .rowcount(), возвращающий количество обработанных строк 
из курсора. Реализация не получает результаты. Например:
```python
rowcount = engine.query(
    'DELETE FROM some_table'
).rowcount()
print(rowcount)
```
Этот метод удобен для логгирования работы приложения.

Для случая, когда результаты запроса не важны, либо требуется работа с курсором
вручную, есть метод .execute(). Он возвращает курсор после выполнения запроса:
```python
cursor = engine.query('SELECT * FROM some_table').execute()
```

Также есть метод .executemany() для множественного исполнения:
```python
engine.query(
    'INSERT INTO some_table(id, value)'
    'VALUES (%(id)s, %(value)s)'
).executemany([
    dict(id=1, value=1),
    dict(id=2, value=2),
])
```

Есть разница в поведении метода со статическими и динамическими запросами.

При статическом запросе произойдет вызов .executemany() у курсора, куда будет
передан запрос "как есть", и набор переданных параметров.

При динамическом запросе для каждого юнита в наборе переданных параметров
произойдет сборка запроса, выполнение и ожидание результата, потому этот метод 
может работать медленно.

Все методы, кроме .executemany(), принимают аргументы по одному и 
тому же принципу.

В случае, когда ваша программа работает с набором данных в виде словарей,
удобно передавать словари в запрос в виде неименованного аргумента:
```python
some_obj = dict(id=1, value=1)
engine.query(
    'INSERT INTO some_table(id, value)'
    'VALUES (%(id)s, %(value)s)'
).execute(some_obj)
```
Также можно переопределить какое-либо значение:
```python
some_obj = dict(id=1, value=1)
engine.query(
    'INSERT INTO some_table(id, value)'
    'VALUES (%(id)s, %(value)s)'
).execute(some_obj, value=2)  # Вставится 1, 2
```

Для случая, когда набор параметров задается статично, или из разных источников,
удобнее использовать **kwargs:
```python
engine.query(
    'INSERT INTO some_table(id, value)'
    'VALUES (%(id)s, %(value)s)'
).execute(id=1, value=1)
```

Также каждому методу (включая .executemany()) можно переопределить курсор, 
отвязав исполнение от Engine для случаев, 
когда необходимо управлять коннектами самостоятельно:
```python
cursor = some_conn.cursor()
engine.query(
    'INSERT INTO some_table(id, value)'
    'VALUES (%(id)s, %(value)s)'
).execute(id=1, value=1, _cursor=cursor)
```


## Маппинг


### Маппинг на классы


### Маппинг на словари


### Кастомные идентификаторы


### Композитные ключи


## Отложенные операции


## ScopedConnection


## Transaction