Овладяване на базата данни SQLite в Python

SQLite е система за управление на релационни бази данни, базирана на езика SQL; това е безсървърна машина за база данни с нулева конфигурация. Това е един от най-популярните двигатели на бази данни и е много лесен за използване в малки приложения. Той създава само един дисков файл за съхранение на цялата база данни, което прави файла преносим. Той се използва в Android OS като основен източник за съхраняване на данни. Той се използва и от Google Chrome за съхраняване на данни за сайта и потребителски данни, включително пароли в локалната машина.

Разширена работа с базата данни SQLite в Python

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

Може да искате да видите и първата част на този урок, която представя основи на SQLite, Предимства при използването му, Свързване с файл на база данни, Създаване на таблица в базата данни, Вмъкване на данни в таблицата, Заявка за данни от таблицата, актуализиране на таблицата и много други.

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

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

За да добавим изображения в база данни на SQLite, трябва да използваме BLOB тип данни на SQLite. Типът данни BLOB () се използва за съхраняване на големи обекти, обикновено големи файлове като изображения, музика, видео, документи, PDF и т.н. Първата стъпка е да конвертирате данните и изображенията в байтовия обект на Python, който е подобен на типа BLOB данни на SQLite. Преди да продължите, създайте таблица с име студент в базата данни с полетата id, име, изображения, марки. Изпълнете следния код, за да създадете таблицата.

импортиране на sqlite3 conn = sqlite3.свързване ("проба.db ") print (" \ n [+] Успешно свързан към базата данни ") cur = conn.cursor () print ("\ n [+] Курсорът е настроен успешно") таблица = cur.execute ("" "CREATE TABLE student (id INT PRIMARY KEY, name TEXT, images BLOB, marks TEXT);" "") print ("\ n [+] Таблицата е създадена успешно") cur.close () conn.commit () conn.близо()

Тази програма ще създаде нова таблица с името студент. Ще видите следния изход в терминала.

Вмъкване на изображение

За да вмъкнете изображение в база данни на SQLite, трансформирайте изображението в байтов обект на python и след това го вмъкнете в колоната images, която приема BLOB данни. Изпълнете следния код, за да добавите изображение img.png в базата данни с помощта на Python.

импортиране на sqlite3 conn = sqlite3.свързване ("проба.db ") свързване.text_factory = str print ("\ n [+] Успешно свързан към базата данни") cur = conn.cursor () print ("\ n [+] Курсорът е настроен успешно") с open ("img.png "," rb ") като файл: данни = файл.read () python_tuple = (101, "robin", data, "90") print ("\ n [+] Изображението е импортирано успешно") print ("\ n [+] Сега се вмъква в базата данни") cur.изпълни ("INSERT INTO student (id, name, images, marks) VALUES (?,?,?,?) ", python_tuple) print (" \ n [+] Данните са вмъкнати успешно ") cur.close () conn.commit () conn.близо()

Тази програма ще вмъкне изображението в базата данни за ученици, която сте създали. Ще видите следния изход.

В горната програма отворихме файла в двоичен режим и четем всеки байт и го съхраняваме в променлива данни. След това използваме тази променлива в оператора INSERT, за да вмъкнем изображението в базата данни.

Извличане на изображение

За да извлечете изображение от база данни, вземете реда с помощта на оператор select и след това отворете двоичните данни на изображението в променлива на python, която ще се съхранява в файл с изображение. Вижте следния код за илюстрация.

импортиране на sqlite3 conn = sqlite3.свързване ("проба.db ") свързване.text_factory = str print ("\ n [+] Успешно свързан към базата данни") cur = conn.cursor () print ("\ n [+] Курсорът е настроен успешно") print ("\ n [+] Извличане на изображението") cur.изпълни ("SELECT * FROM student") ret = cur.fetchall () за i in ret: data = i [2] с open ("img2.png "," wb ") като файл: файл.запис (данни) печат ("\ n [+] Изображението е запазено") cur.close () conn.commit () conn.близо()

Тази проста програма ще извлече изображението от базата данни и ще го запази на диска с име img2.png. Можете също да изберете друго име за файла с изображението. Резултатът от програмата е показан по-долу.

Избройте всички таблици на база данни

В база данни можем да създадем множество таблици. Така че също така е необходимо да се изброят всички таблици, присъстващи в база данни. За да изброите таблиците, присъстващи в база данни, направете заявка за таблицата sqlite_master, като използвате оператора SELECT на SQL. Синтаксисът на заявката ще бъде:

ИЗБЕРЕТЕ име ОТ sqlite_master WHERE type = "table"

Ето как използваме тази заявка, за да изброим всички таблици, присъстващи в нашата база данни.

импортиране на sqlite3 conn = sqlite3.свързване ("проба.db ") print (" \ n [+] Успешно свързан към базата данни ") cur = conn.cursor () print ("\ n [+] Курсорът е настроен успешно") cur.изпълни ("ИЗБЕРЕТЕ име от sqlite_master, където type =" table "") редове = cur.fetchall () print (редове) cur.close () conn.commit () conn.близо()

