SQLite с Python: основы работы с базой данных

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

SQLite — это встроенная библиотека SQL-движка, совместимая с несколькими языками программирования

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

База данных SQLite сохраняется в виде одного файла на диске, схожего с CSV-файлом, но предлагает множество преимуществ, таких как:

• Написан на C — статически типизированном компилируемом языке, который

значительно быстрее большинства языков, включая Python

• Легковесен, поэтому работает лучше и быстрее, чем чтение из CSV-файла
• Легко настраивается
• Может обрабатывать более сложные запросы
• Полезнее изучать на случай, если вам когда-нибудь придётся работать с SQL

или MySQL в будущем

Установка и настройка SQLite в Python

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

Сначала создайте проект Python с файлом main.py. Я буду использовать библиотеку SQL от CS50, которую можно установить, выполнив команду:

pip3 install cs50

Первый шаг — создать файл database.db в корневом каталоге. Это делается следующей командой в терминале:

touch database.db

После этого в файл main.py необходимо добавить следующий код:

from cs50 import SQL

db = SQL("sqlite:///database.db")

По этой теме полезно отдельно посмотреть EXPLAIN QUERY PLAN: план выполнения SQL-запроса в SQLite, чтобы расширить контекст и сравнить подходы

По этой теме полезно отдельно посмотреть Создание Flutter-приложения с SQLite, BLoC и Streams, чтобы расширить контекст и сравнить подходы

CRUD-операции в SQLite с Python

Создание таблицы в SQLite (CREATE TABLE)

Следующий шаг — создать таблицу в базе данных. SQL хранит данные в таблицах, которые похожи на таблицы в Excel или Google Sheets. Код для этого выглядит следующим образом:

db.execute( "CREATE TABLE IF NOT EXISTS users (name TEXT, age NUMBER, fav_food STRING)"
)

Разберём это подробнее: db — это база данных, в которую записываются данные. Далее выполняется команда. Если таблица users не существует, создаётся таблица с именем users, с именами столбцов name, age и fav_food, с указанием типов данных для каждого значения

Запись данных в SQLite (INSERT)

Для добавления пользователя можно использовать операцию INSERT

db.execute( "INSERT INTO users (name, age, fav_food) VALUES(?, ?, ?)", "eesa", 14, "pizza"
)

Значение "eesa" вставляется в столбец name, значение 14 вставляется в столбец age, а значение "pizza" вставляется в столбец fav_food

Код для добавления ещё одного пользователя (в данном случае Bob) выглядел бы следующим образом:

db.execute( "INSERT INTO users (name, age, fav_food) VALUES(?, ?, ?)", "bob", 20, "burger"
)

Чтение из базы данных

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

people = db.execute("SELECT * FROM users")
print(people)
# [{'name': 'eesa', 'age': 14, 'fav_food': 'pizza'}]

Символ * в операторе SELECT выбирает всё, что находится в базе данных

Чтобы выбрать только определённые значения, можно использовать оператор DISTINCT. Например, если нужна только любимая еда каждого пользователя:

people = db.execute("SELECT DISTINCT fav_food FROM users")
print(people)

Также можно разделять значения запятыми в запросе SELECT DISTINCT:

people = db.execute("SELECT DISTINCT age, fav_food FROM users")
print(people)

Для того чтобы прочитать данные только для Bob, можно воспользоваться условием WHERE:

people = db.execute("SELECT * FROM users WHERE name='bob'")
print(people)

Для более сложных запросов используется синтаксис AND, OR и NOT. Конструкции WHERE можно разделять этими ключевыми словами:

people = db.execute("SELECT * FROM users WHERE name='bob' AND age=20")
print(people)

Это выведет данные для Bob, потому что Bob — 20 лет

Обновление строки в базе данных

Для обновления строки используется оператор UPDATE:

db.execute( "UPDATE users SET fav_food='shawarma' WHERE name='eesa'"
)

Удаление строки из базы данных

Для удаления строки используется синтаксис DELETE:

db.execute("DELETE FROM users WHERE name='bob'")
# goodbye bob

people = db.execute("SELECT * FROM users")
print(people)
# [{'name': 'eesa', 'age': 14, 'fav_food': 'shawarma'}]

Чтобы удалить все строки в таблице, достаточно убрать конструкцию WHERE:

db.execute("DELETE FROM users")

Типичные ошибки при работе с SQLite и Python

Работая с SQLite на практике, я замечал несколько ошибок, которые встречаются особенно часто

При создании таблицы всегда используйте условие IF NOT EXISTS, иначе повторный запуск скрипта приведет к ошибке

Не используйте конкатенацию строк для формирования SQL-запросов. Безопаснее передавать параметры как отдельные аргументы

Не забудьте использовать условие WHERE при выполнении операций UPDATE и DELETE, чтобы избежать изменения всех строк в таблице

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

Часто задаваемые вопросы по SQLite и Python

Нужно ли устанавливать SQLite отдельно? Нет. SQLite встроен в стандартную библиотеку Python через модуль sqlite3. В примерах этого руководства используется библиотека CS50 SQL, которую устанавливают командой pip3 install cs50, — она служит удобной обёрткой поверх SQLite

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

Можно ли использовать SQLite в продакшн-приложении? Да, для приложений с небольшим числом одновременных пользователей SQLite вполне пригоден. Если приложение предполагает высокую конкурентную нагрузку на запись, стоит рассмотреть PostgreSQL или MySQL

Что делает символ ? в запросах INSERT? Это заполнитель для параметра. Библиотека подставляет переданные значения на место ? безопасным образом, защищая от SQL-инъекций

Как посмотреть содержимое файла database.db вне Python? Можно использовать утилиту командной строки sqlite3, встроенную в большинство операционных систем, или графический инструмент DB Browser for SQLite — он позволяет просматривать и редактировать таблицы через удобный интерфейс