Как создавать бэкап SQLite3?

Question

SQLite3 хранит данные в одном или нескольких файлах (для режима WAL) и сделал решение через копирование файлов базы данных (пример):

def db_create_backup(
        log: logging.Logger,
        backup_dir=BACKUP_DIR_NAME,
        date_fmt="%Y-%m-%d",
):
    backup_path = Path(backup_dir)

    backup_path_db = backup_path / DB_DIR_NAME.name
    backup_path_db.mkdir(parents=True, exist_ok=True)

    ...

    zip_name = dt.datetime.today().strftime(date_fmt)
    zip_name = backup_path_db / zip_name

    attempts = 5
    for i in range(attempts):
        try:
            log.info(f"Создание бэкапа базы данных в: {zip_name}")
            shutil.make_archive(zip_name, "zip", DB_DIR_NAME)

            ...

Но это решение вызывает сомнения, несмотря на его работоспособность - выглядит костыльно-велосипедно.

Можно ли более правильно создавать бекапы sqlite3?

Answer 1

Нашлось решение (но я бы рекомендовал решение из ответа с sqlite3.Connection.backup) в sql-команде VACUUM INTO {filename}. В нее нужно передавать строку, поэтому, если используется pathlib.Path, нужно будет сделать приведение к строке. Еще, команда не умеет переписывать существующий файл (если еще не пустой), поэтому нужно будет удалять файл.

Из плюсов, эта команда умеет выгружать базу из памяти (:memory:)

Алгоритм:

if file_name.exists():
    file_name.unlink()

connect.execute("VACUUM INTO ?", (str(file_name),))

Реализация и пример:

import sqlite3

from datetime import date
from pathlib import Path


FILE_NAME = Path(__file__).resolve()
DIR = FILE_NAME.parent

DIR_DB = DIR / "databases"
DIR_DB.mkdir(parents=True, exist_ok=True)

DIR_DB_BACKUP = DIR_DB / "backup"
DIR_DB_BACKUP.mkdir(parents=True, exist_ok=True)


def backup(connect: sqlite3.Connection, file_name: Path):
    if file_name.exists():
        file_name.unlink()

    connect.execute("VACUUM INTO ?", (str(file_name),))


file_name_db = str(DIR_DB / FILE_NAME.stem) + ".db"
print(file_name_db)
# C:\Users\...\databases\backup.db

with sqlite3.connect(file_name_db) as connect:
    connect.executescript(
        """
        create table if not exists stocks (
            date text, 
            trans text, 
            symbol text,
            qty real,
            price real
        );

        insert into stocks values ('2006-01-05', 'BUY', 'RHAT', 100, 35.14);
        insert into stocks values ('2006-05-01', 'BUY', 'TFAR', 40, 112.10);
    """
    )

    print()
    print(connect.execute("select * from stocks").fetchall())
    print()

    file_name_backup = DIR_DB_BACKUP / f"{FILE_NAME.stem}_{date.today()}.db"
    backup(connect, file_name_backup)
    print(f"Создан бэкап базы данных в: {file_name_backup}")
    # Создан бэкап базы данных в: C:\Users\...\databases\backup\backup_2024-07-01.db

Answer 2

Второй вариант - через Backup API, который реализован (см. 1, см. 2) через метод соединения sqlite3.Connection.backup (спасибо needKVAS за информацию)

Как и VACUUM INTO у умеет работать с :memory:, но в отличии от него не нужно чистить файл/базу - бэкап не накапливает предыдущие значения

Алгоритм (минимальный вариант):

dst = sqlite3.connect(file_name)
connect.backup(dst)
dst.close()

Алгоритм (расширенный вариант - указание инкрементальности выгрузки, логирование процесса):

def progress(status, remaining, total):
    print(f'Copied {total-remaining} of {total} pages...')


dst = sqlite3.connect(file_name, pages=1, progress=progress)
connect.backup(dst)
dst.close()

Реализация и пример:

import sqlite3

from datetime import date
from pathlib import Path


FILE_NAME = Path(__file__).resolve()
DIR = FILE_NAME.parent

DIR_DB = DIR / "databases"
DIR_DB.mkdir(parents=True, exist_ok=True)

DIR_DB_BACKUP = DIR_DB / "backup"
DIR_DB_BACKUP.mkdir(parents=True, exist_ok=True)


def backup(
        connect: sqlite3.Connection,
        file_name: Path,
) -> Path:
    dst = sqlite3.connect(file_name)
    connect.backup(dst)
    dst.close()

    return file_name


file_name_db = str(DIR_DB / FILE_NAME.stem) + ".db"
print(file_name_db)
# C:\Users\...\databases\backup.db

with sqlite3.connect(file_name_db) as connect:
    connect.executescript(
        """
        create table if not exists stocks (
            date text, 
            trans text, 
            symbol text,
            qty real,
            price real
        );

        insert into stocks values ('2006-01-05', 'BUY', 'RHAT', 100, 35.14);
        insert into stocks values ('2006-05-01', 'BUY', 'TFAR', 40, 112.10);
    """
    )

    print()
    print(connect.execute("select * from stocks").fetchall())
    print()

    file_name_backup = DIR_DB_BACKUP / f"{FILE_NAME.stem}_{date.today()}.db"
    backup(connect, file_name_backup)
    print(f"Создан бэкап базы данных в: {file_name_backup}")
    # Создан бэкап базы данных в: C:\Users\...\databases\backup\backup_2024-07-01.db

БЛОГ НА HUSL

Как создавать бэкап SQLite3?

Ответы (2 шт):