Горният код ще изброи всички таблици, присъстващи в нашата база данни. Изходът, произведен от кода при изпълнение, е както следва. Може да видите някои други резултати в зависимост от таблиците, които сте създали в базата данни.

Идентифициране на общите промени след свързване с базата данни

Във всяка ситуация е полезно да се идентифицира броят на редовете, които са били модифицирани, вмъкнати или изтрити след свързването на базата данни. За това използвайте общо_промени () метод на обекта на свързване, който ще върне общия брой редове на базата данни, които са били засегнати от връзката. Нека видим примерна демонстрация, за да разберем как работи.

импортиране на sqlite3 conn = sqlite3.свързване ("проба.db ") свързване.text_factory = str print ("\ n [+] Успешно свързан към двете бази данни") cur = conn.cursor () print ("\ n [+] И двата курсора са настроени успешно") cur.изпълни ("INSERT INTO student (id, name, images, marks) VALUES (140, 'David',", 99) ") cur.изпълни ("INSERT INTO student (id, name, images, marks) VALUES (150, 'Sam',", 97) ") промени = conn.total_changes print ("\ n [+] Общото количество промени в редовете е:", промени) conn.commit () cur.close () conn.близо()

Горната програма ще отпечата броя промени в редовете в текущата връзка. Ще видите следния изход.

Отмяна в SQLite

Когато става въпрос за отмяна на някои задачи, можете да използвате функцията за връщане назад (). Този метод може да се използва за отмяна на задача, която е била направена след последния фиксиране. Вижте долния пример за илюстрация.

импортиране на sqlite3 conn = sqlite3.свързване ("проба.db ") свързване.text_factory = str print ("\ n [+] Успешно свързан към двете бази данни") cur = conn.cursor () print ("\ n [+] Курсорът е настроен успешно") cur.изпълни ("INSERT INTO student (id, name, images, marks) VALUES (100001, 'David',", 99) ") cur.изпълни ("INSERT INTO student (id, name, images, marks) VALUES (100002, 'Sam',", 97) ") conn.commit () print ("\ n [+] Двата реда бяха вмъкнати успешно") cur.изпълни ("SELECT * FROM student") first = cur.fetchall () print ("\ n [+] Новите записи в базата данни са:") за i в first: print (i) cur.изпълни ("INSERT INTO student (id, name, images, marks) VALUES (10003, 'Kishan',", 100) ") cur.execute ("INSERT INTO student (id, name, images, marks) VALUES (10004, 'Ankit',", 100) ") print (" \ n [+] Двата реда са вмъкнати успешно, но не са ангажирани ") conn.rollback () print ("\ n [+] Върнахме предишните команди, така че новите данни няма да бъдат вмъкнати") conn.commit () cur.изпълни ("SELECT * FROM student") second = cur.fetchall () print ("\ n [+] Новите записи в базата данни са:") за i във секунда: print (i) cur.close () conn.близо()

В горния пример първите два оператора за вмъкване ще вмъкнат данните, както са дадени, но последните два оператора за вмъкване ще бъдат върнати, така че няма да добавят никакви данни в таблицата. Резултатът ще бъде както е показано по-долу.

Архивирайте база данни

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

архивиране (цел, *, страници = 0, прогрес = няма, име = "основно", сън = 0.250)

По подразбиране или кога страници са или 0 или отрицателно цяло число, цялата база данни се копира в една стъпка, което е за предпочитане за малка база данни; в противен случай методът извършва копиране на цикъл до страници в момент, който може да се направи с обширната база данни. The име аргументът показва името на базата данни, която ще се копира: тя трябва да бъде низ, съдържащ или подразбиране, за да посочи основната база данни или да посочи временната база. The сън аргументът указва времето в секунди за заспиване между опитите за архивиране на останалите страници. Тя може да бъде или като цяло число, или като стойност с плаваща запетая.

Нека вземем резервно копие на база данни.db база данни, която използвахме в урока.

импортиране на sqlite3 conn_main = sqlite3.свързване ("проба.db ") conn_backup = sqlite3.свързване ("sample_backup.db ") print (" \ n [+] Успешно свързан към двете бази данни ") cur_main = conn_main.cursor () cur_backup = conn_backup.cursor () print ("\ n [+] И двата курсора са настроени успешно") conn_main.backup (conn_backup, pages = 0, progress = None, name = "main") print ("Базата данни е архивирана успешно") cur_main.close () cur_backup.close () conn_main.commit () conn_backup.commit () conn_main.close () conn_backup.близо()

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

Дъмпинг на база данни на SQLite

Дъмпингът на бази данни е важна задача. Обикновено дамп файлът е набор от SQL изрази за данните, който обикновено се използва за архивиране. Можем да изхвърлим база данни, използвайки метода dump (). Вижте примера по-долу, за да знаете как да пуснете база данни на SQLite.

импортиране на sqlite3 con = sqlite3.свързване ("база данни.db ") с отворен ('dump.sql ',' w ') като f: за ред в con.iterdump (): f.запис ('% s \ n'% ред)

Горната програма ще извади пробата от базата данни.db и ще запази изхвърлените данни във файл с име dump.кв. Можете да видите данните в директорията, където са актуални файловете на python, и да ги отворите с помощта на всеки текстов редактор.

метод на executemany () на SQLite3

The Изпълнение () метод изпълнява SQL команда срещу всички последователности от параметри или съпоставяния, намерени в последователността seq_of_parameters. За простота този метод може да се използва за изпълнение на повечето SQL команди в един ред. Е.ж., С помощта на тази команда можем да вмъкнем произволен брой редове през списък на 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 ("INSERT INTO student (id, name, images, marks) VALUES (?,?,?,?) ", python_list) print (" \ n [+] Всички данни са вмъкнати успешно ") cur.close () conn.commit () conn.близо()

Горната програма ще вмъкне всички данни, дадени в списъка на python. Продукцията, получена от програмата, е показана по-долу.

Изтриване на записи от таблица

Можем да използваме операцията DELETE за изтриване на записи от таблица. Можем бързо да премахнем ред, използвайки операцията DELETE с клаузата WHERE. Основният синтаксис на оператора DELETE е:

ИЗТРИВАНЕ от име_на таблица КЪДЕ някакво_условие;

Да видим пример. Ще изтрием реда с идентификатор 1001 от таблицата със служители в нашата база данни.

импортиране на sqlite3 conn = sqlite3.свързване ("проба.db ") свързване.text_factory = str print ("\ n [+] Успешно свързан към базата данни") cur = conn.cursor () print ("\ n [+] Курсорът е настроен успешно") cur.изпълни ("ИЗТРИВАНЕ ОТ ученика, КЪДЕ id = 1001") print ("\ n [+] Редът беше изтрит Успешно") cur.изпълни ("SELECT * FROM student") данни = cur.fetchall () за ред в данни: печат (ред) cur.close () conn.commit () conn.близо()

Горният код ще изтрие реда с ID 1001. От връщането на оператора SELECT можете да видите, че редът е премахнат. Резултатът от програмата е както е показано по-долу.

Пуснете маса

Можем бързо да пуснем или изтрием таблица, като използваме оператора SQLite DROP. Синтаксисът на израза DROP е показан по-долу:

DROP таблица име_на_име

Ако таблицата не съществува, тогава SQLite ще изведе грешка, така че за да предотвратим това, можем да използваме ако съществуват таг с израза DROP. Вижте синтаксиса по-долу:

DROP таблица, ако съществува име_на_таблица

Нека видим как можем да използваме това изявление с python sqlite3 модул за изтриване на таблица. В тази програма ще премахнем студент таблица, която сме създали по-рано.

импортиране на sqlite3 conn = sqlite3.свързване ("проба.db ") свързване.text_factory = str print ("\ n [+] Успешно свързан към базата данни") cur = conn.cursor () print ("\ n [+] Курсорът е настроен успешно") cur.изпълни ("DROP TABLE IF EXISTS student") print ("\ n [+] Таблицата е пусната успешно") cur.close () conn.commit () conn.близо()

Горната програма ще изтрие таблицата студент от проба база данни. Можем да използваме командата list list, която сме виждали по-рано, за да видим дали таблицата е изтрита. Резултатът от програмата е както е показано по-долу.

Изключения на базата данни на SQLite

Някои изключения на базата данни на SQLite могат да бъдат повдигнати поради някаква грешка. Нека да видим малко кога са възникнали тези грешки.

sqlite3.Предупреждение: Това е подклас на изключение. Това изключение показва някои предупреждения и те могат да бъдат игнорирани в много случаи.
sqlite3.Грешка: Това също е подклас на изключение. Това е основният клас на всички други изключения в sqlite3 модул.
sqlite3.DatabaseError: Това са грешките, които са възникнали поради някои грешки в базите данни. Например: - Ако се опитаме да се свържем с криптирана база данни или грешен файл на базата данни, тогава той ще покаже DatabaseError, че данните са криптирани или не са валиден файл на базата данни.
sqlite3.IntegrityError: Това изключение е подклас на DatabaseError. Ще получим това изключение, когато е засегната релационната цялост на база данни, напр.ж., Неуспех при проверката на външния ключ.
sqlite3.ProgrammingError: Това изключение също е подклас на DatabaseError. Това изключение е повдигнато поради грешки в програмирането, напр.ж., създаване на таблица със същото име, което вече съществува, синтаксична грешка в SQL заявките и т.н.
sqlite3.OperationalError: Това е и подклас на DatabaseError. Това изключение възниква за грешки, които са свързани с работата на базата данни и не са под наш контрол. Например случайно прекъсване на връзката със системата, прекъсване на сървъра, изчакване, проблеми с източника на данни, изключване на машината и т.н.
sqlite3.NotSupportedError: Това изключение се повдига, когато базата данни не поддържа API на база данни, който се използва.

Това е списъкът на всички изключения на SQLite; можем да се справим с тези изключения в нашите програми, като използваме основния метод за обработка на грешки / с изключение на Python.

Заключение

Това ни води до края на изчерпателното ръководство за усъвършенствани методи за работа с SQLite с помощта на Python. Надявам се, че сте научили всички аспекти на SQLite3 с помощта на Python, което ще ни помогне да изградим фантастични проекти на Python.