SQLite – это автономный, работающий без сервера транзакционный механизм базы данных SQL. Python получил модуль sqlite3 в версии 2.5, что значит что вы можете создавать базу данных SQLite в любой настоящей версии Python, без необходимости скачивания дополнительных инструментов. Mozilla использует базы данных SQLite в своем популярном браузере Firefox для хранения закладок и прочей различной информации. В данной статье мы рассмотрим следующее:
- Как создать базу данных SQLite
- Как вставить данные в таблицу
- Как редактировать данные
- Как удалять данные
- Базовые запросы SQL
Другими словами, вместо того чтобы собирать по кусочкам сам модуль, мы непосредственно ознакомимся с тем, как его использовать.
Если вы хотите визуально проверить свою базу данных, вы можете использовать плагин SQLite Manager в Firefox (рекомендуем погуглить данный вопрос), или, если вы предпочитаете командную строку, вы можете использовать оболочку командной строки SQLite Python.
Как создавать базу данных и вставлять различные данные
Создание базы данных в SQLite – это очень просто, но процесс требует того, чтобы вы немного разбирались в том, что такое SQL. Давайте взглянем на код, который создаст базу данных для хранения музыкальных альбомов:
|
1 2 3 4 5 6 7 8 9 10 |
import sqlite3 conn = sqlite3.connect("mydatabase.db") # или :memory: чтобы сохранить в RAM cursor = conn.cursor() # Создание таблицы cursor.execute("""CREATE TABLE albums (title text, artist text, release_date text, publisher text, media_type text) """) |
Сначала нам нужно импортировать модуль sqlite3 и создать связь с базой данных. Вы можете передать название файла или просто использовать специальную строку “:memory:” для создания базы данных в памяти. В нашем случае, мы создаем его на диске в файле под названием mydatabase.db.
Далее мы создаем объект cursor, который позволяет нам взаимодействовать с базой данных и добавлять записи, помимо всего прочего. Здесь мы используем синтаксис SQL для создания таблицы под названием альбомы с пятью следующими полями: title, artist, release_date, publisher и media_type. SQLite поддерживает только пять типов данных: null, integer, real, text и blob. Давайте напишем этот код и вставим кое-какие данные в нашей новой таблице. Запомните, если вы запускаете команду CREATE TABLE, при этом база данных уже существует, вы получите сообщение об ошибке.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
# Вставляем данные в таблицу cursor.execute("""INSERT INTO albums VALUES ('Glow', 'Andy Hunter', '7/24/2012', 'Xplore Records', 'MP3')""" ) # Сохраняем изменения conn.commit() # Вставляем множество данных в таблицу используя безопасный метод "?" albums = [('Exodus', 'Andy Hunter', '7/9/2002', 'Sparrow Records', 'CD'), ('Until We Have Faces', 'Red', '2/1/2011', 'Essential Records', 'CD'), ('The End is Where We Begin', 'Thousand Foot Krutch', '4/17/2012', 'TFKmusic', 'CD'), ('The Good Life', 'Trip Lee', '4/10/2012', 'Reach Records', 'CD')] cursor.executemany("INSERT INTO albums VALUES (?,?,?,?,?)", albums) conn.commit() |
Здесь мы использовали команду INSERT INTO SQL чтобы вставить запись в нашу базу данных. Обратите внимание на то, что каждый объект находится в одинарных кавычках. Это может усложнить работу, если вам нужно вставить строчки, которые содержат одинарные кавычки. В любом случае, чтобы сохранить запись в базе данных, нам нужно создать её. Следующая часть кода показывает, как добавить несколько записей за раз при помощи метода курсора executemany. Обратите внимание на то, что мы используем знаки вопроса (?), вместо строк замещения (%) чтобы вставить значения. Обратите внимание, что использование строки замещения не безопасно, так как может стать причиной появления атаки инъекций SQL . Использование знака вопроса намного лучше, а использование SQLAlchemy тем более, так как он делаете все необходимое, чтобы уберечь вас от правки встроенных одинарных кавычек на то, что SQLite в состоянии принимать.
Редактирование и удаление записей
Возможность обновлять записи в вашей базе данных это ключ к тому, чтобы ваши данные велись аккуратно, и был полный порядок. Если вы не можете редактировать данные, тогда ваша база станет бесполезной достаточно скоро. Иногда вам, в том числе, нужно будет удалять и строки. Мы рассмотрим оба вопроса в данном разделе. Для начала, давайте немного обновимся.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
import sqlite3 conn = sqlite3.connect("mydatabase.db") cursor = conn.cursor() sql = """ UPDATE albums SET artist = 'John Doe' WHERE artist = 'Andy Hunter' """ cursor.execute(sql) conn.commit() |
Здесь мы использовали команду SQL UPDATE, чтобы обновить таблицу альбомов. Здесь вы можете использовать команду SET, чтобы изменить поле, так что в нашем случае мы изменим имя исполнителя на John Doe в каждой записи, где поле исполнителя указано для Andy Hunter. Весьма просто, не так ли? Обратите внимание на то, что если вы не подтвердите изменения, то они не будут внесены в базу данных. Команда DELETE настолько же проста. Давайте посмотрим.
|
1 2 3 4 5 6 7 8 9 |
import sqlite3 conn = sqlite3.connect("mydatabase.db") cursor = conn.cursor() sql = "DELETE FROM albums WHERE artist = 'John Doe'" cursor.execute(sql) conn.commit() |
Удаление еще проще, чем обновление. У SQL это занимает всего две строчки. В данном случае, все, что нам нужно сделать, это указать SQLite, из какой таблицы удалить (albums), и какую именно запись при помощи пункта WHERE. Таким образом, был выполнен поиск записи, в которой присутствует имя “John Doe” в поле исполнителей, после чего эти данные были удалены.
Основные запросы SQLite
Запросы в SQLite очень похожи на те, которые вы используете в других базах данных, таких как MySQL или Postgres. Мы просто используем обычный синтаксис SQL для выполнения запросов, после чего объект cursor выполняет SQL. Вот несколько примеров:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
import sqlite3 conn = sqlite3.connect("mydatabase.db") #conn.row_factory = sqlite3.Row cursor = conn.cursor() sql = "SELECT * FROM albums WHERE artist=?" cursor.execute(sql, [("Red")]) print(cursor.fetchall()) # or use fetchone() print("Here's a listing of all the records in the table:") for row in cursor.execute("SELECT rowid, * FROM albums ORDER BY artist"): print(row) print("Results from a LIKE query:") sql = "SELECT * FROM albums WHERE title LIKE 'The%'" cursor.execute(sql) print(cursor.fetchall()) |
Первый запрос, который мы выполнили, называется SELECT *, что означает, что мы хотим выбрать все записи, подходящие под переданное имя исполнителя, в нашем случае это “Red”. Далее мы выполняем SQL и используем функцию fetchall() для получения результатов. Вы также можете использовать функцию fetchone() для получения первого результата. Обратите внимание на то, что здесь есть прокомментированный раздел, связанный с таинственным row_factory. Если вы не прокомментируете эту строку, результат вернется, так как объекты Row, подобны словарям Python и дают вам доступ к полям строк точь в точь, как и словарь. В любом случае, вы не можете выполнить назначение пункта, используя объект Row. Второй запрос очень похож на первый, но возвращает каждую запись в базе данных и упорядочивает результаты по имени артиста в порядке возрастания. Это также показывает, как мы можем зациклить результаты выдачи. Последний запрос показывает, как команда LIKE используется при поиске частичных фраз. В нашем случае, мы искали по всей таблице заголовки, которые начинаются с артикля The. Знак процента (%) является подстановочным оператором.
Подведем итоги
Теперь вы знаете, как использовать Python для создания базы данных SQLite. Вы также научились создавать, обновлять, редактировать и удалять записи, а также выполнять запросы по базе данных.
Являюсь администратором нескольких порталов по обучению языков программирования Python, Golang и Kotlin. В составе небольшой команды единомышленников, мы занимаемся популяризацией языков программирования на русскоязычную аудиторию. Большая часть статей была адаптирована нами на русский язык и распространяется бесплатно.
E-mail: vasile.buldumac@ati.utm.md
Образование
Universitatea Tehnică a Moldovei (utm.md)
- 2014 — 2018 Технический Университет Молдовы, ИТ-Инженер. Тема дипломной работы «Автоматизация покупки и продажи криптовалюты используя технический анализ»
- 2018 — 2020 Технический Университет Молдовы, Магистр, Магистерская диссертация «Идентификация человека в киберпространстве по фотографии лица»