Создание таблиц, добавление данных и настройка ~ PythonRu

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

  • Загрузка библиотеки
  • Создание и соединение с базой данных
  • Создание таблиц базы данных
  • Добавление данных
  • Запросы на получение данных
  • Удаление данных
  • И многое другое!

SQLite3 (часто говорят просто SQLite) — это часть стандартного пакета Python 3, поэтому ничего дополнительно устанавливать не придется.

Типы данных SQLite в Python

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

  • NULL — значение NULL
  • INTEGER — целое число
  • REAL — число с плавающей точкой
  • TEXT — текст
  • BLOB — бинарное представление крупных объектов, хранящееся в точности с тем, как его ввели

К сожалению, других привычных для SQL типов данных в SQLite нет.

Первые шаги с SQLite в Python

Начнем руководство с загрузки библиотеки. Для этого нужно использовать следующую команду:

Следующий шаг — создание базы данных.

Создание базы данных SQLite в Python

Есть несколько способов создания базы данных в Python с помощью SQLite. Для этого используется объект Connection, который и представляет собой базу. Он создается с помощью функции connect().

Создадим файл. db, поскольку это стандартный способ управления базой SQLite. Файл будет называться orders. За соединение будет отвечать переменная conn.

conn sqlite3connect

Эта строка создает объект connection, а также новый файл orders. db в рабочей директории. Если нужно использовать другую, ее нужно обозначить явно:

conn sqlite3connectr

Если файл уже существует, то функция connect осуществит подключение к нему.

перед строкой с путем стоит символ «r». Это дает понять Python, что речь идет о «сырой» строке, где символы «/» не отвечают за экранирование.

Функция connect создает соединение с базой данных SQLite и возвращает объект, представляющий ее.

Резидентная база данных

Еще один способ создания баз данных с помощью SQLite в Python — создание их в памяти. Это отличный вариант для тестирования, ведь такие базы существуют только в оперативной памяти.

conn sqlite3connectmemory

Однако в большинстве случаев (и в этом руководстве) будет использоваться описанный до этого способ.

Создание объекта cursor

После создания объекта соединения с базой данных нужно создать объект cursor. Он позволяет делать SQL-запросы к базе. Используем переменную cur для хранения объекта:

cur conncursor

Теперь выполнять запросы можно следующим образом:

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

Создание таблиц в SQLite в Python

Пришло время создать первую таблицу в базе данных. С объектами соединения (conn) и cursor (cur) это можно сделать. Будем следовать этой схеме.

Создание таблиц, добавление данных и настройка ~ PythonRu

В коде выше выполняются следующие операции:

  • Функция execute отвечает за SQL-запрос
  • IF NOT EXISTS поможет при попытке повторного подключения к базе данных. Запрос проверит, существует ли таблица. Если да — проверит, ничего ли не поменялось.
  • Сохраняем изменения с помощью функции commit для объекта соединения.

Для создания второй таблицы просто повторим последовательность действий, используя следующие команды:

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

Добавление данных с SQLite в Python

По аналогии с запросом для создания таблиц для добавления данных также нужно использовать объект cursor.

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

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

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

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

Но нужно использовать функцию executemany вместо обычной execute:

Если применить execute, то функция подумает, то пользователь хочет передать в таблицу два объекта (два кортежа), а не два кортежа, каждый из которых содержит по 4 значения для каждого пользователя. Хотя в первую очередь вообще должна была возникнуть ошибка.

SQLite и предотвращение SQL-инъекций

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

customers

orders

Используйте следующие запросы:

curexecutemany customers
curexecutemany»INSERT INTO orders VALUES(?, ?, ?, ?);» orders
conncommit

Получение данных с SQLite в Python

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

Использование fetchone() в SQLite в Python

Начнем с использования функции fetchone(). Создадим переменную one_result для получения только одного результата:

curexecute
one_result curfetchone
one_result

Она вернет следующее:

Использование fetchmany() в SQLite в Python

Если же нужно получить много данных, то используется функция fetchmany(). Выполним другой скрипт для генерации 3 результатов:

curexecute
three_results curfetchmany
three_results

Он вернет следующее:

Использование fetchall() в SQLite в Python

Функцию fetchall() можно использовать для получения всех результатов. Вот что будет, если запустить скрипт:

curexecute
all_results curfetchall
all_results

Удаление данных в SQLite в Python

Теперь рассмотрим процесс удаления данных с SQLite в Python. Здесь та же структура. Предположим, нужно удалить любого пользователя с фамилией «Parker». Напишем следующее:

Если затем сделать следующей запрос:

Будет выведен пустой список, подтверждающий, что запись удалена.

Объединение таблиц в SQLite в Python

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

Для этого напишем следующее:

Тот же подход работает с другими SQL-операциями.

