Работа с изображениями и файлами в SQLite

В этом материале вы узнаете, как вставлять и получать BLOB-данные из таблицы SQLite с помощью Python и модуля sqlite3.

  • В качестве бинарных данных могут выступать файлы с любым расширением, изображения, видео или другие медиа;
  • BLOB-данные можно считывать из таблицы SQLite.

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

В этом примере будет использоваться таблица new_employee. Ее можно создать с помощью следующего скрипта:

CREATE TABLE new_employee (id INTEGER PRIMARY KEY, name TEXT NOT NULL, photo BLOB NOT NULL, resume BLOB NOT NULL);

Эта таблица содержит две BLOB-колонки:

  • Колонка photo для хранения изображения сотрудника.
  • Колонка resume для хранения файла резюме.

Но стоит также разобраться с тем, что же такое BLOB.

Что такое BLOB

BLOB (large binary object — «большой бинарный объект») — это тип данных, который используется для хранения «тяжелых» файлов, таких как изображения, видео, музыка, документы и так далее. Перед сохранением в базе данных эти файлы нужно конвертировать в бинарные данные — то есть, массив байтов.

Вставка изображений и файлов в таблицу

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

  • Установить SQLite-соединение с базой данных из Python;
  • Создать объект cursor из объекта соединения;
  • Создать INSERT-запрос. На этом этапе нужно знать названия таблицы и колонки, в которую будет выполняться вставка;
  • Создать функцию для конвертации цифровых данных (например, изображений или файлов) в бинарные;
  • Выполнить INSERT-запрос с помощью cursor.execute();
  • После успешного завершения операции закоммитить сохранения в базу данных;
  • Закрыть объект cursor и соединение;
  • Перехватить любые SQL-исключения.

Посмотрим на пример:


import sqlite3

def convert_to_binary_data(filename):
# Преобразование данных в двоичный формат
with open(filename, 'rb') as file:
blob_data = file.read()
return blob_data

def insert_blob(emp_id, name, photo, resume_file):
try:
sqlite_connection = sqlite3.connect('sqlite_python.db')
cursor = sqlite_connection.cursor()
print("Подключен к SQLite")

sqlite_insert_blob_query = """INSERT INTO new_employee
(id, name, photo, resume) VALUES (?, ?, ?, ?)"""

emp_photo = convert_to_binary_data(photo)
resume = convert_to_binary_data(resume_file)
# Преобразование данных в формат кортежа
data_tuple = (emp_id, name, emp_photo, resume)
cursor.execute(sqlite_insert_blob_query, data_tuple)
sqlite_connection.commit()
print("Изображение и файл успешно вставлены как BLOB в таблиу")
cursor.close()

except sqlite3.Error as error:
print("Ошибка при работе с SQLite", error)
finally:
if sqlite_connection:
sqlite_connection.close()
print("Соединение с SQLite закрыто")

insert_blob(1, "Smith", "smith.jpg", "smith_resume.docx")
insert_blob(2, "David", "david.jpg", "david_resume.docx")

Вывод:

Подключен к SQLite
Изображение и файл успешно вставлены как BLOB в таблиу
Соединение с SQLite закрыто
Подключен к SQLite
Изображение и файл успешно вставлены как BLOB в таблиу
Соединение с SQLite закрыто

Вот как выглядит таблица после вставки данных:

Работа с изображениями и файлами в SQLite
  • В примере были вставлены id сотрудника, имя, фото и файл с резюме. Для последних двух были переданы местоположения файлов, так что программа смогла считать их и конвертировать в бинарные данные
  • Как можно явно увидеть, изображение и файл конвертировались в бинарный формат в процессе чтения данных в режиме rb. И только после этого данные были вставлены в колонку BLOB. Также был использован запрос с параметрами для вставки динамических данных в таблицу.

Получение изображения и файла, сохраненных в виде BLOB

Предположим, данные, которые хранятся в виде BLOB в базе данных, нужно получить, записать в файл на диске и открыть в привычном виде. Как это делается?

