Skip to content

Latest commit

 

History

History
930 lines (633 loc) · 67.5 KB

Модуль Базы Данных и Python.md

File metadata and controls

930 lines (633 loc) · 67.5 KB
Raw

[[Практика Python sqlite3]] [[PYTHON315 HW №45]]

SQLite3 - Библиотека Python

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

Основные концепции

  • Встроенность: SQLite является встроенной СУБД, что означает, что она полностью содержится в одном исполняемом файле без необходимости во внешних серверах или процессах.
  • База данных как файл: Вся база данных SQLite, включая таблицы, индексы, триггеры и т. д., хранится в одном файле на диске. Это упрощает управление базой данных, так как для создания резервной копии, переноса или восстановления базы данных достаточно скопировать или переместить файл.
  • Транзакции: SQLite поддерживает транзакции, что обеспечивает согласованность данных даже в случае сбоев системы или отключения питания. Транзакции в SQLite соответствуют стандарту ACID (атомарность, согласованность, изоляция, долговечность).
  • Мультиязычность: SQLite поддерживает множество типов данных SQL, включая текст, числа, даты и булевы значения.

Как работает

  • Подключение к базе данных: Чтобы начать работу с базой данных SQLite в Python, необходимо сначала создать соединение с файлом базы данных. Если файл не существует, SQLite автоматически создаст его.
  • Создание объекта курсора: После установления соединения вы создаете объект курсора, который позволяет выполнять SQL-запросы к базе данных.
  • Выполнение SQL-запросов: С помощью методов курсора можно выполнять различные SQL-запросы для управления данными и структурой базы данных, включая запросы на выборку (SELECT), вставку (INSERT), обновление (UPDATE) и удаление (DELETE), а также для управления схемой базы данных, например, создание или удаление таблиц.
  • Фиксация транзакций: После выполнения изменяющих данные операций (например, INSERT, UPDATE, DELETE) необходимо зафиксировать транзакцию, чтобы изменения были сохранены в базе данных.
  • Закрытие соединения: После завершения работы с базой данных соединение следует закрыть, чтобы освободить системные ресурсы.

Типы запросов

Библиотека sqlite3 поддерживает широкий спектр SQL-запросов, включая:

  • DDL (Data Definition Language): Запросы для определения или изменения структуры базы данных, например, CREATE TABLE для создания таблиц, ALTER TABLE для изменения таблиц, DROP TABLE для удаления таблиц.
  • DML (Data Manipulation Language): Запросы для управления данными, включая INSERT для добавления данных, UPDATE для изменения данных и DELETE для удаления данных.
  • DQL (Data Query Language): Запросы SELECT для выборки данных из базы данных.
  • Транзакционные операции: Включают BEGIN TRANSACTION для начала транзакции, COMMIT для ее завершения и сохранения изменений, а также ROLLBACK для отмены изменений в рамках текущей транзакции.

Библиотека sqlite3 в Python предоставляет удобный и эффективный интерфейс для работы с базами данных SQLite, делая ее отличным выбором для множества приложений, требующих легковесных и надежных решений для хранения данных.

Библиотека sqlite3 в Python предоставляет простые и мощные инструменты для работы с базами данных SQLite. Давайте разберемся с основами этой библиотеки, включая подключение к базе данных, основные объекты и методы, а также примеры использования.

Основные инструменты и объекты

  1. Подключение к базе данных: Для начала работы с базой данных SQLite необходимо установить соединение. Это делается с помощью функции connect(), которая принимает в качестве аргумента путь к файлу базы данных. Если файл не существует, SQLite создаст его.

    import sqlite3
conn = sqlite3.connect('example.db')

    Этот код создает (или открывает, если он уже существует) файл базы данных example.db.

  2. Объект курсора: Для выполнения SQL-запросов используется объект курсора. Курсор создается вызовом метода cursor() объекта соединения.

    cursor = conn.cursor()

    С помощью курсора можно выполнять запросы к базе данных.

  3. Выполнение SQL-запросов: SQL-запросы выполняются с помощью метода execute() объекта курсора.

    • Создание таблицы:

      cursor.execute('''CREATE TABLE if not exists inventory
                  (item_id INTEGER PRIMARY KEY, name TEXT, quantity INTEGER)''')

      Этот запрос создает таблицу inventory с тремя столбцами: item_id, name и quantity.

    • Вставка данных:

      cursor.execute("INSERT INTO inventory (name, quantity) VALUES ('Apple', 3)")

      Добавляет в таблицу inventory запись с названием товара 'Apple' и количеством 3.

    • Выборка данных:

      cursor.execute("SELECT * FROM inventory")
