Как работает модуль sqlite3 в Python 3

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

В этом руководстве мы поговорим о модуле sqlite3 в Python 3. Чтобы потренироваться, мы создадим соединение с базой данных SQLite, добавим в эту БД таблицу, вставим данные, а также извлечем и отредактируем их.

Предположим, у нас есть аквариум и мы хотим создать БД, чтобы хранить в ней данные о рыбах, которые в нем живут.

Требования

Чтобы получить максимальную пользу от этого руководства, вы должны иметь некоторое представление о работе с Python и некоторый базовый опыт работы с SQL.

Для получения необходимой справочной информации вы можете просмотреть эти ресурсы:

1: Создание подключения к базе данных SQLite

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

Подключиться к базе данных SQLite можно с помощью модуля sqlite3:

import sqlite3
connection = sqlite3.connect("aquarium.db")

Строка import sqlite3 дает программе Python доступ к модулю sqlite3. Функция sqlite3.connect () возвращает объект Connection, который мы будем использовать для взаимодействия с базой данных SQLite, хранящейся в файле aquarium.db. Файл aquarium.db создается функцией sqlite3.connect() автоматически (если такой файл еще не существует на компьютере).

Чтобы убедиться, что объект connection создан успешно, запустите:

print(connection.total_changes)

Если мы запустим этот код Python, мы увидим такой результат:

0

connection.total_changes в команде выше – это общее количество строк базы данных, которые были изменены объектом connection. Поскольку мы еще не выполнили ни одной команды SQL, мы ожидаем получить 0.

Если в какой-то момент вы захотите начать это руководство сначала, вы можете удалить файл aquarium.db с компьютера.

Примечание: Также можно подключиться к базе данных SQLite, которая находится строго в памяти (а не в файле), передав функции sqlite3.connect()специальную строку “:memory:”. Например:

sqlite3.connect(“:memory:”)

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

2: Добавление данных в БД SQLite

После того как мы подключились к базе данных aquarium.db, мы можем попробовать вставить в нее данные и извлечь их.

В базах SQL данные хранятся в таблицах. Таблицы определяют набор столбцов и содержат 0 или более строк с данными для каждого столбца.

Давайте создадим таблицу fish, в которой будут такие данные:

name species tank_number
Willy shark 1
Jamie cuttlefish 7

Итак, таблица fish будет содержать имя (столбец name), вид (species) и номер резервуара (tank_number) для каждой рыбы в аквариуме. Согласно записям, пока что в аквариуме есть два жителя: акула Вилли и каракатица по имени Джейми.

Создать эту таблицу рыб в SQLite можно при помощи соединения, которое мы установили в разделе 1:

cursor = connection.cursor()
cursor.execute("CREATE TABLE fish (name TEXT, species TEXT, tank_number INTEGER)")

connection.cursor() возвращает объект Cursor. Такие объекты позволяют отправлять SQL-операторы в базу данных SQLite с помощью cursor.execute(). Строка “CREATE TABLE fish …” – это SQL-оператор, который создает таблицу fish с тремя описанными столбцами: name (с типом данных TEXT), species (также с типом TEXT) и tank_number (с типом INTEGER).

Теперь, когда мы создали таблицу, мы можем вставить в нее строки данных:

cursor.execute("INSERT INTO fish VALUES ('Willy', 'shark', 1)")
cursor.execute("INSERT INTO fish VALUES ('Jamie', 'cuttlefish', 7)")

Мы вызываем cursor.execute() дважды: один раз, чтобы вставить строку об акуле Вилли из резервуара 1, и еще раз, чтобы вставить строку о каракатице Джейми из резервуара 7. “INSERT INTO fish VALUES …” – это SQL-оператор, который позволяет добавлять строки в таблицу.

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

3: Извлечение данных из БД SQLite

В разделе 2 мы добавили в таблицу по имени fish две строки. Извлечь эти строки можно с помощью оператора SELECT:

rows = cursor.execute("SELECT name, species, tank_number FROM fish").fetchall()
print(rows)

Если запустить этот код, мы получим такой результат:

[('Willy', 'shark', 1), ('Jamie', 'cuttlefish', 7)]

Функция cursor.execute() запускает оператор SELECT для получения значений столбцов name, species и tank_number в таблице fish. Функция fetchall() извлекает все результаты оператора SELECT. Когда мы с помощью print(rows) выводим строки на экран, мы видим список из двух кортежей. Каждый кортеж состоит из трех записей – по одной записи для каждого столбца из таблицы. Оба кортежа содержат данные, которые мы вставили в таблицу в предыдущем разделе.

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

