Python sqlite3 изменение данных

Пишем запросы через модуль sqlite в Python 3

Для работы с базой SQLite в Python уже есть модуль с аналогичным названием. Модуль относится к базовым и устанавливается вместе с интерпретатором. Вам может понадобится установка через pip, если вы используете какую-то другую сборку Python. Рассмотрим модуль на примерах подключения к базе и создания запросов.

Особенности SQLite

У самой базы SQLite есть несколько особенностей, которые отличают ее от остальных баз. В первую очередь — эта база данных не нуждается в установке, а работает через модули языков. У нее так же нет клиент-серверной архитектуры. Приложение и база, обычно, располагаются рядом на одном хосте. Из-за этого эта база популярна у мобильных приложений, которые не нуждаются в подключении к интернету, но это не единственное место где ее используют.

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

Хоть SQLite не является клиент-серверной базой мы все равное должны подключаться к ней. Это можно сделать так:

import sqlite3 connection = sqlite3.connect('database.db')

Если этот файл не будет найден по указанному пути, то создастся новый. Если у вас указан полный путь до файла, а папки не существует — произойдет ошибка. Вы можете использовать следующую проверку что бы убедится, что файл существует и данные не будут записаны в новый файл:

import sqlite3 import os database = 'database.db' def check_db(filename): return os.path.exists(filename) if check_db(database): connection = sqlite3.connect(database)

Еще один способ создать базу — поместить ее в оперативную память. Учитывайте, что в этом случае она временная и будет жить до перезагрузки компьютера или смерти процесса:

import sqlite3 connection = sqlite3.connect(':memory:')

Так как база — это все тот же файл и в SQL существует понятие транзакций, которые должны корректно завершаться, мы должны закрывать каждое подключение к базе. Это можно сделать через метод ‘.close()’ или использовать менеджер контекста:

database = 'database.db' connection = sqlite3.connect(database) connection.close() # или from contextlib import closing with closing(sqlite3.connect(database)) as connection: pass

Использовать библиотеку contextlib и метод closing — совет со stackoverflow, где объясняется что иначе соединение не будет закрыто.

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

Курсор и execute

Для выполнения SQL запросов в библиотеке есть несколько методов. Обычный способ — это создание курсора, который будет перемещать по полученным значениям:

После его создания мы сможем выполнять запрос:

Или можно использовать метод .scriptexecute(), который учитывает что вы передаете несколько запросов разделенных ‘;’. В этом случае коммиты выполняются автоматически:

cursor.executescript(query1)

Есть еще один способ создания курсора, в этом случае он будет временным:

connection.execute() connection.scriptexecute()

Выполнение запросов

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

name	size	date
pic1.jpg	50KB	2021-12-19
pic2.jpg	60KB	2021-12-20

Создание такой таблицы выполняется в несколько шагов.

Создание таблицы и вставка значений с CREATE и INSERT

Любая таблица состоит из колонок. Эти колонки определяют типы данных. В нашем случае, чистый SQL запрос, который создаст таблицу, будет следующим:

CREATE TABLE images( name text primary key, size text, date date );

Еще один запрос, который вставит эти данные в таблицу:

INSERT INTO images(name, size, date) VALUES ('pic1.jpg', '50KB', '2021-12-19'),('pic2.jpg', '50KB', '2021-12-20')

Что бы эти запросы были выполнены нужно использовать курсор одним из методов, которые были описаны выше. Это может выглядеть так:

import sqlite3 # запрос на создание таблицы query1 = ''' CREATE TABLE images( name text primary key, size text, date date );''' # запрос на вставку данных query2 = ''' INSERT INTO images(name, size, date) VALUES ("pic1.jpg", "50KB", "2021-12-19"),("pic2.jpg", "50KB", "2021-12-20") ''' database = 'database.db' connection = sqlite3.connect(database) # выполняем 1-ый запрос connection.execute(query1) # выполняем 2-ый запрос connection.execute(query2) connection.commit() connection.close()

Узнать сколько строк было изменено через запрос можно следующим образом:

connection.execute(query1).rowcount()

Еще один метод total_changes(), который показывает сколько строк было изменено во всей базе с момента подключения:

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

Обновление данных с UPDATE

В языке SQL, обновить одно из значений в базе, можно с инструкцией UPDATE. Так мы, за счет чистого SQL, изменим название одной из строк созданных раннее:

UPDATE images SET name = 'newpic.jpg' WHERE name = 'pic1.jpg'

С помощью sqlite и python этот запрос выполняется так же как и с INSERT:

import sqlite3 # запрос на изменение строки query1 = ''' UPDATE images SET name = 'newpic.jpg' WHERE name = 'pic1.jpg'; ''' database = 'database.db' connection = sqlite3.connect(database) connection.execute(query1) connection.commit() connection.close()

Если commit не будет выполнен, то результат не будет сохранен в базе.

Чтение данных через SELECT

Чтение данных из базы выполняется через перемещение курсора. Этот процесс выполняется через методы fetchone или fetchall, которые возвращают кортеж:

import sqlite3 from contextlib import closing database = 'database.db' with closing(sqlite3.connect(database)) as connection: cursor = connection.cursor() cursor.execute(""" select * from images """) # получаем все значения print(cursor.fetchall())

