Типы данных SQLite и CREATE TABLE: строим схему базы

В первом уроке ты объявил таблицу books с колонками id, title, author и year — и это уже была схема. Сегодня разберём её по косточкам: какие типы данных бывают в SQLite (спойлер: их пять, а не два десятка, как в учебниках по MySQL), зачем таблице ограничения и как менять схему, не пересоздавая таблицу. Всё на сквозном примере раздела — таблице товаров магазина.

Схема — это контракт о данных: какие колонки есть, какие у них типы и что запрещено. Написана один раз — проверяется движком при каждой вставке. Такой контракт экономит часы отладки: мусор отсеивается на входе, а не всплывает посреди отчёта.

Какие типы данных есть в SQLite?

У каждой ячейки SQLite может лежать значение одного из пяти классов типов: NULL (пустота), INTEGER (целое число), REAL (вещественное число с плавающей точкой), TEXT (строка) и BLOB (набор байтов: картинка, файл, бинарный пакет). Всё. Никаких DATE, BOOLEAN и DECIMAL — даты хранят текстом в формате ISO или числом-меткой, логические значения — нулём и единицей.

Проверить, что реально лежит в ячейке, помогает служебная функция typeof() — она возвращает название класса значения. Запустим её над всеми пятью классами сразу: колонку без типа SQLite заполняет чем угодно.

import sqlite3

conn = sqlite3.connect(":memory:")
cur = conn.cursor()
cur.execute("CREATE TABLE demo (value)")     # колонка без типа

cur.execute("INSERT INTO demo VALUES (42)")             # целое
cur.execute("INSERT INTO demo VALUES (3.14)")           # вещественное
cur.execute("INSERT INTO demo VALUES ('текст')")        # строка
cur.execute("INSERT INTO demo VALUES (NULL)")           # пустота
cur.execute("INSERT INTO demo VALUES (x'89504E47')")    # байты: SQL-литерал BLOB

cur.execute("SELECT value, typeof(value) FROM demo")
for row in cur.fetchall():
    print(row)
conn.close()

(42, 'integer')
(3.14, 'real')
('текст', 'text')
(None, 'null')
(b'\x89PNG', 'blob')

Обрати внимание на последнюю строку: литерал x'89504E47' — это способ записать байты прямо в SQL (это заголовок PNG-файла). Python получил их как объект bytes. А NULL в кортеже выглядит как привычный Python-None: sqlite3 переводит пустоты в обе стороны сам.

Чем типы SQLite отличаются от MySQL?

В MySQL и PostgreSQL колонка обязана выбрать тип из длинного меню: VARCHAR(255), DECIMAL(10,2), DATETIME — и движок жёстко следит, чтобы в неё попадало только оно. SQLite выбрала другой путь: типы указывает колонке, но хранит каждая ячейка то, что в неё положили. Сравни оба подхода.

-- Серверные базы заставляют выбирать из десятков типов:
-- CREATE TABLE products (
--     id    INT AUTO_INCREMENT PRIMARY KEY,
--     name  VARCHAR(255) NOT NULL,
--     price DECIMAL(10, 2) NOT NULL
-- );

-- SQLite хватает пяти классов, а VARCHAR и DECIMAL - просто синонимы:
CREATE TABLE products (
    id    INTEGER PRIMARY KEY,
    name  TEXT NOT NULL,
    price REAL NOT NULL
);

Забавная деталь: написать VARCHAR(255) в SQLite можно — она распознает знакомые имена как синонимы (VARCHAR станет TEXT-подобным типом, DECIMAL — REAL-подобным) и не выдаст ошибки. Но длина в скобках игнорируется: строка из тысячи символов в VARCHAR(10) влезет спокойно. Это удобно для совместимости, но полагаться на длину колонки в SQLite нельзя.

Почему SQLite не ругается на строку в числовой колонке?

Вот он, самый неожиданный факт урока: у колонки есть не жёсткий тип, а аффинити типов — предпочтение. При вставке SQLite пытается привести значение к предпочтению колонки, а если не выходит — молча хранит как есть. Аффинити типов — это вежливость SQLite: колонка INTEGER молча превратит строку '200' в число, а 'abc' оставит строкой. Проверим на живой таблице.

import sqlite3

conn = sqlite3.connect(":memory:")
cur = conn.cursor()
cur.execute("CREATE TABLE nums (value INTEGER)")

cur.execute("INSERT INTO nums VALUES (100)")      # честное целое
cur.execute("INSERT INTO nums VALUES ('200')")    # строка с числом!
cur.execute("INSERT INTO nums VALUES ('abc')")    # строка-не-число
cur.execute("INSERT INTO nums VALUES (2.5)")      # вещественное

cur.execute("SELECT value, typeof(value) FROM nums")
for row in cur.fetchall():
    print(row)
