Лекция 12. Программирование на языках высокого уровня. Тема 15. Работа с базами данных

1. Программирование на языках высокого уровня

Лекция 12
1
15. Работа с базами данных

2. 15. Работа с базами данных

2
Роль баз данных в современных приложениях
Базы данных — основа хранения и
информации в большинстве ИТ-систем.
обработки
Используются в веб-приложениях, CRM, банковских
системах, аналитике, IoT и т.д.
Обеспечивают надёжность, целостность и быстрый
доступ к данным.

3. 15. Работа с базами данных

3
Зачем нужны интерфейсы доступа к БД
Python-программе
нужен
способ взаимодействия с БД.
унифицированный
Без API код жёстко привязан к конкретной СУБД →
сложнее поддержка и перенос.
Интерфейс обеспечивает
логика — разные СУБД.
абстракцию:
одна

4. 15. Работа с базами данных

4
В python существует 2 принципиально
подхода к работе с базами данных:
различных
1. Стандарт DB API 2.0 для работы с реляционными
базами данных в Python - низкоуровневый,
стандартный интерфейс (PEP 249) для SQL-баз.
2. ORM-технология
(Object-Relational
Mapping
—
Объектно-реляционное отображение) для работы с
базами
данных через
объектную
модель высокоуровневый способ работы с БД через классы
Python.

5. 15. Работа с базами данных

5
1. Стандарт DB API 2.0
(Python Database API Specification v2.0) — это
стандарт интерфейса между Python-приложением и
реляционной СУБД.
Описан в
стандарт.
PEP
249,
принят
https://peps.python.org/pep-0249/
как
промышленный

6. 15. Работа с базами данных

6
Назначение DB API 2.0
Унифицированная работа с разными СУБД:
DB API 2.0 предоставляет единый интерфейс для
работы с различными реляционными базами данных,
такими как MySQL, PostgreSQL, SQLite и другие. Это
позволяет разработчику не заботиться о специфике
каждой конкретной СУБД, а использовать один и тот же
код для работы с разными базами данных.

7. 15. Работа с базами данных

7
Независимость от конкретной БД:
Одной из ключевых целей DB API 2.0 является
обеспечение абстракции от конкретной СУБД. Это
позволяет переключаться между разными СУБД,
минимизируя необходимость изменений в коде
приложения. Все обращения к базе данных выполняются
через общий интерфейс, что упрощает миграцию
между различными СУБД.

8. 15. Работа с базами данных

8
Минимальные изменения при переходе между СУБД:
DB API 2.0 разрабатывался таким образом, чтобы
изменения в приложении при переходе от одной СУБД к
другой сводились к минимуму. Это особенно важно для
крупных проектов, где требуется возможность выбора
подходящей СУБД для конкретных нужд, не требуя
переписывания кода для каждой базы данных.

9. 15. Работа с базами данных

9
Интерфейс модуля DB API 2.0
Интерфейс модуля DB API 2.0 предоставляет набор
стандартных методов и констант, которые позволяют
взаимодействовать с реляционными базами данных. Он
описан в PEP 249 и обеспечивает унифицированный
способ работы с различными СУБД.

10. 15. Работа с базами данных

10
Функция connect() и её параметры:
connect() — основная функция для подключения к базе
данных. Она принимает различные параметры для
настройки соединения, включая:
• dsn (Data Source Name) — строка подключения.
• user — имя пользователя.
• password — пароль пользователя.
• host — хост базы данных.
• port — порт для подключения.

11. 15. Работа с базами данных

11
• дополнительно
могут
быть
параметры
для
управления временем ожидания соединения,
кодировкой и другими аспектами.
Пример подключения к базе данных:
import psycopg2
conn = psycopg2.connect(database="mydb",
user="myuser", password="mypassword",
host="localhost", port="5432")

12. 15. Работа с базами данных

12
Константы DB API 2.0:
• apilevel: Уровень совместимости API. Например,
"2.0".
• paramstyle: Стиль параметров в запросах,
например,
pyformat
(Коды
расширенного
формата
Python,
например
...WHERE
name=%(name)s), qmark (Стиль вопросительного
знака, например...WHERE name=?), numeric
(Числовой, позиционный стиль, например...WHERE
name=:1),
named
(Названный
стиль,
например...WHERE name=:name), format (Коды
формата ANSI C printf, например...WHERE
name=%s).

