Зміст

Підключення до SQLite у Python

У цьому розділі розберемо, як створювати базу даних SQLite та підключатися до неї у Python за допомогою модуля sqlite3.

Для встановлення з'єднання необхідно вказати назву бази даних, до якої потрібно підключитися. Якщо вказати назву тієї, що вже є на диску, буде підключення. Якщо ж вказати інше, SQLite створить нову базу даних.

Для підключення до SQLite необхідно виконати такі кроки

Використовувати метод connect() з модуля sqlite3 і передати як аргумент назву бази даних
Створити об'єкт cursor за допомогою об'єкта з'єднання, який повернув попередній метод для виконання SQLite-запитів з Python
Закрити об'єкт cursor після завершення роботи
Перехопити виключення бази даних, якщо під час підключення сталася помилка

Наступна програма створює файл бази даних sqlite_python.db та виводить подробиці про версію SQLite.

import sqlite3

try:
    sqlite_connection = sqlite3.connect('sqlite_python.db')
    cursor = sqlite_connection.cursor()
    print("База данных создана и успешно подключена к SQLite")

    sqlite_select_query = "select sqlite_version();"
    cursor.execute(sqlite_select_query)
    record = cursor.fetchall()
    print("Версия базы данных SQLite: ", record)
    cursor.close()

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

Після підключення має з'явитися таке повідомлення:

База данных создана и успешно подключена к SQLite
Версия базы данных SQLite:  [('3.31.1',)]
Соединение с SQLite закрыто

Розуміння SQLite-підключення у подробицях

import sqlite3

Цей рядок імпортує модуль sqlite3. За допомогою класів і методів цього модуля можна взаємодіяти з базою даних SQLite.

sqlite3.connect()

За допомогою методу connect() виконується підключення до бази даних. Цей метод повертає SQLite об'єкт підключення.
Об'єкт connection не є безпечним. Модуль sqlite3 не дозволяє ділитися з'єднанням між потоками. Якщо спробувати зробити це, можна отримати виняток.
Метод connect() приймає різні аргументи. У цьому вся прикладі передається назва бази даних.

cursor=sqliteConnection.cursor()

За допомогою об'єкта з'єднання створюється об'єкт cursor, який дозволяє виконувати SQLite запити з Python
Для одного з'єднання можна створити необмежену кількість cursor. Він також не є потокобезпечним. Модуль не дозволяє ділитися об'єктами cursor між потоками. Якщо це зробити, буде помилка

Після цього створюється запит на отримання версії бази даних.

cursor.execute()

За допомогою методу execute об'єкта cursor можна виконати запит до бази даних із Python. Він приймає SQLite-запит як параметр і повертає resultSet - тобто рядки бази даних
Отримати результат запиту з resultSet можна за допомогою методів, наприклад, fetchAll()
У цьому прикладі SELECT version(); виконується для отримання версії бази даних SQLite

Блок try-except-finally: весь код розташований у блоці try-except, що дозволить перехопити винятки та помилки бази даних, які можуть виникнути в процесі.

За допомогою класу sqlite3.Error можна обробити будь-яку помилку та виняток, які можуть з'явитися під час роботи з SQLite з Python
Це дозволить зробити програму більш стійкою до відмови. Клас sqlite3.Error дозволить зрозуміти суть помилки. Він повертає повідомлення та код помилки

cursor.close() та connection.close()

Хорошою практикою вважається закривати об'єкти connection і curosor після завершення роботи, щоб уникнути проблем із базою даних.

Створення таблиці SQLite у Python

У цьому розділі розберемося, як створювати таблиці в базі даних SQLite за допомогою Python та sqlite3.

Створення таблиці — це DDL-запит, який виконується з Python.

У цьому прикладі створимо базу sqlitedb_developers у базі даних sqlite_python.db.

Кроки для створення таблиці в SQLite за допомогою Python:

З'єднатися з базою даних за допомогою sqlite3.connect(). Йшлося про це у першому розділі
Підготувати запит створення таблиці
Виконати запит, використовуючи cursor.execute(query)
Закрити з'єднання з базою та об'єктом cursor

import sqlite3

try:
    sqlite_connection = sqlite3.connect('sqlite_python.db')
    sqlite_create_table_query = '''CREATE TABLE sqlitedb_developers (
                                id INTEGER PRIMARY KEY,
                                name TEXT NOT NULL,
                                email text NOT NULL UNIQUE,
                                joining_date datetime,
                                salary REAL NOT NULL);'''

    cursor = sqlite_connection.cursor()
    print("База данных подключена к SQLite")
    cursor.execute(sqlite_create_table_query)
    sqlite_connection.commit()
    print("Таблица SQLite создана")

    cursor.close()

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

База данных подключена к SQLite
Таблица SQLite создана
Соединение с SQLite закрыто

Типи даних SQLite та відповідність типу Python

Перед переходом до виконання CRUD-операцій у SQLite з Python спочатку потрібно розібратися з типами даних SQLite та відповідними ним типами даних у Python, які допомагають зберігати та зчитувати дані з таблиці.

У движка SQLite є кілька класів для зберігання значень. Кожне значення, що зберігається в базі даних, має один із таких типів або класів.

