Создание или переопределение SQL-функций в SQLite

В таких базах данных, как MySQL, MSSQL и PostgreSQL, есть возможность создавать функции и хранимые процедуры, но у SQLite такой возможности нет. Таким образом, CREATE FUNCTION и CREATE PROCEDURE с этой базой данных работать не будут. В этом материале рассмотрим, как создавать или переиспользовать SQL-функции из Python.

C API базы данных SQLite дает возможность создавать пользовательские функции или переопределять поведение существующих. Модуль sqlite3 — это всего лишь оболочка для этого C API, которая предоставляет возможность создавать или переопределять SQLite-функции из Python.

В этой статье мы рассмотрим:

• Использовать connection.create_function() из sqlite3 в Python для создания и переопределения функций в SQLite;
• Использовать connection.create_aggregate() для создания агрегатных функций в SQLite.

Создание SQL-функций из Python для SQLite

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

В SQLite есть масса встроенных функций: LENGTH, LOWER, UPPER, SUBSTR, REPLACE и другие. Добавим к этому списку TOTITLE для конвертации любой строки и в строку с заглавными буквами.

Для начала нужно разобраться с connection.create_function().

Синтаксис:

create_function(name, num_params, func)

Функция принимает три аргумента:

• name — имя функции
• num_params — количество параметров, которые функция принимает
• func — функция Python, которая вызывается внутри запроса

Эта функция создает пользовательскую функцию, которую можно использовать в инструкциях SQL, ссылаясь на ее name.

Примечание: если в качестве параметра num_params передать значение -1, то функция будет принимать любое количество аргументов. connection.create_function() может возвращать любые типы, поддерживаемые SQLite: bytes, str, int, float и None.

Создадим новую функцию в SQLite с помощью connection.create_function().

import sqlite3



def _to_title_case(string):

    return str(string).title()



def get_developer_name(dev_id):

    try:

        sqlite_connection = sqlite3.connect('sqlite_python.db')

        cursor = sqlite_connection.cursor()

        print("Подключен к SQLite")



        sqlite_connection.create_function("TOTILECASE", 1, _to_title_case)

        select_query = "SELECT TOTILECASE(name) FROM sqlitedb_developers where id = ?"

        cursor.execute(select_query, (dev_id,))

        name = cursor.fetchone()

        print("Имя разработчика", name)

        cursor.close()



    except sqlite3.Error as error:

        print("Ошибка при работе с SQLite", error)

    finally:

        if sqlite_connection:

            sqlite_connection.close()

            print("Соединение с SQLite закрыто")



get_developer_name(2)

Вывод:

Подключен к SQLite
Имя разработчика ('Viktoria',)
Соединение с SQLite закрыто

Разбор примера в подробностях

В прошлом разделе была создана функция _to_title_case, которая принимает строку в качестве входящего значения и конвертирует ее в строку с заглавными буквами.

import sqlite3:

• Эта строка импортирует модуль sqlite3 в программу.
• С помощью классов и методов модуля sqlite3 можно взаимодействовать с базой данных SQLite.

sqlite3.connect() и connection.cursor():

• С помощью sqlite3.connect() устанавливается соединение с базой данных.
• Дальше метод connection.cursor() используется для получения объекта cursor из объекта соединения.

сonnection.create_function():

• После этого вызывается create_function из класса connection. В нее передаются три аргумента: название функции, количество параметров, которые будет принимать _to_title_case и функция Python, которая будет вызываться как SQL-функция.
• После этого функция TOTITLECASE вызывается в запросе SELECT для получения имени разработчика в виде строки с заглавными буквами.

cursor.execute():

• Выполняем запрос с помощью метода execute() объекта cursor и получаем имя с помощью cursor.fetchone().

Наконец, объекты cursor и connection закрываются в блоке finally после завершения операции обновления.

Переопределение существующих функций SQLite

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

В качестве демонстрации конвертируем встроенную в SQLite функцию LOWER в UPPER, чтобы при ее вызове она превращала входящие данные в верхний регистр.

Создадим новое определение для функции lower() с помощью метода connection.create_function(). Таким образом мы перезаписываем уже существующую реализацию функции lower().

import sqlite3



def lower(string):

    return str(string).upper()



def get_developer_name(dev_id):

    try:

        sqlite_connection = sqlite3.connect('sqlite_python.db')

        cursor = sqlite_connection.cursor()

        print("Подключен к SQLite")



        sqlite_connection.create_function("lower", 1, lower)

        select_query = "SELECT lower(name) FROM sqlitedb_developers where id = ?"

        cursor.execute(select_query, (dev_id,))

        name = cursor.fetchone()

        print("Имя разработчика", name)

        cursor.close()



    except sqlite3.Error as error:

        print("Ошибка при работе с SQLite", error)

    finally:

        if sqlite_connection:

            sqlite_connection.close()

            print("Соединение с SQLite закрыто")



get_developer_name(1)

Вывод:

Подключен к SQLite
Имя разработчика ('OLEG',)
Соединение с SQLite закрыто