Основные операции с данными в SQLite

Рассмотрим основные операции с базой данных SQLite с помощью библиотеки sqlite3 на примере таблицы:

CREATE TABLE people (
    id INTEGER PRIMARY KEY AUTOINCREMENT,  
    name TEXT, 
    age INTEGER
)

Добавление данных

Для добавления данных применяется SQL-инструкция INSERT. Для добавления одной строки используем метод execute() объекта Cursor:

import sqlite3
 
connection = sqlite3.connect("monogenes.db")
cursor = connection.cursor()
 
# добавляем строку в таблицу people
cursor.execute("INSERT INTO people (name, age) VALUES ('Tom', 38)")
connection.commit()  # выполняем транзакцию

Здесь добавляется одна строка, где name = "Tom", а age = 38.

Выражение INSERT неявно открывает транзакцию, для завершения которой необходимо вызвать метод commit() текущего объекта Connection.

Установка параметров

С помощью второго параметра в метод execute() можно передать значения для параметров SQL-запроса:

import sqlite3
 
connection = sqlite3.connect("monogenes.db")
cursor = connection.cursor()
 
# данные для добавления
bob = ("Bob", 42)
cursor.execute("INSERT INTO people (name, age) VALUES (?, ?)", bob)

connection.commit()

В данном случае добавляемые в БД значения представляют кортеж bob. В SQL-запросе вместо конкретных значений используются знаки подстановки ?. Вместо этих символов при выполнении запроса будут вставляться данные из кортежа data. Так, первый элемент кортежа - строка "Bob" передается на место первого знака ?, второй элемент - число 42 передается на место второго знака ?.

И если мы посмотрим на содержимое базы данных, то найдем там все добавленные объекты:

Множественная вставка

Метод executemany() позволяет вставить набор строк:

import sqlite3
 
connection = sqlite3.connect("monogenes.db")
cursor = connection.cursor()
 
# данные для добавления
people = [("Sam", 28), ("Alice", 33), ("Kate", 25)]
cursor.executemany("INSERT INTO people (name, age) VALUES (?, ?)", people)
 
connection.commit()

В метод cursor.executemany() по сути передается то же самое выражение SQL, только теперь данные определены в виде списка кортежей people. Фактически каждый кортеж в этом списке представляет отдельную строку - данные отдельного пользователя, и при выполнении метода для каждого кортежа будет создаваться свое выражение INSERT INTO.

Добавление множества строк в базу данных SQLite с помощью метода executemany в Python

Получение данных

Для получения данных применяется SQL-команда SELECT. После выполнения этой команды курсор получает данные, которые можно получить с помощью одного из методов: fetchall() (возвращает список со всеми строками), fetchmany() (возвращает указанное количество строк) и fetchone() (возвращает одну в наборе строку).

Получение всех строк

Например, получим все ранее добавленные данные из таблицы people:

import sqlite3
 
connection = sqlite3.connect("monogenes.db")
cursor = connection.cursor()
 
# получаем все данные из таблицы people
cursor.execute("SELECT * FROM people")
print(cursor.fetchall())

При выполнении этой программы на консоль будет выведен список строк, где каждая строка представляет кортеж:

[(1, 'Tom', 38), (2, 'Bob', 42), (3, 'Sam', 28), (4, 'Alice', 33), (5, 'Kate', 25)]

При необходимости мы можем перебрать список, используя стандартные циклические конструкции:

import sqlite3
 
connection = sqlite3.connect("monogenes.db")
cursor = connection.cursor()
 
cursor.execute("SELECT * FROM people")
for person in cursor.fetchall():
    print(f"{person[1]} - {person[2]}")

Результат работы программы:

Tom - 38
Bob - 42
Sam - 28
Alice - 33
Kate - 25

Стоит отметить, что в примере выше необязательно вызывать метод fetchall, можем перебрать курсор в цикле как обычный набор:

for person in cursor:
    print(f"{person[1]} - {person[2]}")

Получение части строк

Получение части строк с помощью метода fetchmany(), в который передается количество строк:

import sqlite3
 
connection = sqlite3.connect("monogenes.db")
cursor = connection.cursor()
 
cursor.execute("SELECT * FROM people")
# извлекаем первые 3 строки в полученном наборе
print(cursor.fetchmany(3))

Результат работы программы:

[(1, 'Tom', 38), (2, 'Bob', 42), (3, 'Sam', 28)]

Выполнение этого метода извлекает следующие ранее не извлеченные строки:

# извлекаем первые 3 строки в полученном наборе
print(cursor.fetchmany(3))  # [(1, 'Tom', 38), (2, 'Bob', 42), (3, 'Sam', 28)]
# извлекаем следующие 3 строки в полученном наборе
print(cursor.fetchmany(3))  # [(4, 'Alice', 33), (5, 'Kate', 25)]

Получение одной строки

Метод fetchone() извлекает следующую строку в виде кортежа значений и возвращает его. Если строк больше нет, то возвращает None:

import sqlite3
 
connection = sqlite3.connect("monogenes.db")
cursor = connection.cursor()
 
cursor.execute("SELECT * FROM people")
# извлекаем одну строку
print(cursor.fetchone())    # (1, 'Tom', 38)

Данный метод удобно применять, когда нам надо извлечь из базы данных только один объект:

import sqlite3
 
connection = sqlite3.connect("monogenes.db")
cursor = connection.cursor()
 
cursor.execute("SELECT name, age FROM people WHERE id=2")
# раскладываем кортеж на две переменных
name, age = cursor.fetchone()
print(f"Name: {name}\tAge: {age}")    # Name: Bob   Age: 42

Здесь получаем из бд строку с id=2, и полученный результат раскладываем на две переменных name и age (так как кортеж в Python можно разложить на отдельные значения)

Обновление

Для обновления в SQL выполняется команда UPDATE. Например, заменим у всех пользователей имя с Tom на Tomas:

import sqlite3
 
connection = sqlite3.connect("monogenes.db")
cursor = connection.cursor()
 
# обновляем строки, где name = Tom
cursor.execute("UPDATE people SET name ='Tomas' WHERE name='Tom'")
# вариант с подстановками
# cursor.execute("UPDATE people SET name =? WHERE name=?", ("Tomas", "Tom"))
connection.commit()
 
# проверяем 
cursor.execute("SELECT * FROM people")
print(cursor.fetchall())    # [(1, 'Tomas', 38), (2, 'Bob', 42), (3, 'Sam', 28), (4, 'Alice', 33), (5, 'Kate', 25)]

Для выполнения обновления также надо выполнять метод connection.commit()

Удаление данных

Для удаления в SQL выполняется команда DЕLETE. Например, удалим всех пользователей с именем Bob:

import sqlite3
 
connection = sqlite3.connect("monogenes.db")
cursor = connection.cursor()
 
# обновляем строки, где name = Tom
cursor.execute("DELETE FROM people WHERE name=?", ("Bob",))
connection.commit()
 
# проверяем 
cursor.execute("SELECT * FROM people")
print(cursor.fetchall())    # [(1, 'Tomas', 38), (3, 'Sam', 28), (4, 'Alice', 33), (5, 'Kate', 25)]

Для выполнения удаления также надо выполнять метод connection.commit()