classic-db-tools 2.0.2

Classic DB tools

Classic DB Tools

Идея библиотеки заключается в попытке работать с SQL-запросами как с шаблонами Jinja. Вдохновлено embrace и jinjasql, оттуда же бралась часть кода.

Установка:

pip install classic-db-tools

Quickstart:

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:

CREATE TABLE tasks (
    id serial PRIMARY KEY,
    title text,
    body text
);

sql/tasks/get_by_id.sql:

SELECT id, title, body FROM tasks WHERE id = %(id)s;

sql/tasks/save.sql:

INSERT INTO tasks (title, body) VALUES (%(title)s, %(body)s);

Управление подключениями и транзакциями

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

Внешний - это когда библиотека не управляет подключениями напрямую, а управление происходит снаружи, вручную, или во внешнем фреймворке. В этом случае следует создавать курсор вручную, и передавать в методы Engine, в свойство _cursor:

cursor = some_connection.cursor()
engine.query('SELECT 1').scalar(_cursor=cursor)

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

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

with engine:
    engine.query('SELECT 1').scalar()

Поведение при ошибках в контексте можно изменить, задав параметр конструктора Engine commit_on_exit=False:

engine = Engine('./queries', some_pool, commit_on_exit=False)

Запросы

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

Запросы в файлах (.query_from)

Для работы с запросами в файлах существует метод .query_from:

query = engine.query_from('path/to/some/file.sql')
query.execute()

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

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

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

Запросы, формируемые в коде (.query)

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

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

Статические запросы

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

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

Чтобы создать статический запрос через метод .query, нужно передать параметр static=True:

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:

query = engine.query('SELECT {{ value }}', static=False)
query.execute(value=1)

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

Сборка шаблонов

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

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

SELECT {{ value }}

и

SELECT {{ value|bind }}

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

SELECT %(value)s

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

Если необходимо отрендерить значение 'как есть', например, если оно было получено из безопасного источника, можно применить фильтр safe:

SELECT {{ value|sqlsafe }}

Также, для случая, когда необходимо передавать названия объектов БД (схем, таблиц, столбцов и прочих), есть фильтр identifier:

SELECT * FROM {{ table|identifier }}

В разных БД идентификаторы выделяются разными знаками препинания. К примеру, Postgres использует двойные кавычки:

SELECT * FROM experiments."some_table"