print(cursor.fetchall())

      Выбирает все записи из таблицы inventory и выводит их на экран. Метод fetchall() возвращает все строки из последнего выполненного запроса.

  4. Фиксация транзакций: После выполнения операций, изменяющих данные (например, INSERT, UPDATE, DELETE), необходимо вызвать метод commit() объекта соединения, чтобы изменения были сохранены в базе данных.

    conn.commit()

  5. Закрытие соединения: По завершении работы с базой данных следует закрыть соединение с помощью метода close().

    conn.close()

Методы объекта курсора и соединения

  • cursor.execute(sql, [parameters]): Выполняет один SQL-запрос. Параметры можно передать вторым аргументом для предотвращения SQL-инъекций.
  • cursor.executemany(sql, seq_of_parameters): Позволяет выполнить SQL-запрос многократно с разными значениями параметров.
  • cursor.fetchall(): Возвращает все строки результата последнего запроса.
  • cursor.fetchone(): Возвращает следующую строку результата запроса.
  • conn.commit(): Сохраняет все изменения, сделанные в транзакции.
  • conn.close(): Закрывает соединение с базой данных.

Эти основные инструменты и методы обеспечивают весь необходимый функционал для работы с базами данных SQLite в Python, позволяя легко выполнять операции создания, чтения, обновления и удаления данных (CRUD).

Методы объекта курсора в библиотеке sqlite3 предоставляют различные способы взаимодействия с базой данных SQLite. Основные методы включают execute(), executemany(), и executescript(). Каждый из них имеет свои особенности и применяется в соответствующих сценариях.

execute()

Метод execute() используется для выполнения одного SQL-запроса. Это может быть запрос на выборку данных, вставку, обновление, удаление или операции с структурой базы данных, такие как создание или удаление таблиц.

  • Особенности:
    • Позволяет передавать параметры в запрос, что делает его удобным для предотвращения SQL-инъекций.
    • Параметры передаются в виде кортежа или словаря.
    • Возвращает сам объект курсора, что позволяет цепочечно вызывать другие методы, например, fetchone() или fetchall() для получения результатов запроса.
conn.execute("PRAGMA foreign_keys = ON;")

Этот параметр позволяет включить проверку ссылочной целостности в SQLite. Когда он включен, SQLite будет проверять, что все внешние ключи имеют соответствующие родительские ключи при выполнении операций INSERT и UPDATE, и предотвращать действия, которые нарушают ссылочную целостность. Это полезно для обеспечения целостности данных в базе данных.

executemany()

Метод executemany() принимает SQL-запрос и последовательность параметров и выполняет этот запрос многократно, подставляя каждый раз новый набор параметров. Это особенно полезно для вставки или обновления множества записей одним вызовом.

  • Особенности:
    • Повышает эффективность операций пакетной обработки данных по сравнению с многократным вызовом execute().
    • Не делает автокоммиты. В SQLite транзакция начинается автоматически перед серией операций вставки/обновления/удаления и должна быть явно завершена вызовом commit() на объекте соединения для фиксации изменений.
    • Возвращает объект курсора.

executescript()

Метод executescript() предназначен для выполнения множества SQL-запросов за один вызов. В отличие от execute() и executemany(), этот метод может выполнять несколько SQL-запросов, разделенных точкой с запятой, что делает его идеальным для выполнения SQL-скриптов.

Пример кода

для использования метода executescript():

import sqlite3

# Подключение к базе данных
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# SQL-скрипт, содержащий несколько запросов
script = '''
CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER
);

INSERT INTO users (name, age) VALUES ('Alice', 30);
INSERT INTO users (name, age) VALUES ('Bob', 25);
'''

# Выполнение SQL-скрипта с помощью executescript()
cursor.executescript(script)

# Сохранение изменений и закрытие соединения
conn.commit()
conn.close()

В этом примере мы создаем базу данных SQLite и выполняем SQL-скрипт, который создает таблицу users и добавляет две записи в эту таблицу. Метод executescript() позволяет выполнить оба SQL-запроса за один вызов.

  • Особенности:
    • Автоматически выполняет коммит перед началом выполнения и после завершения всех запросов в скрипте. Это означает, что если в скрипте содержится несколько операций, изменяющих данные, изменения будут автоматически сохранены после выполнения скрипта.
    • Подходит для выполнения сложных скриптов с множеством последовательных операций, включая создание или модификацию структуры базы данных и манипуляции с данными.
    • Возвращает объект курсора.

Дополнительная важная информация

  • Параметризация запросов: Все три метода поддерживают параметризованные запросы, что помогает защитить от SQL-инъекций. Параметры в execute() и executemany() можно передавать как позиционные, так и именованные.
  • Управление транзакциями: SQLite использует механизм логических блокировок для управления транзакциями. По умолчанию, каждый отдельный SQL-запрос выполняется в рамках своей транзакции, если только не было явно начато выполнение транзакции. Понимание этого механизма важно для эффективного управления транзакциями и