Понимание SQLite-подключения в подробностях

  • С помощью метода connect() выполняется подключение к базе данных. Этот метод возвращает объект подключения SQLite.
  • Объект connection не является потокобезопасным. Модуль sqlite3 не позволяет делиться подключением между потоками. Если попытаться сделать это, то можно получить исключение.
  • Метод connect() принимает разные аргументы. В этом примере передается название базы данных.
  • С помощью объекта соединения создается объект cursor, который позволяет выполнять SQLite-запросы из Python.
  • Для одного соединения можно создать неограниченное количество cursor. Он также не является потокобезопасным. Модуль не позволяет делиться объектами cursor между потоками. Если это сделать, то будет ошибка.

После этого создается запрос для получения версии базы данных.

  • С помощью метода execute объекта cursor можно выполнить запрос в базу данных из Python. Он принимает SQLite-запрос в качестве параметра и возвращает resultSet — то есть, строки базы данных
  • Получить результат запроса из resultSet можно с помощью методов, например, fetchAll()
  • В этом примере SELECT version(); выполняется для получения версии базы данных SQLite.

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

  • С помощью класса sqlite3.Error можно обработать любую ошибку и исключение, которые могут появиться при работе с SQLite из Python.
  • Это позволит сделать приложение более отказоустойчивым. Класс sqlite3.Error позволит понять суть ошибки. Он возвращает сообщение и код ошибки.

cursor. close() и connection. close()

Создание таблицы SQLite в Python

В этом разделе разберемся, как создавать таблицы в базе данных SQLite с помощью Python и модуля sqlite3. Создание таблицы — это DDL-запрос, выполняемый из Python.

В этом примере создадим базу sqlitedb_developers в базе данных sqlite_python.

Шаги для создания таблицы в SQLite с помощью Python:

  • Соединиться с базой данных с помощью sqlite3.connect(). Речь об этом шла в первом разделе.
  • Подготовить запрос создания таблицы.
  • Выполнить запрос с помощью cursor.execute(query).
  • Закрыть соединение с базой и объектом cursor.

import sqlite3try:
sqlite_connection = sqlite3. connect(‘sqlite_python. db’)
sqlite_create_table_query = »’CREATE TABLE sqlitedb_developers (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email text NOT NULL UNIQUE,
joining_date datetime,
salary REAL NOT NULL);»’cursor = sqlite_connection. cursor()
print(«База данных подключена к SQLite»)
cursor. execute(sqlite_create_table_query)
sqlite_connection. commit()
print(«Таблица SQLite создана»)except sqlite3. Error as error:
print(«Ошибка при подключении к sqlite», error)
finally:
if (sqlite_connection):
sqlite_connection. close()
print(«Соединение с SQLite закрыто»)

База данных подключена к SQLite
Таблица SQLite создана
Соединение с SQLite закрыто

Создание таблиц, добавление данных и настройка ~ PythonRu

Типы данных SQLite и соответствие типам Python

Перед переходом к выполнению CRUD-операций в SQLite из Python сначала нужно разобраться с типами данных SQLite и соответствующими им типами данных в Python, которые помогают хранить и считывать данные из таблицы.

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

Типы данных SQLite:

  • NULL — значение NULL
  • INTEGER — числовые значения. Целые числа хранятся в 1, 2, 3, 4, 6 и 8 байтах в зависимости от величины
  • REAL — числа с плавающей точкой, например, 3.14, число Пи
  • TEXT — текстовые значения. Могут храниться в кодировке UTF-8, UTF-16BE или UTF-16LE
  • BLOB — бинарные данные. Для хранения изображений и файлов

Следующие типы данных из Python без проблем конвертируются в SQLite. Для конвертации достаточно лишь запомнить эту таблицу.

Выполнение SQL запросов с помощью функции executescript

Скрипты SQLite отлично справляются со стандартными задачами. Это набор SQL-команд, сохраненных в файле (в формате. sql). Один файл содержит одну или больше SQL-операций, которые затем выполняются из командной строки.

Дальше несколько распространенных сценариев использования SQL-скриптов

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

Выполнить скрипт из командной строки SQLite можно с помощью команды. read:

Например, этот простой скрипт создает две таблицы.

CREATE TABLE fruits (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
price REAL NOT NULL
);

CREATE TABLE drinks (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
price REAL NOT NULL
);

Теперь посмотрим, как выполнить его из Python.

import sqlite3try:
sqlite_connection = sqlite3. connect(‘sqlite_python. db’)
cursor = sqlite_connection. cursor()
print(«База данных подключена к SQLite»)with open(‘sqlite_create_tables. sql’, ‘r’) as sqlite_file:
sql_script = sqlite_file. read()cursor. executescript(sql_script)
print(«Скрипт SQLite успешно выполнен»)
cursor. close()except sqlite3. Error as error:
print(«Ошибка при подключении к sqlite», error)
finally:
if (sqlite_connection):
sqlite_connection. close()
print(«Соединение с SQLite закрыто»)

Создание таблиц, добавление данных и настройка ~ PythonRu

Таблицы SQLite создаются за счет выполнения скрипта из Python. Вывод:

База данных подключена к SQLite
Скрипт SQLite успешно выполнен
Соединение с SQLite закрыто

Примечание: после соединения с SQLite все содержимое файла сохраняется в переменной. Затем используется команда cursor. executescript(script) для выполнения всех инструкций за раз.

