Технология Python DB-API

Лекция Управление базами данных из Python-программ Технология Python DB-API Python DB-API – это не конкретная библиотека, а набор правил, которым подчиняются отдельные модули, реализующие работу с конкретными базами данных. Отдельные нюансы реализации для разразличных баз могут отличаться, но общие принципы позволяют использовать один и тот же подход при работе с разными базами данных (рисунок 1). Рис. 1. Python DB-API методы Схематично работа с базой данных из программы выглядит так.  Подключение к базе данных (вызов connect() с получением объектасоединения).  Создание одного или нескольких курсоров (вызов метода объекта-соединения cursor() с получением объекта-курсора).  Выполнение запроса (вызов метода execute() или его вариантов).  Получение результатов запроса (вызов метода fetchone() или его вариантов).  Завершение транзакции или ее откат (вызов методов объекта-соединения commit() или rollback()).  Когда все необходимые транзакции произведены, подключение закрывается вызовом метода close() объекта-соединения. Рассмотрим технологию Python DB-API на примере базы данных SQLite. Python имеет встроенную поддержку SQLite базы данных, для этого ничего дополнительно устанавливать не нужно, достаточно в скрипте указать импорт стандартной библиотеки: import sqlite3 1 1. Доступ к базе данных осуществляется с помощью объекта-соединения, который создается с помощью метода connection(), в качестве параметра передается имя базы данных: con = sqlite3.connect("library.sqlite") В результате выполнения этого оператора создается объект-соединение con с базой данных library.sqlite. Если базы данных с таким именем в текущей папке нет, то база данных будет создана, в противном случае связь устанавливается с существующей базой данных. 2. Объект-соединение con содержит следующие методы:  close() – закрывает соединение с базой данных;  commit() – завершает транзакцию, то есть изменяет данные в базе данных;  rollback() – «откатывает» начатую транзакцию (восстанавливает исходное состояние).  cursor()– возвращает объект-курсор, использующий данное соединение. 3. Для работы с базой данных необходимо создать объект-курсор: cursor = con.cursor() Курсор (от англ. cursor - CURrrent Set Of Records, текущий набор записей) служит для работы с результатом запроса. Результатом запроса обычно является одна или несколько таблиц. Программа может читать и обрабатывать полученные таблицы и записи в таблице по одной, поэтому в курсоре хранится информация о текущей таблице и записи. Конкретный курсор в любой момент времени связан с выполнением одной SQL инструкции. Методы объекта-курсора:  execute(query[, parameters]) – выполняет запрос к базе данных, может включать необязательные параметры;  executescript(script[, parameters]) – выполняет несколько запросов;  fetchall() – возвращает все записи результата запроса;  fetchone() – возвращает текущую запись из результата запроса или None при отсутствии данных. Для того, чтобы выполнить запрос к базе данных используется метод execute(). Например, выберем из базы все жанры (будет работать, если в базе данных есть таблица genre): cursor.execute("SELECT * FROM genre") При использовании метода можно запустить на выполнение несколько запросов корректировки данных с помощью метода executescript(). При этом, чтобы располагать запросы на нескольких строках, вместо кавычек используются «'''» (три подряд стоящие одинарные или двойные кавычки). Например, создадим и заполним таблицу genre, предварительно удалив ее, если она в базе данных есть: cursor.executescript(""" DROP TABLE IF EXISTS genre; CREATE TABLE genre( 2 genre_id INTEGER PRIMARY KEY AUTOINCREMENT, genre_name VARCHAR(30) ); INSERT INTO genre(genre_name) VALUES ('Роман'),( 'Поэзия'),( 'Приключения'); """) Для получения всех данных, связанных с курсором, используется fetchall(). Например, получим из базы все жанры и выведем результат: метод cursor.execute("SELECT * FROM genre") print(cursor.fetchall()) Результат: [(1, 'Роман'), (2, 'Поэзия'), (3, 'Приключения')] При этом после обращения к методу, курсор перестает быть связан с отобранными запросом данными: print(cursor.fetchall()) Результат: [] Если результаты запроса будут нужны в программе в дальнейшем, необходимо сохранить их в переменную: cursor.execute("SELECT * FROM genre") result = cursor.fetchall() print(result) print(result) Результат: [(1, 'Роман'), (2, 'Поэзия'), (3, 'Приключения')] [(1, 'Роман'), (2, 'Поэзия'), (3, 'Приключения')] Переменная result представляет собой двухмерный список, это позволяет обратиться к отдельным значениям: cursor.execute("SELECT * FROM genre") result = cursor.fetchall() print(result[1]) print(result[2][0]) Результат: (2, 'Поэзия') 3 Для получения текущей записи таблицы отобранных данных, связанных с курсором, используется метод fetchone(). Например, отберем из базы все жанры и выведем результат: cursor.execute("SELECT * FROM genre") print(cursor.fetchone()) print(cursor.fetchone()) print(cursor.fetchone()) print(cursor.fetchone()) Результат: (1, 'Роман') (2, 'Поэзия') (3, 'Приключения') None 3 Перед первым применением fetchone()курсор установлен на начало отобранных из базы данных. При обращении к методу первый раз становится доступной первая запись результата запроса, второй раз – вторая и так далее. Когда записи исчерпаны, метод fetchone()возвращает None. 4. Если методами execute() и executescript() исполнялись запросы корректировки данных, необходимо выполнить транзакцию, то есть изменить данные в базе данных с помощью метода commit(): con.commit() Например: cursor.executescript(""" DROP TABLE IF EXISTS genre; CREATE TABLE genre( genre_id INTEGER PRIMARY KEY AUTOINCREMENT, genre_name VARCHAR(30) ); INSERT INTO genre(genre_name) VALUES ('Роман'),( 'Поэзия'),( 'Приключения'); """) cursor.execute("SELECT * FROM genre") print(cursor.fetchall()) Результат: [(1, 'Роман'), (2, 'Поэзия'), (3, 'Приключения')] 5. После того, как работа с базой закончена, необходимо закрыть подключение: con.close() Важно! Файл SQLite с базой данных (со структурой таблиц и их наполнением) создается один раз (для этого используется одна программа Python). Для дальнейшей работы необходимо создать новую программу на Python, в которой только устанавливать соединение с базой. Самостоятельное задание 1. Создать таблицу publisher и таблицу book следующей структуры: Рис.2. Логическая структура базы данных 4 2. Наполнить таблицы информацией. publisher: Название ЭКСМО ДРОФА АСТ book (это результат запроса по трем таблицам, выполнить заполнение таблицы в соответствии с ее структурой): Название Мастер и Маргарита Таинственный остров Бородино Дубровский Вокруг света за 80 дней Убийства по алфавиту Затерянный мир Герой нашего времени Смерть поэта Поэмы Жанр Роман Приключения Поэзия Роман Приключения Роман Приключения Роман Поэзия Поэзия Издат-во ДРОФА ДРОФА АСТ ДРОФА ДРОФА ЭКСМО ЭКСМО АСТ ЭКСМО АСТ Год 2014 2015 2015 2020 2019 2017 2020 2017 2020 2019 Кол-во 5 10 12 7 5 9 3 2 2 5 Выполнение запросов с параметрами При выполнении SQL запросов можно указывать параметры, которые будут подставляться в запрос: execute("..... :p_1 .... : p_2", {"p_1": значение, "p_2": значение}) Первый параметр в методе – запрос, в котором используется две переменные (p_1, p_2), перед каждой переменной вставляется символ двоеточие «:», второй параметр – список, в котором указывается какое конкретное значение нужно подставить в запрос при его выполнении. Например, выведем книги заданного издательства: cursor.execute(''' SELECT title FROM book JOIN publisher USING (publisher_id) WHERE publisher_name = :p_publisher ''', {"p_publisher": "АСТ"}) print(cursor.fetchall()) Результат: [('Бородино',), ('Поэмы',), ('Герой нашего времени',)] Теперь, чтобы посмотреть книги другого издательства, достаточно изменить значение параметра p_publisher: cursor.execute(''' SELECT title FROM 5 book JOIN publisher USING (publisher_id) WHERE publisher_name = :p_publisher ''', {"p_publisher": "ДРОФА"}) print(cursor.fetchall()) Результат: [('Мастер и Маргарита',), ('Таинственный ('Вокруг света за 80 дней',)] остров',), ('Дубровский',), В случае если параметр нужно использовать внутри текстовой строки, например, при использовании оператора LIKE, текстовую строку нужно разбить на несколько частей, чтобы параметр получился без кавычек, а затем полученные части соединить оператором текстового объединения SQLite «||» (две подряд идущие вертикальные черты). Например, рассмотрим, как записать шаблон поиска названия книги, которое содержит текстовый фрагмент «по»: title LIKE "%по%" -> title LIKE "%" || "по" || "%" -> title LIKE "%" || :p_text || "%" Например, выведем книги, название которых содержит заданный текстовый фрагмент: cursor.execute(''' SELECT title FROM book WHERE title LIKE "%" || :p_text || "%" ''', {"p_text": "по"}) print(cursor.fetchall()) Результат: [('Убийства по алфавиту',), ('Смерть поэта',)] Модуль pandas и SQLite Модуль pandas – программная библиотека, реализованная на языке Python, предназначенная для обработки и анализа данных. В начале программы необходимо этот модуль импортировать: import pandas as pd Одним из методов этой библиотеки является read_sql(), который возвращает результат выполнения запроса в виде столбцов DataFrame. df = pd.read_sql(запрос_SQL, имя_объекта_соединения, params={"имя": значение, ...}) Первый параметром указывается SQL запрос, который нужно выполнить, затем задается объект-соединение. Последний параметр является необязательным, используется в запрос нужно подставить значения переменных. Например: df = pd.read_sql(''' SELECT title AS Название, publisher_id AS Издательство, genre_id AS Жанр, year_publication AS Год FROM book WHERE genre_id = :p_genre ''', con, params={"p_genre": 3}) 6 print(df) Результат: 1 2 Название Таинственный остров Вокруг света за 80 дней Затерянный мир Издательство 2 2 1 Жанр 3 3 3 Год 2015 2019 2020 Функция read_sql()возвращает результат запроса в виде DataFrame. Эта структура данных похожа на обычную таблицу, состоящую из строк и столбцов, на пересечении которых расположено поле. Каждой строке присваивается метка (в нашем примере 0, 1, 2), по которой можно обратиться к строке с помощью метода loc[метка]: print(df.loc[1]) Результат: Название Вокруг света за 80 дней Издательство 2 Жанр 3 Год 2019 Name: 1, dtype: object Можно выделить отдельный столбец DataFrame, для этого после имени переменной в квадратных скобках указывается имя столбца в кавычках. print(df["Название"]) Результат: Таинственный остров 1 Вокруг света за 80 дней 2 Затерянный мир Name: Название, dtype: object Количество строк и столбцов в DataFrame определяется с помощью метода shape[измерение]. Если в качестве измерения указать 0 – то будет вычисляться количество строк, если 1 – то количество столбцов: print("Количество строк:", df.shape[0]) print("Количество столбцов:", df.shape[1]) Результат: Количество строк: 3 Количество столбцов: 4 Для того чтобы получить значение поля, используется метод at[метка_строки, имя_столбца]: print(df.at[1,"Год"]) Результат: 2019 Чтобы получить dtypes.index: название столбцов в DataFrame используется метод print(df.dtypes.index) Результат: Index(['Название', 'Издательство', 'Жанр', 'Год'], dtype='object') Самостоятельное задание Отобрать информацию о книгах, количество которых больше 3. Столбцы назвать Книга, Жанр, Издательство и Количество. 7 Вывести отобранную информацию: - в виде таблицы; - только столбец Название; - 3-ю строку результата запроса; - количество строк и столбцов в результате запроса; - названия столбцов. Использование f-строк для передачи параметров в запросы Начиная с версии 3.6, в Python появился новый тип строк — f-строки, которые буквально означают «formatted string». Эти строки улучшают читаемость кода, а также работают быстрее, чем другие способы форматирования. F-строки задаются с помощью литерала «f» перед кавычками: "обычная строка" f"f-строка" F-строки делают очень простую вещь — они берут значения переменных, которые есть в текущей области видимости программы, и подставляют их в строку. В самой строке нужно указать имя этой переменной в фигурных скобках. name = "Дмитрий" age = 25 print(f"Меня зовут {name}. Мне {age} лет.") Результат: Меня зовут Дмитрий. Мне 25 лет. Например, создадим список genre_list с id жанров и отберем все книги, относящиеся к этим жанрам. При этом список необходимо представить в виде кортежа (перечислить значения в круглых скобках), чтобы его можно было использовать в SQL запросах: genre_list = (2, 3) df = pd.read_sql(f''' SELECT title AS Название, publisher_id AS Издательство, genre_id AS Жанр, year_publication AS Год FROM book WHERE genre_id in {genre_list} ''', con) print(df) Результат: 1 2 3 4 5 Название Таинственный остров Бородино Вокруг света за 80 дней Затерянный мир Смерть поэта Поэмы Издательство 2 3 2 1 1 3 8 Жанр 3 2 3 3 2 2 Год 2015 2015 2019 2020 2020 2019