избежания потери данных.

  • Производительность: Использование executemany() для пакетной вставки или обновления данных может значительно повысить производительность по сравнению с многократным вызовом execute() для каждой записи. Однако стоит помнить, что открытая транзакция может блокировать таблицу для других операций, поэтому важно своевременно выполнять commit().

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

Автокоммит в executemany и SQLite

В контексте SQLite и библиотеки sqlite3 в Python, автокоммит является частью управления транзакциями. По умолчанию, SQLite работает в режиме автокоммита, когда каждая операция SQL запускается в своей транзакции и автоматически фиксируется. Однако, использование executemany само по себе не вызывает автокоммит после каждой вставки. Автокоммит происходит, если не была явно начата транзакция. Если нужно отключить автокоммит во время использования executemany, можно явно начать транзакцию с помощью BEGIN TRANSACTION и завершить её вызовом commit() после выполнения executemany.

cursor.fetchone()

Метод fetchone() используется для извлечения одной строки из результата запроса. Если строк больше нет, он возвращает None. Это полезно, когда необходимо обрабатывать результат запроса построчно.

Пример использования:

import sqlite3

# Подключение к базе данных
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Выполнение запроса
cursor.execute("SELECT * FROM inventory")

# Извлечение одной строки
row = cursor.fetchone()

# Обработка результата
if row:
    print(row)
else:
    print("No more rows")

# Закрытие соединения
conn.close()

conn.commit()

Метод commit() используется для сохранения всех изменений, сделанных в рамках текущей транзакции, в базу данных. Он фиксирует изменения и делает их постоянными. Этот метод следует вызывать после операций, изменяющих данные (например, INSERT, UPDATE, DELETE).

Пример использования:

import sqlite3

# Подключение к базе данных
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Выполнение запроса на изменение данных
cursor.execute("INSERT INTO inventory (name, quantity) VALUES ('Orange', 5)")

# Фиксация изменений
conn.commit()

# Закрытие соединения
conn.close()

conn.close()

Метод close() используется для закрытия соединения с базой данных. Это освобождает ресурсы, связанные с соединением. Рекомендуется всегда закрывать соединение после завершения работы с базой данных.

Пример использования:

import sqlite3

# Подключение к базе данных
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Выполнение запроса на выборку данных
cursor.execute("SELECT * FROM inventory")
rows = cursor.fetchall()

# Обработка результата
for row in rows:
    print(row)

# Закрытие соединения
conn.close()

Особенности работы с коммитами в SQLite с использованием sqlite3

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

  • Автокоммит: По умолчанию, SQLite работает в режиме автокоммита. Это означает, что каждая SQL-команда, выполняемая вне явной транзакции, автоматически фиксируется сразу после выполнения. Это удобно для простых операций, но для сложных последовательностей изменений рекомендуется использовать явные транзакции.

  • Явные транзакции: Для выполнения нескольких операций в рамках одной транзакции можно использовать команды BEGIN TRANSACTION, COMMIT и ROLLBACK. Это позволяет гарантировать, что все изменения будут зафиксированы только если все операции в транзакции прошли успешно.

Пример использования явных транзакций:

import sqlite3

# Подключение к базе данных
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Начало транзакции
conn.execute("BEGIN TRANSACTION")

try:
    # Выполнение нескольких запросов
    cursor.execute("INSERT INTO inventory (name, quantity) VALUES ('Banana', 10)")
    cursor.execute("UPDATE inventory SET quantity = quantity + 5 WHERE name = 'Apple'")
    
    # Фиксация изменений
    conn.commit()
except sqlite3.Error as e:
    # Откат транзакции в случае ошибки
    conn.rollback()
    print("Transaction failed: ", e)

# Закрытие соединения
conn.close()

  • Параллелизм и блокировки: SQLite использует блокировки для управления параллельным доступом к базе данных. Во время выполнения транзакции таблица может быть заблокирована для других операций. Это важно учитывать при проектировании приложений с параллельным доступом к базе данных.

  • Постепенные изменения: Для эффективного управления большими объемами данных рекомендуется использовать методы пакетной обработки, такие как executemany(), в сочетании с явными транзакциями. Это позволяет снизить накладные расходы на блокировки и повысить производительность.

Понимание этих аспектов поможет эффективно управлять транзакциями и обеспечивать целостность данных при работе с SQLite в Python.

SQL инъекция

Что такое SQL-инъекции?