Исключения базы данных SQLite

  • sqlite3.Warning. Подкласс Exception. Его можно игнорировать, если нужно, чтобы оно не останавливало выполнение.
  • sqlite3.Error. Базовый класс для остальных исключений модуля sqlite3. Подкласс Exception.
  • sqlite3.DatabaseError. Исключение, которое возвращается при ошибках базы данных. Например, если попытаться открыть файл как базу sqite3, хотя он ею не является, то вернется ошибка «sqlite3.DatabaseError: file is encrypted or is not a database».
  • sqlite3.IntegrityError. Подкласс DatabaseError. Эта ошибка возвращается, когда затрагиваются отношения в базе, например, например, не проходит проверка внешнего ключа.
  • sqlite3.ProgrammingError. Подкласс DatabaseError. Эта ошибка возникает из-за ошибок программиста: создание таблицы с именем, которое уже занято, синтаксическая ошибка в SQL-запросах.
  • sqlite3.OperationalError. Подкласс DatabaseError. Эту ошибку невозможно контролировать. Она появляется в ситуациях, которые касаются работы базы данных, например, обрыв соединения, неработающий сервер, проблемы с источником данных и так далее.
  • sqlite3.NotSupportedError. Это исключение появляется при попытке использовать неподдерживаемое базой данных API. Пример: вызов метода rollback() для соединения, которое не поддерживает транзакции. Вызов коммита после команды создания таблицы.

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

Например, попробуем добавить данные в таблицу, которой не существует и выведем весь стек исключений из Python.

import sqlite3
import traceback
import systry:
sqlite_connection = sqlite3. connect(‘sqlite_python. db’)
cursor = sqlite_connection. cursor()
print(«База данных подключена к SQLite»)sqlite_insert_query = «»»INSERT INTO unknown_table_1
(id, text) VALUES (1, ‘Демо текст’)»»»count = cursor. execute(sqlite_insert_query)
sqlite_connection. commit()
print(«Запись успешно вставлена ​​в таблицу sqlitedb_developers «, cursor. rowcount)
cursor. close()except sqlite3. Error as error:
print(«Не удалось вставить данные в таблицу sqlite»)
print(«Класс исключения: «, error. __class__)
print(«Исключение», error. args)
print(«Печать подробноcтей исключения SQLite: «)
exc_type, exc_value, exc_tb = sys. exc_info()
print(traceback. format_exception(exc_type, exc_value, exc_tb))
finally:
if (sqlite_connection):
sqlite_connection. close()
print(«Соединение с SQLite закрыто»)

Изменения timeout при подключении из Python

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

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

По умолчанию значение этого параметра равно 5. 0 (5 секунд). Его не нужно задавать, потому что это значение по умолчанию. Таким образом при подключении к базе данных из Python, если ответ не будет получен в течение 5 секунд, вернется исключение. Однако параметр все-таки можно задать в функции sqlite3. connect.

Посмотрим, как это сделать из Python.

import sqlite3def read_sqlite_table():
try:
sqlite_connection= sqlite3. connect(‘sqlite_python. db’, timeout=20)
cursor = sqlite_connection. cursor()
print(«Подключен к SQLite»)sqlite_select_query = «»»SELECT count(*) from sqlitedb_developers»»»
cursor. execute(sqlite_select_query)
total_rows = cursor. fetchone()
print(«Всего строк: «, total_rows)
cursor. close()except sqlite3. Error as error:
print(«Ошибка при подключении к sqlite», error)
finally:
if (sqlite_connection):
sqlite_connection. close()
print(«Соединение с SQLite закрыто»)

Подключен к SQLite
Всего строк: (0,)
Соединение с SQLite закрыто

Получение изменений с момента подключения к базе данных

Для статистики может потребоваться найти количество строк базы данных, которые были вставлены, удалены или изменены с момента открытия соединения. Для этого используется функция connection. total_changes модуля sqlite3.

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

import sqlite3try:
sqlite_connection = sqlite3. connect(‘sqlite_python. db’)
cursor = sqlite_connection. cursor()
print(«Подключен к SQLite»)sql_update_query = «»»Update sqlitedb_developers set salary = 10000 where id = 4″»»
cursor. execute(sql_update_query)sql_delete_query = «»»DELETE from sqlitedb_developers where id = 4″»»
cursor. execute(sql_delete_query)sqlite_connection. commit()
cursor. close()except sqlite3. Error as error:
print(«Ошибка при работе с SQLite», error)
finally:
if (sqlite_connection):
print(«Всего строк, измененных после подключения к базе данных: «, sqlite_connection. total_changes)
sqlite_connection. close()
print(«Соединение с SQLite закрыто»)

Подключен к SQLite
Всего строк, измененных после подключения к базе данных: 3
Соединение с SQLite закрыто

Сохранение резервной копии базы данных из Python

Модуль sqlite3 в Python предоставляет функцию для сохранения резервной копии базы данных SQLite. С помощью метода connection. backup() можно сделать резервную копию базы SQLite.

connection. backup(target, *, pages=0, progress=None, name=»main», sleep=0. 250)

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