target_fish_name = "Jamie"
rows = cursor.execute(
"SELECT name, species, tank_number FROM fish WHERE name = ?",
(target_fish_name,),
).fetchall()
print(rows)
Это даст следующий результат:
[('Jamie', 'cuttlefish', 7)]

Как и в предыдущем примере, cursor.execute(<SQL statement>).fetchall() позволяет извлечь все результаты оператора SELECT. Оператор WHERE в SELECT фильтрует данные и выбирает строки, в которых значение name равно искомому target_fish_name. Обратите внимание: мы используем символ «?», чтобы заменить значение переменной target_fish_name в операторе SELECT. Мы ожидаем, что заданному фильтру будет соответствовать только одна строка, и в результате действительно видим только строку Jamie, cuttlefish, 7.

Важно! Никогда не используйте строковые операции Python, чтобы динамически создать строку оператора SQL. Использование строковых операций Python для сборки операторов SQL открывает все двери атакам на основе SQL-инъекций. Подобные атаки могут использоваться для кражи или изменения данных, хранящихся в вашей БД. Для динамической замены всегда используйте заполнитель «?» в SQL-операторах. Передайте функции Cursor.execute() кортеж значений в качестве второго аргумента, чтобы привязать ваши значения к оператору. Такой подход используется в этом руководстве.

4: Изменение данных в БД SQLite

Строки в базе данных SQLite можно изменять с помощью SQL-операторов UPDATE и DELETE.

Предположим, что акула Вилли переехала в резервуар №2. Следовательно, нам нужно изменить строку в таблице, чтобы отразить это изменение:

new_tank_number = 2
moved_fish_name = "Willy"
cursor.execute(
"UPDATE fish SET tank_number = ? WHERE name = ?",
(new_tank_number, moved_fish_name)
)

SQL-оператор UPDATE изменит значение tank_number для записи Willy на 2. Оператор WHERE внутри UPDATE отфильтрует все остальные записи: значение tank_number изменится только в том случае, если name = “Willy”.

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

rows = cursor.execute("SELECT name, species, tank_number FROM fish").fetchall()
print(rows)

Мы увидим следующий результат:

[('Willy', 'shark', 2), ('Jamie', 'cuttlefish', 7)]

Обратите внимание, в строке для Willy столбец tank_number теперь имеет значение 2.

Допустим, акула Вилли была выпущена в дикую природу и больше не живет в нашем аквариуме. Значит, имеет смысл удалить эту строку из таблицы.

Выполните SQL-оператор DELETE, чтобы удалить строку:

released_fish_name = "Willy"
cursor.execute(
"DELETE FROM fish WHERE name = ?",
(released_fish_name,)
)

SQL-оператор DELETE удалит строку, а оператор WHERE поможет ему найти необходимую: строка будет удалена только в том случае, если name = “Willy”.

Следующий оператор SELECT подтвердит, что удаление выполнено правильно:

rows = cursor.execute("SELECT name, species, tank_number FROM fish").fetchall()
print(rows)

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

[('Jamie', 'cuttlefish', 7)]

Обратите внимание, строки про акулу Вилли больше нет в таблице, остается только каракатица Джейми.

5: Оператор with

В течение работы с этим руководством мы использовали два основных объекта для взаимодействия с базой данных aquarium.db: объект Connection по имени connection и объект Cursor по имени cursor.

Когда файлы Python больше не используются, их нужно закрывать – точно так же следует закрыть и объекты Connection и Cursor, когда они больше не нужны.

Автоматически закрывать объекты Connection и Cursor можно при помощи оператора with.

from contextlib import closing
with closing(sqlite3.connect("aquarium.db")) as connection:
with closing(connection.cursor()) as cursor:
rows = cursor.execute("SELECT 1").fetchall()
print(rows)

closing – это удобная функция, предоставляемая модулем contextlib. Когда оператор with завершает работу, closing вызывает функцию close()для любого переданного ему объекта. В этом примере closing используется дважды: один раз для объекта Connection, второй раз – для объекта Cursor.

Этот код даст нам следующий результат:

[(1,)]

Поскольку оператор SELECT 1 всегда возвращает одну строку с одним столбцом со значением 1, в результате мы получаем кортеж с единственным значением 1.

Заключение

Модуль sqlite3 – мощная часть стандартной библиотеки Python; он позволяет работать с полнофункциональной базой данных SQL, не устанавливая никакого дополнительного программного обеспечения.

В этом руководстве вы узнали, как использовать sqlite3 для подключения к базе данных SQLite, добавлять данные в эту БД, а также читать и изменять эти данные. Попутно мы также кратко поговорили об опасности SQL-инъекций и о том, как использовать contextlib.closing, чтобы автоматически закрыть объекты Python.

Читайте также: Краткий обзор реляционных систем управления базами данных

Tags: , , ,

Добавить комментарий