fetchone() будет возвращать по одной строке после каждого вызова:

import sqlite3 from contextlib import closing database = 'database.db' with closing(sqlite3.connect(database)) as connection: cursor = connection.cursor() cursor.execute(""" select * from images """) print('1: ', cursor.fetchone()) print('2: ', cursor.fetchone()) print('3: ', cursor.fetchone())

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

SQL запрос, который удалит строку используя колонку ‘name’, выглядит так:

DELETE FROM images WHERE name='newpic.jpg'

Через код Python это будет выглядеть так:

import sqlite3 # запрос на изменение строки query1 = ''' DELETE FROM images WHERE name='newpic.jpg'; ''' database = 'database.db' connection = sqlite3.connect(database) connection.execute(query1) connection.commit() connection.close()

Параметризация запроса

У вас может быть необходимость передать в SQL дополнительные параметры. Вы можете использовать форматирования строк Python, но это будет ошибкой:

value = 'pic2.jpg' cursor.execute(f'select * from images where name = ""')

Проблема этого способа в том, что пользователь может передать вместо ‘value’ другой SQL запрос. Такой действие называется SQL инъекцией. Каждый передаваемый параметр в SQL нуждается в дополнительных проверках и они уже реализованы в методе execute:

values = ('pic2.jpg', 'pic1.jpg') cursor.execute('select * from images where name = ? or name = ?', values)

То есть вместо знаков ‘?’ у нас будут подставляться значения из кортежа.

Мы можем создать несколько запросов используя .executemany(). В примере ниже будет выполнено 2 разных ‘INSERT’ для каждого из кортежей (с точки зрения SQL такой подход считается плохой практикой):

values = [('pic4.jpg', '52KB', '2021-12-19'), ('pic5.jpg', '54KB', '2021-12-19')] cursor.executemany('INSERT INTO images(name, size, date) VALUES (?, ?, ?)', values)

Получение таблицы в виде словаря

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

import sqlite3 from contextlib import closing database = 'database.db' with closing(sqlite3.connect(database)) as connection: connection.row_factory = sqlite3.Row cursor = connection.cursor() cursor.execute(""" select * from images """) row = cursor.fetchone() print('Объект "Row": ', row) print('Словарь: ', dict(row))

Вам не обязательно использовать преобразование в словарь, что бы получить значение по ключу. Такая возможно уже есть у класса Row:

with closing(sqlite3.connect(database)) as connection: connection.row_factory = sqlite3.Row cursor = connection.cursor() cursor.execute(""" select * from images """) for i in cursor.fetchall(): # получаем значения по ключу print(i['name']) 

Транзакции

В реляционных базах данные есть принцип ACID. Если не вдаваться в технические подробности, то он объясняет как должны работать транзакции. Сама транзакция — это один или несколько запросов, которые выполняются вместе. Эти запросы могут быть успешно выполнены или все вместе отменены. Простой пример — это 2 таблицы, в которые вы вставляете данные. Если в 1 таблице произойдет ошибка, то и операция во 2-ой таблице должна отмениться.

Условия для успешного выполнения транзакций могут быть заданы самим разработчиком. Подтверждаются транзакции через метод commit(), а отменяются через rollback().

Если вы закрываете подключение к базе корректно (используя менеджер контекста with или метод .close()), то все не завершенные запросы выполнят commit. Если у вас произойдет сбой (аварийное выключение компьютера например), то должен будет сработать один из принципов ACID для корректного завершения транзакций, но может произойти и rollback.

В следующем примере мы создадим 2 подключения к базе и в одном из них данные не будут записаны в базу из-за отсутствия commit():

import sqlite3 from contextlib import closing database = 'database.db' # функция для вывода данных из таблицы def show_table(db_con): cursor = db_con.cursor() cursor.execute('select name, size, date from images;') for name, size, date in cursor.fetchall(): print(name, size, date) with closing(sqlite3.connect(database)) as db_con: print('Таблица до добавления данных:') show_table(db_con) cursor1 = db_con.cursor() # создание транзакции cursor1.execute(""" insert into images (name, size, date) values ('pic3.jph', '10KB', '2021-03-10'); """) print('Данные при текущем подключении и без завершения транзакции:') show_table(db_con) print('Данные при новом подключении и без завершения транзакции:') with sqlite3.connect(database) as db_con2: show_table(db_con2) # Выполняем коммит db_con.commit() print('Данные при новом подключении и с завершённой транзакцией:') with sqlite3.connect(database) as db_con3: show_table(db_con3)

rollback работает противоположным образом. Т.е. данные, которые были созданы в первом insert запросе будут удалены из базы вовсе. Транзакция может либо завершиться, либо откатится. Выполнить первое, после второго, как и наоборот, нельзя.

Лучше практикой является явное закрытие каждого подключения и транзакции:

timeout транзакции

Если транзакция не будет закрыта, то таблица будет заблокирована (зависит так же от типа используемой транзакции). Сколько эта транзакция останется в таком виде зависит от параметра ‘timeout’. Он устанавливается при подключении:

sqlite3.connect(database, timeout=5)

По умолчанию это значение равное 5.

Источник