13. 15. Работа с базами данных

13
Константы DB API 2.0:
• threadsafety: Указывает, поддерживает ли модуль
работу в многопоточном режиме. Может быть 0
(потоки не могут совместно использовать модуль),
1 (потоки могут совместно использовать модуль,
но не соединения.), 2 (Потоки могут совместно
использовать модуль и соединения.), 3 (Потоки
могут
совместно
использовать
модуль,
соединения и курсоры.).

14. 15. Работа с базами данных

14
Пример:
print(conn.apilevel) # 2.0
print(conn.threadsafety) # 2 (частичная
поддержка многопоточности)

15. 15. Работа с базами данных

15
Объект-соединение (Connection)
Объект-соединение (Connection) — это ключевой
компонент в работе с базой данных через DB API 2.0. Он
представляет
собой
активное
соединение
с
реляционной СУБД, которое позволяет выполнять
транзакции, отправлять запросы и управлять состоянием
базы данных.

16. 15. Работа с базами данных

16
Основные методы объекта-соединения:
commit() — сохраняет все изменения, сделанные в
базе данных в рамках текущей транзакции.
conn.commit()
rollback() — откатывает изменения, сделанные в
текущей транзакции, и возвращает базу данных в
исходное
состояние
до
начала
последней
транзакции.
conn.rollback()

17. 15. Работа с базами данных

17
close() — закрывает соединение с базой данных.
После вызова этого метода объект соединения
больше не может использоваться для выполнения
запросов.
conn.close()
cursor() — создает объект-курсор для выполнения SQLзапросов и получения результатов.
cur = conn.cursor()

18. 15. Работа с базами данных

18
Примерная последовательность подключения к базе
данных:
1. Вначале создаем соединение с базой данных через
connect().
2. Далее с помощью метода cursor() создаем курсор
для выполнения запросов.
3. По завершении работы с базой данных важно
закрыть соединение с помощью close().

19. 15. Работа с базами данных

19
SQLite — это библиотека на языке C, которая
предоставляет легкую дисковую базу данных, не
требующую отдельного серверного процесса и
позволяющую получать доступ к базе данных с
использованием
нестандартного
варианта
языка
запросов
SQL.
Некоторые
приложения
могут
использовать SQLite для внутреннего хранения данных.
Также возможно создать прототип приложения с
использованием SQLite, а затем перенести код в более
крупную базу данных, например PostgreSQL или Oracle.

20. 15. Работа с базами данных

20
Библиотека
sqlite3
была
написана
Герхардом
Херингом.
Он
предоставляет
интерфейс
SQL,
совместимый
со
спецификацией
DB-API
2.0,
описанной PEP 249 и требует SQLite 3.15.2 или более
поздней версии.

21. 15. Работа с базами данных

21
import sqlite3
# Подключение к базе данных
conn = sqlite3.connect('example.db')
# Создание курсора
cur = conn.cursor()
# Выполнение запроса
cur.execute('SELECT * FROM users')
# Обработка результата
rows = cur.fetchall()
for row in rows:

22. 15. Работа с базами данных

22
Транзакции:
Методы commit() и rollback() позволяют управлять
транзакциями. Все изменения в базе данных могут быть
выполнены в рамках одной транзакции, и их можно
зафиксировать с помощью commit() или отменить с
помощью rollback(). Это важно для обеспечения
целостности данных.

23. 15. Работа с базами данных

23
Объект-курсор (Cursor)
Методы объекта-курсора:
execute():
Выполняет SQL-запрос. Это основной метод для
выполнения одиночных SQL-запросов, таких как SELECT,
INSERT, UPDATE и другие.
cur.execute("SELECT * FROM users WHERE age > 30")

24. 15. Работа с базами данных

24
executemany():
Выполняет несколько SQL-запросов за один вызов, что
полезно для массовых операций, например, при
вставке нескольких строк данных.
user_list = [("Alice", 25), ("Bob", 30),
("Charlie", 35)]
cur.executemany("INSERT INTO users (name, age)
VALUES (?, ?)", [(name, age) for name, age in
user_list])