conn.close()

(100, 'integer')
(200, 'integer')
('abc', 'text')
(2.5, 'real')

Смотри, что произошло. Строка '200' стала числом 200 — аффинити INTEGER распознало число в тексте. Строка 'abc' числом не стала и легла в колонку INTEGER как текст — ни ошибки, ни предупреждения. А 2.5 осталось вещественным: INTEGER-аффинити превращает REAL в целое только когда преобразование без потерь.

Что делают PRIMARY KEY и AUTOINCREMENT?

PRIMARY KEY — главный ключ таблицы: колонка (или набор колонок), которая однозначно определяет строку. В SQLite у него есть суперспособность: колонка INTEGER PRIMARY KEY — псевдоним внутреннего номера строки (rowid), и если при вставке передать NULL или не указать её вовсе, движок присвоит номер сам: 1, 2, 3… Сравним с обычной колонкой id без PRIMARY KEY.

import sqlite3

conn = sqlite3.connect(":memory:")
cur = conn.cursor()

cur.execute("CREATE TABLE t1 (id INTEGER, title TEXT)")
cur.execute("INSERT INTO t1 (title) VALUES ('без ключа')")
cur.execute("SELECT * FROM t1")
print(cur.fetchall())

cur.execute("CREATE TABLE t2 (id INTEGER PRIMARY KEY, title TEXT)")
cur.execute("INSERT INTO t2 (title) VALUES ('с ключом')")
cur.execute("SELECT * FROM t2")
print(cur.fetchall())
conn.close()

[(None, 'без ключа')]
[(1, 'с ключом')]

В первой таблице никто не заполнил id — и там остался NULL. Во второй движок подставил 1 сам. Автонумерация — заслуга INTEGER PRIMARY KEY, а не отдельного слова.

А что же AUTOINCREMENT? Это усиление: оно запрещает движку переиспользовать освободившиеся номера. Без него, удалив строку с id 3, следующая вставка может снова получить id 3; с AUTOINCREMENT счётчик живёт своей жизнью и растёт только вверх. Для учебных баз переписывать его не нужно — просто знай, что INTEGER PRIMARY KEY уже нумерует сам, а AUTOINCREMENT добавляет строгость ценой скорости. В схеме магазина ниже мы его используем, чтобы показать оба слова вместе.

Зачем нужны NOT NULL, UNIQUE и DEFAULT?

Ограничения — это правила, которые движок проверяет при каждой вставке, бесплатно для твоего кода. NOT NULL запрещает пустоту: колонка обязана получить значение. UNIQUE запрещает повторы: два одинаковых значения в колонке не уживутся. DEFAULT подставляет значение по умолчанию, если колонку при вставке не упомянули. Соберём схему магазина из плана раздела: товары с именем, ценой, артикулом и остатком на складе.

import sqlite3

conn = sqlite3.connect(":memory:")
cur = conn.cursor()

cur.execute("""
    CREATE TABLE products (
        id    INTEGER PRIMARY KEY AUTOINCREMENT,
        name  TEXT NOT NULL,
        price REAL NOT NULL DEFAULT 0.0,
        sku   TEXT UNIQUE,
        stock INTEGER DEFAULT 0
    )
""")

cur.execute("INSERT INTO products (name, price, sku) VALUES ('Клавиатура', 2990.0, 'KB-01')")
# цену и остаток не указали - DEFAULT подставит сам:
cur.execute("INSERT INTO products (name) VALUES ('Мышь')")

cur.execute("SELECT * FROM products")
for row in cur.fetchall():
    print(row)
conn.close()

(1, 'Клавиатура', 2990.0, 'KB-01', 0)
(2, 'Мышь', 0.0, None, 0)

Вторая вставка упомянула только имя — и всё остальное сделала схема: price и stock получили значения по умолчанию, id выдал счётчик. А sku у мыши остался NULL. Тонкость: UNIQUE пропускает сколько угодно NULL — «нет артикула» и «артикул занят» считаются разными ситуациями. Если пустой артикул запрещён, пиши sku TEXT UNIQUE NOT NULL.

А что случится, если правила всё-таки нарушить? Движок поднимает исключение sqlite3.IntegrityError — и вставка не проходит. Ловим оба нарушения в одном блоке.

import sqlite3

conn = sqlite3.connect(":memory:")
cur = conn.cursor()
cur.execute("""
    CREATE TABLE products (
        id   INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        sku  TEXT UNIQUE
    )
""")
cur.execute("INSERT INTO products (name, sku) VALUES ('Стол', 'TB-9')")

try:
    # name не указали - а он NOT NULL:
    cur.execute("INSERT INTO products (sku) VALUES ('TB-9')")
except sqlite3.IntegrityError as e:
    print("Ошибка 1:", e)