SQL-инъекции — это метод атаки, при котором злоумышленник вводит вредоносный SQL-код в поля ввода веб-приложения. Цель этой атаки — выполнить нежелательные SQL-запросы к базе данных, получить доступ к данным, изменить их или даже уничтожить. SQL-инъекции являются одной из самых распространенных и опасных уязвимостей веб-приложений.

Как происходит SQL-инъекция?

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

import sqlite3

# Подключение к базе данных
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Ввод пользователя
user_input = "1 OR 1=1"

# Уязвимый SQL-запрос
query = f"SELECT * FROM users WHERE id = {user_input}"
cursor.execute(query)

# Извлечение данных
rows = cursor.fetchall()
for row in rows:
    print(row)

# Закрытие соединения
conn.close()

Если злоумышленник вводит 1 OR 1=1, запрос станет SELECT * FROM users WHERE id = 1 OR 1=1, что приведет к выборке всех строк из таблицы users, так как 1=1 всегда истинно. Это может привести к утечке конфиденциальной информации.

Как бороться с SQL-инъекциями в Python с использованием sqlite3?

  1. Использование параметризованных запросов: Вместо того чтобы включать пользовательский ввод непосредственно в SQL-запрос, используйте параметризованные запросы. Библиотека sqlite3 поддерживает это с помощью заполнителей ? или :param_name.

  2. Проверка и очистка данных: Проверяйте и очищайте все входные данные, чтобы убедиться, что они соответствуют ожидаемому формату.

  3. Ограничение прав доступа: Минимизируйте привилегии, предоставленные учетной записи базы данных, используемой вашим приложением, чтобы ограничить потенциальный ущерб от успешной атаки.

Пример защиты с помощью параметризованных запросов

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

Пример использования параметризованных запросов:

import sqlite3

# Подключение к базе данных
conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Ввод пользователя
user_input = "1 OR 1=1"

# Безопасный SQL-запрос с параметром
query = "SELECT * FROM users WHERE id = ?"
cursor.execute(query, (user_input,))

# Извлечение данных
rows = cursor.fetchall()
for row in rows:
    print(row)

# Закрытие соединения
conn.close()

В этом примере пользовательский ввод передается в качестве параметра, и SQLite обрабатывает его безопасно, предотвращая выполнение вредоносного кода.

Первый запрос небезопасен, потому что он использует небезопасную подстановку значений через метод format(), что позволяет злоумышленнику вставить произвольный SQL-код. В результате SQL-инъекции запрос превращается в следующий вид:

SELECT * FROM Cats WHERE CatID = 1 OR 1 = 1;

Запрос "1 OR 1 = 1" всегда истинный, и поэтому он возвращает все строки таблицы Cats.

Второй запрос безопасен, потому что он использует параметризованный запрос. Это достигается путем использования плейсхолдеров (в данном случае, ?) и передачи значений как параметров метода execute(). Этот подход предотвращает выполнение произвольного SQL-кода, введенного злоумышленником.

Когда вы используете параметризованный запрос, значение '1' передается как параметр и обрабатывается библиотекой SQLite как безопасное значение, а не как часть SQL-кода. Запрос выглядит так:

SELECT * FROM Cats WHERE CatID = ?;

И значение '1' подставляется на место ? безопасным способом, что предотвращает возможность SQL-инъекции.

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

Если хотите увидеть примеры того, как можно создать безопасные и небезопасные запросы, можно воспользоваться следующим примером кода:

import sqlite3

# Небезопасный запрос
SQL_INJECTION = "SELECT * FROM Cats WHERE CatID = {};"
cat_id = "1 OR 1 = 1"

print("Unsafe query:")
cursor.execute(SQL_INJECTION.format(cat_id))

rows = cursor.fetchall()
for row in rows:
    print(row)

print('-------------------')

# Безопасный запрос
SAFE_SQL = "SELECT * FROM Cats WHERE CatID = ?;"
cursor.execute(SAFE_SQL, ('1',))

print("Safe query:")
rows = cursor.fetchall()
for row in rows:
    print(row)

Этот код демонстрирует разницу между небезопасным и безопасным запросами на практике.

Дополнительные меры безопасности

  • Использование ORM: Использование ORM (Object-Relational Mapping) библиотек, таких как SQLAlchemy или Django ORM, также помогает предотвратить SQL-инъекции, так как они автоматически параметризуют запросы.

  • Логи и мониторинг: Внедрение системы логирования и мониторинга, чтобы обнаружить подозрительные действия и потенциальные атаки.

Заключение

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

Подстановка данных

Давайте разберем примеры использования методов execute, executemany, и executescript из библиотеки sqlite3 в Python, объясняя каждый шаг для лучшего понимания.

execute с подстановкой данных