25. 15. Работа с базами данных

25
fetchone():
Возвращает одну строку результата запроса. Если
данных больше нет, возвращает None.
row = cur.fetchone()
fetchall():
Возвращает все строки результата запроса как список
кортежей.
rows = cur.fetchall()

26. 15. Работа с базами данных

26
close():
Закрывает курсор после завершения работы. Это
важно для освобождения ресурсов.
cur.close()

27. 15. Работа с базами данных

27
Атрибуты курсора:
rowcount:
Количество строк, затронутых последним запросом.
Например, для SELECT возвращает количество строк в
результате.
print(cur.rowcount) # количество
строк, затронутых запросом

28. 15. Работа с базами данных

28
description:
Содержит
описание
столбцов,
возвращаемых
результатом запроса. Это кортеж, где каждый элемент
— это описание одного столбца.
print(cur.description)
столбцов результата
# описание

29. 15. Работа с базами данных

29
arraysize:
Количество строк, которые будут извлечены за один раз
при вызове fetchmany(). Этот атрибут можно настроить
для улучшения производительности при получении
больших объемов данных.
cur.arraysize = 10
# изменяем размер пакета

30. 15. Работа с базами данных

30
Типы данных и конструкторы
Типы данных в DB API 2.0: DB API 2.0 поддерживает
несколько
стандартных
типов
данных,
которые
используются для представления различных типов
информации в базе данных:
1. STRING:
Представляет строковые данные. Используется для
хранения текстовых значений.
cur.execute("SELECT name FROM users")

31. 15. Работа с базами данных

31
2. NUMBER:
Представляет числовые данные, как целые числа или
числа с плавающей запятой.
cur.execute("SELECT age FROM users")
3. DATETIME:
Представляет дату и время. Этот тип данных
используется для хранения временных значений.
cur.execute("SELECT birth_date FROM users")

32. 15. Работа с базами данных

32
2. NUMBER:
Представляет числовые данные, как целые числа или
числа с плавающей запятой.
cur.execute("SELECT age FROM users")
3. DATETIME:
Представляет дату и время. Этот тип данных
используется для хранения временных значений.
cur.execute("SELECT birth_date FROM users")

33. 15. Работа с базами данных

33
4. BINARY:
Представляет
двоичные
изображения или файлы.
данные,
например,
cur.execute("SELECT profile_picture FROM users")

34. 15. Работа с базами данных

34
Конструкторы для работы с датами и временем:
DB API 2.0 включает несколько вспомогательных классов
для работы с датами и временем, которые могут быть
использованы для вставки или извлечения временных
данных в БД:
1. Date():
Используется для создания объекта, представляющего
только дату (без времени).
from datetime import date
my_date = date(2023, 4, 9)

35. 15. Работа с базами данных

35
2. Time():
Используется для создания объекта, представляющего
только время (без даты).
from datetime import time
my_time = time(14, 30, 0)
3. Timestamp():
Используется для создания объекта, представляющего
дату и время (с точностью до секунд).
from datetime import datetime
my_timestamp = datetime(2023, 4, 9, 14, 30, 0)

36. 15. Работа с базами данных