try:
    # sku уже занят другим товаром:
    cur.execute("INSERT INTO products (name, sku) VALUES ('Стул', 'TB-9')")
except sqlite3.IntegrityError as e:
    print("Ошибка 2:", e)
conn.close()

Ошибка 1: NOT NULL constraint failed: products.name
Ошибка 2: UNIQUE constraint failed: products.sku

Читай текст исключения как сводку: NOT NULL constraint failed: products.name — колонка name таблицы products не потерпела пустоту. Именно так выглядит «валидация на входе»: мусор не попал в базу, а код узнал об этом управляемым исключением, которое можно перехватить и красиво обработать.

Как посмотреть схему таблицы?

Схему, которую ты создал вчера (или написал коллега), удобно рассмотреть изнутри. Для этого есть команда PRAGMA table_info(имя_таблицы) — она возвращает по строке на каждую колонку: порядковый номер, имя, тип, флаг NOT NULL, значение по умолчанию и флаг PRIMARY KEY.

import sqlite3

conn = sqlite3.connect(":memory:")
cur = conn.cursor()
cur.execute("""
    CREATE TABLE products (
        id    INTEGER PRIMARY KEY,
        name  TEXT NOT NULL,
        price REAL DEFAULT 0.0
    )
""")

cur.execute("PRAGMA table_info(products)")
for row in cur.fetchall():
    print(row)
conn.close()

(0, 'id', 'INTEGER', 0, None, 1)
(1, 'name', 'TEXT', 1, None, 0)
(2, 'price', 'REAL', 0, '0.0', 0)

Разбор колонок выдачи: имя видно сразу; notnull (четвёртое поле) равно 1 только у name; в пятом поле лежит DEFAULT — у price это строка '0.0'; шестое поле (pk) равно 1 у id — это и есть PRIMARY KEY. Такой же рентген доступен и чужим таблицам: PRAGMA — твой инструмент разведки в незнакомой базе.

Как добавить колонку в готовую таблицу?

Схемы меняются: понадобился рейтинг товара, которого при проектировании не было. Пересоздавать таблицу с данными не нужно — есть ALTER TABLE ... ADD COLUMN. Новая колонка появляется у всех существующих строк, и они получают её значение по умолчанию.

import sqlite3

conn = sqlite3.connect(":memory:")
cur = conn.cursor()
cur.execute("CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT)")
cur.execute("INSERT INTO books (title) VALUES ('Война и мир')")

# таблица уже с данными - добавляем колонку:
cur.execute("ALTER TABLE books ADD COLUMN rating REAL DEFAULT 0.0")

cur.execute("SELECT * FROM books")
print(cur.fetchall())
conn.close()

[(1, 'Война и мир', 0.0)]

Строка с «Войной и миром» вставлялась до ALTER — а после него у неё уже три колонки, и rating честно заполнен нулём из DEFAULT. Ограничение у ADD COLUMN одно: новая колонка не может быть NOT NULL без DEFAULT — иначе движку неоткуда взять значения для существующих строк.

Как включить строгую проверку типов?

С версии 3.37 у SQLite есть ответ тем, кто хочет вести себя как серверная база: слово STRICT в конце CREATE TABLE. В строгой таблице вставка значения чужого типа — честная ошибка, а не молчаливая терпимость. Сравни с питфоллом из середины урока.

import sqlite3

conn = sqlite3.connect(":memory:")
cur = conn.cursor()
cur.execute("CREATE TABLE prices (value REAL) STRICT")

try:
    cur.execute("INSERT INTO prices VALUES ('много')")
except sqlite3.IntegrityError as e:
    print("Строгая таблица:", e)

cur.execute("INSERT INTO prices VALUES (1990.0)")
cur.execute("SELECT * FROM prices")
print(cur.fetchall())
conn.close()

Строгая таблица: cannot store TEXT value in REAL column prices.value
[(1990.0,)]

Строгая таблица допускает только пять классов типов (INTEGER, REAL, TEXT, BLOB, ANY) и проверяет каждый INSERT. Для учебных упражнений обычных таблиц достаточно, но в проектах STRICT — дешёвая страховка от сюрпризов аффинити.

Что дальше

Ты умеешь читать и писать схемы: пять классов типов, PRIMARY KEY с автонумерацией, NOT NULL, UNIQUE и DEFAULT, ALTER TABLE и строгие таблицы. Схема готова принимать данные — в уроке 3 займёмся наполнением всерьёз: INSERT с параметрами ?, вставка пачки через executemany и первый большой разговор о безопасности — SQL-инъекции. А если прошёл урок 1 давно — освежи тройку connect — cursor — execute, там она вводится.

Схема — это контракт о данных: написана один раз, а проверяется движком при каждой вставке, бесплатно для твоего кода.