Метод execute используется для выполнения одного SQL-запроса. Особенно полезен для вставки, обновления, удаления данных или выполнения запросов на выборку.

Пример:

import sqlite3

conn = sqlite3.connect('example.db')  # Подключаемся к базе данных
cursor = conn.cursor()  # Создаем курсор

# Вставка данных с подстановкой переменных
name = 'Alice'
age = 30
cursor.execute("INSERT INTO People (name, age) VALUES (?, ?)", (name, age))

conn.commit()  # Сохраняем изменения
conn.close()  # Закрываем соединение

В этом примере ? используется в качестве плейсхолдера для переменных name и age, которые подставляются в запрос. Это предотвращает SQL-инъекции и является рекомендуемым способом работы с динамическими данными.

executemany для пакетной обработки

Метод executemany идеален для выполнения одного и того же SQL-запроса с разными значениями, например, при вставке нескольких строк данных одновременно.

Пример:

import sqlite3

conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Пакетная вставка данных
people = [('Bob', 25), ('Cindy', 35), ('Dave', 20)]
cursor.executemany("INSERT INTO People (name, age) VALUES (?, ?)", people)

conn.commit()
conn.close()

Здесь executemany используется для вставки нескольких записей в таблицу People. Каждый элемент списка people представляет собой кортеж, который соответствует плейсхолдерам в SQL-запросе.

executescript для выполнения нескольких SQL-запросов

Метод executescript позволяет выполнить несколько SQL-запросов за один вызов. Это полезно для выполнения скриптов SQL, содержащих несколько команд.

Пример:

import sqlite3

conn = sqlite3.connect('example.db')
cursor = conn.cursor()

# Выполнение нескольких SQL-команд
script = """
CREATE TABLE IF NOT EXISTS Products (id INTEGER PRIMARY KEY, name TEXT, price INTEGER);
INSERT INTO Products (name, price) VALUES ('Pen', 10);
INSERT INTO Products (name, price) VALUES ('Pencil', 5);
"""
cursor.executescript(script)

conn.commit()
conn.close()

В этом примере executescript используется для создания новой таблицы Products и вставки в нее двух строк. Метод идеально подходит для инициализации базы данных или выполнения миграций.

Общие советы по использованию:

  • Всегда используйте подстановку переменных (?) в execute и executemany для предотвращения SQL-инъекций.
  • После выполнения изменяющих данные операций не забудьте вызвать commit(), чтобы сохранить изменения.
  • Закрывайте соединение с базой данных (close()) после выполнения всех необходимых операций для освобождения ресурсов.

Эти примеры должны дать студентам хорошее представление о том, как использовать основные методы sqlite3 для работы с базами данных SQLite в Python.

Оптимальный способ писать функции sqlite3

Почему соединение передается из функции в функцию?

Передача соединения из функции в функцию позволяет:

  • Управлять транзакциями: Поддержание одного и того же соединения позволяет вам управлять транзакциями. Вы можете начать транзакцию, выполнить несколько операций и затем подтвердить (commit) или отменить (rollback) изменения.
  • Ресурсоемкость: Создание нового соединения для каждой операции может быть ресурсоемким и снижать производительность, особенно если операции выполняются часто.
  • Координация действий: Использование одного соединения позволяет координировать действия между разными функциями, что особенно полезно в случае сложных операций.

Почему курсор создается заново в каждой функции?

Создание нового курсора в каждой функции позволяет:

  • Изолирование контекста запроса: Курсор предоставляет контекст для выполнения SQL-запросов. Создание нового курсора для каждой операции изолирует контексты этих операций, что уменьшает вероятность конфликтов.
  • Безопасность и чистота кода: Каждый курсор привязан к определенному запросу и его результатам, что упрощает управление и делает код более понятным и безопасным.
  • Управление ресурсами: Курсоры автоматически закрываются после завершения операции, если они создаются в пределах функции. Это освобождает ресурсы и предотвращает утечки памяти.

Конструкция INSERT OR IGNORE

Конструкция INSERT OR IGNORE используется в SQLite для попытки вставить новую запись в таблицу. Если запись с таким же уникальным значением уже существует (например, при наличии ограничения уникальности на столбец), SQLite игнорирует попытку вставки, не выбрасывая ошибки и не прерывая выполнение программы.

Пример:

INSERT OR IGNORE INTO tags (name) VALUES ('python');

Если в таблице tags уже существует строка с именем python, эта операция будет проигнорирована, и никаких изменений не будет внесено.

Работа со списками updates и params в функциях

В функции update_post списки updates и params используются для динамического построения SQL-запроса на обновление.

Пример кода:

updates = []
params = []

if title:
    updates.append("title = ?")
    params.append(title)
    