36
Пример вставки данных в базу с использованием типов
данных:
import sqlite3
from datetime import datetime
# Подключение к базе данных
conn = sqlite3.connect('example.db')
cur = conn.cursor()
# Вставка данных с использованием различных типов
birth_date = datetime(2000, 5, 15)
cur.execute("INSERT INTO users (name, birth_date)
VALUES (?, ?)", ('Alice', birth_date))

37. 15. Работа с базами данных

37
Обработка ошибок
Иерархия исключений в DB API 2.0:
DB API 2.0 предоставляет несколько типов исключений,
которые помогают обрабатывать ошибки, возникающие
при взаимодействии с базой данных. Все ошибки в DB
API являются подклассами базового исключения Error.
1. Error:
Базовый класс для всех исключений, связанных с базой
данных.

38. 15. Работа с базами данных

38
try:
cur.execute("SELECT * FROM non_existent_table")
except sqlite3.Error as e:
print(f"Database error: {e}")

39. 15. Работа с базами данных

39
2. DatabaseError:
Возникает при ошибках, связанных с выполнением SQLзапросов, например, синтаксических ошибок.
try:
cur.execute("SELECT * FROM users WHERE age
= 'non_integer'")
except sqlite3.DatabaseError as e:
print(f"SQL query error: {e}")

40. 15. Работа с базами данных

40
3. IntegrityError:
Ошибка, возникающая при нарушении целостности
данных, например, при попытке вставить дубликат в
уникальное
поле
или
нарушении
ограничений
внешнего ключа.
try:
cur.execute("INSERT INTO users (id, name)
VALUES (1, 'Alice')")
cur.execute("INSERT INTO users (id, name)
VALUES (1, 'Bob')")
except sqlite3.IntegrityError as e:
print(f"Integrity constraint violated: {e}")

41. 15. Работа с базами данных

41
4. OperationalError:
Возникает при ошибках в ходе выполнения запроса или
при проблемах с подключением к базе данных,
например, из-за отсутствия доступа к базе данных или
сбоя сети.
import psycopg2
try:
conn =
psycopg2.connect("dbname=non_existent_db")
except psycopg2.OperationalError as e:
print(f"Operational error: {e}")

42. 15. Работа с базами данных

42
5. ProgrammingError:
Ошибка, возникающая при неправильной подготовке
запроса, например, если запрос имеет некорректную
синтаксическую структуру или переданы неправильные
параметры.
try:
cur.execute("SELECT * FROM users WHERE
id = %s", ('incorrect',))
except sqlite3.ProgrammingError as e:
print(f"Programming error: {e}")

43. 15. Работа с базами данных

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

44. 15. Работа с базами данных

44
import sqlite3
try:
conn = sqlite3.connect('example.db')
cur = conn.cursor()
# Ошибочный запрос
cur.execute("SELECT * FROM non_existent_table")
except sqlite3.DatabaseError as e:
print(f"Database error occurred: {e}")
conn.rollback()
finally:

45. 15. Работа с базами данных

45
Рекомендации:
Всегда обрабатывайте ошибки с использованием
соответствующих исключений.
Используйте try-except-finally, чтобы гарантировать
закрытие соединений и курсоров, даже если
произошла ошибка.

46. 15. Работа с базами данных

46
Пример использования DB API 2.0
Общий процесс работы с базой данных через DB API 2.0:
1. Подключение: Создаем соединение с базой данных.
2. Курсор: Создаем объект курсора для выполнения SQLзапросов.
3. Запрос: Выполняем
курсора.
SQL-запросы
с
помощью
4. Результат: Извлекаем и обрабатываем результаты.
5. Закрытие: Закрываем курсор и соединение.

47. 15. Работа с базами данных

47
Пример работы с SQLite:
В этом примере мы подключаемся к базе данных SQLite,
выполняем SQL-запрос для извлечения данных и
обрабатываем результат:
import sqlite3
# 1. Подключение к базе данных
conn = sqlite3.connect('example.db')
# 2. Создание курсора
cur = conn.cursor()
# 3. Выполнение запроса

48. 15. Работа с базами данных

48
Пример работы с psycopg2 (PostgreSQL):
В этом примере подключаемся к базе данных
PostgreSQL,
выполняем
запрос
и
обрабатываем
результаты:
import psycopg2
# 1. Подключение к базе данных
conn = psycopg2.connect("dbname=test user=postgres pa
# 2. Создание курсора
cur = conn.cursor()
# 3. Выполнение запроса

49. 15. Работа с базами данных

49
2. ORM-технология
ORM (Object-Relational Mapping) — это метод
программирования, который позволяет работать с базой
данных, используя объектно-ориентированный подход.
ORM
автоматически
преобразует
данные
из
реляционной базы данных в объекты, с которыми можно
работать в коде как с обычными Python-объектами.
Объектно-реляционное отображение: ORM позволяет
разработчикам работать с таблицами базы данных как
с
классами
Python.
Каждая
строка
таблицы
представляется как объект класса, а каждый столбец —
как атрибут объекта.

50. 15. Работа с базами данных

50
Как работает ORM: В традиционном подходе с DB API
2.0 необходимо писать SQL-запросы для извлечения
данных и их обработки. ORM скрывает детали SQL и
позволяет работать с данными как с обычными
объектами.
Например, вместо написания SQL-запроса для вставки
данных в таблицу:
cur.execute("INSERT INTO users (name,
age) VALUES ('Alice', 30)")

51. 15. Работа с базами данных

51
с использованием ORM можно просто создать объект:
user = User(name='Alice', age=30)
user.save()

52. 15. Работа с базами данных

52
Основные преимущества ORM:
Простота и удобство: ORM позволяет быстро и легко
взаимодействовать с базой данных, не написав ни
одной строки SQL-кода.
Повышение
производительности:
ORM
автоматизирует выполнение стандартных операций с
базой данных, таких как создание, чтение, обновление
и удаление данных (CRUD).

53. 15. Работа с базами данных

53
Поддержка
миграций:
ORM
автоматически
отслеживает изменения в структуре базы данных,
такие как добавление новых полей или изменение
типов данных, и помогает синхронизировать эти
изменения с реальной базой данных.
Поддержка разных СУБД: ORM часто предоставляет
абстракцию, позволяющую работать с несколькими
СУБД, скрывая различия между ними.

54. 15. Работа с базами данных

54
Пример работы с ORM: Рассмотрим пример с
использованием
популярной
ORM-библиотеки
SQLAlchemy:
from sqlalchemy import create_engine, Column,
Integer, String
from sqlalchemy.ext.declarative import
declarative_base
from sqlalchemy.orm import sessionmaker
Base = declarative_base()
class User(Base):
__tablename__ = 'users'

55. 15. Работа с базами данных

55
ORM представляет собой мощный инструмент для
работы с базами данных в Python, который позволяет
разрабатывать приложения быстрее и с меньшим
количеством кода. ORM подходит для большинства
типичных случаев работы с базой данных, скрывая
сложность SQL и давая удобный интерфейс для
разработки.

56. 15. Работа с базами данных

56
Сравнение DB API 2.0 и ORM
Критерий
Уровень доступа
Язык запросов
Гибкость
Простота
DB API 2.0
ORM
Высокоуровневый
Низкоуровневый
(объектно(работа с базой данных
ориентированный
напрямую)
доступ)
Python-классы
Чистый SQL
(автоматическая
генерация SQL)
Максимальная, требует Удобство, не всегда
написания SQLгибкость в сложных
запросов
запросах
Удобен для операций
Менее удобен для
CRUD (создание,
быстрого
чтение, обновление,
прототипирования
удаление)
Подходит для типичных

57. 15. Работа с базами данных

57
Уровень
доступа:
DB
API
2.0
предоставляет
низкоуровневый интерфейс для работы с базой данных,
где каждый запрос нужно писать вручную. ORM, в свою
очередь, обеспечивает высокоуровневую работу с
объектами, скрывая детали SQL.
Язык запросов: В DB API 2.0 мы напрямую пишем SQLзапросы. В ORM же мы манипулируем Pythonобъектами, а библиотека автоматически генерирует
SQL-запросы для взаимодействия с базой данных.

58. 15. Работа с базами данных

58
Гибкость: DB API 2.0 предоставляет максимальную
гибкость в написании запросов, так как мы пишем SQLкод вручную. Однако это требует более глубоких знаний
SQL. ORM же удобнее для простых операций, но иногда
не поддерживает все возможности SQL (например,
сложные подзапросы или нестандартные типы данных).
Простота: ORM значительно упрощает работу с базой
данных, особенно для базовых операций CRUD, так как
большинство действий выполняются с использованием
Python-классов и методов. В DB API 2.0 каждый запрос
нужно писать вручную, что требует больше кода.

59. 15. Работа с базами данных

59
Использование: DB API 2.0 лучше подходит для сложных
запросов, которые требуют оптимизации и полного
контроля над выполнением SQL. ORM используется для
типичных операций, таких как создание, чтение,
обновление и удаление данных, обеспечивая быстрое и
удобное решение.
Миграции: В DB API 2.0 миграции базы данных нужно
реализовывать вручную, если они требуются. В ORM же,
например, в Django или SQLAlchemy, миграции обычно
поддерживаются встроенными инструментами, что
облегчает процесс обновления структуры базы данных.

60. 15. Работа с базами данных

60
Производительность: В DB API 2.0 можно полностью
контролировать SQL-запросы, что позволяет достигать
максимальной производительности. ORM же иногда
может быть немного медленнее из-за абстракции, хотя
в большинстве случаев разница в производительности
незначительна для большинства приложений.
Портируемость: ORM библиотеки часто скрывают
детали работы с конкретной СУБД, что позволяет легко
переносить приложение между различными базами
данных. В DB API 2.0 переносимость ограничена
спецификой SQL-запросов для каждой СУБД.

61. 15. Работа с базами данных

61
Заключение:
DB API 2.0 и ORM — это два разных подхода для работы с
базами данных. DB API 2.0 предоставляет полный
контроль и гибкость, но требует больше кода и знаний.
ORM, с другой стороны, облегчает разработку и
ускоряет процесс создания приложений, скрывая
детали SQL и предлагая удобный интерфейс для работы
с базой данных. Выбор между ними зависит от
конкретных задач проекта и требований к гибкости и
производительности.

62. 15. Работа с базами данных

62
Когда использовать DB API 2.0:
1. Контроль и оптимизация:
Если требуется полный контроль над SQLзапросами для оптимизации производительности,
например,
для
сложных
объединений
(JOIN),
подзапросов или оптимизации запросов.
Когда необходимо точно управлять тем, как SQLзапросы
выполняются,
например,
в
больших
распределённых системах или при работе с
огромными объёмами данных.

63. 15. Работа с базами данных

63
Когда использовать DB API 2.0:
2. Сложные запросы и специфические особенности
СУБД:
Если проект требует работы с специфичными
возможностями
конкретной
СУБД
(например,
хранимые
процедуры,
оконные
функции
или
специфичные типы данных).
В случае, когда используется менее популярная или
нестандартная СУБД, для которой нет хорошо
поддерживаемой ORM.

64. 15. Работа с базами данных

64
Когда использовать DB API 2.0:
3.
Минимизация зависимостей:
Когда важно сократить количество сторонних
библиотек и зависимостей в проекте. DB API 2.0 — это
стандарт, и многие СУБД поддерживают его из коробки,
без необходимости в дополнительных библиотеках.
4.
Обработка ошибок и исключений:
Когда требуется точная настройка обработки
ошибок и исключений, например, для работы с
транзакциями или восстановлением после сбоев.

65. 15. Работа с базами данных

65
Когда использовать ORM:
1. Упрощение разработки:
Когда нужно быстро разрабатывать приложение, не
тратя много времени на написание SQL-запросов
вручную. ORM позволяет работать с базой данных через
Python-объекты, что делает код более читаемым и
удобным для разработки.
Для типичных операций CRUD (создание, чтение,
обновление,
удаление),
которые
встречаются
в
большинстве приложений.

66. 15. Работа с базами данных

66
Когда использовать ORM:
2.
Масштабируемость:
Когда
необходимо
быстро
масштабировать
приложение, добавлять новые модели, миграции и
обеспечивать поддержку различных СУБД. ORM часто
предоставляет
автоматические
инструменты
для
миграций и синхронизации структуры базы данных.

67. 15. Работа с базами данных

67
Когда использовать ORM:
3.
Чистота кода:
Когда важно, чтобы код был более абстрагирован
от низкоуровневых SQL-деталей, что делает проект
более чистым, поддерживаемым и удобным для
командной работы.

68. 15. Работа с базами данных

68
Когда использовать ORM:
4.
Интеграция
фреймворка:
с
другими
компонентами
ORM часто интегрируется с другими частями
фреймворка. Например, в Django ORM тесно связано с
формами, админкой и другими компонентами, что
упрощает создание полноценного веб-приложения.

69. 15. Работа с базами данных

69
Комбинированное использование:
В некоторых случаях, особенно в крупных проектах,
может быть полезно сочетать DB API 2.0 и ORM.
Например, для большинства операций использовать
ORM, а для сложных запросов, которые требуют
максимальной производительности или специфических
возможностей базы данных, — использовать DB API 2.0.
Преимущества:
ORM упрощает повседневные задачи, в то время
как DB API 2.0 позволяет вручную оптимизировать
запросы для сложных случаев.