В этом примере считаем изображение сотрудника и файл с резюме из SQLite-таблицы.

Для этого нужно проделать следующие шаги:

  • Установить SQLite-соединение с базой данных из Python;
  • Создать объект cursor из объекта соединения;
  • Создать SELECT-запрос для получения BLOB-колонок из таблицы;
  • Использовать cursor.fetchall() для получения всех строк и перебора по ним;
  • Создать функцию для конвертации BLOB-данных в нужный формат и записать готовые файлы на диск;
  • Закрыть объект cursor и соединение.

import sqlite3, os

def write_to_file(data, filename):
# Преобразование двоичных данных в нужный формат
with open(filename, 'wb') as file:
file.write(data)
print("Данный из blob сохранены в: ", filename, "\n")

def read_blob_data(emp_id):
try:
sqlite_connection = sqlite3.connect('sqlite_python.db')
cursor = sqlite_connection.cursor()
print("Подключен к SQLite")

sql_fetch_blob_query = """SELECT * from new_employee where id = ?"""
cursor.execute(sql_fetch_blob_query, (emp_id,))
record = cursor.fetchall()
for row in record:
print("Id = ", row[0], "Name = ", row[1])
name = row[1]
photo = row[2]
resume_file = row[3]

print("Сохранение изображения сотрудника и резюме на диске \n")
photo_path = os.path.join("db_data", name + ".jpg")
resume_path = os.path.join("db_data", name + "_resume.txt")
write_to_file(photo, photo_path)
write_to_file(resume_file, resume_path)
cursor.close()

except sqlite3.Error as error:
print("Ошибка при работе с SQLite", error)
finally:
if sqlite_connection:
sqlite_connection.close()
print("Соединение с SQLite закрыто")

read_blob_data(1)
read_blob_data(2)

Вывод:

Подключен к SQLite
Id =  1 Name =  Smith
Сохранение изображения сотрудника и резюме на диске 

Данный из blob сохранены в:  db_data\Smith.jpg 

Данный из blob сохранены в:  db_data\Smith_resume.txt 

Соединение с SQLite закрыто
Подключен к SQLite
Id =  2 Name =  David
Сохранение изображения сотрудника и резюме на диске 

Данный из blob сохранены в:  db_data\David.jpg 

Данный из blob сохранены в:  db_data\David_resume.txt 

Соединение с SQLite закрыто

Изображения и файлы действительно сохранились на диске.

Получение изображения и файла, сохраненных в виде BLOB

Примечание: для копирования бинарных данных на диск они сперва должны быть конвертированы в нужный формат. В этом примере форматами были .jpg и .txt.

Появились вопросы? Задайте на Яндекс Кью

У блога есть сообщество на Кью >> Python Q <<. Там я и другие участники отвечаем на вопросы по python, программированию и контенту сайта.

Обучение Python и Data Science

Профессия Python-разработчик / Skillbox

Профессия Python-разработчик / Skillbox

7 313 2 925 ₽/мес.
Профессия Python Fullstack / Skillbox

Профессия Python Fullstack / Skillbox

6 569 2 627 ₽/мес.
Профессия Data Scientist / Skillbox

Профессия Data Scientist / Skillbox

9 187 3 675 ₽/мес.
Python-фреймворк на Django / Skillbox

Python-фреймворк на Django / Skillbox

818 ₽/мес.
Профессия DS: машинное обучение / Skillbox

Профессия DS: машинное обучение / Skillbox

6172 2469 ₽/мес.
Профессия DS: анализ данных / Skillbox

Профессия DS: анализ данных / Skillbox

6172 2469 ₽/мес.

Вам помогла эта статья? Поделитесь в соцсетях или блоге. Репосты помогают сайту развиться.

Александр эксперт Яндекс.Кью
Я создал этот блог в 2018 году, чтобы распространять полезные учебные материалы, документации и уроки на русском. На сайте опубликовано множество статей по основам python и библиотекам, уроков для начинающих и примеров написания программ.
Мои контакты: