Освоение базы данных SQLite на Python

SQLite - это система управления реляционными базами данных, основанная на языке SQL; это бессерверная СУБД с нулевой конфигурацией. Это один из самых популярных движков баз данных, который очень легко использовать в небольших приложениях. Он создает только один файл на диске для хранения всей базы данных, что делает файл переносимым. Он используется в ОС Android в качестве основного источника для хранения данных. Он также используется Google Chrome для хранения данных сайта и пользовательских данных, включая пароли, на локальном компьютере.

Расширенная работа с базой данных SQLite на Python

В этом руководстве будут рассмотрены следующие темы: вставка изображений в таблицу SQLite, перечисление таблиц, присутствующих в базе данных, определение общих изменений с момента подключения базы данных, резервное копирование базы данных, сброс базы данных SQLite, откат в SQLite, удаление записи из таблицы, удаление таблицы и исключения базы данных SQLite.

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

Файлы и изображения в базе данных SQLite

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

Чтобы добавить изображения в базу данных SQLite, нам нужно использовать тип данных BLOB SQLite. Тип данных BLOB () используется для хранения больших объектов, обычно больших файлов, таких как изображения, музыка, видео, документы, PDF и т. Д. Первым шагом является преобразование данных и изображений в байтовый объект Python, который похож на тип данных BLOB в SQLite. Прежде чем продолжить, создайте таблицу с именем ученик в базе с полями id, name, images, mark. Выполните следующий код, чтобы создать таблицу.

импортировать sqlite3 conn = sqlite3.подключить ("образец.db ") print (" \ n [+] Успешное подключение к базе данных ") cur = conn.cursor () print ("\ n [+] Курсор успешно настроен") table = cur.execute ("" "СОЗДАТЬ ТАБЛИЦУ student (id INT PRIMARY KEY, имя ТЕКСТ, изображения BLOB, отмечает ТЕКСТ);" "") print ("\ n [+] Таблица была успешно создана") cur.close () conn.commit () conn.Закрыть()

Эта программа создаст новую таблицу с именем ученик. Вы увидите следующий вывод в терминале.

Вставка изображения

Чтобы вставить изображение в базу данных SQLite, преобразуйте изображение в байтовый объект python, а затем вставьте его в столбец изображений, который принимает данные BLOB. Выполните следующий код, чтобы добавить изображение img.PNG в базе данных с использованием Python.

импортировать sqlite3 conn = sqlite3.подключить ("образец.db ") conn.text_factory = str print ("\ n [+] Успешное подключение к базе данных") cur = conn.cursor () print ("\ n [+] Курсор успешно настроен") with open ("img.png "," rb ") как файл: data = file.read () python_tuple = (101, "robin", data, "90") print ("\ n [+] Изображение было успешно импортировано") print ("\ n [+] Теперь вставка в базу данных") cur.execute ("ВСТАВИТЬ ученика (идентификатор, имя, изображения, оценки) ЗНАЧЕНИЯ (?,?,?,?) ", python_tuple) print (" \ n [+] Данные успешно вставлены ") cur.close () conn.commit () conn.Закрыть()

Эта программа вставит изображение в созданную вами студенческую базу данных. Вы увидите следующий вывод.

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

Получение изображения

Чтобы получить изображение из базы данных, выберите строку с помощью оператора select, а затем получите доступ к двоичным данным изображения в переменной python, которая будет сохранена в файле изображения. См. Следующий код для иллюстрации.

импортировать sqlite3 conn = sqlite3.подключить ("образец.db ") conn.text_factory = str print ("\ n [+] Успешное подключение к базе данных") cur = conn.cursor () print ("\ n [+] Курсор успешно настроен") print ("\ n [+] Получение изображения") cur.выполнить ("ВЫБРАТЬ * ОТ ученика") ret = cur.fetchall () для i в ret: data = i [2] с open ("img2.png "," wb ") как файл: файл.write (data) print ("\ n [+] Изображение сохранено") cur.close () conn.commit () conn.Закрыть()

Эта простая программа извлечет изображение из базы данных и сохранит его на диске с именем iмг2.PNG. Вы также можете выбрать другое имя для файла изображения. Результат программы показан ниже.

Список всех таблиц базы данных

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

ВЫБЕРИТЕ имя ИЗ sqlite_master WHERE type = "table"

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

импортировать sqlite3 conn = sqlite3.подключить ("образец.db ") print (" \ n [+] Успешное подключение к базе данных ") cur = conn.cursor () print ("\ n [+] Курсор успешно настроен") cur.execute ("ВЫБРАТЬ имя из sqlite_master, где type =" table "") rows = cur.fetchall () печать (строки) cur.close () conn.commit () conn.Закрыть()

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

Выявление общих изменений с момента подключения к базе данных

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