if content:
    updates.append("content = ?")
    params.append(content)
    
if category_name:
    updates.append("category_id = (SELECT category_id FROM categories WHERE name = ?)")
    params.append(category_name)
    
if updates:
    sql = f"UPDATE posts SET {', '.join(updates)}, updated_at = CURRENT_TIMESTAMP WHERE post_id = ?"
    params.append(post_id)
    
    with connection:
        connection.execute(sql, params)

Объяснение:

  • Списки updates: Содержит фрагменты SQL-запроса для обновления столбцов. Эти фрагменты добавляются в список только если соответствующие аргументы функции не равны None.
  • Список params: Содержит значения, которые будут подставлены вместо ? в SQL-запросе. Эти значения добавляются в список в том же порядке, в каком добавляются фрагменты в updates.
  • Построение SQL-запроса: SQL-запрос строится динамически путем объединения элементов списка updates через запятую, затем добавляется условие WHERE, и значения подставляются из списка params.

Метод rowcount в delete_post

Метод rowcount используется для получения количества строк, затронутых последним выполненным SQL-запросом.

Пример:

rows_affected = connection.execute("DELETE FROM posts WHERE post_id = ?", (post_id,)).rowcount
if rows_affected == 0:
    raise ValueError(f"No post exists with ID {post_id}")

Объяснение:

  • Количество затронутых строк: После выполнения команды DELETE, метод rowcount возвращает количество строк, которые были удалены.
  • Проверка успеха операции: В данном случае rowcount используется для проверки, была ли удалена хотя бы одна строка. Если rowcount равен нулю, значит, строка с указанным post_id не существовала в таблице, и мы выбрасываем исключение ValueError.

Использование rowcount помогает убедиться в том, что операция удаления действительно имела место и была успешной.

Работа с транзакциями в SQLite

Работа с транзакциями в SQLite через библиотеку sqlite3 в Python выполняется с помощью методов commit() и rollback() объекта соединения. В библиотеке sqlite3 транзакции управляются автоматически, что означает, что по умолчанию каждое изменение автоматически фиксируется (автокоммит). Однако вы можете явно управлять транзакциями для выполнения группы операций атомарно.

Начало транзакции

Для начала транзакции вам не нужно выполнять явный SQL-запрос, достаточно отключить режим автокоммита. Это можно сделать путем выполнения операций внутри блока with или с использованием методов begin, commit, rollback.

Использование блока with

import sqlite3

connection = sqlite3.connect('example.db')

try:
    with connection:
        cursor = connection.cursor()
        cursor.execute("INSERT INTO posts (title, content) VALUES (?, ?)", ("Sample Title", "Sample Content"))
        cursor.execute("UPDATE posts SET views = views + 1 WHERE post_id = ?", (1,))
        # Все операции внутри блока будут выполнены в одной транзакции.
        # Если произойдет ошибка, транзакция будет автоматически откатана.
except sqlite3.Error as e:
    print(f"An error occurred: {e}")

Явное управление транзакциями

Если вы хотите вручную управлять началом и завершением транзакции, можно использовать методы commit() и rollback().

import sqlite3

connection = sqlite3.connect('example.db')

try:
    connection.execute('BEGIN')  # Явно начинаем транзакцию
    cursor = connection.cursor()
    cursor.execute("INSERT INTO posts (title, content) VALUES (?, ?)", ("Sample Title", "Sample Content"))
    cursor.execute("UPDATE posts SET views = views + 1 WHERE post_id = ?", (1,))
    connection.commit()  # Фиксируем транзакцию
except sqlite3.Error as e:
    connection.rollback()  # Откатываем транзакцию в случае ошибки
    print(f"An error occurred: {e}")

Работа с автокоммитом

По умолчанию, библиотека sqlite3 в Python находится в режиме автокоммита. Это означает, что каждое изменение в базе данных (например, вставка, обновление или удаление) немедленно фиксируется. Для отключения автокоммита можно явно начинать транзакцию и использовать commit() для завершения транзакции или rollback() для её отмены.

Пример отключения автокоммита:

import sqlite3

connection = sqlite3.connect('example.db')

# Отключение автокоммита
connection.isolation_level = None

try:
    connection.execute('BEGIN')  # Начинаем транзакцию
    cursor = connection.cursor()
    cursor.execute("INSERT INTO posts (title, content) VALUES (?, ?)", ("Sample Title", "Sample Content"))
    cursor.execute("UPDATE posts SET views = views + 1 WHERE post_id = ?", (1,))
    connection.commit()  # Фиксируем транзакцию
except sqlite3.Error as e:
    connection.rollback()  # Откатываем транзакцию в случае ошибки
    print(f"An error occurred: {e}")