Типи даних SQLite:

NULL — значення NULL
INTEGER — числові значення. Цілі числа зберігаються в 1, 2, 3, 4, 6 та 8 байтах залежно від величини
REAL — числа з плаваючою точкою, наприклад, 3.14, число Пі
TEXT — текстові значення. Можуть зберігатися у кодуванні UTF-8, UTF-16BE або UTF-16LE
BLOB — бінарні дані. Для зберігання зображень та файлів

Наступні типи даних з Python без проблем конвертуються в SQLite.

Для конвертації достатньо лише запам'ятати цю таблицю:

Тип Python	Тип SQLite
None	NULL
int	INTEGER
float	REAL
str	TEXT
bytes	BLOB

Виконання SQL запитів за допомогою функції executescript

Скрипти SQLite чудово справляються зі стандартними завданнями.

Це набір SQL-команд, збережених у файлі (у форматі .sql). Один файл містить одну або більше SQL-операцій, які потім виконуються з командного рядка.

Далі кілька поширених сценаріїв використання SQL-скриптів

Створення резервних копій відразу кількох баз даних за один раз
Порівняння кількості рядків двох різних баз із однією схемою
Створення всіх таблиць в одному скрипті, що дозволить створити потрібну схему на будь-якому сервері

Виконати скрипт із командного рядка SQLite можна за допомогою команди .read:

sqlite> .read sqlitescript.sql

Наприклад, цей простий скрипт створює дві таблиці:

CREATE TABLE fruits (
 id INTEGER PRIMARY KEY,
 name TEXT NOT NULL,
 price REAL NOT NULL
);

CREATE TABLE drinks (
 id INTEGER PRIMARY KEY,
 name TEXT NOT NULL,
 price REAL NOT NULL
);

Тепер подивимося, як виконати його з Python:

import sqlite3

try:
    sqlite_connection = sqlite3.connect('sqlite_python.db')
    cursor = sqlite_connection.cursor()
    print("База данных подключена к SQLite")

    with open('sqlite_create_tables.sql', 'r') as sqlite_file:
        sql_script = sqlite_file.read()

    cursor.executescript(sql_script)
    print("Скрипт SQLite успешно выполнен")
    cursor.close()

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

Таблиці SQLite створюються з допомогою виконання скрипта з Python. Висновок:

База данных подключена к SQLite
Скрипт SQLite успешно выполнен
Соединение с SQLite закрыто

Примітка: після з'єднання з SQLite весь вміст файлу зберігається в змінній. Потім використовується команда cursor.executescript(script) для виконання всіх інструкцій за один раз.

Винятки бази даних SQLite

sqlite3.Warning. Підклас Exception. Його можна ігнорувати, якщо потрібно, щоб воно не зупиняло виконання.
sqlite3.Error. Базовий клас для інших винятків sqlite3. Підклас Exception.
sqlite3.DatabaseError. Виняток, який повертається при помилках бази даних. Наприклад, якщо спробувати відкрити файл як базу sqite3, хоча він не є, то повернеться помилка «sqlite3.DatabaseError: file is encrypted or is not a database».
sqlite3.IntegrityError. Підклас DatabaseError. Ця помилка повертається, коли стосуються відносини в базі, наприклад, наприклад, не проходить перевірка зовнішнього ключа.
sqlite3.ProgrammingError. Підклас DatabaseError. Ця помилка виникає через помилки програміста: створення таблиці з ім'ям, яке вже зайняте, синтаксична помилка в SQL-запитах.
sqlite3.OperationalError. Підклас DatabaseError. Цю помилку неможливо контролювати. Вона з'являється у ситуаціях, що стосуються роботи бази даних, наприклад, обрив з'єднання, непрацюючий сервер, проблеми з джерелом даних тощо.
sqlite3.NotSupportedError. Цей виняток з'являється при спробі використовувати API, що не підтримується базою даних. Приклад: виклик методу rollback() для з'єднання, яке не підтримує транзакцію. Виклик комміту після команди створення таблиці.

Таким чином рекомендується завжди писати код управління базою даних у блоці try, щоб була можливість перехоплювати винятки та робити дії, які допоможуть впоратися з ними.

Наприклад, спробуємо додати дані в таблицю, якої немає, і виведемо весь стек винятків з Python.

import sqlite3
import traceback
import sys

try:
    sqlite_connection = sqlite3.connect('sqlite_python.db')
    cursor = sqlite_connection.cursor()
    print("База данных подключена к SQLite")

    sqlite_insert_query = """INSERT INTO unknown_table_1
                          (id, text)  VALUES  (1, 'Демо текст')"""

    count = cursor.execute(sqlite_insert_query)
    sqlite_connection.commit()
    print("Запись успешно вставлена в таблицу sqlitedb_developers ", cursor.rowcount)
    cursor.close()

except sqlite3.Error as error:
    print("Не удалось вставить данные в таблицу sqlite")
    print("Класс исключения: ", error.__class__)
    print("Исключение", error.args)
    print("Печать подробноcтей исключения SQLite: ")
    exc_type, exc_value, exc_tb = sys.exc_info()
    print(traceback.format_exception(exc_type, exc_value, exc_tb))
