Как использовать базу данных SQLite с Python [шаг за шагом]

В этом уроке по Python мы узнаем, как подключиться к базе данных SQLite и как выполнять операции CRUD (Create Read Update Delete) с помощью Python.

Для взаимодействия с базой данных SQLite в Python вам нужно подключиться к базе данных, создать курсор и использовать функцию execute() на курсоре. Это позволяет вам выполнять все необходимые операции CRUD. Не забудьте выполнить commit() для ваших изменений и close() для подключения к базе данных, как только вы закончите с ней.

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

Давайте научимся использовать SQLite с Python!

Как подключиться к базе данных SQLite3 с помощью Python

Первым шагом для подключения к базе данных SQLite в Python является импорт модуля sqlite3, который является частью Python начиная с версии 2.5, поэтому вам не нужно устанавливать его, если вы используете Python 3 (а вам следует это сделать).

Этот модуль предоставляет интерфейс для взаимодействия с базами данных SQLite, соответствующий спецификации Database API 2.0.

import sqlite3

Чтобы прочитать данные из базы данных SQLite, вам необходимо подключиться к ней с помощью функции connect() модуля sqlite3. Вам необходимо передать имя файла базы данных SQLite в функцию connect().

Имя файла базы данных, которую мы будем использовать в этом примере, — flight_booking.db.

До сих пор мы не создали файл базы данных. Что же произойдет, если мы выполним следующий оператор Python?

conn = sqlite3.connect('flight_booking.db')

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

В оболочке ниже вы можете увидеть, что был создан пустой файл базы данных SQLite с именем flight_booking.db.

(python-env) # ls -ltr flight_booking.db 
-rw-r--r--  1 codefathertech  codefathertech  0 Mar 18 09:52 flight_booking.db

Что дальше?

Как создать таблицу в SQLite с помощью Python

База данных, которую мы создали, пуста, поэтому мы создадим в ней таблицу.

Для выполнения операторов SQL с помощью SQLite и получения результатов операторов с помощью Python вам необходимо создать объект курсора. Вы можете создать курсор, как только у вас будет объект соединения, используя cursor().

cursor = conn.cursor()

Используя функцию Python dir(), давайте посмотрим, какие методы есть у объекта курсора.

print(dir(cursor))

[output]
['__class__', '__delattr__', '__dir__', '__doc__', '__eq__', '__format__', '__ge__', '__getattribute__', '__gt__', '__hash__', '__init__', '__init_subclass__', '__iter__', '__le__', '__lt__', '__ne__', '__new__', '__next__', '__reduce__', '__reduce_ex__', '__repr__', '__setattr__', '__sizeof__', '__str__', '__subclasshook__', 'arraysize', 'close', 'connection', 'description', 'execute', 'executemany', 'executescript', 'fetchall', 'fetchmany', 'fetchone', 'lastrowid', 'row_factory', 'rowcount', 'setinputsizes', 'setoutputsize']

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

Давайте выполним первую команду SQL для нашей базы данных SQLite.

Передайте SQL-оператор «CREATE TABLE» в cursor.execute(). Предположим, мы создаем приложение Python для бронирования рейсов и нам нужна таблица с именем flights.

cursor.execute('''
    CREATE TABLE flights(
        flight_number TEXT PRIMARY KEY NOT NULL,
        origin TEXT NOT NULL,
        destination TEXT NOT NULL
   )
''')

Как видите, мы устанавливаем flight_number в качестве первичного ключа таблицы.

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

conn.commit()

После выполнения оператора CREATE TABLE и фиксации мы хотим убедиться, что таблица существует в базе данных.

Для изучения структуры базы данных SQLite вы можете использовать клиент sqlite3. Команда для доступа к базе данных — «sqlite3 <database_filename>». Для просмотра таблиц в базе данных используйте команду «.tables».

(python-env) # sqlite3 flight_booking.db
SQLite version 3.32.3 2020-06-18 14:16:19
Enter ".help" for usage hints.
sqlite>.tables
flights
sqlite>.schema flights
CREATE TABLE flights(
        flight_number TEXT PRIMARY KEY NOT NULL,
        origin TEXT NOT NULL,
        destination TEXT NOT NULL
   );

Таблица существует!

Давайте попробуем снова выполнить код Python, который мы написали до сих пор. При выполнении кода вы увидите следующую ошибку:

Traceback (most recent call last):
  File "sqlite_tutorial.py", line 5, in <module>
    cursor.execute('''
sqlite3.OperationalError: table flights already exists

Ошибка «table already exists» возникает из-за того, что мы пытаемся воссоздать таблицу, которая уже присутствует в базе данных.

Чтобы избежать этой ошибки, вы можете обновить оператор SQL «CREATE TABLE», как показано ниже.

cursor.execute('''
    CREATE TABLE IF NOT EXISTS flights(
        flight_number TEXT PRIMARY KEY NOT NULL,
        origin TEXT NOT NULL,
        destination TEXT NOT NULL
   )
''')

После добавления «IF NOT EXISTS» в оператор SQL «CREATE TABLE» выполните код Python еще раз и убедитесь, что ошибка, которую мы видели ранее, больше не возникает.

Как вставить одну запись в базу данных SQLite с помощью Python

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

Чтобы вставить данные в таблицу SQLite, вы используете cursor.execute() и передаете ему SQL-оператор «INSERT INTO». Вы можете вставить одну или несколько записей в таблицу.

Ниже вы можете увидеть, как вставить отдельную запись в созданную нами таблицу.

cursor.execute("INSERT INTO flights (flight_number, origin, destination) VALUES (?,?,?)", ("AZ12345", "Rome", "Paris"))
conn.commit()

Используйте sqlite3, чтобы убедиться, что мы видим запись в таблице.

sqlite>.header ON
sqlite> select * from flights;
flight_number|origin|destination
AZ12345|Rome|Paris

Мы можем видеть новую запись. Мы используем «.header ON», чтобы также отображать имена столбцов как часть запроса SELECT SQL.

Если вы попытаетесь выполнить оператор «INSERT INTO» выше, вы получите следующую ошибку.

Traceback (most recent call last):
  File "sqlite_tutorial.py", line 14, in <module>
    cursor.execute("INSERT INTO flights (flight_number, origin, destination) VALUES (?,?,?)", ("AZ12345", "Rome", "Paris"))
sqlite3.IntegrityError: UNIQUE constraint failed: flights.flight_number

Почему возникает эта ошибка?

Эта ошибка вызвана тем, что мы установили столбец flight_number как первичный ключ, и, следовательно, он должен быть уникальным. Вот почему SQLite не позволяет нам вставить другую запись с тем же flight_number.

Это хороший способ предотвратить ошибки, которые в противном случае могли бы быть вызваны наличием нескольких записей с одинаковым номером рейса.

Как вставить несколько записей в базу данных SQLite с помощью Python

Если вы только начинаете создавать приложение Python, вам может потребоваться заполнить базу данных достаточным объемом данных, чтобы обеспечить хороший уровень тестирования при разработке кода.

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

Чтобы вставить несколько записей в таблицу SQLite с помощью Python, можно определить список кортежей, где каждый кортеж — это данные для одной записи базы данных. Затем используйте функцию cursor.executemany() и передайте ей список кортежей.

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

data = [
    ("BD36457", "New York", "Los Angeles"),
    ("SZ37486", "Milan", "London"),
    ("BT34567", "Oslo", "Copenhagen"),
    ("RS25690", "Madrid", "Turin")
]

cursor.executemany("INSERT INTO flights (flight_number, origin, destination) VALUES (?,?,?)", data)
conn.commit()

После вставки данных вы можете увидеть данные, хранящиеся в SQLite. Новые строки видны в таблице с помощью клиента sqlite3.

sqlite> select * from flights;
AZ12345|Rome|Paris
BD36457|New York|Los Angeles
SZ37486|Milan|London
BT34567|Oslo|Copenhagen
RS25690|Madrid|Turin

Выглядит хорошо, мы вставили несколько строк в нашу таблицу базы данных SQLite.

Как создать запрос для чтения данных из SQLite в Python

Теперь, когда мы вставили данные в базу данных SQLite с помощью Python, следующим шагом будет изучение того, как получить доступ к данным.

Как извлечь данные из базы данных SQLite и преобразовать их в структуры данных, которые можно использовать в программе на Python?

Чтобы прочитать строки базы данных из таблицы SQLite с помощью Python, можно использовать функцию cursor.execute() для выполнения оператора SQL «SELECT FROM». Затем используйте функцию cursor.fetchall(), чтобы получить данные в виде списка.

cursor.execute("SELECT * FROM flights")
flights = cursor.fetchall()

После выполнения SQL-выражения «SELECT FROM» мы используем cursor.fetchall(), чтобы получить данные обратно в виде списка. С помощью выражения print() мы можем подтвердить , что полученный тип данных является списком.

print(type(flights))

[output]
<class 'list'>

А теперь давайте посмотрим на содержимое этого списка Python.

print("Flights: ", flights)

[output]
Flights:  [('AZ12345', 'Rome', 'Paris'), ('BD36457', 'New York', 'Los Angeles'), ('SZ37486', 'Milan', 'London'), ('BT34567', 'Oslo', 'Copenhagen'), ('RS25690', 'Madrid', 'Turin')]

Как видите, мы получили список кортежей.

Отлично, теперь мы можем использовать его так, как хотим, в нашем приложении Python.

А теперь давайте применим условие к нашему оператору SELECT.

cursor.execute("SELECT * FROM flights WHERE origin =?", ("Rome",))
flights = cursor.fetchall()
print("Flights from Rome: ", flights)

Вот данные, которые мы получаем из SELECT.

Flights:  [('AZ12345', 'Rome', 'Paris')]

Это правильно!

Как обновить строку в таблице SQLite с помощью Python

Продолжим изучение библиотеки SQLite.

Мы создали данные в таблице нашей базы данных, теперь давайте посмотрим, как обновить эти данные.

Предположим, что данные о рейсе из Нью-Йорка неверны и предполагается, что пунктом отправления рейса является Бостон.

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

cursor.execute("SELECT * FROM flights WHERE origin =?", ("New York",))
flights = cursor.fetchall()
print("Flights from New York: ", flights)

[output]
Flights from New York:  [('BD36457', 'New York', 'Los Angeles')]

Теперь выполните оператор UPDATE SQL, чтобы изменить пункт отправления этого рейса из Нью-Йорка в Бостон.

cursor.execute("UPDATE flights SET origin =? WHERE flight_number =?", ("Boston", "BD36457"))
conn.commit()

Затем подтвердите обновление записи, выполнив выборку рейсов из Нью-Йорка и Бостона.

cursor.execute("SELECT * FROM flights WHERE origin =?", ("New York",))
flights_from_new_york = cursor.fetchall()

cursor.execute("SELECT * FROM flights WHERE origin =?", ("Boston",))
flights_from_boston = cursor.fetchall()

print("Flights from New York: ", flights_from_new_york)
print("Flights from New York: ", flights_from_boston)

Вывод:

Flights from New York:  []
Flights from New York:  [('BD36457', 'Boston', 'Los Angeles')]

Это подтверждает, что мы правильно обновили запись в базе данных.

Как удалить запись SQLite из таблицы в Python

А что, если одна из записей, вставленных нами в таблицу, неверна?

Как удалить эту запись SQLite?

Аналогично тому, как мы делали раньше, мы можем передать SQL-оператор DELETE в cursor.execute().

cursor.execute("DELETE FROM flights WHERE flight_number =?", ("BT34567",))
conn.commit()

После удаления записи, указанной выше на основе flight_number, мы можем использовать оператор SELECT, чтобы убедиться, что запись больше не присутствует в таблице.

cursor.execute("SELECT * FROM flights")
flights = cursor.fetchall()
print("Flights: ", flights)

[output]
Flights:  [('AZ12345', 'Rome', 'Paris'), ('BD36457', 'Boston', 'Los Angeles'), ('SZ37486', 'Milan', 'London'), ('RS25690', 'Madrid', 'Turin')]

Запись базы данных успешно удалена.

Как закрыть соединение SQLite с помощью Python?

Чтобы закрыть соединение с базой данных SQLite, вы можете вызвать следующий оператор Python, который использует функцию соединения close(). Функция close() не вызывается автоматически, вам нужно помнить, что ее нужно вызывать вручную.

conn.close()

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

На этом мы завершаем наше руководство по модулю Python sqlite3.

Завершение этого урока по Python SQLite

После изучения этого руководства по Python sqlite вы научитесь работать с базой данных SQLite и выполнять операции CRUD для:

  • Вставьте запись в таблицу (create).
  • Извлечь записи из таблицы (read).
  • Обновить запись в таблице (update).
  • Удалить запись из таблицы (delete).

Теперь пришло время использовать эти знания для создания приложения Python, использующего внутреннее хранилище данных.

Автор

Фото аватара

Владимир Михайлов

Программист на Python с большим количеством опыта и разнообразных проектов.