Как создать и удалить таблицу в DB Browser for SQLite

Создание и удаление таблиц в DB Browser for SQLite делается двумя способами: через визуальный мастер или SQL-запросом на вкладке Execute SQL. Ниже пошагово разобраны оба варианта, флаги столбцов PK, AI, NN и U, изменение структуры готовой таблицы, безопасное удаление и то, как те же действия выполнить из Python.

Подготовка базы данных

DB Browser for SQLite (DB4S) - бесплатная программа с открытым кодом для Windows, macOS и Linux. Установщик берут на официальном сайте, обзор интерфейса есть в статье про DB Browser for SQLite. Интерфейс переведен на русский, но ниже используются английские названия кнопок, так как они одинаковы во всех сборках.

База SQLite хранится в одном файле. Чтобы начать с нуля, нажмите New Database на панели инструментов и выберите место и имя файла, например shop.db. Сразу после сохранения программа откроет окно создания первой таблицы. Его можно закрыть и вернуться позже. Существующий файл открывается кнопкой Open Database.

Главное правило DB4S: все изменения сначала копятся в памяти и попадают в файл только после нажатия Write Changes (Ctrl+S). Кнопка Revert Changes отменяет все, что сделано после последнего сохранения. Если закрыть программу без сохранения, она спросит, записать ли изменения.

Создание таблицы через мастер

Мастер удобен, когда SQL еще не освоен или нужно быстро набросать структуру. Он сам формирует запрос CREATE TABLE и показывает его внизу окна.

1. Откройте вкладку Database Structure и нажмите Create Table.
2. В поле с именем таблицы введите название латиницей, например products.
3. На вкладке Fields нажмите Add, чтобы добавить столбец, и задайте ему имя и тип.
4. Отметьте нужные флаги, при необходимости укажите значение по умолчанию в Default.
5. Нажмите OK, затем Write Changes.

Флаги в таблице полей отвечают за ограничения столбца:

Для типов в SQLite достаточно пяти вариантов: INTEGER, REAL, TEXT, BLOB и NUMERIC. Как SQLite трактует типы и почему в столбец INTEGER формально можно записать строку, разобрано в статье про типы данных SQLite.

Создание таблицы SQL-запросом

Запросом быстрее, если таблиц несколько или структуру нужно повторить в другой базе. Откройте вкладку Execute SQL, введите запрос в редактор и выполните его кнопкой с треугольником или клавишами Ctrl+Return. Результат и текст ошибки, если она есть, появятся в панели под редактором.

CREATE TABLE IF NOT EXISTS products (
    id       INTEGER PRIMARY KEY AUTOINCREMENT,
    title    TEXT    NOT NULL,
    price    REAL    NOT NULL DEFAULT 0,
    sku      TEXT    UNIQUE,
    added_at TEXT    DEFAULT CURRENT_TIMESTAMP
);

Конструкция IF NOT EXISTS защищает от ошибки table products already exists при повторном запуске. После выполнения нажмите Write Changes, затем проверьте результат на вкладке Database Structure: таблица появится в списке, а при раскрытии видны ее столбцы. На вкладке Browse Data в нее можно сразу вносить строки.

Связанная таблица с внешним ключом создается так же. Столбец product_id ссылается на id из products, подробнее о связях в статье про первичный и внешний ключ.

CREATE TABLE IF NOT EXISTS reviews (
    id         INTEGER PRIMARY KEY,
    product_id INTEGER NOT NULL REFERENCES products(id),
    rating     INTEGER CHECK (rating BETWEEN 1 AND 5),
    text       TEXT
);

Изменение структуры готовой таблицы

Если таблица уже создана, а в ней не хватает столбца или нужно поменять тип, выделите ее на вкладке Database Structure и нажмите Modify Table. Откроется то же окно, что и при создании. Возможности команды ALTER TABLE в SQLite ограничены, поэтому для сложных правок DB4S за вас создает новую таблицу, копирует в нее данные и удаляет старую.

Простые изменения можно сделать и запросом:

ALTER TABLE products ADD COLUMN stock INTEGER DEFAULT 0;
ALTER TABLE products RENAME COLUMN sku TO article;
ALTER TABLE products RENAME TO goods;

Удаление столбца через ALTER TABLE ... DROP COLUMN поддерживается начиная с SQLite 3.35. Столбец, который входит в первичный ключ, имеет ограничение UNIQUE или используется в индексе, так удалить нельзя, остается путь через Modify Table.

Удаление таблицы

Через интерфейс: на вкладке Database Structure щелкните по таблице правой кнопкой и выберите Delete Table, либо выделите ее и нажмите ту же кнопку на панели. Программа попросит подтверждение. Таблица удаляется вместе со всеми данными, индексами и триггерами.

Через SQL на вкладке Execute SQL:

DROP TABLE IF EXISTS reviews;

Без IF EXISTS запрос к несуществующей таблице завершится ошибкой no such table. Пока не нажата Write Changes, удаление можно отменить кнопкой Revert Changes. После сохранения вернуть данные можно только из резервной копии, поэтому перед удалением важной таблицы стоит скопировать файл .db.

Если нужно очистить таблицу, но сохранить ее структуру, используйте не DROP, а DELETE FROM reviews;. Порядок удаления тоже важен: при включенной проверке внешних ключей сначала удаляют зависимую таблицу reviews и только потом products.

То же самое из Python

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

import sqlite3

with sqlite3.connect("shop.db") as conn:
    conn.execute("""
        CREATE TABLE IF NOT EXISTS products (
            id    INTEGER PRIMARY KEY AUTOINCREMENT,
            title TEXT NOT NULL,
            price REAL NOT NULL DEFAULT 0
        )
    """)
    tables = conn.execute(
        "SELECT name FROM sqlite_master WHERE type = 'table'"
    ).fetchall()
    print(tables)  # [('products',), ('sqlite_sequence',)]
    conn.execute("DROP TABLE IF EXISTS products")

Блок with у соединения sqlite3 фиксирует транзакцию при успешном выходе, но само соединение не закрывает. В длинной программе вызывайте conn.close() явно. Служебная таблица sqlite_sequence появляется автоматически, когда в базе есть столбец с AUTOINCREMENT.

Частые вопросы

Ответы на вопросы, которые чаще всего возникают при работе с таблицами в DB4S.

Почему созданная таблица пропала после перезапуска?

Изменения не были сохранены. DB4S держит их в открытой транзакции до нажатия Write Changes или Ctrl+S. Если закрыть программу и отказаться от сохранения, таблица в файл не попадет.

Почему база заблокирована и Python пишет database is locked?

В DB4S остались несохраненные изменения, и программа удерживает блокировку файла. Нажмите Write Changes или Revert Changes, после этого скрипт сможет записывать в базу.

Можно ли переименовать таблицу без потери данных?

Да, запросом ALTER TABLE старое_имя RENAME TO новое_имя; или в окне Modify Table. Данные, индексы и триггеры сохраняются.

Как удалить сразу несколько таблиц?

В SQLite одна команда DROP TABLE удаляет одну таблицу. Запишите несколько команд подряд через точку с запятой на вкладке Execute SQL и выполните их все одним запуском.