finally:
    if (sqlite_connection):
        sqlite_connection.close()
        print("Соединение с SQLite закрыто")

База данных подключена к SQLite
Не удалось вставить данные в таблицу sqlite
Класс исключения:  <class 'sqlite3.OperationalError'>
Исключение ('no such table: unknown_table_1',)
Печать подробноcтей исключения SQLite: 
['Traceback (most recent call last):\n', '  File "C:\\Users\\demo\\AppData\\Local\\Programs\\Python\\Python38\\sqlitet.py", line 13, in <module>\n    count = cursor.execute(sqlite_insert_query)\n', 'sqlite3.OperationalError: no such table: unknown_table_1\n']
Соединение с SQLite закрыто

Зміни timeout при підключенні з Python

Буває таке, що є кілька підключень до бази даних SQLite і одне з них виконує певну зміну. Для цього з'єднання потрібно виконати блокування — база даних блокується доти, доки транзакція не буде завершена.

Параметр timeout, який задається при підключенні, визначає, як довго з'єднання чекатиме відключення блокування перед поверненням виключення.

За замовчуванням значення цього параметра дорівнює 5.0 (5 секунд). Його не потрібно ставити, тому що це значення за замовчуванням. Таким чином, при підключенні до бази даних з Python, якщо відповідь не буде отримана протягом 5 секунд, повернеться виняток. Однак параметр можна задати у функції sqlite3.connect.

Подивимося, як це зробити з Python:

import sqlite3

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

        sqlite_select_query = """SELECT count(*) from sqlitedb_developers"""
        cursor.execute(sqlite_select_query)
        total_rows = cursor.fetchone()
        print("Всего строк:  ", total_rows)
        cursor.close()

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

read_sqlite_table()

Висновок:

Подключен к SQLite
Всего строк:   (0,)
Соединение с SQLite закрыто

Отримання змін з моменту підключення до бази даних

Для статистики може знадобитися кількість рядків бази даних, які були вставлені, видалені або змінені з моменту відкриття з'єднання. Для цього використовується функція connection.total_changes sqlite3.

Цей метод повертається загальна кількість рядків, які були порушені.

Розглянемо приклад:

import sqlite3

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

    sqlite_insert_query = """INSERT INTO sqlitedb_developers
                          (id, name, email, joining_date, salary)
                          VALUES (4, 'Alex', 'sale@gmail.com', '2020-11-20', 8600);"""
    cursor.execute(sqlite_insert_query)

    sql_update_query = """Update sqlitedb_developers set salary = 10000 where id = 4"""
    cursor.execute(sql_update_query)

    sql_delete_query = """DELETE from sqlitedb_developers where id = 4"""
    cursor.execute(sql_delete_query)

    sqlite_connection.commit()
    cursor.close()

except sqlite3.Error as error:
    print("Ошибка при работе с SQLite", error)
finally:
    if (sqlite_connection):
        print("Всего строк, измененных после подключения к базе данных: ", sqlite_connection.total_changes)
        sqlite_connection.close()
        print("Соединение с SQLite закрыто")

Подключен к SQLite
Всего строк, измененных после подключения к базе данных:  3
Соединение с SQLite закрыто

Збереження резервної копії бази даних із Python

Модуль sqlite3 у Python надає функцію для збереження резервної копії бази даних SQLite.

За допомогою методу connection.backup() можна створити резервну копію бази SQLite.

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

Ця функція створює повну резервну копію бази даних SQLite. Зміни записуються в аргумент target, який має бути екземпляром іншого з'єднання.

За замовчуванням, коли параметр pages дорівнює 0 або негативному числу, вся база даних копіюється в один крок. В іншому випадку метод виконує цикл, копіюючи задану кількість сторінок за один раз.

Аргумент name визначає базу даних, резервну копію якої необхідно зробити. Аргумент sleep — кількість секунд між послідовними спробами зберегти сторінки, що залишилися. Аргумент sleep можна задати як цілого числа, так і у вигляді числа з плаваючою точкою.

Розглянемо один приклад копіювання бази даних до іншої:

import sqlite3

def progress(status, remaining, total):
    print(f'Скопировано {total-remaining} из {total}...')

try:
    sqlite_con = sqlite3.connect('sqlite_python.db')
    backup_con = sqlite3.connect('sqlite_backup.db')
    with backup_con:
        sqlite_con.backup(backup_con, pages=3, progress=progress)
    print("Резервное копирование выполнено успешно")
except sqlite3.Error as error:
    print("Ошибка при резервном копировании: ", error)
finally:
    if(backup_con):
        backup_con.close()
        sqlite_con.close()

Скопировано 3 из 5...
Скопировано 5 из 5...
Резервное копирование выполнено успешно

Примітки:

Після підключення до SQLite обидві бази даних були відкриті за допомогою двох різних підключень
Далі виконується метод connection.backup() за допомогою екземпляра першого підключення. Також встановлено кількість сторінок, які потрібно скопіювати за одну ітерацію

Потрібно підтягнути знання Рython? Курси до ваших послуг 👇

Введення в SQLite Python