По умолчанию когда параметр pages равен 0 или отрицательному числу, вся база данных копируется в один шаг. В противном случае метод выполняет цикл, копируя заданное количество страниц за раз.

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

Рассмотрим один пример копирования базы данных в другую.

import sqlite3try:
sqlite_con = sqlite3. connect(‘sqlite_python. db’)
backup_con = sqlite3. connect(‘sqlite_backup. db’)
with backup_con:
sqlite_con. backup(backup_con, pages=3, progress=progress)
print(«Резервное копирование выполнено успешно»)
except sqlite3. Error as error:
print(«Ошибка при резервном копировании: «, error)
finally:
if(backup_con):
backup_con. close()
sqlite_con. close()

Скопировано 3 из 5. Скопировано 5 из 5. Резервное копирование выполнено успешно

  • После подключения к SQLite обе базы данных были открыты с помощью двух разных подключений
  • Дальше выполняется метод connection.backup() с помощью экземпляра первого подключения. Также задано количество страниц, которые нужно скопировать за одну итерацию.

🧾 Плоские базы данных

Под плоской базой данных (плоской таблицей) будем понимать файл, содержащий данные без внутренней иерархии и ссылок на внешние файлы. Это объясняет ряд особенностей: в таких базах данных нет необходимости использовать фиксированную ширину полей, а csv-файлы (англ. comma separated values) представляют собой строки простого текста, в которых элементы данных разделены запятыми. Каждая строка текста представляет собой строку данных, а каждое значение в строке, отделенное от остальных запятой соответствует одному из полей таблицы.

first_name,last_name,title,publisher
Isaac,Asimov,Foundation,Random House
Pearl,Buck,The Good Earth,Random House
Pearl,Buck,The Good Earth,Simon & Schuster
Tom,Clancy,The Hunt For Red October,Berkley
Tom,Clancy,Patriot Games,Simon & Schuster
Stephen,King,It,Random House
Stephen,King,It,Penguin Random House
Stephen,King,Dead Zone,Random House
Stephen,King,The Shining,Penguin Random House
John,Le Carre,»Tinker, Tailor, Solider, Spy: A George Smiley Novel»,Berkley
Alex,Michaelides,The Silent Patient,Simon & Schuster
Carol,Shaben,Into The Abyss,Simon & Schuster

В первой строке представленного примера находится список полей – имена столбцов данных. Каждая следующая строка соответствует одной записи.

➕ Преимущества плоских баз данных

Небольшие плоские базы данных легко создавать и корректировать с помощью текстового редактора, не составляет трудностей найти несоответствие или иную проблему. Многие приложения умеют их импортировать и экспортировать. В Excel можно превратить csv-файл в электронную таблицу и обратно.

Ещё одно преимущество плоских файлов – они автономны, данными в такой форме легко поделиться. Практически в любом языке программирования есть инструменты и библиотеки для работы с csv-файлами. Python имеет встроенный модуль csv и мощную стороннюю библиотеку pandas.

➖ Недостатки плоских баз данных

Преимущества плоских баз данных меркнут по мере увеличения объема информации. Файлы остаются читаемыми, но редактирование и поиск отдельных записей вызывают трудности. И не только для человека – файл разрастается, и его обработка требует вычислительных ресурсов.

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

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

📇 Пример работы с плоской базой данных

В качестве примера рассмотрим описанный выше файл author_book_publisher. csv, содержащий список авторов, опубликованных ими книг и издателей. Для простоты будем считать, что все авторы, книги и издательства уникальны, и все книги пишутся автором без соавторов.

Обратите внимание на особенность набора данных

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

Рассмотрим функцию main() программы, находящейся в упомянутом репозитории по относительному адресу examples/example_1/main.

При запуске программы для обновленного csv-файла выводится следующий результат:

$ python main. py
Publisher: Simon & Schuster, total books: 4
Publisher: Random House, total books: 4
Publisher: Penguin Random House, total books: 2
Publisher: Berkley, total books: 2

Publisher: Simon & Schuster, total authors: 4
Publisher: Random House, total authors: 3
Publisher: Berkley, total authors: 2
Publisher: Penguin Random House, total authors: 1

Authors
├── Alex Michaelides
│ └── The Silent Patient
│ └── Simon & Schuster
├── Carol Shaben
│ └── Into The Abyss
│ └── Simon & Schuster
├── Isaac Asimov
│ └── Foundation
│ └── Random House
├── John Le Carre
│ └── Tinker, Tailor, Solider, Spy: A George Smiley Novel
│ └── Berkley
├── Pearl Buck
│ └── The Good Earth
│ ├── Random House
│ └── Simon & Schuster
├── Stephen King
│ ├── Dead Zone
│ │ └── Random House
│ ├── It
│ │ ├── Penguin Random House
│ │ └── Random House
│ └── The Shining
│ └── Penguin Random House
└── Tom Clancy
├── Patriot Games
│ └── Simon & Schuster
└── The Hunt For Red October
└── Berkley