MS SQL Server использует `:

SELECT * FROM `public`.`some_table`

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

Выдача значений

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

Метод .all() вернет список всех значений результатов. Реализация использует метод .fetchall() у курсора. Например:

for row in engine.query(
    'SELECT * FROM some_table'
).all():
    print(row)

Метод .iter вернет итератор по батчам. Реализация вызывает .fetchmany() у курсора, буферизует результат, и выдает наружу записи по одной. Размер батча можно задать параметром _batch:

for row in engine.query(
        'SELECT * FROM some_table'
).iter(batch=100):
    print(row)

Метод .one() вернет одно (первое) значение или None. Реализация использует метод .fetchone() у курсора. Например:

row = engine.query(
    'SELECT * FROM some_table ' 
    'WHERE id = %(id)s').one(id=id)
print(row)

Метод .scalar() вернет одно (первое) значение или None. Реализация использует метод .fetchone() у курсора. Например:

name = engine.query(
    'SELECT name FROM some_table '
    'WHERE id = %(id)s'
).scalar(id=id)
print(name)

Также есть метод .rowcount(), возвращающий количество обработанных строк из курсора. Реализация не получает результаты. Например:

rowcount = engine.query(
    'DELETE FROM some_table'
).rowcount()
print(rowcount)

Этот метод удобен для логгирования работы приложения.

Для случая, когда результаты запроса не важны, либо требуется работа с курсором вручную, есть метод .execute(). Он возвращает курсор после выполнения запроса:

cursor = engine.query('SELECT * FROM some_table').execute()

Также есть метод .executemany() для множественного исполнения:

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(), принимают аргументы по одному и тому же принципу.

В случае, когда ваша программа работает с набором данных в виде словарей, удобно передавать словари в запрос в виде неименованного аргумента:

some_obj = dict(id=1, value=1)
engine.query(
    'INSERT INTO some_table(id, value)'
    'VALUES (%(id)s, %(value)s)'
).execute(some_obj)

Также можно переопределить какое-либо значение:

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:

engine.query(
    'INSERT INTO some_table(id, value)'
    'VALUES (%(id)s, %(value)s)'
).execute(id=1, value=1)

Также каждому методу (включая .executemany()) можно переопределить курсор, отвязав исполнение от Engine для случаев, когда необходимо управлять подключениями самостоятельно:

cursor = conn.cursor()
engine.query(
    'INSERT INTO some_table(id, value)'
    'VALUES (%(id)s, %(value)s)'
).execute(id=1, value=1, _cursor=cursor)

Маппинг

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

Для примера предположим, что у нас есть класс Task и таблица applications:

from dataclasses import dataclass

@dataclass
class Task:
    id: int
    name: str
    description: str

CREATE TABLE applications(
    pk      integer PRIMARY KEY,
    title   varchar,
    content varchar 
)

В примере названия таблицы и столбцов намеренно расходятся с названиями класса и полей, чтобы продемонстрировать отсутствие каких-либо автоматических привязок к именам, все делается "вручную".

Чтобы смаппить данные, необходимо указать в запросе label для каждого столбца, состоящий из префикса и названия поля, соединенные через _:

SELECT
    pk      AS SomeObj__id,
    title   AS SomeObj__name,
    content AS SomeObj__description
FROM some_table;

Нужно помнить, что SQL - регистронезависим, поэтому нет разницы, в каком регистре писать префиксы и поля. Библиотека внутри все имена переведет в нижний регистр.

Затем нужно объявить маппинг и сделать запрос::

from classic.db_tools import Engine, Entity

mapping = dict(
    someobj=Entity(Task, 'id'),
)

engine = Engine(
    some_con_pool,
    templates_dirs='some/sql/dir',
    default_mapping=mapping,
)

with engine:
    task = engine.query_from(
        'example_select.sql',
    ).map_to(Task).one(id=1)

print(task)

Названия полей маппера, подаваемые в словарь mapping, должны соответствовать префиксам из запроса. Значения, содержащиеся в mapping - это параметры маппера, они могут быть Entity и Value. В любом случае, первым аргументов подается класс, на который осуществляется маппинг.

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

Объекты-сущности при маппинге сопоставляются через идентификатор, поэтому если запрос выдает несколько строк с одним и тем же идентификатором, будет инстанцирован только один объект-сущность с таким идентификатором.

Value применяется для объектов-значений. Это такие объекты в предметной области, которые не имеют идентификатора, и различаются по полному составу полей. Как правило, это подчиненные, дочерние по отношению к каким-либо сущностям объекты. Вторым неименованным аргументом можно подать bool

Объекты-значения при маппинге не сопоставляются друг с другом вообще, маппер просто инстанцирует такие классы каждый раз, когда соответствующие строки встречаются в полученном наборе данных.

Также бывает случай, когда во все поля сущности приходит NULL из БД. Эта ситуация двоякая - с одной стороны, во многих случаях нужно вернуть None вместо объекта, с другой - бывают случаи, когда все поля объекта содержат None, и объект при этом "легален" и имеет смысл для бизнеса. По умолчанию маппер сокращает до None такие объекты, но это поведение можно изменить, указав вторым неименованным аргументом False:

mapper = dict(
    some_val=Value(SomeObj, False)
)

map_to

Метод map_to имеет 3 аргумента - result, prefix и mapper. Result и prefix связаны друг с другом.

Если подается только result (желаемый класс), то в качестве префикса будет использоваться название класса, а класс в result будет использован для аннотаций типов.

obj = engine.query_from('test.sql').map_to(Task).one()
# obj - Task с точки зрения typing

Если подается prefix, то он будет использован в качестве префикса, result будет использован для аннотаций типов:

obj = engine.query_from('test.sql').map_to(Task, 'task').one()
# obj - Task с точки зрения typing

Если не подан result, а только prefix - prefix будет использован в качестве префикса, но аннотаций типов уже не будет.

obj = engine.query_from('test.sql').map_to('task').one()
# obj - Any с точки зрения typing

Если не указать ни то, ни другое, маппер выкинет ошибку, так как он не понимает, объекты с каким префиксом он должен вернуть в ответ.

Третий аргумент - маппер. Если подан, то будет использован вместо дефолтного.

Дефолтный маппер задается в конструкторе Engine.

Relationships

Маппер не будет полноценным без возможности управлять отношениями между объектами, поэтому Entity и Value принимают keyword-аргументы. Названия аргументов - это названия полей в классе, участвующие в отношении. Значения - объекты Assign, Append и Add, принимающие единственный аргумент

• префикс объекта, участвующего в отношении.

Append используется для обработки списков, реализуя OneToMany. При использовании Append маппер будет использовать метод .append() у указанного свойства:

from dataclasses import dataclass, field

from classic.db_tools import Entity, Value, Append


@dataclass
class Status:
    title: str


@dataclass
class Task:
    id: int
    title: str
    statuses: list[Status] = field(default_factory=list)

    
mapping = dict(
    task=Entity(Task, 'id', statuses=Append('status')),
    status=Value(Status)
)

pool = ConnectionPool(...)
engine = Engine(pool, default_mapping=mapping)

with engine:
    tasks = engine.query('''
    SELECT
        tasks.id AS task__id,
        tasks.id AS task__title,
        statuses.title AS status__title
    FROM tasks
    JOIN statuses ON statuses.task_id = tasks.id
    ''').map_to(Task).all()

print(tasks)
# [
#     Task(id=1, title='example', statuses=[
#         Status('new'), 
#         Status('completed'),
#     ]),
# ]

Add очень похож на Append, только используется для обработки множеств. При использовании Add маппер будет использовать метод .add() у указанного свойства.

Assign используется для присвоения объекта указанному свойству, реализуя OneToOne.

Пример:

from dataclasses import dataclass

from classic.db_tools import Engine, Entity, Value, Assign


@dataclass
class Status:
    title: str


@dataclass
class Task:
    id: int
    title: str
    status: Status

    
mapping = dict(
    task=Entity(Task, 'id', status=Assign(Status)),
    status=Value(Status)
)

pool = ConnectionPool(...)
engine = Engine(pool, default_mapping=mapping)

with engine:
    tasks = engine.query('''
    SELECT
        tasks.id AS task__id,
        tasks.id AS task__title,
        statuses.title AS status__title
    FROM tasks
    JOIN statuses ON statuses.task_id = tasks.id
    ''').map_to(Task).all()

print(tasks)
# [
#     Task(id=1, title='example', status=Status('new')]),
# ]

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

inspect

Также маппер умеет разбирать аннотации типов у подаваемых классов, и автоматически догадываться об отношениях, поэтому маппер из примера выше можно сократить до:

mapper = dict(
    task=Entity(Task, 'id'),
    status=Value(Status)
)

Указанные вручную отношения имеют приоритет над автоматически распознанными. Так можно кастомизировать поведение маппера.

Компиляция

Под капотом библиотека компилирует функцию-маппер с учетом полученного курсора, и производит кеширование, так как компиляция занимает время. Ключ кеша зависит от маппера, запрашиваемого префикса и названий и порядка столбцов в запросе.

Для облегчения отладки объект запроса с назначенным маппером имеет метод .sources(), возвращающий текст скомпилированный функции.