Создание или переопределение 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 закрыто

Подписывайтесь на канал в Дзене

Полезный контент для начинающих и опытных программистов в канале Лента Python разработчика — Как успевать больше, делать лучше и не потерять мотивацию.

Обучение Python и Data Science

Профессия Data Scientist

Профессия Data Scientist

11 520 5 760 ₽/мес.
Профессия Python-разработчик

Профессия Python-разработчик

7 820 3 910 ₽/мес.
Профессия Python Fullstack

Профессия Python Fullstack

7 820 3 910 ₽/мес.
Курс Аналитик данных с нуля

Курс Аналитик данных с нуля

6 500 3 900 ₽/мес.

Появились вопросы? Задайте на Яндекс.Кью

У сайта есть сообщество на Кью >> Python Q <<. Там я, эксперты и участники отвечаем на вопросы по python и программированию.