Для выполнения основной части работы main() вызывает другие функции, с которыми можно ознакомиться в файле. Само приложение работает корректно и демонстрирует возможности библиотеки pandas.

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

Как мы упомянули выше в примечании, в файле author_book_publisher. csv в некоторых данных есть повторы. Например, роман Перл Бак The Good Earth опубликовали два разных издателя.

Представьте, что было бы, если файл содержал больше связанных данных, например, сведения об авторе, дату публикации, ISBN книги, адрес и телефонный номер издательства. Подобные сведения будут дублироваться для каждого корневого элемента: автора, книги, издательства. Такое построение не только избыточно, но и усложняет процедуры 1) изменения полей, связанных с отдельным объектом и 2) добавления новых свойств.

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

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

🏗️ Создаем структуру базы данных

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

SQL – это декларативный язык, используемый для создания, управления и поиска данных, содержащихся в базе. Декларативный язык описывает, что должно быть выполнено, а не то, как это нужно сделать. Мы увидим примеры операторов SQL позже, когда перейдем к созданию таблиц базы данных.

Если вы уже знакомы с SQL и для вас больший интерес представляет работа с SQLAlchemy, вы можете пропустить следующие разделы, дающие общее представление о работе с SQL.

Чтобы воспользоваться преимуществами SQL, нам нужно провести нормализацию базы данных из файла author_book_publisher. csv. Для этого мы перенесем авторов, книги и издателей в отдельные таблицы базы данных.

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

Каждая запись в таблице имеет первичный ключ (англ. primary key), определенный для присвоения записи уникального идентификатора. Первичный ключ похож на ключ в словаре Python. Движок базы данных обычно сам генерирует целочисленный первичный ключ, увеличивая значение на единицу для каждой новой записи. Если данные, хранящиеся в поле, уникальны среди всех других данных в этом поле, это значение также может использоваться, как первичный ключ. Например, в таблице, содержащей данные о книгах, в качестве первичного ключа может использоваться уникальный по своей природе ISBN.

🏢 Создание таблиц базы данных

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

CREATE TABLE author (
author_id INTEGER NOT NULL PRIMARY KEY,
first_name VARCHAR,
last_name VARCHAR
);

CREATE TABLE book (
book_id INTEGER NOT NULL PRIMARY KEY,
author_id INTEGER REFERENCES author,
title VARCHAR
);

CREATE TABLE publisher (
publisher_id INTEGER NOT NULL PRIMARY KEY,
name VARCHAR
);

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

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

Представим, что далее мы сделали SQL-запросы, чтобы заполнить таблицы базы данных информацией. Следующий оператор использует знак *, чтобы получить и вывести все данные в таблице авторов:

SELECT * FROM author;

Вы можете использовать инструмент командной строки sqlite3 для взаимодействия с файлом базы данных author_book_publisher. db:

Ниже показан результат работы указанной команды SQL и ее вывод, за которой следует команда. q для выхода из программы:

Обратите внимание, что в отличие от csv-файла каждый автор присутствует в таблице только один раз.

🕹️ Манипуляции данными с помощью SQL

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

INSERT INTO author
(first_name, last_name)
VALUES (‘Paul’, ‘Mendez’);

Оператор INSERT вставляет строковые значения Paul и Mendez в соответствующие столбцы first_name и last_name таблицы author.

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

Обновление записей в таблице базы данных – также несложный процесс. Например, предположим, что Стивен Кинг хотел, чтобы его знали под псевдонимом Ричард Бахман:

UPDATE author
SET first_name = ‘Richard’, last_name = ‘Bachman’
WHERE first_name = ‘Stephen’ AND last_name = ‘King’;

Оператор SQL находит запись с помощью условного оператора WHERE, а затем обновляет поля first_name и last_name свежими значениями. Знак равенства (=) в SQL используется и для сравнения, и для присваивания.

Пример оператора SQL для удаления записи из таблицы авторов:

DELETE FROM author
WHERE first_name = ‘Paul’
AND last_name = ‘Mendez’;

Будьте осторожны при удалении записей!

Слишком широкое условие может привести к удалению большего количества записей, чем вы предполагали. Например, если бы условие было основано только на строке first_name = ‘Paul’, то были бы удалены все авторы с именем Paul.

🤝 Строим отношения

Данные в файле author_book_publisher. csv отображают данные и связи путем дублирования данных. База данных SQLite разбивает данные на три таблицы (author, book, and publisher) и устанавливает между ними отношения.

Связь «один ко многим» похоже на связь покупателя с заказываемыми товарами в Интернете. У одного покупателя может быть много заказов, но каждый заказ принадлежит одному покупателю. В базе данных author_book_publisher. db связь «один ко многим» представлена отношением авторов и книг. Каждый автор может написать много книг, но каждая художественная книга написана одним автором (в рамках рассматриваемого примера).

Как реализовать связь «один ко многим» между двумя таблицами? Каждая таблица в базе данных имеет поле первичного ключа, обычно названное по шаблону <имя таблицы>_id.

Кроме того, таблица book содержит поле author_id, которое ссылается на таблицу author (см. выше SQL-запрос для создания таблиц). Таким образом, поле author_id устанавливает связь «один ко многим» между авторами и книгами, которая выглядит следующим образом.

Создание таблиц, добавление данных и настройка ~ PythonRu

Связь между таблицами author и book

Приведенная выше диаграмма представляет собой простую диаграмму отношений сущностей (ERD), созданную с помощью приложения JetBrains DataGrip. Два графических элемента добавляют информацию о связях:

  • Значки ключей обозначают первичный (желтый цвет) и внешний (голубой цвет) ключи.
  • Стрелка указывает на связь между таблицами на основе внешнего ключа author_id.

Чтобы вывести две таблицы вместе, используем SQL-оператор JOIN:

Приведенный SQL-запрос собирает информацию из author и book, используя установленную между ними связь. Конкатенация строк SQL позволяет присвоить author_name полное имя автора. Данные, собранные запросом, сортируются в порядке возрастания по полю last_name.

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

«многие ко многим»

Автор может работать со многими издателями, а издатель – со многими авторами. Книга может быть опубликована в нескольких издательствах, а издатель может опубликовать множество разных книг. То есть в базе данных author_book_publisher. db отношение «многие ко многим» существует между авторами и издателями, а также между издателями и книгами.

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

CREATE TABLE author_publisher (
author_id INTEGER REFERENCES author,
publisher_id INTEGER REFERENCES publisher
);

В этом примере создается новая таблица author_publisher, которая ссылается на первичные ключи уже существующих таблиц author и publisher. То есть таблица author_publisher устанавливает отношения между автором и издателем. Аналогично создается таблица book_publisher.

Создание таблиц, добавление данных и настройка ~ PythonRu

Отношения между всеми необходимыми таблицами

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

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

  • Соединяя таблицы author и author_publisher
  • Соединяя таблицы author_publisher и publisher

Пример SQL-запроса, возвращающего список авторов и издателей, публикующих их книги:

Приведем пример еще одного запроса, отражающего некоторые возможности SQL:

Этот запрос возвращает список авторов и количество написанных ими книг. Список сортируется сначала по количеству книг в порядке убывания, а затем по имени автора в алфавитном порядке.

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

🧰 Работа с SQLAlchemy и объектами Python

SQLAlchemy – это мощный набор Python-инструментов для доступа к базам данных. Когда мы программируем на объектно-ориентированном языке, полезно мыслить в категориях объектов. Но между объектно-ориентированной и реляционной моделями существует концептуальный разрыв. Этот зазор покрывает объектно-реляционное отображение (ORM), предоставляемое SQLAlchemy. Между базой данных и программой Python появляется модель, преобразующая информацию из потока базы данных в объекты Python и обратно. Таким образом, SQLAlchemy позволяет мыслить в терминах объектов, при этом сохраняя мощные механизмы базы данных.

Модель. Для подключения SQLAlchemy к базе данных создается модель – класс Python, унаследованный от класса SQLAlchemy Base , определяющий отображение данных между объектами Python, возвращаемыми в результате запроса, и самими таблицами базы данных.

Представленный ниже файл models. py создает модели для представления базы данных author_book_publisher. db:

# импортируем классы, используемые для определения атрибутов модели
from sqlalchemy import Column, Integer, String, ForeignKey, Table

# импортируем объекты для создания отношения между объектами
from sqlalchemy. orm import relationship, backref

# объект для подключения ядро базы данных
from sqlalchemy. ext. declarative import declarative_base

# создаем класс, от которого будут наследоваться модели
Base = declarative_base()

# создаем модель таблицы связей таблиц author и publisher
author_publisher = Table(
«author_publisher»,
Base. metadata,
Column(«author_id», Integer, ForeignKey(«author. author_id»)),
Column(«publisher_id», Integer, ForeignKey(«publisher. publisher_id»)),
)

# создаем модель таблицы связей таблиц book и publisher
book_publisher = Table(
«book_publisher»,
Base. metadata,
Column(«book_id», Integer, ForeignKey(«book. book_id»)),
Column(«publisher_id», Integer, ForeignKey(«publisher. publisher_id»)),
)

# определяем модельи классов для автора, книги и издательства
class Author(Base):
__tablename__ = «author»
author_id = Column(Integer, primary_key=True)
first_name = Column(String)
last_name = Column(String)
books = relationship(«Book», backref=backref(«author»))
publishers = relationship(
«Publisher», secondary=author_publisher, back_populates=»authors»
)

class Book(Base):
__tablename__ = «book»
book_id = Column(Integer, primary_key=True)
author_id = Column(Integer, ForeignKey(«author. author_id»))
title = Column(String)
publishers = relationship(
«Publisher», secondary=book_publisher, back_populates=»books»
)

class Publisher(Base):
__tablename__ = «publisher»
publisher_id = Column(Integer, primary_key=True)
name = Column(String)
authors = relationship(
«Author», secondary=author_publisher, back_populates=»publishers»
)
books = relationship(
«Book», secondary=book_publisher, back_populates=»publishers»
)

В приведенных в коде комментариях модели сопоставлены пяти показанным выше таблицам базы данных author_book_publisher.

Класс Table служит для создания связующей таблицы, то есть для создания отношений многие-ко-многим. Первый параметр – имя таблицы, определенное в базе данных, второй (Base. metadata) обеспечивает связь между функциональностью SQLAlchemy и механизмами базы данных. Остальные параметры являются экземплярами класса Column, определяющего поле таблицы по имени, типу, а также соответствие внешнему ключу.

Класс ForeignKey определяет взаимосвязь между двумя полями Column в разных таблицах. Например, следующая строка в определении таблицы author_publisher сообщает SQLAlchemy, что в таблице author_publisher есть поле с именем author_id, тип этого поля – Integer, и он является внешним ключом, связанным с первичным ключом в таблице author.

Функция relationship определяет отношение один-ко-многим.

Первый параметр функции relationship – имя класса Book (не путайте с именем таблицы book) – это класс, к которому относится атрибут books. Это отношение сообщает SQLAlchemy, что существует связь между классами Author и Book. SQLAlchemy найдет связь в определении класса Book:

SQLAlchemy распознает, что это точка соединения ForeignKey между двумя классами.

Параметр backref создает атрибут author для каждого экземпляра Book. Этот атрибут ссылается на Author, с которым связан экземпляр Book.

Например, если выполнить следующий код Python, то в результате запроса будет возвращен экземпляр Book, имеющий атрибуты, которые можно использовать для вывода информации о книге:

Наличие атрибута author в book связано с определением backref. Обратная ссылка очень удобна, когда нам нужно обратиться к родительскому объекту, а все, что у нас есть, – это дочерний экземпляр.

Другая связь у Author с классом Publisher:

publishers = relationship(
«Publisher», secondary=author_publisher, back_populates=»authors»
)

# для сравнения
books = relationship(«Book», backref=backref(«author»))

Как и книги, атрибут publishers обозначает совокупность издателей, связанных с автором. Первый параметр, «Publisher», сообщает SQLAlchemy, что это за класс. Второй параметр secondary сообщает SQLAlchemy, что связь с классом Publisher осуществляется через «вторичную» таблицу – таблицу author_publisher. Третий параметр back_populate сообщает SQLAlchemy о наличии дополнительной коллекции в классе Publisher, называемой authors.

💬 Запросы к базе данных

Стандартный запрос наподобие SELECT * FROM author в SQLAlchemy можно сделать вот так:

results = session. query(Author). all()

Здесь session – объект SQLAlchemy, используемый для связи с SQLite в программах Python. Здесь мы сообщаем, что хотим выполнить запрос к модели Author и вернуть все записи.

На этом этапе преимущества использования SQLAlchemy вместо простого SQL могут быть неочевидны, особенно с учетом необходимости создания моделей. Однако оказывается, что вместо списка скалярных данных, теперь мы получаем список экземпляров объектов Author с атрибутами, соответствующими заданным именам столбцов.

Коллекции книг и издателей, поддерживаемая SQLAlchemy, создает иерархический список авторов и написанных ими книг, а также издателей, которые эти книги опубликовали. За кулисами SQLAlchemy превращает вызовы объектов и методов в операторы SQL для выполнения в системе управления базами данных SQLite. И наоборот, SQLAlchemy преобразует данные, возвращаемые запросами SQL, в объекты Python.

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

author_book_totals = (
session. query(
Author. first_name,
Author. last_name,
func. count(Book. title). label(«book_total»)
). join(Book). group_by(Author. last_name). order_by(desc(«book_total»)). all()
)

Запрос session. query возвращает имя и фамилию автора, а также количество книг, написанных автором. Агрегирующий счетчик в инструкции group_by производит подсчет по фамилии автора. Результаты сортируются в порядке убывания на основе расчетной переменной с псевдонимом book_total.

👨‍💻 Пример программы

Пример программы examples/example_2/main. py содержит те же функции, что и первый программный пример, но использует SQLAlchemy исключительно для взаимодействия с базой данных SQLite author_book_publisher. Приведем здесь функцию main():

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

👥 Предоставление доступа к данным нескольким пользователям

К этому моменту мы узнали, как для доступа к одним и тем же данным могут использоваться pandas, SQLite и SQLAlchemy. Одним из решающих факторов при выборе между использованием плоского файла или базы данных является объем данных и количество связей между различными структурами данных. Еще один фактор, который следует учитывать, – с каким количеством пользователей мы делимся данными и вопрос критичности их рассинхронизации.

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

Последний пример – законченное веб-приложение и пользовательский интерфейс для образца существенно более крупной учебной базы данных SQLite Chinook, содержащей информацию о музыкальных исполнителях, альбомах, треках, жанрах и т. (11 связанных таблиц).

🌐 Использование Flask с SQLite и SQLAlchemy

Программа examples/example_3/chinook_server. py создает приложение Flask, с которым можно взаимодействовать с помощью браузера. В приложении используются следующие технологии:

  • Flask Blueprint – часть Flask для делегирования задач отдельным модулям с заранее определенной функциональностью;
  • Flask SQLAlchemy – расширение Flask, добавляющее в веб-приложения поддержку SQLAlchemy;
  • Flask_Bootstrap4 упаковывает набор интерфейсных инструментов Bootstrap, интегрируя его с веб-приложениями Flask;
  • Flask_WTF расширяет Flask с помощью WTForms, предоставляя вашим веб-приложениям удобный способ создания и проверки веб-форм;
  • python_dotenv – модуль Python, используемый приложением для чтения переменных среды из файла и сохранения конфиденциальной информации за пределами программного кода.

Пример приложения довольно большой, и лишь некоторая его часть имеет отношение к руководству. По этой причине изучение кода оставлено в качестве упражнения для читателя. Тем не менее вы можете посмотреть представленный ниже скринкаст, за которой следует HTML-код шаблона домашней страницы и Python-скрипт, который динамически предоставляет данные.

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

HTML-шаблон Jinja2, который создает домашнюю страницу приложения:

Файл Python, ответственный за отображение страницы:

Создание REST API сервера

Использование переменных в запросе INSERT

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

Для этого есть запрос с параметрами. Он позволяет использовать переменные Python на месте заполнителей (?) в запросе. Пример:

import sqlite3def insert_varible_into_table(dev_id, name, email, join_date, salary):
try:
sqlite_connection = sqlite3. connect(‘sqlite_python. db’)
cursor = sqlite_connection. cursor()
print(«Подключен к SQLite»)sqlite_insert_with_param = «»»INSERT INTO sqlitedb_developers
(id, name, email, joining_date, salary)
VALUES (?, ?, ?, ?, ?);»»»data_tuple = (dev_id, name, email, join_date, salary)
cursor. execute(sqlite_insert_with_param, data_tuple)
sqlite_connection. commit()
print(«Переменные Python успешно вставлены в таблицу sqlitedb_developers»)except sqlite3. Error as error:
print(«Ошибка при работе с SQLite», error)
finally:
if sqlite_connection:
sqlite_connection. close()
print(«Соединение с SQLite закрыто»)

Вывод: таблица sqlitedb_developers после вставки переменной Python в качестве значения колонки.

Подключен к SQLite
Переменные Python успешно вставлены в таблицу sqlitedb_developers
Соединение с SQLite закрыто
Подключен к SQLite
Переменные Python успешно вставлены в таблицу sqlitedb_developers
Соединение с SQLite закрыто

Проверить результат можно, получив данные из таблицы.

Создание таблиц, добавление данных и настройка ~ PythonRu

Вставка нескольких строк с помощью executemany()

В предыдущем примере для вставки одной записи в таблицу использовался метод execute() объекта cursor, но иногда требуется вставить несколько строчек.

Например, при чтении файла, например, CSV, может потребоваться добавить все записи из него в таблицу SQLite. Вместе выполнения запроса INSERT для каждой записи, можно выполнить все операции в один запрос. Добавить несколько записей в таблицу SQLite можно с помощью метода executemany() объекта cursor.

Этот метод принимает два аргумента: запрос SQL и список записей.

import sqlite3def insert_multiple_records(records):
try:
sqlite_connection = sqlite3. connect(‘sqlite_python. db’)
cursor = sqlite_connection. cursor()
print(«Подключен к SQLite»)sqlite_insert_query = «»»INSERT INTO sqlitedb_developers
(id, name, email, joining_date, salary)
VALUES (?, ?, ?, ?, ?);»»»cursor. executemany(sqlite_insert_query, records)
sqlite_connection. commit()
print(«Записи успешно вставлены в таблицу sqlitedb_developers», cursor. rowcount)
sqlite_connection. commit()
cursor. close()except sqlite3. Error as error:
print(«Ошибка при работе с SQLite», error)
finally:
if sqlite_connection:
sqlite_connection. close()
print(«Соединение с SQLite закрыто»)

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

Подключен к SQLite
Записи успешно вставлены в таблицу sqlitedb_developers 3
Соединение с SQLite закрыто

Создание таблиц, добавление данных и настройка ~ PythonRu

Разберем последний пример:

  • После подключения к базе данных подготавливается список записей для вставки в таблицу. Каждая из них — это всего лишь строка.
  • Инструкция SQLite INSERT содержит запрос с параметрами, где на месте каждого значения стоит вопросительный знак.
  • Дальше с помощью cursor.executemany(sqlite_insert_query, recordList) в таблицу вставляются несколько записей.
  • Чтобы узнать количество вставленных строк используется метод cursor.rowcount. Наконец, нужно не забыть сохранить изменения в базе.

Заключение

Разумно задаться вопросом: является ли SQLite правильным выбором в качестве серверной части базы данных для веб-приложения. На веб-сайте SQLite указано, что SQLite – хороший выбор для сайтов, обслуживающих около 100 тыс. обращений в день. Если на вашем сайт посещений больше, в первую очередь вас стоит поздравить 🎉

Если вы реализовали веб-сайт с помощью SQLAlchemy, то можно перенести данные из SQLite в другую базу данных, такую ​​как MySQL или PostgreSQL. Описательные модели данных SQLAlchemy останутся прежними.