finally:
    # Возвращаем автокоммит
    connection.isolation_level = ''

Резюме

  • Начало транзакции: Можно использовать connection.execute('BEGIN') для явного начала транзакции.
  • Фиксация транзакции: Используйте connection.commit() для фиксации изменений.
  • Откат транзакции: Используйте connection.rollback() для отмены изменений.
  • Автокоммит: По умолчанию включен, но его можно отключить, установив connection.isolation_level = None.

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

Контекстный менеджер и транзакции

Когда вы используете контекстный менеджер (блок with) для подключения к базе данных:

  • Все операции, выполненные в рамках этого блока, будут выполнены в одной транзакции.
  • Если внутри блока произойдет ошибка, транзакция будет автоматически откатана.
  • Вам не нужно явно начинать транзакцию с помощью BEGIN, так как контекстный менеджер сам управляет транзакциями.
  • После успешного завершения блока контекстного менеджера транзакция будет автоматически зафиксирована (commit).

Пример использования контекстного менеджера:

import sqlite3

connection = sqlite3.connect('example.db')

try:
    with connection:
        cursor = connection.cursor()
        cursor.execute("INSERT INTO posts (title, content) VALUES (?, ?)", ("Sample Title", "Sample Content"))
        cursor.execute("UPDATE posts SET views = views + 1 WHERE post_id = ?", (1,))
        # Все операции внутри блока будут выполнены в одной транзакции.
except sqlite3.Error as e:
    print(f"An error occurred: {e}")

Явное управление транзакциями

Если вы хотите явно управлять транзакциями:

  • Не используйте контекстный менеджер для управления соединением.
  • Начните транзакцию с помощью SQL-запроса BEGIN.
  • Выполняйте ваши операции.
  • Завершите транзакцию с помощью методов commit() или rollback() в зависимости от результата операций.

Пример явного управления транзакциями:

import sqlite3

connection = sqlite3.connect('example.db')

try:
    connection.execute('BEGIN')  # Явно начинаем транзакцию
    cursor = connection.cursor()
    cursor.execute("INSERT INTO posts (title, content) VALUES (?, ?)", ("Sample Title", "Sample Content"))
    cursor.execute("UPDATE posts SET views = views + 1 WHERE post_id = ?", (1,))
    connection.commit()  # Фиксируем транзакцию
except sqlite3.Error as e:
    connection.rollback()  # Откатываем транзакцию в случае ошибки
    print(f"An error occurred: {e}")

Сравнение подходов

  • Контекстный менеджер: Автоматически управляет транзакциями. Все операции внутри блока будут в одной транзакции. Если возникает ошибка, все изменения откатываются.
  • Явное управление: Позволяет полностью контролировать начало и завершение транзакций. Требует явного вызова методов BEGIN, commit() и rollback().

Выбор подхода зависит от вашего сценария использования:

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

Параллелизм и блокировки в SQLite

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

  1. Блокировка базы данных (Database Lock):

    • SQLite использует механизм блокировок для управления доступом к базе данных и обеспечения целостности данных.
    • Есть три основных уровня блокировки:
      • SHARED (совместная блокировка): Используется для операций чтения. Несколько транзакций могут одновременно удерживать совместную блокировку.
      • RESERVED (зарезервированная блокировка): Используется, когда транзакция планирует внести изменения, но еще не начала их фиксировать. Она позволяет другим транзакциям продолжать чтение, но предотвращает другие записи.
      • EXCLUSIVE (исключительная блокировка): Используется для операций записи. Только одна транзакция может удерживать исключительную блокировку, что предотвращает любые другие транзакции (чтение и запись) до завершения текущей транзакции.

  2. Параллельный доступ на чтение и запись:

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

Особенности и практические аспекты

  • Одновременные операции чтения: Несколько потоков или процессов могут одновременно читать из базы данных без каких-либо проблем. SQLite обрабатывает совместные блокировки для чтения, что позволяет параллельный доступ.

  • Операции записи: Только один поток или процесс может выполнять операцию записи в базу данных в данный момент времени. Когда транзакция начинается, она устанавливает RESERVED блокировку. После подготовки изменений транзакция пытается установить EXCLUSIVE блокировку для фиксации изменений, что может блокировать другие операции до завершения транзакции.

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

  • Настройки многопоточности в SQLite: SQLite можно настроить для работы в одном из трех режимов многопоточности:

    • Single-thread: Библиотека SQLite не использует никакие блокировки. Это безопасно только в случае, если все вызовы SQLite происходят из одного потока.
    • Multi-thread: Несколько потоков могут выполнять чтение, но только один поток может выполнять запись. Это режим по умолчанию.
    • Serialized: Все функции библиотеки SQLite безопасны для вызова из нескольких потоков одновременно. Это наиболее безопасный режим для многопоточных приложений.