импортировать sqlite3 conn = sqlite3.подключить ("образец.db ") conn.text_factory = str print ("\ n [+] Успешное соединение с обеими базами данных") cur = conn.cursor () print ("\ n [+] Оба курсора успешно настроены") cur.execute ("INSERT INTO student (id, name, images ,marks) VALUES (140, 'David',", 99) ") cur.execute ("INSERT INTO student (id, name, images, mark) VALUES (150, 'Sam',", 97) ") changes = conn.total_changes print ("\ n [+] Всего изменений строк сейчас:", изменений) conn.commit () cur.close () conn.Закрыть()

Вышеупомянутая программа напечатает количество изменений строк в текущем соединении. Вы увидите следующий результат.

Откат в SQLite

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

импортировать sqlite3 conn = sqlite3.подключить ("образец.db ") conn.text_factory = str print ("\ n [+] Успешное соединение с обеими базами данных") cur = conn.cursor () print ("\ n [+] Оба курсора успешно настроены") cur.execute ("INSERT INTO student (id, name, images, mark) VALUES (100001, 'David',", 99) ") cur.execute ("INSERT INTO student (id, name, images ,marks) VALUES (100002, 'Sam',", 97) ") conn.commit () print ("\ n [+] Две строки успешно вставлены") cur.выполнить ("ВЫБРАТЬ * ОТ ученика") first = cur.fetchall () print ("\ n [+] Новые записи в базе данных:") для i в первом: print (i) cur.execute ("INSERT INTO student (id, name, images ,marks) VALUES (10003, 'Kishan',", 100) ") cur.execute ("INSERT INTO student (id, name, images, mark) VALUES (10004, 'Ankit',", 100) ") print (" \ n [+] Две строки были успешно вставлены, но не зафиксированы ") conn.rollback () print ("\ n [+] Мы откатили предыдущие команды, поэтому новые данные не будут вставлены") conn.commit () cur.выполнить ("ВЫБРАТЬ * ОТ ученика") second = cur.fetchall () print ("\ n [+] Новые записи в базе данных:") для i за секунду: print (i) cur.close () conn.Закрыть()

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

Резервное копирование базы данных

При работе с базой данных необходимо делать резервную копию базы данных. Модуль sqlite3 предоставляет функцию резервного копирования базы данных. Используя метод backup () объекта подключения, мы можем сделать резервную копию базы данных SQLite. Основной синтаксис метода резервного копирования:

backup (target, *, pages = 0, progress = None, name = "main", sleep = 0.250)

По умолчанию или когда страницы либо 0 или отрицательное целое число, вся база данных копируется за один шаг, что предпочтительно для небольшой базы данных; в противном случае метод выполняет копирование цикла до страницы в то время, когда это можно было сделать с обширной базой данных. В название Аргумент показывает имя базы данных, которая будет скопирована: это должна быть строка, содержащая либо значение по умолчанию, чтобы указать основную базу данных, либо указать временную базу данных. В спать аргумент указывает время в секундах, в течение которого засыпает между попытками резервного копирования оставшихся страниц. Это может быть целое число или значение с плавающей запятой.

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

импортировать sqlite3 conn_main = sqlite3.подключить ("образец.db ") conn_backup = sqlite3.подключить ("sample_backup.db ") print (" \ n [+] Успешное соединение с обеими базами данных ") cur_main = conn_main.курсор () cur_backup = conn_backup.cursor () print ("\ n [+] Оба курсора успешно настроены") conn_main.backup (conn_backup, pages = 0, progress = None, name = "main") print ("Резервное копирование базы данных выполнено успешно") cur_main.закрыть () cur_backup.закрыть () conn_main.совершить () conn_backup.совершить () conn_main.закрыть () conn_backup.Закрыть()

В приведенном выше коде две базы данных связаны, одна - это база данных, для которой мы хотим сделать резервную копию, а вторая - это база данных, в которой мы будем делать резервную копию. Использовать резервное копирование() метод первого объекта подключения к базе данных для создания резервной копии. Эта функция принимает объект подключения второй базы данных в качестве цели для создания резервной копии в другой базе данных. Использовать страницы = 0 аргументов, поэтому процесс будет происходить за один шаг, что рекомендуется для небольших баз данных. Эта программа создаст новый образец имени базы данных_резервное копирование.db и заполните его бэкапом первой базы. Вы можете увидеть, что в текущей папке была создана новая база данных с тем же размером файла, что и предыдущая.

Дамп базы данных SQLite

Дамп баз данных - важная задача. Обычно файл дампа представляет собой набор операторов SQL для данных, который обычно используется для резервного копирования. Мы можем выгрузить базу данных с помощью метода dump (). См. Пример ниже, чтобы узнать, как удалить базу данных SQLite.

импортировать sqlite3 con = sqlite3.connect ("база данных.db ") с open ('dump.sql ',' w ') как f: для строки в con.iterdump (): f.запись (строка '% s \ n'%)

Вышеупомянутая программа сбросит образец базы данных.db, и он сохранит выгруженные данные в файл с именем dump.sql. Вы можете увидеть данные, присутствующие в каталоге, в котором находятся файлы Python, и открыть их с помощью любого текстового редактора.

executemany () метод SQLite3

