Создание или переопределение 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 Q <<. Там я и другие участники отвечаем на вопросы по python, программированию и контенту сайта.

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

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

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

7 313 2 925 ₽/мес.
Профессия Python Fullstack / Skillbox

Профессия Python Fullstack / Skillbox

6 569 2 627 ₽/мес.
Профессия Data Scientist / Skillbox

Профессия Data Scientist / Skillbox

9 187 3 675 ₽/мес.
Python-фреймворк на Django / Skillbox

Python-фреймворк на Django / Skillbox

818 ₽/мес.
Профессия DS: машинное обучение / Skillbox

Профессия DS: машинное обучение / Skillbox

6172 2469 ₽/мес.
Профессия DS: анализ данных / Skillbox

Профессия DS: анализ данных / Skillbox

6172 2469 ₽/мес.

Вам помогла эта статья? Поделитесь в соцсетях или блоге. Репосты помогают сайту развиться.

Александр эксперт Яндекс.Кью
Я создал этот блог в 2018 году, чтобы распространять полезные учебные материалы, документации и уроки на русском. На сайте опубликовано множество статей по основам python и библиотекам, уроков для начинающих и примеров написания программ.
Мои контакты: