Python может работать со всеми распространенными СУБД (системами управления базами данных):
- Реляционными (SQL)
- Нереляционными (NoSQL)
- Объектно-ориентированными
- Объектно-реляционными
- Резидентными
- Столбцовыми
- Ключ-значение
В этой статье мы будем изучать приемы работы с реляционной СУБД SQLite, которая поставляется с Python. Еще мы рассмотрим базы типа «ключ-значение», которые отлично подходят для хранения данных в простых приложениях.
Что такое SQL, СУБД, SQLite и ORM
SQL (Structured Query Language) – это специальный язык запросов, который используется для создания, изменения и выборки данных в реляционных базах данных, управляемых определенной СУБД.
Система управления базами данных (СУБД) – это программное обеспечение, которое позволяет сохранять, организовывать, изменять, получать и анализировать данные. Обычно СУБД выполняют следующие функции:
- Создание и управление структурой данных (таблицы, индексы, ограничения и т.д.)
- Хранение данных на диске или в памяти.
- Поиск и выборка нужных данных с помощью запросов.
- Манипулирование данными (добавление, изменение, удаление).
- Обеспечение сохранности и целостности данных (транзакции, резервное копирование, восстановление).
- Обеспечение доступности и безопасности данных (права доступа, шифрование и т.д.)
Реляционные СУБД используют язык SQL для выполнения запросов к данным и управления ими. Данные в реляционных базах хранятся в виде таблиц: каждая строка представляет собой отдельную запись, а каждый столбец – отдельное поле данных.
Надо заметить, что язык SQL – не единственный способ создавать запросы к базе данных: при создании веб-приложений на базе фреймворков Django и Flask разработчики обычно используют ORM.
ORM (объектно-реляционное отображение) – это своеобразная прослойка, которая позволяет программистам работать с данными в реляционных базах данных как с объектами Python. ORM использует язык SQL под капотом, но разработчикам не нужно его знать – таблицы в базах данных создаются автоматически, а запросы имеют очень простой и понятный синтаксис. Самые популярные прослойки – Django ORM и SQLAlchemy (для Flask).
SQLite – компактная, быстрая, универсальная СУБД. Хранит данные в локальном файле, не требует отдельного сервера для выполнения запросов или управления данными: вместо этого она использует библиотеку, которая работает внутри приложения. SQLite можно использовать для мобильных, настольных и веб-приложений.
Создание базы данных в Python
Новую SQLite базу можно создать за несколько простых шагов.
1. Импортировать DB-API 2.0 интерфейс (библиотеку sqlite3):
import sqlite3
2. Создать подключение к базе данных SQLite:
conn = sqlite3.connect('example.db')
3. Создать объект курсора:
cursor = conn.cursor()
4. Создать таблицу в базе данных:
cursor.execute('''CREATE TABLE users
(id INTEGER PRIMARY KEY, name TEXT, age INTEGER)''')
5. Закрыть соединение с базой данных:
conn.close()
Полностью код выглядит так:
import sqlite3
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
cursor.execute('''CREATE TABLE users
(id INTEGER PRIMARY KEY, name TEXT, age INTEGER)''')
conn.close()
Этот код создает базу данных с названием example. База включает в себя таблицу users, которая содержит поля id, name и age:
- INTEGER означает, что в поле хранятся целочисленные значения.
- PRIMARY KEY указывает на то, что это поле будет использоваться в качестве первичного ключа таблицы. Первичный ключ – это уникальный идентификатор, который используется для создания связи между таблицами в базе данных.
- TEXT – класс данных, который используется для хранения строковых значений. Например, имя пользователя, почтовый адрес или наименование товара могут храниться в поле типа TEXT.
Помимо TEXT и INTEGER SQLite имеет следующие классы для хранения данных:
- NULL – представляет нулевое значение.
- REAL – используется для хранения чисел с плавающей точкой.
- BLOB – применяется для хранения бинарных объектов (изображений, аудио или видео). На практике мультимедийные файлы очень редко хранят в БД – целесообразнее хранить там только ссылки на объекты.
В SQLite не предусмотрены отдельные типы данных для хранения даты и времени, но можно использовать тип данных TEXT для хранения даты и времени в виде строки в формате ISO-8601.
Некоторые другие типы данных при необходимости можно преобразовать в классы данных SQLite. К примеру, для хранения BOOLEAN значений можно использовать INTEGER, присваивая записи значения 0 или 1.
Добавление столбцов в таблицу
В предыдущем примере мы создали таблицу users, в которой хранятся имена и возраст пользователей. Добавим два новых поля – для хранения фамилии и названия факультета:
import sqlite3
# Открываем соединение с базой данных example.db
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
# Выполняем запрос на добавление новых столбцов в таблицу users
cursor.execute('''ALTER TABLE users ADD COLUMN surname TEXT''')
cursor.execute('''ALTER TABLE users ADD COLUMN faculty TEXT''')
# Сохраняем изменения и закрываем соединение с базой
conn.commit()
conn.close()
Как узнать, что находится в базе данных
Есть два способа посмотреть, что записано в БД:
- Выполнить специальный запрос к системной таблице sqlite_master.
- Воспользоваться визуальным браузером/редактором.
Начнем с первого способа – напишем и выполним запрос, чтобы узнать, какие поля (столбцы) есть в таблице:
import sqlite3
# создаем соединение с нашей базой данных
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
# получаем метаданные для таблицы
cursor.execute("PRAGMA table_info(users)")
# выводим названия полей таблицы
fields = cursor.fetchall()
for field in fields:
print(field[1])
# закрываем соединение с базой данных
conn.close()
Результат:
id
name
age
surname
faculty
Второй способ проще и удобнее – можно сразу увидеть и структуру, и содержимое БД. Нам понадобится любой визуальный редактор, поддерживающий SQLite. Самый минималистичный вариант – sqlite-gui:
Более продвинутый браузер/редактор – DB Browser:
Из многофункциональных инструментов для работы с SQLite отлично подходит базовая версия Dbeaver.
Добавление записей в БД
Внесем в базу первую запись – информацию о пользователе по имени Инна Егорова, 20 лет, с факультета прикладной математики:
import sqlite3
# устанавливаем соединение с базой данных
conn = sqlite3.connect('example.db')
# создаем курсор для выполнения операций с базой данных
cursor = conn.cursor()
# задаем значения для новой записи
name = 'Инна'
surname = 'Егорова'
age = 20
faculty = 'Прикладная математика'
# добавляем новую запись в таблицу users
cursor.execute('INSERT INTO users (name, surname, age, faculty) VALUES (?, ?, ?, ?)', (name, surname, age, faculty))
# сохраняем изменения в базе данных
conn.commit()
# закрываем соединение с базой данных
conn.close()
Результат:
Редактирование записей
Изменим возраст для пользователя с именем Инна и фамилией Егорова:
import sqlite3
# создаем соединение с нашей базой данных
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
# обновляем возраст пользователя
cursor.execute("UPDATE users SET age = ? WHERE name = ? AND surname = ?", (19, 'Инна', 'Егорова'))
conn.commit()
# закрываем соединение с базой данных
conn.close()
Результат:
Удаление записей
Напишем запрос на удаление из БД всех записей, которые содержат «Прикладная математика» в поле faculty:
import sqlite3
conn = sqlite3.connect('example.db')
cursor = conn.cursor()
# удаляем записи, содержащие "прикладная математика" в поле faculty
cursor.execute("DELETE FROM users WHERE faculty LIKE '%Прикладная математика%'")
conn.commit()
# закрываем соединение с базой данных
conn.close()
Поскольку в БД была всего одна запись, и она соответствовала критерию, после выполнения запроса база опустела:
Отлично! Вы освоили все базовые операции для работы с одной таблицей в SQLite.
Вы умеете создавать базы данных, таблицы, а также добавлять, редактировать и удалять в них записи. Это крепкий фундамент для работы с данными.
Но настоящая сила баз данных — в умении работать со связанными данными из нескольких таблиц и решать на их основе сложные задачи. В полной версии урока вы:
- Научитесь объединять данные из нескольких таблиц с помощью оператора JOIN — ключевой навык для работы с реляционными базами.
- Познакомитесь с простыми альтернативами SQLite, такими как dbm и shelve.
- Решите 10 практических задач по анализу данных: от создания БД из CSV-файла и сложных выборок до удаления дубликатов.
Остались вопросы? Задайте их в комментариях, постараемся помочь!