Настройки можно изменить с помощью функции sqlite3_config() до открытия соединений с базой данных:

import sqlite3

sqlite3.config(sqlite3.SQLITE_CONFIG_SERIALIZED)
conn = sqlite3.connect('example.db')

Пример многопоточной программы

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

import sqlite3
import threading

def read_data():
    conn = sqlite3.connect('example.db')
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM inventory")
    rows = cursor.fetchall()
    for row in rows:
        print(row)
    conn.close()

def write_data(name, quantity):
    conn = sqlite3.connect('example.db')
    cursor = conn.cursor()
    cursor.execute("INSERT INTO inventory (name, quantity) VALUES (?, ?)", (name, quantity))
    conn.commit()
    conn.close()

# Создание потоков для чтения и записи
read_thread = threading.Thread(target=read_data)
write_thread = threading.Thread(target=write_data, args=('Banana', 10))

# Запуск потоков
read_thread.start()
write_thread.start()

# Ожидание завершения потоков
read_thread.join()
write_thread.join()

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

Заключение

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

Тестирование запросов в pytest

Для тестирования запросов к базе данных в pytest можно использовать виртуальные таблицы и фикстуры. Фикстуры с yield позволяют настроить тестовую среду перед выполнением теста и очистить её после, что идеально подходит для тестирования взаимодействия с базой данных.

Пример фикстуры для создания и удаления тестовой базы данных:

import pytest
import sqlite3

@pytest.fixture
def db():
    conn = sqlite3.connect(":memory:")  # Создание базы данных в памяти
    cursor = conn.cursor()
    cursor.execute('''CREATE TABLE test_table (id INTEGER PRIMARY KEY, value TEXT)''')
    yield conn  # Возвращает объект соединения для использования в тесте
    conn.close()  # Закрытие соединения после завершения теста

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

Параллельное чтение/запись в SQLite

SQLite поддерживает параллельное чтение, но запись в базу данных может производиться только одним процессом или потоком одновременно из-за механизма блокировок. Для управления конкурентным доступом SQLite использует несколько режимов изоляции транзакций, включая режим WAL (Write-Ahead Logging), который позволяет читателям продолжать чтение из базы данных в то время, как другая транзакция записывает данные.

Пример использования SQLite в Python для обеспечения конкурентного доступа в режиме WAL:

import sqlite3

conn = sqlite3.connect('example.db')
conn.execute('PRAGMA journal_mode=WAL;')  # Включение режима WAL

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

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

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

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

Получение информации о таблицах

Чтобы получить список всех таблиц в базе данных SQLite, можно использовать следующий запрос к служебной таблице sqlite_master:

SELECT name FROM sqlite_master WHERE type='table';

Этот запрос возвращает имена всех таблиц в базе данных.

Получение информации о столбцах таблицы

Для получения списка столбцов определенной таблицы и информации о них можно использовать запрос PRAGMA table_info():

PRAGMA table_info('имя_таблицы');

Этот запрос вернет информацию о каждом столбце в таблице, включая идентификатор столбца, имя, тип данных, признак необходимости значения (not null) и значение по умолчанию.

Получение информации о первичных и внешних ключах

  • Первичные ключи: Информация о первичных ключах также возвращается запросом PRAGMA table_info(), где для столбцов, являющихся частью первичного ключа, будет установлено специальное значение.

  • Внешние ключи: Для получения информации о внешних ключах таблицы можно использовать запрос PRAGMA foreign_key_list():

    PRAGMA foreign_key_list('имя_таблицы');

    Этот запрос вернет список внешних ключей для указанной таблицы, включая имя столбца в текущей таблице, имя связанной таблицы и имя столбца в связанной таблице.

Параметризированные тесты в pytest

Для тестирования наличия таблиц, столбцов и ключей можно использовать параметризированные тесты в pytest. Пример параметризации для проверки наличия таблиц:

import pytest
import sqlite3

# Предположим, у нас есть функция для получения списка таблиц
def get_tables(db_path):
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table';")
    tables = cursor.fetchall()
    conn.close()
    return [t[0] for t in tables]

# Параметризированный тест для проверки наличия определенных таблиц
@pytest.mark.parametrize('table_name', ['students', 'courses'])
def test_table_exists(db_path, table_name):
    assert table_name in get_tables(db_path)

Аналогичный подход можно использовать для тестирования наличия столбцов и проверки свойств ключей, используя служебные запросы PRAGMA table_info() и PRAGMA foreign_key_list() для получения необходимой информации и параметризацию в pytest для определения тестовых случаев.

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