В исполнение () метод выполняет команду SQL для всех последовательностей параметров или сопоставлений, найденных в последовательности seq_of_parameters. Для простоты этот метод можно использовать для выполнения большинства команд SQL в одной строке. E.грамм., Мы можем вставить любое количество строк через список Python, используя эту команду. См. Пример ниже для иллюстрации.

импортировать sqlite3 conn = sqlite3.подключить ("образец.db ") print (" \ n [+] Успешное подключение к базе данных ") cur = conn.cursor () print ("\ n [+] Курсор успешно настроен") python_list = [(10000000, 'vivek', ", '10'), (100000001, 'rose',", '21'), (100000002, 'robin', ", '31'), (100000003, 'Dev',", '4'), (100000004, 'michael', ", '52')] cur.executemany ("ВСТАВИТЬ ученика (идентификатор, имя, изображения, оценки) ЗНАЧЕНИЯ (?,?,?,?) ", python_list) print (" \ n [+] Все данные успешно вставлены ") cur.close () conn.commit () conn.Закрыть()

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

Удалить записи из таблицы

Мы можем использовать операцию DELETE для удаления записей из таблицы. Мы можем быстро удалить строку, используя операцию DELETE с предложением WHERE. Базовый синтаксис оператора DELETE:

УДАЛИТЬ из table_name WHERE some_condition;

Посмотрим на пример. Мы удалим строку с идентификатором 1001 из таблицы сотрудников нашей базы данных.

импортировать sqlite3 conn = sqlite3.подключить ("образец.db ") conn.text_factory = str print ("\ n [+] Успешное подключение к базе данных") cur = conn.cursor () print ("\ n [+] Курсор успешно настроен") cur.execute ("УДАЛИТЬ ОТ ученика, ГДЕ id = 1001") print ("\ n [+] Строка успешно удалена") cur.выполнить ("ВЫБРАТЬ * ОТ ученика") data = cur.fetchall () для строки в данных: print (row) cur.close () conn.commit () conn.Закрыть()

Приведенный выше код удалит строку с идентификатором 1001. Вы можете видеть из возврата оператора SELECT, что строка была удалена. Результат программы показан ниже.

Бросить стол

Мы можем быстро удалить или удалить таблицу с помощью оператора SQLite DROP. Синтаксис оператора DROP показан ниже:

DROP table имя_таблицы 

Если таблица не существует, SQLite выдаст ошибку, поэтому, чтобы предотвратить это, мы можем использовать если есть тег с оператором DROP. См. Синтаксис ниже:

DROP table, если существует table_name

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

импортировать sqlite3 conn = sqlite3.подключить ("образец.db ") conn.text_factory = str print ("\ n [+] Успешное подключение к базе данных") cur = conn.cursor () print ("\ n [+] Курсор успешно настроен") cur.execute ("УДАЛИТЬ ТАБЛИЦУ, ЕСЛИ СУЩЕСТВУЕТ студент") print ("\ n [+] Таблица успешно удалена") cur.close () conn.commit () conn.Закрыть()

Вышеупомянутая программа удалит таблицу ученик от образец база данных. Мы можем использовать команду list table, которую мы видели ранее, чтобы увидеть, удалена ли таблица. Результат программы показан ниже.

Исключения базы данных SQLite

Некоторые исключения базы данных SQLite могут возникать из-за ошибки. Давайте посмотрим, когда возникли эти ошибки.

• sqlite3.Предупреждение: это подкласс исключения. Это исключение показывает некоторые предупреждения, которые во многих случаях можно игнорировать.
• sqlite3.Ошибка: это также подкласс исключения. Это базовый класс всех остальных исключений в sqlite3 модуль.
• sqlite3.DatabaseError: это ошибки, которые возникают из-за некоторых ошибок в базах данных. Например: - Если мы попытаемся подключиться к зашифрованной базе данных или неправильному файлу базы данных, то появится ошибка DatabaseError, что данные зашифрованы или недействительный файл базы данных.
• sqlite3.IntegrityError: это исключение является подклассом DatabaseError. Мы получим это исключение, когда будет нарушена реляционная целостность базы данных, e.грамм., Ошибка проверки внешнего ключа.
• sqlite3.ProgrammingError: это исключение также является подклассом DatabaseError. Это исключение возникает из-за ошибок программирования, e.грамм., создание таблицы с тем же именем, которая уже существует, синтаксическая ошибка в запросах SQL и т. д.
• sqlite3.OperationalError: это также подкласс DatabaseError. Это исключение возникает для ошибок, которые связаны с работой базы данных и не находятся под нашим контролем. Например, случайное отключение системы, отключение сервера, тайм-аут, проблемы с источником данных, выключение компьютера и т. Д.
• sqlite3.NotSupportedError: это исключение возникает, когда база данных не поддерживает API базы данных, который использовал.

Это список всех исключений SQLite; мы можем обрабатывать эти исключения в наших программах, используя базовый метод обработки ошибок try / except Python.

Заключение

На этом мы подошли к концу подробного руководства по расширенным методам работы с SQLite с использованием Python. Надеюсь, вы изучили все аспекты SQLite3 с помощью Python, что поможет нам создавать фантастические проекты Python.